MySQL 8.0+ 索引优化50招！从踩坑到精通，让查询快到飞起

说起MySQL查询慢，十有八九是我们没把索引用清楚！不少小伙伴刚接触时，要么觉得“建个索引就行”，随手加一堆反而拖慢插入更新；要么写个查询就全表扫描，眼睁睁看着页面加载转圈。实则MySQL索引就像图书馆的目录，翻对了一秒找书，翻错了就得挨个书架乱逛，尤其到了8.0版本，多了降序索引、函数索引这些新词，用好了能让查询速度翻翻翻，用不好就是白费劲。下面这些，全是我们平时干活踩过的坑、试过的招。从最基础的B+树原理，到怎么设计联合索引不浪费，再到查询时哪些写法会让索引失效，甚至大表、JSON数据、地理信息这些特殊场景，都给我们拆解得明清楚白。不管我们是刚入门的新手，还是想去优化老项目，跟着“基础→设计→避坑→维护”的节奏走，不用死记硬背，也能把索引玩清楚，让我们的MySQL跑起来又快又稳！

一、先搞懂索引的底层逻辑

1、常用索引类型与B+树特点

MySQL 8.0常用索引可分为6类，分别是主键索引（唯一标识表中记录，InnoDB默认基于主键构建聚簇索引）、唯一索引（确保字段无重复值，查询时无需判断重复，效率高于普通索引）、普通索引（最基础的索引类型，无唯一性约束，仅用于加速查询）、联合索引（多字段组合构建的索引，遵循“最左匹配”原则）、全文索引（针对长文本的高效检索索引，替代LIKE %xxx%的低效模糊查询）、空间索引（专门优化地理信息数据查询，如：经纬度范围匹配）。

作为MySQL主流索引结构，B+树有三大特性：非叶子节点仅存储索引值，不存实际数据，能最大化单个节点存储的索引数量，减少IO层级；叶子节点存储完整数据（聚簇索引，如：主键索引）或主键值（二级索引，如：普通索引、唯一索引），查询到叶子节点即能获取目标数据或定位到主键；叶子节点通过双向指针串联，形成有序链表，支持快速范围查询（如：查询age>20且age<30的数据），无需回溯上层节点。

2、聚簇索引与二级索引的区别

存储内容不同：聚簇索引的叶子节点直接存储表的完整行数据，索引与数据“聚簇”在一起，查询命中聚簇索引时无需额外跳转；二级索引的叶子节点仅存储主键值，查询命中二级索引后，需通过主键值再次查询聚簇索引获取完整数据，这个过程我们称为“回表”。
数量限制不同：一张表仅能有1个聚簇索引（InnoDB默认以主键为聚簇索引，若未显式定义主键，会选唯一非空字段，若无则自动生成隐藏主键）；二级索引数量无强制限制（但需控制在5个以内，避免写性能损耗），我们可根据查询需求创建多个普通索引、唯一索引等。
查询效率不同：等值查询、范围查询场景下，聚簇索引效率更高，无需回表；二级索引若未形成“覆盖索引”（索引包含查询所需所有字段），会因回表增加IO开销，效率低于聚簇索引；若形成覆盖索引，二级索引效率可接近聚簇索引。

3、MySQL 8.0新增索引特性（降序索引、部分索引）

降序索引：MySQL 8.0前，联合索引默认按升序排列，若查询需“混合排序”（如：ORDER BY age ASC, score DESC），无法利用索引，需触发“文件排序”；8.0支持显式创建降序索引（建索引时指定字段排序方向），可直接匹配混合排序场景，避免文件排序。例如：CREATE INDEX idx_age_score_desc ON user(age ASC, score DESC)，查询SELECT * FROM user ORDER BY age ASC, score DESC时能命中索引，大幅提升排序效率。
部分索引：也称“条件索引”，仅对表中符合特定条件的数据构建索引，而非全量数据，能显著减少索引体积。例如：针对“活跃用户”（user_status=’active’）的查询，可创建CREATE INDEX idx_active_user ON user(username, email) WHERE user_status = ‘active’，仅活跃用户数据会进入该索引，索引体积可减少50%以上，查询活跃用户时既能命中索引，又能降低IO开销。我们需注意，部分索引仅支持InnoDB引擎，且条件需用常量表达式（如：=、IN，不支持范围条件）。

二、索引设计：从“源头”优化结构，避免性能浪费

索引设计是所有优化的基础，设计合理的索引结构，避免“先天性能缺陷”，减少后续优化成本，不合理的设计会让后续调优事倍功半。

1、联合索引顺序：按“最左匹配+选择性”排序

MySQL对联合索引的匹配遵循“从左到右、中断失效”原则，排序需满足3个优先级：
1、选择性高（不重复值多，如：身份证号，选择性>0.8）的字段放前面；
2、查询频率高的字段放前面；
3、范围查询（如：age>20）的字段放最后。

踩坑示例：

-- 错误顺序：username(低选择性)→user_age(范围)→user_city，导致后续字段无法命中
CREATE INDEX idx_customer_bad ON customer(username, user_age, user_city);

-- 坑1：缺失最左username，索引完全失效
SELECT * FROM customer WHERE user_age = 28 AND user_city = 'Shanghai';

-- 坑2：中间user_age是范围查询，后续user_city失效
SELECT * FROM customer WHERE username = 'Jerry' AND user_age > 28 AND user_city = 'Shanghai';

正确操作：

-- 正确顺序：user_age(选择性0.9)→username(选择性0.85)→user_city(选择性0.3)
CREATE INDEX idx_customer_good ON customer(user_age, username, user_city);

-- 命中前2列，效率高
SELECT * FROM customer WHERE user_age = 28 AND username = 'Jerry';

-- 三列全命中，索引最大化利用
SELECT * FROM customer WHERE user_age = 28 AND username = 'Jerry' AND user_city = 'Shanghai';

2、覆盖索引：避免“回表”，一次查全数据

“回表”指查询完二级索引后，还需通过主键索引查询全表数据的操作。若索引包含查询所需的所有字段，则无需回表，即覆盖索引。

需回表：

-- 仅username单列索引，无法覆盖user_age和user_city
CREATE INDEX idx_customer_username ON customer(username);

-- 需回表查询user_age和user_city，效率低
SELECT username, user_age, user_city FROM customer WHERE username = 'Jerry';

覆盖索引：

-- 索引包含查询的三个字段，实现覆盖查询
CREATE INDEX idx_customer_cover ON customer(username, user_age, user_city);

-- 直接从索引取数，无需回表，速度提升5-10倍
SELECT username, user_age, user_city FROM customer WHERE username = 'Jerry';

3、长字符串优化：前缀索引省空间

对于商品描述、文章内容等长字符串（如：varchar(200)），全字段建索引会占用大量空间，我们可截取前缀建立前缀索引，需保证前缀选择性接近全字段。

全字段浪费空间：

-- 商品详情字段200字符，全建索引占用空间大
CREATE INDEX idx_goods_detail_bad ON goods(goods_detail);

前缀索引高效省空间：

-- 第一步：计算不同前缀的选择性，找到接近全字段的值
SELECT
  COUNT(DISTINCT LEFT(goods_detail, 10))/COUNT(*) AS sel_10,  -- 选择性0.65
  COUNT(DISTINCT LEFT(goods_detail, 20))/COUNT(*) AS sel_20,  -- 选择性0.92
  COUNT(DISTINCT LEFT(goods_detail, 30))/COUNT(*) AS sel_30,  -- 选择性0.99（接近全字段0.995）
  COUNT(DISTINCT goods_detail)/COUNT(*) AS sel_full           -- 全字段选择性
FROM goods;

-- 第二步：用30个字符建前缀索引，平衡空间与效率
CREATE INDEX idx_goods_detail_good ON goods(goods_detail(30));

4、多条件查询：复合索引替代多单列索引

MySQL一次查询仅能使用一个单列索引（“索引合并”例外，但效率低），多条件查询我们提议直接建复合索引，而非多个单列索引。

多单列索引低效：

-- 分别建user_age和user_city单列索引，查询仅能用一个
CREATE INDEX idx_customer_age ON customer(user_age);
CREATE INDEX idx_customer_city ON customer(user_city);

