MySQL存储引擎 lnnoDB的逻辑架构详解 InnoDB存储引擎执行流程

连接层

1. 验证用户的身份，用户名密码是否匹配
2. 提供两种连接方式（TCP/IP连接、socket连接）
3. 连接层提供了一个与sql层交互的线程

SQL层

1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL，DML，DQL，DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
7.6.1 与存储引擎层建立交互的线程
6.2 将要执行的sql发给存储引擎层
8.如果有缓存，则走缓存
9.记录日志（如binlog）
 

存储引擎层

1. 接收SQL层传来的语句

2. 与磁盘交互，获取数据，返回给sql层

3. 建立与sql层交互的线程

MySQL数据库管理软件服务端 =====> 存储引擎innodb

操作系统 =====> 文件系统

计算机硬件 =====> 硬盘

查看存储引擎信息

# 查看所有支持的存储引擎
show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | 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         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

innodb存储引擎支持的核心特性

事务
行级锁：innodb支持行级锁，myisam是表级锁，锁的粒度越小并发能力越强
外键
MVCC 多版本并发控制
备份和恢复 innodb支持支持热备，myisam不支持
自动故障恢复 （CSR） Crash Safe Recovery
6.1 对硬盘上的数据进行修改， 当修改过程中，突然断电或者由于其它外界因素导致数据丢失。
6.2 内存里面的数据先记录在日志（如binlog），再写入硬盘。innodb搜索引擎自带的故障恢复功能，redo日志。

• MySQL 5.5.8 版本开始是默认的存储引擎：innodb
• MySQL 5.5.8 版本之前是默认的存储引擎：myisam

myisam搜索引擎的弊端

1. 表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。
2. 不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题

# 查看正在使用的存储引擎
show variables like 'storage_engine%';
或者
ELECT @@default_storage_engine;

# 查看innodb的表有哪些，通过查表information_schema.tables来获取
# table_schema字段的值即表所在的库
select table_schema,table_name,engine from information_schema.tables where engine='innodb';

# 查看myisam的表有哪些，通过查表information_schema.tables来获取
select table_schema,table_name,engine from information_schema.tables where engine='myisam';

# 查看表的存储引擎
SHOW CREATE TABLE db1.t1\G
或者
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1' AND TABLE_SCHEMA = 'db1'\G

修改存储引擎

配置文件修改存储引擎

vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb
# 每个表都有自己独立的.ibd(数据文件)文件。innodb搜索引擎5.5之前的版本中,不加该选项,默认为数据文件都是存在mysql/ibdata1(共享表空间)文件中
innodb_file_per_table=1    
 

临时修改存储引擎

#在MySQL命令行中临时设置

SET @@storage_engine=myisam;

#查看

SELECT @@default_storage_engine;

建表时修改存储引擎

CREATE TABLE egon(id INT) ENGINE=myisam;

将Myisam存储引擎替换为Innodb存储引擎，并实现数据迁移

实验环境

# 将当前使用的数据库搜索引擎改成mysiam存储引擎
mysql> SET @@storage_engine=myisam;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 查看当前数据库默认使用的存储引擎
mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+

# 使用Myisam存储引擎创建数据
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

mysql> use db01;
Database changed
mysql> create table t1(
    -> id int primary key auto_increment,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(name) values("nana"),("dada");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

# 查看t1表的存储引擎
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
替换存储引擎，实现数据迁移

1. 导出Mysiam存储引擎的存储的库文件
2. 修改导出库的配置文件，将配置文件的存储引擎myisam该成innodb。
3. 将导出的库配置文件传给新的数据库服务器
4. 在新的数据库服务器中导入库配置文件

# 激活服务器上的Binlogging
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin

# 在旧机器上备份系统库以外的生产库数据
# --triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
# -R:–routines,导出存储过程以及自定义函数
mysqldump -uroot -p123 -B db01 --triggers -R --master-data=2 >/tmp/db01.sql

# 对备份数据进行处理（将engine字段替换）
sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#gi' /tmp/db01.sql

# 将备份的数据传到新的数据库服务器上
scp -r /tmp/db01.sql root@192.168.15.7:/tmp

# 将修改后的备份恢复到新库
mysql -uroot -p123 < /tmp/db01.sql
 

lnnoDB的逻辑架构

如下图所示，InnoDB的逻辑架构主要分为三个大的组成部分：

1. 在内存中的架构（In-Memory Structures）：用户空间
2. 操作系统缓存（Operating System Cache) ：内核空间
3. 在硬盘上的架构（On-Disk Structures）：硬盘

 

