数据库入门之学习《MySQL实战45讲》第26讲-第30讲笔记

《MySQL实战45讲》第26讲 备库为什么会延迟好几个小时？

备库并行复制模型

其实，所有的多线程复制机制，都是要把只有一个线程的 sql_thread，拆成多个线程

coordinator 就是原来的 sql_thread,不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。

coordinator分发时遵守的规则

• 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
• 同一个事务不能被拆开，必须放到同一个 worker 中。

按表分发策略

按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。

每个事务在分发的时候，跟所有 worker 的冲突关系

1. 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
2. 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1个；
3. 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。

这个按表分发的方案，在多个表负载均匀的场景里应用效果很好。但是，如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。

按行分发策略

按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。

相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。

按行分发策略问题

1. 耗费内存。比如一个语句要删除 100 万行数据，这时候 hash 表就要记录 100 万个项。
2. 耗费 CPU。解析 binlog，然后计算 hash 值，对于大事务，这个成本还是很高的。

《MySQL实战45讲》第27讲 主库出问题了，从库怎么办？

一主多从基本结构

虚线箭头表示的是主备关系，也就是 A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。

GTID

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：

GTID=server_uuid:gno

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

基于 GTID 的主备切换

在 GTID 模式下，备库 B 要设置为新主库 A’的从库。

我们把现在这个时刻，实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。接下来，我们就看看现在的主备切换逻辑。

我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：

1. 实例 B 指定主库 A’，基于主备协议建立连接。
2. 实例 B 把 set_b 发给主库 A’。
3. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断A’本地是否包含了这个差集需要的所有 binlog 事务。（a. 如果不包含，表示 A’已经把实例 B 需要的 binlog给删掉了，直接返回错误； b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；）
4. 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

《MySQL实战45讲》第28讲 读写分离有哪些坑？

处理过期读的方案

过期读，即在从库上会读到系统的一个过期状态。

处理方案大致如下：

• 强制走主库方案；
• sleep 方案；
• 判断主备无延迟方案；
• 配合 semi-sync 方案；
• 等主库位点方案；
• 等 GTID 方案。

强制走主库方案

强制走主库方案其实就是，将查询请求做分类。通常情况下，我们可以将查询请求分为这么两类：

• 对于必须要拿到最新结果的请求，强制将其发到主库上。
• 对于可以读到旧数据的请求，才将其发到从库上。

sleep 方案

主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。

判断主备无延迟方案

要确保备库无延迟，通常有三种做法。

第一种确保主备无延迟的方法是，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。第二种方法，对比位点确保主备无延迟。第三种方法，对比 GTID 集合确保主备无延迟。

配合 semi-sync 方案

semi-sync 做了这样的设计：

1. 事务提交的时候，主库把 binlog 发给从库；
2. 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
3. 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

semi-sync+ 位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

1. 如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；
2. 但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。

其实，semi-sync 配合判断主备无延迟的方案，存在两个问题：

1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
2. 在持续延迟的情况下，可能出现过度等待的问题。

等主库位点方案

要理解等主库位点方案，我需要先和你介绍一条命令：

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：

1. 它是在从库执行的；
2. 参数 file 和 pos 指的是主库上的文件名和位置；
3. timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。

对于先执行 trx1，再执行一个查询请求的逻辑，要保证能够查到正确的数据，我们可以使用这个逻辑：

1. trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；
2. 选定一个从库执行查询语句；
3. 在从库上执行 select master_pos_wait(File, Position, 1)；
4. 如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
5. 否则，到主库执行查询语句。

GTID 方案

执行流程：

1. trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
2. 选定一个从库执行查询语句；
3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
4. 如果返回值是 0，则在这个从库执行查询语句；
5. 否则，到主库执行查询语句。

《MySQL实战45讲》第29讲 如何判断一个数据库是不是出问题了？

select 1 判断

select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题。

innodb_thread_concurrency

innodb_thread_concurrency控制 InnoDB 的并发线程上限。也就是说，一旦并发线程数达到这个值，InnoDB 在接收到新请求的时候，就会进入等待状态，直到有线程退出。

并发连接和并发查询

并发连接和并发查询，并不是同一个概念。你在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。

在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算的。

查表判断

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：

mysql> select * from mysql.health_check; 

使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况。

缺陷

空间满了以后，这种方法又会变得不好使。更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据的。

更新判断

既然要更新，就要放个有意义的字段，常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间。

mysql> update mysql.health_check set t_modified=now();

如果主库 A 和备库 B 都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。所以，现在看来 mysql.health_check 这个表就不能只有一行数据了。

为了让主备之间的更新不产生冲突，我们可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键。

内部统计

针对磁盘利用率这个问题，如果 MySQL 可以告诉我们，内部每一次 IO 请求的时间，那我们判断数据库是否出问题的方法就可靠得多了。

其实，MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name表里统计了每次 IO 请求的时间。（显示的是 redo log 操作的时间统计）

在 performance_schema 库的 file_summary_by_event_name 表里，binlog 对应的是 event_name = "wait/io/file/sql/binlog"这一行。各个字段的统计逻辑，与 redo log 的各个字段完全相同。

因为我们每一次操作数据库，performance_schema 都需要额外地统计这些信息，所以我们打开这个统计功能是有性能损耗的。

《MySQL实战45讲》第30讲 用动态的观点看加锁

怎么看死锁？

• 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
• 在发生死锁的时刻，InnoDB会选择回滚成本更小的语句来回滚。

学习地址：MySQL实战45讲