MySQL 存储引擎

与其他数据库不同，MySQL有一个存储引擎的概念，根据不同的需求可以选择最优的存储引擎。

MySQL存储引擎概述

插件式存储引擎是MySQL最重要的特性之一，用户可以根据应用的需要选择如何存储、索引数据、是否使用事务等。MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以根据自己的需求定制和使用自己的存储引擎，以实现最大程度的可定制性。

MySQL5.7支持的存储引擎包括：InnoDB 、MyISAM、MEMORY、CSV、BLACKHOLE、ARCHIVE、MERGE(MRG_MyISAM)、FEDERATED、EXAMPLE、NDB 等，其中 InooDB 和NDB 提供事务安全表，其他存储引擎都是非事务安全表。

创建新表时如果不指定存储引擎，那么系统就会使用默认存储引擎，MySQL5.5 之前默认存储引擎是 MyISAM，5.5版本之后就改为了 InnoDB 。

修改默认存储引擎

如果要修改默认的存储引擎，可以在参数文件中设置 default_storage_engine，查看当前的默认存储引擎，可以使用如下命令：

mysql> show variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

可以通过以下方法查看当前数据库支持的存储引擎：

mysql> show engines G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

其中 Support 不同值的含义分别是：

DEFAULT 支持并启用，并且为默认引擎
YES 支持并启用
NO 不支持
DISABLED 支持，但数据库启动时被禁用

创建表时指定存储引擎

创建新表时可以通过 ENGINE 关键字设置新建表的存储引擎，例如在下面的例子中，ai表是存储引擎是MyISAM 而 countyry的存储引擎是 InnoDB:

