概述

分享一个最近自己写的简单实验，主要是为了验证一下mysql的几种高效分页设计实现方案，下面一起来看看吧！

1、创建表

创建一个测试表

create table t1
(c1 int(11) default NULL, 
c2 varchar(30) default NULL, 
c3 date default NULL ) 
engine=innodb;

2、插入数据

--停止binlog日志写入
set sql_log_bin=off;
或者SET @@SESSION.sql_log_bin = 0
--创建存储过程
delimiter //
CREATE PROCEDURE load_hwb()
begin
declare v int default 0;
 while v < 1000000
 do
 insert into t1 values (v,'testing fenye',adddate('2019-01-01',(rand(v)*36520) mod 3652));
 set v = v + 1;
 end while;
 end
 //
--插入100万数据
mysql> delimiter ;
mysql> call load_hwb();
 
--开启binlog日志写入
set sql_log_bin=on;
或者SET @@SESSION.sql_log_bin=1

说明：使用sql_log_bin这个参数是需要注意几点：

1)、千万不要不假思索的加上 global 修饰符（set global sql_log_bin=0)，这样会导致所有在Master数据库上执行的语句都不记录binlog

2)、INSERT、UPDATE、DELETE的SQL语句会导致Master和Slave数据库数据不一致，要谨慎操作。

3、建立索引

create index idx_id on t1(c1);

4、传统分页

mysql> select * from t1 order by c1 desc limit 0, 10
--在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量增加页数会越来越多，查看后几页SQL就可能类似：
mysql> select * from t1 order by c1 desc limit 999990,10;
--总之就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。

一开始是0.01s

到这里就要3.12s了。

5、limit的高效分页(直接走索引)

mysql> select * from t1 WHERE c1>=0 order by c1 limit 10;

可以看到输出结果一致，但基本秒出结果。

看执行计划已经走了索引。

6、limit的高效分页(id>=或<=)

子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

mysql> select * from t1 where c1 <= ( select c1 from t1 order by c1 desc limit 999990,1 ) limit 10;

注意观察耗时和执行计划。

7、limit的高效分页(join)

mysql> select * from t1 inner join (select c1 from t1 order by c1 desc limit 999990,10) as t using(c1);

8、limit的高效分页（程序实现）

因为是程序实现，这里就只提供想法了...

--程序先取c1 
select c1 from t1 order by c1 desc limit 999990,10;
select * from c1 where c1 in (0,1,2,3,4...);

9、limit分页与缓存结合

场景：在分页的时候，缓存5页记录，默认每页20条，缓存大小100

基本思路是：第一次查的时候，先判断缓存中存不存在当前页的数据，如果存在，查找缓存中的数据，如果不存在，则去数据库中查询5页数据放入缓存中，然后再缓存中读取数据。如果是当前页是前3页，那么读取1-5页的数据，如果当前页是后3页，读取后5页的数据，如果是中间页，读取当前页前两页+当前页+当前页后两页的数据；

类似百度的搜索界面，总共保留最新的10条记录，分10页。

分页样式类似如下：

后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~

如果你觉得这篇文章对你有帮助, 请小小打赏下~