DuckDB 2.0 为何更快

# 为什么 DuckDB 2.0 更快 | MotherDuck 来源：https://motherduck.com/blog/why-duckdb-20-is-faster/ DuckDB 2.0 即将在今年秋季发布，alpha 版本已经推出！我在自己的笔记本电脑上测试了其中一些有趣的功能，也测试了对 AWS S3 的访问，看看对于那些构建表和数据管道（而非数据库引擎本身）的人来说，到底发生了哪些变化。 没错，DuckDB 2.0 确实更快了。但要获得性能提升，你需要了解数据的形态，有时还需要了解如何建模。本文涵盖了我认为最重要的三个特性，附上我测试的数据，以及我在提交日志中发现的一些隐藏宝藏。 以下所有数据均来自一台机器（M5 笔记本）和我的家庭网络，它对两个版本的拖慢程度大致相当。引用之前请先自己跑一遍试试 ;) 让我们从最简单也最令人兴奋的特性开始：异步 I/O。 ## 异步 I/O：查询 AWS S3 快得多 这是我最喜欢的特性，因为你的查询完全不需要改变。 这是一个查询 S3 上 2.2 GB Parquet 文件的查询（Stack Overflow 投票数据，2.28 亿行，2268 个行组），按投票类型计数。它只读取四列中的一列，约 230 MB。 ```sql CREATE SECRET s3 (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1'); SET enable_external_file_cache = false; -- 确保每次运行都真正访问 S3 SELECT VoteTypeId, count(*) AS n FROM read_parquet('s3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/votes.parquet') GROUP BY ALL ORDER BY 1; ``` 相同的查询，相同的笔记本： | | DuckDB 1.5.5 | DuckDB 2.0 alpha | |---|---|---| | 耗时 | 18.8 秒 | 7.7 秒 | 先说个注意事项：正如我在引言中提到的，这是我从家庭网络访问 us-east-1 的数据，所以两个数字都比较慢。如果你在云端计算资源上运行，速度会更快。 那么这里到底是什么"魔法"呢？ 文件被切成 2268 个行组，每个行组约 122,000 行。对于每个行组，DuckDB 需要下载字节数据、解码 Parquet、按类型计数投票，最后合并所有部分计数结果。 两类工作：等待网络，以及 CPU 运算。 在 1.5.5 中，18 个工作线程轮流执行这两项工作：下载、等待、解码、下载、等待。当一个工作线程等待时，CPU 处于空闲状态；当它解码时，就没有正在进行的下载，同时进行的下载数永远不会超过 18 个。 在 2.0 中，一个独立的线程池专门负责下载，保持几十个行组同时传输，并将字节数据缓存到缓冲区中。工作线程只负责解码，而且始终有现成的行组可供处理。**网络和 CPU 同时忙碌。** 异步 I/O： ```sql SELECT VoteTypeId, count(*) AS n FROM read_parquet('s3://.../votes.parquet') GROUP BY ALL ORDER BY 1; ``` **DuckDB 1.5.5**：下载、等待、解码，循环往复 votes.parquet · 2268 个行组 · 一列 ``` worker 1 idle 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 done worker 2 idle 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 done worker 3 idle 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 done worker 4 idle 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 等待 解码 done elapsed 0.0s ──────────────────────────────────────────── 18.8s ``` **DuckDB 2.0 alpha**：一个线程池提前下载，工作线程只负责解码 votes.parquet · 2268 个行组 · 一列 ``` dl pool idle fetch done worker 1 idle 解码 done worker 2 idle 解码 done worker 3 idle 解码 done elapsed 0.0s ────────────── 7.7s ``` 有一个设置控制这一行为：`read_ahead_depth`，即下载线程池可以比工作线程提前预取多少个行组。默认值为 `-1`（自动，根据线程数调整大小），所以**异步 I/O 默认开启**。设为 `0` 则恢复 1.5 的行为。 **S3 读取性能对比**： | 测试场景 | 1.5.5 | 2.0 alpha | |---|---|---| | 单个 2.2 GB Parquet 文件，一列 | 18.8 秒 | 7.7 秒 | | 23 个大 Parquet 文件，13.6 GB，一列 | 11.8 秒 | 3.9 秒 | | 单个 1.7 GB 普通 CSV | 116 秒 | 55 秒 | | 30 个微型 Parquet 文件，每个约 1 MB | 3.7 秒 | 3.3 秒 | 关于微型文件的说明：没有明显变化，因为这里的耗时主要是每个文件的网络往返（先读取 footer，再读取数据），预读无法消除这些开销。将数据湖存储为成千上万个 1 MB 的 Parquet 文件本来就是一种不良实践，2.0 并不能挽救它。基本功依然重要！ **TL;DR：通过 S3 读取数据，2.0 快了 2 到 3 倍，且查询零改动**，因为有一个独立的线程池在工作线程之前进行预取下载。默认开启（`read_ahead_depth = -1`）；设为 `0` 则恢复旧行为。 ## 递归 CTE：为深层父子关系数据提供加速 DuckDB 团队重写了递归 CTE 引擎，宣称在图可达性查询上性能提升 40 倍。别担心，我会用一个你熟悉的表来解释这是什么意思。 递归 CTE 就是对一张表进行循环遍历。 假设有一张 `employees` 表，包含两列：`manager` 和 `employee`。你想回答一个简单的问题：谁向谁汇报。 | manager | employee | |---|---| | Ana | Ben | | Ana | Cléa | | Ben | Dev | | Ben | Eli | | Cléa | Fay | | Dev | Gus | | Fay | Hal | 要做到这一点，从一行开始：Ana，CEO。 第一轮：找出所有直属 Ana 的人（Ben、Cléa）。 第二轮：找出所有直属上述人员的人（Dev、Eli、Fay）。 继续下一轮，直到某一轮没有找到新的人。组织架构图的每一层就是一轮查询。 查询如下： ```sql WITH RECURSIVE team(person) AS ( SELECT 'Ana' UNION SELECT e.employee FROM team t JOIN employees e ON e.manager = t.person ) SELECT count(*) FROM team; ``` 组织架构图、文件夹树、物料清单（BOM）、回复线程、数据血缘、git 历史：这些都属于同一类数据——表通常就是相同的两列，父和子。唯一的区别在于层级有多深，而深度就是查询的轮数。组织架构图可能只有八层，git 历史可能有数万层。 这就是 1.5 遇到的问题。每一轮，它都要回去重新读取整张表来找到下一层。八层意味着八次完整的全表扫描。数千层意味着对同一张表进行数千次全表扫描。 在 2.0 中，表只读取一次，在父列上构建一次查找索引，每一轮只需要查找刚刚找到的那几行。**成本现在取决于你实际触及的行数**，而不是轮数乘以表大小。 递归 CTE： ```sql WITH RECURSIVE team(person) AS ( SELECT 'Ana' UNION SELECT e.employee FROM team t JOIN employees e ON e.manager = t.person ) SELECT count(*) FROM team; ``` **DuckDB 1.5.5**：每轮重新读取整张表 ``` manager employee Ana Ben Ana Cléa Ben Dev Ben Eli Cléa Fay Dev Gus Fay Hal Fay Ida Gus Jon Hal Kim frontier round 1 → Ana round 2 → Ben, Cléa round 3 → Dev, Eli, Fay round 4 → Gus, Hal, Ida rows read 0 ─────────────────────────────────────────── 40 round 1 · 10 round 2 · 10 round 3 · 10 round 4 · 10 ``` **DuckDB 2.0 alpha**：只读取一次，每轮进行查找 ``` manager employee Ana Ben Ana Cléa Ben Dev Ben Eli Cléa Fay Dev Gus Fay Hal Fay Ida Gus Jon Hal Kim frontier round 1 → Ana round 2 → Ben, Cléa round 3 → Dev, Eli, Fay round 4 → Gus, Hal, Ida manager 查找索引： Ana → Ben, Cléa Ben → Dev, Eli Cléa → Fay Dev → Gus Fay → Hal, Ida Gus → Jon Hal → Kim rows touched 0 ────────── 10 ``` 回到 git 历史，这正是你会看到性能提升的地方。每个 commit 指向它的父 commit，所以表就是 `commit_id, parent_id`，遍历 HEAD 的祖先链就是每个 commit 一轮。 我生成了一个包含 20,000 个 commit 的仓库（带一些合并），用这样的递归 CTE 回溯到根： ```sql WITH RECURSIVE ancestors(id) AS ( SELECT max(id) FROM commits -- HEAD UNION SELECT p.parent_id FROM ancestors a JOIN commit_parents p ON p.commit_id = a.id ) SELECT count(*) FROM ancestors; ``` 查询耗时： | 祖先链遍历，20,000 个 commit | 耗时 | |---|---| | DuckDB 1.5.5 | 每次运行 1.8 秒到 16 秒不等 | | DuckDB 2.0 alpha | 每次运行均为 0.10 秒 | **TL;DR**：如果你需要遍历深层父子链（git 历史、数据血缘、回复线程、完整物料清单），**2.0 将你以前需要推给图数据库的任务变成了普通查询**。如果你的层级较浅（如组织架构图），可能看不到太大提升。无论哪种情况，都请将层级关系存储为一张父子表，使用整数 ID；当递归需要携带深度或成本等值时，使用 `USING KEY`。 ## VARIANT：比 JSON 更小更快 大家都喜欢 JSON。VARIANT 现在是 DuckDB 的一等数据类型，需要记住的关键词是"拆解"（shredding）。 不是弹吉他的那种拆解。在 VARIANT 语境下，"拆解"指的是：当 DuckDB 将一个行组写入磁盘时，它会分析你的 JSON 列，找出那些在大多数行中出现、且每次值类型都相同的字段。 一个常见示例，五百万条事件中的一个： ```json {"type": "purchase", "user": {"id": 42, "country": "FR", "premium": true}, "props": {"amount": 12.5, "currency": "EUR", "items": 2}, "tags": ["a", "b"]} ``` 事件类型始终是文本，`user.id` 始终是数字，`props.amount` 始终是小数。这些**字段在底层被提取为各自的真实列**。稀有字段，以及那些某行是数字、下一行是文本的字段，会保留在一个二进制余量（remainder）中。 所以你 JSON 中一致的部分被像普通表一样存储，只有混乱的部分才被存储为二进制 blob。 **JSON vs VARIANT：** ```sql SELECT count(*) FROM ev WHERE type='purchase' AND user.country='FR'; ``` **DuckDB 1.5.5**：JSON 字符串，查询时解析 payload · 以文本存储 ``` {"type":"click","user":{"id":51054,"country":"BR"},"amount":null} {"type":"click","user":{"id":51054,"country":"BR"},"amount":null} {"type":"purchase","user":{"id":42,"country":"FR"},"amount":12.5} {"type":"purchase","user":{"id":42,"country":"FR"},"amount":12.5} {"type":"view","user":{"id":11,"country":"FR"},"amount":null} {"type":"view","user":{"id":11,"country":"FR"},"amount":null} {"type":"purchase","user":{"id":8890,"country":"US"},"amount":301.2} {"type":"purchase","user":{"id":8890,"country":"US"},"amount":301.2} type · 解析后 click purchase view purchase country · 解析后 BR FR FR US 已读字符数 0 ──────────────────────────────────────────── 259 ``` **DuckDB 2.0 alpha**：VARIANT，checkpoint 时拆解 payload · 以 VARIANT 存储 ``` {"type":"click","user":{"id":51054,"country":"BR"},"amount":null} {"type":"click","user":{"id":51054,"country":"BR"},"amount":null} {"type":"purchase","user":{"id":42,"country":"FR"},"amount":12.5} {"type":"purchase","user":{"id":42,"country":"FR"},"amount":12.5} {"type":"view","user":{"id":11,"country":"FR"},"amount":null} {"type":"view","user":{"id":11,"country":"FR"},"amount":null} {"type":"purchase","user":{"id":8890,"country":"US"},"amount":301.2} {"type":"purchase","user":{"id":8890,"country":"US"},"amount":301.2} type click purchase view purchase user.id 51054 42 11 8890 country BR FR FR US amount null 12.5 null 301.2 已读值数 0 ────────── 8 ``` 最理想的场景是结构化日志。`level`、`service`、`latency_ms`、`trace_id` 每行都有，且类型始终一致，所以它们都会被拆解。那个不规则的 `extra` 对象会留在余量中，仍然可查询，只是稍慢一些。 陷阱：某行的 `latency_ms` 是 `231`，下一行变成 `"231ms"`，这种情况也会落入余量中。保持值类型的一致性很重要。 VARIANT 不仅仅是速度上的提升。文本在存储和 CPU 上都是"贪婪"的。 我取了五百万条事件，用三种方式存储相同的数据： ```sql -- A: JSON 字符串 CREATE TABLE ev AS SELECT json AS payload FROM read_ndjson_objects('events.ndjson'); -- B: VARIANT CREATE TABLE ev AS SELECT json::VARIANT AS payload FROM read_ndjson_objects('events.ndjson'); -- C: 普通表，每个字段一列，带类型 CREATE TABLE ev AS SELECT * FROM read_json('events.ndjson'); ``` 然后对每种存储方式运行三个查询：两个字段的过滤、按国家分组的数值字段求和、以及列表查找。 ```sql -- Q1: 两个字段的过滤 SELECT count(*) FROM ev WHERE payload.type::VARCHAR = 'purchase' AND payload.user.country::VARCHAR = 'FR'; -- Q2: 数值子字段的求和，按国家分组 SELECT payload.user.country::VARCHAR AS country, sum(payload.props.amount::DOUBLE) AS amount FROM ev WHERE payload.type::VARCHAR = 'purchase' GROUP BY ALL ORDER BY 1; -- Q3: 列表查找 SELECT count(*) FROM ev WHERE list_contains(payload.tags::VARCHAR[], 'c'); ``` | | JSON 字符串 | VARIANT (2.0 alpha) | VARIANT (1.5.5) | 每字段一列 | |---|---|---|---|---| | 磁盘占用 | 224 MB | 85 MB | 81 MB | 45 MB | | Q1 过滤 | 366 ms | 63 ms | 4.96 秒 | 52 ms | | Q2 按国家求和 | 408 ms | 61 ms | 5.28 秒 | 51 ms | | Q3 列表包含 | 357 ms | 1.96 秒 | 4.65 秒 | 67 ms | 我们能从中看到什么？ - VARIANT **比 JSON 字符串小 2.7 倍**。你可以用 `pragma_storage_info('ev')` 看到原因：对象被拆分为子列，事件类型以字典编码存储。`EXPLAIN` 显示过滤被下推到扫描阶段，所以对 `payload.type` 的查询只需读取一个子列。 - 对于触及被拆解字段的查询（过滤或数值子字段的求和），**VARIANT 比解析 JSON 文本快约 6 倍**，与带类型的普通列相比差距在 20% 以内。与 1.5.5 中的 VARIANT 相比快了 78 倍，因为 1.5.5 有这个类型但没有拆解功能。 - 列表查询是个例外。在这个 alpha 版本中，将 VARIANT 列表转换为 `VARCHAR[]` 需要两秒，比 JSON 路径还慢。字段访问目前是拆解的主要受益场景；列表还没跟上。 所以建模的黄金法则依然有效：**对你已知的结构进行建模**。每个查询都涉及的字段值得拥有真实的列，而提升一个字段只需要两句话： ```sql ALTER TABLE ev ADD COLUMN type VARCHAR; UPDATE ev SET type = payload.type::VARCHAR; ``` **TL;DR**：如果你的事件共享一组一致的字段和一致的值类型，**将它们存储为 VARIANT 而不是 JSON 字符串**。你将获得三分之一的存储空间，以及像真实列一样运行的字段查询。将每个查询都涉及的字段提升为真实列，长尾字段保留在 VARIANT 中，目前避免在热查询中做列表类型转换。 ## 从版本更新和提交日志中的快速发现 **触发器（Triggers）**。当行发生变化时，表可以自动执行一小段 SQL。例如，更新一些价格，触发器可以在变更前后捕获每一行（"转换表"），并将新旧数据都写入历史表。以前，每个触及这张表的工具都必须记得将历史记录写到某处。现在可以直接在数据库端完成。 ```sql CREATE TRIGGER price_history AFTER UPDATE ON prices REFERENCING OLD TABLE AS before_rows NEW TABLE AS after_rows FOR EACH STATEMENT INSERT INTO price_history (id, old_price, new_price) SELECT a.id, b.price, a.price FROM before_rows b JOIN after_rows a USING (id); ``` **嵌套 Schema**：`CREATE SCHEMA finance.reports;` 以及其中的表。 **在 CTE 中执行 DML**：在 `WITH` 中执行 `DELETE ... RETURNING`，然后从中 `INSERT`，这是原子移动行的经典模式。 ```sql WITH moved AS MATERIALIZED ( DELETE FROM staging RETURNING * ) INSERT INTO prod SELECT * FROM moved; ``` 其他有趣的功能： -