mysql架构
极客时间mysql45讲总结
https://blog.csdn.net/junmoxi/article/details/85044982
https://blog.csdn.net/cph691647465/article/details/118677935
mysql基础架构
mysql的基本架构示意图
客户端—>连接器—>查询缓存
客户端—》连接器—》分析器—》优化器—》执行器—》存储引擎
连接器、缓存、分析器、优化器、执行器为server层
存储引擎为存储引擎层
连接器：管理连接，权限验证
分析器：词法分析，语法分析
优化器：执行计划生成，索引选择
执行器：操作引擎，返回结果（用户表权限的验证）
存储引擎：存储数据，提供读写接口
连接器
连接器负责跟客户端建立连接、获取权限、维持和管理连接。
TCP连接，输入用户名与密码
如果用户名或密码不对，你就会收到一个”Access denied for user”的错误，然后客户端程序结束执行。
如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。
一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。
客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。
数据库里面，
长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
缓存
查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。
如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。
不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，
1 | |
MYSQL 8.0 后不支持查询缓存
分析器
对 SQL 语句做解析，判断sql是否正确。（先会做“词法分析”，再做“语法分析”。 ）
词法分析：有没有这个表，有没有这个字段
语法分析：select 写成 elect ,语法有没有错误
优化器
在优化器之前调用 precheck 验证权限（你对这个表 T 有没有执行查询的权限）
决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器
在执行之前，判断时候有执行的权限。（precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。所以在执行器之前要再做权限验证）
select * from T where ID=10;
操作存储引擎：
1.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
涉及参数
mysql8.0以前可以禁用缓存
query_cache_type 设置成 DEMAND
wait_timeout 连接超时时间 秒
问与答
使用mysql长连接后。内存涨得快；导致内存占用太大，被系统强行杀掉（OOM）,异常重启。
因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。
解决方案：
1.定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2.如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
提示 you have an error in your sql syntax 语法错误，在哪个阶段抛出。
分析器抛出。
提示表不存在、字段不存在，在哪个阶段抛出。
分析器抛出。
提示权限不足
缓存、优化器之前、执行器。
mysql日志系统
mysql不会每次更新都需要写磁盘，先找到磁盘上的那条记录，再更新，整个过程IO成本、查找时间成本都很高。
所以mysql会使用WAL技术（write Ahead logging）,先写日志，再写磁盘。
redo log（重做日志）和 binlog（归档日志）
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
redo log 与 binlog 如何联系起来的？
它们有一个共同的数据字段，叫 XID。
binlog与redo log的不同：
1.redo log 是innoDB引擎特有的; binlog是mysql的server层实现的，所有引擎都可以使用。
2.redo log是物理日志，记录的是“在某个数据页上做了什么修改”; binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的C字段加1”
3.redo log 是循环写的，空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log(P02、P09、P12、P15、P23)
redo log 是实现了类似环形缓冲区，一个指针 write pos 是当前记录的位置，另一个指针 checkpoint 是当前要擦除的位置，write pos 和checkpoint 之间是空闲部分。如果 write pos 快追上 checkpoint 时，代表缓冲区快满了，需要暂停刷盘。
redo log buffer
1 | |
插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。
所以，redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。
redolog 写入机制
事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。
这三种状态分别是：
1.存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
2.写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
3.持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。
日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。
为了控制 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 来恢复的。
通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。
binlog(P02、P09、P12、P15、P23)
Server层日志。binlog 日志只能用于归档，没有crash-safe能力。
三个用途:
- 恢复：利用binlog日志恢复数据库数据
- 复制：主从同步
- 审计：通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击
格式：
| format | 定义 | 优点 | 缺点 |
|---|---|---|---|
| statement | 记录的是修改SQL语句 | 日志文件小，节约IO，提高性能 | 准确性差，对一些系统函数不能准确复制或不能复制，如now()、uuid()等 |
| row(推荐) | 记录的是每行实际数据的变更，记两条，更新前和更新后 | 准确性强，能准确复制数据的变更 | 日志文件大，较大的网络IO和磁盘IO |
| mixed | statement和row模式的混合 | 准确性强，文件大小适中 | 有可能发生主从不一致问题 |
binlog的三种格式对比：
一种是 statement，完整记录原sql,会因为主备库索引用的不一样，导至数据不一致（delete from t limit 3）
一种是 row。记录操作与操作记录详情，数据量会多（delete from t limit 100000）,但是易于恢复数据
可能你在其他资料上还会看到有第三种格式，叫作 mixed，其实它就是前两种格式的混合，如果msyql认为不会有歧义会用statement,会有用row
binlog 的写入逻辑
事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。
每个线程有自己 binlog cache，但是共用同一份 binlog 文件。
图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS(（Input/Output Operations Per Second)。
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 日志。
组提交
因为redolog 和 binlog的写入机制，这意味着我从 MySQL 看到的 TPS 是每秒两万的话，每秒就会写四万次磁盘。但是，我用工具测试出来，磁盘能力也就两万左右，怎么能实现两万的 TPS？
mysql 使用了组提交（group commit）机制。
日志逻辑序列号（log sequence number，LSN）。LSN 是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。
LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。
如图所示，是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。
从图中可以看到，
1.trx1 是第一个到达的，会被选为这组的 leader；
2.等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3.trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
4.这时候 trx2 和 trx3 就可以直接返回了。
组提交可以减少磁盘IO。
在并发更新场景下，第一个事务写完 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 来实现。
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
这两个条件是或的关系，也就是说只要有一个满足条件就会调用 fsync。所以，当 binlog_group_commit_sync_delay 设置为 0 的时候，binlog_group_commit_sync_no_delay_count 也无效了。
WAL 机制是减少磁盘写，可是每次提交事务都要写 redo log 和 binlog，这磁盘读写次数也没变少呀？
WAL 机制主要得益于两个方面：
redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
1.设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
2.将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
3.将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小。
update T set c=c+1 where ID=2的执行过程
取ID=2这一行–》数据页在内存中？—》不在内存—》磁盘读入内存
取ID=2这一行–》数据页在内存中？—》在内存中—》返回行数据—》将这行的C值加1—》写入新行—》新行更新到内存—》写入redolog处于prepare阶段—》写binlog—》提交事物处于commit状态
两阶段提交
1.redolog prepare阶段
2.写binlog
3.redolog commit
当在2之前崩溃时
重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。
当在3之前崩溃
重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog 。
磁盘数据页
innodb引擎的数据都是按数据页读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
buffer pool(内存缓冲池)（P12、P33）
InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页。
内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。
执行 show engine innodb status ，可以看到“Buffer pool hit rate”字样，显示的就是当前的命中率。
InnoDB Buffer Pool 的大小是由参数 innodb_buffer_pool_size 确定的，一般建议设置成可用物理内存的 60%~80%。
mysql使用了改进的LRU算法：
改进后的 LRU 算法执行流程变成了下面这样。
1.状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。
2.之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。
3.处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：
若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；
如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。
这个策略最大的收益，就是在扫描这个大表的过程中，虽然也用到了 Buffer Pool，但是对 young 区域完全没有影响，从而保证了 Buffer Pool 响应正常业务的查询命中率。
change buffer(P09)
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
merge
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
change buffer 是优化修改用的。
redo log 是保证 crash safe的。redo log会记录 change buffer的内容。
涉及参数
innodb_flush_log_at_trx_commit这个参数设置成1，表示每次事务的redo log都直接持久化到磁盘。
sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。
参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
binlog_format binlog格式
innodb_log_buffer_size log缓冲区大小
磁盘存储（P13）
一个 InnoDB 表包含两部分，即：表结构定义和数据。
在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。
而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。
参数 innodb_file_per_table设置为OFF，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
参数 innodb_file_per_table设置为ON,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
强烈建议使用on,如果drop一个表，磁盘上空间就可以使用了。
InnoDB 里的数据都是用 B+ 树的结构组织的。
磁盘空间的空洞
mysql删除一条记录，会这条记录标记为删除，然后如果有数据刚好符合这个位置的特性，就复用。如果删除的是整个数据页，就复用这个数据页，但数据页没有特性。总体来说mysql删除记录不会影响占用的磁盘空间。
mysql插入也会导导致磁盘空间的空洞，数据页与数据页。
如何去除这些已经删除了的数据的磁盘空间的空洞可以用重建表的方式。
如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心。在重建表的时候，InnoDB 不会把整张表占满，每个页留了 1/16 给后续的更新用。也就是说，其实重建表之后不是“最”紧凑的。
alter table A engine=InnoDB
5.6以前执行流程
5.6以后 online DDL
1.建立一个临时文件，扫描表 A 主键的所有数据页；
2.用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3.生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态；
4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5.用临时文件替换表 A 的数据文件。
5.6以前花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。
5.6以后引入了Online DDL。
Online（在线） 与 inplace（原地）
对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。
inplace 的 DDL是要占用临时空间的。
如果说这两个逻辑之间的关系是什么的话，可以概括为：
1.DDL 过程如果是 Online 的，就一定是 inplace 的；
2.反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。
optimize table、analyze table 和 alter table 这三种方式重建表的区别。
1.从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）online；
2.analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
3.optimize table t 等于 recreate+analyze。
涉及参数
参数 innodb_file_per_table设置为OFF，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
参数 innodb_file_per_table设置为ON,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
事务（P03、P08、P20、P21）
事务的特性：ACID(atomicity、consistency、isolation、durability),原子性、一致性、隔离性、持久性
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 读提交（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
mysql 5.6查看事务隔离级别：SELECT @@tx_isolation
mysql 8.0查看事务隔离级别：SELECT @@transaction_isolation
脏读：读到未提交的事务数据
不可重复读：一个事务执行过程中看到的数据，启动时和以后查询看到的不一致。（修改）
什么是幻读？
幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
幻读是针对insert导致的数据不一致，不可重复读是针对 delete、update导致的数据不一致。
如何解决幻读？
为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
读未提交没有视图的概念，
串行化是加锁来避免并行访问，
可重复读是在事务启动时创建的视图，整个事务存在期间都用这个视图，
读提交是每个sql语句开始执行的时候创建的。
当快照读时，因为读的是视图（MVCC），所以查询一直是一样的。
当“当前读”时(insert/update/delete/forupdate),读的是当前数据，使用间隙锁来防止幻读。
当快照读与当前读混用时,还是会出现幻读：
| 事务A | 事务A结果 | 事务B | 事务B |
|---|---|---|---|
| Begin | Begin | ||
| select * from t_a where id = 1; | 无结果 | ||
| Insert t_a(name) value(“test”) | 插入成功 | ||
| Commit | 提交成功 | ||
| select * from t_a where id = 1; | 无结果 | ||
| Update t_a set name = “test1” where id = 1; | 修改成功 | ||
| select * from t_a where id = 1; | 返回结果 |
| 事务A | 事务A结果 | 事务B | 事务B |
|---|---|---|---|
| Begin | Begin | ||
| select * from t_a where id > 0; | 无结果 | ||
| Insert t_a(name) value(“test”) | 插入成功 | ||
| Commit | 提交成功 | ||
| select * from t_a where id >0 for update | 返回结果 | ||
| Commit |
事务隔离的实现
回滚段、undo log
rollback segment称为回滚段，每个回滚段中有1024个undo log segment。每个undo操作在记录的时候占用一个undo log segment。
undo log有两个作用：提供回滚和多个行版本控制(MVCC)（Multi-Version Concurrency Control）。
在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样，undo log是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
数据库的多版本并发控制（MVCC）
在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。
当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。
同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。
回滚段会一直保留吗？
不会，在不需要的时候才删除。
什么时候才不需要回滚段？
当系统里没有比这个回滚日志更早的read-view的时候。
为什么建议你尽量不要使用长事务？
mysql会保留它可能用到的回滚记录，会导致大量占用存储空间。
长事务占用锁资源，可能拖垮整个库。
你会有什么方案来避免系统中出现长事务，或者如何处理这种情况？
在应用开发端：
1.general_log开启
2.不需要用的事务的方法去掉事务
3.set_max_execution_time避免单个语句意外执行太长时间
从数据库：
1.监控information_schema.innodb_trx表，设置长事务阈值，超过就报警/或者kill;
用于查找持续时间超过 60s 的事务
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
2.percona的pt-kill这个工具不错，推荐使用;
3.在业务功能测试阶段要求输出所有的general_log,分析日志行为提前发现问题;
4.如果使用的是mysql 5.6以后，把innodb_undo_tablespaces设置成2(或者更大的值)。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。
事务启动方式
MySQL 的事务启动方式有以下几种：
1.显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2.set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。
一致性视图
开启一致性视图
begin/start transactioin命令并不是一个事务的起点，在执行到它们之后的第一个操作innodb表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用start transaction with consistent snapshot之个命令。
启动方式一，一致性视图是在执行第一个快照读语句时创建的;
启动方式二，一致性视图是在执行start transaction with consistent snapshot时创建的。
一致性视图
在 MySQL 里，有两个“视图”的概念：
一个是 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。
innodb为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指是启动了但还没提交。
数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。
而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。
这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：
1.如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2.如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3.如果落在黄色部分，那就包括两种情况
a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
innoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。
查询语句：
一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：
1.版本未提交，不可见；
2.版本已提交，但是是在视图创建后提交的，不可见；
3.版本已提交，而且是在视图创建前提交的，可见。
更新语句：
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
除了 update 语句外，select 语句如果加锁，也是当前读。select加上 lock in share mode 或 for update也是当前读。就是分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）。
涉及参数
mysql 5.6查看事务隔离级别：SELECT @@tx_isolation
mysql 8.0查看事务隔离级别：SELECT @@transaction_isolation
索引（P04、P05、P09、P10、P11、P18）
索引的出现其实就是为了提高数据查询的效率。
索引的常见模型:哈希表、有序数组和搜索树
哈希表:
hashmap 数组加链表，新增数据和查询等值数据会快，查询区间数据慢。
使用Memory引擎时，使用的索引类型
有序数组：
在等值查询和范围查询场景中的性能就都非常优秀。二分法查询，logn,新增会移动插入后的数据会很慢。有序数组索引只适用于静态存储引擎。
二叉树:
因为数据是存储在磁盘中的，二叉树有可能会访问多个数据块，所以改进为b+树。
树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。
你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。
为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用“N 叉”树。这里，“N 叉”树中的“N”取决于数据块的大小。
以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。
N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。
InnoDB 的索引模型b+树
索引类型分为主键索引和非主键索引。
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
基于主键索引和普通索引的查询有什么区别？
普通索引需要回表，就是查一下主键索引。
索引维护
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。
一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
覆盖索引
select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
最左前缀原则
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
在建立联合索引的时候，如何安排索引内的字段顺序？
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
1 | |
索引ca可以去掉，如果查询条件中有C走C索引，如果有a会根据最左前缀原则走ab索引，如果查询条件c 和 b 会使用 c 和 cb
索引下推
可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
扫描行数是怎么判断的？
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
show index from 表名 查看表的索引情况。
show slow query log 中的rows_examined 的行数。
explain 语句 查看rows字段，扫描行数。
show status like “%Innodb_rows_read%”; 执行语句; 再执行show status like “%Innodb_rows_read%”两数相减获得行数。
MySQL 是怎样得到索引的基数的呢？
采样统计：InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
对于由于索引统计信息不准确导致的问题，你可以用 analyze table 来解决。
而对于其他优化器误判的情况，
你可以在应用端用 force index 来强行指定索引，
也可以通过修改语句来引导优化器，
还可以通过增加或者删除索引来绕过这个问题。
惟一索引与普通索引的选择
查询：
a、普通索引，查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录
b、唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索，但是，两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。
更新：
a. 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
b. 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。
更新这种情况，唯一索引会导致磁盘大量随机IO的访问（机械硬盘瓶颈）。
但这种情况不是绝对的，写多读少的场景change buffer记录的变更多，收益越大。常见业务模型账单类、日志类的系统。对于写完马上读取的情况，会立即触发merge，反而增加了维护change buffer的成本。
所以尽量选择普通索引。
索引失效
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
1.直接对索引字段做函数操作
2.隐式类型转换(字符串与整数进行比较时，字符串会转换成整数，如果字段是字符串，相当于加了转换函数，这时不走索引，如果条件是字符串，条件会转换成整数，可以走索引)
3.隐式字符编码转换（同隐式类型转换，比如utf8mb4与utf8）
虚拟列(P37)
1 | |
在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引（如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题）。
1 | |
1 | |
涉及参数
在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。
锁（P06、P07、P19）
数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
全局锁、表级锁和行锁三类
全局锁和表级锁是在server层实现的，而行级锁是在引擎层实现。
全局锁
全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 (FTWRL)
1 | |
当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。
在备份过程中整个库完全处于只读状态。
如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。
官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
一致性读是好，但前提是引擎要支持这个隔离级别。single-transaction 方法只适用于所有的表使用事务引擎的库。
但当引擎不支持事务时，只能使用FTWRL 命令了。
既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
1.其他业务上的判断，比如主从
2.没有回滚机制，一直是readonly=true
如何查看flush
使用 show processlist 命令查看 state列为Waiting for table flush，现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：
flush tables t with read lock;
flush tables with read lock;
但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了，所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
表锁
表锁的语法是 lock tables … read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
元数据锁(MDL)
另一类表级的锁是 MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
如何查看MDL写锁，MDL写锁会阻塞sql语句的执行
使用 show processlist 命令查看 state列为Waiting for table metadata lock，说明有mdl写锁
给一个小表加个字段，导致整个库挂了。
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。
我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。
由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。
前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
mdl读锁（select）执行的时间长点，mdl写锁（修改结构）被阻塞，mdl读锁（select）因为读写互斥，以后的mdl读都被阻塞，整个库挂掉。
如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
1 | |
行锁
行锁就是针对数据表中行记录的锁。
在innodb事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
顾客A要在影院B购买电影票。
1.从顾客A账户余额中扣除电影票价;
2.给影院B的账户余额增加这张电影票价;
3.记录一条交易日志;
因为影院B的账户会对应多个顾客所以会影响并发度。
3 1 2的编写顺序是比较好的。3是新增，1是个人账户，2是并发度高的账户。这样的顺序提升了并发度。
如何查看行锁
使用show processlist命令查看state列值为statistics ID字段为thread_id
或使select * from information_schema.innodb_trx where trx_mysql_thread_id =
例如
sessionA 执行update t set c=c+1 where id = 1;
sessionB执行select * from t where id = 1 lock in share mode;
怎么查出是谁占着这个写锁？
可以通过 sys.innodb_lock_waits 表查到。
select * from t sys.innodb_lock_waits where locked_table=’test.t‘\G
查看blocking_pid:4就是哪个线程的ID,执行kill 4
间隙锁（P20、P21）
跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。就是(0,5],(5,10],锁(0,5]是不包含0，包含5的锁。行锁为5，间隙锁为（0，5）
开区间 不包含 由（）表示； 闭区间 包含 由[] 表示
间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。
比如sessionA与sessionB同时执行select * for update语句，同时插入同一条数据，这时就形成了死锁。两个间隙锁同时要插入数据，但是都被对方拿着锁。
间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了，但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。为了解决binlog一致性问题。
如果binlog为statmen,会出现一致性问题：
binlog 里面的内容。
T2 时刻，session B 事务提交，写入了两条语句；
T4 时刻，session C 事务提交，写入了两条语句；
T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。
1 | |
这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。
出现了数据一致性问题。
mysql 可重复读的隔离级别下加锁规则：
包含了两个“原则”、两个“优化”和一个“bug”。
1.原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
2.原则 2：查找过程中访问到的对象才会加锁。
3.优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。只有记录存在时才会优化，不存在时会按普通索引处理。
4.优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。范围查询不会退化。
5.一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。(8.0版本已经不是这样了)
锁与索引关系
锁是加在索引上的.
lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
在惟一索引上做范围查询，左侧有等于时，会按照等值查询，后面按照范围查询。例如id>=10 and id <15
间隙锁是可重入锁，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。
死锁和死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程进入无限等待的状态，称为死锁。
出现死锁后的两种策略：
策略1.直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。默认是50秒。
策略2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以断续执行。将参数innodb_deadlock_detect设置为on,表示开启这个逻辑。
减少死锁的主要方向就是控制访问相同资源的并发事务量。
涉及参数
锁等待超时innodb_lock_wait_timeout来设置。默认是50秒。
问与答
你要删除一个表里面的前10000行数据。以下三个方法哪个好，为什么？
1.直接执行delete from T limit 10000;
2.在一个连接中循环执行20次delete from T limit 500;
3.在20个连接中同时执行delete from T limit 500;
方法一 长事务，造成锁等待，锁等待时间长。
方法三 人为造成锁竞争。
方法二比较好，短事务，锁时间短。
怎么解决由这种热点更新导致的性能问题呢？（cpu100%,每秒执行不到100个事务，因为开启了死锁检测，cpu一直在死锁检测）
方案1.如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
方案2.控制并发度。有中间件，可以考虑在中间件实现;有能改mysql源码的人，可以在mysql里面做。基本思路：对于相同行的更新，在进入引擎之前排队。
把一行并发资源改为多行资源，减少锁等待个数，也就减少了死锁检测的cpu消耗。（业务上有特殊处理）
1、业务不会出现死锁时，可以临时关闭
2、在客户端控制并发
3、修改MYSQL源码，并发引入引擎之前排队
4、将一行数据改为多行，如将一个余额账户分为多个，但在数据减少操作时需考虑小于0的情况。
自增主键（P39、P45）
主键是选自增主键还是业务主键
要求建表语句里一定要有自增主键。为什么？
自增主键，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
从性能和存储空间方面考量，自增主键往往是更合理的选择。
有没有什么场景适合用业务字段直接做主键的呢？
还是有的。比如，有些业务的场景需求是这样的：
只有一个索引；
该索引必须是唯一索引。
你一定看出来了，这就是典型的 KV 场景。
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。
自增值保存在哪儿？
表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值。
不同的引擎对于自增值的保存策略不同:
MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：
1.在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
2.在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。
自增值修改机制
1.如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
2.如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。
新的自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。
在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。
自增值的修改时机
1.执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
2.InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
3.将传入的行的值改成 (2,1,1);
4.将表的自增值改成 3；
5.继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。
自增锁的优化
在 MySQL 5.0 版本的时候，自增锁的范围是语句级别。也就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。显然，这样设计会影响并发度。
MySQL 5.1.22 版本引入了一个新策略，新增参数 innodb_autoinc_lock_mode，默认值是 1。
这个参数的值被设置为 0 时，表示采用之前 MySQL 5.0 版本的策略，即语句执行结束后才释放锁；
这个参数的值被设置为 1 时：普通 insert 语句，自增锁在申请之后就马上释放；类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
这个参数的值被设置为 2 时，所有的申请自增主键的动作都是申请后就释放锁。
生产有insert … select 这种批量插入数据的场景时优化
在生产上，尤其是有 insert … select 这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置：innodb_autoinc_lock_mode=2 ，并且 binlog_format=row. 这样做，既能提升并发性，又不会出现数据一致性问题。
批量插入数据，包含的语句类型是 insert … select、replace … select 和 load data 语句。
MySQL 有一个批量申请自增 id 的策略：
语句执行过程中，第一次申请自增 id，会分配 1 个；
1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。
在 binlog_format=statement 时， binlog里会有SET INSERT_ID=ID值的行，主从同步时不会出现问题。
MySQL 不同的自增 id 达到上限以后的行为(P45)
1.表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。
2.row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。
3.Xid 只需要不在同一个 binlog 文件中出现重复值即可。虽然理论上会出现重复值，但是概率极小，可以忽略不计。
4.InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，所以我们文章中提到的脏读的例子就是一个必现的 bug，好在留给我们的时间还很充裕。
5.thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。
问与答
自增值为什么不能回退？
导致性能问题
自增主键为什么不是连续的？
第一种原因: 惟一索引插入失败。
第二种原因：回滚也会产生类似的现象。
第三种原因：批量申请自增 id 的策略。
客户端(P32)
两个关于客户端的误解
第一个误解是：如果库里面的表特别多，连接就会很慢。
MySQL 客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作：
1.执行 show databases；
2.切到 db1 库，执行 show tables；
3.把这两个命令的结果用于构建一个本地的哈希表。
我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。
quick 是一个更容易引起误会的参数，也是关于客户端常见的一个误解。
MySQL 客户端发送请求后，接收服务端返回结果的方式有两种：
一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用 API 开发，对应的就是 mysql_store_result 方法。
另一种是不缓存，读一个处理一个。如果你用 API 开发，对应的就是 mysql_use_result 方法。
第一点，就是前面提到的，跳过表名自动补全功能。
第二点，mysql_store_result 需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能；
第三点，是不会把执行命令记录到本地的命令历史文件。
–quick 参数的意思，是让客户端变得更快。
查询从客户端到服务器的过程(P33)
取数据和发数据的流程是这样的：
1.获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
2.重复获取行，直到 net_buffer 写满，调用网络接口发出去。
3.如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
4.如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。
MySQL 是“边读边发的”，这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。
Sending to client就表示服务器端的网络栈写满了
对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，我都建议你使用 mysql_store_result 这个接口，直接把查询结果保存到本地内存。
Sending data表示正在执行
一个查询语句的状态变化是这样的（注意：这里，我略去了其他无关的状态）：
1.MySQL 查询语句进入执行阶段后，首先把状态设置成“Sending data”；
2.然后，发送执行结果的列相关的信息（meta data) 给客户端；
3.再继续执行语句的流程；
4.执行完成后，把状态设置成空字符串。
临时表（P36、P37）
用户临时表：
内存表和临时表的区别：
内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
而临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用 Memory 引擎。
临时表的特性：
建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。
临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。show tables 命令不显示临时表。
临时表就特别适合 join 优化这种场景:
不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。
不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。
临时表的应用：
分库分表时，select * from t where k<=n limit 100时，可以把所有库的limit 100条数据放到一个库上创建临时表，对临时表进行查询。
为什么临时表可以重名？
临时表frm 文件（保存表结构）存储位置：这个 frm 文件放在临时文件目录下，文件名的后缀是.frm，前缀是“#sql{进程 id}{线程 id} 序列号”
数据的存放方式：
在 5.6 以及之前的版本里，MySQL 会在临时文件目录下创建一个相同前缀、以.ibd 为后缀的文件，用来存放数据文件；
而从 5.7 版本开始，MySQL 引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建 ibd 文件了。
主从同步在非binlog_format=’row的时候也会同步过去。在 binlog_format=’row’的时候，临时表的操作不记录到 binlog 中，也省去了不少麻烦，这也可以成为你选择 binlog_format 时的一个考虑因素。需要注意的是，我们上面说到的这种临时表，
内部临时表：
分为内存临时表和磁盘临时表。
MySQL 什么时候会使用内部临时表？
1.如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
2.join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构；
3.如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union 需要用到唯一索引约束， group by 还需要用到另外一个字段来存累积计数。
Memory引擎(内存表)（P38）
InnoDB 和 Memory 引擎的数据组织方式是不同的：
InnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）。
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）。
InnoDB 和 Memory 引擎的一些典型不同：
1.InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
2.当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
3.数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
4.InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
5.InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。
内存表支hash索引和b+树索引
为什么不建议在生产环境上使用内存表：
1.锁粒度问题；
没有行锁，只有表锁
2.数据持久化问题。
在分布式布署情况下，会因为内存表出主从同步问题;会因为内存表出现业务上数据不见，表不见了等问题
有一个场景却是例外的：（使用内存临时表时，用于优化）
1.临时表不会被其他线程访问，没有并发性的问题；
2.临时表重启后也是需要删除的，清空数据这个问题不存在；
3.备库的临时表也不会影响主库的用户线程。
分区表（P43）
1 | |
这个表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件。也就是说：对于引擎层来说，这是 4 个表；对于 Server 层来说，这是 1 个表。
分区表的引擎层行为：
session A 的 select 语句其实只操作了分区 p_2018，所以，session B 要写入一行 ftime 是 2018-2-1 的时候是可以成功的，而要写入 2017-12-1 这个记录，就要等 session A 的间隙锁。
分区策略
每当第一次访问一个分区表的时候，MySQL 需要把所有的分区都访问一遍。一个典型的报错情况是这样的：如果一个分区表的分区很多，比如超过了 1000 个，而 MySQL 启动的时候，open_files_limit 参数使用的是默认值 1024，那么就会在访问这个表的时候，由于需要打开所有的文件，导致打开表文件的个数超过了上限而报错。
MyISAM 分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由 server 层控制。通用分区策略，是 MySQL 一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题。
从 MySQL 5.7.9 开始，InnoDB 引擎引入了本地分区策略（native partitioning）。这个策略是在 InnoDB 内部自己管理打开分区的行为。
MySQL 从 5.7.17 开始，将 MyISAM 分区表标记为即将弃用 (deprecated)，意思是“从这个版本开始不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能”。
从 MySQL 8.0 版本开始，就不允许创建 MyISAM 分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。
分区表的 server 层行为
如果从 server 层看的话，一个分区表就只是一个表。
可以看到，虽然 session B 只需要操作 p_2017 这个分区，但是由于 session A 持有整个表 t 的 MDL 锁，就导致了 session B 的 alter 语句被堵住。
MySQL 在第一次打开分区表的时候，需要访问所有的分区；
在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。
分区表的应用场景
分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过 alter table t drop partition … 这个语法删掉分区，从而删掉过期的历史数据。这个 alter table t drop partition … 操作是直接删除分区文件，效果跟 drop 普通表类似。与使用 delete 语句删除数据相比，优势是速度快、对系统影响小。
权限（P42）
MySQL 用户权限在数据表和内存中的存在形式;grant 和 revoke 命令的执行逻辑。flush privileges 语句.
create user ‘ua‘@’%’ identified by ‘pa’;
这条命令做了两个动作：
1.磁盘上，往 mysql.user 表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是 N；
2.内存里，往数组 acl_users 里插入一个 acl_user 对象，这个对象的 access 字段值为 0。
revoke all privileges on . from ‘ua‘@’%’;
这条 revoke 命令的用法与 grant 类似，做了如下两个动作：
1.磁盘上，将 mysql.user 表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为“N”；
2.内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 的值修改为 0。
全局权限
全局权限，作用于整个 MySQL 实例，这些权限信息保存在 mysql 库的 user 表里。
grant all privileges on . to ‘ua‘@’%’ with grant option;
这个 grant 命令做了两个动作：
1.磁盘上，将 mysql.user 表里，用户’ua’@’%’这一行的所有表示权限的字段的值都修改为‘Y’；
2.内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。
在这个 grant 命令执行完成后，如果有新的客户端使用用户名 ua 登录成功，MySQL 会为新连接维护一个线程对象，然后从 acl_users 数组里查到这个用户的权限，并将权限值拷贝到这个线程对象中。之后在这个连接中执行的语句，所有关于全局权限的判断，都直接使用线程对象内部保存的权限位。
基于上面的分析我们可以知道：
1.grant 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。
2.对于一个已经存在的连接，它的全局权限不受 grant 命令的影响。
db 权限
MySQL 也支持库级别的权限定义。如果要让用户 ua 拥有库 db1 的所有权限，可以执行下面这条命令：
grant all privileges on db1.* to ‘ua‘@’%’ with grant option;
基于库的权限记录保存在 mysql.db 表中，在内存里则保存在数组 acl_dbs 中。这条 grant 命令做了如下两个动作：
1.磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
2.内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。
db grant 操作对于已经存在的连接的影响,对use 库名的没有效果，对库名.表名的有效果。
表权限和列权限
MySQL 支持更细粒度的表权限和列权限。其中，表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中。
create table db1.t1(id int, a int);
grant all privileges on db1.t1 to ‘ua‘@’%’ with grant option;
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO ‘ua‘@’%’ with grant option;
跟 db 权限类似，这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。
因此，正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令。
flush privileges 命令。
flush privileges 命令会清空 acl_users 数组，然后从 mysql.user 表中读取数据重新加载，重新构造一个 acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍。同样地，对于 db 权限、表权限和列权限，MySQL 也做了这样的处理。
flush privileges 是在什么时候使用呢？显然，当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges 语句可以用来重建内存数据，达到一致状态。
这种不一致往往是由不规范的操作导致的，比如直接用 DML 语句操作系统权限表。
高可用（P07、P24、P25、P26、P27、P28、P29）
当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎么样？
1 | |
在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；
设置一个保存点，这个很重要（Q3）；
show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。
DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。
1.如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2.如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3.如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4.从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。
主备
主备：节点A主库、节点B备库
我依然建议你把节点 B（也就是备库）设置成只读（readonly）模式。这样做，有以下几个考虑：
1.有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
2.防止切换逻辑有 bug，比如切换过程中出现双写，造成主备不一致；
3.可以用 readonly 状态，来判断节点的角色。
因为 readonly 设置对超级 (super) 权限用户是无效的，而用于同步更新的线程，就拥有超级权限。
主从同步过程
在备库 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 读取中转日志，解析出日志里的命令，并执行。
主备延迟
主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。
1.主库 A 执行完成一个事务，写入 binlog，我们把这个时刻记为 T1;
2.之后传给备库 B，我们把备库 B 接收完这个 binlog 的时刻记为 T2;
3.备库 B 执行完成这个事务，我们把这个时刻记为 T3
你可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。
主备延迟的来源
有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
备库的压力大（由于主库直接影响业务，大家使用起来会比较克制，反而忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。）
备库压力大解决方案：
1.一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
2.通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。
大事务（例如delete多行或者大表的数据结构修改）
由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略。
可靠性优先策略：
1.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
2.把主库 A 改成只读状态，即把 readonly 设置为 true；
3.判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
4.把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
5.把业务请求切到备库 B。
会有一段时间不可用。
可用性优先策略：
1.把备库 B 改成可读写状态，也就是把 readonly 设置为 false；
2.把业务请求切到备库 B。
3.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；
4.把主库 A 改成只读状态，即把 readonly 设置为 true；
5.判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；
会有数据不一致情况需要处理
主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。
在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。
过期读（在从库上会读到系统的一个过期状态）
强制走主库方案；
将查询请求做分类，必须拿到最新数据的走主库，可以读到旧数据的走从库
sleep 方案；
select sleep(1)
如果这个查询请求本来 0.5 秒就可以在从库上拿到正确结果，也会等 1 秒；
如果延迟超过 1 秒，还是会出现过期读。
判断主备无延迟方案；
方案1：show slave status 结果里的 seconds_behind_master
方案2：对比位点确保主备无延迟：Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点； Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
方案3：对比 GTID 集合确保主备无延迟：Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
配合 semi-sync 方案；（一主一备）
事务提交的时候，主库把 binlog 发给从库；
从库收到 binlog 以后，发回给主库一个 ack，表示收到了；
主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。
当一主多从时：
如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；
但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。
等主库位点方案；
select master_pos_wait(file, pos[, timeout]);
trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的 File 和 Position；
选定一个从库执行查询语句；
在从库上执行 select master_pos_wait(File, Position, 1)；
如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
否则，到主库执行查询语句。
等 GTID 方案。
select wait_for_executed_gtid_set(gtid_set, 1);
trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
选定一个从库执行查询语句；
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
如果返回值是 0，则在这个从库执行查询语句；
否则，到主库执行查询语句
读写分离
读写分离，以及怎么处理主备延迟导致的读写分离问题。
两种架构：
1.客户端直连方案，因为少了一层 proxy 转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。你可能会觉得这样客户端也太麻烦了，信息大量冗余，架构很丑。其实也未必，一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如 Zookeeper，尽量让业务端只专注于业务逻辑开发。
2.带 proxy 的架构，对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由 proxy 完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy 也需要有高可用架构。因此，带 proxy 架构的整体就相对比较复杂。
双主库的情况下会有什么问题？
会有循环复制问题。
业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。
1.规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2.一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3.每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
如何判断一个数据库是不是出问题了？
1.select 1判断
innodb_thread_concurrency 参数的目的是，控制 InnoDB 的并发线程上限。
select 1 是能执行成功的，但是查询表 t 的语句会被堵住
建议把 innodb_thread_concurrency 设置为 64~128 之间的值。
并发连接和并发查询，并不是同一个概念。你在 show processlist 的结果里，看到的几千个连接，指的就是并发连接。而“当前正在执行”的语句，才是我们所说的并发查询。
在线程进入锁等待以后，并发线程的计数会减一
2.查表判断
select * from mysql.health_check;
空间满了以后，这种方法又会变得不好使。
3.更新判断
update mysql.health_check set t_modified=now();
为了让主备之间的更新不产生冲突，我们可以在 mysql.health_check 表上存入多行数据，并用 A、B 的 server_id 做主键。
因为，外部检测都需要定时轮询，所以系统可能已经出问题了，但是却需要等到下一个检测发起执行语句的时候，我们才有可能发现问题。而且，如果你的运气不够好的话，可能第一次轮询还不能发现，这就会导致切换慢的问题。
4.内部统计
其实，MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。
update setup_instruments set ENABLED=’YES’, Timed=’YES’ where name like ‘%wait/io/file/innodb/innodb_log_file%’;
比如，你可以设定阈值，单次 IO 请求时间超过 200 毫秒属于异常，然后使用类似下面这条语句作为检测逻辑。
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;
发现异常后，取到你需要的信息，再通过下面这条语句，把之前的统计信息清空。
truncate table performance_schema.file_summary_by_event_name;
备库从主库获取的binlog并行策略
看26讲
一主多从时，主库出问题，如何切换？
看27讲
误删数据(P31)
我们需要先对和 MySQL 相关的误删数据，做下分类：
1.使用 delete 语句误删数据行；
2.使用 drop table 或者 truncate table 语句误删数据表；
3.使用 drop database 语句误删数据库；
4.使用 rm 命令误删整个 MySQL 实例。
误删行
可以用 Flashback 工具通过闪回把数据恢复回来。
我不建议你直接在主库上执行这些操作。
这是因为，一个在执行线上逻辑的主库，数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点儿，就导致已经在之前误操作的基础上，业务代码逻辑又继续修改了其他数据。所以，如果这时候单独恢复这几行数据，而又未经确认的话，就可能会出现对数据的二次破坏。
我们不止要说误删数据的事后处理办法，更重要是要做到事前预防：
1.把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
2.代码上线前，必须经过 SQL 审计。
误删库 / 表
这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份 binlog。
在这两个条件都具备的情况下，假如有人中午 12 点误删了一个库，恢复数据的流程如下：
1.取最近一次全量备份，假设这个库是一天一备，上次备份是当天 0 点；
2.用备份恢复出一个临时库；
3.从日志备份里面，取出凌晨 0 点之后的日志；
4.把这些日志，除了误删除数据的语句外，全部应用到临时库。
预防误删库 / 表的方法：
第一条建议是，账号分离。这样做的目的是，避免写错命令。
比如：我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
第二条建议是，制定操作规范。这样做的目的，是避免写错要删除的表名。
比如：在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。
rm 删除数据
其实，对于一个有高可用机制的 MySQL 集群来说，最不怕的就是 rm 删除数据了。只要不是恶意地把整个集群删除，而只是删掉了其中某一个节点的数据的话，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。这时，你要做的就是在这个节点上把数据恢复回来，再接入整个集群。
kill(P32)
在 MySQL 中有两个 kill 命令：一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的。
不知道你在使用 MySQL 的时候，有没有遇到过这样的现象：使用了 kill 命令，却没能断开这个连接。再执行 show processlist 命令，看到这条语句的 Command 列显示的是 Killed。
收到 kill 以后，线程做什么？
实现上，当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：
1.把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
2.给 session B 的执行线程发一个信号。
发一个信号的目的，就是让 session B 退出等待，来处理这个 THD::KILL_QUERY 状态。
1.一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；
2.如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
3.语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。
kill 无效的情况
情况一：线程没有执行到判断线程状态的逻辑。跟这种情况相同的，还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。
情况二：终止逻辑耗时较长。
1.超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
2.大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
3.DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。