innodb在内存上的架构

InnoDB的内存架构分为4个部分:

1. 缓冲池（Buffer Pool）

2. 缓冲池是一块用于缓存被访问过的表和索引数据的内存区域，缓冲池允许在内存中处理一些被用户频繁访问的数据。 在某一些专用的服务器上，甚至有可能使用80%的物理内存作为缓冲池。

3. 缓冲池主要是为了通过降低磁盘IO的次数来提升数据的读性能。

写缓冲（Change Buffer）

写缓冲是为了缓存缓冲池（Buffer Pool）中不存在的二级索引(Secondary Index)页的变更操作的一种特殊的内存数据结构。
这些变更通常是一些Insert、Update、Delete等DML操作引发的。
如果有一些其它的读操作将这些被变更的二级索引页加进了缓冲池（Buffer Pool)，则这些变更的数据会被马上合并至缓冲池中以保证用户读取数据的一致性。

日志缓冲（Log Buffer)

InnoDB将数据的每次写优化为了批量写，这便以降低磁盘IO的次数，为了防止一些数据尚未写入硬盘就断电了，需要记录日志。 而日志缓冲就是用来缓存一些即将要被写入磁盘日志文件（log files)中的数据。

自适应hash索引（Adaptive Hash Index)

在Innodb中，用户是不可以直接去创建哈希索引的，这个自适应哈希索引是InnoDB为了加速查询性能。
会根据实际需要来决定是否对于一些频繁需要被访问的索引页构建哈希索引，它会利用key的前缀来构建哈希索引。
只适用于等值查询，例如：select * from db01.t1 where id = 1；
 

innodb在操作系统上的架构

write 和 fsync的操作是操作系统中的调用函数

 

• write操作

  将数据写到操作系统缓存后立即返回。
  后面依靠操作系统的调度机制将缓存数据刷到磁盘中去，其顺序是user buffer——>page cache——>disk。
  

fsync操作

将数据提交到硬盘中，强制硬盘同步。
将一直阻塞到写入硬盘完成后返回，大量进行fsync操作就有性能瓶颈。

o_direct选项：是在Linux系统中的选项，使用该选项后，对文件进行直接IO操作，不经过文件系统缓存，直接写入磁盘。

innodb在硬盘上的架构

InnoDB在硬盘上总共分为六个部分，也就是：

1. 表（Tables）；

1.如果已经指定了数据的默认存储引擎，那么创建表的时候，无需指定再指定存储引擎。

2.默认情况下，创建InnoDB表的时候innodb_file_per_table参数是开启的，它表明用户创建的表和索引，会被以单表单文件的形式放入到file-per-table表空间中。
mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

3.如果禁用了该参数innodb_file_per_table，那么表及索引会被放入系统表空间(system tablespaces)中。

4.如果创建表的时候，想要把表创建在通用表空间（general tablespaces)中，那么需要用户使用create table … tablespace语法来创建表结构。

表空间（Tablespaces），指的就是.ibd文件；

在InnoDB中，表空间总共分为：

1.系统表空间（System Tablespaces : ibdata1）
系统表空间主要用于存储双写缓冲、写缓存以及用户创建的表和索引（当innodb_file_per_table被禁用的情况下）

