git-zjx / git-zjx.github.io Goto Github PK

MySQL 架构

Server 层

包括 连接器、查询缓存、分析器、优化器和执行器等，涵盖 MySQL 的大部分核心功能，所有的内置函数（例如：日期，数字和加密函数）和所有的跨存储引擎的功能（例如：存储过程、触发器和视图）都在这层

存储引擎层

负责数据的读取和存储

连接过程

1. 连接器

客户端通过连接器和服务端进行连接，经过 TCP 三次握手之后，连接器开始验证客户端身份，验证通过之后，连接器会去权限表获取权限，之后这个连接里面的所有权限判断都依赖此时获取的权限（也就意味着就算管理员修改了该用户的权限，也不会影响该连接）
连接完成之后，如果不使用会处于空闲状态，使用 show processlist 可以查看。 如果客户端长时间没有响应，连接器会断开该连接，时间由 wait_timeout 控制，默认为 8 小时

长连接和短链接

数据库里面，长连接是指连接成功之后，如果客户端持续有请求，则使用同一个连接。短连接是指每次执行很少的几次查询之后就断开连接，下一次查询再重新建立连接

建议尽量减少连接的创建动作，因为创建连接的过程通常比较复杂。但因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候释放，所以长连接积累下来会导致内存占用过大，会被系统强行杀掉（OOM）。可以考虑一下两种方案解决：

1. 定期断开长连接
2. MySQL 5.7 或更新版本可以使用 mysql_reset_connection 重新初始化连接资源，该操作只会将连接还原成初始状态，不会再去重连和重做权限验证

2. 查询缓存

MySQL 拿到查询请求之后，会先去查询缓存内查找之前该查询是否执行过，执行过的查询及其结果会以 key-value 的形式存储于内存中，如果在就直接返回，不在就执行下一阶段

查询缓存的弊大于利

查询缓存失效的非常频繁，只要表有改动，在这个表上的所有查询缓存都会失效
可以设置 query_cache_type 为 DEMAND，这样之后默认的语句都不会使用查询缓存，如果确定使用查询缓存可以使用 SQL_CACHE 显示指定

3. 分析器

若查询缓存没有命中，就会到达分析器，分析器会进行词法分析和语法分析让 MySQL 知道你要做什么。
词法分析会分析出一条 SQL 语句中的字符串是什么，代表什么，从 SQL 语句中 SELECT 关键字识别出这是一条查询语句，把字符串 T 识别成表名 T ，字符串 ID 识别成列 ID（所以不存在列的错误会在分析器内抛出）。
根据词法分析的结果，语法分析会根据语法规则判断该 SQL 是否符合语法，如果语法错误会抛出，而且语法错误会提示第一个错误出现的位置

4. 优化器

进过分析器处理之后会进入优化器，MySQL 会在开始执行之前对 SQL 语句进行优化处理。
优化器主要决定多个索引时使用哪个索引，JOIN 时决定连接顺序等，选择完成之后进入执行器

5. 执行器

执行器会在开始执行前判断是否有执行查询的权限（查询缓存命中后，会在返回结果时也做一次权限判断。也会在优化器之前做一次权限判断），没有权限会抛出权限错误，有权限会打开表继续执行
大体的执行流程如下：

1. 调用引擎接口读取表的第一行，判断值是否符合，不符合跳过，符合就存放在结果集中
2. 调用引擎接口读取下一行，执行相同逻辑，直到表的最后一行
3. 执行器将结果集返回给客户端

全局锁

对整个数据库实例加锁，MySQL 提供一个加全局读锁的命令：Flush tables with read lock（FTWRL），命令执行后，数据更新语句（增删改）、数据定义语句（建表和修改表结构等）和更新类事务都会被阻塞，经常用于数据库备份场景

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

参数 -single-transaction 需要数据库引擎支持事务，如果不支持就只能使用 FTWRL 方案

为什么不使用 set global readonly = true 方式？

1. 有些系统中 readonly 会被用作其他逻辑，例如判断是主库还是从库
2. 异常处理机制有差异，FTWRL 后客户端异常断开，MySQL 会自动释放全局锁，库恢复正常；设置readonly，客户端异常，则会保持 readonly，会导致长时间处于不可写状态，风险较高
3. readonly 对 super 权限无效

表级锁

MySQL 的表级锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）

lock tables … read/write 语句用于加表锁，使用 unlock tables 可以释放锁，客户端断开连接时也会释放锁
需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象
举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

在 MySQL 5.5 版本中引入了 MDL，MDL 不需要显式调用，在访问一个表时自动调用，主要保证读写的正确性，防止DDL和DML并发的冲突

当表做增删改查操作时，加MDL读锁；当对表结构变更的时候，加MDL写锁

• 读锁之间不互斥，多线程可对同一张表增删改查
• 读写锁之间、写锁之间互斥。两个线程同时给一个表增加字段，则第二个需要等待第一个执行完才能继续

小表加字段为什么会导致整个库崩溃？

Session A 会给表 t 加 MDL 读锁，Session B 需要的也是读锁，可以执行
Session C 需要 MDL 写锁，但 Session A 的 MDL 读锁还未释放，需要等待
Session D 需要 MDL 读锁，但是因为 MySQL 内部维护了一个队列决定执行顺序，Session C 阻塞了 Session D
这里可以知道事务中的 MDL 锁会等到整个事务结束后再释放

如何安全的给小表加字段？

• 解决长事务
• 在 alter table 语句中设置等待时间

案例一：条件字段函数操作

B+ 树依靠同一层兄弟节点的有序性而拥有快速定位能力，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但优化器并不是要放弃使用这个索引，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引

对于不改变有序性的函数，也不会考虑使用索引，例如：select * from tradelog where id + 1 = 10000

案例二：隐式类型转换

在 MySQL 中，如果查询字段类型和参数类型不一致时，会执行类型转换，也就会触发案例一的情况。
字符串和数字做比较会将字符串转换成数字

案例三：隐式字符编码转换

表连接查询的时候如果两个表的字符集不同则用不上关联字段的索引，有两种办法解决：

• 将两个表的字符集修改一致
• 查询时主动转换关联字段字符集

全表扫描对 Server 的影响

假设执行以下语句进行全表扫描：

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

取数据和发数据的流程是这样的：

1. 获取一行，写到 net_buffer 中。这块内存的大小由参数 net_buffer_length 定义，默认为 16K
2. 重复获取行，知道 net_buffer 写满，调用网络接口发出去
3. 如果发生成功，就清空 net_buffer ，然后继续取下一行，并写入 net_buffer
4. 如果发生函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
   
   从图中可以知道：
5. 一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 的大小
6. socket send buffer 也不会太大，如果 socket send buffer 被写满，就会暂停读数据的流程
   所以内存是不会被打爆。
   并且也可以知道，MySQL 是边读边发的，这也就意味着，如果客户端接收的慢，会导致 MySQL 服务端由于结果发送不出去，这个事务的执行时间会变长。

如果故意让客户端不去读 socket receive buffer 中的内容，然后在服务端 show processlist 可以看到：

State 值会处于 Sending to client 状态，表示服务器端的网络栈写满了。
真实场景中，如果客户端使用 -quick 参数，MySQL 会使用 mysql_use_result 方式，而这个方法是读一行处理一行，如果一个业务的逻辑比较复杂，每读一行数据都处理的很慢，就会导致客户端要过很久才会去取下一行的数据，就会出现上述的情况
因此，对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用 mysql_store_result 接口，直接把查询结果保存到本地内存
如果在 MySQL 中看到多个线程都处于 Sending to client 状态，就应该去优化查询结果，并评估返回结果是否过多。如果要减少处于这个状态的线程，可以将 net_buffer_length 参数调大

与 Sending to client 长的类似的状态是 Sending data，Sending data 并不一定是指正在发送数据，也可能是处于执行器过程中的任意阶段。
实际上，一个查询语句的变化状态是这样的（略去了其他无关状态）：

• MySQL 查询语句进入执行阶段后，首先把状态设置为 Sending data
• 然后，发送执行结果的列相关信息（meta data）给客户端
• 再继续执行语句的流程
• 执行完成后，把状态设置成空字符串
  可以通过构造一个锁等待的场景，就能看到 Sending data 状态：
  
  
  总的来说，Sending to client 状态表示一个线程处于等待客户端接收结果的状态，Sending data 状态表示正在执行

全表扫描对 InnoDB 的影响

InnoDB 内存的数据页是在 Buffer Pool（BP）中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。其实，Buffer Pool 也有加速查询的作用。
这里涉及到一个问题：由于有 WAL 机制，当事务提交的时候，磁盘上的数据页是旧的，如果这时候马上有一个查询要读这个数据页，是不是要把 redo log 应用到数据页呢？
答案是不需要，因为这时候内存数据页的结果是最新的，直接读内存页即可，所以说 Buffer Pool 有加速查询的作用

但是 Buffer Pool 对查询的加速效果依赖于内存命中率。可以在 show engine innodb status 结果中查看系统的当前的 BP 命中率（Buffer pool hit rate）。一般情况下，一个稳定的线上系统，要保证响应时间符合要求，内存命中率要在 99% 以上

InnoDB Buffer Pool 的大小由参数 innodb_buffer_pool_size 决定，一般建议设置成可用物理内存的 60%~80%

InnoDB 内存管理用的是最近最少使用（Last Recently Used，LRU）算法，不过对其进行了改进。因为原始的 LRU 算法在进行全表扫描时，会把 Buffer Pool 中的数据全部淘汰，存入扫描过程中访问到的数据，这会导致 Buffer Pool 的内存命中率下降，磁盘压力增加，SQL 语句响应变慢

在 InnoDB 实现上，按照 5：3 的比例把整个 LRU 链表分成了 young 区域和 old 区域，如下图：

图中 LRU_old 指向的是 old 区域的第一个位置，是整个链表的 5/8 处。
改进后的 LRU 算法执行流程如下：

1. 图中状态 1 要访问数据页 P3，由于 P3 在 young 区域，因此将其移到链表头部，变成状态 2
2. 之后要访问一个新的不存在与当前链表的数据页，这时候要淘汰数据页 Pm，但新插入的数据页 Px，是放在 LRU_old 处
3. 处于 old 区域的数据页，每次被访问的时候都要做如下判断：

• 若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部
• 若这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变
  1 秒这个时间由参数 innodb_old_blocksZ_time 控制，默认为 1000，单位毫秒

前缀索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引

mysql> alter table SUser add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，但如果长度不合适就会导致查询的次数过多，可以通过统计索引上有多少个不同的值来判断要使用多长的前缀

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

而且使用前缀索引之后，就无法使用覆盖索引，因为系统并不确定前缀索引的定义是否截断了完整信息，所以还得去 ID 索引查询一遍

其他方式

倒序存储

遇到前缀的区分度不够好的情况时，可以使用倒序存储

mysql> select field_list from t where id_card = reverse('input_id_card_string');

hash 字段

在表上新建一个 hash 字段，存储字符串的 hash 值

使用倒序存储和使用 hash 字段这两种方法的异同点

相同点

都不支持范围查询，倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，hash 只支持等值查询

不同点

• 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
• 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
• 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复

binlog 的写入机制

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache

可以看到，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

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

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志

redo log 的写入机制

redo log 可能存在的三种状态，对应图中三种颜色

这三种状态分别是：

1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

注意，事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些 redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的 redo log，也是可能已经持久化到磁盘的。

以下两种场景也会让一个没有提交的事务的 redo log 写入到磁盘中：

1. redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
2. 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

如果把 innodb_flush_log_at_trx_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的
每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了
通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog

组提交机制

为了优化写日志时的刷磁盘问题，MySQL 用到了组提交机制。 组提交**是，将多个事务redo log的刷盘动作合并，减少磁盘顺序写。
Innodb的日志系统里面，每条redo log都有一个日志逻辑序列号（log sequence number，LSN），LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log

从图中可以看到：

1. trx1 是第一个到达的，会被选为这组的 leader；
2. 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3. trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
4. 这时候 trx2 和 trx3 就可以直接返回了

一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。也就是说，在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好

图中，实际上写 binlog 是分成两步的：

1. 先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件；
2. 调用 fsync 持久化

MySQL 为了让组提交的效果更好，把 redo log 做 fsync 的时间拖到了步骤 1 之后。也就是说，上面的图变成了这样：

这么一来，binlog 也可以组提交了。在执行图 5 中第 4 步把 binlog fsync 到磁盘时，如果有多个事务的 binlog 已经写完了，也是一起持久化的，这样也可以减少 IOPS 的消耗。不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用
2. fsync;binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync

问题

1. WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？
   WAL 机制主要得益于两个方面：

• redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
• 组提交机制，可以大幅度降低磁盘的 IOPS 消耗

2. 如果你的 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。这样做的风险是，主机掉电的时候会丢数据。

3. 执行一个 update 语句以后，我再去执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢？
   这可能是因为 WAL 机制的原因。update 语句执行完成后，InnoDB 只保证写完了 redo log、内存，可能还没来得及将数据写到磁盘

4. 为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？
   MySQL 这么设计的主要原因是，binlog 是不能“被打断的”。一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中。redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中

5. 事务执行期间，还没到提交阶段，如果发生 crash 的话，redo log 肯定丢了，这会不会导致主备不一致呢？
   不会。因为这时候 binlog 也还在 binlog cache 里，没发给备库。crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的

6. 如果 binlog 写完盘以后发生 crash，这时候还没给客户端答复就重启了。等客户端再重连进来，发现事务已经提交成功了，这是不是 bug？
   不是
   你可以设想一下更极端的情况，整个事务都提交成功了，redo log commit 完成了，备库也收到 binlog 并执行了。但是主库和客户端网络断开了，导致事务成功的包返回不回去，这时候客户端也会收到“网络断开”的异常。这种也只能算是事务成功的，不能认为是 bug。
   实际上数据库的 crash-safe 保证的是：

