SQL存储事务引擎+架构

ccc_0
170 阅读11分钟

在数据库收到客户端发来的SQL语句后，会经过三大引擎——SQL引擎，存储引擎和事务引擎后，返回最终的结果给客户端。三大引擎也是数据库管理系统最核心的部分，其中，SQL引擎是来解决SQL执行的问题，存储引擎是来解决存储问题，事务引擎是用来实现ACID特性保证事务准确可靠。

该笔记对三大引擎的内部原理进行了梳理，目录结构如下：

  • 0. 一条SQL的一生
    1. SQL引擎
    • 1.1 语法解析器Parser
    • 1.2 优化器Optimizer
    • 1.3 执行器Executor
    1. 存储引擎-InnoDB
    • 内部数据结构与表扫描
    • LRU算法：解决页面缓存不够的问题
    • 页面查询：哈希表和B+树
    1. 事务引擎
    • 原子性实现原理
    • 一致性实现原理
    • 隔离性实现原理
    • 持久性实现原理
    1. 架构

0. 一条SQL的一生

1676428797312.png 整个流程梳理如下：

  • 客户端发起请求-->后台路由服务器收到请求，发送SQL语句到数据库-->数据库收到语句-->语法解析器Parser-->优化器Optimizer-->执行器Executor -->将结果一步步返回给用户。

中间产物：

  • AST：由语法解析器对SQL语句生成的语法树
  • plan：优化器的产物，树状结构

数据库的核心模块如下：

  • SQL引擎：Parser、Optimizer、Executor
    解决SQL执行问题
  • 事务引擎：写日志本身就是为了事务做的准备
  • 存储引擎：DataFile、Log File
    解决存储问题

1. SQL引擎

1.1 Parser

所有的代码在执行之前都存在一个解析编译的过程，差异点无非在于是静态编译还是动态的。SQL语言也类似，在SQL查询执行的第一步就是查询解析。解析器一般分为词法分析、语法分析、词义分析等步骤。

  • 词法分析：将一条SQL语句对应的字符串分割为一个个token，这些token可以简单分类。
  • 语法分析：把词法分析的结果转为语法树，根据token序列匹配不同的语法规则(如update语法规则)。根据规则匹配SQL语句中的关键字，最终输出一个结构化的数据结构。
  • 语义分析：对语法树中的信息进行合法性校验。

1676430648809.png

1.2. Optimizer

作为语法分析后就已经是程序可以处理的东西了,为什么还需要一个优化器？

程序的执行路径有多种、根据不同目标选择的不同路径。

  • 基于规则的优化(Rule Base Optimizer, RBO)
    • 条件化简
    • 表连接优化：以小表先链接为规则
    • Scan优化：以唯一索引-->普通索引-->全表扫描的查找顺序为规则。
  • 基于代价的优化(Cost Base Optimizer, CBO)
    对于数据库来说，什么是一条SQL的执行代价？时间由用户直接感知，资源在并发情形时也是必须考虑的。更多不仅考虑的是单个代价最低,而考虑整体并发代价最低,整个系统时延吞吐最低,效率最高。基于代价的优化方式更常见。

1.3 Executor

优化器产生一个执行路径后,由执行器负责执行，它执行的时候会从文件中读取数据同时写入日志。

  • 执行模型-火山模型：
    如果我们把每一个用户的SQL都写成一个大的函数是不太可能的,拆分成不同的算子解决用户SQL千变万化.通过组合实现不同功能。
    1676432670975.png
  • 执行模型-向量化模型：
    向量化执行更适合大批量数据处理，对于很多单行数据处理并没有优势，而且往往搭配列式存储使用。
    1676432738136.png
  • 执行模型-编译执行模型
    代码生成之后数据库运行时仍然是一个for循环，但循环内部的代码从简单的虚函数调用plan.next()展开成了一系列具体的运算过程，这样数据就不同再各个operator之间进行传递，而且有些数据还可以直接被存放再寄存器中，进一步提升系统性能。 1676433036345.png

    LLVM动态编译：拿到用户的SQL后,对于optimizer和parser做完解析后来生成已经生成的计划树,动态编译拿到计划树来去动态的生成一个执行的代码路径

2. 存储引擎 :-InnoDB

各个数据库的存储引擎都有差别，但是概念共通，只是实现各有差异。 1676433384677.png

  • System Tablespace: 元信息: 表名、列名、列属性、用户、用户信息
  • General Tablespace：数据
  • Undo Tablespaces：Undo事务日志
  • Redo Log：redo事务日志
  • In-Memory:内存缓存,访问磁盘代价远高于访问内存
  • On-Dist：磁盘
  • Buffer Pool: 存页面数据Page
    分为多个instance,每次访问页面一定再某一个instance里面降低页面访问冲突.
    每个chunk大小为128M，用来存储缓存页面数据。 每个页面16k大小 1676439189814.png

全表扫描：
对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存（Buffer Pool）中，然后再检测记录是否符合搜索条件。

对于使用二级索引+回表方式的查询。设计MySQL的大致计算查询成本依赖两个方面的数据：范围区间数量，需要回表数据量。

