MySQL分页优化 limit n,m

作/译者：陶会祥 来源：http://blog.chinaunix.net/u3/107145/ 欢迎转载,请注明作者及出处~

近日分享DB机器慢查较多,发现存在很多慢查SQL如下：

原始SQL:

select id, user_id, type, url, url_md5 as url_md_5, thumb_url, title, summary,.... from share_0 force index (idx_userid_id) where user_id = 288519500 order by id desc limit 940, 20;

因为上句sql 要取出title, summary,.... 等字段的记录940+20条，最后却只取20条，完全是浪费，影响性能~

讨论后给出优化方案：

方案1.select id from share_0 force index (idx_userid_id) where user_id = 288519500 order by id desc limit 940, 1;   

select id,user_id,summary from share_0 force index (idx_userid_id) where user_id = 288519500 and id <= 2766163484 order by id desc limit 10;

方案2. select id from share_0 force index (idx_userid_id) where user_id = 288519500 order by id desc limit 940, 10;  

select id,user_id,summary from share_0   where id in (xxx);

方案3.desc select ta.id,user_id,summary,title from share_0 ta, ( select id from share_0 force index (idx_userid_id) where user_id = 288519500 order by id desc limit 940, 10 ) tb where ta.id=tb.id;

分析：

从索引的角度来看方案1，2都使用到了覆盖索引并可以取较少的IO,性能较原始SQL好~