Mysql 数据库使用入门系列：Mysql 表设计原则和实战分析（二）

这篇文章是系列的文章《Mysql 数据库使用入门系列》的又一个要点，Mysql 表设计原则是什么，今天我们将通过理论 + 实战的方式，讲解如何根据 Mysql 表设计原则，针对业务特性设计一个数据表。

数据库三大范式，数据表设计的基本原则

数据库设计的三大范式：

• （1NF）确保每一列具有原子性，即每一列的数据都是不可分割的。

  即一列只存一种数据，比如一个json 格式的数据，不应该放到一个字段中，应该拆分成不同的字段。

• （2NF）确保表格中的每一行数据只与该表格的主键有关系，即非主键列必须完全依赖于主键。

  即只存储跟表格定义有直接关系的数据。

• （3NF）确保非主键列之间没有传递依赖关系，即不存在一个非主键列依赖于另一个非主键列。

  消除传递依赖。比如，内容表 content 存在来源 source，如果直接将来源抽象为一个名字，那么可以直接在 content 表内建立一个source 字段，存储来源的名字。但是如果将来源也实体化（即来源也是一个相对复杂的对象），那么来源应该有独立的表结构，内容表里面只应该存储直接关联 source_id 字段。至于来源的详细，比如来源名称，创建时间等等之类的信息都属于内容的间接依赖，应该属于 source 表，而不再是 content 表。

  再比如地址表 address 别把 “省 / 市 / 区” 塞一个字段，假如省 / 市 / 区有单独的 region 表，那 address 应该依赖 region 表中 省 / 市 / 区的ID主键，而不是非主键 name。原因也简单，id 是永远不会改变的，代表的是唯一的 region，但是名字是可能会变化的（比如行政区的名重新规划），一旦变化，直接依赖名字会导致所有数据需要重新清洗，以更新到最新名称。而如果依赖的是 region 的 id 则不会出现这种问题，名字源头只有一个，一旦更改，所有依赖此地区 id 的名字等于都变成了最新。

数据类型的选用

之前已经写过一篇文章，教大家如何使用数据库常用字段类型。所以这里不做过多的关于字段类型使用介绍，不同的数据类型，解决不同的问题，选用正确的数据类型，不管能优化资源占用，还能在后续的使用（比如范围检索、或者唯一性检索）中获得更快的查询性能。

简单举个例子，比如时间戳存储，我们选用 unsigned int，而不用 datetime 类型。unsigned int 只占用 4个字节的存储空间，天然支持时区的改变。datetime 类型占用 8 个字节，当然优点是时间跨度更大，不会因时区改变而改变。具体如何选用，需要考虑业务场景。

找准主键

一般自增型主键，我们都选用 INT/BIGINT（unsigned）。考虑分布式场景的话，用 UUID （不同库的主键ID不会冲突），尽量不用业务字段（比如手机号可能换）做主键。主键稳定，索引就不会频繁变更，进行条件检索的时候，或者关联查询时候就更快速。

索引常用必加，不用不加

索引建立原则

首先明确一个问题，并不是索引越多就越好。索引多了，缺点就是也要占用大量的存储空间，另外关联索引的字段有变更，索引也必须更新。索引太多，会导致更新变慢。

查询频繁的字段（如订单号、用户名）加索引，但不能随便乱加。

• 大文本字段（TEXT）不加
• 频繁更新的字段（如库存）少加
• 单表索引别超 5 个，多了反而变慢
• 多条件查询，尽量建立联合索引，命中率高，且占用空间小（注意关联索引的最左优先原则）
• 建最少得索引，覆盖尽量多的业务查询

注意：怎么建立索引，跟业务使用息息相关。同样的表结构，因为业务场景不同（或者说查询条件不同），索引的建立方式可能完全不一样。要尽量让最少的索引尽可能最多的覆盖所有的业务查询。

那如何建立索引才能覆盖更多的业务呢，讲到这里，又需要开一个专门的话题来讲 Mysql 查询优化。这里就不专门单独讲了，清关注博主后续Mysql 数据库使用入门系列文章。

索引类型说明

Mysql 索引，一般最常用的就是两种，普通索引和唯一索引。

• 普通索引（或者叫非唯一索引）

  • 索引可以由多个字段列创建，这就叫联合索引。联合索引查询需要符合最左优先原则。（此处不细说）

  普通索引允许在索引列中存在重复的值。在InnoDB存储引擎中，普通索引通常使用B+树（B-Tree）数据结构来实现。B+树是一种平衡树结构，它保持数据排序，并允许快速的键值搜索、数据检索、顺序访问、插入和删除。简单说就是，普通索引可以包含重复的键值，适用于需要快速查找但不要求唯一性的场景。

