本文详细探讨了InnoDB和MyISAM存储引擎在MySQL中的优缺点，提供了InnoDB引擎的深入解析，包括事务处理、ACID特性、数据结构和存储管理。针对Zabbix监控系统架构优化案例，展示了从存储引擎升级、表空间管理和参数调整等方面的实际操作。
目录
存储引擎概述
存储引擎简介
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。
这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎。存储引擎就相当于MYSQL中的文件系统。
存储引擎功能
- 数据读写
- 数据安全与一致性
- 提高性能
- 热备份
- 自动故障恢复
- 高可用支持等
存储引擎类型
类型介绍
MySQL数据库存储引擎分类
- InnoDB
- MyISAM
- MRG_MYISAM
- CSV
- BLACKHOLE
- MEMORY
- PERFORMANCE_SCHEMA
- ARCHIVE
- FEDERATED
MySQL分支产品的存储引擎介绍
- PerconaDB:默认是XtraDB（InnoDB改写版）
- MariaDB:默认是InnoDB
其他的存储引擎支持
- TokuDB：insert数据比Innodb快得多，数据压缩比比InnoDB高
- RocksDB
- MyRocks
以上三种存储引擎的共同点：压缩比较高,数据插入性能极高。现在很多的NewSQL,使用比较多的功能特性。
类型查看
show engines; 存储引擎是作用在表上的，也就意味着，不同的表可以有不同的存储引擎类型。
查看MySQL支持的存储引擎：
mysql [(none)]>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
查看存储引擎为InnoDB的表：
mysql [(none)]>SELECT table_name,engine from information_schema.tables where engine='InnoDB';
Zabbix监控系统架构整改案例
环境：Zabbix 3.2 + Centos 7.3 + MariaDB 5.5 InnoDB引擎
Zabbix系统监控2000多个节点服务
现象：每隔一段时间zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题：zabbix 版本过低；
数据库版本（5.5版本使用共享表空间，数据混乱）；
zabbix数据库500G,存在一个文件里ibdata1，手工删除1个月之前的数据，空间不释放。
优化建议：zabbix升级更高版本(为了升级数据库版本);
数据库版本升级到 percona 5.7 + 版本 mariadb 10.+(原生态支持TokuDB);
存储引擎替换为tokudb（TokuDB引擎insert数据比Innodb快的多，数据压缩比要Innodb高）;
监控数据按月份进行切割，为了能够truncate每个分区表,立即释放空间(可二次开发:zabbix 数据保留机制功能重写,数据库分表);
关闭binlog（减少无关日志的记录）和双1（刷新磁盘的策略）;
参数调整（安全性参数关闭,提高性能）;
优化结果：监控状态良好
InnoDB和MyISAM存储引擎替换案例
环境: centos 5.8, MySQL 5.0版本, MyISAM存储引擎, 网站业务(LNMP), 数据量50G左右
现象问题: 业务压力大的时候,非常卡;
经历过宕机,会有部分数据丢失
问题分析: MyISAM存储引擎表级锁,在高并发时,会有很高锁等待；
MyISAM存储引擎不支持事务,在断电时,会有可能丢失数据.
职责：监控锁的情况:有很多的表锁等待；
存储引擎查看:所有表默认是MyISAM.
解决方案: 升级MySQL 5.6.10版本；
迁移所有表到新环境；
开启双1安全参数.
InnoDB存储引擎了解
InnoDB引擎介绍
InnoDB存储引擎是MySQL 5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
| 功能 | 支持 |
|---|---|
| 存储限制 | 64TB |
| MVCC | 是 |
| B树索引 | 是 |
| 群集索引 | 是 |
| 压缩数据 | 是 |
| 加密数据 | 是 |
| 查询高速缓存 | 是 |
| 事务 | 是 |
| 锁定粒度 | 行 |
| 外键 | 是 |
| 文件格式管理 | 是 |
| 多个缓冲区池 | 是 |
| 更改缓冲 | 是 |
| 索引高速缓存 | 是 |
| 数据高速缓存 | 是 |
| 自适应散列索引 | 是 |
| 复制 | 是 |
| 更新数据字典 | 是 |
| 地理空间数据类型 | 是 |
| 地理空间索引 | 否 |
| 全文搜索索引 | 是 |
| 群集数据库 | 否 |
| 备份和恢复 | 是 |
| 快速索引创建 | 是 |
| PERFORMANCE_SCHEMA | 是 |
| 自动故障恢复 | 是 |
InnoDB引擎优点
- InnoDB支持事务（Transaction），遵从ACID ，MyISAM不支持事务；
- InnoDB支持行级锁(Row-level Lock)，MyISAM支持表级锁；
- InnoDB支持热备份(Hot Backup)，MyISAM支持温备份；
- InnoDB支持ACSR（Auto Crash Safey Recovery，自动故障恢复），MyISAM不支持；
- InnoDB支持MVCC（Multi-Version Concurrency Control多版本并发控制）
- InnoDB支持外键（保证多表的数据一致性），MyISAM不支持；
- InnoDB支持 Replication: Group Commit , GTID (Global Transaction ID) ,多线程(Multi-Threads-SQL )
InnoDB引擎查看
SELECT确认会话存储引擎
查看默认存储引擎（建立表若不指定引擎，则默认为默认存储引擎）
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是建议统一为innodb。
SELECT @@default_storage_engine;
mysql [(none)]>SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
如何修改默认存储引擎？
会话级别修改：
set default_storage_engine=myisam;
全局级别修改(仅影响新会话，重启之后,所有参数均失效)：
set global default_storage_engine=myisam;
如果要永久生效，写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
确认表的存储引擎
SHOW
SHOW CREATE TABLE table_name\G;
SHOW TABLE STATUS LIKE ‘CountryLanguage’\G
INFORMATION_SCHEMA
select table_schema,table_name ,engine from information_schema.tables where table_schema not in (‘sys’,‘mysql’,‘information_schema’,‘performance_schema’);
修改表的存储引擎
alter table table_name engine innodb;
注意：此命令经常用于进行innodb表的碎片整理
案例：碎片处理
删除数据必然会在数据文件中造成不连续的空白空间，而当再次插入数据时，这些空白空间则会被利用起来，于是造成了数据的存储位置不连续，以及物理存储顺序与理论上的排序顺序不同，这种是数据碎片。
Innodb存储引擎处理碎片方法
ALTER TABLE tablename ENGINE=InnoDB
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法: 过去——将数据逻辑导出,手工drop表,然后导入进去
现在——对表进行按月进行分表(partition,中间件)
业务替换为truncate方式（无碎片产生）
扩展：如何批量修改？
需求：将zabbix库中的所有表,innodb替换为tokudb。生成替代SQL语句：
select concat("alter table zabbix.",table_name," engine tokudb;")
from information_schema.tables
where table_schema='zabbix'
into outfile '/tmp/tokudb.sql';
InnoDB记录存储结构和数据页结构
InnoDB记录存储结构
InnoDB是一个将表中的数据存储到磁盘上的存储引擎，但是真正处理数据的过程发生在内存中，所有需要处理数据时需要将磁盘中的数据加载到内存中。如果是处理写入或修改请求，还需要把内存中的内容刷新到磁盘上。InnoDB将数据分为若干个页，以页作为磁盘和内存之间交互的基本单位。页的大小一般为16KB，因此一般情况下，一次最少从磁盘中读取16KB的内容到内存，一次最少把内存中的16KB内容刷新到磁盘中。
root@localhost [(none)] > show variables like "innodb_page_size"; # 该变量只能在第一次初始化MySQL数据目录时指定
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.48 sec)
行格式类型
对表插入数据都是以记录的形式进行的，这些记录在磁盘上的存放形式也被称为行格式或记录格式。InnoDB设计者设计了4种不同类型的行格式，分别是：
- COMPACT
- REDUNDANT
- DYNAMIC
- COMPRESSED
REDUNDANT是一种比较原始的行格式，它是非紧凑的。而COMPACT、DYNAMIC、COMPRESSED是较新的行格式，它们是紧凑的（占用的存储空间少）。
DYNAMIC和COMPRESSED与COMPACT的区别仅在于处理溢出列的时候，它们不会在记录真实数据处存储该溢出列真实数据的前768字节，而是把该列的所有真实数据存储到溢出列中，只在记录真实数据处存储20字节大小指向溢出页的地址。
COMPRESSED行格式不同于DYNAMIC的一点是，COMPRESSED行格式会采用压缩算法对页面进行压缩，以节省空间。
MySQL 5.7的默认行格式是DYNAMIC。
指定行格式
CREATE TABLE table_name （列的信息）ROW_FORMAT=行格式名称；
ALTER TABLE table_name ROW_FORMAT=行格式名称；
COMPACT行格式
记录的额外信息
变长字段长度列表
MySQL支持VARCHAR等变长数据类型，因为这些变长数据类型中存储多少字节是不固定的，因此在存储真实数据的时候需要顺便将这些数据占用的字节数也存起来。变长数据类型在MySQL中包含两部分：
- 真正的数据内容
- 该数据占用的字节数
注意：如果表中所有的列都不是变长的数据类型或者所有列的值都是NULL的话（变长字段长度列表只存储值为非NULL的列的内容长度），就不需要有变长字段长度列表。
变长字段占用字节存放顺序
在COMPACT行格式中，所有变长字段的真实数据占用的字节数都存放在记录的开头位置，各变长字段的真实数据占用的字节数按照列的顺序逆序存放。
变长字段占用字节在列表中使用1字节还是2字节？
InnoDB对于变长字段的真实数据占用的字节数使用1字节还是2字节来表示有一套自己的规则：
- W：某字符集最多需要W个字节来表示一个字符；
- M：对于变长类型VARCHAR(M)来说，这种类型最多存储M个字符（注意是字符不是字节），则这种类型最多占用的字节数就是 W x M；
- L：该变长字段实际存储的字符串占用的字节数为L。
若 W x M <= 255，则使用1字节表示真实数据占用的字节数；
若 W x M > 255，有以下两种情况：
- L <= 127，则用1字节来表示真实数据占用的字节数；
- L > 127，则用2字节来表示真实数据占用的字节数.
注意：InnoDB在读取记录的变长字段长度列表时先查看表结构。
CHAR(M)的存储格式
对于CHAR(M)类型来说，当列采用的是定长编码的字符集时，该列占用的字节数不会被加到变长字段长度列表；如果采用的是变长编码的字符集时，该列占用的字节数就会被加到变长字段长度列表。
但是CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)就没有这个要求。
NULL值列表
COMPACT行格式将一条记录为NULL的列统一管理起来，存储到NULL值列表，在记录的真实数据处就不再冗余存储，从而节省了存储空间。它的存储过程如下：
- 首先统计表中允许存储NULL的列有哪些（排除主键列和使用NOT NULL修饰的列）；
- 如果表中没有允许存储NULL的列，NULL值列表就不存在；否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：
- 二进制位的值为1时，代表该列的值为NULL；
- 二进制位的值为0时，代表该列的值不为NULL。
- MySQL规定NULL值列表必须用整数个字节的位表示（8的倍数），如果使用的二进制位个数不足8的倍数，则在字节高位补0。
记录头信息
记录头信息由固定的5字节组成，用于描述记录的一些属性。5字节也就是40个二进制位，不同位代表不同的意思。记录头信息的前4位也被称为info bit。
| 名称 | 大小（位） | 描述 |
|---|---|---|
| 预留位1 | 1 | 未使用 |
| 预留位2 | 1 | 未使用 |
| deleted_flag | 1 | 标记该记录是否被删除 |
| min_rec_flag | 1 | B+树的每层非叶子节点中最小的目录项记录都会添加该标记 |
| n_owned | 4 | 一个页面中的记录被分为若干个组，每个组的老大哥记录的n_owned值为该组的所有记录条数，该组其余记录的n_owned为0 |
| heap_no | 13 | 当前记录在页面堆中的相对位置 |
| record_type | 3 | 当前记录的类型，0表示普通记录，1表示B+树非叶子节点的目录项记录，2表示Infimum记录，3表示Supremum记录 |
| next_record | 16 | 下一条记录的相对位置 |
记录的真实数据
MySQL会为每个记录默认的添加一些隐藏列：
| 列名 | 是否必需 | 占用空间 | 描述 |
|---|---|---|---|
| DB_ROW_ID | 否 | 6字节 | 行ID，唯一标识一条记录 |
| DB_TRX_ID | 是 | 6字节 | 事务ID |
| DB_ROLL_POINTER | 是 | 7字节 | 回滚指针 |
InnoDB的主键生成策略：
- 优先使用用户自定义的主键作为主键；
- 如果用户没有定义主键，则选取一个不允许存储NULL的UNIQUE键作为主键；
- 如果没有定义不允许存储NULL的UNIQUE键作为主键，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
InnoDB数据页结构
页是InnoDB管理存储空间的基本单位，一个页的大小一般为16KB。InnoDB为了不同的目的设计了多种不同类型的页，比如存放表空间头部信息的页、存放Change Buffer的页、存放INODE信息的页、存放undo日志的页等。存放表中记录的页一般称之为INDEX索引页。
Infimum + Supermum + User Records
用户存储的记录会按照指定的行格式存储到User Records部分。一开始生成页的时候没有User Records部分，每插入一条记录就会从Free Space部分申请一个记录大小的空间并将其划到User Records部分，当Free Spce部分的空间全部被User Records部分代替后，就意味着该页用完了。
详解记录头信息
| 名称 | 大小（位） | 描述 |
|---|---|---|
| 预留位1 | 1 | 未使用 |
| 预留位2 | 1 | 未使用 |
| deleted_flag | 1 | 标记该记录是否被删除 |
| min_rec_flag | 1 | B+树的每层非叶子节点中最小的目录项记录都会添加该标记 |
| n_owned | 4 | 一个页面中的记录被分为若干个组，每个组的老大哥记录的n_owned值为该组的所有记录条数，该组其余记录的n_owned为0 |
| heap_no | 13 | 当前记录在页面堆中的相对位置 |
| record_type | 3 | 当前记录的类型，0表示普通记录，1表示B+树非叶子节点的目录项记录，2表示Infimum记录，3表示Supremum记录 |
| next_record | 16 | 下一条记录的相对位置 |
-
deleted_flag：该属性用来标记当前记录是否被删除，占用1比特，值为0时表示记录没有被删除，值为1时表示记录被删除了。因此当一条记录被删除时，它其实还在真实的磁盘上。
之所以不移除删除的数据，是因为移除它们之后，还需要在磁盘上重新排列其他记录，会造成一定的性能消耗，而只打一个删除标记便可以避免性能消耗。
被删除的记录会组成一个垃圾链表，记录在这个链表中占用的空间称之为可重用空间，之后若有新纪录插入到表中，它们就可能覆盖掉被删除的这些记录占用的存储空间。
注意：将deleted_flag属性设置为1和将被删除的记录加入到垃圾链表属于两个阶段。 -
min_rec_flag：B+树每层非叶子节点中的最小的目录项记录都会添加该标记。
-
n_owned：一个页面中的记录被分为若干个组，每个组的老大哥记录的n_owned值为该组的所有记录条数，该组其余记录的n_owned为0（Page directory）
-
heap_no：向表中插入的记录本质上来说都是放在数据页的User Records部分，这些记录一条挨着一条紧密排列，称这种结构为堆(heap)。为了方便管理堆，将一条记录在堆中的相对位置称为heap_no。在页面前面的heap_no相对较小，在页面后面的heap_no相对较大，每新申请一条记录的存储空间时，该条记录比物理位置在它前面的那条记录的heap_no的值大1。
注意：堆中记录的heap_no值在分配之后就不会再发生变动，即使之后删除了堆中的某条记录，被删除的记录的heap_no也不会发生变动。其中heap_no值为0和1的记录分别被Infimum记录和Supremum记录占据。
-
Infimum记录：页面中的最小记录
-
Supremum记录：页面中的最大记录
对于一条完整的记录来说，比较记录的大小就是比较主键的大小。虽然Infimum记录和Supremum记录没有主键值，但是规定就是任何用户记录都比Infimum记录大，任何用户记录都比Supremum记录小。
-
-
record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点的目录项记录，2表示Infimum记录，3表示Supremum记录。
-
next_record：表示从当前记录的真实数据到下一条记录的真实数据的距离。若该属性为正，则表示当前记录的下一条记录在当前记录的后面；若该属性为负，则表示当前记录的下一条记录在当前记录的前面；由此可以看出，记录按照主键从小到大的顺序形成了一个单向链表。
如infimum的next_record为28，则表示从infimum记录的真实数据的地址向后找28字节便是下一条记录的真实数据。
supremum的next_record为0，表示supremum记录后没有下一条记录了。删除一条记录也会改变链表，被删除的记录next_record标记为0。注意：下一条记录指的并不是插入顺序中的下一条记录，而是按照主键值由小到大的顺序排列的下一条记录；且规定Infimum记录是主键值最小的记录，supremum记录是主键值最大的记录。
注意：无论如何对页中的记录进行增删改操作，InnoDB始终会维护记录的一个单向链表，链表中的各个节点是按照主键值由小到大的顺序链接起来的。问：为什么变长字段长度列表和NULL值列表都是逆序排列的？
答：因为next_record指针指向的是一条记录的记录头信息和真实数据之间的位置，向左读取就是记录头信息，向右读取就是真实数据。变长字段长度列表和NULL值列表都是逆序排列可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近，这可能会提高缓存的命中率。
Page Directory
已知记录在页中是按照主键值由小到大的顺序串联成一个单向链表，如果想要根据主键值查找页中的某条记录，需要从Infimum记录开始，沿着单向链表一直往后找。在页内记录较少时，这种方法没什么问题，但是页内若有很多条记录，遍历操作就会造成性能损耗。而后设计出了页目录：
- 将所有正常的记录（包括Infimum和Supremum记录，但不包括已经移除到垃圾链表的记录）划分为几个组；
- 每个组的最后一条记录（组内最大的记录）作为老大哥，组内其余的记录作为小老弟，老大哥的记录头信息的n_owned属性会表示该组内一共多少条记录；
- 将每个组中最后一条记录在页面中的地址偏移量（该记录的真实数据与页面中第0个字节之间的距离）单独提取出来，按顺序存储到靠近页尾部的地方，这个地方就称之为页目录（Page Directory）。页目录中的这些地址偏移量称之为槽（Slot），每个槽占用2个字节，页目录就是由多个槽组成的。
划分分组的依据
Infimum记录所在的分组只能有1条记录，Supremum记录所在的分组拥有的记录条数只能在1-8条之间，剩下的分组记录的条数范围只能在4-8之间。因此给记录进行分组是按照下面步骤进行的：
- 在初始情况下，一个数据页中只有Infimum和Supremum两条记录，它们分别属于两个分组。页目录也只有两个槽，分别代表Infimum和Supremum记录在页面中的地址偏移量；
- 之后每插入一条记录，都会从页目录中找到对应记录的主键值比待插入记录的主键值大并且差值最小的槽（从本质说，槽是一个组内最大的那条记录在页面中的地址偏移量，通过槽可以快速找到对应的记录的主键值）。然后把该槽对应的记录的n_owned加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
- 当一个组中的记录数等于8后，再插入一条记录，会将组中的记录拆分成两个组，其中一个组中4条记录，一个组中5条记录。该拆分过程会在页目录中新增一个槽，记录这个新增分组中最大的那条记录的偏移量。
Page directory查找方法
- 通过二分法确定该记录所在分组对应的槽，然后找到该槽所在分组中主键值最小的那条记录；
- 通过记录的next_record属性遍历该槽所在的组中的各个记录。
Page Header
InnoDB定义了Page Header来记录数据中记录的状态信息，比如数据页中存储了多少条记录、Free Space在页面中的地址偏移量、页目录中存储了多少个槽等。Page Header占用固定的56字节，专门用来存储各种状态信息。
| 状态名称 | 占用空间大小 | 描述 |
|---|---|---|
| PAGE_N_DIR_SLOTS | 2字节 | 在页目录中的槽数量 |
| PAGE_HEAP_TOP | 2字节 | 还未使用的空间最小地址 |
| PAGE_N_HEAP | 2字节 | 第一位表示本记录是否是紧凑型的记录，剩余的十五位表示本页的堆中记录的数量（包括Infimum和Supremum及被删除的记录） |
| PAGE_FREE | 2字节 | 各个已删除记录通过next_record组成单向链表，这个单向链表中的记录所占用的存储空间可以被重新利用：PAGE_FREE表示该链表头节点对应记录在页面中的偏移量 |
| PAGE_GARBAGE | 2字节 | 已删除记录占用的字节数 |
| PAGE_LAST_INSERT | 2字节 | 最后插入记录的位置 |
| PAGE_DIRECTION | 2字节 | 记录插入的方向 |
| PAGE_N_DIRECTION | 2字节 | 一个方向连续插入的记录数量 |
| PAGE_N_RECS | 2字节 | 该页中用户记录的数量（不包括Infimum和Supremum及被删除的记录） |
| PAGE_MAX_TRX_ID | 8字节 | 修改当前页的最大事务id，该值仅在二级索引页面中定义 |
| PAGE_LEVEL | 2字节 | 当前页在B+树种所处的层级 |
| PAGE_INDEX_ID | 8字节 | 索引ID，表示当前页属于哪个索引 |
| PAGE_BTR_SEG_LEAF | 10字节 | B+树叶子节点段的头部信息，仅在B+树的根页面中定义 |
| PAGE_BTR_SEG_TOP | 10字节 | B+树非叶子节点段的头部信息，仅在B+树的根页面中定义 |
File Header
File Header通用于各种类型的页，它描述了一些通用于各种页的信息。File Header部分占用固定的38字节。
| 状态名称 | 占用空间大小 | 描述 |
|---|---|---|
| FIL_PAGE_SPACE_OR_CHKSUM | 4字节 | 页的校验和 |
| FIL_PAGE_OFFSET | 4字节 | 页号 |
| FIL_PAGE_PREV | 4字节 | 上一个页的页号 |
| FIL_PAGE_NEXT | 4字节 | 下一个页的页号 |
| FIL_PAGE_LSN | 8字节 | 页面被最后修改时的LSN值（Log Sequence Number日志序列号） |
| FIL_PAGE_TYPE | 2字节 | 该页的类型 |
| FIL_PAGE_FILE_FLUSH_LSN | 8字节 | 仅在系统表空间的第一个页中定义，代表文件至少被刷新到了对应的LSN值 |
| FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID | 4字节 | 页属于哪个表空间 |
- FIL_PAGE_SPACE_OR_CHKSUM：校验和是就是对于一个很长的字节串来说，通过某种算法计算出一个比较短的值来代表这个很长的字节串，这个比较短的值就是校验和。
- FIL_PAGE_OFFSET：每个页都有一个单独的页号，InnoDB通过页号来唯一定位一个页。
- FIL_PAGE_TYPE：InnoDB为了不同的目的把页分为不同的类型，页的类型如下所示：
| 类型名称 | 描述 |
|---|---|
| FIL_PAGE_TYPE_ALLOCATED | 最新分配还未使用 |
| FIL_PAGE_UNDO_LOG | undo日志页 |
| FIL_PAGE_INODE | 存储段的信息 |
| FIL_PAGE_IBUF_FREE_LIST | Change Buffer 空闲列表 |
| FIL_PAGE_IBUF_BITMAP | Change Buffer的一些属性 |
| FIL_PAGE_TYPE_SYS | 存储一些系统数据 |
| FIL_PAGE_TYPE_TRX_SYS | 事务系统数据 |
| FIL_PAGE_TYPE_FSP_HDR | 表空间头部信息 |
| FIL_PAGE_TYPE_XDES | 存储区的一些属性 |
| FIL_PAGE_TYPE_BLOB | 溢出页 |
| FIL_PAGE_INDEX | 索引页（数据页） |
File Trailer
为了检测一个页是否完整（避免刷新时只刷新了一半），在每个页的尾部加了一个FIle Trailer。File Trailer由8字节组成，可以分成2个小部分。
- 前4个字节代表页的校验和。这个部分是和File Header中的校验和相对应的。每当一个页面在内存中修改了，在刷新之前就要把页面的校验和算出来，因为File Header在页面的前边，所以File Header的校验和会被首先刷新到磁盘，当完全写完后，校验和也会被写到页的尾部。如果页面刷新成功，则页首和页尾的校验和应该是一致的。如果刷新了一半的时候发生了如断电的情况，那么File Header中的校验和就代表着已经修改过的页，而File Trailer中的校验和代表着原先的页，二者不同则意味着刷新期间发生了错误。
- 后4个字节代表页面被最后修改时对应的LSN的后4字节，正常情况下应该与File Header部分的FIL_PAGE_LSN的后4字节相同。这个部分也用于校验页的完整性。
在将页从内存刷新到磁盘时，为了保证页的完整性，页首和页尾都会存储页中数据的校验和，以及页面最后修改时对应的LSN值（页尾只会存储LSN值的后4字节）。如果页首和页尾的校验和以及LSN值校验不成功，说明刷新期间出了问题。
InnoDB引擎物理存储结构(数据目录)
数据库与文件系统
InnoDB、MyISAM存储引擎都是将表存储在磁盘上，而操作系统又是使用文件系统来管理磁盘，因此像InnoDB、MyISAM这样的存储引擎都是把数据存储在文件系统上。也正因为MySQL的数据都是存储在文件系统中，因此也会受到文件系统的一些限制，如：
- 每个数据库都对应数据目录下的一个与数据库同名的子目录，每个表都会在数据目录的子目录下产生与表名同名的.frm文件，因此数据库名称与表名称不得超过文件系统所允许的最大长度；
- 特殊字符：为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况，MySQL会把数据库名和表名中所有除数字和字母以外的任何字符在文件名中映射为@+编码值的形式，并将其作为文件名；
- 文件长度受文件系统最大长度的限制。对于InnoDB的独立表空间来说，每个表的数据会被存储到一个与表名同名的.ibd文件中；对于MyISAM存储引擎来说，数据和索引分别存到与表同名的.MYD和.MYI文件中。这些文件随着记录的增多而增大，它们会受到文件系统支持的最大文件大小的限制。
MySQL数据目录结构
数据库在文件系统中的表示
每个数据库都对应数据目录下的一个子目录，每当使用"CREATE DATABASE "创建一个数据库的时候，MySQL会做两件事：
- 在数据目录下创建一个和数据库同名的子目录；
- 在与该数据库同名的子目录下创建一个名为db.opt的文件，这个文件包含了该数据库的一些属性。
[root@localhost ~]# ll /data/mysql/
total 122936
-rw-r-----. 1 mysql mysql 56 Sep 23 12:17 auto.cnf
-rw-r-----. 1 mysql mysql 312 Sep 28 21:53 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 29 09:17 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 29 09:17 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 23 12:17 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 29 09:17 ibtmp1
-rw-r-----. 1 mysql mysql 15374 Sep 23 15:55 localhost.localdomain.err
-rw-r-----. 1 mysql mysql 5 Sep 29 09:17 localhost.pid
drwxr-x---. 2 mysql mysql 4096 Sep 23 12:17 mysql
drwxr-x---. 2 mysql mysql 8192 Sep 23 12:17 performance_schema
drwxr-x---. 2 mysql mysql 60 Sep 25 15:51 school
drwxr-x---. 2 mysql mysql 8192 Sep 23 12:17 sys
MySQL系统数据库
- mysql：存储了MySQL用户和权限信息，一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、一些帮助信息和时区信息等。
- information_schema：存储MySQL服务器维护的其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息不是真实的用户数据，而是一些描述性信息，可称之为元数据。
- performance_schema：存储MySQL在运行过程中的一些状态信息，包含了如最近执行了哪些语句，在执行过程中每个阶段花费多长时间，内存的使用情况等。
- sys：主要通过视图的形式把information_schema和performance_schema结合起来，方便管理者了解MySQL服务器的性能信息。
表在文件系统中的表示
数据都是以记录的形式插入到表中的，每个表的信息可以分为两种：
- 表结构定义
- 表中的数据
在建表语句中设置的表名称、表有多少列、每个列的数据类型、约束条件和索引、字符集和比较规则等信息，为了保存它们，InnoDB和MyISAM存储引擎都在数据目录下对应的数据库子目录创建了一个专门用于描述表结构的文件，文件名为：
- 表名.frm：二进制格式存储
InnoDB是如何存储表数据的
为了更好的管理页，InnoDB存储引擎使用表空间概念，表空间可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）；每一个表空间可以被划分为很多页，表数据就存放在某个表空间下的某些页中。InnoDB表空间分为以下几种不同类型：
-
系统表空间（system tablespace）
默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12MB的文件，这个文件就是对应的系统表空间在文件系统上的展示。该文件是自扩展文件。[root@localhost mysql]# ls auto.cnf ibdata1 ib_logfile1 localhost.pid performance_schema test ib_buffer_pool ib_logfile0 ibtmp1 mysql sys如果想要系统表空间对应文件系统上的多个实际文件，可以在MySQL服务器启动时配置文件路径及名称，如配置两个512M的系统表空间data1和data2文件：
[root@localhost ~]# vim /etc/my.cnf innodb_data_file_path=data1:512M;data2:512M:autoextend # autoextend表示文件不够用时自动扩展注意：系统表空间只有一份
-
独立表空间（file-per-table tablespace）
在MySQL 5.6.6以及之后的版本中，InnoDB默认不再默认把各个表的数据存储到系统表空间，而是为每一个表建立一个独立表空间。使用独立表空间存储表数据会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，其文件名和表明相同：表名.ibd
如果使用独立表空间存储表，就会在相应的数据库子目录下建立如下两个文件：
-rw-r-----. 1 mysql mysql 8838 Oct 12 09:17 servers.frm -rw-r-----. 1 mysql mysql 98304 Oct 12 09:17 servers.ibd
可以自主指定使用系统表空间还是独立表空间存数据，通过启动选项innodb_file_per_table来设置：
innodb_file_per_table=0 使用系统表空间
innodb_file_per_table=1 使用独立表空间
MyISAM是如何存储表数据的
在MyISAM存储引擎中的索引全部相当于二级索引，该存储引擎的索引和数据是分开存放的。且MyISAM没有表空间概念，表的数据和索引都存放到对应的数据库子目录下：
-rw-r-----. 1 mysql mysql 10816 Oct 12 09:17 user.frm # 表结构的定义
-rw-r-----. 1 mysql mysql 340 Oct 12 09:17 user.MYD # 表的数据文件
-rw-r-----. 1 mysql mysql 4096 Oct 12 09:17 user.MYI # 表的索引文件
InnoDB 表空间（Tablespace，TBS）
表空间概念
表空间是在表和物理文件之间的一种逻辑结构，表空间管理模式的出现是为了数据库的存储更容易扩展（类比LVM）。
InnoDB存储引擎可将所有数据存放在ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间（共享表空间以及独立表空间都是针对数据的存储方式而言的）。
- MySQL从5.5版本以后出现共享表空间概念，默认使用共享表空间；
- MySQL5.6版本中默认使用的是独立表空间，共享表空间只用来存储系统表相关数据、UNDO、临时表。
- MySQL 5.7版本，临时表被独立出去
- MySQL 8.0版本，UNDO被独立出去
共享表空间
共享表空间简介
- 数据库的所有的表数据及索引文件全部都放在一个文件中，管理比较混乱。
- 默认这个共享表空间的文件路径在data目录下。
- 默认的文件名为ibdata1，初始化为12M，以64M进行增长，且不会释放。
共享表空间查看
- 物理文件查看
[root@localhost ~]# ll /data/mysql/ibdata1 -rw-r-----. 1 mysql mysql 12582912 Sep 29 09:17 /data/mysql/ibdata1 - 在数据库命令行中查看
mysql [(none)]>select @@innodb_data_file_path; +-------------------------+ | @@innodb_data_file_path | +-------------------------+ | ibdata1:12M:autoextend | +-------------------------+ 1 row in set (0.00 sec)mysql [(none)]>show variables like "innodb_data_file%"; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.00 sec)
共享表空间设置
在搭建MySQL时，初始化数据之前设置到参数文件中
[root@db01 data]# vim /etc/my.cnf
[mysqld]
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend
-
可能产生的问题：修改完启动可能会报错，一般是由于ibdata1的大小设置与当前表空间大小不一致造成的。
解决办法：将ibdata1的大小设置成与当前表空间一致，就可以正常启动。
独立表空间
独立表空间简介
MySQL 5.6版本起，默认表空间不再使用共享表空间，替换为独立表空间。每个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。独立表空间主要存储的是用户数据。
mysql表数据 =（ibdataX+frm）+ibd(段、区、页)
- 元数据（表属性）
- 真实数据 （数据行+索引） ibd文件
- 表的列信息 frm文件
MySQL的存储引擎日志：
- Redo Log: ib_logfile0 ib_logfile1，重做日志
- Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
- 临时表:ibtmp1，在做join union操作产生临时数据，用完就自动
独立表空间查看
-
物理查看
[root@localhost ~]# ll /data/mysql/school/ total 128 -rw-r-----. 1 mysql mysql 61 Sep 25 15:07 db.opt -rw-r-----. 1 mysql mysql 8789 Sep 25 15:51 students.frm -rw-r-----. 1 mysql mysql 114688 Sep 25 16:07 students.ibd -
数据库命令行查看
mysql [(none)]>select @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ 1 row in set (0.09 sec)1：默认独立表空间
0：默认共享表空间
独立表空间设置
-
临时设置（命令行设置）
SET GLOBAL innodb_file_per_table=1; -
永久设置（修改配置文件）
[root@db01 ~]# vim /etc/my.cnf [mysqld] innodb_file_per_table=1 -
MySQL表空间迁移
select @@innodb_file_per_table; +-------------------------+ | @@innodb_file_per_table | +-------------------------+ | 1 | +-------------------------+ alter table table_name dicard tablespace; alter table table_name import tablespace;
真实案例
案例背景：硬件及软件环境——联想服务器（IBM） 磁盘500G 没有raid centos 6.8
mysql 5.6.33 innodb引擎 独立表空间 无备份 无日志
开发用户专用库:jira(bug追踪) 、 confluence(内部知识库) ------>LNMT
故障描述：断电，启动完成后“/” 只读；fsck重启,系统成功启动,mysql启动不了。
结果，confulence库在，jira库消失
处理设想：表空间迁移:
create table xxx
alter table confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
处理过程： confulence库中一共有107张表。
1、创建107和和原来一模一样的表，同时mysqldump备份confulence历史库
mysqldump -uroot -ppassword -B confulence --no-data >test.sql
拿到测试库，进行恢复，到这步为止，获得表结构。
2、表空间删除，批量生成建表语句
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功（主外键关系）
set foreign_key_checks=0 跳过外键检查，把有问题的表空间也删掉。
3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
4、验证数据
表都可以访问，数据挽回到了出现问题时刻的状态（2-8）
InnoDB引擎核心特性
事务
在对某个页面进行读写访问时，都会先把这个页面加载到Buffer Pool中；之后如果修改了某个页面，也不会立即把修改刷新到磁盘，而只是把这个修改后的页面添加到Buffer Pool的flush的链表中，在之后的某个时间点才会刷新到磁盘。如果在将修改过的页刷新到磁盘之前系统崩溃了，那就丢失了这些修改。
为了避免这种故障发生，出现了事务的概念。事务就是保证了原子性、隔离性、一致性和持久性的一个或多个数据库操作。由此看来，事务只是抽象的概念，它其实对应着一个或多个数据库操作。根据这些操作所执行的不同阶段把事务大致划分为以下几种状态：
- 活动的（active）：事务对应的数据库操作正在执行过程中时，即事务正处于活动的状态。
- 部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，就称事务处于部分提交的状态。
- 失败的（failed）：当事务处于活动的状态或部分提交的状态时，可能遇到了某些错误（数据库自身的错误、操作系统错误或断电等）而无法继续执行，或者人为停止了事务的执行，就称事务处于失败的状态。
- 中止的（aborted）：如果事务执行了半截而变为失败的状态，就要撤销失败事务已经对数据库造成的影响——回滚。当回滚操作执行完毕后，即数据库恢复到了执行事务之前的状态，就称事务处于中止的状态。
- 提交的（committed）：当一个处于部分提交的状态的事务将修改过的数据都刷新到磁盘中后，就称事务处于提交的状态。
只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算结束。对于已经提交的事务来说，该事务对数据库所作的修改将永久生效；对于处于中止的状态的事务来说，该事务对数据库所做的修改都会被回滚到没执行该事务之前的状态。
MySQL事务主要用于处理操作量大，复杂度高的数据：
- 在MySQL中只有使用了InnoDB存储存储引擎的数据库或表才支持事务；
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要不全部不执行；
- 事务用来管理INSERT、UPDATE、DELETE语句。
事务的ACID特性
- Atomicity，原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- Consistency，一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- Isolation，隔离性：事务之间不相互影响。数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- Durability，持久性：事务处理结束后，所操作的数据都应该在磁盘中保留下来，对数据的修改就是永久的，即便系统故障也不会丢失。
事务的生命周期（语法）
事务的本质就是一系列数据库操作，只不过这些数据库操作符合ACID特性而已。
事务的开始
-
BEGIN语句代表开启一个事务。开启事务后，就可以继续写若干个语句，这些语句都属于刚刚开启的这个事务。
BEGIN [WORK];
注意：在MySQL 5.5 以上的版本，不需要手工BEGIN，只要执行的是一个DML，会自动在前面加一个BEGIN命令。
-
START TRANSACTION语句与BEGIN语句有相同的共享，都标志着开启一个事务。
START TRANSACTION;
相较于BEGIN，START TRANSACTION语句后面可以跟随几个修饰符（如果不显式指定事务的访问模式，则该事务的访问模式就是读写模式）：
- READ ONLY：标识当前事务是一个只读事务，即属于该事务的数据库操作只能读取数据，而不能修改数据（不能修改其他事务也能访问的表中的数据，可以修改临时表数据）。
- READ WRITE：标识当前事务是一个读写事务，即属于该事务的数据库操作既可以读取数据，也可以修改数据。
- WITH CONSISTENT SNAPSHOT：启动一致性读
事务的结束
-
COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的。一旦事务提交成功，就说明具备ACID特性。
COMMIT [WORK];
-
手动中止事务：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改（将内存中已执行过的操作回滚回去，已经提交的数据不能回滚）。
ROLLBACK [WORK];
注意：ROLLBACK是手动回滚事务时使用的。如果事务在执行过程中遇到了某些错误而无法继续执行的话，大部分情况下会回滚失败的语句；在发生例如死锁的情况下会回滚整个事务。
事务的自动提交和隐式提交
自动提交
MySQL中有一个系统遍历autocommit，用来自动提交事务。
mysql [(none)]>SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)
# SET AUTOCOMMIT=0 禁止自动提交
# SET AUTOCOMMIT=1 开启自动提交
在默认情况下，如果不显式的使用START TRANSACTION或BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称为事务的自动提交。
关闭自动提交有两种方法：
- 显式的使用START TRANSACTION 或 BEGIN语句开启一个事务，这样在本次事务提交或者回滚前会暂时关闭自动提交的功能。
- 将系统变量autocommit的值设置为0，如此写入的多条语句就属于同一个事务，直到显式的写出COMMIT语句将事务提交或者显式写出ROLLBACK语句将事务回滚。
注意：不管有没有事务需求，一般建议autocommit为0将自动提交关闭，可以很大程度上提高数据库性能。
隐式提交
因为某些特殊的语句而导致事务提交的情况称为隐式提交。会导致事务隐式提交的语句如下：
- 定义或修改数据库对象的数据定义语言（Data Definition Language，DDL），当使用CREATE、ALTER、DROP等语句修改数据库对象时，会隐式提交前面语句所属事务。
- 隐式使用或修改mysql数据库中的表，在使用ALTER USER、CREATEE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等语句时，也会会隐式提交前面语句所属事务。
- 事务控制或关于锁定的语句：
- 当在一个事务还没提交或者还没回滚时就使用START TRANSACTION 或 BEIGIN 语句开启另一个事务，就会隐式提交上一个事务。
- 在autocommit系统变量的值为时，手动的将其调整为1，会隐式提交前面语句所属的事务。
- 使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句也会隐式地提交前面语句所属事务。
- 加载数据的语句。如使用LOAD DATA语句向数据库中批量导入数据时，也会隐式地提交前面语句所属事务。
- 有关MySQL复制的一些语句。使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO 等语句时也会隐式的提交前面语句所属事务。
- 其他语句：使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等语句时也会隐式的提交前面语句所属事务。
保存点（savepoint）
定义保存点之后，在调用ROLLBACK语句的时候就可以指定回滚到哪个点，而不是回到最初的原点。定义保存点的语法如下：
SAVEPOINT 保存点名称;
如果ROLLBACK后不加保存点名称，直接回滚到事务执行之前的状态；当想回滚到指定的保存点时，可以使用如下语句：
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;
如果想删除某个保存点，可以使用这个语句：
RELEASE SAVEPOINT 保存点名称;
开始事务流程
-
检查autocommit是否为关闭状态
SELECT @@autocommit;
or
SELECT variables LIKE ‘autocommit’; -
开启事务,并结束事务
回滚结束BEGIN
DELETE FROM student WHERE name=‘test’;
UPDATE student SET name=‘test01’ WHERE name=‘test’;
ROLLBACK;提交结束
BEGIN
DELETE FROM student WHERE name=‘test’;
UPDATE student SET name=‘test01’ WHERE name=‘test’;
COMMIT;
InnoDB 事务的ACID如何保证?
概念名词
Redo Log：重做日志 ib_logfile0~1 轮询使用
Redo Log Buffer：Redo内存区域
ibd：存储数据行和索引
Buffer Pool：缓冲区池,数据和索引的缓冲
LSN：日志序列号 (在磁盘数据页,redo文件,buffer pool,redo buffer中存在)
MySQL 每次数据库启动,都会比较磁盘数据页和redo log的LSN,必须要求两者LSN一致数据库才能正常启动
WAL（Write Ahead Log）： 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,把内存页称之为脏页.
CKPT（Checkpoint,检查点）：将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务
redo log
redo 简介
问：如果不使用redo log会有什么问题？
答：为了保证数据的持久性，一个简单的做法是事务提交完成之前，把该事务修改的所有页面都刷新到磁盘，但是会存在以下问题：
- 每次刷新整个数据页。
即使仅更改了某页面中一个字节，由于InnoDB以页为单位进行磁盘I/O，该事务提交时也需要将一整个页刷新到磁盘。 - 随机I/O刷新比较慢。
一个事务可能包含多个语句，一条语句可能修改很多页面，这些页面可能并不相邻。这意味着在某个事务修改的Buffer Pool中的页面刷新到磁盘时，需要进行很多的随机I/O，随机I/O比顺序I/O慢得多，性能比较差。
我们只想让已经提交的事务对数据库中的数据所做的修改能永久生效，即使后来系统崩溃，在重启后也能将修改恢复过来。没有必要在每次提交事务时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改的内容记录下来，并将修改记录刷新到磁盘上。后面即使系统崩溃了，重启后只要按记录中的内容步骤重新更新一下数据页，那么该事务对数据库所作的修改就可以恢复，也就意味着满足持久性的要求。
这些记录被称为重做日志（redo log），记录了事务对数据库做了哪些修改，并在事务提交时，将redo log中的内容刷新到磁盘。
redo log会在事务在执行过程中，对数据库所做的所有修改都记录下来，后续如果系统崩溃重启，可以通过redo log恢复事务所做的任何修改。
事务提交时只将执行过程中产生的redo日志刷新到磁盘，而不是将所有修改过的页面都刷新到磁盘，这么做的好处如下：
- redo日志占用的空间非常小
- redo日志是按顺序写入磁盘
redo 日志格式
redo日志的本质只是记录了一下事务对数据库进行了哪些修改，InnoDB针对事务对数据库不同的修改场景，定义了多种类型的redo日志，绝大多类型的redo日志的格式如下：
- type：redo日志类型
- space ID：表空间ID
- page number：页号
- data：redo日志具体内容
redo 日志写入过程
redo log block
为了更好的管理redo日志，InnoDB中将通过MTR生成的redo日志都放在了大小为512字节的页中。这里将用来存储redo 日志的页称为block。redo log block如下所示：
- 真正的redo日志存储到占用496bytes的log block body中；
- log block header和log block trailer存储的是一些管理信息。
MTR：Mini-Transaction，对底层页面进行一次原子访问的过程，一个MTR包含一组redo日志，在进行崩溃恢复时，需要把这一组redo日志作为一个不可分割的整体来处理。一个事务可以包含若干条语句，每一条语句又包含若干个MTR，每一个MTR又包含若干条redo语句。
redo log buffer
写入redo日志时不能直接写入到磁盘中，MySQL服务器在启动时就向操作系统申请了一大片称为redo log buffer（redo日志缓冲区）的连续内存空间，这片内存空间被划分成若干个连续的redo log block。MySQL5.7中，这个空间的默认大小为16K。
可以通过启动选项innodb_log_buffer_size来指定log buffer的大小。
root@localhost [(none)] >show variables like "innodb_log_buffer_size";
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.01 sec)
redo 日志写入log buffer
向log buffer中写入redo日志的过程是顺序写入的，也就是先往前面的block写，当该block的空闲空间用完之后再往下一个block中写。在想要向log buffer写入redo日志时，如何确定写在哪个block的哪个偏移量处？可以通过buf_free的全局变量进行查看，该变量指明了后续写入的redo日志应该写到log buffer的哪个位置。
redo 日志文件
redo 日志刷新时机
- log buffer空间不足时
InnoDB中，在当前写入log buffer的redo日志量已经占满log buffer总容量的50%左右，就需要把这些日志刷新到磁盘。 - 事务提交时
提出redo的概念是因为它占用的空间少，且可以将其顺序写入磁盘。引入redo日志后，虽然在事务提交时可以不把修改过的Buffer Pool页面立即刷新到磁盘，但是为了保持持久性，必须要把页面修改时所对应的redo日志刷新到磁盘。 - 后台定时刷新
后台有一个线程，大约以每秒一次的频率将log buffer中的redo日志刷新到磁盘 - 正常关闭服务器时
- 做checkpoint时
redo 日志文件组
redo log日志文件与MySQL数据库文件在同一个目录下，使用系统变量datadir可以查看数据库的数据目录：
mysql [(none)]>show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value |
+---------------+--------------+
| datadir | /data/mysql/ |
+---------------+--------------+
1 row in set (0.39 sec)
MySQL的数据目录下默认有名为ib_logfile0和ib_logfile1的两个文件，log buffer中的日志在默认情况下就是刷新到这两个文件中。
[root@localhost ~]# ll /data/mysql/ib_log*
-rw-r-----. 1 mysql mysql 50331648 Sep 29 09:17 /data/mysql/ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 23 12:17 /data/mysql/ib_logfile1
可以以下几个启动项来调节redo文件组：
-
innodb_log_group_home_dir：指定redo日志文件所在的目录，默认值就是当前的数据目录
root@localhost [(none)] >show variables like "innodb_log_group_home_dir"; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_group_home_dir | ./ | +---------------------------+-------+ 1 row in set (0.01 sec) -
innodb_log_file_size：指定每个redo日志文件的大小，在MySQL 5.7版本中的默认值为48M
root@localhost [(none)] >show variables like "innodb_log_file_size"; +----------------------+----------+ | Variable_name | Value | +----------------------+----------+ | innodb_log_file_size | 50331648 | +----------------------+----------+ 1 row in set (0.00 sec) -
innodb_log_files_in_group：指定redo日志文件的个数，默认值为2，最大值为100
root@localhost [(none)] >show variables like "innodb_log_files_in_group"; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | innodb_log_files_in_group | 2 | +---------------------------+-------+ 1 row in set (0.00 sec)
由此看来，磁盘上的redo日志文件不止一个，而是以一个日志文件组的形式出现的。这些文件以“ib_logfile[0、1、2…]”的形式进行命名，在将redo日志写入日志组时，从ib_logfile0开始写起；如果ib_logfile0写满了，就接着ib_logfile1写；同理，ib_logfile1写满了就去写ib_logfile2；依次类推，如果写到最后一个文件发现写满了，就重新转到ib_logfile0继续写。
redo 日志文件的总大小其实就是innodb_log_file_size x innodb_log_files_in_group。
redo 日志文件格式
log buffer本质上是一片连续的内存空间，被划分为若干个512字节大小的block，将log buffer中的redo日志刷新到磁盘的本质就是将block镜像写入日志文件中。所以redo日志文件有若干个512字节大小的block组成。
在redo日志文件组中，每个文件的大小都一样，格式也一样，都是由下面两部分组成：
- 前2048字节（前4个block）用来存储一些管理信息
- 从第2048字节往后的字节用来存储log buffer中的block镜像
Log Sequence Number
自系统开始运行，就在不断地修改页面，这就意味着会不断地生成redo日志，redo日志的量在不断递增。因此InnoDB设计了一个名为ISN（Log Sequence Number）的全局变量，用来记录当前总共已经写入的redo日志量，LSN规定初始值为8704（即一条redo也没写入时，LSN的值就是8704）。
向log buffer写入redo日志时不是一条一条写入的，而是以MTR生成的一组redo日志为单位写入的，实际是把日志内容写在了log block body处。但是在统计LSN的增长量时，是按照实际写入的日志量加上占用的log block header和log block trailer来计算的。
注意：每一组由MTR生成的redo日志都有一个唯一的LSN值与其对应；LSN值越小，说明redo日志产生越早。
flushed_to_disk_lsn
redo日志先写到log buffer中，之后才会被刷新到磁盘的redo日志文件中。InnoDB中有一个名为buf_next_to_write的全局变量，用来标记当前有哪些日志被刷新到磁盘中。
LSN表示了当前系统中写入的redo日志量，这包括了写到log buffer但没有刷新到磁盘的redo日志。InnoDB中有一个表示刷新到磁盘中的redo日志量的全局变量，名为flushed_to_disk_lsn。系统在第一次启动时，该变量的值与初始的ISN值相同，都是8704。随着系统的运行，redo日志被不断写入log buffer，但是并不会立即刷新到磁盘，lsn值就与flushed_to_disk_lsn的值拉开了差距。
- buf_free：指明后续写入的redo日志应该写到log buffer的哪个位置
- buf_next_to_write：当前有哪些日志被刷新到磁盘中
- lsn：当前系统中写入的redo日志量（包括写到log buffer但没有刷新到磁盘的redo日志），当有新的redo日志写入到log buffer时，lsn的值会增长，但flushed_to_disk_lsn值不变
- flushed_to_disk_lsn：刷新到磁盘中的redo日志量，当log buffer中的日志被刷新到磁盘上，flushed_to_disk_lsn的值会增长
当lsn和flushed_to_disk_lsn值相同，说明所有redo log都刷新到了磁盘上
checkpoint
由于redo文件组的容量是有限的，所以需要循环使用redo日志文件组中的文件，但是这会造成最后写入的redo日志和最开始写入的redo日志追尾。redo日志只是为了在系统崩溃后恢复脏页用的，如果对应的脏页已经刷新到磁盘，就不再需要该redo日志了，其所占用的磁盘空间就可以被后续的redo日志重用。
判断redo日志占用的磁盘空间是否可以覆盖的依据，就是它对应的脏页是否已经被刷新到了磁盘中。全局变量checkpoint_lsn用来表示当前系统中可以被覆盖的redo日志总量是多少，该变量初始值为8704.。
当某页被刷新到磁盘，其对应的redo日志就可以被覆盖，所以可以进行一个增加checkpoint_lsn的操作，称该过程为执行一次checkpoint。执行一次checkpoint分为两个步骤：
- 计算当前系统中可以被覆盖的redo日志对应的lsn值最大为多少
- 将 checkpoint_lsn与对应的redo日志文件组偏移量以及此次checkpoint的编号写到日志文件的管理信息中（有关checkpoint的信息只会被写到日志文件组中第一个日志文件的管理信息中）
注意：
- 后台有线程不断将脏页刷新到磁盘，但将脏页刷新到磁盘上和执行一次checkpoint是两回事。一般将脏页刷新到磁盘上和执行一次checkpoint是由不同线程执行的，并不是每次有脏页刷新就要去执行checkpoint；
- 每执行一次checkpoint就要修改redo日志文件的管理信息，因此执行checkpoint是由代价的。
redo 前滚操作
MySQL : 在启动时，必须保证redo日志文件和ibd数据文件LSN一致, 如果不一致就会触发CSR，最终保证一致。
进行一个事务，BEGIN 、UPDATE、COMMIT
- BEGIN，立即分配一个TXID=tx_01；
- UPDATE，会将需要修改的数据页(dp_01,LSN=101)，加载到data buffer中；
- 数据页读写线程，会对dp_01数据页修改更新,并更新LSN=102；
- 日志写线程会将dp_01数据页的变化+LSN+TXID存储到redo buffer；
- COMMIT，日志写线程会将redo buffer信息写入redo log日志文件中，基于WAL原则，在日志完全写入磁盘后，commit命令才执行成功(会将此日志打上commit标记)。
假如此时宕机，内存脏页没有来得及写入磁盘，内存数据全部丢失。
MySQL再次重启时,必须要redo log和磁盘数据页的LSN是一致的。但是，此时dp_01，TXID=tx_01，磁盘是LSN=101，dp_01，TXID=tx_01，redo log中LSN=102，MySQL此时无法正常启动，MySQL触发CSR。在内存追平LSN号，触发ckpt，将内存数据页更新到磁盘，从而保证磁盘数据页和redo log LSN一值，这时MySQL正常启动。
以上的工作过程，将它称之为基于REDO的"前滚操作"。
undo log
undo log简介
事务需要保证原子性，即事务中的操作要么全部完成，要么什么都不做。在事务执行过程中，如果出现了以下情况：
- 遇到服务器错误、操作系统错误或断电等意外情况
- ROLLBACK语句结束当前事务的执行
以上情况都会导致事务执行到一半就结束，但是事务已经修改了数据库的很多东西，为了保证事务的原子性，需要将数据库变回执行事务之前的状态，这个过程称为回滚。每当对一条记录进行改动时，将对其回滚所需的信息记录下来，为了回滚而记录的文件称之为undo log。
undo log作用
在事务ACID过程中，实现的是A原子性的作用，另外CI也依赖于undo。
- 在rollback时,将数据恢复到修改之前的状态；
- undo提供快照技术，保存事务修改之前的数据状态，保证了MVCC隔离性，mysqldump的热备。
事务ID
分配事务ID的时机
如果某个事务在执行过程中对某个表执行了增删改操作，那么InnoDB存储引擎就会给它分配一个独一无二的事务ID，分配方式如下：
- 对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增删改操作时，才会为这个事务分配一个事务ID，否则是不分配事务ID的。
- 对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增删改操作时，才会为这个事务分配一个ID，否则是不分配事务ID的。
注意：如果不为某个事务分配事务ID，则它的事务ID值默认为0。
事务ID是怎么生成的
事务ID本质上是一个数字，它的分配策略如下：
- 服务器在内存中维护一个全局变量，每当需要为某个事务分配事务ID时，就会把该变量的值作为事务ID分配给该事务，并且将变量自增1；
- 每当这个变量的值为256的倍数时，就会把该变量的值刷新到系统表空间中页号为5的页面中一个名为Max Trx ID的属性中，这个属性占用8字节的存储空间；
- 当系统下一次重新启动时，会将这个Max Trx ID属性加载到内存中，将该值加上256之后赋值给前面提到的全局变量（因为上次关机时，该全局变量的值可能大于个Max Trx ID属性值），这样就可以保证整个系统中分配的事务ID值是一个递增的数字。先分配事务ID的事务得到的ID较小，后分配事务ID的事务得到的ID较大。
undo log 格式
为了实现事务的原子性，InnoDB存储引擎在实际进行记录的增删改操作时，都需要先把对应的undo日志记下来。InnoDB中针对不同的场景使用不同类型的undo日志：
- TRX_UNDO_INSERT_REC
- TRX_UNDO_DEL_MARK_REC
- TRX_UNDO_UPD_EXIST_REC
INSERT操作对应的undo日志
向表中插入一条数据，想要回滚只需要在插入时记下主键ID，回滚时根据主键ID去删除这条记录即可。
- undo no在一个事务中是从0开始递增的，也就是说只要事务没提交，每生成一条undo日志，那么该条日志的undo no就增1；
- 主键各列信息：如果记录中的主键只有一个列，那么就要把该列占用的存储空间大小和真实值记录下来，如果记录中的主键包含多个列，那么每个列占用的存储空间大小和真实值都需要记录下来。
DELETE操作对应的undo日志
插入到页面的记录会根据记录头信息中的next_record属性组成一个单项链表，称之为正常记录链表；被删除的记录也会根据next_record属性组成一个链表，称之为垃圾链表。
将一条记录从正常记录链表中删除的步骤分为两个阶段：
- delete mark
在事务中执行删除语句，但是还未提交事务时，只会将记录头信息中的delete_mask标志位设置为1。 - purge
当该删除语句所在的事务提交后，有专门的线程把记录从正常记录链表中移除，并添加到垃圾链表中。
- 对一条记录进行delete_mark操作前，需要把该记录的旧的trx_id和roll_pointer的值都记到对应的undo日志中来，这样就可以通过undo日志的old roll_pointer找到记录在修改之前对应的undo日志。
- 与insert的undo日志不同的是，delete的undo日志中需要记录所有索引相关的列的信息，因为在purge阶段，需要去删除该记录所有索引信息。
UPDATE操作对应的undo日志
update时更新主键和不更新主键是两种不同的处理方案
-
不更新主键
不更新主键又分为两种情况：- 记录更新的每一列的数据和更新前占用的空间完全一样。这时可以直接在原记录上更新，在update的undo日志中记录下原先的列的值。
- 更新前后列的占用空间不一样。此时就不能再原记录上直接更新了。新建update的undo日志，记录要修改的列的原值，然后需要把原记录直接删除掉，再插入新记录（这里的删除是直接将记录从正常记录链表中移除并加入到垃圾链表中）。
-
更新主键
更新主键分为两个步骤：- 将旧记录进行delete mark操作。在delete mark前会记录一条delete 的undo日志。
- 插入一条新记录，需要重新定位插入的位置。插入新记录时，会记录一条insert的undo日志。所以更新主键的update操作会生成两条undo日志，先是delete的undo，再是insert的undo。
隔离
MySQL是一个客户端/服务器架构的软件，对于一个服务器来说，可以有很多个客户端与之连接。每个客户端与服务器建立连接之后，就形成了一个会话。每个客户端都可以在自己的会话中向服务器发送请求语句，服务器可以同时处理来自多个客户端的多个事务。
为了保证事务的一致性，需要确保多事务最终执行的效果与事务单独执行的效果一样，即事务需要隔离的执行，互不干涉。实现隔离性最简单的方式就是使事务串行执行，但是串行执行会严重降低系统吞吐量和资源利用率，并会增加事务等待时间。事务并发执行之所以可能影响一致性，是因为它们在执行过程中可能访问相同的数据。两个并发的事务在执行过程中访问相同数据的情况有：
- 读-读情况
- 读-写情况
- 写-读情况
- 写-写情况
若某事务访问某个数据时，对要求其他试图访问相同数据的事务进行限制，让它们进行排队。当该事务提交后，其他事务才能继续访问该数据。这样可以让并发执行的事务执行结果与串行执行的结果一样，称这种多事务的执行方式为：可串行化执行。多事务在不进行可串行化执行时，可能会出现如下问题：
- 脏写（Dirty Write）：如果一个事务修改了另一个未提交事务修改过的数据，就意味着发生了脏写现象。
- 脏读（Dirty Read）：如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象。
- 不可重复读（Non-Repeatable Read）：如果一个事务修改了另一个未提交事务读取的数据，就意味着发生了不可重复读现象，或者叫模糊读（Fuzzy Read）现象。
- 幻读（Phantom）：如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入一些符合那些搜索条件的记录，就意味着发生了幻读现象。
以上可导致一致性问题的现象的严重性排行为：脏写>脏读>不可重复读>幻读。
可以通过设立一些隔离级别来舍弃一部分的隔离性换取一部分性能。
SQL标准隔离级别
SQL标准中规定（SQL标准，而不是MySQL）：针对不同的隔离级别，并发事务执行过程中可以发生不同的现象。
- READ UNCOMMITTED：未提交读
- READ COMMITTED：已提交读
- REPEATABLE READ：可重复读
- SERIALIZABLE：可串行化
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 |
| READ COMMITTED | 不可能 | 可能 | 可能 |
| REPEATABLE READ | 不可能 | 不可能 | 可能 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 |
- RU : 读未提交，可脏读，一般不依序出现；
- RC : 读已提交，可能出现幻读，可以防止脏读
- RR : 可重复读，功能是防止"幻读"现象 ，利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)；
- SR : 可串行化，可以防止死锁，但是并发事务性能较差。
补充: 在RC级别下，可以减轻GAP+NextLock锁的问题，但是会出现幻读现象，一般在为了读一致性会在正常select后添加for update语句。但是请记住执行完一定要commit ，否则容易出现所等待比较严重。
[world]>select * from city where id=999 for update;
[world]>commit;
MySQL的默认隔离级别是REPEATABLE READ，如果想让事务在不同的隔离级别中运行，可以通过以下语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
level: {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
可以通过查看系统变量transaction_isolation的值来确定当前会话默认的隔离级别：
root@localhost [(none)] >show variables like "transaction_isolation";
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.03 sec)
MVCC原理
MVCC（Multi-Version Concurrency Control，多版本并发控制）就是指在使用READ COMMITTED、REPEATABLE READ这两种隔离级别的事务执行普通的SELECT操作时，访问记录的版本链的过程。这样可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
READ COMMITTED 和 REPEATABLE READ这两个隔离级别有一个很大的不同，就是生成ReadView的时机不同：
- READ COMMITTED在每一次进行普通SELECT操作前都会生成一个ReadView；
- REPEATABLE READ只在第一次进行SELECT操作前生成一个ReadView，之后查询操作重复使用这个ReadView。
版本链
对于使用InnoDB存储引擎的表来说，它的聚集索引记录中都包含下面两个必要的隐藏列：
- trx_id：一个事务每次对某条聚集索引记录进行改动时，都会把该事务的事务ID赋值给trx_id隐藏列；
- roll_pointer：每次对某条聚集索引记录进行改动时，都会把旧的版本写入到undo日志中；这个隐藏列相当于一个指针，可以通过它找到该记录修改前的信息。
每对记录进行一次改动，都会记录一条undo日志。每条undo日志都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性），通过这个属性可以将这些undo日志串成一个链表。在每次更新该记录后，都会将旧值放到一条undo日志中（算是该记录的一个旧版本）。随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，称之为版本链。
版本链的头节点就是当前记录的最新值，每个版本中还包含生成该版本时对应的事务ID。之后利用这个记录的版本链来控制并发事务访问相同记录时的行为，将这种机制称为多版本并发控制（Multi-Version Concurrency Control，MVCC）。
ReadView
- 对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本即可；
- 对于使用SERIALIZABLE隔离级别的事务来说，InnoDB规定使用加锁的方式访问记录；
- 对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交的事务修改过的记录。
因此需要判断版本链中的哪个版本是当前事务可见的，InnoDB中有ReadView（一致性视图）的概念，主要包含：
- m_ids：在生成ReadView时，当前系统中活跃的读写事务的事务ID列表
- min_trx_id：在生成ReadView时，当前系统中活跃的读写事务中最小的事务ID，也就是m_ids的最小值
- max_trx_id：在生成ReadView时，系统应该分配给下一个事务的事务ID(注意max_trx_id并不是m_ids的最大值)
- creator_trx_id：生成该ReadView的事务的事务ID（只有对表中记录进行改动时才会为事务分配唯一的ID，否则一个事务的事务ID都默认为0）
有了ReadView之后，在访问某条记录时，只需要按照下面的步骤来判断记录的某个版本是否可见：
- 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，则需要判断trx_id属性值是否在m_ids列表中。如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。
如果某个版本的数据对当前事务不可见，那就顺着版本链找到下一个版本的数据，并继续执行上面的步骤来判断记录的可见性；以此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见，说明该记录对当前事务完全不可见，查询结果就不包含该记录。
二级索引
只有在聚集索引记录中才有trx_id和roll_pointer隐藏列。如果某个查询语句是使用二级索引来执行查询，判断可见性步骤如下：
-
二级索引的Page Header部分有一个名叫PAGE_MAX_TRX_ID的属性，每当对该页面中的记录执行增删改查操作时，如果执行该操作的事务的事务id大于PAGE_MAX_TRX_ID属性值，就会把PAGE_MAX_TRX_ID属性设置为执行该操作的事务的事务id。即PAGE_MAX_TRX_ID代表着修改该二级索引页面的最大事务id是什么。
当SELECT语句访问某个二级索引记录时，首先会对应的ReadView的min_trx_id是否大于该页面的PAGE_MAX_TRX_ID属性值，如果是，说明该页面中的所有记录都对该ReadView可见，否则就得执行步骤2，在回表之后再判断可见性。 -
利用二级索引记录中的主键进行回表操作，得到对应的聚集索引记录后再按照上面讲的方式找到对应该ReadView可见的第一个版本，然后判断该版本中相应的二级索引列的值是否与利用该二级索引查询时的值相同。
锁
锁是计算机协调多个进程或线程并发访问某一资源机制，保证数据并发访问的一致性、有效性。
MySQL锁划分
- 按使用方式划分：乐观锁，悲观锁
- 按粒度划分：表级锁、行级锁、页级锁
粒度锁
从锁的粒度，可以将锁分为两大类：
- 表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突概率最高，并发度最低。
- 一次性同时获取SQL语句所需要的全部锁，并总是按照相同的顺序获取表级锁来避免死锁。
- 表级锁更适合以查询为主，并发用户少，只有少量按索引条件更新数据的应用。
- 行级锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度最高。
- 最大程度支持并发，同时带来了最大的锁开销。
- 在InnoDB中，除了单个SQL组成的事务外，锁是逐步获得的，因此可能发生死锁。
- 行级锁只在存储引擎层实现，更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。
- 页面锁：开销和加锁时间介于表级锁和行级锁之间，会出现死锁；锁定粒度介于表级锁和行级锁之间，且并发度一般。
MySQL不同的存储引擎支持不同的锁机制，且存储引擎层中的锁实现对服务器是透明的。
- MyISAM和MEMORY存储引擎采用表级锁（table-level locking）
- InnoDB存储引擎既支持行级锁（row-leveling locking），也支持表级锁；默认采用行级锁。
MyISAM表级锁
表锁又分为两种模式：
- 表共享读锁（Table Read Lock）：不会阻塞其他用户对同一个表的读请求，但会阻塞对同一个表的写请求；
- 表独占写锁（Tble Write Lock）：会阻塞其他用户对同一个表的读和写请求。
MyISAM 表读写操作是串行进行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行操作， 其他线程的读写操作都会等待，直到锁被释放为止。
默认情况下，写锁比读锁具有更高的优先级。当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。
因此MyISAM存储引擎不适合有大量更新操作和查询操作的应用，因为大量的更新操作会使得查询操作很难获得读锁，甚至可能永远阻塞。
MyISAM在执行查询语句（SELECT）前，会自动给涉及的表加读锁；在执行更新语句（UPDATE、DELETE、INSERT）前，会自动给涉及的表加写锁，该过程无需用户干预。且在自动加锁的情况下，MyISAM总是一次性获得SQL语句所需要的全部锁，这也是MyISAM表级锁不会出现死锁的原因。
InnoDB行级锁和表级锁
InnoDB实现了以下两种类型的行锁：
- 共享锁（S锁，读锁）：其他事务可以读，但不可以写
- 排他锁（X锁，写锁）：其他事务不可以读，也不可以写
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加行排他锁前必须先取得该表的IX锁。
意向锁由InnoDB自动添加，不需要用户干预。
MVCC行级锁
MVCC（Multi-Version ConcurrencyControl，多版本并发控制），InnoDB基于行锁实现了MVCC，MVCC在隔离级别下的READ COMMITTED和REPEATABLE READ下工作。
在表级锁中读写都是阻塞的，基于性能考虑，MVCC一般读写并不阻塞（很多情况避免加锁）。使用MVCC对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以不用加锁。
乐观锁和悲观锁
-
悲观锁（Pessimistic Lock）：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
当要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制，在修改数据之前先锁定再修改的方式被称之为悲观并发控制（Pessimistic Concurrency Control，缩写“PCC”，又名悲观锁）。传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。
-
乐观锁（Optimistic Lock）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果冲突，则返回给用户异常信息，让用户决定如何去做。乐观锁适用于读多写少的场景，这样可以提高程序的吞吐量。乐观锁采取了更加宽松的加锁机制。也是为了避免数据库幻读、业务处理时间过长等原因引起数据处理错误的一种机制(乐观锁无法避免脏读)，但乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。
死锁
-
死锁产生
所谓死锁，是指两个或两个以上事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。 -
检测死锁
数据库系统实现了各种死锁检测和死锁超时的机制，如InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。 -
死锁恢复
死锁发生以后，只有部分或完全回滚其中一个事务，才能被打破。InnoDB目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚。 -
外部锁的死锁检测
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放死锁并回滚，使得另一个事务获得锁继续完成。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。 -
死锁检测影响性能
死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。 有时当发生死锁时，禁用死锁检测而是使用innodb_deadlock_detect配置选项可能会更有效，可以依赖innodb_lock_wait_timeout设置进行事务回滚。
架构改造项目
项目背景: 2台 IBM X3650 32G , 原来主从关系,2年多没有主从了,"小问题"不断(锁,宕机后的安全)
MySQL 5.1.77 默认存储引擎 MyISAM
数据量: 60G左右 ,每周全备,没有开二进制日志
架构方案:
1. 升级数据库版本到5.7.20
2. 更新所有业务表的存储引擎为InnoDB
3. 重新设计备份策略为热备份,每天全备,并备份日志
4. 重新构建主从
结果:
1.性能
2.安全方面
3.快速故障处理
参数整理
存储引擎
-
查看
show engines;
show variables like ‘default_storage_engine’;
select @@default_storage_engine; -
设置
- 通过参数设置默认存储引擎
- 建表的时候设置默认存储引擎
-
修改
alter table table_name engine=innodb;
表空间
- 共享表空间
show variables like ‘innodb_data_file_path’;
- 独立表空间
show variables like ‘innodb_file_per_table’;
缓冲区池
-
查询
select @@innodb_buffer_pool_size;
show engine innodb status\Ginnodb_buffer_pool_size 一般建议最多是物理内存的 75-80%
innodb_flush_log_at_trx_commit
-
作用
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。 -
查询
select @@innodb_flush_log_at_trx_commit;
-
参数说明
- 在事务提交时不立即向磁盘同步redo日志，这个任务交给后台线程处理；这样虽然明显加快请求处理速度，但是如果事务提交后发生宕机，后台线程没有及时将redo日志刷新到磁盘，那么该事务对页面的修改就会丢失。
- 在事务提交时就需要将redo日志同步到磁盘，这样可以保证事务的持久性；innodb_flush_log_at_trx_commit的默认值为1。
- 在事务提交时需要将redo日志写到操作系统的缓冲区中，但并不需要保证将日志真正刷新到磁盘。如果数据库故障而操作系统没事就可以保持事务的持久性，如果操作系统也故障那么完蛋。
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
Innodb_flush_method=(O_DIRECT, fdatasync)
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method
-
作用
控制的是log buffer 和 data buffer，刷新磁盘的时候是否经过文件系统缓存。 -
查看
show variables like ‘%innodb_flush%’;
-
参数
O_DIRECT ：数据缓冲区写磁盘，不走OS buffer fsync ：日志和数据缓冲区写磁盘，都走OS buffer O_DSYNC ：日志缓冲区写磁盘，不走 OS buffer -
使用建议
最高安全模式innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT最高性能：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