• 如果客户端收到事务成功的消息，事务就一定持久化了；
• 如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了；
• 如果客户端收到“执行异常”的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了。

参考
MYSQL-GroupCommit

两个容易误解的概念

• 内存表：指的是使用 Memory 引擎的表，表的数据都保存在内存里，系统重启的时候会被情况，但是表结构还在
• 临时表：可以使用各种引擎类型。如果使用 InnoDB 引擎或者 MyISAM 引擎的临时表，数据是写到磁盘上的，也可以使用 Memory 引擎

临时表的特性

1. 建表语法是 creat temporary table ...
2. 一个临时表只能被创建它的 session 访问，对其他线程不可见
3. 临时表可以和普通表同名
4. session 内有同名的临时表和普通表时，show create 语句，以及增删改查语句访问的是临时表
5. show tables 命令不显示临时表
6. session 结束时，临时表会自动销毁

临时表适合 join 优化的原因

1. 不同 session 的临时表是可以重名的，如果多个 session 同时执行 join 优化，不需要担心因表名重复导致建表失败的问题
2. 不需要担心数据删除的问题，临时表会自动回收

临时表在分库分表场景下的应用

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如，将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上，如下图：

在这个架构中，是以字段 f 作为分区键，如果查询条件中没有字段 f，例如：

select v from ht where k >= M order by t_modified desc limit 100;

这种情况就只能到所有分区中查询满足条件的数据，一般有两种解决方案：

1. 在 proxy 层的进程代码中实现排序
   优势是速度快，拿到分库的数据之后，直接在内存中参与计算。
   缺点在于需要开发的工作量比较大，特别是涉及到复杂的操作，而且对 proxy 端的压力比较大，容易出现内存不够和 CPU 瓶颈问题
2. 将各分库的数据汇总到一个 MySQL 实例的表中，然后操作该表
   执行流程大致为：

• 在汇总库上创建一个临时表 temp_ht
• 在各个分库上执行

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

• 把分库执行的结果插入到 temp_ht 中
• 在汇总表中执行

select v from temp_ht order by t_modified desc limit 100;

流程图如下：

临时表可以重名的原因

create temporary table temp_t(id int primary key)engine=innodb;

这个语句执行时，MySQL 要给这个 InnoDB 表创建一个 frm 文件保存表结构定义，这个 frm 文件放在临时文件目录下，文件名的后缀是 .frm，前缀是 #sql{进程id}_{线程id}_序列号

select @@tmpdir 命令可以查看临时文件目录。

而表中数据的存放方式，在不同的 MySQL 版本中有不同的处理方式：

• 在 5.6 以及之前的版本里，MySQL 会在临时文件目录下创建一个相同前缀、以 .ibd 为后缀的文件，来存放数据文件
• 在 5.7 之后，MySQL 引入了一个临时文件表空间，专门用来存放临时文件数据

从文件的前缀规则来看，创建一个叫做 t1 的临时表和普通表 t1 在存储上是不同的，所以可以和普通表重名。
而且 MySQL 维护数据表时，除了物理上有文件外，内存里也有一套机制区别不同的表，每个表都对应一个 table_def_key

• 一个普通表的 table_def_key 的值是由 库名+表名 得到的
• 一个临时表的 table_def_key 的值在 库名+表名 的基础上又加入了 server_id + thread_id
  也就是说，session A 和 session B 创建的两个临时表 t1，它们的 table_def_key 不同，磁盘文件名也不同，所以可以并存。

在实现上，每个线程都维护了自己的临时表链表，每次 session 内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表；在 session 结束的时候，对链表里的每个临时表，执行 DROP TEMPORARY TABLE + 表名 的操作

临时表和主备复制

在 statement 和 mixed 格式下，所有对临时表的操作都要记录 binlog，但在 row 格式下，只有 Drop table 才会记录 binlog。

因为在一些特殊情况下，还是需要将主机的临时表同步到备机的，比如主机上执行 insert into t1 select * from temp1，其中t1是普通表，而temp1是临时表。当binlog格式为statement时，这条语句会被记录到binlog，然后同步到备机，在备机上replay，若备机之前没有将主机上的临时表同步过来，那这条语句的replay就会出现问题。因此在statement格式下，对临时表的操作如创建、删除及其它DML语句都必须记录binlog，然后同步到备机执行replay。但在row格式下，因为binlog中已经记录了实际的row，那么对临时表的创建、DML语句是不是记录binlog就不是那么重要了

对临时表的删除还是要记录binlog。因为用户可以随时修改binlog的格式，若之前创建临时表时是statement格式，而创建成功后，又修改为row格式，若row格式下删除表不记录binlog，那么在备机上就会产生问题，创建了临时表，但却没有删除它。因此对drop table语句，无论binlog格式采用statement或是row格式，都会记录binlog

join 语句有两种算法，分别是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)。对 join 语句的优化，也会涉及到对这两种算法的优化

