MySQL性能优化实战：从慢查询到索引设计的完整方法论

数据库往往是web应用的最大性能瓶颈。一个未经优化的MySQL查询可能比优化后慢100倍以上。本文不讲理论，只讲实战中真正有效的优化手段。

1. 打开慢查询日志——优化的起点

没有数据就没有优化方向。MySQL的慢查询日志是所有性能优化的出发点。

在my.cnf中配置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

将阈值设为0.5秒，比默认的10秒敏感得多。超过0.5秒的查询在用户体验上已经能感受到延迟。同时记录未使用索引的查询，这些是潜在的优化目标。

然后用pt-query-digest分析慢查询日志：

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

这个工具会自动按查询指纹分组、按总耗时排序，告诉你哪些查询最值得优化。通常20%的查询类型消耗了80%的时间，优先优化这些。

2. 索引设计的黄金法则

索引是MySQL优化中最有效的手段，但错误的索引比没有索引更糟糕。

最左前缀原则：联合索引(a, b, c)可以加速查询条件为a、a+b、a+b+c的情况，但不能加速b或b+c。设计联合索引时把等值查询列放前面、范围查询列放后面。

覆盖索引：如果查询的所有列都在索引中，MySQL不需要回表查询数据行，性能提升数倍到数十倍。比如SELECT name, age FROM users WHERE email = ?，建一个(email, name, age)的联合索引即可覆盖。

避免在索引列上做函数运算：WHERE DATE(created_at) = '2026-07-12'会使索引失效。应改为WHERE created_at >= '2026-07-12' AND created_at < '2026-07-13'。

EXPLAIN解读要点：

type从好到差：system > const > eq_ref > ref > range > index > ALL。出现ALL意味着全表扫描。
key：实际使用的索引。看是否和你的预期一致。
rows：MySQL估计需要扫描的行数。这个数字越小越好。
Extra：Using index（覆盖索引，最优）> Using where > Using filesort（需要排序，有大问题）> Using temporary（需要临时表，大问题）。

3. 查询重写技巧

很多时候改一下SQL写法比加索引更有效。

用IN替代OR：MySQL对IN的优化远好于OR。WHERE id IN (1,2,3) 比 WHERE id=1 OR id=2 OR id=3 快得多。

LIMIT大偏移量优化：LIMIT 100000, 20 实际上扫描了100020行然后丢弃前100000行。改为延迟关联：先SELECT id FROM table ORDER BY id LIMIT 100000, 20，然后再SELECT * FROM table WHERE id IN (...)。

COUNT优化：COUNT(*) 在有非空索引时会走索引统计，很快。但COUNT(col)需要检查列值是否为NULL，在大量数据时更慢。

避免SELECT *：只取需要的列。减少了网络传输和内存占用，也为使用覆盖索引创造了可能。

4. 配置调优

innodb_buffer_pool_size是MySQL最重要的配置参数，应该设置为物理内存的70-80%。一台16G内存的服务器，buffer pool设置为12G。

innodb_log_file_size影响写入性能。设置为buffer pool的25-50%。写密集场景下更大的日志文件减少checkpoint频率。

join_buffer_size和sort_buffer_size不要盲目调大。这两个参数是每个连接分配的，调太大在高并发下反而导致内存不足。

5. 监控与持续优化

用Performance Schema监控热点查询和锁等待。开启performance_schema=ON后可以查询events_statements_summary_by_digest找出最耗时的SQL指纹。

用InnoDB Monitor检查buffer pool命中率。命中率低于95%说明buffer pool太小或查询太分散。

数据库优化不是一次性工程，而是持续迭代的过程。伴随着数据增长和业务变化，需要定期回顾和调整。

MySQL性能优化实战：从慢查询到索引设计的完整方法论