MySQL关键字记录

MySQL关键字记录

 

1.Server层

1、Server层包括连接器、查询缓存（已下线）、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
 

2.存储引擎层

1、存储引擎层负责数据的存储和提取。其架构模式是插拔式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。提供接口给 Server 层，从 MySQL 5.5.5 版本开始默认存储引擎为 InnoDB。
 

3.连接器

1、管理连接，权限校验
 

4.查询缓存

1、命中则直接返回结果
 

5.分析器

1、词法分析，语法分析
 

6.优化器

1、执行计划生成，索引选择
 

7.执行器

1、操作引擎，返回结果
 

8.redo log（重做日志，数据页 16KB）

1、当有一条记录需要更新（删改插）的时候，InnoDB 就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。适当的时候，会把这个操作记录更新到磁盘里面。一般用于crash-safe以及把随机写转成顺序写。
2、redo log 一般是固定大小的，一般配置成一组4个文件，每个文件大小是 1GB，并且为循环写。
3、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog日志是逻辑日志（通用），记录的是这个语句的原始逻辑，比如”给 ID=2 这一行的 c 字段加 1“。
 

9.binlog（备份日志，并不负责数据落盘）

1、属于 Server 层，采用追加写的方式，不会覆盖以前的日志。
2、两阶段提交：redo log 和 binlog 属于两个不同逻辑的日志，要保证事务的情况下写两个日志，需要两阶段提交，可用反证法证明。写入 redo log（处于 prepare 阶段） -> 写 binlog -> 提交事务（处于 commit 状态）
3、binlog-checksum：用于校验 binlog 日志的正确性。
4、statement 格式的 binlog，最后会有 COMMIT。
5、row 格式的 binlog，最后会有一个 XID event。
 

10.WAL 技术

1、Write-Ahead Loggin（先写日志），先写日志，再写磁盘。
 

11.事务

1、事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
2、读已提交：每次执行语句的时候开启一个视图。
3、可重复读：事务T启动的时候会创建一个视图 read-view。
4、事务 ID：transaction Id。事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。
 

12.ACID

1、Atomicity：原子性
2、Consistency：一致性
3、Isolation：隔离性
4、Durability：持久性
 

13.隔离级别

1、脏读、不可重复读、幻读。
2、读未提交：其他事务能读到该事务未提交的变更。
3、读已提交：其他事务只能读到该事务已提交的变更。
4、可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
5、串行化：当出现读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
 

14.MySQL参数

参数	默认值	备注
transaction-isolation	READ-COMMITTED	启动参数隔离级别
set autocommit	–	控制当前线程是否自动提交，关闭之后只能通过主动执行 commit 或 rollback 结束事务，或者断开连接
Flush tables with read lock（FTWRL）	–	让整个库处于只读状态
set global readonly = true	false	让全库进入只读状态
innodb_lock_wait_timeout	50	死锁超时时间
innodb_deadlock_detect	on	开启后发起死锁检测
einnodb_change_buffer_max_size	–	表示 change buffer 占用 buffer pool 空间的最大百分比
innodb_stats_persistent	–	on：统计信息会持久化存储； off：统计信息只存储在内存中
innodb_flush_neighbors	0	是否开启刷脏连坐模式，0否1是
innodb_file_per_table	on	off：表的数据放在系统共享表空间，也就是和数据字典放在一起。on：每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中，drop table 命令就会直接删除这个文件，如果在共享表空间中，即使表删掉了，空间也不会回收
binlog_cache_size	–	用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
max_connections	–	控制 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，报 Too many connections

 

15.read-view（视图）

