本文主要介绍了在数据库结构设计过程中需要考虑的一些问题,来源于以往工作、项目经历以及网络资料等,如果有不准确的地方,欢迎指正交流。
在数据库结构的设计过程中,务必注意以下两点:
- 减少数据冗余,节约数据库存储空间;
- 尽量避免在数据维护过程中出现更新、插入、删除异常。
一般我们按照如下步骤开始:
- 需求分析:全面了解产品设计的存储需求,保证数据的安全性和完整性
- 逻辑设计:设计数据的逻辑存储结构,处理数据实体之间的逻辑关系,解决数据冗余和数据维护异常
- 物理设计:根据具体所使用数据库的特点进行表结构设计
- 维护优化:根据实际情况,对索引、存储结构等进行优化
其中 1、2 是确立大方向;3 是对 1、2 的具体实施(本文会介绍一些注意事项和建议,详见下文);4 是后期的迭代优化。
<!-- more -->
1. 如何进行物理设计
1.1. 定义数据库、表、字段的命名规范
- 可读性
- 表意性
- 长名
1.2. 选择合适的存储引擎
1.2.0. 如何选择
- 首选
InnoDB
,适用于大部分场景 - 参考因素:事务、备份、崩溃恢复、各存储引擎的专有特性(详见下文)
- 注意:尽量不要混合使用多种存储引擎
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. 如何选择
对比优缺点如下,建议根据业务需要合理的结合二者使用 👍。
范式化
优点
- 可以尽量的减少数据冗余
- 更新操作比「反范式化」更快
- 数据表通常比「反范式化」的更小
缺点
- 查询需要关联多个表
- 索引优化困难
反范式化
优点
- 减少表的关联
- 更好的进行索引优化
缺点
- 存在数据冗余及数据维护异常
- 对数据对修改需要更多成本