search

annidy / notes

0 stars 0 forks source link

MySQL 实战 45 讲 #328

Open annidy opened 1 week ago

annidy commented 1 week ago

01 | 基础架构：一条SQL查询语句是如何执行的？

image 查询缓存在8.0默认去除了；连接器管理所有客户端连接，连接器避免不了要保存上下文等，有一定开销。 查询最大连接数：

SHOW VARIABLES LIKE 'max_connections';

分析器是分析这条SQL是的语法是否正确，是否是查询语句，执行的是个什么操作。 优化器分析这条查询语句怎么执行，使用那个索引。由于SQL是声明式语言，如果里面有多个查询过程，执行顺序没有定义。优化器需要计算不同执行的效率，这里的优化空间不小。 慢查询通过long_query_time配置（查询同上），默认10s。慢查询日志slow_query_log_file 开启命令：

SET GLOBAL slow_query_log = ON;

执行器翻译优化器的指令，调用引擎的接口，（引擎提供的接口类似I/O），把结果存发给客户端。执行器不缓存数据？ 引擎则是真实的读取每一行或查询索引

annidy commented 1 week ago

02 | 日志系统：一条SQL更新语句是如何执行的？

redo log就是常用的WAL（Write-Ahead log），它是InnoDB才支持的特性。 binlog（归档）是Server层的日志，它是逻辑日志，记录的是操作日志。

典型的日志写入顺序为 redo log(prepare) -> binlog -> redo log(commit)。redo log的二次提交主要是为了crash恢复：在binlog写入之前crash的下次启动回滚，否则commit；binlog 则是为了数据库回滚，比如误删数据库，可以利用上一次的备份+binlog前一次操作恢复到删除之间的状态。

binlog日志支持3种记录模式1. Statement 记录SQL语句；2. ROW 记录更新前后的行；3. Mix 混合模式。第二种方式有利于恢复，但是空间占用比较大。 查看记录模式使用SHOW VARIABLES LIKE 'binlog_format';

备份数据库

简单的mysqldump备份

mysqldump -u $MYSQL_USER -p$MYSQL_PASS $DATABASE | gzip > "$BAK_DIR/$DATABASE-$BAK_DATE.sql.gz"

恢复数据库

  1. 还原全量备份 
    mysql -u 用户名 -p 密码 数据库名 < /path/to/your/backup_yesterday.sql
  2. 获取备份结束位置 使用SHOW MASTER LOGS;或SHOW BINARY LOGS;查看昨天binlog的结束时间
  3. 应用binlog 
    mysqlbinlog --start-position=开始位置 --stop-datetime="YYYY-MM-DD HH:MM:SS" /path/to/binlogs/binlog_file_1 | mysql -u 用户名 -p 密码

以上是一个比较粗劣的过程。注意恢复期间需要停止mysql服务。

annidy commented 1 week ago

03 | 事务隔离：为什么你改了我还看不见？

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

查看隔离级别

show variables like 'transaction_isolation';

实现原理

image

每个对数据的修改都有回滚日志，通过read view的索引就可以知道事务视图创建时的值时什么。 当read view没有任何事务引用时，视图会自动清理。所以建议不要使用长事务，会妨碍清理。

另外事务还会影响ALTER表。

查看事务

可以去information_schema.innodb_trx表中查看正在运行的事务

  1. 查询执行超过60s的事务 
    select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

隐性事务

隐性事务是指没有用begin命令启动的事务。当set autocommit=0时，执行SQL语句（比如SELECT）默认开启了一个事务，而且这个事务不会提交，直到主动commit/rollback或线程or连接结束。 默认autocommit=1，即执行一条SQL语句后自动提交（原子操作）。某些ORM为了方便开启了隐性事务，对并发性能影响很大。

annidy commented 1 week ago

04 | 深入浅出索引

最常用的三种索引数据结构：哈希表、有序数组和搜索树。哈希表不适用于范围查找，只在KV数据库中有用。有序数组插入删除太慢，一般只用于固定数据集。因此，数据库的选择只剩搜索树。

二叉树最接近理论上限，但是它的需要多次跳转，对磁盘访问不友好，所以数据都使用的N叉树。（跳表、LSM搜索树在有些场景中也在使用）

InnoDB 的索引模型

每一个索引都对应一个B+树。每个表都有一个主键，每一行按照主键的顺序保存在B+树中。 示例：

create table T(id int primary key, k int not null, index(k)) engine=InnoDB;

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

image

主键索引下存的是整行数据，非主键索引下存的是主键的值。 所以非主键查询有一个回表过程，相对主键会慢一点。

