从数据到信息
从信息到洞察

主键、外键和索引

什么是主键

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

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

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 值时的更新失败提示

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

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

解决循环依赖问题

此处为隐藏内容 VIP会员和付费用户可见

发生上下文转换时的可见行

此处为隐藏内容 VIP会员和付费用户可见

此处为隐藏内容 VIP会员和付费用户可见

 

扩展阅读

  1. 维基百科:关系键
  2. 外键 – 廖雪峰的官方网站

下载面板

以上隐藏内容查看价格为3G 币,请先
注:加入 VIP 会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

15
说点什么

avatar
1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
6 评论数
9 被回复的评论
5 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
158****4138
成员
158****4138

对我的帮助很大,很感谢,请问有些案例有没有源文件呀

muxiaomi
成员
muxiaomi

联合主键 定义里最后一句似乎应该是 “只要[~~不是~~]构成联合主键的所有列的组合不重复即可”双重否定表示肯定

> 在介绍主键定义的时候提到过,除了可以使用单列作为主键,也可以组合多列作为主键,这种就是联合主键,联合主键允许一列有重复值,** 只要不是构成联合主键的所有列的组合不重复即可 **。

跑//
成员
跑//

请教!
如果:通过“行标识符”或通过维度表建立关系,均能达到设置主键的作用的话,怎么理解:
假设:1对多关系中,维度表中存在2列均唯一的字段,如果建立关系的字段1是主键的话,我在”表行为”中设置”行标识符”为字段2,那么此时哪个字段是主键呢?
维度表中的字段1是不是在功能上与主键相同,但字段2才是维度表的主键?

跑//
成员
跑//

pivot中通过表行为添加主键时,”保持唯一行”的作用是什么?

超哥
成员
超哥

谢谢!对我这样的初学者,基本数据管理的知识真是很有帮助!

dony
成员
dony

# 主键是什么

定义:就像是一个不重复的编号,可以作为识别一个数据的唯一标识。

举例:主键就是周星驰电影里的9527

## 注意事项

主键不要有业务相关功能,它的作用只有一个,就是唯一标识

主键尽量用系统函数生成

# 主键的类型

> 主键最好是由系统自动生成

## 自定主键

就是Excel当中的序号、索引

## GUID类主键

系统自动生成的一段唯一标识

## 联合主键

由多个字段构建出的不重复标识。

比如VLOOKUP的多条件查找时,合并两列数据,构建不重复标识。

# 其他主键技巧

外键,主键在多端中

其他看不懂的,还有:超键、候选键、自然键、代理键

# 主键注意事项

主键不能为空

## 避免循环依赖

没看懂,没有案例,没有场景,看不懂