索引是MySQL性能优化的“金钥匙”，但很多开发者陷入了“盲目建索引”的误区：认为建的索引越多，查询越快，结果反而导致写入性能暴跌、索引失效频发；还有人掌握了聚簇索引与非聚簇索引的基础，却不知道如何落地到实际优化中，让索引真正发挥作用。

结合MySQL8的最新特性，本文将从“基础回顾→优化原则→实操方法→失效排查→实战案例”五个维度，手把手教你做好索引优化，既避开常见坑，又能针对性解决线上慢查询问题，不管是日常开发、SQL调优，还是面试备考，都能直接套用，让你的SQL查询提速10倍。

一、核心回顾：索引优化的前提，读懂两种核心索引

在深入优化之前，我们先快速回顾MySQL8中最核心的两种索引——聚簇索引与非聚簇索引，这是所有优化操作的基础，也是避免踩坑的关键（详细原理可参考前文《MySQL8聚簇索引与非聚簇索引深度解析》）。

• 聚簇索引：InnoDB的灵魂设计，“索引即数据，数据即索引”，叶子节点存储完整行数据，主键查询无需回表，效率最高；必须有且仅有一个，优先由主键充当，无主键时MySQL8会隐式生成6字节rowid。

• 非聚簇索引：也称二级索引，索引与数据分离，叶子节点存储聚簇索引键（主键），普通查询需回表；可创建多个，核心优化点是“覆盖索引”，避免回表开销。

核心结论：索引优化的本质，是让MySQL“少走弯路”——尽量通过索引快速定位数据，减少磁盘IO，避免全表扫描；而优化的核心逻辑，就是“选对索引类型、建对索引结构、避开失效场景”。

二、MySQL8索引优化核心原则：3个“不”+3个“优先”

索引优化不是“盲目建索引”，而是“精准建索引”。结合MySQL8的特性，总结6个核心原则，覆盖90%的业务场景，帮你从源头避开优化误区。

（一）3个“不”：避开索引优化的致命坑

1. 不建冗余索引：避免重复索引（如对name建普通索引后，再建唯一索引）、无用索引（很少用到的查询条件无需建索引）。索引越多，写入（插入/更新/删除）时同步更新索引的开销越大，还可能导致优化器判断失误，选择错误索引。MySQL8中，单表非聚簇索引建议不超过5个。

2. 不建过长索引：索引字段长度越长，非叶子节点存储的索引项越少，B+树高度越高，磁盘IO次数越多。比如用32位UUID作为主键（聚簇索引），会导致所有非聚簇索引的叶子节点占用更多空间，查询效率下降；优先用自增INT/BIGINT作为主键。

3. 不依赖无序索引：聚簇索引的排序决定数据物理存储顺序，若主键是UUID、随机字符串等无序值，插入数据时会频繁导致B+树节点分裂，写入变慢、产生碎片；非聚簇索引也需避免无序字段，否则索引排序无意义，查询效率低下。

（二）3个“优先”：精准提升索引效率

1. 优先用自增主键：自增主键是聚簇索引的最优选择，插入数据时按物理顺序连续存储，无需调整B+树结构，写入性能高、不易产生碎片；同时短小的自增ID，能减少非聚簇索引的存储空间，降低B+树高度。

2. 优先设计覆盖索引：覆盖索引是优化非聚簇索引的“神器”——查询的所有字段，都包含在非聚簇索引的索引键中，无需回表，查询效率接近聚簇索引。比如查询name和age，可创建联合索引idx_age_name，而非单一索引idx_age。

3. 优先优化高频查询：索引优化需聚焦核心业务，优先给高频查询（如用户登录、商品列表、订单查询）建索引，低频查询无需浪费资源建索引；同时，高频查询的索引需优先保证“不失效”，避免全表扫描。

三、MySQL8索引优化实操方法：从设计到落地

掌握优化原则后，更重要的是落地到实际操作中。下面结合MySQL8的特性，讲解4个核心实操方法，每个方法都搭配实战示例，帮你快速上手。

方法1：主键与聚簇索引优化（MySQL8重点）

聚簇索引的性能直接决定整个表的查询效率，尤其是数据量较大时，主键设计的优劣的影响尤为明显。

