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

2020 年 9 月 21 日 星期一
5

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

limit-offset查询时间过长原理

近期接触了一个百万级别的表,普通的单表列表查询竟然>10s,如下图,常见limit-offset分页查询一条数据竟然都高达2s,如下面所示, mysql 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. 根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

如下图所示

20200921-01.png

20200921-01.png

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

优化

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

第一个假如是主键正序或者倒序排序的话就可以使用 mysql MySQL [yuetong1]> SELECT * FROM pa_order WHERE id > 800000 LIMIT 1; //查询类容不展示了 1 row in set (0.01 sec) 这种直接限定了主键的查询集合是800000往后的,所以只查询一次索引节点

第二个是假如有条件查询和排序各式各样的话推荐使用inner join查询 mysql 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主键查询字段,当然啦也是只查询了一次索引节点哦。

  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Mysql之LIMIT-OFFSET查询时间过长处理办法 - ctexthuang