MySQL 存储引擎类问题

一、常用的MySQL存储引擎

存储引擎名称	是否支持事务	说明
Myisam	不支持	MySQL5.6之前的默认引擎，最常用的非事务型存储引擎
Csv	不支持	以csv格式存储的非事务型存储引擎
Archive	不支持	只允许查询新增，而不允许修改的非事务型存储引擎
Memory	不支持	数据存储在内存，读写速度快，易失性非事务存储引擎
InnoDB	支持	最常用的事务型存储引擎，MySQL5.6之后的默认引擎
NDB	支持	只支持读已提交隔离级别

说明：archive一般用于归档历史数据，或记录操作日志等场景，和它功能类似的还有tokuDB，只不过tokuDB需要额外安装，不是默认就提供的引擎。

对于事务的四种隔离级别，可以查看文章数据库架构-大表和大事务

二、Myisam存储引擎

1、Myisam的特点

（1）非事务型存储引擎

（2）以堆表方式存储

在磁盘存储上有三个文件，每个文件名以表名开头，.frm 用于存储表的定义，.MYD 用于存放数据，.MYI 用于存放表索引。

注：MySQL8.0中，frm表结构定义文件已经合并到数据字典中，因此在MySQL8.0中，myisam表是没有frm文件的。

非聚簇索引（叶子节点存指针）

（3）使用表级锁

读写操作之间会相互阻塞

（4）支持btree索引，空间索引，全文索引

2、修复Myisam数据表

Myisam数据表存在一个问题，经常会索引文件损坏，这时我们就可以使用 repair table table_name命令来修复数据表

# 创建数据表
CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

# 检查数据表
mysql> check table test;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test_80.test | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

# 修复数据表
mysql> repair table test;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test_80.test | repair | status   | OK       |
+--------------+--------+----------+----------+
1 row in set (0.00 sec)

3、压缩Myisam数据表

在数据文件所在目录执行压缩，-b参数是生成备份，-f参数是强制
root@72b7d0258d58:/var/lib/mysql/test_80# myisampack -b -f test
Compressing test.MYD: (5 records)
- Calculating statistics
- Compressing file
20%     
Remember to run myisamchk -rq on compressed tables

# 压缩之后生成了一个test.OLD文件，该文件是备份文件
root@72b7d0258d58:/var/lib/mysql/test_80# ls -lh
total 256K
-rw-r----- 1 mysql mysql 112K Aug 18 13:21 inventory.ibd
-rw-r----- 1 mysql mysql 128K Aug 18 02:16 t1.ibd
-rw-r----- 1 mysql mysql   70 Aug 19 01:17 test.MYD
-rw-r----- 1 mysql mysql 1.0K Aug 19 01:18 test.MYI
-rw-r----- 1 mysql mysql   60 Aug 19 01:17 test.OLD
-rw-r----- 1 mysql mysql 2.8K Aug 19 01:17 test_367.sdi

# 解压缩
root@72b7d0258d58:/var/lib/mysql/test_80# myisamchk --unpack test
- recovering (with sort) MyISAM-table 'test'
Data records: 5
- Fixing index 1
Wrong bytesec: 254-254-  8 at          0; Skipped
Data records: 0

注：在这个过程中，正常压缩之后就应该是只读了，但我压缩时只有第一遍变成了只读，剩下的几次都是可读可写，感觉压缩并不靠谱，慎用压缩功能。

4、Myisam使用场景

读操作远远大于写操作的场景（如果和innodb对比，最显著的区别是表锁和行级锁）

不需要使用事务的场景（innodb支持事务，myisam不支持事务，注定了myisam在大多业务场景中都不太适合）

三、CSV存储引擎

1、csv存储引擎的特点

（1）非事务型存储引擎

（2）数据以csv格式存储

（3）所有列都不能为null，否则建表会出现错误

（4）不支持索引

（5）csv的数据文件是可以直接查看的，每列的值由逗号分开，每行的值通过换行体现

