读数据工程之道：设计和构建健壮的数据系统25查询

1. 查询

1.1. 通过理解查询、建模和转换​，你会掌握将原始数据转化为下游利益相关者可用数据的工具

1.2. 被很多人熟知的SQL，这是最流行和通用的查询语言

1.3. 查询是数据工程、数据科学和数据分析的基础

1.4. 在了解数据转换的基本模式和技术之前，你需要了解什么是查询、它如何在各种数据上工作，以及如何提高查询性能

1.5. 查询允许你检索和处理数据

1.6. 数据工程师使用的常见的SQL DDL表达式包括：CREATE，DROP和UPDATE

1.7. 数据控制语言(Data Control Language，DCL)允许你通过使用SQL命令[如授予(GRANT)、拒绝(DENY)和撤销(REVOKE)]来控制对数据库对象或数据的访问

1.8. 事务控制语言(Transaction Control Language，TCL)是支持控制事务细节的命令

• 1.8.1. 通过事务控制语言，我们可以定义检查点，以及操作回滚的条件

• 1.8.2. 两个常见的事务控制语言命令包括提交(COMMIT)和回滚(ROLLBACK)

2. 查询的生命周期

2.1. 数据库引擎编译SQL，解析代码以检查语义是否正确，确保引用的数据库对象存在，并且当前用户对这些对象有访问权

2.2. SQL代码会被转换为字节码

• 2.2.1. 这个字节码以一种有效的、机器可读的形式表示查询在数据库引擎中的执行步骤

2.3. 数据库的查询优化器分析字节码，以确定如何执行查询、重新排序和重构步骤，尽可能有效地使用资源

2.4. 查询被执行，并产生结果

3. 查询优化器

3.1. 查询优化器的任务是优化查询性能，并通过有效的顺序将查询分成适当的步骤，使成本最小化

• 3.1.1. 优化器将评估连接、索引、扫描数据大小和一些其他因素

• 3.1.2. 查询优化器试图以成本最低的方式执行查询

3.2. 连接是组合数据集和创建新数据集的最常见手段之一

• 3.2.1. 通常一个数据集（如表或文件）很少会被单独使用，我们通过将其与其他数据集相结合来创造价值

• 3.2.2. 连接在数据工程中担任很关键的角色，许多数据库都支持连接操作并具有很好的性能

• 3.2.3. 即使是过去以连接性能差而闻名的列式数据库，现在也普遍具有出色的性能

3.3. 提高查询性能的一个常见技术是预连接数据

• 3.3.1. 提前连接数据并让查询读取预连接的数据往往是有帮助的，这样你就不会重复进行计算密集型的工作了

• 3.3.2. 另一个策略是保持一个更规范化的模式，但为最常见的分析和数据科学用例创建预连接表

• 3.3.3. 可以很容易地创建预连接表，并培训用户利用这些表或在物化视图内进行连接

• 3.3.4. 使用公用表表达式(Common Table Expression，CTE)而不是嵌套子查询或临时表

• 3.3.4.1. 公用表表达式会比创建中间表的性能更好

• 3.3.5. 行爆炸

3.4. 查询优化器的解释计划将向你展示查询优化器如何确定其最佳的最低成本的查询，使用了哪些数据库对象（表、索引、缓存等）​，以及每个查询阶段的各种资源消耗和性能统计

• 3.4.1. 可以通过SQL的EXPLAIN命令提供解释计划，显示数据库执行查询的步骤顺序

3.5. 监控的内容

• 3.5.1. 关键资源的使用情况，如磁盘、内存和网络

• 3.5.2. 数据加载时间与处理时间

• 3.5.3. 查询的执行时间、记录数、扫描的数据大小，以及重分配的数据量

• 3.5.4. 可能导致数据库资源争夺的竞争性查询

• 3.5.5. 使用的并发连接数与可用连接数。并发连接过多会导致一些用户无法连接数据库

3.6. 避免全表扫描

• 3.6.1. 所有的查询都会扫描数据，但扫描的数据量是不一样的

• 3.6.2. 作为一个经验法则，你应该只查询你需要的数据

• 3.6.3. 只要有可能，就使用剪枝来减少查询中扫描的数据

