本文首发于个人微信公众号《andqian》，期待你的关注!
前言
前面写了《读懂MySQL执行计划》和《写会MySQL索引》后,今天我们来说说MySQL中另外一个比较重要的概念,就是 LIMIT 关键字。
LIMIT是什么?
LIMIT的概念,其实大家应该都很清楚,在百度百科中是这样描述的:
LIMIT是一种数据语言，主要是用于查询之后要显示返回的前几条或者中间某几行数据。
这里着重需要注意的是: offset为开始角标,count代表数量,如下图所示:
(图片来源: mysqltutorial)
理解了这个概念之后,我们就能够知道下面这两个语句的意思了:
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: 在Java面试时会经常问到哦。我之前就被问到过。
执行LIMIT发生了什么?
我们都知道, LIMIT一般是跟在order by xx asc|desc语句后的,接下来我们就来看看下面这两个语句,一起来分析一下上述问题的答案:
语句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;
结果:
语句B的执行计划是:
explain select * from t_base_user order by oid desc limit 10000,100;
结果:
到这里,我们会发现扫描的行数是完全不一样的,在语句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是一个不错的习惯。
最后: 祝大家晚安!
相关阅读:
扫码关注，一起进步
个人博客: http://www.andyqian.com