MySQL面试 存储引擎篇

问：说一下你了解的mysql存储引擎

Mysql是插件式的服务器架构，支持多种插件式的存储引擎。
共有9种，有支持的有8种
InnoDB 、bdb、MRG_MYISAM、MEMORY、BLACKHOLE、MyISAM、CSV、ARCHIVE、PERFORMANCE_SCHEMA、FEDERATED。

问：说说对MyISAM引擎的了解？

Myisam是MySQL缺省存储引擎，是从旧的ISAM引擎发展来的，具有检查和修复表格的大多数工具。表可以被压缩、支持全文索引，不支持集群数据库、不支持聚集索引、数据缓存、外检、hash indexes、mvcc、t-tree indexes、事务。

Myisam数据存储是以堆表的方式存储的，也就是说存储在myisam中保存的数据是没有顺序的。不像存储在聚集索引的表中，数据可以按照索引的顺序存储的。

在myisam引擎表中并不存在聚集索引的概念，索引的叶子节点直接指向的是数据的物理地址。因此也避免了索引的二次查找，单独从查询上来看，myisam引擎的表也一致好于innodb的表，这点是不需要怀疑的。

Myisam使用的是表级锁，在查询数据的时候会给表加共享锁，修改表中的数据会给表加排它锁。

对于使用myisam引擎的表同时执行读写操作时，读操作会阻塞写操作，写操作也会阻塞读操作。也就是说读写操作之间会有相互阻塞的情况出现，因此也能看出myisam存储引擎的表并不适合在高并发的读写场景中使用。

Myisam引擎是在mysql中使用时间最长的存储引擎，所以支持的索引种类是非常多是，不但支持我们常用的btree索引，同时还支持空间索引和全文索引。空间索引和全文索引一直到mysql5.6版本后对innodb引擎才逐渐支持。在之前如果要用全文索引的话就只能选择myisam了。

当然了，除了这些特点外，每个myisam引擎的表在磁盘都会保存三个文件。文件名都是以表名开头。
.frm 存表格式
.MYD 存数据
.MYI 存索引
8.0版本中只会有MYD和MYI，移除了FRM，将表定义直接存在了数据字典中。

在5.7中，要想使用myisam引擎就得通过engine显示指定，因为默认的是innodb。
如果myisam表有损坏可以使用mysqlcheck客户端或myisamchk实用程序检查或修复MyISAM表。
还可以使用myisampack压缩MyISAM表以占用更少的空间。
MyISAM大表如果索引文件损坏可以用repair table 修复。可以用check table检查表是否有损坏。

使用场景：
读操作远远大于写操作的场景
不需要事务的场景

问：说说对Memory引擎的了解

Memory引擎以前称为HEAP，由于数据存储在内存存储器中，容易受到硬件问题或断点的影响，它是一种数据易失性的引擎。所以一般将这种引擎的表用做临时工作区或只读缓存，一般用于存储其他表中读取的数据。反正存储在memory中的数据一般都是可以重新生成的。

在mysql中information_schema库中的表使用这个引擎，比如TABLES、ENGINES、FILES、INNODB_LOCKS、INNODB_TRX、GLOBAL_STATUS等，并且他们都是以TEMPORARY临时表创建的。

Memory支持自增，为了内存不受影响，memory引擎的表不能包含blob或text列。
内存表是表锁，不支持事务、数据库集群、聚集索引、数据压缩、外键、全文索引、MVCC、索引缓存。当修改频繁时，性能可能会下降。

Memory引擎表中的字段都是固定长度的，即使我们建立了一个varchar类型的字段，存储的时候还是按照char存储的。

Memory引擎表在磁盘上只有一个文件,表名称.frm ,里面存储的是表定义而不是数据。

MEMORY支持btree和hash索引。默认情况下，使用哈希索引，这也是它对于单值查找非常快的原因。但是，当服务器关闭时，MEMORY表中存储的所有行都会丢失。但是表结构会保留，因为表定义存储在.frm磁盘文件中，但是当服务器重新启动时它们为空。

MEMORY表 的最大大小受max_heap_table_size系统变量的限制，系统变量的默认值为16MB。

如果想在mysql启动时就填满mem表数据，可以使用init_file系统变量，比如–init-file="/data/mysql/init.sql"。还可以使用insert into例如可以将insert into …select或load data语句放到文件中，以便从持久表加载数据。

内存表与临时表并不相同，临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size = 128M设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降，
而内存表不会，内存表满后，会提示数据满错误。

在数据库复制时，如果主机当掉，则会在binLog中自动加入delete from [内存表]，将slave的数据也删除掉，以保证两边的数据一致性。

使用场景
涉及临时性，非关键性数据的操作，例如会话管理或缓存。当MySQL服务器停止或重新启动时，MEMORY表中的数据将丢失。
内存中存储，可实现快速访问和低延迟。数据量可以完全容纳在内存中，而不会导致操作系统交换swap分区。
只读或只读的数据访问模式

问：CSV存储引擎的了解

CSV也是非事务型存储引擎，数据以csv格式存储在磁盘上。

最大的特点是：数据以文本方式存储在文件中，所以我们可以直接编辑csv文件修改表数据
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息 mysql8 将frm 统一替换成了数据字典.sdi

1.表不支持自增主键。
2.所有列都不能为null 如果是严格模式的话还需要指定默认值。
3.Csv所有列都不能为null，建表的时候需要显示指定not null。
4.不支持索引，不适合作为平凡查询和更新的表使用。

在MySQL5.7中使用这个引擎的只有mysql.general_log和mysql.slow_log

使用场景：
作为数据交换中间表使用，也可以用来导出其他表数据

问：ARCHIVE存储引擎了解

Archive也不支持事务，可以有主键自增列，列可以是null，但这个引擎的表只允许新增和查询数据。除了主键列不能在创建其他索引。
一般用于归档历史数据，记录日志数据。

在磁盘上存储两个文件 .arz 和 sdi

这个引擎会缓存索引写操作对表数据使用zlib压缩，所以archive引擎的表比myisam引擎的表更加节约磁盘空间。如果用archive引擎的表存储myisam表数据的话，大约可以节约75%的空间。比innodb引擎的表小23%。

使用场景：
日志和数据采集类应用
数据归档存储应用

问：NDB 存储引擎了解

NDB引擎是非常出名且又很少被用到的一种引擎，普通版本的Mysql安装后是看不到的，这个是mysql集群使用的事务型存储引擎。这个是不同于mysql主从复制集群的另外一种高可用方案，如果要使用这个引擎就得安装支持ndb集群的mysql实例。但是由于性能原因，ndb集群也是不很常用。

