前阵子帮朋友排查一个生产库的问题一张快两千万行的订单流水表按用户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_idabc123对应的主键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 123MySQL会隐式把列转成数字相当于对索引列做了函数操作索引直接失效。这是最容易踩的坑因为业务上线初期数据量小全表扫描也无所谓等数据量上来了才在慢查询日志里看到问题。类似的还有在索引列上用函数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.0SET 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;重点看这几个字段字段值含义typeref / range / ALLALL就是全表扫描ref是等值匹配索引range是范围扫描keyidx_user_status_time实际用到的索引名rows24500优化器预估扫描行数filtered12.5经过索引下推后剩余条件的过滤比例ExtraUsing filesort排序没走索引额外做了排序操作如果看到typeALL先确认是不是索引失效看到Using filesort考虑能不能把排序字段并进索引看到Using temporary多半是GROUP BY或者DISTINCT没走索引。rows字段是个估算值但能直观反映是否发生了数量级问题——比如预估扫描100万行那就要警惕了。4.3 一个真实案例多表JOIN从全表扫描到索引嵌套循环说一个近期排查的生产问题。三个表做JOIN订单表orders5000万行、订单明细表order_items8000万行、商品表products200万行。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_id1001的订单量可能只有几百条作为驱动表然后对每个订单去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、创建日期。半年后回头清理冗余索引时这张清单能帮你判断哪些索引是真在服务业务哪些只是心理安慰。索引不是建得越多越好而是每一条都能说出它存在的理由。