面经--mysql--001

一．Mysql 里面为什么用 B+ 树

正文
数据库的索引及其数据是存储在磁盘中的，从磁盘中读取数据的速度会比内存中读取慢很多，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。 如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

树的演化

1. 树：非线性结构，每个节点有唯一的一个父结点和多个子结点（子树），为一对多的关系。
2. 二叉树：每个结点最多有两颗子树，并且子树有左右之分，不能颠倒。
3. 满二叉树：每一层的结点个数都达到了当层能达到的最大结点数。
4. 完全二叉树：除了最下面一层之外，其余层的结点个数都达到了当层能达到的最大结点数，且最下面一层只从左至右连续存在若干结点，而这些连续结点右边的结点全部不存在。
5. 二叉查找树(BST)是一种特殊的二叉树，又称为二叉排序树、二叉搜索树。
6. 平衡二叉树(AVL树)，是一种特殊的二叉排序树，其左右子树都平衡二叉树，且左右子树高度之差不大超过1
7. B树属于多叉树，又名平衡多路查找树，查找路径有多个，一个树节点中包含多条数据，并包含多个指针域。B-树就是B树（别讲什么B减树，‘-’是分隔符）。
8. B+树，也是一种平衡多路查找树，是在B树的基础上发展而来的。

为什么不使用二叉查找树？

二叉查找树可能出现“一条龙”的景象，把二叉树特殊化为一个链表，相当于全表扫描，查找元素发挥不了二叉排序树的优势，只能按照链表的形式查找，高度太高了，查找效率不稳定。

为什么不使用平衡二叉树？

平衡二叉树解决了二叉树高度太高，查找效率不稳定的问题。但是，平衡二叉树的每个节点只存储一个键值和数据，如果数据非常的多（大多数情况下数据是海量的），二叉树的结点将会非常多，高度也会及其高，查找效率降低。

为什么不使用B树？

B树相对于平衡二叉树，优势在于每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子结点，高度就会降低，B树在提高了IO性能的同时并没有解决元素遍历效率低下的问题，在找数据时需要遍历整个B树，为解决这个问题引出了B+树，。

为什么不使用红黑树？
在内存中，红黑树比B树更优，但是涉及到磁盘操作B树就更优了

B+树数据结构

1. B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

2. 因为B+树索引的所有数据均存储在叶子节点，并用链表链接成一个线性表【在数据库设计中B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的】。而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

为什么MySQL选择B+树做索引
1、B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、B+树更便于遍历：由于B+树的数据都存储在叶子结点中，非叶子结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4、B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

Hash索引和B+树区别是什么？你在设计索引是怎么抉择的？

1. B+树可以进行范围查询，Hash索引不能。
2. B+树支持联合索引的最左侧原则，Hash索引不支持。
3. B+树支持order by排序，Hash索引不支持。
4. Hash索引在等值查询上比B+树效率更高。
5. B+树使用like进行模糊查询的时候，like后面（比如%开头）的话可以起到优化的作用，Hash索引根本无法进行模糊查询。

既然增加树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？

这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找。

扩展MongDB

至于MongoDB为什么使用B-树而不是B+树，可以从它的设计角度来考虑，它并不是传统的关系性数据库，而是以Json格式作为存储的nosql，目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，上面所述的优点2需求就没那么强烈了，其次Mysql由于使用B+树，数据都在叶节点上，每次查询都需要访问到叶节点，而MongoDB使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql（但侧面来看Mysql至少平均查询耗时差不多）。

二．事务的隔离级别

引言
数据库事务的隔离级别有4种，由低到高分别为Read uncommitted（读未提交） 、Read committed （读已提交）、Repeatable read （重复读）、Serializable （序列化）。读现象是在多个事务并发执行时，在读取数据方面可能碰到的问题。包括脏读、不可重复读、幻读。

