概述

数据库表结构的设计对于数据库性能的影响是非常大的。
在本文中，我们将会针对数据库表结构的设计展开进行说明，帮助你了解在面对业务需求时，应该如何合理的进行数据库表结构设计。
实际上，数据表的设计和工作流程的设计很像，我们既需要规范性，也要考虑到执行时的方便性。

数据库中键的基本概念

在数据库中，键是一个重要的概念，我们经常会提到各种数据库的键，数据库中的键（Key）由一个或者多个属性组成。下面，我们来了解一下这些键的含义：

  • 超键：能唯一标识记录的属性集叫做超键。
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
  • 主键：用户可以从候选键中选择一个作为主键。
  • 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。
  • 主属性：包含在任一候选键中的属性称为主属性。
  • 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

看到上面的描述你可能还是有点懵，我举个简单的例子。
以之前用过 NBA 的球员表（player）和球队表（team）为例，可以把球员表定义为包含球员编号、姓名、身份证号、年龄和球队编号；球队表包含球队编号、主教练和球队所在地。

  • 超键：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。
  • 候选键：最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
  • 主键：自己选定，也就是从候选键中选择一个，比如（球员编号）。
  • 外键：球员表中的球队编号。
  • 主属性：player 表中就是球员编号和身份证号。

  • 非主属性：player 表中就是姓名、年龄、球队编号。

    数据库设计范式

    在设计关系型数据库模型的时候，需要对关系内部各个属性之间联系的合理化程度进行定义，这就有了不同等级的规范要求，这些规范要求被称为范式（NF）。
    可以把范式理解为，一张数据表的设计结构需要满足的某种设计标准的级别。
    目前关系型数据库一共有 6 种范式，按照范式级别，从低到高分别是：

  • 1NF（第一范式）

  • 2NF（第二范式）
  • 3NF（第三范式）
  • BCNF（巴斯 - 科德范式）
  • 4NF（第四范式）
  • 5NF（第五范式，又叫做完美范式）。

数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求，比如满足 2NF 的一定满足 1NF，满足 3NF 的一定满足 2NF，依次类推。
那我们真的需要了解这么多的范式么？一般来说数据表的设计应尽量满足 3NF。但也不绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。
下面，我们来分别看一下各个范式的含义，这些范式的含义需要与上述我们了解的各种键的概念结合起来一起看。

第一范式

第一范式指的是数据库表中的任何属性都是原子性的，不可再分。
这个概念比较好理解，就是每个字段的含义都是原子的，不能拆分成其他两个字段的组合。

第二范式

第二范式指的是数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。如果有哪些非主属性只和主键的一部分有关的话，它就不符合第二范式。同时可以得出：如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式(前提是该数据表符合第一范式)。
举一个没有满足 2NF 的例子，比如说我们设计一张球员比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键是（球员编号，比赛编号），这个数据表就不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

(球员编号) → (姓名，年龄) (比赛编号) → (比赛时间, 比赛场地)

换句话说，候选键中的某个单一字段其实提前就已经决定了部分非主属性。
那如果不满足第二范式会引发什么问题呢？

  • 数据冗余：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。
  • 插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
  • 删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
  • 更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

为了满足第二范式，我们可以把球员比赛表设计为下面的三张表：

  • 球员 player 表包含球员编号、姓名和年龄等属性；
  • 比赛 game 表包含比赛编号、比赛时间和比赛场地等属性；

  • 球员比赛关系 player_game 表包含球员编号、比赛编号和得分等属性。

    第三范式

    第三范式是指在满足第二范式的同时，对任何非主属性都不传递依赖于候选键。也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。
    我们用球员 player 表举例子，这张表包含的属性包括球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出来，如下图所示：
    image.png
    你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合第三范式的要求。
    如果要达到 3NF 的要求，需要把数据表拆成下面这样：

  • 球员表的属性包括球员编号、姓名和球队名称；

  • 球队表的属性包括球队名称、球队主教练。

    (BCNF)巴斯-科德范式

    符合第三范式的表关系就没问题了吗？来看下这张仓库管理关系 warehouse_keeper 表：
    image.png
    在这个数据表中，一个仓库只有一个管理员，同时一个管理员也只管理一个仓库。我们先来梳理下这些属性之间的依赖关系。仓库名决定了管理员，管理员也决定了仓库名，同时（仓库名，物品名）的属性集合可以决定数量这个属性。
    总结一下：数据表的候选键是（管理员，物品名）和（仓库名，物品名），主属性是包含在任一候选键中的属性，也就是仓库名，管理员和物品名。非主属性是数量这个属性。
    我们来看一下是否符合第一、第二、第三范式：

  • 数据表每个属性都是原子性的，符合 1NF 的要求；

  • 数据表中非主属性”数量“都与候选键全部依赖，（仓库名，物品名）决定数量，（管理员，物品名）决定数量，因此，数据表符合 2NF 的要求；
  • 数据表中的非主属性，不传递依赖于候选键。

那我们看一下这个数据表有什么问题嘛？

  • 增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因此会出现插入异常；
  • 如果仓库更换了管理员，我们就可能会修改数据表中的多条记录；
  • 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

