innodb

Pgsql系列文章链接：

pg的编译安装 (Jan 1, 2022)
pg高可用 (Jan 1, 2022)
pg为何需要HOT (Jan 1, 2022)
pg常用查询 (Jan 1, 2022)
LSM-Tree (Jan 1, 2022)
innodb (Jul 1, 2022)
mysql基础 (Jul 1, 2022)
乐观锁和悲观锁 (Jul 1, 2022)
全文索引 (Jul 1, 2022)
问题 (Jul 1, 2022)

InnoDB Buffer Poll

InnoDB Buffer Poll的大小是由参数innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%。

执行show engine innodb status，可以看到"buffer pool hit rate"字样，显示的就是当前的内存命中率。

如果一个Buffer Pool满了，而从磁盘读入一个数据页，就肯定要淘汰一个旧数据页的。如果使用LRU来淘汰的话，而我们要扫描的这个200G的表是一个历史数据表，平时没有业务访问它。

那么，按照这个算法扫描的话，就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说 Buffer Pool 里面主要放的是这个历史数据表的数据。

所以，InnoDB对LRU算法做了改进：在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中LRU_old指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。

       read
          \
+----------\-------------------------+
|           \   state1               |
|head        \     LRU_old      tail |
|  v          v      v           v   |
|[P1]->[P2]->[P3]->[...]->[Pu]->[Pm] |
+------------------------------------+
                 v
+------------------------------------+
|               state2               |
|head              LRU_old      tail |
|  v                 v           v   |
|[P3]->[P1]->[P2]->[...]->[Pu]->[Pm] |
+------------------------------------+
                 v
+-------------------------------------------+
|               state3                      |
|head                   LRU_old        tail |
|  v                       v            v   |
|[P3]->[P1]->[P2]->[...]->[Px]->[...]->[Pu] |
+-------------------------------------------+

图中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在LRU_old处。
处于 old 区域的数据页，每次被访问的时候都要做下面这个判断： 1秒这个时间，是由参数innodb_old_blocks_time控制的。其默认值是 1000，单位毫秒。
- 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
- 如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。

可以看到，这个策略最大的收益，就是在扫描这个大表的过程中，如果它的大小没有超过Buffer Poll的3/8，就不会对young区域造成任何影响。

change buffer

当需要更新一个数据页时，

如果数据页在内存中就直接更新，
而如果这个数据页还没有在内存中的话
- 唯一索引的更新操作：需要先判断该记录是否存在，所以不会使用change buffer
- 普通索引：
  - InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
  - 在下次查询需要访问这个数据页的时候，将 change buffer 中的操作应用到原数据页上。
change buffer 在内存中有拷贝，也会被写入到磁盘上(存放在系统表空间ibdata1上)。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。
- 除了访问这个数据页会触发 merge 外，
- 系统有后台线程会定期 merge，
- 在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。
- innodb_change_buffer_max_size为50的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

merge操作的执行流程如下：

从磁盘读入数据页到内存（老版本的数据页）；
从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

change buffer 的主要目的就是将记录的变更动作缓存下来

在一个数据页做 merge 之前，change buffer 记录的变更越多，收益就越大。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

change buffer写操作的执行流程：

Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
将上述两个动作记入 redo log 中（图中 3 和 4）。
- 这样即使mysql异常重启，change buffer也能找得回来。

change buffer读操作的执行流程：

+------------------------------------------------------+
|InnoDB buffer pool             +--------------------+ |
|                               |     Page1          | |
|+--------------------------+   +--------------------+ |
||  change buffer           |   |(a,b)|(id1,k1)|(c,d)|-+-->read Page1
||+------------------------+|   +--------------------+ |
|||"add (id2,k2) to Page2" ||   +--------------------+ |
||+------------------------+|   |     Page2          | |
|+--------------------------+ \ +--------------------+ |
|                              >|(e,f)|(id2,k2)|(g,h)|-+-->read Page2
|                              >+--------------------+ |
+-----------------------------/------------------------+
                             /
+-------------------------------+
|     data(t.ibd)               |
|+----------+------+-----------+|
||  Page1   |      |  Page2    ||
||(a,b)(c,d)|......|(e,f)(g,h) ||
|+----------+------+-----------+|
+-------------------------------+

