Mysql--实战篇--数据库设计（范式和反范式，数据表设计原则）

标签：范式 范式化 -- 主键 数据表 INT id

一、范式和反范式

在数据库设计中，范式（Normalization）和反范式（Denormalization）是两种不同的设计理念，它们分别用于优化数据库的结构以满足不同的需求。范式主要用于减少数据冗余和提高数据完整性，而反范式则通过引入冗余来优化查询性能。

1、范式（Normalization）

范式是一种数据库设计方法，旨在通过消除重复数据和冗余信息，确保数据的一致性和完整性。范式通过将数据分解为多个表，并建立关系（如外键），来减少数据冗余和避免更新异常。

2、范式的优缺点

（1）、优点

• 减少数据冗余：通过将数据分散到多个表中，避免了重复存储相同的信息。
• 提高数据完整性：范式化的设计减少了插入、更新和删除操作中的异常，确保数据的一致性。
• 易于维护：由于数据被规范化为多个表，修改或更新数据变得更加简单和安全。
• 符合ACID属性：范式化的设计有助于确保数据库事务的原子性、一致性、隔离性和持久性（ACID）。

（2）、缺点

• 查询复杂度增加：由于数据被分散到多个表中，查询时需要进行大量的JOIN操作，导致查询性能下降。
• 维护成本增加：范式化的设计要求开发者编写更复杂的SQL查询，并且在数据更新时需要维护多个表之间的关系。
• 不适合高并发读取场景：在高并发读取的场景下，频繁的JOIN操作可能会成为性能瓶颈。

3、范式级别分类

（1）、第一范式（1NF）

第一范式要求每个表中的每一列都是不可再分的基本数据项，即每个字段只能包含单一值。此外，每个表必须有唯一的主键，确保每条记录的唯一性。

目标：

• 消除重复组，确保每个字段都是原子的。
• 确保每个表都有唯一的主键。

示例：
假设我们有一个未范式化的表Orders，其中包含客户的订单信息。每个订单可能包含多个产品，且这些产品信息直接存储在同一行中：
未范式化sql示例：

-- 未范式化的Orders表
CREATE TABLE Orders (
    order_id INT,
    customer_name VARCHAR(100),
    products VARCHAR(255)  -- 产品列表，如"Product A, Product B, Product C"
);

解释：
在这个表中，products列是一个非原子字段，因为它存储了多个产品的信息。这违反了1NF的要求。

1NF规范化后的表结构：
为了满足 1NF，需要将products列拆分为单独的行，确保每个字段只包含单一值。我们可以创建一个新的表OrderItems来存储每个订单的详细产品信息。

-- 1NF 规范化后的表结构
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);
CREATE TABLE OrderItems (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),  - 产品"Product A"或"Product B"
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

现在，OrderItems表中的每一行都只包含一个产品的信息，符合1NF的要求。

（2）、第二范式（2NF）

第二范式要求在满足1NF的基础上，消除非主属性对部分主键的依赖。换句话说，所有非主属性必须完全依赖于整个主键，而不能依赖于主键的一部分。

目标：

• 消除非主属性对部分主键的依赖。
• 确保所有非主属性完全依赖于整个主键。

示例：
假设我们有一个复合主键的表OrderDetails，其中order_id和product_id组成联合主键，存储订单和产品的详细信息。
未范式化sql示例：

-- 未范式化的OrderDetails表
CREATE TABLE OrderDetails (
    order_id INT,               -- 订单id
    product_id INT,             -- 产品id
    product_name VARCHAR(100),  -- 产品名称，仅依赖于product_id
    quantity INT,               -- 数量，依赖于 (order_id, product_id)
    price DECIMAL(10, 2),       -- 产品价格，仅依赖于product_id
    PRIMARY KEY (order_id, product_id)
);

在这个表中，product_name和price只依赖于product_id，而不是整个主键(order_id, product_id)。这违反了2NF的要求。

2NF规范化后的表结构：
为了满足2NF，我们需要将与product_id相关的字段（如product_name和price）移动到一个独立的Products表中，而OrderDetails表只保留与订单相关的字段。

-- 2NF 规范化后的表结构
CREATE TABLE Products (
    product_id INT PRIMARY KEY,     # 产品id
    product_name VARCHAR(100),      # 产品名称
    price DECIMAL(10, 2)            # 产品价格
);

