数据库篇之InnoDB存储引擎

1. 数据库和实例

数据库是文件的集合，是依照某种数据模型组织起来并存放在二级存储器中的数据集合。

数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件。用户对数据库的任何操作，都是在数据库实例下进行的。

任何对数据库的操作，都需要通过数据库实例来完成对数据库的操作。

2. MySQL数据库

（图片来源于网络）

MySQL数据库区别于其他数据库的最重要的一个特点就是插件式的表存储引擎。

存储引擎是基于表的，而不是数据库。MySQL架构的存储引擎架构提供了一系列标准的管理和服务支持，这些标准和存储引擎无关。存储引擎是底层物理结构的实现，每个存储引擎开发者可按照自己的意愿来进行开发。

由于MySQL数据库是开源的，所以存储引擎有分为MySQL官方引擎和第三方存储引擎有。些第三方存储引擎，如InnoDB存储引擎（后被Oracle收购），应用广泛。

3. 存储引擎介绍

3.1 InnoDB

InnoDB存储引擎的设计目标主要是面向在线事务处理OLTP的应用，它支持事务，特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读（读操作默认是不加锁）。从MySQL5.5.8开始，MySQL存储引擎默认是InnoDB。InnoDB具有高可用性、高性能以及高可扩展性。

InnoDB（1）通过多版本并发控制****MVCC来获得高并发性，并且实现了SQL标准的4种隔离级别，默认是REPEATABLE READ可重复读级别。（2）同时使用一种**next-key-locking**的策略来避免幻读的产生。（3）还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。

对于表中数据的存储，InnoDB采用了聚集的方式，因此每张表的存储时按照主键的顺序进行存放。如果表中没有自定义主键，InnoDB会为每一行生成一个6字节的ROWID，作为主键。

（图片来源于网络）

3.2 MyISAM

MyISAM主要面向一些联机分析处理OLAP的应用，它支持全文索引，不支持事物、表锁设计。

MyISAM不同的一点是缓冲池只缓存索引文件，而不缓存数据文件。数据文件的缓存交由操作系统完成。

4. 连接数据库

连接数据库，是一个进程和数据库实例进行通信。从程序设计的角度来说，本质上是进程通信。

所以，通信的方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。

• 不在同一台服务器上：TCP/IP套接字

• 在同一台服务器上：UNIX域套接字、命名管道、共享内存

5. InnoDB 存储引擎

5.1 后台线程

InnoDB是一个多线程的模型，不同的线程负责不同的任务。

• 核心线程：负责缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合插入缓冲、UNDO页的回收等。

• IO线程：负责IO请求的回调。大量使用了异步IO来处理IO请求，从而提高性能。

• 清空purge线程：负责回收已经使用并分配的undo页。在事务提交之后，其所使用的undolog不再需要，就需要回收undo页。

• 脏页刷新线程：负责脏页的刷新操作。目的是减轻核心线程的工作和用户查询线程的阻塞。

5.2 内存

1）缓冲池

InnoDB是基于磁盘存储的，并将其中的记录按照页的方式进行管理。

在数据库系统中，由于CPU速度和磁盘的速度差别很大，所以常使用缓冲池技术来提高数据库的整体性能。

缓冲池是一块内存区域，默认是16KB，通过内存的速度来弥补磁盘速度较慢对数据的影响。

🎨 读页操作

从磁盘读取的页存放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在，则读取该页，返回结果。若不在，读取磁盘上的页，返回结果。

🖌 写页操作

先修改缓冲池中的页，以一定的频率刷新到磁盘中。

是通过checkpoint机制刷新会磁盘，而不是在每次页发生修改时。

👜 缓冲的数据类型

缓冲池缓冲的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、锁信息、数据字典信息等。

🍵 管理页

LRU最近最少使用算法，释放最少使用的页。

InnoDB对LRU算法做了优化，在LRU列表中加入midpoint位置，新读取到的页，放在midpoint位置，而不是列表的首部。默认midpoint在列表长度的 5/8 处。new 列表中的页是最为活跃的热点数据。

🌺 脏页

LRU列表中的页被修改后，成为脏页（缓冲池和磁盘上的数据不一致）。数据库会通过checkpoint机制将脏页刷新回磁盘。

脏页即存在于LRU列表，也存在于Flush列表中。Flush列表中的页是脏页列表。

LRU列表管理缓冲池中页的可用性，Flush列表管理将页刷新回磁盘，两者互不影响。

2）重做日志缓冲

重做日志缓冲 redo log buffer。

InnoDB会先将重做日志信息放入到这个缓冲区，然后按一定的频率将其刷新到重做日志文件（磁盘中）。

