MySQL学习日记 -- MySQL Innodb-MyISAM引擎概述|ZklMao-Space

这里主要回答两个问题：MySQL 中支持的引擎分别时什么？InnoDB 和 MyISAM 存储引擎有什么区别？

MySQL 支持的引擎

控制台中使用配置查询语句即可查看当前版本MySQL 支持的引擎有那些.

SHOW ENGINES;
select version();
SELECT *, VERSION() FROM INFORMATION_SCHEMA.ENGINES WHERE SUPPORT = 'YES';

可以看到8.0.4 中支持的存储引擎有如下几个:

InnDB 数据库启动默认
MyISAM
MEMORY
CSV
ARCHIVE

Tip: 各引擎对比看最后一节，这一主要分享InnoDB 和 MyISAM

在命令行中使用
--default-storage-engine=InnoDB // 可以修改引擎默认配置 
或者在配置文件my.conf 修改 default-storage-engine 配置默认引擎

创建表时也可以在 Create table table_name engine=engine_name (....); 表创建头语句指定表引擎

MySQL InnoDB

这是MySQL 默认引擎，支持特性时最多的，是上面五个引擎中各方面性能均衡的一个引擎，其他四个各有千秋。

InnoDB 索引特性

InnoDB 中，主索引和辅助索引都由B+数构成，二者并非平级关系。

引擎存储文件

MyISAM 把表和数据划分为三个文件存储不同信息:

.frm 表元信息，统计信、存放索引定义结构的地方
.idb (innodb): 存放数据的地方

Inno DB 内存总览

InnoDB architecture diagram showing in-memory and on-disk structures. In-memory structures include the buffer pool, adaptive hash index, change buffer, and log buffer. On-disk structures include tablespaces, redo logs, and doublewrite buffer files.

InnoDB 内存结构

InnoDB 在内存中由四部分构成:

buffer pool 缓冲池
non-clustered index change buffer 辅助索引修改缓冲池 (不能厚此失彼，带辅助索引的非热点数据也得带上)
自适应哈希索引(adaptive hash index) - 建立于buffer pool 之上的热点数据索引中的索引
日志缓冲池

buffer pool

Content is described in the surrounding text.

缓冲池构建在内存中，作用时提高数据读写I/O 吞吐量，引入缓冲池可以更高效地管理磁盘I/O，让刷盘时尽可能少影响数据库读写操作。

buffer pool 有一下特点:

默认大小128M，以页为基本单I/O磁盘单位，页和页之间通过指针链接。非空页中有若干行数据行，行是InnoDB的最小存储单位。
buffer pool 因为大小受配置限定且不会动态扩缩容，所以，buffer pool 仅用于存储热点数据。
buffer pool 使用的内存淘汰算法是改进的LRU--即新热点数据会从缓冲池的中部插入，被淘汰从出buffer pool 的页会被放到write buffer 中，有后台刷盘线程统一落盘。
对于在buffer pool 中查到的数据，会直接返回。
对于热点数据的修改，都会直接修改buffer pool 的对应数据，并写入redolog 中，在包缓存页被LRU淘汰时由刷盘线程写入磁盘，若在此时奔溃，重启后可由redolog 恢复。

change buffer

Content is described in the surrounding text.

change buffer 用于缓存非热点数据的辅助索引(由于辅助索引数据的不完整性)，在对非热点数据辅助索引写入时可以减少磁盘I/O次数。change buffer 有下特点:

所有对非热点辅助索引的修改，都直接修改change buffer 的对应数据，同时写入redolog.当所在页被LRU淘汰时,由后台刷盘线程统一写入磁盘。
默认占用buffer pool 的25%(最大50%),在引擎启动时初始化完成。存储结构是名为ibuf 的B-Tree
change buffer 在系统表空间中由对应的持久化区域
会周期性的合并索引页
周期性的精华磁盘中的二级索引页

Adapvie hash index buffer pool 自适应缓冲池

建立于buffer pool 之上的热点数据索引中的索引:

大小只有buffer pool 1/64
adaptive hash index 建立目的是加速，超高热点数据页和超热点索引页的查找/访问效率，通过自适应索引，可以通过一次查找即可找到对饮数据或索引项，在查找的的是索引页时，可通过查找到索引页的记录的指针，在根据主键沿着聚簇索引找到对应的数据项返回；如果通过主键(聚簇)索引命中AHI则直接返回目标数据页中的记录指针，通过此记录指针可拿到对应数据项。

log buffer

各日志存储位置分散，繁重的日志写入任务会影响I/O 性能，日志具有顺序性，可收集做统一写入以此优化，减少I/O 系统调用，减少日志项磁盘I/O 资源占用时间，log buffe 就是为此设计：

任何写日志操作都将录入到缓冲池中，比如 redolog、undolog、errorlog、binlog等由后台刷盘线程统一批量吸入写入应文件。

InnoDB 特性一览表

MyISAM

索引特性

MyISAM 索引都是非聚簇索，辅助索引是由B树构成，和主索引树平级。

引擎存储文件

MyISAM 把表和数据划分为三个文件存储不同信息:

.frm 表元信息，统计信息
.MYI (mysiam index): 存放索引定义结构的地方
.MYD (myisam data) : 存放数据的地方

特点

表信息中带有行统计信息，不需扫描即可返回count(*)
所有索引都是非聚簇索引，可以没有主键

MyISAM 特性一览表

InnoDB 和 MyISAM 区别在哪里？

innodb 必须存在主键，即使用户不指定也会自动创建一个隐藏主键并生成聚簇索引，myisam 可以没有主键，也就是说，表中的索引都可以是非聚簇索引
索引关系：MyISAM 各索引之间的关系是平级关系，而InnoDB 不是所以在锁颗粒度上和事务支持上有一定区别
锁颗粒度： MyISAM 仅支持表级，InnoDB 支持到行级（行级特性支持使得发生数据竞争概率变小，并发性能更高）。
支持事务：MyISAM 不支持事务，InnodDB 支持
MVCC：并发版本控制, InnoDB支持，MyISAM 不支持
故障恢复：innodb undolog(事务记录日志)/redolog(数据刷盘日志) 保证了数据安全性，在崩溃时可以从redolog 日志中继续刷盘恢复数据，可以在奔溃后从undolog 中恢复事务和回滚事务，MyISAM仅记录binlog 二进制日志，在写入binlog过程中过程中崩溃(此时数据还未写入.myd文件) 数据无法恢复。
内存利用：*myism 诞生的年代，ram 存储成本时非常昂贵的，所以在利用内存方面myisam 设计基于当时环境诞生的，但随这经济发展，科技进步内存储器成本不断下降，器工作频率越来越高，直到远超过后机械磁盘，固态硬盘；*innodb 正是看到了这未来趋势，做了十分有前瞻性的设计，innodb 引入了在内存中引用了索引四大缓存(排去8.0移除的query buffer), 而myisam 仅有一个查询缓存，在内存利用上Innodb是更充分的。