CREATE TABLE OrderDetails (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,        # 订单详情id
    order_id INT,                         # 订单id
    product_id INT,                       # 产品id
    quantity INT,                         # 数量
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

解释：
这样OrderDetails表中的所有非主属性（如quantity）都完全依赖于整个主键(order_id, product_id)，符合2NF的要求。

（3）、第三范式（3NF）

第三范式要求在满足2NF的基础上，消除传递依赖。也就是说，所有非主属性不能依赖于其他非主属性，而只能依赖于主键。

目标：

• 消除传递依赖，确保所有非主属性只依赖于主键。
• 避免冗余数据和更新异常。

示例：
假设我们有一个表Employees，其中存储员工的信息，包括他们的部门名称：

未范式化sql示例：

-- 未范式化的Employees表
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,    -- 员工id，主键
    employee_name VARCHAR(100),     -- 员工名称
    department_name VARCHAR(100),   -- 部门名称，依赖于department_id
    department_id INT               -- 员工部门id
);

解释：
在这个表中，department_name依赖于department_id，department_id依赖于主键id。这意味着department_name是通过department_id间接依赖于主键的，属于典型的传递依赖，这违反了3NF的要求。

规范化后的表结构：
为了满足3NF，我们需要将与department_id相关的字段（如department_name）移动到一个独立的Departments表中，而Employees表只保留与员工相关的字段：

-- 3NF规范化后的表结构
CREATE TABLE Departments (
    department_id INT PRIMARY KEY,   -- 部门id
    department_name VARCHAR(100)     -- 部门名称
);

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,     -- 员工id
    employee_name VARCHAR(100),      -- 员工名称
    department_id INT,               -- 员工部门id
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

现在，Employees表中的所有非主属性（如employee_name）都只依赖于主键employee_id，而department_name依赖于department_id，符合3NF的要求。

（4）、BCNF（Boyce-Codd Normal Form）

BCNF是第三范式的加强版，要求在满足3NF的基础上，消除任何非平凡的函数依赖。具体来说，BCNF要求每个决定因素都是候选键。

目标：

• 消除任何非平凡的函数依赖，确保每个决定因素都是候选键。
• 进一步减少冗余和更新异常。

示例：
假设我们有一个表Courses，其中存储课程和教师的信息，且每个课程只能由一位教师教授，每位教师也只能教授一门课程。换句话说就是该表中，课程id不会存在重复，教师id也不会存在重复。

未范式化sql示例：

-- 未范式化的 Courses 表
CREATE TABLE Courses (
    course_id INT PRIMARY KEY,     -- 课程id
    course_name VARCHAR(100),      -- 课程名称
    teacher_id INT,                -- 教师id
    teacher_name VARCHAR(100)      -- 教师名称
);

解释：
在这个表中，course_id和teacher_id都可以唯一确定一条记录，因此它们都是候选键。然而，teacher_name依赖于teacher_id，而不是course_id，这违反了BCNF的要求。

规范化后的表结构：
为了满足BCNF，我们需要将与teacher_id相关的字段（如teacher_name）移动到一个独立的Teachers表中，而Courses表只保留与课程相关的字段。

-- BCNF 规范化后的表结构
CREATE TABLE Teachers (
    teacher_id INT PRIMARY KEY,
    teacher_name VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id)
);

现在，Courses表中的所有非主属性（如course_name）都只依赖于主键course_id，而teacher_name依赖于teacher_id，符合BCNF的要求。

（5）、第四范式（4NF）

第四范式要求在满足BCNF的基础上，消除多值依赖。多值依赖是指一个属性可以有多个值，且这些值之间没有直接的关系。

目标：

• 消除多值依赖，确保表中没有多值属性。
• 避免冗余数据和更新异常。

示例：
假设我们有一个表Employees，其中存储员工的技能信息。每个员工可以拥有多个技能，且这些技能之间没有直接的关系：

未范式化sql示例：

-- 未范式化的Employees表
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,   -- 员工id
    employee_name VARCHAR(100),    -- 员工名称
    skill1 VARCHAR(50),           -- 该员工的技术1
    skill2 VARCHAR(50),           -- 该员工的技术2
    skill3 VARCHAR(50)            -- 该员工的技术3
);

