自動インクリメント主キーを使用しない大きな MySQL テーブルへの挿入は非常に遅い

Question

<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> エンジン バージョン <th>行フォーマット</th> OK <th>平均ライン長</th> データ長 <th>最大データ長</th> <th>Index_length</th> <th>データなし</th> <th>自動インクリメント</th> 作成時間 <th>更新</th> <th>時刻を確認</th> <th>組織</th> <th>チェックサム</th> <th>作成オプション</th> <th>コメント</th> </tr> </頭> <みんな> <tr> <td>保存されたスクリーンショット</td> <td>InnoDB</td> 10 <td>ニュース</td> <td>483430208</td> <td>61</td> 29780606976 0 21380464640 <td>6291456</td> <td>空</td> 「2021-10-21 01:03:21」 「2022-11-07 16:51:45」 <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> </tr> <tr> <td>保存されたセグメント</td> <td>InnoDB</td> 10 <td>ニュース</td> <td>281861164</td> <td>73</td> <td>20802699264</td> 0 0 <td>4194304</td> <td>空</td> 「2022-11-02 09:03:05」 「2022-11-07 16:51:22」 <td>空</td> <td>utf8mb4_0900_ai_ci</td> <td>空</td> <td></td> <td></td> </tr> </tbody> </table></p>

Answer 1

私はこの回答で大胆に答えます。

＃＃＃予測＃＃＃

innodb_buffer_pool_size
• の値は 20MB よりわずかに小さい、 および then
• 1 秒あたり 1K の選択がテーブルのランダムな部分に到着します。
最近、システムは I/O バウンドになっています。これは、次の Select に必要な「次の」ブロックが、buffer_pool にキャッシュされないことが多くなっているためです。

簡単な解決策は、RAM を増やして、この調整パラメータの設定を増やすことです。ただし、テーブルは次に購入する制限までしか拡大しません。

代わりに、部分的な解決策をいくつか紹介します。

数値が大きすぎない場合、最初の 2 列は

INT UNSIGNED
• (8 バイトではなく 4 バイト)、または MEDIUMINT UNSIGNED (3 バイト) になる場合もあります。 ALTER TABLE はテーブルを長時間ロックすることに注意してください。 これらの開始時間と終了時間は、小数秒を含むタイムスタンプのように見え、常に「.000」です。
DATETIME
• と TIMESTAMP は (8 バイトではなく) 5 バイトを必要とします。 この例では、経過時間が 0 と表示されています。通常、(終了-開始) が非常に小さい場合、終了時間の代わりに経過時間を保存すると、データがさらに圧縮されます。 (ただし、終了時刻を使用すると、状況が混乱する可能性があります)。
• 提供されたサンプル データは「連続」しているように見えます。これは自動インクリメントとほぼ同じくらい効率的です。これは標準ですか?そうでない場合、INSERT は I/O スラッシングの一部である可能性があります。
• 二次インデックスだけでなく人工知能も追加することを提案していますが、これにより挿​​入作業が 2 倍になるため、お勧めしません。
• ＃＃＃＃＃＃もっと＃＃＃＃＃＃
はい、その通りです。

これを INDEX として、またはさらに良いのは

PRIMARY KEY

の先頭に置くと、両方のクエリに対して最も役立ちます:

リーリー ＃＃＃返事：＃＃＃ リーリー

他の SQL を制御するために使用されている場合は、他の SQL に追加することを検討してください。 リーリー このクエリ (どちらの形式でも) には、すでに持っているコンテンツが必要です リーリー その他のお問い合わせニーズ リーリー

したがって、インデックス、

または

...

を追加します。

より良い...この組み合わせは

両方にとってより良いです

SELECT

:

リーリー この組み合わせでは、

単一行の存在チェックは「カバーされている」ため、「インデックスを使用して」実行されます。 別のクエリでは、PK 上でクラスター化されたすべての関連行が検索されます。 (PK には一意である必要があるため、これら 3 つの列があります。これらをこの順序で配置すると、2 番目のクエリに役立ちます。また、これは単なる INDEX ではなく PK であるため、BTree 間のインデックスのバウンスに含まれる必要はありません。および BTree のデータ。)

「クラスター」

は、そのようなクエリに必要なディスク ブロックの数を減らすことで、パフォーマンスを

向上させることができます。これにより、buffer_pool 内の「スラッシング」が減少し、RAM を増やす必要性が減ります。
私のインデックスの提案は、データ型の提案とほぼ直交しています。