MySQL存储引擎比较

今天药忘吃喽~ 2022-09-18 00:57 189阅读 0赞

MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。   
  
MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。   
  
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。   
  
MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。   
  
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。   
  
MyISAM表还支持3中不同的存储格式：   
1 静态表   
2 动态表   
3 压缩表   
静态表是默认的存储格式，静态表中的字段都是非变长的字段，优点是：存储非常迅速，容易缓存，出现故障容易恢复；缺点是：占用的空间通常比动态表多。（注意： 在存储时，列的宽度不足时，用空格补足，当时在访问的时候并不会得到这些空格）   
动态表的字段是变长的，优点是：占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期改善性能，并且出现故障的时候恢复相对比较困难。   
压缩表占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支。   
  
MySQL支持外键存储引擎只有InnoDB，在创建外键的时候，要求附表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。   
InnoDB存储方式为两种：1 使用共享表空间存储 2 使用多表空间   
  
MEMORY类型的存储引擎主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。对MEMORY存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。   
  
MERGE用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。

另一篇:-)

这段时间在看《High Performance MySQL》，看到存储引擎这个地方感到很多细节比较陌生，所以总结小记一些  
  
为了适应各种不同的运行环境，MYSQL提供了多种不同的存储引擎（Storage Engine ）,在应用程序开发这个层面上，开发者可以根据不同的需求选择适合的Storage Engine 方案，更为灵活的是，你可以根据每张表将要存储数据的特点，选择不同的Storage Engine,也就是说，在一个MYSQL数据库中，可以混合使用多种不同的Storage Engine  
  
首先小瞥一下MySQL的体系结构，在最高抽象层度下，可以用Garlan & Shaw的分层结构体系来表示（左）  
![e9ca34d9-432d-3ad2-b8d9-d13b45b74fc9.jpg][]   
其中应用层为所有RDBMS用户提供用户接口，逻辑层包括了所有核心功能的实现，物理层则负责将数据存储在硬件设备上。  
  
图中右侧更为具体的描述了逻辑层的组成，查询处理子系统、事务管理子系统、恢复管理子系统和存储管理子系统共同组成了MySQL的逻辑层。相信Storage Engine的位置是在Storage Management处，既Storage Engine属于Storage Management子系统的一部分  
  
为了让思路更清晰一些，下面给出一幅比较全面的体系结构图（或更确切的说是流程图，只是忽略了反馈）  
![e3795bbd-c9d8-3c04-bc0e-83db0bfb8322.jpg][]   
上面三幅图来自于一篇非官方（不保证百分百的正确）的MySQL体系结构的报告，与《High Performance MySQL》一书中给出的MySQL大体结构（下图,基本对应于Logic Layer，从第一幅图右侧可以看出MySQL logic layer同样遵从分层体系结构）还是比较吻合的。  
![a8a8105e-5ba7-3c4d-9bc3-6716c7bb9c3c.jpg][]   
连接上图中第二层和第三层之间的接口是并不针对任何存储引擎的单一API,.大概由20个基本的类似“启动事务，返回结果集”等函数组成。存储引擎并不处理SQL，相互之间也不通信，它们的任务只是简单的响应高层传来的请求。  
  
**存储引擎各自的一些特点 **  
  
上面提到的四种存储引擎都有各自适用的环境，这取决于它们独有的一些特征。主要体现在性能、事务、并发控制、参照完整性、缓存、 故障恢复，备份及回存等几个方面  
  
  
目前比较普及的存储引擎是MyISAM和InnoDB.而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。  
  
