MYSQL-存储引擎

查询存户引擎

1. 查看 MYSQL 提供的存储引擎命令：

show engines;

Engine            |Support|Comment                                                       |Transactions|XA |Savepoints|
------------------+-------+--------------------------------------------------------------+------------+---+----------+
MEMORY            |YES    |Hash based, stored in memory, useful for temporary tables     |NO          |NO |NO        |
MRG_MYISAM        |YES    |Collection of identical MyISAM tables                         |NO          |NO |NO        |
CSV               |YES    |CSV storage engine                                            |NO          |NO |NO        |
FEDERATED         |NO     |Federated MySQL storage engine                                |            |   |          |
PERFORMANCE_SCHEMA|YES    |Performance Schema                                            |NO          |NO |NO        |
MyISAM            |YES    |MyISAM storage engine                                         |NO          |NO |NO        |
InnoDB            |DEFAULT|Supports transactions, row-level locking, and foreign keys    |YES         |YES|YES       |
ndbinfo           |NO     |MySQL Cluster system information storage engine               |            |   |          |
BLACKHOLE         |YES    |/dev/null storage engine (anything you write to it disappears)|NO          |NO |NO        |
ARCHIVE           |YES    |Archive storage engine                                        |NO          |NO |NO        |
ndbcluster        |NO     |Clustered, fault-tolerant tables                              |            |   |          |

存储引擎	特点	适用场景
InnoDB	支持事务、行级锁定、外键约束、MVCC（多版本并发控制）	需要事务支持和高并发访问的OLTP（在线事务处理）系统
MyISAM	表级锁定、支持全文索引、读取速度快、不支持事务和外键约束	读操作远多于写操作的OLAP（在线分析处理）系统或小型应用
Memory	数据存储在内存中，读取速度极快、支持表级锁定、不支持事务和外键约束、数据易丢失	需要快速访问临时数据或缓存数据的场景
CSV	以CSV格式存储数据、不支持索引、适合数据交换	数据导入导出、数据交换
Archive	高效地存储和检索大量很少被访问的数据、不支持索引、支持压缩	日志记录和大量历史数据的存储
Blackhole	不存储任何数据，所有插入操作都会被丢弃，但会记录二进制日志	用于复制数据到其他数据库而不实际存储数据，或用于测试

---

设置系统默认的存储引擎

1. 查看默认的存储引擎

show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;

// output
Variable_name                  |Value    |
-------------------------------+---------+
default_storage_engine         |InnoDB   |
default_tmp_storage_engine     |InnoDB   |
disabled_storage_engines       |         |
internal_tmp_mem_storage_engine|TempTable|

修改默认存储引擎

如果在创建表的语句中没有显示指定表的存储引擎，MYSQL 默认使用 InnoDB 作为表的存储引擎。
用户可以使用如下命令修改默认存存储引擎

SET DEFAULT_STORAGE_ENGINE = <ENGINE NAME>
//
SET DEFAULT_STORAGE_ENGINE = MyISAM

或者修改 my.cnf 配置文件

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service

---

设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表是指定存储引擎

用户在创建表的语句如果没有指定存储引擎，MYSQL 默认使用 InnoDB。
用户可以使用 ENGINE 关键字指定表的存储引擎：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE = InnoDB;

修改表的存储引擎

用户可以使用如下命令修改现存表的存储引擎：

ALTER TABLE my_table ENGINE = MyISAM;

// 再查看表存储引擎
SHOW CREATE TABLE my_table;

---

引擎介绍

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

1. InnoDB 是 MySQL 的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
2. 支持事务、行级锁定、外键约束、MVCC（多版本并发控制）
3. 除了增加和查询外，还需要更新、删除操作，那么，应优先选择 InnoDB 存储引擎。
4. 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。
5. 数据文件结构：（在《第02章_MySQL 数据目录》章节已讲）
   1. 表名.frm 存储表结构（MySQL8.0 时，合并在表名 .ibd 中）
   2. 表名.ibd 存储数据和索引
6. InnoDB 是 为处理巨大数据量的最大性能设计 。
7. 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如：.frm，.par，.trn， .isl，.db.opt 等都在 MySQL8.0 中不存在了。
8. 对比 MyISAM 的存储引擎， InnoDB 写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引。
9. MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高，而且内存大小对性能有决定性的影响。

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

1. MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS)、表级锁等，但 MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是崩溃后无法安全恢复 。
2. 5.5 之前默认的存储引擎是 MyISAM。
3. 优势是访问的速度快 ，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用
4. 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
5. 数据文件结构：
   1. 表名.frm 存储表结构
   2. 表名.MYD 存储数据 (MYData)
   3. 表名.MYI 存储索引 (MYIndex)

应用场景：只读应用或者以读为主的业务

Archive 引擎：用于数据存档

Archive 存储引擎是 MySQL 中专门用于数据存档的存储引擎。它非常适合存储大量不经常访问的历史数据，并且可以高效地进行数据的插入和压缩。Archive引擎的特点如下：

1. 高压缩比：Archive 引擎对存储的数据进行高压缩，可以显著减少磁盘空间的使用。
2. 快速插入：由于优化了数据的插入操作，Archive引擎非常适合大量数据的插入操作。
3. 不支持索引：Archive 表不支持索引，因此不适合需要频繁查询或更新数据的应用场景。
4. 只支持插入和查询操作：Archive 表不支持删除和更新操作，这意味着数据一旦插入就无法修改或删除。
5. 支持行级锁：尽管不支持索引，但 Archive 引擎支持行级锁，可以在多线程插入数据时提供更好的并发性能。

由于Archive引擎的设计目的是为了高效地存储和检索大量历史数据，因此它通常用于日志记录、数据仓库和历史数据存档等场景，特别是在数据不需要经常修改的情况下。

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

CSV 存储引擎是 MySQL 中用于以 CSV（逗号分隔值）格式存储数据的存储引擎。这种存储引擎的特点如下：

1. CSV格式：数据以 CSV 格式存储，每一行代表一条记录，字段之间使用逗号分隔。
2. 不支持索引：CSV 引擎不支持索引，因此不适合需要快速查询或频繁进行数据检索的场景。
3. 只读模式：CSV 表通常是只读的，虽然可以插入数据，但更新和删除操作可能会受到限制。
4. 文本存储：数据以文本形式存储，这使得 CSV 文件可以直接被外部应用程序（如电子表格软件）读取和编辑。
5. 用于数据交换：CSV 引擎非常适合于数据的导入和导出，以及不同数据库或系统之间的数据交换。

由于 CSV 引擎的这些特点，它通常用于数据迁移、数据备份、数据交换或需要将 MySQL 数据与外部系统集成的场景。