mysql底层

一 架构

查看连接器的连接：

抽象语法树AST：

1.1 词法分析器（Lexer）
词法分析器又称为Scanner，Lexical analyser和Tokenizer。程序设计语言通常由关键字和严格定义的语法结构组成。编译的最终目的是将程序设计语言的高层指令翻译成物理机器或虚拟机可以执行的指令。词法分析器的工作是分析量化那些本来毫无意义的字符流，将他们翻译成离散的字符组（也就是一个一个的Token），包括关键字，标识符，符号（symbols）和操作符供语法分析器使用。

1.2 语法分析器（Parser）
编译器又称为Syntactical analyser。在分析字符流的时候，Lexer不关心所生成的单个Token的语法意义及其与上下文之间的关系，而这就是Parser的工作。语法分析器将收到的Tokens组织起来，并转换成为目标语言语法定义所允许的序列。
无论是Lexer还是Parser都是一种识别器，Lexer是字符序列识别器而Parser是Token序列识别器。他们在本质上是类似的东西，而只是在分工上有所不同而已。如图1所示：

1.3 树分析器 (tree parser)
树分析器可以用于对语法分析生成的抽象语法树进行遍历，并能执行一些相关的操作。

1.4 ANTLR
ANTLR将上述结合起来，它允许我们定义识别字符流的词法规则和用于解释Token流的语法分析规则。然后，ANTLR将根据用户提供的语法文件自动生成相应的词法/语法分析器。用户可以利用他们将输入的文本进行编译，并转换成其他形式（如AST—Abstract Syntax Tree，抽象的语法树）。

1.5Rule Based Optimizer(RBO)基于规则

1.6Cost Based Optimizer(CBO)基于成本

myisam存储结构：

innodb存储结构：

InnoDB架构

总结：

$存储引擎可以理解为不同的数据文件在磁盘的不同组织形式$

表结构 ：frm 索引文件： idb

日志

binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

那 binlog 到底是用来干嘛的？

可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

binlog_format

statement格式

当binlog=statement时，binlog记录的是SQL本身的语句

ues `test`;delete from t where a>=4 and u_time<='2021-11-15' limit 1

特点：

（1）此模式不支持RU,RC隔离级别；

（2）binglog日志文件中上一个事物的结束点是下一个事物的开始点；

（3）DML，DDL语句都会明文显示；

问题：

（4）对一些系统函数不能准确复制或者不能复制，如load_file()、uuid()、user()、found_rows()、sysdate()，注意（now()可以复制； ）

（5）主库执行delete from t1 where c1=xxx limit 1，statement模式下，从库也会这么执行，可能导致删除的不是同一行数据

（6）主库有id=1和id=10两行数据，从库有id=1,2,3,10这四行数据，主库执行delete from t1 where id<10命令，从库删除过多数据；

（7）同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

row模式

Table_map    |   table_id: 226(test.t)
Delete_rows  |   table_id: 226 flags: STMT_END_F

（1）相对statement更加安全；

（2）在表有主键的情况下复制更加快；

（3）系统的特殊函数也能复制；

（4）更少的锁，只有行锁；

（5）binlog文件比较大，如单语句更新20万行数据，可能要半小时，也有可能把主库跑挂；

（6）无法从binog看见用户执行的SQL语句（mysql 5.6后通过设置binlog_rows_query_log_events=on，日志格式为row中的binlog日志中看到执行过得SQL语句。）

（7）5.7默认的日志模式为row；

（8）DDL语句明文显示，DML语句加密显示；

（9）DML经过base64加密，需要使用参数–base64-output=decode-rows --verbose；

（10）update修改的语句可以看到历史旧数据；

mixed模式

特点：

（1）innodb引擎，如果隔离级别是RU、RC，则mixed模式会转成Row模式存储；

（2）mixed模式下，在以下几种情况会自动将binlog的模式有SBR转化成RBR模式：

写入机制
binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

binlog日志刷盘流程如下

redolog

硬盘上存储的redo log日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

比如可以配置为一组4个文件，每个文件的大小是1GB，整个redo log日志文件组可以记录4G的内容。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在个日志文件组中还有两个重要的属性，分别是write pos、checkpoint

write pos是当前记录的位置，一边写一边后移
checkpoint是当前要擦除的位置，也是往后推移
每次刷盘redo log记录到日志文件组中，write pos位置就会后移更新。

每次MySQL加载日志文件组恢复数据时，会清空加载过的redo log记录，并把checkpoint后移更新。

write pos和checkpoint之间的还空着的部分可以用来写入新的redo log记录。

如果write pos追上checkpoint，表示日志文件组满了，这时候不能再写入新的redo log记录，MySQL得停下来，清空一些记录，把checkpoint推进一下。

undolog

概念：
undo log是innodb引擎的一种日志，在事务的修改记录之前，会把该记录的原值（before image）先保存起来（undo log）再做修改，以便修改过程中出错能够恢复原值或者其他的事务读取。

作用：

1 事务回滚 - 原子性： undo log是为了实现事务的原子性而出现的产物，事务处理的过程中，如果出现了错误或者用户执行ROLLBACK语句，MySQL可以利用undo log中的备份将数据恢复到事务开始之前的状态。

2 多个行版本控制（MVCC）- 隔离性： undo log在MySQL InnoDB储存引擎中用来实现多版本并发控制，事务未提交之前，当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

什么时候会生成undo log
在事务中，进行以下四种操作，都会创建undo log：

1 insert用户定义的表
2 update或者delete用户定义的表
3 insert用户定义的临时表
4 update或者delete用户定义的临时表

insert 类型的undo log

update类型的undolog

update类型回滚：通过DATA_ROLL_ID指针找到上一个事务版本

二 主从复制

binlog复制

mysql的主从复制base on binlog

大概步骤分为 写binlog-复制binlog–恢复数据

MySQL 主从复制概念
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

MySQL 主从复制主要用途

1 读写分离
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2 数据实时备份，当系统中某个节点出现故障的时候，方便切换

3 高可用HA

4 架构扩展
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。

MySQL 主从复制原理
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:

1主节点 binary log dump 线程作用
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

2从节点I/O线程作用
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。

3 从节点SQL线程作用
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

此外 mysql提供三种模式的复制：

binlog记录模式
MySQL 主从复制有三种方式：
基于SQL语句的复制（statement-based replication，SBR），
基于行的复制（row-based replication，RBR)，
混合模式复制（mixed-based replication,MBR)

