Mysql 思维导图模板_ProcessOn思维导图、流程图

基础组件

Mysql Server层

连接器

建立连接

输入连接指令

连接指令

方法一

输入： mysql -h$ip -p$port -u$user -p

回车以后再输入密码

案例：mysql -h127.0.0.1 -uroot -p;

方法二

输入：mysql -h$ip -p$port -u$user -p$password

案例：mysql -h127.0.0.1 -uroot -p123456；

两种方法比较

方法二为明文输入，有暴露密码的风险

账户密码错误

抛出"Access denied for user"错误

管理权限

当用户登录成功以后，mysql会到权限表查询用户具有的权限

注意事项

当用户登录成功获取权限以后，之后的查询都会依赖此时查询的结果集。如果修改了权限，对之前的用户也不会产生影响，只对新连接产生影响。也可以手动刷新权限

维持和管理连接

连接创建完成以后就处于空闲状态

如果客户端空闲时间太长，mysql会断开连接

mysql空闲时间设置参数

wait_timeout

默认8小时

解决方案

将wait_timeout时间延长

不推荐

客户端每隔一段时间发送一次查询语句维持连接

不推荐

采用数据库连接池

原理

数据库连接池会创建指定数量的连接，并且维护在链表中

数据库连接池每隔6小时自动检测空闲连接

如果存在空闲连接则关闭

如果剩余连接不够指定数量

则创建的新的连接

查询缓存

执行流程

以sql语句作为key去查询缓存

precheck 验证权限

验证失败，不能查询

命中缓存则返回查询结果（效率极高）

未命中缓存则继续向下执行

缺陷

查询缓存的失效非常频繁

只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

sql语句发生更改，则查不到对应的缓存

解析器

词法解析

MySQL 需要识别出sql语句里面的字符串分别是什么，代表什么。

例如

”select“识别成查询语句

“T”识别成“表名 T”

把字符串“ID”识别成“列 ID”。

语法解析

判断你输入的这个 SQL 语句是否满足 MySQL 语法

根据sql语句生成语法树

检测关键字是否缺失，表明和列名是否存在

sql语句错误

You have an error in your SQL syntax

优化器

有多个索引的时候，决定使用哪个索引

语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

执行sql语句

判断当前连接是否具有权限

无权限

返回没有权限错误

查看表的存储引擎，调用引擎的接口查询数据

无索引的情况

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

有索引的情况

第一次调用的是“取满足条件的第一行”这个接口

之后循环取“满足条件的下一行”这个接口

存储引擎

引擎类型

InnoDB

支持行锁

支持事务

Myisam

支持表锁

不支持事务

Memory

指定引擎

create table 语句中使用 engine=memory

查询执行原理

基础组件模块从上往下执行即为查询的执行流程

mysql查询执行原理

日志系统

undo log 日志

redo log日志（重做日志）

作用

crash-safe

数据库发生异常重启，之前提交的记录都不会丢失

原理

InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB

redo log 日志具有两个指针，write pos 和 check point。

write pos

一开始write pos在四个文件的最开始

当写入redo log 日志时，write pos就往后开始移动

check point

一开始check point在文件最末尾的位置

当触发刷盘操作时（redo log 日志写入到磁盘中），check point指针朝有记录的方向开始移动

redo log三种状态

图片

MySQL redo log 存储状态

状态：

存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；（速度很快）

写到磁盘 write（速度很快），但是没有持久化fsync，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；

持久化到磁盘fsync（速度很慢），对应的是 hard disk，也就是图中的绿色部分。

redo log写入策略

控制参数

InnoDB 提供了 innodb_flush_log_at_trx_commit 参数

参数取值

设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；（redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。）

设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

redo log 写入磁盘的场景

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。

并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

组提交机制（group commit）

日志逻辑序列号（log sequence number，LSN）

LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

组提交过程

图片：redo log 组提交

步骤

trx1 是第一个到达的，会被选为这组的 leader；

等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；

trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；

这时候 trx2 和 trx3 就可以直接返回了。（所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好）

Mysql优化组提交机制

图片：两阶段提交细化

redo log fsync 在binlog执行write之后，拖延fsync的时间，让更多成员进入该组

binlog fsync也延迟到redo log fsync后，这样binlog也可以组提交，不过，因为redo log fsync的速度很快，所以，binlog日志的组提交效果较差

binlog组提交优化

binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。

binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

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

设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。

将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

binlog日志

binlog cache

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

binlog 的写入机制

图片

binlog 写盘状态(每个线程有自己 binlog cache，但是共用同一份 binlog 文件。)

步骤

事务执行过程中，先把日志写到 binlog cache

事务提交的时候，再把 binlog cache 写到 binlog 文件中

sync_binlog=0 的时候，表示每次提交事务都只文件系统的page cache中（速度很快）

sync_binlog=1 的时候，表示每次提交事务都会执行 fsync（将数据持久化到硬盘，效率较低）；

sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

清空binlog cache里面的内容

binlog日志格式

statement

案例展示

statement 格式 binlog 示例

BEGIN和COMMIT表示中间开启了事务；xid是mysql表隐式的字段，用于redo log和binlog日志之间的对应

use `test`；表示选择某个数据库，这样在同步数据的时候能找到对应的数据库。

statement格式下存储了完整的sql语句，包括备注

statement格式存在的问题

在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。导致主备数据不一致

案例

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

因为 delete 带 limit，很可能会出现主备数据不一致的情况。

如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；

但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

row

案例展示

row 格式 binlog 示例

BEGIN和COMMIT表示中间开启了事务；xid是mysql表隐式的字段，用于redo log和binlog日志之间的对应

Table_map event，用于说明接下来要操作的表是 test 库的表 t;

Delete_rows event，用于定义删除的行为。

insert

row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了。

delete

row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。

update

binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了。

mixed

为什么会有 mixed 格式的 binlog？

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。比如我们这个例子，设置为 mixed 后，就会记录为 row 格式；而如果执行的语句去掉 limit 1，就会记录为 statement 格式。

案例

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

mixed格式下采用的是statement格式存储

如果隔了一段时间存储，对now函数是没有影响的，因为在写日志的时候将时间戳也写进去了，不管备库多久执行，写入的都会是同一个时间，但是，如果运维人员直接将binlog日志拿去执行，则会产生数据不一致的问题、

用 binlog 来恢复数据的标准做法

用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。

binlog写入时的注意事项

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入

日志

redo_log和binlog日志的区别

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

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

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

两段提交协议

作用

保证redo log 日志和binlog日志逻辑保持一致

例子

写完redo log后发生crash，binlog日志还没写入

在进行数据恢复时，使用binlog日志恢复就差了一条数据

先写binlog日志，发生crash，redo log 日志还没写入

在进行数据恢复时，使用redo log 日志恢复便差了一条数据

写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash）

由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。

原理

先写入redo_log日志，redo_log状态为prepare

生成binlog并写入磁盘

将redo_log状态更改为commit

崩溃恢复时的判断规则

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

如果是，则提交事务；

否则，回滚事务。

redo log 和 binlog 是怎么关联起来的

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

MySQL 怎么知道 binlog 是完整的?

一个事务的 binlog 是有完整格式的：

statement 格式的 binlog，最后会有 COMMIT；

row 格式的 binlog，最后会有一个 XID event。

在 MySQL 5.6.2 版本以后，还引入了 binlog-checksum 参数，用来验证 binlog 内容的正确性。对于 binlog 日志由于磁盘原因，可能会在日志中间出错的情况，MySQL 可以通过校验 checksum 的结果来发现。

redo log 一般设置多大？

redo log 太小的话，会导致很快就被写满，然后不得不强行刷 redo log，这样 WAL 机制的能力就发挥不出来了。

所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

更新执行原理

WAL 技术

全称：Write-Ahead Logging

作用：先写日志，再写磁盘

执行流程

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

记录undo_log日志

对于insert操作，undo_log日志记录其主键id，恢复时按照id删除即可。

对于delete/update操作，undo_log日志记录原来的row数据，回滚时直接恢复

执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

Mysql特性

Atomicity（原子性）

一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。

Consistency（一致性）

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。

Isolation（隔离性）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务

事务的隔离级别

产生的原因

解决脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

脏读

读到其他事务未提交的数据；

不可重复读

前后读取的记录内容不一致；

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

说明

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。

上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

产生的条件

必须是当前读，不能是快照读（reason：可重复读级别下，普通查询为快照读，根据undo log可见性规则，是看不到其他事务插入的数据）

幻读案例一

描述

图 1 假设只在 id=5 这一行加行锁（for update会使查询语句从快照读变成当前读）

分析流程

可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。