MyISAM是早期ISAM(Indexed Sequential Access Method,我现在用的MySQL5.0已经不支持ISAM了）的扩展实现，ISAM被设计为适合处理读频率远大于写频率这样一种情况，因此ISAM以及后来的MyISAM都没有考虑对事物的支持，排除了TPM，不需要事务记录，ISAM的查询效率相当可观，而且内存占用很少。MyISAM在继承了这类优点的同时，与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制，提供了表级锁，虽然MyISAM本身不支持容错，但可以通过myisamchk进行故障恢复。而且由于MyISAM是每张表使用各自独立的存储文件（MYD数据文件和MYI索引文件），使得备份及恢复十分方便（拷贝覆盖即可），而且还支持在线恢复。  
  
所以如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择  
  
InnoDB被设计成适用于高并发读写的情况.使用MVCC(Multi-Version Concurrency Control)以及行级锁来提供遵从ACID的事务支持。InnoDB支持外键参照完整性，具备故障恢复能力。另外 InnoDB的性能其实还是不错的，特别是在处理大数据量的情况下,用官方的话说就是： InnoDB的CPU效率是其他基于磁盘的关系数据库存储引擎所不能比的。不过InnoDB的备份恢复要麻烦一点，除非你使用了4.1以后版本提供的Mulit-tablespace支持，因为InnoDB和MyISAM不同，他的数据文件并不是独立对应于每张表的。而是使用的共享表空间，简单的拷贝覆盖方法对他不适用，必须在停掉MYSQL后对进行数据恢复。使用Per-Table Tablespacesd，使其每张表对应一个独立的表空间文件，则情况要简单很多。  
  
一般来说，如果需要事务支持，并且有较高的并发读写频率，InnoDB是不错的选择。要是并发读写频率不高的话，其实可以考虑BDB,但由于在MySQL5.1及其以后版本中，将不再提供BDB支持。这个选项也就没有了  
  
至于Heap和BDB（Berkeley DB），相对来说，普及率不如前两种，但在有些情况下，还是挺适用的  
  
Heap存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。  
   
Heap挺适合做测试的时候使用  
  
  
BDB是MySQL第一款事务安全的存储引擎。在Berkeley DB database library的基础上建立，同样是事务安全的，但BDB的普及率显然不及InnoDB，因为大多数在MySQL中寻找支持事务的存储引擎的同时也在找支持MVCC或是行级锁定存储引擎，而BDB只支持Page-level Lock。

附上一张《High Performance MySQL》 中的各存储引擎的特性表

Attribute

MyISAM

Heap

BDB

InnoDB

<table style="color:rgb(0,0,0); font-size:14px; line-height:25.1875px"> 
 <tbody> 
 <tr></tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Transactions </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Lock granularity </td> 
 <td style="font-size:1em; padding:5px"> Table </td> 
 <td style="font-size:1em; padding:5px"> Table </td> 
 <td style="font-size:1em; padding:5px"> Page (8 KB) </td> 
 <td style="font-size:1em; padding:5px"> Row </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Storage </td> 
 <td style="font-size:1em; padding:5px"> Split files </td> 
 <td style="font-size:1em; padding:5px"> In-memory </td> 
 <td style="font-size:1em; padding:5px"> Single file per table </td> 
 <td style="font-size:1em; padding:5px"> Tablespace(s) </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Isolation levels </td> 
 <td style="font-size:1em; padding:5px"> None </td> 
 <td style="font-size:1em; padding:5px"> None </td> 
 <td style="font-size:1em; padding:5px"> Read committed </td> 
 <td style="font-size:1em; padding:5px"> All </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Portable format </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 <td style="font-size:1em; padding:5px"> N/A </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Referential integrity </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Primary key with data </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> MySQL&nbsp;caches data records </td> 
 <td style="font-size:1em; padding:5px"> No </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 <td style="font-size:1em; padding:5px"> Yes </td> 
 </tr> 
 <tr> 
 <td style="font-size:1em; padding:5px"> Availability </td> 
 <td style="font-size:1em; padding:5px"> All versions </td> 
 <td style="font-size:1em; padding:5px"> All versions </td> 
 <td style="font-size:1em; padding:5px"> MySQL-Max </td> 
 <td style="font-size:1em; padding:5px"> All Versions </td> 
 </tr> 
 </tbody> 
</table>

[e9ca34d9-432d-3ad2-b8d9-d13b45b74fc9.jpg]: /images/20220828/406fcee9665b455b86b6cadd4317b236.png
[e3795bbd-c9d8-3c04-bc0e-83db0bfb8322.jpg]: /images/20220828/5c3b4d282a114ed2b7e8b37fdad3a416.png
[a8a8105e-5ba7-3c4d-9bc3-6716c7bb9c3c.jpg]: /images/20220828/2c52fab6dd0146f78880af14e713c547.png