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における不変値の説明

推薦する

CentOS 6.5 i386 インストール MySQL 5.7.18 詳細チュートリアル

ほとんどの人はMySQLをコンパイルしてシステムディレクトリに置きますが、私のやり方はコンパイルした...

JavaScript ESの新機能letとconstキーワードに基づく

目次1. letキーワード1.1 基本的な使い方1.2 変動昇進はない1.3 一時的なデッドゾーン1...

MyISAMとInnoDBの違いについてお話しましょう

主な違いは次のとおりです。 1. MySQL はデフォルトで MyISAM を使用します。 2. M...

一般的なブラウザ互換性の問題(概要)

ブラウザの互換性とは、スタイルの互換性 (CSS)、インタラクションの互換性 (JavaScript...

JavaScript デザインパターン 責任連鎖パターン

目次概要コードの実装パラメータ定義成し遂げる責任連鎖パターンの実装改善概要責任チェーン パターンは、...

CenterOS7 インストールおよび構成環境 jdk1.8 チュートリアル

1. まずcenterosに付属のJDKをアンインストールします rpm-qa|grepopenjd...

今日、私は非常に奇妙なクリックの問題に遭遇し、自分で解決しました

...こんな感じで、今日はポップアップウィンドウを作ろうと思ったのですが、バックエンド PHP によ...

HTML のテキストエリアの改行問題の概要

最近、Textrea に転送したときに、データが本当に行ごとに保存できるかどうかという問題に遭遇しま...

Linuxのファイル操作の知識ポイントを詳しく解説

ファイル操作に関連するシステムコール作成するint creat(const char *ファイル名,...

Vue3+Vantコンポーネントを使用してアプリの検索履歴機能を実装する(サンプルコード)

現在、新しいアプリプロジェクトを開発中です。私にとっても初めてのアプリ開発です。チームで調査と検討を...

JavaScriptタイマーとボタン効果設定の詳細な説明

タイマー効果: <div> <font id='timeCount'...

Linux での MySQL 5.6.27 インストール チュートリアル

この記事では、LinuxでのMySQL 5.6.27のインストールチュートリアルを参考までに紹介しま...

MySQLの一般的なメモリ不足による起動失敗に対する完璧な解決策

1. MySQLが正常に起動しない場合は、エラーログ/var/log/mysql/error.log...

単一/複数行テキストを含む div を垂直方向に中央揃えする N 通りの方法 (高さ不明/高さ固定)

この問題について話すとき、垂直方向の中央揃えを設定するための vertical-align 属性が ...

Javascript で関数のカリー化とデカリー化を実装する方法

関数のカリー化(黒い疑問符の顔)? ? ?カレー(黒い疑問符の顔)? ? ?これは完璧な中国語翻訳で...