Mysql での 4 つの挿入方法の効率比較

この記事では、項目ごとの挿入、トランザクションベースのバッチ挿入、一度に複数のデータセットを挿入する単一ステートメント、およびインポートの 4 つの方法の比較を含む、Mysql でのいくつかの挿入方法の効率の比較を例で紹介します。この記事では、サンプルコードを通じてそれを詳しく紹介しています。必要な場合は、一緒に見てください。

はじめに

最近、仕事の都合でMysqlに1000万ほどの大量のデータを入れる必要があり、目視検査の方が手間がかかります。したがって、現在は、どの方法でデータを挿入するのがより速く、より効率的であるかをテストするようなものです。

以下では、各メソッドの異なるデータ量での挿入効率をテストします。

テスト データベースの基本と操作は次のとおりです:

mysql> create database test;
Query OK, 1 row affected (0.02 sec)
mysql> use test;
Database changed
mysql> create table mytable(id int primary key auto_increment ,value varchar(50));
Query OK, 0 rows affected (0.35 sec)
mysql> desc mytable;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra  |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| value | varchar(50) | YES | | NULL |  |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

テストを容易にするために、ここでは 2 つのフィールドを含むテーブルが構築されます。1 つは自動インクリメントされる ID、もう 1 つはコンテンツを表す文字列です。

テストする場合は、実験のたびにmysql> truncate mytable既存のテーブルをクリアする必要があります。

方法1: 1つずつ挿入

テストコード：（途中に1000個のinsert文があります。vimでコピー＆ペーストした方が便利です。書いた後、a.sqlに保存し、 mysql プロンプト .sql にソース a と入力します)

set @start=(select current_timestamp(6));
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
set @end=(select current_timestamp(6));
select @start;
select @end;

出力結果:

Query OK, 1 row affected (0.03 sec)
......
Query OK, 1 row affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
+----------------------------+
| @start   |
+----------------------------+
| 2016-05-05 23:06:51.267029 |
+----------------------------+
1 row in set (0.00 sec)
+----------------------------+
| @end   |
+----------------------------+
| 2016-05-05 23:07:22.831889 |
+----------------------------+
1 row in set (0.00 sec)

実際、ほぼすべてのステートメントに同じ時間がかかり、基本的には 30 ミリ秒かかります。

この方法では、1000wのデータには87時間かかります。

データ量が大きい場合は、この方法は絶対にお勧めしません。

方法 2: トランザクションベースのバッチ挿入

実際には、1 つのトランザクションに非常に多くのクエリを入れることになります。実際、メソッド 1 のすべてのステートメントはトランザクションを開くため、特に遅くなります。

テストコード: (基本的に方法1と似ていますが、主に2行追加しています。高速なため、ここではさまざまなデータ量をテストします)

set @start=(select current_timestamp(6));
start transaction;
insert into mytable values(null,"value");
......
insert into mytable values(null,"value");
commit;
set @end=(select current_timestamp(6));
select @start;
select @end;

テスト結果:

数据量 时间(s)
1k  0.1458
1w  1.0793
10w 5.546006
100w 38.930997

基本的に対数時間であることがわかります、効率は依然として比較的高いです。

方法3: 1つのステートメントで一度に複数のデータセットを挿入する

一度に複数の値を挿入する挿入です。

テストコード:

insert into mytable values (null,"value"),
    (null,"value"),
    ......
    (null,"value");

テスト結果:

数据量 时间(s)
1k  0.15
1w  0.80
10w 2.14
100w *

これも対数時間のようで、方法2より若干速いです。ただし、問題は、単一の SQL ステートメントにはバッファ サイズ制限があることです。設定を変更してサイズを大きくすることはできますが、大きすぎることはできません。そのため、大量のデータを挿入する場合には使用できません。

方法 4: データ ファイルをインポートする

数値データをデータ ファイルに書き込み、直接インポートします (前のセクションを参照)。

データファイル(a.dat):

null value
null value
.....
null value
null value

テストコード:

mysql> load data local infile "a.dat" into table mytable;

テスト結果:

数据量 时间(s)
1k  0.13
1w  0.75
10w 1.97
100w 6.75
1000w 58.18

最速タイムを出したのは彼です。 。 。 。

以上がMysql での 4 つの挿入方法の効率比較の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。