MySQL 存储引擎

• 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建，查询，更新和删除数据。
• 不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。
• 现在许多不同的数据库管理系统都支持多种不同的数据引擎， MySQL 的核心就是插件式存储引擎。
• MySQL 给用户提供了诸多的存储引擎，包括处理事务安全表的引擎和出来了非事物安全表的引擎。

查看存储引擎

SHOW ENGINES

• Support 列的值表示某种引擎是否能使用： YES 表示可以使用， NO 表示不能使用， DEFAULT 表示该引擎为当前默认的存储引擎。
• 查看数据库默认使用哪个引擎，也可以通过以下命令。

SHOW VARIABLES LIKE 'storage_engine';

• 在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

InnoDB 存储引擎

• InnoDB 是事务型数据库的首选引擎，支持事务安全表（ACID)，支持行锁定和外键， InnoDB 是默认的 MySQL 引擎。
• InnoDB 主要特性：
  • 为 MySQL 提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID 兼容）特性。
  • InnoDB 锁为行级锁，并且也在 SELECT 语句中提供一个类似 Oracle 的非锁定读，这些功能增加了多用户部署和性能，在 SQL 查询中，可以自由地将 InnoDB 类型的表和其他 MySQL 的表类型混合起来，甚至在同一个查询中也可以混合。
  • InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池， InnoDB 将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件），这与 MyISAM 表不同，比如在 MyISAM 表中每个表被存放在分离的文件中， InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为 2 GB 的操作系统上。
  • InnoDB 支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键， InnoDB 会为每一行生成一个 6 字节的 ROWID，并以此作为主键。
• 使用 InnoDB 存储引擎时 MySQL 将在数据目录下创建一个名为 ibdata1 的 10 MB 大小的自动扩展数据文件，以及两个名为 ib_logfile0 和 ib_logfile1 的 5 MB 大小的日志文件。

MyISAM 存储引擎

• MyISAM 基于 ISAM 存储引擎，并对其进行扩展，它是在 Web，数据仓储和其他应用环境下最常使用的存储引擎之一。
• MyISAM 拥有较高的插入，查询速度，但不支持事务。
• MyISAM 主要特性：
  • 被大文件系统和操作系统支持。
  • 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片，这要通过合并相邻被删除的块，若下一个块被删除，就扩展到下一块自动完成。
  • 每个 MyISAM 表最大索引数是 64，这可以通过重新编译来改变，每个索引最大的列数是 16
  • 最大的键长度是 1000 字节，这也可以通过编译来改变，对于键长度超过 250 字节的情况，一个超过 1024 字节的键将被用上。
  • BLOB 和 TEXT 列可以被索引。
  • NULL 被允许在索引的列中，这个值占每个键的 0~1 个字节。
  • 所有数字键值以高字节优先被存储以允许一个更高的索引压缩。
  • 每个 MyISAM 类型的表都有一个 AUTO_INCREMENT 的内部列，当 INSERT 和 UPDATE 操作的时候该列被更新，同时 AUTO_INCREMENT 列将被刷新，所以说， MyISAM 类型表的 AUTO_INCREMENT 列更新比 InnoDB 类型的 AUTO_INCREMENT 更快。
  • 可以把数据文件和索引文件放在不同目录。
  • 每个字符列可以有不同的字符集。
  • 有 VARCHAR 的表可以固定或动态记录长度。
  • VARCHAR 和 CHAR 列可以多达 64 KB
• 使用 MyISAM 引擎创建数据库，将产生 3 个文件，文件的名字以表名字开始，扩展名之处文件类型： frm 文件存储表定义，数据文件的扩展名为. MYD (MYData)，索引文件的扩展名时. MYI (MYIndex)

MEMORY 存储引擎

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

存储引擎的选择

• 在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题，每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好，但建议选择使用 InnoDB

特性	InnoDB	MyISAM	MEMORY
事务安全	支持	无	无
存储限制	64 TB	有	有
空间使用	高	低	低
内存使用	高	低	高
插入数据速度	低	高	高
对外键的支持	支持	无	无

• InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制，如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择 InnoDB 有很大的优势，如果需要频繁的更新，删除操作的数据库，也可以选择 InnoDB，因为支持事务的提交（commit）和回滚（rollback)
• MyISAM：插入数据快，空间和内存使用比较低，如果表主要是用于插入新记录和读出记录，那么选择 MyISAM 能实现处理高效率，如果应用的完整性，并发性要求比较低，也可以使用。
• MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高，如果需要很快的读写速度，对数据的安全性要求较低，可以选择 MEMOEY，它对表的大小有要求，不能建立太大的表，所以，这类数据库只使用在相对较小的数据库表。

总结：同一个数据库也可以使用多种存储引擎的表，如果一个表要求比较高的事务处理，可以选择 InnoDB，这个数据库中可以将查询要求比较高的表选择 MyISAM 存储，如果该数据库需要一个用于查询的临时表，可以选择 MEMORY 存储引擎。

不同的存储引擎支持的索引类型也不一样

• InnoDB 支持事务，支持行级别锁定，支持 B-tree, Full-text 等索引，不支持 Hash 索引。
• MyISAM 不支持事务，支持表级别锁定，支持 B-tree, Full-text 等索引，不支持 Hash 索引。
• Memory 不支持事务，支持表级别锁定，支持 B-tree, Hash 等索引，不支持 Full-text 索引。
• NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree, Full-text 等索引。
• Archive 不支持事务，支持表级别锁定，不支持 B-tree, Hash, Full-text 等索引。