システムのデータ量によっては、データベースへのデータ取り込みに時間がかかるだけでなく、データベースは非効率的なクエリという問題に直面しています。特にレポーティングシステムのように、データのインポートに費やす時間は毎日数時間から十数時間に及ぶこともあります。したがって、データベース挿入のパフォーマンスを最適化することは理にかなっています。
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実行の効率が高い主な理由は、マージ後にログの量が減り、データ量とログをスワイプする頻度が減り、効率が向上するためです。また、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;
トランザクションを使用すると、INSERT 操作が実行されるときに MySQL 内でトランザクションが作成され、実際の挿入はトランザクション内で処理されるため、データ挿入の効率が向上します。トランザクションを使用することで、トランザクションを作成する消費量を削減することができ、すべての挿入は実行後にコミットされます。
ここでは、レコード数がそれぞれ100、1,000、10,000の場合に、トランザクションを使用しない場合と使用する場合のテスト比較も行っています。
3.データの整然とした挿入。
データ順序挿入とは、挿入されたレコードが主キーで順序付けされることを意味します：
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);
に修正しました：
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 +ツリーインデックスを参照することができます;インデックスの途中で挿入されたレコードは、より多くの計算資源を消費するB +ツリーマージおよびその他の処理を分割する必要があり、インデックスの位置決め効率のレコードの挿入が低下し、データ量が大きいほど、頻繁になります。ディスク操作。
以下は、100、1,000、10,000、100,000、100万レコードのランダムデータとシーケンシャルデータの性能比較です。
テスト結果から、最適化手法の性能は向上していますが、その向上はあまり顕著ではありません。
パフォーマンスの総合テスト：
上記の3つの方法を同時に使用したINSERT効率最適化のテストは、ここで提供されます。
テスト結果から、我々は、マージデータ+トランザクションメソッドは、小さいデータボリュームで、パフォーマンスの向上は非常に明白であることがわかりますが、データ量が大きくなると、パフォーマンスが急激に低下し、これは、この時点でデータ量innodb_bufferの容量よりも、インデックスを検索するたびに、ディスク上のより多くの読み取りと書き込み操作が含まれるため、パフォーマンスが速く低下します。データ量のマージデータ+トランザクション+順序データの使用は、1000万人以上のパフォーマンスに達するにはまだ良いです、データ量が大きく、順序データのインデックスの位置決めは、より便利ですが、頻繁にディスク上の読み取りと書き込み操作をする必要はありませんので、高いパフォーマンスを維持することができます。
注意事項
1。SQLステートメントは、同じSQL内のデータのマージで、長さの制限は、max_allowed_packetの設定を変更することができますSQLの長さの制限を超えてはならない、デフォルトは1M、テストは8Mに変更されます。
2トランザクションは、トランザクションのサイズを制御する必要が大きすぎるMySQLの実装の効率に影響を与える可能性がありますinnodb_log_buffer_size設定項目を持って、この値以上は、ディスクにinnodbデータブラシされ、その後、効率が低下します。だから、より良いプラクティスは、データがこの値に達する前にトランザクションをコミットすることです。