• 3.6.3.1. 列式数据库和行式数据库需要不同的剪枝策略

• 3.6.3.2. 在一个列式数据库中，你应该只选择你需要的列

• 3.6.3.3. 大多数面向列的OLAP数据库还提供了额外的工具来优化你的表以提高查询性能

• 3.6.3.4. 可以将数据排序，从而更高效地访问非常大的数据集

• 3.6.3.5. BigQuery还允许你将表分割成更小的部分，允许你只查询特定的部分，而不是整个表

• 3.6.3.6. 在行式数据库中，剪枝通常以表索引为中心

• 3.6.3.7. 一般的策略是创建表索引以提高对性能最敏感的查询的性能，同时不要让表的索引太多，以免降低性

3.7. 数据库的提交

• 3.7.1. 数据库的提交是指在数据库中产生的一个变更，如创建、更新或删除一条记录、表或其他数据库对象

• 3.7.2. 许多数据库都支持事务，即以一种保持一致状态的方式同时提交几个操作的概念

• 3.7.3. 事务的目的是在数据库处于健康状态和发生故障时，保持数据库的一致状态

• 3.7.4. 当多个并发事件在同一数据库对象中进行读、写和删除时，事务也会处理隔离问题

• 3.7.5. 如果没有事务，用户在查询数据库时就会得到潜在的不一致的信息

• 3.7.6. 如果不符合ACID，你的查询可能会返回意外的结果

• 3.7.6.1. 可能是由于脏读造成的，脏读是指在一行数据被读取的同时，另一个未提交的事务改变了该行数据

• 3.7.7. 在更新和删除事务期间，一些数据库会创建新的文件来代表数据库的新状态，并保留旧的文件作为失败检查点的参考

• 3.7.8. 运行大量的小提交会消耗大量的存储空间且产生许多杂乱的文件，需要定期进行数据清理

• 3.7.9. 用在适当的场景并正确配置时，都是很棒的数据库

• 3.7.9.1. PostgreSQL

>  3.7.9.1.1. PostgreSQL方法的缺点是它需要行锁定（阻止对某些行的读写）​，这在很多情况下可能会降低性能

>  3.7.9.1.2. PostgreSQL没有针对大规模扫描或适合大规模分析应用程序的数据进行优化

• 3.7.9.2. MongoDB

>  3.7.9.2.1. 可变一致性数据库

>  3.7.9.2.2. 支持超高的写入性能

>  3.7.9.2.3. 代价是如果数据库写入流量过大，它会在没有通知的情况下放弃部分写入

>  3.7.9.2.4. 物联网应用，我们只想获得尽可能多的测量数据，并不关心捕获的测量数据是否完整

• 3.7.9.3. BigQuery

>  3.7.9.3.1. 利用了一个时间点全表提交模型

>  3.7.9.3.2. 可以在一个单一写查询中并行地写入大量的数据，这个操作是高度并发的

>  3.7.9.3.3. BigQuery的提交模型与Snowflake、Spark和其他一些公司使用的提交模型相似

3.8. 清理“死”记录

• 3.8.1. 事务在某些操作中会产生创建新记录的开销，比如更新、删除和索引操作，同时保留旧记录作为数据库最后状态的指针

• 3.8.2. 旧记录在数据库文件系统中会逐渐积累，而且永远不再需要使用

• 3.8.3. 应该在一个叫作清理的过程中删除这些记录

• 3.8.3.1. 为新记录释放了空间，从而减少了表的臃肿，加快了查询速度

• 3.8.3.2. 经过清理后的记录意味着查询计划可以更准确，过时的记录会导致查询优化器产生次优和不准确的执行计划

• 3.8.3.3. 可以清理低效的索引，使索引性能更好

• 3.8.4. 在由对象存储支持的数据库中(BigQuery、Snowflake、Databricks)，保留旧数据的唯一缺点是它占用了存储空间，根据数据库的存储定价模式，可能会多花钱

• 3.8.4.1. Databricks通常无限期地保留数据，直到它被手动清理

• 3.8.5. Amazon Redshift集群的磁盘支持多种配置方式，清理会影响其性能和可用存储

