mysql 的 limit 优化

Question

下面两个语句

select phone from user where id >(select id from user limit 3000000,1) order by id limit 0,10;

select phone from user order by id limit 3000001,10;
测试多次，第一个总是比第二个好， 索引都是主键 id 为何会有区别？

Answer 1

我来说下原理吧

1、第一条括号内的sql因为只取了主键id的值，所以是直接根据id主键定位到3000000,1的一个id值，然后外层再根据主键范围索引取出10行整行记录获取phone值
2、这个sql是根据主键检索3000010条整行记录过滤掉3000000条取到phone的，所以差距很明显

Answer 2

手机不方便码字，直接贴链接了。

• 大数据分页方案

• MySQL 单表百万数据记录分页性能优化

• 分页查询的那些坑和各种技巧

Answer 3

第二个会直接查询3000010条，然后删掉3000000条，这样肯定慢了哈。
用第一种sql，只会查询10条

Answer 4

第一条select，你在id > (select id from user limit 3000000,1)里面没用order by，根本不用排序，当然快了。但是查出来的结果id不一定满足第二条select的要求。

Answer 5

首先应该理解 limit offset,n 的意思。
当offset非常大时，效率极低。mysql并不是跳过offset行，然后单取n行，而是取offset+n行，返回放弃前offset行，返回n行。
所以第一句比第二句速度快的原因关键不是因为用了索引覆盖，而是因为减小了 offset。当 offset 为 0 时，也可以不写 offset，直接写：
select phone from user where id >(select id from user limit 3000000,1) order by id limit 10;

这里有个疑问，如果已经确定要取 id > 3000001 的，为什么还要用(select id from user limit 3000000,1)这种方式？

至于 order by id , 如果 id 是已经是有序 id,不排序也可以，即使排序了，因为 id 是索引，排序方式是 using index，对查询速度的影响也是微乎其微的。

Answer 6

索引覆盖，索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做索引覆盖。

Answer 7

为什么用limit时，offset很大会影响性能