前阵子帮朋友排查一个生产库的问题：一张快两千万行的订单流水表，按用户ID查最近三个月的订单，接口平均耗时2.4秒，慢查询日志里几乎每秒钟都在刷这条语句。我看了眼建表语句，user_id连索引都没有，主键是自增ID。解决方案其实就一句话：加一个普通二级索引。索引建完之后，同样的SQL耗时降到90毫秒上下，前后差了接近30倍。
这就是索引策略的威力，也是我这次想聊透的话题。很多人对索引的理解停留在“查询慢就加索引”，可真到实际项目里，会发现种种问题：加了索引还是慢、索引明明存在却不走、组合索引顺序怎么排、覆盖索引怎么用、慢查询日志怎么看……这些细节组合在一起，才构成了一套完整的索引策略。这篇文章我会结合真实的线上案例，把索引从设计、落地、排查到维护整条链路展开讲，适合刚接触索引优化的开发同学，也适合被慢查询折腾过、想系统补齐这块拼图的进阶读者。
1. 索引的本质：它到底在加速什么？
索引之所以能让查询“快如闪电”，核心不是魔法，而是改变了数据的访问路径。理解这点之前，你先得接受一个事实：数据库最昂贵的操作不是CPU计算，而是磁盘IO。
1.1 磁盘IO是查询慢的真正瓶颈
机械硬盘随机读写一次大概10毫秒，SSD虽然快，但一次随机IO也要几十微秒到上百微秒。别小看这个数字，当数据量达到千万级，一张表按堆表方式存储，查询符合条件的记录就只能从第一页扫到最后一页，逐条匹配。假设表有100万行、每行1KB，全表扫描就要读约1GB数据，即便按SSD每秒500MB的顺序读速度，也得两秒上下。这还只是一条查询。
索引解决的就是这个问题：它建立了一棵额外的B+树，让你不需要扫描全表，只要沿着树的路径查几次IO就能找到目标记录。B+树的高度一般在3到4层，意味着定位一条记录只需要3到4次磁盘IO，和表行数几乎无关，这是数量级上的差距。
我给个直观的类比：全表扫描相当于在一本没有目录的十万页书里找一句话，只能一页页翻；索引查询相当于先查目录页，定位到具体页码，再翻到那一页。目录可能要多占几页纸，但这几页纸能帮你省掉大量翻书时间。
1.2 主键索引与二级索引的底层分工
以MySQL InnoDB为例，表本身就是一个按主键组织的聚簇索引。数据行存在B+树的叶子节点里，主键值决定了行在物理存储上的顺序。所以InnoDB表建了主键，本质上是把“表”和“索引”合体了。
二级索引（非聚簇索引）则是另一棵独立的B+树，叶子节点存的是索引列的值加上主键值。查询时如果走二级索引，先在这棵索引树上找到主键，再到主键索引树回表取完整数据行。这个“先查索引树，再查主键树”的过程叫回表。
明白了这个结构，你就能理解很多优化手段的原理。比如覆盖索引，就是让二级索引叶子节点里已经包含了你需要的所有列，查询引擎发现不用回表，省掉一次IO。再比如索引下推，MySQL 5.6开始支持，把部分WHERE条件的过滤下推到索引遍历过程中提前过滤，减少回表次数。
1.3 回表、覆盖索引与索引下推的实际区别
举个实际例子。有一张用户表user，包含id（主键）、user_id、nickname、status这几个字段，表里有2000万行。下面三条查询走不同路径：
第一条：SELECT * FROM user WHERE user_id = 'abc123'。如果user_id上建了普通索引，执行过程是：走二级索引找到user_id='abc123'对应的主键id，然后回表取整行数据。一次查询至少两次索引树查找。
第二条：SELECT id, user_id FROM user WHERE user_id = 'abc123'。如果二级索引是idx_user_id(user_id)，你会发现id是主键，已经包含在二级索引叶子节点里，查询要的列全都能在索引树上拿到，不需要回表。这样就走上了覆盖索引路径。
第三条：假设联合索引是idx_user_status(user_id, status)，执行SELECT * FROM user WHERE user_id = 'abc123' AND status = 1。MySQL会在遍历联合索引时，先用user_id定位到区间，再在索引内部用status过滤，只对少量满足条件的记录回表。
这些细节决定了同样一条业务SQL，在数据量大时性能差10倍还是接近相等。很多“为什么我建了索引还是慢”的疑问，答案都藏在这几个概念里。
2. 建索引前的设计决策：哪些列值得建
建索引不是越多越好，也不是看着WHERE条件里有哪列就建哪列。索引是有成本的：每建一个索引，写入时就要多维护一棵B+树，占空间，还会拖慢INSERT和UPDATE。生产环境最怕的不是少建一个索引，而是无脑建一堆索引后，写入性能崩了，查询也没快多少。
2.1 基数和选择性：能筛掉多少数据
衡量一列适不适合建索引，最先看的是基数（Cardinality）。基数是指某列有多少个不同的值。选择性就是基数除以总行数，比如2000万行的表，某列有100万个不同值，选择性就是5%。
选择性越高，索引的价值越大。极端反例是性别列，只有男女两个值，选择性0.0001%，就算建了索引，查询条件WHERE gender = 'male'还是可能命中一半的行。数据库优化器算完账发现，按索引查还不如直接全表扫描来得划算，于是宁可走全表也不走你的索引，这就是“建了索引但没用上”的常见原因之一。
经验上，选择性超过10%的列值得考虑单列索引；低于1%的列，除非是分区裁剪类似场景，否则基本不用为它单独建索引。可以用下面这条SQL快速查看某列基数：
SELECT COUNT(DISTINCT column_name) AS cardinality, COUNT(*) AS total_rows, ROUND(COUNT(DISTINCT column_name) / COUNT(*) * 100, 2) AS selectivity_pct FROM table_name;2.2 查询模式分析：写SQL前先复盘
建索引之前，我习惯性做一件事：把这套接口涉及的核心SQL全部列出来，逐条拆解它们的WHERE条件、JOIN条件、ORDER BY和GROUP BY字段。不是凭感觉猜，而是基于实际查询模式决定索引结构。
有个典型的隐性成本很多人忽略：建立一个联合索引idx_a_b(a, b)之后，它其实能同时服务WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? GROUP BY b这几类查询，因为最左前缀原则允许你只用到最左边的一部分列。但如果你的查询都是WHERE b = ?，这个联合索引对你就没有半点帮助。索引设计本质上是一次“用空间换查询速度，按查询模式做取舍”的决策。
2.3 联合索引的列顺序：等值优先，范围垫后
联合索引设计最核心的一条规则：把等值查询的列放前面，范围查询的列放后面，排序字段尽量并进索引。原因在于B+树的索引结构：前导列确定后，后续列的排序才是有意义的；一旦碰到范围条件，后面的列就没法继续走索引定位了，只能做索引内过滤。
举个我实际调过的例子。某订单查询接口的SQL长这样：
SELECT order_id, amount, create_time FROM orders WHERE user_id = 1001 AND status = 2 AND create_time >= '2024-01-01' ORDER BY create_time DESC LIMIT 20;orders表有5000万行。最初的索引是单列idx_user_id(user_id)，查询虽然是等值匹配，但后续的status和create_time都是在回表之后才过滤，create_time还需要在内存里做排序，压测时P99超过1.5秒。
我把它改成联合索引idx_user_status_time(user_id, status, create_time)之后，执行计划变成了：先用user_id和status两个等值条件精确定位，再在索引内部按create_time区间扫描，而且这个索引天然按create_time有序，排序直接省掉。LIMIT 20只需要取20条就停，P99降到了120毫秒左右。
这条规则有一个通用口诀：等值在前，范围在中，排序最后。范围查询一出现，后面的列基本"作废"，所以要尽量把范围列往右放。排序字段如果能并进索引，就省掉一次filesort，这在ORDER BY数据量大时收益极其可观。
3. 索引失效的常见坑：为什么加了索引还是慢
线上最常见的困惑是：索引明明建了，EXPLAIN一看却是全表扫描，或者明明走了索引还是慢得离谱。这些坑大多有固定套路，我把踩过的几点总结一下。
3.1 最左前缀的误用
联合索引idx_user_status_time(user_id, status, create_time)，查询条件是WHERE status = 2 AND create_time >= '2024-01-01'，没带user_id，那这个索引从第一列就使不上，直接全表扫描。很多人建了联合索引之后，以为把所有列都放进去了，后面查询随便用哪列都能走索引，这是认知误区。
联合索引的本质是多层嵌套的排序结构，必须从最左列开始逐列匹配。反过来看，这也是设计索引时要考虑周全的原因：你得想清楚业务SQL实际会不会带上这些等值条件。如果经常有只按status查询的需求，就得额外再为status单独建一个索引，或者调整联合索引列顺序，但这个调整会牺牲user_id场景的性能，需要权衡。
3.2 隐式类型转换与函数包裹
一列是VARCHAR，查询时却传了数字，比如WHERE user_id = 123而不是WHERE user_id = '123'，MySQL会隐式把列转成数字，相当于对索引列做了函数操作，索引直接失效。这是最容易踩的坑，因为业务上线初期数据量小，全表扫描也无所谓，等数据量上来了才在慢查询日志里看到问题。
类似的还有在索引列上用函数：WHERE DATE(create_time) = '2024-01-01'，即使create_time有索引也走不了，因为每行都要先算DATE()再比较。正确写法是改成范围条件：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'，或者使用MySQL 8.0的函数索引。函数索引的本质是把计算后的结果也存进索引里，这个特性在PostgreSQL里叫表达式索引，遇到无法改写SQL的场景时非常有用。
3.3 前导模糊与OR条件的处理
LIKE '%关键词%'因为前导有通配符，B+树无法按前缀定位，索引也是失效的。真的要支持任意位置匹配，要么靠全文索引，要么靠搜索引擎，要么接受全表扫描加并行扫描。如果只是LIKE '关键词%'这种前缀匹配，则可以走索引，因为字符串在B+树里的排序天然支持前缀定位。
OR条件则是另一个陷阱。WHERE user_id = 1 OR status = 2，即使user_id和status各自都有单列索引，MySQL也可能把OR拆成两个索引扫描再合并，当其中一个条件选择性差时，代价可能比全表扫描还高。优化器大概率会直接选全表扫描。应对办法是改写为UNION ALL，或者给两个列建联合索引后尽量别用OR，改成IN。这里要注意，WHERE user_id IN (1, 2, 3)是可以走索引的，IN的本质是多个等值条件的合并，和OR的语义相同但代价模型完全不同。
4. 实操排查链路：从慢查询日志到执行计划
上面讲了很多理论坑，接下来给一个完整的排查链路。遇到线上查询慢，我是按照固定顺序来处理的：先看慢查询日志定位语句，再用EXPLAIN拆解执行计划，最后根据计划里的关键字段做针对性修改，修改后压测验证。
4.1 打开慢查询日志与阈值设置
MySQL默认可能没开慢查询日志，可以先检查：
SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time';没开启的话，执行下面的设置（MySQL 8.0）：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';long_query_time设为1秒是比较常用的生产阈值，超过1秒的SQL全记下来。log_queries_not_using_indexes把没走索引的查询也记下来，这个开关帮我抓出过不少隐患SQL。日志文件通常在数据目录下的主机名-slow.log，也可以执行SHOW VARIABLES LIKE 'slow_query_log_file'看具体路径。
慢查询日志里除了SQL文本，还会记录执行时间、锁等待时间、扫描行数和返回行数。扫描行数和返回行数的比值差距巨大，基本就说明查询路径选得不对。
4.2 读懂EXPLAIN的关键字段
拿到慢SQL，下一步就是EXPLAIN。以这条为例：
EXPLAIN SELECT order_id, amount, create_time FROM orders WHERE user_id = 1001 AND status = 2 ORDER BY create_time DESC LIMIT 20;重点看这几个字段：
| 字段 | 值 | 含义 |
|---|---|---|
| type | ref / range / ALL | ALL就是全表扫描，ref是等值匹配索引，range是范围扫描 |
| key | idx_user_status_time | 实际用到的索引名 |
| rows | 24500 | 优化器预估扫描行数 |
| filtered | 12.5 | 经过索引下推后剩余条件的过滤比例 |
| Extra | Using filesort | 排序没走索引，额外做了排序操作 |
如果看到type=ALL，先确认是不是索引失效；看到Using filesort，考虑能不能把排序字段并进索引；看到Using temporary，多半是GROUP BY或者DISTINCT没走索引。rows字段是个估算值，但能直观反映是否发生了数量级问题——比如预估扫描100万行，那就要警惕了。
4.3 一个真实案例：多表JOIN从全表扫描到索引嵌套循环
说一个近期排查的生产问题。三个表做JOIN：订单表orders（5000万行）、订单明细表order_items（8000万行）、商品表products（200万行）。SQL简化后：
SELECT o.order_id, o.user_id, i.item_sku, p.product_name FROM orders o JOIN order_items i ON o.order_id = i.order_id JOIN products p ON i.product_id = p.product_id WHERE o.user_id = 1001 ORDER BY o.create_time DESC LIMIT 50;执行计划里出现了一个很夸张的信号：order_items表的访问方式走了ALL，预估扫描7000万行。这就是经典的JOIN连接列无索引问题。优化器选择从orders表开始，用小结果集（user_id=1001的订单量可能只有几百条）作为驱动表，然后对每个订单去order_items里找明细，i.order_id没有索引，于是每次都要全表扫，320条驱动记录乘7000万行，这个乘法结果就是慢的根源。
修复方式是给连接列补索引：
ALTER TABLE order_items ADD INDEX idx_order_id (order_id); ALTER TABLE products ADD INDEX idx_product_id (product_id);改完后执行计划中order_items的访问方式变成ref，预估扫描行数降到个位数。整条SQL从3.8秒降到180毫秒。这个案例想强调的是：JOIN查询的性能很大程度上取决于连接列有没有索引。无论是MySQL的Index Nested-Loop Join，还是PostgreSQL的Hash Join，连接列的索引都能极大减少被驱动表的访问代价。
5. 索引的维护与进阶：让“快”长期有效
索引策略不是一锤子买卖。今天建好索引，查询可能很快，但运行半年后随着数据增长、查询模式变化、索引碎片积累，性能会慢慢劣化。维护阶段同样重要。
5.1 索引碎片、重复索引与冗余索引
InnoDB的B+树在频繁插入和删除后会产生页碎片。碎片本身不会让索引失效，但会让顺序扫描变成随机IO，减少每页有效记录数，数据量不变的情况下索引体积变大、IO变多。定期用OPTIMIZE TABLE table_name可以重建表并整理碎片，低峰期执行。也可以先查看表的碎片率：
SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb FROM information_schema.tables WHERE table_schema = 'your_db' ORDER BY free_mb DESC;重复索引是指完全相同的列和顺序建了多个，比如idx_user_id(user_id)和idx_user_id_2(user_id)，属于纯粹浪费空间和写入开销。冗余索引是指联合索引idx_a_b(a, b)已存在的情况下，又单独建了idx_a(a)，前者能覆盖后者的功能，后者就是冗余，可以删掉。
5.2 覆盖索引与回表优化的实战组合
覆盖索引在统计类接口里收益特别大。举个例子，运营后台要统计某个用户群在不同时间段的下单量，SQL是：
SELECT DATE(create_time), COUNT(*) FROM orders WHERE user_id = 1001 AND create_time >= '2024-01-01' GROUP BY DATE(create_time);如果索引是idx_user_time(user_id, create_time)，COUNT(*)和DATE(create_time)都不需要回表拿其他列，整个查询可以完全在索引树上完成，Extra里会出现Using index，这是覆盖索引的标志。统计类查询动辄扫描几百万行，覆盖索引能把每次IO都打在更小更紧凑的索引页上，速度和全表扫描比可能是数量级差距。
实践中我常用的组合思路是：优先满足核心查询场景，顺手用覆盖索引把高频统计接口也覆盖掉。比如给订单表建idx_user_status_time(user_id, status, create_time)，同时把order_id和amount这两个高频查询字段也放进索引，变成idx_user_status_time(user_id, status, create_time, order_id, amount)。注意，覆盖索引不是越宽越好，多放字段会牺牲写入性能和空间，只覆盖线上真正高频的查询列。
5.3 索引之外：查询重写与语句优化
有时候不动索引也能大幅提升查询性能，关键是语句改写。分享几个我常用的手段。
分页深翻页优化：LIMIT 200000, 20这类深分页，InnoDB需要先扫描并丢弃前面20万行，成本很高。常见优化方式是改成基于游标或基于上一页最大主键的写法：
-- 原始写法 SELECT * FROM orders WHERE user_id = 1001 ORDER BY id LIMIT 200000, 20; -- 改写后 SELECT * FROM orders WHERE user_id = 1001 AND id > 198765 ORDER BY id LIMIT 20;前提是WHERE条件稳定、排序字段唯一。这个优化对排序接口几乎是无损的，能省掉深分页的排序和大量IO。
**避免SELECT ***：尽量只查需要的列，减少回表概率，也减少网络传输和临时表使用。很多时候你以为慢在查询，其实慢在把几百KB不需要的字段拖回应用层。
EXISTS与IN的选择：小表驱动大表时，WHERE EXISTS (SELECT 1 FROM big_table WHERE ...)通常比WHERE IN (SELECT ... FROM big_table)更友好，因为EXISTS是对每个外部行做存在性判断，可以尽早短路。反过来如果外部表大、子查询结果集小，IN反而更好。现代数据库优化器会在部分场景自动做半连接改写，但业务SQL写得规整，能减少优化器猜错的概率。
最后分享一个我自己坚持的原则：每次上线索引变更，都顺手记录一张索引清单，包含表名、索引名、索引列、对应解决的慢SQL、创建日期。半年后回头清理冗余索引时，这张清单能帮你判断哪些索引是真在服务业务，哪些只是心理安慰。索引不是建得越多越好，而是每一条都能说出它存在的理由。