1、在 MySQL 中，实际每条记录在更新的时候都会同时记录一条回滚操作（undo log）。记录上的最新的值，通过回滚操作，都可以得到前一个状态的值。
2、在查询的时候，不同时刻启动的事务会有不同的 read-view，同一条记录在系统中可以存在多个版本，这就是 MVCC (多版本并发控制）。
4、view：查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。语句：create view …。
5、快照读：一行数据被修改过，但是该事务不论什么时候查询，看到这行数据的结果都是一致的。
6、当前读：更新数据都是先读后写的，而这个读。只能读当前的值。
7、InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。 这个视图数组和高水位，就组成了当前事务的一致性视图（read-view） 。
 

16.undo log（回滚日志）

1、什么时候删除：在没有事务需要使用到这些回滚日志时，回滚日志会被删除。即当系统里没有比这个回滚日志更早的 read-view 的时候，此时会放入待清理的链表，由 purge 线程判断是否有更老的事务存在来决定删除。
2、避免长事务：长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库的任何数据，所以在这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
3、MySQL 5.5 及以前的版本，undo log 是跟数据字典一起放在 ibdata（共享表空间） 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小，只能重建库。
4、MySQL 5.6及之后的版本，undo log 可以配置成独立的文件，但是需要提前在配置文件中配置，完成数据库初始化生效后生效且不可改变 undo log文件的个数
 

17.事务的启动方式

1、显示启动：begin 或 start transaction 启动，commit 提交或 rollback 回滚
2、自动提交：set autocommit = 0，关闭当前线程的自动提交，需要主动执行 commit 或 rollback 结束事务，或者断开连接。
3、commit work and chain语法：在 set autocommit = 1 时，用 begin 显式启动的事务，如果执行 commit work and chain，则是提交事务并自动启动下一个事务。
 

18.sql

1、查找持续时间超过 60s 的事务：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

2、重建（主键）索引

alter table T drop index k;
alter table T add index(k);

alter table T drop primary key;
alter table T add primary key(id);

 

19.常见的索引模型

1、哈希表：key-value 存储数据的结构，新增快，范围查询需要全表扫描，适合等值查询场景。
2、有序数组：采用二分法等值查询，查询快，新增成本高，适合静态存储场景。
3、多叉树：父节点左子树所有节点的值小于父节点的值，右子树所有节点的值大于父节点的值。100 万节点的平衡二叉树，树高20，一次查询可能需要访问 20 个数据快，查询慢。所以采用N叉树，根据数据块的大小来决定 N ，让树高在保持在一个高度，均衡每一次查询的访问磁盘的次数。

20.索引类型

1、主键索引（聚簇索引）：主键索引的叶子节点存的是整行数据，不需要回表查询。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
2、非主键索引（二级索引）：非主键索引的叶子节点存的是主键的值，可能需要回表查询。
3、普通索引：索引值可重复。
4、唯一索引：索引值不可重复。
5、覆盖索引：非索引类型，需要查询的列在二级索引中能直接提供结果，不需要回表，则成为覆盖索引。
6、索引下推： 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
7、索引前缀：最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
 

21.全局锁

1、全局锁就是对整个数据库实例加锁，处于只读状态。其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包含建表、修改表结构等）和更新类事务的提交语句。
2、使用场景：全库逻辑备份，也就是把整库都 select 出来存成文本。
3、set global readonly = true：

• 在有些系统中，readonly 的值会被用来做其他逻辑，用来判断一个库是主库还是备库，修改 global 变量的方式影响面更大。
• FTWRL 会自动释放，而 readonly 只能手动释放。
  4、缺点：
• 主库备份，在备份期间不能执行更新，业务停摆。
• 从库备份，不能执行主库同步过来的 binlog，会导致主从延迟。
   

22.表级锁

1、表锁：lock tables … read/write。可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写歪，也限定了本线程接下来的操作对象。

2、元数据锁（meta data lock，MDL）：MDL 不需要显式使用，在访问一个表的时候会被自动加上。

• MDL 读锁：增删改查操作
• MDL 写锁：对表做结构变更操作
   

23.行锁

1、行锁就是针对数据表中行记录的锁。行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才结束。所以，当事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
2、
 

24.备份工具：mysqldump

1、mysqldump 使用 -single-transaction 的时候，导致据之前就会启动一个事务，来确保拿到一致性视图。
2、只适用于所有的表使用事务引擎的表。
 

25.Online DDL

1、拿 MDL 写锁。
2、降级成 MDL 读锁。
3、真正做 DDL。（占用大部分时间）
4、升级成 MDL 写锁。
5、缩放 MDL 锁。
6、MySQL 8.0 已经可以把表结构放在 InnoDB 字典里了，支持表结构的可重复读。
 

26.死锁和死锁检测

1、死锁：不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无线等待的状态。

• 等待锁超时，innodb_lock_wait_timeout = 50s
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
   

27.change buffer（普通索引，顺序读）

1、定义：当需要更新一个数据页的时候，若在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，无需从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。存放在内存和 ibdata 中 。
2、meger：将 change buffer 中的操作应用到原数据页，得到最新结果的过程。

• 查询
• 后台线程定期merge
• 数据库正常关闭（shutdown）
  3、 使用场景：写多读少的业务，账单类、日志类
   

28.区分度

1、用来估算记录数。一个索引上不同的值越多（基数），这个索引的区分度就越好。
2、analyze table t ： 重新统计索引信息；force index ：强行执行索引
3、采样统计： N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候，会自动触发重新做一个索引统计。

• 统计信息持久化存储：N=20，M=10
• 统计信息存储在内存中：N=8，M=16
   

29.刷脏页

1、当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
2、MySQL 偶尔"抖"一下的瞬间，可能就是在刷脏页（flush）。

• redo log 写满了，系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
• 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
• MySQL 认为系统“空闲”的时候。
• MySQL 正常关闭的时候。
   

30.InnoDB 刷脏页的控制策略

1、MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大。可能就是以上参数设置出了问题。刷脏页的速度比脏页生成的速度还慢。
2、连坐机制：flush 脏页的时候，如果旁边的数据页刚好是脏页，则会一起 flush 掉，一直蔓延下去。innodb_flush_neighbors
3、参考因素：脏页比例M和（当前日志序号 - checkpoint）N。选择其中比较大的那个值 R。按照 R% 速度刷脏页。

• 脏页比例：默认 75%
• redo log写盘速度：innodb_io_capacity

31.空洞

1、记录的复用：例如 ID 是 100 的行被删除了，可以直接复用这个空间。单如果插入的是一个 ID 是 200 的行，就不能复用这个位置。
2、数据页的复用：当整个页在 B+ 树里面摘掉以后，可以复用到任何位置。
3、delete 命令：只是将数据页标记为可复用，磁盘并不会变小，而没有被使用的空间，看起来就像是“空洞”。
4、不止删除数据会造成空洞，插入数据也会（随机插入），以及更新数据。
 

32.重建表

1、alter table a engine = InnoDB（recreate） 命令来重建表：自动完成转存数据、交换表名、删除旧表的操作。
2、Online DDL：生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，后续在应用回去。
3、alter table t engine=innodb,ALGORITHM=inplace;使用临时文件，对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作。
4、alter table t engine=innodb,ALGORITHM=copy;强制拷贝表，使用临时表。
5、DDL过程如果是 Online 的，一定是 inplace，反之未必（添加全文索引）。
6、analyze table t没有重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁。
7、optimize table t 等于 recreate + analyze。
 

33.count(*)

1、由于 MVCC 的原因，InnoDB表“应该返回多少行”是不确定的。
2、count(*)：MySQL 优化器会找到最小的那棵树来遍历，只取行数。
3、count(主键 id)：遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。会自动找最小的那棵树。
4、count(字段)：跟 count(id) 类似，但是只会找指定的字段。
5、count(1)：server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
 

34.全字段排序

1、按 name 排序的时候，会根据排序所需的内存和参数 sort_buffer_size（MySQL 为排序开启的内存（sort_buffer）大小）来决定使用内存或磁盘临时文件。
2、全字段排序会将所有查询字段都放进 sort_buffer 中排序，不需要回表。
 

35.rowid 排序

1、max_length_for_sort_data：专门控制用于排序的行数据的长度的一个参数。字段定义的长度大于这个参数，则不适用全字段排序。
2、rowid 排序只会将需要排序的列和主键 id 放入sort_buffer 中，后续需要根据主键进行回表查询。
3、如果内存够，就要多利用内存，尽量减少磁盘访问。对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
4、归并排序算法：生成多个临时文件，每个文件排好顺序，再归并成一个大文件，会将所有值排列好顺序。
5、优先队列排序算法：可以选择前 N 个行进行排序，不需要生成临时文件。如果维护的堆大于 sort_buffer_size 大小的话，就只能使用归并排序算法。

 

36.数据修改

1、MySQL 当执行一个没有变化的 update 的语句时，是否会执行，还是判断一致之后不进行修改？

• binlog_format=statement 格式会一样执行，进行覆写。
• binlog_format=row 并且 binlog_row_image=FULL 时，由于 MySQL 需要在 binlog 里面记录所有的字段，所以在读数据的时候把所有的数据都读出来了，“既然读了数据，就会判断”
• binlog_format=row 并且 binlog_row_image=NOBLOB，会读出 blob 外的所有字段
• timestamp：如果表中有 timestamp 字段并且设置了自动更新的话，那么更新“别的字段”的时候，MySQL 会读入所有涉及的字段，这样通过判断，就会发现不需要修改。

37.order by rand()

1、随机排序并取前 3 个。

38.临时表

1、临时内存表：memory 引擎，使用 rowid（不需要回表） 排序的方法

2、磁盘临时表：tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16 M，如果临时表大小超过 temp_table_size，内存临时表就会转换成磁盘临时表。默认是 InnoDB 引擎，由 internal_tmp_disk_storage_engine 控制。
 

39.索引失效场景

1、函数操作：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但是如果二级索引比主键索引更小的话，遍历二级索引比遍历主键索引来得更快，最终还是会选择二级索引。
2、隐式类型转换：就是进行了函数操作，转换了类型。

• 字符串和数字做比较的话，是将字符串转换成数字。
  3、隐式字符编码转换：联表查询的时候，两张表的字符集不一致，导致索引失效。连接过程中要求在被驱动表的索引字段上加函数操作。
• utf8mb4 是 utf8 的超集，会将 utf8 转换成 utf8mb4 字符集，再做比较。
   

40.查询性能问题

1、查询长时间不返回：

• 等 MDL 锁：show processlist：有线程正在表 t 上请求或持有 MDL 写锁，把 select 语句堵住了。
• 等 flush：flush 语句被其他线程堵住了。
• 等行锁：
  mysql> select * from t sys.innodb_lock_waits where locked_table=’test.t’\G

2、查询慢：回滚日志过多，导致当前读很慢。
 

41.幻读

1、在可重复读隔离级别下，并且在“当前读“下才会出现。幻读仅专指”新插入的行“。
2、问题：

• 语义上被破坏
• 数据一致性问题：导致数据和日志在逻辑上不一致。

3、产生原因：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的”间隙“。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁（Gap lock）。
4、读提交隔离级别没有间隙锁，但是需要将 binlog 格式设置为 row，避免数据和日志不一致问题。
 

42.间隙锁

1、跟间隙锁存在冲突关系的，是”往这个间隙中插入一个记录“这个操作。间隙锁之间都不存在冲突关系。
2、next-key lock：间隙锁和行锁合称。每个 next-key lock 是前开后闭区间。
3、间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响了并发度。
 

43.加锁规则（可重复读隔离级别）

• 原则1：加锁的基本单位是 next-key lock。
• 原则2：查找过程中访问到的对象才会加锁，查询使用覆盖索引，不会给主键上锁（lock in share mode）。
• 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化插入成行锁。
• 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化成间隙锁。
• bug1：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
   

44.短连接风暴

1、大量的短连接，连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。
2、先处理掉那些占着连接但是不工作的线程（show processlist）。
3、减少连接过程的消耗：跳过权限校验阶段（不安全）
 

45.慢查询性能问题

1、索引没有设计好：如果数据库已经被打挂了，紧急创建索引来解决，MySQL 5.6 版本以后，创建索引都支持 Online DDL 了。
2、语句没写好：MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式（存储过程）。
 

46.QPS突增问题

1、将该功能下线或者重写语句返回 select 1（注意业务逻辑）
 

47.binlog 的写入机制

1、binlog 的写入机制：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。
2、一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
3、系统给 binlog cache 分配了一篇内存，每个线程一个。参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中（先把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，fsync：才是将数据持久化到磁盘的操作），并清空 binlog cache。
4、write 和 fsync 的时机，是由参数 sync_binlog 控制，常见设置为 100~1000 中的某个数值

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

48.redo log 的写入机制

1、事务在执行过程中，生成的 redo log 是要写道 redo log buffer 的。不会每次生成后都直接持久化到磁盘里，因为事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交，所以这时日志丢了也不会有损失。
2、事务还还没提交的时候，redo log buffer 中的部分日志有可能被持久化到磁盘。

• redo log buffer：物理上是 MySQL 进程内存中。
• page cache：写到磁盘（write），但是没有持久化（fsync），物理上是在文件系统的 page cache。
• hard disk：持久化到磁盘

3、redo log 的写入策略（innodb_flush_log_at_trx_commit）

• 0：表示每次事务提交时都只是把 redo log 留在 redo log buffer中。
• 1：表示每次事务提交时都将 redo log 直接持久化到磁盘。
• 2：表示每次事务提交时都只是把 redo log 写到 page cache。

4、InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后带哦用 fsync 持久化到磁盘（所以一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的）。
5、redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘（由于事务没有提交，这个写盘动作只是 write，没有调用 fsync）。
6、并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。
7、由于两阶段提交的时候，时序上 redo log 先 prepare，再写 bin log，最后再把 redo log commit。如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。
8、“双 1” 配置：sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。
 

49.组提交机制（group commit）

1、日志逻辑序列号（log sequence number，LSN）：单调递增，用来对应 redo log 的一个个写入点，每次写入长度为 length 的 redo log，LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确认数据页不会被多次执行重复 redo log。
2、组提交：事务写盘的时候，会拿组里最大 LSN 去写盘，当事务返回的时候，所有 LSN 小于等于之前 LSN 的 redo log，都已经被持久化到磁盘，这时候其他组内事务就可以直接返回了。在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来的这个 fsync 越晚调用，组员可能越多，节约磁盘 IOPS 的效果越好。

3、binlog_group_commit_sync_delay：表示延迟多少微秒后才调用 fsync。当为0时，以下参数无效。
4、binlog_group_commit_sync_no_delay_count：表示累积多少次以后才调用 fsync。与上面的参数是或关系，只要一个满足即可。
5、以上两个参数满足其中之一后，才会走 sync_binlog 逻辑。
 

50.一个事务日志同步的完成过程

1、在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。
2、在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，io_thread（负责建立连接）、sql_thread（读取中转日志，解析出日志里的命令，并执行）。
3、主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发到备库 B。
4、备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
5、sql_thread 读取中转日志，解析出日志里的命令，并执行。
 

51.binlog 的三种格式

1、statement：binlog 里面记录的是 SQL 语句的原文。缺点：可能会导致主备不一致。
2、row：binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除指定的行，不会有主备删除不同行的问题。缺点：占空间、写 binlog 也要耗费 IO 资源，影响执行速度。
3、mixed：如果执行的语句去掉 limit，就会记录为 statement 格式，不然会记录为 row 格式。
 

52.binlog 循环复制问题（双 M 结构）

1、规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2、一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的server id 相同的新的 binlog；
3、每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
 

53.主备延迟

1、主备延迟时间（seconds_behind_master）：备库 B 执行完这个事务的时间 - 主库 A 写入 binlog 的时间。
2、主备延迟最直接的表现是：备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

• 备库所在机器的性能要比主库所在的机器性能差。
• 备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成了主备延迟。
• 大事务：例如一次性地用 delete 语句删太多数据或者大表 DDL。
• 备库的并行复制能力。
   

54.可靠性优先策略（主备切换）

1、判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
2、把主库 A 改成只读状态，即把 readonly 设置为 true；
3、判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4、把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5、把业务请求切到备库B。
6、有一定的不可用时间，所以要保证 SBM 的值足够小。
 

55.可用性优先策略（主备切换）

1、将上一点的 4、5 步骤放到最前，binlog_format=mixed时，可能会导致主备数据不一致。binlog_format=row时，会直接同步报错。
 

56.备库多线程复制

1、MySQL 5.6 之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。
2、coordinator 就是原来的sql_thread，负责读取中转日志和分发事务。worker 线程的个数，是由参数 slave_paraller_workers 决定的。这个值设置为 8~16 之间最好（32 核物理机）。
3、coordinator 在分发的时候，需要满足一下这两个基本要求：

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

4、按表分发策略：每个 woker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。碰到热点表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
5、按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并发执行。（要求 binlog 格式必须是 row、表必须有主键、不能有外键）。消耗更多的计算资源。
6、MariaDB 的并行复制策略：

• 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id + 1。
• commit_id 直接写到 binlog 里面。
• 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行。
• 这一组全部执行完成后，coordinator 再去取下一批。

7、MySQL 5.7.22 的并发复制策略，binlog-transaction-dependency-tracking：

• COMMIT_ORDER：根据同时进入 prepare 和 commit 来判断是否可以并行的策略。
• WRITESET：表示的是对于事务涉及更新的每一行，计算出这一行的 hash ，组成集合 writeset。如果两个事务没有操作相同的行，就可以并行。
• WRITESET_SESSION：是在 WRITESET 的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。
   

57.主备切换时跳过错误

1、主动跳过一个事务。set global sql_slave_skip_counter = 1;
start slave。
2、设置 slave_skip_errors 参数，直接设置跳过指定的错误，1062错误：插入数据时唯一键冲突。1032错误：删除数据时找不到行。
3、GTID：全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。有两种生成方式：

• gtid_next=automatic，代表使用默认值
• set gtid_next=‘current_gtid’ 指定 current_gtid
   

58.过期读

1、过期读：在从库上会读到系统的一个过期状态。
2、强制走主库方案：将查询请求做分类。
3、sleep 方案：主库更新后，读从库之前先 sleep 一下。
4、判断主备无延迟方案：判断 SBM 是否已经等于 0。
5、配合 semi-sync 方案（半同步复制）：所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

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

6、等主库位点方案：判断接收到的日志已经同步完成。
7、等 GTID 方案：判断主从库的 GTID 集合是否一致。

666.其他

1、如果查询的内容超过了定义的长度，会进行截取再搜索，最后拿着满足的条件再回表判断，并不会判断超过长度就直接返回。
2、