《MySQL技术内幕——InnoDB存储引擎》

MySQL体系结构和存储引擎

1.1 定义数据库和实例

数据库：物理操作系统文件或其他形式文件类型的集合。
实例：MYSQL数据库由一个后台线程以及一个共享内存区构成。

1.2 MySQL体系架构

连接者：不同语言的代码程序和mysql的交互（SQL交互）
1、连接池 管理、缓冲用户的连接，线程处理等需要缓存的需求
2、管理服务和工具组件 系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3、sql接口 接受用户的SQL命令，并且返回用户需要查询的结果
4、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
5、查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化
select id,name from user where age = 40;
a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤
b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤
c、将这两个查询条件联接起来生成最终查询结果
6、缓存 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
7、插入式存储引擎 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库 中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

1.3 MySQL存储引擎

show ENGINES 可以查看支持哪些引擎。

InnoDB存储引擎

2.1 InnoDB存储引擎概述

MySQL5.5版本开始的默认存储引擎。
其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读。

2.2 InnoDB存储引擎的版本

2.3 InnoDB体系架构

InnoDB的多个内存块组成了内存池，负责如下工作：
1.维护所有进程/线程需要访问的多个内部数据结构。
2.缓存磁盘上的数据，方便快速的读取，并且在对磁盘文件的数据进行修改之前在这里缓存。
3.重做日志缓存。
后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据，此外、将已经修改的数据文件刷新到磁盘文件。

2.3.1 后台线程

InnoDB存储引擎是多线程模型。
1.Master Thread
是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等。
2.IO Thread
在InnoDB存储引擎中大量使用了AIO来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些IO请求的回调（call back）处理。
3.Purge Thread
事务提交后，其所使用undolog不再需要，PurgeThread来回收已经使用并分配的undo页。purge可以在master thread 中完成，也可以独立到单独的线程中进行，在配置文件中添加innodb_purge_threads=1来启用独立的purge thread。且1.2版本开始，该参数可设置为>1,进一步加快undo页面的回收。
4.Page Cleaner Thread
1.2版本中引入，将之前版本中的脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原来masterthread的工作及对于用户查询线程的阻塞，进一步提高性能。

2.3.2 内存

1.缓冲池
缓冲池就是一块内存区域，通过内存的速度来弥补磁盘较慢对数据库性能的影响。

缓冲池中缓冲的数据页类型有：索引页、数据页、undo页、插入缓冲（insertbuffer）、自适应哈希索引（adaptive hash index）,InnoDB存储的锁信息（lock info）、数据字典信息（data dictionary）等。

2.LRU List、Free List 和Flush List。
LRU List：用来管理已经读取的页，数据库中的缓冲池是通LRU(LastestRecent Used,最近最少使用)算法来进行管理的。即最频繁使用的页放在LRU列表的前端，而最少使用的页放在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。
Free List：数据库刚启动时，LRU列表为空，所有页都存放在Free列表中。当需要从缓冲池中分页时，首选从Free列表中查找是否有可用的空闲页，若有，则将该页从free列表中删除，放入到LRU列表中。

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

3.重做缓存日志。
innoDB首先将重做日志信息放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。一般情况下，每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。由参数innodb_log_buffer_size控制，默认为8M.

下面情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。
1.master thread每一秒将重做日志缓冲刷新到重做日志文件；
2.每个事务提交时会将重做日志缓冲刷新到重做日志文件；
3.当重做日志缓冲池剩余空间小于一半时，重做日志缓冲刷新到重做日志文件；

2.4 CheckPoint技术

页的操作首先都是在缓冲池中完成的，缓冲池的页的版本要比磁盘的新。数据库需要将新版本的页从缓冲池刷新到磁盘。为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了write ahead log策略，即当事务提交时，先写重做日志，再修改页。如因为宕机而导致数据丢失，通过重做日志来完成数据的恢复。这也是事务ACID中D的要求。

checkpoint技术的目的是为了解决如下问题：

1.缩短数据库的恢复时间，当数据库发生宕机时，数据库只需对checkpoint后的重做日志进行恢复。

2.缓冲池不够用时，将脏页刷新到磁盘，当缓冲池不够用时，需强制执行checkpoint，将脏页刷回磁盘。

3.重做日志不可用时，刷新脏页。宕机时，数据库恢复操作不需要的重做日志的部分，可以被覆盖重用，此时，需要强制产生checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

2.5 Master Thread工作方式

有最高的线程优先级别。内部由多个循环组成：主循环，后台循环，刷新循环，暂停循环。master thread会根据数据库运行的状态在四个循环中进行切换。

loop(主循环):两大部分操作，每秒钟的操作和每10秒的操作。

每秒的操作：
1.日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）（为什么再大的事务，提交（commit）的时间也很短）；
2.合并插入缓冲（可能）；判断前一秒发生的IO是否小于5次，若小于，可执行。
3.至多刷新100个innoDB的缓冲池中的脏页到磁盘（可能）；判断当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）是否超过了配置文件中(innodb_max_dirty_pages_pct)的阈值,如超过，则将100个脏页写入磁盘中。
4.如果当前没有用户活动，则切换到background loop （可能）；

每10秒的操作：

