MySQL 必知必会

1、什么是 B+树?
  • B+树定义:
    每个节点最多有 m 个子女。
    除根节点外,每个节点至少有 [m/2] 个子女,根节点至少 2 个子女。
    有 k 个子女的节点必有 k 个关键字。
  • B+树特征:
    叶子存放了数据,而非叶子节点只是存放了关键字。
    叶子节点被链表串联起来了。
  • B+树优势:
    B+ 树的高度和二叉树之类的比起来更低,树的高度代表了查询的耗时,所以查询性能更好。
    B+ 树的叶子节点都被串联起来了,适合范围查询。
    B+ 树的非叶子节点没有存放数据,所以适合放入内存中。
2、为何 MySQL 用 B+树,而不是红黑树或跳表?
  • 磁盘I/O效率:B+树的层级显著低于跳表和红黑树
    B+树的扇出优势:B+树是多路平衡树,每个节点(即磁盘页)可存储大量键值(例如,16KB的页可存数千个键),导致树的高度极低。通常3层即可存储约2000万数据,而跳表在相同数据量下可能需要24层。由于MySQL的索引数据存储在磁盘上,每次磁盘I/O对应一次树层访问,B+树的低层高特性大幅减少I/O次数,提升查询速度。
    跳表的层级劣势:跳表的层级由随机算法决定,数据量越大层级越高。例如,2000万数据的跳表可能需要24层,导致每次查询需进行24次磁盘I/O,性能远低于B+树的3次。
    红黑树的树高问题:红黑树是二叉树,数据量大时树高可达数十层,且每个节点仅存储一个键值,导致磁盘I/O次数急剧增加,不适合数据库的高频磁盘访问场景。
  • 范围查询与顺序访问的优化
    B+树的叶子节点链表:B+树的叶子节点通过双向链表连接,支持高效的范围查询(如WHERE id BETWEEN 10 AND 100)。查询时只需定位到起始节点,后续通过链表遍历即可,无需跨层访问。而跳表和红黑树需逐层遍历,效率较低。
    数据局部性:B+树的叶子节点存储连续数据,磁盘读取时可批量加载相邻数据页,利用磁盘的顺序读取特性,进一步减少I/O开销。跳表的随机层级结构破坏了数据局部性,增加磁盘寻址时间。
  • 数据结构的稳定性与平衡性
    B+树的写操作优化:B+树的插入和删除操作主要集中在叶子节点,通过节点分裂或合并维持平衡,减少了对内部节点的影响。相比之下,红黑树的旋转操作和跳表的随机层数调整可能导致数据分布不均,增加维护开销。
    跳表的写性能优势与局限性:跳表在写入时无需旋转或分裂节点,性能优于B+树,但其读性能劣势在MySQL的读多写少场景中成为关键瓶颈。例如,RocksDB存储引擎虽使用跳表提升写入速度,但读性能仍逊于InnoDB的B+树。
  • 存储引擎与页结构的适配
    MySQL的页机制:InnoDB将索引数据存储为固定大小的页(通常16KB),B+树的节点设计与页结构高度契合。每个节点可存储多个键值,最大化利用页空间,减少碎片。跳表的链表结构无法有效利用固定页大小,导致空间浪费。
    缓存效率:B+树的内部节点仅存储键值,可缓存在内存中,减少磁盘访问。而跳表和红黑树的中间节点可能包含冗余数据,降低缓存利用率。
3、MySQL innoDB 有哪些类型的索引?
  • 聚簇索引&非聚簇索引
    如果索引叶子节点存储的是数据行,那么它就是聚簇索引,否则就是非聚簇索引。
    简单来说,某个数据表本身你就可以看作是一棵使用主键搭建起来 B+ 树,这棵树的叶子节点放着表的所有行。而其他索引也是 B+ 树,不同的是它们的叶子节点存放的是主键。
    如果你查询一张表,用到了索引,那么数据库就会先在索引里面找到主键,然后再根据主键去聚簇索引中查找,最终找出数据。这个数据行存放在磁盘里,所以触发磁盘 IO 之后能够读取出来。磁盘 IO 是非常慢的,因此回表性能极差,你在实践中要尽可能避免回表。
  • 覆盖索引:如果你查询的列全部都在某个索引里面,那么数据库可以直接把索引存储的这些列的值给你,而不必回表。
  • 唯一索引:索引的值必须是唯一的,不能重复
  • 前缀索引:索引的某个列,只包含该列值的前一部分。如说在一个类型是 varchar(128) 的列上,选择前 64 个字符作为索引。
  • 组合索引:某个索引由多个列组成,也叫联合索引

