Mysql 索引与优化 加锁处理分析

Mysql  索引与优化及加锁处理分析

1 Mysql介绍

1.1 Mysql简介

定义：MySQL 是一款优秀的开源小型关系数据库系统。

历史：

1.       1979年，始于一款报表工具、数据引擎。

2.       1996年，发布 MySQL1.0 版本。

3.       2000年，成立 MySQL AB公司。

4.       2008年，Sun 以 10 亿元收购了 MySQLAB 公司。

5.       2009年，Oracle 以 76 亿元收购了 Sun 公司。

特点（从DB产品对比角度）：

开源，基于通用公共许可证协议（GPL）可以免费使用修改。引擎，采用开放式存储引擎架构，有多种免费或付费引擎可选择。可用度高，通过优秀的架构设计及相关手段能实现企业级高可用性、高扩展性。免费，有效降低企业投资运营成本。趋势，产品成熟度逐渐提高，占行业应用比重增大，相关产业链丰富，从业人员逐步形成规模效应。

特点（从开发管理角度）：不简单，MySQL由于引擎众多，且各自内部机制和实现技术差别很大，完全掌握需要花很多精力，并不像很多人想象中的那样简单。

版本：企业版本（MySQLEnterprise）：收费，更稳定，完整的配套监控工具，官方技术支持。

社区版本（MySQL）：免费，没有企业版稳定，没有完整监控工具及官方支持。

下载：

http://dev.mysql.com/downloads/mysql/

1.2 MySQL体系结构

图1-1 Mysql体系结构

1.3 MySQL存储引擎

1.3.1 InnoDB

1.       支持事务

行锁设计，提供并发性，同时支持MVCC

MVCC——多版本并发控制

支持外键，以确保数据完整性

独立的ibd文件存储表结构和数据

其他特性：插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用功能

适用场合：强调可靠性和需要支持事务处理

1.3.2 MyISAM

1.       不支持事务

2.       表锁设计

3.       全文索引

4.       存储表由MYD和MYI组成，分别存放数据和索引

5.       使用数据压缩和索引优化

6.       特点：可靠性高、适用范围广、检索速度快

7.       适用场合：强调数据检索速度

1.3.3 NDB

1.       Network database，一种集群存储引擎

2.       采用share nothing 集群架构，每个节点都拥有自己的内存和磁盘

3.       特点：数据全部放在内存中，主键查询速度快，增加NDB存储节点，可线性提高性能,Join连接的网络开销大,对RAM需求大

4.       适用场合：冗余、高可用性、负载均衡

1.3.4 Memory

1.       将表中数据存放在内存中

2.       默认使用哈希索引

3.       由一个.frm的扩展名来指明它存储的表定义

4.       特点：服务器重启时，Memory表里的数据全部丢失, 只支持表锁，并发性能差, 不支持TEXT和BLOB类型, 按照定常方式存储变长字段(varchar)数据高速度，低延迟，不适合频繁写

5.       适用场合：存储临时数据、数据仓库的纬度表

1.3.5 Archive

1.       目标：提供高速的插入和压缩功能

2.       特点：只支持Insert和Select操作, 支持行锁来实现高并发的插入操作,     不支持事务, 采用zlib算法压缩存储，压缩比例达1:10

3.       适用场合：存储归档数据，如日志信息

1.3.6 Maria

1.     目标：取代原有的MyISAM存储引擎

2.     特点：缓存数据和索引文件, 行锁设计, 提供MVCC功能, 支持事务和非事务安全

1.3.7 综合比较

图1-2 存储引擎综合比较

1.4 InnoDB存储引擎

1.       由Innobase Oy公司开发（2006被Oracle收购）

2.       第一个完整支持ACID事务的MySQL存储引擎(ACID:原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）)

3.       行锁设计

4.       支持MVCC

5.       支持外键

6.       一致性非锁定读

7.       应用广泛：Yahoo、Facebook、Youtube、Flickr、Wow、   SecondLife …

1.5 InnoDB存储引擎——体系架构

图1-3体系架构

 

图1-4 内存结构

缓冲池：存放各种数据缓存，将数据库文件按页读取，LRU算法维护缓存，定时flush脏页到文件

日志缓冲：将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件