create table t1(id int primary key, a int, b int, index(a));
create table t2 like t1;
drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, 1001-i, i);
    set i=i+1;
  end while;
  
  set i=1;
  while(i<=1000000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;

end;;
delimiter ;
call idata();

Multi-Range Read 优化

Multi-Range Read(MRR) 优化的主要目的是尽量使用顺序读盘。

select * from t1 where a>=1 and a<=100;

在 InnoDB 中，这个语句会在普通索引 a 上查到主键 id 的值后，再根据一个个主键 id 的值到主键索引上获取整行的数据。而且，在主键索引这棵 B+ 树上，每次只能根据一个主键 id 查到一行数据

这样，随着 a 的值的递增，id 的值就会变成随机的，就会出现随机访问的问题，导致性能降低。
MRR 优化的思路就是，大多数的情况下，数据都是按照主键递增的顺序插入的，所以可以认为如果按照主键递增顺序查询，对磁盘来说就比较接近顺序读，会提高读性能。

MRR 优化后的执行流程：

1. 根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中
2. 将 read_rnd_buffer 中的 id 进行递增排序
3. 排序后的 id 数组，依次到主键 id 索引中查数据，并作为结果返回
   
   read_rnd_buffer 的大小由 read_rnd_buffer_size 参数控制，如果步骤 1 中 read_rnd_buffer 满了，会先执行步骤 2 和步骤 3，然后清空 read_rnd_buffer，之后再继续执行步骤 1

如果需要稳定的使用 MRR，需要设置 set optimizer_switch="mrr_cost_based=off"（官方文档说法：现在优化器的策略在判断消耗的时候，会更倾向于不使用 MRR，这里把 mrr_cost_based 设置为 off，就是固定使用 MRR）

explain 结果中，如果 Extra 字段中有 Using MRR，就表示用上了 MRR 优化

而且，由于 read_rnd_buffer 中按照主键 id 做了排序，所以最后得到的结果也是按照主键 id 递增的，也就是说如果存在 order by 的话，会用不到 MRR

Batched Key Access

MySQL 5.6 引入了 Batched Key Access (BKA) 算法，是对 NLJ 算法的优化

NLJ 算法的执行流程：

从驱动表 t1，一行行的取出 a 的值，再到被驱动表 t2 去做 join，对于 t2 来说，每次都是匹配一个值

而对于 BKA 算法来说，执行流程就是先把表 t1 的数据取出一部分放到 join_buffer 中，然后在把这部分数据一次性传给 t2，这里就用到了 MRR 的优化

图中，join_buffer 中的 P1 ~ P100 表示的是只会取查询需要的字段

如果要使用 BKA 优化算法，需要先设置：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

前两个参数作用是启用 MRR，因为 BKA 算法的优化依赖于 MRR

BNL 算法的性能问题

1. 可能会多次扫描被驱动表，占用磁盘 IO 资源
2. 判断 join 条件需要执行 M*N 次对比（M、N 分别是两张表的行数），如果是大表就会占用非常多的 CPU 资源
3. 可能会导致 Buffer Pool 的热数据被淘汰，影响内存命中率

BNL 算法的优化

1. BNL 转 BKA
   通常可以直接在被驱动表上建索引，转成 BKA 算法。
   但有些情况下不适合在被驱动表上建索引，就可以使用临时表的方案，大致思路是：

• 把表 t2 满足条件的数据放在临时表 tmp_t 中
• 为了让 join 使用 BKA 算法，给临时表 tmp_t 的字段 b 加上索引
• 让表 t1 和 tmp_t 做 join 操作

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

2. 扩展 hash join
   MySQL 不支持哈希 join，但是我们可以在业务端实现类似的流程，实际流程如下：

• select * from t1; 取得 t1 的全部 1000 行数据， 在业务端存入一个 hash 结构，比如 c++ 里的 set、PHP 的数组这样的数据结构
• select * from t2 where b>= 1 and b <= 2000; 获取 t2 中满足条件的 2000 行数据
• 把这 2000 行数据，一行一行的取到业务端，到 hash 结构的数据表中寻找匹配的数据，满足条件的数据作为结果集的一行

你的 SQL 语句为什么变“慢”了

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

什么情况会引发数据库的 flush 过程呢？

1. InnoDB 的 redo log 写满了
   这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写，checkpoint 往前推进时，会把对应的所有脏页都 flush 到磁盘上
2. 系统内存不足
   当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
   刷脏页一定会写盘，就保证了每个数据页有两种状态：
   a. 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
   b.另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。
3. MySQL 认为系统“空闲”的时候
4. MySQL 正常关闭的情况
   MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快

四种场景对性能的影响

1. “redo log 写满了，要 flush 脏页”
   这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
2. “内存不够用了，要先将脏页写到磁盘”
   这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
   a. 第一种是，还没有使用的；
   b. 第二种是，使用了并且是干净页；
   c. 第三种是，使用了并且是脏页。
   InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
   所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
   a. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
   b. 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。
   所以，InnoDB 需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

InnoDB 刷脏页的控制策略

innodb_io_capacity 参数会告诉 InnoDB 你的磁盘能力，这样 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 

innodb_max_dirty_pages_pct 参数是脏页比例上限，默认值是 75%。InnoDB 会根据当前的脏页比例（假设为 M），算出一个范围在 0 到 100 之间的数字 F1(M) ，InnoDB 每次写入的日志都有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值，我们假设为 N。InnoDB 会根据这个 N 算出一个范围在 0 到 100 之间的数字 F2(N)，根据上述算得的 F1(M) 和 F2(N) 两个值，取其中较大的值记为 R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因
要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%
其中，脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具体的命令参考下面的代码：

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

MySQL 在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。
在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

第一类：查询长时间不返回

一般碰到这种情况的话，大概率是表 t 被锁住了。分析原因时一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态

等 MDL 锁

出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可(MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)

等 flush

这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。也可能是有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句
这类问题的处理方式，可以执行 show processlist 查看结果，然后 kill 掉堵塞的语句

等行锁

这个问题并不难分析，但问题是怎么查出是谁占着这个写锁。如果你用的是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到。

mysql> select * from sys.innodb_lock_waits where locked_table='`test`.`t`'\G

可以看到，这个信息很全，4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是 KILL QUERY 4 或 KILL 4。不过，这里不应该显示 KILL QUERY 4。这个命令表示停止 4 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句，这个语句已经是之前执行完成了的，现在执行 KILL QUERY，无法让这个事务去掉 id=1 上的行锁。实际上，KILL 4 才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

第二类：查询慢

没有索引

mysql> select * from t where c=50000 limit 1;

由于字段 c 上没有索引，这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行，随着数据量的增加，执行的时间会越来越长，坏查询不一定是慢查询

回滚日志太大

假如生成了 100w 条回滚日志，如果是一致性读的话，会依次执行 undo log，执行 100w 次之后才把结果返回

count(*) 的实现方式

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；
InnoDB 引擎执行 count() 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

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

即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，每一行记录都要判断自己是否对这个会话可见

InnoDB 对 count(*) 的优化

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

其他计数方法

1. 缓存计数
   丢失更新，逻辑不精确（在并发系统里面，无法精确控制不同线程的执行时刻的）
2. 数据库计数
   利用事务存储计数

不同的 count 用法

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值

分析性能差别的时候，你可以记住这么几个原则：

• server 层要什么就给什么；
• InnoDB 只给必要的值；
• 优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
对于 count(字段) 来说，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
对于 count(*) 来说， 并不会把全部字段取出来，而是专门做了优化，不取值，按行累加

结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以建议尽量使用 count(*)

解决 IDEA 下的 Terminal 中文乱码（Unicode 编码）的方法

前提

业务代码已经保证了不会写入两个重复的信息

对查询过程的影响

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

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16 KB。

因此，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

关于 change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。
在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
change buffer 是可以持久化的数据，在内存中有拷贝，也会被写入到磁盘上。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。
除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。
change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
change buffer 只能二级索引使用，因为唯一索引需要判断数据是否存在，需要把数据页读取到内存中，也就没有使用 change buffer 的必要了

对更新过程的影响

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

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

change buffer 的使用场景

适合写多读少的场景，在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大

索引选择和实践

建议你尽量选择普通索引，如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能

change buffer 和 redo log

执行以下语句的操作过程

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
   将上述两个动作记入 redo log 中（图中 3 和 4）。

执行以下语句的操作过程

select * from t where k in (k1, k2)

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

redo log 主要节省的是随机写磁盘的 IO 消耗（随机写磁盘转成顺序写redo log），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗(写操作不用先读数据页到内存)

将 IDEA Terminal 命令窗口修改为 Git bash 命令窗口
1、打开 settings 设置界面，选择 Tools 中的 Terminal (File -> settings -> Tools -> Terminal)
2、修改 Shell path 为你的 Git bash 安装路径

修改 Git 的安装目录下的bash.bashrc文件

# 解决 IDEA 下的 terminal 中文 Unicode 编码问题
export LANG="zh_CN.UTF-8"
export LC_ALL="zh_CN.UTF-8"

重启 Terminal 即可

答疑

示例

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
7. 按照排序结果取前 1000 行返回给客户端。

图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

确定一个排序语句是否使用了临时文件的办法

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看到是否使用了临时文件，number_of_tmp_files 表示的是，排序过程中使用的临时文件数，内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。

如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大

examined_rows 表示参与排序的行数
sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

rowid 排序

如果需要返回的列的总长度大于 max_length_for_sort_data 则会使用rowid 排序算法。新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。

SET max_length_for_sort_data = 16;

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 ciy='杭州’条件的主键 id，也就是图中的 ID_X；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
   

rowid 排序多访问了一次表 t 的主键索引

需要说明的是，最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

从 OPTIMIZER_TRACE 的结果中，你还能看到另外两个信息也变了。

• sort_mode 变成了 <sort_key, rowid>，表示参与排序的只有 name 和 id 这两个字段。
• number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计**：如果内存够，就要多利用内存，尽量减少磁盘访问。

什么情况下 ORDER BY 不用排序

查询使用到联合索引或者使用了覆盖索引

重新安装 SVN ，勾选 command line client tools 安装命令行工具，之后重启 PhpStorm 即可

对于显示随机消息，一般会想到用 order by rand() 实现，以下是 explain 结果

Extra 字段表示会用到临时表，会进行排序操作

内存临时表

对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越小越好了，所以，MySQL 这时就会选择 rowid 排序。

这条语句的执行流程是这样的：

1. 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
3. 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
5. 从内存临时表中一行一行地取出 R 值和位置信息，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
6. 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

位置信息 rowid 是每个引擎用来唯一标识数据行的信息。

• 对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
• 对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
• MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

磁盘临时表

tmp_table_size这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表
磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的
当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程

MySQL 5.6 版本引入了优先队列排序算法，可以减少很多计算量。

随机排序方法-1

1. 取得这个表的主键 id 的最大值 M 和最小值 N;
2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
3. 取不小于 X 的第一个 ID 的行。
   这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但实际上，这个算法本身并不严格满足题目的随机要求，因为 ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机

随机排序方法-2

1. 取得整个表的行数，并记为 C。
2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
3. 再用 limit Y,1 取得一行。

MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高

简介

MySQL 在 5.7.2 版本中新增了 log_timestamps 控制 error log、slow_log、genera log，等等记录日志的显示时间参数，但不会影响 general log 和 slow log 写到表 (mysql.general_log, mysql.slow_log) 中的显示时间

设置

可以被设置的值有：UTC 和 SYSTEM，默认使用 UTC

log_timestamps='SYSTEM'

误删行
如果是使用 delete 语句误删了数据行，可以用 Flashback 工具通过闪回把数据恢复回来

Flashback 恢复数据的原理，是修改 binlog 的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保 binlog_format=row 和 binlog_row_image=FULL

具体恢复数据时，对单个事务做如下处理：

1. 对于 insert 语句，对应的 binlog event 类型是 Write_rows event，把它改成 Delete_rows event 即可；
2. 同理，对于 delete 语句，也是将 Delete_rows event 改为 Write_rows event；
3. 而如果是 Update_rows 的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可

对于多个事务，例如：

(A)delete ...
(B)insert ...
(C)update ...

现在要把数据库恢复回这三个事务操作之前的状态，用 Flashback 工具解析 binlog 后，写回主库的命令是：

(reverse C)update ...
(reverse B)delete ...
(reverse A)insert ...

也就是说，如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行

场景一：短链接风暴
正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。在业务高峰期的时候，就可能出现连接数突然暴涨的情况
max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示Too many connections。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用

解决办法一：先处理掉那些占着连接但是不工作的线程

max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置 wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接

优先断开事务外空闲太久的连接，再考虑断开事务内空闲太久的连接

可以使用 show processlist 和 select * from information_schema.innodb_trx; 查看空闲情况

从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错ERROR 2013 (HY000): Lost connection to MySQL server during query。从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”

解决办法二：减少连接过程的消耗

跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内
在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接

慢查询性能问题

原因一：索引没有设计好

最高效的做法就是直接执行 alter table 语句紧急创建索引

比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
2. 执行主备切换；
3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

这是一个“古老”的 DDL 方案。平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥

原因二：语句没写好

我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();

call query_rewrite.flush_rewrite_rules() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”

原因三：MySQL 选错了索引

应急方案就是给这个语句加上 force index。
同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题

预防方案

1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
2. 在测试表里插入模拟线上的数据，做一遍回归测试；
3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致

QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务

下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用：

• 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
• 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
• 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

会存在两个副作用：

• 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
• 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败

简介

数据库访问 DAO 为不同的数据库提供了一套统一的API
查询生成器 QueryBuilder 用于创建动态的查询语句
活动记录 ActiveRecord 提供了数据库与模型(MVC 中的 M,Model) 的交互

关系

查询生成器建立在 数据库访问 DAO 基础之上，可让你创建 程序化的、DBMS无关的SQL语句
yii\db\ActiveRecord::find() 创建的 查询生成器对象 yii\db\ActiveQuery 继承自 yii\db\Query 查询生成器，所以查询生成器的所有方法活动记录 ActiveRecord 都可以用

SQL四种语言：DDL,DML,DCL,TCL

简介

事务用于保证一组操作要么全部成功，要么全部失败。
在 MySQL 中事务实现于存储引擎层。

隔离性与隔离级别

当数据库有多个事务同时执行时，就可能出现 脏读（dirty read）、不可重复读（not-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，引入了隔离级别的概念。

SQL 标准的隔离级别包括：

1. 读未提交（read uncommitted），一个事务还未提交时，它做的变更别的事务就能看到
2. 读提交（read committed），一个事务提交之后，它做的变更别的事务才能看到
3. 可重复读（repeatable read），一个事务执行过程中看到的数据，始终和事务启动时看到的数据一致
4. 串行化（serializable），对于同一行记录，写时会加写锁，读时会加读锁，后访问的事务必须等待前一个事务执行完成之后才能继续执行

事务隔离的实现

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问

在 MySQL 中，每条记录的更新都会同时记录一条回滚操作，记录上的最新值通过回滚操作都可以得到前一个状态的值。同一条记录可以在数据库中有多个版本，这就是数据库的多版本并发控制（MVCC）
回滚日志不会一直保留，当系统里没有比这个回滚日志更早的 read-view 时会被删除，因为这时已经没有事务需要使用这个回滚日志了。

为什么尽量不使用长事务？

长事务就意味着数据库中存在很老的事务视图，而由于这些事务可能随时访问数据库的任何数据，所以这些事务提交前，数据库里它们可能用到的回滚日志都必须保留，会占用大量空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库

除了对回滚段的影响，长事务还占用锁资源

事务的启动方式

1. 显式启动，begin 或 start transaction，commit 提交，rollback 回滚
2. set autocommit=0，关闭自动提交，意味着执行一个语句就会开启事务，且这个事务不会自动提交，直到执行 commit 或 rollback，或者断开连接

set autocommit=0 可能会意外的导致长事务，建议总是使用 set autocommit=1

在 set auotcommit=1 的情况下，使用 begin 启动事务，如果执行 commit 则提交事务，如果执行 commit work and chain 则提交事务并自动启动下一事务，适用于需要频繁使用事务的业务

读写分离的主要目标就是分摊主库的压力。下图中的结构是客户端（client）主动做负载均衡，这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说，由客户端来选择后端数据库进行查询。

还有一种架构是，在 MySQL 和客户端之间有一个中间代理层 proxy，客户端只连接 proxy， 由 proxy 根据请求类型和上下文决定请求的分发路由

客户端直连和带 proxy 的读写分离架构各有的特点：

1. 客户端直连方案，因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
   一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
2. 带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。

不论使用哪种架构，你都会碰到我们今天要讨论的问题：由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态。这种“在从库上会读到系统的一个过期状态”的现象，在这篇文章里，我们暂且称之为“过期读”。

主从延迟是不能 100% 避免的，有以下几种方式解决过期读的问题：

强制走主库方案

强制走主库方案其实就是，将查询请求做分类。通常情况下，我们可以将查询请求分为这么两类：

1. 对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。
2. 对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库

这个方案最大的问题在于，有时候你会碰到“所有查询都不能是过期读”的需求，比如一些金融类的业务。这样的话，你就要放弃读写分离，所有读写压力都在主库，等同于放弃了扩展性

Sleep 方案

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

这个方案的假设是，大多数情况下主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据

以卖家发布商品为例，商品发布后，用 Ajax（Asynchronous JavaScript + XML，异步 JavaScript 和 XML）直接把客户端输入的内容作为“新的商品”显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了 sleep 的目的，进而也就解决了过期读的问题。

也就是说，这个 sleep 方案确实解决了类似场景下的过期读问题。但，从严格意义上来说，这个方案存在的问题就是不精确。这个不精确包含了两层意思：

1. 如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；
2. 如果延迟超过 1 秒，还是会出现过期读。

判断主备无延迟方案

要确保备库无延迟，通常有三种做法：
第一种方法，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。seconds_behind_master 的单位是秒，如果精度要求高的话可以采取后两种做法
第二种方法，对比位点确保主备无延迟：

• Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
• Relay_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 集合。
  如果这两个集合相同，也表示备库接收到的日志都已经同步完成

在执行查询请求之前，先判断从库是否同步完成的方法，相比于 sleep 方案，准确度确实提升了不少，但还是没有达到“精确”的程度。因为一个事务的 binlog 在主备库之间有如下三个状态：

1. 主库执行完成，写入 binlog，并反馈给客户端；
2. binlog 被从主库发送给备库，备库收到；
3. 在备库执行 binlog 完成。
   我们上面判断主备无延迟的逻辑，是“备库收到的日志都执行完成了”。但是，从 binlog 在主备之间状态的分析中，不难看出还有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态。
   
   上图表示主库上执行完成了三个事务 trx1、trx2 和 trx3，其中：
4. trx1 和 trx2 已经传到从库，并且已经执行完成了；
5. trx3 在主库执行完成，并且已经回复给客户端，但是还没有传到从库中
   如果这时候你在从库 B 上执行查询请求，按照我们上面的逻辑，从库认为已经没有同步延迟，但还是查不到 trx3 的。严格地说，就是出现了过期读

配合 semi-sync
要解决这个问题，就要引入半同步复制，也就是 semi-sync replication。
semi-sync 做了这样的设计：

1. 事务提交的时候，主库把 binlog 发给从库；
2. 从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
3. 主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。
   也就是说，如果启用了 semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志
   这样，semi-sync 配合前面关于位点的判断，就能够确定在从库上执行的查询请求，可以避免过期读。

但是，semi-sync+ 位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。这时，在从库上执行查询请求，就有两种情况：

1. 如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；
2. 但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题

其实，判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者 GTID 集合更新很快，那么上面的两个位点等值判断就会一直不成立，很可能出现从库上迟迟无法响应查询请求的情况。

当发起一个查询请求以后，我们要得到准确的结果，其实并不需要等到“主备完全同步”，例如下图

图中备库 B 下的虚线框，分别表示 relaylog 和 binlog 中的事务。
可以看到，图中从状态 1 到状态 4，一直处于延迟一个事务的状态。备库 B 一直到状态 4 都和主库 A 存在延迟，如果用上面必须等到无延迟才能查询的方案，select 语句直到状态 4 都不能被执行。
但是，其实客户端是在发完 trx1 更新后发起的 select 语句，我们只需要确保 trx1 已经执行完成就可以执行 select 语句了。也就是说，如果在状态 3 执行查询请求，得到的就是预期结果了

总结来说，semi-sync 配合判断主备无延迟的方案，存在两个问题：

1. 一主多从的时候，在某些从库执行查询请求会存在过期读的现象；
2. 在持续延迟的情况下，可能出现过度等待的问题。

等主库位点方案
等主库位点方案可以解决 semi-sync 方案的两个问题

要理解等主库位点方案，需要先了解一条命令：

select master_pos_wait(file, pos[, timeout]);

这条命令的逻辑如下：

1. 它是在从库执行的；
2. 参数 file 和 pos 指的是主库上的文件名和位置；
3. timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

这个命令正常返回的结果是一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。

除了正常返回一个正整数 M 外，这条命令还会返回一些其他结果，包括：

1. 如果执行期间，备库同步线程发生异常，则返回 NULL；
2. 如果等待超过 N 秒，就返回 -1；
3. 如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0。

对于上图中先执行 trx1，再执行一个查询请求的逻辑，要保证能够查到正确的数据，我们可以使用这个逻辑：

1. trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；
2. 选定一个从库执行查询语句；
3. 在从库上执行 select master_pos_wait(File, Position, 1)；
4. 如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
5. 否则，到主库执行查询语句。

这里我们假设，这条 select 查询最多在从库上等待 1 秒。那么，如果 1 秒内 master_pos_wait 返回一个大于等于 0 的整数，就确保了从库上执行的这个查询结果一定包含了 trx1 的数据。
步骤 5 到主库执行查询语句，是这类方案常用的退化机制。因为从库的延迟时间不可控，不能无限等待，所以如果等待超时，就应该放弃，然后到主库去查

如果所有的从库都延迟超过 1 秒了，那查询压力就都跑到主库上，但按照我们设定不允许过期读的要求，就只有两种选择，一种是超时放弃，一种是转到主库查询

GTID 方案
如果你的数据库开启了 GTID 模式，对应的也有等待 GTID 的方案。

MySQL 中同样提供了一个类似的命令：

 select wait_for_executed_gtid_set(gtid_set, 1);

这条命令的逻辑是：

1. 等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；
2. 超时返回 1。

在前面等位点的方案中，我们执行完事务后，还要主动去主库执行 show master status。而 MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端，这样等 GTID 的方案就可以减少一次查询。
这时，等 GTID 的执行流程就变成了：

1. trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
2. 选定一个从库执行查询语句；
3. 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
4. 如果返回值是 0，则在这个从库执行查询语句；
5. 否则，到主库执行查询语句。
   跟等主库位点的方案一样，等待超时后是否直接到主库查询，需要业务开发同学来做限流考虑。
   
   在上面的第一步中，trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，需要将参数 session_track_gtids 设置为 OWN_GTID，然后通过 API 接口 mysql_session_track_get_first 从返回包解析出 GTID 的值

其实，在实际应用中，这几个方案是可以混合使用的。比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。
但话说回来，过期读在本质上是由一写多读导致的。在实际应用中，可能会有别的不需要等待就可以水平扩展的数据库方案，但这往往是用牺牲写性能换来的，也就是需要在读性能和写性能中取权衡。

事务的启动时机

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句（第一个快照读语句），事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

两个“视图”的概念

• 一个是 view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view ...，而它的查询方法与表一样。
• 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

“快照”在MVCC里是怎么工作的？

在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

图中的三个虚线箭头，就是 undo log；而 V1、V2、V3 并不是物理上真实存在的，而是每次需要的时候根据当前版本和 undo log 计算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。因此，一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

在实现上， InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

• 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
• 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
• 如果落在黄色部分，那就包括两种情况
  a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
  b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。除了update 语句外，select 语句如果加锁，也是当前读

事务的可重复读的能力是怎么实现的？
可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。
而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

我们用下面两个表来说明 join 是怎么执行的，然后来回答这个问题：

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

Index Nested-Loop Join

select * from t1 straight_join t2 on (t1.a=t2.a);

这里使用 straight join 让 MySQL 使用固定的连接方式执行查询，便于分析执行过程中的性能问题。
在这个语句里，t1 是驱动表，t2 是被驱动表，现在我们看下 explain 的结果：

可以看到，被驱动表 t2 的字段 a 上有索引，并且 join 过程中使用到了该索引，因此这个语句的执行流程如下：

1. 从表 t1 中读入一行数据 R
2. 从数据行 R 中，取出 a 字段到表 t2 中查找
3. 取出表 t2 中满足条件的行，跟 R 组成一行 ，作为结果集的一部分
4. 重复执行步骤 1 到 3， 直到表 t1 的末尾循环结束
   在形式上，这个过程和我们写程序时的嵌套查询类型，并且可以用上被驱动表的索引，称之为 Index Nested-Loop Join，简称 NLJ，对应流程图如下：
   
   在这个流程里：
5. 对驱动表 t1 做了全表扫描，这个过程需要扫描 100 行
6. 对于每一行 R，根据 a 字段去表 t2 查找，走的是树搜索过程。由于构造的数据是一一对应的，所以总共扫描 100 行
7. 所以整个执行流程共扫描 200 行

那如果不使用 join，执行流程是怎么样的？
不使用 join ，就执行用单表查询，执行流程如下：

1. 执行 select * from t1，查出 t1 的所有数据，这里共有 100 行
2. 循环遍历这 100 行数据：
   • 从每一行 R 取出字段 a 的值 R.a
   • 执行 select * from t2 where a = R.a
   • 把返回结果和 R 构成结果集的一行
     可以看到，查询过程中也是扫描了 200 行， 但总共执行了 101 条语句，比使用 join 多了 100 次交互，而且客户端还要自己拼接 SQL 语句和结果，显然不如使用 join

那如果使用 join，怎么选择驱动表呢？
在这个 join 语句的执行过程中，驱动表走的是全表扫描，被驱动表走的是树搜索。
假设被驱动表的行数是 M，每次在被驱动表查一行数据，首先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度为 ，所以在被驱动表上查一行的时间复杂度是
假设驱动板的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次，因此整个执行过程，近似复杂度是
显然，N 对复杂度的影响更大，因此应该让小表来做驱动表

通过上面的分析可以知道，在可以使用被驱动表索引的情况下：

1. 使用 join 语句，性能比拆成多个单表执行性能更好
2. 如果使用 join，需要让小表作为驱动表

Simple Nested-Loop Join

如果被驱动表用不上索引的执行流程会怎么样呢？我们先修改下 SQL ：

select * from t1 straight_join t2 on (t1.a=t2.b);

由于 t2 的字段 b 没有索引，因此每次去 t2 去匹配的时候，就要做一次全表扫描，这个算法叫做 Simple Nested-Loop Join

这样算来，这个 SQL 请求需要扫描表 t2 100次，总共扫描 100 * 1000 行，不过 MySQL 也没有使用这个算法，而是使用了 Block Nested-Loop Join，简称 BNL

Block Nested-Loop Join

该算法流程如下：

1. 把表 t1 的数据读入线程内存 join_buffer 中
2. 扫描表 t2， 把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的作为结果集的一部分返回
   执行流程图如下：
   
   语句的 explain 结果如下：
   
   在这个过程中，对 t1 和 t2 都做了一次全表扫描，总的扫描行数为 1100。由于 join_buffer 是以无序数组的方式组织的，因此对 t2 的每一行都要做 100 次判断，总共需要在内存中做 100 * 1000 次判断
   从时间复杂度上来说 Simple Nested-Loop Join 和 Block Nested-Loop Join 一致，但是 BNJ 是内存操作，所以性能更好

那么这种情况下应该选择哪个表作为驱动表呢？
假设小表的行数是 N，大表的行数是 M，那么：

1. 两个表都做一次全表扫描，所以总的扫描行数是 M + N
2. 内存中的判断次数是 M*N
   可以看到哪个表作为驱动表，执行耗时都是一样的。但是大表会受到 join_buffer 的影响

join_buffer 的大小由参数 join_buffer_size 设定，默认为 256K，如果放不下 t1 的所有数据，就分段放。假设 t1 表到 88 行之后，join_buffer 就满了，执行过程如下：

1. 扫描表 t1， 顺序读取数据行放入 join_buffer，放到第 88 行 join_buffer 满了，继续执行第 2 步
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的作为结果集的一部分返回
3. 清空 join_buffer
4. 继续扫描表 t1， 顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步
   流程图如下：
   
   步骤 4 和 5 表示清空 join_buffer 再复用

可以看到，t1 分了两次放入 join_buffer，导致 t2 会被扫描 2 次，不过判断次数不会变
那这种情况下，怎么选择驱动表呢？
假设，驱动表的数据行数是 N，需要分 K 段才能完成算法流程，被驱动表的数据行数是 M 。
这里的 K 不是常数，N 越大 K 就会越大，因此把 K 表示为 λ*N，λ 的取值范围为 (0,1)，所以在这个算法的执行过程中：

1. 扫描行数是 N + λ * N * M
2. 内存判断 N * M 次
   显然，内存判断次数不受选择哪个表作为驱动表影响，而扫描行数在 M 和 N 大小确定的情况下，N 小一些，整个算式的结果会更小
   当 N 固定的时候 K 受 join_buffer_size 的影响，join_buffer_size 越大，K 越小

什么叫做小表？
按照条件过滤后，参与 join 的各个字段的总数据量小的为小表

幻读是什么？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行
在可重复读隔离级别下，幻读在“当前读”下才会出现
幻读仅专指“新插入的行”

幻读有什么问题？

破坏语义
影响数据一致性，因为即使把所有的记录都加上锁，还是阻止不了新插入的记录
这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性

如何解决幻读？

为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。间隙锁，锁的就是两个值之间的空隙
在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁
跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和行锁合称 next-key lock，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间
InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。

间隙锁和 next-key lock 的引入带来的问题

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的
可以通过设置隔离级别为读提交+binlog=row避免，但要考虑实际的业务场景

更新语句的执行流程

更新语句的执行流程和查询语句的执行流程类似，区别在于更新语句会使查询语句的查询缓存失效，同时还会涉及到两个日志模块：redo log （重做日志）和 bin log （归档日志）

redo log（重做日志）

当有一条记录需要更新时，InnoDB 引擎就会先把记录写到 redo log 中，并更新内存，此时更新算是完成了，之后，InnoDB 引擎会在适当的时候将记录写入磁盘中。

这里涉及到 WAL 技术（Write-Ahead Logging），先写日志，再写磁盘

redo log 的大小是固定的，比如可以配置为一组 4 个文件，每个文件的大小是 1 GB，那么 redo log 可以记录 4 GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下图：

write pos 是当前记录位置，一边写一边后移；checkpoint 是当前要擦除的位置，也是往后推移并循环的，擦除记录前要把记录更新到数据文件；write pos 和 checkpoint 之间的部分用来记录新的操作。如果 write pos 追上了 checkpoint 就需要停下先擦除一些记录，把 checkpoint 推进下

redo log 可以让 InnoDB 保证即使数据库发生异常重启，之前提交的记录也不会丢失，这个能力称为 crash-safe

bin log（归档日志）

bin log 是 MySQL 的 Server 层维护的一种二进制日志，主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中

redo log 和 binlog 的区别

1. redo log 是 InnoDB 引擎特有的；binlog 是 Server 层实现的，所有引擎都可以用
2. redo log 是物理日志，记录的是 “在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是语句的原始逻辑，例如：给 ID=2 这一行的 C 加 1
3. redo log 是循环写的，空间固定会用完；binlog 是追加写的，文件写到一定大小，会切换到下一文件，不会覆盖

update 语句执行流程

update T set c=c+1 where ID=2;

1. 执行器先查找引擎取 ID=2 这一行
2. 执行器拿到引擎给的行数据，把这个值加上1，得到新的一行数据，再调用引擎接口写入这行新数据
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时，redo log 处于 prepare 状态，然后告知执行器执行完成，随时可以提交事务
4. 执行器生成这个操作的 binlog ，并把 binlog 写入磁盘
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成 commit 状态，更新完成

两阶段提交

两阶段提交主要是为了保证 redo log 和 binlog 保持一致

假设当前 ID=2 的行，字段 c 为 0，语句写完第一个日志后，第二个日志还没写完期间发生了 crash：

1. 先写 redo log 后写 binlog
   由于 redo log 写完之后，系统即使 crash，仍可以恢复数据，这时 c=1。但由于 binlog 没有写完，之后需要使用这个 binlog 恢复临时库时，恢复出来的 c=0，与原库不同
2. 先写 binlog 后写 redo log
   由于 redo log 还没写完，系统恢复后 c=0，但 binlog 里已经记录了，之后用 binlog 恢复时，恢复出来的 c=1，与原库不同

redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致

数据恢复

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

1. 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
2. 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻

示例表

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

union 的执行

(select 1000 as f) union (select id from t1 order by id desc limit 2);

下图为该语句的 explain 结果

可以看到：

• 第二行 key = PRIMARY，说明第二个子句用到了索引 id
• 第三行 Extra 字段，表示在对子查询的结果集做 union 的时候，使用了临时表（Using temporary）

这个语句的执行流程如下：

1. 创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段
2. 执行第一个子查询，得到 1000 这个值，并存入临时表中
3. 执行第二个子查询：
   • 拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行
   • 取到第二行 id=999，插入临时表成功
4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999

流程图如下：

这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键 id 的唯一性约束，实现了 union 的语义

如果 union 改成 union all 的话，就没有了去重的语义，这样执行的时候也就用不到临时表了，查询的结果直接作为结果集的一部分了

group by 的执行

select id%10 as m, count(*) as c from t1 group by m;

下图为该语句的 explain 结果

可以看到：

• Using index，表示这个语句使用了覆盖索引，选择了索引 a，不需要回表
• Using temporary，表示使用了临时表
• Using filesort，表示需要排序

这个语句的执行流程如下：

1. 创建内存临时表，表里有两个字段 m 和 c，主键是 m
2. 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id % 10 的结果，记为 x
   • 如果临时表中没有主键为 x 的行，就插入一个记录 （x，1）
   • 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1
3. 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端

流程图如下：

如果不需要排序，可以使用 order by null，直接从临时表取数据返回

内存临时表的大小是有限制的，由参数 tmp_table_size 控制，默认为 16 M，如果内存临时表大小到达了上限，这时候会把内存临时表转换成磁盘临时表，磁盘临时表默认使用 InnoDB

group by 优化方法----索引

group by 的语义逻辑是统计不同的值出现的个数，但是由于每一行 id % 100 的结果是无序的，所以就需要一个临时表，来记录并统计结果

如果可以保证出现的数据是有序的，就可以避免临时表的使用

执行过程：

• 当碰到第一个 1 的时候，已经知道累积了 X 个 0，结果集的第一行就是 （0，X）
• 当碰到第一个 2 的时候，已经知道累计了 Y 个 1，结果集的第二行就是 （1，Y）

按照这个逻辑，扫描到数据结束就可以直接拿到 group by 的结果，不再需要临时表

在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。如果是 MySQL 5.6 及之前的版本，可以创建普通列和索引来解决这个问题

alter table t1 add column z int generated always as(id % 100), add index(z);

group by 语句可以改为：

select z, count(*) as c from t1 group by z;

explain 的结果：

group by 优化方法----直接排序

可以在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），告诉优化器这个语句涉及的数据量很大，直接用磁盘临时表，这样就省去了放入内存临时表的步骤

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

执行流程：

1. 初始化 sort_buffer，确定放入一个整型字段，记为 m
2. 扫描表 t1 的索引 a，依次取出里面的 id 值，将 id % 100 的值存入 sort_buffer 中
3. 扫描完成后，对 sort_buffer 的字段 m 做排序（如果 sort_buffer 内存不够用，就会利用到磁盘临时文件辅助排序）
4. 排序完成后，就得到一个有序数组
5. 执行 group by 得到结果集

执行流程图：

explain 结果：

总结

1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果
2. join_buffer 是无序数据，sort_buffer 是有序数组，临时表是二维表结构
3. 如果只选逻辑需要用到二维表特性，就会优先考虑使用临时表。例如 union 需要用到唯一索引约束，group by 需要用到另外一个字段来保存累计计数

不论是偶发性的查询压力，还是备份，对备库延迟的影响一般是分钟级的，而且在备库恢复正常以后都能够追上来。但是，如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。

谈到主备的并行复制能力，我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库，另一箭头代表的是备库上 sql_thread 执行中转日志（relay log）
在主库上，影响并发度的原因就是各种锁了。由于 InnoDB 引擎支持行锁，除了所有并发事务都在更新同一行（热点行）这种极端场景外，它对业务并发度的支持还是很友好的
而日志在备库上的执行，就是图中备库上 sql_thread 更新数据 (DATA) 的逻辑。如果是用单线程的话，就会导致备库应用日志不够快，造成主备延迟。

在官方的 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。而所有的多线程复制机制，都是要把 sql_thread 拆成多个线程，也就是都符合下面的这个模型

coordinator 就是原来的 sql_thread, 不过现在它不再直接更新数据了，只负责读取中转日志和分发事务。真正更新日志的，变成了 worker 线程。而 work 线程的个数，就是由参数 slave_parallel_workers 决定的

事务分发会遇到两个问题：

1. 事务能不能按照轮询的方式分发给各个 worker，也就是第一个事务分给 worker_1，第二个事务发给 worker_2 呢？
   不行，因为，事务被分发给 worker 以后，不同的 worker 就独立执行了。但是，由于 CPU 的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不一致的问题
2. 同一个事务的多个更新语句，能不能分给不同的 worker 来执行呢？
   不行，例如，一个事务更新了表 t1 和表 t2 中的各一行，如果这两条更新语句被分到不同 worker 的话，虽然最终的结果是主备一致的，但如果表 t1 执行完成的瞬间，备库上有一个查询，就会看到这个事务“更新了一半的结果”，破坏了事务逻辑的隔离性

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

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

丁奇在 MySQL 5.5 版本的使用的并行复制策略

官方 MySQL 5.5 版本是不支持并行复制的，但是丁奇为了解决主备延迟问题，先后写了两个版本的并行策略

1. 按表分发策略

按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。如果有跨表的事务，还是要把两张表放在一起考虑。

可以看到，每个 worker 线程对应一个 hash 表，用于保存当前正在这个 worker 的“执行队列”里的事务所涉及的表。hash 表的 key 是“库名. 表名”，value 是一个数字，表示队列中有多少个事务修改这个表。
在有事务分配给 worker 时，事务里面涉及的表会被加到对应的 hash 表中。worker 执行完成后，这个表会被从 hash 表中去掉。
现在我们用事务 T 的分配流程，来看一下分配规则：

Ⅰ. 由于事务 T 中涉及修改表 t1，而 worker_1 队列中有事务在修改表 t1，事务 T 和队列中的某个事务要修改同一个表的数据，这种情况我们说事务 T 和 worker_1 是冲突的。
Ⅱ. 按照这个逻辑，顺序判断事务 T 和每个 worker 队列的冲突关系，会发现事务 T 跟 worker_2 也冲突。
Ⅲ. 事务 T 跟多于一个 worker 冲突，coordinator 线程就进入等待。
Ⅳ. 每个 worker 继续执行，同时修改 hash_table。假设 hash_table_2 里面涉及到修改表 t3 的事务先执行完成，就会从 hash_table_2 中把 db1.t3 这一项去掉。
Ⅴ. 这样 coordinator 会发现跟事务 T 冲突的 worker 只有 worker_1 了，因此就把它分配给 worker_1。
Ⅵ. coordinator 继续读下一个中转日志，继续分配事务。
也就是说，每个事务在分发的时候，跟所有 worker 的冲突关系包括以下三种情况：

Ⅰ. 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
Ⅱ. 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
Ⅲ. 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。
这个按表分发的方案，在多个表负载均匀的场景里应用效果很好。但是，如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了

2. 按行分发策略

要解决热点表的并行复制问题，就需要一个按行并行复制的方案。按行复制的核心思路是：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。
按行复制和按表复制的数据结构差不多，也是为每个 worker，分配一个 hash 表。只是要实现按行分发，这时候的 key，就必须是“库名 + 表名 + 唯一键的值”，但是，这个“唯一键”只有主键 id 还是不够的，如果表中还存在唯一索引，还需要考虑唯一索引的键值

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;

insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);

