limit 10和limit 10000 10效率相同吗

先说结论：不相同，差异很大。

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页，往后的页面就不看了。

我们经常刷的抖音短视频的设计就是根基上面的代码，用户只能上滑下滑决定想看的视频，这样就能保证查询的效率，不会出现深度分页的情况。