MySQL：EXPLAIN 是 SQL 性能调优的利器

🔍 1、简述

当你面对一个慢查询，不知从何下手优化时，EXPLAIN 是你最值得信赖的工具。它能告诉你：MySQL 是如何执行 SQL 语句的？是否使用了索引？是否全表扫描？ 本文将全面介绍 EXPLAIN 的用法、关键字段解读及实战样例。

🧠 2、什么是 EXPLAIN？

EXPLAIN 是 MySQL 提供的用于分析 SELECT、DELETE、INSERT、REPLACE、UPDATE 查询执行计划的命令。

通过执行：

EXPLAIN SELECT * FROM users WHERE id = 1;

MySQL 会返回一个表格，展示 SQL 语句的执行计划，有助于定位性能瓶颈。

2.1 EXPLAIN 输出字段详解

2.2 重点字段详解与优化建议

type —— 最重要的性能指标

从好到差如下：

system > const > eq_ref > ref > range > index > ALL

🔹 ALL：全表扫描，性能最差，需优化
🔹 index：扫描索引全表
🔹 range：索引范围扫描（如 BETWEEN, <, IN）
🔹 ref：使用非唯一索引等值匹配
🔹 eq_ref：使用唯一索引等值匹配（如主键）
🔹 const/system：单行常量匹配，最快

🧠 优化目标：避免 ALL，争取 eq_ref 或 range

Extra 常见值说明

📦 3、实践样例分析

🌱 创建样本数据

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  age INT,
  email VARCHAR(100),
  INDEX idx_age (age),
  INDEX idx_email (email)
);

INSERT INTO users (name, age, email)
SELECT 
  CONCAT('user', id), 
  FLOOR(RAND() * 100), 
  CONCAT('user', id, '@mail.com')
FROM (SELECT @id := @id + 1 AS id FROM information_schema.columns LIMIT 10000) AS t;

🧪 示例 1：全表扫描（低效）

EXPLAIN SELECT * FROM users WHERE name = 'user9999';

结果示例：

说明：

🔹 未命中任何索引
🔹 type=ALL，表示全表扫描
🔹 建议：对 name 添加索引

🧪 示例 2：索引等值匹配（推荐）

EXPLAIN SELECT * FROM users WHERE age = 30;

结果示例：

说明：

🔹 使用了 age 索引
🔹 扫描范围较小，性能良好

🧪 示例 3：索引范围扫描

EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;

结果示例：

说明：

🔹 使用了 range 类型扫描，属于较高性能操作

🧪 示例 4：覆盖索引优化

EXPLAIN SELECT email FROM users WHERE age = 25;

结果示例：

说明：

🔹 查询字段在索引中 → 不用回表
🔹 Using index 表示使用了 覆盖索引

🧰 4、高级分析技巧

4.1 使用 EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE SELECT e.* FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 6000 AND d.dept_name = 'Engineering';

输出示例：

-> Nested loop inner join  (cost=2.71 rows=1) (actual time=0.123..0.125 rows=1 loops=1)
    -> Index lookup on d using PRIMARY (dept_id=e.dept_id)  (cost=0.35 rows=1) (actual time=0.056..0.057 rows=1 loops=1)
    -> Filter: (e.salary > 6000)  (cost=2.36 rows=1) (actual time=0.065..0.066 rows=0 loops=1)
        -> Index lookup on e using idx_dept_id (dept_id=d.dept_id)  (cost=2.36 rows=5) (actual time=0.063..0.064 rows=5 loops=1)

4.2 优化器开关调试

-- 查看当前优化器开关
SELECT @@optimizer_switch;

-- 临时禁用某些优化
SET optimizer_switch='materialization=off';
EXPLAIN SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department='IT');

4.3 索引提示使用

-- 强制使用特定索引
EXPLAIN SELECT * FROM employees FORCE INDEX (idx_dept) WHERE department = 'IT' OR salary > 5000;

-- 忽略索引
EXPLAIN SELECT * FROM employees IGNORE INDEX (idx_salary) WHERE salary > 5000;

🛡️5、常见性能问题及解决方案

问题1：全表扫描（type=ALL）

-- 添加合适索引
ALTER TABLE employees ADD INDEX idx_age (age);

-- 重写查询
EXPLAIN SELECT * FROM employees WHERE age = 25;  -- 替换 age > 20

问题2：文件排序（Using filesort）

-- 创建排序索引
ALTER TABLE employees ADD INDEX idx_dept_hire (department, hire_date);

-- 使用索引排序
EXPLAIN SELECT * FROM employees 
WHERE department = 'IT' 
ORDER BY hire_date DESC;

问题3：临时表（Using temporary）

-- 优化GROUP BY
EXPLAIN SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;  -- 确保GROUP BY使用索引

-- 添加适当索引
ALTER TABLE employees ADD INDEX idx_dept_name (department, name);

常见优化建议

🔚 6、总结

✅ 工具推荐

🔹 Navicat / DBeaver：图形化 EXPLAIN 查看
🔹 MySQL Workbench：性能分析、索引建议
🔹 pt-query-digest：分析慢查询日志

如你希望，我可以帮你生成：

🔹 Spring Boot + MySQL 实战项目，集成 Druid 监控慢 SQL
🔹 自动化 SQL 分析脚本
🔹 基于 Explain 的优化报告模板

欢迎继续提问 🔍