MySQL分页查询优化

之前搬砖的时候遇到对行数大的表进行分页的操作，性能好差。最近在读《高性能MySQL》，正好讲到这个方面的，记录一下（基本上都是原文）。

优化LIMIT分页

在系统中需要进行分页才做的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候（翻页到非常靠后的页面），例如可能是LIMIT 10000,20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都被抛弃，这样的代价非常高。如果素所的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据，要优化这种查询，要么在页面中限制分页的数量，要么是优化大偏移量的性能。

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

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

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

mysql> SELECT film.film_id, film.description
    -> FROM sakila.film
    ->     INNER JOIN (
    ->         SELECT film_id FROM sakila.film
    ->         ORDER BY title LIMIT 50, 5
    ->     ) AS lim USING(film_id);

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

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果。例如，如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写为：

mysql> SELECT film_id, description FROM sakila.film
    -> WHERE position BETWEEN 50 AND 54 ORDER BY position 

对数据进行排名的问题也与此类似，但往往还会和GROUP BY混合使用。在这种情况下通常需要预先计算并储存排名信息。

LIMIT和OFFSET的问题，它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上一次取数据的位置，那么下一次就可以直接从该书签的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租借记录做翻页，那么可以根据最新一条租借记录向后追溯，这种做法可行是因为租借记录的逐渐是单调增长的。首先使用下面的查询获取第一组结果：

mysql> SELECT * FROM sakila.rental
    -> ORDER BY rental_id DESC LIMIT 20;

该技术的好处是无论翻页到多么后面，其性能都会很好。

优化SQL_CALC_FOUND_ROWS

分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint),这样做可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。看起来，MySQL做了一些非常高深的优化，像是通过某种方法预测了总行数。但实际上MySQL只有在扫描了所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描。所有该提示的代价可能非常高。(几年前本菜鸟在大学做项目的时候，就是用这个查询优化器的提示，以为这样会减少查询次数和扫描行数，后来我在工作后操作几百万行数的表的时候，这种方法性能很差)

一个更好的设计是将具体的页面换成“下一页”按钮，假设煤业显示20条记录，那么我们每次查询都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么我们就显示“下一页”按钮，否则就说明没有更多的数据，也就无需显示“下一页”按钮了。

一种做法是先获取并缓存较多的数据————例如，缓存1000条————然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，例如结果集少于1000，就可以在页面上显示所有的页面链接，因为数据都在缓存中，所以这样做性能不会有问题。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多余1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率高很多。

有时候可以考虑使用EXPLAIN的结果集中的rows列的值作为结果集总数的近似值（实际上Google的搜索结果总数也是个近似值）。当需要精确结果的时候，在单独使用COUNT(*)来满足需求，这时如果能够使用索引覆盖扫描则通常会比SQL_CALC_FOUND_ROWS快得多。