在mysql NDB集群中，这个引擎的表可以在集群的各个节点中保持数据的一致性。

为什么不常用的原因是因为NDB的特性决定的
它不适合在高并发大数据的场景中使用，因此用的也就少了。

特点：
也是事务型引擎，和innodb不同的是ndb只支持读以提交的事务隔离级别。
而innodb支持 读以提交、读未提交、可重复度、顺序读 四种隔离级别

NDB数据在使用前要求数据需要从磁盘中加载到内存中，和mem引擎的区别是，mem引擎只会在磁盘中存储表的定义，不会存储具体数据。而ndb引擎会将数据保存到磁盘中，只是在使用的时候会将所有的数据加载到内存中。所以我们不用担心存储在NDB引擎中的数据在服务器重启后会丢失，但是要求服务器加载到内存中才可以使用，所以对服务器的内存会有一定影响。

虽然在新版本的ndb引擎中可以允许一部分数据存储在磁盘中一部分保留在内存中，但是优化效果还是有很多限制，所以这也就是ndb得不到广泛使用的原因。

在锁的实现上ndb实现的也是行级锁，大大提高了数据表的并发处理能力，但是性能并没有innodb那么好

Ndb引擎是所有引擎里面唯一支持集群的存储引擎。所以如果我们使用mysql NDB集群的话，只有将数据存储在NDB引擎表中才可以同步到个个节点中。
而其他引擎比如innodb myisam引擎的数据是不能在NDB集群中同步的。

Ndb支持的并不是btree而是tree，tree树索引也是一种平衡二叉树结构，主要是被使用在了内存类型的数据库中。
一般使用在需要数据完全同步的高可用场景中。

问：INNODB 存储引擎理解

BDB引擎也支持事务，和innodb区别是InnoDB是行锁定的而BDB是页锁定的。

支持事务
Innodb和其他引擎相比，最大的特点就是支持事务。
完全支持事务的 原子性、隔离性、持久性、一致性 的特点。
换句话说呀，如果我们的业务中有需要用到事务的场景，那我们首选的应该都是innodb引擎。

并且一定要注意，在要用到事务的场景中一定不能混合使用事务型存储引擎和非事务型存储引擎。
一旦出现事务回滚，非事务表中的数据是无法被恢复的。这样就破坏了事务一致性。

聚集索引
和myisam堆表方式不同的地方是innodb使用的是基于主键顺序存储的，也就是说innodb使用的是聚集索引。

那么我们在选择哪个列做为表中的主键就要特别注意了。
和堆存储方式不同，在具有聚集索引主键的表中，每一个非主键索引的叶子节点，所指向的都是数据行中的主键，而不是数据行的物理存储位置。所以主键的大小会直接影响数据的查找性能。

另一方面，数据是按照主键顺序存储的，如果主键经常是无规则的变化，在后期迁移也会带来io上的一些影响。
所以一般情况下，我们如果使用innodb引擎表的话，都是建议使用自增id列作为表的主键。

支持行锁
Innodb引擎的表在读取数据的时候，只会在需要的行上加锁，不会向myisam一样对整个表加锁，这也就大大增强了innodb的并发能力。

MVCC
另外innodb的表还支持mvcc多版本并发控制，可以避免读写操作的相互阻塞。

索引支持
除了支持btree索引以外还支持自适应hash索引。什么是自适应hash索引呢，这就是innodb根据数据的统计信息在内存中会建立一种hash索引，这种hash索引也能用于等值查找，这种索引是不需要dba人工干预的，这种索引完全是有innodb引擎建立管理的。
在mysql5.6版本以后呢innodb也支持全文索引5.7版本后支持了空间索引。

使用场景
大多数OLTP场景

问：innodb可以在线修改表结构吗？

在线处理就是这个表在使用时对表进行DDL操作，而且不会阻塞表的性能。
在mysql5.5版本中如果给表加个索引或修改一下表结构就会对表的DML阻塞掉。

Innodb不支持在线修改表结构的场景：

修改表结构包括增加、修改删除索引，这都是平时的日常操作。
比如在给innodb加第一个全文索引（create fulltext index name on table (column)）或加第一个空间索引（alter table biao ADD spatial index(column)）时，由于需要额外增加一个隐藏列，用于记录索引的信息，所以需要系统自动重新建表，这时候无法在进行DDL操作，第二次创建就可以在线操作了。

Innodb是按照主键逻辑顺序存储的，当我们修改主键的时候必定会对表中的数据重新排列，所以也无法在线进行删除主键的操作。比如：Alter table 表 drop primary key

和删除主键相反的就是增加一个自增列。在给innodb增加自增列必定会改变数据存储顺序，所以也不能在线增加
Alter table 表 add column id int auto_increment not null primary key.

修改列类型，比如将char改成int，因为会对数据进行校验，所以也不能在线操作。
修改表字符集，不同字符集存储字节不同，所以也不能在线修改。

问：在线ddl存在什么问题？

目前还是一部分情况是不支持在线ddl的
在一个异步复制的环境中，长时间的ddl操作会带来严重的主从延迟
无法对ddl进行资源显示，比如磁盘、cpu、io的限制。

而且ddl操作会占用一定的零时磁盘空间，当对一个大表进行ddl时，很容易的就会出现磁盘临时目录空间不足的情况，然后导致ddl操作失败。

问：如何更安全的在线修改表结构

使用pt-online-schema-change 工具。
原理是它会先创建一个修改后的新表，然后在分批次的将旧表中的数据导入到新表，数据导入完成后在把新表和旧表做一次重命名，就完成了表结构修改了。在整个过程中，只有对表重命名的时候会对原表有一个短暂的锁定。而在copy的过程中并不会影响原表数据的读写操作。
由于的分批就行的copy，所以也不会带来很大的主从延迟和cpu消耗。所以在对大表进行修改的时候，pt online是很不错的。常用参数–alter --charset --execute

问：Innodb 引擎是如何实现事务的

什么是事务？事务有哪些特点？
通俗的说，事务就是一组要执行是SQL，这组SQL要么都成功要么都失败。用银行汇款举个例子，既要保证A账户的钱正确扣除了，也要保证B账户的钱正确收到。之间有任何一个环节出现错误，这个汇款就不能完成。
事务也是关系型数据库与NoSQL数据库最主要的一个区别。

四个特性

原子性：A
事务中的操作要么都做，要么都不做。
实现方式：使用 undo log来实现，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

一致性：C
在一个事务开始到结束，数据库的完整性不能被破坏。
实现方式：通过redo log回滚、恢复以及并发情况下的隔离性，从而实现一致性。

