更优美的limit使用方法(limit 对mysql 数据查询的性能影响)

今天药忘吃喽~ 2023-07-01 06:30 32阅读 0赞

# 更优美的limit使用方法(limit 对mysql 数据查询的性能影响) #

> 本文在`zhangyachen`的基础上重新排版  
> 来源：[zhangyachen][]

## 一，前言 ##

### 首先说明一下`MySQL`的版本： ###

mysql> select version();
    
    +-----------+
    | version() |
    +-----------+
    | 5.7.17    |
    +-----------+
    1 row in set (0.00 sec)

### 表结构： ###

mysql> desc test;
    
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | val    | int(10) unsigned    | NO   | MUL | 0       |                |
    | source | int(10) unsigned    | NO   |     | 0       |                |
    +--------+---------------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)

*  `id`: 自增主键
 *  `val`: 非唯一索引。

### 数据量，共500多万 ###

mysql> select count(*) from test;
    
    +----------+
    | count(*) |
    +----------+
    |  5242882 |
    +----------+
    1 row in set (4.25 sec)

### 当limit offset rows中的offset很大时，会出现效率问题： ###

mysql> select * from test where val=4 limit 300000,5;
    
    +---------+-----+--------+
    | id      | val | source |
    +---------+-----+--------+
    | 3327622 |   4 |      4 |
    | 3327632 |   4 |      4 |
    | 3327642 |   4 |      4 |
    | 3327652 |   4 |      4 |
    | 3327662 |   4 |      4 |
    +---------+-----+--------+
    5 rows in set (15.98 sec)

## 我们可以改写成如下语句(`效率优化版`)达到相同的目的： ##

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
    
    +---------+-----+--------+---------+
    | id      | val | source | id      |
    +---------+-----+--------+---------+
    | 3327622 |   4 |      4 | 3327622 |
    | 3327632 |   4 |      4 | 3327632 |
    | 3327642 |   4 |      4 | 3327642 |
    | 3327652 |   4 |      4 | 3327652 |
    | 3327662 |   4 |      4 | 3327662 |
    +---------+-----+--------+---------+
    5 rows in set (0.38 sec)

### 时间相差很明显。 ###

为什么会出现上面的结果？  
我们看一下`select * from test where val=4 limit 300000,5;`的查询过程：

1.  查询到索引叶子节点数据。
2.  根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。

类似于下面这张图：  
![format_png][]

像上面这样，

1.  需要查询300005次索引节
2.  查询300005次聚簇索引的数据
3.  最后再将结果过滤掉前300000条，取出最后5条  
    MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有300000次随机I/O查询到的数据是不会出现在结果集当中的。

> 肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。

这样只需要5次随机I/O，类似于下面图片的过程：  
![format_png 1][]

## 证实 ##

> 下面我们实际操作一下来证实上述的推论：

为了证实`select * from test where val=4 limit 300000,5`是扫描`300005`个索引节点和`300005`个聚簇索引上的数据节点，我们需要统计MySQL在一个sql中通过索引节点查询数据节点的次数。  
我先试了Handler\_read\_\*系列，很遗憾没有一个变量能满足条件。

### 通过间接的方式来证实： ###

`InnoDB`中有`buffer pool`  
里面存有最近访问过的数据页，包括数据页和索引页

所以我们需要运行两个sql，来比较buffer pool中的数据页的数量

> 预测结果:  
> `select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;`使用的`buffer pool`中的数据页的数量远远少于`select * from test where val=4 limit 300000,5;`对应的数量

### 测试普通的limit查询 ###

#### 检查buffer pool的使用量 ####

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.04 sec)

> 可以看出，目前buffer pool中没有关于test表的数据页。

#### 执行查询语句 ####

#### 查看buffer pool的使用量 ####

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY    |     4098 |
    | val        |      208 |
    +------------+----------+
    2 rows in set (0.04 sec)

可以看出，此时`buffer pool`中关于`test`表有`4098`个数据页，`208`个索引页。

### 清空buffer pool ###

防止上次试验的影响，我们需要清空buffer pool，重启mysql。

#### 执行语句 ####

mysqladmin shutdown /usr/local/bin/mysqld_safe &

#### 检查buffer pool的使用量 ####

mysql> select index_name,count(*) from  information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    Empty set (0.03 sec)

### 测试优化版的limit查询 ###

#### 执行查询语句 ####

#### 查看buffer pool的使用量 ####

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('val','primary') and TABLE_NAME like '%test%' group by index_name;
    
    +------------+----------+
    | index_name | count(*) |
    +------------+----------+
    | PRIMARY    |        5 |
    | val        |      390 |
    +------------+----------+
    2 rows in set (0.03 sec)

我们可以看明显的看出两者的差别：  
第一个`sql`加载了`4098`个数据页到`buffer pool`  
第二个`sql`只加载了`5`个数据页到`buffer pool`  
符合我们的预测, 也证实了为什么第一个`sql`会慢：读取`300000`无用数据行，最后却抛弃掉。

而且这会造成一个问题：浪费了`buffer pool`的空间

## 遇到的问题 ##

为了在每次重启时确保清空`buffer pool`，我们需要关闭`innodb_buffer_pool_dump_at_shutdown`  
`innodb_buffer_pool_load_at_startup`  
这两个选项能够控制数据库关闭时dump出buffer pool中的数据和在数据库开启时载入在磁盘上备份buffer pool的数据。

## 参考资料： ##

> 1.https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/  
> 2.https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html

[zhangyachen]: https://github.com/zhangyachen/zhangyachen.github.io/issues/117
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9yYXcuZ2l0aHVidXNlcmNvbnRlbnQuY29tL1RXYW5HVC9waWMvbWFzdGVyLzIwMjAwMTE5MTcyNjAxLnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9yYXcuZ2l0aHVidXNlcmNvbnRlbnQuY29tL1RXYW5HVC9waWMvbWFzdGVyLzIwMjAwMTE5MTczMDMxLnBuZw?x-oss-process=image/format,png