索引最左匹配原则(例如,A、B、C 三个列上的组合索引 <A, B, C>):

  • WHERE A=a1 AND B=b1 AND C=c1,索引会使用A,、B,、C三列,查询类似:
for a in A {
  if a == a1 {
    for b in B {
      if b == b1 {
        for c in C {
          if c == c1 {
            // 这就是你要的数据,拿到主键之后去磁盘里面加载出来
          }
        }
      }
    }
  }
}
  • WHERE A = a1 AND B = b1,索引会使用A、B两列,查询类似:
for a in A {
  if a == a1 {
      for b in B {
        if b == b1 {
          // 这就是你要的结果,去磁盘里面读取
        }
      }
  }
}
  • WHERE A = a1 OR B = b1,不会走到索引,查询类似:
for a in A {
  if a == a1 {
      as = append(as, a)
  }
}
for b in B {
  if b == b1 {
      bs = append(bs, b)
  }
}
// as 和 bs 的并集就是你要的结果
  • WHERE A=a1 AND B > b1 AND C = c1,索引会使用A、B两列,查询类似:
for a in A {
  if a == a1 {
      for b in B {
        if b > b1 {
          // C 是无序的,所以用不了。你可以从前面的表格里面看出来
          // 比如说 b > 23 之后,对应的 C 是乱序的
          // 这就是你要的结果,去磁盘里面读取
        }
      }
  }
}
  • WHERE A !=a1,不会走到索引。

记忆口诀,从左往右:AND 用 OR 不用,正用反不用,范围则中断,终极武器 Explain看看。

索引的代价:

  • 索引本身需要存储起来,消耗磁盘空间。
  • 在运行的时候,索引会被加载到内存里面,消耗内存空间
  • 在增删改的时候,数据库还需要同步维护索引,引入额外的消耗。
4、MySQL 什么场景可能不会使用索引?
  • 使用了 !=、LIKE 之类的查询
  • 字段区分度不大。比如说你的 status 列只有 0 和 1 两个值,那么数据库也有可能不用。
  • 使用了特殊表达式,包括数学运算和函数调用。
  • 数据量太小,或者 MySQL 觉得全表扫描反而更快的时候。
5、你平常如何进行 SQL 优化的?
5.1 SQL 优化的2 个目标:
  • 减少磁盘 IO,这个又可以说是尽量避免全表扫描、尽量使用索引以及尽量使用覆盖索引。
  • 减少内存 CPU 消耗,这一部分主要是尽可能减少排序、分组、去重之类的操作。
5.2 Explain 命令:
  • type:指的是查询到所需行的方式,从好到坏依次是 system > const > eq_ref > ref > range > index > ALL
    system 和 const 都可以理解为数据库只会返回一行数据,所以查询时间是固定的。
    eq_ref 和 ref 字面意思是根据索引的值来查找。
    range:索引范围扫描。
    index:索引全表扫描,也就是扫描整棵索引。
    ALL:全表扫描,发起磁盘 IO 的那种全表扫描。
  • possible_keys:候选的索引。
  • key:实际使用的索引。
  • rows:扫描的行数。数据库可能扫描了很多行之后才找到你需要的数据。
  • filtered:查找到你所需的数据占 rows 的比例。
5.3 选择索引列
  • 外键,一般都会用于关联、过滤数据,所以正常来说都会为表的外键创建索引。
  • 频繁出现在 WHERE 中的列,主要是为了避免全表扫描。
  • 频繁出现在 ORDER BY 的列,这是为了避免数据库在查询出来结果之后再次排序。
  • 区分度很高的列。比如每一行的数据都不同的列,并且在创建组合索引的时候,区分度很高的列应该尽可能放到左边。
5.4 Alter改表操作(特别是大表)

MySQL上直接执行Alter语句存在诸多风险,例如Alter引起表锁、Alter耗时太久引起主从延迟等等。为了避免这些风险,某司数据库平台通过gh-ost工具实现安全在线修改表结构,大致操作步骤及原理为:

  • 在DB上创建ghost临时表,表结构与目标表相同,ghost临时表以下简称为新表。
  • 新表上直接应用用户提交的Alter语句,完成表结构更新。
  • 分批次迁移原表全量数据到新表。
  • 同时拉取解析binlog事件,将任务期间的新增数据应用到新表。
  • 数据完全同步后,通过Rename语句使新表替换老表。
