パフォーマンスを最適化するにはどうすればよいですか? MySQL でバッチ挿入を実装してパフォーマンスを最適化する例の詳細な説明

この記事では、パフォーマンスを最適化するためのバッチ挿入を実装するための MySQL のチュートリアルを主に紹介します。パフォーマンスの最適化後の比較を表すために、実行時間が示されています。

大量のデータを含む一部のシステムの場合、データベース。クエリ効率が低いことに加えて、データベースにデータが保存されるまでに時間がかかるという問題があります。特にレポート システムの場合、データのインポートに費やす時間が毎日数時間から 10 時間以上かかる場合があります。したがって、データベース挿入のパフォーマンスを最適化することは理にかなっています。
MySQL innodb でいくつかのパフォーマンス テストを行った結果、挿入効率を向上させるいくつかの方法が見つかりましたので、ご参照ください。

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

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1);

などの一般的に使用される挿入文は、次のように変更されます。

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('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. トランザクション内で挿入処理を行います。
挿入を次のように変更します:

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

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

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
  VALUES ('2', 'userid_2', 'content_2',2);

は、保守する必要があるため、次のように変更されます。インデックス データをデータベースに挿入する場合、順序付けされていません。 ロギングによりインデックスの維持コストが増加します。 innodb によって使用される B+tree インデックスを参照できます。挿入されたレコードがインデックスの最後にある場合、インデックスの配置効率は非常に高く、挿入されたレコードがインデックスの中央にある場合、インデックスの調整は小さくなります。インデックス、B+ツリーが必要です。分割やマージなどの処理により多くのコンピューティング リソースが消費され、データ量が多い場合、挿入されたレコードのインデックス配置効率が低下します。
ランダム データとシーケンシャル データのパフォーマンス比較を以下に示します。それぞれ 100、1000、10000、100000、100 万として記録されます。

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

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

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

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

注:

1. SQL ステートメントには長さ制限があります。データをマージする場合、同じ SQL 内で SQL 長さ制限を超えてはなりません。デフォルトは 1M です。テスト中は8M。 2. トランザクションのサイズを制御する必要があります。大きすぎるトランザクションは実行効率に影響を与える可能性があります。 MySQL には innodb_log_buffer_size 設定項目があり、この値を超えると、innodb データがディスクにフラッシュされ、効率が低下します。したがって、より良いアプローチは、データがこの値に達する前にトランザクションをコミットすることです。

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