python

MySQL数据生成

文章暂存

systemime
2020-11-03
9 min

摘要.

# 百万级

# 1、生成思路

利用mysql内存表插入速度快的特点,先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中

# 2、创建内存表及普通表

1. CREATE TABLE `vote_record_memory` (
2. 	`id` INT (11) NOT NULL AUTO_INCREMENT,
3. 	`user_id` VARCHAR (20) NOT NULL,
4. 	`vote_id` INT (11) NOT NULL,
5. 	`group_id` INT (11) NOT NULL,
6. 	`create_time` datetime NOT NULL,
7. 	PRIMARY KEY (`id`),
8. 	KEY `index_id` (`user_id`) USING HASH
9. ) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
1. CREATE TABLE `vote_record` (
2. 	`id` INT (11) NOT NULL AUTO_INCREMENT,
3. 	`user_id` VARCHAR (20) NOT NULL,
4. 	`vote_id` INT (11) NOT NULL,
5. 	`group_id` INT (11) NOT NULL,
6. 	`create_time` datetime NOT NULL,
7. 	PRIMARY KEY (`id`),
8. 	KEY `index_user_id` (`user_id`) USING HASH
9. ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

# 3、创建函数及存储过程

1. CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
2. BEGIN
3. DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
4. DECLARE return_str varchar(255) DEFAULT '' ;
5. DECLARE i INT DEFAULT 0; 
6. WHILE i < n DO
7. SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); 
8. SET i = i +1; 
9. END WHILE; 
10. RETURN return_str; 
11. END
1. CREATE  PROCEDURE `add_vote_memory`(IN n int)
2. BEGIN
3. DECLARE i INT DEFAULT 1;
4.     WHILE (i <= n ) DO
5. INSERT into vote_record_memory  (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
6. 			set i=i+1;
7. END WHILE;
8. END

# 4、调用存储过程

CALL add_vote_memory(1000000)


根据电脑性能不能所花时间不一样,大概时间在小时级别,如果报错内存满了,只在修改max_heap_table_size 个参数即可,win7修改位置如下,linux,修改my.cnf文件,修改后要重启mysql,重启后内存表数据会丢失


# 5、插入普通表中

INSERT into vote_record SELECT * from  vote_record_memory

# 6、结果




# 千万级

这种做法生成的测试数据相对比较符合实际,注册时间和最后登录时间都既有一定的时间顺序,又有在时间区间内有随机生成,代码都有注释,这里就不多说了,代码如下:

1. #建测试表
2. drop table if exists t;
3. CREATE TABLE t (
4. id int NOT NULL AUTO_INCREMENT PRIMARY KEY comment '自增主键', 
5.                 dept tinyint not null comment '部门id',
6. name varchar(30) comment '用户名称',
7.                 create_time datetime not null comment '注册时间', 
8.                 last_login_time datetime comment '最后登录时间'
9.                ) comment '测试表';
10. 
11. #手工插入第一条测试数据,后面数据会根据这条数据作为基础生成
12. insert into t values(1,1,'user_1', '2018-01-01 00:00:00', '2018-03-01 12:00:00');
13. #初始化序列变量
14. set @i=1;
15. 
16. 
17. #==================此处拷贝反复执行,直接符合预想的数据量===================
18. #执行20次即2的20次方=1048576 条记录
19. #执行23次即2的23次方=8388608 条记录
20. #执行24次即2的24次方=16777216 条记录
21. #......
22. insert into t(dept, name, create_time, last_login_time) 
23. select left(rand()*10,1) as dept,   #随机生成1~10的整数
24. concat('user_',@i:=@i+1),   #按序列生成不同的name
25. date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), #生成有时间大顺序随机注册时间
26. date_add(date_add(create_time,interval +@i*cast(rand()*100 as signed) SECOND), interval + cast(rand()*1000000 as signed) SECOND) #生成有时间大顺序的随机的最后登录时间
27. from t;
28. select count(1) from t;
29. #==================此处结束反复执行=====================
30. 
31. 
32. #创建索引(视情况执行)
33. create index idx_dept on t(dept);
34. create index idx_create_time on t(create_time);
35. create index idx_last_login_time on t(last_login_time);


  上面也可以改写成存储过程,但这也不是每天执行就懒得改了,就这样吧,怎么简单怎么做。

