写过太多慢查询优化见过太多因为索引没建对导致全表扫描把数据库拖垮的案例。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 1WHERE a 1 AND b 2WHERE a 1 AND b 2 AND c 3WHERE b 2 AND a 1查询优化器会重排条件顺序以下情况不能用WHERE b 2WHERE c 3WHERE b 2 AND c 3在实际开发中优化器具备子句重排能力条件顺序不一致通常不影响索引使用但如果你通过 EXPLAIN 看到 typeindex 或者 typeALL心里就要有数大概率是没满足最左前缀。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 13800138000phone 字段是 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 NULLInnoDB 对 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 显示 typeALLrows 预估几百万检查 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使用临时表。如果 typeref 或者 range结合 rows 和 Extra 基本能判断索引是否健康。比如 typerefkeyidx_nameExtraUsing 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)执行以下 SQLSELECT * 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 2MySQL 会尝试索引合并。索引合并有三种方式交集INDEX MERGE INTERSECTION、并集INDEX MERGE UNION、排序并集INDEX MERGE SORT UNION。交集通常发生在 AND 条件中并集发生在 OR 条件中。索引合并不是银弹它的执行过程是分别扫描两个索引、合并结果、再去聚簇索引回表逻辑复杂还可能因中间结果集过大导致性能反而劣化。所以设计阶段更推荐直接根据高频查询建联合索引避免依赖索引合并。排查索引合并问题时EXPLAIN 的 type 列会显示 index_mergekey 列会显示多个索引名。确认是 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 ENGINEInnoDB 重建表或者用 OPTIMIZE TABLE。但 OPTIMIZE TABLE 会锁表大表上执行会阻塞业务需要选择业务低峰期。MySQL 8.0 开始支持 ALTER TABLE xxx ENGINEInnoDB 的在线 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 显示 typerangekey 是联合索引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 是非聚簇索引索引存数据地址支持全文索引但无事务。现代业务基本默认 InnoDBMyISAM 只适合特定只读场景。实际开发中遇到过问“能用 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 和业务频率来定不能机械套模板。