06MySQL 的存储引擎

最新推荐文章于 2023-09-18 14:07:37 发布

great-wind

最新推荐文章于 2023-09-18 14:07:37 发布

阅读量74

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/csdn1e/article/details/108649294

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

MySQL 的存储引擎

简介
MyISAM 存储引擎
MEMORY 存储引擎
ARCHIVE 存储引擎
InnoDB 存储引擎
- InnoDB 存储引擎中的锁
- InnoDB 行锁实现

简介

MySQL 的逻辑构架，如下图所示：

在这里插入图片描述
存储引擎是指表的类型以及表在计算机上的存储方式，它位于引擎层，作用是存储和提取数据。用户可以通过使用不同的存储引擎来提高应用的效率。

在 MySQL 数据库中支持多种存储引擎，如果想要知道 MySQL 中包含哪些存储引擎，我们可以使用以下两种方式来查看 MySQL 的存储引擎信息。
方式1：

SHOW ENGINES\G;

SELECT * FROM INFORMATION_SCHEMA.ENGINES\G;

INFORMATION_SCHEMA 是 MySQL 的一个信息数据库，它保存着 MySQL 服务器所维护的所有其他数据库的信息，而 ENGINES 是这个数据库中的一张表，存放着存储引擎的信息，所以第二条命令可以查询引擎信息。
方式2：

# 使用数据库
USE INFORMATION_SCHEMA;
# 查看所有表
SHOW TABLES;
# 查看ENGINES表信息
SHOW ENGINES\G;

MyISAM 存储引擎

每个 MyISAM 都以三个文件存储在磁盘上。这些文件的名称都以表名开头，并具有扩展名以指示文件类型，其扩展名如下所示：

.frm 文件存储表的定义数据。
.MYD（MYData）存放表具体记录的数据。
.MYI（MYIndex）文件用来存储索引。

MyISAM 存储引擎的特性：

数据文件和索引文件可以放置在不同的目录下，获得更快的速度。
当按排序顺序插入行时，比如，使用 AUTO_INCREMENT 列时，索引树将被拆分，以便高级节点仅包含一个键，这样可以提高索引树的空间利用率。
AUTO_INCREMENT 支持每个表对一列数据的内部处理。MyISAM 自动更新此列的 INSERT 和 UPDATE 操作。这使得 AUTO_INCREMENT 列会更快。
当表损坏时，MyISAM 类型的表提供修复工具，可以使用 CHECK TABLE 来检查表的情况，并且可用 REPAIR TABLE 来修复。

MyISAM 的表还支持三种存储格式：

静态固定长度表：默认存储格式，存储速度快、易缓存、易修复，但是占空间。
动态可变长度表：由于记录不是固定长度，所以能够节省空间，但发生错误后，不易恢复。
压缩表：占用磁盘空间小，当数据文件发生错误时，可检查也可恢复。
MyISAM 存储引擎主要会应用在系统表或者系统临时表。

若想使用 MyISAM 存储引擎，你在创建表时，要使用 ENGINE 来设置，格式如下所示：

CREATE TABLE <表名>（<字段名> <字段类型>）ENGINE = MyISAM;

例如，我们创建一个名为 test 的数据库，在数据库中创建一张名为 t1 的数据表，同时使用 MyISAM 存储引擎。

# 创建数据库
CREATE DATABASE test;
# 使用数据库
USE test;
# 创建一张存储引擎为 MyISAM 的表
CREATE TABLE t1(id INT)ENGINE = MyISAM;
# 查看当前数据库中 t1 表的引擎类型
SHOW TABLE STATUS FROM test WHERE NAME='t1'\G;

MEMORY 存储引擎

MEMORY 存储引擎使用存在于内存中的内容来创建表。每个 MEMORY 表实际对应一个磁盘文件，其格式为 .frm。

MEMORY 存储引擎的特性：

MEMORY 支持 HASH 和 BTREE 这两种索引的数据结构，默认的是 HASH 索引。
支持的数据类型有限制，例如，不支持 TEXT 和 BLOB 类型，VARCHAR 会被自动存储为 CHAR 类型。
数据存储在内存中，一旦服务器出现故障，数据都会丢失。

