上一篇：钗头凤(2019-06-26 23:49:49)
文章大纲

mysql如何快速生成千万级测试数据

2019-06-30 08:30:51
打算结合实践分析验证mysql索引工作的原理，所以先在mysql里学习着准备好千万数量级的数据。

本文我是用mysql存储过程造的数据。
为了提高数据的丰富性，除了text类型暂时没包含进来，其它数值型、字符串型、日期型皆有包含。

下面是我生成测试数据用到的用户表：
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`email_verified_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`password` VARCHAR(255) NULL DEFAULT NULL,
`remember_token` VARCHAR(20) NULL DEFAULT NULL,
`created_at` TIMESTAMP NULL DEFAULT NULL,
`updated_at` TIMESTAMP NULL DEFAULT NULL,
`score` DECIMAL(10,4) NULL DEFAULT '0.0000',
`cate_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

为了提高数据的多样性，我还另外创建了两个方法。
生成指定长度的随机字符串：
delimiter //
create function rand_string1(n INT)
returns varchar(255) charset utf8
begin
  declare chars_str varchar (100) default 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz';
  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 ;

生成随机的时间字符串：
delimiter //
create function getTime()
returns timestamp
begin
return ( select concat( 2009+floor(rand()*10) ,'-',lpad(1+floor(rand()*11),2,0),'-',floor(1+rand()*28),' ', floor(8+rand()*10),':',floor(10+rand()*49),':',floor(10+rand()*49)) );
END //
delimiter ;

创建数据的存储过程：
delimiter //
CREATE PROCEDURE insert_users(IN start int(10), IN max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
    set i = i+1;
    insert into users(name,email,password,remember_token,created_at,score,cate_id) 
        values(rand_string(20), rand_string(30), rand_string(64), rand_string(20), getTime(), 50+floor(rand()*50), floor(rand()*100) );
    until i = max_num
end repeat;
commit;
end //
delimiter ;

进入Windows dos窗口，用命令连上数据库，然后调用存储过程就ok了。
(注意不要在Navicat、Heidisql等客户端执行这个，不然很容易卡死)

初次我什么优化都没做，直接调用存储过程，耗时如下：
call insert_users(0,10000000);
Query OK, 0 rows affected (2 hours 48 min 13.58 sec)
我只是在睡前留下命令，让它晚上自个儿跑的，用时接近3小时。

为了提高mysql插入测试数据的速度，我在mysql配置文件里将下面参数改为了0
innodb_flush_log_at_trx_commit = 0

重新建了user1表和insert_users1的存储过程，然后生成千万数据耗时如下：


为何要改这个参数？这还得从mysql的innodb引擎私底下是如何工作的来说。
因为数据的安全，mysql每次执行语句都会有日志记录的数据。
但是又为了提高mysql的性能，这些数据平常都是写在redo log buffer缓冲区的，而没有直接写往磁盘。因为往磁盘写的速度比往缓冲区写的速度慢。
说回来，数据又不够安全了，因为log数据写在log buffer缓冲区里，一旦Mysql服务崩掉，还未来得及同步写到磁盘的log数据就会丢失。所以mysql又安排了几个当差的来处理磁盘写这个任务。

有哪些当差的呢？
1.mysql的主线程（Master Thread）就是其中之一，每秒都会将buffer缓冲区里的日志缓冲(log buffer)同步写到磁盘。其实这个差员手头上还有很多其它活儿要干，具体大家可参考《Mysql技术内幕-InnoDB存储引擎》里Master Thread这一章节。
2.另外一个就是这里我们提的innodb_flush_log_at_trx_commit参数了。
看字面意思，就是说，是否在每次事务提交的时候，将日志缓冲同步写到磁盘。

变换到生活中快递员发快递的场景中来看，无非就是一个地区有仓库，快递来了你只管往仓库里丢，快递员自然会一天24小时的取走仓库里这些快递，然后送到收货人手上；另一个地区业务不多，没有仓库，只要有快递来，快递员就会立马去送。这里的仓库，就相当于我们上面所说的日志缓冲区。





数据准备好了，就让我们来写分页sql语句，并分析优化。
一开始先执行以下命令看看效果：
mysql> select * from users limit 1 offset 100000;
1 row in set (0.28 sec)

mysql> select * from users limit 1 offset 1000000;
1 row in set (0.75 sec)

mysql> select * from users limit 1 offset 5000000;
1 row in set (3.71 sec)
可以看出，越往后面查，同样的记录条数，所花费的时间越长。
这个语句并没有用到索引，所以是按顺序来查找，要经过百万的数据自然是比经过一千条数据费时要长，就像同样的跑步速度，马拉松自然耗时比3km要长一样。

有没有用到索引，在sql语句前加上explain就好了。
mysql> explain select * from users limit 15 offset 5000000;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8909863 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+

上面查询仅仅只是获取指定数目的数据记录，我们一般实现分页，还需要计算结果记录的总条数。
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
| 10000030 |
+----------+
1 row in set (17.12 sec)
多次执行上面命令，发现第一次耗时长，后面都是4、5秒左右。
没有加where条件时，速度几乎算是最快的，仍耗时十几秒，这在网站应用中肯定是不能接受的。
所以本文的目的，就是加快上面mysql语句以及count语句在where条件的查询速度。 实际应用还涉及到关联表，也将一并实践学习。




上一篇：钗头凤(2019-06-26 23:49:49)
我要评论
评论列表