高性能MySQL之查询执行背后的机制

前段时间在团队技术分享上,分享了关于看了《高性能MySQL》这边书之后的技术分享。今天就捡查询执行机制来分享下。

mysql数据库是我们平时Web开发中用的最多的数据库之一;也是经典的关系型数据库。平时后端开发如PHP,Python都是通过相关的驱动去连接数据库,执行增删改查操作。那么问题来了,我们的SQL语句发送到mysql后,mysql内部是怎么处理这个SQL的呢?

这是一张Mysql 服务器的简要逻辑架构图,通常我们shell连接mysql或者通过pdo, mysqli等都是作为客户端形式去连接mysql server.

  1. 在Mysql server端会有相应的连接线程池处理客户端的sql请求.
  2. 收到请求后其实会有一层查询优化,如果sql查询过的缓存,会直接返回结果,避免再次遍历磁盘
  3. 如果没有命中缓存,mysql会进行sql语句的分析,预处理然后解析语法树。
  4. mysql会对生成的解析树进行优化。最终生成查询计划
  5. 将查询计划交由指定的存储引擎MyISAM, InnoDB等进行执行,然后将相应结果返回给客户端,并进行相关缓存。

下面我们就详细看下这些流程,Mysql都做了什么。

  1. 首先是客服端与服务端的通信
    1. client 与 server之间的协议是一个半双工的通信协议。半双工呢就是可以发生数据和接收数据,但是不能同时发生并且同时接收数据。
    2. client 将sql语句用一个数据包发送给服务器。这里可以看出这个数据包的大小应该是有限制的。实际上也是有的。max_packet_size就是配置中的一个参数,用来设置可接受的包的大小,这也就决定了Mysql能够接收到的最大sql语句是多大。超过包大小的sql语句服务器会拒绝接收,并抛出一个错误。类似于PHP接收数据时设置post_max_size设置接收的数据大小。
    3. sever端返回数据时,则是分成许多数据包返回给客户端。
    4. 客户端提取到完整的结果通常是缓存到内存中。Mysql只有在所有的结果都被提取后才会释放掉所以的锁和资源
  2. 查询缓存优化来说。类似我们对web应用做的cache一样,减少同样的查询多次执行。提升性能
  3. 对sql语句的解析和优化。跟C、PHP、Python等语言编译原理类似。mysql会对sql语句继续解析,生成一系列的语言token片段,然后通过解析预处理器对token进行编译,语义检查,生成解析语法树。通常意义的sql语法错误就发生在这个预处理阶段。
  4. 查询优化器。这个是个潘多拉魔盒,挺有意思的模块。Mysql默认会对发送过来的sql进行优化,调整查询顺序,常量表达式替换等,然后生产查询计划。
    1. 所谓条条大路通罗马。那哪条路是最近的呢,mysql会对sql语句进行开销预估,选出它认为开销比较小的sql进行执行。
    2. 为什么不是开销最小的?因为开销最小的会要求mysql把每种可能得方式都去遍历一遍,这个会产生额外的开销。综合呢,mysql采用了统计数据,根据表或索引的页数,唯一性,键和行的长度,行数等等做了预估。选取了他认为比较好的方式。所以从这里可以看出一点端倪,就是sql语句的执行未必可能如我们所期望的那样
    3. 关于查询优化的类型可以参考这篇 Mysql优化器能够处理的优化类型
  5. 关于查询计划的执行,这个涉及了存储引擎底部的数据存储结构,下次会着重讲解。涉及了B+tree以及相关的底层存储知识。最终引擎将查询到的结果返回,然后进行相关排序筛选,最终将查询结果返回给客户端
    1. 如果查询是可缓存的,MySQL会在这时缓存查询。
    2. MySQL增量的产生和发送结果。一旦产生一行输出,就直接将这个结果返回给客户端。有两个好处:
      • 服务器不用将这一行保存在内存中
      • 客户端可以尽快地开始工作

将上面的图再细致的画出。

整个流程就是Mysql关于SQL查询语句执行的基本流程。那么Mysql到底是怎么执行查询计划的?

关于Mysql执行计划,不得不提的就是关联查询。

  1. mysql每个单个查询本质都是一个联接
  2. 把每个联接看成一个嵌套循环。MySQL用一个循环从表中获取数据,然后再利用一个嵌套循环从下一个表中发现匹配数据。不停的持续这个过程。
  3. 子查询,union查询,会将查询结果会存到临时表里,然后将临时表作为普通表做联接查询

 

One thought on “高性能MySQL之查询执行背后的机制”

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>