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表:
暂时:目标数据临时需要且必须立即可用。
相对无关:数据丢失不会造成实质性负面影响,不会对数据完整性有长期影响。