数据库基本设计方法

数据库基本设计方法

注：  文章仅提供对标题内容的直接讨论，并不提供间接相关知识点的讨论。这些间接涉及的知识点后续可能会更新文章。如果喜欢我的描述方式，欢迎评论区提问或直接与我讨论，并请关注我留意我的更新。或者也可以直接去找搜索引擎求知。

1. 规范化（Normalization）

在数据库设计中，规范化是一种将数据结构化以减少数据冗余的方法。规范化通常会产生多个表，通过外键进行关联。例如：

• 主表：存储主要信息，如 students 和 courses 表。
• 关系表：存储关联信息，如 enrollments 表。

-- 表结构
students(student_id, student_name)
courses(course_id, course_name)
enrollments(enrollment_id, student_id, course_id)

-- 示例数据
students:
1, Alice
2, Bob

courses:
1, Math
2, Science

enrollments:
1, 1, 1
2, 1, 2
3, 2, 1

这种设计使得数据更加规范，减少了数据冗余，并且更易于维护。

2. 使用ID进行关联

在关系表中使用ID进行关联可以显著减少存储空间，因为整数ID比字符串等其他数据类型占用更少的空间。例如：

-- Students 表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255)
);

-- Courses 表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(255)
);

-- Enrollments 表
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

3. 联查获取可读数据

通过使用 JOIN 操作，可以从这些规范化的表中获取可读的数据。例如：

SELECT s.student_name, c.course_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

这种方法既节省了存储空间，又使得查询结果易于阅读。

实际案例

在实际应用中，类似的设计方法被广泛应用于各种场景，包括但不限于以下几个方面：

1. 电商平台

电商平台通常会有多个表来存储用户信息、产品信息和订单信息。例如：

• 用户表（users）
• 产品表（products）
• 订单表（orders）
• 订单项表（order_items），用于存储每个订单中的具体产品和数量

通过联查，平台可以很容易地获取订单详情，包括用户信息和产品信息。

2. 社交媒体

社交媒体平台通常会有多个表来存储用户信息、帖子信息和评论信息。例如：

• 用户表（users）
• 帖子表（posts）
• 评论表（comments）

通过联查，平台可以展示每个帖子及其相关的评论和用户信息。

3. 内容管理系统

内容管理系统通常会有多个表来存储作者信息、文章信息和分类信息。例如：

• 作者表（authors）
• 文章表（articles）
• 分类表（categories）

通过联查，系统可以展示每篇文章及其相关的作者和分类信息。

三大范式

其中的依赖，便是一对多关系下，多依赖一来决定其唯一性。

关系数据库设计中的三个重要范式，用于确保数据的规范化，减少数据冗余，提高数据的一致性和完整性。它们分别是：第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。

• 第一范式（1NF） ：确保每个列的值都是原子性的。
• 第二范式（2NF） ：在满足1NF的基础上，消除部分依赖，每个非主键列完全依赖于整个主键。
• 第三范式（3NF） ：在满足2NF的基础上，消除传递依赖，每个非主键列直接依赖于主键。

总结：

sql三范式就是要求每张表表内，不论是复合主键还是单主键，都要每个列的值原子性的情况下，保证非主键列直接依赖主键，不能有不完整或传递依赖。传递依赖只能在不同表之间出现。

第一范式（1NF）

定义：第一范式要求每个列中的值都是原子性的，即不可再分割的基本数据项。

要求：

• 表中的每一列都只包含单一值。
• 每个列中的值必须是原子性的数据项。

示例：

不符合1NF的表：

| order_id | customer_name    | items               |
|----------|------------------|---------------------|
| 1        | John Doe         | item1, item2        |
| 2        | Jane Smith       | item3, item4, item5 |

符合1NF的表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    item VARCHAR(100)
);

INSERT INTO orders (order_id, customer_name, item) VALUES
(1, 'John Doe', 'item1'),
(1, 'John Doe', 'item2'),
(2, 'Jane Smith', 'item3'),
(2, 'Jane Smith', 'item4'),
(2, 'Jane Smith', 'item5');

第二范式（2NF）

定义：第二范式要求在满足第一范式的基础上，消除部分依赖，即每个非主键列必须完全依赖于整个主键，而不是主键的一部分。

要求：

• 表必须符合第一范式。
• 表中的每个非主键列必须完全依赖于整个主键，而不能依赖于主键的一部分。

示例：

不符合2NF的表：

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    customer_name VARCHAR(100),
    item_name VARCHAR(100),
    PRIMARY KEY (order_id, item_id)
);

在这个例子中，customer_name 只依赖于 order_id，而不是 (order_id, item_id)。

符合2NF的表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    item_name VARCHAR(100),
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

第三范式（3NF）

定义：第三范式要求在满足第二范式的基础上，消除传递依赖，即每个非主键列必须直接依赖于主键，而不是通过其他非主键列间接依赖于主键。

