勇闯BAT 聊聊Mysql的存储引擎

冷不防 2023-01-17 03:16 66阅读 0赞

### 文章目录 ###

*   *  存储引擎是什么
     *  查看支持的引擎
     *  常用存储引擎介绍
     *   *  Archive
         *  Memory
         *  MyISAM
         *  InnoDB
         *  概要
     *  常见问题
     *   *  MyISAM 和 InnoDB的区别
         *  存储引擎是针对实例的还是表
         *  自增主键最大值保存问题
         *  InnoDB引擎是否支持hash索引

## 存储引擎是什么 ##

MySQL架构可以分为4层，先看下，其所在的位置。

*  连接层：包括了数据库连接、授权认证、安全管理等
 *  Server层：包括 SQL 解析、优化、执行以及缓存等
 *  存储引擎层：包括了各种存储引擎，主要负责数据的存取等
 *  数据存储层：主要负责将数据存储在文件系统中，并完成与存储引擎的交互。

![img][]

*  存储引擎层负责**数据的存储和提取**。其架构模式是**插件式**的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。
 *  存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。
 *  MySQL中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的每一种技术都使用不同的存储机制，索引技巧，锁定水平并且最终提供广泛的不同功能和能力。在MySQL中将这些不同的技术及配套的相关功能称为存储引擎。

## 查看支持的引擎 ##

SHOW ENGINES;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center][]

## 常用存储引擎介绍 ##

### Archive ###

压缩协议进行数据的存储，数据存储为ARZ文件的格式。在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差一些。

特点：

*  只支持insert和select操作
 *  只允许自增ID列建索引
 *  行级锁
 *  不支持事务
 *  数据占用磁盘少

应用场景：

*  日志系统
 *  大量的设备采集

### Memory ###

数据都是存储在内存中，IO效率高，服务重启数据会丢失，内存表默认只有16M。

特点:

*  支持Hash、Btree索引
 *  不支持大数据存储类型，例如：blob、text
 *  表级锁
 *  只能使用比较运算符=，<，>，=>，= <
 *  HEAP表不支持AUTO\_INCREMENT

应用场景：

*  缓存表

### MyISAM ###

在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

特点：

*  select count（\*）无需扫描所有行
 *  数据（MYD）和索引（MYI）分开存储
 *  表级锁，限制了读/写工作负载中的性能
 *  不支持事务

应用场景：

*  通常用于Web和数据仓库配置中的只读场景。
 *  以读为主的应用程序，比如博客系统、新闻门户网站

### InnoDB ###

它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

特点：

*  数据和索引在一个文件(.ibd)
 *  行级锁
 *  支持事务

应用场景

*  更新（删除）操作频率高，或者要保证数据的完整性。
 *  并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

### 概要 ###

<table> 
 <thead> 
  <tr> 
   <th>功能</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
   <th>Archive</th> 
   <th>Memory</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>B-tree 索引</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td><strong>安全点恢复</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td><strong>集群数据库支持</strong></td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>聚集索引</strong></td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>压缩数据</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>数据缓存</strong></td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>N/A</td> 
  </tr> 
  <tr> 
   <td><strong>加密数据</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td><strong>外键</strong></td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>全文搜索索引</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>地理空间数据类型支持</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>地理空间索引支持</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>哈希索引</strong></td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td><strong>索引缓存</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>N/A</td> 
  </tr> 
  <tr> 
   <td><strong>锁定粒度</strong></td> 
   <td>Table</td> 
   <td>Row</td> 
   <td>Row</td> 
   <td>Table</td> 
  </tr> 
  <tr> 
   <td><strong>MVCC</strong></td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>复制支持</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Limited</td> 
  </tr> 
  <tr> 
   <td><strong>储存限制</strong></td> 
   <td>256TB</td> 
   <td>64TB</td> 
   <td>None</td> 
   <td>RAM</td> 
  </tr> 
  <tr> 
   <td><strong>T树索引</strong></td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>事务</strong></td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td><strong>更新数据字典的统计信息</strong></td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
 </tbody> 
