mysql是如何进行order排序的

不会说话的刘同学
133 阅读7分钟

mysql排序是我们在一般在项目中会经常会用到的，mysql排序就是直接用 order by 进行，但我们执行order by 之后，mysql是如何进行排序的呢，本篇文章就将以一个案列来进行讲解

现在有一个员工表 employees，并且往员工表里插入了五条数据，建表语句如下

CREATE TABLE `employees`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT 0 COMMENT '年龄',
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '入职时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_position`(`name`, `age`, `position`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 100007 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工记录表' ROW_FORMAT = Dynamic;

INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (1, 'zhangsan1', 22, 'manager', '2021-06-05 13:40:23');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (2, 'zhangsan2', 23, 'dev', '2021-06-05 13:40:23');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (3, 'zhangsan3', 23, 'dev', '2021-06-05 13:40:23');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (4, 'zhangsan4', 1, 'dev', '2021-06-08 22:34:04');
INSERT INTO `employees`(`id`, `name`, `age`, `position`, `hire_time`) VALUES (5, 'zhangsan5', 2, 'dev', '2021-06-08 22:34:04');

索引结构

这个表里有个组合索引 idx_name_age_position ，还有一个主键 id (关于主键id，我们可以称为聚簇索引)，对于索引结构的选着，我们使用了B+树，因此mysql会有两颗B+树来组织这个表里的数据，分别对应主键 id 和 idx_name_age_position，具体的索引树结构如图所示

image.png

image.png

当我们有一个查询语句 select * from employees where name='zhangsan1' 的时候，会有以下步骤：

1、通过 idx_name_age_position 索引树查找到 name 为 zhangsan1 的叶子节点

2、然后获取到叶子节点上的主键id

3、再根据主键id去主键id索引树上获取整行数据

到了这里我想你应该知道mysql是如何根据索引进行查找数据的吧

说完了等值查询数据，那么再来看看如果要讲数据进行排序的话，那是怎么运行的呢

order排序的两种方式

mysql在排序主要有两种方式----内存排序和磁盘辅助排序

内存排序就是mysql会将查找出来的数据先放到内存中(这块排序的内存我们称之为sort buffer)，然后再进行排序，排序的速度非常的快，一般在查询出的数据量比较小的情况下都会使用到内存排序

我们假设有个语句 select * from employees where name = 'zhangsan1' order by position，我们来看看执行步骤：

1、首先会从idx_name_age_position索引树上找出 name 为 zhangsan1 的数据，并将全部的字段信息放入到 sort buffer 中

2、继续在索引树上查找直到条件不满足为止

3、对sort buffer里的数据按照 position 进行排序

4、将结果返回给客户端

对应的图如下所示

image.png

在上面的sql语句中 ，mysql会将所有的返回给客户端的字段放到 sort buffer中，我们暂时将这种称之为全字段排序

全字段排序虽然能够从直接从内存中将字段返回给客户端，但是还有个得不偿失的问题，那就是非常的占用sort buffer空间

表里的每个字段存放到sort buffer中都会占用一定的空间，而mysql对于sort buffer初始化默认的大小是 256KB，如果要排序的数据大小大于这个数值的话，那么mysql就不得不使用磁盘临时文件来辅助排序了，这种排序是非常的影响性能的

为了降低磁盘排序的使用率，提高sort buffer的利用率，mysql对于排序使用了另外的一种算法，那就是在sort buffer中不直接放返回给客户端的全部字段，而是只放将主键id和排序字段，在内存中排完序后，在根据主键id从索引树上找到整行数据并返回给客户端，这种我们称之为rowid排序

上面的sql语句使用rowid排序执行流程图如下

image.png

在rowid排序的过程中过了一次回表的操作，查询出的数据不会直接消耗内存，而是直接将数据返回给客户端

那么在全字段排序和rowid排序，mysql改怎样去选择呢

全字段排序会将返回给客户端的所有字段放到sort buffer中，非常的消耗内存，如果数据量过大就会使用到磁盘排序，非常的消耗性能；rowid排序虽然减少了内存消耗，但是在排序的过程中会多一次回表操作

mysql在sort buffer比较大的情况下会优先选用全字段排序，把全部的字段存放到内存中就可以直接返回给客户端，就不用再回到原表去取数据；sort buffer比较小的情况下，mysql会担心使用到磁盘排序从而影响到效率，就会选用rowid排序

说到这里，难道所有的 order by 操作都需要 mysql 再内存中进行排序吗，其实并不是

我们来想一下，为什么 order by 操作会将数据先放到内存中再排序呢，原因就在于我们查询的数据是无序的，假如在上面的sql中，我们查询的数据取出的行，天然就是按照排序字段排好序的，是不是就可以不用进行再在内存中排序了呢

在mysql中确实有这种情况

我们在employees表里建了一个 idx_name_age_position 组合索引，这个组合索引在索引树上就是按照索引字段的顺序来进行排列的，如果我们使用sql语句 select * from employees where name = 'zhangsan1' order by age ,那么在索引树上查找到 name = 'zhangsan1' 的数据，结果一定就是按照 age 排好序的

对于这条sql语句，我们可以来看下执行步骤：

1、从 idx_name_age_position 索引树上找到满足 name = 'zhangsan1' 的条件的主键id

2、在从主键id索引树上找到整行数据，作为结果集的一部分

3、从索引树上继续取下一个满足条件的值，继续添加到结果集中，直到找到不满足条件的数据，查找结束，将结果集返回

对应的执行流程图如下所示

image.png

说完了内存排序，我们再来看看磁盘辅助排序，磁盘辅助排序是当查询的数据大小大于sort buffer大小时，mysql才会直接走磁盘排序，会用到归并排序算法，所谓归并算法就是 mysql 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件,sort_buffer_size 越小，需要分成的份数越多，需要的临时文件就越大，因此在排序的过程中我们应当尽量减少磁盘排序

如何优化排序

1、我们在使用order by排序的时候应尽量减少磁盘辅助排序，在查询的过程中可以按需取字段，这样可以减少字段对内存的占用，在必要的时候可以适当的调大sort buffer

2、在使用组合索引的时候尽量遵循最左匹配原则，这样可以减少在内存中排序的步骤，提高效率

3、尽量的使用覆盖索引，这样可以避免回表操作

avatar
文章
阅读
粉丝