解释：
在这个表中，skill1、skill2和skill3是多值属性，表示每个员工可以拥有多个技能。这违反了4NF的要求。

规范化后的表结构：
为了满足4NF，我们需要将技能信息存储在一个独立的EmployeeSkills表中，每个员工的每个技能都作为单独的一行存储：

-- 4NF 规范化后的表结构
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100)
);

CREATE TABLE EmployeeSkills (
    employee_skill_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    skill_name VARCHAR(50),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

现在，EmployeeSkills表中的每个技能都作为单独的一行存储，符合4NF的要求。

（6）、第五范式（5NF）

第五范式（也称为投影-连接范式，PJ/NF）要求在满足4NF的基础上，消除连接依赖。连接依赖是指当多个表之间的关系可以通过连接操作来表达时，应该将这些表进一步分解，以避免冗余和更新异常。

目标：

• 消除连接依赖，确保表中没有复杂的多对多关系。
• 进一步减少冗余和更新异常。

示例：
假设我们有一个表Sales，其中存储销售记录，涉及多个产品和多个客户。每个销售记录可以包含多个产品，且每个产品可以被多个客户购买。这种多对多关系会导致复杂的连接依赖。

未范式化sql示例：

-- 未范式化的 Sales 表
CREATE TABLE Sales (
    sale_id INT PRIMARY KEY,    -- 销售记录id
    customer_id INT,            -- 顾客id
    product_id INT,             -- 产品id
    quantity INT                -- 数量
);

解释：
在这个表中，customer_id和product_id之间存在多对多关系，这违反了5NF的要求。

规范化后的表结构：
为了满足5NF，我们可以将Sales表分解为多个表，分别存储客户、产品和销售记录之间的关系。

-- 5NF 规范化后的表结构
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

CREATE TABLE Sales (                         -- 销售表
    sale_id INT AUTO_INCREMENT PRIMARY KEY,    --销售记录id
    customer_id INT,                       -- 顾客id
    sale_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE SaleItems (                      -- 销售详情表
    sale_item_id INT AUTO_INCREMENT PRIMARY KEY,    -- 销售记录详情id
    sale_id INT,                                    -- 销售id
    product_id INT,
    quantity INT,
    FOREIGN KEY (sale_id) REFERENCES Sales(sale_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

现在，Sales表和SaleItems表之间的关系更加清晰，符合5NF的要求。

（7）、范式总结

通过逐步应用范式化的原则，我们可以有效地减少数据冗余、提高数据一致性和可维护性。每个范式级别都解决了特定类型的数据冗余问题。

具体解释如下：

• 1NF：确保每个字段都是不可再分的基本数据项，并且每个表都有唯一的主键。（即：每个字段仅存储1个值，不能存多个值）
• 2NF：消除非主属性对部分主键的依赖，确保所有非主属性完全依赖于整个主键。（即：组合主键的情况时会出现，一般较少）
• 3NF：消除传递依赖，确保所有非主属性只依赖于主键。（即：间接通过非主键属性才能和主键产生关联的情况）
• BCNF：消除任何非平凡的函数依赖，确保每个决定因素都是候选键。（和3NF差不多，增加了候选键的概念）
• 4NF：消除多值依赖，确保表中没有多值属性。（即：避免多值的属性挂到一条记录中，可以采取详情表多条记录的方式优化）
• 5NF：消除连接依赖，确保表中没有复杂的多对多关系。（即：一条记录中尽量不要出现3个id形式的关联，容易造成多对多）

仅从概念上去理解这些范式的意思和区别还是比较困难的，可以多参考下上面具体的sql示例比较容易理解。

4、反范式（Denormalization）

反范式是范式的对立面，它通过有意地引入数据冗余来优化查询性能。反范式的设计通常会将多个表中的数据合并到一个表中，或者在表中存储重复的数据，以减少查询时的JOIN操作。

5、反范式的优缺点

优点：

• 提高查询性能：通过减少JOIN操作，反范式可以显著提高查询的速度，尤其是在处理大量数据时。
• 简化查询逻辑：反范式化的表结构更简单，查询语句也更加直观，减少了开发和维护的复杂性。
• 适合高并发读取场景：在高并发读取的场景下，反范式可以减少锁争用和I/O操作，提升系统的响应速度。
• 支持大数据分析：在数据仓库和OLAP（在线分析处理）系统中，反范式化的设计可以加速复杂的聚合查询和报表生成。

缺点：

• 增加数据冗余：反范式化会导致数据重复存储，增加了存储空间的需求，并且可能影响数据的一致性。
• 更新复杂度增加：由于数据冗余的存在，更新操作变得更加复杂，容易引发数据不一致的问题。例如，更新一个字段时，可能需要同时更新多个表中的相同数据。
• 维护成本增加：反范式化的设计需要更多的开发和维护工作，尤其是在数据结构发生变化时，可能需要重新设计表结构并调整应用程序逻辑。
• 不符合ACID属性：反范式化的设计可能会降低数据库事务的隔离性和一致性，特别是在并发写入的情况下。

6、常见的反范式技术

（1）、预计算结果

• 将常用的查询结果预先计算并存储在表中，避免每次查询时重新计算。
• 适用于需要频繁执行复杂聚合查询的场景。

（2）、冗余列

• 在表中添加冗余列，存储从其他表中派生的数据。
• 适用于需要频繁访问某些关联数据的场景。

（3）、宽表设计

• 将多个相关的表合并为一个宽表，减少JOIN操作。
• 适用于需要快速读取大量数据的场景。

（4）、缓存

• 使用内存缓存或分布式缓存（如Redis）来存储常用的数据，减少对数据库的访问。
• 适用于高并发读取的场景。

（5）、物化视图

• 创建物化视图（Materialized View），将复杂的查询结果物理存储在数据库中。
• 适用于需要定期刷新查询结果的场景。

7、范式与反范式的应用场景

（1）、范式适用场景

• OLTP（在线事务处理）系统：范式化的设计非常适合OLTP系统，因为这些系统通常涉及频繁的插入、更新和删除操作，范式化可以确保数据的一致性和完整性。
• 小型数据库：对于小型数据库，范式化的设计可以有效地减少存储空间和维护成本。
• 需要严格数据一致性的场景：如果数据的一致性和完整性至关重要（如金融系统、医疗系统等），范式化是更好的选择。

（2）、反范式适用场景

• OLAP（在线分析处理）系统：反范式化的设计非常适合OLAP系统，因为这些系统通常涉及大量的读取操作和复杂的聚合查询，反范式可以显著提高查询性能。
• 大数据分析：在大数据分析中，反范式化可以帮助加速数据的加载和查询，尤其是在处理海量数据时。
• 高并发读取场景：如果系统的主要负载是读取操作，反范式化可以减少JOIN操作，提升系统的响应速度。
• 实时报表系统：反范式化可以帮助加速报表的生成，尤其是在需要频繁生成复杂报表的场景中。

8、范式与反范式的结合使用

在实际应用中，范式和反范式并不是互斥的，而是可以根据具体需求进行结合使用。

以下是一些常见的策略：

• 混合设计：*在某些情况下，可以对部分数据进行范式化设计，而对于那些频繁查询但较少更新的数据进行反范式化设计。*例如，在用户信息表中，可以将用户的静态信息（如姓名、性别）范式化存储，而将用户的动态信息（如最近登录时间、订单历史）反范式化存储。
• 缓存层：可以在数据库之上添加缓存层（如Redis、Memcached），将常用的查询结果缓存起来，减少对数据库的直接访问。这样可以在保持范式化设计的同时，提升查询性能。
• 物化视图：可以创建物化视图，将复杂的查询结果物理存储在数据库中，避免每次查询时进行大量的JOIN操作。物化视图可以在后台定期刷新，确保数据的及时性。
• 分区表：对于大型表，可以使用分区表技术，将数据按一定规则划分为多个子表，减少查询时的扫描范围。分区表可以结合范式化和反范式化设计，既能保证数据的一致性，又能提高查询性能。

9、范式和反范式总结

• 范式（Normalization）通过消除数据冗余和依赖，确保数据的一致性和完整性，适用于OLTP系统和需要严格数据一致性的场景。
• 反范式（Denormalization）通过引入数据冗余来优化查询性能，适用于OLAP系统、大数据分析和高并发读取场景。
• 混合设计是一种常见的做法，结合范式和反范式的优势，根据具体需求灵活调整数据库结构，以达到最佳的性能和可维护性。

二、表设计原则

在设计MySQL数据库表时，遵循良好的设计原则可以帮助你创建高效、可维护且易于扩展的数据库结构。

1、范式化（Normalization）

范式化是通过消除数据冗余和依赖关系，将数据分解为多个表的过程。范式化的目的是确保数据的一致性和完整性，减少更新异常，并提高数据库的可维护性。

范式的优点：

• 减少数据冗余：避免重复存储相同的数据，节省存储空间。
• 提高数据完整性：通过外键约束等机制，确保数据的一致性和准确性。
• 易于维护：范式化的设计使得数据的插入、更新和删除操作更加简单和安全。

常见的范式级别：

• 第一范式（1NF）：确保每一列都是不可再分的基本数据项，每个表都有唯一的主键。
• 第二范式（2NF）：在满足 1NF 的基础上，消除非主属性对部分主键的依赖。
• 第三范式（3NF）：在满足 2NF 的基础上，消除传递依赖，确保所有非主属性只依赖于主键。
  一般设计满足前三个比较OK了，第四五的范式可以参考上面。

何时停止范式化:
虽然范式化有助于提高数据一致性和减少冗余，但在某些情况下，过度范式化可能会导致查询性能下降。因此，在设计表时，需要根据具体的应用场景权衡范式化和反范式化的优缺点。
例如：

• OLTP系统（在线事务系统）：通常更适合范式化设计，因为这些系统涉及频繁的插入、更新和删除操作。
• OLAP系统（在线分析系统）：可能更适合反范式化设计，以优化查询性能，尤其是在处理大量数据时。

2、选择合适的数据类型

（1）、使用最小的数据类型

选择合适的数据类型不仅可以节省存储空间，还可以提高查询性能。

以下是选择数据类型的几个建议：

• 整数类型：

  • TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT（4字节）、BIGINT（8字节）。根据实际需求选择最小的整数类型。

• 浮点数类型：

  • FLOAT和DOUBLE用于存储浮点数。如果不需要精确的小数运算，可以考虑使用DECIMAL 类型来避免精度问题。

• 字符类型：

  • CHAR用于固定长度的字符串，VARCHAR用于可变长度的字符串。尽量避免使用TEXT类型，除非确实需要存储非常大的文本数据。
  • 使用ENUM或SET类型来限制字段的取值范围，减少存储空间。

• 日期和时间类型：

  • DATE用于存储日期，DATETIME用于存储日期和时间，TIMESTAMP用于存储带有时区的时间戳。TIMESTAMP通常占用更少的空间，但需要注意时区的影响。

• 布尔类型：

  • MySQL没有专门的布尔类型，通常使用TINYINT(1)来表示布尔值（0或1）。

（2）、避免不必要的复杂类型

尽量避免使用过于复杂的类型（如JSON、BLOB），除非确实有必要。复杂类型可能会增加查询的复杂性和性能开销。

3、使用合适的主键

（1）、选择合适的主键类型

• 自增主键（AUTO_INCREMENT）：

  • 对于大多数表，使用INT或BIGINT类型的自增主键是一个常见且有效的选择。自增主键简单易用，适合大多数应用场景。

• UUID主键：

  • 如果你需要分布式系统中的唯一标识符，或者希望避免主键冲突，可以考虑使用UUID。不过，UUID会占用更多的存储空间，并且可能会影响索引性能。

• 复合主键：

  • 在某些情况下，使用复合主键（即由多个字段组成的主键）可能是合理的。复合主键可以确保数据的唯一性，但也会增加查询的复杂性。使用复合主键时，确保它们是真正必要的，并且不会影响性能。

（2）、避免使用过长或复杂的主键

主键越短越好，因为它会影响索引的大小和查询性能。过长的主键会导致索引占用更多的内存和磁盘空间，进而影响查询速度。

4、使用外键约束

外键约束用于确保两个表之间的引用完整性。通过定义外键，可以防止无效的数据插入，并确保相关表之间的数据一致性。

sql示例:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

外键的优点：

• 数据完整性：外键可以防止无效的外键值插入到表中，确保引用的记录存在。
• 级联操作：可以通过ON DELETE和ON UPDATE子句定义级联操作，自动处理相关表中的数据。例如，当父表中的记录被删除时，子表中的相关记录可以自动删除或设置为NULL。

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

外键的注意事项：

• 性能影响：外键约束会增加插入、更新和删除操作的开销，尤其是在大表中。因此，在性能敏感的应用中，可能需要权衡是否使用外键。
• 锁争用：外键约束可能会导致锁争用，特别是在高并发写入的情况下。如果你的应用程序对性能要求较高，可以考虑使用应用程序级别的逻辑来确保数据一致性，而不是依赖外键。

5、合理使用索引

索引的作用：
索引可以显著提高查询性能，尤其是在处理大量数据时。索引通过创建快速查找路径，减少了查询时的扫描范围。

常见的索引类型：

• 主键索引：主键字段自动创建索引，确保每条记录的唯一性。
• 唯一索引：确保索引列中的值是唯一的，类似于主键索引，但可以包含 NULL 值。
• 普通索引：普通的非唯一索引，用于加速查询。
• 组合索引：由多个字段组成的索引，适用于多列查询条件。
• 全文索引：用于加速全文搜索，特别适合处理大量的文本数据。

索引的优化建议：

• 避免过多的索引：虽然索引可以提高查询性能，但过多的索引会增加插入、更新和删除操作的开销。因此，应该只在常用的查询条件上创建索引。

• 选择合适的索引列：索引应该基于最常用的查询条件和排序条件。对于频繁使用的查询，确保相关的列已经索引。

• 使用前缀索引：对于VARCHAR类型的字段，可以考虑使用前缀索引，而不是对整个字段进行索引。前缀索引可以减少索引的大小，同时仍然提供较好的查询性能。
  sql示例：

CREATE INDEX idx_name_prefix ON users (name(10));

• 定期分析和优化索引：随着数据的增长，索引可能会变得不再有效。可以使用 ANALYZE TABLE 和 OPTIMIZE TABLE 命令来分析和优化表的索引。

6、使用适当的约束

• NOT NULL约束
  NOT NULL约束用于确保字段不能为空。这有助于维护数据的完整性和一致性，避免出现无效的空值。
  sql示例：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

• 默认值约束
  DEFAULT约束用于为字段指定默认值。当插入新记录时，如果没有显式提供该字段的值MySQL会自动使用默认值。
  sql示例：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending'
);

• 唯一性约束
  UNIQUE约束用于确保字段中的值是唯一的。这可以防止重复数据的插入，确保数据的唯一性。
  sql示例：

CREATE TABLE emails (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

• 检查约束
  CHECK约束用于限制字段的取值范围。虽然MySQL 5.7及之前版本不完全支持CHECK约束，但从MySQL 8.0开始，CHECK约束得到了完整的支持。
  sql示例：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2) CHECK (price > 0)
);

7、分区表

什么是分区表？
分区表是将一个大表拆分为多个较小的子表的技术。分区表可以根据某些条件（如日期、范围、列表等）将数据分散到不同的物理存储区域，从而提高查询性能和管理效率。

分区的好处：

• 提高查询性能：通过将数据分区，查询时只需要扫描相关的分区，减少数据的扫描，减少了I/O操作。
• 简化数据管理：分区表可以更容易地管理和维护大规模数据，例如可以定期删除旧的分区，而不会影响其他数据。
• 优化备份和恢复：可以针对特定分区进行备份和恢复，而不必备份整个表。

常见的分区类型：

• 范围分区（RANGE）：
  根据某个字段的值范围进行分区。例如，按年份或月份分区。
  sql示例：

  CREATE TABLE sales (
      id INT AUTO_INCREMENT PRIMARY KEY,
      sale_date DATE,
      amount DECIMAL(10, 2)
  ) PARTITION BY RANGE (YEAR(sale_date)) (
      PARTITION p2020 VALUES LESS THAN (2021),
      PARTITION p2021 VALUES LESS THAN (2022),
      PARTITION p2022 VALUES LESS THAN (2023)
  );

• 列表分区（LIST）：
  根据某个字段的离散值进行分区。例如，按地区分区。
  sql示例：

  CREATE TABLE employees (
      id INT AUTO_INCREMENT PRIMARY KEY,
      department VARCHAR(50)
  ) PARTITION BY LIST (department) (
      PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
      PARTITION p_engineering VALUES IN ('Engineering', 'Product')
  );

• 哈希分区（HASH）：
  根据某个字段的哈希值进行分区。适用于均匀分布数据的场景。
  sql示例：

  CREATE TABLE orders (
      order_id INT AUTO_INCREMENT PRIMARY KEY,
      customer_id INT
  ) PARTITION BY HASH (customer_id) PARTITIONS 4;

• 键分区（KEY）：
  类似于哈希分区，但使用MySQL内部的哈希函数。
  sql示例：

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100)
  ) PARTITION BY KEY (id) PARTITIONS 4;

