Mysql存储引擎与锁机制详解-优快云博客

Mysql逻辑架构视图

主要存储引擎

引擎	Mysql版本	事务	锁粒度	主要引用	忌用
InnoDB	全部	支持	支持Mvcc行级锁	事务处理
MyISAM	全部	不支持	支持并发插入的表锁	Select,insert高负载	读写并重的场合
MyISAM Merge	全部	不支持	支持并发插入的表锁	分段归档,	许多全局查找
Memory(HEAP)	全部	不支持	表锁	中间计算，静态数据查证	大型数据集,持久性存储,重启后数据丢失
Falcon	6.0	支持	支持Mvcc行级锁	事务处理
Archive	4.1	支持	支持Mvcc行级锁	日志记录，聚合分析，只支持insert，select操作	需要随机读取删除
CSV	4.1	不支持	表锁	日志记录，大规模加载外部数据	需要随机读取,索引等
NDB Cluster	5.0	支持	行级锁	高可用集群	典型引用
Maria	6.x	支持	支持mvcc行级锁	替代MyISAM

可以通过show table status like '表名称'\G 来查看表的信息

锁粒度

1.表锁 (Table Lock)

     当一个用户对表进行写操作(新增,删除,修改), 会获得一个写锁，写锁会禁止其他用户的读写操作，当无人做写操作时，其他用户才能获得读锁，读锁与读锁之间不会冲突。表锁是最小锁策略，其性能良好，表锁由Mysql本身和存储引擎实现。

2.行级锁(Row Lock)

  行级锁可以支持最大的并发处理，同时也带来了最大锁开销，行级锁在InnoDB，Falcon存储引擎出以实现。行级锁由存储引擎实现，而不是Mysql服务器本身。

3.MVCC行级锁

   MVCC 是一种多版本并发控制(Multiversion Concurrency Control)的技术，MVCC不是mysql独有的技术，Oracle，PostgreSQL等其他数据库也使用了该技术。

每种存储引擎实现MVCC的方式是不同的，例如乐观并发控制，悲观并发控制，下面以Innodb的实现方式说说其简要工作原理：

Innodb通过为每个数据行增加两个隐士的字段来实现MVCC，这两个隐士字段记录了行的创建的时间，以及过期时间(删除时间)，每一行都存储了事件发生时的系统版本号，用来替代事件发生时的实际时间。每一次开启一个新的事务时，版本号都会递增，每个事务都会保存它在开始时的“系统版本”的记录，而每个查询都会根据事务的版本号，检查每行数据的版本号。

SELECT

Innodb 检查每行数据，确保它们符合两个标准

*Innodb只查找版本号小于或等于当前版本号的数据，这确保了当前事务读取的数据行都是在事务开始前已存在，或者是当前事务创建或修改的行。

*数据行的删除标识必须是未定义，或者是大于事务版本的，这确保了查询出来的数据在事务开始时是未被删除的。

INSERT

Innodb为新增的数据行记录当前版本号

DELETE

修改要删除行的过期时间标识

UPDATE

为每个需要更新的行建立一个新的行的拷贝，并为新的行拷贝记录当前系统版本。也为更新前的数行记录系统版本号，作为旧行的删除版本标识。保存这些额外记录的好处是大多数并发操作都不必申请枷锁，这使读操作变得更快，因为读操作是要选取符合标准的数据行即可，这种方式的缺点是：存储引擎必须为每行数据存储更多的额外数据，浪费空间，做更多的检查工作，以及整理一些额外的数据带来的开销。

加锁策略并发系统开销引擎
表级加锁最低最低 MyISAM，Merge，MyISAM Merge
行级加锁高高 NDB Cluster
支持MVCC的行级加锁最高最高 InnoDB，Falcon