mysql笔记：6. 存储引擎

数据库存储引擎是数据库底层组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等，使用不同的存储引擎，还可以获得特定的功能。
mysql提供多个不同的存储引擎，包括处理事务安全表的引擎和非事务安全表的引擎。在mysql中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对不同的表使用不同的存储引擎。
mysql支持的存储引擎有：InnoDB/MyISAM/MEMORY/MERGE/ARCHIVE/FEDERATED/CSV/BLACKHOLE等。

查看引擎信息

可以使用show engines查看系统中的引擎信息。

mysql> show engines;

常用引擎介绍

InnoDB

InnoDB存储引擎为事务弄数据库的首选引擎，支持事务安全表，支持行锁和外键。MySQL 5.5.5后，它也是默认存储引擎。其特性如下：

• 具有提交、回滚和崩溃恢复能力的事务安全性，其锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。在SQL查询中，可以自由将InnoDB类型的表与其他类型的表混合起来。
• 它是为处理巨大数据量的最大性能设计。其CPU效率可能是其他基于磁盘的关系数据库引擎所不能匹敌的。
• 它完全与MySQL服务器整合，在主内存中缓存数据和索引而维持其自己的缓冲池。InnoDB表可以是任意尺寸的，即便是文件尺寸被限制为2GB也不影响。而在MyISAM表中，每个表被存在分离的文件中。
• 它支持外键完整性约束。存储数据时，每张表的存储按主键顺序存放，如果没有显示定义主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
• 它被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录。

MyISAM

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下经常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但它不支持事务。在MySQL5.5.5之前版本， MyISAM是默认存储引擎。其主要特点如下：

• 大文件达63位文件长度。
• 当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少的碎片。
• 每个表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列是16。
• 最大的键长度是1000字节，这也可以通过编译来改变。
• BLOB和TEXT列可以被索引
• NULL值被允许在索引的列中。这个值占每个键的0-1个字节
• 所有数字键值以高字节优先被存储以允许一个更高的索引压缩
• 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当执行INSERT和UPDATE时该列被更新，所以MyISAM表的AUTO_INCREMENT列更新比InnoDB更快
• 可以把数据文件和索引文件放在不同目录
• 每个字符列可以有不同的字符集
• 有VARCHAR的表可以固定或动态记录长度
• VARCHAR和CHAR列可以多达64KB
  使用MyISAM创建数据库时，将生成三个文件。文件名以表名开始，并以不同的扩展名来指出文件类型：
• .frm - 文件存储表定义
• .MYD(MYData) - 数据文件
• .MYI(MYIndex) - 索引文件

MEMORY

Memory引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。其特性如下：

• 每个表可以有32个索引，每个索引16列，以及500字节的最大键长度
• 存储引擎执行HASH和BTREE索引
• 可以在一个表中有非唯一键
• 使用一个固定的记录长度格式
• 不支持BLOB和TEXT列
• 支持AUTO_INCREMENT列和对可包含NULL值的列的索引
• 在所有客户端之间共享
• 当MEMORY表和服务器空闲时，MySQL会在内存中创建表共享
• 当不再需要表内容时，想释放被表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE命令，或者使用DROP TABLE直接删除整个表

存储引擎的选择

不同的存储引擎特点不同，为了在实际应用中作出选择，先需要考虑每一个存储引擎提供了哪些不同的功能。

• 如果要提供提交、回滚和崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是不错的选择。
• 如果表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
• 如果只是临时存储数据，数据量不大，并且不需要较高的数据安全性，可以选择MEMORY引擎。MySQL也使用该引擎作为临时表，存放查询的中间结果
• 如果只有INSERT和SELECT操作，可以选择ARCHIVE引擎，它支持高并发的插入操作，但本身不是事务安全的，非常适合存储归档数据，如记录日志信息等。

几种常用引擎的功能比较

功能	InnoDB	MyISAM	MEMORY	ARCHIVE
存储限制	64TB	256TB	RAM	None
支持事务	Yes	No	No	No
支持全文索引	No	Yes	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	No	Yes	No
支持数据缓存	Yes	No	N/A	No
支持外键	Yes	No	No	No