MEMORY 存储引擎主要会应用在等值查找热度较高数据或者**查询结果内存中的计算。

在创建 MEMORY 表时，我们还可以指定使用的索引类型，MEMORY 存储引擎的创建格式为：

# 使用 HASH 索引的 MEMORY 表
CREATE TABLE <表名>（<字段名> <字段类型>）ENGINE = MEMORY;

# 使用 BTREE 索引的 MEMORY 表
CREATE INDEX <索引名> USING BTREE on <表名>（<字段名> <字段类型>);

例如，我们在刚才创建的名为 test 的数据库中创建一张名为 t2 的数据表，该表是一张 BTREE 索引的 MEMORY 表。

# 创建一张名为 t2 的 MEMORY 表
CREATE TABLE t2(id INT)ENGINE=MEMORY;

# 在 t2 表中创建一个名为 idx_btree 的索引
CREATE INDEX idx_btree USING BTREE ON t2(id);

# 查看索引信息
SHOW INDEX FROM t2\G;

ARCHIVE 存储引擎

ARCHIVE 存储引擎的特性：

ARCHIVE 存储引擎将产生大量未索引数据存储在一个小的专用表里。
创建 ARCHIVE 表时，服务器会在数据库目录中创建表格式文件。该文件以表名开头，并具有 .frm 扩展名。存储引擎创建其他文件，所有文件的名称均以表名开头。数据文件的扩展名为 .ARZ。在 .ARN 优化过程中，操作文件可能会出现。
ARCHIVE 存储引擎支持 INSERT，REPLACE 和 SELECT 操作，但是不支持 DELETE 或者 UPDATE 操作。它支持 ORDER BY 操作，BLOB 列以及基本上所有的数据类型，包括空间数据类型。
压缩协议进行数据存储只允许自增 id 列建立索引。

ARCHIVE 存储引擎的创建格式为：

CREATE TABLE <表名>（<字段名> <字段类型>）ENGINE = ARCHIVE;

ARCHIVE 存储引擎主要会应用在日志系统或者设备数据中。

InnoDB 存储引擎

InnoDB 是一种具有高可靠性和高性能的通用存储引擎。InnoDB 的数据存储在表空间中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。

InnoDB 存储引擎的主要特点：

InnoDB 存储引擎支持事务，它的操作遵守 ACID 原则，并具有提交、回滚和恢复的功能去保护用户的数据。
具有行级锁，提高多用户并发性。
InnoDB 表将数据存放在磁盘上，基于主键优化查询，每个 InnoDB 都有一个称为聚集索引的主键索引。
InnoDB 存储引擎支持外键约束，能够维护数据的完整性，使用外键检查 INSERET，UPDATE 和 DELATE 操作，以确保它们不会导致不同表之间的不一致。

在实际应用中，InnoDB 存储引擎是经常被使用的，使用 InnoDB 表有以下几个好处：

如果服务器因为软件或者硬件问题而崩溃，InnoDB 会自动恢复到崩溃之前已经提交的所有更改处，仅需重启并从上次中断的地方继续即可。
InnoDB 存储引擎会维护它自己的缓冲池，在主内存缓存表和索引数据作为数据被访问，这个缓存适用于多种类型的信息并且加快处理速度。
如果将数据拆分到不同的表里，设置外键去增强完整性。更新或者删除数据，会自动更新或者删除其他表里的相关数据。
创建数据库并在每张表中设置合适的主键后，这些列操作会自动进行优化。
能够压缩表和关联索引。
能够创建和删除索引，对性能的影响很小。
能够通过查询 INFORMATION-SCHEMA 表来监控存储引擎的内部工作情况。
可以通过查询 Performance Schema 表去监控存储引擎的性能详细信息。
能够将 InnoDB 表和其他存储引擎表混合使用，例如，可以使用联接操作在单个查询中合并来自 InnoDB 和 MEMORY 表中的数据。