隔离性：I
隔离性也可以称为是并发控制，事务之间执行都是相互独立，互补干扰的。事务对数据的修改，在没有提交之前是不可见的。
实现方式：通过共享锁和排它锁以及MVCC来实现。
事务隔离性分为四个级别：
级别从低到高分为：读未提交、读提交、可重复读、串行化，四个级别中只有读未提交会出现脏读，只有串行化不会出现幻读。前两个都是不可重复读。SHOW VARIABLES LIKE ‘%ISO%’ tx_isolation

持久性：D
事务对数据的修改的持久化到磁盘的。
实现方式：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
事务之间不隔离会有哪些问题？
脏读：强调的是第二个事务读到的不够新。脏读意思是读取了其他事务没有提交的数据，主要针对于单条数据。

个人理解：事务A开启进行了查询数据，同时事务B开启，修改了其中一笔数据，但并未提交，这时事务A又进行了查询，这时就有两笔不一样的数据，然后事务B并没有结束，事务B进行了事务回滚，这样事务A就读取了事务B修改后未提交的数据。因为这里出现这种根本原因是未对数据进行提交，就进行了读取。
需进行读提交（Read Committed）就能解决。

幻读：幻读的重点在于新增或者删除，同样的条件 , 第 1 次和第 2 次读出来的记录数不一样。对表加锁就可以解决。
个人理解：事务A对数据进行查询，这时事物B开启，对其中一笔数据进行了新增，然后进行了提交（这里进行了提交），然后事务A又对数据进行了查询，发现查询所得的结果集是不一样的。幻读针对的是多笔记录。
读提交（Read Committed）是不足以解决的，需进行Serializable 序列化就能解决。

不可重复读：不可重复读的重点是修改，同一事务，两次读取到的数据不一样。
个人理解：事务A对数据进行查询，这时事物B开启，对其中一笔数据进行了修改，然后进行了提交（这里进行了提交），然后事务A又对数据进行了查询，发现同一笔不同了，所以事务A读取了两笔不同的数据，两次读取同笔数据有了不同的数据。出现这种根本原因是在事务A进行读操作时，其他事务对数据进行了修改。
读提交（Read Committed）是不足以解决的，需进行可重复读（Repeatable read）就能解决。

不可重复读和幻读的区别

从总的来看，两者都是对数据进行了两次查询，但两次查询的结果都不一样。
但如果你从控制的角度来看, 两者的区别就比较大
对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

避免不可重复读需要锁行。
避免幻影读则需要锁表。

确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复 读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会 发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

这种加锁说的是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

四种隔离级别

Read uncommitted
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
Read committed
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。
解决了脏读，但不能解决不可重复读和幻读。
Repeatable read
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作
解决了不可重复读，但不能解决幻读。
Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低，像Serializeble这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况来，在MYSQL数据库中默认的隔离级别是Repeatable read（可重复读）。

在MYSQL数据库中，支持上面四种隔离级别，默认的为Repeatable read(可重复读)；而在Oracle数据库中，只支持Serializeble(串行化)级别和Read committed(读已提交)这两种级别，其中默认的为Read committed级别。

事务实现原理

redo log和undo log来保证事务的原子性、一致性和持久性，同时采用预写式日志（WAL）方式将随机写入变成顺序追加写入，提升事务性能。而隔离性是通过锁技术来保证的。

redo log是重做日志，undo log是回滚日志，它们都算是用来回滚恢复的日志。
redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页。
undo log 根据每行记录进行记录。

Undo log 记录数据修改前的状态
Redo log 记录数据修改后的状态

redo log 它包含两部分

一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；
二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

当需要修改事务中的数据时，先将对应的redo log写入到redo log buffer中，然后才在内存中执行相关的数据修改操作。InnoDB通过“force log at commit”机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有redo log都写入到磁盘上的redo log file中，然后待事务的commit操作完成才算整个事务操作完成。

在每次将redo log buffer中的内容写入redo log file时，都需要调用一次fsync操作，以此确保redo log成功写入到磁盘上（参考下图，内容的流向为：用户态的内存->操作系统的页缓存->物理磁盘）。因此，磁盘的性能在一定程度上也决定了事务提交的性能。这里还可以通过innodb_flush_log_at_trx_commit来控制redo log刷磁盘的策略。

undo log 有2个功能

实现回滚和多版本并发控制（MVCC, Multi-Version Concurrency Control）。

在数据修改的时候，不仅记录了redo log，还记录了相对应的undo log，如果因为某些原因导致事务失败或回滚了，可以借助该undo log进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

说到undo log，就不得不顺带提一下MVCC了，因为MVCC的实现依赖了undo log。当然，MVCC的实现还依赖了隐藏字段（DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID）、Read View等。

MVCC的全称是多版本并发控制，它使得在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大技术，因为这样的一来的话查询就不用等待另一个事务释放锁，使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

这里的读指的是“快照读”。普通的SELECT操作就是快照读，有的地方也称之为“一致性读”或者“一致性无锁读”。它不会对表中的任何记录做加锁动作，即不加锁的非阻塞读。快照读的前提是隔离级别不是串行化级别，串行化级别下的快照读会退化成当前读。之所以出现快照读的情况，是基于提高并发性能的考虑，这里可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。当然，既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

对应的还有“当前读”。类似UPDATE、DELETE、INSERT、SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE这些操作就是当前读。为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
锁技术
并发事务的读-读情况并不会引起什么问题（读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生），不过对于写-写、读-写或写-读这些情况可能会引起一些问题，需要使用MVCC或者加锁的方式来解决它们。

在使用加锁的方式解决问题时，既要允许读-读情况不受影响，又要使写-写、读-写或写-读情况中的操作相互阻塞。这里引入了两种行级锁：

共享锁：英文名为Shared Locks，简称S锁。允许事务读一行数据。
排它锁：也常称独占锁，英文名为Exclusive Locks，简称X锁。允许事务删除或更新一行数据。
假如事务A首先获取了一条记录的S锁之后，事务B接着也要访问这条记录：1) 如果事务B想要再获取一个记录的S锁，那么事务B也会获得该锁，也就意味着事务A和B在该记录上同时持有S锁；2) 如果事务B想要再获取一个记录的X锁，那么此操作会被阻塞，直到事务A提交之后将S锁释放掉。

如果事务A首先获取了一条记录的X锁之后，那么不管事务B接着想获取该记录的S锁还是X锁都会被阻塞，直到事务A提交。

除了 S锁 和 S 锁兼容，其他都不兼容。

InnoDB存储引擎还支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为此，InnoDB存储引擎引入了意向锁（表级别锁）：

