《MySQL技术内部 InnoDB存储引擎》读书笔记

前言

完结，撒花！这本书是我看过最枯燥的书，也是难得我需要配合度娘来查找概念来理解书中概念的书。这本书对INNODB的方方面面进行讲解，事无巨细，经常看着就会迷失方向，不知何为重点。

而且书中在前面对许多重要概念都一笔带过（可能是因为作者希望每一章都独立成册，不要有太多的前后关联性，但是这样真的对初学者不太友好……），到了很后面的章节才解释（比如ACID的概念），因此特地设立了第0章用于讲解可能遇到的关键术语。此外，对于一些难以理解的概念（比如next-key locking），还是配合了知乎之类的回答来理解的。

看这本书有什么收获？和作者所说的一样，许多开发人员会用CRUD，就觉得自己会用数据库了，我就是这样的：）

毕竟对于实验室的一些开发项目，似乎真的不需要使用事务——是的，我最大的收获，就是知道了事务这个至关重要的东西，不知道事务就像在并发环境中不知道锁一样，但是我真就不知道。

接下来还有一些让我惊讶的功能：辅助索引、分区。我从来不知道除了主键原来还能有别的索引；也不知道数据可以干脆按日期分区存储，便于统计和处理。

排在最后的，才是什么数据库引擎的原理之类的。

是的，虽然这本书真够枯燥的，但是的确没白看——起码，我懂得我真的不会用数据库。

那么，希望同看这本书的你，也能有自己的收获~

----序二----

虽然这本书看起来蛮枯燥的，但是在面试中问的点就那么多。

后续加入了一些常见面试题的解答和分析，力求知行合一~

0.基础知识/术语汇总

DBA

Database Administrator 数据库管理员

ACID

ACID是事务的四大要求：

原子性atomicity：要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作

一致性consistency：事务执行前后，数据库都必须处于一致性的状态，反复读取一个值不会出现更改，读取所有值的版本都必须和事务开始时一模一样（就好像事务开始后，整个数据库被冻结不允许别的线程修改了一样）

隔离性isolation：在并发环境中，并发的事务是相互隔离的，一个事务的执行不能被其它事务干扰，既不会读取到事务开始后数据库的更改，写入的数据也不能被别的事务读取。不过事务的隔离级别也分为4种，分别对应隔离的四大问题。

持久性durability：一旦事务提交，对数据库中的数据更改就会永久保存到数据库中。即使数据库崩溃或者机器宕机，数据库中都会恢复到事务成功提交后的状态

这四者中，原子+无并发环境即可保证一致性（Redis是典型例子）；在并发环境中，需要额外的隔离属性才能保证一致性。

事务隔离性/会遇到的问题

脏读：脏读即读到别的事务还未提交的更改，违反了隔离的要求。这种隔离被称为**“读未提交”（read uncommited）。这是最低级别的隔离属性，等同于没有任何隔离。采用已提交读**隔离属性即可解决，实际上是加了行读锁。

不可重复读：即读取到事务开始后别的事务提交后的数据，违反了一致性的要求。造成的影响是：在该事务中，前后两次读取同一值，会出现不同的结果。与之对应的隔离属性称为：已提交读（read commited）。采用可重复读隔离属性即可解决，实际上是加了行写锁。

幻影读：执行相同的SQL语句，会读取到之前不存在的行。与不可重复读的区别在于，广义的不可重复读只表达了读取一行已存在的数据时，前后读取到的不一样（也就是禁止对同一行UPDATE），而幻读强调读取到之前不存在的数据（INSERT操作）。采用串行化即可解决。

也就是说：共会遇到三种问题，脏读，不可重复读，幻影读。共有四种事务隔离属性：读未提交，已提交读，可重复读，串行化；分别对应无锁，无锁，行锁，表锁（还有下面看到了next-key locking），要求依次增加，并发性能依次降低。

MYSQL默认的隔离属性是可重复读，并且通过next-key locking，实际上已经避免了幻影读问题。

此外，还有一种并发导致的问题，不是由隔离产生的，叫做丢失更新。比如对账户余额的更新操作（本质上是一个类似a++的自增操作，需要先读，然后在读取到的值基础上进行更新）。需要通过整个过程加写锁来保证。

MVCC

多版本并发控制，可用于提高并发性：提供一个行的多个版本，一方面保证事务隔离属性，另一方面又不影响读的并发性。

但是MVCC并不能解决幻读问题，只能保证，在对该行数据仅SELECT时无幻读问题，对于先UPDATE后SELECT的情况，就会有幻读问题，详见MVCC 能解决幻读吗？

全文检索

是将存储于数据库中整本书或者整篇文章的任意内容信息查找出来的技术。如，搜索引擎中查找关键字。innodb主要是通过b+树来实现索引的，而b+树只能检索以某些关键字开头的记录（想想树的结构），因此导致最初并不支持全文检索。

OLTP&OLAP

二者的全称分别为在线事务处理（Online transaction processing）和在线分析处理（Online analysis processing），前者的事务一般比较短，要求快速响应；后者事务较长，要求高吞吐。前者的应用比如购物网站等；后者的应用比如数据仓库等。

AIO

异步IO。Native AIO则是操作系统支持的异步IO。

REDO/UNDO

这两者对应到编辑文档的操作时，分别是“重做”和“撤销”。

Redo是持久性质的体现，在修改页之前，先会写入Redo日志。所以Redo中记录了对页的修改。

一致性、持久性通过UNDO LOG实现。其中记录了事务对数据库的逻辑操作。当需要回滚数据库时，数据库会执行相反的逻辑操作进行回滚。此外，MVVC也是通过UNDO实现的。

面向列/行存储

innodb是面向列的，也就是说数据是按行存放的。按列存储的数据库对数据分析和数据压缩非常有利。

1. MYSQL体系结构和存储引擎

1.1数据库&实例

数据库：一般是文件（包括存储于磁盘和内存中）的集合。如果存储与磁盘上，可能是以.frm MYD MYI ibd结尾的文件

实例：由后台线程与一个共享内存区组成。

