1.关系数据库mysql全面总结

学习思维导图

---

重要网站导航

Mysql官网

• 这里详细记录是sql优化，sql操作等重要且权威的mysql知识

InnoDB引擎衍生版的数据库

• perconaDB官网
• perconaDB官网

重要知识参考

索引下推

索引下推介绍

---

执行引擎和数据库事务

·mysql默认的事务隔离等级为可重复读,因为使用了MVCC机制效率高，同时解决了幻读问题

🥅事务的特点

事务具有原子性，一致性，隔离性，持久性也就是所谓的ACID
事务的原子性：保证事务内的sql执行要不全成功，要不全失败，不会部分成功
事务的一致性：保证各个事务执行的结果符合真实逻辑
事务的隔离性: 保证了事务在执行的时候，不会被别的事务执行结果所影响（影响程度受到事务隔离级别影响）
事务的持久性：保证了事务执行结果保存到数据库中

事务的隔离级别分为四级：
读未提交：一个事务可以可以读取另一个事务中未提交的数据，产生脏读问题
读已提交： 一个事务只可以读取已提交的数据，产生不可重复读的问题
可重复读： 一个事务去读取其他提交的数据的时候，另一个事务再这期间修改了数据并提交了，但是这个事务读取的结果仍然是以前的，产出幻读问题（虽然可以读到不重复的数据，但是新增加的数据不可避免的要重新读）
可串行化：可串行保证了数据必须加锁才可以读写，读写锁互斥，严格保证数据安全。

事务中存在的脏读，脏写，不可重复读，幻读问题
脏写：多个事务同时操作一个数据，因为没有加锁导致刚写人的数据立马被覆盖了，后面的操作无法获得之间写人的真实数据的问题，而产生的数据脏写问题。使用事务隔离即可
脏读：一个事务读到了另一个事务执行中间过程是数据，而导致之后计算结果产生一致性问题。使用读已提交解决
不可重复读：一个事务执行过程中获得了一个事务提交后的结果，之后另一个事务对这个结果做了修改再次提交，次事务再次获得这个结果的时候，数据不一致了，也就是每次读同一个数据导致结果不一致。解决办法使用可重复读的事务隔离级别，保证读取的是事务第一次获得的结果。这样可以保证之后操作的结果是在第一次事务获得结果的基础上计算而来的。注意结果是从第一获得的结果基础上拿到的，具体业务中要考虑这个时间点基准，防止因为业务逻辑导致的数据不一致
幻读：对指定的数据进行查询，但是另一个事务会再次插入数据，导致每次查询的结果不一致。使用可序列化事务，严格保证数据的执行顺序

🥅事务的执行的实现原理

事务的原子性和一致性以及持久性是通过undo,redo日志保证
事务的隔离性是通过锁机制保证的

⚽事务持久性和原子性保证：undo和redo日志

redo日志：重做日志，提供再写人操作的，恢复提交事务修改的页操作，用来保证事务的持久性。他是存储引擎记录物理层面上需要修改或者插入数据的位置，值等信息
undo日志 回滚日志，回滚记录到某个特定的版本，保证事务的原子性和一致性。他是记录了逻辑层面上数据的修改插入的反操作。用于事务回滚和非锁定读

• undo日志分别针对插入，删除，更新操作有不同的策略，参考连接总的分为两类insert undo 和 update undo
  • insert undo 是针对数据插入的，每次事务提交之后就可以立马清理日志记录
  • update undo是针对数据删除和更新，每次事务提交后也不会立马清除，需要等待purage线程判断是否被其他事务需要后才可以删除，一般配合mvcc机制使用

🎾redo日志的缓冲区内存格式

redo日志是最后被存在文件夹里的日志，存放之前会先在内存中有一个缓冲区来存放它，它的格式如下：

🎾undo日志缓冲区的内存格式

undo日志只是记录在内存中的一些日志，并不会持久化到文件中。它的具体内存结构分为两级，一个是用来保存日志的最小单位结构称为日志页,一页数据记录一个次undo操作，而管理这些日志页的数据结构为回滚段。

• 事务操作中，先申请回滚段(rollback seegment)的段空间（undo log segement），每个事务只能申请一个回滚段，申请回滚段后，这个事务的所有更新操作产生的undo日志都会保存到这个段内，回滚日志之间是通过链表连接的，所以不用管内存的物理地址是否连续，所有的操作产出的undo页可以混合存放在一个回滚段内。默认一个回滚段内有1024个段空间undo log segment。具体的内存格式如下：undo日志总的存储逻辑区域是undo tablepace，有两个，每个talbespace内部有128个回滚段，每个段内有1024个slot用来保存undo日志，具体如下：参考连接
  
