【MySQL学习】3.表结构设计和数据类型优化

MySQL 范式与反范式设计
MySQL学习
发布日期:   2023-05-31
文章字数:   3.2k
阅读时长:   11 分

1 表设计

1.1 数据库表设计

良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花费大量不必要的优化时间，效果往往还不怎么样。

在数据库表设计上有个很重要的设计准则，称为范式设计。

1.1.1 范式设计

什么是范式?
范式来自英文 Normal Form，简称 NF。要想设计一个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入 (insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息。

目前关系数据库有六种范式：

  • 第一范式(1NF)
  • 第二范式(2NF)
  • 第三范式(3NF)
  • 巴斯-科德范式(BCNF)
  • 第四范式(4NF)
  • 第五范式(5NF，又称完美范式)。

满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)，其余范式以次类推。一般来说，数据库只需满足第三范式(3NF)就行了。

1.1.1.1 1NF

定义：所有属性都不可再分，即数据项不可分。

第一范式强调数据表的原子性，是其他范式的基础。例如下表：

idname-age
1张三-23

name-age 列具有两个属性，一个 name，一个 age ，不符合第一范式，把它拆分成两列：

idnameage
1张三23

上表就符合第一范式关系。但日常生活中仅用第一范式来规范表格是远远不够的，依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题，此时就需要引入规范化概念，将其转化为更标准化的表格，减少数据依赖。

1.1.1.2 2NF

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。

总之，2NF必须满足：

  • 必须满足1NF；
  • 必须有主键；
  • 非主属性必须完全依赖于主属性。

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。即不能存在部分依赖于主关键字的属性。

比如有两张表：订单表，产品表

订单表ID订单时间产品ID
12019-01-013
12019-01-014
产品表ID产品名称
2娃娃
3飞机
4乐高911

一个订单有多个产品，所以订单的主键为【订单 ID】和【产品 ID】组成的联合主键，这样 2 个组件不符合第二范式，而且产品 ID 和订单 ID 没有强关联， 故，把订单表进行拆分为订单表与订单与商品的中间表。

订单表ID订单时间
12019-01-01
订单与商品的中间表ID订单ID产品ID
113
214
产品表ID产品名称
2娃娃
3飞机
4乐高911

1.1.1.3 3NF

3NF是在2NF的基础上消除了非主属性对主属性的传递依赖。即每一个非主属性既不部分依赖于也不传递依赖于业务主键。

订单表ID订单时间产品ID产品名称
12019-01-013飞机
12019-01-014乐高911
产品表ID产品名称
2娃娃
3飞机
4乐高911

其中

  • 产品 ID 与订单ID存在关联关系；
  • 产品名称与订单ID存在关联关系；
  • 产品 ID 与产品名称存在关联关系；

如果产品 ID 发生改变，导致产品名称发生变化，这样不符合第三范式，应该把订单表中的产品名称这一列从订单表中删除。

1.1.1.4 BCNF

定义：第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满足第三范式（3NF）；

符合3NF，并且，主属性不依赖于主属性。

1.1.1.5 4NF

要求把同一表内的多对多关系删除。

1.1.1.6 5NF

  • 满足4NF
  • 从最终结构重新建立原始结构
  • 表必须可以分解为较小的表，除非那些表在逻辑上拥有与原始表相同的主键

1.1.2 反范式设计

定义：为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量冗余，换句话来说反范式化就是使用空间来换取时间。

下面是范式设计的商品信息表：

ID商品名称出版社名称图书价格图书表述作者
1星空现代出版社26有阴影的地方，必定有光。几米

分类信息：

分类名称分类描述
漫画xxx
小说xxxx

商品-分类：

商品名称分类名称
星空漫画

而实际中，商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗余存放。

ID商品名称分类名称出版社名称图书价格图书表述作者
1星空漫画现代出版社26有阴影的地方，必定有光。几米

1.1.2.1 实际工作中的反范式实现

  • 缓存和汇总
  • 计数器表
  • 分库分表中的查询

1.1.3 范式化和反范式总结