2.file-per-table表空间（file-per-tableTablespaces : 每个table对应的.ibd文件）
存储用户创建的表和索引数据，默认情况下（innodb_file_per_table参数是启用的）

3.通用表空间（General Tablespaces）
General Tablespaces是一种共享的 innodb 表空间，类似系统表空间，可以存储多个schema(架构)下的多张表。与系统表空间相比，这种方式可以将存储表空间的元数据到内存中，可以减小内存消耗。
General Tablespaces可以将数据文件存放在其它盘上。
这样做的好处是，比如说我需要将几张热点表放到更快的存储盘上，就可以在更快的存储盘上创建 general tablespace ，将热点表放过去。
数据表可以在 general tablespace 和 per_file_table tablespace 之间来回移动。

4.回滚表空间（Undo Tablespaces） 
回滚表空间是为了存储回滚日志，通常回滚日志在表空间会以回滚段(Undo Segments)的形式存在。

5.临时表空间（Temporary Tablespaces） 
临时表空间用于存储用户创建的临时表，或者优化器内部自己创建的临时表。
 

索引（Indexes）；

按键的类别划分：主键索引和二级索引/辅助索引；
按索引的类型分：BTree索引和自适应哈希索引；
按存储结构划分：聚集索引和非聚集索引。

索引存在的目的主要是为了加速数据的读取速度，InnoDB采用BTree（实际为优化改进后的B+树索引）。

主键索引也是聚集索引，二级索引都是非聚集索引。

自适应哈希索引是InnoDB为了加速查询性能，它自己按需在内存中对加载进内存的BTree索引优化为哈希索引的一种手段。
 

双写缓冲(Doublewrite Buffer)；

双写缓冲是一个在系统表空间System Tablespaces中存储区，在这个存储区中，在InnoDB将页面写入InnoDB数据文件中的适当位置之前，会先从缓冲池中刷新页面 。
如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从双写缓冲中找到页面的原来的数据。
 

Redo日志：记录的是尚未完成的操作，断电则用其重做

redo即redo日志，是用于记录数据库中数据变化的日志，只要你修改了数据块那么就会记录redo信息，当然nologging除外了。

你的每次操作都会先记录到redo日志中，当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须redo，重新把数据更新到数据文件。
 

Undo日志：记录的改动之前的旧数据，一旦改错，可以回滚

undo即undo段，是指数据库为了保持读一致性，存储历史数据在一个位置。 用于记录更改前的一份copy，用于回滚、撤销还原

innodb存储引擎执行流程

执行一条更新sql语句，存储引擎执行流程可以分为三大阶段，8个小步骤

三大阶段:

  1、执行阶段(1、2、3、4)
       数据加载到内存，写undo log，更新内存中数据，写redo log buffer

  2、事务提交阶段(5、6、7)
        redo log(修改的数据)和binlog(修改的操作)刷盘，commit标记写入redo log中
        
  3、最后(8)
        后台io线程随机把内存中脏数据(更新之后的数据)刷到磁盘上

8个小步骤:

1.把该行数据从磁盘加载到buffer pool中，并对该行数据进行加锁
2.写undo log
3.在buffer pool中的数据更新，得到脏数据(更新之后的数据)
4.把所作的修改写入到redo log buffer当中
5.准备提交事务redo log刷入磁盘
6.准备提交事务binlog写入磁盘
7.把binlog的文件名和位置写入commit标记，commit标记写入redolog中，事务才算提交成功；否则不会成功
8.IO线程Buffer Pool中的脏数据刷入磁盘文件，完成最终修改
 

缓冲池 buffer pool(更新后的数据)

1. 会把一些磁盘上的数据加载到该内存当中 
2. 查询数据的时候不从磁盘查，从该内存里查

undo log(回滚日志)

1. 逻辑日志，可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录 
2. 用于数据回滚
3. 实现mvcc

redo log(修改的数据)