可以看到，这两个事务要更新的行的主键值不同，但是如果它们被分到不同的 worker，就有可能 session B 的语句先执行。这时候 id=1 的行的 a 的值还是 1，就会报唯一键冲突。所以当前 key 应该是“库名 + 表名 + 索引 a 的名字 +a 的值”。
比如，在上面这个例子中，我要在表 t1 上执行 update t1 set a=1 where id=2 语句，在 binlog 里面记录了整行的数据修改前各个字段的值，和修改后各个字段的值。
因此，coordinator 在解析这个语句的 binlog 的时候，这个事务的 hash 表就有三个项:

Ⅰ. key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变，出现了两次。
Ⅱ. key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表 a=2 的行。
Ⅲ. key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表 a=1 的行。
相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源

3. 两个方案的约束条件

Ⅰ. 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说，主库的 binlog 格式必须是 row；
Ⅱ. 表必须有主键；
Ⅲ. 不能有外键。表上如果有外键，级联更新的行不会记录在 binlog 中，这样冲突检测就不准确。

4. 按行分发的问题及退化过程

主要问题，发生于处理大事务时：
Ⅰ. 耗费内存。比如一个语句要删除 100 万行数据，这时候 hash 表就要记录 100 万个项。
Ⅱ. 耗费 CPU。解析 binlog，然后计算 hash 值，对于大事务，这个成本还是很高的。
所以实现这个策略的时候会设置一个阈值，单个事务如果超过设置的行数阈值（比如，如果单个事务更新的行数超过 10 万行），就暂时退化为单线程模式，退化过程的逻辑大概是这样的：
Ⅰ. coordinator 暂时先 hold 住这个事务；
Ⅱ. 等待所有 worker 都执行完成，变成空队列；
Ⅲ. coordinator 直接执行这个事务；
Ⅳ. 恢复并行模式。

