列式数据库详解

在本指南中，我们将探讨列式数据库、列存储、面向列的数据库、按列数据库或“插入您最喜欢的首字母缩略词”。它们与基于行的数据库有何不同？它们擅长什么？使用列存储有什么优势？

我们将回答这些问题以及更多，但首先，先来一段简短的历史课。

按列存储数据的想法并不新鲜。1985 年，GP Copeland 和 SN Khoshafian 在一篇题为 分解存储模型 (DSM) 的论文中介绍了这个概念。

DSM 提出将数据存储在二元关系中，将每个属性值与记录的代理配对，从而有效地按列而不是按行组织数据。作者认为，虽然这种方法总体上需要更多的存储空间，但在简单性、通用性和检索性能方面具有潜在的优势，尤其是在涉及属性子集的查询中。

到 1999 年，MonetDB 已经实现了 DSM 论文中的想法，并展示了它们的有效性。Peter A. Boncz、Stefan Manegold 和 Martin L. Kersten 的论文“针对新瓶颈优化的数据库架构：内存访问”详细介绍了他们构建 MonetDB 的经验，并深入分析了他们的设计选择。

作者强调，他们早期关于垂直碎片化在改善内存访问方面重要性的直觉，后来通过实验和详细的性能建模得到了验证。他们的工作表明，即使在 CPU 和内存速度差距不断扩大的情况下，围绕内存访问考虑因素精心设计的数据库架构也可以产生显着的性能提升。

自那时以来，向量化处理和数据压缩技术等进一步的创新，使得基于列的存储对于大规模分析处理更加有用。

随着列存储从理论概念发展到实际实现，它们与传统基于行的数据库的根本区别变得越来越明显。

数据组织上的这种差异是列存储在特定查询中表现出色的核心原因，而基于行的系统在其他查询中仍然是首选。将它们的数据布局与传统的基于行的数据库进行比较，对于理解列存储的优势和劣势至关重要。

在面向行的数据库中，连续的表行按顺序存储，一个接一个。这种布局允许快速检索行，因为每一行的列值都存储在一起。

在面向列的数据库中，表存储为列的集合，即，每个列的值按顺序存储，一个接一个。这种布局使得恢复单行变得更加困难（因为现在行值之间存在间隙），但是诸如过滤器或聚合之类的列操作比在面向行的数据库中快得多。

在列存储中，诸如过滤器或聚合之类的列操作比在面向行的数据库中更快。

下图显示了天气数据在基于行和基于列的数据库中的存储方式

在基于行的方法中，给定行的所有值都是相邻的，而在基于列的方法中，给定列的值是相邻的。

基于行的方法更适合单行查找。基于列的方法更适合执行聚合或过滤少量列的分析查询，尤其是在处理大型数据集时。

当您想要运行聚合和过滤少量列但大量行的查询时，请使用列存储。这些数据集通常包含很多列，但是我们在每个查询中只会接触到这些列的子集。

例如，以真实世界的匿名网络分析数据集为例，该数据集包含 1 亿行，并具有以下列

SELECT groupArray(name) AS columns
FROM system.columns
WHERE (database = 'metrica') AND (`table` = 'hits')
FORMAT Vertical;

columns: ['WatchID','JavaEnable','Title','GoodEvent','EventTime','EventDate','CounterID','ClientIP','RegionID','UserID','CounterClass','OS','UserAgent','URL','Referer','Refresh','RefererCategoryID','RefererRegionID','URLCategoryID','URLRegionID','ResolutionWidth','ResolutionHeight','ResolutionDepth','FlashMajor','FlashMinor','FlashMinor2','NetMajor','NetMinor','UserAgentMajor','UserAgentMinor','CookieEnable','JavascriptEnable','IsMobile','MobilePhone','MobilePhoneModel','Params','IPNetworkID','TraficSourceID','SearchEngineID','SearchPhrase','AdvEngineID','IsArtifical','WindowClientWidth','WindowClientHeight','ClientTimeZone','ClientEventTime','SilverlightVersion1','SilverlightVersion2','SilverlightVersion3','SilverlightVersion4','PageCharset','CodeVersion','IsLink','IsDownload','IsNotBounce','FUniqID','OriginalURL','HID','IsOldCounter','IsEvent','IsParameter','DontCountHits','WithHash','HitColor','LocalEventTime','Age','Sex','Income','Interests','Robotness','RemoteIP','WindowName','OpenerName','HistoryLength','BrowserLanguage','BrowserCountry','SocialNetwork','SocialAction','HTTPError','SendTiming','DNSTiming','ConnectTiming','ResponseStartTiming','ResponseEndTiming','FetchTiming','SocialSourceNetworkID','SocialSourcePage','ParamPrice','ParamOrderID','ParamCurrency','ParamCurrencyID','OpenstatServiceName','OpenstatCampaignID','OpenstatAdID','OpenstatSourceID','UTMSource','UTMMedium','UTMCampaign','UTMContent','UTMTerm','FromTag','HasGCLID','RefererHash','URLHash','CLID']

