2-3-1-4、表结构设计和数据类型优化_数据表设计优化项-CSDN博客

本文链接：https://blog.csdn.net/weixin_41381248/article/details/128854448

数据库设计

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

范式设计

定义

范式来自英文 Normal Form，简称 NF。要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟，不仅给数据库的编程人员制造麻烦，而且面目可憎，可能存储了大量不需要的冗余信息
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了
下面提到的范式定义并非官方定义，由于官方定义晦涩难懂，因此下面的说法是比较通俗易懂的

第一范式

定义：属于第一范式关系的所有属性都不可再分，即数据项不可分
理解：第一范式强调数据表的原子性，是其他范式的基础

第二范式

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分
通常在实现来说，需要为表加上一个列，以存储各个实例的惟一标识。例如员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码

第三范式

指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余

反范式设计

定义

在实际的业务查询中会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余，换句话来说反范式化本质思想就是使用空间来换取时间

优点

反范式设计可以减少表的关联
可以更好的进行索引优化

缺点

存在数据冗余及数据维护异常
对数据的修改需要更多的成本

字段数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择

基本原则

更小的通常更好

一般情况下,应该尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期也更少
但是要确保没有低估需要存储的值的范围，因为在的多个地方增加数据类型的范围是一个非常耗时和痛苦的操作。如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型

简单就好

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

尽量避免NULL

很多表都包含可为 NULL(空值)的列，即使应用程序并不需要保存 NULL 也是如此，这是因为可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值
如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节
通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先在现有 schema 中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列

整数类型

存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用 8，16，24，32，64 位存储空间，也就是 1、2、3、4、8 个字节
同时整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如 TINYINT UNSIGNED 可以存储的范围是 0~255，而 TINYINT 的存储范围是-128~127
有符号和无符号类型使用相同的存储空间，并具有相同的性能，因此可以根据实际情况选择合适的类型
MySQL 可以为整数类型指定宽度，例如 INT(11)，对大多数应用这是没有意义的，它不会限制值的合法范围,只是规定了 MySQL 的一些交互工具（例如 MySQL 命令行客户端)用来显示字符的个数。对于存储和计算来说，INT(1)和 INT(20)是相同的
在选择上，遵循着更小的通常更好的原则，在业务许可的情况下，尽量选择位数小的

实数类型

实数是带有小数部分的数字。MySQL 既支持精确类型的存储 DECIMAL 类型，也支持不精确类型存储 FLOAT 和 DOUBLE 类型
DECIMAL 类型用于存储精确的小数，本质上 MySQL 是以字符串形式存放的。所以 CPU 不支持对 DECIMAL 的直接计算，所以在 MySQL 中自身实现了 DECIMAL 的高精度计算。相对而言，CPU 直接支持原生浮点计算，所以浮点运算明显更快
浮点和 DECIMAL 类型都可以指定精度。对于 DECIMAL 列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0 和更高版本将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字)。例如，DECIMAL(18,9) 小数点两边将各存储 9 个数字，一共使用 9 个字节:小数点前的数字用 4 个字节，小数点后的数字用 4 个字节，小数点本身占 1 个字节
MySQL 5.0 和更高版本中的 DECIMAL 类型允许最多 65 个数字
浮点类型在存储同样范围的值时，通常比 DECIMAL 使用更少的空间。FLOAT 使用 4 个字节存储，DOUBLE 占用 8 个字节，所以 DOUBLE 比 FLOAT 有更高的精度和更大的范围
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 DECIMAL，例如存储财务或金融数据，在精度不敏感和需要快速运算的时候，选择 FLOAT 和 DOUBLE
但在数据量比较大的而且要求精度时，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在 BIGINT 里，这样可以同时避免浮点存储计算不精确和 DECIMAL 精确计算代价高的问题

字符串类型

MysQL 支持多种字符串类型，包括 VARCHAR 和 CHAR 类型、BLOB 和 TEXT 类型、ENUM（枚举）和 SET 类型

VARCHAR 和 CHAR 类型

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

VARCHAR

VARCHAR 类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)
在内部实现上，既然是变长，VARCHAR 需要使用 1 或 2 个额外字节记录字符串的长度，如果列的最大长度小于或等于 255 字节,则只使用 1 个字节表示,否则使用 2 个字节
VARCHAR 节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在 UPDATE 时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MyISAM 会将行拆成不同的片段存储，InnoDB 则需要分裂页来使行可以放进页内
使用 VARCHAR(5)和 VARCHAR(200)存储’hello’在磁盘空间上开销是一样的，但是更长的列会消耗更多的内存，因为 MySQL 通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略是只分配真正需要的空间

CHAR

CHAR 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 会删除所有的末尾空格，CHAR 值会根据需要采用空格进行填充以方便比较

对比

