0

⭐《MySQL 实战45讲》笔记

[TOC]

一. 索引与优化

本篇内容主要来自极客时间《MySQL实战45讲》中的:

04 - 深入浅出索引(上) 05 - 深入浅出索引(下)

基本数据存储模型

有序数组 哈希表 搜索树

跳表

Redis 的有序集合使用的就是这个结构

LSM树 等

有序数组:

优点: 查找很快, 支持范围查询

缺点: 插入代价高, 必须逻辑上移动后续的所有记录

搜索树:

二叉搜索树

搜索效率最高, 但实际并不采用, 因为索引是存在磁盘的.

假设一棵树高为20, 一次搜索就需要20个数据块, 对应磁盘就是20次随机查找. 对于普通硬盘来说, 一次寻址约 10ms, 则一次搜索就需要 20x10ms = 200ms.如果要让一个查询尽量少读磁盘, 那就必须尽量少地查询数据块, 应该使用下面的多叉树.

多叉树

为了减少磁盘访问次数, 可以使用 "N叉"树, 这里的 N 取决于数据块的大小.

以 InnoDB 中 一个整数字段为例, 这个N差不多是1200.

计算方法: 补充!!

如果树高为4, 则可以存储 1200^3 个值, 考虑树根数据块基本都在内存中, 因此一次搜索只需要3次磁盘查找, 考虑到第2层数据块也有很大概率在内存中, 那么访问磁盘次数就更少了.

引申: InnoDB 里N叉树中的N如何调整

N = 页page的大小 / 每个索引项大小

N叉树中非叶子节点存放的是索引信息, 每个索引项包含Key和Point指针及其他辅助数据, 其中Point指针固定大小6字节, 默认索引页的大小是16KB. 因此主键为int时, int占用4个字节, 加上辅助数据差不多每个索引项占用13字节, 因此非叶子节点大约可以存储 16k/13 ≈ 1260 个左右.

N的大小大致是根据上述式子决定的, 因此若要调整N, 则有2个防线:

MySQL 5.6以后可以修改 page 大小, 参数: innodb_page_size

未测试

通过修改Key字段类型, 比如 int 占用4字节, bitint 占用8字节.

哈希表

优点: 新增和查找都很快

缺点: 无法进行范围遍历, 必须一个一个查找.

InnoDB 索引的存储结构

索引组织表: 按照主键顺序, 以索引形式存放的表.

InnoDB 使用了 B+树 作为索引的存储结构.

InnoDB 中的索引, 按照叶子节点内容来区分, 分为两类:

主键索引(聚簇索引, clustered index) 非主键索引(二级索引, secondary index)

InnoDB 中 B+ 树的叶子节点存放的是 页, 一页里面可以存多个行.

这里的页指的是 InnoDB 的页, 而非磁盘页, 默认大小是 16KB.

索引的维护涉及 插入 及 删除, 这两个操作可能导致 页分裂 及 页合并 的问题.

插入: 如果插入不是有序递增的, 就需要逻辑上移动插入点后面的数据. 更糟糕的是, 如果插入点所在的数据块已满, 根据B+树的算法, 此时需要进行 页分裂 操作(新申请一个页, 将部分数据挪动过去). 页分裂 操作除了影响性能外, 还会影响页的利用率, 降低了约 50% 的利用率. 删除: 当两个相邻页由于删除元素导致利用率很低后, 会将数据页做合并, 合并的过程可以理解为页分裂的逆过程.

索引可能因为删除或页分裂的原因导致数据页有空洞, 而重建索引的过程会创建一个新的索引, 并将数据顺序插入, 使得索引更紧凑, 空间利用率更高.

Q. 为什么表删除了一半数据, 文件大小却没变?

A. 简单回答一下.

删除时仅仅是将数据从所在的数据页上标记删除, 遗留的空位还会保留着, 供后续插入新记录时直接存放.

这种情况可以考虑重建索引以减少磁盘空间占用

optimize table 表名;
-- 或
alter table 表名 engine=InnoDB;

注意 alter table 表名 = engine=InnoDB; 会加 MDL 读锁.

如果是 MySQL 5.7, 则会使用 OnlineDDL, 避免长时间的 MDL 锁导致业务不可用.

Q. 主键索引和非主键索引的区别

A. 主要区别在于:

主键索引(叶子节点)存储的是行记录, 非主键索引(叶子节点)存储的是对应主键的内容. 非主键索引查询时, 需要先在该索引上查找到对应主键, 再去主键索引查找, 这个过程叫做 回表.因此在应用中应尽量使用主键索引, 避免多一次回表

Q. 非主键索引中字段值相同的索引项是如何存储的?

A. 结论: 独立存储.

以索引c为例, id是主键, 假设有两个记录 (c=10, id=1), (c=10, id=2), 这其实在索引c上是两条不同的索引项, 它的存放顺序是先按照c递增, c等值情况下再按照id递增, 因此可以理解为索引c 是 (c, id)

Q. 若不给表设置主键会怎样?

A. InnoDB 会为每一行隐式分配一个 RowId 作为主键. 所以其实还是有主键索引的

Q. 联合索引的存储结构是怎样的?

A. 《高性能MySQL 第三版》P144，关于索引类型的插图，说明了联合索引是N个字段组合成一个索引的。

Q. 在联合索引中多个字段顺序是怎样的?

A. 以 (a,b) 为例, id 是主键. 则在该索引上, 是先按照 a 递增, 再根据 b 递增, 最后根据 id 递增的顺序排序.

可以和下面写到的 最左前缀 一起理解.

Q. 如果表用到了联合主键, 那么在二级索引中是如何存储的?

A. 假设联合主键是 (a,b), 此时表中还有个字段 c, 可以分3种情况考虑:

如果建立了索引 (c), 则先按照 c递增, 其次 a 递增, 最后是 b 递增. 如果建立了索引 (c,a), 那么顺序同1, 这种情况下是没必要单独创建 (c,a), 而只需要索引(c)即可 如果建立了索引 (c,b), 那么会先按照 c递增, 然后是 b 递增, 最后是 a 递增.

索引的选择

主键的选择

主键尽量使用自增主键, 原因:

自增主键是有序递增的, 往索引插入时都是追加操作, 避免了 页分裂的问题, 而业务上的主键一般不满足有序递增. 自增主键通常是 int not null primary key auto_increment 或 bigint not null primary key auto_increment, 使用整形做主键只需要4个字节, 使用长整型则是8个字节. 主键的字段越小, 普通索引的叶子节点也就越小, 占用的空间就越小.

因此从性能和存储空间看, 自增主键通常是最好的选择.

那么什么时候可以考虑用业务字段作为主键:

没有其他二级索引(无需考虑二级索引叶子节点大小) 业务字段唯一

↑ 这就是典型的 KV 场景了, 考虑到查询时尽量用主键索引, 避免回表, 此时就可以将这个索引设置为主键.

覆盖索引

当查询语句中涉及的所有字段都在同一个索引中, 此时由于只需要在该索引树上查找而不需要回表, 这成为覆盖索引.

覆盖索引可以减少树的搜索次数, 显著提升性能, 因此是常用的优化手段.

注意: 索引的维护是有代价的, 因此是否新增冗余索引来支持覆盖索引时需要权衡考量.

