数据库系统期末复习II: 查询算法实现 | 聚合 | 排序 | 连接

前情提要，上回 数据库系统期末复习I：数据库存储与索引技术_我说我谁呢 --CSDN博客 复习完了DBMS存储模型的内模式的实现，包括怎么存储 records，不同的索引实现的数据结构算法以及不同的适用情况。本文聚焦数据库系统中的查询算法。由于 point query 太简单，在上一篇里面就讲完了，这里主要讲解的是 join，aggregation，order by 的实现算法。

位置（数据库系统基本构成）

• Disk Manager：最底层是负责和 OS 打交道的 Disk Manager，但是尽量希望 bypass OS，然后自己管理磁盘访问。所以实际 Disk Manager 是负责抽象出 OS 的 I/O 接口的，具体到 bustub 里面，Disk Manager 做的事情就是简单的打开 db 文件和 log 文件。这里做的是单文件。注意的是我们上层管理读写的时候都是以 page 为单位的，这是因为驱动能够保证每次对 Page 的读写都是 atomic 的（理论是这样，上一篇笔记分析了 HDD  和 SSD 的 Page 访问逻辑）。
• Buffer Pool Manager： 由于 Disk Manager 抽象了数据库文件在 OS 上的存在形式（单文件或者多文件，但是上层不在乎，这些应当由下层屏蔽掉），基于他构建一个以页为最小读写单位的缓存管理。使得所有的页面访问都在内存进行读写，这一点和 OS 的空闲内存用来做文件缓存是一样的的。做的工作也和 OS 的一样（或者和 CPU 的 Cache 的管理器也差不多了，不过是软件层面的）。这里主要就是页面池塘加上 Replacement 算法。值得注意的是 replacement 算法和 buffer pool 都会由多种方式使用，包括针对不同的数据采用不同的 replacement 算法，针对不同的页面访问采用分开的 buffer pool（多个 buffer pool 使用不同 replacement policy），以及对于临时数据 bypass buffer 都要做。
• Access Methods：访问层是我们对于 Tuple，Column，Table 的访问，他们最终转换为对 Page 的读写，这一层要负责吧之前分析的存储模式（内模式）和索引等都给实现了，从而把对表和 tuple 的查询能转换为对 pageid 和  tuple id 的访问。具体到 bustub 里面，这一层包括了 table 和 tuple 的数据结构，page（包括元数据的，index 的，table 的），扩大化一下，这里就还要包括数据层，因为 DBMS 里面存储的数据必须都是指定类型的，不会说像动态类型语言里面变来变去的。比如 uint，int，varchar，char8，char16 等等这些类型。
• Operator Execution： 这一层是数据库引擎的部分，他进行 query 的每个独立的查询，需要实现所有的关系代数 operator 类似的东西。包括 iterator pattern，递归查询，迭代查询，循环等等都在这里实现。
• Query Planning：这里就是基于下层提供的 Operator，根据用户输入的 SQL，进行 operator 树的生成，简化，关系代数/演算优化，进行 cost modeling 和比较，最后选用一个 plan 进行查询，然后传给下层。

查询由很多功能组成，包括排序（Order by），Partition （Group by），去重（DISTINCT），Hashing （Join）。

等值查询和范围查询这个是可以直接基于 B+ Tree 去做的（首先通过 lowerbound 的 FindLeafPage 找到 key 可能（注意这里查询是没有进入叶子里面查的）所在的那个叶子节点，然后叶子内部通过二分法 lowerbound 找到第一个 key 相等的，然后就能返回 tuple id 了，range 的话再找一个右边界进顺着链表过去全部上传上去就行了）。所以下面我们主要讲解 merge sort 和 join 用到的 hashing 算法。

对于 disk-oriented dbms，必须采用分布式的算法，优化方向在 disk IO，即所有算法都要考虑 IO 瓶颈（时间复杂度以 IO 次数衡量！），而不是内存上访问的瓶颈。总的来说就是 divide and conquer。算法设计与分析课程里面学到，所有的问题你找解决方案，无非首先看规模，如果暴力 is acceptable（对于笛卡尔积，nested loop），那就结束了。如果不行，先尝试剪枝（query planing 的时候用到），根据必要条件缩小范围。还是不行，那么上 divide and conquer，要么分治（merge sort）， greedy 要么 dynamic programing。再到优化：空间换时间（反范式冗余，covering index），提前打表查表法（zone map），单线程转多线程（IO parallelism）。逻辑和确定性方法做不了的，上随机化算法（随机快排，跳表，布隆过滤器，快速选择，蒙特卡洛方法，蓄水池采样）。算法的重要思路在各个领域都差不多的。

