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

 

摘抄自: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 大偏移量执行时间变久的原因有:

  • limit a, b会查询前a+b条数据,然后丢弃前a条数据

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;

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

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

  • 这种写法要求主键ID必须是连续的

  • Where子句不允许再添加其他条件

 

延迟关联

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

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

 

上一篇:ubuntu20.04 + OpenLdap 实现企业运维账户管理系统(上)


下一篇:MYSQL不支持full join,但需要有相同字段又有不同字段的两个表的数据汇总排序的时候的解决方案