MySQL 存储引擎（2）[通俗易懂]

首先我们带着下边三个问题来认识存储引擎

• 存储引擎在MySQL中的作用是什么？
• MySQL都有哪些存储引擎
• SQL又与存储引擎有什么关系？

存储引擎在MySQL中的作用是什么？

顾名思义，存储引擎就是用于存储我们的数据的。在关系型数据库中我们一般将数据库存放在表中（Table）。

我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。

在MySQL中，支持多种存储引擎，他们是可以替换的，所以叫插件式的存储引擎。为什么要弄这么多存储引擎呢？一种还不够用吗？

MySQL都有哪些存储引擎？

可以通过下边命令，查询已存在表的存储引擎

show table status from `data_test`;

代码100分

在MySQL里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。

下面我们简单创建三种类型的存储引擎做测试

代码100分CREATE TABLE `user_innoDB`  (
  `id` int(5) NOT NULL,
  `name` varchar(255) NULL,
  `age` int(4) NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;


CREATE TABLE `user_MEMORY`  (
  `id` int(5) NOT NULL,
  `name` varchar(255) NULL,
  `age` int(4) NULL,
  PRIMARY KEY (`id`)
) ENGINE = MEMORY;


CREATE TABLE `user_MyISAM`  (
  `id` int(5) NOT NULL,
  `name` varchar(255) NULL,
  `age` int(4) NULL,
  PRIMARY KEY (`id`)
) ENGINE = MyISAM;

存储引擎在服务器上它们是怎么存储的呢？我们先要找到数据库存放数据的路径：

show variables like "datadir";

进入我们创建的DataBase下就可以看到，上边我们刚创建的3种引擎类型的表。

任何一个存储引擎都有一个frm文件，这个是表结构定义文件。

不同的存储引擎存放数据的方式不一样，产生的文件也不一样，innodb是1个，memory没有，myisam是两个。

各引擎特点说明

TIPS：怎么快速向数据库插入100万条数据？先用MyISAM插入数据，然后修改存储引擎为InnoDB。

在实际应用中可以根据不同的业务场景来选择不同的存储引擎，如果以上存储引擎都不能满足你的需求，并且你的技术能力足够，可以根据官网内部手册用C语言自己开发一个存储引擎：

https://dev.mysql.com/doc/internals/en/custom-engine.html

SQL又与存储引擎有什么关系？

SQL无法独立执行，他是通过执行引擎执行的，执行引擎是通过公共的API调用存储引擎并返回结果。

insert & update & delete 是如何执行的？

传冲池（Buffer Pool）

InnoDB的数据都是放在磁盘上的，InnoDB操作数据有一个最小的逻辑单位，叫做页（索引页和数据页）。我们对数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢。InnoDB使用了一种缓冲池的技术，就是把磁盘读到的页放到一块内存区域里。这个内存区域就叫Buffer Pool。

下次读取相同的页，先判断是不是在缓冲池中，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池中的页。内存数据页和磁盘数据不一致的时候我们把他叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性把多个修改写入磁盘，这个动作就叫做刷脏。

Buffer Pool是InnoDB里面非常重要的一个结构，它的内部又分成几块区域。

InnoDB内存结构和磁盘结构

BufferPool主要分为3个部分：Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。

Buffer Pool

BufferPool缓存的是页面信息，包括数据页、索引页。

代码100分# 查看服务器状态
# 详细说明https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
show status like "%innodb_buffer_pool%";

BufferPool默认大小是128M（134217728字节），可以调整。

# 查看系统变量
# 详细说明https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
show variables like "%innodb_buffer_pool%";

内存的缓冲池写满了怎么办？（Redis设置的内存满了怎么办？）InnoDB用LRU算法来管理缓冲池（链表实现，不是传统的LRU，分成了young和old），经过淘汰的数据就是热点数据。

Change Buffer 写缓冲

如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句（Insert、Delete、Update）的执行速度。

最后把Change Buffer记录到数据页的操作叫做merge。什么时候发生merge？

有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、redo log写满时触发。

如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用Change Buffer（写缓冲）。

# 如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，可以调整Change Buffer
# 写多读少的业务，可以调大这个值
# Change Buffer占Buffer Pool的比例，默认25%
show variables like "innodb_change_buffer_max_size";

Adaptive Hash Index

索引应该是放在磁盘的，为什么要专门把一种哈希的索引放到内存？

下章详细讲解索引时说明。

（redo）Log Buffer

思考一个问题：如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会破坏数据文件导致数据库不可用。

为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现crash-safe）——用它来实现事务的持久性。

这个文件就是磁盘的redolog（叫做重做日志），对应于/var/lib/mysql/目录下的ib_logfile0和ib_logfile1，每个48M。

这种日志和磁盘配合的整个过程，其实就是MySQL里的WAL技术（Write-Ahead Logging），它的关键点就是先写日志，再写磁盘。

# 查看Log日志相关配置
show variables like "innodb_log%";

同样是写磁盘，为什么不直接写到dbfile里面去？为什么先写日志再写磁盘？

我们先来了解一下随机I/O和顺序I/O的概念。

磁盘的最小组成单元是扇区，通常是512个字节。

操作系统和内存打交道，最小的单位是页Page。

操作系统和磁盘打交道，读写磁盘，最小的单位是块Block。

如果我们所需要的数据是随机分散在不同页的不同扇区中，那么找到相应的数据需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一次进行此过程直到找完所有数据，这个就是随机IO，读取数据速度较慢。

假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO。

刷盘是随机I/O，而记录日志是顺序I/O，顺序I/O效率更高。因此先把修改写入日志，可以延迟刷盘时机，进而提升系统吞吐。

当然redo log也不是每一次都直接写入磁盘，在Buffer Pool里面有一块内存区域（Log Buffer）专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省磁盘IO。

注意：redo log的内容主要是用于崩溃恢复。磁盘的数据文件，数据来自buffer pool。redo log写入磁盘，不是写入数据文件。

Log Buffer什么时候写入log file？

在我们写入数据到磁盘的时候，操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘。

# logbuffer写入磁盘的时机，由一个参数控制，默认是1
show variables like "innodb_flush_log_at_trx_commit";

redo log 特点

1. redolog是InnoDB存储引擎实现的，并不是所有存储引擎都有。
2. 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。
3. redolog的大小是固定的，前面的内容会被覆盖。

以上是MySQL的4种内存结构，磁盘结构里主要是各种各样的表空间，叫做Table space。

磁盘结构

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB的表空间分为5大类。

系统表空间（system tablespace）

在默认情况下InnoDB存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。

InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，Change Buffer和Undo Logs），如果没有指定file-per-table，也包含用户创建的表和索引数据。

1. undo在后面介绍，因为有独立的表空间
2. 数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）
3. 双写缓冲（InnoDB的一大特性）

InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partialpagewrite），可能会导致数据丢失。

# innoDB双写开关
show variables like "innodb_doublewrite";

我们不是有redolog吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write，InnoDB的双写技术。通过它实现了数据页的可靠性。

跟redolog一样，double write由两部分组成，一部分是内存的double write，一个部分是磁盘上的double write。因为doublewrite是顺序写入的，不会带来很大的开销。

在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。

独占表空间file-per-table tablespaces

# 我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置。
show variables like "innodb_file_per_table";

开启后，则每张表会开辟一个表空间，这个文件就是数据目录下的ibd文件（例如/var/lib/mysql/data_test/user_innodb.ibd），存放表的索引和数据。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。

通用表空间general tablespaces

通用表空间也是一种共享的表空间，跟ibdata1类似。可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件可以自定义。

# 创建一个通用的表空间
create tablespace tabtest123 add datafile "/var/lib/mysql/tabtest123.ibd" file_block_size=16K engine=innodb;

# 创建表时指定表空间
create table user (id integer) tablespace tabtest123;

临时表空间 temporary tablespaces

存储临时表的数据，包括用户创建的临时表，和磁盘的内部临时表。对应数据目录下的ibtmp1文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。

Redo log

磁盘结构里面的redo log，在前面已经介绍过了。

undo log tablespace

undo log（撤销日志或回滚日志）记录了事务发生之前的数据状态（不包括select）。

如果修改数据时出现异常，可以用undo log来实现回滚操作（保持原子性）。

在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，属于逻辑格式的日志。

redo Log和undo Log与事务密切相关，统称为事务日志。

undo Log的数据默认在系统表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。

# 查看撤销日志相关参数
show global variables like "%undo%";

mysql后台线程 |后台线程|说明| |–|–| |master thread|负责刷新缓存数据到磁盘并协调调度其它后台进程| |IO thread|分为insert buffer、log、read、write进程。分别用来处理insert buffer、重做日志、读写请求的IO回调| |purge thread|用来回收undo页| |page cleaner thread|用来刷新脏页|

除了InnoDB架构中的日志文件，MySQL的Server层也有一个日志文件，叫做binlog，它可以被所有的存储引擎使用。

Binlog

binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。

跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。

binlog的另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器的binlog，然后执行一遍。