精华内容
下载资源
问答
  • mysql架构与sql执行流程
    千次阅读
    2022-03-23 23:10:22

    MySQL 8.0 SQL 执行流程

    首先我们先来看下 MySQL 的经典架构图,8.0 的没怎么翻到,先看看这个了。

    1.png

    图上有这么几个模块:

    Collectos

    连接器,客户端可以通过这些方式对 MySQL 服务端发起通信。

    Services & utilities

    Connection pool

    连接池,负责连接的处理、安全和认证,比如我们的请求线程的新建、密码认证、权限获取都是由这个模块负责。这里缓存了权限,所以当你不断开连接,其他用户修改权限后,当前的连接的权限也不会改变。

    SQL Interface

    MySQL 的接口

    Parser

    语法解析器,将我们的 SQL 语句解析成相关的语法树。解析器先提取相应的关键词,然后进行语法解析,判断 SQL 是否符合相应的语法,然后生成语法树,上面拆分了 SQL 的各种成分,比如查询字段、查询表、匹配条件等。

    Optimzer

    优化器,将 SQL 进行优化生成多个执行计划。

    执行器

    上面优化器生成了多份执行计划后,接下来就由执行器选择一份计划执行了。执行器先会判断当前是否具有权限,然后才会去执行相应的 SQL 语句。

    Caches

    缓存命中,8.0 中已经被干掉了。作用是在每次查询时将结果缓存,然后再次查询可以加快访问速度,但是因为命中率太低,有点鸡肋所以在 MySQL 8.0已经不存在了。比如他是将 SQL 语句作为 key 进行命中匹配的,如果 SQL 中多加了一个空格也会被认为不是同一条 SQL 导致匹配不到。

    Pluggable storage Engines

    数据库的执行引擎插件。

    文件系统

    这个是存放 MySQL 的文件系统。

    SQL 执行流程

    SQL 流程是 SQL --> 解析器 --> 优化器 --> 执行器 --> 返回结果。

    下面会将各个组件单独拉出来做分析。

    更多内容请关注作者公众号或者官方网站

    更多相关内容
  • 理解一条sql究竟都走了哪些流程
  • 【课堂笔记】MySQL架构与SQL执行流程.pdf
  • MySQL架构与SQL执行流程

    千次阅读 2019-05-31 09:13:06
    MySQL架构设计 下面是一张MySQL的架构图: 上方各个组件的含义如下: Connectors 指的是不同语言中与SQL的交互 Management Serveices & Utilities: 系统管理和控制工具 Connection Pool:连接池 管理...

    MySQL架构设计

    下面是一张MySQL的架构图:

    1

    上方各个组件的含义如下:

    Connectors

    指的是不同语言中与SQL的交互

    Management Serveices & Utilities:

    系统管理和控制工具

    Connection Pool:连接池

    管理缓冲用户连接,线程处理等需要缓存的需求。
    负责监听对 MySQL Server 的各种请求,接收连接请求,转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配(或创建)一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信,
    接受客户端的命令请求,传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建,线程的 cache 等

    SQL Interface:SQL接口

    接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface

    Parser:命令解析器

    SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的,是一个很长的脚本。
    在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ,在 MySQL Server 里面,连接线程接收到客户端的一个 Query 后,会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
    主要功能:
    a . 将SQL语句进行语义和语法的分析,分解成数据结构,然后按照不同的操作类型进行分类,然后做出针对性的转发到后续步骤,以后SQL语句的传递和处理就是基于这个结构的。
    b. 如果在分解构成中遇到错误,那么就说明这个sql语句是不合理的

    Optimizer: 查询优化器

    SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query(sql语句) ,根据客户端请求的 query 语句,和数据库中的一些统计信息,在一系列算法的基础上进行分析,得出一个最优的策略,告诉后面的程序如何取得这个 query 语句的结果
    他使用的是“选取-投影-联接”策略进行查询。
    用一个例子就可以理解:

    1
    
    select uid,name from user where gender = 1;
    

    这个select 查询先根据where 语句进行选取,而不是先将表全部查询出来以后再进行gender过滤
    这个select查询先根据uid和name进行属性投影,而不是将属性全部取出以后再进行过滤
    将这两个查询条件联接起来生成最终查询结果

    Cache和Buffer: 查询缓存。

    他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中,与该 query 的一个 hash 值 做
    一个对应。该 Query 所取数据的基表发生任何数据的变化之后, MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中, Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的
    如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等
    存储引擎接口
    存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中,基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类,但正是因为它成功地将各种数据处理高度抽象化,才成就了今天 MySQL 可插拔存储引擎的特色。
    注意:存储引擎是基于表的,而不是数据库。

    SQL语句执行流程

    连接

    • 客户端发来一条SQL语句,监听客户端的‘连接管理模块’接收请求
    • 将请求转发到‘连接进/线程模块’
    • 调用‘用户模块’来进行授权检查
    • 通过检查后,‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接,如果失败则创建一个新的连接请求

    处理

    • 如果是查询语句则先查询缓存,检查语句是否完全匹配,接着再检查是否具有权限,都成功则直接取数据返回
    • 上一步有失败则转交给‘命令解析器’,经过词法分析,语法分析后生成解析树
    • 接下来是预处理阶段,处理解析器无法解决的语义,检查权限等,生成新的解析树
    • 再转交给对应的模块处理
    • 如果是查询还会经由‘查询优化器’做大量的优化,生成执行计划
    • 模块收到请求后,通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
    • 有则调用‘表管理模块’,先是查看table cache中是否存在,有则直接对应的表和获取锁,否则重新打开表文件
    • 根据表的meta数据,获取表的存储引擎类型等信息,通过接口调用对应的存储引擎处理
    • 上述过程中产生数据变化的时候,若打开日志功能,则会记录到相应二进制日志文件中

    结果

    • SQL执行完成后,将结果集返回给‘连接进/线程模块’
    • 返回的也可以是相应的状态标识,如成功或失败等
    • 连接进/线程模块’进行后续的清理工作,并继续等待请求或断开与客户端的连接

     

    推荐阅读

     

    1. SpringCloud学习系列汇总
    2. 为什么一线大厂面试必问redis,有啥好问的?
    3. 多线程面试必备基础知识汇总
    4. Java集合源码分析汇总-JDK1.8
    5. Linux常用命令速查-汇总篇
    6. JVM系列文章汇总

     

    博客所有文章首发于公众号《Java学习录》转载请保留
    扫码关注公众号即可领取2000GJava学习资源

     

    1

    展开全文
  • 课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解,MySQL事务原理事务并发,MySQL性能优化总结MySQL配置优化。课程安排的学习的教程对应的学习课件,详细的学习笔以及课程...
  • Msql系列文章第一篇,一条SQL执行过程解析

    从这一章开始,将通过一个系列完整的介绍研发人员需要知道的MySQL知识。
    先通过整体流程图从全局上了解一条SQL语句在MySQL中的执行过程,建立整体概念,帮助你从高维度理解问题。

    大致了解相关知识点即可,通过后续的文章将会逐步讲解各个环节。

    范围

    本系列文章知识主要基于MySQL 5.7 版本 InnoDb引擎

    更新语句的整体流程图

    在这里插入图片描述

    下面依次介绍下每个步骤的作用

    Server 层

    1. 连接器

    1. 校验用户名密码

    2. 从权限表获取用户拥有的权限并设置

      之后这个连接里面的权限判断逻辑,都依赖此时读到的权限,即时用户权限在此期间发生变化,也只有再新建连接的时候才会生效

    3. 建立连接(半双工通信,边查询边发送), 管理连接
      在这里插入图片描述

      连接成功后,如果客户端没有后续动作,这个连接就处于空闲状态,show processlist可以看到Comman列为Sleep。
      客户端如果长时间不发送Command到Server端,Server端会主动断开连接,超时时间由参数wait_timeout和interactive_timeout控制,默认为8小时。
      在这里插入图片描述
      在这里插入图片描述

    4. 关于长连接与短连接

      长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

      由于建立连接过程比较复杂、耗时,建议使用长链接,减少建立连接的次数。

      但由于MySQL在执行过程中临时使用的内存是管理在连接对象中,这些资源会在连接断开时才释放。如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了,解决该问题的方案如下:

      1. 定期断开长连接或者程序里面判断执行过一个占用内存的大查询后,断开连接,后续查询再重连。
      2. MySQL 5.7及以上版本,可以通过执行mysql_reset_connection来重新初始化连接资源,这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

    2. 查询缓存

    当为SELECT语句时,之前执行过的语句及结果以key-value对的形式被直接缓存到内存中,以SQL为KEY,查询结果为value。

    1. 如果命中,先做权限验证,然后返回结果。
    2. 如果未命中则执行后面的步骤,执行完成后将结果写入查询缓存。

    大多数情况下,查询缓存都较为鸡肋
    因为只要对表执行一次更新,不管更新了多少数据,这个表上所有的查询缓存都会被清空。所以对于更新压力大的数据库来说,查询缓存的命中率会非常低。费劲存起来的缓存可能还没怎么使用,就被一个更新全清空了。

    # 查询缓存相关的status变量
    mysql>SHOW GLOBAL STATUS LIKE 'QCache\_%';
    +-------------------------+----------+
    | Variable_name           | Value    |
    +-------------------------+----------+
    | Qcache_free_blocks      | 1        |  --查询缓存中可用内存块的数目。
    | Qcache_free_memory      | 33268592 |  --查询缓存的可用内存量。
    | Qcache_hits             | 121      |  --从QC中获取结果集的次数。
    | Qcache_inserts          | 91       |  --将查询结果集添加到QC的次数,意味着查询已经不在QC中。
    | Qcache_lowmem_prunes    | 0        |  --由于内存不足而从查询缓存中删除的查询数。
    | Qcache_not_cached       | 0        |  --未缓存的查询数目。
    | Qcache_queries_in_cache | 106      |  --在查询缓存中注册的查询数。
    | Qcache_total_blocks     | 256      |  --查询缓存中的块总数。
    
    # 查询缓存命中率 ≈ Qcache_hits / (Qcache_hits + Qcache_hits + Qcache_not_cached) * 100%
    

    查询缓存QC的大小只有几MB,不适合将缓存设置得太大,由于在更新过程中需要线程锁定QueryCache,因此对于非常大的缓存,可能会看到锁争用问题。

    但存在即合理,可以将my.cnf参数query_cache_type设置为2(DEMAND)用户自定义模式,在特殊场景下查询时加上 SQL_CACHE 关键字来使用查询缓存,其他的则默认不使用,以下场景适用查询缓存:

    1. 相同的查询是由相同或多个客户机重复发出的。
    2. 被访问的底层数据本质上是静态或半静态的(极少更新)。
    3. 查询有可能是资源密集型和/或构建简短但计算复杂的结果集,同时结果集比较小。
    4. 并发性和查询QPS都不高。

    实际业务中大多数时候很难满足上述条件,通常是配置表,数据字典表等静态表才适合,不过此情况也可以考虑通过配置管理系统或者缓存中间件来实现。

    # 例如对城市表使用SQL_CACHE关键字来命中查询缓存
    select SQL_CACHE * from citys
    

    查询缓存在MySQL 5.6(默认禁用), 5.7(废弃), 8.0(移除)

    最后注意:在线上判断SQL执行效率时,最好加上 SQL_NO_CACHE 显示指定不使用查询缓存,才能拿到真实的执行时间。

    select SQL_NO_CACHE id,username from userinfo where username='Alice' 
    

    3. 分析器

    到这步就说明要开始真正的执行语句了,因此需要对SQL语句做解析,包括词法分析,语法分析,步骤如下:

    1. 词法分析: 由MysqlSQLLex完成
    2. 语法分析: 由Bison生成(参考https://en.wikipedia.org/wiki/LR_parser)
    3. 语义分析
    4. 构造执行树

    4. 优化器

    优化器主要是计算各种执行方式的成本,确定最终的执行方案,可能包含以下方案一种会多种:

    1. 重写查询,优化查询条件顺序
    2. 决定表的读取顺序
    3. 计算使用各索引以及全表扫描的成本进行比较
    4. 计算各种表连接顺序的成本
    5. 生成选择索引后的最终执行计划

    优化器不关心表使用什么存储引擎,但是存储引擎对于优化器是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销以及表的统计信息。可以通过关键字提示(hint)或 force index来影响优化器的决策过程。

    5. 执行器

    1. 校验表的查询权限

      思考:为什么不在优化器或分析器阶段校验权限呢?因为SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器,得在执行器阶段(过程中)才能确定。之前的阶段是无能为力的。

    2. 调用存储引擎接口完成查询

    在慢查询日中有一个rows_examined字段,表示这个语句执行过程中扫描了多少行。这个值就是执行器每次调用引擎获取数据行的时候累计的。

    有些场景下,执行器调用一次,在引擎内部扫描了多行,因此引擎扫描行数跟 rows_examined 并不是完全相同的。

    引擎层

    6. 加载数据页到Buffer Pool

    1. 这里会先判断Buffer Pool里是否有该数据, 若没有则从磁盘读取
    2. MySQL是页为单位从磁盘读取数据, 一次I/O可以读取多页

    7. 写入Undo Log(回滚日志)

    1. 记录更新前的旧值。
    2. 如果事务提交失败要回滚数据,可以用undo日志里的数据恢复Buffer Pool里的缓存数据

    8. 修改Buffer Pool

    1. 将SQL变更的结果更新到Buffer Pool。
    2. 根据不同的事务隔离级别结合数据的版本号,客户端可能可以读到不同版本的数据,具体逻辑需要理解 MVCC(多版本并发控制)。
    3. 此时磁盘上的数据还没有发生变化,依然是更新前的值

    9. 写Redo Log(重做日志)

    1. 记录Redo Log
    2. RedoLog 打上 prepare 状态

    关于记录Redo Log:

    1. 这里 redo log是先写入 Redo log buffer。

    2. 然后以一定的策略将Buffer中的数据刷到磁盘

      InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件 的大小是 1GB,从头开始写,写到末尾就 又回到开头循环写。
      在这里插入图片描述
      write pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件 开头。checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录 更新到数据文件。

      write pos 和 checkpoint 之间的是“粉板”上还空着的部分,可以用来记录新的操作。如 果 write pos 追上 checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下 来先擦掉一些记录,把 checkpoint 推进一下。

      如果服务宕机,使用RedoLog恢复Buffer Pool,避免丢失数据(关于具体在各种情况下如何恢复数据,由于是面向后端研发人员,此处没有展开说明)

    控制Redo Log刷盘策略的参数叫 innodb_flush_log_at_trx_commit:

    1. innodb_flush_log_at_trx_commit = 0

      Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件,同时还会通知文件系统进行文件同步的flush操作,保证数据确实已经写入到磁盘上面的物理文件。但是,每次事务的结束(commit 或者是rollback)并不会触发Log Thread 将log buffer 中的数据写入文件。
      所以,当设置为0 的时候,当MySQL Crash 和OS Crash或者主机断电之后,最极端的情况是丢失1 秒时间的数据变更。

    2. innodb_flush_log_at_trx_commit = 1

      这也是Innodb的默认设置。我们每次事务的结束都会触发Log Thread将log buffer中的数据写入文件并通知文件系统同步文件。
      这个设置是最安全的设置,能够保证不论是MySQL Crash 还是OS Crash或者是主机断电都不会丢失任何已经提交的数据。

    3. innodb_flush_log_at_trx_commit = 2

      当我们设置为2 的时候,Log Thread 会在我们每次事务结束的时候将数据写入事务日志,但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的,所以Log Thread的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。
      所以,当设置为2 的时候,MySQL Crash 并不会造成数据的丢失,但是OS Crash或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样,大家可以自行参阅相关的手册。

      建议设置为1,这样Mysql不会丢失数据,损失一点性能,保证数据安全。

    10. 写入Bin Log日志

    1. 执行器写入binlog并执行fsync(刷盘)
    2. BinLog的写入由Server完成,所有引擎均有,上述的undo, redo为InnoDb特有

    BinLog是Mysql Server层实现的二进制日志,有以下三种格式:

    statement(记录会修改数据的原始SQL语句)

    • 优点:
    1. binlog文件较小
    2. 日志是包含用户执行的原始SQL,方便统计和审计
    • 缺点:
    1. 存在安全隐患,可能导致主从不一致
    2. 对一些系统函数不能准确复制或是不能复制

    row(记录被修改的记录值)

    记录行的内容,记两条,更新 前和更新后都有。

    • 优点:
    1. 相比statement更加安全的复制格式
    2. 在某些情况下复制速度更快(SQL复杂,表有主键)
    3. 系统的特殊函数也可以复制
    4. 更少的锁
    5. 在复制时,对于更新和删除语句检查是否有主键,如果有则直接执行,如果没有,看是否有二级索引,如再没有,则全表扫描
    • 缺点:
    1. binlog比较大(myql5.6支持binlog_row_image)
    2. 单语句更新(删除)表的行数过多,会形成大量binlog
    3. 无法从binlog看见用户执行SQL

    mixed(是以上两种level的混合使用)

    是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog,如一些函数,statement无法完成主从复制的操作,则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。新版本的MySQL中队row level模式也被做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句,还是会记录所有行的变更。

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

    BinLog同样有控制刷盘时机的参数 sync_binlog,可配置值说明如下:

    1. sync_binlog = 0

      表示当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

    2. sync_binlog = 1

      表示每次事务的 binlog 都持久化到磁盘

    3. sync_binlog = n

      表示当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

      建议设置为1,保证 MySQL 异常重启之后数据不丢失,损失一点性能,保证数据安全。

    11. 写入 commit 状态到Redo Log

    1. 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成。
    2. 此时已返回客户端告知事务提交成功,实际上磁盘数据还未更新,即存在脏页。

    恢复步骤

    1. 对于活跃的事务,直接回滚
    2. 对于Redo Log中是prepare状态的事务,如果BinLog中已记录完成则提交,否则回滚事务

    12. 随机写入磁盘, 以Page为单位

    1. InndoDb内部基于一定的策略不定期刷盘,将BufferPool的数据持久化到磁盘。
    2. 磁盘的随机读/写和顺序读/写在性能上有巨大差异, MySQL数据本身是逻辑有序而不是物理有序。
    3. Redo、Undo、Binlog是顺序写入,性能较高。

    系列文章

    下一篇:【MySQL系列文章(二)】InnoDb索引结构及特点

    展开全文
  • 本文将从MySQL总体架构—>查询执行流程—>语句执行顺序来探讨一下其中的知识。 一、MySQL架构总览: 架构最好看图,再配上必要的说明文字。 下图根据参考书籍中一图为原本,再在其上添加上了自己的理解。   从...
  • 查询执行流程--->语句执行顺序来探讨一下其中的知识。从上图中我们可以看到,整个架构分为两层,上层是MySQLD的被称为的‘SQL Layer’,下层是各种各样对上提供接口的存储引擎,被称为‘StorageEngineLayer’。...
  • 主要给大家介绍了SQL语句执行的相关内容,文中一步步给大家深入的讲解,包括MySQL架构总览->查询执行流程->SQL解析顺序,需要的朋友可以参考下
  • 最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。...
  • 在解释这条语句执行流程之前,我们看看mysql的基础架构。 图来自极客时间的mysql实践,该图是描述的是MySQL的逻辑架构。 server层包括连接器、查询缓存、分析器、优化器、执行器涵盖 MySQL 的大多数核心服务功能,...
  • mysql 一条sql执行流程

    2021-01-27 13:37:39
    1.1 select语句执行过程下图是 MySQL 的一个简要架构图,从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图,连接器: 身份认证和...

    1.1  select语句执行过程

    下图是 MySQL 的一个简要架构图,从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。

    bc21e019e7ce

    先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图,

    连接器: 身份认证和权限相关(登录 MySQL)。

    查询缓存: 执行查询语句的时候,会先查询缓存(MySQL 8.0 版本后移除,因为这个功能不太实用)。

    分析器: 没有命中缓存的话,SQL 语句就会经过分析器,分析器说白了就是要先看你的 SQL 语句要干嘛,再检查你的 SQL 语句语法是否正确。

    优化器: 按照 MySQL 认为最优的方案去执行。根据cost开销进行优化,比如走哪条索引、哪个表驱动,选择cost开销最小的执行计划去执行

    执行器: 执行语句,然后从存储引擎返回数据

    简单来说 MySQL 主要分为 Server 层和存储引擎层:

    Server 层:主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog 日志模块。

    存储引擎: 主要负责数据的存储和读取,采用可以替换的插件式架构,支持 InnoDB、MyISAM、Memory 等多个存储引擎,其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

    bc21e019e7ce

    2. update/insert/delete 语句执行过程

    buffer bool: innodb为了提升读写效率,设计了一个缓冲区buffer bool 每次从磁盘读取固定大小的数据存储到缓冲区buffer pool。缓冲区还没有从内存区同步到磁盘的时候,叫做脏页,因为断电等其他操作可能会导致与磁盘数据不一致。由后台线程同步到磁盘,不断进行,动作叫刷脏。刷脏并不是时时进行,在同步过程中如果服务器重启或者断电?这时候数据一致性怎么保证?

    redo log:mysql innodb为了解决这种情况设计了redolog,重启后查看redolog中还有没有没同步的数据,若有则同步,实现数据的持久性。那这样设计有什么漏洞吗?

    先写内存,又要写redo log ,为什么不直接写磁盘呢?这里引申处个顺序IO和随机IO的问题,读取数据先要找到扇区,磁盘不停转动,如果存储数据是随机存储,需要不停的寻址,叫做随机IO,如果存储的数据在一个块区,叫做顺序IO。redo log 写入日志是采用的是顺序IO,减少了寻址次数,加快IO的读写速度。

    redo log 默认 48M ib_logfile0  ib_logfile1 ,记录在某个数据页进行了什么修改,属于物理日志。redolog 写满了触发刷盘操作,可以尽量设置的大一点,延缓redolog 的刷盘频率。

    bc21e019e7ce

    undo log: 记录事物发生之前的数据状态,发生异常时候回滚,保证数据的原子性。作用场景:如果一个操作包含多个sql语句,如果执行了一半失败了,保证所有的sql失败或者成功,实现原子性。没有独立的日志~

    bc21e019e7ce

    根据上述我们可以总结出一条更新语句的执行流程:

    update table set name=“new name”

    1、事物开始,从内存buffer pool 或磁盘data file 取到这条数据的数据页面,返回给Selver执行器

    2.执行器修改这条数据的值如:set name=“new name”

    3.记录name = oldname 到 undolog

    4.记录name=newname 到 redolog

    5.调用存储引擎innodb接口,记录修改后的数据页到buffer pool

    6.事物提交

    bc21e019e7ce

    总结:在buffer pool 即内存中修改,修改完成后提交到磁盘。undolog 记录修改前数据,redolog 记录修改后数据。

    log buffer   buffer pool 的内存缓冲区  默认提交事物时候log buffer 提交 进行刷盘。

    change buffer 是buffer pool 一部分。若缓冲区数据是非唯一索引,且不存在重复数据,这种情况也就不需要从磁盘中加载数据判断数据非唯一性。可把修改数据放在缓存中,提升读写效率。根据页面场景调整change buffer大小

    bc21e019e7ce

    一般占到机器80%,提高读写效率

    bc21e019e7ce

    buffer pool LRU 内存淘汰算法 ,根据热点数据进行淘汰。新查询或者进来的数据放到头部,不访问的数据放到链表尾部。类似JVM 热数据、冷数据区~

    bc21e019e7ce

    binlog: Server 层日志。非innodb独有。以事件的形式记录的DDL、DML日志,记录的是操作而不是数据值,属于逻辑值。可用于实现主从复制。和数据恢复(需要定时记录全量日志,先恢复全量数据,再根据binlog日志进行数据更新)

    崩溃恢复时根据binlog日志记录,判断是否需要提交或回滚,走到了第6步则提交,否则回滚。

    bc21e019e7ce

    3. 总结

    MySQL 主要分为 Server 层和引擎层,Server 层主要包括连接器、查询缓存、分析器、优化器、执行器,同时还有一个日志模块(binlog),这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。

    引擎层是插件式的,目前主要包括,MyISAM,InnoDB,Memory 等。

    SQL 等执行过程分为两类,一类对于查询等过程如下:权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎

    对于更新等语句执行流程如下:分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

    展开全文
  • MySQL逻辑架构(五)——SQL执行流程

    千次阅读 2022-03-12 22:20:52
    本文章介绍了一条SQL语句的执行流程
  • 一条sql语句执行时,在mysql内部的流程是怎样的呢? 这里对mysql内部拆解一下,这样当碰到mysql的一些异常或者问题时,能快速定位解决问题 先看下mysql的逻辑架构图 大体来说,mysql可以分为Server层跟存储引擎...
  • 文章目录一、逻辑架构1.1 第一层:连接层1.2 第二层:服务层1.3 第三层:引擎层1.4 存储层1.5 小结二、SQL执行流程2.1 查询缓存2.2 解析器/分析器2.3 优化器2.4 执行器2.5 小结三、数据库缓冲池(buffer pool)3.1 ...
  • Mysql架构以及SQL语句的执行流程

    千次阅读 2021-05-05 16:02:38
    Mysql架构以及SQL语句的执行流程 在平时的开发过程中,我们使用的数据库大多数看到的只是数据库的一个整体,一般都是输入一条语句,返回一个结果集,但是如果我们不知道其内部执行的细节,当我们在碰到一些异常情况...
  • title: “SQL语句在MySQL中的执行流程” date: 2022-02-25T15:28:58+08:00 draft: false 文章目录1 基本架构概述2 组件介绍2.1 连接器2.2 查询缓存2.3 分析器2.4 优化器2.5 执行器 1 基本架构概述 MySQL 分为两...
  • 详细介绍了Mysql的基础架构以及一条查询sql执行流程
  • 上图给出的是 MySQL 的基本架构示意图,从中你可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。 Server 层包括连接器、查询缓存、分析器、...
  • MySQL--一条SQL查询语句的执行流程

    多人点赞 2021-11-09 23:25:15
    本文介绍MySQL中一条SQL查询语句的执行流程。 此问题也是Java后端面试中常见的一个问题。 流程概述 说明 MySQL 可以分为 Server 层和存储引擎层两部分。 Server 层 包括连接器、查询缓存、分析器、优化器、...
  • MySQL数据库:SQL语句的执行过程

    万次阅读 多人点赞 2021-02-22 03:59:20
    我们的系统在和 MySQL 数据库进行通信前,需要先和数据库建立连接,而这个功能就是由MySQL驱动底层帮我们完成的,建立完连接之后,我们只需要发送 SQL 语句就可以执行 CRUD 了。如下图所示: 一次 SQL 请求就会...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 51,951
精华内容 20,780
关键字:

mysql架构与sql执行流程

mysql 订阅