读 Page 1 的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL 之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用的。你可以看一下图 3 的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。
要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

刷账页

内存页分为脏页和干净页：

当内存数据页跟磁盘数据页的内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

什么情况会引发数据库的flush过程呢？

redo log写满了。
- 这个时候，系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
- 一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议将redo log设置成4个1GB的文件。
- 但不慎将redo log设置成了1个100MB的文件，会发生什么情况呢？
- 由于redo log设置得太小，很快就会被写满，这时候系统就不得不停止所有更新，去推进check_point
  - 这个时候，磁盘的压力会很小，因为更新操作被阻塞，相当于分配给写redo log的磁盘资源没有被使用，但是白白占着。
系统内存不足。
- 当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。
  - 如果淘汰的是“脏页”，就要先将脏页写到磁盘。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：
    - 内存里存在，内存里就肯定是正确的结果，直接返回
    - 内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回
MySQL认为系统“空闲”的时候，会疯狂的刷账页
- MySQL正常关闭的时候，这时候，MySQL会把内存的脏页都flush到磁盘上
- MySQL异常崩溃后，如果业务对MySQL的请求很少，那么重启速度会很慢，因为MySQL认为此时很闲，会疯狂地刷账页。
  - 例如，业务代码事先将权限表写入到内存中，而其他请求都依赖于内存中的权限。MySQL异常重启后，权限表还没来得及写入到内存，所以此时只有一个将权限表写入内存的MySQL请求，这个时候MySQL就会认为此时很闲而疯狂的刷账页。
  - 可以写个脚本，每隔 0.5 秒发一个请求，执行一个简单的 SQL 查询，告诉数据库其实我现在很忙，脏页刷得慢一点。
MySQL认为系统“很忙”的时候，会尽可能少的刷账页，但是还是会见缝插针地找时间，只要有机会就刷一点“脏页”

控制InnoDB刷脏页的的比例因子

innodb_io_capacity设置成磁盘的IOPS

磁盘的IOPS可以通过fio这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

1
2

$ fio -filename=mytest -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync \
   -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

mysql并不会把100%的磁盘资源都用来刷脏页，而是会考虑这两个因素:

脏页比例

innodb_max_dirty_pages_pct=脏页比例上限，默认值是75%
M: 当前脏页比例

1
2
3

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

F1的计算公式:

F1(M){
   if M>=innodb_max_dirty_pages_pct then
   return 100;
   return 100*(M/innodb_max_dirty_pages_pct);
}

redo log写盘速度 F2(N): N越大，F2(N)就越大，N=|check_point - write_pos|

按照R%的速度刷脏页：$R=max{F1,F2}$

连坐机制innodb_flush_neighbors:

值为1：采用“连坐”机制
- 如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉
- 如果是机械硬盘，一般采用这种策略，机械硬盘的随机IOPS一般只有几百
值为0：不找邻居，自己刷自己的。
- 如果是SSD这类IOPS比较高的设备，就设置为0，以减少SQL语句的响应时间

1. 事务

1.1 隔离性与隔离级别

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

1.2 如何避免长事务对业务的影响？

首先，从应用开发端来看：

确保使用了set autocommit=1，可以通过mysql的general_log日志来确定。 – 在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；
对只读事务，去除bigin/commit语句。
业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：

监控information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill。 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
percona的pt-kill这个工具不错，推荐使用；
如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespace设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。 undo log的作用： – 刷账页时会用到 – 计算某行的历史版本

1.3 “快照"在MVCC里是怎么实现的？

一致性视图(read-view)：基于此实现基于整库的快照，只作用于RR和RC这两种隔离级别 InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

InnoDB 为每个事务构造了一个数组，用来保存这个事务_启动瞬间_，当前“启动了还未提交”的所有事务 ID。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
在读提交隔离级别下，每一个语句执行前都会重新创建一个新的一致性视图。

可见性规则和一致性读一致性读(consistent read): 从最新版本开始找，如果不可见，就找到它的上一个版本，如果上一个版本也不可见，就继续往前找。一致性试图把所有的 row trx_id分成了几种不同的情况。

          低水位     高水位
            v          v
{已提交事务}[未提交事务](未开始事务)

