《MySQL技术内幕 InnoDB存储引擎 第2版》学习笔记

本文是我自己在秋招复习时的读书笔记，整理的知识点，也是为了防止忘记，尊重劳动成果，转载注明出处哦！如果你也喜欢，那就点个小心心，文末赞赏一杯豆奶吧，嘻嘻。 让我们共同成长吧……

---

第1章 MySQL体系结构和存储引擎

1.1 定义数据库和实例

​ 数据库：物理操作系统文件或其他形式文件类型集合。mysql中数据库文件可以是frm、MYD、MYI、ibd结尾的文件。

​ 实例： mysql数据库由后台线程以及一个共享内存区组成。数据库实例才是正真操作数据库文件的。

​ MySQL是一个单进程多线程架构的数据库。MYSQL数据库实例在系统上的表现是一个进程。

1.2 MySQL体系结构

​ 从概念上讲，数据库是文件的集合，是按照某种数据模型组织起来并存放在二级存储器中的数据集合；数据库实例是程序，用户通过数据库实例与数据库打交道。

​ MySQL体系结构：连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件。

​ 注意：存储引擎是基于表的，不是基于数据库的

1.3 MySQL存储引擎

1.3.1 InnoDB存储引擎

​ InnoDB存储引擎支持事务、支持行级锁、支持外键、支持非锁定读、MySQL5.5.8之后的默认存储引擎、支持MVCC获取高并发性、实现了SQL的4中隔离级别（默认repeatable）、使用next-key locking策略避免幻读产生、提供了插入缓冲、二次写、自适应哈希索引、预读、采用聚集方式存储数据。

1.3.2 MyISAM存储引擎

​ MyISAM不支持事务、支持表级锁、支持全文索引、面向OLAP数据库应用、缓冲池中指存储索引文件，不缓冲数据文件，MyISAM存储引擎表由MYD和MYI组成。

1.3.3 其他储存引擎

​ NDB(集群存储引擎)、Memory（默认使用哈希索引）、Archive、Federated、Maria等。

1.4 各存储引擎之间的比较

1.5 连接MySQL

​ 连接MySQL操作是一个连接进程和MySQL数据库实例进行通信。本质上是进程通信。进程通信方式：管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。

---

第2章 InnoDB存储引擎

​ InnoDB是事务安全型MySQL存储引擎，是OLTP应用首选的存储引擎。

2.1 InnoDB存储引擎概述

2.2 InnoDB存储引擎的版本

2.3 InnoDB体系架构

​ InnoDB存储引擎体系架构由：后台线程、内存池、和数据库文件组成。

​ 内存池：维护所有进程/线程需要访问的多个内部数据结构；缓存磁盘上的数据，方便读取；重做日志缓冲……

​ 后台线程：负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据；将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB能恢复到正常运行的状态下。

2.3.1 后台线程

​ InnoBD是多线程模型，后台的不同线程负责处理不同任务。

1、Master Thread

​ 主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、undo页的回收等。

2、IO Thread

​ InnoDB使用了大量的AIO处理写IO请求，极大提高了数据库性能，IO Thread主要负责这些IO请求的回调处理。

3、Purge Thread

​ 负责回收已经使用并分配的undo页。

4、Page Cleaner Thread

​ 将脏页刷新操作放在单独的线程中执行。目的是为了减轻Master Thread的工作以及对于用户查询线程的阻塞。

2.3.2 内存

1、缓冲池

​ InnoDB是基于磁盘存储的，将其中的记录按照页的方式进行管理。缓冲池用来弥补磁盘速度相对于内存慢对数据库性能的影响。

​ 缓冲池中缓存的数据页：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等。

​ InnoDB内存数据对象：缓冲池（索引页、数据页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、数据字典信息等）、重做日志缓冲、额外内存池。

​ 允许有多个缓冲池实例，每个页根据哈希值分配平均分配到不同缓冲池实例中，这样做减少了数据库内部的资源竞争，增加数据库并发处理能力。

2、LRU List、Free List和Flush List