1.刷新100个脏页到磁盘（可能），判断过去10秒内磁盘的IO操作是否小于200次，如果是，将100个脏页刷新到磁盘。
2.合并至多5个插入缓冲（总是）
3.将日志缓冲刷新到磁盘（总是）
4.删除无用的undo页（总是）
5.刷新100个或者10个脏页到磁盘（总是）

background loop：若当前没有用户活动或者数据库关闭时，会切换到这个循环。执行以下操作：
1.删除无用的undo页（总是）
2.合并20个插入缓冲（总是）
3.跳回到主循环（总是）
4.不断刷新100个页直到符合条件。

2.6 InnoDB的关键特性

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

2.6.1 插入缓冲

1.Insert Buffer
对于非聚集性索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断要插入的非聚集性索引是都在缓冲池中，如果存在，则直接插入，如果不存在则放入到一个Insert Buffer对象中。

Insert Buffer使用的条件：
索引是辅助索引。
索引不是唯一的。

2.Change Buffer
1.0.x版本后，insert buffer升级为change buffer，可以对insert，delete，update都进行缓冲，分别是insert buffer，delete buffer，purge buffer，依然适用非唯一的辅助索引。

2.6.2 两次写

doublewrite两次写提高数据页的可靠性。
数据库宕机时，可能正在写入某个页到表中，如果这个页没有写完成，则会丢失数据。
在刷新脏页时，先复制到doublewrite buffer，再分两次，每次1M顺序写入共享表空间的物理磁盘上，再调用fsync函数，同步磁盘。

2.6.3 自适应哈希索引

存储引擎会自动根据访问的频率和模式来自动地为某些热点数据建立哈希索引。只能用来搜索等值查询。
访问模式：
select * from table where col = ‘xxx’;

2.6.4 异步IO

异步IO可以同时发起多个IO操作，还可以进行IO merage。
1.1.x版本之前AIO通过代码来模拟实现，之后由内核级别AIO支持，称为Native AIO

2.6.5 刷新邻接页

当刷新一个脏页时，存储引擎会检测改页所在区的所有页，如果有脏页，然后一起进行刷新操作。

文件

3.1 参数文件

当MySQL启动的时候，需要加载一个配置文件，这些配置文件通常定义了某些内存结构有多大等。
数据库参数可以看成键值对。包括动态参数和静态参数。

3.2 日志文件

• 错误日志 error log
• 二进制日志 binlog
• 慢查询日志 show query log
• 查询日志

错误日志

错误日志对MySQL的启动、运行、关闭过程进行了记录。

慢查询日志

通过慢查询日志可以找出有问题的SQL 语句，对其进行优化。
可以在MySQL启动的时候设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。可以通过参数long_query_time来设置，默认值为10.

查询日志

查询日志记录了所有对数据库请求的信息。

二进制文件

二进制文件记录了所有对MySQL数据库执行更改的所有操作（不包括 select和show）。
二进制日志主要有以下作用：

• 恢复 某些数据的恢复需要二进制日志，例如在一个数据库全备文件恢复之后，用户可以通过二进制日志进行point-in-time的恢复。
• 复制 通过复制和执行二进制日志使得一台远程的MySQL数据库与一台MySQL数据库进行实时同步。
• 审计 通过二进制日志中的信息来进行审计，判断是否对数据库进行注入的攻击。

3.3 套接字文件

UNIX下可以通过套接字方式连接，因此需要一个套接字文件。

3.4 pid文件

当MYSQL实例启动时，会将自己的进程写入一个文件——该文件以.pid结尾。

3.5 表结构定义文件

.firm文件用来存储表结构定义文件和视图。

3.6 InnoDB存储引擎文件

