【MySQL】之存储引擎

曾经终败给现在 2022-09-05 14:52 192阅读 0赞

# 目录 #

*  一、常用的存储引擎
 *  二、Myisam 存储引擎
 *  三、InnoDB 存储引擎
 *  四、Memory 存储引擎
 *  五、Archive 存储引擎
 *  六、存储引擎的选择

# 一、常用的存储引擎 #

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

<table> 
 <thead> 
  <tr> 
   <th>功能特性</th> 
   <th>Myisam</th> 
   <th>InnoDB</th> 
   <th>Memory</th> 
   <th>Archive</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>批量插入的速度</td> 
   <td>高</td> 
   <td>低</td> 
   <td>高</td> 
   <td>非常高</td> 
  </tr> 
  <tr> 
   <td>支持事务</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>锁机制</td> 
   <td>表锁</td> 
   <td>行锁</td> 
   <td>表锁</td> 
   <td>行锁</td> 
  </tr> 
  <tr> 
   <td>存储限制</td> 
   <td>256TB</td> 
   <td>64TB</td> 
   <td>RAM内存</td> 
   <td>没有</td> 
  </tr> 
  <tr> 
   <td>支持全文索引</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持B树索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持哈希索引</td> 
   <td></td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持集群索引</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持数据缓存</td> 
   <td></td> 
   <td>支持</td> 
   <td>N/A</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持索引缓存</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>支持数据压缩</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>空间使用率</td> 
   <td>低</td> 
   <td>高</td> 
   <td>N/A</td> 
   <td>非常低</td> 
  </tr> 
  <tr> 
   <td>内存时间</td> 
   <td>低</td> 
   <td>高</td> 
   <td>中等</td> 
   <td>低</td> 
  </tr> 
 </tbody> 
</table>

# 二、Myisam 存储引擎 #

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

**Mysql5.5版本之前的默认存储引擎，但MySQL8已废除**

**特点：**

*  `select count(*) from table` 无需进行数据的扫描，它有一个专门计算数据的函数，InnoDB需要一行行的扫描，计算出来；
 *  数据（`MYD`）和索引（`MYI`）分开存储；
 *  表级锁；
 *  不支持事务。

数据和索引分别存储，不管用哪个存储引擎，都会生成一个 `.frm` 文件（表定义文件），数据保存在 `myd` 文件，索引保存在 `myi` 文件里面。在 *Myisam* 里面，叶子节点的数据区保存的是 `.myd` 的内存地址，在 `.myi` 通过索引找到这条数据的内存地址，再通过这个地址去 `.myd` 里面找到对应的数据。

# 三、InnoDB 存储引擎 #

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

**Mysql5.5及以后版本的默认存储引擎**

### 1、InnoDB 引擎的特点： ###

*  *InnoDB* 给 *MySQL* 提供了具有提交、回滚和崩溃恢复能力的事物安全（*ACID兼容*）；
 *  *InnoDB* 是为处理巨大数据量的最大性能设计，它的 *CPU* 效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的；
 *  使用行级锁，但容易造成死锁；
 *  使用聚集索引（主键索引）方式进行数据存储；
 *  *InnoDB* 支持外键完整性约束，**存储表的数据时，每张表的存储都按照主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个隐藏的主键**

只有 `.ibd` 和 `.frm` 两个文件，他的 *index* 和数据放在了一起，在 *InnoDB* 中，以主键为索引来组织数据的存储，如果没有明确指定一个主键（*ID*）索引，他会默认的生成一个隐藏的 *6byte* 的 *Int* 型的索引来作为他的主键索引，只是这个隐式的索引看不到而已。

**注意：** *MySQL* 每个版本都是略微差异，比如在 *MySQL5.7* 之前都有 `.frm` 文件，而在 *MySQL8.0* 之后就将该文件移除掉了。

### 2、InnoDB 引擎的 4 大特性： ###

*  **插入缓冲（Insert buffer)：** Insert Buffer 用于非聚集索引的插入和更新操作。先判断插入的非聚集索引是否在缓存池中，如果在则直接插入，否则插入到 Insert Buffer 对象里。再以一定的频率进行 Insert Buffer 和辅助索引叶子节点的 merge 操作，将多次插入合并到一个操作中，提高对非聚集索引的插入性能；
 *  **二次写 (Double write)：** Double Write 由两部分组成，一部分是内存中的 double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过 memcpy 函数将脏页先复制到内存中的该区域，之后通过 doublewrite buffer 再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免操作系统缓冲写带来的问题；
 *  **自适应哈希索引 (Adaptive Hash Index)：** InnoDB会根据访问的频率和模式，为热点页建立哈希索引，来提高查询效率。索引通过缓存池的 B+ 树页构造而来，因此建立速度很快，InnoDB存储引擎会监控对表上各个索引页的查询，如果观察到建立哈希索引可以带来速度上的提升，则建立哈希索引，所以叫做自适应哈希索引；
 *  **缓存池：** 为了提高数据库的性能，引入缓存池的概念，通过参数 *innodb\_buffer\_pool\_size* 可以设置缓存池的大小，参数 *innodb\_buffer\_pool\_instances* 可以设置缓存池的实例个数。

### 3、InnoDB与MYISAM的区别： ###

*  InnoDB 支持事务（TRANSACTION），支持行级锁，但可能会发生死锁
 *  Myisam 使用表锁，没有事务，不带条件的统计 count 比 InnoDB 统计 count 效率高

# 四、Memory 存储引擎 #

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

**数据都是存储在内存中，IO效率要比其他引擎高很多 服务重启数据丢失，内存数据表默认只有16M。**

*一般不会在生产上使用 memory存储引擎，但是他是临时表默认的存储引擎。*

**特点：**

*  支持 *hash* 索引，*B tree* 索引，默认 *hash*（查找复杂度 0(1) ）；
 *  字段长度都是固定长度 `varchar(32)=char(32)` 不支持大数据存储类型字段如 `blog`、`text`（如果超过的话，会选择 *Myisam* 引擎）；
 *  每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度；
 *  表使用一个固定的记录长度格式
 *  支持 *AUTO\_INCREMENT* 列和对可包含 *NULL* 值的列的索引
 *  当不再需要 *MEMORY* 表的内容时，要释放被 *MEMORY* 表使用的内存，应该执行`DELETE FROM` 或 `TRUNCATE TABLE`，或者删除整个表（使用 `DROP TABLE`）

**应用场景：** 等值查找热度较高数据 查询结果内存中的计算，大多数都是采用这种存储引擎 作为临时表存储需计算的数据。

# 五、Archive 存储引擎 #

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

**使用压缩协议进行数据的存储，数据存储为ARZ文件格式**

**特点：**

*  只支持 `insert` 和 `select` 两种操作
 *  只允许自增 *ID* 列建立索引
 *  行级锁
 *  不支持事务
 *  数据占用磁盘少（*较其他存储引擎小的多，基本 1:8-1:9 的比例*）

\*\*应用场景：\*\*日志系统、大量的设备数据采集。

# 六、存储引擎的选择 #

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

*  **InnoDB：** 如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
 *  **MyISAM：** 如果数据表主要用来插入和查询记录，则MyISAM（但是不支持事务）引擎能提供较高的处理效率
 *  **Memory：** 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。数据的处理速度很快但是安全性不高。
 *  **Archive：** 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive