一张两千万行的订单表某个工作日下午接口响应从 30 毫秒涨到 1.8 秒值班同学第一反应是网络抖动第二反应是连接池不够用。我从监控里调出慢查询日志发现是一条再普通不过的“按用户 ID 查最近订单”的语句执行计划里 type 是 ALLrows 显示接近两千万。问题不在于服务器扛不住而在于这一列上压根没有一条能用的索引。数据库索引这个东西平时安安静静不刷存在感一旦缺位整条链路跟着遭殃。它本质上就是给数据建的一本目录书有五百页你要找“第 37 条提到成本核算的段落”没目录只能一页页翻有目录先翻到关键词页码再直达那一页。这篇文章我打算把索引这件事从头讲透包括它为什么快、什么结构、怎么建、怎么判断建得对不对、线上变更怎么不锁表、失效的几种典型写法以及我这几年在真实场景里踩过的坑。适合刚接手数据库的开发和运维同学也适合已经有几年经验、但一直靠“试试加个索引”来解决问题的朋友看完你应该能对一条慢查询给出确定性的判断而不是靠猜。1. 从一次慢查询说起索引到底解决了什么问题1.1 索引不是“加速开关”而是空间换时间的目录很多人对索引的理解停留在“加了就快”。这个说法不算错但它掩盖了真正需要理解的部分索引是额外维护的一份有序数据结构用额外的存储空间和额外的写入成本换取查询时更少的磁盘页读取次数。我们可以把数据表想象成一摞按插入顺序堆放的单据新单据永远压在最上面。当你要找“客户编号为 8848 的所有单据”时数据库没有别的办法只能从上到下逐张翻这就是所谓的全表扫描。磁盘读取是按块进行的每读一块就要一次寻道和一次传输两千万行数据可能分布在几十万个数据页上全部读完的时间累积起来就是秒级。而索引做的事情是把“客户编号”这一列单独抄一份并且按编号排好序同时记录每一条对应原始单据的物理位置。排好序意味着可以用二分查找几十万页里只需要走三四层就能定位磁盘读取次数从几十万次降到三四次。这个数量级的差距才是索引真正值钱的地方。我习惯用“页码表”来类比书末尾的索引页告诉你“成本核算在第 137、208、356 页”你不需要读完全书。但页码表本身也占纸张书越厚索引页越多而且每次改版都得重新排一遍索引页。数据库中“重新排索引”这件事发生在每一次 INSERT、UPDATE、DELETE 上这就是写入放大。1.2 没有索引时数据库在干什么理解全表扫描的代价是判断“要不要加索引”的前提。以常见的 InnoDB 引擎为例数据是按主键顺序组织在聚簇索引的叶子节点上的一张表本身就是一棵以主键为键的 B 树。当你执行SELECT * FROM orders WHERE user_id 12345而没有 user_id 索引时优化器只能选择扫描这棵聚簇索引的全部叶子节点。扫描过程中还有两个隐形成本容易被忽略。第一是回表判断每读到一行都要把 user_id 取出来和 12345 比较比较本身不贵贵的是把整行数据从磁盘读进内存。假设一行平均 500 字节两千万行就是 10GB 左右即使有缓冲池缓存冷数据依然要落盘。第二是无效 IO 放大因为磁盘预读机制数据库通常会一次读 16KB 的一个页而你只关心其中一行剩下的 15.9KB 全是浪费。这也是为什么有些查询加了索引之后提升几十倍有些只提升两三倍。前者往往是从“扫全表”变成“走索引精确定位”后者可能只是从“扫全表”变成“扫索引全表”读取的字节少了但扫描的行数没变。判断的时候不能只看“有没有索引”要看扫描行数和实际读取的数据量这两个指标。1.3 索引的代价写入放大与空间占用索引绝对不是免费的。每建一条二级索引数据库就要多维护一棵 B 树每一次写入都要同步更新这些树。一条 INSERT 语句如果有 5 个二级索引实际发生的页修改可能是 6 次聚簇索引加 5 个二级索引。在高并发写入场景下索引数量直接决定了写入吞吐的上限。空间占用同样不能忽视。我见过一张日增百万行的埋点表主键加三个业务索引索引占用的磁盘空间超过了数据本身达到 1.4 倍。更麻烦的是索引页也会占用缓冲池挤占本该用来缓存热数据的空间形成“索引越多、缓存命中率越低、查询越慢”的负向循环。注意在建索引之前先问自己三个问题——这条查询每天执行多少次扫描行数能降到多少这张表的写入频率有多高如果一条查询每天只跑一次报表为它建索引去拖慢十万次写入这笔账是亏的。我的经验阈值是读多写少的表可以放开建写多读少的表严格控制在三条索引以内纯日志型表只保留主键和一条必要的查询索引。这个比例不是绝对的但能避免大部分“索引建太多反而整体变慢”的情况。2. 索引结构选型B树、哈希、倒排与 LSM 的适用边界2.1 B树为什么成了关系型数据库的默认答案几乎所有主流关系型数据库的默认索引结构都是 B 树这不是历史惯性而是它同时满足了三个硬性要求范围查询、有序遍历、磁盘友好。先说磁盘友好。B 树是一棵“矮胖”的多叉树每个节点大小通常对齐一个磁盘页16KB。假设主键是 8 字节的 bigint页内指针 6 字节那么一个非叶子节点大约能存16384 / 14 ≈ 1170个指针。三层 B 树可以容纳1170 × 1170 × 16 ≈ 2190 万行叶子数据叶子节点按每页存 16 行估算。也就是说两千万行的表查一条记录只需要 3 次页读取其中根节点和第二层几乎常驻内存实际磁盘 IO 往往只有 1 次。这个数字是 B 树统治地位的核心原因。再说范围查询。B 树的所有数据都在叶子节点且叶子节点之间用双向链表连接。执行WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31时先定位到起始值所在的叶子然后顺着链表往后扫即可不需要回到根节点重新查找。这一点是哈希索引做不到的也是 B 树相比 B 树的优势——B 树的中间节点也存数据范围扫描要在树里反复上下跳。最后是有序遍历。ORDER BY如果能命中索引顺序就可以直接省掉排序步骤。执行计划里出现Using filesort就说明排序没走索引数据量大时这个步骤会在临时文件里做外部排序慢得离谱。2.2 哈希索引与倒排索引的用武之地哈希索引的结构是哈希表等值查询的时间复杂度是 O(1)比 B 树的 O(log n) 更快。但它有两个致命短板不支持范围查询不支持排序。所以你只能在“确定只做等值匹配”的场景用它比如缓存类的键值查找。在关系型数据库中哈希索引更多以“自适应哈希索引”的形式存在——引擎自己判断某些热点页值得建哈希自动在内存中维护不需要人工干预。人工显式建哈希索引的场景其实不多我一般只在内存型存储里用它做会话查找、配置项查找这类纯 KV 操作。倒排索引是另一套逻辑它解决的是“文本包含哪些词”的问题。结构是“词项 → 文档 ID 列表”搜索时先把查询语句分词再取各词项对应的文档列表做交集。搜索引擎、日志检索、商品标题模糊匹配都依赖它。需要提醒的是倒排索引对写入很不友好每次新增文档都要更新多个词项的列表所以它更适合“批量导入 高频检索”的场景而不是高频实时写入。另外中文分词的质量直接决定召回率用之前一定要拿真实数据验证分词效果不要想当然。2.3 LSM-Tree 与写多读少场景LSM-Tree 的思路和 B 树完全相反。B 树是“原地更新”写入时要先找到位置再改页涉及随机 IOLSM 是“追加写”新数据先写内存表写满后顺序落盘成不可变的有序文件后台再定期合并。顺序写的吞吐远高于随机写所以 LSM 在写密集型场景优势明显。代价在读取端。因为数据分散在多个层级的有序文件里一次查询可能要检查多个文件读放大严重。为了缓解通常会配合布隆过滤器快速判断“这个文件里有没有这个键”以及定期做合并压缩来减少文件数量。选型时的判断标准很直接写读比高于 10:1考虑 LSM读多写少、需要复杂范围查询和事务选 B 树。这个结论在大多数业务系统里都成立。2.4 索引结构选型对照表结构等值查询范围查询排序支持写入成本典型适用场景B 树快优秀原生支持中随机写订单、用户、账务等事务型业务哈希极快不支持不支持低会话、配置、纯 KV 缓存倒排按词项快不适用不适用高写放大明显全文检索、日志分析、商品搜索LSM-Tree快较好需额外处理低顺序写时序数据、埋点、消息存储这张表我一般放在团队文档里新人来的时候先看一遍能省掉很多“为什么这个字段加了索引还是慢”的无效讨论。3. 建索引前的必修课选择性、基数与回表成本3.1 用选择性判断一列值不值得建索引索引的价值取决于它能过滤掉多少数据。衡量指标叫选择性计算公式是选择性 不重复值的数量 / 总行数取值范围在 0 到 1 之间越接近 1 越好。比如性别列只有两个值两千万行表里选择性是 0.0000001建了索引也没用——优化器算一下就知道“扫索引再回表”比“直接全表扫”还贵大概率会放弃使用。而用户 ID 列如果几乎不重复选择性接近 1索引效果就非常好。实际计算可以直接跑 SQLSELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_id_sel, COUNT(DISTINCT status) / COUNT(*) AS status_sel, COUNT(DISTINCT DATE(create_time)) / COUNT(*) AS day_sel FROM orders;我一般的判断标准是选择性低于 0.01 的列单独建索引意义不大除非它和其他高选择性列组成联合索引。还有一个容易忽略的点是数据分布。平均值好看不代表没问题如果某个值占了全表的 80%查这个值的时候索引反而会成为负担。这种情况可以用直方图统计或者手动采样验证SELECT status, COUNT(*) AS cnt FROM orders GROUP BY status ORDER BY cnt DESC LIMIT 5;3.2 联合索引的列顺序怎么排联合索引遵循最左前缀原则(a, b, c)这条索引能支持a、a,b、a,b,c三种查询条件组合但单独查b或c用不上。所以列顺序不是随便排的我按下面这个优先级来定等值条件列放最左。WHERE user_id ? AND status ?中两列都是等值选择性高的放前面能更快缩小范围。范围条件列放最后。WHERE user_id ? AND create_time ?create_time 是范围条件放在 user_id 后面。因为一旦遇到范围条件后面的列就无法再用于索引定位了。排序和分组列考虑纳入。如果查询有ORDER BY create_time DESC把 create_time 放进索引可以省掉排序。但要注意排序方向联合索引的列方向必须和 ORDER BY 一致否则还是 filesort。举个具体的例子。查询是SELECT id, amount, create_time FROM orders WHERE user_id 12345 AND status IN (1, 2) AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;候选索引(user_id, status, create_time)是合理的user_id 等值、status 是 IN 可以视为多个等值、create_time 用来做范围过滤同时兼顾排序。但这里有个细节——status IN (1,2)之后 create_time 的有序性只在每个 status 分组内部保证跨分组时全局有序会被打破所以这条查询可能仍然需要 filesort。如果 status 的取值很少可以考虑把 status 从索引里去掉改成(user_id, create_time)让排序完全走索引再用 status 做回表后的过滤。两种方案我都实测过具体选哪个要看 status 的过滤强度用 EXPLAIN 对比rows和Extra就能定。注意联合索引不是列越多越好。每多一列索引体积增大、写入成本上升而且超出实际查询需要的列纯属浪费。我见过一个八列的联合索引实际只用到了前两列纯属历史遗留。3.3 覆盖索引与回表二级索引的叶子节点只存索引列的值和主键值不存整行数据。所以通过二级索引查到主键后还要再回聚簇索引取一次完整行这个过程叫回表。一次查询如果涉及 1000 行就要回表 1000 次每次都是一次随机 IO累积起来非常可观。覆盖索引就是让查询需要的所有列都在索引里从而跳过回表。比如-- 索引(user_id, create_time, amount) SELECT create_time, amount FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;这条查询需要的三列全部命中索引执行计划的 Extra 会显示Using index表示只读索引不回表。实测中覆盖索引相比回表查询往往有 3 到 10 倍的提升数据越分散提升越明显。但覆盖索引不能滥用。为了覆盖一条查询而把 amount 塞进索引会让索引体积变大、写入变慢。我的判断标准是这条查询的 QPS 是否足够高高到值得为它单独维护一条索引。低频报表查询宁可回表也不要为它加列。另外要注意SELECT *天然无法被覆盖这是我不推荐生产环境写SELECT *的实际原因之一跟代码规范关系不大。3.4 索引下推把过滤提前到索引层索引下推Index Condition Pushdown是 5.6 之后引入的优化。在没有它之前联合索引(user_id, status)遇到WHERE user_id ? AND status 1时只能在索引里定位到 user_id然后把所有该用户的记录回表回表后再判断 status。有了下推status 的判断直接在索引层完成不满足条件的记录根本不回表。这个优化默认开启EXPLAIN 的 Extra 列会显示Using index condition。它带来的收益和“该用户下不满足 status 条件的记录占比”正相关。如果一个用户有 1000 条订单其中 990 条 status 不等于 1下推能省掉 990 次回表收益巨大如果 999 条都满足收益就微乎其微。需要提醒的是下推只对索引列生效。如果条件是WHERE user_id ? AND remark LIKE %催单%remark 不在索引里无法下推只能回表后过滤。这也是为什么“把常用过滤条件放进联合索引”比“事后加函数处理”更有效。4. 实操从慢查询日志到执行计划的完整排查链路4.1 打开慢查询日志并设定合理阈值排查索引问题的第一步是拿到证据。慢查询日志是最直接的入口配置项通常有三个-- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 阈值单位秒生产环境建议 1 秒起步压测环境可以 0.1 SET GLOBAL long_query_time 1; -- 记录未使用索引的语句谨慎开启日志量会暴涨 SET GLOBAL log_queries_not_using_indexes OFF;log_queries_not_using_indexes这个开关我建议默认关闭。开着小表查询也会被记录日志几分钟就能涨到几个 G反而掩盖了真正的问题。需要的时候临时开排查完立刻关。阈值设定有个经验不要照搬 1 秒。核心交易链路的接口 RT 要求是 200ms那慢查询阈值设 0.2 秒才合理离线报表任务跑几分钟都正常设 1 秒会淹没在噪音里。阈值应该跟着业务 SLA 走而不是跟着默认配置走。拿到日志后用汇总工具按“执行次数 × 平均耗时”排序优先处理总耗时最高的那几条而不是单次最慢的那条。一条执行 10 万次、每次 50ms 的查询危害远大于一条执行 1 次、耗时 5 秒的查询。4.2 读懂 EXPLAIN 的关键列EXPLAIN 输出列很多真正需要重点关注的就四个type、key、rows、Extra。列名关注点危险信号type访问类型ALL全表扫、index扫全索引key实际使用的索引NULL或与预期不符rows预估扫描行数远大于最终返回行数Extra额外信息Using filesort、Using temporary、Using join buffertype的优劣顺序大致是system const eq_ref ref range index ALL。生产环境里核心查询至少要到range最好能到ref。出现ALL且 rows 很大基本就是缺索引或者索引失效。rows是估算值不是精确值但它的量级很有参考意义。如果 rows 是 100 万而实际返回 10 行说明索引过滤性不够优化空间很大如果 rows 是 100 万而实际也返回 80 万行那说明这条查询本身就是批量操作加索引也救不了得从业务层面改分页、异步、预聚合。Extra里的Using filesort和Using temporary是两个高频性能杀手。前者说明排序没走索引后者说明用到了临时表通常出现在 GROUP BY 或 DISTINCT 场景。这两个信号出现时优先考虑调整联合索引的列顺序让排序和分组也能命中索引。4.3 一次完整的优化过程记录拿前面那条订单查询做完整演练。原始语句和原始执行计划EXPLAIN SELECT id, amount, create_time FROM orders WHERE user_id 12345 AND status IN (1,2) ORDER BY create_time DESC LIMIT 20;优化前的结果大致是这样idtypekeyrowsExtra1ALLNULL19,800,000Using where; Using filesort这里两个问题同时存在全表扫描加外部排序。第一步先建联合索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);再跑 EXPLAINidtypekeyrowsExtra1rangeidx_user_status_time1,240Using index condition; Using filesort扫描行数从近两千万降到一千二提升三个数量级但 filesort 还在。原因是 status 是 IN 条件跨 status 分组后 create_time 不再是全局有序。这时候有两个选择一是接受 filesort一千多行的排序成本可以忽略二是去掉 status改用(user_id, create_time)索引让排序完全走索引status 放到回表后过滤。实测下来第二种方案在 status 过滤性不强时更优在 status 过滤性很强比如只占 5%时反而更差因为回表次数变多了。最终我选了(user_id, status, create_time)因为 status 的分布比较均匀filesort 只排一千多行耗时在 1ms 以内而另一种方案的回表次数多出十倍。这个结论不是拍脑袋定的是两条索引都建上、分别用真实参数跑 EXPLAIN 对比出来的。注意线上加索引前一定要在预发环境用生产数据量级的表验证。小表上两条索引差异不明显数据量上去之后结论可能完全反过来。4.4 索引失效的七种典型写法索引建好了不代表一定会被用上下面这些写法我在代码评审里反复见到在索引列上做函数运算WHERE DATE(create_time) 2024-01-01。应该改成范围写法create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换字段是 varchar查询写成WHERE order_no 123456数字不加引号引擎会把列转成数字再比较索引直接失效。这个坑极隐蔽因为不报错只是变慢。前导模糊匹配LIKE %关键词用不上索引LIKE 关键词%可以。确实需要前导匹配的考虑倒排索引或者独立的搜索服务。OR 连接非索引列WHERE user_id 1 OR remark x只要 OR 两侧有一侧没索引整体就走全表。可以拆成两条查询 UNION。不等于和 NOT IN!、NOT IN在数据量大时优化器通常放弃索引因为要判断的行太多。联合索引跳过最左列索引是(a,b,c)查询只用b用不上。范围条件后面的列失效索引(a,b,c)查询a 1 AND b 2 AND c 3c 无法用于定位只能用于下推过滤。最后一条经常被误解成“索引坏了”其实是 B 树的物理顺序决定的——b 是范围b 的多个值对应的 c 并不全局有序自然没法再用 c 做精确定位。5. 索引维护与线上变更的避坑经验5.1 冗余索引与未使用索引的清理表跑久了索引会自然膨胀。常见的冗余有三种完全重复的索引列和顺序都一样、前缀包含的索引有(a,b,c)又建了(a,b)、以及主键已经被二级索引隐含包含的情况。清理之前先确认哪些索引真的没被用过。多数数据库提供索引使用统计可以查询SELECT index_name, rows_read FROM performance_schema.table_io_waits_summary_by_index_usage WHERE object_schema your_db AND object_name orders AND index_name IS NOT NULL ORDER BY rows_read ASC;rows_read为 0 或极小的索引就是清理候选。但要注意统计周期——如果统计只覆盖了最近七天而某个索引是季度报表专用的误删会出事。我一般会观察至少一个完整的业务周期比如一个月并且删除前先改成“不可见索引”观察一段时间ALTER TABLE orders ALTER INDEX idx_old INVISIBLE;不可见索引对优化器隐藏但仍然正常维护。观察一周没有异常再真正 DROP出问题随时改回 VISIBLE。这个操作比直接删安全得多代价只是多占一点空间。5.2 大表加索引怎么做才不锁表千万级以上的表直接执行ALTER TABLE ADD INDEX在旧版本里会锁表业务直接雪崩。现在的在线 DDL 虽然支持并发读写但仍然有坑执行期间会占用大量 IO 和 CPU并且需要额外的磁盘空间存放临时表空间不足会导致变更失败甚至回滚。我的操作顺序是这样的先看磁盘剩余空间至少要能容纳一张完整的新表和索引。空间不够就先扩别硬上。避开业务高峰选择流量最低的时间窗口。在线 DDL 虽然不锁表但会拖慢正常查询。设置锁等待超时避免 DDL 拿不到锁时把后面所有请求堵死SET SESSION lock_wait_timeout 5;分批验证先在从库或者影子表上执行一遍记录耗时。千万级表加一个索引通常几分钟到几十分钟如果超过一小时要重新评估。变更后立刻验证执行计划确认新索引被用上rows符合预期再通知业务方。注意不要在同一条 ALTER 语句里同时加多个索引和改字段类型。一旦失败回滚成本极高而且很难定位是哪一步出的问题。拆成多条逐条执行逐条验证。5.3 索引统计信息与执行计划漂移有时候索引明明在前几天还走得好好的突然就不走了。大概率是统计信息过期导致优化器误判。优化器选择索引靠的是成本估算而成本估算依赖表和索引的统计信息行数、列的分布、索引的基数。数据大量增删后统计信息没跟上估算就会失真。可以手动更新统计信息ANALYZE TABLE orders;多数数据库也会在后台自动采样更新但触发条件不一定及时。我遇到过一次一张表批量导入了 500 万行历史数据之后原本走索引的查询全部退化成全表扫手动 ANALYZE 之后立刻恢复。这类问题的特征是代码没改、索引没动、数据量变了、计划变了。遇到这种情况先别怀疑索引失效先看统计信息。还有一种情况是参数嗅探——同一条语句第一次执行时传入的参数过滤性很强走索引执行计划被缓存后续传入过滤性很弱的参数仍然复用旧计划导致慢查询。这类问题的排查方法是看执行计划里估算行数和实际行数的偏差偏差超过一个数量级就要警惕。5.4 索引相关常见问题速查表现象可能原因排查动作处理方式查询突然变慢代码没改统计信息过期对比 EXPLAIN 的 rows 与实际行数手动更新统计信息索引存在但没被使用索引失效写法检查是否有函数、隐式转换、前导模糊改写 SQL 或调整索引加了索引反而更慢索引太多拖慢写入或优化器选错看写入 RT 与缓冲池命中率清理冗余索引范围查询后排序仍 filesort联合索引列顺序不合理看 Extra 是否 Using filesort调整列顺序或去掉范围列DDL 执行超时磁盘空间不足或锁等待查剩余空间与锁等待日志扩容或改时间窗口重试覆盖索引没生效SELECT 了索引外的列看 Extra 是否 Using index精简查询列或补索引列这张表我贴在团队知识库里新人遇到问题先自查一遍能解决八成常见情况。剩下两成通常涉及数据分布的特殊性需要拿真实参数跑 EXPLAIN 才能定位。最后分享一个我自己的习惯每次给线上表加索引我都会在变更记录里写清楚四件事——这条索引是为哪条 SQL 服务的、预期的扫描行数是多少、验证时的实际执行计划是什么、以及如果它没被用上该找谁。看起来啰嗦但半年后回头看这张记录能省掉大量“这条索引是干嘛的、能不能删”的扯皮。索引这东西建的时候容易维护的时候才知道当初多写两行说明有多值。