对于当前事务的启动瞬间来说，一个数据版本的 row `trx_id`，有以下几种可能：
1. 如果落在{}部分，表示这个版本是已提交的事务，这个数据是可见的；
2. 如果落在()部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3. 如果落在[]部分，那就包括两种情况
  a. 若 row `trx_id`在数组中，表示这个版本是由还没提交的事务生成的，不可见；
  b. 若 row `trx_id`不在数组中，表示这个版本是已经提交了的事务生成的
     或者是当前事务自己生成的，可见。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读” 除了update语句外，select语句如果加锁，也是当前读。

读锁(S锁，共享锁): select k from t where id=1 lock in share mode;
写锁(X锁，排他锁): select k from t where id=1 for update;

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

+----------------------+------------------------------+------------------------------+
|事务A                 |事务B                          |事务C                         |
+----------------------+------------------------------+------------------------------+
|start transaction with|                              |                              |
|consistent snapshot;  |                              |                              |
+----------------------+------------------------------+------------------------------+
|                      |start transaction with        |                              |
|                      |consistent snapshot;          |                              |
+----------------------+------------------------------+------------------------------+
|                      |                              |update t set k=k+1 where id=1;|
+----------------------+------------------------------+------------------------------+
|                      |update t set k=k+1 where id=1;|                              |
|                      |select k from t where id=1;   |                              |
+----------------------+------------------------------+------------------------------+
|select k from t where |                              |                              |
|id=1;                 |                              |                              |
|commit;               |                              |                              |
+----------------------+------------------------------+------------------------------+
|                      |commit;                       |                              |
+----------------------+------------------------------+------------------------------+

注意：begin/start transaction命令并不是一个事务的起点，在执行到它们之后的第一个操作
InnoDB表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction
with consistent snapshot这个命令。

假设:

事务A开始前，系统里面只有一个活跃事务ID是99；
事务A、B、C的版本号分别是100、101、102，且当前系统里只有这四个事务；
三个事务开始前，(1,1)这一行数据的row trx_id是90。

为了简化分析，只画出跟事务A查询逻辑相关的操作：

RR场景：
+------------+ +--------------+ +------------------+
|{事务A(100)}| |              | |                  |
|{[99],101  }| |{事务B(101)  }| |                  |  [  90 ] 历史             row `trx_id`
|            | |{[99,100],102}| |{事务C(102)       |  [(1,1)] 版本2                 v
|            | |              | |{[99,100,101],103}|     ^ <-----------undo log: [102]{set k=k+1}
|            | |              | |set k=k+1 --------|->[ 102 ] 历史
|            | |              | |                  |  [(1,2)] 版本1
|            | |              | |                  |     ^ <-----------undo log: [101]{set k=k+1}
|            | |set k=k+1 ----|-|------------------|->[ 101 ] 当前
|{get k   }  | |              | |                  |  [(1,3)] 版本
|{[99],101}  | |              | |                  |
+------------+ +--------------+ +------------------+

从图中可以看出：
  * 第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。
    这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。
  * 第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。
    这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。
    注意：事务B在更新的时候，当前读拿到的数据是(1,2)，一致性读拿到的是(1,1)，
          由于是更新语句，所以最终使用的是当前读(1,2)，并执行k=k+1操作，
          这样更新后生成的新版本数据是(1,3)
  * 事务 A 查询语句的读数据流程是这样的：
    * 找到 (1,3) 的时候，判断出 row trx_id=101，等于高水位，处于红色区域，不可见；
    * 接着，找到上一个历史版本，一看 row trx_id=102，比高水位大，处于红色区域，不可见；
    * 再往前找，终于找到了（1,1)，它的 row trx_id=90，比低水位小，处于绿色区域，可见。