范式化和反范式总结优点缺点
范式设计更新操作通常比反范式化要快通常需要关联
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据可能索引无效
范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
很少有多余的数据意味着检索列表数据时更少需要DISTINCT 或者GROUP BY 语句
反范式设计减少表的关联存在数据冗余及数据维护异常
可以更好的进行索引优化对数据的修改需要更多的成本

2 字段数据类型优化

2.1 基本原则

2.1.1 更小的通常更好

一般情况下，应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少。

2.1.2 简单就好

简单数据类型的操作通常需要更少的 CPU 周期。例如，整型比字符操作代价更低，因为字符集和校对规则(排序规则)使字符比较比整型比较更复杂。比如应该使用 MySQL 内建的类型而不是字符串来存储日期和时间。

2.1.3 尽量避免NULL

可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL， 除非真的需要存储 NULL 值。

如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。

可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。

当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节。

如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

2.2 类型

2.2.1 整型

分为：TINYINT(1B)，SMALLINT(2B)，MEDIUMINT(3B)，INT(4B)，BIGINT(8B)。

可选的UNSIGNED，与有符号数具有相同的性能，使用相同的存储空间；可以使正数的上限提高一倍。

MySQL 可以为整数类型指定宽度。它不会限制值的合法范围，只是规定了MySQL 的一些交互工具（例如MySQL命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

2.2.2 实数型

  • 精确类型：Decimal

    MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中（每4 个字节存9 个数字)。 例如，DECIMAL(18,9)小数点两边将各存储9 个数字，一共使用9 个字节:小数点前的数字用4 个字节，小数点后的数字用4 个字节，小数点本身占1 个字节.

  • 不精确类型
    • FLOAT
    • DOUBLE

浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储，DOUBLE 占用 8 个字节，所以 DOUBLE 比 FLOAT 有更高的精度和更大的范围。

因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如存储财务或金融数据，在精度不敏感和需要快速运算的时候，选择 FLOAT 和 DOUBLE。

但在数据量比较大而且要求精度时，可以考虑使用 BIGINT 代替 DECIMAL， 将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

2.2.3 字符串类型

2.2.3.1 VARCHAR 和 CHAR 类型

VARCHAR 和 CHAR 是两种最主要的字符串类型。

  • VARCHAR

    • 用于存储可变长字符串；
    • 它比定长类型更节省空间，因为它仅使用必要的空间；
    • 需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255 字节，则只使用1个字节表示；否则使用2 个字节；
    • 由于行是变长的，在UPDATE 时新值比旧值长时，使行变得比原来更长，这就可能导致需要做额外的工作。

  • CHAR

    • 定长
    • 当存储CHAR值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较。

2.2.3.2 BLOB和TEXT

BLOB 和 TEXT 类型都是为存储很大的数据而设计的字符串数据类型。

MySQL 把每个BLOB 和TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB 和TEXT 值太大时，InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4 个字节存储一个指针，然后在外部存储区域存储实际的值。

  • BLOB：二进制数据存储，没有排序规则或字符集；
  • TEXT：字符方式存储，有字符集和排序规则。

使用建议：

  • BLOB 和TEXT 值会引起一些性能问题，所以尽量避免使用BLOB 和TEXT类型，一定要用，建议把BLOB 或TEXT 列分离到单独的表中；
  • 在不必要的时候避免检索大型的BLOB 或TEXT 值；
  • 还可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或TEXT)的查询性能。

2.2.4 日期和时间类型

日期类型占用空间表示范围
DATETIME81000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE31000-01-01 ~ 9999-12-31
TIMESTAMP41970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR1YEAR(2):1970-2070, YEAR(4):1901-2155
TIME3-838:59:59 ~ 838:59:59

3 工程实践

  • 数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写
  • 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字
  • 表名不使用复数名词
  • 数据库、表、字段的命名禁用保留字，如desc、range、match 之类
  • 对象的名字应该能够描述它所表示的对象
  • 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名
  • 表达是与否概念的字段，应该使用is_xxx 的方式命名，数据类型是unsigned tinyint（1 表示是，0 表示否）
文章作者: Kezade
文章链接: https://zyxelva.github.io/posts/3779042887.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Kezade !
MySQL 范式与反范式设计
赏
评论
  目录