菜鸟笔记
提升您的技术认知

orderby是如何工作的?-ag真人游戏

假设我们现在有一个市民表定义如下:

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;

假设我们现在要查询城市是“杭州”的所有人的名字,并且排名按照姓名排序,返回前1000个人的姓名、年龄。那我们会写出如下的sql语句:

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

下面我们来分析下这条语句的执行流程。

为了避免全表扫描,所以我们在city字段加上了索引。索引的示意图如下:

我们使用explain命令来看语句的执行情况:

我们在extra字段里面看到using filesort表示需要进行排序 ,mysql会给每个线程分配一块内存用于排序,称为sort_buffer

正常情况下,这个语句流程如下所示:

  1. 初始化sort_buffer,确定放入name,city,age三个字段
  2. 从索引city上找到第一个满足city="杭州"的叶子节点,然后回到主键id索引上取出name,city,age三个字段的值,存入sort_buffer中
  3. 不断重复上述过程,直到不满足查询条件为止
  4. 对sort_buffer中的数据按照字段name进行快速排序
  5. 取前1000行返回给客户端

这个排序过程,我们称为全字段排序。过程如下图:

对于字段的排序,可能在内存中直接完成,也可能使用外部排序,这取决于所需的内存和sort_buffer_size。sort_buffer_size是mysql为排序开辟的内存。如果排序需要的内存小于sort_buffer_size,则直接在内存中排序。如果大于sort_buffer_size,则需要借助外部排序。

在这个算法里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和磁盘文件中进行的。但是这个算法有个问题,如果这个查询要返回很多字段的话,那可能sort_buffer就放不下,就需要借用很多的磁盘文件进行外部排序。

那么,mysql对于长度过长的行会如何进行处理?
我们可以通过设置max_length_for_sort_data来让mysql换一个算法,如果长度超过max_length_for_sort_data就会换一个算法:

set max_length_for_sort_data = 16;

新的算法中,只会将要排序的字段(name)和主键id放入sort_buffer中。流程会变成如下的样子:

  1. 初始化sort_buffer,确定放入name,id两个字段
  2. 从索引city上找到第一个满足city=“杭州”条件的主键id,到主键索引id上取出name和id两个字段,存入sort_buffer中;
  3. 不断重复上述过程,直到不满足查询条件为止
  4. 对sort_buffer中的数据按照字段name进行排序
  5. 遍历排序结果,取得前1000行,然后回到主键索引id中取出city、name和age三个字段返回给客户端

这种排序方式我们称为rowid排序,可以看到这种方式多访问了一遍主键索引id,但是可以减少排序所需要的内存。过程如下图所示:

mysql的设计思想是:如果内存够,就要多利用内存,尽量减少磁盘访问。也就是如果mysql实在担心内存太小,会影响到效率,才会使用rowid排序算法,因为全字段排序可以减少一次全表查询。

但是其实实际上也不是所有的order by语句都会进行排序操作的。mysql之所以进行排序,是因为在city索引上,name是无序的。

那如果建立city和name的联合索引:

alter table t add index city_user(city , name);

这就保证了在city相同的情况下,那么一定是有序的,就不需要进行排序了。查询流程就变成了下面的流程:

  1. 从索引(city , name)上找到第一个是city='杭州’的主键id,回到主键索引id上取出name,city,age字段,作为结果集的一部分
  2. 重复以上过程,直到不满足查询条件或到达1000条记录后直接返回结果集

流程如下图所示:

我们也可以通过explain语句来观察,发现using filesort没有了。

如果我们再结合之前覆盖索引的知识,建立(city,name,age)联合索引:

alter table t add index city_name_age(city,name,age)

这样name字段是有序的,而且需要的字段也都有,都不需要回到主键索引中去查询。流程就变成了:

  1. 从(city , name,age)索引上找到第一个满足city=“杭州”的记录,并取出city,name,age添加到结果集中
  2. 重复以上过程,直到不满足查询条件或到达1000条记录后直接返回结果集

流程如下图所示:

网站地图