MySQL分页查询优化

1、现象

将表中数据全部查出来缓存到redis中，表中数据量约百万级，表的id是自增的。

使用select * from table order by id limit m,n 分页查询出数据缓存到redis，当翻到比较靠后的页时，查询效率会越来越慢

2、原因

这种方式分页是从磁盘拿整一个数据页到存储引擎缓冲区，比如 limit 100000, 10 那就要按顺序先取出前100000条数据，跳过了offset后再拿出第[100000,100010]这10条数据，如果这条数据字段很多且体积比较大，那么数据页就会比较大，我们知道存储引擎内存页大小是有限制的，如果数据页较大，那可能一次内存页只能存一两条数据，那么每次磁盘IO只能取到一两条，所以要翻掉前100000条，那就可能需要非常多次的IO，这就是为什么慢的原因。

3、优化思路

思路一：针对“按顺序取前100000条”这个问题 , 使用id限定优化

通过id限定可以不用取完前面100000条就能拿到第[100000,100010]条，比如改造limit 100000,10这个sql，用where id > 100000 limit 10，这样的话可以按 id 主键索引先定位到哪个磁盘数据页，然后按顺序取10条数据就好了

例： select * from table where id > 100000 limit 10;

思路二：针对“取大数据页到内存进行过滤”的问题

把数据页做小，使得一个内存页能容纳更多条数据，从而减少磁盘IO次数，又或者直接通过索引页来过滤，这样就不需要用原数据页来进行内存计算。就是通过select id from table limit 100000,10先分页查出id，再回表查询将这10个id的数据取出来就好了，因为id是主键索引，所以拿id来内存计算，就比拿一整页数据计算，IO次数要少的多了（甚至，存储引擎可能把id索引页都缓存到cache中了的话，压根都不需要硬盘IO了）。此外，order by的字段尽量要是索引字段，比如order by id，所以建表的时候考虑到要分页查询的话，尽量保证id的自增序就是分页的顺序/逆序，这样分页排序就能直接order by id了。

例： select * from ctr_manage JOIN (select id from ctr_manage order by id  limit 100000, 10) b using (id) ;

4、按思路一优化后方案

将表中数据全部查出来缓存到redis中，表中数据量约百万级，表的id是自增的

步骤1：查出最小的id

步骤2：每次查询出5000条(条数可以自定义，在一个合理的范围就行)数据，查询语句

SELECT * FROM TABLE WHERE ID > (最小id)  order by id limit 5000

如果查出来的集合不为空，将查出来的数据缓存到redis中，并将查出来的数据的最后一条记录的id记为最小id,重新进入步骤2

如果查出来的集合为空，说明遍历处理完成，结束