Mysql排序算法

参考MySQL45讲

准备工作

假设这个表的部分定义是这样的：

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  ;

全字段排序

将需要查询的字段和排序字段一起排序

使用explain命令查看语句的执行情况

==Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。==

通常情况下，这个语句执行流程如下所示 ：

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行返回给客户端。

我们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示，下一篇文章中我们还会用到这个排序。

rowid排序

如果MySQL认为排序的单行长度太大，则会使用rowid算法

查看单行长度：show VARIABLES like ‘max_length_for_sort_data’;

==rowid算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。==

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

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三个字段返回给客户端。

这个执行流程的示意图如下，我把它称为rowid排序。

全字段排序 VS rowid排序

==对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。==

优化

尽量保证索引树的数据是有序的，这样使用order by的时候就不需要额外的开销进行排序了

1. 将排序字段与查询字段建立联合索引

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

   

   

2. 使用覆盖索引，减少回表

   

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