MySQL优化（1）：字段的设计
Web项目中，当Java或者Go等语言速度提升到瓶颈的时候，我们需要关心MySQL的优化
可以优化的方面有很多：设计表、负载均衡、读写分离、SQL语句优化等
（1）IP地址设计
例如我们需要存储IP地址：192.168.1.1
第一反应是选用VARCHAR（15）；但是更好的方式是INT UNSIGNED（占用四个字节）
因为：IP地址可以很容易地转换为无符号整数
仔细观察IP地址，四部分都是0-255的数字，1个字节（8位）恰好可以表示0-255的整数
而MySQL有函数：inet_aton()：地址转成数字，inet_ntoa()：数字转成地址
示例：
SELECT INET_ATON("192.168.0.1")
结果：3232235521
SELECT INET_NTOA("3232235521")
结果：192.168.0.1
INT UNSIGNED占用字节少于VARCHAR(15)，并且整数查询效率更高（代价是需要使用转换函数）
总结：在设计字段的时候，尽量使用整数来表示字符串
（2）关联表设计
整型的优势：固定存储空间，通常是少量空间
例如：MySQL内部的枚举和集合类型：
enum(男，女，未知)
insert into user(gender) values(男)
这句话在MySQL中实际存储的是1，这就是最典型的把字符串存成整数
注意：实际中，很少使用mysql的enum和set，因为维护成果过高
比如性别需要把未知改为人妖，需要执行alter table modify column操作，需要独占整张表，检查记录性别值的合法性
如果一定要使用这种方式：关联表，字段id，title，存储1，男，2，女。这种方式使用较为广泛
但是，比如家庭地址这样的字符串，是无法改成整型的，不能强行操作
（3）金额存储
金额的存储对数据的精度要求较高，按理来说要使用DECIMAL()
例如DECIMAL(10,2)：有2位小数的定点数
实际中有另一种操作：INT或BIGINT，这时候为了精度不丢失，采用”分“为单位（12.51元记录为1251）
计算机中小数是无法做到不损失精度的，但是金额较特殊，固定了两位小数，所以可以采用这种方式
而且编程中，整数的计算相对于小数较为方便
DECIMAL也有擅长的地方，比如存储大数：123456789123456789
这时候不可以使用INT，只能使用BIGINT或者DECIMAL
注意：这里为什么我们不选择浮点数DOUBLE和FLOAT呢？因为浮点数会导致精度丢失
原因：浮点数占用固定的存储空间，无论存储多大的数，空间是一定的；但是定点数空间会随着数字变大而增加
由此引出了定长类型和变长类型：
定长类型：存储空间固定（INT、FLOAT、DOUBLE、CHAR、DATE、TIME、DATETIME、YEAR、TIMESTAMP）
变长类型：存储空间可变（VARCHAR、DECIMAL、TEXT）
注意：只有定长类型才会有损失精度的问题，定长类型效率较高
结论：在乎存储空间采用定长类型，在乎存储精度采用变长类型
（4）TEXT和VARCHAR的选择
TEXT：通常感觉存储容量较大，其实最大容量和VARCHAR的最大容量几乎一样
但是，TEXT是独立存储的，不占用字段的总空间，但是VARCHAR占用字段总空间，通常总空间是65535字节
结论：更大的数据还是采用TEXT更好
更大的数据类型有LONGTEXT，可以用于选择
（5）字段设计的原则
1.尽可能选择小的数据类型，这条无需多说
2.尽可能使用NOT NULL，因为数据库不需要判断是否为NULL，NULL在MYSQL中的存储和运算更麻烦：
NULL参与常规运算的结果都是NULL，当判断是否为空的时候，必须采用IS NULL和IS NOT NULL
MYSQL中每条记录会使用到额外的存储空间，用于表示每个字段是否为NULL
通常使用一个特殊的数据来占位，比如我要表达NULL通常设置为空字符串或者0
这种情况又会出现问题，比如成绩字段，0代表没有的话无法区分0分的学生，所以可以采用-1，消除歧义
3.字段注释要完整：gender int comment "性别"
4.单张表的字段数量不宜过多，通常最多二三十个；数量过多通常会出现某个业务逻辑只是用其中一部分，浪费性能
5.预留字段，比如field1 int field2 varcahr等等；后期项目如果需要更改表结构，这样做会方便很多
（6）关联表的设计
一对一：一条记录的字段较多，分布到多个表中存储
例如学生表，基础信息：姓名、身高、班级，还有一些不常用的数据：籍贯、家庭成员等信息
这时候应该设计基础信息一张表，不常用数据一张表，使用相同的主键来表示
一对多：在多的一端使用关联字段，关联一端的主键
例如文章和分类表，分类是一端，文章是多端；那么在文章表中需要有一个分类ID的字段做关联
多对多：使用中间表来实现
例如文章和标签，多对多，那么就需要一张表，字段至少有ID、文章ID、标签ID，每一条记录代表一个关联
（7）范式
第一范式：字段的原子性，不可再分割
关系型数据库默认满足第一范式，MYSQL满足
但也可以强行做：比如一个时间字段，同时写入开始时间和结束时间，这就不合理
一个容易出现的问题：（6）中的例子，文档和标签的设计中：如果我为了省事，不引入第三张表，而是在文章表中用一个字段标签IDs字段（例如存入1，2，3）
这种情况很常见，是不合理的做法，在更新的时候会出现很多问题，需要把逗号拆开处理，而且无法建索引
除非是类似日志系统，存入后不再维护，那么可以使用这种方式
第二范式：满足第一范式后，消除对主键的部分依赖（A字段可以确定B字段，那么B字段依赖A字段）
主键：可以唯一标识记录的字段或者字段集合
部分依赖：如果某个字段依赖复合主键的一部分字段，称之为对主键的部分依赖
例如一个课程信息表，字段有：老师，性别，班级，教室，时间，但是不存在ID
这时候需要我们选一个主键，这里面每一个字段都不能作为主键
老师和班级同时可以作为一个主键（复合主键）
但是性别对主键是部分依赖，如何消除呢？
部分依赖的产生必须是复合主键，那么增加一个ID即可消除对主键的部分依赖
第三范式：第二范式的基础上，消除对主键的传递依赖
传递依赖：C依赖于B，B依赖于主键，那么C对主键存在传递依赖关系
上门的例子：性别依赖于老师，老师依赖于ID，那么存在传递依赖关系
消除方式：将独立数据单独建表，使用关联字段进行存储
例子中，建立一个单独的表，记录老师和性别的关系
总结：独立数据独立建表；表中存在与业务逻辑无关的ID主键；表之间的关系由关联字段（或关联表）进行表示
通俗来讲：我们建表中，基本都是满足三大范式的
以上是 MySQL优化（1）：字段的设计 的全部内容， 来源链接： utcz.com/z/532570.html