意向共享锁（IS 锁）：事务想要获取一张表的几行数据的共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX 锁）：事务想要获取一张表中几行数据的排它锁，事务在给一个数据行加排它锁前必须先取得该表的 IX 锁。

当我们在对使用InnoDB存储引擎的表的某些记录加S锁之前，那就需要先在表级别加一个IS锁，当我们在对使用InnoDB存储引擎的表的某些记录加X锁之前，那就需要先在表级别加一个IX锁。IS锁和IX锁的使命只是为了后续在加表级别的S锁和X锁时判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。

锁粒度
MySQL中不同的存储引擎支持不同的锁机制：MyISAM与MEMORY存储引擎采用表级锁；BDB存储引擎采用的是页级锁，也支持表级锁；InnoDB存储引擎既支持行级锁，也支持表级锁，默认采用行级锁。

1:表级锁： MySQL中开销最小的策略，加锁速度快，锁定整张表，粒度大。不会出现死锁，发生锁竞争的概率最高，并发度最低，性能最差。

2:行级锁： 开销大，加锁速度慢，锁定一行数据，粒度小。会出现死锁，发生锁竞争的概率最低，并发读最高，性能高。

3:页级锁： 开销和加锁速度介于表锁和行锁之间，锁定一页数据。会出现死锁，锁竞争概率、并发性、性能均位于表锁和行锁之间。
InnoDB中 3 种行锁的算法
Record Locks（记录锁）：单个行记录上的锁。

Gap Locks（间隙锁）：在记录之间加锁，或者在第一个记录之前加锁，亦或者在最后一个记录之后加锁，即锁定一个范围，而非记录本身。

Next-Key Locks：结合 Gap Locks 和 Record Locks，锁定一个范围，并且锁定记录本身。
主要解决的是REPEATABLE READ 隔离级别下的幻读问题。

对于Next-Key Locks，如果我们锁定了一个行，且查询的索引含有唯一属性时（即有唯一索引），那么这个时候InnoDB会将Next-Key Locks优化成Record Locks，也就是锁定当前行，而不是锁定当前行加一个范围；如果我们使用的不是唯一索引锁定一行数据，那么此时InnoDB就会按照本来的规则锁定一个范围和记录。还有需要注意的点是，当唯一索引由多个列组成时，如果查询仅是查找其中的一个列，这时候是不会降级的。InnoDB存储引擎还会对辅助索引的下一个键值区间加Gap Locks（这么做也是为了防止幻读）。

问：MyISAM 与 InnoDB 有哪些区别？

从数据存储结构分析
MyISAM：每个 MyISAM 在磁盘上存储成三个文件。
.frm 存储表定义
.MYD（MYData）数据文件
.MYI（MYIndex）索引文件

InnoDB 表的所有信息都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件 ），后缀为 .frm（前提是不开启独立表空间的情况下，如果开启独立表空间会另外一个文件，后缀为 .idb），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。

从索引实现分析：
InnoDB（ 索引组织表 ）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据。
MyISAM（ 堆组织表 ）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引。

从存储空间分析：
MyISAM 可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
从可移植性、备份及恢复分析：
MyISAM 数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了。

从事务支持分析：
MyISAM 强调的是性能，每次查询具有原子性，其执行数度比 InnoDB 类型更快，但是不提供事务支持。
InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务（commit）、回滚（rollback）和崩溃修复能力（crash recovery capabilities）的事务安全（transaction-safe（ACID compliant））型表。
AUTO_INCREMENT分析：
MyISAM 可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
InnoDB 中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

从锁的角度分析：
MySQL 支持三种锁定级别，行级、页级、表级。
MyISAM 只支持表级锁，用户在操作 MyISAM 表时，select、update、delete、insert 语句都会给表自动加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插入新的数据。
InnoDB 支持事务和行级锁，是 InnoDB 的最大特色。行锁大幅度提高了多用户并发操作的新能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁全表的。

从全文索引分析：
5.7之前MyISAM 支持 FULLTEXT 类型的全文索引。
InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 Sphinx 插件支持全文索引，并且效果更好。

从表主键分析：
MyISAM 允许没有任何索引和主键的表存在，索引都是保存行的地址。
InnoDB 如果没有设定主键或者非空唯一索引，就会自动生成一个 6 字节的主键（用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值。
从表具体行数分析：
MyISAM 保存有表的总行数，如果 select count() from table; 会直接取出该值。
InnoDB 没有保存表的总行数，如果使用 select count() from table; 就会遍历整个表，消耗相当大，好消息是 5.7 MySQL 对 count 做了优化，有辅助索引就不在回表查询。但是在加了 WHERE 条件后， MyISAM 和 InnoDB 处理的方式都一样。

从 CURD 操作分析：
MyISAM 如果执行大量的 SELECT，MyISAM 是更好的选择。
InnoDB 如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用 InnoDB 表。
DELETE 从性能上 InnoDB 更优，但 DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除，在 InnoDB 上如果要清空保存有大量数据的表，最好使用 truncate table 这个命令。

从外键分析：
MyISAM 不支持
InnoDB 支持

从读写阻塞角度分析：
MyISAM 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM 还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
InnoDB 读写阻塞与事务隔离级别相关。
通过上述的分析，基本上可以考虑使用 InnoDB 来替代 MyISAM 引擎了。
原因是 InnoDB 自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信 InnoDB 的表现肯定要比 MyISAM 强很多。
另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥 MySQL 的性能优势。
如果不是很复杂的 Web 应用，非关键应用，还是可以继续考虑MyISAM 的，这个具体情况可以自己斟酌。

MyISAM 与 InnoDB 如何选择更好呢？

MyISAM

不需要事务支持（ 不支持 ）
并发相对较低（ 锁定机制问题 ）
数据修改相对较少（ 阻塞问题 ），以读为主
数据一致性要求不是非常高
尽量索引（ 缓存机制 ）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让 insert 数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用 Query Cache 可以极大的提高访问效率
MyISAM 的 Count 只有在全表扫描的时候特别高效，带有其他条件的 count 都需要进行实际的数据访问

InnoDB

需要事务支持（ 具有较好的事务特性 ）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用 InnoDB 较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
主键尽可能小，避免给 Secondary index 带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用 autocommit 自动提交
合理设置 innodb_flush_log_at_trx_commit 参数值，不要过度追求安全性避免主键更新，因为这会带来大量的数据移动

问：为什么MyISAM会比 InnoDB 的查询速度快？

InnoDB 在做 SELECT 的时候，要维护的东西比 MyISAM 引擎多很多。
InnoDB 要缓存数据和索引，MyISAM只缓存索引块，这中间还有换进换出的减少。
InnoDB 寻址要映射到块，再到行。
MyISAM 记录的直接是文件的 OFFSET ，定位比 InnoDB 要快。
InnoDB 还需要维护 MVCC 一致，虽然你的场景没有，但它还是需要去检查和维护。