事务并发的几类问题：
脏读：读到了脏数据，即无效数据。
不可重复读：是指在数据库访问中，一个事务内的多次相同查询却返回了不同数据。
幻读：指同一个事务内多次查询返回的结果集不一样，比如增加了行记录。
备注：不可重复读对应的是修改，即update操作。幻读对应的是插入操作。幻读是不可重复读的一种特殊场景。

要想解决脏读、不可重复读、幻读等读现象，那么就需要提高事务的隔离级别。但是随之带来的，隔离级别越高，并发能力越低。所以，需要根据业务去进行衡量，具体场景应该使用哪种隔离级别。

事务的隔离级别：
读未提交:什么都没做，上面的一个问题都没解决；
读提交：只解决了上面的第一个问题；
可重复读：解决了1.2和部分3问题，MySQL 默认采用可重复读隔离级别；
串行化：完全解决上面4个问题。

mysql查看数据库实例默认的全局隔离级别sql
Mysql8以前：SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
Mysql8开始：SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

修改隔离级别命令：
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
建议开发者在修改时，仅修改当前session隔离级别即可。

mysql默认隔离级别
REPEATABLE-READ，可以避免脏读，不可重复读，不可避免幻读

三、mysql死锁介绍以及解决

什么是死锁
死锁是2+个线程在执行过程中， 因争夺资源而造成的相互等待的现象，若无外力作用，它们将无法推进下去。
死锁产生的4个必要条件
互斥条件
指进程对所分配的资源进行排他性使用，即一段时间内某资源只有一个进程占用，其他的进程请求资源只能等待，直至被占有资源的进程得到释放。
请求和保留条件
指进程至少保持占用一个资源，但又提出新的资源请求，而该资源正被其他进程占用，此时请求进程阻塞，但对以获得的其他资源保持不放。
不剥夺条件
指进程已获得的资源，在未使用完之前，不能剥夺，只能使用完时由自己释放。
环路等待条件
值发生死锁时，必然存在一个进程占用资源的环形链，即进程集合（P0，P1，P2, … Pn），P0等待P1资源释放，P1等待P2资源释放，P3等待 … Pn等待P0资源释放。
对应到mysql中存在的互斥锁，和事务对资源使用排他锁占用，并且事务不结束不会释放，事务之间可会出现资源之间的相互占用，相互等待，因此看来，mysql中是会出现死锁的。

死锁导致长时间阻塞的危害
众所周知，数据库的连接资源是很珍贵的，如果一个连接因为事务阻塞长时间不释放，那么后面新的请求要执行的sql也会排队等待，越积越多，最终会拖垮整个应用。一旦你的应用部署在微服务体系中而又没有做熔断处理，由于整个链路被阻断，那么就会引发雪崩效应，导致很严重的生产事故。

Mysql对死锁的检测与处理

1,超时机制
一是超时机制 即两个事务相互等待时，一旦等待时间超过一个阈值，那么超时事务回滚释放资源，另一个事务就能正常执行了。
在InnoDB存储引擎中，，参数innodb lock_wait timeout 用来设置事务超时的时间

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据 FIFO 的顺序选择回滚对象。

但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO
的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。另一方面，事务时间的等待时间过长，造成的阻塞时间过长，很多情况下也无法接受

2,wait-for graph(等待图)
除了超时机制，当前数据库还都普遍采用wait-for graph(等待图)的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。wait-for graph 要求数据库保存以下两种信息:锁的信息链表,事务等待链表

在上述操作中，会话B 中的事务抛出了 1213 这个错误提示，即表示事务发生了死锁。死锁的原因是会话A 和B 的资源在相等待。大多数的死锁InoDB 存储引擎本身可以侦测到，不需要人为进行干预。但是在上面的例子中，在会话 B 中的事务抛出死锁异常后，会话 A 中马上得到了记录为2的这个资源，这其实是因为会话 B 中的事务发生了回滚，否则会话A 中的事务是不可能得到该资源的。InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。因此如果在应用程序中捕获了 1213这个错误，其实并不需要对其进行回滚。
疑问
那么看到这里看起来 mysql对于死锁的处理是挑选一个回滚代价小的事务进行归滚，接触循环等待的条件，对死锁进行解除。
但是那么为什么在业务上还是碰到锁等待的问题导致阻塞的问题？
1，死锁检测被关闭
mysql死锁检测被死锁检测机制被关闭

