EXPLAIN优化 SQL示例

发布于：2025-06-23 ⋅ 阅读:(13) ⋅ 点赞:(0)

以下通过 6 个真实案例展示如何使用 EXPLAIN 优化 SQL，每个案例包含问题 SQL、EXPLAIN 分析、优化方案和优化后效果对比：

案例 1：全表扫描优化 (type=ALL)

问题 SQL（用户订单查询）：
SELECT * FROM orders 
WHERE user_id = 1005 
  AND create_date > '2023-01-01';
⚠️ EXPLAIN 分析：
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | rows | Extra  |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | 50万 | Using where|
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

诊断：

  • type=ALL：全表扫描
  • rows=500,000：扫描 50 万行
  • key=NULL：未使用索引
🔧 优化方案：
-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_date);
✅ 优化后 EXPLAIN：
+----+-------------+--------+-------+---------------+------------------+---------+------+------+
| id | select_type | table  | type  | key           | key_len | rows | Extra|
+----+-------------+--------+-------+---------------+---------+------+------+
| 1  | SIMPLE      | orders | ref   | idx_user_create | 8       | 15   | Using index |
+----+-------------+--------+-------+---------------+---------+------+------+

效果：

  • 扫描行数 50万 → 15 行
  • 查询时间 2.8秒 → 0.02秒

案例 2：文件排序优化 (Using filesort)

问题 SQL（最新商品查询）：
SELECT * FROM products 
WHERE category = 'electronics' 
ORDER BY create_time DESC 
LIMIT 20;
⚠️ EXPLAIN 分析：
+----+-------------+----------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table    | type | key           | rows | Extra                              |
+----+-------------+----------+------+---------------+------+-------------------------------------+
| 1  | SIMPLE      | products | ref  | idx_category  | 2500 | Using index condition; Using filesort|
+----+-------------+----------+------+---------------+------+-------------------------------------+

诊断：

  • Using filesort：额外文件排序
  • 虽然用了索引，但排序字段未包含
🔧 优化方案：
-- 创建包含排序字段的索引
ALTER TABLE products ADD INDEX idx_cat_time (category, create_time DESC);
✅ 优化后 EXPLAIN：
+----+-------------+----------+-------+----------------+---------+------+-------------+
| id | select_type | table    | type  | key            | rows    | Extra          |
+----+-------------+----------+-------+----------------+---------+----------------+
| 1  | SIMPLE      | products | ref   | idx_cat_time   | 2500    | Using where    |
+----+-------------+----------+-------+----------------+---------+----------------+

效果：

  • 移除 Using filesort（索引已排好序）
  • 500ms 的文件排序操作 → 0ms

案例 3：子查询优化 (DEPENDENT SUBQUERY)

问题 SQL（高消费用户查询）：
SELECT * FROM users 
WHERE id IN (
    SELECT user_id 
    FROM orders 
    WHERE amount > 1000
);
⚠️ EXPLAIN 分析：
+----+--------------------+--------+----------------+---------------+-----------------+
| id | select_type        | table  | type           | key           | rows | Extra       |
+----+--------------------+--------+----------------+---------------+-----------------+
| 1  | PRIMARY            | users  | ALL            | NULL          | 10万 | Using where |
| 2  | DEPENDENT SUBQUERY | orders | index_subquery | idx_user      | 30   | Using where |
+----+--------------------+--------+----------------+---------------+-----------------+

诊断：

  • DEPENDENT SUBQUERY：外查询每行都执行子查询
  • 外层全表扫描 10万行 × 子查询 30行 = 实际扫描 300万行
🔧 优化方案：
-- 改为 JOIN 写法
SELECT u.* 
FROM users u
JOIN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE amount > 1000
) o ON u.id = o.user_id;
✅ 优化后 EXPLAIN：
+----+-------------+------------+--------+---------------+---------+------+-------+
| id | select_type | table      | type   | key           | rows    | Extra|
+----+-------------+------------+--------+---------------+---------+------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL          | 1500    |       |
| 1  | PRIMARY     | u          | eq_ref | PRIMARY       | 1       |       |
| 2  | DERIVED     | orders     | range  | idx_amount     | 1500    |       |
+----+-------------+------------+--------+---------------+---------+------+

效果：

  • 执行时间 4.2秒 → 0.3秒
  • 扫描总量 300万行 → 1500 + 1500行

案例 4：索引覆盖优化 (回表查询)

问题 SQL（用户统计）：
SELECT username, email FROM users 
WHERE register_time BETWEEN '2022-01-01' AND '2022-12-31';
⚠️ EXPLAIN 分析：
+----+-------------+-------+-------+------------------+---------+------+-------+
| id | select_type | table | type  | key              | rows    | Extra       |
+----+-------------+-------+-------+------------------+---------+-------------+
| 1  | SIMPLE      | users | range | idx_register_time| 15000   | Using where |
+----+-------------+-------+-------+------------------+---------+-------------+

