1. 索引优化先搞清楚 MySQL 到底在背后做了什么先聊点实在的。不管你是刚接手一个线上业务库还是自己写着玩的小项目只要数据量过了百万级早晚会遇到同一个问题SQL 越来越慢系统越来越卡老板越来越急。很多人第一反应就是“加索引”但加了索引之后呢有时快如闪电有时纹丝不动甚至更慢。这时候你就得停下来想一想MySQL 的索引到底是怎么工作的为什么同样是走索引有的查询能到毫秒级有的却依然全表扫描如果你去翻 MySQL 的官方文档会看到索引被描述为“帮助 MySQL 高效获取数据的数据结构”。这句话听起来很像废话但它的分量很重——索引的本质就是把你原本需要“从头翻到尾”的数据查找变成“按目录直接翻到那一页”。在关系型数据库里这个“目录”最常见的物理载体就是 B Tree。理解了 B Tree 的查找逻辑你基本就理解了 MySQL 索引优化的底层逻辑。这篇文章不是什么高深论文也不是把官方文档抄一遍。我打算从原理讲到实操从慢查询分析讲到索引设计中间穿插我自己在真实业务里踩过的坑以及最终总结出来的、可以直接拿去用的优化套路。适合谁看刚接触索引优化、想知道为什么加了索引还是不快的初级开发也包括写过不少 SQL、想系统梳理索引知识的后端工程师。看完这篇文章你至少能回答这几个高频面试题主键索引和唯一索引有什么区别哪些场景会导致索引失效以及当你的 SQL 慢到不能忍的时候第一步应该做什么。2. 索引为何能让查询“快如闪电”2.1 从 B Tree 说起为什么不是二叉树也不是哈希表先说一个结论MySQL InnoDB 引擎的索引默认都是 B Tree 结构。很多人背过这个结论但不理解为什么。你可以把 B Tree 想象成一棵“扁而宽”的树。它的特点是所有数据都存储在叶子节点非叶子节点只存索引键值叶子节点之间通过指针串联成一个有序链表。这意味着两件事。第一无论你要找的数据在树的第几层从根节点走到叶子节点的路径长度几乎相同也就是说查询耗时非常稳定。第二因为叶子节点是排好序且相连的做范围查询比如WHERE age BETWEEN 20 AND 30的时候只要找到起点顺着链表一路往后扫就行不需要反复回树里找。那为什么不用二叉树二叉树在数据量大的时候会变得很深比如 1000 万条数据二叉树可能要 20 多层每层一次磁盘 IO20 多次 IO 就很要命了。而 B Tree 因为每个节点可以存储多个键值树的层数通常只有 3 到 4 层也就是说一次查询最多只需要 3 到 4 次磁盘 IO这个效率完全不在一个量级。也有同学问为什么不用哈希索引哈希索引的查找速度更快理论上是 O(1)。但哈希索引最大的问题是它只支持等值查询不支持范围查询也不支持排序。你的业务里只要有、、BETWEEN、ORDER BY这类操作哈希索引就废了。所以 InnoDB 默认用 B Tree本质上是在“查找效率”和“功能覆盖范围”之间做了最优权衡。2.2 聚簇索引与二级索引回表到底是什么InnoDB 的索引还有两个必须分清楚的概念聚簇索引clustered index和二级索引secondary index。聚簇索引就是主键索引。InnoDB 的表数据本身就是按主键聚集存储的也就是说表的每一行数据就挂在主键索引的叶子节点上。你通过主键查询直接就能拿到整行数据不需要额外操作。如果你建表时没有定义主键InnoDB 会找一个非空的唯一索引代替连唯一索引都没有它就会生成一个隐藏的 row_id 作为聚簇索引。二级索引则是你额外创建的普通索引比如CREATE INDEX idx_name ON users(name)。二级索引的叶子节点存储的并不是整行数据而是“索引列的值 主键值”。所以当你通过二级索引查询时第一步是通过 B Tree 找到对应的主键值第二步还得再用这个主键值去聚簇索引里查一次完整行数据这个过程就叫“回表”。这里就引出了一个非常核心的优化思路如果 SQL 需要的所有列都已经包含在二级索引里那就不需要回表了这种索引叫“覆盖索引”。比如你有SELECT name, age FROM users WHERE name 张三如果你建的是(name, age)联合索引那么 MySQL 在二级索引的叶子节点上就已经拿到了 name 和 age根本不用回表。这在高频查询场景下是巨大的性能提升。3. 索引设计不是越多越好而是刚刚好3.1 常规索引、唯一索引与主键索引的真实区别很多人搞不清这三者的关系我这里直接说结论。主键索引是聚簇索引一张表只能有一个通常不允许为 NULL它的叶子节点存的是完整行数据。唯一索引是二级索引一张表可以有多个它允许有一个 NULL 值在 InnoDB 里多个 NULL 值其实也是允许的因为 NULL 被视为互不相等它的叶子节点存的是“唯一索引列 主键值”。普通索引没有任何限制叶子节点同样是“索引列 主键值”。所以面试题“主键索引和唯一索引的区别”答案很清晰物理结构不同、数量限制不同、叶子节点存储内容不同以及主键索引不需要回表唯一索引在绝大多数情况下需要回表。这里有一个实操经验要分享不要把唯一约束和业务逻辑混在一起。比如用户表里手机号要求唯一很多人直接就把手机号设成唯一索引这没问题。但如果你在多个业务表里都为了“防重复”去加唯一索引就要仔细想一想插入数据时MySQL 做唯一性检查是有额外开销的。如果这个表写入量极大唯一索引会明显拉低写入性能因为每次插入都要先查一遍、再判断冲突。不如把唯一性校验放到应用层去处理数据库层只保留必要的唯一索引。3.2 联合索引的“最左前缀原则”联合索引是工作中用得最多、也最容易踩坑的索引类型。它遵循一个核心规则最左前缀原则。假设你建立了一个联合索引(a, b, c)实际上 MySQL 会按 a、b、c 的顺序先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。所以它能高效命中的查询条件组合只有三种a、a,b、a,b,c。如果你直接查b或者c这个索引基本帮不上忙因为索引的整体顺序已经被 a 决定了没有 a 作为前缀b 和 c 就是无序的。这个原则直接决定了你设计联合索引时的字段顺序。一个常见的经验法则是把“区分度高”的字段放在前面把“经常用于等值查询”的字段放在前面把“范围查询”的字段尽量往后放。举个例子WHERE status 1 AND created_at 2024-01-01如果 status 只有 0 和 1 两种取值区分度很低把它放前面意义不大。但如果查询模式固定是“先按 status 过滤再按时间排序”那(status, created_at)反而更合适因为 status 的等值过滤能迅速缩小数据范围created_at 则负责排序和范围筛选。说句实话没有完美的索引设计只有适合当前业务查询模式的索引设计。你在建索引之前先把自己业务里最频繁执行的 10 条 SQL 拿出来看看它们的 WHERE 条件、排序字段、关联字段再决定怎么建。这才是索引设计的正确起点。3.3 索引下推MySQL 帮我们做的额外优化讲联合索引时经常有人忽略一个内置优化——索引下推Index Condition Pushdown简称 ICP。这个功能默认开启它做的事情是在索引遍历过程中提前对索引包含的字段进行条件过滤减少回表次数。举个例子有联合索引(name, age)执行SELECT * FROM users WHERE name LIKE 张% AND age 20。在 MySQL 5.6 之前InnoDB 只能先根据name LIKE 张%找到一批主键然后回表查出完整行再在服务层过滤 age。有了索引下推之后InnoDB 在索引遍历阶段就会判断 age 20 这个条件age 不匹配的直接跳过回表次数大大减少。这个优化我实测过在数据量百万级、name 前缀匹配出的结果集很大的场景下开启和关闭 ICP 的耗时差距能接近一倍。所以有些时候你写 SQL 觉得“已经走索引了怎么还是慢”不妨看看 EXPLAIN 里有没有Using index condition这个标记就表示走了索引下推但还能继续优化。4. 索引失效的经典场景与排查实操4.1 那些年我们一起踩过的失效坑网上流传的“索引失效”清单很多但很多人只会背不会排查。我把自己在真实业务里踩过、也帮别人排查过的场景列出来每条都带案例说明这样你遇到类似问题时能一眼识别。第一类对索引列使用了函数或计算。比如WHERE DATE(created_at) 2024-01-01哪怕 created_at 上有索引这个查询也不会走索引因为 MySQL 必须先对每一行的 created_at 做 DATE() 运算才能和常量比较。正确的写法是范围查询WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00。这是一个极其常见的慢查询原因。第二类隐式类型转换。如果索引列是 varchar 类型你查的时候却用了数字WHERE phone 13800138000MySQL 会把字符串列转换为数字再比较导致索引失效。反过来数字列用字符串查一般没问题但为了统一规范条件值的类型最好和列类型完全一致。第三类模糊查询的左侧通配符。WHERE name LIKE %张三%无法使用索引因为 B Tree 是按前缀排序的你要从中间截一段去匹配顺序就丢了。如果你的业务确实需要这种模糊搜索建议考虑全文索引或者 Elasticsearch而不是死磕 MySQL。第四类联合索引不满足最左前缀。刚才讲过(a, b, c)索引你只查 b 或 c索引直接失效。还有一点要注意如果最左字段是范围查询后面的字段也无法用于索引过滤但前面的等值字段可以。这个细节很多人容易忽略。第五类OR条件连接。比如WHERE a 1 OR b 2如果 a 和 b 不是都建了索引MySQL 可能放弃索引改成全表扫描。因为 OR 意味着只要任意一个条件满足就返回如果某个字段没有索引MySQL 必须先全表扫一遍才能准确返回。解决办法是给 OR 两端涉及的字段都建索引或者拆成两个查询用 UNION ALL 合并。4.2 用 EXPLAIN 定位慢查询排查索引失效和慢查询EXPLAIN 是绕不开的工具。你不需要背所有输出列但必须会看几个重点字段。type 列是最直观的访问类型指示器。常见的值从好到差依次是system const eq_ref ref range index ALL。ALL就是全表扫描通常是要尽量避免的index是扫描了整个索引树虽然没有全表扫但也不理想range是范围扫描可以接受ref和eq_ref是使用了非唯一索引或唯一索引等值匹配表现不错const是主键或唯一索引等值查询理论上是最优。key 列表示实际用到的索引ken_len 列表示索引字段的最大字节长度rows 列是 MySQL 预估需要扫描的行数filtered 列是经过条件过滤后剩余行的百分比。我通常的习惯是先看 type 是否到了 range 或更好然后看 key 是否命中了预期索引最后看 rows 是否过大。如果你发现 type 是 ALL 或者 rows 数值大得离谱就说明这条 SQL 的索引利用情况很差需要回头检查条件写法或索引设计。实操时有个小技巧在 EXPLAIN 后面加FORMATJSON可以看到更详细的成本估算信息。当索引选择器在多条候选索引之间纠结时JSON 输出里会包含considered_execution_plans你能直接看到每一条可选索引的成本方便判断 MySQL 为什么会选那个“看起来不合理”的索引。4.3 实战案例一条从 3 秒到 50 毫秒的查询我之前在某个电商项目里处理过一个典型慢查询SQL 大概是这样的SELECT order_id, user_id, amount, status FROM orders WHERE user_id U123456 AND create_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 23:59:59 ORDER BY create_time DESC LIMIT 20;orders 表当时有三千多万行这条 SQL 跑了将近 3 秒。EXPLAIN 一看type 是 ALLrows 预估扫描了三百多万行key 是 NULL。问题的根源是 orders 表只有一个主键查询里使用的 user_id 和 create_time 完全靠全表扫描过滤。后面我做了两件事。第一加了联合索引(user_id, create_time)正好覆盖这个 SQL 的 WHERE 条件和 ORDER BY 字段。第二顺手把查询字段调整为SELECT order_id, user_id, amount, status由于这些字段中没有 create_time无法完全覆盖所以还是需要回表查字段但回表量已经控制在 20 条以内成本低到可以忽略。最终这条 SQL 从 3 秒降到了 50 毫秒以内。这个例子说明一个道理慢查询优化不是每次都要上缓存、上分库分表很多问题就是一条联合索引的事。很多团队一慢就上 Redis上消息队列最后还是没解决根本问题。先看索引再看 SQL 写法最后才考虑架构层面的改造这是成本最低、效果最明显的优化路径。5. 索引优化做到位的几个额外关键点5.1 覆盖索引让回表不再发生前面提到覆盖索引我再展开说说。覆盖索引的定义是查询所需要的所有列都能在二级索引的叶子节点上直接找到。查询不需要回表访问主键索引里的完整行。这个优化在统计类 SQL 里效果尤其明显。比如SELECT COUNT(*) FROM orders WHERE status 1如果你有(status)索引MySQL 直接扫索引树上的 status 列就能数出来不需要碰数据行。如果查询里还有其他字段比如SELECT status, COUNT(*) FROM orders GROUP BY status那么(status)索引也足够用了。我在实践中还发现一个细节覆盖索引可以显著减少磁盘 IO但它会占用更多存储空间因为索引里存的列更多了。所以不要为了覆盖而无脑把表里所有字段塞进一个索引。一般只对高频查询中需要返回的少量字段做覆盖比如用户列表页需要查 id、name、avatar 这 3 个字段那就建一个(name(长度), id, avatar)之类的索引前提是你的查询条件能命中这个索引。5.2 索引基数与区分度的取舍索引的“性价比”怎么衡量一个很重要的指标是基数cardinality也就是索引列中不同值的数量。基数越高说明这个列的区分度越好。比如一个用户表性别列的基数只有 2几乎区分不了数据手机号列的基数接近表行数区分度极高。MySQL 的优化器在决定走不走某个索引时会参考索引的基数来估算扫描行数。如果区分度太低优化器认为即使走索引也要扫描大量行不如直接全表扫。所以你在选择索引列时尽量选基数高、业务查询频繁的字段。如果某列的区分度确实很低但又经常作为查询条件那可以考虑把它放在联合索引里专门用于过滤和减少后续计算量但别指望它能单独成为高性能索引。这里分享一个教训我曾经在一个活动表里给is_deleted只有 0 和 1 两种值单独建过索引结果 EXPLAIN 显示 MySQL 完全不走直接全表扫。因为 90% 的行都是 0索引扫描一大半数据还带回表优化器一算账就知道不划算。后来我把is_deleted和其他查询字段组合成联合索引才真正发挥价值。低区分度字段单独建索引基本是浪费空间。5.3 排序与分组避免 filesort 和临时表ORDER BY 和 GROUP BY 也是索引优化的重点。如果排序字段没有索引MySQL 就需要把结果集先放进内存或磁盘临时文件里做排序EXPLAIN 里会看到Using filesort。同理GROUP BY 没有索引时也容易使用临时表。要避免 filesort最好的办法就是让排序字段出现在某个索引中并且保证排序字段的顺序和索引顺序一致。比如索引(user_id, create_time)执行WHERE user_id U123 ORDER BY create_time DESC时MySQL 可以直接利用索引的顺序完成排序不需要额外的排序操作。但如果是ORDER BY create_time ASC, user_id DESC排序方向不一致也可能导致额外的排序。GROUP BY 稍微复杂一点如果只关心分组后的统计值可以在索引里包含分组字段和聚合字段比如(status, amount)这样GROUP BY status就不用生成临时表。不过分组查询的优化空间通常有限业务层能用内存聚合解决的也值得考虑。5.4 索引维护成本写入变慢的代价必须心里有数很多人忽略一个事实索引不是免费的。每一次 INSERT、UPDATE、DELETEInnoDB 都要同步维护索引树。索引建得越多写入路径越长性能损耗越大。尤其是在高并发写入场景下一个表上有五六个索引每次写入都会拖垮性能。我处理过的一个真实事故某个日志表为了支持多种查询组合建了 4 个联合索引结果数据写入从单条毫秒级变成了几十毫秒级最终拖慢了整个业务的写入链路。后面我们把不常用的索引删掉保留两个核心查询索引写入时延才恢复过来。所以一个健康的索引策略应该是优先保证核心高频查询严格控制索引数量。一个表最多建议 5 到 6 个索引就差不多了再多就要警惕。定期用sys.schema_unused_indexes或者慢查询日志来识别从未被使用的索引然后果断删除这是降低维护成本最直接的手段。6. 写 SQL 本身也是一种优化6.1 SELECT 只取需要的字段这个点听起来很基础但很多人真的做不到。SELECT *在数据量大、字段多的情况下会带来两个问题传输数据量变大IO 和网络开销增加更重要的是它几乎不可能命中覆盖索引因为表的新增字段不在索引里时MySQL 只能回表取完整行。我自己比较习惯的做法是业务需要哪些字段SQL 就写哪些字段不多不少。尤其在使用二级索引的查询里把字段控制在索引包含的列内可以直接避免回表好处谁用谁知道。6.2 分页查询深翻页的坑LIMIT 1000000, 20深分页是另一个经典性能杀手。LIMIT 1000000, 20的意思是MySQL 要先找到前面 1000020 行然后丢掉前 1000000 行只返回最后 20 行。扫描行数并不会因为 LIMIT 的偏移量变大而变小而是随着偏移量线性增长。优化方案有两个。第一个是延迟关联先通过覆盖索引找到目标主键集合再用主键关联原表取完整行。例如SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE user_id U123 ORDER BY create_time DESC LIMIT 1000000, 20 ) tmp ON t.id tmp.id;这个写法让内层查询只走索引不碰数据行外层再根据 20 个主键精准回表。第二个方案是记录上次查询的最后一条 ID也就是“键集分页”WHERE id last_id ORDER BY id DESC LIMIT 20然后无限往下翻。这种方案更适合 App 列表场景但不适合任意跳页。6.3 批量插入与 UPDATE小步快跑更安全在业务高峰期做大批量 UPDATE 或 DELETE也是 DBA 最怕的事之一。一次 UPDATE 几百万行会持有大量行锁甚至可能升级为表锁严重影响在线业务。实操中我常用的方法是分批处理每次只更新一万行用LIMIT控制批次加一点 SLEEP 时间再继续下一批。虽然总耗时变长了但对线上影响能控制在非常小的范围。7. 从理论到实战一次完整索引优化的流程演示为了让你能把前面所有内容串起来我干脆模拟一个完整的优化流程你之后照着做就行。7.1 第一步定位慢 SQL打开 MySQL 慢查询日志配置如下slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time 1表示执行超过 1 秒的 SQL 都会被记录。log_queries_not_using_indexes ON可以顺便把不走索引的查询也记下来虽然有时会误伤一些本来就该全表扫的小表但对发现问题很有帮助。7.2 第二步EXPLAIN 分析执行计划拿一条慢 SQL例如EXPLAIN SELECT * FROM payment_records WHERE uid A100 AND pay_time 2024-05-01 00:00:00 ORDER BY pay_time DESC LIMIT 10;输出结果里如果 type 是 ALLrows 很大并且 key 是 NULL基本可以断定没有合适索引。接下来看一眼表的结构和现有索引结合业务判断需要创建什么索引。7.3 第三步设计索引并验证根据 SQL 的查询条件最合理是创建一个(uid, pay_time)联合索引。uid 是等值条件放前面pay_time 是范围条件和排序字段放后面。建好索引后再跑一次 EXPLAINEXPLAIN SELECT * FROM payment_records WHERE uid A100 AND pay_time 2024-05-01 00:00:00 ORDER BY pay_time DESC LIMIT 10;这时候 type 应该变成 rangekey 显示新索引rows 会大幅下降Extra 里也不再出现Using filesort。如果查询只需要 uid、pay_time、amount 这几个字段还可以把 amount 加进联合索引变成(uid, pay_time, amount)直接实现覆盖索引连回表都省了。7.4 第四步监控和回滚索引上线后继续观察慢查询日志看这条 SQL 是否还在慢日志里出现。如果出现再对比优化前后平均耗时。如果效果不理想或引入了写入性能问题可以用ALTER TABLE ... DROP INDEX快速回滚。线上操作尽量在低峰期执行并且先用SHOW PROCESSLIST确认当前没有大事务在跑。8. 常见问题速查与独家避坑清单结合我自己的经验把常见问题和排查方式整理成一个速查表你后面遇到症状时可以按图索骥。现象常见原因排查方法解决建议type 是 ALL 全表扫描查询条件列无索引或索引失效EXPLAIN 看 key 和 rows结合 WHERE 条件建联合索引有索引但 type 仍是 index索引无法高效过滤扫了整个索引树看查询条件是否满足最左前缀调整索引字段顺序或新建索引出现 Using filesortORDER BY 字段不在索引中观察索引是否包含排序字段设计联合索引包含排序字段出现 Using temporaryGROUP BY 或 DISTINCT 未走索引检查分组字段索引情况调整索引减少临时表回表过多导致慢查询二级索引查出的主键太多观察 rows 和回表次数使用覆盖索引或改写 SQL写入变慢索引过多或大事务查看写入期间 IO 和锁等待控制索引数量分批写入深翻页慢LIMIT 偏移量过大查看扫描行数延迟关联或键集分页还要提醒几个容易被忽略的细节。第一字符串类型的索引列在建表时最好设置合适的长度比如VARCHAR(64)不要图省事全用VARCHAR(255)。太长的列会让索引体积膨胀IO 开销变大。如果不得不索引很长的字符串可以考虑使用前缀索引比如INDEX(name(20))牺牲一点区分度换回性能。第二NULL值在索引中的处理方式容易让人迷惑IS NULL和IS NOT NULL在某些条件下可以走索引但查询条件写得不好照样全表扫。如果业务允许尽量给字段加NOT NULL DEFAULT默认值减少 MySQL 的判断负担。第三频繁更新的字段不太适合做索引因为每次更新都要改索引树结构。尤其在高并发场景更新热点字段加索引会放大锁竞争。9. 我个人的一点体会做索引优化这些年我最深的感觉是很多慢查询问题根本不需要多高深的技术缺的只是“回头看索引”的习惯。一条 SQL 跑慢了先去 EXPLAIN看看 MySQL 到底在干嘛再对症下药。比起动不动就上各种重量级中间件先把索引设计合理成本低、见效快、也不容易留后患。最后分享一个小技巧我每次改完索引或者优化完慢 SQL都会顺手记录到一个本地文档里内容包括原 SQL、EXPLAIN 结果、优化的索引设计和最终耗时对比。三个月后回头看这些记录能帮你发现很多业务模式的变化——比如某条 SQL 曾经优化得很好但随着数据增长或查询条件变化又开始变慢了。索引优化不是一劳永逸的它是个持续迭代的过程而记录恰恰是这个过程中最容易被忽略但最有价值的一环。