索引在磁盘中需要保持顺序，因此主键最好是自增的（AUTO_INCREMENT），这样写入新数据直接在后面，性能会比较好（为什么UUID不适合做主键，原因在此）。使用业务字段做主键，需要考虑二级索引保存主键的成本（列举了一个身份证号的例子）。

重建索引

alter table T drop index k;
alter table T add index(k);

重建索引能清理B树中因为删除而导致的空洞，所以有一定的现实意义。但是如果要重建主键，正确的写法是

alter table T engine=InnoDB;

虽然把上面的index改为primary key语法没有错，但是drop主键后，表会重新创建一个默认的rowid作为主键，重新创建表，此时再add主键又重复建表的操作，等于第一次drop完全没有意义。

联合索引和索引覆盖

索引覆盖是指索引的值里有我们想要的数据，不需要回表。由于索引中保存的是主键，如果我们把主键扩展，加入一些业务字段，称之为联合索引。

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就是联合索引。表创建后，可以通过id_card很快的查找到name和age，同时基于最左前缀原则，name的搜索也很快。

INDEX和KEY关键字是同义词，也可以用下面的方式创建 CREATE TABLE tuser( id int PRIMARY KEY, id_card varchar(32) DEFAULT NULL, name varchar(32) DEFAULT NULL, age int(11) DEFAULT NULL, ismale tinyint(1) DEFAULT NULL, INDEX(id_card), -- 单独为id_card创建索引 INDEX(name_age (name, age)) -- 为name和age创建联合索引，并命名该索引为name_age ) ENGINE=InnoDB;

索引下推是另一种数据库优化索引使用的技术，比如select * from tuser where name like '张%' and age=10 and ismale=1;，age的条件也可以使用name_age索引。

annidy commented 1 week ago

06 | 全局锁和表锁：给表加个字段怎么有这么多阻碍？

全局锁

全局锁可以阻止数据库上所有写操作，常用于备份、校验操作

FLUSH TABLES WITH READ LOCK;   -- 上锁

UNLOCK TABLES; -- 解锁

这个操作比较少用，一般的备份工具比如mysqldump，都是通过开启一个事务方式来确保拿到一致性视图

事务不局限于单个表，开启事务整个数据库都有一个快照

表级锁

表锁加锁的粒度更低、更细，可以阻止读。列表全局锁是读写锁，表锁是互斥

LOCK TABLES t1 READ, t2 WRITE;    -- 上锁。表 t1 禁止别人读

UNLOCK TABLES; -- 解锁

现实中，遇到的表锁同时是MDL（metadata lock），当我们alter table（DDL）时，引擎默认加一个MDL写锁，其他人在使用表时，默认加MDL读锁。 如果数据库一直有人在SELECT和INSERT表，是不是ALTER就没机会执行了？在操作系统中这是可能的（饥饿），数据库会用排队方式解决这个问题，但是会导致ALTER后面的查询都阻塞了。同时ALTER也会提示

Waiting for table metadata lock

前面提到开发者可能不小心引入了长事务，长事务也会加MDL读锁！MDL的锁也有超时，超时后会提示

Lock wait timeout exceeded; try restarting transaction

这个值是通过innodb_lock_wait_timeout控制，默认是50s。但是客户端的超时一般更短，超时重试又会新建一个session，如果没有规避策略，业务服务器有可能很快被打爆。

ALTER导致的线上事故有很多，规避方式总结下来主要有：

  1. 更新表选一个线上用户少的时段
  2. 更新前检查一下事务表，是否异常
  3. 有异常的事务时，用 SHOW PROCESSLIST;找到锁等待的ID，KILL掉
  4. 升级数据版本，有的ALTER支持NOWAIT/WAIT n语法
annidy commented 1 week ago

07 | 行锁功过：怎么减少行锁对性能的影响？

前面提过，MySQL通过read view实现多事务读，但是更新操作需要加行锁，且行锁的释放需要等到事务结束。所以当两个事务更新同一行数据时，另一个必须等前一个事务提交后才能继续。

死锁检测

由于行锁太多，现实中必然会出现死锁。MySQL的策略是发起死锁检测，如果发现两个事务死锁了，就主动回滚其中的某个事务。 死锁检测的复杂度是O(n)。注意这个检测是不管有没有死锁，只要事务更新时遇到了行锁，就会发起死锁检测。

死锁检测可以关闭，但不推荐。

数据库CPU飙高，但TPS不高，有可能是死锁检测的锅

