SQL解析和执行过程

1. 简介

  MySQL整体的架构是分层设计的,各个模块都有自己独立的功能。通常,在执行一个查询的时候,会大致经过三个阶段的处理,还有就是在MySQL中有一个查询缓存管理,如果在接受到请求的开始能够在查询缓存中直接找到对应的查询,就可以直接返回查询缓存中的结果集,而不用执行以下的查询解析、优化和执行这些步骤了。
  (1) SQL解析操作
  从用户接受SQL语句,切分语句并进行词法语法分析,最后生成MySQL可以理解的解析树这种内部数据结构,该过程是SQL解析器负责处理的。
  (2) 进行优化处理并确定最终执行计划
  这个阶段通过优化器的工作,会对上面生成的解析树作出如下处理:a.删除不必要的条件,将复合运算简单化,甚至必要时候重写某些查询操作;b.若存在多表连接,则确定读表的顺序;c.根据用于各数据表的条件和索引统计信息,确定要使用的索引;d.将获取的记录放入临时表,确定是否需要再次加工;
  这个过程用户也可以使用提示器来影响执行计划的决策过程。同时,虽然优化器并不关心表使用的是什么存储引擎,但是这个过程优化器也会请求存储引擎提供数据表容量、某个操作的开销等信息,以及数据表的统计信息等,来帮助优化决策。
  (3) 执行查询计划,获取数据
  依据最终执行计划向存储引擎请求读取记录,此时MySQL可能会将获得的记录执行连接、排序等处理。
  这边祭上MySQL框架图镇楼!
mysql

2. 逻辑查询处理

  逻辑查询处理表示执行查询应该产生什么样的结果,而物理查询代表了MySQL数据库是如何得到该结果的,两种查询方法可能完全不同,不过最终得到的结果必定是相同的。SQL作为一种结构化的查询语言,针对查询语句中的每一个部分都有特定的处理顺序,下面以一个包含较多查询元素的简单SELECT查询为例,整理整根SQL的处理顺序。
tu
  上述的查询语句会执行11个处理步骤,每个操作都会产生一个虚拟表,该虚拟表作为下一个处理的输入部分,同时这些虚拟表对用户是透明的,只有最后一步生成的虚拟表才会返回给用户。
  (1) FROM
  对FROM子句中的左表和右表执行笛卡尔积,产生虚拟表VT1。
  笛卡尔积也叫作交叉连接(Cross Join),对于FROM子句前表包含a行记录,后表包含b行记录,则虚拟表VT1将包含a*b行数据。
  (2) ON
  对虚拟表VT1应用ON筛选,只有那些符合的行才会被插入虚拟表VT2中,这是最先执行的一个过滤条件.
  在SQL中当有NULL存在的时候执行的将是三值逻辑计算,此时对于NULL的逻辑运算结果也被视为UNKNOWN,如果确定要判断是否NULL则应该使用IS NULL、IS NOT NULL这种语句来显式指明意图。例外的情况是,在GROUP BY子句和ORDER BY子句的环境中,会把所有的NULL作为相等值对待。
  (3) JOIN
  如果指定了OUTER JOIN的连接类型(默认关键字OUTER可以省略),比如LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN的时候,那么保留表中被过滤条件过滤掉的数据会被作为外部行添加到虚拟表VT2中,非保留表中的数据字段会被赋于NULL值,以此产生虚拟表VT3。
  注意:如果FROM子句包含两个以上的表需要执行连接操作,则对上面步骤产生的VT3和下一个表重复执行上述(1)~(3)步骤,直到处理完所有的表为止。
  (4) WHERE
  对虚拟表VT3应用WHERE过滤条件,只有符合的记录才会被插入到虚拟表VT4。这个阶段对一下两种过滤条件是不允许的:a. 由于数据还没有分组,所以不能在WHERE的过滤器中使用MIN(col)这类的分组统计类的过滤操作;b.由于此时还没有进行列的选取操作,因此在SELECT中使用的列的别名是不被允许使用的。
  (5) GROUP BY
  根据GROUP BY子句中的列,对虚拟表VT4中的记录进行分组操作,产生VT5;在分组操作中,多个NULL会被认为是相等的,这些NULL会被丢到同一个分组当中去。
  (6) CUBE | ROLLUP
  对虚拟表VT5执行ROLLUP操作,产生虚拟表VT6;而MySQL只支持CUBE关键字但是没有在数据库层面实现该功能,使用该功能的语句会报错退出。
  ROLLUP是在GROUP BY操作的后面增加WITH ROLLUP的方式工作,其效果是对GROUP BY中的列按照多个维度进行聚合操作,即分别对GROUP BY中的N列、前N-1列、前N-2列……前1列分别进行聚合操作。使用ROLL UP的时候一定要注意如果列中包含NULL值,那么ROLL UP出来的结果可能是混乱的,因为NULL在ROLL UP表示忽略聚合的列,具有特殊的含义的,因此对于含有NULL的列使用ROLL UP最好将这些NULLABLE的列进行转换IFNULL(xxx, 0)。
  (7) HAVING
  对虚拟表VT6应用HAVING过滤器,只有符合的记录才会被插入到虚拟表VT7中。这是继ON和WHERE后的最后一个过滤条件了,该不走会对上一步产生的虚拟表VT6使用HAVING过滤器,HAVING过滤器是对分组条件执行的过滤器。
  注意:子查询是不能用于分组的聚合函数,比如HAVING COUNT(SELECT …)是非法的;同时如果采用了外连接,这需要注意HAVING中的COUNT(1)或COUNT(*)可能会包含OUTER JOIN中额外加入的插入行统计在内。
  (8) SELECT
  执行SELECT操作,选定指定的列并插入到虚拟表VT8中;
  虽然SELECT是在查询语句中最先出现的,不过直到这里才会被处理,从上一个虚表VT8中选出需要的列。注意列的别名不能在SELECT的其他表达式中使用,比如SELECT order_id as o, o+x… 是非法的。
  (9) DISTINCT
  去除重复数据,产生虚拟表VT9;这步产生的虚表和上一步的虚表差异是会对DISTINCT操作的列添加一个唯一索引,以此来去除重复的数据记录。
  (10) ORDER BY
  将虚拟表VT9中的记录按照进行排序操作,产生虚拟表VT10;
  这边我们不应该假定最终取出的数据是按照表中主键的大小顺序取出的,即使对于InnoDB这种聚簇索引也没有这样的保证。关系数据库的理论是建立在数学的集合理论之上的,是没有任何顺序的假设和保证的,如果想要取得的记录有序,则必须显式的使用ORDER BY操作。
  对于NULL,在ORDER BY中总是会排在最前面,所以在排序上是以最小值来对待NULL的。
  (11) LIMIT
  取出指定的行记录,产生虚拟表VT11并返回给查询用户。
  LIMIT n,m表示从第n条记录开始选择m条记录,是Web开发中常用的分页操作。在开发的时候要记住如果对于很大规模的数据集,这种形式的操作是很耗费资源的,需要考虑建立一定的缓存机制来优化效率。

