order by 排序

举例说明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;

查询语句：

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

1.全字段排序

通过explain查看这个语句的执行情况：

上图中，extra字段中的"Using filesort"意思就是需要排序，mysql会给每个线程分配一块内存用于排序，称为 sort buffer. sort buffer是server层的。

为了说明这个sql语句的执行过程，先来看下city的索引示意图：

如上图，满足条件的为 ID(X)到ID(X+N)之间的这些记录。

通常情况下，该语句的执行流程如下：

a.初始化sort buffer,确定放入city,name,age三个字段

b.从索引city中找到第一个满足city=杭州的主键id,即图中的idx

c.到主键索引中取出idx的整行记录，取出name,city,age三个字段的值，放入sort buffer中。

d.从索引city中取下一个满足条件的主键id,重复b,c。直到city的值不是杭州为止。

e.对sort buffer中的数据按照字段name做快速排序。

f.根据排序结构取前1000行返回给客户端。

上述过程就是全字段排序。其中，按name排序这个操作，可能在内存中完成，也可能需要使用外部排序。这取决于排序所需内存及参数sort_buffer_size。sort_buffer_size就是sort_buffer的大小。如果要排序的数据量小于sort_buffer_size,排序就在内存中完成，否则，内存中放不下，就要利用磁盘临时文件辅助排序。

 

2. rowid排序

上述的全字段排序，只是将原表数据读了出来，之后的操作都是在sort_buffer和临时文件中执行的。但是如果查询需要返回的字段很多，内存中放不下，就需要用到多个临时文件(磁盘io很慢)，性能会很差。

如果mysql认为排序的单行长度过大，可以通过修改参数 max_length_for_sort_data来使得mysql使用rowid排序。

max_length_for_sort_data，如果单行长度超过这个值，mysql就会放弃全字段排序，而使用rowid排序。

这种排序方法，只会将 要排序的列和主键放入sort buffer中，即只有name,id会放入sort buffer中。整个执行流程如下：

a.初始化sort buffer，确定放入name和id

b. 从索引中找到第一个满足条件的主键id，即IDX

c 到主键索引取出整行，取出name,id两个字段值，存入sort buffer

d, 从索引city找下一个满足条件的主键id，重复c,d ，直到不满足查询条件未知，即到IDY截止。

e,对sort buffer中的数据按照name值排序

f，遍历排序结果，取前1000行，并按照id的值到原表中取出city,name和age三个字段返回给客户端。

 

可以看出，rowid比全字段步骤多了一个 步骤：多访问了一次主键索引

上面已经说过，如果内存足够，会优先选择全字段排序，否则内存太小的话,mysql采用rowid排序算法,这样可以一次排更多行，但需要再回到原表(主键索引)去取数据。

mysql的设计思想：如果内存够，就优先多使用内存，尽量减少磁盘访问。

对于innoDB引擎的表来说，rowid排序会要求回表多，造成优先读，因为不会被优先选择。

3.order by的排序，mysql排序是成本比较高的操作。可以通过改变索引来优化这种操作。在这个例子中，可以给city和name建立联合索引，这样，只要city的值是杭州，那么取到 的name值就一定是有序的。而且，假如有5000行满足city="杭州"，如果没有city和name联合索引，就需要把满足条件的五千行都扫描一遍，再按name排序，而有了city和name的联合索引之后，就不需要扫描那么多行了，只需要找到满足条件的前1000行就可以了，即扫描行数减少到了1000行。

就这个例子来说，现在不需要排序了，但还是最后要回表,因此还可以通过索引覆盖来避免回表：那就是建立city,name,age的联合索引，对于city字段值相同的行来说，还是按照name字段的值递增排序的，这样，仍然不需要排序，而且找到后需要返回的字段在联合索引上都有，也不需要再回表了。