一条SQL拖垮整个数据库这事每天都在发生。生产环境一次“慢查询屠杀”凌晨3点核心交易表因一个漏写索引的WHERE order_date CURDATE()触发了全表扫描 文件排序瞬间占满磁盘IO数据库连接数飙到上限订单服务全面瘫痪20分钟。而事后定位时发现慢查询日志里那条SQL执行时间从正常的50ms飙升到了48秒——差了一个960倍的数量级。慢查询不是“偶尔慢一下”而是系统性的性能定时炸弹。本文将带你从慢查询的“第一现场”出发逐步深入EXPLAIN执行计划的内核通过索引优化的四个层级、SQL重写的十种实战技巧、以及三个真实生产案例彻底掌握MySQL查询优化的屠龙之术。一、慢查询从“开启日志”到“精准破案”1.1 生产环境慢查询配置——别再默认关闭了很多MySQL实例默认slow_query_log OFF相当于在裸奔。正确的生产配置如下sql-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 在线开启重启失效建议写入my.cnf SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.5; -- 捕捉超过500ms的SQL高并发业务建议0.1~0.5 SET GLOBAL log_queries_not_using_indexes ON; -- 没有索引的SQL也记录 SET GLOBAL log_throttle_queries_not_using_indexes 10; -- 每分钟最多记录10条无索引SQL防止日志爆炸my.cnf 稳定配置ini[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow-query.log long_query_time 0.3 log_queries_not_using_indexes 1 log_slow_admin_statements 1 # 记录慢的管理命令OPTIMIZE, ANALYZE TABLE min_examined_row_limit 1000 # 扫描行数超过1000才记录1.2 慢查询分析神器pt-query-digestMySQL自带的mysqldumpslow功能太弱生产环境首选Percona Toolkit的pt-query-digest。bash# 分析今天的慢查询日志按查询时间降序输出前10 pt-query-digest /var/log/mysql/slow-query.log --limit 10 # 分析并输出到报告文件 pt-query-digest /var/log/mysql/slow-query.log --since 2026-06-04 00:00:00 --until 2026-06-04 23:59:59 slow_report_0604.txt输出报告中最关键的三个指标Response time总响应时间占比 → 哪个SQL是最大元凶Rows examine扫描行数 vs 返回行数扫描/返回比理想值接近1:1Full scan全表扫描标记1.3 一条价值千万元的慢查询日志实例sql# Time: 2026-06-04T03:15:23.123456Z # UserHost: order_app[order_app] [10.0.1.100] # Query_time: 45.832901 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 5847296 SET timestamp1749071723; SELECT o.order_id, o.amount, u.name, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status PAID AND DATE(o.create_time) 2026-06-03 AND u.vip_level 3 ORDER BY o.amount DESC LIMIT 10;问题一眼可见Rows_examined: 5847296→ 扫描了584万行只返回10行效率极差DATE(o.create_time) ...→ 对索引列使用函数索引失效LEFT JOINWHERE u.vip_level 3→ 实际将LEFT JOIN变成了INNER JOIN可能误导优化器大表orders没有合适索引status和create_time分开索引无法合并优化后该SQL从45秒降到0.08秒QPS从35提升到2100。二、EXPLAIN 执行计划——一眼看穿SQL的“五脏六腑”EXPLAIN是MySQL查询优化的手术刀每个字段都藏着关键信息。我们拆解最核心的字段。2.1 type连接类型性能从好到差type含义是否可用system系统表只有一行极少见const主键或唯一索引等值匹配完美eq_ref连接时使用主键或唯一索引优秀ref非唯一索引等值匹配良好range索引范围扫描BETWEEN, , , IN可接受index遍历整个索引树比全表好一点较差ALL全表扫描绝对禁止在生产出现原则线上OLTP系统大部分查询的type至少应为range核心查询必须是ref或const。2.2 possible_keys vs keypossible_keys优化器认为可能用到的索引key实际选择的索引常见陷阱possible_keys有值但key为NULL → 优化器认为走索引不如全表扫描。原因可能是索引区分度太低如性别字段或统计信息过期。sql-- 更新统计信息让优化器重新评估 ANALYZE TABLE orders;2.3 rows优化器估算需要扫描的行数rows不是精确值但量级非常重要。如果估算值远大于实际返回行数说明过滤条件严重依赖索引。案例一个rows500000但LIMIT 10的查询很可能在filesort或临时表中处理了50万行后才丢弃。2.4 Extra魔鬼藏在细节里Extra 信息含义处理方式Using where使用WHERE条件过滤正常尽量让过滤发生在索引层Using index覆盖索引不回表最佳状态Using index condition索引下推ICPMySQL 5.6 好特性减少回表Using filesort需要额外排序不能利用索引顺序必须优化为ORDER BY字段建索引Using temporary使用临时表GROUP BY或DISTINCT无索引高危必须加索引Using join buffer连接无索引使用缓存块需要为连接字段加索引一条必须看懂的EXPLAIN输出text------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | key | rows | Extra | ------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | 2345678 | Using where; Using filesort | -------------------------------------------------------------------------------------------------------------------诊断typeALL全表扫描234万行Using filesort→ 死定了必须重建索引。三、索引优化从“随便建”到“艺术级设计”3.1 联合索引的“三最法则”最左前缀、最高区分度、最少字段。案例订单表需要按status、create_time、user_id查询。sql-- ❌ 错误设计区分度低的字段放前面 CREATE INDEX idx_status_time_user ON orders(status, create_time, user_id); -- status只有PAID,UNPAID,CANCELLED等少数值索引树很快遍历大量相同值 -- ✅ 正确设计区分度高的字段优先user_id唯一性高其次范围查询字段放最后 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); -- 但如果查询条件是WHERE status PAID则索引无法使用最左前缀需要重新评估更精确的设计方法分析查询模式将等值查询字段放在前面范围查询字段、、BETWEEN放在最后。3.2 覆盖索引零回表的极致性能当索引中包含查询所需的所有列时InnoDB不需要回表直接从索引树返回数据性能提升巨大。sql-- 查询需求根据订单号获取订单金额和状态 SELECT order_no, amount, status FROM orders WHERE order_no ORD123456; -- 创建覆盖索引 CREATE INDEX idx_order_no_cover ON orders(order_no, amount, status); -- EXPLAIN 显示 Using index适用场景高频查询字段固定、对响应极度敏感的核心接口。3.3 索引失效的12种场景附验证SQL失效场景示例解决1. 索引列使用函数WHERE DATE(create_time) 2026-06-04改为范围查询create_time BETWEEN 2026-06-04 00:00:00 AND 2026-06-04 23:59:592. 隐式类型转换WHERE phone 13800138000phone是varchar统一类型WHERE phone 138001380003. 模糊查询左匹配WHERE name LIKE %张三业务允许改为右匹配张三%或使用全文索引4. OR条件WHERE status PAID OR amount 1000使用UNION合并两个索引结果或用IN5. 不等于!, WHERE status ! CANCELLED改为IN列举可能值6. IS NULL / IS NOT NULLWHERE deleted_at IS NULL避免为可空列建索引或用默认值代替NULL7. 联合索引跳过最左列索引(a,b,c)WHERE b1重建索引顺序或额外建索引(b)8. 范围查询后列失效索引(a,b)WHERE a10 AND b1范围列放最后9. 使用表达式或计算WHERE amount 100 2000移项到常量侧amount 190010. 使用NOT IN / NOT EXISTSWHERE id NOT IN (1,2,3)改为EXISTS或LEFT JOIN ... IS NULL11. 字符集不一致表utf8mb4连接字符集latin1统一字符集12. 排序字段方向不一致索引(a ASC,b DESC)ORDER BY a ASC,b ASC索引排序方向需与ORDER BY完全一致3.4 索引维护不可忽视的“熵增”索引不是一建永逸的。随着数据更新索引会产生碎片导致扫描效率下降。sql-- 查看索引碎片程度information_schema SELECT TABLE_NAME, INDEX_NAME, STAT_NAME, STAT_VALUE FROM mysql.innodb_index_stats WHERE TABLE_NAME orders AND STAT_NAME n_diff_pfx%; -- 重建索引Online DDLMySQL 5.6不锁表 ALTER TABLE orders ENGINE InnoDB; -- 或使用OPTIMIZE TABLE会锁表生产低峰期执行 OPTIMIZE TABLE orders;建议每周或每月低峰期对高频更新的大表重建一次索引。四、SQL重写实战十条军规4.1 避免 SELECT *只取需要的列sql-- ❌ 返回了所有字段浪费网络和内存 SELECT * FROM orders WHERE order_id 12345; -- ✅ 明确列并且可以走覆盖索引 SELECT order_id, amount, status FROM orders WHERE order_id 12345;4.2 分页优化深度分页的“大坑”sql-- ❌ 偏移量100万行MySQL需要扫描并丢弃前100万行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- ✅ 延迟关联或记录上次ID SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20; -- ✅ 或者使用子查询先获取ID SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) AS tmp USING(id);4.3 JOIN优化小表驱动大表确保连接字段有索引INNER JOINMySQL自动选择驱动表但需保证被驱动表连接字段有索引LEFT JOIN驱动表固定为左表左表尽量小案例sql-- ❌ 连接字段无索引导致全表扫描 SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id; -- users.id有主键但o.user_id无索引orders全扫 -- ✅ 为orders.user_id建索引 CREATE INDEX idx_user_id ON orders(user_id);4.4 使用 EXISTS 替代 IN当子查询数据量大时sql-- 当子查询结果集很大时IN会先构建临时表 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level 1); -- EXISTS 更适合外部查询驱动利用索引 SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id o.user_id AND u.vip_level 1);4.5 批量操作逐条循环改批量java// ❌ 逐条更新1000次网络往返 for (Long id : ids) { jdbcTemplate.update(UPDATE orders SET status DONE WHERE id ?, id); } // ✅ 单条SQL批量更新1次网络往返 jdbcTemplate.update(UPDATE orders SET status DONE WHERE id IN (?), ids);4.6 GROUP BY 索引优化如果GROUP BY的列没有索引会生成临时表并可能产生Using filesort。sql-- 为group by的列加上索引同时满足where条件 CREATE INDEX idx_status_user ON orders(status, user_id); SELECT status, COUNT(*) FROM orders GROUP BY status;4.7 避免在WHERE子句中对字段进行NULL判断sql-- ❌ SELECT * FROM orders WHERE update_time IS NULL; -- ✅ 改为默认值如1970-01-01并建索引4.8 UNION 使用原则UNION ALL比UNION快不去重对每个子查询的WHERE条件充分利用索引4.9 使用约束代替查询后过滤尽量在SQL层面完成数据过滤而不是在应用层。4.10 定期审查慢查询周报每周分析慢查询日志趋势发现潜在的性能退化。五、实战案例从30秒到0.03秒的三次蜕变5.1 案例1统计报表中的“全表扫描临时表”原始SQL统计某天不同状态的订单总额sqlSELECT status, SUM(amount) FROM orders WHERE DATE(create_time) 2026-05-01 GROUP BY status;执行计划typeALLExtraUsing where; Using temporary; Using filesort。扫描行数834万。响应时间32秒。优化过程消除函数create_time BETWEEN 2026-05-01 00:00:00 AND 2026-05-01 23:59:59创建联合索引(create_time, status, amount)覆盖索引改写SQL利用索引有序性优化后sqlSELECT status, SUM(amount) FROM orders WHERE create_time 2026-05-01 00:00:00 AND create_time 2026-05-02 00:00:00 GROUP BY status;结果typerangeExtraUsing index扫描行数8230行响应时间0.09秒。5.2 案例2分页查询中的“延迟关联”原始分页查询sqlSELECT * FROM orders WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20;即使有(status, create_time)索引MySQL仍需要扫描前100020行数据offset 100000的性能灾难。优化后sqlSELECT * FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;原理子查询SELECT id可以利用覆盖索引快速定位20个ID然后主查询用主键快速回表。响应时间从5.2秒降到0.08秒。5.3 案例3JOIN顺序导致的“全表扫描噩梦”原始SQLsqlSELECT o.order_no, u.name FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.registration_time 2026-01-01 AND o.amount 1000;问题LEFT JOINWHERE o.amount 1000将LEFT JOIN隐式转为INNER JOIN且驱动表为users可能很大orders表的user_id无索引。优化先过滤小表再JOIN并为关联字段加索引。sqlSELECT o.order_no, u.name FROM (SELECT id, name FROM users WHERE registration_time 2026-01-01) u INNER JOIN orders o ON u.id o.user_id WHERE o.amount 1000;创建索引ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);效果从18秒降到0.2秒。六、慢查询治理体系从“救火”到“免疫”6.1 开发规范上线前必须经过EXPLAIN审核禁止typeALL的SQL上线禁止使用SELECT *禁止对索引列使用函数和隐式转换联合索引必须遵循最左前缀6.2 自动化工具pt-query-digest 监控告警将慢查询日志集成到Prometheus或ELK设置阈值告警如慢查询频率 10次/分钟触发报警。6.3 周期性复盘每月一次SQL性能Review挑选TOP 10慢查询归类分析原因并优化。建立SQL性能基线和退化检测。七、总结查询优化的核心心法慢查询日志是起点没有度量就没有优化EXPLAIN是手术刀学会读懂type、rows、Extra一眼定位问题索引设计是艺术遵循“三最法则”覆盖索引是最强武器SQL重写是基本功避免函数、隐式转换、深度分页、不合理的JOIN治理体系是保障规范自动化周期复盘让系统具备免疫能力。最后分享一个真实感受很多团队花了大量时间在架构设计、中间件选型上却不愿意花半小时分析一条慢查询。然而实际生产线上一条索引漏建导致的数据库CPU飙高足以摧毁所有上层的高可用设计。从今天起把你的EXPLAIN能力练到炉火纯青那才是真正保护系统尊严的核武器。文末互动你在工作中遇到过最离谱的慢查询是什么样的欢迎在评论区分享你的踩坑经历
MySQL 查询性能核武器
发布时间：2026/6/4 11:57:08
一条SQL拖垮整个数据库这事每天都在发生。生产环境一次“慢查询屠杀”凌晨3点核心交易表因一个漏写索引的WHERE order_date CURDATE()触发了全表扫描 文件排序瞬间占满磁盘IO数据库连接数飙到上限订单服务全面瘫痪20分钟。而事后定位时发现慢查询日志里那条SQL执行时间从正常的50ms飙升到了48秒——差了一个960倍的数量级。慢查询不是“偶尔慢一下”而是系统性的性能定时炸弹。本文将带你从慢查询的“第一现场”出发逐步深入EXPLAIN执行计划的内核通过索引优化的四个层级、SQL重写的十种实战技巧、以及三个真实生产案例彻底掌握MySQL查询优化的屠龙之术。一、慢查询从“开启日志”到“精准破案”1.1 生产环境慢查询配置——别再默认关闭了很多MySQL实例默认slow_query_log OFF相当于在裸奔。正确的生产配置如下sql-- 查看当前状态 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 在线开启重启失效建议写入my.cnf SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.5; -- 捕捉超过500ms的SQL高并发业务建议0.1~0.5 SET GLOBAL log_queries_not_using_indexes ON; -- 没有索引的SQL也记录 SET GLOBAL log_throttle_queries_not_using_indexes 10; -- 每分钟最多记录10条无索引SQL防止日志爆炸my.cnf 稳定配置ini[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow-query.log long_query_time 0.3 log_queries_not_using_indexes 1 log_slow_admin_statements 1 # 记录慢的管理命令OPTIMIZE, ANALYZE TABLE min_examined_row_limit 1000 # 扫描行数超过1000才记录1.2 慢查询分析神器pt-query-digestMySQL自带的mysqldumpslow功能太弱生产环境首选Percona Toolkit的pt-query-digest。bash# 分析今天的慢查询日志按查询时间降序输出前10 pt-query-digest /var/log/mysql/slow-query.log --limit 10 # 分析并输出到报告文件 pt-query-digest /var/log/mysql/slow-query.log --since 2026-06-04 00:00:00 --until 2026-06-04 23:59:59 slow_report_0604.txt输出报告中最关键的三个指标Response time总响应时间占比 → 哪个SQL是最大元凶Rows examine扫描行数 vs 返回行数扫描/返回比理想值接近1:1Full scan全表扫描标记1.3 一条价值千万元的慢查询日志实例sql# Time: 2026-06-04T03:15:23.123456Z # UserHost: order_app[order_app] [10.0.1.100] # Query_time: 45.832901 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 5847296 SET timestamp1749071723; SELECT o.order_id, o.amount, u.name, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status PAID AND DATE(o.create_time) 2026-06-03 AND u.vip_level 3 ORDER BY o.amount DESC LIMIT 10;问题一眼可见Rows_examined: 5847296→ 扫描了584万行只返回10行效率极差DATE(o.create_time) ...→ 对索引列使用函数索引失效LEFT JOINWHERE u.vip_level 3→ 实际将LEFT JOIN变成了INNER JOIN可能误导优化器大表orders没有合适索引status和create_time分开索引无法合并优化后该SQL从45秒降到0.08秒QPS从35提升到2100。二、EXPLAIN 执行计划——一眼看穿SQL的“五脏六腑”EXPLAIN是MySQL查询优化的手术刀每个字段都藏着关键信息。我们拆解最核心的字段。2.1 type连接类型性能从好到差type含义是否可用system系统表只有一行极少见const主键或唯一索引等值匹配完美eq_ref连接时使用主键或唯一索引优秀ref非唯一索引等值匹配良好range索引范围扫描BETWEEN, , , IN可接受index遍历整个索引树比全表好一点较差ALL全表扫描绝对禁止在生产出现原则线上OLTP系统大部分查询的type至少应为range核心查询必须是ref或const。2.2 possible_keys vs keypossible_keys优化器认为可能用到的索引key实际选择的索引常见陷阱possible_keys有值但key为NULL → 优化器认为走索引不如全表扫描。原因可能是索引区分度太低如性别字段或统计信息过期。sql-- 更新统计信息让优化器重新评估 ANALYZE TABLE orders;2.3 rows优化器估算需要扫描的行数rows不是精确值但量级非常重要。如果估算值远大于实际返回行数说明过滤条件严重依赖索引。案例一个rows500000但LIMIT 10的查询很可能在filesort或临时表中处理了50万行后才丢弃。2.4 Extra魔鬼藏在细节里Extra 信息含义处理方式Using where使用WHERE条件过滤正常尽量让过滤发生在索引层Using index覆盖索引不回表最佳状态Using index condition索引下推ICPMySQL 5.6 好特性减少回表Using filesort需要额外排序不能利用索引顺序必须优化为ORDER BY字段建索引Using temporary使用临时表GROUP BY或DISTINCT无索引高危必须加索引Using join buffer连接无索引使用缓存块需要为连接字段加索引一条必须看懂的EXPLAIN输出text------------------------------------------------------------------------------------------------------------------- | id | select_type | table | type | key | rows | Extra | ------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | ALL | NULL | 2345678 | Using where; Using filesort | -------------------------------------------------------------------------------------------------------------------诊断typeALL全表扫描234万行Using filesort→ 死定了必须重建索引。三、索引优化从“随便建”到“艺术级设计”3.1 联合索引的“三最法则”最左前缀、最高区分度、最少字段。案例订单表需要按status、create_time、user_id查询。sql-- ❌ 错误设计区分度低的字段放前面 CREATE INDEX idx_status_time_user ON orders(status, create_time, user_id); -- status只有PAID,UNPAID,CANCELLED等少数值索引树很快遍历大量相同值 -- ✅ 正确设计区分度高的字段优先user_id唯一性高其次范围查询字段放最后 CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time); -- 但如果查询条件是WHERE status PAID则索引无法使用最左前缀需要重新评估更精确的设计方法分析查询模式将等值查询字段放在前面范围查询字段、、BETWEEN放在最后。3.2 覆盖索引零回表的极致性能当索引中包含查询所需的所有列时InnoDB不需要回表直接从索引树返回数据性能提升巨大。sql-- 查询需求根据订单号获取订单金额和状态 SELECT order_no, amount, status FROM orders WHERE order_no ORD123456; -- 创建覆盖索引 CREATE INDEX idx_order_no_cover ON orders(order_no, amount, status); -- EXPLAIN 显示 Using index适用场景高频查询字段固定、对响应极度敏感的核心接口。3.3 索引失效的12种场景附验证SQL失效场景示例解决1. 索引列使用函数WHERE DATE(create_time) 2026-06-04改为范围查询create_time BETWEEN 2026-06-04 00:00:00 AND 2026-06-04 23:59:592. 隐式类型转换WHERE phone 13800138000phone是varchar统一类型WHERE phone 138001380003. 模糊查询左匹配WHERE name LIKE %张三业务允许改为右匹配张三%或使用全文索引4. OR条件WHERE status PAID OR amount 1000使用UNION合并两个索引结果或用IN5. 不等于!, WHERE status ! CANCELLED改为IN列举可能值6. IS NULL / IS NOT NULLWHERE deleted_at IS NULL避免为可空列建索引或用默认值代替NULL7. 联合索引跳过最左列索引(a,b,c)WHERE b1重建索引顺序或额外建索引(b)8. 范围查询后列失效索引(a,b)WHERE a10 AND b1范围列放最后9. 使用表达式或计算WHERE amount 100 2000移项到常量侧amount 190010. 使用NOT IN / NOT EXISTSWHERE id NOT IN (1,2,3)改为EXISTS或LEFT JOIN ... IS NULL11. 字符集不一致表utf8mb4连接字符集latin1统一字符集12. 排序字段方向不一致索引(a ASC,b DESC)ORDER BY a ASC,b ASC索引排序方向需与ORDER BY完全一致3.4 索引维护不可忽视的“熵增”索引不是一建永逸的。随着数据更新索引会产生碎片导致扫描效率下降。sql-- 查看索引碎片程度information_schema SELECT TABLE_NAME, INDEX_NAME, STAT_NAME, STAT_VALUE FROM mysql.innodb_index_stats WHERE TABLE_NAME orders AND STAT_NAME n_diff_pfx%; -- 重建索引Online DDLMySQL 5.6不锁表 ALTER TABLE orders ENGINE InnoDB; -- 或使用OPTIMIZE TABLE会锁表生产低峰期执行 OPTIMIZE TABLE orders;建议每周或每月低峰期对高频更新的大表重建一次索引。四、SQL重写实战十条军规4.1 避免 SELECT *只取需要的列sql-- ❌ 返回了所有字段浪费网络和内存 SELECT * FROM orders WHERE order_id 12345; -- ✅ 明确列并且可以走覆盖索引 SELECT order_id, amount, status FROM orders WHERE order_id 12345;4.2 分页优化深度分页的“大坑”sql-- ❌ 偏移量100万行MySQL需要扫描并丢弃前100万行 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- ✅ 延迟关联或记录上次ID SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20; -- ✅ 或者使用子查询先获取ID SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) AS tmp USING(id);4.3 JOIN优化小表驱动大表确保连接字段有索引INNER JOINMySQL自动选择驱动表但需保证被驱动表连接字段有索引LEFT JOIN驱动表固定为左表左表尽量小案例sql-- ❌ 连接字段无索引导致全表扫描 SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id; -- users.id有主键但o.user_id无索引orders全扫 -- ✅ 为orders.user_id建索引 CREATE INDEX idx_user_id ON orders(user_id);4.4 使用 EXISTS 替代 IN当子查询数据量大时sql-- 当子查询结果集很大时IN会先构建临时表 SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level 1); -- EXISTS 更适合外部查询驱动利用索引 SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id o.user_id AND u.vip_level 1);4.5 批量操作逐条循环改批量java// ❌ 逐条更新1000次网络往返 for (Long id : ids) { jdbcTemplate.update(UPDATE orders SET status DONE WHERE id ?, id); } // ✅ 单条SQL批量更新1次网络往返 jdbcTemplate.update(UPDATE orders SET status DONE WHERE id IN (?), ids);4.6 GROUP BY 索引优化如果GROUP BY的列没有索引会生成临时表并可能产生Using filesort。sql-- 为group by的列加上索引同时满足where条件 CREATE INDEX idx_status_user ON orders(status, user_id); SELECT status, COUNT(*) FROM orders GROUP BY status;4.7 避免在WHERE子句中对字段进行NULL判断sql-- ❌ SELECT * FROM orders WHERE update_time IS NULL; -- ✅ 改为默认值如1970-01-01并建索引4.8 UNION 使用原则UNION ALL比UNION快不去重对每个子查询的WHERE条件充分利用索引4.9 使用约束代替查询后过滤尽量在SQL层面完成数据过滤而不是在应用层。4.10 定期审查慢查询周报每周分析慢查询日志趋势发现潜在的性能退化。五、实战案例从30秒到0.03秒的三次蜕变5.1 案例1统计报表中的“全表扫描临时表”原始SQL统计某天不同状态的订单总额sqlSELECT status, SUM(amount) FROM orders WHERE DATE(create_time) 2026-05-01 GROUP BY status;执行计划typeALLExtraUsing where; Using temporary; Using filesort。扫描行数834万。响应时间32秒。优化过程消除函数create_time BETWEEN 2026-05-01 00:00:00 AND 2026-05-01 23:59:59创建联合索引(create_time, status, amount)覆盖索引改写SQL利用索引有序性优化后sqlSELECT status, SUM(amount) FROM orders WHERE create_time 2026-05-01 00:00:00 AND create_time 2026-05-02 00:00:00 GROUP BY status;结果typerangeExtraUsing index扫描行数8230行响应时间0.09秒。5.2 案例2分页查询中的“延迟关联”原始分页查询sqlSELECT * FROM orders WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20;即使有(status, create_time)索引MySQL仍需要扫描前100020行数据offset 100000的性能灾难。优化后sqlSELECT * FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;原理子查询SELECT id可以利用覆盖索引快速定位20个ID然后主查询用主键快速回表。响应时间从5.2秒降到0.08秒。5.3 案例3JOIN顺序导致的“全表扫描噩梦”原始SQLsqlSELECT o.order_no, u.name FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.registration_time 2026-01-01 AND o.amount 1000;问题LEFT JOINWHERE o.amount 1000将LEFT JOIN隐式转为INNER JOIN且驱动表为users可能很大orders表的user_id无索引。优化先过滤小表再JOIN并为关联字段加索引。sqlSELECT o.order_no, u.name FROM (SELECT id, name FROM users WHERE registration_time 2026-01-01) u INNER JOIN orders o ON u.id o.user_id WHERE o.amount 1000;创建索引ALTER TABLE orders ADD INDEX idx_user_amount (user_id, amount);效果从18秒降到0.2秒。六、慢查询治理体系从“救火”到“免疫”6.1 开发规范上线前必须经过EXPLAIN审核禁止typeALL的SQL上线禁止使用SELECT *禁止对索引列使用函数和隐式转换联合索引必须遵循最左前缀6.2 自动化工具pt-query-digest 监控告警将慢查询日志集成到Prometheus或ELK设置阈值告警如慢查询频率 10次/分钟触发报警。6.3 周期性复盘每月一次SQL性能Review挑选TOP 10慢查询归类分析原因并优化。建立SQL性能基线和退化检测。七、总结查询优化的核心心法慢查询日志是起点没有度量就没有优化EXPLAIN是手术刀学会读懂type、rows、Extra一眼定位问题索引设计是艺术遵循“三最法则”覆盖索引是最强武器SQL重写是基本功避免函数、隐式转换、深度分页、不合理的JOIN治理体系是保障规范自动化周期复盘让系统具备免疫能力。最后分享一个真实感受很多团队花了大量时间在架构设计、中间件选型上却不愿意花半小时分析一条慢查询。然而实际生产线上一条索引漏建导致的数据库CPU飙高足以摧毁所有上层的高可用设计。从今天起把你的EXPLAIN能力练到炉火纯青那才是真正保护系统尊严的核武器。文末互动你在工作中遇到过最离谱的慢查询是什么样的欢迎在评论区分享你的踩坑经历
![3步搞定PS手柄Windows连接：DS4Windows让游戏体验飞升！[特殊字符]](http://pic.xiahunao.cn/yaotu/3步搞定PS手柄Windows连接：DS4Windows让游戏体验飞升！[特殊字符])
