摘要.
# 百万级
# 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-9共10个数字，领完创建一个表用于存放千万级别的表数据：
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.2和10.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文件