很多人在面试的时候能把索引背得滚瓜烂熟B树、最左前缀、覆盖索引、回表……但真正到了线上一条慢查询打过来能快速定位到是索引问题、并且能给出合理优化方案的人其实并不多。我见过不少同事建索引全凭感觉查询条件里出现什么字段就给什么字段加索引结果一张表上挂了十几个单列索引写入越来越慢查询却没快多少。这篇文章不打算重复教科书上那些概念而是结合我这些年踩过的坑把MySQL索引从设计到验证、再到日常维护的完整思路捋一遍。无论你是后端开发、DBA还是刚接触数据库的新人只要按照这个思路去分析慢查询基本都能找到方向。1. 索引到底是什么先搞懂B树这棵大树的底层逻辑1.1 没有索引的时候MySQL是怎么找数据的在没有可用索引的情况下InnoDB 对表的查询几乎是纯体力活从表的第一行开始一行一行往下扫直到把所有行都检查完这就是我们常说的全表扫描。在线上环境里我经常在慢查询日志里看到几百万行的表一条查询直接拖到几百毫秒甚至几秒十有八九就是缺索引或者索引没被正确使用。为什么全表扫描这么慢对于 InnoDB 来说数据是按主键聚簇存放的也就是说 B树的叶子节点上完整地保存了每一行数据。如果没有索引可用优化器只能把整棵树的叶子节点全部读一遍I/O 量几乎等于读完整张表。对一张千万级别的表来讲即使按照顺序读也会产生大量的磁盘 I/O 和内存拷贝。拿生活里的场景类比一下全表扫描就像一本没有目录、没有章节号的字典你要找一个字只能一页一页翻。这个行为在数据量小的时候还能忍受几千行全表扫也就几毫秒但数据量一旦上去了响应时间就会断崖式变差。很多新人容易忽略的是全表扫描并非一无是处当表很小或者查询需要读取超过一定比例的数据时全表扫描反而比走索引更高效。这也是为什么有时候你明明建了索引优化器却选择了全表扫描后面我会专门讲到这个点。1.2 B树索引结构为什么适合数据库MySQL 索引最常见的底层数据结构是 B树。为什么不是哈希表、二叉搜索树或者普通 B 树这个问题的答案几乎决定了我们对索引使用的理解深度。哈希表虽然能在等值查询时做到 O(1) 的查找速度但它天生不支持范围查询也无法做排序。SQL 里最常见的WHERE id 100、ORDER BY create_time这类操作哈希结构基本帮不上忙。二叉搜索树会随着数据不断插入而失去平衡最坏情况退化成链表查询性能不可控。普通 B 树虽然已经比较矮胖但它的非叶子节点也存储了实际数据这样在范围扫描时需要在不同层级之间反复回溯效率不够流畅。B树的设计恰恰解决了这些痛点只有叶子节点才存实际数据内部节点只存索引键这样每个节点能容纳更多键值整棵树可以做得很矮很宽。以 InnoDB 默认 16KB 的页大小为例如果一行索引键加上指针大约占几十字节三层高度的 B树往往就能容纳几千万条记录。这意味着查询一两千万行的表通常只需要两到三次磁盘 I/O就能定位到目标数据所在的叶子页。另一个关键设计是叶子节点之间通过指针连成有序链表。这让范围查询变得非常流畅找到一个起始位置后沿着链表往后顺序读就行避免了跨层回溯。BETWEEN、ORDER BY、以及分页查询这类操作B树都能给优化器提供清晰的扫描路径。理解了这层设计你就能明白为什么索引在排序和范围查询场景下如此重要。1.3 二级索引、回表与索引覆盖除了主键对应的聚簇索引普通字段上的索引都叫二级索引。二级索引的叶子节点里存的不再是完整数据行而是索引键和主键值。当查询条件命中二级索引但需要读取的列不在索引里时MySQL 还要拿着主键值回到聚簇索引里再查一次这个过程叫回表。这里就到了一个非常关键的优化点如果 SELECT 需要读取的所有列都恰好包含在某个索引中InnoDB 可以直接从索引叶子节点上拿到数据不需要回表这就是覆盖索引。我曾经处理过一个列表接口原来查询无论怎么调都慢最后把查询列全部纳入联合索引执行计划的 Extra 字段里出现 Using index 之后性能立刻上了一个台阶。理解回表和覆盖索引对后续设计联合索引至关重要。你设计索引时不仅要看 WHERE 条件用到了哪些列还要想清楚 SELECT 了哪些列。两者同时纳入视野才算一个完整的设计思路。尤其是那种高频查询的列表页覆盖索引往往是性价比最高的优化手段它能同时减少 I/O 次数和回表带来的随机读开销。2. 索引设计实战字段怎么选、联合索引怎么排2.1 区分度是索引设计的先决条件很多人建索引的规则是“查询条件里有什么字段就加什么”这个习惯其实非常危险。一个字段是否适合建立索引最先要看的指标是区分度也就是这一列有多少种不同的值。区分度的计算可以简单写成COUNT(DISTINCT column) / COUNT(*)。这个比例越接近 1说明列的取值越分散索引筛选能力越强。性别、状态、是否删除这类只有几个固定值的字段区分度非常低。如果单独给它们建索引一次匹配可能还会命中几百万行索引的意义就大打折扣了。我见过一个实际案例某张业务表有 800 万数据status 字段只有 5 种取值团队给 status 单独建了个索引。结果查询WHERE status 1时优化器算了一下发现走索引要匹配 160 万行再回表还不如全表扫描快于是执行计划直接放弃了索引。这就是低区分度字段单独建索引的典型困境。低区分度字段并非完全不能用但通常要放在联合索引靠后的位置或者与其他高区分度字段配合使用。比如一条查询里既能用 user_id 过滤到几千行又能用 status 进一步过滤到几百行那么联合索引 (user_id, status) 就很有价值单独拿 status 建索引则基本属于浪费空间。判断一个索引有没有价值不是看语法上能不能建而是看它到底能把扫描范围缩小到什么程度。2.2 联合索引的最左前缀原则联合索引的存储规则决定了它和多个单列索引有本质区别。以 (a, b, c) 联合索引为例B树叶子节点上的数据会先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。理解这个顺序就能理解为什么 MySQL 只认最左前缀。所谓最左前缀就是查询条件里必须从联合索引的最左边一列开始匹配索引才可能被用到。(a, b, c) 这个索引能服务于 (a)、(a, b)、(a, b, c) 三种查询条件组合。如果查询只针对 b 或者 c 作为过滤条件这个联合索引通常帮不上忙。有些开发者以为三个字段建了一个索引所有组合都能优化这是个很常见的误区。联合索引的真正优势在于它让等值条件下的多列过滤变成了一次索引范围的精确扫描。举个例子一个订单表有联合索引 (user_id, status)查询WHERE user_id U10001 AND status 1时InnoDB 可以非常精确地在索引树中定位到 user_id 为 U10001、status 为 1 的那一段叶子节点不需要一个一个回表看数据。还有一点容易被忽略范围条件之后的列会停止匹配。如果查询是WHERE a 1 AND b 10 AND c 2那么索引最多用到 a 和 bc 列没法继续参与索引定位只能等回表后再过滤。设计联合索引时通常建议把等值条件的字段放在左边范围条件的字段放在右边排序字段再进一步考虑。这样能让索引发挥最大作用。2.3 单列索引和联合索引该怎么取舍面试里常问“多个条件查询为什么要建联合索引而不是多个单列索引”实际工作里这个问题的答案非常真实多个单列索引对优化器的选择非常不友好。旧版本的 MySQL 虽然有 index merge 优化但大多数情况下优化器只会挑选其中一个区分度较高的索引去扫回表后再过滤其他条件。这意味着你建了三个单列索引但一次查询大概率只能“吃掉”其中一个另外两个除了浪费磁盘空间和维护开销几乎没有实际收益。而联合索引因为把多个条件按顺序保存在一起可以一次扫描完成多条件筛选回表次数也会更少。当然联合索引也不是越多越好。联合索引本身占空间且每次写入、更新都要同步维护索引树索引过多会显著拖慢写入性能。更麻烦的是联合索引之间存在互相覆盖的可能有了 (a, b) 索引再建 (a) 单列索引就比较冗余。我自己在做索引维护时会把那些“可以被其他索引替代的多余索引”清理掉这一步对高写入场景的表来说收益非常明显。3. 用 EXPLAIN 验证索引一次慢查询优化全过程3.1 读懂 EXPLAIN 里的几个关键字段索引到底有没有生效不能靠猜要以 EXPLAIN 的执行计划为准。看执行计划看多了之后我基本只盯几个关键字段type、key、key_len、rows、Extra。type 字段表示访问类型从好到差大致是 const、eq_ref、ref、range、index、ALL。看到 ALL 说明是全表扫描这是最危险的状态index 表示扫描整个索引树虽然比全表扫描好一点但也不代表高效range 是索引范围扫描常见于 BETWEEN、IN、大于小于这类条件ref 是等值匹配二级索引是比较理想的状态const 是主键或唯一索引等值查询性能最好。key_len 用来判断联合索引真正用到了几列。拿联合索引 (user_id, status, create_time) 来说如果执行计划里的 key_len 只覆盖了前两列的长度说明第三条索引列没有参与本次查询的定位。计算 key_len 时要考虑字符集比如 utf8mb4 下一个 varchar(32) 字段本身占 128 字节再加 2 字节长度和可空标识最终可能到 132 左右。不需要背精确公式但要知道这个值越大通常意味着索引利用得越充分。Extra 里的信息更需要重视。Using index 表示用到了覆盖索引全程没回表Using index condition 表示用上了索引下推ICP能在索引层过滤一部分数据Using filesort 表示排序没有走索引需要额外做文件排序数据量大时是性能黑洞Using temporary 则往往和 group by、去重等操作相关。执行计划里如果同时出现 Using filesort 和 Using temporary基本可以断定这条 SQL 还有很大的优化空间。3.2 从 1.6 秒到 30 毫秒一个订单分页查询的真实优化过程我之前接手过一个后台订单查询接口功能很简单根据用户 ID 查订单列表按创建时间倒序分页返回。表数据量大约 700 万接口在高峰期经常超时用户端反馈“列表页转圈圈转很久”。最初 SQL 长这样SELECT id, order_no, status, create_time FROM orders WHERE user_id U20240001 ORDER BY create_time DESC LIMIT 20;EXPLAIN 的结果让我印象深刻type: ALL possible_keys: NULL key: NULL rows: 682340 Extra: Using where; Using filesorttype 是 ALL直接全表扫还带 Using filesort。问题很明确表结构里只有主键索引没有任何二级索引能辅助 user_id 过滤和 create_time 排序。优化器只能先把 68 万行全部扫出来再对结果做一次完整排序最后才取 20 条返回。优化方案是加一个贴合查询的联合索引ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);再跑一次 EXPLAIN结果变成type: ref possible_keys: idx_user_time key: idx_user_time key_len: 132 rows: 38 Extra: NULLrows 从 68 万直接降到几十行文件排序也消失了。因为联合索引的叶子节点已经按照 user_id 和 create_time 排序对于同一个用户的订单InnoDB 可以直接从匹配范围的尾部往前取 20 条不需要把匹配行全部捞出来再排一次。上线之后我又复测了几轮单次查询从原来的 1.6 秒左右稳定在 30 毫秒内接口 99 分位耗时也明显下降。这里给一个实操上的建议加完索引之后不要只看 SELECT 是否变快还要观察 INSERT、UPDATE 的耗时是否在可接受范围内。像这种订单表写入量本身就大加索引之前最好先在测试环境评估一下写入链路的压力变化。3.3 覆盖索引带来的下一步优化同样是上面这个查询如果想彻底避免回表可以把 SELECT 涉及的列也放进索引。比如 order_no、status 是高频 SELECT 字段把索引扩展成 (user_id, create_time, order_no, status)Extra 里就会出现 Using index整个过程不再访问聚簇索引。不过这种方案需要权衡索引列变多存储空间和维护成本都会增加单个索引页能容纳的键数量减少索引体积变大会反过来影响缓存命中率。实际操作中我通常先看接口的核心瓶颈在哪里。如果 30 毫秒已经能满足业务要求就没必要为了追求 Using index 盲目加长索引。性能优化不是把执行计划做到最完美而是把耗时控制在业务能接受的范围同时保证复杂度和维护成本可控。这里也想提醒一下不是所有 SELECT 的列都适合塞进索引。像长文本、大 JSON 这种字段放进索引会直接把索引体积撑爆通常的做法是只在 SQL 里查询必要字段或者把大字段拆到单独的表里避免拖垮主表的索引效率。4. 索引失效的几大场景与排查清单4.1 常见失效场景索引设计得再好只要 SQL 写法不配合索引照样不生效。下面这几种情况是我在线上排查慢查询时遇到最多的。第一类是函数包裹索引列。WHERE DATE(create_time) 2024-01-01这种写法的意思是先对 create_time 做函数运算再比较结果索引树上的值没法直接匹配。类似问题还有WHERE YEAR(create_time) 2024。正确做法是改成范围条件create_time 2024-01-01 AND create_time 2024-01-02这样索引才能正常走 range 扫描。第二类是隐式类型转换。比如 mobile 字段在表里是 varchar 类型查询写成WHERE mobile 13800000000MySQL 会把字符串列转成数字去比较转换过程导致索引列上发生函数操作索引随之失效。这类问题排查起来特别隐蔽因为类型转换是数据库内部做的EXPLAIN 里不会给出很显眼的提示只看到 type 变成 ALLrows 飙得很高。第三类是前导模糊查询。WHERE name LIKE %abc因为无法确定前缀索引的排序和定位优势完全发挥不出来。反过来WHERE name LIKE abc%就可以走 range 范围扫描。如果业务确实需要后缀模糊搜索可以考虑全文索引或者额外冗余一个反向字符串字段不要让这种查询硬打主表索引。第四类是对索引列做运算。WHERE id 1 100这类写法同样会导致索引失效应该改写成WHERE id 99。MySQL 在优化阶段不会替你把等式两边的运算消除掉索引列一旦参与运算就无法按原始值去定位。还有一种比较容易被忽视的场景是 OR。如果 OR 条件中的某个列没有可用索引优化器往往直接放弃索引走全表扫描因为无法保证每一分支都能高效命中。遇到这种情况解决办法是给所有 OR 列都加上索引或者把 SQL 改写成 UNION把有索引和无索引的查询拆开执行让优化器可以选择更优的访问路径。4.2 大表在线加索引的实操经验我们总说“直接执行一条 ALTER TABLE 加索引就行”但真正要在几百万行甚至更大的生产表上加索引事情没那么简单。InnoDB 从 5.6 开始支持在线 DDL添加二级索引时允许并发 DML但这并不代表没有代价。大表加索引有几个典型影响第一个是主库 DDL 期间仍然可能产生较大的磁盘 I/O 压力因为要重建二级索引树第二个是如果通过主从复制让从库也执行 DDL从库延迟很可能会被拉高。我踩过这类坑曾经在写流量较大的表上直接跑 ALTER TABLE结果主库会话堆积、从库复制延迟飙升。后来总结了一条经验生产环境大表加索引优先放在业务低峰期执行并提前确认磁盘空间足够同时观察主从复制延迟必要的时候用并行复制参数来缓解。需要回退时也要小心。索引一旦创建删除它同样不是瞬间完成的事也会触发索引页的回收。所以给大表加索引之前最好是先在测试环境用完整数据量跑一遍 DDL记录耗时再决定正式执行的窗口。临时表、备份表这些手段虽然老套但在关键时刻能兜底。4.3 索引统计信息与优化器选错索引有时候明明有合适的索引执行计划却选了别的索引甚至全表扫描这通常是优化器基于统计信息做了“错误”判断。InnoDB 的统计信息是从采样页里估算出来的如果表的数据分布发生较大变化统计信息可能没有及时更新。解决办法不是直接上手 FORCE INDEX而是先刷新统计信息ANALYZE TABLE orders;。刷新之后优化器通常会给出更合理的执行计划。如果刷新之后依然选错再考虑使用 FORCE INDEX 指定索引。不过 FORCE INDEX 属于强制手段一旦数据分布再次变化手里这个索引可能不再是最优解所以线上使用要非常克制并且要写清楚注释说明为什么强制指定。另外一个细节是优化器对“预计扫描行数”和实际回表成本之间的权衡不一定准确。在二级索引上MySQL 要逐行回表才能判断是否满足 WHERE 条件如果匹配的行数太多优化器宁愿选择全表扫描而不是走二级索引。这也能解释为什么低区分度字段单独建索引经常不生效优化器快速估算一下觉得走索引还不如全表扫。遇到这种情况要从根本上改变查询条件或者重新设计联合索引而不是跟优化器较劲。5. 索引日常巡检与维护工具速查5.1 如何查看现有索引与潜在冗余索引不是建完就一劳永逸的业务变化后原来的索引可能变成累赘。我比较习惯用SHOW INDEX FROM orders;查看表上的索引情况但这个输出太原始实际维护时更多会对比列顺序和查询场景。目标是找出冗余索引。判断标准很简单如果一个索引的列是另一个联合索引的最左前缀那它大概率可以被覆盖。例如有一个联合索引 (user_id, status)同时又存在 user_id 单列索引后者就是冗余的。这种冗余索引浪费的是每次写入时的维护开销在高写入场景下影响非常明显。也可以直接查询系统元数据比如 information_schema.STATISTICS 和 sys.schema_unused_indexes。后者能直接列出未被使用的索引对日常巡检非常有帮助。遇到长期不用的索引建议先在业务代码里完全确认没有隐性使用再下线避免直接删除后突然被打回。有趣的是一些团队喜欢把所有可能用到的索引都“一次性建好”美其名曰以防万一。这种做法恰恰是索引膨胀的根源。索引数量越多写放大越严重查询优化器的选择成本也越高。理想的策略是根据实际慢查询和业务 SQL 的分布来动态维护而不是靠拍脑袋预防式建索引。设计索引永远服务核心查询链路不为偶尔跑一次的后台报表任务牺牲主链路性能。5.2 慢查询日志与执行计划常态化检查我把慢查询日志的检查当成了日常习惯。long_query_time设置 1 秒或更小慢日志里出现的每一条 SQL我都会用 EXPLAIN 重新看一眼。倒不是为了“批量性能优化”而是通过这些执行计划来判断未来数据量增长后的风险。如果一个 SQL 当前虽然只查了 5 毫秒但 EXPLAIN 显示 type 是 ALLrows 接近整表大小一旦数据翻倍它很可能就变成慢查询。这种隐患是慢查询优化里最值得提前处理的类型。反过来如果 type 已经是 refrows 也只有几十行那基本可以放心很多短时间内不太会出问题。在维护索引这件事上我还倾向于定期观察索引页的碎片情况。InnoDB 表经过大量随机删除、更新后索引页可能变得稀疏占用空间变大扫描效率下降。必要时可以通过 OPTIMIZE TABLE 重建表和索引但要注意这个操作在超大表上的耗时和资源消耗依旧要安排在低峰期执行。一条 SQL 经常无法用上索引的时候我更愿意退一步想想 SQL 逻辑本身是不是有优化空间。比如把函数处理挪到应用层完成、把 OR 改成 UNION、把分页条件从 LIMIT 大偏移改成基于上一页最大 ID 的游标模式。这些做法往往比纠结单个索引更有效而且能从根源上降低数据库的压力。5.3 几条实用经验总结文章写到这里把我这些年跟 MySQL 索引打交道的核心体会整理一下。不要凭感觉建索引先用区分度和核心查询场景做判断。区分度太低、查询本身命中数据量过大的字段建了也是白建。写完任何一条涉及多条件筛选的 SQL都养成跑一次 EXPLAIN 的习惯。执行计划里的 type、key_len、rows、Extra 四个字段就能回答 90% 的“为什么慢”问题。联合索引的列顺序要从“等值优先、范围其次、排序最后”这个思路去设计。这个顺序排错了索引实际价值会大打折扣。线上改索引必须有回退方案且要在业务低峰期执行。大表 DDL 不是一句 ALTER TABLE 就能草率搞定的。保持索引数量的克制。一个表的索引数量超过 5~6 个之后写入性能的劣化往往会超过新增索引的查询收益需要非常谨慎地评估。索引是手段让查询快速返回结果才是目的。这一点我一直写在团队的开发规范里也是每次评审表结构时最常强调的一句话。慢查询排查的次数多了之后你会发现真正需要你记在脑子里的不是各种复杂的索引语法而是一个朴素的思考习惯这条 SQL 想要定位的数据到底被存在哪里、怎么才能最快找到它。最后再多说一句每次优化的答案都藏在执行计划里。不需要背下所有语法细节但一定要能看懂 EXPLAIN 正在做什么。慢通常不是 MySQL 的问题而是我们对数据的访问路径还不够理解。把这条路径理清了索引设计自然会跟着清晰起来。