张会挽's Blog

一次数据库慢查询优化：EXPLAIN 分析与索引重建

Posted on In

数据库慢查询是后端性能问题的常见原因。一次看似简单的 SQL，可能因为缺少索引或写法问题导致全表扫描，几秒甚至几十秒的响应时间。本文记录一次完整的慢查询优化过程。

慢查询日志开启

查看慢查询配置

1
2
3
4
5
6
7
8
-- 查看慢查询是否开启
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 输出：
-- slow_query_log = ON
-- long_query_time = 1.000
-- slow_query_log_file = /var/lib/mysql/slow.log

开启慢查询日志

1
2
3
4
5
6
7
8
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置阈值：超过 0.5 秒记录
SET GLOBAL long_query_time = 0.5;

-- 指定日志文件
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

永久配置

1
2
3
4
5
6
# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

分析慢查询日志

1
2
3
4
5
6
7
8
9
# 使用 mysqldumpslow
mysqldumpslow -t 10 /var/log/mysql/slow.log

# 输出：
# Reading N most recent dummies
# Count: 5  Time=2.34s (11s)  Lock=0.00s (0s)  Rows=100000 (500000), User=root

# 使用 pt-query-digest（更强大）
pt-query-digest /var/log/mysql/slow.log

EXPLAIN 结果解读

基本用法

1
2
3
4
EXPLAIN SELECT u.id, u.name, o.order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.create_time > '2024-01-01';

关键字段

字段 含义 好的值
type 访问类型 ref, range, const
key 使用的索引 非空
rows 扫描行数 越少越好
Extra 额外信息 避免 Using filesort, Using temporary

type 详解

type 说明 性能
ALL 全表扫描 最差
index 索引全扫描 差
range 索引范围扫描 中
ref 索引等值查询 好
eq_ref 唯一索引等值 好
const/system 常量/系统查询 最好

Extra 详解

1
2
3
4
Using index    # 使用覆盖索引，无需回表
Using where    # 使用 WHERE 过滤
Using filesort # 需要额外排序
Using temporary# 使用临时表

索引失效场景

1. 函数/计算导致索引失效

1
2
3
4
5
6
7
-- 错误：索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
SELECT * FROM orders WHERE DAYOFMONTH(create_time) = 15;

-- 正确：范围查询，索引有效
SELECT * FROM orders
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

2. LIKE 前缀匹配

1
2
3
4
5
-- 索引失效
SELECT * FROM users WHERE name LIKE '%张%';

-- 索引有效
SELECT * FROM users WHERE name LIKE '张%';

3. OR 导致索引断裂

1
2
3
4
5
6
7
8
9
10
-- 错误：OR 前后有一方不使用索引
SELECT * FROM users WHERE id = 1 OR phone = '13800000000';

-- 正确：拆分为 UNION
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE phone = '13800000000';

-- 或者使用 IN
SELECT * FROM users WHERE id IN (1, 2, 3);

4. 隐式类型转换

1
2
3
4
5
6
-- phone 是 VARCHAR 类型
-- 传入数字，索引失效
SELECT * FROM users WHERE phone = 13800000000;

-- 正确：传入字符串
SELECT * FROM users WHERE phone = '13800000000';

5. IS NULL / IS NOT NULL

1
2
-- 如果允许，NULL 值较少时避免 IS NULL
-- 考虑添加默认值 '' 或 0

6. != 和 NOT IN

1
2
3
4
5
6
-- != 和 NOT IN 通常无法使用索引
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);

-- 考虑使用 BETWEEN
SELECT * FROM users WHERE id BETWEEN 1 AND 100;

联合索引最左前缀

联合索引结构

1
2
3
4
5
6
7
8
-- 创建联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 索引结构（按 B+ 树排序）
-- (user_id, status, create_time)
--  (1, 0, '2024-01-01')
--  (1, 1, '2024-01-02')
--  (2, 0, '2024-01-03')

命中规则

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 完全命中：使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 1 AND create_time > '2024-01-01';

-- 命中前两个：使用索引
SELECT * FROM orders WHERE user_id = 1 AND status = 1;

-- 命中第一个：使用索引
SELECT * FROM orders WHERE user_id = 1;

-- 跳过第一个：不使用索引（索引断裂）
SELECT * FROM orders WHERE status = 1;
SELECT * FROM orders WHERE create_time > '2024-01-01';
SELECT * FROM orders WHERE user_id = 1 AND create_time > '2024-01-01';

索引顺序选择

原则：区分度高的列放前面