问：MVCC（Multi-Version Concurrency Control）多版本并发控制

InnoDB 通过为每一行记录添加两个额外的隐藏的值来实现 MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（ 或者被删除 ）。

但是 InnoDB 并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。

每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。

REPEATABLE READ 隔离级别时 SELECT InnoDB 引擎的表，每行数据必须的保证它符合两个条件
1.InnoDB 必须找到一个行的版本，它至少要和事务的版本一样老（也即它的版本号不大于事务的版本号）。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
2.这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

问：InnoDB 引擎中数据的存储方式是什么样的？

聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。
InnoDB 的主键一定是聚集索引，如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。

问：InnoDB 表对主键生成策略是什么样的？

优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键。
InnoDB 存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（ 在没有自定义主键以及 Unique 键的情况下才会添加该列 ）。
这些隐藏列的值不用我们操心，InnoDB 存储引擎会自己帮我们生成的。

问：AUTO_INCREMENT 有哪些约束

AUTO_INCREMENT 是 Innodb 提供的一种可配置的锁定机制，如果某个表的某一列具有 AUTO_INCREMENT 约束，那么向该表添加数据的时候可以很明显的提高 SQL 语句的性能和可伸缩性。
为了充分使用 Innodb 表的 AUTO_INCREMENT 机制，必须 将 AUTO_INCREMENT 字段定义为 「 索引 」 的一部分，这样就可以在表上使用索引执行下面的语句来查找最大的列值。
SELECT MAX (ai_col ) FROM tablename;

通常情况下，为了最大化性能，添加了 AUTO_INCREMENT 约束的列要么独自成一个索引 ( 主索引 )，那么是组合索引中的第一列

AUTO_INCREMENT 不仅仅是一个字段约束条件，它还是一个 「 锁 」，也就是那个很少见到的 「 AUTO_INCREMENT 锁」。

问：Innodb AUTO_INCREMENT 锁的模式是什么

使用了 AUTO_INCREMENT 那么多次，它的主要作用就是产生一个不重复的 「 自增值 」。

插入多条数据有两种插入方法，一种是一条一条的执行 INSERT INTO，另一种是 INSERT INTO VALUES(…),(…) 多条一起插入

这两种插入方法都能正确的自增 AUTO_INCREMENT 列，底层实现就是AUTO_INCREMENT 锁，为了适应这两种插入方法，它同时也具有多种模式。
问：给表插入数据的常用语句有哪些？
常用的有
INSERT, INSERT ... SELECT
REPLACE
REPLACE ... SELECT
LOAD DATA

总体可以归纳为三类「 simple-inserts 」、「 bulk-inserts 」和 「 mixed-mode 」

「 simple-inserts 」 是可以预先确定要插入的行数的语句。包括不带子查询的 单行 和 多行 INSERT 和REPLACE 语句，但不包括 INSERT ... ON DUPLICATE KEY UPDATE 语句。

「 Bulk inserts 」是预先不知道要插入的行数的语句。
包括 INSERT ... SELECT，REPLACE ... SELECT 和 LOAD DATA 语句，但不包括普通的 INSERT
在处理每一行时，InnoDB 都会重新为 AUTO_INCREMENT 列分配一个新值

「 Mixed-mode inserts 」 是指「 simple-inserts 」 语句中，有些指定了 AUTO_INCREMENT 列的值，而另一些则没有。
例如下面的 SQL 语句，其中 c1 是表 t1 的 AUTO_INCREMENT 列
INSERT INTO t1 (c1,c2) VALUES (1,‘a’), (NULL,‘b’), (5,‘c’), (NULL,‘d’);

另一种类型的 「 Mixed-mode inserts 」 是 INSERT ... ON DUPLICATE KEY UPDATE ，这种语句最坏的情况下实际上是 INSERT 后跟 UPDATE，其中在更新阶段，可能会也可能不会为 AUTO_INCREMENT 列的分配值

问：innodb_autoinc_lock_mode 是如何配置的

「 AUTO_INCREMENT 锁」模式的配置变量为 innodb_autoinc_lock_mode。
show variables like ‘innodb_autoinc_lock_mode’;

配置参数 innodb_autoinc_lock_mode 有三个可选的值，分别是 0、1 和 2 ，分别代表着 「 传统 」，「 连续 」 或 「 交错 」 三种锁模式

在不同的版本下，innodb_autoinc_lock_mode 的默认值是不一样的，5.7中默认是1，在 mysql >= 8.0.3 版本中是 2，也就是 「 交错 」 模式，而 mysql <= 8.0.2 版本中是 1，也就是 「 连续 」 模式

对于 8.0.3 版本中的这种变更，也反应了 Innodb 的默认 「 复制模式 」 已经从基于 SQL 语句 变更为基于 行 ( row )

基于 SQL 语句的复制需要 「 连续 」 模式的 「 AUTO_INCREMENT 锁」，确保为给定的 SQL 语句序列以可预测和可重复的顺序分配自动增量值，而基于行的复制对 SQL 语句的执行顺序不敏感。

innodb_autoinc_lock_mode = 0 传统锁模式

传统模式是5.1的默认配置，现在，传统锁模式存在的意义，仅仅是用于向后兼容，性能测试以及解决 「 混合模式插入 」 问题。

在传统锁模式下，所有的 「 insert like 」 语句都会获得一个特殊的 表级 AUTO-INC 锁，这种锁会自动添加到 SQL 语句的末尾 ( 不是事务的末尾 )，以确保以可预测且可重复的顺序为给定的 INSERT 语句序列分配自增值，并确保为任何给定语句分配的自增值都是连续的。

在基于 SQL 语句的 ( 主从 ) 复制环境中，在从服务器上运行复制 SQL 语句时，自增量列的值和主服务器的值相同，这样执行多个 INSERT 语句的结果是确定性的，并且从服务器的数据和主服务器的数据一摸一样。

如果多个 INSERT 语句生成的自增值是交错的，那么两个并发 INSERT 语句的结果将是不确定的，这样就无法使用基于 SQL 语句的复制模式将数据可靠地复制到从服务器。

看看一些示例，假设存在一张表 t1，它的建表语句如下

CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)) ENGINE=InnoDB;

把表建完后，我们假设有两个事务在运行，两个事务都是往 t1 表中插入数据，第一个事务使用一个事务使用 INSERT … SELECT 语句插入 1000 行的，另一个事务使用简单的 INSERT 语句插入一行数据

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table …
Tx2: INSERT INTO t1 (c2) VALUES (‘xxx’);

