插入到没有自动增量主键的大型 MySQL 表中非常慢

Question

我最近注意到完成简单 INSERT 语句所需的时间差异显着增加。虽然这些语句平均需要大约 11 毫秒，但有时可能需要 10-30 秒，我什至注意到它们的执行时间超过 5 分钟。

MySQL版本是8.0.24，运行在Windows Server 2016上。据我所知，服务器的资源从未过载。服务器有充足的 CPU 开销可供使用，并为其分配了 32GB 的 RAM。

这是我正在使用的表：

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

该表没有其他索引或外键，也不用作任何其他表中外键的引用。整个表大小约为 20GB，行数约为 281M，我觉得这并不算太大。

该表几乎完全以只读方式使用，每秒读取次数高达 1000 次。所有这些读取都发生在简单的 SELECT 查询中，而不是在复杂的事务中，并且它们有效地利用了主键索引。对此表的并发写入（如果有的话）非常少。这样做是有意为之，目的是试图弄清楚它是否有助于缓慢插入，但事实并非如此。在此之前，始终有最多 10 个并发插入在进行。永远不会在此表上执行 UPDATE 或 DELETE 语句。

我遇到问题的查询都是这样构造的。它们从未出现在交易中。虽然根据聚集主键插入绝对不是仅追加的，但查询几乎总是将 1 到 20 个相邻行插入到表中：

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 语句的输出：

id	选择类型	表	分区	类型	可能的键	键	key_len	参考	行	已过滤	额外
1	插入	已保存的段	空	全部	空	空	空	空	空	空	空

这些问题是相对较新的问题，并且在桌子大约小两倍时并不明显。

我尝试减少表中的并发插入数量，从大约 10 个减少到 1 个。我还删除了某些列上的外键 (recording_id)，以进一步加快插入速度。 分析表 和模式分析没有产生任何可操作的信息。

我想到的一个解决方案是删除聚集主键，在 (recording_id, index) 列上添加自动增量主键和常规索引。在我看来，这将有助于使插入“仅附加”。我愿意接受任何和所有建议，提前致谢！

编辑： 我将解决评论和答案中提出的一些观点和问题：

• autocommit 设置为 ON
• innodb_buffer_pool_size 的值为 21474836480，innodb_buffer_pool_chunk_size 的值为 134217728
• 一条评论提出了对读取所使用的读锁与写入所使用的排它锁之间的争用的担忧。该表的使用有点像缓存，我不需要读取来始终反映表的最新状态，如果这意味着性能的提高。然而，即使在服务器崩溃和硬件故障的情况下，该表也应该保持耐用。这可以通过更宽松的事务隔离级别来实现吗？
• 架构绝对可以优化； recording_id 可以是 4 字节整数，end_filetime 可以改为经过的值，并且 start_filetime 也可能更小。恐怕这些更改只会将问题推迟一段时间，直到表的大小增大以补偿节省的空间。
• 对表的插入始终是连续的 在表上执行的 SELECT 如下所示：

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

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

第二种类型的查询肯定可以通过索引来改进，但我担心这会进一步降低 INSERT 性能。

我忘记提及的另一件事是存在一个与此非常相似的表。它的查询和插入方式完全相同，但可能会进一步导致 IO 饥饿。

编辑2： SHOW TABLE STATUS 表 saved_segment 的结果，以及一个非常相似的表 saved_screenshot （该表在 bigint unsigned not null 列上有一个附加索引）。

姓名	引擎	版本	行格式	行	平均行长度	数据长度	最大数据长度	Index_length	无数据	自动增量	创建时间	更新时间	检查时间	整理	校验和	创建选项	评论
已保存的屏幕截图	InnoDB	10	动态	483430208	61	29780606976	0	21380464640	6291456	空	“2021-10-21 01:03:21”	“2022-11-07 16:51:45”	空	utf8mb4_0900_ai_ci	空
已保存的段	InnoDB	10	动态	281861164	73	20802699264	0	0	4194304	空	“2022-11-02 09:03:05”	“2022-11-07 16:51:22”	空	utf8mb4_0900_ai_ci	空

Answer 1

我会带着这个答案冒险。

假设

• innodb_buffer_pool_size 的值略小于 20MB，并且
• 每秒 1K 个选择随机到达表的各个部分，然后

系统最近变得受 I/O 限制，因为下一个 Select 所需的“下一个”块越来越经常不缓存在 buffer_pool 中。

简单的解决方案是获取更多 RAM 并提高该可调参数的设置。但表格只会增长到您购买的下一个限制。

相反，这里有一些部分解决方案。

• 如果数字不太大，前两列可能是 INT UNSIGNED（4 个字节而不是 8），甚至可能是 MEDIUMINT UNSIGNED（3 个字节） ）。注意 ALTER TABLE 会长时间锁定表。
• 这些开始和结束时间看起来像带有小数秒的时间戳，且始终为“.000”。 DATETIME 和 TIMESTAMP 占用 5 个字节（而不是 8 个字节）。
• 您的示例显示经过时间为 0。如果 (end-start) 通常非常小，那么存储经过时间而不是结束时间会进一步缩小数据。 （但是使用结束时间会让事情变得混乱）。
• 您提供的示例数据看起来“连续”。这与自动增量的效率差不多。这是常态吗？如果不是，INSERT 可能是 I/O 抖动的一部分。
• 您建议添加人工智能以及二级索引，这使得插入的工作量增加了一倍；所以我不推荐它。

更多

是的，情况就是这样。

将其作为 INDEX，或者更好的是，作为 PRIMARY KEY 的开头，可以为您的两个查询提供最佳帮助：

(recording_id, index)

回复：

SELECT  TRUE
FROM    saved_segment
WHERE   recording_id = ? AND `index` = ?

如果它用于控制其他一些 SQL，请考虑将其添加到其他 SQL 中：

... EXISTS ( SELECT 1
        FROM    saved_segment
        WHERE   recording_id = ? AND `index` = ? ) ...

该查询（无论哪种形式）都需要您已有的内容

PRIMARY KEY(recording_id, index)

您的其他查询需求

INDEX(recording_id, start_filetime)

所以，添加索引，或...

更好...这种组合对于两者来说都更好SELECT：

PRIMARY KEY(recording_id, start_filetime, index).
INDEX(recording_id, index)

有了这个组合，

• 单行存在性检查将“使用索引”执行，因为它是“覆盖”。
• 另一个查询将找到在 PK 上聚集在一起的所有相关行。
• （PK 有这 3 列，因为它需要是唯一的。它们按这个顺序排列有利于您的第二个查询。而且它是 PK，而不仅仅是一个 INDEX，因此它不需要在索引的 BTree 之间反弹和数据的 BTree。）
• “集群”可以通过减少此类查询所需的磁盘块数量来提高性能。这会减少 buffer_pool 中的“颠簸”，从而减少增加 RAM 的需要。
• 我的索引建议大部分与我的数据类型建议正交。