mysql之存储引擎

小咪咪 2022-08-17 15:25 176阅读 0赞

[   上篇文章][Link 1]中多次提到“存储引擎”，什么是存储引擎呢？别急，现在就给大家揭晓！

# 存储引擎 #

MySql区别于其它数据库系统的一个重要特点是支持插入式存储引擎。存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型。每一个表都有一个存储引擎，可在创建时指定，也可之后使用ALTER TABLE语句修改，都是通过ENGINE关键字设置的；若创建时没有指定，则为默认存储引擎，默认存储引擎也可通过参数文件中default-table-type参数修改。

例如：

CREATE TABLE testtable (id int, title char(20)) ENGINE = INNODB

ALTER TABLE testtable ENGINE = MyISAM

注：在以这种方式修改表类型的时候需要非常仔细，因为对不支持同样的索引、字段类型或者表大小的一个类型进行修改可能使你丢失数据。另外，如果你指定一个在你的当前的数据库中不存在的一个存储引擎，那么就会创建一个MyISAM(默认的)类型的表。

![Center][]

解析：查询结果中，Engine参数指存储引擎名称；Support参数说明MySQL是否支持该类引擎，YES表示支持；Comment参数指对该引擎的评论；Transactions 参数表示是否支持事务处理，YES表示支持；XA参数表示是否分布式交易处理XA规范，YES表示支持；Savepoints参数表示是否支持保存点，以便事务回滚到保存点，YES表示支持。

由图可以看出，mysql支持的存储引擎包括：FEDERATED，MRG\_MYISAM，MYISAM，BLACKHOLE，CSV，MEMORY，ARCHIVE，InnoDB，PERFORMANCE\_SCHEMA。其中，InnoDB是Mysql当前默认的存储引擎。

为了大家能够更好地选择使用的存储引擎，下面介绍几种常用的存储引擎。

## MyISAM ##

MyISAM是MySQL默认的存储引擎，MyISAM支持全文索引、压缩存放、空间索引（空间函数）、表级锁、延迟更新索引键。但是MyISAM不支持事务、行级锁、更无法忍受的是崩溃后不能保证完全恢复（只能手动修复）。

MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件的名字和表的名字相同。这三个文件的扩展名分别为.frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引）。

MyISAM类型的表支持3中不同的存储格式，如下：

静态表：默认存储格式，字段长度固定，存储迅速，容易缓存，缺点是占用空间多。注意：字段存储按照宽度定义补足空格，应用访问时去掉空格，若字段本身就带有空格，也会去掉，这点特别注意。

动态表：变长字段，优点是占用空间少，但频繁的更新删除操作会产生碎片，因此，这种类型的表要经常用optimize table命令或优化工具来进行碎片整理，出现故障时难以恢复。

压缩表：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

适用的情况：

1、选择和插入密集的表。MyISAM的并发插入特性允许同时选择和插入数据，且在筛选大量数据时非常迅速。

2、对应用的完整性、并发性要求很低。

## InnoDB ##

InnoDB是一个健壮的事务型存储引擎，具有提交、回滚、崩溃恢复的事务安全。这种存储引擎已经被很多互联网公司使用，为用户操作非常大的数据存储提供了一个强大的解决方案。且在mysql5.1之后，将其设置成默认存储引擎。InnoDB还引入了行级锁定和外键约束。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

InnoDB采用MVCC（多版本并发控制）来支持高并发，实现了四个标准的隔离级别，默认级别是可重复读（repeatable read），并通过间隙锁策略防止幻读的出现。间隙锁是的InnoDB不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

适用的情况：

1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。  
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。  
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。  
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。  
5.支持自动增加列AUTO\_INCREMENT属性。

## Memory(heap) ##

Memory存储引擎的出发点是速度。所有数据置于内存中，且使用散列索引，所以拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。对该类表需谨慎使用，因为数据都保存在内存中，并没有实际写入磁盘，一旦重启，Memory表的结构还会保留，但是数据则会丢失。Memory是表即锁。

适用的情况：

1.目标数据较小，而且被非常频繁地访问。

2.某些特殊场景像查找和映射，缓存周期性的聚合数据，保存数据分析中产生的中间数据等等。

## Merge ##

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，MERGE表本身没有数据；对MERGE表查询、更新、删除操作实际上是对内部实际MyISAM表进行的。正和这个单词的中文意思“合并“对应。

适用情况：

1.常应用于日志和数据仓库。

2.将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用。

## Archive ##

Archive见名知意可看出是归档，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能，其插入速度很快，且Archive引擎支持行级锁和专用的缓冲区，可以实现高并发插入，但查询就相对较慢了，因为每次select查询都要执行全表扫描。在MySQL 5.1版以前，Archive是不支持索引，但是在MySQL 5.1以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，所以它经常被用来当做仓库使用。

适用情况：

1.常应用于日志记录或其他按时间序列实现的数据采集的方面，这类应用做数据分析时往往需要全表扫描。

## Bloakhole引擎 ##

Blackhole引擎没有实现任何的存储机制，会丢弃所有插入的数据，不做任何保存。但是服务器会记录Blackhole表的日志。

适用情况：

用于复制数据到备库，或者简单记录到日志。

## CSV ##

CSV引擎可以将普通的CSV文件（逗号分隔值的文件）作为mysql的表来处理，但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等电子表格软件中的数据存储为CSV文件，然后复制到Mysql数据目录下，就能在mysql中打开使用。同样，如果将数据写入到一个CSV引擎表，其他的外部程序也能立即从表的数据文件汇总读取CSV格式的数据。因此，CSV引擎可以作为一种数据交换的机制。

# 如何选择合适的存储引擎？ #

选择标准可以分为：  
（1）是否需要支持事务；  
（2）是否需要使用热备；  
（3）崩溃恢复：能否接受崩溃；  
（4）是否需要外键支持；

# 小结 #

其它数据库系统（例如，Oracle和SQL Server数据库）仅支持一种类型的存储引擎，所有数据存储管理机制都是一样的。遗憾的是，其它类型的数据库解决方案采取的“一个尺码满足一切需求”的方式意味着你要么就牺牲一些性能，要么你就用几个小时甚至几天的时间详细调整你的数据库。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，也就是说每一张表都可以选择适合自己的存储引擎。用户也可以根据自己的需要编写自己的存储引擎。

# 自我剖析    #

这些存储引擎小编只用到了InnoDB存储引擎，大部分都是查阅资料总结出来的。若有不足之处，还望指正！

[Link 1]: http://blog.csdn.net/shiqijiamengjie/article/details/50099479
[Center]: /images/20220731/4a4e5851ebf74eeda95d1d62f6b1d14f.png