​ 数据库中的缓冲池是通过LRU算法进行管理的。InnoDB缓冲池中的页默认16KB。InnoDB对LRU进行了优化，在LRU列表中加入了midpoint位置，读取最新的页是放在该位置，默认是LRU长度的5/8处。

3、重做日志缓冲

​ InnoDB首先将重做日志信息放到该缓冲，然后按照一定频率将其刷新到重做日志文件。默认8MB.

4、额外的内存池

2.4 Checkpoint技术

​ Checkpoint（检查点）技术目的是解决如下问题：缩短数据库的恢复时间；缓冲池不够用时将脏页刷新到磁盘；重做日志不可用时刷新脏页。

2.5 Master Thread 工作方式

2.6 InnoDB关键特性

​ 插入缓冲、两次写、自适应哈希索引、异步IO、刷新邻接页。

2.6.1 插入缓冲

1、Insert Buffer

​ 由于B+树的特性决定了非聚集索引插入的离散性，因而InnoDB设计了Insert Buffer，对于非聚集索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引页是否存在缓冲池中，若在，则直接插入；若不在，则先放到Insert Buffer对象中。

​ Insert Buffer的使用满足：索引是辅助索引，索引不是唯一的。当满足这两个条件InnoDB才会使用Insert Buffer，这样能提高插入操作的性能。

​ insert buffer 也是B+树实现的。

2、Change Buffer

​ 可以看做是Insert Buffer的升级。可以对insert、delete、update进行缓冲，分别是：Insert Buffer、Delete Buffer、Purge Buffer。

​ Change Buffer也要求索引是非唯一的辅助索引。

2.6.2 两次写

​ 两次写保证InnoDB数据页的可靠性。

​ 当应用重做日志前，用户需要一个页的副本，当写入失效时，先通过页的副本还原该页，再进行重做，这就是两次写。

​ 两次写组成：一部分是内存中的doublewrite buffer（2MB）,另一部分是物理磁盘上共享表空间中连续的128个页，即2个区，大小也是2MB.2.6.

2.6.3 自适应哈希索引

​ InnoDB会监控对各索引页的查询，如果发现建立哈希索引可以提升速度，则建立哈希索引，这就是自适应哈希索引（AHI）。AHI通过缓冲池的B+树页构造而来。AHI要求对这个页的连续访问模式必须是一样的。、，并且以该模式访问了100次，页通过该模式访问了N次（N=页中记录*1/16）

2.6.4 异步IO

2.6.5 刷新邻接页

​ 当刷新一个脏页时，InnoDB会检查该页所在的区的所有页，如果是脏页，那么一起进行刷新。

2.7 启动、关闭与恢复

---

第3章 文件

​ 本章介绍MySQL数据库和InnoDB存储引擎的各种类型文件。

​ 参数文件：告诉MySQL数据库实例在哪里可以找到数据库文件，并且指定某些初始化参数等；

​ 日志文件：用来记录MySQL实例对某种条件作出的响应时写入的文件，例如：错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件；

​ socket文件：当用UNIX域套接字方式进行连接时需要的文件；

​ pid文件：MySQL实例的进程ID文件；

​ MySQL表结构文件：用来存放MySQ表结构定义文件；

​ 存储引擎文件：存储引擎真正存储了记录和索引等数据。

3.1 参数文件

​ MySQL数据库的参数文件是以文本方式进行存储的。

3.1.1 什么是参数？

​ 数据库参数可以看成一个键值对。

3.1.2 参数类型

​ MySQL数据库中参数分为：

​ 动态参数：可以在MySQL实例运行中进行更改。

​ 静态参数：在整个实例生命周期内都不能更改。

3.2 日志文件

​ 日志文件记录了影响MySQL数据库的各种类型活动。MySQL常见文件：错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件。

3.2.1 错误日志

​ 错误日志文件对MySQL的启动、运行、关闭过程进行记录。可以得到进行数据库优化的信息。

3.2.2 慢查询日志

​ 慢查询日志可以定位可能存在问题的sql语句，从而进行sql层面的优化。

