关于java:一文搞定关系数据库设计要领值得收藏

摘要

本文探讨关系数据库设计相干的一些内容，波及关系模型，表结构设计等内容，以学生选修课程讲述设计过程，在尽量讲清楚设计要领的前提下，简化设计内容。

本文基于 MySQL 数据库为根底，适宜有肯定关系型数据库根底的人浏览。

实体 - 关系模型（E-R）

首先搞清楚什么是 E - R 数据模型？它有什么用？

E- R 模型在将事实世界中事实的含意和互相关联映射到概念模式方面十分有用，因而，许多数据库设计工具都利用了 E - R 模型的概念。E- R 模型所采纳的三个次要概念是：实体集、关系集和属性。

• 实体：实体是世界中能够区别于其余对象的“事件”或者“物体”，例如，学校里的每个学生、学生选修的每门课程等都是一个实体。
• 属性：属性是实体集中每个成员具备的描述性性质。例如，学生的姓名，学号等。
• 实体集：实体集就是就有雷同类型及属性的实体汇合，比方，学校里的所有学生，学生选修的所有课程等。
• 关系：关系是多个实体间的互相关联。例如，小明选修语文课程。
• 关系集：关系集是同类关系的汇合。例如，所用学生选修课程的汇合。

既然晓得了 E - R 数据模型的作用，上面就让咱们来画出学生选修课程的 E - R 图吧。

其中，（学号，姓名，年龄，性别）为学生的属性，（问题）为选修关系的属性，（课程号，课程名，学分）为课程的属性。学生和课程之间的关系是多对多，即一个学生能够抉择多门课程，一门课程能够被多个学生选修。

关系表设计

从下面的 E - R 图，咱们一眼就能看出他们之间的分割，那该如何设计关系模式呢？

咱们要晓得，关系数据库设计的目标是为了生成一组关系模式，使咱们可能既不用存储不必要的冗余信息，又能不便地获取信息。为了是咱们不便的达到这个目标，范式设计应运而生。

Boyce-Codd 范式

咱们所晓得的令人满意的范式之一是 Boyce-Codd 范式（BCNF）。如果对 F + 中所有形如 α→β 的函数依赖，其中 α⊆R 且 β⊆R，上面的定义至多有一个成立：

• α→β 是平庸函数依赖（即 β ⊂ α）。（一般来说，平庸函数依赖并没有探讨意义，探讨的都是非平庸函数依赖，即 β ∉⊂ α 的状况）
• α 是模式 R 的超码。

思考如下关系模式及其相应的函数依赖：

• 学生 =（学号，姓名，年龄，性别）

学号 → 姓名 年龄 性别

• 课程 =（课程号，课程名，学分）

课程号 → 课程名 学分

• 选修 =（学号，课程号，问题）

学号 课程号 → 问题

以上模式均属于 BCNF。就拿第一组关系模式来说，学生上仅有的非平庸函数依赖，箭头左侧是学号，学号是该模式的一个候选码（候选码属于超码的子集），没有毁坏 BCNF 的定义。

其实并不是每个 BCNF 都能放弃函数依赖的，例如：

Banker-schema =（branch-name，customer-name，banker-name）

它示意的是一个客户在某一分支机构有一个银行账户负责人。它要求满足的函数依赖集 F 为

• banker-name → branch-name
• branch-name customer-name → banker-name

显然，Banker-schema 不属于 BCNF，因为 banker-name 不是超码。

咱们能够将它合成失去如下的 BCNF：

Banker-branch-schema =（banker-name，branch-name）

Customer-banker-schema =（customer-name，banker-name）

合成后的模式只放弃了 banker-name → branch-name，而 branch-name customer-name → banker-name 的依赖没有放弃。

第三范式

当咱们不能同时满足以下三个设计指标：

• BCNF。
• 无损连贯。
• 放弃函数依赖。

咱们能够放弃 BCNF 而承受绝对较弱的第三范式（3NF）。因为 3NF 总能找到无损连贯并放弃依赖的合成。

