简介

内容总结自《MySQL技术内幕InnoDB存储引擎》第2版。

概要

数据库：是一个操作系统文件，frm、MYD、MYI、ibd 等为结尾的文件。

实例：MySQL 由后台线程和一个共享内存区组成。真正用于操作数据库文件。

MySQL 被设计为一个单进程多线程的数据库。

存储引擎是基于表的，而不是数据库。

数据操纵语言 DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。

数据完整性：

• 实体完整性：保证表中有一个主键。可通过Primary Key和Unique Key约束或触发器来保证实体完整性
• 域完整性：保证每列数据的值满足特定条件。在数据库中可以采用，选择合适的数据类型、Foreign Key、Default、触发器来保证域完整性
• 参照完整性：保证两张表之间的关系。可以通过Foreign Key以强制参照完整性

约束：

• Primary Key
• Unique Key（唯一约束，并不是主键或索引）
• Foreign Key
• Default
• NOT NULL

约束和索引的区别：

当用户创建了一个唯一的索引就创建了一个唯一的约束。但是约束和索引的概念是不同的。约束更是一个逻辑上的概念，用来保证数据完整性；而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

外键约束：

foreign key (parent_id) refereces parent(id)

可以定义on delete 或者on update表示在父表进行delete或者update操作时，对子表所作的操作：

• cascade：也进行相应的delete或者update
• set null：更新为null（列属性必须允许为空）
• no action：抛出错误，不允许这类事情发生
• restrict：抛出错误，不允许这类事情发生（如果定义外键时没有说明on delete或on update，这是默认的外键设置）

缺点：数据导入时，每一行都会进行外键检查，花费大量的时间（使用set foreign_key_checks=0可以避免外键检查）

InnoDB 存储引擎

支持事务，支持全文索引

特点：行级锁、支持外键、非锁定读、MVCC。

架构：InnoDB 由若干后台线程、缓存池、文件组成。

后台线程

Master Thread

把缓冲池中的数据异步刷新到磁盘，保证数据一致性

IO Thread

处理 IO 请求

Purge Thread

事务提交后，回收不再使用的 Undo 页

Page Cleadner Thread

脏页刷新

缓冲池

InnoDB 将其中的记录按照页的方式进行管理，页默认大小为 16KB。

数据库怎么读取页？

• 从磁盘读取到的页放入缓冲池中
• 下一次再读相同页时，首先判断是否在缓冲池，若在，则直接读取该页，否则，将磁盘上的页调入缓冲池，并读取

数据库怎么修改页？

• 首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上（通过 checkpoint 机制）

缓冲池中缓存的数据页有：

• 索引页
• 数据页
• undo 页
• 插入缓冲页
• 自适应哈希索引
• 锁信息
• 数据字典信息

如何管理缓冲池？（通过 show engine innodb status 可以看到这些管理列表）

• LRU List：管理已经读取到缓冲池的页。对LRU算法做了优化，加入了 midpoint 位置，新读取的页放在 midpoint 位置，默认为 LRU List 长度的 5/8 处。这样做的好处在于：如果需要做一次全表的扫描操作，需要访问表中许多页，这些页尽在此次查询中用到，并不真正的热点数据。如果放在 List 首部，将影响到真正的热点数据页在 LRU List 中的位置，甚至是移除它们。设置 midpoint 的语句是：set global innodb_old_blocks_pct = xx; // LRU List 尾端 %xx 处
• Free List：数据库刚启动时，LRU 中无页，当需要分页时，从 FreeList 中检索空闲页，交给 LRU List 进行调度
• Flush List：存放脏页，将之通过 CHECKPOINT 机制刷新回磁盘。脏页：缓冲池中的页和磁盘上的页的数据产生了不一致。脏页既存在于 LRU List 也存在于 Flush List。

 Write Ahead Log 策略

为了避免脏页刷新回数据库的时候，发生宕机，造成数据库不可恢复的问题，普遍采用 Write Ahead Log 策略。

当事务提交时，先写重做日志，再修改页。当由于数据库宕机而引发数据丢失时，可以通过重做日志来完成数据的恢复。这是 ACID 中 D 的要求。

CHECKPOINT 机制

