尧图网页设计 
慢查询是数据库性能问题的常见根源。一条写得随意的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条件会让整条查询走全表扫描。拆成UNIONSELECT * 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作为驱动表先筛出statusPAID的少量订单再拿这些订单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子查询会走相关子查询对外层每一行都执行一次内层查询效率低。改成JOINSELECT 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要给内层结果去重再匹配开销大。换EXISTSSELECT * 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行翻到第几页性能都稳。不过这路子适合上一页/下一页式翻页想直接跳到任意页就不适用了。排查慢查询按这个顺序来实际排查的时候大致这么走开慢查询日志收集慢SQL清单按执行次数 × 单次耗时排序先处理总耗时贡献大的查询。对目标SQL跑一遍EXPLAIN看type、key、rows、Extra哪里异常。先解决索引缺失和失效——建索引、改写条件再处理JOIN顺序和子查询。深度分页单独用游标或延迟关联处理。优化完用EXPLAIN验证执行计划变化挑生产低峰期灰度上线盯住QPS和响应时间。话说回来索引不是越多越好。建多了写入开销和存储占用都会涨建索引前得掂量查询频率和写入频率的权衡。优化的本质说白了就一句话减少扫描行数避免回表和额外排序把每一行IO都花在真正需要的数据上。 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条件会让整条查询走全表扫描。拆成UNIONSELECT * 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作为驱动表先筛出statusPAID的少量订单再拿这些订单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子查询会走相关子查询对外层每一行都执行一次内层查询效率低。改成JOINSELECT 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要给内层结果去重再匹配开销大。换EXISTSSELECT * 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行翻到第几页性能都稳。不过这路子适合上一页/下一页式翻页想直接跳到任意页就不适用了。排查慢查询按这个顺序来实际排查的时候大致这么走开慢查询日志收集慢SQL清单按执行次数 × 单次耗时排序先处理总耗时贡献大的查询。对目标SQL跑一遍EXPLAIN看type、key、rows、Extra哪里异常。先解决索引缺失和失效——建索引、改写条件再处理JOIN顺序和子查询。深度分页单独用游标或延迟关联处理。优化完用EXPLAIN验证执行计划变化挑生产低峰期灰度上线盯住QPS和响应时间。话说回来索引不是越多越好。建多了写入开销和存储占用都会涨建索引前得掂量查询频率和写入频率的权衡。优化的本质说白了就一句话减少扫描行数避免回表和额外排序把每一行IO都花在真正需要的数据上。