show VARIABLES like  'innodb_deadlock_detect' -- 查看当前死锁检测是否开启

set global innodb_deadlock_detect = OFF; --设置死锁检测关闭

set global innodb_deadlock_detect = ON; --设置死锁检测开启

可以设置innodb_deadlock_detect=on 来开启死锁检测。死锁检测在发生死锁的时候，能够快速发现并进行处理，回滚并重新启动。但是死锁检测会比较好资源。当每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作可能就是 100 万的量级。虽然最终检测的结果可能没有死锁，但是这期间要消耗大量的 CPU 资源。

死锁检测时默认开启的，如果被关闭，那么会导致碰到事务之间相互等待的死锁问题，就只能等待事务超时后回滚释放资源

死锁产生的案例

1. 案例一：事物之间对资源访问顺序的交替
2. 案例二：并发修改同一记录
3. 案例三：索引不当导致全表扫描
   mysql锁算法最终锁的是索引，如果更新语句条件围台过大，无法确定主键/索引范围，那么把行级锁/间隙锁上升为表级锁，锁的粒度庞大，扫描时间更长，占用资源多，且耗时，会导致死锁的概率大大增加！
4. 案例四：存在耗时事务
   注意耗时事务的存在导致资源长时间得不到释放，会增加死锁的概率
5. 案例五： 网络问题导致的死连接的产生（出现概率很小）

总结
首先在没有特殊要求情况下死锁检测最好不要关闭，死锁检测会对死锁处理，资源释放，避免阻塞，即使产生死锁，对系统的影响也很小。
死锁的预防就是从死锁的四个条件上入手
死锁发生的条件：
1、资源不能共享，需要只能由一个进程或者线程使用
2、请求且保持，已经锁定的资源自给保持着不释放
3、不剥夺，自给申请到的资源不能被别人剥夺
4、循环等待
那么解决总体的思路就是

1. 使用乐观锁mvcc机制，读取数据不上锁，在读情况下共享资源
2. 保证资源的加锁顺序，避免循环等待的产生
3. 减少对资源的占用时间和占用范围，避免长事务，锁粒度变大的情况，可以大大减少死锁产生的概率

四、Mysql锁机制

概述
事务的隔离性（隔离级别）是由锁来保证的。
并发访问数据的情况分为：

1. 读-读，即并发事务相继读取相同的记录，因为没涉及到数据的更改，所以不会有并发安全问题，允许这种情况发生。
2. 写-写，即并发事务对相同记录进行修改，会出现脏写问题，因为任何一种隔离级别都不允许发生脏写，所以多个未提交的事务对同一个记录修改时需要加锁，保证它们是顺序执行的。
3. 写-读或读-写，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读 、不可重复读 、幻读的问题。可以使用两种方式解决(都离不开锁)：
   读写都采用加锁的方式，读写也需要排队执行，性能较差
   写操作加锁，读操作利用MVVC多版本并发控制，读取历史记录，性能更高，涉及到写操作时，必须有锁

锁的分类

五、事务的实现

什么是事务
事务是作为单个逻辑工作单元执行的一系列操作。

MySQL 事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

**持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：
BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

DBMS 采用 日志 来保证事务的 原子性、一致性 和 持久性。Mysql通过预写式日志undo log、redo log，如果整个事务执行的过程系统崩溃或者断电了，在系统重启的时候，恢复机制会将undo log中未提交的事务进行回滚，保证事务的原子性；而redo log中已提交的事务重做，保证事务的持久性。
DBMS 采用 锁机制 来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许 持有锁的事务 能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

六．讲讲 MVCC

