11. MySQL存储引擎
mysql的数据库文件都存储在/var/lib/mysql/目录下。
1. 查看引擎⚓
执行SHOW ENGINES;查看所有的引擎。
MariaDB [blog]> 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 |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
MySQL 5.5.5之前默认的存储引擎是MyISAM，后续是InnoDB。
2. MyISAM⚓
MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访问速度快。
2.1 存储方式⚓
每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：
- .frm（存储表定义）；
- .MYD（MYData，存储数据）；
- .MYI （MYIndex，存储索引）。
数据文件和索引文件可以放置在不 同的目录，平均分布 IO ，获得更快的速度。
要指定索引文件和数据文件的路径， 需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定。文件路径需要是绝对路径，并且具有访问权限。
ALTER TABLE TEST DATA DIRECTORY='/tmp/test/', INDEX DIRECTORY='/tmp/test/';
实际测试会发出参数无效的警告（暂时没找到原因，TODO）：
+---------+------+----------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
MyISAM 类型的表可能会损坏，会提示需要修复或者访问后返回错误的结果。
2.2 存储格式⚓
MyISAM 的表又支持 3 种不同的存储格式，分别是：
- 静态（固定长度）表，默认格式。字段都是非变长字段。优点是存储非常迅速， 容易缓存， 出现故障容易恢复； 缺点是占用的空间通常比动态表多。 静态表的数据在存储的时候会按照列的宽度定义补足空格， 但
是在应用访问的时候并会将尾部的空格全部删除。
CHAR和VARCHAR可用，但是space-padded， 类型不可变（实际测试可变？？？）。 - 动态表。动态表中包含变长字段， 记录不是固定长度的， 这样存储的优点是占用的空间相对较少， 但 是频繁地更新删除记录会产生碎片（可执行命令改善），并且出现故障的时候恢复相对比较困难。
- 压缩表。压缩表是只读的，由
myisampack工具创建，占据非常小的磁盘空间。访问开支非常小。
前两种会根据字段类型自动选择。
3. InnoDB⚓
InnoDB 存储引擎提供了具有提交、 回滚和崩溃恢复能力的事务安全。但是相对于MyISAM效率差并且空间占用率高。
3.1 自动增长列⚓
InnoDB 表的自动增长列可以手工插入， 但是插入的值如果是空、NULL或者 0， 则实际插入的将是自动增长后的值。
可以通过 ALTER TABLE *** AUTO_INCREMENT = n; 语句强制设置自动增长列的初识值，默认是1。但是如果未使用该值并重启了数据库则需要重新设置。
3.2 外键约束⚓
MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
在创建索引的时候， 可以指定在删除、 更新父表时， 对子表进行的相应操作， 包 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
- RESTRICT 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新
- CASCADE 表示父表在更新或者删除时， 更新或者删除子表对应记录
- SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL
暂时关闭/打开外键约束：SET FOREIGN_KEY_CHECKS = 0;/SET FOREIGN_KEY_CHECKS = 1;
3.3 存储方式⚓
InnoDB 存储表和索引有以下两种方式。
3.3.1 共享表空间存储⚓
这种方式创建的表的表结构保存在.frm 文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
3.3.2 多表空间存储⚓
这种方式创建的表的表结构仍然保存在.frm 文件中， 但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件， 文件名是 “表名+分区名” ， 可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上。
使用此种存储方式，需要设置参数 innodb_file_per_table，并重新启动服务后才可以生效。默认打开的
MariaDB [MYISAM_TEST]> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
注意：当你修改了存储方式后，只对新建的表生效，原来的表仍然保持原先的存储方式及访问方式。
多表空间的数据文件没有大小限制， 不需要设置初始大小， 也不需要设置文件的最大限制、扩展大小等参数。
如果要将单表恢复到目标数据库，则需要通过 mysqldump和 mysqlimport来实现。
注意：即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 把内部数据词典和在线重做日志放在这个文件中。
4. MEMORY⚓
使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。
存储方式：访问速度非常快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失。
适用场景：MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。
MAX_ROWS：这不是一个硬性限制，只是提示存储引擎至少能够存储这么多行。
MIN_ROWS：计划在表中存储的最少行数。MEMORY 将这个选项看作内存使用的提示。
5. MRG_MyISAM⚓
MRG_MyISAM 存储引擎是一组 MyISAM 表的组合， 这些 MyISAM 表结构必须完全相同， MRG_MyISAM表本身并没有数据。
对 MRG_MyISAM 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。
对于 MRG_MyISAM 类型表的插入操作 ，是通过INSERT_METHOD 子句定义插入的表，可以有 3 个不同的值，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为 NO，表示不能对这个 MRG_MyISAM 表执行插入操作。
可以对 MRG_MyISAM 表进行 DROP 操作， 这个操作只是删除 MRG_MyISAM 的定义， 对内部的表没有任何的影响。
存储方式：MRG_MyISAM 表在磁盘上保留两个文件， 文件名以表的名字开始， 一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息， 包括 MRG_MyISAM 表由哪些表组成、 插入新的数据时的依据。可以通过修改.MRG 文件来修改 MRG_MyISAM 表，但是修改后要通过 FLUSH TABLES 刷新。
# 创建MyISAM表
MariaDB [MYISAM_TEST]> SHOW CREATE TABLE TEST \G;
*************************** 1. row ***************************
Table: TEST
Create Table: CREATE TABLE `TEST` (
`NAME` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
# 创建MyISAM表
MariaDB [MYISAM_TEST]> SHOW CREATE TABLE TEST2 \G;
*************************** 1. row ***************************
Table: TEST2
Create Table: CREATE TABLE `TEST2` (
`NAME` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
# 创建MRG_MyISAM表
MariaDB [MYISAM_TEST]> SHOW CREATE TABLE TEST3 \G;
*************************** 1. row ***************************
Table: TEST3
Create Table: CREATE TABLE `TEST3` (
`NAME` char(5) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`TEST`,`TEST2`)
# 向子表中插入数据
MariaDB [MYISAM_TEST]> INSERT INTO TEST VALUES('SHI');
MariaDB [MYISAM_TEST]> INSERT INTO TEST2 VALUES('SHI2');
# 在总表可以查询到所有的数据
MariaDB [MYISAM_TEST]> SELECT * FROM TEST3;
+------+
| NAME |
+------+
| SHI |
| SHI2 |
+------+
# 向总表中插入数据
MariaDB [MYISAM_TEST]> INSERT INTO TEST3 VALUES('SHI3');
# 检索总表数据
MariaDB [MYISAM_TEST]> SELECT * FROM TEST3;
+------+
| NAME |
+------+
| SHI |
| SHI2 |
| SHI3 |
+------+
# 实际存储在子表中
MariaDB [MYISAM_TEST]> SELECT * FROM TEST2;
+------+
| NAME |
+------+
| SHI2 |
| SHI3 |
+------+
这也是 MRG_MyISAM 表和分区表的区别， MRG_MyISAM 表并不能智能地将记录写到对应的表中，而分区表是可以的。
6. TukoDB⚓
第三方存储引擎。
- 高性能、支持事务
- 高扩展性、高压缩率、高效的写入性能
- 支持大多数在线DDL操作
适用场景：
- 日志数据
- 历史数据
- 在线DDL频繁的场景