Mysql基础篇-35-limit的优化

Alan0517

已于 2023-06-17 09:59:01 修改

阅读量682

点赞数

文章标签： mysql

于 2021-04-26 22:18:05 首次发布

本文链接：https://blog.csdn.net/Hmj050117/article/details/116175112

版权

Mysql-基础篇专栏收录该内容

41 篇文章 5 订阅

订阅专栏

本文探讨了MySQL在处理大数据量分页查询时的效率问题，特别是使用`LIMIT`语句随着页码增加导致的性能下降。提出了通过避免`LIMIT offset, size`方式，改用获取特定ID后再`LIMIT size`的方法，以及利用覆盖索引来优化查询。同时，文章还对比了`BETWEEN`和`>下界值 AND <上界值`的区别，并提供了针对不同数据规模的优化策略。

摘要由CSDN通过智能技术生成

1. 背景

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。
当一个表数据有几百万的数据的时候成了问题！
如 select * from table limit 0,10 这个没有问题当 limit 200000,10 的时候数据读取就很慢

原因本质：

limit语句的查询时间与起始记录（offset）的位置成正比
mysql的limit语句是很方便，但是对记录很多:百万，千万级别的表并不适合直接使用。
例如： limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。 LIMIT 2000000, 30 扫描了200万+ 30行，怪不得慢的都堵死了，甚至会导致磁盘io 100%消耗。但是: limit 30 这样的语句仅仅扫描30行。

2. 优化：

2.1 `使用 limit offset 而不是 limit offset,size`

不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据

2.1.1 `limit offset,size（含子查询）`

几十万数据可以使用这个，但是实战中可以直接将上一页的最后一条记录id 传入到下一页查询中作为起点使用，从而节省子查询的消耗

2.1.2 `仅仅使用 id<max and limit size 或者使用 min<=id<=max`

千万级数据不建议使用覆合索引+join ，耗cpu/IO 极其严重

select * from order_table where company_id = 1 and mark =0 and id <=82543981 order by id desc limit 200000;

2.2 `利用表的覆盖索引来加速分页查询覆盖索引 + join`

覆盖索引:
就是select 的数据列只用从索引中就能获得，不必读取数据行。mysql 可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说：查询列要被所创建的索引覆盖

其中覆盖索引获取起始id ：select id from order_table where xxx limit 2600000 ,1; 的耗时会随着offset 的增加而增加。此种方式在查询前200万左右的数据时基本能在10s左右搞定，但是要查询 500万-600万这区间数据时覆盖索引的耗时显著提升。