MySQL数据库性能调优实战指南

前言

数据库性能直接影响应用的响应速度和用户体验。本文将分享MySQL性能调优的实战经验，从配置优化、SQL优化、索引设计等多个维度，帮助你打造高性能的MySQL数据库。

一、性能监控与分析

1.1 慢查询日志

# 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

1.2 EXPLAIN分析

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

重点关注：

• type：访问类型（ALL>index>range>ref>eq_ref>const）
• key：实际使用的索引
• rows：扫描的行数
• Extra：额外信息（Using filesort, Using temporary）

1.3 SHOW PROFILE

SET profiling = 1;
SELECT * FROM large_table;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

二、索引优化

2.1 索引设计原则

好的索引：

• 区分度高的列（基数大）
• 经常出现在WHERE、JOIN、ORDER BY中的列
• 覆盖索引（避免回表）

避免：

• 过多的索引
• 重复索引
• 低基数列建索引

2.2 联合索引最左前缀

-- 索引 (a, b, c)
-- 有效：a | a,b | a,b,c
-- 无效：b | c | b,c
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

2.3 索引优化技巧

-- 前缀索引
CREATE INDEX idx_email ON users(email(10));

-- 覆盖索引
CREATE INDEX idx_name_email ON users(name, email);

-- 强制索引
SELECT * FROM users USE INDEX(idx_name) WHERE name = '张三';

三、SQL语句优化

3.1 SELECT优化

避免：

-- 避免SELECT *
SELECT * FROM users;

-- 避免在字段上用函数
WHERE DATE(create_time) = '2022-01-01';

-- 避免LIKE前导通配
WHERE name LIKE '%张三%';

推荐：

-- 只查询需要的列
SELECT id, name, email FROM users;

-- 函数放到右边
WHERE create_time >= '2022-01-01' AND create_time < '2022-01-02';

-- 后导通配可以用索引
WHERE name LIKE '张三%';

3.2 JOIN优化

• 小表驱动大表
• JOIN字段建立索引
• 减少JOIN表数量（不超过3-4个）

3.3 分页优化

-- 传统分页（越往后越慢）
SELECT * FROM articles ORDER BY id LIMIT 10000, 10;

-- 优化方案
SELECT * FROM articles WHERE id > 10000 ORDER BY id LIMIT 10;

-- 延迟关联
SELECT a.* FROM articles a 
INNER JOIN (SELECT id FROM articles ORDER BY id LIMIT 10000, 10) b 
ON a.id = b.id;

四、配置优化（my.cnf）

4.1 内存相关

# InnoDB缓冲池（建议物理内存50-70%）
innodb_buffer_pool_size = 4G

# 日志缓冲区
innodb_log_buffer_size = 64M

# 排序缓冲区
sort_buffer_size = 4M

# 连接缓冲区
join_buffer_size = 4M

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

4.2 连接相关

# 最大连接数
max_connections = 500

# 连接超时
wait_timeout = 600
interactive_timeout = 600

4.3 InnoDB优化

# 每个表独立表空间
innodb_file_per_table = 1

# 日志文件大小
innodb_log_file_size = 256M

# 刷新日志策略（1最安全，2最高性能）
innodb_flush_log_at_trx_commit = 1

# 刷新方法
innodb_flush_method = O_DIRECT

# 并发线程数
innodb_thread_concurrency = 8

五、表结构优化

5.1 数据类型选择

• 最小化原则：能用TINYINT不用INT
• 避免NULL：NOT NULL + DEFAULT
• 整数优先：IP地址存INT，枚举用TINYINT
• 避免TEXT/BLOB：尽量VARCHAR

5.2 字段类型建议

5.3 分库分表策略

• 水平分表：按时间、ID范围、哈希
• 垂直分表：冷热数据分离
• 读写分离：主从复制，读库扩展

六、架构优化

6.1 读写分离

主库（写） → 从库1（读）
            → 从库2（读）
            → 从库3（读）

6.2 缓存策略

• 应用层缓存：Redis缓存热点数据
• 查询缓存：MySQL 8.0已移除，不推荐
• 对象缓存：ORM层缓存

6.3 连接池

• 合理设置连接池大小
• 监控连接使用率
• 避免连接泄漏

七、常用优化工具

7.1 运维工具

# 配置检查
mysqltuner

# 性能分析
pt-query-digest slow.log

# 压力测试
sysbench --test=oltp --mysql-user=root run

7.2 监控指标

• QPS/TPS
• 连接数
• 缓存命中率
• 慢查询数量
• 锁等待时间

八、优化检查清单

✅ 开启慢查询日志并定期分析 ✅ 所有查询都正确使用索引 ✅ 避免SELECT *和全表扫描 ✅ 合理设置buffer pool大小 ✅ 定期OPTIMIZE和ANALYZE TABLE ✅ 监控关键性能指标 ✅ 有备份和容灾方案

结语

MySQL性能调优是一个持续迭代的过程。没有银弹，需要根据实际业务场景不断测量、分析、优化。记住：先优化SQL和索引，再调配置，最后考虑架构扩展。

优化的目标不是极致性能，而是在成本、复杂度和性能之间找到最佳平衡点。