MySQL 相关 QA

[TOC]

## 1. 如何避免长事务对业务的影响？

这个问题，我们可以从应用开发端和数据库端来看。

**首先，从应用开发端来看：**

1. 确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 `general_log` 开起来，然后随便跑一个业务逻辑，通过 `general_log` 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成 1。

2. 确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。

3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）

**其次，从数据库端来看：**

1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；

2. Percona 的 pt-kill 这个工具不错，推荐使用；

3. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；

4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

## 2. 重建索引的方法是否合理？

**Q：**

```SQL
create table T(
 id int primary key,
 k int not null,
 name varchar(16),
 index (k))engine=InnoDB;
```

对于上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写：

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

如果你要重建主键索引，也可以这么写：

```SQL
alter table T drop primary key;
alter table T add primary key(id);
```

我的问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

**A：**

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

这道题目，我给你的“参考答案”是：重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： `ALTER TABLE T ENGINE=InnoDB`。

> **说明：**`ALTER TABLE T ENGINE=InnoDB`
上面回答中使用 `ALTER TABLE T ENGINE=InnoDB` 语句来重建索引，这条语句到底做了什么？
> `ALTER TABLE T ENGINE=InnoDB` 语句本身并不能直接刷新索引，但在执行这个语句的过程中，可能会间接地对索引产生一些影响。下面为你详细分析：
> **1. 不能直接刷新索引**
> 索引刷新通常指的是对索引数据结构进行重新构建，以确保索引与表中的数据保持同步，或者对索引进行优化以提高查询性能。而 `ALTER TABLE T ENGINE=InnoDB` 语句的主要目的是更改表的存储引擎，并非专门用于刷新索引。

> **2. 可能产生的间接影响**
当执行 `ALTER TABLE T ENGINE=InnoDB` 语句将表的存储引擎变更为 `InnoDB` 时，实际上是对表进行了重建操作。在这个重建过程中，会重新创建表的数据和索引。这意味着：
- **索引重建**：原有的索引会被删除并重新创建。这个过程类似于刷新索引，因为重新创建的索引会基于当前表中的数据，保证索引数据与表数据的一致性。
- **索引优化**：在重新创建索引时，存储引擎会按照 `InnoDB` 的索引结构和算法对索引进行优化，可能会提升索引的查询性能。

> **3. 示例说明**
```sql
-- 创建一个使用 MyISAM 存储引擎的表，并插入一些数据
CREATE TABLE T (
    id INT,
    name VARCHAR(50)
) ENGINE=MyISAM;
INSERT INTO T (id, name) VALUES (1, 'Alice');
INSERT INTO T (id, name) VALUES (2, 'Bob');
-- 为 id 列创建索引
CREATE INDEX idx_id ON T (id);
-- 假设由于某些原因，索引数据与表数据不一致
-- 将表 T 的存储引擎修改为 InnoDB
ALTER TABLE T ENGINE=InnoDB;
-- 此时索引已经被重建，与表数据保持一致
```

> 在这个示例中，首先创建了一个使用 `MyISAM` 存储引擎的表 `T`，并为 `id` 列创建了索引。假设由于某些原因，索引数据与表数据不一致。然后执行 `ALTER TABLE T ENGINE=InnoDB` 语句将表的存储引擎修改为 `InnoDB`，在这个过程中，索引会被重建，从而与表数据保持一致。

> 综上所述，虽然 `ALTER TABLE T ENGINE=InnoDB` 语句本身不是专门用于刷新索引的，但在更改存储引擎的过程中，会间接实现索引的重建和优化，从而达到类似于刷新索引的效果。不过，如果只是想刷新索引，建议使用专门的索引重建语句，如 `ALTER TABLE T FORCE` 或 `OPTIMIZE TABLE T`。

### 对重建索引做法的理解

#### 重建普通索引
```sql
alter table T drop index k;
alter table T add index(k);
```
- **原理**：这两条 SQL 语句的操作逻辑是先把名为 `k` 的索引从表 `T` 中删除，接着再基于 `k` 列重新创建一个新的索引。
- **作用**：这样做能够让索引结构重新构建，使得索引数据和表中的数据保持同步。在某些情形下，比如索引数据碎片化或者索引统计信息不准确时，重建索引可以提升查询性能。
- **缺点**：在执行 `DROP INDEX` 操作期间，依赖该索引的查询会变得很慢，因为此时没有索引可供使用。而且如果表的数据量较大，重建索引会耗费较多的时间和系统资源。

