前言

传送门(含目录)
计网分层:https://blog.csdn.net/qq_45021207/article/details/112387871
计网应用层:https://blog.csdn.net/qq_45021207/article/details/112723944
计网传输层:https://blog.csdn.net/qq_45021207/article/details/113184737
计网网络层&数据链路层 :https://blog.csdn.net/qq_45021207/article/details/113248814
数据库:https://blog.csdn.net/qq_45021207/article/details/113427419

自查自问

1. 事务的ACID特性,如何实现,几个特性之间的关系
2. 几种并发不一致
3. 隔离级别 不同隔离级别的并发不一致
4. mysql的逻辑架构 一条语句的执行路径
5. explain的字段
6. mysql调优  索引的创建使用
7. InnoDB的行锁
8. 意向锁
9. MVCC 原理 解决了什么问题
10. SQL 与 NoSQL 的比较
11. mysql 连接
12. 索引和B+树  原理优势
13. 红黑树 概念
14. 存储引擎的比较
15. 主从复制,读写分离 过程
16. 分表 水平切分垂直切分
17. mysql用的什么协议 有状态还是无状态
18. innoDB的架构
19. join 的底层原理
20. innoDB索引计算

ACID 事务

事务指的是满足ACID特性的一组操作

原子性: undolog(记录上一个一致性状态) 回滚至上一个一致性状态

持久性 : Redolog 可以重做恢复

并发不一致

产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性

丢失修改
T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
一级封锁协议
事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

读脏数据
T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
二级封锁协议
在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。
可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据

不可重复读
T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
三级封锁协议
在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。 可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

幻影读
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次 读取的结果不同。

隔离级别

横着读
Mysql默认的事务隔离级别是可重复读

mysql 逻辑架构

explain

type 访问类型 all range const

mysql调优

排除缓存干扰

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况，因为存在缓存，我发现我sql怎么执行都是很快，当然

第一次其实不快但是我没注意到，以至于上线后因为缓存经常失效，导致rt（Response time）时高时低
。
后面就发现了是缓存的问题，我们在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。
select count() from users where email = 'hello';
select SQL_NO_CACHE count() from users where email = 'hello';

我说一下为什么缓存会失效，而且是经常失效。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大

家也看到我们的结构图了，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之

前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

覆盖索引

上面我提到了，可能需要回表这样的操作，那我们怎么能做到不回表呢？在自己的索引上就查到自己想要的，不要去主键索引查了。
覆盖索引
如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品id，id一般都是主键，可能sql类似这样：

联合索引

还是商品表举例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？
大家可以思考上面的回表的消耗对SQL进行优化。

是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。
刚才我举的例子其实有点生硬，正常通过商品名称去查询库存的请求是不多的，但是也不代表没有哈，真来了，难道我们去全表扫描？

最左匹配原则

大家在写sql的时候，最好能利用到现有的SQL最大化利用，像上面的场景，如果利用一个模糊查询 itemname like ’敖丙%‘，这样还是能利用到这个索引的，而且如果有这样的联合索引，大家也没必要去新建一个商品名称单独的索引了。
很多时候我们索引可能没建对，那你调整一下顺序，可能就可以优化到整个SQL了。
MySQL 建立联合索引的规则是这样的，它会首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

创建和使用索引

like是以%开头的查询语句 会使索引失效

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，查询中才会使用索引。否则，查询将不使用索引。

对索引进行计算 会使索引失效

唯一索引普通索引选择难题

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。
除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率
那么，什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。
要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。
change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

InnoDB行锁

1，Record Lock(行锁)：单个行记录上的锁。
2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
Record Locks
锁定一个记录上的索引，而不是记录本身。
如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用(锁表了)。
Gap Locks
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks
Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 的方式虽然能解决快照读的不可重复与幻读问题，但不能解决当前读的。
当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。 索引唯一该值只能是一个所以不可能再插入一个同样的数据,

锁住10-20 是因为再插入15的时候 会插入这个范围

意向锁

解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗。
锁在加锁前要先加意向锁。意向锁是一种表锁。

https://blog.csdn.net/a1102325298/article/details/86586629

事务 A 锁住了表中的一行，让这一行只能读，不能写。
之后，事务 B 申请整个表的写锁。
如果事务 B 申请成功，那么理论上它就能修改表中的任意一行，这与 A 持有的行锁是冲突的。

MVCC

RR模式MYSQL 中的SELECT 操作只读取某一时间点的数据，即transaction开始时刻的数据，尽管后续有transaction 对数据做出了更改，当前transaction 也看不到。这有点类似于 MYSQL 在transaction开始的时刻打了一个快照。所以这种读叫快照读。它可重复但不是实时的。

MYSQL 中还有另外一种读叫当前读（CURRENT READ）. 这种读只读取表中当前最新的已提交的数据，可以理解为是一种READ COMMITTED。