3.9. 利用缓存查询结果

• 3.9.1. 如果你的数据库缓存了查询结果，重新运行同一个查询可能会在1秒或更短时间内返回结果

• 3.9.1.1. 结果被缓存了，而且查询不需要冷启动

• 3.9.2. 只要有可能，就利用查询缓存结果来减少数据库的压力，为频繁运行的查询提供更好的用户体验

• 3.9.3. 物化视图提供了另一种形式的查询缓存

4. 流数据上的查询

4.1. 流数据是不断产生的

• 4.1.1. 为了充分利用数据流的优势，我们必须调整查询模式，以反映其实时性

4.2. CDC基本上是将分析数据库设置为生产数据库的从库

• 4.2.1. 历史最悠久的流查询模式之一是查询分析数据库，在略微滞后于生产数据库的情况下查询统计结果和聚合

4.3. 快速追随者的方法

• 4.3.1. 生产数据库一般不具备在处理生产工作负载的同时又运行大型分析扫描的能力

• 4.3.2. 快速追随者模式可以利用传统的事务数据库作为跟随者，但使用适当的OLAP系统也有很大优势

• 4.3.3. Druid和BigQuery都将流缓存与列存储结合起来，其设置有点类似于Lambda架构

• 4.3.4. 没有从根本上重新思考批处理查询模式

• 4.3.4.1. 仍在对表的当前状态运行SELECT查询，错过了根据流中的变更动态触发事件的机会

4.4. Kappa架构

• 4.4.1. 架构的主要思想是像处理事件一样处理所有数据，并将这些事件存储为一个流而不是一个表

• 4.4.2. Kappa架构存储来自变更数据捕获的事件

• 4.4.2.1. 事件流也可以直接从应用程序后端、物联网设备群或任何生成事件并能通过网络推送的系统中流出来

• 4.4.3. Kappa架构不是简单地把流式存储系统当作一个缓冲区，而是在一个较长的保留期将事件保留在存储中，并且可以直接从这个存储中查询数据

• 4.4.3.1. 保留期可以相当长（几个月或几年）

• 4.4.4. “核心思想”是将流式存储作为用于检索和查询历史数据的实时传输层和数据库

4.5. 窗口

• 4.5.1. 传统批处理查询的一个基本限制是将查询引擎视为一个外部观测者

• 4.5.2. 窗口是流查询和处理的一个基本特性

• 4.5.2.1. 窗口是通过动态触发器进行处理的小批次数据

• 4.5.2.2. 窗口是以某种方式根据时间动态生成的

• 4.5.3. 会话窗口

• 4.5.3.1. 会话窗口将发生在一起的事件分组，并过滤掉没有事件发生的非活动期

• 4.5.3.2. 会话窗口也可以为迟到的数据做要求

• 4.5.4. 固定时间窗口

• 4.5.4.1. 固定时间（又称滚动）窗口的特点是按固定的时间段运行并处理自上一个窗口关闭后的所有数据

• 4.5.5. 滑动窗口

• 4.5.5.1. 滑动窗口中的事件被归入固定时间长度的窗口中，窗口可能会重叠在一起

• 4.5.6. 水印

• 4.5.6.1. 水印是一个窗口用来确定数据是否属于既定的时间间隔或是否被认为迟到的阈值

• 4.5.6.2. 数据有时会不按照数据源的生成顺序获取

>  4.5.6.2.1. 如果到达的数据对窗口来说是新的，但比水印的时间戳要早，那么它就被认为是迟到的数据

5. 连接

5.1. 传统的表连接

• 5.1.1. 简单地在数据库中连接这两个表

• 5.1.2. 流可以为这些表中的一个或两个提供数据

5.2. 丰富性意味着我们将流加入其他数据中

• 5.2.1. 用于增强的数据源几乎可以来自任何地方（云数据仓库、RDBMS）中的表，或对象存储中的文件

5.3. 流对流的连接

• 5.3.1. 越来越多的流式系统支持流对流的直接连接

5.4. 典型的流数据连接架构依赖于流的缓存

• 5.4.1. 缓存数据的保留时间是可配置的

• 5.4.2. 更长的保留间隔需要更多的存储和其他资源