3. 查询连接

  数据库按照连接的类型分为INNER JOIN、(LEFT|RIGHT|FULL) OUTER JOIN、CROSS JOIN这几类。在连接过程中通常连接的顺序很重要,如果将两个表连接的过程看作是内外两层嵌套循来实现的话(MySQL使用的传统算法),那么外层循环操作的就是驱动表,内层循环操作的就是被驱动表。在INNER JOIN中最终连接的结果不会因为顺序的不同而产生差异,所以优化器可以灵活的选择连接顺序进行优化处理,但是OUTER JOIN中不同的连接顺序会产生不同的结果,优化器就没有这种自由度了。
  (1) INNER JOIN
  在连接的过程中外部表比内部表先读取,在连接的过程中起主导作用,最终连接结果的记录由内侧循环决定,因此被称之为INNER JOIN。
  (2) OUTER JOIN
  外连接中最终连接结果的记录完全由外侧循环决定,如果内侧循环不存在满足条件的记录的时候,对应的列会全部填充为NULL。LEFT和RIGHT只需要将表名互相调换一下就可以得到相同的结果,不过习惯上我们都使用LEFT OUTER JOIN的连接方式。
  LEFT OUTER JOIN使用的时候有黑暗点,就是当ON和WHERE一起使用的时候,对于表列可能为NULL的表(被驱动表)字段的限制条件一定要写到ON中,而不能在WHERE中表示,否则优化器会将该连接转化成INNER JOIN的形式去执行,那么得到的结果将会是天壤之别。

1
2
3
4
SELECT COUNT(*) FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no WHERE s.salary > 80000;
SELECT COUNT(*) FROM employees e INNER JOIN salaries s ON s.emp_no = e.emp_no WHERE s.salary > 80000;

SELECT COUNT(*) FROM employees e LEFT OUTER JOIN salaries s ON s.emp_no = e.emp_no AND s.salary > 80000;

  上面两种写法的结果是等价的,因为LEFT OUTER JOIN与WHERE子句互相冲突,所以将连接转化成了INNER JOIN,前面INNER JOIN生成的记录数明显要少的多。产生这种差异的根本原因是ON条件是在连接的时候使用的,而WHERE条件是在连接之后再筛选记录使用的,但连接后添加的NULL记录在筛选的时候总是会被过滤掉的,导致外连接添加的记录全部被删除掉了。还有一点就是LEFT OUTER JOIN和被驱动表在ON中有筛选条件的情况,比如:

