MySQL的四大常见存储引擎

谈到MyISAM和InnoDB了我们先来了解一下什么是存储引擎吧。MySQL中的数据用各种不同的技术存储在文件（或者内存）中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能，我们把这些不同的技术以及配套的相关功能称为存储引擎(也称作表类型)。MySQL默认配置了许多不同的存储引擎，我们可以选择不同的存储引擎来满足我们对数据的处理（存储、检索等）需求，以改善我们应用程序的整体功能。正因为MySQL存储引擎的多样性，使得MySQL深受广大开发者的垂青。

我们的前提条件：我用的是5.7.24-log版本，可以在Navicat中通过SELECT VERSION();命令查看。那么MySQL都有哪些存储引擎呢？我们可以使用sql命令SHOW ENGINES;来查看，结果如下：

• Engine：表示储存引擎名称；

• Support：表示MySQL是否支持该存储引擎，DEFAULT为默认的存储引擎；

• Comment：是对该存储引擎的功能描述，例如：InnoDB支持事务、行级锁定和外键；

• Transactions：是否支持事务；

• XA：存储引擎是否支持分布式事务；

• Savepoints：存储引擎是否支持保存点。

接着让我们来说一下其中比较常见的四大存储引擎吧。

InnoDB

InnoDB是MySQL（MySQL5.5以后）的默认存储引擎，支持事务、行级锁和外键，被用来处理大量短期事务。如果使用到外键、需求并发程度较高、数据一致性要求较高的话，那么通常选择InnoDB引擎，这也是互联网大厂使用InnoDB存储引擎的原因。除非有非常特别的原因需要使用其他的存储引擎，否则建议优先考虑InnoDB。但是对比MyISAM，InnoDB写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

MyISAM

MyISAM提供了大量的特性，包含全文索引、压缩、空间行数等，支持3种不同的存储格式，分别是：静态表、动态表、压缩表。

• 静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的。优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格），在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

• 动态表：表中的字段都是变长字段，记录不是固定长度的。这样存储的优点是占用的空间相对较少；缺点是频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。

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

在MyISAM中，数据文件和索引文件可以放置在不同的目录（在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定文件的绝对路径），平均分配IO，获取更快的访问速度。但是MyISAM不支持事务，不支持外键，也不支持行级锁，支持表级锁，有个缺陷就是崩溃后无法恢复。如果应用程序以检索为主，只有少量的插入、更新和删除操作，并且对事物的完整性、并发程度不是很高的话，通常建议选择MyISAM存储引擎。

Memory

Memory存储引擎使用存在内存中的内容来创建表，所以它的访问速度非常快，并且默认使用HASH索引。但是一旦服务器关闭或者mysqld守护进程崩溃时，所有的Memory数据都会丢失，但表还会继续存在，获得速度的同时也带来了一些缺陷。

它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。

服务器需要足够的内存来维持在同一时间内使用的MEMORY表，当不再使用MEMORY表时，要释放MEMORY表所占用的内存，应该执行DELETE FROM或truncate table或者删除整个表。每个MEMORY表中放置的数据量的大小，受到max_heap_table_size系统变量的约束，这个系统变量的初始值是16M，同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。它通常用于更新不太频繁的小表。

Merge

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，Merge表本身没有数据，对Merge类型的表进行查询、更新、删除的操作，实际上是对内部的MyISAM表进行的。Merge表在磁盘上保留两个文件，一个是.frm文件存储表定义、一个是.MRG文件存储Merge表的组成等。MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。

我们可以通过show create table 表名 命令来查看表使用的引擎，由以下代码可以看出test表使用的是MyISAM存储引擎。

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=46 DEFAULT CHARSET=utf8

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎（5.1之后就不直接支持了，因为BDB被oracle收购了）采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。接下来就让我们来了解一下MyISAM和InnoDB锁的具体分类与使用方法。

MyISAM锁

MyISAM存储引擎支持的表级锁分为表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock），以下简称读锁和写锁。先看一下他们的特性：

• 读锁：不会阻碍其它进程的读，但是会阻碍写，只有当读锁释放之后，才会执行其它进程的写--读锁阻塞写锁，但是不阻塞读锁；

• 写锁：会阻碍其他进程的读和写，只有当写锁释放，才会执行其它写操作--写锁阻塞读锁和写锁；

接下来让我们用例子来演示一下上边的结论，在演示之前，先让我们来说几个命令吧。

