mysql--order by排序

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;

如果要查询city是杭州的所有人名字,并且按姓名排序返回前1000个人的姓名和年龄,sql可以这么写:

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

全字段排序:

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
7. 按照排序结果取前 1000 行返回给客户端。

排序过程中,可能在内存中完成,也可能使用外部排序,取决于排序所需内存以及参数 sort_buffer_size,小于这个值则使用内存快速排序,否则使用外部归并排序

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;  //值为4001

number_of_tmp_files 表示的是，排序过程中使用的临时文件数。
packed_additional_fields表示字符串做了“紧凑”处理,name字段为varchar(16),排序过程中按实际长度分配空间

rowid排序

当返回字段太多时,sort_buffer中存放的行数会很少,需要使用多个临时文件,排序性能会很差;

SET max_length_for_sort_data = 16; --如果单行长度超过这个值,就会使用rowid排序

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
   

全字段排序VSrowid排序

全字段排序浪费内存,在内存足够时会使用,
rowid排序会要求回表多造成磁盘读,不会被优先选择

如果name字段本身就是有序的,那就可以不需要排序操作,节省响应时间;
如果在表上创建一个city和name的联合索引:

alter table t add index city_user(city, name);

1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；从索引 (city,name) 取下一个记录主键 id；
3. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。
   

如果使用覆盖索引,则可以避免回表,进一步节省时间

alter table t add index city_user_age(city, name, age);

随机展示信息

某个英语学习APP首页需要随机展示三个单词,数据库如下:

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可以使用rand():

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

上述sql执行过程中会使用临时表,临时表优先选择rowid排序

1. 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
3. 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
5. 从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
6. 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。
   

总结: order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

set tmp_table_size=1024; --临时表大小,默认16M,这个值决定使用内存临时表还是临时文件表
set sort_buffer_size=32768; --排序缓存大小,这个值决定内存排序还是磁盘排序
set max_length_for_sort_data=16; --排序单行数据长度,决定rowid排序还是全字段排序
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

number_of_tmp_files 的值居然是 0,原因是mysql5.6引入了优先队列排序算法(堆排序),
filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。
执行流程如下:

1. 对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；
2. 取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
3. 重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。