MySQL

MySQL

关系数据库设计理论

函数依赖

• 记A->B为A能决定B，当A确定了，B也就能唯一确定了，此时就是B函数依赖A，多对一可行
• 完全函数依赖，B依赖于集合A中所有的属性，一旦A中少了某个属性，A就不能唯一确定B了
• 部分函数依赖，与完全函数依赖相对，B只依赖于A集合中的某些属性
• 传递函数依赖，只B依赖与A，C依赖于B，则称C传递函数依赖A，此时C和A的关系就是传递函数依赖

三大范式

• 第一范式
  • 每列的属性不可再分，当然这个是看实际情况，比如年月日，如果觉得没必要再分，就可以认为满足第一范式，如果说实际中需要再分，那就需要分，不能作为一列的属性
• 第二范式
  • 在第一范式的基础上，要保证非主属性完全函数依赖于主属性，不能出现部分函数依赖与主属性的情况
  • 比如：一张表有属性：学号、学生姓名、课程号、课程名称。对于这个表来说，主属性为学号和课程号，只有这两个确定了，才能保证每条记录唯一，但由于课程名称只需要课程号确定即可，所以存在部分函数依赖，不满足第二范式
• 第三范式
  • 在第二范式的基础上，需要保证非主属性不存在传递函数依赖与主属性。
  • 比如：一张表有属性：学号，学生姓名，学院号，学院电话。学号能唯一确定一条记录，但学院电话依赖于学院号，所以学院电话是传递函数依赖于学号，不满足第三范式
• 范式的优点
  • 数据不会冗余，每张表的数据都是与主属性直接关联并且完全函数依赖
    • 部分函数依赖，那么就有可能冗余，比如某些属性A函数依赖主属性的部分属性B，则一旦主属性的属性B一样，则记录中的属性A就会一样，导致冗余，因为主属性虽然每个都不一样，但是主属性的部分属性可能会一样，一旦一样，就会出现冗余
    • 间接函数依赖，和上面有些类似，因为不是直接依赖主属性，所以导致，主属性不一样的时候，中间那个起连接作用的属性可能一样，一旦一样那么就会出现数据冗余
  • 更新会更快，因为数据不冗余，更新只需要更新一条即可，不需要说一个条件还需要更新其他的冗余数据
  • 每张表的大小会更少，存储空间肯定也会更少，也是因为没有了数据的冗余
• 范式的缺点
  • 会增加查询的成本，因为分的表太多了，在一些复杂查询的时候，就得关联多个表，进而成本就提高了
  • 难以进行索引优化，每个表都有自己的索引，如果查询的字段都在一张表里，那么就可以在一张表里相关字段建立索引，这样就能更快，但是满足范式了，可能这些字段就不再一张表里了，就无法建立相关索引了

反范式

• 指在设计过程中不按照范式规则，增加数据的冗余，以提高查询的性能
• 反范式的优点
  • 可以使用索引来优化某些字段的查询
  • 可以避免某些表的关联，以此来减少查询的成本
• 反范式缺点
  • 表中冗余数据多，需要的存储空间肯定多些
  • 会造成某些异常进而导致数据不一致情况，删除或者修改某些数据时，可能多删或者多改了

MySQL体系架构

• MySQL架构是C/S架构，分别是客户层，Server层，存储引擎层
• [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pT6sYst0-1646209438264)(image/image-20220301170754915.png)]
• Server层
  • 连接器：管理连接，权限验证（登录验证，以及是否有操作的权限等验证）
  • 分析器：将SQL语句经过词法分析生成token，在经过语法分析生成抽象语法树，然后会检查语法是否错误
  • 优化器：一条SQL语句可能有不同的执行方式，此时就需要来权衡哪种执行方式最优，优化器会基于成本来生成最优的执行语句
  • 执行器：将优化器生成的执行语句执行，会负责与存储引擎进行交互
• 存储引擎
  • 是用来与数据文件进行操作的，不同的存储引擎的数据文件在磁盘的存储方式不同

事务

事物的概念

• 事务是一个程序的执行逻辑单元，里面包含了一系列对数据的访问和更新操作。

事物的特性

• 原子性：事务中的一系列操作要么全部完成，要么全部不完成
• 一致性：指事务执行的前后，数据要保证一致性
• 隔离性：指每个事务的执行不能影响其他事务的执行，每个事务之间是感觉不到其他事务的执行
• 永久性：事务执行成功后，是不可更改的，会永久的写到磁盘上面去，即使数据库崩溃也不会影响执行结果。
• 重点
  • 事务的一致性是操作目的
  • 原子性通过undo log来保证
  • 隔离性通过锁机制和MVCC机制保证
  • 永久性通过redo log来保证

事务并发以及隔离级别

• 事务的并发处理会带来一下问题，如下：

  • 数据丢失：当一个事务A对一个数据修改后，还未提交，另一个事务B又对这个数据进行修改并提交，导致A事务此时在访问该数据时，该数据不是自己修改的数据，自己的数据发生了丢失

  • 脏读：当事务A修改一个数据后，未提交，此时事务B对该数据读取，然后事务A进行了回滚操作，导致事务B得到的数据是个失效数据，也就是脏数据

  • 不可重复读：指的是事务A第一次读取一个数据时，和第二次读取的不一样，因为A在读取后，事务B对该数据进行修改了，导致事务A再次读取发现两次数据不一致

  • 幻读：其实和不可重复读类似，都是因为前后两次读取的数据不一样，但是不可重复读强调的是修改，而幻读强调的是对数据的插入和删除，导致的前后结果不一致

  • 注意

    • 每个事务就相当于一个客户端，要从客户端的角度，考虑这些问题

    • 以上出现的并发问题，可以通过加锁解决，也可以用MVCC来解决，但MVCC无法解决幻读问题，还是得加锁

