上一篇：php redis pipeline怎么用(2019-06-23 20:53:38)
文章大纲

千万级数据下mysql分页优化+Laravel实战

2019-06-23 20:37:02

在文章开始之前，我先推荐一篇讲聚集索引、非聚集索引（也叫辅助索引）、覆盖索引的文章，算是比较通俗易懂的，就当是温习了。

https://blog.csdn.net/itguangit/article/details/82145322#


总结起来3句话：

  1. 聚集索引，是主键作为查询关键字，叶子节点含行记录数据
  2. 非聚集索引，是加索引的列的值作为关键字查询，叶子节点是主键值。获取整行记录数据，还需要根据找到的主键值进行1操作。
  3. 覆盖索引，避免像非聚集索引那样，还要进行1操作才能获取需要的数据，直接将要的数据列一起创建组合索引。

本文着重通过分析比对实践后的效果，从而达到分页优化的目标。通过分析认证来巩固自己对mysql理论的认知，而不是看网上大家说啥就是啥。
至于要从理论方面细致入微的分析为何效果得到了优化，本人理论储备暂时还不足够，日后若有所悟，再分享给大家。

为了方便实践，看优化后的效果，我们首先准备一千万条测试数据。
我是用mysql存储过程造的数据。
下面是我们测试将要用到的用户表：
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了，数据量大，插入要点时间，你可以先去吃个夜宵或睡个早觉。
call insert_users(0,10000000);
(注意不要在Navicat等客户端执行这个，不然很容易卡死)

我就是让它晚上跑的，用时接近3小时。不过这个造数据的速度优化，就不在本文拓展了。
Query OK, 0 rows affected (2 hours 48 min 13.58 sec)

数据准备好了，就让我们来写分页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条件的查询速度。 实际应用还涉及到关联表，也将一并实践学习。




上一篇：php redis pipeline怎么用(2019-06-23 20:53:38)
我要评论
评论列表