以索引 (code, name) 为例, 当使用如下语句时是可以用到覆盖索引, 避免回表的:

select name from 表 where code = "xxx";

-- 或

select id from 表 where code = "xxx";

Q. 是否有必要为了覆盖索引而设立联合索引?

A. 分情况:

如果是高频请求, 那么可以建立联合索引来使用覆盖索引优化, 避免回表 如果是低频请求, 若已有现成的可利用最左前缀优化的索引, 或单独索引, 则没必要. 此时索引带来的优化好处可能已经被维护索引的代价盖掉了.

最左前缀

最左前缀指的是联合索引的前几个字段, 以及字符串索引的前几个字符.

由于索引是以B+树结构存储的, 而B+树这种索引结构是可以利用索引的最左前缀来定位记录的.

以 (name, age) 这个联合索引为例, 它的大致示意图如下:

可以看出索引项的顺序是按照索引定义的字段顺序来排序的.

以下语句会用到上面的这个索引的最左前缀:

-- 联合索引上的最左N个字段
select * from 表 where name = "xx";

-- 字符串的最左N个字符
select * from 表 where name like '张%';

Q. 联合索引上的字段顺序如何确定?

A. 优先考虑复用能力, 其次考虑存储空间.

原则1: 如果通过调整顺序可以少创建一个索引, 那么通常就会优先考虑调整后的这个顺序了.

原则2: 优先考虑原则1, 其次应考虑空间占用.

以联合索引 (a,b) 为例, 由于最左前缀优化的原因, 在该表上就不需要单独再建立索引 (a) 了, 因此这种情况只需要建立一个联合索引 (a,b) 即可.

但是, 如果此时同样需要用到索引 (b), 那么这时候有两个选择:

创建 (a,b) 及 (b) 创建 (b,a) 及 (a)

此时若字段a比较大, 则应考虑方案1, 否则应考虑方案2.

索引下推 index condition pushdown

对于联合索引, 对于不满足最左前缀的部分, 在某些情况下是可以用到 索引下推 的.

索引下推: 在索引遍历过程中, 利用索引中已有的字段过滤不满足条件的记录, 避免每次判断都回表.

先明确:

索引下推 是在 MySQL 5.6 引入的.

在 explain 的时候可以在 Extra 看到 Using index condition , 说明可以用到索引下推

"可以"用, 但不一定用/没有.

这个地方还不大明确

以索引 (name, age) 为例, 查看一下SQL语句:

select * from 表 where name like '张%' and age > 20;

此时会先利用索引, 快速找到 name以"张"开头的记录, 然后依次向右遍历:

若是在 MySQL 5.6 以前, 则需要一个一个回表并筛选 age > 20 的记录 若是在 MySQL 5.6 及以后, 则根据 索引下推 则会在索引遍历过程中对索引包含的字段先做判断, 过滤不满足条件的记录, 减少回表次数.

Change Buffer 之普通索引和唯一索引的选择

前提: 业务能保证记录是唯一的情况下, 才需要考虑.

理解这部分内容的 意义:

在遇到大量插入数据慢, 内存命中率低的情况下, 多一个排查思路.

相关配置:

## 最大占用 innodb_buffer_poll 内存空间的百分比
innodb_change_buffer_max_size=50

Change Buffer

只会针对 普通索引 (肯定是二级索引了) 能够在不影响数据一致性前提下将数据更新操作(DML, insert/update/delete)缓存在 Change Buffer 中, 而无需立即读取(磁盘)数据页. 当下次需要访问这个数据页的时候, 会将该数据页读取到内存中, 再将这些缓存的操作应用上去. 记录的操作存储在 Change Buffer 中, 它占用的是InnoDB Buffer Pool, 同时它是可持久化的. Change Buffer 减少的是随机读的次数(无需每次更新都读取), 若在读取记录前保存在该Buffer中操作越多, 则受益更大. 因此它同时也提高了内存利用效率(因此读取数据页是会占用内存空间的) 从磁盘读取索引数据页并将Change Buffer缓存的操作应用上去, 这个过程称为 Merge

Merge 发生的情况:

读取记录时应用Change Buffer 后台线程定期Merge 正常关闭(shutdown)数据库

想象一下, 一张表有4,5个普通二级索引, 这些索引的使用率并不高.

同时该表会频繁更新数据, 若没有Change Buffer, 那么每次更新操作维护二级索引时都需要从磁盘读入索引对应的数据页, 而有了Change Buffer后只需将这些操作保存在该Buffer中, 极大减少了磁盘随机读次数, 最后统一Merge即可.

查询过程的区别:

普通索引

从索引树根目录, 逐层查找对应记录所在数据页.

若不在内存中, 则需要先从磁盘上读入内存.

若数据所在页已经在内存中, 则读取该记录, 并向右遍历直到不符合条件. 由于数据的读取是以数据页为单位(默认16KB), 因此这个过程是在内存中, 对性能影响极小, 除非是记录刚好在数据页的最后一条.(考虑到概率, 可以忽略)

唯一索引

类似普通索引, 只是在找到对应一条记录后就停止了.

结论: 在查询过程中性能区别不大.

更新过程的区别:

若数据都在内存中则没有什么区别, 因此以下只讨论不在内存中的情况.

普通索引

将更新语句记录在 Change Buffer 中, 更新结束.

唯一索引

由于更新操作需要判断是否违反数据一致性约束, 因此无法使用 Change Buffer, 需要先将数据页从磁盘读取到内存, 进行判断, 再做更新操作.

Q. Change Buffer 什么时候会成为负优化?

A. 在下述普通索引场景:

当每次更新操作后马上读取, 由于更新操作会缓存在Change Buffer中, 下一次马上读取时需要立即 Merge.

此时反而多了维护 Change Buffer的代价, 同时随机访问IO不会减少.

Q. Change Buffer 适合什么场景?

A. 写多读少业务

Change Buffer 会将更新的操作缓存起来, 缓存得越多, 则在 Merge 操作的时候收益就越大.

常见业务模型: 账单类, 日志类系统.

联合索引的字段顺序 - 根据区分度

当需要创建联合索引的情况下, 在 不考虑索引复用 前提, 且 字段顺序不影响索引完整使用 前提下, 如何确定联合索引中的字段顺序?

!!! 注意这里的前提:

不考虑索引复用

字段顺序不影响索引完整使用

如果是 where a = xx order by b 这类语句, 那么直接就是联合索引 (a, b) 了.

此时应该按照字段的区分度, 区分度高的在前.

以索引 (status, product_id) 为例, 分别查看其区分度:

SELECT
    COUNT(DISTINCT status)/COUNT(*) as status_disc,
    COUNT(DISTINCT product_id)/COUNT(*) as product_id_disc
FROM
    表名;

当前这个例子很清楚, status 就几种取值, 基数很小, 区分度很差, 因此应该建立联合索引 (product_id, status)

如何创建字符串索引

一般有以下几种选择:

完整索引

最耗费空间

前缀索引, 只选择前N个字符

适用: 前N个字符具有足够区分度的情况.

缺点: 增加额外扫描行数, 同时无法使用覆盖索引.

字符串倒序 + 前缀索引

适用: 字符串前N个字符区分度不够的情况下, 且后N个字符有足够区分度

