理解基数 Cardinality

显示全部楼层 · 2021-2-21 19:32:24

Cardinality 是数据库和数据建模领域的一个重要的基础概念，数据库领域的 Cardinality 表示去重后唯一值（Unique Values）的数量，比如 Columns Cardinality 指列包含的不重复值的个数，数据建模中的 Cardinality 表示关系的类型。

参照微软文档的官方翻译，Cardinality 被译为基数，这个翻译比较抽象，你也可以直接用它的原文。

列基数 Columns Cardinality

列基数是列包含的不重复值的数量。这个数字对于控制列的大小非常重要，它将直接影响模型压缩的效果和引擎扫描时的性能。在允许的范围内，你应该尽量将列基数减少到最低。原因是：

基数是影响模型文件大小的关键因素之一，它比我们直觉上认为的行数更重要。
许多 DAX 操作（如迭代和筛选）的执行时间直接依赖于这个数字。

列基数与文件大小

我们习惯用行数评估文件的大小，比如认为 100 万行的文件体积比 10 万行的更大，实际上，行数并非决定文件大小的直接因素。DAX 运行在 SQL Server Analysis Services (SSAS)表格模型、Power BI 服务以及作为 Microsoft Excel 插件的 Power Pivot 中，本质上它们都是表格模型的不同形式。

表格模型的数据存储于 VertiPaq，这是一个位于内存中的列式数据库，与行式数据库不同，这是一种以列形式存储数据的结构

产品表在 Vertipaq 中以列的形式存储

VertiPaq 会使用各种手段来压缩数据以减少数据模型的内存占用，包括尝试不同的列编码方式、确定表的最佳排序等等（你将在 Vertipaq 引擎一章详细了解这方面的知识）。在表自身的属性中，列的数据类型、列值的分布特征、列基数都会影响编码效率，进而影响最终的压缩效果，这其中列基数起到关键作用。VertiPaq 为模型的每列创建一个不重复值的字典和一个引用字典的位图索引，这个位图索引可以高度压缩，在某些情况下，高基数列的字典开销可能占该列开销的 90%以上

举例来说，行数相同的列，基数越大，所占的体积也越大，下图是用 PQ 生成的 100 万行随机数的单个列，左表的列基数只有 11，而右表是 1001，需要一张更大的字典表，所以经过压缩后虽然两张表行数相同，但右表的体积更大，是左表的 2.8 倍。

行数相同的情况下，基数越大，文件也越大

如果在列基数相同的情况下增加行数，这时文件大小取决于数据的分布、压缩算法的效率和引擎的版本，一般来说，行数越大，压缩效果越好，比如列基数同为 11 的两张单列表，一亿行的表没有将 100 万行表的体积线性增加 100 倍，而是只有的前者的 90 倍，压缩效果更好。

在基数相同的情况下，行数越多，压缩效果越好

查看列基数

既然列基数如此重要，手工查看和统计每列的基数显然是不切实际的，Vertipaq Analyzer 可以帮你快速查看并导出模型的基础属性数据，其中就包括 Cardinality，现在这个工具已经集成到 DAX Studio 中，下图是使用 DAX Studio 查看 Contoso 数据模型中 Sales 表的列基数分布，你可以将其导出到 Excel

使用 DAX Studio 查看每列的基数

优化列基数的建议

在设计数据模型时，应该考虑列基数的影响，如果要在关系、筛选器或计算中使用列，考虑采取可能的优化措施，在允许的范围内减少列的基数，这可以显著提升性能表现，以下优化建议可供参考：

优化关系列：建立关系的两张表所用的列的基数会影响所有基于关系的计算，降低列基数可以提高计算效率，但需要考虑参照完整性和模型的准确性。当关系列的基数达到 10 万的时候，查询已经开始存在潜在的性能风险，当升至 100 万的时候，需要考虑适当使用反规范化的方法优化模型。
优化用于计算的列：降低度量值中所用列的基数可以提高公式的性能，比如一个表示交易数量或金额的数字列，你一般不会更改数字的精度。但如果列是浮点型的数值列，你可以考虑删除不相关的小数位以减少列基数。例如在统计温度的时候，你可以将该值四舍五入到需要的小数位，避免不必要的精度导致列基数过高。
优化文本列：因为列基数影响的是字典大小，所以把列移动到单独的表也不会有任何好处，因为字典并没有变化。最好的建议是删除报告不需要的列，只保留有用的列。
优化事务 ID：此列在大型表中具有高基数。如果 DAX 查询中不需要，可以考虑删除它。如果需要在钻取操作中使用，例如，查看形成特定聚合的事务数据，可以考虑将数字/字符串分成两个或多个部分，每个部分可以具有更少的惟一值。
优化日期和时间列：对于同时包含日期和时间信息的列，应始终将其拆分为两列，日期和时间分开存放。不要使用计算列来执行此类拆分。而是通过读取原始列来进行拆分。对于时间列，建议只保留需要的精度，比如将秒截断至分钟可以大大降低基数，但同时也牺牲了分析的精度，需要你根据实际情况取舍。
优化审计列：关系数据库中的表通常有用于审计目的的标准列，例如，时间戳和上次操作更新的用户。除非需要钻取，否则不应该在数据模型中导入这些列，如需钻取，考虑按照应用于日期和时间的相同规则分割时间戳。

根据经验，减少列的基数将节省内存并提高性能。但因为减少基数可能意味着丢失信息和准确性，所以在考虑这些优化建议时必须非常小心。

关系的基数 Cardinality of the Relationships

关系的基数是根据关系两侧的列是否具有重复值定义的，用于划分关系的类型。这个概念来自数据建模，在 DAX 中，关系有三种基数类型，它们在技术上和语义上都有一定的区别。在这里，我们不做深入讨论，只是阐述基数在这个语境下的含义：

一对多关系：这是最常见的基数类型。在关系的一端，列必须具有唯一值；在关系的多端，通常包含重复值。
一对一关系：这是一种相当少见的基数类型。关系两端的列都具有唯一值。
多对多关系：关系两端的列可以有多个重复值。这个功能是在 2018 年引入的，遗憾的是，它的名字有点令人困惑。在公共数据建模语言中，“多对多”指的是另一种不同的实现方式，它是使用一对多和多对一创建的关系。重要的是要理解，在这个场景中，many-to-many 不是指多对多关系，而是指关系中的多对多基数类型。