额外内存池：对一些数据结构本身信息的内存分配

2 MySQL加锁处理分析

2.1 背景

2.1.1 MVCC Snapshot Read vs Current Read

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

在MVCC并发控制中，读操作可以分成两类：快照读(snapshot read)与当前读(currentread)。快照读，读取的是记录的可见版本(有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

 

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

n select * from table where ?;

 

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

n select * from table where ? lock in share mode;

n select * from table where ? for update;

n insert into table values (…);

n update table set ? where ?;

n deletefrom table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁(共享锁)外，其他的操作，都加的是X锁(排它锁)。

为什么将插入/更新/删除操作，都归为当前读？可以看看下面这个更新操作，在数据库中的执行流程：

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁(current read)。待MySQLServer收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

2.1.2 Cluster Index：聚簇索引

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

2.1.3 Clustered and Secondary Indexes

Every InnoDB table has a special indexcalled the clustered index where the data forthe rows is stored. Typically, the clustered index is synonymous(同义词) with theprimary key. To get the best performance from queries,inserts, and other database operations, you must understand how InnoDB uses theclustered index to optimize the most common lookup and DML operations for eachtable.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clusteredindex.

If you do notdefine a PRIMARY KEY for your table, MySQL picks(采用) the firstUNIQUE index that has onlyNOT NULL columns as the primary keyandInnoDB uses itas the clustered index.

If the table hasno PRIMARY KEY orsuitable UNIQUEindex,InnoDBinternally generates a hidden clustered index on a synthetic column containingrow ID values. The rows are ordered by the ID thatInnoDB assigns to the rows in such atable. The row ID is a 6-byte field that increases monotonically as new rowsare inserted. Thus, the rows ordered by the row ID are physically in insertionorder.

(InnoDB内部生成一个隐藏的聚集索引列包含行ID值的合成)

How the Clustered Index Speeds Up Queries

Accessing a rowthrough the clustered index is fast because the row data is on the same pagewhere the index search leads. If a table is large, the clustered indexarchitecture often saves a disk I/O operation when compared to storageorganizations that store row data using a different page from the index record.(For example, MyISAM uses one file for data rows and another for index records.)

How Secondary Indexes Relate to the Clustered Index

All indexesother than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columnsfor the row, as well as the columns specified for the secondary index.InnoDB uses this primary key value tosearch for the row in the clustered index.

If the primarykey is long, the secondary indexes use more space, so it is advantageous tohave a short primary key.

2.1.4 2PL：Two-Phase Locking

传统RDBMS加锁的一个原则，就是2PL(二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。

从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。

2.1.5 Isolation Level

隔离级别：Isolation Level，也是RDBMS的一个关键特性。相信对数据库有所了解的朋友，对于4种隔离级别：Read Uncommited，ReadCommitted，Repeatable Read，Serializable，都有了深入的认识。本文不打算讨论数据库理论中，是如何定义这4种隔离级别的含义的，而是跟大家介绍一下MySQL/InnoDB是如何定义这4种隔离级别的。

此隔离级别，不会使用，忽略。

Ø Read Committed(RC)

 

快照读忽略，本文不考虑。

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

Ø Repeatable Read(RR)

 

快照读忽略，本文不考虑。

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

Ø Serializable

 

从MVCC并发控制退化为基于锁的并发控制。部分快照读与当前读，所有的读操作均为当前读，读加读锁(S锁)，写加写锁(X锁)。

Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

2.1.6聚簇索引

聚簇索引不是一个独立的索引类型，而是一种数据存储的方式。

InnoDB使用B-Tree来实现聚簇索引，并把索引和数据存放在同一结构中。

在有聚簇索引的表中，索引和行是存放在同一个叶子节点（B-Tree）中的。

"聚簇"表示索引值相邻的行的存储位置一般也是相邻的——一般来说是这样，但在某些情况下不是。

一张表只能有一个聚簇索引，因为同一份数据不可能出现两个地方。

 

图中展示了一个聚簇索引的示例，需要注意的是：只有叶子节点才包括了数据行的值，非叶子节点只存了索引字段。

在某些DB实现中，可以选择按照哪个索引做聚簇，但是MySQL的所有存储都不支持这样——InnoDB只能按照主键做聚簇。

如果表中没有定义主键，InnoDB会选择一个“非空且有唯一性约束“的索引来做聚簇，如果没有这样的索引，则会定义一个不可见的主键，并以这个主键来聚簇。

从图中的布局还可以看到，整个索引以B-Tree方式组织，其中叶子节点包含了行的主键值和这一行所有字段的值，非叶子节点只是单纯的提供查找路径信息。

在非主键索引中，叶子节点存放的是索引字段的值和一个指向聚簇索引的”行指针“——对应行的主键的值。

InnoDB clustersrecords together only within a page.Pages with adjacent key values may bedistant from each other.

InnoDB 的聚簇是页面级别的，即聚簇索引值相邻的行会被存入同一个页面，当这个页面存满时，接下来的相邻行会被存入另外一个页面，而这两个页面可能会离的很远。如图中的布局所示，索引值为1～10的行被存在第一个页面，11～20的行被存在另外一个页面，数据被聚簇在单个页面内，而页面1和页面2的磁盘位置却可能离的很远。

聚簇的好处：

1. You can keeprelated data close together——在设计一个邮件系统时，按照（用户id，邮件id）来做聚簇，同一个用户的邮件会被存在相邻的磁盘区域，这样获取用户所有邮件的时候，磁盘IO开销就很小。

2. Data accessis fast——聚簇索引把索引和数据放在同一个B树中，在以聚簇索引获取整行数据会很快。

3. Queries thatuse covering indexes can use the primary key values contained at the leafnode——只要是以聚簇索引为条件来查找数据，都能利用到”覆盖索引“这个特性，效率高。

聚簇的不足：

1. 聚簇是数据在磁盘上的组织方式，所以只能带来磁盘IO上的收益，如果表的数据能全部载入内存，聚簇索引就没什么帮助了。

2. 数据插入（Insert）的速度依赖于插入顺序——大量数据按照聚簇索引顺序插入会很快，如果乱序插入，就会比较慢，并且在大量乱序插入数据后，最好对表做一次rebuild操作。

3. 更新聚簇索引字段的开销很大，因为系统不得不维护所有有关的页面。

4. 聚簇的数据按照”页面“来存放，当一个列必须要放入一个已经满了的页面时，就会触发页面分裂——页面分裂会带来附加的IO开销，也会占用更多的磁盘空间。

5. 在聚簇的表上做全表扫描会很慢，特别是数据的密集度很低（每个页面都有大量的空闲项）或因为多次页面分裂后，数据存放无序时。

6. 非主键索引会比想象中的要大，因为非主键索引除了要存放索引字段，还需要一个指向聚簇索引的入口指针。

7. 以非主键索引为条件访问不包含在索引中的字段时，需要做2次索引扫描。

The last pointcan be a bit confusing. Why would a secondary index require two index lookups?The answer lies in the nature of the "row pointers" the secondaryindex stores. Remember, a leaf node doesn't store a pointer to the referencedrow's physical location; rather, it stores the row's primary key values.

That means thatto find a row from a secondary index, the storage engine first finds the leafnode in the secondary index and then uses the primary key values stored thereto navigate the primary key and find the row. That's double work: two B-Treenavigations instead of one. (In InnoDB, the adaptive hash index can help reducethis penalty.)

为什么非主键索引需要两次索引扫描？这关系到非主键索引的一个特性——行指针——非主键索引的叶子节点存放的不是指向数据行实际物理位置的指针，而是行的主键值。

当按照非主键索引查找数据时，存储引擎首先按照非主键索引取得对应的”行指针“，然后再按照这个指针查询主键索引获取整行的值，所以产生了两次B-Tree查询。

书中原文以 cluster index和secondary index来区分两种索引，针对InnoDB，可以直接说成是主键和非主键。

 

2.2一条简单SQL的加锁实现分析

在介绍完一些背景知识之后，本文接下来将选择几个有代表性的例子，来详细分析MySQL的加锁处理。当然，还是从最简单的例子说起。经常有朋友发给我一个SQL，然后问我，这个SQL加什么锁？就如同下面两条简单的SQL，他们加什么锁？

Ø SQL1：select * from t1 where id = 10;

Ø SQL2：delete from t1 where id = 10;

 

针对这个问题，该怎么回答？我能想象到的一个答案是：

Ø SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。

Ø SQL2：对id = 10的记录加写锁(走主键索引)。

 

这个答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不足，这个问题没有答案。如果让我来回答这个问题，我必须还要知道以下的一些前提，前提不同，我能给出的答案也就不同。要回答这个问题，还缺少哪些前提条件？

Ø 前提一：id列是不是主键？

Ø 前提二：当前系统的隔离级别是什么？

Ø 前提三：id列如果不是主键，那么id列上有索引吗？

Ø 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？

Ø 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

没有这些前提，直接就给定一条SQL，然后问这个SQL会加什么锁，都是很业余的表现。而当这些问题有了明确的答案之后，给定的SQL会加什么锁，也就一目了然。下面，我将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁？

注：下面的这些组合，我做了一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤(索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。

 

Ø 组合一：id列是主键，RC隔离级别

Ø 组合二：id列是二级唯一索引，RC隔离级别

Ø 组合三：id列是二级非唯一索引，RC隔离级别

Ø 组合四：id列上没有索引，RC隔离级别

Ø 组合五：id列是主键，RR隔离级别

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

Ø 组合七：id列是二级非唯一索引，RR隔离级别

Ø 组合八：id列上没有索引，RR隔离级别

Ø 组合九：Serializable隔离级别

 

排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另一个角度来说，只要你选定了一种组合，SQL需要加哪些锁，其实也就确定了。接下来，就让我们来逐个分析这9种组合下的SQL加锁策略。

注：在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。

2.2.1组合一：id主键+RC

这个组合，是最简单，最容易分析的组合。id是主键，ReadCommitted隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。如下图所示：

结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。

2.2.2 组合二：id唯一索引+RC

这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：

此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ’d’对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

2.2.3 组合三：id非唯一索引+RC

相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：

根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

2.2.4 组合四：id无索引+RC

相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁(违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

2.2.5组合五：id主键+RR

上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。

组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。

2.2.6组合六：id唯一索引+RR

与组合五类似，组合六的加锁，与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

注：根据博文《MySQL加锁处理分析》下面的评论，id为唯一索引，针对id的并发等值删除操作，有可能会产生死锁。具体死锁的场景与分析，可参考本人的另一篇文章：《一个最不可思议的MySQL死锁分析》。

2.2.7组合七：id非唯一索引+RR

还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。

组合七，RepeatableRead隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：

此图，相对于组合三：[id列上非唯一锁，Read Committed]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读(例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录(记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录(幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项(id =10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上(X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

有心的朋友看到这儿，可以会问：既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，去不需要加GAP锁呢？

首先，这是一个好问题。其次，回答这个问题，也很简单。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。其实，针对此问题，还有一个更深入的问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？此问题留给大家思考。

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

2.2.8组合八：id无索引+RR

组合八，RepeatableRead隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：

如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？

在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，

不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合四：[id无索引, ReadCommitted]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistentread如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，但是设置了innodb_locks_unsafe_for_binlog参数。更详细的关于semi-consistentread的介绍，可参考我之前的一篇博客：MySQL+InnoDB semi-consitent read原理及实现分析。

结论：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

2.2.9组合九：Serializable

针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL1：select * from t1 where id =10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

2.2.10一条复杂的SQL

写到这里，其实MySQL的加锁实现也已经介绍的八八九九。只要将本文上面的分析思路，大部分的SQL，都能分析出其会加哪些锁。而这里，再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：

如图中的SQL，会加什么锁？假定在Repeatable Read隔离级别下(Read Committed隔离级别下的加锁情况，留给读者分析。)，同时，假设SQL走的是idx_t1_pu索引。

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？具体的介绍，建议阅读我之前的一篇文章：SQL中的where条件，在数据库中提取与应用浅析。在这里，我直接给出分析后的结果：

Ø Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。

 

Ø Index Filter：userid = ‘hdc’。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。

 

Ø Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

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

从图中可以看出，在RepeatableRead隔离级别下，由Index Key所确定的范围，被加上了GAP锁；Index Filter锁给定的条件(userid = ‘hdc’)何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了IndexCondition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁(图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

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

2.3死锁原理与分析

本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：

Ø 可以根据MySQL的加锁规则，写出不会发生死锁的SQL；

Ø 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；

下面，来看看两个死锁的例子(一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：

上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条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恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的，分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。

2.4 总结

做一个简单的总结，要做的完全掌握MySQL/InnoDB的加锁规则，甚至是其他任何数据库的加锁规则，需要具备以下的一些知识点：

Ø 了解数据库的一些基本理论知识：数据的存储格式(堆组织表vs 聚簇索引表)；并发控制协议(MVCC vs Lock-Based CC)；Two-Phase Locking；数据库的隔离级别定义(Isolation Level)；

Ø 了解SQL本身的执行计划(主键扫描vs 唯一键扫描vs 范围扫描vs 全表扫描)；

Ø 了解数据库本身的一些实现细节(过滤条件提取；Index Condition Pushdown；Semi-Consistent Read)；

Ø 了解死锁产生的原因及分析的方法(加锁顺序不一致；分析每个SQL的加锁顺序)

有了这些知识点，再加上适当的实战经验，全面掌控MySQL/InnoDB的加锁规则，当不在话下。

3索引与优化

3.1 选择索引的数据类型

MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：

越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
    简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
    尽量避免NULL：应该指定列为NOTNULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

3.1.1选择标识符

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。

 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。

3.2索引入门

对于任何DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影响不是很大，但是，当随着数据量的增加，性能会急剧下降。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假 设存在组合索引it1c1c2(c1,c2)，查询语句select *from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select* from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

3.2.1索引的类型

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

3.2.2 B-Tree索引

假设有如下一个表：

CREATE TABLEPeople (

  last_name varchar(50)    not null,

  first_name varchar(50)    not null,

  dob       date           not null,

  gender     enum('m', 'f') not null,

  key(last_name, first_name, dob)

);

其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
(1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
(2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
(3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找lastname以J开始的人，这仅仅使用索引中的第1列。
(4)匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
(5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找lastname为Allen，而first name以字母K开始的人。
(6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制：
(1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
(2) 不能跳过某一索引列。例如，你不能利用索引查找lastname为Smith且出生于某一天的人。
(3) 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%'AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

3.3 高性能的索引策略

3.3.1聚簇索引(ClusteredIndexes)

聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表 只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
聚簇索引的结构大致如下：

资料来源：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

 注：叶子页面包含完整的元组，而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引，但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

3.3.2索引与加锁

索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：首先，即使InnoDB行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存 在开销。其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。
InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组 数。但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对 InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。
来看个例子：

create tableactor(

actor_id intunsigned NOT NULL AUTO_INCREMENT,

name     varchar(16) NOT NULL DEFAULT '',

password   varchar(16)NOT NULL DEFAULT '',

PRIMARYKEY(actor_id),

KEY    (name)

) ENGINE=InnoDB

insert intoactor(name,password) values('cat01','1234567');

insert intoactor(name,password) values('cat02','1234567');

insert intoactor(name,password) values('ddddd','1234567');

insert intoactor(name,password) values('aaaaa','1234567');

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id < 4

AND actor_id <> 1 FOR UPDATE;

该查询仅仅返回2---3的数据，实际已经对1---3的数据加上排它锁了。InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询（而没有进行过滤操作，WHERE中第二个条件已经无法使用索引了）

mysql>EXPLAIN SELECT actor_id FROM test.actor

   -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G

***************************1. row ***************************

          id: 1

 select_type:SIMPLE

       table: actor

        type: index

possible_keys:PRIMARY

         key: PRIMARY

     key_len: 4

         ref: NULL

        rows: 4

       Extra: Using where; Using index

1 row in set(0.00 sec)

mysql>

表明存储引擎从索引的起始处开始，获取所有的行，直到actor_id<4为假，服务器无法告诉InnoDB去掉元组1。
为了证明row 1已经被锁住，我们另外建一个连接，执行如下操作

SET AUTOCOMMIT=0;

BEGIN;

SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

该查询会被挂起，直到第一个连接的事务提交释放锁时，才会执行（这种行为对于基于语句的复制(statement-based replication)是必要的）。
如上所示，当使用索引时，InnoDB会锁住它不需要的元组。更糟糕的是，如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。