（6）可以通过编辑csv表数据文件（.CSV格式文件）来修改数据表中的值

2、模拟csv数据文件是可以直接编辑的

# 新建csv数据表
CREATE TABLE `csv_test` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL
) ENGINE=CSV COMMENT='csv引擎测试表';

BEGIN;
INSERT INTO `csv_test` VALUES (1, 'haveyb', 'man');
INSERT INTO `csv_test` VALUES (2, 'xiaobudiu', 'man');
COMMIT;

# 到数据目录编辑该数据表文件
vi csv_test.CSV

1,"yangyang","man" 
2,"wangyuan","man" 

:wq

# 在MySQL命令行中执行， 查看值是否已经变化了

mysql> select * from csv_test;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | haveyb    | man |
|  2 | xiaobudiu | man |
+----+-----------+-----+
2 rows in set (0.01 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from csv_test;
+----+----------+-----+
| id | name     | sex |
+----+----------+-----+
|  1 | yangyang | man |
|  2 | wangyuan | man |
+----+----------+-----+
2 rows in set (0.00 sec)

结果证明，csv数据表是可以通过直接编辑表数据文件来修改数据表中的值的，新增或修改均可。

3、csv存储引擎适用场景

MySQL系统库中记录常规日志的general_log表，就是使用的CSV存储引擎。

当然，我们如果开启常规日志，一般也不会选择记录到数据表中，而是选择记录到文件里。

所以实际上csv引擎在实际应用场景中，并不多见。

四、Archive存储引擎

１、Archive存储引擎的特点

（1）非事务型存储引擎

（2）表数据使用zlib压缩

存储相同数据量的数据，Archive表相比于myisam表，存储空间占用大概会小75%，比innodb表小大概23%。

（3）只支持select和inset操作

只支持查询和新增，不支持删除和更新操作

（4）只允许在自增ID上建立索引

2、Archive存储引擎适用场景

（1）日志和数据采集类应用

（2）数据归档存储

因为相同数据量，Archive存储引擎占用磁盘空间相比其他存储引擎小很多，所以极其适合数据归档和日志记录操作，因为这两项一旦数据创建之后，都不需要更新和删除，所以极其适合使用该存储引擎。

五、Memory存储引擎

1、Memory存储引擎的特点

（1）非事务型存储引擎

（2）数据保存在内存中

因为数据存储在内存中，所以注定Memory存储引擎相对其他存储引擎，读写速度要快，但需要注意的是，MySQL一旦重启，存储的数据就会丢失，所以它是一种数据易失型存储引擎。

（3）所有字段的长度是固定的

即使创建的是varchar类型，实际上也是char类型，因此使用时要注意尽量不要建立超过使用需要大小的值，比如存储手机号，就char(11)，存储身份证，就char(18)，要不然会造成资源浪费

（4）不支持text、blob类型

同样处于节省空间的目的，Memory存储引擎是不支持大字段类型的

（5）支持btree和hash索引

2、Memory存储引擎的使用场景

（1）用于缓存字典映射表

注意，这里说的是缓存，也就是说，数据库中应该有一份同样的数据表，但这张数据表应该支持数据持久化的，比如myisam、innodb

（2）缓存周期型的分析数据

说明：实际应用中，并不建议使用Memory存储引擎，因为这种存储引擎并不支持数据持久化，太容易丢失了，所以为了加快查询速度，更建议使用Redis这种来做。

六、InnoDB存储引擎

1、InnoDB存储引擎的特点

（1）事务型引擎

（2）数据按主键聚集存储

InnoDB存储引擎，索引数据存储方式是聚簇索引（聚集索引），相比较而言，myisam存储引擎采用的是非聚簇索引（非聚集索引）。

注意，这里说的聚簇索引和非聚簇索引，代表的并不是一种索引，而是一种数据的存储方式。

详细可查看文章 MySQL中的聚簇索引和非聚簇索引

（3）支持行级锁

与myisam的表级锁不同，InnoDB支持行级锁，在读写操作时，只要在需要操作的数据行上加锁，而不会像myisam一样，在整个表上加锁。

在这点上，增加了数据的并发处理能力。

（4）支持MVCC

MVCC（Multi-Version Concurrency Control），多版本并发控制。

（5）支持Btree和自适应Hash索引

（6）支持全文和空间索引

5.6版本之后，开始支持全文索引；5.7版本后，开始支持空间索引。

2、InnoDB存储引擎的使用场景

大多数场景，基本所有业务，均可使用InnoDB存储引擎.

除非是一些特殊的需求，比如归档历史数据，可能考虑使用TokuDB或Archive，当然使用InnoDB也未尝不可。

七、NDB存储引擎

1、NDB存储引擎的特点

（1）事务型存储引擎

相比于InnoDB，二者都是事务型存储引擎，但不同之处在于：

InnoDB支持的事务隔离级别有：未提交读、已提交读、可重复读、串行化。

NDB支持的事务隔离级别只有已提交读。

（2）数据存储在内存中

相比于Memory存储引擎，二者数据都是存储在内存中，但不同之处在于：

Memory存储引擎在磁盘中只会保存表的定义，不会保存表中数据。

NDB存储引擎则会将表的定义及数据都会保存在磁盘中，只不过在使用时，会把所有数据都加载到内存中。

因此相比于Memory存储，使用NDB存储引擎时，不用担心MySQL的重启导致数据丢失，但由于NDB存储引擎要求将数据加载到内存中才可以使用，所以对服务器内存的大小要求会随着数据量的增加而不断增加。

说明：虽然在新版本的NDB集群中，也允许把一部分未被使用的数据存储在磁盘中，在使用的时候再加载到内存中，但是仍然存在很多限制，因此优化效果并不是很明显，同时也会影响NDB集群使用时的性能，这也是NDB集群得不到广泛使用的因素之一。

（3）支持行级锁

虽然和InnoDB存储引擎一样都支持行级锁，但性能和InnoDB相比要差一些。

（4）支持Tree索引

2、NDB存储引擎的使用场景

需要数据完全同步的高可用场景。其自由一种NDB集群。

但事实上，很少会有公司去使用它，目前在数据同步上，很多公司采用的都是 Replication集群 + PXC集群结合的方式。

Replication集群可以保证数据最终一致性， PXC集群可以保证数据强一致性。

八、存储引擎类常见问题

1、如何更安全的在线修改表结构

使用percona工具中的pt-online-schema-change。

该工具会新建一个表，该表的表结构是我们修改后的表结构，然后把数据复制过来，在数据复制成功之后，再把数据表重命名，就完成了在线修改表结构。

在整个过程中只有在对表进行重命名时，会短暂的对原表进行锁定，而在拷贝数据时，并不会对原表进行锁定，不会影响原表的读写操作。而且是分批拷贝，也不会对主从复制造成太大延迟。

详见文章 pt-online-schema-change工具的使用

2、InnoDB的事务是如何实现的

事务的实现是通过两种日志来完成的，分别是undo.log（回滚日志），redo.log（重做日志）。

当开始事务后，每执行一条sql语句，都会对应一条相反的sql语句（insert 对应 delete，update 对应相反 update）写入 undo.log（回滚日志中）。

当事务执行完毕后，会写入一个checkpoint（检查点）到 undo.log 日志中，MySQL 在下次执行事务或者重启MySQL，只会检查最近的checkpoint后面的内容，checkpoint 之前的说明已经执行成功不需要回滚了。

如果执行过程中，服务器突然挂掉，也就是说，执行了部分sql，不确定提没提交，那么这个时候，重启MySQL 时，MySQL 会检查 undo.log，如果最近的检查点后面有内容，则说明有部分sql执行了，但是没有提交，这时，MySQL 会对数据进行回滚。

并执行redo.log内的SQL，进行重做。

打赏赞