来源:blog.csdn.net/lvoelife/article/details/81943070

物理分页为什么用limit

在讲解limit之间,我们先说说分页的事情。

分页有逻辑分页和物理分页,就像删除有逻辑删除和物理删除。逻辑删除就是改变数据库的状态,物理删除就是直接删除数据库的记录,而逻辑删除只是改变该数据库的状态。例如

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(1)

同理,逻辑分页和物理分页是有区别的

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(2)

为什么逻辑分页占用较大的内存空间,比如我有一张表,表的信息是:

------------------------------ --Tablestructureforvote_record_memory ------------------------------ DROPTABLEIFEXISTS`vote_record_memory`; CREATETABLE`vote_record_memory`( `id`int(11)NOTNULLAUTO_INCREMENT, `user_id`varchar(20)NOTNULL, `vote_id`int(11)NOTNULL, `group_id`int(11)NOTNULL, `create_time`datetimeNOTNULL, PRIMARYKEY(`id`), KEY`index_id`(`user_id`)USINGhash )ENGINE=MEMORYAUTO_INCREMENT=3000001DEFAULTCHARSET=utf8;

向该表中插入300万条数据后,再转储到桌面,查看转储后的SQL文件的属性:

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(3)

这是多么庞大的数据,占用的内存多么可怕,为什么我们再选用数据库。这也是我们使用云服务器时,设定mysql的存储空间的大小。

我们一般不推荐使用逻辑分页,而使用物理分页。在使用物理分页的时候,就要考虑到limit的用法。

往期:一年内容,200期Java面试题阶段汇总


解释limit

limit X,Y ,跳过前X条数据,读取Y条数据


通过业务分析limit

SELECT cue.real_nameempName, zs.push_moneyASpushMoney, zs.push_money_noteASpushMoneyNote, zs.create_datetimeAScreateTime FROM zq_salaryzs//主表 LEFTJOINcore_user_extcueONcue.id=zs.user_id//从表on之后是从表的条件 WHERE zs.is_deleted=0 AND( cue.real_nameLIKE'%李%' ORzs.push_money_noteLIKE'%测%' ) ORDERBY zs.create_datetimeDESC LIMIT2; 就相当于 ORDERBY zs.create_datetimeDESC LIMIT0,2;

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(4)

limit的效率问题

--方法1 SELECT*FROMvote_record_memoryvrmLIMIT3600000,20000; --方法2 SELECT*FROMvote_record_memoryvrmWHEREvrm.id>=3600000LIMIT20000

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(5)

你会发现,方法2的执行效率远比方法1的执行效率高,几乎是方法1的九分之一的时间。

为什么方法1的效率低,而方法二的效率高呢?

因为在方法1中,我们使用的单纯的limit。limit随着行偏移量的增大,当大到一定程度后,会出现效率下降。而方法2用上索引加where和limit,性能基本稳定,受偏移量和行数的影响不大。

我们用explain来分析

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(6)

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(7)

可见,limit语句的执行效率未必很高,因为会进行全表扫描,这就是为什么方法1扫描的的行数是400万行的原因。方法2的扫描行数是47945行,这也是为什么方法2执行效率高的原因。我们尽量避免全表扫描查询,尤其是数据非常庞大,这张表仅有400万条数据,方法1和方法就有这么大差距,可想而知上千万条的数据呢。

往期:一年内容,200期Java面试题阶段汇总

能用索引的尽量使用索引,type至少达到range级别_,这不是我说的,这是阿里巴巴开发手册的5.2.8中要求的_

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(8)

我不用索引查询到的结果和返回的时间和方法1的时间差不多:

SELECT*FROMvote_record_memoryvrmWHEREvrm.id>=3600000LIMIT

20000 受影响的行: 0 时间: 0.196s

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(9)

这也就是我们为什么尽量使用索引的原因。mysql索引方法一般有BTREE索引和HASH索引,hash索引的效率比BTREE索引的效率高,但我们经常使用BTREE索引,而不是hash索引。因为最重要的一点就是:Hash索引仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询。

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(10)

如果是范围查询,我们为什么用BTREE索引的原因。BTREE索引就是二叉树索引,学过数据结构的应该都清楚,这里就不赘述了。

limit物理分页

我们都知道limit一般有两个参数,X和Y,X表示跳过X个数据,读取Y个数据,我们就此来查询数据

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(11)

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(12)

如果是SQL语句来进行分页的话,我们可以看到的是:

--首页 SELECT*fromvote_record_memoryLIMIT0,20; --第二页 SELECT*fromvote_record_memoryLIMIT20,20; --第三页 SELECT*fromvote_record_memoryLIMIT40,20; --第四页 SELECT*fromvote_record_memoryLIMIT60,20; --n页 SELECT*fromvote_record_memoryLIMIT(n-1)*20,20;

mysql是怎么分页的(面试官谈谈MySQL的limit用法)(13)

因而,如果是用java的话,我们就可以写一个方法,有两个参数,一个是页数,一个每页显示的行数

/** *@description简单的模拟分页雏形 *@authorzby *@paramcurrentPage当前页 *@paramlines每页显示的多少条 *@return数据的集合 */ publicList<Object>listObjects(intcurrentPage,intlines){ Stringsql="SELECT*fromvote_record_memoryLIMIT" (currentPage-1)*lines "," lines; returnnull; }

,