登录
    Technology changes quickly but people's minds change slowly.

Mysql-Limit 优化(百万、千万级)

技术宅 破玉 1960次浏览 0个评论

参考链接《千万级mysql分页查询优化》

耗时本质

mysql大数据量使用limit分页,随着页码的增大,查询效率越低下。
当一个表数据有几百万的数据的时候成了问题!
如 select * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢
原因本质: 1)limit语句的查询时间与起始记录(offset)的位置成正比 2)mysql的limit语句是很方便,但是对记录很多:百万,千万级别的表并不适合直接使用。
例如: limit10000,20的意思扫描满足条件的10020行,扔掉前面的10000行,返回最后的20行,问题就在这里。 ​ LIMIT 2000000, 30 扫描了200万+ 30行,怪不得慢的都堵死了,甚至会导致磁盘io 100%消耗。 ​ 但是: limit 30 这样的语句仅仅扫描30行。

优化手段

干掉或者利用 limit offset,size 中的offset
不是直接使用limit,而是首先获取到offset的id然后直接使用limit size来获取

对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
覆盖索引:
就是select 的数据列只用从索引中就能获得,不必读取数据行。mysql 可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说:查询列要被所创建的索引覆盖
因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。
这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:

#覆盖索引只包含id列 的时间显著优于 select * 不言而喻
select * from order_table where company_id = 1 and mark =0 order by id desc limit 200000 ,20;
select id from order_table where company_id = 1 and mark =0 order by id desc limit 200000 ,20;

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

#两者用的都是一个原理嘛,所以效果也差不多
SELECT * FROM xxx WHERE ID > =(select id from xxx limit 1000000, 1) limit 20;
SELECT * FROM xxx a JOIN (select id from xxx limit 1000000, 20) b ON a.ID = b.id;

以上原理就是缩小数据范围和体积,充分利用limit的本质。


华裳绕指柔, 版权所有丨如未注明 , 均为原创|转载请注明Mysql-Limit 优化(百万、千万级)
喜欢 (1)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址