《MySQL实战45讲》(20-35讲)学习总结

2021-08-15 18:49:53

注明:极客时间《MySQL实战45讲》个人学习3

第二十一讲：为什么我只改一行的语句，锁这么多？
- 加锁规则
- 案例一：等值查询间隙锁
- 案例二：非唯一索引等值锁
- 案例三：主键索引范围锁
- 案例四：非唯一索引范围锁
- 案例五：唯一索引范围锁 bug
- 案例六：非唯一索引上存在"等值"的例子
- 案例七：limit 语句加锁
- 案例八：一个死锁的例子
- 总结
- 问题
第二十二讲：MySQL有哪些“饮鸩止渴”提高性能的方法？
- 短连接风暴
- - 第一种方法：先处理掉那些占着连接但是不工作的线程。
  - 第二种方法：减少连接过程的消耗。
- 慢查询性能问题
第二十三讲：MySQL是怎么保证数据不丢的？
- binlog 的写入机制
- redo log 的写入机制
- WAL的好处
- 问题
第24讲：MySQL是怎么保证主备一致的？
- MySQL 主备的基本原理
- binlog 的三种格式对比
- 为什么会有 mixed 格式的 binlog？
- 循环复制问题
- 问题
第25讲：MySQL是怎么保证高可用的？
- 主备延迟
- 主备延迟的来源
- 可靠性优先策略
- 可用性优先策略
- - 有没有哪种情况数据的可用性优先级更高呢？
第26讲：备库为什么会延迟好几个小时？
- MySQL 5.5 版本的并行复制策略
- - 按表分发策略
  - 按行分发策略
- MySQL 5.6 版本的并行复制策略
- MariaDB 的并行复制策略
- MySQL 5.7 的并行复制策略
- MySQL 5.7.22 的并行复制策略
- - 优势
- 问题
第27讲: 主库出问题了，从库怎么办？
- 基于位点的主备切换
- GTID
- 基于 GTID 的主备切换
- GTID 和在线 DDL
- 问题
第28讲：读写分离有哪些坑？
- 强制走主库方案
- Sleep 方案
- 判断主备无延迟方案
- 配合 semi-sync
- 等主库位点方案
- GTID 方案
- 问题
第29讲：如何判断一个数据库是不是出问题了？
- select 1 判断
- 查表判断
- 更新判断
- 内部统计
第30讲：答疑文章（二）：用动态的观点看加锁
- 不等号条件里的等值查询
- 等值查询的过程
- 怎么看死锁？
- 怎么看锁等待？
- update 的例子
- 问题
第31讲：误删数据后除了跑路，还能怎么办？
- 误删行
- 不建议在主库上执行操作
- 误删库 / 表
- 延迟复制备库
- - 搭建延迟复制备库
- 预防误删库 / 表的方法
第32讲：为什么还有kill不掉的语句？
- 收到 kill 以后，线程做什么？
- kill不掉的例子
- 那么ctrl+c是不是可以终止线程？
- 另外两个关于客户端的误解
- 问题
第33讲.我查这么多数据，会不会把数据库内存打爆？
- 全表扫描对 server 层的影响
- 全表扫描对 InnoDB 的影响
第34讲：到底可不可以使用join？
- Index Nested-Loop Join
- Simple Nested-Loop Join
- Block Nested-Loop Join
第35讲：join语句如何优化？
- Muti-Range Read优化
- Batched Key Access
- BNL算法的性能
- BNL 转 BKA

第二十一讲：为什么我只改一行的语句，锁这么多？

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

加锁规则

原则1：加锁的基本单位是next-key lock
原则2：访问到的对象都要加锁
优化1：索引等值查询，唯一索引加锁，next-key lock退化成行锁
优化2：索引等值查询，向右遍历而且最后最后一个不符合规则，next-key lock退化成间隙锁（不代表一开始加上的next-key lock是可以被优化的，而是要往右移动到第一个不符合规则的才是能够进行优化的）
bug：唯一索引范围查询会访问到不满足的第一个值为止

案例一：等值查询间隙锁

根据原则1，现在就要给id=7加上next-key lock，因为id=7这个记录并不存在。加锁的范围是(5,10]
然后就是优化2，现在遍历5-10，发现10很明显就是不符合规则，因为id=10记录是存在的而且id不等于7。所以退化为间隙锁(5,10)。
这也就是为什么B被阻塞，但是C可以修改的原因了。（测试成功）

案例二：非唯一索引等值锁

分析

根据原则1给(0,5]加上next-key lock
由于c是普通索引根据原则2需要继续向右遍历找到不符合规则的记录，刚好找到id=10的记录，并且c不符合规则等于5，那么就要给(5,10]加上next-key lock。因为要给访问到的对象加上锁。
还需要配合优化2使用，因为id=10的位置不符合规则所以(5-10)退化成了间隙锁
由于只有访问到的对象才会加锁，这里访问的只是普通索引的记录，所以主键索引不会受到任何影响。所以下面是成立的。

案例三：主键索引范围锁

对于这里的第一条语句是从(5-10]退化成行锁，只是锁住id=10这条记录
但是对于第二条就不一样的，因为是唯一索引而且还是范围查找，那么还需要继续去查找后面第一个符合规则的记录。也就是给(10-15]加上next-key lock，而且由于不是等值查询所以不会退化。
所以B和C都会被阻塞。但是8这条并不会被阻塞。

 select * from t where id=10 for update;
 select * from t where id>=10 and id<11 for update;

案例四：非唯一索引范围锁

首先是找到c=10的位置，并且加上(5-10]的next-key lock。
然后就是要继续往后面找到不符合范围的记录刚好就是15,所以还要加上一个(10,15]的next-key lock。由于不是一个范围查询所以不能进行优化。

案例五：唯一索引范围锁 bug

这个案例正常来说是直接锁上(10,15]但是现在多了一个bug，就是需要拓展到(15,20].

案例六：非唯一索引上存在"等值"的例子

insert into t values(30,10,30);

假设现在有两个c=10是相等的，那么他们的一个间隙是什么？他们的间隙就是id之间的一个间隙。10和30

对于下面的过程其实就是delete和select的规则是一样的，先找到第一个c的位置，然后给c=5，id=5到c=10、id=10加上一个next-key lock。接着就是向右遍历到c=15 、id=15。也就是覆盖范围和一个的时候相似。

案例七：limit 语句加锁

对于下面的场景加上了limit 2，虽然结果相同但是加锁的范围不同，加锁会直接结束，找到第二个记录的时候。
所以删除的时候限制删除的条目，能够缩短锁的范围。那样插入各方面或者update的并发度能够得到更好的提升。

案例八：一个死锁的例子

