Mysql

一丶mysql存储引擎

1. innoDB

支持事务和行锁

2. myIsam

只能表锁，不支持事务

二丶sql的执行流程

1. 查询的执行流程

mysql查询执行过程.drawio.png

2. mysql写入的执行流程

mysql写入数据.drawio.png

三丶表设计的范式

1.第一范式

每一列数据都是应该是最小单元，不能在进行拆分。

2. 第二范式

一张表的数据，其数据都必须依赖于主键，也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系

3.第三范式

每一列数据不能与主键之外的字段有直接关系

总结：

第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。
第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

4. 反范式设计

在表中增加冗余字段，从而为了方便关联和查询。

四丶mysql索引机制

1.分类

从结构上分：主键索引，唯一索引，普通索引，全文索引，空间索引
从存储上分：聚簇索引和非聚簇索引。

说明：聚簇索引指的索引上不仅存储的有索引编号，还有数据。非聚簇索引存储索引编号和聚簇索引的编号。

2. 索引比对

	主键索引	唯一索引	普通索引
是否主键	√
是否可以为空		√	√
是否可以重复			√

3. 联合索引

使用多个字段创建的索引。

4. 各种索引的问题

主键索引:如果不选用自增的序列作为索引，由于使用的b+tree的数据结构，那么每次插入都需要重新进行位置的调整，造成资源的浪费。
联合索引:联合索引必须满足最左匹配原则才能查询到数据。
唯一索引和普通索引：唯一索引和普通索引查询同一个数据的时候，唯一索引更快，因为唯一索引查询到数据之后就会立即返回，但是普通索引还会继续检索，相反插入的时候正好相反。
hash索引：天生不支持排序问题。

5. 索引的回表、索引覆盖、索引下推

回表：一般出现在非聚簇索引中，通过索引查到聚簇索引的编号，然后在聚簇索引去查询数据叫做回表。
覆盖：联合索引中查询联合索引的字段值
索引下推：其实mysql左右的优化，在联合索引中，先通过第一个索引查询到数据，在用第二个索引来查询。

6.索引失效的情况

查询语句出现or
反向查询，比如not in
联合索引没有满足最左匹配原则
like中查询以%开头

7.MRR(Multi-Range Read)机制

对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

8.mysql索引的底层原理

1）. 索引和数据的存储

myisam:
1. 没有聚簇索引，都是非聚簇索引
2. 底层存储三个文件，一个村防护数据结构，一个存储数据，一个存储索引
innodb:
1. 存在聚簇索引，和非聚簇索引

2）.索引和数据

myIsam:索引指向指针，指针找到数据
innodb:非聚簇索引，存储的是簇聚索引的值，簇聚索引叶节点存索引建，字节点是双向链表，存储数据。

五丶mysql事务

1.事务的ACID

A/Atomicity：原子性，指组成一个事务的一组SQL要么全部执行成功，要么全部执行失败，事务中的一组SQL会被看成一个不可分割的整体。
C/Consistency：一致性，一个事务中的所有操作，要么一起改变数据库中的数据，要么都不改变，对于其他事务而言，数据的变化是一致的。
I/Isolation：隔离性，指多个事务之间都是独立的，相当于每个事务都被装在一个箱子中，每个箱子之间都是隔开的，相互之间并不影响。
D/Durability：持久性，事务一旦被提交，它会保持永久性，所更改的数据都会被写入到磁盘做持久化。

2. 事务的隔离级别

1）脏读：

一个事务读取的时候读取到其他其他事务没有进行提交的数据。

2）不可重复读：

同一个事务在进行连续两次读取的时候，读取到了其他事务提交的事务，导致读取的数据结果不一样。

3）幻读

一个事务在处理数据的时候前后读取数据，发现查询结果和预期不一致。

隔离级别

读未提交：会出现脏读，幻读，不可重复问题

第已提交：幻读，不可重复问题

可重复读：幻读

可序列化：不会出现问题，但是效率降低

3. 事务的实现原理

1）.事务的正常提交

事务正常提交流程.drawio.png

2）事务的回滚

回滚数据提交.drawio.png

*** 关于redo log、 undo log、bin log

redo log :记录的sql的执行记录，持久性
undo log:记录事务回滚执行的语句，原子性
bin log：数据库存储log,mysql宕机的时候，用来做数据恢复
mvcc:事务的隔离性

六丶mysql锁分类

锁的分类.png

七丶MVCC机制详解

1. mvcc基本原理

undo：重要信息

DB_ROW_ID：主键id

DB_Deleted_Bit：删除已标识

DB_TRX_ID：当前数据的事务id

DB_ROLL_PTR：上一个log日志记录信息

read view

creator_trx_id：代表创建当前这个ReadView的事务ID。

trx_ids：表示在生成当前ReadView时，系统内活跃的事务ID列表。

up_limit_id：活跃的事务列表中，最小的事务ID。

low_limit_id：表示在生成当前ReadView时，系统中要给下一个事务分配的ID值。

** 其实就是把数据分为几个阶段。1 已经提交的事务 2 可能提交也可能没有提交的事务 3 一定没有提交的事务 **

2. mvcc原理

读取mysql的数据的时候，创建快照，快照信息
- 判断当前数据的事务id，和创建快照的信息，如果
  
  情况一：当前数据的事务id小于mvcc中最小事务id，就可以看到
  
  情况二：当前数据的事务id大于mvcc中最大事务id，就不能看到
  
  情况三：当前数据的事务在活跃的事务之间
  
  如何是活跃事务：就不能看到
  
  不在活跃事务：可以看到

如果不能看到，就通过undolog 找到上一条数据进行比对，直到找到对应的数据为止。

3.rr和rc如何做到的

rc：读已提交就是每次读取都创建一个快照

rr:可重复读就是在第一次读取的创建了一个快照

mysql学习笔记