MySQL 系列 - SQL 查询与修改执行过程

发布于: 11 小时前
MySQL系列 - SQL查询与修改执行过程

我们平时都是输入一条语句,返回结果。那么,这条语句在MySQL内部的执行过程是什么样的呢?

select * from tab1 where id = 1;

MySQL的逻辑架构

首先我们来看MySQL的基本逻辑架构示意图

Server

MySQL的核心服务功能。

  • 查询解析、分析、优化、缓存

  • 所有的内置函数:日期、时间、数学与加密函数等

  • 所有的跨存储引起 的功能都在这一层实现:存储过程、触发器、视图

存储引擎

数据的存储与提取。

  • 每个存储引擎都有它的优势与劣势。

  • Server层通过API接口与存储引擎通信,屏蔽不同引擎的差异。

  • 存储引擎不会解析SQL(InnoDB的外键定义解析是例外)。

连接管理与安全性

每个客户端通过服务端的连接器:建立连接、获取权限、维持连接、管理连接。

mysql -h$ip -P$port -u$user -p
  1. 首先进行认证,基于用户名、原始主机信息、密码,如果使用了SSL的方式连接,还可以使用证书认证。

  2. 一旦客户端认证成功建立连接,服务器会继续验证该客户端是否具有执行某个特定查询的权限(这一点意味着一个用户连接后,再进行权限修改,是不会改版已经存在的连接权限的)。

  3. 可以用过 show processlist 查看连接状态。

  4. 线程在客户端太长时间没有操作的情况下,会自动断开,由参数wait_timeout控制,默认8小时。

长连接与短链接

连接建立的过程是比较复杂的,所以我们尽量使用长连接。

但是要注意内存情况,查询过程中临时使用的内存式管理在连接对象里面的,这些资源会在连接断开的时候才释放。所以随着长连接基类下来,可能会产生OOM。

  1. 定期断开长连接再重连。

  2. MySQL 5.7+ 可以通过执行 mysql_reset_connection 进行初始化连接资源。

优化与执行

查询缓存

一个查询请求会先查询一个对大小写敏感的哈希缓存,看之前是否执行过,假如匹配到了,直接返回结果给客户端。

但是,查询缓存会跟踪查询中涉及的每张表,如果这些表有数据变化,那么和这个表相关的所有缓存都将失效。所以对于一个线上业务的表来说,查询缓存是弊大于利的,因为频繁的缓存和失效会带来更多的消耗。MySQL 8.0 +开始直接将查询缓存的整块功能删掉了。

解析与优化

MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引。

  1. 词法分析

  2. 语法分析

  3. 查询优化

  4. 执行语句

  5. 判断是否有对表的执行权限

  6. 调用引擎接口获取表的第一行,判断是否符合预期,如果不是,调用引擎接口“下一行”,直到取到最后一行(走索引的执行逻辑也类似)。

Tips:

  • 用户可以通过关键帧提示优化器,影响它的决策过程。

  • 可以通过 explain 解释优化过重中的各个因素,使用户知道优化器如何决策,提供一个参考基准,便于用户重构查询与Schema,优化配置。

  • 优化器不关心使用的是什么存储引擎,但存储引擎对优化查询是有影响的,所以优化器会请求存储引擎的一些“元”数据,比如表数据统计信息、容量、操作开销等。

  • 慢查询中的 rows_examined 就是指执行器扫描了多少行。

数据更新

相比于查询流程,数据更新基于WAL(Write-Ahead Log)。

Binary Log、Redo Log

Binlog 是 MySQL 本身提供的一种逻辑日志,和具体存储引擎无关,描述的是数据库所执行的 SQL 语句或数据变更情况,主要用于数据复制。

Redo Log 是 InnoDB 存储引擎提供的一种物理日志结构,用来描述对底层数据页操作的具体内容,主要用于实现 crash-safe,并将部分随机 IO 写变为顺序写,提升磁盘操作效率。

innodb_flush_log_at_trx_commit 这个参数设置成1表示每次事务的redo log都持久化到磁盘

两阶段提交

两阶段提交不是 Redo Log 或 InnoDB 中的设计,而是 MySQL 服务器的设计。MySQL是插件化的存储引擎设计,事务提交时,服务器本身与存储引擎都需要提交数据,所以从整体来看,其本身就面临着分布式事务问题。

MySQL 执行器和 InnoDB 存储引擎在执行简单 update 语句 update t set n = n + 1 where id = 2 时的流程(因为此例只执行单条更新语句,所以其自身就是一个事务)。

  1. 执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。

  2. 执行器拿到引擎给的行数据,把这个值加上1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据。

  3. 引擎将这行新数据更新到内存中。然后将对内存数据页的更新内容记录在 Redo Log Buffer 中(这里不详细介绍 Redo Log Buffer。只需知道对 Redo Log 的操作并不会直接写在文件上,而是先记录在内存中,然后在特定时刻才会写入磁盘)。此时完成了数据更新操作。

  4. 接下来要进行事务提交的操作。事务提交时,Redo Log 被标记为 Prepare 状态。通常此时,Redo Log 会从 Buffer 写入磁盘(innodb_flush_log_at_trx_commit,值为1时,每次提交事务 Redo Log 都会写入磁盘)。然后 InnoDB 告知执行器执行完成,可以提交事务。

  5. 执行器生成本次操作的 Binlog,并把 Binlog 写入磁盘。

  6. 执行器调用引擎的提交事务接口,引擎把刚刚写入的 Redo Log 改成提交 Commit 状态,更新完成。

小结

关于具体的优化与执行点:重写查询、决定表的读取顺序,以及选择合适的索引会在该系列其他文章详细解读。

分布式事务的两段提交(2PC)、三段提交(3PC)、共识算法(VSR、Paxos、Raft、Zab等)与全序广播,也会出专门的文章讲解。

发布于: 11 小时前 阅读数: 9
用户头像

俊俊哥

关注

还未添加个人签名 2013.06.01 加入

还未添加个人简介

评论

发布
暂无评论
MySQL系列 - SQL查询与修改执行过程