过程是A先给（5,10]上next-key lock然后给在(10,15)上间隙锁
这个时候B要进行更新，所以要给(5,10]上next-key lock
但是A要再c=8的位置插入数据但是B的间隙锁让A无法插入，而A的间隙锁也让B无法修改。
B的上锁过程是间隙锁(5,10)然后再给c=10上一个行锁。

总结

对于这个地方需要思考清除规则。

第一个规则是只要是访问加锁，那么基本上都是间隙锁+行锁
第二个规则是只要访问到了的地方就要加锁
第三个规则是唯一索引如果是等值比较，而且符合规则就可以改成行锁。
第四个规则，制造了第一个间隙锁+行锁之后仍然需要往右边遍历，找到第一个不符合的为止才能够结束。而且还能优化为间隙锁
最后一个就是bug，对于唯一索引来说就是已经找到了第一个间隙，但是仍然要去找第一个不符合规则的，并且加上间隙锁+行锁。

问题

对于这里为什么会锁上(5,10]的原因？

第一个要解释的地方就是为什么加上锁之后仍然需要扫描？原因就是你不知道右边是不是还有和当前的c相同的值，所以必须要找到一个不同的值的记录并且进行锁定。如果没有锁的问题就是可能会通过修改，把c相同的一些记录修改了，或者是插入了一些新的记录导致出现的幻读问题。
原因其实就是因为他是一个desc，所以首先锁住的是next-key lock(15-20],然后加上间隙锁(20,25)
然后向左边进行遍历，扫描到10的时候停下。所以给10也加上了next-key lock范围刚好就是(5,10]。向左边遍历的原因就是因为desc导致的遍历方向不同，开始的位置不同。如果没有desc，那么正常的加锁状态应该是，(10-15]加next锁，然后往右边遍历，发现了20不符合，然后给加上(15-20)的间隙锁（等值比较）,接着就是给20加上一个(15-20]的next锁，然后往右遍历直到25。但是加上desc之后关系相反，现在从20开始进行加锁，先加上next-key lock(15-20]，然后往右遍历(20-25)间隙锁，这个时候仍然需要往左边去遍历范围，然后遍历到15加上一个next-key lock(10,15],接着就是往右边扫描发现已经有间隙锁，由于这个时候15还是符合的，所以需要接着需要往左边的记录进行遍历，扫描到10终于不符合了，所以在这个时候给10加上一个next-key lock刚好锁住(5,10]。这里是个人理解，可能会不对。但是我觉得是按照规则进行的一个扫描。
而且select * 说明需要回表访问，所以主键索引这里也有锁，不过被优化成了行锁。

第二十二讲：MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

短连接的问题就是如果并发量起来就会导致服务器大部分时间在做登录验证，权限查询等。所以需要限制每次的连接数。

第一种方法：先处理掉那些占着连接但是不工作的线程。

优先断开事务外空闲连接，避免断开事务中的连接不然就只能回滚事务了
要是不行才断开事务内的连接太久的连接。

第二种方法：减少连接过程的消耗。

可以选择减少权限授予这部分，但是风险太大

慢查询性能问题

索引问题
sql语句问题
mysql选错了索引

导致慢查询的第一种可能是，索引没有设计好。

这种只能通过紧急创建索引的方式建立索引

备库B执行set sql_log_bin=off，不写binlog，然后alter table添加索引
执行主备切换
主库是B，备库是A。A执行set sql_log_bin=off，然后alter table来添加索引。

导致慢查询的第二种可能是，语句没写好。

比如语句的索引列使用了函数
没有使用到索引等。

选错了索引

统计错误

QPS 突增问题

第二十三讲：MySQL是怎么保证数据不丢的？

binlog 的写入机制

写到binlog cache然后事务提交之后再写入到binlog文件中
一个事务的binlog不能被拆开，每个事务的binlog都要保证一次性写入。
所以每个事务的线程都对应着一份binlog cache，如果binlog cache不够用那么就要使用到临时文件先存入到磁盘。这样是为了保证每个事务的binlog的完整性。
而且整个过程是binlog cache写到文件系统缓存，然后才是同步到磁盘。
关于写的时机
- sync_binlog=0只write到文件系统缓存但是不同步到磁盘
- sync_binlog=1每次提交事务都会同步到磁盘
- sync_binlog=2每次提交事务都会先写到文件缓存，然后写多几次才会同步到磁盘。
对于0和2来说主机宕机都会导致binlog的几次写的数据丢失。

redo log 的写入机制

对于redo log cache来说不需要每次生成都同步到磁盘，而且如果mysql重启那么redo log cache就会丢失，问题是事务没有提交，所以没有关系。
三种redo log状态
- 在redo log cache中
- 在page cache中
- 持久化到磁盘中
redo log的写入策略，innodb_flush_log_at_trx_commit 参数控制。
- 0每次事务提交都只是写到redo log buffer
- 1事务提交直接持久化到磁盘
- 2每次事务提交都只是把redo log同步到page cache中
有一个后台线程会把redo log buffer的数据同步到磁盘。也就是未提交的redo log也可能会比存储到磁盘。

还有两种场景会把未提交的redo log也存储到磁盘。

redo log buffer的大小等于这个参数innodb_log_buffer_size的一半，后台线程会主动写盘。这里只是写到page cache中。
并行事务提交会顺带把redo log buffer持久化到磁盘。

对于redo log来说在prepare的时候就需要进行一次持久化，如果发生崩溃，那么恢复就需要依靠redo log+binlog进行恢复。

如果是mysql的双1，也就是sync_binlog和innodb_flush_log_at_trx_commit 都是1，那么就是每次事务提交都需要刷两次盘。但是为什么有时候mysql的TPS是每秒2w，相当于是刷4w磁盘，但是磁盘io只有2w，那么如何实现？

他依靠的是组提交，可以看看上面的图，lsn日志逻辑序列号，主要是对应redo log的写入点。
- trx1是组长
- trx1写的时候已经有了3个事务，所以会连带这些事务的redo log 写盘，并且lsn已经写到了160。
- 现在所有lsn<=160都被写盘了
- trx2和3都可以直接返回了。
并发场景，组员多，fync晚调用，积累更多写盘，那么节约IOPS的效果就越好。

下面的意思是redo log和binlog都先写盘，然后等待他们都写盘之后再进行一次fync同步到磁盘。大大节约了IO的成本。

WAL的好处

redo log和binlog都是顺序写，顺序写速度比随机写快
组提交机制能够更好降低磁盘的IOPS

问题

如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

通过组提交，减少binlog写盘次数。
设置sync_binlog>1意思就是写盘多次，fync一次，但是如果主机挂了会丢失数据。
将 innodb_flush_log_at_trx_commit=2也是主机挂的时候导致数据全部丢失。

