关于java:MySQL用的在溜不知道业务如何设计也白搭

MySQL 业务设计

• 作者: 博学谷狂野架构师

• GitHub：GitHub 地址（有我精心筹备的 130 本电子书 PDF）

  只分享干货、不吹水，让咱们一起加油！😄

逻辑设计

范式设计

范式概述

第一范式：当关系模式 R 的所有属性都不能在合成为更根本的数据单位时，称 R 是满足第一范式的，简记为 1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式 R 满足第一范式，并且 R 得所有非主属性都齐全依赖于 R 的每一个候选要害属性，称 R 满足第二范式，简记为 2NF。

第三范式：设 R 是一个满足第一范式条件的关系模式，X 是 R 的任意属性集，如果 X 非传递依赖于 R 的任意一个候选关键字，称 R 满足第三范式，简记为 3NF。

第一范式

• 数据库表中的所有字段都只具备 繁多属性。
• 繁多属性的列是由 根本数据类型 所形成的。
• 设计进去的表都是 简略的二维表。

示例

解决办法

name-age 列具备两个属性，一个 name，一个 age 不合乎第一范式，把它拆分成两列。

第二范式

要求表中只具备一个业务主键 ，也就是说合乎第二范式的表 不能存在非主键列只对局部主键的依赖关系。

示例

有两张表：订单表，产品表

解决办法

一个订单有多个产品，所以订单的主键为【订单 ID】和【产品 ID】组成的联结主键，这样 2 个主键不合乎第二范式，而且产品 ID 和订单 ID 没有强关联，故，把订单表进行拆分为订单表与订单与商品的两头表。

第三范式

指每一个非主属性既不局部依赖于也不传递依赖于业务主键，也就是在第二范式的根底上打消了非主键对主键的传递依赖。

示例

解决办法

其中

客户编号 和订单编号治理 关联

客户姓名 和订单编号治理 关联

客户编号 和 客户姓名 关联

如果客户编号产生扭转，用户姓名也会扭转，这样不合乎第三大范式，应该 把客户姓名这一列删除

范式设计实战

按要求设计一个电子商务网站的数据库构造，本网站只销售图书类产品，须要具备以下性能：

• 用户登陆 商品展现 供应商治理
• 用户治理 商品治理 订单销售

用户登陆及用户治理

• 用户必须注册并登陆零碎能力进行网上交易，用户名用来作为用户信息的业务主键
• 同一时间一个用户只能在一个中央登陆

只有一个业务主键，肯定是合乎第二范式，没有属性和业务主键存在传递依赖的关系，合乎第三范式。

商品信息

一个商品能够属于多个分类，故，商品名称和分类应该是组合主键，会有大量冗余，不合乎第二范式。应该把分类信息独自寄存

解决办法

另外再建设一个两头表把分类信息和商品信息进行关联

最初的三张表如下

供应商治理性能

合乎三大范式，不须要批改，但如果减少新的一列【银行支行】，这样随着银行账户的变动，银行支行也会编号，不合乎第三大范式

在线销售性能

有多个业务主键，不合乎第二范式，订单商品单价、订单数量、订单金额存在传递依赖关系，不合乎第三范式，须要拆解

解决办法

创立一个订单关联表，将商品分类和商品名称拆解进去

这时候，【订单商品分类】与【订单商品名】有依赖关联，故合并如下

表汇总

查问练习

编写 SQL 查问出每一个用户的订单总金额（用户名，订单总金额）

COPYSELECT a. 单用户名, sum(d. 商品价格 * b. 商品数量)
FROM 订单表 a
JOIN 订单分类关联表 b ON a. 订单编号 = b. 订单编号
JOIN 商品分类关联表 c ON c. 商品分类 ID = b. 商品分类 ID
JOIN 商品信息表 d ON d. 商品名称 = c. 商品名称
GROUP BY a. 下单用户名

编写 SQL 查问出下单用户和订单详情（订单编号，用户名，手机号，商品名称，商品数量，商品价格）

COPYSELECT a. 订单编号, e. 用户名, e. 手机号, d. 商品名称, c. 商品数量, d. 商品价格
FROM 订单表 a
JOIN 订单分类关联表 b ON a. 订单编号 = b. 订单编号
JOIN 商品分类关联表 c ON c. 商品分类 ID = b. 商品分类 ID
JOIN 商品信息表 d ON d. 商品名称 = c. 商品名称
JOIN 用户信息表 e ON e. 用户名 = a. 下单用户

存在的问题

• 大量的表关联十分影响查问的性能
• 完全符合范式化的设计有时并不能失去良好得 SQL 查问性能

反范式设计

什么叫反范式化设计

• 反范式化是针对范式化而言得，在后面介绍了数据库设计得范式
• 所谓得反范式化就是为了性能和读取效率得思考而适当得对数据库设计范式得要求进行违反
• 容许存在大量得冗余，换句话来说反范式化就是应用空间来换取工夫

商品信息反范式设计

上面是范式设计的商品信息表

商品信息和分类信息常常一起查问，所以把分类信息也放到商品表外面，冗余寄存。