5.5 总结下,常见的 SQL 优化手段有哪些?

高频 SQL尽量使用覆盖索引,避免回表操作。如 select * 变为 select 特定列(命中覆盖索引)
优化 Order By,即将排序列加入索引,避免数据库二次排序的消耗
如果不是使用聚合函数来作为过滤条件,最好还是将HAVING过滤条件优先写到 WHERE 里面。优化分页偏移量,即 limit offset limit 改为 where xx > lastMaxId limit limit

6、谈谈你对 MySQL 锁的理解?
6.1 锁与索引关系
  • 在 MySQL 的 InnoDB 引擎里面,锁是借助索引来实现的。或者说,加锁锁住的其实是索引项,更加具体地来说,就是锁住了叶子节点。
  • 一个表有很多索引,锁的是哪个索引呢?其实就是查询最终使用的那个索引。万一查询没有使用任何索引呢?那么锁住的就是整个表,也就是此时退化为表锁。
  • 如果查询条件的值并不存在,id = 15 的值根本不存在,那么怎么锁?
SELECT * FROM your_tab WHERE id = 15 FOR UPDATE

InnoDB 引擎会利用最接近 15 的相邻的两个节点,构造一个临键锁。此时如果别的事务想要插入一个 id=15 的记录,就不会成功。

  • 范围查询也是利用索引上的数据,构造一个恰好能够装下这个范围的临键锁。例如
SELECT * FROM your_tab WHERE id > 33 FOR UPDATE

InnoDB 引擎会构造一个 (33,supremum] 的临键锁,锁住整个范围。supremum 你可以直观理解为 MySQL 认为的一个虚拟的最大值。

6.2 释放锁时机

当一个事务内部给数据加上锁之后,只有在执行 Rollback 或者 Commit 的时候,锁才会被释放掉。

6.3 乐观锁与悲观锁
  • 乐观锁是直到要修改数据的时候,才检测数据是否已经被别人修改过。
SELECT * FROM your_tab WHERE id = 1; // 在这里拿到了 a = 1
// 一大堆的业务操作
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1 AND a =1
  • 悲观锁是在初始时刻就直接加锁保护好临界资源。
SELECT * FROM your_tab WHERE id = 1 FOR UPDATE; // 在这里拿到了 a = 1
// 一大堆的业务操作
UPDATE your_tab SET a = 3, b = 4 WHERE id = 1
  • 乐观锁适用于读多写少的场景,互联网中大部分应用都属于这一类。而悲观锁则适用于写多读少的场景,比如在金融领域里面对金额的操作就是以写为主。
6.4 行锁与表锁
  • 行锁是指锁住行,可能是锁住一行,也可能是锁住多行。表锁则是直接将整个表都锁住。
  • 在 MySQL 里面,InnoDB 引擎同时支持行锁和表锁。但是行锁是借助索引来实现的,也就是说,如果你的查询没有命中任何的索引,那么 InnoDB 引擎是用不了行锁的,只能使用表锁。
6.5 共享锁与排他锁
  • 共享锁(S Lock)
    定义:允许事务读取某一行数据,多个事务可同时持有同一行的共享锁,但会阻止其他事务对该行加排他锁。用途:确保事务读取到最新已提交的数据,且多个读操作可并行执行。
  • 排他锁(X Lock)
    定义:允许事务修改(更新、删除或插入)某一行数据,且同一时间仅允许一个事务持有该行的排他锁,其他事务的读写操作均被阻塞。用途:保证数据修改的独占性,防止并发写入冲突。


    image.png

共享锁的典型场景

  • 并发读取:多个事务可同时读取同一行数据,但无法修改。
SELECT * FROM table WHERE id=1 FOR SHARE MODE;  -- 显式加共享锁
  • 快照读与当前读:
    快照读(默认SELECT):不加锁,直接读取历史版本数据。
    当前读(如SELECT ... FOR SHARE):加共享锁,确保读取最新数据。

排他锁的典型场景

  • 数据修改:插入、更新、删除操作会自动加排他锁。
UPDATE table SET value=100 WHERE id=1;  -- 自动加X Lock
  • 显式加锁:
SELECT * FROM table WHERE id=1 FOR UPDATE;  -- 显式加排他锁
6.6 记录锁、间隙锁和临键锁
  • 记录锁,当用主键或唯一索引查询时,若命中,则加记录锁;若未命中,加间隙锁。举个例子,如果数据库中只有 id 为(1,4,7)的三条记录,也就是 id= 3 这个条件没有命中任何数据,那么这条语句会在(1,4)加上间隙锁。
