mysql limit的效率及优化和不支持limit子查询的猜测[数据库]

原文地址:http://kkito.cn/index.php/blog/getArticle/163/mysql_limit%E7%9A%84%E6%95%88%E7%8E%87%E5%8F%8A%E4%BC%98%E5%8C%96%E5%92%8C%E4%B8%8D%E6%94%AF%E6%8C%81limit%E5%AD%90%E6%9F%A5%E8%AF%A2%E7%9A%84%E7%8C%9C%E6%B5%8B

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条，飞快