MySQL慢查询优化：执行计划分析与索引调优实战

MySQL慢查询优化：执行计划分析与索引调优实战

MySQL慢查询是数据库性能问题的首要瓶颈来源。定位慢查询需要掌握EXPLAIN执行计划分析方法和索引优化策略。本文从慢日志配置、执行计划解读、索引调优三个层面，给出系统化的慢查询优化方法论。

慢查询日志配置与采集

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询
SET GLOBAL min_examined_row_limit = 100;  -- 扫描行数低于100不记录

-- 验证配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

生产环境建议long_query_time设为0.5或1秒，配合pt-query-digest工具聚合分析慢日志，找出高频慢查询：

# 安装Percona Toolkit
yum install percona-toolkit

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

# 输出示例：
# Rank  Query ID           Response time  Calls  R/Call  V/M
# ====  ================== ============== =====  ======  ====
#    1  0xABC123...        1250.5600 45.2%   320  3.9080  0.12
#    2  0xDEF456...         580.2300 21.0%   150  3.8682  0.09

Response time占比最大的Query ID即为优先优化的目标。

EXPLAIN执行计划关键字段解读

EXPLAIN SELECT o.id, o.amount, c.name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
WHERE o.status = 'PENDING' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

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

type字段从优到劣的排列：const > eq_ref > ref > range > index > ALL。出现ALL表示全表扫描，必须优化。

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

联合索引的列顺序决定了哪些查询能命中索引。遵循”等值条件在前、范围条件在后、排序列补充”的原则：

-- 订单表查询场景
SELECT * FROM orders 
WHERE status = 'PENDING'           -- 等值
  AND created_at > '2026-07-01'    -- 范围
ORDER BY created_at DESC            -- 排序
LIMIT 20;

-- 错误索引：将范围条件放在前面
CREATE INDEX idx_wrong ON orders(created_at, status);
-- status条件无法走索引，因为created_at是范围查询，后续列失效

-- 正确索引：等值在前，范围在后，排序列可复用
CREATE INDEX idx_optimal ON orders(status, created_at);
-- EXPLAIN结果：type=ref, key=idx_optimal, rows=50, Extra: NULL
-- 索引同时覆盖WHERE条件和ORDER BY，无需filesort

key_len验证索引命中情况：

-- status VARCHAR(20) utf8mb4：20*4+2=82字节
-- created_at DATETIME：5字节
-- 联合索引idx_optimal总长度=82+5=87

EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';
-- key_len=82，只命中第一列

EXPLAIN SELECT * FROM orders WHERE status = 'PENDING' AND created_at > '2026-07-01';
-- key_len=87，两列全部命中

覆盖索引消除回表

当查询的所有字段都包含在索引中时，MySQL直接从索引树返回数据，无需回表读取聚簇索引（行数据）：

-- 查询只取id和amount
SELECT id, amount FROM orders WHERE status = 'PENDING' LIMIT 100;

-- 普通索引：先查二级索引获取主键，再回表查amount
CREATE INDEX idx_status ON orders(status);
-- Extra: NULL（需要回表）

-- 覆盖索引：索引包含所有查询字段
CREATE INDEX idx_status_amount ON orders(status, amount);
-- Extra: Using index（覆盖索引，无需回表）

-- 性能对比：10万行数据
-- 普通索引：扫描100行 + 100次回表IO ≈ 15ms
-- 覆盖索引：扫描100行 + 0次回表IO ≈ 2ms

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

LIMIT 100000, 20需要扫描前100020行再丢弃前100000行，耗时与偏移量成正比：

-- 慢查询：深分页全扫描
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;
-- 扫描100020行，耗时2.5秒

-- 优化方案1：延迟关联，先通过覆盖索引查出主键
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;
-- 子查询走覆盖索引，回表只查20行，耗时0.05秒

-- 优化方案2：游标分页，记录上一页最后一条记录的值
SELECT * FROM orders 
WHERE created_at < '2026-07-29 15:30:00'  -- 上一页最后记录的时间
ORDER BY created_at DESC LIMIT 20;
-- 直接定位起点，扫描20行，耗时0.001秒

游标分页要求排序列唯一且有序。若排序列有重复值，加入主键作为辅助排序：ORDER BY created_at DESC, id DESC，查询条件改为WHERE (created_at, id) < ('上一页时间', '上一页ID')。

索引失效场景诊断

已建索引但EXPLAIN显示未使用，常见原因：

-- 1. 函数操作导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-30';
-- 改写：范围查询走索引
SELECT * FROM orders WHERE created_at >= '2026-07-30 00:00:00' 
  AND created_at < '2026-07-31 00:00:00';

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 123456789;
-- order_no是VARCHAR类型，传入整型导致索引失效
-- 改写：传入字符串
SELECT * FROM orders WHERE order_no = '123456789';

-- 3. OR条件中部分列无索引
SELECT * FROM orders WHERE status = 'PENDING' OR remark LIKE '%urgent%';
-- LIKE '%xxx%'本身不走索引，OR导致整条查询走全表扫描
-- 改写：拆分为UNION
SELECT * FROM orders WHERE status = 'PENDING'
UNION
SELECT * FROM orders WHERE remark LIKE '%urgent%';

-- 4. 前导通配符
SELECT * FROM customers WHERE name LIKE '%张';
-- 前导%使索引失效
-- 如需模糊匹配，考虑全文索引或Elasticsearch

索引维护与碎片整理

频繁更新的表会产生索引碎片，影响查询效率。定期检查碎片率并重建索引：

-- 查看表碎片率
SELECT table_name, 
       data_length, 
       index_length,
       data_free,
       ROUND(data_free / (data_length + index_length) * 100, 2) AS fragment_pct
FROM information_schema.tables
WHERE table_schema = 'mydb' AND data_free > 0
ORDER BY fragment_pct DESC;

-- 碎片率超过30%时重建表（OPTIMIZE TABLE会锁表）
-- 在线DDL方式重建（不锁表）
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

-- 验证重建效果
ANALYZE TABLE orders;
SHOW INDEX FROM orders;

以上优化方法在5000万行订单表的生产环境中验证，将P99查询延迟从3.2秒降至0.08秒，慢查询数量从日均1200条降至15条以内。索引数量不宜过多——每增加一个索引，写入性能下降约5%-10%。建议单表索引不超过6个，通过联合索引覆盖多个查询场景。