MySQL数据库优化实战总结

目录

1. ​硬件与配置优化
   1.1 服务器硬件选型
   1.2 关键参数调优
   1.3 存储引擎选择

2. ​架构设计优化
   2.1 读写分离方案
   2.2 缓存层设计
   2.3 分库分表策略

3. ​SQL与索引优化
   3.1 慢查询日志分析
   3.2 执行计划解读
   3.3 索引设计陷阱

4. ​事务与锁优化
   4.1 事务隔离级别
   4.2 死锁检测与预防

5. ​监控与维护方案
   5.1 性能监控工具
   5.2 数据归档策略

---

1. 硬件与配置优化

1.1 服务器硬件选型

• ​案例：某电商平台QPS从2000提升至8000的硬件升级方案
  原配置：机械硬盘+32G内存
  优化后：NVMe SSD+128G内存，innodb_buffer_pool_size调整为96G
  效果：订单查询响应时间从120ms降至35ms

1.2 关键参数调优

ini

# my.cnf核心配置
innodb_flush_log_at_trx_commit = 2  # 非金融场景降低持久化频率
sync_binlog = 1000                  # 批量提交二进制日志
query_cache_type = OFF              # 高并发场景禁用查询缓存

1.3 存储引擎选择

• InnoDB适合事务型业务（订单系统）
• MyISAM适合读密集型场景（数据报表）

---

2. 架构设计优化

2.1 读写分离方案

• ​实战案例：某社交平台采用ProxySQL实现自动分流
  读请求分发到3个从库，写操作直连主库，查询吞吐量提升3倍

2.2 缓存层设计

sql

-- 使用Redis缓存热点数据
SELECT * FROM products WHERE id = ? 
-- 改造为：
$product = redis->get('product_123');
if(!$product){
    $product = db->query("SELECT * FROM products WHERE id=123");
    redis->setex('product_123', 300, $product);
}

2.3 分库分表策略

• ​水平分表示例：用户表按user_id%128拆分
  原表3亿数据，拆分后单表数据量降至230万，查询速度提升20倍

---

3. SQL与索引优化

3.1 慢查询日志分析

sql

# 开启慢查询监控
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

3.2 执行计划解读

sql

EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 AND status = 'PAID'\G

-- 重点关注：
-- type: range/index/all
-- key: 使用索引
-- rows: 扫描行数

3.3 索引设计陷阱

• ​失效案例：某物流系统联合索引设计错误
  错误索引：(province, city, district)
  实际查询：WHERE city='北京' AND district='海淀'
  优化方案：改为(city, district, province)

---

4. 事务与锁优化

4.1 事务隔离级别

• ​案例：金融系统采用RC级别避免幻读
  原RR级别下转账操作存在锁等待，调整后TPS提升40%

4.2 死锁检测与预防

sql

SHOW ENGINE INNODB STATUS;  -- 查看死锁日志

-- 典型死锁场景：
-- 事务1：UPDATE A → UPDATE B
-- 事务2：UPDATE B → UPDATE A
-- 解决方案：统一操作顺序

---

5. 监控与维护方案

5.1 性能监控工具

• Prometheus + Grafana监控体系
• Percona Toolkit关键指标：

  bash

  pt-query-digest /var/log/mysql/slow.log

5.2 数据归档策略

• ​案例：某CRM系统历史数据归档方案
  使用pt-archiver工具，将3年前订单归档到历史库
  原表数据量从1.2亿降至300万，索引体积减少75%

---

结语

MySQL优化需要从架构设计、SQL编写、参数调优多维度着手。建议定期进行：

1. 每季度执行一次全量SQL审计
2. 每月检查索引使用率（统计index_not_used_count）
3. 每周分析慢查询日志
   通过持续优化，某在线教育平台将数据库整体性能提升了6倍，服务器成本降低40%。优化永无止境，需建立长效监控机制。