2021-10-08

innodb存储引擎
插入缓存
将更新操作放入到insert buffer，然后以一定的频率刷将insert buffer和辅助索引的叶子结点数据进行merge
1、适用于非聚簇索引和唯一索引、应该会涉及到唯一值的校验。
2、适用于写多读少的系统，因为read会触发系统page merge。
3、如果全部都是insert，在crash恢复时恢复时间也会比较长。
change buffer 主要节省的则是随机读磁盘的 IO 消耗。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。
在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。merge 的执行流程是这样的：从磁盘读入数据页到内存（老版本的数据页）；从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

double-write 
    16K的page 仅仅写入了4K，发生了损坏
    double write分为2个部分，内存中2MB，磁盘2MB,2个区,128个页。
    先将脏页复制到内存中的buffer->buffer将脏页分为2次，每次1MB写入到磁盘的共享表空间->调用fsync同步到磁盘避免缓冲写失效->再将buffer中的脏页写入到各个表空间
异步IO
    单个SQL需要访问多个page,在查询时不需要等待其返回结果可先处理其余逻辑，此时可将多个io操作合并提升磁盘的iops。
刷新邻接页
    在刷脏时候会检测是否有属于同一区的脏页，主要目的是为了提升磁盘的IOPS，会存在刷脏了，但是又脏了的问题，如果是在ssd硬件的条件下iops不再受限可将其关闭。

事务相关：
隔离级别
原子性、持久性是通过redo实现，一致性是通过undo实现，隔离性是通过
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

    begin/commit 第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
    第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。
    commit work and chain 提交事务并开启下一个事务（少执行一个begin语句）

长事务
    1.通过information_schema.innodb_trx表监控事务的持续时间 
    2.增加undo表空间 
    3.通过配置参数max_execution_time指定事务执行的最长时间 
    4.利用pt工具监控长事务

索引相关
索引设计
常见的三种索引模型分哈希表、有序数组和搜索树。
索引设计的原则
1. 查询谓词都能够通过index进行扫描
2. 排序谓词都能够利用index的有序性
3. index包含了查询所需要的所有字段
这就是传说中的Three-star index。
《Wiley,.Relational.Database.Index.Design.and.the.Optimizers》
索引下推
select * from tuser where name like '张%' and age=10 and ismale=1;
在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，仅按顺序把“name 第一个字是’张’”的记录一条条取出来回表。
因此，需要回表 4 次。
图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。
在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
唯一索引和普通索引的选择
这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，所以尽量选择普通索引。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少（B+树的高度越高），搜索的效率也就会越低。

死锁问题
电影票在线交易业务，顾客 A 要在影院 B 购买电影票，我们简化一点，这个业务需要涉及到以下操作：
从顾客 A 账户余额中扣除电影票价->给影院 B 的账户余额增加这张电影票价->记录一条交易日志。
两阶段锁协议:在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

备份MDL锁的问题

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。

killed状态原因分析
这些“kill 不掉”的情况，其实是因为发送 kill 命令的客户端，并没有强行停止目标线程的执行，而只是设置了个状态，并唤醒对应的线程。
而被 kill 的线程，需要执行到判断状态的“埋点”，才会开始进入终止逻辑阶段。并且，终止逻辑本身也是需要耗费时间的。
超大事务执行期间被 kill：这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
大查询回滚：如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

解决方案：（可延展到innodb_thread_concurrency内部并发线程数）
innodb_thread_concurrency 这个参数的默认值是 0，表示不限制并发线程数量。
但是，不限制并发线程数肯定是不行的。因为，一个机器的 CPU 核数有限，线程全冲进来，上下文切换的成本就会太高。

如果一个事务被 kill 之后，持续处于回滚状态，从恢复速度的角度看，你是应该重启等它执行结束，还是应该强行重启整个 MySQL 进程。
因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。
当然，如果这个语句可能会占用别的锁，或者由于占用 IO 资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。
切换之后别的线程都断开了连接，自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的，减少系统压力，加速终止逻辑。

双1配置
sync_binlog ：
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
innodb_flush_log_at_trx_commit ：
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache

并行复制
不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
同一个事务不能被拆开，必须放到同一个 worker 中。
不用等到 commit 阶段，只要能够到达 redo log prepare 阶段，就表示事务已经通过锁冲突的检验了
MySQL 5.7 并行复制策略的思想是：
同时处于 prepare 状态的事务，在备库执行时是可以并行的；
处于 prepare 状态的事务，与处于 commit 状态的事务之间，在备库执行时也是可以并行的。
binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync
也就是说，这两个参数，既可以“故意”让主库提交得慢些，又可以让备库执行得快些。
在 MySQL 5.7 处理备库延迟的时候，可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。

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

