【MySQL】存储引擎

基础

存储引擎是指存储数据，建立索引，查询和更新数据等技术的实现方式。Oracle，Sql Server等数据库只有一种存储引擎，而MySQL提供了多种存储引擎的选择。因为MySQL存储引擎是插件式的，所以用户可以根据需要使用最优的存储引擎，甚至编写自己的存储引擎。

MySQL5.0支持的存储引擎包含：InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

查看MySQL数据库默认的存储引擎：

mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)

查看当前数据库支持的存储引擎：

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

从事务、锁等多个方面，对比一下几种常用的存储引擎，如下表所示：

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持			表锁	行锁
锁机制	行锁(适合高并发)	表锁	表锁	支持	支持
B树索引	支持	支持	支持		支持
哈希索引		支持		支持	低
全文索引	支持(5.6版本之后)	支持		低	高
集群索引	支持			低	高
数据索引	支持	支持	支持	高
索引缓存	支持	支持	支持
数据可压缩	支持		N/A
空间使用	高	低	中等
内存使用	高	低	高
批量插入速度	低	高
支持外键	支持

下面我们将重点介绍最常使用的两种存储引擎：MyISAM、InnoDB。

MyISAM

MYSQL有哪些存储引擎，各自优缺点。

MyISAM是MySQL5.5以前的默认存储引擎。MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎。MyISAM的优点还包括数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。

每个MyISAM表在磁盘上存储成3个文件，其文件名都和表名相同，但扩展名分别是：

• .frm：存储表定义；
• .MYD：存储数据；
• .MYI：存储索引；

MyISAM把数据和索引存在不同的文件中，甚至可以把数据文件和索引文件放在不同的路径下，以获得更快速度。如果要设置索引文件和数据文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定。文件路径需要是绝对路径，并且要有访问权限。

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

• 静态表：默认的存储格式。静态表中的地段都是非变长字段，这样每个记录都是固定的长度。优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用空间通常比动态表多。
• 动态表：动态表包含变长字段，记录不是固定长度的。优点是占用的空间相对较少，但是可能会产生碎片，需要定期制定OPTIMIZE TABLE语句。
• 压缩表：压缩表有myisampack工具创建，占据非常小的磁盘空间。

MyISAM表可能会损坏，原因是多种多样的，损坏的表可能不能被访问，会提示需要修复或访问后返回错误的结果。MyISAM有检查和修复的工具，可以用CHECK TABLE语句来检查MyISAM表的健康，并用REPAIR TABLE语句修复一个损坏的MyISAM表。

InnoDB

InnoDB是MySQL5.5及以后的默认存储引擎。InnoDB存储引擎支持外键和事务，具有事务提交、回滚、崩溃恢复的能力，而且支持行级锁。但是对比MyISAM存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

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

InnoDB的默认事务隔离级别是REPEATABLE READ，并且通过间隙锁策略防止幻读，到达了最高隔离级别的效果。

InnoDB的主键索引是聚簇索引，通过主键进行查询有很高的性能；通过非主键索引查询会查出主键值，如果信息不足够再通过主键值进行查询（二次回表）。非主键索引底层也是B+树，叶子节点存放了主键值，所以如果主键很大的话其他的非主键索引都会很大，因此主键应当尽可能小。

外键

外键是指创建一个表（子表）时，使某一列参考另一个表（父表）中的某一列，其中子表中的这一列称为外键。

在所有存储引擎中，只有InnoDB支持外键。在创建外键时，要求父表中被参考的列必须有对应的索引，子表会在外键上自动创建索引。子表中的外键通常会参考父表中的主键。

举例：

