SQL 性能调优：EXPLAIN 详解与慢查询优化案例

各位架构师、数据库的“老中医”大家好今天我们来聊聊数据库的“体检报告”——EXPLAIN。当你的接口响应慢得像蜗牛CPU 飙升得像火箭时千万别急着重启数据库也别盲目地加索引。这时候你需要的是给 SQL 拍一张“X 光片”看看它到底是在“跑步”高效索引扫描还是在“散步”全表扫描。今天我们用硬核的方式“彻底”拆解 SQL 性能调优。第一步捕捉“嫌疑人”——慢查询日志在优化之前你得先知道是谁在拖后腿。MySQL 有个自带的“监控摄像头”叫慢查询日志。开启方式临时生效-- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置阈值超过 2 秒的 SQL 才会被记录生产环境建议设为 1 或 0.5 SET GLOBAL long_query_time 2;分析工具日志文件通常位于/var/lib/mysql/slow-query.log。别用记事本一行行看太累用 MySQL 自带的工具mysqldumpslow# 按查询时间排序取前 10 条“最慢”的 SQL mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log第二步拍“X 光片”——EXPLAIN 详解拿到慢 SQL 后在它前面加上EXPLAIN就能得到它的执行计划。EXPLAIN SELECT * FROM users WHERE name Alice;结果里字段很多别慌。作为架构师你只需要重点关注三个“命门”type、key和Extra。1. type扫描类型性能的生命线这是最重要的指标它决定了 MySQL 是怎么找数据的。性能从优到差等级森严const / eq_ref这是“特快专递”。通过主键或唯一索引查询直接定位只读一行。ref这是“普通快递”。通过普通索引查询找到匹配的行。range这是“区间扫描”。比如WHERE id 10只扫描一部分索引。index这是“全索引扫描”。虽然也是扫描但只扫索引树不扫数据页比全表快一点。ALL这是“地毯式搜索”。全表扫描看到ALL就像看到医生在体检报告上写了个“危”必须优化洞察一般要求 SQL 至少达到range级别最好是ref。如果是ALL说明你的索引在“罢工”。2. key实际使用的索引keyMySQL 实际用了哪个索引。如果是NULL说明没走索引。possible_keysMySQL 觉得可以用哪些索引。注意如果possible_keys有一堆索引但key是NULL说明 MySQL 的优化器“犯傻”了或者索引失效了。3. Extra额外信息隐藏的性能杀手这里的信息量最大也是“坑”最多的地方Using index完美覆盖索引。MySQL 直接在索引里就找到了所有需要的数据连表都不用回不用查数据页。Using where普通。需要在存储引擎层根据条件过滤。Using filesort警告文件排序。说明 MySQL 无法利用索引来完成排序必须把数据取出来放到内存或磁盘上单独排序。这是性能杀手Using temporary严重警告使用了临时表。常见于GROUP BY或ORDER BY字段不一致时。先把数据塞进临时表处理完再返回效率极低。第三步对症下药——常见优化套路1. 索引为什么会“迷路”失效明明建了索引为什么type还是ALL通常是因为你触犯了“索引禁忌”对索引列“动刀”-- 错误在索引列上做计算索引直接报废 SELECT * FROM orders WHERE YEAR(create_time) 2026; -- 正确把计算移到右边 SELECT * FROM orders WHERE create_time 2026-01-01;模糊查询的前导通配符-- 错误LIKE %abc因为索引是从左到右的前面模糊相当于大海捞针 -- 正确LIKE abc%走范围扫描类型隐式转换-- 错误phone 字段是字符串你却传了数字 SELECT * FROM users WHERE phone 13800000000; -- 正确加引号 SELECT * FROM users WHERE phone 13800000000;2. 分页优化LIMIT 1000000, 10 的痛当用户翻到第 100 万页时你的 SQL 可能会慢死SELECT * FROM products LIMIT 1000000, 10;原理MySQL 会扫描前 1000010 条记录然后丢弃前 1000000 条只返回最后 10 条。这简直是浪费生命优化方案延迟关联利用覆盖索引先只查主键再回表。SELECT p.* FROM products p JOIN (SELECT id FROM products LIMIT 1000000, 10) AS tmp ON p.id tmp.id;解释子查询tmp利用了覆盖索引只查 id速度极快。拿到 10 个 id 后再跟原表关联瞬间完成。索引绝对不是越多越好索引是一把双刃剑它在加速查询读操作的同时会显著拖慢数据的写入和更新写操作并消耗宝贵的存储和内存资源。索引是用空间和写入性能换取读取性能的工具。优秀的程序员不会盲目堆砌索引而是像狙击手一样精准地只为最关键的查询路径提供支援。写入性能的“多米诺骨牌”效应这是索引过多最直接的代价。在 InnoDB 引擎中数据的增删改INSERT/UPDATE/DELETE不仅仅是修改数据页还必须同步维护所有相关的二级索引。底层原理当你插入一行数据时数据库不仅要写入聚簇索引主键索引还要找到该行数据在所有二级索引树中的位置并插入。如果一张表有 10 个索引一次INSERT操作实际上变成了11 次磁盘 I/O 操作1次数据页 10次索引页。更糟糕的是这会导致频繁的页分裂Page Split。为了保持 B 树的有序性插入新数据可能导致索引页满了需要分裂出新页这会极大地消耗 CPU 和 I/O 资源。内存Buffer Pool的“挤兑”数据库的性能很大程度上依赖于内存缓存如 MySQL 的 Buffer Pool。内存是有限的资源。底层原理索引也是要加载到内存中的。如果你建立了大量低频使用的索引这些索引页会挤占 Buffer Pool 的空间。结果就是真正热点的数据页Data Page被置换出内存导致核心业务查询时发生大量的磁盘 I/O缺页中断反而降低了整体系统的吞吐量。查询优化器的“选择困难症”你可能认为索引多了优化器Optimizer的选择就多了查询会更快。其实恰恰相反。底层原理当一张表上有几十个索引时MySQL 的查询优化器在生成执行计划时需要计算和评估每一条路径的成本。这不仅增加了 SQL解析阶段的 CPU 开销还可能导致优化器“眼花”错误地选择了一个次优索引比如选了区分度很低的索引导致查询性能不升反降。冗余与维护成本很多索引其实是重复的或者根本用不上。最左前缀原则的冗余如果你已经建立了一个联合索引(a, b)那么单独给a再建一个索引就是完全多余的。因为(a, b)索引的最左前缀已经覆盖了a的查询需求。维护噩梦随着数据量的增长索引会产生碎片Fragmentation。索引越多碎片整理OPTIMIZE TABLE或Rebuild Index的时间就越长线上运维的风险也越大。什么时候索引会“失效”即使你建了很多索引如果写法不对它们也会全部失效变成摆设。以下情况索引会“迷路”对索引列做运算WHERE YEAR(create_time) 2026索引失效全表扫描。模糊查询前导通配符LIKE %abc索引失效。类型隐式转换字符串字段没加引号phone 1380000索引失效。“索引设计法则”为了平衡读写性能建议遵循以下原则按需创建少而精只给查询频率高、区分度大基数大的字段建索引。不要给“性别”、“状态”这种只有几个值的字段单独建索引。利用联合索引覆盖索引尽量使用联合索引如(a, b, c)来覆盖多个查询场景减少回表操作。定期清理利用sys.schema_unused_indexes(MySQL 5.7) 或 Performance Schema 定期排查从未使用的索引果断删除。写入优先场景对于日志表、流水表这种写多读少的表尽量少建索引甚至只保留主键索引以保证写入吞吐量。总结SQL 调优不是靠猜是靠数据。EXPLAIN是你的听诊器通过type听心跳通过Extra找病灶。索引是你的高速公路别让ALL把你拉回泥泞土路。覆盖索引是你的VIP通道能不走回头路回表就不走。最后送上金句“调优不是靠猜是靠数据。EXPLAIN 是你的听诊器通过分析执行计划让每一条 SQL 都走在最短的路径上。”