高性能应用的数据库设计最佳实践

为了使应用程序具有良好的性能，您需要一个强大的应用程序服务器，足够的带宽以及良好的编程工作。但有一个方面通常被忽视，但通常对任何应用程序的性能产生很大影响：数据库设计。

现在我们来看一下数据库设计的最佳实践，以确保数据访问不成为影响应用程序性能的瓶颈。

好的数据库设计的目的是什么？

除了提高数据访问性能外，良好的设计还能实现其他好处，例如保持数据一致性、准确性和可靠性，通过消除冗余来减少存储空间。良好的设计的另一个好处是数据库更容易使用和维护。任何需要管理它的人只需查看实体-关系图（ERD）即可了解它的结构。

ERD是数据库设计的基本工具。它们可以在三个设计级别上创建和可视化：概念、逻辑和物理。

概念设计显示了一个非常概括的图表，只包含与项目相关方达成一致的必要元素，他们不需要了解数据库的技术细节。逻辑设计以数据库无关的方式详细展示实体及其关系。

有许多工具可以用于通过ERD方便地设计数据库。其中最好的工具包括DbSchema、SqlDBM和Vertabelo。

DbSchema

DbSchema可以帮助您以图形方式设计和管理SQL、NoSQL或云数据库。该工具允许您在计算机上设计模式，并将其部署到多个数据库中，并生成HTML5图表的文档，编写查询，并在可视化中探索数据，等等。它还提供模式同步、随机数据生成和带有自动完成的SQL代码编辑功能。

SqlDBM

SqlDBM是最好的数据库图表设计工具之一，因为它提供了一种在任何浏览器中设计数据库的简便方法。您不需要其他数据库引擎或建模工具即可使用它，尽管SqlDBM允许您从现有数据库导入模式。它非常适合团队协作，因为它允许您与同事共享设计项目。

Vertabelo

Vertabelo是一种在线的可视化数据库设计工具，可以逻辑地设计数据库并自动生成物理模式。它可以进行逆向工程，从现有数据库生成图表，并通过区分所有者、编辑和查看者的访问权限来控制对图表的访问。

最后，物理设计是在ERD中添加所有必要细节以将其转化为特定DBMS（如MySQL、MariaDB、MS SQL Server等）中可用的数据库。让我们来看一下在设计ERD时需要牢记的最佳实践，以确保生成的数据库能够发挥其最佳性能。

定义要设计的数据库类型

通常区分两种基本类型的数据库：关系型和维度型。

关系型数据库用于执行对数据进行事务处理的传统应用程序 – 也就是说，它们从数据库获取信息，对其进行处理，并存储结果。

另一方面，维度数据库用于创建数据仓库：用于数据分析和数据挖掘以获得洞察的大型信息存储库。

在任何数据库设计任务中，第一步是选择要使用的两种主要数据库类型之一：关系型或维度型。在开始设计之前，明确这一点非常重要。否则，您很容易陷入设计错误中，最终会导致许多问题，并且很难（或不可能）进行更正。

采用命名约定

数据库设计中使用的名称非常重要，因为一旦在数据库中创建了对象，更改其名称可能会导致灾难性后果。仅更改名称的一个字母就可能破坏依赖关系、关联关系甚至整个系统。

这就是为什么使用健全的命名约定至关重要：一组规则可以帮助您避免尝试50种不同的可能性来查找您记不住的对象名称的麻烦。

关于命名约定应该是什么以完成其工作，没有通用指南。但是重要的是在为数据库中的任何对象命名之前建立一个命名约定，并且始终保持这种约定不变。命名约定建立了诸如在单词之间使用下划线还是直接连接它们、是否使用全大写字母或大写首字母（驼峰命名法）、是否使用复数或单数词来命名对象等准则。

从概念设计开始，然后进行逻辑设计，最后进行物理设计。

这是事情的自然顺序。作为设计师，您可能会希望直接在DBMS上创建对象以跳过步骤。但是这将阻止您拥有与利益相关者讨论设计是否满足业务需求的工具。

在概念设计之后，您必须进入逻辑设计阶段，以获得适当的文档，以帮助程序员理解数据库结构。保持逻辑设计的更新对于独立于要使用的数据库引擎非常重要。这样，如果您最终将数据库迁移到不同的引擎上，逻辑设计仍将很有用。

最后，程序员自己或DBA可以根据逻辑设计创建物理设计，并添加实施所需的所有实现细节，以在特定的DBMS上实现它。

创建并维护数据字典

<p即使ERD清晰且描述详细，您也应该添加数据字典以使其更清晰。数据字典在数据库设计中保持一致性和一致性非常重要，特别是当其中的对象数量显著增加时。

数据字典的主要目的是维护有关数据模型和其属性的实体的参考信息的单一存储库。数据字典应包含所有实体的名称、所有属性的名称、其格式和数据类型以及每个属性的简要描述。

数据字典为构成数据库的所有元素提供了明确而简洁的指南。这避免了创建表示相同事物的多个对象，这样在需要查询或更新信息时很难知道要求助于哪个对象。

保持主键的一致性标准

在数据模型中使用自然键还是代理键的决定必须保持一致。如果数据模型中的实体具有可以有效管理为其各自表的主键的唯一标识符，则无需创建代理键。

