mysql limit 性能优化

** 本文所使用 mysql 版本为 5.6.11 **

起因

需求:获取某用户的所有操作记录日志

日志数量虽然不多,但不可能一股脑的塞给用户,难看不说,还拖累服务器性能,因而分页必不可少

limit

基础用法

limit 的用法是 limit [offset], [rows],其中 offset 表示偏移值, rows 表示需要返回的数据行。

问题

mysql 的 limit 给分页带来了极大的方便,但数据偏移量一大,limit 的性能就急剧下降。

以下是两条查询语句,都是取10条数据,但性能就相去甚远。

select * from table_name limit 10000,10

select * from table_name limit 0,10

所以不能简单的使用 limit 语句实现数据分页。

探究

为什么 offset 偏大之后 limit 查找会变慢?这需要了解 limit 操作是如何运作的,以下面这句查询为例:

select * from table_name limit 10000,10

这句 SQL 的执行逻辑是
1.从数据表中读取第N条数据添加到数据集中
2.重复第一步直到 N = 10000 + 10
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 10 条数据

显然,导致这句 SQL 速度慢的问题出现在第二步!这前面的 10000 条数据完全对本次查询没有意义,但是却占据了绝大部分的查询时间!如何解决?首先我们得了解为什么数据库为什么会这样查询。

首先,数据库的数据存储并不是像我们想象中那样,按表按顺序存储数据,一方面是因为计算机存储本身就是随机读写,另一方面是因为数据的操作有很大的随机性,即使一开始数据的存储是有序的,经过一系列的增删查改之后也会变得凌乱不堪。所以数据库的数据存储是随机的,使用 B+Tree, Hash 等方式组织索引。所以当你让数据库读取第 10001 条数据的时候,数据库就只能一条一条的去查去数。

第一次优化

根据数据库这种查找的特性,就有了一种想当然的方法,利用自增索引(假设为id):

select * from table_name where (id >= 10000) limit 10

由于普通搜索是全表搜索,适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索,大大缩小搜索的范围,从而提高搜索效率。

这个优化思路就是告诉数据库:「你别数了,我告诉你,第10001条数据是这样的,你直接去拿吧。」

但是!!!你可能已经注意到了,这个查询太简单了,没有任何的附加查询条件,如果我需要一些额外的查询条件,比如我只要某个用户的数据 ,这种方法就行不通了。

可以见到这种思路是有局限性的,首先必须要有自增索引列,而且数据在逻辑上必须是连续的,其次,你还必须知道特征值。

如此苛刻的要求,在实际应用中是不可能满足的。

第二次优化

说起数据库查询优化,第一时间想到的就是索引,所以便有了第二次优化:先查找出需要数据的索引列(假设为 id),再通过索引列查找出需要的数据。

Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;

select * from table_name where( user = xxx ) limit 10000,10

相比较结果是(500w条数据):第一条花费平均耗时约为第二条的 1/3 左右。

同样是较大的 offset,第一条的查询更为复杂,为什么性能反而得到了提升?

这涉及到 mysql 主索引的数据结构 b+Tree ,这里不展开,基本原理就是:

  • 子查询只用到了索引列,没有取实际的数据,所以不涉及到磁盘IO,所以即使是比较大的 offset 查询速度也不会太差。
  • 利用子查询的方式,把原来的基于 user 的搜索转化为基于主键(id)的搜索,主查询因为已经获得了准确的索引值,所以查询过程也相对较快。

第三次优化

在数据量大的时候 in 操作的效率就不怎么样了,我们需要把 in 操作替换掉,使用 join 就是一个不错的选择。

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

至此 limit 在查询上的优化就告一段落了。如果还有更好的优化方式,欢迎留言告知

最终优化

技术上的优化始终是有天花板的,业务的优化效果往往更为显著。

比如在本例中,因为数据的时效性,我们最终决定,只提供最近15天内的操作日志,在这个前提下,偏移值 offset 基本不会超过一万,这样一来,即使是没有经过任何优化的 sql,其执行效率也变得可以接受了,所以优化不能局限于技术层面,有时候对需求进行一下调整,可能会达到意想不到的效果

推荐阅读更多精彩内容