文章目录[隐藏]
存储引擎
1. 概述
- 为了管理方便，人们把
连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能 - 把真实存取数据的功能划分为
存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的 API, 获取到数据后返回给客户端就好了 - MySQL 中提到了存储引擎的概念。简而言之，
存储引擎就是指表的类型。其实存储引擎以前叫做表处理器，后来改名为存储引擎，它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作
2. 查看存储引擎
- 查看 MySQL 提供什么存储引擎
show engines;
只有 InnoDB 支持事务、分布式事务（XA）、savepoints
3. 设置默认的存储引擎
- 查看默认的存储引擎
show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;
- 修改默认的存储引擎
如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：
SET DEFAULT_STORAGE_ENGINE = MyISAM;或者修改 my.cnf 文件：
default-storage-engine = MyISAM
# 重启服务
systemctl restart mysqld.service4. 设置表的存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式
4.1 创建表时指定存储引擎
我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB 。如果我们想显式的指定一下表的存储引擎，那可以这么写：
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;4.2 修改表的存储引擎
如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：
ALTER TABLE 表名 ENGINE = 存储引擎名称;比如我们修改一下 engine_demo_table 表的存储引擎：
ALTER TABLE engine_demo_table ENGINE = InnoDB;这时我们再查看一下 engine_demo_table 的表结构：
mysql> SHOW CREATE TABLE engine_demo_table\G
*************************** 1. row ***************************
Table: engine_demo_table
Create Table: CREATE TABLE engine_demo_table (
i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)5. 引擎介绍
5.1 InnoDB 引擎：具备外键功能的事务存储引擎
- MySQL 从 3.23.34a 开始就包含 InnoDB 存储引擎。
大于等于5.5之后，默认采用InnoDB引擎 - InnoDB 是 MySQL 的
默认事务型引擎，它被设计用来处理大量的短期 (short-lived) 事务。可以确保事务的完整提交 (Commit) 和回滚 (Rollback) - 除了增加和查询外，还需要更新、删除操作，那么，应优先选择 InnoDB 存储引擎
- 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎
- 数据文件结构：
- 表名.frm 存储表结构（MySQL8.0 时，在数据字典中）
- 表名.ibd 存储数据和索引
- InnoDB 是
为处理巨大数据量的最大性能设计- 在以前的版本中，数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了，比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在 MySQL8.0 中不存在了
- 对比 MyISAM 的存储引擎，
InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引 - MyISAM 只缓存索引，不缓存真实数据；InnoDB 不仅缓存索引还要缓存真实数据，
对内存要求较高，而且内存大小对性能有决定性的影响 支持行锁、事务、外键
5.2 MyISAM 引擎：非事务处理存储引擎
- MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数 (GIS) 等，但 MyISAM
不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复 5.5之前默认的存储引擎- 对事务完整性没有要求或者以
SELECT、INSERT为主的应用，优势是访问的速度快 - 针对数据统计有额外的常数存储。故而 count () 的查询效率很高
- 数据文件结构：
- 表名.frm (8.0 后.sdi) 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
- 应用场景：只读应用或者以读为主的业务
5.3 Memory 引擎：置于内存的表
Memory 采用的逻辑介质是内存 ，响应速度很快 ，但是当 mysqld 守护进程崩溃的时候数据会丢失 。另外，要求存储的数据是数据长度不变的格式，比如，Blob 和 Text 类型的数据不可用 (长度不固定的)
主要特征：
- Memory 同时
支持哈希（HASH）索引和B+树索引- 哈希索引相等的比较快，但是对于范围的比较慢很多
默认使用哈希(HASH)索引，其速度要比使用 B 型树 (BTREE) 索引快- 如果希望使用 B 树索引，可以在创建索引时选择使用
- Memory 表至少比 MyISAM 表要
快一个数量级 - MEMORY
表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows 可以在创建表时指定；max_heap_table_size 的大小默 认为 16MB，可以按需要进行扩大 - 数据文件与索引文件分开存储
- 每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为
frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的 - 这样有利于数据的快速处理，提供整个表的处理效率
- 每个基于 MEMORY 存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为
- 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择 MEMORY 存储引擎时需要特别小心
使用 Memory 存储引擎的场景：
目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制 Memory 表的大小，限制 Memory 表的最大的大小- 如果
数据是临时的，而且必须立即可用得到，那么就可以放在内存中 - 存储在 Memory 表中的数据如果突然间
丢失的话也没有太大的关系
5.4 对比
以下是 MyISAM、InnoDB 和 MEMORY 存储引擎的详细比较表：
| 特性/存储引擎 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 事务支持 | 不支持 | 支持 | 不支持 |
| 锁定机制 | 表级锁 | 行级锁、表级锁 | 表级锁 |
| 数据缓存 | 只缓存索引 | 缓存数据和索引 | 缓存数据和索引 |
| 外键支持 | 不支持 | 支持 | 不支持 |
| ACID 兼容 | 不兼容 | 兼容 | 不兼容 |
| MVCC | 不支持 | 支持 | 不支持 |
| 数据持久性 | 较弱 | 强 | 数据在重启后丢失 |
| 适用场景 | 读密集的应用 | 读写混合、需要事务支持的应用 | 临时数据存储、快速读写 |
| 优点 | 读取速度快、简单 | 数据完整性、并发性能好 | 读写速度极快 |
| 缺点 | 不支持事务、写性能一般 | 占用更多系统资源、写性能稍差于 MEMORY | 数据不持久、内存占用高 |
说明：
- MyISAM:
- 适用于读密集的应用，因为它的读取速度非常快
- 不支持事务和行级锁定，写操作可能会被阻塞
- 数据持久性较弱，可能不适合需要高数据安全性的应用
- InnoDB:
- 提供了事务支持、行级锁定和外键约束，适合需要这些特性的复杂应用
- 数据持久性和完整性较强，但可能会占用更多的系统资源
- 适合读写混合的应用，特别是需要事务支持的场景
- MEMORY:
- 所有数据都存储在内存中，所以读写速度非常快
- 适用于临时数据存储和需要快速读写的应用，但数据不是持久的
- 重启数据库或服务器后，所有数据都会丢失
站位