MySQL技术内幕 InnoDB存储引擎（学习笔记总结）

土豆与红薯

已于 2024-05-15 12:30:27 修改

阅读量1.1k

点赞数 14

文章标签： java mysql 后端数据库 database

于 2024-05-15 10:08:17 首次发布

本文链接：https://blog.csdn.net/yxtudou/article/details/138896226

版权

MySQL技术内幕 InnoDB存储引擎

1 体系结构和存储引擎

1.1 数据库和实例

数据库：**物理操作系统文件或其他形式文件类型的集合。**可以是frm,MYD,MYI,ibd结尾的文件。使用NDB引擎时，数据库文件可能不是OS上的文件，而是存放内存中的文件。

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

可以一个数据库对应一个实例，集群一个数据库对应多个实例。

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

启动MySQL实例：

观察MySQL数据库启动后的情况：

查看MySQL数据库启动时会在哪些位置查找配置文件：

启动实例时，MySQL数据库会去读取配置文件，可以没有配置文件，MySQL会按照编译时的默认参数设置启动实例。如果几个配置文件都有同一个参数，将以读取到的最后一个配置文件参数为准。

1.2 体系结构

MySQL由以下几部分组成：

连接池组件
管理服务和工具组件
SQL接口组件
查询分析器
优化器
缓存
存储引擎
物理文件

特别注意，存储引擎基于表而不是数据库

1.3 存储引擎

InnoDB

MySQL5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。

支持事务，设计目标主要面向在线事务处理（OLTP）应用
**行锁设计、支持外键。**支持非锁定读，默认读取操作不会产生锁
将数据存储在一个逻辑的表空间，可以将每个表单独存放一个独立IDB文件中。支持用裸设备建立表空间。
使用MVCC实现高并发。默认可重复读级别。使用next-key locking来避免幻读现象。
提供插入缓冲、二次写、自适应哈希索引、预读等功能

MyISAM

主要面向一些OLAP数据库应用。由MYD用来存放数据文件，MYI存放索引文件

不支持事务。
支持表锁、全文索引。
与大多数数据库都不同，缓冲池只缓存索引文件，而不缓冲数据文件。

NDB

一个集群存储引擎，其结构是share nothing的集群架构，能提供高可用性。

**特点：**数据全部存放在内存中，主键查找的速度极快，可以添加NDB数据存储节点线性提高数据库性能。

瓶颈：NDB连接操作在MySQL数据库层完成，而不是存储引擎层完成。故复杂的连接操作需要巨大的网络开销，查询速度很慢。

Memory存储引擎

**将表中数据全部存放在内存中。**若数据库重启或崩溃，表中数据都会消失。适用于存放临时表或维度表
使用哈希索引。
只支持表锁，并发性能较差，不支持TEXT和BLOB列。
存储varchar时按照char方式进行，会浪费内存
当Memory作为临时表存放中间结果集，若该集大于Memory存储引擎容量，或包含TEXT或BLOB列字段。MySQL会转到MyISAM存储引擎表而存放磁盘中。

Federated

并不存储数据，只是指向一台远程MySQL数据库服务器上的表。

Maria

设计目标是用于取代原有的MyISAM存储引擎。

特点：支持缓冲数据和索引文件，应用行锁设计，提供MVCC功能，支持事务和非事务安全选项，以及更好的BLOB字符类型处理性能。

1.4 存储引擎对比

可以使用SHOW ENGINES语句查看当前使用MySQL数据库锁支持的存储引擎。

实例：对于同一个示例数据库，MyISAM存储引擎表大小为40.7MB,InnoDB为113.6MB,Archive为20.3MB。揭示出了各存储引擎在表的大小方面的不同。

1.5 连接MySQL

连接MySQL操作是一个连接进程和MySQL数据库实例进行通信。本质上是进程通信。

常用的进程通信方式有**：管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。**

TCP/IP

客户端为服务器，MySQL实例作为服务端，通过TCP/IP网络进行连接。

示例：用户可以在Windows服务器下请求一台Linux服务器下的MySQL实例。

客户端是Windows，向192.168.0.101的host发起TCP/IP连接，连接后即可进行操作。

在连接前，MySQL数据库会先检查一张权限视图，用于判断发起请求的客户端IP是否允许连接到MySQL实例。

命名管道和共享内存

命名管道：某些Windows平台上，两个需要进程通信的进程在同一台服务器上可以使用

共享内存：通过在配置文件中加入–shared-memory实现，若想使用共享内存，连接时，MySQL客户端必须使用–protocol=memory选项。

UNIX域套接字

Linux和UNIX环境下，还可以使用UNIX域套接字。只能在MySQL客户端和数据库实例在一台服务器的情况下使用。

可以在配置文件中指定套接字文件的路径。如：

–socket=/temp/mysql.sock

数据库实例启动后，可以通过以下命令进行UNIX域套接字文件查找：

使用该方式进行连接：

2 InnoDB存储引擎

2.1 概述

InnoDB是事务安全的存储引擎。是OLTP应用中核心表首选存储引擎。

特点：**行锁设计、MVCC、外键、一致性非锁定读。**被设计用来最有效使用内存和CPU。

事实证明InnoDB是一个高性能、高可用、高可扩展性的存储引擎。

2.2 版本

2.3 体系架构

InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池。

内存池负责如下工作：

维护所有进程/线程需要访问的多个内部数据结构
缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在这里缓存
redo log（重做日志）的缓冲

后台线程作用：

负责刷新内存池中的数据，保证缓冲池中内存缓存的是最近的数据
将已修改的数据文件刷新到磁盘文件
保证在数据库发生异常情况下InnoDB能恢复正常运行

后台线程

InnoDB是多线程模型，后台有多个不同后台线程，处理不同任务。

有以下几种：

Master Thread

非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收。

工作方式：

1.0.x版本之前的

Master thread拥有最高的线程优先级别。内部由多个循环组成：

主循环（loop）
后台循环（backgroup loop）
刷新循环（flush loop）
暂停循环（suspend loop）

根据运行状态在这几种状态中切换。

Loop主循环：

在这个循环中，有两大部分的操作：每秒钟的操作和每10秒的操作

每秒一次的操作：

日志缓冲刷新到磁盘，即使这个事务还没有提交
合并插入缓冲（may），判断前一秒发生的IO次数是否小于5次，若小于5次，InnoDB认为当前IO压力很小，可以执行合并插入缓冲的操作。
至多刷新100个InnoDB的缓冲池的脏页到磁盘（may），InnoDB存储引擎判断当前缓冲池中脏页的比例是否超过配置文件中innodb_max_dirty_pages_pct这个参数，若超过，则存储引擎认为需要做磁盘同步的操作，将100个脏页写入。
如果当前用户没有提交活动，则切换到background loop（may）

每10秒一次的操作：

刷新100个脏页到磁盘（may）。判断过去10秒内磁盘的IO操作是否小于200次，若是，则存储引擎认为当前磁盘有足够IO能力，故将100个脏页刷入磁盘。
合并至多5个插入缓冲
将日志缓冲刷新到磁盘，和每一秒进行一次的那个刷新相同。
删除无用的undo页，最多尝试回收20个undo页。
刷新100或10个脏页到磁盘若超过70%脏页，则刷新100个，否则刷新10个。

后台循环（background loop）

当前没有用户活动或数据库关闭就会切换到这个循环。

主要会执行以下操作：

删除无用undo页
合并20个插入缓冲
跳回主循环，没有停止的话。
不断刷新100个页直到符合条件（可能，跳转到flush loop完成）

若flush loop也没有什么事情可做，会切换suspend loop，Master Thread挂起，等待事件的发生。若启用了InnoDB，而没使用InnoDB，则Master Thread总会挂起。

1.2.x版本之前的

上一代在缓冲池向磁盘刷新做了硬编码，对于SSD来说很大程度上，这个规定很大程度限制了InnoDB存储引擎对IO的性能，尤其是写入。

提供innodb_io_capacity的百分比来进行控制刷新到磁盘页的数量，规则如下：

合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity的5%
在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity

innodb_max_dirty_pages_pct在这个版本调整到了75。

新增的另一个参数为innodb_adaptive_flushing(自适应刷新)，影响每秒刷新脏页数量，InnoDB通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。是通过产生redo log的速度来决定最合适的刷新脏页数量。

从1.0.x版本开始引入了参数innodb_purge_batch_size参数，该参数可以控制每次full purge回收的undo页的数量，该参数默认值为20，并且可以动态修改。

InnoDB1.0.x版本在性能方面取得极大提高，这和Master Thread的改动是密不可分的。

从1.0.x开始，命令SHOW ENGINE INNODB STATUS可以查看当前Master Thread的状态信息。

1.2.x版本的

再次进行优化，将Master Thread的伪代码进行修改，如下：

srv_master_do_idle_tasks()就是之前版本中每10秒的操作

srv_master_do_active_tasks()就是之前每秒的操作。

同时对于刷新脏页的操作，从Master Thread线程分离到了一个单独的Page Cleaner Thread，从而减轻了Master Thread的工作，同时进一步提高系统并发性。

IO Thread

InnoDB存储引擎使用大量AIO（Async IO）来处理写IO请求。IO Thread负责这些IO请求的回调处理。

InnoDB1.0以前有四个IO Thread，分别是write、read、insert buffer和log IO thread。

Windows平台可以通过参数innodb_file_io_threads来增大IO Thread。

从InnoDB 1.0.x版本开始，read thread和write thread分别增大到4个，并且参数改为使用innodb_read_io_threads和innodb_write_io_threads。

可以通过命令：SHOW ENGINE INNODB STATUS来观察InnoDB中的IO Thread。

Purge Thread

事务提交后，所使用的undolog可能不再被需要，因此需要PurgeThread来回收已经使用并分配的undo页。

可以在MySQL数据库的配置文件中添加如下命令来启用独立的Purge Thread：

在InnoDB1.1版本，即使innodb_purge_threads设置大于1，InnoDB启动时也会将其设为1。

从InnoDB1.2开始，InnoDB支持多个PurgeThread，目的是为了进一步加快undo页的回收。进一步利用磁盘随机读取性能。设置4个Purge Thread的方式如下：

Page Cleaner Thread

在InnoDB1.2.x版本中引入。作用是将之前版本的脏页刷新操作放到单独的线程中来完成。目的是为了减轻原Master Thread的工作以及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎性能。

内存

缓冲池

InnoDB在磁盘中按照页的方式进行管理，即为基于磁盘的数据库系统。由于CPU和磁盘之间的速度差异，故在内存中常用缓冲池技术来提高性能。

缓冲池机制：在下一次再读的时候会检查是否还在缓冲池中，若还在则命中，无需IO。

对域数据库中页的修改操作：首先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上。页刷新到磁盘上是通过Checkpoint的机制刷新回磁盘上。

**InnoDB缓冲池配置设置：**通过innodb_buffer_pool_size来设置。

**允许多个缓冲池实例：**InnoDB 1.0.x版本开始，

每个页根据哈希值平均分配到不同缓冲池实例中。可以通过参数innodb_buffer_pool_instances来进行设置。

可以通过INNODB_BUFFER_POOL_STATS来观察缓冲状态：

例子：

缓冲池管理方案：

通过**LRU（最近最少使用算法）**进行管理，最频繁使用的页在LRU前端，最少使用的在LRU尾端。缓冲池不能存放新读取到的页时，首先释放LRU尾端的页。

InnoDB缓冲池页大小默认16KB。并对LRU进行优化，设置midpoint位置，新读取的页放入midpoint位置，称为midpoint insertion stategy。mindpoint之前的列表称为new列表，之后的列表称为old列表，该位置在LRU列表长度的5/8处，可以由innodb_old_blocks_pct控制。

例如：

默认值为37，代表新读取的页插入到LRU列表尾端的37%位置。

由此引出问题，为什么不采用朴素LRU，直接将读取的页放入LRU列表首部？

若是直接将读取的页放入LRU首部，某些SQL操作（如索引或数据的扫描操作），这些操作需要访问表中很多页，而这些页仅仅在此次查询中需要，并不是活跃数据，若放在首部则很可能将所需要的热点数据从LRU中移除。

因此，InnoDB引入了innodb_old_blocks_ti

me来表示页需要读取到mid后需要等待多久才回被加入到LRU列表热端。

可以用以下方式尽可能使LRU热点数据不被刷出：

即扫描索引时，把该时间设置较长。

**页移动：**首先从Free列表中查找是否有空闲页，若有则放入LRU列表，否则淘汰LRU末尾页，分配给新页。

LRU部分old到new时，操作叫做page made young。

而由于innodb_old_blocks_time而导致没有从old到new的操作称为page not made young。可以通过命令SHOW ENGINE INNODB STATUS来观察LRU列表和Free列表的情况，显示的是过去某个时间范围内InnoDB存储引擎的状态。

主要包含一些变量：

Free Buffers：当前Free列表中页的数量。

Database pages：LRU列表中页数据。

注：缓冲池页可能会分配给自适应哈希索引、Lock信息、Insert Buffer等页，故Free Buffers和Database pages数量之和不等于Buffer pool size。

pages made young：LRU列表中页移动到前端的次数。

youngs/s、non-youngs/s表示每秒这两种操作的次数。

Buffer pool hit rate：缓冲池的命中率，不应该低于95%。

INNODB_BUFFER_POOL_STATS来观察缓冲池的运行状态。