-- 仅命中一个索引，另一个条件需过滤，效率低
SELECT * FROM customer WHERE user_age = 28 AND user_city = 'Shanghai';

复合索引高效：

-- 建user_age+user_city复合索引，同时命中两个条件
CREATE INDEX idx_customer_age_city ON customer(user_age, user_city);

-- 索引直接匹配两个条件，查询速度提升明显
SELECT * FROM customer WHERE user_age = 28 AND user_city = 'Shanghai';

5、唯一索引：优先用在“无重复”场景

对于用户手机号、身份证号等唯一字段，我们优先建唯一索引而非普通索引。唯一索引的B+树结构更紧凑，查询时无需判断“是否有重复值”，效率更高。

示例：

-- 手机号唯一，建唯一索引（避免重复+提升查询效率）
CREATE UNIQUE INDEX idx_customer_phone ON customer(phone);

-- 唯一索引查询效率略高于普通索引
SELECT * FROM customer WHERE phone = '13800138000';

6、避免“过度索引”：控制单表索引数量（单表提议≤5个）

索引并非越多越好，每个索引都会增加插入/更新/删除的开销（需同步维护索引）。单表索引我们提议不超过5个，定期清理无用索引。

查冗余/未用索引：

-- 1、查未被使用过的索引（需先开启Performance Schema）
SELECT * FROM performance_schema.schema_unused_indexes WHERE table_schema = 'shop';

-- 2、查冗余索引（如：两个索引为(username,user_age)和(username)，后者冗余）
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'shop';

-- 3、删除冗余索引
DROP INDEX idx_customer_username ON customer;

三、查询避坑：别让索引“失效”

索引建好后，错误的查询写法会导致索引失效，白做优化。我们需规避查询语句中导致索引无法命中的“高频错误操作”，确保索引被有效利用，避免全表扫描。

7、避免字段上用函数/运算：索引会失效

若在索引字段上使用函数（如：YEAR()、SUBSTR()）或运算（如：age+1），MySQL无法直接匹配索引值，会导致全表扫描。

索引失效：

-- 建order_time索引，但查询时用了YEAR()函数
CREATE INDEX idx_order_time ON orders(order_time);
SELECT * FROM orders WHERE YEAR(order_time) = 2024;  -- 全表扫描，索引失效

索引生效：

-- 用时间范围替代函数，命中索引
SELECT * FROM orders WHERE order_time >= '2024-01-01 00:00:00' AND order_time < '2025-01-01 00:00:00';

8、字段类型必须匹配：避免隐式转换

若查询条件的值类型与字段类型不匹配（如：varchar字段用数字查询），MySQL会进行隐式转换，导致索引失效。

隐式转换，索引失效：

-- customer_id为varchar类型，建索引后用数字查询
CREATE INDEX idx_customer_id ON customer(customer_id);
SELECT * FROM customer WHERE customer_id = 456;  -- 隐式转换，索引失效

类型一致，索引生效：

-- 用字符串匹配varchar字段，命中索引
SELECT * FROM customer WHERE customer_id = '456';

9、模糊查询：避免“%”开头

LIKE ‘%xxx’（前缀模糊）会导致索引失效，LIKE ‘xxx%’（后缀模糊）可命中索引；若需前缀模糊，我们用“字段反转+索引”解决。

场景1：后缀模糊（直接用索引）：

-- 建goods_name索引，后缀模糊可命中
CREATE INDEX idx_goods_name ON goods(goods_name);
SELECT * FROM goods WHERE goods_name LIKE '华为%';  -- 索引生效

场景2：前缀模糊（反转字段+索引）：

-- 优化前：前缀模糊，索引失效
SELECT * FROM goods WHERE goods_name LIKE '%手机';  -- 全表扫描

-- 优化后：反转字段+索引
-- 1、新增反转字段
ALTER TABLE goods ADD goods_name_rev VARCHAR(100);
-- 2、建触发器，插入/更新时自动反转
DELIMITER //
CREATE TRIGGER trg_goods_rev BEFORE INSERT ON goods
FOR EACH ROW SET NEW.goods_name_rev = REVERSE(NEW.goods_name);
//
CREATE TRIGGER trg_goods_rev_update BEFORE UPDATE ON goods
FOR EACH ROW SET NEW.goods_name_rev = REVERSE(NEW.goods_name);
//
DELIMITER ;
-- 3、建反转字段索引
CREATE INDEX idx_goods_name_rev ON goods(goods_name_rev);
-- 4、查询时反转关键词，命中索引
SELECT * FROM goods WHERE goods_name_rev LIKE CONCAT(REVERSE('手机'), '%');

10、避免否定查询：用范围替代NOT/!=

NOT IN、!=、<>等否定查询会导致索引失效，我们可转换为范围查询（<+>）命中索引。

否定查询，索引失效：

-- goods_type建索引，否定查询导致全表扫描
CREATE INDEX idx_goods_type ON goods(goods_type);
SELECT * FROM goods WHERE goods_type != 3;  -- 全表扫描

范围查询，索引生效：

-- 拆分为两个范围条件，命中索引
SELECT * FROM goods WHERE goods_type < 3 OR goods_type > 3;

11、分页大偏移量：用“索引覆盖+关联”或“主键定位”优化

LIMIT 100000,10需扫描100010条数据再丢弃前100000条，效率极低，我们可通过两种方法优化。

方法1：索引覆盖+关联（支持任意分页）：

-- 优化前：大偏移量，慢
SELECT * FROM goods ORDER BY goods_id LIMIT 100000, 10;

-- 优化后：子查询用主键索引查ID，再关联主表
SELECT g.* FROM goods g
JOIN (
    SELECT goods_id FROM goods ORDER BY goods_id LIMIT 100000, 10  -- 主键索引，快
) tmp ON g.goods_id = tmp.goods_id;

方法2：主键定位（适合下拉加载，不支持跳页）：

-- 记录上一页最后一个goods_id（如：100233），直接查后续数据
SELECT * FROM goods WHERE goods_id > 100233 ORDER BY goods_id LIMIT 10;

12、禁用SELECT *：按需取字段

SELECT *会查询所有字段，可能破坏覆盖索引，还浪费内存和网络带宽，我们应明确指定所需字段。

SELECT * 低效：

-- 查所有字段，即使有覆盖索引也失效
SELECT * FROM customer WHERE username = 'Jerry';

指定字段，覆盖索引生效：

-- 仅查需要的字段，命中覆盖索引
SELECT customer_id, username, email FROM customer WHERE username = 'Jerry';

13、用EXPLAIN验证：提前发现索引问题

写好查询后，我们先用EXPLAIN分析执行计划，主要看3个字段：

type：最好为ref（等值匹配）、range（范围匹配），避免ALL（全表扫描）；
key：显示实际使用的索引，为空则未命中；
Extra：出现Using index表明命中覆盖索引，Using filesort（文件排序）、Using temporary（临时表）需优化。

示例：

-- 分析查询是否命中索引
EXPLAIN SELECT customer_id, username FROM customer WHERE user_age = 28 AND username = 'Jerry';

四、特殊场景：针对性优化

针对MySQL中高频特殊场景（排序、大表、非结构化数据、地理信息等），需解决“通用优化无法覆盖”的问题。

14、ORDER BY排序：用索引避免“文件排序”

若查询同时包含“过滤+排序”，我们需将过滤字段和排序字段组合成联合索引，让排序直接利用索引顺序，避免Using filesort。

文件排序，慢：

-- 仅username索引，排序需额外文件排序
CREATE INDEX idx_customer_username ON customer(username);
SELECT * FROM customer WHERE username = 'Jerry' ORDER BY user_age;  -- Extra: Using filesort

索引排序，快：

-- 建username+user_age联合索引，过滤后直接按索引排序
CREATE INDEX idx_customer_name_age ON customer(username, user_age);
SELECT * FROM customer WHERE username = 'Jerry' ORDER BY user_age;  -- 无文件排序

15、混合排序（ASC+DESC）：用降序索引解决