3.2.3 查询日志

​ 查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到正确的执行。

3.2.4 二进制日志

​ 二进制日志记录了对MySQL数据库执行更改的所有操作，不包括select、show这类操作。

​ 二进制日志作用：恢复、复制、审计。

3.3 套接字文件

3.4 pid文件

3.5 表结构定义文件

​ MySQL数据的存储是根表进行的，每个表都会与之对应的文件。MySQL中以frm为后缀的文件，记录了表的表结构定义，frm还用来存放视图的定义。

3.6 InnoDB存储引擎文件

​ 之前介绍的文件都是MySQL数据库本身的文件，和存储引擎无关。与innoDB存储引擎相关的文件有：表空间文件、重做日志文件，

3.6.1 表空间文件

​ InnoDB采用将存储的数据按照表空间进行存放的设计。默认10MB，名为ibdata1.

3.6.2 重做日志文件

---

第4章 表

4.1 索引组织表

​ 在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。若是创建表没有显示定义主键，InnoDB会将表中的第一个非空唯一索引设置为主键，若是没有非空唯一的索引，InnoDB自动创建一个6字节大小的指针。

4.2 InnoDB逻辑存储结构

​ InnoDB存储引擎将所有的数据存储在表空间中。InnoDB逻辑存储结构：表—段—区—页—行

4.2.1 表空间

​ 表空间是InnoDB所有数据存放的地方。

4.2.2 段

​ 表空间由各个段组成，常见的段有数据段、索引段、回滚段等。数据段就是B+树的叶子节点，索引段就是B+树的非索引节点。

4.2.3 区

​ 区是由连续页组成的空间，在任何情况下每个区都是1MB。为了保证区的连续性，通常InnoDB申请4~5个区。默认页是16kB,即一个区中有64个连续页。

4.2.4 页

​ 页（块）是InnoDB磁盘管理的最小单位，默认16KB，也可以设置为4KB 、8KB、16KB.

​ 常见的页类型：

​ 数据页、undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

4.2.5 行

​ InnoDB是面向列的，也就是说数据是按行进行存放的。每个页存放16KB/2-200行的记录，就是7992行记录。

4.3 InnoDB行记录格式

​ InnoDB提供了Compact和Redundant两种格式来存放行记数据。

4.4 InnoDB数据页结构

​ InnoDB数据页组成：File Header（文件头）、Page Header（页头）、Infimun和Supremun Records、User Records（行记录）、Free Space（空闲空间）、Page Directory（页目录）、File Trailer（文件结尾信息）

4.5 Named File Formats机制

​ InnoDB通过Named File Formats机制来解决不同版本下页结构兼容性问题。

4.6 约束

4.6.1 数据完整性

​ 关系数据库本身能保证存储的数据的完整性，不需要应用程序的控制。数据完整性有：实体完整性、域完整性（用户自定义完整性）、参照完整性.

​ InnoDB中的约束：主键约束、唯一约束、外键约束、默认约束

4.6.2 约束的创建和查找

4.6.3 约束和索引的区别

​ 约束是逻辑的概念，用来保证数据的完整性；而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储方式。

4.6.6 触发器与约束

​ 一个表最多创建6个触发器，分别为insert、update、delete、的before和after各定义一个。

4.7 视图

​ 视图（View）是一个虚表，可以当做表使用，视图中只存放了定义不存放实际的数据，实际的数据还在基本表中。虽然视图是虚表，但是对视图的更新操作本质上是通过更新基本表实现的。

4.6.1 视图的作用

​ 可以使得用户从不同的角度看待相同的数据；具有一定的安全性。

4.8 分区表

​ MySQL数据库支持水平分区，不支持垂直分区，并且是局部分区索引。常见的分区：Range分区、List分区、Hash分区、Key分区

​ 分区和性能：

​ 对于OLAP的应用，分区可以很好的提高查询性能，因为OLAP大多数查询需要频繁的扫描一张很大的表。

​ 对于OLTP的应用，分区不一定有很好的的效果，这种应用通常只查询一张大表中的10%的数据，B+树就能很好的满足查询需求性能要求。

