mysql存储引擎

存储引擎的基本介绍

在关系型数据库里面，数据是放在什么结构里面的？（放在表Table里面的）
我们可以把这个表理解成Excel电子表格的形式。所以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定的，所以我们也可以把存储引擎叫做表类型。

查看存储引擎

show table status from `xxxtable`

或者通过DDL建表语句来查看

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

我们说一张表使用的存储引擎决定我们存储数据的结构，那在服务器上它们是怎么存储的呢？我们先要找到数据库存放数据的路径：

show variables like 'datadir';

默认情况下，每个数据库有一个自己的文件夹

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

存储引擎比较

常见的存储引擎
MyISAM和InnoDB是我们用得最多的两个存储引擎，在Mysql5.5版本之前，默认的存储引擎是MyISAM,它是MySQL自带的。我们创建表的时候不指定存储引擎，他就会使用MyISAM作为存储引擎。

MYISAM的前身是ISAM（Indexed Sequential Access Method：利用索引，顺序存取数据的方法）。

5.5版本之后默认的存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。为什么要改呢？最主要的原因还是InnoDB支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合。

这里有个小故事可以听下：
这个里面又有 Oracle 和 MySQL 公司的一段恩怨情仇。 InnoDB 本来是 InnobaseOy 公司开发的，它和 MySQL AB 公司合作开源了 InnoDB 的代码。但是没想到 MySQL 的竞争对手 Oracle 把 InnobaseOy 收购了。 后来 08 年 Sun 公司（开发 Java 语言的 Sun）收购了 MySQL AB，09 年 Sun 公司 又被 Oracle 收购了，所以 MySQL，InnoDB 又是一家了。有人觉得 MySQL 越来越像 Oracle，其实也是这个原因。

那么除了这两个我们最熟悉的存储引擎，数据库还支持其他

数据库支持的存储引擎

show engines ;  查看数据库支持的索引

其中有存储引擎的描述和对事物，XA协议和Savepoints的支持。
XA协议用来实现分布式事物（分为本地资源管理器，事物管理器）。
Savepoints用来实现子事物（嵌套事物）。创建一个Savepoints之后，事物就可以回滚到这个点。不会影响创建Savepoints之前的操作。

MyISAM

These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

应用范围比较小。表级锁定限制了读/写的性能，因此在web和数据仓库配置中，它通常用于只读或以读为主的工作。

特点：
支持表级别的锁（插入和更新会锁表）。不支持事务。
拥有较高的插入（insert）和查询（select）速度。
存储了表的行数（count速度较快）

（怎么快速向数据库插入100万条数据？我们有一种先用MyISAM插入数据，然后修改存储引擎为InnoDB的操作）

适合：只读之类的数据分析的项目。

InnoDB

The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints

mysql5.7中默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性。InnoDB还支持外键引用完整性约束。
特点：
支持事务、支持外键，因此数据完整性，一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞（MVCC）
特殊的索引存放方式，可以减少IO，提升查询效率。
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

Memory

Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB及其缓存池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中，而ndbcluster为大型分布式数据提供了快速的键值查找。

特点：
把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失，只适合做临时表。
将表中数据存储到内存中。

CSV

Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

它的表实际上是带有逗号分隔值的文本文件。csv表允许以csv格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在innodb表中，并且只在导入和到处阶段使用csv表。
特点：不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入和导出。

如何选择存储引擎

如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
如果需要一个用于查询的临时表，可以选择Memory。

执行引擎 （Query Execution Engine）

存储引擎分析完了，它是我们存储数据的形式，继续第二个问题，是谁使用执行计划去操作存储引擎呢？
这就是我们的执行引擎，它利用存储引擎提供的相应API来完成操作。
为什么我们修改了表的存储引擎，操作方式不需要任何改变？因为不同功能的存储引擎实现的API是相同的。

最后把数据返回给客户端，即使没有结果也要返回。

MySQL体系结构总结

基于上面的分析的流程，我们一起来梳理下Mysql的模块。

1，Connector:用来支持各种语言和SQL的交互，比如PHP、Python、Java的JDBC
2，Management Services & Utilities ： 系统管理和控制工具，包括备份恢复、MySQL复制、集群等
3，Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等。
4，SQL Interface：用来接收用户的SQL命令，返回用户需要的查询结果
5，Parser：用来解析SQL语句
6，Optimizer：查询优化器
7，Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，key缓存，权限缓存等等。
8，Pluggable Storage Engines：插件式存储引擎，它提供API给服务层使用，和具体的文件打交道。

架构分层

总体上，我们可以把MySQL分成三层，跟客户端对接的连接层，真正执行操作的服务层，和跟硬件打交道的存储引擎层（参考Mybatis：接口、核心、基础）。

连接层

我们客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能就在连接层完成。

服务层