此表包含 100 多个列，但我们通常在每个查询中只考虑其中的几个。例如，我们可以编写以下查询来查找 2013 年 7 月最受欢迎的手机型号

SELECT MobilePhoneModel, COUNT() AS c
FROM metrica.hits
WHERE
      RegionID = 229
  AND EventDate >= '2013-07-01'
  AND EventDate <= '2013-07-31'
  AND MobilePhone != 0
  AND MobilePhoneModel not in ['', 'iPad']
GROUP BY MobilePhoneModel
ORDER BY c DESC
LIMIT 8;

此查询演示了使其成为列存储理想选择的几个特征

1. 选择性列访问：尽管该表有 100 多个列，但查询只需要从 MobilePhoneModel、RegionID、EventDate 和 MobilePhone 读取数据。
2. 过滤：WHERE 子句允许数据库快速消除不相关的行。
3. 聚合：COUNT() 函数聚合数百万行的数据。
4. 大规模处理：该查询在 1 亿条记录的数据集上运行，这是列存储开始显示其分析查询价值的下限。

您可以在 ClickHouse SQL Playground 上尝试此查询，该 Playground 托管在 sql.clickhouse.com。该查询在不到 100 毫秒的时间内处理 1 亿行，您可以将结果作为表格进行探索

或作为图表

虽然列存储在某些情况下（尤其是在涉及大型数据集的分析工作负载中）表现出色，但它们并非万能的解决方案。了解它们的局限性对于就数据库架构做出明智的决策至关重要。

让我们探讨一下在哪些情况下，可能存在比列存储更好的选择。

列存储专为分析查询而设计，这些查询通常聚合或扫描许多行中的少量列。但是，对于基于行的查找（在 在线事务处理 (OLTP) 系统中很常见），它们可能不是最佳选择。

以我们的网络分析数据集示例为例。如果您的大多数查询都类似于这样

SELECT * 
FROM metrica.hits 
WHERE WatchID = 8120543446287442873;

列存储需要

1. 读取 WatchID 列以找到匹配的行
2. 从匹配行的每个其他列中获取数据
3. 重建完整的行

此过程可能效率低下，尤其是在您获取许多列时。相比之下，基于行的存储会将单行的所有数据连续存储，从而使此类查找速度更快。

真实世界的示例：电子商务平台通常需要快速检索特定订单的所有详细信息。此操作在基于行的存储中效率更高。

列存储的优势通常只有在规模上才变得明显。当处理较小的数据集（例如，数百万行或更少）时，列存储和行存储在分析查询方面的性能差异可能不需要非常显着，即可证明添加另一个数据库是合理的。

例如，如果您要分析一家每月交易量只有几千笔的小型企业的销售数据，那么一个索引良好的基于行的数据库可能就足够了，而无需列存储。

大多数列存储不支持 ACID（原子性、一致性、隔离性、持久性）事务，这对于许多业务应用程序至关重要。如果您的用例需要强大的事务保证，那么传统的基于行的 RDBMS 将是更合适的选择。

例如，银行系统处理帐户转账时，必须确保借方和贷方在帐户之间以原子方式应用。这通常更容易通过基于行的事务数据库来实现。

使用列存储有两个主要优势：大型数据集的查询性能和高效的数据存储。这些优势在数据仓库、商业智能和大规模分析场景中尤其有价值。让我们了解一下用于实现此目的的技术。

在本文的开头，我们了解到列存储将来自同一列的数据彼此相邻地存储。这意味着相同的值通常是相邻的，这非常适合数据压缩。

列存储非常适合数据压缩。

列存储利用各种编码和压缩技术

1. 字典编码：用整数 ID 替换重复的字符串值，从而显着减少低基数列的存储。
2. 游程编码 (RLE)：通过存储值及其计数来压缩重复值的序列。例如，“AAAABBBCC”变为“(A,4)(B,3)(C,2)”。
3. 位打包使用表示给定范围内的整数所需的最小位数，这对于值范围有限的列尤其有效。
4. 通用压缩：对于进一步压缩，诸如 ZSTD、LZ4 和 GZIP 之类的算法应用于这些专门的编码。

例如，考虑一个存储国家/地区代码的列。字典编码可能会将“USA”或“CAN”分别替换为 1 和 2，而不是重复存储它们。如果有许多连续的“USA”条目，RLE 可以进一步将其压缩为 (1, 1000)，表示“USA”重复 1000 次。

好处不仅限于节省存储空间。磁盘上的数据减少转化为 I/O 减少，从而加快查询和数据插入。虽然解压缩确实会引入一些 CPU 开销，但 I/O 减少通常远远超过此成本，尤其是在 I/O 密集型分析工作负载中。

列存储在分析查询（尤其是涉及大型数据集的查询）中真正大放异彩。它们的性能优势源于以下几个因素