MySQL 8.0前，ORDER BY age ASC, score DESC这类混合排序无法利用索引；8.0支持降序索引，可直接匹配排序方向。

无法利用索引：

-- 普通升序索引，混合排序需文件排序
CREATE INDEX idx_customer_age_score ON customer(user_age, user_score);
SELECT * FROM customer ORDER BY user_age ASC, user_score DESC;  -- Using filesort

降序索引生效：

-- 我们建索引时明确user_score为降序，匹配查询排序
CREATE INDEX idx_customer_age_score_desc ON customer(user_age ASC, user_score DESC);
SELECT * FROM customer ORDER BY user_age ASC, user_score DESC;  -- 无文件排序

16、大表建索引：用在线DDL避免锁表

百万级以上大表直接建索引会锁表，导致业务中断，MySQL 8.0的在线DDL（ALGORITHM=INPLACE+LOCK=NONE）可实现无锁建索引。

直接建索引，锁表：

-- 大表直接建索引，业务卡住
CREATE INDEX idx_order_state ON orders(order_state);

在线DDL，不锁表：

-- 不锁表、不拷贝全表，建索引期间业务正常
CREATE INDEX idx_order_state ON orders(order_state) ALGORITHM=INPLACE, LOCK=NONE;

17、频繁计算过滤：虚拟列+索引一步到位

若频繁用“字段计算”作为查询条件（如：price*quantity>10000），每次计算耗时，我们可通过虚拟列存储计算结果，再建索引。

每次计算，慢：

-- 每次查询都计算price*quantity，无索引
SELECT * FROM goods WHERE goods_price * sales_count > 20000;

虚拟列+索引，快：

-- 1、新增虚拟列，自动计算结果（VIRTUAL：不存磁盘；STORED：存磁盘，更新同步）
ALTER TABLE goods ADD total_sales DECIMAL(12,2) AS (goods_price * sales_count) STORED;

-- 2、我们给虚拟列建索引
CREATE INDEX idx_goods_total_sales ON goods(total_sales);

-- 3、直接查询虚拟列，命中索引
SELECT * FROM goods WHERE total_sales > 20000;

18、高频等值查询：哈希索引提速

InnoDB无显式哈希索引，我们可通过“生成哈希列+普通索引”模拟，适合高频等值查询（如：按用户名查用户），哈希索引等值匹配速度快于B+树索引。

B+树索引：

-- B+树索引等值查询效率较高，但哈希索引更快
CREATE INDEX idx_customer_username ON customer(username);
SELECT * FROM customer WHERE username = 'Jerry';

模拟哈希索引：

-- 1、新增哈希列，用SHA1生成哈希值（冲突率低）
ALTER TABLE customer ADD username_hash CHAR(40) GENERATED ALWAYS AS (SHA1(username)) STORED;

-- 2、我们给哈希列建索引
CREATE INDEX idx_customer_name_hash ON customer(username_hash);

-- 3、查询时先匹配哈希值，再验证原字段（避免哈希冲突）
SELECT * FROM customer WHERE username_hash = SHA1('Jerry') AND username = 'Jerry';

19、部分数据查询：部分索引省空间

若仅针对表中部分数据查询（如：只查“活跃用户”），我们可建部分索引（MySQL 8.0.13+支持），仅对符合条件的数据建索引，大幅减少索引体积。

全量索引，浪费空间：

-- 给所有用户建索引，包括不活跃用户，索引体积大
CREATE INDEX idx_customer_name_email ON customer(username, email);
SELECT username, email FROM customer WHERE user_status = 'active' AND username = 'Jerry';

部分索引，高效省空间：

-- 仅对user_status='active'的用户建索引，索引体积减少50%+
CREATE INDEX idx_active_customer ON customer(username, email) WHERE user_status = 'active';

-- 查询直接命中部分索引，效率提升
SELECT username, email FROM customer WHERE user_status = 'active' AND username = 'Jerry';

20、空间数据查询：用SPATIAL索引加速地理信息查询

若业务涉及经纬度、地址等空间数据（如：“查询某商圈5公里内的店铺”），普通索引效率极低，MySQL的SPATIAL索引（空间索引）可针对性优化。

示例：经纬度查询

-- 1、建表时定义空间类型字段（POINT类型存储经纬度：X=经度，Y=纬度）
CREATE TABLE shops (
    shop_id INT PRIMARY KEY AUTO_INCREMENT,
    shop_name VARCHAR(100),
    location POINT NOT NULL,  -- 空间类型：存储经纬度
    SPATIAL INDEX idx_shop_location (location)  -- 建空间索引
);

-- 2、插入数据（用ST_GeomFromText函数生成POINT对象）
INSERT INTO shops (shop_name, location) 
VALUES ('便利店A', ST_GeomFromText('POINT(116.4042 39.9153)')),  -- 北京天安门附近
       ('超市B', ST_GeomFromText('POINT(116.4142 39.9253)'));

-- 3、空间查询（查询距离目标点1公里内的店铺）
-- 目标点：116.4042,39.9153；1公里≈0.009度经纬度
SELECT shop_name, 
       ST_Distance_Sphere(location, ST_GeomFromText('POINT(116.4042 39.9153)')) AS distance  -- 计算距离（米）
FROM shops
WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(116.4042 39.9153)')) < 1000;  -- 1公里内

21、JSON数据查询：用JSON索引优化非结构化数据

MySQL 8.0支持JSON数据类型，但直接查询JSON字段会全表扫描，我们需创建JSON索引（针对JSON中的特定键值）提升效率。

示例：用户标签JSON查询

-- 1、建表：user_tags为JSON类型，存储用户标签（如：{"gender":"male","hobby":["reading","sports"]}）
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    user_tags JSON
);

-- 2、建JSON索引：针对user_tags中的"gender"键（需用CAST转换为字符串类型）
CREATE INDEX idx_users_tags_gender ON users(CAST(user_tags->>'$.gender' AS CHAR(10)));

-- 3、建JSON多键索引：针对"hobby"数组中的值（需用JSON_CONTAINS查询）
-- 注意：JSON数组索引需用MEMBER OF或JSON_CONTAINS，且索引需匹配数组元素类型
CREATE INDEX idx_users_tags_hobby ON users(CAST(user_tags->>'$.hobby[*]' AS CHAR(20)));

-- 4、索引查询示例
-- 查性别为男性的用户（命中idx_users_tags_gender）
SELECT username FROM users WHERE CAST(user_tags->>'$.gender' AS CHAR(10)) = 'male';

-- 查爱好包含"reading"的用户（命中idx_users_tags_hobby）
SELECT username FROM users WHERE JSON_CONTAINS(user_tags->>'$.hobby', '"reading"');

注意：

JSON索引仅支持对JSON中的“标量值”（字符串、数字等）创建，不支持直接对整个JSON对象/数组建索引；
查询时的表达式需与索引创建时完全一致（如：CAST类型、JSON路径），否则索引失效。

22、全文检索：用FULLTEXT索引替代LIKE

对文章、商品描述等长文本做模糊查询时，LIKE ‘%关键词%’效率极低，我们可建FULLTEXT索引实现高效全文检索。

示例：

-- 1、建全文索引
CREATE FULLTEXT INDEX idx_goods_desc ON goods(goods_description);

-- 2、全文检索（MATCH+AGAINST）
-- 匹配包含“华为”或“手机”的商品
SELECT * FROM goods WHERE MATCH(goods_description) AGAINST('华为 手机' IN BOOLEAN MODE);
-- 匹配同时包含“华为”和“手机”的商品
SELECT * FROM goods WHERE MATCH(goods_description) AGAINST('+华为 +手机' IN BOOLEAN MODE);

23、函数索引：解决“字段函数查询”的索引失效问题

MySQL 8.0前，字段用函数会导致索引失效；8.0支持函数索引（基于函数结果建索引），可直接命中含函数的查询。

示例：手机号脱敏查询

-- 场景：用户手机号存储为11位明文（如：13800138000），需查询脱敏后的手机号（如：138****8000）
-- 优化前：用函数查询，全表扫描
SELECT username FROM users WHERE CONCAT(LEFT(phone,3), '****', RIGHT(phone,4)) = '138****8000';

