查询优化与执行计划深度解析

面试开场白

面试官：你做过 SQL 优化吗？

你：做过，我会用 EXPLAIN 分析执行计划，然后根据结果优化索引…

面试官：那 EXPLAIN 的 type 字段有哪些值？Extra 字段的 Using filesort 是什么意思？

这个追问把很多人问住了。能说清「type 从好到差的排序、Extra 各项含义、如何针对性优化」的候选人，才能真正脱颖而出。

---

链式追问一：慢查询分析

Q1：如何定位慢查询？必考

慢查询日志配置：

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒算慢查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录所有未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

慢查询分析工具：

# 1. mysqldumpslow（MySQL 自带）
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 10: 显示前 10 条

# 2. pt-query-digest（Percona Toolkit，更强大）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

pt-query-digest 输出示例：

# 1. 查询概况
# 总查询数：1000
# 总时间：5000s
# 平均时间：5s

# 2. Top 10 慢查询
# Rank  Query ID           Response time  Calls  R/Call  Query
# ====  ================== ============== =====  ======  =====
# 1     0x1234567890ABCDEF 2000s (40%)    100    20s     SELECT * FROM users WHERE...
# 2     0xABCDEF1234567890 1500s (30%)    50     30s     SELECT * FROM orders WHERE...

面试技巧

回答时要提到”生产环境 long_query_time 一般设置 0.1~0.5 秒”，因为 1 秒太长了，很多慢查询会被漏掉。还要提到”pt-query-digest 比 mysqldumpslow 更强大”，体现工具选型能力。

---

链式追问二：EXPLAIN 执行计划

Q2：EXPLAIN 的 type 字段有哪些值？性能如何排序？必考

type 字段表示 MySQL 如何查找数据，从好到差排序：

type	含义	性能	示例
system	表只有一行（系统表）	⭐⭐⭐⭐⭐	SELECT * FROM mysql.proxies_priv
const	主键/唯一索引常量查询	⭐⭐⭐⭐⭐	WHERE id = 1
eq_ref	Join 时使用主键/唯一索引	⭐⭐⭐⭐⭐	JOIN ON a.id = b.id
ref	非唯一索引等值查询	⭐⭐⭐⭐	WHERE name = 'Alice'
range	索引范围扫描	⭐⭐⭐	WHERE age > 20
index	全索引扫描	⭐⭐	SELECT id FROM users
ALL	全表扫描	⭐	WHERE age + 1 = 25

性能排序：

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

优化目标：
- 至少达到 range 级别
- 最好达到 ref 级别
- 避免 ALL（全表扫描）

示例分析：

-- ✅ const（主键查询）
EXPLAIN SELECT * FROM users WHERE id = 1;
+------+-------------+-------+-------+---------------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys | key     | ref   | rows | Extra |
+------+-------------+-------+-------+---------------+---------+-------+------+-------+
| 1    | SIMPLE      | users | const | PRIMARY       | PRIMARY | const | 1    |       |
+------+-------------+-------+-------+---------------+---------+-------+------+-------+

-- ✅ ref（非唯一索引查询）
EXPLAIN SELECT * FROM users WHERE name = 'Alice';
+------+-------------+-------+------+---------------+----------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key      | ref   | rows | Extra |
+------+-------------+-------+------+---------------+----------+-------+------+-------+
| 1    | SIMPLE      | users | ref  | idx_name      | idx_name | const | 10   |       |
+------+-------------+-------+------+---------------+----------+-------+------+-------+

-- ❌ ALL（全表扫描）
EXPLAIN SELECT * FROM users WHERE age + 1 = 25;
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table | type | possible_keys | key  | ref     | rows | Extra  |             |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1    | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | 1000 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+

面试陷阱

很多候选人只知道”ALL 是全表扫描，性能差”，但说不清其他 type 的含义。回答时要能说出每个 type 的具体场景，并强调”至少要达到 range 级别”。

---

Q3：EXPLAIN 的 Extra 字段有哪些常见值？高频

Extra 字段表示额外信息，常见值：

Extra 值	含义	性能影响
Using index	索引覆盖，无需回表	✅ 好
Using where	WHERE 条件过滤	⚠️ 一般
Using index condition	索引下推（ICP）	✅ 好
Using filesort	文件排序（内存/磁盘）	❌ 差
Using temporary	使用临时表	❌ 差
Using join buffer	Join 使用了缓冲区	⚠️ 一般

详解：

1. Using index（索引覆盖）

