MySQL中Limit性能优化的方案

在MySQL中Limit有两种语法：

• limit offset, rows
• limit rows

其中offset表示偏移量，rows表示要返回的记录条数。比如我们要查出第1000到第1010行的记录，就会写成 limit 999, 10 。一般我们在实际开发中做分页查询的时候，rows不会太大，因为rows表示每页要显示的数量（你总不可能每页要显示个几万条吧）。而随着页数的加深，offset就会变大，查询效率随之就变慢了。
为什么当offset很大时，查询效率会变慢呢？
比如当我们用 limit 1000000, 10 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。

我们先准备一张tb_test表

表中有1000+W行数据

mysql> select count(*) from tb_test;
+----------+
| count(*) |
+----------+
| 11075415 |
+----------+
1 row in set (1.15 sec)

我们先试一下 最常规的写法

select * from tb_test where val = 4 limit 100000, 5;

耗时1秒多，然后我们试试两种优化方案。

如果主键id有序，可以用子查询优化

select * from tb_test 
where val = 4 and id >= (select id from tb_test where val = 4 limit 100000, 1) 
limit 5;

如果主键id是无序的，可以用inner join来优化

select t.* from tb_test t 
inner join (select id from tb_test where val = 4 limit 100000, 5) tmp 
on t.id = tmp.id;

好像优化后的SQL查询效率也没提高多少？？？
别急，接下来我们给val字段添加上索引，再来测试一下

效率差距立马就体现出来了有木有。
所以我们可以得出结论：如果要优化limit查询的话，where条件中的字段一定要有索引。

PS：如果是手机端的话，我们可以让前端每次都把当前页的最大id（或者最小id）传过来，我们的查询中就可以直接使用下面这样的SQL语句。

select * from tb_test where id > ? and val = ? limit 5

这种SQL语句在id是有序且不会跳页的业务场景下是非常合适的，但是PC端一般是允许跳页的，就不太合适了