总体：为了保证mysql的redo log和binlog的完整，主要还是通过各种策略。比如双1是比较稳定的策略，事务提交就写盘，fync。但是这样导致的问题就是并发度不够高，如果在系统相对稳定可以写到page cache多次，然后再集中fync到磁盘。并且可以通过组提交和lsn的减少IOPS，减少的原因就是减少了写磁盘的频率，把redo log和binlog集中起来刷盘。

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

原因就是binlog需要保证完整写完一次事务，但是对于redo log并不是。而且redo log buffer的连续能够让并行事务同时刷新到磁盘。

事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？

不会因为binlog也还在binlog buffer所以没有推送到备库。因为binlog在事务提交才会进行写盘和刷新。

第24讲：MySQL是怎么保证主备一致的？

MySQL 主备的基本原理

对备库需要设置readonly
防止切换时的双写问题。

readonly对超级用户权限无效，仍然可以写，所以这就可以进行主从配置的同步。

主库接收到客户端的请求，更新并且写binlog
- 备库B能够通过change master得知如何请求A主库的binlog
- 备库B执行start slave，启动一个io线程和一个sql线程，io线程和主库进行连接
- 主库A给B发送binlog
- B读binlog写到本地文件，这个就是文件就是relay log
- sql线程读取relay 日志并且解析命令和执行。

binlog 的三种格式对比

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `a` (`a`),
 KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;
insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

binlog如果是statement的时候就是直接保存原文的。

mysql> show binlog events in 'master.000001';

delete from t /comment/ where a>=4 and t_modified<=‘2018-11-10’ limit 1;

delete语句会造成一个warnings，因为delete+limit可能会造成主从设备不一致的问题

如果使用索引a，那么先找到a=4的情况
如果是索引t.modified那么第一个找到就是2018-11-10的情况

这个是statement格式的问题。这样就会导致主库可能使用的是a索引，但是从库使用的是t_modified索引。那么删除的顺序就有可能不同。

所以现在把binlog格式改成row格式
- 发现这里的原文语句变成了两个活动语句
- table_map:说明要操作test.t表
- delete_rows:说明定义删除行为。

下面展示的是上面图的具体信息

server id说明事务在server id=1的库上面执行
table map event对应了number 226，每个表都有对应的table_map event对应不同表
然后就是语句的具体细节@1=4，@2=4这种参数。对应id=4，a=4完全具体到了一条语句上
binlog_row_image默认null，但是在这里设置为MINIMAL，为了记录必要的删除信息。那么就不会像statement的语句造成使用不同索引，删除顺序不同导致主从不一致。

为什么会有 mixed 格式的 binlog？

row很占用空间，写binlog耗费IO资源
statement容易造成主从不一致，但是占用空间小
mixed可以根据语句是否造成主从不一致灵活调控两种格式的应用。

为什么大部分时候mysql场景要求使用row格式？

恢复数据很方便

row记录了整行数据包括各种参数，如果要恢复那么直接重新插入即可
insert记录了整行数据，如果插入错了，可以直接通过binlog删除
update会记录前和后的数据，只要查一下就可以恢复过来。

insert into t values(10,10, now());这条语句使用的是statement还是row？

答案是使用statement，可能你会认为如果使用statement那么从库调用now是不是就和主库不一样，但是这里有个好处就是能够直接SET TIMESTAMP=1546103491，先设置好这个timestamp那么从库也就能够使用它了。
但是有的就不能这么做，你会发现直接把statement拿出来恢复整个数据库，可能有些函数是依赖上下文等，比如时间，随机数，这些都会造成最后恢复的不正确。

循环复制问题

这个问题就是双M结构的问题，因为两个库互为主从，导致的问题就是可能会有重复的binlog执行。比如A更新一条记录发送binlog给B，但是B更新之后也写binlog发送给A。那么要如何避免？

两个库的server id不同
备库获取binlog重放，生成server id和原binlog相同的。
收到主库发来的binlog检查server id是否和自己相同，相同就不执行了。

问题

什么情况会造成循环复制

执行事务之后修改了server id，导致备库认为这是它的binlog然后发送给主库，主库就发现server id不同又执行，又把server id改为备库的。

第25讲：MySQL是怎么保证高可用的？

主备延迟

同步延迟，和数据同步相关的三个时间点

主库A完成事务写入binlog是T1
传给备库B，备库B接收完这个binlog的时刻是T2
备库B执行完成事务，这个时刻是T3

主备延迟指的就是T3-T1

seconds_behind_master也就是备库的一个status用来记录主库和自己的一个延迟，计算方式

每个事务的binlog都有一个时间，记录主库写入时间
备库取出这个时间和当前时间计算差值就是seconds_behind_master
如果主库设置系统时间不一致，备库能够通过SELECT UNIX_TIMESTAMP() 函数进行对比，并且减去差值保持时间的准确

主备延迟最大的问题就是备库消费relay日志比主库写入binlog日志要慢。对于T2和T1的速度是非常快的，关键就是T3.

主备延迟的来源

1.有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

把备库放到一台机器，但是主库放到多台机器，问题就是多个备库可能会产生争夺主机资源来读取主库送过来的binlog。但是现在基本使用对称部署，也就是主库和备库都是使用性能差不多的机器。

2.但是，做了对称部署以后，还可能会有延迟。这是为什么呢？

从库的查询压力大

可以使用一主多从进行处理分担读压力
binlog输出到外部系统，让别的系统分担读压力

3.采用了一主多从，保证备库的压力不会超过主库，还有什么情况可能导致主备延迟吗？

大事务场景

如果一个事务执行时间很长就会导致主备延迟，假如要删除百万条数据，延时就会影响整个业务，所以通常是晚上处理，而且要分批删除。
大表 DDL

4.如果主库上也不做大事务了，还有什么原因会导致主备延迟吗？

备库并行复制能力不行。

可靠性优先策略

双M结构从状态1到状态2的转换

判断备库B的seconds_behind_master如果小于某个值继续，不然就要重试这一步
把主库A改为只读,readonly=true
判断B的seconds_behind_master变为0
B改为可读写，readonly=false
业务请求切换到B

SBM就是seconds_behinds_master

这里从步骤2开始A和B都是只读导致系统不可用，这就是为什么需要判断SBM足够小才进行切换，而且步骤3需要一点时间，可能会比较慢。
但是保证了主备数据的一致性。

可用性优先策略

把上面4,5放到最前面。

那么就没有不可用的时间了。
但是牺牲的就是主备数据的一致性。

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

insert into t(c) values(4);
insert into t(c) values(5);

分析切换流程

