MySQL-16:order by如何运作

order by:根据提供的字段进行排序;用法:order by column[ASC(默认) | DESC]。

limit: 从结果集上再进行刷选;用法:limit offset ,rows。

如下语句建表:给city建索引

CREATE table a(
    id int(11) not null,
		name varchar(16) not null,
		age VARCHAR(16) not null,
		city VARCHAR(16) not null,
		PRIMARY key(id),
		key city (city)
)ENGINE = INNODB

当我们查询city = “南京”时的sql语句。

SELECT name,age,city from a WHERE city="南京" ORDER BY age limit 10

我们用explain命令看下语句,发现它使用的是city索引,而且Extra是Using filesort,表示是需要排序的。

MySQL会给每个线程分配一块内存用于排序,叫做sort_buffer

在这里插入图片描述

16.1 全字段排序

sql语句的执行流程:

  1. 初始化sort_buffer,确定name,age,city三个字段
  2. 去city索引中找到第一个满足city="南京"的数据,得到id
  3. 带着上面的id去id主键索引树中寻找对应的数据行,取出name,age,city字段值存入sort_buffer
  4. 从city索引去下一个满足条件的id
  5. 重复3,4步,直到从city索引中取不到满足条件的id
  6. 对sort_buffer中的数据根据age进行排序

流程示意图如下:

在这里插入图片描述

sort_buffer_size:

上面流程的第五步,根据age将数据进行排序,可能是在内存中进行,也可能借助了临时文件。这取决于参数sort_buffer_size的大小与所需要的内存大小。如果排序数据量过大,无法在内存中完成排序,就必需要借助于磁盘临时文件辅助排序。排序磁盘临时文件的算法是归并算法,归并算法的思想是分而治之。

16.2 row id 排序

MySQL中有个参数max_length_for_sort_data表示用于排序的当行长度,如果我们排序的字段有a,b,c…h这么多,内存中存下数据有限,必定会借用更多的磁盘临时表,性能不佳。

当出现单行的数据长度超过参数设定值,排序的列只会是 主键 id和 需要排序列。

sql语句的执行流程:

  1. 初始化sort_buffer,确定主键id,age列
  2. 从city索引中查找到第一个满足city = “南京”数据并取到id
  3. 带着主键id去id索引树找到对应的数据行,取出age,id两列放入sort_buffer中
  4. 去city索引树继续找到下一个满足条件的数据,取出id
  5. 重复3,4步,直到city索引树找不到满足条件的数据。
  6. 对sort_buffer中的数据根据age进行排序
  7. 遍历排序结果,根据id去id索引树中取到name,age,city返回给客户端

流程示意图:

在这里插入图片描述

16.3讨论及优化

  1. 使用临时表排序会造成较大的性能消耗,能用内存尽量用内存,减少磁盘访问。
  2. 利用索引覆盖规则,我们创建name,age,city可以减少回表的步骤,直接得到结果。
  3. 利用磁盘文件的目的是为了排序,那我们通过建立联合索引,在当初数据放置的时候就已经排序好,我们取出来可以直接用。

像这样做:

alter TABLE a add index city_age(city,age)

练习问题:

  1. order by 与 limit 的用法
  2. sort_buffer是啥?什么参数控制?
  3. 全字段排序执行流程?
  4. row id排序执行流程?
  5. 优化方案?
  6. MySQL的OPTIMIZER_TRACE 特性?
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页