MySQL查询和更新原理

在对于MySQL的优化,网上有很多小技巧,比如加索引。不过前几天在极客时间上买了门《MySQL实战45讲》。这篇文章主要是在学习过程中关于MySQL原理的一些笔记。

在学习如何优化的过程中,最好对于MySQL查询的过程有一定的理解,这样有利于如何进行优化。下面这张图片是MySQL的逻辑框架:

MySQL从图中可以看出,一般分为三部分:客户端、核心服务、存储引擎。客户端这个就不说了,主要是Java这些客户端;而关于存储引擎的,在之前整理的一篇文章有简绍——MySQL的存储引擎 —— InnoDB和MyIsAM。所以今天主要是讲解下关于核心服务。

MySQL优化原理

MySQL查询过程

mysql> select * from T where ID=10;

当我们输入上面这一条SQL查询语句的时候,发生了什么?

这里面主要涉及的是核心服务中的模块:连接器、查询缓存、分析器、优化器、执行器等,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

连接器

连接器主要的功能是跟客户端建立连接,获取权限,维持和管理连接。一般的命令:

mysql -h$ip -P$port -u$user -p

查询缓存

当MySQL获取到一个查询SQL的时候,会查看缓存,判断这条SQL是否已经执行过了。之前的执行结果会已key-value保存,key是查询SQL,value是查询结果。

之前看网上说,在MySQL8里面,已经去掉了缓存模块。从这里可以看出,在工作中,不建议使用MySQL的缓存。主要是当一个表更新数据的时候,这张表的缓存数据都会被清空,所以缓存适合哪种表内数据变化不大的表。

分析器

如果上面没有命中缓存,就开始真正的执行SQL了。在这一步,MySQL对SQL语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。

Unknown column ‘k’ in ‘where clause 这种错误也是在这一层中出现的。

优化器

这一步需要对解析后的SQL进行优化,比如使用什么索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。

执行器

这一步主要先获取是否对该表有权限操作,然后就是从存储引擎中获取数据。

更新语句

更新SQL执行和上面的过程大致相同。分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后,执行器负责具体执行,找到这一行,然后更新。

更新模块主要在涉及了两个日志模块:redo log(重做日志) 和 binlog(归档日志)。

redo log

当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到 redo log 里面,并更新内存,这个时候更新就算完成了。同时,InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做。

在InnoDB中,redo log 日志是固定大小的,比如分配4个文件,每个文件1G,这样就有4G。在写的时候,就会从头开始写,写到末尾有从新开始循环写。

binlog

redo log 是位于InnoDB存储引擎中的,而binlog 则是位于server层的。

区别

  • redo log是 InnoDB引擎特有的;binlog是MSαL的 Server层实现的,所有引擎都可
    以使用。
  • redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给=2这一行的c字段加1”。
  • redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志

两阶段提交

为了保证两份日志中的逻辑的完整性和正确性,MySQL使用的是两阶段提交来保证数据的完整性。

参考

阅读

博主 wechat
如果不想经常浏览博客,可以关注公众号,会把博客的文章发送到公众号上
客官,赏一杯coffee嘛~~~~
0%