主键、外键和索引

显示全部楼层 · 2021-2-21 19:37:08

什么是主键

Power BI 使用的表格模型是一种基于关系型数据库的模型，表和表之间通过关系链接，因此表格模型需要遵循关系型数据库的一些基本约束，其中主键约束是最重要的一种约束。它基于这样一种考虑：数据表中任意两条记录不能完全相同，存在某个字段能唯一区分出不同的记录，这个字段被称为主键。

主键是每条记录的唯一标识，只要找到记录的标识就能调出关于这条记录的所有信息。

Product 表的主键 ProductKey

主键保证了数据的唯一性，以 Power BI 最常见的一对多关系为例，关系从一端指向多端，其中一端的表用来创建关系的列就具备主键的作用。

产品表的 ProductKey 列就是它的主键

位于关系一端的表（维度表）必须至少有一列可以作为表的唯一标识，否则一对多关系就无法建立。想象一下，如果维度表的 A，B 两个产品 ProductKey 都是 001，假设一对多关系仍然成立，模型在计算的时候就无法确定 Sales 表中所有 001 产品的销售记录到底对应的是 A 产品还是 B 产品，这在一对多关系中是不被允许的。

Power BI 允许创建多对多关系，在这种特殊情况下，用于创建关系的两列可以有重复值，关于多对多关系我们将在后面的文章中详细介绍

主键的选择

数据库对主键的要求中最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

由于主键的作用十分重要，如何选取主键会对模型结构产生重要影响。如果我们以用户的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。从存储成本的角度考虑，身份证可以作为 13 亿人的唯一身份标识，如果一家公司用它来做主键，未免有点浪费存储空间。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，严格来讲均不可用作主键。可用做主键的类型有：

自增整数类型：为表的每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
全局唯一 GUID 类型：使用一种全局唯一的字符串作为主键，类似 8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID 算法通过网卡 MAC 地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了 GUID 算法，可以自己预算出主键。

以上给出的是主键的严谨用法，在大型的、需要长期维护的 Power BI 模型中建议使用严谨的方式定义主键，以避免后期出现问题对报告产生严重影响。反之，如果是在数据量有限、且对主键字段有充分了解的情况下，可以不必严格遵守以上建议

联合主键

除了可以使用单列作为主键，也可以组合多列作为主键，这种就是联合主键，联合主键允许一列有重复值，只要构成联合主键的所有列的组合不重复即可。

Sales 表的 ProductKey 和 Order Number 可以起到联合主键的效果

比如 Sales 表中本身存在 OnlineSalesKey 这一主键，但是用 ProductKey 和 Order Number 也可以作为联合主键，区分唯一记录。

Power BI 模型不支持联合主键，但是编辑查询的合并查询功能在 Join 的时候可以设置多列进行匹配

如何在表格模型中手动设置主键

Excel Power Pivot 和 SSAS 都支持对任意表设置主键，Power BI Desktop 目前只支持对日期表设置主键，不过你可以通过另外一种方式创建主键，在下文解决循环依赖问题时会提到。

Power Pivot：模型视图 – 高级 – 表行为 – 行标识符

Power BI Desktop：建模 – 标记为日期表

SSDT:属性设置 – Row Identifier

Power Pivot 的表行为设置中，行标识符设置起到主键的作用，保持唯一行的作用是将列中的重复值也视为不同记录，此设置只在 PowerView 中生效，无法在透视表中重现

外键

一对多关系中位于关系多端的列就是外键，外键可以包含重复值。第一张图 Sales 表包含的 ProductKey 就是 Product 表的外键。通过设置关系数据库的外键约束，可以控制存储在外键表中的数据，使其不能插入主键表中不存在的记录。

外键可以理解为一种参照（Reference）,通过外键你可以查到主键对应的记录在关系的多端是如何分布的

外键

Power BI 不使用外键约束，如果事实表中存在于维表不匹配的记录，基于参照完整性，图表会自动显示一行空值以匹配这个不存在的记录。

数据库中的其他键

