mysql查询,offset过大影响性能原因与分析

通常我们会利用如下语句进行分页查询:

select * from `table` order by id desc limit 0, 20

如果使用索引,这种查询方法在中小数据数据量情况下完全够用,但是随着数据量增加,这种写法就会带来性能问题。下面就来分析offset过大带来的性能问题、原因以及解决方案。


准备测试表和数据,并插入足够多的数据。

Create Table: CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `gender` tinyint(3) unsigned NOT NULL COMMENT '性别',
  `ip_addr_str` varchar(64) DEFAULT '192.168.1.1',
  `ip_addr_int` int(11) DEFAULT '16885952',
  PRIMARY KEY (`id`),
  KEY `gender` (`gender`)
) ENGINE=InnoDB AUTO_INCREMENT=1745815 DEFAULT CHARSET=utf8

查看一下users表中总的记录条数:

mysql> select count(id) from users;
+-----------+
| count(id) |
+-----------+
|   1745814 |
+-----------+
1 row in set (0.28 sec)

首先来测试不同offset查询时性能
1、offset较小的情况

mysql> select * from users where gender=1 limit 1000,1;
+------+-------+--------+-------------+-------------+
| id   | name  | gender | ip_addr_str | ip_addr_int |
+------+-------+--------+-------------+-------------+
| 1001 | b4815 |      1 | 192.168.1.1 |    16885952 |
+------+-------+--------+-------------+-------------+
1 row in set (0.01 sec)

mysql> select * from users where gender=1 limit 10000,1;
+-------+-------+--------+-------------+-------------+
| id    | name  | gender | ip_addr_str | ip_addr_int |
+-------+-------+--------+-------------+-------------+
| 10001 | d9a37 |      1 | 192.168.1.1 |    16885952 |
+-------+-------+--------+-------------+-------------+
1 row in set (0.06 sec)

当offset较小时,查询速度很快,效率较高。
2、offset较大的情况

mysql> select * from users where gender=1 limit 100000,1;
+--------+-------+--------+-------------+-------------+
| id     | name  | gender | ip_addr_str | ip_addr_int |
+--------+-------+--------+-------------+-------------+
| 100001 | ae024 |      1 | 192.168.1.1 |    16885952 |
+--------+-------+--------+-------------+-------------+
1 row in set (0.16 sec)

mysql> select * from users where gender=1 limit 500000,1;
+--------+-------+--------+-------------+-------------+
| id     | name  | gender | ip_addr_str | ip_addr_int |
+--------+-------+--------+-------------+-------------+
| 500001 | 12213 |      1 | 192.168.1.1 |    16885952 |
+--------+-------+--------+-------------+-------------+
1 row in set (0.95 sec)

当offset很大时,会出现效率问题,随着offset的增大,执行效率下降。


对于如下查询,过程是怎么样的呢?

mysql> select * from users where gender=1 limit 500000,1;
+--------+-------+--------+-------------+-------------+
| id     | name  | gender | ip_addr_str | ip_addr_int |
+--------+-------+--------+-------------+-------------+
| 500001 | 12213 |      1 | 192.168.1.1 |    16885952 |
+--------+-------+--------+-------------+-------------+
1 row in set (0.95 sec)

因为我们使用的是InnoDB引擎,查询过程如下:
- 通过二级索引查到主键值(找出所有gender=1的id)。
- 再根据查到的主键值通过主键索引找到相应的数据块(根据id找出对应的数据块内容)。
- 根据offset的值,查询500001次主键索引的数据,最后将之前的500000条丢弃,取出最后1条。

可以看出首先要通过二级索引找到主键值,不过既然二级索引已经找到主键值,为什么还需要先用主键索引找到数据块,再根据offset的值做偏移处理呢?

如果在找到主键索引后,先执行offset偏移处理,跳过500000条,再通过第500001条记录的主键索引去读取数据块,这样就能提高效率了。

我们改为如下查询方式,只查询出主键,对比一下效率:

mysql> select id from users where gender=1 limit 500000,1;
+--------+
| id     |
+--------+
| 500001 |
+--------+
1 row in set (0.15 sec)

结果对比很明显,只查询主键值相比查询所有字段,效率上有很大提升。


下面看看InnoDB与MyISAM引擎索引结构对比图:

二级索引的访问需要两次索引查找。第一次通过查找 二级索引 找二级索引中叶子节点存储的 主键的值;第二次通过这个主键的值去 聚簇索引 中查找对应的行。

正是因为这样,当offset过大时,会多次通过主键索引访问数据块的I/O,导致查询性能下降。(注意,只有InnoDB有这个问题,而MYISAM索引结构与InnoDB不同,二级索引都是直接指向数据块的,因此没有此问题 )

下面来证实一下:
InnoDB中有buffer pool,存放最近访问过的数据页,包括数据页和索引页。
1、首先把mysql重启,重启后查看buffer pool的内容

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

可以看到,重启后,没有访问过任何的数据页。
2、查询所有字段,再查看buffer pool的内容

mysql> select * from users where gender=1 limit 300000,1;
+--------+------------+--------+
| id     | name       | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 |      1 |
+--------+------------+--------+
1 row in set (0.38 sec)

mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%users%' group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| gender     |      261 |
| PRIMARY    |     1385 |
+------------+----------+
2 rows in set (0.06 sec)

可以看出,此时buffer pool中关于member表有1385个数据页,261个索引页。
3、重启mysql清空buffer pool,继续测试只查询主键

mysql> select id from users where gender=1 limit 300000,1;
+--------+
| id     |
+--------+
| 599465 |
+--------+
1 row in set (0.08 sec)

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

可以看出,此时buffer pool中关于member表只有13个数据页,263个索引页。因此减少了多次通过主键索引访问数据块的I/O操作,提高执行效率。


优化方法
根据上面的分析,我们知道查询所有字段会导致主键索引多次访问数据块造成的I/O操作。

因此我们先查出偏移后的主键,再根据主键索引查询数据块的所有内容即可优化。

mysql> select a.* from users as a inner join (select id from users where gender=1 limit 1500000,1) as b on a.id=b.id;
+---------+-------+--------+-------------+-------------+
| id      | name  | gender | ip_addr_str | ip_addr_int |
+---------+-------+--------+-------------+-------------+
| 1500001 | d6af5 |      1 | 192.168.1.1 |    16885952 |
+---------+-------+--------+-------------+-------------+
1 row in set (0.29 sec)

可以看出查询速度得到很大的提升。

但是这种大表驱动小表的方式还不够完美,我们应该用小表驱动大表,即用小的结果集驱动大的结果集。

mysql> select a.* from (select id from users where gender=1 limit 1500000,1) as b  inner join users as a on a.id=b.id;
+---------+-------+--------+-------------+-------------+
| id      | name  | gender | ip_addr_str | ip_addr_int |
+---------+-------+--------+-------------+-------------+
| 1500001 | d6af5 |      1 | 192.168.1.1 |    16885952 |
+---------+-------+--------+-------------+-------------+
1 row in set (0.28 sec)

原文地址:mysql查询时,offset过大影响性能的原因与优化方法