1
2
3
4
5
6
-- user_id 区分度高，status 区分度低
-- 正确顺序：区分度高的放前
INDEX idx1 (user_id, status, create_time)

-- 如果 status = 0/1 各占 50%，单独用 status 没意义
-- 但 user_id + status 组合效果好

索引重建与优化

查看现有索引

1
2
3
4
5
-- 查看表的所有索引
SHOW INDEX FROM orders;

-- 查看某个索引的使用情况
SHOW INDEX FROM orders FROM mysql;

重建索引

1
2
3
4
5
-- 优化表（重建索引）
OPTIMIZE TABLE orders;

-- 或
ALTER TABLE orders ENGINE = InnoDB;

删除冗余索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查找冗余索引
SELECT
    a.index_name as `Index`,
    a.column_name as Column,
    b.index_name as RedundantTo,
    b.column_name as RedundantColumn
FROM information_schema.statistics a
JOIN information_schema.statistics b
  ON a.table_name = b.table_name
  AND a.index_name != b.index_name
  AND a.column_name = b.column_name
WHERE a.index_name != 'PRIMARY';

-- 删除冗余索引
ALTER TABLE orders DROP INDEX idx_redundant;

添加索引（在线 DDL）

1
2
3
4
5
-- MySQL 5.6+ 支持在线 DDL
ALTER TABLE orders
  ADD INDEX idx_user_status (user_id, status),
  ALGORITHM=INPLACE,   -- 在线修改
  LOCK=NONE;           -- 不锁表

实战：复杂查询优化

原始 SQL（2秒）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
    u.id,
    u.name,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_amount,
    AVG(o.amount) as avg_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE u.status = 1
    AND o.create_time >= '2024-01-01'
    AND o.create_time < '2025-01-01'
    AND p.category_id IN (1, 2, 3, 4, 5)
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10
ORDER BY total_amount DESC
LIMIT 100;

EXPLAIN 分析

1
2
3
4
5
6
7
EXPLAIN SELECT ...

-- 问题发现：
-- type: ALL  (全表扫描)
-- key: NULL  (没有索引)
-- rows: 500000 (扫描 50 万行)
-- Extra: Using filesort, Using temporary

问题诊断

  1. 没有索引：WHERE 条件列没有索引
  2. JOIN 过多：多层 JOIN 导致性能差
  3. SELECT 过多：SELECT * 会获取不必要的数据
  4. GROUP BY 效率低：数据量大时 Using temporary

优化后 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Step 1: 添加必要的索引
ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);
ALTER TABLE order_items ADD INDEX idx_order_id (order_id);
ALTER TABLE products ADD INDEX idx_category (category_id);

-- Step 2: 优化 SQL 结构
SELECT
    u.id,
    u.name,
    order_stats.order_count,
    order_stats.total_amount,
    order_stats.avg_amount
FROM users u
INNER JOIN (
    -- 预聚合，减少数据量
    SELECT
        o.user_id,
        COUNT(*) as order_count,
        SUM(o.amount) as total_amount,
        AVG(o.amount) as avg_amount
    FROM orders o
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id
    WHERE o.status = 1
        AND o.create_time >= '2024-01-01'
        AND o.create_time < '2025-01-01'
        AND p.category_id IN (1, 2, 3, 4, 5)
    GROUP BY o.user_id
    HAVING COUNT(*) > 10
) order_stats ON u.id = order_stats.user_id
WHERE u.status = 1
ORDER BY order_stats.total_amount DESC
LIMIT 100;

-- Step 3: 再次 EXPLAIN
EXPLAIN SELECT ...

优化后效果

1
2
3
4
-- type: ref  (使用索引)
-- key: idx_status_time (使用索引)
-- rows: 1000  (只扫描 1000 行)
-- Extra: Using index (覆盖索引)
指标 优化前 优化后
执行时间 2.0s 0.05s
扫描行数 500,000 1,000
索引使用 无 idx_status_time

总结

慢查询优化流程：

  1. 开启慢查询日志：slow_query_log
  2. 分析慢查询：mysqldumpslow、pt-query-digest
  3. EXPLAIN 分析：type、key、rows、Extra
  4. 定位问题：全表扫描、索引断裂、Using filesort
  5. 优化：
    • 添加索引
    • 改写 SQL
    • 减少 JOIN
    • 使用覆盖索引
  6. 验证：再次 EXPLAIN

常见优化场景：

场景 解决方案
全表扫描 添加 WHERE 条件索引
Using filesort 添加 ORDER BY 列索引
Using temporary 减少 GROUP BY 数据量
索引断裂 调整 SQL 或索引顺序
JOIN 效率低 预聚合、小表驱动大表