MySQL常用存储引擎

写完就吃饭
180 阅读12分钟

存储引擎介绍

MySQL支持多种存储引擎，在MySQL中存储引擎决定了数据的存储形式和对数据对管理方式。存储引擎位于MySQL架构的第三层，对数据的存或者取都是由它所决定的，因此它也是MySQL的核心。

2022-10-08-20-17-26-image.png

在MySQL中可以在客户端使用SHOW ENGINES;来查看当前版本所支持的存储引擎，在我电脑的MySQL版本mysql Ver 14.14 Distrib 5.7.35, for Win64 (x86_64)上支持以下的存储引擎。

mysql> show ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

虽然MySQL支持这么多存储引擎，但是开发过程中常用的就InnoDB、MyISAM这两种比较多，其它的都是要在特殊场景下才会使用，比如我们曾经用过MRG_MYISAM也用过MEMORY。

  • InnoDB：现在最常用的存储引擎，平衡了读、写之间的操作，支持事务操作，支持行级的锁，目前是业务系统中最常用的存储引擎，索引采用聚簇索引结构存储。
  • MyISAM：第二常用的存储引擎，在MySQL5.5以前默认的存储引擎，5.5之后默认为InnoDB，不支持事务，表级锁插入和查询速度很快，但是编辑操作性能不高，索引采用非聚簇索引存储。
  • MRG_MYISAM：一种分区表的存储引擎看名字可以知道，它的存储结构是MYISAM引擎的结构，它将多个MyISAM聚合起来形成一个主表，分区表每个表按照一定规则存储相应数据，在使用宽表这类型的大单表中有着比较好的运用。
  • MEMORY：内存存储引擎，数据都存储在内存中，结构存储在磁盘上，拥有极高的插入，更新，查询效率，但是内存占用也大，当服务重启数据就会丢失掉，结构还在。
  • Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差。
  • Federated ：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用，默认只是安装了该存储引擎并没有开启，可以在配置文件中开启该存储引擎。
  • CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。这种类型的存储引擎可以很方便的与其它应用做数据传递，因为很多其它数据库和数据处理框架都支持这种类型的数据。

MySQL还有个牛逼的地方就是一个数据库可以使用多种存储引擎的表，极大的满足了在同一个库中，多种用途的表共存，减少了程序之间对数据管理的复杂度。只需要在建表的时候指定存储引擎即可。

CREATE TABLE tb_xxxx ( ...) ENGINE=InnoDB;

比如同一个业务系统，主表数据为InnoDB，业务操作过程中的日志等可以存储在MRG_MYISAM引擎表中，有效的分割了日志表的大小，并且由于其底层采用MyISAM疫情，检索效率也较为可观。

存储引擎的常用操作：

以下内容来自： zhuanlan.zhihu.com/p/524858210

-- 查询当前数据库支持的存储引擎：
show engines;

-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;

-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 
show create table student; 

-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;

-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

-- 修改MySQL默认存储引擎方法
1. 关闭mysql服务 
2. 找到mysql安装目录下的my.ini文件： 
3. 找到default-storage-engine=INNODB 改为目标引擎，
   如：default-storage-engine=MYISAM 
4. 启动mysql服务

常用存储引擎

InnoDB

InnoDB作为现在首选的存储引擎，它综合表现都比较出色，支持事务（ACID）、锁、外键、索引等。

被设计用来处理大量的短期（ short-lived ）事务场景。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash 索引以加速读操作的自适应哈希索引（     adaptive hash index ），以及能够加速插入操作的插入缓冲区（ insert buffer ）等。

InnoDB 采用MVCC 来支持高并发，井且实现了四个标准的隔离级别。其默认级别是REPEATABLE 旺AD （可重复读），井且通过间隙锁（ ext-key locking ）策略防止幻读的出现。间隙锁使得InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

事务

作为唯一一个支持事务的存储引擎，InnoDB在这一块下了狠功夫。

InnoDB使用undo log日志实现了日志回滚实现了事务的原子性；

InnoDB使用redo log实现了持久性提高了写的性能和容灾等问题；

