【MySQL】索引和锁

前言

本文摘自

声明：如果没有说明具体的数据库和存储引擎，默认指的是mysql中的innodb存储引擎

索引

在之前，我对索引有以下的认知：

• 索引可以加快数据库的检索速度
• 表经常进行insert/update/delete操作就不要建立索引了，换言之：索引会降低插入、删除、修改等维护任务的速度。
• 索引需要占物理和数据空间。
• 了解过索引的最左匹配原则
• 知道索引的分类：聚集索引和非聚集索引
• mysql支持hash索引和b+树索引两种

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

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

索引的基础知识

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

可以得出以下结论：

　　1. 各个数据页可以组成一个双向链表

　　2. 而每个数据页中的记录又可以组成一个单向链表

　　3. 每个数据页都会为记录生成一个页目录，通过主键查找某条记录时可以在页目录中使用二分法快速定位到对应的槽，然后遍历该槽对应分组中的记录找到指定的记录

　　4. 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where username = 'java3y' 这样没有进行任何优化的sql语句，默认会这样做：

• 遍历双向链表，定位到所在的页
• 从所在的页内中查找相应的记录，因为不是根据主键查询，因而只能遍历

很明显，在数据量很大的情况下这样查找会很慢！

索引提高检索速度

通过上面可知，加快查找速度势在必行，那么，索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)

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

很明显的是：

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

【参考资料】：mysql索引

索引降低增删改的速度

平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

b+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的(基本符合均衡的结构)【这样一来我们检索的时间复杂度就是o(logn)】！从上一节的图我们也可以

看见，建立索引实际上就是建立一颗b+树。

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

【参考资料】：b+树删除和修改

哈希索引

除了b+树之外，还有一种常见的是哈希索引。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似b+树那样从根节点到叶子节点逐级查找，只

需一次哈希算法即可立刻定位到相应的位置，速度非常快。但是hash索引有好几个局限：

• 哈希索引也没办法利用索引完成排序
• 不支持最左匹配原则
• 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
• 不支持范围查询

【参考资料】：b+树索引和哈希索引的区别

innodb支持哈希索引嘛？

主流的还是使用b+树索引比较多，对于哈希索引，innodb是自适应哈希索引的（hash索引的创建由innodb存储引擎引擎自动优化创建，用户干预不了）！

聚集和非聚集索引

• 聚集索引就是以主键创建的索引
• 非聚集索引就是以非主键创建的索引

聚集索引有2个特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义

• 页内的记录是按照主键的大小顺序排成一个单向链表。
• 各个存放用户记录的页也是根据页中记录的主键大小顺序排成一个双向链表。
• 各个存放目录项的页也是根据页中记录的主键大小顺序排成一个双向链表。

2. b+树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值。

　　我们把具有这两种特性的b+树称为聚集索引，所有完整的用户记录都存放在这个聚集索引的叶子节点处。这种聚集索引并不需要我们在mysql语句中显式的去创建，

innodb存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在innodb存储引擎中，聚集索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也

就是所谓的索引即数据。

非聚集索引也叫做二级索引

　　上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为b+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢？难道

只能从头到尾沿着链表依次遍历记录么？我们可以多建几棵b+树，不同的b+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，

再建一棵b+树

• 其中，b+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
• 索引项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

　过程如下：首先根据c2列的值，定位到非聚集索引中的某一个叶子节点，然后根据该叶子节点中的主键去聚簇索引中再查找一遍完整的用户记录，称之为回表

索引优化

在执行sql时，mysql只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引，根据b+树的性质，很容易理解各种常见的mysql索引优化思路

1. 优先使用自增key作为主键

假设用4b的自增key作为索引，则m可达到512，层高仅有3。参考这里，使用自增的key有两个好处：

　　1. 自增key一般为int等整数型，key比较紧凑，这样m可以非常大，而且索引占用空间小。最极端的例子，如果使用50b的varchar（包括长度），那么m = 4 * 1024 /

54m = 75.85 ~= 76，深度最大log(76/2)(10^7) = 4.43 ~= 5 ，再加上cache缺失、字符串比较的成本，时间成本增加较大。同时，key由4b增长到50b，整棵索引树

的空间占用增长也是极为恐怖的（如果二级索引使用主键定位数据行，则空间增长更加严重）

　　2. 自增的性质使得新数据行的插入请求必然落到索引树的最右侧，发生节点分裂的频率较低，理想情况下，索引树可以达到“满”的状态。索引树满，一方面层高更低，一

方面删除节点时发生节点合并的频率也较低

优化案例：

曾使用varchar(100)的列做过主键，存储containerid，过了3、4天100g的数据库就满了，之后增加了自增列作为主键，containerid作为unique的二级索引，时间、空间优化效果相当显著。

2. 最左前缀匹配 

• 索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等）
• 遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找，也就是此时不会通过索引来查询
• =、in自动优化顺序，mysql会自动优化这些条件的顺序

3. 索引列不能参与计算 

有索引列参与计算的查询条件对索引不友好（甚至无法使用索引）。原因很简单，如何在节点中查找到对应key？如果线性扫描，则每次都需要重新计算，成本太高；如果二分

查找，则需要确定大小关系

4. 能扩展就不要新建索引

