数据库设计
1. 软件项目开发周期中的数据库设计
- 需求分析阶段:分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图,确认需求的正确和完整性
- 详细设计阶段:应用三大范式审核数据库
- 代码编写阶段:物理实现数据库,编码实现应用
- 软件测试阶段
- 安装部署
2. 设计数据库的步骤
2.1 收集信息
- 与相关人员进行交流、访谈充分了解用户需求,理解数据库需要完成的任务
2.2 标示实体(Entity)
- 标识数据库要管理的关键对象或者实体,实体一般是名词
2.3 标示实体的属性(Attribute)
2.4 标示实体之间的关系(RelationShip)
3. 数据库ER图
- ER图:实体关系同,简记E-R图,是指以实体、关系、属性三个基本概念概括数据的基本结构,从而描述静态数据结构的概念模式
符号 | 含义 |
---|---|
长方形
| 实体,一般是名词 |
椭圆形
| 属性,一般是名词 |
菱形
| 关系,一般是动词 |
3.1 ER图的实体(entity)
- ER图的实体(entity)即数据模型中的数据对象,例如人、学生、音乐都可以作为一个数据对象,用长方形来表示
3.2 ER图的属性(attribute)
- ER图的属性(attribute)即数据对象所具有的属性,例如学生具有姓名、学好、年级等属性,用椭圆形表示
- 属性分类
- 唯一属性:唯一可用来标识该实体实例或成员的属性,用下划线表示,一般来讲实体至少有一个唯一属性。
- 非唯一属性
3.3 ER图的关系(relationship)
- ER图的关系用来表现数据对象和数据对象之间的联系
- 例如:
- 学生的实体和成绩表的实体之间有一定的联系,每个学生都有自己的成绩表,这就是一种关系,关系用菱形来表示
3.4 ER图中的关联关系
3.4.1 1对1(1:1)
- 1对1关系,指:
- 对于实体集合A与实体集合B,A中的每一个实体之多与B中一个实体有关系;反之,在实体B中的每个实体至多与实体集A中有一个实体关系。
学生
身份证信息
- X ---------------------------------------------------Y
- X ---------------------------------------------------Y
- X ---------------------------------------------------Y
- X ---------------------------------------------------Y
3.4.2 1对多(1:N)
- 1对多关系,指
- 实体集A和实体集B中至少有N(N>0)个实体有关系;并且实体B中每一个实体至多与实体A中一个实体有关系
- 实体集A和实体集B中至少有N(N>0)个实体有关系;并且实体B中每一个实体至多与实体A中一个实体有关系
3.4.3 多对多(M:N)
- 多对多,指的是
- 实体集A中的每一个实体与实体集B至少有M(M>0)个实体有关系,并且实体集B中的每一个实体与实体集A中的至少N(N>0)个实体有关系。
- 实体集A中的每一个实体与实体集B至少有M(M>0)个实体有关系,并且实体集B中的每一个实体与实体集A中的至少N(N>0)个实体有关系。
4 数据库设计的三大范式
4.1 不合理的表设计
- 信息重复
- 更新异常
- 插入异常
- 删除异常
4.2 三大范式
4.2.1 第一范式(1NF)
- 数据表中的每一列(每一个字段)都必须是不可拆分的最小单元,也就是确保每一列的原子性
ID | 地址 |
---|---|
1 | 中国上海 |
2 | 美国曼哈顿 |
3 | 英国伦敦 |
4 | 日本大阪 |
… | … |
应该根据第一范式转化为:
ID | 国家 | 城市 |
---|---|---|
1 | 中国 | 上海 |
2 | 美国 | 曼哈顿 |
3 | 英国 | 伦敦 |
4 | 日本 | 大阪 |
… | … | … |
4.2.2 第二范式(2NF)
-
满足1NF后,要求表中的所有列,都必须依赖于主键,而不能有任何一列与主键没有关系,也就是说一个表只能描述意见事情
-
举例:如下订单表,主键是订单编号,就不满足第二范式要求,因为:
- 每列都需要跟主键有关,而身份证号等个人信息那么多,跟订单编号没有关系
- 一个人同时顶几个房间,就会出来一个订单号多条数据,这样子联系人都是重复的,就会造成数据冗余
- 所以可以转成一张订单表跟一张联系人表,如下面第二和第三张表
订单编号 | 房间号 | 联系人 | 联系人电话 | 身份证号 |
---|
优化后的表有如下两张:
订单编号 | 房间号 | 联系人编号 |
---|
以上是订单表
联系人编号 | 联系人 | 联系人电话 | 身份证 |
---|
以上是联系人表
4.2.3 第三范式(3NF)
- 满足2NF后,要求:表中的每一列只与主键又直接关系,而不是间接关系(表中的每一列只能依赖于主键)
- 数据不能存在传递关系,即每个属性都跟主键又直接关系而不是间接关系
学生编号 | 学生姓名 | 年级ID | 年级名称 |
---|---|---|---|
1 | 张三 | 1 | 年级 |
主键为学生编号,学生姓名依赖主键,年级ID依赖主键,但是年级名称是依赖年级ID,而不是直接依赖主键学生编号,所以需要拆分成如下两个表
学生编号 | 学生姓名 | 年级ID |
---|---|---|
1 | 张三 | 1 |
以上是学生表
年级ID | 年级名称 |
---|---|
1 | 一年级 |
以上是年级表
4.2.4 如何更好的区分三大范式
- 第一范式和第二范式的区别在于有没有分出两张表,第二范式是说明第一张表中包含了多种不同的实体属性,那么要必须分成多张表
- 第三范式是要求已经分成了多张表,那么一张表中只能有另一张表中的id(主键)。而不能有其他的任何信息(其他的信息一律用未安检在另一张表的主键查看)
5. RBAC
- 基于角色的权限访问控制(Role-Based Access Control)
- RBAC就是用户通过角色与权限进行关联
- 简单地说,一个用户拥有若干橘色,每个橘色拥有若干权限,每个权限可以操作若干资源,这样就构造成"用户-角色-权限-资源"的授权模型
- 在这种模型中,用户与角色之间,橘色与权限之间,权限与资源之间,一般都是多对多的关系
- 在RBAC中最重要的概念包括:用户(User)、角色(Role)、权限(Permission)、资源(Resource)
5.1 安全原则
- 最小权限原则
- RBAC可以将其角色配置成其完成任务所需要的最小的权限集
- 责任分离原则
- 可以通过互斥的角色来共同完成敏感的任务
- 比如:记账员和财务管理员共同过账
- 可以通过互斥的角色来共同完成敏感的任务
- 数据抽象可以通过权限的抽象来体现
- 比如财务操作用借款、存款等来替代操作系统提供的典型的读、写、执行权限。
6. 事务
6.1 为什么需要事务
- 银行转账问题
- A账户资金减少
- B账户资金增加
CREATE DATEBASE bank;
use bank;
CREATE TABLE account
(
name varchar(64),
balance decimal(10,2)
)
INSERT INTO account(name,balance) VALUES('张三',100);
INSERT INTO account(name,balance) VALUES('李四',100);
UPDATE account SET balance = balance - 10 WHERE name = '张三';
UPDATE account SET balance = balance + 10 WHERE name = '李四';
6.2 什么是事务
- 事务是作为单个逻辑工作单元执行的一系列操作
- 多个操作作为一个整体向系统提交,要么都执行,要么都不执行
- 事务是一个不可分割的工作逻辑单元
转账过程就是一个整体,它需要两条UPDATE语句,如果任何一个出错,则整个转账业务取消,两个账户余额都恢复到原来的数据,确保总月不变
6.3 事务的特性ACID
- 原子性(Atomicity)事务是一个完整的操作,事务各个部分是不可分的,要么都执行,要么都不执行
- 一致性(Consistency)当事务完成后,数据必须处理完整的状态
- 隔离性(Isolation)并发事务彼此隔离、独立,他不应该以任何方式依赖于其他事务
- 持久性(Durability)事务完成后,它对数据库的修改被永久保持
6.4 如何创建事务
- 开始事务 START TRANSACTION 或者 BEGIN
- 提交事务 COMMIT
- 关闭/开启自动提交状态 SET AUTOCOMMIT=0/1 0关闭 1开启
关闭自动提交后,从吓一跳SQL语句开始开启新的事务,需要使用COMMIT或ROLLBACK结束该事务
7. 锁
- 锁时计算机协调多个进程或线程并发访问某一资源的机制
7.1 锁的分类
- 从对数据库操作的类型分类,分为:
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响,但是不能删除跟修改
- 写锁(排它锁):当前写操作没有完成前,它会阻断其他写锁和读锁,也就是我写完前,别人不能读也不能写
- 读锁会阻塞写,但不会阻塞读;而写锁则会把读和写都阻塞。
- 从对数据操作的粒度分,分为
- 表锁:把整个表锁住,别人就不能操作表内的任何内容
- 行锁:可以锁定某一行,锁定的行,别人不能操作,其他没锁定的可以操作
8.2 表锁
- 表锁偏向MyISAM存储引擎,开销小,加锁快,锁定粒度大,发生锁冲突的概率最高,并发度最低
- MyISAM存储引擎:操作非常快,但不支持事务
- INNODB存储引擎:操作慢,但支持事务
- 语法:
- 加一个锁:LOCK TABLE 表名1 read(write),表名2 read(write);
- read表示加的是读锁
- write表示加的是写锁
- 解锁:UNLOCK TABLES;
8.2.1 表锁举例:
- 准备数据
CREATE TABLE users(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM DEFALUT CHARSET=utf8;
INSERT INTO users(id,name) VALUES(1,'a');
INSERT INTO users(id,name) VALUES(2,'b');
INSERT INTO users(id,name) VALUES(3,'c');
INSERT INTO users(id,name) VALUES(4,'d');
LOCK TABLE 表名1 read(write),表名2 read(write);
8.3 行锁
- 行锁偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁,锁定粒度小,发生锁冲突的概率最低,并发度也最高
- InnoDB与MYISAM的最大不同是:
- 前者支持事务,后者不支持
- 前者采用了行级锁,后者是表级锁
8.3.1 行锁支持事务
8.3.1.1 并发事务处理带来的问题
8.3.1.1.1 更新丢失(Lost Update)
-
当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新。
-
后面的事务覆盖了前面的值
-
举例:
- A同学建立了一个事务,正在修改数据,且没有提交。B同学也建立了一个事务,也在修改数据,且没有提交。
//A同学 set autocommit=0;//关闭自动提交 begin; select * from user; update user set age = 90 where name='张三'; //此时正在修改,并没有commit提交 //B同学 set autocommit=0;//关闭自动提交 begin; select * from user; update user set age = 80 where name='张三'; //此时正在修改,并没有commit提交
- 这时候A先提交,B也提交了。
- A提交完,张三是90岁,B提交完,张三成了80岁。B的提交覆盖了A的提交,而A并不知道B在提交,所以A以为现在张三还是90岁,其实已经被后提交的事务覆盖成了80岁。
8.3.1.1.2 脏读(Dirty Reads)
- 一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致的状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此作进一步的处理,就会产生未提交的数据依赖关系。这种现象叫做“脏读”
- 一句话:事务A读取了事务B已经修改但尚未提交数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取的数据无效,不符合一致性要求。
- 脏读是事务B修改了数据,这是不正常的
- 解决办法:如果在第一个事务提交前,任何其他事务不可读取其修改过的值,则可以避免该问题。
8.3.1.1.3 不可重复读(Non-Repeatable Reads)
- 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象叫"不可重复读"。
- 一句话:事务A读取到了事务B已经提交的修改数据,不符合隔离性,这是不正常的。
- 解决办法:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。
8.3.1.1.4(Phantom Reads)
- 一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就成为“幻读”。
- 一句话:事务A读取到了事务B提交的新增数据,不符合隔离性
- 幻读是事务B里面新增了数据,这是不正常的
- 解决办法:如果在操作事务完成数据处理之前,任何其他事务都不可以添加新数据,则可避免该问题。
8.3.1.2 事务隔离四种级别
- read uncommited:读取尚未提交的数据:就是脏读
- read committed:读取已经提交的数据:可以解决脏读
- repeatable read:重读读取:可以解决脏读和不可重复读 (mysql默认的)
- serializable:串行化:解决脏读、不可重复读和幻读 (相当于锁表)
8.3.1.2.1 事务隔离操作
- 查看当前表的事务隔离等级
SELECT @@tx_isolation;
- 修改表的事务隔离等级
set session transaction isolation level read uncommitted;//修改成了脏读等级
8.3.2 死锁
- A在修改数据库,B想改A在改的那个数据库,那么B是修改不了的,要等A释放,B才可以修改。这就是死锁。
- 锁的学问很大,可以自己学习。