MySQL之数据库结构设计

本文主要介绍了在数据库结构设计过程中需要考虑的一些问题，来源于以往工作、项目经历以及网络资料等，如果有不准确的地方，欢迎指正交流。

在数据库结构的设计过程中，务必注意以下两点：

• 减少数据冗余，节约数据库存储空间；
• 尽量避免在数据维护过程中出现更新、插入、删除异常。

一般我们按照如下步骤开始：

1. 需求分析：全面了解产品设计的存储需求，保证数据的安全性和完整性
2. 逻辑设计：设计数据的逻辑存储结构，处理数据实体之间的逻辑关系，解决数据冗余和数据维护异常
3. 物理设计：根据具体所使用数据库的特点进行表结构设计
4. 维护优化：根据实际情况，对索引、存储结构等进行优化

其中 1、2 是确立大方向；3 是对 1、2 的具体实施（本文会介绍一些注意事项和建议，详见下文）；4 是后期的迭代优化。

1. 如何进行物理设计

1.1. 定义数据库、表、字段的命名规范

• 可读性
• 表意性
• 长名

1.2. 选择合适的存储引擎

1.2.0. 如何选择

1. 首选 InnoDB，适用于大部分场景
2. 参考因素：事务、备份、崩溃恢复、各存储引擎的专有特性（详见下文）
3. 注意：尽量不要混合使用多种存储引擎

1.2.1. MyISAM

v5.5 之前默认

文件组成

• .frm 表结构
• .MYD 数据
• .MYI 索引

特性

• 表级锁
• 基本的表损坏修复，非事务性，会造成数据丢失
• 支持「全文索引」
• 支持数据压缩（压缩之后只支持读操作）

限制

• 小于 v5.0 版本时，单表默认大小 4G
• 大于 v5.0 版本，单表默认 256TB

适用场景

• 非事务型应用
• 只读类应用（如：数据报表）
• 空间类应用（如：GPS）

1.2.2. InnoDB

v5.58 之后默认

使用表空间进行数据存储，通过配置参数 innodb_file_per_table

• ON 独立表空间（v5.6 之后默认）

  • tablename.ibd 数据
  • tablename.frm 表结构

• OFF 系统表空间

  • ibdata[1 ~ n]

特性

• 事务性存储引擎
• 完全支持事务的 ACID 特性
• 日志
  • Redo Log 已提交的事务
  • Undo Log 未提交的事务
• 支持行级锁
  • 可以最大程度支持并发
  • 由存储引擎层实现
  • 关于锁
    • 主要作用是管理共享资源的并发访问
    • 用于实现事务的隔离性
    • 常用类型
      • 共享锁（读锁）
      • 独占锁（写锁）
    • 常用锁粒度（策略）
      • 表级
      • 行级
    • 其它
      • 阻塞
      • 死锁
• 状态检查
  • show engine innodb status

1.2.3. CSV

文件组成

• .CSV 表内容
• .CSM 表的元数据，如表状态、数据量
• .frm 表结构

特性

• 数据以文本方式存储在文件中
• 所有列不能为 NULL
• 不支持索引（不适合大表、在线处理）
• 对数据文件直接编辑

适用场景

• 数据交互的中间表

1.2.4. ARCHIVE

以 zlib 对表数据进行压缩，磁盘 I/O 更少

文件组成

• .ARZ 数据
• .frm 表结构

特性

• 只支持 insert 和 select 操作
• 只支持在自增 ID 列上添加索引

适用场景

• 日志和数据采集类

1.2.5. MEMORY

也被称为 HEAP 存储引擎，所有数据保存在内存中

文件组成

• .frm 表结构

特性

• 支持的索引类型
  • HASH（默认，适用于：等值查找）
  • BTree（适用于：范围查找）
• 所有字段都是固定长度
• 不支持 BLOG 和 TEXT 等大字段
• 使用表级锁
• 单表大小由参数 max_heap_table_size 决定（默认 16M）

适用场景

• 查找或映射类，如：邮编和地区对应
• 保存数据分析中产生的中间表
• 缓存周期性聚合数据的结果表

⚠️ 注意：

• 数据易丢失，所以要求数据是可再生的。

