★ FEATURED ARTICLE 做了这么多年数据库优化我见过太多系统栽在索引上。刚带项目那几年最怕半夜收到告警某个曾经秒开的查询突然把数据库拖到濒临打满排查下来发现问题往往不在SQL写法本身而在索引——要么建错了要么建少了要么建了一堆根本没用的。说90%开发者踩过索引的坑我实际感受是这个比例可能还说低了。这篇文章把我在真实系统中踩过、也看别人踩过的索引坑集中倒出来从设计选型到执行计划排查再到一个完整系统的优化过程最后聊索引维护。无论是刚开始接触数据库的新手还是被慢查询折磨的开发和运维都能在里面找到对应的场景。1. 索引设计与选型的四个典型误判1.1 加索引就一定能变快全表扫描也会逆袭很多开发者遇到慢查询的第一反应就是“加索引”。这个思路不全错但有个前提经常被忽略索引不是万能加速器在某些条件下数据库引擎会主动放弃索引走全表扫描反而更快。我做了一个很简单的对比场景你大概就理解了。拿一本一千页的书举例正常情况下通过目录查内容最快但如果某一页的内容占了全书一半还要先翻目录、再定位页码、翻过去就不如直接从第一页翻到那一页更快。数据库的逻辑完全相同如果查询需要返回的数据行数占全表比例过高优化器会认为走索引本身需要多次随机IO反而不如一次顺序IO扫描整个表划算。这里的经验阈值一般在20%到30%左右。当查询条件命中的行数超过这个比例别再纠结索引设计先考虑改查询逻辑或者换一种数据统计方案。我实际排查慢查询时第一件事不是看索引而是先算这个查询到底要取多少行——如果取出来的数据占比本身就大加不加索引都救不了。1.2 单字段建索引不等于联合索引优化“最左前缀”是索引优化里被说得最多、也被误解最多的概念。很多人把联合索引当成了多个单列索引的叠加这是一个根本性的误区。联合索引本质上是一棵按多个列依次排序的B树先按第一个列排序再在第一个列等值的情况下按第二个列排序依此类推。这就意味着如果查询条件里跳过了联合索引最左边的列后面的列在索引中根本没法定位。举个具体例子一个表上有联合索引(user_id, create_time)查询条件是where create_time 2024-01-01 and user_id 100这个就能走到索引因为优化器会先用user_id定位。但如果是where create_time 2024-01-01没有user_id条件那这个联合索引就废了因为B树按user_id排好序后create_time已经切成了一段段碎片无法利用索引有序性做范围扫描。这个坑在实际项目里特别常见尤其是那种“这个列经常查询那个列也经常查询”的场景。我见过不少表上建了(a)、 (b)、(a,b)三个索引结果三个索引里只有一个真正被用到剩下两个是冗余。判断联合索引顺序是否合理看三点等值条件放前面范围条件放后面区分度高的列放前面。这三点不能单独看要结合业务实际查询频率来权衡。1.3 覆盖索引到底覆盖了什么回表是另一个让新手困惑的概念。InnoDB的索引结构分主键索引和二级索引二级索引的叶子节点存的是主键值不是完整行数据。这意味着用二级索引查先找到一大串主键再根据这些主键回主键索引去取整行数据这个额外过程就叫回表。回表是全随机IO行数越多越慢。覆盖索引就是让二级索引的叶子节点直接包含你查询所需的全部列这样查询就只需要遍历索引树不需要再回表取数据。覆盖索引的价值在真实项目中非常大尤其是在高频查询、需要返回固定字段的场景。比如用户订单列表页只需要展示订单号、金额、状态如果联合索引包含了这三列再加查询条件(user_id)和排序字段(create_time)整个查询可以在索引里一次完成。使用覆盖索引有个直接要求查询中SELECT的字段必须被索引包含且不能超过索引列范围。不是所有字段都适合塞进索引索引也不是列越多越好列太多会让索引体积变大、写入变慢。合理做法是只把列表页最需要返回的核心字段放进索引涉及大字段如备注、详情内容就拆出去。1.4 普通索引与唯一索引不能拍脑袋选唯一索引和普通索引在写入性能上存在明显差异这是很多人都没意识到的。唯一索引在插入新纪录时必须先读取索引页来判断唯一性是否冲突普通索引插入时没有这个检查步骤可以利用缓冲区的合并机制优化写入。在业务不强制要求数据唯一的情况下优先用普通索引。很多业务一开始为了防重复不管什么字段都上唯一索引结果写入高峰期经常出现性能瓶颈。我见过一个案例某个系统的登录日志表在账号字段上建了唯一索引业务上账号本来就不会重复但每次写入都需要额外做唯一性校验结果这个表成了整个系统的写入瓶颈。后来把唯一索引改成普通索引写入性能明显提升也没有出现任何数据重复问题。唯一索引要用在真正需要防重的核心场景比如订单号、交易流水号而不是到处滥用。2. 执行计划深挖索引失效的现场还原2.1 慢查询日志与EXPLAIN怎么配合使用排查索引问题不能靠猜。第一步一定是开启慢查询日志把执行时间超过阈值的SQL记录下来。我通常把阈值设成1秒业务低峰期再统计重点看那些频繁出现的慢SQL。拿到慢SQL后下一步就是对这条SQL执行EXPLAIN看数据库优化器的执行计划。EXPLAIN输出里最关键的有几列type列表示访问类型从好到差大致是const、eq_ref、ref、range、index、ALL如果看到ALL基本就是全表扫描key列表示实际用到的索引这一列如果为NULL说明没用索引rows列是优化器估计需要扫描的行数Extra列里的Using filesort或Using temporary代表查询里有额外的排序或临时表操作通常是性能隐患。看EXPLAIN有个经验不要只盯着key列要看整体。有些SQL虽然命中了索引但rows高达几十万说明索引选择有问题可能命中了一个区分度很差的索引这时候性能依然会很差。真正好的执行计划应该是type不是ALL、rows远小于全表行数、Extra里没有filesort和temporary。2.2 五个最常见的索引失效现场第一个对索引列使用函数或运算。比如where date(create_time) 2024-01-01这时候create_time上的索引会被跳过。正确做法是改成范围条件create_time 2024-01-01 and create_time 2024-01-02。这个看似简单实际代码里到处都是因为业务上“按格式化日期查”这种写法太顺手了。第二个隐式类型转换。如果字段是varchar类型查询条件里却写数字数据库会把字符串列隐式转成数字类型再比较然后索引就失效了。比如where phone 13800138000如果phone是varchar这个索引几乎必然失效。解决办法是应用层保证传参类型和字段类型一致。第三个LIKE前缀模糊。where name like %张三%匹配以通配符开头B树的有序性就无法被利用索引直接失效。如果是where name like 张三%索引还可以用。前缀模糊查询的解法一般是改用全文索引或搜索引擎但也不是所有场景都值得上搜索引擎如果可以接受一定的延迟单独建一张规范化的搜索表也是现实选项。第四个OR条件搭配不当。如果where a 1 or b 2其中a和b恰好都建了索引数据库可能还会合并但一旦有一侧没索引整个查询基本就退化成了全表扫描。这种情况最好的方案是拆成两个查询后UNION或者给两侧都补上索引。第五个排序字段与索引顺序冲突。查询条件用了索引但ORDER BY的字段不在索引里或者排序方向与索引顺序不一致就会触发filesort。filesort意味着数据库要把结果重新排序行数一多性能就崩。解决思路是把排序字段也纳入联合索引并保持排序方向与索引方向一致。2.3 优化器选错索引rows并不总是可靠完全相同的SQL有时候走索引很快有时候却很慢这背后往往是统计信息在悄悄变化。数据库的优化器不是真的去扫描整个表再决定怎么查而是依赖表的统计信息做判断。统计信息长期不更新优化器就会基于过时的数据做出错误选择。我遇到过最典型的一次某业务表从几十万涨到了几千万行但统计信息一直没刷新优化器始终认为“全表扫描只需要扫几十万行”于是走了全表扫描结果线上查询几十秒超时。刷新统计信息后优化器发现全表行数已经几千万主动选择了正确的索引慢查询立刻消失。这个教训很深刻如果业务表的数据量波动比较大或者定期有大批量导入就要养成定期刷新统计信息的习惯。遇到优化器选错索引的应急手段是用FORCE INDEX强制走某个索引但这只是救火方案治本还是要刷新统计信息或者调整SQL让优化器更准确地估算。长期依赖FORCE INDEX有风险一旦数据分布继续变化强制索引可能从一个错误走向另一个错误。3. 实战复盘一个千万级订单查询的优化全过程3.1 现象与第一步定位某系统订单表在数据量达到千万级之后用户查询自己订单列表的接口突然变慢。明确的现象是首页查询还好翻到十几页之后越来越慢最深层页面能拖到十秒以上。业务方一开始怀疑是数据库负载太高但检查后发现CPU和内存都不紧张于是怀疑到了SQL和索引上。我拿到这个接口的SQL之后先做的是把执行计划拉出来。当时的SQL大概长这样select order_id, amount, status from orders where user_id ? order by create_time desc limit 100000, 20。EXPLAIN显示user_id上的索引确实命中了rows估计有几十万行Extra里出现了Using filesort这意味着数据库在索引里找到用户所有订单后还要再做一次全量排序然后才能取第100001到100020行。这里慢的根源有三层全量表扫描了该用户的所有订单每行都要回表取完整数据因为查询字段里有amount、status等不在二级索引里的列深分页导致limit 100000要遍历前十万行才能定位这个开销不会因为数据少而减少。3.2 第一轮优化先把联合索引做对发现user_id上的单列索引不够用以后我做的第一件事是调整索引结构把这个查询需要的数据全部考虑进去。设计的联合索引为(user_id, create_time, order_id, amount, status)这样查询条件里的user_id能精确命中insert时间create_time天然有序排序不再需要filesortselect里需要的字段也在索引里面可以直接覆盖索引输出。这一步做完之后同一个EXPLAIN出来的结果变化很明显type从ref变成了index或者refrows大幅减少Extra里不再出现Using filesort。实际接口响应时间从原来的七八秒降到了几百毫秒。但仅仅改到这一步还没完因为我发现翻页到很后面的时候耗时又开始上升虽然整体还算可控但这是一个信号深分页还藏着一个隐患。3.3 第二轮优化延迟关联解决深分页问题深分页慢的核心原因是数据库要把前N行的结果都扫描一遍才能返回第N1行这个成本在线性增长。limit 100000, 20意味着要先找到十万条之后才开始返回而前面这十万条的排序和回表开销全部白费。延迟关联方案的思路是先只查出当前页需要的主键然后再用主键去关联原表取完整数据。改成两步第一步select id from orders where user_id ? order by create_time desc limit 100000, 20这一部分如果联合索引覆盖了id和create_time就完全在索引里完成第二步select order_id, amount, status from orders where id in (刚才拿到的主键列表)。这样深分页时的前十万次扫描不再涉及回表和数据传输性能提升非常大。实际改完以后深翻页的耗时也被压到了几百毫秒以内。这里有个细节值得注意如果业务场景是固定客户翻订单列表翻几十页已经是极端行为延迟关联优化到这个程度基本够用。如果是内部后台系统查全量数据建议换成分页游标方式用上一次查询返回的最后一条记录的主键值作为下一次查询的起始位置从机制上彻底消灭深分页。3.4 上线前必做的风险控制索引优化不只改SQL表结构变更本身在线上也是高风险操作。大表加索引时如果直接执行标准DDL可能长时间持锁导致业务写入阻塞。MySQL的DDL在多数情况下支持在线变更但依然会带来主从延迟尤其是大表的索引重建操作延迟可能从秒级放大到分钟级。我每次做这种变更都会选择业务低峰期并且分阶段执行先在一套从库上把索引建好确认执行计划和性能变化再逐步切到主库。还有一点是加索引会影响写入路径。联合索引列数越多每次插入和更新时要维护的索引树就越多热点表的写入性能必然下降。上线前必须观察一段时间的主从延迟和写入延迟指标。如果系统是订单量很大的写入密集型业务新索引上线前务必评估一下写入放大问题避免捡了查询的芝麻、丢了写入的西瓜。4. 索引的“后事”清理、统计与长期维护4.1 冗余索引比没索引更可怕很多表的索引是时间一长自然堆积出来的A同事加了一个、B同事补了一个谁也不知道哪些还在实际使用。冗余索引的危害在于每次写入要维护多棵B树磁盘占用增加查询优化还可能搞不清该用哪个索引。索引(a)和索引(a,b)就是典型的冗余关系前者完全能被后者替代但因为没人处理它就一直默默消耗资源。排查冗余索引有一个简单办法翻阅数据库字典信息找出所有索引列开头相同的组合。然后再配合慢查询日志和性能监控逐个确认哪些索引最近一周、一个月有没有被执行计划选中。如果一个索引长时间没有出现在explain里又没有明确业务逻辑约束就基本可以认为是僵尸索引了删除前做好备份和回滚预案即可。4.2 统计信息过期和碎片化索引时灵时不灵的隐形元凶数据库优化器的决策精度依赖统计信息。周期性执行的统计信息刷新任务非常重要特别是在表的行数发生大幅变化之后。大批量导入或删除数据之后无论如何都应该手动刷新一次统计信息。这一步操作很轻但收益可能非常大能让优化器从“瞎猜”变成“有理有据”。频繁随机插入和删除会让索引页产生碎片。索引的物理存储不是完全紧凑的页分裂和合并会留下空洞导致实际扫描的块数比理论值多很多访问性能自然下降。碎片严重时需要用表优化操作重建索引。这里要慎重表优化在大表上非常耗时且可能加锁不要把它当日常任务来跑通常做法是定期监控碎片率超过一定阈值比如30%再在低峰期统一处理。4.3 如何监控索引的真实使用情况监控索引有没有被用到不能靠“我觉得”要靠数据库自带的能力。数据库的performance_schema会记录各种IO等待事件可以从表级别看到某个索引是否发生大量读写。sys库下面有一张视图专门列出从未被使用的索引这些就是清理候选对象。我长期维护索引的方法是定一个季度周期每个季度导出所有表和索引的统计信息比对慢查询日志中出现的SQL给每条SQL打上具体使用的索引标号然后对一段周期内完全没被使用过的索引进行下线评审评审时看两个维度一个是该索引有没有不可替代的业务约束另一个是它对应的SQL是不是已经被业务废弃。确认没有用途的索引直接移除。这个流程坚持下来整个系统的索引数量不仅没有膨胀反而越来越精简。索引不是越多越稳真正的稳定来自对线上行为的持续理解和清理。我个人觉得索引优化的本质不是技术炫技而是对数据读写代价的深刻理解和权衡。每个索引都该有它服务的SQL、它解决的问题和它被保留的理由没有理由的索引终究会在某一天成为线上事故的伏笔。
阅读完成 · 觉得有帮助？
