一、SQL执行过程与日志
02 | 日志系统：一条SQL更新语句是如何执行的？
1、redo log（重做日志）
为什么需要日志：每一次的更新操作都需要写进磁盘，随机IO 成本太高
核心技术：
WAL（write after log）：把记录写到 redo log里面（写日志是顺序IO，相对随机IO性能高），并更新内存。系统空闲时，再更新到磁盘里
redo log file是固定大小的，写到末尾就又回到开头循环写，写满了要将内容强制存盘
有了 redo log，InnoDB 可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe
redo log buffer和****file： 有参数控制是否每次commit都需要写到file
2、binlog（归档日志）
为什么有了redo log还要有bin log呢？（两者的区别）
1）redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的
2）redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，并不会覆盖以前的日志
3）bin log用来备份，主从同步；redo log用来提高性能减少IO以及crash-safe
4）redo log内容是物理的，bin log内容是逻辑的
3、写入日志流程（两阶段提交）
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。
然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
4、备份流程
_根据bin log载入，然后执行redo log中在bin log也存在的记录，_redo log中存在在bin log不存在的则舍弃回滚
12 | 为什么我的MySQL会“抖”一下？
1、flush
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为**“脏页”**。
平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 的抖动，可能就是在刷脏页（flush）
**刷脏页过程不用动redo log文件的。 redo log在“重放”的时候，如果一个数据页已经是刷过的，会识别出来并跳过。（LSN不仅只存在于重做日志中，在每个数据页头部也会有对应的LSN号，该LSN记录当前页最后一次修改的LSN号）
**
2、触发flush的情景
**平时要多关注脏页比例，不要让它经常接近 75%。
**
1） redo log 写满了。系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
2）系统内存不足。内存不够用的时候，就要淘汰一些数据页。如果淘汰的是“脏页”，就要先将脏页写到磁盘。把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿 redo log 出来应用不就行了？这里其实是从性能考虑的
3）系统空闲、关机，但这个时候不会影响性能
3、innodb_flush_neighbors
在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为。找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。而如果使用的是 SSD 这类 IOPS 比较高的设备的话，建议把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。
15 | 答疑文章（一）：日志和索引相关问题
1、在两阶段提交的不同瞬间，MySQL 如果发生异常重启，是怎么保证数据完整性的？
崩溃恢复时的判断规则：
如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。
这依赖redo log的二阶段提交
redo log 和 binlog 是怎么关联起来的?它们有一个共同的数据字段，叫 XID
2、MySQL 怎么知道 binlog 是完整的
一个事务的 binlog 是有完整格式的：statement 格式的 binlog，最后会有 COMMIT；row 格式的 binlog，最后会有一个 XID event。
binlog还主要用于主从同步
3、为什么binlog不支持崩溃恢复
InnoDB 引擎使用的是 WAL 技术，执行事务的时候，写完内存和日志，事务就算完成了。如果之后崩溃，要依赖于日志来恢复数据页。binlog 里面并没有记录数据页的更新细节，是补不回来的。
redolog支持崩溃恢复
4、为什么不只用redo log
一个是归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
一个就是 MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL 系统高可用的基础，就是 binlog主从 复制。
5、正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？（为什么redo log支持崩溃恢复）
redo log 并没有记录数据页的完整数据，所以它并没有能力去更新磁盘数据页
刷脏页的过程，与 redo log 毫无关系。刷脏页是把值从buffer pool写入磁盘
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页**。redolog只负责恢复内存**
6、redo log buffer 是什么？是先修改内存，还是先写 redo log 文件？
在执行 insert、update 的时候，数据的内存被修改了，redo log buffer（内存） 写入了。真正把日志写到 redo log 文件，是在执行 commit 语句的时候做的。
18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？
1、条件字段函数操作
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
2、隐式类型转换
数据类型转换的规则是什么？在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。
为什么有数据类型转换，就需要走全索引扫描？触发了上面的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。
23 | MySQL是怎么保证数据不丢的？
1、binlog 的写入机制
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。系统给 binlog cache 分配了一片内存，每个线程一个
write指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync将数据持久化到磁盘的操作，fsync 才占磁盘的 IOPS。
write 和 fsync 的时机，是由参数 sync_binlog 控制的：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
2、redo log 的写入机制
redo log 的三种状态：
存在 redo log buffer 中，物理上是在 MySQL 进程内存中；
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面；
持久化到磁盘，对应的是 hard disk；
日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。
**innodb_flush_log_at_trx_commit 参数，**它有三种可能取值：
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
此外，InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
如果把 innodb_flush_log_at_trx_commit 设置成 1，**那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log。**每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了
3、组提交
MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，**一个事务完整提交前，需要等待两次刷盘，**一次是 redo log（prepare 阶段），一次是 binlog。
组提交（group commit）机制，尽量减少刷盘操作
4、IO性能瓶颈可以通过哪些方法来提升性能
将 sync_binlog 设置为大于 1 的值。风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。风险是，主机掉电的时候会丢数据。
二、事务、隔离机制、锁、MVCC
03 | 事务隔离：为什么你改了我还看不见？
**1、**事务概念
ACID特性。不是所有引擎支持事务（innoDB支持）
2、SQL 标准的事务隔离级别
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。（脏读：读到其他事务还未提交的记录）
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。（不可重复读：在事务执行中读到其他事务提交的变更（update操作），导致前后读的数据不同）
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。（幻读）
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
3、一致性视图read-view
数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
可重复读：视图是在事务启动时创建的，整个事务存在期间都用这个视图。
读提交：视图是在每个 SQL 语句开始执行的时候创建的
读未提交：直接返回记录上的最新值，没有视图概念
串行化：直接用加锁的方式来避免并行访问
4、回滚日志undo log
每条记录在更新的时候都会同时记录一条****回滚日志**（undo log）。记录上的最新值，通过回滚日志操作，都可以得到前一个状态**的值
删除时机：当系统里没有比这个回滚日志更早的 read-view 的时候，回滚日志会被删除。这也是为什么建议尽量不要使用长事务。
btw，长事务还会影响锁
5. MVCC = undo log + readview
06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
1、全局锁
全局锁就是对整个数据库实例加锁，典型使用场景是，做全库逻辑备份（FTWRL 方法）
让整库都只读，很危险：
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
2、MVCC避免全局锁
官方自带的逻辑备份工具是 mysqldump。导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。
3、表锁
对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL锁
MDL 不需要显式使用，在访问一个表的时候会被自动加上。
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
不要在一个事务中先做增删改查，再alter table，因为事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。会导致死锁
比较理想的机制是，在 alter table 语句里面设定等待时间，拿不到锁也不要阻塞后面的业务语句，先放弃
07 | 行锁功过：怎么减少行锁对性能的影响？
1、两阶段锁协议
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议（防止两个事务同时写）
对我们使用事务有什么帮助呢？如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放
2、死锁和死锁检测
两种策略：一种策略是，直接进入等待，直到超时。另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行
正常情况下我们还是要采用第二种策略，即：主动死锁检测；每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
3、热点行更新（死锁检测）导致的性能问题
如果所有事务都要更新同一行的场景呢？每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。
1）控制并发度。可以考虑在中间件实现
2）考虑通过将一行改成逻辑上的多行来减少锁冲突
08 | 事务到底是隔离的还是不隔离的？（重点）
1、事务启动时机（视图创建时机）
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。
快照 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来
**2、MVCC：read view+****row trx_id+**undo log
InnoDB 里面每个事务有一个唯一的事务 ID，并且时严格递增的。
每次事务更新数据的时候，还会生成undo log，并且把事务 ID记为该数据版本的 row trx_id。旧数据版本的值被新的数据值覆盖，但能够通过 undo log和最新的数据值推导出
InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前启动了但还没提交的所有事务 ID。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个高低水位的数组组成了当前事务的一致性视图（read-view）。
**read-view：此事务可以看见那些事务的修改
**
3、一致性读（可重复读隔离机制下读操作select）（MVCC）
**Innodb 要保证这个规则：事务启动以前所有还没提交的事务，它都不可见。
**存已经提交事务的最大值是不够的。 因为存在一个问题，那些比最大值小的事务，之后也可能更新。所以事务启动的时候还要保存“现在正在执行的所有事物ID列表”，如果一个row trx_id在这列表中，也要不可见。
**tips：**可重复读的原则是一致性读，采用MVCC方式实现。而读提交的读也是MVCC控制，只不过可重复读的read view在事务启动时生成，读提交的read view在每个sql语句时重新生成；一致性读还有一个问题未解决：幻读，这个需要加间隙锁解决
由事务id的递增性，****事务的一致性视图（read-view），数据行的 row trx_id共同决定：
1）如果数据行的 row trx_id小于****read-view的低水位，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2）如果数据行的 row trx_id大于等于****read-view的高水位，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3）如果数据行的 row trx_id介于两者之间，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
如果该行当前版本不可见，就通过redo log往前找上一个版本，直至可见版本
4、当前读（更新操作update）（两阶段锁协议）
更新数据都是先读后写的，而这个读只能读当前的值，称为**“当前读”**（current read）。当前读需要加锁：读锁（S 锁，共享锁）和写锁（X 锁，排他锁）
update，select for update加写锁；select lock in share mode加读锁
当前读和两阶段锁，避免了更新丢失：
1）事务a开始，事务b开始，事务a写某行，事务b也写某行，事务a提交，事务b提交：带来更新丢失，b覆盖a的case不会发生。因为a的写锁在提交后才释放，b无法在a提交前写
2）事务a开始，事务b开始，事务a写某行，事务a提交，事务b也写某行，事务b提交：带来更新丢失，b覆盖a的case不会发生。因为b的写操作是当前读，绕过了MVCC的视图机制
20 | 幻读是什么，幻读有什么问题？（重点）
设定在可重复读隔离级别下
1、幻读（可重复读MVCC不会发生幻读，当前读才会发生）
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。事务的update操作被其他事务的 select 语句用“当前读”看到，不能称为幻读，幻读仅专指“新插入的行”。
即使把所有的记录都加上锁，还是阻止不了新插入的记录，阻止不了幻读
2、间隙锁 (Gap Lock)，next-key lock
跟间隙锁存在冲突关系的，是**“往这个间隙中插入一个记录”这个操作**。**间隙锁之间都不存在冲突关系，对于同一个区间，可以有多个事务同时加间隙锁。**这与读锁、写锁不同
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间
3、间隙锁的问题与解决方案
1）对于非索引字段进行update或select .. for update操作，代价极高：所有记录上锁，以及所有间隔的锁。
对于索引字段进行上述操作：只有索引字段本身和附近的间隔会被加锁。
所以，update、delete语句用不上索引是很恐怖的
2）易死锁。业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据。两个事务并发，同时访问一行，间隙锁会彼此互锁导致死锁
insert … on duplicate key update
方案：业务没有强要求一致性读的时候，采用可提交隔离机制。
间隙锁是在可重复读隔离级别下才会生效的，因为幻读违背了可重复读的一致性读原则。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了，因为读提交不需要保证一致性读。但同时，需要把 **binlog 格式设置为 row，**解决可能出现的数据和日志不一致问题
在 tx_isolation= READ-COMMITTED 、binlog_format =statement 的情况下，mysql 没有gap 锁，这样binlog 记录的数据修改的顺序可能会导致 复制环境的 slave 数据和master 数据不一致 www.cnblogs.com/chaosheng/p…
21 | 为什么我只改一行的语句，锁这么多？（重点）
1、可重复读下的一致性读（防幻读）加锁原则
原则 1：加锁的基本单位是 next-key lock。前开后闭区间。
原则 2：索引字段的范围查询，查找过程中访问到的对象才会加 next-key lock（非索引字段的全表扫描会给所有next-key lock加锁）
优化 1：唯一索引上的等值查询，满足等值条件的时候，next-key lock 退化为行锁。
优化 2：唯一索引上的等值查询，不满足等值条件的时候，next-key lock 退化为间隙锁。
优化 3：非唯一索引上的等值查询，向右遍历时会访问到不满足条件的第一个值为止，然后最后一个next-key lock 退化为间隙锁，遍历过程中是完整的next-key lock。不会退化为行锁，锁的是行+左右开区间
**例如，**已经存在id，a：5，5；10，10；其中id为唯一索引，a为普通索引
操作id为5的记录，只锁一行
操作id为7的记录（不存在），锁区间（5，10）
操作a为5的记录，锁区间（0，10）加a为5的那一行
操作a为7的记录（不存在），锁区间（5，10）
索引字段的范围查询会锁遍历过的next-key lock，无索引字段的等值查询、范围查询必须全表扫描，所有next-key都加锁
tips：只有访问到的对象才会加锁**，如果lock in share mode 查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁**，可以被修改。lock in share mode 只锁覆盖索引，但是如果是 for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。（案例二）
2、next-key lock
next-key lock=行锁+间隙锁。间隙锁是不互斥的，行锁是互斥的，分析死锁的时候要将next-key lock一分为二的看 （案例8）
30 | 答疑文章（二）：用动态的观点看加锁
1、不等号条件里的等值查询
select * from t where id>9 and id<12 order by id desc for update;这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。也就是说，id=15 这一行，并没有被加上行锁。
首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。其实是要找到 id=12 的这个值，等值查询，只是最终没找到，但找到了 (10,15) 这个间隙。
然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。
2、等值in查询加锁过程，导致的死锁
select id from t where c in(5,20,10) lock in share mode;
在查找 c=5 的时候，锁住了 (0,5]和(5,10)。同样的，执行 c=10 ，加锁的范围是 (5,10] 和 (10,15)；执行 c=20 这个逻辑的时候，加锁的范围是 (15,20] 和 (20,25)。这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。
如果同时有另外一个语句，是这么写的：select id from t where c in(5,20,10) order by c desc for update;这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。
3、间隙锁范围自动变大
例如， delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。另一个事务 执行完 select 语句后，什么都没做，但它加锁的范围突然“变大”了
例如，已有记录5，10，15，20，25，执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode;锁的范围就是：索引 c 上 (5, 25)；主键索引上 id=15、20 两个行锁。
由于是 order by c desc，第一个要定位的是（等值查询）索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。在索引 c 上向左遍历（范围查询），要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]
也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。
39 | 自增主键为什么不是连续的？
1、自增值保存在哪儿
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。
2、使用机制
如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。该值大于维护的自增值，则自增值增长，否则不变
auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。
在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突
3、自增主键 id 不连续的原因
1）唯一键冲突。写入 (0,1,1);没有指定自增 id 的值，获取表 t 当前的自增值 2；将传入的行的值改成 (2,1,1);将表的自增值改成 3；继续执行插入数据操作，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。
**2）事务回滚，**类似唯一键冲突
3）**批量插入时，申请id的策略。**每次申请到的自增 id 个数都是上一次的两倍，不用的会丢弃
4、为什么自增值为什么不能回退？
假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4
事务 B 正确提交了，但事务 A 出现了唯一键冲突。如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况**：表里面已经有 id=3 的行，而当前的自增 id 值是 2。**接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。要解决这个问题都会导致性能问题
**5、**自增锁
自增 id 锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请
MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。
这个参数的值被设置为 0 时，语句执行结束后才释放锁；
这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁
为什么批量插入自增锁释放规则不同？
binlog_format=statement的时候，如果事务a，b并发批量插入，a、b的id不是连续的，但是binlog只能要么先记 session A 的，要么先记 session B 的。备库用binlog主从同步的时候，session B生成的结果里面，id 都是连续的。这时，这个库就发生了**数据不一致。
**
两个解决方案：
**1）**让原库的批量插入数据语句，固定生成连续的 id 值。所以，自增锁直到语句执行结束才释放，就是为了达到这个目的。innodb_autoinc_lock_mode设为1
**2）**备库执行的时候，不再依赖于自增主键去生成，binlog记录实际值。innodb_autoinc_lock_mode 设置为 2，同时 binlog_format 设置为 row
40 | insert语句的锁为什么这么多？
1、insert … select
**insert … select 是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。
**
在可重复读隔离级别下，binlog_format=statement 时执行：insert into t2(c,d) select c,d from t;这个语句时，需要对表 t 的所有行和间隙加锁
事务a插入，事务b拷贝，在 binlog_format=statement 的情况下，这个语句到了备库执行，出现主备不一致。
2、insert 唯一键冲突
insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。
3、insert into … on duplicate key update
insert into … on duplicate key update 这个语义的逻辑是，插入一行数据，如果碰到唯一键约束，就执行后面的更新语句。如果有多个列违反了唯一性约束，就会按照索引的顺序，修改跟第一个索引冲突的行。
三、索引、存储原理
04 | 深入浅出索引（上）
1、不同类型索引的优劣
1）hash：读写快适合等值查询，但是无序，不方便区间查询
2）有序数组：只适用于静态存储引擎，因为写操作慢
3）搜索树：折中
2、innnoDB索引（B+树）
索引分为两类：
主键索引（聚簇索引）：主键索引的叶子节点存的是整行数据
非主键索引（非聚簇索引）：非主键索引的叶子节点内容是主键的值。需要回表操作
对于innoDB来说，一个索引就是一颗B+树；一张表的数据存在主键索引对应的树上
查找一个记录，先根据B+树找到对应叶子结点，再在节点内部二分查找
3、使用优化
1）顺序写入性能好（为什么每个表推荐用自增主键）
写操作**：页分裂**；读操作：页合并
为了减少上面情况的发生：自增主键的插入数据模式，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
2）主键长度越小越好
一是，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
二是，主键越小，一个数据页的主键就越多。而聚簇索引B+树的非叶子节点的大小取决于数据页的大小，而数据页主键越多，B+树层数就会少，读写效率高
节点和页？
数据量很大的情况下，二级索引比聚簇索引快？
%like% 什么情况下走索引
05 | 深入浅出索引（下）
1、覆盖索引
将需要的字段也放在非主键索引中（联合索引），达到避免回表的作用****
此外还有一个应用：延迟关联查询
2、最左前缀原则
如果时多字段联合索引，索引项是按照索引定义里面出现的字段顺序排序的。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了
3、索引下推
不符合最左前缀的部分，会怎么样呢？
在 MySQL 5.6 之前，只能一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
4、重建索引
通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理？
为什么要重建索引？索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高
重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是**删除主键还是创建主键，都会将整个表重建。**所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB
5、非主键索引的存储方式
非主键索引的叶子节点存放的是主键。如果非主键索引的字段包含了主键索引的字段，那么InnoDB会把主键字段放到索引定义字段后面，当然同时也会去重。
例如，当主键是(a,b)的时候，定义为c的索引，实际上是（c,a,b);定义为(c,a)的索引，实际上是(c,a,b)； 定义为(c,b）的索引，实际上是（c,b,a)
6. like
SELECT * FROM test_like WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'
like 'j' 跟 like 'j%',这两种是可以用到索引去定位数据的，like '%j' 跟 like '%j%'这两中where条件是不会使用索引去定位数据的，但是因为此时表里面字段只有id,uname，正好这两列数据在uname索引里面都有保存，优化器认为遍历普通索引比遍历主键索引的开销要小，所以会选择遍历普通索引，但是两者还是有区别的；前者explain分析可以看到type=range，后者是type=index
09 | 普通索引和唯一索引，应该怎么选择？
1、普通索引和唯一索引的查询性能对比
普通索引查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录；唯一索引来说由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
性能差距微乎其微。因为引擎是按页读写的，绝大多数情况下就只需要一次指针寻找和一次计算。
2**、change buffer**
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
change buffer是可以持久化的数据。change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。
唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用
对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了
change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。反过来，假设一个业务的更新模式是写入之后马上会做查询，由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价
3、普通索引和唯一索引的查询更新性能对比
唯一索引所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。
4、redo log与change buffer
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
执行更新时的操作步骤
在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。
merge 的执行流程是这样的：从磁盘读入数据页到内存（老版本的数据页）；从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。
**5、**普通索引和唯一索引的选择
首先，业务正确性优先。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。
然后，在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。上期问题时间
10 | MySQL为什么有时候会选错索引？
1、优化器选择索引的依据
扫描行数，是否使用临时表、是否排序、是主键索引还是非主键索引等因素进行综合判断。关键是扫描行数的判断
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好
2、索引的基数计算
没有采用行行统计，而是采用采样统计。InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计，通过设置参数 innodb_stats_persistent 的值来选择
3、弥补措施
analyze table t 命令，可以用来重新统计索引信息
采用 force index 强行选择一个索引
11 | 怎么给字符串字段加索引？
1、前缀索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？统计索引上有多少个不同的值来判断要使用多长的前缀。
使用前缀索引就用不上覆盖索引对查询性能的优化，因为前缀索引必然不是唯一索引，需要回表操作。
2、其他常用方式
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗
但是，都不支持范围扫描
13 | 为什么表数据删掉一半，表文件大小不变？
1、删除机制
delete 命令其实只是把记录的位置，或者数据页标记为了**“可复用”，但磁盘文件的大小是不会变的**。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是**“空洞”**
2、插入，更改索引上的值，都会带来空洞
3、alter table
现在有一个表 A，需要做空间收缩把表中存在的空洞去掉，可以新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。
这也是为什么mysql按主键ID递增的顺序插入数据是最合适的原因
alter table A engine=InnoDB 命令来重建表，区别只是这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作
花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新
4、Online DDL
过程：
·建立一个临时文件，扫描表 A 主键的所有数据页；
·用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
·生成临时文件的过程中，将所有对 A 的操作记录在一个**日志文件（row log）**中；
·临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
·用临时文件替换表 A 的数据文件。
DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？
alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。为了实现 Online，MDL 读锁不会阻塞增删改操作。为了保护自己，禁止其他线程对这个表同时做 DDL。而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
需要补充说明的是，操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。
5、optimize table、analyze table 和 alter table 的区别
从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是online DDL；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。
6.请问下分布式ID（雪花算法生成的ID）生成的索引会比自增长的ID性能低吗？
16 | “order by”是怎么工作的？
1、sort_buffer
MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
排序可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
2、全字段排序
1）初始化 sort_buffer，和需要返回的字段（select）；
2）根据where筛选需要的记录，取需要返回的字段，存入 sort_buffer 中；
3）对 sort_buffer 中的数据按照字段 排序字段 做快速排序；
只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。
3、rowid 排序
**如果 MySQL 认为排序的单行长度太大会怎么做呢
**
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法
只将主键id和排序字段放入sort_buffer 中，排好序再根据id获取****需要返回的字段
rowid 排序多访问了一次表 t，减少了sort_buffer的空间
4、全字段排序 VS rowid 排序
MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
5、利用索引排序
联合索引 orderby字段
设计覆盖索引：排序字段+需要返回的字段
并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定
select * from t where city in (“杭州”," 苏州 ") order by name limit 100; 这个 SQL 语句是否需要排序？有什么方案可以避免排序？
四、分布式相关
架构方式：主、从、备；主从复制、主从延迟、主备切换、读写分离
24 | MySQL是怎么保证主备一致的？
1、主从同步流程（binlog的作用）
备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。
在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog。
在备库 B 上执行 start slave 命令，这时候备库会启动两个线程： io_thread 和 sql_thread。 io_thread 负责与主库建立连接。
主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。
2、binlog 的格式
statement：binlog记录了 SQL 命令。可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引b，会有主备执行不同行的问题
row ：binlog 里面记录了真实删除行的主键 id和值，不会有主备删除不同行的问题。
3、binlog的mixed格式日志（优缺点对比）
statement 格式的 binlog 可能会导致主备不一致。
row 格式的缺点是很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。也要耗费 IO 资源，影响执行速度。
mixed 格式的 binlog：MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。
此外row还有一个好处：方便恢复数据。
执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了。
**4、****双 M 结构，**主从同步循环复制问题
双 M 结构（ 区别于M-S 结构）：节点 A 和 B 之间总是互为主备关系，在切换的时候就不用再修改主备关系。但是主从同步会有循环复制问题
解决方案：规定两个库的 server id 必须不同；一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，就直接丢弃这个日志。
25 | MySQL是怎么保证高可用的？
1、主从延迟
主备延迟：同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值。在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒
主备延迟最直接的表现是：备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢
2、原因与处理方式
1）备库所在机器的性能要比主库所在的机器性能差
2）备库的压力大，忽视了备库的压力控制。
一般可以这么处理：
一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
3）大事务。
4）备库单线程复制（旧版），主库多线程写入
3、主备切换（考虑到主备延迟的存在）
1）可靠性优先策略：
判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
把主库 A 改成只读状态，即把 readonly 设置为 true；
判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
把业务请求切到备库 B。
可以看到，这个切换流程中是有不可用时间的。
2）可用性优先策略：
不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。
3）可用性优先策略结合binlog
使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据不一致很难被发现。
26 | 备库为什么会延迟好几个小时？
1、新版备库多线程复制的原则
事务能不能按照轮询的方式分发给各个线程，也就是第一个事务分给线程1，第二个事务发给 线程2 呢？
不行。由于 CPU 的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不一致的问题。
同一个事务的多个更新语句，能不能分给不同线程来执行呢？
不行。一个事务更新了表 t1 和表 t2 中的各一行，这两条更新语句被分到不同线程的话，虽然最终的结果是主备一致的，但如果表 t1 执行完成的瞬间，备库上有一个查询，就会看到这个事务“更新了一半的结果”，破坏了事务逻辑的隔离性。
两个基本原则：
更新同一行的两个事务，必须被分发到同一个线程中。
同一个事务不能被拆开，必须放到同一个线程中。
2、基于原则的多线程复制策略（自行设计）
1）按表分发策略：如果两个事务更新不同的表，它们就可以并行；如果有跨表的事务，就把两张表放在一起
缺点：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
2）按行分发策略：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row
3、MySQL 5.6 版本的并行复制策略
官方 MySQL5.6 版本，支持了并行复制，只是支持的粒度是按库并行
构造 hash 值的时候很快，只需要库名；而且一个实例上 DB 数也不会很多，不会出现需要构造 100 万个项这种情况。不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。但是，如果你的主库上的表都放在同一个 DB 里面，这个策略就没有效果了
27 | 主库出问题了，从库怎么办？
1、一主多从基本结构
A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。
2、一主多从架构的主备切换问题
一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。正是由于多了从库 B、C、D 重新指向的这个过程，所以主备切换的复杂性也相应增加了。
把节点 B 设置成节点 A’的从库的时候，需要执行一条 change master 命令：两个参数 MASTER_LOG_FILE 和 MASTER_LOG_POS 表示主库对应的文件名和日志偏移量。相同的日志，**A 的位点和 A’的位点是不同的。**考虑到切换过程中不能丢数据，所以我们找位点的时候，总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库 B 上已经执行过的事务。
存在这样一个问题：在从库 B 上，由于同步了 A的binlog， R 这一行已经存在；在新主库 A’上， R 这一行也已经存在，日志是写在这之后的；我们在从库 B 上执行 change master 命令，指向 A’的 File 文件的 位置，就会把插入 R 这一行数据的 binlog 又同步到从库 B 去执行。从库 B 的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 错误，提示出现了主键冲突，然后停止同步。
所以，通常情况下，我们在切换任务的时候，要先主动跳过这些错误，有两种常用的方法。一种做法是，主动跳过一个事务。
另外一种方式是，通过设置 slave_skip_errors 参数，直接设置跳过指定的错误。在执行主备切换时，有这么两类错误，是经常会遇到的：1062 错误是插入数据时唯一键冲突；1032 错误是删除数据时找不到行。
**3、**GTID
MySQL 5.6 版本引入了 GTID。GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。
它由两部分组成，格式是**：GTID=server_uuid:gno**
其中：server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。
4、基于 GTID 的主备复制
CHANGE MASTER TO命令，MASTER_LOG_FILE 和 MASTER_LOG_POS 参数，已经不需要指定了。因为master_auto_position=1 就表示这个主备关系使用的是 GTID 协议
在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：
实例 B 指定主库 A’，基于主备协议建立连接。
实例 B 把 set_b 发给主库 A’。
实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务
a. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；
b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给B；之后就从这个事务开始，按顺序取 binlog 发给 B 去执行
设计思想：在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。这跟基于位点的主备协议不同。基于位点的协议，是由备库决定的，备库指定哪个位点，主库就发哪个位点，不做日志的完整性判断。
28 | 读写分离有哪些坑？
1、读写分离架构
客户端直连方案：少了一层 proxy 转发，所以查询性能稍好，整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
带 proxy 的架构：对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但带 proxy 架构的整体就相对比较复杂。
2、由主从延迟导致的读写分离问题与方案
在从库上会读到系统的一个过期状态的现象称之为**“过期读”**。虽然有各种优化方法，主从延迟还是不能 100% 避免的。
1）对于必须要拿到最新结果的请求，强制将其发到主库上。这个方案是用得最多的，但是如果所有查询都不能是过期读，只能放弃读写分离，所有读写压力都在主库
2）主库更新后，读从库之前先 sleep 一下
3）判断主备无延迟。每次从库执行查询请求前，先判断 **seconds_behind_master 是否已经等于 0。**如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。此外，还可以对比binlog位点、对比 GTID 集合。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。如果这两个集合相同，也表示备库接收到的日志都已经同步完成。。可见，对比位点和对比 GTID 这两种方法，都要比判断 seconds_behind_master 是否为 0 更准确。
4）semi-sync 方案：事务提交的时候，主库把 binlog 发给从库；从库收到 binlog 以后，发回给主库一个 ack，表示收到了；主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。但是，在持续延迟的情况下，可能出现过度等待的问题
tips：如果主库掉电的时候，有些 binlog 还来不及发给从库，如果使用的是普通的异步复制模式，就可能会丢失数据，但 semi-sync 就可以解决这个问题。
五、其他
14 | count(*)这么慢，我该怎么办？
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数
为什么innoDB不直接存储count（*）？MVCC导致必须遍历每一行并判断
show table status 命令虽然返回很快，但是不准确。采样统计，不是实时统计，也是有时候选择索引出错的原因
Redis计数？分布式一致性无法实时一致
解决方案：利用事务和innoDB隔离机制，在数据库中单独存一行保存数量；提高并发读，在多行统计，并逻辑合并统计
按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)
17 | 如何正确地显示随机消息？
19 | 为什么我只查一行的语句，也执行这么慢？
show processlist 命令
锁
等 MDL 锁（找到谁持有 MDL 写锁，然后把它 kill 掉）
等 flush（出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句）
等行锁：select * from t where id=1 lock in share mode。要加读锁。如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。
mvcc
回滚日志 (undo log)太多，反而没有当前读来的快
22 | MySQL有哪些“饮鸩止渴”提高性能的方法？
29 | 如何判断一个数据库是不是出问题了？
33 | 我查这么多数据，会不会把数据库内存打爆？
1. net_buffer。MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长
2. 全表扫描对 InnoDB 的影响
内存命中率
按照普通lru算法扫描的话，就会把当前的 Buffer Pool 里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。实际上，InnoDB 对 LRU 算法做了改进
整个 LRU 链表分成了 young 区域和 old 区域
在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率
