说一说数据库三大范式

1. 第一范式(每列原子性)

第一范式要求所有的域都是原子性的,即每一列都不可(不须)分割。
以学生表为例,学生表有姓名、学号、年龄、性别、家庭住址等信息组成。在这个表中,可以看作所有列都不可分割(假设地址中的省、市、区、详细地址在本应用中是组合在一起的,不会拆分开来用。从这一点来看,地址列不可/不须分割),所以是满足第一范式的。
在这里插入图片描述
第一范式的合理性也需要结合实际需求来,上表满足第一范式是有一个前提条件,就是地址中的省、市、区、详细地址在本应用中是组合在一起的,不会拆分开来用。 假设本应用中需要把省、市、区、详细地址拆分开来使用,比如统计某省、某市、某区有多少学生,从而安排合适的校车接送。那上述设计就不满足数据库的第一范式了。此时就需要把地址拆分开来,才满足第一范式。
在这里插入图片描述
每列是否满足原子性是根据当前应用来判断的,并不是绝对的,就像本例中的地址一样。甚至是姓名在不同的应用中也可以说它满足满足或不满足原子性(假设需要把姓和名拆开…),所以在实际项目中一定要灵活处理。

2. 第二范式(要有主键,且实体属性完全依赖主键)

第二范式首先要求数据表必须要有主键,可以是单列构成的主键也可以是多列组合构成的主键。满足第二范式的表一定要先满足第一范式。
为什么要有主键?
因为要确保这一行数据的唯一性,因为主键本身的唯一性,就保证了这一行数据的唯一性,这样才能定位到这行数据。就像学生信息表,学号便可以当作主键来用,因为学号一定唯一。而姓名不可,因为可能会有重名,重复了就没有唯一性了。
实体属性完全依赖主键是什么意思?
是指每一列都和主键相关,并且不能只与主键的某一部分相关(主要针对联合主键)。

2.1 非联合主键情况

就像上面的学生信息表,主键number = 2020010223就已经决定了当前这一列归学号为2020010223的小明同学所有。这一列只能存放小明的年龄、性别、家庭住址等信息,而不能把小红的年龄、性别、家庭住址存放到这一列。如果把小红的年龄、性别、家庭住址存放到这一列,那这些属性就和主键没有任何关系了。

接下来再举个稍微复杂点的例子。
在这里插入图片描述
这张表里的班级名、语文老师名、英语老师名依赖小明吗?
不依赖!!!即使没有小明这个学生,也依然有高二三班,一个叫李明的语文老师和一个叫托尼的英语老师。所以说上述设计不满足数据库设计的第二范式。可以按照以下方式将上表更改成满足第二范式的 :
将班级信息单独抽取出来放到一张表中,在原来的学生信息表中新添加班级id列,作为外键。

学生信息表:
在这里插入图片描述
班级表:
在这里插入图片描述
这样一来就满足数据库设计第二范式了。

接下来我们再看点更复杂的。

2.2 联合主键情况

在前面我们还提到了一点:不能只与主键的某一部分相关(主要针对联合主键),这是怎么回事呢?一起来看一下吧!

假设有一张订单表是这样的:
在这里插入图片描述
这张表的主键是由order_id和book_id联合组成的。

