“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 字段上创建索引,然后执行下面语句
select city,name,age from t where city='杭州' order by name limit 1000 ;
- 通过 explain 结果会出 Extra 字段中,出现 Using filesort,表示需要排序,MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/4b5cbff602871aaac6c4c0c733fe4d53.png)
- 上述语句执行流程如下
![![在这里插入图片描述](https://img-blog.csdnimg.cn/05312064b47d43b18a5e5ff5a538aa55.png](https://i-blog.csdnimg.cn/blog_migrate/3c1310408f867f0a92448cc0e12c5cb5.png)
- 初始化 sort_buffer,确定放入 name、city、age 这三个字段
- 从索引 city 找到第一个满足 city='杭州’ 条件的主键 id,也就是图中的 ID_X
- 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中
- 从索引 city 取下一个记录的主键 id
- 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y
- 对 sort_buffer 中的数据按照字段 name 做快速排序
- 按照排序结果取前 1000 行返回给客户端
- 其中,排序的时候可能会用到外部排序,就需要设置
sort_buffer_size
,避免导致 sort_buffer 太小而不得不利用磁盘临时文件来辅助排序 - 确定排序语句是否使用了临时文件的方法
SET optimizer_trace='enabled=on';
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;
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
SELECT VARIABLE_VALUE INTO @b FROM performance_schema.session_status WHERE variable_name = 'Innodb_rows_read';
SELECT @b-@a;
- 其中,在 SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G 的内容中如果出现
number_of_tmp_files
,就表明用了临时表的份数(8.0 版本好像没看着)
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/0bd429f6651aadda0bf886cbebe49fb1.png)
- 而 SELECT @b-@a; 代表的是查询前后获取的值的相减,得到的意思是整个过程扫描了多少行
- 如果发现值和预期值多 1,就需要设置
internal_tmp_disk_storage_engine
属性为 MyISAM(默认 InnoDB),可能是因为查询 OPTIMIZER_TRACE 时,用到了临时表,所以会加 1
rowid 排序
- 全字段排序会把要返回的字段放到 sort_buffer 中,如果字段太多,就会分成多个临表
- 当排序单行太大,MySQL 会用另外一种算法,例如更改
max_length_for_sort_data
专门用于控制排序的行数据参数
SET max_length_for_sort_data = 16;
- city、name、age 这三个字段的定义总长度是 36 > 16,所以 sort_buffer 只会放入 排序的列 和 主键 id
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/791c734801a7b97abde4dbbc94433873.png)
- 初始化 sort_buffer,确定放入两个字段,即 name 和 id
- 从索引 city 找到第一个满足 city=‘杭州’ 条件的主键 id,也就是图中的 ID_X
- 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中
- 从索引 city 取下一个记录的主键 id
- 重复步骤 3、4 直到不满足 city='杭州’条件为止,也就是图中的 ID_Y
- 对 sort_buffer 中的数据按照字段 name 进行排序
- 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端
- 其中 “结果集” 是逻辑概念,实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的
- 如果用刚刚的 确定临时文件的方法,会发现比以前多了一些值,就是因为要 id 去原表取值
联合索引与索引覆盖
- 对表的字段 city 和 name 的联合索引
alter table t add index city_user(city, name);
- 查询过程就变成下面这样
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c9951cba87bfc5df1b84985c21c65855.png)
- 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id
- 到主键 id 索引取出整行,取 name、city、age 三个字段的值,作为结果集的一部分直接返回
- 从索引 (city,name) 取下一个记录主键 id
- 重复步骤 2、3,直到查到第 1000 条记录,或者是不满足 city=‘杭州’ 条件时循环结束
- 通过 explain 查询,就发现没有出现 Using filesort
- 如果想更快,就进行索引覆盖吧
alter table t add index city_user_age(city, name, age);
- 执行流程将会如下
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e7c3e8923498d5a55e6d7a57b5e80431.png)
- 从索引 (city,name,age) 找到第一个满足 city=‘杭州’ 条件的记录,取出其中的 city、name 和 age 这三个字段的值,作为结果集的一部分直接返回
- 从索引 (city,name,age) 取下一个记录,同样取出这三个字段的值,作为结果集的一部分直接返回
- 重复执行步骤 2,直到查到第 1000 条记录,或者是不满足 city=‘杭州’ 条件时循环结束