• 隔离级别

  • 未提交读：会出现脏读，不可重复读，幻读
  • 已提交读：不会出现脏读，会出现不可重复读，幻读
  • 可重复读：不会出现脏读，不可重复读，会出现幻读。InnoDB存储引擎默认隔离级别
  • 串行化：均不会出现，但是并发度太低

锁

三级封锁协议

• 对于事务并发处理带来的问题，加锁是肯定能解决的，三级封锁协议就可以在一定程度上解决。

• 先介绍两种锁：读锁和写锁

  • 读锁：一个事务对数据加了读锁，那么该事务可以对数据进行读取，但不可以进行更新。可以防止，其他事务对该数据加写锁，但是其他事务仍然可以加读锁
  • 写锁：一个事务对数据加了写锁，那么该事务可以对数据进行读取，也可以进行更新。其他事务既不可以加读锁也不可以加写锁。

• 一级封锁协议

  • 指的是一个事务在修改数据前，需要对该数据加写锁，直到该事务提交后在释放锁。
  • 这样能避免出现数据丢失的问题，数据只能由一个执行的事务进行修改，该事务提交前，这个数据不会被其他事务修改覆盖

• 二级封锁协议

  • 在一级封锁协议的基础上，一个事务想要读数据，需要对该数据加锁，在读完后立即释放读锁。
  • 这样能避免出现脏读问题，如果说当前事务加读锁成功，说明该数据没有被任何执行过程中的事务加写锁，此时读到的数据一定不是脏数据，如果加读锁没成功，说明该数据被一个事务加了写锁，直到那个事务提交或者回滚后，此时才能加读锁，而此时的数据也不是脏数据，因为那个事务已经结束了

• 三级封锁协议

  • 在一级封锁协议和二级封锁协议的基础上，一个事务想要读数据，需要对该数据加锁，直到该事务提交后立即释放读锁。
  • 这样能避免出现不可重复读的问题。一旦加了读锁，其他事务不可加写锁，那么在该事务的执行过程中，该数据不会被其他事务修改，自然不会出现不可重复的问题。

• 注意

  • 三阶段封锁协议无法阻止幻读问题，因为不能阻止另一个事务新增数据

两阶段加锁协议

• 三级封锁协议能解决一些事务并发处理问题，那么两阶段加锁协议能够保证事务的可串行化，是一种更强的约束
• 加锁和释放锁分为两个阶段
  • 加锁阶段：只允许事务加锁，想读数据加读锁，想写数据加写锁，不能加则等待
  • 释放锁阶段：只允许事务释放锁
• 两阶段加锁协议会导致死锁问题：我想要的资源被你锁住了，你想要的资源被我锁住了。所以又引出了一种方法，一口气加上所有锁

锁的分类

按锁的粒度分

• 行级锁：锁住一行数据，也就是一条记录。
• 页级锁：锁住一页数据，也就是一组连续的记录
• 表级锁：锁住一个表的数据，将整个表的数据都给锁住了
• 三者的优缺点
  • 行级锁：粒度最小，并发度最高；但加锁很慢；会出现死锁
  • 表级锁：粒度最大，并发度最低；加锁很快，开销小；不会出现死锁
  • 页级锁：之间；之间；会出现死锁
• 意向锁
  • 能更容易地支持多粒度加锁。在多粒度的情况下，比如想在一个加了行锁的表上，加表锁，那么这个事务就得遍历表中的每一行，判断是否加锁，这样效率比较低，因此就引入了意向锁。
  • 意向锁是对上一级粒度加锁的。
    • 一个事务A想对表中的某一行数据加读锁，它需要先对该表加意向读锁或意向写锁。在获得意向锁后才能对该数据加锁。
    • 一个事务A想对表中的某一行数据加写锁，它需要先对该表加意向写锁。在获得意向锁后才能对该数据加锁。
  • 意向锁的兼容性
    • 意向锁之间是互相兼容的
    • 一个事务想加表的写锁不兼容意向写锁和读锁，因为一个表加了意向写锁或意向读锁后，说明肯定有个事务对该表中的某一行记录加锁了，此时是不允许对该表加写锁的。
    • 一个事务相加表的读锁，是兼容意向读锁，但不兼容意向写锁

按照读写分

• 读锁（共享锁）
• 写锁（排他锁）

按照是否加锁分

• 乐观锁：比较乐观，认为数据不会被别人修改，就不加锁，等到更新数据的时候再看看有没有人修改。MVCC机制就是一种乐观锁，常用于都多写少的情况
• 悲观锁：比较悲观，总认为数据会被别人修改，所以操作数据前都先加锁，再操作

其他锁

• 间隙锁：锁的是记录之间的间隙，不是具体记录索引。不允许插入操作，用来解决幻读问题
• 记录锁：锁的是记录索引
• next-key锁：锁的是一个间隙加记录，innoDB默认是这种锁，可以用来解决幻读

MVCC

• MySQL的默认存储引擎InnoDB默认的隔离级别是RR，且用MVCC机制来解决脏读和不可重复读问题，再用next-key锁的机制来解决幻读
• 发现一个好的介绍很详细：InnoDB 中的锁和 MVCC

日志

• 两阶段提交，是说redo log先prepare状态，然后改bin log，在改成commit状态

索引

• 链接1
• 链接2：主要有索引失效情况说明

MySQL的存储引擎

• 链接1：介绍了InnoDB和MyISAM区别

常见面试题

• 链接