一、数据库三大范式
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及实现原理