0%

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。在写的时候,就会从头开始写,写到末尾又从新开始循环写。

有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为 crash-safe。

binlog

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

区别

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

UPDATE 语句更新流程

  • 执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
  • 执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
  • 引擎将这行新数据更新到内存中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。
  • 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。
  • 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成。

两阶段提交

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

参考

阅读

客官,赏一杯coffee嘛~~~~