InnoDB 在使用过程中有区别于其他存储引擎的明显特点：
InnoDB 表的自动增长列可以手动插入，但若插入的值为空，实际插入将是自动增长后的值。例如，我们创建一张名为 t3 的表，将 id 设置为自动增长列。

CREATE TABLE t3(
    id INT NOT NULL AUTO_INCREMENT,
    name CHAR(10) NOT NULL,
    PRIMARY KEY(id)
    )ENGINE=InnoDB;

INSERT INTO t3 VALUE(null,'Cici'),(2,'Lotus'),(null,'Jane');

SELECT * FROM t3;

将id列设置为自动增长列后，由上面的插入语句可以看出插入的第一个和第三个id为空值，实际情况如下：
在这里插入图片描述

在 MySQL 中只有 InnoDB 存储引擎支持外键，在创建外键时，要求父表必须有对应的索引。

InnoDB 存储引擎中的锁

InnoDB 支持事务，但事务的引入也会带来一些新的问题，比如脏读、幻读等问题，这些都是数据库读一致性问题，所以数据库提供一定的隔离机制来解决，也就是我们实验所讲到的四种事务隔离级别，通过选择不同的隔离级别来平衡“隔离”与“并发”的矛盾。

锁冲突也是影响数据库并发访问的一个重要因素，接下来会给大家介绍关于 InnoDB 存储引擎中的锁。

MySQL 有三种锁：

表级锁：开销大，加锁快，不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度低。
行级锁：开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
页面锁：开销和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

粒度是指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级别就越小，反之亦然。
InnoDB 存储引擎既支持行级锁又支持表级锁，但默认情况下采用行级锁。
InnoDB 实现了两种类型的行锁：

共享锁（S）:允许一个事务读一行数据，阻止其他事务获得相同数据集的排他锁。
排他锁（X）:允许获得排他锁的事务更新数据，阻止其他事务取得相同的数据集的共享锁和排他写锁。

关于共享锁与排他锁的理解
第一，要知道共享锁和排他锁的作用对象是数据，那么对于上述共享锁和排他锁的定义，可将共享锁的主要限制作用简单理解为读，而排他锁的主要限制作用理解为更新(或者修改)数据；
了解了第一条，理解共享锁和排他锁定义就会简单很多。共享锁：允许读取数据，事务A可以读，事务B也可以读，大家都可以读，但是不能大家正在读取数据的时候，事务C却要修改数据；排他锁：某个事务正在修改数据的时候，禁止其他事务在同一时间读取和修改数据。

为了让行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Lock），这两种锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
意向锁是 InnoDB 自动加的，不需要用户去干预。事务可以显式给记录加共享锁和排他锁，加锁方式如下：

# 给表添加共享锁
SELECT * FROM <表名> WHERE <条件> LOCK IN SHARE MODE;
# 给表添加排他锁
SELECT * FROM <表名> WHERE <条件> FOR UPDATE;

InnoDB 行锁实现

InnoDB 行锁是通过给索引上的索引项加锁来实现的，若没有索引，InnoDB 将通过隐藏的聚簇索引来对记录加锁。InnoDB 的行锁有以下三种情况：

记录锁 (Record Lock)：锁定的是索引记录。
间隙锁（Gap Lock）：对索引之间的间隙锁定，也能对第一个或最后一个索引记录之前的间隙锁定。
临键锁（Next-Key Lock）：间隙锁和记录锁的一个组合，对记录和前面的间隙加锁，用来解决数据库的幻读问题。

行锁是通过索引上的索引项来实现的，所以只有通过使用索引条件来检索数据，InnoDB 才能使用行级锁。若不通过使用索引条件来检索，那它使用的便是表级锁。

InnoDB 有以下几个锁等待的排查命令：

# 查看当前线程处理情况，对处理突发事件非常有用
SHOW FULL PROCESSLIST\G;

# 查询 InnoDB 当前锁请求的信息
SHOW ENGINE INNODB STATUS\G;

# 查看当前运行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G;

# 查看当前出现的锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G;

# 查看锁等待的对应关系
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS\G;