MySQL(二)：SQL运行机制

尺有所短，寸有所长；不忘初心，方得始终。

请关注公众号：星河之码

我们一般都不会去操作数据库本身，而是通过SQL语句调用MySQL，由MySQL处理并返回执行结果。那么SQL语句是如何执行sql语句的呢？

Sql语句执行过程图解：

从图中可以看出sql的执行基本上分为五步:

• 建立连接（Connectors&Connection Pool）
• 查询缓存（Cache&Buffer）
• 解析器（Parser）
• 预处理器（preprocessor）
• 查询优化器（Optimizer）
• 操作引擎执行 SQL 语句

一、建立连接（Connectors&Connection Pool）

当客户端发送请求时，客户端与服务器首先通过通信协议与MySQL的connectors建立连接。之后MySQL将收到请求暂存在连接池（connection pool)中，由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。

MySQL客户端与服务端的通信方式是【半双工】。所以对于每一个 MySQL 的连接，时刻都有一个【线程状态】来标识这个连接正在做什么。

• 通讯机制

  • 全双工：能同时发送和接收数据。例如平时打电话。

    全双工通信允许数据同时进行双向传输，即有两个信道。

    全双工通信是两个单工通信方式的结合，要求收发双方都有独立的接收和发送能力。

  • 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机

    半双工通信允许信号在两个方向上传输，但某一时刻只允许信号在一个信道上单向传输。

    半双工通信实际上是一种可切换方向的单工通信。

  • 单工：只能发送数据或只能接收数据。例如单行道

    单工通信只支持信号在一个方向上传输（正向或反向），任何时候不能改变信号的传输方向。

  

• 线程状态

  线程状态可以查看用户正在运行的线程信息，查询命令：

  show processlist; #root用户能查看所有线程，其他用户只能看自己的
  

  

  如图可以获取线程的详细信息：

  • Id：线程 ID ，结束线程可以使用以下命令

    kill id
    

  • User：启动这个线程的用户；

  • Host：发送请求的客户端的 IP 和端口号；

  • db：当前命令在哪个库执行；

  • Command：该线程正在执行的操作命令

    • Create DB：正在创建库操作；
    • Drop DB：正在删除库操作；
    • Execute：正在执行一个 PreparedStatement；
    • Close Stmt：正在关闭一个 PreparedStatement；
    • Query：正在执行一个语句；
    • Sleep：正在等待客户端发送语句；
    • Quit：正在退出；
    • Shutdown：正在关闭服务器

  • Time：表示该线程处于当前状态的时间，单位是秒。

  • State：线程状态

    • Updating：正在搜索匹配记录，进行修改；
    • Sleeping：正在等待客户端发送新请求；
    • Starting：正在执行请求处理；
    • Checking table：正在检查数据表；
    • Closing table：正在将表中数据刷新到磁盘中；
    • Locked：被其他线程锁住了记录；
    • Sending Data：正在处理 Select 查询，同时将结果返回发送给客户端；

  • Info：一般记录线程执行的语句，默认显示前100个字符。想看完整语句可以使用

    show full processlist;
    

二、查询缓存（Cache&Buffer）

在sql接口接收到请求后，首先会在查询缓存中查找结果

• 开启查询缓存后，在查询缓存中找到完全相同的SQL语句，则将查询结果直接返回给客户端。

• 没有开启查询缓存或者没有找到完全相同的 SQL 语句。则会由解析器进行语法语义解析，并生成【解析树】。

2.1 开启查询缓存

• 连接mysql服务

  #mysql  -h  ip  -u 用户 -P 端口 -p 
  mysql  -h  127.0.0.1  -u root -P 3306 -p 
  

• 查看是否开启查询缓存

  show variables like "%query_cache%";
  

  

  上图中【query_cache_type】代表是否开启查询缓存

  • 0代表关闭查询缓存（显示OFF）

  • 1表示始终开启查询缓存（显示ON）

    【当sql语句中有sql_no_cache关键词时不使用缓存】

    如：select sql_no_cache user_name from users where user_id = ‘100’;

  • 2表示按需开启查询缓存（显示DEMAND）

    【代表当sql语句中有SQL_CACHE关键词时才缓存】

    如：select SQL_CACHE user_name from users where user_id = ‘100’;

