网站屏蔽省份,目前个人网站做地最好是哪几家,wordpress安装不了插件,易语言做自动登陆网站作者#xff1a;IKNOW本尊链接#xff1a;http://t.cn/E96Shud背景在进行查询操作的性能测试或者 sql 优化时#xff0c;我们经常需要在线下环境构建大量的基础数据供我们测试#xff0c;模拟线上的真实环境。总不能让我去线上去测试吧#xff0c;会被DBA砍死的创建测试数… 作者IKNOW本尊链接http://t.cn/E96Shud背景在进行查询操作的性能测试或者 sql 优化时我们经常需要在线下环境构建大量的基础数据供我们测试模拟线上的真实环境。总不能让我去线上去测试吧会被DBA砍死的创建测试数据的方式 1. 编写代码通过代码批量插库本人使用过步骤太繁琐性能不高不推荐2. 编写存储过程和函数执行本文实现方式13. 临时数据表方式执行 本文实现方式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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;
方式1采用存储过程和内存表创建内存表利用 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)
) ENGINEMEMORY DEFAULT CHARSETutf8mb4;
创建函数和存储过程# 创建随机字符串和随机时间的函数
mysql delimiter $$
mysql CREATE DEFINERroot% 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 DEFINERroot% 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(1RAND()*(sub-1)) SECOND);- RETURN ret;- END $$
mysql delimiter ;
# 创建插入数据存储过程
mysql CREATE DEFINERroot% 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
出现内存已满时修改 max_heap_table_size 参数的大小我使用64M内存插入了22W数据看情况改不过这个值不要太大默认32M或者64M就好生产环境不要乱尝试
从内存表插入普通表mysql INSERT INTO t_user SELECT * FROM t_user_memory;
Query OK, 218953 rows affected (1.70 sec)
Records: 218953 Duplicates: 0 Warnings: 0
方式2采用临时表创建临时数据表tmp_tableCREATE TABLE tmp_table (id INT,PRIMARY KEY (id)
);
用 python或者bash 生成 100w 记录的数据文件python瞬间就会生成完python(推荐): python -c for i in range(1, 11000000): print(i) 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
千万级数据 20秒插入完成
注意导入数据时有可能会报错原因是mysql默认没有开securefilepriv 这个参数用来限制数据导入和导出操作的效果例如执行LOAD DATA、SELECT … INTO OUTFILE语句和LOAD_FILE()函数。这些操作需要用户具有FILE权限 。解决办法在mysql的配置文件中my.ini 或者 my.conf中添加 securefilepriv /Users/LJTjintao/temp/, 然后重启mysql 解决以临时表为基础数据插入数据到t_user中100W数据插入需要10.37smysql 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_timedate_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_timedate_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)
注意此文章的数据量在100W如果想要千万级调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降。往期精彩喜庆王磊的博客搬迁无套路送机械键盘面试官问你MySQL的优化看这篇文章就够了经典面试题如何保证缓存与数据库的双写一致性 关注下方二维码订阅更多精彩内容。转发朋友圈是对我最大的支持。