GTID
GTID=server_uuid:transaction_id
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;commit;
set gtid_next=automatic;
start slave;
GTID跳错:
    stop slave;
    set gtid_next='';
    begin;commit;
    set gtid_next='automatic';
    start slave;
    
GTID主从切换:
    stop slave;
    change master to master_host='',master_port='',master_auto_position=1;
    start slave;

主从架构延迟方案解决
强制走主库方案
sleep 方案
主库更新后，读从库之前先 sleep 一下。具体的方案就是，类似于执行一条 select sleep(1) 命令。
判断主备无延迟方案；
1、每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。
2、Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
3、Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
上面判断主备无延迟的逻辑，是“备库收到的日志都执行完成了”。从 binlog 在主备之间状态的分析中，还有一部分日志，处于客户端已经收到提交确认，而备库还没收到日志的状态。
判断同步位点的方案还有另外潜在的问题，即：如果在业务更新的高峰期，主库的位点或者 GTID 集合更新很快，那么上面的两个位点等值判断就会一直不成立，很可能出现从库上迟迟无法响应查询请求的情况。
配合 semi-sync 方案
事务提交的时候，主库把 binlog 发给从库。
从库收到 binlog 以后，发回给主库一个 ack，表示收到了。
主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

semi-sync+ 位点判断的方案，只对一主一备的场景是成立的。在一主多从场景中，主库只要等到一个从库的 ack，就开始给客户端返回确认。
这时，在从库上执行查询请求，就有两种情况：如果查询是落在这个响应了 ack 的从库上，是能够确保读到最新数据；但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。
增强半同步
 rpl_semi_sync_master_wait_point
1.InnoDB Redo File Write
2.binlog File Flush & Sync
3.InnoDB Redo File Commit（同时释放事务持有的锁）
4.Send binlog to Slave。

等主库位点方案
select master_pos_wait(file, pos[, timeout]);
它是在从库执行的；参数 file 和 pos 指的是主库上的文件名和位置；timeout 可选，设置为正整数 N 表示这个函数最多等待 N 秒。

等 GTID 方案
trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
选定一个从库执行查询语句；
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
如果返回值是 0，则在这个从库执行查询语句；
否则，到主库执行查询语句。

大表查询
与“Sending to client”长相很类似的一个状态是“Sending data”，这是一个经常被误会的问题。
“Sending to client”，就表示服务器端的网络栈写满了。
“Sending data”并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段。

授权相关
全局权限
磁盘上，将 mysql.user 表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；
内存里，从数组 acl_users 中找到这个用户对应的对象，将 access 值（权限位）修改为二进制的“全 1”。
db 权限
磁盘上，往 mysql.db 表中插入了一行记录，所有权限位字段设置为“Y”；
内存里，增加一个对象到数组 acl_dbs 中，这个对象的权限位为“全 1”。
表权限和列权限
表权限定义存放在表 mysql.tables_priv 中，列权限定义存放在表 mysql.columns_priv 中。
这两类权限，组合起来存放在内存的 hash 结构 column_priv_hash 中。
这两个权限每次 grant 的时候都会修改数据表，也会同步修改内存中的 hash 结构。因此，对这两类权限的操作，也会马上影响到已经存在的连接。
正常情况grant/revoke都是可以立即生效，将表数据和内存权限数组维护一致的。仅在使用DML操作系统表会导致不一致的情况。
注意：
1、acl_dbs是一个全局数组，所有线程判断db权限都用这个数组。 但是如果当前会话已经处于某一个db中，之前use这个库的时候拿到的库权限会保存在绘画变量中，直到切换出这个库。
2、super 是全局权限，这个权限信息在线程对象中，而 revoke 操作影响不到这个线程对象。

LSN（log sequence number 日志序列号）
1、重做日志写入的数量
2、checkpoint的位置
3、页的版本，存在每个页的头部记录当前的版本号