# 千万级

背景需求:
随机生成千万数据用于测试和验证
1.利用现有的生产数据。

1. 统计现有生产环境的数据,若有千万级别数据的表则可以直接利用:
2. SELECT table_schema,table_name,table_rows FROM information_schema.tables WHERE table_rows >10000000;
3. 
4. 直接备份还原到测试环境 即可。

2.利用sysbench生成单表千万上亿的数据:

1. 这里使用的sysbench RPM安装包:
2. sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=172.16.1.81 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=xxxxxx --table_size=10000000 --tables=20 --threads=50 --time=240 --report-interval=20 --db-driver=mysql prepare
3. sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=172.16.1.81 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=xxxxxx --table_size=10000000 --tables=20 --threads=50 --time=240 --report-interval=20 --db-driver=mysql run
4. 
5. 注意这里的table_size指定单表的行数,tables指定生产表的个数;使用完测试数据后自己手动删除即可。

3.自己手写SQL代码生成千万数据。

1. 创建一个表存储0-910个数字,领完创建一个表用于存放千万级别的表数据:
2. CREATE TABLE a (i int);
3. INSERT INTO a(i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
4. 
5. create table bigtable(i bigint unsigned );
6. insert into bigtable(i)
7. SELECT
8. a.i*1
9. +a1.i*10
10. +a2.i*100
11. +a3.i*1000
12. +a4.i*10000
13. +a5.i*100000
14. +a6.i*1000000
15. +a7.i*10000000
16. AS id
17. FROM  a 
18. CROSS JOIN a AS a1
19. CROSS JOIN a AS a2
20. CROSS JOIN a AS a3
21. CROSS JOIN a AS a4
22. CROSS JOIN a AS a5
23. CROSS JOIN a AS a6
24. CROSS JOIN a AS a7;
25. Query OK, 100000000 rows affected (8 min 47.86 sec)
26. Records: 100000000  Duplicates: 0  Warnings: 0
27. --查询验证:
28. mysql> SELECT MIN(b.i),MAX(b.i),COUNT(1) from bigtable b;  
29. +----------+----------+-----------+
30. | MIN(b.i) | MAX(b.i) | COUNT(1)  |
31. +----------+----------+-----------+
32. |        0 | 99999999 | 100000000 |
33. +----------+----------+-----------+
34. 1 row in set (1 min 24.20 sec)
35. 
36. 
37. 在MariaDB10.210.3版本以及MySQL8.0.11版本中支持with语句后,上面的过程一条SQL语句即可搞定。
38. WITH a AS (
39. SELECT 1 AS i
40. UNION ALL SELECT 2
41. UNION ALL SELECT 3
42. UNION ALL SELECT 4
43. UNION ALL SELECT 5
44. UNION ALL SELECT 6
45. UNION ALL SELECT 7
46. UNION ALL SELECT 8
47. UNION ALL SELECT 9
48. UNION ALL SELECT 0)
49. ,b as (
50. SELECT
51. a.i*1
52. +a1.i*10
53. +a2.i*100
54. +a3.i*1000
55. +a4.i*10000
56. +a5.i*100000
57. +a6.i*1000000
58. +a7.i*10000000
59. AS id
60. FROM  a 
61. CROSS JOIN a AS a1
62. CROSS JOIN a AS a2
63. CROSS JOIN a AS a3
64. CROSS JOIN a AS a4
65. CROSS JOIN a AS a5
66. CROSS JOIN a AS a6
67. CROSS JOIN a AS a7)
68. SELECT MIN(b.id),MAX(b.id),COUNT(1) FROM b;
69. min(b.id)	max(b.id)	count(1)
70. 0	99999999	100000000
71. 耗时3min24sec。具体的消耗时间视电脑的性能。由于insert操作需要大量写入数据到磁盘,
72.insert之前可以临时关闭binlog文件


上次编辑于: 2021/5/20 下午3:26:49