MySQL 笔记整理(16) --“order by”是怎么工作的?

笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》

(本篇内图片均来自丁奇老师的讲解,如有侵权,请联系我删除)

16) --“order by”是怎么工作的?

  在林老师的课程中,第15讲是前面问题的答疑,我打算最后将答疑问题统一整理出来,所以就继续这些内容的笔记了。

全字段排序:

假设有一个表是这么设计的:

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;

  如果我们使用explain命令来看这个语句的情况会发现,extra这个字段中的"Using filesort"表示的就是需要排序,MySQL会给每个线程分配一块内存用于排序,称为sort buffer.我们知道,非主键索引其实存储的是对应的主键id。因此上述语句的执行流程大致如下:

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

  我们暂且把这个排序过程,称为全字段排序,执行流程的示意图如下:

  

  其中的“按name排序”这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size。这个参数的含义是MySQL为排序开辟的内存(sort buffer)大小。如果要排序的数据量小于sort_buffer_size,排序就在内存中完成。但如果排序量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。而关于外部排序,需要知道的是:外部排序一般使用归并算法进行排序。可以这么简单理解,MySQL将需要排序的数据分成N份,每一份单独排序后存在这些临时文件中。然后把这N个有序文件再合并成一个有序的大文件。如果sort_buffer_size越小,需要分成的份数就越多,number_of_tmp_files的值就越大。

RowId排序:

  上面这个过程中,排序操作是在sort_buffer和临时文件中执行的。但这个算法有一个问题,就是如果查询要返回的字段很多,那么sort_buffer里面要放的字段就会很多,这样能放得下的行数就会很少,要分成很多个临时文件,排序的性能会很差。那么,如果MySQL认为排序的单行长度太大会怎么做呢?

  我们先来修改一个参数的值。SET max_length_for_sort_data=16.这个参数是MySQL中专门用于控制排序的行数据的长度的一个参数。它的意思是,如果单行的长度超过这个值,MySQL就认为单行太大,要换一个排序算法。上述建表中可以看到,city,name,age的总长度为36,大于我们刚刚设置的16,我们再来看看计算过程有什么变化。

  新的方式中要放入sort_buffer的字段由原来的name,city,age变为了要排序的列(name)和主键Id.很明显,这样排序的结果是不包含city和age字段的,因此不能直接返回。所以整个流程就变为:

  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三个字段返回给客户端。

全字段排序 VS rowid排序:

  如果MySQL担心排序内存太小,会影响排序效率,才会采用rowid排序算法,这样排序过程中一次可以排序更多行,但是需要回到原表去取数据。

  如果MySQL认为内存足够大,会优先选择全字段排序,把需要的字段都放到sort_buffer中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。这也体现了MySQL的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。对于InnoDB表来说,rowid排序会要求会标多造成磁盘读,因此不会被优先选择。排序是一个成本比较高的操作,其实,并不是所有的order by语句,都需要排序操作的。从上面分析的执行过程,我们可以看到,MySQL之所以需要临时表来进行排序,原因是原来的数据都是无序的。(好像有点废话~~)。如果能保证从city这个索引上取出来的行,天然就是按照name递增来排序的,就可以不用排序了。

  因此,我们可以在这个表上创建一个city和name的联合索引,对应的sql语句是:

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

  这个索引除了满足city=‘杭州’的数据会聚集在一起,还额外满足了一个条件。那就是如果一组city='杭州'的记录,那么就可以保证这些记录都是按name继续进行排序的。所以语句执行流程就变为:

  1. 从索引city_user找到第一个满足条件city=‘杭州’的主键id
  2. 到主键id索引去除整行,取name,city,age三个字段的值,作为结果集的一部分直接返回。
  3. 从索引city_user取下一个记录
  4. 重复步骤2,3直到查到第1000条记录或是不满足city=‘杭州’为止。

  额外补充一点,假设city=‘杭州’的数据有4000条,当你用explain命令去查看上面几次的排序语句时会发现,除city_user这个索引外,其他的扫描行数都为4000。因为要获取全部的city=‘杭州’之后才能按照name排序取前一行。而如果使用了city_user索引,则只需要扫描1000行就能返回结果。

  那么,还有没有可能再快一点呢?答案是肯定的。我们之前有介绍过“覆盖索引”.如果你要查询的字段完全包含在覆盖索引之内,就不需要再返回主键id索引中取出全部的数据来了。即减少了回表,具体流程就不写出来了。

上期问题:

  在上期关于count计数的讨论中,我们用了事务来确保数据的精确性。由于事务可以保证中间结果不被别的事务读到,因此修改计数值和插入新的记录的顺序是不影响逻辑结果的。但是,从并发系统性能的角度考虑,你觉得在这个事务序列里,应该先插入操作记录,还是先更新计数表呢?

  关于上期的内容,具体可以点击这里查看上一篇

  用一个计数表记录一个业务表的总行数,在往业务表插入数据的时候,需要给计数值加1.逻辑上实现是启动一个事务,执行两个语句:

  1. insert into 数据表。
  2. update 计数表,计数值加1.

  应该怎么安排这两个语句的顺序呢?并发系统性能的角度考虑,应该先插入操作记录,再更新计数表。因为更新计数表涉及到行锁的竞争,先插入再更新能最大程度地减少事务之间的锁等待,提升并发度。

  关于每篇最后这个问题,我打算更换一下格式,以后整理几篇文章之后再单独出一篇问题的总结,也方便阅读。因此这篇就先不留下问题了。

posted @ 2019-04-04 10:32  DogTwo  阅读(256)  评论(0编辑  收藏  举报