MySQL数据引擎(一)

MySQL引擎

MySQL 的核心就是存储引擎。
提示：InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。
MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

• MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。
• MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。MySQL 中使用该引擎作为临时表，存放查询的中间结果。
• Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据，如记录日志信息可以使用 Archive 引擎。

查询版本5.7.26支持如下(显示命令show engines)：

#	Engine	support	comment	Transactions	XA	savepoints
1	MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
2	MEMORY	YES	Hash based,stored in memory,useful for temporary tables	NO	NO	NO
3	BLACKHOLE	YES	/dev/null storage engine(anything you write to it disappears)	NO	NO	NO
4	MyISAM	YES	MyISAM storage engine	NO	NO	NO
5	CSV	YES	CSV storage engine	NO	NO	NO
6	ARCHIVE	YES	Archive storage engine	NO	NO	NO
7	PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
8	FEDERATED	NO	Federated MySQL storage engine
9	InnoDB	DEFAULT	Support stransactions,row-levellocking,and foreignkeys	YES	YES	YES

如何选择数据库引擎:

优先选择引擎	业务需要
InnoDB	要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制
MyISAM	主要是插入和查询记录
MEMORY	只是临时存放数据，数据量不大,并且不需要较高的数据安全性
Archive	只有 INSERT 和 SELECT 操作

引擎特性对比:

#	InnoDB	MyISAM	MEMORY	MEGER	NDB
存储限制	64TB	有	有		有
事务安全	支持
锁机制	行锁（高并发）	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低		低	低
内存使用	高	低	中等	低	低
批量插入速度	低	高	高	高	高
支持外键	支持

1. InnoDB

事务性数据库首选，支持事务安全，支持行锁定，是默认的数据库引擎
特性：
1、具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。
2、为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、完全与MySQL服务器整合，为主内存中缓存数据和索引而维持它自己的缓冲池
4、支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

2. MyISAM存储引擎

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

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

3. MEMORY存储引擎

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