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

大量のデータを含む一部のシステムでは、データベースはクエリ効率が低いだけでなく、データの保存時間が長くなるという問題にも直面しています。特にレポート システムの場合、データのインポートに費やす時間が毎日数時間から 10 時間以上かかる場合があります。したがって、データベース挿入のパフォーマンスを最適化することは理にかなっています。

推奨: "mysql チュートリアル "

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

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

よく使用される挿入ステートメント (

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

など) は、次のように変更されます。

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0), (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>

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

ここでは、単一のデータをインポートしてインポート用の SQL ステートメントに変換するテスト比較データと、それぞれ 100、1,000、および 10,000 個のデータ レコードをテストするテスト比較データを示します。

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

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

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>...
COMMIT;

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

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

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

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

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;2&#39;, &#39;userid_2&#39;, &#39;content_2&#39;,2);<br/>

は次のように変更されます。

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;0&#39;, &#39;userid_0&#39;, &#39;content_0&#39;, 0);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) <br/>    VALUES (&#39;1&#39;, &#39;userid_1&#39;, &#39;content_1&#39;, 1);<br/>INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('2', 'userid_2', 'content_2',2);

データベースは挿入時にインデックス データを維持する必要があるため、レコードが不規則であるとインデックスの維持コストが増加します。 InnoDB が使用する B ツリー インデックスを参照できます。挿入された各レコードがインデックスの最後にある場合、インデックスの配置効率は非常に高く、インデックスの調整は小さくなります。挿入されたレコードがインデックスの途中にある場合、インデックスの調整は小さくなります。の場合、B ツリーを分割してマージする必要があります。処理を待機すると、より多くのコンピューティング リソースが消費され、挿入されたレコードのインデックス配置効率が低下します。データ量が多い場合、ディスク操作が頻繁に発生します。

以下に、ランダム データとシーケンシャル データのパフォーマンスの比較を示します。これらは、それぞれ 100、1000、10000、100000、100 万として記録されます。

テスト結果から、この最適化手法のパフォーマンスは向上していますが、その向上はそれほど明らかではありません。

4. 包括的なパフォーマンス テスト

ここでは、INSERT 効率を最適化するために、上記の 3 つの方法を同時に使用するテストを提供します。

テスト結果から、データ量が少ない場合、データ トランザクションをマージする方法のパフォーマンスの向上が明らかであることがわかります。大きい (1,000 万以上) と、パフォーマンスが急激に低下します。これは、この時点でデータ量が innodb_buffer の容量を超えているためです。インデックスを配置するたびに、より多くのディスク読み取りおよび書き込み操作が発生し、パフォーマンスが急速に低下します。マージされたデータ トランザクションを使用してデータを並べ替える方法は、データ ボリュームが数千万に達した場合でも良好に実行されます。データ ボリュームが大きい場合、順序付けされたデータ インデックスの配置はより便利であり、インデックスに対する頻繁な読み取りおよび書き込み操作は必要ありません。ディスクなので、高いパフォーマンスを維持できます。

注:

1. SQL ステートメントには長さの制限があります。データをマージするときは、同じ SQL 内で SQL の長さ制限を超えてはなりません。変更できます。 max_allowed_pa​​cket 設定により、デフォルトは 1M ですが、テスト中に 8M に変更されました。

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

以上がMySQL バッチ SQL 挿入パフォーマンスの最適化の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。