MySQLで数千万のテストデータを素早く作成する方法

述べる：

この記事で扱うデータ量は 100 万です。数千万のデータが必要な場合は、量を増やすだけで済みます。ただし、パフォーマンスの低下を招くため、rand() や uuid() を大量に使用しないでください。

背景

クエリ操作のパフォーマンステストや SQL 最適化を実行する場合、実際のオンライン環境をシミュレートするために、オフライン環境で大量の基本データを構築する必要があることがよくあります。

ナンセンスだ、オンラインでテストさせてもらえないよ。そうしないと DBA に殺されてしまうぞ。

テストデータの作成方法

1. コードを書き、コードを通じてライブラリを一括挿入する（私は使ったことがありますが、手順が面倒で、パフォーマンスも高くなく、お勧めできません）

2. ストアドプロシージャを記述して関数を実行する（この記事の実装方法1）

3. 一時データテーブルモードで実行します (この記事では、強く推奨されるモード 2 を実装しています。非常にシンプルで、データの挿入が高速です。100W のデータで数秒しかかかりません)

4. 1行ずつ手動で挿入します（何てこった、地獄に落ちろ）

基本的なテーブル構造を作成する

どのような方法を使用する場合でも、挿入するテーブルを作成する必要があります。

テーブル `t_user` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL デフォルト ''
 `c_name` varchar(22) NOT NULL デフォルト ''
 `c_province_id` int(11) NULLではない、
 `c_city_id` int(11) NULLではない、
 `create_time` 日時 NOT NULL、
 主キー (`id`)、
 キー `idx_user_id` (`c_user_id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

方法 1: ストアドプロシージャとメモリテーブルを使用する

メモリテーブルを作成する

MySQLメモリテーブルの高速な挿入速度を利用して、まず関数とストアドプロシージャを使用してメモリテーブルにデータを生成し、次にメモリテーブルから通常のテーブルに挿入します。

テーブル `t_user_memory` を作成します (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL デフォルト ''
 `c_name` varchar(22) NOT NULL デフォルト ''
 `c_province_id` int(11) NULLではない、
 `c_city_id` int(11) NULLではない、
 `create_time` 日時 NOT NULL、
 主キー (`id`)、
 キー `idx_user_id` (`c_user_id`)
)ENGINE=MEMORY デフォルト文字セット=utf8mb4;

関数とストアドプロシージャの作成

# ランダムな文字列とランダムな時間を作成する関数を作成する mysql> 区切り文字 $$
mysql> CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) 戻り値 varchar(255) CHARSET utf8mb4
 -> 決定論的
 -> 開始
 -> chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' を宣言します。
 -> return_str varchar(255) DEFAULT '' を宣言します。
 -> i INT DEFAULT 0 を宣言します。
 -> ながら i < n DO
 -> SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1));
 -> i = i + 1 を設定します。
 -> END WHILE;
 -> 戻り値 return_str;
 -> 終了$$
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime
 -> 決定論的
 -> 開始
 -> sub INT DEFAULT 0 を宣言します。
 -> ret DATETIME を宣言します。
 -> SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
 -> SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);
 -> 戻り値 ret;
 -> 終了 $$

mysql> 区切り文字;

# データストレージにデータを挿入するためのプロシージャを作成します。 mysql> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int)
 -> 開始
 -> i INT DEFAULT 1 を宣言します。
 -> (i <= n) の間、
 -> t_user_memory に (c_user_id、c_name、c_province_id、c_city_id、create_time) VALUES (uuid()、randStr(20)、FLOOR(RAND() * 1000)、FLOOR(RAND() * 100)、NOW() を挿入します。
 -> i = i + 1 を設定します。
 -> END WHILE;
 -> 終了
 -> $$
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

ストアドプロシージャの呼び出し

mysql> add_t_user_memory(1000000) を呼び出します。
エラー 1114 (HY000): テーブル 't_user_memory' がいっぱいです

メモリがいっぱいになったら、max_heap_table_sizeパラメータを変更します。64Mのメモリを使用し、22Wのデータを挿入しました。状況に応じて変更しますが、値は大きすぎないようにしてください。デフォルト値の32Mまたは64Mで問題ありません。本番環境では試さないでください。

メモリテーブルから通常のテーブルへの挿入

mysql> t_user に INSERT INTO し、 t_user_memory から * を選択します。
クエリは正常、218953 行が影響を受けました (1.70 秒)
レコード: 218953 重複: 0 警告: 0

方法2: 一時テーブルを使用する

一時データテーブル tmp_table を作成する

テーブルtmp_tableを作成します（
	id INT、
	主キー (id)
);

Python または bash を使用して、100 万件のレコードを含むデータファイルを生成します (Python では即座に生成されます)

python (推奨): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
bash (非推奨、遅い): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done > base.txt

一時テーブルtmp_tableにデータをインポートする

mysql> '/Users/LJTjintao/temp/base.txt' ファイルにデータをロードし、tmp_table テーブルに置き換えます。
クエリは正常、1000000 行が影響を受けました (2.55 秒)
レコード: 1000000 削除: 0 スキップ: 0 警告: 0

数千万のデータを20秒で挿入可能

注意: MySQL では secure_file_priv がデフォルトで有効になっていないため、データのインポート時にエラーが発生する可能性があります (このパラメータは、LOAD DATA、SELECT ... INTO OUTFILE ステートメント、および LOAD_FILE() 関数の実行など、データのインポートおよびエクスポート操作の影響を制限するために使用されます。これらの操作を行うには、ユーザーに FILE 権限が必要です)。

解決策: mysql 構成ファイル (my.ini または my.conf) にsecure_file_priv = /Users/LJTjintao/temp/`を追加し、mysql を再起動します。

一時テーブルを基本データとして使用し、t_user にデータを挿入します。100 万件のデータを挿入するには 10.37 秒かかります。

mysql> t_userに挿入
 -> 選択
 -> ID、
 ->uuid(),
 -> CONCAT('userNickName', id),
 -> FLOOR(Rand() * 1000)、
 -> FLOOR(Rand() * 100)、
 -> 今()
 -> から
 -> tmp_table;
クエリは正常、1000000 行が影響を受けました (10.37 秒)
レコード: 1000000 重複: 0 警告: 0

挿入されたデータの作成時間をよりランダムにするために、作成時間フィールドを更新します。

t_user を更新し、create_time=date_add(create_time、間隔 FLOOR(1 + (RAND() * 7)) 年) を設定します。

クエリは正常、1000000 行が影響を受けました (5.21 秒)
一致した行: 1000000 変更された行: 1000000 警告: 0

mysql> UPDATE t_user SET create_time=date_add(create_time, 間隔 FLOOR(1 + (RAND() * 7)) 年);


クエリは正常、1000000 行が影響を受けました (4.77 秒)
一致した行: 1000000 変更された行: 1000000 警告: 0

mysql> t_user 制限 30 から * を選択します。
+----+---------------------------------------+----------------+-----------+----------+---------------------+
| id | c_user_id | c_name | c_province_id | c_city_id | 作成時間 |
+----+---------------------------------------+----------------+-----------+----------+---------------------+
| 1 | bf5e227a-7b84-11e9-9d6e-751d319e85c2 | userNickName1 | 84 | 64 | 2015-11-13 21:13:19 |
| 2 | bf5e26f8-7b84-11e9-9d6e-751d319e85c2 | userNickName2 | 967 | 90 | 2019-11-13 20:19:33 |
| 3 | bf5e2810-7b84-11e9-9d6e-751d319e85c2 | userNickName3 | 623 | 40 | 2014-11-13 20:57:46 |
| 4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | userNickName4 | 140 | 49 | 2016-11-13 20:50:11 |
| 5 | bf5e28f6-7b84-11e9-9d6e-751d319e85c2 | userNickName5 | 47 | 75 | 2016-11-13 21:17:38 |
| 6 | bf5e295a-7b84-11e9-9d6e-751d319e85c2 | userNickName6 | 642 | 94 | 2015-11-13 20:57:36 |
| 7 | bf5e29be-7b84-11e9-9d6e-751d319e85c2 | userNickName7 | 780 | 7 | 2015-11-13 20:55:07 |
| 8 | bf5e2a4a-7b84-11e9-9d6e-751d319e85c2 | userNickName8 | 39 | 96 | 2017-11-13 21:42:46 |
| 9 | bf5e2b58-7b84-11e9-9d6e-751d319e85c2 | userNickName9 | 731 | 74 | 2015-11-13 22:48:30 |
| 10 | bf5e2bb2-7b84-11e9-9d6e-751d319e85c2 | userNickName10 | 534 | 43 | 2016-11-13 22:54:10 |
| 11 | bf5e2c16-7b84-11e9-9d6e-751d319e85c2 | userNickName11 | 572 | 55 | 2018-11-13 20:05:19 |
| 12 | bf5e2c70-7b84-11e9-9d6e-751d319e85c2 | userNickName12 | 71 | 68 | 2014-11-13 20:44:04 |
| 13 | bf5e2cca-7b84-11e9-9d6e-751d319e85c2 | userNickName13 | 204 | 97 | 2019-11-13 20:24:23 |
| 14 | bf5e2d2e-7b84-11e9-9d6e-751d319e85c2 | userNickName14 | 249 | 32 | 2019-11-13 22:49:43 |
| 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | userNickName15 | 900 | 51 | 2019-11-13 20:55:26 |
| 16 | bf5e2dec-7b84-11e9-9d6e-751d319e85c2 | userNickName16 | 854 | 74 | 2018-11-13 22:07:58 |
| 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | userNickName17 | 136 | 46 | 2013-11-13 21:53:34 |
| 18 | bf5e2eb4-7b84-11e9-9d6e-751d319e85c2 | userNickName18 | 897 | 10 | 2018-11-13 20:03:55 |
| 19 | bf5e2f0e-7b84-11e9-9d6e-751d319e85c2 | userNickName19 | 829 | 83 | 2013-11-13 20:38:54 |
| 20 | bf5e2f68-7b84-11e9-9d6e-751d319e85c2 | userNickName20 | 683 | 91 | 2019-11-13 20:02:42 |
| 21 | bf5e2fcc-7b84-11e9-9d6e-751d319e85c2 | userNickName21 | 511 | 81 | 2013-11-13 21:16:48 |
| 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | userNickName22 | 562 | 35 | 2019-11-13 20:15:52 |
| 23 | bf5e3080-7b84-11e9-9d6e-751d319e85c2 | userNickName23 | 91 | 39 | 2016-11-13 20:28:59 |
| 24 | bf5e30da-7b84-11e9-9d6e-751d319e85c2 | userNickName24 | 677 | 21 | 2016-11-13 21:37:15 |
| 25 | bf5e3134-7b84-11e9-9d6e-751d319e85c2 | userNickName25 | 50 | 60 | 2018-11-13 20:39:20 |
| 26 | bf5e318e-7b84-11e9-9d6e-751d319e85c2 | userNickName26 | 856 | 47 | 2018-11-13 21:24:53 |
| 27 | bf5e31e8-7b84-11e9-9d6e-751d319e85c2 | userNickName27 | 816 | 65 | 2014-11-13 22:06:26 |
| 28 | bf5e324c-7b84-11e9-9d6e-751d319e85c2 | userNickName28 | 806 | 7 | 2019-11-13 20:17:30 |
| 29 | bf5e32a6-7b84-11e9-9d6e-751d319e85c2 | userNickName29 | 973 | 63 | 2014-11-13 21:08:09 |
| 30 | bf5e3300-7b84-11e9-9d6e-751d319e85c2 | userNickName30 | 237 | 29 | 2018-11-13 21:48:17 |
+----+---------------------------------------+----------------+-----------+----------+---------------------+
セット内の行数は30行（0.01秒）

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。

以下もご興味があるかもしれません: