mysql分页查询优化

MySQL分页查询原理

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。【阿里巴巴JAVA开发手册】

// 查询样例，查询不同的条数效率完全不一样

select * from employees limit 10000,10; ## 耗时：0.007s

select * from employees limit 100000,10; ## 耗时：0.031s

select * from employees limit 1000000,10; ## 耗时：0.42s

select * from employees limit 3000000,10; ## 耗时：1.2s

分页查询优化方式

1.基于主键ID实现查询优化分页查询优化

EXPLAIN select * from employees where id>3000003 limit 10; ##耗时：0.0005s

通过id定位到3000003数据，然后通过offset值取得想要的结果，效率提高不是一点点。

原理：

a、先查询到主键ID的索引

b、通过索引ID关联employees表

优化缺陷：如果主键不是自增长的(如uuid方式)，则此方法失效。

2.基于主键ID实现查询优化分页查询优化

select * from employees e inner join (select id from employees limit 3000000,10) ed on e.id = ed.id; ##耗时：0.69s

原理：select id from employees limit 3000000,10语句直接走索引，快速定位所需要的id，然后通过id回表查询，比原先的语句少了很多的数据，而且走索引，速度会快很多。

3.分表分库

尽管上面两种方法可以优化查询的速度，但数据表的数据量超过500W的时候，还是建议使用分表分库，将数据拆分到不同的表中，减少数据行和索引的量也会大幅度提高查询效率。