极客时间 MySQL 实战 45 讲——课后问题

Q1: Unknown column ‘k’ in ‘where clause’

在分析器中报的错。参考 MySQL 45 讲学习笔记 ☞ 01 基础架构

Q2: 一天一备 vs 一周一备

一天一备的优势是：“最长恢复时间”更短，系统的对应指标是 RTO（恢复目标时间），但需要更大的成本如储存空间。

Q3: 如何避免长事务

从应用开发端和数据库端考虑这个问题。

• 从应用开发端来看

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（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

Q4: 重建索引/主键索引的语句理解

现有表构造如下：

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

如果要重建索引 k or 重建主键 id 索引的语句如下：

alter table T drop index k;
alter table T add index(k);
alter table T drop primary key;
alter table T add primary key(id);

这里，重建索引 k 的做法是合理的，可以达到节省空间的目的。

但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。

更佳方案为用这个语句代替： alter table T engine=InnoDB 。

为什么要重建索引？

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

Q5: 索引冗余了吗？

有一个表结构是这样的：

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;

平时有如下查询语句：

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

表中，a 和 b 是业务需要做联合主键的，问 ca/cb 两个联合索引多余了吗？

这里的答案是，ca 这个联合索引多余了。

• 当存在主键 ab 的时候，相当于 order by a, b
• 当存在索引 c 的时候，相当于 order by c, (a, b)
• 当存在索引 ca 的时候，相当于 order by c, a, (b)
• 当存在索引 cb 的时候，相当于 order by c, b, (a)

索引均会记录主键部分，回表用。

可见，索引 ca 就是多余的了。

Q6: 当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？

列出备份过程的关键语句：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 确保在 RR 隔离级别 
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; -- 回滚，释放 t1 的 MDL
/* 时刻 4 */
/* other tables */

参考答案如下：

1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

Q7: 删除一个表里面的前 10000 行数据，哪个语句会更好？

1. 直接执行 delete from T limit 10000;
2. 在一个连接中循环执行 20 次 delete from T limit 500;
3. 在 20 个连接中同时执行 delete from T limit 500。

答案：

1. 单个语句占用时间长，锁时间也比较长；而且大事务还会导致主从延迟。
2. 第二种相对较好。
3. 会人为造成锁冲突。

Q8: 更新不了？

表构造如下：

mysql> 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);

执行语句如下：

为什么更新不了？？

正常情况下会返回 Affected rows: 4，则将 id=c 的记录把 c 改为 0。

复现情景1：

这样，session A 看到的就是我截图的效果了。

复现情景2：

这个 session B' 启动的事务比 A 要早，在事务版本的可见性规则里还有一个“活跃事务的判断”，在 session A 视图数组创建的瞬间，session B' 是活跃的，属于“版本未提交，不可见”这种情况。

分析：

1. session B' 的视图数组 [101]
2. session A' 的视图数组 [100]，因为 101 未提交，所以不是 [101, 100]
3. 然后 session B' 更新了数据，变为 2,3,4,5
4. session A' 当前读且去更新数据，但是无符合条件记录，更新 0 条
5. session A' 不可重复读，得到事务 A 创建的视图 [100]，不包含 [101] 的修改，结果为1,2,3,4

Q9: change buffer 一开始是写内存后，掉电重启，会不会导致 change buffer 丢失？

答：不会丢失。

虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。

merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

Q10: 影响预计扫描行数 rows 值的因素？

通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。

而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。

这是什么原因呢？（隔离级别为 RR ，Repeatable Read，可重复读）

答：

session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。

这样，索引 a 上的数据其实就有两份。

主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数还是 100000 左右。

因为，这里是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。

Q11: 如何设计登录名？