实例通常与数据库是一一对应的。但是在集群的时候，一个数据库可以对应多个实例。

1.2MYSQL体系结构

MYSQL的组成部分如下图所示。其中包含了连接池，管理工具，SQL分析器优化器等。需要注意的是，mysql与其它数据库最大的区别之一就是采用了插件式的表存储引擎。

1.3 MYSQL存储引擎

由于mysql的开源特性，因此用户可以根据mysql预定义的存储引擎接口编写自己的存储引擎。对原有的存储引擎不满意，也可以通过修改引擎源码来实现改进。

1.3.1 innodb存储引擎

主要面对OLTP应用。其特点为行锁设计，支持外键，支持非锁定读

采用MVCC提高并发性，实现了四种隔离级别，默认为可重复读，采用next-key locking来避免幻读。

提供了插入缓存，二次写，自适应哈希索引，预读等高性能高可用性功能。

表中数据采用聚集方式存储，也就是说表中都是按主键的顺序存放的。

1.3.2 MyISAM存储引擎

不支持事务（这也是很多对mysql不支持事务的误解的由来），不支持外键，表锁设计，支持全文索引，面对OLAP应用。

只缓冲索引文件，不缓冲数据文件（innodb的叶子节点实际上就是数据）

1.3.3 ndb

是集群引擎。将所有数据都放在内存中。

1.3.4 Memory

之前也成为HEAP存储引擎。将表中数据存放于内存中。适合存储临时数据。默认采用哈希索引而非B+树。

MySQL采用该引擎作为临时表来存放查询的中间结果集。

1.3.5 Archive

可以压缩数据，非常适合存储归档数据，如日志信息等。

1.3.6 Federated

不存放数据，而是指向远程MySQL服务器上的表，类似于SQL SERVER的链接服务器和ORACLE的透明网关。当前只支持MySQL表。

1.3.7 Maria

新开发的引擎，用于取代原有的MyISAM引擎，从而称为mysql的默认引擎。

1.5 连接MySQL

TCP/IP，命名管道&共享内存，UNIX域套接字（只适用于CS位于同一台服务器的情况）

2.InnoDB存储引擎

2.3 体系架构

下图简单显示了体系架构。具有多个内存块，这些 内存构成了一个大的内存池，负责如下工作：

维护所有线程需要访问的多个内部数据结构

缓存磁盘数据，方便快速读取。同时也会缓存对磁盘文件的修改。

重做日志缓冲

后台线程主要负责刷新内存池中的数据；将已修改的数据文件刷新到磁盘；保证在数据库发生异常的情况下innodb能恢复到正常运行状态。

2.3.1 后台线程

Master Thread

核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性。主要包括：

脏页的刷新

合并插入缓存

UNDO页的回收

等等等

IO Thread

主要负责IO请求的回调.

具有四种线程：write, read, insert buffer, log

Purge Thread

事务提交后，undolog可能不再需要（为了维护其它未提交的事务的MVCC，可能还需要这些日志），需要该线程来回收。

Page Cleaner Thread

将MT的脏页刷新操作移到该线程中完成，充分利用多线程的优势并且减轻MT的工作压力。

2.3.2 内存

缓冲池

将从磁盘读到的页放置于缓冲池中

对于页的修改操作，先修改缓冲池中的页，再以一定频率刷新到磁盘上。刷新机制称为：check point。

缓冲池中缓冲的数据页类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，锁信息，数据字典信息等。因此缓冲池中不仅有索引页和数据页，但是它们的确占大部分。

LRU/Free/Flush List

缓冲池（中的数据和索引页）通过LRU（Least Recently Uesd）算法进行管理，即最频繁使用的页放置于队列首。

优化的LRU算法：新读取的页，放置于midpoint（一般为靠近队尾的地方）位置，避免热点页直接被挤出去。同时，需要多次读取后，新页才能越过midpoint，成为热点数据。

缓存命中率：正常情况下，应该在95%以上。

Free队列存放空闲页，当读新数据时，会先检查Free页，如果没有，则用LRU淘汰队尾元素。

Flush队列存放脏页。注意，脏页存在于Flush和LRU列表之中。

重做日志缓冲（redo log）

重做日志会先写入缓冲区，再写入磁盘

三种情况会将其刷新到磁盘：MT 每秒都会刷新；每个事务提交时；重做日志缓冲池剩余空间小于一半时。

额外内存池

对一些数据结构本身的内存进行分配时，需要从额外内存池中申请。

2.4 Checkpoint

当事务提交时，先写重做日志，再修改页，这样当宕机导致数据丢失时，可以通过重做日志完成数据的恢复。这也是ACID中D的要求。

那能不能直接修改页？为什么要先存储日志？

因为修改页很可能是随机写，而redo日志一般都是顺序(循环)写入的，使用顺序写入替代随机写入，甚至可以避免b+树节点分裂的情况，这样操作会很快。实际上包括DWRITE等操作，都是用顺序写来替代随机写

那能不能只使用重做日志？

不能！顺序结构的查找很慢，要使用b+树进行排序
此外，缓冲和重做日志不可能无限大，否则数据库重启时需要大量的时间来通过重做日志恢复到最近的状态。而内存缓冲的大小也要受到内存大小的限制。

因此采用checkpoint技术。定时将脏页刷新回磁盘

触发cp有两种方式：第一种，是数据库关闭时，默认会将所有脏页刷新回磁盘。运行时通过以下四种机制刷新

主线程定时刷新；当LRU列表中空闲页过少时；重做日志不可用时（由于先写重做日志再写页，所以太多只要脏页存在，那与之对应的重做日志就必须存在，否则宕机后无法恢复。因此重做日志没有空闲时，就不允许有新的修改存在了）；脏页占比过多（实际上还是保证LRU有足够的空闲页，类似第二种）

2.5 MT的工作方式

2.5.1 三大循环

主循环、后台循环、刷新循环、暂停循环。

主循环

有两大部分的操作：每秒钟和每10s的操作

每秒钟的操作有：

