MySQL Innodb和Myisam

Innodb

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，架构分为两块：内存中的结构和磁盘上的结构。InnoDB 使用日志先行策略，将数据修改先在内存中完成，并且将事务记录成重做日志(Redo Log)，转换为顺序IO高效的提交事务。

这里说的是日志记录到数据库以后，对应的事务就可以返回给用户，表示事务完成。实际上这个数据可能还只在内存中修改完，并没有刷到磁盘上去。如果在数据落地前机器挂了，那么这部分数据就丢失了。

InnoDB 通过 redo 日志来保证数据的一致性。即定期检查（检查点机制），保证检查点之前的日志都已经写到磁盘，则下次恢复只需要从检查点开始。

主要优势

• 它的 DML 操作遵循 ACID 模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据。
• 行级锁定和 Oracle 风格的一致读取提高了多用户并发性和性能。
• InnoDB表将您的数据排列在磁盘上以优化基于主键的查询。每个 InnoDB表都有一个称为聚集索引的主键索引，用于组织数据以最小化主键查找的 I/O。
• 为维护数据完整性，InnoDB支持 FOREIGN KEY约束。使用外键，检查插入、更新和删除以确保它们不会导致相关表之间的不一致。

ACID 模型

ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

1、原子性方面主要涉及InnoDB事务。

• Autocommit 设置
• COMMIT 声明
• ROLLBACK 声明

2、一致性方面主要涉及InnoDB保护数据免受崩溃的内部处理，指保护数据不受系统崩溃影响。

• InnoDB 的双写缓冲区(doublewrite buffer)
• InnoDB 的故障恢复机制(crash recovery)

3、innodb的隔离性也是主要通过事务机制实现，特别是为事务提供的多种隔离级别。

• Autocommit设置
• SET ISOLATION LEVEL 语句
• InnoDB 锁机制

4、持久性方面涉及的硬件配置交互的MySQL软件功能，根据CPU、网络和存储设备的能力存在多种可能性，因此提供具体指导方针的这一方面是最复杂的。

• 双写缓冲区(doublewrite buffer)
• innodb_flush_log_at_trx_commit 变量
• sync_binlog 变量
• innodb_file_per_table 变量
• 存储设备中的写入缓冲区，例如磁盘驱动器、SSD 或 RAID 阵列
• 存储设备中的电池后备缓存
• 用于运行 MySQL 的操作系统，特别是它对fsync()系统调用的支持
• 不间断电源 (UPS) 保护运行 MySQL 服务器和存储 MySQL 数据的所有计算机服务器和存储设备的电源
• 备份策略，例如备份的频率和类型以及备份保留期
• 对于分布式或托管数据应用程序，MySQL 服务器硬件所在的数据中心的特定特征，以及数据中心之间的网络连接

多版本

InnoDB是一个多版本的存储引擎。它保留有关已更改行的旧版本的信息以支持事务功能，例如并发和回滚。

InnoDB 使用回滚段中的信息来执行事务回滚所需的撤消操作。它还使用这些信息来构建行的早期版本以实现一致读取。

InnoDB为存储在数据库中的每一行添加三个字段:

• 一个 6 字节DB_TRX_ID字段指示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，其中设置了行中的特殊位以将其标记为已删除。
• DB_ROLL_PTR称为滚动指针的 7 字节字段。回滚指针指向写入回滚段的撤消日志记录。如果该行被更新，撤消日志记录包含在更新前重建该行内容所需的信息。
• 一个 6 字节的DB_ROW_ID字段包含一个行 ID，随着插入新行而单调增加。如果 InnoDB自动生成聚集索引，则该索引包含行 ID 值。否则，该 DB_ROW_ID列不会出现在任何索引中。

回滚段中的撤消日志分为插入和更新撤消日志。插入撤消日志仅在事务回滚时需要，并且可以在事务提交后立即丢弃。更新撤消日志也用于一致性读取，但只有在没有事务存在且为其InnoDB分配快照的情况下才能丢弃它们 ，在一致性读取中可能需要更新撤消日志中的信息来构建较早版本的数据库排。

多版本和二级索引

InnoDB多版本并发控制 (MVCC) 处理二级索引与聚簇索引不同。聚集索引中的记录就地更新，它们隐藏的系统列指向撤消日志条目，可以从中重建记录的早期版本。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不会就地更新。

• 当二级索引列被更新时，旧的二级索引记录被删除标记，新记录被插入，并最终被删除标记记录被清除。

