mysql分页查询优化,大数据量优化

传统的mysql分页查询

select * from table limit n , m
MySQL 执行此类SQL时需要先分页(默认一页1000条数据)通过全表扫描到N行，然后再去取M行。对于此类操作，获取前面少数几行数据会很快，但是随着扫描的记录数越多，SQL的性能就会越差，因为N的值越大，MySQL需要扫描越多的数据来定位到具体的N行，这样耗费大量的 IO 成本和时间成本。
特别是上线后数据量积累比较快,必须重视SQL优化,否则影响系统运行和用户使用体验
性能实验

1. 直接用limit start, count分页语句， 也是我程序中用的方法：

select * from table limit start, count

当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000， 10000,
30000开始分页的执行时间（每页取10条）。
如下：

select * from table limit 10, 10   0.000秒
select * from table limit 100, 10   0.000秒
select * from table limit 1000, 10   0.005秒
select * from table limit 10000,10   0.038秒
select * from table limit 30000, 10   1.160秒

可以告诉大家,做性能试验的表数据在30W+条,大家可以看出随着查询条数的增加,相对查询所需时间也增加了,这说明分页语句limit跟起始页码是有很大关系的,同样大家也看到查询起始页小时,查询时间为0秒,为什么会出现这样的情况?

这需要引入innoDB的缓存池概念

查看InnoDB缓存池大小:
show global variables like ‘innodb_buffer_pool_size’;
-------------------------±------------+
| Variable_name | Value |
±------------------------±------------+
| innodb_buffer_pool_size | 34359738368 |
±------------------------±------------+
默认InnoDB缓存池大小为128M
InnoDB缓存池用来存储各种数据的缓存，包括：
数据页，插入缓存，自适应索引哈希，索引页，锁信息，数据字典信息等。
InnoDB缓存池会把查询过的热点数据资源加载到缓存池,只要加载过到缓存池的数据,再次查询的时候直接从缓存池获取而不需要通过mysql查询,所以查询时间会出现0秒的情况.
相关的缓存知识,会单独开辟一篇博客

言归正传,在查询数据起始页越多的情况下,查询效率越慢,我们从缓存池配置知道,通过增加缓存池大小,把经常查询的数据加载到缓存池,可以起到优化查询的作用,但是数据本身的查询效率慢是否可以优化呢?

利用表的覆盖索引来加速分页查询

查看表索引:
show index from table;

+---------+------------+-------------+--------------+------