春节放假前夕，收拾行囊准备回家时，顺便塞了一本自己早就买好但一直没来得及看的书——《MySql技术内幕-InnoDB存储引擎》。按照往常，一般带回家的书很少会打开看的，但是今年受新冠病毒疫情的影响，过年基本宅在家中，闲来无事就把书翻出来随便看了下，随着慢慢深入看进去后，发现收获还挺大，于是坚持看完了并整理了些笔记，便于日后温习～

第一章 概述

• 数据库和数据库实例的概念与区别，前者是数据文件的集合，后者是线程和共享空间的小集合。一个数据库可以有多个实例
• MySql与其他数据库的最大不同是支持插件式的存储引擎
• • InnoDB 支持事务，多版本并发控制，支持聚集索引存储方式
• • MyISAM 不支持事务，支持全文索引
• • NODB 索引数据存在内存中
• • Memory 表数据存放在内存中
• DDL Data Define Language 数据定义语言，如create, alter等
• DML Data Manipulation Language 数据操纵语言，如CRUD操作。
• 既然支持插件式的存储引擎，那么一个实例只能选择一种存储引擎，还是说可以选择多个？ 答：存储引擎是基于表的，而不是数据库。每张表都可以指定一个存储引擎，一个实例中多张表可以分别指定不同的存储引擎。

第二章 InnoDB存储引擎

内部构造包含多种后台线程(Master Thread、IO Thread、Purge Thread、Page Cleaner Thread等)和内存池(缓冲池、重做日志缓冲、其他额外内存等)

内部线程

• MasterThread：核心后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，包括脏页刷新、合并插入缓冲、undo页回收等。
• IO Thread：由于InnoDB大量使用AIO(异步IO)来处理写IO请求，因此IO Thread则是用于负责这些IO的回调处理。
• Purge Thread：顾名思义，这是清理线程，它主要用于清理undo日志在事务提交后没有立即删除的数据，从而腾出更多空间。
• Page Cleaner Thread：InnoDB1.2.x版本引入的，用于将脏页的数据刷新到磁盘中，从而减轻Master Thread的压力。

内存

• 缓冲池用于缓存磁盘中的文件数据，DML动作先操作缓冲池，再刷新到磁盘文件中。
• 缓冲池中采用LRU算法维护空间的可用，当空间不足时将缓冲区中的数据刷新到磁盘上。当然InnoDB也对LRU算法进行了一些改进，比如增加midpoint，对于新读取等页，不是直接放在LRU首部，而是放在midpoint位置。
• 重做日志缓冲是redo log先写到缓冲区中，然后根据刷新策略刷新到重做日志文件中。

Checkpoint作用

• 缩短数据库恢复的时间
• 当LRU算法溢出时，检查溢出页若包含脏页则强制执行Checkpoint检查并将脏页刷新到磁盘文件中
• 重做日志不可用，刷新脏页

关键特性

• 插入缓冲机制：是指非聚集索引的插入或更新不是一次性直接插入到索引页中，而是先判断插入的非聚集索引页是否再缓冲池中，若在则直接插入，若不再则先放入Insert Buffer对象中，然后再以一定频率将Insert Buffer和辅助索引页子节点进行merge操作，从而提高非聚集索引的插入性能。
• 自适应索引：是指InnoDB会监控表上各索引页等查询，如果观察到建立哈希索引可以提升性能，则自动建立哈希索引，无需人工干预。
• 异步IO：InnoDB采用异步AIO方式操作磁盘，另外还支持IO Merge操作，即将连续页的三个IO请求合并为一个进行处理。
• 两次写：用于提升数据页的可靠性。

第三章 文件

• 参数文件
• 日志文件
• • 错误日志文件
• • 满查询文件
• • 查询日志文件
• • 二进制文件
• 套接字文件
• pid文件
• 表结构定义文件
• InnoDB存储引擎文件
• • 表空间文件
• • 重做日志文件

第四章 表

存储结构

• innoDB存储引擎根据主键顺序存放，即索引组织表。
• 对于未指定主键的表，则会根据是否有非空唯一索引，有则取第一个。没有则自动创建一个6字节大小的指针；
• 从逻辑结构看，所有数据都被存放在表空间中，包含：段(sequence)、区(extent)、页(page)。他们的包含关系为表空间>段>区>页>行。
• 表空间由各个段组成，常见的有数据段、索引段、回滚段。
• 区由连续页组成，每个区的大小都为1MB
• 页是InnoDB最小的单位，默认每页大小为16KB。常见的页有数据页、undo页、系统页、事务数据页。

行记录格式

