MySQL 体系结构-存储引擎

存储引擎概念：用来处理数据库相关的 CRUD 操作。

• 可以通过 SHOW ENGINES; 命令查看当前 MySQL 支持的存储引擎。InnoDB 是 MySQL 的默认存储引擎，创建表的时候如果不指定存储引擎，默认使用 InnoDB。

• 启动时可以禁用某些存储引擎。

推荐使用 InnoDB 存储引擎，其他存储引擎已经不在维护和开发，存在很多问题。

CSV 存储引擎

数据存储以 CSV 文件。

特点：

• CSV 是一种表准文件格式（CSV 文件），文件以存文本形式存储表格数据，不支持特殊字符。
• CSV 数据的存储用逗号隔开，可直接编辑 CSV 文件进行数据的编排，数据安全性低。编辑之后，要生效使用 flush table XXX 命令。
• 不能定义索引、列定义必须为 NOT NULL、不能设置自增列， 不适用大表或者数据的在线处理。

应用场景：

• 数据的快速导出导入。
• 表格直接转换成 表格直接转换成 CSV。

Archive 存储引擎

压缩协议进行数据的存储，数据存储为 ARZ 文件格式。

特点：

• 只支持 insert 和 select 两种操作。
• 只允许自增 ID 列建立索引。
• 行级锁。
• 不支持事务。
• 数据占用磁盘少。

应用场景：

• 日志系统。
• 大量的设备数据采集。

Memory 存储引擎

数据都是存储在内存中，IO 效率要比其他引擎高很多，服务重启数据会丢失，内存数据表默认只有 16M，一般我们不会使用到 Memory 存储引擎。

千万不要用 Memory 存储引擎去做缓存， 性能上不及 Redis 和 Memcahced。

特点：

• 支持 hash 索引，B tree 索引，默认 hash（查找复杂度 0(1)），但仅支持等值查询。
• 字段长度都是固定长度 varchar(32)=char(32)。
• 不支持大数据存储类型字段如 blog，text。
• 表级锁。
• 不支持事务。
• Memory 引擎不能禁用，当涉及内部排序操作的临时表时，使用该存储引擎 。
  • max_heap_table_size 决定使用内存的大小，默认是 16M。无论该表使用的什么引擎，只要使用到临时表，或者指定 Memory，都受该参数影响。
  • 当上面设置的内存放不下数据时，MySQL 版本 >= 5.6 的转为 MyISAM 引擎，>=5.7 的转为 InnoDB 引擎。注意磁盘上临时路径空间的大小（tmpdir）。
  • 内存使用为会话（SESSION）级别，当心内核 OOM。

mysql> show global status like "%tmp%tables";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |   -- 内存放不下，转成磁盘存储的数量,如果过大，考虑增大内存参数
| Created_tmp_tables      | 4     |   -- 创建临时表的数量
+-------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |  -- memory转成磁盘存储的路径
+---------------+-------+
1 row in set (0.00 sec)

应用场景：

• 等值查找热度较高数据。
• 在排序、分组等操作中，当数据量小于16M（默认大小），由查询优化器建立的临时表就是 Memory 类型。

MyISAM 存储引擎

MySQL5.5 版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到 MyISAM 存储引擎。

特点：

• 堆表数据结构。
• 表锁设计。
• 支持数据静态压缩。
• 不支持事务。
• 数据容易丢失。
• 索引容易损坏，不保证数据文件和索引文件同步更新，所以数据库一些异常宕机、磁盘满了等情况都可能造成数据文件和索引文件数据不一致。
• select count(*) from table 无需进行数据的扫描。
• 数据（MYD）和索引（MYI）分开存储。
• 唯一的优点是：数据文件可以直接拷贝到另一台服务器上使用。**

应用场景：

• 在排序、分组等操作中，当数量超过一定大小之后，由查询优化器建立的临时表就是 MyISAM 类型，后面这些表都会转为 InnoDB。
• MySQL 一些系统表在 5.6 5.7 版本中还是使用的 MyISAM 存储引擎，历史原因，后面些表都会转为 InnoDB
• 报表，数据仓库。

InnoDB 存储引擎

MySQL 5.5 及以后版本的默认存储引擎。

特点：

• 事务 ACID。
• 行级锁。
• 聚集索引（主键索引）方式进行数据存储。
• 支持外键关系保证数据完整性。

对比

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/ivm8gf 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。