目录
一、MVCC多版本并发控制：实现隔离性，解决读-写冲突（无锁并发控制）
⭐⭐背诵版：
-
1） 多版本并发控制（MVCC）是一种用来解决 「读-写冲突」 的 「无锁并发控制」 ，同时也是实现 「隔离性」 的机制。它为事务分配单向增长的时间戳，为每个修改保存一个版本存储在undo log中，「读操作只能读到该事务开始前的数据库的快照」。
-
2） 通过MVCC机制，能够解决 「快照读」 下的 「脏读、幻读、不可重复读」 等问题，但不能解决第二类更新丢失问题；
-
3） MVCC的实现原理主要依赖于：
① 聚簇索引记录中的 「3个隐式字段」：隐藏的自增id、最后一次修改记录的事务id、指向undo log中上个版本的回滚指针
② 记录着历史记录版本链式结构「undo log日志」
③ 以及 根据可见性判断决定当前快照读能够读到的版本的读视图「Read View 」 来实现的； -
4） MVCC机制只在 「读已提交」 和 「可重复读」 的隔离级别下生效；在 「读已提交」 的隔离级别下，当前事务的每次快照读都会生成一个新的Read View， 这就保证了当前事务一定能通过可见性判断，读到其他事务提交的最新数据；而在 「可重复读」 的隔离级别下，Read View 只在当前事务第一次快照读的时候创建 ，后边再快照读时，读的都是第一个Read View ，保证当前事务能够重复读取数据；
MVCC决定当前快照读能读到的版本的步骤如下：
| 步骤 | 总结 | 执行 |
|---|---|---|
| Step1 | 事务修改前 + 排他锁 | 当某个事务A修改某条记录时，首先数据库会给该条记录加上排他锁； |
| Step2 | 拷贝原记录到undo log日志 | 事务A修改之前，将原记录拷贝一份到undo log日志中，作为链表头（最新的历史记录） |
| Step3 | 更新回滚指针指向的历史版本与最后一次修改的事务id | 事务A修改完毕，聚簇索引中的回滚指针指向undo log中的上一个历史版本，当前最后一次修改记录的id变为事务A的id |
| Step4 | 事务提交 + 释放锁 | 事务A提交，释放排他锁 |
| Step5 | 快照读时创建Read View | 当某个事务B进行快照读时，会在当前时刻创建一个读视图Read View，此视图维护着当前数据库中活跃（未提交的）事务id列表 |
| Step6 | 可见性判断 | 根据最后一次修改的事务id（DB_TRX_ID），与Read View中的属性进行「可见性判断」，决定当前快照读能够读到哪个版本的记录 |
| Step7 | 判断步骤1 | 若 DB_TRX_ID< 当前活跃事务id的最小值，则当前执行快照读的事务可以看到数据库最新的记录；否则，转入下一步判断； |
| Step8 | 判断步骤2 | 若 DB_TRX_ID > 当前活跃事务id的最大值，则当前执行快照读的事务不能看到数据库最新的记录；否则，转入下一步判断； |
| Step9 | 判断步骤3 | 若 DB_TRX_ID 不在 当前活跃事务列表中，则当前执行快照读的事务可以看到数据库最新的记录；否则，看不到当前最新数据；DB_LAST_ID就更新为undo log 中最新历史数据绑定的事务id值，转入Step7重新循环判断，直到找到当前事务快照读能够读到的历史版本进行展示； |
| Step10 | 展示读到的版本 | 根据可见性判断，找到了当前事务快照读所能读到的版本，进行读取展示 |
1、MVCC解决的是什么问题？
数据库并发有三种场景：
1）读-读： 不存在任何任何问题，不需要考虑并发控制；
2）读-写： 有线程安全问题，还要考虑事务隔离性，可能会出现脏读、幻读、不可重复读等问题；而加锁虽然能够解决读-写冲突，但是性能太差。因此引入了一种MVCC机制，可以实现无锁并发，实现事务隔离性，解决脏读、幻读、不可重复读等问题；但是不能解决更新丢失问题；
3）写-写： 有线程安全问题。采用加锁和乐观锁的机制可以解决写-写冲突；
通过MVCC + 乐观锁的机制（MVCC解决读写冲突，乐观锁解决写写冲突），能够最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题；
1）MVCC能解决幻读问题吗？
- 1） 当前读下MVCC无法解决幻读问题；需要用临键锁解决；
- 2）快照读下，读已提交 + MVCC无法解决幻读；原因是： 读已提交隔离级别下，MVCC每次快照读都会创建一个新的读视图ReadView，用于保证本次快照读能够通过可见性判断，进而读到其它事务已经提交的结果，此时【会出现幻读问题】；
- 3）快照读下：可重复读隔离级别 + MVCC能解决幻读问题；原因是： 可重复读隔离级别下，MVCC机制的ReadView，只在第一次快照读的时候创建，后续读到的都是第一次的ReadView（可重复读），保证不能通过可见性判断，进而每次都读取历史版本而不读最新版本，因此【不会出现幻读问题】；
2、MVCC的实现原理
| 原理 | 作用 |
|---|---|
| 聚簇索引中的3个隐藏字段 | ① 隐藏的自增主键id； ② 记录最后一次修改该记录的事务id； ③ 指向该记录的上一个版本的回滚指针； |
| undo log 日志 | 用于记录历史版本的快照，与事务id相关联。它能够解决数据库的一致性问题； ① insert undo log： 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃； ②update undo log： 事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除 |
| Read View 读视图 | 当前事务进行快照读时产生，维护当前活跃(未提交)的事务id；作用是进行可见性判断，根据read view的属性来判断当前事务能够看到哪个版本的数据； ① 读已提交隔离级别下： 当前事务每次快照读都会创建一个新的Read View ，保证当前事务能够读取到其他事务提交后的新数据； ②可重复读隔离级别下： Read View 只会在当前事务的第一次快照读中创建，后续的快照读，都是读的第一次创建的Read View ，保证可重读； |
3、举例说明MVCC的执行过程
persion表中有一条记录，name为Jerry，age为24岁，隐式主键是1。
1）初始没有事务修改，也没有历史版本，事务ID和回滚指针均为NULL
2） 事务1将表中name字段Jerry的改为Tom
① 在事务1修改该行(记录)数据时，数据库会先对该行加排他锁；
② 然后把该行数据拷贝到undo log中，作为当前新记录的历史版本；
③ 修改数据，事务id字段存储事务1的id，回滚指针指向undo log的历史版本数据；
④ 事务1提交后，释放锁；
3) 又来了个事务2修改person表的同一个记录，将age修改为30岁
① 在事务2修改该行(记录)数据时，数据库会先对该行加排他锁；
② 然后把该行数据拷贝到undo log中，发现undo log中已经有数据，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面
③ 修改数据，事务id字段存储事务2的id，回滚指针指向undo log的历史版本最新数据；
④ 事务2提交后，释放锁；
4、Read View 可见性判断算法过程
Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，可能是最新数据，也可能是undo log 中某个历史版本的数据；
Read View维护了维护系统当前活跃（未提交）事务的id列表，它有三个属性
| 属性 | 作用 |
|---|---|
| trx_list | 一个数值列表，用来维护Read View生成时刻系统正活跃的事务id |
| up_limit_id | 记录活跃事务列表中id最小(早)的事务id |
| low_limit_id | ReadView生成时刻系统尚未分配的下一个事务id，也就是目前已出现过的事务id的最大值+1 |
我们现在拿着最后一次修改记录的事务id（DB_TRX_ID）去进行比较；
Step1~~Step3的循环判断，直到找到当前事务快照读能读到的记录版本；
| 阶段 | 作用 |
|---|---|
| Step1 | 若 DB_TRX_ID< 当前活跃事务id的最小值，则当前执行快照读的事务可以看到数据库最新的记录；否则，转入Step2判断； |
| Step2 | 若 DB_TRX_ID > 当前活跃事务id的最大值，则当前执行快照读的事务不能看到数据库最新的记录；否则，转入Step3判断； |
| Step3 | 若 DB_TRX_ID 不在 当前活跃事务列表中，则当前执行快照读的事务可以看到数据库最新的记录；否则，看不到当前最新数据；DB_TRX就更新为undo log 中最新历史数据绑定的事务id值，转入Step1重新循环判断，直到找到当前事务快照读可以读到的历史版本； |
二、数据库的锁
1、MySQL锁机制
| 级别 | 引擎 | 原理 |
|---|---|---|
| 行锁 | InnoDB默认 | ① 记录锁： 将查询范围的行都锁上；共享锁(读锁) 与 排他锁(写锁) ② 间隙锁： 锁定一个范围，防止其他事务操作间隙插入或删除数据； ③ 临键锁： 记录锁 + 间隙锁；用于解决当前读下的幻读问题； |
| 表锁 | InnoDB支持 Myisam默认 | 表锁是一种悲观锁，分为 表共享锁 与 表排他锁 ；不会出现死锁现象； |
| 页锁 | InnoDB支持 | 页锁，即锁住B+树的一次IO读写的磁盘块，因此锁住的是一组相邻的数据；粒度介于行锁与表锁之间；会出现死锁 |
⭐记录锁、间隙锁、临键锁的底层
| 行锁 | 描述 |
|---|---|
| 记录锁 | 锁住的是B+数从根节点到叶子节点，从上往下的一条路径；能够保证该条记录不会被直接访问到，但是解决不了幻读问题；因为我们可以通过访问与该记录相邻的记录，然后根据叶子节点的next指针访问到该条记录，并且根据间隙插入新的数据； |
| 间隙锁 | 针对上边的问题，锁住的是叶子节点相邻磁盘块的next指针，使得我们不能通过查询相邻的数据然后再根据next指针进行插入操作； |
| 临键锁 | 幻读问题是更侧重数量的改变，但是原数据被修改也是不可以的，因此要同时用记录锁和间隙锁都锁上，保证不会被其它事务插入 |
⭐ 幻读问题，比如数据库中有3条记录
注意：幻读指的是前后两次查询同一个范围；
| 主键 | 姓名 |
|---|---|
| id=2 | 张三 |
| id=8 | 张三 |
| … | … |
| id=∞ | 王五 |
① 如果我们想查询id≥2的张三，那么首先for update会将在 id≥2范围内的所有记录加锁，其它事务就不能修改这个范围内的数据了；
- select * from user where id≥2 and name = “张三” for update;
② 为了防止其它事务操作间隙，比如其他事务想插入一条数据：
- insert into (id,name) values(3,“张三”)；
单纯用记录锁是解决不了该问题的，因此还要加上间隙锁，使得其它事务不能通过id=1(举例)通过next指针访问到id = 3的间隙，阻止其插入的动作；
2、悲观锁与乐观锁
注意：悲观锁与乐观只是一种思想；
| 锁 | 适用场景 | 原理 |
|---|---|---|
| 乐观锁 | 读多，读写冲突小 | ① CAS算法（失败重试） ② 版本号机制（只有版本号比当前事务的版本号大时才能操作） |
| 悲观锁 | 读多，读写冲突小 | 直接通过排他锁或共享锁锁住【行、表、页】，屏蔽一切可能违反数据完整性的操作； |
3、如何避免死锁？
| 解决方式 |
|---|
| ① 约定读取表的顺序； |
| ② 同一事务中，一次锁定全部的资源； |
| ③ 升级锁的颗粒，升级为页锁或者表锁； |
| ④ 乐观锁机制 或者 分布式锁（redis） |
三、数据库事务
1、事务的ACID是怎么保证的？
| 特性 | 概述 | 实现原理 |
|---|---|---|
| 原子性（A） | 要么都做，要么都不做 | undo log 日志： 事务未提交成功，则撤销已经执行成功的sql语句 |
| 一致性（C） | 从一个一致性到另一个一致性，例如转账 | 原子性 + 隔离性 + 持久性 + 业务层面上的逻辑 |
| 隔离性（I） | 事件之间不能互相干扰 | MVCC机制： 基于聚簇索引中的3个隐藏字段（自增id，回滚指针、事务id） + undo log日志（历史版本） + Read View读视图（可见性判断），保证读已提交与可重复读的隔离级别 |
| 持久性（D） | 事务提交写入磁盘，永久性保存 | redo log 日志： 在事务的执行过程中，将日志写入redo log日志中，即使数据没有持久化成功，只要redo log日志保存了。当Mysql服务器重启时，就会根据redo log日志重新持久化 |
2、事务的隔离级别?
| 隔离级别 | 问题 | 概述 | 原理 |
|---|---|---|---|
| 读未提交 | 脏读、幻读、不可重复读、第二类更新丢失 | 可以读到其它事务没有提交的执行结果 | - |
| 读已提交 | 幻读、不可重复读、第二类更新丢失 | 只能读到其它事务提交后的执行结果 | MVCC机制： 每次快照读都会创建一个新的Read View |
| 可重复读 | 幻读、第二类更新丢失 | 支持读已提交，可以重复读取相同的数据（Mysql默认） | MVCC机制： Read View只会在第一次快照读的时候创建，后续的快照读都是读的第一个Read View |
| 可串行化 | 无 | 支持可重复读，最高隔离级别（InnoDB分布式事务下默认） | 事务排序 + 共享锁，解决幻读 |
3、快照读与当前读
| 类型 | 形式 | 出现问题 | 如何解决 |
|---|---|---|---|
| 快照读 | 普通的select语句 | 脏读、幻读、不可重复读、第二类更新丢失 | ①脏读、幻读、不可重复读： MVCC机制（Read View 与可见性判断） ②第二类更新丢失： 乐观锁 + CAS重试版本号（版本号 > 当前事务版本号才能执行更新操作） |
| 当前读 | ①排他锁：select … for update ②共享锁：select …lock in share mode ③insert、update、delete：自动加了排他锁 for update | 幻读、第二类更新丢失 | ①幻读： 临键锁（当前读自身的排他锁 + 间隙锁） ②第二类更新丢失： 执行更新操作前，先用排他锁锁住，然后再更新； |
4、脏读、幻读、不可重复读、更新丢失问题
| 问题 | 描述 | 解决方式 |
|---|---|---|
| 脏读 | 读到了其它事务未提交的数据，然后其它事务回滚 | ①当前读下（即排他锁 / 共享锁）： 其它事务无法获取锁进行修改，也就不会有脏读的问题； ②快照读下： 利用MVCC机制的Read view读视图的创建时机与可见性判断，可以实现读已提交和可重复读的隔离级别，进行解决； |
| 不可重复读 | 同一事务中，两次读到的数据不一样 | ①当前读下（即排他锁 / 共享锁）： 其它事务无法获取锁进行修改，也就不会有不可重复读的问题； ②快照读下： 利用MVCC机制的Read view读视图的创建时机与可见性判断，来实现可重复读的隔离级别进行解决； |
| 幻读 | 由于其它事务的插入、删除是操作行的间隙，因此只加行锁是阻止不了其他事务操作间隙的。就会导致前后两次本事务读到的结果变多或者变少 | ①当前读下（即排他锁 / 共享锁）： 可以阻止其它事务的更新操作，但是不能阻止其它事务的插入、删除操作；因此要引入间隙锁把行的间隙也锁住；而当前读自己本身的排他锁 与 引入的间隙锁，就组合成了临键锁； ②快照读下： 只在可重复读隔离级别 + MVCC机制，可以解决幻读问题；原因就是利用MVCC机制的ReadView，只在第一次快照读的时候创建，后续读到的都是第一次的ReadView（可重复读），保证了每次都读取历史版本，因此不会出现幻读问题； 而读已提交隔离级别 + MVCC无法解决幻读问题； |
| 更新丢失问题 | ①第一类更新丢失（所有隔离级别都能够避免，可以忽略）： 事务A与事务B同时操作一个数据行，事务A先提交完毕，事务B回滚了，导致事务A的更新操作丢失 ②第二类更新丢失： 事务A与事务B同时操作一个数据行（都没加记录锁），事务A先提交完毕。然后事务B进行了修改然后提交，覆盖掉了事务A提交的结果 | 针对第二类更新丢失问题 ①更新前，先用当前读锁住行： 先select+（排他锁 / 共享锁）锁住当前行之后，再进行更新操作； ②快照读下： 乐观锁机制，在并发的表上加一个version字段，每次更新version+1；更新的时候只有版本号大于当前版本号才能更新成功（确保其它事务已经提交成功，否则CAS重试直到版本号满足更新条件） |
数据库第二类更新丢失的问题：
第二类数据丢失的问题是关于多个事务同时更新一行数据导致的问题，如下表所示，事务A和事务B都更新一行数据，他们事务开始的时候都查询到账户有1000元，然后都往账户添加了100元，最后大家都提交了各自的事务，结果却是错误的。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开启事务 | 开启事务 |
| T2 | 快照读（money = 1000元） | 快照读（money=1000元） |
| T3 | 存100（money = 1100元），此时直到事务A结束，事务B才能获得锁 | |
| T4 | ||
| T5 | 存款100（此时事务A已经提交，实际的money应该为1000，但是我们操作的还是1000），money = 1100（实际应该等于1200） | |
| T6 | 提交事务（money = 1100元），实际应该为1200元 | |
| T7 | 提交事务 (money =1100元) |
四、数据库的日志系统
1、四种日志总结
| 日志 | 作用 | 原理 |
|---|---|---|
| undo log 日志 | 保证 「原子性」 | 记录数据的历史版本，可回滚到事务未提交之前的版本，MVCC机制也需要undo log日志 |
| ⭐redo log 日志 | 保证 「持久性」 | redo log是InnoDB数据库引擎层面上的重做物理日志，基于WAL技术（先写日志，再写入磁盘）与两阶段式提交，保证事务提交后的数据能够写入到磁盘 |
| ⭐bin log 日志 | 数据的复制与恢复，实现 「主从复制」 | bin log是 MySql中Srevice层面上的逻辑日志，作用是记录所有数据库表的变更（create、Alter等）与表数据的修改（insert、update、delete）等；主服务器的binlog日志与从服务器的relay log共同实现主从复制； |
| relay log 日志 | 从服务器的中继日志，从服务器中存储从bin log中读到的数据；从服务器用于解析成SQL进行重放； 「主从复制」 | relay log日志有两个info结构； ①master.info：记录上一次从master节点同步的bin log位置，以及连接master和启动复制所需的全部信息； ②relay.info：记录了slave复制master文件的进度，以及下一次复制从哪个位置开始； |
2、bin log 日志 与 redo log 日志?
1）逻辑日志 bin log
- 作用： 逻辑日志bin log的作用是记录数据库表和表数据的修改操作，它有三种记录模式：基于SQL语句模式的statement、基于行的模式row以及混合模式；
- 工作层： MySql中的Srevice层上，可以结合任意存储引擎使用
- 空间大小： 无限
- 记录模式： 基于SQL语句的模式、基于行的模式、混合模式
| 模式 | 原理 |
|---|---|
| statement | 基于sql语句的模式。缺点：有些sql语句中有UUID等随机函数会导致记录的数据不一致； |
| row | 基于数据行的模式。直接记录行的变化，但是开销大，可能会造成库延迟，效率相对低一些； |
| mixed | 前两种的混合模式，根据语句来自适应的选择用哪一种（推荐） |
2）物理日志redo log
- 作用： 物理日志redo log的作用是实现数据库的持久化；Mysql中有一个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都太高。因此，MySql引入了WAL技术（Write-Ahead Logging），关键点就在于先写入redo log日志然后同步更新内存，InnoDB 引擎在系统空闲的时候再写入到磁盘中持久化，提高效率；
- 工作层： InnoDB存储引擎特有的
- 空间大小： 空间大小固定，循环写入，当空间不够时，就需要先持久化到磁盘，然后空出来的部分再写入
3）bin log 日志与 redo log 日志的区别？
| bin log 日志 | redo log 日志 | |
|---|---|---|
| 工作层 | Service层 | InnoDB存储引擎特有的 |
| 日志类型 | 逻辑日志： 存储sql语句修改的逻辑，用于主从复制与数据恢复 | 物理日志： 存储的是“在某个数据页上做了什么修改”，更新内存并写入磁盘进行持久化保存 |
| 内存空间 | binlog 是可以追加写入的，没有空间的概念，一直写就行 | redo log 是循环写入的，有固定大小的，所以它的空间会用完，如果用完的话，一定要进行一些写入磁盘的操作才可以继续； |
| crash-safe能力 | 不具备 | 能够恢复到数据库宕机之前的最后一次事务提交版本 |
4）bin log 日志与 redo log 日志如何保证一致性：二阶段提交（2pc）
阶段一：二阶段提交
redo log 和 binlog 有一个共同的数据字段，叫 XID；
| 提交阶段 | 执行内容 |
|---|---|
| Step1 | 事务开始执行； |
| Step2 | 开始写入redo log日志，并同步持久化到redo log的磁盘空间；redo log事务中记录二阶段提交的XID，并且标识变为prepare ； |
| Step3 | 开始写入bin log日志，并同步持久化到bin log的磁盘空间；写完之后，bin log事务记录二阶段提交的XID； |
| Step4 | bin log 写入成功，redo log事务标识变为commit； |
| Step5 | 事务提交； |
阶段二：数据恢复：
⭐若在上述二阶段提交的过程中，数据库宕机了；那么在MySql重启时，会根据 redo log的标识 与 bin log的二阶段XID字段 进行数据恢复，判断是否应该提交[宕机时未能提交的事务]以保证redo log 与 undo log 中数据的一致性；
⭐ 恢复关键点：redo log 与 bin log 是否全部写入成功；
原因：redo log与bin log的执行过程是在事务的执行过程中进行的，实际上当redo log 与 bin log 都写入成功时，事务就已经真正意义上的提交了，因此数据恢复时，是否提交[由于宕机未能提交的事务]关键就在于：redo log 与 bin log 是否都写入成功；
| 恢复阶段 | 执行内容 |
|---|---|
| Step1 | 按顺序扫描redo log，如果redo log中的事务既有prepare标识，又有commit标识，就表示事务在日志层面上已经真正意义的提交了，此时我们可以直接提交【宕机时未提交成功的】事务，保证数据库的表中、redolog日志、binlog日志三者的一致性 |
| Step2 | 若redo log中的事务只有prepare标识，则证明bin log的写入过程宕机了；此时要根据XID字段来判断bin log日志是否已经写入成功，以决定是否应该提交 【宕机时未提交成功的】事务； |
| Step3 | ①若bin log中没有二阶段提交的XID字段，那么证明bin log日志也没有写入成功；则使用undo log来回滚redo log中的对应事务（prepare已经写入了redo log），保证两个日志中的数据一致； ②若bin log中存在二阶段提交的XID字段，那么证明bin log 日志写入成功；此时可以提交 【宕机时未提交成功的】事务，保证一致性； |
5）MySQLl的执行查询过程？
注意：查询缓存现在已经没有了，因为缓存命中率低；
五、MySQL主从复制
1、什么是主从复制?
MySQL主从复制指的是，数据可以MySQL的主服务器（master）复制到其它的从服务器（slave）中；MySQL默认采用异步复制的方式，因此从服务器可以不用一致连接着主服务器，甚至可以用拨号的方式断断续续的连接主服务器；根据配置文件，从服务器可以指定复制主服务器中全部或者部分的数据；作用：通过主从复制，可以实现读写分离（主写从读）以及数据备份，提高数据库的访问性能以及数据安全性；
2、主从复制的作用？
- 1）读写分离，提高数据库访问性能； 主(写)从(读)，减少主服务器的访问压力；
- 2）数据备份，提高数据安全性； 从服务器可多地备份主服务器的数据，且能随时终止复制流程，不破坏主服务器的数据；
3、主从复制的流程（原理）？
主从复制过程的关键点：
| 文件 | 位置 | 作用 |
|---|---|---|
| bin log日志 | 主 | 用于记录主服务器中表结构与行数据的更新操作（基于SQL语句的模式、基于行变化的模式、混合模式） |
| dump线程 | 主 | 响应从服务器的I/O线程请求，给从服务器发送bin log日志数据 |
| I/O线程 | 从 | 当从服务器探测到了主服务器bin log日志的变化，则开启I/O线程向主服务器请求读取bin log日志 |
| SQL线程 | 从 | 从服务器开始SQL线程读取relat log日志，解析成SQL语句进行本地重放 |
| relay log日志 | 从 | 从服务器的I/O线程读取bin log日志后，写入到本地的中继日志relay log中； |
| master.info | relay log 日志 | 记录了上一次I/O线程读取到bin log的位置；目的是：本次I/O线程从bin log的哪个位置请求读取 |
| relay.info | relay log 日志 | 记录了上一次SQL线程读取relay log的位置；目的是：本次SQL线程从relay log的哪个位置开始SQL重放 |
⭐主从复制的过程：
| 阶段 | 服务器 | 执行内容 |
|---|---|---|
| Step1 | 主 | master服务器将更新的SQL语句，记录在master中的bin log 日志中；然后授予slave服务器远程连接的权限； |
| Step2 | 从 | ① slave服务器在一定时间间隔中探测bin log日志是否发生改变； ② 若发生改变，则slave开启I/O线程，向master服务器请求读取bin log日志； |
| Step3 | 主 | master服务器收到slave服务器I/O线程的请求，开启 dump线程 给slave服务器传输bin log日志的二进制数据 |
| Step4 | 从 | ① slave服务器收到bin log日志数据，存储到TCP/IP缓存，立即返回ACK给主库（解决主从同步延时问题），并更新master.info（记录了读取到了bin log日志的哪个位置）；② 将TCP/IP缓存，写入到自己的中继日志relay log； ③ 然后开启SQL线程，SQL线程读取relay log中的relay.info，获取到SQL线程上次读取relay log的位置，然后将relay log的新数据解析成SQL语句，进行本地重放（保证slave与master的数据一致性）；重放结束后，更新relay.info； ④ I/O线程与SQL线程进入睡眠，等待下一次被唤醒； |
六、MySQL索引
1、MySQL中的两种存储引擎
| 存储引擎 | 索引结构 | 区别（数据存储位置） |
|---|---|---|
| InnoDB引擎 | B+Tree | 数据与主键都存储在B+Tree的叶子节点中，非叶子节点存储的是叶子节点的key值 |
| Myisam引擎 | B+Tree | B+Tree的叶子节点存储的是数据的磁盘物理地址，需要额外再进行一次IO去真正的地址中读取数据 |
2、为什么索引数据结构使用B+树？
1）索引的数据结构总结
| 数据结构 | 原因 |
|---|---|
| 二叉树 | 缺点： 数据量越多，树越高，且树的高度不能自平衡，查找效率与树的高度有关； |
| 红黑树 | 缺点： 红黑树本质上也是二叉树，因此随着数据量越多，查找效率会随着树的高度增加而变差； |
| Hash | 优点： ①能够快速定位数据，更适用于等值查询； 缺点： ①不适用于范围查询； ②不支持模糊查询； ③不支持排序； ④不支持只查询部分字段； ⑤数据量极大时，容易哈希碰撞； ⑥Hash表占用大量内存； |
| B-tree | B-tree就是 多叉二叉搜索树 优点： ① B-tree是一个高度自平衡的多叉树，数据分布在各个节点中 （每个节点都保存有key-value值）； ②每个节点都储存了多个关键字，I/O效率更高; ③相对B+树，当要查询的数据离根节点越近时，查询效率通常比B+树高； 缺点： ① B-tree不利于范围查找(区间查找)，如果要找 0~100的索引值，那么B树需要多次从根结点开始逐个查找 ② 由于B树的每个节点都存储了key和数据，导致读取数据的时候IO次数不稳定，若目标数据离根节点越近，那么IO次数就越少；反之则越多；这是因为存放相同的数据量，B树的高度远比B+树的高度高的多，因此IO次数不稳定； |
| B+tree | 优点： B+tree同样是一个高度自平衡的多叉树，它与B-tree的区别就在于： ① B+tree只有叶子节点存储key-value值（主键 + 数据），其它节点都属于索引节点只存储key值（叶子节点的主键）；这就保证了存放相同的数据量，B+树的高度比B树要低的多，而且由于数据只存放在叶子节点中，这样就能保证每次查询数据IO次数是固定的，总是要到叶子节点中查询。IO次数稳定，效率更好； ② B+tree的每个叶子节点都指向相邻的叶子节点的地址（即顺序访问指针），解决了B-tree不适用于范围查询的问题；因此B+tree实用性更好，效率更高； |
2）B树与B+树的查询过程
⭐2.1）B树的查询过程
注意：① B树的每个节点都存储着数据data；
② 一次IO就是读取一个16KB大小的磁盘块（数据页）
16KB的数据可以改，调整参数innodb_page_size；
1）以查询 key值为【29】的数据data为例：
| 步骤 | 执行 |
|---|---|
| Step1 | 查磁盘块1（数据页），16 ≤ 【29】 ≤ 34，则选中该层的指针p2； |
| Step2 | 查磁盘块3（数据页），25 ≤ 【29】 ≤ 31，则选中该层的指针p2； |
| Step3 | 查磁盘块8（数据页），找到目标key值【29】，得到数据data； |
2）以查询key值为【64】的数据data为例：
| 步骤 | 执行 |
|---|---|
| Step1 | 查磁盘块1（数据页），34 ≤ 【64】，则选中该层的指针p3； |
| Step2 | 查磁盘块4（数据页），找到目标key值【64】，得到数据data； |
⭐2.2）B+树的查询过程
注意：B+树只有叶子节点存储着数据data，其它节点只存储叶子节点的key值；因此在查找时，要先从非叶子节点中获取key值，然后再去叶子节点中获得数据data
1）以查询 key值为【15】的数据data为例：
| 步骤 | 执行 |
|---|---|
| Step1 | 查磁盘块1（数据页），【15】 ≤ 28，则选中该层的指针p1； |
| Step2 | 查磁盘块2（数据页），10 ≤ 【15】 ≤ 17，则选中该层的指针p2； |
| Step3 | 查磁盘块5（数据页），找到目标key值【15】，得到数据data； |
2.3）B+树的两种查询方式
- 在B+Tree上有两个头指针，一个向指向根节点。另一个指向主键key最小的叶子节点。且所有叶子节点（数据节点）之间是一种链式环型结构。因此B+树有两种查询方式：
- 1）从【根节点】开始进行开始【随机查找】；
- 2）从【主键最小的叶子节点】开始进行【范围查找和分页查找】
3）为什么B+树只在叶子节点中存储数据，非叶子节点只存储key？
① 通常情况下，数据库的B+树高度在3~4层左右，已经能够存储千万级别的数据量了；
②能够当作key值的类型的优先级：主键索引 > 唯一索引 > 6个字节的row_id
⭐原因：
- 总： B树与B+树1次读取就是与磁盘的1次IO交互，而磁盘(数据页)占用16KB的空间；也就是说，如果16KB的空间能够存储的key值（主键值）范围越大，那么我们1次IO交互就能读到的数据量越多；换句话说，在数据量固定的情况下，磁盘能存储的key值范围越大，我们就能减小IO交互次数，提高IO读写效率；
- 分： ① B树的每个16KB的磁盘节点（可以调整参数innodb_page_size）中，存储着三个类型的数据：【key值（主键）、value值（查询的数据）、指针（存储下一个磁盘节点的地址信息）】，而这些数据中（查询的数据）占用的空间最大，导致剩余能够存储key值范围的空间就很少；
② 而B+树只会在叶子节点层进行存储（查询的数据），而非叶子节点层只需要存储叶子节点的key值与指针，而且一旦可以不存储data数据（data数据占主要的16KB极大部分的内存），那么相同高度下就能够比B树存储更多的key值范围；这样1次IO交互读取的内容就越多，从而减少数据库IO交互次数，提高数据库的IO读写效率；
4）索引主键key的存储类型是用int还是varchar？
- 综上分析，在一个16KB的磁盘中，主键key占的字节越少，那么能存储的key范围就越大；因此用int还是varchar就看哪个占的字节小；int（4个字节），① 当varchar占的字节小于4个时，推荐用varchar ② 反之，则用int类型；
以一颗高度为3的B树与B+树举例说明上述原因：
- ⭐B树： key值（主键）和 指针（存储下一个磁盘节点的地址信息）所占的空间非常小，我们假设忽略；并且假设value值（查询的数据）占1KB的空间，那么一个磁盘就只能包含16 / 1 = 16个key-value对，这个16也被称之为节点的度（Degree）；那么3层的高度，就可以存储 16 × 16 × 16 = 4096 个数据，如图所示：
-
⭐B+树： 由于B+树只在叶子节点层存储数据，其它层都不存数据；因此其它层能够存储的key值范围就越大；B+树在非叶子节点层只存储两个类型的数据：【叶子节点中的key值，指针（存储下一个磁盘节点的地址信息）】，我们就假设这两个类型的数据占10个字节，那么16KB的磁盘数据，就能存储 16 × 1024 / 10 = 1638个key值；那么高度为3，已知最后一层可以存储16个数据；那么总共能存储：1638 × 1638 × 16 = 42928702 个数据，已经达到了千万级别，是B树所能存储数据的1万倍；
图略：B+树的图就略了，原理与上边B树的一样；
一句话总结原因： B+树通过只在叶子节点存储数据的方式，能够大大的提高一个磁盘（数据页）中存储的key值数量；因此存储相同的数据量时，B+树比B树的高度要低的多；在查询数据时，B树由于每个叶子节点都存放了数据，因此总的IO次数(读取磁盘)是不一定的，要看目标数据离根节点是否近，如果离着近，那么IO次数是比B+树少的；但是如果是在叶子节点，那么B树高度又高，磁盘IO次数会非常多，远超过B+树；而B+树的只在叶子节点存放数据，这就保证了每次IO的次数固定，IO次数等于树的层数，而且B+树的高度又低，通常3-4层已经能够应对千万级别的数据了，所以总的IO效率B+树更好；
3、聚簇索引与非聚簇索引
| 存储引擎 | 存储内容 | 是否需要回表 | |
|---|---|---|---|
| 聚簇索引 | InnoDB引擎特有的，主键就是聚簇索引 | 索引B+树的叶子节点，存储的是：【数据 + 索引】 | 不需要回表，找到聚簇索引就找到了数据 |
| 非聚簇索引（二级索引） | ①InnoDB引擎下，除了主键索引以外的索引都是非聚簇索引（包括自己建的） ②Myisam引擎下，主键索引和二级索引都是非聚簇索引 | 索引B+树的叶子节点，存储的是：【聚簇索引的key值】 | 根据是否二级索引覆盖来判断是否要回表； ①若查询的内容讲就是二级索引本身能够查询到的（索引覆盖），则无需回表； ②没有覆盖索引，则需要回表； |
1）为什么聚簇索引的key值推荐使用自增主键？
-
① 自增主键的区分度为1，检索效率高；
-
② 由于自增主键是连续的，因此在插入或者删除过程中，只需要追加到最后；能够减少数据的移动，减少页分裂和移动的频率
-
解释： 每次向B+树添加或者删除节点时，都会影响到B+树的整体结构或者高度的变化（当达到某个磁盘块（数据页页）的最大值时，就会向上分裂，树的高度会变化）；如果不适用自增主键，那么B+树每次插入和删除都要判断是否要分裂，维护麻烦；反之如果使用自增主键id，那么我们只需要向叶子节点后按顺序追加到最后即可，当达到磁盘页的最大值时，再进行分裂即可，维护方便；
2）分布式情况下还能用自增主键充当B+树的叶子节点key吗？
- 不能用自增主键；
- 分布式数据库，B+树的key需要用雪花算法或者分布式id生成器；
4、回表查询
1）什么是回表？
① 数据只在聚簇索引中的叶子节点中存储，非聚簇索引只存储【自己本身的索引字段数据 + 聚簇索引中叶子节点的key值】；
② 一个表中可以含有多个字段充当索引， 但是能够当作聚簇索引中的key值的只有3种：主键索引 > 唯一索引 > 6个字节的row_id ；除了这些，其它索引都属于非聚簇索引；
③ 因此，当我们使用非聚簇索引进行查询时，首先要查询非聚簇索引的B+树。然后判断是否非聚簇索引自己本身的字段已经能够命中想要查询的全部内容（覆盖索引）；1) 如果能覆盖，则不需要再去聚簇索引的B+树中查询（回表）；2) 如果不能覆盖，则需要拿着从非聚簇索引中查询的key值，再去聚簇索引的B+树中查询；
2）回表的几种情况分析
三种查询的执行流程：
-
1）根据主键索引（聚簇索引）查询：不需要回表
①SQL语句： select * from table where id = 5；
②分析： 直接查询聚簇索引的B+树结构，索引与数据放在一起，直接命中要查询的内容，无需回表；
-
2）根据二级索引（非聚簇索引）查询
2.1）不需要回表的情况：二级索引覆盖
①SQL语句： select name,id from table where name = 李四；
②分析： name是二级索引（非聚簇索引），先查询非聚簇索引B+树，查到了数据（name = 李四，id = 5），此时发现要查询的内容全部命中，那么可以不用去聚簇索引中回表查询；
2.2）需要回表的情况：二级索引没有覆盖
① SQL语句： select * from table where name = 李四；
② 分析： name是二级索引（非聚簇索引），先查询非聚簇索引B+树，查到了数据（name = 李四，id = 5），此时发现要查询的内容没有全部命中，那么就根据 id = 5 去查询聚簇索引B+树，最终得到数据（id = 5，name = 李四，age = 25，sex = 男） ；
3）索引下推
- 索引下推的作用：提高非聚簇索引的查询效率，减少回表次数：
- ① 在MySQL5.6之前，当使用非主键联合索引进行查询的时，存储引擎在非主键索引B+树中查询数据之后，然后将结果返回给MySQL的Server层，在Server层判断是否符合条件（即回表）。
- ② MySQL5.6之后，推出了索引下推技术，判断是否符合条件的这一步是在存储引擎层中实现的；存储引擎会直接根据索引条件过滤掉不符合的结果，然后回表查询得到结果，将结果返回给MySQL server。
例子：(name，level)是联合索引，查询下表
SELECT address FROM user_info WHERE name LIKE "大%" AND level = 1;
1）在MySQL5.6之前，没有索引下推；
| 步骤 | 内容 |
|---|---|
| Step1 | 根据条件【name LIKE “大%”】在【存储引擎层】的联合索引（name,level）的B+树中查到了两条记录（id =1,name = 大彬,level = 1）与 （id = 4,name = 大铁锤,level = 4） |
| Step2 | 将这两条记录返回给MySQL 【Service层】，然后拿着（id = 1）与（id = 4）去主键索引B+树中回表，根据条件【level = 1】进行查询 |
| Step3 | 经过2次回表后，最终得到（weapon = 铁锤 ） |
2）在MySQL5.6之后，开始索引下推技术
| 步骤 | 内容 |
|---|---|
| Step1 | 根据条件【name like ‘陈%’】在【存储引擎层】的联合索引（name,level）的B+树中查到了两条记录（id =1,name = 大彬,level = 1）与 （id = 4,name = 大铁锤,level = 4） |
| Step2 | 然后再根据索引条件【level= 1】过滤得到（id =1,name = 大彬,level = 1） |
| Step3 | 将这1条记录返回给MySQL 【Service层】，然后拿着（id = 1）去主键索引B+树中回表，根据条件【level = 1】进行查询 |
| Step4 | 经过1次回表，得到（weapon = 铁锤 ） |
5、最左前缀原则
最左前缀原则与B+树的匹配原理相关，从建立的联合索引顺序从左到右匹配；
例子：表table建立了联合索引(a,b,c)；
- 1）MySQL的索引顺序必须按照从左往右排序；
1、不会走索引的情况：跳过索引a
select * from table where b = "1";
select * from table where c = "1";
select * from table where b = "1" and c = "2";
2、走索引的情况
1)全部索引都存在，但是乱序。优化器可以优化
select * from table where c = "1" and b = "2" and a = "3";
2) 部分索引顺序是相对从左至右的，也可以走索引
select * from table where a = "1" and c = "3";
select * from table where a = "1" and b = "3";
- 2）MySQL会一直向右匹配直到遇到范围查询就停止匹配索引；
1、不会走索引的情况
1) 遇到范围查询（in、between、>、<）
select * from table where a = "1" and b > "2" and c = "3";// c不走索引
select * from table where a in ("1","2") and b = "2" and c = "3";// b、c都不走索引
- 3）MySQL遇到不符合最左前缀原则的模糊查询，也不会走索引
1、不会走索引的情况
1) %在前 或 %%
select * from table where a like %张 and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描
select * from table where a like %张% and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描
1、走索引的情况
1) %在后
select * from table where a like 张% and b = "1" and c = "2";// a,b,c都走索引；
//a的索引类型为index或者range（由优化器决定）
6、索引失效的几种情况
| 失效情况 | 举例 |
|---|---|
| ① 索引类型不一致 | phone字段类型是varchar，查询用的是int：select * from table where phone = 123； |
| ② where中使用了函数 | select * from table where DATE(create_time) = '2022-01-01' ； |
| ③ 模糊查询%不在后边 | select * from table where name like "%张%" select * from table where name like "%张" |
| ④ or引起索引失效 | select * from table where name = "张三" and age = 10 ； name和age只要有一个没有建索引，那么就都不走索引 |
| ⑤ 不符合最左前缀原则 | 具体见上 |
| ⑥ 索引列参与计算 | select * from table where age - 1 = 10 |
七、MySQL调优，解决慢查询
问到如何调优的万能回答：
| 解决层面 | 方式 |
|---|---|
| 索引层面 | ① 联合索引： 提高检索效率； ② 索引覆盖： 减少回表次数； ③ 索引下堆： 减少回表次数； ④ 索引类型： 当varchar字节小于4时，比int类型效率高； |
| 数据库层面 | ① 主从同步： 读写分离（主写从读）； ② 分库分表： 垂直拆分与水平拆分；提高并发量与磁盘使用率； |
| 缓存层面 | Redis缓存： 但需要考虑数据一致性（延迟双删、redis用消息队列订阅MySQL的bin log日志、读取与更新操作的异步串行化） |
| SQL语法层面 | 通过explain命令查看SQL执行计划， ① 避免索引失效的语法； ② 适当使用子查询，来覆盖索引，减小回表次数； ③ 避免一次性查询超大量数据，尽可能用limit等语法；能不用 * 就不用 *，只查询要查询的字段； |
524
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
