mysql实现分页功能

概述

分享一个最近自己写的简单实验，主要是为了验证一下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--创建存储过程ｄｅｌｉｍｉｔｅｒ //CREATE PROCEDURE load_hwb()begindeclare v int default 0; while v < 1000000 do ｉｎｓｅｒｔ into t1 values (v,‘testing fenye‘,adddate(‘2019-01-01‘,(rand(v)*36520) mod 3652)); set v = v + 1; end while; end //--插入100万数据mysql> ｄｅｌｉｍｉｔｅｒ ;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)、ｉｎｓｅｒｔ、ｕｐｄａｔｅ、ｄｅｌｅｔｅ的SQL语句会导致Master和Slave数据库数据不一致，要谨慎操作。

3、建立索引create index idx_id on t1(c1);

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

一开始是0.01s

到这里就要3.12s了。

5、limit的高效分页(直接走索引)mysql> ｓｅｌｅｃｔ * from t1 WHERE c1>=0 order by c1 limit 10;

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

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

6、limit的高效分页(id>=或 ｓｅｌｅｃｔ * from t1 where c1 ｓｅｌｅｃｔ * from t1 inner join (ｓｅｌｅｃｔ c1 from t1 order by c1 desc limit 999990,10) as t using(c1);

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

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

--程序先取c1 ｓｅｌｅｃｔ c1 from t1 order by c1 desc limit 999990,10;ｓｅｌｅｃｔ * 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方面的内容，感兴趣的朋友可以关注下~

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