MySQL慢查询是数据库运维中最常见的性能瓶颈来源。一条低效SQL在高并发下可能拖垮整个数据库实例，引发连锁超时。本文从执行计划解读入手，系统讲解SQL查询优化的方法论和实操技巧。

慢查询日志配置与采集

定位慢查询的第一步是启用慢查询日志，记录所有执行时间超过阈值的SQL语句。

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 100;

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

使用pt-query-digest分析慢查询日志，按总耗时排序找出最需要优化的SQL：

# 安装Percona Toolkit
yum install percona-toolkit

# 分析慢查询日志，按总耗时排序
pt-query-digest /var/log/mysql/slow.log --order-by Query_time:sum

# 只统计最近1小时的慢查询
pt-query-digest --since "1h ago" /var/log/mysql/slow.log

EXPLAIN执行计划深度解读

拿到慢SQL后，使用EXPLAIN分析其执行计划是SQL查询优化的核心步骤。执行计划中的关键字段决定了查询的效率。

-- 查看执行计划
EXPLAIN SELECT o.id, o.total, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'paid' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC 
LIMIT 20;

-- 查看完整执行计划（包含成本估算）
EXPLAIN FORMAT=JSON SELECT ...;

执行计划输出中需重点关注以下字段：

-- type字段：访问类型，性能从好到差
-- system > const > eq_ref > ref > range > index > ALL

-- const：主键或唯一索引等值查询，最快
EXPLAIN SELECT * FROM users WHERE id = 1;

-- eq_ref：JOIN时被驱动表使用主键或唯一索引
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- range：索引范围扫描
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-08-01';

-- index：全索引扫描
-- ALL：全表扫描（最差，必须优化）

key字段显示实际使用的索引，rows字段是预估扫描行数，Extra字段提供附加信息。以下Extra值需要注意：

-- Using index：覆盖索引，不需要回表，最优
-- Using where：通过索引查找后还需过滤
-- Using temporary：使用临时表（GROUP BY、DISTINCT常见）
-- Using filesort：文件排序（需要优化排序逻辑或索引）
-- Using join buffer：JOIN使用Block Nested Loop（缺少索引）

-- 糟糕示例：Using temporary; Using filesort
EXPLAIN SELECT user_id, COUNT(*) 
FROM orders 
WHERE status = 'paid' 
GROUP BY user_id 
ORDER BY COUNT(*) DESC;

复合索引设计与最左前缀原则

MySQL性能调优中，索引设计的核心原则是覆盖查询条件和排序需求。复合索引遵循最左前缀匹配规则，列顺序直接影响索引利用率。

-- 场景：订单查询，常见WHERE条件组合
-- WHERE status = ? AND created_at > ? ORDER BY created_at DESC
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- WHERE user_id = ? AND created_at BETWEEN ? AND ?

-- 错误索引：单独建三个单列索引
-- 查询时MySQL最多选择一个索引，无法同时覆盖多条件
ALTER TABLE orders ADD INDEX idx_status (status);
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_created (created_at);

-- 正确索引：根据查询模式设计复合索引
-- 索引1：覆盖status + created_at查询和排序
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 索引2：覆盖user_id + status + created_at
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

-- 验证索引使用情况
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 AND status = 'paid' 
ORDER BY created_at DESC LIMIT 20;
-- 期望：type=ref, key=idx_user_status_created, Extra=Using index

索引列顺序的经验法则：等值条件列在前，范围条件列在后，排序列最后。前缀匹配可以完整利用索引，排序也可以通过索引有序性避免filesort。

覆盖索引消除回表开销

InnoDB的二级索引存储的是主键值，通过二级索引查找数据需要先查索引拿到主键，再用主键回主键索引获取完整行。如果查询字段全部包含在索引中，可以跳过回表操作。

-- 原始查询：需要回表获取total字段
SELECT id, user_id, status, total FROM orders WHERE user_id = 100;
-- 索引 idx_user_status_created (user_id, status, created_at)
-- user_id走索引，但total不在索引中，需要回表

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_user_status_total (user_id, status, total);

-- 再次执行：Extra显示Using index，无需回表
EXPLAIN SELECT id, user_id, status, total FROM orders WHERE user_id = 100;
-- type: ref, Extra: Using index

覆盖索引的代价是索引体积增大和写入开销增加。在读取远多于写入的场景下，这是数据库高可用架构中提升读性能的有效手段。

分页查询优化与深分页问题

LIMIT offset在小偏移量时性能尚可，但当offset达到数万甚至百万级别时，MySQL需要扫描并丢弃前offset条记录，效率极低。分库分表方案中这个问题更加突出。

-- 慢查询：深分页，offset越大越慢
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;
-- 扫描100020行，丢弃前100000行

-- 方案1：游标分页（推荐）
SELECT * FROM orders 
WHERE created_at < '2026-08-05 10:30:00' 
ORDER BY created_at DESC 
LIMIT 20;
-- 只扫描20行，时间复杂度O(1)

-- 方案2：延迟关联
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE user_id = 100 
    ORDER BY created_at DESC 
    LIMIT 100000, 20
) t ON o.id = t.id;
-- 子查询使用覆盖索引快速定位主键，外层关联20行

-- 方案3：缓存热门页
-- 前几页数据缓存在Redis中，避免重复查询

JOIN优化与驱动表选择

MySQL优化器自动选择驱动表，但有时选择并非最优。数据备份恢复后的统计信息不准确会导致优化器误判。必要时通过STRAIGHT_JOIN强制指定驱动表。

-- 查看JOIN的执行计划
EXPLAIN SELECT o.*, p.name 
FROM orders o 
JOIN products p ON o.product_id = p.id 
WHERE o.user_id = 100;

-- 关键看：
-- 1. 哪个表是驱动表（id=1的表）
-- 2. 被驱动表是否走索引（ref类型）
-- 3. rows乘积是否过大

-- 强制小表驱动大表
SELECT STRAIGHT_JOIN o.*, p.name 
FROM orders o 
JOIN products p ON o.product_id = p.id 
WHERE o.user_id = 100;

-- 更新统计信息
ANALYZE TABLE orders, products;

监控与持续优化

数据库运维中应建立慢查询持续监控机制。通过performance_schema实时捕获执行统计，配合自动化告警在问题恶化前发现并修复。

-- 启用statements_summary
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%statements_summary%';

-- 查询平均执行时间最长的SQL
SELECT DIGEST_TEXT, COUNT_STAR, 
       ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
       ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'production'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 查看索引使用情况（识别未使用索引）
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, 
       COUNT_READ, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'production'
ORDER BY COUNT_READ DESC;

-- 删除未使用的索引减少写入开销
ALTER TABLE orders DROP INDEX idx_unused;

SQL查询优化是一个持续过程。国产数据库如OceanBase、TiDB在执行计划展示和索引行为上与MySQL存在差异，但核心方法论相通。建立慢查询巡检SOP，每周分析Top 10慢SQL并跟踪优化效果，是保障数据库稳定运行的基本功。