MySQL架构和存储引擎＜三＞-优快云博客

本文链接：https://blog.youkuaiyun.com/Miwll/article/details/143185653

本篇接着上篇接着介绍存储引擎

传送门:MySQL架构与存储引擎<二>InnoDB和MyISAM存储引擎

MEMORY存储引擎

使⽤MEMORY存储引擎(以前称为HEAP)创建的表，内容存储在内存中。当服务器由于硬件问题、

断电或其他原因崩溃时数据会丢失，因此这些表仅⽤作临时⼯作区或从其他表中提取数据的只读缓

存。

1.使用场景

(1)涉及瞬时、⾮关键数据的操作，例如会话管理或需要缓存的数据，当服务器停⽌或重新启动时，MEMORY 表中的数据会丢失；

(2)⽤于快速访问和低延时，数据量可以完全放在物理内存中，不使⽤虚拟内存；

(3)只读或以读为主的数据访问场景(有限的更新)

2.MEMORY存储引擎的特性

(1)使⽤固定⻓度的存储格式，可变⻓度类型，例如 VARCHAR 使⽤固定⻓度存储；

(2)不能包含 BLOB 或 TEXT 列；

(3)⽀持 AUTO_INCREMENT 的列；

(4)⾮ TEMPORARY MEMORY 表在所有客⼾端之间共享；

(5)⽀持 HASH 索引(默认)和 BTREE 索引；

(6)不⽀持表分区；

(7)由于使⽤单线程，在⾼负载的场景下可能会涉及严重的锁竞争，特别是在多个客⼾端并发执⾏更新操作的情况下，性能并不⼀定会⽐ InnoDB 更快

3.创建MEMORY表

# 创建⼀个使⽤MEMORY存储引擎的表
CREATE TABLE t_memory1 (
 id int(11) PRIMARY KEY AUTO_INCREMENT,
 name varchar(20)
) ENGINE = MEMORY;

(1)由于数据在内存中保存，所以 MEMORY 表不会在磁盘上⽣成数据⽂件，表结构保存数据字典和 .sdi ⽂件中

(2)创建表并加载数据

# 从其他表中加载数据
#依据查询结果中的字段初始化表结构，在填充查询出来的数据
mysql> CREATE TABLE t_memory ENGINE=MEMORY select * from student;

(3)启动时填充 MEMORY 表的内容，可以使⽤ init_file 系统变量指定⼀个SQL⽂件，⽂件中可以编写⽤于初始化数据的SQL语句，例如： INSERT INTO ... SELECT 或 LOAD DATA

4.内存管理

(1)删除单⾏数据，不会回收内存，只有删除整个表时才会回收内存。当不需要内存表的内容时，要释放该表所使⽤的所有内存，可以执⾏ DELETE 或 TRUNCATE table 删除所有⾏，或者使⽤ DROP table 删除表。如果要释放被删除⾏所使⽤的内存，使⽤ ALTER TABLE ENGINE= MEMORY 命令强制重建表；

(2)表中⼀⾏数据所需的内存使⽤以下表达式计算：

# 涉及到所有 BTREE 索引⻓度、所有的 HASH 索引⻓度、单⾏数据⻓度

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) * 4)

+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) * 2)

+ ALIGN(length_of_row+1, sizeof(char*))

ALIGN() 函数的作⽤：使⾏⻓度为 char 类型⼤⼩的精确倍数。 sizeof (char*) 在32位

机器上是4，在64位机器上是8。

(3)max_heap_table_size 系统变量设置了内存表的最⼤⼤⼩限制，默认为16MB，要控制单个表

的最⼤⼤⼩，在创建每个表之前设置该变量的 session 值。(不要改变全局的

max_heap_table_size 值，除⾮要明确设置所有客⼾端创建的内存表)，下⾯的⽰例创建了两

个内存表，最⼤⼤⼩分别为 1MB 和 2MB

# 指定第⼀张表的内存最⼤值为1MB
mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)

# 指定第⼆张表的内存最⼤值为2MB
mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)

(4) 每次重启服务器内存表中的数据将被清空，内存表中的数据永远不会写⼊磁盘

CSV存储引擎

