mysql系列之存储引擎

太过爱你忘了你带给我的痛 2022-12-06 15:11 168阅读 0赞

> 本篇主要介绍mysql的存储引擎，说明主要的几个存储引擎的特性及其差别。

## 一、存储引擎介绍 ##

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

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

MySQL 的核心就是存储引擎，不同存储引擎支持的特性各不相同。

MySQL中的存储引擎是插件式存储引擎。

MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。

MySQL中的存储引擎的控制级别是表级别，我们可以在建表的时候指定表采用的存储引擎。

### 二、MySQL支持的存储引擎类型 ###

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。

可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如图所示。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 1]  
Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

官网对各个存储引擎特性的说明：

<table> 
 <thead> 
  <tr> 
   <th>Feature</th> 
   <th>MyISAM</th> 
   <th>Memory</th> 
   <th>InnoDB</th> 
   <th>Archive</th> 
   <th>NDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>事务支持</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>锁的粒度</td> 
   <td>Table</td> 
   <td>Table</td> 
   <td>Row</td> 
   <td>Row</td> 
   <td>Row</td> 
  </tr> 
  <tr> 
   <td>存储限制</td> 
   <td>256TB</td> 
   <td>RAM</td> 
   <td>64TB</td> 
   <td>None</td> 
   <td>384EB</td> 
  </tr> 
  <tr> 
   <td>MVCC（多版本控制）</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>外键支持</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes (note 5)</td> 
  </tr> 
  <tr> 
   <td>聚簇索引</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>B-tree indexes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>全文检索支持</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes (note 6)</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>地理空间数据类型支持</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>地理空间索引支持</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes (note 7)</td> 
   <td>No</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>Hash indexes</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>No (note 8)</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>T-tree 索引</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>索引缓存</td> 
   <td>Yes</td> 
   <td>N/A</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>集群支持</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>压缩数据</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>No</td> 
  </tr> 
  <tr> 
   <td>数据缓存</td> 
   <td>No</td> 
   <td>N/A</td> 
   <td>Yes</td> 
   <td>No</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>数据加密</td> 
   <td>Yes (note 3)</td> 
   <td>Yes (note 3)</td> 
   <td>Yes (note 4)</td> 
   <td>Yes (note 3)</td> 
   <td>Yes (note 3)</td> 
  </tr> 
  <tr> 
   <td>备份和时间点恢复</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>Replication support (主从支持)</td> 
   <td>Yes</td> 
   <td>Limited (note 9)</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <td>Update statistics for data dictionary</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
   <td>Yes</td> 
  </tr> 
 </tbody> 
</table>

注意：  
根据特性的重要程度和差别情况，对顺序进行了一定的调整。

### 三、重点引擎的类型介绍 ###

#### 1、InnoDB存储引擎 ####

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了，他的主要特点有：  
（1）灾难恢复性比较好；  
（2）支持事务。默认的事务隔离级别为可重复读，通过MVCC（并发版本控制）来实现的。  
（3）使用的锁粒度为行级锁，可以支持更高的并发；  
（4）支持外键；  
（5）配合一些热备工具可以支持在线热备份；  
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；  
（7）支持聚簇索引。对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上，通过聚簇索引来查询可以减少回表查询。  
（8）InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描  
（9）支持B-tree索引和全文检索（ MySQL 5.6后InnoDB存储引擎开始支持全文检索）  
（10）不支持Hash索引，但是内置了自适应hash索引。

#### 2、MyISAM存储引擎 ####

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点为：

（1）不支持事务；  
（2）不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；  
（3）不支持聚簇索引，对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；  
（4）默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；  
（5）支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。  
（6）数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

#### 重点:InnoDB和MyISAM的对比 ####

1、事务支持：InnoDB支持事务，MyISAM不支持事务。  
2、锁粒度：InnoDB为行级锁，MyISAM为表级锁。  
所以MyISAM相对于InnoDB来说，更容易发生死锁，锁冲突的概率更大，而且上锁的开销也更大，因为需要为每一行加锁，所以InnoDB比MyISAM支持更高的并发；  
3、外键支持：InnoDB支持外键，MyISAM不支持外键。  
4、聚簇索引支持：InnoDB支持聚簇索引，MyISAM不支持聚簇。  
5、数据安全和备份：在备份容灾上，InnoDB支持在线热备，有很成熟的在线热备解决方案；  
6、查询性能：MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；  
7、表总记录数统计：SELECT COUNT(\*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要朱行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；  
8、存储文件：MyISAM的表结构文件包括：.frm(表结构定义),.MYI(索引),.MYD(数据)；而InnoDB的表数据文件为:.ibd和.frm(表结构定义)；

#### 3、MEMORY存储引擎 ####

将数据存在内存中，和市场上的Redis，memcached等思想类似，为了提高数据的访问速度，主要特点：

（1）支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；  
（2）支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；  
（3）由于数据是存放在内存中，所以在服务器重启之后，所有数据都会丢失；  
（4）查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；

### 四、如何选择合适的存储引擎 ###

1、使用场景是否需要事务支持；  
2、是否需要支持高并发，InnoDB的并发度远高于MyISAM；  
3、是否需要支持外键；  
4、是否需要支持在线热备；  
5、高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；  
6、索引，不同存储引擎的索引并不太一样；  
7、是否需要支持集群。

总的来说，目前主流的MySQL使用场景都是采用InnoDB存储引擎，这也是为什么MySQL官方在MySQL 5.5.5 之后将InnoDB 作为默认存储引擎的原因。

### 五、存储引擎设置 ###

#### 1、建表的时候指定存储引擎 ####

CREATE TABLE t1 (i INT) ENGINE = INNODB;

#### 2、设置默认的存储引擎 ####

MySQL 5.5.5 之后InnoDB是默认存储引擎，如果需要修改默认的存储引擎，可以通过修改my.cnf配置文件中的default-storage-engine的值。

default_storage_engine=NDBCLUSTER;

#### 3、修改表的存储引擎 ####

ALTER TABLE t ENGINE = InnoDB;

### 总结 ###

本文主要介绍了mysql的存储引擎以及各存储引擎的特性。  
根据各个存储引擎的特性指出各自适用的使用场景。  
核心是：InnoDB和MyISAM的区别，面试中的高频问题。  
注意MySQL的**存储引擎控制的粒度是表级别的**，也就是同一个数据库中的不同表可以设置不同的存储引擎。

更多精彩，关注我吧。  
![图注:跟着老万学java][java]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/581873341b6c4d5ab37bd867eaa57385.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3cxMDE0MDc0Nzk0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/3d2bcd000d8f474eb95c42c20512f168.png
[java]: /images/20221123/0599d304bb024d699468e5e63ef89521.png