mysql存储引擎MyISAM、InnoDB总结篇

1.MySQL存储引擎对比

 

 这里主要讲MyISAM和InnoDB对比：

 

 

 MyISAM和InnoDB详细区别（聚集索引和非聚集索引）：

推荐一篇文章

2.MyISAM表锁和InnoDB表锁、行锁

1.MyISAM表锁

MyISAM只支持表锁，在进行select时会自动加读锁，进行增删改时自动添加写锁。

手动加锁方式：lock table 表名 read/write

读锁：

　　当客户端a加了读锁时，其他客户端仅能进行读操作，不能进行写操作，必须等待a释放锁。

写锁：

　　当客户端a加了写锁时，其他客户端不能进行增删改查，必须等待a释放锁。

2.InnoDB表锁、行锁

InnoDB中默认为行锁，行锁是基于索引存在的，即增删查改中没有使用索引则会退化为表锁，行锁锁的是索引而不是一行数据。

行锁：

　　行锁中分为共享锁、排他锁（必须使用到索引）。

　　通过select进行查询时默认不会进行加锁，需要手动加锁。进行增删改时会自动添加排他锁。

　　　　select * from linelock where age = 22 lock in share mode; -- 添加共享锁

　　　　select * from linelock where age = 22 for update; -- 添加排他锁

　　共享锁：

　　　　客户端A进行查询并对查询的行使用共享锁：

　　　　1.其他客户端在对这些上锁的数据查询时可以进行查询（不论是否手动添加共享锁），在进行修改操作时不可以（排他锁）。

　　　　2.其他客户端在对上锁数据以外的数据进行操作时没有任何影响。

　　排他锁：是指排斥其他锁，但不排斥没有使用锁的情况。即对一行或多行数据使用排他锁，那么在对使用排他锁的数据添加共享锁和排他锁都是不可以的，但是对使用排他锁的数据进行查询时（默认不会使用锁）可以执行。

3.InnoDB事务隔离级别

InnoDB支持事务，并将事务隔离级别分为四种：未提交读(read uncommitted)、已提交读(read committed)、可重复读(repeatable read)、串行化(serializable)，默认使用可重复读。

四种事务隔离级别对比：

 

下面对每种隔离级别的临界情况进行说明：

备注：mysql通过MVCC（多版本并发控制协议）解决了不可重复读和幻读

MVCC（多版本并发控制协议）在InnoDB存储引擎中主要提供两种读方式，一种是快照读，一种是当前读。
快照读：
　　所谓快照读就是一致性非锁定读，就是读取的是历史版本，不用加锁，提高并发，在数据库不同的隔离级别下读取的历史版本有所不同，在RR（REPEATABLE READ）隔离级别下读的历史版本总是事务刚开始的那一个版本，所以可以避免不可重复读与幻读，因为其对其他事务提交的数据是不可见的。在RC（READ COMMITTED）隔离级别下读的是最新的历史版本，或者是可见版本（另一个事务刚已经提交的数据），这样就会出现不可重复读现象。

当前读：
　　当前读时一致性锁定读，采取加锁的方法保证并发数据的一致性：
　　SELECT * FROM table WHERE ...  LOCK IN SHARE MODE（加的是共享锁（S））
　　SELECT * FROM table WHERE ...  FOR UPDATE（加的是排他锁（X））
　　INSERT INTO table ........ （在插入数据前先读取数据，进行唯一性检查，读取数据时会加X锁）
　　UPDATE table SET .........  （在更新数据之前得先读取数据，在对读取的数据进行修改，读取数据时会加X锁）
　　DELETE FROM table ........（在删除数据之前也是得先读取数据，读取数据时会对数据加X锁）
说明：在serializable隔离级别下，MVCC退化成了base-lock concurrency control（基于锁的并发控制），也就不存在快照读了，全是当前读，序列化的操作，并发性受到了很大的影响，一般不采用该隔离级别。

1.未提交读

事务A修改数据，但并未提交。事务B读取A修改的数据（未提交），但是事务A因为一些原因进行了事务回滚。-- 出现了脏读数据

2.已提交读

事务B读取数据（未提交），事务A对B读取的数据进行修改并提交，事务B再次读取是更新后的数据（未提交）。前后两次事务B读取的数据不同 -- 出现了不可重复读取现象

3.可重复读

事务A读取数据（未提交），事务B更新数据并提交，事务A再次读取数据（未提交）,事务A前后两次读取的数据相同。

　　这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。 简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
       InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题

4.幻读概念

幻读是指事务A在读取数据时，另一个事务B进行插入删除操作，事务A再次查询时，出现了多出来一条数据或者少一条数据的现象。

5.补充：

　　1、事务隔离级别为读提交时，写数据只会锁住相应的行

 

　　2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

 

　　3、事务隔离级别为串行化时，读写数据都会锁住整张表

 

　　 4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。