MySQL-排序相关原理分析

全字段排序和rowId排序

建表语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

sql语句如下：

select city,name,age from t where city='杭州' order by name limit 1000  ;

相关概念定义

sort_buffer：MySQL会给每个线程分配一块内存区域用于排序，这块区域叫sort_buffer。如果待排序的数据足够存放在sort_buffer中，那么就会直接用这块区域进行排序，算法为快速排序；如果待排序的数据超过了sort_buffer大小，会使用磁盘临时文件来辅助排序，算法为归并排序。

全字段排序：sort_buffer中存储的待排序数据，包括需要返回的所有字段，比如，上面sql语句中的city,name,age，虽然只用name来排序，但是还是冗余存放了city和age的数据，排序完直接返回即可。

rowId排序：sort_buffer中存储的待排序数据，只包括待排序字段和对应行的主键id，比如，上面sql语句，如果使用rowId排序，那么sort_buffer中只会存储name和rowID字段，等到排序完毕，需要回表查询出来需要返回的其他字段数据。

什么时候选择全字段排序？什么时候选择rowID排序？

当MySQL判断，当待处理表为InnoDB磁盘表时，会优先使用全字段排序，目的是为了减少rowID排序最后需要再次回表查询需要返回的字段的操作开销，但是全字段排序如果需要冗余的单行数据量太大时，就不会选择全字段排序，而选择rowID排序。

• 如何判断单行数据是否过大？MySQL中会使用max_length_for_sort_data来判断。

为什么单行数据量大，就需要切换算法？

如果单行数据量太大，内存中能存储下的行数就会变少，就需要使用更多的磁盘临时文件来存储，排序的性能会比较差。

---

内存临时表和磁盘临时表

看这个业务：

有一张单词表，我们需要随机显示三个单词给用户。

建表语句和生成数据存储过程：

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

SQL语句：

mysql> select word from words order by rand() limit 3;

注：rand() 这个函数会返回一个0~1之间的随机小数值。

当需要使用这个随机值来排序时，就需要使用临时表来存储这个随机数据。

内存临时表

执行过程如下：

1. 首先生成Memory引擎的内存临时表，在主键索引中，依次取出所有的word值，调用rand函数生成 一个随机值，把word和随机值存储到临时表中。
2. 然后针对这个临时表开始排序。使用sort_buffer并且使用rowId算法。
   1. 这里为什么使用rowID算法了呢？因为上面提到过的全字段排序会被优先选择，前提是待排序的表是磁盘表；现在的待排序表为Memory引擎的内存表，虽然使用rowID，但是最后的回表查询都是在内存中完成的，开销大大降低，MySQL当然会选择可以一次排序更多行的rowId算法。

磁盘临时表

MySQL中有一个参数，tmp_table_size 这个参数限制了内存临时表的大小，默认值是 16M。如果临时表大于16M，就会使用磁盘临时表来存储临时数据，默认是InnoDB引擎表。关于默认的InnoDB引擎表的排序过程，在上面的全字段排序和rowId排序中已经介绍过了。

---

新的排序算法

上面介绍过，InnoDB磁盘表，要么使用基于全内存的快排，要么基于辅助的磁盘临时文件的归并排序，其实在MySQL5.6之后，还引入了一种新的排序算法，优先队列排序算法。

为什么需要这种算法？

考虑刚才的SQL语句

mysql> select word from words order by rand() limit 3;

无论是使用快排还是归并排序，他们都是基于所有的数据进行排序。

但分析上面的sql语句，其实我们只需要排序后的前面三条数据，并且后面的排序数据在计算上来说是浪费资源的。有没有一种算法，可以通过排序，只得到我们需要的最小的三条或者最大的三条数据，并且尽量不使用磁盘临时文件呢？

优先队列排序算法

优先队列排序算法，如果执行上面的sql语句，会先从表中顺序取最开始的3条数据，存储到一个最大堆中（最大堆：堆头永远是容器内数据的最大值）。然后遍历后面的所有数据，判断当前取值和堆最大值比较，如果比堆最大值小，就把新数据入堆，并且重新排序堆中的顺序，保持堆头为最大值。

经过这种排序以后，堆中就是我们需要的前三个最小值了。

示意图如下：

什么时候会选择这种算法？

当存在limit字句时，并且limit需要的维护的最大堆的大小小于 sort_buffer，就会使用这个算法。