Sorting

• 归并排序：基本策略是两路 merge sort 。由于一次只能用到最多三个 page，然后排完这两个页之后又要载入新的两个页然后进行下一轮。不能利用所有的 page。可以 double buffering 和 prefetch，io parallelism。比如一开始分配 6 个页面，然后 4 个两两一组，提前载入四个 page，然后在一个线程做排序，等排完两页之后，马上就可以排另一组。此时开一个 IO 线程去载入新的两个页面。
• 多路归并：多路归并的算法分析，首先第一轮是 N 个页面 B 个 buffer，每次最多只能 B-1 路归并（留一个页面做输出窗口）。结果是得到 N/B 个页面作为结果，之后再在这些页面上进行 B-1 多路归并。总的复杂度还是算法课程里面分析的 nlgn 。不过实际是有优化的，因为每个页面内的排序我们不会递归/迭代进行归并排序，而是使用随机化内省快速排序（毕竟已经在内存里了，而且我们没有额外的空间来做这个内部排序），如果是多关键字排序，也是用快排，你不要想着用 stable_sort 来排几次，这种事情是没必要的，我们直接重载一下 comparator 就行了。
• 索引排序：由于 B+ 树本身是排序好的，如果是 clustering 的，直接就能输出结果，如果不是 clustering 的，还是使用归并排序（因为这样访问leaf node 需要 random io，涉及 page 载入载出，而且也还要根据 tuple id 去组装重新排列内模式）。
• Order By 的实现：如果是一个查询的话，数据量不大，本身可以内存排序，那就直接采用快速排序。在 MySQL 里面会有一个 sort_buffer_size（比如 256 KB） 来决定排序缓冲区的大小。理论上这个大小由可用的内存决定，比如你所有的数据都可以放下，那当然可以直接排序结果。根据这个也能理解一个点是实际排序我们上面说的页面本身也不一定是 4KB 的。对于我们之前提到的组合覆盖索引（covering index），order by 不会走排序。
• InnoDB 的内模式：前面说了索引有 clustered index，对于这个来说，没有必要再 leaf 里面在搞一个 tuple id 然后间接访问，我们直接让 leaf node 就是数据的 page 就行了，反正不过是添加了一些关于 B+Tree 的 meta 字段而已。InnoDB 的存储就是基于这个来做的，他所有的表都存在 B+ Tree 里面（leaf node 中的数据为整个 row），所以他是覆盖+聚簇。如果有 Primary Key，如果有第一个 not NULL UNIQUE attribute，如果都没有，就创建一个自增 row-id 做聚簇索引。而 secondary index 的叶子节点存的是 Primary Key/第一个 not NULL UNIQUE attribute/row id 而不是直接的 tuple id。（我们前面一篇的分析其实也明白了你要唯一定位一个 tuple 位置其实是不简单的，肯定得有什么，InnoDB 直接用 clustered index 的 key 做唯一标识符），结果就是这样回查两次不同的 B+Tree 才能得到结果。这种查询叫做回表查询。（找不到他的英语描述了属于）为了避免回表查询，最简单的方法就是做一个 covering index，空间冗余换时间，缺点是更新表数据的时候要更新索引，对，他是增量更新，不可能 rebuild 整个 index 的。
• 为什么 stl sort 不会退化（内省随机快排）：这个我之前算法设计与分析报告写了一篇，这里数据库就不重复了。删掉之前这里的分析。

Aggregation

• zone map: 首先一种方法是维护一个 zone map，precompute 好了直接返回数据。zone map 的算法实现无非就那些，什么最大最小栈（如果有 cluster b+ tree index 更方便），主要是增量算法的量更容易保留。
• group by: 最简单的想法不就是你直接建一个 hashmap，然后每次 aggragate 数据进去 value 里不就行了。但是不可行，考虑我实际 group by 的那个 attribute 有几百万个呢？甚至说我就 aggregate group by user id 来统计用户的信息，你一个 hashtable 放得下吗？肯定是不能的。所以要做两阶段 hashing。首先用用hashing 做一次 partition，基本方案是用 hash 决定放到哪个 bucket（partition），这样保证了 partition 里面的基本都是同一个 key 的。然后再 in memory hash table 对每个分区进行 rehash，hash 到同一个位置就进行计算 aggregation，这样保证了什么呢？保证了每一个 first phase bucket 里面的东西肯定回 hash 到同一个地方，这样就不会出现超大 hashtable 了，我总是能保证我这一次做 aggregation 的东西都基本是几个不同的 key 而已。
• 
• aggregation 过程也可以 bypass buffer pool 因为可能都是些临时数据。
• Partition：前一篇笔记提到，MySQL 有 Partition 技术从而基本不用 bitmap（which is adopted by Oracle） 来解决冗余问题，使用方法是提供某个 Partition 标准，比如前面说的 Linear Hashing MySQL :: MySQL 5.7 Reference Manual :: 22.2.4.1 LINEAR HASH Partitioning。如果提前库表就是 partition ，那 aggregation 会轻松很多。