-- 假设在 (name, age) 上有联合索引
EXPLAIN SELECT name, age FROM users WHERE name = 'Alice';

Extra: Using index
-- 说明：查询的字段都在索引中，无需回表

2. Using filesort（文件排序）

-- 假设在 name 上有索引，但 ORDER BY age
EXPLAIN SELECT * FROM users WHERE name = 'Alice' ORDER BY age;

Extra: Using filesort
-- 说明：无法使用索引排序，需要在内存/磁盘排序
-- 优化：建立 (name, age) 联合索引

3. Using temporary（临时表）

-- GROUP BY 字段没有索引
EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;

Extra: Using temporary
-- 说明：需要创建临时表进行分组
-- 优化：为 name 字段建立索引

4. Using index condition（索引下推）

-- MySQL 5.6+ 特性
-- 假设在 (name, age) 上有联合索引
EXPLAIN SELECT * FROM users WHERE name LIKE 'Ali%' AND age = 25;

Extra: Using index condition
-- 说明：age 条件在索引层面就过滤了，减少回表次数

面试技巧

回答时要强调”Using filesort 和 Using temporary 是性能杀手”，并给出优化方案。可以说”通过建立合适的联合索引，可以避免 filesort 和 temporary”。

---

链式追问三：Join 原理与优化

Q4：MySQL 的 Join 是如何执行的？高频

Join 的三种算法：

1. Simple Nested Loop Join（简单嵌套循环）
   for each row in 驱动表:
       for each row in 被驱动表:
           if 匹配: 输出

   性能：O(M × N)，极差

2. Block Nested Loop Join（块嵌套循环，MySQL 默认）
   for each row in 驱动表:
       读取被驱动表的一批数据到 join_buffer
       for each row in join_buffer:
           if 匹配: 输出

   性能：O(M × (N / join_buffer_size))，较好

3. Index Nested Loop Join（索引嵌套循环）
   for each row in 驱动表:
       通过索引查找被驱动表（O(log N)）
       if 匹配: 输出

   性能：O(M × log N)，最好

驱动表选择：

-- 小表驱动大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.sid;

-- 原因：
-- 1. 小表作为驱动表，循环次数少
-- 2. join_buffer 可以缓存更多数据

Join 优化建议：

1. 被驱动表字段建立索引

   -- ✅ 被驱动表 l 的 sid 字段有索引
   SELECT * FROM small_table s
   JOIN large_table l ON s.id = l.sid;
   
   -- 使用 Index Nested Loop Join，性能最好

2. 小表驱动大表

   -- ✅ 小表在前
   SELECT * FROM small_table s
   JOIN large_table l ON s.id = l.sid;
   
   -- ❌ 大表在前
   SELECT * FROM large_table l
   JOIN small_table s ON s.id = l.sid;

3. 增大 join_buffer_size

   -- 默认 256KB，可增大到 1~4MB
   SET join_buffer_size = 4194304;  -- 4MB

面试陷阱

很多候选人只知道”小表驱动大表”，但说不清原因。回答时要提到”join_buffer 的大小有限，小表可以完全放入 join_buffer，减少扫描被驱动表的次数”。

---

Q5：如何优化分页查询？实战

问题场景：

-- 假设有 100 万条数据，查询第 90 万页
SELECT * FROM users ORDER BY id LIMIT 900000, 10;

-- 执行过程：
-- 1. 扫描前 900010 条记录
-- 2. 抛弃前 900000 条
-- 3. 返回后 10 条

-- 性能：极差，需要扫描大量数据

优化方案：

方案1：延迟关联

-- ✅ 先查主键，再关联
SELECT u.*
FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 900000, 10
) t ON u.id = t.id;

-- 原理：
-- 1. 子查询只查主键 id，走索引覆盖，速度快
-- 2. 外层查询通过主键关联，只需 10 次回表

方案2：记录上次最大 ID

-- ✅ 记住上一页的最大 ID
SELECT * FROM users WHERE id > 900000 ORDER BY id LIMIT 10;

-- 原理：
-- 1. 直接定位到 id > 900000 的位置
-- 2. 只需扫描 10 条记录

-- 适用场景：连续分页（如无限滚动）

性能对比：

方案	扫描行数	性能
原始方案	900010 行	❌ 极慢
延迟关联	900010 行（索引覆盖） + 10 行（回表）	✅ 快
记录最大 ID	10 行	⭐⭐⭐ 最快

面试技巧

回答时要提到”延迟关联利用了索引覆盖”，并给出性能对比数据。可以说”从扫描 90 万行降到扫描 10 行，性能提升上万倍”。

