SQL是怎样执行的
下面的查询SQL语句是经过哪些阶段,然后把数据返回给客户端的?
select * from t where a = 1;
下面的更新语句又是怎样执行的?
update t set a = a+1 where id =1;
我们都知道磁盘IO性能的问题,那么针对更新操作这种随机写,MySQL又是怎么进行优化的呢?
带着这些疑问,我们一起来学习下MySQL的相关思想和机制。
资料参考来自极客时间MySQL实战45讲
MySQL 服务架构
MySQL 整体逻辑架构如下图,从图中可以清楚的看到一个SQL在各个模块中的执行过程。
图一:MySQL逻辑架构从总体来看,可以分为服务层和存储引擎层。服务层主要用于连接管理,语法分析检查,SQL优化,以及存储引擎的调用来获取结果。除了这些,服务层还内置了所有的函数,如日期、时间、数学操作等,所有的跨存储引擎的功能也在这一层实现,比如存储过程,触发器、试图等。
存储引擎层主要提供数据的读取和存储的能力,存储引擎层架构是采用插件式开发的,目前的常见的存储引擎主要有InnoDB、MyISAM、Memory等。MySQL5.5 以后默认的存储引擎是InnoDB,后面我们不特殊说明的情况下,所描述的引擎都是InnoDB 引擎。
连接器
客户端发起连接请求,完成TCP连接后,连接器去权限表查询该请求的用户的权限,之后该链接的权限判断逻辑,都依赖此时读取到的权限。也就是说,连接建立完毕,此时修改用户的权限,不会对该链接产生任何影响。
查询缓存
完成连接后,执行select 语句就会进入到查询缓存,如果命中了,则判断下是否有该表的读权限,然后返回。缓存是通过key-value 形式存储的,key你之前执行的SQL,value 结果集。不过select 语句所涉及的表任何一个发生了更新,这个缓存就会失效。因此查询缓存的命中率不高,后续MySQL8.0 已废弃该功能。可以通过设置query_cache_type,来控制使用查询缓存的机制。
分析器
这一模块主要是针对SQL进行词法分析和语法分析。语句执行到这一步,先进行词法分析,提取关键词,获取对应的语句特征词,如 表名 t,做完词法分析后,在进行语法分析,检查语法是否存在异常。分析器还会检测表、列等是否存在。
优化器
分析器分析校验通过后,来到优化器阶段。优化器会基于成本模型去分析SQL,然后给出相应的执行计划。不同的执行计划,逻辑结果相同,但是性能可能相差甚远。
执行器
MySQL 通过分析器知道你要做什么,通过优化器知道该怎么做,那么执行器就是去执行了。不过在执行前,会校验下你有没有对应表的操作权限,如果没有则返回没有权限的错误。与查询缓存不同的是,查询缓存是返回结果的时候,才进行表操作权限的校验。
查询语句是怎样执行的
如开题的查询SQL,如果a 列不是索引,那么可以理解MySQL执行如下操作:
- 调用存储引擎接口,获取 t 表一行数据。校验数据中 a 的值是否 等于1,是则保存在结果集,不是则跳过。
- 调用引擎接口,获取下一行,重复进行判断逻辑,直至取完表的最后一行。
- 执行器将上述遍历过程中,产生的结果集,返回给客户端。
可以看出执行器查询的过程,就是重复循环获取和判断的过程。如果循环很多次的话,那么性能就会变得非常糟糕。如果触发了索引,那么存储引擎将通过索引进行返回行的过滤,减少循环次数。
更新语句是怎样执行的
我们看下 update t set c=c+1 where ID = 2 这个更新语句的执行过程。
图二:MySQL两阶段提交如图,深绿色背景的动作发生在MySQL server层,浅绿色背景的发生在InnoDB引擎层。MySQL 通过redo log 和binlog的两阶段,保证了事务的完整性。
日志系统
我们都知道,磁盘的IO是很稀缺的系统资源,其中可以分为顺序读、顺序写、随机读、随机写,其中顺序操作效率高于随机操作。
我们操作数据库,增删改查,肯定不能保证磁盘顺序的操作,其中更新、插入、删除都可以看做对磁盘的写操作,查询看做读操作。我们先不去关心读的问题,先来看看MySQL是怎样应对随机写。
我们先来看下这样的一个场景:
小明 从学校图书馆借了一本《高性能MySQL》,经过1个月的努力,终于看完了,于是决定把书还了。来到图书馆门口,在门卫处登记:小明还《高性能MySQL》,然后门卫放行;来到图书管理员处,将书交给管理员,管理员将这本书打上标签,4号书架第一行的第三个的位置;如果此时,每来一个人还书,管理员都将书放回到书架,那是多么恐怖的一件事情,估摸着管理员也是衣带渐宽终不悔了。这个时候,管理员一般都是在附近放几个收纳箱,然后将书按照顺序放置到收纳箱中;然后小明离开,门卫处登记:小明还《高性能MySQL》完成;志愿者会定期的将收纳箱中的书按照管理员标记的位置,放置到对应的书架上。当然门卫登记这事是我添加的。
从场景中,我们可以如下抽出
门卫处登记:binlog,记录一些逻辑操作,如小明还《高性能MySQL》。
管理员登记:redo log,记录物理操作,将《高性能MySQL》放还至4号书架,第一行的第三个位置。
志愿者:InnoDB后台进程,将数据页刷到磁盘上;即志愿者将《高性能MySQL》,书放还至4号书架,第一行的第三个位置。
这种先写日志,再写磁盘的技术,我们称之为WAL(Write-Ahead logging)。
redo log
redo log 主要记录了InnoDB 引擎的数据页的物理操作日志,就好比上述场景的收纳箱。InnoDB 的redo log 是固定大小的,比如可以配置一组4个文件,每个文件1GB,那么这写”收纳箱“可以暂存4GB 的操作。如下图,从头开始写,写到文件末尾,又回到开头循环写。
图三:redo log
write pos:当前写记录的位置,每记录一条,顺时针移动一次。
check point : 是当前擦除记录的位置,每擦除一条记录,顺时针移动一次。
write pos 和check point 之间,代表着”收纳箱“还可以放书的地方。如果write pos 追上了 checkpoint,代表着收纳箱满了,此时暂停接收还书,先把收纳箱的书放置一部分到对应的书架上,把checkpoint 往前推进下,再继续执行还书的流程。
有了redo log,InnoDB 就可以保证数据库发生异常重启,之前提交的记录都不会丢失。也就是crash-safe能力。
binlog
binlog 就类似于上面的场景中的门卫登记,记录着逻辑日志,如小明还《高性能MySQL》,小明还《高性能MySQL》完成。
binlog采用追加写的方式,发生在MySQL的服务层,主要用来归档使用,MySQL 主从同步,是基于binlog的。日志记录有三种模式,ROW、statement、mixed。
ROW:可以理解为记录的变化,可以从日志里看出行记录的变化前和变化后的状态。
Statement:可以理解为SQL语句。
Mixed:是ROW 和Statement 混合着使用。
我们先看下这两个日志的不同之处:
- redo log 是InnoDB 引擎特有的;binlog 是MySQL Server层实现的,所有的引擎都可以使用。
- redo log 是物理日志,记录的是某个数据页上做了什么修改;binlog 是逻辑日志,记录了语句的原始逻辑,如 给 id= 1 这行的 a字段加1。
- redo log 是循环写,空间固定会用完;binlog 是追加写,binlog文件写到一定大小,会切换下一个,不会覆盖以前的日志。
- redo log 可以保证提交的数据,在数据库异常重启后,不丢失。binlog不可以保证。
为什么binlog没有crash-safe 能力
前面我们提到,MySQL采用WAL技术,来保证高效的性能的。如果没有redo log,引擎层先把数据写入内存,然后binlog commit,这时数据库发生了重启,但是引擎层是无法确保内存中的数据页已经持久化到磁盘的。但是如果redo log 存在就不一样了,redo log会将数据页的物理操作写到日志里,这个时候数据库异常重新,可以通过redo log 进行数据页恢复。
为什么两阶段提交
如图二所示,redo log 和binlog 通过两阶段提交,保证一个事务的完整性的。为什么要两阶段提交,直接提交redo log 或者直接提交binlog为什么不可以?我们看下面两个场景。
redo log prepare 阶段直接commit,然后再binlog commit;如果redo log commit了,binlog 尚未commit,此时如果crash,那么主从模式下,从库比主库少了一次数据库操作。
先binlog commit,然后redo log commit,redo log不存在prepare 阶段。这样的场景,如果redo log commit 前binlog commit 后发生了crash,则从库会比主库多了一次数据库操作,总之不采用两阶段提交,会导致主从不一致,事务的完整性得不到保障。
如图三所示,
- 如果redo log 在prepare阶段发生了crash,那么可以肯定的,本次数据回滚。
- 如果在redo log commit 前,prepare 后发生了crash,这种情况分下面两种:
- binlog commit 则 完成redo log commit。
- binlog 事务不完整,即没有commit,则整个事务rollback。
以上就是一个SQL在MySQL中是怎样执行的,以及MySQL日志系统是怎么保证事务的。后面有时间,我们在一起学习MySQL 多版本
网友评论