A在步骤2完成插入4之后开始切换。
步骤3由于主备延迟是5s，所以没来的及完成relay log转存insert 4（因为是一个大事务，除了insert4还有前面操作别的表）的动作，就先执行insert 5了。
步骤4中B插入了（4,5），并且把binlog发送给A。
步骤5的时候B才开始执行insert 4的中转日志，所以插入了(5,4).这个插入语句通过binlog交给了A，A也插入5.
所以可以发现A和B之所以出现数据不一致，就是因为插入的语句顺序改变了，改变的原因就是因为主备延迟，由于直接修改B为主库，导致上一次的relay log没有写完，就要插入一条新的语句，这个时候A和B插入语句的顺序就反了。

但是改一下格式binlog_format=row
使用row的时候可以记录行的具体信息，所以数据不一致问题可以更好地被发现。
所以还是建议使用可靠性优先策略。通过两个只读一段时间，防止主备不一致。之所以要等待SMB为0原因是如果不等，那么就可能导致B接收业务，但是之前的事务没有解决导致上面的插入错序问题。

有没有哪种情况数据的可用性优先级更高呢？

A宕机，只能是B备库切换为主库。虽然延迟很大，但是仍然需要先切换，保持可用，然后让中转日志慢慢恢复数据，这段时间造成数据不一致，而且客户端发现B是暂时丢失数据的状态。

第26讲：备库为什么会延迟好几个小时？

主库写入binlog能力和备库的并行复制能力很重要，如果他们速度不匹配就会导致延迟能够一直累加。
对于客户端写入到主库的并行度是远远大于sql_thread写入到备库的速度，这也就是为什么主库和备库之间存在这么大的延迟问题。那么怎么解决？

下面的coordinator就是之前的sql_thread现在不会写了，而是读取所有的中转日志和分发任务到各个线程进行处理。分配给work，slave_parallel_workers 决定worker个数，通常分配8-16个，剩下还是要用于查询的。
是否能用轮询方式分配事务？不行，因为两个事务之间可能会有顺序排列，如果不按照顺序执行可能导致数据库的不一致问题。
同一个事务多个更新是否能够分配给不同worker？不行因为要保证事务的完整性，如果t1表改给w1处理，t2给w2处理，t1完成修改刚好查询发现更新一半，那么破坏了事务逻辑的隔离性。

分发事务要满足要求

不能更新覆盖，修改同一行记录交给同一个worker
同一个事务不能拆分。

MySQL 5.5 版本的并行复制策略

按表分发策略

如果两个事务是更新两个表，而且互不关联那么就可以分发不同的worker，但是如果表之间有关联那么仍然是不可以分配给两个worker的。
worker对应一个hash表，存储库名.表名，value是多少个事务在修改这张表。

事务T的分配流程

事务T要修改t1，但是worker_1队列有事务在修改表t1，事务和队列某个事务修改同一个表的数据，这种就是T和worker_1冲突。
顺序判断发现worker_2也冲突
如果冲突worker多于一个那么进入到coordinator等待
每个worker执行并且修改hash table，如果事务执行完就从table去掉。worker_2去掉t3
这个时候coordinator发现事务T只和worker_1发生冲突，所以可以分配给worker_1
继续分配事务。

冲突关系的三种情况

和所有worker不冲突，分配给空闲worker
和一个worker冲突，分配给这个worker
和多于1个worker冲突，那么就要继续阻塞。

相当于一个事务要分配给worker，如果worker有修改相同的表，为了防止更新覆盖，所以要分配给这个worker。如果事务发现多个worker和自己冲突，为了防止更新覆盖所以只能阻塞。

按行分发策略

binlog必须是row
而且两个并行事务没有更新同一行。
现在的key是库+名表名+唯一键的值,value还是多少个事务。

CREATE TABLE `t1` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

上面这个场景的问题就是B无法修改的原因就是可能id=1的还没修改完，导致唯一键冲突。前提是分配到worker并且是B先执行。
所以key应该改为库名 + 表名 + 索引 a 的名字 +a 的值
所以对于hash表来说这个语句需要分析三个项
- key=db1+t1+id+2，value=2
- key=db1+t1+a+2，value=1
- key=db1+t1+a+1，value=1
然后就会发现a=1这里冲突，所以只能加入到A的worker上去。等待A执行完再到B
行策略消耗更多计算资源

binlog解析出表名，主键，唯一索引的值，binlog还要是row
表必须有主键
不能有外键。

但是如果操作行很多的大事务

耗费内存
耗费CPU

所以如果并行度超过阈值就要转换为单线程

coordinator先hold住事务
等待worker执行完，变成空队列
coordinator直接执行事务
恢复并行。

MySQL 5.6 版本的并行复制策略

直接用库为单位的分配，但是表都存在一个库就没什么用了

MariaDB 的并行复制策略

同一组提交的事务，不会修改同一行
主库并行的事务，备库也可以并行执行

mariaDB这么做

每组提交都会有一个commit_id并且会自增
commit_id写入到binlog中。
相同commit_id的事务分发到多个worker中执行
然后取下一批。

原因就是假设同一组能够修改同一行，但是反例就是修改同一行一定有一个会先被阻塞，所以同一组提交那么只能是修改不同行的事务。

主库的执行情况

备库执行情况，很明显可以看到每次都是一组执行完，才能到下一组。如果其中有一个大事务，就导致同一组事务需要等待这个大事务执行完。所以没有真正模拟主库的并发度。

MySQL 5.7 的并行复制策略

slave-parallel-type控制执行策略

配置为 DATABASE，按库并行执行
配置LOGICAL_CLOCK，也就是MariaDB的执行方式。但是做了优化

同时处于“执行状态”的所有事务，是不是可以并行？

当然是不行的
对于MariaDB的核心就是事务都可以提交，也就是不会造成锁冲突问题。

其实到redo log prepare阶段也是说明组事务已经解决锁冲突检测

所以5.7的复制策略

同时处于prepare的事务，在备库可以并行执行
处于prepare和commit之间的事务也可以并行执行。

binlog_group_commit_sync_delay：延迟多少微妙才fsync到磁盘

binlog_group_commit_sync_no_delay_count：累积多少次才调用fsync。

拉长write之后fsync的时间，但是能够减少fsync的次数。
制造更多同时处于prepare事务，相当于增加了备库复制的并行度。

MySQL 5.7.22 的并行复制策略

基于writeset的并行复制

binlog-transaction-dependency-tracking参数控制并行策略

COMMIT_ORDER：同时进入prepare和commit来判断是否可以并行
WRITESET：计算事务的更新行的hash，如果两个事务更新行的hash没有交集说明可以并行
WRITESET_SESSION：保证事务的执行顺序。

hash值是通过库名 + 表名 + 索引名 + 值来计算的。

优势