1. 高效的 I/O 利用率：列存储可以通过仅读取与查询相关的列来跳过大量不相关的数据。例如，在像 SELECT AVG(salary) FROM employees WHERE department = 'Sales 这样的查询中，列存储只需要读取 salary 和 department 列，可能会忽略数十甚至数百个其他列。
2. 向量化查询执行：列式数据布局与现代 CPU 架构完美对齐，从而实现高效的向量化处理。列存储不是逐行处理数据，而是可以同时对单个列的大块（向量）进行操作。这种方法最大限度地提高了 CPU 缓存利用率，并允许进行 SIMD（单指令、多数据）操作，从而显着加快计算速度。

列存储擅长分析查询。它们可以有效利用 I/O，因为它们可以快速跳过不相关的数据，因为它们只需要读取查询中涉及的列，而不是整行。

列存储中的数据布局也是向量化查询执行的理想选择，这是列存储如何通过有效利用现代 CPU 架构来实现查询性能的关键特征。

虽然列存储为分析工作负载提供了显着的优势，但它们也带来了独特的挑战，特别是对于习惯于传统基于行的系统的用户而言。了解这些挑战对于有效实施和管理列存储数据库至关重要。

在基于行的存储中，更新非常简单：数据在原地修改，事务完成。但是，列存储在根本不同的范例上运行。

列存储通常将数据组织成不可变的列块。这种不变性是一把双刃剑：它实现了高效的压缩和查询性能，但也使更新过程复杂化。当需要更新行时，整个过程变得更加复杂。

我们无法就地更新行，而是需要写入新的列块。

列存储的实现将决定如何将新值提供给查询引擎。可以替换整个列块，可以将新块与现有块合并，或者查找表可以指示要为给定行读取的适当块。

这增加了数据库内部的复杂性，并且意味着列存储通常针对批量插入或更新记录进行优化，而不是进行许多小的更新。

从历史上看，列存储针对读取繁重的工作负载进行了优化，并且通常缺乏有效的连接功能。这种限制导致了一种常见的做法：在摄取期间反规范化数据。

反规范化涉及将来自多个表的数据合并到单个宽表中。虽然这种方法可以显着提高查询性能，但它也带来了权衡

1. 数据冗余：反规范化数据通常包含重复信息，从而增加了存储需求。
2. 更新复杂性：对反规范化数据的更改可能需要在多个行或列中进行更新。
3. 数据一致性：维护反规范化数据之间的一致性可能具有挑战性，尤其是在频繁更新的系统中。

现代列存储改进了其连接性能，使得极端的反规范化变得不必要。但是，为了获得最大的查询性能，某种程度的反规范化通常仍然是有益的。挑战在于在规范化（为了数据完整性和易于更新）和反规范化（为了查询性能）之间找到适当的平衡。

在列存储中，数据的物理组织会显着影响查询性能。在数据摄取之前了解您的查询模式至关重要。

关键考虑因素包括

1. 排序键：选择正确的列进行排序可以显着加快范围查询和连接。
2. 分区：有效的数据分区可以使查询引擎跳过大量不相关的数据块。

例如，如果大多数查询都按日期范围进行过滤，则主要按日期对数据进行排序可能会产生显着的性能优势。但是，如果在初始数据加载期间未考虑这一点，则要获得最佳性能可能需要代价高昂的数据重组过程。

有大量的数据库具有面向列的存储，因此我们将仅介绍撰写本文时最流行的数据库。

如前所述，MonetDB 是最初的列存储，并且至今仍然存在。从那时起，又出现了其他列存储，包括 SAP IQ、Greenplum DB、Vertica 等。

Amazon Redshift、Google BigQuery 和 Snowflake 于 2010 年代初作为云数据仓库发布。它们都以列形式存储数据，主要用于对大量数据进行面向内部的分析。

Apache Pinot、Apache Imply 和 ClickHouse 出现在 2010 年代后期。它们可以用于面向内部的分析，但也支持 实时分析，这是数据库向外部用户和客户提供洞察力的先决条件。

此外，像 Postgres 这样的基于行的存储通过 Citus 或 Timescale 具有列式附加组件。

是的，ClickHouse 是一个列式数据库。它以 开源软件 和 云产品 的形式提供，并且是最快和资源效率最高的实时数据仓库和开源数据库。

ClickHouse Cloud 被 Sony、Lyft、Cisco、GitLab 和许多其他公司使用。

您可以在用户案例部分中了解有关 ClickHouse 解决的问题的更多信息。

是的，这很常见。使用两种类型的存储的混合架构可能如下所示

• 处理日常事务操作
• 管理实时数据更新和插入
• 确保 ACID（原子性、一致性、隔离性、持久性）合规性

• 管理大规模数据分析
• 处理对历史和实时数据的分析查询
• 针对读取繁重的工作负载和聚合进行优化

许多组织采用变更数据捕获 (CDC) 技术来保持这些系统的同步。CDC 是一组软件设计模式，用于确定和跟踪数据更改，以便可以使用更改后的数据采取操作。

在 OLAP 与 OLTP 指南中，您可以阅读有关使用 CDC 在 OLTP 和 OLAP 之间移动数据的更多信息。