后端程序员必备——Mysql数据库相关流程图,原理图

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 后端程序员必备——Mysql数据库相关流程图,原理图

前言

整理了一些Mysql数据库相关流程图/原理图,做一下笔记,大家一起学习。

1.mysql主从复制原理图

mysql主从复制原理是大厂后端的高频面试题,了解mysql主从复制原理非常有必要。

主从复制原理,简言之,就三步曲,如下:

  • 主数据库有个bin-log二进制文件,纪录了所有增删改Sql语句。(binlog线程)
  • 从数据库把主数据库的bin-log文件的sql语句复制过来。(io线程)
  • 从数据库的relay-log重做日志文件中再执行一次这些sql语句。(Sql执行线程)
  • 如下图所示:

    后端程序员必备:Mysql数据库相关流程图/原理图

    上图主从复制分了五个步骤进行:

    步骤一:主库的更新事件(update、insert、delete)被写到binlog

    步骤二:从库发起连接,连接到主库。

    步骤三:此时主库创建一个binlog dump thread,把binlog的内容发送到从库。

    步骤四:从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log

    步骤五:还会创建一个SQL线程,从relay log里面读取内容,从ExecMasterLog_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db

    2.Mysql逻辑架构图

    如果能在脑海中构建出MySql各组件之间如何协同工作的架构图,就会有助于深入理解MySql服务器后端程序员必备:Mysql数据库相关流程图/原理图

    Mysql逻辑架构图主要分三层:

    1) 第一层负责连接处理,授权认证,安全等等

  • 每个客户端连接都会在服务器进程中拥有一个线程,服务器维护了一个线程池,因此不需要为每一个新建的连接创建或者销毁线程。
  • 当客户端连接到Mysql服务器时,服务器对其进行认证,通过用户名和密码认证,也可以通过SSL证书进行认证。
  • 一旦客户端连接成功,服务器会继续验证客户端是否具有执行某个特定查询的权限。
  • 2)第二层负责编译并优化SQL

  • 这一层包括查询解析,分析,优化,缓存以及所有的的内置函数。
  • 对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。
  • 所有跨存储引擎的功能都在这一层实现:存储过程,触发器,视图。
  • 3)第三层是存储引擎。

  • 存储引擎负责在MySQL中存储数据、提取数据。
  • 存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。
  • 存储引擎不会去解析SQL,不同存储引擎之间也不会相互通信,而只是简单地响应上层服务器的请求。
  • 3.InnoDb 逻辑存储结构图

    从InnoDb 存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称之为表空间(tablespace)。表空间又由段(segment),区(extent),页(page)组成。页在一些文档中有时候也称为块(block)。InnoDb 逻辑存储结构图如下:后端程序员必备:Mysql数据库相关流程图/原理图

    表空间(tablespace)

  • 表空间是Innodb存储引擎逻辑的最高层,**所有的数据都存放在表空间中**。
  • 默认情况下,Innodb存储引擎有一个**共享表空间ibdata1**,即所有数据都存放在这个表空间中内。
  • 如果启用了**innodbfileper_table**参数,需要注意的是每张表的表空间内存放的**只是数据、索引、和插入缓冲Bitmap**,其他类的数据,比如**回滚(undo)信息、插入缓冲检索页、系统事物信息,二次写缓冲等**还是放在原来的共享表内的。
  • 段(segment)

  • 表空间由段组成,常见的段有**数据段、索引段、回滚段**等。
  • InnoDB存储引擎表是索引组织的,因此数据即索引,索引即数据。**数据段即为B+树的叶子结点,索引段即为B+树的非索引结点**。
  • 在InnoDB存储引擎中对段的管理都是由**引擎自身所完成**,DBA不能也没必要对其进行控制。
  • 区(extent)

  • 区是由**连续页**组成的空间,在任何情况下每个区的大小都为**1MB**。
  • 为了**保证区中页的连续性**,InnoDB存储引擎**一次从磁盘申请4~5个区**。
  • 默认情况下,InnoDB存储引擎页的大小为**16KB**,一个区中一共**64个连续**的区。
  • 页(page)

  • **页是InnoDB磁盘管理的最小单位**。
  • 在InnoDB存储引擎中,默认每个页的大小为**16KB**。
  • 从InnoDB1.2.x版本开始,可以通过参数**innodbpagesize**将页的大小设置为4K,8K,16K。
  • InnoDB存储引擎中,常见的页类型有:**数据页,undo页,系统页,事务数据页,插入缓冲位图页,插入缓冲空闲列表页**等。
  • 4.Innodb页结构相关示意图

    Innodb页结构单体图

    InnoDB数据页由以下7部分组成,如图所示:

    后端程序员必备:Mysql数据库相关流程图/原理图

    其中File Header、Page Header、File Trailer的大小是固定的,分别为38,56,8字节,这些空间用来标记该页的一些信息,如Checksum,数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际的行记录存储空间,因此大小是动态的。

    下边我们用表格的方式来大致描述一下这7个部分:

    后端程序员必备:Mysql数据库相关流程图/原理图

    记录在页中的存储流程图

    每当我们插入一条记录,都会从Free Space部分,也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分,当Free Space部分的空间全部被User Records部分替代掉之后,也就意味着这个页使用完了,如果还有新的记录插入的话,就需要去申请新的页了,这个过程的图示如下:

    后端程序员必备:Mysql数据库相关流程图/原理图

    不同Innodb页构成的数据结构图

    一张表中可以有成千上万条记录,一个页只有16KB,所以可能需要好多页来存放数据。不同页其实构成了一条双向链表,File Header是InnoDB页的第一部分,它的FILPAGEPREV和FILPAGENEXT就分别代表本页的上一个和下一个页的页号,即链表的上一个以及下一个节点指针。

    后端程序员必备:Mysql数据库相关流程图/原理图

    5.Innodb索引结构图

    我们先看一份数据表样本,假设Col1是主键,如下:

    后端程序员必备:Mysql数据库相关流程图/原理图

    B+树聚集索引结构图

    后端程序员必备:Mysql数据库相关流程图/原理图
  • 聚集索引就是以主键创建的索引
  • 聚集索引在叶子节点存储的是表中的数据
  • 非聚集索引结构图

    假设索引列为Col3,索引结构图如下:后端程序员必备:Mysql数据库相关流程图/原理图

  • 非聚集索引就是以非主键创建的索引
  • 非聚集索引在叶子节点存储的是主键和索引列
  • 使用非聚集索引查询出数据时,拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做**回表**)
  • 假设所查询的列,刚好都是索引对应的列,不用再**回表**查,那么这个索引列,就叫**覆盖索引**。
  • InnoDB 锁类型思维导图

    后端程序员必备:Mysql数据库相关流程图/原理图

    加锁机制

    乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题。

    乐观锁

  • 每次去取数据,都很乐观,觉得不会出现并发问题。
  • 因此,访问、处理数据每次都不上锁。
  • 但是在更新的时候,再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。
  • 悲观锁

  • 每次去取数据,很悲观,都觉得会被别人修改,会有并发问题。
  • 因此,访问、处理数据前就加**排他锁**。
  • 在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁.
  • 锁粒度

  • **表锁:** 开销小,加锁快;锁定力度大,发生锁冲突概率高,并发度最低;不会出现死锁。
  • **行锁:** 开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高。
  • **页锁:** 开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般
  • 兼容性

    共享锁:

  • 又称读锁(S锁)。
  • 一个事务获取了共享锁,其他事务可以获取共享锁,不能获取排他锁,其他事务可以进行读操作,不能进行写操作。
  • SELECT ... LOCK IN SHARE MODE 显示加共享锁。
  • 排他锁:

  • 又称写锁(X锁)。
  • 如果事务T对数据A加上排他锁后,则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。
  • SELECT ... FOR UPDATE 显示添加排他锁。
  • 锁模式

  • **记录锁:** 在行相应的索引记录上的锁,锁定一个行记录
  • **gap锁:** 是在索引记录间歇上的锁,锁定一个区间
  • **next-key锁:** 是记录锁和在此索引记录之前的gap上的锁的结合,锁定行记录+区间。
  • **意向锁** 是为了支持多种粒度锁同时存在;
  • 参考与感谢

  • 《MySQL技术内幕》
  • 《高性能MySql》
  • MySQL InnoDB锁机制全面解析分享
  • 数据库两大神器【索引和锁】
  • InnoDB的逻辑存储结构学习
  • MySQL索引背后的数据结构及算法原理
  • 原文始发于微信公众号(捡田螺的小男孩):

    本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

    转载声明:转载请注明出处,本技术博客是本人原创文章

    本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

    原文链接:blog.ouyangsihai.cn >> 后端程序员必备——Mysql数据库相关流程图,原理图


     上一篇
    MySQL 快速创建千万级测试数据 MySQL 快速创建千万级测试数据
    点击上方“后端技术精选”,选择“置顶公众号” 技术文章第一时间送达! 作者:IKNOW本尊 juejin.im/post/5ce372c36fb9a07ef63fb191 juejin.im/post/5ce372c36fb
    下一篇 
    InnoDB一棵B+树可以存放多少行数据? InnoDB一棵B+树可以存放多少行数据?
    点击上方“后端技术精选”,选择“置顶公众号” 技术文章第一时间送达! 作者:飘扬的红领巾 cnblogs.com/leefreeman/p/8315844.html cnblogs.com/leefreeman/p/8315