专栏首页Java深度编程SQL优化之LIMIT语法, limit n,m 和 limit n有什么区别?

SQL优化之LIMIT语法, limit n,m 和 limit n有什么区别?

在某些面试题中会遇到这样的问答或笔试题:“limit 0,1 和 limit 1有什么区别?” 要准确回答这个问题就等深入明白limit一个参数和两个参数的本质区别。

limit n,m 中的第一次参数n表示的游标的偏移量,初始值为0,第二个参数m表示的是想要获取多少条数据。所以limit 0,1表示的是从第一条记录开始,只取一条即可。limit 1表示的也是只取一条数据,也就是说limit 0,1从结果上来说是等价与limit 1。如果你回答是一样的,那就错了,那么你就钻进套子里了……哈哈哈哈哈……

我们首先来说一说 limit n,m是怎么回事,首先它要获取到第一个参数游标n的位置,那么它就必须得扫描到n的位置,接着从此位置起往后取m条数据,不足m条的返回实际的数量。那么这就会有一个性能的问题,当游标的数值越来越大时性能就会越来越差。

例如:

我们先创建用户表,再使用plsql插入100万数据:

CREATE TABLE `user` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`NAME` varchar(45) NOT NULL COMMENT '用户姓名',
`NUM` varchar(1) NOT NULL COMMENT '编号',
) COMMENT='用户表';
BEGIN 
DECLARE i INT; 
START TRANSACTION; 
SET i=0; 
WHILE i<1000000 DO 
INSERT INTO user VALUES(NULL,CONCAT('Java深度编程',i+1),i+1); 
SET i=i+1; 
END WHILE; 
COMMIT; 
END 

然后我们分别执行sql语句:

select * FROM Sys_User limit 0,10000        //耗时 0.809s
select * FROM Sys_User limit 50000,10000    //耗时 1.654s
select * FROM Sys_User limit 100000,10000   //耗时 3.509s

你会发现按上面分页执行的顺序,执行时间所消耗的cpu会越来越大,执行时间越来越长……

同样是查询10000条记录,为何这三条语句消耗的时候不一样呢?这正是因为游标的偏移量位置不同,偏移量越大,sql语句需要像下扫描的次数就越多,若取到数据的尾部就相当于全表扫描了,所以偏移量越大消耗的性能就越多。

LIMIT n 又是什么?

上面已经说过limit0,1等价与limit 1,那他们到底有啥区别呢?

没错,虽然limit 0,1 等价于limit 1,但limit 100,1并不等价于limit 1。其原理也就是上面所说的油表的偏移量问题所带来的性能消耗,limit 100,1 需要先全表扫描到第100条之后再取一条,而limit 1只需要扫描到第一条就结束了。

另外limit 1的写法还可以用于提升sql性能的优化,具体是怎么做的呢?

根据我们上面创建的用户表,执行sql语句:

 SELECT * FROM user WHERE NAME=?;

假设我们上面创建的用户表的姓名是唯一的,那么该语句只会找到1条记录,但如果没有索引的情况下它会进行全表扫描,于是性能低下,但如果将sql语句改成:

SELECT * FROM user WHERE NAME=? limit 1;

这样的话就不会全表扫描,扫描到第一条就会结束了,因为适当的使用limit 1能够提升性能。但此方法对有索引的列无效,也就是说如果NAME这一列加了索引,执行以上两条sql语句效率是一样的。

本文分享自微信公众号 - Java深度编程(JavaDeep),作者:龚文学

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 什么是前端技术与后端技术

    在HTML入门教程学习之前,我们有必要跟大家讲一下网站开发的一些知识。了解这些知识,对你以后网站开发之路如何走、该学习些什么,是非常有用的。同时也避免你走太多的...

    Java深度编程
  • 框架源码调试实战之easypoi异常解决方案精讲

    最近有个同事遇到了个棘手的问题,easypoi导出文件出了bug,却不知道是怎么回事,无从下手,无可奈何,由于事态紧急,只能火急火急的求助于我。我问他...

    Java深度编程
  • 算法与编程之 -- IO流

    编写一个程序,将 a.txt 文件中的单词与 b.txt 文件中的单词交替合并到 c.txt文件中,a.txt 文件中的单词用回车符分隔,b.txt 文件中用...

    Java深度编程
  • 分表分页/跨库分页为什么这么难?

    当业务数据达到一定量级(比如:mysql单表记录量>1千万)后,通常会考虑“分库分表”将数据分散到不同的库或表中,这样可以大大提高读/写性能。但是问题来了,对于...

    菩提树下的杨过
  • laravel自定义分页的实现案例offset()和limit()

    情景:因个人使用layui在开发后台模块,因为layui自带了table模块,是都封装了分页的,并且返回数据格式也是有固定要求的

    砸漏
  • 今天写出一个十分弱智的bug!

    因为一些新的需求,要在后面加一些where条件,limit操作不能在嵌套查询里面加了,于是乎把limit 0,10提出来放到最外面,结果order by还留在里...

    Java技术栈
  • MySQL的limit查询优化

    我们大家都知道MySQL数据库的优化是相当重要的。其他最为常用也是最为需要优化的就是limit。MySQL的limit给分页带来了极大的方便,但数据量一大的时候...

    java达人
  • Nginx 配置对流量、连接和请求的限制

      limit_rate开启nginx限速功能,可配置在http、server、location和if in location配置段。 limit_rate 5...

    拓荒者
  • 【死磕Sharding-jdbc】---结果合并

    接下来以执行 SELECT o.*FROM t_order o whereo.user_id=10order byo.order_id desc limit 2...

    用户1655470
  • 责任链模式

    责任链模式是行为型设计模式之一,它使多个对象都有机会处理请求,从而避免了请求的发送者和接受者之间的耦合关系。将这些对象连成一条链,并沿着这条链传递该请求,直到有...

    提莫队长

扫码关注云+社区

领取腾讯云代金券