MySQL 8.0查询优化实战：慢查询定位与索引策略全解析

慢查询定位的系统性方法

MySQL慢查询优化不能靠猜测，必须基于数据定位问题。第一件事是确保慢查询日志处于开启状态，且阈值设置合理。生产环境中long_query_time建议设为0.1秒（100毫秒），这样能捕获所有超过100ms的查询。不要设为1秒或更高，否则会遗漏大量优化机会。

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL min_examined_row_limit = 100;

min_examined_row_limit设为100是为了过滤掉扫描行数极少但触发阈值的短查询。开启log_queries_not_using_indexes可以捕获未走索引的查询，这类查询即使执行时间不长，在数据量增长后也会成为隐患。

分析慢查询日志推荐使用pt-query-digest工具：

pt-query-digest /var/lib/mysql/slow.log > slow_report.txt

# 输出按执行时间排序的TOP查询，包含：
# - Query ID（查询指纹）
# - 执行次数
# - 总耗时/平均耗时/P95耗时
# - 扫描行数/返回行数比值
# - EXPLAIN结果摘要

EXPLAIN执行计划深度解读

拿到慢查询后，第一步是看执行计划。但很多开发者只关注type字段是否为ALL（全表扫描），忽略了其他关键信息：

EXPLAIN FORMAT=JSON
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 50;

执行计划JSON中需要关注的几个关键字段：

1. access_type：ALL（全表扫描）、index（全索引扫描）、range（索引范围扫描）、ref（索引等值查找）、const（单行查找）。理想情况是ref或range。

2. possible_keys vs key：possible_keys列出了可能使用的索引，key是实际选择的索引。两者不一致说明优化器选择了非预期索引，需要排查原因。

3. rows_examined vs rows_produced：扫描行数远大于返回行数说明索引选择性低或过滤条件没有完全利用索引。这个比值超过100:1的查询通常需要优化。

4. attached_subqueries：如果出现attached_subqueries，说明有子查询无法被优化为semi-join，可能需要改写为JOIN。

索引设计策略与常见反模式

索引设计不是越多越好，每个多余的索引都会增加写入开销和存储空间。核心原则是覆盖查询，减少回表。

策略一：复合索引的最左前缀匹配

-- 查询：WHERE status = 'pending' AND created_at > '2026-07-01'
-- 索引应该建为 (status, created_at)，status在前
-- 因为status是等值条件，created_at是范围条件
-- 等值条件放前面，范围条件放后面，可以最大化索引利用率

CREATE INDEX idx_status_created ON orders(status, created_at);

-- 反模式：created_at放前面
-- CREATE INDEX idx_created_status ON orders(created_at, status);
-- 范围条件created_at在前，等值条件status在后
-- 优化器无法跳过created_at的范围扫描去匹配status

策略二：覆盖索引消除回表

-- 查询只需要的列全部包含在索引中，不需要回表读取行数据
SELECT status, COUNT(*), SUM(amount)
FROM orders
WHERE created_at BETWEEN '2026-07-01' AND '2026-08-01'
GROUP BY status;

-- 建立覆盖索引
CREATE INDEX idx_created_status_amount ON orders(created_at, status, amount);

-- EXPLAIN中Extra列出现 Using index 说明使用了覆盖索引
-- 这比回表查询快5-50倍，取决于行数据和索引的大小差距

策略三：避免索引列上的函数调用

-- 反模式：索引列使用了函数，导致索引失效
WHERE DATE(created_at) = '2026-08-05'  -- 索引失效，全表扫描

-- 正确写法：使用范围查询
WHERE created_at >= '2026-08-05' AND created_at < '2026-08-06'  -- 索引有效

-- 反模式：隐式类型转换
WHERE varchar_col = 123  -- varchar列与整数比较，索引失效
WHERE varchar_col = '123'  -- 正确

复杂查询的改写技巧

技巧一：子查询改写为JOIN

-- 慢查询：相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.order_id = o.id AND oi.product_id = 100
);

-- 改写为JOIN
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON oi.order_id = o.id AND oi.product_id = 100;

技巧二：分页优化——避免深分页

-- 反模式：深分页扫描大量行后丢弃
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 实际扫描100020行，返回20行

-- 优化方案1：游标分页
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 利用主键索引直接定位，只扫描20行

-- 优化方案2：延迟关联
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;
-- 子查询只走索引扫描（覆盖索引），外层查询通过主键回表

技巧三：UNION优化

-- 反模式：UNION ALL后外层排序
(SELECT id, name FROM products WHERE category = 'A' ORDER BY price)
UNION ALL
(SELECT id, name FROM products WHERE category = 'B' ORDER BY price)
ORDER BY price LIMIT 20;

-- 优化：MySQL 8.0支持Index Merge，可以合并索引扫描
-- 如果category和price有联合索引，直接使用单查询
SELECT id, name FROM products
WHERE category IN ('A', 'B')
ORDER BY price LIMIT 20;

优化效果的持续监控

优化完成后，需要持续监控查询性能变化。MySQL 8.0的Performance Schema提供了细粒度的语句事件统计：

-- 查询TOP 10最耗时的SQL
SELECT DIGEST_TEXT,
       COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1000000000, 3) AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

建议建立每周慢查询评审流程：导出TOP 20慢查询，逐条分析执行计划变化。数据量增长可能导致原本高效的索引变得低效，需要根据数据分布变化调整索引策略。定期使用ANALYZE TABLE更新统计信息，确保优化器基于准确的数据选择执行计划。