---

第5章 索引与算法

5.1 InnoDB存储引擎索引概述

​ InnoDB常见索引：B+树索引、全文索引、哈希索引、唯一索引、主键索引、联合索引……

​ B+树索引就是传统意义上的索引，B+树索引并不能找到一个给定的键值的具体行，只能找到数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，得到最后所要查找的数据。

5.2 数据结构与算法

5.2.1 二分查找

5.2.2 二叉查找树和平衡二叉树

5.3 B+树

5.4 B+树索引

​ B+树索引在数据库中的一个特点是高扇出性，因此一般B+树的高度是2~4层。B+树索引可以分为：聚集索引、辅助索引。

5.4.1 聚集索引

​ 聚集索引是按照每张表的主键构造的B+树，同时叶子节点存放的就是整张表的行记录数据，聚集索引的叶子节点就是数据页。每个数据页是通过双向链表来进行链接的。一张表只能有一个聚集索引。多数情况下，查询优化器倾向采用聚集索引。以为聚集索引能在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够很快的访问针对范围的查询。

​ 数据页上存放的是完整的每行记录，而在非数据页的索引中存放的是键值以及指向数据页的偏移量。

​ 聚集索引在逻辑上是连续的。对于主键的排序查找和范围查找速度非常快。

5.4.2 辅助索引（非聚集索引）

​ 辅助索引的存在不影响聚集索引的组织，每张表可以有多个辅助索引。

5.4.3 B+树索引的分裂

5.4.4 B+树索引的管理

​ Cardinality：优化器会根据这个值来判断是否使用索引

5.5 Cardinality值

5.5.1 什么是Cardinality

​ 并不是在所有查询条件中出现的列都需要添加索引。

​ 低选择性：对于像性别、类型、地区这样的，他们的取值范围很小，称为低选择性。

​ 高选择性：某个字段的取值范围很广，几乎没有重复的。高选择性最适合使用B+树索引。

​ Cardinality表示索引汇总不重复记录数量的预估值。在实际应用中Cardinality/n_rows_in_table应该尽可能接近1.

5.5.2 InnoDB存储引擎的Cardinality统计

​ 数据库对于Cardinality的统计是通过采样的方法来完成的。Cardinality统计信息的更新发生在insert、update这两个操作中。InnoDB对Cardinality更新的策略是：表中的1/16的数据已经发生了改变；stat_modified_counter>2000000000(20亿)

​ InnoDB默认对8个叶子节点进行采样,采样过程：（1）采取B+树索引中叶子节点的数量A；（2）随机抽取B+树索引中的8个叶子节点，统计每个数据页不同记录数P1,P2……P8；(3)根据采样信息计算Cardinality预估值：Cardinality=（P1+P2+……+P8）*A/8

5.6 B+树索引的使用

5.6.2 联合索引

​ 联合索引指的是对表上的多个列进行索引。联合索引的键值是大于1的。

5.6.3 覆盖索引

​ InnoDB支持覆盖索引（索引覆盖），即从辅助索引中就可以查询到记录，而不需要查询聚集索引中的记录。

5.6.4 优化器选择不使用索引的情况

​ 优化器不使用索引的情况多发生在范围查找，join连接操作等情况下。

5.7 哈希算法

​ InnoDB使用哈希算法对字典进行查找，冲突解决机制是采用链表方式，哈希函数采用除法散列的方式。

5.8 全文检索

​ 全文检索：将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术。

5.8.2 倒排索引

​ 全文检索通常使用倒排索引来实现。倒排索引在辅助表中存储了单词与单词自身在一个或者等多个文档中所在的位置之间的映射。通常利用关联数组实现。常见表现形式：

​ inverted file index,{单词，单词所在文档的ID}；

​ full inverted index,{单词，（单词所在文档的ID,在具体文档中的位置）}

---

第6章 锁

6.1 什么是锁

​ 数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

6.2 lock与latch

