面试时你必须知道的MySQL存储引擎

MySQL数据库

作为开源软件的代表，MySQL数据库凭借它日益完善的功能、不断提高的可靠性以及可移植性，已经成为互联网平台上应用广泛的数据库软件。像国内阿里、京东、腾讯，国外的亚马逊、Facebook等都大量地应用了MySQL数据库，可以说MySQL是现在开发者必须要了解的数据库之一。

MySQL数据库存储引擎

MySQL数据库区别于其他数据库最重要的一个特点就是插件式的存储引擎架构。存储引擎的好处是，每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。对于开发人员来说，存储引擎对其是透明的，但了解各种存储引擎的区别对于开发人员来说也是有好处的。下面我就来介绍一下MySQL中主要的几种存储引擎。

先看一下常用的几种引擎的对比

MyISAM存储引擎

从表中我们可以看到，MyISAM存储引擎不支持事务、行锁设计，但支持全文索引(一种基于分词创建的索引),主要面向一些OLAP数据库应用。

加锁

MylSAM还使用一种表格锁定的机制，我们知道，其实在多线程情况下，对于数据是不安全的，因为多线程下数据是共享的。那么既要保证数据安全性，有不能降低查询效率，就要用到锁的概念。MyISAM对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。这种表格锁定机制用来优化多个并发的读写操作，但是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。但在这个时候，表是被锁定的，无法进行其他操作。

写入

MyISAM表可以包含动态或者静态（长度固定）行。MySQL会根据表的定义来决定采用何种行格式。
创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in-memorykey buffer），只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

压缩

MyISAM存储引擎表由拓展名为.frm、.MYD和.MYI的文件组成。
.frm （用来存储表的结构）
.MYD（用来存放数据文件）
.MYI （用来存放索引文件）
可以通过使用myisampack 工具来进一步压缩数据文件，因为myisampack工具使用赫夫曼（Huffman）编码静态算法来压缩数据，因此使用myisampack工具压缩后的表是只读的，当然用户也可以通过myisampack来解压数据文件。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘I/O，从而提升查询性能。

MYISAM 强调了快速读取操作，所以在WEB开发时优势尤其明显，因为在WEB开发中你所进行的大量数据操作都是读取操作。

此外，MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存（cache）索引文件，而不缓冲数据文件，这点和大多数的数据库都非常不同。

InnoDB存储引擎

这个可以说是现在MySQL最常用的引擎了，从MySQL5.5版本开始就成为了默认的表存储引擎。该存储引擎完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU。

多线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。
1.Master Thread
Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（INSERTBUFFER）、UNDO页的回收等。
2.IO Thread
在InnoDB存储引擎中大量使用了AIO（AsyncI0）来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些I0请求的回调（call back）处理。
3.Purge Thread
事务被提交后，其所使用的undolog可能不再需要，因此需要PurgeThread来回收已经使用并分配的undo页。
4.Page Cleaner Thread
Page Cleaner Thread是在InnoDB1.2.x版本中引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻原Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

存储

对于表中数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

InnoDB存储引擎将数据放在一个逻辑的表空间中，这个表空间就像黑盒一样由InnoDB存储引擎自身进行管理。它可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。此外，InnoDB存储引擎支持用裸设备（row disk）用来建立其表空间。

高并发

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

MEMORY存储引擎

使用存在于内存中的内容来创建表。如果数据库重启或发生崩溃，表中的数据都将消失。它适合用于存储临时数据的临时表或者数据仓库中的纬度表。Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引。

虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如，只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段（varchar）时是按照定常字段（char）的方式进行的，因此会浪费内存。

所以服务器需要足够内存来维持所有在同一时间使用的MEMORY表，当不再需要 MEMORY表的内容之时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者整个地删除表（使用DROPTABLE操作）。

MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃。

NDB存储引擎

NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC集群，不过与Qracle RAC share everything架构不同的是，其结构是share nothing的集群架构，因此能提供更高的可用性。

NDB的特点是数据全部放在内存中（从MySQL5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找（primary key lookups）的速度极快，并且通过添加NDB数据存储节点（Data Node）可以线性地提高数据库性能，是高可用、高性能的集群系统。

关于NDB存储引擎，有一个问题值得注意，那就是NDB存储引擎的连接操作（JOIN）是在MySQL数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。

怎样选择存储引擎？

对于如何选择存储引擎，我记得书上有句话：“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎”。

如果不需要用到InnoDB的特性，同时其他引擎的特性能够更好地满足需求，也可以考虑一下其他存储引擎。

举个例子，如果不在乎可扩展能力和并发能力，也不在乎崩溃后的数据丢失问题，却对InnoDB的空间占用过多比较敏感，这种场合下选择MyISAM就比较合适。

选择存储引擎是应考虑下面几个因素：
1.是否需要事务
如果应用需要事务支持，那么InnoDB是目前的不二之选。如果不需要事务，并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型的应用比较符合这一特性。
2.是否需要备份
备份的需求也会影响存储引擎的选择。如果可以定期地关闭服务器来执行备份，那
么备份的因素可以忽略。反之，如果需要在线热备份，那么选择InnoDB就是基本的要求。
3.系统崩溃恢复
数据量比较大的时候，系统崩溃后如何快速地恢复是一个需要考虑的问题。相对而言，MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。因此，即使不需要事务支持，很多人也选择InnoDB引擎，这是一个非常重要的因素。

常用命令

说了这么多理论知识，我们看看几个常用的查看和切换存储引擎的命令

查看数据库支持的引擎

show engines

查看表使用的引擎

show create table table name

查看当前数据库默认引擎

show variables like %storage engine%';

修改存储引擎

mysq1>ALTER TABLE 表名 ENGINE = 引擎名称;

注意：如果转换表的存储引擎，将会失去和原引擎相关的所有特性。例如，如果将一张InnoDB表转换为MyISAM，然后再转换回InnoDB，原InnoDB表上所有的外键将丢失。

总结

讲了这么多，我们总结一下
如果面试官问你：讲一下常用的MySQL引擎，他们有什么区别，各自的使用场景呢？
MySQL常见的引擎有：InnoDB，MyISAM，MEMORY，NDB等
区别：可以看一下文章前面那张表，对比记忆一下，使用场景一定要和他们各自的特性联系起来。

InnoDB：行级锁，是MySQL5.5后默认的存储引擎，InnoDB第一个完整完整支持ACID事务的MySQL存储引擎。但处理效率相对较差，占用空间较高。除非需要用到某些InnoDB不具备的特性，且没有好的替代方法，都应该优先选择InnoDB引擎。

MyISAM：表级锁，引擎不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。

Memory：表级锁，存储引擎速度非常快，但在使用上还是有一定的限制。比如，只支持表锁，并发性能较差，适合用于存储临时数据的临时表数据或保存数据分析中产生的中间数据。

参考书籍：《MySQL技术内幕》

大家好,我是猿出没,平常主要写一些基础知识加深自己的印象,能和大家分享我也很高兴.我会尽力写一些能帮助到大家的文章,如果文章中有错误的地方,请各位前辈指出,我一定及时改正。编程小白可以关注我的公众号，和我一起学习交流，也可以给你们分享一些我在用的比较好的编程工具或者学习资源、书籍等。