数据存储优化，SQL中的数据类型和规范化-51CTO.COM

在当今世纪，数据就是新的石油。优化数据存储对于获得良好的性能始终至关重要。选择合适的数据类型并应用正确的规范化过程对于决定其性能至关重要。

本文将研究最重要和最常用的数据类型，并了解规范化过程。

SQL中的数据类型

SQL中主要有两种数据类型：字符串和数字。除此之外，还有其他数据类型，如布尔型、日期和时间、数组、区间、XML等。

字符串数据类型

这些数据类型用于存储字符串。字符串通常作为数组数据类型实现，并包含一系列元素，通常是字符。

CHAR(n):

它是一个固定长度的字符串，可以包含字符、数字和特殊字符。n表示它可以容纳的字符串的最大长度（以字符为单位）。

它的最大范围是从0到255个字符，这种数据类型的问题是，即使实际字符串的长度小于指定的长度，它也会占用全部指定的空间。额外的字符串长度会用额外的内存空间填充。

VARCHAR(n):

Varchar与Char类似，但可以支持大小可变的字符串，并且没有填充。该数据类型的存储大小等于字符串的实际长度。

它最多可以存储65535个字符。由于其大小可变的特性，它的性能不如CHAR数据类型好。

BINARY(n):

它类似于CHAR数据类型，但只接受二进制字符串或二进制数据。它可以用于存储图像、文件或任何序列化对象。还有另一种数据类型VARBINARY(n)，它类似于VARCHAR数据类型，但也只接受二进制字符串或二进制数据。

TEXT(n):

这种数据类型也用于存储字符串，但最大大小为65535字节。

BLOB(n):

代表二进制大对象，可以容纳最多65535字节的数据。

除此之外，还有其他数据类型，如LONGTEXT和LONGBLOB，它们可以存储更多字符。

数字数据类型

INT():

它可以存储一个4字节（32位）的整数数字。这里的n表示显示宽度，最大可以达到255。它指定了用于显示整数值的最小字符数。

范围：

a） -2147483648<=Signed INT<=2147483647
b） 0<=Unsigned INT<=4294967295

BIGINT():

它可以存储一个大小为64位的大整数。

范围：

a） -9223372036854775808<=Signed BIGINT<=9223372036854775807
b） 0<=Unsigned BIGINT<=18446744073709551615

FLOAT():

它可以存储浮点数，其小数点位以一定精度近似。它存在一些小的舍入误差，因此在需要精确精度的情况下不适用。

DOUBLE():

这种数据类型表示双精度浮点数。与FLOAT数据类型相比，它可以存储具有更高精度的小数值。

DECIMAL(n, d):

该数据类型表示精确的十进制数，精度固定，用d表示。参数d指定小数点后的位数，参数n表示数字的大小。d的最大值为30，其默认值为0。

一些其他数据类型

BOOLEAN:

这种数据类型只存储True或False两种状态。它用于执行逻辑操作。

ENUM:

它代表枚举。它允许你从预定义选项列表中选择一个值。它还能确保存储的值仅来自指定的选项。

例如，考虑一个只能是“红色”、“绿色”或“蓝色”的属性颜色。当我们将这些值放入ENUM中时，颜色的值只能是这些指定的颜色之一。

XML：

XML代表可扩展标记语言（eXtensible Markup Language）。这种数据类型用于存储XML数据，XML数据用于结构化数据表示。

AutoNumber：

它是一个整数，当每条记录被添加时，它会自动递增其值。它用于生成唯一或连续的数字。

Hyperlink：

它可以存储文件和网页的超链接。

关于SQL数据类型的讨论到此为止。其他数据类型还有很多，但本文所讨论的是最常用的数据类型。

SQL中的规范化

规范化是从数据库中移除冗余、不一致和异常的过程。冗余表示相同数据的重复值存在，而数据库中的不一致表示相同数据以多种格式存在于多个表中。

数据库异常可以定义为数据库中不应存在的任何突然变化或不一致。这些变化可能是由于各种原因引起的，例如数据损坏、硬件故障、软件错误等。异常情况可能导致严重后果，如数据丢失或不一致，所以尽快检测和修复异常情况至关重要。主要有三种类型的异常情况。本文将简要讨论每种类型，但如果想了解更多信息，请参阅以下链接。

【异常情况】：https://www.geeksforgeeks.org/anomalies-in-relational-model/

插入异常：

当新插入的行在表中导致不一致时，就会发生插入异常。例如，我们想要将一个员工添加到组织中，但是他的部门没有分配给他。那么我们就无法将该员工添加到表中，这就产生了一个插入异常。

删除异常：

当我们想要从表中删除某些行，并且还需要删除数据库中的其他数据时，就会发生删除异常。

更新异常：

当我们想要更新某些行并导致数据库的数据不一致时，就会发生这种异常。

