数据库存储引擎的应用

存储引擎其实就是存储数据，为存储的数据建立索引，以及更新、查询数据等技术的实现方法。因为在关系数据库中数据是以表的形式存储的，所以存储引擎也可以成为表类型（即存储和操作此表的类型）。在Oracle和SQL Server等数据库中只有 一种存储引擎，所有数据存储管理机制都是一样的，而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，也可以根据自己的需要编写自己的引擎。

1、查询MySQL中支持的存储引擎

1.1、查询支持的全部存储引擎

在MySQL中，可以使用show engines语句来查询MySQL中支持的存储引擎。其查询语句如下：show engines;
show engines语句可以用“;”结束，也可以用“\g”或者“\G”结束。“\g”与“;”的作用是相同的，“\G”可以让结果显示的更加美观。
查询结果中的Engine参数指的是存储引擎的名称；support参数指的是MySQL是否支持该类引擎，Yes表示支持；Comment参数指对该引擎的评论。从查询结果中可以看出，MySQL支持多个存储引擎，其中InnoDB为默认存储引擎。

1.2、查询默认的存储引擎

如果想要知道当前MySQL服务器采用的默认存储引擎是什么，可以通过执行SHOW Variables命令来查看。在该命令中，可以使用like关键字进行模糊查询。
例1查询默认的存储引擎，具体代码如下：show variables like '%storage_engine%';
有些表根本不用来存储长期数据，实际上用户需要完全在服务器的RAM或特殊的临时文件中创建和维护这些书库，以确保性能，但这样也存在很高的不稳定风险。还有一些表只是为了简化对一组相同的表的维护和访问，为同时与所有这些表交互提供一个单一接口。另外还有其他一些特别用途的表，但重点是：MySQL支持很多类型的表，每种类型都有自己特定的作用、优点和缺点。MySQL还相应的提供了很多不同的存储引擎，可以以最适合于应用需求的方式存储数据。MySQL有多个可用的存储引擎，下面主要介绍InnoDB、MYISAM和MEMORY三种存储引擎。

2、InnoDB存储引擎

InnoDB是MySQL史上第一个提供外键约束的表引擎，而且InnoDB对事务处理的能力也是MySQL其他存储引擎所无法比拟的。下面介绍InnoDB存储引擎的特点及其优缺点。
InnoDB存储引擎中支持自动增长列AUTO_INCREMENT。自动增长列的值不能为空，且值必须唯一。MySQL中规定自增列必须为主键。在插入值时，如果自动增长列不输入值，则插入的值为自动增长后面的值；如果输入的值为0或者为null，则插入的值也为自动增长后的值；如果插入某个确定的值，且该值在前面没有出现过，则可以直接插入。
InnoDB存储引擎中支持外键（FOREIGN KEY)，外键所在的表为子表，外键所依赖的表为父表。父表中被子表外键关联的字段必须为主键。当删除、更新父表的某条信息时，子表也必须有相应的改变。InnoDB存储引擎中，创建的表结构存储在.frm文件中。数据和索引存储在innodb_data_home_dir和innodb_data_file_path表空间中。
InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对比较大。
InnoDB表是如下情况的理想引擎。

• 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求。
• 事务：InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎，这是管理敏感数据（如金融信息和用户注册信息）的必须软件。
• 自动灾难恢复：与其他存储引擎不同，InnoDB表能够自动从灾难中恢复，虽然MyISAM表也能在灾难后修复，但其过程要长的多。
  因为InnoDB可提供高效的ACID 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）兼容事务处理能力，并具有独特的高性能和可扩展性的构架要素，因此广泛应用于MySQL的web、电子商务、金融系统、健康护理以及零售应用。另外InnoDB设计用于事务处理应用，这些应用需要处理崩溃修复、参照完整性、高级别的用户并发数，以及响应时间超时等问题。

3、MYISAM存储引擎

MyISAM存储引擎是MySQL中常见的存储引擎，曾是MySQL的默认存储引擎。MyISAM存储引擎是基于ISAM存储引擎发展起来的，它解决了ISAM的很多不足，并增加了很多有用的扩展。

3.1MyISAM存储引擎的文件类型

MyISAM存储引擎的表存储成3个文件。文件的名字与表名相同，扩展名包括frm,MYD和MYI。

• frm:存储表的结构。
• MYD：存储数据，是MYData 的缩写
• MYI:存储索引，是MYIndex的缩写。

3.2、MyISAM存储引擎的存储格式