实战优化方案：

• 最优方案：用自增BIGINT作为主键（适配大数据量，避免INT溢出），如id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY；

• 特殊场景：若业务需用UUID作为唯一标识，不建议用UUID作为主键，可将其作为普通字段，加唯一索引，主键仍用自增ID，示例： CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -- 聚簇索引 uuid VARCHAR(36) NOT NULL, name VARCHAR(50) NOT NULL, phone VARCHAR(11) NOT NULL, UNIQUE INDEX idx_user_uuid (uuid) -- 非聚簇索引，保证UUID唯一 );

• 避坑提醒：禁止用长字符串、随机值作为主键；若表无主键，MySQL8会隐式生成rowid，虽不影响使用，但建议手动定义自增主键，便于维护和优化。

方法2：非聚簇索引优化，善用覆盖索引与联合索引

非聚簇索引是高频查询优化的核心，重点在于“避免回表”和“合理设计联合索引”，结合MySQL8的联合索引优化特性，具体操作如下：

1. 覆盖索引实战（最常用）

场景：查询用户的name和phone，按age筛选，高频查询。

-- 错误写法：创建单一索引，查询需回表
CREATE INDEX idx_user_age ON user(age);
SELECT name, phone FROM user WHERE age > 18; -- 需回表，效率低

-- 正确写法：创建联合索引，覆盖查询字段，无需回表
CREATE INDEX idx_user_age_name_phone ON user(age, name, phone);
SELECT name, phone FROM user WHERE age > 18; -- 覆盖索引，效率提升5-10倍

关键提醒：覆盖索引的核心是“查询字段包含在索引键中”，无需包含所有字段，只需包含查询所需的字段即可，避免索引过长。

2. 联合索引设计（遵循左前缀原则）

MySQL8的联合索引遵循“左前缀原则”，即查询条件需从联合索引的最左列开始，否则索引失效。设计联合索引时，需遵循“高频查询字段放左边、区分度高的字段放左边”。

-- 场景：高频查询条件为age和name，偶尔单独查询age
-- 正确设计：联合索引 (age, name)，可适配两种查询
CREATE INDEX idx_user_age_name ON user(age, name);
SELECT * FROM user WHERE age = 18 AND name = '张三'; -- 用到完整索引
SELECT * FROM user WHERE age = 18; -- 用到索引的左前缀（age）

-- 错误设计：联合索引 (name, age)，单独查询age时索引失效
CREATE INDEX idx_user_name_age ON user(name, age);
SELECT * FROM user WHERE age = 18; -- 索引失效，全表扫描

补充：MySQL8支持“隐藏索引”，可用于测试联合索引的有效性——先将索引设为隐藏，观察查询性能变化，确认有效后再设为显示，避免误删有用索引。

方法3：索引失效排查与修复（MySQL8实战重点）

很多时候，我们建了索引，但查询依然卡顿，核心原因是“索引失效”。MySQL8中，可通过EXPLAIN 工具快速判断索引是否失效，再针对性修复。

1. 用EXPLAIN判断索引失效

执行EXPLAIN + 查询SQL，重点关注两个字段：

• type：访问类型，从好到差依次为 system > const > eq_ref > ref > range > index > ALL；若出现ALL（全表扫描），说明索引大概率失效。

• key：实际使用的索引，为NULL表示未用到索引，索引失效。

-- 示例：判断索引是否失效
EXPLAIN SELECT name, phone FROM user WHERE age > 18;
-- 若key为idx_user_age_name_phone，说明索引生效；若key为NULL，说明索引失效

2. 10种常见索引失效场景及修复方案（MySQL8最新）

结合MySQL8的特性，整理最常见的10种索引失效场景，每个场景搭配错误示例、失效原因和修复方案，帮你快速避坑：

1. 违反左前缀原则（联合索引） 错误示例：联合索引idx_a_b_c(a,b,c)，执行SELECT * FROM t WHERE b=2 失效原因：跳过联合索引最左列a，直接查询b，违反左前缀原则 修复方案：调整查询条件，包含最左列a（如WHERE a=1 AND b=2），或调整联合索引顺序

