MySQL千万数据调研,order by 原理详解

2022-11-09 00:00:00 数据 字段 算法 内存 排序

大家好,我是Leo。

之前聊的RocketMQ暂时放放,目前正在调研一个千万数据的处理方案。

在做数据库结构优化时,遇到了 order by 调优点的问题。苦思冥想!觉得不了解 order by 的原理,无法把这个细节把控好。于是就来了这一篇。

本章概括

order by 默认算法

首先看一下表的建表语句以及查询语句,这里SQL只是伪代码。

CREATE TABLE `waybill` (
`id` bigint(20) NOT NULL,
`shipping_number` varchar(26) COMMENT '运单号',
`order_create_time` datetime COMMENT '开单时间',
`status` tinyint(1) comment '运单状态',
PRIMARY KEY (`id`),
KEY `waybill_index_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运单表';


explain SELECT shipping_number,age,status FROM `waybill` where status = 4 order by order_create_time desc

根据伪代码和索引我们画一下 B+树的原型图。方便大家更好的理解

附带着贴一下 执行计划后的结果集,由 Using filesort 可以看出这是需要排序的,也证实了我们上述的 order by

根据索引结构图分析一下执行流程

  1. 初始化 sort_buffer ,放入shipping_number,order_create_time,status字段。
  2. 从索引status树上寻找满足status=4 的主键id,也就是图上的 1,2,3,.....4,5,6
  3. 拿到了主键id再去主键树上取出id为 1,2,3,.....4,5,6 的shipping_number,order_create_time,status三个字段的值,然后放入 sort_buffer 。
  4. 依次取出所有的id对应的值也就结束了。
  5. 后在sort_buffer 中对 order_create_time 字段进行快速排序,返回所有满足数据的数据

sort_buffer 是一个用于排序的buffer缓冲区

上述流程中的第五步,在 sort_buffer 中排序的规则主要 sort_buffer_size 决定的。我们可以通过下列代码查询对应的值。默认为256K。

这个是MySQL开辟给排序的内存大小,如果小于这个值就在内存中完成。如果大于这个值就在磁盘临时文件中辅助排序。

可以通过这个参数 number_of_tmp_files 来决定是否使用了临时文件排序,它表示的是排序过程中使用的临时文件数。

他会把分成的每一个文件,先在文件中排序,后再把所有文件再合并成一个有序的大文件。

当 sort_buffer_size 越小,需要分成的份数越多,number_of_tmp_files 的值就越大。

rowid算法

第二种排序算法,我们可以称为 rowid 排序。

默认的话还是采用上面那种算法,如果要采用 rowid 排序算法的话需要修改一个参数

SET max_length_for_sort_data = 16;

max_length_for_sort_data 是MySQL提供的一个判断采用哪种算法的参数。

如果单行的长度超过这个值,就认为太多,换一个rowid算法。否则就用默认算法。

由上述表的自定义我们可以得知,26+8+1=35。36大于16,所以会采用rowid算法

  • shipping_number 为 26
  • order_create_time为 8
  • status 为 1

rowid算法,执行流程 如下:

  1. 初始化 sort_buffer , 放入 order_create_time 和 id 这两个字段。
  2. 从索引树 status上找到满足条件的id,也就是 1,2,3,.....4,5,6
  3. 回到主键树上查找对应的order_create_time 和 id放入 sort_buffer 中
  4. 重复找到所有的id的对应的值,循环结束
  5. 对 sort_buffer 中的数据按照字段order_create_time 进行排序。
  6. 遍历排序结果取出所有满足条件的数据,并按照id回到原表中取出shipping_number,age,status字段返回给客户端

区别

默认算法与rowid的区别

  • 如果 MySQL 实在是担心排序内存太小,会影响排序效率,才会采用 rowid 排序算法,这样排序过程中一次可以排序更多行,但是需要再回到原表去取数据。
  • 如果 MySQL 认为内存足够大,会优先选择默认算法排序,把需要的字段都放到 sort_buffer 中,这样排序后就会直接从内存里面返回查询结果了,不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想:如果内存够,就要多利用内存,尽量减少磁盘访问。

优化

优化可以分多层优化。

  • 层优化就是给排序的字段加上索引,让他在索引树上查找数据时更快。
  • 第二层优化就是通过根据排序的数据大小,来决定采用哪种算法,第二层也就是内存与磁盘的抉择。
  • 第三层优化是从数据底层优化的。直接在插入数据的时候就按照列表的数据插入

第三层的方案主要有三种

  1. id自增(越来越大,也是有序的)
  2. 雪花算法生成id(根据时间戳生成的,自然也是有序的)
  3. 利用组合索引(索引的有序性)
  4. 字段少的话可以考虑覆盖索引

覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。

相关文章