列式数据库为什么这么快？原因揭秘

中文العربيةDeutschEnglishEspañolFrançaisहिन्दीBahasa Indonesia日本語한국어PortuguêsРусский
BBetter Stack
Computing/Software

Transcript

00:00:00虽然我非常喜欢 Postgres，但它并不适合所有场景。还有另一种
00:00:04叫做列式数据库的数据库，它运行某些查询的速度可以快上 40 倍。这些
00:00:10数据库通过将列而不是行存储在一起运作，这意味着在处理
00:00:15分析平台等特定用例时，你会获得巨大的优势。当然，任何技术都有权衡，
00:00:20但今天我们将深入探讨什么是列式数据库，并对比一些查询在
00:00:25Postgres、ClickHouse 和 DuckDB 上的表现，
00:00:31看看它们的优缺点。请拭目以待，看完后你将对列式数据库
00:00:36以及何时选择正确的技术有深刻的理解。
00:00:43所以在开头我说过，某些查询的速度可以快 40 倍，这确实不假。如果我们拿一个
00:00:49数据库表（这里我加载了 1 亿行数据），并用 Postgres 运行一个 group by
00:00:55查询，它大约需要 9.7 秒才能执行完毕，因为我们需要对每一行
00:01:00数据的收入进行聚合。如果我们使用列式数据库对同一数据集运行相同的查询——
00:01:06而且运行的完全是同一段 SQL，ClickHouse 和 DuckDB 都具有类似 Postgres 的 SQL 语法，所以你
00:01:13会觉得非常亲切——ClickHouse 仅需 0.28 秒，而 DuckDB 仅需 0.24 秒，速度足足快了 40 多倍。
00:01:22这怎么可能？听起来简直难以置信，同样处理 1 亿行数据居然只需要 0.24 秒。
00:01:30而列式数据库之所以能给出相同结果，是因为它们所做的工作要少好几个数量级。
00:01:36让我们来看看另一个实际查询。顺便提醒大家，我们经常发布关于 AI 和技术的持续内容，
00:01:42如果你喜欢这类内容，为什么不订阅 Better Stack 呢？这次我们来统计
00:01:47两个时间戳之间、国家代码为 gb 的事件数量。在我们这个例子中，查看的是
00:01:533 月份的数据。结果：Postgres 用时 5.9 秒，ClickHouse 用时 0.06 秒，DuckDB 用时 0.03 秒。3 月份
00:02:02大约占了总事件的 13%，所以 Postgres 仍然需要检查数百万行数据。我们可以
00:02:09在这里添加激进的索引来改善状况，但这依然无法拉近与 ClickHouse 和 DuckDB 的差距。那么为什么
00:02:15列存数据库在这里快这么多呢？其实，ClickHouse 根本不为单行建立索引。当你创建
00:02:20一张表时，你需要给它一个排序列（sort key），数据会按时间戳排序，然后它会将排序后的数据分割成
00:02:32若干块，并只记下每个块中第一个时间戳。对于 1 亿行数据来说，这大约只有 12,000 个标记，
00:02:39小到足以全部放在内存中。所以当我们请求 3 月份的数据时，它会快速搜索这些
00:02:44标记，找出可能包含 3 月数据的块，并只读取这些块。在我们的例子中，只需从 12,208 个块中读取 1,633 个，
00:02:52磁盘上的其他所有内容都被跳过了。DuckDB 的做法也与之类似，因为它对每一列的每个块都保存了最小值
00:03:00和最大值。因此我们可以查看一个块，发现时间戳是从一月到
00:03:06二月，从而直接跳过整块而无需读取。再加上之前提到的只打开
00:03:10所需列的技巧，你就能实现 0.03 秒的极速。然而，当你想要
00:03:17通过 ID 选择单行时，情况就会发生 180 度大转弯。这个简单的查询说明了很多问题：Postgres 耗时 2 毫秒，ClickHouse 耗时 168
00:03:26毫秒，而 DuckDB 有趣的是仍然只需 2 毫秒。Postgres 在 ID 上拥有
00:03:32B树索引，所以它顺着树向下查找，直接落到包含该行数据的单个页面上，仅需 2
00:03:37毫秒即可完成。但在 ClickHouse 中我们遇到了问题，它的唯一索引就是那个排序列，而这张表
00:03:43首先按时间戳排序，其次才按 ID 排序。所以当我们请求单个 ID 时，它根本不知道这个 ID
00:03:51存在于哪一个块中，最终不得不检查全部 12,208 个块。一旦找到该行，它
00:03:57依然必须打开全部 8 个列文件并将该行重新拼装起来，对于这样一个
00:04:03简单的查询来说，这要做大量的工作。那么 DuckDB 又是如何蒙混过关的呢？它在数据上碰巧运气好：
00:04:09这些 ID 是按顺序插入的，因此每个块上的最小和最大值与 ID 完美对应，DuckDB 可以直接跳转到
00:04:16正确的那一个。如果 ID 被打乱了，它就会像 ClickHouse 一样扫描整列。我们现在来看一下
00:04:21更新一行的操作。我们针对 Postgres 和 DuckDB 使用了一个查询，而为
00:04:26ClickHouse 使用了稍微不同的版本。Postgres 的耗时为 5 毫秒，ClickHouse 为 5.8 秒，而 DuckDB 再次仅需几十
00:04:34毫秒。Postgres 只需重写一行并更新一个索引条目；而在 ClickHouse 中，数据文件
00:04:40是不可变的，从不原地修改，因此为了更改一个收入值，它必须重写
00:04:46该表对应数据块的整个收入列文件。ClickHouse 甚至要求你将其写为
00:04:51ALTER TABLE（修改表），因为它将其视为对整张表的变更。虽然有一个处于测试阶段的轻量级更新，
00:04:56但它只适用于少量行（最多约占表的 10%）。DuckDB 则介于
00:05:03两者之间，因为它的文件可以原地修改，所以单行更新的耗时只需几十
00:05:08毫秒。最后，让我们统计表中不同用户的数量，ClickHouse 使用了
00:05:14稍微变体的查询。结果是：Postgres 38.4 秒，ClickHouse 0.78 秒，
00:05:22DuckDB 不到 1 秒。同样，Postgres 在如此庞大的整张表上进行聚合时，
00:05:28需要做大量的工作。通常你会在分析等场景下使用列式数据库，
00:05:34在这些场景中，跨各个列聚合数据是一项常见任务。在这里我们看了两个选项：ClickHouse
00:05:41是一个托管服务器，开源并在大多数主流平台上可用，要在本地运行它，你需要在你的机器上运行
00:05:47一个 ClickHouse 服务器，这更接近 Postgres 的工作方式。然而，DuckDB 更像是 SQLite，
00:05:54它是一个在其自身进程中运行的库，整个数据库存放在磁盘上的单个文件中，因此
00:06:00当一个更新正在进行时该文件可能会被锁住，这会影响并发性。两者都是列式
00:06:06数据库，但采用了截然不同的方法。因此，你最终决定使用哪个数据库，不仅仅取决于
00:06:12我在这里能假设的因素，它不只是关乎在本地机器上运行演示时的原始查询速度，
00:06:17还关乎可扩展性、冗余性和可扩展性。当然，Postgres 拥有丰富的插件生态系统，
00:06:24可以通过多种方式扩展其功能集，你可以在接下来的视频中看到这一点。