</table>

特别注意InnoDB的更新情况

*  MySQL 5.6和更高版本提供了对FULLTEXT索引的支持。
 *  MySQL 5.7和更高版本提供了对地理空间索引的支持。

> https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

## 常见问题 ##

### MyISAM 和 InnoDB的区别 ###

<table> 
 <thead> 
  <tr> 
   <th>存储结构</th> 
   <th>MyISAM</th> 
   <th>InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储格式</td> 
   <td>在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。</td> 
   <td>在磁盘上存储成二个文件。文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据和索引集中存储.ibd。</td> 
  </tr> 
  <tr> 
   <td>存储空间</td> 
   <td>可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表</td> 
   <td>需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。</td> 
  </tr> 
  <tr> 
   <td>可移植性、备份及恢复</td> 
   <td>数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。</td> 
   <td>可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。</td> 
  </tr> 
  <tr> 
   <td>事务支持</td> 
   <td>强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。</td> 
   <td>提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表</td> 
  </tr> 
  <tr> 
   <td>表锁差异</td> 
   <td>只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。</td> 
   <td>支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的</td> 
  </tr> 
  <tr> 
   <td>表的具体行数</td> 
   <td>保存有表的总行数，如果select count(*) from table;会直接取出出该值</td> 
   <td>没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样</td> 
  </tr> 
 </tbody> 
</table>

> 来源：https://www.jianshu.com/p/a957b18ba40d

### 存储引擎是针对实例的还是表 ###

存储引擎是指定在表之上的，即一个库中的每一个表都可 以指定专用的存储引擎。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 1][]

### 自增主键最大值保存问题 ###

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

1.**Mysql8.0以下版本**  
表类型为**InnoDB**引擎，这条记录的ID是15。因为InnoDB表只把自增主键的最大ID记录到**内存**中，所以重启MYSQL或者对表OPTIMIZE操作，都会使最大ID丢失。  
表类型为**MylSAM**引擎，这条记录的ID是18。因为MylSAM表会把自增主键的最大ID记录到**数据文件**里面，重启MYSQL后，自增主键的最大ID也不会丢失。

2.**Mysql8.0及以上版本**  
这条记录的ID是18，因为这个版本保存ID的值是在**redo**日志中的，重启之后是可以恢复的。

### InnoDB引擎是否支持hash索引 ###

上面其实已经介绍过了，是**不支持Hash索引**的，但是其内部有个**自适应Hash索引**。

**自适应Hash索引**特征能使InnoDB在具有适当的工作负载和足够缓冲池内存的系统上执行的更像内存中的数据库的操作，且不会牺牲事务特性或可靠性，MySQL能基于监视到的搜索规则，使用索引键的前缀构建Hash索引，前缀可以是任意长度，并且可能只有b+树中的某些值出现在Hash索引中，Hash索引其实就是对经常访问的索引页进行构建的。

这又说明其实InnoDB是支持Hash索引的，但并不是真正意义上的Hash，而是通过自己的监视情况自动对某些热点索引值构建的内存Hash。可以**理解为其是索引的索引**。

看下具体的图就很容易明白，这里借用沈剑老师的图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 2][]

但是我自己在InnoDB下怎么可以设置Hash索引呢？

这里对name做了hash索引，并没有报错，能正常执行。

CREATE TABLE `test` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `name` (`name`) USING HASH
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这也太奇怪了，使用 `show index from test`查看索引信息

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 3][]

实际上其还是BTREE索引，应该是内部自动转换为 BTREE索引了，明显的泰国人妖，外壳妹子，内核汉子。

[img]: /images/20221022/40a5bf34ebe44d7281713cdc2441e428.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center]: /images/20221022/4df6df3734174446843cbd21371cabc0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221022/0c37bbab396844c8ad8e94caa7cfd01f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221022/ed0533d34c3c4af29b311da21d67f11a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FidTkzNTAwOTA2Ng_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221022/f44447295605449b9dc9318ac4853b50.png