规范化过程包含一系列准则，可使数据库设计高效、优化，并且不含冗余和异常。有几种常见的规范化形式，如第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、BCNF等。

1. 第一范式（1NF）

第一范式确保表中不包含复合或多值属性。这意味着单个属性中只有一个值。如果每个属性都是单值的，那么关系就符合第一范式。

例如：

图片

在表1中，属性STUD_PHONE包含多个电话号码。但是在表2中，该属性被分解为第一范式。

2. 第二范式（2NF）

表格必须符合第一范式，并且关系中不能存在任何部分依赖关系。部分依赖意味着非主属性（不是候选键的一部分的属性）部分依赖于候选键的任何一个真子集。为了使关系符合第二范式，非主属性必须完全依赖于整个候选键。

例如，考虑一个名为Employee的表，具有以下属性：

EmployeeID (Primary Key)
ProjectID (Primary Key)
EmployeeName
ProjectName
HoursWorked

在这里，EmployeeID和ProjectID共同构成主键。不过，你可以注意到EmployeeName和EmployeeID之间存在部分依赖关系。这意味着EmployeeName只依赖于主键的一部分（即EmployeeID）。要实现完全依赖，EmployeeName必须同时依赖于EmployeeID和ProjectID。因此，这违反了第二范式的原则。

为了使这种关系符合第二范式，我们必须将表拆分成两个独立的表。第一个表包含所有雇员的详细信息，第二个表包含所有项目的详细信息。

因此，Employee表具有以下属性：

EmployeeID (Primary Key)
EmployeeName

而Project表具有以下属性：

Project ID (Primary Key)
Project Name
Hours Worked

现在可以看到，通过创建两个独立的表，部分依赖关系已经被消除。而且两个表的非主属性依赖于完整的主键集合。

3. 第三范式（3NF）

在第二范式之后，关系仍然可能存在更新异常。如果我们只更新了一个元组而不更新其他元组，就会出现这种情况。这将导致数据库的不一致性。

第三范式的条件是表应该符合第二范式，并且非主属性不存在传递依赖关系。传递依赖发生在非主属性不直接依赖于主属性，而是依赖于另一个非主属性的情况下。主属性是候选键的一部分。

考虑一个关系R（A，B，C），其中A是主键，B和C是非主属性。假设A→B和B→C是两个函数依赖关系，那么A→C就是传递依赖关系。这意味着属性C不是由属性A直接确定的。B在它们之间起中间人的作用。

如果一个表存在传递依赖关系，那么我们可以通过将表拆分为独立的关系来将其转化为第三范式。

4.Boyce-Codd范式

尽管第二范式和第三范式消除了大部分冗余，但仍然没有完全消除冗余。如果函数依赖关系的左侧不是候选键或超键，就可能存在冗余。候选键由主属性形成，超级键是候选键的超集。为了解决这个问题，还存在另一种类型的函数依赖关系，称为Boyce-Codd范式（BCNF）。

对于一个表来说，要达到BCNF，函数依赖关系的左侧必须是候选键或超键。例如，对于一个函数依赖关系X→Y，X必须是候选键或超键。

考虑一个包含以下属性的Employee表：

员工ID（主键）
员工姓名
部门
部门负责人

EmployeeID是唯一标识每一行的主键。Department属性表示特定员工所在的部门，而Department Head属性表示担任该特定部门负责人的员工的EmployeeID。

现在，我们将检查这个表是否符合BCNF。条件是函数依赖关系的左侧必须是超键。下面是该表的两个函数依赖关系。

函数依赖关系1：员工ID→员工姓名，部门，部门负责人
函数依赖关系2：部门→部门负责人

对于FD1，员工ID是主键，也是超键。但对于FD2，部门不是超键，因为多个员工可能属于同一个部门。

因此，这个表违反了BCNF的条件。为了满足BCNF的属性，我们需要将该表拆分为两个独立的表：Employee表和Department表。Employee表包含员工ID、员工姓名和部门，而Department表则包含部门和部门负责人。

图片

现在我们可以看到，在这两个表中，所有的函数依赖关系都依赖于主键，即不存在非三维依赖关系。

我们已经介绍了所有知名的规范化技术，但除此之外，还有两种更高级的范式，分别是第四范式（4NF）和第五范式（5NF）。如果想了解更多信息，可以参阅以下链接。

【链接】：https://www.geeksforgeeks.org/difference-between-4nf-and-5nf/

总结

本文讨论了SQL中最常用的数据类型以及数据库管理系统中重要的规范化技术。在设计数据库系统时，我们的目标是使其具有可扩展性，最小化冗余并确保数据完整性。

通过选择适当的数据类型，我们可以在存储、精度和内存消耗之间取得微妙的平衡。同时，规范化过程有助于消除数据异常并使数据库模式更有组织性。