mysql数据库存储引擎的比较——＜一＞

最新推荐文章于 2024-11-15 14:04:07 发布

原创最新推荐文章于 2024-11-15 14:04:07 发布 · 252 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

数据库专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了MySQL中的四种常见存储引擎：InnoDB、MyISAM、MERGE和BLACKHOLE的特点及适用场景。InnoDB支持事务处理，适用于需要高完整性的应用如银行业；MyISAM插入查询速度快，适用于以读为主的场景；MERGE引擎可以合并多个MyISAM表的数据；BLACKHOLE引擎用于日志记录或同步归档。

一、常用命令

1.1 查看当前默认存储引擎

show VARIABLES like "%storage_engine%";

1.2 查看所有存储引擎

SHOW engines;

在这里插入图片描述
1.3 临时修改存储引擎

SET default_storage_engine=MEMORY;

show ENGINES;

在这里插入图片描述

二、介绍理解

2.1 InnoDB存储引擎

InnoDB是事务型数据库的首选引擎，是目前MYSQL的默认事务型引擎，是目前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行锁定和外键。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支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，
   如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件， 
以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。
   如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。
   如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

2.2 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。
它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
MyISAM拥有较高的插入、查询速度，但不支持事物和外键。

存储格式:
1、静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。
   存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。
2、动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，
   需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。
3、压缩表：使用myisampack工具创建，占用非常小的磁盘空间。
   因为每个记录是被单独压缩的，所以只有非常小的访问开支。
静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。
如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）
使用MyISAM引擎创建数据库，将产生3个文件。
文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

2.3 MERGE存储引擎

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，
尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；
Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。
例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。
	当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。
	与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，
	删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

2.4 BLACKHOLE存储引擎（黑洞引擎）

该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，
主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

场景：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，
执行binlogdump命令而且多个此类进程并不是共享的。
为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。

其他存储引擎
参考:https://blog.csdn.net/yjclsx/article/details/81911027
参考:http://c.biancheng.net/view/2418.html