CSV是逗号分隔值（Comma-Separated Values)的缩写，以纯⽂本形式存储表格数据

1. 创建CSV表

(1)在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=CSV

mysql> CREATE TABLE t_csv (id INT NOT NULL, content CHAR(100) NOT NULL) ENGINE 
= CSV;
Query OK, 0 rows affected (0.06 sec)

(2)创建 CSV 表时，服务器会创建三个⽂件，其中以 .CSV 为扩展名的⽂件⽤于以逗号分隔值的格式保存数据；扩展名为 .CSM 的⽂件，⽤于存储表的状态和表中的⾏数；以 .sdi 为后缀的表信息描述⽂件(JSON格式)

2.CSV表中的数据

(1)向表中插⼊数据

mysql> INSERT INTO t_csv VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t_csv ;
+---+------------+
| id | content |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)

(2)由于 .CSV 是⽂件格式的⽂件，我们在命令⾏查看⽂件内容如下：

þroot@guangchen-vm:/var/lib/mysql/test_db# cat t_csv.CSV #查看内容
1,"record one"
2,"record two"

(3) CSV 格式可以被 Microsoft Excel 等电⼦表格应⽤程序读取和写⼊

3.CSV 表的修复和检查

(1)CSV存储引擎⽀持使⽤ CHECK TABLE 和 REPAIR TABLE 语句来验证或修复损坏的 CSV表。

(2)当⽤⽂本编辑器打开t_csv.CSV⽂件，并写⼊⼀条新数据，如下所⽰：

root@mjwl:/var/lib/mysql/test_db# vim t_csv.CSV # 编辑 CSV ⽂件

1,"record one"

2,"record two"

3,"record three" # ⼿动写⼊⼀条新数据 , 保存并退出

(3)再次执⾏查询语句发现没有第三条数据，这是由于.CSM⽂件中并没有记录新增的⾏，可以使⽤REPAIR TABLE 语句修改表内容和CSM⽂件

(4) 检查损坏的表时会返回错误，例如把t_csv.CSV⽂件的内容进⾏修改

注:在修复期时，只有从 CSV ⽂件第⼀⾏到第⼀个损坏⾏的⾏被复制到新表中，从第⼀个损坏的⾏到表末尾的所有其他⾏都会被删除，即使是有效数据

4.CSV表限制

(1)CSV 存储引擎不⽀持索引；

(2)CSV 存储引擎不⽀持分区；

(3)使⽤ CSV 存储引擎创建的表中的所有列都必须为 NOT NULL 。

ARCHIVE存储引擎

使⽤ ARCHIVE 存储引擎创建的表，存储⼤量不被索引的数据且占⽤空间很⼩，⼀般⽤于归档数据的存储

1. ARCHIVE存储引擎的特性

(1)⽀持 INSERT , REPLACE 和 SELECT ，但不⽀持 DELETE 和 UPDATE ；

(2)⽀持列的 AUTO_INCREMENT 属性，该列可以有唯⼀约束，且⼿动指定的值不能⼩于该列的最⼤值；

(3)不⽀持索引，在任何列上尝试建⽴索引都会报错；

(4)插⼊时，数据将被压缩， ARCHIVE 引擎使⽤ zlib ⽆损数据压缩； INSERT 语句只是将数据写⼊压缩缓冲区并且根据需要刷新到磁盘，当执⾏ SELECT 时会强制刷新缓冲区；

(5)检索时，按需要进⾏解压缩，不⽀持⾏缓存；

(6)SELECT操作执⾏全表扫描，找出当前查询的⾏，并读取⾏数；

(7)使⽤⾏级锁定

(8)不⽀持表分区

2.创建ARCHIVE表

在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE= ARCHIVE

# 创建⼀个使⽤ARCHIVE存储引擎的表
CREATE TABLE t_archive (
 id int(11) UNIQUE AUTO_INCREMENT,
 name varchar(20)
) ENGINE = ARCHIVE;

创建 ARCHIVE 表会根据表名⽣成两个不同后缀名⽂件，分别是以 .ARZ 为后缀的数据⽂件，以 .sdi 为后缀的表信息描述⽂件(JSON格式)， .ARN ⽂件在优化操作期间可能会出现

