MySQL存储引擎(表类型)的选择

一、MySQL存储引擎概述

     MySQL与多数数据库不同的是包含存储引擎这一特性，用户可以根据应用的需要选择合适的存储引擎来使存储和索引数据，以及是否使用事务等。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。创建新表时在不指定存储引擎的情况下，系统会使用默认存储引擎，MySQL5.5之前默认的是MyISAM，5.5后改为InnoDB。如果要修改默认存储引擎，可以在参数文件中设置，Linux系统为/etc/my.cnf，Windows为MySQLServer5.7/my-default.ini，在[mysqld]后面增加default-storage-engine=INNODB即可。

二、MySQL存储引擎常规操作

        1、查询当前数据库支持的存储引擎的两种方法：

              a. mysql> SHOW ENGINES \G

              b. mysql> SHOW VARIABLES LIKE 'have%';

        2、将已存在的表修改成其他的存储引擎，如将表ai的存储引擎从MyISAM改为innodb：

              a. mysql> alter table ai engine = innodb;

三、各存储引擎相关特性（常用）

     

 

1、MyISAM

MyISAM不支持事务和外键，访问速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用该引擎创建表。每个MyISAM会在磁盘上存储成3各文件，文件名均与表名相同，但扩展名分别是：

   .frm(存储表定义)

   .MYD(MYData，存储数据);

   .MYI(MYIndex，存储索引);

且MyISAM的表还支持3种不同的存储格式，分别是：

静态(固定长度)表：默认的存储格式，非变长字段，每个记录都是固定长度，存储非常迅速，出现故障易恢复但占用空间多。静态表数据在存储时会按照列的宽度定义补足空格，但在应用访问时因在返回给用用之前已去掉，所以并不会得到这些空格。如果需要保存的内容后面本就带有空格，那么在返回的结果时也会被去掉。
动态表：变长字段，记录不是固定长度，占用空间少，但频繁地更新和删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，且出现故障不易恢复。
压缩表：由myisampack工具创建，占据磁盘空间非常小——因每个记录是被单独压缩地，所以只有非常小地开支。
MyISAM的自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面激烈进行排序后递增。例如创建一个新的MyISAM类型的表autoincre_demo，自动增长列d1作为组合索引第二列，插入记录后发现自动增长列是按组合索引的第一列d2进行排序后递增。

 

 

 

 

2、InnoDB

支持事务：具有提交、回滚和崩溃恢复能力的事务安全。相比MyISAM，写的处理效率较差，且会占用更多磁盘空间以保留数据和索引。InnoDB表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。可通过“ALTER TABLE 表名 AUTO_INCREMENT=n;”语句强制设置自动增长列的初始值，默认从1开始，但是强制的默认值是保留在内存中的，如果使用前重启数据库则该值会丢失，需要在数据库重启后重新设置。对于InnoDB，自动增长列必须是索引。如果是组合索引也必须是其第一列。MySQL中只有InnoDB支持外键，且在创建外键时，父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。

 

 

 

在创建索引时，可以指定在删除、更新父表时，对子表进行相应操作，包括：

RESTRICT、NO ACTION：两者均指限制在子表有关联记录的情况下父表不能更新。

CASCADE：父表在更新或者删除时，更新或者删除子表对应记录。

SET NULL：表示父表在更新或者删除的时候，子表对应的字段被SET NULL。

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

在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA和ALTER TABLE操作的时候，可以暂时关闭外键约束来加快处理的速度，关闭命令是

“SET FOREIGN_KEY_CHECKS = 0;" 开启的命令值为1。  

可通过SHOW CREATE TABLE 和 SHOW TABLE STATUS命令显示表外键信息。

InnoDB存储表和索引包含共享表空间存储和多表空间存储两种方式。

3、MEMORY

MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常地快，因为它地数据存放在内存中且默认使用HASH索引，但一旦服务关闭，表中的数据便会丢失。下面为创建一个MEMORY表，并从city表获得记录。

 

 

 

给MEMORY表创建索引时，可以指定使用HASH索引和BTREE索引：

 

 可通过SHOW INDEX FROM tab_memory;查看索引

当不再需要MEMORY表的内容要释放被使用的内存时，可执行DELETE FROM或TRUNCATE TABLE或整个删除表(DROP TABLE)。

