MySQL之数据库结构设计

原创
2020/07/09 08:19
阅读数 687

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

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

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

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

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

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

<!-- more -->

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(适用于:范围查找)
  • 所有字段都是固定长度
  • 不支持 BLOGTEXT 等大字段
  • 使用表级锁
  • 单表大小由参数 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. 如何选择

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

范式化

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

反范式化

  • 优点
    • 减少表的关联
    • 更好的进行索引优化
  • 缺点
    • 存在数据冗余及数据维护异常
    • 对数据对修改需要更多成本
展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
0 评论
0 收藏
0
分享
返回顶部
顶部