• InnoDB面向列，即数据按行存放。提供了Compact和Redundant两种格式
• Compact格式下char和varchar中NULL不占用存储空间。
• Rdendant是为了兼容之前版本的页格式，该格式下CHAR类型会占用可能存放的最大值字节数。
• 行溢出数据是指一个16KB大小的页放不下一个varchar类型的数据(最大值为65532字节)而发生的数据溢出。一般情况下，InnoDB存储引擎的数据都是存放在页类型为B-tree node中，但是当发生行溢出时，数据存放在页类型为Uncompress BLOB页（未压缩的二进制大对象页）中。
• Compact和Rdendant统称为Antelope格式，InnoDB 1.0.x引入了一个新的文件格式——Barracuda。该格式又分为Compressed和Dynamic两种。
• InnoDB中CHAR和VARCHAR实际存储没有区别的，CHAR(N)中的N是字符长度，而不是字节长度。

约束

• 主键和外键：外键简单理解为外部表的主键，比如学生表的学号为主键，课程表的课程编号为主键，成绩表中主键是学号和课程编号联合的，此时称成绩表中的学号是学生表的外键。
  外键的作用是用于保证数据的相对完整性，即父表数据的变动需要联动子表，关联方式可以通过创建外键时指定类型，比如ON UPDATE或ON DELETE以及子表动作类型CASCADE或RESTRICT等。

分区表

• 分区是将同一表中的逻辑连续等数据分布物理存储不连续等空间上。MySql只支持水平分区，即根据行分区。
• 支持RANGE,LIST,HASH,KEY,COLUMNS五种分区类型
• 分区并不一定能带来性能上的提升，对于OLAP(在线分析处理，如数据仓库等)而言，每次需要取出大量等数据，因此分区后对于此类的查询有很大的提升。但是对于OLTP(在线事务处理，如电子商务，交易等)的查询通常不会获取超过10%的数据，一般根据B+索引便可用很快的实现。

第五章 索引与算法

B+树索引

• B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。
• B+数插入后通过拆分页和旋转方法来维持平衡
• 索引分为聚集索引和辅助索引
• Cardinality值是一个非常重要的数据，它表示索引中不重复记录数量的预估值(不是准确值，可以通过做一次Analyze Table操作刷新)，即作为检查索引是否合理的指标。Cardinality值/表总数据量的比值应该尽可能接近1，如果非常小则应该考虑该索引是否有必要存在。
• 快速索引创建(Fast Index Creation)，不需要新建临时表，通过给创建索引的表加S锁，但是只针对辅助索引，且期间只能对该表进行读操作。
• 联合索引即多列组成的索引，联合索引和普通索引的B+树类似，只是联合索引的键值数量大于1。联合索引会根据索引列排好序，因此当查询sql中涉及order by操作时，可以优先直接使用联合索引，避免多一次的sort操作。

哈希索引

• 哈希索引针对键值不重复 且 查询为等于的索引查询速度快。

全文索引

• 全文索引通常使用倒排索引实现，倒排索引需要辅助表进行存储相关映射关系，有两种表现形式：inverted file index和full inverted index。区别就是后者存储的内容更详细，还包含了单词所在到具体位置。
• InnoDB采用full inverted index方式，并且增加了FTS Index Cache缓存来提升性能。当全文检索查询时将它的数据刷入到辅助表中。
• FTS Document ID，在InnoDB中为支持全文索引，必须有一个与word映射到列，命名为FTS_DOC_ID，即需要创建该列，类型为BIGINT UNSIGNED NOT NULL。
• stoplist表示不需要进行索引分词操作的word列表，比如the这个单词没有具体的意义。

第六章 锁

Lock 和 Latch

• latch是轻量级锁，要求锁定时间非常短，分为mutex(互斥量)和rwlock(读写锁)。它面向对象线程
• lock的对象是事务，用于锁定数据库中的类似表、页、行等。

锁类型

• 锁分为
• • 共享锁(S)：允许事务读一行数据
• • 排他锁(X)：允许事务删除或更新一行数据
• • 意向共享锁(IS)：事务想要获得一张表中某几行的共享锁
• • 意向排他锁(IX)：事务想要获得一张表中某几行的拍他锁
• 监控当前事务并分析锁问题的三张表：INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。
• 多行版本控制：是指在一个事务对数据进行修改会对修改前的数据进行快照保存到undo中，若修改多次则存在多个版本的快照，要对这些快照进行并发管理的方式则叫多行版本控制(有点绕口了)。
• 一致性非锁定读：是指InnoDB通过多行版本控制的方式读取当前执行时间数据库中的数据(或快照数据)。事务隔离级别READ COMMITTED和REPEATABLE READ下采用的就是一致性非锁定读，只不过读取的快照不一样，前者读的是最新的快照，而后者只读事务开始时的数据。
• 一致性锁定读则表示读取数据的时候，也要对数据进行加锁。InnoDB支持两种一致性锁定读：
• • select..for update 对行记录加X锁，不能修改但是还可以读取
• • select..lock in share mode 对行记录加S锁

锁的算法

