MySQL高级——引擎

引擎介绍

InnoDB引擎：具备外键支持功能的事务存储引擎

• MySQL从3.23.34a开始就包含InnoDB存储引擎，>=5.5之后默认采用InnoDB引擎
• 默认事务型引擎，被设计用来处理大量的短期事务，确保事务的完整提交和回滚
• 除了增加、查询外，若还需更新、删除，那么优先选择InnoDB存储引擎
• 数据文件结构
  • 表名.frm存储表结果（MySQL8.0时，合并在表名.ibd中）
  • 表名.ibd存储数据和索引
• 为处理巨大数据量的最大性能而设计（在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了）
• 缓存索引和真实数据，对内存要求较高，内存大小对性能有绝对性影响

MyISAM引擎：主要的非事务处理引擎

• 5.5之前默认的存储引擎
• 提供大量特性，包括全文索引、压缩、空间函数等。但不支持事务、行级锁、外键，崩溃后无法安全恢复
• 访问速度快，对事务完整没有要求或者以SELECT、INSERT为主的应用
• 针对数据统计有额外的常数存储
• 数据文件结构
  • 表名.frm 存储表结构
  • 表名.MYD 存储数据 (MYData)
  • 表名.MYI 存储索引 (MYIndex)

Archive引擎：用于数据存档

特征	支持
压缩数据	支持
备份/时间点恢复（在服务器中实现，而不是在存储引擎中）	支持
地理空间数据类型	支持
加密数据（在服务器中实现）	支持
更新数据字典的统计信息	支持
锁粒度	行锁
数据缓存	不支持
外键	不支持
全文检索索引	不支持
聚集索引	不支持
地理空间索引	不支持
哈希索引	不支持
索引缓存	不支持
B树索引	不支持
MVCC	不支持
存储限制	无限制
交易	不支持
集群数据库	不支持

Blackhole引擎：丢弃写操作，读操作会返回空内容

CSV引擎：存储数据时，以逗号分隔各个数据项

Memory引擎：置于内存的表

Memory采用的逻辑介质是内存，响应速度快。当mysqld守护进程崩溃时数据会丢失，另外要求存储的数据是数据长度不变的格式。比如，Blob和Text类型的数据不可用（长度不固定）

特征：

• 同时支持哈希索引、B+树索引
• 比MyISAM快一个数量级
• 表的大小主要取决于两个参数，max_rows（创建表时指定）、max_heap_table_size（默认16MB）
• 数据文件和索引文件分开存储
• 数据易丢失，生命周期短

使用场景：

• 目标数据较少，频繁的进行访问。在内存中存放数据，若数据太大会造成内存溢出
• 数据是临时的，必须立即可用
• 存储在Memory表中的数据突然间丢失也没有太大关系

Federated存储引擎：访问远程表

访问其他MySQL服务器的一个代理 ，尽管该引擎看起来提供了一种很好的跨服务器的灵活性 ，但也经常带来问题，因此默认是禁用的

Merge引擎：管理多个MyISAM表构成的表集合

NDB引擎：MySQL集群专用存储引擎

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集 群

常用引擎对比

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全性		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	缓存索引，不缓存数据	缓存索引、数据	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

补充

InnoDB表的优势

• 操作便利、提高数据库的性能、维护成本低
• 由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作，InnoDB崩溃 恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行
• InnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程
• InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘
• 在不影响性能和可用性的情况下创建或删除索引
• 当处理大数据量时， InnoDB兼顾CPU，以达到最大性能

InnoDB和ACID模型

1、原子，主要涉及InnoDB事务，与MySQL相关的特性主要包括：

• 自动提交设置
• COMMIT语句
• ROLLBACK语句
• 操作INFORMATION_SCHEMA库中的表数据

2、一致性，主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性主要包括：

• InnoDB双写缓存
• InnoDB崩溃恢复

3、隔离，应用于事务的级别，与MySQL相关的特性主要包括：

• 自动提交设置
• SET ISOLATION LEVEL语句
• InnoDB锁的低级别信息

4、耐久性， ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样 化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：

• InnoDB双写缓存，通过innodb_doublewrite配置项配置
• 配置项innodb_flush_log_at_trx_commit
• 配置项sync_binlog
• 配置项innodb_file_per_table
• 存储设备的写入缓存
• 存储设备的备用电池缓存
• 运行MySQL的操作系统
• 持续的电力供应
• 备份策略
• 对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况

InnoDB架构

• 缓冲池：主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的 数据能够直接在内存中获得，从而提高速度
• 更改缓存：一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索 引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并
• 自适应哈希索引：将负载和足够的内存结合，使得InnoDB像内存数据库一样运行， 不需要降低事务上的性能或可靠性
• 重做日志缓存：存放要放入重做日志的数据，定期地将日志文件刷入磁盘，大型的重做日志缓存 使得大型事务能够正常运行而不需要写入磁盘
• 系统表空间：包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引 数据。多表共享，系统表空间被视为共享表空间
• 双写缓存：位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存 后，InnoDB才会将数据页写入合适的位置
• 撤销日志：一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改
• 每个表一个文件的表空间：每个单独的表空间创建在自身的数据文件中， 而不是系统表空间中，每个表空间由一个单独的.ibd数 据文件代表，该文件默认被创建在数据库目录中
• 通用表空间： 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数 据目录之外能够管理多个表并支持所有行格式的表
• 撤销表空间：由一个或多个包含撤销日志的文件组成
• 临时表空间：用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间
• 重做日志：基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间， 重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自 动在初始化期间重新进行