存储: 存储的时候直接存储倒序的字符串

使用: update 表 set s = reverse("123456");

缺点: 除了前缀索引的缺点外, 每次更新/查找都需要额外的 reverse 函数调用消耗, 同时无法利用索引进行范围查找.

额外字段存储hash值

存储: 新增额外字段存储字符串对应的hash值, 若使用 crc32 函数, 则额外占用4个字节

优点: 查找性能稳定, 基本在 O(1)

使用: 由于hash值会冲突, 因此查找时除了hash字段判断外, 还要判断原始字符串是否一致. select * from 表 where s_hash = crc32("123456") and s = "123456";

缺点: 占用额外的存储空间, 无法利用索引进行范围查找

索引创建命令

CREATE TABLE 时创建

CREATE TABLE IF NOT EXISTS `users` (
    -- 省略字段定义
    
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_phone` (`phone`),
    KEY `users_name` (`name`),
) Engine=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
-- 可忽略索引名
-- ALTER TABLE table_name ADD INDEX (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

-- 一个语句建多个索引
ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

索引失效情况

索引失效的情况个人认为主要是以下情况:

区分度太低, 导致优化器认为全表扫描会更快.

对索引字段使用函数、进行计算、类型转换

WHERE a + 1 = 2 这种语句也会导致索引 a 失效, 此时应该改写 SQL 语句为: WHERE a = 1

包括显式转换及隐式转换

如果字段 phone 是 char 类型, 那么 WHERE phone = 18612345678 同样可能会导致索引失效, 应该改写成 WHERE phone = '18612345678 '

不满足最左前缀

包括联合索引和字符串最左前缀

索引列存在NULL且查询条件是 is not null, 若索引没有覆盖查询字段和查询条件时, 此时会符合以下的<u>情况6</u>, 导致全表扫描.

以下是个人测试

-- UserName 是 varchar, nullable

explain select Uid from new_light_user where UserName is  null;
-- SIMPLE    new_light_user    ref    UserName    UserName    768    const    10    Using where; Using index

explain select * from new_light_user where UserName is not null;
-- SIMPLE    new_light_user    ALL    UserName    null         null  null   17979    Using where

根据查询条件, 无法使用索引快速定位, 但可以使用索引扫描时, 若innodb认为代价太大也会直接走全表扫描.

其他注意点

索引设计规范

单表索引建议控制在5个以内 但索引字段不允许超过5个

索引字段要定义为 NOT NULL, 否则:

占用额外存储空间(1字节) 导致索引的使用更加复杂, 在某些情况下会导致索引失效 条件判断更麻烦, 需要 IS NULL, IS NOT NULL 区分度不高的字段不建议建立索引

除非查询值的筛选力度很高, 比如 status = 0 (表示未完成), 因为大多数值是 1, 因此这种情况下建索引还是有意义的.

建立联合索引时, 优先将区分度高的字段放前面.

二. 加锁规则及案例

本文内容主要是 《MySQL实战45讲》 课程中第 20,21,30 课程的个人笔记及相关理解.

主要是对于加锁规则的理解及分析.

以下仅针对 MySQL 的 InnoDB 引擎.

MyISM 引擎就只有表锁

基本概念

锁的种类

MySQL 中的锁主要分为:

全局锁

flush tables with read lock;

表级锁

表锁

lock table 表名 read;
lock table 表名 write;

元数据锁(Meta Data Lock, MDL)

在 MySQL 5.5 引入 MDL 锁.

MySQL 5.6 以后支持 OnlineDDL

行锁

还有个自增锁, 后续补充.

意向锁在此先不做讨论.

表级锁

元数据锁 MDL

MDL支持的版本:

在 MySQL 5.5 引入 MDL 锁. MySQL 5.6 以后支持 OnlineDDL.

MDL锁目的: 控制对表元数据修改的并发.

MDL锁类型分为:

MDL 读锁(读锁之间不冲突) MDL 写锁(读写锁冲突, 写锁之间也冲突)

普通的增删改查会自动获取MDL读锁, 而对表的字段修改或创建索引等修改表元数据的操作会自动获取MDL写锁, 在此期间增删改查就会被阻塞掉.

OnlineDDL 是一种近似不锁表的特性, 它的过程如下:

获取MDL写锁

这个期间会阻塞

降级为MDL读锁 执行DDL语句

大部分时间消耗在这里, 比如重建表(alter table 表 Engine=Innodb)时, 需要将数据从旧表按主键顺序逐一添加到新表, 而大部分时间就消耗在这里.

同时在此期间, 所有对数据库的增删改操作都会记录在特定日志中, 待这部分执行完毕后再应用这些日志, 从而保证数据一致性.

升级为MDL写锁

这个期间会也阻塞

释放MDL写锁

也就是说 OnlineDDL 其实还是会锁表, 但只会在开始跟结束的时候锁, 中间大部分时间是不锁的.

对于 ALTER TABLE 表名 Engine=Innodb 这种DDL操作:

5.6之前是在Server层面上通过创建临时表来实现的(锁表+创建临时表+拷贝数据+替换表) 5.7及之后的OnlineDDL是在InnoDB层面上处理的, 它会创建临时文件.

部分DDL操作不支持OnlineDDL, 比如添加全文索引(FULLTEXT)和空间索引(SPATIAL)

InnoDB 中的锁

行锁

行锁也叫做记录锁, 这个锁是加在具体的索引项上的.

行锁分为两种:

读锁: 共享锁 写锁: 排它锁

行锁冲突情况:

读锁与写锁冲突 写锁与写锁冲突

需要明确:

锁的对象是索引

间隙锁

记录之间是存在间隙的, 这个间隙也是可以加上锁实体, 称为间隙锁.

间隙锁存在的目的: 解决幻读问题.

间隙锁冲突情况:

间隙锁之间是不冲突的, 它们都是为了防止插入新的记录. 间隙锁与插入操作(插入意向锁)产生冲突

需要明确:

间隙锁仅在 可重复读隔离级别下才存在.

间隙锁的概念是动态的

对间隙(a,b)加锁后, 存在间隙锁 (a,b).

此时若 a 不存在(删除), 则间隙锁会向左延伸直到找到一条记录.

若b不存在了(删除), 则间隙锁会向右延伸直到找到一条记录.

假设主键上存在记录 id=5 和 id=10 和 id=15 的3条记录, 当存在某个间隙锁 (10,15) 时, 若我们将 id=10 这一行删掉, 则间隙锁 (10, 15) 会动态扩展成 (5, 15), 此时想要插入 id=7 的记录会被阻塞住.

此处的删除指的是事务提交后, 否则间隙锁依旧是 (10,15)

next-key lock

next-key lock = 行锁 + 间隙锁

next-key lock 的加锁顺序:

先加间隙锁 再加行锁

如果加完间隙锁后, 再加行锁时被阻塞进入锁等待时, 间隙锁在此期间是不会释放的.

两阶段锁协议

两阶段锁协议指的是:

在用到的时候会加锁 在事务提交的时候才会释放锁

了解这个协议的启发在于:

在一个事务中需要对多个资源进行加锁时, 应尽量把最可能造成锁冲突的放在最后, 这边可以避免持有这个锁的时间过久导致线程长时间等待, 降低并发度.

索引搜索

索引搜索指的是就是:

在索引树上利用树搜索快速定位找到第一个值 然后向左或向右遍历

order by desc 就是用最大的值来找第一个

order by 就是用最小的值来找第一个

等值查询

等值查询指的是:

在索引树上利用树搜索快速定位 xx=yy的过程

where xx > yy 时, 也是先找到 xx = yy 这条记录, 这一个步骤是等值查询.但后续的向右遍历则属于范围查询.

以及在找到具体记录后, 使用 xx=yy 向右遍历的过程.

加锁规则

该部分源自《MySQL实战45讲》中的 《21-为什么我只改了一行的语句, 锁这么多》

以下仅针对 MySQL 的 InnoDB 引擎在 可重复读隔离级别, 具体MySQL版本:

5.x 系列 <= 5.7.24 8.0 系列 <=8.0.13

以下测试若未指定, 则默认使用以下表, 相关案例为了避免污染原始数据, 因此在不影响测试结果前提下, 都放在事务中执行, 且最终不提交.

create table c20(
    id int not null primary key, 
    c int default null, 
    d int default null, 
    key `c`(`c`)
) Engine=InnoDB;

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

/*
+----+------+------+
| id | c    | d    |
+----+------+------+
|  0 |    0 |    0 |
|  5 |    5 |    5 |
| 10 |   10 |   10 |
| 15 |   15 |   15 |
| 20 |   20 |   20 |
| 25 |   25 |   25 |
+----+------+------+
*/

2个"原则", 2个"优化", 1个"BUG"

原则1: 加锁的基本单位是next-key lock, 前开后闭区间

原则2: 访问到的对象才会加锁

select id from t where c = 15 lock in share mode;

加读锁时, 覆盖索引优化情况下, 不会访问主键索引, 因此如果要通过 lock in share mode 给行加锁避免数据被修改, 那就需要绕过索引优化, 如 select 一个不在索引中的值.

但如果改成 for update , 则 mysql 认为接下来会更新数据, 因此会将对应主键索引也一起锁了

优化1: 索引上的等值查询, 对唯一索引加锁时, next-key lock 会退化为行锁

select * from t where id = 10 for update;

引擎会在主键索引上查找到 id=10 这一行, 这一个操作是等值查询.

锁范围是

优化2: 索引上的等值查询, 向右遍历时且最后一个值不满足等值条件时, next-key Lock 会退化为间隙锁

select * from t where c = 10 for update;

由于索引c是普通索引, 引擎在找到 c=10 这一条索引项后继续向右遍历到 c=15 这一条, 此时锁范围是 (5, 10], (10, 15)

BUG 1: 唯一索引上的范围查询会访问到不满足条件的第一个值

id> 10 and id <=15, 这时候会访问 id=15 以及下一个记录.

对索引上的更新操作, 本质上是 删除+插入

读提交与可重复读的加锁区别

读提交下没有间隙锁

读提交下有一个针对 update 语句的 "semi-consistent" read 优化.

如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.

注意这个策略对 delete 是无效的.

?????? 语句执行过程中加上的行锁, 会在语句执行完成后将"不满足条件的行"上的行锁直接释放, 无需等到事务提交.

insert into ... select ... 加锁

https://time.geekbang.org/col...

在可重复读隔离级别, binlog_format = statement 时, 该语句会对被 select 的那个表访问到的记录和间隙加锁

小伙子, 很危险的.

生产环境大表复制数据一般用 pt-archiver 工具来处理, 避免 insert ... select ... 锁导致的长阻塞.

pt-archiver: 数据归档工具

或者简单用 select ... into outfile 和 load data infile 组合来代替 insert ... select 完成插入操作.

简单例子

例子1

begin;
select * from c20 where id=5 for update;

在主键索引 id 上快速查找到 id=5 这一行是等值查询

例子2

begin;
select * from c20 where id > 9 and id < 12 for update;

在主键索引 id 上找到首个大于 9 的值, 这个过程其实是在索引树上快速找到 id=9 这条记录(不存在), 找到了 (5,10) 这个间隙, 这个过程是等值查询.

然后向右遍历, 在遍历过程中就不是等值查询了, 依次扫描到 id=10 , id=15 这两个记录, 其中 id=15 不符合条件, 因此最终锁范围是 (5,10], (10, 15]

例子3

begin;
select * from c20 where id > 9 and id < 12 order by id desc for update;

根据语义 order by id desc, 优化器必须先找到第一个 id < 12 的值, 在主键索引树上快速查找 id=12 的值(不存在), 此时是向右遍历到 id=15, 根据优化2, 仅加了间隙锁 (10,15) , 这个过程是等值查询.

接着向左遍历, 遍历过程就不是等值查询了, 最终锁范围是: (0,5], (5, 10], (10, 15)

个人理解:

由于有 order by id desc, 因此首先是等值查询 id=12 不存在, 向右遍历不满足, 优化, 因此加了间隙锁 (10, 15) 向左遍历到 id=10, next-key lock, (5,10] 向左遍历到 id=5, next-key lock, (0,5], 不满足条件, 停止遍历

例子4

begin;
select * from c20 where c>=15 and c<=20 order by c desc lock in share mode;

执行过程:

在索引c上搜索 c=20 这一行, 由于索引c是普通索引, 因此此处的查找条件是 <u>最右边c=20</u> 的行, 因此需要继续向右遍历, 直到找到 c=25 这一行, 这个过程是等值查询. 根据优化2, 锁的范围是 (20, 25)? 接着再向左遍历, 之后的过程就不是等值查询了.

个人理解:

由于 order by c desc, 因此首先等值查询 c=20 存在, 加锁 (15, 20] 向右遍历到 c=25, 不满足, 但可优化, 加锁 (20,25) 向左遍历到 c=15, 加锁 (10, 15] 向左遍历到 c=10, 加锁 (5,10]

例子5

begin;
select * from c20 where c<=20 order by c desc lock in share mode;

这里留意一下 , 加锁范围并不是 (20, 25], (15, 20], (10,15], (5,10], (0, 5], (-∞, 5], 而是

...........

..........

.........

........

.......

......

.....

......

.......

........

.........

..........

...........

所有行锁+间隙锁.

具体为什么, 其实只要 explain 看一下就明白了.

+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|    1 | SIMPLE      | c20   | ALL  | c             | NULL | NULL    | NULL |   14 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

但如果是 c<=19, 则会使用索引 c, 这说明 innodb 引擎有自己一套规则用于"估算"当前使用二级索引还是主键索引哪个开销会更小.

explain select * from c20 where c<=19 order by c desc lock in share mode;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | c20   | range | c             | c    | 5       | NULL |    4 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

例子6

begin;
select * from c20 where c>=10 and c<15 for update;

加锁范围是

索引 c 的 (5,10], (10,15]

这里对索引 c 的 15 好像是退化成行锁了, 不是很理解.

主键索引的 [10]

访问到的才会加锁, 由于没有访问主键 id=15, 因此不会对齐加锁.

例子7 - 个人不理解的地方

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞

-- T3 事务A 人为制造死锁, 方便查看锁状态
update c20 set d=d+1 where id=25;    -- OK
/*
此时 事务B 提示:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/

个人不理解的:

根据order by id desc, T1 时刻事务A首先在主键索引上搜索 id=20 这一行, 正常来说主键索引上 id=20 的只有一行, 没必要向右遍历.

加锁范围:

(5,10] (10,15] (15,20] (20,25)

mysql> show engine innodb status
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-27 10:34:29 0xe2e8
*** (1) TRANSACTION:
TRANSACTION 1645, ACTIVE 100 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1080, 4 row lock(s), undo log entries 1
MySQL thread id 82, OS thread handle 77904, query id 61115 localhost ::1 root update
insert into c20 values(21,21,21)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1645 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1646, ACTIVE 271 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1080, 5 row lock(s)
MySQL thread id 81, OS thread handle 58088, query id 61120 localhost ::1 root updating
update c20 set d=d+1 where id=25
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock mode S locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `test_yjx`.`c20` trx id 1646 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000019; asc     ;;
 1: len 6; hex 00000000066d; asc      m;;
 2: len 7; hex 6e0000019a0110; asc n      ;;
 3: len 4; hex 80000019; asc     ;;
 4: len 4; hex 8000001a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

上述的:

(1) TRANSACTION(事务1) 指的是事务B (2) TRANSACTION(事务2) 指的是事务A

注意与上面的 事务A, 事务B 顺序是相反了, 别看错了.

分析:

(1) TRANSACTION

insert into c20 values(21,21,21) 最后一句执行语句

(1) WAITING FOR THIS LOCK TO BE GRANTED

index PRIMARY of table test_yjx.c20 说明在等表 c20 主键索引上的锁 lock_mode X locks gap before rec insert intention waiting 说明在插入一条记录, 试图插入一个意向锁, 与间隙锁产生冲突了 0: len 4; hex 80000019; asc ;; 冲突的间隙锁: 16进制的 19, 即 10进制的 id=25 左边的间隙.

(2) TRANSACTION 事务2信息

update c20 set d=d+1 where id=25 最后一句执行语句

(2) HOLDS THE LOCK(S) 事务2持有锁的信息

index PRIMARY of table test_yjx.c20 说明持有c20表主键索引上的锁 lock mode S locks gap before rec 说明只有间隙锁 0: len 4; hex 80000019; asc ;; 间隙锁: id=25 左边的间隙

(2) WAITING FOR THIS LOCK TO BE GRANTED: 事务2正在等待的锁

index PRIMARY of table test_yjx.c20 说明在等待 c20 表主键索引上的锁 lock_mode X locks rec but not gap waiting 需要对行加写锁 0: len 4; hex 80000019; asc ;; 等待给 id=25 加行锁(写) WE ROLL BACK TRANSACTION (1) 表示回滚了事务1

个人猜测实际情况是:

首先找到 id=20 这一条记录, 由于bug, 引擎认为可能存在不止一条的 id=20 的记录(即将其认为是普通索引), 因此向右遍历, 找到了 id=25 这一行, 由于此时是等值查询, 根据优化2, 锁退化为间隙锁, 即 (20,25) 之后正常向左遍历.

无法证实自己的猜测. 已在课程21和课程30留下以下留言, 等待解答(或者无人解答). 2019年9月27日

-- T1 事务A
begin;
select * from c20 where id>=15 and id<=20 order by id desc lock in share mode;

-- T2 事务B
begin;
update c20 set d=d+1 where id=25;    -- OK
insert into c20 values(21,21,21);    -- 阻塞

不能理解, 为什么事务A执行的语句会给 间隙(20,25) 加上锁.
通过 show engine innodb status; 查看发现事务A确实持有上述间隙锁.
通过 explain select * from c20 where id>=15 and id<=20 order by id desc lock in share mode; 查看 Extra 也没有 filesort, key=PRIMARY, 因此个人认为是按照主键索引向左遍历得到结果.

按照我的理解, 由于 order by id desc , 因此首先是在主键索引上搜索 id=20, 同时由于主键索引上这个值是唯一的, 因此不必向右遍历. 然而事实上它确实这么做了, 这让我想到了 BUG1: 主键索引上的范围查询会遍历到不满足条件的第一个.
但是这一步的搜索过程应该是等值查询才对, 完全一脸懵住了...
不知道老师现在还能看到这条评论不?

加锁案例

案例: 主键索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where id=7;
/*
1. 在主键索引上不存在id=7记录, 根据规则1: 加锁基本单位是 next-key lock, 因此加锁范围是(5,10]
2. 由于id=7是一个等值查询, 根据优化2, id=10不满足条件, 因此锁退化为间隙锁 (5,10)
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where id=10;    -- OK

对应课程的案例一

案例: 非唯一索引 - 等值查询 - 间隙锁

-- T1 事务A
begin;
update c20 set d=d+1 where c=7;
/* 分析
1. 加锁基本单位是next-key lock, 加锁范围就是 (5,10]   -- 此时只是分析过程, 并非加锁过程
2. 根据优化2, 索引上的等值查询(c=7)向右遍历且最后一个值不满足条件时, next-key lock 退化为间隙锁, 加锁范围变为 (5, 10)
3. 由于是在索引c上查询, 因此加锁范围实际上是索引 c 上的 ((5,5), (10,10)) , 格式 (c, id)
*/

-- T2 事务B
begin;
insert into c20 values(4,5,4);    -- OK
insert into c20 values(6,5,4);    -- 被间隙锁堵住
insert into c20 values(9,10,9);    -- 被间隙锁堵住
insert into c20 values(11,10,9);    -- OK

案例: 非唯一索引 - 等值查询 - 覆盖索引

关注重点: 覆盖索引优化导致无需回表的情况对主键索引影响

-- T1 事务A
begin;
select id from c20 where c = 5 lock in share mode;    
-- 索引c是普通索引, 因此会扫描到 c=10 这一行, 因此加锁范围是 (0,5], (5,10)
-- 同时由于优化2: 索引上的等值查询向右遍历且最后一个值不满足条件时next-key lock退化为间隙锁, 即加锁范围实际是  (0,5], (5,10)
-- 注意, 该条查询由于只 select id, 实际只访问了索引c, 并没有访问到主键索引, 根据规则2: 访问到的对象才会加锁, 因此最终只对索引c 的范围 (0,5], (5,10) 加锁

-- T2 事务B
begin;
update c20 set d=d+1 where id=5;    -- OK, 因为覆盖索引优化导致并没有给主键索引上加锁
insert into c20 values(7,7,7);

对应课程的案例二

注意, 上面是使用 lock in share mode 加读锁, 因此会被覆盖索引优化.

如果使用 for update, mysql认为你接下来要更新行, 因此也会锁上对应的主键索引.

案例: 非主键索引 - 范围查询 - 对主键的影响

关注重点在于: 普通索引上的范围查询时对不符合条件的索引加锁时, 是否会对对应的主键索引产生影响.

-- T1 事务A
begin;
select * from c20 where c>=10 and c<11 for update;
/*
1. 首先查找到 c=10 这一行, 锁范围 (5,10]
2. 接着向右遍历(这时候不是等值查询, 是遍历查询), 找到 c=15 这一行, 不符合条件, 查询结束. 根据规则2: 只有访问到的对象才会加锁, 由于不需要访问c=15对应的主键索引项, 因此这里的锁范围是索引c上的 (5,10], (10,15], 以及主键上的行锁[10]
*/

-- T2 事务B
begin;
select * from c20 where c=15 for update;     -- 阻塞
select * from c20 where id=15 for update;    -- OK

加锁范围

索引 c

(5,10] (10,15]

主键

[10]

案例: 主键索引 - 范围锁

-- T1 事务A
begin;
select * from c20 where id>=10 and id<11 for update;
/*
1. 首先在主键索引上查找 id=10 这一行, 根据优化1: 索引上的等值查询在对唯一索引加锁时, next-key lock 退化为行锁, 此时加锁范围是 [10]
2. 继续向右遍历到下一个 id=15 的行, 此时并非等值查询, 因此加锁范围是 [10], (10,15]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- OK
insert into c20 values(13,13,13);    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞

对应课程案例三

这里要注意, 事务A首次定位查找id=10这一行的时候是等值查询, 而后续向右扫描到id=15的时候是范围查询判断.

主键索引的加锁范围

[10] (10,15]

案例: 非唯一索引 - 范围锁

-- T1 事务A
begin;
select * from c20 where c >= 10 and c < 11 for update;
/*
1. 首先在索引c上找到 c=10 这一行, 加上锁 (5,10]
2. 向右遍历找到 c=15 这一行, 不满足条件, 最终加锁范围是 索引c上的 (5,10], (10,15], 及主键索引 [5]
*/

-- T2 事务B
begin;
insert into c20 values(8,8,8);        -- 阻塞
update c20 set d=d+1 where c=15;    -- 阻塞
update c20 set d=d+1 where id=15;    -- 阻塞

对应课程案例四

主键的加锁范围

(5,10] (10,15]

案例: 唯一索引 - 范围锁 - bug

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update

-- T2 事务B
begin;
update c20 set d=d+1 where id=20;    -- 阻塞
insert into c20 values(16,16,16);    -- 阻塞

顺便提一下:

begin;
select * from c20 where id>10 and id<15 for update;
/*
1. 在主键索引上找到id=15这一行, 不满足条件, 根据原则1, 加锁 (10,15]
*/

对应课程案例五

主键的加锁范围

(10,15] (15,20]

案例: 非唯一索引 - 等值

-- T1 事务A
begin;
insert into c20 values(30,10,30);
commit;
/*
在索引c上, 此时有两行 c=10 的行
由于二级索引上保存着主键的值, 因此并不会有两行完全一致的行, 如下:
c    0    5    10    10    15    20    25
id    0    5    10    30    15    20    25

此时两个 (c=10, id=10) 和 (c=10, id=30) 之间也是存在间隙的
*/

-- T2 事务B
begin;
delete from c20 where c=10;
/*
1. 首先找到索引c上 (c=10, id=10) 这一行, 加锁 (5,10]
2. 向右遍历, 找到 (c=10, id=30) 这一行, 加锁 ( (c=10,id=10), (c=10,id=30) ]
3. 向右遍历, 找到 c=20 这一行, 根据优化2, 索引上的等值查询向右遍历且最后一个值不匹配时, next-key lock 退化为间隙锁, 即加锁 (10,15)
4. 总的加锁范围是 (5,10], ( (c=10,id=10), (c=10,id=30) ], (10,15]
*/

-- T3 事务C
begin;
insert into c20 values(12,12,12);    -- 阻塞
update c20 set d=d+1 where c=15;    -- OK


-- T4 扫尾, 无视
delete from c20 where id=30;

对应课程案例六

delete 的加锁逻辑跟 select ... for update 是类似的.

事务 B 对索引 c 的加锁范围

(5,10] (10,15)

案例: 非唯一索引 - limit

-- T0 初始环境
insert into c20 values(30,10,30);

-- T1 事务A
begin;
delete from c20 where c=10 limit 2;
/*
1. 找到 c=10 的第一条, 加锁 (5,10]
2. 向右遍历, 找到 c=10,id=30 的记录, 加锁 ( (c=10,id=10), (c=10,id=30) ], 此时满足 limit 2
*/

-- T2, 事务B
begin;
insert into c20 values(12,12,12);    -- OK

如果不加 limit 2 则会继续向右遍历找到 c=15 的记录, 新增加锁范围 (10,15)

对应课程案例七

指导意义:

在删除数据时尽量加 limit, 不仅可以控制删除的条数, 还可以减小加锁的范围.

案例: 死锁例子

-- T1 事务A
begin;
select id from c20 where c=10 lock in share mode;
/*
1. 在索引c上找到 c=10 这一行, 由于覆盖索引的优化, 没有回表, 因此只会在索引c上加锁 (5,10]
2. 向右遍历, 找到 c=15, 不满足, 根据优化2, 加锁范围退化为 (10,15)
3. 总的加锁范围是在索引c上的 (5,10], (10,15)
*/

-- T2 事务B
begin;
update c20 set d=d+1 where c=10;    -- 阻塞
/*
1. 找到 c=10 这一行, 试图加上锁 (5,10], 按照顺序先加上间隙锁(5,10), 由于间隙锁之间不冲突, OK. 之后再加上 [10] 的行锁, 但被T1时刻的事务A阻塞了, 进入锁等待
*/

-- T3 事务A
insert into t values(8,8,8);    -- OK, 但造成 事务B 回滚
/*
往 (5,10) 这个间隙插入行, 此时与 T2时刻事务B 加的间隙锁产生冲突.
同时由于 事务B 也在等待 T1时刻事务A 加的行锁, 两个事务间存在循环资源依赖, 造成死锁.
此时事务B被回滚了, 报错如下:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
*/

对应课程案例八

案例: 非主键索引 - 逆序

-- T1 事务A
begin;
select * from c20 where c>=15 and c<=20 order by c desc lock in share mode;
/*
1. 在索引c上找到 c=20 这一行, 加锁 (15,20]
2. 向左遍历, 找到 c=15 这一行, 加锁 (10,15]
3. 继续向左遍历, 找到 c=10 这一行, 由于不满足优化条件, 因此直接加锁 (5,10], 不满足查询条件, 停止遍历. 
4. 最终加锁范围是 (5,10], (10,15], (15, 20]
*/

-- T2 事务B
insert into c20 values(6,6,6);    -- 阻塞

对应课程的上期答疑

索引 c 的加锁范围

(5,10] (10,15] (15,20] (20, 25)

案例: 读提交级别 - semi-consistent 优化

-- 表结构
create table t(a int not null, b int default null)Engine=Innodb;
insert into t values(1,1),(2,2),(3,3),(4,4),(5,5);

-- T1 事务A
set session transaction isolation level read committed;
begin;
update t set a=6 where b=1;
/*
b没有索引, 因此全表扫描, 对主键索引上所有行加上行锁
*/

-- T2 事务B
set session transaction isolation level read committed;
begin;
update t set a=7 where b=2;    -- OK
/*
在读提交隔离级别下, 如果 update 语句碰到一个已经被锁了的行, 会读入最新的版本, 然后判断是不是满足查询条件, 若满足则进入锁等待, 若不满足则直接跳过.
*/
delete from t where b=3;    -- 阻塞
/*
注意这个策略对 delete 是无效的, 因此delete语句被阻塞
*/

对应课程评论下方 @时隐时现 2019-01-30 的留言

案例: 主键索引 - 动态间隙锁 - delete

-- T1 事务A
begin;
select * from c20 where id>10 and id<=15 for update;
/*
加锁 (10,15], (15, 20]???
*/

-- T2 事务B 注意此处没加 begin, 是马上执行并提交的单个事务.
delete from c20 where id=10;    -- OK
/*
事务A在T1时刻加的间隙锁 (10,15) 此时动态扩展成 (5,15)
*/

-- T3 事务C
insert into c20 values(10,10,10);    -- 阻塞
/*
被新的间隙锁堵住了
*/

对应课程评论下方 @Geek_9ca34e 2019-01-09 的留言

如果将上方的 T2时刻的事务B 和 T3时刻的事务C 合并在一个事务里, 则不会出现这种情况.

个人理解是, 事务未提交时, 期间删除/修改的数据仅仅是标记删除/修改, 此时记录还在, 因此间隙锁范围不变.

只有在事务提价后才会进行实际的删除/修改, 因此间隙锁才"会动态扩大范围"

案例: 普通索引 - 动态间隙锁 - update

-- T1 事务A
begin;
select c from c20 where c>5 lock in share mode;
/*
找到 c=5, 不满足, 向右遍历找到 c=10, 加锁 (5,10], 继续遍历, 继续加锁...
*/

-- T2 事务B
update c20 set c=1 where c=5;    -- OK
/*
删除了 c=5 这一行, 导致 T1时刻事务A 加的间隙锁 (5,10) 变为 (1,10)
*/

-- T3 事务C
update c20 set c=5 where c=1;    -- 阻塞
/*
将 update 理解为两步:
1. 插入 (c=5, id=5) 这个记录    -- 被间隙锁阻塞
2. 删除 (c=1, id=5) 这个记录
*/

案例: 非主键索引 - IN - 等值查询

begin;
select id from c20 where c in (5,20,10) lock in share mode;

通过 explain 分析语句:

mysql> explain select id from c20 where c in (5,20,10) lock in share mode;
+----+-------------+-------+-------+---------------+------+---------+------+------+---------------------
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra     
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
|  1 | SIMPLE      | c20   | range | c             | c    | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+---------
1 row in set, 1 warning (0.00 sec)

显示结果太长, 因此将 partitions, filtered 列删除了

结果分析:

使用了索引 c rows = 3 说明这3个值都是通过 B+ 树搜索定位的

语句分析:

在索引c上查找 c=5, 加锁 (0,5], 向右遍历找到 c=10, 不满足条件, 根据优化2, 加锁 (5,10) 在索引c上查找 c=10, 类似步骤1, 加锁 (5,10], (10, 15) 在索引c上查找 c=20, 加锁 (15,20], (20, 25)

注意上述锁是一个个逐步加上去的, 而非一次性全部加上去.

考虑以下语句:

begin;
select id from c20 where c in (5,20,10) order by id desc for update;

根据语义 order by id desc, 会依次查找 c=20, c=10, c=5.

由于加锁顺序相反, 因此如果这两个语句并发执行的时候就有可能发生死锁.

相关命令

查看最后一个死锁现场

show engine innodb status;

查看 LATEST DETECTED DEADLOCK 这一节, 记录了最后一次死锁信息.

示例

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-24 16:24:18 0x5484
*** (1) TRANSACTION:
TRANSACTION 1400, ACTIVE 191 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1080, 3 row lock(s)
MySQL thread id 54, OS thread handle 74124, query id 36912 localhost ::1 root updating
update c20 set d=d+1 where c=10
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1400 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1401, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1080, 3 row lock(s), undo log entries 1
MySQL thread id 53, OS thread handle 21636, query id 36916 localhost ::1 root update
insert into c20 values(8,8,8)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1401 lock mode S
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23 page no 4 n bits 80 index c of table `test_yjx`.`c20` trx id 1401 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 8000000a; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

结果分为3个部分:

(1) TRANSACTION 第一个事务的信息

WAITING FOR THIS LOCK TO BE GRANTED, 表示这个事务在等待的锁资源

(2) TRANSACTION 第二个事务的信息

HOLDS THE LOCK(S) 显示该事务持有哪些锁 WE ROLL BACK TRANSACTION (1) 死锁检测的处理: 回滚了第一个事务

第一个事务的信息中:

update c20 set d=d+1 where c=10 导致死锁时执行的最后一条 sql 语句

WAITING FOR THIS LOCK TO BE GRANTED

index c of table test_yjx.c20, 说明在等的是表 c20 的索引 c 上面的锁 lock_mode X waiting 表示这个语句要自己加一个写锁, 当前状态是等待中. Record lock 说明这是一个记录锁 n_fields 2 表示这个记录是两列, 即 字段c 和 主键字段 id

0: len 4; hex 8000000a; asc ;; 是第一个字段(即字段c), 值(忽略里面的8)是十六进制 a, 即 10

值 8000000a 中的 8...我也不理解为什么, 先忽略

1: len 4; hex 8000000a; asc ;; 是第二个字段(即字段id), 值是 10

上面两行里的 asc 表示, 接下来要打印出值里面的"可打印字符", 但10不是可打印字符, 因此就显示空格

这里不太理解

第一个事务信息只显示出等锁的状态, 在等待 (c=10, id=10) 这一行的锁 没有显示当前事务持有的锁, 但可以从第二个事务中推测出来.

第二个事务的信息中:

insert into c20 values(8,8,8) 导致死锁时最后执行的语句

HOLDS THE LOCK(S)

index c of table test_yjx.c20 trx id 1401 lock mode S 表示锁是在表 c20 的索引 c 上, 加的是读锁 hex 8000000a;表示这个事务持有 c=10 这个记录锁

WAITING FOR THIS LOCK TO BE GRANTED

index c of table test_yjx.c20 trx id 1401 lock_mode X locks gap before rec insert intention waiting

insert intention 表示试图插入一个记录, 这是一个插入意向锁, 与间隙锁产生锁冲突 gap before rec 表示这是一个间隙锁, 而不是记录锁.

补充:

lock_mode X waiting 表示 next-key lock lock_mode X locks rec but not gap 表示只有行锁 locks gap before rec 就是只有间隙锁

从上面信息可以知道:

第一个事务

推测出持有间隙锁 (?, 10) 试图更新 c=10 这一行, 但被索引c 的 行锁 c=10 阻塞了

第二个事务

持有行锁 c=10 试图插入 (8,8,8), 但被间隙锁 (?, 10) 阻塞了 检测到死锁时, InnoDB 认为 第二个事务回滚成本更高, 因此回滚了第一个事务.

待整理

案例

-- 前提: 表 T 上有普通索引 k

-- 语句1
select * from T where k in (1,2,3,4,5);

-- 语句2
select * from T where k between 1 and 5;

这两条语句的区别是:

语句1: 在索引k上进行了5次树查找

语句2: 在索引k上进行了1次树查找(k=1), 之后向右遍历直到id>5

很明显, 语句2 性能会更好.

三. WAL 机制及脏页刷新

文章链接: https://segmentfault.com/a/11...

本部分主要来自: 极客时间《MySQL实战45讲》的第12讲 - 为什么我的MySQL会“抖”一下

WAL(Write-Ahead Loggin)

WAL 是预写式日志, 关键点在于先写日志再写磁盘.

在对数据页进行修改时, 通过将"修改了什么"这个操作记录在日志中, 而不必马上将更改内容刷新到磁盘上, 从而将随机写转换为顺序写, 提高了性能.

但由此带来的问题是, 内存中的数据页会和磁盘上的数据页内容不一致, 此时将内存中的这种数据页称为 脏页

Redo Log(重做日志)

这里的日志指的是Redo Log(重做日志), 这个日志是循环写入的.

它记录的是在某个数据页上做了什么修改, 这个日志会携带一个LSN, 同时每个数据页上也会记录一个LSN(日志序列号).

这个日志序列号(LSN)可以用于数据页是否是脏页的判断, 比如说 write pos对应的LSN比某个数据页的LSN大, 则这个数据页肯定是干净页, 同时当脏页提前刷到磁盘时, 在应用Redo Log可以识别是否刷过并跳过.

这里有两个关键位置点:

write pos 当前记录的位置, 一边写一边后移. checkpoint 是当前要擦除的位置, 擦除记录前要把记录更新到数据文件.

脏页

当内存数据页和磁盘数据页内容不一致的时候, 将内存页称为"脏页".
内存数据页写入磁盘后, 两边内容一致, 此时称为"干净页".
将内存数据页写入磁盘的这个操作叫做"刷脏页"(flush).

InnoDB是以缓冲池(Buffer Pool)来管理内存的, 缓冲池中的内存页有3种状态:

未被使用 已被使用, 并且是干净页 已被使用, 并且是脏页

由于InnoDB的策略通常是尽量使用内存, 因此长时间运行的数据库中的内存页基本都是被使用的, 未被使用的内存页很少.

刷脏页(flush)

时机

刷脏页的时机:

Redo Log写满了, 需要将 checkpoint 向前推进, 以便继续写入日志

checkpoint 向前推进时, 需要将推进区间涉及的所有脏页刷新到磁盘.

内存不足, 需要淘汰一些内存页(最久未使用的)给别的数据页使用.

此时如果是干净页, 则直接拿来复用.

如果是脏页, 则需要先刷新到磁盘(直接写入磁盘, 不用管Redo Log, 后续Redo Log刷脏页时会判断对应数据页是否已刷新到磁盘), 使之成为干净页再拿来使用.

数据库系统空闲时

当然平时忙的时候也会尽量刷脏页.

数据库正常关闭

此时需要将所有脏页刷新到磁盘.

InnoDB需要控制脏页比例来避免Redo Log写满以及单次淘汰过多脏页过多的情况.

Redo Log 写满

这种情况尽量避免, 因此此时系统就不接受更新, 所有更新语句都会被堵住, 此时更新数为0.

对于敏感业务来说, 这是不能接受的.

此时需要将 write pos 向前推进, 推进范围内Redo Log涉及的所有脏页都需要flush到磁盘中.

Redo Log设置过小或写太慢的问题: 此时由于Redo Log频繁写满, 会导致频繁触发flush脏页, 影响tps.

内存不足

这种情况其实是常态.

当从磁盘读取的数据页在内存中没有内存时, 就需要到缓冲池中申请一个内存页, 这时候根据LRU(最近最少使用算法)就需要淘汰掉一个内存页来使用.

此时淘汰的是脏页, 则需要将脏页刷新到磁盘, 变成干净页后才能复用.

注意, 这个过程 Write Pos 位置是不会向前推进的.

当一个查询要淘汰的脏页数太多, 会导致查询的响应时间明显变长.

策略

InnoDB 控制刷脏页的策略主要参考:

脏页比例

当脏页比例接近或超过参数 innodb_max_dirty_pages_pct 时, 则会全力, 否则按照百分比.

redo log 写盘速度

N = (write pos 位置的日志序号 - checkpoint对应序号), 当N越大, 则刷盘速度越快.

最终刷盘速度取上述两者中最快的.

参数 innodb_io_capacity

InnoDB 有一个关键参数: innodb_io_capacity, 该参数是用于告知InnoDB你的磁盘能力, 该值通常建议设置为磁盘的写IOPS.

该参数在 MySQL 5.5 及后续版本才可以调整.

测试磁盘的IOPS:

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

注意, 上面的 -filename 要指定具体的文件名, 千万不要指定分区, 否则会导致分区不可用, 需要重新格式化.

innodb_io_capacity 一般参考 写能力的IOPS

innodb_io_capacity 设置过低导致的性能问题案例:

MySQL写入速度很慢, TPS很低, 但是数据库主机的IO压力并不大.

当innodb_io_capacity 设置过小时, InnoDB会认为磁盘性能差, 导致刷脏页很慢, 甚至比脏页生成速度还慢, 就会造成脏页累积, 影响查询和更新性能.

innodb_io_capacity 大小设置:

配置小, 此时由于InnoDB认为你的磁盘性能差, 因此刷脏页频率会更高, 以此来确保内存中的脏页比例较少. 配置大, InnoDB认为磁盘性能好, 因此刷脏页频率会降低, 抖动的频率也会降低.

参数innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct 指的是脏页比例上限(默认值是75%), 内存中的脏页比例越是接近该值, 则InnoDB刷盘速度会越接近全力.

如何计算内存中的脏页比例:

show global status like 'Innodb_buffer_pool_pages%';

脏页比例 = 100 * Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 的值

参数 innodb_flush_neighbors

当刷脏页时, 若脏页旁边的数据页也是脏页, 则会连带刷新, 注意这个机制是会蔓延的.

当 innodb_flush_neighbors=1 时开启该机制, 默认是1, 但在 MySQL 8.0 中默认值是 0.

由于机械硬盘时代的IOPS一般只有几百, 该机制可以有效减少很多随机IO, 提高系统性能.

但在固态硬盘时代, 此时IOPS高达几千, 此时IOPS往往不是瓶颈, "只刷自己"可以更快执行完查询操作, 减少SQL语句的响应时间.

如果Redo Log 设置太小

这里有一个案例:

测试在做压力测试时, 刚开始 insert, update 很快, 但是一会就变慢且响应延迟很高.

↑ 出现这种情况大部分是因为 Redo Log 设置太小引起的.

因为此时 Redo Log 写满后需要将 checkpoint 前推, 此时需要刷脏页, 可能还会连坐(innodb_flush_neighbors=1), 数据库"抖"的频率变高.

其实此时内存的脏页比例可能还很低, 并没有充分利用到大内存优势, 此时需要频繁flush, 性能会变差.

同时, 如果Redo Log中存在change buffer, 同样需要做相应的merge操作, 导致 change buffer 发挥不出作用.

对于实际场景:

在一台高性能机器上配置了非常小的Redo Log.

此时由于每次都很快写满Redo Log, 此时Write Pos会一直追着Check Point, 因此系统就会停止所有更新, 去推进 Check Point.

此时看到的现象就是: 磁盘压力很小, 但是数据库出现间歇性性能下降.

待整理

ORDER BY 的工作方式

关键字:

Using filesort, sort_buffer sort_buffer_size, 磁盘临时文件 全字段排序, OPTIMIZER_TRACE，sort_mode，num_of_tmp_files rowid 排序, max_length_for_sort_data

原文: https://time.geekbang.org/col...

TODO