-- 优化后：创建函数索引
-- 1、建函数索引（基于脱敏表达式）
CREATE INDEX idx_users_phone_masked ON users(CONCAT(LEFT(phone,3), '****', RIGHT(phone,4)));

-- 2、查询直接命中函数索引
SELECT username FROM users WHERE CONCAT(LEFT(phone,3), '****', RIGHT(phone,4)) = '138****8000';

日期格式化查询：

-- 建索引：针对DATE_FORMAT(order_time, '%Y-%m')的函数结果
CREATE INDEX idx_orders_month ON orders(DATE_FORMAT(order_time, '%Y-%m'));

-- 查询2024年3月的订单，命中索引
SELECT order_id FROM orders WHERE DATE_FORMAT(order_time, '%Y-%m') = '2024-03';

五、维护监控：让索引持续高效

索引长期使用后会出现“碎片、统计失效”等问题，我们需通过定期维护和监控，确保索引性能不衰减，避免碎片、统计不准等问题导致性能下降。

24、清理索引碎片：定期优化重建

频繁删除/更新数据会导致索引碎片化（索引页存在空洞），查询时需扫描更多页，需定期优化。

示例：

-- 1、分析表，更新统计信息（让MySQL优化器更懂数据分布）
ANALYZE TABLE orders;

-- 2、优化表（整理碎片，仅支持InnoDB/MyISAM，低峰期执行）
OPTIMIZE TABLE orders;

-- 3、或直接重建索引（效果等同于OPTIMIZE，更灵活）
ALTER TABLE orders DROP INDEX idx_order_state, ADD INDEX idx_order_state(order_state);

25、刷新索引统计：避免优化器误判

MySQL依赖索引统计信息（如：基数、行数）选择索引，若统计不准（如：刚导入大量数据），优化器可能选错索引，需定期刷新。

示例：

-- 1、查看表统计信息（行数、数据大小等）
SHOW TABLE STATUS LIKE 'customer';

-- 2、查看索引基数（不重复值数量，越准越好）
SHOW INDEX FROM customer;

-- 3、刷新统计信息（InnoDB默认自动刷新，可手动触发）
ANALYZE TABLE customer;

26、索引提示：强制优化器选对索引

偶尔优化器会因统计偏差选错索引，我们可通过FORCE INDEX强制使用指定索引（不提议滥用，需定期复核）。

优化器选错索引：

-- 存在idx_customer_name_age（username+user_age），但优化器可能选idx_customer_username
SELECT * FROM customer WHERE username = 'Jerry' AND user_age > 28;

强制使用正确索引：

-- 强制使用idx_customer_name_age索引
SELECT * FROM customer FORCE INDEX(idx_customer_name_age) WHERE username = 'Jerry' AND user_age > 28;

-- 反之，可忽略低效索引
SELECT * FROM customer IGNORE INDEX(idx_customer_status) WHERE user_status = 'active' AND user_age > 28;

27、索引失效异常排查：用“performance_schema”定位未命中索引的查询

生产环境中，若突然出现慢查询，需快速定位“哪些查询未命中索引”，我们可通过performance_schema监控未使用索引的SQL，精准定位优化对象。

定位未命中索引的查询：

-- 1、开启performance_schema的SQL监控（默认关闭）
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME='events_statements_current';
UPDATE performance_schema.setup_instruments SET ENABLED='YES' WHERE NAME LIKE 'statement/sql/%';

-- 2、查看“未使用索引”的查询（type=ALL或index_merge，key=NULL）
SELECT 
    DIGEST_TEXT AS sql_text,  -- SQL语句
    COUNT_STAR AS exec_count,  -- 执行次数
    SUM_TIMER_WAIT AS total_time  -- 总耗时
FROM performance_schema.events_statements_summary_by_digest
WHERE 
    -- 排除系统SQL，只看业务SQL
    DIGEST_TEXT NOT LIKE '%performance_schema%'
    -- 未使用索引（key=NULL）或全表扫描（type=ALL）
    AND (DIGEST_TEXT LIKE '%FROM %' AND NOT DIGEST_TEXT LIKE '%FORCE INDEX%')
ORDER BY total_time DESC LIMIT 10;

-- 3、针对定位到的SQL优化
-- 例：发现查询“SELECT * FROM customer WHERE phone='13800138000'”未命中索引
-- 解决方案：给phone字段建索引
CREATE INDEX idx_customer_phone ON customer(phone);

排查流程：

1、开启监控：启用performance_schema的语句级监控；
2、筛选慢查询：按总耗时排序，过滤未使用索引的业务SQL；
3、分析SQL：我们用EXPLAIN验证索引失效缘由（如：函数、隐式转换）；
4、优化落地：建索引或调整SQL，验证后上线。

28、优化器索引选择调优：调整“index_merge_cost_factor”，让优化器优先选索引合并

MySQL优化器默认对“索引合并”的成本评估较高，有时会放弃索引合并转而全表扫描，我们可通过调整系统参数index_merge_cost_factor，降低索引合并的成本权重，让优化器优先选择索引合并。

强制优化器选索引合并：

-- 场景：表有两个单列索引，查询条件为OR，但优化器选择全表扫描
CREATE TABLE customer (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    user_age INT,
    user_city VARCHAR(50),
    INDEX idx_age (user_age),
    INDEX idx_city (user_city)
);

-- 优化前：EXPLAIN显示type=ALL（全表扫描），未用索引合并
EXPLAIN SELECT * FROM customer WHERE user_age = 28 OR user_city = 'Shanghai';

-- 优化：我们调整index_merge_cost_factor参数（默认100，降低为20，减少索引合并成本）
SET session optimizer_switch = 'index_merge_cost_factor=20';

-- 优化后：EXPLAIN显示type=index_merge，使用idx_age和idx_city合并索引
EXPLAIN SELECT * FROM customer WHERE user_age = 28 OR user_city = 'Shanghai';

注意：

该参数提议在会话级别调整（仅对当前连接有效），避免影响全局；
调整后需用EXPLAIN验证，若仍未选择索引合并，可进一步降低参数值（如：10），但不提议低于5（可能导致优化器误判）。

六、痛点攻坚：解决经典性能问题

主要针对的是实际业务中常见的“索引失效、区分度低、关联慢”等痛点。

29、多值查询优化：用IN取代OR，配合联合索引

当需要匹配多个值（如：user_id IN (101,102,103)）时，OR会导致索引失效，而IN可命中索引；若结合联合索引，效率更优。

OR失效：

-- 建user_id索引，OR查询可能全表扫描
CREATE INDEX idx_customer_id ON customer(user_id);
SELECT * FROM customer WHERE user_id = 101 OR user_id = 102 OR user_id = 103;  -- 可能失效

IN+联合索引：

-- 1、若需查询多字段，建联合索引实现覆盖查询
CREATE INDEX idx_customer_id_name ON customer(user_id, username, email);

-- 2、IN查询命中索引，且覆盖查询无需回表
SELECT user_id, username, email FROM customer WHERE user_id IN (101,102,103);

注意：

IN后的值不宜过多（提议不超过1000个），否则优化器可能转为全表扫描；
若值过多，可拆分为多个小IN查询（如：IN (1-500)、IN (501-1000)）或用临时表关联。

30、大表分区+索引：按时间/范围分区，降低索引扫描范围

对于千万级以上的大表（如：订单表、日志表），单一索引扫描范围过大，我们可结合表分区（如：按时间分区），让查询仅扫描目标分区的索引，大幅提升效率。

示例：按时间分区的订单表

-- 1、建分区表（按订单时间分4个季度分区）
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_time DATETIME,
    order_amount DECIMAL(10,2),
    INDEX idx_order_user_time (user_id, order_time)  -- 联合索引
)
PARTITION BY RANGE (TO_DAYS(order_time)) (
    PARTITION p2024q1 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p2024q2 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p2024q3 VALUES LESS THAN (TO_DAYS('2024-10-01')),
    PARTITION p2024q4 VALUES LESS THAN (TO_DAYS('2025-01-01'))
);