writeset主库生成后直接写入到binlog，备库就不需要解析了
不需要扫描binlog所有的事务，可以通过writeset的交集就能够判断是不是可以分配。
分发策略不依赖binlog，所以statement格式也可以使用。之前需要扫描事务状态，mariaDB需要扫描事务是不是在同一个组，5.5版本需要扫描行并且去到worker判断是不是冲突。但是这里只需要在writeset中记录更新行，并且在判断是不是可以并行只需要判断writeset有没有交集就可以了。

问题

你为了更快地让备库追上主库，要开并行复制。在 binlog-transactiondependency-tracking 参数的 COMMIT_ORDER、WRITESET 和 WRITE_SESSION 这三个取值中，你会选择哪一个呢？

选择WRITESET
对于commit_order来说如果每个commit_id不同导致的就是单线程模式，相当于就是没有组事务
WRITESET_SESSION如果要求事务是顺序执行，那么并行事务就无效了。

总结：主从复制的策略，包括行和表，行很明显由于需要row格式，并且还需要注意唯一索引所以导致的问题就是binlog占用的内存很大，而且CPU计算成本也很高。另一种方案就是maria复制，这种利用同时提交组事务，并行地执行，但是问题就是无法模拟主库并发度，也就是run和commit在从库必须是同时执行完才能换下一组。对于现在的5.7来创新了，①行和表②组提交并行，到现在的writeset实际上就是根据每行数据执行一个hash函数得到hash值，也就是每行数据有自己的hash，如果事务要修改，那么自然就会加上这个hash到writeset中，好处很明显，判断是否能执行只需要判断writeset是否有交集，没有那么就可以并行执行。否则只能等待。①需要通过hash表计算，遍历行②需要判断每个事务的状态，并且判断是不是同一组事务关键是不是同时commit或者是prepare。但是③不需要扫描，能够直接判断是否可以并行执行。

第27讲: 主库出问题了，从库怎么办？

基于位点的主备切换

如果下面的主库A出现了故障，这个时候从库都需要修改对应的主库为A1
master_log_name和master_log_pos是修改主库的时候必要的参数，那么如何设置？

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos

第一个要找的是位点，但是这个同步位点不是准确的。

等待主库A1把中转日志完成
在A1中执行show master status，获取file和position
获取原主库A故障的时刻T
使用mybinlog工具解析A1的file，得到T时刻的位点。

end_log_pos 123说明的是A1实例在T时刻写入binlog的位置。这个123就可以作为从库change master的master_log_pos参数。但是为什么不精确？
如果这个时候A插入一条记录，把binlog传给A1和B的瞬间A挂了
系统的状态

B由于同步binlog所以记录插入
A1新主库也插入了这条记录，但是日志写在了123后面。原因就是A和A1之间的一个主从延迟问题，导致插入语句在123之后执行而不是同步A同时执行。
如果B重新指向A1那么就会重新执行123后面的插入R记录数据。报错，然后就停止同步

解决方案

跳过这些错误事务，设置 sql_slave_skip_counter=1。
slave_skip_errors设置这个参数，跳过指定的错误。

GTID

global transaction identifier全局事务id。
GTID=server_uuid（全局唯一值）:gno（每次提交事务才会赋值，并且自增）

启动

gtid_mode=on 和 enforce_gtid_consistency=on
两种id生成方式，取决于 session 变量 gtid_next

gtid_next=automatic默认值
1. 记录binlog的时候记录一行set @@SESSION.GTID_NEXT=‘server_uuid:gno’;
2. 把gtid加入到实例的gtid集合
set gtid_next='current_gtid
1. current_gtid已经存在本实例gtid集合，那么这个事务执行就会被忽略
2. 如果没有存在，那么就分配给接下来执行的事务

这个时候mysql实例通过gtid集合维护了执行过的事务集合
如果这个时候A插入一条记录insert(1,1)，第一件事情要做的就是set一个gtid。如果这条插入语句同步到B库，那么也会执行这个set gtid。那么他们的gtid就是相同的。所以可以解决之前切换主库的重新插入问题。
如果A是Y的从库，Y也要插入一条insert(1,1)，那么A是不是就会出现主键冲突问题。

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;

提交空事务，设置gtid，假装已经处理过这条语句不再执行。并且恢复默认的gtid_next的自动增长机制。

基于 GTID 的主备切换

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 #说明是使用gtid协议

这个时候已经不需要执行 MASTER_LOG_FILE 和 MASTER_LOG_POS参数。
整个切换逻辑，B执行start slave

B指定主库A1
把set_b（就是B拥有的gtid集合）发送给A1
然后求set_a（A1的gtid集合）和set_b差集，而且必须是set_a包含set_b不然就报错，如果判断成功就把差集的binlog发送给B
然后按顺序给B发送binlogw

基于位点是备库决定从什么位点开始传输，但是不做日志完整性判断

GTID 和在线 DDL

为了避免备库建立索引对主库产生影响，所以可以先把X（主库）和Y（从库），断开沟通，但是保持Y的binlog写入。下面是X的对于Y的DDL操作的一个处理，把它建立索引的事务变成一个空事务，这样Y备库可以创建索引而且不影响主库的继续运行。

set GTID_NEXT="server_uuid_of_Y:gno";
begin;
commit;
set gtid_next=automatic;
start slave;

问题

你在 GTID 模式下设置主从关系的时候，从库执行 start slave 命令后，主库发现需要的 binlog 已经被删除掉了，导致主备创建不成功。这种情况下，你觉得可以怎么处理呢？（这部分仍然存疑，因为自己暂时还不太会分析）

第28讲：读写分离有哪些坑？

读写分离

直连：查询性能好，但是需要了解后端的架构，而且操作繁杂
proxy：客户用起来很方便，但是后端人员维护很难。

但是读写分离仍然会遇到主从延迟的问题，假如更新之后立刻访问从库

这个时候就会读到一个过期的数据，叫做过期读
处理过期读的方案

强制访问主库
sleep方案
判断主备是否延迟
配合semi-sync
等主库位点
等GTID方案

强制走主库方案

查询分为两类

对于必须拿到最新结果那么就访问主库
可以读取旧数据就可以先访问从库

Sleep 方案

更新之后先让从库sleep（1），并且如果客户要访问，那么就通过ajax发送模拟新的商品。但是这样是不准确的

如果查询请求本来是0.5那么还是要等1秒
延迟超过1s仍然还是会过期读。

只能解决一下ajax场景的过期读问题。

判断主备无延迟方案

