共计 28921 个字符，预计需要花费 73 分钟才能阅读完成。
[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=50Change 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_ciALTER 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) TRANSACTIONinsert into c20 values(21,21,21)最初一句执行语句
-
(1) WAITING FOR THIS LOCK TO BE GRANTEDindex 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 GRANTEDindex 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 waitinginsert intention示意试图插入一个记录, 这是一个插入意向锁, 与间隙锁产生锁抵触gap before rec示意这是一个间隙锁, 而不是记录锁.
-
补充:
lock_mode X waiting示意 next-key locklock_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
