《MySQL中级系列教程-第四章-存储引擎详解》

末蓝、 2023-01-02 12:21 171阅读 0赞

# 《MySQL中级系列教程-第四章-存储引擎详解》 #

# 一、什么是MySQL存储引擎 #

## 1.1 存储引擎概述 ##

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

Oracle，SQL Server等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

> **简单来说，MySQL提供了可插板式的存储引擎，你需要什么功能，就切换什么存储引擎。我们接下来就是要了解不同的存储引擎到底提供了什么功能。**

## 1.2 查看存储引擎 ##

*  查看当前数据库服务器支持哪些存储引擎：

show engines;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]

*  查看当前数据库默认使用什么存储引擎：

show variables like '%storage_engine%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]

## 1.3 修改默认存储引擎 ##

可以看到MySQL默认使用的存储引擎是InnoDB，如果想要修改MySQL的存储引擎可以有如下操作

*  1）创建表的时候指定存储引擎

CREATE TABLE `demo1` (
    	`id` int(11) NOT NULL AUTO_INCREMENT,
    	`name` varchar(20) DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM default CHARSET=utf8;			-- 使用Myisam存储引擎
    
    
    CREATE TABLE `demo2` (
    	`id` int(11) NOT NULL AUTO_INCREMENT,
    	`name` varchar(20) DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;		-- 使用InnoDB

*  2）基于已经创建好的表修改存储引擎

mysql> alter table demo2 engine=myisam;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql>

> 这里需要注意一点：**存储引擎是基于表的**，一个数据库下可以有很多不同存储引擎的表。

## 1.4 常见存储引擎 ##

<table> 
 <thead> 
  <tr> 
   <th>特点</th> 
   <th>InnoDB</th> 
   <th>MyISAM</th> 
   <th>MEMORY</th> 
   <th>MERGE</th> 
   <th>NDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储限制</td> 
   <td>64TB</td> 
   <td>有</td> 
   <td>有</td> 
   <td>没有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>事务安全</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>锁机制</td> 
   <td>行锁、表锁</td> 
   <td>表锁</td> 
   <td>表锁</td> 
   <td>表锁</td> 
   <td>行锁</td> 
  </tr> 
  <tr> 
   <td>B树索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>哈希索引</td> 
   <td></td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持(5.6版本之后)</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>集群索引</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>数据索引</td> 
   <td>支持</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>索引缓存</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>数据可压缩</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>空间使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>N/A</td> 
   <td>低</td> 
   <td>低</td> 
  </tr> 
  <tr> 
   <td>内存使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>中等</td> 
   <td>低</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>批量插入速度</td> 
   <td>低</td> 
   <td>高</td> 
   <td>高</td> 
   <td>高</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

> 我们发现，在众多存储引擎中，只有InnoDB支持事务

# 二、 存储引擎的特点 #

## 2.1 InnoDB 存储引擎 ##

**InnoDB存储引擎是MySQL的默认存储引擎**。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。并且会占用更多的磁盘空间以保留数据和索引。

*  InnoDB 存储引擎特点如下：
    
     *  **事务**：支持事务的ACID
     *  **外键**：支持外键
     *  **锁粒度**：行锁（表锁）
     *  **存储形式为**：.frm 表定义文件 .ibd 数据文件

> 这里说明一下，InnoDB在没有使用到索引的情况下也会触发表级锁。

## 2.2 MyISAM 存储引擎 ##

在MySQL5.1及之前的版本，MySIAM是默认的存储引擎。MyISAM是基于ISAM引擎发展起来的，增加了许多有用的扩展。**MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和修改并发**。

*  MyISAM存储引擎特点如下：
    
     *  **事务**：不支持事务
     *  **外键**：不支持外键
     *  **锁粒度**：表锁
     *  **存储形式为**：.frm 表定义文件 .myd 数据文件 .myi 索引文件

### 2.2.1 MyISAM表的存储格式 ###

MySIAM表支持三种不同存储格式，分别为静态表、动态表、压缩表。

*  **静态表**：**静态表是默认的存储格式**。表中的字段都是定长的（非变长）。
    
     *  优点：存储非常迅速，容易缓存，服务器崩溃数据易恢复。
     *  缺点：占用的空间通常比动态表多。
 *  **动态表**：动态表的字段是变长的
    
     *  优点：占用的空间相对较少。
     *  缺点：频繁地更新删除记录会产生碎片，需要定期使用`optimize table`语句优化表，整理碎片，服务器崩溃数据难恢复。
 *  **压缩表**：MyISAM可以使用`myisampack`工具压缩表数据，压缩表占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支。

**说了这么多类型的表，那么怎么样创建静态表、动态表、以及压缩表呢？**

**其实静态表和动态表不用我们创建**，当表不包含**变长列**（VARCHAR, BLOB, 或TEXT）时，该表就是静态表，反之如果一个MyISAM表包含任何**可变长度列**（VARCHAR, BLOB或TEXT）那么此表就是动态表。

*  总结：
    
     *  当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时为静态表
     *  MyISAM表包含任何可变长度 列（VARCHAR, BLOB或TEXTDynamic）时为动态表

**当然了，还有一种情况，如果表中都没有变长列，我依旧想使用动态表呢（毕竟占用空间少嘛）？**

**答：你可以在创建表的时候就指定这张表为动态表**

CREATE TABLE `demo3` (
    	`id` int(11) NOT NULL AUTO_INCREMENT,
    	`name` char(20) DEFAULT NULL,
    	PRIMARY KEY (`id`)
    ) ENGINE=MyISAM default CHARSET=utf8 ROW_FORMAT=DYNAMIC;

> ROW\_FORMAT=DYNAMIC：指定该表为动态表。

**你刚刚说完了静态表和动态表，那么压缩表呢？**

MySQL官方对压缩表的定义就是使用过`myisampack`命令进行压缩的表，就是压缩表，简单吧？那么`myisampack`命令又是什么呢？

*  创建一个测试表：

CREATE TABLE `userinfo` (
      `id` int(10) NOT NULL COMMENT '用户id',
      `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
      `age` int(3) NULL DEFAULT NULL COMMENT '年龄',
      `phone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '手机号',
      `gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别: ‘0’-男 ‘1’-女',
      `desc` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '自我介绍',
      `register_time` datetime(0) NULL DEFAULT NULL COMMENT '注册时间',
      `login_time` datetime(0) NULL DEFAULT NULL COMMENT '上一次登录时间',
      `pic` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像地址',
      `look` int(10) NULL DEFAULT NULL COMMENT '查看数',
      PRIMARY KEY (`id`)
    ) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

*  创建一个存储过程脚本，用于批量插入数据

CREATE PROCEDURE `test_test`(count int)
    begin
    		declare i int default 1;
    		while i<=count do 
    			INSERT INTO userinfo values(
    			i,
    			uuid(),
    			CEILING(RAND()*90+10),
    			FLOOR(RAND()*100000000000),
    			round(FORMAT(rand(),1)),
    			uuid(),
    			now(),
    			now(),
    			uuid(),
    			CEILING(RAND()*90+10)
    			);
    			set i=i+1;
    		end while;
    end

调用存储过程，在表中插入10W数据：

call test_insert(100000);

![在这里插入图片描述][20200603112820400.png]  
我们去查看表的物理文件，看下占用磁盘空间是多少：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]

> 数据占用了15M，索引占用了1004KB

*  我们使用`myisampack`工具，对表进行压缩：

myisampack userinfo

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]

> 使用`myisampack`工具压缩过后，发现数据文件占用6.4M，索引文件占用1KB。此时这个表就是压缩表了。

关于压缩工具的使用，参考这篇文章：[MyISAM引擎压缩工具myisampack][MyISAM_myisampack]

> 说明一下：MySQL官方提出使用`myisampack`压缩过后的数据为只读模式，不可修改。如果想要修改，请使用`myisamchk`工具先把数据解压。

关于更多MyISAM引擎详细资料，请阅读MySQL官方文档：[https://dev.mysql.com/doc/refman/5.7/en/myisam-table-formats.html][https_dev.mysql.com_doc_refman_5.7_en_myisam-table-formats.html]

## 2.3 MyISAM与InnoDB对比 ##

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th><strong>Innodb</strong></th> 
   <th><strong>MyISAM</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>存储文件</strong></td> 
   <td>.frm 表定义文件 .ibd 数据文件</td> 
   <td>.frm 表定义文件 .myd 数据文件 .myi 索引文件</td> 
  </tr> 
  <tr> 
   <td><strong>锁</strong></td> 
   <td>行锁、表锁</td> 
   <td>表锁</td> 
  </tr> 
  <tr> 
   <td><strong>事务</strong></td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
  <tr> 
   <td><strong>CRDU</strong></td> 
   <td>读、写</td> 
   <td>读多</td> 
  </tr> 
  <tr> 
   <td><strong>count</strong></td> 
   <td>扫表</td> 
   <td>专门存储的地方</td> 
  </tr> 
  <tr> 
   <td><strong>索引结构</strong></td> 
   <td>B+Tree</td> 
   <td>B+Tree</td> 
  </tr> 
  <tr> 
   <td><strong>应用场景</strong></td> 
   <td>事务</td> 
   <td>读取性能</td> 
  </tr> 
  <tr> 
   <td><strong>外键</strong></td> 
   <td>支持</td> 
   <td>不支持</td> 
  </tr> 
 </tbody> 
</table>

*  **存储文件**
    
     *  **Innodb**：.frm 表定义文件 .ibd 数据文件
     *  **MyIsam**：.frm 表定义文件 .myd 数据文件 .myi 索引文件
 *  **锁**
    
     *  **Innodb**：锁粒度为行级锁，操作某一条数据时，只锁某一行，对其他行没有影响
     *  **MyIsam**：锁粒度为表级锁，操作某一条数据时，锁整一张表
 *  事务支持：
    
     *  **Innodb**：支持
     *  **MyIsam**：不支持
 *  外键支持：
    
     *  **Innodb**：支持
     *  **MyIsam**：不支持
 *  **应用场景**
    
     *  **Innodb**：关注事务要求性高的应用场景，适用于高并发**修改**场景（锁的粒度小）
     *  **MyIsam**：更关注读取的性能，不适用于高并发下**频繁的修改**数据（锁的粒度大）

### 2.3.1 底层存储文件不一样 ###

show variables like '%dir%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]

> linux的在`/var/lib/mysql/`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]  
进入数据库文件夹：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 6]  
再查看一下windows版本数据存储的位置：

show variables like '%dir%';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 7]  
进入目录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 8]

> 记牢：  
> InnoDB存储文件为两个：.frm（表定义文件）、.idb（数据&索引文件）  
> MyISAM存储文件为三个：.frm（表定义文件）、myd（数据文件）、（myi）索引文件）  
>   
>   
> **总结：MyISAM的索引和数据文件是分开的，InnoDB的数据和索引文件是放在一起的。**

### 2.3.2 锁粒度不同 ###

InnoDB并发修改情况下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 9]

> 只要不同的客户端操作的不是同一条记录，那么不会造成锁等待。

MyISAM并发修改情况下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 10]

> 其实表一旦上锁了，**其他线程读/写都不能操作**，必须要等待上一个线程释放锁（操作完毕）。因此我们可以知道，**MyISAM表在出现并发读写的情况下操作，效率极其低下。**

### 2.3.3 事务支持 ###

InnoDB是支持事务的，MyISAM是不支持事务的，这也是两个存储引擎最大的区别之一。

*  查看数据库表的存储引擎：

mysql> use test
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | demo1          |
    | demo2          |
    +----------------+
    2 rows in set (0.00 sec)
    
    mysql> show create table demo1;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | demo1 | CREATE TABLE `demo1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show create table demo2;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | demo2 | CREATE TABLE `demo2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>

> demo1表是MyISAM存储引擎，demo2表是InnoDB存储引擎。

*  测试MyISAM不支持事务  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 11]

> 发现事务还没有提交，另一个窗口就可以查询到数据，MyISAM是不支持事务的。

*  测试InnoDB支持事务  
    ![在这里插入图片描述][20200603101309450.png]

> 发现事务还未提交，另一个窗口就查询不到（我现在数据库的事务隔离级别为"可重复读"）  
> **MyISAM存储引擎不支持事务，也自然而然不存在事务隔离级别的概念。**

> **再提一点，MyISAM存储引擎是不支持外键约束的，我们实际开发中，通常也不使用外键约束，一般使用的都是逻辑外键（通过应用程序控制逻辑关系），因此这个我就不演示了，有兴趣的小伙伴可以自行测试。**

### 2.3.4 插入性能对比 ###

*  数据准备：

CREATE TABLE `user_myisam`  (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;			-- 存储引擎为myisam
    
    
    CREATE TABLE `user_innodb`  (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;			-- 存储引擎为innodb
    
    -- 创建存储过程批量插入数据
    create procedure innodb_insert(count int)
    begin
    		declare i int default 1;
    		while i<=count do 
    			INSERT INTO user_innodb values(i,uuid());
    			set i=i+1;
    		end while;
    end;
    
    -- 创建存储过程批量插入数据
    create procedure myisam_insert(count int)
    begin
    		declare i int default 1;
    		while i<=count do 
    			INSERT INTO user_myisam values(i,uuid());
    			set i=i+1;
    		end while;
    end;

> 编写两张表，两个存储过程，一张存储引擎为InnoDB，一张存储引擎为MyISAM表。存储过程用于批量插入

*  执行SQL语句测试：

call myisam_insert(100000);
    
    call innodb_insert(100000);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 12]

> 同样是插入10W的数据，InnoDB需要31.60s，而MyISAM只需要1.30s。

在这里可能有人会想起前面的对，不是MyISAM是表级锁吗？InnoDB还是行级锁呢！为什么还比InnoDB快那么多啊！**如果你这样想，那就大错特错了！****记住，锁是在并发情况下的，并发！并发！咱们现在是一个客户端呢！**

*  在这里稍微解释一下，为什么MyISAM插入性能比InnoDB快这么多。

InnoDB插入速度慢的很大一部分原因还是因为事务的原因。

1、**频繁的开启/提交事务，会消耗相当大的一部分资源**。2、**其次InnoDB需要支持事务，需要频繁记录事务日志**（`redo log/undo log`，当然你也可以调整事务刷新频率`innodb_flush_log_at_trx_commit`）**。3、并且innodb需要维护MVCC一致**。

> 这些操作极大的影响了innodb在插入时的速度，Myisam则这些问题统统不用考虑。

### 2.3.5 查询性能对比 ###

为了把对比效果彰显的更加明显，我们把数据量切换为300W。

truncate user_innodb;
    
    truncate user_myisam;
    
    -- 关闭唯一性校验
    set unique_checks=0;
    
    -- 控制在一个事务中(提高插入速度)
    start transaction;
    
    call innodb_insert(3000000);
    
    commit;
    
    call myisam_insert(3000000);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 13]

反复执行如下SQL，查看查询效率：

select * from user_innodb where username='1';
    
    select * from user_innodb where username='1';
    
    select * from user_innodb where username='1';
    
    select * from user_innodb where username='1';
    
    select * from user_myisam where username='1';
    
    select * from user_myisam where username='1';
    
    select * from user_myisam where username='1';
    
    select * from user_myisam where username='1';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 14]

> 可以发现InnoDB的查询效率明显要比MyISAM的慢。

**谈谈为什么InnoDB查询效率会比MyISAM慢？**

## 2.4 Memory 存储引擎 ##

Memory存储引擎将表的数据存放在内存中。每个Memory表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而**其数据文件，都是存储在内存中**，这样有利于数据的快速处理，提高整个表的效率。Memory类型的表访问非常地快，因为他的**数据是存放在内存中的，并且默认使用HASH索引** ， 但是服务一旦关闭，表中的数据就会丢失。

*  创建一张Memory存储引擎的表

CREATE TABLE `user_memory`  (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = memory AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 ;
    
    insert into user_memory values(1,'zs');
    
    insert into user_memory values(2,'ls');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 15]

> 发现只有表定义文件（.frm），memory的表数据是存储在内存中的。

*  我们编写一个存储过程，批量插入300W数据

create procedure memory_insert(count int)
    begin
    		declare i int default 1;
    		while i<=count do 
    			INSERT INTO user_memory values(i,
    										uuid(),
    										CEILING(RAND()*90+10));
    			set i=i+1;
    		end while;
    end;

*  执行存储过程

call memory_insert(3000000);

![在这里插入图片描述][20200603184511483.png]

> 出现：ERROR 1114 (HY000): The table ‘user\_memory’ is full错误

在MySQL中，有两个参数影响着memory表的使用：

*  max\_heap\_table\_size：memory表能使用的最大内存大小
 *  tmp\_table\_size：memory表超过此参数值将转化为磁盘表（即使转换为磁盘表，服务器重启后，数据仍会丢失）。

出现上面错误的原因是MySQL给Memory表分配的默认内存大小是16MB，300W的数据远远超过了16MB的存储空间，我们可以通过修改`max_heap_table_size`变量，来提升Memory表的存储大小限制。

*  查看`max_heap_table_size`大小。

select @@max_heap_table_size;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 16]

> 16777216B / 1024K / 1024M =16‬M

修改mysql配置文件（`/etc/my.cnf`），添加如下配置：

max_heap_table_size=800M
    tmp_table_size=800M

重启mysql服务器：

systemctl restart mysqld

*  再次插入300W数据：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 17]

> 要注意的是，在mysql服务器重启之后，memory存储引擎的表中的数据都会被清空。

使用Memory存储引擎需要注意如下几点：

*  Memory不支持BLOB或TEXT列。
 *  Memory表在所有客户端之间共享
 *  Memory表内容被存在内存中，默认大小是16MB，通过`max_heap_table_size`参数维护，当超过此大小则会报错，Memory表中的内容超过`tmp_table_size`值则会采用磁盘表，此时速度低，重启服务器数据仍会丢失
 *  当你不再需要Memory表的内容之时，要释放被Memory表使用的内存，你应该执行`DELETE FROM`或`TRUNCATE TABLE`，或者整个地删除表（使用`DROP TABLE`）。

例如：

max_heap_table_size：300M
    
    tmp_table_size：100M

说明：memory表最大能存储200M的数据，其中100M数据存放在内存中，另外200M存放在磁盘中。

> 这里提一下：如果真的要使用内存存储数据，一般我们都会采用市面上比较流行的缓存数据库（比如Redis），而不会采用Memory存储引擎，这些专门做缓存的数据库功能、操作都比Memory存储引擎要强大的多，我个人觉得这是Memory存储引擎没落的根本原因，当然另外一个原有也有可能是Memory存储引擎提供了很多其他的高级功能，由于市面上的资料和文献相对较少，因此大众并不知道Memory存储引擎的高级功能。

## 2.5 Merge 存储引擎 ##

Merge存储引擎，也被认识为MRG\_MyISAM引擎，Merge表 用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它。这些MyISAM表必须结构完全相同。Merge 表本身没有数据，对Merge 类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。

Merge 数据表的定义里可以包括一个`INSERT_METHOD`选项，这个选项的可取值是`NO`、`FIRST`、`LAST`，他们的含义依次是禁止插入、插入到第一个子表、插入到最后一个子表。

*  （1）创建三张表：

create table user_01(
    	id int,
    	username varchar(10)
    ) engine = myisam default charset=utf8;
    
    create table user_02(
    	id int,
    	username varchar(10)
    ) engine = myisam default charset=utf8;
    
    create table user_all(
    	id int,
    	username varchar(10)
    ) engine=merge union = (user_01,user_02) INSERT_METHOD=LAST default charset=utf8;

> 使用union确定merge表关联的myisam表，注意，这些myisam必须**一模一样**。  
>   
>   
> INSERT\_METHOD=LAST ：代表可以对merge表进行插入操作，进行的插入操作都到union关联的最后一张表去了（user\_02）表。

*  （2）在表中插入数据：

insert into user_01 values(1,'张三');
    insert into user_01 values(2,'李四');
    
    insert into user_02 values(10,'王五');
    insert into user_02 values(11,'赵六');

*  （3）查看表中的数据：

user\_01表中数据：  
![在这里插入图片描述][20200604105626352.png]  
user\_02表中数据：  
![在这里插入图片描述][20200604105649810.png]  
user\_all表中数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 18]

*  （4）在user\_all表中插入一条数据：

因为在创建Merge表时，INSERT\_METHOD设置的是LAST，因此插入到最后面的表中（user\_02）。

insert into user_all values(100,'钱七');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 19]  
Merge表除了在创建的时候选择合并的表，还可以在创建之后再添加其他的表（表结构必须一致）

*  1）创建一张新的表：

create table user_03(
    	id int,
    	username varchar(10)
    ) engine = myisam default charset=utf8;

> 存储引擎必须是MyISAM，其次表结构必须完全相同。

*  2）插入数据：

insert into user_03 values(1,'小龙');
    insert into user_03 values(2,'小明');

*  3）修改user\_all表

alter table user_all union = (user_01,user_02,user_03);

*  4）查询user\_all表：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 20]
 *  5）在user\_all插入一条新的数据，注意，此时的LAST表为user\_03了；

insert into user_all values(1,'小刚');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 21]

当你创建一个Merge表之时，MySQL在磁盘上创建两个文件。文件名以表的名字开始，并且有一个扩展名来指明文件类型。一个.frm文件存储表定义，一个.MRG文件维护着此Merge表关联了多少张myisam表（真正的数据不存放在.MRG文件，而是存放在不同的.MYD文件中）

--------------------

**Merge表有以下优点：**

*  容易地管理一套日志表。比如，你可以把不同月的数据放进分离的表中，用myisampack压缩其中的一些，并随后创建一个Merge表来把它们当作一个来使用。
 *  获得更快的速度。你可以以一些标准来分割大的只读表，然后放进不同磁盘上的单个表中。基于此的一个Merge表可比使用大表要快得多。
 *  执行更有效的搜索。如果你确切知道要搜索什么，对一些查询你可以只在被分割的表的其中之一来搜索，并且对其它使用Merge。
 *  执行更有效的修补。修补被映射到一个Merge表中的单个表比修补单个大型表要更轻松。
 *  超过操作系统的文件尺寸限制。每个MyISAM表都受制于这个限制，但是一个MyISAM表的集合则不然。

--------------------

**Merge表的缺点：**

*  Merge表只支持MyISAM表。
 *  你不能在Merge表中使用很多MyISAM功能。比如，你不能在Merge表上创建`FULLTEXT`索引。

> 关于更详细的Merge存储引擎说明，请参考MySQL官网：[https://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html][https_dev.mysql.com_doc_refman_5.7_en_merge-storage-engine.html]

# 三、总结 #

存储引擎这篇就讲到这里了，MySQL支持的存储引擎还有很多，只不过这些存储引擎大部分在安装MySQL的时候并没有安装。

下面列举一下MySQL支持的存储引擎：InnoDB（默认）、MyISAM（MySQL5.1以及之前版本默认）、Memory、Merge、CSV、BDB、Example、Archive、Blackhole、Federated等。

--------------------

MySQL存储引擎官网资料：[https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html][https_dev.mysql.com_doc_refman_5.7_en_storage-engines.html]

> 大家有兴趣可以来官网查询MySQL其他存储引擎的相关资料

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70]: /images/20221119/47cf4324857c4b5e88887199bd4c1810.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 1]: /images/20221119/1df972f8b1504714b792b9333cbadc1e.png
[20200603112820400.png]: /images/20221119/de57ea54dc704a6a82f58e9f2a287c24.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 2]: /images/20221119/06d1856cd5d243e49ebd97d61e167a93.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 3]: /images/20221119/e2cfc081b34346a0b0f7b356e116e904.png
[MyISAM_myisampack]: https://blog.csdn.net/Bb15070047748/article/details/106520776
[https_dev.mysql.com_doc_refman_5.7_en_myisam-table-formats.html]: https://dev.mysql.com/doc/refman/5.7/en/myisam-table-formats.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 4]: /images/20221119/b0e387df7a274367abee76051cfac105.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 5]: /images/20221119/23c265d1413c43a4ac61726123c6141a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 6]: /images/20221119/e6f00b93e9d44689a4e04a6749f18a0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 7]: /images/20221119/b15f866bebb743a6a8efa55ee6ac75be.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 8]: /images/20221119/d65a1073804848af8083c6eb939083e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 9]: /images/20221119/b452b142658c4d52beb79c0cdea62e14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 10]: /images/20221119/4a74432c85ff470cbd05b838e6493ab0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 11]: /images/20221119/cb084eedb6314bc19dc3e45c1b7f0a49.png
[20200603101309450.png]: /images/20221119/7483c8f09f274a7682d6da3fdc3265c8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 12]: /images/20221119/49c44053b1264f239bcd650a60c45d2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 13]: /images/20221119/70ec24672ffe4b40bcd80f371fc712fc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 14]: /images/20221119/4adb81708afd4005903625c7fe828e45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 15]: /images/20221119/65602832ce084fcb86ffea961cb3dc0e.png
[20200603184511483.png]: /images/20221119/ddc1c33681164178b23b21e9b82c98af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 16]: /images/20221119/26f19385ba5b44ceb84529051aa1c9fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 17]: /images/20221119/6d5ca354625844afac0c92a76db81011.png
[20200604105626352.png]: /images/20221119/5495015d66a64988a98f0b11bf598185.png
[20200604105649810.png]: /images/20221119/2c70cab76a8e467a8f4023255821a38f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 18]: /images/20221119/665679d1edce4feebff9f2db797ae3eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 19]: /images/20221119/82123660b53648b59b433ad440495518.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 20]: /images/20221119/47cdfd11b45f4babb805bec944dfd905.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JiMTUwNzAwNDc3NDg_size_16_color_FFFFFF_t_70 21]: /images/20221119/1433aae1311b4bd5a4ffed488b37db0a.png
[https_dev.mysql.com_doc_refman_5.7_en_merge-storage-engine.html]: https://dev.mysql.com/doc/refman/5.7/en/merge-storage-engine.html
[https_dev.mysql.com_doc_refman_5.7_en_storage-engines.html]: https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html