SELECT * FROM your_tab WHERE id = 31 FOR UPDATE
SELECT * FROM your_tab WHERE email='your_email' FOR UPDATE #email有唯一索引
  • 间隙锁,是锁住了某一段记录的锁。直观来说就是你锁住了一个范围的记录。比如说你在查询的时候使用了 <、<=、BETWEEN 之类的范围查询条件,就会使用间隙锁。
SELECT * FROM your_tab WHERE id BETWEEN 50 AND 100 FOR UPDATE

如果你的表里面没有 50,那么数据库就会一直向左,找到第一个存在的数据,比如说 40;如果你的表里面没有 100,那么数据库就会一直向右,找到第一个存在的数据,比如说 120。那么使用的间隙锁就是 (40,120)。如果此时有人想要插入一个主键为 70 的行,是无法插入的,它需要等这个 SELECT 语句释放掉间隙锁。
间隙锁我们一般都说两边都是开的,即端点是没有被间隙锁锁住的。记录锁和记录锁是排它的,但是间隙锁和间隙锁不是排它的。也就是说两个间隙锁之间即便重叠了,也还是可以加锁成功的。

  • 临键锁(Next-Key Locks)是很独特的一种锁,直观上来说可以看做是一个记录锁和间隙锁的组合。也就是说临键锁不仅仅是会用记录锁锁住命中的记录,也会用间隙锁锁住记录之间的空隙。临键锁和数据库隔离级别的联系最为紧密,它可以解决在可重复读隔离级别之下的幻读问题。
    间隙锁是左开右开,而临键锁是左开右闭。还是用前面的例子来说明。如果 id 只有(1,4,7)三条记录,那么临键锁就将(1,4]锁住。

识别临键锁、间隙锁和记录锁总结:

  • 遇事不决临键锁。你可以认为,全部都是加临键锁的,除了下面两个子句提到的例外情况。
  • 右边缺省间隙锁。例如你的值只有(1,4,7)三个,但是你查询的条件是 WHERE id < 5,那么加的其实是间隙锁,因为 7 本身不在你的条件范围内。
  • 等值查询记录锁。这个其实针对的是主键和唯一索引,普通索引只适用上面两条。
6.7 总结下对锁的理解

MySQL 里面的锁机制特别丰富,这里我以 InnoDB 引擎为例。首先,从锁的范围来看,可以分成行锁和表锁。其次,从排它性来看,可以分成排它锁和共享锁。还有意向锁,结合排它性,就分为排它意向锁和共享意向锁。还有三个重要的锁概念,记录锁、间隙锁和临键锁。记录锁,是指锁住某条记录;间隙锁,是指锁住两条记录之间的位置;临键锁可以看成是记录锁与间隙锁的组合情况。

还有一种分类方法,是乐观锁和悲观锁。那么在数据库里面使用乐观锁,本质上是一种应用层面的 CAS 操作。

在 MySQL 的 InnoDB 引擎里面,锁和索引、隔离级别都是有密切关系的。在 InnoDB 引擎里面,锁是依赖于索引来实现的。或者说,锁都是加在索引项上的。因此,如果一个查询用了索引,那么会用行锁,如果没用到任何索引,那么就会用表锁。此外,在 MySQL 里面,间隙锁和临键锁是只工作在可重复读这个隔离级别下的。

7、锁优化案例
7.1 临建锁导致的死锁
BEGIN;
SELECT * FROM biz WHERE id = ? FOR UPDATE
// 中间有很多业务操作
INSERT INTO biz(id, data) VALUE(?, ?);
COMMIT;
  • 假设说现在数据库中 ID 最大的值是 78。那么如果两个业务进来,同时执行这个逻辑。一个准备插入 id=79 的数据,一个准备插入 id = 80 的数据。如果它们的执行时序如下图,那么你就会得到一个死锁错误。
  • 在线程 1 执行 SELECT FOR UPDATE 的时候,因为 id=79 的数据不存在,所以实际上数据库会产生一个 (78,supremum] 的临键锁。类似地,线程 2 也会产生一个 (78,supremum] 临键锁。
  • 当线程 1 想要执行插入的时候,它想要获得 id = 79 的行锁。当线程 2 想要执行插入的时候,它想要获得 id = 80 的行锁,这个时候就会出现死锁。因为线程 1 和线程 2 同时还在等着对方释放掉持有的间隙锁。