• 当二级索引记录被删除标记或二级索引页被更新的事务更新时，InnoDB在聚集索引中查找数据库记录。在聚集索引中，DB_TRX_ID检查记录，如果在启动读取事务后修改了记录，则从撤消日志中检索记录的正确版本。

如果二级索引记录被标记为删除或二级索引页被更新的事务更新， 则不使用覆盖索引技术。不是从索引结构返回值，而是InnoDB在聚集索引中查找记录。

官方架构图

内存结构

1、缓冲池

缓冲池是主内存中的一个区域，用于在 InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，多达 80% 的物理内存通常分配给缓冲池。

为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页。为了缓存管理的效率，缓冲池被实现为页的链表；很少使用的数据使用最近最少使用 (LRU) 算法的变体从缓存中老化。

1.1、缓冲池LRU算法

缓冲池使用 LRU 算法的变体作为列表进行管理。当需要空间将新页添加到缓冲池时，最近最少使用的页会被逐出，并将新页添加到列表中间。

此中点插入策略将列表视为两个子列表：

• 头部：最近访问的新（“年轻”）页的子列表
• 尾部：最近访问过的旧页的子列表

默认情况下，算法操作如下：

• 缓冲池的 3/8 专用于旧子列表。
• 列表的中点是新子列表尾部与旧子列表头部相交的边界。
• 当InnoDB将页读入缓冲池时，它最初将它插入到中点（旧子列表的头部）。可以读取页，因为它是用户启动的操作（例如 SQL 查询）所必需的，或者是由 自动执行的预读操作的一部分 InnoDB。
• 访问旧子列表中的页使其 “年轻”，将其移动到新子列表的头部。如果页是因为用户启动的操作需要它而被读取，则第一次访问会立即发生，并且页会变年轻。如果页是由于预读操作而读取的，则第一次访问不会立即发生，并且在页被逐出之前可能根本不会发生。
• 随着数据库的运行，缓冲池中未被访问的页会通过向列表尾部移动来“老化”。新旧子列表中的页随着其他页的更新而老化。旧子列表中的页也会随着页插入中点而老化。最终，一个未使用的页到达旧子列表的尾部并被驱逐。

1.2、缓冲池配置

可以通过配置缓冲池的各个方面来提高性能

• 将缓冲池的大小设置为尽可能大的值，从而为服务器上的其他进程留出足够的内存来运行而不会出现过多的分页。缓冲池越大，就越InnoDB像内存数据库，从磁盘读取数据一次，然后在后续读取期间从内存访问数据。
• 在具有足够内存的 64 位系统上，可以将缓冲池拆分为多个部分，以最大程度地减少并发操作之间对内存结构的争用。
• 可以将经常访问的数据保留在内存中，而不管操作的活动突然激增，这些操作会将大量不常访问的数据带入缓冲池。
• 可以控制如何以及何时执行预读请求以异步地将页预取到缓冲池中，以预期很快就会需要这些页。
• 可以控制何时发生后台刷新以及是否根据工作负载动态调整刷新速率。
• 可以配置如何InnoDB保留当前缓冲池状态以避免服务器重新启动后的长时间预热。

2、更改缓冲区

更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的 更改 。可能由INSERT、 UPDATE或 DELETE操作 (DML)导致的缓冲更改 稍后在其他读取操作将页加载到缓冲池时合并。

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页。稍后在其他操作将受影响的页读入缓冲池时合并缓存的更改，可避免大量随机访问 I/O，而这些 I/O 将需要将二级索引页从磁盘读入缓冲池。

在系统大部分空闲时或在缓慢关闭期间运行的清除操作会定期将更新的索引页写入磁盘。与将每个值立即写入磁盘相比，清除操作可以更有效地为一系列索引值写入磁盘块。

在内存中，更改缓冲区占据了缓冲池的一部分。在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改会在其中缓冲。

更改缓冲区中缓存的数据类型由 innodb_change_buffering 变量控制。

允许的innodb_change_buffering值有：

• all 默认值,缓冲区插入、删除标记操作和清除
• none 不要缓冲任何操作
• inserts 缓冲区插入操作
• deletes 缓冲区删除标记操作
• changes 缓冲插入和删除标记操作
• purges 在后台发生的缓冲区物理删除操作

3、自适应哈希索引

自适应哈希索引能够InnoDB在具有适当组合的工作负载和足够的缓冲池内存的系统上执行更像内存数据库，而不会牺牲事务功能或可靠性。自适应哈希索引由innodb_adaptive_hash_index 变量启用，或在服务器启动时关闭 --skip-innodb-adaptive-hash-index。

