1. 先看一个真实例子索引为什么能让慢SQL起死回生前两天线上有个列表查询接口又超时了现象很典型数据量也就两千万行单条 SQL 跑了四十多秒页面直接转圈圈。第一反应当然是看慢查询日志发现是一张订单明细表条件里只写了user_id然后ORDER BY create_time DESC LIMIT 20。这语句按说是常规操作为什么慢到不可接受explain 出来之后type 是 ALLrows 估算一千八百万也就是全表扫了一遍然后临时表排序最后才取 20 条。根子就一句话表上没有一个能把user_id和create_time一起用上的索引。后来我加了一个(user_id, create_time)联合索引查询时间从四十多秒掉到个位数毫秒。这个案例我印象特别深因为它把 MySQL 索引优化里的几个核心词全串起来了B树决定了索引怎么存回表是二级索引查到主键后回去取整行的过程覆盖索引是让查询不用回表的巧妙设计最左匹配原则是联合索引能不能被用上的关键。这篇文章我不打算把 MySQL 所有知识点都翻出来讲一遍就围绕这四个关键词把索引这件事拆透适合写 SQL 的开发者、刚接手数据库优化的 DBA以及所有听过“加索引”但没系统理解过原理的人。看完之后你至少能自己判断一条慢 SQL 为什么慢该建什么索引以及哪些写法会让索引白建。在动手加索引之前得先明白一个基本事实MySQL 不是把数据一行行堆在硬盘上的。InnoDB 把表的数据和索引都存成一颗 B树主键索引的叶子节点存的是整行记录这就是聚簇索引。搞清楚这棵树长什么样后面所有优化技巧才说得通。2. B树MySQL索引的底层骨架2.1 为什么索引结构偏偏选了B树很多人刚接触索引时会想为什么不用哈希表哈希表的等值查找明明是 O(1)太香了。但 SQL 查询不是只有等值查询还有范围查询比如WHERE amount BETWEEN 100 AND 200、ORDER BY create_time哈希表完全没法排序也没法做范围扫描。二叉树呢极端情况下会退化成链表树高不可控。红黑树虽然平衡了但每次插入删除要做旋转操作而且树高还是有点高节点只存一个键值在磁盘上定位下一层节点要靠指针跳一次磁盘 IO 一次层。B树的设计思路就是冲着“减少磁盘 IO 次数”去的。它把所有数据行放在叶子节点非叶子节点只存键值和指针每个节点能容纳很多个键值扇形广树就矮。磁盘读取的最小单位是页默认 16KBB树一个节点就是一个页读一次 IO 就能拿到一个节点里全部索引项这样从根节点到叶子节点三层树通常就能覆盖几千万行数据也就是最多三次 IO 定位到记录。这个特性是哈希表、红黑树都比不上的。2.2 B树长什么样和B树差在哪如果你去看教材B树和B树都是多路平衡搜索树但有几个关键区别B树的非叶子节点也存数据B树的非叶子节点只存索引键和指向子节点的指针。B树的叶子节点通过链表串联而且叶子节点之间按顺序排列天然适合范围扫描和排序。B树的数据检索必须到叶子节点B树可能在非叶子节点就命中数据但查询路径不稳定。叶子节点链表这一点特别重要。WHERE id 100这种范围查询在 B树上只需要找到id100所在的叶子节点然后顺着链表往后扫就行不需要每次都从根节点重新搜索。这也是为什么 InnoDB 在范围查询、排序场景下比很多其他结构表现更稳。2.3 三层B树到底能存多少数据这个数字值得手算一遍心里有底。InnoDB 默认页大小 16KB假设主键是BIGINT占 8 字节指向子节点的指针占 6 字节那么一个非叶子节点大约能存16 * 1024 / (86) ≈ 1170个索引项。三层树的话根节点至少有 1170 个分叉第二层 1170 × 1170 ≈ 137 万个分叉第三层叶子节点每个按 1KB 存一条完整记录来估算能覆盖到一亿行以上。所以日常业务里绝大多数表的索引树高度就三层查询走主键索引最多三次磁盘 IO。这也是为什么几千万行的表用主键查询依然很快的原因。实操里要留意的是不要把主键设成超长的字符串。如果主键是 UUID 那种 36 个字符的字符串一个非叶子节点能存的索引项就少很多树会变高磁盘 IO 次数增加每一条二级索引的叶子节点还要复制这个主键值整个索引体积都会膨胀。这也是我强烈建议 InnoDB 表用自增整数主键的一个底层原因。3. 聚簇索引与二级索引回表到底是怎么回事3.1 主键索引为什么叫聚簇索引InnoDB 里表的数据行本身就存放在主键索引的叶子节点上也就是说“数据即索引”所以叫聚簇索引。你建表时指定了主键那这棵树就是按主键顺序组织的如果没指定主键InnoDB 会找一个非空的唯一索引当主键再找不到就生成一个 6 字节的隐式ROW_ID。这个设计带来一个直接后果按主键范围查询时数据物理上也是大致连续的读取效率极高。但二级索引就不一样了。二级索引也叫辅助索引它的叶子节点存的是什么存的是索引键值加主键值而不是整行数据。比如你在user_id上建了一个普通索引这棵树的结构是按user_id排序的但叶子节点里只有user_id和主键id。查询如果只需要id和user_id那在二级索引里就够了不用再去别的地方。但如果你还要查user_name、order_amount这些字段二级索引里没有那 MySQL 只能拿着叶子节点里存的主键回头去主键索引的树里再查一次完整记录这个过程就叫“回表”。3.2 回表一次还好回表几百万次就是灾难回表本身不是错误它是 InnoDB 在存储结构上的必然选择。真正的问题在于回表次数。上面那个慢 SQL如果只有user_id单列索引那 MySQL 会先扫二级索引找到所有满足user_id条件的主键假设有一百万条匹配就要回表一百万次每次通过主键随机查找记录效率非常低。而且在二级索引和聚簇索引之间来回跳也是随机 IO比顺序 IO 慢得多。这就是为什么我强调面对高频查询尽量让查询只用二级索引就能拿到全部需要的数据连回表都不需要。实操中判断一条 SQL 是否回表看执行计划的 Extra 列就行。如果读到Using index说明查询所需字段都在索引树里不需要回表如果读不到这个提示而key列又显示了索引名通常就是走了索引但还得回表。Using index condition是另外一回事代表使用了索引条件下推ICP这个后面再提。3.3 辅助索引如何避免回表避免回表最直接的手段是设计覆盖索引。但还有两个小技巧值得说。第一在二级索引的末尾显式加上常用字段让索引能“覆盖”更多查询第二如果只是确认记录是否存在可以用SELECT 1甚至EXPLAIN先行分析因为经过优化器判断后不一定真需要回表取数。还有一点符合最左匹配原则的联合索引本身就会减少回表次数因为它把多个过滤字段都塞进一棵索引树里先用复合条件把结果集缩得很小再考虑是否回表回表次数自然少了。不过要注意索引不是越多越好。每个索引都是一棵独立的 B树写入时要同步维护索引过多会导致 insert/update 变慢磁盘占用也会成倍增长。我曾经优化过一个表业务方在二十几个字段上各建了单列索引写入性能掉了三成最后按查询模式收敛到三组联合索引才恢复正常。4. 覆盖索引让查询不碰聚簇索引4.1 覆盖索引是什么当一个二级索引包含了查询所需的全部列那么这个索引本身就能覆盖查询不需要回表这种索引就叫覆盖索引。注意“覆盖”不是索引类型的名字而是一种使用状态。同一个索引在这条 SQL 里是覆盖索引在另一条 SQL 里可能就不覆盖。举个例子假设表里有字段id, user_id, order_amount, create_time你在user_id上建了普通索引 idx_user。执行SELECT id, user_id FROM t WHERE user_id 123因为 idx_user 的叶子节点存了user_id和主键id查询结果所需字段都齐了Extra 会是Using index不用回表。但如果执行SELECT order_amount FROM t WHERE user_id 123order_amount 不在 idx_user 里就必须回表去主键索引取整行。4.2 怎么设计出覆盖索引设计覆盖索引的诀窍是先关注高频查询把查询里涉及的字段尽可能吸收到同一个联合索引里。还是拿开头的订单表举例如果业务上高频查询是SELECT order_no, create_time, status FROM orders WHERE user_id 1001 ORDER BY create_time DESC LIMIT 20;这时候.user_id单列索引虽然能快速定位但查询还要取order_no、status每行都要回表然后临时排序。更优方案是建联合索引(user_id, create_time, order_no, status)字段顺序从等值条件到排序字段再到查询字段这样二级索引树里叶子节点已经按user_id, create_time排序好了范围扫描时也能直接用索引排序需要的字段也全覆盖执行计划里 Extra 会显示Using index; Using filesort消失改为Using index。实际工作中不要追求让所有查询都覆盖尤其是查询SELECT *的场景全表字段根本不可能塞进一个索引。正确做法是只针对最热门的几条 SQL 做覆盖索引优化其余查询容忍回表。索引的作用是把高频访问路径变快不是把每一条随心所欲的 SQL 都养肥。4.3 覆盖索引和ICP的区别这两个概念经常被混在一起提我顺手区分一下。覆盖索引是查询结果不需要回表整条 SQL 都在索引树里完成。ICP 是 Index Condition Pushdown 的缩写指的是把WHERE里能下推到存储引擎的条件先放到索引扫描阶段过滤减少回表次数但最终可能还是要回表。执行计划里的Using index condition就是 ICP。举个典型例子联合索引(a, b)查询WHERE a 1 AND b 2。有了 ICPb 2这个条件可以在索引遍历时直接过滤不用先取回每行再过滤。如果没 ICP即使a 1能定位到一段索引记录MySQL 也得先回表取数再判断b 2返回的数据量会大很多。所以看到Using index condition不用慌这说明存储引擎已经帮你做了条件过滤只要确认key列有合理索引一般不会是性能瓶颈。5. 最左匹配原则联合索引的使用规则5.1 为什么联合索引要求最左匹配联合索引(a, b, c)底层是一棵按照a, b, c三个字段依次排序的 B树。先按a排序a相同的记录再按b排序b也相同的再按c排序。这种排序方式决定了优化器如果想走这个索引条件里必须从a开始。比如WHERE b 2 AND c 3在索引树里找不到统一的起始扫描范围因为它压根就没按b或c单独排过序。很多人把最左匹配理解为“查询条件里必须包含第一个字段”其实更准确的说法是查询条件必须能利用联合索引的一个连续前缀。比如WHERE a 1能走索引使用前缀a。WHERE a 1 AND b 2能走索引使用前缀a, b。WHERE a 1 AND b 2 AND c 3能走索引完整使用。WHERE b 2 AND c 3不能走索引因为没有a条件无法定位扫描起点。WHERE a 1 AND c 3能走索引但只用了a这个前缀c的过滤无法在索引层完成得回表后过滤。这里有个细微但重要的点MySQL 8.0 引入了索引跳跃扫描Skip Scan在某些特定的WHERE a 1 AND b 2场景下即使跳过了第一个字段也能用上索引但它有诸多限制日常优化时别依赖这个特性老老实实按最左匹配设计才是稳的。5.2 哪些写法会打破最左匹配最常见的违规操作是把联合索引第一个字段写在查询条件里但中间跳了一个字段。比如联合索引(user_id, status, create_time)查询WHERE user_id 100 AND create_time 2024-01-01这个查询虽然包含第一个字段但跳过了status所以 MySQL 只能用到user_id这一个前缀create_time的过滤得回表后再做。此外最左匹配也会被这些操作破坏对索引列使用函数比如WHERE DATE(create_time) 2024-01-01这会让优化器放弃索引。应该改成WHERE create_time 2024-01-01 AND create_time 2024-01-02。对索引列做隐式类型转换比如索引是字符串类型查询条件却写成WHERE mobile 13800138000数字会被转成字符串再比较导致索引失效。前导通配符WHERE name LIKE %张无法利用索引因为 B树按前缀排序无法从后往前定位WHERE name LIKE 张%则可以。范围条件之后的字段失效比如联合索引(a, b)查询WHERE a 100 AND b 5a用上了索引但b无法用来定位因为索引树按a排序后a 100的区间内b没有有序性可言。5.3 联合索引字段顺序怎么排实战里我给联合索引排字段顺序按这个优先级来等值条件字段优先放前面。等值条件定位精准能让索引前缀更完整。需要排序的字段次之。利用索引有序性避免 filesort。范围查询字段尽量放最后。比如create_time BETWEEN ... AND ...放后面就不影响前面等值字段的使用。最常用的过滤字段优先。如果一个查询天天跑另一个查询偶尔跑把高频查询的字段排前面更划算。这么说可能有点抽象我拿一个实际场景套一遍。表orders有user_id、status、create_time三个字段高频查询是“查某个用户近30天的已完成订单”SQL 是WHERE user_id ? AND status FINISHED AND create_time ?。按规则user_id和status是等值条件create_time是范围条件所以联合索引设计成(user_id, status, create_time)。这个顺序能完整匹配查询而且create_time在最后也能参与范围定位不会影响前面的等值条件。如果随手建成了(create_time, user_id, status)那这条高频 SQL 只能用到create_time这一个前缀后面的等值条件全部浪费。6. 实操过程用EXPLAIN定位并优化慢查询6.1 一次完整的分析流程我处理慢 SQL 有一个固定套路这里分享出来你现在就可以拿去用。第一步开慢查询日志定位具体 SQLSET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;线上环境建议把long_query_time设成 1 秒开发环境可以设 0.1 秒。第二步拿到 SQL 后不要急着加索引先看表结构和数据分布确认条件列没有函数包裹、没有隐式转换。第三步执行EXPLAIN重点关注四个字段type、key、rows、Extra。type从好到差一般是system const eq_ref ref range index ALL。看到ALL就是全表扫描是优化重点。key是实际用到的索引如果为 NULL 说明没走索引。rows是估算扫描行数值越小越好。Extra看到Using filesort要考虑排序字段是否在索引里看到Using temporary要考虑分组、去重是否可以从索引层规避。第四步按前面讲的最左匹配原则设计联合索引测试前后执行时间。最后一步用EXPLAIN ANALYZEMySQL 8.0 支持看实际耗时确认优化结果。6.2 一个回表优化的完整例子假设一张用户订单表CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user (user_id) ) ENGINEInnoDB;慢 SQL 是SELECT order_no, amount, status FROM orders WHERE user_id 1001 AND status 1 ORDER BY create_time DESC LIMIT 10;我直接加联合索引(user_id, status, create_time)为什么这个索引既能用最左匹配定位user_id和status又能让create_time直接在索引里倒序扫描避免 filesort。但这里有个问题查询字段还包括order_no和amount这两个字段不在索引里所以还是得回表取十行回表次数是LIMIT 10完全能接受。如果查询频率高到夸张可以进一步把索引改成(user_id, status, create_time, order_no, amount)覆盖查询字段实现零回表。但代价是索引体积变大写入变慢。值不值取决于这个查询的量级。我一般建议先用三层联合索引观察一段时间只有热查询还是慢再考虑扩成宽索引。6.3 常见索引失效场景速查表场景写法原因正确做法函数处理WHERE DATE(create_time)2024-01-01无法在索引树上做范围定位改写成和的区间条件隐式转换WHERE mobile13800138000字符串字段被转成数值比较查询条件写成字符串字面量前导通配符WHERE name LIKE %张索引按前缀排序无法前缀匹配改造为LIKE 张%或全文检索范围查询后字段联合索引(a,b,c)查a1 AND b0 AND c1b范围之后的c失去有序性调整索引字段顺序把范围字段放最后使用 OR 连接WHERE a1 OR b2除非两边都是索引否则优化器容易放弃拆成两个 SQL 或用 UNION ALL索引列参与运算WHERE amount1100索引列被修改无法匹配索引键写成WHERE amount99上面表格里的案例我在真实环境都遇到过特别是隐式转换数据库字段是varchar业务方传了个数字类型参数MySQL 会自动把列转成数值索引直接失效。排查这类问题有个土办法在 SQL 前加EXPLAIN如果type从ref变成ALL十有八九就是列上发生了类型转换或函数运算。6.4 排序、分组、DISTINCT 的索引优化小技巧索引 B树的有序性不止能加速 WHERE 过滤还能加速排序和分组。ORDER BY create_time之所以慢是因为大多数情况下 MySQL 要对结果集再做一次内存或磁盘排序也就是 filesort数据量大时非常费时。如果排序字段恰好是联合索引的组成部分且前面的等值条件已经确定那 MySQL 可以直接按索引顺序读取不需要额外排序执行计划的 Extra 里就不会出现Using filesort。分组场景同理GROUP BY user_id, status如果联合索引的字段顺序和分组字段顺序一致MySQL 可以直接沿着索引顺序分段避免临时表。DISTINCT也是和索引顺序一致时效率极高。所以优化时不要只看 WHERE把 ORDER BY、GROUP BY、DISTINCT 里出现的字段一起纳入索引设计收益往往比单纯防回表更大。7. 常见问题与排查技巧实录7.1 主键索引和唯一索引到底有什么区别这个问题面试常问实际开发中也经常有人混着用。主键索引是聚簇索引它的叶子节点存整行数据一个表只能有一个主键索引而且主键列不允许为 NULL。唯一索引是二级索引叶子节点存索引键值加主键值一个表可以有很多唯一索引允许一个 NULL 值MySQL 里唯一索引允许多个 NULL但具体取决于版本和存储引擎。从约束能力上看两者都能防重复但从存储结构上看完全两码事。如果你只是想保证某个业务字段不重复比如订单号那就建唯一索引但别忘了它本质还是二级索引查询如果只走唯一索引且需要其他字段同样会回表。如果业务需求就是按这个字段定位整行记录那考虑是否把它设置为主键更合适前提是你愿意接受它作为聚簇键带来的写入和维护成本。7.2 为什么有时候明明有索引MySQL 却不用我在优化时经常被问“我建了索引为什么 explain 显示 type 是 ALL”这里有个很容易忽略的点优化器会基于索引基数、扫描行数、回表成本做综合估算。如果你查询条件WHERE sex male一张表里 90% 都是男性优化器觉得走索引还不如全表扫描就会放弃索引。这不是索引失效是优化器认为全表扫描成本更低。遇到这种情况正确做法不是逼它用索引而是从业务侧缩小结果集范围比如加上create_time的时间过滤条件让命中行数降到全表的百分之几。还有一个实战技巧用FORCE INDEX强制走索引做一次对比测试如果强制走的耗时的确比全表低那可能不是索引本身的问题而是统计信息过期或索引基数估算不准触发ANALYZE TABLE更新统计信息后再看。7.3 索引优化后写变慢了怎么办索引优化的本质是用写入维护成本换查询速度。每多一个索引插入、删除、更新时都要多维护一棵 B树所以一个表索引过多写入性能一定会下降。我见过有些表被建了十几个索引业务高峰期写入直接卡死。处理办法是控制索引数量一般来说单表单索引不超过五六个联合索引尽量覆盖多个高频查询。如果业务必须高性能写入可以考虑把索引从线上表拆到归档表或者用异步方式同步到分析库查询走只读副本主库只承担写入。这个属于架构层面的事但作为 DBA 或者后端开发心里要有这根弦索引不是银弹不是越多越好而是要跟业务查询模式对齐。7.4 常见的索引优化面试题和回答思路整理几个我经常拿来考别人的题目顺便给出标准回答思路供你自查B树是红黑树吗不是。红黑树是二叉平衡树B树是多路平衡搜索树非叶子节点可以存大量索引键树高更低更适合磁盘 IO 场景。辅助索引如何避免回表让辅助索引覆盖查询所需全部字段即构建覆盖索引。哪些场景会导致索引失效函数操作、隐式类型转换、前导通配符、OR 连接、范围查询后的字段、索引列参与运算等。为什么联合索引要求最左匹配因为联合索引的键值按第一列、第二列的顺序排列只有从第一个字段开始才能定义索引上的扫描区间。8. 收尾我自己的一些体会做了这么多年数据库优化一个很深的感受是索引优化不是一个孤立的 SQL 技巧它是一整套理解数据组织方式的思维方式。B树决定了 MySQL 的能力边界聚簇索引和二级索引的设计决定了回表必然存在覆盖索引和联合索引则是在这个框架里找最优解。你把这几层想通了再看 explain 输出看慢日志就不会再靠猜。最后再分享一个小习惯我每次上线新索引都会顺手多跑几条相关的 UPDATE 和 DELETE确认写入压力可接受并且观察一周慢查询数量用数据说话而不是只看单个查询变快就收工。索引是给业务服务的不是给 benchmark 服务的这个出发点摆正了优化方向就不会跑偏。