MySQL 5.6 版本的并行复制策略

官方 MySQL5.6 版本，支持了并行复制，只是支持的粒度是按库并行，用于决定分发策略的 hash 表里，key 就是数据库名

这个策略的并行效果，取决于压力模型。如果在主库上有多个 DB，并且各个 DB 的压力均衡，使用这个策略的效果会很好。

相比于按表和按行分发，这个策略有两个优势：

1. 构造 hash 值的时候很快，只需要库名；而且一个实例上 DB 数也不会很多，不会出现需要构造 100 万个项这种情况。
2. 不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。

MariaDB 的并行复制策略

MariaDB 的并行复制策略利用了 redo log 组提交 (group commit) 优化特性：

1. 能够在同一组里提交的事务，一定不会修改同一行；
2. 主库上可以并行执行的事务，备库上也一定是可以并行执行的。

处理过程：

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

MariaDB 的这个策略，目标是“模拟主库的并行模式”，但它并没有实现“真正的模拟主库并发度”这个目标，因为在主库上，一组事务在 commit 的时候，下一组事务是同时处于“执行中”状态的。

如图所示，假设了三组事务在主库的执行情况，你可以看到在 trx1、trx2 和 trx3 提交的时候，trx4、trx5 和 trx6 是在执行的。这样，在第一组事务提交完成的时候，下一组事务很快就会进入 commit 状态。

而按照 MariaDB 的并行复制策略，备库上的执行效果如图所示：

可以看到，在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行，这样系统的吞吐量就不够。另外，这个方案很容易被大事务拖后腿，需要等待大事务执行完成之后，下一组才会继续执行

MySQL 5.7 的并行复制策略
在 MariaDB 并行复制实现之后，官方的 MySQL5.7 版本也提供了类似的功能，由参数 slave-parallel-type 来控制并行复制策略：

1. 配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
2. 配置为 LOGICAL_CLOCK，表示的就是类似 MariaDB 的策略。不过，MySQL 5.7 这个策略，针对并行度做了优化。
   你可以先考虑这样一个问题：同时处于“执行状态”的所有事务，是不是可以并行？
   答案是，不能。因为，这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的 worker，就会出现备库跟主库不一致的情况
   MariaDB 策略的核心，是“所有处于 commit”状态的事务可以并行。事务处于 commit 状态，表示已经通过了锁冲突的检验了
   所以，也就是说通过锁冲突检验的事务是可以并行的。

而通过两阶段提交细化过程可以知道，只要能够到达 redo log prepare 阶段，就表示事务已经通过锁冲突的检验了。

因此，MySQL 5.7 并行复制策略的**是：

1. 同时处于 prepare 状态的事务，在备库执行时是可以并行的；
2. 处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。

binlog 的组提交的时候，有两个参数：

1. binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
2. binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

这两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数。在 MySQL 5.7 的并行复制策略里，它们可以用来制造更多的“同时处于 prepare 阶段的事务”。这样就增加了备库复制的并行度。

也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。在 MySQL 5.7 处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

MySQL 5.7.22 的并行复制策略

在 MySQL 5.7.22 版本里，MySQL 增加了一个新的并行复制策略，基于 WRITESET 的并行复制。
相应地，新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种：

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

当然为了唯一标识，这个 hash 值是通过“库名 + 表名 + 索引名 + 值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，insert 语句对应的 writeset 就要多增加一个 hash 值。

这跟我们前面介绍的按行分发的策略是差不多的。不过，MySQL 官方的这个实现还是有很大的优势（毕竟是官方实现）：

1. writeset 是在主库生成后直接写入到 binlog 里面的，这样在备库执行的时候，不需要解析 binlog 内容（event 里的行数据），节省了很多计算量；
2. 不需要把整个事务的 binlog 都扫一遍才能决定分发到哪个 worker，更省内存；
3. 由于备库的分发策略不依赖于 binlog 内容，所以 binlog 是 statement 格式也是可以的。

因此，MySQL 5.7.22 的并行复制策略在通用性上还是有保证的。

当然，对于“表上没主键”和“外键约束”的场景，WRITESET 策略也是没法并行的，也会暂时退化为单线程模型。

参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

• 设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
• 设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

建议设置为 ON，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的

数据删除

InnoDB 删除记录时，只会把记录标记为已删除，如果再插入符合条件的记录会复用，但是磁盘大小不会变
如果删除一个页上的所有记录，整个页就会被标记为已删除，可以被复用到任何位置。
如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用
如果删除整个表，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小

数据插入

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂
经过大量增删改的表，都是可能是存在空洞的

重建表

使用 alter table A engine=InnoDB 命令来重建表，MySQL 会自动完成转存数据、交换表名、删除旧表的操作

显然花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的

Online DDL

MySQL 5.6 版本开始引入的 Online DDL，引入之后的创建流程：

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件。

DDL 之前是要拿 MDL 写锁的，这样还能叫 Online DDL 吗？

alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢？为了实现 Online，MDL 读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做 DDL。

优化器的逻辑

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。
在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。
当然扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数的获取方式

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。
这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
可以使用 show index 方法，看到一个索引的基数

获得索引基数的方式

使用采样统计的方法，因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

• 设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。
• 设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

analyze table t 命令，可以用来重新统计索引信息

索引选择异常和处理

1. 采用 force index 强行选择一个索引
2. 修改语句，引导 MySQL 使用我们期望的索引
3. 新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

MySQL 主备的基本原理

下图展示的是主备切换的流程

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

在状态 1 中，虽然节点 B 没有被直接访问，但是我依然建议你把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：

1. 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
2. 防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
3. 可以用 readonly 状态，来判断节点的角色。
   因为 readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限，所以备库的 readonly 也不会影响同步

下图是update 语句在节点 A 执行，然后同步到节点 B 的流程

备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

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

binlog 的三种格式

binlog 有三种格式：statement、row 和 mixed ，其中 mixed 是前两种格式的混合

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

如果要在表中删除一行数据的话，我们来看看这个 delete 语句的 binlog 是怎么记录的
mysql> delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
当 binlog_format=statement 时，binlog 里面记录的就是 SQL 语句的原文。

mysql> show binlog events in 'master.000001'; 该命令查看 binlog 中的内容。

下图为statement 格式 binlog 示例

第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’；
第二行是一个 BEGIN，跟第四行的 commit 对应，表示中间是一个事务；
第三行就是真实执行的语句了。可以看到，在真实执行的 delete 命令之前，还有一个“use ‘test’”命令。这条命令不是我们主动执行的，而是 MySQL 根据当前要操作的表所在的数据库，自行添加的。这样做可以保证日志传到备库去执行的时候，不论当前的工作线程在哪个库里，都能够正确地更新到 test 库的表 t。use 'test’命令之后的 delete 语句，就是我们输入的 SQL 原文了。可以看到，binlog“忠实”地记录了 SQL 命令，甚至连注释也一并记录了。
最后一行是一个 COMMIT，XID 用于关联 redo log 和 bin log

运行的这条 delete 会产生一个 warning

原因是当前 binlog 设置的是 statement 格式，并且语句中有 limit。为什么这样呢？这是因为 delete 带 limit，很可能会出现主备数据不一致的情况：

1. 如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；
2. 但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

把 binlog 的格式改为 binlog_format=‘row’ 就会解决这个问题，因为 row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows

1. Table_map event，用于说明接下来要操作的表是 test 库的表 t;
2. Delete_rows event，用于定义删除的行为。

