慢SQL优化实战：从索引策略到执行计划的性能排查指南

凌晨一点电话响了。业务方声音焦急用户端查询订单列表的接口从平时的200毫秒变成了3秒后台管理系统的月度报表查询更是跑了两分钟还没出结果。我打开慢查询日志几条执行时间接近20秒的SQL赫然列在最前面。这就是典型的慢SQL问题。很多人一听到SQL优化就觉得高深觉得是DBA的专职工作实际上大部分线上性能问题的根因并不神秘——要么索引设计不合理要么查询写法让优化器没法定向走索引要么数据量涨到了某个临界点导致原有的执行计划整体失效。这篇文章就结合实际排查和优化过程从索引策略讲到具体调优手法覆盖慢SQL定位、执行计划解读、索引设计思路、并行优化场景和日常防坑经验希望能帮正在被慢查询困扰的人理清思路。1. 慢SQL为什么会突然出现数据量与执行计划的双重拐点很多团队对慢SQL的第一反应是加索引。这个方向没错但如果搞不清为什么以前快的查询突然慢了加索引也只是碰运气。我的经验是慢SQL的出现往往有两个叠加的拐点。第一个拐点是数据量的量级跃迁。一条查询在10万行数据时走全表扫描可能也就几十毫秒用户感知不明显。但同一张表涨到500万行、1000万行时全表扫描的代价呈线性甚至超线性增长尤其是涉及多表关联、大字段回表的时候响应时间可能直接从几百毫秒恶化到几十秒。MySQL里有一类默认规则当优化器估算需要扫描的行数超过全表的一定比例通常是20%到30%它就会放弃索引选择全表扫描因为此时离散IO的成本高于顺序IO。这个拐点非常隐蔽哪怕索引还在执行计划也可能已经变了。第二个拐点是执行计划的漂移。优化器选择索引的依据是统计信息和代价估算。统计信息不更新、字段的数据分布发生变化比如某个状态值从1%变成50%、或者查询条件里的参数值改变了选择性都会导致优化器换一个低效的执行路径。我遇到过最典型的情况一个order_status字段平时99%都是completed查询条件传pending时走索引很快某天大促期间pending的订单量暴涨到30%优化器一看估算扫描行数太多直接选择了全表扫描结果线上接口瞬间雪崩。所以做SQL优化的第一个动作不是改SQL而是先确认这条SQL一直慢还是最近才变慢如果是一直慢大概率是索引策略从一开始就没设计对如果是变慢优先检查数据量变化、统计信息新鲜度和执行计划是否漂移。定位问题的时间点和解决问题同样重要。2. 索引策略的核心逻辑从B树原理到联合索引的匹配规则索引不是加得越多越好也不是随便给查询条件字段加个索引就万事大吉。要设计好索引得先理解索引为什么能加速以及它加速的边界在哪里。2.1 B树索引加速的本质减少无效数据的访问MySQL的InnoDB索引底层是B树。它的核心优势有两个一是树的高度低一般是2到4层意味着定位到一条记录最多只需要几次磁盘IO二是叶子节点之间通过链表相连对范围查询非常友好。很多人忽略了一点B树在InnoDB里既存储索引也存储数据本身。聚簇索引主键索引的叶子节点直接存整行数据二级索引普通索引的叶子节点存的是索引列和主键值。这个结构决定了重要规律走二级索引查询时如果目标列不在二级索引里每次拿到主键后都要再回聚簇索引取一行完整数据这就是回表开销。回表次数少还好如果满足条件的记录有上万条哪怕每条回表只要0.1毫秒累加起来也是1秒以上的额外耗时。这也是为什么覆盖索引如此重要——查询的列全部包含在二级索引的叶子节点里数据库直接返回索引数据连回表都省了。2.2 联合索引设计等值匹配、最左前缀和排序利用联合索引多列索引的设计是索引策略里最有技术含量的一环。很多新人常见的困惑是我在三个字段上分别建了单列索引查询时它们应该都会生效吧 答案是否定的。MySQL在一个查询里通常只会选择一个索引执行把三个独立索引a、b、c和一个(a,b,c)联合索引混为一谈是常见误区。联合索引的本质是逐列比较排序所以它遵守最左前缀原则查询条件里必须包含联合索引的第一个字段索引才能被用到。比如索引(a,b,c)只有条件覆盖a、(a,b)、(a,b,c)时能走索引直接查b或c索引基本用不上。设计联合索引时我通常按这个顺序确认字段可等值匹配的字段放最前。比如user_id通常是等值条件优先放第一位。把选择性高的字段放前面。选择性是指字段去重后的比值比如status只有成功、失败、处理中三种值选择性极低放最前反而让索引区分度变差。利用索引避免排序。B树的叶子本身有序如果查询条件里带了ORDER BY且排序字段正好是索引的后续列就能避免文件排序。比如(user_id, create_time)这个索引在WHERE user_id 123 ORDER BY create_time场景下本身就是按时间排好的直接顺序读取返回就行。有一种反直觉的情况也值得注意把选择性低的字段放在联合索引前面有时候反而是正确的。比如WHERE status pending AND create_time 2024-04-01如果时间列的选择性明显更高我们会倾向把create_time放前面。但实际还要考虑等值条件与范围条件的区别——等值字段放前面可以让后续字段继续保序而范围条件字段放前面会导致后面的字段无法参与索引匹配所以这类查询把等值的status放前面create_time放在后面利用范围扫描效果往往更好。2.3 索引下推一个容易忽略的性能红利说到联合索引就不得不提索引下推Index Condition Pushdown。这个机制看着抽象其实特别好理解以前当索引里只有部分列时引擎用索引定位到一批主键后会把整行数据捞出来逐条检查剩余条件这被称为先捞后筛。开启索引下推后引擎直接在索引层面把不满足条件的记录先过滤掉再回表。这意味着回表次数大幅减少。举个实际数字。索引是(a,b)查询条件是a 1 AND b 100。没有索引下推时把所有a 1的记录全部回表然后再过滤b 100有索引下推时直接在二级索引里同时判断a和b回表的只有满足b 100的极少量记录。在MySQL 5.6之后默认开启如果查看执行计划看到Extra列里有Using index condition就说明这条SQL已经吃到了这个红利性能比普通的回表扫描要好很多。3. 慢SQL排查的完整链路从日志定位到执行计划定量分析索引策略理论再熟线上排查还是得靠工具和流程。我处理慢SQL有一套固定的动作可以说80%的案例按这个流程走都能找到根因。3.1 第一步先把慢查询捞出来MySQL的慢查询日志是最直接的切入点。生产环境一般建议打开慢查询日志并设置阈值我习惯设成1秒SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;第一条开启慢查询日志第二条定义超过1秒的SQL就会被记录。要注意这个参数对已有连接不生效需要新开的会话才会按新阈值记录或者直接改配置文件后重启。日志里能看到实际的SQL、执行时间、锁等待时间、返回行数、扫描行数。我通常先看两个数字扫描行数和返回行数的比值。一条SQL扫描了5万行最后只返回20条说明大概率在数据访问路径上做了大量无效工作这就是优化的方向。3.2 第二步EXPLAIN的关键列到底在看什么EXPLAIN是最常用的分析工具但要真正读明白它不能只看key列有没有索引。我习惯按以下顺序看type列反映访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就是全表扫描看到index也不是什么好事说明全索引扫描虽然走了索引结构但本质上还是把所有索引行读了一遍。最应该关注的是这条SQL能不能做到ref或者range因为这意味着查询用上了索引进行精确定位或范围定位。possible_keys和key列possible_keys列出优化器认为可用的索引key是最终选用的索引。如果possible_keys有值但key为NULL说明优化器算了代价后觉得走索引不如全表扫描。这时候要思考是统计信息不准还是确实应该全表扫rows列这是执行计划里的预估扫描行数单位是行。这个数和实际扫描行数对比意义重大。预估与实际严重偏离通常指向统计信息过期。Extra列最值得警惕的是Using filesort和Using temporary。出现Using filesort意味着排序没法利用索引数据量一大就可能在内存或磁盘上做额外排序Using temporary通常意味着隐式的临时表操作常见于GROUP BY、DISTINCT或部分子查询场景这两个都容易引发性能瓶颈。3.3 第三步用量纲数据验证而不是凭感觉EXPLAIN给出的是代价估算最终还是要看真实执行。MySQL 5.7以上可以用EXPLAIN ANALYZE直接拿到实际执行时间和扫描行数8.0.18以上支持EXPLAIN ANALYZE SELECT user_id, order_amount FROM orders WHERE status pending AND create_time 2024-01-01 LIMIT 20;结果里能看到类似actual time0.356..2.181 rows20 loops1的输出。这里有个细节actual time的第一段是取第一行数据的时间第二段是取全部行的时间rows20是实际行数。如果实际行数和执行计划的预估行数差了数量级基本可以断定是统计信息不准导致的错误执行计划。到了这一步问题的性质就已经清楚了要么是索引缺失要么是索引设计不合理要么是执行计划选错了索引。接下来就要进入实际优化动作。4. 一次订单查询的真实优化过程从500ms到30ms的路径拆解前面讲了一堆原理和排查工具可能还是有点抽象。下面用一个我实际处理过的订单表查询案例完整走一遍优化过程。表结构大概是这样的CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(12,2), KEY idx_user_create (user_id, create_time) );原始慢SQL长这样SELECT id, order_no, amount FROM orders WHERE user_id 12345 AND DATE_FORMAT(create_time, %Y-%m-%d) 2024-05-20 AND status 1 ORDER BY create_time DESC LIMIT 20;这条SQL的响应时间稳定在500毫秒左右数据量大约800万行。乍一看有联合索引idx_user_create条件也覆盖了user_id按说应该不慢。但执行计划一出来发现问题不少。4.1 第一个坑函数包裹索引列索引失效执行计划里possible_keys显示idx_user_create但key为NULL。原因就在DATE_FORMAT(create_time, %Y-%m-%d)这个写法。当索引列被函数包裹时优化器无法直接比较B树里的原始值索引就废了。这就好比一本电话簿按姓氏拼音排序你偏要按名字的笔画数去查那只能从头翻到尾。这个问题的标准改法是去掉函数把计算放在常量一侧AND create_time 2024-05-20 00:00:00 AND create_time 2024-05-21 00:00:00改成范围条件后create_time的原始值可以直接参与索引比较idx_user_create里以user_id定位后第二个字段create_time自然形成了范围扫描区间。这一个改动就把全表扫描变成了索引范围扫描。4.2 第二个坑索引字段顺序与SQL条件不匹配改完函数问题后执行计划确实走到了索引但我发现rows还是很高达到了两万多行。继续深挖问题出在查询条件里多了个status 1。idx_user_create只包含(user_id, create_time)两个字段status没有参与索引匹配。也就是说InnoDB拿到满足user_id 12345且时间在当天的所有记录后再逐条回表读取数据然后筛掉status ! 1的记录。这个过程并不高效如果user_id 12345在当天有500条记录但有490条都是status 2那就有490次回表是白做的。这个场景正好可以优化联合索引设计。设计时把status字段加进联合索引放在user_id之后、create_time之前ALTER TABLE orders ADD KEY idx_user_status_create (user_id, status, create_time);推荐的联合索引结构为(user_id, status, create_time)。在WHERE里user_id做等值匹配status也是等值匹配索引能够在这两层上精确定位create_time就利用范围扫描同时查询和排序都在索引内完成ORDER BY create_time也能直接顺序读取不再产生filesort。4.3 第三个坑覆盖索引少了一个字段回表成本翻倍索引改成(user_id, status, create_time)之后执行计划上的rows从两万多降到了几百响应时间到了50毫秒左右已经不错了但我觉得还能再压一压。继续看Extra列发现还是有个现象同时出现了Using index condition; Backward index scan说明正在反向扫描索引。条件user_id、status都是等值create_time是范围加上ORDER BY create_time DESC引擎是从后往前读索引完全正常。问题在于目标查询里的order_no和amount字段不在索引中每返回一行都要执行回表。当结果集有几百行时回表几百次是不可避免的开销。如果这张表上需要高频跑这个查询最彻底的优化是把查询需要的列也塞进索引做成覆盖索引ALTER TABLE orders ADD KEY idx_user_status_create_cover (user_id, status, create_time, order_no, amount);本质上是空间换时间。order_no和amount是查询要返回的列把它们放进索引叶子节点后这个查询的全部数据都能从二级索引直接获得回表这一步彻底消失。优化后这条SQL的执行计划变成了Using index响应时间稳定在30毫秒以内。这是典型的从能查出来到查得够快的进阶优化。4.4 深分页问题LIMIT很深的隐藏成本同一个查询还有一个经常被忽视的变体就是分页翻到很后面时的深分页问题例如SELECT id, order_no, amount FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 100000, 20;即使索引已经完全覆盖了数据库也要先读取10万条索引记录然后丢弃前面的99980条最后只返回20条。这个过程越往后越慢本质是扫描了但不用的开销。更优的写法是改造成先取主键再关联回原表。MySQL 8.0以上可以直接用窗口函数也可以分两步SELECT t.id, t.order_no, t.amount FROM ( SELECT id FROM orders WHERE user_id 12345 AND status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) AS tmp JOIN orders t ON t.id tmp.id ORDER BY tmp.create_time DESC;子查询只扫索引拿到的还是主键值然后再用主键去聚簇索引精准取20条。这样能把扫10万行的代价限制在索引层面而不会产生10万次回表。这里有个前提优化前要确保二级索引的叶子节点里有足够的数据支撑排序和分页典型的做法是把create_time放在索引里否则子查询排序还是要回表。5. 并行SQL优化什么时候该用什么时候不该用热搜词里有并行sql优化这个方向确实值得单独聊一聊。很多从Oracle迁移过来的工程师对并行的态度往往两极分化要么完全不用要么到处乱开。我的判断标准很简单——并行主要是用于大查询、批量任务、分析类场景对OLTP类型的短事务几乎没有任何帮助甚至可能帮倒忙。5.1 Oracle并行执行的适用场景Oracle数据库里并行执行可以发生在全表扫描、索引全扫描、表关联、聚合操作等多个环节。它的核心思路是把一个大任务拆成多个小分片多个服务进程同时处理最后汇总结果。官方文档里的PARALLEL提示是这么写的SELECT /* PARALLEL(employees, 4) */ * FROM employees;适合用并行的场景有几个共同特点单条SQL处理的数据量特别大比如几千万行的统计执行时间以分钟计而且系统当前有足够的空闲CPU和IO资源。数据仓库跑月报、历史数据归档、大表刷字段这类任务适合开并行。不适合的场景也很明确高并发OLTP事务、短查询、CPU已经接近满载的系统。试想一下如果一条本来只要20毫秒的小查询你强行给它开8个并行进程光是进程调度和结果汇总的通信开销就可能超过执行本身还会抢占其他事务的CPU资源最终整体吞吐量反而下降。5.2 并行度的设置逻辑并行度设置不是越大越好。Oracle里设置并行度的常用方式是加提示符比如DOP 4表示用4个并行进程。如果什么都不写Oracle会根据parallel_degree_policy和机器的CPU核心数自动决定这时候可能出现一个意外情况本来只想让报表快一点结果优化器把并行度开到了机器CPU的核心数其他应用全部被拖垮。所以实际运维中我会对所有大查询明确指定并行度上限而不是依赖自动策略。并行度一般控制在CPU核心数的一半以内同时要考虑IO子系统是否跟得上。曾经有一台数据库服务器CPU是32核SSD阵列我在一批统计SQL上开了16并行效果还挺明显。后来往同一台服务器上加了批量的普通查询请求发现并行进程把IO队列打满普通查询的等待时间急剧上升最后还是把并行度降到了8整个系统的总吞吐量反而上去了。并行执行还经常和分区表配合使用。如果一张大表按月份做了分区并行扫描时每个进程可以只负责两个分区减少相互之间的资源争用。要注意并行查询在SQL执行计划的Note部分会有提示调试时记得看有没有真正生效有时候因为没有授权提示符只是被当成注释忽略了。5.3 MySQL的并行限制与替代思路MySQL在并行执行方面比Oracle保守很多。官方8.0支持innodb_parallel_read_threads可以在聚集索引的范围扫描层面并行读取但这些并行策略主要针对特定场景对普通OLTP查询的收益并不明显。日常MySQL优化里我更推荐用三种替代思路实现慢查询的并行效果分区裁剪把大表按时间分区查询条件里带上分区键实际执行时只扫描对应分区。批量任务拆分如果是一条处理1000万行的UPDATE可以考虑按主键范围拆成多个小批次UPDATE并配合限流小步快跑。既能错峰利用CPU又能避免大事务占用过多资源和导致主从延迟过大。读写分离把复杂的统计查询引流到只读从库主库专心处理写事务。实际上在MySQL场景里索引策略和查询写法优化到位后很多想用并行解决的问题根本到不了需要并行的程度。6. 索引优化的边界与防坑经验优化做到这里文章已经覆盖了基本的索引设计、慢SQL排查和并行场景。最后这部分想写几个踩过的坑这些经验在文档里往往不会明确写出来但线上实战中却经常遇到。6.1 索引不是越多越好写入放大和优化器选择困难索引的代价在于更新成本。每次INSERT、UPDATE、DELETE除了写入聚簇索引还要同步维护所有二级索引。一张表如果有10个索引写入一条数据就相当于更新11处结构。在写入频繁的订单表上我见过因为索引冗余导致写入性能下降50%以上的情况。另外索引过多还会让优化器花眼。当一条SQL的可选索引太多优化器的代价模型偶尔会选中一个次优索引。解决方法往往是精简索引能复用联合索引前缀的就不再建单独的单列索引。6.2 线上加索引的姿势不是直接ALTER就行大表加索引是一个高频需求但风险极高。MySQL 8.0之前ALTER TABLE ADD KEY在一些版本和场景下会锁表即使是INPLACE算法也会占用大量IO和空间资源大表持续时间可能会很长。如果你在8.0以上版本做这类操作也要先确认ALGORITHMINPLACE能被执行否则数据库可能自动降级为COPY原表。拷大表不仅耗时还会把服务器磁盘空间吃得很紧。稳妥的办法是用工具在线变更比如pt-online-schema-change或gh-ost。它们的基本原理是创建一张新表、通过触发器或binlog同步增量数据、在某个时间点切换表名整个过程业务无感知。我自己在百万级以上的表加索引都会走这个工具并且会在业务低峰期执行。哪怕工具再好物理资源的占用是不可避免的。6.3 隐式类型转换最隐蔽的索引失效有一种索引失效很难被察觉字段的类型和传入参数类型不一致优化器会做隐式转换导致索引无法使用。比如user_id是varchar类型代码里传了一个整型的userId查询条件写成WHERE user_id 12345时MySQL会把两边的值都转成浮点数再比较索引就没法直接命中。执行计划里能看到key列为NULLrows飙高但好多人想不到是类型转换的原因。判断隐式转换的快捷方式在WHERE条件上对索引列做CAST(user_id AS CHAR)之类的操作会导致索引失效但反过来对常量做转换则不产生影响。所以写法上要尽量保证索引列保持原样。6.4 统计信息更新一张命令解决的问题前面反复提到统计信息过期会导致执行计划漂移。在MySQL里ANALYZE TABLE可以重新收集统计信息。Oracle里则常用DBMS_STATS.GATHER_TABLE_STATS。这些命令看似简单却经常被团队遗漏在例行维护清单之外。我的习惯是核心大表在每周的维护窗口里固定执行一次统计信息收集尤其是在大批量数据导入之后立刻执行。这一条操作及时与否能避免掉大量索引明明存在但就是不走的诡异问题。写在最后一套可复用的SQL优化流程这几年做了不少SQL优化工作积累了一套固定的动作。每次接手一条慢SQL我都会按这个顺序来看慢查询日志记录的实际执行时间和扫描行数用EXPLAIN看执行计划重点核对type、key、rows和Extra检查是不是统计信息过期或隐式类型转换结合等值条件、排序字段、回表列重新设计联合索引根据数据量和业务场景判断是否要用覆盖索引或并行策略变更上线前先用测试环境的同量级数据做压测观察执行时间是否真正回落。索引策略和SQL优化没有银弹不可能靠一两个技巧解决所有问题但只要能在数据分布—索引结构—查询写法—执行计划这条链路上建立自己的判断框架绝大多数慢SQL都能被快速定位和解决。遇到问题时切记多做执行计划分析少凭直觉猜。保证每一步操作有据可依线上系统才会越来越稳查问题才会越来越快。