INNODB_BUFFER_PAGE_LRU来观察每个LRU列表每个页的具体信息。

压缩页：

1.0.x版本开始支持压缩页功能，将原本16KB的页压缩，对于非16KB的页，通过unzip_LRU列表进行管理。通过SHOW ENGINE INNODB STATUS来查看

如何管理？

unzip_LRU列表对不同压缩页大小页进行管理，通过伙伴算法进行内存分配。从缓冲池申请4KB页过程如下：

检查4KB的unzip_LRU列表，检查是否有可用的空闲页。有则直接使用
没有则检查8KB的unzip_LRU列表
若能获取，则将页分成两个4KB页，存放到4KB的unzip_LRU列表
若不能获取，则从LRU列表申请一个16KB的页，将页分为一个8KB和两个4KB的页，分别存储。

可以通过INNODB_BUFFER_PAGE_LRU来观察unzip_LRU的页。

LRU中被修改后的页被称为脏页。缓冲池的页和磁盘上的页数据产生不一致。通过CHECKPOINT机制将页刷新回磁盘，Flush列表中的页即为脏页列表。

脏页既存在LRU列表中，又存在Flush列表中。Flush是用来管理将页刷新回磁盘。

Flush列表也能通过SHOW ENGINE INNODB STATUS来进行查看。

脏页可以通过INNODB_BUFFER_PAGE_LRU来查看，并且必须加上OLDEST_MODIFICATION大于0的SQL查询条件。

重做日志缓冲

InnoDB存储引擎首先将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。无需设置很大，保证每秒产生的事务量在此缓冲之内。该值由innodb_log_buffer_size控制，默认8MB。

默认大小足以满足绝大部分要求，下列三种情况会将重做日志缓冲中内容刷新到外部磁盘重做日志文件中：

Master Thread每一秒
每个事务提交时
重做日志缓冲池剩余空间小于1/2

额外内存池

InnoDB存储引擎对内存管理通过内存堆进行管理。**每个缓冲池的帧缓冲有对应的缓冲控制对象，记录了一些诸如：LRU、锁、等待信息…**这个对象内存需要从额外内存池申请。

2.4 Checkpoint技术

用于解决“脏页”问题。

**Write Ahead Log策略：**用于避免数据丢失的问题。当事务提交时，先写重做日志，再修改页。发生宕机时，通过重做日志来恢复数据；

Checkpoint使用目的：

缩短数据库恢复时间
缓冲池不够时，将脏页刷新到磁盘
重做日志不可用时，刷新脏页

数据库宕机时，数据库无需重做所有日志，因为Checkpoint之前的页都已经刷新回磁盘。

缓冲池不够用时，LRU算法会溢出最近最少使用的页，此页为脏页，需要强行执行Checkpoint，将脏页刷新回磁盘。

在InnoDB中，有两种Checkpoint：

Sharp Checkpoint：发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数innodb_fast_shutdown=1。
Fuzzy Checkpoint：在运行时采用该方式，而有如下几种Fuzzy Checkpoint：
- Master Thread Checkpoint：
  
  每秒或每10秒的速度从缓冲池的脏页列表刷新一定比例的页回磁盘。
- FLUSH_LRU_LIST Checkpoint：
  
  **InnoDB需要保证LRU列表中需要有100个空闲页可供使用，**1.1.x版本之前，检查操作发生在用户查询线程中，若空闲页不足会将LRU列表尾端页移除，若有脏页则会进行Checkpoint，这些页来自LRU列表。1.2.x版本开始这个检查放在单独page Cleaner线程中进行。innodb_lru_scan_depth来控制LRU列表中可用页数量，默认1024。
- Async/Sync Flush Checkpoint:
  
  保证重做日志的循环使用的可用性。指重做日志不可用的情况，需要强制将一些页刷新回磁盘，脏页从脏页列表中选取。
  
  lsn:日志逻辑序列号
  
  redo_lsn：已经写入到重做日志的LSN
  
  checkpoint_lsn：已经刷新回磁盘最新页的LSN
  
  checkpoint_age=redo_lsn-checkpoint_lsn
  
  async_water_mark=75%*total_redo_log_file_size
  
  sync_water_mark=90%*total_redo_log_file_size
  
  若：
  
  checkpoint_age<async_water_mark,无需刷新任何脏页到磁盘
  
  async_water_mark<checkpoint_age<sync_water_mark触发Async Flush，从Flush列表中刷新足够的脏页回磁盘，使得刷新后满足第一条条件
  
  checkpoint_age>sync_water_mark，这种情况很少发生，除非设置重做日志太小，并且1进行LOAD DATA的BULK INSERT操作，此时触发Sync Flush操作，从Flush
  
  列表中刷新足够的脏页回磁盘，满足第一条条件。
- Dirty Page too much Checkpoint
  
  脏页数量过多，导致InnoDB存储引擎强制进行Checkpoint。参数可用innodb_max_dirty_pages_pct控制
  
  该值为75表示，当缓冲池脏页数量占据75%时，强制执行Checkpoint。

2.5 InnoDB的关键特性

InnoDB的关键特性包括：

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

这些特性给InnoDB带来了更好的性能和更高可靠性

插入缓冲

带来的是性能上的提升。

Insert BUffer

Insert Buffer既在缓冲池中有信息，也和数据页一样，也是物理页的一个组成部分。

功能：

对于非聚簇索引的插入和更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚簇索引页是否在缓冲池中。**若在则直接插入，否则先放入一个Insert Buffer对象中。**以一定频率和情况进行Insert Buffer和辅助索引叶子节点的merge操作，这时通常可以将多个插入合并到一个操作中。

Insert Buffer的使用需要同时满足两个条件：

索引是二级索引
索引不是唯一的

可以通过SHOW ENGINE INNODB STATUS查看插入缓冲的信息。

segsize代表当前InsertBuffer的大小

free list代表空闲列表长度

size代表合并记录页的数量

修改IBUF_POOL_SIZE_PER_MAX_SIZE可以对插入缓冲的大小进行控制，如改为3，就最大只能使用1/3的缓冲池内存。

Change Buffer

可以对DML操作（INSERT、DELETE、UPDATE）都进行缓冲，分别是Insert Buffer、Delete Buffer、Purge Buffer。

所适用的对象仍然是非唯一的辅助索引。

对一条记录UPDATE有两个过程：

将记录标记为已删除
真正将记录删除

1.2.x版本开始，可以通过innodb_change_buffer_max_size来控制Change Buffer的最大使用内存量。默认值为25，表示最多使用1/4的缓冲池内存空间，该参数最大有效值为50。

Insert Buffer的实现原理

数据结构是B+树，全局只有一颗B+树，负责对所有的表的辅助索引进行Insert Buffer，该B+树存放在ibdata1共享空间中。

非叶子节点存放的是search key：

共9个字节，space表示待插入记录所在表的表空间id

space占4字节，marker占1字节，offset表示页所在偏移量。

实现流程：

辅助索引要插入到页时，如果页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询Insert Buffer这颗B+树，再将这条记录插入到叶子节点中。

叶子节点：

metadata占4字节，

IBUF_REC_OFFSET_COUNT是保存两个字节的整数，用来排序每个记录进入Insert Buffer的顺序。

Merge Insert Buffer

引入问题：Insert Buffer中的记录何时合并到真正的辅助索引中？

Merge Insert Buffer的操作可能会发生以下几种情况：

辅助索引页被读取到缓冲池
Insert Buffer Bitmap页追踪到该辅助索引页已经无可用空间时
Master Thread

第一种情况：执行正常SELECT操作，检查Insert Buffer Bitmap页，发现辅助索引有记录存放在Insert Buffer B+树中。故可用将对该页通过一次操作将多次操作合并到原有的辅助索引页中。

第二种情况：Insert Buffer Bitmap追踪每个辅助索引页的可用空间小于1/32页时，会将之进行一次合并操作。

第三种情况：在Master Thread线程种每秒或每10秒进行一次Merge Insert Buffer操作，每次进行merge操作的页数量不同。根据srv_innodb_io_capacity的百分比来决定真正要合并多少个辅助索引页。

两次写

带给InnoDB存储引擎在数据页上的可靠性。

为什么不用redo log进行恢复？

重做日志种记录的是对页的物理操作，即记录偏移量和所需做的操作。如果页已经发生损坏，再对其进行重做是没有意义的。即用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原页，再重做，这就是两次写。

DoubleWrite由两部分组成。一部分是内存的DoubleWrite Buffer，大小为2MB。另一部分是物理磁盘上共享表空间中连续的128个页，即两个区，大小同样为2MB。

在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的DoubleWrite Buffer中，之后再通过DoubleWriteBuffer分两次，每次1MB的顺序写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。

doublewrite页是连续的，开销不大。

将doublewrite buffer页写入各个表空间文件中，这个写是离散的。

参数skip_innodb_doublewrite可以禁用doublewrite功能，可能会发生写失效问题。

自适应哈希索引

优化效率：启用了AHI后，读取和写入速度可以提高2倍，辅助索引的连接操作性能可以提高5倍。

InnoDB存储引擎会监控对表上个索引页的查询，如果观察到建立哈希索引可以来来速度提升，则建立哈希索引，即Adaptive Hash Index（AHI）。

AHI通过缓冲池的B+树构造而来，建立速度快，无需对整张表建立索引。根据访问频率和模式来自动为热点建立索引。

访问模式是指：查询的条件一样。

AHI还有如下要求：

以该模式访问100次
页通过该模式访问了N次，其中N=页中记录*1/16

使用SHOW ENGINE INNODB STATUS可以查看当前AHI使用状况。

注：哈希索引只能用来搜索等值查询

异步IO

即Asynchronous IO，用于提升磁盘操作性能。

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

AIO的另一个优势是可以进行IO Merge操作，就是将多个IO合并为一个IO，可以提高IOPS的性能。

例子：

用户需要访问页（8，6）、（8，7）、（8，8）

同步IO需要进行3次IO操作，而AIO会判断这三个页是连续的，因此AIO底层会发送一个IO请求，从（8，6）开始，读取48KB的页。

Native AIO需要OS提供支持。Windows和Linux都支持。可以用参数innodb_use_native_aio来控制是否启用Native AIO

在InnoDB存储引擎中，read head读取都是通过AIO完成，脏页刷新，即磁盘写入都是通过AIO完成。

刷新邻接页

工作原理：当刷新一个脏页，InnoDB存储引擎会检测该页所在区所有页。如果是脏页，则会一起刷新。

通过AIO可以将多个IO写入操作合并为一个IO操作。

考虑两个问题：

是不是可能将不怎么脏的页进行了写入，而该页又很快变成脏页。
固态硬盘有较高的IOPS，是否需要这个特性？

InnoDB在1.2.x版本开始提供参数innodb_flush_neighbors来控制是否启用该特性。

对于固态硬盘建议关闭此特性，即参数设置为0。

2.6 启动、关闭和恢复

关闭时，参数**innodb_fasst_shutdown影响表的存储引擎为InnoDB的行为。**该参数可以取0，1，2。默认为1。

0代表：关闭时，InnoDB需要完成所有的full purge和merge insert buffer。并且将所有脏页刷回磁盘。

1代表：不需要完成full purge 和merge insert buffer操作，但是缓冲池中一些数据脏页还是会1刷回磁盘。

2代表：什么都不做，只把日志写入日志文件。这样不会有任何事务的丢失，但是下次MySQL启动会进行恢复操作。

innodb_force_recovery可以设置0-6的值：

0代表当发生需要恢复时，进行所有的恢复操作。若无法进行有效恢复，如数据页发生corruption，数据库可能会宕机。

1：忽略检查到的corrupt页

2：阻止Master Thread的运行

3：不进行事务回滚

4：不进行插入缓冲合并操作

5：不查看撤销日志，InnoDB存储引擎会将未提交事务视为2已提交

6：不进行前滚操作

注：若设置了大于0操作，用户可以对表进行select、create和drop操作，但是insert、update和delete操作是不允许的。

3 文件

文件主要有以下这些：

参数文件：告诉MySQL实例启动时哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，各种参数类型
日志文件：记录MySQL实例对某种条件做出响应写入的文件，包括：错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件。
**socket文件：**当用UNIX域套接字方式进行连接时需要的文件。
**pid文件：**MySQL实例的进程ID文件。
**表结构文件：**用来存放MySQL表结构定义的文件。
**存储引擎文件：**每个存储引擎都会有自己的文件保存各种数据，这些存储引擎真正存储了记录和索引等数据。

3.1 参数文件

作用：用来寻找数据库各种文件所在位置以及指定初始化参数，这些参数定义了某种内存结构有多大等。

读取方式：MySQL实例会按照一定顺序在指定位置读取，使用命令mysql–help|grep my.cnf来寻找。

参数文件：MySQL实例可以不需要参数文件，此时所有参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值

启动：实例在默认数据库目录下找不到mysql架构同样会启动失败。mysql架构记录了访问该实例的权限。

存储方式：以文本方式进行存储，可以通过常用文本编辑软件（如vi，emacs）进行参数修改。

什么是参数？

可以把数据库参数看作一个键值对。

例子：比如一个对于InnoDB存储引擎来说很重要的一个参数innodb_buffer_pool_size。如果把该参数设置为1G，即innodb_buffer_pool_size=1G。这里的键即为innodb_buffer_size，而值是1G。

查看参数：

