学会MySQL LIMIT

前言

前面写了《读懂MySQL执行计划》和《写会MySQL索引》后,今天我们来说说MySQL中另外一个比较重要的概念,就是 LIMIT 关键字。

LIMIT是什么?

LIMIT的概念,其实大家应该都很清楚,在百度百科中是这样描述的:

LIMIT是一种数据语言，主要是用于查询之后要显示返回的前几条或者中间某几行数据。

这里着重需要注意的是,offset为开始角标,count代表数量,如下图所示:

(网络配图)

理解了这个概念之后,我们就能够知道下面这两个语句的意思了:

LIMIT 0,100; (A)
LIMIT 10,100; (B)

语句A代表的是 : 从起始角标为0的位置,往后获取100条记录。

语句B代表的是 : 从起始角标为10的位置,往后获取100条记录。

(别以为这很简单,在之前的面试过程中,就有很多童鞋搞混了,将语句B理解成了: 从起始角标为10的位置,获取90条数据呢。)

其实,LIMIT还有一个比较常用的简化写法,如下所示:

LIMIT 100;

这其实就是对上述A语句的简化,其意思代表的是: 从其实角标为0的位置,往后获取100条记录。只是将其实角标0省略掉了而已。真是这样的特性,有很多应用也直接使用LIMIT来进行分页操作。

提问时间

上面我们介绍了,LIMIT的概念,也理清楚了LIMIT每个参数的含义,那现在就留一个问题:

问: LIMIT 0,100与 LIMIT 100000,100的执行效率是一样吗？一样为什么？不一样又为什么？

ps: 面试时经常有这样的问题哦。这个之前我也被问到过。

执行LIMIT发生了什么?

我们知道,一般是在order by xx asc|desc语句后紧跟着LIMIT语句,下面我们就来看看下面这两个语句,揭露一下:
语句A:

select * from t_base_user order by oid desc limit 0,100;

语句B:

select * from t_base_user order by oid desc limit 10000,100;

分别看下执行计划:
语句A的执行计划是:

explain select * from t_base_user order by oid desc limit 0,100;

结果:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t_base_user	index	null	PRIMARY	8	null	100	null

语句B的执行计划是:

explain select * from t_base_user order by oid desc limit 10000,100;

结果:
id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
—|—|—|—|—|—|—|—|–|–|
1 | SIMPLE|t_base_user|index|null|PRIMARY|8|null|1000100|null

到这里,我们会发现扫描的行数是完全不一样的,在语句B中,其实MySQL实际扫描1000100行记录,然后只返回100条记录,将前面的1000000条记录活生生的抛弃掉，你说这成本大不大,代价高不高？看到这里,我们应该已经知道上面问题的答案了。

如何优化

现在我们来说说如何优化LIMIT，我们知道,在offset比较大的时候,效率会非常低,所以,对LIMIT优化,要么限制分页的数量,要么降低offset的大小。
例如:

select * from t_base_user limit 100000,100

比如上面这语句,因为我们主键是连续的。

方法一 : 我们就可以通过这样来优化:

select * from t_base_user where oid between 100000 and 1000100;

此时如果我们看执行计划的话,其实type已经从all(全表扫描)扫描优化到range(范围查找),也走了PRIMARY索引。

方法二: 我们可以倒序LIMIT
如果我们表中一共有120万数据,此时我们就可以倒序LIMIT,如下所述:

select * from t_base_user order by oid desc limit 100;

或者这样:

select * from t_base_user where oid<1000000 order by oid desc limit 100;

同样也达到来优化的效果。

数据结构

本文所有数据,均基于以下数据结构:

create table t_base_user(
oid bigint(20) not null primary key auto_increment,
name varchar(30) null comment “name”,
email varchar(30) null comment “email”,
age int null comment “age”,
telephone varchar(30) null comment “telephone”,
status tinyint(4) null comment “0 无效 1 有效”,
created_at datetime null default now() comment “创建时间”,
updated_at datetime null default now() comment “修改时间”
)
// 新增记录:
insert into t_base_user(name,email,age,telephone,status,created_at,updated_at) values (“andyqian”,”andytohome”,20,”15608411”,1,now(),now());

这里提供一个简单的方法复制数据

insert into t_base_user(name,email,age,telephone,status) select name,email,age,telephone,status from t_base_user;

使用该语句,可以快速的复制数据。执行多次后,就能够生成不少数据,(备注: 该数据仅用作LIMIT关键字演示,新建索引,计算区分度其值偏差会比较大,请勿将该结果作为建索引的参考值。)

小结

上面对MySQL LIMIT关键字做了详细的讲解,你可别小瞧它哦，它在平时开发中有很大的用处哦,例如: 在平时开发查询数据时，加上LIMIT后，查询效果可会大大增加,能节省不少时间呢。在查询数据时养成加上LIMIT是一个不错的习惯。

最后: 祝大家晚安!