自動インクリメント主キーを使用しない大きな MySQL テーブルへの挿入は非常に遅い
<p>最近、単純な INSERT ステートメントを完了するのに必要な時間の差が大幅に増加していることに気づきました。これらのステートメントには平均で約 11 ミリ秒かかりますが、場合によっては 10 ～ 30 秒かかることもあり、実行に 5 分以上かかる場合もあります。 </p>
<p>MySQL のバージョンは <code>8.0.24</code> で、Windows Server 2016 上で実行されます。私の知る限り、サーバーのリソースが過負荷になったことはありません。サーバーには十分な CPU オーバーヘッドがあり、32 GB の 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、
主キー (`recording_id`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci</pre>
<p>このテーブルには他のインデックスや外部キーがなく、他のテーブルの外部キーへの参照として使用されません。テーブル全体のサイズは約20GB、行数は約281Mとそれほど大きくないと感じます。 </p>
<p>テーブルはほぼ読み取り専用モードで使用され、1 秒あたり最大 1000 回の読み取りが行われます。これらの読み取りはすべて、複雑なトランザクションではなく単純な SELECT クエリで発生し、主キー インデックスを効果的に利用します。このテーブルへの同時書き込みは、たとえあったとしてもごくわずかです。これは、挿入が遅い場合に役立つかどうかを確認するために意図的に行われましたが、効果はありませんでした。それまでは、常に最大 10 個の同時挿入が進行します。 UPDATE または DELETE ステートメントは、このテーブルに対して実行されることはありません。 </p>
<p>私が問題を抱えているクエリはすべてこのように構築されています。これらはトランザクションには決して現れません。クラスター化された主キーに基づく挿入は明らかに追加専用ではありませんが、クエリはほとんどの場合、1 ～ 20 の隣接する行をテーブルに挿入します。
<pre class="brush:php;toolbar:false;">saved_segment に IGNORE を挿入
(recording_id, `index`, start_filetime, end_filetime, offset_and_size, storage_id) 値
(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)
<p>これは、上記のクエリの EXPLAIN ステートメントの出力です。</p>
<テーブルクラス="s-テーブル">
<頭>
<tr>
id
<th>タイプを選択</th>
<th>テーブル</th>
パーティション
タイプ
<th>可能なキー</th>
キー
<th>key_len</th>
<th>参考</th>
OK
<th>フィルタリング済み</th>
<th>エクストラ</th>
</tr>
</頭>
<みんな>
<tr>
1
<td>挿入</td>
<td>保存されたセグメント</td>
<td>空</td>
<td>すべて</td>
<td>空</td>
<td>空</td>
<td>空</td>
<td>空</td>
<td>空</td>
<td>空</td>
<td>空</td>
</tr>
</tbody>
</テーブル>
<p>これらの問題は比較的新しいもので、テーブルが約 2 倍小さかったときには目立たなくなりました。 </p>
<p>テーブルへの同時挿入の数を約 10 から 1 に減らしてみました。また、挿入をさらに高速化するために、一部の列の外部キー (<code>recording_id</code>) も削除しました。 <code>テーブル分析</code>とスキーマ分析では、実用的な情報は得られませんでした。</p>
<p>私が考えた解決策の 1 つは、クラスター化主キーを削除し、自動インクリメント主キーと通常のインデックスを <code>(recording_id,index)</code> 列に追加することでした。私の意見では、これは挿入を「追加専用」にするのに役立ちます。あらゆる提案を歓迎します。よろしくお願いします。 </p>
<p>編集:
コメントと回答で提起されたいくつかの点と質問について説明します。 </p>
-
<li>
autocommit が ON に設定されています。
<li><code>innodb_buffer_pool_size</code> の値は <code>21474836480</code>、<code>innodb_buffer_pool_chunk_size</code> の値は <code>134217728</コード> </ 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;">TRUE を選択
FROM 保存済みセグメント
WHERE Recording_id = ? AND `index` = ?</pre>
<pre class="brush:php;toolbar:false;">SELECT インデックス、start_filetime、end_filetime、offset_and_size、storage_id
FROM 保存済みセグメント
WHERE レコーディング ID = ? AND
start_filetime >= ? かつ
start_filetime <= ?
ORDER BY `index` ASC</pre>
<p>2 番目のタイプのクエリはインデックスを使用して確かに改善できますが、これにより INSERT のパフォーマンスがさらに低下するのではないかと心配しています。 </p>
<p>もう 1 つ言い忘れていたのですが、これとよく似たテーブルが存在します。クエリと挿入はまったく同じですが、さらに IO 不足が発生する可能性があります。 </p>
<p>編集 2:
<code>SHOW TABLE STATUS</code> テーブル <code>saved_segment</code> と非常によく似たテーブル <code>saved_screenshot</code> (<code>bigint unsigned にあります) null</code> 列の追加インデックス)。</p>
<テーブルクラス="s-テーブル">
<頭>
<tr>
<th>名前</th>
