MySQL 8.0查询性能调优：慢查询诊断与索引优化实战

慢查询诊断的完整路径

MySQL性能问题的排查从慢查询日志开始。很多运维人员遇到数据库慢就调参数、加内存，这属于经验驱动的猜测式调优。正确路径是：定位慢查询、分析执行计划、确定瓶颈（全表扫描、临时表、文件排序、锁等待）、针对性优化。MySQL 8.0提供了丰富的诊断工具，按步骤使用可以精准定位问题。

# 开启慢查询日志并配置阈值
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# 使用mysqldumpslow分析慢日志摘要
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

MySQL 8.0的Performance Schema提供了比慢日志更精细的查询分析能力。events_statements_summary_by_digest表记录了每条SQL模板的执行次数、总耗时、锁等待时间等统计信息，是发现性能热点的高效入口。

# 查询耗时最高的SQL模板
SELECT
    DIGEST_TEXT AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 3) AS avg_time_ms,
    ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS avg_rows_examined,
    ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS avg_rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

avg_rows_examined / avg_rows_sent的比值反映查询效率，比值越大说明扫描了大量行但只返回少量结果，通常意味着索引不够精确或查询条件有问题。

EXPLAIN执行计划深度解读

拿到慢SQL后，用EXPLAIN分析执行计划是必须步骤。MySQL 8.0的EXPLAIN FORMAT=TREE提供了更直观的执行树视图。

# 标准EXPLAIN
EXPLAIN SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'shipped'
  AND o.created_at >= '2026-07-01'
ORDER BY o.total_amount DESC
LIMIT 50;

# 树形EXPLAIN（MySQL 8.0+）
EXPLAIN FORMAT=TREE SELECT ...;

EXPLAIN结果中需要重点关注的字段：

• type：访问类型，从好到差依次为system > const > eq_ref > ref > range > index > ALL。出现ALL（全表扫描）或index（全索引扫描）时需要优化
• key：实际使用的索引，NULL表示未使用索引
• rows：预估扫描行数，与实际行数可能有偏差，但数量级有参考价值
• Extra：附加信息，Using filesort（额外排序）、Using temporary（临时表）是需要优化的信号

索引设计原则与常见反模式

最左前缀原则

复合索引遵循最左前缀匹配规则。索引(a, b, c)可以支持a、(a,b)、(a,b,c)的查询条件，但不能直接支持b或(b,c)的条件。这是B+Tree索引结构的固有约束，调整索引列顺序可以覆盖更多查询模式。

# 场景：订单表有以下查询模式
# 1. WHERE customer_id = ? AND status = ?
# 2. WHERE customer_id = ? AND created_at BETWEEN ? AND ?
# 3. WHERE status = ? AND created_at BETWEEN ? AND ?

# 优化方案：三个复合索引覆盖三种查询
CREATE INDEX idx_customer_status ON orders(customer_id, status);
CREATE INDEX idx_customer_created ON orders(customer_id, created_at);
CREATE INDEX idx_status_created ON orders(status, created_at);

# 验证索引选择
EXPLAIN SELECT * FROM orders
WHERE customer_id = 1001 AND status = 'shipped';

索引下推优化

MySQL 5.6+引入的Index Condition Pushdown（ICP）可以将WHERE条件下推到索引扫描阶段，减少回表次数。但ICP只在特定条件下生效：条件必须引用索引列、存储引擎支持（InnoDB支持）、不涉及子查询。

# ICP生效示例
# 索引：idx_name_age (last_name, age)
# 查询：WHERE last_name LIKE 'Zhang%' AND age > 25

# EXPLAIN中Extra显示Using index condition表示ICP生效
EXPLAIN SELECT * FROM employees
WHERE last_name LIKE 'Zhang%' AND age > 25;

MySQL 8.0的隐藏索引与索引合并

MySQL 8.0支持不可见索引（Invisible Index），索引对优化器不可见但仍维护。这在索引删除前验证影响时非常有用。

# 将索引设为不可见，观察查询性能变化
ALTER INDEX idx_old ON orders INVISIBLE;

# 如果性能正常说明索引可以安全删除
# 如果性能下降则立即恢复
ALTER INDEX idx_old ON orders VISIBLE;

# 确认删除
DROP INDEX idx_old ON orders;

SQL查询改写优化实例

子查询转JOIN

# 慢查询：相关子查询，每行执行一次子查询
SELECT * FROM orders o
WHERE o.total_amount > (
    SELECT AVG(total_amount)
    FROM orders
    WHERE customer_id = o.customer_id
);

# 优化：先计算每个客户的平均金额，再JOIN
SELECT o.* FROM orders o
JOIN (
    SELECT customer_id, AVG(total_amount) AS avg_amount
    FROM orders
    GROUP BY customer_id
) a ON o.customer_id = a.customer_id
WHERE o.total_amount > a.avg_amount;

避免函数导致索引失效

# 索引失效：对索引列使用函数
SELECT * FROM orders
WHERE DATE(created_at) = '2026-08-04';  # 索引失效

# 优化：等值范围替代函数
SELECT * FROM orders
WHERE created_at >= '2026-08-04 00:00:00'
  AND created_at < '2026-08-05 00:00:00';  # 走range索引

分页查询优化

# 慢查询：深分页，OFFSET越大越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

# 优化方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

# 优化方案2：延迟关联，先查主键再回表
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

InnoDB Buffer Pool调优

查询优化之外，InnoDB Buffer Pool的配置直接影响数据读取性能。Buffer Pool应该容纳热数据集，但不必追求缓存全部数据。

# 评估Buffer Pool使用效率
SHOW ENGINE INNODB STATUS\G
# 关注:
# Buffer pool hit rate: 应在99%以上
# Free buffers: 过多说明分配过大

# MySQL 8.0动态调整Buffer Pool大小
SET GLOBAL innodb_buffer_pool_size = 8589934592;  # 8GB

# 多实例配置
# innodb_buffer_pool_instances = innodb_buffer_pool_size / 1GB

MySQL查询性能调优不是孤立的参数调整，而是系统性的诊断和优化过程：从慢日志定位热点SQL，用EXPLAIN分析执行计划，根据访问模式设计索引，改写低效SQL，最后调整Buffer Pool等引擎参数。这套流程可以反复迭代，每次调优都应有量化指标验证效果，避免盲调。