MVCC 的方式虽然能解决快照读的不可重复与幻读问题，但不能解决当前读的。

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外保存两个隐藏的列：当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列称为回滚指针，用于事务回滚，不在本文范畴）。这里的版本号并不是实际的时间值，而是系统版本号。每开始新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。
每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

MVCC下InnoDB的增删查改是怎么work的

1.插入数据（insert）:记录的版本号即当前事务的版本号
执行一条数据语句：insert into testmvcc values(1,"test");
假设事务id为1，那么插入后的数据行如下：

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。
比如，针对上面那行记录，事务Id为2 要把name字段更新
update table set name= 'new_value' where id=1;

3、删除操作的时候，就把事务版本号作为删除版本号。比如
delete from table where id=1;

4、查询操作：
从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1) 删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。(即上述事务id为2的事务查询时，依然能读取到事务id为3所删除的数据行)

2) 创建版本号 小于或者等于当前事务版本号 ，就是说记录创建是在当前事务中（等于的情况）或者在当前事务启动之前的其他事物进行的insert。
（即事务id为2的事务只能读取到create version<=2的已提交的事务的数据集）

SQL 与 NoSQL 的比较

mysql 连接

INNER JOIN(JOIN)

LEFT JOIN

RIGHT JOIN

索引和B+树

innodb非主键索引 里面装的非主键索引的值 和对应的主键索引

不用 hash是因为:无法范围查找,数据量大的时候会有hash冲突

自增主键

红黑树

红黑树定义和性质
红黑树是一种含有红黑结点并能自平衡的二叉查找树。它必须满足下面性质：
*
性质1：每个节点要么是黑色，要么是红色。
*
性质2：根节点是黑色。
*
性质3：每个叶子节点（NIL）是黑色。
*
性质4：每个红色结点的两个子结点一定都是黑色。
*
性质5：任意一结点到每个叶子结点的路径都包含数量相同的黑结点。

插入节点默认是红色

调整策略: 变色,自旋

这里“平衡”的意思并不是说两个子树的叶子节点个数精确一样，而是说两个子树的高度不会差太多（对于AVL树，任何一个节点的两个子树高度差不会超过1；对于红黑树，则是不会相差两倍以上）

红黑树的颜色是保证红黑树查找速度的一种方式，从任意的节点开始到叶节点的路径，黑节点的个数是相同的，这就能保证搜索路径的最大长度不超过搜索路径的最短长度的2倍

存储引擎

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

   MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

主从复制,读写分离

binlog 记录了 所有更改的内容 不包括 select

水平切分垂直切分

通常情况下，我们使用取模的方式来进行表的拆分;比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4
通过用ID取模的方法把数据分散到四张表内Id%4+1 = [1,2,3,4]
然后查询,更新,删除也是通过取模的方法来查询。

例：QQ的登录表。假设QQ的用户有100亿，如果只有一张表，每个用户登录的时候数据库都要从这100亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1亿条，就小了很多，比如qq0,qq1,qq1…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

另外部分业务逻辑也可以通过地区，年份等字段来进行归档拆分;进行拆分后的表，只能满足部分查询的高效查询需求，这时我们就要在产品策划上，从界面上约束用户查询行为。比如我们是按年来进行归档拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询;在做分析或者统计时，由于是自己人的需求,多点等待其实是没关系的,并且并发很低,这个时候可以用union把所有表都组合成一张视图来进行查询,然后再进行查询。

mysql协议状态

mysql用的TCP链接

举个例子我和朋友出去吃饭 不需要每次报上姓名 联系方式 等 朋友就知道我是谁 这是有状态的

而我去办事大厅 工作人员不会记得我是谁 每次去都要填表 出示身份证 这就是无状态的

无状态协议：在下一次链接不记住这一次链接的信息。

HTTP,UDP都是无状态协议

TCP,FTP是有状态协议 (三次握手)

IP是无状态的，它只负责将一个IP包发送到指定的IP地址上去。它不会考虑这个包与前面已经发送的包和后面的包的联系。（可能是重发包、可能是不连续包，它不管）

TCP是有状态的，它通过包头中的一些控制字段（序列编码等）来表明各个包之间的关系（前后关系，重包与否等等）。所以，通过这个协议你可以做到一个可靠的传输。那么TCP是面向连接的协议是什么意思呢？其实这里的面向连接其实就是“三次握手”。三次握手，首先可以保证对方的存在，其次握手的所交换的内容是为将来进行有状态的传输做准备。
UDP是无状态的，它仅仅是在IP上加了Port，其他的事情什么也不干。这样它不可能做到可靠的传输，同样也不需要连接。

innoDB架构

下图简单描述了InnoDB存储引擎的体系结构：

InnoDB存储引擎有多个内存块，这些内存块组成了一个大的内存池。后台线程主要负责刷新内存池中的数据、将已修改的数据刷新到磁盘等等。

InnoDB 存储引擎是基于磁盘存储的,也就是说数据都是存储在磁盘上的,由于 CPU 速度和磁盘速度之间的鸿沟, InnoDB 引擎使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘。
缓冲池中缓存的数据页类型有:索引页、数据页、 undo 页、插入缓冲、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等。索引页和数据页占缓冲池的很大一部分（知道有这些页，把这些页当做名词即可，不用感到迷惑）。在InnoDB中，缓冲池中的页大小默认为16KB。

