写过太多慢查询优化，见过太多因为索引没建对导致全表扫描把数据库拖垮的案例。MySQL索引这个东西，说简单就一个B+树，说复杂能牵扯出回表、覆盖索引、最左前缀、索引下推一堆概念。但实际开发中真正需要掌握的，无非就是搞清楚索引底层怎么工作，遇到具体业务怎么写SQL才能走索引，以及索引失效的常见坑怎么避开。
这篇文章围绕 MySQL 索引，从数据结构、聚簇索引与非聚簇索引的区别，到联合索引设计、排序分组优化、索引失效场景、慢查询排查，最后用一组典型面试题收尾。适合刚接触数据库索引的开发者系统建立认知，也适合有几年经验但没系统梳理过索引原理的朋友查漏补缺。内容全部基于我自己实际排查线上慢查询和优化接口性能的经验，代码和案例都可以直接参考。
1. 索引的底层存储结构：为什么是B+树而不是别的
1.1 从二叉树到B+树的演进逻辑
很多人刚开始学索引，第一反应是“索引就是一棵树”。话没错，但不准确。索引的底层数据结构经历了从二叉搜索树、平衡二叉树、B树到B+树的演进。为什么最终的答案是B+树，核心原因是磁盘IO的成本太高。
二叉搜索树在数据量大了之后会退化成链表，查找效率直接变成O(n)。平衡二叉树（AVL树）通过旋转保持左右子树高度差不超过1，查找效率稳定在O(log n)，但每个节点只存一个数据，树的高度会随着数据量增长而快速变高。假设一张表有1000万条数据，平衡二叉树的高度大约是24层左右，极端情况下要读24次磁盘，每次磁盘IO大概10毫秒，光这棵树的查找就要240毫秒，这还没算上回表读取实际数据的开销。
B树允许每个节点存储多个键值，树的高度压缩到3到4层。但B树有个问题：所有节点都存储数据，范围查找时需要中序遍历，而且中间节点存储数据导致单个节点能容纳的键值数量减少。
B+树的改进在于两点：第一，只有叶子节点存储数据，非叶子节点只存键值，这样单个节点能容纳的键值更多，树更矮；第二，叶子节点之间通过双向链表连接，范围查找时定位到起始位置后，直接沿着链表顺序扫描就行，不需要反复回溯父节点。
提示：InnoDB 的默认页大小是 16KB，假设主键是 BIGINT 类型占 8 字节，加上指针等开销，一个非叶子节点大约能存放 1000 个左右的键值。三层高的 B+ 树大约能存储 1000 × 1000 × 16KB/行大小 的数据，如果一行数据约 1KB，那就是大约 1000 万到 2000 万行。这也是为什么三到四层的 B+ 树能轻松支撑千万级数据量的核心原因。
1.2 InnoDB的聚簇索引与非聚簇索引
InnoDB 存储引擎里，主键索引就是聚簇索引，数据行实际存储在叶子节点上。也就是说，找到了主键索引，就等于找到了整行数据，不需要额外回表。这也是主键查找最快的原因。
二级索引（也叫辅助索引、普通索引、非聚簇索引）的叶子节点存储的是索引列的值加上主键值。举个例子，你在 name 字段上建了索引，那么这棵 B+ 树的叶子节点存的就是 name 和对应的主键 id。当你用 WHERE name = '张三' 查询时，首先扫描二级索引树，找到 name 对应的主键值，再用主键值去聚簇索引树里查整行数据，这个过程就叫回表。
MyISAM 引擎则是彻底的堆表结构，索引和数据分开存放，索引叶子节点存储的是数据行的物理地址，查一次索引就能定位到数据，严格来说不需要回表，但它不直接存数据行，所以要额外读一次数据文件。InnoDB 的聚簇结构在大多数场景下表现更好，因为主键查询只走一棵树，而且数据按主键顺序物理排列，范围查询的局部性更好。
说到这必须提一个多年踩坑得出的结论：尽量让主键保持自增或者趋势递增，不要用 UUID 这类随机值做主键。因为聚簇索引的叶子节点按主键顺序排列，随机主键会导致 B+ 树频繁分裂、页碎片化严重，插入性能会明显下降。我见过有人用 UUID 做主键，结果插入速度从每秒几千掉到几百，重建表之后恢复。
1.3 二级索引回表与覆盖索引的取舍
回表伤不伤性能，关键看回表次数和命中率。如果你 WHERE 条件命中了二级索引，返回结果集有 100 行，每行都要回表读一次主键索引，最坏情况下就是 100 次随机 IO，这在机械硬盘上就是灾难，SSD 上还好一点，但也不能掉以轻心。
覆盖索引就是让查询的所有字段都命中了同一个二级索引的叶子节点，不需要回表。比如表里只有 id、name、age 三个字段，你建了一个联合索引 (name, age)，然后执行 SELECT name, age FROM user WHERE name = '张三'，此时二级索引的叶子节点已经有 name 和 age，不需要再回到主键索引取数据，这就是一次覆盖索引扫描，性能比回表高很多。
可是要注意，覆盖索引不是万能的。索引字段越多，占用的存储空间越大，写入和更新时的维护成本越高。如果业务查询经常需要 SELECT * 返回所有字段，覆盖索引覆盖不了，所以没必要为了覆盖率把所有字段都塞进索引里，关键还是分析高频查询的 SELECT 字段清单，针对性地设计覆盖索引。
2. 联合索引设计与最左前缀规则
2.1 联合索引的匹配顺序：一个电话簿的比喻
联合索引的匹配规则，本质就是字典序。就好比电话簿先按姓氏排序，再按名字排序。你要找“张三”，必须先通过“张”定位到姓氏为张的区域，再在里面找“三”。你要直接找名字叫“三”的人，电话簿就帮不上忙，只能从头翻。
联合索引 (a, b, c) 建立的 B+ 树，先用 a 排序，a 相同再用 b 排序，b 相同再用 c 排序。查询条件里的 a 是最左侧的定位键，只有命中 a，索引才能继续往下顺着 b、c 查找。如果查询条件只写了 b 和 c，索引无法定位起点，就无法使用。
这里有个容易误解的点：最左前缀不完全等同于查询条件里必须包含第一个字段。它指的是，索引的匹配必须从最左边的列开始连续匹配。比如联合索引 (a, b, c)，以下情况能用索引：
- WHERE a = 1
- WHERE a = 1 AND b = 2
- WHERE a = 1 AND b = 2 AND c = 3
- WHERE b = 2 AND a = 1（查询优化器会重排条件顺序）
以下情况不能用：
- WHERE b = 2
- WHERE c = 3
- WHERE b = 2 AND c = 3
在实际开发中，优化器具备子句重排能力，条件顺序不一致通常不影响索引使用，但如果你通过 EXPLAIN 看到 type=index 或者 type=ALL，心里就要有数，大概率是没满足最左前缀。
2.2 where条件里 a and b 应该怎么建索引
回到热搜词里出现频率极高的一个问题：WHERE a AND b 应该怎么建索引？最简单的答案是建立一个联合索引 (a, b)，而不是分别建两个单列索引。
为什么联合索引更优？因为一个联合索引只需要维护一棵 B+ 树。而两个单列索引时，MySQL 的优化器通常会选择其中一个索引过滤，然后回表再过滤另一个条件，另一个索引可能完全没用上。极端情况下优化器会尝试索引合并（Index Merge），但那是特定场景下的优化路径，不是普惠方案。
但建立联合索引也得讲究顺序。如果查询条件只有 WHERE a AND b，顺序 (a, b) 和 (b, a) 都能用上，此时要根据选择性决定。选择性高的字段放前面，比如 a 字段有 100 个不同值，b 字段有 10000 个不同值，那应该把 b 放前面，因为 b 能过滤掉更多行。如果 a 已经能过滤到只剩几百行，b 放前面意义不大，除非还考虑排序和分组的需求。
还有一点很多文章没讲透：如果查询条件里 a 和 b 之外还经常带上 c 一起查，建 (a, b, c) 是合理的。但如果有的查询只要 a 和 b，有的查询只要 a 和 c，有的查询三者都有，建 (a, b, c) 或 (a, c, b) 只能覆盖两种前缀组合，另一种情况下索引就失效了。此时要根据最频繁的查询模式来决定字段顺序，同时额外建一个单列索引补充覆盖缺失的查询路径。
注意：联合索引能复用的前提是满足最左前缀。查询条件 WHERE a AND c 能用到联合索引 (a, b, c) 中的 a 部分，c 的条件在索引树里无法用于定位，但可以在二级索引回表前进行过滤，这块机制叫索引条件下推（ICP），后文会专门讲。
2.3 区分度高与低的字段如何确定位置
索引的作用是快速缩小查询范围，区分度低的字段定位能力弱。比如性别字段只有男、女两个值，无论放哪里，B+ 树能过滤的数据都有限。但区分度低的字段放在联合索引前部，会影响后续字段的定位能力。
假设有表结构和查询：
CREATE TABLE user ( id BIGINT PRIMARY KEY, gender TINYINT, city VARCHAR(50), age INT, name VARCHAR(50) ); SELECT * FROM user WHERE gender = 1 AND city = '杭州';如果建立联合索引 (gender, city, age)，因为 gender 选择性极低，MySQL 扫描时先按 gender 定位，会拿到全表一半的数据量，然后才能在数据块里筛 city。如果反过来建 (city, gender, age)，city 字段区分度高得多，扫描范围大大缩小。
但别一棍子打死所有低区分度字段。如果查询用到了索引覆盖，低区分度字段放前面带来的回表减少可能更划算。比如高频场景是 SELECT gender, city FROM user WHERE city = '杭州'，此时 (city, gender) 就是覆盖索引，gender 放不放前面不影响定位，放前面反而会让叶子节点额外存储无意义的数据。所以原则是：首先保证最左前缀能命中高频查询，其次在能命中多个查询的情况下，把区分度高的字段放前面。
3. 索引失效的常见场景与排查方法
3.1 索引失效的十大典型场景
聊索引不可能绕开“失效”这个话题。面试问得多，实际写 SQL 踩坑也多。我整理了自己排查线上问题过程中遇到过的大部分失效场景，基本覆盖了日常开发：
- 对索引列使用函数或表达式：WHERE YEAR(create_time) = 2024 会导致 create_time 索引失效，正确做法是 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'。
- 隐式类型转换：WHERE phone = 13800138000，phone 字段是 VARCHAR，传入整数时 MySQL 会做类型转换，索引失效。
- 模糊匹配以通配符开头：WHERE name LIKE '%张' 无法用索引，WHERE name LIKE '张%' 可以。
- 联合索引不满足最左前缀：前文已经展开，这是高频问题。
- OR 条件中包含非索引列：WHERE name = '张三' OR age = 20，如果 age 没有索引，整个 OR 可能走全表。
- 对索引列进行运算：WHERE id + 1 = 100，索引列参与了算术运算。
- 使用 NOT IN、!=、NOT LIKE：这些操作通常无法利用索引快速定位，优化器倾向于全表扫描。
- IS NULL / IS NOT NULL：InnoDB 对 NULL 的索引处理比较特殊，某些场景下优化器放弃索引，多数情况下 IS NULL 反而能走索引，IS NOT NULL 则容易失效。
- 字符集不一致：两张表关联字段字符集不同，会导致索引失效，这种情况很隐蔽。
- 数据量太小：表只有几十行数据，优化器判定全表扫描比走索引更快，这时 EXPLAIN 会显示全表扫描。
3.2 隐式类型转换的底层原理
隐式类型转换很值得单独说，因为它在代码里几乎看不出来。MySQL 对字符串字段灌入数字时，会把字符串转换为数字再比较，相当于对列执行了 CAST(phone AS SIGNED)，导致索引列参与函数运算，索引自然没法用。
优化前后的对比：
-- 慢，索引失效 SELECT * FROM user WHERE phone = 13800138000; -- 快，索引生效 SELECT * FROM user WHERE phone = '13800138000';反过来，如果索引列本身是数值类型，传入字符串不会导致索引失效，因为 MySQL 会把字符串转成数字去比较数值列，方向不同。但最规范的做法是应用层就保证类型一致。
我在排查一次接口超时问题时，发现某个查询语句执行时间从 50ms 涨到了 3 秒。EXPLAIN 显示 type=ALL，rows 预估几百万，检查 SQL 后发现是 phone 条件传入的是 Long 类型，而表结构 phone 是 VARCHAR。改成字符串传参之后执行时间立刻回到 60ms，这个案例我印象非常深刻。
3.3 使用 EXPLAIN 定位索引失效问题
排查索引问题的第一件事永远是 EXPLAIN。格式如下：
EXPLAIN SELECT * FROM user WHERE name = '张三'\G重点看几个字段：
- type：从好到坏依次是 system、const、eq_ref、ref、range、index、ALL。出现 ALL 或者 index，基本说明索引没用好。
- key：实际命中的索引名称。如果 NULL 说明没用到索引。
- rows：预估需要扫描的行数，数值越大说明过滤性越差。
- Extra：常见的有 Using index（覆盖索引）、Using where（存储引擎层过滤后还需要 MySQL 服务层过滤）、Using filesort（需要额外排序）、Using temporary（使用临时表）。
如果 type=ref 或者 range，结合 rows 和 Extra 基本能判断索引是否健康。比如 type=ref，key=idx_name，Extra=Using where，说明虽然定位到了 name 但 where 里有其他字段需要在服务层过滤，回表后再过滤是可以的，但如果过滤比例高，就要考虑扩大联合索引覆盖范围。
提示：EXPLAIN 的结果是优化器根据统计信息预估的，出现估算偏差时可以执行 ANALYZE TABLE 更新统计信息，让优化器拿到更准确的基数和分布密度。
4. 排序、分组优化与索引的妙用
4.1 文件排序与索引排序的抉择
ORDER BY 是数据库性能的重灾区。MySQL 执行排序有两种方式：利用索引直接按顺序读取数据（Using index 或不用额外排序，Extra 里没有 filesort），以及内存或磁盘文件排序（Using filesort）。
当 ORDER BY 字段能匹配联合索引的排序顺序时，查询结果直接按索引顺序输出，不需要额外排序，效率非常高。但如果排序字段不在索引中，或者排序方向与索引方向不一致，MySQL 就需要把结果集先读出来再排序。结果集小的话走内存 sort buffer，结果集大的话走磁盘临时文件，性能急剧下降。
举一个典型场景：表结构有联合索引 (city, age)，执行以下 SQL：
SELECT * FROM user WHERE city = '杭州' ORDER BY age DESC;因为 city 条件命中了联合索引的第一个字段，age 又是索引的第二个字段，所以每个 city 分区内部的 age 天然有序，不需要额外排序。如果改成 ORDER BY name，因为 name 不在索引里，MySQL 就必须把满足 city 条件的行全部拿出来，再对这些行的 name 排序，这就是 filesort。
再补一个容易忽略的点：索引排序方向的问题。联合索引定义 (a ASC, b ASC)，但查询要求 ORDER BY a ASC, b DESC，此时 b 的排序方向与索引相反，MySQL 可能无法直接利用索引顺序获取数据。如果你确认业务上 ORDER BY b DESC 是高频路径，可以考虑建 (a ASC, b DESC) 的倒排序索引。MySQL 8.0 开始支持倒序索引，不过使用前要评估维护成本。
4.2 GROUP BY 同样依赖索引有序性
GROUP BY 的底层机制是先排序再分组（或者用哈希分组）。如果分组字段恰好满足索引最左前缀，排序可以省略，效率会高很多。比如联合索引 (dept_id, status)，执行：
SELECT dept_id, COUNT(*) FROM order WHERE status = 1 GROUP BY dept_id;这个查询依赖了索引 col1 的定位，同时 group by dept_id 正好是索引的第一个字段，可以顺序读取并分组。但如果 GROUP BY 的字段不满足最左前缀，比如 GROUP BY status，索引帮助不大，MySQL 需要临时表 + filesort 来完成分组。
涉及 GROUP BY 的优化，还可以考虑引入汇总表、物化视图、或者利用覆盖索引。比如 SELECT dept_id, COUNT() FROM order GROUP BY dept_id，如果索引是 (dept_id, id)，那 COUNT() 可以基于二级索引统计行数，不需要回表，这就是一个典型的覆盖索引在聚合场景下的优化。
4.3 双向索引与降序排列的实践认知
热搜词里出现了双向索引，实际工作中 MySQL 8.0 的倒序索引经常被低估。标准 B+ 树索引叶子节点按升序排列。DESC 排序时，默认实现方式是反向扫描索引，在 8.0 之前反向扫描的效率略低于正向扫描。如果你的查询高频使用 ORDER BY create_time DESC 且 LIMIT 10，这时候建一个 (create_time DESC) 的索引可能比默认 (create_time ASC) 性能更好，因为引擎可以直接正向扫描倒序索引，避免反向遍历。
一个判断依据：如果查询中 ORDER BY 方向比较固定，而且和索引定义方向相反，可以考虑建倒序索引；如果只偶尔排序，倒序索引带来的额外维护成本就不划算。
5. 索引下推、索引合并与优化器行为
5.1 索引条件下推（ICP）：联合索引的隐藏加速
索引下推（Index Condition Pushdown）是 MySQL 5.6 引入的优化手段。在没有 ICP 的年代，联合索引 (a, b) 遇到 WHERE a > 1 AND b = 2 时，只能通过 a 定位索引区间，拿到一批主键后回表取整行数据，再在服务层过滤 b = 2。其实 b 是索引的一部分，可以在扫描二级索引时就判断，不必回表。
开启 ICP 之后，MySQL 会把 b = 2 这个条件下推到存储引擎，在二级索引扫描时就过滤掉不符合 b 条件的索引项，只有满足条件的主键才回表。这样回表次数大幅减少。
ICP 是默认开启的，表示方法是 EXPLAIN 的 Extra 列出现 Using index condition。只要查询满足最左前缀但后续条件无法走索引定位，就会触发 ICP。比如前文提到的 WHERE a AND c 且联合索引是 (a, b, c)，c 无法定位，但可以在索引层过滤，这就是 ICP。
ICP 对性能的提升幅度取决于过滤比例。比如 a 条件筛出 10 万行，其中 b = 2 的只有 100 行，ICP 能把回表次数从 10 万降到 100，效果极其明显。但如果过滤比例不高，ICP 提升有限。
5.2 索引合并：多个单列索引的协同工作
有时候你在两个字段上分别建了索引，查询 WHERE a = 1 OR b = 2，MySQL 会尝试索引合并。索引合并有三种方式：交集（INDEX MERGE INTERSECTION）、并集（INDEX MERGE UNION）、排序并集（INDEX MERGE SORT UNION）。交集通常发生在 AND 条件中，并集发生在 OR 条件中。
索引合并不是银弹，它的执行过程是分别扫描两个索引、合并结果、再去聚簇索引回表，逻辑复杂，还可能因中间结果集过大导致性能反而劣化。所以设计阶段更推荐直接根据高频查询建联合索引，避免依赖索引合并。
排查索引合并问题时，EXPLAIN 的 type 列会显示 index_merge，key 列会显示多个索引名。确认是 index_merge 之后，建议评估一下是否能调整为联合索引，查询性能通常会更好。
但要补一句实话：如果 a 和 b 各自单独查询的频率非常高，且联合查询占比不大，两个单列索引也有价值，因为一个联合索引无法同时优化 a 单独查询和 b 单独查询的最左前缀。联合索引只能照顾一个起点，两个单索引可以照顾两个起点。遇到这种情况，合理的选择是保留两个单列索引，同时接受查询 a AND b 时优化器可能选择 Index Merge，除非 a AND b 是绝对的高频路径，再考虑额外建联合索引。
5.3 优化器选错索引的处理手段
MySQL 的查询优化器基于统计信息估算代价，选出执行计划。但统计信息可能不够准确，或者查询条件复杂时优化器计算出错，导致选错索引。典型表现是明明有更合适的索引，EXPLAIN 显示的 key 是另一个。
处理方法有几种：
- 使用 FORCE INDEX 强制指定索引，语法是 SELECT * FROM user FORCE INDEX(idx_name) WHERE xxx。
- 使用 USE INDEX 建议优化器优先使用某个索引（不强制）。
- 调整索引设计，删除冗余索引，减少优化器的选择范围。
- 更新统计信息，执行 ANALYZE TABLE。
FORCE INDEX 是一把双刃剑。如果数据分布后续发生巨大变化，强制索引可能比让优化器自己选更差。所以我会建议先用 ANALYZE TABLE 和重新审查 SQL 写法，确认是不是 SQL 本身存在优化空间，最后才考虑 FORCE INDEX，并且要留注释说明为什么强制了一枚索引，方便后来人维护。
6. 主键索引与唯一索引：区别与选型
6.1 主键索引与唯一索引的本质差异
主键索引和唯一索引经常被拿来比较，两者都要求列值唯一，但有几处关键差异：
- 主键是聚簇索引，直接决定表数据的物理存储顺序；唯一索引是二级索引，需要回表。
- 一个表只能有一个主键，但可以有多个唯一索引。
- 主键不允许 NULL，唯一索引在 MySQL 中允许存在多个 NULL 值（因为 NULL 不等于任何值，包括 NULL 本身）。
- 主键通常作为 InnoDB 聚簇索引的锚点，索引叶子节点是整行数据；唯一索引叶子节点是主键值。
从查询性能看，主键查询通常比唯一索引快，因为主键只需要一次 B+ 树搜索就拿到整行数据，唯一索引需要先扫描二级索引，再用主键回表。但这个差距在大多数业务场景下是微乎其微的，真正需要考虑的是业务约束和物理设计。
6.2 什么时候用唯一索引而不是主键
业务上需要唯一约束但不是主键的字段，典型如身份证号、手机号、订单号。如果系统统一使用自增 BIGINT 作为主键，那么身份证号这类字段应该用唯一索引来保证业务约束。
这里有一个设计经验：不要为了“复用唯一约束”就直接把业务字段设成主键。比如用手机号做主键，后续如果业务规则变化，一个用户可能有多个手机号，主键就要变更，聚簇索引结构重排的成本非常高。用一个与业务无关的自增主键，业务唯一键用 UNIQUE KEY 控制，是更稳妥的方案。
6.3 覆盖索引在唯一键查询中的优势
唯一索引作为二级索引，如果能覆盖查询字段，就不需要回表。比如用户表有唯一索引 idx_phone(phone)，执行 SELECT phone, id FROM user WHERE phone = '13800138000'，二级索引叶子节点已经包含 phone 和主键 id，不需要回表。如果执行 SELECT phone, name FROM user WHERE phone = 'xxx'，二级索引没有 name，必须回表读一行数据。
高频场景尽量用覆盖索引，这个经验适用于所有二级索引，唯一索引只是其中一种。
7. 索引表空间、冗余索引与维护策略
7.1 索引表空间是什么以及占用怎么估算
InnoDB 中索引和数据都存储在表空间中。独立表空间模式下，每个表一个 .ibd 文件，索引和数据都在里面。查询索引占用大小可以用：
SELECT table_name, index_name, stat_value * @@innodb_page_size AS estimated_size_bytes FROM mysql.innodb_index_stats WHERE table_schema = '你的库名' AND stat_name = 'size';实际运维中用 information_schema.TABLES 拿到 data_length 和 index_length，可以估算一张表有多少数据、多少索引。设计阶段估算索引占用时，简单方法就是：一个二级索引大约占用等于“索引列字节数 + 主键字节数 + 额外指针开销（约 40 字节）”乘以行数。
索引不是免费的，每一个索引在写入、更新、删除时都要同步维护。一张表如果建了 10 个索引，写入性能会直线下降，因为每次 INSERT 要同时维护 11 棵 B+ 树。所以索引数量要克制，通常单表单索引数量建议控制在 5 个以内，高频写表甚至更少。
7.2 冗余索引排查与删除：袖里乾坤的清理
冗余索引的典型情况有两种：一是联合索引 (a, b) 已经存在，又单独建了索引 (a)，后者可以被前者覆盖，属于冗余；二是索引 (a, b, c) 在外，又建了 (a, b)，后者同样冗余。
排查冗余索引可以用 sys 库的视图：
SELECT * FROM sys.schema_redundant_indexes;这个视图会直接告诉你哪两个索引是冗余关系。清理冗余索引可以降低写入开销和存储占用，但有一个例外必须考虑：如果单独建的 (a) 已经被 (a, b) 覆盖了，而 (a, b) 索引占用的空间比 (a) 大得多，你为了一个低频率查询去扫描 (a, b) 可能比 (a) 慢，这时候保留 (a) 反而更合理。判断标准是查询频率与数据量，不要一刀切删。
7.3 索引碎片的产生与重建
索引在频繁插入、删除、更新后会产生碎片，导致叶子节点填充率下降、页分裂增多，扫描效率变差。碎片率可以通过 information_schema 或 sys.schema_index_statistics 查看，严重碎片时考虑 ALTER TABLE xxx ENGINE=InnoDB 重建表，或者用 OPTIMIZE TABLE。
但 OPTIMIZE TABLE 会锁表，大表上执行会阻塞业务，需要选择业务低峰期。MySQL 8.0 开始支持 ALTER TABLE xxx ENGINE=InnoDB 的在线 DDL，不过还是要评估当时的负载情况。
另一个减少碎片的策略是控制自增主键的随机性，前文提过尽量别用 UUID 做主键。还有一个策略是批量删除数据时，避免一次删除超大范围的数据导致 B+ 树大面积调整，建议分批删除，每批几百到几千行，给索引树的合并留出缓冲。
8. 慢查询排查与索引全链路优化
8.1 从慢查询日志发现索引问题
排查系统性能问题时，第一步通常是打开慢查询日志。相关参数：
SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; SHOW VARIABLES LIKE 'slow_query_log_file';设置方式：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';long_query_time 控制阈值，单位秒，建议线上设置为 1 秒或者更低，根据业务情况调整。log_queries_not_using_indexes 开启后，没有使用索引的查询也会记录，这样能排查出隐藏的全表扫描 SQL。
拿到慢查询之后，将 SQL 复制出来，逐条做 EXPLAIN，结合表结构和业务特点评估是否缺索引、是否索引失效、是否 SQL 写法有优化空间。这个步骤是索引优化的入口。
8.2 千万级数据表的索引优化实战
分享一个实际优化案例。一张订单表 order，约 1800 万行，高频查询场景如下：
SELECT id, order_no, amount, status FROM order WHERE user_id = 123456 AND status = 1 AND create_time BETWEEN '2024-01-01' AND '2024-06-01' ORDER BY create_time DESC LIMIT 20;原始表只有 PRIMARY KEY (id)，执行上述 SQL 时全表扫描，查询耗时 4.8 秒。分析后发现查询条件和排序字段有 user_id、status、create_time，于是设计联合索引 (user_id, create_time, status)。
为什么这样设计而不是 (user_id, status, create_time)？因为 ORDER BY create_time 希望用索引排序避免 filesort，而且 create_time 的区分度比 status 高，把 create_time 放在 status 前面，既能保证 WHERE 条件下 user_id 定位后 create_time 范围扫描，又避免了 ORDER BY 额外排序。
改造后 EXPLAIN 显示 type=range，key 是联合索引，Extra 从 Using filesort 变成了 Using index condition，查询耗时降到 45ms。当然代价是写入性能稍降，但对读多写少的订单查询场景来说完全划算。
提示：覆盖索引在这个查询里也能做，把 SELECT 的 id、order_no、amount、status 中除了 amount 之外的字段都放进索引，amount 因为业务含义大、字段宽，很少加入索引，实际可根据数据量权衡。
8.3 索引优化后的验证与回归
索引优化不是建完就算完。验证环节我一般做三件事：
第一，EXPLAIN 确认执行计划符合预期，type、key、rows、Extra 都达到标准。第二，用真实用户数据查询多次，观察耗时是否稳定，注意清掉 SQL 缓存或者换个 where 条件值验证不同数据分布。第三，观察线上一段时间表现，看看慢查询数量是否下降，以及磁盘 IO 是否有异常波动，因为索引增多会导致写放大。
回归也很重要。优化一个查询可能引入新索引影响写路径，如果业务是高频写入，就要观察写入延迟是否有明显上升。有些场景要把索引设计放到整个系统的写入链路里综合评估，而不只是看单条查询的收益。
9. 经典面试题串联：主键索引与唯一索引、索引失效、SQL优化
9.1 主键索引和唯一索引的区别，一条条说清
面试被问到这类问题时，不建议只背名词，要从结构、约束、性能、设计四个维度展开：
- 结构上：主键索引是聚簇索引，数据存在叶子节点；唯一索引是二级索引，叶子节点存主键值。
- 约束上：主键不可为空且唯一；唯一索引可为空但唯一（NULL 可重复）。
- 数量上：一张表只有一个主键索引，唯一索引可以有多个。
- 性能上：主键查询一次 B+ 树扫描拿数据；唯一索引两次扫描（索引 + 回表），覆盖索引可免回表。
- 设计上：主键尽量与业务无关、自增、短；唯一索引承载业务唯一约束。
回答这类问题如果能加入自己实际处理过的案例，比如手机号唯一索引导致回表过多变慢、UUID 主键导致写入性能下降，会让面试官觉得你有真实经验，而不是只会背书。
9.2 哪些场景会导致索引失效
面试高频题。下面这个回答框架可以直接用：
- 违反最左前缀：联合索引使用不完整。
- 索引列参与运算：函数、算术表达式、隐式类型转换。
- 模糊匹配通配符开头：LIKE '%xx'。
- OR 连接非索引列。
- 数据分布极不均匀，优化器放弃索引。
- 索引列大量 NULL 值导致优化器选择全表。
- 字符集不一致、排序规则不一致导致无法使用索引关联。
最好每个失效场景都能补一句“遇到过线上什么问题，后来怎么解决的”，比干背八条规则有价值得多。
9.3 一条 SQL 的优化思路如何表达
被问到“说一些 SQL 优化上面的经验”，我认为好的回答不是背出各种大招，而是展现出清晰的排查流程。我会在面试里这样回答：
第一步，确认慢在哪，通过慢查询日志、性能监控找到具体的 SQL 和对应执行计划。第二步，EXPLAIN 分析 type、key、rows、Extra，定位是全表扫描、回表过多还是 filesort。第三步，结合业务 SQL 的查询条件和排序分组需求，设计联合索引，字段顺序按最左前缀、区分度、排序需求综合决定。第四步，看能否用覆盖索引减少回表。第五步，改写 SQL，避免函数运算、隐式转换、OR 和模糊匹配通配符开头。第六步，如果 SQL 本身没问题，再考虑表结构设计，比如分表、归档历史数据。
这样的表达方式既展示了问题排查思路，又包含了落地技能，比单纯罗列十几个优化点更能让人记住。
9.4 MySQL 存储引擎的选择对索引的影响
面试问存储引擎，基本会对比 InnoDB 和 MyISAM。从索引角度说，InnoDB 是聚簇索引，数据随主键存储，支持事务、行级锁、外键；MyISAM 是非聚簇索引，索引存数据地址，支持全文索引但无事务。现代业务基本默认 InnoDB，MyISAM 只适合特定只读场景。
实际开发中遇到过问“能用 MyISAM 代替 InnoDB 加速查询吗”的场景。结论是：能，但代价太大。你把引擎切换成 MyISAM 相当于放弃了事务和行锁，如果表在并发写入场景下使用，后果很严重。为了查询性能优先，正确方向是索引优化和读写分离，而不是换引擎。
10. 一次真实的全链路索引优化记录
最后分享一个最近的完整案例。某车联网项目的车辆轨迹表，产生了约 3200 万行数据，业务上高频查询是查某辆车某个时间段的轨迹点。原始 SQL 如下：
SELECT * FROM track_data WHERE vehicle_id = 10086 AND track_time >= '2024-03-01 00:00:00' AND track_time < '2024-03-02 00:00:00';这条 SQL 没命中任何索引，全表扫描约 3200 万行，每次查询耗时 7~12 秒，接口直接超时。
第一步，确认表结构，发现只有主键 id，无任何业务索引。第二步，分析查询条件，核心是 vehicle_id 和 track_time。第三步，设计联合索引。
我最初的设计是 idx_vehicle_time (vehicle_id, track_time)，但 EXPLAIN 发现 Filesort 几乎没有但回表量大。进一步分析查询返回的是完整轨迹数据，SELECT * 无法覆盖，回表无法避免。此时核心思路是：先尽量缩小二级索引扫描范围，减少回表行数。
结果证明联合索引 (vehicle_id, track_time) 效果已经很好了。按 vehicle_id 先过滤，一辆车一天的轨迹数据最多几千条，再按 track_time 范围过滤，返回到主键回表查询的行数就很小。实际线上验证：查询耗时从 8 秒降到了 80ms 左右。
但还有一道隐藏关卡：车辆轨迹表写入频率很高，每几秒就有新轨迹插入。联合索引的引入会导致每次 INSERT 都要维护 idx_vehicle_time 这棵 B+ 树。由于车辆轨迹表的数据是时间顺序追加的，索引 (vehicle_id, track_time) 的分裂压力主要集中在末尾节点，总体影响可控，线上观察后写入延迟无异常。
这次优化再做第二次迭代时，我发现部分统计查询需要 GROUP BY vehicle_id 计算某天的轨迹点数，于是继续调整索引字段，让 (vehicle_id, track_time) 覆盖这个聚合需求，虽然 SELECT * 无法覆盖，但 COUNT 聚合可以直接在二级索引上完成，不需要回表。
最终效果：慢查询日志里 track_data 相关 SQL 从一天几十条降到了零，接口 P95 从 3.2 秒降到 220ms，写入无感。整个过程耗时大约一个下午，工具就是 EXPLAIN 加慢查询日志，没有用到花哨的配置，核心就是把索引设计还原到业务 SQL 本身的查询模式上。
这个案例里最有价值的经验是：联合索引 (vehicle_id, track_time) 不是靠直觉拍脑袋定的，而是因为查询条件里已经有了最有效的过滤字段 vehicle_id，排序或范围字段才选择 track_time 放在第二位。如果你把所有查询字段都平等对待，一部分字段适合放前部做过滤，一部分字段适合放后部避免 filesort，这种取舍需要结合具体 SQL 和业务频率来定，不能机械套模板。
