MySQL 存储引擎

概念

MySQL 数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要注意的是，存储引擎是基于表的，而不是数据库的（即同一个数据库中的不同表可以有不同的存储引擎）。

查看支持哪些存储引擎

想了解 MySQL 中支持的引擎的情况，可以使用如下命令查看：

1	show engines;

常用搜索引擎介绍

MyISAM

索引顺序存取方法（ISAM, Indexed Sequential Access Method）最初是 IBM 公司发展起来的一个文件系统，可以连续地（按照他们进入的顺序）或者任意地（根据索引）记录任何访问。MySQL 实现 ISAM 为 MyISAM。

MyISAM 是 MYSQL 的默认存储引擎 (5.5版之前)，优势在于占用空间小，处理速度快，但却有一个缺点：不支持事务处理。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小。

每个MyISAM资料表，皆由存储在硬盘上的3个文件所组成，每个文件都以资料表名称为文件主名，并搭配不同扩展名区分文件类型：

.frm 存储资料表定义，此文件非 MyISAM 引擎的一部分。
.MYD 存放真正的资料，是MYData的缩写。
.MYI 存储索引信息，是MYIndex的缩写。

InnoDB

MySQL 5.5 以及之后版本默认存储引擎。

InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。
提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能。

数据和索引存储在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中。

innodb中通过B+树作为索引的数据结构，并且主键所在的索引为ClusterIndex(聚簇索引), ClusterIndex中的叶子节点中保存了对应的数据内容。一个表只能有一个主键，所以只能有一个聚簇索引，如果表没有定义主键，则选择第一个非NULL唯一索引作为聚簇索引，如果还没有则生成一个隐藏id列作为聚簇索引。

除了Cluster Index外的索引是Secondary Index(辅助索引)。辅助索引中的叶子节点保存的是聚簇索引的叶子节点的值。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

每个InnoDB资料表，皆由存储在硬盘上的2个文件所组成，每个文件都以资料表名称为文件主名，并搭配不同扩展名区分文件类型：

.frm 表定义文件。
.ibd 表数据文件。

变量设置

innodb_flush_log_at_trx_commit 设置多少次事务刷新一次事务日志

MEMORY

MEMORY 是 MySQL 中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。

每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为 frm 类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B树索引快。

因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

搜索引擎选择

当你的数据库主要以 查询为主，相比较而言更新和写入比较少，并且业务方面 数据完整性要求不那么严格，就选择 MyISAM 。

当你的数据库有 大量的写入、更新操作 而查询比较少或者 数据完整性要求比较高 的时候就选择 InnoDB。

如果需要 很快的读写速度，对 数据的安全性要求较低，可以选择 MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

InnoDB 与 MyISAM 存储引擎对比

意外恢复、缓存管理、数据压缩、事务、锁、

InnoDB 通过重放其日志从崩溃或其他意外关闭中恢复；而 MyISAM 遇到错误，必须完全扫描并修复或重建任何已更新但未完全刷新到磁盘的索引或可能的表。InnoDB 的修复时间，大略都是固定的，但 MyISAM 的修复时间，则与资料量的多少成正比。相对而言，随着资料量的增加，InnoDB会有较佳的稳定性。
MyISAM 依赖于文件系统块缓存来缓存对数据行和索引的读取，而 InnoDB 则是有自己的读写缓存管理机制。（InnoDB不会将被修改的数据页立即交给操作系统）因此在某些情况下，InnoDB的资料访问会比MyISAM更有效率。
InnoDB 目前并不支持MyISAM所提供的压缩与terse row formats（简洁的行格式），所以对硬盘与缓存的使用量较大。因此MySQL从5.0版开始，提供另一个负载较轻的格式，可减少约略20%的系统负载，而压缩功能已项目于未来的新版中推出。
当操作完全兼容 ACID（事务）时，虽然InnoDB会自动合并多个连接，但每次有事务产生时，仍至少须写入硬盘一次，因此对于某些硬盘或磁盘阵列，会造成每秒200次的事务处理上限。对于需要更高事务率的应用程序，将需要具有写入缓存和备用电池的磁盘控制器，以保持事务完整性。当然InnoDB也提供数种对性能冲击较低的模式，但相对的也会降低事务的完整性。而MyISAM则无此问题，但这并非因为它比较先进，这只是因为它不支持事务。
InnoDB 为数据和索引提供可更新的 LZW 压缩页面存储。 MyISAM 压缩表无法更新。
MyISAM 支持表锁不支持行锁、外键。 InnoDB 支持行锁、外键。

一卓的博客