Q1 只返回 id=5 这一行；

在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；

在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。（仅SessionC插入的这一一行称为幻读）

幻读案例二

描述

假设只在 id=5 这一行加行锁 -- 数据一致性问题

分析流程

事务执行的结果，记录的日志如下
sessionB
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

sessionC
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/

sessionA
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

幻读导致的问题

语义被破坏

从案例中分析，sessionA中是要将d=5的行都加上行锁，但是sessionB和sessionC后面又更新或插入其他行使d=5，但没有被锁住，这样语义就破坏了

数据一致性的问题

正常情况下只有sessionA的数据会被修改为100，但，出现幻读的情况下，sessionB和sessionC的数据也会被改成100

幻读解决方案

方案一

间隙锁与next-key lock

方案二

读提交隔离级别（rc） + binlog日志格式为row

间隙锁

定义

两条记录之间的间隙，使用间隙锁锁住以后，往这个间隙里面插入记录都会阻塞

next-key lock

间隙锁和行锁合称

间隙锁与next-key lock带来的问题

产生死锁问题

间隙锁导致的死锁

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

加锁规则

原则 1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

案例表结构与数据

案例一

等值查询的间隙锁

案例二

只加在非唯一索引上的锁

案例三

描述

对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？

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

主键索引上范围查询的锁

案例四

非唯一索引范围锁

案例五

唯一索引范围锁的 bug

案例六

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

描述：给表 t 插入一条新记录
mysql> insert into t values(30,10,30);

delete 示例

这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。

然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

加锁效果示例

案例七

limit 语句加锁，表 t 里 c=10 的记录有两条

这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。

这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如图所示：

案例八

操作序列

读未提交（read uncommitted）

一个事务还没提交时，它做的变更就能被别的事务看到。

读提交（read committed）

一个事务提交之后，它做的变更才会被其他事务看到。

可重复读（repeatable read）

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

串行化（serializable ）

“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MVCC多版本并发控制1

全称：Multi-Version Concurrency Control

作用

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

技术概念提前引入

当前读

读的时候需要加锁

快照读

不加锁的非阻塞读

实现原理

隐式字段

DB_TRX_ID

6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

DB_ROLL_PTR

7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）

DB_ROW_ID

6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

undo log日志

undo log日志种类

insert undo log

代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

记录的是insert记录的主键id

update undo log

事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

记录的是

undo log 日志存储原理

比如一个有个事务插入 persion 表插入了一条新记录，记录如下，name 为 Jerry , age 为 24 岁，隐式主键是 1，事务 ID和回滚指针，我们假设为 NULL

现在来了一个事务 1对该记录的 name 做出了修改，改为 Tom

在事务 1修改该行(记录)数据时，数据库会先对该行加排他锁

然后把该行数据拷贝到 undo log 中，作为旧记录，既在 undo log 中有当前行的拷贝副本

拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务 ID 为当前事务 1的 ID, 我们默认从 1 开始，之后递增，回滚指针指向拷贝到 undo log 的副本记录，既表示我的上一个版本就是它

事务提交后，释放锁

又来了个事务 2修改person 表的同一个记录，将age修改为 30 岁

在事务2修改该行数据时，数据库也先为该行加锁

然后把该行数据拷贝到 undo log 中，作为旧记录，发现该行记录已经有 undo log 了，那么最新的旧数据作为链表的表头，插在该行记录的 undo log 最前面

修改该行 age 为 30 岁，并且修改隐藏字段的事务 ID 为当前事务 2的 ID, 那就是 2 ，回滚指针指向刚刚拷贝到 undo log 的副本记录

事务提交，释放锁

Read View 读视图

定义

Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)

作用

可见性判断

当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

当前执行快照读的事务能否看见最后一次事务修改的数据，如果不能，则去找最新版本或者历史版本

具有的属性

trx_list

一个数值列表

用于维护 Read View 生成时刻系统正活跃的事务 ID 列表，即begin以后，还没有commit和rollback的事务

up_limit_id（lower water remark）

是 trx_list活跃事务列表中最小的 ID

low_limit_id（hight water mark）

ReadView 生成时刻系统尚未分配的下一个事务 ID ，也就是目前已出现过的事务 ID 的最大值 + 1

MVCC原理

首先比较 DB_TRX_ID < up_limit_id , 如果小于，则当前事务能看到 DB_TRX_ID 所在的记录，如果大于等于进入下一个判断

