MySQL大数据量分页查询方法及其优化

2022-04-10 21:35:09

摘抄自：https://mp.weixin.qq.com/s?__biz=MzkxMDI2NTc2OQ==&mid=2247485241&idx=1&sn=3330bf2abc82a857692aaee316824d90

limit偏移量不变，随着查询记录量越来越大，所花费的时间也会越来越多。

limit查询记录数不变，随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

select * from user where sex = 1 limit 100,10

由于 sex 列是索引列，MySQL会走 sex 这棵索引树，命中 sex=1 的数据。

然后又由于非聚簇索引中存储的是主键 id 的值，且查询语句要求查询所有列，所以这里会发生一个回表的情况，在命中 sex 索引树中值为1的数据后，拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列（name、sex）的值，最后返回到结果集中，这样第一行数据就查询成功了。

最后这句 SQL 要求limit 100, 10，也就是查询第101到110个数据，但是 MySQL 会查询前110行，然后将前100行抛弃，最后结果集中就只剩下了第101到110行，执行结束。

小结一下，在上述的执行过程中，造成 limit 大偏移量执行时间变久的原因有：

MySQL数据库的查询优化器是采用了基于代价的方式，而查询代价的估算是基于CPU代价和IO代价。如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。

t5表有200万数据，id为主键，text为普通索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

让我们来对比一下使用了覆盖索引，性能会提升多少吧。

没有使用覆盖索引

select * from t5 order by text limit 1000000, 10;

这次查询花了3.690秒，让我们看一下使用了覆盖索引优化会提升多少性能吧。

使用了覆盖索引

select id, `text` from t5 order by text limit 1000000, 10;

从上面的对比中，超大分页查询中，使用了覆盖索引之后，花了0.201秒，而没有使用覆盖索引花了3.690秒，提高了18倍多，这在实际开发中，就是一个大的性能优化了。

因为实际开发中，用SELECT查询一两列操作是非常少的，因此上述的覆盖索引的适用范围就比较有限。

所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性：

和上述的子查询做法类似，我们可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;

码农公寓