多版本并发控制 (MVCC) 通过保持某个时间点的数据快照来实现。也就是说，无论事务执行多长时间，事务中看到的数据都不会受到其他事务的影响。根据事务的开始时间，每个事务可能在同一时间看到同一表的不同数据。
简单来说，多版本并发控制的思想就是保存数据的历史版本，通过数据行的多版本控制来实现数据库的并发控制。通过这种方式，我们可以比较版本号，以确定是否显示数据，并在不锁定事务隔离效果的情况下读取数据。

七．MySQL主从复制？

主从复制是指将 主数据库（Master）中的 DDL 和 DML 操作通过二进制日志传输到 从数据库（Slave） 上，然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
主从复制的作用有：

当主数据库出现问题时，可以切换到从数据库；
可以进行数据库层面的读写分离，实现负载均衡；
可以在从数据库上进行实时数据备份。
基本原理流程：
MySQL 的主从复制是一个 异步 的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。
Master 端：打开二进制日志（binlog ）记录功能 —— 记录下所有改变了数据库数据的语句，放进 Master 的 binlog 中；
Slave 端：开启一个 I/O 线程 —— 负责从 Master上拉取 binlog 内容，放进自己的中继日志（Relay log）中；
Slave 端：SQL 执行线程 —— 读取 Relay log，并顺序执行该日志中的 SQL 事件。

三种主从复制的方式：
1）同步复制：所有从机接收到Binlog,才认为事务提交成功；最安全，但性能差，一般不用
2）异步复制：只要主机事务提交成功，就对客户端返回成功；后台线程异步把Binlog同步给从机，然后从机回放。最快，但可能丢失数据；
3）半同步复制：部分从机接收到Binlog，才对客户端返回成功
一般做法是牺牲一致性换取高可用；数据丢失后，人工修复。
为了解决主从复制延迟太大，切换到从机后数据丢失太多的问题，采用了并行回放策略（1.按数据维度并行——两个事务没有操作交集可以并行；2.按事务提交顺序并行——同时提交成功的事务是可以并行的）

八．Binlog和Redo Log的一致性问题

通过2PC（2阶段提交方案）解决，具体为：
阶段1：事务提交之前，Redo Log,Undo Log已写入，然后Binlog写入内存，等待刷盘；
阶段2：先刷盘Binlog，然后让InnoDB执行Commit。
以Binlog的刷盘判定一个事务是否被成功提交，让Redo Log 向Binlog靠齐。
宕机场景：
1）如果阶段1宕机，Binlog在内存，数据丢失，Redo Log 通过回滚解决；
2）如果阶段2宕机，Binlog写一部分，对Binlog做截断，对Redo Log做回滚；
3）如果阶段2宕机，Binlog已刷盘，但未执行Commit,恢复时通过比对Binlog和Redo Log,Undo Log发起Commit操作。

九．主从同步的延迟原因及解决办法？

架构方面
1） 业务的持久层采用分库架构，mysql 服务能力水平扩展，分散压力
2） 单个库读写分离，一主多从，主写读从，分散压力。这样从库比主库压力高，保护主库
3） 服务在业务和DB之间加入 memcache 和 redis 的cache层，降低读的压力
4） 不同业务的 mysql 放在不同的物理机，降低压力
5） 使用比主库更好的硬件设备，Mqsql 压力小，延迟就减少了

硬件方面
1） 采用好服务器，比如 4u 比 2u 性能明显好，2u 比 1u 性能明显好。
2） 存储用 ssd 或者盘阵或者 san，提升随机写的性能。
3） 主从间保证处在同一个交换机下面，并且是万兆环境。

Mysql 主从同步加速
1） sync_binlog 在 Slave 端设置为 0。
2） log-slave-updates 从服务器从主服务器接受的更新日志不计入二进制日志
3） 直接禁用 Slave 的 binlog
4） Slave 端，如果存储引擎是 innodb，innodb_flush_log_at_trx_commit =2
5） 同步参数调整主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率