Maison > Article > base de données > MySQL crée rapidement des dizaines de millions de données de test
MySQL crée rapidement des dizaines de millions de données de test
- 步履不停original
- 2019-06-18 14:42:192385parcourir
Remarque : le volume de données de cet article est de 100 W. Si vous voulez des dizaines de millions, augmentez simplement le montant. Cependant, n'utilisez pas rand() ou uuid() en grande quantité. , ce qui entraînera une dégradation des performances.
Contexte
Lorsque nous effectuons des tests de performances d'opérations de requête ou d'optimisation SQL, nous devons souvent créer une grande quantité de données de base dans l'environnement hors ligne pour nos tests. simuler le véritable environnement en ligne.
C'est absurde, vous ne pouvez pas me laisser tester en ligne, je serai piraté à mort par le DBA
Comment créer des données de test
1. 编写代码，通过代码批量插库（本人使用过，步骤太繁琐，性能不高，不推荐） 2. 编写存储过程和函数执行（本文实现方式1） 3. 临时数据表方式执行 （本文实现方式2，强烈推荐该方式，非常简单，数据插入快速，100W，只需几秒） 4. 一行一行手动插入，（WTF，去死吧）
Créer les bases de la structure de la table
Peu importe la méthode utilisée, la table que je souhaite insérer doit être créée
CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT NULL DEFAULT '', `c_name` varchar(22) NOT NULL DEFAULT '', `c_province_id` int(11) NOT NULL, `c_city_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Méthode 1 : Utiliser des procédures stockées et des tables mémoire
Créer une table mémoire
利用 MySQL 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中 CREATE TABLE `t_user_memory` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT NULL DEFAULT '', `c_name` varchar(22) NOT NULL DEFAULT '', `c_province_id` int(11) NOT NULL, `c_city_id` int(11) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_id` (`c_user_id`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;
Créer des fonctions et des procédures stockées
# 创建随机字符串和随机时间的函数 mysql> delimiter $$ mysql> CREATE DEFINER=`root`@`%` FUNCTION `randStr`(n INT) RETURNS varchar(255) CHARSET utf8mb4 -> DETERMINISTIC -> BEGIN -> DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; -> DECLARE return_str varchar(255) DEFAULT '' ; -> DECLARE i INT DEFAULT 0; -> WHILE i SET return_str = concat(return_str, substring(chars_str, FLOOR(1 + RAND() * 62), 1)); -> SET i = i + 1; -> END WHILE; -> RETURN return_str; -> END$$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE DEFINER=`root`@`%` FUNCTION `randDataTime`(sd DATETIME,ed DATETIME) RETURNS datetime -> DETERMINISTIC -> BEGIN -> DECLARE sub INT DEFAULT 0; -> DECLARE ret DATETIME; -> SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)); -> SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND); -> RETURN ret; -> END $$ mysql> delimiter ; # 创建插入数据存储过程 mysql> CREATE DEFINER=`root`@`%` PROCEDURE `add_t_user_memory`(IN n int) -> BEGIN -> DECLARE i INT DEFAULT 1; -> WHILE (i INSERT INTO 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()); -> SET i = i + 1; -> END WHILE; -> END -> $$ Query OK, 0 rows affected (0.01 sec)
Appeler une procédure stockée
mysql> CALL add_t_user_memory(1000000); ERROR 1114 (HY000): The table 't_user_memory' is full 出现内存已满时，修改 max_heap_table_size 参数的大小，我使用64M内存，插入了22W数据，看情况改，不过这个值不要太大，默认32M或者64M就好，生产环境不要乱尝试
Insérer dans une table ordinaire à partir d'une table mémoire
mysql> INSERT INTO t_user SELECT * FROM t_user_memory; Query OK, 218953 rows affected (1.70 sec) Records: 218953 Duplicates: 0 Warnings: 0
Méthode 2 : Utilisez la table temporaire
pour créer une table de données temporaire tmp_table
mysql> INSERT INTO t_user SELECT * FROM t_user_memory; Query OK, 218953 rows affected (1.70 sec) Records: 218953 Duplicates: 0 Warnings: 0
Utilisez python ou bash pour générer un Fichier de données enregistrées de 100 w (Python sera généré en un instant)
python(推荐): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
Importer les données dans la table temporaire tmp_table
mysql> load data infile '/Users/LJTjintao/temp/base.txt' replace into table tmp_table; Query OK, 1000000 rows affected (2.55 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0 千万级数据 20秒插入完成
Remarque : Une erreur peut être signalée lors de l'importation de données car mysql n'active pas secure_file_priv par défaut (Ce paramètre est utilisé pour limiter l'effet des opérations d'importation et d'exportation de données, telles que l'exécution de LOAD DATA, SELECT... Instruction INTO OUTFILE et fonctions LOAD_FILE(). Ces opérations nécessitent l'utilisateur avec l'autorisation FILE. )
Solution : Ajoutez secure_file_priv = /Users/LJTjintao/temp/` dans le fichier de configuration mysql ( my.ini ou my.conf), puis redémarrez mysql résout
en utilisant la table temporaire comme données de base, insertion de données dans t_user, l'insertion de données 100W prend 10,37 s
mysql> INSERT INTO t_user
-> SELECT
-> id,
-> uuid(),
-> CONCAT('userNickName', id),
-> FLOOR(Rand() * 1000),
-> FLOOR(Rand() * 100),
-> NOW()
-> FROM
-> tmp_table;
Query OK, 1000000 rows affected (10.37 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
Mettre à jour le champ d'heure de création pour rendre l'heure de création des données insérées plus aléatoire
UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year); Query OK, 1000000 rows affected (5.21 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0 mysql> UPDATE t_user SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year); Query OK, 1000000 rows affected (4.77 sec) Rows matched: 1000000 Changed: 1000000 Warnings: 0
mysql> select * from t_user limit 30; +----+--------------------------------------+----------------+---------------+-----------+---------------------+ | id | c_user_id | c_name | c_province_id | c_city_id | create_time | +----+--------------------------------------+----------------+---------------+-----------+---------------------+ | 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 rows in set (0.01 sec)
Plus de détails sur MySQL Pour les articles techniques, veuillez visiter la colonne Tutoriel MySQL pour apprendre !
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!