通过命令SHOW VARIABLES查看数据库中所有参数。
也可以通过LIKE来过滤参数名。
从MySQL5.1版本开始，可以通过GLOBAL_VARIABLES视图来进行查找。

例子：

推荐使用SHOW VARIABLES，该命令比较简单。

参数类型

MySQL数据库中的参数可以分为两类：

动态参数：可以在MySQL实例运行中进行更改。
静态参数：说明在整个实例生命周期都不得进行修改。就如只读。

修改方式：可以通过SET命令对动态参数值进行修改，SET语法如下：

global和session关键字表明该参数的修改是基于整个实例的生命周期还是当前会话。

某些参数只能在会话中修改，如autocommit

有些参数修改完后，在整个实例生命周期都有效，如binlog_cache_size

有些参数既可以在会话中又可以在整个实例生命周期中有效，如read_buffer_size

需要注意，对变量全局进行修改后，并不会修改参数文件中的值，若数据库实例下次启动该参数并不是此时的修改值。

MySQL所有动态变量的可修改范围可以参考Dynamic System Variables的相关内容。

修改的例子：

对于静态参数的修改会报如下错误：

3.2 日志文件

日志文件记录影响了MySQL数据库的各种类型活动。常见的日志文件有：

错误日志（error log）
二进制日志（binlog）
慢查询日志（slow query log）
查询日志（log）

错误日志

**对MySQL的启动、运行、关闭过程进行了记录。**在遇到问题时应该首先查看该文件以便定位问题。

不仅记录了错误的内容，也记录了警告的信息，通过一些警告有助于DBA对于数据库和存储引擎进行优化。

可以通过命令**SHOW VARIABLES LIKE ‘log_error’**来定位该文件。

慢查询日志

帮助DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。

例子：可以在MySQL启动时设置一个阈值，**将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。**DBA每天或一段时间都进行检查，确认是否有SQL语句进行优化。该阈值通过参数long_query_time来进行设置，默认为10，代表10秒。

默认情况MySQL不启动慢查询日志，用户需手工设置为ON。

注意记录的是所有运行时间大于long_query_time这个阈值的SQL语句。从5.1开始，该参数用微秒来记录SQL的运行时间。

log_queries_not_using_indexes:作用是如果运行的SQL语句没有使用索引，则MySQL数据库会将该条SQL语句记录到慢查询日志文件中。

log_throttle_queries_not_using_indexes:用于表示每分钟允许记录到slow log的且未使用索引的SQL语句次数。默认为0，代表没有限制。

解决时间增加，慢查询日志中SQL语句变多而难以分析的方案：使用mysqldumpslow命令，可以帮助DBA解决该问题。

如果用户希望得到执行时间最长的10条SQL语句，可以运行如下命令：

MySQL5.1将慢查询日志放入一张表中，在mysql架构下，名为slow_log。定义如下：

InnoDB加强了对于SQL语句的捕获方式，在原版MySQL的基础上再slow log中增加了对于逻辑读取和物理读取。

物理读取指的是从磁盘进行IO读取次数，逻辑读取包含所有读取。

**long_query_io:**将超过该逻辑IO次数的SQL语句记录到slow log中。默认值100。

**slow_query_type:**表示启动slow log的方式，包括：

0:表示不将SQL语句记录到slow log

1：根据运行时间将SQL语句记录到slow log

2：根据逻辑IO次数将SQL语句记录到slow log

3：根据运行时间以及逻辑IO次数将SQL语句记录到slow log

查询日志

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

默认文件名：主机名.log

二进制日志

**记录了对MySQL数据库执行更改的所有操作。**不包括SELECT和SHOW这类操作。若操作没有导致数据库变化，也可能写入二进制日志。

作用：

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

启动：

配置参数log-bin [=name]可以启动二进制日志，若不指定name，则默认二进制日志文件为主机名。后缀名为二进制日志的序列号，所在路径为数据库所在目录。

影响二进制日志记录的信息和行为的参数：

max_binlog_size：指定单个二进制日志文件的最大值。超过该值，产生新的二进制日志文件。5.0开始默认1G

binlog_chache_size：缓冲大小，使用事务时，未提交的二进制日志被记录到一个缓存中，等待事务提交时直接将缓冲中的二进制日志写入二进制日志文件。默认大小32K，并且是基于会话的。事务的记录大于该参数时，会把缓冲日志写入一个临时文件中。

sync_binlog：表示每写缓冲多少次就会同步到磁盘上。

binlog-do-db：表示需要写入哪些库的日志

binlog-ignore-db：表示需要忽略哪些库的日志

log-slave-update：如果需要搭建master=>slave=>slave架构的复制，则必须设置该参数

binlog_format：该参数影响了记录二进制日志的格式。可设置的值有STATEMENT、ROW、MIXED。

STATEMENT格式和之前MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

在ROW格式下，二进制日志记录的是表的行更改情况。若设置该格式，可以把InnoDB事务隔离级别设置为READ COMMITTED

MIX格式下，默认采用STATEMENT格式进行二进制日志文件记录，在一些情况下会使用ROW格式。可能情况：

表存储引擎为NDB，对表DML操作以ROW格式记录
使用了UUID（），USER（），CURRENT_USER(),FOUND_ROWS(),ROW_COUNT()等不确定函数
使用了INSERT DELAY语句
使用了用户定义函数
使用了临时表

ROW所要求的空间大于STATEMENT

**如何查看日志？**要查看二进制日志文件的内容，必须通过mysqlbinlog工具来查看。

如果是ROW格式的记录方式，则会发现mysqlbinlog变得无法直观阅读。加上参数-v或-vv就能清楚看到执行的具体信息。-vv会比-v多显示出更新的类型。

3.3 套接字文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字文件，套接字文件可以由socket参数控制，一般在/tmp目录下，名为mysql.sock。

3.4 pid文件

MySQL实例启动时，会将自己的进程ID写入一个文件中，该文件即为pid文件，该文件由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid。

3.5 表结构定义文件

MySQL数据的存储是根据表进行的，每个表都有与之对应的文件。

无论采用何种存储引擎，MySQL都有一个以.frm为后缀名的文件，该文件记录了该表的表结构定义。

frm还用来存放视图的定义，如用户创建了一个v_a视图，那么对应地会产生一个v_a.frm文件，用来记录视图的定义，该文件是文本文件，可以直接使用cat命令进行擦好看

3.6 InnoDB存储引擎文件

表空间文件

InnoDB采用将存储的数据按表空间进行存放。默认配置有初始大小为10MB，名为ibdatal的文件，该文件为默认表空间文件。用户可以通过innodb_data_file_path参数进行设置，格式为：

可以通过多个文件组成表空间，同时制定文件的属性。

此处将/db/ibdata1和/dr2/db/ibdata2两个文件来组成表空间。

若设置参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立的表空间。独立表空间命名规则为：表名.ibd。

单独的表空间只存储该表的数据、索引和插入缓冲BITMAP。其余信息还是存放在默认的表空间中。

重做日志文件

文件名：默认情况下，InnoDB数据目录下以ib_logfile0和ib_logfile1的文件。

工作方式：当实例或介质失败时，重做日志文件就登场了。例如：数据库由于所在主机掉电导致实例失败，InnoDB会使用重做日志恢复到掉电前的时刻，用于保证数据的完整性。

存储原理：每个InnoDB至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。可以设置多个镜像日志组，将不同文件放在不同磁盘，提高重做日志可用性。写入方式为：循环写入。即当一个重做日志写满后切换另一个。

影响重做日志文件的属性：

**innodb_log_file_size：指定每个重做日志文件的大小。**1.2.x版本以前，重做日志文件总大小不能大于4GB，1.2.x版本扩大到512GB。
**innodb_log_files_in_group:**指定日志文件组中重做日志文件的数量，默认为2。
**innodb_mirrored_log_groups:**指定日志镜像文件组的数量，默认为1。1表示只有一个文件组，没有镜像。
**innodb_log_group_home_dir:**指定了日志文件组所在的路径，默认为./（表示在MySQL数据库的数据目录下）。

重做日志文件不能过大或过小。

如果设置的过大，在恢复时可能需要很长的时间。

如果设置的过小，导致一个事务的日志需要多次切换重做日志文件。并且会频繁发生async checkpoint，导致性能的抖动。

重做日志有一个capacity变量，该值代表最后的检查点不能超过这个阈值，如果超过则必须将缓冲区中的脏页列表中的部分脏数据页写回磁盘，这时会导致用户线程的阻塞。

同样是记录事务日志，和二进制日志有什么区别？

**记录存储引擎不同：**二进制日志会记录所有与MySQL数据库有关的日志记录，包括InnoDB，MyISAM，Heap等其他存储引擎的日志。而InnoDB重做日志只记录有关该存储引擎本身的事务日志。

记录内容不同：二进制日志无论格式设置为哪一种（STATEMENT，ROW，MIXED）,记录的都是关于一个事务的具体操作内容。而重做日志记录到是关于每个页的更改物理情况

写入时间不同：二进制日志文件仅在事务提交前进行提交，即只写磁盘一次。对于重做日志，不断有重做日志条目被写入到重做日志文件中。

重做日志条目的结构：

redo_log_type占用1字节，代表重做日志的类型

space表示表空间的ID，但是采用压缩的方式，因此占用空间可能小于4字节。

page_no表示页的偏移量，同样采用压缩的方式。

redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析。

重做日志的写入过程：

重做日志往磁盘写入时，按512个字节，即一个扇区的大小进行写入。扇区是写入的最小单位，因此可以保证写入必定成功，因此在重做日志的写入过程中不需要有doublewrite

除了MasterThread会每秒将重做日志缓冲写入磁盘的重做日志文件中。另一个触发写磁盘的过程由参数innodb_flush_log_at_trx_commit控制，表示提交时处理重做日志的方式。

该参数有效值为0，1，2。

0：代表提交事务时，不将事务的重做日志写入磁盘上的日志文件，而等待主线程每秒刷新。

1：执行commit时将重做日志缓冲同步写入磁盘，即伴有fsync的调用

2：将重做日志异步写入到磁盘，即**写到文件系统的缓存中。**不能完全保证在执行commit时肯定会写入重做日志文件，只是有这个动作发生。

为了保证ACID的持久性，必须将这个参数设置为1，即确保事务都已经写入重做日志文件。当数据库宕机时，可以通过重做日志文件恢复。若设置为0或2，都有可能导致恢复时部分事务的丢失。设置为2时，MySQL发生宕机而OS以及服务器并没有宕机，此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

4 表

本节讲述逻辑：InnoDB逻辑存储以及实现->表的物理存储特征

表是关于特定实体的数据集合，是关系型数据库模型的核心。

4.1 索引组织表

概念：InnoDB存储引擎中，表中数据是根据主键顺序组织存放的（聚簇索引）。

主键选取规则：

主键
非空唯一索引
InnoDB自动创建一个6字节大小的指针

当表中由多个非空唯一索引时，选取建表时第一个定义的非空唯一索引。注意，顺序是按照索引的顺序，可以通过_rowid显示表的主键，但是只能用于查看单个列为主键的情况，不能用于多列组成的主键。

4.2 InnoDB逻辑存储结构

从逻辑结构看，所有数据存放在表空间（tablespace），表空间由段（segment），区（extent），页（page）组成。页也称为块。

表空间

InnoDB存储引擎逻辑结构最高层。所有数据都存放在表空间中。

例如：默认情况下InnoDB都有一个共享表空间ibdata1。若用户启用innodb_file_per_table，则每张表内数据可以单独放在一个表空间中。

每张表的表空间只存储：数据、索引和插入缓冲Bitmap页

如：回滚(undo)信息、插入缓冲索引页、系统事务信息、二次写缓冲还是放在原来的共享表空间内。

**故：**启用了innodb_file_per_table后，共享表空间还是会不断增加大小。

InnoDB不会再执行rollback时收缩这个表空间，Master Thread每10秒回执行一次full purge操作对undo页进行回收。

段

表空间由各个段组成，常见的段有数据段、索引段、回滚段。

InnoDB中数据段即为B+树的叶子节点，索引段即为B+树的非索引节点。

对段的管理是由引擎自身所完成。

区

区是由连续的页组成的空间，任何情况下每个区的大小都为1MB。

InnoDB一次从磁盘申请4-5个区，默认情况下InnoDB页大小为16KB，一个区中有64个连续的页。（64*16KB=1024KB）

1.0.x版本引入压缩页，每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K，4K，8K。此时每个区对应页数量为512，256，128。

1.2.x版本引入参数innodb_page_size，通过该参数可以将默认页大小设置为4K、8K。区的大小总是1M，并不改变。

启用参数innodb_file_per_table后，创建的表默认大小时96KB。而区中是64个连续的页，创建表的大小至少应该是1MB才对？

每个段开始时，先用32个页大小的碎片页来存放数据。使用完这些页之后才是64个连续页的申请。目的是：对于一些小表，或者时undo这类段，可以在开始时申请较少的空间，节省容量开销。

页

**页是InnoDB磁盘管理的最小单位。**默认每个页大小为16KB。

InnoDB 1.2.x版本开始，可以通过参数innodb_page_size将页大小设置为4K、8K、16K。

常见的页类型：

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

行

数据是按照行进行存放的。每个页最多存放16KB/2-200行记录，即7992行记录。

4.3 行记录格式

InnoDB是以行的形式存储的。