对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。

1 Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4 及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日志量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。

2 Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。

3 Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。

延迟复制的原因

分析：
主备库复制延迟的原因有哪些？

1 在某些线上环境中，备库的性能要比主库差，机器资源不足，会影响同步效率

2备库充当了读库，主库充当了写库，当备库的读压力较大时，可能或造成同步延迟

3 大事务执行，比如要执行10min，而binlog的写入要等执行完后，这就延迟了10min

4 主库的写binlog是顺序写，从库读binlog也是顺序读，mysql的主从同步都是单线程，但读写都是顺序进行，不太影响x效率，但是读回来后从relaylog恢复的时候是随机进行，这样会影响效率

5从库同步数据的时候，同其他读线程发生锁的抢占，会影响效率

6主库的tps较高的时候，产生的ddl超出一个线程的承受范围，效率降低

7还要考虑同步过程中的网络延迟

v5.6基于coordinate的库间多线程

上面分析了影响复制效率的瓶颈在sqlthread，那么怎么优化？

-可以开启多个sqlthread线程并行的恢复数据，如图

coordinator线程主要负责前两部分的内容：

若判断可以并行执行，那么选择worker线程执行事务的二进制日志。

若判断不可以并行执行，如该操作是DDL，亦或者是事务跨schema操作，则等待所有的worker线程执行完成之后，再执行当前的日志。

这意味着coordinator线程并不是仅将日志发送给worker线程，自己也可以回放日志，但是所有可以并行的操作交付由worker线程完成。coordinator线程与worker是典型的生产者与消费者模型。

5.6版本存在的问题

上述机制实现了基于schema的并行复制存在两个问题:

