原创 MYSQL查询和插入数据的流程是怎样的

发布时间:2021-06-24 23:38:21 浏览 255 来源:猿笔记 作者:居然遇见不如同行

    主要用于根据语法规则来验证语句是否正确:一条查询可以有很多种执行方式:执行计划调用查询执行引擎;而查询引擎通过一系列API接口查询到数据:后台线程:首先将磁盘读到的页的数据放在内存池中:而外还有一些日志文件:
    以上大致的介绍一下MySQL的整体架构;其中内存池、文件、后台线程等一些跟细节的东西没有介绍:


    一个查询语句经过哪些步骤

    这次我们从MySQL的整体架构来讲SQL的执行过程,如下图:

    Server层

    \ u 003 cpstyle = " font-size:inherit;颜色:继承;行高:继承;填充:0px边距:1.5em0px">连接器:连接器负责与客户端建立连接、获取权限、维护和管理连接;\ u003Cbr >查询缓存:服务的查询缓存。如果能找到对应的查询,则不需要进行查询分析、优化和执行,直接返回缓存中的结果集;
    解析器:解析器会根据查询语句构造解析树,主要用来根据语法规则验证语句是否正确,比如SQL的关键字是否正确,关键字的顺序是否正确;
    优化器:解析树被转换成查询计划。一般来说,一个查询可以通过多种方式执行,最终返回相同的结果。优化器根据成本找到最佳执行计划。
    执行器:执行计划调用查询执行引擎,查询引擎通过一系列API接口查询数据;

    InnoDB

    \ u 003 cpstyle = " font-size:inherit;颜色:继承;行高:继承;填充:0px边距:1.5em0px">后台线程:负责刷新内存池中的数据,确保缓存池中的内存缓存是最新的数据,将修改后的数据刷新到磁盘文件,确保数据库的异常情况能够恢复正常;\ u003Cbr >内存池:内存池也可以称为缓存池,主要用来弥补磁盘速度慢对数据库的影响。查询时,先将磁盘读取的页面数据放入内存池,下次读取时直接从内存池读取数据。修改数据时,先修改内存池中的数据,再由后台线程按照一定的频率刷新到磁盘。\ u003Cbr >文件:主要指表空间文件,除此之外还有一些日志文件;
    上面简单介绍了MySQL的整体架构,其中包括了内存池、文件、后台线程等一些细节。没有被介绍。稍后,我们将介绍其他时间推出的详细部分。另外附上MySQL5.6整体架构图:\ u 003 ccbr > \ u 003 c/p > \ u 003 cuigurestyle = " font-size。颜色:继承;行高:继承;边距:0px填充:0px"> \ u003Cimgsrc = "

    InnoDB如何保存数据

    这部分内容是建立在上部分的基础上,需要对内存池、文件、后台线程深入到细节去了解组成,接下我们还是分三部分开始讲解:

    文件

    文件分为日志文件和存储文件,分为两部分讲起:

    存储文件

    存储文件也就是表数据的存储,整体的存储结构如下图:

    段(Segment)
    常见的Segment有数据段、索引段、回滚段等,数据段为B+树的叶子节点(Leafnodesegment)、索引段为B+树的非叶子节点(Non-leafnodesegment)。如下图:
    \ u 003 list style = " font-size:inherit;颜色:继承;行高:继承;边距:0px填充:0px边缘-底部:0.5微米;">扩展)\ u003Cbr >面积是线段的基本元素。一个线段由几个区域组成,一个区域是物理上连续分配的空间。每个线段至少有一个区域,创建线段时会创建一个默认区域。如果在存储数据时,一个区域不足以容纳更多的数据,则需要从该部分分配一个新区域来容纳新数据。一个段管理的空间大小是无限的,可以一直扩展,但扩展的最小单位是面积。每个区域大小固定为1MB,区域由页面组成。为了保证区域内页面的连续性,InnoDB通常一次从磁盘申请4-5个区域。当默认页面大小为16KB时,一个区域由64个连续页面组成。

    \ u 003 list style = " font-size:inherit;颜色:继承;行高:继承;边距:0px填充:0px边距-底部:0.5em">页面:\ u003cbr >页面是构成区域的基本单位,也是InnoDB磁盘管理的最小单位。逻辑上(页码从小到大是连续的),物理上也是连续的。当向表中插入数据时,如果已经写入了一页,系统将从当前区域分配一个新的空闲页进行处理。如果当前区域的64页全部被分配,系统将从当前页所在的段分配一个新的区域,然后从这个区域分配一个新的页供使用。

    行(Row):
    InnoDB按照行进行存放数据,每个页存放的数据有硬性规定,最多存放16KB,当数据大于16KB的时候会发生行溢出,会存储到而外的页(UncompressedBLOBPage)当中。

    日志文件

    关于日志文件这里主要介绍三种日志文件,分别为binlog、redolog、redolog:

    binlog

    binlog用于记录数据库执行的写入性操作(不包括查询)信息,以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志,并且由Server层进行记录,使用任何存储引擎的mysql数据库都会记录binlog日志。binlog是通过追加的方式进行写入的,可以通过max_binlog_size参数设置每个binlog文件的大小,当文件大小达到给定值之后,会生成新的文件来保存日志。

    binlog日志格式

    ROW
    基于行的复制,不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了。
    优点:
    不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题;
    缺点:
    因为每行都要记录日志,会照成日志量暴涨;

    STATMENT
    基于SQL语句的复制,每一条会修改数据的sql语句会记录到binlog中。
    优点:
    不需要记录每一行的变化,减少了binlog日志量,节约了IO,从而提高了性能;
    缺点:
    在某些情况下会导致主从数据不一致,比如执行sysdate()等函数的时候。

    MIXED
    基于STATMENT和ROW两种模式的混合复制,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

    使用场景

    binlog的主要使用场景有两个,分别是主从复制和数据恢复;

    主从复制
    在Master端开启binlog,然后将binlog发送到各个Slave端,Slave端重放binlog从而达到主从数据一致。

    数据恢复
    恢复到某一时刻的日志,通过使用mysqlbinlog工具来恢复数据;

    刷盘时机

    对于InnoDB存储引擎而言,只有在事务提交时才会记录biglog,此时记录还在内存中,Mysql通过sync_binlog参数控制biglog的刷盘时机,取值范围是0-N,
    N代表多少条以后开始进行刷盘,当设置为0的时候由系统自行判断何时写入磁盘,当设置为1的时候,相当于每次Commit就进行刷盘一次,但是这个时候要注意与redolog日志可能存在不一致的情况,这个时候需要设置innodb_support_xa参数也为1,这样就能保证两个两份日志是同步的。

    redolog

    redolog包括两部分:redologbuffer和redologfile,redologbuffer是在内存中,redologfile是在磁盘上,当MySQL执行DML语句的时候,首先写入redologbuffer,然后按照一定条件顺序写入redologfile,什么时候会触发buffer内容写入到file当中呢?

    InnoDB后台线程中的主线程,每秒会进行一次将buffer中的数据刷入到磁盘当中;

    通过设置innodb_flush_log_at_trx_commit参数,来控制刷新的时机,当设置为1的时候,事务每次提交都会将logbuffer中的日志写入osbuffer并调用fsync()刷到logfileondisk中。这种方式即使系统崩溃也不会丢失任何数据,但是因为每次提交都写入磁盘,IO的性能较差。当设置为0的时候,事务提交时不会将logbuffer中日志写入到osbuffer,而是每秒写入osbuffer并调用fsync()写入到logfileondisk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的,当系统崩溃,会丢失1秒钟的数据。当设置为2的时候,每次提交都仅写入到osbuffer,然后是每秒调用fsync()将osbuffer中的日志写入到logfileondisk。

    redolog日志格式

    redolog记录数据页的变更,在设计上redolog采用了大小固定,循环写入的方式,当写到结尾时,会回到开头循环写日志,本质上就是一个环状。

    checkpoint表示已经完整刷到磁盘上datapage上的LSN,因此恢复时仅需要恢复从checkpoint开始的日志部分,LSN表示写入日志的字节的总量,例如,当数据库在上一次checkpoint的LSN为10000时宕机,且事务是已经提交过的状态。启动数据库时会检查磁盘中数据页的LSN,如果数据页的LSN小于日志中的LSN,则会从检查点开始恢复。

    在宕机前正处于checkpoint的刷盘过程,且数据页的刷盘进度超过了日志页的刷盘进度。这时候一宕机,数据页中记录的LSN就会大于日志页中的LSN,在重启的恢复过程中会检查到这一情况,这时超出日志进度的部分将不会重做,因为这本身就表示已经做过的事情,无需再重做。

    在恢复的过程中因为redolog记录的是数据页的物理变化,因此恢复的时候速度比逻辑日志(如binlog)要快很多;

    使用的场景

    MySQL用来确保事务的持久性。redolog记录事务执行后的状态,用来恢复未写入datafile的已成功事务更新的数据。防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候,根据redolog进行重做,从而达到事务的持久性这一特性。

    undolog

    undolog记录数据的逻辑变化,用户事务的回滚操作和MVCC,undolog存放在共享表空间中,以段(rollbacksegment)的形式存在。

    undolog日志格式

    逻辑格式的日志,在事务进行回滚的时候,可以将数据从逻辑上恢复至事务之前的状态。

作者信息

发布了 7 篇专栏 · 获得点赞 11 · 获得阅读 676

相关推荐 更多