1. 存储引擎层日志 
2. 物理日志（类似于"对哪个数据页中的什么记录，做了个什么修改"） 
3. 记录对数据做了什么修改，防止已提交事务的数据丢失。
   因为数据不是实时刷盘的，数据是在buffer pool当中，如果数据库宕机了并且buffer pool中的数据还没有刷盘，修改过的数据就丢失了，redo log解决这一问题。 
4. redo log buffer是redo log的缓冲区，数据做了什么修改，首先会写入到redo log buffer中，再刷盘写入redo log中。
 

binlog(修改的操作)

归档日志，属于mysql server层，不属于存储引擎层 
逻辑性日志（类似于"对users表中的id=10的一行数据做了更新操作，更新以后的值是什么"）

常见面试题

问题1：事务还没有提交，mysql宕机了怎么办？

• buffer pool和redo log buffer中的数据都会丢失，数据库返回异常，提示事务失败。磁盘数据并不会发生任何改变，不影响。

• 问题2：事务提交了，mysql突然宕机了怎么办？

  分两种情况
  第一种情况: 事务提交失败了，那么对磁盘原来的数据没有任何影响。
  第二种情况: 事务提交成功了，buffer pool和redo log buffer中的数据都会丢失，但事务提交成功意味着更新后的数据已经写入了redo日志文件，重启数据库之后，数据库会根据redo日志文件自动恢复数据。

commit标记，undo log，redo log主要是innodb存储引擎保护数据库数据安全的机制！！！

commit标记的意义

• commit命令用于把事务所做的修改保存到数据库，它把上一个commit或rollback(回滚)命令之后的全部事务都保存到数据库。

commit写入redo log，才能判定事务成功；因为此时，redo log中有这次更新记录，binlog也有这次更新记录。
redo log和binlog保持了一致，否则:

1. redo log刷盘成功，binlog还没刷盘   
   数据库宕机，没有commit标记写到redo log中，事务判定为失败。   
   因为redolog中有这次更新日志，binlog中没有这次更新日志，会出现数据不一致问题。

2. redo log刷盘成功，binlog刷盘成功   
   commit标记还没来得及写入redo log中，数据库宕机，同样判定事务提交失败。
 

我们需要知道的一个概念: commit标记成功之前，redo log和binlog都是在内存空间里面的。commit标记成功之后，才开始刷盘。
只有同时满足

redo log刷盘成功，
binlog刷盘成功，
commit标记写入redo log日志文件。

这样的条件下，才能判定事务提交成功。
innodb存储引擎每提交一条sql语句，默认是开启一个事务的。

redo log刷盘策略

当提交事务的时候，redo log buffer里的数据会根据一定规则刷到磁盘上

通过innodb_flush_log_at_trx_commit参数来配置

0  提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。
此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。

1 （默认值，建议）提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去。
只要事务提交成功，那么 redo log 就必然在磁盘里了。

2  提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 操作系统缓存里去，而不是直接进入磁盘文件。
可能 1 秒后才会把 os cache 操作系统缓冲里的数据写入到磁盘文件里去。此时mysql宕机，数据不会丢失；如果机器宕机，数据会丢失。
 

binlog刷盘策略

当提交事务的时候，binlog也会刷到磁盘上去
通过sync_binlog参数来配置

0  默认值。事务提交后，将二进制日志写入了操作系统缓存，若操作系统宕机则会丢失部分二进制日志。

1 （推荐）事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
 

内存(buffer pool)中更新过脏数据什么时候刷盘

redo log满的情况下才会主动刷入磁盘
系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
MySQL 认为空闲的时间，这种情况没有性能问题；
MySQL 正常关闭之前，会把所有的脏页刷入到磁盘，这种情况也没有性能问题
 

LRU算法淘汰策略

常用的的索引页和数据页都会缓存在内存缓冲池（Buffer Pool）中，在查询数据时，只要在内存缓冲池中存在该数据，InnoDB就不用每次都去磁盘中读取页，从而提高数据库的查询性能。
缓冲池需要通过 LRU 算法将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。