mysql> show create table orders;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                             |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `book_name` varchar(128) DEFAULT NULL,
  `count` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `customer` varchar(64) NOT NULL,
  `phone` varchar(32) NOT NULL,
  PRIMARY KEY (`order_id`,`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

在这张表中,book_name、price只与联合主键中的book_id有关,而和order_id无关。在这里违反了第二范式的设计原则。如果想把这张表设计成符合第二范式的,需要把这张表进行拆分。把订单信息放到一张表中(包含创建订单的客户以及联系电话)、把书籍信息放到一张表(书籍名称、单价)、最后再用订单项目表关联两者。

订单信息表:
在这里插入图片描述

mysql> show create table order_message;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                     |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order_message | CREATE TABLE `order_message` (
  `order_id` int(11) NOT NULL,
  `customer` varchar(64) NOT NULL,
  `phone` varchar(32) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

书籍信息表:
在这里插入图片描述

mysql> show create table book_message;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                          |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| book_message | CREATE TABLE `book_message` (
  `book_id` int(11) NOT NULL,
  `book_name` varchar(128) NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

订单项目表:
在这里插入图片描述

mysql> show create table order_item;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                   |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| order_item | CREATE TABLE `order_item` (
  `order_id` int(11) NOT NULL,
  `book_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`order_id`,`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

使用多表联合查询,查到的结果是一样的

select 	order_item.order_id,
		order_item.book_id,book_message.book_name,
		order_item.count,book_message.price,order_message.customer,order_message.phone 
  from 
  	order_message,book_message,order_item 
  where 
  	order_item.order_id = order_message.order_id and 
  	order_item.book_id = book_message.book_id;
+----------+---------+------------------+-------+-------+----------+-------------+
| order_id | book_id | book_name        | count | price | customer | phone       |
+----------+---------+------------------+-------+-------+----------+-------------+
|        1 |      12 | java核心技术 |     2 | 99.80 | 小葱   | 1111-123456 |
|        1 |      18 | 高性能MySQL   |     1 | 88.00 | 小葱   | 1111-123456 |
|        2 |      12 | java核心技术 |     2 | 99.80 | 嘿嘿   | 222-111111  |
+----------+---------+------------------+-------+-------+----------+-------------+
3 rows in set (0.00 sec)

在这里,我们为了让数据表符合第二范式,将数据表改成了“多对多”的形式。
这种设计方式极大限度的减少了数据冗余,可以有效提高更新速度。带来的缺点是通常需要关联,这将会带来昂贵的代价,也可能使一些索引无效。

3. 第三范式(消除依赖传递)

第三范式是第二范式的一个子集。第三范式要求,一个关系表中不能包含已在其他表中包含的非主键信息,换句话说同一个信息只在一个地方存储,不出现在多张表中。只要做到这一点,就满足第三范式了。
上面我们讲的学生、班级的例子就是满足第三范式的。

学生表:
在这里插入图片描述
班级表:
在这里插入图片描述
不满足第三范式的学生信息表:

学生表:
在这里插入图片描述
班级表:
在这里插入图片描述
学生表后面的班级名、语文老师名、英语老师名这三个字段和班级表重复了,因此不满足第三范式的设计要求。

看到这里你或许有点懵,或许你想问,第二范式和第三范式有什么区别?
第二范式是某张表,如果存在多种不同实体的属性,必须将这些属性单独拿出来拆分成另一张表。其前提是那些不完全依赖于主键的列还没有单独抽取出来设计成另一张表。第三范式是在已经存在多张表的情况下(那些不完全依赖于主键的列都在其他表中存在),一张表中只能有另一张表的ID,不能有其他字段信息。其他字段信息一律用主键在另一张表中查询

一定要满足第一范式才有可能满足第二范式,一定要满足第一第二范式才有可能满足第三范式。

4. 范式的优缺点

优点:

  1. 更新操作比反范式化更快
  2. 重复数据很少或没有
  3. 占用内存更少

缺点:

  1. 查询速度慢,因为通常需要对多表进行关联一次或多次,这将导致昂贵的代价。
  2. 可能使一些索引策略失效。例如,范式化可能将列存放在不同的表中,而这些列如果在一个表中本可以属于同一个索引。

5. 反范式的优缺点

优点:

  1. 避免了表与表之间的关联。
  2. 可以使用更有效的索引策略。
  3. 上面两条汇总起来就是查询起来更快。

总结

范式化只是一个参考标准,我们一定要遵守它吗?答案是否定的。在实际应用中经常混用范式化和反范式化,毕竟很多时候我们需要根据自己的实际需求,需要在数据冗余、更新代价以及查询代价上寻求一个平衡点

发布了41 篇原创文章 · 获赞 17 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览