诊断：

  • Extra列信息仅显示 Using where，​没有出现 Using index​（重要！）
    这表示：
    虽然使用了索引idx_register_time定位数据（type=range证明索引生效）
    但索引未覆盖所有查询字段，需回聚簇索引获取完整行数据
  • 虽然使用了索引，但需要回表查 username, email 字段
  • 潜在优化点：覆盖索引
🔧 优化方案：
-- 创建包含所有查询字段的覆盖索引
ALTER TABLE users ADD INDEX idx_cover_register (register_time, username, email);
✅ 优化后 EXPLAIN：
+----+-------------+-------+-------+--------------------+---------+------+-------------+
| id | select_type | table | type  | key                | rows    | Extra            |
+----+-------------+-------+-------+--------------------+---------+------------------+
| 1  | SIMPLE      | users | range | idx_cover_register | 15000   | Using where; Using index |
+----+-------------+-------+-------+--------------------+---------+------------------+

效果：

  • Using index：避免回表操作
  • I/O 操作减少 60%
  • 查询时间 450ms → 120ms

案例 5：JOIN 优化 (错误的 JOIN 顺序)

问题 SQL（订单详情查询）：
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.amount > 500 
  AND u.vip_level > 3;
⚠️ EXPLAIN 分析：
+----+-------------+-------+------+---------------+---------+------+----------+-------------+
| id | select_type | table | type | key           | rows    | Extra                        |
+----+-------------+-------+------+---------------+---------+------------------------------+
| 1  | SIMPLE      | u     | ALL  | idx_vip       | 10000   | Using where                  |
| 1  | SIMPLE      | o     | ref  | idx_user      | 25      | Using where                  |
+----+-------------+-------+------+---------------+---------+------------------------------+

诊断：

  • 先扫描 1万VIP用户，再关联订单
  • 实际订单筛选条件 amount>500 在关联后执行
🔧 优化方案：
-- 重写查询调整 JOIN 顺序
SELECT o.*, u.name 
FROM (
    SELECT * FROM orders 
    WHERE amount > 500  -- 先过滤大表
) o
JOIN users u ON o.user_id = u.id 
WHERE u.vip_level > 3;
✅ 优化后 EXPLAIN：
+----+-------------+------------+--------+---------------+---------+------+--------+
| id | select_type | table      | type   | key           | rows    | Extra       |
+----+-------------+------------+--------+---------------+---------+-------------+
| 1  | PRIMARY     | <derived2> | ALL    | NULL          | 8000    |             |
| 1  | PRIMARY     | u          | eq_ref | PRIMARY       | 1       | Using where |
| 2  | DERIVED     | orders     | range  | idx_amount    | 8000    | Using where |
+----+-------------+------------+--------+---------------+---------+-------------+

效果：

  • 减少驱动表数据量：1万行 → 8000行
  • 总扫描行数：1万×25=25万行 → 8000+8000行
  • 执行时间：1.8秒 → 0.4秒

案例 6：分页深度优化 (大偏移量分页)

问题 SQL（第10000页数据）：
SELECT id, title FROM articles 
ORDER BY create_time DESC 
LIMIT 10000, 20; -- 跳过10000条
⚠️ EXPLAIN 分析：
+----+-------------+----------+-------+---------------+---------+------+-------------+
| id | select_type | table    | type  | key           | rows    | Extra                 |
+----+-------------+----------+-------+---------------+---------+-----------------------+
| 1  | SIMPLE      | articles | index | idx_create    | 10020   | Using index           |
+----+-------------+----------+-------+---------------+---------+-----------------------+

诊断：

  • rows=10020：实际读取 10020 行（即使最终只返回20条）
  • 深度分页性能灾难
🔧 优化方案：
-- 基于游标的优化写法
SELECT id, title 
FROM articles 
WHERE create_time < '2023-06-01'  -- 传入上一页的最后时间
ORDER BY create_time DESC 
LIMIT 20;
✅ 优化效果对比：
方案 扫描行数 执行时间
原始方案 10020 320ms
游标方案 20 1.2ms
提升 500倍 266倍

总结：EXPLAIN 优化路线图

graph TD
    A[捕获问题SQL] --> B[运行EXPLAIN]
    B --> C{关键问题点}
    C -->|type=ALL| D[添加缺失索引]
    C -->|Using filesort| E[添加排序索引]
    C -->|DEPENDENT SUBQUERY| F[改写为JOIN]
    C -->|全表扫描| G[添加覆盖索引]
    C -->|高rows值| H[优化查询条件]
    C -->|Using temporary| I[优化GROUP BY]
    D & E & F & G & H & I --> J[重新EXPLAIN验证]
    J --> K{性能达标？}
    K -->|是| L[完成]
    K -->|否| B

通过系统分析 EXPLAIN 结果，遵循 “减少扫描行数” 和 “避免额外操作” 两大原则，可解决大多数 SQL 性能问题。建议将 EXPLAIN 作为 SQL 上线前的标准检查项。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布