⚙️ MySQL 性能调优

🐌 慢查询日志

慢查询日志是发现性能问题的第一步，记录执行时间超过阈值的 SQL。

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

-- 查看当前状态
SHOW VARIABLES LIKE 'slow%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

收集到慢查询日志后，使用 pt-query-digest 进行聚合分析：

pt-query-digest /var/log/mysql/slow.log | head -100

🔍 EXPLAIN 详解

EXPLAIN 是分析 SQL 执行计划的核心工具，必须掌握。

EXPLAIN SELECT * FROM articles a
JOIN users u ON a.author_id = u.id
WHERE a.status = 2 AND a.created_at > '2025-01-01'\G

关键字段说明

字段	含义	好的值
type	访问类型	const > eq_ref > ref > range > index > ALL
key	实际使用的索引	非 NULL，且是预期索引
key_len	索引使用字节数	越大越完整
rows	预估扫描行数	越小越好
Extra	额外信息	Using index（覆盖索引）最佳

⚡ 索引优化

覆盖索引

查询的所有字段都在索引中，无需回表，性能极佳。

-- 假设有 INDEX(status, created_at, title)
SELECT title, created_at FROM articles
WHERE status = 2
ORDER BY created_at DESC;
-- Extra: Using index（覆盖索引，不回表）✅

索引失效的常见场景

-- ❌ 对索引列做函数运算
WHERE YEAR(created_at) = 2025
-- ✅ 改为范围查询
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31'

-- ❌ 隐式类型转换（id 是 INT，传了字符串）
WHERE id = '123'
-- ✅ 类型匹配
WHERE id = 123

-- ❌ LIKE 左模糊
WHERE title LIKE '%MySQL%'
-- ✅ 右前缀可用索引
WHERE title LIKE 'MySQL%'

✍️ SQL 优化技巧

分页优化

-- ❌ 深翻页性能差（扫描 100010 行）
SELECT * FROM articles ORDER BY id LIMIT 100000, 10;

-- ✅ 游标分页（记录上次最大 ID）
SELECT * FROM articles
WHERE id > 100000          -- 上次结果的最后 id
ORDER BY id LIMIT 10;

-- ✅ 子查询延迟关联（先拿主键，再 JOIN 取数据）
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY id LIMIT 100000, 10) t
ON a.id = t.id;

IN vs EXISTS

-- 外表小、子查询大 → 用 EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM articles a WHERE a.author_id = u.id);

-- 外表大、子查询小 → 用 IN
SELECT * FROM articles
WHERE author_id IN (SELECT id FROM users WHERE level = 'admin');

🔧 关键参数调优

参数	建议值	说明
innodb_buffer_pool_size	物理内存的 60~70%	InnoDB 最重要的缓存，直接影响性能
innodb_log_file_size	256M ~ 1G	redo log 大小，影响写入性能和恢复时间
max_connections	500 ~ 2000	根据应用连接数合理设置
innodb_flush_log_at_trx_commit	1（安全）/ 2（性能）	1=每次提交刷盘，2=每秒刷盘
sync_binlog	1	开启 binlog 同步，保证主从一致
query_cache_size	0（关闭）	MySQL 8.0 已移除，5.7 建议关闭

✅ 性能优化清单

分类	检查项
索引	WHERE/JOIN/ORDER BY 列是否有合适索引
索引	是否存在冗余或未使用的索引
SQL	是否有 SELECT *（尽量按需取列）
SQL	分页是否有深翻页问题
SQL	子查询是否可改写为 JOIN
配置	innodb_buffer_pool_size 是否合理
硬件	磁盘是否为 SSD，网络延迟是否正常
架构	是否引入读写分离/缓存层（Redis）