mysql 进阶

 

binlog vs relaylog

1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的，所有引擎 都可以使用。

2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日 志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

3. redo log 是循环写的，空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

一天一备跟一周一备的对比。好处是“最长恢复时间”更短。在一天一备的模式里，最坏情况下需要应用一天的 binlog。比如，你每天 0 点做一次全量 备份，而要恢复出一个到昨天晚上 23 点的备份。一周一备最坏情况就要应用一周的 binlog 了。

 

今天，我介绍了 MySQL 里面最重要的两个日志，即物理日志 redo log 和逻辑日志 binlog。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1， 这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个 参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

我还跟你介绍了与 MySQL 日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维 持数据逻辑一致性时常用的一个方案，即使你不做数据库内核开发，日常开发中也有可能会用到。

 

若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就 是这个要求:事务在执行期间看到的数据前后必须是一致的。

若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事 务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值 是 2。

 

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。 如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在

加微信 ixuexi66 获取最新一手资源

系统中可以存在多个版本，就是数据库的多版本并发控制(MVCC)。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。

同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、 B、C 对应的事务是不会冲突的。

 

今天，我跟你分析了数据库引擎可用的数据结构，介绍了 InnoDB 采用的 B+ 树结构，以 及为什么 InnoDB 要这么选择。B+ 树能够很好地配合磁盘的读写特性，减少单次查询的 磁盘访问次数。

由于 InnoDB 是索引组织表，一般情况下我会建议你创建一个自增主键，这样非主键索引 占用的空间最小。但事无绝对，我也跟你讨论了使用业务逻辑字段做主键的应用场景。

 

这里，我先和你说结论吧。B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位 记录。为了直观地说明这个概念，我们用(name，age)这个联合索引来分析。

今天这篇文章，我和你继续讨论了数据库索引的概念，包括了覆盖索引、前缀索引、索引 下推。你可以看到，在满足语句需求的情况下， 尽量少地访问资源是数据库设计的重要原 则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目 标。

 

上期的问题是，通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是 否合理。

在评论区，有同学问到为什么要重建索引。我们文章里面有提到，索引可能因为删除，或 者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺 序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。 这道题目，我给你的“参考答案”是: 重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不 论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个 语句就白做了。这两个语句，你可以用这个语句代替 : alter table T engine=InnoDB。 在专栏的第 12 篇文章《为什么表数据删掉一半，表文件大小不变?》中，我会和你分析 这条语句的执行流程。

 

 

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文 本。

以前有一种做法，是通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做 备份。注意，在备份过程中整个库完全处于只读状态。

但是让整库都只读，听上去就很危险:
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆;
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从 延迟。

 

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持， 这个过程中数据是可以正常更新的。

 

MySQL 里面表级别的锁有两种:一种是表锁，一种是元数据锁(meta data lock， MDL)。

今天，我跟你介绍了 MySQL 的全局锁和表级锁。

全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，我建议你选择使用– single-transaction 参数，对应用会更友好。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是:

要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎;

要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查 询和更新。

 

今天，我和你介绍了 MySQL 的行锁，涉及了两阶段锁协议、死锁和死锁检测这两大部分 内容。

其中，我以两阶段协议为起点，和你一起讨论了在开发的时候如何安排正确的事务语句。 这里的原则 / 我给你的建议是:如果你的事务中需要锁多个行，要把最可能造成锁冲突、 最可能影响并发度的锁的申请时机尽量往后放。

但是，调整语句顺序并不能完全避免死锁。所以我们引入了死锁和死锁检测的概念，以及
提供了三个方案，来减少死锁对数据库的影响。减少死锁的主要方向，就是控制访问相同
资源的并发事务量。

 

避免死锁方案：

方案一，事务相对较长，则占用锁的时间较长，会导致其他客户端等待资源时间较长。

方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间 相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源 分片使用(每次执行使用不同片段的资源)，可以提高并发性。

方案三，人为自己制造锁竞争，加剧并发量。

 

InnoDB 的行数据有多个版本，每个数据版本有自己的 row trx_id，每个事务或者语句有 自己的一致性视图。普通查询语句是一致性读，一致性读会根据 row trx_id 和一致性视图 确定数据版本的可见性。

 对于可重复读，查询只承认在事务启动前就已经提交完成的数据;
 对于读提交，查询只承认在语句启动前就已经提交完成的数据;

而当前读，总是读取已经提交完成的最新版本。

