Mysql基础知识

范式化设计

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式5NF，又称完美范式

第一范式（1NF）

• 每一列属性都是不可再分的属性值，确保每一列的原子性。
• 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
• 单一属性的列为基本数据类型构成。
• 设计出来的表都是简单的二维表。

错误案例

其中name中包含了手机号

正确案例

基础字段，不可再分

第二范式（2NF）

• 满足第二范式必须满足第一范式。
• 为每个表建立主键（primary key）。
• 实体的属性完全依赖于主关键字（非主键列必须完全依赖主键列）。

错误案例

其中一个订单id对应了两个商品id，并且该表没有主键

正确案例

将order表中的商品独立出来一张表，order（一）对order_goods（多）

第三范式（3NF）

• 满足第三范式必须先满足第二范式。
• 要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系

错误案例

订单商品关联表，存在商品名称

正确案例

订单商品关联表去掉商品名称，将商品名称放在商品表中

反范式化设计

日常开发工作中，尽量满足第三范式，但是为了满足业务需求并且保证sql查询的效率，开发人员需要根据业务情况做一些反范式设计。反范式设计就是做一些字段冗余，用空间换时间。

• 为了性能和读取效率而适当的违反对数据库设计范式的要求。
• 为了查询的性能（减少join查询带来的io），允许存在部分（少量）冗余数据。

总结：日常开发表结构设计时，通常范式和反范式相结合，在满足业务的同时保证更好的用户体验。

字段数据类型优化

• 尽量选择最小的数据类型

更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

比如：是有一个类型既可以用字符串也可以使用整型，优先选择整型。因为字符串牵涉到了字符集及校对规则等

• 避免字段为null

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

整型

• 存储整数，可以使用这几种整数类型:TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT。分别使用8，16，24，32，64位存储空间，也就是1、2、3、4、8个字节。
• 同时整数类型有可选的 UNSIGNED属性，表示不允许负值，这大致可以使正数的上限提高一倍。例如TINYINT UNSIGNED可以存储的范围是0255，而TINYINT的存储范围是-128127。
• integer和int存储及大小没有任何差别，只是为了业务上区分

实数类型

• FLOAT：浮点型，占用4个字节（32位），精确度大约是6-7位小数，适合精度不敏感和需要快速运算（CPU直接支持原生浮点计算，所以浮点运算明显更快）。
• DOUBLE：浮点型，占用8个字节（64位），精确度大约是15-16位小数，相对于float而言有更高的精度和更大的范围。
• DECIMAL：占用4-65个字节，具体取决于精确度，本质上MySQL是以字符串形式存放的，CPU不支持对DECIMAL的直接计算，相比浮点型有更高的精度和范围。

实数是带有小数部分的数字。MySQL既支持精确类型的存储DECIMAL类型，也支持不精确类型存储FLOAT和 DOUBLE类型（浮点类型）。DECIMAL类型用于存储精确的小数，本质上MySQL是以字符串形式存放的。所以CPU不支持对DECIMAL的直接计算，只是在MySQL中自身实现了DECIMAL的高精度计算。相对而言，CPU直接支持原生浮点计算，所以浮点运算明显更快。

浮点类型在存储同样范围的值时，通常比DECIMAL使用更少的空间。DECIMAL对于列的空间消耗比较大，另外DOUBLE比 FLOAT有更高的精度和更大的范围。

备注：涉及金额的字段，可以使用bigint，以“分”为单位，在代码中进行对应的“分”转“元”处理即可，这样可以避免浮点存储计算不精确和 DECIMAL精确计算代价高的问题。

字符串类型

MysQL支持多种字符串类型，包括VARCHAR、CHAR、BLOB、TEXT、ENUM、SET、JSON。

CHAR

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

VARCHAR

VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间)。在内部实现上，既然是变长，VARCHAR需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。

VARCHAR节省了存储空间，所以对性能也有帮助。但是由于行是变长的，在UPDATE时新值比旧值长时，使行变得比原来更长，这就肯能导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储（innodb以页为存储单位，每页默认16k，即16384字节，而varchar最多可存65532字节 ），在这种情况下，MyISAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

ENUM

如果表中的字段的取值是固定几个字符串，可以使用枚举列代替常用的字符串类型。

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

• 枚举字段是按照内部存储的整数而不是定义的字符串进行排序的，所以尽量按照需要的顺序来定义枚举列。

日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和 DATE以及DATETIME和TIMESTAMP。MySQL能存储的最小时间粒度为秒。

• datetime：存储日期范围：1001年~9999年。
• timestamp：存储日期范围：1970年~2038年,并且跟时区有关系。

MySQL目前没有提供合适的数据类型来存储比秒更小粒度时间，但是可以使用自己的存储格式：可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分。

Bit

Bit字段类型用来存储二进制的位值，Bit(M)代表可存储M位的二进制数据，M取值为区间为1-64。数据库Bit类型常用来存储Boolean类型的状态，也就是可以使用一个字段来存最多64个状态，如果你的系统状态够多或者将来可能会有扩展状态，那推荐使用这个类型进行处理，查询或更新某个状态时按位操作即可。

命名规范

• 可读性原则

数据库、表、字段的命名要遵守可读性原则，尽可能少使用或者不使用缩写。

对象的名字应该能够描述它所表示的对象。例如：表的名称应该能够体现表中存储的数据内容，最好是遵循“业务名称_表的作用”；对于存储过程存储过程应该能够体现存储过程的功能。库名与应用名称尽量一致。

表达是与否概念的字段，应该使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。

• 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
• 表名不使用复数名词
• 数据库、表、字段的命名禁用保留字（关键字），如desc、range、match之类
• 主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。