limit用到的非常平凡,特别是数据量很多,要用分页来显示的时候。而这种情况几乎是不能避免的。
那limit的效率如何呢?结果比较令人失望。
假设我的user表中有超过20w条的记录。以下三个查询,所需要的时间。
select login_name from user order by login_name limit 200 , 10;
#还算比较快
select login_name from user order by login_name limit 2000 , 10;
#10倍慢于第一种情况
select login_name from user order by login_name limit 20000 , 10;
#1000倍慢于第一种,用时约0.5s
得到这个结果还是在login_name有索引的情况下的。
比较容易看到和猜到的是limit还是遍历了offset+limit数量的内容,所以如果offset数值比较大,
它就得花更多的时间。
我用了一下google搜索结果分页,上面标的时间差不多和我的结果一致,
所用时间和offset的数值是正相关的。
还有更令人失望的。
前面三个查询实际都实现了cover index,login_name建立了索引,查询和排序内容只和login_name有关,
所以整个查询只在那一个索引中就可以完成。之前有写过,关于cover index。
如果没有用cover index 会是如何呢?
select id , login_name , password from user order by login_name limit 20000 , 10
15s时间完成
虽然说limit遍历偏移量不能避免但是也只用了0.5s,而这用了接近30倍的时间,可以猜测,
在遍历login_name那个索引的同时肯定读取了很多user主记录表的很多内容。
如何优化这种情况??
第一个想到的是这么做
select id , login_name , password from user where id in (select id from user order by login_name limit 200000 , 10);
结果是发生了错误
//error
//not yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
非但如此我还想到了上次说的关于子查询效率的事情
仔细想想觉得不支持很有道理
1,不支持子查询,因为limit本身效率不是很高
2,子查询的效率更差
一个双重的陷阱,所以还是禁止为妙。
说了半天到底怎么优化了?采用和子查询相同的策略,用join
子查询用了join来优化
select id , login_name , password from user join (select id from user order by login_name limit 200000 , 10) as user2 using(id);
用了0.5x秒
explain 上述
三次查询
1,using index 的limit
2,没看懂,可能是join的过程
3, 主键in查询 10条,飞快