我正在与我工作的公司的开发人员进行长期讨论,因为他们说最好摆脱关系数据库中的关系强制(通过 FOREIGN KEY 约束定义),以加快大型查询并获得更好的结果表现。
正在考虑的平台是 MySQL 5.x,没有设置 FOREIGN KEY,甚至相关表的一些 PRIMARY KEY 约束都丢失了,至少对我来说是不合理的。也许他们是对的,我是错的,但我没有足够的论据来讨论这种情况。
三年来,这一直是首选方法。我是这家公司的新人(只有一个月),但由于产品“有效”,因此对增强数据库犹豫不决;尽管如此,我注意到的第一件事是加载一个页面需要 1 分钟(是的,60 秒!)。
当前状态背后的主张之一是“非规范化”数据库比规范化数据库更快,但我不认为这是真的。
大多数相关查询都包含 JOIN 操作,这使得它们在处理大量数据(数据库包含数百万行)时运行非常、非常、非常慢。
通常,“CRUD”操作的处理是在应用程序代码级别实现的;例如,为了删除一些数据 FROM,比方说TableA
:
- 有必要首先动态检查和的行之间是否存在某种关系,
TableA
TableB
- 如果“检测到”所述关系,则应用程序代码将不允许删除相关行,但是
- 如果由于某种原因应用程序代码失败,则删除操作将“成功”,无论涉及的行和表是否有任何关系。
问题
您能帮我详细阐述一个好的、准确和可靠的答案来丰富辩论吗?
注意:也许以前有人问过(并回答过)这样的问题,但我通过谷歌找不到任何东西。
如果,如您的帖子中所述,其目的是创建一个关系数据库(为简洁起见,RDB),因此,预计它的功能是这样的,简短的回答是:
主要目标应该是按原样管理相关数据:一项非常有价值的组织资产,实现上述目标的可靠方式是采用基于合理理论的技术手段。
因此,作为一名数据库专业人士,您可以利用EF Codd 博士提供的最先进和优雅的关系模型机制来执行业务规则,并避免最终会出现的(技术和组织)问题,如果它们没有被利用。
在这方面,我将分享(a)我对约束的总体看法,以及(b)关于数据库事务状态和有争议的工作环境的几个考虑如下。
FOREIGN KEY 约束、数据关系和参照完整性
RDB 必须高度准确地反映感兴趣的业务上下文的特征,这肯定需要由遵循最佳实践的建模师或设计师领导的深入的概念级别分析,并在业务专家不可或缺的帮助下进行。该分析必须产生正确的识别并制定适用的业务规则。
因此,如果这样的建模者已经识别出相关数据之间存在相互关系,他或她必须配置相应的逻辑级限制,以便数据库管理系统(DBMS)可以保证数据与确切的特征保持一致,并且上述 分析 中 确定 的规则.
关于正在讨论的数据库,可以推断出相关的相互关系已经确定,因为您提到有一种程序(并且易于规避)尝试通过应用程序代码(其中是一种前关系方法)在任何情况下都必须“触摸”数据库以尝试验证所述相互关系的完整性。
但是,如您所知,这不是保护参照完整性的最佳技术,因为关系科学为此目的规定了一种非常强大的工具,即外键 (FK) 约束。这些约束很容易创建(通过优越的声明性方法),因为它们是单个句子,可以避免诉诸不必要且容易出错的临时程序。值得注意的是,FK 约束的执行速度已经被专业的程序员高度优化(并且主要平台供应商已经为此工作了几十年)。
此外,由于 RDB 必须是一个独立的(自我保护、自我描述等)软件组件,能够被多个应用程序(桌面、自动、Web、移动、它们的组合)访问,因此它不应该是与任何这些应用程序的代码“耦合”。
同样,作为重要的组织资源的数据自然往往比应用程序、应用程序程序员、应用程序开发平台和编程范式更长寿。
PRIMARY KEY 约束和重复行的含义
当——从概念上讲——一种特定的事物在商业环境中被认为是重要的,数据库建模者必须(1)确定它的相关特征——即它的属性——,确认这种事物作为实体实例原型——即,实体类型 - 并且 (2) 通过由逻辑设计中的一个或多个列集成的表来表示它。
然后,就像在现实世界的业务中区分给定实体类型的每个单独实例至关重要一样,表中包含的每个对应行也必须唯一区分。如果一个表没有声明任何 KEY,它最终会保留重复,如果有两行或多行保留完全相同的值,那么它们都具有相同的含义,它们都代表相同的事实。
在这一点上,由于多种原因,应该丢弃重复的行。从理论的角度来看,设计者必须确保每一行始终是唯一的,以使表在 SQL 数据子语言允许的范围内工作(对数据操作操作有重要影响)。此外,从信息的角度来看,如果多行代表同一个事实,记录它们不仅是多余的,而且是有害的,如下所示:
这样:
如您所知,这种现象甚至会产生法律影响,而这种情况肯定是非常重要的。
此外,处理此类矛盾(可能通过某种“更新同步”)所必须花费的时间和精力应该更好地投入到真正为您的组织创造价值的任务上。因此,应该通过设计来避免保留矛盾的行,以保持数据库的一致性。
这就是为什么主键 (PK) 的标识和相应约束的声明应始终由数据库设计者执行。但也必须提到的是,一个表可能有多个列或列组合,这些列包含唯一标识每一行的值;因此,除了设置一个 PK 约束(由于实用原因理想地设置为 PRIMARY)外,设计人员还必须在应用时声明一个或多个 ALTERNATE KEY(通常通过一个或多个 UNIQUE 加上 NOT NULL 约束定义)(即很常见)。
PK 的另一个优点是,当“迁移”到其他表以参与单个或复合 FK 时,它们可以帮助强制执行数据之间存在的关系/关联的基数比。所有这一切,是的,通过简单而有效的声明性设置,由 DBMS 始终确保。
(当前)CHECK 约束和单行验证
让我们不要忘记(当前)CHECK 约束的相关性,它以声明方式限制行的有效列值集(这可能看起来很简单,但实际上是关系 DBMS 的基本特征),也有助于确保业务上下文的规则始终精确地反映。
当您用 MySQL 标记标记您的问题时,从版本8.0.16(另请参阅此 MySQL 服务器团队博客文章)开始,终于有了这样一个平台!强制执行这种类型的约束。在这方面,必须提到的是,在所有以前的版本中,这个 DBMS 确实允许声明 CHECK 限制,但忽略了它的强制执行!,可以理解的是,自 2004 年以来,这种情况被报告为错误。
因此,如果使用旧版本,则必须通过其他方式处理此因素,例如ACID TRANSACTIONS、 TRIGGERS 或 DBMS 本身中的其他方法(有关此主题的信息,请参阅@ypercubeᵀᴹ 的此答案),以便数据继续保持一致。
ASSERTION 约束:以声明方式设置进一步的多行和多表业务规则
无论出于何种原因,包括 MySQL 在内的不同 SQL DBMS 都很难支持(如果有的话)的一个方面是以声明的方式启用多行和多表约束(显然,超越了 PK 和 FK)。
就其本身而言,SQL 标准已经包含了多年以来的断言。我不知道您的业务环境的哪些规则会从这种逻辑级验证方法中受益,但作为数据库设计人员,我认为在需要时使用一个或多个 ASSERTION 来约束数据会非常方便,尽管我不得不提一下从 DBMS 开发人员的角度来看,这种最重要的工具很难在物理抽象级别上实现。
自 2016 年以来,Oracle 供应商和/或开发人员似乎正在评估ASSERTION 支持,这将使 DBMS 更加符合关系,因此更加健壮和具有竞争力。我猜想,如果 (i) 他们的消费者继续推动并且 (ii) Oracle 成功实施,那么 (iii) 其他 DBMS 供应商/社区也将不得不启用它们,并且它们的使用将开始传播。当然,这将是数据管理领域的巨大进步,并且作为 Codd 博士设想的最独特的工具之一,我个人希望我们能很快看到这种情况发生。
数据一致性和决策过程
如上所述,RDB 最重要的方面之一是它自己保证它保留的数据的一致性,并且只有当 RDB 符合建模者声明的完整性约束时才能满足所述一致性。
在这方面,必须拥有完整性受到保护的基表(在 DDL 结构中建立的那些),以便能够创建可信赖的派生表(例如,从多个表中检索列的 SELECT 语句或视图),因为必须根据基表生成派生表。
很明显,人们在组织(和普通)决策过程中使用信息作为主要工具。那么,如果数据库提供的信息不连贯且不准确,那么基于此类信息的决策将是不合理的(至少可以这么说)。这就是为什么必须仔细设计和实施 RDB:它应该被构建成一种可靠的资源,可以帮助其用户做出有根据的决策。
“非规范化”
唉,“‘非规范化’的数据库比规范化的数据库更快”是一个广泛传播的误解,尽管它也是一个可以在逻辑、物理和实用的基础上反驳的“论据”。
首先,非规范化必然意味着基表先前已经被规范化(凭借在数据库的逻辑抽象级别上实现的正式的、基于科学的过程)。
因此,假设该表实际上已正确规范化,则将其“非规范化”(与该词的正式含义相反,涉及向其附加属于广告中其他表的列,并且也是其一部分hoc方式)可能有助于,例如,加速(在物理级别)仅处理一个或几个特定的 SELECT 语句,而同时这样的操作过程可能会破坏许多其他相关数据的执行操作操作(例如,几个 INSERT、UPDATE、DELETE 和 SELECT 语句,或其组合包含在单个或多个 ACID TRANSACTIONS 中)。
此外,非规范化(无论是正式的还是非正式的)会引入更新/修改异常,从而恶化数据库的一致性,这个问题“可能”由复杂、昂贵且容易出错的过程来处理,而所有这些都可以避免一开始。
支持规范化和“非规范化”表的物理级脚手架
旨在在现实世界中使用的逻辑(抽象)布局(SQL-DDL 设计)显然包含必须考虑的物理(具体)影响。
以这种方式,“非规范化”表必然会“更宽”(包含额外的列),这意味着它的行必然会更重(需要更多更大的物理级组件),这意味着底层计算过程(例如,那些与硬盘驱动器或内存有关的)很容易变慢。
相比之下,当然“更窄”(列更少)的规范化表将是“表现更快”的“更轻”元素(由更少和更小的物理组件提供服务),这将加速与,例如,数据写入和读取。
既然如此,很方便(a)正式和谨慎地规范化相关表,保持它们原样,然后(b)利用任何可以优化数据检索和修改速度的物理级资源,例如,实现谨慎高效的索引策略,启用适当的软件和硬件服务器配置,升级网络带宽能力等。
正在考虑并与您的同事联系的数据库的功能
您问题的以下段落与数据检索操作的速度有关:
如果加载某个页面需要那么长时间,那么系统的用户显然没有得到适当的服务;因此,即使它“有效”,它的功能似乎也根本不是最佳的,这表明您使整个计算机化信息系统(数据库和应用程序)更高效的意图得到了很好的维持,并表现出非常建设性的态度.
然后,即使科学肯定支持你,因此你应该保持坚定的姿态,我建议以外交方式处理这种情况,因为归根结底,你的雇主、同事和你自己都在共同努力,以使整个组织更成功。因此,这是您应该强调的一个论点,即尽管他们在其他方面做得非常好,但改进一般和特定的数据管理实践可以大大有助于产生更多的组织和个人成长。
值得注意的是,JOIN 运算符是与数据的关系操作相关的重要且强大的元素。然后,尽管更强大的平台以相对更快的执行速度为其提供服务,但您描述的情况很可能是设计效率低下的症状(在抽象的概念、逻辑和物理级别)。所以,我的第一眼估计是:
此外,是的,正如@TommCatt在他的回答中提到的那样,有时查询的(逻辑)重写会修改其(物理)执行计划,从而加速数据读取/写入,这是一个应该明确考虑的因素。
您的开发人员的基本前提是绝对错误的。外键会稍微影响系统 DML 的性能。它们根本不在查询中使用,因此对它们的性能没有影响。因此,您的开发人员不知道他们在说什么,并且是您最不应该考虑听取建议的人。
外键在维护数据完整性方面发挥着关键作用。这比通过删除它们获得的任何微小的性能改进都重要得多(即使这是真的)。
在任何情况下都不要从 OLTP 数据库中删除 FK。
此外,非规范化有时会加速某些查询。正如他们所说,这取决于。尽管如此,即使速度有所提高,通常也不值得为维护数据完整性付出额外的努力。
当简单的调整不能比非规范化更快地提高速度时,这是非常罕见的。这就是优秀的 DBA 可以(最终)获得报酬的地方。您还可以调整您的查询。我曾经接受过一个查询,该查询在不少于 30 分钟内返回了答案,并在 8 秒内完成了工作。没有更改数据库,只是重写了查询。当然,这是我个人最好的记录,所以你的里程可能会有所不同,但非规范化应该是你尝试的最后一件事。
您可能还希望阻止开发人员编写更复杂的查询。询问他们想要什么数据以及他们想要的格式。然后提供视图以将其提供给他们。复杂的查询将是视图。然后,开发人员只需编写:
我还假设您的数据库设计得很好。糟糕的数据库设计,甚至是其中的一小部分,都会真正减慢速度。我经常使用非常大的表(每个表有数十亿条记录),查询将它们左右连接在一起,并在几分之一秒内预期(并得到)答案。表的大小不是查询速度的决定性因素。
当有人说“因为产品‘有效’而对增强数据库犹豫不决”时,我真的感到畏缩。如果这种“犹豫”更像是“不在我的手表上,伙计!” 那么您甚至可能想开始更新您的简历。这样的环境永远不会产生任何好处,即使您可能已经游说了几个小时以做出可以防止失败的改变,但您将因未来的每一次失败而受到指责。你会一遍又一遍地听到“现在不是做出改变的好时机”。正确的。祝你好运。
更改标题会更改问题。
FOREIGN KEYs
是可选的。他们是这样:INDEX
。可以手动添加这样的索引。(所以这不需要FK 。)CHECK
选项不被执行。CASCADE
做事。(就个人而言,我更喜欢保持控制,而不是假设 FK 会“做正确的事”。)FK 的底线:有些人坚持 FK;一些产品没有它们也能活得很好。你决定。
在 InnoDB 中删除
PRIMARY KEY
是一个很大的错误。另一方面,摆脱代理AUTO_INCREMENT
并使用由一个(或多个)列组成的“自然”PK 通常是正确的做法。一个简单、常见的情况是多对多映射表,如这里所讨论的。根据个人经验,我建议 2/3 的表最好使用“自然”而不是 auto_inc PK。