目录
一、简介
1.1 定义
1.2 分类
二、两种常用引擎 InnoDB和MyISAM 的原理详解
2.1 InnoDB的原理
2.1.1 后台线程（Thread）
2.1.2 内存（In-Memory Structure）
2.1.2 磁盘（On-Disk Structure）
2.1.3 InnoDB关键特性
2.2 MyISAM的原理
一、简介
1.1 定义
MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
当前主流的mysql版本有 5.6、5.7、5.8，最新版本的8.0性能方面不太看好，可自行比较跟5.7版本的读写速度。
1.2 分类
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用 SHOW ENGINES;语句查看系统所支持的引擎类型，结果如图所示。
每个存储引擎的简易说明：
类型 | 描述 |
MyISAM | 拥有较高的插入、查询速度，但不支持事务 |
InnoDB | 5.5版本后Mysql的默认数据库，5.6版本后支持全文索引，事务型数据库的首选引擎，支持ACID事务，支持行级锁定，数据更新速度较快 |
BDB | 源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性 |
Memory | 所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失 |
Merge | 将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用 |
Archive | 非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差 |
Federated | 将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用 |
NDB | 高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用 |
CSV | 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引 |
BlackHole | 黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继 |
二、两种常用引擎 InnoDB和MyISAM 的原理详解
2.1 InnoDB的原理
InnoDB的整个体系架构就是由多个内存块组成的缓冲池及多个后台线程构成。缓冲池缓存磁盘数据（解决cpu速度和磁盘速度的严重不匹配问题），后台进程保证缓存池和磁盘数据的一致性（读取、刷新），并保证数据异常宕机时能恢复到正常状态。
2.1.1 后台线程（Thread）
InnoDB后台有多个不同的线程，用来负责不同的任务。主要有如下：
- Master Thread
这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲、UNDO 页的回收等. - IO Thread
在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调处理。 - Purge Thread
事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。 - Page Cleaner Thread
Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。
2.1.2 内存（In-Memory Structure）
内存中的结构主要包括 Buffer Pool，Change Buffer、Adaptive Hash Index以及 Log Buffer 四部分。
如果从内存上来看，Change Buffer 和 Adaptive Hash Index 占用的内存都属于 Buffer Pool，Log Buffer占用的内存与 Buffer Pool独立。
Buffer Pool
缓冲池缓存的数据包括Page Cache、Change Buffer、Data Dictionary Cache等，通常 MySQL 服务器的 80% 的物理内存会分配给 Buffer Pool。
基于效率考虑，InnoDB中数据管理的最小单位为页，默认每页大小为16KB，每页包含若干行数据。
为了提高缓存管理效率，InnoDB的缓存池通过一个页链表实现，很少访问的页会通过缓存池的 LRU 算法淘汰出去。
InnoDB 的缓冲池页链表分为两部分：New sublist(默认占5/8缓存池) 和 Old sublist(默认占3/8缓存池，可以通过 innodb_old_blocks_pct修改，默认值为 37)，其中新读取的页会加入到 Old sublist的头部，而 Old sublist中的页如果被访问，则会移到 New sublist的头部。
使用情况可以通过 show engine innodb status\G; 命令查看，其中一些主要信息如下：
Change Buffer
通常来说，InnoDB辅助索引不同于聚集索引的顺序插入，如果每次修改二级索引都直接写入磁盘，则会有大量频繁的随机IO。Change buffer 的主要目的是将对 非唯一 辅助索引页的操作缓存下来，以此减少辅助索引的随机IO，并达到操作合并的效果。它会占用部分Buffer Pool 的内存空间。
在 MySQL5.5 之前 Change Buffer其实叫 Insert Buffer，最初只支持 insert 操作的缓存，随着支持操作类型的增加，改名为 Change Buffer。
如果辅助索引页已经在缓冲区了，则直接修改即可；如果不在，则先将修改保存到 Change Buffer。Change Buffer的数据在对应辅助索引页读取到缓冲区时合并到真正的辅助索引页中。Change Buffer 内部实现也是使用的 B+ 树。
可以通过 innodb_change_buffering 配置是否缓存辅助索引页的修改，默认为 all，即缓存 insert/delete-mark/purge 操作(注：MySQL 删除数据通常分为两步，第一步是delete-mark，即只标记，而purge才是真正的删除数据)。
使用情况可以通过 show engine innodb status\G; 命令查看，其中一些主要信息如下：
Adaptive Hash Index
自适应哈希索引(AHI)查询非常快，一般时间复杂度为 O(1)，相比 B+ 树通常要查询 3~4次，效率会有很大提升。innodb 通过观察索引页上的查询次数，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引，不需要人工干预，可以通过 innodb_adaptive_hash_index 开启，MySQL5.7 默认开启。
考虑到不同系统的差异，有些系统开启自适应哈希索引可能会导致性能提升不明显，而且为监控索引页查询次数增加了多余的性能损耗， MySQL5.7 更改了 AHI 实现机制，每个 AHI 都分配了专门分区，通过 innodb_adaptive_hash_index_parts配置分区数目，默认是8个，如前一节命令列出所示。
使用情况可以通过 show engine innodb status\G; 命令查看，其中一些主要信息如下：
Log Buffer
Log Buffer是 重做日志在内存中的缓冲区，大小由 innodb_log_buffer_size 定义，默认是 16M。一个大的 Log Buffer可以让大事务在提交前不必将日志中途刷到磁盘，可以提高效率。如果你的系统有很多修改很多行记录的大事务，可以增大该值。
配置项 innodb_flush_log_at_trx_commit 用于控制 Log Buffer 如何写入和刷到磁盘。注意，除了 MySQL 的缓冲区，操作系统本身也有内核缓冲区。默认为1，表示每次事务提交都会将 Log Buffer 写入操作系统缓存，并调用配置的 “flush” 方法将数据写到磁盘。设置为 1 因为频繁刷磁盘效率会偏低，但是安全性高，最多丢失 1个 事务数据。而设置为 0 和 2 则可能丢失 1秒以上 的事务数据。为 0 则表示每秒才将 Log Buffer 写入内核缓冲区并调用 “flush” 方法将数据写到磁盘。为 2 则是每次事务提交都将 Log Buffer写入内核缓冲区，但是每秒才调用 “flush” 将内核缓冲区的数据刷到磁盘。
innodb_flush_log_at_timeout 可以配置刷新日志缓存到磁盘的频率，默认是1秒。注意刷磁盘的频率并不保证就正好是这个时间，可能因为MySQL的一些操作导致推迟或提前。
而这个 “flush” 方法并不是C标准库的 fflush 方法(fflush是将C标准库的缓冲写到内核缓冲区，并不保证刷到磁盘)，它通过 innodb_flush_method 配置的，默认是 fsync，即日志和数据都通过 fsync 系统调用刷到磁盘。
可以发现，InnoDB 基本每秒都会将 Log buffer落盘。而InnoDB中使用的 redo log 和 undo log，它们是分开存储的。
redo log在内存中有log buffer，在磁盘对应ib_logfile文件。而undo log是记录在表空间ibd文件中的，InnoDB为undo log会生成undo页，对undo log本身的操作（比如向undo log插入一条记录），也会记录redo log，因此undo log并不需要马上落盘。而 redo log 则通常会分配一块连续的磁盘空间，然后先写到log buffer，并每秒刷一次磁盘。
redo log 必须在数据落盘前先落盘(Write Ahead Log)，从而保证数据持久性和一致性。而数据本身的修改可以先驻留在内存缓冲池中，再根据特定的策略定期刷到磁盘。
使用情况可以通过 show engine innodb status\G; 命令查看。其中一些主要信息如下：
2.1.2 磁盘（On-Disk Structure）
表空间：
分为系统表空间(MySQL 目录的 ibdata1 文件)、临时表空间、常规表空间、Undo 表空间以及 file-per-table 表空间(MySQL5.7默认打开file_per_table 配置）。
系统表空间又包括了InnoDB数据字典，双写缓冲区(Doublewrite Buffer)、修改缓存(Change Buffer、Undo日志等。
通常一个表文件会生成三个后缀的文件，包括.opt、.frm 和 .ibd 。其中 .opt 保存了数据库的默认字符集和校验方法，.frm 是表的数据字典信息，.ibd是表的数据和索引。
Redo日志：
存储的就是 Log Buffer 刷到磁盘的数据。
2.1.3 InnoDB关键特性
1）CheckPoint技术
CheckPoint技术是用来解决如下几个问题：
- 缩短数据库恢复时间
- 缓冲池不够用时，将脏页刷新到磁盘
- 重做日志不可用时，刷新脏页
缩短数据库恢复时间，重做日志中记录了的checkpoint的位置，这个点之前的页已经刷新回磁盘，只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间。
缓冲池不够用时，根据LRU算法，溢出最近最少使用的页，如果页为脏页，强制执行checkpoint，将脏页刷新回磁盘。
重做日志不可用，是指重做日志的这部分不可以被覆盖，为什么？因为：由于重做日志的设计是循环使用的。这部分对应的数据还未刷新到磁盘上。数据库恢复时，如果不需要这部分日志，即可被覆盖；如果需要，必须强制执行checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
2）双写缓冲（Doublewrite Buffer）
先回顾下InnoDB的记录更新流程：先在Buffer Pool中更新，并将更新记录到 Redo Log 文件中，Buffer Pool中的记录会标记为脏数据并定期刷到磁盘。由于InnoDB默认Page大小是16KB，而磁盘通常以扇区为单位写入，每次默认只能写入512个字节，无法保证16K数据可以原子的写入。
如果写入过程发生故障(比如机器掉电或者操作系统崩溃)，会出现页的部分写入(partial page writes)，导致难以恢复。因为 MySQL 的重做日志采用的是物理逻辑日志，即页间是物理信息，而页内是逻辑信息，在发生页部分写入时，无法确认数据页的具体修改而导致难以恢复。
MySQL 的数据页在真正写入到表空间文件前，会先写到系统表空间文件的一段连续区域双写缓冲(Double-Write Buffer，默认大小为 2MB，128个页)并 fsync 落盘，等双写缓冲写入成功后才会将数据页写到实际表空间的位置。
因为双写缓冲和数据页的写入时机不一致，如果在写入双写缓冲出错，可以直接丢弃该缓冲页，而如果是写入数据页时出错，则可以根据双写缓冲区数据恢复表空间文件。
3）自适应哈希索引
哈希是一种非常快的查找方法，在一般情况时间复杂度为O(1)。而B+树的查找次数，取决于B+树的高度，在生成环境中，B+树的高度一般为3-4层，不需要查询3-4次。
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度，这简历哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B+树页构造而来的。因此建立的速度非常快，且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。
AHI有一个要求，对这个页的连续访问模式(查询条件)必须一样的。例如联合索引(a,b)其访问模式可以有以下情况:
- WHERE a=XXX;
- WHERE a=xxx AND b=xxx。
若交替进行上述两张查询，InnoDB存储引擎不会对该页构造AHI。此外AHI还有如下要求： - 以该模式访问了100次；
- 页通过该模式访问了N次，其中N=页中记录/16。
根据官方文档显示，启用AHI后，读取和写入的速度可以提高2倍，负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的，无需DBA对数据库进行人为调整。
4）异步IO(AIO)
为了提高磁盘操作性能，当前的数据库系统都采用异步IO的方式来处理磁盘操作。InnoDB也是如此。
与AIO对应的是Sync IO，即每进行一次IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条SQL语句可能需要扫描多个索引页，也就是需要进行多次IO操作。在每扫描一个页并等待其完成再进行下一次扫描，这是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求，当全部IO请求发送完毕后，等待所有IO操作完成，这就是AIO。
AIO的另外一个优势是进行IO Merge操作，也就是将多个IO合并为一个IO操作，这样可以提高IOPS的性能。
在InnoDB 1.1.x之前，AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后，提供了内核级别的AIO的支持，称为Native AIO。Native AIO需要操作系统提供支持。Windows和Linux都支持，而Mac则未提供。在选择MySQL数据库服务器的操作系统时，需要考虑这方面的因素。
MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中，read ahead方式的读取都是通过AIO完成，脏页的刷新，也是通过AIO完成。
5）刷新邻接页
InnoDB存储引擎在刷新一个脏页时，会检测该页所在区(extent)的所有页，如果是脏页，那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。该工作机制在传统机械磁盘下有显著优势
2.2 MyISAM的原理
MyISAM 存储引擎是 MySQL 中常见的存储引擎，曾（MySQL 5.1及之前版本）是 MySQL 的默认存储引擎。
MyISAM 是基于 ISAM 存储引擎发展起来的。实际上那会还没有存储引擎的概念，ISAM 只是一种算法，或者说是数据的处理方式。如同 SQL Server/Oracle 这类产品一样，MySQL 对表对象的管理方式只有一种。随着 MySQL 架构的不断发展和演进，最终才引入插件式存储引擎的概念，ISAM 也进化为 MyISAM 并一直作为 MySQL 数据库的默认存储引擎，直到 MySQL 5.5 版本才被 InnoDB 引擎取代了默认存储引擎的地位。
2.2.1 MyISAM的优缺点
作为 MySQL 最早的存储引擎之一，MyISAM 有一些已经开发出来很多年的特性，可以满足用户的实际需求。例如全文索引、压缩、空间函数（GIS）等。但 MySQL 官方的重心早就不在 MyISAM 引擎上了，所以近些年来，MyISAM 一直没有很大的改进，也存在着许多的缺陷。
优点：
占用空间小
访问速度快，对事务完整性没有要求或以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表
可以配合锁，实现操作系统下的复制备份
支持全文检索（InnoDB 在 MySQL 5.6 版本以后也支持全文检索）
数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。
1. 加锁与并发
MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入）。
2. 修复
对于 MyISAM 表，MySQL 可以手工（执行命令 CHECK TABLE tablename）或者自动执行检查和修复（执行命令 REPAIR TABLE tablename）操作，但这里说的修复和事务恢复以及崩溃修复是不同的概念。
另外，如果 MySQL 服务器已经关闭，也可以通过 myisamchk 命令行工具进行检查和修复操作。
3. 索引特性
MyISAM 支持以下 3 种类型的索引：
1）B-Tree 索引
B-Tree 索引，顾名思义，就是所有的索引节点都按照 balance tree 的数据结构来存储，所有的索引数据节点都在叶节点。
2）R-Tree 索引
R-Tree 索引的存储方式和 b-tree 索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以对于目前的 MySQL 版本来说，也仅支持 geometry 类型的字段作索引。
3）Full-text 索引
Full-text 索引就是全文索引，它的存储结构也是 b-tree。主要是为了解决需要用 like 查询时的低效问题。
MyISAM 上面三种索引类型中，最经常使用的就是 B-Tree 索引了，偶尔会使用到 Full-text，但是 R-Tree 索引一般系统中都是很少用到的。另外 MyISAM 的 B-Tree 索引有一个较大的限制，那就是参与一个索引的所有字段的长度之和不能超过 1000 字节。
缺点：
不支持事务的完整性和并发性
不支持行级锁，使用表级锁，并发性差
主机宕机后，MyISAM表易损坏，灾难恢复性不佳
数据库崩溃后无法安全恢复
只缓存索引，数据的缓存是利用操作系统缓冲区来实现的，可能会引发过多的系统调用，且效率不佳
2.2.1 物理存储
MyISAM 存储引擎的表在数据库中被存储成 3 个物理文件，文件名与表名相同。扩展名为 frm、MYD 和 MYI。其中：
frm 为扩展名的文件存储表的结构；
MYD 为扩展名的文件存储数据，其是 MYData 的缩写；
MYI 为扩展名的文件存储索引，其是 MYIndex 的缩写。不管表有多少索引，都是存放在同一个 .MYI 文件中。
MyISAM 类型的数据文件和索引文件可以放置在不同的目录，平均分布 IO，以此来获得更快的速度。
要指定索引文件和数据文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，也就是说不同 MyISAM 表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。
虽然每一个 MyISAM 的表数据都存放在后缀名为 .MYD 的文件中，但是每个文件的存放格式可能并不完全一样。因为 MyISAM 支持 3 种不同的数据存放格式，即静态型、动态型和压缩型。
1）静态型（FIXED 静态）
静态型为 MyISAM 存储引擎的默认存储格式，其字段是固定长度，这样每个记录都是固定长度的，这种存储方式存储非常迅速，容易缓存，出现故障容易恢复。缺点是占用的空间比动态表多。静态型的表的数据在存储的时候会按照列的宽度定义去补足空格，但是在应用访问的时候并不会得到这些空格，空格在返回给应用之前就被去掉了。
需要注意的是，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉。这一点开发人员在编写程序的时候需要特别注意，因为静态表是默认的存储格式，开发人员可能并没有意识到这一点，从而丢失了尾部的空格。
2）动态型（DYNAMIC）
动态型包含变长字段，记录的长度不是固定的。这样存储的优点是占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
3）压缩型（COMPRESSED）
与上面两种格式相比，压缩型的表就显得特殊一些。压缩型的表需要使用 myisampack 工具创建，解压缩则用另外的 myisamchk 命令。压缩表是制度的，不支持添加或修改记录。
压缩表是基于静态或动态格式表的，优点在于占用的磁盘空间非常小，可以减少磁盘 I/O，从而提升查询性能。因为每个记录都是被单独压缩的，所以只有非常小的开支。
理论上，MyISAM 存储引擎的表可以被多个数据库实例同时使用同时操作，但是一般不建议这样做，关于这点，MySQL 官方的用户手册中也有提到，建议尽量不要在多个 mysqld 之间共享 MyISAM 存储文件。
如果表在创建并导入数据以后，不会再进行修改操作，这样的表或许适合采用 MyISAM 压缩表。
