专栏首页兜兜毛毛MySQL SQL语句是如果被执行的?(1)原

MySQL SQL语句是如果被执行的?(1)原

一个SQL语句被发送到MySQL是如果被执行的?

select name,age from user where age > 8

如上一个SQL语句,发送到MySQL服务器之后,会做什么,如何识别上边语句并返回结果?下面我们来详细说明这个过程。

语法解析和预处理(Parser & Preprocessor)

假如我们随便执行一个字符串,MySQL服务器会报一个1064的错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from zhangsan' at line 1, Time: 0.000000s

它是怎么知道我输入的内容是错误的?

这个就是MySQL的Parser解析器和Preprocessor预处理模块。

这一步主要做的就是对传过来SQL语句进行词法和语法分析和语义的解析。

任何一个编程语言都会有自己特有的词法、语法定义。

  • 语法,描述程序看起来是什么样的;
  • 语义,描述程序的含义
词法解析

词法分析就是把一个完整的SQL语句打碎成一个个的单词。

比如上边的SQL语句

select name,age from user where age > 8

它会被打碎成8个符号,每个符号都是什么类型,从哪里开始到哪里结束。

语法解析

第二步就是语法分析,语法分析会对SQL做一些语法检查,比如单引号有没有闭合,然后根据MySQL定义的语法规则,根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树(select_lex)。

任何数据库的中间件,比如Mycat,Sharding-JDBC(用到了DruidParser),都必须要有词法和语法分析功能,在市面上也有很多的开源的词法解析的工具(比如LEX,Yacc)。

sql语法优先级

顺序

语法

说明

1

from

2

where

从左往右,自上而下

3

group by

4

having

消耗资源。尽量避免使用。

5

select

少用*号,尽量使用字段名称

6

distinct

少用*号,尽量使用字段名称

7

order by

执行顺序从左到右,消耗资源

8

limit

预处理器

如果我写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?是在数据库的执行层还是解析器?比如:

select * from user_back;

解析器可以分析语法,但是它怎么知道数据库里面有什么表,表里面有什么字段呢?

实际上还是在解析的时候报错,解析SQL的环节里面有个预处理器。

它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。

预处理之后得到一个新的解析树。

查询优化器(Query Optimizer)与查询计划

得到解析树之后,是不是执行SQL语句了呢?

这里我们有一个问题,一条SQL语句是不是只有一种执行方式?或者说数据库最终执行的SQL是不是就是我们发送的SQL?

这个答案是否定的。一条SQL语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?

这个就是MySQL的查询优化器的模块(Optimizer)。