利用undo log+redolog实现了一致性；

使用锁+MVCC实现了隔离性，也就是默认的隔离级别可重复读。

锁

InnoDB支持多种锁，其中包括行锁、表锁、间隙锁、临键锁、共享锁、意向锁等。并且InnoDB的锁依赖于索引实现的。

  • 行锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
  • 间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
  • 临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。
  • 共享锁支持多事务读，在MVCC里面也叫当前读，在SQL后面加 LOCK IN SHARE MODE;。
  • 意向锁是一种为了提高锁判断性能设计的锁，为了辅助表级和行级锁的判断冲突设计的，当对某一行加锁后，如果没有意向锁，下一个事务要判断锁冲突就得便利所有行，意向锁表示了该表存在行锁，减少了遍历行的步骤。也是因为InnoDB支持行锁，因此也支持意向锁。

索引：

InnoDB采用聚簇索引，什么是聚簇索引？聚簇索引就是将索引和数据存储在同一个文件中就称为聚簇索引，它与之对应的是非聚簇索引是MyISAM存储引擎的索引存储结构。

聚簇索引对主键查询有很高的性能。

MyISAM

MyISAM作为曾经的王者也是非常强悍的，虽然不支持事务，但是在批量插入和查询方面也表现出了强悍的性能，但是在大量短期事务的场景不是很适合因为它不支持行级锁，只支持表锁。

存储方面索引采用的非聚簇索引，会产生三个文件.frm(表定义)，.myd(数据文件)，.myi(索引文件)。

加锁与并发

MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为井发插入， CONCURRENT INSERT ）。

MRG_MYISAM

MRG_MyISAM是一种基于MyISAM存储引擎聚合的存储引擎，该存储引擎具有聚合多个MyISAM引擎表的功能。

示例：

创建子表：

CREATE TABLE `ts_teacher_1`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `t_birth` date NOT NULL COMMENT '出生年月',
  `t_sex` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `t_phone` bigint(20) NOT NULL COMMENT '手机号',
  `t_height` int(11) NOT NULL COMMENT '身高',
  PRIMARY KEY (`t_id`) USING BTREE,
  UNIQUE INDEX `uk_phone`(`t_phone`) USING BTREE,
  INDEX `idx_name`(`t_name`) USING BTREE,
  INDEX `idx_birth`(`t_birth`) USING BTREE
) ENGINE=MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表_MyISAM_1' ROW_FORMAT = Dynamic; 


CREATE TABLE `ts_teacher_2`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `t_birth` date NOT NULL COMMENT '出生年月',
  `t_sex` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `t_phone` bigint(20) NOT NULL COMMENT '手机号',
  `t_height` int(11) NOT NULL COMMENT '身高',
  PRIMARY KEY (`t_id`) USING BTREE,
  UNIQUE INDEX `uk_phone`(`t_phone`) USING BTREE,
  INDEX `idx_name`(`t_name`) USING BTREE,
  INDEX `idx_birth`(`t_birth`) USING BTREE
) ENGINE=MyISAM CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表_MyISAM_2' ROW_FORMAT = Dynamic;

分别对子表插入数据：

insert into ts_teacher_1 select * from tb_teacher where t_id between 1 and 500000;
insert into ts_teacher_1 select * from tb_teacher where t_id between 500000 and 1000000;

创建MRG_MyISAM聚合表：

CREATE TABLE `ts_teacher`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `t_birth` date NOT NULL COMMENT '出生年月',
  `t_sex` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `t_phone` bigint(20) NOT NULL COMMENT '手机号',
  `t_height` int(11) NOT NULL COMMENT '身高',
  PRIMARY KEY (`t_id`) USING BTREE,
  UNIQUE INDEX `uk_phone`(`t_phone`) USING BTREE,
  INDEX `idx_name`(`t_name`) USING BTREE,
  INDEX `idx_birth`(`t_birth`) USING BTREE
) ENGINE=MRG_MYISAM UNION = (`ts_teacher_1`,`ts_teacher_2`) INSERT_METHOD=LAST CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表主表' ROW_FORMAT = Dynamic;

