MySQL教程（8）存储引擎原理与日志机制

一、MySQL存储引擎介绍

数据库存储引擎可以理解为是数据库的文件系统，MySQL主要存储引擎有MyISAM、InnoDB、Memory、TokuDB、CSV等。存储引擎位于表级别，通过不同的存储引擎来定义表的文件类型。不同的存储引擎所创建出来的表，其文件格式是不相同的，也就是说可以为每张表设置不同的存储引擎进行使用。

1、MySQL主要存储引擎

· MyISAM

MySQL 5.5.5之前默认的存储引擎，其优势是访问速度快，适合对事务完整性没有要求或者以查询、新增为主的场景。针对数据统计有额外的常数存储，所以执行count(*)的效率非常高。数据存放在.frm、.MYD、.MYI三种类型的文件中，可以直接拷贝表文件到其他数据库中进行使用。由于是表级锁、不支持行锁、不支持热备、不支持事务等原因已经少使用。

· InnoDB

MySQL中唯一支持事务的存储引擎，也是MySQL 5.5版本以后默认存储引擎，除非有特别的原因否则都应该优先考虑使用InnoDB存储引擎。InnoDb最主要的特性就是支持事务，实现MVCC（多版本并发控制）、聚簇索引、行级锁、热备等特性。

Innodb 存储引擎将数据分为了行、页、区、段多个层次进行管理，行是记录数据的基本单元，每一行存储在数据页中；多个页组成一个区，用于分配大块连续的存储空间，提升数据读取效率；最后多个区组成一个段，用来管理表或索引的数据。

InnoDB相比MyISAM会需要更多的空间来存放索引数据和索引，在仅增加和查询的场景下，性能比MyISAM稍微逊色。InnoDB存储引擎下的表结构存放于.frm文件，数据和索引存放在.ibd文件中。从 MySQL 5.6 开始，默认启用了独立表空间，每张表都有一个独立的.ibd文件，但由于InnoDB还依赖共享表空间文件ibdata1文件来存放undo log、依赖ib_logfile文件存放redo log，所以不能直接拷贝.ibd文件到其他实例中使用。

· MEMROY

数据存放在内存中，速度非常快，但是不进行持久化处理，如果数据库重启或者崩溃，只会保存表结构（因为有.frm文件），数据会丢失，适用于存储临时数据

· CSV

数据以文本形式进行存放，可以直接编辑，但是由于不支持索引，所以不适合OLTP应用（因为所有SQL都是全表扫描）

· TokuDB

压缩比可以达到25倍，对数据的插入和删除速度也很快。适用于有大量插入或者删除操作的场景，如Zabbix

· Archive

适合于存储归档数据，如日志信息。数据使用zlib压缩，同样数据量下，其大小约是InnoDB表的五分之一。该存储引擎只支持插入和查询操作，插入速度快，但是不支持索引，所以查询性能差

2、MySQL存储引擎管理

查看当前实例支持的存储引擎

#查看当前MySQL所支持与默认的存储引擎
mysql > show engines;

修改默认存储引擎

#在线修改全局配置
mysql > set global default_storage_engine=innodb  

#写入到配置文件中的配置
[mysqld]
default_storage_engine=innodb

二、MySQL日志机制

1、Redo Log

MySQL中的Redo Log也称为事务重做日志，是InnoDB引擎独有的物理级别日志，用于记录数据页上所做的修改，该文件保存于数据库的data目录下，以ib_logfile进行命名。在事务提交时会InnoDB会基于Write-Ahead Logging（WAL，日志先行机制）将这些更新记录按照顺序IO写的方式先记录到一块内存空间中，这块空间叫做Redo Log Buffer。由于数据还暂时处于内存中，与磁盘页上的数据还不一致，此刻属于脏页。而MySQL会按照配置的策略对这些内存中的脏页数据刷新到磁盘中，正式完成落盘，确保内存和磁盘中的数据一致。

1.1 Redo Log与Binlog的二阶段提交

MySQL事务通过Redo log和Binlog形成的两阶段提交机制来确保事务不丢失，是保证事务持久性的关键。其过程大致如下：

事务产生 > 写入Redo Log Buffer > 将Buffer刷入Redo Log（prepare阶段） > Redo Log Commit  > 写入Binlog 。

两阶段提交机制要求两个日志都需完成写入（Bin log是事务完成后写入，Redo Log是在事务执行的时候同时写入），这样可以避免因为某个日志没有写入而导致数据崩溃后出现数据不一致的情况。如果数据库在事务写入Redo Log后发生崩溃，那么在重启数据库后，MySQL可以通过Redo log将已经提交的事务重新写入。此刻在主从架构下也不用担心，因为是在写Bin log之前发生的崩溃，此时Binlog还没写入，主从都不会完成事务，确保数据一致性。如果是在写入Binlog后发生崩溃，那么按照双1配置，数据依然是完整的（这也是为什么强烈建议配置双1参数的原因，保证了数据的安全）