查看事务统计的命令 SHOW GLOBAL STATUS LIKE 'Com_commit'; -- 数据库上次启动以来已经提交的事务数量 SHOW GLOBAL STATUS LIKE 'Com_rollback'; -- 数据库上次启动以来已经回滚的事务数量

避免死锁

死锁不会损坏数据库，只会让事务回滚，客户端需要做重试。但是生产中还是需要尽量避免死锁

  1. 如果多个事务需要更新同一行，尽量把它放到事务后面，减少加锁的时间
  2. 可以尝试将热点行拆成多个行（比如示例中的电影院收入金额），客户端随机选
  3. 客户端事务串行化（或者通过中间件），数据库的锁比语言提供的锁慢太多
annidy commented 6 days ago

08 | 事务到底是隔离的还是不隔离的？

read-view的原理

  1. 每个事务有一个唯一ID，且严格顺序递增
  2. 每次事务更新数据时，都会生产一个新的数据版本（row），并把transaction id赋值给数据版本的row trx_id
  3. “快照”的原理：以事务启动的时刻基准，比事务id小的数据版本是可接受的，如果大于就往前找旧版本

    事务启动时刻一般不是begin的时间，而是第一次访问数据的时刻

InnoDB在实现时，为每个事务构造了一个数组，数组中保存事务启动瞬间，还活跃的事务ID集合。上面第3步实际上是”比事务id小且不在活跃事务ID集合中“，否则可能看到前一个事务未提交的数据。

更新逻辑

image

上图中，事务B的set k=k+1是在102版本上累加；如果是先k'=get k，再set k=k‘+1，结果会不一样。

set k=k+1称之为当前读，它看起来有悖快照隔离（不是可重复读，像是读已提交那种隔离级别），但大部分SQL都是这样实现的（为了效率）。

如果希望k'=get k和set k=k+1的效果一致，需要给它加锁

select k from t where id=1 for update;  -- 排他锁，可阻止其事务写

但是，加了锁就不支持多事务并发。魔鬼藏在细节中。

case: update失败

update使用的是当前读，它看到的数据和select返回的不一样。比如

begin;
select * from t;
update t set k=0 where id=k;
select * from t;
commit;

由于事务开始后做了一次select，建立了快照，后面的select是用的前面的快照，而条件where语句中的id和k，不是快照的值，是redo log中最新的数据。

annidy commented 6 days ago

09 | 普通索引和唯一索引，应该怎么选择？

两种索引都是B树，区别是重复索引在查找的时候，需要判断下一个是不是重复值。性能差距不大。 拉开差距的是InnoDB实现是引入的特性。change buffer主要用于提高对非聚集索引（非唯一普通索引）更新操作的性能。当对索引进行插入、删除或更新操作时，如果该索引页尚未加载到Buffer Pool中，InnoDB会将这些变更操作暂时存储在Change Buffer中。之后，当相关索引页由于其他查询而被读入Buffer Pool时，Change Buffer中累积的变更会与索引页合并（Merge），这个过程称为“Change Buffer Merge”。同时，后台线程也会定期同步change buffer到磁盘。

查看change buffer设置

SHOW VARIABLES LIKE 'innodb_change_buffering';

有all、none、inserts几种可选，默认是all，表示插入、删除、更新操作都开启缓存。

一般只有插入后需要立马消费才有必要关闭成none

为什么是唯一索引

唯一索引只会增加数据库的压力。现实中往往是业务不想保证插入的唯一性，而是希望数据库来做约束，才会使用唯一索引。

唯一索引引入的问题：插入数据慢，数据量大时每次写盘会很明显变慢。所以，如果业务能保证数据插入唯一，最好不要在数据库侧做约束。

annidy commented 6 days ago

10 | MySQL为什么有时候会选错索引？

分析执行

解释执行计划功能打印的内容包括：可能用到的索引、扫描行数，过滤百分比。扫描的行数越少说明效率越高。

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
|  1 | SIMPLE      | t     | NULL       | range | a,b           | b    | 5       | NULL | 48973 |     1.02 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

另一种就是查看慢日志，这个会更精确一些

# Time: 2024-06-27T07:49:17.128053Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    14
# Query_time: 0.086870  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 50001
SET timestamp=1719474557;
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

索引区分度

MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根 据统计信息来估算记录数。 这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越 好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越 大，索引的区分度越好。

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| t     |          1 | a        |            1 | a           | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t     |          1 | b        |            1 | b           | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

上面显示统计值是0，是有问题的。补救方法是重新分析并修正索引统计信息

analyze table t;

引导正确的索引