RC场景：
+--------------+ +--------------+ +------------------+
|{事务A(100)}  | |              | |                  |
|{[99],101  }  | |{事务B(101)  }| |                  |  [  90 ] 历史
|              | |{[99,100],102}| |{事务C(102)       |  [(1,1)] 版本2
|              | |              | |{[99,100,101],103}|     ^
|              | |              | |set k=k+1 --------|->[ 102 ] 历史
|              | |              | |                  |  [(1,2)] 版本1
|              | |              | |                  |     ^
|              | |set k=k+1 ----|-|------------------|->[ 101 ] 当前
|              | |{get k       }| |                  |  [(1,3)] 版本
|              | |{[99,100],103}| |                  |
|{get k       }| |              | |                  |
|{[99,101],103}| |              | |                  |
|              | |commit        | |                  |
+--------------+ +--------------+ +------------------+

从图中可以看出：
  * 第一个有效更新是事务 C，把数据从 (1,1) 改成了 (1,2)。
    这时候，这个数据的最新版本的 row trx_id 是 102，而 90 这个版本已经成为了历史版本。
  * 第二个有效更新是事务 B，把数据从 (1,2) 改成了 (1,3)。
    这时候，这个数据的最新版本（即 row trx_id）是 101，而 102 又成为了历史版本。
    注意：事务B在更新的时候，当前读拿到的数据是(1,2)，一致性读拿到的也是(1,2)，
          由于是更新语句，所以最终使用的是当前读(1,2)，并执行k=k+1操作，
          这样更新后生成的新版本数据是(1,3)
  * 事务 A 查询语句的读数据流程是这样的：
    * 找到 (1,3) 的时候，判断出 row trx_id=101，小于高水位，大于低水位，处于黄色区域；
       -- (1,3)还没提交，属于情况3-a，不可见；
    * 接着，找到上一个历史版本(1,2)，判断出 row trx_id=102，小于高水位，大于低水位，处于黄色区域；
       -- (1,2)提交了，属于情况3-b，可见；所以事务A查询语句返回的结果是 k=2；

2. b+树索引

索引使用的时间长了就会变慢，这个时候就需要重建索引了: alter table T engine=InnoDB。

2.1 主键索引和非主键索引

mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

ID索引:
                [600]
               /    \
              /      \
          [300]      [600]
         /     \        \
        /       \        \
   [200]         [500]    600
   /   \         /   \      \  
  /     \       /     \      \ 
 V       V     V       V      V
[100]<>[200]<>[300]<>[500]<>[600]
[R1 ]<>[R2 ]<>[R3 ]<>[R4 ]<>[R5 ]

k索引:
                [6  ]
               /    \
              /      \
          [3  ]      [6  ]
         /     \        \
        /       \        \
   [2  ]         [5  ]   [6  ]
   /   \         /   \      \  
  /     \       /     \      \ 
 V       V     V       V      V
[1  ]<>[2  ]<>[3  ]<>[5  ]<>[6  ]
[100]<>[200]<>[300]<>[500]<>[600]

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

2.2 覆盖索引

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

现在，我们一起来看看这条 SQL 查询语句的执行流程：

在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束。

可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。

但是，如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。这就是“覆盖索引”。

有的时候，select的字段中包含的字段不是主键，要使用到“覆盖索引”，就需要使用到联合索引了。

2.3 最左前缀原则

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

对于联合索引(name, age)，

["李四",20]["王五",10] ["张六",30]["张三",10]["张三",10] ["张三",20]
[ID1      ][ID2      ][ID3      ][ID4      ][ID5      ][ID6      ]

可以看到，索引项是按照索引定义里面出现的字段顺序排序的，

当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到 ID4，然后向后遍历得到所有需要的结果。

如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。

可以看到，这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

2.4 索引下推

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

["李四",20]["王五",10] ["张六",30]["张三",10]["张三",10] ["张三",20]
[ID1      ][ID2      ][ID3      ][ID4      ][ID5      ][ID6      ]

我们还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。然后判断其他条件是否满足。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

3. 锁

3.1 元数据锁(meta data lock, MDL)

MDL不需要显式使用，在访问一个表的时候会被自动加上。

MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL：

当对一个表做增删改查操作的时候，加 MDL 读锁；
当要对表做结构变更操作的时候，加 MDL 写锁。
事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

给个小表加个字段，结果整个库挂了的原因：

# 备注：这里的实验环境是MySQL 5.6 (5.6开始支持online ddl)
# Online DDL的过程是这样的：
# 1. 拿MDL写锁
# 2. 降级成MDL读锁
# 3. 真正做DDL
# 4. 升级成MDL写锁
# 5. 释放MDL锁
# 1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”
+---------------+--------------+-------------+---------------+
|sessionA       |sessionB      |sessionC     |sessionD       |
+---------------+--------------+-------------+---------------+
|begin;         |              |             |               |
+---------------+--------------+-------------+---------------+
|select *       |              |             |               |
|from t limit 1;|              |             |               |
+---------------+--------------+-------------+---------------+
|               |select *      |             |               |
|               |from t limit1;|             |               |
+---------------+--------------+-------------+---------------+
|               |              |alter table t|               |
|               |              |add f int;   |               |
|               |              |(blocked)    |               |
+---------------+--------------+-------------+---------------+
|               |              |             |select *       |
|               |              |             |from t limit 1;|
|               |              |             |(blocked)      |
+---------------+--------------+-------------+---------------+

我们可以看到sessionA先启动，这时候会对表t加一个MDL读锁。由于sessionB需要的也是 MDL读锁，因此可以正常执行。

之后sessionC会被blocked，是因为sessionA的MDL读锁还没有释放，而sessionC需要MDL 写锁，因此只能被阻塞。

如果只有sessionC自己被阻塞还没有什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被sessionC阻塞，因为所有对表的增删改查操作都需要先申请MDL读锁。

如果这个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话，这个库的线程很快就会爆满。

如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的information_schema库的innodb_trx表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
如果你要更新的是一个热点表，而且不得不加个字段，该怎么做？。这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

MariaDB已经合并了AliSQL的这个功能，所以这两个开源分支目前都支持DDL NOWAIT/WAIT n这个语法。
1 2

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

3.2 行锁

两阶段锁协议（只在RR隔离级别下生效）在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

所以，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

而RC隔离级别下，语句执行过程中加上的行锁，在语句执行完成后，会释放这些行锁，不需要等到事务提交。

例子假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

语句1：从顾客 A 账户余额中扣除电影票价；
语句2：给影院 B 的账户余额增加这张电影票价；
语句3：记录一条交易日志。

也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

所以，按照两阶段锁协议，应该将语句2放在最后，以保证影院余额这一行的行锁在一个事务中不会停留很长时间。

死锁和死锁监测如果这个影院做活动，可以低价预售一年内所有的电影票，而且这个活动只做一天。于是在活动时间开始的时候，你的 MySQL 就挂了。你登上服务器一看，CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？

+------------------------------+------------------------------+
| 事务A                        | 事务B                        |
+------------------------------+------------------------------+
|begin;                        |                              |
|update t set k=k+1 where id=1;| begin;                       |
+------------------------------+------------------------------+
|                              |update t set k=k+1 where id=2;|
+------------------------------+------------------------------+
|update t set k=k+1 where id=2;|                              |
+------------------------------+------------------------------+
|                              |update t set k=k+1 where id=1;|
+------------------------------+------------------------------+

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout来设置，默认值是50s。当一个线程因为锁等待超过50s的时候，就退出。可能是正常的锁等待，也可能是因为死锁造成的锁等待。
另一种策略是，发起__死锁检测__，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为 on，表示开启这个逻辑。默认值是on。

死锁监测：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

如果每个新来的线程都被阻塞在这一行，都要判断会不会是由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁监测操作就是1000*1000=100万这个量级的。这期间要消耗大量的CPU资源。因此，你会看到CPU利用率很高，但是每秒却执行不了几个事务。

如何解决由热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的CPU资源。

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
另一个思路是控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
如果团队里暂时没有数据库方面的专家，无法在数据库层控制并发度怎么办？你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

查看死锁执行show engine innodb status命令会输出很多信息，有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。

3.3 间隙锁

注意，对于行锁和间隙锁来说，锁是加在索引上的。
注意，间隙锁只会在可重复读级别下才会生效，而且在MySQL中，可重复读级别下并不会出现幻读问题。
注意，读提交隔离级别在外键场景下还是有间隔锁，相对比较复杂，暂时不考虑。
注意，在读提交级别下时，就没有间隙锁了，但同时，要解决可能出现的数据和日志不一致问题，需要把binlog_format设置为row。
如果使用读提交级别，就要考虑，在备份期间，备份线程用的是可重复读，而业务线程用的是读提交。 同时存在两种事务隔离级别，会不会有问题？

3.3.1 幻读是什么，幻读有什么问题？

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

# 以下操作的事务隔离级别是可重复读
# 实际上，由于间隙锁的存在，session B的update操作会被阻塞；这里假设没有间隙锁：
+--+-------------------------------------------+----------------+--------------+
|  |session A                                  |session B       |session C     |
+--+-------------------------------------------+----------------+--------------+
|T1|begin;                                     |                |              |
|  |select * from t where d=5 for update;/*Q1*/|                |              |
|  |result: (5,5,5)                            |                |              |
+--+-------------------------------------------+----------------+--------------+
|T2|                                           |update t set d=5|              |
|  |                                           |where id=0;     |              |
+--+-------------------------------------------+----------------+--------------+
|T3|select * from t where d=5 for update;/*Q2*/|                |              |
|  |result: (0,0,5),(5,5,5)                    |                |              |
+--+-------------------------------------------+----------------+--------------+
|T4|                                           |                |insert into t |
|  |                                           |                |values(1,1,5);|
+--+-------------------------------------------+----------------+--------------+
|T5|select * from t where d=5 for update;/*Q3*/|                |              |
|  |result: (0,0,5),(1,1,5),(5,5,5)            |                |              |
+--+-------------------------------------------+----------------+--------------+
|T6|commit;                                    |                |              |
+--+-------------------------------------------+----------------+--------------+

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询相对于前一次查询突然多了一行或者少了一行。

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

幻读有什么问题？

首先是语义上的。session A 在 T1 时刻就声明了， “我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
其次，是数据一致性的问题。我们知道，锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包括了数据和日志在逻辑上的一致性。

+--+-------------------------------------------+----------------+--------------+
|  |session A                                  |session B       |session C     |
+--+-------------------------------------------+----------------+--------------+
|T1|begin;                                     |                |              |
|  |select * from t where d=5 for update;/*Q1*/|                |              |
|  |update t set d=100 where d=5;              |                |              |
|  |result: (5,5,100)                          |                |              |
+--+-------------------------------------------+----------------+--------------+
|T2|                                           |update t set d=5|              |
|  |                                           |where id=0;     |              |
+--+-------------------------------------------+----------------+--------------+
|T3|select * from t where d=5 for update;/*Q2*/|                |              |
|  |result: (0,0,5),(5,5,100)                  |                |              |
+--+-------------------------------------------+----------------+--------------+
|T4|                                           |                |insert into t |
|  |                                           |                |values(1,1,5);|
+--+-------------------------------------------+----------------+--------------+
|T5|select * from t where d=5 for update;/*Q3*/|                |              |
|  |result: (0,0,5),(1,1,5),(5,5,100)          |                |              |
+--+-------------------------------------------+----------------+--------------+
|T6|commit;                                    |                |              |
+--+-------------------------------------------+----------------+--------------+

* T2时刻，session B事务提交，在binlog日志中写入`update t set d=5 where id=0`;
* T4时刻，session C事务提交，在binlog日志中写入`insert into t values(1,1,5)`;
* T6时刻，session A事务提交，在binlog日志中写入`update t set d=100 where d=5`；

它们放在一起就是：

update t set d=5 where id=0; /*(0,0,5)*/

insert into t values(1,1,5); /*(1,1,5)*/

update t set d=100 where d=5;/*所有d=5的行，d改成100*/

这个时候，你应该看出问题了。这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,0,100)、(1,1,100) 和 (5,5,100)。