具备函数依赖即 F 的关系模式 R 属于 3NF，只有 F + 中所有形如 α→β 的函数依赖，其中 α⊆R 且 β⊆R，上面的定义至多有一个成立：

• α→β 是平庸函数依赖（即 β ⊂ α）。
• α 是模式 R 的超码。
• β – α 中的每个属性 A 都蕴含在 R 的候选码中。

回到 Banker-schema 的例子中，咱们曾经看到了没能将该关系模式转化成 BCNF 而又放弃依赖和无损连贯的合成，但改模式属于 3NF。在 Banker-schema 中，候选码是{branch-name，customer-name}，所以 Banker-schema 上不蕴含候选码的就只有 banker-name。

而形如 α → banker-name 的非平庸函数依赖都是以 {branch-name，customer-name} 作为 α 的一部分。因为 {branch-name，customer-name} 是候选码，所以合乎 3NF 的定义。

每个 BCNF 都属于 3NF，因为 BCNF 的束缚比 3NF 更严格。

存储引擎的抉择

关系模式一但确定，根本的数据库表构造就确定了，接下来就是表构造的具体设计了，这里先从存储引擎开始，MySQL 提供的各种存储引擎都是依据不同的用例设计的。

下表概述了 MySQL 提供的一些存储引擎。

最罕用的两种存储引擎：MyISAM 和 InnoDB。

• MyISAM：MySQL 5.5.5 以前，MyISAM 作为 MySQL 的默认存储引擎。
• InnoDB：MySQL 5.5.5 当前，InnoDB 作为 MySQL 的默认存储引擎。

另外，关注公众号 Java 技术栈，在后盾回复：面试，能够获取我整顿的 MySQL 系列面试题和答案，十分齐全。

何如抉择？

抉择规范: 依据利用特点抉择适合的存储引擎，对于简单的利用零碎能够依据理论状况抉择多种存储引擎进行组合。然而要晓得组合应用的毛病：

• InnoDB 和非 InnoDB 存储引擎的组合比照，仅应用 InnoDB 存储引擎能够简化备份和复原操作。MySQL Enterprise Backup 对应用 InnoDB 存储引擎的所有表进行热备份。对于应用 MyISAM 或其余非 InnoDB 存储引擎的表，它会执行“热”备份，数据库会持续运行，但这些表在备份时不能批改。

上面是罕用存储引擎的实用环境：

• InnoDB：事务型业务场景首选。
• MyISAM：非事务型的大多数业务场景。
• Memory：数据保留到内存中，能提供极速的访问速度。（集体感觉能够应用 Redis 等 NoSQL 数据库代替）

字符集抉择

存储引擎之后就是确定字符集，字符集的抉择非常重要，不论是 MySQL 还是 Oracle，如果在数据库创立阶段没有正确抉择字符集，那么在前期须要更换字符集的时候将要付出昂扬的代价。

如何抉择？

倡议在可能齐全满足利用当下和将来几年倒退的前提下，尽量应用小的字符集。应为更小的字符集意味着可能节俭空间、缩小网络传输字节数，同时因为存储空间小间接的晋升了零碎的性能。

不同的数据库有不同的字符集利用级别，别离为服务器级别、库级别、表级别、字段级别，通常举荐应用库级别或者表级别。因为库级别或者表级别在保有灵活性的同时，兼顾数据间字符集的对立，这能够给开发省去很多解决字符集的麻烦。

数据类型的抉择

抉择准则

前提：应用适合的存储引擎。

抉择准则：为了获得最佳的存储，您应该在所有状况下尝试应用最准确的类型。

固定长度和可变长度

char 与 varchar

上面这个例子阐明二者的区别：

请留神上表中最初一行的值只实用不应用严格模式时；如果 MySQL 运行在严格模式，超过列 长度的值不保留，并且会呈现谬误。

