Mysql知识（我是小白）

前言：

好久没有写了，突然想写写东西，空背八股文，不懂原理，背完也是忘，所以结合一些题来逐步理解Mysql中的知识，我尽量用一些直白的语言来解释。知识点有误，请看到的人指点。

一.一条sql是如何执行的呢？

首先我们要知道mysql服务器有哪几主要部分组成呢？

Connections/Thread handling：处理连接的线程

Query Cache：查询缓存

Parser：解析器

Optimizer：优化器

Storage Engines：存储引擎 

一条SQL命令从客户端发送过来之后，创建一条线程，走到缓存区查找是否有缓存信息，有则返回，没有的话会，走到解析器，解析该条语句是否正确，有误则返回报错，正确则走到优化器，优化SQL语句（优化器分为动态优化与静态优化），之后执行该条SQL，到存储引擎（Innodb/MYISAM等）中，返回结果给客户端，同时讲述数据保存到缓存区中。

以上就是我对上题的回答，扩展下缓存区mysql，其实在5.7之后就没有缓存区的概念了，为什么呢？

因为需要缓存中的缓存需要和请求来的命令保持完全一致，大小写也需要一致，所以命中率较低，功能比较鸡肋。

扩展一下存储引擎，MYISAM是5.5之前的默认存储引擎，InnoDB是之后默认的存储引擎，区别在于MYISAM不支持事务，InnoDB支持。

二.操作系统写流程

为什么要说这个东东呢，只有知道IO写流程，我们才会理解底层为什么这么设计，原因其实都是根据减少写流程的耗时时间。

操作系统体系结构：

1.应用程序也可以指用户态，用户态其实就是指我们用户可以操作的位置

2.系统调用其实就是用户态的用户操作通过系统调用中的命令去指挥内核态进行操作，进而将我们需要保存的数据，保存到磁盘中。

三.CPU的上下文切换

CPU会将在用户态命令进行到的位置，记录下来，之后到内核态的新位置进行执行，当内核态命令执行完毕后，CPU会重新切换到用户态中刚才记录的位置上，其中CPU进行了两次上下文切换，然而上下文切换是耗时并且占用性能，所以我们所有的程序在设计时，都应该考虑下CPU的上下文切换，用最少的切换干最多的活。

当然mysql也是这么干的。

四.操作系统的写流程：

简要概述上图，其实内部非常复杂，大家可以简要了解下。

写流程：首先我们用户态中的数据会保存到用户态的缓存区中，在将缓存区中的数据flush到内核态中，内核态会将接受到的数据，保存到内核态的缓存区中，也就是我们常说的page cache，然后操作系统将通过fsync命令将缓存区中的数据写到磁盘缓存区，最后磁盘会将缓存区中的数据落盘。

其中我们需要知道的是，当我们的数据保存在用户态的缓存区中时，如果我们的服务器电脑断电后，用户态的数据就会丢失，然而内核态的数据不会，但是内核态的数据也不是最安全的，也有可能丢失，只有落在磁盘中的数据我们才会认为是安全的，但是也要时常进行备份数据，磁盘也不是万无一失的。

经过前面这么多的铺垫就是为了方便我们理解mysql问什么这么设计，其实就是因为上述的问题，在保证效率和数据不丢失的情况下进行设计,我们说下mysql我们会遇到的底层知识。

五.一条SQL在存储引擎中发生了什么？（InnoDB为例）

1.当一条SQL执行时，存储引擎会首先检查下BuffPool中的数据是否存在。

2.如果存在就会将缓存区中的数据返回给我们的客户端。

3.如果不存在的话，就会将在磁盘中的数据，从而将新数据写入到BuffPool中，同时如果是更新命令会将旧值写入到undo log日志中，用于回滚数据。

4.更新命令将SQL写入到redo log日志中，用来保持一致性，redo log日志会将数据写入到缓存log  Buff中，再写入到redo log日志中去，由redo log日志写入到磁盘中，同时将数据写入到bin log日志中，主要用于主从复制数据。

六.mysql中的二次提交（也是上述第4点中发生的）

首先将数据写入到redo log中并标记为Prepare状态，再将数据写入到bin log中，这是会将redo log中的状态标记为commit状态。这是正常流程的二次提交

但是当在二次提交的过程中如果我们的服务器在中间过程中挂了怎么办？