当buffer pool里的页面都被使用后需要换存其他页面怎么办？——基于LRU算法淘汰数据。

  • LRU算法：磁盘数据肯定比内存大很多.源源不断访问时内存大小肯定是不足的,有时候需要从磁盘访问数据,内存放不下肯定会涉及一个淘汰的机制,通过LRU算法实现,把最近最常使用的数据保留下来,很少使用的数据淘汰掉.管理内存空间.
  • LRU算法缺陷：如果我们需要扫描100G的表，而buffer Pool只有1GB，这样就会因为全表扫描的数据量大而导致需要淘汰的缓存页多。在淘汰的过程中极有可能将需要频繁使用到的缓存页淘汰掉了，而替换进来使用频率很低的数据。
  • LRU算法优化：MySQL在LRU算法上进行了优化。核心思路为对数据进行冷热分离，冷数据是刚从硬盘读进来，热数据是经常被访问到的数据。优先淘汰冷数据区。

    将LRU链表分成两部分实现冷热分离，冷数据的缓存页在1s之后被访问则移到热数据头部。否则不移动。

Page:

  • NULL值标志位:标志哪些列是空的
  • 扫描的时候会先对Directory做一个二分查找和一个很散的遍历操作就可以得到这个数据 1676440304746.png

查询具体页面：哈希表

  • 哈希表：通过page_id查找,找到哈希桶,在哈希桶里面找到对应的block。
    1676440229766.png

页面内查询：B+ Tree:

  • 数据库索引：是数据库管理系统中辅助数据结构、以协助快速查询、更新数据库表中数据。目前最常用到的索引是通过B+树实现的。B+树是二分查找树的一种拓展。叶节点之间通过双向列表串起来。
    1676440763305.png
  • 页面内查询：二分法快速定位到对应槽，然后再遍历该槽对应分组中的记录即可快速找到指定记录。
    • 点查:可以快速从根到页查到对应数据
      Select * from table where id=200;
    • 范维查:从根到页查到这个数据后通过双向列表往左往右遍历.
      Select * from table where id>2000

2.1 存储引擎的比较

MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive 等等. MyISAM和InnoDB的区别：

  • 就事务，InnoDB支持，MyISAM不支持
  • 就外键，InnoDB支持，MyISAM不支持
  • 就索引，InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，主键不应该过大，因为主键太大，其他索引也都会很大；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
  • Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
  • MyISAM 采用表级锁(table-level locking)；InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁
  • InnoDB 不保存表的具体行数，执行select count(*) fron table 时需要全表扫描，而MyISAM 用一个变量保存了整个表的行数。

InnoDB：
适合高并发的(行锁大幅度提高了多用户并发操作)、事务特性、奔溃恢复、外键特性的场景 比如订单处理支付的场景。
缺点：占用更多的存储空间、不适合大量只读操作且对事务一致性不高的场景。
MyISAM：
读密集型，对存储空间有要求。
缺点：不适合写密集型应用、不支持外键和事务，

3. 事务引擎

事务引擎是为了解决事务ACID问题。
1676441346206.png

  • Atomicity 原子性
    一个事务中的所有操作要么全部完成要么全部失败，不会存在中间状态。
    实现：通过Undo(回滚)日志，全部失败则事务回滚，将数据库回退到之前的状态。同时实现了多版本并发控制(MVCC)，解决读写冲突和一致性读的问题。
    1676441540794.png

    具体Undo日志怎么保证原子性：
    Undo日志是用来记录事务操作前的数据状态的日志，允许数据库在事务发生错误或被回滚时将数据恢复到事务开始之前的状态。Undo Log通常存储在InnoDB存储引擎的表空间中。
    基本过程：在事务开始前生成Undo log，在修改数据(CRUD)前将会被修改的数据保存到Undolog中，事务提交时删除Undolog，事务回滚时使用Undolog还原数据

  • Consistency 一致性
    每个操作都必须是合法的，账户信息应该从一个有效状态变为另一个有效状态。
    一致性逻辑判断由一般由业务实现。

  • Isolation 隔离性
    数据库允许多个并发事务同时对数据进行读写和修改的能力。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。
    实现： 多个并发事务访问同一行记录通过锁机制实现隔离。

    都读：都加读锁共享锁机制
    都写：一个人拿到排他锁,另一个人就不能再使用了。
    一边读一边写: 有的人要拿读锁,有的人要拿写锁怎么办？

    MVCC概念:数据的多版本,老版本存在undo,通过roll point行成链表.有人拿到锁更新新版本数据时,则读老版本数据.实现了读写互不阻塞的方式 1676442017062.png

  • Durability 持久性
    如何保证事务结束后,对数据的修改永久的保存?

    • 方案一: 事务提交前页面写盘。
      缺点1：写放大。本身只写了十几字节的数据,放的时候要整个页面放下去。因为页面是数据管理最小单元。
      缺点2：随机IO。数据在磁盘上随机分布,而磁盘随机访问能力差
    • 方案二：WAL(Write-ahead logging)+Redo Log
      MySQL使用WAL技术，确保在数据据写入磁盘之前相关的事务日志已经被持久化。
      Redo Log物理日志，记录页面变化，保证事务持久化。它在事务提交之前一定会把日志写完,日志写完后事务就代表提交了。如果数据写入磁盘时发生故障则重启MySQL后会根据redo Log重做。

      WAL+RedoLog: 是一种写入日志的策略，确保在数据据写入磁盘之前相关的事务日志已经被持久化。 具体做法是事务开始时，在内存中记录该事务的变更，将首先变更写入redolog日志，事务提交时，相关的redoLog会持久化被刷新到硬盘，如果数据库崩溃或异常关闭，当数据库重新启动时，系统会检查redo log，将其中未提交的事务重新应用到数据库中，以保持数据库的一致性。

4. 架构

blog.csdn.net/qq130966416…? blog.csdn.net/mingyuli/ar…?

avatar
文章
阅读
粉丝