示例语句:select city,name,age from t where city='杭州' order by name limit 1000;
全字段排序
MySQL会给每个线程分配一块内存用于排序,称为sort_buffer。
city索引示意图:
通常情况这个语句的执行流程如下所示:
- 初始化sort_buffer,确定放入name,city,age这三个字段;
- 从索引city找到第一个满足city='杭州'条件的主键id,也就是上图中ID_X;
- 到主键id索引取出整行,取name,city,age三个字段的值,存入sort_buffer中;
- 从索引city取下一个记录的主键id;
- 重复步骤3、4直到city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;
- 对sort_buffer中的数据按照字段name做快速排序;
- 按照排序结果取前1000行返回给客户端。
流程图如下:
全字段排序的排序过程可能在内存中完成,也可能使用外部排序,取决于排序所需的内存和参数sort_buffer_size。
sort_buffer_size,就是MySQL为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于sort_buffer_size,排序就在内存中完成,否则就要利用磁盘临时文件辅助排序。
外部排序可能会使用到多个临时文件,因为外部排序一般使用归并排序算法,每个文件单独排序,然后将这些排序后的临时文件合并成一个有序的大文件。
sort_buffer_size越小,需要分成的份数就越多。
rowid排序
全字段培训只对原表数据读了一遍,剩下的都是在sort_buffer和临时文件中执行的。但是有个问题,如果查询要返回的字很多,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,要分成很多个临时文件,排序性能会很差。
如果MySQL认为排序的单行长度太大会怎么做呢?
参数max_length_for_sort_data,是MySQL中专门控制用于排序的行数据长度的一个参数。意思是,如果当行的长度(用于排序的所有字段定义长度的总和)超过这个值,MySQL就认为单行太大,要还一个算法。
新的算法放入sort_buffer的字段,只有要排序的列(即name)和主键id。
但这时因为排序结果少了字段,不能直接返回,整个执行流程如下:
- 初始化sort_buffer,确定放入两个字段,即name和id;
- 从索引city找到第一个满足条件city='杭州'的主键id;
- 到主键id索引取出整行,取name,id这两个字段放入sort_buffer中;
- 从索引city取下一个记录的主键id;
- 重复步骤3、4直到不满足city='杭州'条件为止;
- 对sort_buffer中的数据按照字段name进行排序;
- 遍历排序结果,取前1000行,并按照id的值回到原表中取出city、name和age三个字段返回给客户端。
执行流程如下图:
说明:最后的“结果集”是一个逻辑概念,根据主键id查到所需要的字段后直接就返回给客户端了,不会再占用内存存储结果。
全字段排序VS rowid排序
如果MySQL实在是担心排序内存太小,会影响排序效率,才会采用rowid排序算法,这样排序过程中一次可以排序更多行,但是需要再回表去取数据。
如果MySQL认为内存足够大,会优先选择全字段排序,把所有字段都放在sort_buffer中完成排序。
这也就提现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。
对于InnoDB来说,rowid排序会要求回表多造成磁盘读,因此不会被优先选择。
并不是所有的order by语句都需要排序,如果字段天然就是有序的,就不需要排序了。
比如可以给city和name建立联合索引,这样name本来就是有序的了。
还有索引覆盖,索引上的信息足够满足信息查询,不需要再回表。可以建立city,name,age的联合索引。
当然并不是为每个查询都用上覆盖索引,毕竟索引也是又维护代价的,需要权衡决定。