其实，我们通过上图是看不到详细信息的，还需要借助 mysqlbinlog 工具，用下面这个命令解析和查看 binlog 中的内容。因为上图中的信息显示，这个事务的 binlog 是从 8900 这个位置开始的，所以可以用 start-position 参数来指定从这个位置的日志开始解析

mysqlbinlog  -vv data/master.000001 --start-position=8900;

• server id 1，表示这个事务是在 server_id=1 的这个库上执行的。
• 每个 event 都有 CRC32 的值，这是因为我把参数 binlog_checksum 设置成了 CRC32。
• Table_map event 跟在之前图中看到的相同，显示了接下来要打开的表，map 到数字 226。如果要操作多张表则每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字，用于区分对不同表的操作。
• 我们在 mysqlbinlog 的命令中，使用了 -vv 参数是为了把内容都解析出来，所以从结果里面可以看到各个字段的值（比如，@1=4、 @2=4 这些值）。
• binlog_row_image 的默认配置是 FULL，因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息，在这个例子里，就是只会记录 id=4 这个信息。
• 最后的 Xid event，用于表示事务被正确地提交了。

当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题

为什么会有 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 格式。

为什么越来越多的场景要求把 MySQL 的 binlog 格式设置成 row

显而易见的好处是恢复数据。
如果执行的是 delete 语句，row 格式的 binlog 也会把被删掉的行的整行信息保存起来。所以，如果你在执行完一条 delete 语句以后，发现删错数据了，可以直接把 binlog 中记录的 delete 语句转成 insert，把被错删的数据插入回去就可以恢复了
如果执行的是 insert 语句，row 格式下，insert 语句的 binlog 里会记录所有的字段信息，这些信息可以用来精确定位刚刚被插入的那一行。这时，你直接把 insert 语句转成 delete 语句，删除掉这被误插入的一行数据就可以了
如果执行的是 update 语句，row 格式的 binlog 里面会记录修改前整行的数据和修改后的整行数据。所以，如果你误执行了 update 语句的话，只需要把这个 event 前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作了

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

用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行。类似下面的命令：
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
因为有些语句的执行结果是依赖于上下文命令的，直接执行的结果很可能是错误的

双 M 结构循环复制问题

实际生产上使用比较多的是双 M 结构，下图即是双 M 结构的主备切换流程

双 M 结构会存在循环复制的问题，业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了
可以用下面的逻辑，来解决两个节点间的循环复制的问题：

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

按照这个逻辑，如果我们设置了双 M 结构，日志的执行流就会变成这样：\

1. 从节点 A 更新的事务，binlog 里面记的都是 A 的 server id；
2. 传到节点 B 执行一次以后，节点 B 生成的 binlog 的 server id 也是 A 的 server id；
3. 再传回给节点 A，A 判断到这个 server id 与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了

问题

1. 什么时候会把线上生产库设置成“非双 1” ？

• 业务高峰期。一般如果有预知的高峰期，DBA 会有预案，把主库设置成“非双 1”。
• 备库延迟，为了让备库尽快赶上主库。
• 用备份恢复主库的副本，应用 binlog 的过程，这个跟上一种场景类似。
• 批量导入数据的时候。

2. 什么情况下双 M 结构会出现循环复制？
   总的来说就是 server_id 改变了，当 binlog 的 server_id 与节点的 server_id 都不同时就会出现

• 在一个主库更新事务后，用命令 set global server_id=x 修改了 server_id。等日志再传回来的时候，发现 server_id 跟自己的 server_id 不同，就只能执行了
• 有三个节点的时候，trx1 是在节点 B 执行的，因此 binlog 上的 server_id 就是 B，binlog 传给节点 A，然后 A 和 A’搭建了双 M 结构，就会出现循环复制
  
  这种三节点复制的场景，做数据库迁移的时候会出现，如果出现循环复制，可以使用如下命令：

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B);  // B 节点的 server_id，也就是不再执行 B 节点的 binlog
start slave;

过段时间之后再更改回来：

stop slave；
CHANGE MASTER TO IGNORE_SERVER_IDS=();
start slave;

在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的

但是会出现这样的现象：使用了 kill 命令，却没能断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是 Killed

为什么会显示 Killed 呢？而且 show processlist 为什么还能看到这个线程呢？

其实大多数情况下，kill query/connection 命令是有效的。比如，执行一个查询的过程中，发现执行时间太久，要放弃继续查询，这时我们就可以用 kill query 命令，终止这条查询语句。
还有一种情况是，语句处于锁等待的时候，直接使用 kill 命令也是有效的：

session C 执行 kill query 以后，session B 几乎同时就提示了语句被中断

收到 kill 以后，线程做什么？

kill 并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”

因为当对一个表做增删改查操作时，会在表上加 MDL 读锁。所以，session B 虽然处于 blocked 状态，但还是拿着一个 MDL 读锁的。如果线程被 kill 的时候，就直接终止，那之后这个 MDL 读锁就没机会被释放了

其实，这跟 Linux 的 kill 命令类似，kill -N pid 并不是让进程直接停止，而是给进程发一个信号，然后进程处理这个信号，进入终止逻辑。只是对于 MySQL 的 kill 命令来说，不需要传信号量参数，就只有“停止”这个命令。

实现上，当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：

1. 把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
2. 给 session B 的执行线程发一个信号。

为什么要发信号呢？

因为像图中我们例子里面，session B 处于锁等待状态，如果只是把 session B 的线程状态设置 THD::KILL_QUERY，线程 B 并不知道这个状态变化，还是会继续等待。发一个信号的目的，就是让 session B 退出等待，来处理这个 THD::KILL_QUERY 状态

上面的分析中，隐含了这么三层意思：

1. 一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；
2. 如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
3. 语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

kill 不掉情况

首先，执行 set global innodb_thread_concurrency=2，将 InnoDB 的并发线程上限数设置为 2；然后，执行下面的序列：

可以看到：

1. sesssion C 执行的时候被堵住了；
2. 但是 session D 执行的 kill query C 命令却没什么效果
3. 直到 session E 执行了 kill connection 命令，才断开了 session C 的连接，提示“Lost connection to MySQL server during query”
4. 但是这时候，如果在 session E 中执行 show processlist，你就能看到下面这个图

这时候，id=12 这个线程的 Commnad 列显示的是 Killed。也就是说，客户端虽然断开了连接，但实际上服务端上这条语句还在执行过程中

那为什么在执行 kill query 命令时，这条语句不像第一个例子的 update 语句一样退出呢？

在实现上，等行锁时，使用的是 pthread_cond_timedwait 函数，这个等待状态可以被唤醒。但是，在这个例子里，12 号线程的等待逻辑是这样的：每 10 毫秒判断一下是否可以进入 InnoDB 执行，如果不行，就调用 nanosleep 函数进入 sleep 状态。
也就是说，虽然 12 号线程的状态已经被设置成了 KILL_QUERY，但是在这个等待进入 InnoDB 的循环过程中，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段
而当 session E 执行 kill connection 命令时，是这么做的：

1. 把 12 号线程状态设置为 KILL_CONNECTION；
2. 关掉 12 号线程的网络连接。因为有这个操作，所以你会看到，这时候 session C 收到了断开连接的提示

那为什么执行 show processlist 的时候，会看到 Command 列显示为 killed 呢？

因为在执行 show processlist 的时候，有一个特别的逻辑：
如果一个线程的状态是 KILL_CONNECTION，就把 Command 列显示成 Killed。

所以其实，即使是客户端退出了，这个线程的状态仍然是在等待中。

那这个线程什么时候会退出呢？

只有等到满足进入 InnoDB 的条件后，session C 的查询语句继续执行，然后才有可能判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION，再进入终止逻辑阶段

kill 无效的总结

1. 线程没有执行到判断线程状态的逻辑
   跟这种情况相同的，还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态
2. 终止逻辑耗时较长
   这时候，从 show processlist 结果上看也是 Command=Killed，需要等到终止逻辑完成，语句才算真正完成。这类情况，比较常见的场景有以下几种：

• 超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长
• 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长
• DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久

这些“kill 不掉”的情况，其实是因为发送 kill 命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。而被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的
如果你发现一个线程处于 Killed 状态，你可以做的事情就是，通过影响系统环境，让这个 Killed 状态尽快结束

关于客户端的误解

1. 如果直接在客户端通过 Ctrl+C 命令，是不是就可以直接终止线程呢？
   不可以，在客户端的操作只能操作到客户端的线程，客户端和服务端只能通过网络交互，是不可能直接操作服务端线程的
   而由于 MySQL 是停等协议，所以这个线程执行的语句还没有返回的时候，再往这个连接里面继续发命令也是没有用的。实际上，执行 Ctrl+C 的时候，是 MySQL 客户端另外启动一个连接，然后发送一个 kill query 命令。

2. 库里面的表特别多时，为什么客户端连接就会很慢？
   有些线上的库，会包含很多表（我见过最多的一个库里有 6 万个表）。这时候，你就会发现，每次用客户端连接都会卡在下面这个界面上：
   
   而如果 db1 这个库里表很少的话，连接起来就会很快，可以很快进入输入命令的状态。因此，有同学会认为是表的数目影响了连接性能。
   每个客户端在和服务端建立连接的时候，需要做的事情就是 TCP 握手、用户校验、获取权限。但这几个操作，显然跟库里面表的个数无关.。但实际上，正如图中的文字提示所说的，当使用默认参数连接的时候，MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作：

3. 执行 show databases；

4. 切到 db1 库，执行 show tables；

5. 把这两个命令的结果用于构建一个本地的哈希表。
   在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。
   也就是说，我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢

如果在连接命令中加上 -A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。
自动补全的效果就是，你在输入库名或者表名的时候，输入前缀，可以使用 Tab 键自动补全表名或者显示提示

除了加 -A 以外，加–quick(或者简写为 -q) 参数，也可以跳过这个阶段

3. –quick 参数不是让服务端加速的参数，反而可能会降低服务端的性能，为什么呢？

MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：

• 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。
• 另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。

MySQL 客户端默认采用第一种方式，而如果加上–quick 参数，就会使用第二种不缓存的方式。
采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。

不过，使用这个参数可以达到以下三点效果：

• 跳过表名自动补全功能
• mysql_store_result 需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能
• 是不会把执行命令记录到本地的命令历史文件

所以，–quick 参数的意思，是让客户端变得更快。

FILE -> Setting -> Tools -> Terminal 更改Shell path为 "cmd.exe" /k "Cmder\vendor\init.bat"

在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高
主备延迟
数据同步有关的时间点主要包括以下三个：

1. 主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
2. 之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
3. 备库 B 执行完成这个事务，我们把这个时刻记为 T3。

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

可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒

seconds_behind_master 的计算方法如下：

1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master

网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。所以说，主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢

主备延迟的原因

1. 备库所在机器的性能比主库所在的机器性能差
   实际上，更新过程中也会触发大量的读操作。所以，当备库主机上的多个备库都在争抢资源的时候，就可能会导致主备延迟了。
   因为主备可能发生切换，备库随时可能变成主库，所以主备库选用相同规格的机器，并且做对称部署，是现在比较常见的情况
2. 备库的压力大
   一般的想法是，主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。
   由于主库直接影响业务，所以使用会比较克制，忽视了备库的压力控制，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟
   可以通过以下方式解决：

• 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力
• 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力

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

• 不要一次性地用 delete 语句删除太多数据
• 大表 DDL，计划内的 DDL，建议使用 gh-ost 方案

4. 备库的并行复制能力-见下节

主备切换策略

1. 可靠性优先策略
   双 M 结构下，从主备切换的详细过程：
   Ⅰ. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步
   Ⅱ. 把主库 A 改成只读状态，即把 readonly 设置为 true；
   Ⅲ. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
   Ⅳ. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
   Ⅴ. 把业务请求切到备库 B。
   
   步骤 Ⅱ 到步骤 Ⅴ 之间系统处于不可写状态，其中比较耗时的是步骤 Ⅲ，这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小
2. 可用性优先策略
   不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间，这个切换流程的代价，就是可能出现数据不一致的情况
   可用性优先策略，且 binlog_format=mixed时的切换流程和数据结果：
   
   可用性优先策略，且 binlog_format=row时的切换流程和数据结果：
   
   因为 row 格式在记录 binlog 的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错 duplicate key error 并停止
   由此可知：

• 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
• 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的

出现的原因
腾讯云服务器在添加PPA的时候，命令如下：

sudo LC_ALL=C.UTF-8 add-apt-repository ppa:ondrej/php

因为本地虚拟机测试该命令无问题，所以判断是腾讯云出现的问题，提交了工单，反馈是服务器和该源的连通率较差，建议更换IP
解决方式
最终还是通过更换IP解决了，具体操作见这里，换完之后再执行，不行就再换，可麻烦

加锁规则

包含了两个“原则”、两个“优化”和一个“bug”

• 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

注意事项

• lock in share mode 只锁覆盖索引，执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁
• 锁是加在索引上的
• 如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段
• 在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围
• next-key lock 执行的时候，是要分成间隙锁和行锁两段来执行的
• 读提交隔离级别下有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交

Project Settings > Editor > Inspections > PHP > Undefined > Undefined field 中的 Notify about access to a field via magic 取消选中即可

回表

回到主键索引树搜索的过程，称为回表。主要出现原因为查找的数据只有主键索引树上存在。

覆盖索引

如果索引覆盖了查询的需求，就叫覆盖索引，减少了树的搜索次数，避免了回表，能显著提升性能

最左前缀原则

只要满足索引的最左前缀，就可以使用该索引，这个最左前缀可以是联合索引的前 N 个字段，也可以是字符串索引的前 N 个字符

在建立联合索引时，如何安排索引的顺序？

• 如果通过调整顺序可以减少索引的数量，优先考虑
• 如果不得不维护另一个索引，则需要考虑空间，选择空间小的单独维护

