MySQL的limit优化

某科学的南条

已于 2022-12-26 11:41:33 修改

阅读量1.9w

点赞数 3

分类专栏： mysql1 文章标签： mysql 数据库 sql

于 2020-09-02 18:09:53 首次发布

本文链接：https://blog.youkuaiyun.com/nlcexiyue/article/details/108367453

版权

mysql1 专栏收录该内容

5 篇文章

订阅专栏

耗时本质

MySQL大数据量使用limit分页，随着页码的增大，查询效率越底下。

当一个表数据有几百万的时候就成了问题。

如：select * from table limit 0,10 这个没有问题当 limit 200000,10 的时候数据读取就很慢

原因本质：

1、limit语句的查询时间与起始记录（offset）的位置成正比

2、MySQL的limit语句是很方便，但是对记录很多：百万，千万级别的表并不适合直接使用。

例如：limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。limit 200000,30扫描了20万+30行，怪不得慢都堵死了，甚至会导致磁盘IO100%消耗，但是：limit 30这样的语句仅仅扫描30行

优化手段：

SELECT film_id,description FROM film ORDER BY title LIMIT 5000000,5; 
    　　如果这张表非常大，那么这个查询最好改成下面的样子：

    
SELECT film.film_id,film.description
 
FROM film INNER JOIN (
 
    SELECT film_id FROM film ORDER BY title LIMIT 5000000,5
 
) AS tmp USING(film_id);

订单中心实际应用例子
SELECT * FROM dst_111yao_doms.d_pl_mt_good_h
        inner join (
          select upc,busno_online from dst_111yao_doms.d_pl_mt_good_h where ( transfer_status = #{transferStatus} )
          LIMIT #{offset}, #{limit}
          ) b using (upc,busno_online)

干掉或者利用limit offset ，size中的offset

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

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

利用表的覆盖索引来加速分页查询

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

因为利用索引查找优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外MySQL中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

原sql，超级慢

select * from record limit 2000000,10

优化后两种方式：

Select * From record Where id >=(

　　　　Select id From record Order By id limit 2000000,1

　　) limit 10

（以上语句含义为：分页时传入的offset为2000000，那么我们将先查询第2000000条数据的id（主键）（第2000000条数据的id不一定为2000000），再在主句中使用主键id作为条件进行查询）


SELECT * FROM `table` WHERE  id > 3057000 LIMIT 500

SELECT * FROM account a JOIN (select id from account limit 10000, 10) b ON a.ID = b.id

以上语句都可以优化limit在大数据量下的查询，具体使用可以测试每种sql语句的耗时，进行选择，每种sql的原理基本上都是一样的