先说结论:不相同,差异很大。
set profiling = 1;
select * from xiatui order by id limit 10000,10;
select * from xiatui order by id limit 10;
show profiles;
select * from xiatui order by name limit 90000,10;
select * from xiatui order by name limit 10;
对比Query_ID为57和64的语句发现执行时间limit 10比limit 10000,10效率高了10倍。什么原因呢?这就要从limit的底层原理开始讲起。请继续往下看。
分页查询和深度分页的问题
select * from page order by id limit offset, size;
分页查询经常会遇到,在查找网站的时候肯定遇到分页的信息,比如百度信息,查找到的信息会分为很多页展示给用户,而不是一股脑的放在一个页展示给用户(一股脑的展示会导致查询全部表信息,效率特别慢,为了解决这种情况才发明了分页查询,但是分页查询会导致深度分页,什么是深度分页呢?往下看)。
大家仔细观察上面这张图,百度解决深度分页查询的方式就在其中。
分页查询语句的解释
上述语句的意思
如果我们前端规定一个页有10条数据,下面的语句就是查询第一页的全部数据。
select * from xiatui order by id limit 10;
一个页有十个数据,第10页就是从第90行取十个数据。
select * from xiatui order by id limit 90,10;
那limit 10000,10就是第1001页。
limit 10000,10和limit 10的执行过程
还是上面的问题limit 10000,10和limit 10为什么查询效率不同?
limit 10的执行过程
limit 10就相当与limit 0,10
主键索引的limit执行过程
limit 10
explain
select * from xiatui order by id limit 10;
可以看到执行计划:主键索引,rows=10,说明这条语句走了主键索引,并且只扫描了10行数据,可想而知就是前十条数据。
具体流程:server层调用存储引擎层innodb接口,在innodb的主键索引获取第0行完整数据返回给server层,放到server层的结果集,之后继续调用接口,获取主键索引的第1行数据返回,依次获取10条数据,放到结果集,返回给客户端。
limit 10000,10
explain
select * from xiatui order by id limit 10000,10;
可以看到执行计划:主键索引,rows=10010,说明走了主键索引,扫描了10010行数据。这是为什么呢,我们只要第10000开始的十条数据,为什么扫描这么多行,这就是mysql优化器存在的缺陷,想要获取第10000行数据,只能从头开始扫描,直到扫描到第10000行数据。
有的人脑子转的快就会想,为什么不通过索引直接找到值为10000的数据?
nonono,这就理解错了,第10000行数据和id为10000是有很大区别的,这两个概念千万别混淆,索然说可能你设置了主键自增,从0开始可能都对上了,但是删除元素呢,况且并不是所有情况都是主键自增。所以mysql只能通过从头扫到想要开始的行(这期间维护了一个count值,扫描下一个就增加1,直到count=想要找到的行)
集体流程:server层调用innodb接口,和上面一样的流程,不一样的就是会在主键索引中依次获得第0行到第10010行的完整记录,依次返回给server层,由server层根据offset值挨个过滤,只留下符合条件的数据,依次放到结果集,返回给客户端。
问题解答
看完基于主键索引的limit可以回答上面的问题,为什么limit 10000,10比limit 10慢?
是因为limit 10000,10会取出10010条数据,由server过滤丢弃10000条记录,这部分消耗很大。
优化方法
那关于limit 10000,10效率慢有什么方法进行优化呢?
我们可以将上面语句改成
select * from xiatui x inner join
(select id from xiatui order by id limit 10000,10) c
on x.id = c.id;
或者
select * from xiatui where id > (select id from xiatui order by id limit 90000,1) order by id limit 10;
可以看到执行时间相对于原语句变快了很多。
上述两条语句的解决方法原理就是利用我上面提到过的问题:为什么不通过索引直接找到值为10000的数据?原因已经在上面做出解答,这两个语句就是利用索引去主键索引中定位到开始数据的行。
子查询语句中的子查询就是找到第90000行数据,有些人就会有疑问这不也得依次从头找到第90000行吗,没错子查询确实是从第0行找到90000行,但是和select *有区别的是select id,这样直接就可以index扫描索引树,取到id值就直接返回数据给server层(而不用返回所有的数据),由server进行过滤,返回的数据量大大减少。
表连接的意思就是先获取结果集的id值生成派生表,之后和原表进行id的关联,这样就可以获取所属id的数据,返回数据给server层。
基于非主键索引的limit执行过程
limit 10
explain
select * from xiatui order by name limit 10;
执行过程:server层调用innodb端口,辅助索引name中获取第0条数据返回主键id,回表找到完整的行数据,返回给server层,放到结果集中,之后依次取10条。
当offset > 0,且offset的值较小的时候,也是这个流程,区别就是会丢弃offset之前的数据。
explain
select * from xiatui order by name limit 100,10;
非主键索引的limit过程比主键索引的limit过程,多了徽标的消耗。
limit 10000,10
当offset变得非常大时,执行计划变成全表扫描。
这和上面的有所不同,全表扫描,rows=93903,这就是表中的全部数据,为什么要扫描全部的数据,而不是从头扫到需要的数据行?
nonono,这也是个误区,既然走了全表扫描,order by name,说明语句需要根据name列进行排序,如果没有走name的辅助索引,而是全表扫描主键索引,主键索引不是的name不是有序的,所以就需要扫描全部数据,应用filesort文件进行排序,所以这是它rows=count(*)的原因。而这个原因也就是order by id 和 order by name的区别。
根据上面说的当offset过大的时候会造成效率很低。
优化方法
select * from xiatui x inner join
(select id from xiatui order by name limit 90000,10) c
on x.id = c.id;
可以看到优化过后的语句效率有显著的提升。
仔细分析上面的优化语句,在何种方面进行了优化?没错避免了回表,注意是避免而不是减少。我们来分析下执行过程:辅助索引依次返回90010条数据,不用回表直接返回给server层,再由server层进行把90000前的数据过滤掉。
总结
不管是基于主键索引还是基于非主键索引,都不能避免从头扫到需要的数据行这个消耗,这也会产生上面提到的深度分页问题。
深度分页问题和各大厂的问题解决
深度分页问题到底是什么?没错limit 10000,10这就是一个深度分页问题。
我们再访问网站的时候,应该遇到过数据分页展示的情况,我们如果想看第5页的就按前端 的第五页面,如果我们想要看第10000页,有的网站还会给用户搜索栏输入10000回车就可以看到第10000页的数据,但是这就产生了深度分页问题。会从第0行扫描到第10000页开头的行,这无疑是很消耗时间的,甚至如果不是使用上述提到的优化语句就算是limit 10000,10 执行计划是全表扫描,这无疑是很浪费时间的。这个问题怎么解决?无解,使用上面的优化语句已经是在语句方面的最高效的方式了。
那个大厂是怎么处理的呢?
用我的话总结:用户没有权力去规定一个页面的数据量,这就是后端定义好也就是写死的。
查看符合条件的全部数据也就是全表扫描,如果用户不能规定offset和size的大小,这两个变量都是由后端代码规定,那效率被影响的几率就不会很大。我们来后端的看看伪代码。
int start = 0;//这是定义每个页起始的行数据id
for{
datas = select * from table where id > start order by id limit 100;
if(datas.length() == 0) break;
fanhuishuju(datas);
start = get_max_start(datas);
}
这样就关于offset的设置就循环由代码进行决定,也就是start就是开始的行,注意start是主键值,这是由主键值推出起始行的经典代码。
上述说过一个情况——搜索栏输入哪页就看哪页这样的方式有必要存在吗,这样只会拉低效率,所以百度的时候没有这个搜索框,而且只有五个页面供你选择,剩下的只能慢慢的去翻页。其实我们在百度的时候大多也就看前3页,往后的页面就不看了。
我们经常刷的抖音短视频的设计就是根基上面的代码,用户只能上滑下滑决定想看的视频,这样就能保证查询的效率,不会出现深度分页的情况。