现在业务离不开数据，我们开发也离不开数据库，有数据就要存储，不管是NoSql还是关系数据库，本质都是存储数据。目前的关系数据库包括Mysql，Oracle，Sqlserver，PostgrcSQL， DB2，GBase等等。

现在市场主流数据库还是以Mysql和Oracle为主，相信大家不管面试中级或者高级开发，都会遇到数据库引擎的问题，在这里我给大家介绍下mysql引擎，为什么没听过别人谈论Oracle引擎呢？
先给大家说下Oracle：

oracle中不存在引擎的概念，数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。

Oracle中OLTP和OLAP区别：

1.OLTP 系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
2.OLAP 系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。

那么数据库引擎到底是什么？

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。

如何查看mysql引擎：

1.查看表的引擎：show create table t;
2.修改表的引擎：alter table t engine=innodb;
3.查看可以提供哪些引擎：show engines;
4.查看当前默认的存储引擎：show variables like '%storage_engine%';

大家可以用命令(show engines;)先查看自己mysql所支持的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

如图，支持InnoDB,CSV；MyISAM；BLACKHOLE；PERFORMANCE_SCHEMA；MRG_MYISAM；ARCHIVE；MEMORY ；FEDERATED——9个引擎

下面就给大家分析一下各个引擎：

（一）InnoDB

Innodb引擎是最常用的引擎也是mysql默认引擎（注意MySQL-5.5版本之后默认才改成Innodb，之前都是MyISAM），它提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率。

特性：

1:支持事务(要么全成功、要么全失败);
2:行级锁定(更新数据时一般指锁定当前行)：注意间隙锁的影响，行锁和间隙锁通过索引实现，不用索引会表级锁;
3:读写阻塞与事务的隔离级别相关；
4:具有非常高的缓存特性(既能缓存索引、也能缓存数据)；
5:这个表和主键以组(Cluster)的方式存储、组成一颗平衡树；
6:所有的辅助索引(secondary indexes)都会保存主键信息；
7:支持分区、表空间类似与oracle 数据库；
8:支持外键约束、不支持全文检索(5.5.5之前的MyISAM支持全文检索、5.5.5之后就不在支持);
9:相对MyISAM而言、对硬件的要求比较高

应用场景：

1:需要支持事务的业务；
2:行级锁定对于高并发有很好的适应能力、但是需要保证查询是通过索引完成的；
3:数据读写都很频繁的环境(如:BBS、微博等);
4:对数据一致性要求比较高的业务(如充值、银行转转);
5:硬件设备内存较大、可以很好的利用InnoDB较好的缓存能里来提高内存利用率、减少IO的开销；

调优精要：

1:主键尽可能的小、避免给辅助索引(secondary indexes)带来过大的空间负担；
2:避免全表扫描(会使用表锁)；
3:尽可能的缓存所有的索引和数据、提高响应速度、减少磁盘IO消耗；
4:在大批量小插入的时候、尽量自己控制事务、而不使用autocommit自动提交；
5:合理设置Innodb_flush_log_ad_trx_commit 参数值、不要过度追求安全性；
如果值为0、log buffer每秒就会被刷写日志文件进入磁盘、提交事务的时候不做任何操作；
6:避免主键更新(主键更新会带来大量的数据移动)；

总结

如果你需要事务，那就选InnoDB吧

（二）MyISAM

再说MyISAM之前，我们要先说下ISAM,也就是MyISAM的前生

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

应用场景：

1.不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
2.不支持外键的表设计。
3.查询速度很快，如果数据库查询的操作比较多的话比较适用。
4.整天 对表进行加锁的场景。
5.MyISAM极度强调快速读取操作。
6.MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

总结

不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。但是要注意的是不能在表损坏后恢复数据。（是不能主动恢复）

（三）MEMORY

使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。
但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

应用场景：

1.那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。
2.目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
3.数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
4.存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
5.Memory同时支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索引相等的比较快但是对于范围的比较慢很多。

总结:

1.要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）。
2.在用完表格之后就删除表格。
3.我用Redis不香么？

（四）FEDERATED

FEDERATED引擎是MySQL5.5之后才加入，但是默认关闭，所以大家要用的时候要手动打开，需要大家在/etc/my.cnf，加入一行federated。在实际工作中，我们可能会遇到需要操作其他数据库实例的部分表，但又不想系统连接多库。此时我们就需要用到数据表映射。

