MySQL的order by事变道理

在措施计划傍边，我们许多场景下城市用 group by 要害字。好比在分页读取数据时，为了停止一再扫描记录，这就是必必要行使 group by 了。

好比我们行使如下 DDL 建设表：

CREATE TABLE `user_info` ( 
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', 
 `city` varchar(16) NOT NULL COMMENT '都市', 
 `name` varchar(16) NOT NULL COMMENT '姓名', 
 `age` int(11) NOT NULL COMMENT '年数', 
 `addr` varchar(128) DEFAULT NULL COMMENT '地点', 
 PRIMARY KEY (`id`), 
 KEY `city` (`city`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

而且我们会执行如下查询语句

SELECT city,`name`,age FROM user_info WHERE city='上海' ORDER BY `name` LIMIT 1000; 

全字段排序

由于上面的建表语句已经在 city 字段上面建设索引了，当我们行使 EXPLAIN 呼吁时，会有如下功效：

上面 Extra 字段中的 “Using filesort” 暗示的就是必要排序，MySQL 会为每个线程分派一块内存用于排序，成为 sort_buffer。下面我们看一下 index(city) 的布局表示图。

执行流程如下：

1. 初始化 sort_buffer，确定放入 city name age 这 3 个字段；
2. 从 city 索引中获取到第一个 city='上海' 的记录，也就是 id_x；
3. 到主键索引中获取对应的记录，并取出 name city age 的值放入 sort_buffer；
4. 取下一条切合前提的记录，一再 3 4 的操纵，直至不切合前提为止；
5. 对 sort_buffer 中的数据凭证 name 做快速排序；
6. 取出前 1000 条数据并返回。

我们暂且叫这种排序进程为“全字段排序”，如下所示：

图中的“按 name 排序” 也许在内存中，也也许行使磁盘文件排序，这取决与排序所必要的内存和 sort_buffer_size 。sort_buffer_size 就是 MySQL 为排序开发的内存巨细，当所需内存小于 sort_buffer_size 时，就直接在内存中完成排序，假如所必要的内存 大于 sort_buffer_size ，就必要特另外磁盘空间帮助排序。

rowid 排序

上面的算法在数据量较量大的时辰，也许会呈现一些题目。由于在排序的时辰，存放了全部的返回字段，增进了 排序空间 （sort_buffer）的压力。

SET max_length_for_sort_data=16; 

max_length_for_sort_data 是MySQL 限定排序行巨细的参数。意思是，假如排序行巨细高出了这个值，就会另选排序算法。上面 name city age 3 个字段的巨细为 36，大于 16 ，在新的算法中将只有 name (排序字段) 和id 参加 sort_buffer 中的排序。进程如下

1. 初始化 sort_buffer，确定放入 name id 这 2 个字段；
2. 从 city 索引中获取到第一个 city='上海' 的记录，也就是 id_x；
3. 到主键索引中获取对应的记录，并取出 name id 的值放入 sort_buffer；
4. 取下一条切合前提的记录，一再 3 4 的操纵，直至不切合前提为止；
5. 对 sort_buffer 中的数据凭证 name 做快速排序；
6. 取出前 1000 条数据，然后按照 id 取出对应记录的 name city age 3 个字段并返回功效。

这种排序进程，我们称为 rowid 排序，进程如下所示：

全字段排序 VS rowid 排序

从上面 2 个流程看来，假如内存足够时，MySQL 会让返回值中全部字段存放在排序空间。当MySQL 内存过小时，才会思量行使rowid 排序。可是从上面的流程看来，rowid 排序在返回功效前，还会再一次的回表。因此MySQL 以为内存富裕的时辰，会优先回收 全字段排序。

上面的场景是：city 字段过滤后，name 字段不是有序的。着实我们可以通过连系索引来规避掉 name 字段的排序。

alter table user_info add index idx_city_user(city, name); 

下面我们看一下连系索引的表示图：

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!