数据库存储引擎[亲测有效]

大家好，欢迎来到IT知识分享网。

        数据库存储引擎是数据库底层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据库操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

        MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表适用不同的存储引擎。MySQL5.7支持的存储引擎有：InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型。

1、InnoDB存储引擎

        InnoDB是事务型数据库的首选引擎，支持事务安全表(ACID)，支持行锁定和外检。MySQL5.5.5之后，InnoDB作为默认存储引擎，InnoDB主要特性有：

        1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表与其他MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

        2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于硬盘的关系数据库引擎所不能匹敌的。

        3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主表内存中缓存数据和索引二维持它自己的缓冲池。InnoDB将它的表和索引存在一个逻辑表空间中，表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，及时在文件尺寸被限制为2GB的操作系统上。

        4、InnoDB支持外键完整性约束(FOREIGN KEY)。存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6B的ROWID，并以此作为主键。

        5、InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录，使用InnoDB时，MySQl将在数据目录下创建一个名为ibdatal的10MB大小的自动拓展数据文件，以及两个名为ib_logfile0和ib_logfilel的5MB大小的日志文件。

2、MyISAM存储引擎

        MyISAM基于ISAM的存储引擎，并对其进行拓展。他是在Web、数据存储和其他应用环境下最长使用的存储引擎之一。MyISAM具有较高的插入、查询速度，但不支持事务。在MySQL5.5.5之前的版本中，MyISAM是默认存储引擎，MyISAM主要特性有：

        1、大文件(达63位文件长度)在支持大文件的文件系统和操作系统上被支持。

        2、当把删除、更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过和并相邻被删除的块，以及若下一个块被删除，就拓展到下一块来自动完成。

        3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16个。

        4、最大的键长度是1000B，这也可以通过编译来改变。对于键长度超过250B的情况，一个超过10123=4B的键将被用上。

        5、BLOB和TEXT列可以被索引。

        6、NULL值被允许在索引的列中。这个值站每个键的0~1字节。

        7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩。

        8、每表一个AUTO_INCREMENT列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这是的AUTO_INCREMENT列更快(至少10%)。在序列项的值被删除之后就不能再利用。

        9、可以吧数据文件和索引文件放在不同的目录。

        10、每个字符列可以有不同的字符集。

        11、有VARCHAR的表可以固定或动态记录长度。

        12、VARCHAR和CHAR列可以多达64KB。使用MyISAM引擎创建数据库，将产生三个文件。文件的名字以表的名字开始，拓展名指出文件类型：frm文件存储表定义，数据文件的拓展名为.MYD(MYData)，索引文件的拓展名是.MYT(MYIndex).

3、MEMORY存储引擎

        MEMROY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。MEMORY主要特征有：

        1、MEMORY标的每个表可以有多达32个索引，每个索引16列，以及500B的最大键长度。

        2、MEMORY存储执行HASHheBTREE索引。

        3、可以在一个MEMORY表中有非唯一键。

        4、MEMROY表使用一个固定的记录长度格式。        

        5、MEMORY不支持BLOB或TEXT列。。

        6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值得列的索引。

        7、MEMORY表在所有客户端之间共享(就像其他任何非TEMPORARY表)。

        8、MEMORY表内容被存在内存中，内存是MEMORY表和服务器在查询处理时的空闲中创建的内部表共享。       

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

4、Archive存储引擎

        从Archive单词的解释我们大概可以明白这个存储引擎的用途，这个存储引擎基本上用于数据归档；他的压缩比非常的高，村出口空间大概是InnoDB的10-15分之一，所以它用来存储历史数据非常适合，由于它不支持索引同时也不能缓存索引和数据，所以它不适合作为并发访问表的存储引擎。Archive存储引擎使用行锁来实现高并发插入操作，但是它不支持事务，其设计目标只是提供高速的插入和压缩功能。

        每个archive表在磁盘上存在两个文件：.frm(存储表定义).arz(存储数据)

        1、archive存储引擎支持insert、replace和select操作，但是不支持update和delete，但是不支持update和delete。

        2、archive存储引擎支持blob、text等大字段类型。支持auto_increment自增列同时自增列可以不是唯一索引。

        3、aechive支持auto_increment列，但是不支持往auto_increment列插入一个小于当前最大的值的值。

        4、archive不支持索引所以无法在archive表上创建主键、唯一索引和一般索引。

        存储

        往archive表插入的数据会经过压缩，archive使用zlib进行数据压缩，archive支持optimize table、check table操作。

        可以使用optimize table或repair table来获取更好的压缩。        

        一个insert 语句仅仅往压缩缓存中插入数据，插入的数据在压缩缓存中被锁定，当select操作时可能会触发压缩缓存中的数据进行刷新。

        可以使用批量插入(bulk insert)或延迟插入(delay insert)插入数据

        对于一个bulk insert操作只有当他完全执行完才能看到记录，除非在同一时刻还有其他的inserts操作，在这种情况下可以看到部分记录，select从不刷新bulk insert除非在它加载时存在一般的Insert操作。

        检索

        对于检索请求反悔的行不会压缩，且不会进行数据缓存；一个select查询会执行完整的表扫描。

        可以使用SHOW TABLES STATUS查看archive表的记录行。

5、存储引擎的选择

        不同存储引擎都有各自的特点，以使用不同的需求。为了做出选择，首先需要考虑每一个存储引擎提供了那些不同的功能。

        如果要提供提交、回滚和崩溃恢复能力的事务安全(ACID兼容)能力，并要求实现并发控制，InnoDB是个很好的选择；

        如果数据表主要用来插入和查询几率，则MyISAM引擎能提供较好的处理效率；

        如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。

        如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高高发的插入操作，但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据，如记录日志信息可以使用Archive引擎。

        使用哪一种引擎要根据需求灵活选择，一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎，将会提高整个数据库的性能。