mysql limit 含义_mysql limit分析

在数据量比较大时，使用limit性能会比较差，即使有索引，性能也不会特别好，甚至有可能更差。

一 准备工作

建表:

CREATE TABLE `people` (

`id` int(11) AUTO_INCREMENT NOT NULL,

`sex` tinyint(4) NOT NULL,

`rating` tinyint(4) NOT NULL,

`country` char(4) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入约1亿条数据。

delimiter //

drop procedure if exists repeate_insert;

create procedure repeate_insert()

begin

declare i int;

set i = 0;

repeat

insert into people(sex, rating, country) values(floor(rand()*2), floor(rand()*100), substring(md5(rand()),1,2));

set i = i + 1;

until i > 129837455 end repeat;

end

//

call repeate_insert();

二 数据对比

查询语句:

SELECT * FROM people where sex = 1 order by rating limit 1, 10;

SELECT * FROM people where sex = 1 order by rating limit 10, 10;

SELECT * FROM people where sex = 1 order by rating limit 100, 10;

SELECT * FROM people where sex = 1 order by rating limit 1000, 10;

SELECT * FROM people where sex = 1 order by rating limit 10000, 10;

SELECT * FROM people where sex = 1 order by rating limit 100000, 10;

SELECT * FROM people where sex = 1 order by rating limit 1000000, 10;

SELECT * FROM people where sex = 1 order by rating limit 10000000, 10;

SELECT * FROM people where sex = 1 order by rating limit 100000000, 10;

下表中的offset为1乘以10的N次方，从1~100000000，逐渐增大。查询时间单位为秒。

2.1 无索引

图2.1 无索引情况下时间与offset的关系

2.2 有索引

alter table people add index idx_sex_rating (`sex`, `rating`);

图2.2 有索引情况下时间与offset的关系

2.3 延迟关联

修改查询语句

select * from people inner join (select id from people where sex = 1 order by rating limit 1, 10) as tbl using(id);

图2.3 有索引且使用延迟关情的情况下时间与offset的关系

三 分析

在无索引的情况下，Mysql进行全表扫描。磁盘顺序读的速度约为929M/s，数据库中每行数据大小约为10个字节，因此读出磁盘所有数据总时间约为 129837455 * 10 / 929 / 1024 / 1024 约为1秒(估算存在偏差?)。那么剩下的时间是用于排序了。因此offset的大小对于查询时间无显著影响，查询时间受制于数据量大小。

在有索引情况下，Mysql会利用索引进行查询。但是比较糟糕的是Mysql只在最后返回结果时才对数据进行过滤。例如 limit 10000, 10，Mysql先查询出10010条数据，然后丢弃掉前10000条数据，返回最后10条。因此对于10010数据而言，Mysql会对磁盘进行10010次随机读。因此随着offset的增加，随机读的次数也在增加，最终导致总耗时也在增加，因此当offset = 1e7时，查询时间超过了全盘扫描的时间。

在延迟关联中，Mysql仍然利用了索引，但由于返回的字段只有id，因此避免了读表行的操作。因此在整个过程中，Mysql只是避免了索引，没有访问磁盘。最后根据返回的id再读表，因此耗非常短。

因此limit的过程分为两步，第一步：查询出所有符合条件的数据，第二步：丢弃前offset+1条数据，返回余下的若干条适当数目的数据。

附

1 offset分析

1.1 解析offset值

limit_options:

limit_option

{

$$.limit= $1;

$$.opt_offset= NULL;

$$.is_offset_first= false;

}

| limit_option ',' limit_option

{

$$.limit= $3;

$$.opt_offset= $1;

$$.is_offset_first= true;

}

| limit_option OFFSET_SYM limit_option

{

$$.limit= $1;

$$.opt_offset= $3;

$$.is_offset_first= false;

}

;

limit_option:

ident

{

$$= NEW_PTN PTI_limit_option_ident(@$, $1, @1.raw);

}

| param_marker

{

$$= NEW_PTN PTI_limit_option_param_marker(@$, $1);

}

| ULONGLONG_NUM

{

$$= NEW_PTN Item_uint(@$, $1.str, $1.length);

}

| LONG_NUM

{

$$= NEW_PTN Item_uint(@$, $1.str, $1.length);

}

| NUM

{

$$= NEW_PTN Item_uint(@$, $1.str, $1.length);

}

;

语法解析将offset值赋值给opt_offset字段。

1.2 opt_offset赋值给offset_limit

pc->select->select_limit= limit_options.limit;

pc->select->offset_limit= limit_options.opt_offset;

pc->select->explicit_limit= true;

1.3 设置limit

handle_query函数调用set_limit函数offset_limit_cnt值

void st_select_lex_unit::set_limit(st_select_lex *sl)

{

offset_limit_cnt= sl->get_offset();

select_limit_cnt= sl->get_limit();

}

1.4 过滤结果

可以看到，在读取结果后，会对行数进行过滤。丢弃前offset+1行数据。

bool Query_result_send::send_data(List &items)

{

Protocol *protocol= thd->get_protocol();

if (unit->offset_limit_cnt)

{// using limit offset,count unit->offset_limit_cnt--;

DBUG_RETURN(FALSE);

}

/*We may be passing the control from mysqld to the client: release theInnoDB adaptive hash S-latch to avoid thread deadlocks if it was reservedby thd*/

protocol->start_row();

if (thd->send_result_set_row(&items))

{

protocol->abort_row();

DBUG_RETURN(TRUE);

}

thd->inc_sent_row_count(1);

DBUG_RETURN(protocol->end_row());

}

2 磁盘读测试

磁盘IO:

随机读:

fio -filename=/tmp/test_randread -direct=1 -iodepth 1 -thread -rw=randread -ioengine=psync -bs=16k -size=30G -numjobs=10 -runtime=600 -group_reporting -name=mytest

Jobs: 10 (f=10): [r(10)][100.0%][r=58.2MiB/s,w=0KiB/s][r=3722,w=0 IOPS][eta 00m:00s]

顺序读:

fio -filename=/tmp/test_randread -direct=1 -iodepth 1 -thread -rw=read -ioengine=psync -bs=16k -size=30G -numjobs=10 -runtime=60 -group_reporting -name=mytest

Jobs: 10 (f=10): [R(10)][100.0%][r=929MiB/s,w=0KiB/s][r=59.5k,w=0 IOPS][eta 00m:00s]