解决方案:直接插入数据或去除 for update 悲观锁,改为乐观锁

7.2 弃用悲观锁
  • 优化前:
// 开启事务
Begin()
// 查询到已有的数据 SELECT * FROM xxx WHERE id = 1 FOR UPDATE
data := SelectForUpdate(id) 
newData := calculate(data) // 一大通计算

// 将新数据写回去数据库 UPDATE xxx SET data = newData WHERE id =1
Update(id, newData) 
Commit()
  • 优化后:
for {
  // 查询到已有的数据 SELECT * FROM xxx WHERE id = 1
  data := Select(id) 
  newData := calculate(data) // 一大通计算

  // 将新数据写回去数据库 
  // UPDATE xxx SET data = newData WHERE id =1 AND data=oldData
  success := CAS(id, newData, data) 
  // 确实更新成功,代表在业务执行过程中没有人修改过这个 data。
  // 适合读多写少的情况
  if success {
    break;
  }
}

结果:性能提升 xx%

7.3 调价任务死锁案例1
  • 表结构:
CREATE TABLE `task_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `task_code` varchar(255) DEFAULT '0' COMMENT '任务ID',
  `task_status` tinyint(3) DEFAULT '0' COMMENT '任务状态,1-待处理,2-已处理,3-已失效,4-已终止, 5-处理中',
  `task_create_time` datetime DEFAULT '1970-01-01 00:00:00' COMMENT '任务创建时间',
  ...
  PRIMARY KEY (`id`),
  KEY `idx_task_code_status` (`task_code`,`task_status`),
  KEY `idx_task_status` (`task_status`),
  KEY `idx_create_time` (`task_create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调价任务表'
  • 死锁日志1:
 LATEST DETECTED DEADLOCK
