慢查询日志的配置与采集

MySQL性能问题的排查起点永远是慢查询日志。默认情况下慢查询日志是关闭的，生产环境必须开启：

-- my.cnf配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

long_query_time设置为0.5秒而不是常见的2秒或10秒。2秒的阈值在OLTP场景下太粗糙，大量几百毫秒的慢查询会被漏掉，这些查询积少成多同样拖垮数据库性能。

用mysqldumpslow做初步统计：

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

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

pt-query-digest是更专业的分析工具，输出更详细：

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

输出中重点关注Rank 1-5的查询，这些就是调优的优先目标。

EXPLAIN执行计划逐行解读

拿到目标SQL后，第一步是看执行计划：

EXPLAIN SELECT o.id, o.order_no, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID' AND o.created_at > '2026-07-01'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN输出的关键字段：

type列：访问类型，性能从好到差：system > const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，必须消灭。index表示全索引扫描，通常也需要优化。

key列：实际使用的索引。NULL表示没走索引。

rows列：预估扫描行数。这个值和实际差距可能很大，但量级是对的。

Extra列：额外信息。关注这几个值：
– Using filesort：额外排序，消耗CPU和内存
– Using temporary：创建临时表，性能杀手
– Using index：覆盖索引，最佳情况
– Using where：在存储引擎返回数据后过滤

常见问题诊断：

场景1：type=ALL，rows=百万级
缺少索引或索引失效。检查WHERE条件列是否有索引，索引是否因为函数转换、隐式类型转换等原因失效。

场景2：type=ref但rows仍然很大
索引选择性差。例如status列只有3个值（PAID/CANCELLED/PENDING），即使有索引，走ref扫描的行数也很多。这种低选择性列不适合单独建索引，应该和其他列建联合索引。

场景3：Extra出现Using filesort
ORDER BY的列没有走索引，MySQL在拿到数据后再做排序。数据量大时排序可能直接落盘。

索引优化实战案例

案例1：联合索引的列顺序

-- 原始查询
SELECT * FROM orders WHERE user_id = 1001 AND status = 'PAID';

-- 错误索引
CREATE INDEX idx_status_user ON orders(status, user_id);

-- 正确索引
CREATE INDEX idx_user_status ON orders(user_id, status);

联合索引遵循最左前缀原则。user_id的选择性通常远高于status（一个用户可能有几百个订单，但PAID状态的订单可能有百万条）。把选择性高的列放在左边，索引能更快缩小搜索范围。

案例2：覆盖索引消除回表

-- 需要回表的查询
SELECT order_no, amount, created_at FROM orders WHERE user_id = 1001;

-- 覆盖索引
CREATE INDEX idx_user_cover ON orders(user_id, order_no, amount, created_at);

覆盖索引让MySQL直接从索引树获取所有需要的列，不需要回表查主键索引。Extra列会显示Using index。对于高频查询，覆盖索引的优化效果非常显著。

案例3：避免索引失效的常见写法

-- 索引失效：在索引列上使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-29';
-- 改写为范围查询
SELECT * FROM orders
WHERE created_at >= '2026-07-29' AND created_at < '2026-07-30';

-- 索引失效：隐式类型转换
SELECT * FROM orders WHERE user_id = '1001';  -- user_id是int
-- 改写
SELECT * FROM orders WHERE user_id = 1001;

-- 索引失效：LIKE前缀通配符
SELECT * FROM users WHERE name LIKE '%张%';
-- 如果需要前缀匹配
SELECT * FROM users WHERE name LIKE '张%';

JOIN优化策略

多表JOIN是慢查询的高发区。核心原则：小表驱动大表，确保被驱动表的JOIN条件有索引。

-- 驱动表是users（小），被驱动表是orders（大）
-- 确保orders.user_id有索引
SELECT u.name, COUNT(*) order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id;

MySQL的Nested Loop Join机制决定了被驱动表每次都要根据驱动表的一行数据做索引查找。如果被驱动表的JOIN列没有索引，每次查找变成全表扫描，复杂度直接从O(N log M)变成O(N * M)。

对于三个以上表的JOIN，优先考虑拆分。拆成两个查询加应用层关联，性能往往好过三表JOIN。这不是偷懒，是务实的工程选择。

参数调优的关键配置

索引优化做完后，MySQL参数调优是第二道防线。

[mysqld]
# InnoDB缓冲池，建议占物理内存的60-70%
innodb_buffer_pool_size = 8G

# 日志文件大小，增大减少checkpoint频率
innodb_log_file_size = 1G

# 每次事务的刷盘策略
# 1最安全但最慢，2是折中方案，0最快但崩溃可能丢1秒数据
innodb_flush_log_at_trx_commit = 2

# 并发线程数，CPU核心数的2-4倍
innodb_thread_concurrency = 32

# 排序缓冲区，ORDER BY时使用
sort_buffer_size = 4M

# JOIN缓冲区，无索引JOIN时使用
join_buffer_size = 4M

innodb_buffer_pool_size是最重要的参数。如果缓冲池太小，热点数据频繁换入换出，磁盘I/O会成为瓶颈。8G的缓冲池对于128G内存的服务器来说偏保守，但比默认的128M强太多。

MySQL性能调优的逻辑：慢查询日志定位问题SQL，EXPLAIN分析执行计划，索引优化消灭全表扫描，参数调优消除系统瓶颈。四步走下来，80%以上的性能问题都能解决。剩下20%的复杂场景，可能需要考虑分库分表或引入缓存层。