MySQL之存储引擎

一、概述

存储引擎的概念是MySQL的一个特性，可简单理解为表类型；每一个表都有一个存储引擎，可在创建时指定，也可之后使用ALTER TABLE语句修改，都是通过ENGINE关键字设置的；若创建时没有指定，则为默认存储引擎，默认存储引擎也可通过参数文件中default-table-type参数修改。

----查看当前默认存储引擎，默认为MyISAM

mysql> show variables like 'table_type';

----查看当前数据库支持的存储引擎

mysql> show engines;

MySQL有多种存储引擎：

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

二、分类描述

MyISAM

默认存储引擎，不支持事务、外键，但访问速度快，对事务完整性不要求，适合于以SELECT/INSERT为主的表；每个MyISAM物理上存储为3个文件，文件名与表名相同，扩展名分别为：.frm(存储表定义)、MYD(MYData存储数据)、MYI(MYIndex存储索引)，其中数据文件和索引文件可以放置在不同目录，平衡I/O。

数据文件和索引文件的路径，需要在创建表时通过DATADIRECTORY和INDEX DIRECTORY语句指定，需要绝对路径，且具有访问权限。

     MyISAM类型的表可能因各种原因损坏，可通过CHECK TABLE语句检查表的健康，使用REPAIR TABLE语句修改。

     MyISAM类型的表支持3中不同的存储格式，如下：

静态表：默认存储格式，字段长度固定，存储迅速，容易缓存，缺点是占用空间多。注意：字段存储按照宽度定义补足空格，应用访问时去掉空格，若字段本身就带有空格，也会去掉，这点特别注意。

动态表：变长字段，记录不是固定长度，优点是占用空间少，但频繁的更新删除操作会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk –r命令来改善，出现故障时难以恢复。

压缩表：由myisampack工具创建，每个记录单独压缩，访问开支小，占用空间小。

InnoDB

具有提交、回滚、崩溃恢复的事务安全，相对MyISAM来说，写处理能力差些，且会占用较多磁盘空间以保留数据和索引。具有以下不同于其他引擎的特点：

（1）自动增长列

InnoDB表可定义自动增长列，若插入的值为0或空时，则实际插入的值为自动增长后的值；可通过ALTER table语句强制设置自动增长列的初始值，默认从1开始，该值保存在内存中，若数据库重启，则会丢失；可使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值，若一次插入多条记录，则返回第一条记录使用的自动增长值。

对于InnoDB表，自动增长列必须是索引，或者是组合索引的第一列；对于MyISAM表，可以为组合索引的其他列，插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

（2）外键约束

只有InnoDB引擎支持外键约束；创建索引时，可指定删除、更新父表时，对子表的相应操作；RESTRICT/NO ACTION相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或删除时，更新或删除子表对应记录；SET NULL表示父表在更新或删除时，子表对应字段被SET NULL。

（3）存储格式

InnoDB存储表和索引有两种方式：

使用共享表空间存储――表结构保存在.frm文件中；数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以为多个文件

使用多表空间存储――表结构仍然存储在.frm文件中，但每个表的数据和索引单独保存在.ibd中。若为分区表，则每个分区对应单独的.ibd文件，文件名为表名+分区名

使用多表空间存储，需设置参数innodb_file_per_table，并重启服务才可生效，只对新建表有效

MEMORY

该存储引擎使用存在于内存中的内容来创建表，每个表实际对应一个磁盘文件，格式为.frm；这类表因为数据在内存中，且默认使用HASH索引，所以访问速度非常快；但一旦服务关闭，表中的数据会丢失。

每个MEMORY表可以放置数据量的大小受max_heap_table_size系统变量的约束，初始值为16MB，可按需求增大；此外，在定义MEMORY表时可通过MAX_ROWS子句定义表的最大行数。

该存储引擎主要用于那些内容稳定的表，或者作为统计操作的中间表；对该类表需谨慎，因为数据并没有实际写入磁盘，一旦重启，则会丢失。

MERGE

该引擎是一组MyISAM表的组合，这些表必须结构相同，MERGE表本身没有数据；对MERGE表查询、更新、删除操作实际上是对内部实际MyISAM表进行的；对于MERGE表的插入，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST/LAST值使得插入操作被相应地作用在第一个或最后一个表上，不定义这个子句或定义为NO表示不能对这个MERGE表执行插入操作。

     DROP操作只是删除MERGE地定义，对内部表没有任何影响。

     MERGE表在磁盘上保存为两个文件，文件名以表名开始，.frm文件存储表定义，.MRG文件包含组合表地信息，如：由那些表组成、插入新记录时的依据。

     MERGE表不能智能的将记录插入对应的表中，而分区表可以，这是两者的区别。

三、 MyISAM和InnoDB的区别

InnoDB和MyISAM是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB 类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

移值性: 
MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。

MyISAM是ISAM表的新版本，有如下扩展：  
二进制层次的可移植性。  
NULL列索引。  
对变长行比ISAM表有更少的碎片。  
支持大文件。  
更好的索引压缩。  
更好的键吗统计分布。  
更好和更快的auto_increment处理。 
支持全文搜索，不过它们是事务不安全的，而且也不支持外键。如果事务回滚将会造成不完全回滚，从而不具备原子性。
读锁和写锁是互斥的，从而读写操作是串行的，MyISAM表不太适合于有大量更新操作和查询操作应用的原因。因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
只有表锁定，不支持行锁定

以下是一些细节和具体实现的差别：

1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB中不保存表的 具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

      如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，同样应该使用InnoDB表。
     
       对于支持事务的InnoDB类型的表来说，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。

      查看autocommit：select@@autocommit；
       设置autocommit：set autocommit=0;

MyISAM和InnoDB存储引擎性能差别并不是很大，针对InnoDB来说，影响性能的主要是 innodb_flush_log_at_trx_commit 这个选项，如果设置为1的话，那么每次插入数据的时候都会自动提交，导致性能急剧下降，应该是跟刷新日志有关系，设置为0效率能够看到明显提升，当然，同样你可以SQL中提交“SET AUTOCOMMIT = 0”来设置达到好的性能。

 

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 wherename like “%aaa%”