InnoDB的数据恢复是一个很复杂的过程，在其恢复过程中，需要redo log、binlog、undo log等参与，这里把InnoDB的恢复过程主要划分为两个阶段。
第一阶段主要依赖于redo log的恢复
第二阶段，恰恰需要binlog和undo log的共同参与
第一阶段:
数据库启动后，InnoDB会通过redo log找到最近一次checkpoint的位置，然后根据checkpoint相对应的LSN开始获取需要重做的日志，
接着解析获取的日志并且保存到一个哈希表中，最后通过遍历哈希表中的redo log信息，读取相关页进行恢复。
redo log全部被解析并且apply完成，整个InnoDB recovery的第一阶段也就结束了，
在该阶段中，所有已经被记录到redo log但是没有完成数据刷盘的记录都被重新落盘。
然而InnoDB单靠redo log的恢复是不够的，这样还是有可能会丢失数据(或者说造成主从数据不一致)
第二阶段:
第一部分，扫面最后一个binlog获取所有可能没有提交事务的xid列表；
Tips：MySQL为何只需要扫描最后一个Binlog文件呢 ？
原因是每次在rotate到新的Binlog文件时，总是保证没有正在提交的事务，然后fsync一次InnoDB的redo log。
这样就可以保证老的Binlog文件中的事务在InnoDB总是提交的。
第二部分，根据undo中的信息构造所有未提交事务链表，最后通过上面两部分协调判断事务是否可以提交。
InnoDB当前版本有128个回滚段，每个回滚段中保存了undo log的位置指针，通过扫描undo日志，
我们可以构造出还未被提交的事务链表(存在于insert_undo_list和update_undo_lsit中的事务都是未被提交的)，
所以通过起始页(0,5)下的solt信息可以定位到回滚段，然后根据回滚段下的undo的slot定位到undo页，把所有的undo信息构建一个undo_list，
然后通过undo_list再创建未提交事务链表trx_sys->trx_list。
MySQL在第二阶段恢复的时候，先会去读取最后一个binlog文件的所有event信息，然后把xid保存到一个列表中，然后进行第二部分的恢复。

MVCC:
定义：
即多版本并发控制。MVCC 是一种并发控制的方法，即同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）
MVCC只在RC和RR两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。
当前读
像 select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)这些操作都是一种当前读，
它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
快照读
像不加锁的 select 操作就是快照读，即不加锁的非阻塞读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制。
即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。
实现
MVCC 就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现
MVCC 的目的就是多版本并发控制，在数据库中的实现是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。
隐藏字段
DB_TRX_ID(这条记录/最后一次修改该记录的事务 ID)
DB_ROLL_PTR（回滚指针指向这条记录的上一个版本）
DB_ROW_ID（隐含的自增 ID（隐藏主键）
undo日志
insert undo log：事务提交后可以被立即丢弃
update undo log：对应的日志才会被 purge 线程统一清除
同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log的链首就是最新的旧记录，链尾就是最早的旧记录
Read-View 读视图
就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，
记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)
RC与RR的区别：
总之在 RC 隔离级别下，是每个快照读都会生成并获取最新的 Read View；
而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View。

为什么RR能实现可重复读而RC不能,分两种情况
快照读的情况下,rr(可重复读)不能更新事务内的up_limit_id,而rc(读提交)每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc(读提交)不能可重复读
当前读的情况下,rr(可重复读)是利用record lock+gap lock来实现的,而rc(读提交)没有gap,所以rc不能可重复读
在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候重分配事务。

通常一个视图中包含创建视图的事务ID，以及在创建视图时活跃的事务ID数组。
例如，当开启一个视图时当前事务的事务ID为5， 事务链表上活跃事务ID为{2,5,6,9,12}，那么就会把{2,6,9,12}存储到当前的视图中（5是当前事务的ID，不记录到视图中），
{2,6,9,12}对应的事务所做的修改对当前事务而言都是不可见的，小于2的事务ID对当前事务都是可见的，大于12的事务ID对当前事务是不可见的。

那么如何判断可见性呢？ 对于聚集索引，每次修改记录时，都会在记录中保存当前的事务ID，同时旧版本记录存储在UNDO中；
对于二级索引，则在二级索引页中存储了更新当前页的最大事务ID，如果该事务ID大于readview->up_limit_id（对于上例，up_limit_id值为2），
那么就需要回聚集索引判断记录可见性；如果小于2， 那么总是可见的可以直接读取。

MHA切换流程
Phase 1: Configuration Check Phase completed.
1、HealthCheck
2、Alive Server check

Phase 2: Dead Master Shutdown Phase..
1、shutdown_script master server

Phase 3: Master Recovery Phase..
Phase 3.1: Getting Latest Slaves Phase.
Phase 3.2: Change Writer Domain Phase..
Phase 3.3: Determining New Master Phase..

Phase 4: Slaves Recovery Phase..
Phase 4.1: Starting Slaves in parallel..
Phase 5: New master cleanup phase..

Bufferpool\redo参数设置：
1、如果redo设置太大会导致crash-recovery时间过长
2、如果redo太小buffer太大会导致频check point；如果redo太大、buffer change会经常淘汰,增加IO
一般buffer:redo=10~5:1
Xtrabackup备份：
xtrabackup是基于innodb自身的崩溃恢复来实现，1个线程负责拷贝idb数据、1个线程负责扫描redo。
拷贝innodb文件->备份锁/FTWRL(lock tables for backup)->拷贝非innodb表->获取binlog点位（lock binlog for backup）->释放锁备份完成

间隙锁：
幻读：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
间隙锁是为了解决幻读而引入的锁，间隙锁之间并不互斥，也就是说在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
为什么binlog不是row模式会导致数据不一致？
1、索引不确定性
2、binlog_format和隔离级别会存在时许问题
MySQL加锁规则：包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。