Mysql - 存储引擎

---

👀常见的存储引擎（物理设计）

---

1️⃣ 总体概述

---

名称(发音)	作用	使用场景	优点	缺点
🛡️ InnoDB (因诺DB)	提供事务安全的存储引擎	通用应用，需要事务支持的应用	- 事务安全（ACID兼容） - 支持行级锁定 - 支持外键 - 提供崩溃恢复功能	可能相对于其他存储引擎使用更多的系统资源
📚 MyISAM (麦艾森)	默认的存储引擎，不支持事务	读密集型应用，不需要事务支持的应用	- 较高的查询性能 - 全文搜索能力 - 较小的磁盘和内存开销	不支持事务 - 表级锁定，可能导致并发问题
💾 Memory (梅莫瑞)	将所有数据存储在内存中	临时表，需要快速访问的数据	- 极高的查询和插入速度 - 数据在服务器重启或崩溃后丢失，因此适用于临时数据	数据不是持久的 - 有内存大小的限制
📦 Archive (阿奇夫)	提供压缩能力，用于存储和检索大量的归档数据	日志存储，归档数据	- 高效的存储空间使用 - 插入速度快 - 提供行级锁定	不支持索引 - 只支持插入和查询操作
🕳️ Blackhole (布莱克霍尔)	丢弃写入的数据，但在外部看起来就像正常的存储引擎	日志和审计	- 可以用于复制和日志 - 不占用存储空间	实际上不存储任何数据
🌐 NDB (恩DB)	集群存储引擎，提供高可用性和冗余	MySQL Cluster 分布式数据库系统	- 高可用性 - 数据冗余 - 支持实时查询	配置和管理相对复杂
📑 CSV (CSV)	将数据存储为逗号分隔的文本文件	数据导入/导出，与其他系统的集成	- 数据以纯文本格式存储 - 可以与其他文本处理工具和系统轻松集成	不支持索引 - 不提供数据完整性检查

---

2️⃣ InnoDB 特性

---

1. 🔒 事务与并发性:

   • 事务支持: 提供完全的 ACID 事务支持。
   • 多版本并发控制 (MVCC): 允许多事务同时读取同一个数据项，不相互阻塞。
   • 自动崩溃恢复: 系统或数据库崩溃后能自动恢复到一致的状态。

2. 🔐 锁与索引:

   • 锁策略: 支持行级锁定和间歇锁策略，防止幻读。
   • 聚簇索引: 表基于聚簇索引建立。二级索引中必须包含主键列。
   • B+ 树索引: 使用 B+ 树作为主索引结构。
   • 全文搜索索引: 支持全文搜索，提高文本搜索效率。

3. 💽 数据存储与组织:

   • 数据存储: 数据保存在由多个数据文件组成的表空间中。
   • 存储格式: 新格式支持数据压缩。
   • BLOB 存储: 更高效的 BLOB 存储方式。
   • 外键支持: 支持外键和级联操作，维护数据引用完整性。

4. 🚀 性能与优化:

   • 自适应哈希索引: 自动在内存中创建哈希索引。
   • 插入缓冲区: 专为加速插入操作设计。
   • 索引创建优化: 利用排序加速索引创建。
   • 缓存机制: 有 InnoDB buffer pool 缓存，缓存数据和索引。

5. 🔧 备份与恢复:

   • 热备份: 支持如 MySQL Enterprise Backup 和 XtraBackup 的热备份。

---

3️⃣ MyISAM 特性

---

1. 🔒事务与锁定: MyISAM 不支持事务处理，并采用表级锁定。但允许在读取时并发插入新记录。
2. 💽数据存储与恢复: 数据保存在 .MYD 文件，索引在 .MYI 文件中。崩溃后可能需要修复，但无法保证数据完整性。
3. 🔍索引特性:
   • 提供基于分词的全文索引。
   • 支持为 BLOB 和 TEXT 等长字段的前 500 个字符创建索引。
   • 创建 MyISAM 表时，如果指定了DELAY_KEY_WRITE 选项，每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，先写入内存中的键缓冲区（in-memory key buffer），只有在清理键缓冲区或则关闭表的时候才会将对应的索引块写入到磁盘。 DELAY_KEY_WRITE 选项增强写入性能，但增加崩溃风险
4. 🗜️数据压缩与存储:
   • 提供数据压缩功能，可以使用 myisampack 进行。压缩后的表变为只读。
   • 数据以紧密格式存储，设计简单，某些场景性能优越。
