MySQL

范式设计

在设计关系型数据库模型的时候，需要对关系内部各个属性之间联系的合理化程度进行定义，即有不同等级的规范要求，这些规范要求叫做范式（NF）。可以理解为，一张数据表的设计需要满足某种设计标准的级别。

目前有 6 种范式，从低到高：1NF、2NF、3NF、BCNF、4NF、5NF。范式越高阶，冗余度越低，同时高阶范式一定符合低阶范式的要求。

键的种类

为了说明键的种类，我们用两张表作为例子：

球员表：球员编号、姓名、身份证号、年龄、球队编号
球队表：球队编号、主教练、球队所在地

  • 超键：能唯一表示元组的属性集叫做超键。对于球员表来说。超键就是包含球员编号或身份证的任意属性组合。如（球员编号）、（球员编号，姓名）、（身份证号，年龄）等。

  • 候选键：不包含多余属性的超键。即最小的超键，对于球员表来说，就是（球员编号）或（身份证号）。

  • 主键：可以从候选键中选择一个作为主键。自己选定，比如（球员编号）。

  • 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。如球员表中的球队编号。

  • 主属性：包含在任意候选键中的属性称为主属性。在球员表中，主属性是（球员编号）、（身份证号）。

  • 非主属性：不包含在任意候选键中的属性。在球员表中，非主属性是（姓名）、（年龄）、（球队编号）。

1NF

数据库表中的任何属性都是原子的，不可再分。

我们在设计某个字段的时候，对于字段 X 来说，不能把字段 X 拆成 X-1、X-2。

2NF

首先满足第一范式，然后数据表里面的非主属性都要和这个数据表的候选键有完全依赖关系。完全依赖指不能仅依赖候选键的一部分属性。

比如下面的例子：

球员比赛表：球员编号、姓名、年龄、比赛编号、比赛时间、比赛场地

候选键和主键都是（球员编号，比赛编号），即： （球员编号，比赛编号）=> （姓名，年龄，比赛时间，比赛场地）

但是此表还有如下关系： （球员编号）=> （姓名，年龄） （球队编号）=> （比赛时间，比赛场地）

不满足第二范式。比如非主属性姓名并非完全依赖于候选键，而是依赖于候选键的一部分。

若不满足第二范式，会产生的一些问题：

  • 数据冗余：球员可以参加 m 场比赛，那么姓名和年龄就重复了 m - 1 次；一场比赛也有 n 个球员，比赛时间和地点就重复了 n - 1 次。

  • 插入异常：如果想添加一场比赛，但是参赛的球员还没确认，那么就没法插入。

  • 删除异常：如果想删除某个球员，那么比赛信息也会被删掉。

  • 更新异常：如果要调整比赛时间，那么数据表中这个比赛的数据都要调整。

所以，可以把上述的表设计为如下三张表：

球员表：球员编号，姓名，年龄
比赛表：比赛编号，比赛时间，比赛场地
球员比赛关系表：球员编号，比赛编号，得分

3NF

首先满足第二范式，然后任何非主属性都不传递依赖于候选键。即任何非主属性不依赖于其它非主属性，必须直接依赖于候选键。

比如有如下表：

球员表：球员编号，姓名，球队名称，球队教练

这张表的依赖关系为： （球员编号）=> （姓名，球队名称） （球队名称）=> （球队教练）

若要满足 3NF，可以把表设计为：

球员表：球员编号，姓名，球队名称
球队表：球队名称，球队主教练

BCNF

首先满足第三范式，然后主属性必须完全依赖于候选键且不传递依赖于候选键。

比如有如下表：

仓库管理关系表：仓库名，管理员，物品名，数量

候选键为：（管理员，物品名）、（仓库名，物品名） 主键选择：（仓库名，物品名） 主属性为：仓库名，管理员，物品名 非主属性为：数量

由此可见，这张表是满足 3NF 的，但是不满足 BFNF，因为主属性仓库名部分依赖于候选键（管理员，物品名）。那么会存在如下问题：

  • 插入异常：增加一个仓库，但是还没存放任务物品，所以不能插入。

  • 更新异常：仓库更换管理员，需要修改多条记录。

  • 删除异常：若仓库的商品都卖空了，那么仓库名称和管理员也会被删除。

