MySQLバルクSQL挿入パフォーマンスの最適化

いくつかのデータ量の大きいシステムでは、データベースが直面している問題は、クエリーの効率が低いだけでなく、データの入庫時間が長いことです.特に、レポート・システムのように、データのインポートに毎日数時間または十数時間かかる場合があります.したがって、データベース挿入のパフォーマンスを最適化することは意味があります.MySQL innodbのいくつかの性能テストを経て、insertの効率を高める方法を発見しました.参考にしてください.
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文への変換を行い、それぞれ1百、1千、1万件のデータ記録をテストするテスト比較データを提供します.
2.取引で挿入処理を行います.挿入を次のように変更します.

STARTTRANSACTION;
INSERTINTO insert_table (`datetime`,`uid`,`content`,`type`)VALUES('0','userid_0','content_0',0);INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)VALUES('1','userid_1','content_1',1);
...
COMMIT;

トランザクションを使用すると、INSERT操作を行うとMySQL内部にトランザクションが作成され、トランザクション内で本格的な挿入処理が行われるため、データの挿入効率が向上します.トランザクションを使用すると、トランザクションの作成にかかる消費量を減らすことができ、すべての挿入は実行後にコミットされます.
ここでは、トランザクションを使用しない場合と、トランザクションを使用してレコード数が1百、1千、1万の場合のテストの比較も提供します.
3.データの順序付け挿入.データ順序の挿入とは、挿入レコードがプライマリ・キーに順序付けされていることを意味します.たとえばdatetimeはレコードのプライマリ・キーです.

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

次のように変更します.

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

データベースが挿入されると、インデックスデータを維持する必要があるため、無秩序なレコードはインデックスを維持するコストを増大させます.innodbで使用されているB+treeインデックスを参照できます.レコードを挿入するたびにインデックスの一番後ろにある場合、インデックスの位置決め効率が高く、インデックスの調整が小さくなります.挿入されたレコードがインデックスの間にある場合、B+treeで分割合併などの処理が必要となり、比較的多くの計算リソースが消費され、挿入されたレコードのインデックスの位置決め効率が低下し、データ量が大きい場合に頻繁にディスク操作が行われる.以下に、ランダムデータとシーケンスデータの性能比較を提供し、それぞれ1百、1千、1万、10万、100万を記録する.
試験結果から,この最適化法の性能は向上したが,向上は顕著ではなかった.
性能総合テスト:ここでは、上記の3つの方法を併用してINSERT効率最適化を行うテストを提供します.
テスト結果から、データ+トランザクションをマージする方法は、データ量が小さい場合にパフォーマンスの向上が顕著であり、データ量が大きい場合(1千万以上)、パフォーマンスが急激に低下することがわかります.これは、innodb_を超えるためです.bufferの容量は、インデックスを配置するたびにディスクの読み書き操作が多くなり、パフォーマンスの低下が速くなります.データ+トランザクション+秩序データをマージする方法は、データ量が千万級以上に達すると依然として良好であり、データ量が大きい場合、秩序データインデックスの位置決めが便利であり、頻繁にディスクの読み書き操作を行う必要がないため、高い性能を維持することができる.
注意事項:1.SQL文には長さ制限があります.データのマージを行うには、同じSQLでSQLの長さ制限を超えてはいけません.max_allowed_packet構成は変更可能で、デフォルトは1 Mで、テスト時には8 Mに変更されます.2.トランザクションはサイズを制御する必要があります.トランザクションが大きすぎると、実行の効率に影響を与える可能性があります.MySQLにinnodb_がありますlog_buffer_size構成項目は、この値を超えるとinnodbのデータがディスクにブラシされ、効率が低下します.したがって、データがこの値に達する前にトランザクションのコミットを行うのが良いです.
原文出典:http://tech.uc.cn/?p=634
転載先:https://blog.51cto.com/alpha001/1264868