MySQL中Limit性能优化的方案

我就是我 2022-11-26 01:58 230阅读 0赞

在MySQL中**Limit**有两种语法：

*  limit offset, rows
 *  limit rows

其中offset表示偏移量，rows表示要返回的记录条数。比如我们要查出第1000到第1010行的记录，就会写成 `limit 999, 10` 。一般我们在实际开发中做分页查询的时候，`rows`不会太大，因为`rows`表示每页要显示的数量（你总不可能每页要显示个几万条吧）。而随着页数的加深，`offset`就会变大，查询效率随之就变慢了。  
为什么当offset很大时，查询效率会变慢呢？  
比如当我们用 `limit 1000000, 10` 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。

我们先准备一张**tb\_test**表  
![在这里插入图片描述][20200810142208126.png]  
表中有1000+W行数据

mysql> select count(*) from tb_test;
    +----------+
    | count(*) |
    +----------+
    | 11075415 |
    +----------+
    1 row in set (1.15 sec)

我们先试一下 最常规的写法

select * from tb_test where val = 4 limit 100000, 5;

![在这里插入图片描述][20200810142428112.png]  
耗时1秒多，然后我们试试两种优化方案。

## 如果主键id有序，可以用子查询优化 ##

select * from tb_test 
    where val = 4 and id >= (select id from tb_test where val = 4 limit 100000, 1) 
    limit 5;

![在这里插入图片描述][format_png]

## 如果主键id是无序的，可以用inner join来优化 ##

select t.* from tb_test t 
    inner join (select id from tb_test where val = 4 limit 100000, 5) tmp 
    on t.id = tmp.id;

![在这里插入图片描述][format_png 1]  
好像优化后的SQL查询效率也没提高多少？？？  
别急，接下来我们给**val字段添加上索引**，再来测试一下  
![在这里插入图片描述][format_png 2]  
效率差距立马就体现出来了有木有。  
所以我们可以得出结论：**如果要优化limit查询的话，where条件中的字段一定要有索引。**

PS：如果是手机端的话，我们可以让前端每次都把当前页的最大id（或者最小id）传过来，我们的查询中就可以直接使用下面这样的SQL语句。

select * from tb_test where id > ? and val = ? limit 5

这种SQL语句在**id是有序且不会跳页的业务场景**下是非常合适的，但是PC端一般是允许跳页的，就不太合适了

[20200810142208126.png]: /images/20221124/94b12f6e980948dbb12e4999f126fee1.png
[20200810142428112.png]: /images/20221124/5ecdf451c0a44e2cb2b0fee49fce23f4.png
[format_png]: /images/20221124/84825fd4b1344483a969c8ccd852cc2a.png
[format_png 1]: /images/20221124/f094de545d5247c0969eabe33c4f62c3.png
[format_png 2]: /images/20221124/4c994b8f6b744c05b4c0d33f12b569b0.png