判断SBM是不是0，如果是那么才能执行请求。
或者对比位点。如果主库最新位点和从库最新位点不同那么就不能读取
对比GTID集合。
- auto_position=1说明使用了GTID协议。
- Retrieved_Gtid_Set:备库收到的日志gtid集合
- Executed_Gtid_Set：备库收到已经执行完的gtid集合
- 如果两个集合相同说明备库已经执行完了。
但是还是没有达到精准，思考一个事务binlog在主库的状态

主库执行完写binlog返回给客户端
binlog从主库发送到备库，备库收到
在备库执行binlog完成。

也有一种可能就是提交了，但是还没有同步到从库的情况，那么这个时候如果去读，那么就会发现没有事务3。

配合 semi-sync

事务提交，主库把binlog发送给从库
从库收到binlog之后给主库发送一个ack
主库收到ack之后，才能给客户端返回确认。

意思就是gtid和判断位点的共同问题就是事务提交但是有的事务还有同步成功就给客户端返回已经提交成功。那么就会造成过期读问题。主要是出现在gtid上。按时现在多了一个确认ack，这个时候才能够返回提交成功。

如果主库掉电，binlog没有发给从库会不会丢失？

普通异步会，但是semi-sync有ack机制所以不会
semi-sync+位点判断，就可以直到从库是否收到新的事务。

只要查询落在返回ack的库那么就能返回
其它没有返回ack的库就可能会出现过期读。

但是其实并不需要等待主从完全同步。

如果使用等待位点，你会发现从库B一直和A有一个延迟，导致等待时间非常长。但是实际上在状态3有些事务已经写入了。
所以只要事务写入，其实就能够得到预期的结果

semi-join配合判断主从延迟的问题

一主多从的时候在某些从库仍然会导致过期读
持续延迟导致等待时间过长。

等主库位点方案

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

在从库执行
file和pos指定主库的文件和位置
timeout超时时间

返回一个M，表示执行命令到应用完file和pos的binlog之后执行了多少事务

还会返回

备库同步异常返回null
等待超时返回-1
发现如果已经执行过那么就是0

整个逻辑过程

trx1更新完立刻执行show master status获取file和pos
选定一个从库执行查询语句
从库执行select master_pos_wait(File, Position, 1)；
如果返回的是>=1那么就在这个从库执行查询语句
否则到主库执行查询语句

如果发现master_pos_wait返回来的数>=0说明就是从库的执行结果一定是包含了trx1，原因就是如果是0就是已经执行过，如果是1，那么就是执行了一个事务。timeout就是select最大的等待时间。这个执行是针对于备库的。

GTID 方案

select wait_for_executed_gtid_set(gtid_set, 1);

逻辑

等待，直到库执行的事务包含传入的gtid_set返回0。
超时返回1

现在允许更新之后发送gtid给用户。那么gtid就不需要查询对应的信息了。

等gtid的流程

trx1事务更新之后，返回gtid1
选定从库执行查询语句
执行select wait_for_executed_gtid_set(gtid1,1);
如果返回0说明从库包含了这个gtid，也就是执行了
否则就去主库里面查询

总结：针对读写分离的最大的坑就是过期读，第一种和第二种方案都是作用不大的，主库查询可能导致主库的访问压力大，sleep只针对ajax的页面。判断主备无延迟方案，就是判断位点和gtid集合，问题就是必须要进行等待SMB=0，造成等待时间过长。而且还有可能造成的问题就是返回给客户端成功的同时没有更新到从库，导致的过期读。所以解决方案可以是semi-sync+判断主备无延迟这只能解决客户端收到的提交一定是从库更新但是没有解决等待时间问题。所以可以通过gtid方案，这个实际上就是在查询主库之前先查询是否有包含事务的gtid，没有那么就访问主库，等待主库位点其实也是这样的方案，也是查询从库前先看看主库位点后面的日志到底在备库是否执行，没有那么只能在主库查询。但是后面两种方案更精妙的地方可以控制某个事务更新就能够查询而不是等待延迟结束才去查询。

问题

假设你的系统采用了我们文中介绍的最后一个方案，也就是等 GTID 的方案，现在你要对主库的一张大表做 DDL，可能会出现什么情况呢？为了避免这种情况，你会怎么做呢？

大事务问题，导致大部分查询压力去到了主库
先把读请求切换到主库，然后再在主库上做DDL，让备库延迟同步之后再切换回备库。

第29讲：如何判断一个数据库是不是出问题了？

select 1 判断

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

set global innodb_thread_concurrency=3;通过这个语句来控制并发线程
可以发现select 1并没有任何作用，但是直接查询表就会被阻塞。

如果出现大量死锁那么并发线程最大数量128都被占用系统是不是就不能运行了

不会，原因是如果发现锁等待，那么这个并发线程是不会占用cpu的。如果锁等待也需要占用线程

线程1执行begin; update t set c=c+1 where id=1然后处于空闲状态
线程2-129都执行update t set c=c+1 where id=1;，那么就会有128个线程都在等待
如果锁等待状态的线程不减1那么就无法进来如何语句进行操作。

如果只是sleep仍然是要计算到并发线程数量里面的，所谓并发线程是真正能够占用cpu运行的线程。

查表判断

 select * from mysql.health_check;

创建一个表，并且放入检测语句定期查询

更新判断

 update mysql.health_check set t_modified=now();

