MySQL知识点全面总结3：Mysql高级篇

三.MySQL知识点全面总结3：mysql高级篇

1.mysql语句的执行过程？

2.myesql事务详解？

3.mysql日志详解？

4.mysql的索引功能详解？

5.mysql的存储引擎详解？

6.mysql事务提交后数据与硬盘如何交互存储？回滚呢？

未更新

三.MySQL知识点全面总结3：mysql高级篇

1.mysql语句的执行过程？

①两个架构

• 客户端层
• 服务器层：
  连接器：身份认证
  分析器：语法分析：检查sql语句正确性。提取查询的表，查询条件等。
  缓存：缓存select语句的结果集
  优化器：多个索引时选择索引
  执行器：调用引擎接口返回结果
• 引擎层：
  存储引擎：具体执行存储操作。

②执行更新语句和查询语句

• 在存储引擎中过程不同
• 对于查询语句返回结果即可
• 对于更新语句：
  引擎将数据保存在内存中，记录redo log（预提交）
  通知执行器，执行器记录bin log，然后调用引擎提交redo log（提交）

2.myesql事务详解？

①为什么出现mysql事务？

• 因为假如插入多条相关联数据，中间宕机，则插入一般结果很差
• 引入事务，要么都执行，要么都不执行

②数据库事务执行过程

• start transaction
• sql1 sql2
• commit
• 执行过程分析
  为了提升性能，不会每次提交数据都写入磁盘。
  事务提交的数据先写入缓冲池，然后使用后台线程来更新缓存和硬盘一致性。
  如果没来得及同步，则通过redo log日志恢复一致性。
  事务提交了一半数据，还没来得及提交另一半，则通过undo log回滚数据。

③事务的属性

• ACID
  原子性，隔离性，持久性—>一致性
  原子性：要么都执行要么都不执行
  隔离性：事务之间互不影响
  持久性：事务提交的数据永久保留
  一致性：事务的结果前后一致（借了100块我有100块，加起来保证总共200块）

④事务产生的问题

• 脏读：A事务读到B事务未提交的数据（mysql读取内存的数据还未提交）
• 丢失修改：A事务和B事务同时读到同样数据，都加10，最后写入内存只是加了一次10。
• 不可重复读：A事务两次读A，第二次读到的被别人修改了的数据。
• 幻读：A事务读两次，第二次读入的多了几条数据。

⑤解决产生的问题的隔离级别

• 读未提交：什么都没解决（可以读到未提交的数据）
• 读已提交：解决脏读（只能读已提交的数据）
• 可重复读：解决脏读和不可重复度
• 可串行化：全部解决

⑤如何设置的隔离级别（锁+MVCC）

• 锁：悲观
  按读写分：读锁和写锁。读锁允许共享，写锁：排斥其他锁。也就是允许读读并行，不允许读写并行和写写并行。
  按表行分：表锁和行锁。
• MVCC：乐观
  隐藏字段+read view来查看当前数据的可见性
  undo log记录某行的版本数据

⑥解决事务的ACID

• 原子性：undo日志回滚实现
• 持久性：redo日志崩溃恢复数据实现
• 隔离性：锁+MVCC实现
• 最终保障一致性

3.mysql日志详解？

①redo log

• 不断写入
• 是InnoDB独有的，为了解决崩溃恢复能力

②bin log

• 事务提交时写入
• 为了保证集群架构一致性

③undo log

• 异常发生，记录所有事务的修改记录，出现异常回滚

④redolog 和binlog联合工作保证数据库的崩溃恢复能力，保证集群架构一致性。

• 为什么不用一个日志？
  mysql原来没有InnoDB引擎，InnoDB引擎是专门为了恢复崩溃数据的。
• 为什么redolog要引入prepare和commit状态？
  如果不引入，redolog写完后未提交给binlog前系统崩溃，则发生数据不一致。
  引入后，崩溃后发现redolog是prepare状态，那么就回滚事务。

4.mysql的索引功能详解？

①为什么引入索引？

• 索引能够帮助mysql高效的获取数据。目的为了加速数据库的查询时间。

②索引怎么实现的？

• 以空间换时间。
• 优点：大大增加了数据的检索速度。
  缺点：索引需要存储浪费空间，索引在数据修改时也会修改降低执行效率。

③索引底层的数据结构是什么？

• 索引的数据结构需要满足什么？
  空间局部访问性原理。
  减少IO次数。
  能够实现顺序查找。
• hash表
  数组+链地址
• 平衡二叉树
  二叉树
• B树
  索引和数据都在树中。平衡多叉树+每个节点有多个子节点
• B+树
  关键字只存储在叶子节点，非叶子结点不存储data，所有叶子节点都用链表相连。