也就是说，id=0和id=1这两行，发生了数据不一致。这个问题很严重，是不行的。

到这里，我们再回顾一下，这个数据不一致到底是怎么引入的？

我们分析一下可以知道，这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的。

但是给表所有行都加上锁，只能阻止T2时刻的update操作，却无法阻止T4时刻的insert操作。

所以，间隙锁(Gap Lock)应运而生。

3.3.2 间隙锁

和行锁一样，只有使用“当前读”的时候，才会加锁。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

读锁和写锁的冲突关系:

+-----+-----+-----+
|     |读锁 |写锁 |
+-----+-----+-----+
|读锁 |兼容 |冲突 |
+-----+-----+-----+
|写锁 |冲突 |冲突 |
+-----+-----+-----+

间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、 (15,20]、(20, 25]、(25, +supermum]。

因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supermum，这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和next-key lock的引入，帮我们解决了幻读的问题，但是也导致同样的语句锁住更大的范围，这其实是影响了并发度的。

间隙锁的加锁规则前提：

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

两个“原则”、两个“优化”和一个“bug”：

通用原则:
- 加锁的基本单位是 next-key lock。
- 查找过程中访问到的对象才会加锁。
唯一索引：
- 等值查询: next-key lock 退化为行锁。
  - select * from t where id>=10 and id<11 for update; 对于id=10的等值查询，只加了id=10的行锁
  - 对于10<id<11的范围查询，会一直找到第一个不满足id<11的记录为止，也就是id=15，因此需要加 next-key lock: (10,15]
  - 最终的加锁范围是：[10,15]
