1. 一次线上慢查询让我重新理解了索引上个月排查线上问题有个查询平时几十毫秒某天突然跑到三秒多直接拖垮了下游服务。看了一圈代码逻辑没毛病数据量也才百万级问题只能出在索引上。查看执行计划发现优化器放弃了我以为“必然命中”的联合索引从头到尾扫表。那一次之后我意识到很多写了好几年SQL的人都停留在“会加索引”的阶段真正遇到索引失效、执行计划飘忽不定的场景还是一头雾水。这篇内容就是给想进阶SQL优化、把索引吃透的同学准备的。我会从底层存储结构讲起解释索引为什么能提速、为什么有时加了索引反而更慢再结合具体的失效场景和EXPLAIN执行计划定位问题最后分享一些我从实际项目里总结出来的索引设计教训。无论你是后端开发、DBA还是刚接触数据库优化的新手这里的内容都直接可落。索引不是什么玄学。把一个查询变快本质是让数据库少读数据让查询变慢本质是优化器选了一个高成本的路径。搞懂索引先得理解数据在磁盘上怎么存的然后你才能看懂优化器在做什么。2. 索引提速的本质从全表扫描到B树查找2.1 全表扫描慢在哪里很多人没想过一个问题一张表在磁盘上是怎么组织的绝大多数关系型数据库表数据以数据页Page为单位存储一个数据页通常是16KB大小可以存若干行记录。查询数据时磁盘IO的最小单位就是数据页——哪怕你只需要一行数据库也会把这16KB整个读进内存。全表扫描之所以慢不是因为数据库“一行一行找”而是它要把这张表占用的所有数据页都读一遍。假设一张表100GB分布在几万个数据页上全表扫描就是几万次磁盘IO。磁盘顺序读虽然快一些但和内存访问速度相比还是有量级差距如果是机械硬盘更是灾难级别。关键点在于全表扫描的代价和数据量严格正相关。数据量翻倍扫描代价翻倍。这就是为什么你的表到百万、千万级别之后原来“还能跑”的查询突然变得不可接受。数据量增长SQL性能问题才真正爆发。2.2 B树查找为什么快索引的核心结构是B树。为什么是B树而不是二叉搜索树或哈希表这是数据库设计者反复权衡后的结果。B树的每个节点可以容纳多个键值通常一个节点就是一个数据页大小能放几百个键树的高度极低——三千万行数据B树高度通常只有3到4层。这意味着从根节点到叶子节点最多4次磁盘IO就能定位到目标数据页。对比二叉搜索树三千万行数据需要约25层最坏情况25次磁盘随机IO性能差了一个数量级。B树的“宽矮”形态天然适配磁盘存储层数少IO次数少节点大小和页大小对齐一次IO拉一个完整节点。哈希索引定位是O(1)看起来比B树更优但它不支持范围查询、不支持排序、不支持前缀匹配。业务查询中“大于某值”、“位于区间”、“按某个字段排序”的场景极其普遍哈希索引根本接不住。所以绝大多数关系型数据库的默认索引结构是B树。2.3 回表对性能的隐性影响搞清楚回表之前先理解一个底层事实一张表的InnoDB主键索引就是表本身。数据行挂在主键索引的叶子节点上这就是“聚簇索引”。除了主键以外的所有二级索引叶子节点上存的是主键值不是行数据。那么问题来了如果你在name字段上建了二级索引查询条件WHERE name 张三查找过程是两步——先在二级索引中定位到name对应的叶子节点取到主键id再用这个id到主键索引聚簇索引中查一次取出完整的行。第二步就叫回表。回表本质是一次按主键查找通常是点查成本不算高。但如果你的查询过滤条件很宽命中了大量二级索引记录比如返回5000行那就意味着5000次主键点查。这5000次如果是随机磁盘IO性能直接崩掉。所以我强调每次SQL写完之后问自己一句“这个查询会不会大量回表”。如果会就想想有没有办法避免——覆盖索引通常是答案后面第四章我会展开讲。3. 索引失效场景复盘不只是“函数别套列”这么简单3.1 隐式类型转换优化器直接不认账我见过最多的索引失效场景就是隐式类型转换。表中user_id字段定义成varchar存储的其实是数字字符串查询时传入了整数SELECT * FROM user_info WHERE user_id 12345;如果你以为MySQL会自动把12345转成12345没事你就踩坑了。实际情况是当字段是字符串类型、传入的是数值类型时MySQL的隐式转换会选择把字段列那一侧做类型转换也就是对user_id这一列套了一次CAST函数。一旦索引列出现在函数内B树的排序结构就失效了优化器只能放弃索引。判断标准很简单看等号两侧哪一侧是字段本身若是字符串列和数值比较则字段被转换。反过来如果你传入的是加了引号的字符串12345则不需要转换字段列索引可以正常使用。这类问题很难通过看代码发现最有效的排查手段就是EXPLAIN看type字段是不是从ref退化成ALL。我在项目里做过一次统计这类隐式类型转换导致的慢查询占了索引相关故障的三成以上。3.2 对索引列做运算优化器无从下手B树索引能快速查找的前提是查询条件能够利用列的原始排序结构。一旦索引列参与了运算这个前提就没了。常见的形式有三类-- 函数处理 SELECT * FROM orders WHERE DATE(create_time) 2025-03-01; -- 隐式运算 SELECT * FROM orders WHERE price 10 100; -- 字段与字段运算 SELECT * FROM orders WHERE a b 20;DATE(create_time)虽然写法直观但你让优化器怎么用索引它不知道B树里哪个节点对应DATE(create_time) 2025-03-01这个计算结果。正确姿势是改成范围查询SELECT * FROM orders WHERE create_time 2025-03-01 00:00:00 AND create_time 2025-03-02 00:00:00;这样优化器可以直接在B树上做区间定位快速找到起始位置再顺序扫。后面这个写法少一次函数计算却能让索引真正生效。类似的price 10 100可以改写成price 90。永远记住索引列要保持“裸”状态所有计算都放到查询条件的一侧去。3.3 LIKE前缀模糊匹配索引的天然缺陷LIKE %keyword%这种写法是索引无法使用的经典场景。B树索引结构是按值排序的它能帮你定位“开头的值”但没法定位“包含某个子串的值”。keyword%这样以通配符结尾的模式没问题索引可以用%keyword或%keyword%索引就直接失效。不过有一种折中方案。如果确实需要做包含式搜索且数据量大、检索频繁我会建议业务方引入全文索引或者专门做分词服务而不是在数据库里硬扛。如果数据量不大LIKE模糊查询也无所谓但一定要明白它走的是全表扫描。另一个可行思路是使用前缀索引配合额外字段比如存一个name_reverse字段反转头尾顺序LIKE %keyword%可以变相转换成LIKE keyword%的查询这个技巧适合有特定业务形态的场景但一般用户用不上了解即可。3.4 联合索引顺序错乱最左前缀原则联合索引(a, b)和两个单列索引(a)、(b)是完全不同的东西。联合索引先按a排序a相同的记录再按b排序。这意味着只有查询条件里用了a才能享受到这个索引的顺序结构。WHERE a 1 AND b 2能走到两个条件都命中WHERE a 1能走到WHERE b 2无法使用联合索引——b在B树上不是第一排序键WHERE b 2 AND a 1优化器会调整顺序依然能走很多同学把“最左前缀”理解成“SQL语句里where条件的书写顺序”这是不对的。优化器最终会进行条件重排判断的是实际用到了哪些列。关键是联合索引的第一列必须出现在查询条件中且这个条件不能是范围之后被阻断的字段。还有一个很容易忽略的点范围查询会阻断后续索引列。比如WHERE a 1 AND b 2联合索引的a列被范围条件占据后b列无法继续作为索引条件进行等值匹配。优化器会选择range方式a列的索引效果还在但b列只能做过滤索引本身不会再参与。这就是为什么设计联合索引时需要把高频等值条件的列放前面范围条件的列放后面。3.5 优化器说“不用索引”不代表索引没用有一种情况最容易让人困惑明明索引没失效符合所有使用条件但执行计划显示typeALL全表扫描。为什么优化器会放弃索引因为它回表成本太高。举个真实案例某个表有个status字段取值范围只有0、1、2三个值分布严重不均90%数据是status1。你给status建了索引查WHERE status 1的时候优化器一算如果走索引需要回表90%的行等于一次范围大的随机IO风暴还不如直接全表顺序扫描来得快。于是它放弃索引选择全表扫描。这不是优化器蠢而是它比你更清楚成本模型。此时如果你强制FORCE INDEX大概率性能反而更差。我的建议是遇到这种“选择性太差”的字段不要建普通索引更不要强迫走索引而是考虑改变存储结构比如加汇总表、引入全文索引或使用覆盖索引。索引不是越多越好选择性太差的字段建索引基本是负收益。4. 用EXPLAIN执行计划给索引“拍CT”4.1 type字段里的性能等级直接翻译成本EXPLAIN输出的第一行type字段是判断一条SQL是否高效的最直接指标。从好到差大致是type含义性能const / system主键或唯一索引点查最多一行极佳ref普通索引等值匹配可能多行很好range索引范围扫描如、BETWEEN、IN良好index全索引扫描遍历整棵索引树一般ALL全表扫描遍历整个聚簇索引差我来解释一下日常排查的优先级。如果看到ref或range说明索引正常工作可以继续看rows和filtered确认扫描代价如果看到ALL先回头看WHERE条件是不是触发了第三章说的失效场景再决定要不要加索引。index全索引扫描常常被忽视——它虽然用了索引但本质上是遍历整棵索引树性能并不比全表扫描好太多区别只是它读取的是更小的索引页而不是更大的数据页。4.2 rows和filtered别只看“有没有走索引”很多工程师看执行计划只看type和key觉得“走了索引就没事”。这个认知太粗糙了。同样一条ref类型的查询可能扫描100行就结束也可能扫描100万行。差别就在rows和filtered上。rows是优化器估算的需要读取的记录数filtered是经过查询条件过滤后剩下的比例。例如rows100000、filtered1意思是优化器预计读取10万条记录最终只剩1%即1000条。这里有一个重要信息如果filtered很低说明索引选的过滤字段区分度不够大量行被读出来却用不上。优化器的估算通常基于统计信息如果你的表长时间没更新统计信息或者数据分布发生明显变化估算就可能失真——这也是为什么要定期ANALYZE TABLE。我在排查线上慢查询时遇到filtered很低的情况第一反应不是加索引而是重新审视查询条件的设计是不是去掉某个过滤条件后可以走一个更窄的索引或者改变查询条件的顺序让选择性更高的列先参与索引匹配这些判断离开EXPLAIN是没法完成的。4.3 一个完整的慢查询定位案例前阵子某项目有个查询开始变慢原始SQL大致长这样SELECT * FROM payment_record WHERE order_no 20250315001 AND status 2 ORDER BY create_time DESC LIMIT 10;EXPLAIN结果typeALLkeyNULLrows800000。一眼看出这是全表扫描。奇怪的是order_no明明有单列索引为什么没走继续看表结构发现order_no字段类型是varchar(64)但查询传入的20250315001本身是字符串类型没有隐式转换问题。再看一眼原来存储引擎是MyISAM不对是InnoDB所以问题不在这。低头细看那张表的order_no单列索引虽然存在但所有查询走的是(order_no, status)这个联合索引单列索引其实是冗余的。但为什么联合索引也没被用上最后通过SHOW INDEX FROM payment_record查索引基数发现统计信息严重过期这张700万行的表索引基数记录只有几百。优化器以为通过索引要扫描所有行计算后认为回表成本高于全表扫描于是放弃。执行ANALYZE TABLE payment_record刷新统计信息后同样的SQL变成了typeref查询耗时从2.8秒降到30毫秒。这个案例的核心教训是执行计划并不是每次查询都实时计算最优路径的它依赖统计信息做成本估算。表数据量剧烈变化后记得关注统计信息是否更新。5. 联合索引与覆盖索引的高级用法不止是“建索引”而已5.1 联合索引的底层逻辑最左前缀背后的排序规则很多人建联合索引就是凭感觉把需要的列排个序建完拉到。但要知道联合索引(a, b, c)本质上是建立了一个复合排序规则先按a排序a相同再按bb相同再按c。所以想要在一条SQL里最大化利用这个索引查询条件的组合就必须符合这个顺序。最经典的例子有个表查询列是(province, city, district)如果命中SQL是WHERE province 广东 AND city 深圳联合索引完美命中如果SQL变成WHERE district 南山索引完全用不上。因为district是第三列前面没有province和city的等值条件它在B树上的顺序对当前查询而言是“打乱的”优化器没有能力跳过前两列直接按第三列定位。设计联合索引时我有一套自己的判断流程找出高频查询里的等值条件列按出现频率排出候选顺序区分度高的列放前面区分度低的放后面范围查询列必须放在所有等值条件列之后结合业务实际避免在联合索引里混入每次查询都不同的列这套流程不一定产出理论最优索引但能保证最常用的大多数查询命中率高并且不会造出一堆乱序组合。5.2 覆盖索引把回表直接消灭在索引层覆盖索引是索引优化里最实用的进阶技巧之一。它的定义很简单一个索引包含了SQL查询所需要的所有列那么查询过程就不需要回表直接从索引树里拿到全部数据。假设表t_order有联合索引(buyer_id, create_time)现在要查询某买家最近10笔订单的下单时间SELECT create_time FROM t_order WHERE buyer_id 10086 ORDER BY create_time DESC LIMIT 10;这条SQL不需要回表buyer_id用于索引定位create_time就是索引的第二列数据直接从索引树的叶子节点取出。相比每次回表去聚簇索引里拿行数据覆盖索引省掉了大量的随机IO。但覆盖索引不能滥用。如果SQL里有SELECT *无论索引设计得多好都必然回表因为二级索引叶子节点不存储整行数据。所以一个常用的优化手段是把查询需要用到的字段尽量收敛用覆盖索引消除回表。我在实际项目里见过一个极端案例一个查询返回的五个字段全部包含在联合索引里SQL从1.2秒优化到40毫秒只是把查询字段从*改成了需要的五列。优化没有增加任何索引只是让已有索引从“部分利用”变成了“完全覆盖”。这类优化收益极高改动却极低很值得在日常开发中养成“查询字段收敛”的习惯。5.3 索引下推优化器在索引层帮你做了预过滤关于索引下推Index Condition PushdownICP很多资料讲得比较晦涩我用一个案例说明。假设表t_user有联合索引(age, name)查询SELECT * FROM t_user WHERE age 20 AND name LIKE 张%;在旧版本数据库没有ICP机制时执行逻辑是先用索引定位age 20的区间然后逐条回表拿到整行在服务层判断name LIKE 张%。也就是说name条件虽然也在联合索引里但只被当作“事后过滤”条件。有了ICP之后执行逻辑变成在索引遍历的过程中直接在索引层就检查name LIKE 张%不满足条件的不回表。这个优化直接减少了从索引到聚簇索引的回表次数。前提是name条件原本就在索引列中否则ICP也帮不上忙。ICP的效果在过滤条件多、WHERE里可下推条件占比大的查询里尤其明显。查看执行计划里Extra字段显示Using index condition就说明ICP生效了。MySQL 5.6之后默认开启ICP不需要额外配置。这部分机制比较底层但对理解“为什么我给联合索引加了一个‘没用’的列后查询反而快了”非常有帮助。6. 索引设计的避坑经验有些坑不是踩一次就完了6.1 冗余索引的代价比表面看到的更大大多数人对冗余索引的认知停留在“浪费存储”上所以他们觉得问题不大。但实际上索引对写入的影响比存储的浪费严重得多。每插入一行数据所有相关索引都要同步更新每次UPDATE如果改了索引列的值对应的索引树节点要重新调整位置。索引越多写入放大的倍数越大TPS越高这个代价越明显。举一个真实例子。某订单表本来有联合索引(order_no, status, create_time)后来某个开发图省事又建了单列索引(order_no)。从查询角度看后者完全冗余前者天然支持WHERE order_no ...。但因为多了一个索引每次批量插入订单时数据库需要额外维护一颗索引树批量导入1万条数据的时间从2秒变成了5秒。验证之后删掉冗余索引写入时间恢复。删除冗余索引之前先跑一遍业务核心SQL的EXPLAIN确认没有其他查询依赖这个单列索引再动手。6.2 低区分度字段建了也白建有一张用户表里面有个gender字段值只有男、女、未知。我给这个字段建了索引回调查了一遍各值占比发现查询流量基本集中在gender 男而“男”占全表的48%。这种字段的索引基本无效——走索引回表一半数据不如直接全表扫。低区分度字段正确的处理方式不是“建索引并祈祷”而是要么接受全表扫描要么用组合索引让这个字段和其他高区分度字段组合起来使用。实际项目里(gender, last_login_time)这样的联合索引是有效的因为gender负责粗筛last_login_time负责把范围缩小。单独给低区分度字段建索引大概率是负收益。另一个常见误区是给状态字段建唯一性不强的索引。订单表里status字段若只有0、1、2、3四个值分布可能相对均匀查status 0能过滤掉75%数据但依然回表量不小。这种场景最稳妥的做法还是加组合索引或者让范围查询配上时间字段。6.3 前缀索引与长字段的取舍如果要索引的字段是超长字符串例如URL、备注、或者某个很长的标识串直接建索引会导致索引树节点容纳的条目数变少树变高IO变多。解决办法是前缀索引——只取字段前N个字符建索引。但前缀索引有两个坑得注意一是无法用于ORDER BY和GROUP BY因为索引里只存了前N个字符完整值没存二是选择性可能下降如果字段前几个字符区分度很低比如存的是带固定前缀的编号你取10个字符做前缀索引可能还是大面积重复。实际选择前缀长度时可以用“估算区分度”的方法执行SELECT COUNT(DISTINCT LEFT(url, 20)) / COUNT(*) AS selectivity FROM t_page;对比不同前缀长度下的区分度比例找到一个接近全列区分度且长度可接受的值。一般建议区分度在0.9以上再考虑使用太小的话不如直接全列索引或者另想方案。6.4 删除索引的窗口期与验证策略最近有个项目因为一张大表索引太多7个索引写入延迟明显上升业务方要求清理。我的做法是分三步走统计所有索引的使用频率从慢查询日志、开启performance_schema后的统计信息里找出哪些索引从未被命中对于疑似冗余的索引在测试环境用业务核心查询脚本回归所有核心SQL的EXPLAIN确认没有生产依赖选择业务低峰期删除先删一个观察几个小时的CPU、IO、慢查询情况确认无误再删下一个这个策略听上去稳妥其实也依赖一个前提慢查询日志覆盖度够高。如果业务有大量低频但重要的报表查询没有跑在统计窗口你删掉的索引很可能在月末结算那天引发事故。说句实在话我开始也删错过一个看起来“完全没用”的索引后来月末对账查询直接全表扫描被业务同事找了好几次。所以删除索引这件事宁可保守不要激进。6.5 全表扫描不一定最差索引全扫描才是隐形杀手有一种很容易被忽视的性能陷阱typeindex即全索引扫描。表面看执行计划里用了索引但实际是把整棵索引树从上到下扫一遍。这种情况常常出现在排序查询里ORDER BY create_time且create_time刚好是某个索引的列。优化器可能选择直接扫描索引树利用索引有序性避免filesort但代价是它要读取这个索引的所有叶子节点。如果你的索引很大这个代价接近全表扫描甚至更糟因为索引页比数据页密度高读的页数可能更多。判断标准依然是EXPLAIN的rows和实际耗时。如果typeindex但rows接近表行数就该考虑查询本身是不是数据量过大、过滤条件太少。这类问题的根治方案通常是缩小查询范围而不是调整索引。7. 写在最后把索引思维变成下意识我接触过不少开发同学谈到索引能说出不少理论但一处理实际问题就抓瞎。差距不在理论储备而在“是否把索引当成查询设计的一部分而不是事后补救的工具”。每写一条SQL如果脑子里能自动跑一遍字段类型匹配吗函数有没有套在列上联合索引的顺序对不对会不会大量回表EXPLAIN的type是什么级别很多慢查询事故压根不会发生。还有个小习惯我特别推荐在自己负责的业务模块里建立一个查询清单把核心SQL对应的执行计划快照定期留档。数据量增长后执行计划是否变化、索引是否还在生效通过对比快照一目了然。这个方法不能解决所有性能问题但至少能让你在业务数据翻倍之前提前发现隐患。索引优化不是做完一次就结束了它是和数据增长同步演进的持续工作。翻过“会加索引”这道坎真正理解优化器怎么选路、为什么选这条路你在SQL这条路上才算真的进阶了。
