高性能MySQL学习——高性能数据库表设计
高性能数据库表设计
范式与反范式
优秀的库表设计是高性能数据库的基础。范式是基础规范,反范式是针对性设计。
范式
范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式,才能设计出高效率、优雅的数据库,否则可能会设计出低效的库表结构。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,还又称完美范式)。
满足最低要求的叫第一范式,简称 1NF。在第一范式基础上进一步满足一些要求的为第二范式,简称 2NF。其余依此类推。各种范式呈递次规范,越高的范式数据库冗余越小。通常所用到的只是前三个范式,即:第一范式(1NF),第二范式(2NF),第三范式(3NF)。
第一范式
第一范式无重复的列,表中的每一列都是拆分的基本数据项,即列不能够再拆分成其他几列,强调的是列的原子性.。
如果在实际场景中,一个联系人有家庭电话和公司电话,那么以“姓名、性别、电话”为表头的表结构就没有达到 1NF。要符合 1NF 只需把电话列拆分,让表头变为姓名、性别、家庭电话、公司电话即可。
第二范式
第二范式属性完全依赖于主键,首先要满足它符合 1NF,另外还需要包含两部分内容:
- 表必须有一个主键;
- 没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。即要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式
第三范式属性不传递依赖于其他非主属性,首先需要满足 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。
第二范式和第三范式的区别
- 第二范式:非主键列是否依赖主键(包括一列通过某一列间接依赖主键),要是有依赖关系就是第二范式;
- 第三范式:非主键列是否直接依赖主键,不能是那种通过传递关系的依赖。要是符合这种依赖关系就是第三范式。
通过对前三个范式的了解, 3NF 是 2NF 的子集,2NF 是 1NF 的子集。
设计符合 2NF 的表
接下来以订单信息表为例,讲述如何设计一个符合 2NF 的表,首先,我们看原始的订单信息表,如下图所示。
图中,以订单编号和商品编号作为联合主键,商品名称、单位、价格等信息不与主键相关,只与编号相关,违反了第二范式。 应该对订单信息表进行拆分,商品信息单独一张表,订单项目一张表,如下所示,拆分分成 3 张表。
- 包含客户信息的订单信息表;
- 包含商品详情的商品信息表;
- 包含订单详情的订单详情表。
范式优缺点
经过前面的讲解和案例分析可知范式具备以下优点:
- 避免数据冗余,减少维护数据完整性的麻烦;
- 减少数据库的空间;
- 数据变更速度快。
同时,也有如下缺点:
- 按照范式的规范设计的表,等级越高的范式设计出来的表数量越多。
- 获取数据时,表关联过多,性能较差。
表的数量越多,查询所需要的时间越多。也就是说所用的范式越高,对数据操作的性能越低。
反范式
范式是普适的规则,满足大多数的业务场景的需求。对于一些特殊的业务场景,范式设计的表,无法满足性能的需求。此时,就需要根据业务场景,在范式的基础之上进行灵活设计,也就是反范式设计。
反范式设计主要从三方面考虑:
- 业务场景;
- 相应时间;
- 字段冗余。
反范式设计就是用空间来换取时间,提高业务场景的响应时间,减少多表关联。主要的优点如下。
- 允许适当的数据冗余,业务场景中需要的数据几乎都可以在一张表上显示,避免关联;
- 可以设计有效的索引。
范式与反范式异同
范式化模型:
- 数据没有冗余,更新容易;
- 当表的数量比较多,查询数据需要多表关联时,会导致查询性能低下。
反范式化模型:
- 冗余将带来很好的读取性能,因为不需要 join 很多表;
- 虽然需要维护冗余数据,但是对磁盘空间的消耗是可以接受的。
MySQL 使用原则和设计规范
讲完范式,接下来我们看看 MySQL 使用中的一