MySQL(一)存储引擎

﹏ヽ暗。殇╰゛Y 2023-01-09 12:32 151阅读 0赞

### MySQL存储引擎 ###

*  InnoDB
 *   *  特性
     *  优势
 *  MyISAM
 *   *  特性
 *  Archive
 *   *  特性
 *  BlackHole
 *  CSV
 *  Memory
 *  MERGE
 *  FEDERATED

# InnoDB #

MySQL5.5版本后的默认存储引擎，支持ACID事务。  
兼顾高**可靠性，高性能**。  
DML语句支持ACID模型，支持commit，rollback，crash-recovery事务特性  
支持行级锁和oracle风格的一致性读取，提高并发，性能  
数据存储磁盘，基于主键优化查询，每个InnoDB表都有一个称为clustered index的索引。该索引优化数据，以最小化IO查找数据  
支持外键

**非特殊需求，优先考虑使用innodb**

## 特性 ##

支持的数据库特性

<table> 
 <thead> 
  <tr> 
   <th align="left">特性</th> 
   <th align="left">是否支持</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">B-tree索引</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">备份/时间点恢复（在服务器中而不是在存储引擎中实现。）</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">集群数据库</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">聚集索引</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">数据压缩</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">数据缓存</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">加密数据</td> 
   <td align="left">是（在MySQL服务中通过加密函数实现，在MySQL5.7以上版本中支持静态数据加密）</td> 
  </tr> 
  <tr> 
   <td align="left">外键</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">全文检索索引</td> 
   <td align="left">是（5.6以上版本）</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间数据类型支持</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间索引</td> 
   <td align="left">是（5.7以上）</td> 
  </tr> 
  <tr> 
   <td align="left">哈希索引</td> 
   <td align="left">否（自适应hash索引）</td> 
  </tr> 
  <tr> 
   <td align="left">索引缓存</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">锁定粒度</td> 
   <td align="left">行级锁</td> 
  </tr> 
  <tr> 
   <td align="left">MVCC</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">replication</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">存储限制</td> 
   <td align="left">64TB</td> 
  </tr> 
  <tr> 
   <td align="left">T-tree索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">事务</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">更新数据字典的统计信息</td> 
   <td align="left">是</td> 
  </tr> 
 </tbody> 
</table>

## 优势 ##

*  如果服务器由于硬件或软件问题而意外退出，无论当时数据库中发生了什么，只需重新启动数据库。 InnoDB的crash-recovery（崩溃恢复）会自动完成在崩溃之前提交的更改，并撤消正在处理但尚未提交的更改，从而允许您重新启动并从上次中断的地方继续。
 *  InnoDB引擎维护自己的缓冲池，该缓冲池在访问数据时将表和索引数据缓存在主内存中。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息，并加快了处理速度。在专用数据库服务器上，通常将多达80％的物理内存分配给缓冲池。
 *  如果将相关数据拆分到不同的表中，也可以设置强制引用完整性的外键。
 *  如果数据在磁盘或内存中损坏，则校验和机制会在使用前提醒您注意虚假数据。 innodb\_checksum\_algorithm变量定义InnoDB使用的校验和算法。
 *  当设计一个具有用于每个表的适当主键列的数据库时，涉及这些列的操作会自动进行优化。在WHERE子句，ORDER BY子句，GROUP BY子句和联接操作中引用主键列非常快速。
 *  插入，更新和删除通过称为更改缓冲的自动机制进行了优化。 InnoDB不仅允许对同一表的并发读写访问，而且还缓存更改的数据以简化磁盘I / O。
 *  性能优势不仅限于具有长时间运行的查询的大型表。当从表中一遍又一遍地访问相同的行时，自适应哈希索引将接管这些查询，以使这些查找变得更快，就好像它们是从哈希表中出来的一样。
 *  支持压缩表和关联的索引。
 *  支持加密数据。
 *  支持创建和删除索引并执行其他DDL操作，而对性能和可用性的影响要小得多。
 *  truncate 表文件表空间非常快，可以释放磁盘空间供操作系统重用，其他引擎也支持。
 *  使用DYNAMIC行格式，表数据对于BLOB和长文本字段更有效。
 *  支持通过查询INFORMATION\_SCHEMA表来监视存储引擎的内部工作情况。
 *  支持通过查询性能架构表来监视存储引擎的性能详细信息。
 *  支持在同一条语句中将InnoDB表与其他MySQL存储引擎的表混合使用。例如，您可以使用联接操作在单个查询中合并InnoDB和MEMORY表中的数据。
 *  InnoDB专为在处理大数据量时提高CPU效率和最佳性能而设计。
 *  InnoDB表可以处理大量数据，即使在文件大小限制为2GB的操作系统上也是如此。