-- 2、查询2024年2月的用户订单（仅扫描p2024q1分区的索引）
SELECT order_id, order_amount 
FROM orders 
WHERE user_id = 501 AND order_time BETWEEN '2024-02-01' AND '2024-02-29';

优势：

查询仅扫描目标分区，索引扫描范围缩小至1/N（N为分区数）；
分区可独立维护（如：删除旧分区数据，无需全表操作）。

31、前缀索引冲突：用“前缀+后缀”组合索引解决

当长字符串的前缀选择性不足（如：多个字符串前缀一样），单独前缀索引会导致大量匹配，我们可通过“前缀索引+后缀截取字段”组合优化。

示例：商品SKU格式为“AB-2024-XXXX”（如：AB-2024-1234、AB-2024-5678），前缀“AB-2024-”重复率高，单独前缀索引效率低。

优化：

-- 1、新增后缀字段（截取SKU后4位）
ALTER TABLE goods ADD sku_suffix CHAR(4) GENERATED ALWAYS AS (RIGHT(sku, 4)) STORED;

-- 2、我们建“前缀+后缀”组合索引
CREATE INDEX idx_goods_sku_combo ON goods(LEFT(sku, 8), sku_suffix);  -- 前缀8位（AB-2024-）+后缀4位

-- 3、查询时同时匹配前缀和后缀（命中组合索引）
SELECT * FROM goods WHERE LEFT(sku, 8) = 'AB-2024-' AND sku_suffix = '1234';

32、避免索引“最左前缀”失效：拆分联合索引适配多查询

若多个查询的“最左前缀”不同（如：查询1用a过滤，查询2用a+b过滤，查询3用b过滤），单一联合索引无法覆盖，我们需拆分索引适配场景。

分析：

查询1：SELECT * FROM t WHERE a = 1（最左前缀为a）；
查询2：SELECT * FROM t WHERE a = 1 AND b = 2（最左前缀为a）；
查询3：SELECT * FROM t WHERE b = 2（最左前缀为b）。

优化：

-- 1、建联合索引(a,b)：覆盖查询1和查询2（查询2可命中全索引，查询1命中前缀a）
CREATE INDEX idx_t_a_b ON t(a, b);

-- 2、建单列索引(b)：覆盖查询3（避免全表扫描）
CREATE INDEX idx_t_b ON t(b);

注意：

避免过度拆分（如：为每个查询建单列索引），需平衡查询效率与写性能；
优先保证高频查询的索引覆盖。

33、前缀索引扩展：用“双前缀索引”解决低选择性问题

当单一前缀索引选择性不足（如：大量字符串前N位一样），我们可创建“短前缀+长前缀”的双前缀索引，通过两次过滤提升效率。

示例：商品编号格式为“PROD-2024-XXXXXX”（如：PROD-2024-123456、PROD-2024-654321），前8位“PROD-2024”完全一样，单一前缀索引选择性为0（无效）。

优化：

-- 1、建双前缀索引：短前缀（8位，快速过滤一样前缀）+ 长前缀（14位，精准匹配）
CREATE INDEX idx_goods_prod_short ON goods(goods_no(8));  -- 短前缀：匹配"PROD-2024"
CREATE INDEX idx_goods_prod_long ON goods(goods_no(14));  -- 长前缀：匹配完整"PROD-2024-XXXXXX"

-- 2、查询时我们先用短前缀过滤，再用长前缀精准匹配（MySQL会自动选择更优索引，或通过FORCE INDEX指定）
SELECT goods_name FROM goods 
FORCE INDEX(idx_goods_prod_long)  -- 直接用长前缀索引精准命中
WHERE goods_no = 'PROD-2024-123456';

-- 3、范围查询时，短前缀索引先过滤大部分数据
SELECT goods_name FROM goods 
WHERE goods_no LIKE 'PROD-2024-12%'  -- 短前缀先过滤出"PROD-2024"的数据，再匹配后续

34、外键索引：强制关联字段建索引，避免关联查询慢

多表关联时，若外键字段未建索引，关联查询会全表扫描外键表，我们需强制为外键字段建索引（即使MySQL未自动创建）。

示例：订单-用户关联

-- 优化前：orders表的user_id（外键）未建索引，关联慢
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)  -- 外键未自动建索引（部分MySQL版本默认不建）
);

-- 关联查询时，orders表全表扫描
SELECT o.order_id, u.username 
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.username = 'Jerry';

-- 优化后：给外键字段建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 关联查询命中索引，效率提升
SELECT o.order_id, u.username 
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.username = 'Jerry';

注意：

InnoDB中，若外键字段是主键或唯一索引，则会自动建索引；否则需手动创建；
外键索引不仅优化关联查询，还能加速外键约束的检查（如：删除主表数据时的关联校验）。

35、索引合并：利用MySQL自动索引合并，适配多单列索引场景

当查询条件包含多个独立字段（无联合索引），MySQL会自动触发索引合并（Index Merge），同时使用多个单列索引提升效率（替代低效的全表扫描）。

多条件无联合索引：

-- 1、建两个单列索引（无联合索引）
CREATE INDEX idx_customer_age ON customer(user_age);
CREATE INDEX idx_customer_city ON customer(user_city);

-- 2、查询时，MySQL自动合并两个索引（Extra字段显示"Using index condition; Using union"）
SELECT username FROM customer 
WHERE user_age = 28 OR user_city = 'Shanghai';

索引合并的三种类型：

1、Union合并：用于OR条件（如：age=28 OR city=’Shanghai’）；
2、Intersection合并：用于AND条件（如：age=28 AND city=’Shanghai’，效率低于联合索引）；
3、Sort-Union合并：用于OR条件，但索引结果需排序。

注意：

索引合并是“临时方案”，优先还是建联合索引（效率更高）；
若合并的索引过多（如：超过3个），优化器可能放弃合并，转为全表扫描，我们需控制单列索引数量。

36、避免“空值”陷阱：索引不存NULL，用默认值替代

InnoDB索引不存储NULL值，若查询条件为field IS NULL，索引无法命中；我们提议给字段设默认值（如：空字符串、0），避免NULL。

NULL值，索引失效：

-- user_email允许NULL，查询IS NULL时无索引可用
CREATE INDEX idx_customer_email ON customer(user_email);
SELECT * FROM customer WHERE user_email IS NULL;  -- 全表扫描

设默认值，索引生效：

-- 1、ALTER TABLE修改字段默认值为空字符串
ALTER TABLE customer MODIFY COLUMN user_email VARCHAR(100) DEFAULT '';

-- 2、查询默认值，命中索引
SELECT * FROM customer WHERE user_email = '';

37、中间模糊查询（%xxx%）：用“倒排索引表”替代LIKE

对于“中间模糊查询”（如：goods_name LIKE ‘%华为手机%’），普通索引和前缀索引均失效，全表扫描效率极低，我们可采用“倒排索引表”方案：提前拆分字段中的关键词，建立关键词与记录的映射关系，实现高效模糊查询。

商品名称中间模糊查询：

-- 场景：goods表的goods_name字段需支持“中间模糊查询”（如：查含“华为手机”的商品）
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT,
    goods_name VARCHAR(100)  -- 如：“华为Mate60手机”“荣耀华为合作款”
);

-- 优化：建倒排索引表存储关键词映射
-- 1、建倒排索引表（关键词→商品ID映射）
CREATE TABLE goods_inverted_index (
    keyword VARCHAR(50),
    goods_id INT,
    PRIMARY KEY (keyword, goods_id),  -- 复合主键，避免重复映射
    INDEX idx_inverted_goods_id (goods_id)
);

-- 2、插入商品时同步生成关键词（可通过程序或触发器实现，此处以手动为例）
-- 例：商品“华为Mate60手机”拆分关键词：华为、Mate60、手机、华为Mate60、Mate60手机
INSERT INTO goods_inverted_index (keyword, goods_id)
VALUES ('华为', 1), ('Mate60', 1), ('手机', 1), ('华为Mate60', 1), ('Mate60手机', 1);

