mysql offset 详解_mysql查询时offset过大影响性能的原因和优化详解

前言

mysql查询使用select命令，配合limit，offset参数可以读取指定范围的记录。本文将介绍mysql查询时，offset过大影响性能的原因及优化方法。

准备测试数据表及数据

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条记录

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