BLACKHOLE存储引擎

BLACKHOLE 存储引擎就像⼀个"⿊洞"，接受数据，但不存储数据，检索时总是返回⼀个空结果

1. BLACKHOLE存储引擎的特性

(1)BLACKHOLE 表不会存储任何数据，但如果启⽤了基于语句的⼆进制⽇志记录，则会记录 SQL 语句并将其复制到副本服务器

(2)⽀持索引；

(3)不⽀持分区；

2.BLACKHOLE存储引擎的⽤途

(1)验证转储⽂件语法(mysqlddump)

(2)通过⽐较启⽤和不启⽤⼆进制⽇志记录的性能，测量⼆进制⽇志记录的开销；

(3)本质上是⼀个 "⽆操作"的存储引擎，可⽤于查找与存储引擎本⾝⽆关的性能瓶颈

主要应用于调优操作的场景

3.创建BLACKHOLE表

(1)在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE= BLACKHOLE

mysql> CREATE TABLE t_blackhole(id INT, content CHAR(10)) ENGINE = BLACKHOLE;

(2)创建 BLACKHOLE 表时，服务器会在全局数据字典中创建表定义并⽣成 .sdi 为后缀的表信息描

述⽂件；

MERGE存储引擎

MERGE存储引擎，也称为MRG_MyISAM引擎，允许MySQL DBA或开发⼈员在逻辑上将⼀系列相同的MyISAM表分组，并将它们作为⼀个对象引⽤。适⽤于VLDB(Very Large Data Bases)环境，如数据仓库。这⾥的相同表⽰所有表中的列都有相同的数据类型和索引信息。

示意图如下：

因为数据是分散的，这时就需要用一个统一的逻辑入口来分发请求，请求会现在逻辑表中查询，再去检索底下的基础表中的数据，把数据归总后返回给客户端

1.创建MERGE表

(1)在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=MERGE

(2)创建MERGE表必须指定 UNION=(list-of-tables) 选项，表⽰要使⽤哪些MyISAM表；还可

以通过指定 INSERT_METHOD 选项来控制如何对MERGE表进⾏插⼊操作， FIRST 或 LAST 值分别表⽰在第⼀个或最后⼀个基础表中进⾏插⼊，如果没有指定 INSERT_METHOD 选项，或者指定它的值为 NO ，那么在 MERGE 表中执⾏插⼊将会报错；

# 创建基础表1
mysql> CREATE TABLE test_m1 (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 content CHAR(20)) ENGINE=MyISAM;
 
# 创建基础表2
mysql> CREATE TABLE test_m2 (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 content CHAR(20)) ENGINE=MyISAM;

# 向基础表中写⼊数据
mysql> INSERT INTO test_m1 (content) VALUES ('Testing1'),('table1'),
('test_m1');
mysql> INSERT INTO test_m2 (content) VALUES ('Testing2'),('table2'),
('test_m2');

# 创建MERGE表
mysql> CREATE TABLE t_merge (
 id INT NOT NULL AUTO_INCREMENT,
 content CHAR(20), INDEX(id))
 ENGINE=MERGE UNION=(test_m1,test_m2) INSERT_METHOD=LAST;

(3)创建 MERGE 表时，会在磁盘上创建⼀个 .mrg ⽂件，其中包含了基础MyISAM表的名称。MERGE的表格式存储在MySQL数据字典中； .sdi 为后缀的表信息描述⽂件；

2.操作MERGE表

(1)基础表中的 id 列作为 PRIMARY KEY 索引，但在MERGE表中并不作为主键，但是可以被索引。因为 MERGE 表不能对基础表集强制唯⼀性，类似的，基础表中具有 UNIQUE 索引的列可以在 MERGE 表中被索引，但不能作为唯⼀约束

(2)要将MERGE表重新映射到不同的MyISAM基础表集合，您可以使⽤以下⽅法之⼀:

1.删除MERGE表并重新创建；

2.使⽤ ALTER TABLE tbl_name UNION=(…) 修改基础表的集合；

ALTER TABLE…UNION=() 列表为空时，表⽰删除所有基础表

