共计 5282 个字符，预计需要花费 14 分钟才能阅读完成。

这篇文章主要介绍了 mysql 查询时 offset 过大影响性能的原因是什么，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

准备测试数据表及数据

1. 创建表

CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(10) NOT NULL COMMENT  姓名 ,
 `gender` tinyint(3) unsigned NOT NULL COMMENT  性别 ,
 PRIMARY KEY (`id`),
 KEY `gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 插入 1000000 条记录

?php
$pdo = new PDO( mysql:host=localhost;dbname=user , root , 
for($i=0; $i 1000000; $i++){ $name = substr(md5(time().mt_rand(000,999)),0,10);
 $gender = mt_rand(1,2);
 $sqlstr =  insert into member(name,gender) values(.$name. , .$gender.) 
 $stmt = $pdo- prepare($sqlstr);
 $stmt- execute();
mysql  select count(*) from member;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.23 sec)

3. 当前数据库版本

mysql  select version();
+-----------+
| version() |
+-----------+
| 5.6.24 |
+-----------+
1 row in set (0.01 sec)

分析 offset 过大影响性能的原因

1.offset 较小的情况

mysql  select * from member where gender=1 limit 10,1;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 26 | 509e279687 | 1 |
+----+------------+--------+
1 row in set (0.00 sec)
mysql  select * from member where gender=1 limit 100,1;
+-----+------------+--------+
| id | name | gender |
+-----+------------+--------+
| 211 | 07c4cbca3a | 1 |
+-----+------------+--------+
1 row in set (0.00 sec)
mysql  select * from member where gender=1 limit 1000,1;
+------+------------+--------+
| id | name | gender |
+------+------------+--------+
| 1975 | e95b8b6ca1 | 1 |
+------+------------+--------+
1 row in set (0.00 sec)

当 offset 较小时，查询速度很快，效率较高。 

2.offset 较大的情况

mysql  select * from member where gender=1 limit 100000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 199798 | 540db8c5bc | 1 |
+--------+------------+--------+
1 row in set (0.12 sec)
mysql  select * from member where gender=1 limit 200000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 399649 | 0b21fec4c6 | 1 |
+--------+------------+--------+
1 row in set (0.23 sec)
mysql  select * from member where gender=1 limit 300000,1;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 row in set (0.31 sec)

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

分析影响性能原因

select * from member where gender=1 limit 300000,1;

因为数据表是 InnoDB，根据 InnoDB 索引的结构，查询过程为：

通过二级索引查到主键值（找出所有 gender= 1 的 id)。

再根据查到的主键值通过主键索引找到相应的数据块（根据 id 找出对应的数据块内容）。

根据 offset 的值，查询 300001 次主键索引的数据，最后将之前的 300000 条丢弃，取出最后 1 条。

不过既然二级索引已经找到主键值，为什么还需要先用主键索引找到数据块，再根据 offset 的值做偏移处理呢？

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

如果我们只查询出主键，看看有什么不同

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

很明显，如果只查询主键，执行效率对比查询全部字段，有很大的提升。 

推测

只查询主键的情况

因为二级索引已经找到主键值，而查询只需要读取主键，因此 mysql 会先执行 offset 偏移操作，再根据后面的主键索引读取数据块。

需要查询所有字段的情况

因为二级索引只找到主键值，但其他字段的值需要读取数据块才能获取。因此 mysql 会先读出数据块内容，再执行 offset 偏移操作，最后丢弃前面需要跳过的数据，返回后面的数据。 

证实

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

为了测试，先把 mysql 重启，重启后查看 buffer pool 的内容。

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

可以看到，重启后，没有访问过任何的数据页。

查询所有字段，再查看 buffer pool 的内容

mysql  select * from member 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  %member%  group by index_name;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| gender | 261 |
| PRIMARY | 1385 |
+------------+----------+
2 rows in set (0.06 sec)

可以看出，此时 buffer pool 中关于 member 表有 1385 个数据页，261 个索引页。 

重启 mysql 清空 buffer pool，继续测试只查询主键

mysql  select id from member 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  %member%  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 操作，提高执行效率。

因此可以证实，mysql 查询时，offset 过大影响性能的原因是多次通过主键索引访问数据块的 I / O 操作。（注意，只有 InnoDB 有这个问题，而 MYISAM 索引结构与 InnoDB 不同，二级索引都是直接指向数据块的，因此没有此问题）。 

InnoDB 与 MyISAM 引擎索引结构对比图

这里写图片描述

优化方法

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

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

mysql  select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
+--------+------------+--------+
| id | name | gender |
+--------+------------+--------+
| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+
1 row in set (0.08 sec)

附：MYSQL limit,offset 区别

SELECT
 keyword
 keyword_rank
WHERE
 advertiserid= 59 
order by
 keyword
LIMIT 2 OFFSET 1;

比如这个 SQL，limit 后面跟的是 2 条数据，offset 后面是从第 1 条开始读取

SELECT
 keyword
 keyword_rank
WHERE
 advertiserid= 59 
ORDER BY
 keyword
LIMIT 2 ,1;

而这个 SQL，limit 后面是从第 2 条开始读，读取 1 条信息。

这两个千万别搞混哦。

感谢你能够认真阅读完这篇文章，希望丸趣 TV 小编分享的“mysql 查询时 offset 过大影响性能的原因是什么”这篇文章对大家有帮助，同时也希望大家多多支持丸趣 TV，关注丸趣 TV 行业资讯频道，更多相关知识等着你来学习!