你也可以想一下，为什么表结构不支持“可重复读”?这是因为表结构没有对应的行数 据，也没有 row trx_id，因此只能遵循当前读的逻辑。

当然，MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，也许以后会支持表结构的可 重复读。

 

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录， 直到碰到第一个不满足 k=5 条件的记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停 止继续检索。

 

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在 内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要 插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数 据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使 用 change buffer 了。

 

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的 是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘 的 IO 消耗。

 

选错索引：

扫描行数有关，索引与区分度有关，

方案：强制读取 更改索引  更改sql

对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。

而对于其他优化器误判的情况，你可以在应用端用 force index 来强行指定索引，也可以 通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

 

如果某次写入使用了 change buffer 机制，之后主 机异常重启，是否会丢失 change buffer 和数据。

这个问题的答案是不会丢失，留言区的很多同学都回答对了。虽然是只更新内存，但是在 事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复 的时候，change buffer 也能找回来。

 

1. 直接创建完整索引，这样可能比较占用空间;

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引;

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题;

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

 

 

为什么经过这个操作序列，explain 的结果就不 对了?这里，我来为你分析一下原因。

delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是 覆盖了原来的 10 万行。

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

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

 

第一种场景是，粉板满了，记不下了。这时候如果再有人来赊账，掌柜就只得放下手里 的活儿，将粉板上的记录擦掉一些，留出空位以便继续记账。当然在擦掉之前，他必须 先将正确的账目记录到账本中才行。
这个场景，对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操 作，把 checkpoint 往前推进，redo log 留出空间可以继续写。我在第二讲画了一个 redo log 的示意图，这里我改成环形，便于大家理解。

第二种场景是，这一天生意太好，要记住的事情太多，掌柜发现自己快记不住了，赶紧 找出账本把孔乙己这笔账先加进去。 这种场景，对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要 淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页 写到磁盘。

第三种场景是，生意不忙的时候，或者打烊之后。这时候柜台没事，掌柜闲着也是闲 着，不如更新账本。
这种场景，对应的就是 MySQL 认为系统“空闲”的时候。当然，MySQL“这家酒 店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即 使是“生意好”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

第四种场景是，年底了咸亨酒店要关门几天，需要把账结清一下。这时候掌柜要把所有 账都记到账本上，这样过完年重新开张的时候，就能就着账本明确账目情况了。 这种场景，对应的就是 MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速 度会很快。

 

今天这篇文章，我延续第 2 篇中介绍的 WAL 的概念，和你解释了这个机制后续需要的刷 脏页操作和执行时机。利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数 据库的性能。

但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰 而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间 长一些。在文章里，我也给你介绍了控制刷脏页的方法和对应的监控方式。

 

如果一个高配的机器，redo log 设置太小，会发生 什么情况。

每次事务提交都要写 redo log，如果设置太小，很快就会被写满，也就是下面这个图的状 态，这个“环”将很快被写满，write pos 一直追着 CP。这时候系统不得不停止所有更新，去推进 checkpoint。 这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

 

那为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢?

这是因为即使是在同一个时刻的多个查询，由于多版本并发控制(MVCC)的原因， InnoDB 表“应该返回多少行”也是不确定的。这里，我用一个算 count(*) 的例子来为你 解释一下。

 

 

 

 

 

 

 

 

 

 

 

查询长时间不返回：

mdl锁 等flush 等行锁

查询慢：

慢查询 或 undo log频繁生成

 

 

行锁确实比较直观，判断规则也相对简单，间隙锁的引入会影响系统的并发度，也增加了 锁分析的复杂度，但也有章可循。下一篇文章，我就会为你讲解 InnoDB 的加锁规则，帮 你理顺这其中的“章法”。

 

适得其反的提高性能的方法：

1.短连接风暴：

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

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

 

慢查询性能问题在 ，大体有以下三种可能:

1. 索引没有设计好;
2. SQL 语句没写好;
3. MySQL 选错了索引。

 

qps突增：

1. 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单 是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那 么快，那么就可以从数据库端直接把白名单去掉。

2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后 断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。

3. 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。 这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写 成"select 1"返回。

 

误删数据：

误删行：binlog设置

误删库/表：全量 增量 跳过误操作点

延迟复制备库

预防误删库/表的方法（账号分离 操作规范）

 

自增主键不连续：

1.唯一键冲突

2.事务回滚

3.每次申请自增id为上一次的两倍

后记：

勤动手 搞清楚原理 构建知识体系 思考查询过程