🚀 每天50万条数据写入MySQL？这份高性能写入优化指南请收好！

写多读少？批量插入慢？主键选错？索引拖后腿？
本文从表设计、SQL 写法、配置调优到架构策略，为你系统梳理 MySQL 高频写入场景下的优化之道。

📊 背景：50万/天，真的算多吗？

每天写入 50 万条数据，换算下来平均每秒约 5.8 条。
看起来不多？但如果你的写入集中在白天 8 小时，那峰值可能达到 17 条/秒；若集中在 1 小时，就是近 140 条/秒！

更关键的是——

你是否在用单条 INSERT？
表上是否有5 个以上索引？
是否在用UUID 做主键？
innodb_flush_log_at_trx_commit 是否还是默认值 1？

这些细节，才是压垮 MySQL 写入性能的“隐形杀手”。

🔧 一、表结构优化：从源头减负

1. 主键：用自增，慎用 UUID

AUTO_INCREMENT INT 是写入性能的黄金标准。
若必须用 UUID，建议：
- 使用 UUID_TO_BIN(uuid, true)（MySQL 8.0+）生成有序 UUID；
- 或改用 雪花 ID（Snowflake）；
- 存储类型用 BINARY(16) 而非 VARCHAR(36)。

2. 精简字段，缩小行尺寸

能用 TINYINT 不用 INT；
时间字段优先选 DATETIME（无时区转换开销）；
避免滥用 VARCHAR(255)，按实际长度定义。

💡 行越小，InnoDB 页能存的数据越多，缓存命中率越高，写入越快。

3. 索引：少即是多

每多一个索引，写入就要多一次 B+ 树更新；
删除从未用于查询的索引；
复合索引注意最左前缀原则，避免冗余。

⚡ 二、SQL 写法优化：批量是王道

✅ 正确姿势：批量插入 + 批量事务

-- 一次插入 1000 行（性能提升 10~100 倍！）
INSERT INTO user_logs (user_id, action, created_at)
VALUES 
  (1001, 'login', NOW()),
  (1002, 'click', NOW()),
  ...
  (2000, 'logout', NOW());

✅ 配合事务控制（避免频繁 commit）

1
2
3

START TRANSACTION;
-- 执行 1000~5000 条 INSERT
COMMIT;

📌 建议：每 2000~5000 行提交一次。太大事务会拖慢崩溃恢复，太小则刷盘频繁。

❌ 避免：

单条循环插入（for 循环里一条条 INSERT）；
每条都 COMMIT（默认 autocommit=1）；
在循环中频繁 SELECT 再 UPDATE（考虑合并为 INSERT ... ON DUPLICATE KEY UPDATE）。

⚙️ 三、MySQL 配置调优：让 InnoDB 飞起来

以下参数建议在 专用写入实例 上调整（测试后上线）：

# InnoDB 日志相关（关键！）
innodb_log_file_size = 1G          # 建议 1~2G，提升顺序写能力
innodb_log_buffer_size = 64M       # 减少日志刷盘次数

# 缓冲池（越大越好，占内存 60~70%）
innodb_buffer_pool_size = 4G       # 8G 内存机器可设为 5-6G

# 刷盘策略（权衡安全 vs 性能）
innodb_flush_log_at_trx_commit = 2 # 每秒刷一次日志（可能丢1秒数据）
sync_binlog = 1000                 # 若开启 binlog（主从复制）

# 其他
skip-name-resolve                  # 关闭 DNS 反查，加速连接

⚠️ 注意：innodb_flush_log_at_trx_commit=2 会牺牲强持久性，仅适用于可容忍秒级数据丢失的场景（如日志、埋点、统计）。

🏗️ 四、架构级优化：为未来扩展铺路

当单机瓶颈逼近，考虑以下策略：

1. 读写分离

写走主库，读走从库；
减轻主库压力，提升整体吞吐。

2. 分表（Sharding）

按时间分表（如 logs_202512）；
或按 user_id 哈希分 10 张表；
配合中间件（ShardingSphere、Vitess）或应用层路由。

3. 异步写入：引入消息队列

graph LR
A[应用] -->|发消息| B(Kafka / RabbitMQ / Redis Streams)
B --> C[消费者]
C -->|批量写入| D[MySQL]

削峰填谷，避免突发流量压垮数据库；
提升系统容错性（失败可重试）。

4. 考虑替代引擎？

ClickHouse：适合时序、日志类追加写入；
TiDB：分布式 MySQL，水平扩展；
MongoDB：文档写入灵活，但需权衡一致性。

🎯 但切记：不要为了“高级”而迁移。先榨干 MySQL 的性能！

🛠️ 五、实战建议：FastAPI + Python 用户注意

如果你用 Python（如 FastAPI）写入数据：

# 使用 executemany 批量插入
data = [(1001, 'login'), (1002, 'click'), ...]
cursor.executemany(
    "INSERT INTO logs (user_id, action) VALUES (%s, %s)",
    data
)
connection.commit()

配合：

autocommit=False
定期批量提交（如每 2000 条）
使用连接池（如 aiomysql + async）

✅ 总结：优化 Checklist

优化维度	关键动作
表设计	自增主键、精简字段、删冗余索引
SQL 写法	批量 INSERT + 批量事务
MySQL 配置	调大 `innodb_log_file_size`，设 `flush_log_at_trx_commit=2`
架构	读写分离、分表、消息队列缓冲
监控	关注 `Innodb_log_waits`、IOPS、慢写入