应用场景：

说到底就是跨库join，比如我库A有user表，库B有role表，但是我要关联查询库A的user表和库B的role表，这时候需要在库A建立role虚拟表（它不需要你插入删除修改，只是用来查询,数据会从B库映射过来），创建表结构的时候加入引擎即可：

CREATE TABLE (......) 
ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename'

username--mysql用户名
password--mysql密码
hostname--ip
port：端口号
database：数据库名
tablename：表名

具体如下：

CREATE TABLE `sys_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '权限名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='权限表'
ENGINE =FEDERATED CONNECTION='mysql://root:123456@127.0.0.2:3306/yang/sys_role'

数据会自动从127.0.0.2:3306/yang/sys_role中映射过来的

总结

1.目标端建表结构可以与源端不一样 推荐与源端结构一致
2.源端DDL语句更改表结构 目标端不会变化
3.源端DML语句目标端查询会同步
4.源端drop表 目标端结构还在但无法查询
5.目标端不能执行DDL语句
6.目标端执行DML语句 源端数据也会变化
7.目标端truncate表 源端表数据也会被清空
8.目标端drop表对源端无影响
9.源端专门创建只读权限的用户来供目标端使用。
10.目标端建议用CREATE SERVER方式创建FEDERATED表。
11.FEDERATED表不宜太多，迁移时要特别注意。
12.目标端应该只做查询使用，禁止在目标端更改FEDERATED表。
13.建议目标端表名及结构和源端保持一致。
14.源端表结构变更后 目标端要及时删除重建。

（五）MRG_MYISAM

截自MySql手册:MERGE存储引擎,也被认识为MRG_MyISAM引擎,是一个相同的可以被当作一个来用的MyISAM表的集合."相同"意味着所有表同样的列和索引信息.你不能合并列被以不同顺序列于其中的表,没有恰好同样列的表,或有不同顺序索引的表.而且,任何或者所有的表可以用myisampack来压缩。

应用场景：

是不是看起来云里雾里？其实他就是用来水平分表的，比如我在表table1，表table2，分别存入数据，表uTable用MRG_MYISAM引擎并且UNION=(table1,table2) ，就可以把A1和A2的数据都聚合过来，当做一个总表。

//新建表table1
CREATE TABLE IF NOT EXISTS `table1` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`name` varchar(50) DEFAULT NULL,  
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
//新建表table2
CREATE TABLE IF NOT EXISTS `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
//在table1插入一条数据
INSERT INTO `table1` (`name`) VALUES('name1');
//在table2插入一条数据
INSERT INTO `table2` (`name`) VALUES('name2');
 //新建表uTable使用MRG_MyISAM引擎并且UNION=(table1,table2)
