一文带你复习好Mysql的核心知识点

点击上方蓝字关注我们

SQL优化调优是体现程序员分析归纳能力的有效手段,虽然我们不是DBA,但是编码开发时也会涉及许多对数据库的CRUD需求。

因此,通过理解Mysql数据库的底层原理,对我们的笔试面试,还有提高业务编码水平是有好处的。

Mysql 的底层结构

大体来说,MySQL 可以分为 Server 层 和 存储引擎层两部分:

  • Server 层包括连接器、查询缓存、分析器、优化器、执行器等。

  • 存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。

现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

下面是Mysql的架构图:

数据库的Server 层包括:连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

1、连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接。

数据库长连接:指连接成功后,如果客户端持续有请求,则一直使用同一个连接。

数据库短连接:则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。 

2、查询缓存:MySQL 拿到一个查询请求后,会先到查询缓存。

但是大多数情况下我会建议你不要使用查询缓存,为什么呢?因为查询缓存往往弊大于利(查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了)。

3、分析器:MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。

4、优化器:优化器是在表里面有多个索引的时候,决定使用哪个索引。

5、执行器:通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句

数据库的存储引擎层:负责数据的存储和提取,其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。

Mysql 日志系统&两阶段提交

RedoLog 和 BinLog 区别

1、 redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。

2、 redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”

3、redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

mysql的两阶段提交原理

阶段1:InnoDB redo log 写盘(引擎层),InnoDB 事务进入 prepare 状态;

阶段2:如果前面prepare成功,binlog 写盘(Server层),那么再继续将事务日志持久化到binlog,如果持久化成功,那么 InnoDB 事务 则进入 commit 状态(实际是在redo log里面写上一个commit记录);

好处:

两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案,这个方案也同时解决磁盘IO的性能。

Mysql 的事务

数据库的事务:就是要保证一组数据库操作,要么全部成功,要么全部失败。

数据库执行引擎中的 InnoDB 支持事务,MySQL 原生的 MyISAM 引擎就不支持事务,这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务隔离特性ACID:表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)。

多事务同时执行引发的问题:脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)。

事务隔离级别:读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable )。

事务隔离真理:隔离级别越高,数据库效率越低。很多时候要在二者之间寻找一个平衡(跟代码加了重量级锁同理:利用 synchronized 锁住整个方法,效率比锁住方法的某个代码块是要低很多的)。

对于事务最重要的便是隔离级别了,但每种隔离级别都不是十全十美的,相对应的会引发匹配的问题,下面逐一解释:

读未提交

读未提交是指:一个事务还没提交时,它做的变更就能被别的事务看到。(隔离性差,会出现脏读情况)

问题:产生了“脏读”数据,另一个未提交的事务,影响了数据居然也被读取到了。解决办法是设置隔离级别为“读提交”。

读提交

读提交是指:一个别的事务提交之后,它做的变更才会被别的事务看到。(oracle默认,隔离性还行,会出现不可重复读情况)

好处:解决了“脏读”问题。

问题:产生了“不可重复读”问题。

但正因为可以“及时的”读取到别的事务提交结果,出现了一个事务范围内同一个事务,但两个相同的查询却返回了不同数据,这就是不可重复读。

解决办法就是设置隔离级别为“可重复读”解决不可重复读就需要锁行了。

可重复读

可重复读是指,一个当前事务执行过程中看到的数据,总是跟这个当前事务在启动时看到的数据是一致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。(Mysql默认)

好处:解决了“不可重复读”问题。


问题:产生了“幻读”问题。

可重复读,就是在开始读取数据(事务开启)时,update操作可以被隔离,但是insert/delete操作除外,这会出现一个新问题(幻读)。解决幻读就需要锁表了。

如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。

但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会 发现莫名其妙多了一条之前没有的数据。

串行化

串行化:顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。

好处:解决了“幻读”问题,因为可重复读是行级锁,而Serializable是表级锁,把整张表锁住了。

问题:性能大打折扣,不推荐。

总结:存在即合理,每种隔离级别都有自己的使用场景,你要根据自己的业务情况来定。

Mysql 的锁

数据库锁出现的原因是为了处理并发问题,因为数据库是一个多用户共享的资源,当出现并发的时候,就会导致出现各种各样奇怪的问题,就像程序代码一样,出现多线程并发的时候,如果不做特殊控制的话,就会出现意外的事情,比如“脏“数据、修改丢失等问题。

从程序员的角度,锁可以分为:“乐观锁” 和 “悲观锁”。

乐观锁:程序员设计表结构加字段&更新操作进行判断是否字段数值一直。

  1. 加上version的版本控制(后续进行更新操作,先比较version是否一致,不一致就需要重新取一遍数据)

  2. 也可以使用timestamp的控制(此处的时间戳应该使用数据库的而非业务系统的)。

悲观锁:(由系统提供的)表锁、行锁、间隙锁等。

  1. 表锁(Table Lock):对整个表进行加锁。

  2. 行锁(Record Lock):对索引记录加锁。

  3. 间隙锁(Gap Lock):锁住整个区间,包括:区间里具体的索引记录,不存在的空闲空间(可以是两个索引记录之间,也可能是第一个索引记录之前或最后一个索引记录之后的空间)。

  4. next-key锁:行锁和间隙锁组合起来。

针对悲观锁,其实日常的很多sql,我们已经不知不觉的使用到了:

实例1:行锁

对主键或者唯一索引进行增删改或显示的加锁,InnoDB会加行锁。

实例2:间隙锁

间隙锁的唯一目的就是阻止其他事务插入到间隙中。

一个表有id为1,2,3,5,6,9行数据,执行如下sql语句:

