MySQL在高并发写入场景下，常出现写入延迟高、数据库连接数耗尽、吞吐量下降等问题，需要通过多层面的优化方案提升整体写入性能，适配高并发业务的需求。
一、架构层面优化
1.1 分库分表
当单表数据量过大或者单个数据库实例的写入压力过大时，可以采用分库分表的方式分散写入压力。常见的分表策略有按时间范围分表、按业务ID哈希分表等。
以用户行为日志表为例，按月份进行分表，建表语句示例如下：
-- 创建2024年1月的行为日志表
CREATE TABLE user_action_log_202401 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
action_type VARCHAR(50) NOT NULL,
action_time DATETIME NOT NULL,
action_content TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建2024年2月的行为日志表
CREATE TABLE user_action_log_202402 (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL,
action_type VARCHAR(50) NOT NULL,
action_time DATETIME NOT NULL,
action_content TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1.2 读写分离
搭建一主多从的MySQL集群，主库负责处理所有的写入请求，从库负责处理查询请求，将写入压力集中在主库，同时避免查询操作占用主库的写入资源。主从同步采用异步复制方式，降低写入操作的等待时间。
二、表结构与索引优化
2.1 选择合适的主键
主键尽量选择自增的整数类型，避免使用UUID等无序字符串作为主键，减少B+树页分裂的概率，提升插入性能。如果使用业务ID作为主键，需要确保其有序性。
2.2 减少不必要的索引
索引会提升查询速度，但会增加写入时的维护成本，每次写入数据都需要更新对应的索引树。因此需要删除表中无用的索引，只保留必要的查询索引。可以通过SHOW INDEX FROM 表名查看表的索引情况，删除冗余索引。
删除冗余索引的示例：
-- 删除user_action_log_202401表上无用的idx_action_time索引 DROP INDEX idx_action_time ON user_action_log_202401;
三、参数配置优化
调整MySQL的配置文件my.cnf中的相关参数，适配高并发写入场景：
- innodb_buffer_pool_size：设置为物理内存的60%-80%，让更多的数据可以在内存中操作，减少磁盘IO。
- innodb_log_file_size：适当调大，减少redo log的刷盘频率，提升写入性能，一般设置为1G-2G。
- innodb_flush_log_at_trx_commit：如果业务可以容忍极小概率的数据丢失，设置为2，减少每次事务提交时的刷盘操作，提升写入速度。
- max_connections：根据业务并发量调整，避免连接数不足导致写入失败，一般设置为1000-2000。
参数配置示例：
[mysqld] innodb_buffer_pool_size = 12G innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 2 max_connections = 1500
四、写入方式优化
4.1 批量写入
避免单条数据逐条写入，将多条写入操作合并为批量写入，减少与数据库的交互次数，降低网络开销和SQL解析开销。批量插入的示例：
-- 批量插入用户行为日志，一次插入10条数据 INSERT INTO user_action_log_202401 (user_id, action_type, action_time, action_content) VALUES (1001, 'login', '2024-01-01 10:00:00', '用户登录'), (1002, 'click', '2024-01-01 10:00:05', '点击首页banner'), (1003, 'order', '2024-01-01 10:00:10', '提交订单'), (1004, 'pay', '2024-01-01 10:00:15', '完成支付'), (1005, 'logout', '2024-01-01 10:00:20', '用户退出'), (1006, 'login', '2024-01-01 10:00:25', '用户登录'), (1007, 'search', '2024-01-01 10:00:30', '搜索商品'), (1008, 'add_cart', '2024-01-01 10:00:35', '添加商品到购物车'), (1009, 'collect', '2024-01-01 10:00:40', '收藏商品'), (1010, 'share', '2024-01-01 10:00:45', '分享商品链接');
4.2 控制事务范围
尽量缩小事务的范围，避免长事务占用数据库连接和锁资源。非必要的操作不要放在事务中，事务提交后及时释放资源。同时避免使用大事务，大事务会导致undo log膨胀，影响写入性能。
合理事务控制示例：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BatchInsertDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://127.0.0.1:3306/test_db?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "test_pwd";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DriverManager.getConnection(url, username, password);
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
String sql = "INSERT INTO user_action_log_202401 (user_id, action_type, action_time, action_content) VALUES (?, ?, ?, ?)";
pstmt = conn.prepareStatement(sql);
// 批量插入100条数据
for (int i = 0; i < 100; i++) {
pstmt.setLong(1, 1000L + i);
pstmt.setString(2, "test_action");
pstmt.setString(3, "2024-01-01 10:00:00");
pstmt.setString(4, "测试动作" + i);
pstmt.addBatch();
// 每50条执行一次批量插入
if (i % 50 == 49) {
pstmt.executeBatch();
conn.commit();
}
}
// 提交剩余未提交的数据
pstmt.executeBatch();
conn.commit();
} catch (SQLException e) {
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
} finally {
try {
if (pstmt != null) {
pstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.3 使用LOAD DATA INFILE导入数据
如果需要导入大量存量数据，使用LOAD DATA INFILE方式比普通的INSERT语句快很多，它是MySQL提供的专门用于批量导入数据的命令，直接读取文件内容写入表中，跳过SQL解析等步骤。
使用示例如下：
-- 从本地文件/data/log_data.txt导入数据到user_action_log_202401表 LOAD DATA LOCAL INFILE '/data/log_data.txt' INTO TABLE user_action_log_202401 FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (user_id, action_type, action_time, action_content);
五、其他优化技巧
- 避免使用外键约束，外键会在写入时进行额外的校验，增加写入开销，可以在业务层实现数据一致性校验。
- 对于非核心的写入数据，可以采用异步写入的方式，先将数据写入消息队列，再由消费者异步批量写入MySQL，削峰填谷，降低数据库的瞬时写入压力。
- 定期清理表中的无用数据，避免表数据量过大影响写入性能，可以使用分区表的方式定期删除旧分区数据。