MySQL性能调优

MySQL性能调优需要从数据库设计、查询优化、配置调整、硬件与架构优化等多个维度入手。

一、数据库设计与索引优化

1. 合理设计表结构

• 避免过度冗余：遵循第三范式（3NF）但适当允许冗余以减少JOIN。

• 选择合适字段类型：
◦ 用INT而非VARCHAR存储数字。
◦ 用DATETIME代替VARCHAR存储时间。
◦ 大文本字段使用TEXT并独立成表。

2. 索引优化

• 添加必要索引：

• 避免索引失效：

◦ 不在索引列上使用函数或运算（如WHERE YEAR(create_time) = 2023）。
◦ 遵循最左前缀原则（联合索引按顺序使用）。
◦ 避免LIKE '%keyword%左模糊查询。

• 删除冗余索引：

二、SQL查询优化

1. 避免全表扫描

• 使用EXPLAIN分析执行计划：◦ 关注type列：ALL表示全表扫描，应优化为ref或range。
◦ 检查key列：确认是否命中索引。

2. 优化复杂查询

拆分大查询：将复杂查询拆分为多个简单操作。避免SELECT *：

3. 减少JOIN复杂度

• 优先使用INNER JOIN：减少不必要的数据关联。
• 小表驱动大表：在JOIN时，将数据量小的表放在左侧。

三、配置参数调优

1. InnoDB缓冲池（Buffer Pool）

设置合理大小：通常为物理内存的70%~80%。监控缓冲池命中率：

2. 日志与写入优化

调整日志写入策略：增大日志文件大小：

3. 连接与线程配置

控制最大连接数：

四、架构与硬件优化

1. 读写分离

• 主库处理写操作，从库处理读操作。
• 使用中间件（如MyCat、ProxySQL）或框架（Spring动态数据源）实现自动路由。

2. 分库分表

• 垂直分库：按业务拆分（如订单库、用户库）。
• 水平分表：按哈希或范围拆分（如user_001到user_100）。
• 工具：ShardingSphere、Vitess。

3. 硬件升级

• SSD替代HDD：提升I/O性能。
• 增加内存：扩大缓冲池容量。
• 多核CPU：支持更高并发。

五、监控与诊断工具

1. 慢查询日志使用mysqldumpslow分析慢日志：

2. Performance Schema

监控服务器事件（如锁等待、文件I/O）：

3. pt-query-digest：

分析SQL日志并生成报告：

六、常见场景优化案例

案例1：分页查询优化

低效写法：优化方案：

案例2：统计计数优化

低效写法：优化方案：

七、调优总结

注意事项
1. 避免过度优化：优先解决性能瓶颈最严重的部分。
2. 基准测试：使用sysbench或自定义脚本验证调优效果。
3. 灰度发布：生产环境调整配置时逐步验证。

通过以上策略，可系统性提升MySQL性能，支撑高并发、大数据量场景。实际调优需结合业务特点，持续监控与迭代。