MySQL性能问题的排查起点：慢查询日志

数据库慢了，不要猜，先开慢查询日志。这是性能诊断的事实数据源，靠经验猜测只会浪费时间。MySQL 8.0默认未开启慢查询日志，需要手动配置：

-- 在线开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;     -- 超过0.5秒记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 没用索引也记录
SET GLOBAL min_examined_row_limit = 100;  -- 扫描少于100行不记录

-- 持久化到配置文件 /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

mysqldumpslow是慢查询日志的快速分析工具：

# 按查询时间排序，取前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 按查询次数排序（找出热点查询）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

pt-query-digest比mysqldumpslow更强大，能生成详细的查询分析报告：

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

EXPLAIN执行计划：读懂MySQL的查询路径

拿到慢SQL后，第一步不是加索引，是读懂执行计划：

EXPLAIN ANALYZE 
SELECT o.order_id, o.amount, u.user_name
FROM orders o 
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01' 
  AND o.status = 'PAID'
ORDER BY o.amount DESC
LIMIT 20;

EXPLAIN ANALYZE（MySQL 8.0.18+）比传统EXPLAIN多了实际执行时间，是真正的性能数据而非优化器估算。关键字段解读：

type列——访问类型，从好到差排序：

– system/const：单行查找，主键或唯一索引等值查询
– eq_ref：JOIN时使用主键或唯一索引
– ref：非唯一索引等值查询
– range：索引范围扫描（BETWEEN, >, <）
– index：全索引扫描
– ALL：全表扫描，必须优化

Extra列——额外信息，重点关注的几种：

– Using index：覆盖索引，不回表，性能最优
– Using where：在存储引擎返回数据后过滤，说明有未走索引的条件
– Using filesort：额外排序，无法利用索引顺序
– Using temporary：使用了临时表，GROUP BY或DISTINCT的常见代价
– Using index condition：索引下推（ICP），MySQL 5.6+的优化

rows列——预估扫描行数。filtered列是过滤比例。实际返回行数 = rows × filtered%。如果rows很大但filtered很低，说明扫描了大量无用数据，索引不够精准。

索引优化的四个实战场景

场景1：复合索引的列顺序

最左前缀原则决定了复合索引的列顺序。选择性高的列放前面：

-- 查询条件：WHERE status = 'PAID' AND created_at > '2024-01-01'
-- status选择性低（几种状态），created_at选择性高（每天不同值）

-- 差索引：status在前，只用到status的等值过滤
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- 对比：创建时间在前
-- 但这样的话status条件无法利用索引
-- 正确做法：根据实际查询条件组合决定

-- 用EXPLAIN验证
EXPLAIN SELECT * FROM orders 
WHERE status = 'PAID' AND created_at > '2024-01-01';
-- 如果idx_status_created的key_len只包含status（4字节），
-- 说明created_at没走索引，需要调整或创建新索引

场景2：覆盖索引避免回表

-- 查询只需要order_id和amount
SELECT order_id, amount FROM orders WHERE status = 'PAID';

-- 覆盖索引：所有查询列都在索引中
ALTER TABLE orders ADD INDEX idx_cover_paid (status, order_id, amount);

-- EXPLAIN结果中Extra列应该显示"Using index"
-- 不需要回表查主键索引，IO量大幅减少

场景3：ORDER BY的索引优化

-- 按创建时间倒序分页
SELECT * FROM orders WHERE user_id = 123 
ORDER BY created_at DESC LIMIT 10;

-- 索引要包含排序列
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at DESC);

-- MySQL 8.0支持降序索引，创建时指定DESC
-- 这样ORDER BY可以直接利用索引顺序，无需filesort

场景4：深度分页优化

-- 传统分页：越往后越慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
-- 扫描100010行，丢掉前100000行

-- 方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- 只扫描10行，但要求页码连续

-- 方案2：延迟JOIN
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 10) tmp
ON o.id = tmp.id;
-- 子查询走覆盖索引，只取10个id再回表
-- 速度提升5-50倍取决于表大小

MySQL 8.0参数调优：InnoDB核心配置

[mysqld]
# 缓冲池大小 - 物理内存的60-70%（专用数据库服务器）
innodb_buffer_pool_size = 12G

# 缓冲池实例数 - 每个实例至少1GB
innodb_buffer_pool_instances = 12

# 日志文件大小 - 影响checkpoint频率
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

# 刷新策略 - 根据数据安全级别选择
# 1 = 每次事务提交刷盘（最安全，最慢）
# 2 = 每次提交写日志，每秒刷盘（折中）
# 0 = 每秒刷盘（最快，崩溃可能丢1秒数据）
innodb_flush_log_at_trx_commit = 1

# 并发线程控制
innodb_thread_concurrency = 0    # 0=自动（推荐8.0+）
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 自适应哈希索引 - 等值查询多时开启
innodb_adaptive_hash_index = ON

# Change Buffer - 非唯一二级索引的插入优化
innodb_change_buffering = all
innodb_change_buffer_max_size = 25

buffer_pool_size的精细化调优：不要一次性设满。留出内存给操作系统文件缓存和临时表。观察Buffer Pool Wait指标，如果经常出现等待，说明不够大。如果Buffer Pool Read（磁盘读）比例低于1%，当前大小已经足够。

-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests / 
-- (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)
-- 应该 > 99.9%

线上诊断工具箱

查看当前正在执行的SQL

SELECT * FROM information_schema.PROCESSLIST 
WHERE Command != 'Sleep' AND Time > 5
ORDER BY Time DESC;

-- MySQL 8.0+用performance_schema更精准
SELECT * FROM performance_schema.events_statements_current
WHERE TIMER_WAIT/1000000000000 > 5;  -- 超过5秒

查看锁等待

SELECT 
  r.trx_id AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_id AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  r.trx_query AS waiting_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

InnoDB状态监控

SHOW ENGINE INNODB STATUS\G
-- 关注几个段落：
-- LATEST DETECTED DEADLOCK：最近死锁
-- TRANSACTIONS：事务状态
-- BUFFER POOL AND MEMORY：缓冲池使用情况
-- ROW OPERATIONS：行操作统计

MySQL性能调优是数据驱动的工程问题。用慢查询日志找到瓶颈，用EXPLAIN分析路径，用索引优化消除全表扫描，用参数调优压出硬件极限。每一步都有数据支撑，不要靠猜。