# MyISAM #

查询插入速度快，不支持事务。

## 特性 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">特性</th> 
   <th align="left">是否支持</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">B-tree索引</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">备份/时间点恢复（MySQL服务实现，而不是在存储引擎中实现。）</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">集群数据库</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">聚集索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">数据压缩</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">数据缓存</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">加密数据</td> 
   <td align="left">是（在MySQL服务中通过加密函数实现）</td> 
  </tr> 
  <tr> 
   <td align="left">外键</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">全文检索索引</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间数据类型支持</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间索引</td> 
   <td align="left">是（5.7以上）</td> 
  </tr> 
  <tr> 
   <td align="left">哈希索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">索引缓存</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">锁定粒度</td> 
   <td align="left">表级锁</td> 
  </tr> 
  <tr> 
   <td align="left">MVCC</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">replication（MySQL服务实现，而不是在存储引擎中实现。）</td> 
   <td align="left">yes</td> 
  </tr> 
  <tr> 
   <td align="left">存储限制</td> 
   <td align="left">256TB</td> 
  </tr> 
  <tr> 
   <td align="left">T-tree索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">事务</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">更新数据字典的统计信息</td> 
   <td align="left">是</td> 
  </tr> 
 </tbody> 
</table>

不支持事务和行级锁，崩溃后无法安全恢复

每个MyISAM表使用三个文件存储在磁盘上。三个文件的名称相同，但是扩展名不同，.frm文件存储表格式，.MYD存储数据，.MYI存储索引  
MySQL5.7以后，创建MyISAM表时需要指定引擎，否则会默认InnoDB引擎。  
支持使用myisampack压缩MyISAM表，以减少占用存储空间。

# Archive #

以尽可能少的空间存储大数据量，不支持索引，一般存储历史数据。

## 特性 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">特性</th> 
   <th align="left">是否支持</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">B-tree索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">备份/时间点恢复（在服务器中而不是在存储引擎中实现。）</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">集群数据库</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">聚集索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">数据压缩</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">数据缓存</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">加密数据</td> 
   <td align="left">是（在MySQL服务中通过加密函数实现，在MySQL5.7以上版本中支持静态数据加密）</td> 
  </tr> 
  <tr> 
   <td align="left">外键</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">全文检索索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间数据类型支持</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">地理空间索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">哈希索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">索引缓存</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">锁定粒度</td> 
   <td align="left">行级锁</td> 
  </tr> 
  <tr> 
   <td align="left">MVCC</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">replication</td> 
   <td align="left">是</td> 
  </tr> 
  <tr> 
   <td align="left">存储限制</td> 
   <td align="left">无限制</td> 
  </tr> 
  <tr> 
   <td align="left">T-tree索引</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">事务</td> 
   <td align="left">否</td> 
  </tr> 
  <tr> 
   <td align="left">更新数据字典的统计信息</td> 
   <td align="left">是</td> 
  </tr> 
 </tbody> 
</table>

创建Archive表，会在数据库数据目录下生成.frm的表描述文件，和.ARZ的数据文件，以及.ARN文件  
行数据在被插入时就压缩，archive使用zlib无损压缩

支持insert select replace操作，不支持delete update操作  
支持Auto\_increment，除了auto\_increment列外，其他列不支持索引  
Auto\_increment列不支持插入比当前列最大值小的值

# BlackHole #

黑洞引擎，BlackHole引擎扮演了黑洞的角色，接收数据，但是丢弃数据，不保存数据，查询返回空结构。

同样创建blackhole表，数据库服务会创建.frm表描述文件，但是没有其他文件了。

支持所有类型的索引

插入到BLACKHOLE表中不会存储任何数据，但是如果启用了基于语句的二进制日志记录，则会记录SQL语句并将其复制到副本服务器。 这可以用作数据转发器或数据过滤器机制。

假设您的应用程序需要副本端过滤规则，但是首先将所有二进制日志数据传输到副本会导致流量过多。 在这种情况下，可以在源主机上设置一个“虚拟”副本进程，其默认存储引擎为BLACKHOLE，如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWhhb18_size_16_color_FFFFFF_t_70]  
源写入其二进制日志。 “虚拟” mysqld进程充当副本，应用了所需的reply-do- \*和replicate-ignore- \*规则组合，并编写了自己的新的，经过过滤的二进制日志。 此过滤后的日志提供给副本。