我们已经知道这个Buffer Pool其实是一片连续的内存空间，那现在就面临这个问题了：怎么将磁盘上的页缓存到内存中的Buffer Pool中呢？直接把需要缓存的页向Buffer Pool里一个一个往里怼么？不不不，为了更好的管理这些被缓存的页，InnoDB为每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址，一些锁信息以及LSN信息（锁和LSN这里可以先忽略），当然还有一些别的控制信息。

为了知道哪些节点是空闲的,我们把所有空闲的页包装成一个节点组成一个链表，这个链表也可以被称作Free链表（或者说空闲链表）。

从图中可以看出，我们为了管理好这个Free链表，特意为这个链表定义了一个控制信息，里边儿包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。我们在每个Free链表的节点中都记录了某个缓存页控制块的地址，而每个缓存页控制块都记录着对应的缓存页地址，所以相当于每个Free链表节点都对应一个空闲的缓存页。

有了这个Free链表事儿就好办了，每当需要从磁盘中加载一个页到Buffer Pool中时，就从Free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的Free链表节点从链表中移除，表示该缓存页已经被使用了～

下面再来简单地回顾Buffer Pool的工作机制。Buffer Pool两个最主要的功能：一个是加速读，一个是加速写。加速读呢？ 就是当需要访问一个数据页面的时候，如果这个页面已经在缓存池中，那么就不再需要访问磁盘，直接从缓冲池中就能获取这个页面的内容。加速写呢？就是当需要修改一个页面的时候，先将这个页面在缓冲池中进行修改，记下相关的重做日志，这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘，这个是后台刷新线程来完成的。

我们设立Buffer Pool的初衷，我们就是想减少和磁盘的I/O交互，最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页，那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率，我们的期望就是让缓存命中率越高越好～

怎么提高缓存命中率呢？InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰，以提高缓存命中率。

当我们需要访问某个页时，可以这样处理LRU链表：
*
如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页包装成节点塞到链表的头部。
*
如果该页在Buffer Pool中，则直接把该页对应的LRU链表节点移动到链表的头部。

但是这样做会有一些性能上的问题，比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了，就会导致导致缓冲池污染问题！Buffer Pool中的所有数据页都被换了一次血，其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作，而这种全表扫描的语句执行的频率也不高，每次执行都要把Buffer Pool中的缓存页换一次血，这严重的影响到其他查询对 Buffer Pool 的使用，严重的降低了缓存命中率 ！
所以InnoDB存储引擎对传统的LRU算法做了一些优化，在InnoDB中加入了midpoint。新读到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。

前面我们讲到页面更新是在缓存池中先进行的，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（英文名：dirty page）。

我们需要创建一个存储脏页的链表，凡是在LRU链表中被修改过的页都需要加入这个链表中，因为这个链表中的页都是需要被刷新到磁盘上的，所以也叫FLUSH链表，有时候也会被简写为FLU链表。链表的构造和Free链表差不多，这就不赘述了。这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改，只有第一次被修改时才需要加入FLUSH链表（代码中是根据Page头部的oldest_modification == 0来判断是否是第一次修改），如果这个页被再次修改就不会再放到FLUSH链表了，因为已经存在。需要注意的是，脏页数据实际还在LRU链表中，而FLUSH链表中的脏页记录只是通过指针指向LRU链表中的脏页。并且在FLUSH链表中的脏页是根据oldest_lsn（这个值表示这个页第一次被更改时的lsn号，对应值oldest_modification，每个页头部记录）进行排序刷新到磁盘的，值越小表示要最先被刷新，避免数据不一致。

join 底层原理

需要join的列有索引

innoDB索引计算

1-3层，约 2 千万行数据。

一页默认 16k
除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3 的页

我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，(page number=3)能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170。

单个叶子节点页（page number=4,5,6,7）中的记录数 =16K/1K=16。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。

那么可以算出一棵高度为 2 的 B+ 树，能存放 117016=18720 条这样的数据记录。
根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 11701170*16=21902400 条这样的记录
在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

自查自问

1. 事务的ACID特性,如何实现,几个特性之间的关系
2. 几种并发不一致
3. 隔离级别 不同隔离级别的并发不一致
4. mysql的逻辑架构 一条语句的执行路径
5. explain的字段
6. mysql调优  索引的创建使用
7. InnoDB的行锁
8. 意向锁
9. MVCC 原理 解决了什么问题
10. SQL 与 NoSQL 的比较
11. mysql 连接
12. 索引和B+树  原理优势
13. 红黑树 概念
14. 存储引擎的比较
15. 主从复制,读写分离 过程
16. 分表 水平切分垂直切分
17. mysql用的什么协议 有状态还是无状态
18. innoDB的架构
19. join 的底层原理
20. innoDB索引计算