要求：

• 表必须符合第二范式。
• 表中的每个非主键列必须直接依赖于主键，而不能通过其他非主键列间接依赖于主键。

示例：

不符合3NF的表：

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    item_name VARCHAR(100),
    supplier_name VARCHAR(100),
    supplier_address VARCHAR(100),
    PRIMARY KEY (order_id, item_id)
);

在这个例子中，supplier_address 依赖于 supplier_name，而 supplier_name 依赖于 order_id。

符合3NF的表：

CREATE TABLE suppliers (
    supplier_name VARCHAR(100) PRIMARY KEY,
    supplier_address VARCHAR(100)
);

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    item_name VARCHAR(100),
    supplier_name VARCHAR(100),
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (supplier_name) REFERENCES suppliers(supplier_name)
);

小表带动大表（也就是链表查询的必要思想）

通常是由要查询的结果的主体（比如此处客户的订单，主体就是客户，订单从属于他），所直接依赖的主键，对其他表中的传递依赖外键进行链表操作。（ 不论这些表之间的关系是直接的还是通过中间表间接关联的）

示例和解释

假设我们有两个表，一个是小表 customers，另一个是大表 orders：

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

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

现在，我们希望查询每个客户的订单：

SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

优化策略

在执行上述查询时，数据库可以选择以下几种联接策略：

1. 嵌套循环联接（Nested Loop Join） ：

• • 数据库遍历小表 customers，对每个客户ID，在大表 orders 中查找匹配的记录。
  • 这种方法在小表上有索引的情况下非常高效。

2. 哈希联接（Hash Join） ：

• • 数据库首先对小表 customers 构建一个哈希表，然后扫描大表 orders，根据哈希表中的键值进行匹配。
  • 这种方法适用于较大的数据集，尤其是没有索引的情况下。

3. 合并联接（Merge Join） ：

• • 数据库首先对两个表进行排序，然后进行合并操作。
  • 这种方法在两个表上都有排序键的情况下非常高效。

“小表带动大表”的优势

使用小表带动大表的联接策略，可以显著提高查询性能，主要原因如下：

1. 减少大表扫描：通过小表中的键值或索引来限制大表的访问量，减少了对大表的全表扫描。
2. 提高索引利用率：小表中的索引可以用于快速查找大表中的匹配记录。
3. 降低内存和I/O开销：小表驱动的方式可以减少内存和磁盘I/O的开销，因为处理的小表数据量较少。

检查优化

EXPLAIN
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

这种策略在处理大数据集时尤其有效，可以显著降低查询的内存和I/O开销。通过合理设计表结构和索引，并使用查询计划工具，可以更好地利用这种优化策略。

父子表映射结构处理

1. 一对多关系 (One-to-Many)：

在一对多关系中，通常是在子表中加入父表的主键来完成关联，像你提到的那样，这是非常常见的做法。以下是这种方式的特点：

• 父表不关心子表的具体信息：子表中的每个记录都与父表的一个记录相关联，但父表本身通常不需要保存子表的具体信息。
• 子表记录较多：父表可能关联了很多子表记录，存储在父表中不切实际。因此，子表保存父表主键即可，查询时根据父表主键查询子表。

示例：

• 订单表（父表）和 订单项表（子表）。订单项表通过一个 订单ID（父表的主键）关联到 订单表，父表无需存储子表信息。

2. 多对多关系 (Many-to-Many)：

在多对多关系中，通常需要一个中间表（也就是上文的小表带大表） ，因为两个表都可能有多条记录彼此关联。如果没有中间表，两个表中都会存在很多冗余数据，维护起来复杂且性能较低。中间表的作用是简化和明确这种关系。

• 中间表保存两张表的主键：中间表保存两个表的主键作为外键，这样可以避免在任意一张表中存储对方表的多条记录。
• 不需要在父表或子表中直接保存对方的信息：通过中间表查询即可完成多对多关系的映射。

示例：

• 学生表 和 课程表 之间的关系，一个学生可以选多门课，一门课也可以有多个学生选修。这时用一个 学生-课程 中间表保存学生和课程的关联信息。

3. 在父表中保存子表信息的情况：

尽管通常情况下，子表保存父表主键即可完成映射，但有一些特殊情况下，我们可能需要在父表中保存子表的信息。这些场景通常和性能优化、业务需求、或频繁访问某些关联数据有关。

3.1 优化查询性能（ 应对一些简化需求但高频的查询优化 ） ：

如果某个父表字段的子表信息非常频繁被访问，而且该信息很小且相对固定，你可以选择将子表中的信息冗余地存储在父表中。这可以避免频繁的关联查询，减少查询时的 I/O 开销，从而提高性能。

示例：