但是，常见的情况是实体由不同类型的多个属性标识 – 日期、数字和/或长字符串 – 这可能对形成主键而言效率不高。在这种情况下，最好创建整数数值类型的代理键，这在索引管理中提供了最大的效率。而且，如果实体缺少唯一标识它的属性，那么代理键是唯一的选项。

为每个属性使用正确的数据类型。

某些数据给我们选择使用哪种数据类型来表示它们的选项。例如，日期。我们可以选择将它们存储在日期类型字段、日期/时间类型字段、varchar类型字段甚至是数值类型字段中。另一种情况是数值数据，它不用于数学运算，而是用于标识实体，例如驾驶执照号码或邮政编码。

在日期的情况下，使用引擎的数据类型是方便操作数据的。如果您只需要存储事件的日期而不指定时间，则应选择数据类型为Date；如果您需要存储某个事件发生的日期和时间，则数据类型应选择为DateTime。

在某些特殊情况下，使用其他类型（如varchar或numeric）来存储日期可能是方便的。例如，如果事先不知道日期将以哪种格式表示，将其存储为varchar是方便的。如果在处理日期类型字段时搜索性能、排序或索引是关键问题，则先将其转换为float可能会有所不同。

不涉及数学运算的数值数据应表示为varchar，并在记录中应用格式验证以避免不一致或重复。否则，您将面临某些数据超出数值字段限制并迫使您在已经生产中进行重构的风险。

使用查找表

一些经验不足的设计师可能认为过度使用查找表来规范化设计会不必要地复杂化数据库的ERD，因为它添加了许多“卫星”表，有时这些表中只有少数几个元素。那些认为如此的人应该理解，使用查找表的好处远远多于缺点。如果ERD的复杂性或大小是问题，可以使用ERD设计工具以不同的方式可视化图表，以便在复杂性的情况下仍然能够理解。

以下示例查询说明了在设计良好的数据库中正确使用查找表：

SELECT
	StreetName,
	StreetNumber,
	Cities.Name AS City,
	States.Name AS State
FROM
	Addresses
	INNER JOIN Cities ON
		Cities.CityId = Addresses.CityId
	INNER JOIN States ON
		States.StateId = Addresses.StateId

在此示例中，我们使用了Cities和States的查找表。

查找表的好处包括减小数据库的大小，improving search performance，并对字段可以包含的有效数据集施加限制，等等。对于所有查找表来说，还应该包含一个位或布尔字段，指示表中的记录是否正在使用或已过时。此字段可以用作过滤器，以避免过时元素作为应用程序UI中的选项。

根据数据库类型进行规范化或去规范化

在用于传统应用程序的关系型数据库中，规范化是必须的。众所周知，规范化通过避免冗余来减小所需的存储空间。它提高了信息的质量，并提供了多种工具来优化复杂查询的性能。

然而，在其他类型的数据库中，应用一种称为去规范化的技术。在数据仓库中使用的维度数据库中，去规范化在模式表中添加了某些有用的冗余信息。

尽管它们似乎是相反的概念，但去规范化并不意味着撤消规范化。实际上，它是一种应用于规范化的数据模型的优化技术，以简化查询编写和报告。

将物理模型分为多个部分进行设计

在软件开发项目中，数据库设计师向利益相关者展示了一个大规模的概念模型，其中不显示任何实现细节。反过来，为了与开发人员合作，设计师必须提供一个包含每个实体和属性的所有细节的物理模型。然而，这两个模型不需要在项目开始时完全创建。

在应用agile methodologies时，每个开发周期开始时的每个开发人员都会选择一个或多个用户故事进行处理。数据库设计师的工作是为每个开发人员提供一个只包含他们在工作单元中需要的对象的物理子模型。

在每个开发周期结束时，将在该周期创建的子模型合并，以便完整的物理模型与应用程序的开发同步进行。

充分利用视图和索引

视图和索引是数据库设计中改善应用程序性能的两个基本工具。使用视图可以处理抽象化，简化查询，隐藏不必要的表细节。而且，视图使得数据库引擎更容易进行查询优化任务，因为它们允许数据库引擎预测数据的获取方式，并选择最佳策略以更快地提供查询结果。

索引可以基于用户在数据库投入生产后的经验，改善缓慢查询的性能。然而，索引的创建可以作为数据库设计任务的一部分，在应用程序的需求之前进行。

要创建索引，您需要对每个表的大小有一个大致的概念 – 以记录计数为单位 – 然后为较大的表创建索引。选择要包含在索引中的字段，主要考虑表示外键的字段和将在搜索中用作过滤器的字段。

当您认为工作已经完成时，是时候进行重构了。

数据库设计总是可以改进的。当没有由于新需求或新的业务需求而对数据库进行更改时，这是一个改进设计的好机会。重构就是这样简单：引入改进设计的更改，而不影响数据库的语义。

有许多重构技术可以改进数据库设计，超出了本文的范围，但知道它们的存在并在需要时使用它们是很好的。

每当需要设计数据库时，拥有这个最佳实践清单将使您能够获得最佳结果，以便应用程序始终在数据访问方面保持optimal performance。