MySQL排序规则
在实际查询应用中,往往需要对一个或多个字段进行排序,来保证结果集数据的有序性。当我们在执行计划的Extra出现了Using filesort的时候就表示语句需要排序,MySQL会给每个线程分配一块排序内存,由参数sort_buffer_size控制大小,如果sort buffer放不下,则需要通过临时文件来进行排序,通过OPTIMIZER_TRACE可以查看SQL是否使用临时文件进行排序
/* 打开optimizer_trace,只对本线程有效 */ |
number_of_tmp_files为0则表示未使用临时文件排序,examined_rows表示查询扫描的行数,sort_mode为排序模式
现有如下数据:
mysql> show create table users; |
计划查询country为china的name,age,country,并且按照age排序
mysql> select name,age,country from users where country='china' order by age; |
全字段排序
在该排序模式下该语句的执行过程如下:
- 初始化sort buffer,确定放入要查询的name,age,country
- 从country上的索引查找第一个country=china的主键
- 到主键索引上,取出name,age,country三个字段的值,存到sort_buffer中
- 从country索引查询下一个符合条件的主键值
- 重复执行上面两步,直到不满足查询条件
- 对sort_buffer中的数据按照age进行排序
- 返回结果集给客户端
ROWID排序
如果排序中单行的数据长度超过了max_length_for_sort_data参数,排序会采用ROWID的排序算法,ROWID排序的过程如下:
- 初始化sort_buffer,确定要放入age和id
- 从country索引查找第一个country=china的主键
- 到主键索引上取出整行,将age和id放入sort_buffer
- 从country索引获取下一行满足条件的数据
- 重复执行上面两步,直到不满足查询条件
- 对sort_buffer中的age进行排序
- 遍历排序结果,回到数据表中取出对应的name,age,country返回给客户端
ROWID排序相对全字段排序减少了sort_buffer中包含的字段,能够包含更多的行数据,但ROWID多了一次主键索引的访问,增加了磁盘读。
排序优化
通常都是利用索引有序的特性来优化排序,避免额外的排序。我们可以创建country和age的联合索引,查询过程就变成下面的过程
- 从联合索引找到第一个满足country=china的主键ID
- 到主键索引上取出整行,把name,age,country作为结果集的一部分直接返回
- 从联合索引取下一个主键ID
- 重复上述两个操作,直到不满足查询条件退出
这时执行计划的Extra中就不存在Using filesort了,意味着查询不再需要排序。
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 DBA学习记录!