Join

• Query Operator 执行顺序：先 project 还是先 join 会影响 io 和数据传送性能。query optimization。
• nested-loop ：他总是最bottom的方案，比如所有 key 都一样，必须笛卡尔积，最终 fall back 成这个。但是这个不能最最普通的方案，因为 cache missed reload 浪费，一个是多 page 内切换，一个是 match 的另一个关系像 csapp 说的切换。always pick the smallest table as the outer table（因为每个外层循环都要 evict 和 reload 内层循环次，不过节约最多 10%左右，不是重点）。复杂度为：外层页数 + 外层 tuple 数 * 内层页数
• block nested loop：第一种是，我们笛卡尔积所有的 page pair, 即一个循环 anchor 是 page，一个循环 anchor 是 tuple，三层循环于是。这样至少能至少保证内循环的 page 不会 evict 和 reload，再 nested loop。实际不只两个 buffer 的时候加速，如果某个关系能 fit in memory 就是线性的，能在百毫秒内完成。
• index nested loop join: 用 index 来 join 很快（因为 index 等于直接一个表不是循环 n 而是 logn）。但是 on the fly 新建一个 index 很慢，要看 trade off，以及是 one time 还是 durable 的优化。
• Sort merge：先排序再双指针 join 也能用，但是涉及backtrack 退化 N。cluster index 利用时加速。如果有 cluster index 就用 sort merge。backtrack 的问题无法解决，所以适合 unique clustered key。也许没人会用这个。
• Hash join: 一个关系 hash 到 buckets 里（build phase）然后另一个线性 probe bucket 里面（probe phase）的，on the fly 生成结果。如果是 row based 就 hash 过程存整个 tuple，防止后续重复读写更多 io，column base 就存 identifier 就行了。
• sideways information passing：有时候你 join 的两个表的那个关键字可能是不对称的，一个表有的东西另一个表没有。这样 hash join 的时候，用一个布隆过滤器标识已经有的 key，linear probe 的时候直接测一下 bloom filter 可以马上知道是不是一定不存在，这样就不用 linear probe 了。因为hash table 是涉及 page 的（要 load 进来，可能他是一个空的 bucket），而且你不进去看也不知道有没有。linear probe 慢是因为你 join 的时候对于重复的 key，是要做笛卡尔积的，所以 bucket 里面不仅会有相同的 key，还会有一些哈希冲突的。sideway 的意思是打破分层思想，比如 db 可以用 redis 做缓存就等于暴露了内模式给 redis。布隆过滤器我自己写了一次（简化版，没有根据论文用那个简单的哈希函数簇），具体来说就是做好多个 hash 函数，我的写法是用不同的 seed，然后做一个大大的 bitset，然后就是妥协版海明码了。
• Grace hash join：因为 hashtable 不 fit-in mem 就很多 evict（每 probe 一个就要 evict）， grace hash join 是用 nested + hash，hash 只用来 partition，对于两个 fit in memory 的 partition 就用 nested loop 来 match 。保证每个 page 只 load 和 evict 一次不会有 evict & reload 的浪费。但是常数更多 3M+3N. 递归 hash 最差情况很差。（这里 grace hash 的 hash 函数肯定是某种冲突大的）
• Realworld：
  • 如果有免费的 clustered index，就用 sort merge。
  • 如果没有 index 就用 hash join。
  • 如果有 index，用 index nested loop（因为 hash 涉及 bucket evict+reload）。
  • 如果求笛卡尔积，直接用 nested loop。
  • 如果是 range join（OLAP 大数据经常用，比如 on a.sc btw b.start and b.end），用 B+Tree index nested loop。

---

下面内容还没有重新复习，仍然是上次期中时的简略笔记，这里暂时留着，之后继续整理。

Query Execution