在线销售性能反范式

上面是在线销售性能的范式设计

首先来看订单表

• 查问订单信息要关联查问到用户表，但用户表的电话是可能扭转的，而且查问订单的时候常常查问到用户的电话
• 查问订单常常会查问到订单金额，所以把订单金额也冗余进来

新设计的订单表如下

再来看订单关联表

• 和商品信息反范式设计一样，查问订单的时候常常查问商品分类，所以把商品分类和订单名冗余进来
• 商品的单价可能会编号，如果关联查问查问只能查问到最新的商品价格，而查问不到下订单时候的价格，并且商品单价常常会查问。所以把订单单价也冗余进来

新设计的商品关联表如下

查问练习

编写 SQL 查问出每一个用户的订单总金额

COPYSELECT 下单用户名, sum(订单金额)
FROM 订单表
GROUP BY 下单用户名;

编写 SQL 查问出下单用户和订单详情

COPY
SELECT  a. 单用户名, sum(d. 商品价格 * b. 商品数量)
FROM   订单表 a
JOIN 订单分类关联表 b ON a. 订单编号 = b. 订单编号
JOIN 商品分类关联表 c ON c. 商品分类 ID = b. 商品分类 ID
JOIN 商品信息表 d ON d. 商品名称 = c. 商品名称
GROUP BY  a. 下单用户名;

总结

不能齐全依照范式得要求进行设计，思考当前如何应用表

范式化设计优缺点

长处

• 能够尽量得缩小数据冗余
• 范式化的更新操作比反范式化更快
• 范式化的表通常比反范式化的表更小

毛病

• 对于查问须要对多个表进行关联
• 更难进行索引优化

反范式化设计优缺点

长处

• 能够缩小表的关联
• 能够更好的进行索引优化

毛病

• 存在数据冗余及数据保护异样
• 对数据的批改须要更多的老本

物理设计

命名标准

数据库、表、字段的命名要恪守可读性准则

应用大小写来格式化的库对象名字以取得良好的可读性

例如：应用 custAddress 而不是 custaddress 来进步可读性。

数据库、表、字段的命名要恪守表意性准则

对象的名字应该可能形容它所示意的对象

例如：对于表，表的名称应该可能体现表中存储的数据内容；对于存储过程存储过程应该可能体现存储过程的性能。

数据库、表、字段的命名要恪守长名准则

尽可能少应用或者不应用缩写

存储引擎抉择

数据类型抉择

当一个列能够抉择多种数据类型时

• 优先思考数字类型
• 其次是日期、工夫类型
• 最初是字符类型
• 对于雷同级别的数据类型，应该优先选择占用空间小的数据类型
• 对精度有要求的时候，抉择精度高的数据类型。int<float<double<decimal.

浮点类型

留神 float 和 double 是非精度类型，如果是和金额相干尽量用 decimal

COPYselect  sum(c1), sum(c2), sum(c3)  from  test_numberic;

日期类型

面试常常问道 timestamp 类型 与 datetime 区别

• datetime 类型在 5.6 中字段长度是 5 个字节
• datetime 类型在 5.5 中字段长度是 8 个字节
• timestamp 和时区无关，而 datetime 无关

COPYDROP TABLE IF EXISTS `test_time`;
CREATE TABLE `test_time`  (`c1` datetime(6) NULL DEFAULT NULL,
  `c2` timestamp(6) NULL DEFAULT NULL,
  `c3` time(6) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert into  test_time  VALUES(NOW(),NOW(),NOW());
COPYmysql> select * from test_time;
+----------------------------+----------------------------+-----------------+
| c1                         | c2                         | c3              |
+----------------------------+----------------------------+-----------------+
| 2019-12-25 14:44:22.000000 | 2019-12-25 14:44:22.000000 | 14:44:22.000000 |
+----------------------------+----------------------------+-----------------+
1 row in set (0.00 sec)

set time_zone="-10:00"

mysql> select * from test_time;
+----------------------------+----------------------------+-----------------+
| c1                         | c2                         | c3              |
+----------------------------+----------------------------+-----------------+
| 2019-12-25 14:44:22.000000 | 2019-12-24 20:44:22.000000 | 14:44:22.000000 |
+----------------------------+----------------------------+-----------------+
1 row in set (0.00 sec)

字符串类型

字符串类型所需的存储和值范畴

定义与变长区别 (CHAR VS VARCHAR)

字符串类型相干注意事项

• 在 BLOB 和 TEXT 列上创立索引时，必须制订索引前缀的长度
• VARCHAR 和 VARBINARY 必须长度是可选的
• BLOB 和 TEXT 列不能有默认值
• BLOB 和 TEXT 列排序时只应用该列的前 max_sort_length 个字节

COPYmysql> show variables like 'max_sort_length';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_sort_length | 1024  |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

本文由 传智教育博学谷狂野架构师 教研团队公布。

如果本文对您有帮忙，欢送 关注 和点赞 ；如果您有任何倡议也可 留言评论 或私信，您的反对是我保持创作的能源。

转载请注明出处！