1.2.6. FEDERATED ？

默认禁止，启用需要在启动时追加 federated 参数（federated=1）

特性

• 提供了访问远程 MySQL 服务器上表的方法
• 本地不存储数据，数据全部存放在远程服务器上
• 本地需要保存表结构和远程服务器的连接信息

适用场景

• 偶尔的统计分析及手工查询

1.3. 为表中字段选择合适的数据类型

1.3.1. 基本原则

• 当一个列可以选择多种数据类型时，应该优先选择数字类型，其次是日期或二进制类型，最后是字符类型。
• 对于相同级别的数据类型，应该优先选择占用空间小的。

1.3.2. 整数类型

列类型	存储空间	取值范围（SIGNED）	取值范围（UNSIGNED）
tinyint	1 个字节	-128 ~ 127	0 ~ 255
smallint	2 个字节	-3276 ~ 3276	0 ~ 65535
mediumint	3 个字节	-8388608 ~ 8388607	0 ~ 16777215
int	4 个字节	-2147483648 ~ 2147483647	0 ~ 4294967295
bigint	8 个字节	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

⚠️ 注意：

• 整型默认 SIGNED 有符号
• 后面括号中的数字，表示的是显示宽度，不表示长度，参见这篇文章：MySQL int(M) 中 M 的作用

1.3.3. 实数类型

列类型	存储空间	是否精确类型
float	4 个字节	否
double	8 个字节	否
decimal	每 4 个字节存 9 个数字，小数点占一个字节	是

1.3.4. 字符串类型

• varchar
  • 特点
    • 存储变长字符串，只占用必要的存储空间
    • 列的最大长度小于 255，则只占用一个字节用于记录字符串长度。
    • 列的最大长度大于 255，则要占用两个字节用于记录字符串长度。
  • 长度（宽度）问题
    • 使用最小的符合需求的长度（根据实际情况，保留一定的空间）
  • 适用场景
    • 列的最大长度比平均长度大很多
    • 列很少被更新
    • 使用了多字节的字符集存储字符串
• char
  • 特点
    • 定长
    • 末尾的空格会被删除
    • 最大宽度 255
  • 适用场景
    • 适合存储长度近似的值
    • 短小的字符串
    • 存储经常被更新的字符串

1.3.5. 日期类型

• datetime

  • 精度
    • 秒 v5.6 之前
    • 微妙 v5.6 之后 datime(6)
  • 与时区无关
  • 占用 8 个字节存储长度
  • 时间范围 1000-01-01 00:00:00 到 9999-12-31 23:59:59

• timestamp

  • 只占用 4 个字节
  • 时间范围 1970-01-01 到 2038-01-19
  • 依赖于指定的时区
  • 在行的数据被修改时，可以自动修改 TIMESTAMP 列的值。

• MySQL v5.6 新增了 date（只需要 3 个字节）、time ，可根据需要了解。

⚠️ 注意：

• 不要使用字符串类型来存储日期数据
• 使用 int 存储日期时间不如直接使用 timestamp 类型

1.4. 建立数据库结构

根据前 3 点给出的建议和注意事项，结合具体业务，依次建立每一个数据表。

2. 关于范式化

2.1. 什么是三范式

• 第一范式
  • 表中所有字段都只具有单一属性
  • 单一属性的列由基本的数据类型所构成
  • 设计出来的表都是简单的二维表
• 第二范式
  • 以「第一范式」为基础
  • 一个业务表中只能有一个业务主键
• 第三范式
  • 以「第二范式」为基础
  • 每一个非主属性既不部分依赖也不传递依赖于业务主键

2.2. 什么是反范式化

以空间换时间，进行适当的冗余，以提高查询效率

2.3. 如何选择

对比优缺点如下，建议根据业务需要合理的结合二者使用 👍。

范式化

• 优点
  • 可以尽量的减少数据冗余
  • 更新操作比「反范式化」更快
  • 数据表通常比「反范式化」的更小
• 缺点
  • 查询需要关联多个表
  • 索引优化困难

反范式化

• 优点
  • 减少表的关联
  • 更好的进行索引优化
• 缺点
  • 存在数据冗余及数据维护异常
  • 对数据对修改需要更多成本