• iteratior model 适合流水线，通过独立的 thread 来运行这些内容。涉及 coordinate 等情况。有点麻烦。但是省空间。materialization model 则是 batch processing，适合 OLTP 少量查询快速不用 inter function communate（各种函数调用等于一个生产者消费者问题）批处理不用。但是如果 IO 量大了还是要 fall back 到 iterator model。迭代器支持 limit 不用访问全部。
• double buffer 技术，由于巨量 itemediate 结果  data copy 和 transmitting 会浪费 buffer pool 和引发 io瓶颈，多线程 IO 。或者 buffer pool bypass。
• vectorlized model 基本就是段页式的 compromise 而已。让函数做更多事情，利用上 Cache。所有的 OLAT 都用这个方法。对于一些操作，也可以利用 CPU SIMD。
• 需要编写 DP 版的 bottom up 的 pipeline 而不是使用递归函数调用。自从递归调用会频繁引发 evict 和 reload。
• 流水线 fallback 的情况是等待，pipeline breaker 必须 block。所有的 aggregate 和 sort 都是 breaker，join 也是。
• 必须 sequential scan 的情况，比如没有 index 进行 range query，或者有 index 也 range query。
• zone map 记录 aggregate function，有了 min max 可以快速排除 out of range query。但是 zone map 只利好 OLAT 对 OLTP 来说很难维护，比如最大最小栈必须消耗很大的空间。（side-info）
• Late materilization 优化，就是 materialization 的时候只记录 tuple record id，然后 bitmap。对于一些操作很有用，比如 where 多个 clause ，and 或者 or 直接多线程 run inter query 的 intra operator 之后对 bitmap 操作即可。
• index scan 如何选择 index，对于大量可能 99% 都符合某个条件，index fall back 为 seq scan。即区分 selective scenario 和 index 不是很 selective 的情况。
• multiple-index 的使用，还是 bitmap。
• 对于 index 不是 clustered 的，还要写磁盘调度器，对于收集到的 record id，我们 bitmap 之后 sort ，或者直接 bitmap 就支持分区 scan 了。
• 默认使用一个隐含的自增id做 cluster index。最好还是指定常用的 primary key。
• 解析语法树，对每个 tuple 运行表达式树 is super slow。有 sql prepared statement 一次编译多次运行，可以 JIT。当然还有 const optimization。（mysql 现在版本没有 JIT）

Parallelism

• 得益于 dbms 的 logical layer 和 physical 是独立的。
• 能够提高 throughput 和 latency 性能。
• 利用 CPU SIMD，还有 GPU 提供更好的并行（并没有）。
• 商业决策 TCO total cost of ownership，买车并不止一个买车钱。
• cpu bound 问题做 computing parallelize，IO bound 做 IO parallelism。
• parallelism vs distributed，一个是很多 local CPU 资源，reliable 的 IPC。一个是很多远程的慢速不可靠 IPC，尽量减少 IPC。CAP consistency, avaliablity, partition tolerance.
• DBMS 对 queries  的 process model 分为 intra 和 inter，然后 inter 也分为 inter operator 和 intra operator。inter 的 coordinate 问题很多，涉及 transaction ？很麻烦。Lec 16 才讲这个。知道 inter 提高 throughput，intra 提高 long query 的 latency 性能就行了。
• postgres 输了因为他用了 process 模型，but when we view back to the late 1990s，POSIX 都没有，也没有 pthread。虽然 thread 模型 fail 全部就 down 了，但是并不影响主流的新 dbms 哦都用 thread model。Linux 的 thread 还能控制哪个 core，实际控制度很大。
• 用 dispatcher 来分发，为了 optimal 利用 cores，我们不能总是 fork （或者 create thread）而是采用一个 worker pool 控制有限的独立 worker。
• pipeline 和 producer、consumer problem。
• sqlserver 的 exchange operator 概念，他基本是一个管理所有 sub query（intra operator），他做三件事，要么 garther 然后传到上一层要么继续 repartition （recursion 的）比如上层可能做 group by，他这里 group 一下继续分发（distribute）了。
• inter operator 就是 pipeline 了，我们要保证 pipeline 饱和才行，需要 coordinate。
• bushy 是很多的 inter 然后交叉 exchange ，这个有点看不懂。
• IO 可以用 raid 冗余独立（廉价）磁盘阵列，这个在 install 的时候 user define 也行。
• 通过 symlink 实现不同硬盘（实际就是快捷方式），又要用到 OS 的 IO 的，不过直接 no buffer 用 O_DIRECT 就行了。