mysql innodb锁与索引

客官°小女子只卖身不卖艺 2022-05-17 00:21 164阅读 0赞

目前公司购买的是阿里云的rds服务，旗舰版的顶配目前是2000个G，我负责的数据模块有自己的一个库，这个库中只有数据相关的内容。容量已经超过当前配置容量的85%。

生产环境的mysql配置的innodb引擎存储引擎，隔离级别为RC。

所以，接下来汇总下，我的一些对mysql的一些理论和经验。

## MVCC 并发控制协议 vs 基于锁的并发控制 ##

innodb实现的是基于多版本的并发控制协议（Multi-Version Concurrency Control 简称mvcc），mvcc最大的好处就是郎朗上口的读不加锁，读写不冲突。

在mvcc并发控制协议中，读分为两种一种是快照读snapshot read，一种是当前读current read。

## 快照读 vs 当前读 ##

快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。

当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

以innodb为例：

**快照读：**简单的select操作，属于快照读，不加锁

select \* from user where ?

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

select \* from user where ? lock in share mode

select \* from user where ? for update

insert into user() values()

update user set  ? where ?

delete from user where ?

当前读中，读取的都是记录的最新版本，当前读返回的记录都会加上锁。 锁主要分为两种共享锁（简写s锁）和排他锁 （简写x锁）。

## 共享锁 vs 排他锁 ##

使用上面的语句 update 为例， 假如实际的sql如下，update user set name = "xxx" where id = 3

1.  先执行当前读，去试图获取id为3的记录

2. 试着给id为3的记录加排他锁，如果成功加上，那么就返回id为3的记录 并且 加上排他锁

3. 执行update

4. 返回成功，并且释放掉排他锁

也就是说，update操作流程中就包括了一个当前读的操作，同理，delete操作也一样。insert 语句就稍微有点不同了，可能会触发Unique Key的冲突检查，然后之后也会进行一个当前读。

一条记录上可以有多个共享锁。一条记录上有且只能由一条排他锁，有排他锁，就不能有共享锁了。

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

*  意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
 *  意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

这个我们后面再解释，可以不用考虑这两种锁。非得要解释的话，就相当于要想知道这个小区，2栋有没有604这个用户，可以直接问门卫就行了，不需要进小区爬到2栋的6楼去确认了，所以这个门卫担当的职责就是小区门卫。

记住innodb的锁，一般都是建立在索引基础上的。

## **聚簇索引 vs 堆索引** ##

聚簇索引并不是索引，不要被名字混淆了，聚簇索引是一种数据存储的方式。当表有聚簇索引时，它的数据行实际上是放在索引的叶子页中，innodb的存储引擎就是使用聚簇索引来存储数据的。

通常业务中，我们创建表的时候，都会声明主键，innodb中的默认主键就是聚簇索引，所以也就是说主键里包含了某条记录的所有列，也就是所有数据，通过主键索引，就可以获取记录所有的列。因为innodb采取的是聚簇索引，所以建立新表的时候必须选取一个聚簇索引，加入新表创建的时候没有创建主键，那么innodb就会采取下面两种方案来建立聚簇索引：

*  如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。
 *  如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚簇索引
 *  还有一种情况，就比较特殊了，这个书中是没有提的，假如我们新增了一张表，建表语句中唯一的非空索引的声明，放到了声明主键的前面，那么这张表就会使用这个唯一的非空索引来作为聚簇索引，这个道理你懂的，就跟代码顺序执行一样

从算法上分析下聚簇索引，聚簇索引肯定使用的就是平衡二叉树了（不管是btree还是b+tree），每个叶子节点保存的是主键以及主键对应的所有行数据。假如主键是自动递增的，新插入的数据对于之前的主键而言，肯定都是有序的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据，造成树的变动，从而导致了页分裂。

所以，** 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值**

## 隔离级别 ##

*  **Read Uncommited 读未提交**
    
    可以读取未提交记录。此隔离级别，不会使用，忽略。
 *  **Read Committed (RC) 读已提交**
    
    针对当前读，**RC隔离级别保证对读取到的记录加锁 (记录锁)**，存在幻读现象。
 *  **Repeatable Read (RR) 可重复读**
    
    针对当前读，**RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)**，不存在幻读现象。
 *  **Serializable**
    
    从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
    
    Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

## RC下的锁分析 ##

根据sql语句去分析加锁的情况时候，一般需要注意一下几点，并不是看着sql就能知道这个sql上应该是什么锁的

*  当前数据的隔离级别
 *  sql语句中的条件，个别字段是主键，还是唯一索引，还是组合索引
 *  针对复杂的sql，尤其要看执行计划，因为数据库自己会做一些优化，就比说sql语句where条件中的字段是有索引的，但是交给数据库执行的时候，不一定数据库会选择使用这个索引，所以复杂sql一定要看执行计划

还是之前的那个例子，我们看下面的sql，当前数据的隔离级别跟生产环境保持一致，RC

sql：update user  set xxx = xxx where id = 3;

情况1： id 是主键

很简单，对id为3的行数据，加x锁

情况2： id只是一个唯一的索引，不是主键，表中还有另外一个字段name为主键，即 table user (name primary key, id unique key). 因为id是个唯一索引，所以id为3的数据只能有一条，假设这条id为3的name为csu

那么，首先会对id为3的数据加x锁，然后回到主键索引（聚簇索引），对name为xiaoxiao的列加锁。为什么要对主键name也加锁呢，假如我们现在只对唯一索引加锁，这个时候先更新id为3的数据，如果此时并发的执行一个根据主键来更新数据的sql，update user set xxx = xxx  where name = xiaoxiao，那么就会出现错误的更新。

情况3： id是个普通索引 name为主键 即 table user ( name primary key , id key），因为id是个普通索引，所以id为3的数据可能为多条

先会对所有id为3的数据都加x锁，同时更情况2一样，也要对对应的主键行记录都加x锁

情况4： id就是个普通字段

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。这里就会有争议了，有的人会说会表锁，有的人会说只对相关的记录加x锁。

结果是虽然走了全表扫描，但是不会加表锁，也不会只对相关对应的记录加x锁，而是对全部行数据加上x锁。这是innodb存储引擎决定的，如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回。但是，mysql的server层针对这种情况做了优化，最后会释放掉非对应行上的排他锁，只保留对应行上的x锁。所以，这个存储引擎层面上对所有数据的加锁操作不能忽略。

## RR下的锁分析 ##

这个后面再补充吧，主要是这里涉及到gap锁，并且讲gap锁，又不得不讲下，很多人都会混淆的next key锁。

## msyql最左前缀 ##

b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,男)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据。

在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

## 执行计划 ##

要结合具体的业务sql，后面单独整理一篇

## 数据库本身的实现细节 ##

有的点时候，看一句sql，理论上应该走这个索引的，但是一看sql执行计划，跟理论一点都不一样，这就说明数据库本身是有自己的实现细节的。

主要的实现细节，集中在存储引擎和服务层。

后面会讲讲 Index Condition Pushdown 和 Semi-Consistent Read ，也是非常有意思的两个内容，具体没记住，需要翻翻官方的文档。