MySQL 8.0性能调优实战：慢查询定位与索引优化深度指南

MySQL慢查询的发现与定位

MySQL性能问题的排查起点是慢查询日志。默认未开启，需手动配置：

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1        # 超过1秒记录
log_queries_not_using_indexes = 1  # 未用索引的查询也记录
min_examined_row_limit = 100     # 检查行数少于100不记录

线上环境开启慢查询日志后，用mysqldumpslow分析Top N慢查询：

$ mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Count: 342  Time=8.32s (2846s)  Lock=0.01s  Rows=1.2
SELECT * FROM orders WHERE status = 'S' AND created_at > 'S'
ORDER BY created_at DESC LIMIT N

这条查询执行342次，平均耗时8.32秒。接下来用EXPLAIN分析执行计划：

mysql> EXPLAIN SELECT * FROM orders
    -> WHERE status = 'shipped' AND created_at > '2026-07-01'
    -> ORDER BY created_at DESC LIMIT 20\G

           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: idx_status,idx_created_at
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1523456
     filtered: 1.23
        Extra: Using where; Using filesort

type为ALL表示全表扫描，key为NULL表示没有使用任何索引，Extra中Using filesort表示额外排序操作。这条查询扫描150万行只为返回20条结果，性能损耗集中在全表扫描和排序上。

索引优化的实战策略

策略一：建立覆盖索引消除回表

上述查询只使用status和created_at两列过滤并排序，如果索引能覆盖查询的所有列，可以避免回表操作：

mysql> ALTER TABLE orders ADD INDEX idx_status_created
    -> (status, created_at);

mysql> EXPLAIN SELECT * FROM orders
    -> WHERE status = 'shipped' AND created_at > '2026-07-01'
    -> ORDER BY created_at DESC LIMIT 20\G

         type: range
          key: idx_status_created
      key_len: 27
         rows: 23456
     filtered: 100.00
        Extra: Using index condition; Backward index scan

扫描行数从150万降到2.3万，type从ALL变为range，排序走了索引的Backward index scan无需filesort。但Extra中仍有Using index condition（ICP），说明仍需要回表获取SELECT *中的其他列。

如果查询只需要少量列，可以建立覆盖索引：

mysql> ALTER TABLE orders ADD INDEX idx_status_created_covering
    -> (status, created_at, order_id, user_id, amount);

mysql> EXPLAIN SELECT order_id, user_id, amount FROM orders
    -> WHERE status = 'shipped' AND created_at > '2026-07-01'
    -> ORDER BY created_at DESC LIMIT 20\G

         type: range
          key: idx_status_created_covering
        Extra: Using where; Using index

Extra变成Using index，表示索引覆盖了查询，不再回表。对于高频查询，覆盖索引的性能提升可达数倍。

策略二：避免索引失效的常见场景

以下几种写法会导致索引失效：

-- 1. 对索引列使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-03';
-- 修正：范围查询
SELECT * FROM orders
WHERE created_at >= '2026-08-03' AND created_at < '2026-08-04';

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 12345;
-- 如果order_no是VARCHAR类型，MySQL会将12345转为字符串，索引失效
-- 修正：使用引号
SELECT * FROM orders WHERE order_no = '12345';

-- 3. OR条件中部分列无索引
SELECT * FROM orders WHERE status = 'shipped' OR remark LIKE '%urgent%';
-- 修正：用UNION ALL拆分
SELECT * FROM orders WHERE status = 'shipped'
UNION ALL
SELECT * FROM orders WHERE remark LIKE '%urgent%' AND status != 'shipped';

-- 4. 前缀模糊查询
SELECT * FROM orders WHERE order_no LIKE '%20260803%';
-- 修正：如果需要前缀匹配
SELECT * FROM orders WHERE order_no LIKE '20260803%';

InnoDB Buffer Pool调优

Buffer Pool是InnoDB性能的核心参数。默认128MB远不够生产使用。调优目标是让热数据尽量驻留在内存中，减少磁盘读取：

# my.cnf
[mysqld]
innodb_buffer_pool_size = 12G   # 物理内存的60-70%
innodb_buffer_pool_instances = 8  # 多实例减少锁竞争
innodb_buffer_pool_chunk_size = 128M
innodb_old_blocks_time = 1000    # 老数据停留1秒后才可被淘汰

监控Buffer Pool命中率：

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_requests      | 89234567  |
| Innodb_buffer_pool_reads              | 234567    |
+---------------------------------------+-----------+

# 命中率 = 1 - (reads / read_requests)
# 命中率 = 1 - (234567 / 89234567) = 99.74%

命中率低于99%需要增大Buffer Pool或检查是否有大表扫描冲刷了热数据。innodb_old_blocks_time=1000让全表扫描读取的数据页在1秒后才进入老数据区域，避免冷数据冲刷热数据。

连接池与线程缓存优化

高并发场景下，连接创建和线程分配是开销大头。核心参数配置：

# my.cnf
[mysqld]
max_connections = 500
thread_cache_size = 64    # 缓存空闲线程避免反复创建
table_open_cache = 4000  # 打开表缓存
table_definition_cache = 2000

# 连接超时
wait_timeout = 28800
interactive_timeout = 28800
max_allowed_packet = 64M

监控线程缓存效率：

mysql> SHOW GLOBAL STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name      | Value |
+-------------------+-------+
| Threads_cached    | 32    |
| Threads_connected | 45    |
| Threads_created   | 234   |
| Threads_running   | 3     |
+-------------------+-------+

# 如果Threads_created持续增长，说明thread_cache_size不够
# 理想状态：Threads_cached接近thread_cache_size，Threads_created增长缓慢

查询改写的性能对比

同一业务逻辑的不同SQL写法性能差异可能达数十倍。以分页查询为例：

-- 写法1：深分页，LIMIT偏移量大
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- MySQL需要扫描100020行再丢弃前100000行

-- 写法2：游标分页，用上次查询的最大ID作为起点
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;
-- 只需扫描20行

-- 写法3：延迟关联，先查主键再回表
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) tmp ON o.id = tmp.id;
-- 子查询走覆盖索引只查id列，回表只取20行

在100万行数据上测试，写法1耗时2.3秒，写法2耗时0.001秒，写法3耗时0.15秒。游标分页性能最优但要求排序字段连续唯一，延迟关联适用范围更广。

Performance Schema深度诊断

当慢查询日志无法覆盖所有场景时，Performance Schema提供更细粒度的诊断。开启statement和wait事件采集：

-- 开启关键消费者
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES' WHERE NAME IN (
    'events_statements_current',
    'events_statements_history_long',
    'events_waits_current'
);

-- 查询Top 10耗时SQL
SELECT DIGEST_TEXT,
       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,
       SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

定位到具体SQL后，结合sys库的schema_index_statistics视图分析索引使用情况：

SELECT index_name,
       rows_selected,
       rows_inserted,
       rows_updated,
       rows_deleted
FROM sys.schema_index_statistics
WHERE table_schema = 'mydb' AND table_name = 'orders'
ORDER BY rows_selected DESC;

rows_selected为0的索引是未被使用的索引，它只会增加写入开销和存储空间，应当评估是否可以删除。通过这套从慢查询日志到Performance Schema的完整诊断链路，可以系统性地发现和解决MySQL性能瓶颈。