MySQL指定存储引擎命令_MySQL常用指令（2）——存储引擎

MySQL常用指令(2)——存储引擎

查询MySQL支持的存储引擎

查询全部支持的引擎：

show engines;

“;”可以使用“\g”等价替换,而使用“\G”会使输出更为美观。

查询默认存储引擎：

show variables like 'storage_engine%';

InnoDB存储引擎

一般作为默认存储引擎。InnoDB是MySQL上第一个提供外键约束的表引擎。InnoDB拥有MySQL其它引擎无法比拟的事务处理能力。

InnoDB引擎支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空，且值必须唯一。MySQL中规定自增列必须为主键。在插入值时，如果自动增长列不输入值，则插入的值为自动增长后的值；如果输入的值为0或空(NULL)，则插入的值也为自动增长后的值；如果插入某个确定值，且该值未在先前出现过，则可以直接插入。

InnoDB引擎支持外键(FOREIGN KEY)。外键所在的表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时，子表也必须有相应的更改。InnoDB存储引擎中，创建的表的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path表空间中。

InnoDB优缺点

InnoDB的优势是良好的事务管理、崩溃修复能力和并发控制。缺点是读写效率较差，占用数据空间较大。

InnoDB是如下情况的理想引擎：

更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。

事务：InnoDB引擎是唯一支持事务的标准MySQL存储引擎，是管理敏感数据(如金融信息和用户注册信息)的必需软件。

自动灾难恢复：与其它存储引擎不同，InnoDB表支持自动从灾难中恢复。虽然MyISAM表能在灾难后恢复，但过程复杂。

MyISAM存储引擎

MyISAM曾经是MySQL的默认存储引擎，由ISAM发展而来，解决了ISAM多种缺陷。

MyISAM存储引擎的文件类型

MyISAM存储的表存储成3个文件。文件名称与表名相同，扩展名包括.frm、.MYD、.MYI。

.frm：存储表的结构。

.MYD：存储数据，即MYData。

.MYI：存储索引，即MYIndex。

MyISAM存储引擎的存储格式

基于MyISAM的表有3种存储格式，包括静态型、动态型、压缩型。

MyISAM静态：如果所有表列的大小都是静态的(即不使用xBLOB、xTEXT或VARCHAR数据类型)，就会自动使用静态MyISAM格式。这种类型的表性能非常高，因为维护和访问预定义格式的数据时开销很低。但这项优点以空间为代价，因为每列都需要分配最大空间，无论是否真正需要使用。

MyISAM动态：如果有表列(即使只有一列)定义为动态的(使用xBLOB、xTEXT或VARCHAR)，MySQL就会自动使用动态格式。这能够节省空间，但会使性能下降。如果某个字段内容发生改变，则其位置很可能需要移动，这会导致碎片的产生。随着数据集中碎片增加，数据访问性能就会相应下降。修复方法有：

① 尽可能使用静态数据类型。

② 经常使用OPTIMIZE TABLE语句，以整理表的碎片，恢复由于表更新和删除而导致的空间丢失。

MyISAM压缩：有时需要创建整个应用程序生命周期中都只读的表。这种情况下可以使用myisampack工具将其转换为MyISAM压缩表来减少占用空间。在给定硬件条件下性能提升显著。

MyISAM优缺点

MyISAM存储引擎的优势在于占用空间小，处理速度快；缺点是不支持事务的完整性和并发性。

MEMORY存储引擎

MEMORY存储引擎是MySQL存储引擎中的特殊一类，其使用存储在内存中的内容来创建表，且所有数据也放在内存中。这些特性与InnoDB、MyISAM不同。

MEMORY存储引擎的文件存储形式

每个基于MEMORY存储引擎的表实际上对应一个磁盘文件。该文件的文件名与表名相同，类型为.frm。该文件仅存储表的结构，数据文件则存储在内存中，这样有利于对数据快速处理，提高整个表的处理效率。同时需要服务器具备足够的内存以维持MEMORY引擎的表的使用，当不再使用时，可以释放内存删除不需要的表。

MEMORY存储引擎的索引类型

MEMORY存储引擎默认使用哈希(HASH)索引，其速度比使用B树(BTREE)索引快。

MEMORY存储引擎的存储周期

基于内存，且MEMORY通常很少被使用，表基本只供一次性使用。

MEMORY优缺点

MEMORY表大小受限，取决于两个参数max_rows和max_heap_table_size。其中，max_rows可在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需扩大。其存在于内存的特性，决定了这类表处理速度非常快、易丢失、生命周期短的特点。

创建MySQL MEMORY存储引擎的出发点是速度。但是在守护进程mysqld崩溃时，所有的MEMORY数据都会丢失。

MEMORY表不支持VARCHAR、BLOB和TEXT数据类型，即这种表只能按照固定长度的记录格式存储。此外，使用版本4.1.0之前的MySQL，不支持自动增加列(通过AUTO_INCREMENT属性)。

当具有如下情况时，可以考虑使用MEMORY表：

暂时：目标数据临时需要且必须立即可用。

相对无关：数据丢失不会造成实质性负面影响，不会对数据完整性有长期影响。