• 开启查询缓存

  • 修改配置文件my.cnf开启查询缓存

  #第一步：查询my.cnf的所在目录
  [root@localhost www]# find / -name my.cnf
  /etc/my.cnf
  #第二步：编辑my.cnf 添加query_cache_type=1
  [root@localhost www]# vim /etc/my.cnf
  query_cache_type=1
  #第三步：重启mysql 生效
  [root@localhost www]# systemctl restart mysqld
  

• 设置变量开启查询缓存

  set global query_cache_type = 1;  
  

2.2 查询缓存失效

当sql语句满足下面条件时，即使开启查询缓存，以下SQL也不能缓存：

• 查询语句使用 SQL_NO_CACHE
• 查询的结果大于 query_cache_limit 设置
• 查询中有一些不确定的参数，比如 now()

三、解析器（Parser）

当查询缓存没有命中时，客户端发送的SQL会被解析器进行语法解析，生成【解析树】，对sqlj进行语法分析，生成解析树如下：

四、预处理器（preprocessor）

当解析器的语法分析通过之后，预处理器会检查生成的解析树，解决解析器无法解析的语义。它会检查表和列名是否存在，检查名字和别名，判断解析树的语义是否正确。预处理之后得到一个新的解析树。

这里留下一个问题，留待以后：

mysql预处理器和解析器生成的解析树有什么区别？

解析器只是语法解析 会生成一个解析树 预处理会检查表名 字段 权限啥的 然后生成一个新的解析树，

预处理会检查表名 字段 权限如果有问题 就直接报错了，那还生成解析树干啥？要是没报错，那他生成的解析树跟解析器的又有啥区别呢？

五、查询优化器

当预处理器生成新的解析树之后，会进入优化器阶段，一条SQL的执行方式有很多种，数据库最终执行的SQL也不是客户端发送的SQL，但是最终返回的结果是相同的。优化器会帮助我们选择一个最优的方式去执行sql.

比如：

表里面有多个索引的时候，决定使用哪个索引；

在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

5.1 查询优化器的作用

查询优化器根据【解析树】生成不同的执行计划，然后选择一种最优的执行计划。MySQL是基于开销（cost）的优化器，选择使用开销最小的执行计划。

#查看查询的开销
show status like 'Last_query_cost';

• 执行计划

  mysql不会生成查询字节码来执行查询，而是生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

5.2 生成优执行计划策略

MySQL生成最优执行计划的有很多种策略，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）

5.2.1 静态优化（编译时优化）

静态优化可以直接对解析树进行分析,并且完成优化。优化器可以通过简单的代数变换将where 条件转换成另一种等价形式,静态优化不依赖于特别的数值,

如where 条件中带入的一些常数等.静态优化再第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化

• 等价变换策略

  • 1=1 and a>1 优化后： a > 1

  • a < b and a=5 优化后 b>5 and a=5

  • 基于联合索引，调整条件位置

5.2.2 动态优化（运行时优化）

• 优化count、min、max等函数

• InnoDB引擎min函数只需要找索引最左边

• InnoDB引擎max函数只需要找索引最右边

• MyISAM引擎count(*)，不需要计算，直接返回

• 提前终止查询

  使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据

• in优化

  MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变 成 in (1,2,3)

六、操作引擎执行 SQL 语句

在优化器生成最优的执行计划之后，会生成指令集，mysql会根据执行计划给出的指令逐步执行，并存储引擎的类型，调用对应的API接口与底层存储引擎缓存或者物理文件的交互，直到完成所有的数据查询。

• 若开启用查询缓存

  会将SQL语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的SQL语句执行则直接返回结果。

• 返回结果采用增量模式返回

  • 服务器端不需要存储太多的结果，不会消耗过多内存。

  • 客户端可以更快的获得返回的结果。

• 当查询不需要返回结果给客户端时，mysql仍然会返回这个查询的其他信息，如行数等

七、总结

回顾一下mysql的运行机制的整体流程

• 首先客户端的请求会通过mysql的connectors与其进行连接，连接后请求会暂存在连接池（connection pool)中，由处理器（Management Serveices & Utilities）管理。

• 当请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。

• SQL接口接收到请求后，会将请求进行hash处理并与缓存中的结果进行对比。如果匹配则返回缓存中的结果，否则解释器处理。

• 解释器接收SQL接口的请求，判断SQL语句语法是否正确，生成解析树。

• 解释器处理完，由预处理器校验权限，表名，字段名等信息。

• 优化器对针对最终的解析树产生多种执行计划，并选择最优的执行计划。

• 确定最优执行计划后，SQL语句交由存储引擎处理，存储引擎会在存储设备中取得相应的数据，并原路返回给客户端。