SQL查询性能优化的实战手册——从执行计划到索引调优-平芜编程栈

慢查询是数据库性能问题的常见根源。一条写得随意的SQL，数据量小的时候感觉不到什么，等表涨到千万行，就可能拖垮整个实例。这篇从执行计划解读入手，覆盖索引失效、JOIN优化、子查询改写、深度分页这些高频场景，每类问题都给出"慢查询原文-问题定位-优化后SQL"的对比，方便直接对照排查。

先把EXPLAIN执行计划看明白

拿到一条慢SQL，第一步是用EXPLAIN看执行计划。以MySQL为例，下面几个字段值得重点关注：

type：访问类型，反映扫描方式。从好到差大致是 const > eq_ref > ref > range > index > ALL。出现ALL就是全表扫描，通常是优化的重点；range是范围扫描，依赖索引；ref表示通过非唯一索引等值匹配。
key：实际使用的索引。如果为NULL，说明压根没走索引，得排查索引缺失或失效。
rows：预估扫描行数。这个值越接近实际结果集越好，扫100万行只返回10行，选择度就很差。
Extra：附加信息。Using index表示覆盖索引，不用回表，性能好；Using where表示存储引擎返回数据后还得在Server层过滤；Using filesort表示没法用索引完成排序，得额外排一次；Using temporary表示用了临时表，GROUP BY和DISTINCT里常见。

排查的时候有个顺序值得记住：先消灭type为ALL的全表扫描，再处理Using filesort和Using temporary。rows偏大就考虑加索引、调索引，把过滤选择度提上去。

索引失效，往往就这几种情况

函数把列包住了

慢查询原文：

SELECT * FROM orders WHERE YEAR(create_time) = 2024;

对索引列用函数，索引就废了，退化为全表扫描。改成范围条件，让create_time上的索引生效：

SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

隐式类型转换

慢查询原文：

SELECT * FROM users WHERE phone = 13800138000;

phone字段是varchar，传入的值却是数字，数据库会把phone转成数字再比较，索引就这么失效了。传个字符串字面量进去就好：

SELECT * FROM users WHERE phone = '13800138000';

类型对上了，索引正常走。

最左前缀被跳过了

慢查询原文（联合索引 idx(a,b,c)）：

SELECT * FROM t WHERE b = 1 AND c = 2;

联合索引遵循最左前缀原则，跳过前导列a直接用b、c，索引使不上劲。带上最左列：

SELECT * FROM t WHERE a = 0 AND b = 1 AND c = 2;

要是业务确实只按b、c查，那就单独给(b,c)建个索引。

OR条件拖出全表扫描

慢查询原文：

SELECT * FROM orders WHERE user_id = 100 OR status = 'PAID';

user_id有索引而status没有，OR条件会让整条查询走全表扫描。拆成UNION：

SELECT * FROM orders WHERE user_id = 100 UNION SELECT * FROM orders WHERE status = 'PAID';

两边各自走索引再合并去重。也可以给status字段补个索引，让两边都能走索引。

JOIN怎么写才不拖后腿

让小表驱动大表

慢查询原文：

SELECT * FROM order_detail d JOIN orders o ON d.order_id = o.id WHERE o.status = 'PAID';

order_detail是大表，orders相对小。拿大表当驱动表逐行去小表查，扫描成本高。换个写法：

SELECT * FROM orders o JOIN order_detail d ON d.order_id = o.id WHERE o.status = 'PAID';

把过滤后结果集较小的orders作为驱动表，先筛出status='PAID'的少量订单，再拿这些订单id去order_detail里找。同时确保被驱动表order_detail的关联字段order_id上有索引。

被驱动表的关联字段必须有索引

慢查询原文：

SELECT * FROM users u JOIN user_login_log l ON l.user_name = u.name WHERE u.create_time > '2024-01-01';

user_login_log的关联字段user_name没索引，每次关联都全表扫描。加上：

ALTER TABLE user_login_log ADD INDEX idx_user_name(user_name); -- 关联字段类型与排序规则需与u.name一致，否则仍可能失效

这里有个容易踩的坑：关联两端的字段类型、字符集、排序规则得一致，不然索引照样失效。

子查询怎么改更顺

IN子查询改JOIN

慢查询原文：

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level >= 5);

部分版本下IN子查询会走相关子查询，对外层每一行都执行一次内层查询，效率低。改成JOIN：

SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip_level >= 5;

让优化器自己选更优的连接顺序，配合users表vip_level或id上的索引。

用EXISTS替代IN

慢查询原文：

SELECT * FROM large_table l WHERE l.key IN (SELECT key FROM small_table);

外层大表、内层结果集小时，IN要给内层结果去重再匹配，开销大。换EXISTS：

SELECT * FROM large_table l WHERE EXISTS (SELECT 1 FROM small_table s WHERE s.key = l.key);

EXISTS对每一外层行做一次内层匹配，配合内层表key上的索引效率更高。反过来的情况——外小内大——IN更合适，得看数据量来定。

深度分页的两种解法

翻到几十万页还想不卡，就是深度分页要解决的问题。

慢查询原文：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;

LIMIT 1000000,20会先扫前100万行再丢掉。越往后翻越慢。

延迟关联

SELECT * FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20 ) t ON o.id = t.id;

子查询只扫索引列id（假设create_time和id上有联合索引，能走覆盖索引），拿到20个id再回表取完整数据，回表行数大幅减少。

游标分页

-- 假设上一页末尾create_time为ct0、id为id0 SELECT * FROM orders WHERE create_time < ct0 OR (create_time = ct0 AND id < id0) ORDER BY create_time DESC, id DESC LIMIT 20;

用上一页末尾的值当过滤条件，每次只扫20行，翻到第几页性能都稳。不过这路子适合"上一页/下一页"式翻页，想直接跳到任意页就不适用了。