mysql limit select_MySQL 用 limit 为什么会影响性能？

原标题：MySQL 用 limit 为什么会影响性能？

作者：zhangyachen

dwz.cn/K1Q1cePW

前言

首先说明一下MySQL的版本：

mysql> select version;

+-----------+

| version |

+-----------+

| 5.7.17|

+-----------+

1row in set( 0.00sec)

表结构：

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| |

+--------+---------------------+------+-----+---------+----------------+

3rows in set( 0.00sec)

id为自增主键，val为非唯一索引。

灌入大量数据，共500万：

mysql> select count(*)from test;

+----------+

| count(*) |

+----------+

| 5242882|

+----------+

1row in set( 4.25sec)

我们知道，当limit offset rows中的offset很大时，会出现效率问题：

mysql> select * from test where val= 4limit 300000, 5;

+---------+-----+--------+

| id | val | source |

+---------+-----+--------+

| 3327622| 4| 4|

| 3327632| 4| 4|

| 3327642| 4| 4|

| 3327652| 4| 4|

| 3327662| 4| 4|

+---------+-----+--------+

5rows in set( 15.98sec)

为了达到相同的目的，我们一般会改写成如下语句：

mysql> select * from test a inner join(select id from test where val= 4limit 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|

+---------+-----+--------+---------+

5rows in set( 0.38sec)

时间相差很明显。

查询到索引叶子节点数据。

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

类似于下面这张图：

肯定会有人问：既然一开始是利用索引的，为什么不先沿着索引叶子节点查询到最后需要的5个节点，然后再去聚簇索引中查询实际数据。这样只需要5次随机I/O，类似于下面图片的过程：

其实我也想问这个问题。

证实

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

我只能通过间接的方式来证实：

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.04sec)

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

mysql> select * from test where val= 4limit 300000, 5;

+---------+-----+--------+

| id | val | source |

+---------+-----+--------+

| 3327622| 4| 4|

| 3327632| 4| 4|

| 3327642| 4| 4|

| 3327652| 4| 4|

| 3327662| 4| 4|

+---------+-----+--------+

5rows in set( 26.19sec)

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|

+------------+----------+

2rows in set( 0.04sec)

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

mysqladmin shutdown

/usr/local/bin/mysqld_safe & 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.03sec)

运行sql：

mysql> select * from test a inner join(select id from test where val= 4limit 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|

+---------+-----+--------+---------+

5rows in set( 0.09sec)

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|

+------------+----------+

2rows in set( 0.03sec)

而且这会造成一个问题：加载了很多热点不是很高的数据页到buffer pool，会造成buffer pool的污染，占用buffer pool的空间。

遇到的问题

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

责任编辑：