重做日志缓冲一般不需要很大，默认是8MB，因为一般每1秒钟会将重做日志刷新到日志文件。

什么情况下刷新：

• 核心线程Master Thread每1秒钟刷新

• 每个事务提交时

• 重做日志缓冲池剩余空间小于 1/2 时

3）额外的缓冲池

在给对象分配内存时，是从内存中的堆中分配，如果堆的空间不够，会从缓冲池进行申请。

5.3 Checkpoint 技术

为了避免数据丢失的问题，当前事务数据库系统普遍采用了Write Ahead Log策略，也就是当事务提交时，先写重做日志，再修改页。这样由于宕机而导致数据丢失时，通过重做日志可以完成数据的恢复。这也是ACID中D（持久性）的要求。

1）解决的问题

Checkpoint检查点技术是解决以下几个问题：

• 缩短数据库的恢复时间

当数据库发生宕机时，不需要从头重做所有日志，因为Checkpoint之前的页都已经刷新回磁盘，所以数据库只需对Checkpoint后的重做日志进行恢复。

• 缓冲池不够用时，将脏页刷新到磁盘

缓冲池不够用时，根据LRU算法会移出最近最少使用的页，如果这页是脏页的话，需要强制执行Checkpoint，将脏页刷新回磁盘。

• 重做日志不可用时，刷新日志

重做日志不可用是因为当前事务数据库系统对重做日志的设计是循环使用的。

当数据库发生宕机时，如果不需要这部分的重做日志，那么这部分就可以被覆盖。如果需要这部分的日志，就必须强制产生Checkpoint，将缓冲池的页至少刷新到当前重做日志的位置。

2）类型

Checkpoint所做的事情就是将缓冲池的页刷新到磁盘中，不同的是刷新多少页磁盘，每次从哪里刷新，什么时间触发Checkpoint。

🍚 Sharp Checkpoint

发生在数据库关闭时。

🍚 Fuzzy Checkpoint

刷新一部分脏页到磁盘，而不是全部的脏页。

5.4 插入缓冲 insert buffer

在插入时还是按照主键进行顺序存放，但是对于非聚集索引叶子节点的插入不是顺序的。

对于非聚集索引和插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。如果在，直接插入；如果不在，先放入到一个Insert Buffer对象中，实际上并没有插入。以一定的频率和情况合并Insert Buffer和辅助索引页子节点，通常能将多个插入合并到一个操作中（因为在一个索引页中）。

1）使用条件

• 索引是辅助索引

• 索引不是唯一的

辅助索引不能是唯一的，是因为在插入缓冲时，数据库不会查询索引页来判断插入记录的唯一性。

2）内部实现

3）Change Buffer

对插入、删除、更新操作都进行缓冲，分别是Insert Buffer、Delete Buffer、Update Buffer。

非唯一的辅助索引

5.5 两次写

当写入失效发生时，先通过页的副本来还原该页，再进行重做。

当数据库宕机时，可能正在写入一个页，而这个页只写了一部分，比如16K的页面只写了4K，然后发生了宕机。这种情况称为部分写失效。如果没有两次写，会因为部分写失效而导致数据失效。

重做日志中的记录的是对页的物理操作，如果页已经损坏，那么再进行重做是没有意义的。

5.6 慢查询日志

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

比如，在MySQL启动时设置一个阈值，将运行时间超过（>）阈值的SQL语句都记录在慢查询日志中。每隔一段时间查看日志，确认是否有SQL语句需要优化。

# 查看进入慢查询日志的SQL语句的执行时间
show variables like 'long_query_time'

# 查看变量，没有使用索引的SQL语句
show variables like 'log_queries_not_using_indexes'

# 开启慢查询日志
set global slow_query_log='ON'

1）slow_log表

MySQL的slow log通过运行时间来对SQL语句进行捕获，这是一个非常有用的优化技巧。

# 查看慢查询表
select * from mysql.slow_log

2） 其他日志

• 查询日志：记录了所有对MySQL数据库请求的信息。默认文件名：主机名.log。对于未正确执行的SQL语句，也会记录。

• 二进制日志：记录了对MySQL数据库（包括不同的存储引擎）执行更改的所有操作，但是不包括select和show操作。

因为这类操作对数据本身并没有修改。然而，有些操作没有导致数据库发生变化，也可能会写入二进制文件。作用有：

• 恢复（恢复某些数据需要二进制文件 ）；

• 复制（通过复制和执行二进制日志使一台远程的MySQL数据库与一台MySQL数据库进行实时同步）；

• 审计（通过二进制日志中的信息来进行审计，判断是否有对数据库进行主任的攻击）。

5.7 索引

InnoDB存储引擎支持B+树、全文索引、哈希索引。

