MySQL 性能优化最佳实践

为什么需要性能优化

随着数据量的增长，数据库查询性能会逐渐下降。合理的优化可以显著提升应用响应速度，改善用户体验。

索引优化

1. 创建合适的索引

-- 为经常查询的字段创建索引
CREATE INDEX idx_user_email ON users(email);

-- 复合索引
CREATE INDEX idx_order_user_status ON orders(user_id, status);

-- 唯一索引
CREATE UNIQUE INDEX idx_user_username ON users(username);

2. 避免索引失效

-- 避免在索引列上使用函数
-- 不好的写法
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 好的写法
SELECT * FROM users WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

-- 避免隐式类型转换
-- 不好的写法（如果 user_id 是整数）
SELECT * FROM users WHERE user_id = '123';

-- 好的写法
SELECT * FROM users WHERE user_id = 123;

查询优化

1. 使用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

2. 避免 SELECT *

-- 不好的写法
SELECT * FROM users WHERE id = 1;

-- 好的写法
SELECT id, username, email FROM users WHERE id = 1;

3. 优化分页查询

-- 深度分页性能差
SELECT * FROM articles ORDER BY id DESC LIMIT 1000000, 10;

-- 使用覆盖索引优化
SELECT * FROM articles 
WHERE id >= (SELECT id FROM articles ORDER BY id DESC LIMIT 1000000, 1)
ORDER BY id DESC LIMIT 10;

配置优化

重要配置参数

# my.cnf

# InnoDB 缓冲池大小（建议设置为物理内存的 50-75%）
innodb_buffer_pool_size = 4G

# 连接数
max_connections = 500

# 查询缓存（MySQL 8.0 已移除）
# query_cache_type = 1
# query_cache_size = 64M

# 临时表大小
tmp_table_size = 64M
max_heap_table_size = 64M

# 日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

监控和分析

使用 Performance Schema

-- 查看慢查询
SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

总结

MySQL 性能优化是一个持续的过程，需要结合实际情况进行调整。建议定期进行性能监控，及时发现和解决问题。