索引下推

MySQL 5.6 引入索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引包含的字段先判断，直接过滤不满足条件的数据，减少回表次数

B树和B+树的插入、删除图文详解

如图所示，就是一个基本的一主多从结构：

图中，虚线箭头表示的是主备关系，也就是 A 和 A’互为主备， 从库 B、C、D 指向的是主库 A。一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则由从库分担。

如图所示，就是主库发生故障，主备切换后的结果：

相比于一主一备的切换流程，一主多从结构在切换完成后，A’会成为新的主库，从库 B、C、D 也要改接到 A’。正是由于多了从库 B、C、D 重新指向的这个过程，所以主备切换的复杂性也相应增加了

基于位点的主备切换

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

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos  

这条命令有这么 6 个参数：

• MASTER_HOST、MASTER_PORT、MASTER_USER 和 MASTER_PASSWORD 四个参数，分别代表了主库 A’的 IP、端口、用户名和密码。
• MASTER_LOG_FILE 和 MASTER_LOG_POS 表示，要从主库的 master_log_name 文件的 master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量。

原来节点 B 是 A 的从库，本地记录的也是 A 的位点。但是相同的日志，A 的位点和 A’的位点是不同的。因此，从库 B 要切换的时候，就需要先经过“找同步位点”这个逻辑，但是位点很难精确取到，只能取一个大概位置

考虑到切换过程中不能丢数据，所以我们找位点的时候，总是要找一个“稍微往前”的，然后再通过判断跳过那些在从库 B 上已经执行过的事务

一种取同步位点的方法是这样的：

1. 等待新主库 A’把中转日志（relay log）全部同步完成；
2. 在 A’上执行 show master status 命令，得到当前 A’上最新的 File 和 Position；
3. 取原主库 A 故障的时刻 T；
4. 用 mysqlbinlog 工具解析 A’的 File，得到 T 时刻的位点。

# T 的格式为 2019-09-23 00:00:00
mysqlbinlog File --stop-datetime=T --start-datetime=T 

图中，end_log_pos 后面的值“123”，表示的就是 A’这个实例，在 T 时刻写入新的 binlog 的位置。然后，我们就可以把 123 这个值作为 $master_log_pos ，用在节点 B 的 change master 命令里

这个值并不精确，假设在 T 这个时刻，主库 A 已经执行完成了一个 insert 语句插入了一行数据 R，并且已经将 binlog 传给了 A’和 B，然后在传完的瞬间主库 A 的主机就掉电了。那么，这时候系统的状态是这样的：

1. 在从库 B 上，由于同步了 binlog， R 这一行已经存在；
2. 在新主库 A’上， R 这一行也已经存在，日志是写在 123 这个位置之后的；
3. 我们在从库 B 上执行 change master 命令，指向 A’的 File 文件的 123 位置，就会把插入 R 这一行数据的 binlog 又同步到从库 B 去执行

这时候，从库 B 的同步线程就会报告 Duplicate entry ‘id_of_R’ for key ‘PRIMARY’ 错误，提示出现了主键冲突，然后停止同步

所以，通常情况下，我们在切换任务的时候，要先主动跳过这些错误，有两种常用的方法：

1. 主动跳过一个事务
   跳过命令的写法是：

set global sql_slave_skip_counter=1;
start slave;

因为切换过程中，可能会不止重复执行一个事务，所以我们需要在从库 B 刚开始接到新主库 A’时，持续观察，每次碰到这些错误就停下来，执行一次跳过命令，直到不再出现停下来的情况，以此来跳过可能涉及的所有事务。

2. 通过设置 slave_skip_errors 参数，直接设置跳过指定的错误

在执行主备切换时，有这么两类错误，是经常会遇到的：

• 1062 错误是插入数据时唯一键冲突；
• 1032 错误是删除数据时找不到行。

因此，我们可以把 slave_skip_errors 设置为 “1032,1062”，这样中间碰到这两个错误时就直接跳过。

这种直接跳过指定错误的方法，针对的是主备切换时，由于找不到精确的同步位点，所以只能采用这种方法来创建从库和新主库的主备关系

而且只有很清楚在主备切换过程中，直接跳过 1032 和 1062 这两类错误是无损的，才可以这么设置 slave_skip_errors 参数

等到主备间的同步关系建立完成，并稳定执行一段时间之后，我们还需要把这个参数设置为空，以免之后真的出现了主从数据不一致，也跳过了

GTID

GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：
GTID=server_uuid:gno
其中：

• server_uuid 是一个实例第一次启动时自动生成的，是一个全局唯一的值；
• gno 是一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并加 1。

不过在 MySQL 的官方文档里，GTID 格式是这么定义的：
GTID=source_id:transaction_id

这里的 source_id 就是 server_uuid；而后面的这个 transaction_id，我觉得容易造成误导，所以我改成了 gno

为什么说使用 transaction_id 容易造成误解呢？

在 MySQL 里面我们说 transaction_id 就是指事务 id，事务 id 是在事务执行过程中分配的，如果这个事务回滚了，事务 id 也会递增，而 gno 是在事务提交的时候才会分配。从效果上看，GTID 往往是连续的，因此我们用 gno 来表示更容易理解

只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以启用 GTID 模式

在 GTID 模式下，每个事务都会跟一个 GTID 一一对应。这个 GTID 有两种生成方式，而使用哪种方式取决于 session 变量 gtid_next 的值：

1. 如果 gtid_next=automatic，代表使用默认值。这时，MySQL 就会把 server_uuid:gno 分配给这个事务。
   a. 记录 binlog 的时候，先记录一行 SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;
   b. 把这个 GTID 加入本实例的 GTID 集合。
2. 如果 gtid_next 是一个指定的 GTID 的值，比如通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：
   a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；
   b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务，也就是说系统不需要给这个事务生成新的 GTID，因此 gno 也不用加 1。

注意，一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic。这样，每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”。

GTID 使用示例

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into t values(1,1);

可以看到，事务的 BEGIN 之前有一条 SET @@SESSION.GTID_NEXT 命令。这时，如果实例 X 有从库，那么将 CREATE TABLE 和 insert 语句的 binlog 同步过去执行的话，执行事务之前就会先执行这两个 SET 命令， 这样被加入从库的 GTID 集合的，就是图中的这两个 GTID。
假设，现在这个实例 X 是另外一个实例 Y 的从库，并且此时在实例 Y 上执行了下面这条插入语句：
insert into t values(1,1);
并且，这条语句在实例 Y 上的 GTID 是 “aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”。
那么，实例 X 作为 Y 的从库，就要同步这个事务过来执行，显然会出现主键冲突，导致实例 X 的同步线程停止。这时，我们应该怎么处理呢？处理方法就是，你可以执行下面的这个语句序列：

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;
set gtid_next=automatic;
start slave;

其中，前三条语句的作用，是通过提交一个空事务，把这个 GTID 加到实例 X 的 GTID 集合中。如图 所示，就是执行完这个空事务之后的 show master status 的结果：

可以看到实例 X 的 Executed_Gtid_set 里面，已经加入了这个 GTID。
这样，我再执行 start slave 命令让同步线程执行起来的时候，虽然实例 X 上还是会继续执行实例 Y 传过来的事务，但是由于“aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10”已经存在于实例 X 的 GTID 集合中了，所以实例 X 就会直接跳过这个事务，也就不会再出现主键冲突的错误。
在上面的这个语句序列中，start slave 命令之前还有一句 set gtid_next=automatic。这句话的作用是“恢复 GTID 的默认分配行为”，也就是说如果之后有新的事务再执行，就还是按照原来的分配方式，继续分配 gno=3。

基于 GTID 的主备切换

在 GTID 模式下，备库 B 要设置为新主库 A’的从库的语法如下：

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
master_auto_position=1 

其中，master_auto_position=1 就表示这个主备关系使用的是 GTID 协议
我们把现在这个时刻，实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b，我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的：

1. 实例 B 指定主库 A’，基于主备协议建立连接。
2. 实例 B 把 set_b 发给主库 A’。
3. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务。
   a. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；
   b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；
4. 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。

这个逻辑里面包含了一个设计**：在基于 GTID 的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例 B 需要的日志已经不存在，A’就拒绝把日志发给 B。
这跟基于位点的主备协议不同。基于位点的协议，是由备库决定的，备库指定哪个位点，主库就发哪个位点，不做日志的完整性判断

引入 GTID 后，一主多从的切换场景下，主备切换时，从库 B、C、D 只需要分别执行 change master 命令指向实例 A’即可。找位点这个工作，在实例 A’内部就已经自动完成了。之后这个系统就由新主库 A’写入，主库 A’的自己生成的 binlog 中的 GTID 集合格式是：server_uuid_of_A’:1-M。如果之前从库 B 的 GTID 集合格式是 server_uuid_of_A:1-N， 那么切换之后 GTID 集合的格式就变成了 server_uuid_of_A:1-N, server_uuid_of_A’:1-M。

GTID 和在线 DDL

之前在第 22 篇文章《MySQL 有哪些“饮鸩止渴”提高性能的方法？》中，提到业务高峰期的慢查询性能问题时，分析到如果是由于索引缺失引起的性能问题，我们可以通过在线加索引来解决。但是，考虑到要避免新增索引对主库性能造成的影响，我们可以先在备库加索引，然后再切换。

当时提到，在双 M 结构下，备库执行的 DDL 语句也会传给主库，为了避免传回后对主库造成影响，要通过 set sql_log_bin=off 关掉 binlog。

不过这样操作之后会导致数据库里面是加了索引，但 binlog 并没有记录下这一个更新，导致数据和日志不一致，下面的操作可以解决这个问题。

假设，这两个互为主备关系的库还是实例 X 和实例 Y，且当前主库是 X，并且都打开了 GTID 模式。这时的主备切换流程可以变成下面这样：

1. 在实例 X 上执行 stop slave。
2. 在实例 Y 上执行 DDL 语句。注意，这里并不需要关闭 binlog。
3. 执行完成后，查出这个 DDL 语句对应的 GTID，并记为 server_uuid_of_Y:gno。
4. 到实例 X 上执行以下语句序列：

set GTID_NEXT="server_uuid_of_Y:gno";
begin;
commit;
set gtid_next=automatic;
start slave;

这样做的目的在于，既可以让实例 Y 的更新有 binlog 记录，同时也可以确保不会在实例 X 上执行这条更新。

MySQL中的重做日志（redo log），回滚日志（undo log），以及二进制日志（binlog）的简单总结

行锁

MySQL 的行锁是由存储引擎实现的，行锁就是针对数据表中行记录的锁。
行锁比表锁的粒度小，发生锁争用的可能小，并发度高。但行锁比表锁开销大，因为锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。

InnoDB 行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁

从两段锁说起

InnoDB 事务中，行锁是在需要的时候才加上，等到事务结束之后才释放，这就是两阶段锁协议。

根据两阶段锁协议，在事务中如果需要锁多个行，要把最可能造成锁冲突、影响并发度的行往后放。

死锁和死锁检测

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

当出现死锁以后，有两种策略：

• 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，这个等待时间长得无法接受；但是如果将这个参数设置得过小，那么 InnoDB 可能就分不清锁等待和死锁。所以，第二种策略是比较常用的：主动监测死锁。

但是第二种策略也会面临一个问题：死锁检测可能会极大耗费 CPU 资源。
死锁检测的一般过程是：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

怎么解决由热点行更新导致的性能问题呢？

• 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
• 控制并发度。但是如果在客户端做并发控制，如果客户端很多，即使每个客户端并发量很小，MySQL 服务器的并发量也会很大。因此，这个并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现；如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
• 从设计上改善，通过将一行改成逻辑上的多行来减少锁冲突

加锁规则
加锁规则中，包含了两个“原则”、两个“优化”和一个“bug”：

• 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

接下来，我们的讨论基于下表 t：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

疑问：等值查询和“遍历”有什么区别？为什么 where 条件是不等号，这个过程里也有等值查询？

我们先分析一下这条查询语句的加锁范围：

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则，我们知道这个语句的加锁范围是主键索引上的 (0,5]、(5,10] 和 (10, 15)。也就是说，id=15 这一行，并没有被加上行锁。为什么呢？

我们说加锁单位是 next-key lock，都是前开后闭区间，但是这里用到了优化 2，即索引上的等值查询，向右遍历的时候 id=15 不满足条件，所以 next-key lock 退化为了间隙锁 (10, 15)

但是，我们的查询语句中 where 条件是大于号和小于号，这里的“等值查询”又是从哪里来的呢？

要知道，加锁动作是发生在语句执行过程中的，所以你在分析加锁行为的时候，要从索引上的数据结构开始。这里，我再把这个过程拆解一下。

如图所示，是这个表的索引 id 的示意图：

1. 首先这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个 id<12 的值”。
2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙。
3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。

也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法

等值查询的过程

与上面这个例子对应的，是下面这个语句的加锁范围是什么？

begin;
select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是 in，我们先来看这条语句的 explain 结果：

可以看到，这条 in 语句使用了索引 c 并且 rows=3，说明这三个值都是通过 B+ 树搜索定位的。
在查找 c=5 的时候，先锁住了 (0,5]。但是因为 c 不是唯一索引，为了确认还有没有别的记录 c=5，就要向右遍历，找到 c=10 才确认没有了，这个过程满足优化 2，所以加了间隙锁 (5,10)。
同样的，执行 c=10 这个逻辑的时候，加锁的范围是 (5,10] 和 (10,15)；执行 c=20 这个逻辑的时候，加锁的范围是 (15,20] 和 (20,25)。
通过这个分析，我们可以知道，这条语句在索引 c 上加的三个记录锁的顺序是：先加 c=5 的记录锁，再加 c=10 的记录锁，最后加 c=20 的记录锁。
你可能会说，这个加锁范围，不就是从 (5,25) 中去掉 c=15 的行锁吗？为什么这么麻烦地分段说呢？
因为我要跟你强调这个过程：这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的

