【MySQL精通系列之二】【存储引擎分类】

最新推荐文章于 2024-07-23 10:17:04 发布

技术自由之路

最新推荐文章于 2024-07-23 10:17:04 发布

阅读量181

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/weixin_36165763/article/details/127605276

版权

数据库专栏收录该内容

5 篇文章 1 订阅

订阅专栏

了解存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体功能。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。

MySQL区别于其他数据库的最重要的一个特点就是插件式的表存储引擎，也就是说存储引擎是基于表的。

存储引擎的概念是MySQL里面才有的，不是所有的关系型数据库都有存储引擎这个概念 。其它数据库系统 (包括大多数商业选择)仅支持一种类型的数据存储，也就是说采用“ 一个尺码满足一切需求 ”的存储方式，也意味着“功能强大，性能平庸”。而MySQL默认配置了许多不同的存储引擎，你可以根据业务需求选取一种最适配最高效的存储引擎。这也是为什么MySQL为何如此受欢迎的主要原因之一。

存储引擎分类

查看当前安装的MySQL版本支持的存储引擎

-- 查看MySQL版本
select version();

-- 查看版本支持的存储引擎
show engines;

我本地安装的社区版MySQL，版本号为5.7.23，支持9种存储引擎或者说是8（FEDERATED NO SUPPORT 不支持FEDERATED），而官网提供了10种存储引擎，大家有兴趣点此👉MySQL 5.7 Supported Storage Engines进行了解。本地与官网支持的存储引擎略微不同，个人估计是社区版和商用版的差别的缘故，或者是安装时候配置项设置导致的差异，有清楚的小伙伴还望告知一下。

官网5.7版本支持的10种存储引擎：

        MyISAM：拥有较高的插入，查询速度，但不支持事务
        InnoDB ：5.5.8版本后Mysql的默认数据库引擎，支持ACID事务，支持行级锁定
        BDB：源自Berkeley DB，事务型数据库的另一种选择，支持COMMIT和ROLLBACK等其他事务特性
        Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失
        Merge ：将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用
        Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差
        Federated：将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用
        Cluster/NDB ：高冗余的存储引擎，用多台数据机器联合提供服务以提高整体性能和安全性。适合数据量大，安全和性能要求高的应用
        CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个.CSV文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV存储引擎不支持索引。
BlackHole ：黑洞引擎，写入的任何数据都会消失，一般用于记录binlog做复制的中继

常用存储引擎特性

存储特性要求

存储引擎常见的目标特性要求

并发性：某些应用程序比其他应用程序具有更高的颗粒级锁定要求（如行级锁定）。

事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如ACID兼容等。

引用完整性：通过DDL定义的外键，服务器需要强制保持关联数据库的引用完整性。

物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。

索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如B-tree索引）对几乎所有的存储引擎来说是共同的。

内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。

性能帮助：包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。

其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。

以上特性很多是互斥的，一个存储引擎只能具备其中某些要求。参考官网

下面主要介绍InnoDB MyISAM Memory三种存储引擎，以下是三者简要特性对比

InnoDB引擎

InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定保证数据一致性提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。默认使用B+TREE数据结构存储索引。

特点

支持事务，支持4个事务隔离（ACID）级别
行级锁定（更新时锁定当前行）
读写阻塞与事务隔离级别相关
既能缓存索引又能缓存数据
支持外键
InnoDB更消耗资源，读取速度没有MyISAM快
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上

业务场景

需要支持事务的场景（银行转账之类）
适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的
数据修改较频繁的业务

InnoDB引擎调优

主键尽可能小，否则会给Secondary index带来负担
避免全表扫描，这会造成锁表
尽可能缓存所有的索引和数据，减少IO操作
避免主键更新，这会造成大量的数据移动

ACID

A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.
C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
I 独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.
D 持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.

MyISAM引擎

MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。默认使用B+TREE数据结构存储索引。

特点

不支持事务
表级锁定（更新时锁定整个表）
读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
不支持外键
读取速度快

业务场景

不需要支持事务的场景（像银行转账之类的不可行）
一般读数据的较多的业务
数据修改相对较少的业务
数据一致性要求不是很高的业务

引擎调优

设置合适索引
启用延迟写入，尽量一次大批量写入，而非频繁写入
尽量顺序insert数据，让数据写入到尾部，减少阻塞
降低并发数，高并发使用排队机制
MyISAM的count只有全表扫描比较高效，带有其它条件都需要进行实际数据访问

Memory引擎

在内存中创建表。每个MEMORY表只实际对应一个磁盘文件(frm 表结构文件)。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。要记住，在用完表格之后就删除表格，不然一直占据内存空间。

特点

支持的数据类型有限制，比如：不支持TEXT和BLOB类型（长度不固定），对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；
默认使用hash索引。
如果一个内部表很大，会转化为磁盘表。

业务场景

那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。
目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。