1
2
3
SELECT employees.emp_no, salaries.salary FROM employees
LEFT OUTER JOIN salaries
ON employees.emp_no = salaries.emp_no AND salaries.salary > 80000;

  这个语句其实也很危险,因为被驱动表被填充后salary字段就是NULL的,而NULL除了跟IS NULL操作可以得到确认的结果外,其他的筛选结果都是NULL的,那么将其用做判断条件中的结果是不确定的。
  刚才说的LEFT OUTER JOIN这个阴暗面在INNER JOIN中是不存在的,既INNER JOIN中的筛选条件可以单独写在WHERE中,也可以并列写到ON中。
  (3) CROSS JOIN
  当执行普通连接没有限制条件的时候,既当没有看到ON条件子句的时候,就会等价于生成笛卡尔连接了。如果连接的两个查询都只有一条记录,那么笛卡尔积就可以使用一条查询返回两个查询的结果了,不过对于单个查询返回多条记录的情况,他们的笛卡尔连接会导致生成的数据成几何级数迅速增加,这种方法常常可以方便的用来生成测试数据。

4. 子查询

  子查询是指在一个SELECT中嵌套另一个使用括号包围的SELECT查询语句,子查询可以帮助用简洁的语法写出业务逻辑复杂的SQL语句,可读性比较高使用起来比较的方便,不过老版本的MySQL对子查询的优化不够好,据说某些查询使用子查询后SQL语句的查询性能变得非常的糟糕。
  子查询的结果集可以返回一个值、一个行、一个列、一个表,对应的这些查询被称为标量、行、列、表子查询,根据这种返回值的类型不同,对应子查询可能只能用于某些特定的环境中。
  (1) 简单子查询
  下面假定comparison_operator可以是 = > < >= <= <>
  a. 使用子查询进行比较

1
2
3
non_subquery_operand comparison_operator (subquery)
SELECT col1 FROM t1 WHERE col1 = (SELECT MAX(col2) FROM t2);
SELECT * FROM t1 as t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

  b. 使用ANY、IN、ALL进行子查询

1
2
operand comparison_operator ANY/ALL (subquery)
operand IN (subquery)

  ANY关键字的意思是指对于子查询返回列中的任一数值,如果通过comparison_operator计算的结果都为TRUE,则返回TRUE。IN是=ANY的别名,他们的意义和效果是等价的。
  ALL则表示对于子查询返回列中的所有值,如果比较结果都为TRUE,则返回TRUE。
  (2) 独立子查询
  MySQL优化器对于IN(subquery)优化是一个经典的性能问题:优化器对于IN语句的优化是LAZY的,也就是对于IN子句如果不是显式的列表定义,比如IN(‘a’, ‘b’),那么IN子句就都会被转换成EXISTS相关子查询来处理,意味着即使是一个INDEPENDENT SUBQUERY会被转换成低效率的DEPENTDENT SUBQUERY来处理,如果外部查询的记录数比较多,那子查询会被执行很多次,比如下面的第一个语句会被转化成第二个语句,结果对外查询中的每一个值都会在子查询中扫描一次:

1
2
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

  如果子查询的代价比较高,则上述的查询将会有很严重的性能问题,解决的方法就是尽量将子查询重新改写为连接的方式来处理。
  (3) 相关子查询
  相关子查询是指引用了外部查询列的子查询,即子查询会对外部查询的每一行进行一次计算。对于相关子查询,有时候是通过派生表重写的方式,来避免子查询和外部子查询的多次比较操作,即将子查询作为派生表,然后再和外查询的表进行JOIN操作。
  (4) EXISTS谓词
  EXISTS谓词根据子查询是否返回行而返回TRUE或者FALSE,即无论输入的子查询是否返回行,EXISTS都不会返回UNKNOWN的情况,如果子查询的过滤器为某行返回UNKNOWN则表示该行不返回,会被当做FALSE来处理。
  在以往经验中查询语句列通常不建议使用通配符*,因为可能会导致性能问题,而且项目后期可能也会要求修改表结构,那么相应的代码也应该做出对应变更,不过在EXISTS子查询这个符号可以方便的使用,EXISTS只关心行是否存在,而不会实际去取各列的值。话说现在SELECT COUNT(*)已经执行很快了,而SELECT COUNT(expr)需要注意,只会计算expr不为NULL的情况。
  目前对于IN、EXISTS大多具有相同的执行计划,所以不用相互改写来进行所谓的优化,而且某些情况下是可以互换的,不过两者在处理NULL的时候的差异还是值得大家去关注的:MySQL处理NULL是将其当做UNKNOWN来对待的,这就意味着任何与NULL比较、操作的结果都是NULL,只有IS NULL、IS NOT NULL才能返回确切的bool结果。当使用IN的时候,意味着需要将操作数和集合中的每个元素做=运算,所以如果输入列表中含有NULL的时候,IN只能返回TRUE或者UNKNOWN,而NOT IN也只能返回FALSE或者UNKNOWN;此时EXISTS的语法就比较清晰,任何值比较的操作都在子查询中完成了,EXISTS的结果只依赖返回的行数目。针对这种比较晦涩的使用方法,通常会优先在子查询中使用IS NOT NULL对NULL值先过滤掉,或者使用函数将NULL值转换成某个默认值,然后使用IN和NOT IN就可以得到比较规则的结果了。
  在查询的时候我们应该尽量使用正向查询,而不要使用负向查询NOT IN和NOT EXISTS,同时对于OR查询最好改写为用IN的方式处理,据说他们俩的复杂度是n和log(n),而且MySQL对IN的优化不断的在优化着。
  (5) 派生表
  派生表又被成为表子查询,与其他表一样出现在FROM的子句中,但是派生表是从子查询派生出的虚拟表中产生的,而且派生表是完全的虚拟表,没有办法被物理地具体化,因此优化器不清楚派生表的信息,所以如果想查看涉及派生表的EXPLAIN执行计划速度可能非常慢。
  如上面所述的,将子查询重写为派生表来提高效率,是对MySQL数据库非常有效的优化手段。

