Mysql的索引和锁

索引和锁在数据库中可以说是非常重要的知识点了，在面试中也会经常会被问到的。本文力求简单讲清每个知识点，希望大家看完能有所收获。声明：如果没有说明具体的数据库和存储引擎，默认指的是MySQL中的InnoDB存储引擎。

一、索引

我们对索引有以下的认知：

索引可以加快数据库的检索速度

• 表经常进行INSERT/UPDATE/DELETE操作就不要建立索引了，换言之：索引会降低插入、删除、修改等维护任务的速度。
• 索引需要占物理和数据空间。
• 了解过索引的最左匹配原则
• 知道索引的分类：聚集索引和非聚集索引
• Mysql支持Hash索引和B+树索引两种

看起来好像啥都知道，但面试让你说的时候可能就GG了：

• 使用索引为什么可以加快数据库的检索速度啊？
• 为什么说索引会降低插入、删除、修改等维护任务的速度。
• 索引的最左匹配原则指的是什么？
• Hash索引和B+树索引有什么区别？主流的使用哪一个比较多？InnoDB存储都支持吗？
• 聚集索引和非聚集索引有什么区别？
  …

1、聊聊索引的基础知识

首先Mysql的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表
• 而每个数据页中的记录又可以组成一个单向链表
• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写

select * from user where username = 'Java3y';

这样没有进行任何优化的sql语句，默认会这样做：

• 页与页之间是双向链表，页内是单链表。需要沿着双向链表，从第一页开始，依次遍历每一页，最终找到对应的数据。很明显，在数据量很大的情况下这样查找会很慢！

2、索引提高检索速度

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过**“目录”**就可以很快地定位到对应的页上了！其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

3、索引降低增删改的速度

B+树是平衡树的一种。
平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。如果一棵普通的树在极端的情况下，是能退化成链表的(树的优点就不复存在了)

B+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的，基本符合矮矮胖胖(均衡)的结构【这样一来我们检索的时间复杂度就是O(logn)】！从上一节的图我们也可以看见，建立索引实际上就是建立一颗B+树。

• B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。
• 要维持平衡树，就必须做额外的工作，正因为这些额外的工作开销，导致索引会降低增删改的速度。

4、哈希索引

除了B+树之外，还有一种常见的是哈希索引。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

• 本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。
  
  看起来哈希索引很牛逼啊，但其实哈希索引有好几个局限(根据他本质的原理可得)：
• 哈希索引也没办法利用索引完成排序
• 不支持最左匹配原则
• 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希冲突问题。
• 不支持范围查询

5、InnoDB支持哈希索引吗？

查看mysql官方文档，从下面的图中可以得知，mysql 是支持hash索引的，但支持和不支持又和具体的存储引擎有关系。从图中，看到InnoDB是支持B+ tree索引，这是我们众所周知的。

我们具体查一下InnoDB文档那一部分：

表中表明，InnoDB不支持hash索引，但又给出了一个特殊的解释，InnoDB存储引擎是支持hash索引的，不过，hash索引的创建由InnoDB存储引擎引擎自动优化创建，不能人为干预是否在一张表中生成哈希索引！

6、聚集和非聚集索引

聚集索引以及非聚集索引用的是B+树索引。

• 聚集clustered）索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序，聚集索引确定了表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据，由于聚集索引规定了数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。
• 非聚集索引，该索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 索引是通过二叉树的数据结构来描述的，聚集索引在叶子节点存储的是表中的数据，非聚集索引在叶子节点存储的是主键和索引列。使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据，(拿到主键再查找这个过程叫做回表)。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。

举个例子：现在我创建了索引(username,age)，在查询数据的时候：

select username , age from user where username = 'Java3y' and age = 20；

很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了。

7、索引总结

索引在数据库中是一个非常重要的知识点！上面谈的其实就是索引最基本的东西，要创建出好的索引要顾及到很多的方面：

1. 最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。
2. 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。
3. 索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = ‘2016-06-06’ 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP(‘2016-06-06’)。
4. 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
5. 单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

二、锁

在mysql中的锁看起来是很复杂的，因为有一大堆的东西和名词：排它锁，共享锁，表锁，页锁，间隙锁，意向排它锁，意向共享锁，行锁，读锁，写锁，乐观锁，悲观锁，死锁。这些名词有的博客又直接写锁的英文的简写：X锁，S锁，IS锁，IX锁，MMVC。锁的相关知识又跟存储引擎，索引，事务的隔离级别都是关联的。

1、为什么需要学习数据库锁知识

不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)，一般也就听过常说的乐观锁和悲观锁，了解过基本的含义之后就没了。

即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了：

• 对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁；
• MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预；

只会在某些特定的场景下才需要手动加锁，学习数据库锁知识就是为了:

• 能让我们在特定的场景下派得上用场
• 更好把控自己写的程序
• 在跟别人聊数据库技术的时候可以搭上几句话
• 构建自己的知识库体系！在面试的时候不虚

2、表锁简单介绍

首先，从锁的粒度，我们可以分成两大类。

