MySQL学习笔记-order by工作原理

MySQL学习笔记-order by工作原理

1.表结构

CREATE TABLE `t` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL DEFAULT '',
  `age` int unsigned NOT NULL DEFAULT '0',
  `addr` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `city` (`city`) USING BTREE
) ENGINE=InnoDB;

2.插入实验数据

delimiter;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=20000)do
	  if i<=15000 then
			insert into t values(i,SUBSTRING("杭州杭州杭州杭州杭州",1,RAND()*10), SUBSTRING("爱因诗贤爱因诗贤爱因诗贤",1,RAND()*10), RAND()*100,SUBSTRING("地址地址地址地址地址",1,RAND()*10));
		else
			insert into t values(i,"武汉", SUBSTRING("爱因诗贤爱因诗贤爱因诗贤",1,RAND()*10), RAND()*100,SUBSTRING("地址地址地址地址地址",1,RAND()*10));
		end if;
    set i=i+1;
  end while;
end;
delimiter ;
call idata();

Tips：使用存储过程插了 20000 条数据，其中插入了 5000 条 city='武汉' 的数据。

3.执行排序查询

select city,name,age from t where city='武汉' order by name limit 1000;

执行结果如下图：

4.explain 分析

explain select city,name,age from t where city='武汉' order by name limit 1000;

Tips：可以看到扫描行数 rows=5000 行，Extra 中的 Using filesort 表示需要排序。

5.全字段排序流程

若查询要返回的字段很少，每行要返回的结果集数据比较小，内存中能同时存放的行数比较多，全字段排序流程如下：

• **（1）**初始化 sort_buffer，确定放入 name、city、age 这三个字段
• （2） 从二级索引树 city 上找到第一个满足 city='武汉' 条件的主键 id
• **（3）**然后到主键索引树上通过 id 取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中
• **（4）**从二级索引树 city 取下一个记录的主键 id；
• **（5）**重复步骤 （3）、（4） 直到 city 的值不满足查询条件为止
• **（6）**对 sort_buffer 中的数据按照字段 name 做快速排序
• **（7）**按照排序结果取前 1000 行返回给客户端

Tips：MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，其中第 （6） 步按照 name 快速排序，可能在内存中完成，也可能需要外部排序，取决于参数 sort_buffer_size 的大小。

6.rowid 排序流程

如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差，先设置一个参数，可以让 MySQL 采用 rowid 的方式来排序：

SET max_length_for_sort_data = 16;

Tips：max_length_for_sort_data 表示用于排序的行数据的长度的参数，表示如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个排序算法，city、name、age 这三个字段的定义总长度是 36，比 16 大。

rowid 排序流程如下：

• **（1）**初始化 sort_buffer，确定放入两个字段，即 name 和 id
• （2） 从二级索引树 city 上找到第一个满足 city='武汉' 条件的主键 id
• **（3）**然后到主键索引树上通过 id 取出整行，取 name、id 这两个字段，存入 sort_buffer 中
• **（4）**从二级索引树 city 取下一个记录的主键 id；
• **（5）**重复步骤 （3）、（4） 直到 city 的值不满足查询条件为止
• **（6）**对 sort_buffer 中的数据按照字段 name 排序
• **（7）**遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端

Tips：相比于全字段排序，rowid 排序多了一次回表

7.优化建议

• 上述排序是按照 name 字段进行排序的，如果 name 字段本来就是有序的，那么在排序查询就不需要使用排序这个步骤，因此可以建立 city、name 联合索引：

ALTER TABLE `t` DROP INDEX `city`,ADD INDEX `city_user`(`city`, `name`) USING BTREE;

explain 分析如下：

explain select city,name,age from t where city='武汉' order by name limit 1000;

Tips：此时 Extra 中没有 Using filesort 了，因此不需要排序了。

• 由于最后返回字段总共包含 city、name、age 三个字段，为了减少回表次数，可以在上面基础上，直接建立 city、name、age 联合索引：

ALTER TABLE `t` DROP INDEX `city`,
ADD INDEX `city_user_age`(`city`, `name`, `age`) USING BTREE;

扫码关注