- 范围查询(bug): 从左至右访问，直到第一个不满不条件的行为止。
  - select * from t where id>10 and id<=15 for update; 对于id=15的等值查询，只加了id=15的行锁
  - 对于10<id<15的范围查询，会一直找到第一个不满足id<15的记录为止，也就是id=20，因此需要加next-key lock: (10, 20]
  - 最终的加锁范围是：(10,20]
普通索引等值查询：
- 向右遍历next-key lock时最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  - update t set d=d+1 where id=7; id上有索引，加锁范围是 (5,10)
  - select id from t where c=5 lock in share mode;
    - c是普通索引，所以找到5之后还会沿着链表往后遍历，直到第一个不满足c=5的记录为止，而这个记录就是c=10
      - 所以，使用limit可以减小锁的大小，因为可以减少要遍历的行数
    - 根据原则二访问到的都要加锁，所以给(5,10]加next-key lock锁，而且使用到了覆盖索引(没有回表)，所以主键索引上没有加任何锁。
      - 如果是update或for update操作就不一样了，会额外给满足条件的行加上行锁。
      - 如果是select * from t where c=5 lock in share mode; 那么回表操作只会给主键所在的行加行锁。
    - 根据优化二，c=10不满足等值查询，所以最终的间隙锁是：(5,10)
  - 对于数据集：
