快速构建Mysql百万级测试数据

在进行查询等操作的验证时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。

背景

在进行查询等操作的验证时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。

构建数据

这里我们快速构建一份测试数据，用来模拟实际生产中量级在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 {
   mathJaxContainer[0]}
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 {
   mathJaxContainer[1]}
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测试表已模拟成功。