1对多属性 mysql 优化_MySQL优化（1）：字段的设计

Web项目中，当Java或者Go等语言速度提高到瓶颈的时候，咱们须要关心MySQL的优化mysql

能够优化的方面有不少：设计表、负载均衡、读写分离、SQL语句优化等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')

结果：3232235521spa

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主键；表之间的关系由关联字段(或关联表)进行表示

通俗来说：咱们建表中，基本都是知足三大范式的