超键(Super Key): 在关系中能唯一标识元组的属性集称为关系模式的超键
候选键(Candidate Key): 不含有多余属性的超键称为候选键，也叫最小超键。通常我们会从候选键中选择一个作为主键。
代理键（Surrogate Key）：当数据表中的候选键都不适合当主键时，例如数据太长，或是意义层面太多，就会请一个无意义的但唯一的字段来代为作主键。在实践中，代理键值通常是个自动递增的数字。
自然键（Natural Key）：与代理键相反，是在业务逻辑中唯一确定一个事物的标识。身份证号（理论上，假设没有因技术原因造成的重复）就是一个自然键，用于确定一个人。

候选键是超键的子集，主键是候选键的子集

索引

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

表格模型的 Vertipaq 引擎在读取数据的时候会自动为每列添加索引，在发生计算时，引擎通过扫描索引可以快速定位数据，保证了 DAX 的计算可以高效完成，关于这部分内容在 Vertipaq 引擎一章会做详细介绍。

字典编码过程会为每一列创建字典和索引

字典编码使用的索引在模型中是不可见的，无法在公式中调用，如果你想显式引用索引，编辑查询中提供了自动创建索引的功能，你也可以预先在数据库中生成索引，或者使用 DAX 在计算列中生成索引。

使用主键时的注意事项
操作不当引发的报错

主键不能包含 null 值，否则关系无法创建，如果已创建的关系在后续更新时混入了 null 值，会使整个更新失败。

包含 null 值时的更新失败提示

一对多关系的主键不能包含重复值，否则关系无法创建，如果已创建的关系在后续更新时混入了重复值，会使整个更新失败。

包含重复值的更新失败提示

解决循环依赖问题
循环依赖是创建计算列时经常遇到的一类问题，简单的说：使用了 CALCULATE 的公式将当前行上下文转换为筛选上下文，如果你没有定义当前表与其他表的关系，也没有为它设置主键，当 CALCULATE 进行上下文转换时，它会筛选表的所有列，此时任何添加到数据模型中的计算列都成为由 CALCULATE 引入的筛选上下文的一部分，被添加到依赖列表中，循环依赖就产生了。

理解循环依赖并不容易，但是找到解决方案很简单，如果表中使用了行标识符（也就是本文的主键），调用 CALCULATE 的所有计算列将仅依赖于该行标识符，从而将依赖列表的数目降低到单列。

例如在产品表中，有一列可以唯一地标识出每一行，即 ProductKey。要将 ProductKey 标记为行标识符（主键），你有两种选择：

可以使用 ProductKey 作为目标列，来创建任意表和产品表之间的关系。执行此操作将确保 ProductKey 列可以唯一区分产品表。
使用上文介绍的表行为属性设置将 ProductKey 列设为行标识符

以上任一操作都会使得 DAX 知道该表中存在行标识符，使用了 CALCULATE 的计算列都只依赖于新设置的主键列。在稍后的理解循环依赖一文中，你将详细了解这种行为
发生上下文转换时的可见行

上下文转换将行上下文转换为等效的筛选上下文，行上下文总是包含单个行，而转换后 CALCULATE 创建的筛选上下文可能包含多个行，可能影响一列或多列，这取决于表结构。

如果模型中的表定义了主键，那么 CALCULATE 就会创建一个只过滤主键的筛选上下文。实际上，这样的筛选上下文包含一行，由主键唯一标识。上文已经介绍了两种标记主键的方式。在这两种情况下，上下文转换都只筛选单个列，又由于主键列是表的唯一标识，因此只过滤单个行。

如果表没有主键，那么上下文转换将在表的所有列上创建一个筛选器。这可能导致筛选器包含一个或多个行，具体取决于表内容。事实上，如果表中所有行都不同，那么筛选上下文可以惟一标识行。但是，如果表中有相同的行，那么所有相同行都将包含在筛选上下文中。
在下面的示例中，[Wrong Sales] 和 [Correct Sales] 返回不同的值：