解决MySQL使用limit偏移量较大效率慢的问题

问题描述

在MySQL中，LIMIT OFFSET偏移量特别大时，效率会非常低

如果说LIMIT 1000,10，一个偏移量很小的值，一般是没有问题的。但是，比如说，LIMIT 10000000,10时就有些费劲了，让你等到花儿都谢了是没有问题的。

---

原因分析：

究其原因，是因为MySQL的查询并非先跳过10000000条，再查询10条，而是先查询再跳过。所以上面例子，要先查询出10000000行之后，再取10条，速度当然很慢，并且跳过的行数越多，会越慢。

---

解决方案：

• 方法A，使用索引
  可以使用 id > 10000000的方式诱导MySQL使用主键索引。

  这样的Where语句MySQL是非常喜欢并且乐于执行的，因为你给到MySQL的是一个范围，它最容易执行的就是有序和范围的查询，这对它来说易如反掌。

  当然，使用这个方法需要注意，id最好是连续的，中间的记录没有被物理删除过。如果其中有数据被物理删除过，用在分页场景的话，就看起来不是那么的优雅。当然，大部分数据库的参与者都倾向于逻辑删除。即便是物理删除的话，也可以使用程序去做区分显示，这不是问题。

• 方法B，迂回战术-延迟关联技巧
  这个方法在《高性能MySQL》中也有提到，所谓延迟关联（deferred join），就是通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。原理还是想方设法利用索引的速度，方可以柔克刚。

  假设，有一张表保存了学生的名字，然后要执行这样的sql：

  select id,name from student limit 1000000,10;

  这样执行的效率必然会很慢

  将SQL改写：

  SELECT id,name FROM student INNER JOIN (SELECT id FROM student LIMIT 1000000,10) stu ON student.id = stu.id;

  可以看到，在上述语句的子查询中，“SELECT id FROM student LIMIT 1000000,10”只查询了主键id一个字段，对于这样的索引覆盖情况，查询速度还是可以接受的。就等于说，该子查询只返回了10个id给上级查询。

  上级查询接收到这10个id之后，迅速的查询出了“name”字段，速度得到了极大提升。