接下来判断 DB_TRX_ID >= low_limit_id , 如果大于等于则代表 DB_TRX_ID 所在的记录在 Read View 生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断

判断 DB_TRX_ID 是否在活跃事务之中，trx_list.contains (DB_TRX_ID)，如果在，则代表我 Read View 生成时刻，你这个事务还在活跃，还没有 Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在 Read View 生成之前就已经 Commit 了，你修改的结果，我当前事务是能看见的

MVCC多版本并发控制2（极客时间版）

事务的启动时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动

一致性视图是在第一个select快照读语句时创建

start transaction with consistent snapshot命令，马上启动一个事务

一致性视图在执行语句时创建

没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。

属性

事务 ID（transaction id）

是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的

row trx_id

每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID

活跃事务列表

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

低水位

数组里面事务 ID 的最小值记为低水位

高水位

当前系统里面已经创建过的事务 ID 的最大值加 1

案例一

事务的执行流程图

undo_log日志记录图

假设当前存在活跃事务，事务ID为90，事务A的ID为100，事务B的ID为101，事务C的ID为102

事务的执行结果为：事务A查询到的值为1，事务B查询到的值为3

事务B查询的值为3详解

在事务C执行之前，数据版本为（1，1）90

事务C（102）没有显示的开启事务，所以在执行update语句时，加行锁，开启事务，将数据修改为（1，2）102

事务B在事务C之前开启事务，但行锁的是在执行update语句时加上的，并且，在执行update语句时，会执行当前读（读取数据的最新值，如果不读取最新的结果，则k=1，事务B的执行结果为（1，2），这样事务C的更新就丢失了），即事务C更新后的值为2，所以，事务B执行的结果为3，在查询时，读取到的也是自己修改的结果，所以k=3，当前数据版本为（1，3）101

在事务A开启时，创建一致性视图，此时的活跃事务列表为【90， 100】，低水位为90，高水位为101，由于事务B执行了更新操作，当前row trx_id = 101 >= 101（高水位），所以该版本的修改对当前事务不可见，则沿着undo_log日志找上一个版本（1，2）row_trx>=101(高水位)，该版本也对当前事务不可见，再往前走为：（1，1）90，rox_trx_id 大于90(低水位)，并且90在活跃事务列表中，可以查看该版本

事务可见性判断

数据版本可见性规则

版本未提交，不可见；数据版本的事务ID

版本已提交，但是是在视图创建后提交的，不可见；

版本已提交，而且是在视图创建前提交的，可见。

执行快照读之前，如果该事务执行了修改操作（update），该事务之后读取到的都是最新结果

事务的更新逻辑

事务B的更新逻辑

事务C开启了事务，创建了当前数据版本，但是没有提交事务

事务B在进行更新时发现id=1的行已经被其他事务上锁，就进行等待，直到事务C执行结束，事务B才能继续执行当前读获取最新的（1，2）数据

sql优化

count(*)优化

效率排序

按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)

count(主键 id)

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

count(1)

InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(字段)

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*)

不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

order by 优化

explain 命令来看看这个语句的执行情况

Using filesort

表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

Using index

表示的就是使用了覆盖索引，性能上会快很多。

Using temporary

表示的是需要使用临时表

全字段排序过程

索引为city的情况下，进行查询和排序

初始化 sort_buffer，确定放入 name、city、age 这三个字段；

从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；

对 sort_buffer 中的数据按照字段 name 做快速排序；

按照排序结果取前 1000 行返回给客户端。

在sort buffer大小不足时，会采用外部文件辅助排序

部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。

rowid 排序

rowid排序过程

初始化 sort_buffer，确定放入两个字段，即 name 和 id；

从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；

从索引 city 取下一个记录的主键 id；

重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

对 sort_buffer 中的数据按照字段 name 进行排序；

遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

rowid排序原理

将需要的条件字段字段和排序字段放入sort buffer中进行排序，排序完成以后，其他字段再从主键索引树中获取

缺点

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

如何让mysql使用该算法

SET max_length_for_sort_data = 16;

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

全字段排序 VS rowid排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

联合索引排序

索引包含查询条件和排序条件

案例

在这个市民表上创建一个 city 和 name 的联合索引，alter table t add index city_user(city, name);

从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；

到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；

从索引 (city,name) 取下一个记录主键 id；

重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

联合索引优化

