MySQL慢查询优化实战：从EXPLAIN到索引设计的完整指南

数据库性能问题往往是线上故障的头号元凶，而慢查询则是其中最常见的表现形式。一条执行缓慢的SQL就可能拖垮整个应用的响应速度，甚至导致数据库连接池耗尽。本文将从实际案例出发，带你系统掌握MySQL慢查询的排查与优化方法，涵盖EXPLAIN分析、索引设计原则和常见反模式。

数据库优化

Table of Contents

一、开启慢查询日志定位问题SQL

优化的第一步是找到慢在哪。MySQL提供了慢查询日志功能，可以记录执行时间超过阈值的所有SQL语句。

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time%';

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

生产环境建议将long_query_time设为1秒或更低。使用mysqldumpslow工具分析慢查询日志：

# 按执行时间排序，显示前10条最慢的查询
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

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

二、使用EXPLAIN深入分析执行计划

找到慢SQL后，用EXPLAIN查看MySQL的执行计划，这是优化的核心工具。

技术分析

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at > '2026-01-01'
ORDER BY o.total DESC
LIMIT 20;

重点关注以下字段：

type列显示为ALL意味着全表扫描，这是严重的性能隐患。Extra列的Using filesort和Using join buffer也说明缺乏有效索引。重点关注type从好到差依次为：system > const > eq_ref > ref > range > index > ALL，优化目标至少要达到range级别。

三、索引设计的核心原则

好的索引设计是慢查询优化的关键。以下是几个经过验证的索引设计原则：

# 使用Python脚本批量检查表的索引情况
import pymysql

def check_missing_indexes(conn, table_name):
    cursor = conn.cursor(pymysql.cursors.DictCursor)

    # 查看表的现有索引
    cursor.execute(f"SHOW INDEX FROM {table_name}")
    indexes = cursor.fetchall()
    print(f"\n=== {table_name} 现有索引 ===")
    for idx in indexes:
        print(f"  {idx['Key_name']}: {idx['Column_name']} (cardinality: {idx['Cardinality']})")

    # 查看表的状态信息
    cursor.execute(f"SHOW TABLE STATUS LIKE '{table_name}'")
    status = cursor.fetchone()
    print(f"  行数: {status['Rows']}, 引擎: {status['Engine']}")

    return indexes

# 连接数据库并分析
conn = pymysql.connect(host='localhost', user='root', password='pwd', db='myapp')
check_missing_indexes(conn, 'orders')

联合索引的最左前缀原则是最容易出错的地方：

-- 创建联合索引
ALTER TABLE orders ADD INDEX idx_status_created_total (status, created_at, total);

-- 能命中索引（使用了最左前缀）
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01';
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2026-01-01' AND total > 100;

-- 无法命中索引（跳过了最左列）
SELECT * FROM orders WHERE created_at > '2026-01-01';
SELECT * FROM orders WHERE total > 100;

四、常见反模式与优化技巧

以下是一些在实际项目中频繁出现的慢查询反模式：

-- 反模式1：在索引列上使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-21';
-- 改用范围查询
SELECT * FROM orders WHERE created_at >= '2026-06-21' AND created_at < '2026-06-22';

-- 反模式2：隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- phone是varchar类型
-- 保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

-- 反模式3：SELECT * 取所有列
SELECT * FROM orders WHERE user_id = 100;
-- 只取需要的列，利用覆盖索引
SELECT id, total, status FROM orders WHERE user_id = 100;

-- 反模式4：大OFFSET分页
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- 使用游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;

数据图表

五、使用pt-query-digest进行深度分析

Percona Toolkit的pt-query-digest是生产环境分析慢查询的利器，比mysqldumpslow功能更强大：

# 安装Percona Toolkit
apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

# 只分析最近1小时的日志
pt-query-digest --since '1h' /var/log/mysql/mysql-slow.log

# 分析特定数据库的查询
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/mysql-slow.log

报告中的关键指标包括：总查询数、总执行时间、每条SQL的平均响应时间、执行次数占比等。通过这些指标可以快速定位最需要优化的SQL语句。建议将pt-query-digest集成到定时任务中，每天自动生成报告并发送告警。

总结

MySQL慢查询优化是一个系统工程，核心要点如下：

1. 开启慢查询日志和log_queries_not_using_indexes，让问题SQL无处遁形。2. 用EXPLAIN分析每一条慢SQL，重点关注type、key、rows和Extra字段。3. 遵循联合索引的最左前缀原则，优先设计覆盖索引。4. 避免在索引列上使用函数、隐式类型转换、SELECT *和大OFFSET分页。5. 使用pt-query-digest进行生产环境的深度分析和持续监控。

记住，优化不是一次性的工作，建立慢查询监控告警机制，定期Review执行计划，才能持续保持数据库的高性能运行。