1.0.x版本之前InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录。InnoDB源代码通过PHYSICAL RECORD和PHYSICAL RECORD来区分两种格式。MySQL 5.1版本默认采用Compact行格式。row_format属性表示当前所使用的行记录结构。

Compact行记录格式

非NULL变长字段长度列表，逆序。长度为：

若列长度小于255字节，用1字节表示
若列长度大于255个字节，用2字节表示

NULL标志位，该位指示了该行数据是否有NULL值，有则用1表示，该部分占1字节。

**记录头信息：**归档占用5字节，每一位的含义如下：

后面就是实际存储每个列的数据，NULL不占用该部分任何空间。

每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别是6字节和7字节的大小。

若InnoDB表没有定义主键，每行还会增加6字节的rowid列

例子：

固定长度CHAR字段在未能完全占用长度空间时，会使用0x20来填充。

记录头信息的最后两个字节，这两个字节代表next_recorder，0x2c代表下一个记录的偏移量。即当前记录加上偏移量就是下条记录的起始位置。

NULL标志位转换为二进制后代表：**第几列的数据为NULL。**注意：NULL值不占用空间。

Redundant行记录格式

MySQL5.0之前InnoDB行记录存储方式。

Redundant字段长度偏移列表：同样按照列的顺序逆序放置。若列长度小于255字节，用1字节表示，若大于255字节，用2字节表示。

记录头信息：与Compact不同，Redundant行记录格式记录头占用6字节。

n_fields值代表一行中列的数量，占用10位。故MySQL一行支持最多的列为1023。1byte_offs_flags定义了偏移列表占用1字节还是2字节。

行溢出数据

InnoDB可以将一条记录中某些数据存储在真正的数据页面之外，一般认为BLOB、LOB这类大对象列类型的存储会把数据存放在数据页面之外。

VARCHAR数据类型

MySQL数据库的VARCHAR类型可以存放65535字节，但是若创建65535字节的VARCHAR类型，会报如下错误：

由于存在别的开销，VARCHAR能存放的最大长度为65532。

MySQL官方手册定义的65535长度是指所有VARCHAR列的长度总和，如果列长度总和超过这个长度，依然无法创建。

**保存方式：**行溢出数据页面只保存了VARCHAR的前部分字节的前缀数据，之后是偏移量，指向行溢出页，也就是Uncompressed BLOB Page。

选择分配单个数据页中还是BLOB页中？

InnoDB存储引擎表通过索引组织，即B+Tree结构，因此每个页都应该至少两条行记录（否则失去B+树意义，变为链表），因此若页中只能存放一条记录，则InnoDB就会将行数据存放到溢出页中。

该阈值为8098，若未超过则VARCHAR类型的行数据就不会放入BLOB页中。

对于TEXT或BLOB数据类型，是存放在数据页还是BLOB页中，页需要保证一个页能够存放两条记录。

实际用户在大多数情况下BLOB的行数据还是会发生行溢出，实际数据存放在BLOB页，数据页只保存数据的前786字节。

Compressed和Dynamic行记录格式

InnoDB1.0.x版本引入新文件格式（可以理解为页格式）。

之前的Compact和Redundant行格式属于Antelope文件格式。

新文件格式Barracuda拥有两种新的行记录格式：Compressed和Dynamic

两种记录格式对于存放在BLOB中的数据采用了完全的行溢出方式。即数据页中只存放20个字节的指针，实际数据都存放在Off Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。

Compressed行记录另一个功能：存储在其中的行数据会以zlib算法进行压缩，故对于BLOB、TEXT、VARCHAR这类大长度类型能进行有效压缩。

CHAR行结构存储

CHR（N）指的是字符长度，代表在多字节字符编码集下，InnoDB在内部将其视作变长字符类型。对于没能填满长度的字符还是0x20。

结论：在多字节字符集的情况下，CHAR和VARCAHR的实际存储基本没有区别。

4.4 InnoDB数据页结构

页是InnoDB存储引擎管理数据库的最小磁盘单位。页类型为B-Tree Node的页存放的是表中行的实际数据。

InnoDB数据页有以下7个部分组成：

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

其中，file Header、Page Header、File Trailer的大小固定，分别为38、56、8字节，这些空间用于标记该页的一些信息，如checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际行记录存储空间，大小是动态的。

File Header

文件头用来记录页的一些头信息，由8部分组成，共占用38字节。

Page Header

用来记录数据页的状态信息，由14个部分组成，共占用56字节。

Infimum和Supremum Record

InnoDB的两个虚拟行记录，用来限定记录的边界。Infimum是比该页任何主键都要小的值，Supremum是比任何可能大的值都要大的值。

页创建时创建，并且不会被删除。

User Record和Free Space

User Record时实际存储行记录的内容。

Free Space指的是空闲空间，是一个链表结构。在一条记录被删除之后，该空间会被加入到空闲链表中。

Page Directory（页目录）

存放了记录的相对位置，并不是偏移量。这些记录指针称为Slots（槽）或目录槽（Directory Slots），逆序存储。每个槽2字节。

InnoDB存储引擎的槽是一个稀疏目录，一个槽中可能包含多个记录。伪记录Infimum的n_owned值总为1，记录Supremum的n_owned的取值范围为[1,8]，其他用户记录n_owned的取值范围[4,8]。当记录被插入或删除时需要对槽进行分裂或平衡的维护操作。

**查找：**二叉查找只是一个粗略结果，因此InnoDB需要通过recorder header中的next_record来继续查找相关记录。

注：B+树索引本身不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入内存，然后通过Page Directory在进行二叉查找。并且二叉查找在内存中速度很快。

File Trailer

目的：检测页是否已经完整写入磁盘（如可能发生在写入过程中磁盘损坏、机器关机等）。

**结构：只有一个FIL_PAGE_END_LSN部分，占用9字节。**前4字节表示该页的checksum值，后4字节代表File Header中的FIL_PAGE_LSN（该页最后被修改的日志序列位置）相同。

将这两个值和File Header中的FIL_PAGE_SPACe_OR_CHKSUM和FIL_PAFE_LSN值进行比较，观察是否一致，以保证页完整性。（checksum的比较通过InnoDB的checksum函数来进行）。

每从磁盘读取一个页都会检查该页完整性。可以通过参数innodb_checksums来开启或关闭这个检查。

innodb_checksum_algorithm该参数用于控制检测checksum的算法，默认为crc32.。

InnoDB数据页结构示例

4.5 Named File Formats机制

1.0.x版本开始，InnoDB存储引擎通过该机制来解决不同版本下页结构兼容性的问题。

1.0.x版本之前的文件格式定义为Antelope，这个版本支持的文件格式定义为Barracuda。新的文件格式总是包含之前的版本页格式。

参数innodb_file_format用来指定文件格式，可以通过下面方式来查看所使用的InnoDB存储引擎文件格式。

参数innodb_file_format_check用来检测当前InnoDB存储引擎文件格式的支持度，该值默认为ON，若出现不支持的文件格式，可能可以在错误日志文件中看到

4.6 约束

数据完整性

关系型数据库和文件系统的不同点是能保证数据的完整性。几乎所有关系型数据库都提供约束机制来保证数据库中的完整性。

数据完整性有以下三种形式：

实体完整性：保证表中有一个主键，InnoDB存储引擎中，用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性。还可以通过编写一个触发器来保证数据的完整性。
**域完整性：保证数据每列的值满足特定的条件。**域完整性可以通过以下几种途径来保证：
- 选择合适的数据类型确保一个数据值满足特定的条件
- 外键约束
- 编写触发器
- 还可以考虑用DEFAULT约束作为强制域完整性的一个方面
参照完整性：保证两张表之间的关系。InnoDB存储引擎支持外键，因此允许用户定义外键以强制参照完整性，也可以通过编写触发器以强制执行。

对于InnoDB存储引擎本身而言，提供了以下几种约束：

Primary Key
Unique Key
Foreign Key
Default
NOT NULL

约束创建和查找

约束的创建可以采用以下两种方式：

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

对于Unique Key的约束，用户还可以通过命令CREATE UNIQUE INDEX来建立。

对于主键约束而言，其默认约束名为PRIMARY。

对于Unique Key约束而言，默认约束名和列名一样。

还可以通过ALTER TABLE来创建约束，定义用户所希望的约束名,例如：

对于Foreign Key，用户需要创建另一张表，如下：

约束和索引的区别？

约束和索引的概念有所不同，约束是一个更为逻辑的概念，用来保证数据的完整性。而索引是一个数据结构，既有逻辑上的概念，也代表着物理存储的方式。

对错误数据的约束

概念：z哎某些默认设置下，MySQL数据库允许非法或不正确的数据插入或更新，或者可以在数据库内部将其转化为一个更为合法的值。例子：向NOT NULL字段插入一个NULL值时，MySQL数据库会将其更改为0后再进行插入。数据库本身没有对数据正确性进行约束。

若用户想通过约束对数据库非法数据插入或更新，让MySQL数据库提示报错而不是警告，那么用户必须设置参数sql_mode，用来严格审核输入的参数。

ENUM和SET约束

MySQL数据库不支持传统CHECK约束，通过ENUM和SET类型可以解决部分这样的约束需求。

例如：表中有一个性别类型，规定域范围只能是male或female，这种情况下用户可以通过ENUM类型来进行约束。

如果想实现CHECK约束，需要配合设置参数sql_mode

此次对非法的输入值进行约束，只限于对离散树值的约束，对于传统CHECK约束支持的连续值范围约束或更复杂的约束，ENUM和SEET类型无能为力，用户需要通过触发器来实现对于值域的约束

触发器与约束

完整性约束可以使用触发器来实现。

触发器作用：在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。

**创建触发器：**CREATE TRIGGER

只有具备super权限的MySQL数据库用户才能执行这条命令。

最多可以为一个表创建6个触发器。分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。

**BEFORE和AFTER代表触发器发生的时间。**表示在每行操作之前还是之后发生。

外键约束

外键用于保证参照完整性，

可以在CREATE TABLE时就添加外键，也可以在表创建后通过ALTER TABLE来添加。

例如：

被引用的表为父表，引用的表称为子表。

ON DELETE和ON UPDATE表示在对父表进行DELETE和UPDATE操作时，对子表进行的操作。

可以定义的子表操作为：

CASCADE：表示当父表发生DELETE或UPDATE操作时，对相应的子表中的数据也进行DELETE或UPDATE操作。

SET NULL：当父表发生DELETE或UPDATE操作时，相应的子表中数据被更新为NULL值。子表中的相对应列必须允许为NULL值。

NO ACTION：表示当父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。

RESTRICT：父表发生DELETE或UPDATE操作时，抛出错误，不允许这类操作发生。如果定义外键没有指定ON DELETE或ON UPDATE，RESTRICT就是默认的外键设置。

4.7 视图

MySQL中，视图是一个命名的虚表。由SQL查询来定义，可以当作表使用。视图中的数据没有实际的物理存储。

作用：

创建视图的语法：

用户可以通过视图的定义来更新基本表。

可以通过WITH CHECK OPTION来让视图在插入数据不符合条件时报错。

例子：

SHOW TABLES会显示出当前数据库中所有的表

可以搜索表类型为BASE TABLE的表，来查看当前架构下的基表。

查看视图的元数据：通过information_schema架构下的VIEWS表。

物化视图

该视图不是基于基表的虚表，而是根据基表实际存在的实表。可以用于预先计算并保存多表的JOIN和GROUP BY等SQL操作。存储在非易失的存储设备上。

Oracle数据库中物化视图创建方式有两种：

BUILD IMMEDIATE(默认)：创建时生成数据

BUILD DEFERRED：以后根据需要再生成数据

查询重写是指当物化视图的基表进行查询时，数据库会自动判断能否通过查询物化视图来直接得到最终结果。

可以避免聚集或连接等复杂操作。

**物化视图的刷新：**当基表发生DML操作后，物化视图采用何种方式和基表进行同步。刷新模式有两种：

ON DEMAND:物化视图再用户需要时进行刷新。

ON COMMIT：物化视图对基表DML操作提交时进行刷新。

刷新办法：

FAST：增量刷新，只刷新上次刷新以后进行的修改。
COMPLETE：对整个物化视图进行完全的刷新
FORCE：数据库在刷新时回去判断是否可以进行快速刷新
NEVER：不进行任何刷新

MySQL不支持物化视图，可以通过一些机制来进行实现。

若是要实现ON DEMAND的物化视图，只需把表清空，重新导入数据即可。这是COMPLETE的刷新方式，要实现FAST的方式，需要记录上次统计时order_id的位置。

若要实现ON COMMIT的物化视图，需要通过触发器来实现。

4.8 分区表

分区功能不是在存储引擎层完成，因此不是只有InnoDB支持分区。

MySQL在5.1添加分区功能。逻辑上可能只有一张表，但是物理上这张表或索引可能由数10个物理分区组成。每个分区都是独立对象，可以独自处理。

MySQL支持水平分区，并采取局部分区索引（分区中既存放数据又存放索引）。

查看是否启用分区：两种方式

分区带来高可用性。

MySQL支持以下几种分区：

RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区
LIST分区：和RANGE类似，只不过LIST分区面向离散的值。
HASH分区：根据用户自定义表达式的返回值来进行分区，返回值不能是负数。
KEY分区：根据MySQL数据库提供的哈希函数来进行分区

如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。唯一索引时可以允许NULL的，不需要整个唯一索引都是分区列。