Key Takeaway

列式数据库通过将列数据连续存储并利用块级极值和稀疏索引，在处理大规模聚合分析查询时能比 Postgres 快上 40 倍以上，但在单行更新和按 ID 检索时表现较差。

Highlights

  • Postgres 运行 1 亿行数据的 group by 查询耗费 9.7 秒，而 ClickHouse 仅需 0.28 秒，DuckDB 仅需 0.24 秒。

  • 在统计 3 月份特定国家事件的查询中，Postgres 耗时 5.9 秒，ClickHouse 耗时 0.06 秒，DuckDB 耗时 0.03 秒。

  • ClickHouse 通过时间戳排序列将 1 亿行数据分割为约 12,000 个标记块，仅读取匹配的 1,633 个块。

  • 通过 ID 选择单行时，Postgres 仅需 2 毫秒，而 ClickHouse 因缺乏主键索引耗费 168 毫秒。

  • ClickHouse 的数据文件不可变，单行更新需要重写对应数据块的整个列文件，耗费 5.8 秒。

  • Postgres 统计不同用户的数量耗费 38.4 秒，而 ClickHouse 仅需 0.78 秒，DuckDB 不到 1 秒。

Timeline

列式数据库的查询性能优势

  • Postgres 在处理 1 亿行数据的 group by 聚合查询时需要 9.7 秒。
  • ClickHouse 和 DuckDB 运行完全相同的 SQL 查询仅需 0.28 秒和 0.24 秒。
  • 列式数据库通过将列存储在一起，使相同操作所需的工作量减少了数个数量级。

Postgres 面对海量行数据聚合时必须逐行读取收入等字段。列式数据库针对分析型场景设计，相同数据集的查询速度实现了 40 倍以上的提升。两者使用完全相同的 SQL 语法，保证了开发体验的连贯性。

列式存储的高效检索机制

  • Postgres 在按时间戳和国家代码统计事件时耗时 5.9 秒，ClickHouse 耗时 0.06 秒，DuckDB 耗时 0.03 秒。
  • ClickHouse 仅为排序列建立稀疏标记，将 1 亿行数据划分为 12,208 个块并跳过无关磁盘内容。
  • DuckDB 通过保存每列每个块的最小值和最大值直接跳过不相关的整块数据。

分析查询通常只涉及部分列和特定的时间范围。ClickHouse 利用排序列将数据切分为块并记录首个时间戳，在面对海量数据时只需读取少部分匹配的数据块。DuckDB 则通过块级的最值范围过滤实现了 0.03 秒的极速响应。

单行查询与更新的性能劣势

  • 按 ID 选择单行时 Postgres 耗时 2 毫秒，ClickHouse 耗费 168 毫秒，DuckDB 耗时 2 毫秒。
  • Postgres 依靠 B 树索引直接定位行数据，而 ClickHouse 由于未按 ID 排序必须检查所有块。
  • 单行更新操作中 Postgres 仅需 5 毫秒，而 ClickHouse 因数据文件不可变需要重写整个列文件耗费 5.8 秒。

列式数据库在行级操作上付出巨大代价。由于 ClickHouse 的主排序键是时间戳而非 ID，按 ID 查询时必须扫描全表并重新拼装列文件。不可变文件的特性也导致单行更新时无法原地修改，必须重写对应数据块的整列数据。

架构差异与技术选型

  • Postgres 统计不同用户数量耗费 38.4 秒，ClickHouse 仅需 0.78 秒，DuckDB 不到 1 秒。
  • ClickHouse 采用独立服务器架构，需要在机器上运行服务进程。
  • DuckDB 类似于 SQLite，作为嵌入式库在自身进程运行，数据库存放在单个磁盘文件中。

列式数据库适用于跨列聚合的分析场景。ClickHouse 与 Postgres 类似需要服务器进程支撑，而 DuckDB 作为单文件库直接运行在进程内。技术选型不仅取决于本地演示查询速度，更需要综合考量可扩展性、并发性及生态系统。

Community Posts

No posts yet. Be the first to write about this video!

Write about this video