MySQL数据库存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建，查询，更新和删除数据 不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能，现在许多不同的数据库管理系统都支持多种不同的数据引擎

备注：因为在关系型数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型
（Table Type，即存储和操作此表的类型）

MySQL存储引擎

在mysql5.5之前的版本中，默认是Myisam存储引擎，在5.5版本以后是Innodb. MYSQL给开发者提供了查询存储引擎的功能，执行以下SQL即可查询到mysql中的存储引擎

show engines;

存储引擎之间的区别：大致可分为: 1.支持的字段和数据类型 2.锁定类型 3.索引 4.处理

mysql目前支持的存储引擎有：InnoDB引擎、MyISAM引擎、MERGE存储引擎、MEMORY引擎、
ARCHIVE引擎、CSV引擎、BLACKHOLE引擎（黑洞引擎）、PERFORMANCE_SCHEMA引擎
Federated引擎。
共九种存储引擎

注：另外还有两种存储引擎，BDB(BerkeleyDB)引擎，NDB Cluster引擎

InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，在5.5版本以后，也是mysql的默认事务型引擎，是目前最重要，
使用最广泛的存储引擎，支持事务安全表（ACID），支持行锁和外键

主要特性有：

• 1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。 InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多 用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来， 甚至在同一个查询中也可以混合
• 2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关 系型数据库引擎锁不能匹敌的
• 3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引 而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件 （或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。 InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
• 4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果 没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
• 5、InnoDB被用在众多需要高性能的大型数据库站点上

设计理念：

1，InnoDB默认将数据放到一个逻辑的表空间中，这个表空间就像黑盒子一样由InnoDB自身进行 管理。从mysql4.1开始，它可以将每个 InnoDB存储引擎的表单独存放到一个独立的ibd文件中。 与orcle类似，InnoDB存储引擎同样可以使用裸设备(row disk)来建立其表空间。 2，InnoDB通过使用多版本并发控制(MVCC)来获取高并发性，并且实现了sql标准的4种隔离级别， 默认为REPEATABLE级别。同时 使用一种next-key-locking的策略来避免幻读（phantom）。 除此之外，InnoDB存储引擎还提供插入缓存(insert buffer)，二次写(double write)， 自适应哈希索引(adaptive hash index)，预读(read ahead)等高性能和高可用的功能。

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB 大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。 如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit） 和回滚（rollback）。

MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用 的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。

MyISAM主要特性有： 1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持 2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成 3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16 4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上 5、BLOB和TEXT列可以被索引，支持FULLTEXT类型的索引，而InnoDB不支持这种类型的索引 6、NULL被允许在索引的列中，这个值占每个键的0~1个字节 7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩 8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快 9、可以把数据文件和索引文件放在不同目录 10、每个字符列可以有不同的字符集 11、有VARCHAR的表可以固定或动态记录长度 12、VARCHAR和CHAR列可以多达64KB

存储格式： 1、静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。 2、动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。 3、压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要 保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若 有这个数据类型也同样会有这个问题）

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存 储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出， 但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。 例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所 有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这 些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据， 删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

MEMORY主要特性有： 1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度 2、MEMORY存储引擎执行HASH和BTREE缩影 3、可以在一个MEMORY表中有非唯一键值 4、MEMORY表使用一个固定的记录长度格式 5、MEMORY不支持BLOB或TEXT列 6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引 7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表） 8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享 9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。 由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、 重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。现在 mongodb、redis等NOSQL数据库愈发流行，MEMORY存储引擎的使用场景越来越少。

场景：如果需要该数据库中一个用于查询的临时表。 另外：

虽然使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适
合作为 Mysql 的底层索引的数据结构

ARCHIVE存储引擎

Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在
MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很
好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

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

CSV存储引擎

使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一
个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占
用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为
null。csv的编码转换需要格外注意

场景：这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL 服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可 以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。

BLACKHOLE存储引擎（黑洞引擎）

该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或
同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

场景：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，执行binlogdump命令 而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独 建立一个伪的从服务器或者叫分发服务器。

PERFORMANCE_SCHEMA存储引擎

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

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

Federated存储引擎

该存储引擎可以将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分
布式应用。

Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发 送到远程数据库的表上执行，本地是不存储任何数据的

缺点： 1、对本地虚拟表的结构修改，并不会修改远程表的结构 2、truncate 命令，会清除远程表数据 3、drop命令只会删除虚拟表，并不会删除远程表 4、不支持 alter table 命令 5、select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引） ~~~~ select id from db.tablea where id > 100 limit 10 ; ~~~~ 而以下查询就很快： ~~~~ select id from db.tablea where id >100 and id<150 ~~~~ 6、如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常 7、类似 where name like “str%” limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。

场景： dblink。

BerkeleyDB存储引擎

BerkeleyDB（简称为BDB）是一种以key-value为结构的嵌入式数据库引擎：

• 嵌入式：bdb提供了一系列应用程序接口（API），调用这些接口很简单，应用程序和bdb所提供的库一起编译/链 接成为可执行程序；
• NOSQL：bdb不支持SQL语言，它对数据的管理很简单，bdb数据库包含若干条记录，每条记录由关键字和数据 （key-value）两部分构成。数据可以是简单的数据类型，也可以是复杂的数据类型，例如C语言的结构体，bdb对数据类型不做任何解释，完全由程序员自行处理，典型的C语言指针的自由风格；