【MySQL】存储引擎

基础

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

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

创建新表时可以通过ENGINE关键字指定存储引擎。举例

CREATE table test_tab (
  id bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=xxx;

如果不指定那么MySQL就会使用默认的存储引擎。MySQL5.5之前的默认存储引擎是MyISAM5.5以及之后的默认存储引擎是InnoDB。

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

show variables like '%storage_engine%'

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

show engines;
-- or show variables like '%have%'

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

特点	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> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

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

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

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

mysql> SET autocommit = 0;

注意设置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 也是合适的比如读密集、对事务的完整性没有要求的场景。