1）哈希索引

InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表生成哈希索引。

2）B+树索引

B+树中B代表平衡。

B+树索引不能找到一个给定键值的具体行。B+树能找到的是数据行所在的页，然后数据库把页读入到内存中，在内存中查找，得到要查找的数据。

B+树是一种平衡查找树。所有记录节点都是按键值的大小顺序放在同一层的叶子节点上，由各叶子节点指针进行连接。比如，一棵高度为2的B+树如下：

B+树索引在数据库中有一个特点是高扇出性。因此在数据库中，B+树的高度一般都在2_{4**层。也就是说查找某一个键值的行记录是最多需要**2}4次IO。

3）聚集索引

按照每张表的主键构造一棵B+树，同时叶子节点中存放的是每张表的行记录数据。也将聚集索引的叶子节点称为数据页。

数据页只能按照一棵B+树进行排序，因此每张表只能有一个聚集索引。在多数情况下，查询优化器会倾向于采用聚集索引，因为聚集索引能够在B+树索引的叶子节点上查找到数据。

由于定义了 逻辑顺序，聚集索引能够很快地访问针对范围值的查询。

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

好处：对于主键的排序查找和范围查找速度很快。

4）辅助索引

辅助索引（非聚集索引），并不影响数据在聚集索引中的组织，因此每张表可以有多个辅助索引。

查找：InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

创建辅助索引时，InnoDB会对创建索引的表上加一个S锁，在创建表的过程中，不需要重建表，所以速度会提高很多。

5）联合索引

对表上的多个列进行索引。联合索引也是一棵B+树。联合索引，先对第一列排序，然后再对第二列排序，依次下去。

6）覆盖索引

覆盖索引：从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

好处：辅助索引不包含整行记录的所有信息，所有辅助索引的大小要远小于聚集索引，可以减少大量的IO操作。

对于统计问题而言，InnoDB存储引擎不会选择查询聚集索引来进行统计，而是会选择辅助索引。

此外，对于(a, b)的联合索引，一般是不可以选择列b作为查询条件的，但如果是统计操作，比如，count(*)，并且是覆盖索引的，优化器会选择联合索引。

对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。

如果访问的数据量较大（占全表的20%），优化器会通过聚集索引来查找数据。

7）优化器不使用索引的情况

优化器没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况大多发生在范围查找、JOIN连接等情况下。

select * from orderdetails
where orderid > 10000 and orderid < 10200

8）哈希索引

哈希索引只能搜索等值的查询，不能进行范围查找和顺序查找。

9）全文检索

全文检索，是将存储于数据库中的整本书或者整篇文章中的任意内容信息查找出来。可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

全文检索通常使用倒排索引来实现。倒排索引也是一种索引结构。在辅助表中存储了单词与单词在一个会多个文档中是在位置之间的映射。通常利用关联数组实现。有两种表现形式：

• 一种是｛单词，单词所在文档的ID｝

• 一种是｛单词，（单词所在文档的ID，在具体文档中的位置｝

6. 锁

InnoDB不需要锁升级，因为一个锁和多个锁的开销是相同的。

锁机制用于管理共享资源的并发访问，提供数据的完整性和一致性。锁是数据库系统区别于文件系统的一个关键特性。

InnoDB会在数据库的多个地方使用锁，从而允许对多种不同资源提供并发访问，比如，操作缓冲池中的LRU列表、删除、添加、移动LRU列表中的元素。

InnoDB提供一致性的非锁定读、行级锁支持。行级锁没有额外的开销，并可以同时得到并发性和一致性。

6.1 Lock

数据库中lock和latch都是锁。latch一般称为闩锁（轻量级锁）。它要求锁定的时间必须非常短，时间长性能会变差。InnoDB中latch分为mutex（互斥量）和rwlock（读写锁），其目的用来保证并发线程操作临界资源的正确性，通常没有死锁检测的机制。

lock的对象是事务，用来锁定数据库中的对象，比如表、页、行。lock的对象在事务commit或rollback后释放。此外，lock是有死锁机制（waits-for graph、time out等机制）的。

6.2 行级锁的类型

• 共享锁 S Lock：允许事务读一行数据。

• 排它锁 X Lock：允许事务删除或更新一行数据。

对同一行数据数据是否冲突：

	S 锁	X 锁
S 锁	不冲突	冲突
X 锁	冲突	冲突

6.3 表级锁的类型

1）意向锁

意向锁：意向锁将锁定的对象分为多个层次，事务希望在在更细粒度上加锁。InnoDB对意向锁的实现时表级别的锁。目的是在一个事务中揭示下一行将被请求的锁类型。

2）类型

• 意向共享锁 IS Lock：事务想获得一张表中某几行的共享锁。

