上个月末我处理了一单让我印象很深的线上告警。一张已经优化过的报表SQL在订单表数据量涨到180万行之后单次执行时间从最初的1.2秒直接飙到32秒。DBA团队给出的第一版建议是加个索引试试结果加上之后只降到9秒距离业务要求的2秒以内还差得远。后来我没有继续在加索引这个动作上纠结而是把整条SQL的索引策略重新拆了一遍哪些条件在做无谓的回表、哪些排序在走文件、联合索引的列顺序是不是放反了、统计信息是不是已经过期。全部调整完之后这条SQL稳定在0.25秒左右业务方一度以为是查了缓存。这件事恰好说明一个问题索引策略优化从来不是哪儿慢就在哪儿堆索引而是一套围绕查询成本、存储结构、优化器行为展开的系统工程。这篇就把我从那次救火中学到、以及过去几年反复用到的索引优化方法论完整写出来偏实战尽量少讲空洞理论。1. 先把索引为什么快这件事掰开揉碎有很多人对索引的理解停留在相当于书的目录然后呢然后没了。这个类比在解释二级索引时有价值但如果你想真正做好索引策略还必须理解树结构本身。因为几乎所有主流关系型数据库的索引底层都是B树。1.1 B树真正牛的地方不是查找快而是少读盘数据库里最贵的操作不是CPU计算也不是内存比较而是磁盘IO。一个数据页通常是16KB如果你的表有180万行每行几百字节整张表可能就是上万个数据页。没索引的时候查一条记录理论上要把所有这些页都读一遍这叫全表扫描。B树的厉害之处在于它是一棵矮胖树。三层B树就能支撑几千万甚至上亿条记录而查询路径只需要从根节点走到叶子节点——也就是3次左右的磁盘IO。更关键的是B树的叶子节点之间是用双向链表串起来的这让范围查询变得非常顺手找到第一个满足条件的叶子节点之后顺着链表往后遍历就行不需要反复回到树上层重新查找。所以索引优化的第一性原理很简单让数据库在执行查询时少读数据页。你做的所有索引设计、列顺序调整、覆盖索引补充本质上都是为了让查询引擎的IO次数降下来。1.2 聚簇索引和二级索引的双轨制决定了很多优化方向在MySQL InnoDB里表数据本身就存储在主键索引的B树叶子节点上这叫聚簇索引。你建的其他索引都叫二级索引它们的叶子节点只存两样东西索引列的值、主键值。这个结构带来的连锁反应是通过二级索引查数据如果索引里没有你要的所有列就得拿主键值再回聚簇索引里查一次完整行这就是回表。回表不是免费的一次回表就是一次随机IO。如果查询命中了1000行那就意味着1000次随机IO。所以后续要讲的覆盖索引、索引下推本质上都是在减少回表次数这件事上做文章。在SQL Server里情况类似它用聚集索引和非聚集索引来称呼这套结构叶子节点还多了个行定位符RID。理解这个双轨制你才能看懂为什么有些索引看起来命中了却还是很慢。1.3 顺手回答一个热门疑问视图到底能不能加快查询速度最近被问得很多的一个问题是把慢SQL包成视图查询速度是不是就上去了答案是否定的。普通视图本质是一条命名的SQL语句不占存储、不物化数据数据库每次查视图都会重新执行里面的查询逻辑。视图对性能的唯一帮助体现在你能把一段复杂JOIN封装好让优化器统一处理或者是出于权限控制的需要它本身不会让SQL更快。真正能物化结果并加速的是物化视图但这个功能在MySQL里原生不支持需要靠定时刷表之类的方案模拟SQL Server里叫索引视图通过强制在视图上建立唯一聚集索引来固化数据。如果你的目标是提速别指望普通视图还是回到索引和SQL写法上来。2. 定位慢查询先别急着加索引把执行计划读明白我见过太多同行拿到一条慢SQL第一反应就是加索引。但索引优化最忌讳的就是盲目。你得先知道这条SQL慢到底是慢在全表扫描慢在回表太多慢在排序慢在JOIN驱动方式不对还是慢在统计信息不准导致优化器走错路搞清楚这些才轮到设计索引。2.1 先从慢查询日志里把病号捞出来配置慢查询日志是第一步。MySQL里的典型配置是SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;线上环境建议把long_query_time先设成1秒观察一段时间看哪些SQL常态化超过1秒。这里有个经验一次性把阈值调太低日志会爆炸调太高呢又容易漏掉那些单次不快但高频执行的SQL。我习惯的做法是先把阈值放2秒跑一周同时用pt-query-digest做聚合分析按总执行时间而不是单次耗时排序。SQL Server没有慢查询日志但动态管理视图一样能捞。常用这条经典查询SELECT TOP 20 qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, SUBSTRING(st.text, (qs.statement_start_offset/2)1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY avg_elapsed_time DESC;按平均逻辑读排序往往比按耗时排序更能定位到索引问题因为逻辑读高意味着索引结构没吃透。2.2 EXPLAIN里的type列是一面照妖镜捞到慢SQL之后下一步就是用EXPLAIN看它的执行计划。你最先要看的是type这一列它直接告诉你访问方式。下面这张表是我自己整理的按性能从好到差排type含义说明system系统表只有一行极少见不用管const主键或唯一索引等值匹配最多返回一行很快eq_ref被驱动表通过主键/唯一索引等值匹配JOIN场景中最优ref普通索引等值匹配非唯一索引还能接受range索引范围扫描常用于、、BETWEENindex扫描整棵索引树看着用了索引实际并不好ALL全表扫描最需要警惕重点要看两个一是ALL说明查询引擎正在蛮力地逐页扫数据这是索引策略失败的最大信号二是index它代表索引全扫描和全表扫描的区别仅仅在于扫描的是索引树而非数据页如果索引里字段少稍微快一点但同样是灾难。2.3 光看EXPLAIN还不够把统计信息打开EXPLAIN给的是优化器基于统计信息的估算。要判断一条SQL到底吃了多少代价实战中我还会把实际统计打开。MySQL里可以这样SET profiling 1; SHOW PROFILE FOR QUERY 1;SQL Server的写法更直观SET STATISTICS IO ON; SET STATISTICS TIME ON;然后执行你的SQL看消息里输出的逻辑读次数。举个例子我优化前那条SQL执行计划显示ALL逻辑读是284万次优化后变成覆盖索引逻辑读直接掉到700多次。这个数字比执行时间更能反映索引质量的好坏因为它不受机器负载影响是一个相对稳定的成本指标。2.4 优化器可能被过期的统计信息带偏这是个很隐蔽的坑。优化器选择索引是根据统计信息估算行数的如果统计信息长时间没更新它可能严重低估某个表的行数从而选错驱动表甚至放弃索引。我处理过的一个真实案例一张表从20万行涨到200万行统计信息还是旧的优化器以为另一张小表是驱动表更划算结果小表反向驱动大表一个Nested Loop JOIN跑出了全表扫描的代价。解决办法是更新统计信息后执行计划立刻恢复正常。MySQL里可以用ANALYZE TABLESQL Server里是UPDATE STATISTICS。如果你发现一条SQL的预估行数和实际返回行数差着几个数量级且执行计划明显不合理优先考虑统计信息过期再去动索引设计。3. 能真正把查询提速10倍的索引策略实操清单到了这一步你的手里应该已经握着一张明确的执行计划和一组IO统计。现在才轮到设计索引。下面的策略我按效果显著程度排了个序每一条都附上它解决的核心问题。3.1 覆盖索引让查询根本不碰数据页这是最容易被低估的一招。一个二级索引如果包含了SQL所需的全部列就不需要回表查聚簇索引了。换句话说查询引擎只需要扫一棵索引树就能拿到所有结果。举例假设有个订单表经常要查某时间段内已完成订单的数量SELECT COUNT(*) FROM orders WHERE status FINISHED AND created_at 2024-01-01 AND created_at 2024-02-01;如果你只在status上建了单列索引执行逻辑是先通过索引找到所有status FINISHED的行的主键再逐一回表判断created_at是否满足条件。中间有多少行FINISHED就要回表多少次。但如果你建的联合索引是(status, created_at)事情就变了。索引树里同时存了这两列计数只需要扫索引就能完成。我用SHOW PROFILE实测过单单把单列索引改成联合索引这类的COUNT查询耗时能从几百毫秒降到几毫秒10倍收益是有的甚至更高。3.2 联合索引列顺序把等值条件放前面范围条件放后面联合索引的列顺序安排直接决定索引能不能被充分利用。这里有个基本原则等值条件列放前面范围条件列放后面高频过滤列放前面选择性略低但能大幅过滤的行放前面。为什么B树对索引列的排序是按顺序逐列进行的。如果第一列是等值匹配剩下列还保持着有序性可以继续用索引定位如果第一列是范围条件那么第二列及之后的列在索引中的顺序对本次查询就没什么利用价值了——它们只能在范围内逐个过滤。实战中的判断标准是能从索引里取走的绝不留给回表。比如下面这条SQLSELECT order_id, user_id FROM orders WHERE status PAID AND created_at BETWEEN 2024-05-01 AND 2024-05-31 ORDER BY order_id;推荐索引策略是(status, created_at, order_id, user_id)。status是等值条件放最前created_at做范围收窄再把要排序的order_id和要查询的user_id也塞进索引里。这样既覆盖了查询列又让ORDER BY order_id直接沿索引顺序走省掉文件排序。建完这个索引这条SQL从1.8秒降到了0.15秒。3.3 索引下推让引擎在索引层就把行过滤掉索引下推是MySQL 5.6引入的能力但很多人建了联合索引却不知道它已经在帮你干活更不知道该怎么利用它。它的作用是对于联合索引无法完全覆盖的条件比如索引里没有的字段存储引擎在读取索引记录时就先进行判断满足条件的才回表而不是把每一条命中的索引记录都送回Server层过滤。打个比方你要在一摞档案里找北京且男性的记录。普通流程是先把所有北京的都抽出来一张张翻到个人页看性别索引下推则是在抽档案的时候顺手把性别那栏也瞄一眼明显不符的直接放回去只有两项都符合才抽出来。省了大量翻页动作。实操上你想主动利用它核心就一条把最常用的过滤列尽量多放进索引里哪怕某些列只是用来过滤不参与查询和排序。比如上面那个订单表的例子如果还有一个channel字段要过滤完全可以把索引扩成(status, channel, created_at, order_id, user_id)让下推阶段就筛掉不想要的渠道。3.4 ORDER BY和GROUP BY的索引策略消灭文件排序排序是另一个隐藏的慢查询元凶。MySQL里如果排序字段无法走索引就会出现Using filesort——先把结果集装进内存或磁盘临时文件再排序。数据量大时这里的时间开销可能比过滤本身还大。让排序走索引的要义是让ORDER BY的字段与索引列的顺序保持完全一致且排序方向一致或者统一反序MySQL 8.0之后可以混排。这条规则同样适用于GROUP BY因为分组本质也是先排序。有个容易踩的点如果WHERE里对某列做了等值过滤而ORDER BY里用到的字段不是索引的第一列优化器仍然有机会避免filesort。举个例子WHERE status PAID ORDER BY created_at联合索引(status, created_at)就很好用先按status定位到一段索引区间这段区间内的created_at天然有序。3.5 前缀索引长字符串字段的瘦身术遇到email、url这种又长又没有唯一规律的字段直接建整列索引会让索引树变得巨大浪费磁盘还拖慢写入。这时候前缀索引就很有价值只取前N个字符做索引。ALTER TABLE users ADD INDEX idx_email_prefix (email(20));但前缀索引有个硬伤无法用于覆盖索引因为索引里存的不是完整值每次查到候选记录后还得回表确认完整内容。所以这里要在索引体积和回表代价之间做权衡。经验做法是取选择性接近完整列的值用两条SQL对比一下SELECT COUNT(DISTINCT email) / COUNT(*) FROM users; SELECT COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) FROM users;第二条的比值越接近第一条说明前缀20的区分度越好。通常达到0.9以上就可以接受。3.6 函数运算和隐式转换让索引失效的隐形杀手你建得再好再完美的索引也架不住在查询条件里对索引列做函数运算。这是索引策略里翻车率最高的一类问题。-- 反例在索引列上包了函数索引直接失效 SELECT * FROM orders WHERE DATE(created_at) 2024-06-01; -- 正解改写为范围条件索引依然有效 SELECT * FROM orders WHERE created_at 2024-06-01 AND created_at 2024-06-02;道理很简单索引树里存的是created_at的原始值你要用DATE()处理它优化器没法直接拿树里的值做匹配只能把所有行的值都算一遍函数再做比较。不光是函数隐式类型转换也一样。WHERE phone 13812345678如果phone是VARCHAR类型你传了数字MySQL会把索引列的字符串转成数字再比较索引照样废掉。3.7 唯一索引和普通索引的选择别迷信只有唯一才快有一种说法是唯一索引比普通索引快这在旧版本里有一定道理——因为引擎读到第一条就停。但在MySQL 8.0的机制下普通索引用change buffer可以优化写入读性能的差距也非常小。如果不是业务上必须保证唯一我倾向于建普通索引因为它的写入成本更低而且后面调整索引结构时更灵活。唯一索引真正的用武之地是防重复而不是提速。别把两件事混为一谈。4. 一次完整案例复盘从12秒到0.2秒我做了哪四步光讲理论容易飘拿一次真实的优化过程来说。下面是当时那条业务SQL的简化版场景是运营后台要按用户昵称模糊搜索订单并关联用户名和订单状态按订单时间倒序排列。SELECT o.order_id, o.amount, o.status, u.nickname FROM orders o JOIN users u ON o.user_id u.id WHERE u.nickname LIKE 小明% AND o.status FINISHED AND o.created_at 2024-03-01 AND o.created_at 2024-04-01 ORDER BY o.created_at DESC LIMIT 50;原始执行时间12.6秒。orders表400万行users表120万行。4.1 第一步用EXPLAIN确认瓶颈在谁身上拿到执行计划第一眼就发现问题驱动表是userstype是ALLrows估算28万行——因为LIKE 小明%这个条件虽然能用前缀索引但当时users表上没有建任何索引。优化器被迫先全表扫出28万个候选用户再逐一把他们的订单捞出来看状态和时间。这个阶段我什么都没改只记录了一个关键数字逻辑读468万次。这数字一眼就能看出问题主要出在全表扫描 回表风暴。4.2 第二步给users表补上前缀索引解决驱动表全表扫描nickname是VARCHAR(64)全列建索引浪费先测了下选择性完整列选择性约0.986LEFT(nickname, 12)选择性约0.971于是补了索引idx_nickname_prefix。这一改users表从全表扫描变成range扫描优化器把驱动表改为orders执行时间从12.6秒降到3.5秒。注意这里我没有急着去动orders表因为3.5秒依然不达标。但这一步很关键它让执行计划的走向更合理了。4.3 第三步把orders表的索引升级为带状态的复合过滤覆盖接下来看orders表的访问路径type是ref走的是idx_user_id单列索引也就是说先按user_id把几十行订单捞出来再逐行判断status和created_at。这里的问题有两个一是过滤不了多少二是回表次数太多。我把索引升级成(status, created_at, amount, order_id, user_id)status等值条件放最前created_at范围条件跟上把所有SELECT涉及的列全部塞进索引做成覆盖索引order_id放在后面顺带支持LIMIT附近可能的排序需求。执行时间直接从3.5秒掉到0.4秒。4.4 第四步用STRAIGHT_JOIN稳住驱动顺序防止优化器犯傻0.4秒其实已经接近业务目标了但上线前我多做了一个动作——在SQL里加了STRAIGHT_JOINMySQL语法明确指定按我写的表顺序作为JOIN顺序。原因很简单两条表的数据量还在增长统计信息更新跟不上时优化器可能会某天突然脑子一热换回那个灾难性的驱动顺序。STRAIGHT_JOIN相当于告诉它别猜了就按这个顺序跑。最终线上稳定在0.2到0.3秒运行两周没有波动。4.5 优化结果对比指标优化前优化后执行时间12.6秒0.25秒逻辑读468万次742次访问类型ALL refref range回表次数数十万次基本为0这个案例里最值得复盘的不是我建了三个索引而是每一步之前我都先问当前的访问路径是什么代价主要消耗在哪个动作上把这两个问题答清楚了索引策略自然就浮出水面了。5. 会让查询变得更慢的四种索引用法索引优化不是加了就一定快。以下四种情况我都在生产环境里见过加了索引反而更慢甚至拖垮了整体性能。5.1 索引冗余严重养了一堆用不上的废索引每多一个索引就意味着每次INSERT/UPDATE/DELETE都要额外维护一棵B树。如果一张表有8个索引但日常查询只用其中2个那剩下6个纯粹在拖累写入性能、浪费磁盘。更隐蔽的问题是索引冗余会让优化器多很多选择有时候它会选一个你完全没想到的次优索引。我遇到过一个案例表上有索引(a)、(a,b)业务查询全是WHERE a? AND b?按说应该走(a,b)结果优化器偏偏选了(a)回表几千行。原因只是(a)的统计信息看起来更诱人。治理方法很直接定期翻数据库的未被使用索引清单。MySQL里可以查performance_schema.table_io_waits_summary_by_index_usageSQL Server里可以查sys.dm_db_index_usage_stats。发现超过30天没有任何读操作的索引就逐个确认后删除。5.2 多个OR条件破坏了索引利用SELECT * FROM orders WHERE status PAID OR user_id 10086;这种情况即使status和user_id上分别有索引MySQL也很可能不会同时使用两者。因为优化器通常无法把OR条件高效拆成两个索引的交集或并集最省事的方案就是全表扫描。解决方案有两个一是UNION ALL拆开两条SQL让它们各自走自己的索引二是评估一下如果两个条件里有一个的选择性极高比如user_id查出来的结果集很小可以把它放在WHERE的最前面辅以条件分支处理。但最稳妥的做法还是UNION ALL实测下来往往能从全表扫描变成两次range扫描。5.3 索引命中了大量行回表代价反而超过全表扫描这是个反直觉但真实存在的场景。假设一张100万行的表二级索引选择性很差比如status只有两种值你按WHERE status FINISHED查询会命中60万行。这时用二级索引就意味着要做几十万次随机回表而全表扫描走聚簇索引是顺序IO——后者往往反而更快。所以优化器在识别到索引命中行数超过全表行数约20%时就会自动放弃索引走ALL。这事不是你写错而是索引本身的设计和查询诉求不匹配。如果SQL确实需要查这么宽的范围就不要指望索引救你应该从统计需求上想办法比如预聚合表、汇总表。5.4 时间范围过大索引反而成了负担我见过有人在近一年订单查询上建了时间索引看起来没问题但业务场景是运营偶尔要看近一年甚至近三年的大盘数据。这种情况时间索引会把大量时间范围内的主键筛出来再回表一次查询触发百万级回表直接在业务高峰期打爆IO。针对这类大范围但低频的分析型SQL我的建议是压根别走线上索引直接引导去只读副本或数仓如果必须在线跑也应该考虑按月分表或者用汇总表。索引是为精准定位少数行设计的不是为拉全量数据设计的。6. 索引不是建完就结束长期维护是策略的一半说实话索引优化里最容易被忽略的一部分是建完索引之后的长期治理。很多团队上线时把索引设计得漂漂亮亮半年后表结构、数据分布、查询模式全变了索引却还停在原地性能自然悄悄劣化。6.1 碎片整理索引也可能生锈B树在持续插入、删除的过程中会产生页分裂和碎片。碎片会让索引扫描时读更多没用的页逻辑读随之上涨。MySQL里可以用OPTIMIZE TABLE重建表SQL Server里可以用ALTER INDEX ... REORGANIZE或REBUILD来整理。要意识到碎片对等值查询影响不大但让范围查询明显变慢。原因是碎片化索引叶子页间的物理顺序混乱通道链表虽然还在但读下一页时往往触发随机IO而不是顺序IO。碎片什么时候需要处理可以定期抽样对数据变化大的核心表每月做一次SHOW TABLE STATUS看Data_free字段MySQL超过20%就可以考虑重建。6.2 关注统计信息的时效别等执行计划歪了才想起统计信息过期几乎是执行计划跳水的头号原因。MySQL里表数据量变化超过10%SQL Server里超过一定行数变化统计信息会自动更新但在高频写入的表上这个阈值可能永远追不上实际变化。我个人的运维节奏是对核心大表每周手动ANALYZE TABLE每次大版本发布涉及表结构变更时也顺手做一次。别小看这个动作它为优化器提供准确的行数预估是索引能被正确选中的前提。6.3 建立索引评审的月度节奏一个健康的索引治理流程不应该等到出了事故才去救火。我的建议是每月固定抽出半天做下面几件事拉本月Top 20慢SQL逐个确认执行计划是否在合理范围。对照索引使用统计清理长期未命中的索引。对比预期内的数据量增长评估现有索引是否需要调整列顺序或加覆盖列。检查新增的业务查询有没有可以直接靠扩展已有索引解决的。这套流程坚持做下来通常能提前发现并消灭掉80%以上的索引性能隐患。最后一个我自己的小习惯每建一个新索引都在索引命名里带上用途备注比如idx_orders_status_time_cover表示状态时间覆盖查询。半年后回头治理时你看着名字就能想起当初为什么建它不用把执行计划翻个底朝天。这个细节在团队协作时尤其有用能省掉不少沟通成本。