查询优化器的目的就是根据解析树生成不同的执行计划(ExecutionPlan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。

可以使用这个命令查看查询的开销:

show status like 'Last_query_cost'
优化器可以做什么?

MySQL的优化器能处理哪些优化类型呢?

举两个简单的例子:

  1. 当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表。
  2. 有多个索引可以使用的时候,选择哪个索引。

实际上,对于每一种数据库来说,优化器的模块都是必不可少的,他们通过复杂的算法实现尽可能优化查询效率的目标。

如果对于优化器的细节感兴趣,可以看看《数据库查询优化器的艺术-原理解析与SQL性能优化》。

但是优化器也不是万能的,并不是再垃圾的SQL语句都能自动优化,也不是每次都能选择到最优的执行计划,大家在编写SQL语句的时候还是要注意。

如果我们想知道优化器是怎么工作的,它生成了几种执行计划,每种执行计划的cost是多少,应该怎么做?

优化器是如何得到执行计划的?

首先我们要启用优化器的追踪(默认是关闭的):

show variables like 'optimizer_trace';

set optimizer_trace='enabled=on';

注意开启这开关是会消耗性能的,因为它要把优化分析的结果写到表里面,所以不要轻易开启,或者查看完之后关闭它(改成off)。

执行一个SQL语句,优化器会生成执行计划

select a.* from sys_user a ,sys_user_role b  where a.id = b.user_id;

这个时候优化器分析的过程已经记录到系统表里面了,我们可以查询:

select * from information_schema.optimizer_trace;

它是一个JSON类型的数据,主要分成三部分,准备阶段、优化阶段和执行阶段。

阶段

类型

说明

1

join_preparation

SQL准备阶段

2

join_optimization

SQL优化阶段

3

join_execution

SQL执行阶段

分析完记得关掉:

set optimizer_trace='enabled=off';

show variables like 'optimizer_trace';
优化器得到的结果

优化完之后,得到一个什么东西呢?

优化器最终会把解析树变成一个查询执行计划,查询执行计划是一个数据结构。

当然,这个执行计划是不是一定是最优的执行计划呢?不一定,因为MySQL也有可能覆盖不到所有的执行计划。

我们怎么查看MySQL的执行计划呢?比如多张表关联查询,先查询哪张表?在执行查询的时候可能用到哪些索引,实际上用到了什么索引?

explan(执行计划)

MySQL提供了一个执行计划的工具。我们在SQL语句前面加上explain,就可以看到执行计划的信息。

explain select a.* from sys_user a ,sys_user_role b  where a.id = b.user_id;

注意:Explain的结果也不一定最终执行的方式。

expain出来的信息有12列,分别是 |列名|描述| |--|--| |id|选择标识符| |select_type|表示查询的类型| |table|输出结果集的表| |partitions|匹配的分区| |type|表示表的连接类型| |possible_keys|表示查询时,可能使用的索引| |key|表示实际使用的索引| |key_len|索引字段的长度| |ref|列与索引的比较| |rows|扫描出的行数(估算的行数)| |filtered|按表条件过滤的行百分比| |Extra|执行情况的描述和说明|

id

这是SELECT的查询序列号,可理解为SQL执行的顺序标识,SQL从大到小的执行。

  1. id相同时,执行顺序由上至下
  2. 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
  3. id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

select_type

类型

说明

SIMPLE

简单SELECT,不使用UNION或子查询等

PRIMARY

子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY

UNION

UNION中的第二个或后面的SELECT语句

DEPENDENT UNION

UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT

UNION的结果,union语句中第二个select开始后面所有select

SUBQUERY

子查询中的第一个SELECT,结果不依赖于外部查询

DEPENDENT SUBQUERY

子查询中的第一个SELECT,依赖于外部查询

DERIVED

派生表的SELECT, FROM子句的子查询

UNCACHEABLE SUBQUERY

一个子查询的结果不能被缓存,必须重新评估外链接的第一行

table

显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是简称,例如上面的a,b,也可能是第几步执行的结果的简称

type

对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。

类型

说明

ALL

Full Table Scan, MySQL将遍历全表以找到匹配的行

index

Full Index Scan,index与ALL区别为index类型只遍历索引树

range

只检索给定范围的行,使用一个索引来选择行

ref

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

eq_ref

类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

const、system

当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

NULL

MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用(该查询可以利用的索引,如果没有任何索引显示 null)

Key

key列显示MySQL实际决定使用的键(索引),必然包含在possible_keys中

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

ref

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况:

类型

说明

Using where

不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary

表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by

Using filesort

当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer

该值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

Impossible where

这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)

Select tables optimized away

这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

No tables used

Query语句中使用from dual 或不含任何from子句

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Redis 缓存问题(13) 原

    因为这些数据是很少修改的,所以在绝大部分的情况下可以命中缓存。但是,一旦被缓存的数据发生变化的时候,我们既要操作数据库的数据,也要操作Redis的数据,所以问题...

    兜兜毛毛
  • 使用k3s 恢复数据库(前提有完整的数据库数据文件)

    前几天在部署k3s相关服务时,不小心把操作系统整坏了。导致无法启动,磁盘上还有我的一些重要数据。

    兜兜毛毛
  • Redis 存储原理(1)

    Redis现在基本也算是后台开发的基础服务,基本像Mysql一样普遍在应用中使用了。我第一次接触的Nosql是memcache用来解决夸服务session共享问...

    兜兜毛毛
  • DBLE3.0来了,你有什么想了解的吗?

    1、我们一般把中间件跟MySQL高可用分开讨论,从您的分享话题来看,中间件指导高可用选型有什么特殊意义吗?

    [3306 Pai ] 社区
  • 「 深入浅出 」集合Set

    扩展 判断两个元素相等的标准:两个对象通过equals()方法比较相等,并且两个对象的hashCode()方法返回值也相等。

    KEN DO EVERTHING
  • 009.Kubernetes二进制部署kube-apiserver

    kube-apiserver、kube-scheduler 和 kube-controller-manager 均以多实例模式运行:

    木二
  • storm RollingTopWords 实时top-N计算任务窗口设计

    转发请注明原创地址 http://www.cnblogs.com/dongxiao-yang/p/6381037.html

    sanmutongzi
  • Linux 下软 RAID 实现方案

    Raid大家都知道是冗余磁盘的意思(Redundant Arrays of Independent Disks,RAID),可以按业务系统的需要提供高可用性和冗...

    用户5807183
  • Linux 下软 RAID 实现方案

    Raid大家都知道是冗余磁盘的意思(Redundant Arrays of Independent Disks,RAID),可以按业务系统的需要提供高可用性和冗...

    小小科
  • 玩转Mysql系列 - 第25篇:sql中where条件在数据库中提取与应用浅析

    一条SQL,在数据库中是如何执行的呢?相信很多人都会对这个问题比较感兴趣。当然,要完整描述一条SQL在数据库中的生命周期,这是一个非常巨大的问题,涵盖了SQL的...

    路人甲Java

扫码关注云+社区

领取腾讯云代金券