limit用来限制查询返回的行数。不用返回整个查询结果集,常用来分页查询。
LIMIT [offset,] row_count
offset是偏移量,默认是0。limit 10即 limit 0,10的意思,获取前10条。
limit 两个参数可以使用预编译 ?进行参数传递。
limit 0 立即返回一个空结果,可以用来检测sql正确性。
limit与distinct
如果distinct和limit row_count同时在查询语句中,在获取到满足distinct(唯一行)的row_count行后会立即结束查询。
limit与order by
如果order by的列在多行有相同的值,如果使用limit每次返回的行可能不同。
select id,name,age from user order by age limit 5;
这个也好理解,按相同年龄排序,如果多个人同岁,只取其中几个,选取哪几个这个对于整条语句来说是无序的,只满足年龄排序接口,如果要保证每次查询返回结果相同,最好加上对应唯一值进行排序。如这里 order by age,id。
limit 分页
开发中经常使用 limit m,n来进行分页操作。但是当翻页较深即深分页的时候,limit查询效率就非常低。原因是因为limit m,n不是只取n行数据,而是扫描m+n行,然后将前m行丢弃返回客户端n行。可以使用explain来观察扫描的行数。
如limit 1000000,100总共要扫描1000100行。
可以使用延迟延迟关联或子查询进行优化
延迟关联
select t1.* from t t1 ,(select id from t where id limit 100000,100) as t2 where t1.id=t2.id
子查询
select * from t where t.id >=(select id from t limit 100000,1) limit 100;
这两种方式都是将limit转换到了主键索引,主键索引可以很好的使用索引覆盖直接使用索引来完成查找。不用扫描过多的行。
结合oracle使用rownumber伪列嵌套来完成分页,感觉上和这里是一个道理。都是需要先确定每行的顺序位置,然后再选取数据。