理解了这个加锁过程之后，我们就可以来分析下面例子中的死锁问题了。如果同时有另外一个语句，是这么写的：

select id from t where c in(5,20,10) order by c desc for update;

此时的加锁范围，又是什么呢？
我们现在都知道间隙锁是不互锁的，但是这两条语句都会在索引 c 上的 c=5、10、20 这三行记录上加记录锁。
这里需要注意，由于语句里面是 order by c desc， 这三个记录锁的加锁顺序，是先锁 c=20，然后 c=10，最后是 c=5。
也就是说，这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。

怎么看死锁？
关于死锁的信息，MySQL 只保留了最后一个死锁的现场，但这个现场还是不完备的。现在，我就来简单分析一下上面这个例子的死锁现场。

下图是在出现死锁后，执行 show engine innodb status 命令得到的部分输出。这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息

这个结果分成三部分：

• (1) TRANSACTION，是第一个事务的信息；
• (2) TRANSACTION，是第二个事务的信息；
• WE ROLL BACK TRANSACTION (1)，是最终的处理结果，表示回滚了第一个事务。
  第一个事务的信息中：
• WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息；
• index c of table test.t，说明在等的是表 t 的索引 c 上面的锁；
• lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中；
• Record lock 说明这是一个记录锁；
• n_fields 2 表示这个记录是两列，也就是字段 c 和主键字段 id；
• 0: len 4; hex 0000000a; asc ;; 是第一个字段，也就是 c。值是十六进制 a，也就是 10；
• 1: len 4; hex 0000000a; asc ;; 是第二个字段，也就是主键 id，值也是 10；
• 这两行里面的 asc 表示的是，接下来要打印出值里面的“可打印字符”，但 10 不是可打印字符，因此就显示空格。
• 第一个事务信息就只显示出了等锁的状态，在等待 (c=10,id=10) 这一行的锁。
• 既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。我们从第二个事务的信息中推导出来。
  第二个事务显示的信息要多一些：
• “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁；
• index c of table test.t 表示锁是在表 t 的索引 c 上；
• hex 0000000a 和 hex 00000014 表示这个事务持有 c=10 和 c=20 这两个记录锁；
• WAITING FOR THIS LOCK TO BE GRANTED，表示在等 (c=5,id=5) 这个记录锁。

从上面这些信息中，我们就知道：

• “lock in share mode”的这条语句，持有 c=5 的记录锁，在等 c=10 的锁；
• “for update”这个语句，持有 c=20 和 c=10 的记录锁，在等 c=5 的记录锁。因此导致了死锁。这里，我们可以得到两个结论：
• 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
• 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以 InnoDB 选择了回滚成本更小的 lock in share mode 语句，来回滚。

怎么看锁等待？

看完死锁，我们再来看一个锁等待的例子：

可以看到，由于 session A 并没有锁住 c=10 这个记录，所以 session B 删除 id=10 这一行是可以的。但是之后，session B 再想 insert id=10 这一行回去就不行了

我们可以看下 show engine innodb status 的结果，信息是在这个命令输出结果的 TRANSACTIONS 这一节：

有几个关键信息：

1. index PRIMARY of table test.t ，表示这个语句被锁住是因为表 t 主键上的某个锁。
2. lock_mode X locks gap before rec insert intention waiting 这里有几个信息：
   • insert intention 表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身
   • gap before rec 表示这是一个间隙锁，而不是记录锁
3. 那么这个 gap 是在哪个记录之前的呢？接下来的 0~4 这 5 行的内容就是这个记录的信息。
4. n_fields 5 也表示了，这一个记录有 5 列：
   • 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段，十六进制 f 就是 id=15。所以，这时我们就知道了，这个间隙就是 id=15 之前的，因为 id=10 已经不存在了，它表示的就是 (5,15)
   • 1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id，表示最后修改这一行的是 trx id 为 1299 的事务
   • 2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到，这里的 acs 后面有显示内容 (% 和 4)，这是因为刚好这个字节是可打印字符
   • 后面两列是 c 和 d 的值，都是 15

因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)

说到这里，你可以联合起来再思考一下这两个现象之间的关联：

1. session A 执行完 select 语句后，什么都没做，但它加锁的范围突然“变大”了；
2. 当我们执行 select * from t where c>=15 and c<=20 order by c desc lock in share mode; 向左扫描到 c=10 的时候，要把 (5, 10] 锁起来

也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的

update 的例子

session A 的加锁范围是索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25] 和 (25,supremum]。

注意：根据 c>5 查到的第一个记录是 c=10，因此不会加 (0,5] 这个 next-key lock。

之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，可以理解为两步：

1. 插入 (c=1, id=5) 这个记录；
2. 删除 (c=5, id=5) 这个记录。

按照我们上一节说的，索引 c 上 (5,10) 间隙是由这个间隙右边的记录，也就是 c=10 定义的。所以通过这个操作，session A 的加锁范围变成了下图所示的样子：

接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了，一样地可以拆成两步：

1. 插入 (c=5, id=5) 这个记录；
2. 删除 (c=1, id=5) 这个记录。

第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了

索引的出现主要为了提高数据库的查询效率，像书的目录一样， 是空间换时间的**

索引的常见数据结构

• 哈希表

哈希表是一种以键-值（key-value）存储的数据结构，会有一个哈希函数将 key 换算出一个位置，然后将 value 存储进去。如果出现哈希一致的情况，就会拉一个链表出来存储

哈希表适合等值查询的情况

• 有序数组

有序数组是一种特殊的数组，里面的元素，按一定的顺序排列

有序数据的等值查询和范围查询的性能都不错，但是不适合插入数据，比较适合静态存储引擎，存储不会改变的数据

• 二叉搜索树

是具有以下性质的二叉树：

1. 若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；
2. 若任意节点的右子树不空，则右子树上所有节点的值均大于它的根节点的值；
3. 任意节点的左、右子树也分别为二叉查找树；
4. 没有键值相等的节点。

二叉搜索树的搜索效率高，但是其树高影响对磁盘数据块的访问，一个树高 20 的二叉搜索树，一次查询可能需要访问 20 个数据块，就会出现 20 次的磁盘随机读

• N 叉搜索树

N 取决于数据库的大小
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB 索引的数据结构**

InnoDB 中，表是根据主键顺序以索引的形式存放的，这种存储方式称为索引组织表，而 InnoDB 使用 B+Tree 数据结构，因此所有数据都是存储在 B+Tree 中的

每一个索引在 InnoDB 中都对应一个 B+Tree 树

根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存储整行数据，在InnoDB 中也被称为聚簇索引；非主键索引的叶子节点存储的是主键的值，在 InnoDB 中也被称为二级索引

基于主键索引和普通索引查询的区别？

• 主键索引查询只会搜索主键这一棵树
• 普通索引查询会先搜索普通索引的树，得到主键之后再去搜索主键的树（回表）

索引维护

B+Tree 为了维护索引的有序性，会出现页分裂和页合并，自增主键的插入模式会减少这两种操作的出现，提高性能并提高数据页的利用效率

主键的长度越小，普通索引的叶子节点越小，占用的空间也就越小

MySQL 5.6 原生Online DDL解析

select 1 判断

select 1 成功返回，只能说明这个库的进程还在，并不能说明主库没问题

set global innodb_thread_concurrency=3;

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 insert into t values(1,1)

设置 innodb_thread_concurrency 参数的目的是，控制 InnoDB 的并发线程上限。也就是说，一旦并发线程数达到这个值，InnoDB 在接收到新请求的时候，就会进入等待状态，直到有线程退出。

这里，我把 innodb_thread_concurrency 设置成 3，表示 InnoDB 只允许 3 个线程并行执行。而在我们的例子中，前三个 session 中的 sleep(100)，使得这三个语句都处于“执行”状态，以此来模拟大查询。
你看到了， session D 里面，select 1 是能执行成功的，但是查询表 t 的语句会被堵住。也就是说，如果这时候我们用 select 1 来检测实例是否正常的话，是检测不出问题的

查表判断

为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况，我们需要找一个访问 InnoDB 的场景。一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：

mysql> select * from mysql.health_check; 

使用这个方法，我们可以检测出由于并发线程过多导致的数据库不可用的情况，但是无法检测磁盘空间满的情况

更新事务要写 binlog，而一旦 binlog 所在磁盘的空间占用率达到 100%，那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据的

更新判断

既然要更新，就要放个有意义的字段，常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间。这条更新语句类似于：

mysql> update mysql.health_check set t_modified=now();

节点可用性的检测都应该包含主库和备库。如果用更新来检测主库的话，那么备库也要进行更新检测。
但备库的检测也是要写 binlog 的。由于我们一般会把数据库 A 和 B 的主备关系设计为双 M 结构，所以在备库 B 上执行的检测命令，也要发回给主库 A。
但是，如果主库 A 和备库 B 都用相同的更新命令，就可能出现行冲突，也就是可能会导致主备同步停止。所以，现在看来 mysql.health_check 这个表就不能只有一行数据了。
为了让主备之间的更新不产生冲突，我们可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键。

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 检测命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

由于 MySQL 规定了主库和备库的 server_id 必须不同（否则创建主备关系的时候就会报错），这样就可以保证主、备库各自的检测命令不会发生冲突

更新判断是一个相对比较常用的方案，不过存在判定慢的问题

首先，所有的检测逻辑都需要一个超时时间 N。执行一条 update 语句，超过 N 秒后还不返回，就认为系统不可用。
你可以设想一个日志盘的 IO 利用率已经是 100% 的场景。这时候，整个系统响应非常慢，已经需要做主备切换了。
但是你要知道，IO 利用率 100% 表示系统的 IO 是在工作的，每个请求都有机会获得 IO 资源，执行自己的任务。而我们的检测使用的 update 命令，需要的资源很少，所以可能在拿到 IO 资源的时候就可以提交成功，并且在超时时间 N 秒未到达之前就返回给了检测系统。
检测系统一看，update 命令没有超时，于是就得到了“系统正常”的结论。
也就是说，这时候在业务系统上正常的 SQL 语句已经执行得很慢了，但是 DBA 上去一看，HA 系统还在正常工作，并且认为主库现在处于可用状态

之所以会出现这个现象，根本原因是我们上面说的所有方法，都是基于外部检测的。外部检测天然有一个问题，就是随机性。
因为，外部检测都需要定时轮询，所以系统可能已经出问题了，但是却需要等到下一个检测发起执行语句的时候，我们才有可能发现问题。而且，如果你的运气不够好的话，可能第一次轮询还不能发现，这就会导致切换慢的问题

内部统计

MySQL 5.6 版本以后提供的 performance_schema 库的 file_summary_by_event_name 表里统计了每次 IO 请求的时间
file_summary_by_event_name 表里有很多行数据，我们先来看看 event_name='wait/io/file/innodb/innodb_log_file’这一行：

图中这一行表示统计的是 redo log 的写入时间，第一列 EVENT_NAME 表示统计的类型。
接下来的三组数据，显示的是 redo log 操作的时间统计。
第一组五列，是所有 IO 类型的统计。其中，COUNT_STAR 是所有 IO 的总次数，接下来四列是具体的统计项， 单位是皮秒；前缀 SUM、MIN、AVG、MAX，顾名思义指的就是总和、最小值、平均值和最大值。
第二组六列，是读操作的统计。最后一列 SUM_NUMBER_OF_BYTES_READ 统计的是，总共从 redo log 里读了多少个字节。
第三组六列，统计的是写操作。
最后的第四组数据，是对其他类型数据的统计。在 redo log 里，你可以认为它们就是对 fsync 的统计。

在 performance_schema 库的 file_summary_by_event_name 表里，binlog 对应的是 event_name = "wait/io/file/sql/binlog"这一行。各个字段的统计逻辑，与 redo log 的各个字段完全相同

因为我们每一次操作数据库，performance_schema 都需要额外地统计这些信息，所以我们打开这个统计功能是有性能损耗的。
所以，建议只打开自己需要的项进行统计。你可以通过下面的方法打开或者关闭某个具体项的统计，如果要打开 redo log 的时间监控，可以执行这个语句：

mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

开启了 redo log 和 binlog 这两个统计信息之后，可以通过 MAX_TIMER 的值来判断数据库是否出问题了。比如，你可以设定阈值，单次 IO 请求时间超过 200 毫秒属于异常，然后使用类似下面这条语句作为检测逻辑：

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

发现异常后，取到你需要的信息，再通过下面这条语句：

mysql> truncate table performance_schema.file_summary_by_event_name;

把之前的统计信息清空。这样如果后面的监控中，再次出现这个异常，就可以加入监控累积值了

总结
优先考虑 update 系统表，然后再配合增加检测 performance_schema 的信息

1. 安装 Git

sudo apt-get install git

2. 创建 git 用户

sudo adduser git

3. 创建证书登录

sudo mkdir /home/git/.ssh

将公钥数据写入（注意数据的完整性）

sudo vim /home/git/.ssh/authorized_keys

修改目录和文件的权限

sudo chown -R git:git /home/git/.ssh
chmod 700 /home/git/.ssh
chmod 600 /home/git/.ssh/authorized_keys

4. 初始化 git 仓库
   在 /srv 目录下输入命令：

sudo git init --bare demo.git
sudo chown -R git:git demo.git

5. 禁用 shell 登录

sudo vim /etc/passwd

将

git:x:1001:1001:,,,:/home/git:/bin/bash

改为

git:x:1001:1001:,,,:/home/git:/usr/bin/git-shell

6. 克隆远程仓库

git clone git@server:/srv/demo.git