从 CHAR(4)和 VARCHAR(4)列检索的值并不总是雷同，因为检索时从 CHAR 列删除了尾部的空 格。通过上面的例子阐明该差异：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO vc VALUES ('ab', 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---------------------+
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---------------------+
| (ab)              | (ab)                |
+---------------------+---------------------+
1 row in set (0.06 sec)

对于 InnoDB 数据表，外部的行格局没有辨别固定长度和可变长度列，所有数据化行都应用指向数据列值的头指针，因而在实质上，应用固定长度的 CHAR 列不肯定比应用可变长度的 VARCHAR 列要好。

因为，次要的性能因数是数据行应用的存储总量。对于占用空间来说，CHAR 总是大于等于 VARCHAR，所以，应用 VARCHAR 来最小化行数据的存储总量，进而缩小磁盘 I / O 频率。

text 和 blob

在应用 text 或者 blob 类型的字段是须要留神一下几点，以便取得更好的性能：

• 执行大量的删除和更新操作后，会留下很”空洞“，须要定期 optimize table 进行碎片整顿；
• 防止查问大型的 text 和 blob。查问大型的 text 和 blob 会使一页能装下的数据量缩小，减少磁盘 I / O 压力。
• 把 text 和 blob 拆散到独自的表中。这会把原来表中的数据列转变为更短的固定长度的数据行格局，这个非常有用。

浮点数和定点数

在 MySQL 中 float、double 是浮点数，decimal 是定点数。

浮点数劣势：在长度肯定的状况下，浮点数能示意更大的数据范畴。

浮点数毛病：精度问题。

情谊揭示：在无关金钱交易方面浮点数慎用！！！

整数

MySQL 反对 SQL 规范整数类型 INTEGER（或 INT）和 SMALLINT。作为规范的扩大，MySQL 还反对整数类型 TINYINT、MEDIUMINT 和 BIGINT。下表显示了每个整数类型所需的存储空间和范畴。

索引设计

设计准则

• 搜寻的索引列，不肯定是所要抉择的列。最适宜索引的列是呈现在 WHERE 子 句中的列，或连接子句中指定的列，而不是呈现在 SELECT 关键字后的抉择列表中的列。
• 应用惟一索引。对于惟一值的列，索引的成果最好，而具备多个 反复值的列，其索引成果最差。
• 应用短索引。如果对字符串列进行索引，应该指定一个前缀长度。例如，如果有一个 CHAR(200) 列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。
• 利用最左前缀。每个额定的索 引都要占用额定的磁盘空间，并升高写操作的性能。
• 不要适度索引。
• 思考在列上进行的比拟类型。如果是在列上做函数运算，对其进行索引将毫无意义。

示例

针对下面提到的学生选课 E - R 图，给出设计后果和阐明：

表 1 -1 学生信息表（Student）

表 1 -2 课程信息表（Course）

表 1 -3 选课成绩表（SC）

• Student 中姓名的长度是 40，这里把外国人也思考进来了；
• Student 中性别定义成枚举，次要是枚举意义扼要；
• Student 中没有存年龄，而存储的出生日期，是因为年龄并不是变化无穷的，并且可能通过出生日期正确计算。
• SC 中问题应用的是 double 而不采纳 decimal，次要是因为问题并不需要那么高的精确度。
• SC 中（sno,cno）作为联结主键而不是独立主键，因为现阶段 markdown 无奈合拼行，所以无奈编辑。

参考

• （美）Abraham Silberschatz 等. 数据库系统概念. 北京：机械工业出版社，2012
• MySQL 5.7 Reference Manual
• [eimhe.com]网易技术部的 MySQL 中文材料.

原文链接：https://blog.csdn.net/qq_3601…

版权申明：本文为 CSDN 博主「pikaxiao」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2021 最新版)

2. 别在再满屏的 if/ else 了，试试策略模式，真香！！

3. 卧槽！Java 中的 xx ≠ null 是什么新语法？

4.Spring Boot 2.5 重磅公布，光明模式太炸了！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！