8、避免过度设计

（1）、保持表结构简单

在设计表时，尽量保持表结构简单，避免过度复杂的设计。过多的表、字段和关系可能会增加开发和维护的难度，降低系统的可读性和可维护性。

（2）、避免过度规范化

虽然范式化有助于减少数据冗余和提高数据一致性，但在某些情况下，过度范式化可能会导致查询性能下降。因此，在设计表时，应该根据具体的应用场景权衡范式化和反范式化的优缺点。

（3）、考虑未来的扩展性

在设计表时，应该考虑到未来的需求变化，确保表结构具有一定的扩展性。例如，可以为未来的字段预留足够的空间，或者使用灵活的数据类型（如 JSON）来存储动态数据。

9、性能优化

（1）、批量插入和更新

在插入或更新大量数据时，尽量使用批量操作，而不是逐行插入或更新。批量操作可以减少与数据库的交互次数，显著提高性能。
sql示例：

INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Charlie', 'charlie@example.com');

（2）、使用延迟加载

在查询时，尽量只加载所需的字段，而不是使用SELECT *。这样可以减少网络传输的数据量，提高查询性能。

（3）、使用缓存

对于频繁访问的数据，可以使用缓存（如Redis、Memcached）来减少对数据库的直接访问。缓存可以显著提高读取性能，尤其是在高并发场景下。