刷新日志缓冲到磁盘，即使事务还未提交（一定会进行，所以再大的事务提交时间也不长）

合并插入缓冲（可能，根据当前IO压力判断是否发生）

至多刷新100个脏页到磁盘（可能，根据当前脏页比例判断）

无用户活动，则切换到后台循环状态（可能）

每10s的操作有：

合并至多5个插入缓冲（一定会进行）

刷新日志缓冲到磁盘（和每秒一次的一样）

删除无用UNDO页（purge操作。无用是指当前没有事务再需要某个时间点之前的版本信息，可以安全删去）

刷新100个或10个脏页到磁盘（根据脏页比例）

刷新100个脏页到磁盘（可能，根据IO情况）

后台循环（background loop）

当数据库空闲、关闭时，切换到这个循环。进行的操作有：

删除无用的undo页

合并20插入缓冲

跳回主循环

不断刷新100个页直到符合条件（可能，跳转到刷新循环中实现）

刷新循环（Flush loop）

刷新脏页

2.5.2 1.2.x之前的MT

由于1.0.x之前，对IO的限制过多，在SSD出现之后，无法发挥硬盘全部的IO能力，因此这一版本主要是提高了硬盘的IO吞吐量。提高方式为：将许多固定参数改为可配置，或者改为百分比参数。

2.5.3 1.2.x版本的MT

将刷新脏页的操作分离到Page Cleaner Thread线程中

2.6 关键特性

两次写（可靠性）

插入缓冲

自适应哈希索引

异步IO

刷新邻接页

2.6.1 插入缓冲（Insert Buffer）

IB介绍

由于innodb中主键为行唯一的标识符，而一般都是自增的，所以如果没有辅助索引（也就是只有主键），按主键递增顺序的插入比较快，因为不需要读取别的页。

但是一个表上可能还会有多个非聚集索引，在进行插入时，数据页按照主键顺序存放，很快；但是非聚集索引的插入不是顺序的，需要离散访问非聚集索引页。