试想，如果数据库更新的时候发生宕机，需要通过重做日志来恢复整个数据库，如果：

1. 数据库特别大，大到缓冲池不足以放下
2. 数据库特别大，大到恢复整个数据库需要若干个月的时间

显然不可能恢复整个数据库。

因此，CHECKPOINT 机制用来解决上述问题：

• 缩短数据库恢复时间
• 缓冲池不够用时，将脏页刷新回磁盘

当数据库发生宕机时，数据库不需要重做所有的日志，因为 CHECKPOINT 机制确保 CHECKPOINT 之前的页都已经刷新回磁盘。故数据库只需要对 CHECKPOINT 之后的重做日志进行恢复。当缓冲池不够用时， LRU 会移出最近最少使用的页，如果这个页是脏页，那么需要强制执行 CHECKPOINT ，将脏页刷新回磁盘。

有两种 CHECKPOINT ：

• Sharp Checkpoint：数据库关闭时将所有的脏页刷新回磁盘（默认）
• Fuzzy Checkpoint：数据库运行的时候定期刷新一定比例脏页回磁盘，而不是刷新所有

（Innodb关键特性）自适应哈希索引

B+树查找次数取决于B+树的高度，一般B+树高度在3-4层，需要3-4次查询。

自适应哈希索引（Adaptive Hash Index）：Innodb存储引擎监控表上个索引页的查询，如果观察到建立哈希所以可以带来速度提升，则会根据访问的频率和模式来自动地为某些热点页建立哈希索引，称为自适应哈希索引。例如，对于（a，b）这样的联合索引页，其访问模式可以是：

1. where a = xxx
2. where a = xx and b = yy

访问模式一样要求的是查询条件一样，如果交替进行上述两种查询，那么Innodb不会为之建立AHI。此外，AHI对频率要求如下：

• 以改模式访问了100次
• 页通过该模式访问了 N 次，N=页中记录数/16

注意：AHI只能用来搜索等值（=）的查询，而对于其他类型查找，如：范围查找，是不能使用哈希索引的。

（Innodb关键特性）异步IO（AIO）

用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送完毕后，等待所有的IO操作完成，而不必按顺序的等待前一个IO完成，是为AIO。

索引

InnoDB支持以下常见索引：

• B+树索引
• 全文索引
• 哈希索引

B+树索引并不能直接找到给定键值所在的具体行，而是找到数据行所在的页，然后数据库把页读到内存，再在内存中进行查找（页内空间连续且顺序，二分查找），最后得到想要的数据。

B+树节点插入：

B+树节点删除：

聚集索引

按照表的主键构建一颗二B+树，每张表最多有一个聚集索引。聚集索引的存储物理上并不一定连续，但逻辑上是连续的。

优点：

聚集索引对主键的排序查找特别快：例如，一张注册用户表，要查找最后10位注册的用户，用户可以快速定位最后一个数据页，并取出其中的最后10条记录（select * from regesters_tb order by id limit 10）。

和范围查找特别快（由上层B+树索引就可以大致确定页的范围，然后在页（叶节点）中进行范围内的顺序查找即可）

辅助索引

叶子节点并不包含行记录的全部数据，它包含键值（并非主键）和一个书签。该书签即相应行数据的聚集索引键。每张表可以有多个辅助索引。

alter table t add key idx_name(name) // 为name属性创建辅助索引，辅助索引名字为idx_name

drop index idx_name on name

show index from t

什么时候添加B+树索引？

对于高选择性的字段，它们可取值的范围非常广，几乎没有重复，为它们建立索引是合适的。

例如：id，phone_number

对于低选择性的字段，它们取值范围窄。为它们建立索引是不合适的。

例如：sex（性别）

Cardinality值可以预估一个字段的选择性高低，通过show index 可以查到这个值（通过随机采样的方法，样品中的非重复个数），Cardinality/n_rows_in_table应尽可能等于1。

联合索引

联合索引是指对表上的多个列进行索引.

create table t(

　　a int,

　　b int,

　　primary key(a),

　　key idx_ab(a, b)

)engine = innodb

数据按照（a，b）的顺序进行了排列（先根据a再根据b排序）。

对于查询：

where a = xx and b = yy，可以使用此联合查询