若要满足 BCNF，可以把表设计为：

仓库表：仓库名，管理员
库存表：仓库名，物品名，数量

反范式设计

越高阶的范式得到的数据表越多，数据冗余度越低。但是有时为了性能和读取效率，我们需要违反一些范式，因为联表查询效率较低。比如我们有如下两张表：

商品评论表：商品评论 ID，商品 ID，评论内容，评论时间，用户 ID
product_comment: comment_id, product_id, comment_text, comment_time, user_id

用户表：用户 ID，用户昵称，注册时间
user: user_id, user_name, create_time

如果我们需要查询某商品的前 1000条评论：

SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p
LEFT JOIN user AS u
ON p.user_id = u.user_id
WHERE p.product_id = 1001
ORDER BY p.comment_id DESC LIMIT 1000;

此 SQL 查询效率不高，因为关联两张表，进行聚集索引扫描，然后 再嵌套循环，非常耗时。如果我们想提升查询效率，允许适当的数据冗余，那么可以设计如下表：

商品评论表：商品评论 ID，商品 ID，评论内容，评论时间，用户 ID，用户昵称
product_comment: comment_id, product_id, comment_text, comment_time, user_id, user_name

同样的查询条件只需要单表查询：

SELECT comment_text, comment_time, user_name FROM product_comment
WHERE product_id = 1001
ORDER BY comment_id DESC LIMIT 1000;

由此可见反范式是通过空间换时间，提升查询效率。但是反范式也存在一些问题：

  • 数据量小时，不能体现性能优势。

  • 数据更新需要保持一致性，如修改用户昵称，需要在两张表修改。

反范式的适用场景是什么呢？在实际业务场景中，我们可能需要冗余信息，比如订单中的收货人姓名、电话、地址，每次发生订单这些信息都属于历史快照，需要保存。就算用户修改了自己的电话，订单的电话也不变。

所以如果冗余信息有价值，或者能大幅提升查询效率时，我们可以采取反范式设计。

另外数据仓库设计也常采取反范式，因为数据仓库通常存储历史数据，不常增删改，但分析数据需求强烈，所以可以适当数据冗余，方便数据分析。

总结

1NF 需要保证表中每个属性都保持原子性；2NF 需要保证表中的非主属性与候选键完全依 赖；3NF 需要保证表中的非主属性与候选键不存在传递依赖。

范式等级越高，数据冗余度越小，但是设计出来的数据表就越多，进行数据查询的时候就可能需要关联多张表，从而影响查询效率。

基本使用

安装

# 仅安装客户端
yum install mysql
brew install mysql

# 仅安装服务端
yum install mysql-server

Docker 启动

docker pull mysql:8.0.16

docker run -d --name mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123qwe \
-v /Users/zhaoyun/Downloads/mysql:/var/lib/mysql \
mysql:8.0.16

基本命令

# 连接 MySQL
mysql -u {username} -p {password} \
    -h {remote server ip or name} -P {port} \
    -D {DB name}
    
mysql -u pcopadm -h 127.0.0.1 -P 3306 -p

调优

数据库调优的目的是让数据库运行更快，响应时间更短，吞吐量更大。

但是怎么反馈快呢？一般可从如下几点：

  • 用户反馈

  • 日志分析

  • 服务器资源监控

  • 数据库内部监控

数据库调优的思路一般有如下几个步骤：

  • 选择合适的 DBMS

  • 优化表设计

    • 尽量遵循三范式

    • 若分析查询较多，尤其是联表查询，可采用反范式，即空间换时间

    • 选择合适数据类型。数值型优于字符型；字符型长度尽量短；字符型若长度确定用 CHAR

  • 优化逻辑查询，即通过改变 SQL 内容让 SQL 执行效率更高

  • 优化物理查询，比如高效地建立索引与合理地使用索引

  • 使用缓存

  • 库优化，如控制表数量、主从、分库分表

PreviousMetaNextSQL

Last updated