MySQL バッチ SQL 挿入パフォーマンス最適化の詳細な説明

大量のデータを扱うシステムの中には、クエリ効率の低さやデータの保存時間の長さといったデータベースの問題を抱えているものがあります。特にレポート システムの場合、データのインポートにかかる時間は 1 日に数時間、場合によっては 10 時間以上に及ぶこともあります。したがって、データベース挿入パフォーマンスを最適化することは理にかなっています。

MySQL innodb でいくつかのパフォーマンス テストを行った後、挿入効率を向上できるいくつかの方法を見つけました。参考までに。

1. 1 つの SQL ステートメントで複数のレコードを挿入します。

よく使用される挿入ステートメントは次のとおりです。

`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('0'、'userid_0'、'content_0'、0); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('1'、'userid_1'、'content_1'、1); 

変更後:

`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('0'、'userid_0'、'content_0'、0)、('1'、'userid_1'、'content_1'、1); 

挿入操作を修正すると、プログラムの挿入効率が向上します。ここで 2 番目の SQL の実行効率が高い主な理由は、マージ後のログの量 (MySQL の binlog と innodb のトランザクションがログを作成する) が削減され、ログのフラッシュの量と頻度が削減され、効率が向上したためです。 SQL 文をマージすることで、SQL 文の解析回数を減らし、ネットワーク転送の IO を削減できます。

以下は、単一データのインポートとインポート用の SQL ステートメントへの変換で、それぞれ 100、1,000、10,000 のデータ レコードをテストしたテスト比較データです。

2. トランザクション内で挿入処理を実行します。

挿入を次のように変更します。

トランザクションを開始します。 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('0'、'userid_0'、'content_0'、0); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('1'、'userid_1'、'content_1'、1); 
... 
専念; 

トランザクションを使用すると、データ挿入の効率が向上します。これは、INSERT 操作を実行するときに、MySQL が内部的にトランザクションを確立し、実際の挿入処理操作がトランザクション内で実行されるためです。トランザクションを使用すると、トランザクションの作成コストを削減でき、すべての挿入は実行後にコミットされます。

ここでは、レコード数が 100、1,000、10,000 の場合にトランザクションを使用しない場合とトランザクションを使用する場合のテスト比較も提供されます。

3. データが順番に挿入されます。

順序付きデータ挿入とは、挿入されたレコードが主キーに基づいて順序付けられることを意味します。たとえば、datetime はレコードの主キーです。

`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('1'、'userid_1'、'content_1'、1); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('0'、'userid_0'、'content_0'、0); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('2'、'userid_2'、'content_2'、2); 

変更後:

`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('0'、'userid_0'、'content_0'、0); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('1'、'userid_1'、'content_1'、1); 
`insert_table` に INSERT します (`datetime`、`uid`、`content`、`type`) 
  値 ('2'、'userid_2'、'content_2'、2); 

データベースはデータを挿入するときにインデックス データを維持する必要があるため、順序が正しくないレコードがあるとインデックスの維持コストが増加します。 innodb が使用する B+tree インデックスを参照できます。各レコードがインデックスの末尾に挿入されると、インデックスの配置効率が非常に高くなり、インデックスの調整は小さくなります。挿入されたレコードがインデックスの途中にある場合、B+tree を分割して結合する必要があり、これによりコンピューティング リソースがさらに消費され、挿入されたレコードのインデックス配置効率が低下します。データ量が大きい場合、ディスク操作が頻繁に発生します。

以下は、100、1,000、10,000、100,000、100 万のレコードを使用したランダム データとシーケンシャル データのパフォーマンス比較を示しています。

テスト結果から、最適化方法のパフォーマンスは向上しましたが、その改善はあまり明白ではありません。

総合的なパフォーマンステスト:

ここでは、上記の 3 つの方法を使用して INSERT 効率を最適化するテストを示します。

テスト結果から、データ量が少ない場合、データ+トランザクションをマージする方法のパフォーマンス向上が明らかであることがわかります。データ量が大きい場合（1000万以上）は、パフォーマンスが急激に低下します。これは、この時点でデータ量がinnodb_bufferの容量を超えるためです。各インデックス配置には、より多くのディスク読み取りおよび書き込み操作が含まれ、パフォーマンスが急激に低下します。データ + トランザクション + 順序付けされたデータをマージする方法は、データ量が数千万以上に達した場合でも、依然として良好なパフォーマンスを発揮します。データ量が大きい場合、順序付けされたデータのインデックス配置はより便利であり、ディスク上での頻繁な読み取りおよび書き込み操作を必要としないため、より高いパフォーマンスを維持できます。

注記：

1. SQL 文には長さの制限があります。同じ SQL でデータをマージする場合、SQL 文の長さは SQL の長さ制限を超えてはなりません。これは、max_allowed_pa​​cket 構成で変更できます。デフォルト値は 1M ですが、テスト中に 8M に変更されました。

2. トランザクションのサイズを制御する必要があります。トランザクションが大きすぎると、実行効率に影響する可能性があります。 MySQL には innodb_log_buffer_size 設定項目があります。この値を超えると、innodb データがディスクにフラッシュされ、効率が低下します。したがって、データがこの値に達する前にトランザクションをコミットする方がよい方法です。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。