MySQL优化器选错索引无法避免，我们可以用force index强制MySQL使用某个索引，但这个有一定风险且不通用。 修改查找的后置条件（order by）也许可以提示优化器，会有一点玄学。 最后作者推荐是删除不必要的索引，最直接。

annidy commented 5 days ago

11 | 怎么给字符串字段加索引？

字符串的特点是可以利用前缀比较解决查询问题，索引针对字符串的索引可以设置前缀长度

alter table SUser add index idx6(email(6));  -- 修改前缀长度为6

使用前缀的优势是索引的B树需要的存储空间更小，缺点是查找可能需要更多次比较。

下面的SQL可以计算不同前缀长度的区分度，可以辅助决策前缀长度

select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;

其它索引

文中有两种方案：倒序存储和hash字段。都是通过优化存储的方式来实现的。

MySQL的索引除了BTree，还有其它几种不太常见的算法，列举如下：

  1. BTree（B-Tree）索引: 特点: BTree索引是最常见的索引类型，它基于平衡多路搜索树结构，能够快速地进行范围查询、排序和等值查询。BTree索引能够保持数据有序，因此适合用于ORDER BY和GROUP BY操作。 使用场景: 适用于大多数情况，特别适合需要进行排序、范围查询和等值查询的场景，例如日期范围查询、数值比较等。
  2. Full Text索引: 特点: Full Text索引专门设计用于处理文本内容的全文搜索，它可以高效地处理LIKE操作符中的模糊查询和全文查询。 使用场景: 适用于包含大量文本的列，如文章内容、评论等，当需要进行全文搜索时，比如查找包含特定关键词的所有文档。
  3. Hash索引: 特点: Hash索引基于哈希表实现，对于等值查询非常快，因为它可以直接通过哈希值定位到数据。但是，它不支持范围查询和排序。 使用场景: 适合于仅需进行等值匹配查询的场景，且查询条件中的字段值分布较为均匀，以减少哈希冲突。例如，用户ID作为查询条件的查询。
  4. RTree（R-Tree）索引: 特点: RTree索引是一种空间索引，用于高效地处理多维数据，如地理坐标、几何形状等。它能够支持空间对象的快速查询，如包含、相交等空间关系查询。 使用场景: 适用于地理信息系统(GIS)应用、地图数据、空间数据管理等，需要对多维空间数据进行范围查询或邻近性查询的场景，比如找出某个点周围的餐馆、查询落在某个区域内的所有设施等。
annidy commented 4 days ago

12 | 为什么我的MySQL会"抖"下？

引发数据库flush的几种场景

  1. redo log写满了，必须flush到数据库文件
  2. 内存不足，换页。（这个属于操作系统范畴了，但确实会卡顿）
  3. 系统空间，后台自动写盘。这个对用户感知不明显
  4. 停止服务时

作者提出的优化方案是控制刷脏页的速度，innodb_io_capacity 这个值在 MySQL 的 InnoDB 存储引擎中代表系统每秒可以进行的 I/O 操作数量。 SSD硬盘可以配置为0，代表可以全力刷。在我老旧的mysql5.6，这个值是200.

13 | 为什么表数据删掉一半，表文件大小不变？

delete命令，删除数据只是把记录or页标记为删除，他们下次有需要时还会复用。

drop table倒是可以清理文件，但这不是我们想要的。

如果要消除『空洞』，需要进行重建表。原理基本上是新建一个临时表，然后按照顺序把数据复制过去。由于是按主键顺序，所以不会出现插入空洞。等效的命令是

alter table T engine=InnoDB;

alter操作会导致MDL加锁的，不过MySQL对这种情况有优化，会尝试以ALGORITHM=inplace的方式执行。表锁的时间非常短，后面就变成了行锁，不会阻塞DML（数据操作语言，如INSERT、UPDATE、DELETE）操作。

annidy commented 3 days ago

14 | count(*)这么慢，我该怎么办？

统计行数是InnoDB需要把每行都读出来再计算总和。由于索引的count和表的count是相同的，mysql会优化读索引最小的那颗B树。

优化获取总行的方案主要有两个：1. 使用外存保存总和，比如redis；2. 用单独一张表。用表的方案会比较精确一些，可以在事务中添加行。

不同count的性能