1 crash safe功能不好做，因为可能之后执行的事务由于并行复制的关系先完成执行，那么当发生crash的时候，这部分的处理逻辑是比较复杂的。从代码上看，5.6这里引入了Low-Water-Mark标记来解决该问题，从设计上看(WL#5569)，其是希望借助于日志的幂等性来解决该问题，不过5.6的二进制日志回放还不能实现幂等性。

2 另一个最为关键的问题是这样设计的并行复制效果并不高，如果用户实例仅有一个库，那么就无法实现并行回放，甚至性能会比原来的单线程更差。而单库多表是比多库多表更为常见的一种情形。

如何设置多线程？

-配置slave_parallel_workers数量

查看workers的状态：

多线程就会有顺序的问题，如何保准顺序？

1不能造成更新覆盖，同一行的多个操作，由一个worker执行
2 同一个事务不能拆开，必须放到同一个worker中

遵循上面的两条原则，可以解决顺序的问题，具体如何操作？

给每一个worker定义一个hash表，用来保存当前worker正在执行的事务涉及到的表，hash表的key按照不同的粒度进行细分：
按库分发-key为数据库标识
按表分发-dbname+tablename
按行分发-dbname+tablename+唯一键

从上图可以看出，每个 Worker 都对应一个 hash 表，用于保存当前 Worker 执行的 binlog 涉及的表。hash 表的 key 是 “库名+表名”，value 表示 Worker 中有多少个事务操作这个表。当事务执行完成后，其所涉及的表的计数会从hash 表中移除。上面 Worker_1 中的hash 表中 db1.t1:4 ，表示：Worker_1 中修改 db1.t1 表的事务数有 4 个。

假设 coordinate 读取一个事务 T （涉及 t1 和 t3的改动），此时分配规则如下：

Worker_1 中有事务在处理 t1 的改动，此时和 Worker_1 是冲突的，对于Worker_2 有在处理 t3 的改动，此时和Worker_2 也是冲突的；

当事务 T 和多于 1 个Worker 冲突时，coordinate 就进入等待状态；

此时如果 Worker_2 中执行完事务后，对应 t3 的计数就会减 1，此时事务 T 就只和 Worker_1 冲突了，此时就会将其加入到 Worker_1 的队列中；

coordinate 读取新事务 T2 继续执行 步骤 1 。

总结以上，coordinate 在分发事务的时候，会考虑以下冲突情况：

1 当没有 Worker 冲突的时候，会将其加入到空间的Worker 中去；

2 当有只有 1 个Worker 冲突的时候，会将其加入到冲突的Worker 中去；

3 当有多于 1 个Worker 冲突的时候，coordinate 会进入等待状态，直到冲突数 <= 1。

v5.6.5 GTID组提交

组复制(group commit)：

通过对事务进行分组，优化减少了生成二进制日志所需的操作数。当事务同时提交时，它们将在单个操作中写入到二进制日志中。如果事务能同时提交成功，那么它们就不会共享任何锁，这意味着它们没有冲突，因此可以在Slave上并行执行。所以通过在主机上的二进制日志中添加组提交信息，这些Slave可以并行地安全地运行事务。

​ 从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

​ 在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID = server_uuid:sequence_id

server_uuid是一个32字节+1字节(/0)的字符串，MySQL第一次启动时生成，并将该信息写入datadir目录下的auto.cnf文件。如果该文件丢失，MySQL会重新生成一个新的server_uuid。相同的server_uuid下的事务对应的sequence_id在binlog文件中是递增且连续有序的，他们以集合的方式呈现。

Gtid的作用

​ Gtid采用了新的复制协议，旧协议是，首先从服务器上在一个特定的偏移量位置连接到主服务器上一个给定的二进制日志文件，然后主服务器再从给定的连接点开始发送所有的事件。

​ 新协议有所不同，支持以全局统一事务ID (GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。

GTID带来的便利性

1）通过gtid定位该事务来自哪个实例

2）搭建主从复制不再需要指定binlog文件以及具体的位点信息，而是通过全局唯一的gtid来做复制，MySQL通过gtid来验证冲突并确保每个事务只会被执行一次。

Gtid的工作原理

①当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
②binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
③sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
④如果有记录，说明该GTID的事务已经执行，slave会忽略。
⑤如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
⑥在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

GTID是一种模式，需要开启

GTID = server_uuid:sequence_id
server_uuid是一个32字节+1字节(/0)的字符串

若没有开启GTID，mysql会设置一个匿名GTID，（兼容5.6版本）

v5.7的并行复制

了解并行复制，先了解两阶段提交


mysql两阶段提交

可以看到，InnoDB在写redo log时，并不是一次性写完的，而有两个阶段，Prepare与Commit阶段，这就是"两阶段提交"的含义。

为什么要写redo log，不写redo log的话，根本就不会出现“两阶段提交”的麻烦事啊？
先说结论：在于崩溃恢复。

看上面的图，总的来说，在崩溃恢复后，只要redo log不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。

在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与binlog的数据一致性

ordered commit流程介绍：

从图中可以看到，只要事务提交(调用ordered_commit)，就都会先加入队列中。而提交有三个步骤，包括FLUSH、SYNC及COMMIT，相应地也有三个队列。

flush-（binglog cache file）-sync-（磁盘）-提交事务

第一个接入队列的就是对长，对长给每个事务分配sequence_number，如果是第一个事务，则将这个组的last_committed设置为sequence_number-1.

将带着last_committed与sequence_number的GTID事件FLUSH到Binlog文件中。

将当前事务所产生的Binlog内容FLUSH到Binlog文件中。这样，一个事务的FLUSH就完成了。接下来，依次做完组内所有事务的FLUSH，然后做SYNC

每个阶段都有 leader、follower 两种角色。当一个事务进入三个阶段中的某一个阶段，如果发现这个阶段中的队列为空，那么这个事务就会成为 leader 的角色，之后进入同一阶段的事务，发现这个阶段的队列中已经有事务存在了，那就变成 follower 角色。leader 角色的任务是安排当前阶段队列中的事务按顺序执行，并且带领队列中所有的事务进入下一个阶段。当 leader 带领队列中的事务进入下一阶段的时候，如果发现下一阶段中已经有事务存在（即下一阶段已有 leader 存在），新来的 leader 自动变成 follower 角色。

MySQL等待binlog_group_commit_sync_delay毫秒直到达到binlog_group_commit_sync_no_delay_count事务个数时，将进行一次组提交

两阶段提交源码分析

这就是order commit的原理，这也是LOGICAL_CLOCK并行复制的基础。因为order commit使得所有的事务分了组，并且有了序列号，从库拿到这些信息之后，就可以根据序号放心大胆地做分发了。

知道了order commit原理之后，现在很容易可以想到在从库端是如何分发的，从库以事务为单位做APPLY的，每个事务有一个GTID事件，从而都有一个last_committed及sequence_number值，分发原理如下。

从库SQL线程拿到一个新事务，取出last_committed及sequence_number值。

判断当前last_committed是不是大于当前已经执行的sequence_number的最小值(low water mark，下面称lwm)。

如果大于，则说明上一个组的事务还没有完成。此时等待lwm变大，直到last_committed与lwm相等，才可以继续。

如果小于或等于，则说明当前事务与正在执行的组是同一个组，不需要等待。

SQL线程通过统计，找到一个空闲的worker线程，如果没有空闲，则SQL线程转入等待状态，直到找到一个为止

MariaDB 的并行复制：

在之前介绍的 redo log 组提交时，有以下特点：

1、在一个组里提交的事务，一定不会修改同一行；

2、主库上面可以并行的事务，在从库上面也是可以并行的。

MariaDB 并行复制的实现上，操作流程如下：

在一个组里面提交的事务有一个相同的 commit_id，下一个组就是 commit_id + 1；

commit_id 直接写到 binlog 里面；

传到备库的时候，相同 commit_id 的事务会被分发到不同的 Worker 中执行；

这一组执行完成后，再去取下一组重复以上步骤。

从上面流程可以看出，下一个组提交的执行依赖上一个组提交的执行完成。此时如果上一个组提交中有大事务，就会影响下一个组提交的执行，容易造成阻塞。

MySQL5.7 的并行复制

MySQL 5.7并行复制的思想简单易懂，一言以蔽之：一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的prepare阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

MariaDB 在实现了并行复制能力之后，MySQL 也提供了类似的功能。由 slave-parallel-type 参数来控制并行复制的策略：

配置为 DATABASE，表示使用 MySQL 5.6 开始提供的按库并行复制的策略；

配置为 LOGICAL_CLICK，表示就是使用类似于 MariaDB 并行复制策略。

对于 LOGICAL_CLICK 这种策略，MySQL 5.7 对其做了优化。说优化之前，我们先看一下之前提到的“事务两阶段提交的细化流程”。

上面提到的 MariaDB 并行复制的核心是：一个组内，已经提交的事务是可以并行的。但是从上面流程可以看出，只要 redo log prepare （第一步）完成之后，事务之间就已经完成冲突检测了。因此 MySQL 5.7 的优化思想如下：

同时处于 prepare 阶段的事务是可以并行执行的；

处于 prepare 阶段的事务与处于 commit 状态的事务之间，也是可以并行执行的。

前面在 binlog 组提交的时候，介绍了下面 2 个参数：

binlog_group_commit_sync_delay 参数：表示延迟多少个微妙之后，再执行 fsync；

binlog_group_commit_sync_no_delay_count 参数：表示累计多少次之后，再执行 fsync。

通过调整主服务器上的参数binlog_group_commit_sync_delay、binlog_group_commit_sync_no_delay_count。前者表示事务延迟提交多少时间来加大整个组提交的事务数量，从而减少进行磁盘刷盘sync的次数，单位为1/1000000秒，最大值1000000也就是1秒；后者表示组提交的事务数量凑齐多少此值时就跳出等待，然后提交事务，而无需等待binlog_group_commit_sync_delay的延迟时间；但是binlog_group_commit_sync_no_delay_count也不会超过binlog_group_commit_sync_delay设置。几个参数都是为了增加主服务器组提交的事务比例，从而增大从机MTS的并行度。

总结：一个组包含多个事务，事务之间因为两阶段提交保证了独立性，可以并行处理，基于GDID，确保每个事务只能被执行一次，可以将其交给多个线程处理

MySQL5.7.22 的并行复制

在 2018年4月发布的 5.7.22 版本里面，新增了基于 writeset 的并行复制。新增了 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略，这个参数有以下 3 个可选值：

COMMIT_ORDER：相等于组提交，就是前面提到的处于 prepare 和 commit 状态的 binlog 都可以被分发到 Worker 上面处理；

WRITESET：对于事务更新的每一行，都计算出一个 hash 值，组成集合 writeset。如果两个事务的 writeset 没有交集，说明事务没有操作相同的行，事务之间是可以并行的；

WRITESET_SESSION：是在 WRITESET 的基础上新增了一个约束，就是在主库上面同一个线程执行的 2 个事务的执行顺序，在从库上面也需要保证顺序性。

可以看出，MySQL 5.7.22 提出的并行复制策略和之前说的按表、按行的并行复制原理类似。另外其还有一些优化点：

writeset 是在主库上面生成后直接写到 binlog 里面的，这样在备库执行时，就需要解析 binlog 的内容，节省了很多计算量；

不需要把整个事务的 binlog 都扫描一遍后，才决定分发到哪个 Worker，更节省内存；

由于备库的分发策略不依赖于 binlog 的内容，因此对 binlog 的格式没有要求。

当然上面所说的并行复制的前提都是，没有外键约束，所有表都有主键的场景。如果不满足，则会退化成单线程的模式

开启writeset：

binlog_transaction_dependency_tracking  = WRITESET                 #    COMMIT_ORDER
transaction_write_set_extraction        = XXHASH64

下面通过实例看下gtid和sriteset的区别：

四句insert被分到同一个组执行，提供并行效率

三 刷盘机制

MySQL 中数据是以页为单位，查询一条记录会将该条记录所在硬盘的数据页的全部数据加载到内存中，加载出来的数据会放入到 Buffer Pool 中。
后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，这种磁盘业加载的方式可以减少磁盘IO操作，提升速度。

在更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里。如果开启了binlog，还会记录binlog逻辑日志。

此后会根据配置，在一定条件下对日志文件和表数据文件进行刷盘。通常在一次事务中，只有redo log和binlog日志文件真正发生了写盘操作，数据文件则不会。

数据从内存持久化到磁盘，也叫刷盘，通常并不是直接写入磁盘中的，通常需要经历两步POSIX标准函数调用：

• flush：事务开始时，即开始记录binlog到binlog文件

• write：由应用程序调用POSIX file API 中的write()函数将数据从用户进程缓冲区（程序内存）写入磁盘，这一步write()函数实际上仅仅写入到了内核缓冲区（或者说磁盘映射内存os cache，或者说文件系统的page cache），这一块区域仍然属于文件系统向内核申请的一块的内存区域，并没有真正的把数据持久化到磁盘，所以速度是比较快的。

• fsync：调用POSIX file API 中的fsync()函数同步的将这些缓存数据真正的持久化到物理磁盘中，这一步完成之后，数据才算真正的完成持久化。一般情况下，我们认为fsync()才占磁盘的IOPS。

BufferPool 脏页以及刷盘机制

对于表数据的增删改查都是直接在Buffer Poll中进行的，并不会马上同步到磁盘中，而是有一定的机制最终落盘，
那么什么时候会触发刷脏？

1 innodb的redo log写满了，需要把checkpoint往前推进。这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。
2 一个查询buffer pool缓冲池内存不足时，会淘汰一些数据页缓存（最近最久未使用算法，LRU），有可能会淘汰到脏页缓存，此时就要先把脏页刷到磁盘。刷脏页一定会写盘，这是为了保证每个数据页有两种状态：1、内存里的一定是正确数据；2、内存里没有，磁盘上的一定是正确数据。
3 MySQL认为系统空闲时，会刷盘。当然系统繁忙时，也会见缝插针刷盘。
4 MySQL正常关闭时。

这里有几个参数：
innodb_page_cleaners–清理脏数据线程的个数，在mysql5.7中清理线程默认是4个。
innodb_max_dirty_pages_pct_lwm–脏页的最低水位，默认为0，非0时表示开启脏页预刷，当脏页达到此值时刷脏页就开始了

innodb_max_dirty_pages_pct—控制脏页在buffer pool中的百分比，默认是75，innodb就会强制刷脏页。

innodb_flush_neighbors–刷一个脏page时，是否刷这个page的整个extent。0表示关闭该功能，1表示刷extent中连续的pages（默认），2表示刷整个extent中的pages。当数据在普通盘上，开启该功能可以减少IO次数，而不是在不同的时间刷page，当数据在ssd上，IO情况较好，可以考虑关闭该功能，刷整个extent会比刷一个page更慢。mysql8.0中innodb_flush_neighbors的默认值为0

innodb_lru_scan_depth—在每个buffer pool子池中扫描LRU链的脏数据刷到磁盘的深度，找到的这些脏块就会flush

binlog刷盘时机

-flush-事务开始时同步记录binlog到binlog文件，然后才修改内存页
上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
-上图的 fsync，才是将数据持久化到磁盘的操作

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

0：不去强制要求，由系统自行判断何时写入磁盘；（write -> anytime fsync） linux的刷脏页机制

1：每次commit的时候都要将binlog写入磁盘；（commit ->write && fsync）

N：每N个事务，才会将binlog写入磁盘。(n*write -> fsync)

redolog刷盘机制

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中。

mysql支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下：

0（无延迟）事务提交不会将redo log buffer写入os buffer，而是每秒写入os buffer并调用fsync写入 redo log file中，也就是设置为零时每秒刷新写入磁盘，当系统崩溃，会丢失一秒钟的数据

1（实时写，实时刷）事务每次提交都将redo log buffer中的日志写入os buffer并调用fsync刷到redo log file中，这种方式及时系统崩溃，因为每次提交都写入磁盘，IO性能较差。

2（实时写，延迟刷）每次提交都写入os buffer，然后每秒调用fsync()将os buffer中的日志写入redo log file中。
(commit-> 1s -> write && fsync | commit->write && fsync | commit ->write -> 1s -> fsync)

总结：

redolog

checkpoint机制

checkpoin是重做日志对数据页刷新到磁盘的操作做的检查点，通过LSN号保存记录，

作用是当发生宕机等crash情况时，再次启动时会查询checkpoint点，在该检查点之后发生的事物修改恢复到磁盘。

InnoDB存储引擎内部，两种checkpoint，分别为:

Sharp Checkpoint

适用于数据库关闭时的一致性刷新 (数据库关闭时 触发)
即同步数据落盘，会阻塞写操作，影响系统的吞吐量。checkpoint范围内的所有的页面落盘的时间都是相同的即所有写操作完成之后此checkpoint才会完成。产生sharp checkpoint的时机：

1. 关闭数据库的时候，将buffer pool中的脏页全部刷新到磁盘中。

2. 日志文件写满，当 redo log 的使用量超过 90%。后续操作的日志无法写入，需要将一部分日志文件空间腾出来。

3. buffer pool的使用量超过90%时执行sharp checkpoint。

Fuzzy Checkpoint

异步数据落盘，本次checkpoint范围内的数据页的落盘时机点可能不同。不会影响系统的吞吐量。目的也是为了避免产生sharp checkpoint造成的性能问题。
适用于数据库运行时的部分页刷新 (数据库运行时周期性触发

Innodb中存在以下几种情况的fuzzy checkpoint：

• master thread checkpoint：

定时(差不多每秒或每十秒)异步地从缓冲池的脏页列表中刷新一定比例的页到磁盘

• flush_lru_list checkpoint：

将LRU列表尾端的页移除，Innodb要保证LRU列表中有足够的可用空闲页。在5.6中通过参数innodb_lru_depth来控制LRU列表中可用页的数量，默认是1024

mysql> show variables like ‘innodb_lru_scan_depth’;
±----------------------±------+ | Variable_name | Value |
±----------------------±------+ | innodb_lru_scan_depth | 1024 |
±----------------------±------+ 1 row in set (0.00 sec)

mysql>

• async/sync flush checkpoint

重做日志不可用时，需要强制将一些页刷新到磁盘。

• dirty page too much checkpoint

脏页太多，强制发生的检查点

mysql> show variables like ‘innodb_max_dirty_pages_pct’;
±---------------------------±------+ | Variable_name | Value |
±---------------------------±------+ | innodb_max_dirty_pages_pct | 75 |
±---------------------------±------+ 1 row in set (0.00 sec)

mysql>

脏页占75%时，强制刷新。

其他 dirty Page 参数：

innodb_max_dirty_pages_pct_lwm–低水位
innodb_max_dirty_pages_pct --缓冲池中脏页的百分比 90%
innodb_flush_neighbors–缓冲池中刷新一个页是否也会刷新相同范围内的其他脏页
innodb_lru_scan_depth–缓冲池LRU列出的页面清理器线程扫描的脏页面的深度
innodb_io_capacity–I/O容量

自适应刷脏页Adaptive Flushing

自适应刷脏页算法可以自动调节刷脏页的频率，频率取决于redo的生成和当前刷脏页的频率

该算法的目的就是让刷脏页的频率能赶上当前数据库的负载情况。自适应刷脏页会自动判断每秒应该刷多少脏页。
在mysql内部测试中发现，自适应刷脏页并不是在所有场景中都有性能提升，但是在redo log快满的时候，有较大的效果

Sharp checkpoints，在innodb想reuse一个log file时触发，会产生大量的redo条目，这些redo需要写到redolog中。logfile中之前的redo会被清空，当redo满后就会触发Sharp checkpoints，即时没有达到innodb_max_dirty_pages_pct的值。

innodb_adaptive_flushing
开启自适应刷脏页，默认开启。

innodb_adaptive_flushing_lwm
是redo log的最低水位（low water mark），当达到这个值，即时innodb_adaptive_flushing是关闭的，也会触发自适应刷脏页

innodb_flushing_avg_loops
保留之前flush动作的资源快照信息。innodb_flushing_avg_loops越高，说明保留的计算资源时间越长，自适应刷脏页也就越慢

总结
对于MySQL的checkpoint机制来说，是对io和内存做了平衡操作。
通过调节参数，对于不同的应用系统，都是提升性能的一种方式，普遍情况下采取默认方式。
另一个思路：重做日志可以无限增大，磁盘足够大，同时缓冲池足够大，能够缓存所有数据，那么就不需要将缓冲池中的脏页频繁刷新。

LSN

LSN概述

LSN(log sequence number) 用于记录日志序号，它是一个不断递增的 unsigned long long 类型整数。在 InnoDB 的日志系统中，LSN 无处不在，它既用于表示修改脏页时的日志序号，也用于记录checkpoint。通过LSN可以具体的定位到其在redo log文件中的位置。为了管理脏页，在 Buffer Pool 的 每个instance上都维持了一个flush list，flush list 上的 page 按照修改这些 page 的LSN号进行排序。因此在定期做redo checkpoint时，就可以快速找到flush list 上最老的那个page（拥有最小的LSN

查看LSN：

show engine innodb status\G;

log sequence number: 代表当前的重做日志redo log(in buffer)在内存中的LSN（redolog写到哪了）
log flushed up to: 代表刷到redo log file on disk中的LSN（redolog有多少写到log file了）
pages flushed up to: 代表已经刷到磁盘数据页上的LSN（本地log file 有多少fsync了）
last checkpoint at: 代表上一次检查点所在位置的LSN（上一次刷盘点？）

根据mysql持久化流程，更新一条记录时：

1 先写内存数据页面（内存的数据页面有LSN字段）
2 然后写缓冲内存的重做日志redo-log（内存的重做日志redo-log记录有LSN字段）
3 最后提交时innodb_flush_log_at_trx_commit写重做日志redo-log进磁盘文件（磁盘的redo-log日志记录有pageLSN字段）
简言之，LSN这个值在几个地方存储：

在数据页面(内存与磁盘）
存储LSN 在重做日志redo-log(内存)存储LSN
在重做日志redo-log(磁盘)存储LSN
Checkpoint对应的LSN也是存储在重做日志记录中

恰好对应上文:

mysql> show engine innodb status\G;

log sequence number: 代表当前的重做日志redo log(in buffer)在内存中的LSN
log flushed up to: 代表刷到redo log file on disk中的LSN
pages flushed up to: 代表已经刷到磁盘数据页上的LSN
last checkpoint at: 代表上一次检查点所在位置的LSN

问题1 MySQL怎么判断脏页？

基于数据页面的LSN，可以了解当前数据页面的版本。MySQL系统在更新记录时，会在对应的重做日志redo-log中更新LSN的值，与此同时记录的数据页面的头部也会记录redo-log中的LSN最新的值。当MySQL刷脏数据页面时，磁盘中数据页面文件的LSN的值也会增加。

回到我们的问题，MySQL怎么判断脏页？

只需要判断这个页面的LSN值，如果数据页面的LSN的值大于 Checkpoint的LSN值，说明这个数据页面接受了新的更新（所有数据页面的LSN更新到更大的值）。那么这个页面就是脏页。

问题2：MySQL基于Checkpoint如何从crash中恢复？
MySQL在崩溃恢复时，会从重做日志redo-log的Checkpoint处开始执行重放操作。 它从last Checkpoint对应的LSN开始扫描redo-log日志，并将其应用到buffer-pool中，直到last Checkpoint对应的LSN等于 log flushed up to 对应的 LSN （也就是redo-log磁盘上存储的LSN值)，则恢复完成 。

说白了，检查点要做的操作就是将缓冲池中的数据页刷到磁盘，最终达到内存与外存数据页的一致。检查点的作用是縮短当数据库发生crash时数据库恢复所需要的时间。

尽管从理论上来说，由于重做日志redo-log记录了所有的页操作，当数据库crash时，恢复重做日志redo-log即可。但考虑到数据库是一个需要保证高可用的应用环境，从crash环境中快速恢复回来对服务的可用性是一个关键指标。因此检查点checkpoint机制就是将内存页写加磁盘，这样发生crash的时候，已经刷到磁盘的数据页就不再需要恢复，只需要恢复检查点之后的操作。

四 读写分离

通过策略将读和写分离开来，提高执行效率

使用中间件来分发读和写的业务

五 分库分表

垂直拆分

垂直拆分特点
1.每个库（表）的结构都不一样
2.每个库（表）的数据至少一列一样
3.每个库（表）的并集是全量数据

垂直拆分优缺点

优点：
1.拆分后业务清晰（专库专用按业务拆分）
2.数据维护简单，按业务不同，业务放到不同机器上

缺点:
1.如果单表的数据量，写读压力大
2.受某种业务决定，或者被限制，也就是说一个业务往往会影响到数据库的瓶颈（性能问题，如双十一抢购）
3.部分业务无法关联join，只能通过java程序接口去调用，提高了开发复杂度

水平拆分

水平拆分的其他方式

range来分

每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了,优点：扩容的时候，就很容易，因为你只要预备好，给每个月都准备一个库就可以了，到了一个新的月份的时候，自然而然，就会写新的库了 缺点：大部分的 请求，都是访问最新的数据。实际生产用range，要看场景，你的用户不是仅仅访问最新的数据，而是均匀的访问现在的数据以及历史的数据

hash分发

优点：可以平均分配每个库的数据量和请求压力 缺点：扩容起来比较麻烦，会有一个数据迁移的这么一个过程
水平拆分特点
1.每个库（表）的结构都一样
2.每个库（表）的数据都不一样
3.每个库（表）的并集是全量数据

水平拆分优缺点

优点：
1.单库/单表的数据保持在一定量（减少），有助于性能提高
2.提高了系统的稳定性和负载能力
3.拆分表的结构相同，程序改造较少。

缺点:
1.数据的扩容很有难度维护量大
2.拆分规则很难抽象出来
3.分片事务的一致性问题部分业务无法关联join，只能通过java程序接口去调用

分库分表带来的问题
1 分布式事务
2 跨库join查询
3 分布式全局唯一id
4 开发成本 对程序员要求高

可选开元组件：

分库分表的开源框架
jdbc 直连层：shardingsphere、tddl
proxy 代理层：mycat，mysql-proxy（360）
TODO：分片键和雪花算法

六 B+树索引

思考 加入我要设计一个支持高并发 高可用，千万级数据的索引系统，如何设计？


一 为什么不用hash？

二、为什么不用二叉树，如果将一个乱序的数据放入二叉树中，效率会高，但是如果数据是有顺序的，比如1、2、3、4、5，则二叉树将会编程一个链表的样式，失去了二叉树的优势

三、为什么不用红黑树，红黑树也叫二叉平衡树，红黑树可以有效解决掉顺序数据一次放入二叉树而导致的形成链表的结果，

1 红黑树一个节点只能存储一个数据，就导致如果是大量的数据，红黑树的高度就不可控，如果一个红黑树是20的高度，要查询的数据在叶子结点，则表示需要需磁盘20次IO，效率还是不高
2 磁盘读取一次读4个kb的数据，红黑树一个节点存储的数据又太少，这样每次加载内存造成IO浪费

四、为什么不用B树，B树比红黑树的优势是，

1 B树是一个节点上存储多个数据，比如磁盘的一页数据，这样的横向扩展，相同的数据量就可以比红黑树减少更多的高度，从而减少了磁盘的IO次数，下面开始对比B树和B+树，就会发现B+树在查询数据方面要比B树有很多便捷的地方
2 b+数的叶子节点是链表，区间查找和范围查找会更方便
3 b树的数据直接存在根节点上，占空间
4 innodb的默认内存页为16kb,每一个节点要占用磁盘的一个block，假如将节点的data填满，一个节点最多存16条数据三层最多存贮4096

B+Tree：

1数据存储在叶子节点，根节点不存数据，占用内存少
2叶子结点两两指针互相连接是链表，增删改查代价小；符合磁盘的预读规则，范围查找更快
3 假如指针和key加起来为10byte，那么每个16kb的节点就能存储1600调数据，三层b+数就能存储千万级的数据
4 能有效控制高度，数据区间更小，实现更快的检索

那么如何添加和删除索引？

问：mysql索引一般是几层 答：一般情况下，3-4层的索引基于可以支持千万级别的数据

总结：
1 基于B+树的存储结构，在一个根节点上，指针一般是固定大小的，优化的空间就是key，因此key在数据类型选择上尽量选择int型，为了方便插入，也最好可以是自增的主键，这样可以减少分裂次数，不影响之前的数据
2 数据迁移过程中，关掉索引，数据前部迁移好之后，再开始建索引，避免迁移过程中频繁的建索引

主键与索引

主键相当于一本书的页码，索引相当于书的目录

主键和索引的关系：

主键是一定是唯一性索引，但唯一性索引不一定是主键

数据库管理系统对于主键自动生成唯一索引，所以主键是一个特殊的索引

唯一索引标识索引值唯一，一个表可以有多个唯一索引，但主键只能有一个

主键列不能为空，但唯一索引列可以为空

一张表只能有一个主键，但可以有多个索引

七 回表查询

如图 当使用非主键索引时，mysql先通过B+树找到主键，再回表通过主键找到行，这个过程就是回表

可见回表查询即浪费内存，有没有效率，如何防止回表查询？

1 查询时使用主键索引查询
2 以int型设置主键

如上面的回表查询，索引只有name和innodb自动创建的id主键索引，当只查询这两个字段的时候不需要回表，查询其他字段或整行都需要回表

还有下面要介绍的联合索引查询

八 联合索引

对于只有一个主键的表，我们可以更改为联合索引

语句: alert table tablename add index “index”(col)

这样构建的索引B+树如下图：

最左原则

1,2符合最左原则，3不符合 ，4的mysql优化器会对sql优化，调换name和age的顺序并不影响查询结果，所以优化器为了匹配最左索引，优化sql后查询，一次会用到最左index

我们会发现第一列name是有序的 其他的列则是无序的，这就是最左原则，这也是联合索引会出现索引失效的原则，当你用第二个索引查询的时候，就会失效，因为在第二个索引键B+树是不成立的

这样在我们select创建的联合索引的列的时候，就不需要再回表，这样达达提高了查询效率

由此看来联合索引的作用就是防止回表，加快查询速度

但是联合索引相对于单列索引容易出现索引失效的情况

联合索引只有最左边的索引才会走索引：

原因很简单，只适合因为name是排好序的，其他都是无序的，不能走b+树查找

九 索引下推

索引下推 也叫索引覆盖

索引下推是mysql版本5.6更新的主要功能，用在二级索引中，如果能通过索引直接过滤数据的，就不再回表查询

十 聚集索引

十一 索引优化

有了上面的基础知识，我们来分析下如何优化索引

1 使用索引查询的时候，尽量不要使用函数和表达式，sql只负责取数据，计算都在应用层完成

2 尽量使用主键查询，避免使用其他col查询时回表

3使用前缀索引

对于字符串，设置索引的时候，不必取全部的长度

通过left（col，n）可以截取string的部分显示

如何决定截取的字符串是合适的长度

当截取的长度的对应idx与初始的idx接近，且增加长度idx也不会改变，说明找到 了合适的角度

4使用索引扫描来进行排序，就是用到order by的时候尽量用有序的索引来order by，且排序的方向一致

不能使用索引排序的情况：
查询条件中包含不同排序方向
查询条件中引用不在索引中的列
无法组合最左前缀时
第一列是查询范围时
where中有多个等于条件

5 union all ,in ,or 都能走索引，但推荐使用in （union要查两次，or 要求col是单列索引）

6 范围列都可以用索引，（B+数的底层支持）< > <= >= bweteen

7 强制类型转换会导致全表扫描

8 数据更新十分频繁，加大B+树维护次数；不建议加索引；数据区分度不高的列 不建议加索引 如gender(0,1)

9 数据可能为NULL的列不建议加索引，可能会返回超预期的结果

10 join的时候，不建议超过三个表，且字段的类型强一致

11 能使用limit的时候尽量使用limit

12 单表索引建议最多5个

13中文编码设置utf8_mb4

14 两表联合查询
LEFT JOIN时，索引建在右表上
RIGHT JOIN时，索引建在左表上

15 三表联合查询，
LEFT JOIN时，索引建在右边两表上
RIGHT JOIN时，索引建在左边两表上

16 小表驱动大表

小表一趟for循环的代价+大表上使用B+树索引的代价<大表一趟for循环的代价+小表使用B+树索引的代价

a.小表放到in之后的效率优于放到in之前
例：select name from B where id in(select id in A);
分析：上面的查询语句可以拆成 首先 select id in A; 其次 select name from B where B.id=A.id;这样的话
b.小表放到exists之前效率优于放到exists之后
例：select name from A where(Select * from B where B.id=A.id);
分析：先遍历小表A，取得A中每个值和B中的值进行比对，如果比对成功了返回true

查看query执行情况：

其他优化 建表 表结构 分表 sql语句 集群 主从复制 读写分离

十二 mysql锁机制

读锁写锁

读读不互斥 读写互斥 写写互斥

1 myisam读锁

线程2被阻塞知道线程1释放锁

2 myisam写锁

lock table tablename write

自己可以写，可以读 ，不能读其他表，其他线程不能不写加锁的表

3 innoDB行锁

需要了解的事务和锁基础知识

1 事务以及ACID

2 事务带来的问题

更新丢失—即一个更新覆盖另外一个
脏读-一个线程读取了另一个线程未提交的数据
不可重复读-前后两次读到的数据不一致
幻读-A线程读到B线程新增的数据

两个线程修改同一行，后来的线程阻塞 等待commit

修改不同行，ok

强制转换字段的类型，导致索引失效，行锁升级成表锁，导致线程阻塞

查看行锁：“show states like ''innodb_row_lock%”

为了解决以上问题，mysql引入四种隔离

3 mysql隔离级别

事务的执行过程如下，以 begin 或者 start transaction 开始，然后执行一系列操作，最后要执行 commit 操作，事务才算结束。当然，如果进行回滚操作(rollback)，事务也会结束

设置事务的隔离级别

set session/global transaction isolation level read uncommitted | read committed | repeatable read | serializable

4 4种隔离级别的实现

一） 未提交读–相当于不加任何锁，也就不能解决上面任何一个问题
二） 读提交-
三） 可重复读-

二三这两个要放在一块分析，在MySQL InnoDB存储引擎下,RC、RR基于MVCC（多版本并发控制）进行并发事务控制
MVCC是基于”数据版本”对并发事务进行访问

MVCC

MVCC维护一行数据的多个版本，快照读维护数据对每个事务可见与不可见

DB_TRX_ID : 6 字节 DB_TRX_ID 字段，表示最后更新的事务 id ( update , delete , insert ) 。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已软删除。

DB_ROLL_PTR : 7 字节回滚指针，指向前一个版本的 undo log 记录，组成 undo 链表。如果更新了行，则撤消日志记录包含在更新行之前重建行内容所需的信息。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。每行数据也都是有多个版本的,涉及到transaction id，每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id

假设这时候Session A开始了，从上一讲，我们已经知道，begin时并不会生成快照，快照在第一次select时才会生成，那么第一次select时，session A都做了什么呢？

session A只需要做一件事：用一个数组，来记录当前活跃的事务ID。

假设session A的事务ID是97，当前还有另外两个事务，事务ID是94、96，所以session A会生成一个[94,96,97]的数组。

这个数组有什么用？后面你就知道了。

接着，session B执行了update语句，来更新id=1这一行数据，给这一行数据生成一个新的版本，假设session B的事务ID是98，因此这行数据就有了两个版本：

这时候，session A又来select了，当前版本是session B生成的，那session A是如何找到之前的版本的呢？

这时候，session A一开始生成的事务数组就派上用场了，session A的事务数组是[94,96,97]，最小事务ID是94，最大事务ID是97，所以，当它遇到一行数据时，会先判断这行数据的版本号X：

1 如果X大于97，那么意味着这行数据，是在session A开始之后，才提交的，应该对session A不可见
2 如果X小于97，那么分两种情况：
a 如果X在数组里面，比如X是96，那么意味着，当session A开始时，生成这个版本的数据的事务，还没提交，因此这行数据对Session A不可见
b 如果X不在数组里面，比如X是95，那么意味着，当session A开始时，生成这个版本的数据的事务，已经提交，因此这行数据对Session A可见

好，现在session A开始遍历id=1这行数据的所有版本：

当前版本是98，大于97，所以不可见，继续看上一个版本；

再往上，版本是90，小于94，可见，就它了，所以session A select出来的id=1的数据，c的值是1。
当然，这样的人肉判断实在太麻烦了，在《Mysql实战45讲》里，丁奇给出了这样一个“等价判断”可见性的原则：

1 版本未提交，不可见；
2 版本已提交，但是是在快照创建后提交的，不可见；
3 版本已提交，而且是在快照创建前提交的，可见。
这其实就是可重复读的想要实现的效果。

ReadView

以上，事务记录的活跃事务ID是数组就是readview，包含三个值，

tips：
“快照”不是全量拷贝，而是利用了数据多版本的特性，也就是MVCC，结合undol og实现
MVCC的核心在于每个事务自己维护的一个事务ID数组
可以用“等价原则”来判断数据版本的可见性

所以由快照的机制，可以看出读提交与可重复读的实现机制， 可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行commit语句的时候都重新生成一次快照。

 对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

当前事务内的更新，可以读到
版本未提交，不能读到
版本已提交，但是却在快照创建后提交的，不能读到
版本已提交，且是在快照创建前提交的，可以读到

两者主要的区别就是在快照的创建上，可重复读仅在事务开始时创建一次，而读提交每次执行commit语句的时候都要重新创建一次。


步骤（时间线）：
1 线程B update
2 线程B commit
3 线程A第一次select**（此时才生成快照，并不是线程A在begin的时候生成快照）**

上面两个图说明了快照在第一次select的时候才生成

区别在于第一次select

上图左边快照生成：

对比这三个图，我们来分析，事务t1第一次select时创建的快照都有什么？
1 trx_list 此时事务t2已经提交，当前活跃事务只有他自己t1
2 up_limit_id 活跃事务中最小的事务id 只有自己
3 low_limit_id 尚未分配的id，也就是下一个要分配的id，就是事务3
4 BD_TRX_ID 最近操作此行记录的事务id，是t2

然后依据可见性算法，判断当前记录对t1可见

再来看下图事务t1生成快照，第一次select快照：
1 活跃事务list有两个 t1和t2
2 活跃事务中最小的是-t1
3 下一个待分配事务id为3
4 DB_TRX_ID 因为t1和t2都还未此行记录进行update操作，因此最近应该是上一个事务，（上一个事务肯定小于t1和t2，我们再此记为0）

走算法第一条，因此能看到，看到的是未修改前的数据

第二次select快照：

因此：
在RC模式下，读第二个快照，能看到t2修改的数据
在RR模式下。读第一个快照,看不到t2更新的数据

幻读：

当前读和快照读一起使用的时候，就会出现幻读情况
左边因为执行了5 当前读，导致出现幻读

当我们用for update加上锁，这个锁不但锁住了行，还锁住了区间，就是next_key_lock,另一个线程操作锁定的区间和行就会阻塞

RR模式下，由记录锁加上间隙锁组成的NEXT_KEY_LOCK区间所保证了不会出现幻读

记录锁（Record Locks）

SELECT * FROM test WHERE id=1 FOR UPDATE;
它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行

间隙锁（Gap Locks）

SELECT * FROM emp WHERE empid > 100 FOR UPDATE
当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（即使这些记录并不存在）的“间隙”加锁。也就是说此时插入id=103/104都会失败

、
、

$如果有索引，update是行锁，没有索引的时候，是表锁$
、
，’
临键锁（Next-Key Locks）
Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

四）系列化
串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、可重复读、幻读的问题，但是效果最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它就相当于单线程，后一个事务的执行必须等待前一个事务结束。

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

– 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table … LOCK IN SHARE MODE;

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

– 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table … FOR UPDATE;

意向共享锁（IS）和 意向排他锁（IX）都是表锁。
意向锁是一种 不与行级锁冲突的表级锁，这一点非常重要。
意向锁是 InnoDB 自动加的， 不需用户干预。
意向锁是在 InnoDB 下存在的内部锁，对于MyISAM 而言 没有意向锁之说。

意向锁不会为难意向锁。也不会为难行级排他(X)/共享(X)锁,它的存在是为难表级排他(X)/共享(X)锁。

插入意向锁

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。
该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。

假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7）之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。

总结来说，插入意向锁 的特性可以分成两部分：

插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

总结来说，插入意向锁 的特性可以分成两部分：

a 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
b 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

由此看出：
1 innoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。
2 插入意向锁是一种特殊的间隙锁。
3 插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。
　
　
注意：
　
1、事务隔离级别为读提交时，写数据只会锁住相应的行

2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。

3、事务隔离级别为串行化时，读写数据都会锁住整张表

4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

十三 explai语句

explain的type类型

| ALL | 全表扫描

| index | 索引全扫描

| range | 索引范围扫描，常用语<,<=,>=,between等操作

| ref | 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中

| eq_ref | 类似ref，区别在于使用的是唯一索引，使用主键的关联查询

| const/system | 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

| null | MySQL不访问任何表或索引，直接返回结果

由上至下，效率越来越高

Extra列

此列是一些额外信息。常见的重要值如下：

1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。需优化

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

条件: 索引是c1 c2 c3 c4
图1

图2

图3

以上，总结：
1 order by 尽量按照索引顺序
2 范围之后索引失效 图2 c3>a3是范围，后面的索引就用不上了
3 mysql底层会优化子句顺序，上面用到3个索引，下面用到4个索引，因为优化器将c3=a3和c4>a4两个条件调换了

联合索引在查询中使用的情况：

十四 show profile分析

分析每一条sql执行的情况

1 先通过show profile查到id 包含执行的时长

2 通过id去查看具体一条sql执行的cpu，block情况