快速构建Mysql百万级测试数据
说明
本文描述问题及解决方法同样适用于 腾讯云 云数据库 MySQL（TencentDB for MySQL，CDB）。
背景
在进行查询等操作的验证时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。
构建数据
这里我们快速构建一份测试数据，用来模拟实际生产中量级在100万的一张数据表。
1. 创建测试库及基础表
MySQL [(none)]> CREATE DATABASE dts_demo;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> USE dts_demo;
Database changed
MySQL [dts_demo]> CREATE TABLE `user_info` (
-> `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;
Query OK, 0 rows affected (0.01 sec)2. 创建内存表
利用 MySQL 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中。
MySQL [dts_demo]> CREATE TABLE `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;
Query OK, 0 rows affected (0.00 sec)3. 创建函数
创建随机字符串和随机时间的函数
MySQL [dts_demo]> delimiter $$
MySQL [dts_demo]>
MySQL [dts_demo]> 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 < n DO
-> 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 [dts_demo]> 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 $$
Query OK, 0 rows affected (0.00 sec)4. 创建存储过程
创建插入数据的存储过程
MySQL [dts_demo]> CREATE DEFINER=`root`@`%` PROCEDURE `add_user_memory`(IN n int)
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE (i <= n) DO
-> INSERT INTO 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.00 sec)
MySQL [dts_demo]> delimiter ;5. 调用存储过程
调用存储过程将测试数据写入内存表
MySQL [dts_demo]> CALL add_user_memory(1000000);
Query OK, 1 row affected (1 min 50.74 sec)生产100万测试数据用时50秒，还是比较效率的。
6. 写入正式表
从内存表插入普通表
MySQL [dts_demo]> INSERT INTO user_info SELECT * FROM user_memory;
Query OK, 1000000 rows affected (7.02 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
MySQL [dts_demo]> DROP TABLE user_memory;
Query OK, 0 rows affected (0.00 sec)7. 打乱创建时间
更新创建时间字段让插入的数据的创建时间更加随机
MySQL [dts_demo]> UPDATE user_info SET create_time=date_add(create_time, interval FLOOR(1 + (RAND() * 7)) year);
Query OK, 1000000 rows affected (2.94 sec)
Rows matched: 1000000 Changed: 1000000 Warnings: 0
MySQL [dts_demo]> select * from user_info limit 20;
+----+--------------------------------------+----------------------+---------------+-----------+---------------------+
| id | c_user_id | c_name | c_province_id | c_city_id | create_time |
+----+--------------------------------------+----------------------+---------------+-----------+---------------------+
| 1 | 1afd2630-88bc-11eb-9c30-0c42a125994e | oxlXASuDAQhIAEmDVAZ4 | 8 | 33 | 2022-03-19 22:05:05 |
| 2 | 1afd300e-88bc-11eb-9c30-0c42a125994e | Nj27hTrqAwIQUPiO0qXo | 727 | 95 | 2028-03-19 22:05:05 |
| 3 | 1afd4041-88bc-11eb-9c30-0c42a125994e | J9rzo41MCC2dM5Whp4Zy | 482 | 22 | 2026-03-19 22:05:05 |
| 4 | 1afd4562-88bc-11eb-9c30-0c42a125994e | RX3eSuFHkqXmNJ8hSoas | 517 | 67 | 2023-03-19 22:05:05 |
| 5 | 1afd4a49-88bc-11eb-9c30-0c42a125994e | YcVRm6gPdssI6cxUMZs9 | 54 | 31 | 2023-03-19 22:05:05 |
| 6 | 1afd4ebd-88bc-11eb-9c30-0c42a125994e | ydfrgRm1VlPX8FLFSeo5 | 968 | 3 | 2027-03-19 22:05:05 |
| 7 | 1afd530c-88bc-11eb-9c30-0c42a125994e | rsMpwgyPk0TiBXO2AFr3 | 585 | 25 | 2027-03-19 22:05:05 |
| 8 | 1afd574a-88bc-11eb-9c30-0c42a125994e | H5aqu0qT4xgB06i1341J | 293 | 73 | 2027-03-19 22:05:05 |
| 9 | 1afd5cf9-88bc-11eb-9c30-0c42a125994e | Y10PZgc4AzTDjxyY5ke0 | 31 | 60 | 2025-03-19 22:05:05 |
| 10 | 1afd61a8-88bc-11eb-9c30-0c42a125994e | 761DXGqU7GUjHpKns2E0 | 732 | 12 | 2022-03-19 22:05:05 |
| 11 | 1afd662c-88bc-11eb-9c30-0c42a125994e | AVIBJG21NLi00PX8HS7O | 384 | 97 | 2022-03-19 22:05:05 |
| 12 | 1afd6ace-88bc-11eb-9c30-0c42a125994e | RK0E38ooDO0r1CSn6dz6 | 474 | 53 | 2022-03-19 22:05:05 |
| 13 | 1afd6f01-88bc-11eb-9c30-0c42a125994e | pNCyKUaVYVyQqowgB3kl | 370 | 31 | 2028-03-19 22:05:05 |
| 14 | 1afd7332-88bc-11eb-9c30-0c42a125994e | CvwX2bCq4VhshQeuy9Yf | 960 | 55 | 2024-03-19 22:05:05 |
| 15 | 1afd775f-88bc-11eb-9c30-0c42a125994e | 3YzKT2oEXGmAIDRdo9on | 383 | 26 | 2024-03-19 22:05:05 |
| 16 | 1afd7bcf-88bc-11eb-9c30-0c42a125994e | j8zjGigivtHUhwDq2OK9 | 172 | 90 | 2025-03-19 22:05:05 |
| 17 | 1afd800c-88bc-11eb-9c30-0c42a125994e | 9pqJfSuEE8AlMKdHHeTD | 130 | 24 | 2025-03-19 22:05:05 |
| 18 | 1afd842c-88bc-11eb-9c30-0c42a125994e | 0DZUqdFwtEGifda3AA4p | 480 | 67 | 2028-03-19 22:05:05 |
| 19 | 1afd886b-88bc-11eb-9c30-0c42a125994e | 6SRyZ7v0mCP981zBaSIL | 374 | 5 | 2022-03-19 22:05:05 |
| 20 | 1afd8c9f-88bc-11eb-9c30-0c42a125994e | jKFUparzjJAyRrv4DMST | 530 | 43 | 2024-03-19 22:05:05 |
+----+--------------------------------------+----------------------+---------------+-----------+---------------------+
20 rows in set (0.00 sec)至此，Mysql测试表已模拟成功。
THE END
0
二维码
海报
快速构建Mysql百万级测试数据
本文描述问题及解决方法同样适用于 腾讯云 云数据库 MySQL（TencentDB for MySQL，CDB）。