​ latch：一般称为闩锁（轻量级锁），因为其要求锁定的时间必须非常短。长时间持续会导致性能下降。在InnoDB中latch分为mutex（互斥量）、rwlock(读写锁)。目的是保证并发线程操作临界资源的正确性，通常没有死锁检测机制。

​ lock的对象是事务，用来锁定的是数据库的对象（表、页、行等），一般在commit或rollback后进行释放。

6.3 InnoDB存储引擎中的锁

6.3.1 锁的类型

​ InnoDB实现的行级锁：共享锁、排它锁。

6.3.2 一致性非锁定读

​ 一致性非锁定读：指的是InnoDB存储引擎通过行多版本控制的方式读取当前执行时间的数据库中行的数据。如果读取的行正在执行delete、update操作，这时读取操作不会等待行上的锁的释放，相反地，InnoDB存储引擎会读取行的一个快照。

​ 由于一个行可能有不止一个快照数据，一般称为行多版本技术，由此带来的并发控制称之为多版本并发控制（MVCC）。

​ 在read committed和repeatable read事务隔离级别下，InnoDB使用的是一致性非锁定读。

6.3.3 一致性锁定读

6.4 锁的算法

6.4.1 行锁的3种算法

​ InnoDB存储引擎的3中行级锁算法：

​ Record Lock：单个行记录上的锁；

​ Gap Lock：间隙锁，锁定一个范围，但不包含记录本身

​ Next-Key Lock： Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

​ InnoDB对于行的查询采用 Next-Key Lock算法，采用该算法的技术叫做 Next-Key Locking，目的是解决幻影问题。

​ Next-Key Lock降级为Record Lock仅在查询的列是唯一索引的情况下。

6.5 锁问题

​ 锁只会带来3中问题：脏读、不可重复读、丢失更新

6.6 阻塞

6.7 死锁

​ 死锁：指的是两个或者两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。

​ 解决死锁方法：超时；等待图检测死锁

6.8 锁升级

​ 锁升级：指的是将当前锁的粒度降低。（行锁->页锁—>表锁）

---

第7章 事务

​ InnoDB中的事务完全符合ACID特性。原子性、一致性、隔离性、持久性

7.1 认识事务

7.1.1 概述

​ A(Atomicity)原子性：指的是数据库事务是不可分割的工作单位，要么做，要么不做。

​ C(Consistency)一致性：指的是事务将数据库从一种状态转换变为另一种一致性状态

​ I(Isolation)隔离性：指的是每个读写事务的对象对其他事务的操作对象之间是相互隔离的。

​ D(Durability)持久性，指的是事务一旦提交，其结果就是永久性的。

7.1.2 分类

​ 扁平事务、带有保存点的扁平事务、链事务、嵌套事务、分布式事务

7.2 事务的实现

​ 事务的原子性和持久性由redo log实现；

​ 事务的一致性由undo实现；

​ 事务的隔离性由锁实现；

7.3 事务控制语句

​ start transaction/begin：显示开启一个事务；

​ commit：提交事务；

​ rollback：回滚事务

​ savepoint identifier：在事务中创建一个保存点，一个事务中可以有多个保存点。

​ release savepoint identifier： 删除一个保存点

​ rollback to [savepoint] identifier:回滚到一个保存点

​ set transaction：设置事务隔离级别（read uncommitted、read committed、repeatable read、serializable）

---

第8章 备份与恢复

8.1 备份与恢复概述

​ 备份分为：热备、冷备、温备；

​ 按照备份后文件的内容，分为：逻辑备份、裸文件备份；

​ 按照备份数据库的内容来分，分为完全备份、增量备份、日志备份

8.7 复制

8.7.1 复制的工作原理

​ 复制：是MySQL数据库提供的一种高可用高性能的解决方案，总体分为以下3个步骤：

​ 1）主服务器把数据更改记录到二进制日志中

​ 2）从服务器把主服务器的二进制日志复制到自己的中继日志中。

​ 3）从服务器重做中继日志中的日志，把更改应用到自己的数据库中，以达到数据的最终一致性。

完结……