------------------------
2024-12-28 18:55:04 0x7f94e45f8700
*** (1) TRANSACTION:
TRANSACTION 159955256119, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 212 lock struct(s), heap size 24784, 489 row lock(s), undo log entries 115
MySQL thread id 41404788, OS thread handle 140267772720896, query id 68455380662 10.201.33.24 kms_kl_pom_azhh updating
/*id:6fab7bf3*//*ip=xx*/UPDATE task_info SET task_status = 2, last_modify_time = '2024-12-28 18:55:04.669', last_modify_by = 'http:handongxu03:韩冬旭' WHERE ((task_code IN ('pom225924312', 'pom225933252', 'pom225941021', 'pom226050763', 'pom226050764', 'pom226054546', 'pom226055915', 'pom226055927', 'pom226056520', 'pom226056885', 'pom226056923', 'pom226056928', 'pom226057449', 'pom226057450', 'pom226057864', 'pom226058812', 'pom226058827', 'pom226059434', 'pom226060905', 'pom226060987', 'pom226061680', 'pom226061909', 'pom226061910', 'pom226062591', 'pom226062670', 'pom226063685', 'pom226064660', 'pom226064682', 'pom226064784', 'pom226064799', 'pom226064807', 'pom226065267', 'pom226065268', 'pom226065270', 'pom226065694', 'pom226065831', 'pom226066513', 'pom226066525', 'pom226066778', 'pom226066779', 'pom226066932', 'pom226067173', 'pom226067559', 'pom226067886', 'pom226068229'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8977 page no 5225297 n bits 448 index idx_task_code_status of table `xx`.`task_info` trx id 159955256119 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 159955256126, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
54 lock struct(s), heap size 8400, 121 row lock(s), undo log entries 16
MySQL thread id 41405050, OS thread handle 140277463353088, query id 68455380676 10.38.127.237 xx updating
/*id:6fab7bf3*//*ip=xx*/UPDATE task_info SET task_status = 2, last_modify_time = '2024-12-28 18:55:04.68', last_modify_by = 'crane:系统:crane' WHERE ((task_code IN ('pom226077742', 'pom226087278', 'pom226089244', 'pom226078709', 'pom226073866', 'pom226083463', 'pom226099014', 'pom226079710', 'pom226095055', 'pom226086419', 'pom226076840', 'pom226094126', 'pom226095056', 'pom226093176', 'pom226094127', 'pom226096073', 'pom226078711', 'pom226078712', 'pom226091238', 'pom226090260', 'pom226091239', 'pom226076841', 'pom226096074', 'pom226087289', 'pom226071916', 'pom226079721', 'pom226093178', 'pom226095058', 'pom226076844', 'pom226081592', 'pom226090265', 'pom226087294', 'pom226091244', 'pom226082481') AND task_status IN (1, 5) AND istatus = 1)) AND (channel_id = 1001)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8977 page no 5225297 n bits 448 index idx_task_code_status of table `xx`.`task_info` trx id 159955256126 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8977 page no 5225287 n bits 640 index idx_task_code_status of table `xx`.`task_info` trx id 159955256126 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
  • 死锁场景:批量更新,task_info字段未排序,导致两事务的间隙锁互相等待死锁。
7.4 调价任务死锁案例2
  • 表结构:
CREATE TABLE `task_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `task_code` varchar(255) DEFAULT '0' COMMENT '任务ID',
  `task_status` tinyint(3) DEFAULT '0' COMMENT '任务状态,1-待处理,2-已处理,3-已失效,4-已终止, 5-处理中',
  `task_create_time` datetime DEFAULT '1970-01-01 00:00:00' COMMENT '任务创建时间',
  ...
  PRIMARY KEY (`id`),
  KEY `idx_task_code_status` (`task_code`,`task_status`),
  KEY `idx_task_status` (`task_status`),
  KEY `idx_create_time` (`task_create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调价任务表'
  • 死锁日志:
 LATEST DETECTED DEADLOCK
------------------------
2024-12-31 10:06:48 0x7f94dc17b700
*** (1) TRANSACTION:
TRANSACTION 160505868556, ACTIVE 2 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 37356 lock struct(s), heap size 3350736, 157381 row lock(s), undo log entries 1
MySQL thread id 41405039, OS thread handle 140261081413376, query id 69019001715 10.122.212.58 kms_kl_pom_azhh Searching rows for update
/*id:6fab7bf3*//*ip= xx*/UPDATE task_info SET task_status = 2, last_modify_time = '2024-12-31 10:06:46.978', last_modify_by = 'http:system:系统' WHERE ((task_code IN ('pom227236219') AND task_status IN (1, 5) AND istatus = 1)) AND (channel_id = 1001)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8977 page no 1136068 n bits 80 index PRIMARY of table `xx`.`task_info` trx id 160505868556 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 160505871819, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 41405040, OS thread handle 140277324429056, query id 69019006415 10.122.212.58 xx updating
/*id:6fab7bf3*//*ip=xx*/UPDATE task_info SET task_code = 'pom227217035', task_status = 4, last_modify_time = '2024-12-31 10:06:48.138', last_modify_by = 'mq:system:system' WHERE ((task_code = 'pom227217035' AND task_status IN (1, 5) AND istatus = 1)) AND (channel_id = 1001)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8977 page no 1136068 n bits 80 index PRIMARY of table `xx`.`task_info` trx id 160505871819 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8977 page no 537892 n bits 1120 index idx_task_status of table `xx`.`task_info` trx id 160505871819 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
  • 死锁场景:IN查询和等值查询的执行计划不同,导致加锁顺序不同
-- 事务1
UPDATE task_info 
SET task_status = 2
WHERE task_code IN ('pom227236219')  -- 注意这里是IN
AND task_status IN (1, 5);

-- 事务2
UPDATE task_info 
SET task_status = 4
WHERE task_code = 'pom227217035'     -- 注意这里是等值
AND task_status IN (1, 5);

事务1
task_code IN (...) 语法可能导致优化器选择 idx_task_status
执行顺序:
先锁 idx_task_status
再尝试获取主键锁(回表)
事务2
task_code = 'xxx' 精确匹配,优化器选择 idx_task_code_status
执行顺序:
先锁主键(通过二级索引快速定位)
再锁其他相关索引
  • 优化方案
-- 1. 统一使用等值更新
UPDATE task_info 
SET task_status = 2
WHERE task_code = 'pom227236219'
AND task_status = 1;

-- 2. 强制使用同一个索引
UPDATE task_info FORCE INDEX(idx_task_code_status)
SET task_status = 2
WHERE task_code IN ('pom227236219')
AND task_status IN (1, 5);
7.5 调价任务死锁案例3

TODO

8、MySQL 的 InnoDB 引擎是怎么控制数据并发访问的?

主要通过 MVCC(多版本并发控制)来控制并发的:

  • 单纯使用锁的时候,并发性能会比较差。即便是在读写锁这种机制下,读和写依旧是互斥的。而数据库是一个性能非常关键的中间件,如果某个线程修改某条数据就让其他线程都不能读这条数据,这种性能损耗是无法接受的。所以 InnoDB 引擎引入了 MVCC,就是为了减少读写阻塞。
  • MVCC 是 MySQL InnoDB 引擎用于控制数据并发访问的协议。MVCC 主要是借助于版本链来实现的。在 InnoDB 引擎里面,每一行都有两个额外的列,一个是 trx_id,代表的是修改这一行数据的事务 ID。另外一个是 roll_ptr,代表的是回滚指针。InnoDB 引擎通过回滚指针,将数据的不同版本串联在一起,也就是版本链。这些串联起来的历史版本,被放到了 undolog 里面。当某一个事务发起查询的时候,MVCC 会根据事务的隔离级别来生成不同的 Read View,从而控制事务查询最终得到的结果。
  • Read View 只用于已提交读和可重复读两个隔离级别,它用于这两个隔离级别的不同点就在于什么时候生成 Read View。已提交读:事务每次发起查询的时候,都会重新创建一个新的 Read View。可重复读:事务开始的时候,创建出 Read View。
image.png

image.png
9、MySQL 的事务隔离级别有哪些?
  • 读未提交(Read Uncommitted)是指一个事务可以看到另外一个事务尚未提交的修改。
  • 读已提交(Read Committed,简写 RC)是指一个事务只能看到已经提交的事务的修改。这意味着如果在事务执行过程中有别的事务提交了,那么事务还是能够看到别的事务最新提交的修改。
  • 可重复读(Repeatable Read,简写 RR)是指在这一个事务内部读同一个数据多次,读到的结果都是同一个。这意味着即便在事务执行过程中有别的事务提交,这个事务依旧看不到别的事务提交的修改。这是 MySQL 默认的隔离级别。
  • 串行化(Serializable)是指事务对数据的读写都是串行化的。

三个读异常:

  • 脏读是指读到了别的事务还没有提交的数据。之所以叫做“脏”读,就是因为未提交数据可能会被回滚掉。
  • 不可重复读是指在一个事务执行过程中,对同一行数据读到的结果不同。
  • 幻读是指在事务执行过程中,别的事务插入了新的数据并且提交了,然后事务在后续步骤中读到了这个新的数据。
隔离级别与读异常 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 不可能(理论上可能)
串行化 不可能 不可能 不可能

理论上来说可重复读是没有解决幻读的。但是 MySQL 因为使用了临键锁,因此它的可重复读隔离级别已经解决了幻读问题。

9、详细谈谈MySQL 几种日志及事务提交过程?
9.1 undo log

undo log 是指回滚日志,当事务回滚的时候,InnoDB 会根据 undo log 里的数据撤销事务的更改,把数据库恢复到原来的状态。

  • 对于 INSERT 来说,对应的 undo log 应该是 DELETE。对于 INSERT 来说,对应的 undo log 记录了该行的主键。那么后续只需要根据 undo log 里面的主键去原本的聚簇索引里面删掉记录,就可以实现回滚效果。


    image.png
  • 对于 DELETE 来说,对应的 undo log 应该是 INSERT。对于 DELETE 来说,对应的 undo log 记录了该行的主键。因为在事务执行 DELETE 的时候,实际上并没有真的把记录删除,只是把原记录的删除标记位设置成了 true。所以这里 undo log 记录了主键之后,在回滚的时候就可以根据 undo log 找到原本的记录,然后再把删除标记位设置成 false。


    image.png
  • 对于 UPDATE 来说,对应的 undo log 也应该是 UPDATE。比如说有一个数据的值原本是 3,要把它更新成 5。那么对应的 undo log 就是把数据更新回 3。
    如果没有更新主键,那么 undo log 里面就记录原记录的主键和被修改的列的原值。


    image.png

    如果更新了主键,那么可以看作是删除了原本的行,然后插入了一个新行。因此 undo log 可以看作是一个 DELETE 原数据的 undo log 再加上插入一个新行的 undo log。


    image.png

undo log 和 MVCC 中版本链的关系


image.png
9.2 redo log

InnoDB 引擎在数据库发生更改的时候,把更改操作记录在 redo log 里,以便在数据库发生崩溃或出现其他问题的时候,能够通过 redo log 来重做。
redo log 是顺序写的,所以也是 WAL(write-ahead-log) 的一种,写性能较随机写高。

image.png

image.png

InnoDB 引擎本身提供了参数 innodb_flush_log_at_trx_commit 来控制写到磁盘的时机,里面有三个不同值。

  • 0:每秒刷新到磁盘,是从 redo log buffer 到磁盘。
  • 1:每次提交的时候刷新到磁盘上,也就是最安全的选项,InnoDB 的默认值。
  • 2:每次提交的时候刷新到 page cache 里,依赖于操作系统后续刷新到磁盘。

除非把 innodb_flush_log_at_trx_commit 设置成 1,否则其他两个都有丢失的风险

  • 0:你提交之后,InnoDB 还没把 redo log buffer 中的数据刷新到磁盘,就宕机了。
  • 2:你提交之后,InnoDB 把 redo log 刷新到了 page cache 里面,紧接着宕机了。
9.3 binlog

binlog 是用于存储 MySQL 中二进制日志(Binary Log)的操作日志文件,它是 MySQL Server 级别的日志,也就是说所有引擎都有。它记录了 MySQL 中数据库的增删改操作,因此 binlog 主要有两个用途,一是在数据库出现故障时恢复数据。二是用于主从同步,即便是 Canal 这一类的中间件本质上也是把自己伪装成一个从节点。

binlog与redolog的两阶段提交:

  • 第一阶段:redo log Prepare(准备)
  • 第二阶段:redo log Commit(提交)
image.png

binlog 也有刷新磁盘的问题,不过你可以通过 sync_binlog 参数来控制它。

  • 0:由操作系统决定,写入 page cache 就认为成功了。0 也是默认值,这个时候数据库的性能最好。
  • N:每 N 次提交就刷新到磁盘,N 越小性能越差。如果 N = 1,那么就是每次事务提交都把 binlog 刷新到磁盘。
9.4 事务执行过程

假如说原本 a = 3,现在要执行 UPDATE tab SET a = 5 WHERE id = 1

  • 事务开始,在执行 UPDATE 语句之前会先查找到目标行,加上锁,然后写入到 buffer pool 里面
  • 写 undo log
  • nnoDB 引擎在内存上更新值,实际上就是把 buffer pool 的值更新为目标值 5。
  • 写 redo log
  • 提交事务,根据 innodb_flush_log_at_trx_commit 决定是否刷新 redo log
  • 刷新 buffer pool 到磁盘
image.png

两种异常情况处理:

  • 如果在 redo log 已经刷新到磁盘,然后数据库宕机了,buffer pool 丢失了修改,那么在 MySQL 重启之后就会回放这个 redo log,从而纠正数据库里的数据。
  • 如果都没有提交,中途回滚,就可以利用 undo log 去修复 buffer pool 和磁盘上的数据。因为有时,buffer pool 脏页会在事务提交前刷新磁盘,所以 undo log 也可以用来修复磁盘数据。
9.5 ACID

事务的 ACID 特性是指原子性(Atomicity)、一致性 (Consistency)、隔离性(Isolation)还有持久性(Durability)。

  • 原子性:事务的所有操作都必须一次性执行,要么都成功,要么都失败。
  • 一致性:事务执行的结果必须满足数据约束条件,不会出现矛盾的结果。
  • 隔离性:事务在执行的时候可以隔离其他事务的干扰,也就是不同事务之间不会相互影响。
  • 持久性:事务执行的结果必须保证在数据库里永久保存,即使系统出现故障或者数据库被删除,事务的结果也不会丢失。
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 面试中会遇到的有关数据可以分为两类:一类是写SQL语句(测试岗会问)(一般考的都是查询SQL),一类是问问题。相应...
    ZYJ2016阅读 5,368评论 3 5
  • 第21章 创建和操纵表 21.1 创建表 MySQL不仅用于表数据操纵,而且还可以用来执行数据库和表的所有操作,包...
    _Cappuccino_阅读 14,005评论 0 0
  • ❑ 任意两行都不具有相同的主键值;❑ 每个行都必须具有一个主键值(主键列不允许NULL值)在使用多列作为主键时,上...
    手捧樱花v阅读 3,582评论 0 0
  • 2018年7月13日笔记 1.数据库的介绍 1.1 什么是数据库 数据库是按照数据结构来组织、存储和管理数据的仓库...
    潇洒坤阅读 4,432评论 0 1
  • 存储引擎InnoDB InnoDB 底层存储结构为B+树, B树的每个节点对应innodb的一个page,page...
    半山Light阅读 3,056评论 0 1