where a = xx，可以使用此联合查询

where a = xx order by b，可以使用此联合查询快速的查找数据

而对于查询：

where b = xx，不可以使用此联合查询

覆盖索引

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

优点：

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

什么时候选择覆盖索引优化？

如果查询不需要知道该行记录的具体内容，只需要知道一些统计信息例如：个数，那么根据辅助索引即可得到统计结果，而不用进一步使用聚集索引找到具体行记录，那么此时使用覆盖索引优化。

select count(*) from tb_1

note：使用explain可以得到执行计划

倒排索引

全文检索通常使用倒排索引来实现。它存储了单词与单词自身在一个或多个文档中所在位置之间的映射（通常使用关联数组）：

• inverted file index：{单词，单词所在文档ID}
• full inverted index：{单词，（单词所在文档ID，在具体文档中的位置}

锁

lock的对象是事务，用来锁定数据库中的表、页、行等。

Innodb实现了两种标准的行级锁：

共享锁(s lock)：允许事务读一行数据

排它锁(x lock)：允许事务删除或更新一行数据

多粒度锁（意向锁）：

允许事务在行级和表级上的锁同时存在。称之为意向锁，分为：

意向共享锁（IS lock），事务想要获得一张表中某几行的共享锁

意向排它锁（IX lock），事务想要获得一张表中某几行的排他锁

如果需要对页上的记录上X锁，首先要对对应的数据库、表、页上IX锁，如果其中任何一个部分等待，那么该操作需要等待粗粒度锁的完成。

一致性非锁定读

数据库默认隔离级别为RR，使用一致性非锁定读。

一致性非锁定读是指InnoDB存储引擎通过MVCC的方法来读取当前执行时间数据库中行的数据。

如果读取的行正在执行update或者delete操作，这时读取操作不会等待锁的释放，而是读取行的一个内存快照。

在RC事务隔离级别下，对于快照数据，非一致性锁定读总是读取被锁定行的最新一份数据快照。

在RR事务隔离级别下，对于快照数据，非一致性锁定读读取事务开始时对应的行数据版本。

一致性锁定读

InnoDB对于select支持两种一致性的锁定读操作：

• select xx for update：对该行记录加X锁
• select xx lock in share mode：对该行记录加S锁

注意：如果不对上述两条语句开启事务，那么仍然使用一致性非锁定读进行读取，因为锁的对象是事务，当事务提交了，锁就释放了。

因此，使用上述语句必须加上 begin，start transaction

外键和锁

对于外键的更新，首先要使用select定位父表中记录，但是此select使用的是select xx lock in share mode。如果父表中对应行记录正在进行update或者delete操作，加了X锁，那么操作子表的事务就会阻塞，直到父表事务释放X锁。

行锁的3种算法

Record Lock：单个行记录上的锁（RC隔离级别下默认）

Gap Lock：间隙锁，锁定一个范围，但不包含记录本身（RR隔离级别下默认）

Next-Key Lock：锁定一个范围，同时锁定记录本身（RR隔离级别下，使用此种算法避免幻读）（当查询的索引含有唯一属性时，会降级为Record Lock，锁住索引本身）

Next-Key Lock降级为Record Lock示例：

phantom problem（幻象问题）：在同一事务下，连续执行两次同样的SQL语句，可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

事务1where条件为>2，实际上对（2，正无穷）加了X锁，因此任何对于这个范围的插入都不是允许的，从而避免了幻读问题。

锁问题

• 脏读
• 不可重复读（MySQL文档中将不可重复读定义为幻象问题）
• 丢失更新

死锁

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

数据库使用wait-for graph（等待图）的方式进行死锁检测，具体为数据库保存两个信息：

• 锁的信息链表
• 事务等待链表

如果事务A等待事务B所占的资源，那么A到B存在一条有向边。

如果等待图中存在回路，则存在死锁。

解决死锁：回滚undo量最小的事务。

死锁示例：

MyISAM 存储引擎

不支持事务、不支持表锁，支持全文索引。

MyISAM 由 MYI 和 MYD 组成，MYD 用来存储数据文件，MYI 用来存储索引文件。只查询而不需要业务的场景可以使用MyISAM引擎。 

MySQL 存储引擎