在 CHAR 和 VARCHAR 的选择上，遵循一下对比即可：

字符串列的最大长度比平均长度大很多﹔列的更新很少；使用了像 UTF-8 这样复杂的字符集，每个字符都使用不同的字节数进行存储
CHAR 适合存储很短的字符串，或者所有值定长或都接近同一个长度。例如， CHAR 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于经常变更的数据，CHAR 也比 VARCHAR 更好，因为定长的 CHAR 类型不容易产生碎片
对于非常短的列，CHAR 比 VARCHAR 在存储空间上也更有效率。例如用 CHAR( 1)来存储只有 Y 和 N 的值，如果采用单字节字符集只需要一个字节，但是 VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节

BLOB 和 TEXT 类型

BLOB 和 TEXT 都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储
与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当 BLOB 和 TEXT 值太大时，InnoDB 会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要 1~4 个字节存储一个指针，然后在外部存储区域存储实际的值
BLOB 和 TEXT 家族之间仅有的不同是 BLOB 类型存储的是二进制数据，没有排序规则或字符集，而 TEXT 类型有字符集和排序规则
使用 BLOB 和 TEXT 需要注意：

BLOB 和 TEXT 值会引起一些性能问题，所以尽量避免使用 BLOB 和 TEXT 类型
一定要用，建议把 BLOB 或 TEXT 列分离到单独的表中
在不必要的时候避免检索大型的 BLOB 或 TEXT 值。例如，SELECT *查询就不是很好的想法，除非能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则，很可能毫无目的地在网络上传输大量的值。建议可以搜索索引列，决定需要的哪些数据行，然后从符合条件的数据行中检索 BLOB 或 TEXT 值
还可以使用合成的(Synthetic)索引来提高大文本字段(BLOB 或 TEXT)的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似“<”或“>=”等范围搜索操作符是没有用处的)。可以使用 MD5 函数生成散列值，也可以使用 SHA1(或 CRC32)，或者使用自己的应用程序逻辑来计算散列值

使用枚举代替字符串

如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型
枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中，MySQL 在内部会将每个值在列表中的位置保存为整数，这样的话可以让表的大小大为缩小
例如：

CREATE TABLE enum_test(
  e ENUM(' fish', 'apple', 'dog') NOT NULL
);

INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

注意：

因为枚举列实际存储为整数，而不是字符串，所以不要使用数字作为 ENUM 枚举常量，这种双重性很容易导致混乱，例如 ENUN( ’ 1’,‘2’,‘3’)
枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列

日期和时间类型

如果需要存储比秒更小粒度的日期和时间值，MySQL 目前没有提供合适的数据类型，但是可以使用自己的存储格式：

可以使用 BIGINT 类型存储微秒级别的时间截
使用 DOUBLE 存储秒之后的小数部分
| 日期类型 | 占用空间 | 表示范围 |
| — | — | — |
| DATETIME | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
| DATE | 3 | 1000-01-01 ~ 9999-12-31 |
| TIME | 3 | YEAR(2):1970-2070, YEAR(4):1901-2155 |
| YEAR | 1 | -838:59:59 ~ 838:59:59 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC |

MySQL 可以使用许多类型来保存日期和时间值，例如 YEAR 和 DATE。MySQL 能存储的最小时间粒度为秒
大部分时间类型都没有替代品，因此没有什么是最佳选择的问题。唯一的问题 MySQL 提供两种相似的日期类型:DATETIME 和 TIMESTAMP。对于很多应用程序，它们都能工作，但是在某些场景，需要做些适当选择

DATETIME

这个类型能保存大范围的值，从 1001 年到 9999 年，精度为秒。它把日期和时间封装到格式为 YyYYMMDDHHMMSS 的整数中，与时区无关。使用 8 个字节的存储空间

TIMESTAMP

TIMETAMP 类型保存了从 1970 年 1 月 1 日午夜（格林尼治标准时间)以来的秒数，它和 UNIX 时间戳相同。TIMESTAMP 只使用 4 个字节的存储空间，因此它的范围比 DATETIME 小得多﹔只能表示从 1970 年到 2038 年。TIMESTAMP 显示的值也依赖于时区。从空间效率来说，当然 TIMETAMP 比 DATETIME 更高

命名规范

数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写
表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑
- 说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝
表名不使用复数名词
数据库、表、字段的命名禁用保留字，如 desc、range、match 之类
对象的名字应该能够描述它所表示的对象
- 表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的作用”；对于存储过程存储过程应该能够体现存储过程的功能。库名与应用名称尽量一致
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名
表达是与否概念的字段，不应该使用 is_xxx 的方式命名，而是使用某种状态，比如是否删除，使用delete_flag，而不是is_del，数据类型是 unsigned tinyint（1 表示是，0 表示否）