Mysql之LIMIT-OFFSET查询时间过长处理办法

limit-offset查询时间过长原理

近期接触了一个百万级别的表，普通的单表列表查询竟然>10s,如下图，常见limit-offset分页查询一条数据竟然都高达2s,如下面所示，

MySQL [yuetong1]> SELECT * FROM pa_order LIMIT 1 OFFSET 800000;
//查询类容不展示了
1 row in set (2.04 sec)

interesting，仅仅是一个单条数据查询就高达2.04 sec。没办法，只能优化这一块的代码了。

首先，我们要明白的是一个问题就是为什么LIMIT-OFFSET要消耗这么长的时间。 我们先看下上面那条SQL语句的查询过程是怎么样的。

1. 查询到索引叶子节点的数据

2. 根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

如下图所示

像上面这样，需要查询800001次索引节点，查询800001次聚簇索引的数据，最后再将结果过滤掉前800000条，取出最后1条。MySQL耗费了大量磁盘I/O在查询聚簇索引的数据上。所以查询时间就会变长。

优化

知道了原因，我们就可以目的性的优化了，最主要的目的就是不让它进行前面800000万次的无理由的I/O消耗就好了。

第一个假如是主键正序或者倒序排序的话就可以使用

MySQL [yuetong1]> SELECT * FROM pa_order WHERE id > 800000 LIMIT 1;
//查询类容不展示了
1 row in set (0.01 sec)

这种直接限定了主键的查询集合是800000往后的，所以只查询一次索引节点

第二个是假如有条件查询和排序各式各样的话推荐使用inner join查询

MySQL [yuetong1]> select * from pa_order as a inner join (select id from pa_order LIMIT 1 OFFSET 800000) as b on a.id=b.id;
//查询类容不展示了
1 row in set (0.12 sec)

这种呢也是inner查询直接把主键查询出来了，主查询直接根据这个id主键查询字段，当然啦也是只查询了一次索引节点哦。