• 意向排它锁 IX Lock：事务想获得一张表中某几行的排它锁。

InnoDB支持的是行级别的锁，所以意向锁不会阻塞全表扫描以外的任何请求。

意向锁之间是互相兼容的

	IS 锁	IX 锁
IS 锁	兼容	兼容
IX 锁	兼容	兼容

意向锁和行级锁共享锁和排它锁的关系如下：

	S 锁	X 锁
IS 锁	兼容	互斥
IX 锁	互斥	互斥

6.4 一致性非锁定读

一致性的非锁定读：InnoDB通过行多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读操作不会去等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据。

非锁定读机制大大提高了数据库的并发性，是InnoDB的默认读取方式，读取不会占用和等待表上的锁。

快照数据是当前行之前的历史版本，每行记录可能有多个版本。一个行可能有多个快照数据，这种技术是行多版本技术。由此带来的并发控制，称为多版本并发控制MVCC。通过MVCC可以获得高并发性。

在事物隔离级别 READ COMMITED和REPEATABLE READ下，InnoDB使用的是非锁定的一致性读。

然而它们对于读取的快照数据并不一样。

• READ COMMITED隔离级别下，读取的是被锁定的行的最新一份快照数据，

• REPEATABLE READ隔离级别下，读取的是事务开始时的行数据版本。

6.5 一致性锁定读

默认配置下（也就是隔离级别REPEATABLE READ），InnoDB的SELECT操作使用的一致性非锁定读，但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。

# 加共享锁
SELECT ... LOCK IN SHARE MODE;
# 加排他锁
SELECT ... FOR UPDATE;

# 在使用上面两个语句时，必须加上 BEGIN, START TRANSACTION或者AUTOCOMMIT=0

上面的某些情况：

对于外键的插入后更新，首先需要查询父表中的记录，也就是SELECT父表。对于父表的SELECT操作，不是使用一致性非锁定读（会发生数据不一致的问题），使用的是SELECT ··· LOCK IN SHARE MODE，主动对父表加一个S锁。

6.7 行锁的3种算法

• Record Lock：单个行记录上的锁。

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

• Next-Key Lock： Gap Lock + Record Lock，锁定一个范围，包含记录本身。

6.8 解决幻像问题

默认的事务隔离级别下，InnoDB会出现幻读的问题，通过Next-Key Lock机制来避免。

比如，SELECT * FROM t WHERE a > 2 FOR UPDATE，锁住的是$$$$(2,\infty )$$$$，而不是大于2的单个值。锁住的期间是不允许插入的。

InnoDB默认的隔离级别是REPEATABLE READ，该级别下，采用Next-Key Lock的方式来加锁。

隔离级别是READ COMMITED，该级别下，采用Record Lock的方式来加锁。

6.9 锁升级

将当前锁的粒度降低。比如，将1000个行锁升级为页锁或者将页锁升级为表锁。

InnoDB不存在锁升级的问题，它是根据每个事务访问的页对锁进行管理，采用的是位图的方式。

因此，不管一个事务锁住页中有一个记录还是多个记录，它的开销是一样的。它不是根据每个记录来产生行锁的。

7. 事务

• 隔离性通过锁机制完成；

• 原子性、一致性、隔离性通过redo log、undo log完成；

通过redo log实现持久性；通过undo log实现原子性；通过锁机制以及MVCC实现隔离性；

一致性得到保证。

7.1 重做日志 redo

重做日志用来实现事务的持久性，包含两部分：一是内存中的重做日志缓冲（易失的），二是重做日志文件（持久的）。

当事务提交COMMIT时，必须先将事务的所有日志写入到重做日志文件进行持久化。

重做日志是顺序写的，在数据库运行时不需要对重做日志进行读取操作。重做日志在事务进行过程中不断地写入，并不是随事务提交的顺序进行写入。

7.2 回滚日志 undo

回滚日志用来帮助事务回滚及MVCC功能。回滚日志是需要随机读写的。

在事务对数据库进行修改时，InnoDB不但会产生redo log，还会产生一定量的undo log。

undo存在在数据库内部一个特殊段中，这个段是undo段。undo段位于共享表空间内。

undo是逻辑日志，只是在逻辑上将数据库恢复到原来的样子，所有修改被逻辑的取消了。但是数据结构和页本身在回滚之后可能大不相同。

undo log也会产生redo log。

其他事务可能需要通过undo log来得到行记录之前的版本。是否最终可以删除undo log及undo log所在的页有purge线程来判断。

7.3 Purge

由purge线程最终完成delete和update操作，以及清理undo log。是因为InnoDB支持MVCC，所以记录不能在事务提交时立即进行处理。