SQL优化15招：从慢SQL定位到索引与查询改写实战

上周处理了一个线上问题一张 1200 万行的订单表一个统计营收的接口从 200ms 慢慢涨到 18 秒调用方疯狂告警。我第一件事不是加缓存也不是拆库而是把慢查询日志打开把这 SQL 捞出来。EXPLAIN 一看typeALL扫描行数 1200 万Extra 里还挂着一个 Using filesort。问题不在数据量而在一条本该走索引的条件因为类型不匹配彻底失效。这类 case 我处理过太多今天把 SQL 优化里最常用、最好用的 15 种核心策略整理出来每条都会讲清原理和实战写法。无论你用的是 MySQL、PostgreSQL 还是其他关系型数据库这些思路都通用尤其适合被慢 SQL 折磨过的后端开发、DBA 和兼职运维的同学。1. 慢SQL定位优化工作真正的起点1.1 慢查询日志与工具聚合先把“凶手”捞出来很多人接到慢 SQL 反馈第一反应是登录服务器 top 看一眼或者直接翻业务日志猜。这样做效率极低。正确的第一步永远是开启慢查询日志让数据库自己把超过阈值的 SQL 记下来。MySQL 里你需要确认三个参数SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time1代表超过 1 秒的 SQL 都会被记录这个值建议根据业务峰谷调整。线上核心交易库设成 1 秒没问题如果是分析型库可能 5 秒更合理否则日志量会大到反过来拖垮 IO。log_queries_not_using_indexes一定打开它能帮你抓住那些“虽然没超过 1 秒但全表扫描”的隐患 SQL。慢日志里最关键的不是 SQL 文本而是Query_time和Rows_examined这两个字段。前者告诉你“慢到什么程度”后者告诉你“到底扫了多少行”。我见过太多人只看 Query_time其实 Rows_examined 才是判断优化空间的核心指标。比如下面这条# Query_time: 3.21 Lock_time: 0.000123 Rows_sent: 20 Rows_examined: 9685102 SELECT order_id, order_status, pay_amount FROM order_info WHERE user_id 88776 ORDER BY created_at DESC LIMIT 20;返回 20 行却扫了近千万行这种 SQL 的优化收益是肉眼可见的。日志一多就需要工具聚合MySQL 自带的mysqldumpslow可以按平均耗时排序但我更推荐 Percona Toolkit 里的pt-query-digest它能按指纹把 SQL 归一化直接告诉你哪一类 SQL 累加消耗了最多时间比一条条看日志高效得多。1.2 EXPLAIN 执行计划解读看懂访问路径再动手捞出慢 SQL 之后下一步就是 EXPLAIN。很多新手只会看 type 是不是 ALL实际上执行计划里每个字段都有价值。EXPLAIN SELECT order_id, order_status, pay_amount FROM order_info WHERE user_id 88776 ORDER BY created_at DESC LIMIT 20;优化前大概是这样的结果idselect_typetabletypepossible_keyskeyrefrowsExtra1SIMPLEorder_infoALLNULLNULLNULL9685102Using where; Using filesort这里面 typeALL 就是全表扫描rows 是优化器估算的扫描行数Using filesort 表示ORDER BY created_at需要额外的排序操作。真实场景里type 的优劣排序大致是system const eq_ref ref range index ALL。看到index也别高兴太早它表示扫描了整棵索引树本质上和全表差不多。Extra 字段里几个高频词也需要心里有数Using index覆盖索引最高效。Using index condition索引下推生效。Using where存储引擎返回后 Server 层再做过滤。Using temporary用了临时表通常伴随 group by 或 distinct。Using filesort文件排序和数据量、排序字段是否走索引强相关。MySQL 8.0.18 之后还提供了EXPLAIN ANALYZE会真实执行 SQL 并输出每步的实际耗时和行数比传统 EXPLAIN 的估算值更有参考价值。不过要小心它是真的会执行语句的DML 场景慎用。1.3 一次完整的定位链路从日志到执行计划到索引修复我把前面的投诉场景完整还原一遍。慢日志里看到那条营收统计 SQLSELECT DATE(created_at) AS day, SUM(pay_amount) FROM order_info WHERE created_at BETWEEN 2024-01-01 00:00:00 AND 2024-01-31 23:59:59 GROUP BY DATE(created_at);EXPLAIN 显示 typeALLrows 接近全表。问题出在DATE(created_at)这个函数上它把 created_at 列包了一层函数导致该列的索引根本没法用。我改成等价的闭区间条件并建了idx_created_at(created_at)索引ALTER TABLE order_info ADD INDEX idx_created_at (created_at);再把 SQL 改成SELECT created_at AS day, SUM(pay_amount) FROM order_info WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-02-01 00:00:00 GROUP BY created_at;执行计划从 typeALL 变成了 typerange执行时间从 18 秒降到 0.4 秒。这个 case 告诉我们定位慢 SQL 的目标不是“优化出一条 SQL”而是搞清楚瓶颈发生在访问路径、排序、临时表还是数据分布再对症下药。2. 索引侧核心策略B树用好了80%的问题都会消失2.1 策略3复合索引最左前缀——顺序写反等于白建复合索引是 SQL 优化里使用频率最高的手段但很多人把字段顺序搞反。最左前缀原则的意思是查询条件必须从复合索引的最左列开始连续匹配才能走索引。比如我建一个(user_id, created_at)复合索引那么以下查询可以命中WHERE user_id 88776 WHERE user_id 88776 AND created_at 2024-01-01但下面这个查不了索引WHERE created_at 2024-01-01为什么因为 B 树先按 user_id 排序再按同一 user_id 下的 created_at 排序。没有 user_id 约束时created_at 在整棵树里是全局无序的。所以设计复合索引时要把等值条件列放前面范围条件列放后面。如果多个等值列把区分度高的放前面通常收益更大。2.2 策略4覆盖索引——把回表省掉覆盖索引是指查询需要的所有字段都能在索引树里找到不需要再回到聚簇索引取整行数据。这是执行计划里Using index的来源。实战里最典型的情况是分页和 count。比如SELECT user_id, created_at FROM order_info WHERE user_id 88776 AND created_at 2024-01-01 ORDER BY created_at DESC;如果只有idx_user_created(user_id, created_at)这个查询在索引树上就能拿到 user_id 和 created_at完全不用回表速度极快。这时候如果把select user_id, created_at改成select *每个命中的记录都要回表性能必然下降。后面我会在策略10里继续讲字段裁剪和覆盖索引的配合关系。2.3 策略5索引下推——引擎层先过滤掉一部分索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化默认开启。它的原理是让存储引擎在读取索引记录时直接对索引中包含的字段做条件判断只把满足条件的记录回表而不是把所有索引记录都回表后再由 Server 层过滤。举个例子联合索引(city, age)查询SELECT * FROM user_address WHERE city 杭州 AND age BETWEEN 18 AND 30;没有 ICP 时存储引擎先通过 city杭州 找到所有主键再逐一回表取出完整记录交给 Server 层判断 age 范围。有 ICP 时存储引擎在索引扫描阶段就用 age 条件筛掉不满足的记录回表次数大幅减少。执行计划里 Extra 显示Using index condition就代表生效了。ICP 对那种“索引范围命中但还要过滤大量行”的场景收益特别大。不过它需要索引字段覆盖到过滤列如果你建的复合索引只有(city)那 age 条件还是压不到引擎层。2.4 策略6隐式类型转换——索引列被悄悄“加工”了回到开头那个线上案例。user_id 在表里是 VARCHAR 类型应用层传参时写的是user_id 88776没有引号。MySQL 的规则是被比较的列是字符串传入的是数字时会把列的字符串值转成数字再比较。这一转索引就废了。-- 你会发现索引失效 WHERE user_id 88776 -- 正确写法 WHERE user_id 88776验证方法很简单SELECT 10 9;返回 1说明字符串被转成了数字。所以凡是字符串类型的关联列、查询条件列传参类型必须严格和 DDL 对齐。这个坑在 Java、Go 这类强类型语言里尤其常见因为 ORM 框架有时候会根据实体字段类型自动转换参数排查时有一定隐蔽性。2.5 策略7索引列上的函数与运算——隐形杀手对索引列做任何函数计算或算术运算都会让优化器放弃索引。比如WHERE DATE(created_at) 2024-03-15 WHERE amount 10 100 WHERE YEAR(created_at) 2024这些写法看似没问题但实际上每一行都要先计算完才能比较索引完全没有用武之地。正确的替换方式是WHERE created_at 2024-03-15 00:00:00 AND created_at 2024-03-16 00:00:00 WHERE amount 90这里有个容易被忽略的变体如果查询条件是WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31BETWEEN 本质上是闭区间会包含 2024-01-31 00:00:00 之后到当天 23:59:59 的所有记录。如果业务只想统计 1 月数据请用 2024-01-01 AND 2024-02-01。这个细节不仅影响索引使用还影响统计准确性。2.6 策略8like 前置通配符——模糊匹配的代价LIKE %关键字%这种包含匹配无法使用普通 B 树索引因为目标字符串可能出现在字段任意位置。LIKE 关键字%则可以走 range 扫描因为它符合索引前缀有序的特性。业务里如果确实需要包含匹配有几种出路用全文索引MySQL 8.0 自带的 ngram 全文解析器可以支持中文分词。数据量小的时候接受全表扫描但要在代码注释里标明原因。数据量大且搜索条件复杂考虑引入外部检索引擎而不是把数据库拖下水。注意很多人会尝试LIKE %关键字反转字符串建索引这种骚操作只适合极个别场景维护成本很高我不建议常规业务用。2.7 策略9区分度与冗余索引——索引不是越多越好索引区分度就是“这个列有多少种不同值”。区分度太低比如 status 只有 0、1、2 三种值单独建索引时优化器扫一遍索引和全表扫描成本差不多干脆不用。区分度可以通过 SQL 算出来SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM order_info;值越接近 1索引效果越好。但要注意低区分度列不是绝对不能建索引而是要看怎么用。比如查询是WHERE status 1 AND created_at 2024-01-01建(status, created_at)联合索引就能让 B 树先按 status 划分成几棵子树再对 created_at 做范围扫描这种场景依然有价值。冗余索引则是另一类问题。表上已经有idx_user_created(user_id, created_at)又单独建了idx_user_id(user_id)后者就是冗余的因为联合索引的最左前缀已经覆盖了 user_id 单列查询。冗余索引不仅浪费磁盘还会拖慢 INSERT、UPDATE 的写入速度。MySQL 可以直接查系统视图SELECT * FROM sys.schema_redundant_indexes;我见过一张 2000 万行的表上有 8 个索引其中 3 个是冗余的每次写入都要维护 8 棵 B 树写入慢也就不奇怪了。3. 查询改写策略不动表结构也能让SQL快一个数量级3.1 策略10select * 换成明确字段列表select *的问题不在于网络传输而在于它让覆盖索引策略失效。前面策略4里说过如果索引树中有查询所需的全部列执行计划会出现Using index完全不用回表。但select *需要所有列索引树里必然没有完整数据于是只能回表。把select *改成实际需要的字段往往能带来数量级的提升。比如后台列表页只需要订单号、状态、金额、创建时间就没必要把支付时间、收货地址、备注这些大字段一起查回来。注意这不是让你把所有表结构都改成宽表而是让查询需求最小化。有一种例外业务明确需要全字段展示比如运营后台详情页。这时候强行裁剪字段会导致代码里硬编码列名后续表结构变更反而更麻烦。我的建议是对高频接口做字段裁剪对低频管理端详情页保持select *可接受但要确认返回行数不大。3.2 策略11深分页优化的两种实战写法LIMIT 900000, 20慢在扫描和回表了 90 万行后才丢弃前面的记录。第一种优化方式是延迟关联先只查主键或者覆盖索引字段再回表取完整数据SELECT o.order_id, o.order_status, o.pay_amount FROM order_info o INNER JOIN ( SELECT order_id FROM order_info WHERE user_id 88776 ORDER BY created_at DESC LIMIT 900000, 20 ) t ON o.order_id t.order_id;子查询里的SELECT order_id可以完全走(user_id, created_at)覆盖索引只返回目标主键然后再用主键关联取回 20 行完整数据。相比直接select * limit 900000,20回表次数从 90 万次降到了 20 次效果立竿见影。第二种是游标分页也叫 keyset pagination适合前后端交互可以改造的场景SELECT order_id, order_status, pay_amount FROM order_info WHERE user_id 88776 AND (created_at, order_id) (2024-03-01 12:00:00, 123456) ORDER BY created_at DESC, order_id DESC LIMIT 20;这里用(created_at, order_id)做成了一个稳定的位置锚点每次从上次最后一条记录之后继续取。它的优势是无论翻到第几页扫描行数始终保持在很小的范围。缺点是要求排序字段唯一且前端翻页必须传入上一页的最后一个值无法直接跳转到任意页。3.3 策略12join 优化——小表驱动大表关联列必须有索引很多慢 join 不是 join 本身的问题而是被驱动表的关联列没有索引。MySQL 的执行逻辑是先从驱动表取一批行再拿着关联字段去被驱动表匹配。所以驱动表越小越好被驱动表的关联列上必须有索引。比如订单表 order_info 1500 万行用户表 users 10 万行。想统计 VIP 用户的订单应该让 users 作为驱动表SELECT u.id, u.nickname, COUNT(o.order_id) FROM users u LEFT JOIN order_info o ON u.id o.user_id WHERE u.level VIP GROUP BY u.id, u.nickname;执行计划里第一行如果是 order_info说明优化器判定错了驱动顺序。极端情况下可以用STRAIGHT_JOIN强制指定但我建议先检查两边的统计数据是否过期多数情况下优化器比人更靠谱。还有一个隐藏点join 字段的类型必须一致。users.id 是 BIGINTorder_info.user_id 是 VARCHAR即使内容相同join 时依然可能发生隐式类型转换导致 order_info.user_id 上的索引失效。上一章的策略6在这里同样适用。如果被驱动表关联列没有索引MySQL 会使用 join buffer 做块嵌套循环连接表面上是Using join buffer (Block Nested Loop)本质上是把驱动表的数据加载到内存里反复扫描被驱动表。调大join_buffer_size只能缓解症状正确做法是给被驱动表建索引。3.4 策略13子查询改写 join 要看清场景传统观念里“子查询慢join 快”这句话在 MySQL 5.6 之后已经不完全正确了。现代优化器会把很多 IN 子查询改写成 semi join自动完成去重和优化SELECT * FROM order_info WHERE user_id IN ( SELECT id FROM users WHERE level VIP );EXPLAIN 里如果看到FirstMatch或者Materialize说明优化器已经在做半连接优化不需要你手动改写。真正需要警惕的是相关子查询也就是子查询里引用了外层表的列SELECT o.*, (SELECT nickname FROM users u WHERE u.id o.user_id) AS nickname FROM order_info o;这个标量子查询会随着外层每一行反复执行等于每行一次索引查找。改成 join 通常更好SELECT o.*, u.nickname FROM order_info o LEFT JOIN users u ON u.id o.user_id;判断标准很简单子查询是否和外表相关。不相关的子查询优化器有各种改写手段不一定比 join 差相关的子查询大概率要重写成 join 或临时表。3.5 策略14union all 优先、排序分组临时表的规避UNION和UNION ALL的区别是前者要去重。去重意味着 MySQL 需要把所有结果放进临时表做唯一性检查这个开销在小数据量时无所谓但在千万级表上非常明显。如果两个查询的结果集在业务上不可能重复直接用UNION ALL。另一个和排序分组强相关的坑是Using temporary; Using filesort。比如SELECT user_id, COUNT(*) FROM order_info WHERE created_at 2024-01-01 GROUP BY user_id ORDER BY user_id;如果没有合适的索引GROUP BY 通常需要临时表再排序。但如果你建了(created_at, user_id)索引查询条件里的 created_at 等值或范围前缀配合 user_id 的有序性GROUP BY 和 ORDER BY 都能直接利用索引顺序彻底避免临时表和文件排序。最后说ORDER BY RAND()这是性能毒药它会为每一行生成随机数再全表排序。真要随机取几条可以用主键范围随机抽样或者维护一个随机偏移量千万不要在线上直接 RAND 排序。还有COUNT(*)的优化也值得提一句。InnoDB 不像 MyISAM 那样存储精确行数COUNT(*) 必须扫描数据。优化器会自动选择最小的二级索引来扫描所以如果你只需要总数尽量让表上存在一个较小的二级索引。精确计数维护成本过高时用百行以内的近似值或者维护计数表、缓存是更务实的方案。4. 并行SQL优化与分区裁剪单条语句之外的架构级加速4.1 策略15并行SQL的适用场景与落地思路并行 SQL 优化这几年讨论很多但对 MySQL 用户要泼一盆冷水MySQL 8.0 至今没有像 PostgreSQL、Oracle 那样成熟的单 SQL 并行执行框架innodb_parallel_read_threads只影响部分内部操作不是通用的并行查询。所以我在实际项目中做并行优化更多是“把一条大 SQL 拆成多条小 SQL在应用层并发执行再合并结果”。什么样的 SQL 适合拆满足三个条件数据量大、耗时长、可以按某个维度自然分片。最典型的例子是统计报表-- 原本一条 SQL 跑五分钟 SELECT created_at, SUM(pay_amount) FROM order_info WHERE created_at 2024-01-01 AND created_at 2024-02-01 GROUP BY created_at;可以按天拆成 31 条 SQL开 8 个协程并发执行最后在应用层合并。注意并发不是越大越好数据库连接池、CPU、缓冲池都会成为瓶颈。我一般控制并发数在 4 到 8 之间并且让查询错开执行时间避免瞬时把所有磁盘 IO 打满。如果你用的是 PostgreSQL情况会好很多。max_parallel_workers_per_gather可以让顺序扫描、聚合、join 自动并行。但并行只在 SQL 运行时间较长时才有收益比如 50ms 以内的查询增加并行度反而因为调度开销变慢。所以不要冲动地把并行 worker 数调到很夸张要结合执行计划里启动成本和实际耗时判断。4.2 分区裁剪与分片并发的配合分区表的核心价值不是“数据存得更漂亮”而是分区裁剪查询条件带上分区键后优化器直接跳过无关分区。我当时升级营收统计业务时把订单表按 created_at 做了 RANGE 分区CREATE TABLE order_info ( id BIGINT NOT NULL, user_id BIGINT NOT NULL, pay_amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p2023q4 VALUES LESS THAN (TO_DAYS(2024-01-01)), PARTITION p2024q1 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p2024q2 VALUES LESS THAN (TO_DAYS(2024-07-01)) );这里有一个容易踩的坑分区键必须包含在主键或唯一键里否则 MySQL 直接报错。所以上面我把主键改成了(id, created_at)联合主键。查询时如果 where 条件里出现created_at范围EXPLAIN 的 partitions 字段会显示只访问少数分区这就是分区裁剪生效了。分区裁剪和并行分片是天然的搭档。比如按 user_id 做 HASH 分区后应用层可以开多个连接每个连接查一个分区最后合并结果。这种方式在做月度、季度聚合时非常有效但要提前设计分区数量别等表到千万级别再改那时候 ALTER 分区的时间成本会让人崩溃。4.3 优化顺序建议先验证再动手最后复盘分享一个我常用的排查顺序也算是对整个优化流程的总结先看执行计划判断访问路径全表扫描还是索引扫描有没有 Using filesort、Using temporary。确认 SQL 写法有没有踩坑隐式类型转换、函数包裹索引列、like 前置通配符。再检查索引设计联合索引顺序是否合理能不能覆盖索引有没有冗余索引。最后考虑 SQL 改写和架构手段深分页改写、join 优化、拆并行、分区裁剪。优化完一定要对比前后执行计划不要只看接口耗时。耗时受缓存和网络影响很大执行计划才能反映真实访问路径。我习惯把优化前后的 EXPLAIN 结果和实际执行耗时记录在案方便后续复盘。有一次我发现一条 SQL 加了索引后从 12 秒降到 0.2 秒但三天后因为数据分布变化又慢到 5 秒翻出当时的执行计划一对比才发现统计信息过期导致优化器没走新索引ANALYZE TABLE之后立刻恢复。所以优化不是一次性的线上数据在变SQL 也要定期回头验证。