【转】大数据量下,分页查询优化方法解释

时间:2022-11-12 11:31:55

原文地址:http://blog.sina.com.cn/s/blog_6353885f0100gn4q.html

 

算法如这篇文章

http://hi.baidu.com/%D4%E7%B9%C8%C9%E7%C7%F8/blog/item/1fd6f3d04927fd86a1ec9c44.html

之前老王也发过此方法:

http://hi.baidu.com/thinkinginlamp/blog/item/a352918fe70d96fd503d925e.html

 

简单解释一下,也作为思路整理:

 

1,使用stat作为一个单一索引

desc Select id from table where stat=1 order by id limit 90000,5 ; 

|  1 | SIMPLE      | table | ref  | stat       | stat | 1      | const |    151906 | Using where; Using filesort |

正常执行分页语句,通过WHERE条件圈定了符合的数据集此时还是用到了filesort,为什么?因为索引只对WHERE条件过滤起到了作用,对ORDER无效。MYSQL操作时的微观应该是这样的:

根据索引限定了结果集范围-》取出结果集数据暂存起来-》进行排序-》输出给客户端。

此时就算是索引去掉了大部分数据,如果表数据基数大,剩下的数据量仍然很大,暂存结果集,并进行排序的过程仍会很长。

 

我们现在优化索引,将ID加到STAT后面形成复合索引:KEY `stat` (`stat`,`id`)

再次执行SQL,运行的时间差不多。EXPLAIN的结果如下

|  1 | SIMPLE      | sp_photo_ff | ref  | pic_stat      | pic_stat | 1       | const | 151906 | Using where |

因为用到了索引进行排序,所以FILESORT没了。那为什么速度仍然不快?我认为MYSQL仍然按上面的流程执行的。。。所以在进行取数据和排序时的时间消耗仍然很大。

 

当我们最终改为两条SQL实现这个,第一条,即只根据条件和排序取出ID,第二条,通过ID进行准确条件查询。第一条SQL是个covering index,即并没有真的从表中出数据,而是通过索引即完成了查询操作,速度当然是最快的,第二条,通过主键进行,速度也是最快的。所以虽然是执行了两条SQL,但速度反而无可比。最早在看老王的这篇文章时,我没能明白,现在才真弄表白了

 

 

在写这篇博文时,要用事实说话,我发现我昨天的想法仍然有错。昨天我认为,当取出的字段不在索引的字段范围中时,MYSQL会先把WHERE过滤的记录暂存起来,再进行排序,因为他要保证对不同的排序此方法都适用。事实是,这个想法是错的 ,上面看到了,两种索引情况下,EXPLAIN的结果中少了一个FILESORT。所以MYSQL还是足够聪明的,它先进行了排序,然后才暂存了记录集。是否真的是这样?那只能去MYSQL内部找答案了。

 

原文地址:http://blog.sina.com.cn/s/blog_6353885f0100gn4q.html