带序列主键的大型MariaDB表INSERT性能优化及相关问题咨询

1. 表增大后，主键唯一性检查导致INSERT变慢是否不可避免？ #

不是完全不可避免，但性能会有不同程度的下滑。核心问题不是唯一性检查本身，而是你当前的主键结构(thing_id DESC, id DESC)带来的写入模式：

• 如果批量插入是不同thing_id交替出现，InnoDB的聚簇索引会频繁跳转到不同的索引块，属于随机写入。随着数据量增大，缓冲池命中率下降，磁盘IO开销飙升，插入耗时会明显上升。
• 如果批量插入能按thing_id分组连续插入，那就是顺序写入，性能下降会平缓很多。
  至于主键唯一性检查，本质是B+树的查找，数据量增大后树高增加会带来一些开销，但远不如随机写入的影响大。

2. 改用AUTO_INCREMENT类型会有性能差异吗？ #

会有非常明显的差异，但得结合主键结构来看：

• 如果把id设为AUTO_INCREMENT，同时将主键改为单字段(id)（InnoDB默认升序，AUTO_INCREMENT升序插入是性能最优的顺序写入），插入时是纯顺序写入（AUTO_INCREMENT的id递增，聚簇索引会连续写入新的索引块），这能极大降低IO开销，批量插入性能会稳定得多。
• 但如果保留原主键(thing_id DESC, id DESC)，哪怕id用AUTO_INCREMENT，插入不同thing_id的数据时依然是随机写入，性能提升有限。
  不过改单字段主键会牺牲“同一thing_id事件聚集”的特性，需要额外创建(thing_id, id DESC)的二级索引来满足你的查询需求——这会增加插入时的索引维护开销，但对于你的查询场景来说，这个 trade-off 是值得的。

3. 适合你业务场景的表结构或策略推荐 #

结合你大量INSERT、按thing_id查最新数据的核心需求，推荐两种方案：

方案一：调整主键与索引，平衡插入与查询 #

• 将id改为AUTO_INCREMENT，主键设为(id)（InnoDB默认升序，AUTO_INCREMENT升序插入是性能最优的顺序写入）。
• 创建覆盖索引(thing_id, id DESC)：如果你的查询是SELECT *，可以把常用字段加入索引（比如(thing_id, id DESC, event_id, ...)），避免回表；如果字段太多，至少用这个索引满足过滤和排序，回表取数据的开销也远小于随机写入的损耗。
  这样插入时是顺序写入聚簇索引，性能稳定；查询时通过二级索引快速定位到指定thing_id的最新N条数据，效率很高。

方案二：按thing_id分表 #

你有2万个不同的thing_id，可以按thing_id的哈希值分表（比如分成64或128张表），每张表只存一部分thing_id的数据。这样单表数据量会大幅降低（2亿行分到100张表，每张才200万行），插入时的索引维护和IO开销都会减少，查询时直接定位到对应分表，效率也更高。

额外优化：批量插入时按thing_id分组 #

不管用哪种方案，批量插入时尽量把同一thing_id的数据放在一起连续插入，减少索引块的跳转，能明显降低插入耗时。

4. 是否应该考虑表分区？ #

非常值得考虑，尤其是你有定期清理旧数据的需求。推荐用RANGE分区：

• 可以按id分区（因为id是递增的，对应插入时间），比如每1000万id一个分区；或者按saved_at按月/按周分区。
• 清理旧数据时直接ALTER TABLE events DROP PARTITION p_old;，这比DELETE操作高效太多，不会产生大量碎片和事务日志。
• 分区后，单分区的数据量更小，插入时的索引维护和IO开销都会降低，查询时如果指定了时间或id范围，还会自动只扫描对应分区，查询效率也会提升。
  注意：如果用saved_at做分区键，主键必须包含saved_at（比如(thing_id DESC, id DESC, saved_at)）；如果用id做分区键，原主键不需要修改，更方便。

5. 能提升INSERT性能的MariaDB配置项 #

• innodb_buffer_pool_size：专用数据库服务器的话，设为物理内存的50%-70%，让大部分索引和数据缓存在内存里，减少磁盘IO。
• innodb_log_file_size：增大到1G-4G（根据服务器内存调整，总日志大小innodb_log_file_size * 2别超过缓冲池的50%），减少日志切换频率，降低批量插入时的日志刷写开销。
• innodb_flush_log_at_trx_commit：如果业务能容忍1秒左右的数据丢失，设为2（每秒刷写日志到磁盘），比默认的1（每次事务提交刷写）性能提升明显；非关键数据批量插入时，甚至可以临时设为0，但风险较高。
• innodb_write_io_threads：调到8-16，提升并发写入的处理能力。
• innodb_autoinc_lock_mode：设为2（交错模式），对批量插入（比如INSERT ... VALUES多值插入）能减少AUTO_INCREMENT锁的持有时间，提升并发插入性能（注意主从同步的话，binlog_format要设为ROW或MIXED）。
• bulk_insert_buffer_size：调到64M-128M，优化批量插入时的二级索引构建速度。

内容的提问来源于stack exchange，提问作者Sokolof