• 表锁：开销小，加锁快，不会出现死锁，锁定力度大，发生锁冲突概率高，并发度最低；
• 行锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，并发度高；

不同的存储引擎支持的锁粒度是不一样的。

• InnoDB行锁和表锁都支持
• MyISAM只支持表锁

在RR和Serial隔离级别下，InnoDB只有通过索引条件检索数据时才使用行级锁，否则，InnoDB将使用Next-Key Lock实现锁表的效果，而在RU与RC隔离级别下，无论查询条件列上是否有索引，都不会锁表，只锁行。

表锁下又分为两种模式：
表读锁（Table Read Lock）
表写锁（Table Write Lock）
从下图可以清晰看到，在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！

读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁；
读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁；
写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁；

读锁和写锁是互斥的，读写操作是串行，如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates。

值得注意的是，MyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式，在MyISAM中默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。但是InnoDB存储引擎是不支持的。

3、行锁细讲

上边简单讲解了表锁的相关知识，我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：

• InnoDB支持行锁
• InnoDB支持事务

从上面也说了：我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的！我们应该更加关注行锁的内容，因为InnoDB一大特性就是支持行锁！

InnoDB实现了以下两种类型的行锁。

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。也叫做读锁，读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。
• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁，写锁是排他的，写锁会阻塞其他的写锁和读锁。

看完上面的有没有发现，在一开始所说的：X锁，S锁，读锁，写锁，共享锁，排它锁其实总共就两个锁，只不过它们有多个名字罢了。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
• 意向锁也是数据库隐式帮我们做了，不需要程序员操心！

快照读和当前读

在MySQL中select分为快照读和当前读，执行下面的语句

select * from table where id = ?;

执行的是快照读，读的是数据库记录的快照版本，是不加锁的（这种说法在隔离级别为Serializable中不成立）。

执行

select * from table where id = ? lock in share mode;

会对读取记录加S锁 (共享锁)，执行

select * from table where id = ? for update

会对读取记录加X锁 (排他锁)，那么加的是表锁还是行锁呢？

针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示:

• Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据
• Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据
• Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生
• Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

4、MVCC和事务的隔离级别

数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别。事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节。

MVCC(Multi-Version Concurrency Control)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的（所以说MVCC很多情况下避免了加锁的操作）。

MVCC实现的读写不阻塞正如其名：多版本并发控制—>通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，快照有两个级别：

• 语句级别：针对Read committed隔离级别
• 事务级别：针对Repeatable read隔离级别

我们在初学的时候已经知道，事务的隔离级别有4种：

• Read uncommitted：会出现脏读，不可重复读，幻读
• Read committed：会出现不可重复读，幻读
• Repeatable read：会出现幻读（但在Mysql实现的Repeatable read隔离级别下，由于配合使用了gap锁不会出现幻读)
• Serializable：串行，避免以上的情况

Read committed避免脏读的做法其实很简单：就是把释放锁的位置调整到事务提交之后，此时在事务提交前，其他进程是无法对该行数据进行读取的。但Read committed会出现不可重复读的现象：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改（A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样，危害是A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了）。

上面也说了，Read committed是语句级别的快照！每次读取的都是当前最新的版本！Repeatable read避免不可重复读采用的是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取到当前事务版本的数据。

来看看InnoDB的MVCC是怎么样的吧，以下摘抄自《高性能MySQL》。

5、间隙锁

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

跟行锁有冲突关系的是“另外一个行锁”。但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁锁定索引记录间隙，确保索引记录的间隙不变，间隙锁是针对事务隔离级别为可重复读或以上级别而已的。Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

值得注意的是：间隙锁只会在Repeatable read隔离级别下使用。

例子：假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101

Select * from  emp where empid > 100 for update;

上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的有两个：

• 为了防止幻读，上面也说了，Repeatable read隔离级别下通过GAP锁即可避免了幻读。
• 满足恢复和复制的需要：MySQL的恢复机制要求在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

6、锁总结

上面说了一大堆关于MySQL数据库锁的东西，现在来简单总结一下。表锁其实我们程序员是很少关心它的：

• 在MyISAM存储引擎中，当执行SQL语句的时候是自动加的。
• 在InnoDB存储引擎中，如果没有使用索引，表锁也是自动加的（死锁的概率大大增加，所以为表添加合理的索引很重要）。

现在我们大多数使用MySQL都是使用InnoDB，InnoDB支持行锁：

• 共享锁–读锁–S锁
• 排它锁–写锁–X锁

在默认的情况下，select是不加任何行锁的，事务可以通过以下语句显示给记录集加共享锁或排他锁。

• 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
• 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。
• 乐观锁其实是一种思想，正如其名，认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段来实现。
• 悲观锁用的就是数据库的行锁【在select 语句后边加了 for update相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改】，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务。

InnoDB基于行锁还实现了MVCC多版本并发控制，MVCC在隔离级别下的Read committed和Repeatable read下工作。MVCC能够实现读写不阻塞！InnoDB实现的Repeatable read隔离级别配合GAP间隙锁已经避免了幻读！

转载声明：

本文转载自知乎博主Java3y的文章：https://zhuanlan.zhihu.com/p/40396971