如果建表时没有指定主键，唯一索引。则可以指定任何一个列为分区列。

分区类型

**RANGE分区：**最常见的一种分区类型。例子：启用分区之后，表由建立分区的各个分区ibd文件组成。当插入一个不在分区中定义的值时，MySQL会抛出异常。可以添加一个MAXVALUE值的分区，该值可以理解为正无穷。优化器只能对YEAR(),TO_DAYS(),TO_SECONDS(),UNIX_TIMESTAMP()这类函数进行优化选择。
LIST分区：与RANGE非常相似，只不过分区列的值是离散的。例如：注意RANGE分区定义的是VALUES LESS THAN，而LIST分区使用的是VALUES IN。注意：在用INSERT插入多个行数据的过程中遇到分区未定义的值时，MyISAM和InnoDB存储引擎的处理方式不同，MyISAM会将之前的行数据全部插入，而InnoDB存储引擎会将其视为一个事务，没有任何数据插入。
HASH分区：该分区类型目的是将数据均匀分不到预先定义的各个分区中。使用该种分区方法只要基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

方式：在CREAT TABLE语句上添加一个PARTITION BY HASH(expr),其中expr是一个返回一个整数的表达式。以及在后面添加一个PARTITION num，num是一个非负的整数，若不含该字段，则分区数量默认为1。例子：LINEAR HASH分区使用了一个更加复杂的算法来确定新行插入的位置。
**KEY分区：**KEY和HASH类似，只不过使用MySQL数据库所提供的函数进行分区。也可以使用LINEAR
**COLUMNS分区：**之前的分区条件是必须是整型。MySQL5.5支持COLUMNS分区。**可以不使用整形。**RANGE COLUMNS分区可以对多个列的值进行分区。COLUMNS分区支持以下类型：
- 所有整数类型
- 日期类型，DATE和DATETIME
- 字符串类型。如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT不支持。对于日期类型，无需再用YEAR()和TO_DAYS()
  
  例子：
因此，对于RANGE和LIST分区，用户可以使用RANGE COLUMNS 和LIST COLUMNS分区来进行代替。

子分区

也称复合分区，是指在分区的基础上再进行分区。

MySQL允许再RANGE和LIST分区的基础上再进行HASH和KEY的子分区。

例子：（两种创建方式）

子分区所需要注意的问题：

每个子分区数量必须相同
要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区
每个SUBPARTITION子句必须包括子分区的一个名字
子分区的名字必须是唯一的（明确定义时）

分区中的NULL

MySQL数据库允许对NULL值做分区，总是视NULL值小于任何一个非NULL值。

对于RANGE分区，如果向分区插入NULL值，MySQL数据库会将该值放入最左边的分区

在LIST分区下要使用NULL值，必须显式指出哪个分区中放入NULL值，否则报错。

必须LIST中有NULL字段才可以插入。

HASH和KEY分区对于NULL的处理方式和RANGE分区、LIST分区不同，任何分区函数都会将含有NULL的值的记录返回0。

分区性能

数据库应用分为两类：

OLTP（在线事务处理）：如Blog，电子商务，网络游戏
OLAP（在线分析处理）：如数据仓库，数据集市

对于OLAP的应用，分区可以很好提高查询性能。OLAP应用大多需要扫描很大的表，而通过分区则只需要扫描部分相应分区即可。即前面介绍的Partion Pruning。

对于OLTP应用，分区应该谨慎，大部分只需要通过索引返回几条记录即可。而B+数索引需要3次左右磁盘IO，可以很好完成任务，无需分区的帮助。

在表和分区间交换数据

MySQL5.6支持ALTER TABLE…EXCHANGE PARTITION语法，该语句允许分区或子分区中的数据与另一个非分区的表中数据进行交换

使用该语句的条件：

要交换的表和分区表有相同的表结构，表不能含有分区。
非分区表中的数据必须在交换分区的定义内。
被交换的表不能含有外键，或者其他的表含有对该表的外键引用。
需要ALTER、INSERT、CREATE和DROP的权限。
使用该语句不会触发交换表和被交换表的触发器。
AUTO_INCREMENT列将被重置。

5 索引

5.1 InnoDB索引概述

InnoDB支持几种常见索引：

B+树索引
全文索引
自适应哈希索引

自适应哈希索引是指：不能人为干预是否在一张表中生成哈希索引。

B+树索引容易忽略的问题：B+树索引所能找到的只是被查找数据行所在的页，然后数据库通过把页读入内存，再在内存中进行查找。

对于为什么选择B+树，而不选择其他数据结构？

**不选择数组和链表？**当数据量非常大时，索引也非常大，无法一次性加载到内存中。而采用树结构时，每个节点都是一个磁盘页，访问该节点时，只需将对应磁盘页加载到内存中。

**不采用二叉树、二叉排序树、平衡二叉树？**对于二叉树，无法做到有序性。对于二叉排序树，在特殊情况下会退化为链表，导致效率很低。对于平衡二叉树，大量数据情况下，树的高度较高，IO量比较大，并且维护相对麻烦

**不采用B树？**B+树非叶子节点没有表数据，因此每一页可以存储更多行。B+树查找相对稳定，IO次数也随之来说比较稳定。对于范围查找只需要在叶子节点上顺着查询就行，而B树需要在叶子节点和非叶子节点上反复横跳。

5.2 B+树索引

B+树高度通常在2-4层，因此查找某一键值的行记录最多只需要2到4次IO。

聚簇索引

按照每张表的主键构造B+树，叶子节点中存放的即为整张表的行记录数据，聚簇索引的叶子节点称为数据页。查询优化器倾向于采用聚簇索引。

数量：每张表只能拥有一个聚簇索引。

优势：由于定义了数据逻辑顺序，能够很快针对范围值的查询。异地对于主键的排序查找也很快。

存储：聚簇索引的存储并不是物理上连续的，而是逻辑上连续的，否则维护成本会很高。注意两点：一是页通过双向链表连接，页按照主键的顺序排序。另一点是每个页中的记录也是通过双向链表进行维护，物理存储上可以不按照主键存储

例子：

每页最多只能存储两个记录。

辅助索引（二级索引）

叶子节点并不包含行记录的所有数据。每个叶子节点除了包含键值，还包含一个书签，即相应行数据的索引聚集键。

每张表可以有多个辅助索引。

**索引方式：**使用二级索引来寻找数据时，InnoDB会遍历二级索引并通过叶子节点来获得主键索引的主键，然后通过主键索引来找到一个完整的行记录。

B+树索引的分裂

B+树索引页的分裂不是从页的中间记录开始，这样可能会导致页空间的浪费。因为插入是顺序的，分裂后前面那个也不会再有记录被插入，从而导致空间的浪费。

InnoDB的Page Header用以下几个部分来保存插入的顺序信息：

PAGE_LAST_INSERT
PAGE_DIRECTION
PAGE_N_DIRECTION

通过这些信息，InnoDB可以决定是向左还是向右分裂，同时决定分裂点是哪一个。

若插入是随机的，则取页中间记录作为分裂点的记录。

若同一方向进行插入的记录数量为5，目前已经定位到的记录之后还有3条记录，则分裂点的记录定位到记录后的第三条记录，否则分裂点记录就是待插入的记录。

B+树索引的管理

索引创建和删除可以通过两种方法，一种是ALTER TABLE，另一种是CREATE/DROP INDEX。语法如下：

例子：对前面创建的表t，列b为varchar（8000），只索引前面100个字段：

使用之前的表t，加一个对于列（a，c）的联合索引idx_a_c：

若想查看表中索引的信息，需要使用命令SHOW INDEX FROM 表。

例子：

各字段含义：

**Table:**索引所在的表名

**Non_unique:**非唯一的索引，可以看到primary key是0，因为必须是唯一的。

**Key_name：**索引的名字，用户可以通过这个名字来执行DROP INDEX

**Seq_in_index:**索引中该列的位置，如果看联合索引idx_a_c就比较直观了

**Column_name:**索引列的名称

**Collation：**列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶来存放索引数据，而不是对数据进行排序。

Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。

**Sub_part：**是否是列的部分被索引。例如idx_b这个索引，显示100，只对b列的前100字符进行索引，如果索引整个列，则该字段为NULL

**Packed：**关键字如何被压缩，若未压缩则为NULL

**Null：**是否索引的列含有NULL

**Index_type:**索引的类型，InnoDB只支持B+树索引，故显示BTREE

**Comment：**注释

其中，Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是不是实时更新的，因此只是估计值。如果要更新Cardinallity的信息，可以使用ANALYZE TABLE命令。

Cardinality为NULL，某些情况下可能会发生索引建立了但是却没有用到的情况。解决方案为ANALYZE TABLE操作。

Fast Index Creation

MySQL5.5版本之前对于索引添加或删除的这类DDL操作，MySQL数据库的操作过程为：

首先创建临时表，表结构通过命令ALTER TABLE新定义的结构。
把原表中的数据导入到临时表
删除原表
把临时表重命名为原来的表名

InnoDB1.0.x开始支持Fast Index Creation。

对于辅助索引的创建，InnoDB会对创建索引的表添加一个S锁

删除索引操作时，InnoDB只需要更新内部视图，并将索引空间标记为可用，同时删除MySQL内部视图上对该表的索引定义。

临时表创建路径通过参数tmpdir设置

Online Schema Change

Facebook实现的一种在线执行DDL的方式，在线是指在事务创建过程中，可用有读写事务对表进行操作，提高并发性，实现OSC的步骤如下：

init：初始化阶段，对创建的表做一些验证工作，如检测表是否有主键，是否存在触发器或外键。

createCopyTable：创建和原始表结构一样的新表

alterCopyTable：对创建的新表进行ALTER TABLE操作，如添加索引或列

createDeltasTable：创建deltas表，作用是为下一步创建的触发器所使用。之后对原表所有的DML操作都会记录到createDrltasTable中

createTriggers：对原表创建INSERT、UPDATE、DELETE操作的触发器，触发操作产生的记录都写入deltas表。

startSnpshotXact：开始OSC操作的事务

selectTableIntoOutfile：将原表的数据写入到新表。为了减少对原表锁定的时间，通过分片将数据输出到多个外部文件，然后将外部文件数据导入到copy表中。

dropNCIndexs：导入到新表前，删除新表中所有辅助索引。

loadCopyTable：将导出的分片文件导入新表。

replayChanges：将OSC过程中原表DML操作的记录应用到新表中，这些记录被保存到deltas表中。

recreateNCIndexes：重新创建辅助索引。

replayChanges：再次进行DML日志的回放操作，这些日志是在上述创建辅助索引中过程中新产生的日志。

swapTables：将原表和新表交换名字，整个操作需要锁定两张表，不允许新的数据产生。

Online DDL

该功能允许辅助索引创建的同时，还允许其他INSERT、UPDATE、DELETE这类DML操作，极大提高了MySQL在生产环境的可用性。

以下几类DDL操作都可以通过在线的方式进行操作：

辅助索引的创建和删除
改变自增长值
添加或删除外键约束
列的重命名

ALGORITHM制定了创建或删除索引的算法。

COPY表示按照MySQL5.1版本之前的工作模式，创建临时表的方式。

INPLACE表示索引创建或删除操作不需要创建临时表。

LOCK部分为索引创建或删除时对表添加锁的情况，可有的选择为：

NONE：执行索引创建或删除时，对目标表不添加任何锁，事务仍然可以读写，不会阻塞。
SHARE：和FIC类似，执行索引创建和删除时，对目标表加上一个S锁，可执行并发读事务，写事务就会发生等待操作。
EXCLUSIVE：执行索引创建或删除操作时，会对目标加上X锁，读写事务都不能进行，会阻塞所有线程。
DEFAULT：首先判断当前操作是否可以使用NONE模式，若不能则判断SHARE模式，最后判断是否可以使用EXCLUSIVE模式。即通过判断事务最大并发性来判断执行DDL模式

原理：执行创建或删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入一个缓存中，待完成索引创建后在重做应用到表中，**来达到数据一致性。**该缓存由参数innodb_online_alter_log_max_size控制，默认128MB。若空间不够，需要调大该值来获得更大日志缓存空间。或者通过设置ALTER TABLE模式为SHARE，来防止写事务发生。

5.3 Cardinality值

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

对于高选择性（几乎没有重复）的字段，使用B+树索引是适合的。

通过SHOW INDEX的Cardinality即可观察一个列的选择性。该值**代表索引中不重复记录数量的预估值。**实际应用中，Cardinality/n_rows_in_table应该尽可能接近1，若比较小，则应该考虑是否创建该索引。

如何统计Cardinality?

由于各种存储引擎对于B+树索引的实现各不相同，故对于Cardinality的统计是放在存储引擎层实现。

数据库对于Cardinality的统计是通过采样的方法来完成。InnoDB中，Cardinality的统计发生在INSERT和UPDATE中，策略为：

表中1/16的数据发生过变化
stat_modified_counter>2000000000，即如果对表中某一行数据频繁更新，表中数据未实际增加。

InnoDB对8个叶子节点进行采样，过程如下：

取得B+树索引中叶子节点的数量，计为A
随机取得B+树索引中8个叶子节点，统计每个页不同记录的个数，P1…p8
根据采样得到Cardinality预估值：Cardinality=（P1+…+P8）*A/8

故Cardinality是一个预估值，每次计算可能都不同。

可以通过innodb_stats_sample_pages来设置计算Cardinality时每次采样页的数量，默认为8。