count(字段)<count(主键id<count(1)≈count() 。count()是特殊优化过的，建议取长度都用这种方式。

16 | "order by"是怎么工作的？

当使用where+order by时，具体的实现原理是先按照city取出所有的数据，然后再进行排序

select city,name,age from t where city='杭州' order by name limit 1000 ;

排序的过程可能发生在文件上（归并排序），取决于设置的sort_buffer_size大小。

索引排序

创建一个city和name的索引，可以完美解决二次排序问题

alter table t add index city_user(city, name);

必须是联合索引，单独的name索引解决不了上面的问题。 image

annidy commented 1 day ago

17 | 如何正确地显示随机消息？

本章的重点是分析下面的语句为什么性能第

select word from words order by rand() limit 3;

其中，order by rand()被认为不是一个高效的操作。 对于上面的操作，它需要先把所有的words取出来并建立一个新的临时表，（以下是我猜测）临时表有一个随机值的列，同时记录前3个随机值，将临时表的随机列重新排序，使用类似where的语句查找的记录的3个随机值。

mysql> explain select word from words order by rand() limit 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|  1 | SIMPLE      | words | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10000 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+

随机问题优化

MySQL随机值需要排序这个问题可能后面会被优化，这也是声明式语言的优势

在业务层模拟随机的方案很多。最通用的解法是计算中的行数，然后随机挑选一行。用SQL实现是

select count(*) into @C from words;
set @Y = floor(@C * rand());
select word from words limit @Y, 1;

limit X, Y表示跳过X行，返回Y行。由于没有用到索引，时间复杂度O(n)

有些数据库用offset

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

列举了一些常见的错误使用的场景

  1. 条件字段函数操作 在条件语句中使用函数时，无法利用索引快速查找。比如

    select count(*) from t where month(t_modified)=7;
select count(*) from t where t_modified >= '2024-7-1' and t_modified < '2024-8-1';

    month函数破坏了索引查找，最终只能是遍历所有索引（全索引扫描）。

  2. 隐式类型转换 字符串和数字比较时，引擎先将字符串转换为数字再比较。这点跟JavaScript很类似。

  3. 隐式字符编码转换 作者这里故意构造一个联级查询，两个表是字符编码不同，然后查询条件判断是否相等。

上面三个例子，都是一个原因：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

where id+1=1000 这个条件语句也破坏了索引，要改写成 where id=1000-1。这纯粹是优化器偷懒了。

annidy commented 4 hours ago

19 | 为什么我只查一行的语句，也执行这么慢？

锁阻塞

  1. 有事务给表加锁了。这种在前面alter表的时候也遇到过
  2. 等待flush table完成。flush类似栅栏锁，他不与前后语句共同指向。（这个完全凭经验，这是数据库的设计）
  3. 等待行锁，查询语句中指定了行锁，那么需要等待其他包含更新的事务完成。

锁问题第一时间用 show processlist; 命令是分析是那个过程加锁了。

慢查询

没有命中索引是最常见的一种慢查询。 还列举了一个极限场景，read view非常长，如果select没有加lock in share mode，就要回溯read view，也可能花很多时间。

20 | 幻读是什么，幻读有什么问题？

幻读的定义是：在一个事务中，同一个范围的查询，后一次查询看到了前一次查询没有看到的行。 这个是很常见的，在可重复读的隔离下，也可以看到其他事务插入的数据（注意是插入，MVCC是能屏蔽更新的）。

间隙锁

幻读的根因是普通的行锁无法阻止新的插入，但是间隙锁可以。

在InnoDB中，当你使用SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE语句时，如果查询条件使用了范围条件（如BETWEEN、<、>;等），并且查询的索引是唯一的（如主键索引或唯一索引），InnoDB可能会自动加上间隙锁。

另外一种间隙锁没有范围条件，当where条件的key不存在是，也会在B树的前后加上间隙锁。

begin;
select * from t where d=9 for update;
insert into t(id, c) values (8, 8); -- ok，但是其它事务执行这条语句会阻塞

如果d=9不存在，但是存在d=5和d=10，那么在它两个中间是有间隙锁的。

21 | 为什么我只改一行的语句，锁这么多？

作者总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

  1. 原则1：加锁的基本单位是next-key lock。next-key lock是前开后闭区间 -- (start, end]。
  2. 原则2：查找过程中访问到的对象才会加锁。
  3. 优化1：唯一索引上的等值查询，next-key lock退化为行锁。
  4. 优化2：非唯一索引上的等值查询，从找到的key开始，继续向右遍历，且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
  5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

汇总下来，就是关于next-key lock的行为理解。这是MySQL最细粒度的锁，它不同于普通的锁，是包含范围的。因为B树中可能有空洞，锁必须保证空洞的安全。插入、删除某个索引时，理论上也要基于前开后闭的方式获取前后行的锁。 当然，如果有可能优化（比如唯一索引），退化为行锁影响就小很多了。