• Undo log segmen里面存储了一个一个真正的undo日志记录 ，回滚段roll segmet和段空间undo log segmet之间的关系如下两图所示
  
  

总结参考连接

⚽事务隔离性保证：锁

事务隔离级别的实现都是通过加锁来实现的，

• 不同的隔离级别加的锁也不同，但是这种锁一般都是系统给隐式添加的（sql层进行添加的），不需要我们手动指定，因此只需要了解原理，指定相关的事务即可，指定事务就是指定相关的锁。
  换句话说就是事务封装了锁，减少了我们编码的难度
• 事务中的使用的锁最重要的是读写锁——读锁共享，写锁排他，读写互斥。
• 在读写锁的基础上对读锁优化，使用MVCC机制，将读锁变为乐观锁锁，以增强并发访问效率

数据库中用到的锁总结如下：

🎾MVCC实现原理

mvcc的目的是在不加读锁的情况下保证事务隔离的时候不产生幻读和不可重复读的问题，通过不加锁是方式实现了可重复读到可串行读的升级。

• 针对不可重复读问题需要解决一个重要问题，判断那些事务时当前事务可见的，也就是那些事务是已经提交的，通常做法是加读锁保证别的事务无法修改，让读取的数据是最新提交的事务，而mvcc通过不加读锁的方式也能实现获得最新提交事务的结果。

可重复

1. innodb自身维护了一个当前表中活跃的事务（未提交事务）的id`

2. 事务中的第一次select操作生成一个ReadView, 每个查询事务对于一个单独的readview``这个过程中有事务对这个数据进行更新删除操作形成版本链

   • readview中保存了如下数据：
     • select对应的事务id，如果是只读事务那么id为0
     • 系统记录的活跃的事务id
     • 活跃事务的最小id
     • 分配给下个要发生事务的id

3. 当前事务开始查询readview，

   • 如果访问数据的事务id等于Readview的事务id可以访问（注意这个select的事务特殊：事务id是不是0，但是对于的readview的create_id是0）
   • 如果事务id在活跃的事务id内或者大于最大的事务id，那就不可以访问
   • 如果事务id小于活跃的事务id就可以访问（这种情况下的事务都是只读事务）

4. 满足访问规则就可以访问数据

5. 没有满足访问规则，那得去undo日志中重新选择上一个版本的数据作为当前事务id和readview的事务id做比较,再做判断,undo日志的选择过程是从事务id大的开始往小的选`，

   • 这个过程通过查找undo日志更新当前比较的事务id，以实现数据查找功能的继续运行。

总结如下

• 实现的关键就是对事务是自增的唯一的，一个事务访问数据，数据就会生成readview,readview会对比事务的版本，确定当前事务是否是已提交事务，当然肯定不是已提交事务了，然后通过查询当前记录的版本号把这个事务的id往后推，直到找最近一次的已提交事务id后，获得这个记录的结果作为查询结果。
• select语句的id和readview的id不同，保证了单纯的查询事务操作不会直接获得当前数据的值。而更新语句就可以直接获得当前最新的值进行后续操作。
• 此外读已提交每次select都会获得一个新的readview，可能导致不可重复读和幻读，而可重复读的事务隔离只会在第一个select中获得一个readview的结果，之后都使用这一个readview的内容作为对比依据。

幻读
对比过程和重复读的对比过程一样，在可重复读的模式下，由于只获得一次readview，可以保证之后添加的语句对应的事务都被过滤掉，而无法访问新添加数据，从而保证每次获得的结果是一样，即看不到新添加数据。

⚽ 事务一致性保证：事务的隔离，原子，持久共同保证了事务一致性

InnoDB的数据组件结构

一条sql的执行顺序

从两个角度说明sql的执行顺序，分别是数据库架构和sql关键字的先后顺序

数据库架构

关键字执行顺序

二进制日志和redo日志的两阶段提交和恢复

两阶段提交是redo在事务提交前写入的内容为prepare阶段，事务提交后在写入binlog和redo log设置为commit阶段

数据恢复

1. 如果事务提交前数据宕机没有记录，因为数据没有提交，所以使用undo日志回滚即可
2. 如果事务提交后写入binlog的时候宕机，这时候binlog没有的记录，redo有，但是处于prepare，那就回滚
3. 如果redo log设置commit的时候宕机，因为binlog存在数据，所以不会回滚。

总结
数据回滚以binlog为主，binlog有了就不回滚，binlog没有就会回滚