InnoDB不仅会锁住id为5和6两行的数据,也会锁住id为4(虽然该行并不存在)的纪录。

间隙锁的目的是为了防止幻读,其主要通过两个方面实现这个目的: 

(1)防止间隙内有新数据被插入 

(2)防止已存在的数据,更新成间隙内的数据

实例3:next-key lock

next-key lock=行锁+间隙锁;如果一个事务在记录R上的某个索引有共享/互斥锁,也会对其前面一个范围加锁。

锁定的区域

根据索引会形成一个个左开右闭的一个区间,根据查询的条件其所在的区间,并且包括其后的区间。

参考文章:

https://blog.csdn.net/wanger61/article/details/107817158

Mysql 的索引模型与应用(InnoDB)

索引目的:

    为了提高数据查询效率(减少磁盘查询次数)。

索引模型:

在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。

Mysql的InnoDB使用的是B+ 树索引结构(当然也提供了Hash索引结构,但是使用的非常非常少)。

Hash索引 与 B+索引:

哈希索引的示意图

Hash索引的不足:

不支持联合索引,不支持排序和分组,不支持范围查询,甚至大规模数据情况下,会带来哈希冲突问题。

B+树索引数据结构见下图:

简要概述:

  • B+树-采用平衡树结构,每个叶子节点到根的路径长度都相同

  • B+树-叶子节点按键值大小顺序,放在同一层的叶子节点上

聚簇索引和非聚簇索引:

主键索引:主键索引树,叶子结点包含整条数据内容。

非主键索引:非主键索引树,叶子结点仅包含数据的ID内容,因此需要回表获取更全面的数据(查询多一次主键树)。

下面总结一些索引使用的法则。

不推荐使用索引的场景:

  1. 表记录太少;

  2. 数据重复且分布平均的字段(只有很少数据值字段或者枚举字段);

  3. 经常插入、删除、修改的表要减少索引;

  4. text,image等类型不应该建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引);

  5. MySQL能估计出全表扫描比使用索引更快时,不使用索引;

联合索引优于单索引的场景:

  1. 由于MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”,倒不如说,和全表扫描比起来,去分析两个索引B+树更加耗费时间。

  2. 所以where A=a and B=b这种查询使用(A,B)的组合索引最佳,B+树根据(A,B)来排序。

推荐使用索引的场景:

  1. 主键,unique字段;

  2. 联表的字段需要加索引;

  3. 在where里使用>,≥,=,<,≤,is null和between等字段;

  4. 使用不以通配符开始的like,where A like 'China%';

  5. 聚集函数MIN(),MAX()中的字段;

  6. order by和group by字段;

索引失效场景:

  1. 组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引;

  2. like未使用最左前缀,where A like '%China';

  3. 搜索一个索引而在另一个索引上做order by,where A=a order by B,(一个失败一个成功)只使用A上的索引,因为查询只使用一个索引 ;

  4. or 可能使第一个索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效)。index merge 技术可以优化这个问题。

  5. 如果列类型是字符串,要使用引号。例如where A='China',否则索引失效(会进行类型转换),见下图:

    第1条sql 走索引,第2条sql则是全表扫描,非常慢。

  6. 在索引列上进行计算、函数、手动或自动的类型转换会导致索引失效;见下图:

索引的五个设计原则:

  1. 第一个索引原则:尽量使用主键索引原则。(主键索引是聚簇索引,叶子结点保留完整数据;非聚簇索引需要回表,多查一次B+树)

  2. 第二个索引原则:控制查询字段(ID替代通配符*),覆盖索引的手段,能够大幅度提升性能。(只查询ID不用回表)

  3. 第三个索引原则:最左前缀原则。(索引复用,索引项是按照索引定义里面出现的字段顺序排序的)

  4. 第四个索引原则:联合索引,索引顺序往往就是需要优先考虑采用的。

  5. 第五个索引原则:索引下推原则。(在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数)

总结

我们总结一下,上文主要归纳总结了Mysql的主要核心概念:日志系统&隔离级别&索引模型&索引使用经验等。如果想学习的更加深入,推荐一门很好的课程给大家:《MySQL精讲45讲》,丁奇大大的优秀课程。

参考文章:

https://blog.csdn.net/weixin_39420024/article/details/80040549

https://www.cnblogs.com/zhangtianle/p/7474554.html

《MySQL精讲45讲》

往期推荐

《源码系列》

JDK之Object 类

JDK之BigDecimal 类

JDK之String 类

JDK之Lambda表达式

Spring源码:Event事件发布与监听

《经典书籍》

Java并发编程实战:第1章 多线程安全性与风险

Java并发编程实战:第2章 影响线程安全性的原子性和加锁机制

Java并发编程实战:第3章 助于线程安全的三剑客:final & volatile & 线程封闭

《服务端技术栈》

《Docker 核心设计理念

《Kafka史上最强原理总结》

《HTTP的前世今生》

《算法系列》

读懂排序算法(一):冒泡&直接插入&选择比较

《读懂排序算法(二):希尔排序算法》

《读懂排序算法(三):堆排序算法》

《读懂排序算法(四):归并算法》

《读懂排序算法(五):快速排序算法》

《读懂排序算法(六):二分查找算法》

《设计模式》

设计模式之六大设计原则

设计模式之创建型(1):单例模式

设计模式之创建型(2):工厂方法模式

设计模式之创建型(3):原型模式

设计模式之创建型(4):建造者模式

设计模式之创建型(5):抽象工厂设计模式

设计模式之结构型(1):代理类设计模式

扫描二维码

获取技术干货

后台技术汇

点个“在看”表示朕

已阅

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值