为了提高插入性能，对于非聚集索引的插入和更新操作，首先判断非聚集索引页是否在缓冲池中，如果在，直接插入，反之放入IB中。然后再以一定频率合并IB和辅助索引页子节点。这样通常能将多个插入合并到一个操作中(因为通常会在一个索引页中），提高了非聚集索引的插入性能。

也就是说：当存在非聚集索引时，非聚集索引的更新是离散的，innodb采用insert buffer来加快辅助索引的更新操作。但是毕竟buffer还是buffer，还是要定时和真正的辅助索引合并的。

需要满足两个条件：辅助索引&索引不是unique

为何？主键一般满足递增性，无需IB；而如果索引是unique的，那么插入的时候，引擎就需要保证插入的辅助索引保证唯一性，这就需要再去查辅助索引页，这会导致离散读取，那么IB就毫无意义。如果去掉了unique限制，那么只要往IB里一放就OK

Change Buffer

1.0.x后，引入 change buffer，可以对Insert delete update都进行缓冲。适用范围还是辅助非唯一索引。

IB内部实现

IB 也是采用B+ 树实现的

Merge

发生在三种情况下：辅助索引页被读到缓冲池时；该辅助索引页已经无可用空间（？？？WTF）；MT

2.6.2 两次写

为了提高稳定性：当写入过程中宕机，那么写入的页只写入了部分，会造成整个页都损坏。所以，在写入脏页之前，为了提高稳定性，需要先备份脏页

由两部分构成，一部分为内存中的double write buffer，大小为2MB（一般来说一个页的大小为16KB），另一部分为物理磁盘上共享表空间中连续的128个页，大小也为2MB。

在对脏页进行刷新时，首先将脏页复制到内存中的DWB，然后将DWB中的内容分两次写入共享表空间的物理磁盘上，然后立即同步磁盘。由于这个过程是顺序写，因此开销不大。这个过程相当于将脏页进行了备份

完成DWB的写入后，再将其中的页写入各个表空间文件中。

这样，当写入页时宕机了，恢复过程中，innodb可以从共享表空间的DW中找到该页的副本，将它复制到表空间文件即可恢复。

2.6.3 自适应哈希索引（Adaptive Hash Index）

一般的查找方法为B+树，生产环境中，高度一般为3-4层，因此需要3-4次查询。而hash只需要一次查询。

如何计算这个3~4层？

一般innodb的页具有16k，主键采用大整数8B，指针为6B，粗略计算之后，每个索引页的出度为1170；
如果每一行数据1k，粗略计算之后，每个数据页大约16行；
这样，两层b+树，就有1170x16>1.8W条数据；
三层b+树，就有1170x1170x16>2000W数据

innodb引擎会自动根据访问的频率和模式来自动地为热点页建立哈希索引。

哈希索引的特点

只能做完全匹配的查询（常数级复杂度），无法做范围查询或模糊查询（而b+可以快速地进行右侧模糊的查询）

2.6.4 异步IO（AIO）

用户可以在发出一个IO请求后立即再发出另一个IO请求，当全部IO请求发送后，等待所有IO操作的完成。

优势之一是可以进行IO MERGE操作，合并连续的IO操作来提高效率。

2.6.5 刷新邻接页

当刷新一个脏页时，会检测所在区（区内存储连续的页）的所有页，如果还有脏页，就一并刷新。

但是也有问题：写入了快速变化的页，这个页很快就变脏，因此造成了额外的IO开销（本可以这个页自己变化多次后再写入的）；固态硬盘具有较高的随机读写性能，不太需要该特性。

2.7 启动 关闭和恢复

关闭时的行为：完成所有的undo页刷新、插入缓冲合并、脏页刷新；只需刷新脏页；只写入日志。中间为默认行为。

启动时可以指定是否恢复、恢复的程度。这样可以加快恢复的速度。

3. 文件

3.1 参数文件

动态、静态参数：运行时是否能修改。

3.2 日志文件

分为错误日志、二进制日志、慢查询日志、查询日志

3.2.1 错误日志

SHOW VARIABLES LIKE 'log_error'\G;

显示错误日志的位置，打开后可以观看错误信息

3.2.2 慢查询日志

记录所有执行时间超过阈值（自己设定）的SQL语句。可用于定位存在问题的SQL语句。

后续版本增加了查询语句对逻辑读取和物理读取（前者包括缓冲池+磁盘IO，后者只包括磁盘IO）的统计。可以将IO超过阈值的语句记录到慢查询日志中。

3.2.3 查询日志

记录所有对MYSQL数据库请求的信息。包括数据库连接、未成功执行的SQL等信息

3.2.4 二进制日志

记录所有对MYSQL执行更改的操作，因此SHOW SELECT这类操作并不会被记录。

分为三种格式，STATEMENT（逻辑SQL语句）, ROW（记录表的行更改情况）, MIXED（默认采用STATEMENT，特殊情况下采用ROW，比如采用了UUID()等不确定函数时）。一般来说，ROW的可靠性更好（例如采用了随机函数，那就必须采用row格式，否则数据不一致），但是所需的空间也更大。

作用：恢复（可以通过二进制日志进行point-in-time的恢复），复制（通过网络传输和执行二进制日志使得一台远程MYSQL数据库与主数据库进行同步，可以搭建类似集群的效果），审计（判断是否由对数据库进行注入的攻击）

3.4 pid文件

实例启动时，会将自己的pid写入一个文件中。一般位于数据库目录下，文件名为 主机名.pid

3.5 表结构定义文件

.frm，记录表结构的定义、视图的定义。

3.6InnoDB引擎存储文件

之前的全都是mysql数据库本身的文件，和存储引擎无关。接下来要介绍innodb存储引擎独有的文件。这些文件包括重做日志文件和表空间文件。

3.6.1 表空间文件

会生成一个默认的表空间。

可以通过多个文件组成一个表空间，或者设定每个表都有一个独立的表空间。这样就不会将全部数据都存在默认表空间中。这个表空间存储该表数据、索引、插入缓冲等信息。其余信息都放在默认表空间中。

3.6.2 重做日志文件

记录innodb引擎的事务日志。具有重做日志组，采用循环写入的方式。

与二进制日志的区别：

1.二进制日志记录mysql数据库有关的日志，包括各个引擎。而（innodb引擎）重做日志只记录innodb引擎本身的事务日志。

2.记录内容不同，二进制日志为逻辑日志，而重做日志为页的物理更改情况。

3.写入时间不同，二进制在事务提交之前进行提交，只写一次；而重做日志在事务进行过程中也会写入，即使还未提交（比如MT中每秒都会写一次）

可以设定，事务提交时，0不写入，1同步写入，2异步写入重做日志。2，3的区别在于，后者在数据库崩溃而系统未宕机时，还能保证数据库数据不丢失，而前者即使系统宕机也可以保证。所以为了保证持久性，必须设置为1.

4. 表

表是关于特定实体的数据集合。

4.1 索引组织表

innodb中，表都是根据主键顺序组织存放的。这种存储方式的表称为索引组织表。

每张表都必须有一个主键，如果创建表时没有显式定义主键，则会1.选择非空唯一索引2.自动创建一个6字节大小的指针。

4.2 innodb逻辑存储结构

表的数据被逻辑地存放于一个空间内，称为“表空间”。表空间由段、区、页（块）组成。

4.2.1 表空间

即使设立了独立表空间，共享表空间内还有回滚（undo）、插入缓冲索引、二次写缓冲等数据。

4.2.2 段

段的概念在汇编中也存在，含义是“存储不同类型数据的地方”，比如数据段，堆栈段，代码段等

表空间由各个段组成，常见的段有数据段 索引段 回滚段等。需要注意innodb为索引组织的，因此数据即索引，索引也是数据，数据段为B+树的叶子节点；索引段位B+树的非叶节点。

4.2.3 区

区由连续的页组成

4.2.4 页

页是innodb磁盘管理的最小单位。可以设置为4 8 16K，如果设置完成，所有表中的页大小都是固定的。

4.2.5 行

innodb是面向列的，也就是说数据是按行存储的。

4.3 行记录格式

4.3.1 Compact行记录格式

正如其名，是一种紧凑的行记录格式。可以看到，其行记录中具有NULL标志位，所以对于NULL数据，只需置位，而无需存储任何数据。每行数据除了用户定义之外，还具有事务ID和回滚指针列；如果不具有主键，还有隐藏的主键列。

不管是CHAR还是VARCHAR类型，NULL都不占空间（与接下来的Redundant不同）

在页内部通过链表的结构（record header的最后俩字节为next record指针，指向下一行行记录）来串连各行记录。

4.3.2 Redundant行记录格式

是MYSQL5.0之前该引擎的行记录存储方式。可以看到它不具有NULL标志位。存储NULL的CHAR时，会占用整个空间（VARCHAR则不用），这点和Compact格式有很大区别。

4.3.3 行溢出数据

用于存放列中的大数据，如大的BLOB，TEXT，VARCHAR等。

可以理解为：行溢出数据就是存储了大对象的指针，指向行溢出页的大对象。

INNODB的VARCHAR，整行所有的VC长度加起来最长可以有65535个字节。

4.3.4 Compressed和Dynamic行记录格式

INNODB 1.0.X新引入的文件格式。对于BLOB采取完全行溢出的方式存储（即只存储大对象的指针）

4.3.5 CHAR的存储

CHAR(n)中的N指的是字符的长度，而不是字节的长度。所以在不同字符集下，CHAR可能会有不一样的长度。

因此对于多字节字符编码的CHAR类型，会被视为变长字符类型。也就是CHAR被视为了VARCHAR！

4.4 数据页结构

数据页即为B+树的叶子节点。里面存放着表中行的实际数据。

4.4.4 User Record & Free Space

UR即为实际存储行记录的内容。FB为空闲空间，在一条记录被删除后，该空间会加入FB的空闲链表中。

4.4.5 Page Directory

也就是 页目录。其中存放了记录的相对位置，这些相对位置也称为记录指针/槽/目录槽等。一个槽内可能包含多个记录指针，槽中的记录按照索引键值顺序存放，这样可以用二叉查找迅速找到某（几）条记录的指针，其实就像是页内部还存了个小b+树索引。

这里举个例子说明：假如行记录的主键有{a b c d e f g h i j k l}这么多，但是实际槽中可能只有a e i三条。所以即使找到了槽的指针，也只是个大致范围，还需要通过行记录的记录头中的next record指针（见4.3.1节）来找到确切的行记录。

B+树索引本身并不能找到具体的一条记录，只能找到该记录所在的页。数据库把页载入内存，然后通过Page Direction进行二叉查找，最终才能找到对应的行记录。由于最后一步的二叉查找在内存中进行，而且二叉很快，相对于磁盘IO的开销，时间可以忽略。

4.4.6 File Trailer

用于检测页是否完整地写入磁盘。也就是有checksum部分。

4.6 约束

4.6.1 数据完整性

实体完整性：有一个主键

域完整性：每列的值满足特定条件（如在某个范围之内）

参照完整性：保证两张表之间的关系。

4.6.6 触发器与约束

触发器的作用是在特定的SQL语句（如INSERT DELETE UPDATE）调用之前或者之后自动调用一些SQL命令。

MYSQL只支持按行触发。触发器可以对数据进行额外的分析和处理。

4.6.7 外键约束

外键是干啥用的？？

4.7 视图（view）

视图是一个命名的虚表，其中的数据没有实际的物理存储。但是可以进行更新等操作（可以认为是基本表某些行的一个别名/引用）

4.8 分区表

分区的过程是将一个表或索引分解成多个更小、更可管理的部分。

MYSQL只支持水平分区（即将不同的行记录分配到不同的物理文件中），不支持垂直分区。

支持以下几种类型的分区：

RANGE分区：行数据的某个给定连续区间的列值被放入分区。

LIST分区：和RANGE类似，只不过是离散的值

HASH分区：根据用户自定义的表达式返回值进行分区

KEY分区：根据MYSQL提供的哈希函数进行分区

当表中存在主键或唯一索引时，分区列必须包含唯一索引（如联合索引）！

唯一索引允许NULL值，只需指定如何分类即可。

4.8.2 分区类型

RANGE分区

例如，可以以时间分区，根据年来分区。这样，SQL优化器会只搜索指定分区。如果启用了分区，要根据分区特性来编写最优SQL语句。

LIST分区

例如，指定VIP等级为白金、钻石的分到1区，为大众、白银、黄金的分到2区。

HASH分区

一般来说，是为了将数据均匀地分布到各个分区内。

还有一种LINEAR HASH分区。它的计算方法为：

1.先取大于分区数量的第一个2的幂值V（如：分区4，则为4；分区5，则为8，分区9，则为16）

2.所在分区=key & (V-1).

实际上，这两步操作，只不过就是取了key的低几位罢了！

？？？它的优点在于增加、删除、拆分、合并分区变得更快捷。缺点在于数据分布可能不够均匀。

KEY分区

HASH采用用户自定义的函数分区，而KEY采用内置函数

COLUMN分区

之前的分区都要求数据是整型，如果不是，则需要通过函数转换成整型再分区。COLUMNS分区可以直接使用非整型的数据进行分区。

4.8.5 分区和性能

对于OLAP，分区的确可以提高性能，因为OLAP应用需要频繁扫描一张很大的表。如，按时间戳分区后，则只需扫描相应的分区。

对于OPTP应用，则不一定。因为B+树层次是基本相同的。即使层次低了一层（毕竟一般也就3~4层），（以HASH分区为例）但是进行整表查找某个值时，需要扫描所有分区才能找到，每个分区扫描的时候得走整个B+树，实际上IO开销翻了好几倍。:)我感觉HASH分区就是一个比较NC的设定，访问连续值时反而要扫描多个表？