-- 3、中间模糊查询（通过关键词匹配实现，无需全表扫描）
-- 例：查含“华为手机”的商品（匹配关键词“华为”和“手机”的交集）
SELECT g.goods_id, g.goods_name
FROM goods g
JOIN goods_inverted_index i1 ON g.goods_id = i1.goods_id
JOIN goods_inverted_index i2 ON g.goods_id = i2.goods_id
WHERE i1.keyword = '华为' AND i2.keyword = '手机';

优势：

中间模糊查询效率从“全表扫描”提升为“索引匹配”，百万级数据查询时间从秒级降至毫秒级；
关键词拆分规则可按需调整（如：支持拼音、缩写），适配更多查询场景。

38、前缀索引区分度不足：用“哈希+前缀”组合索引提升区分度

当长字符串的前缀区分度极低（如：大量字符串前10位完全一样），单一前缀索引无法有效过滤数据，我们可结合“哈希值”和“前缀”建立组合索引，大幅提升区分度。

设备SN码前缀区分度不足优化：

-- 场景：设备SN码格式为“SN-2024-XXXX-YYYY”，前8位“SN-2024-”完全一样，前缀索引区分度为0
CREATE TABLE devices (
    device_id INT PRIMARY KEY AUTO_INCREMENT,
    sn_code VARCHAR(20)  -- 如：SN-2024-1234-ABCD、SN-2024-5678-EFGH
);

-- 优化前：单一前缀索引无效
CREATE INDEX idx_sn_prefix ON devices(sn_code(8));  -- 前8位全一样，查询时匹配所有数据
SELECT * FROM devices WHERE sn_code LIKE 'SN-2024-1234%';  -- 需扫描全表匹配的记录

-- 优化后：哈希+前缀组合索引
-- 1、新增SN码的哈希字段（用CRC32生成短哈希值，区分度高）
ALTER TABLE devices ADD sn_hash INT GENERATED ALWAYS AS (CRC32(sn_code)) STORED;

-- 2、建“哈希+前缀”组合索引（哈希先过滤，前缀再精准匹配）
CREATE INDEX idx_sn_hash_prefix ON devices(sn_hash, sn_code(12));

-- 3、查询时先匹配哈希值（快速过滤大部分数据），再匹配前缀
SELECT * FROM devices 
WHERE sn_hash = CRC32('SN-2024-1234-ABCD') AND sn_code LIKE 'SN-2024-1234%';

优势：

哈希值先过滤掉99%以上的不匹配数据，再用前缀索引精准匹配，查询效率提升100倍以上；
CRC32生成的哈希值仅4字节，索引体积小，IO开销低。

39、临时表+索引：优化复杂统计查询

对于多表关联的复杂统计（如：“按用户、月份统计订单量”），直接查询效率低，我们可先用临时表存储中间结果，再建索引加速统计。

复杂关联慢：

-- 直接关联用户表、订单表统计，多次扫描大表
SELECT u.user_id, DATE_FORMAT(o.order_time, '%Y-%m') AS month, COUNT(o.order_id) AS order_count
FROM customer u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.user_status = 'active'
GROUP BY u.user_id, month;

临时表+索引：

-- 1、建临时表存储用户订单中间数据
CREATE TEMPORARY TABLE tmp_user_orders (
    user_id INT,
    month CHAR(7),
    order_id BIGINT,
    INDEX idx_tmp_user_month (user_id, month)  -- 建联合索引加速分组
);

-- 2、插入中间数据（仅扫描一次订单表）
INSERT INTO tmp_user_orders (user_id, month, order_id)
SELECT o.user_id, DATE_FORMAT(o.order_time, '%Y-%m'), o.order_id
FROM orders o
JOIN customer u ON o.user_id = u.user_id
WHERE u.user_status = 'active';

-- 3、基于临时表统计（索引命中，分组效率提升）
SELECT user_id, month, COUNT(order_id) AS order_count
FROM tmp_user_orders
GROUP BY user_id, month;

优势：

临时表隔离中间计算，避免重复扫描大表；
临时表索引仅在会话内有效，不影响主表写性能。

40、临时统计查询：用“内存表+索引”替代临时表，加速临时计算

当执行临时统计查询（如：“实时计算今日TOP10热销商品”）时，普通临时表（磁盘存储）效率低，我们可改用内存表（MEMORY引擎） 存储中间数据并建索引，利用内存IO优势提升临时计算速度。

示例：实时热销商品统计

-- 场景：临时统计今日（2024-09-04）销量TOP10的商品，涉及订单表与商品表关联
-- 优化前：用普通临时表（磁盘存储），统计耗时5秒
CREATE TEMPORARY TABLE tmp_daily_sales (
    goods_id INT,
    sales_count INT,
    sales_amount DECIMAL(10,2)
);

-- 插入今日销售数据（磁盘写入慢）
INSERT INTO tmp_daily_sales (goods_id, sales_count, sales_amount)
SELECT o.goods_id, COUNT(o.order_id), SUM(o.goods_price)
FROM order_items o
JOIN orders ord ON o.order_id = ord.order_id
WHERE ord.order_time >= '2024-09-04 00:00:00' AND ord.order_time < '2024-09-05 00:00:00'
GROUP BY o.goods_id;

-- 统计TOP10（磁盘查询慢）
SELECT g.goods_name, t.sales_count
FROM tmp_daily_sales t
JOIN goods g ON t.goods_id = g.goods_id
ORDER BY t.sales_count DESC LIMIT 10;

-- 优化后：用内存表+索引，统计耗时降至0.5秒
-- 1、建内存表（MEMORY引擎，数据存内存）并建索引
CREATE TEMPORARY TABLE tmp_daily_sales_mem (
    goods_id INT,
    sales_count INT,
    sales_amount DECIMAL(10,2),
    INDEX idx_sales_count (sales_count)  -- 建索引加速排序
) ENGINE=MEMORY;

-- 2、插入今日销售数据（内存写入快，比磁盘快10倍+）
INSERT INTO tmp_daily_sales_mem (goods_id, sales_count, sales_amount)
SELECT o.goods_id, COUNT(o.order_id), SUM(o.goods_price)
FROM order_items o
JOIN orders ord ON o.order_id = ord.order_id
WHERE ord.order_time >= '2024-09-04 00:00:00' AND ord.order_time < '2024-09-05 00:00:00'
GROUP BY o.goods_id;

-- 3、统计TOP10（索引命中+内存查询，速度极快）
SELECT g.goods_name, t.sales_count
FROM tmp_daily_sales_mem t
JOIN goods g ON t.goods_id = g.goods_id
ORDER BY t.sales_count DESC LIMIT 10;

优势：

内存表读写速度比普通临时表快10-100倍，适合临时高频计算；
内存表索引同样存于内存，排序、过滤效率远超磁盘索引。

注意：

内存表数据在会话结束或数据库重启后丢失，仅适合临时计算；
内存表大小受max_heap_table_size参数限制，需提前调整（如：设为1G）。

七、读写平衡与批量场景：平衡性能与效率

在“高频更新、批量操作、读写分离”等场景下，避免索引成为“性能瓶颈”，平衡读写效率。

41、高频更新字段：避免建索引，用“延迟索引”平衡读写

对于高频更新的字段（如：商品库存、用户在线状态，每秒更新数十次），建索引会导致每次更新都需同步维护索引，严重拖慢写性能，我们可采用“延迟索引”方法：不直接建索引，而是通过“辅助表+定时同步”间接实现查询优化。

示例：商品库存查询优化

-- 场景：goods表的stock字段每秒更新50次，直接建索引会拖慢写性能
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT,
    goods_name VARCHAR(100),
    stock INT  -- 高频更新字段，不建索引
);

-- 优化：建辅助表存储需查询的库存数据，定时同步
-- 1、建辅助表（仅存储需查询的字段，建索引）
CREATE TABLE goods_stock_index (
    goods_id INT PRIMARY KEY,
    stock INT,
    INDEX idx_goods_stock (stock)  -- 库存查询索引
);

-- 2、定时同步主表数据到辅助表（用定时任务如：crontab+SQL，间隔1-5秒，根据查询实时性调整）
REPLACE INTO goods_stock_index (goods_id, stock)
SELECT goods_id, stock FROM goods;