CREATE TABLE IF NOT EXISTS `uTable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
INDEX(id)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 UNION=(table1,table2) INSERT_METHOD=LAST AUTO_INCREMENT=1;
//查询uTable
select id,name from uTable;
+----+--------+
| id | name   |
+----+--------+
|  1 | name1  |
|  1 | name2  |
+----+--------+
2 rows in set (0.00 sec)

总结

1.主表类似于SQL中的union机制。
2.主表结构必须和基本表完全一致，包括列名、顺序。UNION表必须同属一DABASE。
3.基本表类型必须是MyISAM
4.定义在他上面的约束没有所有作用，约束是由基本表控制的，例如两个基本表中存在着同样的一个Key值，那么在MERGE表中会有两个相同的Key值。
5.每个表的主键要注意，不要用自增主键（可以用专门的一个表生成自增主键）
6.主表一般只允许查询，不允许其他操作

（六）CSV

CSV存储引擎可以将csv文件作为mysql的表进行处理。存储格式就是普通的csv文件。有点类似Oracle的外部表。它可以将“逗号分隔值（CSV）文件”作为表进行处理，但不支持在这种文件上建立相关索引。在服务器运行中，这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。

应用场景：

适合做为数据交换的中间表（能够在服务器运行的时候，拷贝和拷出文件，可以将电子表格存储为CSV文件再拷贝到MySQL数据目录下，就能够在数据库中打开和使用。同样，如果将数据写入到CSV文件数据表中，其它web程序也可以迅速读取到数据。

总结

1.以CSV格式进行数据存储（逗号隔开，引号）
2.所有的列必须都是不能为NULL的
3.不支持索引（不适合大表，不适合在线处理）
4.可以对数据文件直接编辑（保存文本文件内容）
5.数据以文本方式存储在文件中（Innodb则是二进制）

（七）BLACKHOLE

MySQL在5.x系列提供了Blackhole引擎–“黑洞”. 其作用正如其名字一样：任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。

应用场景：

在大规模的Mysql服务器集群中，如果是存在一台主服务，多台从服务器，在繁忙的业务中，意味着主服务器每操作一个事件，都要往自己的二进制日志中写数据，同时还要往多台从服务器发一次，N台服务器指向一台主服务器，那么需要主服务器发送N次，会启动N个线程，每个线程各自从线程里读二进制日志，那么会有大量的IO，本来是为主服务器减轻负担的，那么这样只能造成压力越来越大，那这样master主机就会为每台slave主机分配出一个binlog dump进程，这样的话会严重影响master的性能。
解决这种问题可以采用多级复制，主服务器还是保持主位置A，再拿一台服务器作为从服务器B，主服务器A只启动一个线程指向从服务器B，那么B服务器再作为其他N台服务器的主，那么B就启动了多个线程，怎么给B服务器减轻压力呢？

black.png

在主从之间添加一个分布式master，配置blackhole存储引擎，他起到一个中继的作用，他接收数据但丢其他而不是存储，只是会把master的二进制日志供下层的slave来读取。
第一，让B服务器不再执行查询操作；
第二，让B服务器不再执行写操作；
第三，负责多线程为每个从服务器提供数据，那么就不需要在B服务器存储数据了，但是需要提供二进制日志和中继日志，但B服务器又不需要数据库；
把blackhole引擎，用做slave，配置一些过滤规则，比如复制某些表、不复制某些表。然后也作为一个master，带多个slave。这样的好 处是省了一定的网络带宽，如果没有blackhole做中间环节，那么就需要把第一个master的所有日志都传递到各个slave上去。经过 blackhole这一个slave兼master过滤后再传递给多个slave，减少了带宽占用。而使用blackhole引擎的原因是它不占硬盘空 间，作为一个中转，只负责记日志、传日志。

总结

1.BLACKHOLE支持所有类型的索引
2.BLACKHOLE 表不存储数据，如果复制基于SBR，语句可以记录并在从库执行；如果复制为RBR、MBR，UPDATE及DELETE操作将会跳过，不会记录也从库不执行。
3.Insert触发器可以正常使用，Update、Delete触发器因为不存储数据不能触发，FOR EACH ROW 也不能触发。
4.BLACKHOLE 表Auto Increment字段不会自动递增,也不保留自增字段的状态
5.结合复制replicate-do和replicate-ignore规则，可使用BLACKHOLE当做一个分发主服务器
6.可用来验证转储文件语法
7.测试binlog的开销量，通过对比 BLACKHOLE 与 不启动 binlog的性能
8.可能被用来查找与存储引擎自身不相关的性能瓶颈

（八）PERFORMANCE_SCHEMA

该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

场景：

DBA能够较明细得了解性能降低可能是由于哪些瓶颈。

总结

没用过

（九）ARCHIVE

区别于InnoDB和MyISAM这两种引擎，ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。 archive存储引擎支持insert、replace和select操作，但是不支持update和delete。

场景：

1.存储引擎基本上用于数据归档；它的压缩比非常的高，存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合，由于它不支持索引同时也不能缓存索引和数据，所以它不适合作为并发访问表的存储引擎。
2.由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

总结

由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

最后给大家一个常用引擎区别的表

MYSQL常用的存储引擎的区别

---------------------------------------------------------------------------------------------------------------------
种类  |  锁机制 |   B_树索引  | 哈希索引   |  外键   | 事务   |  索引缓存 | 数据缓存
---------------------------------------------------------------------------------------------------------------------
MYISAM|  表锁  |     支持     | 不支持    |  不支持  |不支持  | 支持      |  不支持 
---------------------------------------------------------------------------------------------------------------------
INNODB|  行锁  |     支持     | 不支持    |  支持    | 支持   | 支持      |  支持 
---------------------------------------------------------------------------------------------------------------------
MEMORY|  表锁  |     支持     | 支持      |  不支持  |不支持  | 支持      |  支持
---------------------------------------------------------------------------------------------------------------------

备注：某些内容源于网络，若有侵权，请及时联系