4、MERGE

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE表可以进行查询、更新、删除操作，但实际是对内部的MyISAM表进行的。对于MERGE类型表的插入操作，是通过INSERT_METHO子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或定义为NO，表示不能对这个MERGE表执行插入操作。可以对MERGE表进行DROP操作，这个操作只是删除MERGE定义，对内部地表没有任何影响。下面是MERGE表的示例：

创建payment_2006、payment_2007和payment_all，其中payment_all是前两个表的MERGE表：

 

 分别向payment_2006和payment_2007表插入测试数据，并分别查询3个表中的记录：

 

 

 

 可以发现payment_all表的数据是payment_2006和payment_2007表的记录合并后的结果集。下面向MERGE表中插入一条记录，由于MERGE表的定义是INSERT_METHOD=LAST，就会向最后一个表（payment_2007）中插入记录。 

 

 

 

5、TokuDB

属于第三方存储引擎 ，高写性能高压缩率，支持事务处理的MySQL和MariaDB的存储引擎，支持大多数在线DDL操作。

TokuDB主要特性：

使用Fractal树索引保证高效的插入性能;

优秀的压缩特性，比InnoDB高近10倍;

Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作。

使用Bulk Loader达到快速加载大量数据;

提供了主从延迟消除技术：

支持ACID和MVCC。

适用场景：

日志数据：因为日志通常插入频繁且存储量大。

历史数据：通常不会再有写操作，可以利用TokuDB的高压缩性进行存储。

在线DDL较频繁的场景，使用TokuDB可以大大增加系统的可用性。

四、选择合适的存储引擎

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择该存储引擎是非常合适的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。

InnoDB：用于事务处理应用程序，支持外键。如果应用程序对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包括很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致地锁定，还可以确保事务地完整提交(Commit)和回滚(Rollback)，对于类似计费系统或者财务系统等对数据准确性要求较高地系统，InnoDB都是合适地选择。

MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速访问结果。

MERGE：用于将一系列等同地MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们，MERGE表地优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率，这对于诸如数据仓库等VLDB环境十分适合。
————————————————
版权声明：本文为CSDN博主「王哲晓」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qushaming/article/details/82773783

一、MySQL存储引擎概述
     MySQL与多数数据库不同的是包含存储引擎这一特性，用户可以根据应用的需要选择合适的存储引擎来使存储和索引数据，以及是否使用事务等。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。创建新表时在不指定存储引擎的情况下，系统会使用默认存储引擎，MySQL5.5之前默认的是MyISAM，5.5后改为InnoDB。如果要修改默认存储引擎，可以在参数文件中设置，Linux系统为/etc/my.cnf，Windows为MySQLServer5.7/my-default.ini，在[mysqld]后面增加default-storage-engine=INNODB即可。
二、MySQL存储引擎常规操作
        1、查询当前数据库支持的存储引擎的两种方法：
              a. mysql> SHOW ENGINES \G
              b. mysql> SHOW VARIABLES LIKE 'have%';
        2、将已存在的表修改成其他的存储引擎，如将表ai的存储引擎从MyISAM改为innodb：
              a. mysql> alter table ai engine = innodb;
三、各存储引擎相关特性（常用）
       
1、MyISAM
MyISAM不支持事务和外键，访问速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用该引擎创建表。每个MyISAM会在磁盘上存储成3各文件，文件名均与表名相同，但扩展名分别是：
   .frm(存储表定义)
   .MYD(MYData，存储数据);
   .MYI(MYIndex，存储索引);
且MyISAM的表还支持3种不同的存储格式，分别是：
静态(固定长度)表：默认的存储格式，非变长字段，每个记录都是固定长度，存储非常迅速，出现故障易恢复但占用空间多。静态表数据在存储时会按照列的宽度定义补足空格，但在应用访问时因在返回给用用之前已去掉，所以并不会得到这些空格。如果需要保存的内容后面本就带有空格，那么在返回的结果时也会被去掉。动态表：变长字段，记录不是固定长度，占用空间少，但频繁地更新和删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，且出现故障不易恢复。压缩表：由myisampack工具创建，占据磁盘空间非常小——因每个记录是被单独压缩地，所以只有非常小地开支。MyISAM的自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面激烈进行排序后递增。例如创建一个新的MyISAM类型的表autoincre_demo，自动增长列d1作为组合索引第二列，插入记录后发现自动增长列是按组合索引的第一列d2进行排序后递增。


 
2、InnoDB
支持事务：具有提交、回滚和崩溃恢复能力的事务安全。相比MyISAM，写的处理效率较差，且会占用更多磁盘空间以保留数据和索引。InnoDB表的自动增长列可以手工插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。可通过“ALTER TABLE 表名 AUTO_INCREMENT=n;”语句强制设置自动增长列的初始值，默认从1开始，但是强制的默认值是保留在内存中的，如果使用前重启数据库则该值会丢失，需要在数据库重启后重新设置。对于InnoDB，自动增长列必须是索引。如果是组合索引也必须是其第一列。MySQL中只有InnoDB支持外键，且在创建外键时，父表必须有对应的索引，子表在创建外键时也会自动创建对应的索引。


