关于mysql 的 limit m,n 和 limit offset 的区别以及mysql分页效率提高的方法

布满荆棘的人生 2023-01-20 05:55 109阅读 0赞

# 关于mysql 的 limit m,n 和 limit offset 的区别以及mysql分页效率提高的方法 #

### 一、背景 ###

我们都知道 mysql 里 limit 有2种写法

*  `limit a,b`
    
    从下标a开始取，取b条数据 。(a从0开始)
 *  `limit b offset a`
    
    查出b条记录，从偏移量a开始，a就是下标，从0开始 (查询结果同 `limit a,b`)

既然查询的结果是相同的，那查询的效率不一样吗？offset写法做了什么优化吗？

`limit a,b` 在分页的时候又有哪些效率问题呢？

### 二、limit \# offset \# 做了什么优化 ###

查看官网和资料，似乎跟 `limit #,#` 的写法没什么区别**没做什么优化**，只是写法不同(**如果有人知道执行效率上的区别，请留言**)

> `limit # offset #` 的写法还是更有可读性的，含义清晰 “limit多少条，offset是从什地方开始的”，不像 `limit #,#` 经常容易搞混参数的位置顺序。

PS：外国人很喜欢用 \# 号表示 number，读的时候也读成 number，比如\#92，92号汽油。所以我这里借鉴了这种写法，别觉得写法有什么奇怪的。

### 三、limit \# offset \# 或者 limit \#,\# 在分页上出现了什么效率问题？ ###

select * from t_table limit b offset a

会越到后面的页数就越慢。

原因有两点

*  取数据的机制：mysql总是取出b条数据，再过滤掉a这个offset之前的数据。所以翻页的时候越翻到后面，取出的数据越多但丢弃掉的也越多，就会越慢。
 *  假设单行记录的数据越大(指数据size)，则越慢

> 上面只是举例了 `limit b offset a`，但对于 `limit a,b` 这种等价的写法是一样的工作原理，所以也有同样的效率问题

### 四、怎么优化 ###

##### 1、首先看看有效率问题的sql的写法 #####

这个也是我们最常用的。

select * from t_table
    where condition_1=xxx,
    and condition_2=xxx,
    and condition_3=xxx
    order by xxx
    limit xx offset yy

> 我建议是如果页数不多或者对效率追求不是极致，直接使用这种简单的写法即可，不要为了优化而优化，要看有没有必要。

##### 2、改进的版本 #####

下面的方案都可以，选一个来执行。

*  方案一：

select a.* from t_table a inner join 
    (
    select id from t_table
    where condition_1=xxx,
    and condition_2=xxx,
    and condition_3=xxx
    order by xxx
    limit xx offset yy
    ) b on a.id=b.id

只要将原来的语句，改成只查id，并且和自己进行inner join 即可。

虽然看起来似乎没啥区别，但是因为改成了只查id，这个数据量就小很多了，而且id是主键，都不需要回表直接就从索引中取这个值。

虽然也是查了多余的id并去掉offset之前的值，但毕竟数据量小很多了且不需要回表。

> 注意，where条件都在内部做即可，需要的排序在内部也是生效的！

*  方案二：(**其实跟方案一是一样的，只是换了一种写法**)

select a.* from t_table a inner join 
    (
    select id from t_table
    where condition_1=xxx,
    and condition_2=xxx,
    and condition_3=xxx
    order by xxx
    limit xx offset yy
    ) b using(id)

跟方案一是一样的，只是用了using的关键字，详细参考 [using][]

> 要注意这里不能用 using(b.id) 这个很容易犯错

### 五、其他 ###

*  本文是讨论mysql的
    
    对于pgsql没有 `limit a,b`的写法，只有 `limit b offset a`
    
    对于mysql，两者都有
 *  至于像pgsql，是不是像mysql分页一样，先取出一定数量的数据再丢弃offset之前的数据？
    
    这个不太清楚。应该都是有这个问题的吧？？(**待读者继续研究**)

[using]: https://blog.csdn.net/gaozongjian/article/details/79622414