015 MySQL 中 limit 分页偏移量过大导致查询慢的优化

学软件技术，读第一手资料，去官方网站：MySQL 5.7参考手册

也可以看看比较经典的书籍，如《高性能MySQL》等

1、简单回顾MySQL中的 limit 的用法

假如你的需求是：从第几条开始总共返回多少条。这时候你将会用到MySQL中的limit。

语法：SELECT * FROM table LIMIT [offset,] rows

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目： 
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

2、MySQL中用 limit 做分页

在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。
一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT 10000 ,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10000条记录都将被拋弃，这样的代价非常高（查询会比较慢，或者某些情况下很慢）。

要优化这种查询，（1）要么是在页面中限制分页的数量，（2）要么是优化大偏移量的性能。

2.1 在页面中限制分页的数量，例如百度的做法，百度搜索出来的结果集最大的分页数是76页

2.2  优化大偏移量的性能之一，索引覆盖+关联查询

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联查询操作再返回所需的列。对于偏移量很大的时候，这样做的效率会提升非常大。考虑下面的查询:
mysql> SELECT film. id, description FROM sakila.film ORDER BY title LIMIT 50000, 5;
如果这个表非常大，那么这个查询最好改写成下面的样子:

mysq1> SELECT film.film_id, film.description
 FROM sakila.film 
 INNER J0IN (
 SELECT film_id FROM sakila.film
 ORDER BY title LIMIT 5000, 5
  ) AS lim USING(fi1m_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

2.3  优化大偏移量的性能之二，假如一个位置列上有索引并且可以计算出边界值

这时候可以写出如下sql

mysql> SELECT fi1m_id, description FROM sakila.film
       WHERE position BETWEEN 5000 AND 5004 ORDER BY position;

2.4 优化大偏移量的性能之三，id自增的前提下，在大的数据量面前使用where id>offset 来代替使用limit offset：

-- 同样的效果
select * from notes limit 1000000,3;
 
select * from notes where id>1000000 limit 3;