MySQL 慢查询优化实战：从 EXPLAIN 到索引调优

前言

MySQL 慢查询是影响应用性能的常见瓶颈。本文从慢查询日志分析入手，结合 EXPLAIN 执行计划解读，系统讲解索引优化和 SQL 调优方法。

一、慢查询日志

1.1 开启慢查询日志

-- 查看慢查询配置
SHOW VARIABLES LIKE "%slow_query%";

-- 开启慢查询日志
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;

1.2 使用 mysqldumpslow 分析

# 按平均查询时间排序，取前 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按总耗时排序
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

二、EXPLAIN 执行计划

2.1 基本用法

EXPLAIN SELECT * FROM orders WHERE user_id = 10086 AND status = "paid";

2.2 关键字段解读

2.3 type 类型（从好到差）

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

• const：主键或唯一索引等值查询
• eq_ref：JOIN 时使用主键或唯一索引
• ref：非唯一索引等值查询
• range：索引范围查询（BETWEEN、>、<、IN）
• index：扫描整个索引树
• ALL：全表扫描，必须优化

三、索引优化

3.1 创建合适的索引

-- 单列索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 联合索引（注意最左前缀原则）
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

-- 覆盖索引（查询字段都在索引中）
CREATE INDEX idx_cover ON orders(user_id, status, amount);

3.2 最左前缀原则

-- 联合索引 (a, b, c)
-- 能命中索引：
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;

-- 不能命中索引：
SELECT * FROM t WHERE b = 2;
SELECT * FROM t WHERE c = 3;
SELECT * FROM t WHERE b = 2 AND c = 3;

3.3 避免索引失效

-- 函数操作导致索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 优化：
SELECT * FROM orders WHERE created_at >= "2026-01-01" AND created_at < "2027-01-01";

-- 隐式类型转换
SELECT * FROM orders WHERE order_no = 10086;  -- order_no 是 varchar
-- 优化：
SELECT * FROM orders WHERE order_no = "10086";

-- LIKE 前导通配符
SELECT * FROM users WHERE name LIKE "%张";
-- 优化：使用全文索引或调整业务逻辑
SELECT * FROM users WHERE name LIKE "张%";

-- OR 导致索引失效
SELECT * FROM orders WHERE user_id = 1 OR status = "paid";
-- 优化：使用 UNION ALL
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = "paid" AND user_id != 1;

四、SQL 调优实战

4.1 分页优化

-- 慢：OFFSET 大时性能差
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化方案 1：延迟关联
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

-- 优化方案 2：游标分页（记住上一页最后一条 ID）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

4.2 批量插入

-- 慢：循环单条插入
INSERT INTO logs (msg) VALUES ("log1");
INSERT INTO logs (msg) VALUES ("log2");

-- 优化：批量插入
INSERT INTO logs (msg) VALUES ("log1"), ("log2"), ("log3");

4.3 使用 Redis 缓存热点数据

import redis
import json

r = redis.Redis(host="localhost", port=6379, db=0)

def get_user(user_id):
    # 先查缓存
    cache_key = f"user:{user_id}"
    cached = r.get(cache_key)
    if cached:
        return json.loads(cached)

    # 查数据库
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
    if user:
        r.setex(cache_key, 3600, json.dumps(user))  # 缓存 1 小时
    return user

五、总结

MySQL 慢查询优化的核心思路：先通过慢查询日志找到问题 SQL，再用 EXPLAIN 分析执行计划，最后通过添加索引、调整 SQL 写法、引入缓存等方式优化。记住：不是所有慢查询都需要加索引，有时调整业务逻辑比优化 SQL 更有效。