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

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 を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • MySQL に大量のデータを挿入する 4 つの方法の例
  • MYSQL バッチ挿入データ実装コード
  • MySQL でバッチ挿入を実装してパフォーマンスを最適化するチュートリアル
  • ユニークインデックスを使用したMySQLバッチ挿入を回避する方法
  • MySQLは挿入を使用して複数のレコードを挿入し、データを一括で追加します。
  • MySQL バッチ挿入ループの詳細なサンプルコード
  • MySQL バッチデータ挿入スクリプト
  • MySql バッチ挿入の最適化 SQL 実行効率の例の詳細な説明
  • MySQLバッチは関数ストアドプロシージャを通じてデータを挿入します

<<:  Docker を使って LEMP 環境を素早く構築する方法の例

>>:  Reactにおける不変値の説明

推薦する

nginx プロキシ サーバーで双方向証明書検証を構成する方法

証明書チェーンを生成するスクリプトを使用して、ルート証明書、中間証明書、および 3 つのクライアント...

ハイパーリンクを開くターゲットのテスト

リンクのターゲット属性は、リンクが開く場所を決定します。その値は通常、_blank、_self、_p...

MySQLの一般的なバックアップコマンドとシェルバックアップスクリプトの共有

複数のデータベースをバックアップするには、次のコマンドを使用できます。 mysqldump -uro...

MySQLでデータベースのインストールパスを表示する方法

mysql コマンドを使用して、mysql のインストール パスを表示できます。 # 次の 2 つの...

MySQL データ分析ストレージエンジンの例の説明

目次1. 事例紹介2. システムのデフォルトのストレージエンジンとデフォルトの文字セットを表示する3...

Nginx に React プロジェクトをデプロイする方法の例

テストプロジェクト: react-demo react-demo プロジェクトをサーバーにクローンし...

nginx設定ファイルの解釈の詳細な説明

nginx 設定ファイルは主に 4 つの部分に分かれています。 main{#(グローバル設定) ht...

Vue で ToDo アプリケーションを実装する例

背景まず最初に、私はフロントエンド開発の専門家ではないことを述べておきたいと思います。私の以前のコン...

MySQL データベースの 1045 エラーの解決方法

ローカル データベースがサーバー データベースに接続されているときに発生する 1045 の問題を解決...

TypeScript ジェネリックパラメータのデフォルト型と新しい厳密なコンパイルオプション

目次概要コンポーネントクラスの型定義を作成するジェネリック型を使用してPropsとStateを定義す...

Tomcat Nativeを使用してTomcat IO効率を向上させる方法の詳細な説明

目次導入Tomcatへの接続方法APR と Tomcat ネイティブtomcat で APR を使用...

MySQL ステートメントロックの実装の分析

概要: 2 つの MySQL SQL ステートメント ロックの分析次のSQL文にどのようなロックが追...

レスポンシブ Web デザイン手法を実装し、ウォーターフォール モデルに別れを告げる 5 つのステップ (グラフィック チュートリアル)

次の Web デザイン プロジェクトはレスポンシブにする必要があると上司をようやく納得させることがで...

Docker を使用した nextcloud パーソナル ネットワーク ディスクの構築に関するチュートリアル

目次1. はじめに2. 導入環境ツール4. 展開プロセス要約する1. はじめにNextcloud は...

Linux の crontab タスク スケジューリングの簡単な分析

1. スケジュールタスクを作成する命令crontab -eは現在のユーザーの編集インターフェースに入...