5. 索引与算法

5.1 索引概况

B+ 全文 哈希

B+并不能找到给定键值的具体行，只能找到数据行对应的页，通过把页读入内存，再在内存中进行查找。

5.2数据结构和算法

涉及二分查找、二叉搜索树（满足左子树<节点值<右子树）、平衡二叉树（左右子树高度差<=1）等概念。

5.3 B+树

b+树=b树+索引顺序访问方法（用人话来说，就是叶子节点前后双向链接起来（而且头尾也是链接的），这样连续读取时就不需要再从父亲节点找页了）

b+树的孩子节点一般>2。这是为了尽量降低树的高度（因为索引都存在磁盘上，树越高就需要更多次的IO，而随机IO相比顺序IO，开销更大）

b+的叶子节点都在同一层上，这样可以得到更稳定的访问效率（不会出现访问一个节点要3次IO，而另一个却要5次的情况）。

b+树的插入要涉及到分裂和合并操作。分裂就是叶子/索引节点满，需要分裂成两片叶子的情况（在可能的情况下，也可能把数据放到左右兄弟上）；合并就是当叶子/索引存储的页数量太小（小于填充因子时，比如小于一半的叶子/索引容量）

b+的根至少要有两个孩子。否则，直接减少一层，减少一次IO不好吗：）

从上面的叙述可以看出，b+树的设计是为了磁盘或其它直接存储设备设计的查找树。它的各种操作核心都是为了：减少IO次数。体现有：减少树的高度（比如根的限制，各种合并）、尽量减少分裂

以下整理b+树相关问答题

b&b+的区别？
 - 索引存不存数据（便于稳定、快速的查找，否则就得中序遍历）
 - 叶子有没有双向链表连接（便于范围查找）
 
为啥不使用二叉树？主要是从io次数上考虑

为啥hashmap中不使用b树，而是红黑树？因为二叉查找效率高，更适合内存查找（为什么效率高？b+每一层，需要查k次，范围减少到1/k；而二叉树查k次，范围都减少到1/2^k了

5.4 B+树索引

可分为聚集索引和辅助索引，区别在于叶子节点是否存放行的信息。

5.4.1 聚集索引

就是按每张表的主键构造一棵B+树。查询优化器倾向于使用该索引查找，因为叶子节点直接可以找到数据。