```
索引c     +--------------+
+---------|--------------|-----------------+
|  [0]  [5]  [10]  [10]  [15]  [20]  [25]  | c
|  [0]  [5]  [10]  [30]  [15]  [20]  [25]  | id
+---------|--------------|-----------------+
          +--------------+
            加锁区域
```
    当执行delete from t where c=10;的时候，它的加锁范围是怎样的：
    - 先找到第一个c=10的记录(c=5,id=5)，然后向右遍历到(c=15,id=15)，根据原则2，最终的加锁范围是( (c=5,id=5), (c=15,id=15) )

一个死锁的例子

+----------------------------+------------------------------+
|session A                   |session B                     |
+----------------------------+------------------------------+
|begin;                      |                              |
|select id from t where c=10 |                              |
|lock in share mode;         |                              |
+----------------------------+------------------------------+
|                            |update t set d=d+1 where c=10;|
|                            |(blocked)                     |
+----------------------------+------------------------------+
|insert into t values(8,8,8);|                              |
+----------------------------+------------------------------+
|                            |ERROR 1213(40001);            |
|                            |Deadlock found when trying to |
|                            |get lock; try restarting      |
|                            |transaction                   |
+----------------------------+------------------------------+

为什么：

session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
session B 的 update 语句也要在索引 c 上加 next-key lock(5,10]和间隙锁(10,15) ，进入锁等待；（其中间隙锁(5,10)和(10,15)成功了，但是行锁10失败了）
然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

专题: pgsql

本文发表于 2022-07-01，最后修改于 2022-07-01。

本站永久域名「 jiavvc.top 」，也可搜索「后浪笔记一零二四」找到我。

上一篇 « mysql基础下一篇 » 1消息中间件NATS基础知识