mysql> CREATE TABLE ai (
    -> i bigint(20) not null AUTO_INCREMENT,
    -> PRIMARY KEY(i)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE country(
    -> country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> country VARCHAR(50) NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY(country_id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

修改已存在表的存储引擎

可以使用 ALTER TABLE 语句，将已经存在的表修改为其他存储引擎：

mysql> show create table ai G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table ai engine = innodb;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ai G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改后，表ai的存储引擎是InnoDB，可以使用InnoDB存储引擎的相关特性。

注意：修改表的存储引擎需要锁表并复制数据，对于线上环境的表进行这个操作非常危险，除非你非常了解可能造成的影响，否则在线上环境请考虑使用其他方式，例如：percona的 OSC工具

各存储引擎特性

重点介绍集中常用的存储引擎，并对比各个存储引擎之间的区别：

MySQL 常用存储引擎对比

特点	MyISAM	Memory	InnoDB	Archive	NDB
B树索引	支持	支持	支持	—	—
备份/时间点恢复	支持	支持	支持	支持	支持
支持集群	—	—	—	—	支持
聚簇索引	—	—	支持	—	—
数据压缩	支持	—	支持	支持	—
数据缓存	—	N/A	支持	—	支持
数据加密	支持	支持	支持	支持	支持
支持外键	—	—	支持	—	支持
全文索引	支持	—	支持	—	—
地理坐标数据类型	支持	—	支持	—	—
地理坐标索引	支持	—	支持	—	—
哈希索引	—	支持	—	—	支持
索引缓存	支持	N/A	支持	—	支持
锁粒度	表级	表级	行级	行级	行级
MVCC多版本控制	—	—	支持	—	—
支持复制	支持	有限支持	支持	支持	支持
存储限制	256TB	RAM	64TB	None	384EB
T树索引	—	—	—	—	支持
支持事务	—	—	支持	—	支持
统计信息	支持	支持	支持	支持	支持

下面重点介绍四种存储引擎：MyISAM、MEMORY、InnoDB、Archive。

MyISAM 引擎

MyISAM引擎是MySQL5.5之前版本默认的存储引擎。MyISAM既不支持事务，也不支持外键，在5.5之前的版本中，MyISAM在某些场景中相对InnoDB的访问速度有明显的优势，对事务完整性没有要求或者以 SELECT 或 INSERT 为主的应用都可以使用这个引擎来创建表，在MySQL5.6之后，MyISAM已经用的越来越少了。

每个MyISAM引擎的表在磁盘上存储成为三个文件，其文件名和表名相同，但扩展名不同：

.frm 存储表定义
.MYD MYData 存储数据
.MYI MYIndex 存储索引

数据文件和索引可以放在不同的目录，平均分布IO，以获取更快的速度。

要指定索引和数据文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，也就是说不同MyISAM表的索引文件和数据文件可以放在不同的路径下，文件路径需要是绝对路径，并且具有访问权限。

MyISAM类型的表有可能会损坏，原因可能是多种多样的，损坏后的表可能不能被访问，会提示需要修复或返回错误的结果。MyISAM类型的表提供修复的工具，可以用 CHECK TABLE 来检查 MyISAM 表的健康，并用 REPAIR TABLE 语句修复一个损坏的MyISAM表。表损坏可能会导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。

MyISAM的表还支持3种不同的存储格式，分别如下：

静态（固定长度）表
动态表
压缩表

压缩表

其中，静态表是默认的存储格式。静态表的字段都是非变长字段，每一个记录的长度都固定的。这个存储方式的特点的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在存储数据时会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回之前已经去掉了。需要注意的是，如果保存的内容后面本来就带有空格，那么返回结果的时候也会被去掉。

mysql> CREATE TABLE Myisam_char (name char(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Myisam_char values('abcde'),('abcde  '),('  abcde'),('  abcde  ');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT  name,length(name) FROM Myisam_char;
+---------+--------------+
| name    | length(name) |
+---------+--------------+
| abcde   |            5 |
| abcde   |            5 |
|   abcde |            7 |
|   abcde |            7 |
+---------+--------------+
4 rows in set (0.00 sec)

可以看到插入记录后面的空格被去掉了，而前面的空格被保留了。

动态表

动态表中包含变长字段，记录不是固定长度的。这样存储的优点是占用空间相对较少，但频繁的更新或删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk-r 命令来改善性能，并且在出现故障时恢复相对比较困难。

压缩表

压缩表由 myisampack 工具创建，占用非常小的磁盘空间。因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

InnoDB引擎

InnoDB 引擎是 MySQL5.5 之后的默认存储引擎，提供了具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小的锁粒度和更强的并发能力，拥有自己独立的缓存和日志，在 MySQL5.6 和 5.7 版本中性能有较大的提升。

相比较 MyISAM 引擎，InnoDB 会占用更多的磁盘空间以保留数据和索引。但在多数场景下，InnoDB 都是更好选择，也是为何MySQL将默认存储引擎改成 InnoDB 的原因，在更是在 MySQL8.0 中将所有的系统表也改为 InnoDB 存储引擎。

下面将介绍 IoonDB 存储引擎的表在使用过程中不同于其他存储引擎的特点，以及如何更好的使用 InnoDB 存储引擎。

自动增长列

InnoDB 表的自动增长列是可以手动插入的，但如果插入的值是空，则实际插入的值将是自动增长后的值。

下面定义表 autoincre_demo，其中列 i 使用自动增长列，对该表插入记录，然后查看自动增长列的处理情况：

mysql> CREATE TABLE autoincre_demo
    -> (i SMALLINT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(10), PRIMARY KEY(i)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO autoincre_demo VALUES(NULL,'1'),(2,'2'),(4,'3'),(NULL,'4');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 4 | 3    |
| 5 | 4    |
+---+------+
4 rows in set (0.00 sec)

可以通过ALTER TABLE [table_name] AUTO_INCREMENT = n; 来强制自动增长的初始值（也就是下次新增从那个数字开始，之后再次插入依然还是在上次的基础上+1）：

mysql> ALTER TABLE autoincre_demo AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO autoincre_demo VALUES(NULL,'5');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM autoincre_demo;
+----+------+
| i  | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  4 | 3    |
|  5 | 4    |
| 10 | 5    |
+----+------+
5 rows in set (0.00 sec)

注意： 在MySQL8.0 之前的版本中，对于InnoDB这个引擎，这个值是存储在内存当中的，如果数据库重启，这个值就会丢失，数据库会自动将 AUTO_INCREMENT 重置为当前存储的最大值+1，自增列记录的值和预期不一致，从而导致数据冲突。

在 MySQL8.0 中这个变量将保存到REDO LOG中，每一次计数器发生改变都会更新 REDO LOG。如果数据库发生重启，InnoDB会根据REDO LOG中的计数器信息来初始化其内存值。

可以使用 LAST_INSERT_ID() 来查询当前线程最后插入记录使用的值，如果插入多条记录，LAST_INSERT_ID() 只返回第一条数据的自动增长值，如果是手动指定了自动增长列的值，则 LAST_INSERT_ID() 的值不会更新：

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO autoincre_demo VALUES(1,'1');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO autoincre_demo(name) VALUES('2'),('3');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

表中没有数据时 LAST_INSERT_ID() 的值是 0
手动指定自动增长列时，LAST_INSERT_ID() 也是 0
当自动插入多条数据时，LAST_INSERT_ID() 的值是第一条数据的自增值

对于 InnoDB 表，自动增长列必须被索引，如果是组合索引，也必须是组合索引的第一列，但对于 MyISAM 表，自动增长列可以是组合索引的其他列看，这样插入记录后，自动增长列是按照组合索引前面几列进行排序后递增的。

例如：创建一个新的 MyISAM 表 myisam_demo ，自动增长列 d1 作为组合索引的第二列，对该表插入一些记录后，自动增长列是按照组合索引的第一列 d2 进行排序后递增的：

mysql> CREATE TABLE myisam_demo
    -> (d1 SMALLINT NOT NULL AUTO_INCREMENT,
    -> d2 SMALLINT NOT NULL,
    -> name VARCHAR(10),
    -> INDEX(d2,d1)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO myisam_demo(d2,name) VALUES(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM myisam_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

外键约束

MySQL 支持外键的常用存储引擎只有 InnoDB ，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

下面的样例数据库中有两张表，country 是父表，country_id 为主键索引，city 是子表，country_id 字段为外键，对应 country 表的主键 country_id。

mysql> CREATE TABLE country (
    -> country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> country VARCHAR(50) NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (country_id)
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE city(
    -> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> city VARCHAR(50) NOT NULL,
    -> country_id SMALLINT UNSIGNED NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY (city_id),
    -> KEY idx_fx_country_id (country_id),
    -> CONSTRAINT fa_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE
CASCADE
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

创建索引时，可以指定在删除、更新父表时对子表进行相应的操作，包括 RESTRICT 、CASCADE 、SET NULL、NO ACTION 。

其中 RESTRICT 和 NO ACTION 相同，作用是限制子表有关联记录时父表不能更新；
CASCADE 表示父表在更新和删除时，更新或删除子表对应记录。
SET NULL 表示父表在更新或删除的时候，子表对应的字段被 SET NULL

选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据丢失。

上面创建的表，子表外键指定的是：ON DELETE RESTRICT ON UPDATE CASCADE 方式，所以在主表删除记录的时候，如果子表有记录，则不允许删除；主表在更新记录时，如果子表有对应记录，则子表对应更新：

mysql> INSERT INTO country VALUES(1,afghanitan,'2020-12-10 05:15:34');
ERROR 1054 (42S22): Unknown column 'afghanitan' in 'field list'
mysql> INSERT INTO country VALUES(1,'afghanitan','2020-12-10 05:15:34');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO city VALUES(2,'kabul',1,'2020-12-13 12:00:12');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM country WHERE country_id = 1;
+------------+------------+---------------------+
| country_id | country    | last_update         |
+------------+------------+---------------------+
|          1 | afghanitan | 2020-12-10 05:15:34 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM city WHERE country_id = 1;
+---------+-------+------------+---------------------+
| city_id | city  | country_id | last_update         |
+---------+-------+------------+---------------------+
|       2 | kabul |          1 | 2020-12-13 12:00:12 |
+---------+-------+------------+---------------------+
1 row in set (0.00 sec)

-- 删除失败 因为子表有关联外键
mysql> DELETE FROM country where country_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fa_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

mysql> UPDATE country SET country_id = 200 WHERE country_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM country WHERE country = 'afghanitan';
+------------+------------+---------------------+
| country_id | country    | last_update         |
+------------+------------+---------------------+
|        200 | afghanitan | 2020-12-16 22:22:23 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

-- 父表更新后 子表的关联字段自动跟着更新
mysql> SELECT * FROM city WHERE city_id = 2;
+---------+-------+------------+---------------------+
| city_id | city  | country_id | last_update         |
+---------+-------+------------+---------------------+
|       2 | kabul |        200 | 2020-12-13 12:00:12 |
+---------+-------+------------+---------------------+
1 row in set (0.00 sec)

当某个表被其他表创建了外键参照，那么该表对应的索引或主键都禁止被删除。

当导入多个表的数据时，如果需要忽略之前的导入顺序，可以暂时关闭外键的检查；同样，在执行 LOAD DATE 和 ALTER TABLE 操作时，可以通过暂时关闭外键约束来加快执行的速度，执行完成后再开启：

关闭外键约束的命令是：SET FOREIGN_KEY_CHECKS = 0;

开启外键约束的命令是：SET FOREIGN_KEY_CHECKS = 1;

对应 InnoDB 类型的表，外键的信息可以通过 SHOW CREATE TABLE 或 SHOW TABLE STATUS 命令显示：

mysql> SHOW TABLE STATUS LIKE 'city' G
*************************** 1. row ***************************
           Name: city
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 3
    Create_time: 2020-12-15 23:28:12
    Update_time: 2020-12-16 22:22:23
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE city G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fx_country_id` (`country_id`),
  CONSTRAINT `fa_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意： 外键需要注意的细节较多，一旦使用不当，可能会带来性能下降或数据不一致的问题，尤其是在 OLTP类型（联机事务处理，个人理解：高可用，对数据有强一致性要求，例如电商、金融这类）的应用中，需要谨慎使用外键。

主键和索引

不同于其他存储引擎，InnoDB 的数据文件本身就是以聚簇索引的形式保存的，这个聚簇索引也被叫做主索引，并且也是 InnoDB 表的主键。InnoDB 的每行数据都是保存在主索引的叶子节点上，所以每个 InnoDB 表都必须包含主键，如果没有显式指定主键，那么 InnoDB 存储引擎会自动创建一个长度为 6 个字节的 LONG 类型的隐藏字段作为主键。

考虑到聚簇索引的特点对于查询优化的效果，所有 InnoDB 表都应该显式的指定主键，一般来说，主键都应该按照以下原则来选择：

满足唯一和非空约束
优先考虑使用经常被当作查询条件的字段或自增字段
字段值基本不会被修改
使用尽可能短的字段

在 InnoDB 表上，除了主键之外的索引都叫做辅助索引或二级索引，二级索引会指向主索引，并通过主索引获取最终数据。因此，主键设置是否合理，都会对索引的效率产生影响。

存储方式

InnoDB 存储表和索引的方式有以下两种：

共享表空间存储，这种方式创建的表的表结构保存在 .frm 文件中，数据和索引保存在 innodb_data_home_dir he innodb_data_file_path 定义的表空间中，可以是多个文件。

多表空间存储，这种方式创建的表的表结构仍然保存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。如果是一个分区表，则每个分区对应的 .idb 文件是 “表名+分区名”，可以在创建分区时指定每个分区的数据文件位置，以此将此表的 IO 平均分配在多个磁盘上。

使用共享表空间时随着数据不断增长，表空间的管理维护将变得越来越困难，所以一般都建议多表空间。多表空间存储在 MySQL5.7 中默认设置为 ON，即新建的表都是按照多表空间的方式创建。如果修改此参数为 OFF，则新创建的表都会改为共享表空间存储，但已经创建的多表空间的表仍然保存原来的访问方式。

在一些老版本的表中，很多都是共享表空间，可以通过以下命令修改为多表空间：

mysql> SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE city ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

多表空间的数据文件没有大小限制，既不需要设置初始大小，也不需要设置文件的最大限制，扩展大小等参数。

对于使用了多表空间特性的表，可以比较方便的进行单表操作的备份和恢复操作，但是直接复制.ibd 文件不行，因为没有共享表空间的字典信息，直接复制的 .ibd 文件和 .frm 文件恢复时是不能被正常识别的，但可以通过以下命令：

ALTER TABLE tel_name DISCARD TABLESPACE;
ALTER TABLE tel_name IMPORT TABLESPACE;

注意：即使在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB 会把内部数据词典和在线重做日志，放在这个文件中。

MEMORY 引擎

MEMORY 存储引擎使用存在于内存中的内容来创建表。每个MEMORY表实际只对应一个磁盘文件 .frm。MEMORY 类型的表访问速度非常快，因为它的数据是放在内存中的，并且默认使用HASH做索引，但是一旦服务关闭，表中的数据就会丢掉。

mysql> CREATE TABLE tab_memory ENGINE=MEMORY
    ->  SELECT phone,sheng,shi,type
    ->  FROM phone limit 600;
Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM tab_memory;
+----------+
| COUNT(*) |
+----------+
|      600 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS like 'tab_memory' G;
*************************** 1. row ***************************
           Name: tab_memory
         Engine: MEMORY
        Version: 10
     Row_format: Fixed
           Rows: 600
 Avg_row_length: 189
    Data_length: 1044672
Max_data_length: 66060225
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-12-20 01:51:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

给 MEMORY 表创建索引时，可以指定使用 HASH 索引还是 BTREE 索引：

-- mysql> CREATE INDEX mem_hash USING BTREE on tab_memory (phone);
mysql> CREATE INDEX mem_hash USING HASH on tab_memory (phone);
Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tab_memory G;
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: phone
    Collation: NULL
  Cardinality: 300
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.00 sec)

在启动MySQL 服务的时候使用 --init-file 选项，把 INSERT INTO … SELECT 或 LOAD DATA INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。

服务器需要足够的内存来维持所有在同一时间使用的 MEMORY 表，当不再需要 MEMORY 表的内容之时，要释放被MEMORY 表使用的内存，应当执行 DELETE FROM 或 TRUNCATE TABLE，或整个删除表（使用 DROP TABLE 操作）。

每个 MEMORY 表都可以方式的数据量大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是16MB，可以根据需求加大。在定义 MEMORY 表的时候，可以通过 MAX_ROWS 子句来指定表的最大行数。

MEMORY 类型的存储引擎主要用于那些内容变化不频繁的代码表，或作为统计操作的中间结果表，便于高效地对中间结果进行分析并且得到最终的计算结果。对存储引擎为 MEMORY 的表更新操作要谨慎，因为数据并没有实际写入磁盘中，所以一定要对下次重启服务如何获取这些修改后的数据有所考虑。

临时表数据量受MySQL设置影响，超过设置临时表数据，无法写入数据：
mysql> INSERT INTO tab_memory SELECT * FROM tab_memory;
ERROR 1114 (HY000): The table 'tab_memory' is full
设置的是临时表内存占用64MB，目前这张表数据量刚好达到64MB，继续写入就会提示这个

MERGE 引擎

MERGE 存储引擎也被称之为 MRG_MyISAM，是一组 MyISAM 表的组合。这些 MyISAM 表必须结构完全相同，MERGE 本身并不存储数据，对 MERGE 类型的表进行删、改、查操作，本质上是对内部的 MyISAM 表进行的。对于 MERGE 类型的表进行插入操作是通过 INSERT_METHOD 子句定义插入的表。可以有三个不同的值，FIRST 或 LAST 值使得插入操作被相应的作用在第一个表或最后一个表上，不定义这个语句或定义为 NO 表示不能对这个 MERGE 表执行插入操作。

可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 表的定义，对内部的表没有任何影响。

MERGE 在磁盘存储上只保留两个文件，文件名以表名开始，一个 .frm 文件存储表定义，另一个 .MRG 存储组合表信息，包括 MERGE 表是由那些表组成的，插入新数据时的依据。可以通过修改 .MRG 文件来修改 MERGE表，但修改后需要通过 FLUSH TABELES 刷新。

下面是创建和使用 MERGE 表的示例：

创建三个表 payment_2006、payment_2007、payment_all，其中 payment_all 是前面两张表的MERGE表：

mysql> CREATE TABLE payment_2006(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE payment_2007(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> KEY idx_fk_country_id (country_id)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE payment_all(
    -> country_id SMALLINT,
    -> payment_data datetime,
    -> amount DECIMAL(15,2),
    -> INDEX(country_id)
    -> )ENGINE=MERGE UNION=(payment_2006,payment_2007) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)

向 payment_2006 、payment_2007表中插入数据，并且查看三张表的数据：

mysql> INSERT INTO payment_2006 VALUES(1,'2006-05-01',100000),(2,'2006-08-15',15000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO payment_2007 VALUES(1,'2007-02-20',35000),(2,'2007-07-15',220000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM payment_2006;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM payment_2007;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM payment_all;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
+------------+---------------------+-----------+
4 rows in set (0.00 sec)

payment_all 表的数据是 payment_2006 和 payment_2007 两张表合并后的结果集。

再向 payment_all 表插入数据，由于 MERGE 表定义的是 INSERT_METHOD=LAST ，就会向最后一张表写入数据：

mysql> INSERT INTO payment_all VALUES(3,'2006-03-31',112200);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM payment_all;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2006-05-01 00:00:00 | 100000.00 |
|          2 | 2006-08-15 00:00:00 |  15000.00 |
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM payment_2007;
+------------+---------------------+-----------+
| country_id | payment_data        | amount    |
+------------+---------------------+-----------+
|          1 | 2007-02-20 00:00:00 |  35000.00 |
|          2 | 2007-07-15 00:00:00 | 220000.00 |
|          3 | 2006-03-31 00:00:00 | 112200.00 |
+------------+---------------------+-----------+
3 rows in set (0.00 sec)

这也是 MERGE 表和分区表的区别，MERGE 表并不能智能的将记录写入对应的表中，而分区表是可以的（分区功能于 MySQL5.1 版本推出，经过多个版本的更新，目前已比较完善）。

通常我们使用的 MERGE 表来透明地对多个表进行查询和更新操作，而这种按照时间记录的操作日志表则可以透明的进行插入。

TokuDB引擎

MySQL 除了自带的存储引擎之外，还支持一些常见的第三方存储引擎，在某些特定应用中也有广泛的使用，比如列式存储引擎 Infobright 以及 ==高写行能==，高压缩的 TokuDB 就是其中两个非常有代表性的存储引擎，这里简单介绍 TokuDB 。

ToKuDB 是一个==高性能、支持事务处理==的存储引擎，在 MySQL5.6 之前，可以安装在 MySQL、MariaDB 中，被 Percona 公司收购后，目前最新版本可在 Percona Server for MySQL （类似于 MariaDB 一样是 MySQL 的分支，完全兼容 MySQL，主要做了服务端的优化增强，客户端无修改）中使用。TokuDB 存储引擎具有高扩展性、高压缩率、高效的写入性能，支持大多数在线的 DDL 操作。且是开源的，可以从 Percona 官网下载。

对比 MySQL5.6 InnoDB 存储引擎， TokuDB 有以下特性：