mysql limit 性能优化 有感

转至 https://www.jianshu.com/p/efecd0b66c55

 

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 条数据

解读: 转化成伪代码

function limit(offset,rows){
    $res = [];
    for($i=1; $i <= offset+rows; $i++;){
       $res[] = select * from table_name where 第$i 条数据;
    }
    foreach($res as $k => $v){
        if($k < offset){
            unset($res[$K]);
        }
    }
    
    return $res;
    
}

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

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