-- 3、查询库存时查辅助表（读性能不受主表更新影响）
-- 例：查库存小于10的商品ID
SELECT goods_id FROM goods_stock_index WHERE stock < 10;
-- 如需商品详情，再关联主表（仅关联少量数据，效率高）
SELECT g.goods_name, gsi.stock 
FROM goods g
JOIN goods_stock_index gsi ON g.goods_id = gsi.goods_id
WHERE gsi.stock < 10;

优势：

主表写性能不受索引影响，更新效率提升5-10倍；
辅助表同步间隔可按需调整，平衡“实时性”与“写性能”。

42、历史数据归档：拆分表+独立索引，减轻主表压力

对于订单表、日志表等大表，历史数据（如：1年前的订单）查询频率低，但占用大量空间，导致主表索引体积过大、查询变慢，我们可采用“表拆分+归档索引”方案：将历史数据迁移到归档表，主表仅保留近期数据，各自维护独立索引。

订单表历史数据归档：

-- 1、主表：仅保留近1年订单，索引体积小
CREATE TABLE orders_current (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_time DATETIME,
    INDEX idx_orders_user_time (user_id, order_time)  -- 小体积索引，查询快
);

-- 2、归档表：存储1年以上历史订单，独立索引
CREATE TABLE orders_archive (
    order_id BIGINT PRIMARY KEY,
    user_id INT,
    order_time DATETIME,
    INDEX idx_orders_archive_user_time (user_id, order_time)  -- 归档数据专用索引
);

-- 3、定时归档（如：每月1号迁移上月数据到归档表）
INSERT INTO orders_archive (order_id, user_id, order_time)
SELECT order_id, user_id, order_time FROM orders_current
WHERE order_time < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- 4、删除主表已归档数据
DELETE FROM orders_current WHERE order_time < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

-- 5、查询方案：
-- 查近期订单（主表，快）
SELECT * FROM orders_current WHERE user_id = 501 AND order_time > '2024-01-01';
-- 查历史订单（归档表，无主表压力）
SELECT * FROM orders_archive WHERE user_id = 501 AND order_time < '2023-01-01';
-- 查全量订单（主表+归档表联合查询，用UNION ALL）
SELECT * FROM orders_current WHERE user_id = 501
UNION ALL
SELECT * FROM orders_archive WHERE user_id = 501;

优势：

主表索引体积减少70%+，查询速度显著提升；
归档表可单独部署在低配置服务器，降低主库资源占用。

43、小表大索引：删除冗余索引，用“主键覆盖”替代

对于小表（行数<1万），若索引体积接近甚至超过表数据体积（如：单表5000行，索引占10MB，表数据仅8MB），索引反而会增加IO开销（查询时需同时加载表和索引），我们可删除冗余索引，利用“主键覆盖”优化查询。

小表索引优化：

-- 优化前：小表建了多个冗余索引，索引体积过大
CREATE TABLE dict_city (
    city_id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
    city_name VARCHAR(50),
    province VARCHAR(50),
    INDEX idx_city_name (city_name),  -- 冗余索引：小表查询直接扫主键更快
    INDEX idx_city_province (province)  -- 冗余索引
);

-- 查询时加载索引+表数据，IO开销大
SELECT city_id FROM dict_city WHERE city_name = 'Shanghai';

-- 优化后：删除冗余索引，利用主键覆盖查询
-- 1、删除冗余索引
DROP INDEX idx_city_name ON dict_city;
DROP INDEX idx_city_province ON dict_city;

-- 2、查询优化：
-- 场景1：查单条数据（主键索引直接命中，无需索引）
SELECT * FROM dict_city WHERE city_id = 10;

-- 场景2：查多条数据（小表全表扫描+主键排序，效率高于索引查询）
SELECT city_id, city_name FROM dict_city WHERE province = 'Guangdong';

底层逻辑：

小表全表扫描的IO开销（加载8MB数据）远低于“加载索引（10MB）+ 回表（8MB）”的开销；
主键是聚簇索引，表数据按主键有序存储，查询后无需额外排序，效率更高。

44、批量操作优化：临时禁用索引，提升批量插入/更新效率

当执行批量操作（如：批量导入10万条数据、批量更新 thousands 行记录）时，每条记录都会触发索引维护（更新B+树结构），导致操作缓慢，我们可临时禁用非主键索引，完成后再重建，大幅提升批量操作效率。

示例：批量导入商品数据

-- 场景：goods表有3个非主键索引，批量导入10万条商品数据时速度慢
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT,
    goods_name VARCHAR(100),
    goods_price DECIMAL(10,2),
    category_id INT,
    INDEX idx_goods_name (goods_name),
    INDEX idx_goods_price (goods_price),
    INDEX idx_goods_category (category_id)
);

-- 优化前：直接批量导入，每条记录维护3个索引，耗时10分钟
LOAD DATA INFILE '/tmp/goods_data.csv' INTO TABLE goods FIELDS TERMINATED BY ',';

-- 优化后：临时禁用非主键索引，导入后重建
-- 1、禁用非主键索引（仅保留主键索引，InnoDB主键索引无法禁用）
ALTER TABLE goods DISABLE KEYS;

-- 2、批量导入数据（无需维护非主键索引，耗时降至1分钟）
LOAD DATA INFILE '/tmp/goods_data.csv' INTO TABLE goods FIELDS TERMINATED BY ',';

-- 3、重建非主键索引（一次性维护，比逐行维护快）
ALTER TABLE goods ENABLE KEYS;

注意：

DISABLE KEYS/ENABLE KEYS仅对MyISAM和InnoDB的非主键索引有效，主键索引始终启用；
批量更新/删除场景同理：先禁用索引，执行批量操作，再重建索引。

45、唯一索引冲突优化：用“INSERT … ON DUPLICATE KEY UPDATE”替代“先查后插”

当插入数据时需避免唯一索引冲突（如：用户手机号唯一），“先查询是否存在→再插入”会产生竞态问题且效率低，我们可直接用INSERT … ON DUPLICATE KEY UPDATE，利用唯一索引的冲突检测机制，一步完成“插入/更新”，效率更高。

示例：用户手机号唯一冲突处理

-- 1、建用户表，手机号唯一索引
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    phone VARCHAR(20) UNIQUE,  -- 唯一索引
    username VARCHAR(50),
    avatar VARCHAR(255)
);

-- 优化前：先查后插，效率低且有竞态问题
-- 步骤1：查询手机号是否存在
SELECT user_id FROM users WHERE phone = '13800138000';
-- 步骤2：不存在则插入，存在则更新（并发时可能重复插入导致唯一冲突）
INSERT INTO users (phone, username) VALUES ('13800138000', 'Tom') ON DUPLICATE KEY UPDATE username = 'Tom';

-- 优化后：直接用唯一索引冲突检测，一步完成
-- 若phone不存在则插入，存在则更新username和avatar（利用唯一索引快速判断）
INSERT INTO users (phone, username, avatar) 
VALUES ('13800138000', 'Tom', 'avatar1.jpg') 
ON DUPLICATE KEY UPDATE username = 'Tom', avatar = 'avatar1.jpg';

优势：

减少一次查询，效率提升50%+；
利用MySQL唯一索引的原子性冲突检测，避免并发场景下的竞态问题。

46、读写分离场景：从库单独加“查询专用索引”，不影响主库写性能

在读写分离架构中，主库负责写入，从库负责查询。若主库因写压力无法加查询索引（如：联合索引会拖慢插入），我们可在从库单独创建查询专用索引，既优化从库查询，又不影响主库写性能。

示例：电商订单读写分离优化

-- 架构：主库（master）负责订单插入，从库（slave）负责订单查询分析
-- 1、主库表结构：仅保留必要索引（避免影响插入性能）
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_time DATETIME,
    order_amount DECIMAL(10,2),
    INDEX idx_orders_user_id (user_id)  -- 仅保留主库写入时需用到的索引
);

-- 2、从库单独加查询专用索引（不同步到主库）
-- 注意：需先关闭从库的SQL同步（临时），避免索引被主库结构覆盖
STOP SLAVE SQL_THREAD;

-- 从库新增“用户+时间”联合索引，优化从库的历史订单查询
CREATE INDEX idx_orders_user_time_slave ON orders(user_id, order_time);

