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




![image.png](/static/img/baa4c5f5a90968dc0dd13df165b17e5b.image.webp)

问题：图中三个事务执行完后 k 的值是什么？

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

MySQL 笔记 08

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



MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有引擎都支持行锁，MyISAM 就不支持行锁，MyISAM 做并发控制的时候只能用表锁实现，这意味着同一张表在某个时刻只能有一个更新在执行。

## 两阶段锁

![image.png](/static/img/13b03f61d59c6622867008be123d18e2.image.webp)

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

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

比如有这样一个例子：

一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。这个业务需要涉及到以下操作：
1. 从顾客 A 账户余额中扣除电影票价；
2. 给影院 B 的账户余额增加这张电影票价；
3. 记录一条交易日志。

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

如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了

所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，这样执行完 2 就 commit 提交事务，然后就释放行锁了。那么影院账户余额这一行的锁时间就最少。


## 死锁和死锁检测

![](https://caibh.tech/static/img/33ab4471054dec8a91079ece06d687f1.image.webp)

上图是死锁的例子。事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。

对死锁有两种处理策略：
- 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 `innodb_lock_wait_timeout` 来设置，默认值是 50s
- 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 `innodb_deadlock_detect` 设置为 `on`，表示开启这个逻辑。默认值是 on

如果有很多线程（比如 1000）需要同时更新同一行数据，这时候死锁检测会消耗大量 CPU 资源。解决的思路是**控制并发度**，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低。

MySQL 笔记 07

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



根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

## 全局锁

### 命令：flush tables with read lock

可以通过命令 `flush tables with read lock(FTWRL)` 开启全局锁，开启后整个库处于只读状态，开启后其它线程的以下操作会被阻塞：
- 数据的增删改
- DDL 语句，包括：建表、修改表结构
- 更新类事务的提交语句

**全局锁的使用场景是做全库逻辑备份**，即把整个库每个表 select 出来保存为文本。

如果做备份的时候，会引起多个表之间数据不一致，备份得到的库不是一个逻辑时间点。

### 参数：mysqldump --single-transaction

官方自带的 `mysqldump` 工具可以使用参数 `--single-transaction`，导出数据的时候就会开启事务，确保导出的数据是一致的，而且由于有 MVCC 的支持，导出数据的时候其它线程可以执行更新操作。


:::note{title="为什么还要 FTWRL"}
使用 mysqldump 的 single-transaction 参数的前提是存储引擎要支持事务，而 MyISAM 引擎是不支持事务的，这时候只能用 `mysqldump --single-transaction` 的方法来确保导出的数据是一致的。
:::

### 参数：set global readonly = true

readonly 的方法也能使整个库进入只读状态，但是会有问题：
1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大
2. 执行 FTWRL 命令后如果客户端断开，MySQL 会自动释放全局锁；而 readonly 设置后，客户端断开后整个数据还是会保持只读状态，风险高


## 表级锁

MySQL 中表级锁有两种：
- 表锁
- 元数据锁（meta data lock, MDL）

### 表锁：lock tables ... read/write

表锁可以用 unlock tables 释放，也可以在客户端断开连接时候释放

使用举例：
```sql
lock tables t1 read, t2 write;
```

上面例子的作用是：
- 其它线程写 t1 会被阻塞，本线程只能读 t1
- 其它线程读写 t2 会被阻塞，本线程能读写 t2，不能写 t1

### 元数据锁

- MDL 锁不需要显式使用，在访问一个表的时候会自动加上。
- MDL 锁是 MySQL 5.5 引入的
- 当对一个表做增删改查的时候，自动加 MDL 读锁
- 当修改表结构时，自动加 MDL 写锁
- 读锁之间不互斥
- 读写锁之间、写锁之间互斥

![image.png](/static/img/432ecc2a378d718e2b2bbb26a2b5a1c6.image.webp)

**如上图，修改线上数据库的表结构是有风险的。**

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

1. 解决长事务，事务不提交就会一直占着 MDL 锁。可以查询 `information_schema` 库的 `innodb_trx` 表看是否有长事务在执行，如果要修改结构的表正在有事务执行，要么等待完成，要么 kill 掉，再执行修改表结构的操作。

2. 可以在 `alter table` 里设置等待时间
```sql
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 
```

MySQL 笔记 06

05 | 深入浅出索引（下）



## 覆盖索引

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

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

![](https://caibh.tech/static/img/da4947b329ba7b9cf80d015a3640e355.image.webp)

- 如果查询语句是 `select * from T where k between 3 and 5`，是需要先在 k 字段对应的普通索引树上根据 k 的值找到对应记录的 ID，然后再去聚簇索引树上找 ID 对应的行数据。
- 但如果查询语句是 `select ID from T where k between 3 and 5`，索引 k 已经有 ID 信息，不需要做回表查询。此时由于索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引
- 使用覆盖索引可以提升查询性能

## 最左前缀原则

联合索引的例子图：
![image.png](/static/img/d086dd442f6f80ca56605b3ee55e743c.image.webp)

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

- 联合索引的最左 N 个字段
- 字符串索引的最左 N 个字符

## 索引下推

如果有一条 sql 要求查询 “名字第一个字是张，而且年龄是 10 岁的所有男孩”
```sql
select * from tuser 
where 
    name like '张%' 
    and age=10 and ismale = 1;
```

- 在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
- 而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

无索引下推的执行流程：
![image.png](/static/img/a9735fae9dbe05a143a68e1e688a220d.image.webp)

有索引下推的执行流程：
![image.png](/static/img/57c443e0ac5188086177740bffe7b32e.image.webp)

MySQL 笔记 05

04 | 深入浅出索引（上）



## 索引的作用

索引的作用是提高查询效率

## 索引模型探讨

以下的数据结构可以用于实现索引

### 哈希表

- 哈希表只适用于等值查询的场景

### 有序数组

- 等值查询和范围查询的性能很好
- 但是更新数据很麻烦，比如往数组中间位置插入数据，插入位置往后的数据都要往后移动一个位置
- 所以有序数组只适用于静态存储引擎

### 二叉树

- 父节点左子树所有节点的值小于父节点的值，右子树所有节点的值大于父节点的值
- 需要保持二叉树为平衡二叉树，才能保证查询的时间复杂度O(log(N))
- 为了减少读取磁盘次数，一般采用 N 叉树

## InnoDB 的索引模型

InnoDB 用的是 B+ 树。

![image.png](/static/img/55b641e2095caf6b629f1dae9b69eaa7.image.webp)


基于主键索引和普通索引的查询的区别是：
- 如果语句是 `select * from T where ID = 500`，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
- 如果语句是 `select * from T where k = 5`，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为**回表**。


## 索引维护

**性能方面的问题：**

- 如果是追加插入就很简单，但如果是在中间位置插入，就很麻烦，需要挪动后面的数据

![image.png](/static/img/ea5c8b3ac97984f53307d20eb3302337.image.webp)

- 另一个问题是，如果插入时数据页满了，会引起页分裂问题
- 与页分裂对应，还有页合并
- 用自增 ID 是追加的方式，不需要挪动数据，也不会引起页分裂

**存储空间方面的问题：**
- 用自增 ID 有利于非主键索引（二级索引）节省空间，因为非主键索引的叶子节点存储的就是每条记录的主键