将联合索引升级成覆盖索引，这样不用回表查询，效率更高

缺点

覆盖索引需要维护的索引文件更多

join优化

条件函数优化

需求1

假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。

走全索引扫描的方案

mysql> select count(*) from tradelog where month(t_modified)=7;

优化方案

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

需求2

优化器“偷懒”行为

对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。

优化方案

select * from tradelog where id = 10000 -1

隐式类型转换优化

描述

mysql> select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

优化

mysql> select * from tradelog where tradeid='110717';

Mysql字符串与数字转换规则

“将字符串转成数字”

以上面的案例为例，Mysql实际执行为：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
这里还是走了条件函数的坑，导致不能走索引搜索

换一种方式的情况下，就能执行
该情况下，id是数值类型
select * from tradelog where id="83126";

隐式字符编码转换

sql语句

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /*语句Q1*/

该语句执行情况为：tradelog表走了主键索引查询，而trade_detail走了全表扫描，因为两张表的tradeid字段的字符集不一致，前者为utf8，后者为uft8mb4（该字符集为uft8的超集）。在次情况下，作比较的时候，会将上述语句进行转换

mysql> select d.* from tradelog l, trade_detail d where CONVERT(traideid USING utf8mb4)=l.tradeid and l.id=2; /*语句Q1*/
在次情况下，又走了条件函数的坑，导致索引失败

优化方案

将两张表的字符集修改为相同的，或者用后表做驱动表，让条件函数转换发生在值这边

索引

目的

提高数据库查询的效率

可以实现的索引结构

Hash索引

优点

等值查询速度快

缺点

不支持做范围查询

有序数组

优点

查询效率高，支持等值查询和范围查询

缺点

更新数据的时候，后面所有的数据都需要移动位置，效率太低

二叉树

优点

支持等值查询和范围查询

缺点

极端情况下，二叉树会退化成为链表，查询效率极低

红黑树

优点

支持等值查询和范围查询

能够自动平衡，查询效率还可以

缺点

每个节点只能存储一条数据，数据很多的时候，树会特别高，导致查询效率降低

B树

优点

支持等值查询和范围查询

每个节点能够存储多个数据

查询效率较高

缺点

索引和数据没有分开，导致树还是比较高

B+树

优点

支持等值查询和范围查询

索引和数据分开，非叶子节点只存储索引，非叶子节点存储数据

树高很低，查询效率极高

索引类别

主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引

非主键索引

非主键索引的叶子节点内容是主键的值

主键索引和非主键索引图

主键索引和非主键索引查询的区别

主键索引

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

非主键索引

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

注意事项

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

回表

先搜索非主键索引树，获取到主键ID，再到主键索引树中查询到相应的记录的过程称为回表

案例

分析select * from T where k between 3 and 5的执行流程

InnoDB 的索引组织结构

在 k 索引树上找到 k=3 的记录，取得 ID = 300；

再到 ID 索引树查到 ID=300 对应的 R3；

在 k 索引树取下一个值 k=5，取得 ID=500；

再回到 ID 索引树查到 ID=500 对应的 R4；

在 k 索引树取下一个值 k=6，不满足条件，循环结束。

该搜索过程即是回表的过程

如何避免回表操作

采用覆盖索引查询

索引创建优化原则

覆盖索引

定义

索引字段覆盖查询字段，在非主键索引树中查询到结果即可返回，避免回表

描述

如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这
个目录就是起到覆盖索引的作用。

优点

覆盖索引可以减少树的搜索次数，显著提升查询性能

顺序优化

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

案例

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

最左前缀原则

定义

在依据索引查询时，如果没有按照索引顺序查询，会导致索引失效

使用越频繁的索引，放在最前面，复用性更高

案例

描述：该案例讲解最左前缀原则

题目：当表具有（id_card,name,age）的联合索引时，以下几种查询索引是否生效

查询语句为 select * from person where name ="张"时，因为该sql语句查询时没有按照id_card,name,age的顺序查询导致索引失效

查询语句为 select * from person where age ="10"时，因为该sql语句查询时没有按照id_card,name,age的顺序查询导致索引失效

查询语句为 select * from person where name = "张" and age ="10"时，因为该sql语句查询时没有按照id_card,name,age的顺序查询导致索引失效

查询语句为 select * from person where id_card like '5105%' and name = "张" and age ="10"时，按照id_card,name,age的顺序查询，索引生效