innodb_stats_method用来判断如何对待索引中出现的NULL值记录。默认nulls_equal表示将NULL视为相等记录。nulls_unequal将NULL事务不同的记录。nulls_ignored忽略NULL记录。

ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的TABLES和STATISTICS会导致重新计算Cardinality值。

5.4 B+树索引的使用

OLTP应用中，查询只从数据库取得一小部分数据，10条或1条左右，例如：**根据主键值来获得用户信息，根据订单号获得订单详细信息。**建立的B+树索引应该是用以取得少部分数据。

OLAP应用中，需要访问表中大量数据，根据数据来产生查询结果面向分析，如：这个月每个用户的消费情况，销售额同比、环比增长情况。通常会对时间字段进行索引，这时大多数统计需要根据时间维度来进行数据筛选。

联合索引

指的是**对表上多个列进行索引。**创建方法和单个索引创建方法一样，不同之处在于有多个索引列。

例子：

联合索引按照多个列已经做好排序，故后面的列也已经做了排序，故在某些情况下无需额外的排序操作。

覆盖索引

从辅助索引中就可以查询到记录，而不需要查询聚簇索引中的记录，无需回表。

覆盖索引的一个好处是：辅助索引不包含整行记录到所有信息，其大小远小于聚簇索引，故可以大量减少IO操作。

覆盖索引叶子节点所存放的数据为：

对于：

InnoDB并不会选择通过查询聚簇索引来进行统计，因为表上还有辅助索引，而辅助索引远小于聚簇索引，因此选择辅助索引可以减少IO操作。

还有一种特殊情况：

表buy_log有（userid，buy_date）的联合索引，此处只根据buy_date来进行条件查询，一般情况下不能使用该联合索引，但是这句SQL查询是统计操作，可以用到覆盖索引的信息，因此优化器会选择该联合索引。

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

当没有索引覆盖时，下面这条语句会选择聚簇索引：

因为若选择二级索引，则还需要访问聚簇索引来查询数据，即是磁盘上的随机读取，而顺序读速度大于随机读。固态硬盘随机读会相对较快。

索引提示

MySQL支持索引提示（INDEX HINT)，显式告诉优化器使用哪个索引，以下两种情况需要用到INDEX HINT。

**MySQL数据库优化器错误选择了某个索引，导致SQL语句运行很慢。**这种情况比较少。
某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间开销可能大于SQL语句本身。

例子：

对于如下表示例：

执行如下SQL语句：

可以得知其使用了（a，b）这个索引

再通过USE INDEX索引提示：

而并没有使用a这个索引，而式通过表扫描的方式**。USE**

INDEX只是告诉优化器可以选择该索引，而优化器还是会根据自己的判断来进行选择。

通过FORCE INDEX的索引提示：

这时优化器的最终选择和用户指定的索引相同。

Multi-Range Read优化

MySQL5.6开始支持MRR优化。此优化的**目的是减少磁盘的随机访问。并且将随机访问转化为较为顺序的数据访问。**MRR优化适用于range，ref，eq_ref类型的查询。

MRR优化的几个好处：

MRR使数据变得较为顺序。在查询辅助索引时，首先根据得到的查询结果按照主键进行排序，并按照主键顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作。

对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR工作方式如下：

将查询到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际数据文件。

启用MRR优化可以通过参数optimizer_switch中的标记flag来控制。当mrr为on时，表示启用Multi-Range Read优化。mrr_cost_based标记是否通过cost based方式来选择是否启用mrr。

若mrr为on，mrr_cost_based设为off，则总是启用Multi-Range Read优化。

read_rnd_buffer_size用来控制键值缓冲区大小，大于该值时，则执行器对已经缓存的数据根据RowID进行排序。该值默认256K。

Index Condition Pushdown（ICP）优化

ICP是MySQL5.6开始支持的一种**根据索引进行优化的方式。**MySQL会在取出索引的同时，判断是否可以进行WHERE条件的过滤，即将WHERE的部分过滤操作放在了存储引擎层。

**ICP支持range，ref，eq_ref,ref_or_null类型的查询。**当优化器选择ICP优化时，可以在执行计划的列Extra看到Using index condition提示。

InnoDB自适应哈希索引

冲突机制采用链表方式。对于除法散列，m的取值为略大于2被缓冲池页数量的质数。

例子：当前参数innodb_buffer_pool_size的大小为10M，则共有640个16KB的页，对于缓冲池页内存的哈希表来说，需要分配640*2=1280个槽，但是1280不是质数，需要取比1280略大的一个质数，应该是1399。

自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，而对于范围查找无能为力。

通过SHOW ENGIN INNODB STATUS可以看到当前自适应哈希索引的使用状况。

可以通过参数innodb_adaptive_hash_index来禁用或启动此特性。

5.5 全文索引

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

InnoDB1.2.x版本开始支持全文索引。

倒排索引

倒排索引**在辅助表中存储了单词和单词自身在一个或多个文档中所在位置之间的映射。**利用关联数组实现，有两种表现形式：

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

例子：

假设有一个全文检索表t：

DocumentId表示进行全文检索文档的Id，Text表示存储的内容。用户需要对存储的这些文档内容进行全文检索。

对于inverted file index的关联数组，如下所示：

full inverted index的关联数组：

InnoDB全文检索

InnoDB存储引擎从1.2.x版本开始支持全文检索技术，采用full inverted index方式。InnoDB存储引擎中，将**（DocumentID，Position）视为一个”ilist“**。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段。在word上设置有索引。

倒排索引将word存放在一张表中，这个表称为Auxiliary Table（辅助表）。为了提高并行性能，共有6张Auxiliary Table。存放于磁盘中。

在InnoDB存储引擎的全文索引还有另一个概念FTS Index Cache（全文检索索引缓存），用来提高全文检索的性能。

FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。和Insert Buffer做法类似。

可以通过innodb_ft_aux_table来观察倒排索引的Auxiliary Table。

例子：

对于InnoDB存储引擎，总是在事务提交时将分词写入FTS Index Cache，再通过批量更新接入磁盘。虽然InnoDB存储引擎通过一种延时的、批量的写入方式来提高数据库性能，但是这些操作只发生在事务提交时。

参数innodb_ft_cache_size用来控制FTS Index Cache的大小，默认32M。若满了则会同步到磁盘的Auxiliary Table。

FTS_DOC_ID,其类型必须为BIGINT UNSIGNED NOT NULL，并且会自动加上一个名为FTS_DOC_ID_INDEX的Unique Index。必须用该列与word来进行映射。

对于删除操作，事务提交时，不删除磁盘Auxiliary Table中的记录，而只是删除FTS Cache Index中的记录。Auxiliary Table中删除的记录，InnoDB会记录FTS Document ID，并记录在DELETED auxiliary table中。

OPTIMIZE TABLE会将用户已经手工删除的记录从索引中彻底删除。以及一些其他操作，如：Cardinality的重新统计。

可以通过innodb_ft_num_word_optimize来限制每次实际删除的分词数量，该参数默认值为2000。

被删除的表会记录在INNODB_FT_DELETED

被彻底删除的表会记录在INNODB_FT_BEING_DELETED

stopword列表：

表示该列表中的word不需要对其进行索引分词操作。

InnoDB有一张默认的stopword列表，其在information_schema架构下的INNODB_FT_DEFAULT_STOPWORD，默认共36个stopword。

也可以通过参数innodb_ft_server_stopword_table来定义stopword列表

InnoDb存储引擎的全文检索存在以下限制：

每张表只能有一个全文检索的索引
有多列组合而成的全文检索索引列必须使用相同的字符集和排序顺序。
不支持没有单词界定符的语言。

语法：

MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。

各种查询模式：

Natural Language

全文索引通过MATCH函数进行查询，默认采用Natural Language模式。表示查询带有指定word的文档。例子：

由于NATURAL LANGUAGE MODE是默认全文检索查询，故可以写为：

对查询进行分析，type列显示了fulltext，即表示使用全文索引的倒排索引。

若没有创建倒排索引，则MATCH函数会报错：

在WHERE条件中使用MATCH函数，查询返回的结果是根据相关性进行降序排序的

6 锁

MyISAM是表锁设计

InnoDB支持行级锁，提供一致性非锁定读。

6.1 lock和latch

latch是轻量级锁，要求锁定时间短。持续时间长性能会很差。

InnoDB中latch分为mutex（互斥量）和relock（读写锁），无死锁检测机制，通常用于保证并发线程操作临界资源正确性。

lockd1对象是事务，锁定的是数据库中的对象，如表、页、行。lock对象在事务commit和rollback后释放，并且有死锁机制。

**查看latch：**通过命令SHOW ENGINE INNODB MUTEX来进行查看。

查看lock：通过命令SHOW ENGINE INNODB STATUS以及information_schema架构下的表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS来观察锁信息。

6.2 InnoDB的锁

锁的类型

InnoDB有两种类型的行级锁：

共享锁（S Lock）：允许事务读一行数据

排他锁（X Lock）：允许事务删除或更新一行数据

即只有共享锁和共享锁是兼容的

**多粒度锁定：**允许事务在行级锁和表级锁上同时存在。意向锁：为了支持在不同粒度上进行加锁操作，即将锁定的对象分为多个层次。意味着事务希望在更细粒度上进行加锁。

将上锁的对象看成一颗树，那么对下层的对象进行上锁，也就是对最细粒度的对象进行上锁，需要首先对粗粒度的对象进行上锁。

例如想对某页中记录进行上X锁，需要对数据库A、表、页上意向锁IX，最后对记录上X锁。

支持两种意向锁：

意向共享锁（IS Lock）：事务想要获得一张表某几行共享锁
意向排他锁（IX Lock）：事务想要获得一张表中某几行的排他锁

可以使用SHOW ENGINE INNODB STATUS来查看当前锁请求的信息。

InnoDB1.0版本之前，用户只能通过命令SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS来查看当前数据库中锁请求，再判断事务锁情况

1.0版本后添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，用以更简单监控当前事务并分析可能存在的锁问题。

INNODB_TRX由8个字段组成：

查看锁需要访问表INNODB_LOCKS

lock_data并非是一个可信值，若用户进行一个范围查找时，lock_data可能只返回第一行的主键值。若当前资源被锁住了，被锁的页因为InnoDB存储引擎缓冲池的容量，导致该页从缓冲池中被刷出，显示NULL。

通过表INNODB_LOCK_WAITS可以直观查看当前事务的等待，由四个字段组成：

例子：

可以通过INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS一起查看详细信息。

一致性非锁定读

指的是InnoDB通过MVCC的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此取等待行上锁的释放，而是读取一个快照数据。

**实现原理：**通过undo段来实现，而undo用来在事务中回滚数据，快照数据本身每额外开销，读取快照数据不需要上锁，因为没有事务需要对历史数据进行修改操作。

在READ COMMITTED和REPEATABLE READ下，InnoDB存储引擎使用非锁定一致性读。但是对于快照数据定义不同。READ COMMITTED事务隔离下，非一致性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务（自己）开始时的行数据版本。

一致性锁定读

InnoDB对于SELECT语句支持两种一致性锁定读操作：

SELECT …FOR UPDATE：对读取的记录加一个X锁，其他事务不能对该锁定行加任何锁。
SELECT…LOCK IN SHARE MODE：对读取行记录加一个X锁，其他事务可以加S锁，加X锁会被阻塞。

即使SELECR…FOR UPDATE，也可以通过一致性非锁定读进行读取。事务提交后，锁就释放了。

故在使用前面两种方式锁定语句时，加上BEGIN,START TRANSACTION或SET AUTOCOMMIT=0。

自增长和锁

在InnoDB内存结构中，每个包含自增长的表都有一个自增长计数器。对含有自增长计数器的表进行插入操作时，这个计数器会被初始化。

得到计数器值：

插入操作会依据这个自增长的计数器值加1赋予自增长列。该方式叫做：AUTO-INC Locking。为特殊表锁机制，完成自增长值插入SQL后立即释放。

MySQL5.1.22版本提供一种轻量级互斥量自增长实现机制，提供innodb_sutoinc_lock_mode来控制自增长模式，默认值为1。

插入方式：

innodb_sutoinc_lock_mode参数值：

外键和锁

对于一个外键列，如果没有显式对该列加一个索引，InnoDB会自动对其加一个索引，因为可以避免表锁。

对于外键值的插入或更新：

需要先查询父表记录，通过SELECT …LOCK IN SHARE MODE方式，**主动对父表加一个S锁。**注意不使用一致性非锁定读，否则会导致父、子表数据不一致。

6.3 锁的算法

行锁的三种算法

InnoDB有三种行锁的算法：

Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身
Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。

对于一个索引有10，11，13，20这四个值，Next-Key Locking的区间为：

若采用previous-key locking技术，那么可以锁定的区间为

当查询的索引含有唯一属性时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。

InnoDB会对辅助索引的下一个键值加上gap lock。

可以通过以下两种方式来显式关闭Gap Lock：

将事务隔离级别设置为READ COMMITTED
将参数innodb_locks_unsafe_for_binlog设置为1。

上述配置除了外键约束和唯一性检测需要Gap Lock，其余情况使用Record Lock进行锁定。但是这种设置破坏了事务的隔离性。

对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列的一个，故还是选择Next-Key Lock

解决Phantom Problem

在REPEATABLE READ事务隔离级别下，InnoDB采用Next-Key Locking机制来避免Phantom Problem。