• LOCK TABLE 表名 WRITE/READ：给表加写锁或者读锁；

• UNLOCK TABLES：给表解锁

演示一：表共享读锁

当session1给表test加读锁时，session1只能读取当前表的数据，不可以读其他表，也不可以修改test和其他表；session2可以读取test表数据，更新test表阻塞，但是可以修改和查询其他表数据。

演示二：表独占写锁

当session1给表test加写锁时，可以更新test表，读test表阻塞，但是不可以修改和查询其他表数据；session2查询和更新test表阻塞，但是可以查询和更新其他表。

另外我们还可以使用show open tables命令来查看在表缓存中当前被打开的非TEMPORARY表的锁使用情况，其中In_use表示有锁正在使用。

也可以使用show status like 'table%'命令来查看锁的争夺情况，其中Table_locks_waited为等待次数，每等待一次，值就加一，值越大，表示存在越严重的表级锁争用；Table_locks_immediate为产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁，值加一。

★

MyISAM默认是使用select语句加读锁，增删改操作加写锁。MyISAM是偏读锁，读写调度写优先，不适合做写为主的表的引擎。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，从而永远阻塞。

”

InnoDB锁

上篇文章中我们讲过了记录锁（行锁）、间隙锁和临键锁，这里就不再赘述了。接下来我们按照锁的模式讲一下InnoDB里的共享锁、排他锁和意向锁，其中共享锁和排他锁属于行级锁，行级锁都是基于索引项的，如果没有索引项，则添加的是表级锁；意向锁属于表级锁。

共享锁：Shared Locks，简称S锁

若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。可以通过select ... lock in share mode来加共享锁，通过Commit、Rollback来释放锁。

排他锁：Exclusive lock，简称X锁，也叫互斥锁

若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁（排他锁不可与其他锁共存），直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。可以通过select ... for update手动加锁，也可以通过增删改操作自动加锁，通过Commit、Rollback来释放锁。

意向锁：Intention Locks

说起意向锁，大家先来考虑一下这个问题：假设存在两个事务A和B对表test进行操作，首先事务A对第十行数据加了一把读锁，锁住了该行数据，让这一行只能读，不能写；然后事务B想要对该表加一把表级的写锁，那么事务B能否加锁成功呢？思考两秒钟...答案当然是否定的，即事务B无法加锁成功。如果我们假设它加锁成功的话，那么理论上它就能修改表中的任意一行，这将与事务A持有的行级锁（读锁）产生冲突。而数据库想避免这种冲突的话，就需要将事务B的加锁申请给阻塞住，直到事务A的行锁被释放。那么问题来了，数据库是怎么判断这种冲突的呢？我们可以想到两种方案：一、判断表是否已被其他事务用表锁锁表；二、判断表中的每一行是否已被行锁锁住。很显然，如果采用第二种方法的话，需要一行一行去遍历整张表，效率太慢进而造成系统消耗，所以我们选择第一种方法，这也就是意向锁是表锁的原因。

意向锁是放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享锁或排它锁，意向锁无法手动创建。如果对任一结点加锁时，必须先对它的上层结点加意向锁也就是如果对一个结点加意向锁，则说明该结点的下层结点正在被加锁。意向锁的执行流程：如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞，第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁，所以意向锁不是用来给数据加锁的，而是用来判断数据有没有存在锁的标志。下面介绍两种常用的意向锁：意向共享锁（Intent Share Lock，简称IS锁）、意向排它锁（Intent Exclusive Lock，简称IX锁）。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁的兼容关系如下：

其他锁简介（了解）

InnoDB还包含插入意向锁、自增锁和空间锁。

• 插入意向锁（Insert Intention Locks）：是间隙锁的一种，它的目的是为了提高插入性能。在多个事务对同一个索引中的同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此，主要是不需要去申请排他锁。

• 自增锁（AUTO-INC Locks）：自增锁是MySQL中一种特殊的锁，如果表中存在自增字段，MySQL便会自动维护一个自增锁。和自增锁相关的一个参数为（5.1.22版本之后加入）innodb_autoinc_lock_mode，可以设定3个值，0：traditonal（每次都会产生表锁，可以控制插入顺序，效率低）；1：consecutive（会产生一个轻量锁，simple insert会获得批量的锁，保证连续插入，默认）；2：interleaved（不会锁表，来一个处理一个，并发最高，会存在复制问题）。

总结

MySQL这三种锁的特性可大致归纳如下：

• 表级锁（偏读）：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁（偏写）：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。