MySQL存储引擎（一）---MyISAM、InnoDB

MySQL存储引擎概述

和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。

插件是存储引擎是MySQL数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。

MySQL5.0支持的存储引擎包括：MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

在数据库中查看当前的默认存储引擎，可以使用以下命令：

show variables like 'table_type';

查询当前数据库支持的存储引擎可用以下两种方法，方法一为：

SHOW ENGINES \G

第二种方法为：

SHOW VARIABLES LIKE 'have%';

MyISAM存储引擎

MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都是可用使用这个引擎来创建表。

每个MyISAM在磁盘上存储成三个文件，其文件名都和表名相同，但拓展名分别是：

.frm（存储表定义）

MYD（MYData，存储数据）

MYI（MYIndex，存储索引）

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

要指定索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，也就是说不同MyISAM表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。

MyISAM类型的表可能会损坏，原因是多种多样的，损坏后的表可能不能被访问，会提示需要修复或者访问后返回错误的结果。MyISAM类型的表提供修复的工具，可以用CHECK TABLE 语句来检查MyISAM表的健康，并用REPAIR TABLE 语句修复一个损坏的MyISAM表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。

MyISAM的表祸害支持三种不同的存储格式，分别是：

1.静态（固定长度）表；

2.动态表；

3.压缩表；

其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方法的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在访问时不会得到这些空格，这些空格在返回给应用之前就已经去掉。

而别需要注意的是，如果需要保存的内容后面本来就带有空格，那么在返回时，后面的空格就会被去掉。但是如果在前面是空格的话，就不会被去掉。

动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但频繁地更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE 语句会myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。

压缩表有myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

InnoDB存储引擎

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同与其他存储引擎的特点：

1.自动增长列

InnoDB表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值

mysql> create table autoincre_demo
    -> (i smallint not null auto_increment,
    -> name varchar(10),primary key(i)
    -> )engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 3 | 3    |
+---+------+
3 rows in set (0.00 sec)

可以通过“ALTER TABLE *** AUTO_INCREMENT=n;” 语句强制设置自动增长列的初始值，默认从1开始，但是该强制的默认值是保存在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要在数据库启动之后重新设置。

可以使用LAST_INSERT_ID()查询当前线程最后插入的记录使用的值。如果一次插入了多条记录，那么返回的是第一条记录的自动增长值。

对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录之后，自动增长列是按照组合索引的前几列进行排序后递增的。

2.外键约束

MySQL支持外键约束的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL和NO ACTION 。其中 RESTRICT 和NO ACTION 相同，是指限制在子表有关联的情况下父表不能更新；CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。选择后两者的时候要谨慎，可能会因为错误的操作导致数据的流失。

当某个表被其他表创建了外键参照，那么该表的对饮索引或者主键禁止被删除。

在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA 和 ALTER TABLE操作时，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是 “SET FOREIGN_KEY_CHECKS = 0;”，执行完成之后，通过执行“SET FOREIGN_KEY_CHECKS = 1;” 语句改回原状态。

对于InnoDB类型的表，外键的信息通过使用show create table 或者 show table status 命令都可以显示。

3.存储方式

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

1、使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。

2、使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm文件中，但是每个表的数据和索引单独保存在.idb中。如果是个分区表，则每个分区对应单独的.idb文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的IO均匀分布在多个磁盘上。

要使用多表空间的存储方式，需要设置参数 innodb_file_per_table，并重新启动服务后才可以生效，对于新建的表按照多表空间的方式创建，已有的表仍然使用共享表空间存储。如果将已有的多表空间方式修改回共享表空间的方式，则新建的表会在共享表空间中创建，但已有的多表空间的表仍然保存原来的访问方式。

多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、拓展大小等参数。