此时查询ts_teacher主表

select count(*) from ts_teacher;
-- 结果有1000000条数据

实际ts_teacher表中并没有数据，它只是对ts_teacher_1和ts_theacher_2做了一个聚合操作 UNION。

这样做得好处：对分表统一出口和入口，为查询提供了方便，并且插入的时候可以指定插入方式，让其插入到FIRST获LAST。

我们会将日志按照日期间隔生成子表，随后要执行日志分析统计任务的时候，只需要动态创建一个主表即可完成操作，在做日志插入和查询的时候也是只需要修改主表聚合范围，这样不用修改原有的代码基础上就可以实现水平分表。

插入：定时生成新的日志表，改变其ts_log_main主表的聚合设置，将新日志表加入聚合范围中，这样插入的完全不用修改任何表名，代码等。

统计：如ts_log_220601，ts_log_220602,ts_log_220603.....。现在需求是定时要对近三天日志进行一系列的sql分析和统计等操作，创建一个ts_log_stat的MG_MyISAM表并设置不允许插入INSERT_METHOD=NO，聚合01,02,03这三个表，随后针对主表执行主表sql逻辑分析即可。

MEMORY

内存存储引擎，该种存储引擎磁盘只存储了表结构，并不存储表数据，这种存储引擎可以理解为支持sql的内存数据库，用来做一些临时表特别适合。比如我们可以用这种存储引擎来作为缓存使用，Redis、ES这些都是非结构型数据，不能很好的做统计聚合等操作，而使用这种存储引擎在保证了速度的同时，还能够使用avg、sum等函数使用。并且如果该缓存后面要做持久化，只需要将表改为其它支持持久化的存储引擎即可，完全不必修改代码。

创建MEMORY存储引擎表：

CREATE TABLE `ts_teacher`  (
  `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `t_birth` date NOT NULL COMMENT '出生年月',
  `t_sex` enum('male','female') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '性别',
  `t_phone` bigint(20) NOT NULL COMMENT '手机号',
  `t_height` int(11) NOT NULL COMMENT '身高',
  PRIMARY KEY (`t_id`) USING BTREE,
  UNIQUE INDEX `uk_phone`(`t_phone`) USING BTREE,
  INDEX `idx_name`(`t_name`) USING BTREE,
  INDEX `idx_birth`(`t_birth`) USING BTREE
) ENGINE=MEMORY  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '教师信息表主表' ROW_FORMAT = Dynamic;

插入数据

insert into ts_teacher select * from tb_teacher ;

总结：

MySQL提供了多种存储引擎，每种存储引擎之间的区别在于数据的存储结构，存储介质，对数据的管理形式等。

常用的有InnoDB，MyISAM这两个是我们在工作中业务系统中使用的最多的，其它还有些想MRG_MyISAM，Memory等，没有最好的存储引擎只有根据不同的业务场景选择适合业务场景的存储引擎。

InnodDB提供了支持事务，支持行锁、表锁、间隙锁、意向锁等，索引采用聚簇索引即数据和索引存储在同一个文件中，这是在5.1之后默认的存储引擎。

MyISAM在5.1之前默认的存储疫情，不支持事务，支持表锁，索引采用非聚簇索引存储，由三个文件存储数据，分别是frm、myd、myi。该存储引擎适用于查询较多的场景，它的查询效率高于InnoDB。

MRG_MyISAM是基于MyISAM表聚合的存储引擎，使用该存储引擎表自身不存储数据，它主要用于聚合MyISAM表，常用于动态宽表、分区表合并等场景，并且它也支持插入，更新等操作，因此可以用这种存储引擎创建分区表代理，提供统一入口这些操作。

MEMORY是将数据存储在内容的存储引擎，该存储引擎用于做临时表，缓存表有着很好的能力，并且基于该存储引擎可以对缓存数据用SQL操作，在业务场景变更，需要持久化的时候可以做到完全不修改代码，修改表的存储引擎将缓存表作为持久化表。

avatar
文章
阅读
粉丝