不同业务场景下的MySQL数据库面临的压力类型不同，比如电商订单系统需要应对高并发写入，数据分析系统需要处理大量复杂查询，因此优化方案不能一概而论，需要结合场景特征针对性调整。
一、读写密集场景的优化方案
1. 读密集场景优化
读密集场景常见于内容展示、商品详情页等场景，核心优化方向是减少数据库的直接查询压力。
首先可以通过合理的索引设计提升查询效率，遵循最左前缀原则，避免冗余索引。比如用户查询商品时常用分类ID和价格区间筛选，可以建立联合索引(category_id, price)。
其次可以引入查询缓存，MySQL自带查询缓存功能，适合查询重复率高的场景，配置方式如下：
-- 开启查询缓存 SET GLOBAL query_cache_type = 1; -- 设置查询缓存大小为256M SET GLOBAL query_cache_size = 268435456;
另外可以采用读写分离架构，将读请求分发到多个从库，主库仅处理写请求，提升整体读吞吐量。
2. 写密集场景优化
写密集场景常见于订单创建、日志上报等场景，需要减少写入时的锁竞争和IO压力。
首先可以调整事务提交策略，非核心业务可以适当降低事务隔离级别，比如从可重复读调整为读已提交，减少间隙锁的使用。
其次可以批量写入数据，避免单条插入的高频交互，示例代码如下：
// 批量插入订单数据，减少数据库连接交互次数
public void batchInsertOrders(List<Order> orderList) {
String sql = "INSERT INTO orders (order_id, user_id, amount) VALUES (?, ?, ?)";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Order order = orderList.get(i);
ps.setString(1, order.getOrderId());
ps.setLong(2, order.getUserId());
ps.setBigDecimal(3, order.getAmount());
}
@Override
public int getBatchSize() {
return orderList.size();
}
});
}
还可以关闭非必要的索引，写入时索引的维护会消耗额外性能，对于写入后很少查询的字段可以暂时去掉索引，写入完成后再重建。
二、大数据量场景的优化方案
1. 单表数据量过大优化
当单表数据量超过千万级时，查询和写入性能都会明显下降，此时优先考虑分表方案。
如果是按时间维度增长的业务，比如操作日志表，可以采用按时间分表的策略，比如每个月生成一张表，表名格式为operation_log_202401。
如果是按用户ID等维度拆分的业务，可以采用哈希分表，比如将用户ID对8取模，拆分为8张表，查询时根据取模结果定位到对应的表。
2. 历史数据归档优化
对于只需要保留近期数据的业务，比如订单表只需要保留近1年的数据，可以将更早的历史数据归档到归档库，减少主库的数据量。
归档操作可以通过定时任务执行，示例SQL如下：
-- 将2022年及之前的订单数据归档到归档表 INSERT INTO orders_archive SELECT * FROM orders WHERE create_time < '2023-01-01 00:00:00'; -- 确认归档完成后删除主表历史数据 DELETE FROM orders WHERE create_time < '2023-01-01 00:00:00';
三、高并发场景的优化方案
1. 连接数优化
高并发场景下容易出现数据库连接数不足的问题，需要调整MySQL的最大连接数配置，修改my.cnf文件：
[mysqld] # 最大连接数设置为1000，根据业务实际并发调整 max_connections = 1000 # 连接超时时间设置为300秒 wait_timeout = 300
同时需要在应用层使用数据库连接池，避免频繁创建和销毁连接，比如Java项目中使用Druid连接池，设置合理的初始连接数和最大连接数。
2. 热点数据优化
高并发场景下容易出现热点行竞争，比如秒杀活动中单个商品的库存行被大量更新请求竞争，此时可以在应用层采用分布式锁或者缓存预扣减库存的方式，减少数据库的直接竞争。
如果必须直接操作数据库，可以将热点行的更新操作合并，比如将多次减库存操作合并为一次批量更新，减少行锁持有时间。
四、通用性能优化建议
除了针对场景的优化，还有一些通用的优化方法适用大部分业务：
- 定期分析慢查询日志，通过
EXPLAIN命令分析查询执行计划，优化不合理的查询语句 - 合理选择字段类型，比如能用INT就不用BIGINT，能用VARCHAR(32)就不用VARCHAR(255)，减少存储和IO开销
- 避免select * 查询，只查询需要的字段，减少数据传输量
- 定期优化表，对于频繁更新的表可以执行
OPTIMIZE TABLE命令整理碎片
优化MySQL性能是一个持续的过程，需要先通过监控工具定位瓶颈，再结合业务场景选择合适的优化方案，避免盲目优化造成不必要的成本投入。