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中不保存表的行数。清空整个表时，InnoDB是一行一行地删除，因此效率非常慢。
• InnoDB使用B+树来存储索引，因此具有查询效率高的特点，并且支持索引上的范围查询
• 它支持外键完整性约束。存储数据时，每张表的存储按主键顺序存放，如果没有显示定义主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
• 它被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录。
• MySQL5.6开始支持全文索引。InnoDB1.2.x开始增加全文索引支持，而MySQL5.6版本中的InnoDB才升级到1.2.x，所以MySQL5.5版本不支持全文索引。

MyISAM

MyISAM基于ISAM存储引擎，并对其进行扩展。它除了提供ISAM里没有的索引的字段管理功能外，它还使用一种表格锁定的机制来优化多个并发的读写操作。该存储引擎管理非事务表，不支持事务。它是在Web、数据仓储和其他应用环境下经常使用的存储引擎之一。在MySQL5.5.5之前版本， MyISAM是默认存储引擎。
MyISAM提供高速存储和检索，以及全文搜索能力。其主要特点如下：

• 不支持事务，不具备事务的ACID特性
• 采用表级锁定。更新数据时锁定整个表，虽然表级锁定实现成本很小，但是却大大降低了其并发的性能
• 读写相互阻塞。不仅会在写入的时候阻塞读取数据的操作，还会在读取的时候阻塞写入数据的操作；读取数据的操作不会阻塞另外的读取数据操作
• 只会缓存索引，不缓存数据
• 读取数据的速度快，占用资源比较少
• 不支持外键约束
• 支持全文检索

还有一些特性：

• 大文件达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) - 索引文件
  所以，MyISAM存储引擎的应用场景如下：
• 不需要事务支持的场景
• 读多或者写多的单一业务场景，读写频繁的则不适合
• 读写并发访问较低的业务
• 数据修改相对较少的业务
• 以读为主的业务
• 对数据的一致性要求不是很高的业务
• 服务器硬件资源相对比较差的场景

MEMORY

Memory引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。数据库中的表如果使用了Memory存储引擎，那么也可以将这张表称为内存表。此时表中的数据只存在于当前MySQL内存中。如果MySQL重新启动或关闭，此时的数据将会丢失。其特性如下：

• 每个表可以有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