(3)使⽤ DROP TABLE 只会删除MERGE表定义，基础MyISAM表不受影响

关于MERGE表的替代⽅案可以使⽤表分区，可以⽀持主键索引，唯⼀索引，全⽂索引等

FEDERATED 存储引擎

1.默认不⽀持，可以在启动时通过命令⾏选项 --federated 或选项⽂件的配置来启⽤

2. 允许访问远程MySQL数据库中的数据，在不使⽤复制或集群技术的情况下， FEDERATED 存储引擎可以实现对远程MySQL数据库中数据的访问，以多个物理服务器为基础创建⼀个逻辑数据库，当查询 FEDERATED 表时，将会从远程数据库获取数据，⾮常适合分布式或数据集市环境

，如下图显示的为一个FEDERATED 表的示意图

3. 创建FEDERATED表

(1)本地配置⽂件中的 [mysqld] 节点下加⼊ federated=1 来启⽤ FEDERATED 引擎，之后重启

MySQL服务

# 配置⽂件路径 /etc/mysql/mysql.cnf
[mysqld]
federated=1 #加⼊配置

(2)重启本地数据库服务并查看 FEDERATED 引擎是否启⽤

(3)在远程服务器上为⽤⼾加⼊远程访问权限，安全性与权限管理专题详细介绍

(4)在MySQL 8.0中 InnoDB 是默认引擎，所以在创建表时需要指定 ENGINE=FEDERATED

(5)创建 FEDERATED 表时，本地的表定义与远程服务器的表定义相同，但数据存储在远程服务器上；

(6)本地表定义中使⽤ CONNECTION 连接字符串指向远程表的连接字符串；

# 远程服务器上的建表语句--创建基础表
CREATE TABLE t_federated (
 id INT(20) PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL DEFAULT '',
 INDEX name (name)
)
ENGINE=MyISAM 
DEFAULT CHARSET=utf8mb4;

(7)确定与远程服务器通信过程中使用的用户名和密码，这里建议创建一个专门的用户

(8) 在本地创建逻辑表

# 本地服务器上的建表语句
CREATE TABLE t_federated (
 id INT(20) PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL DEFAULT '',
 INDEX name (name)
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8mb4 
CONNECTION='mysql://federated:Fed123!!!@192.168.100.242:3306/test_db/t_federate
d'; # 指定远程服务器的连接

--CONNECTION='mysql://fed_user@remote_host:3306/test_database/test_table';

连接字符串的格式：

scheme :// user_name [: password ]@ host_name [: port_num ]/ db_name / tbl_name

scheme : 连接协议，⽬前只⽀持mysql；

user_name : ⽤于连接远程服务器的⽤⼾名，注意：这个⽤⼾在远程服务器已创建，并授予了相应

的操作权限；

password ：⽤⼾的密码；

host_name ：远程服务器的IP地址；

port_num ：远程服务器MySQL服务的端⼝号；

db_name : 远程表所在的数据库名；

tbl_name ：远程表名，本地表名与远程表名可以不同，但建议保持⼀致

(9)不会⽣成数据⽂件，表定义在数据字典中，⽣成 .sdi 为后缀的表信息描述⽂件(JSON格式)

(10)建表成功后，对本地表的增删改查和操作远程表⼀样

4.FEDERATED表注意事项

(1)远程服务器必须是MySQL服务器；

(2)使⽤ CONNECTION 字符串时，密码中不能使⽤"@"字符；

(3)DROP TABLE 只删除本地表，不删除远程表；

(4)不⽀持事务

EXAMPLE 存储引擎

(1)EXAMPLE 存储引擎什么也不做，它的存在⽬的是为开发⼈员说明如何开始编写⼀个新的存储引擎，是MySQL源代码中的⼀个⽰例。

(2)不⽀持索引和表分区

(3)当创建⼀个 EXAMPLE 表时，不会在磁盘上创建任何⽂件，表中不能存储任何数据，查询时始终返回⼀个空结果。

mysql> CREATE TABLE t_example (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)
mysql> INSERT INTO t_example VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' does not have this option
mysql> SELECT * FROM test;
Empty set (0.31 sec)