MySQL查询性能优化—优化LIMIT分页

当数据量较大的时候，我们可能需要分批处理数据，首先想到的是使用limit加上偏移量的方法首先分页操作，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，mysql需要做大量的文件排序操作。

但是当偏移量非常大的时候，比如limit 10000, 20这样的查询，这时MySQL需要查询10020条记录后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。

优化此类分页查询的一个最简单的方法就是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询：

select id from blog order by id limit 1000, 50

如果这个表非常大，那么这个查询最好改成下面的样子：

select t1.id, t1.title
from blog t1
  inner join(
    select id from blog 
    order by id limit 1000, 50		
  ) as lim using(id)