【学习日记二------MYSQL】

一、数据库三大范式

1、列不可分割
2、在第一范式基础上，非主键列必须完全依赖于主键，不能只依赖主键的一部分。
3、在第二范式基础上，非主键列必须只依赖于主键，不能依赖于其它非主键。

二、一条sql执行流程

通过上面的图可知道，一条sql执行流程大概分为三部分：客户端，server服务端，存储引擎。
server端主要是由：连接器，查询缓存，分析器，优化器，执行器组成，主要了解一下server端。

1、连接器：
客户端想要对数据库进行操作，必须要与数据库进行连接操作，连接器就是负责跟客户端进行连接、获取权限、保持连接、管理连接的。

（1）连接方式：
包括长连接和短连接，短连接就是执行完请求就立马关闭掉，长连接就是一直保持着这个连接不关闭，减少服务端创建和关闭连接带来的消耗。

（2）连接池：
假如我们的服务是部署在Tomcat容器中的，Tomcat是可以并发处理多个请求的（Tomcat主要面对的是各种网络io，所以重写了java原生线程池，尽可能多的使用核心线程，当达到核心线程数时，但是还未到达最大线程数，就接着创建，直到到最大线程数就放入到队列中），会频繁的创建销毁连接，消耗系统的性能，所以采用数据库连接池的形式，来管理连接，常用的连接池有Druid，c3p0，dbcp等，一般连接池中创建的都是长连接。

2、查询缓存
mysq默认是关闭查询缓存的，也就是说不推荐使用查询缓存，mysql8.0甚至直接将查询缓存模块删除掉了。

（1）为什么不推荐使用查询缓存

因为查询场景限制导致：
①mysql查询缓存数据结构：key（sql语句）—value（数据值），所以只要sql语句有一点不一样，就会去查询数据库，还多了一步查询缓存操作，浪费性能。

②对于同一个key（sql语句）来说，value（数据值）一般不是一成不变的，所以当数据库的数据发生了变化，那么对应的value也需要跟着变化，还要进行额外的维护，浪费性能。

3、分析/解析器：做什么what
主要工作内容就是将sql语句进行解析，得到抽象语法树，再使用预处理器判断是否存在这张表，再判断投影列字段是否存在于这张表。

4、查询优化器：怎么做how

（1）逻辑转换：将常量表达式等计算完。
（2）代价优化：判断这条sql是否可以走索引，走哪些索引。
explain可以查看执行计划。

5、执行器：开始做just do it
判断是否有执行权限，无权限直接返回无权限异常，有权限按照生成的执行计划继续执行。

三、mysql存储引擎

1、索引：

1.1、聚集索引和非聚集索引

1.1.1
聚集索引：InnoDB存储引擎使用的，也就是聚簇索引，将数据和索引放在了一起，叶子结点中保存的是主键索引+完成的列信息+行数据，默认使用的是主键，如果没有主键就会默认找一个唯一且不为空的索引，如果不存在这样的索引，那么就会隐式地定义一个主键来做聚集索引。如下图。

1.1.2
聚集索引相关注意事项：
1）聚簇索引具有唯一性，所以一张表只能有一个聚簇索引。

2）同时主键不应该过大，如果过大会导致其它索引也很大。

3）并且主键最好不要用uuid，因为uuid不具有顺序性，离散型过大可能会导致新出现的主键，插入到两个主键中间，这样数会来回扩散，耗费性能。

4）主键最好是自增id，这样只需要一页一页的写，结构紧凑，磁盘碎片也就少，效率高。

5）聚簇索引的辅助索引结构是普通索引+主键，都是需要回表再次进行查询，来实现。
例子：user表结构：id为主键，name创建普通索引，age无索引。
sql：

	① select * from user where id= 7;（根据主键索引直接可以取到数据，不需要回表）
	
	② select * from user where name = jobs;（先根据辅助键索引找到主键11，然后回表，根据主键11查到所有信息）
	
	③ select id ,name from user where name = jobs;（发生索引覆盖，不需要回表）
	
	④ select id ,name from user where name like '%jo' and age = 10;(索引下推，不需要回表。)

1.1.3
非聚集索引：MyISAM存储引擎使用的，也就是非聚簇索引，将数据和索引隔离开来，叶子结点中保存的是指向行数据的指针。如下图