---

链式追问四：其他优化技巧

Q6：如何优化 COUNT 查询？加分

COUNT 的几种写法：

-- 1. COUNT(*)
SELECT COUNT(*) FROM users;
-- 统计总行数，不关心 NULL

-- 2. COUNT(1)
SELECT COUNT(1) FROM users;
-- 与 COUNT(*) 性能相同

-- 3. COUNT(id)
SELECT COUNT(id) FROM users;
-- 统计 id 非 NULL 的行数

-- 4. COUNT(name)
SELECT COUNT(name) FROM users;
-- 统计 name 非 NULL 的行数

性能对比：

写法	性能	说明
COUNT(*)	⭐⭐⭐⭐⭐	MySQL 优化过，最快
COUNT(1)	⭐⭐⭐⭐⭐	与 COUNT(*) 相同
COUNT(id)	⭐⭐⭐⭐	需要判断 id 是否为 NULL
COUNT(name)	⭐⭐⭐	需要判断 name 是否为 NULL

优化建议：

1. 使用 COUNT(*)

   -- ✅ 推荐
   SELECT COUNT(*) FROM users;
   
   -- ❌ 不推荐
   SELECT COUNT(1) FROM users;
   SELECT COUNT(id) FROM users;

2. 维护计数表

   -- 高频统计场景
   CREATE TABLE table_counts (
       table_name VARCHAR(50) PRIMARY KEY,
       row_count BIGINT
   );
   
   -- 插入/删除时更新计数
   INSERT INTO table_counts VALUES ('users', 1000);
   UPDATE table_counts SET row_count = row_count + 1 WHERE table_name = 'users';
   
   -- 查询时直接读计数表
   SELECT row_count FROM table_counts WHERE table_name = 'users';

面试陷阱

很多候选人认为 COUNT(1) 比 COUNT() 快，这是错误的。MySQL 对 COUNT() 做了专门优化，性能最好。回答时要纠正这个误区。

---

Q7：如何优化大表 DDL？实战

问题：大表 DDL（ALTER TABLE）会锁表，导致业务不可用。

优化方案：

方案1：pt-online-schema-change（Percona Toolkit）

# 在线修改表结构
pt-online-schema-change \
  --alter "ADD COLUMN age INT" \
  D=database,t=users \
  --execute

# 原理：
# 1. 创建新表（修改后的结构）
# 2. 逐行拷贝数据到新表
# 3. 同时同步增量数据（通过触发器）
# 4. 原子切换表名

方案2：gh-ost（GitHub 开源）

# 无触发器的在线 DDL
gh-ost \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=1000 \
  --chunk-size=1000 \
  --throttle-control-replicas="..." \
  --database=database \
  --table=users \
  --alter="ADD COLUMN age INT" \
  --allow-on-master \
  --execute

# 优势：
# 1. 无触发器，性能更好
# 2. 可暂停、可回滚
# 3. 支持限流

方案3：MySQL 8.0 Instant DDL

-- MySQL 8.0 部分DDL 支持即时完成
ALTER TABLE users ADD COLUMN age INT, ALGORITHM=INSTANT;

-- 支持的 INSTANT DDL：
-- 1. 添加列（非虚拟列，非 NOT NULL）
-- 2. 删除列
-- 3. 重命名列
-- 4. 设置列默认值

面试技巧

回答时要提到”pt-osc 和 gh-ost 的区别”：pt-osc 使用触发器，gh-osc 使用 binlog。可以说”gh-ost 性能更好，但配置更复杂”。

---

总结：查询优化的核心思路

优化三步骤：
1. 定位慢查询（slow log + pt-query-digest）
2. 分析执行计划（EXPLAIN）
3. 针对性优化（索引、SQL 改写、参数调整）

EXPLAIN 关注点：
- type：至少达到 range，避免 ALL
- key：是否用到了索引
- rows：预估扫描行数
- Extra：避免 Using filesort、Using temporary

常见优化：
- 索引覆盖：避免回表
- 延迟关联：优化分页
- 小表驱动大表：优化 Join
- COUNT(*)：统计最快

面试必杀技

如果面试官问”你做过什么 SQL 优化”，可以讲一个真实的案例：

1. 发现慢查询（通过 slow log，查询时间 5s）
2. 用 EXPLAIN 分析（发现全表扫描 + filesort）
3. 建立联合索引（覆盖查询字段 + 排序字段）
4. 验证效果（查询时间降到 10ms，提升 500 倍）

这样的回答既有理论又有实战，最能打动面试官。