MySQL 查询语句的 limit, offset 是怎么实现的?
作者 | 操盛春
来源 | 一树一溪(ID:lovespring52)
转载请联系授权(微信ID:csch52)
在写 select 语句的时候,使用 limit, offset 可能就像是我们吃饭喝水一样自然了。
刚开始工作的时候也经常听前辈们教导:使用 limit, offset,当 offset 变大的时候执行效率会越来越低。
相信在前辈们的言传身教,和自己的实战过程中,大家也都知道了为什么会这样。
因为 select 在执行过程中,对于存储引擎返回的记录,经过 server 层的 WHERE 条件筛选之后,符合条件的前 offset 条记录,会被直接无情的抛弃,直到符合条件的第 offset + 1 条记录,才开始发送给客户端,发送了 limit 条记录之后,查询结束。
虽然知道了是什么,也知道了为什么,但是我也一直好奇底层是怎么实现的,所以今天我们来扒一扒它的庐山真面目。
1. 语法回顾
先来简单的回顾一下 select 语句中 limit, offset 的语法,MySQL 支持 3 种形式:
- LIMIT limit: 因为没有指定 offset,所以 offset = 0,表示读取符合 WHERE 条件的第 1 ~ limit 条记录。
- LIMIT offset, limit: 我们常用的就是这种了。
- LIMIT limit OFFSET offset: 这种不常用。
offset 和 limit 的值都不能为负数,在源码里这两个属性定义的是无符号整数,并且在解析阶段就做了限制,如果为负数,直接报语法错误了。
2. 语法解析阶段
在读取数据的过程中,对于符合条件的前 offset 条记录,会直接忽略,不发送给客户端,从符合条件的第 offset + 1 条记录开始,发送 limit 条记录给客户端。
所以,server 层实际上需要从存储引擎读取 offset + limit 条记录,源码里也是这么实现的,语法解析阶段,在验证了 offset 和 limit 都是大于等于 0 的整数之后,就把 offset + limit 的计算结果保存到一个叫做 select_limit_cnt 的属性里,offset 也会保存到一个叫做 offset_limit_cnt 的属性里。
3. 发送数据阶段
来到发送数据阶段,此时的记录已经通过了 WHERE 条件的筛选,接下来就是判断这条记录是不是要发送给客户端。
第 1 步
因为 offset 已经保存到 offset_limit_cnt 中了,先来判断 offset_limit_cnt 是否大于 0,如果大于 0,这条记录就会被抛弃了,不发送给客户端;如果等于 0,记录就具备了发送给客户端的资格了,然后接着进入第 2 步。
在抛弃记录之前,还会干一件事:对一个叫做 send_records 的属性进行加 1 操作,就是假装这条记录已经发送了(为什么这样干?第 2 步会用到这个属性)。
offset_limit_cnt 是保证不会小于 0 的,所以在这一步只需要判断是大于 0 还是等于 0 就可以了。
第 2 步
来到这一步,记录就具备了发送给客户端的资格了,至于要不要发,就看客户端想不想要它了,而客户端想不想要它,取决于 select_limit_cnt。
所以,在这一步要判断已发送记录数量(send_records)和需要发送记的录数量(select_limit_cnt)之间的关系,如果已发送记录数量大于等于需要发送的记录数量,则结束查询,否则就接着进入第 3 步。
第 3 步
在这里,记录愉快的等待着被发送给客户端。
是的,还要愉快的等着,因为要排队,毕竟运输也是需要成本的,不能来一条记录,就发一趟车,要等一辆车装满之后,才会发车的。这里的车指的是网络缓冲区,以后也会写文章介绍,敬请期待。
4. 最佳实践
既然在 offset 变大之后,使用 limit, offset 效率越来越低,那应该怎么办呢?对于实战经验丰富的小伙伴来说,这是相当简单了,但是以防万一刚看到本文的小伙伴是刚刚开始用 SQL 写 Bug,所以还是要大概的写一下的。
以一个 SQL 为例:
select * from t2
where i1 > 90000000 limit 8888, 10
为了取到 10 条记录,要先找到 8888 条记录,然后取到需要的 10 条,前面 8888 条记录都白找了,太浪费了,可以这样修改一下:
select * from t2
where i1 > 90000000 and id > LAST_MAX_ID
limit 10
LAST_MAX_ID 是上一次执行 SQL 时读取到的主键 ID 的最大值,如果是第一次执行语句,LAST_MAX_ID = 0。
不过这种方案也有个问题,不支持跳着翻页,只支持顺序翻页(就是每次都点下一页的这种)。
如果要支持跳着翻页,怎么办?
只用 MySQL 这把锤子显然有点不够用了,还要再找一把锤子(Redis),可以把符合条件的记录的主键 ID 都读取出来,存入到 Redis 的有序集合(zset)中,用 zset 相应的函数读取到某一页应该展示的数据对应的那些主键 ID,然后用这些主键 ID 去 MySQL 中查询对应的数据,从而用两把锤子间接的实现了分页功能。
当然,这个方案也是有适用场景的,比如,这个方案明显就不适用于这些场景:符合条件的记录非常非常多导致存主键 ID 到 Redis 要占用很大的内存、记录更新频繁导致存主键 ID 的缓存经常被清除。如果碰到更复杂的场景,就要结合业务具体情况具体分析了。