连接层会把SQL语句交给服务层，这里面又包含一系列的流程。
比如查询缓存的判断、根据SQL调用相应的接口，对我们的SQL语句进行词法和语法的解析（比如关键字怎么识别，别名怎么识别，语法有没有错误等等）
优化器，Mysql会根据一定的规则对我们的SQL语句进行优化，最后再交给执行器区执行。

存储引擎

存储引擎就是我们的数据真正存放的地方，在Mysql里面支持不同的存储引擎。

一条更新SQL是如何执行的

在数据库里面，我们说的update操作其实包含了更新、插入和删除。如果大家有看过MyBatis的源码，应该知道Executor里面也只有doQuery（）和doUpdate（）方法，没有doDelete（）和doInsert（）。

更新流程和查询流程有什么不同呢？
基本流程也是一致的，也就是说，它要经过解析器、优化器的处理，最后交给执行器。

区别就在于拿到符号条件的数据之后的操作。

缓冲池

首先，InnoDB的数据都是放在磁盘上的，InnoDB操作数据有一个最小的逻辑单元，叫做页（索引页和数据页）。我们对于数据的操作，不是每次都直接操作磁盘，因为磁盘的速度太慢了。InnoDB使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域叫Buffer Pool。
下一次读取相同的页，先判断是不是在缓冲池里面，如果是，就直接读取，不用再次访问磁盘。

修改数据的时候，先修改缓冲池里面的页。内存的数据和磁盘数据不一致的时候，我们把它叫做脏页。InnoDB里面有专门的后台线程把Buffer Pool的数据写到磁盘。

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

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

InnoDB内存结构和磁盘结构

内存结构

Buffer Pool 主要分为3个部分：Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer。
Buffer Pool
Buffer Pool缓存的是页面信息，包括数据页、索引页。

查看服务器状态，里面有很多跟Buffer Pool相关的信息。

SHOW STATUS LIKE '%innodb_buffer_pool%';

这些状态都可以在官网查到详细的含义，用搜索功能

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

查看参数（系统变量）：

SHOW VARIABLES like '%innodb_buffer_pool%';

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

内存缓冲区对于提升读写性能有很大的作用。思考一个问题：
当需要更新一个数据页时，如果数据页在Buffer Pool中存在，那么就直接更新好了。否者的话就需要从磁盘加载到内存，再对内存的数据页进行操作。也就是说，如果没有命中缓冲池，至少要产生一次磁盘 IO，有没有优化的方式呢。

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

这一块区域就是Change Buffer。

最后把Change Buffer记录到数据页的操作叫做Merge。
什么时候发生merge
在访问这个数据页的时候，或者通过后台线程、或者数据库shut down、redo log写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会再写数据库后立刻读取，就可以使用Change Buffer（写缓冲）

SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

（redo） Log Buffer
如果Buffer Pool里面的脏页还没有刷入磁盘时，数据库拓机或者重启，这些数据丢失。如果写操作写到一半，甚至可能会被破坏数据文件导致数据库不可用。
为了避免这个问题，InnoDB把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现crash-safe）-- 用它来实现事务的持久性

这个文件就是磁盘的redo log（叫做重做日志），对应于/var/lib/mysql/目录下的ib_logfile()和ib_logfile1，每个48M.。
这种日志和磁盘配合的整个过程，其实就是Mysql里的WAL技术，它的关键点就是先写日志，再写磁盘。

show variables like 'innodb_log%';

问题：
同样是写磁盘，为什么不直接写到db file里面去？ 为什么先写日志再写磁盘？
我们先来了解一下随机I/O和顺序I/O的概念。
磁盘的最小组成单元是扇区，通常是512个字节。
操作系统和内存打交道，最小单位是页Page。
操作系统和磁盘打交道，读写磁盘，最小单位是块Block。

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

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

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

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

磁盘结构

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

系统表空间system tablespace

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

InnoDB系统表空间包含InnoDB数据字典和双写缓冲区，chang buffer和undo logs，如果没有指定file-per-table，也包含用户创建的表和索引数据。
1，undo在后面介绍，因为有独立的表空间。
2，数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）
3，双写缓冲（InnoDB的一大特性）
InnoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16Kb，操作系统页大小为4K，InnoDB的页写入到磁盘时，一个页需要分4次写。

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

我们不是有redo log吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩溃恢复是没有意义的。所以在对于应用redo log之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再引用redo log。这个页的副本就是double write，InnoDB双写技术。通过它实现了数据页的可靠性。
跟redo log一样，double write 由两部分组成，一部分是内存的double write，一个部分是磁盘上的double write。 因为double write是顺序写入的，不会带来很大的开销。
在默认情况下，所有的表共享一个系统表空间，这个文件会越来越大，而且它的空间不会收缩。

独占表空间 file-per-table tablespace

我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置，默认开启。

SHOW VARIABLES LIKE 'innodb_file_per_table';

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

通用表空间 general tablespace

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

后台线程

后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread，IO thread，purge thread，page cleaner thread。

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

binlog

binlog以事件的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
跟redo log不一样，它的文件内容是可以追加的，没有固定大小限制。
在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。

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