查询语句为 select * from person where id_card like '5105%' and name = "张"，按照id_card,name,age的顺序查询，索引生效

查询语句为 select * from person where id_card like '5105%'时，按照id_card,name,age的顺序查询，索引生效

空间优化

比如上面这个市民表的情况，name 字段是比 age 字段大的，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

出现版本

Mysql5.6之前无索引下推优化

Mysql5.6以后有索引下推优化

作用

索引下推优化是指，联合索引查询时，以(name,age)联合索引为例，在查询到name符合条件以后，还要判断age是否符合条件才回去主键索引中查找记录，减少回表次数

案例

无索引下推优化图

有索引下推优化图

查询过程

普通索引

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

唯一索引

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

查询比较

普通索引相比于唯一索引，会多执行查找下一个记录的操作，但是，将数据读入内存是按照页读取，查找下一个记录都在内存中进行，效率很高

更新过程

知识补充

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，再执行merge操作进行落盘。通过这种方式就能保证这个数据逻辑的正确性。

大小设置

innodb_change_buffer_max_size

这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

效率提高的原因

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

使用场景

写多读少的业务

不适合的场景

写完数据立马就要查询，该情况下应该关闭change buffer

适用的索引

普通索引

redo_log与change buffer的优点

redo_log日志是将写入磁盘时的随机IO优化为顺序写入IO（性能提升）

change buffer减少的是磁盘读取IO（此处指的是写入数据时，通常需要先读取数据，再进行写入，而此处时直接写入change buffer中即可。这样减少了写入时的磁盘IO）

merge

定义

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge

触发merege的方式

访问这个数据页会触发 merge

系统有后台线程会定期 merge

数据库正常关闭（shutdown）的过程中，也会执行 merge 操作

目标页在内存中

唯一索引

找到 3 和 5 之间的位置

判断到没有冲突，插入这个值

语句执行结束；

普通索引

找到 3 和 5 之间的位置，插入这个值

语句执行结束。

目标页不在内存中

唯一索引

需要将数据页读入内存

判断到没有冲突，插入这个值

语句执行结束；

普通索引

将更新记录在 change buffer

语句执行就结束；

change buffer的更新过程

change buffer 的更新过程

四个部分

内存

redo log（ib_log_fileX）

数据表空间（t.ibd）

系统表空间（ibdata1）

mysql> insert into t(id,k) values(id1,k1),(id2,k2);
这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 2 所示是带 change buffer 的更新状态图。

执行流程

Page 1 在内存中，直接更新内存；

Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息

将上述两个动作记入 redo log 中（顺序IO写入）。到此，更新数据就结束了

后台线程会将数据merge到磁盘中（图中的两个虚线箭头，是后台操作，不影响更新的响应时间。）

change buffer 查询过程

数据的查询过程

读 Page 1 的时候，直接从内存返回

要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

锁

全局锁

定义

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

使用场景

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

缺点

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

更好的备份方式

InnoDB等支持事务的引擎，可以将事务的隔离级别设置成可重复读，即可备份数据，如果不支持事务的引擎，备份数据就只能使用全局读锁

表锁

语法

加锁

lock tables … read

lock tables … write

解锁

unlock tables

客户端断开连接也能解锁

缺点

锁粒度太大，导致并发度不高

元数据锁（meta data lock，MDL)

定义

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。

场景

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

修改表结构由MDL锁引发的问题

表t

如何安全地给小表加字段

非热点表

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

热点表

这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

表锁由Mysql实现

行锁

定义

行锁就是针对数据表中行记录的锁。

案例

比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新。

实现

MySQL 的行锁是在引擎层由各个引擎自己实现的

支持行锁的引擎

InnoDB

不支持行锁的引擎

MyISAM

优点

锁粒度更细，业务并发度更高

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

案例

多个线程更新记录图

行锁优化

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

案例

电影院案例

死锁

定义

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

案例

数据库死锁图

死锁解决方案

方案一

直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

缺点

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

方案二

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

如果对同一行数据有大量线程进行更新也会降低并发度，每一个更新的线程都需要做死锁检测，消耗大量的资源

解决方案

确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

问题：关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

控制并发度

比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题

如果你有中间件，可以考虑在中间件实现；

你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。

间隙锁

子主题

主从同步

前导知识

主节点

主要做写入数据的节点，同时，会将数据通过binlog日志同步给备节点和从节点