• 为什么不用hash表结构呢？
  因为Hash表结构不支持顺序和范围查询。
• 为什么不用平衡二叉树呢？
  因为平衡二叉树每个节点一次IO过度浪费资源，且看似很近的数据存储离得很远，满足不了空间局部访问原理，实现不了顺序查找。（树的高度为IO次数）
• 为什么不用B树呢？
  空间局部性原理无法更好的利用。
• 为什么用B+树呢？
  由于节点不存储数据，故索引存储的范围更大更精确。即B+树相对于B树同样的IO能获得更多的索引即更精确的数据。
  由于B+树有链表连接叶子节点故可以实现空间局部性原理。
• B+树的不同使用
  聚簇索引：InnoDB引擎。叶子节点直接存放的是数据。
  优点：速度快
  缺点：依赖于有序数据， 更新代价大
  非聚簇索引：MyISM引擎。叶子节点存放数据记录地址（数据文件和索引文件分离）。
  优点：更新代价小。
  缺点：依赖于有序数据，二次查询（即查到记录地址还需再去查文件）。

④索引分级

• 从数据结构分
  B-Tree索引
  哈希索引
• 从底层存储方式分
  聚簇索引
  非聚簇索引
• 从应用维度分：
  主键索引
  普通索引
  全文索引
• 从级别分
  一级索引：直接根据主键查到数据
  辅助索引 ：查到的是数据的主键（再去一级索引查）
• 其他索引
  覆盖索引：包含所有需要查的字段值（查主键）
  联合索引：表中多个字段联合创建的索引

⑤索引的使用

• 正确选字段：不选Null的值，选择频繁查询的字段
• 不选择频繁更新的字段（还需更新索引）
• 限制每张表的索引：影响插入更新效率，可能影响查找效率
  因为优化器选择优查询时会选择对应的索引表，索引表太多选择也会慢。

5.mysql的存储引擎详解？

①存储引擎使用

• mysql采用的是插件式架构，支持多种存储引擎。
• 存储引擎是基于表的，而不是数据库，即每一个表都可以有一个存储引擎。

②主流存储引擎对比

• InnoDB 行级锁+表级锁， 实现四个隔离级别， 支持外键， 数据库崩溃后能安全恢复redo log，数据文件本身即是索引文件
• MyISAM 表级锁， 不支持事务， 不支持外键，无安全恢复，索引文件和数据文件分离。

6.mysql事务提交后数据与硬盘如何交互存储？

①write和fsyc操作

• write操作：从buffer（缓冲区）中写入到page cache（操作系统文件缓冲区）
• fsyc操作：从page cache（操作系统文件缓冲区）写入到硬盘

②update更新流程

• CURD操作到达执行器。
• 判断BufferPool中是否有该数据（没有则从磁盘中调入，有则直接更改BufferPool的数据）。
• 事务执行过程中，会记录更改过程到 bin log buffer 。
• redo log buffer记录更新操作。
• 如果事务提交了
  （mysql默认配置事务自动提交）
• redo log buffer的更新操作进入prepare状态
• 同时告诉执行器 bin log buffer执行write操作。
  默认参数为0 只执行write操作prepare状态
  参数为1 执行write + fsyc
• 然后redo log buffer 执行write + fsyc操作。
  默认参数为1 执行write + fsyc操作
  参数为0 不执行（等待后台线程一秒后自动write操作）
  参数为2 write

③示例

• 如果redo为0 mysql挂了或者宕机 损失1秒数据。
• 如果redo为1 不损失任何数据。
• 如果redo为2 mysql挂了不损失任何数据，宕机损失一秒的数据。

③为什么不直接将bufferPool的数据写入磁盘？

• 因为bufferPool的数据过大，刷盘耗时，利用redo.file刷盘，bufferPool也会通过IO线程写入磁盘的（合适的时机）

④事务回滚机制

• 进行更新操作前，都会记录一条undo buffer（相反操作，如果是delete 则是add，如果是add，则是delete），然后undo buffer会持久化到 undo log文件中
• 事务提交了（利用binlog和redolog），则undo log日志删除，事务未提交而回滚，只需要执行undo log日志的内容即可。
• undo log会持久化到磁盘上，如果数据库宕机，那么数据库可以通过查undo log回滚未完成的事务。
• 注意点：
  优点：
  undo log会先于数据持久化到磁盘上，即数据持久化到磁盘上，此时undo log一定已经在磁盘中了。这样能够保证事务的原子性，即如果mysql挂了，则可以通过undo log来回滚事务。
  缺点：
  每个事务提交前将undo log写入磁盘，这样会导致大量的磁盘IO，因此性能较差。

未更新