此外，还可以通过InnoDB的Next-Key Locking机制在应用层面实现唯一性的检查。例如：

用户通过索引查询一个值，并加上SLock，即使查询的值不在，也会锁定一个范围，若没有返回任何行，新插入值也是唯一的。

6.4 锁问题

锁只会带来三种问题，若防止这三种情况发生，则不会发生异常。

脏读

**脏数据：**事务对缓冲池中行记录到修改，还没有被提交。

注意与脏页的区别。

脏页：缓冲池中已经被修改的页，但是还没有被刷新到磁盘中。

脏页是数据库实例内存和磁盘异步造成的，并不影响数据的一致性。

不可重复读

指的是一个事务内两次读取数据之间，由于第二个事务的修改，两次读到的数据可能是不一样的。

**不可重复读和脏读的区别：**脏读是读到未提交的数据，而不可重复读读到的是已经提交的数据。

一般来说不可重复读的问题是可以接受的，因为读到的是已经提交的数据。

在InnoDB，通过使用Next-Key Lock算法来避免使用不可重复读问题。InnoDB默认事务隔离级别是READ REPEATABLE

丢失更新

一个事务的更新操作会被另一个事务的更新操作锁覆盖，导致数据不一致。

例子：

事务T1将行记录r更新为v1，但是事务T1并没有提交
与此同时，事务T2将行记录r更新为v2，事务T2并未提交
事务T1提交
事务T2提交

任何隔离级别都不会导致数据库理论意义上的丢失更新问题。因为对于DML操作，会对行或其他粗粒度级别的对象加锁，因此上方事务T2并不能对行记录r进行更新操作，其会被阻塞，直到事务T1提交。

但是还有另一个逻辑意义的丢失更新问题：

事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。
事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
User1修改这行记录，更新数据库并提交
User2修改这行记录，更新数据库并提交

如何避免？

在用户读取步骤1和2中都加上一个排他X锁。

6.5 阻塞

指的是有些时刻一个事务中的锁需要等待另一个事务中的锁释放它锁占用的资源。

参数innodb_lock_wait_timeout用来设定等待的时间（默认50秒），该参数时动态的，可以在运行时进行调整。

innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作。（默认OFF，代表不回滚）。该参数是静态的，不可以在启动时进行修改。

超时时，MySQL会抛出一个1205的错误。

默认情况下InnoDB不会回滚超时引发的错误异常，InnoDB大部分情况都不会对异常进行回滚。

6.6 死锁

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

若无外力作用，事务将无法推进下去。

解决死锁的方式：

回滚：不要有等待，将任何等待都转化为回滚，并且事务重新开始。但是会导致并发性能的下降。

超时：当两个事务相互等待时，当一个等待时间超过设置的某一阈值，其中一个事务开始回滚，另一个等待的事务继续进行。参数innodb_lock_wait_timeout用来设置超时时间。

但是若超时的事务所占权重较大，占用较多的undo log，这时采用FIFO就不太合适。

**wait-for graph（等待图）：**普遍采用的方式，这是较为主动的死锁检测方式，InnoDB采用的该方式。需要储存两种信息：

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

若能上述链表构造的图存在回路，则代表存在死锁。

事务作为图中的节点，T1指向T2边的定义是：事务T1等待事务T2所占的资源。

例子：

此处存在回路（t1，t2），故存在死锁。

每个事务请求锁并发生等待时都会判断是否存在回路，若存在死锁，则InnoDB选择回滚undo量最小的事务。

算法实现：通过DFS实现，InnoDB1.2版本前采用递归实现，而1.2版本后采用非递归实现。

死锁发生概率与几点因素有关：

系统中事务数量（n），越多概率越大
每个事务操作数量，越多概率越大
操作数据集合，越小概率越大

死锁案例：

当案例抛出1213这个错误提示，即表示事务发生了死锁。

接下来会话B中的事务会发生回滚，会话A中的事务会得到记录为2的该资源。

InnoDB存储引擎会自动帮助外键添加索引，并且无法人为删除，避免索引。

**还有一种死锁情况：**当前事务持有待插入记录的下一个记录的X锁，但是在等待队列中存在一个S锁请求，则有可能发生死锁。

会话A对记录4持有X锁，但是会话A中插入记录3会导致死锁发生。

由于会话B中请求记录4的S锁而发生等待，但是之前请求的锁对于主键值1，2都已经成功，若在事件5能插入记录，那么会话B在获得记录4持有的S锁后，还需要向后获得记录3的记录，有点不合理，因此InnoDB主动选择了死锁，而回滚的是undo log记录最大的事务，这和AB-BA死锁处理方式不同

6.7 锁升级

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

举例就是：数据库可以把一个表的1000个行升级为一个页锁，或者将当前页锁升级为表锁。

InnoDB不存在该问题。因为是根据每个事务访问的每个页来对锁进行管理。不论一个事务锁住页中一个记录还是多个记录开销都是一样的。

7 事务

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

事务的四大特性（ACID）：

原子性
一致性
隔离性
持久性

锁实现了事务的隔离性。

而本章关注事务的原子性。

7.1 事务简介

四大特性

**对于InnoDB，默认事务级别为READ REPEATABLE，完全满足事物的ACID特性。**而对于Oracle，事务隔离级别为READ COMMITTED，不满足隔离性。

A（Atomicity）原子性：

将事务整个操作过程视作原子操作。要么都做，要么都不做。通过undo log实现。

C（consistency）一致性：

指事务将数据库从一种状态转变为下一种一致的状态。事务开始前和结束后，数据库完整性约束没有被破坏。

例子：表中有一个字段为名字，为唯一约束，在表中姓名不能重复。如果一个事务对姓名字段进行了修改，但是在事务提交或事务操作发生回滚后，表中姓名变得非唯一了，这就破坏了事务一致性要求。

I（isolation）隔离性：

要求每个读写事务的对象对其他事务的操作对象能够相互分离，即事务提交前对其他事务都不可见。通过锁来实现。

D（durability）持久性：

**事务一旦提交，结果就是永久性的。**即使发生宕机等故障，数据库也能恢复。通过redo日志保证

事务分类

可以把事务分为以下几种类型：

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

扁平事务

最简单的一种事务，但是可能是使用最频繁的事务。

**所有操作都处于同一层次。**由BEGIN WORK开始，由COMMIT WORK 或ROLLBACK WORK结束。

带有保存点的扁平事务

**允许在事务执行过程中回滚到同一事务中较早的一个状态。**因为某些事务可能在执行过程中出现的错误并不会导致所有操作都无效，放弃整个事务不合乎要求。

**保存点：**用来通知系统应该记住事务当前的状态，以便发生错误时，事务能回到保存点当时的状态。

保存点用SAVE WORK函数来创建，通知系统记录当前的记录状态。

链事务

保存点事务的一种变种。

带有保存点的扁平事务，当系统发生崩溃时，所有保存点都将消失，因此保存点是易失的，而非持久的。这就意味着恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。

**思想：**提交一个事务时，释放不必要的数据对象，将必要的处理上下文隐式传递给下一个要开始的事务。**提交事务和开始下一个事务合并为一个原子操作。**下一个事务将看到上一个事务的结果。

链事务回滚仅限当前事务，即只能恢复到最近的一个保存点。对于锁，事务在执行COMMIT之后释放当前事务所持有的锁。

嵌套事务

是一个层次结构的框架，由一个顶层事务控制各个层次的事务，顶层事务之下嵌套的事务被称为子事务，控制每一个局部的变换。

Moss对嵌套事务的定义：

嵌套事务由若干事务组成的一棵树，子树既可以是嵌套事务，也可以是扁平事务。
处在叶子节点的事务时扁平事务。
根节点称为顶层事务，其他事务称为子事务。事务前驱称为父事务，下一层称为子事务。
**子事务可以提交也可以回滚，提交并不马上生效，除非父事务已经提交。**任何子事务都在顶层事务提交后才真正提交。
树中任何一个事务回滚都会引起所有子事务回滚，故子事务仅保留ACI特性，不具有D特性。

Moss理论中，只有叶子节点的事务才能访问数据库、发送消息、获取其他类型的资源。而高层事务负责逻辑控制，决定何时调用相关的子业务。

分布式事务

在分布式环境下运行的扁平事务，需要根据数据所在位置访问网络不同节点。

例如通过ATM持卡人从招商银行的粗虚空转账到工商银行的储蓄卡。

InnoDB不支持嵌套事务。

7.2 事务实现

redo通常是物理日志，记录到时页的物理修改操作。

undo是逻辑日志，根据每行记录进行记录。

redo

功能：用于实现事务的持久性。

由两部分组成：

内存中的重做日志缓冲（redo log buffer ），易失
重做日志文件（redo log file），持久。

InnoDB通过Force Log at Commit机制实现事务的持久性，当事务提交时，**必须先将该事务所有日志写入到重做日志文件进行持久化。**日志有redo log和undo log，redo log用于保证事务的持久性。undo log用来帮助事务回滚和MVCC功能。

redo log顺序写，undo log随机读写。

确保每次日志都写入重做日志文件，每次重做日志缓冲写入重做日志文件后，InnoDB需要调用fsync操作，由于重做日志文件打开并没有使用O_DIRECT选项，故重做日志缓冲先写入文件系统缓存。

参数innodb_flush_log_at_trx_commit来控制重做日志刷新到磁盘的策略。默认值为1，代表事务提交时必须调用一次fsync操作。这个操作在Master Thread中完成。还可以设置为0或2，0表示事务提交时不进行写入重做日志操作。2表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统缓冲中，不进行fsync操作。

例子：对于一个插入50万条数据的存储过程所需要的时间：

将该参数设置为0或2会丧失事务的ACID特性。

二进制日志是进行POINT-IN-TIME(PIT)的恢复和主从复制环境的建立

redo log和二进制日志的区别？

redo log在InnoDB存储引擎层产生，二进制日志在MySQL数据库上层产生。

二进制日志记录SQL语句。redo log是记录对于每个页的修改。

二进制日志在事务提交完成后进行写入，redo log在事务进行过程中不断写入。

log block重做日志块

InnoDB，redo log以512字节进行存储，redo log和redo log缓存都是以块的方式保存，称为重做日志块，每块512字节。

重做日志块大小和磁盘扇区大小一样，都是512字节，因此重做日志写入可以保证原子性，不需要doublewrite技术

重做日志块包括：重做日志，日志块头（12字节），日志块尾（8字节）。每个重做日志块实际可以存储512-12-8=492字节。

日志块头由4部分组成：

log buffer由log block组成，在内部log buffer类似一个数组，LOG_BLOCK_HDR_ON用来标记这个数组中的位置，并且是递增并循环使用。

LOG_BLOCK_HDR_DATA_LEN占用2字节，表示log block所占大小。

LOG_BLOCK_FIRST_REC_GROUP：表示log block中第一个日志所在偏移量，占用2字节。

若该值和所占大小相同，则表示log block不包含新日志。

**LOG_BLOCK_CHECKPOINT_NO：表示log block最后被写入时的检查点第四字节的值。**占用4字节。

日志尾只由1个部分组成，其值和LOG_BLOCK_HDR_NO相同，并在log_block_init中被初始化

log group重做日志组

逻辑概念，其中有多个重做日志文件，InnoDB存储引擎实际只有一个log group。

log buffer根据一定规则将内存中的log block刷新到磁盘，这个规则是：

事务提交时
log buffer中有一半内存空间已经被使用
log checkpoint时

**上述信息在每个log group的第一个redo log file中存储。**log group中其余redo log file 仅保留这些空间，但是不保存信息。

重做日志格式

redo_log_type:重做日志的类型

space:表空间的ID

page_no:页的偏移量。

对于redo log body的部分，根据重做日志类型不同，会有不同的存储内容。

LSN

代表日志序列号。InnoDB中LSN占用8字节，并且单调递增。LSN代表含义为：

重做日志写入总量
checkpoint位置
页版本

LSN是重做日志的总量，单位为字节。所有redo log写入的字节。

对于每个页都有一个FIL_PAGE_LSN，记录该页的LSN。重做日志记录的是每个页的日志，因此页中LSN用来判断是否需要进行恢复操作。

可以通过 SHOW ENGINE INNODB STATUS来查看LSN情况。

恢复

InnoDB存储引擎在启动时不管上次数据库运行是否正常关闭，都会尝试进行恢复操作。重做日志是物理日志，比二进制日志这种逻辑日志恢复速度快很多。

重做日志是物理日志，因此是幂等的。

undo

事务有时候需要进行回滚操作，就需要undo。如果用户执行的事务或语句由于某种原因失败了，或者用户用一条ROLLBACK语句请求回滚，就可以使用这些undo信息。

**存放位置：**数据库一个特殊的段中，称为undo段，位于共享表空间中。

undo是逻辑日志，因此比如INSERT 10W条数据，事务会分配新的段，即表空间会增大，回滚后空间并不会收缩。

**作用：**除了回滚操作，undo另一个作用是实现MVCC。例如：用户读取一行记录时，若该记录已经被其他事务锁占用，当前事务可用通过undo读取之前的行版本信息来进行非锁定读。

undo log 会产生redo log，undo log需要持久性保护。

存储方式

rollback segment记录1024个undo log segment，每个undo log segment都进行undo页申请。共享偏移量为5的页（0，5)记录所有rollback segment header所在页。

