MySQL中order by的实现 和 by rand() 和优化
几种order by的情况
乍一看这个问题好像有点复杂,我们从最简单的case开始看起。
用这个表来说明:(10w行数据)
1、 最简单的order ―― order by索引字段
从explain的结果来看(Extra列),这个语句并不作排序。因为字段a已经是有顺序的。就是按照索引a的顺序依次读pk的值(在这里是隐藏的系统列),一个个从聚簇索引的data中读入。
2、 复杂一点 ―― order by 非索引字段
这里Extra列显示一个Using filesort。这里的filesort并不是指字面上的“文件排序”,说的就是与上面一种情况相比,在Server层作了排序。至于是否使用文件,取决于排序过程中的内存是否足够,不够则需要临时文件。
并不到此为止,我们细细想一下,server层要怎么作排序呢?
一个简单的想法是把表数据都读到内存,然后排序。读到内存当然可以想怎么整就怎么整。但是这个做法很耗费内存。需要占用与表一样大小的内存。
另外一个做法,只读入字段b和其对应的主键id。可以想象为这两个字段构成的结构体,按照b的值作排序。排序完成后,按字段b的顺序依次取主键id,取得结果返回。
实际上第二种作法就是这个例子中的实际执行过程。存放用于排序的字段值的结构我们成为sort_keys.
至于order by b,c这样的语句,效果与order by b相同,可以简单理解为上面结构体多了一个字段。
3、 字段函数排序
有了上面的流程,这里就简单了,还是按顺序读入所有的字段b,只是sort_keys中存的是b的长度而已。
4、 Order by rand()
按照自然想法, order by rand() 也可以仿照上面描述的做法,对于每一行,将生成的rand()的值放入sort_kyes里即可。但实际上上效果如下:
Extra字段里面有一个Using temporary, 也就是说用到了临时表。那么Using temporary的时候操作流程是怎样的呢?
a) 创建一个heap引擎的临时表,字段名为 ”” a b c d, 第一个字段为匿名;
b) 将表tb中的数据按行读入到临时表中,同时给第一字段填入一个随机实数(0,1);
c) 按照第一个字段排序,返回
d) 查询完成删除临时表
分析一下这个过程,由于把数据从InnoDB表里面读入临时表,则InnoDB表实际上也已经读入内存,在这个过程中,若不考虑内存不够时的写文件策略, 则内存中有两份表的全拷贝;另外多了从内存中将数据一一拷贝到临时表的过程。
这个查询在我的测试环境中耗时2.41s(多次次执行,不计第一次加载数据的时间)
order by rand()的改进
我们前面说过,实际上对于这种简单的order by rand() 的情况,也可以等同于按照非索引字段来处理。在sort_array 中存入随机值即可。
按照这个思路的patch在这里,效果上
执行时间减少为1.89s,性能提升21%, 这个例子单行1k,单行越大提升效果越好。
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:丁奇 来源: MySQLOPS 数据库与运维自动化技术分享
- 标签: order rand
- 发布时间:2012-05-02 23:40:31
- [54] IOS安全–浅谈关于IOS加固的几种方法
- [52] android 开发入门
- [52] 如何拿下简短的域名
- [51] 图书馆的世界纪录
- [49] Oracle MTS模式下 进程地址与会话信
- [49] Go Reflect 性能
- [47] 【社会化设计】自我(self)部分――欢迎区
- [46] 读书笔记-壹百度:百度十年千倍的29条法则
- [36] 程序员技术练级攻略
- [29] 视觉调整-设计师 vs. 逻辑