MYSQL高级

相关术语介绍

多版本并发控制

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version ConcurrencyControl)(注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

当前读和快照读

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。

• 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
• 当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。 以MySQL InnoDB为例：
• 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

  	select * from table where ?;
  

• 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。

  	select * from table where ? lock in share mode;//共享锁（S）
  	select * from table where ? for update;//排他锁(X)
  	insert into table values (...) ; //排他锁(X)
  	update table set ? where ? ; //排他锁(X)
  	delete from table where ? ; //排他锁(X)12345开课吧java高级架构师
  

聚集索引

Cluster Index：聚簇索引。

InnoDB存储引擎的数据组织方式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列。关于聚簇索引表的组织方式，可以参考MySQL的官方文档：Clustered and Secondary Indexes 。本课程就不再做具体的介绍。接下来的部分，主键索引/聚簇索引 两个名称，会有一些混用，望读者知晓。

最左前缀原则

顾名思义，就是最左优先，这个最左是针对于组合索引和前缀索引，理解如下：

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

两阶段锁

传统RDBMS加锁的一个原则，就是2PL (Two-Phase Locking，二阶段锁)。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。

Gap锁和Next-Key锁

InnoDB中的完整行锁包含三部分：记录锁（Record Lock）、间隙锁（Gap Lock）、Next-Key Lock。以下定义摘自MySQL官方文档

记录锁（Record Locks）:记录锁锁定索引中一条记录。
间隙锁（Gap Locks）:间隙锁要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记录后面的值。 Next-Key Locks:Next-Key锁是索引记录上的记录锁和在索引记录之前的间隙锁的组合。

隔离级别

Read Uncommited

• 可以读取未提交记录。此隔离级别，不会使用，忽略

Read Committed (RC)

• 针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。

Repeatable Read (RR)

• 针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

Serializable

• 从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
• Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

行锁原理分析

• 简单SQL的加锁分析:

  1. select * from t1 where id = 10;
  2. delete from t1 where id = 10;

• RC隔离级别下:

  1. id是主键
     • 只需要将主键上id = 10的记录加(索引=10)上X锁即可
  2. id唯一索引
     1. 找到索引=10上X锁
     2. 找到这条记录的主键 (回主键索引(聚簇索引) )索引加锁 (如果并发一个sql语句根据主键查询,如果这条记录没有在主键索引上加锁,则会违背同一条记录的修改操作)
  3. id唯一索引
     1. 满足SQL查询条件的记录，都会被加锁。
     2. 这些记录在主键索引上的记录，也会被加锁。
  4. id 无索引
     1. 如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。
     2. 在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁/放锁操作还是不能省略的。

• RR隔离级别:

  1. id是主键 (同上) 只需要将主键上id = 10的记录加(索引=10)上X锁即可
  2. id唯一索引(同上) 两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个
  3. id非唯一索引
     1. 循环定位到第满足查询条件的记录，加记录上的X锁，加GAP上的GAP(间隙锁)锁，然后加主键聚簇索引上的记录X锁
     • 非唯一索引的存储结构: B树, 一个节点存储多个数据,左边的节点索引<当前节点>右侧节点, 如果其他session在这个区间插入或者删除数据,导致幻读,因为间隙锁的存在会解决这个问题, 我个人理解为一个双向链表,每一个元素都有向前向后的指针,如果两个元素中间存在间隙锁,则无法插入或更新数据
  4. id无索引
     1. 会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。
  • Serializable
  1. 会对sql1 加读锁

• 一条复杂SQL的加锁分析

  	Table: t1(id primary key, userid,pubtime,comment);
  	index: idx_te_pu(pubtime, id key);
  	sql  : delete from t1 where pubtime>1 and pubtime <20 and userid ="os" and content = "1"
  

  • RR隔离级别
  1. 在pubtime的范围内加x锁和GAP锁
  2. 根据1对应的数据的主键加X锁(回表)
  • 测试

  	CREATE TABLE `t1` (
  	`id`  int(11) NOT NULL ,
  	`pubtime`  int(2) NULL DEFAULT NULL ,
  	`userid`  varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  	`content`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
  	PRIMARY KEY (`id`),
  	INDEX `pu` (`pubtime`, `userid`) USING BTREE);
  
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("1", "1", "1", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("10", "1", "a", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("2", "2", "2", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("4", "3", "b", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("22", "5", "os", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("5", "10", "os", "1");
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("100", "20", "c", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("30", "21", "d", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("31", "31", "e", NULL);
  	INSERT INTO `t1` (`id`, `pubtime`, `userid`, `content`) VALUES ("32", "32", "e", NULL);
  
  

• sessiona

  1. set autocommit = 0;
  2. "delete from t1 where pubtime>1 and pubtime <20 and userid ="os" and content = "1""

• sessionb

  1. update t1 set content = 4 where id = 30;

• 进入阻塞状态 猜想: 因为content没有索引导致所有行加X锁

  
  	mysql> show status like "%row_lock%";
  	+-------------------------------+--------+
  	| Variable_name                 | Value  |
  	+-------------------------------+--------+
  	| Innodb_row_lock_current_waits | 1      |
  	| Innodb_row_lock_time          | 460003 |
  	| Innodb_row_lock_time_avg      | 25555  |
  	| Innodb_row_lock_time_max      | 51004  |
  	| Innodb_row_lock_waits         | 18     |
  	+-------------------------------+--------+
  	5 rows in set (0.00 sec)
  
  

  • Innodb_row_lock_current_waits | 1 |有一个等待

• 执行commit并回复数据后

  1. sessiona 执行delete from t1 where pubtime>4 and pubtime <20 and userid ="os";
  2. sessionb INSERT INTO t1 (id, pubtime, userid, content) VALUES ("19", "19", "d", ""); 会进入等待 取消命令( ctrl +c) 被阻塞,原因是pubtime在 索引3到20范围内, 此时已对该区域加GAP(间隙锁)
  3. sessionb INSERT INTO t1 (id, pubtime, userid, content) VALUES ("21", "21", "d", ""); 执行成功
  4. sessionb INSERT INTO t1 (id, pubtime, userid, content) VALUES ("25", "18", "d", ""); 被阻塞,原因是pubtime在 索引3到20范围内, 此时已对该区域加GAP(间隙锁)

死锁原理与分析

1. 每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。
2. 多个事物对两条记录同时更新,sessiona先持有 a记录, sessionb先持有b记录,sessiona更新b记录的时候 需要获取sessionb的锁,但是sessionb 去更新a记录的时候同样需要获取sessiona的锁,导致死锁.

• 如何解决死锁呢？MySQL默认会主动探知死锁，并回滚某一个影响最小的事务。等另一事务执行完成之后，再重新执行该事务。