笔记MySQL

MySQL如何快速构建500w测试数据的大表？

Posted by jintang on 2014-05-12

最近学习innodb的索引原理，想要看看索引的效果，但是发现没有大表，很多东西没法试，公司只能拿到测试数据，但是数据量都不是很大。怎么办呢？

第一时间想到用php写一个程序，一直跑insert语句，但是想想这个过程：

  1. php连接mysql
  2. 发送指令给mysql
  3. mysql解析执行

这过程肯定非常慢的。

为什么不直接在mysql上进行呢？这时想到使用存储过程

用户表

以user为例：

1
2
3
4
5
6
7
8
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `phone` char(11) NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `level` tinyint(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8

添加随机函数

为了让name不重复，使用一个rand_string函数来获得随机字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter $$
create function rand_string(n int)
returns varchar(255) 
begin
    declare chars_str varchar(100) default 'abckefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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()*52),1));
        set i = i + 1;
    end while;
    return return_str;
end$$
delimiter ;

插入数据的存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
delimiter $$
create procedure insert_user(in max_num int(10), in phone_prefix char(3), in lv int(3))  
begin                                                         
    declare i int default 0;                                  
    set autocommit = 0;                                       
    repeat                                                    
        set i = i + 1;                                        
        insert into user (name, phone, age, level) values     
        (rand_string(10), concat(phone_prefix, floor(10000000+rand()*10000000)), floor(rand()*100), lv);                
        until i = max_num                                     
    end repeat;                                               
    commit;                                                   
end$$
delimiter ;

phone_prefix为了可以得到不同的电话前缀，如：137，135等
lv为了设计不同的等级，如100, 200等

使用脚本分段执行

有了存储过程，我们当然可以调用一次insert_user()来完成500w调数据，但是一次性太大比较容易出问题

使用分段的方式，既可以设定不同的电话前缀和等级，方便测试，也可以插入数据比较灵活。

编写insert_user.sh脚本:

1
2
3
4
5
6
7
8
9
10
11
12
#!/bin/bash

for i in {1..5}
do
    phone_prefix=(135 137 187 150 138)
    pp=${phone_prefix[$i-1]}
    rows=1000000
    level=$((i*10))
    echo "no.${i} insert ${rows}..."
    time mysql -uroot -ppass -e "use test; call insert_user(${rows}, ${pp}, ${level})"
    echo -e "\n"
done

执行结果

服务器信息：

1
2
2核 Intel(R) Core(TM) i7-8850H CPU @ 2.60GHz
4G 内存
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@node2 ~]# sh insert_user.sh 
no.1 insert 1000000...
real	0m54.238s
user	0m0.003s
sys	0m0.001s

no.2 insert 1000000...
real	0m53.788s
user	0m0.002s
sys	0m0.001s

no.3 insert 1000000...
real	0m55.424s
user	0m0.003s
sys	0m0.000s

no.4 insert 1000000...
real	0m55.998s
user	0m0.002s
sys	0m0.003s

no.5 insert 1000000...
real	0m56.000s
user	0m0.000s
sys	0m0.003s

总共耗时5分钟，插入500w条数据，平均1分钟100w，算很快吧。

验证一下：

1
2
3
4
5
6
7
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (0.96 sec)

可以愉快的玩耍了！