虚拟进程实际上并不存储任何数据，因此在复制源主机上运行附加的mysqld进程几乎不会产生处理开销。可以使用其他复制副本重复这种类型的设置。

**其他用途**  
验证转储文件语法。  
通过比较在启用和未启用二进制日志记录的情况下使用BLACKHOLE的性能来比较二进制日志记录的开销。  
BLACKHOLE本质上是“无操作”存储引擎，因此可以用于查找与存储引擎本身无关的性能瓶颈。

# CSV #

逻辑上由逗号分隔的数据  
创建CSV表时，服务器会在数据库目录中创建表格式文件\{表名\}.frm。 存储引擎还创建纯文本数据文件\{表名\}.CSV。 当将数据存储到表中时，存储引擎会将其以逗号分隔的值格式保存到数据文件中。  
创建CSV表还会创建一个对应的图元文件，该图元文件存储表的状态和表中存在的行数。 该文件的名称与带有扩展名CSM的表的名称相同。  
电子表格软件可以读取，甚至数据库创建的.CSV文件  
如创建test\_csv表并添加数据后

# ls
    db.opt  test_csv.CSM  test_csv.CSV  test_csv.frm
    # cat test_csv.CSV
    1,"ruoxi","语文",99,"2021-03-01 16:17:56"
    2,"ruoxi","数学",98,"2021-03-01 16:18:27"
    3,"ruoxi","英语",97,"2021-03-01 16:18:27"

**局限性**  
CSV引擎，不支持索引  
不支持分片  
CSV引擎创建表所有列都必须Not Null  
不支持列值制动增长 AUTO\_INCREMENT

# Memory #

Memory存储引擎，在内存中存储。数据容易受到崩溃，断电，硬件问题影响，因此只用做临时工作区或者只读缓存，通常从其他表提取数据。

Memory存储引擎 VS NDB Cluster

希望部署使用Memory存储引擎处理重要，高可用或经常更新的数据时，应该考虑NDB Cluster是否是更好的选择。  
选择Memory引擎场景的典型特征：

*  临时性，非关键性数据操作，如Session，缓存。当MySQL服务器停止或者重启后，数据将会丢失。
 *  数据量适中，内存中存储，可以快速访问和低延迟，但是一般内存资源更稀缺。
 *  只读数据或只能只读访问的数据  
    NDB Cluster提供了与Memory引擎相同功能，但是具有更高的性能水平，并提供了Memory没有的功能：
 *  行级锁，多线程操作，减少竞争，提高性能
 *  包含语句混合也具有可伸缩性
 *  可选的磁盘支持操作，支持持久化
 *  多主机操作，没有单点故障，高可用99.999%
 *  跨节点自动数据分发，应用程序无需自定义分片或分区
 *  支持Memory不支持的可变长度数据类型（Blob Text）

# MERGE #

Merge存储引擎，也称作MRG\_MyISAM引擎，特点是一组相同结构的MyISAM表可以用作一个Merge表。

结构相同包括列数据类型相同，索引相同，列顺序相同。  
但是列和索引的名称可以不同，表、列、索引的注释可以相同。

Merge表的替代方案是分区表，将单个表分区，存储在单独文件中。分区可以使某些操作有效执行，并不限于MyISAM引擎。

创建Merge表时创建.frm文件和.MRG文件，.frm文件存储表格式，.MRG存储基础MyISAM表的信息

可以在merge表上执行INSERT UPDATE DELETE SELECT来操作MyISAM多个表的数据，但是映射到Merge表的MyISAM表不许有SELECT UPDATE INSERT DELETE权限

使用 Drop table删除表后，只会删除Merge表，MyISAM表不会受到影响

# FEDERATED #

FEDERATED存储引擎使您可以从远程MySQL数据库访问数据，而无需使用复制或群集技术。 查询本地FEDERATED表会自动从远程表中提取数据。 没有数据存储在本地表上。  
如表创建语句如下：

CREATE TABLE federated_table (
        id     INT(20) NOT NULL AUTO_INCREMENT,
        name   VARCHAR(32) NOT NULL DEFAULT '',
        other  INT(20) NOT NULL DEFAULT '0',
        PRIMARY KEY  (id),
        INDEX name (name),
        INDEX other_key (other)
    )
    ENGINE=FEDERATED
    DEFAULT CHARSET=latin1
    CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

（完）（^\_^）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWhhb18_size_16_color_FFFFFF_t_70]: /images/20221119/dc41bd3dd5e54a079e39f9ad27852723.png