sql分页:mysql实验--如何进行分页设计可以让sql更高

 2021-07-01 14:21    77  

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

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

1sql分页、创建表创建一个测试表

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

2sql分页、插入数据--停止binlog日志写入set sql_log_bin=off;或者SET @@SESSION.sql_log_bin = 0--创建存储过程delimiter //CREATE PROCEDURE load_hwb()begindeclare 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这个参数是需要注意几点:

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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语句的偏移量就会越大,速度也会明显变慢。

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

一开始是0.01s

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

到这里就要3.12s了。

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

5、limit的高效分页(直接走索引)mysql> select * from t1 WHERE c1>=0 order by c1 limit 10;可以看到输出结果一致,但基本秒出结果。

sql分页:mysql实验--如何进行分页设计可以让sql更高效?

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

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方面的内容,感兴趣的朋友可以关注下~

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

本文标签:sql分页进行

原文链接:https://www.xgfox.com/bcrm/241.html

本文版权:如无特别标注,本站文章均为原创。