MySQL如何快速的创建千万级测试数据
备注:
此文章的数据量在100W,如果想要千万级,调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降
背景
在进行查询操作的性能测试或者sql优化时,我们经常需要在线下环境构建大量的基础数据供我们测试,模拟线上的真实环境。
废话,总不能让我去线上去测试吧,会被DBA砍死的
创建测试数据的方式
1. 编写代码,通过代码批量插库(本人使用过,步骤太繁琐,性能不高,不推荐)
2. 编写存储过程和函数执行(本文实现方式1)
3. 临时数据表方式执行 (本文实现方式2,强烈推荐该方式,非常简单,数据插入快速,100W,只需几秒)
4. 一行一行手动插入,(WTF,去死吧)
创建基础表结构
不管用何种方式,我要插在那张表总要创建的吧
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;
创建内存表
利用 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;
# 创建随机字符串和随机时间的函数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 < 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> 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 <= n) DO -> 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)
mysql> CALL add_t_user_memory(1000000);ERROR 1114 (HY000): The table 't_user_memory' is full
从内存表插入普通表
mysql> INSERT INTO t_user SELECT * FROM t_user_memory;Query OK, 218953 rows affected (1.70 sec)Records: 218953 Duplicates: 0 Warnings: 0
创建临时数据表tmp_table
CREATE TABLE tmp_table (id INT,PRIMARY KEY (id));
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> 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
注意: 导入数据时有可能会报错,原因是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中,100W数据插入需要10.37s
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
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: 0mysql> 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系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- android从服务器下载文件(php+apache+win7+MySql)
- 【有图】android通过jdbc连接mysql(附文件)
- android 通过php 连接 mysql
- android通过php连接mysql数据库!!!!
- 关于Android连接远程数据库(mysql、oracle)
- 图书馆座位管理系统(android,java后台,mysql)