16 | "order by"是怎么工作的

HigginCui
212 阅读6分钟

全字段排序

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;

查询的SQL如下：

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

在city字段加上索引后，用explain命令查看执行情况：

image.png

  • Extra中的"Using filesort"表示：需要排序。
  • MySQL为每个线程分配一块内存用于排序，称为sort_buffer。

image.png 基于上述这个SQL查询语句的执行过程 （图中满足city="杭州"条件的索引为ID_X到ID_(X+N)这些记录）

  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行返回给客户端

image.png

图中“按name排序”这个动作可能在内存中完成，也可能需要外部排序，取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size参数：

  • MySQL为排序开辟的内存（sort_buffer）的大小
  • 如果需要排序的数据量<sort_buffer_size，排序就在内存中完成。
  • 如果排序的数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

如何确定一个排序语句是否使用了临时文件：通过查看OPRIMIZER_TRACE的结果来确认。

-- 1. 打开optimizer_trace，只对本线程有效。
set optimizer_trace = `enable=on`;

-- 2. @a 保存Innodb_rows_read的初始值。
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read' 

-- 3.执行语句
select city,name,age from t where city="杭州" order by name limit 1000;

-- 4.查看OPTIMIZE_TRACE 输出
select * from `information_schema`.`OPTIMIZER_TRACE`\G;

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

-- 6.计算 Innodb_rows_read 差值
select @b-@a;

从number_of_tmp_files(排序过程中使用的临时文件数) 判断是使用内存排序还是外部排序：

  • number_of_tmp_files>0，使用了临时文件数量，内存放不下，就会需要使用外部排序，外部排序一般使用归并排序算法，
  • number_of_tmp_files=0，表示排序可以直接在内存中完成。

image.png

rowid排序

上面那个查询返回的字段较少(city,name,age)，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中进行。

如果查询返回的字段太多，内存能同时放下的行会很少，需要分成多个临时文件，排序性能会很差。

如果MySQL认为排序的单行长度太大会怎么做？

max_length_for_sort_data

  • 是MySQL中专门控制用于排序的行数据的长度的一个参数。
  • 如果单行的长度超过这个值，MySQL认为单行太大，要换一个算法。

原来city,name,age这三个字段的总长度为36，如果将max_length_for_sort_data设置为16，带来的改变：

  • 新算法放入sort_buffer的字段，只有要排序的列(name)和主键id。

新的执行流程如下（称为rowid排序）：

  1. 初始化sort_buffer，确定放入2个字段，即name和id
  2. 从索引city找到第一个满足city="杭州"的条件的主键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三个字段返回给客户端。 image.png

rowid排序和全字段排序的重要区别：

  • rowid排序比全字段排序多访问了一次表t的主键索引，即上面的步骤7.

全字段排序 VS rowid排序

  • 如果担心MySQL排序内存太小，影响排序效率，才会使用rowid排序，这样一次可以排更多行，但是需要多一次回表取数。
  • 如果MySQL排序内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样可以直接从内存中返回查询结果，无需再做一次回表取数。

原则：如果内存够，就多利用内存，尽量减少磁盘访问。

加了联合索引(city,name)后的效果

加了(city,name)联合索引，可以保证city这个索引取出来的树，就是天然按照name递增排序。

image.png

基于(city,name)联合索引的查询过程：

  1. 从索引(city,name)找到第一个满足city="杭州"条件的主键ID。
  2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的第一部分直接返回。
  3. 从索引(city、age)取下一个记录的主键id。
  4. 重复步骤2、3，直到查到第1000行记录，或者是city!="杭州"条件时循环结束。

image.png 结论：这个查询过程不需要临时表，也不需要排序。用explain验证如下：

  • Extra字段中没有Using filesort了，即不需要排序。
  • 由于(city,name)本身就有序，这个查询无需把4000行数据全部读一遍，只要找到满足条件的前1000条记录即可。 image.png

进一步优化：加了联合索引(city,name,age)后的效果

因为查询的结果只需要这3个字段，即覆盖索引，索引上的信息满足查询请求，不需要再回到主键索引上去取数据了。

查询流程如下：

  1. 从联合索引(city,name,age)找到第一个满足city="杭州"条件的记录，取出其中的city、name、age这三个字段的值，作为结果集的一部分直接返回。
  2. 从联合索引(city,name,age)继续取下一个满足该条件的记录，重复当前步骤，直到第1000条记录，或者是city!="杭州"条件时循环结束。

image.png

查看explain里的执行计划

image.png Extra字段里多了"Using index"表示使用了覆盖索引，性能上会快很多。

avatar
文章
阅读
粉丝