数据页上存放的是完整的每行的记录。而索引页中，仅存放键值（也就是B+的“分隔符”）和指向数据页的偏移量。

5.4.2 辅助索引

不包含行记录的数据。除了包含键值，还包含一个书签，指向相应行数据的聚集索引键（也就是主key的键值！）

可以看出：辅助索引的存在不影响聚集索引的组织。因此一张表上可以有多个辅助索引。

当通过辅助索引查找数据时，innodb会查找辅助索引B+树，叶节点存有相应行记录的主键，然后通过聚集索引的B+树来找到对应一个完整的行记录。

也就是说：通过辅助索引查找一个行记录时，需要1.找到符合辅助索引的行记录的主键2.找到主键对应的行记录。也就是说，要从上到下搜索两棵树！但是他也是有好处的，比如可能可以避免表扫。

5.4.4 B+树的索引管理

用户可以设置对整个列的数据进行索引，也可以对一个列的开头进行索引，例如，对VARCHAR的前100个字符进行索引。

5.5 Cardinality

并不是所有查询条件中出现的列都需要添加索引。一般经验是：

1.通常只访问表中很少的一部分（毕竟需要查找两棵树）

2.辅助索引具有高选择性（性别、地区等就是低选择性，通过这个索引查找出来的行会有很多，辅助索引基本没意义）

Cardinality记录了表中不重复记录数量的预估值，所以当Cardinality / rows ≈ 1时，而且每次只访问表中少量数据时，非常有必要添加辅助索引。

Cardinality的统计是放在存储引擎层进行的。采用采样的方式进行，所以多次计算可能得出的结果不一样。

如何设计数据库表的索引？
 - 多查少改，因为索引主要是加快了查找（包括排序和范围查找）的速度，一旦涉及到插入，那就要在多棵树中进行插入，尤其是非聚集索引，通常是随机插入，因此插入的效率会随着索引的增加而变低
 - 哪些需要频繁查询？排序？group by？因为这些都是运用索引的地方
 - 需要查出哪些数据？尽量让需要查找的列被索引覆盖，减少回表次数。
 - 区分度。对于区分度低的列（比如性别），无需索引，因为会使索引毫无意义（索引就是为了可以logn级找到匹配的数据，如果大量都是重复的，那就会退化成线性的了。
 - 主键这种聚集索引，尽量使用自增的（少用uuid等随机生成的无序字符串），因为这样插入新记录时，聚集索引树的叶子节点也是按顺序插入的，可以尽量避免b+树节点分裂的情况。

5.6 B+树索引的使用

5.6.2 联合索引

与单个索引的创建方法一样，只不过有多个索引列。

当使用了（a, b）这样的索引时，查单个列的索引a还是可以使用这个辅助索引的，但是查b就没法使用这个索引了。这是由于B+树的特性决定的。

使用联合索引，如：（user_id, date），当user_id确定后，date就是有序的，因此如果获取某个user_id的购买记录并排序，可以省去排序的操作。

5.6.3 覆盖索引

一般来说，辅助索引是不包含行记录信息的，但是覆盖索引可以查到主键+该辅助索引的具体信息。 由于不包含整行记录的所有信息，因此可能的IO次数也更少。因此当统计count时，可能就会偏向于采用辅助索引统计。

比方说，有一个辅助联合索引为：(key1, key2, …)，而辅助索引B+树一定存储着主键，所以实际上辅助索引B+树上存储的内容有：(pkey1, pkey2, …, key1, key2, …)，其中pkey表示主键，当然，主键也可以是联合的！

5.6.5 索引提示

用于提示数据库使用某个索引

5.6.6 Multi-Range Read

在进行辅助索引查找时，会先查找出所有的主键，然后按照主键排序，统一查找。这样可以把随机访问转化成较为顺序的数据访问。

5.6.7 Index Condition Pushdown优化

例如，执行SELECT … FROM … WHERE XXX=YYY时，会先SELECT，然后再筛选出符合条件的语句。

而ICP优化则是在SELECT数据的同时就进行了筛选，将筛选工作放到了存储引擎层，可以大大减少上层SQL层对记录的索取，进而提高整个数据库的性能。

5.7 哈希算法

5.7.3 自适应哈希索引

数据库自身创建并使用，不能进行干预。对于字典型的查找非常快，对于范围查找则无能为力。因此只能用于搜索等值的查询。

5.8 全文检索

介绍详见0章术语解释。

5.8.2 倒排索引（inverted index）

在辅助表中存储了单词与单词位置的映射。通常采用关联数组来实现：

形式1（inverted file index）：{单词， [单词所在的文档id1，单词所在的文档id2，…]}

形式2（full inverted index）：{单词， [(单词所在的文档id1，[单词所在文档的具体位置1, …]), (单词所在的文档id2，[单词所在文档的具体位置1, …]) ]}

5.8.3 INNODB全文检索

full inverted index实现。

stopword：比如the等词，不具有具体的意义，因此不进行索引分词操作。

QueryExpansion： 进行扩展查询。比如查找“数据库”这个关键字时，可能MYSQL ORACLE SQLSERVER等关键字都是有帮助的。但是很可能会带来很多不相关的查询。

6.锁

开发多用户、数据库驱动的应用时，一方面要最大程度利用数据库的并发访问，另一方面还要确保用户能以一致的方式读取修改数据。因此需要引入锁。

不同的数据库对锁的实现差别很大。

INNODB支持一致性非锁定读、行级锁支持。而且它的行锁是没有额外开销的。

6.2 lock & latch

数据库中它们俩都可以被称为锁。但是前者是面向事务的，也是本章关注的内容；而后者是面向内存中共享数据的，是数据库底层实现时的锁。

lock是有死锁检测机制的，当发现死锁时，会报错并解决死锁（如回滚事务）

6.3 innodb中的锁

6.3.1 锁的类型

有以下两种行级锁：共享锁（S锁，允许事务读一行数据），排他锁（X锁，允许事务删除、更新一行数据，也就是修改）