第一个事务 Tx1 ，因为 InnoDB 无法预先判断在 INSERT 语句从 SELECT 中检索了多少行，所以它会在每插入一条数据的时候分配一个自增值。这种情况下，会使用 表锁 ，会在 SQL 语句的末尾自动添加一个表锁，一次只能在表 t1 执行一条 INSERT 语句，这样就能保证每条 INSERT 语句的自增值是连续的且不会交错。

这样由 Tx1 INSERT … SELECT 语句生成的自增值是连续的，并且 Tx2 事务中 INSERT 语句使用的 ( 单个 ) 自增值要小于或大于 Tx1 的所有自增值，结果具体取决于哪个语句先执行

这时候在主从复制或数据恢复时，只要以二进制日志重放 SQL 语句时 ( 使用基于语句的复制时或恢复方案中）以相同的顺序执行，那么重放的结果与 Tx1 和 Tx2 首次运行时的结果相同

如果前面的示例没有使用 「 表锁 」 ，那么 Tx2 中 INSERT 的自增列的值取决于语句执行的时间。如果 Tx2 的 INSERT 在 Tx1 的 INSERT 运行时 ( 而不是在它开始之前或完成之后 ) 执行，则两个 INSERT 语句分配的特定自增值是不确定的，并且可能因运行而异。

在 「 连续锁 」模式下，InnoDB 可以避免将表级 AUTO-INC 锁用于 「 insert like 」 语句，因为行数已预先知道，而且还可以确保基于语句的复制的确定性执行和安全性

在恢复或复制数据的重放 SQL 语句时如果不使用二进制日志，那么可以使用 「 交错锁 」模式用来消除表级 AUTO-INC 锁的使用，以获得更高的并发性和性能，但代价语句分配的自增值数字可能不是连续的，而且可能因为并发的执行而存在重复的数字。

innodb_autoinc_lock_mode = 1 连续锁模式

在此 「 连续锁 」 模式下，「 批量插入 」会首先获取一个特殊的表级 AUTO_INC 锁，并一直保持到所有语句执行结束才释放。连续锁模式适用于所有的批量插入语句，包括 INSERT … SELECT、REPLACE … SELECT 和 LOAD DATA 语句。

Innodb 存储引擎一次只会执行一个持有 AUTO-INC 锁的 SQL 语句。
如果批量插入操作的源表与目标表不同，则会先在源表中选择的第一行上执行共享锁，然后对目标表执行 AUTO-INC 锁。如果批量插入操作的源表和目标表是同一个表，则会先对所有选定的行执行共享锁之后再执行 AUTO-INC 锁

简单插入 「 Simple inserts 」 ( 即预先知道要插入的行数 ） 则是不同的，它会在互斥锁 ( 一个轻量级的锁 ) 的控制下获得所需数量的自增量值来避免表级 AUTO-INC 锁定。但这种获得自增值的互斥锁只在分配过程的持续时间内持有，而不是在语句完成之前。除非另一个事务首先持有 AUTO-INC 锁，否则不使用表级 AUTO-INC 锁。如果另一个事务持有 AUTO-INC 锁，则 「 简单插入 」会一直等待直到自己获得 AUTO-INC 锁，就好像它自己也是批量插入一样

这种锁定模式可以确保，在存在 INSERT 语句情况下，纵使事先不知道行数 ( 以及在语句执行时分配的自增值数 )，任何由 「 INSERT-like 」 分配的所有自增值都是连续的，且确保对基于语句的复制操作是安全的。

简而言之，这种锁模式显着提高了可伸缩性，同时可以安全地进行基于语句的复制。此外，与 「 传统锁」 模式一样，可以确保为任何给定语句分配的自增值数字是连续的。
从某些方面说，与 「 传统锁 」 模式相比，对于任何使用自增值的语句，语义并没有变化，除了下面这个重要的例外
这个例外就是 「 混合模式插入 」 ( mixed-mode inserts ) ， 在混合模式插入中，那些多行的 「 简单插入 」中的某些行 ( 但不是所有行 ) 会显式为 AUTO_INCREMENT 列提供一个值。对与这种插入模式，InnoDB 会分配比要插入的行数更多的自增值。
但这样也存在一个问题 ( 可以被忽略的问题 )，因为自动分配的所有值都是连续生成，因此可能高于最后执行的语句生成的自增值，也就是超过的没用到的自增值则被丢失了。

innodb_autoinc_lock_mode = 2 交错锁模式

在 「 交错锁 」模式下，任何 「 INSERT-like 」语句都不会使用表级 AUTO-INC 锁，且多个语句可以同时执行。
这是最快且可扩展性最强的锁模式，但在二进制日志重放 SQL 语句中，会让使用基于语句
的复制或恢复方案变得不安全。
这种锁模式，会确保自增值是唯一的，且可以在所有同时执行的 「 INSERT-like 」 语句中保持单调递增
当然了，这种锁模式也是有缺点的，因为多个语句可以同时生成数字 (即自增值数字的分配会在语句之间交错进行 ) ，会造成任何给定语句插入的行生成的值可能不是连续的。

三种锁模式

1、 传统锁模式 – 不管三七二十一，先用表级的 AUTO-INC 锁，直到语句插入完成，然后释放锁
2、 连续锁模式 – 不管三七二十一，先用互斥锁，然后生成所有插入行需要的自增值，然后释放互斥锁，最后使用这些自增值来插入数据。对于行数未知的，那就只能使用 「 传统锁 」 模式了，先锁起来，执行完毕了再释放，因为人家不知道要生成多少自增值啊
3、 交错模式 – 管它刮风下雨，需要的时候再生成，也管它连续与否，用了就是了
自增锁三种模式的缺点

MySQL Innodb AUTO_INCREMENT 锁的三种模式，分别为 「 传统模式 」 、 「 连续模式 」 、「 交错模式 」 ，这三种模式我们可以用同学聚会定餐馆来形象的描述下
1、「 传统模式 」 – 老板，你家的店我包了，等到我同学聚会完了你再招待其它客人啊….其它客人：坑啊！ 你们快点吃啊，慢吞吞的…
2、 「 连续模式 」 – 老爸，我同学总共 50 个人，先给我来 50 个位置，有些同学可能没来，那就空着。剩下的位置，你可以招待其它的同学。有时候，如果不知道有多少同学 ( 健忘症犯了… )，就只能再回到传统模式了。
3、 「 交错模式 」 – 老板，我也不知道会来几个同学，反正，来了一个就坐一个位置，其它客人来了也可以坐 ( 假设老板家座位足够 )

这三种锁模式的优缺点是啥 ？

1、 「 传统模式 」 能保证所有的自增值是连续的，且不会浪费 ( 也就是会一直 123456789…自增下去，不会断掉 )，但其它的事务或语句要等到当前语句执行完才会继续执行
2、 「 交错模式 」解决了要 「 传统模式 」中要等待的问题，但也会引入新问题，就是可能造成自增值出现断层，比如 12345 缺了 4 这样。同时，在指定插入数据条数不确定的情况下，会回到 「 传统模式 」
3、 「 交错模式 」 解决了自增值断层的问题，但引入了自增值顺序混乱的问题，可能会导致自增值如下 13245687
交错模式在日常的 SELECT 语句中是不会出啥问题的，因为会按照自增值排序，出问题就处恢复数据或主从过程中的二进制日志回放，可能导致从库或者恢复的数据的自增值和源数据不一致。

MySQL Innodb AUTO_INCREMENT 锁的使用说明

主从复制中的 AUTO_INCREMENT
主从复制时，如果你使用的是基于 SQL 语句的复制，需要将 innodb_autoinc_lock_mode 配置项的值设置为 0 或 1 ，而且主服务器和从服务器上的值必须相同。
如果设置 innodb_autoinc_lock_mode = 2，也就是使用 「 交错锁 」 模式，或者主从服务器上的值不相同，则不能确保从服务器上相同行的自增值和主服务器相同
如果你使用的是基于 行 ( row ) 或混合模式的主从复制，那么使用任何一个 AUTO_INCREMENT 锁模式都是安全的，因为基于行的复制对 SQL 语句的执行顺序不敏感。
在 交错锁 模式下，主从复制来说，任何基于语句的复制都是不安全的
注意： 混合模式使用的是基于行的复制方式。

无论你使用的是哪种锁模式 ( 0 , 1 或 2 )，如果生成自增值的事务回滚，则这些自增量值将 「 丢失 」。
一旦为 AUTO_INCREMENT 列生成一个自增值后，无论 「 INSERT-like 」 语句是否完成，以及包含事务是否回滚，都无法回滚该自增值。
这些丢失的自增值不会被重复使用。因此，表的 AUTO_INCREMENT 列中的值可能存在间隙

为 AUTO_INCREMENT 列指定 NULL 或 0 时候
无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为 NULL 或 0，那么 Innodb 将会忽略这些值并为该列生成一个新的自增值。
也就是说，不管是否为 AUTO_INCREMENT 列指定了值为 NULL ，还是指定了值为 0 ，或是未指定，Innodb 都会自动生成一个自增值作为该列的值。

如果 AUTO_INCREMENT 列的值超过规定范围会抛出错误
1264 - Out of range value for column ‘id’

AUTO_INCREMENT 计数器是如何初始化

如果为 InnoDB 表指定了 AUTO_INCREMENT 列，那么内存中，该表对象会包含一个称为 「 自增值计数器 」 的特殊计数器，用于为 AUTO_INCREMENT 列分配新值时使用。

在 MySQL 5.7 及更早版本中，自增值计数器仅存储在内存中，而不是磁盘上。
为了在 MySQL 启动或重新启动后初始化 自增值计数器，在往含有 AUTO_INCREMENT 列的 InnoDB 表中第一次插入数据之前，会执行以下语句获取当前的 自增值
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
这个操作是隐式执行的，就是在插入时，如果 Innodb 发现内存中该表对象不包含自增计数器的时候，会执行上面的 SQL 语句来获取这个自增值

在 MySQL 8.0 中，此行为已经变更了。每次更改时，会将当前自增值计数器的最大值将写入重做 ( redo ) 日志，并保存到每个检查点上的引擎专用系统表中。这种变更使得自增值计数器的当前最大值会在重启时保持不变。

在原来的模式中，重启前，可能内存中的自增值计数器已经到了 1000+ ，但表中的实际最大值可能只有 100，那么重启后，自增值就会停留在 100，而 8.0 的变更中，最大值仍会保持重启前的 1000+

当正常关闭 MySQL 服务器后再重新启动它时，InnoDB 会使用存储在数据字典系统表中的当前最大自增值来初始化内存中的自增值计数器。
如果服务器发生了非正常崩溃，在崩溃恢复期间重新启动服务器时，InnoDB 仍会使用存储在数据字典系统表中的当前最大自增值初始化内存中自增值计数器，并且会扫描重做日志 ( redo log ) 以查找自上一个检查点以来写入的自增计数器的值。如果重做日志值大于内存中计数器值，则会应用重做日志值。
因此，在服务器崩溃的情况下，无法保证重用先前分配的自增值
当每一次执行 INSERT 或 UPDATE 操作会更改当前最大自增值时，会将新值将写入重做日志。但如果在将重做日志刷新到磁盘之前发生崩溃，那么在重新启动服务器后初始化自增计数器时可能会重用以前分配的值。

在 MySQL 5.7 及更早版本中，服务器重新启动时会忽略执行表选项中的 AUTO_INCREMENT = N，该选项一般用来在 CREATE TABLE 或 ALTER TABLE 语句中用于设置初始计数器值或更改现有计数器值。
但在 MySQL 8.0 及更高的版本中，服务器重新启动时并不会忽略表选项 AUTO_INCREMENT = N。 如果将自增计数器初始化为特定值，或者将自增计数器值更改为更大的值，则新值会在服务器重新启动时保持不变。

ALTER TABLE … AUTO_INCREMENT = N 语句仅仅只能用于将自增计数器的值修改为大于当前计数器的最大值，如果小于则是没有任何效果的。

在 MySQL 5.7 及更早版本中，服务器在 ROLLBACK 操作之后立即重新启动可能会导致重用先前分配给回滚事务的自增值，从而可以有效地回滚当前最大自增值。

但在 MySQL 8.0 中，当前的最大自增量值是持久的，从而阻止了重用以前分配的值。

如果使用 SHOW TABLE STATUS 语句在初始化自增计数器之前检查表，InnoDB 将打开表并使用存储在数据字典系统表中的当前最大自增值初始化计数器值。并将该值存储在内存中以供以后插入或更新使用。

启动或重新启动服务器时，初始化自增计数器会使用表上的常规独占锁 ( for update ) 读取，并且会持有该独占锁直到事务结束。在初始化新创建的表的自增计数器时，InnoDB 遵循相同的过程。当然了，新创建的表可以使用 AUTO_INCREMENT = N 选项指定一个大于 0 的自增值

初始化自增计数器完成后，如果在插入行时未显式指定自增值，InnoDB 会隐式递增计数器并将新值分配给 AUTO_INCREMENT 列

只要服务器一直在运行，InnoDB 就会使用内存中的自增计数器。当服务器停止并重新启动时，InnoDB 会重新初始化自增计数器，如前所述。
my.cnf 配置文件中的 AUTO_INCREMENT
在 my.cnf 配置文件中，可以使用 auto_increment_offset 配置选项确定 AUTO_INCREMENT 列值的起始点。默认设置为 1
在 my_cnf 配置文件中，可以使用 auto_increment_increment 配置选项控制连续列值之间的间隔。默认设置为 1
一般情况下，在双主互相备份时，我们一般会指定一台服务器的两个配置项为
auto_increment_offset=1;
auto_increment_increment=2;
这样，这台服务器的自增值将会遵循 1 3 5 7 9 11 13 ，奇数数列
而在另一台服务器上指定
auto_increment_offset=2;
auto_increment_increment=2;
这样，这台服务器的自增值将会是 2 4 6 8 10 …. 偶数数列
这样，在互为主从的时候，就不会出现自增值重复的问题

问：InnoDB引擎中表数据是怎么存储的

假如我们创建一张test表，就会生成一个test.ibd的表空间文件。

为了保证顺序IO，表空间被划分为多个连续的数据区，256个连续的数据区称为一个数据区组，一个数据区又由64个连续的数据页组成，数据页包含数据行。

一个数据页大小为 16 KB
一个数据区大小为 64 * 16 KB = 1 MB
一个数据区组大小为 256 * 1 MB = 256 MB

在 InnoDB 中有个参数 innodb_file_per_table ，默认值为 on，表示每张表的数据单独放到一个表空间。

常见的段有数据段，索引段，回滚段。数据即索引，那么数据段即为B+树的叶子节点，索引段即为B+树的非索引节点。
段是一个逻辑概念，段最小申请内存为1MB，为了防止空间浪费，先用32个页大小的碎片页来存放数据，在使用完这些页后才是64个连续页的申请。

页是 InnoDB 磁盘管理的最小单位，大小为16KB，和操作系统的页（4KB）概念不同。

表空间第一个数据区组的第一个数据区前3个数据页是固定的，存放一些描述信息
(1) FSP_HDR：存放表空间和这一组数据区的信息
(2) IBUF_BITMAP：这一组数据页的 insert buffer 数据信息
(3) INODE：存放特殊信息
表空间里的其他各组数据区，每一组数据区的第一个数据区的头两个数据页，都是存放特殊信息
(1) XDES：记录这一组数据区的信息
(2) IBUF_BITMAP：这一组数据页的 insert buffer 数据信息

InnoDB 数据页结构

InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？

不，那样会慢死，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

File Header：文件头，记录页的一些头信息
Page Header：数据页头，记录数据页的状态信息
Supremum和Infimum：最大记录最小记录，限定数据记录的边界
User Records：数据行，实际存储行记录的内容
Free Space：空闲区域，数据页中剩余可插入数据的空间
Page Directory：数据页目录，存放数据记录的相对位置，有时候这些记录指针称为槽（Slots），我们通过B+树索引并不能找到具体的一条记录，只能找到记录所在的数据页，然后通过数据页目录进行二叉查找找到最终的数据
FileTrailer：文件尾部，校验页是否完整的写入磁盘

InnoDB 行记录格式

我们平时是以行为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。设计InnoDB存储引擎的大叔们到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式，随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的。

指定行格式的语法

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

COMPACT行格式

变长字段长度列表：字段类型为变长的字段长度，按照列顺序逆序排列
null值列表：记录字段是否为null，0：非null，1：null，占用1个字节
数据头： 40位，固定占用5个字节

隐藏列：
DB_ROW_ID：如果 Innodb 没有指定主键，会生成一个rowid列，6字节
DB_TRX_ID：事务ID，6字节
DB_ROLL_PTR：回滚指针，7字节

在InnoDB中，每张表都有一个主键，如果再创建表时没有显示的定义主键，则InnoDB会按如下方式选择或创建主键：

首先判断是否有非空的唯一索引，如果有，则该列即为主键
如果不符合上述条件，InnoDB自动创建一个6字节大小的指针。当有多个非空唯一索引时，InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键

行溢出

一个数据页大小为16KB，如果有一个表里有这样一个字段 varchar(65532) ，表示可以存放65532个字符，也就是65532个字节，远大于16KB，说明一个数据页放不下这一行数据。就需要将数据拆分到多个数据页中存放。每行数据都有个指针指向其他数据页中的溢出行。

如图所示，通过多个数据页来存储一行数据，当要读取这一行数据时，就需要加载多个数据页到缓冲池中了。
Compact格式与Redundant格式，在处理行溢出时，真实数据中只保存 768 字节的前缀，之后的数据都是偏移量，指向溢出页
Compressed格式与Dynamic格式，在处理行溢出时，数据完全溢出，在数据页只存放20字节的指针，实际的数据都存放在Off Page中

使用16进制编辑器查看 tb.ibd 文件，我们看到2行数据是连续存储的，第二行数据紧挨着第一行数据

第一行数据高亮如下
0b 05 04 ：变长字段长度，逆序
0a ：null值列表
00 00 10 00 30：数据头
00 00 00 00 29 73：RowId
00 00 00 00 3d a5：事务ID
c8 00 00 01 7b 01 10：回滚指针
31 30 30 31 1001
6d 65 6c 6f 6e melon
63 68 65 6e 67 78 75 79 75 61 6e chengxuyuan

1001字符串长度：4，用16进制表示：0x04
melon字符串长度：5，用16进制表示：0x05
chengxuyuan字符串长度：11，用16进制表示：0x0b
倒序展示为： 0b 05 04

允许为null值的有4个字段，2个null，2个非null，null用1表示
null值列表按顺序表示为：0101，倒序排列为：1010，
16进制表示为 0a

第二行数据高亮如下.

04 08 04 ：变长字段长度，逆序
06 ：null值列表
00 00 18 ff bf ：数据头
00 00 00 00 29 74 ：RowId
00 00 00 00 3d a6 ：事务ID
c9 00 00 01 3f 01 10 ：回滚指针
31 30 30 32 1002
7a 68 61 6e 67 73 61 6e zhangsan
74 65 73 74 test

1002字符串长度：4，用16进制表示：0x04
zhangsan 字符串长度：8，用16进制表示：0x08
test字符串长度：4，用16进制表示：0x04
倒序展示为： 04 08 04

允许为null值的有4个字段，2个null，2个非null，null用1表示
null值列表按顺序表示为：0110，倒序排列为：0110，
16进制表示为： 06