<p>我最近注意到完成简单 INSERT 语句所需的时间差异显着增加。虽然这些语句平均需要大约 11 毫秒，但有时可能需要 10-30 秒，我什至注意到它们的执行时间超过 5 分钟。</p> <p>MySQL版本是<code>8.0.24</code>，运行在Windows Server 2016上。据我所知，服务器的资源从未过载。服务器有充足的 CPU 开销可供使用，并为其分配了 32GB 的 RAM。</p> <p>这是我正在使用的表：</p> <pre class="brush:php;toolbar:false;">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</pre> <p>该表没有其他索引或外键，也不用作任何其他表中外键的引用。整个表大小约为 20GB，行数约为 281M，我觉得这并不算太大。</p> <p>该表几乎完全以只读方式使用，每秒读取次数高达 1000 次。所有这些读取都发生在简单的 SELECT 查询中，而不是在复杂的事务中，并且它们有效地利用了主键索引。对此表的并发写入（如果有的话）非常少。这样做是有意为之，目的是试图弄清楚它是否有助于缓慢插入，但事实并非如此。在此之前，始终有最多 10 个并发插入在进行。永远不会在此表上执行 UPDATE 或 DELETE 语句。</p> <p>我遇到问题的查询都是这样构造的。它们从未出现在交易中。虽然根据聚集主键插入绝对不是仅追加的，但查询几乎总是将 1 到 20 个相邻行插入到表中：</p> <pre class="brush:php;toolbar:false;">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)</pre> <p>这是上述查询的 EXPLAIN 语句的输出：</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>选择类型</th> <th>表</th> <th>分区</th> <th>类型</th> <th>可能的键</th> <th>键</th> <th>key_len</th> <th>参考</th> <th>行</th> <th>已过滤</th> <th>额外</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>插入</td> <td>已保存的段</td> <td>空</td> <td>全部</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> <td>空</td> </tr> </tbody> </table> <p>这些问题是相对较新的问题，并且在桌子大约小两倍时并不明显。</p> <p>我尝试减少表中的并发插入数量，从大约 10 个减少到 1 个。我还删除了某些列上的外键 (<code>recording_id</code>)，以进一步加快插入速度。 <code>分析表</code> 和模式分析没有产生任何可操作的信息。</p> <p>我想到的一个解决方案是删除聚集主键，在 <code>(recording_id, index)</code> 列上添加自动增量主键和常规索引。在我看来，这将有助于使插入“仅附加”。我愿意接受任何和所有建议，提前致谢！</p> <p>编辑： 我将解决评论和答案中提出的一些观点和问题：</p> <ul> <li><code>autocommit</code> 设置为 <code>ON</code></li> <li><code>innodb_buffer_pool_size</code> 的值为 <code>21474836480</code>，<code>innodb_buffer_pool_chunk_size</code> 的值为 <code>134217728</code></li> <li>一条评论提出了对读取所使用的读锁与写入所使用的排它锁之间的争用的担忧。该表的使用有点像缓存，我不需要读取来始终反映表的最新状态，如果这意味着性能的提高。然而，即使在服务器崩溃和硬件故障的情况下，该表也应该保持耐用。这可以通过更宽松的事务隔离级别来实现吗？</li> <li>架构绝对可以优化； <code>recording_id</code> 可以是 4 字节整数，<code>end_filetime</code> 可以改为经过的值，并且 <code>start_filetime</code> 也可能更小。恐怕这些更改只会将问题推迟一段时间，直到表的大小增大以补偿节省的空间。</li> <li>对表的插入始终是连续的 在表上执行的 SELECT 如下所示：</li> </ul> <pre class="brush:php;toolbar:false;">SELECT TRUE FROM saved_segment WHERE recording_id = ? AND `index` = ?</pre> <pre class="brush:php;toolbar:false;">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</pre> <p>第二种类型的查询肯定可以通过索引来改进，但我担心这会进一步降低 INSERT 性能。</p> <p>我忘记提及的另一件事是存在一个与此非常相似的表。它的查询和插入方式完全相同，但可能会进一步导致 IO 饥饿。</p> <p>编辑2： <code>SHOW TABLE STATUS</code> 表 <code>saved_segment</code> 的结果，以及一个非常相似的表 <code>saved_screenshot</code> （该表在 <code>bigint unsigned not null</code> 列上有一个附加索引）。</p> <table class="s-table"> <thead> <tr> <th>姓名</th> <th>引擎</th> <th>版本</th> <th>行格式</th> <th>行</th> <th>平均行长度</th> <th>数据长度</th> <th>最大数据长度</th> <th>Index_length</th> <th>无数据</th> <th>自动增量</th> <th>创建时间</th> <th>更新时间</th> <th>检查时间</th> <th>整理</th> <th>校验和</th> <th>创建选项</th> <th>评论</th> </tr> </thead> <tbody> <tr> <td>已保存的屏幕截图</td> <td>InnoDB</td> <td>10</td> <td>动态</td> <td>483430208</td> <td>61</td> <td>29780606976</td> <td>0</td> <td>21380464640</td> <td>6291456</td> <td>空</td> <td>“2021-10-21 01:03:21”</td> <td>“2022-11-07 16:51:45”</td> <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> <td></td> <td></td> </tr> <tr> <td>已保存的段</td> <td>InnoDB</td> <td>10</td> <td>动态</td> <td>281861164</td> <td>73</td> <td>20802699264</td> <td>0</td> <td>0</td> <td>4194304</td> <td>空</td> <td>“2022-11-02 09:03:05”</td> <td>“2022-11-07 16:51:22”</td> <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> <td></td> <td></td> </tr> </tbody> </table></p>

(recording_id, index)

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

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

PRIMARY KEY(recording_id, index)

INDEX(recording_id, start_filetime)

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