2. 索引列参与函数运算 错误示例：create_time有索引，执行SELECT * FROM order WHERE DATE(create_time) = '2026-04-04' 失效原因：对索引列使用函数，MySQL无法利用索引进行范围查找 修复方案：将函数运算转移到查询值上，SELECT * FROM order WHERE create_time >= '2026-04-04' AND create_time < '2026-04-05'

3. 类型隐式转换 错误示例：mobile是VARCHAR类型且有索引，执行SELECT * FROM user WHERE mobile = 13888888888 失效原因：字段类型（VARCHAR）与查询值类型（INT）不一致，MySQL进行隐式转换，导致索引失效 修复方案：查询值加上引号，与字段类型一致，SELECT * FROM user WHERE mobile = '13888888888'

4. LIKE以通配符%开头 错误示例：title有索引，执行SELECT * FROM article WHERE title LIKE '%MySQL' 失效原因：通配符在开头，MySQL无法利用索引进行前缀匹配 修复方案：避免通配符在开头，或用全文索引（MySQL8支持InnoDB全文索引）

5. 使用!=或<>操作符 错误示例：age有索引，执行SELECT * FROM user WHERE age != 18 失效原因：不等于操作符通常会导致索引失效，优化器可能选择全表扫描 修复方案：替换为WHERE age < 18 OR age > 18，可能触发索引扫描

6. OR连接的条件不是全部有索引 错误示例：age有索引，name无索引，执行SELECT * FROM user WHERE age=18 OR name='张三' 失效原因：OR前后有一个字段无索引，另一个字段的索引也会失效 修复方案：给name建索引，或用UNION替代OR

7. 索引列参与算术运算 错误示例：price有索引，执行SELECT * FROM product WHERE price * 0.8 > 100 失效原因：索引列参与计算，MySQL无法利用索引 修复方案：将运算转移到查询值，SELECT * FROM product WHERE price > 100 / 0.8

8. 使用NOT IN或NOT EXISTS 错误示例：执行SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM order) 失效原因：NOT IN/NOT EXISTS可能导致索引失效，尤其是子查询返回大量数据时 修复方案：用LEFT JOIN替代，SELECT u.* FROM user u LEFT JOIN order o ON u.id = o.user_id WHERE o.user_id IS NULL

9. 数据分布不均匀，优化器放弃索引 错误示例：status字段只有0和1，90%的数据为1，执行SELECT * FROM order WHERE status=1 失效原因：数据分布不均，优化器认为全表扫描比索引扫描更高效 修复方案：强制使用索引（FORCE INDEX(idx_status)），或重新设计字段

10. 查询字段包含NULL值，未处理 错误示例：name有索引，执行SELECT * FROM user WHERE name IS NULL 失效原因：MySQL中NULL值无法被索引有效匹配（InnoDB索引不存储NULL值） 修复方案：用默认值（如空字符串）替代NULL，或调整查询条件

方法4：索引维护与碎片优化（MySQL8特性）

索引使用一段时间后，会产生碎片（尤其是频繁插入、删除、更新的表），导致索引效率下降。MySQL8提供了多种索引维护方式，无需停机，即可优化索引碎片。

1. 查看索引碎片：通过INFORMATION_SCHEMA.TABLES 查看表的碎片情况，重点关注DATA_FREE字段（碎片空间）： SELECT TABLE_NAME, DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';

2. 优化索引碎片（MySQL8推荐）：

   1. 对于InnoDB表，使用ALTER TABLE 表名 ENGINE=InnoDB;（重建表，整理碎片，MySQL8中无需锁表，不影响读写）；

   2. 使用OPTIMIZE TABLE 表名;（适用于小表，会锁表，建议在低峰期执行）；

   3. 开启InnoDB自动碎片整理（MySQL8.0.23+支持），通过配置innodb_defragment = ON，自动整理索引碎片。

3. 定期清理无用索引：通过MySQL8的sys.schema_unused_indexes 视图，查询未使用的索引，定期清理，减少索引冗余： SELECT * FROM sys.schema_unused_indexes WHERE table_schema = '数据库名';

四、MySQL8索引优化实战案例：从慢查询到秒级响应

结合线上常见的3个业务场景，讲解索引优化的完整流程，从慢查询定位到优化落地，让你直观感受优化效果。