（4）、分页查询

在处理大量数据时，使用分页查询可以避免一次性加载过多的数据，减少内存占用和查询时间。
sql示例：

SELECT * FROM users LIMIT 10 OFFSET 0;  -- 第一页
SELECT * FROM users LIMIT 10 OFFSET 10;  -- 第二页

10、安全性

（1）、使用最小权限原则

为每个用户分配最小的权限，确保他们只能访问和修改必要的数据。避免使用超级用户（如 root）进行日常操作，而是为每个应用程序创建专用的数据库用户。
sql示例：

GRANT SELECT, INSERT, UPDATE ON mydb. TO 'app_user'@'localhost' IDENTIFIED BY 'password';

（2）、加密敏感数据

对于敏感数据（如密码、信用卡信息等），应该使用加密技术进行保护。MySQL提供了内置的加密函数（如AES_ENCRYPT和AES_DECRYPT），或者你可以使用应用程序级别的加密库。
sql示例：

INSERT INTO users (username, password) VALUES ('alice', AES_ENCRYPT('mypassword', 'secret_key'));

（3）、启用SSL/TLS

在客户端和服务器之间启用SSL/TLS加密通信，确保数据在传输过程中不会被窃听或篡改。你可以按照之前的指南配置MySQL的SSL证书。

11、表设计总结

在设计MySQL数据库表时，遵循以下原则可以帮助你创建高效、可维护且易于扩展的数据库结构。
建议如下：

• 范式化：通过消除数据冗余和依赖，确保数据的一致性和完整性。
• 选择合适的数据类型：使用最小的数据类型，避免不必要的复杂类型。
• 使用合适的主键：选择合适的主键类型，避免使用过长或复杂的主键。
• 使用外键约束：确保引用完整性，但要注意性能影响。
• 合理使用索引：创建必要的索引以提高查询性能，但避免过多的索引。
• 使用适当的约束：确保数据的完整性和一致性，避免无效数据的插入。
• 分区表：根据具体需求使用分区表，提高查询性能和管理效率。
• 避免过度设计：保持表结构简单，避免过度规范化。
• 性能优化：使用批量操作、延迟加载、缓存和分页查询等技术优化性能。
• 安全性：遵循最小权限原则，加密敏感数据，启用SSL/TLS加密通信。

乘风破浪会有时，直挂云帆济沧海！！！

标签：范式,范式化,--,主键,数据表,INT,id
From： https://blog.csdn.net/qq_34207422/article/details/145166870