• 用户表 和 地址表 的关系。通常，用户表会存储 address_id，用户的地址信息存储在 地址表 中。但是，如果应用中非常频繁地查询用户的地址，而且地址变化不频繁，那么可以在 用户表 中存储一些简单的地址信息（如国家、城市），以减少复杂的关联查询。

3.2 业务逻辑需要父表中存储子表的信息（ 父表的某列需要用到子表相关（而非直接）的数据 ） ：

在某些业务场景下，父表的某个字段依赖于子表的数据。例如，父表中的某个字段是基于子表的某些数据计算而来的，这时候父表需要保存子表的某些信息。

示例：

• 订单表 和 订单项表。虽然 订单表 和 订单项表 是一对多关系，通常 订单项表 记录各个商品的详细信息，但 订单表 可能需要保存订单的总金额（即所有订单项金额的总和）。在这种情况下，订单表 存储了一个从子表计算得来的信息。

3.3 缓存场景（第二条的扩展，性能优化向） ：

当某些子表的数据是父表逻辑的核心或是频繁操作的对象时，为了降低查询复杂度，可以在父表中冗余存储部分子表的数据。这样可以在某些查询中避免去子表查询，作为一种缓存机制。

示例：

• 电商应用中的购物车。购物车表（父表）可能需要频繁显示购物车中的商品总量或总价格。如果每次显示购物车都要去 购物车项表（子表）查询所有商品并计算总数，这会影响性能。可以选择在 购物车表 中存储一个 total_items 和 total_price 字段来缓存这个信息。

4. 在父表存储子表信息的代价：

虽然在父表中存储子表信息可以提高性能或简化查询，但也有以下潜在问题：

• 数据冗余：父表存储了子表的信息，会导致数据冗余。如果子表的信息发生变化，父表中的信息也需要保持同步，这增加了维护成本。
• 一致性问题：如果子表更新了某个字段，而忘记同步更新父表中冗余的该字段，就可能导致数据不一致的情况。

回表

“回表”是数据库查询优化中的一个概念，主要涉及到如何从索引获取数据。在关系数据库中，特别是在使用B树或B+树索引的数据库（如MySQL和PostgreSQL）中，回表操作是指通过索引获取行的指针（如行ID或行地址），然后使用这些指针到表中获取完整的行数据。

回表的概念

如果本次搜索的列，被索引全覆盖，就不需要回表了。同时，索引覆盖的含义是指，select后跟着的所有列，都有对应的索引在上面。

示例

假设我们有一个如下的表 employees：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);

并在 last_name 列上创建了一个索引：

CREATE INDEX idx_last_name ON employees (last_name);

情景1：索引覆盖查询

如果我们执行一个只需要索引列的查询：

SELECT last_name FROM employees WHERE last_name = 'Smith';

因为 last_name 列在索引中已经存在，数据库可以直接从索引中获取所需的数据，而不需要回表。

情景2：需要回表的查询

如果我们执行一个需要除索引列之外其他列的查询：

SELECT first_name, last_name, department FROM employees WHERE last_name = 'Smith';

在这种情况下，数据库会执行以下步骤：

1. 使用索引查找：首先，数据库会使用 idx_last_name 索引查找所有 last_name 为 'Smith' 的行。
2. 回表操作：找到这些行的指针（如行ID或行地址），然后回表获取 first_name 和 department 列的值。

为什么回表会影响性能？

回表操作会影响查询性能，原因包括：

1. 额外的I/O操作：回表需要额外的磁盘I/O操作，因为它需要访问表中的数据页。
2. 增加查询时间：由于需要从索引跳转到表数据，查询时间会增加。
3. 影响缓存命中率：回表可能导致更多的数据页访问，从而影响缓存的命中率，增加内存开销。

如何减少回表操作？

为了减少回表操作，提高查询性能，可以考虑以下方法：

1. 索引覆盖查询：确保索引包含所有需要查询的列，创建覆盖索引。
2. 适当的索引设计：根据查询需求设计合适的复合索引，尽量避免需要回表的情况。
3. 调整查询：调整查询语句，使其只查询索引中已有的列。

示例：覆盖索引

假设我们经常需要查询 last_name 和 department 列，可以创建一个覆盖索引：

CREATE INDEX idx_last_name_department ON employees (last_name, department);

现在，以下查询将是一个索引覆盖查询，不需要回表：

SELECT last_name, department FROM employees WHERE last_name = 'Smith';

个人： 如果阅读我的文章对你有所帮助，切实的解决了你对该知识的大部分疑问，又或对我的描述方式认可，欢迎阅读我的 思想篇专栏（TODO） 。这个专栏并不直接更新技术类文章，而是对一些思维能力做梳理，方便提升思考和认知等能力。思维能力的提升对于你的技术、你的人生都是大有裨益，希望更多的人可以共同深度思考！总之和谐评论，点赞有益身心健康，谢谢！