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语句效率是一样的。