案例1：用户登录查询（高频核心场景）

场景描述

原SQL与索引

-- 原SQL
SELECT * FROM user WHERE phone = '13888888888';
-- 原索引：无（仅主键id，聚簇索引）

问题分析

优化方案

-- 优化：创建唯一覆盖索引，包含查询所需字段
CREATE UNIQUE INDEX idx_user_phone ON user(phone, id, name, password);
-- 优化后SQL（无需修改，自动使用索引）
SELECT * FROM user WHERE phone = '13888888888';

优化效果

案例2：商品列表查询（分页+筛选场景）

场景描述

原SQL与索引

-- 原SQL
SELECT id, name, price, category_id FROM product WHERE category_id = 10 ORDER BY price DESC LIMIT 1000, 20;
-- 原索引：idx_category_id（单一索引）

问题分析

优化方案

-- 优化：创建联合覆盖索引（筛选字段+排序字段+查询字段）
CREATE INDEX idx_category_price ON product(category_id, price DESC, id, name);
-- 优化后SQL（无需修改）
SELECT id, name, price, category_id FROM product WHERE category_id = 10 ORDER BY price DESC LIMIT 1000, 20;

优化效果

案例3：订单范围查询（时间筛选场景）

场景描述

原SQL与索引

-- 原SQL
SELECT id, order_no, user_id, amount FROM order WHERE DATE(create_time) BETWEEN '2026-03-01' AND '2026-03-31';
-- 原索引：idx_create_time（单一索引）

问题分析

优化方案

-- 优化1：调整SQL，避免函数运算
SELECT id, order_no, user_id, amount FROM order WHERE create_time >= '2026-03-01 00:00:00' AND create_time < '2026-04-01 00:00:00';
-- 优化2：创建覆盖索引
CREATE INDEX idx_create_time_cover ON order(create_time, id, order_no, user_id, amount);

优化效果

五、MySQL8索引优化进阶：这些特性你必须掌握

除了基础优化，MySQL8还提供了多个索引相关的新特性，合理利用这些特性，能进一步提升索引效率，简化优化操作。

1. 隐藏索引（Invisible Index）：可将索引设为隐藏（ALTER TABLE 表名 ALTER INDEX 索引名 INVISIBLE;），隐藏后优化器不会使用该索引，但索引仍会同步更新；用于测试索引有效性，避免误删有用索引，测试完成后可设为显示（VISIBLE）。

2. 直方图（Histogram）：MySQL8引入直方图统计信息，优化器可通过直方图更精准地获取数据分布，避免因统计信息过时导致的索引选择失误；可通过ANALYZE TABLE 表名 UPDATE HISTOGRAM ON 字段名; 更新直方图。

3. InnoDB全文索引：MySQL8支持InnoDB表的全文索引，解决LIKE通配符开头导致的索引失效问题，适用于模糊查询场景（如文章标题、内容搜索），创建语法：CREATE FULLTEXT INDEX idx_article_title ON article(title);。

4. 索引合并（Index Merge）：当查询条件包含多个索引字段时，MySQL8会自动合并多个索引的结果，提升查询效率；无需手动干预，优化器会自动判断是否启用。

六、核心总结：索引优化的本质是“顺势而为”

MySQL8索引优化，从来不是“建越多索引越好”，而是“顺势而为”——顺应InnoDB的存储原理，顺应SQL的查询逻辑，顺应业务的访问场景，才能让索引真正发挥作用。

总结3个核心要点，帮你快速掌握索引优化的精髓：

1. 基础是前提：读懂聚簇索引与非聚簇索引的差异，选对主键类型，避免从源头踩坑；

2. 实操是核心：善用覆盖索引、联合索引，避开10种索引失效场景，定期维护索引碎片；

3. 实战是关键：结合业务场景，用EXPLAIN定位问题，用案例验证优化效果，避免纸上谈兵。

最后提醒：索引优化是一个持续迭代的过程，不是一次优化就能一劳永逸。随着数据量的增长、业务场景的变化，需要定期排查慢查询、优化索引结构，才能保证MySQL的性能稳定。

如果在索引优化、慢查询排查中遇到问题，欢迎留言讨论，一起交流优化技巧，避开那些年我们踩过的索引坑！