在redo log第一次提交过程中，标记为Prepare状态的情况下挂了，分为两种情况，这时redo log日志会通过事务ID去 bin log中去查找这条数据，如果存在，继续将redo log中的状态标记为commit状态，如果不存在，通过redo log日志进行回滚。

七.mysql中的主从复制（bin log）

首先主节点将数据写入到bin log日志中，然后从节点将bin log从主节点上拉取下来。从节点将数据写入到relay log中，然后执行relay log中的SQL语句从而实现主从复制。

八.索引结构B+树和B树索引

B+树索引只有叶子节点保存着键值和对应的数据，B树每个节点都保存着键值和数据

B+是有序链表，设用于范围查询，查询时只需要遍历叶子节点即可

B+设用于范围查询，B树设用于随机查询

InnoDB存储引擎的索引结构就是用的B+树的数据结构

九.回表的概念

当我们基于非主键索引进行查询时，当找到对应的B+中的键值时，数据保存的是主键时，这时将会发生回表，通过主键去B+树中查询索引对应的具体数据，这个过程就叫做回表。

十.InnoDB没有主键索引怎么办？

mysql会以我们设置的主键为主键索引，我们没有的话默认会将只有唯一值的列作为索引，也没有的情况下，mysql会默认使用隐藏字段trx_id最为主键索引。

十一.聚簇索引

聚簇索引：将数据存储与主键放到一起，主键索引就是聚簇索引

非聚簇索引：将数据和索引分开存储，叶子节点的指向数据对应的位置

InnoDB是有主键索引的，MyIsam是没有主键索引的

十二.事务的ACID

1.原子性：一个事务要么全部提交成功，要么全部提交失败 --undo log

2.一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行前后数据库都必须保持一致性 -- redo log

3.隔离性：在并发环境中，一个事务的执行不能被其他事务干扰 --MVCC机制

4.持久性：一旦事务提交，那么对数据库中对应的数据状态变更会永久保存在数据库中 --redo log

十三.mysql中脏读 幻读 不可重复读

1.脏读：一个事务读取到另一个事务未提交的数据

2.不可重复读：一个事务先后读取同一个数据，两次数据不同

3.幻读：一个事务按照条件查询数据时，没有查询到对应的数据行，但在插入数据时又发现这行数据已经存在

上述问题可以通过不同的隔离级别进行控制，读未提交级别什么都解决不了，读已提交解决脏读，可重复读解决脏读和不可重复读，序列化解决脏读，不可重复读，幻读。

InnoDB默认的隔离级别为可重复读

十四.如何实现事务的隔离性（MVCC）

1.隐藏字段

最近修改的事务ID: DB_TRX_ID

隐藏主键：DB_ROW_ID

回滚指针：DB_ROLL_PTR 记录上一个版本

2.undo log

版本链，不同事物或相同事务对同一个数据进行修改会导致记录的undo log生成一条记录版本链，头部为最新记录，尾部为最早记录。

3.read view

快照读：记录可见的版本

把不同隔离级别下生成read view的时机也不一致

读已提交--每次select就会生成一个

可重复读--第一次select时会生成一个

当前读：记录最新的版本，读取时其他并发事务不能修改当前记录

核心字段：

m_ids 当前活跃的事务ID集合（未commit的事务）

min_trx_id 最小活跃事务ID

max_trx_id 预分配事务ID，最大事务ID+1

creat_trx_id read view创建者id

trx_id（当前事务）可访问的事务ID

规则如下：

trx_id == creat_trx_id 成立，说明数据是当前这个事务更改的

trx_id == min_trx_id 成立，说明数据已提交

trx_id > max_trx_id 成立，说明事务实在Read view之后生成的

min_trx_id <= trx_id <=max_trx_id 并且trx_id不在m_ids中 说明数据已提交

MVCC(并发版本控制)，通过以上技术实现了隔离性

十五.三大范式

第一范式：每个列不可差分 例如广东省深圳市，就可以拆分成广东省和深圳市两个字段进行存储

第二范式：要有主键

第三范式：只保存一个关联表的主键id减少冗余

平时开发中我们也会使用反范式进行设计（违反第三范式）

以空间换时间，读多写少的情况下，在显示商品信息时，我们可以在表中同时存储着类别信息而不是类别信息的主键，减少关联表。

保存历史快照信息，如订单表，保存收货信息，不通过ID关联查询。