Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

16 | “order by”是怎么工作的? #26

Open
git-zjx opened this issue Jul 31, 2019 · 0 comments

Comments

@git-zjx
Copy link
Owner

commented Jul 31, 2019

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

全字段排序

5334cca9118be14bde95ec94b02f0a3e

  1. 初始化 sort_buffer,确定放入 name、city、age 这三个字段;
  2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id,也就是图中的 ID_X;
  3. 到主键 id 索引取出整行,取 name、city、age 三个字段的值,存入 sort_buffer 中;
  4. 从索引 city 取下一个记录的主键 id;
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止,对应的主键 id 也就是图中的 ID_Y;
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序;
  7. 按照排序结果取前 1000 行返回给客户端。

6c821828cddf46670f9d56e126e3e772

图中“按 name 排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。

确定一个排序语句是否使用了临时文件的办法

/* 打开 optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
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; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

89baf99cdeefe90a22370e1d6f5e6495
通过查看 OPTIMIZER_TRACE 的结果来确认的,你可以从 number_of_tmp_files 中看到是否使用了临时文件,number_of_tmp_files 表示的是,排序过程中使用的临时文件数,内存放不下时,就需要使用外部排序,外部排序一般使用归并排序算法。

如果 sort_buffer_size 超过了需要排序的数据量的大小,number_of_tmp_files 就是 0,表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort_buffer_size 越小,需要分成的份数越多,number_of_tmp_files 的值就越大

examined_rows 表示参与排序的行数
sort_mode 里面的 packed_additional_fields 的意思是,排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16),在排序过程中还是要按照实际长度来分配空间的。

rowid 排序

如果需要返回的列的总长度大于 max_length_for_sort_data 则会使用rowid 排序算法。新的算法放入 sort_buffer 的字段,只有要排序的列(即 name 字段)和主键 id。

SET max_length_for_sort_data = 16;
  1. 初始化 sort_buffer,确定放入两个字段,即 name 和 id;
  2. 从索引 city 找到第一个满足 ciy='杭州’条件的主键 id,也就是图中的 ID_X;
  3. 到主键 id 索引取出整行,取 name、id 这两个字段,存入 sort_buffer 中;
  4. 从索引 city 取下一个记录的主键 id;
  5. 重复步骤 3、4 直到不满足 city='杭州’条件为止,也就是图中的 ID_Y;
  6. 对 sort_buffer 中的数据按照字段 name 进行排序;
  7. 遍历排序结果,取前 1000 行,并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
    dc92b67721171206a302eb679c83e86d

rowid 排序多访问了一次表 t 的主键索引

需要说明的是,最后的“结果集”是一个逻辑概念,实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id,然后到原表查到 city、name 和 age 这三个字段的结果,不需要在服务端再耗费内存存储结果,是直接返回给客户端的。

27f164804d1a4689718291be5d10f89b
从 OPTIMIZER_TRACE 的结果中,你还能看到另外两个信息也变了。

  • sort_mode 变成了 <sort_key, rowid>,表示参与排序的只有 name 和 id 这两个字段。
  • number_of_tmp_files 变成 10 了,是因为这时候参与排序的行数虽然仍然是 4000 行,但是每一行都变小了,因此需要排序的总数据量就变小了,需要的临时文件也相应地变少了。

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大,会优先选择全字段排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。

什么情况下 ORDER BY 不用排序

查询使用到联合索引或者使用了覆盖索引

@git-zjx git-zjx added this to MySQL实战45讲 in MySQL Aug 1, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
1 participant
You can’t perform that action at this time.