-- 重启SQL同步（仅同步数据，不同步从库单独创建的索引）
START SLAVE SQL_THREAD;

-- 3、从库查询优化：命中专用索引，效率提升
-- 例：从库查询用户2024年的订单趋势
SELECT DATE_FORMAT(order_time, '%Y-%m'), SUM(order_amount)
FROM orders
WHERE user_id = 1001 AND order_time >= '2024-01-01'
GROUP BY DATE_FORMAT(order_time, '%Y-%m');

优势：

主库写性能不受额外索引影响，从库查询因专用索引大幅提速；
专用索引仅存在于从库，不占用主库存储资源。

注意：

从库单独加索引时需临时停止SQL同步，避免主库的DDL语句覆盖从库索引；
若主库执行表结构变更（如：ALTER TABLE），需重新在从库重建专用索引。

八、分布式与复杂场景：拓展索引能力

针对“分表、分布式、缓存协同”等复杂架构场景，解决跨节点、跨表的索引低效问题。

47、分布式分表场景：分表键+局部索引，避免跨表查询低效

在分布式分表（如：Sharding-JDBC分表）场景中，索引设计需绑定“分表键”，否则易触发跨表查询，我们需遵循“分表键优先+局部索引适配”原则，确保查询命中单表索引。

示例：订单表按用户ID哈希分表

-- 场景：用Sharding-JDBC将orders表按user_id哈希分为4张表（orders_0~orders_3）
-- 分表规则：user_id % 4 = 表后缀（如：user_id=10→orders_2，10%4=2）

-- 优化前：索引未绑定分表键，触发跨表查询
-- 每张分表仅建order_time索引，无分表键user_id
CREATE TABLE orders_0 (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_time DATETIME,
    INDEX idx_order_time (order_time)  -- 未包含分表键
);

-- 查询user_id=10的2024年订单：因索引无user_id，需扫描所有4张分表，效率极低
SELECT * FROM orders WHERE user_id=10 AND order_time>='2024-01-01';

-- 优化后：分表键+局部字段联合索引
-- 每张分表建“分表键user_id+查询字段order_time”联合索引
CREATE TABLE orders_0 (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_time DATETIME,
    INDEX idx_user_time (user_id, order_time)  -- 分表键放前面
);

-- 查询时：Sharding-JDBC通过user_id%4定位到orders_2，直接命中联合索引，无需跨表
SELECT * FROM orders WHERE user_id=10 AND order_time>='2024-01-01';

优化原则：

1、所有查询索引必须包含分表键：确保Sharding中间件能精准定位分表；
2、分表键放联合索引最左：符合最左匹配原则，同时适配分表定位；
3、避免非分表键的范围查询：如：order_time>=’2024-01-01’需跟在分表键后，否则索引失效。

48、索引与缓存协同：缓存“索引过滤后的数据”，减少重复计算

高频查询场景中，若每次都通过索引过滤数据，仍会消耗数据库CPU，我们可将“索引过滤后的小结果集”缓存到Redis，让查询先查缓存，未命中再查数据库索引，减轻数据库压力。

示例：商品分类列表查询

-- 场景：高频查询“分类ID=10的上架商品列表”，每日查询量10万+
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    goods_name VARCHAR(100),
    status TINYINT,  -- 1=上架，0=下架
    INDEX idx_category_status (category_id, status)  -- 联合索引
);

-- 优化前：每次查询都命中索引，但数据库压力大
SELECT goods_id, goods_name FROM goods WHERE category_id=10 AND status=1;

-- 优化后：索引+Redis缓存协同
-- 1、首次查询：数据库命中idx_category_status索引，返回结果集
SELECT goods_id, goods_name FROM goods WHERE category_id=10 AND status=1;

-- 2、将结果集缓存到Redis（key=goods:category:10，value=JSON数组）
-- 3、后续查询：先查Redis，未命中再查数据库索引
-- 4、数据更新时：更新数据库后同步删除Redis缓存（避免脏数据）
--    例：商品下架时删除缓存
UPDATE goods SET status=0 WHERE goods_id=1001;
DELTE FROM Redis WHERE key='goods:category:10';

协同要点：

缓存结果集需满足“小而稳定”：结果集行数提议<1000，更新频率低（如：分类列表、字典数据）；
缓存键设计需关联索引条件：如：goods:category:10对应“category_id=10+status=1”的索引条件；
更新策略优先“删除缓存”：避免“更新数据库+更新缓存”的竞态问题。

49、关联查询优化：小表驱动大表+索引

多表关联时，遵循“小表驱动大表”原则（用小表结果集驱动大表查询），并在关联字段上建索引。

示例：

-- 场景：查询用户及其订单，customer是小表，orders是大表
-- 1、在关联字段customer_id上建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 2、小表驱动大表（INNER JOIN默认小表驱动大表，或用EXISTS明确）
SELECT c.username, o.order_id 
FROM customer c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.user_status = 'active';

50、索引失效特殊案例：警惕“字段隐式转换”的特殊场景

除了“varchar用数字查询”的常规隐式转换，还有一些特殊隐式转换会导致索引失效（如：datetime与timestamp互转、decimal与int互转），我们需重点规避。

示例1：datetime与timestamp隐式转换

-- 场景：orders表的order_time为datetime类型，建索引后用timestamp值查询
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_time DATETIME,  -- datetime类型
    INDEX idx_order_time (order_time)
);

-- 优化前：用timestamp值查询，触发隐式转换（datetime→timestamp），索引失效
SELECT * FROM orders WHERE order_time = UNIX_TIMESTAMP('2024-09-04 10:00:00');  -- 全表扫描

-- 优化后：类型一致，索引生效
-- 方法1：将查询值转为datetime
SELECT * FROM orders WHERE order_time = '2024-09-04 10:00:00';
-- 方法2：用FROM_UNIXTIME将timestamp转为datetime
SELECT * FROM orders WHERE order_time = FROM_UNIXTIME(UNIX_TIMESTAMP('2024-09-04 10:00:00'));

示例2：decimal与int隐式转换

-- 场景：goods表的goods_price为decimal(10,2)类型，建索引后用int值查询
CREATE TABLE goods (
    goods_id INT PRIMARY KEY AUTO_INCREMENT,
    goods_price DECIMAL(10,2),  -- decimal类型
    INDEX idx_goods_price (goods_price)
);

-- 优化前：用int值查询，触发隐式转换（decimal→int），索引失效
SELECT * FROM goods WHERE goods_price = 99;  -- 全表扫描

-- 优化后：类型一致，索引生效
SELECT * FROM goods WHERE goods_price = 99.00;  -- 命中索引

规避原则：

查询值的类型必须与字段类型完全一致（包括精度、格式）；
若需转换，转换查询值而非字段（如：FROM_UNIXTIME(ts)而非UNIX_TIMESTAMP(dt)）。

MySQL索引优化没那么玄乎，实则就是我们要让数据库少跑腿、快干活。
第一设计时我们别瞎建索引，联合索引得按“最左匹配+字段区分度”排顺序，长字符串用前缀索引省空间，多条件查询优先用复合索引，单表索引尽量控制在5个以内，不然插入更新会变慢。
查数据时我们要避开“坑”：别在字段上套函数、别让类型不匹配（列如：字符串用数字查）、别用SELECT *，这些都会让索引失效。模糊查询别让“%”开头，大偏移量分页用主键定位或索引关联，排序就把过滤和排序字段放联合索引里，能避免额外排序。
特殊情况我们得对症：JSON数据上JSON索引，地理信息用空间索引，全文检索别用LIKE而是上FULLTEXT索引。大表建索引用在线DDL避免锁表，高频更新字段可以搞个辅助表延迟同步，平衡读写。
架构和维护我们也不能漏：读写分离就去从库加查询专用索引，不影响主库；批量操作前先临时禁用索引，干完再打开；定期清索引碎片、刷统计信息，用EXPLAIN盯着执行计划，发现没命中索引的查询及时调整。
总之，没有万能索引，关键是我们要结合业务场景，先定位问题，再落地测试，最后持续优化。