基于MyISAM存储引擎的表支持3中不同的存储格式，包括静态型、动态型、压缩型。
（1）MyISAM静态
如果所有的列的大小都是静态的（即不用xBLOB,xTEXT或VARCHAR数据类型），MySQL就会自动使用静态MyISAM格式。使用这种类型的表性能非常高，因为在维护和访问以预定义格式存储的数据时需要的开销很低。但是这个优点要以空间为代价，因为每列都需要分配给该列的最大空间，而无论该空间是否真正的被使用。
（2）MyISAM动态
如果有表列（即使只有一列）定义为动态的（使用xBLOB,xTEXT或VARCHAR），MySQL就会自动的使用动态格式。虽然MyISAM动态表占用的空间比静态格式所占空间少，但空间的节省导致了性能的下降。如果某个字段的内容发生改变，则其位置很可能就需要移动，这回导致碎片的产生，随着数据集中的碎片增加，数据访问性能就会相应降低。这个问题有下面两种修复方法。

• 尽可能使用静态数据类型。
• 经常使用OPTIMIZE TABLE语句，它会整理表的碎片，恢复由于表更新和删除而导致的空间丢失

（3）MyISAM压缩
有时我们会创建在整个应用程序生命周期中都只读的表。如果是这种情况，就可以使用myisampack工具将其转换为MyISAM压缩表来减少空间。在给定硬件配置下（例如，快速的处理器和低速的硬盘驱动器）性能的提升将相当显著。

3.3、MyISAM存储引擎的优缺点

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

4、MEMORY存储引擎

MEMORY存储引擎是MySQL中一类特殊的存储引擎，其使用存储在内存中的内容来创建表，而且所有数据也放在内存中。这些特性都与InnoDB存储引擎、MyISAM存储引擎不同。下面将对MEMORY存储引擎的文件存储形式、索引类型、存储周期和优缺点进行讲解。

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

每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm。该文件中只存储表的结构，而其数据文件都是存储在内存中。这样有利于对数据的快速处理，提高整个表的处理效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要使用了，可以释放这些内容，甚至可以删除不需要的表。

4.2、MEMORY存储引擎的索引类型

MEMORY存储引擎默认使用哈希（Hash）索引。其速度要比使用B型树（BTREE）索引快。如果希望使用B型树索引，可以在创建索引时选择使用。

4.3、MEMORY存储引擎的存储周期

MEMORY存储引擎通常很少用到。因为MEMORY表的所有数据是存储在内存上的，如果内存出现异常就会影响到数据的完整性。如果重启机器或者关机，表中的所有苏几句将消失。因此，基于MEMORY存储引擎的表生命周期都很短，一般都是一次性的。

4.4、MEMORY存储引擎的优缺点

MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中max_rows可以在创建表时指定。max_heap_table_size的大小默认为16MB，可以按需要进行扩大。因此，其存在于内存中的特性使得这类表的处理速度非常快。但是其数据容易丢失，生命周期短。
创建MySQL MEMORY存储引擎的出发点是速度。为了得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提高性能，但要记住当mysqld守护进程崩溃时，所有的MEMORY数据都会丢失。
MEMORY表不支持VARCHAR、BOLB和TEXT数据类型，因为这种表类型按固定长度的记录格式存储。此外，4.1.0版本之前的MySQL不支持自动增加列。当然， MEMORY表只用于特殊的范围，不会用于长期存储数据，基于这个缺陷，选择MEMORY存储引擎时要特别小心。
当数据有如下情况时，可以考虑使用MEMORY表。

• 暂时：目标数据只是临时需要，在其生命周期中必须立即可用。
• 相对无关：存储在MEMORY表中的数据如果突然丢失，不会对应用服务产生实质的负面影响，而且不会对数据完整性有长期影响。

5、如何选择存储引擎

没中存储引擎都有各自的优势，不能笼统的说谁比谁好，只有适合不适合。下面将给出存储引擎的建议：

• InnoDB存储引擎：用于事务处理应用程序，具有众多特性，包括ACID事务支持，支持外键。同时支持崩溃修复能力和并发控制。如果对事物的完整性要求比较高，要求实现并发控制，那选择InnoDB存储引擎有很大的优势。如果是需要频繁的进行更新、删除操作的数据库，也可以选择InnoDB存储引擎，因为该类存储引擎可以实现事务的提交（commit）和回滚（Rollback）
• MyISAM存储引擎：管理非事务表，它提供高速存储和检索，以及全文搜索能力。MyISAM存储引擎插入数据快，空间和内存使用比较低。如果表主要用于插入记录和读出记录，那么选择MyISAM存储引擎能试下能处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。
• MEMORY存储引擎：MEMORY存储引擎提供“内存中”的表，MEMORY存储引擎的所有数据都在内存中，书库的处理速度快，但安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMORY存储引擎。MEMORY存储引擎对表的大小有要求，不能见大的表。所以这类数据库只使用相对较小的数据库表。
  以上存储引擎的选择建议是根据不同存储引擎的特点提出的，不是绝对的。实际应用中还需要根据各自的情况进行分析。