MySQL性能优化之表结构设计优化

本文主要是介绍MySQL性能优化之表结构设计优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

　　在实际生产中根据业务需求设计数据库模型时，在表结构的设计上做文章也可以提高数据库的性能。可以采用以下策略：

　　1、设计中间表，一般针对于统计分析功能，或者实时性不高的需求（OLTP、OLAP）

　　2、设计冗余字段

　　　　为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）
　　3、 拆表
　　　　　　1）对于字段太多的大表，考虑拆表（比如一个表有100多个字段）
　　　　　　2）对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表
　　4、主键优化
　　　　每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（不考虑分布式系统的情况下 雪花算法）。
　　5、字段的设计
　　　　数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。尽量把字段设NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

　　　　对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。能用数字的用数值类型。

　　就以上各种策略懂的都懂，但是如何落地呢？比如拆表，分析业务时一大堆的要素，不可能将这些要素放在一张表里，如何将它们分配到不同的表里去呢？这里不得不提到数据库范式。

　　范式就是一张数据表结构所符合的某种设计标准的级别，一张表可以根据不同需求拆分为不同范式级别的多张表；这跟事务的隔离级别有点像,就是级别不同,标准就不同,后一个级别会解除前一个级别存在的部分问题。

　　那么利用范式规则如何将表结构拆分落地呢？先了解范式等一些数据库中基础的概念：

　　关系数据描述术语对应表：
　　　　　　

　　关键码：

　　　　1) 超键：在关系中能唯一标识元组的属性或属性集称为关键模式的超键。
　　　　2) 候选键：不含有多余属性的超键称为候选键。也就是在候选键中在删除属性就不是键了。
　　　　3) 主键：用户选作元组标识的候选键称为主键。一般不加说明，键就是指主键。
　　　　4) 外键：如果模式R中属性K是其他模式的主键，那么K在模式R中称为外键。

　　完全依赖、部分依赖、传递依赖：
　　　　部分函数依赖：设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。
　　　　　　举个例子：学生基本信息表R中（学号，身份证号，姓名）当然学号属性取值是唯一的，在R关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
　　　　完全函数依赖：设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。
　　　　　　举个例子：学生基本信息表R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；

　　　　传递函数依赖：设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。
　　　　　　举个例子：在关系R(学号 ,宿舍, 费用)中，(学号)->(宿舍),宿舍！=学号，(宿舍)->(费用),费用!=宿舍，所以符合传递函数的要求；
　　范式：

　　1NF ：“第一范式的数据表必须是二维数据表”，第一范式是指数据库的每一列都是不可分割的基本数据项，强调列的原子性，试题中某一属性不能拥有几个值。比如数据库的电话号码属性里面不可以有固定电话和移动电话值，如下图：

　　说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

　　实际业务分析中，将一手分析出来的要素建立1NF的表(通常表字段比较多的大表)。

　　2NF： 第二范式建立在第一范式的基础上，即满足第二范式一定满足第一范式，第二范式要求数据表每一个实例或者行必须被唯一标识。除满足第一范式外还有两个条件，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

　　每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。

　　举例来说：当数据表中是联合主键，但是有的列只依赖联合主键中的一个或一部分属性组成的联合主键，此时需要拆表才能复合第二范式。

　　大白话就是非主属性不可对主属性有多个依赖存储——2NF不能存在部分函数依赖(对主键与非主键的要求)。

　　3NF： 若某一范式是第二范式，且每一个非主属性都不传递依赖于该范式的候选键，则称为第三范式，即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
　　举例来说：Employee(emp_id,emp_name,emp_age,dept_id,dept_name,dept_info)，当员工表中emp_id能够唯一确定员工员工信息，但是dept_name可由dept_id唯一确定，此时，该表不符合第三范式，此时可以删除除了dept_id之外的其他部门信息，把所有部门信息单独建立一张部门表。

　　大白话就是非主属性之间不能存在完全依赖关系——3NF不能存在传递函数依赖(对非主键的要求)。

　　BCNF： 在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BCNF。

　　　　（1）所有非主属性对每一个码都是完全函数依赖；
　　　　（2）所有的主属性对于每一个不包含它的码，也是完全函数依赖；
　　　　（3）没有任何属性完全函数依赖于非码的任意一个组合。

　　　　BCNF可以理解为对主键的约束——如果是联合主键，主键之间不可以有关系。

　　R属于3NF，不一定属于BCNF，如果R属于BCNF，一定属于3NF——即3NF与BCNF是非充要关系。
　　下面示例表结构的拆分，将满足1NF的表拆分至满足3NF(必须拆分至BCNF，保持适当冗余)：

　　　　选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)。

　　　　选课关系表SelectCourse是满足1NF的表，同时显然不满足2NF因为存在姓名完全函数依赖于学号而不函数依赖课程名称。将联合主键拆分可以将SelectCourse拆分为2NF表如下：

　　　　　　SelectCourse(学号，姓名，年龄，成绩，课程号)，关键字为(学号)

　　　　　　Course(课程号，课程名称，学分)，关键字为(课程号)

　　　　SelectCourse(学号，姓名，年龄，成绩，课程号)满足了2NF，但是显然不满足3NF因为存在课程号与成绩之间的传递函数依赖，将SelectCourse中存在传递依赖的非主属性再次拆分，如下：

　　　　　　SelectCoure(学号，姓名，年龄，课程号)，关键字为(学号)

　　　　　　Score(课程号，成绩)，关键字为(课程号)

　　　　实际生产中如果拆分至此，那么选课表SelectCourse中姓名，年龄是冗余的，对于数据一致性就提出了要求。

　　　　如果SelectCoure(学号，姓名，年龄，课程号)的关键字为(学号，姓名，年龄)，可进一步进行拆分满足BCNF——主键之间没有任何函数依赖，如下：

　　　　　　SelectCoure(学号，课程号)，关键字为(学号)

　　　　　　Student(学号，姓名，年龄)，关键字为(学号)

　　　　这样拆分后也不存在数据冗余了。

　　总结：根据所选主键，分析主属性与非主属性、非主属性与非主属性、主属性与主属性之间的函数依赖关系，可以实现表结构拆分即数据冗余落地。

这篇关于MySQL性能优化之表结构设计优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！