#### 重建主键索引
```sql
alter table T drop primary key;
alter table T add primary key(id);
```
- **原理**：这两条 SQL 语句先将表 `T` 的主键索引删除，然后重新把 `id` 列设置为主键，进而重建主键索引。
- **作用**：和重建普通索引类似，重建主键索引可以保证主键索引的数据和表数据一致，在一定程度上优化查询性能。
- **缺点**：在 InnoDB 里，主键索引和数据是存储在一起的，删除主键索引相当于删除了整个表的数据组织方式，会造成数据的大量移动和重新组织。这个过程不仅会消耗大量的时间和系统资源，还会在操作期间使表无法正常使用，影响业务的正常运行。

### 不合适的原因及更好的方法

#### 重建普通索引
- **不合适原因**：删除索引后到重新创建索引的这段时间内，依赖该索引的查询性能会大幅下降，并且在高并发场景下可能会影响业务的正常运行。
- **更好的方法**：可以使用 `ALTER TABLE T FORCE` 或者 `OPTIMIZE TABLE T` 语句。
    - `ALTER TABLE T FORCE`：会重建表和索引，它会重新组织表的数据和索引，去除索引中的碎片，提升索引的性能。
    - `OPTIMIZE TABLE T`：对于 InnoDB 表，它会先创建一个临时表，将原表的数据复制到临时表中，然后删除原表，最后将临时表重命名为原表。这个过程会重建索引，使索引更加紧凑和高效。

#### 重建主键索引
- **不合适原因**：InnoDB 的主键索引和数据是紧密关联的，删除主键索引会导致数据的大量移动和重新组织，操作成本极高，可能会使业务长时间不可用。
- **更好的方法**：在大多数情况下，不需要专门重建主键索引。因为 InnoDB 的主键索引会随着数据的插入、更新和删除自动维护。如果确实需要重建，可以考虑使用 `ALTER TABLE T FORCE` 或者 `OPTIMIZE TABLE T` 语句，它们会在重建表的过程中同时重建主键索引，相对来说对业务的影响较小。

以下是使用 `ALTER TABLE T FORCE` 和 `OPTIMIZE TABLE T` 的示例：
```sql
-- 重建表和索引，适用于普通索引和主键索引
ALTER TABLE T FORCE;

-- 优化表，适用于普通索引和主键索引
OPTIMIZE TABLE T;
```

这两种方法在重建索引时对业务的影响相对较小，并且能达到类似的索引优化效果。

## 3. 主键索引使用多个字段问题

**Q：**

DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

```SQL
CREATE TABLE `geek` (
 `a` int(11) NOT NULL,
 `b` int(11) NOT NULL,
 `c` int(11) NOT NULL,
 `d` int(11) NOT NULL,
 PRIMARY KEY (`a`,`b`),
 KEY `c` (`c`),
 KEY `ca` (`c`,`a`),
 KEY `cb` (`c`,`b`)
 ) ENGINE=InnoDB;
```

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？

同事告诉他，是因为他们的业务里面有这样的两种语句：

```SQL
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
```

我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？

**A：**

表记录

|a |b |c |d |
|--|--|--|--|
|1| 2| 3| d |
|1| 3| 2| d |
|1| 4| 3| d |
|2| 1| 3| d |
|2| 2| 2| d |
|2| 3| 4| d |

主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。

索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键

|c|a|主键部分b（注意，这里不是 ab，而是只有 b）|
|--|--|--|
|2| 1| 3 |
|2| 2| 2 |
|3| 1| 2 |
|3| 1| 4 |
|3| 2| 1 |
|4| 2| 3 |

> 这个跟索引 c 的数据是一模一样的。

索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键

|c|b|主键部分a （同上）|
|--|--|--|
|2| 2| 2 |
|2| 3| 1 |
|3| 1| 2 |
|3| 2| 1 |
|3| 4| 1 |
|4| 3| 2 |

所以，结论是 ca 可以去掉（与c相同），cb 需要保留。

## 3. 备份时对表做出修改的影响

**Q：**

备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL（Data Definition Language，数据定义语言），比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

**A：**

假设这个 DDL 是针对表 t1 的， 这里我把备份过程中几个关键的语句列出来：

```SQL
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
```

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);

启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；

设置一个保存点，这个很重要（Q3）；

show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。当然这部分属于“超纲”，上文正文里面都没提到。

DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。

参考答案如下：

1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。

2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition haschanged, please retry transaction，现象：mysqldump 终止；

3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog被阻塞，现象：主从延迟，直到 Q6 执行完成。

4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是DDL 前的表结构。

## 4. 删除带来的并发问题

**Q：**

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;
第二种，在一个连接中循环执行 20 次 delete from T limit 500;
第三种，在 20 个连接中同时执行 delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

选择哪种方法来删除表中的前 10000 行数据，需要综合考虑数据库性能、锁机制、事务处理和数据一致性等多方面因素，下面为你详细分析这三种方法：

**A：**

### 方法一：

`delete from T limit 10000;`

- **优点**：
    - **操作简单**：只需执行一条 SQL 语句，代码实现简单，对于开发者来说易于编写和维护。
    - **锁定范围集中**：在删除过程中，数据库会对涉及的行进行锁定。由于是一次性删除 10000 行，锁定范围相对集中，减少了多次锁定和解锁带来的开销。
- **缺点**：
    - **事务回滚代价大**：如果在删除过程中出现问题需要回滚事务，由于涉及 10000 行数据，回滚操作会比较耗时，可能会对数据库性能产生较大影响。
    - **锁持有时间长**：一次性删除大量数据，数据库需要长时间持有锁，这可能会阻塞其他对该表的读写操作，影响并发性能。

### 方法二：

在一个连接中循环执行 20 次 `delete from T limit 500;`

- **优点**：
    - **事务回滚代价小**：每次只删除 500 行数据，若出现问题需要回滚事务，回滚操作相对较快，对数据库性能的影响较小。
    - **减少锁持有时间**：每次删除操作的时间较短，锁持有时间也相应减少，从而降低了对其他事务的阻塞影响，提高了并发性能。
- **缺点**：
    - **性能开销**：由于需要循环执行 20 次 SQL 语句，每次执行都有一定的开销，如网络传输、SQL 解析等，可能会导致整体性能不如一次性删除。

### 方法三：

在 20 个连接中同时执行 `delete from T limit 500;`

- **优点**：
    - **并行处理**：多个连接同时执行删除操作，可以充分利用数据库的并行处理能力，理论上能更快地完成删除任务。
- **缺点**：
    - **锁竞争激烈**：多个连接同时对表进行操作，会导致锁竞争激烈，可能会出现死锁的情况，影响数据库的稳定性。
    - **资源消耗大**：同时开启 20 个连接会消耗较多的数据库资源，如内存、CPU 等，可能会对数据库的整体性能产生负面影响。

### 选择建议

- **数据量较小且对并发影响要求不高**：如果表中的数据量较小，且对其他事务的并发影响要求不高，可以选择方法一，因为它操作简单，能快速完成删除任务。
- **数据量较大且对并发性能有一定要求**：推荐使用方法二，它既能减少事务回滚的代价，又能降低对其他事务的阻塞影响，是一种比较平衡的选择。
- **对删除速度有极高要求且数据库资源充足**：在确保数据库有足够的资源支持，并且能够有效处理锁竞争的情况下，可以考虑方法三，但需要谨慎使用，避免出现死锁等问题。

<mark>综上所述，一般情况下，方法二是比较合适的选择。</mark>

## 5. 事务可重复读隔离问题

我用下面的表结构和初始化语句作为试验环境，事务隔离级别是可重复读。现在，我要把所有“字段 c 和 id 值相等的行”的 c 值清零，但是却发现了一个“诡异”的、改不掉的情况。请你构造出这种情况，并说明其原理。

```SQL
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);
```

```bash
mysql> begin;
Query OK,0 rows affected(0.00 sec)

mysql> select * from t;
+-----+------+
|  id |   c  |
+-----+------+
|  1  |   1  |
|  2  |   2  |
|  3  |   3  |
|  4  |   4  |
4 rows in set(0.00 sec)

mysql> update t set c=0 where id=c;
Query OK,0 rows affected(0.00 sec)
Rows matched:0 changed:0 Warnings:0

mysql> select * from t;
+-----+------+
|  id |   c  |
+-----+------+
|  1  |   1  |
|  2  |   2  |
|  3  |   3  |
|  4  |   4  |
4 rows in set(0.00 sec)
```

复现出来以后，请你再思考一下，在实际的业务开发中有没有可能碰到这种情况？你的应用代码会不会掉进这个“坑”里，你又是怎么解决的呢？