MySQL性能优化实战经验

MySQL性能优化是每个后端开发工程师的必备技能。本文将从索引优化、查询优化、配置调优等多个方面分享MySQL性能优化的实战经验。

索引优化

1. 合理创建索引

索引是提升查询性能的关键，但也不能滥用：

为WHERE、JOIN、ORDER BY中频繁使用的列创建索引
避免冗余索引，定期检查并清理无用索引
利用复合索引的最左前缀原则

2. 使用EXPLAIN分析执行计划

EXPLAIN SELECT * FROM users WHERE age > 25 AND city = 'Shanghai';

-- 关注以下字段：
-- type: 连接类型，从好到差：system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引
-- rows: 扫描的行数
-- Extra: 额外信息，关注 Using filesort, Using temporary

3. 覆盖索引

当查询的所有列都在索引中时，MySQL可以直接从索引中获取数据，无需回表：

-- 假设有索引 idx_name_age(name, age)
SELECT name, age FROM users WHERE name = '张三';
-- 这个查询可以完全通过索引完成，不需要回表

查询优化

1. 避免SELECT *

只查询需要的列，减少数据传输量：

-- 不推荐
SELECT * FROM orders WHERE user_id = 100;

-- 推荐
SELECT order_id, amount, status FROM orders WHERE user_id = 100;

2. 合理使用JOIN

在某些场景下，JOIN比子查询性能更好：

-- 子查询（可能较慢）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Shanghai');

-- JOIN（通常更快）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.city = 'Shanghai';

3. 分页优化

大偏移量的LIMIT查询性能很差：

-- 慢查询（需要扫描100010行）
SELECT * FROM articles ORDER BY id LIMIT 100000, 10;

-- 优化方案：使用游标分页
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 10;

配置调优

关键参数

innodb_buffer_pool_size：InnoDB缓冲池大小，建议设置为物理内存的60-70%
innodb_log_file_size：redo log文件大小，影响写入性能
max_connections：最大连接数，根据实际并发量设置
query_cache_size：查询缓存大小（MySQL 8.0已移除）

慢查询分析

开启慢查询日志，找出需要优化的SQL：

-- 开启慢查询日志
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

总结

MySQL性能优化是一个系统工程，需要从多个层面综合考虑：

合理设计索引，利用EXPLAIN分析执行计划
优化SQL查询，避免全表扫描
调整数据库配置参数
定期监控和分析慢查询
根据业务场景选择合适的优化策略