1.1.4 innoDB和MyISAM区别：

1）innoDB支持事务，MyISAM不支持。
2）innoDB支持行锁和MVCC，MyISAM不支持。
3）innoDB使用聚簇索引，MyISAM使用非聚簇索引。

1.1.5 如何选择innoDB和MyISAM：

1）MyISAM有行的数量，如果系统是CMS运营后台管理这种读多写少的场景可以选MyISAM。如果是读写频繁选择innoDB。
2）如果想有事务管理机制，选择innoDB。
3）系统崩溃后MyISAM恢复起来困难，innoDB有undo log、redo log帮助恢复。
4）innoDB在mysql5.5之后是默认的存储引擎。

2、事务

2.1、binlog、undolog、redolog作用

undolog（撤销日志）：保存数据修改前的数据，当把数据读取到内存时，这些数据就是脏数据，需要都记录到undolog里面，用于回滚操作。

redolog（重置日志）：保存数据修改后的内容，作用于缓冲池，数据修改后生成redolog，需要把这些内存中的日志刷新到磁盘内，数据库宕机时，这些redolog就是重要的记录，重启之后会把redolog的数据写入发哦磁盘中。

binlog（操作日志）：二进制文件，保存在磁盘中，binlog是mysql的service层的，用于记录操作记录，变更历史，mysql的复制（主从同步）。

假设有A、B两条数据，值分别为1、2，开启一个事务，事务的操作内容为把1改成3，2改成4，那么过程如下：
A.事务开始.
B.记录A=1到undo log.
C.修改A=3.
D.记录A=3到redo log.

E.记录B=2到undo log.
F.修改B=4.
G.记录B=4到redo log.
H.将redo log写入磁盘。

I.事务提交

2.2、事务四大特性

A（原子性）：一个事务内多次操作要么全部成功，要么全部失败。
实现原理：将修改的多条记录所在的数据页，从磁盘加载到内存缓冲池，然后修改数据，有undolog备份修改前的数据用于回滚，修改后的数据redolog一起提交，从而保证了原子性。
C（ 一致性）:依赖于其它三个特性。
I（隔离性）:多个事务进行操作时，事物内部操作不能影响其他事务，互不干扰。
MVCC多版本并发控制器（多个事务来修改同一条数据，通过mvcc版本号来控制，不是简单的加锁控制，提高了效率）
D（ 持久性）:持久性就是事务一旦提交了，就会改变数据，数据库故障也不会改变。
实现原理：我们在同一个事务中修改了多条数据，不会一次一次的进行提交，而是先把修改的数据记录到redolog。

1：首先执行器调用引擎获取数据，如果数据在内存中就直接返回；否则先从磁盘中读取数据，写入内存后再返回。
2：修改数据后再调用引擎接口写入这行数据
3：引擎层将这行数据更新到内存中，然后将更新操作写入redo log，这时候redo log标记prepare状态。然后告诉执行器我处理完了，可以提交事务了。
4：执行器生成这个操作的binlog，并把binlog写入磁盘，然后调用引擎提交事务
引擎收到commit命令后，把刚才写入的redo log改成commit状态
5：至此我们的一条更新语句就算基本完成了，这里面涉及了两阶段提交prepare阶段和commit阶段。

2.3 两段式提交

·时刻A发生故障，redolog写完，binlog没有写，回滚。
·时刻B发生故障，redolog和binlog都写完，查看redolog和binlog的数据一致性，不一致回滚。

参考资料：
Mysql第三章：存储引擎（MyISAM和Innodb）
Mysql第五章：mysql进阶(数据引擎索引的数据结构)
关于B树 B+树 B*树以及红黑树的理解
InnoDB中数据是如何存储的
Mysql 中 MyISAM 和 InnoDB 的区别有哪些？
聚簇索引和非聚簇索引的关系
GoGoCoder底层原理分析–聚集索引和辅助索引查询过程
索引知识系列一：聚集索引与非聚集索引详解
MySQL之InnoDB存储引擎
一条查询SQL的执行流程
头条二面：详述一条 SQL 的执行过程
MySQL事务四大特性以及如何保证
【MySQL笔记】正确的理解MySQL的MVCC及实现原理