MySQL 常用存储引擎详解和区别

Question

MySQL 提供多种存储引擎对数据表进行处理，MySQL 5.1 (如无特殊说明, 本文所使用版本均为 5.1) 引入了新的插件式存储引擎体系结构，对于编程人员而言，插拔及修改存储引擎都是极其方便的。

MyISAM

• 表级锁
• 数据非自动恢复
• 不支持事务
• 仅索引加载在内存中，数据缓存由操作系统
• 紧凑的数据存储，正是这样才能实现高速的数据扫描

HEAP

• 表级锁
• 不支持动态数据类型Text和BLOB，varchar会被当做成char来处理
• 默认索引类型是Hash索引
• 不支持索引统计
• MySQL数据库服务器重启后数据全部丢失

InnoDB

• 支持事务
• 支持外键（version 5.0，only the storage engine level support）
• 行级锁
• 多版本支持
• 主键聚集，所有InnoDB表都依赖于主键聚集
• 所有的非主键索引内容包含主键列的值
• 最优化缓存，缓存中包含索引和数据，innodb_buffer_pool_size.InnoDB缓存采用的是自动hash便于查找
• 非压缩的索引
• 缓慢的数据加载，因为MySQL 5.0没有特别优化数据加载操作，每条记录建一次索引，所以大数据量的服务重启会很慢。
• AUTO_INCREMENT锁表，在MySQL 5.1以前的InnoDB中，如果主键列采用了AUTO_INCREMENT则是表级锁。在MySQL5.1以后支持innodb_autoinc_lock_mode，默认值1，含义是bulk insert。0是传统的表级锁，2是交叉的表级锁（interleaved：fastest and most scalable lock mode，but not safe）。
• 不缓存COUNT（*）的值

纵观以上3中常用的存储引擎，InnoDB和Heap在以后新的应用中更有可用性。

以下再看看MyISAM和InnoDB的索引图示

存储引擎简介

这些引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

• MyISAM 管理非事务表. 提供高速存储和检索, 以及全文搜索能力. 主要用于 Web, 数据仓库等应用环境. 是 5.5 之前的默认存储引擎 (可以通过配置修改).
• InnoDB 提供事务安全表 (ACID 兼容). 支持 commit, rollback, crash-recovery.
• BDB 提供事务安全表.
• Memory 将所有数据存储在 RAM 中以提高存取速度, 其正式称谓为 HEAP 引擎.
• Merge 管理非事务表. 允许 DBA 或开发者将一组逻辑相关的 MyISAM 表视为一张单独的表.
• Archive 存储及索引大批量存档数据.
• Federated 联合多台物理数据库服务器, 组合成一个逻辑数据库.
• NDBCLUSTER (NDB) 集群数据库存储引擎.
• CSV 把数据以逗号分隔的格式存储在文本文件中. 用于应用程序间导入/导出 CSV 格式数据.
• Blackhole 接受但不存储数据, 并且检索总是返回一个空集. 可用于分布式数据库设计 (数据自动复制, 不在本地存储).
• Example "存根 (stub)" 引擎, 不做什么事. 可以用它创建表, 但不能用于存储或检索数据. 用于向开发者展示如何编写 MySQL 存储引擎.

Feature	MyISAM	Memory	InnoDB	Archive	NDB
Storage limits	256TB	RAM	64TB	None	384EB
Transactions	No	No	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	No	No	No
B-tree indexes	Yes	Yes	Yes	No	Yes
Hash indexes	No	Yes	No	No	Yes
Full-text search indexes	Yes	No	No	No	No
Clustered indexes	No	No	Yes	No	No
Data caches	No	N/A	Yes	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Compressed data	Yes	No	Yes	Yes	No
Encrypted data	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Replication support	Yes	Yes	Yes	Yes	Yes
Foreign key support	No	No	Yes	No	No
Backup / point-in-time recovery	Yes	Yes	Yes	Yes	Yes
Query cache support	Yes	Yes	Yes	Yes	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

一般应用程序中常用的存储引擎主要是 MyISAM 与 InnoDB 两种.

MyISAM

MyISAM 是 MySQL 5.5 之前 (不含 5.5) 的默认存储引擎, MyISAM 的最大缺陷在于不支持事务处理 (transaction).

每个 MyISAM 表在磁盘上存储成三个文件 (如 /var/lib/mysql/db 下). 文件名即为表名, 扩展名指出文件类型. 表格式文件的扩展名为 .frm; 数据文件的扩展名为.MYD (MYData); 索引文件的扩展名是.MYI (MYIndex).

InnoDB

从 MySQL 5.5 开始, InnoDB 成为 MySQL 的默认存储引擎.

InnoDB 为 MySQL 提供了具有提交, 回滚和崩溃恢复能力的事务安全 (ACID兼容) 存储引擎. InnoDB 设计目的是为处理巨大数据量时发挥最大性能. 它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的.

InnoDB 存储引擎被完全与 MySQL 服务器整合, InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池. InnoDB 将其表 & 索引存储在一个表空间中, 表空间可以包含数个文件 (或原始磁盘分区), 这与 MyISAM 表中每个表被存在分离的文件中有所不同. InnoDB 表可以是任何尺寸, 即使在文件尺寸被限制为 2GB 的操作系统上.

InnoDB 被用在众多需要高性能的大型数据库站点上.

常用命令

创建表时指定存储引擎

mysql> CREATE TABLE t (i INT) ENGINE = MYISAM;

修改表的存储引擎

mysql> ALTER TABLE t ENGINE = MYISAM;

显示存储引擎状态信息

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV 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       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
显示当前默认存储引擎
mysql> SHOW VARIABLES LIKE '%storage_engine%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)

显示当前默认存储引擎

$ mysqlshow -u<user> -p<password> --status <database>
mysql> SHOW TABLE STATUS FROM trac;

数据库备份与恢复

MyISAM 表保存为文件方式, 很容易备份. 为保持备份一致性, 对相关表执行 LOCK TABLES 操作进行读锁定 (复制数据库目录中的文件时, 允许其它客户继续查询表), 然后对表执行 FLUSH TABLES. 你只需要读锁定; 这样当你复制数据库目录中的文件时, 允许其它客户继续查询表. 需要 FLUSH TABLES 语句来确保开始备份前将所有激活的索引页写入硬盘.

只要服务器不再进行更新, 还可以只复制所有表文件(.frm、.MYD和*.MYI文件). mysqlhotcopy 脚本使用该方法. (但请注意如果数据库包含 InnoDB 表, 这些方法不工作. InnoDB 不将表的内容保存到数据库目录中, mysqlhotcopy 只适合 MyISAM 表）.

如果你在服务器上进行备份, 并且表均为MyISAM表, 应考虑使用mysqlhotcopy, 因为可以更快地进行备份和恢复:

$ mysqlhotcopy db_name [/path/to/new_directory]
$ mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
$ mysqlhotcopy db_name./regex/

对于 InnoDB 表, 可以进行在线备份, 不需要对表进行锁定:

$ mysqldump [options] db_name [tables]
$ mysqldump [options] ---database DB1 [DB2 DB3...]
$ mysqldump [options] --all--database