1.1版本前有一个rollback segment，支持在线事务1024个。1.1之后支持最大128个rollback segment，同时支持在线事务128*1024。

1.2版本开始可以通过参数对rollback segment做进一步处理。

**innodb_undo_directory:**设置rollback segment文件所在路径。.表示当前InnoDB的目录

**innodb_undo_logs:**设置rollback segment个数，默认128

**innodb_undo_tablespaces:**设置构成rollback segment文件数量。

需要注意的是，在undo log segment分配页并写入undo log这个过程中同样需要写入重做日志。事务提交时：

将undo log放入列表中，以供之后的purge操作
判断undo log 所在页是否可以重用，若可以则分配给下一个事务使用。

事务体提交后不能马上删除undo log和undolog所在的页，因为可能还有其他事务通过undo log来得到行记录之前的版本，故事务提交时将undo log放入一个链表中，是否可以最终删除undo log和undo log所在页由purge线程判断。

undo页重用：事务提交时，首先将undo log放入链表中，判断undo页使用空间是否小于3/4，若是则重用。因此undo页存放不同事务的undo log，因此purge操作需要涉及磁盘的离散读取操作，比较缓慢。

undo log格式

InnoDB中，undo log分为：

insert undo log
update undo log

insert undo log是指在insert操作中产生的undo log，因为insert操作记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。

next：记录下一个undo log的位置，通过该字段的字节可用知道一个undo log所占空间字节数。

**type_cmp1:**占用1字节，记录undo类型。对于insert undo log该值总是11.

**undo_no:**记录事务的ID。

**table_id:**记录undo log所对应表对象。

接着就是记录所有主键列和值，在进行rollback时，根据这些值可以定位到具体的记录，然后进行删除。

update undo log是指对delete和update操作产生的undo log。该日志需要提供MVCC机制，因此不能再事务提交时就进行删除，提交时放入undo log链表，等待purge线程进行删除。

next、start、undo_no、table_id和之前insert undo log相同。type_cmpl可能的值如下：

**12 TRX_UNDO_UPD_EXIST_REC：**更新non—delete-mark的记录

**13 TRX_UNDO_UPD_DEL_REC：**将delete的记录标记为not delete

**14 TRX_UNDO_DEL_MARK_REC：**将记录标记为delete

update_vector表示update操作导致发生改变的列。

查看undo信息

INNODB_TRX_ROLLBACK_SEGMENT来查看rollback segment

查看rollback segment所在页：

INNODB_TRX_UNDO记录事务对应undo log

例子：

purge

delete和update操作可能并不直接删除原有数据，而真正删除这种记录的操作被延时到purge操作中完成。

InnoDB有一个history list，根据事务提交的顺序，将undo log进行连接。

执行purge过程中，首从history list顺序照被清理的记录，然后回再undo log所在页中寻找是否存在可以被清理的记录。然后根据history list继续查找。这样可以避免大量随机读取操作，提高purge效率。

innodb_purge_batch_size用来设置每次purge操作需要清理undo page数量。

innodb_max_purge_lag用来控制history list的长度，若长度大于该参数，会延缓DML操作。

延缓算法：

innodb_max_purge_lag_delay用来控制delay的最大毫秒数。

group commit

若事务非只读事务，则每次事务提交时都需要进行一次fsync操作。保证重做日志都已经写入磁盘。

group commit保证一次fsync可以刷新确保多个事务日志被写入文件。

对于InnoDB，事务提交会进行两个阶段操作：

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

开启二进制日志后，group commit会失效。是因为为了保证存储引擎中事务和二进制日志的一致性，二者之间使用了两阶段事务。步骤如下：

当事务提交时InnoDB进行prepare
MySQL上层写入二进制日志
InnoDB将日志写入redo log

修改内存中事务对应信息，将日志写入重做日志缓冲

调用fsync将确保日志从重做日志缓冲写入磁盘。

MySQL内部使用prepare_commit_mutex锁，启用该锁后3步骤的第一步不可以再其他事务执行第二步时执行，从而group commit失效。

7.3 事务控制语句

默认设置下，事务是自动提交的（auto commit），即执行SQL后立马执行COMMIT。

**START TRANSACTION|BEGIN：**显式开启一个事务。

**COMMIT：**提交事务，并使得已对数据库做的所有修改变为永久性的。

**ROLLBACK：**回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

**SAVEPOINT identifier：**运行再事务中创建一个保存点，一个事务中有多个SAVEPOINT。

**ROLLBACK TO[SAVEPOINT]identifier：**把事务回滚到标记点。

**SET TRANSACTION:**设置事务的隔离级别。READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE

存储过程中只能使用START TRANSACTION来开启一个事务，而不能使用BEGIN。

COMMIT和CMMIT WORK都是用于提交事务的，而COMMIT WORK**用来控制事务结束后行为是CHARIN还是RELEASE的。**如果是CHAIN，则事务变为链事务。

completion_type默认为0，此时COMMIT和COMMIT WORK完全等价。若为1，则COMMIT WORK等价于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务。

completion_type为2时，COMMIT WORK等同于COMMIT AND RELEASE，事务提交后会自动断开和服务器的连接。

ROLLBACK TO SAVEPOINT使用时，虽然有ROLLBACK，但是并不是真正结束一个事务，即使执行了ROLLBACK TO SAVEPOINT，之后也需要显式运行COMMIT 或ROLLBACK命令。

7.4 隐式提交的SQL语句

TRUNCATE TABLE和整张表执行DELETE结果是一样的，但是它不能被回滚。

7.5 对于事务操作的统计

InnoDB支持事务，因此InnoDB应用需要再考虑每秒请求数QPS的同时，应该关注每秒事务处理的能力TPS。

计算TPS的方法是：（com_commit+com_rollback）/time

使用这种方法计算的前提是事务都是显式提交的。

即不包含隐式提交回滚（默认autocommit=1）

7.6 事务隔离级别

SQL定义四个隔离级别：

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

InnoDB默认支持REPEATABLE READ，使用Next-Key Lock锁的算法来避免幻读。

可以通过以下命令来设置当前会话或全局的事务隔离级别：

修改默认隔离级别，修改配置文件：

查看当前会话事务隔离级别：

查看全局事务隔离级别：

SERIALIABLE事务隔离级别主要用于InnoDB存储引擎分布式事务。

在READ COMMITTED事务隔离级别下，除了唯一性的约束检查和外键约束检查，InnoDB不会使用gap lock的锁算法。

在该事务隔离级别下，在MySQL5.1中，READ COMMITTED事务默认级别只能在replication二进制日志为ROW的格式下，若二进制日志工作在默认STATEMENT下，则会出现错误。

7.7 分布式事务

InnoDB提供对XA事务的支持，通过XA事务来支持分布式事务。

**分布式事务：允许多个独立的事务资源参与到一个全局的事务当中。**全局事务要求在其中的所有参与的事务要么都提交，要么都回滚。

隔离级别：必须设置为SERIALIZABLE

XA事务：由一个或多个资源管理器，一个事务管理器以及一个应用程序组成。

资源管理器：提供访问事务资源的方法。通常是一个数据库。

事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。

应用程序：定义事务的边界，指定全局事务中的操作。

分布式事务采用两段式提交方式：

所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了。
事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的系欸点后被告知需要回滚。

XA事务的SQL语法如下：

内部XA事务

在存储引擎和插件之间，或者在存储引擎和存储引擎之间。

最常见的XA事务存在于binlog和InnoDB之间。

在事务提交时，先写二进制日志，再写InnoDB存储引擎的redo log。二者必须是原子的。

7.8 不好的事务习惯

在循环中提交

这样会产生大量的事务，写很多次的redo log，所以会导致大量IO。

使用自动提交

自动提交不是一个好的习惯。改变方式：

也可以使用START TRANSACTION ,BEGIN来显式开始一个事务，会自动执行SET AUTOCOMMIT=0。并在COMMIT 或ROLLBACK结束后执行 SET AUTOCOMMIT=1。

使用自动回滚

InnoDB支持定义一个HANDLER来进行自动事务回滚。会不知道发生什么样的错误。建议交给程序来完成。

7.9 长事务

对于执行时间非常长的事务，通常可以转化为小批量的事务来进行处理。当事务发生错误时，只需要回滚一部分数据。然后接着上次已经完成的事务继续进行。并且可以得知完成进度。
identifier：**运行再事务中创建一个保存点，一个事务中有多个SAVEPOINT。

**ROLLBACK TO[SAVEPOINT]identifier：**把事务回滚到标记点。

**SET TRANSACTION:**设置事务的隔离级别。READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE

存储过程中只能使用START TRANSACTION来开启一个事务，而不能使用BEGIN。

COMMIT和CMMIT WORK都是用于提交事务的，而COMMIT WORK**用来控制事务结束后行为是CHARIN还是RELEASE的。**如果是CHAIN，则事务变为链事务。

completion_type默认为0，此时COMMIT和COMMIT WORK完全等价。若为1，则COMMIT WORK等价于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务。

completion_type为2时，COMMIT WORK等同于COMMIT AND RELEASE，事务提交后会自动断开和服务器的连接。

ROLLBACK TO SAVEPOINT使用时，虽然有ROLLBACK，但是并不是真正结束一个事务，即使执行了ROLLBACK TO SAVEPOINT，之后也需要显式运行COMMIT 或ROLLBACK命令。

7.4 隐式提交的SQL语句

[外链图片转存中…(img-809iuYbn-1715738475716)]

TRUNCATE TABLE和整张表执行DELETE结果是一样的，但是它不能被回滚。

7.5 对于事务操作的统计

InnoDB支持事务，因此InnoDB应用需要再考虑每秒请求数QPS的同时，应该关注每秒事务处理的能力TPS。

计算TPS的方法是：（com_commit+com_rollback）/time

使用这种方法计算的前提是事务都是显式提交的。

即不包含隐式提交回滚（默认autocommit=1）

7.6 事务隔离级别

SQL定义四个隔离级别：

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

InnoDB默认支持REPEATABLE READ，使用Next-Key Lock锁的算法来避免幻读。

可以通过以下命令来设置当前会话或全局的事务隔离级别：

[外链图片转存中…(img-ZSk24wmf-1715738475716)]

修改默认隔离级别，修改配置文件：[外链图片转存中…(img-jkHe2xkM-1715738475716)]

查看当前会话事务隔离级别：

[外链图片转存中…(img-YiZlmMsv-1715738475717)]

查看全局事务隔离级别：

[外链图片转存中…(img-VypiJVB0-1715738475717)]

SERIALIABLE事务隔离级别主要用于InnoDB存储引擎分布式事务。

在READ COMMITTED事务隔离级别下，除了唯一性的约束检查和外键约束检查，InnoDB不会使用gap lock的锁算法。

7.7 分布式事务

InnoDB提供对XA事务的支持，通过XA事务来支持分布式事务。

**分布式事务：允许多个独立的事务资源参与到一个全局的事务当中。**全局事务要求在其中的所有参与的事务要么都提交，要么都回滚。

隔离级别：必须设置为SERIALIZABLE

XA事务：由一个或多个资源管理器，一个事务管理器以及一个应用程序组成。

资源管理器：提供访问事务资源的方法。通常是一个数据库。

事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。

应用程序：定义事务的边界，指定全局事务中的操作。[外链图片转存中…(img-KSC4z9ob-1715738475717)]

分布式事务采用两段式提交方式：

所有参与全局事务的节点都开始准备，告诉事务管理器它们准备好提交了。
事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的系欸点后被告知需要回滚。

XA事务的SQL语法如下：

[外链图片转存中…(img-qTDhc2Gz-1715738475717)]

[外链图片转存中…(img-10j6Yr8F-1715738475717)]

内部XA事务

在存储引擎和插件之间，或者在存储引擎和存储引擎之间。

最常见的XA事务存在于binlog和InnoDB之间。

在事务提交时，先写二进制日志，再写InnoDB存储引擎的redo log。二者必须是原子的。

[外链图片转存中…(img-AQxCUVGi-1715738475717)]

7.8 不好的事务习惯

在循环中提交

这样会产生大量的事务，写很多次的redo log，所以会导致大量IO。

使用自动提交

自动提交不是一个好的习惯。改变方式：

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

也可以使用START TRANSACTION ,BEGIN来显式开始一个事务，会自动执行SET AUTOCOMMIT=0。并在COMMIT 或ROLLBACK结束后执行 SET AUTOCOMMIT=1。

使用自动回滚

InnoDB支持定义一个HANDLER来进行自动事务回滚。会不知道发生什么样的错误。建议交给程序来完成。

7.9 长事务

对于执行时间非常长的事务，通常可以转化为小批量的事务来进行处理。当事务发生错误时，只需要回滚一部分数据。然后接着上次已经完成的事务继续进行。并且可以得知完成进度。

土豆与红薯

关注

14
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
MySQL技术内幕 InnoDB存储引擎（学习笔记总结）

InnoDB是事务安全的存储引擎。是OLTP应用中核心表首选存储引擎。特点：**行锁设计、MVCC、外键、一致性非锁定读。**被设计用来最有效使用内存和CPU。事实证明InnoDB是一个高性能、高可用、高可扩展性的存储引擎。MySQL数据的存储是根据表进行的，每个表都有与之对应的文件。无论采用何种存储引擎，MySQL都有一个以.frm为后缀名的文件，该文件记录了该表的表结构定义。
复制链接

扫一扫