1
  FROM ( SELECT ... subquery expression) AS derived_table_alis

5. 子查询优化

  在MySQL中除了之前描述的使用JOIN进行全连接查询之外,还有很多半连接的查询操作,即只带有半边连接性质的查询,比如常见的IN(subquery)这种形式,而对于这类查询MySQL 5.5之前只能通过IN-to-EXISTS进行处理,通常大多数情况下这种处理方式效率都不高。下面优化都是在新版本的数据库中优化器自动实现了的,但是在老版本的数据库中可能还需要手动进行查询重写,而且按照排序前面的优化优先级比后面优化优先级要高,很多时候需要显式关闭某种优化策略才会用到后面的优化方式。
  (1) Table pullout优化
  先将用于半连接子查询的数据表抽取到外部查询,然后将查询改写为连接查询,这是最具代表性的手动查询调优方法。

1
2
3
4
5
6
7
SELECT * FROM employees e WHERE e.emp_no 
IN (SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');
SELECT * FROM employees e WHERE EXISTS
(SELECT 1 FROM dept_emp de WHERE e.emp_no = de.emp_no AND de.dept_no='d009');

SELECT * FROM dept_emp de INNER JOIN employees e
WHERE e.emp_no = de.emp_no AND de.dept_no = 'd009';

  比如上面的IN(subquery)查询,在老版本的数据库中会执行像第二个查询等价的IN-to-EXISTS处理,这会对employees表执行全表扫描,然后对返回的每一个记录执行一次DEPENDENT SUBQUERY,效率是非常低的。现在的数据库会自动将其优化成第三种查询模式,该表先对dept_emp表进行索引范围查询,然后再和employees进行连接操作,整个过程中没有使用到子查询。
  这种形式的优化非常满足MySQL提倡的“尽量使用连接处理子查询”的思路,不过Table pullout只能在子查询使用主键或者唯一性索引查询结果只有一条的形式才可以使用,否则优化和非优化形式查询出来的条目数目是不一致的。
  (2) FirstMatch优化
  FirstMatch优化也是和IN-to-EXISTS类似的处理方式,不过不是通过子查询检索满足条件的记录,而是通过连接的方式,在被驱动表中只要发现第一个满足条件的记录就立马返回而不再继续扫描表了,所以效率很高。不过由于子查询一旦找到第一条记录就返回了,所以不能使用于聚合类性子查询优化。

1
2
SELECT * FROM employees e WHERE e.first_name= 'Matt' AND e.emp_no 
IN (SELECT t.emp_no FROM titles t WHERE t.from_date BETWEEN '1995-01-01' AND '1955-01-30');

  在执行的过程中,会首先访问子查询引用的所有外部数据,比如这里通过e.first_name先对数据进行过滤,然后再使用连接执行子查询操作,所以当外部独立查询带有条件,而且还可以使用独立索引的时候,会选择这种优化处理关联子查询。
  (3) Semi-join Materialization优化
  该方式可以将半连接中的子查询全部具体化,即将子查询的数据全部取出来建立临时表,然后再将外部表和这个临时表执行连接起来,同时在创建临时表的时候会对key创建唯一性索引,因为对IN(subquery)形态的查询需要子查询返回唯一值。

1
2
SELECT * FROM employees e WHERE e.emp_no 
IN (SELECT t.emp_no FROM titles t WHERE t.from_date BETWEEN '1995-01-01' AND '1955-01-30');

  通常,只要IN(subquery)中子查询不是相关查询,优化器都可以考虑将子查询进行具体化。而且不单单于半关联,对于非半联的子查询也可以使用该优化。

本文完!

参考