1.2 Redo Log 写入磁盘策略

· innodb_flush_log_at_trx_commit=0：不主动将redolog buffer刷新到Redo Log File中，而是依靠Innodb每秒刷新到磁盘，存在丢失一秒数据的风险

· innodb_flush_log_at_trx_commit=1：每次事务提交时都将redolog buffer刷新到Redo Log File中

· innodb_flush_log_at_trx_commit=2：每次事务提交时都将redolog buffer刷新到系统缓存中，然后由系统决定落盘时间，只有系统宕机时候会有丢失数据的风险

1.3 Redo Log 自动刷脏策略

· 当Redo log写满时，系统会停止数据库所有更新操作，开始flush内存数据到磁盘，flush完成后Redo log继续滚动写入。此刻flush操作会让所有更新都阻塞，如果有监控的话，更新数基本为0。

· 当系统内存不太够但又出现一个查询操作需要申请内存时也会触发flush操作。此刻如果需要淘汰的脏页太多也会影响服务性能。通常将innodb_io_capacity这个参数设置成磁盘的IOPS实现优化（磁盘的 IOPS 可以通过 fio 工具测试）

· 当MySQL空闲时自动flush，默认每秒一次

· 停止MySQL服务时会自动flush内存中的数据到磁盘

注：如果发现数据库突然慢了一下，有可能正在刷脏页

1.4 Redo Log 相关配置参数

innodb_flush_log_at_trx_commit=1   #redolog写入策略，双1参数之一
innodb_log_file_size=6G           #redolog文件大小，文件会自动命名为ib_logfile0~ib_logfileN，如果设置太小会导致数据库因为经常flush而间歇性性能下降，调大以后可以提高性能，缺点是重启服务需要更长的时间
innodb_log_files_in_group=2       #设置redo log文件个数
innodb_log_group_home_dir=./      #设置redo log文件路径
# innodb_io_capacity=2000         #设置脏页刷新速度，单位为页。磁盘速度越快就设置越大，建议和磁盘IOPS一样
# innodb_flush_neighbors=0       #刷脏页时是否把数据页旁边的脏页也刷掉，1为一起刷，SSD磁盘建议为0，8.0开始默认为0

2、Undo Log

MySQL中的Undo Log也称为事务回滚日志，是InnoDB存储引擎生成的日志，属于一种逻辑日志，默认写入到ibdata和ibtmp文件中，当数据库重启进行crash-recovery时会通过Redo log将已经提交事务的更改写到数据文件，而还没有提交的就通过Undo log进行roll back。由于Undo Log记录了事务更新前的状态，所以可以通过Undo Log让数据回滚到某个特定的版本，保证了事务的原子性和一致性。

2.1 Undo Log相关参数

innodb_data_home_dir = /data/mysql/  #共享表空间文件存放路径，文件名为ibdata1
innodb_data_file_path 	= ibdata1:1024M:autoextend  #共享表空间文件大小，可以写多个文件进行拆分
innodb_undo_tablespaces			= 4   #undolog独立表空间个数，默认为0，表示不为undolog设置独立表空间，而是记录到ibdata文件中。如果设置为4则代表创建命名为undo001~undo004的undolog。该配置在mysql初始化后就无法进行修改。
innodb_max_undo_log_size		= 1G  #单个回滚日志最大占用空间大小，默认为10M
innodb_undo_logs 	 			= 128 
innodb_undo_log_truncate		= 1   #当undolog超过innodb_max_und

2.2 Undo Log与MVCC

MVCC即多版本并发控制，其实现的基础是Undo Log和Read View。通过数据行的多个版本管理实现了数据库的并发控制，比如查询正在被更新的数据行，有了MVCC就可以看到被更新之前的值，这样就可以让查询事务不需要等待更新事务释放锁了。这也是为什么在RC读已提交的隔离级别下，用户还可以查看到历史数据的原因，因为读取的是数据的快照版本，属于快照读（与之对应的是当前读）

MVCC实现的基础是Undo Log，MVCC整体操作流程大概为：获取事务自身版本号Read View（如果是查询语句，版本号永远都为0）> 获取数据历史版本Readview > 将查询得到的数据和视图的版本号进行对比 > 如果不符合Readview规则就从Undo log获取历史快照。

当删除或者插入了大批量数据后出现进行简单查询也很慢的问题，这个就是因为之前的批量操作产生了大量Undo Log，导致查询需要额外扫描列表。5.7起支持通过innodb_undo_log_truncate手动发起清理

数据库mysql