CREATE TABLE `health_check` (
 `id` int(11) NOT NULL,
 `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

更新检测是为了主库和从库更新不会冲突，所以创建了表，记录两个库的id和更新时间。
如果更新失败那么就需要切换主从库。

但是为什么还有判定慢的问题？

原因就是IO资源分配问题，对于这个更新检测是需要N秒的时间，但是有时候运气好IO压力不大的时候很快就能够返回这个时候就认为主库没有问题，但是如果由于IO压力大导致的超时，那么可能就会误判。
而且由于IO问题导致本来检测很快的语句变得很慢。

内部统计

performance_schema库能够返回内部的mysql的IO请求时间
file_summary_by_event_name表里面统计了IO的请求时间。

count_star:IO总次数
SUM、MIN、AVG、MAX：这些就是IO的请求数，最小，平均，最大值。
SUM_NUMBER_OF_BYTES_READ：总共从redo log里读了多少字节。
但是performance_schema需要额外的检测成本，如果全部打开数据库性能会下降百分之10，所以要选择自己需要的来检测。

第30讲：答疑文章（二）：用动态的观点看加锁

CREATE TABLE `t` (
 `id` int(11) NOT NULL,
 `c` int(11) DEFAULT NULL,
 `d` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

begin;
select * from t where id>9 and id<12 order by id desc for update;

这条语句加上的锁(0,5],(5,10],(10,15)
对于优化器来说是一定要找到第一条id<12的值。
那么为什么会有15？原因就是所谓的等值查询是看看到底有没有在索引上面进行查找，查找一个值是id<12的，所以这个15就是等值查询。这里没有找到id=12的值，但是找到10-15这个间隙。由于没有找到12，所以还需要继续往右边遍历第一个不等于12的值。所以是一个等值查询。
然后就要向左边遍历，但是向左遍历的时候10是符合规则的，所以要继续往左边遍历找到第一个5不符合规则，所以要给5加上next-key lock。这里向左遍历实际上就是看是不是符合范围，但是第一条的那种是找id=12的记录，由于没有所以往右边遍历找到不符合的为止。并且优化成间隙锁

等值查询的过程

begin;
select id from t where c in(5,20,10) lock in share mode;

这个分析就很简单(0,5],(5,10),(5,10],(10,15),(15,20],(20,25);

 select id from t where c in(5,20,10) order by c desc for update;

上面语句的执行流程是20，10，5，如果上面两条语句同时执行就会产生死锁问题

怎么看死锁？

show engine innodb status

结果的三个部分

(1) TRANSACTION第一个事务
(2) TRANSACTION第二个事务
WE ROLL BACK TRANSACTION (1)：处理结果，回滚第一个事务

第一个事务的信息中

WAITING FOR THIS LOCK TO BE GRANTED等待锁信息
index c of table `test.t等待索引c上面的锁
lock mode S waiting 自己要加上一个读锁，状态在等待
Record lock这是一个记录锁
n_fields 2记录是两列，字段c和id字段
0: len 4; hex 0000000a; asc ;;第一个字段是c，表示是16
1: len 4; hex 0000000a; asc ;; 第二个字段id=1

意思就是在等待(c=10,id=10)这一行的锁

第二个事务显示的信息

index c of table test.t 等待索引c的锁
hex 0000000a 和 hex 00000014持有c=10 和 c=20 的记录锁
WAITING FOR THIS LOCK TO BE GRANTED等待(c=5,id=5)记录锁

“lock in share mode”：占有5的记录锁，等待20的锁

“for update”：占有c=20和c=10的记录锁，等待5的锁

选择成本小的进行回滚。
尽量顺序加锁。

怎么看锁等待？

这里原因就是删除10之后，原本的间隙锁(10,15)变成了(5,15)因为需要扫描到不符合规则的第一个记录。刚好就是5了，所以插入是不行的。

update 的例子

加锁范围是一直往右边遍历扫描(5,10]、(10,15]、(15,20]、 (20,25] 和 (25,supremum]，有c>5的第一个记录是c=10所以并不会加上(0,5]的锁
看上去c好像没什么问题实际上间隙不仅仅是c，id也有（1-10），所以对于下面的update t set c=5 where c=1语句就像是插入c=5，而且id=5的记录。并且删除c=1，id=5。而第一条刚好是间隙锁没有锁住id=1所以可以执行。

问题

空表的间隙？

一个空表只有一个间隙，最小和最大记录之间，只要锁住就无法插入任何数据

第31讲：误删数据后除了跑路，还能怎么办？

误删行

flashback的原理，修改binlog内容，拿到原库重放。需要确保binlog_format是row，binlog_row_image=null

单个事务恢复

insert语句的write_rows_events需要改成delete_rows_events
对于delete来说就是相反的操作
update来说只需要对调两个前后的行数据

如果一个事务涉及到多行，那么就要调转顺序来进行恢复

不建议在主库上执行操作

那么为什么要在从库先进行恢复？

防止在误删除的时候，业务逻辑重新操作这行数据，如果恢复就会造成二次破坏

那么怎么预防？

sql_safe_updates设置为on，必须要有where条件才能删除。
代码上线前需要sql审计。

对于truncate和drop table是无法进行恢复操作的。因为就算配置了row格式，但是最后执行的是statement也就是只有一条语句没有具体的行信息所以是无法恢复的。

误删库 / 表

需要定期全量备份。实时配备binlog
12点删除表之后恢复流程

取全量备份
备份恢复出一个临时库
从日志备份取出0点之后的日志
除了误删语句之外，其它语句都需要应用到临时库。

几点注意事项

如果临时库包含多个数据库，可以-database指定误删除表的那个数据库
跳过12点误删的那条语句，如果有gtid可以直接执行一个空事务。

但是仍然mysqlbinlog恢复比较慢

误删表没有只针对这张表的日志
mysqlbinlog应用日志只能是单线程

加速的方法

start slave之前需要change replication filter replicate_do_table = (tbl_name)，让临时库同步误删除的表
使用到了并行复制技术，主要是把临时库设置成从库，那么应用日志的时候就可以通过并行复制了。

如果binlog备库缺少了临时实例所需要的binlog就需要去到binlog备份里面去找，并且传输给线上备库。
恢复数据的主要思路就是备份+binlog日志。必须定期进行全量备份。

延迟复制备库

如果上一次全量备份相比现在的误删除比较就，那么可能需要恢复的量就会非常大。比如上一次备份在上周，误删除在这周过了6天的时候删除，就会导致全量备份之后需要应用6天的binlog进行恢复。

搭建延迟复制备库

CHANGE MASTER TO MASTER_DELAY = N可以让主库和备库之间的传输binlog延迟N秒
如果设置比较长，比如一个小时，只要一个小时之内发现误删除都是可以快速恢复的。通过stop slave，和之前的方法就可以进行恢复，跳过误删除命令。

预防误删库 / 表的方法

账号分离
制定操作规范

第32讲：为什么还有kill不掉的语句？

收到 kill 以后，线程做什么？

把sessionB的状态改成THD::KILL_QUERY
给执行sessionB的线程发送停止信号

只是修改状态sessionB是无法发现的，但是传输信号那么线程才会发现状态已经改成停止了

一个语句的执行有埋点，这些埋点就是判断线程状态
等待状态必须要能唤醒
语句开始到终止都是有一个过程而不是立刻停止的。

kill不掉的例子

innodb_thread_concurrency 不够用

set global innodb_thread_concurrency=2设置并发线程数，然后执行下面的流程

这里的kill query无效。这是为什么？

pthread_cond_timedwait是等待行锁使用的函数。这个时候是可以被唤醒的，但是由于上面的12号线程每10毫秒就会调用一次nanosleep进入sleep状态，即使现在12线程的状态已经改变，但是并没有去判断当前线程的状态。
如果是kill connection的执行逻辑

12号状态被设置为 KILL_CONNECTION；
关掉12号线程的网络连接，所以sessionC收到了关闭连接的信号

但是12号线程的状态仍然是killed，需要等待满足innodb条件的时候查询语句执行。才能修改状态为kill_connection

kill无效的情况

线程没有判断状态的逻辑
终止逻辑耗时比较长

超大事务，对新数据进行回滚
大查询回滚，临时文件太大
DDL需要删除临时文件，但是IO压力太大。

那么ctrl+c是不是可以终止线程？

不可以，因为客户端不能操作服务器端，他只是发送了一个kill_query命令
所以kill仍然需要涉及到后端的大量操作。

另外两个关于客户端的误解

表多连接会慢

连接的过程，补全过程是比较慢的也就是建立库与表的哈希表。

show databases；
切换到db1库，show tables
两个命令结果做一个哈希表

连接慢是因为客户端慢，因为做哈希表，如果表多确实会比较慢，所以可以通过-A不做补全。

–quick 加上就不使用缓存导致性能变慢

接收服务端的数据可以是

本地缓存
不缓存

问题

如果你碰到一个被 killed 的事务一直处于回滚状态，你认为是应该直接把 MySQL 进程强行重启，还是应该让它自己执行完成呢？为什么呢？

第33讲.我查这么多数据，会不会把数据库内存打爆？

全表扫描对 server 层的影响

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

扫描的是主键，那么每次都会把数据加入到结果集，结果集存放到哪？

获取一行写到net buffer默认16k
重复获取行写入net buffer，调用接口发出去socket buffer
发送后清空net buffer再次执行上述
如果socket buffer本地网络栈满了就会给net buffer发送eagain。停止继续读数据，直到发送给客户端清空之后再继续

所以mysql服务器端是边读边发的，而不是一次到位。
而且如果发现查询线程处于sending to client可能就是客户端没有读取socket buffer的数据，或者是读取太慢的问题。通常需要使用mysql_store_result来使用本地缓存先读多行数据，然后再发。
sending data可能是执行器处于任意状态，下面的B就是处于阻塞的状态但是仍然是sending data

全表扫描对 InnoDB 的影响

LRU算法的基本流程

状态1的链表头是P1，表示刚被访问
后面就是访问P3，所以P1和P2后移
状态3访问新的数据页从磁盘读取，清空末尾的一个节点，腾出内存出来。
很明显这个LRU出现的问题就是如果进行一次全表扫描就清除所有的热点数据

为了解决这个问题LRU优化一下，把链表5:3分为young和old

访问页3这个时候它在young所以放到头
访问px不存在内存的页就放到old区的头。
处在old区需要判断
1. 存在超过1s放到链表头
2. 短于1s不用管，为了全表扫描。
新插入的页在old
数据页如果被多次访问而且上下间隔不超过1s那么就一直放到old
所以这个数据页如果是属于全表扫描的很快就会被淘汰

相当于young保存热点数据，old保存新加入的数据，经过筛选才会进入到young。

第34讲：到底可不可以使用join？

CREATE TABLE `t2` (
 `id` int(11) NOT NULL,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `a` (`a`)
) ENGINE=InnoDB;
create table t1 like t2;

给t2插入1000行数据，t1插入100行数据

Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

相当于就是两个表连接的方式就是嵌套循环，而且被驱动表可以使用索引
t1读取一行然后直接去到t2进行一个索引的等值查询，所以定位的速度非常快

如果不使用join会出现什么问题？

先查询t1表的结果集
然后还需要每次调用t2表的查询语句select * from t2 where a=$R.a
相当于就是比起join多调用了100次语句。所以不如直接使用join好了

怎么选择驱动表？

对于被驱动表都可以使用索引的情况，那么自然就是驱动表越小越好，因为驱动表需要根据条件扫描表的行数，如果表小那么扫描的行数可能就越小。

Simple Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.b);

