MySQL 存储引擎（2）原
首先我们带着下边三个问题来认识存储引擎
- 存储引擎在MySQL中的作用是什么？
- MySQL都有哪些存储引擎
- SQL又与存储引擎有什么关系？
存储引擎在MySQL中的作用是什么？
顾名思义，存储引擎就是用于存储我们的数据的。在关系型数据库中我们一般将数据库存放在表中（Table）。
我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。
在MySQL中，支持多种存储引擎，他们是可以替换的，所以叫插件式的存储引擎。为什么要弄这么多存储引擎呢？一种还不够用吗？
MySQL都有哪些存储引擎？
可以通过下边命令，查询已存在表的存储引擎
show table status from `data_test`;在MySQL里面，我们创建的每一张表都可以指定它的存储引擎，而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的。而且，创建表之后还可以修改存储引擎。
下面我们简单创建三种类型的存储引擎做测试
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是两个。
各引擎特点说明
类型 | 说明 | 特点 | 适合场景 |
|---|---|---|---|
InnoDB | 默认存储引擎 | 支持事务、行级锁和外键、支持读写并发，写不阻塞读（MVCC）、特殊的索引存放方式，可以减少IO，提升查询效率 | 经常更新的表，存在并发读写或者有事务处理的业务系统 |
MEMORY | 将表中的数据存储到内存中 | 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表 | 不建议使用 |
MyISAM | 不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎 | 支持表级别的锁（插入和更新会锁表）。不支持事务。拥有较高的插入（insert）和查询（select）速度。存储了表的行数（count速度更快） | 只读之类的数据分析的项目 |
CSV | 带有逗号分隔值的文本文件 | 不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出 | |
ARCHIVE | 这些紧凑的未索引的表用于存储和检索大量很少引用的历史、存档或安全审计信息 | 不支持索引，不支持update 、delete | |
PERFORMANCE_SCHEMA | |||
FEDERATED | |||
BLACKHOLE | |||
MRG_MYISAM |
TIPS：怎么快速向数据库插入100万条数据？先用MyISAM插入数据，然后修改存储引擎为InnoDB。
在实际应用中可以根据不同的业务场景来选择不同的存储引擎，如果以上存储引擎都不能满足你的需求，并且你的技术能力足够，可以根据官网内部手册用C语言自己开发一个存储引擎：
https://dev.mysql.com/doc/internals/en/custom-engine.html
SQL又与存储引擎有什么关系？
SQL无法独立执行，他是通过执行引擎执行的，执行引擎是通过公共的API调用存储引擎并返回结果。
模块 | 说明 |
|---|---|
Connector | 用来支持各种语言和SQL的交互，比如PHP，Python，Java的JDBC |
Management Serveices & Utilities | 系统管理和控制工具，包括备份恢复、MySQL复制、集群等 |
Connection Pool | 连接池，管理需要缓冲的资源，包括用户密码权限线程等 |
SQL Interface | 用来接收用户的SQL命令，返回用户需要的查询结果 |
Parser | 用来解析SQL语句 |
Optimizer | 查询优化器 |
Cache and Buffer | 查询缓存，除了行记录的缓存之外，还有表缓存，Key缓存，权限缓存等 |
Pluggable Storage Engines | 插件式存储引擎，它提供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缓存的是页面信息，包括数据页、索引页。
# 查看服务器状态
# 详细说明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';
值 | 含义 |
|---|---|
0(延迟写) | logbuffer将每秒一次地写入logfile中，并且logfile的flush操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。 |
1(默认，实时写，实时刷) | 每次事务提交时MySQL都会把logbuffer的数据写入logfile，并且刷到磁盘中去。 |
2(实时写，延迟刷) | 每次事务提交时MySQL都会把logbuffer的数据写入logfile。但是flush操作并不会同时进行。该模式下，MySQL会每秒执行一次flush操作。 |
redo log 特点
- redolog是InnoDB存储引擎实现的，并不是所有存储引擎都有。
- 不是记录数据页更新之后的状态，而是记录这个页做了什么改动，属于物理日志。
- redolog的大小是固定的，前面的内容会被覆盖。
以上是MySQL的4种内存结构，磁盘结构里主要是各种各样的表空间，叫做Table space。
磁盘结构
表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB的表空间分为5大类。
系统表空间（system tablespace）
在默认情况下InnoDB存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。
InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，Change Buffer和Undo Logs），如果没有指定file-per-table，也包含用户创建的表和索引数据。
- undo在后面介绍，因为有独立的表空间
- 数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）
- 双写缓冲（InnoDB的一大特性）
InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。
如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partialpagewrite），可能会导致数据丢失。
# innoDB双写开关
show variables like 'innodb_doublewrite';我们不是有redo log吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write，InnoDB的双写技术。通过它实现了数据页的可靠性。
跟redo log一样，double write由两部分组成，一部分是内存的double write，一个部分是磁盘上的double write。因为double write是顺序写入的，不会带来很大的开销。
在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。
独占表空间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，然后执行一遍。