Mysql存储原理（2）

今天我们来讲一下排序

mysql的排序方式是，我们先根据条件查询出结果集，然后在内存中对结果集进行排序，如果结果集数量比较大，还需要将结果集写入到多个文件中去，然后单独进行排序，然后在文件间进行归并排序，排序完成后在进行 limit 操作。

CREATE TABLE `person` (
  `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;

select city,name,age from person where city='武汉' order by name limit 100  ;

 使用 explain 发现该语句会使用 city 索引，并且会有 filesort . 我们分析下该语句的执行流程

• 1.初始化 sortbuffer ，用来存放结果集

• 2.找到 city  索引，定位到 city 等于武汉的第一条记录，获取主键索引ID

• 3.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段放入 sortbuffer

• 4.在 city 索引取下一个 city 等于武汉的记录的主键ID

• 5.重复上面的步骤，直到所有 city 等于武汉的记录都放入 sortbuffer

• 6.对 sortbuffer 里的数据根据 name 做快速排序

• 7.根据排序结果取前面 1000 条返回

这里面查询得是city,name,age三个字段，比较少。如果查询得字段比较多，他们都会放在sortbuffer里面，sortbuffer会占据大量内存空间。另一个方案就是只取出待排的字段和主键放在sortbuffer里面，这里是 name 和 id ,排序完成后在根据 id 取出需要查询的字段返回，其实就是时间换取空间的做法，这里通过 max_length_for_sort_data 参数控制，是否采用后面的方案进行排序。 

另外如果 sortbuffer 里的条数很多，同样会占有大量的内存空间，可以通过参数 sort_buffer_size 来控制是否需要借助文件进行排序，这里会把 sortbuffer 里的数据放入多个文件里，用归并排序的思路最终输出一个大的文件。

以上方案主要是 name 字段没有加上索引，如果 name 字段上有索引，由于索引在构建的时候已经是有序的了，所以就不需要进行额外的排序流程只需要在查询的时候查出指定的条数就可以了，这将大大提升查询速度。我们现在加一个 city 和 name 的联合索引

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

 

• 1.根据 city,name 联合索引定位到 city 等于武汉的第一条记录，获取主键索引ID

• 2.根据 ID 去主键索引上找到对应记录，取出 city,name,age 字段作为结果集返回

• 3.继续重复以上步骤直到 city 不等于武汉，或者条数大于 1000

由于联合所以在构建索引的时候，在 city 等于武汉的索引节点中的数据已经是根据 name 进行排序了的，所以这里只需要直接查询就可，另外这里如果加上 city, name, age 的联合索引，则可以用到索引覆盖，不用到主键索引上进行回表。