【数据库】1.mysql相关知识(六)

数据库设计

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中一个实体有关系
    

3.4.3 多对多(M:N)

• 多对多，指的是
  • 实体集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 表锁举例：

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才可以修改。这就是死锁。
• 锁的学问很大，可以自己学习。