可以允许多个共享锁同时存在，但是共享锁存在时，排他锁需要等待；排他锁存在时，共享、其它排他锁都需要等待。当然，这指的是对同一资源（在innodb中，通常指的是同一行）的锁。

意向锁（用IS IX表示）：当事务上细粒度的锁时，需要先获取粗粒度的意向锁。比如：当需要获取行锁时，需要先上表的意向锁。它的意义在于：别的事务想要申请这个表的锁时，首先要看看这个表的意向锁有没有冲突！比如，事务A上了行的S锁，事务B想要上表的X锁，那么数据库怎么知道这个表能不能上X锁呢？这就需要先看看表的意向锁，如果表已经被上意向锁了，那显然不可能再加上一个X锁，因为这就冲突了（怎么能某一行被一个事务锁定，而整个表又被另一个事务用排他锁锁住呢？）

意向锁和锁是互斥的，意向锁和意向锁是兼容的

6.3.2 一致性非锁定读

这是默认的读取方式。

通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果当前读取的行正在执行修改操作，那不会等待锁释放，而是读取行的快照数据。这是通过UNDO段来完成的。UNDO又用于在事务中回滚数据，因此快照数据本身也没有额外开销。

对于不同的隔离级别，读到的版本也不同。在可重复读的隔离级别下，会读到事务开始时的版本（不然怎么保证事务隔离性？）；在读已提交的隔离级别下，会读到最新版本的数据（没有任何异议吧？）

6.3.3 一致性锁定读

虽然默认为一致性非锁定读，但是在一些情况下，用户需要对读取操作进行加锁以保证数据逻辑一致性。

比如：网上购物，读取银行卡金额的时候，就必须获取读锁，否则读取的时候可能钱购，提交的时候发现钱不够了：）

可以采用如下的SQL语句进行锁定读：

SELECT ... FOR UPDATE // 加上X锁
SELECT ... LOCK IN SHARE MODE // 加上S锁

需要注意：即使加锁，别的事务一样可以通过一致性非锁定读进行读取。只不过本事务显式声明了需要锁定读，那就会先获取锁再读。

6.4 锁的算法

6.4.1 行锁的3种算法

Record Lock：单个行记录上锁

Gap Lock：锁定一个范围，但是不包括记录本身

Next-Key Lock：锁定一个范围，且包括记录本身。等于R+G Lock

Next-key Locking

当查询的索引含有唯一属性时，且是点查询时（也就是WHERE a=1这种，而不是a>1这种。后者一定会进行范围锁定），InnoDB会将NKL降级为行锁，只锁住索引本身而不是范围。

在对非唯一索引进行锁定时，还会使用next-key lock。例如，在某个表中，有主键a和辅助索引b。当执行

SELECT ... FROM ... WHERE b=3 FOR UPDATE;

时，假如这时整个表共有三条记录，分别为b=1, b=3, b=6，且b=3的记录主键a=5.那么将会上三个锁：

该行的行锁；b=(1, 3)的gap锁；b=(3,6)的gap锁。

这是因为：对于唯一键，因为已经通过主键锁定，所以不可能存在修改的情况。那么如果根据主键查询，只要上了锁并且未退出事务，一定可以满足一致性要求。而对于非唯一的辅助索引键，执行了上面的语句后，很可能别的事务插入了新的行，而辅助索引也是3，那么再次执行该语句很可能就会返回两个结果，造成幻读问题。

那么问题来了，我不能只锁住b=3的节点，不允许所有的b=3的辅助索引插入吗！（以下解释来自关于mysql innodb next-key lock 的一些疑问？）

这是对gap锁的理解问题：gap锁表示，在这个辅助索引节点的叶子节点的前一个叶子节点之间，不允许插入新的索引。也就是说，(1, 3)的间隙锁，表达的意义是：1和3的叶子节点之间，不允许塞入任何的辅助索引！

那么，我不允许当前叶子和前一个索引之前塞入任何值，也不允许当前叶子后后一个索引之间塞入任何值，这样就能避免辅助节点中塞入b=3的索引。当然，这也会导致在这个范围内的其它值都无法插入了，这是副作用，也是innodb实现的细节，并没有非常具体的为什么（有点像自己做项目时，甲方改需求，不想改动太大，所以少加几行代码苟一苟）

请注意，什么Next-key Locking解决幻读问题，前提是要自己上锁！！！如果没上锁，就不要谈什么解决幻读！！

6.4.2 幻读问题

指在事务执行过程中，两次执行同样的SQL语句，第二次执行会返回之前不存在的行。

INNODB在可重复读的隔离属性下就可以解决幻读问题，而ORACLE必须在SERIALIZABLE隔离属性下才可以。

为了避免幻读问题，在可重复读的隔离级别下，上行锁时，会根据SQL语句对区间（而不仅仅是查询到的行记录）上锁。比如，数据库中现有a=1, 5两个数据，如果使用MySQL，使用可重复读的隔离属性，执行下面的SQL语句

SELECT * FROM t WHERE a > 2 for UPDATE;

则会对a=(2, +∞)都上X锁，而不仅仅是a=5这条记录本身。

如果不想使用NKL，则可以将隔离属性改为读已提交或在配置文件里取消NKL，这样就只会采用行锁。这时如果还采用刚刚的语句，就会出现幻读问题。

6.5 锁问题

锁提高了并发，但是会带来潜在问题。由于事务隔离性的要求，锁只会带来以下三种问题。

脏读、不可重复读、丢失更新。前两者都已经在第0章中介绍过了。

6.5.3 丢失更新

一个事务中的更新操作会被另一个事务的更新操作覆盖。造成逻辑上的错误，也就是程序执行的结果并非本意。

典型例子是银行扣款：两个事务先读、后UPDATE余额。会造成余额只减少一次。

解决方法：首先上X锁，然后再UPDATE，最后提交事务。在读到数据和写入数据之间，不允许别的事务进行任何操作，包括读操作。

6.6 阻塞

可以设置阻塞超时。

6.7 死锁

普遍采用超时和等待图机制进行死锁监测。在等待图中出现环则表示出现了死锁，也就是资源产生了相互等待的情况。