那为什么会出现这种问题呢？主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况。
因此，巴斯-科德范式其实就是在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。
为此，我们可以把仓库管理关系 warehouse_keeper 表拆分成下面这样：

  • 仓库表：（仓库名，管理员）

  • 库存表：（仓库名，物品名，数量）

    反范式设计

    虽然数据库表结构设计中有这么多范式，那么，我们在设计数据表的时候一定要满足这些范式么？实际上不是的！
    在之前已经了解了越高阶的范式得到的数据表越多，数据冗余度越低。但有时候，我们在设计数据表的时候，还需要为了性能和读取效率违反范式化的原则。反范式就是相对范式化而言的，换句话说，就是允许少量的冗余，通过空间来换时间。

    测试数据准备

    比如我们想要查询某个商品的前 1000 条评论，会涉及到两张表。
    商品评论表 product_comment，对应的字段名称及含义如下：
    image.png
    用户表 user，对应的字段名称及含义如下：
    image.png
    为了更好地进行 SQL 优化实验，我们需要给用户表和商品评论表随机模拟出百万量级的数据。我们可以通过存储过程来实现模拟数据。
    创建用户表：

    1. CREATE TABLE `user` (
    2. `user_id` int NOT NULL,
    3. `user_name` varchar(255) NOT NULL,
    4. `create_time` datetime NOT NULL,
    5. PRIMARY KEY (`user_id`)
    6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    给用户表随机生成 100 万用户的代码：

    1. CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
    2. BEGIN
    3. DECLARE i INT DEFAULT 0;
    4. DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
    5. DECLARE date_temp DATETIME;
    6. SET date_temp = date_start;
    7. SET autocommit=0;
    8. REPEAT
    9. SET i=i+1;
    10. SET date_temp = date_add(date_temp, interval RAND()*60 second);
    11. INSERT INTO user(user_id, user_name, create_time)
    12. VALUES((start+i), CONCAT('user_',i), date_temp);
    13. UNTIL i = max_num
    14. END REPEAT;
    15. COMMIT;
    16. END;

    然后我们调用存储过程来完成数据写入操作：

    1. call insert_many_user(10000, 1000000);

    可以看到，创建 100 万条用户数据的耗时大约是：3m8s。
    image.png
    接着我们再来创建一下商品评论表 product_comment：

    1. CREATE TABLE `product_comment` (
    2. `comment_id` int NOT NULL AUTO_INCREMENT,
    3. `product_id` int NOT NULL,
    4. `comment_text` varchar(255) DEFAULT NULL,
    5. `comment_time` datetime NOT NULL,
    6. `user_id` int NOT NULL,
    7. PRIMARY KEY (`comment_id`)
    8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    然后，我们来随机生成 100 万条商品评论：

    1. CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(IN START INT(10), IN max_num INT(10))
    2. BEGIN
    3. DECLARE i INT DEFAULT 0;
    4. DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
    5. DECLARE date_temp DATETIME;
    6. DECLARE comment_text VARCHAR(25);
    7. DECLARE user_id INT;
    8. SET date_temp = date_start;
    9. SET autocommit=0;
    10. REPEAT
    11. SET i=i+1;
    12. SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
    13. SET comment_text = substr(MD5(RAND()),1, 20);
    14. SET user_id = FLOOR(RAND()*1000000);
    15. INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
    16. VALUES((START+i), 10001, comment_text, date_temp, user_id);
    17. UNTIL i = max_num
    18. END REPEAT;
    19. COMMIT;
    20. END;

    然后调用存储过程，运行结果如下：

    1. call insert_many_product_comments(10000, 1000000);

    MySQL 一共用了 3m44s 完成了商品评论数据的创建。

    反范式优化实验对比

    如果我们想要查询某个商品 ID，比如 10001 的前 1000 条评论，需要写成下面这样：

    1. SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p 
    2. LEFT JOIN user AS u 
    3. ON p.user_id = u.user_id 
    4. WHERE p.product_id = 10001 
    5. ORDER BY p.comment_id DESC LIMIT 1000

    运行时长为 256ms，查询效率并不高。
    这是因为在实际生活中，我们在显示商品评论的时候，通常会显示这个用户的昵称，而不是用户 ID，因此我们还需要关联 product_comment 和 user 这两张表来进行查询。
    如果我们想要提升查询的效率，可以允许适当的数据冗余，也就是在商品评论表中增加用户昵称字段，在 product_comment 数据表的基础上增加 user_name 字段，就得到了 product_comment2 数据表。
    这样一来，只需单表查询就可以得到数据集结果：

    1. SELECT comment_text, comment_time, user_name FROM product_comment2 WHERE product_id = 10001 ORDER BY comment_id DESC LIMIT 1000

    优化之后只需要扫描一次聚集索引即可，运行时间为 74ms 。可以看到，看到，在数据量大的情况下，查询效率会有显著的提升。

    反范式适用场景

    可以看出，反范式可以通过空间换时间，提升查询的效率，但是反范式也会带来一些新问题：

  • 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂。

  • 需要采用存储过程来支持数据的更新、删除等额外操作，很容易增加系统的维护成本，比如用户每次更改昵称的时候，都需要执行存储过程来更新，如果昵称更改频繁，会非常消耗系统资源。

那么反范式优化适用于哪些场景呢？

  • 当冗余信息有价值或者能大幅度提高查询效率的时候，我们就可以采取反范式的优化。
  • 反范式优化也常用在数据仓库的设计中，因为数据仓库通常存储历史数据，对增删改的实时性要求不强，对历史数据的分析需求强。