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