在创建索引时，可以指定在删除、更新父表时，对子表进行相应操作，包括：
RESTRICT、NO ACTION：两者均指限制在子表有关联记录的情况下父表不能更新。
CASCADE：父表在更新或者删除时，更新或者删除子表对应记录。
SET NULL：表示父表在更新或者删除的时候，子表对应的字段被SET NULL。
当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；同样，在执行LOAD DATA和ALTER TABLE操作的时候，可以暂时关闭外键约束来加快处理的速度，关闭命令是
“SET FOREIGN_KEY_CHECKS = 0;" 开启的命令值为1。  
可通过SHOW CREATE TABLE 和 SHOW TABLE STATUS命令显示表外键信息。
InnoDB存储表和索引包含共享表空间存储和多表空间存储两种方式。
3、MEMORY
MEMORY存储引擎使用存在与内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常地快，因为它地数据存放在内存中且默认使用HASH索引，但一旦服务关闭，表中的数据便会丢失。下面为创建一个MEMORY表，并从city表获得记录。


给MEMORY表创建索引时，可以指定使用HASH索引和BTREE索引：
 可通过SHOW INDEX FROM tab_memory;查看索引
当不再需要MEMORY表的内容要释放被使用的内存时，可执行DELETE FROM或TRUNCATE TABLE或整个删除表(DROP TABLE)。
4、MERGE
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有数据，对MERGE表可以进行查询、更新、删除操作，但实际是对内部的MyISAM表进行的。对于MERGE类型表的插入操作，是通过INSERT_METHO子句定义插入的表，可以有3个不同的值，使用FIRST或LAST值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或定义为NO，表示不能对这个MERGE表执行插入操作。可以对MERGE表进行DROP操作，这个操作只是删除MERGE定义，对内部地表没有任何影响。下面是MERGE表的示例：
创建payment_2006、payment_2007和payment_all，其中payment_all是前两个表的MERGE表：
 分别向payment_2006和payment_2007表插入测试数据，并分别查询3个表中的记录：


 可以发现payment_all表的数据是payment_2006和payment_2007表的记录合并后的结果集。下面向MERGE表中插入一条记录，由于MERGE表的定义是INSERT_METHOD=LAST，就会向最后一个表（payment_2007）中插入记录。 

 
5、TokuDB
属于第三方存储引擎 ，高写性能高压缩率，支持事务处理的MySQL和MariaDB的存储引擎，支持大多数在线DDL操作。
TokuDB主要特性：
使用Fractal树索引保证高效的插入性能;
优秀的压缩特性，比InnoDB高近10倍;
Hot Schema Changes特性支持在线创建索引和添加、删除属性列等DDL操作。
使用Bulk Loader达到快速加载大量数据;
提供了主从延迟消除技术：
支持ACID和MVCC。
适用场景：
日志数据：因为日志通常插入频繁且存储量大。
历史数据：通常不会再有写操作，可以利用TokuDB的高压缩性进行存储。
在线DDL较频繁的场景，使用TokuDB可以大大增加系统的可用性。
四、选择合适的存储引擎
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择该存储引擎是非常合适的。MyISAM是在Web、数据仓库和其他应用环境下最常使用的存储引擎之一。
InnoDB：用于事务处理应用程序，支持外键。如果应用程序对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还包括很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致地锁定，还可以确保事务地完整提交(Commit)和回滚(Rollback)，对于类似计费系统或者财务系统等对数据准确性要求较高地系统，InnoDB都是合适地选择。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速访问结果。
MERGE：用于将一系列等同地MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们，MERGE表地优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率，这对于诸如数据仓库等VLDB环境十分适合。

原文链接：https://blog.csdn.net/qushaming/article/details/82773783