MySQL总结（一）——存储引擎及各种锁

一、存储引擎

存储引擎在MySQL的逻辑架构中位于第三层，负责MySQL中的数据的存储和提取。MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

InnoDB和MyISAM区别

1. 是否支持行级锁: MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

二、MySQL中的锁

行级锁

行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行级锁适用于高并发环境下，对事务完整性要求较高的系统

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
表级锁使用与并发性不高，以查询为主，少量更新的应用

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

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

Mysql数据库中存储引擎

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

1. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
2. InnoDB存储引擎既支持行级锁（ row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MyISAM锁

1. MyISAM存储引擎主要是表级锁，它有两种模式：
   表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

2. 当MyISAM在执行查询语句时，会自动给涉及到表加读锁，在执行更新操作时，会加写锁。当然用户也可以用LOCK TABLE 去显式的加锁。

需要在一个时间点实现多个表的一致性读取，不然的话，可能读第一个表时，其他表由于还没进行读操作，没有自动加锁，可能数据会发生改变。并且显示加锁后只能访问加锁的表，不能访问其他表。

3. MyISAM存储引擎有个系统变量 concurrent_insert，专门用来控制并发插入的行为，可以取 0 ， 1 ， 2。
   0表示不允许并发插入，1表示表中间没有删除的行时可以在表末尾插入，2表示总是可以插入。

注意：

1. 读操作不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2. 并且当写锁和读锁同时被申请时，优先获得写锁，这也这正是表级锁发生锁冲突概率最高的原因，因为写锁可能会一直阻塞读锁
3. 虽然写锁优先于读锁获取，但是长时间的查询操作也可能会让写操作饿死，所以尽量避免一条SQL语句执行所有的查询，应该进行必要的分解。

InnoDB锁

由于InnoDB支持事务，并默认是使用行级锁。

1. 共享锁(S)和排他锁(X)，分别类似于MyISAM的读锁和写锁。对于 UPDATE、 DELETE 和 INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB不会加任何锁。
2. 可以显式的加锁，用lock in share mode 显式的加共享锁，用 for update 显式的加排他锁。

如果线程A加了共享锁后，线程B对同一个表加了共享锁，那么两个线程需要进行更新操作时会产生死锁。所以，进行更新操作时最好加排他锁。

3. InnoDB行锁的实现方式——索引加锁
   （1）、只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁。
   （2）、即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突。
   （3）、如果数据表建有多个索引时，可以通过不同的索引锁定不同的行。

数据库中死锁

MyISAM是没有死锁问题的，因为他会一次性获得所有的锁。
InnoDB发生死锁后一般能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。
避免死锁方式：
(1) 如果不同的程序会并发的存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。
(2) 在程序以批量方式处理数据时，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大的降低出现死锁的可能。