如果被驱动表没有对应的索引，其实无论小表还是大表最后的处理方式都是扫描整个表。
这里的判断次数是100 * 1000。

Block Nested-Loop Join

这种就是上面那种情况另一种叫法

把t1表的数据存入join buffer
然后t2取出一行数据对比t1表的所有数据如果符合那么就加入到结果集。

问题就是扫描的行数更多，需要扫描整个被驱动表，而且还需要每行数据都要去和t1表的结果集进行一一比较

如果join buffer装不完t1怎么办？

这个时候就需要分段装，比如t1一开始只能装88行数据，t2判断完一次之后，join buffer清空，t1剩下的12行加载进来，然后t2又要再次进行整个表去对比这12行数据。相当于就是对于被驱动表来说需要扫描更多次。比如扫描的第一次对比t1的88行数据，但是t1没有完全加载进来，所以还需要重新扫描一次t2来对比t1的12行数据。
这个时候怎么选表？
通常选小表作为驱动表，尽量减少表占用join buffer的内存，那么就可以减少对被驱动表的扫描次数。

第35讲：join语句如何优化？

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
 declare i int;
 set i=1;
 while(i<=1000)do
 insert into t1 values(i, 1001-i, i);
 set i=i+1;
 end while;
 
 set i=1;
 while(i<=1000000)do
 insert into t2 values(i, i, i);
 set i=i+1;
 end while;
end;;
delimiter ;
call idata();

Muti-Range Read优化

 select * from t1 where a>=1 and a<=100;

这个优化是直接把a得到的id集合放到read_rnd_buffer，排序之后再回表。
把随机访问的id变成顺序访问，大大加快了访问速度。

Batched Key Access

对于NLJ来说MRR优化无效是因为每次取出都是t1一个行数据，然后去到t2进行查询，而不是一个范围查询。
join buffer在BNL中用来保存驱动表，但是没有在NLJ中使用。所以BKA算法就是先把t1的数据先取出来一部分，然后交给到t2进行一个顺序查询。

BNL算法的性能

使用 Block Nested-Loop Join(BNL) 算法时，可能会对被驱动表做多次扫描。如果这个被驱动表是一个大的冷数据表，除了会导致 IO 压力大以外，还会对系统有什么影响呢？

如果冷表的数据量只有buffer pool的3/8的话，那么刚好就存进old，问题就是下一次扫描冷表由于已经超过在old一秒所以会存入young
如果冷表太大的问题就是，不断淘汰新的页，如果现在有一个未来热点页进来，由于old需要呆够1s才能去掉young，那么导致热点页没能去到young，影响young的淘汰机制。
解决办法还是提高join buffer。尽量让驱动表一次能被被驱动表扫描完。
对系统影响
- 多次扫描被驱动表，占用IO
- 占用CPU资源，大量判断
- buffer pool热点数据被淘汰。

BNL 转 BKA

假如t2有100w条数据，t1有1000行数据，这个时候如果使用BNL问题就是10亿次的判断。

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

整个算法的过程就是先把t2符合条件的记录取出来，然后创建临时表，给b加上索引
然后让t1和临时表建立连接。那么这个时候只需要扫描t2的100w，然后扫描一次t1，最后就是临时表也要扫描一次。明显比10亿次少多了

码农公寓

目录