CREATE country_tab (
  country_id int(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE=Innodb;

CREATE city_tab (
  city_id int(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  country_id int(11) UNSIGNED NOT NULL,
  CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country_tab(country_id)
) ENGINE=Innodb;

外键的使用条件包括：

1. 父表和子表两个表必须是InnoDB表，MyISAM表暂时不支持外键；
2. 外键列必须建立了索引，MySQL4.1.2以后的版本在建立外键时会自动给父表中的列创建索引，但如果在较早的版本则需要显式建立；
3. 建立外键关系的两个列必须是数据类型相似的，也就是两个列的数据类型必须可以相互转换，比如int和tinyint可以，而int和char则不可以；

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

• RESTRICT和NO ACTION相同，表示在子表有关联记录的情况下，父表不能更新；
• CASCADE，表示父表在更新或者删除时，更新或者删除子表对应的记录；
• SET NULL，表示父表在更新或者删除的时候，子表的对应字段被SET NULL。

外键的优点：可以使得两张表关联，保证数据的一致性和实现一些级联操作。

外键的缺点：表之间存在硬性的关联，删除或更新父表可能会导致额外的操作。所以，不推荐使用外键。

文件存储方式

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

• 使用共享表空间存储。这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中，可以是多个文件。
• 使用多表空间存储（默认）。这种方式创建的表的表结构同样存在 .frm 文件中，但是每个表的数据和索引单独保存在.ibd文件中。

要使用多表空间的存储方式，需要设置参数innodb_file_per_tab，并且重新启动服务后才可以生效。对于新建的表，按照多空间的方式创建，已有的表仍然使用共享表空间存储。

多表空间的数据文件没有大小限制，不需要设置初始大小，也不需要设置文件的最大限制、扩展大小等参数。对于使用多表空间特性的表，可以比较方便地进行单表备份和恢复操作。

事务

事务是一组SQL语句组成的逻辑操作单元。

事务具有4个特性（简称为ACID）：

• 原子性（Atomicity）：事务在逻辑上是不可分割的最小工作单元。事务中的所有操作要么全都执行，要么全都不执行，不能只执行其中的一部分。
• 一致性（Consistency）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如索引或双向链表）也都必须是正确的。数据库总是从一个一致性的状态转换到另一个一致性的状态。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着一个事务所做的修改在最终提交以前，对其他事务是不可见的。简单来说，并发运行的多个事务之间不会相互影响。
• 持久性（Durability）：事务完成之后，它对于数据的修改是永久性的。即使系统崩溃，修改的数据也不会丢失。

默认情况下，事务是默认提交的，可以查看autocommit变量：

mysql> show variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

当事务是默认提交时，执行一个SELECT、INSERT、UPDATE、DELETE语句都会创建一个事务并自动提交。

如果需要在一个事务中包含多个语句，可以使用BEGIN （或START TRANSACTION）、COMMIT命令，其中BEGIN （或START TRANSACTION）命令用于开启一个事务，COMMIT用于提交一个事务。

另外，也可以通过设置autocommit变量关闭自动提交，

mysql> SET autocommit = 0;

mysql> show variables like 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

注意，设置autommit 是 session 级别的，就是当前连接更改了 autocommit=0，对其他连接没有影响。

当事务不是自动提交时，执行一个SELECT、INSETRT、UPDATE、DELETE语句会开启一个事务，然后需要手动执行COMMIT命令提交事务，否则数据变动无法被其他的session观察到。

redo log

redo log又称重做日志，是Innodb存储引擎自带的日志，用于记录事务操作的变化。redo log记录的是数据修改之后的值，不管事务是否提交都会记录下来。

在数据库服务器崩溃时，比如如数据库突然断电，redo log文件就能派上用场。InnoDB存储引擎会使用redo log恢复到崩溃前的时刻，以此来保证数据的完整性。

对比和选择

MyISAM是MySQL5.5版本之前的默认数据库引擎。MyISAM性能比较好，支持全文索引、数据压缩、空间函数等，但不支持事务、行级锁和崩溃恢复。InnoDB是MySQL5.5及以后的默认存储引擎。InnoDB存储引擎支持外键和事务，具有事务提交、回滚、崩溃恢复的能力，而且支持行级锁。

两者的对比：

• 外键：MyISAM不支持，而InnoDB支持。
• 事务和崩溃恢复：MyISAM不提供事务支持。InnoDB提供事务支持，具有事务提交回滚、崩溃修复的能力。
• 行级锁：MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认为行级锁。
• MVCC：只有InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。

绝大部分情况，我们都应该使用InnoDB 存储引擎，因为其支持事务和崩溃恢复。但是，在某些情况下使用 MyISAM 也是合适的，比如读密集、对事务的完整性没有要求的场景。