Mysql文章笔记

Mysql名词解释/含义/读书笔记

MVCC(Multiversion concurrency control)

快照读

简单的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锁

clustered index

官方文档：
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

• If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.(假设定义的主键，那么InnoDB会选择主键作为聚簇索引。

  )

• If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.(假设不存在主键，那么Mysql会选择一个唯一建而且唯一建列为Not Null作为InnoDB索引。)

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.(假设表主键和唯一建都不存在。那么InnoDB内部会生成一个隐藏的包括了列号值。而且作为聚簇索引。

  )

2PL—Two-Phase Locking：二阶段锁

在数据库和会话程序中。使用2PL来保证线程安全。即获取锁与释放锁。2PL有两个原语：

-Expanding phase : locks are acquired and no locks are released;
-Shrinking phase : locks are released and no locks are acquired;
2PL定于区分了两种锁，Shared locks 和 Exclusive locks。

參见:

Mysql 事务隔离级别：Isolation level

在database systems中，isolations 决定事务对于其它用户和系统的可见性吗，PS：ACID（Atomicty,Consistency, Isolation, Durability）。

事务操作中出现的一些名词

幻读

在一个事务的两次查询中数据笔数不一致，比如有一个事务查询了几行(Row)数据，而还有一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。

脏读

某个事务已更新一份数据，还有一个事务在此时读取了同一份数据。因为某些原因。前一个RollBack了操作，则后一个事务所读取的数据就会是不对的，原因是事务能看到别的事务的未提交的结果。

不可反复读

在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。原因是事务能够看到别的事务提交的结果。

四中隔离级别

Serializable

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之。它是在每一个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

Repeatable reads

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到相同的数据行。

只是理论上，这会导致还有一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，还有一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时。会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本号并发控制（MVCC，Multiversion Concurrency Control）机制攻克了该问题，GAP锁。

Read committed

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务仅仅能看见已经提交事务所做的改变。

这样的隔离级别 也支持所谓的不可反复读（Nonrepeatable Read）。因为同一事务的其它实例在该实例处理其间可能会有新的commit。所以同一select可能返回不同结果。

Read uncommitted

在该隔离级别，全部事务都能够看到其它未提交事务的运行结果。本隔离级别非常少用于实际应用，因为它的性能也不比其它级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

分析一条Sql的加锁情况，參见博文

• Sql: select * from t1 where id=10;
• Sql: delete from t1 where id=10;
  在回答这个问题之前我们须要明白几个前提条件：
• 前提一：id列是不是主键
• 当前数据库引擎的隔离级别是什么
• Id列不是主键。那么Id列上面有无索引
• Id列上面假设有二阶索引，那么Id是否是Unique Key
• 两个Sql的运行计划是什么？索引扫描？全表扫描？
  另外一个Sql即便通过分析结论会使用索引，但实际运行计划有非常多复杂的其它条件，即便”看上去“会走索引可是终于通过运行计划看却走了全表扫描。

组合一：id列是主键。RC隔离级别，运行delete from t1 where id = 10;

create table t1(
    id int(32) not null,
    name varchar(50) not null，
    primary key(id)
);

结论：假设id列是主键，这样的情况仅仅须要在id=10的列上加上X锁。

组合二：id是Unique_key , RC隔离级别，运行delete from t1 where id=10;

create table t1(
    id int(32) not null,
    name varchar(50) not null,
    primary key (`name`),
    unique key `key_name`(`name`)
);

运行图()：

组合三：id为非唯一索引，RC隔离级别delete from t1 where id=10;

create table t1(
    id int(32) not null ,
    name varchar(50) not null ,
    primary key (`name`),
    key `key_name`(`name`)
);

运行图():

结论：若id列上有非唯一索引，那么相应的全部满足SQL查询条件的记录。都会被加锁。

同一时候，这些记录在主键索引上的记录，也会被加锁。

组合四：id列上无索引。RC隔离级别delete from t1 where id=10;

create table t1(
    id int(32) not null ,
    name varchar(50) not null,
    primary key (`name`),
);

运行图():

组合五：id为主键列。RR隔离级别

这样的情况下加锁机制同组合一一致。

组合六：id列为唯一索引，RR隔离级别

这样的情况下加锁同组合二一致

组合七：id列为非唯一索引，RR隔离级别 （GAP锁）

因为Mysql事务离别为RC的情况下是同意幻读的，可是隔离级别在RR的情况下是不运行幻读。Mysql是怎样做到RR隔离级别不产生幻读？这个组合中会加以说明。
运行图()：

GAP锁之所以在组合五和组合六中不会出现的原因是因为上面两种组合保证了记录的唯一性，也就没有必要使用GAP锁。

结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，相应SQL：delete from t1 where id = 10; 首先。通过id索引定位到第一条满足查询条件的记录。加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回。然后读取下一条，反复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不须要加记录X锁，可是仍旧须要加GAP锁，最后返回结束。

组合八：id上无索引。RR事务隔离级别

加锁情况()：

欣喜的是同组合四相似，Mysql会提前过滤where条件为不满足条件的提前释放锁。

组合九：Serializable

Serializable情况下。delete from t1 where id=10 通RR情况下一样会通过Gap锁解决掉幻读情况。

Serializable影响的是在select * from t1 where id=10 ,这条Sql在RR 和 RC以下都是快照度不加锁。可是在Serializable情况下会加锁。

一条复杂Sql的分析

Sql用比例如以下()：

在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，例如以下图所看到的：

若不支持ICP，不满足Index Filter的记录。也须要加上记录X锁。若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中。用红色箭头标出的X锁，是否要加，视是否支持ICP而定)。而Table Filter相应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤。因此聚簇索引上也须要X锁。

最后。选取出了一条满足条件的记录[8,hdc,d,5,good]，可是加锁的数量。要远远大于满足条件的记录数量。

结论：在Repeatable Read隔离级别下，针对一个复杂的SQL，首先须要提取其where条件。Index Key确定的范围，须要加上GAP锁；Index Filter过滤条件，视MySQL版本号是否支持ICP。若支持ICP，则不满足Index Filter的记录。不加X锁，否则须要X锁；Table Filter过滤条件，不管是否满足，都须要加X锁。

死锁的原理和分析

死锁的情况1():

死锁情况2():

上面的两个死锁用例。第一个非常好理解。也是最常见的死锁，每一个事务运行两条SQL，分别持有了一把锁。然后加还有一把锁。产生死锁。

第二个用例。尽管每一个Session都仅仅有一条语句，仍旧会产生死锁。要分析这个死锁，首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1。从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁。加锁顺序为先[1,hdc,100]，后[6,hdc,10]。

而Session 2，从pubtime索引出发。[10,6],[100,1]均满足过滤条件。相同也会加聚簇索引上的记录X锁。加锁顺序为[6,hdc,10]。后[1,hdc,100]。

发现没有。跟Session 1的加锁顺序正好相反，假设两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

參见文档