MySQL大分页，LIMIT优化

一、场景说明

SELECT * from forlan_table ORDER BY id LIMIT 55000,10;

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的化需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。
要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能
化此类查询的最简单的办法就是尽可能地使用覆盖索引，而不是查询所有的列

二、优化方式

说明：表数据量6W

1、限定id范围

SELECT * from forlan_table WHERE id> 55000 ORDER BY id LIMIT 10;

优点：效率非常高
缺点：id必须是连续的，不能有where条件（会过滤数据），非id升序场景不适用
时间: 0.009s

2、覆盖索引

思路：先查出id，作为IN条件
（1）直接IN

SELECT * FROM forlan_table WHERE id IN(SELECT id FROM forlan_table ORDER BY id LIMIT 55000,10);

执行报错：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
原因：当前MySQL版本，IN里面的语句不能有LIMIT

（2）IN里面的查询再包装一层

SELECT * FROM forlan_table WHERE id IN(SELECT T.id FROM (SELECT id FROM forlan_table ORDER BY id LIMIT 55000,10) T);

时间: 0.242s

（3）使用伪表的方式，进行表连接操

SELECT * FROM forlan_table INNER JOIN (SELECT id FROM forlan_table ORDER BY id LIMIT 55000,10) T USING(id);

SELECT * FROM forlan_table A INNER JOIN (SELECT id FROM forlan_table ORDER BY id LIMIT 55000,10) T ON A.id = T.id;

时间: 0.037s
说明：上面两种写法是等同的，using主要用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用该列作为join的条件