5. 🌍其他特性:
   • 支持地理信息系统 (GIS) 功能。
   • 可调整表指针长度，通过 MAX_ROWS 和 AVG_ROW_LENGTH 选项。
   • 提供表修复功能，但可能导致数据丢失
     • CHECK TABLE mytable 检查表的错误
     • PERAIR TABLE mytable 修复错误

---

4️⃣ InnoDB vs MyISAM

---

📌事务与并发性

特性	InnoDB	MyISAM
事务支持	支持	不支持
多版本并发控制	支持	不支持
并发插入	不支持	支持

📌锁机制与并发控制

特性	InnoDB	MyISAM
锁定机制	行级锁定	表级锁定

📌数据存储与恢复

特性	InnoDB	MyISAM
数据存储	表空间	.MYD (数据), .MYI (索引)
崩溃恢复	自动恢复	手动修复 (可能数据丢失)

📌索引与搜索

特性	InnoDB	MyISAM
全文索引	较新版本支持	支持
BLOB/TEXT 字段索引	支持	支持前 500 个字符

📌特性与优化

特性	InnoDB	MyISAM
外键支持	支持	不支持
空间函数 (GIS)	较新版本支持	支持
表压缩	支持	支持
延迟更新索引键	不支持	支持
数据和索引缓存	缓存数据和索引	只缓存索引

📌完整性与安全性

特性	InnoDB	MyISAM
数据的完整性和引用完整性	通过事务和外键保证	无此保证

📌表尺寸与限制

特性	InnoDB	MyISAM
表存储的尺寸限制	主要受表空间大小限制	由 .MYD 和 .MYI 文件大小限制

---

5️⃣ 如何选择存储引擎

---

📌选择合适的存储引擎

• 默认选择: 除非有特殊需求，否则推荐使用 InnoDB，因其提供事务支持、行级锁定和崩溃恢复。
• 复杂性警告: ⚠️尽量不要在同一个数据库中混合使用多种存储引擎，以避免潜在的问题和管理难题。

---

📌根据应用场景选择

1. 📝日志型应用: 如果主要是插入数据，并且查询需求不高，如日志记录，考虑MyISAM或Archive。
2. 🔍只读表: 如果表的数据更多是用于查询而非频繁更新，而且可以接受MyISAM的恢复风险，那么MyISAM可能是合适的。
3. 🔒事务处理: 如果业务需要事务支持，如订单处理，选择InnoDB。
4. 👥社交应用: 如社区论坛，需要根据实际读写比例选择。
5. 📀只读媒体应用: 基于CD-ROM或DVD的应用，可使用MyISAM或其压缩表格式。
6. 🌍大数据量: 数据量巨大时，除InnoDB外，可以考虑如TokuDB这样的专门解决方案。

---

6️⃣ 转换表的引擎

---

📌 直接更改 (ALTER TABLE)

• 通过ALTER TABLE命令，你可以直接更改存储引擎：

  ALTER TABLE mytable ENGINE = InnoDB;
  

• 注意: ⏰这个方法可能需要执行很长时间，因为MySQL会从原表复制数据到新表，并在此过程中锁定原表。

---

📌 导出与导入

• 使用mysqldump工具导出数据，然后修改文件中的CREATE TABLE语句：
  • 导出数据。
  • 修改文件中的存储引擎选项和表名。
  • 删除文件中的DROP TABLE语句（mysqldump默认加上的）。
  • 导入修改后的数据。

---

📌 创建与查询 (CREATE 和 SELECT)

• 创建一个新的表，使用不同的存储引擎，然后将数据从旧表复制到新表：

  CREATE TABLE innodb_table LIKE myisam_table;
  ALTER TABLE innodb_table ENGINE=InnoDB;
  INSERT INTO innodb_table SELECT * FROM myisam_table;
  

• 针对大数据量: 你可以使用事务分批复制数据。假设有主键字段id：

  START TRANSACTION;
  INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
  COMMIT;
  

---

📌 使用工具

• Percona Toolkit提供了一个名为pt-online-schema-change的工具，可以自动化上述过程，降低手动操作的风险。

• 注意: 🚫转换存储引擎会失去与原引擎相关的特性。例如，将InnoDB表转为MyISAM，然后再转回InnoDB，原来的外键会丢失。

---