4、日志缓冲区

日志缓冲区是保存要写入磁盘上日志文件的数据的内存区域, 日志缓冲区大小由innodb_log_buffer_size变量定义, 默认大小为16MB。

日志缓冲区的内容会定期刷新到磁盘, 大型日志缓冲区使大型事务能够运行, 而无需在事务提交之前将重做日志数据写入磁盘。如果有更新、插入或删除许多行的事务, 增加日志缓冲区的大小可以节省磁盘I/O。

磁盘结构

1、创建InnoDB表

CREATE TABLE t1 (a INT, b VARCHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

ENGINE=InnoDB 当InnoDB定义为默认存储引擎时不需要该子句，默认情况下它是。

以通过发出以下语句来确定 MySQL 服务器实例上的默认存储引擎：

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

InnoDB默认情况下，表是在 file-per-table 表空间中创建的。

2、.frm 文件

MySQL 将表的数据字典信息存储在数据库目录中的 .frm 文件中。与其他 MySQL 存储引擎不同， InnoDB它还在系统表空间内自己的内部数据字典中对有关表的信息进行编码。当 MySQL 删除一个表或一个数据库时，它会删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。

不能InnoDB简单地通过移动.frm 文件在数据库之间移动表。

3、行格式

InnoDB表的行格式决定了其行在磁盘上的物理存储方式。

InnoDB支持四种行格式，每种格式具有不同的存储特性，支持行格式包括REDUNDANT、COMPACT、 DYNAMIC(默认)、COMPRESSED。

4、主键

选择主键的特征：

• 最重要的查询引用的列
• 永远不会留空的列
• 从不具有重复值的列
• 插入后很少更改值的列

5、查看 InnoDB 表属性

要查看InnoDB表的属性，执行 SHOW TABLE STATUS 语句：

mysql> SHOW TABLE STATUS FROM test LIKE 't%' G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-18 12:18:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment:

还可以InnoDB通过查询InnoDB信息架构系统表来访问表属性：

ysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' G
*************************** 1. row ***************************
     TABLE_ID: 45
         NAME: test/t1
         FLAG: 1
       N_COLS: 5
        SPACE: 35
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

索引

每个InnoDB表都有一个称为聚集索引的特殊索引，用于存储行数据。

• 在PRIMARY KEY表上定义时，InnoDB将其用作聚集索引。
• 如果没有PRIMARY KEY为表定义，则InnoDB使用第一个UNIQUE索引，并将所有键列定义为NOT NULL聚集索引。
• 如果表没有索引PRIMARY KEY或没有合适的UNIQUE索引，则InnoDB生成以GEN_CLUST_INDEX包含行ID值的合成列命名的隐藏聚集索引。

通过聚集索引访问一行很快，因为索引搜索直接指向包含行数据的页。如果表很大，与使用与索引记录不同的页存储行数据的存储组织相比，聚簇索引体系结构通常可以节省磁盘 I/O 操作。

1、二级索引与聚集索引的关系

聚集索引以外的索引称为二级索引。二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。InnoDB使用此主键值搜索聚集索引中的行。

2、索引的物理结构

除空间索引外，InnoDB索引都是B树数据结构。

空间索引使用R树，它是用于索引多维数据的专用数据结构。索引记录存储在其B树或R树数据结构的叶页中。索引页的默认大小为16KB。

当新记录插入到InnoDB 聚集索引中时，InnoDB尝试保留 1/16 的页空闲空间以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的索引页大约为 15/16。如果以随机顺序插入记录，则页从 1/2 到 15/16 已满。

InnoDB的锁

InnoDB实现标准的行级锁定，其中有两种类型的锁，共享锁和排它锁。

• 共享锁允许持有锁读取行的事务
• 排它锁允许持有锁，更新或删除行的事务

InnoDB支持多粒度锁定，允许行锁和表锁共存。

为了使多粒度级别的锁定实用，InnoDB使用意向锁，意向锁是表级锁，指事务稍后需要对表中的行使用哪种类型的锁（共享锁或独占锁）。

事务模型

InnoDB报价由SQL描述的所有四个事务隔离级别：READ UNCOMMITTED(读未提交)、 READ COMMITTED(读已提交)、 REPEATABLE READ(可重复读取)、SERIALIZABLE(可序列化)

InnoDB默认隔离级别是 REPEATABLE READ

READ UNCOMMITTED

允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

READ COMMITTED

只能读取到已经提交的数据

REPEATABLE READ

可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

SERIALIZABLE

一个个事务排成序列的形式。事务一个挨一个执行，等待前一个事务执行完，后面的事务才可以顺序执行。

死锁

死锁是不同事务无法继续进行的情况，因为每个事务都持有另一个需要的锁。因为两个事务都在等待资源变得可用，所以它们都不会释放它持有的锁。

当事务锁定多个表中的行（UPDATE 或 SELECT ... FOR UPDATE）但顺序相反时，可能会发生死锁。当这些语句锁定索引记录和间隙的范围时，也可能发生死锁，每个事务由于时间问题而获取一些锁而不是其他锁。

死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁的发生是因为写操作。

当启用死锁检测（默认）并且死锁确实发生时，InnoDB检测条件并回滚其中一个事务（受害者）。

Myisam

MyISAM表使用B树索引，MyISAM表都存储在磁盘上的三个文件中，文件的名称以表名开头，并具有指示文件类型的扩展名。

• 文件存储表格式 .frm
• 数据文件具有 .MYD( MYData)
• 索引文件具有.MYI ( MYIndex)

创建MyISAM表：

CREATE TABLE t (i INT) ENGINE = MYISAM;

MyISAM表具有的特征：

MyISAM支持的功能：

• 支持真实VARCHAR类型；一个VARCHAR列以存储在一个或两个字节中的长度开始。
• 带有VARCHAR列的表可能具有固定或动态的行长。
• 表中VARCHAR和CHAR列的长度总和可能高达64KB。
• 任意长度限制UNIQUE。

表的存储格式

1、静态表

静态格式是MyISAM 表的默认格式。当表不包含可变长度列它用于（VARCHAR，VARBINARY，BLOB或TEXT）。每行都使用固定数量的字节存储。

在三种MyISAM存储格式中，静态格式是最简单和最安全的（最不容易损坏）。

• CHAR和 VARCHAR列被空格填充到指定的列宽，尽管列类型没有改变。BINARY和 VARBINARY列用0x00字节填充到列宽
• NULL列在行中需要额外的空间来记录它们的值是否为 NULL。每NULL列多占一位，四舍五入到最接近的字节
• 很快
• 易于缓存
• 崩溃后易于重建，因为行位于固定位置
• 通常需要比动态格式表更多的磁盘空间

2、动态表

当表包含任何可变长度列（VARCHAR，VARBINARY，BLOB或TEXT），或者表是使用ROW_FORMAT = DYNAMIC表选项创建的，则使用动态存储格式。

动态格式比静态格式复杂一点，因为每一行都有一个标题，表示它有多长。当由于更新而变长时，行可能变得碎片化（以不连续的片段存储）。

• 除了长度小于4的字符串列之外，所有字符串列都是动态的。
• 每行前面都有一个位图，指示哪些列包含空字符串（对于字符串列）或零（对于数字列）
• NULL列在行中需要额外的空间来记录它们的值是否为NULL。每NULL列多占一位，四舍五入到最接近的字节。
• 通常需要比固定长度表少得多的磁盘空间。
• 崩溃后比静态格式表更难重建，因为行可能被分成许多部分并且链接（片段）可能会丢失。

3、压缩表

压缩存储格式是使用myisampack工具生成的只读格式，压缩表可以用myisamchk解压缩。

• 压缩表占用很少的磁盘空间
• 每行都单独压缩，因此访问开销非常小
• 可用于固定长度或动态长度的行

MyISAM表问题

即使MyISAM表格式非常可靠（SQL 语句对表所做的所有更改都在语句返回之前写入），但如果发生以下任何事件，仍然可能会损坏表：

• mysqld的进程在写中间被杀害
• 发生意外的计算机关机
• 硬件故障
• 正在使用外部程序（例如 myisamchk）来修改同时由服务器修改的表
• MySQL 或MyISAM 代码中的软件错误

损坏表的典型症状是

• 从表中选择数据时出现以下错误 Incorrect key file for table: '...'. Try to repair it
• 查询不会在表中找到行或返回不完整的结果

区别

场景选择

两种存储引擎的区别:

• InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
• MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
• InnoDB支持外键，MyISAM不支持
• MyISAM是默认引擎，InnoDB需要指定
• InnoDB不支持FULLTEXT类型的索引
• InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
• 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
• 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
• InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’

欢迎关注