备节点

做主备切换的节点，当主节点故障宕机，备节点主动切换为主节点

从节点

主要做查询的节点

主备切换

主备切换流程

MySQL 主备切换流程（在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。）

将备库设置成readonly=ture的原因

有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；

防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；

可以用 readonly 状态，来判断节点的角色。

备库设置成只读了，还怎么跟主库保持同步更新呢？

readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。

主备数据同步

同步过程

图片：主备流程图

在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。（建立的是长连接）

主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，如果这个文件现在还在 page cache中，直接读走，发给 B。如果不在page cache里，就只好去磁盘读取以后，再发送给B

备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）

sql_thread 读取中转日志，解析出日志里的命令，并执行。（由于多线程复制方案的引入，sql_thread 演化成为了多个线程）

循环复制问题

图片：MySQL 主备切换流程 -- 双 M 结构

问题描述

节点 A 和 B 之间总是互为主备关系，这样在切换的时候就不用再修改主备关系。业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。然后再同步给节点A，造成循环复制

解决方案

MySQL 在 binlog 中记录了这个命令第一次执行时所在实例的 server id

规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；

一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；

每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

如果在中途有人将server id修改，还是会造成循环复制问题

解决方案案例

从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；

传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；

再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了。

主备延迟

同步延迟

主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;

之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;

备库 B 执行完成这个事务，我们把这个时刻记为 T3。

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是 T3-T1。

查看主备延迟时间

你可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。这个值的时间精度是秒。

seconds_behind_master 的计算方法

每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；

备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。

如果主备库机器的系统时间设置不一致，会不会导致主备延迟的值不准？

其实不会的。因为，备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值。

主备延迟的来源

第一种情况：有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

第二种情况：即备库的压力大。

解决方案

一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。

通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

第三种情况：

大事务

因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。

解决方案

不要一次性地用 delete 语句删除太多数据

经典大事务场景：大表 DDL

子主题

处理方案就是，计划内的 DDL，建议使用 gh-ost 方案

第四种情况：备库的并行复制能力

工作中主备延迟的场景

主库做大量的dml操作，引起延迟

主库有个大事务在处理，引起延迟

对myisam存储引擎的表做dml操作，从库会有延迟。

利用pt工具对主库的大表做字段新增、修改和添加索引等操作，从库会有延迟。

主备切换策略（解决主备延迟问题）

可靠性优先策略

流程

图片：MySQL 可靠性优先主备切换流程（图中的 SBM，是 seconds_behind_master 参数的简写。）

判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；

把主库 A 改成只读状态，即把 readonly 设置为 true；

判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；

把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

把业务请求切到备库 B。

如不理解，请看备注

异常情况

图片：可靠性优先策略，主库不可用

可用性优先策略

binlog日志为mixed格式下的切换流程

图片：可用性优先策略，且 binlog_format=mixed（id是主键，且能自增）
假设，现在主库上其他的数据表有大量的更新，导致主备延迟达到 5 秒。在插入一条 c=4 的语句后，发起了主备切换。

步骤 2 中，主库 A 执行完 insert 语句，插入了一行数据（4,4），之后开始进行主备切换。

步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用“插入 c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。

步骤 4 中，备库 B 插入了一行数据（4,5），并且把这个 binlog 发给主库 A。

步骤 5 中，备库 B 执行“插入 c=4”这个中转日志，插入了一行数据（5,4）。而直接在备库 B 执行的“插入 c=5”这个语句，传到主库 A，就插入了一行新数据（5,5）。

binlog日志为row格式下的切换流程

图片：可用性优先策略，且 binlog_format=row

步骤 2 中，主库 A 执行完 insert 语句，插入了一行数据（4,4），之后开始进行主备切换。

步骤 3 中，由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用“插入 c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。

步骤 4 中，备库 B 插入了一行数据（4,5），并且把这个 binlog 发给主库 A。

步骤5中，主库A插入4.5这条数据时，主键重复报错，备库B插入relay log中的4，4数据，也会出现主键重复报错。

可靠性优先策略与可用性优先策略对比

使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。

主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

可用性优先策略场景

场景描述

有一个库的作用是记录操作日志。这时候，如果数据不一致可以通过 binlog 来修补，而这个短暂的不一致也不会引发业务问题。

同时，业务系统依赖于这个日志写入逻辑，如果这个库不可写，会导致线上的业务操作无法执行。

