MySQL性能调优实战：慢查询诊断与索引优化策略详解

MySQL慢查询是数据库性能问题的最常见来源。一条低效SQL可能导致整个数据库实例的连接池耗尽，影响所有业务。本文从慢查询日志分析、执行计划解读、索引设计、SQL重写四个层面，提供一套完整的MySQL性能调优操作流程。

慢查询日志配置与分析

慢查询日志是定位性能问题的第一步。开启慢查询日志后，MySQL会将执行时间超过阈值的SQL记录到日志文件。

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

-- 开启慢查询日志
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';  -- 记录未用索引的查询

-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100  -- 扫描行数少于100不记录

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

# 关键输出字段：
# Rank - 按总耗时排名
# Response time - 累计执行时间
# Calls - 执行次数
# R/Call - 平均每次执行时间
# Examined - 扫描行数

pt-query-digest会将相似SQL归并统计，按累计耗时排序。优先优化排名靠前的SQL，它们通常贡献了80%以上的数据库负载。

EXPLAIN执行计划深度解读

定位到慢SQL后，通过EXPLAIN查看执行计划，了解MySQL如何执行这条查询。

-- 查看执行计划
EXPLAIN SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 10086 AND o.status = 'paid'
ORDER BY o.created_at DESC LIMIT 20;

执行计划的关键字段：

• type：访问类型。从好到差依次为：system > const > eq_ref > ref > range > index > ALL。出现ALL表示全表扫描，必须优化。
• key：实际使用的索引。NULL表示未走索引。
• rows：预估扫描行数。值越小说明索引越有效。
• Extra：额外信息。Using filesort（文件排序）和Using temporary（临时表）都是性能隐患。
• key_len：索引使用长度。可用于判断联合索引用了几个字段。

-- 查看完整执行计划（包含JSON格式，信息更详细）
EXPLAIN FORMAT=JSON SELECT ...;

-- 查看实际执行耗时（MySQL 8.0+）
EXPLAIN ANALYZE SELECT ...;

索引设计与覆盖索引优化

索引设计是MySQL调优的核心。联合索引遵循最左前缀原则，字段顺序决定了索引能覆盖哪些查询模式。

-- 问题SQL：扫描全表
SELECT id, order_no, amount, status FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

-- 优化前：单列索引
CREATE INDEX idx_user_id ON orders(user_id);
-- type=ref, rows=50000, Extra: Using where; Using filesort

-- 优化后：联合索引覆盖查询条件和排序
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
-- type=ref, rows=20, Extra: Using index condition
-- filesort消失，排序通过索引有序性完成

覆盖索引（Covering Index）指查询所需的所有字段都包含在索引中，MySQL直接从索引树返回数据，无需回表读取数据行。

-- 覆盖索引示例
-- 查询字段与索引字段完全匹配
SELECT user_id, status, created_at FROM orders
WHERE user_id = 10086 AND status = 'paid';
-- Extra: Using index  表示覆盖索引命中

-- 避免SELECT *，只查需要的字段
-- 错误：SELECT * 会回表读取全部列
SELECT * FROM orders WHERE user_id = 10086;

-- 正确：指定字段，可能命中覆盖索引
SELECT id, order_no, amount FROM orders WHERE user_id = 10086;

SQL查询重写技巧

同样的查询结果可以用不同SQL实现，执行效率可能差几个数量级。

-- 技巧1：避免函数调用导致索引失效
-- 错误：DATE函数导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-23';
-- 正确：范围查询走索引
SELECT * FROM orders
WHERE created_at >= '2026-07-23 00:00:00'
  AND created_at < '2026-07-24 00:00:00';

-- 技巧2：分页优化 - 深度分页使用游标
-- 错误：OFFSET过大时扫描大量行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 正确：游标分页，利用索引定位
SELECT * FROM orders WHERE id > 100020 ORDER BY id LIMIT 20;

-- 技巧3：子查询改JOIN
-- 错误：相关子查询执行多次
SELECT * FROM orders o
WHERE o.user_id IN (SELECT id FROM users WHERE level > 5);
-- 正确：JOIN执行效率更高
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.level > 5;

-- 技巧4：COUNT优化
-- 错误：COUNT(*)在大表上很慢
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 正确：使用汇总表或缓存近似值
SELECT approximate_count FROM order_stats WHERE status = 'paid';

InnoDB缓冲池与核心参数调优

InnoDB Buffer Pool是MySQL最重要的内存区域，缓存数据页和索引页。配置不当会导致频繁磁盘IO。

-- 查看Buffer Pool状态
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 关键配置（my.cnf）
[mysqld]
# Buffer Pool大小：物理内存的60%-70%
innodb_buffer_pool_size = 16G

# Buffer Pool实例数：每GB一个实例
innodb_buffer_pool_instances = 16

# 日志文件大小：影响崩溃恢复时间
innodb_log_file_size = 2G
innodb_log_files_in_group = 3

# 刷脏页策略
innodb_flush_method = O_DIRECT  # 绕过OS缓存
innodb_io_capacity = 2000       # SSD可设2000-5000
innodb_io_capacity_max = 4000

# 连接数配置
max_connections = 500
wait_timeout = 600
interactive_timeout = 600

-- 查看Buffer Pool命中率
SELECT
  (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100
  AS hit_rate
FROM (
  SELECT
    MAX(IF(Variable_name = 'Innodb_buffer_pool_reads', Value, 0)) AS Innodb_buffer_pool_reads,
    MAX(IF(Variable_name = 'Innodb_buffer_pool_read_requests', Value, 0)) AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE Variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) t;
-- 命中率应 > 99%，低于此值说明Buffer Pool不足

连接池配置与并发控制

应用层连接池配置同样影响数据库性能。HikariCP是Spring Boot默认连接池，参数调优要点：

# application.yml
spring:
  datasource:
    hikari:
      maximum-pool-size: 20        # 最大连接数
      minimum-idle: 10             # 最小空闲连接
      connection-timeout: 3000     # 连接超时3秒
      idle-timeout: 600000         # 空闲超时10分钟
      max-lifetime: 1800000        # 连接最大生命周期30分钟
      leak-detection-threshold: 60000  # 连接泄漏检测

连接池大小公式：pool_size = (核心数 * 2) + 有效磁盘数。对于4核SSD服务器，连接池大小约10。过大的连接池会增加数据库端的上下文切换开销，反而降低吞吐量。

慢查询优化是持续过程。建议建立慢查询监控看板，设置阈值告警，新上线的SQL必须通过EXPLAIN审核。定期执行ANALYZE TABLE更新统计信息，确保优化器选择正确的执行计划。对于数据量超过千万的大表，提前规划分库分表方案，避免单表成为性能瓶颈。