MySQL分页查询优化

 

 

背景：

     使用分页是不可避免的，通常情况下我们的 做法是使用limit加偏移量： s elect * from table where column=xxx order by xxx limit 1,20 。 当数据量比较小时（100万以内），无论你翻到哪一页，性能都是很快的。如果查询慢，只要在 where条件和order by 的列上加上索引就可以解决。但是，当数据量大的时候（小编遇到的情况 是500万数据），如果翻到最后几页，即使加了索引，查询也是非常慢的。

 

limit分页原理

    当我们翻到最后几页时，查询的sql通常是： select * from table where column=xxx order by xxx limit 1000000,20 。 查询非常慢。但是我们查看前几页的时候，速度并不慢。这是 因为limit的偏移量太大导致 的。 MySql使用limit时的原理是（用上面的例子举例）：

1. MySql将查询出1000020条记录。
2. 然后舍掉前面的1000000条记录。
3. 返回剩下的20条记录。

 

 

解决方法：

1、 尽量使用索引覆盖扫 描 ，就是我们select后面检出的是索引列，而不是 所有的列，而且这个索引的列最好是id，然后再做一次关联查询返回所有的列 。 上述的sql可以写成：

SELECT * FROM table AS t
INNER JOIN (
      SELECT id FROM table WHERE xxx_id=14833 LIMIT 1000000,20
) AS t1 ON t.id = t1.id;

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
改成
SELECT film.film_id,film.description FROM film 
INNER JOIN (
      SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询所需要的列。

2、书签记录位置

    有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：

SELECT id FROM t LIMIT 10000, 10;

改为：

SELECT id FROM t WHERE id > 10000 LIMIT 10;

其它优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。

 

 

参考：

https://www.jianshu.com/p/efecd0b66c55/MySQL limit分页的一次优化