MySQL调优--09--分页查询优化

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

分页查询优化

1.深度分页—>查询效率低

MyBatis–09–PageHelper插件分页 实现原理

2.分析

select * from table order by column limit 100, 10

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

3.优化的两种思路

1. 针对上述问题，主要有两种优化思路，第一种就是针对“按顺序取前100条”这个问题，那我是不是可以不用取完前面100条就能拿到第[100,110]条呢？方法的话比如改造limit 100,10这个sql，用where id > 100 limit 10，这样的话可以按 id主键索引先定位到哪个磁盘数据页，然后按顺序取10条数据就好了。
2. 第二种思路是针对“取大数据页到内存进行过滤”的问题，那我能不能把数据页做小，使得一个内存页能容纳更多条数据，从而减少磁盘IO次数？又或者我直接通过索引页来过滤，这样就不需要用原数据页来进行内存计算？这就是类似博主所述的方法，就是通过select id from table limit 100,10先分页查出id，再回表查询将这10个id的数据取出来就好了，因为id是主键索引，所以拿id来内存计算（因为id是主键索引，所以不涉及数据页的内存计算），就比拿一整页数据计算，IO次数要少的多了（甚至，存储引擎可能把id索引页都缓存到cache中了的话，压根都不需要硬盘IO了）。此外，order by的字段尽量要是索引字段，比如order by id，所以建表的时候考虑到要分页查询的话，尽量保证id的自增序就是分页的顺序/逆序，这样分页排序就能直接order by id了。

优化方法1

1.1 imit m语句实现上一页下一页

翻页查询中上一页和下一页功能实现，一般会记录查询的信息，比如当前页数、id最大值、id最小值和需要到的页数。

• 假设当前页面大小为m，当前页数为no1，则页面最大值为max=(no1+1)m-1，最小值为min=no1m，可以使用以下SQL实现上一页和下一页：

select * from sbtest1 where id>max order by id asc limit n;//下一页

select * from sbtest1 where id<min order by id desc limit n;//上一页

原理：利用上一页最后一个记录的某个唯一值（如主键或排序字段）作为下一页查询的起点，避免使用固定的OFFSET。

1.2 分页游标

优化方法2

2.1 子查询

2.2 使用inner join关联查询

其他方法