• 唯一索引

  • 重复值处理：在插入新行时，如果尝试插入的键值已经存在于唯一索引中，插入操作将失败。
  • NULL值处理：如果唯一索引列被定义为允许NULL值，那么可以有多个NULL值存在。但如果明确指定了列的唯一性且不允许NULL（例如使用UNIQUE KEY约束），则只能有一个NULL值。

  唯一索引不仅要求索引列的所有值都必须唯一，还允许NULL值（除非明确指定唯一索引列为NOT NULL）。唯一索引同样使用B+树结构，但是它的实现略有不同，特别是在处理重复值和NULL值时。唯一索引确保索引列中的每个值都是唯一的，适用于需要保证数据唯一性的场景。

外键

早起的 sql server 数据库，建表时候，我们会使用一些外键，避免数据删除时候产生残余数据。外键保证，数据删除的顺序和结果一致性。

如下示例：

关联表（如订单表关联用户表）用外键能保证数据一致（比如删用户前必须先删订单）。

在现代 MySQL 表设计中，外键的使用确实越来越少，这主要源于实际业务场景中的性能、灵活性和架构需求考量，具体原因有以下几点：

性能损耗问题

外键本质是数据库层面的约束，会强制数据库在执行INSERT/UPDATE/DELETE操作时进行额外的检查（如验证关联表是否存在对应记录、处理级联操作等）。

• 这些检查会增加数据库的 IO 和 CPU 开销，在高并发场景下（如电商订单、高频写入业务），可能成为性能瓶颈。
• 外键还可能导致表之间的锁竞争加剧（例如删除父表记录时，需要锁定子表相关行进行检查），进一步降低并发能力。

分布式与分库分表的限制

现代系统为了应对大数据量和高并发，常采用分库分表或分布式数据库架构：

• 外键约束只能作用于同一个数据库实例中的表，无法跨库、跨分片生效。如果关联表被拆分到不同数据库或分片，外键会完全失效。

  例如：订单表和用户表可能分属不同数据库，此时外键无法维护它们的关联关系，必须依赖应用层逻辑。

灵活性与可维护性降低

外键会增强表之间的耦合性，导致表结构修改困难。

比如需删除、重命名或者修改关联表，必须先处理外键约束，操作起来非常繁琐。

应用层控制更灵活

外键的约束限制，在现在开发中，更倾向于应用层维护数据一致性。应用层可以通过事务 + 逻辑判断实现同样的约束判定，逻辑更清晰，而且调试方便。

可以结合缓存减少数据库查询，进一步提升性能。比如查询数据是否存在时候，可以通过缓存确认数据是否存在，在执行相关操作。

外键的隐形约束，对新手要求更高，如果不了解，很容易导致异常操作，而应用层逻辑更直观，方便修改，文档管理、评审管理流程更加统一。

数据表关系如何映射为实体

在数据库设计中，数据表之间的关系（如一对一、一对多、多对多）该如何设计呢。我们来通过实例进行一一说明。

一对一关系（One-to-One）

两个表的记录一一对应（如用户表和用户详情表，一个用户只有一份详情）。

• 在 “从属表” 中添加关联字段（如user_id），直接对应 “主表” 的主键（如user表的id）。
• 为保证 “一对一”，可在关联字段上添加唯一索引（如UNIQUE KEY uk_user_id (user_id)），或完全由应用层控制（插入时检查该user_id是否已被使用）。

如下示例：

-- 主表：用户基本信息
CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL
);

-- 从属表：用户详细资料（与user一一对应）
CREATE TABLE user_detail (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL, -- 关联user表的id（无外键约束）
  address VARCHAR(100),
  UNIQUE KEY uk_user_id (user_id) -- 确保一个user只对应一条detail
);

一对多关系（One-to-Many）

一个主表记录对应多个从表记录（如一个部门包含多个员工）。

• 在 “从表”（多的一方）中添加关联字段（如dept_id），对应 “主表”（一的一方）的主键（如department表的id）。
• 对关联字段建立普通索引（如KEY idx_dept_id (dept_id)），优化关联查询性能。

示例如下：

-- 主表：部门
CREATE TABLE department (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 从表：员工（多个员工可属于同一部门）
CREATE TABLE employee (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  dept_id INT, -- 关联department表的id（无外键约束）
  KEY idx_dept_id (dept_id) -- 优化查询
);

多对多关系（Many-to-Many）

两个表的记录可互相对应多条（如学生与课程，一个学生可选多门课，一门课可有多个学生）。

• 新增 “中间表”，存储两个主表的主键作为关联字段（如student_id和course_id）。
• 中间表的主键可设为两个关联字段的组合（复合主键），避免重复关联。

如下示例：

-- 主表1：学生
CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 主表2：课程
CREATE TABLE course (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

-- 中间表：关联学生与课程
CREATE TABLE student_course (
  student_id INT NOT NULL, -- 关联student表的id
  course_id INT NOT NULL, -- 关联course表的id
  PRIMARY KEY (student_id, course_id), -- 复合主键，避免重复选同一门课
  KEY idx_course_id (course_id) -- 优化查询
);