mysql查询时，offset过大影响性能的原因与优化方法

遇到的问题

我们大家都知道，mysql查询使用select命令，配合limit，offset参数可以读取指定范围的记录,但是offset过大影响查询性能的原因及优化方法，这次工作中因为要导出40W的数据遇到这个offset过大的问题，遍历写入excel的时间花了2个多小时。

---

准备测试数据表及数据

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)

---

分析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条。

  所以，mysql查询时，offset过大影响性能的原因是多次通过主键索引访问数据块的I/O操作，如下图。

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

InnoDB有这个问题，而MYISAM索引结构与InnoDB不同，二级索引都是直接指向数据块的，因此没有此问题。 

优化方法

根据上面的分析，我们知道查询所有字段会导致主键索引多次访问数据块造成的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)

所以最后我通过这个方式查询数据，百万级的数据基本都是毫秒级别的查询出结果，40W数据写入excel时间从2个多小时优化成20分钟，满满的成就感哈。

---

参考文章：

1. 转载from：https://blog.csdn.net/fdipzone/article/details/72793837
2. sefault：https://segmentfault.com/p/1210000008951080/read

转载于:https://blog.51cto.com/onebig/2124954