如果存在死锁，INNODB选择回滚UNDO量最小的事务。

发生死锁后，INNODB会马上回滚一个事务。INNODB不会回滚大多数的异常错误，死锁是个例外。

6.8 锁升级

是指将当前的锁粒度降低。

例如SQL SERVER 认为锁是一种稀缺资源，在合适的时候会将行锁升级成键锁、页锁、表锁等。

INNODB不存在锁升级。因为它不是根据每个记录来产生行锁，而是在页内的位图记录行锁，因此没有额外开销，不需要锁升级。

7.事务

事务是区别于文件系统的重要特性之一。事务会把数据库从一个一致状态转换到另一个一致状态。在数据库提交工作时，要么所有修改都已经保存了，要么所有修改都不保存。

7.1 认识事务

7.1.1 概述

事务遵循ACID特性。详见第0章概念介绍。

7.1.2 分类

事务分为以下几种：

扁平事务：最简单但是最频繁被使用的事务。所有事务都是并列的，从BEGIN开始，到COMMIT或者ROLLBACK结束。限制在于：不能提交或者回滚事务的一部分。因此出现了带保存点的事务

带有保存点的扁平事务：允许事务执行过程中回滚到同一事务中较早的一个状态。采用SAVE WORK x & ROLLBACK WORK x 语句来保存和回滚

链事务：链式执行事务，类似于将大事务分为几个小事务串联执行。既实现了类似保存点的效果，也实现了分段提交。

嵌套事务：类似树。父亲节点回滚会导致子事务全部回滚。所以子事务只有ACI特性，不具有D特性。

分布式事务：通常是在分布式环境下运行的扁平事务。例如跨行转账。要求：任一节点回滚，其它节点也必须一起回滚。

INNODB支持扁平事务、带有保存点的事务、链事务、分布式事务，不支持嵌套事务。

7.2 事务的实现

7.2.1 REDO

在事务提交之前，必须写入REDO LOG，写入后才能提交成功。REDO基本都是顺序写的，在数据库运行时无需读。记录页的更改，是物理日志

与UNDO的区别：UNDO用于MVCC和事务回滚，一般进行随机读写。是逻辑日志

与二进制日志的区别：bin由MYSQL引擎产生，记录对应的SQL语句（也可能为行的更改，详见3.2.4节）。是逻辑日志。仅在事务提交时写入。

LSN(Log Sequence Number)：代表日志序列号。可用于表示：redo写入的总量，checkpoint的位置，页的版本。可用于恢复时确定从哪儿开始（checkpoint之后）恢复。

redo是幂等的：因为涉及物理页的修改。但是bin不是幂等的，比如insert就不是。

7.2.2 undo

undo是逻辑日志，所有修改都被逻辑地取消了，但是实际的物理存储可能发生很大变化（比如插入了很多数据后回滚，表的空间并不会因此收缩）。为何？因为如果修改了页，那可能会覆盖别的事务的修改，而不是将其恢复到初始样子。

当回滚时，实际上做的就是与之前相反的工作：对于INSERT，会完成DELETE，对于DELETE，会完成INSERT，对于UDPATE，执行一个相反的UPDATE，总而言之就是把数据库逻辑地恢复到执行那条语句之前。

提交事务后，不能立刻删除UNDO，因为别的事务可能需要UNDO来读取之前的行版本信息。只是将其放入一个链表中，由purge线程判断何时条件合适了进行清除。此外，还会重用UNDO页，当UNDO页可用空间较多时，会在其后append其它事务的UNDO。

分为insert和update log。前者可以在事务执行后直接删除，因为insert只对当前事务可见。后者包括delete&update操作。

7.3 事务控制语句

有以下这些语句

START TRANSACTION | BEGIN // 开始一个事务
COMMIT // 提交事务
ROLLBACK // 回滚事务
SET TRANSACTION ... // 设置事务隔离级别

7.8 不好的事务习惯

循环中提交事务：疯狂写入redo，性能差；发生错误时，数据库停留在未知位置，比如插入1w条数据，第5k时失败了，那这算什么呢？

使用自动提交：如果出错或者后悔了，很麻烦！

使用自动回滚：程序员难以得知出错的原因（尤其针对MYSQL来说，不会给出出错原因）。

7.9 长事务

可以通过转化为小批量事务来处理。这样也可以加快回滚和恢复时间。当然，前提是，这些事务可以分成小批量来处理！最好加上共享锁，不允许执行过程中其它事务的修改。

9.性能调优

9.1 选择合适的CPU

OLAP是CPU密集型；OLTP为IO密集型。

9.2 内存的重要性

内存大小直接反映了数据库性能。可以通过缓冲池命中率来判断数据库内存是否已经达到瓶颈。

9.3 硬盘的影响

对于传统机械硬盘来说，由于寻道需要时间，所以顺序访问远高于随机访问。

对于SSD来说，随机读写性能远远高于机械硬盘，但是：闪存中的数据是不可更新的，必须通过扇区的覆盖重写，需要执行非常耗时的擦除操作。因此读的速度远远高于写，所以需要多利用其读取的性能。

9.4合理设置RAID(Redundant Array of Indepenent Disks)

将多个相对便宜的硬盘组合起来，成为一个磁盘组，使性能达到或超过一个价格昂贵、容量巨大的硬盘。

一方面，RAID可以增强读写速率（如RAID0），另一方面，还能增强数据安全性（如RAID1）。

RAID0：多个硬盘并行IO，速度最快，但是没有冗余功能，只要一个硬盘损坏，数据就不可恢复，因此数据安全性最低。

RAID1：多个硬盘互为镜像。读取速度可以很快，但是写入速度略低。且磁盘空间利用率很低，但是安全性最高。

RAID5：至少需要3块硬盘。兼顾性能和数据安全、存储成本。选用一块硬盘存储奇偶校验信息，其它几块硬盘并行存储。

RAID10/01：先1后0或者先0后1

RAID50：

9.6 不同文件系统对数据库性能的影响

几乎没影响