无自增主键的大型MySQL表INSERT操作速度极慢求助

问题现象 #

近期发现saved_segment表的简单INSERT语句执行时间波动极大：平均耗时约11ms，但有时会达到10-30秒，甚至超过5分钟。该问题在表规模增长到当前2.81亿行（约20GB）后出现，表规模为当前一半时无此现象。

环境与表信息 #

基础环境 #

• MySQL版本：8.0.24
• 操作系统：Windows Server 2016
• 服务器资源：32GB内存，CPU有剩余，资源充足

表结构 #

CREATE TABLE `saved_segment` (
  `recording_id`        bigint unsigned NOT NULL,
  `index`               bigint unsigned NOT NULL,
  `start_filetime`      bigint unsigned NOT NULL,
  `end_filetime`        bigint unsigned NOT NULL,
  `offset_and_size`     bigint unsigned NOT NULL DEFAULT '18446744073709551615',
  `storage_id`          tinyint unsigned NOT NULL,
  PRIMARY KEY (`recording_id`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• 表无其他索引或外键，也未被其他表作为外键引用
• 表以只读操作为主：每秒最多1000次SELECT查询，均通过主键索引高效执行
• 写入特征：几乎无并发写入（已从10个并发INSERT降至1个），无UPDATE/DELETE操作；INSERT为INSERT IGNORE批量插入1-20条相邻行，非主键顺序追加，且不在事务内执行

问题INSERT语句示例 #

INSERT IGNORE INTO saved_segment
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) VALUES
(19173, 631609, 133121662986640000, 133121663016640000, 20562291758298876, 10),
(19173, 631610, 133121663016640000, 133121663046640000, 20574308942546216, 10),
(19173, 631611, 133121663046640000, 133121663076640000, 20585348350688128, 10),
(19173, 631612, 133121663076640000, 133121663106640000, 20596854568114720, 10),
(19173, 631613, 133121663106640000, 133121663136640000, 20609723363860884, 10),
(19173, 631614, 133121663136640000, 133121663166640000, 20622106425668780, 10),
(19173, 631615, 133121663166640000, 133121663196640000, 20634653501528448, 10),
(19173, 631616, 133121663196640000, 133121663226640000, 20646967172721148, 10),
(19173, 631617, 133121663226640000, 133121663256640000, 20657773176227488, 10),
(19173, 631618, 133121663256640000, 133121663286640000, 20668825200822108, 10)

EXPLAIN结果 #

已尝试的优化操作 #

• 将并发INSERT数量从10降至1，问题未解决
• 删除recording_id上的外键
• 执行ANALYZE TABLE和schema分析，未获得有效信息

补充信息 #

• 数据库参数：autocommit=ON；innodb_buffer_pool_size=21474836480（20GB）；innodb_buffer_pool_chunk_size=134217728（128MB）
• 表定位：类似缓存，无需读取强一致性，但需保证崩溃/硬件故障时的数据持久性
• 表结构可调整（如recording_id改为4字节整数），但担心仅推迟问题出现时间
• SELECT语句类型：

  -- 类型1：主键查询
  SELECT  TRUE
  FROM    saved_segment
  WHERE   recording_id = ? AND `index` = ?
  

  -- 类型2：范围查询
  SELECT  index, start_filetime, end_filetime, offset_and_size, storage_id
  FROM    saved_segment
  WHERE   recording_id    = ? AND
          start_filetime >= ? AND
          start_filetime <= ?
  ORDER   BY `index` ASC
  

• 存在同类型表saved_screenshot，可能加剧IO资源竞争
• SHOW TABLE STATUS结果：

可行优化建议 #

1. 重构聚簇主键（核心优化） #

InnoDB的聚簇主键与数据行存储在一起，非顺序插入会触发频繁的页分裂，表越大页分裂的IO代价越高，这是导致INSERT耗时波动的核心原因。建议：

-- 1. 添加自增主键作为聚簇索引
ALTER TABLE saved_segment ADD COLUMN `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);
-- 2. 为原主键创建唯一索引，保证查询效率
ALTER TABLE saved_segment ADD UNIQUE KEY `uk_recording_index` (`recording_id`, `index`);

• 优势：自增主键是追加式插入，彻底避免页分裂，大幅提升INSERT的稳定性和速度；唯一索引的查询效率接近主键，对只读为主的场景影响极小。

2. 调整InnoDB写入相关参数 #

• 调大redo日志文件大小：当前默认的innodb_log_file_size可能过小，导致频繁切换日志文件引发IO波动。建议设置为2GB（Windows环境下避免过大）：

  innodb_log_file_size = 2G
  

  注意：修改后需要重启MySQL，且重启前需正常关闭数据库，避免日志损坏。
• 优化刷盘策略：Windows环境下设置innodb_flush_method=async_unbuffered，提升日志刷盘效率；开启innodb_adaptive_flushing=ON，让InnoDB根据负载动态调整脏页刷写节奏，避免突发IO压力。
• 缓冲池调优：当前缓冲池20GB，而saved_segment数据20GB + saved_screenshot索引21GB，总数据量超过缓冲池，导致缓存命中率下降。建议将innodb_buffer_pool_size调整为24GB（服务器有32GB内存，预留足够系统资源）：

  innodb_buffer_pool_size = 24G
  

3. 优化INSERT语句 #

• 移除INSERT IGNORE：如果业务上能保证无主键冲突（当前几乎无并发写入，插入相邻行），直接使用INSERT替代INSERT IGNORE，避免额外的冲突检查开销。
• 合并事务：当前autocommit=ON，每次INSERT都是独立事务，会触发频繁的日志刷盘。建议将多个批量INSERT合并为一个事务：

  BEGIN;
  INSERT INTO saved_segment (...) VALUES (...);
  INSERT INTO saved_segment (...) VALUES (...);
  COMMIT;
  

  减少事务提交次数，降低日志刷盘的IO开销。

4. 调整事务隔离级别 #

表作为缓存无需强一致性，可将事务隔离级别从默认的REPEATABLE READ改为READ COMMITTED：

SET GLOBAL transaction_isolation = 'READ-COMMITTED';
SET SESSION transaction_isolation = 'READ-COMMITTED';

• 优势：减少InnoDB的间隙锁和幻读锁开销，提升写入性能；不影响崩溃后的持久性（持久性由redo日志保证）。

5. 表结构与存储优化 #

• 缩小字段类型：如果recording_id的最大值远小于2^32-1，将其改为INT UNSIGNED（4字节）；同理index若无需8字节存储，也改为INT UNSIGNED，减少每行存储空间和索引占用，降低IO压力。
• 启用行压缩：对于只读为主的表，启用COMPRESSED行格式，减少磁盘IO消耗（CPU有剩余，可承受压缩开销）：

  ALTER TABLE saved_segment ROW_FORMAT=COMPRESSED;
  

  可配合调整innodb_compression_level=6（默认值，平衡压缩比和CPU消耗）。

6. 缓解IO竞争 #

• 将saved_segment和saved_screenshot的数据文件放在不同的物理磁盘上，避免IO资源竞争；若使用SSD，优先选择NVMe SSD，提升随机IO性能。
• 用Windows性能监视器监控磁盘队列长度、平均响应时间，若响应时间超过20ms，说明磁盘IO存在瓶颈，需升级存储硬件。

7. 定期维护表 #

• 清理表碎片：非顺序插入会产生大量表碎片，定期在业务低峰期执行ALTER TABLE saved_segment ENGINE=InnoDB;（Online DDL，MySQL 8.0支持）重建表，减少碎片，提升IO效率。

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