• 支持三种锁算法
• • Record Lock：单个行记录上的锁
• • Gap Lock：间隙锁，即锁定一个记录周边范围，不锁定记录
• • Next-Key Lock：前两者的集合，锁定范围和记录
• 当查询的索引有唯一属性时，InnoDB会对Next-Key Lock优化降级为Record Lock，即只锁定记录本身，而不锁定范围。
• Phantom Problem(幻象问题)表示在同一个事务下连续执行2次同样的sql可能返回不同的结果，第二次返回的可能是之前不存在的行(出现该问题前提是隔离级别为READ COMMITTED)，MySql官方将不可重复读定义为幻象读。
• InnoDB采用Next-Key Lock算法避免Phantom Problem。因为同时对范围和记录进行锁定，这样两次读到的记录就没有改变了

锁问题

• 存在三种锁问题
• • 脏读：读取到另一个事务中未提交的数据
• • 不可重复读：也就是幻象读Phantom Problem
• • 丢失更新：一个事务的更新操作被另一个事务的更新操作覆盖。
• InnoDB不会回滚因超时引发的错误异常。比如一个同一个事务中执行两个insert操作，第一个成功了，第二个因超时而失败，但是InnoDB不会回滚第一个操作的成功数据。而需要用户根据异常来决定Commit或rollback。

死锁

• 死锁表示两个或两个以上事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
• 解决死锁的办法
• • 超时机制，即两个事务互相等待时，其中一个触发超时而回滚，另一个事务则继续进行。超时机制简单粗暴，且采用FIFO方式进行回滚，但是如果超时的事务更新了很多行，或者说undo log很大时，混滚效率较慢，而此时应该回滚事务较小的那个比较合适。
• • wait for graph(等待图)，这种方式更加主动，它能事先检测出存在死锁的可能，然后InnoDB选择undo最小的事务进行回滚。

第七章 事务

ACID特性

• 原子性-Atomicity
• 一致性-Consistency
• 隔离性-Isolation
• 持久性-Durability

事务分类

• 扁平事务：最简单的事务，由begin/commit/rollback组成
• 带有保存点的扁平事务：在扁平事务的基础上进行保存点设置并支持回滚到指定保存点。
• 链事务：保存点模式的增强，它允许将此前的保存点持久化到磁盘，但是逻辑上和下一个事务合并一个事务。需要注意的是链事务只支持回滚到最近一个保存点。
• 嵌套事务：是一个层次框架，顶层事务控制下层的各个子事务。
• 分布式事务：也就是在分布式环境下运行的扁平事务。

事务实现

redo日志

• redo日志叫重做日志，用来保证事务的原子性和持久性
• redo由两部分组成
• • 重做日志缓冲(redo log buffer)
• • 重做日志文件(redo log file)
• 当事务提交时，先将事务的所有日志写入redo日志文件进行持久化，避免当事务提交后还没写入数据文件前系统宕机而带来的数据丢失
• 上述步骤中其实是先将事务日志写入redo日志缓冲，然后根据innodb_flush_log_at_trx_commit参数来决定什么时候刷到文件中。

undo日志

• undo日志用于保证事务的一致性，用于事务失败后回滚行记录到某个版本。多行版本并发控制-MVCC就是通过获取undo日志中的数据来实现的。
• undo日志会产生redo日志，也就是undo 日志的产生会伴随redo log的产生，因为undo日志也需要持久性的保护。
• InnoDB引擎对数据进行回滚时，并不是直接物理恢复，而是执行与之前相反的操作，比如insert回滚会执行一个delete,delete回滚执行insert，update执行相反的update恢复。
• 事务中insert操作产生的undo日志会在事务提交后就清除，但是update和delete则会先保留，因为MVCC机制，其他事务可能会使用到该undo的某一版本的数据。因此，update和delete的删除由purge线程来清除。

事务隔离级别

• Read Uncommitted ：允许读取其他事务中未提交的数据，其实本质已经违反了事务的隔离性
• Read Committed：允许读取其他事务中已提交的数据。采用Record Lock记录锁方式来实现。可能会出现幻象读问题。
• Repeatable Read：可重复读，InnoDB默认的隔离级别，采用Next-Key Lock算法避免了幻象读问题。
• Serializable：事务串行执行隔离，该隔离级别下，InnoDB会对每个select语句后自动加上Lock in share mode。

分布式事务

• 分布式事务是指允许多个独立的事务资源(Mysql等)参与到一个全局事务中。
• XA事务是由一个或多个资源管理器(Resource Managers)、一个事务管理器(Transaction Manager)以及一个应用程序(Application program)组成。
• InnoDB通过提供对XA事务的支持，并通过它来实现分布式事务的实现。
• 分布式事务使用两阶段提交等方式，第一阶段准备(Prepare)，第二阶段为提交或回滚，若任一阶段不能提交，则所有节点都要回滚。

不好的事务习惯

• 在循环中执行事务Commit：由于每次提交都要写一次重做日志，频繁提交会加大这部分的开销
• 使用自动提交：最好把事务控制权限给应用开发人员。
• 使用自动回滚：应该将事务的回滚在程序中控制，这样用户可以得知失败回滚等原因。
• 长事务：长事务耗时且当事务失败时，重新开始的代价也高。因此应该分解为小批次的处理。

更多原创文章请关注微信公众号 👇👇👇 唠吧嗑吧