1、表空间文件(设置成多个ibdata文件，通过参数每个表存一个文件名称为表名.ibd)。
2、重做日志文件(ib_logfile0和ib_logfile1，先写入重做日志缓存，再写入日志文件，重做日志缓冲往磁盘写入的时候，是按照512字节，也就是一个扇区的大小进行写入，因为扇区是写入的最小单位，因此在重做日志的写入过程中不需要有doublewrite。

重做日志与二进制日志区别：

• 二进制日志会记录下所有与MySql数据库有关的日志记录，Binlog是在数据库的上层提供的，包括InnoDB,MyISAM等等，但是重做日志文件只记录InnoDB本身的事务日志。
• 记录的内容不同，因为无论我们binlog_format设置为什么，其实记录的都是关于一个事务的集体操作的内容，即是一种逻辑日志，但是重做日志是关于物理存储的每个页的变更情况，比如在哪个页上偏移量为800的地方做什么修改，与binlog相比更像是一种物理日志。
• 写入的时间不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，无论这个事务有多大，而在事务进行中，却不断的产生重做日志被写入到重做日志文件中。

表

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

4.1 索引组织表

在 InnoDB存储引擎中，表就是关于特定实体的数据集合。在InnoDB存储引擎中，表都是根据主键组织存放的，这种存储方式的表称为索引组织表。

在 InnoDB存储引擎表中，每张表都是有主键的，如果没有主键则会按照如下方式选择或者创建主键：

• 首先判断表是否有唯一非空索引，如果有，则该列为主键。
• 如果不符合，则创建一个6字节的指针。
  如果有多个非空唯一索引时，则按照创建顺序现在第一个列作为主键。

4.2 InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构来看，所有数据都被逻辑地存放在一个空间中，称为表空间。
表空间又由段(segment)、区(extent)、页(page)组成。

4.2.1 表空间

表空间是InnoDB存储引擎结构的最高层，所有的数据都存在表空间。默认情况下，InnoDB有一个共享表空间ibdata1，所有的数据都存在这个表空间中，如果启用了参数innodb_file_per_table，则每张表的数据会单独放在一个表空间中。

每张表的表空间中存放的是数据、索引和插入缓存，如回滚信息、插入缓冲索引页、系统事务信息，两次写缓冲还是存放在共享表空间。
所以即使启用了innodb_file_per_table，共享表空间依然会不断增大。

4.2.2 段

表空间是由各个段组成的，常见的有数据段、索引段、回滚段等。
前面介绍过InnoDB引擎表是索引组织的，因此数据即索引，索引即数据。数据段中为B+树的叶子节点，索引段中为B+树的非索引节点。

4.2.3 区

区是由连续的页组成的空间。在任何情况下，每个区的大小都为1MB。在默认情况下，InnoDB存储引擎的页大小为16kb，即一个区有64个页。

4.2.4 页

页是InnoDB磁盘管理的最小单位。InnoDB页的大小为16KB。从InnoDB1.2.x版本开始，可以通过参数innodb_page_size进行设置。
常见页类型有：

数据页（B-tree Node）
Undo页 （Undo Log Page）
系统页 （System Page）
事务数据页（Transaction system Page）
插入缓冲位图页 （Insert Buffer Bitmap）
插入缓冲空闲列表页 （Insert Buffer Free List）
未压缩的二进制大对象页（Uncompressed BLOB Page）
压缩的二进制大对象页（Compressed BLOB Page）

4.2.5 行

InnoDB是面向行的（row-oriented，相对比是面向列的数据库），也就是说数据的存放按行进行存放。每个页最多允许存放16KB / 2 ~ 200行的记录。

4.3 InnoDB行记录格式

5.1后有Compact（默认）和Redundant两种格式。

Compact行记录格式

每行除了用户定义的列，还有两个隐藏列，事务ID列（6字节）和回滚指针列（7字节），若没有定义主键，每行还会有一个6字节的RowID列。

Redundant行记录格式

行溢出数据
将一条记录中的某些数据存储在真正的数据页面之外，即为溢出数据。
不单单BLOB、LOB这类大对象列类型，varchar也可以存放为行溢出数据。varchar理论可以放65535个字节，减去别的开销，实际为65532个字节。
不超过一页两行是存在数据页中，否则存在BLOB页。

Compressed 与 Dynamic行记录格式
对于BLOB类型的数据采用了完全行溢出的方式存储数据。
Compressed 行记录格式的另一个功能就是存储在其中的行数据会以zlib的算法进行压缩。

通常理解，varchar存储变长长度的字符类型，char存储定长长度的字符类型。然而在多字节字符集的情况下，char和varchar的行存储基本是没有区别的。

4.4 InnoDB数据页结构

B+树索引本身并不能找到具体的一条记录，B+树索引找到的只是该记录所在的页。
InnoDB数据页由以下七个部分组成，如图所示：

File Header（文件头）。
Page Header（页头）。
Infimun+Supremum Records。
User Records（用户记录，即行记录）。
Free Space（空闲空间）。
Page Directory（页目录）。
File Trailer（文件结尾信息）。

4.6 约束

4.6.1 数据完整性

关系型数据库能保证自身存储数据的完整性，不需要应用程序的控制。
一般来说，数据完整性有以下三种形式：

• 实体完整性保证表中有一个主键。(可以使用Primary Key或Unique Key或编写触发器)
• 域完整性保证数据每列的值满足特定的条件
  1、选择合适的数据类型保证一个数据值满足特定类型
  2、外键约束
  3、编写触发器
  4、还可以使用DEFAULT约束作为强制域完整性的一个方面。
• 参照完整性保证两张表之间的关系。

InnoDB中的几种约束
Primary Key
Unique Key
Foreign Key
Default
NOT NULL

4.6.2 约束的创建和查找

表建立时就进行定义
利用ALERT TABLE命令来创建约束

4.6.3 约束和索引的区别

约束是一个逻辑概念，用来保证数据完整性。
索引是一个数据结构，既有逻辑上的概念，又有物理表现形式。

4.6.4 对错误数据的约束

通过sql_mode来设置对输入值的约束。

4.6.5 ENUM和SET约束

sex EMUM(‘male’,‘female’) 用来约束性别只能是male或female。统一需要设置sql_mode。

4.6.6 触发器与约束

触发器的作用是在执行INSERT、DELETE、UPDATE命令之前或之后自动调用sql命令或者存储过程。

4.6.7 外键约束

CREATE TABLE parent(
id INT NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB

CREATE TABLE child(
id INT NOT NULL,
parent_id INT,
FOREIGN KEY(parent_id) REFERENCES parent(id)
)ENGINE=INNODB

4.7 视图

在MySQL数据库中，视图是一个命名的虚表，它由一个sql查询来定义，可以当做表使用。视图中的数据没有实际存储。

4.8 分区表

分区概述

分区不是在存储引擎层完成的，因此不是只有InnoDB存储引擎支持分区，还有MyISAM、NDB等都支持。
分区的过程是将一个表或索引分解为更小、更可管理的部分。

一个表或索引可能由数十个物理分区组成。
每个分区都是独立处理的对象，可以独自处理。

分区类型

1、RANGE分区(最常用)
下面创建了一个id列的区间分区表。当id小于10的时候，数据插入p0分区，当id大于10小于20的时候，数据插入p1分区。

2、LIST分区
LIST和Range相似，只不过分区列的值是离散的，而不是连续的。

3、HASH分区
HASH分区的目的是将数据均匀的分布到预先定义的各个分区中。
在RANGE和LIST中，必须明确指定一个给定的列值或者列值集合应该保存在哪个分区中，而在HASH分区中，MySQL自动完成这个工作，用户所要做的只是基于将要进行哈希分区的列指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
4、KEY分区(使用MySQL提供的函数分区)
KEY分区和HASH分区类似，不同之处在于HASH分区使用用户定义的函数进行分区。KEY分区使用MySQL数据库提供的函数进行分区

5、COLUMNS分区
上面讲的四种分区，数据必须是整形，如果不是，进行转化。
MySQL5.5支持COLUMNS分区可以直接使用非整形的数据进行分区，分区根据类型直接比较而得。

索引与算法

5.1 INNODB存储引擎索引概述

InnoDB存储引擎常见索引：

• B+树索引
  B+树索引并不能找到给定键值的具体的行，而是找到数据行所在的页，然后把页读到内存，查找需要的数据。
• 全文索引
• 哈希索引
  InnoDB存储引擎的哈希索引是自适应的，会根据表的使用情况自动生成哈希索引。

5.2 数据结构与算法

5.2.1二分查找法

数据必须有序，根据前面的学习，每页Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过Page Directory进行二分查找得到的。

5.2.2 二叉树和平衡二叉树

平衡二叉树首先是一棵二叉查找树，虽然平衡二叉树的查找效率很高，但是维护可能需要一次旋转或者多次。

5.3 B+树

B+树是由B树和索引顺序访问方法演化而来。
B+树是为磁盘或其他存储辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按照键值的大小顺序存放在同一层的叶子节点上的，由各个叶子节点指针进行连接。

1、B+树的插入操作(可能需要拆页)
2、B+树的删除操作(填充因子最小50%)

5.4 B+树索引

B+树索引就是B+树在数据库中的实现。
B+树的高度一般都在2-4层，这也就是说查找某一键值的记录时，最多需要2到4次IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做100次IO，2-4次的IO的意思是查询时间只需0.02-0.04秒。

B+树索引可以分类：聚集索引和辅助索引。不管是聚集索引还是辅助索引，其内部都是B+树实现，即高度平衡，叶子节点存放着数据。

聚集索引与辅助索引的不同是，叶子节点是否存放一整行数据。

5.4.1 聚集索引

逻辑上连续，物理上不连续，每个表只有一个

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点存放的是一整行的数据

由于定义了数据的逻辑顺序，聚簇索引能够特别快的访问针对范围值的查询。

聚集索引的另一个好处是对于主键的排序查找和范围查找速度非常快。

5.4.2 辅助索引

辅助索引叶子节点并不包含行记录的全部数据，辅助索引存储的页签存储的是聚集索引键值。
辅助索引的书签就是相应行数据的聚集索引键。

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

5.4.3 B+树索引的分裂

InnoDB存储引擎的Page Header中有几个部分来保存插入的顺序信息：
PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION
通过这些信息，可以决定是向左还是向右分裂，同时决定将分裂点记录为哪一个。

5.4.3 B+树索引的管理

• 索引的管理
  索引的创建和删除有两种方法。一种是ALERT语句，一种是CREATE/DROP INDEX。
  用户想要查看索引，可以使用SHOW INDEX。
• Fast Index Creation

5.5 Cardinality值

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

• 什么时候添加索引？(字段有高选择性)
  对于有高选择性的字段添加索引即字段取值范围很广，几乎没重复。(sex的话只有M和F可以选，它是第选择性的，添加索引就没没有必要)
• 怎么查看是否有高选择性(通过show index结果中的列Cardinality来观察)
  Cardinality是一个预估值，不是准确值。在实际应用中，Cardinality/表行应该尽可能接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

5.6 B+树索引的使用

5.6.1 不同应用中B+树索引的使用

数据库存在两种不同类型的应用：

• OLTP 查询操作只从数据库取得一小部分数据。一般在10条记录以下。 如根据订单号得到订单信息等。
• OLAP 需要访问表中的大量数据，根据这些数据产生查询结果。 目的是为决策者提供支持，如这个月的消费情况、环比增长等。

5.6.2 联合索引

联合索引指对表上多列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅仅在于有多个索引列。
创建例子：

CREATE TABLE t(
	a INT NOT NULL,
	b INT,
	FOREIGN KEY(a)
	KEY index1(a,b)
)ENGINE=INNODB

联合索引内部结构：

• 联合索引是键值都是有序的。

  就上面的例子来说，数据按照(a,b)的顺序进行存放，即(1,1)、(1,2)、(2,1)、(2,4)、(3,1)、(3,2)。
  select * from t where a = xxx and b = xxxx;
  select * from t where a = xxx; (a,b)、(a)都是有序的，都可以使用到联合索引。
  select * from t where b = xxx;因为b不是排序的所以无法使用到索引。

• 联合索引的第二个好处是已经对第二个键值进行了排序处理。下列语句是可以用到索引的： select * from t where a = xxx and order by b;

5.6.3 覆盖索引

nnoDB支持覆盖索引，即从辅助索引就可以直接得到查询的记录，而不要查询聚集索引中的记录。使用覆盖索引的好处是辅助索引不包含整行记录的所有信息，故其远小于聚集索引，因此减少大量IO。

CREATE TABLE buy_log(
	user_id INT NOT NULL,
	buy_date TIMESTAMP
)
ALTER TABLE buy_log ADD KEY(user_id,buy_date)

在通常条件下，诸如(a,b)的联合索引，一般是不可用选择b列中的查询条件的，但是对于统计操作，并且是覆盖索引，可以使用：

SELECT COUNT(*) FROM buy_log WHERE buy_date > "2011-01-01" AND buy_date < "2012-01-01"

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

某些情况下，EXPLIAIN查看执行计划时，发现优化器没有选择索引去查找数据，而是通过扫描聚集索引。这种情况下多发生于范围扫描、JOIN连接操作。

select * from orderdetails where orderid>10000 and orderid<102000;

• 范围扫描
  上面例子没走OrderID索引而走聚集索引理由
  因为用户要查找的是整行数据，而OrderID索引不能覆盖到我们要查找的信息。因此在对OrderID索引查询到指定数据后，还需进行一次书签访问来查找整行数据，虽然OrderID数据是顺序的，但是查找的是一个范围，找到的主键可能不是连续的，因此就要变成磁盘上的离散读操作，如果要查找的数据很少，则优化器会选择辅助索引，但是当访问的数据占整个表的很大一部分的时候(一般20%左右)，优化器会通过聚集索引查找数据。
• JOIN链接操作
  小表驱动大表，也就是数据量小的表作为主表进行连接
  连表条件增加索引
  这样的话其实连表是最快的，主要是因为连表条件会走索引。
  如果连表条件没有索引，那么抱歉，速度真的很慢，他会对两个表全都全表扫描

5.6.5 索引提示

用户指定某个索引可以使用FORCE INDEX。

5.6.6 Multi-Range Read 优化

Multi-Range Read 优化的目的是为了减少磁盘的随机访问，并且将随机访问转换为较为顺序的访问。

5.6.7 Index Condition Pushdown(ICP)优化

MySQL5.6开始支持，以前进行索引查询的时候，首先根据索引来查找数据，然后在根据where过滤(where可以过滤的条件是要该索引可以覆盖到的范围)，在支持ICP之后，MySQL在取出索引的同时，判断是否进行where的优化，也就是将where的部分过滤操作放在存储引擎层

ICP优化支持range、ref、eq_ref、ref_or_null类型的查询。当优化器使用ICP优化的时候，可在执行计划的列Extra看到Using index condition。

5.7 哈希算法

哈希算法是一种常见的算法，时间复杂度为O(1);
1.哈希表
数据库中哈希函数通常使用除法散列的方法，解决哈希冲突使用链表的方式。

在哈希函数的除法散列法中，通过取k除以m的余数，将关键字k映射到m个槽的某一个中去，即哈希函数为：
h(k) = k mod m
2.InnoDB存储引擎中的哈希算法
3.自适应哈希索引
数据库自身创建并使用的，DBA本身并不能对其进行干预。
哈希索引只能用来搜索等值的查询,对于其他查找类型如范围查找是不能使用哈希索引的：
select * from table where index_col = ‘xxx’;
可以通过参数innodb_adaptive_hash_index来禁用或者启动此特性，默认为开启。

5.8 全文索引

5.8.1 概述

可以通过索引字段的前缀进行查找如：

select * from blog where content like ‘xxx%’;

如果需要在博客中查询内容包含单词xxx的文章，可能B+索引不能满足要求，因为xxx可能不满足最左前缀。

select * from blog where content like ‘%xxx%’;

全文检索是将存储与数据库中的整本书或者整篇文章中的任意内容信息查找出来的技术。可以根据需要获得本文中有关章、节、段、句等信息。

InnoDB存储引擎之前并不支持全文检索技术，InnoDB1.2版本开始，InnoDB支持全文检索。

5.8.2 倒排索引

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

• inverted file index，其表现形式为{单词，单词所在文档的ID}
• full inverted index，表现形式为{单词，(单词所在文档ID，单词在文档中具体位置)}

5.8.3 InnoDB全文检索

InnoDB从1.2.x版本开始支持全文检索的技术，采用full inverted index方式，将(DocumentId,Position)视为一个“ilist”，因此在全文检索的表中，有两个列，一个是word字段，一个是ilist字段，并且在word字段上设置了索引。

锁

6.1 什么是锁

锁用于管理对共享资源的并发访问。InnoDB支持行锁。
MyISAM锁表锁，并发情况下，读没有问题，但是在并发插入的时候性能就差一点。

6.2 lock 与 latch

latch一般称为闩锁，其要求持续时间短，模式主要为互斥量和读写锁。其目的用来保护并发线程操作临界资源的准确性，并且通常没有死锁检测机制。
lock的对象是事务，用来锁定的是数据库中的对象，入表、页、行。有死锁检测机制。

通过 show engine innodb mutex 查看mutex锁

6.3 INNODB引擎中的锁

6.3.1 锁的类型

INNODB存储引擎实现了如下两种标准的行级锁

• 共享锁(S LOCK)：允许事务读取一行的数据。
• 排它锁(X LOCK)：允许事务删除或更新一行数据。

InnoDB支持多粒度加锁，这种锁定允许事务在行和表级别上的锁同时存在，为了支持不同粒度上进行加锁，InnoDB支持意向锁。
其支持两种意向锁：
意向共享锁(IS Lock)，事务想要获得一张表中某几行的共享锁
意向排他锁(IX Lock)，事务想要获得一张表某几行的排他锁

共享锁之间锁兼容，其他之间不兼容。

6.3.2 一致性非锁定读

一致性非锁定读是指数据库通过多版本控制的方式来读取当前执行时间数据库中的行数据。如果读取的行正在执行DELETE或UPDATE操作，这时读操作不会等待行上的锁释放，而是去读取行的快照数据。
之所以成为非锁定读，因为不需要等待访问行上X锁释放。快照数据是指该行之前版本的数据，是通过undo段来完成。

一行记录的快照可能不只有一个快照数据，一般称这种技术为多版本并发技术，MVCC多版本并发控制。

READ COMMITED 隔离级别下读取到的是被锁定行的最新一份快照数据。
REPATETABLE READ 隔离级别下总是读取事务开始时的数据版本。

READ COMMITED 隔离级别下读取到的总是最新的数据，所以是空。
REPATETABLE READ 隔离级别下总是读取事务开始时的数据版本，读取结果是 id=1的数据。

所谓乐观锁

6.3.3 一致性锁定读

在REPATETABLE READ模型下，InnoDB的SELECT操作使用一致性非锁定读。在某些情况下，需要对数据库读取操作加锁以保证数据逻辑一致性。
InnoDB提供两种一致性锁定读操作

SELECT ... FOR UPDATE （对锁定的行加X锁）

SELECT … FOR UPDATE对读取的行加一个X锁，其他事物不能对已锁定的行加任何锁。

SELECT ... LOCK IN SHARE MODE （对锁定的行加S锁）

SELECT … LOCK IN SHARE MODE 对读取的记录加一个S锁，其他事物可以向被锁定的行加S锁，但是加X锁会被阻塞。

所谓悲观锁

6.3.4 自增长与锁

每个自增长的值的表都一个自增长计数器，当我们对含有自增长的计时器的表进行插入操作时，这个计时器会被初始化
这其实是一个自增长锁，为了提高插入性能，锁在一个sql语句执行完就释放，而不是整个事务结束。
为了提高插入的性能 引入了轻量级互斥量的自增长机制。提了一个参数 innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值是1

6.3.5 外键和锁

前面介绍了外键，外键主要用于引用完整性的约束检查，在InnoDB存储引擎中，对于一个外键列，如果没有显式对这个列加索引，InnoDB存储引擎会自动对其加1个索引。Oracle数据库用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

6.4 锁的算法

6.4.1 行锁有三种算法

• Record Lock 单行记录上的锁
  Record Lock 总是锁住索引记录，如果没有设置任何索引，innodb会设置隐式的隐式主键来锁定记录。
• Gap Lock 间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock Record Lock+Gap Lock ，锁定一个范围，并锁定记录本身
  当查询的索引含有唯一属性时，会降级为 Record Lock，即仅锁住索引本身。

6.4.2 解决Phantom Problem(幻像问题)

Phantom Problem指在同一事务下连续执行两次相同的sql语句，可能导致不同的结果，第二次SQL语句可能返回之前不存在的行。
在READ REPEATABLE下， Next-Key Lock机制可以避免Phantom Problem问题。

6.5 锁问题

锁会带来三种问题

6.5.1 脏读

脏读就是再不同的事务下，一个事务可以读到另一个事务未提交的数据，违反了数据库的隔离性。

脏读发生的条件是READ UNCOMMITED

6.5.2 不可重复读(幻读)

一个事务内两次读取到的数据不一致的情况为不可重复读。违反了数据库一致性的要求。
不可重复读与脏读的区别是：脏读读到的是未提交的数据，不可重复读读到的是已经提交的数据。
不可重复读示例：

在Next-Lock算法下，对索引的扫描，不仅锁住扫描到的索引，还会锁住这些索引覆盖的范围，因此在这个锁住的范围内插入是不允许的。避免了另外的事务在范围内插入导致的不可重复读问题。
因此InnoDB默认事务隔离级别是READ REPEATABLE。

6.5.3 丢失更新

丢失更新是另一个锁导致的问题，简单来说就是其中一个事务的操作会被另一个事务所覆盖，从而导致不一致的问题。

 例如：
 1.事务t1将行记录r更改为v1，但是事务t1未提交。
 2.与此同时，t2将行记录r改完t2，t2未提交。
 3.事务t1提交。
 4.事务t2提交。

在当前事务的隔离级别中，DML操作会被加上行锁或其他对象级别的锁，在步骤2中t2会被阻塞，直到t1提交。
在生产应用中还会产生逻辑意义上的丢失问题。

 1.事务t1查询一行数据并防止本地内存中，展示给用户user1。
 2.事务t2也查询到此行数据并展示给用户user2。
 3.用户user1修改这行记录，并更新数据库提交。
 4.用户user2修改这行记录，并更新数据库提交。

在这个过程中，user1的操作丢失了。要避免这种情况发生，需要让事务在这种情况下的操作串行化，而不是并行操作。

如果用户查询之后，进行一些额外的操作，再进行update就可能会出现丢失更新的情况。

将操作变成串行化，对用户读取的记录加上排他X锁。

6.6 阻塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁来是否它所占的资源，这就是阻塞。

6.7 死锁

6.7.1 死锁的概念

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

6.7.3 死锁的实例

A和B资源互相等待的时候会产生死锁，简称AB-BA锁。

A对4持有了X锁，B在请求中获得了4的S锁，在A进行插入时会导致死锁发生。

6.8 锁升级

锁升级是指将当前锁的粒度降低。
InnoDB引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，根据事务访问的页来进行锁管理，采用的是位图的方式。

事务

事务：事务会把数据库从一种一致状态转化为另外一种一致状态，在数据库提交工作时，可以确保要么所有修改已经保存了，要么所有修改都不保存。

7.1 认识事务

7.1.1 概述

事务可以由一条简单的SQL语句组成，也可以由一组复杂的SQL语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作要，饿都成功，要么都不做。

• A(Atomicity)
  要保持原子性，要么都做，要么什么都不做。
• C(Consistency)
  一致性是指从一种状态转移到另一种状态时一致的状态。
• I(Isolation)
  隔离性要求事务提交前对其他事务都不可以见。
• D(Durability)
  事务一旦提交，其结果就是永久性的。

7.1.2 分类

• 扁平事务
  扁平事务(最简单、使用最频繁)
  在扁平事务中，所有操作都处于同一层次。
  扁平事务由begin work开始，由commit work或者rollback work结束，其间的操作是原子的。要么都执行，要么都回滚。
• 带有保存点的 扁平事务
  除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为，某些事务可能在执行过程中出现的错误并不会导致所有的操作都无效。
• 链事务
  带有保存点的扁平事务，当发生系统崩溃的时候，所有保存点都将消息，因为其保存点是易失的。这就意味着当进行恢复的时候，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。
  链事务的思想是：在提交事务的时候，释放不需要的数据对象，将必要的处理上下文隐式传给下一个要开始的事务。注意：提交事务操作和开始下一个事务操作将合并为一个原子操作。
• 嵌套事务
  套事务是一个层次结构框架，由一个顶层事务控制各个层次的事务。顶层事务之下嵌套的事务被称为子事务。
• 分布式事务
  通常是一个在分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

7.2 事务的实现

事务的隔离性由锁来实现。
原子性、一致性、持久性通过数据库的redo log、undo log来完成，redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。
redo log是物理日志，记录的是页的物理修改操作，undo log是逻辑日志，记录的是每行修改的记录。

7.2.1 redo

重做日志用来实现事务的持久性，即D。其由两部分组成：一是内存中的重做日志缓存，其是易失的；二是重做日志文件，其是持久的。
redo log用来保证事务的持久性，undo log用来帮助事务回滚以及MVCC的功能。

与binlog的区别

• 重做日志是在InnoDB引擎产生的，二进制日志是在mysql上层产生，任何引擎都会产生二进制日志。
• 其次二进制的日志进记录的是逻辑日志，记录的是对应的sql；重做日志是物理格式的日志，其记录的是对于每一个页的修改。
• 二进制日志只在事务提交完成后进行一次写入。重做日志是在事务进行中不断写入。
  重做日志是幂等的，而二进制日志不一定是幂等的，比如有INSERT操作。

7.2.2 undo

重做日志记录了日志的行为，可以很好地通过其对页进行“重做”操作。
因此在对数据库进行修改的时候，InnoDB存储引擎不但会产生redo，还会产生一定量的undo。如果回滚，利用undo信息即那个数据回滚到修改之前的样子。
redo存放在重做日志文件中，与redo不同，undo存放在数据库内部的一个特殊段中，这个段叫做undo段，undo段在共享空间(ibdata)中。
undo是逻辑日志，实际上做的是与先前相反的动作。对应每个INSETR，执行DELETE操作。
除了回滚操作，undo的另一个作用是MVCC。

7.2.3 purge

delete和update操作可能并不直接删除原有的数据。
比如下面的SQL：

delete from t where a=1；

表t上列a有聚集索引，对于上面的delete操作，通过前面关于undo log的介绍已经知道仅仅是将主键列等于1的记录delete flag设置为1，记录并没有被删除，即记录还是存在于B+树中。

purge用于最终完成MVCC的删除操作，如果该行没有其他事务引用，就可以进行真正的delete操作。

purge用于最终完成delete和update操作，这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交的时候立即进行处理，这时其他事务可能正在使用这行，故InnoDB存储引擎需要保留记录之前的版本。如果该行没有其他事务引用，就可以进行真正的delete操作。

7.2.4 group commit

如果事务为非只读事务，每次事务提交的时候需要进行一次fsync操作，以此保证重做日志都已经写入了磁盘。当数据库发生宕机的时候，可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能，然而磁盘的fsync性能是有限的，为了提高fsync的效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件，对于InnoDB存储引擎来说，事务提交的时候会进行两个阶段的操作：

修改内存中事务对应的信息，并且将日志写入重做日志缓冲
调用fsync将确保日志都从重做日志缓冲写入磁盘

7.3 事务控制语句

显式开启事务命令：

begin
start transaction
set autocommit=0禁用当前会话的自动提交

一些事务控制语句

commit也可以写成commit work，两者几乎是等价的。
rollback或者rollback work，两者几乎是等价的。
savepoint identifier：允许事务中创建一个保存点，一个事务可以有多个保存点。
release savepoint identifier：删除事务的保存点。当没有一个保存点执行这语句的时候，会抛出一次
rollback back to [savepoint] identifier：和savepoint一起使用，可以把事务回滚到之前的保存点。
set transaction：设置隔离级别

7.4 隐式提交的SQL语句

以下的SQL会产生隐式的提交操作，即完成这些语句后，会有一个隐式的COMMIT操作：

DDL语句
用来隐式修改MySQL架构的操作：
管理语句

7.5 对于事务操作的统计

因为InnoDB支持事务，因此需要在考虑每秒请求数的同时，关注每秒事务处理的能力(Transaction Per Second,TPS)。

计算TPS的方法是(com_commit + com_rollback)/time。但是利用这些方法进行计算的前提是：所有的事务必须是显式进行提交的，如果存在隐式提交和回滚，不会计算到com_commit和com_rollback变量中。

7.6 事务的隔离级别

InnoDB默认支持的存储引擎隔离级别是REPEATABLE READ。
InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的算法，因此避免幻读的产生。

• 读未提交
• 读已提交
• 可重复读
• 序列化
  在SERIALIABLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即为每个读取操作加上一个共享锁。因此在这个事务隔离级别下，读占用了锁。对一致性的非锁定读不再予以支持。

7.7 分布式事务

7.7.1 mysql分布式事务

分布式事务指允许多个独立的事务资源参与到一个全局的事务中。全局事务要求所有参与的事务要么全部提交，要么全部失败。

XA事务允许不同数据库之间的分布式事务。
XA事务由一个或多个资源管理器、一个事务管理器、以及一个应用程序组成。
分布式事务使用两阶段提交方式。
第一阶段，所有参与全局事务的节点都开始准备，告诉事务管理器准备好提交了。
第二阶段，事务管理器告诉资源管理器执行ROLLBACk还是COMMIT。如果任何一个节点显示不能提交，那么所有节被告知需要回滚。
MYSQL XA事务语法：
XA START ‘a’;
INSERT INTO z SELECT 11;
XA END ‘a’;
XA PREPARE ‘a’;
XA RECOVER;
XA COMMIT;

InnoDB存储引擎提供了对XA事务的支持，并通过XA事务支持分布式事务的实现。
分布式事务指的是运行多个独立的事务资源参与到一个全局的事务中。事务资源通常是关系型数据库系统，但是也可以是其他类型的资源。

全局事务要求在其他的所有参与的事务要么都提交，要么都回滚。

在使用分布式事务时，InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE。

7.7.2 内部分布式事务

最为常见的XA事务存在与binlog和InnoDB存储引擎之间，由于复制的需要，很多数据库都开启了binlog功能。在提交的时候，先写二进制日志，再写InnoDB的重做日志。这两个操作必须是原子的，不然可能存在主从不一致的情况。

最常见的XA存在于binlog和InnoDB之间。如果执行完1,2，在执行3时发生了宕机，就会造成主从不同步。

7.8 不好的事务习惯

7.8.1 在循环中提交

每一次提交都要写重做日志，循环提交效率会很慢

7.8.2 使用自动提交

mysql模型使用自动提交.开发人员应该意思到自动提交的问题。

7.8.3 使用自动回滚

对应开发人员，重要的不仅要知道发生了错误，还要知道发生了什么错误，自动回滚存在这样的问题。

7.9 使用长事务

长事务就是执行时间较长的事务。长事务的回滚代码不可接受，可以拆分为小批量事务来完成。

备份与恢复

8.1 备份与恢复概述

按照不同的备份方法分为：

• 热备
• 冷备
• 温备
  按照备份后的文件内容，又分为：
• 逻辑备份
• 裸文件备份

裸文件备份有分为：

• 完全备份
• 增量备份
• 日志备份

8.2 冷备

只需要定期备份备份mysql的文件。

8.3 逻辑备份

8.3.1 mysqldump

8.3.2 select into outfile

8.7 复制

8.7.1 复制的工作原理

复制是mysql提供的一种高可用高性能解决方案。可用分为三个步骤
1.主服务器把数据更改记录到二进制日志中
2…从服务器把主服务器的二进制日志复制到自己的中继日志中。
3.从服务器重做中继日志，把更改应用到自己的服务器上，以达到数据的最终一致性。