limit 10 offset 1000000为什么会很慢

一、前言

相信在后台开发得过程中，都会遇到分页问题，一般sql语句用limit实现分页。

一般刚开始学SQL的时候，会这样写：

SELECT * FROM table ORDER BY id LIMIT 1000, 10;

但在数据达到百万级的时候，这样写会慢死

SELECT * FROM table ORDER BY id LIMIT 1000000, 10;

也许耗费几十秒

二、查询慢原因

但是为什么数据量达到百万级别后分页会这么慢呢？

limit100 0000,20的意思扫描满足条件的1000020行，扔掉前面的1000000行，返回最后的20行，问题就在这里。一般得sql语句肯定不知一个limit函数，where条件还有很多负责得条件过滤，甚至分组，聚合函数等等，所以扫描满足条件得数据量太大，如果不采取优化得措施，一定会把你服务器卡死的。

LIMIT 451350,30 扫描了45万多行，怪不得慢的都堵死了。

但是limit 30 这样的语句仅仅扫描30行。

三、limit查询慢的优化

1、网上很多优化的方法是这样的

SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;

是的，速度提升到0.x秒了，看样子还行了
可是，还不是完美的！

以下这句才是完美的！

SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;

比上面那句，还要再快5至10倍

另外，如果需要查询 id 不是连续的一段，最佳的方法就是先找出 id ，然后用 in 查询

SELECT * FROM table WHERE id IN(10000, 100000, 1000000...);

2、再举个例子：
日常分页SQL语句：
select id,name,content from users order by id asc limit 100000,20
扫描100020行

如果记录了上次的最大ID

select id,name,content from users where id>100073 order by id asc limit 20

扫描20行。

总数据有500万左右，以下例子

select * from users where name=‘张三’ order by id limit 300000,10 执行时间是 3.21s

优化后：

select * from (

select id from users

where name=‘张三’ order by id limit 300000,10

) a

left join users b on a.id=b.id

执行时间为 0.11s 速度明显提升

这里需要说明的是 我这里用到的字段是 name ,id 需要把这两个字段做复合索引，否则的话效果提升不明显。

四、总结

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

sql示例：

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id 
limit 900000, 10);  

如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment
sql示例：

  SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

SELECT * FROM users WHERE uid >=  (SELECT uid FROM users ORDER BY uid limit 895682, 1) 
limit 0, 10;  

如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户

复合索引：

开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引，可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试

select id from collect where vtype=1 limit 90000,10; 非常快！0.04秒完成！

再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾，8-9秒，没走search索引！

再测试：search(id,vtype)，还是select id 这个语句，也非常遗憾，0.5秒。

综上：如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！