优化方案

让业务逻辑不要依赖于这类日志的写入。也就是说，日志写入这个逻辑模块应该可以降级，比如写到本地文件，或者写到另外一个临时库里面。

这种场景就又可以使用可靠性优先策略了。

主备切换方式

基于位点的主备切换

当我们把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：

GTID模式主备切换

定义

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。

格式

GTID=server_uuid:gno

启动GTID模式

需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on

GTID生成方式

如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。

a. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;

b. 把这个 GTID 加入本实例的 GTID 集合。

如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：

a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；

b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。

GTID使用方式

每一个Mysql都会维护一个GTID列表current_gtid

每一个事务都会生成一个GTID

如果是以默认方式生成的，则添加到当前Mysql实例的current_gtid列表中，再同步给其他从节点、

如果是以指定的GTID生成的话，需要验证当前Mysql实力的current_gtid列表中是否含有该GTID，如果有的情况下，该GTID直接抛弃，对应的事务也不会执行，没有则进行添加

使用案例

GTID使用案例

图 4 初始化数据的 binlog

基于 GTID 的主备切换

读写分离导致的问题

什么是读写分离

写入数据都在主库写入，主库写入数据以后，再通过binlog日志同步到从库

查询数据都在从库查询

读写分离架构图

读写分离会导致什么问题

过期读

在主库写入数据以后，还未同步到从库，就开始查询，这样会导致在从库查询不到数据

读写分离的解决方案

强制走主库方案；

将请求做分类

对于必须要拿到最新结果的请求，强制将其发到主库上。
比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。

对于可以读到旧数据的请求，才将其发到从库上。
在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库。

存在问题

有可能请求量太大，把主库打挂

sleep 方案；

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

存在问题

如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；

如果延迟超过 1 秒，还是会出现过期读。

判断主备无延迟方案；

每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。

下面两种方案的使用图解

等主库位点方案；

处理流程

Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；

elay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。

如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。

等 GTID 方案。

处理流程

Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。

Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；

Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

如果这两个集合相同，也表示备库接收到的日志都已经同步完成。

等主库位点方案；等 GTID 方案。共同存在的存在问题

图 4 备库还没收到 trx3

trx1 和 trx2 已经传到从库，并且已经执行完成了；

trx3 在主库执行完成，并且已经回复给客户端，但是还没有传到从库中。

配合 semi-sync 方案；

解决方案

事务提交的时候，主库把 binlog 发给从库；

从库收到 binlog 以后，发回给主库一个 ack，表示收到了；

主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

并行复制策略减少主备延迟

coordinator 在分发的时候，需要满足以下这两个基本要求

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

同一个事务不能被拆开，必须放到同一个 worker 中。

如何判断Mysql机器是否正常

从业务代码出发

监控日志是否出现抛错，死锁等问题

从mysql服务出发

连接是否被占用满了，之后查询都失败

是否存在慢查询

是否存在事务没有释放

从机器状态出发

服务器是否挂了

磁盘IO速度异常

磁盘是否写满了

Mysql实践

字符串字段添加索引

前缀索引

作用

字符串字段做索引，只取前面的n个字符前缀用来作索引

优点

占用的空间少

缺点

可能会增加额外的记录扫描次数

优化

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

区分度

因为区分度越高（90%以上即可），意味着重复的键值越少

具体计算公式

count(distinct left(列名, 索引长度))/count(*)

前缀索引对覆盖索引的影响

使用了前缀索引，覆盖索引就失效了

前缀相同的字符串添加索引

将字符串反转以后再存入数据库

采用hash字段

在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

Mysql抖动问题（0写入）

前导知识点

脏页

当内存数据页跟磁盘数据页内容不一致

干净页

内存和磁盘上的数据页的内容一致

数据库flush过程

将脏页的内容写入到磁盘

触发数据库flush过程的场景

InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。checkpoint从cp移动到cp'，需要将中间日志记录的内容从内存flush到磁盘，write pos 到cp'之间的位置才是又可以写入的内容

对应的就是系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

MySQL 认为系统“空闲”的时候

MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

数据flush到磁盘的过程

redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。

在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

影响Mysql性能的因素

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略

策略一

设置innodb_io_capacity参数，告诉InnoDB 你的磁盘能力,这个值设置成磁盘的 IOPS

磁盘的 IOPS 可以通过 fio 这个工具来测试

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

刷脏页的速度控制