做 MySQL 优化绕不开索引这个话题。很多朋友碰到慢 SQL第一反应就是“给查询字段加个索引”加了之后发现有时候快有时候慢甚至有的 SQL 怎么加索引都不走反而全表扫描更快。我早些年也在这个问题上栽过跟头后来啃了一段时间的索引原理又看了几百条线上慢查询的执行计划才算把这块真正理顺。这篇文章就把我从原理到实战的完整思路梳理一遍包括索引为什么能加速、不同类型的索引适用什么场景、哪些坑会导致索引白建以及怎么用 explain 定位一条慢 SQL 的全过程。新手可以通读一遍建立整体认知有经验的同学可以直接跳到第三节和第四节看失效场景和实战排查应该会有一些值得对号入座的收获。1. 索引优化前必须搞懂的底层逻辑1.1 一条慢查询背后的真实代价先看一个很常见的场景。业务表里存了 500 万行订单数据你要查某个用户最近 20 笔订单SQL 长这样SELECT id, order_no, amount, create_time FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;没有索引的情况下MySQL 只能走全表扫描也就是从第一行数据开始把 user_id ! 10086 的行全部过滤掉符合条件的留下来然后做一次 filesort 排序最后取 20 行返回。听起来简单但这意味着 InnoDB 要把聚簇索引也就是主键索引的叶子节点全部扫一遍一条按主键顺序存放的表扫描 500 万行数据少说也要几百毫秒如果行宽比较大、数据不在内存里这个时间会膨胀到秒级。这还只是单条查询。如果这个接口的 QPS 是 100数据库的 CPU 和 IO 会瞬间被打满其他业务的查询也会跟着遭殃。索引优化的本质其实就是把“扫描 500 万行”变成“扫描几十行”。它像一个书的目录你不需要从第一页翻到最后一页找某个关键词而是先翻目录定位到页码再直接翻到那一页。查询优化器通过索引能快速定位到符合条件的记录指针再利用索引的有序性跳过大量无关数据。1.2 索引为什么选 B 树而不是哈希表或二叉树很多人一开始想不通索引不就是加速查询吗用哈希表不是更快吗O(1) 的查找效率不吊打树结构这个问题的答案藏在数据库的实际应用场景里。哈希索引确实能做到单条记录等值查询的 O(1) 复杂度但它有两个致命短板第一明明是根据区间范围查询的比如create_time BETWEEN 2024-01-01 AND 2024-06-30哈希结构完全无能为力它只能逐个去比对第二哈希索引存储时是无序的无法利用索引来排序ORDER BY create_time这类操作依然要额外排序。那二叉树呢平衡二叉树AVL 树查找复杂度是 O(log n)理论上很不错但数据库场景下索引是存在磁盘上的树的高度决定了查询时要做多少次磁盘 IO。一棵二叉树在 500 万行数据下高度大约是 23 层每次查询光沿路径找叶子节点就要读 23 个磁盘页而一次随机磁盘 IO 的耗时大概是 10ms 量级230ms 就出去了这还没算后续操作。红黑树虽然也是平衡树但同样存在树高的问题。B 树的核心设计是“矮胖”。一个节点能存多个键值MySQL 默认一个页大小是 16KB假设主键是 BigInt 类型占 8 字节再加指针大概 16 字节那么一个节点就能存大概 1000 个键值。高度为 3 的 B 树就能存 10 亿级别的数据1000 的 3 次方换句话说查询一条数据最多只需要 3~4 次磁盘 IO这在工程上是完全可接受的水平。B 树还有一个被很多人忽视的特点数据只存储在叶子节点并且叶子节点之间通过双向链表串联。这个设计让范围查询和排序变得极其高效。你查到一个起始位置后沿着链表往后遍历就能拿到整段有序数据不需要再回树中间反复跳转。1.3 聚簇索引与二级索引的关系InnoDB 的索引结构有个不太直观但特别重要的概念聚簇索引。每个 InnoDB 表只能有一个聚簇索引通常就是主键索引。聚簇索引的叶子节点直接存放整行数据意思是扫描到叶子节点后这一行的所有列数据都齐了不需要额外去别的地方取。二级索引也就是我们平时手动建的索引叶子节点存放的则是指向聚簇索引的主键值而不是完整的行数据。所以走二级索引查数据时会经历两个步骤先在二级索引的 B 树里找到主键值再拿这个主键值回聚簇索引树里找完整的行记录这个过程叫“回表”。理解这一点对索引优化至关重要。如果你查询的列已经全部包含在二级索引里MySQL 就不需要回表直接取二级索引叶子节点的值返回这叫“覆盖索引”。这也是为什么很多时候我们建议把查询列和条件列一并放进组合索引里省掉回表就是省掉一次 B 树搜索在千万级数据量下这个开销差异非常明显。2. 索引类型剖析别再傻傻分不清2.1 主键索引与唯一索引的区别面试和实际建表时主键索引和唯一索引是最容易被混淆的两个概念。从语义上讲主键索引和唯一索引都要求列值不重复但有几个本质区别一张表只能有一个主键索引但可以有多个唯一索引主键列不允许为 NULL唯一索引列允许有 NULL 且可以有多个 NULL 值主键索引在 InnoDB 里是聚簇索引叶子节点直接存整行数据唯一索引只是普通二级索引叶子节点存主键值。从存储和性能来说这个差异导致了唯一索引的查询会多一步回表操作。但唯一索引真正要注意的是它的约束语义业务上要保证某个字段不能重复比如用户表的手机号、订单表的业务单号就应该建唯一索引而不是靠应用层先查后插去判断。因为并发场景下两个请求同时查到不存在同时插入应用层的判断根本拦不住只有数据库的唯一约束才能兜底。我在实际项目里见过一个反例订单号没建唯一索引靠应用层做幂等判断结果在高并发重复请求下还是产生了重复数据最后只能写脚本清洗数据。所以唯一索引该建就要建不要拿应用逻辑去挑战数据库约束。2.2 普通索引与组合索引的应用差异普通索引是二级索引的默认形态它的价值在于加速单列查询。但实际业务中WHERE a 1 AND b 2这类多条件过滤才是最常见的。这时候你单独给 a 建一个索引再单独给 b 建一个索引MySQL 的优化器通常只能选择其中一个索引来用另外一个条件只能在回表后再做过滤效果并不理想。组合索引的威力在于它把多个列的排序信息整合进一棵 B 树。假设建了一个(user_id, status, create_time)的组合索引B 树的第一层按 user_id 排序user_id 相同的时候按 status 排序两者都相同时再按 create_time 排序。意味着查询条件里只要是按这个顺序前缀来过滤就能高效定位而ORDER BY create_time也可以直接走索引拿到有序结果省去 filesort。组合索引设计有一个核心原则叫“最左前缀”也是后面会说的高频失效点。最左前缀的意思是组合索引的生效必须从最左边的列开始。比如索引(a, b, c)查询条件里只带b和c用不到索引只有带a或者带a和b或者a、b、c全带才能走这个索引。这个规则是由 B 树的排序结构天然决定的——没有 a 参与的排序前提b 和 c 在树里的顺序就没有意义。2.3 覆盖索引与索引下推两个省事利器覆盖索引前面提到了就是查询列全部落在索引列中的情况。它最大的意义是避免了回表这在查询频繁的接口上收益非常明显。举个例子订单列表页只需要展示订单号、金额、状态、创建时间那建一个(user_id, order_no, amount, status, create_time)的索引查询的时候条件用 user_id完全走覆盖索引执行计划中 Extra 列会显示Using index代表不需要回表。再说索引下推这个特性是 MySQL 5.6 引入的优化很多人没意识到它有多好用。没有索引下推的时候二级索引查到主键值后要先回表取出整行再在服务层判断那些不在索引里的过滤条件。有了索引下推如果过滤条件里的列也包含在索引中那 MySQL 会在索引遍历过程中先做一遍过滤把不满足条件的直接过滤掉减少回表次数。比如索引是(user_id, status, create_time)查询条件是user_id 1 AND status PAID AND amount 100。amount 不在索引里传统做法是把所有 user_id1 且 statusPAID 的记录全部回表取出来再过滤 amount。开启索引下推后amount 条件的过滤逻辑会在存储引擎层提前处理虽然 amount 不是索引列但可以先在索引扫描时用一个“虚过滤”减少回表行数。优化器会在 Extra 列显示Using index condition。这类细节对压测场景的提升非常明显尤其是过滤率高的 SQL。3. 索引失效场景全盘点踩坑实录3.1 最左前缀原则组合索引的生死线这是我在代码评审里见到最多的一个问题。开发建了组合索引(a, b, c)业务上某个查询需要按 b 和 c 过滤以为索引会自动适配结果发现查询特别慢。看 explain 才发现key字段是 NULL索引根本没被用上。最左前缀失效的典型场景包括查询条件里只包含中间列或末尾列组合索引第一列参与了范围查询导致后面的列索引失效。举个具体例子索引(a, b, c)查询WHERE a 100 AND b 5这时候只有 a 列能用上索引的定位能力b 列过滤是在索引扫描过程中做的但 c 不会参与排序和定位。如果查询是WHERE b 5 AND a 100优化器帮助调整了顺序还是只用到 a。这里有个容易被忽略的“跳跃列”问题索引(a, b, c)查询条件带 a 和 c 但没带 bc 就没法完整走索引。因为 B 树排序是 a 相同再看 bb 相同再看 c缺少 b 这个中间层a 定位后 c 的顺序无法利用。这种场景想优化要么调整索引顺序把常用过滤列放前面要么根据实际查询重新设计组合索引。3.2 函数操作与隐式类型转换索引在计算面前无能为力索引列上做函数运算绝对是让索引失效的头号元凶。比如WHERE DATE(create_time) 2024-06-01虽然 create_time 有索引但优化器为了对每一行的 create_time 执行 DATE 函数就只能放弃索引扫描全表扫描。正确写法是改成范围条件WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00。注意范围条件对索引是完全友好的因为 B 树天然支持区间定位。隐式类型转换的坑也很深。最常见的就是字符串列没有加引号WHERE phone 13800138000phone 是 varchar 类型MySQL 会把常量转成字符串去匹配看起来没问题但实际上优化器为了匹配类型会在索引列的隐式转换上做文章导致索引失效。反过来如果是数字列和字符串常量比较同样有类型转换问题。经验法则索引列的类型是什么条件里就按什么类型传参别让 MySQL 去猜。我在网上看过一个很经典的案例用户表手机号字段 50 万的量一个WHERE phone 13800138000不小心把索引打失效查询时间从 5 毫秒涨到 800 毫秒。排查半天发现 int 常量和 varchar 列比较时MySQL 会把 phone 列转换成数字再比较。这就是隐式类型转换真实付出的代价必须加引号写WHERE phone 13800138000。3.3 不等于、LIKE 与 OR 连接的注意点很多人以为只要有索引就万事大吉其实索引能高效处理的操作本质是等值查询和范围查询。!和这种不等于操作优化器很难用 B 树做定位因为它需要排除一大片不连续的数据代价不如直接全表扫描。同样的道理NOT IN这类否定操作也很难走索引。模糊查询要看情况。LIKE abc%这种前缀匹配是可以走索引的因为字符串排序时以 abc 开头的记录在树里是连续的一段。但LIKE %abc和LIKE %abc%就无能为力了前导的 % 让优化器无法确定起始位置只能全表扫描。业务里真的需要后缀匹配可以考虑反向存储或者引入搜索引擎不要在数据库里死磕。OR 连接也有讲究。如果WHERE a 1 OR b 2a 有索引b 没索引MySQL 可能无法走索引因为 OR 意味着要么走 a 的索引拿到结果又要全表扫描去拿 b 的结果优化器评估下来觉得不划算干脆全表扫描。只有当 OR 连接的每个条件列都有独立的索引可用时MySQL 才有可能走索引合并(Index Merge)优化。更稳妥的方式是拆成两条 SQL 用 UNION ALL 合并或者在业务层直接分开查。3.4 优化器的“自我判断”有索引不一定是好事还有一个让新手一脸懵的情况索引明明存在条件也完全满足最左前缀但 explain 结果显示全表扫描。我要说的是有时候这是优化器的合理选择倒不一定是问题。优化器决定是否走某个索引核心依据是估算扫描行数和回表代价。如果索引的区分度太低比如性别列只有“男”“女”两个值你查WHERE gender M全表扫描可能只需要扫一半数据而走索引的话除了扫描索引每一行还要回表取完整数据。两相比较全表扫描反而更快。这种情况叫“回表成本高于全表扫描成本”。优化器判断选择的依据是索引基数cardinality也就是索引列有多少个不同的值。用SHOW INDEX FROM table能看到这个字段。如果统计信息过期优化器可能做出错误的判断。遇到这种“该走索引但不走”的情况可以先执行ANALYZE TABLE重新收集统计信息再不行就考虑用FORCE INDEX强制走索引但强制索引要谨慎往往说明索引设计本身有问题不是长久之计。4. 索引优化实战一条慢 SQL 的完整诊疗过程4.1 用慢查询日志定位目标 SQL理论聊了不老少现在说说实战流程。我接到一个线上告警某订单分页查询接口在高峰期平均响应时间到了 3 秒多数据库 CPU 报警。第一步不是猜而是把慢 SQL 挖出来看看。先在数据库里开启慢查询日志并设置阈值线上环境通常设 1 秒比较合理。配置如下slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON重启或动态设置后跑一段时间然后用 mysqldumpslow 工具汇总分析慢日志里的热门 SQL。例如mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这条命令会按照查询耗时排序把最耗时的 10 条 SQL 列出来。配合-s c还能按次数排序找出调用频率最高的慢 SQL。拿到 SQL 之后我会先看它的表结构和现有索引然后执行 explain 分析执行计划。4.2 Explain 执行计划关键字段解读explain 是索引优化里最重要的一把武器但前提是你真的能读懂它在说什么。我最关心以下几个字段type访问类型从好到差依次是system const eq_ref ref range index ALL。能看到ref或range基本正常看到ALL就要警惕全表扫描了。key实际用到的索引名。NULL 说明没走索引。rows优化器预估需要扫描的行数数值越小越好。Extra这里藏了很多线索。Using index是好信号代表覆盖索引Using where是回表后再过滤Using filesort代表额外排序Using temporary代表用到临时表优化空间很大。拿之前的慢查询来看SQL 是查用户的订单列表按创建时间排序分页。explain 出来 type 是 ALLrows 显示 480 万Extra 显示Using filesort。这就是典型的全表扫描 额外排序慢的根因已经很清楚了。4.3 真实案例改造从 3 秒到 30 毫秒目标 SQL 原始状态SELECT id, order_no, amount, status, create_time FROM orders WHERE user_id 10086 ORDER BY create_time DESC LIMIT 20;表里有一个user_id的单列索引但 explain 显示 type 是index表示它扫了整个二级索引去排序再回表过滤 user_id所以依然很慢。这里的核心问题是索引能定位 user_id但返回结果需要按照 create_time 排序而单列 user_id 索引内部并没有 create_time 的顺序于是 filesort 不可避免。改造方案是建一个(user_id, create_time)的组合索引。这样 B 树在 user_id 相等的情况下已经按 create_time 排好序查询时直接定位到 user_id 10086 的第一条记录沿着索引顺序往下读最多 20 条就拿到了结果。ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time);再加一个覆盖索引的变体把查询列也塞进去避免回表ALTER TABLE orders ADD INDEX idx_user_create_time_cover (user_id, create_time, id, order_no, amount, status);建完索引后再次 explaintype 从 ALL 变成了refrows 从 480 万降到 20Extra 没有Using filesort变成了Using index。压测一下平均耗时从 3.03 秒降到 31 毫秒效果立竿见影。这个案例里真正的优化点是理解排序需求。SQL 里带了ORDER BY create_time而旧索引没有这个字段的连续性导致排序无法走索引。组合索引的设计不只是考虑 where 条件还要考虑 order by 和 group by这一点经常被忽略。4.4 进一步优化分页深翻页的代价很多订单列表接口除了前几页还会被人翻到几百页。如果 LIMIT 的偏移量很大比如LIMIT 100000, 20即使走索引MySQL 也得先扫描索引前 100020 条然后丢掉前 100000 条这个成本随着偏移量增长而线性上涨。深翻页优化的常用手段是用上一页的游标。比如记录上一页最后一条数据的 create_time 和 id下一页查询直接写成WHERE user_id 10086 AND (create_time 2024-06-01 10:00:00 OR (create_time 2024-06-01 10:00:00 AND id 123456)) ORDER BY create_time DESC LIMIT 20;这样每一页查询都只需要走索引定位起点然后往下扫 20 条不管翻到第几页成本都是恒定的。这个思路在数据量大的列表页里非常实用。5. 索引维护策略建好索引只是开始5.1 冗余索引与重复索引排查建索引不是越多越好。在我经手的项目里见过很多表上有五六个索引但实际使用率很高的可能就两三个。索引多带来的直接问题是每个索引都是一棵独立的 B 树插入、更新、删除时所有索引都要同步维护写入慢不说还额外占磁盘空间。最常见的问题是重复索引和冗余索引。PRIMARY KEY和另一个UNIQUE KEY加在同一列上就属于重复。从 InnoDB 的实现来说唯一索引和普通索引的区别只是约束校验存储结构类似没必要叠着建。冗余索引说的是组合索引的前缀被单独索引重复覆盖。比如已经有(a, b, c)的索引又单独建了a的索引后者就是冗余的因为(a, b, c)已经能完全覆盖以 a 为前缀的查询。但反过来如果只建了(a, b, c)没有单独的b索引那就不算冗余因为前者无法支持以 b 开头的查询。排查冗余索引可以借助 MySQL 5.7 的 sys 库视图sys.schema_redundant_indexes这条 SQL 能帮你把重复和冗余的索引列出来非常方便SELECT * FROM sys.schema_redundant_indexes;5.2 索引碎片与统计信息维护索引随着频繁的插入、删除会产生页分裂和碎片导致 B 树的页空间利用率下降范围扫描的 IO 次数变多。表数据量很大时可以定期执行OPTIMIZE TABLE来重建表和索引。但这里要提醒一句OPTIMIZE TABLE会锁表线上执行一定要选低峰期或者考虑用在线 DDL 工具比如 pt-online-schema-change来避免长时间阻塞业务。统计信息的更新同样不能忽视。MySQL 优化器依据索引基数做决策如果统计信息不准会出现错误的全表扫描。ANALYZE TABLE可以手动触发统计信息重新采样在批量灌入大量数据后建议顺手执行一次。另外innodb_stats_auto_recalc默认开启正常情况下会自动统计但在大表上自动统计的触发频率可能不够。5.3 一套可直接落地的索引设计自查清单最后整理一份我在实际工作中沉淀下来的索引设计自查清单每条背后都对应着一个踩过的坑为每个查询的核心过滤列建立索引优先组合索引而非多个单列索引。组合索引的列顺序按“等值条件列在前范围条件列在后”排列。把ORDER BY、GROUP BY涉及的列纳入组合索引设计尽量避免 filesort 和临时表。高频查询尽量用覆盖索引把 select 需要的列包含进来避免回表。避免在索引列上使用函数、表达式和隐式类型转换。对区分度极低的列如性别、状态位谨慎建独立索引很可能被优化器放弃。每张表索引数量控制在 5 个以内优先保证写入性能。定期用 sys.schema_redundant_indexes 清理冗余索引。新增索引后观察一段时间用 performance_schema 或慢日志确认索引被真实使用没被用的索引及时删除。索引设计说到底是一门权衡的艺术查询要快写入也不能太慢存储成本也得控制。没有一套配置适合所有业务最靠谱的方法还是结合 explain 和线上流量做针对性优化。从我个人这些年的经验来看索引优化最大的难点不是不懂原理而是没有建立“先分析、后动手”的思维习惯。每一条慢 SQL 上线前都跑一遍 explain每新增一个索引都确认它真的被用上把这两个习惯坚持下来线上数据库的稳定性会有一个质的提升。