如果已有索引(a)，想建立索引(a, b)，尽量选择修改索引(a)为索引(a, b)。mysql可以直接在索引a的b+树上，经过分裂、合并等修改为索引(a, b)

5. 不需要建立前缀有包含关系的索引

如果已有索引(a, b)，则不需要再建立索引(a)，但是如果有必要，则仍然需考虑建立索引(b)。

6. 选择区分度高的列作索引

这很容易理解。如，用性别作索引，那么索引仅能将1000w行数据划分为两部分（如500w男，500w女），索引几乎无效。区分度的公式是count(distinct <col>) /

count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0

锁

锁的相关知识又跟存储引擎，索引，事务的隔离级别都是关联的

数据库锁知识

不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)，即使我们不会这些锁知识，我们的程序在一

般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了，只会在某些特定的场景下才需要手动加锁。

• 对于update、delete、insert语句，innodb会自动给涉及数据集加排他锁（x)
• myisam在执行查询语句select前，会自动给涉及的所有表加读锁，在执行增、删、改操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预

表锁

首先要明确的是，用户很少手动加表锁

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

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

不同的存储引擎支持的锁粒度是不一样的：innodb行锁和表锁都支持、myisam只支持表锁！innodb只有通过索引条件检索数据才使用行级锁，否则，innodb使用表

锁也就是说，innodb的行锁是基于索引的！

表锁下又分为两种模式：

• 表读锁（table read lock）
• 表写锁（table write lock）
• 从下图可以清晰看到，在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！

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

从上面已经看到了：读锁和写锁是互斥的，读写操作是串行。

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

 注：

 

行锁

innodb和myisam有两个本质的区别：innodb支持行锁、innodb支持事务

innodb实现了以下两种类型的行锁：

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

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

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

mvcc

mvcc(multi-version concurrency control)多版本并发控制，可以简单地认为：mvcc就是行级锁的一个变种(升级版)。在表锁中我们读写是阻塞的，基于提升并发性能

的考虑，mvcc一般读写是不阻塞的(很多情况下避免了加锁的操作)。可以简单的理解为：对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与

老版本共存，使得读取时可以完全不加锁。

事务的隔离级别

事务的隔离级别就是通过锁的机制来实现，锁的应用最终导致不同事务的隔离级别，只不过隐藏了加锁细节，事务的隔离级别有4种：

• read uncommitted：会出现脏读，不可重复读，幻读
• read committed：会出现不可重复读，幻读
• repeatable read：会出现幻读(mysql默认的隔离级别，但是repeatable read配合gap锁不会出现幻读！)
• serializable：串行，避免以上的情况

read uncommitted：出现的现象--->脏读：一个事务读取到另外一个事务未提交的数据，例子：a向b转账，a执行了转账语句，但a还没有提交事务，b读取数据，发现

自己账户钱变多了！b跟a说，我已经收到钱了。a回滚事务【rollback】，等b再查看账户的钱时，发现钱并没有多...

出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据

read committed：出现的现象--->不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改，例如：a查询数据库得到

数据，b去修改数据库的数据，导致a多次查询数据库的结果都不一样【危害：a每次查询的结果都是受b的影响的，那么a查询出来的信息就没有意思了】

repeatable read：避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据

如果还是不太清楚，我们来看看innodb的mvcc是怎么样的吧《高性能mysql》

至于虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不

一致，幻读的重点在于新增或者删除 (数据条数变化)，不可重复读的重点是修改，？

乐观锁和悲观锁

无论是read committed还是repeatable read隔离级别，都是为了解决读写冲突的问题，现在考虑一个问题：有一张数据库表user，只有id、name字段，现在有2个请求

同时候操作表a，过程如下：（模拟更新丢失，虽然不是很恰当）

　1. 操作1查询出name="zhangsan" 

　2. 操作2也查询出name="zhangsan" 

　3. 操作1把name字段数据修改成lisi并提交

　4. 操作2把name字段数据修改为wangwu并提交

那么操作1的更新丢失啦，即一个事务的更新覆盖了其它事务的更新结果，解决上述更新丢失的方式有如下3种：

• 使用serializable隔离级别，事务是串行执行的！
• 乐观锁
• 悲观锁

悲观锁

我们使用悲观锁的话其实很简单(手动加行锁就行了)：select * from xxxx for update，在select 语句后边加了for update 相当于加了排它锁(写锁)，加了写锁以后，其

他事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.也就是说，如果操作1使用select ... for update，操作2就无法对该条记录修改了，即可避免更新丢失。

乐观锁

乐观锁不是数据库层面上的锁，需要用户手动去加的锁。一般我们在数据库表中添加一个版本字段version来实现，例如操作1和操作2在更新user表的时，执行语句如下：

update a set name=lisi,version=version+1 where id=#{id} and version=#{version}，此时即可避免更新丢失。

【参考资料】：

间隙锁gap

间隙锁只会在repeatable read隔离级别下使用

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在

的记录，叫做“间隙（gap)”。innodb也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101

select * from emp where empid > 100 for update;

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

innodb使用间隙锁的目的有2个：

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

死锁

并发的问题就少不了死锁，在mysql中同样会存在死锁的问题

【参考资料】：mysql死锁问题分析

【参考资料】：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

innodb基于行锁还实现了mvcc多版本并发控制，mvcc在隔离级别下的read committed和repeatable read下工作。mvcc实现了读写不阻塞