youyichannel

志于道，据于德，依于仁，游于艺！

0%

重拾MySQL —— MySQL基于规则的优化

发表于 更新于 分类于 ，
本文字数： 9.6k 阅读时长 ≈ 9 分钟

《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第十四章

MySQL会对于用户编写的SQL做转换，转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写。

一、条件化简

1.1 移除不必要的括号

有时候表达式里有许多无用的括号，比如：

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))

优化器会去除那些用不到的括号：

(a = 5 and b = c) OR (a > c AND c < 5)

1.2 常量传递（constant_propagation）

当常量表达式和其他涉及该列的表达式使用AND连接起来时，可以替换掉其中的列名：

a = 5;
a = 5 AND b > a;
# 优化成
a = 5 AND b > 5;

1.3 等值传递（equality_propagation）

有时候多个列之间存在等值匹配的关系，比如：

a = b and b = c and c = 5

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

1.4 移除没用的条件

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们。

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

# 优化之后

a < 1 OR a = 6

1.5 表达式计算

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来。

a = 5 + 1

# 简化成

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，比如：

ABS(a) > 5
# OR
-a < -8

优化器是不会尝试对这些表达式进行化简的。只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

1.6 HAVING子句和WHERE子句的合并

如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。

1.7 常量表检测

MySQL认为以下两种查询运行非常快：

  • 查询的表中一条记录没有，或者只有一条记录。

    这个依靠的是统计数据。不过InnoDB的统计数据数据不准确，所以这一条不能用于使用InnoDB作为存储引擎的表，只能适用于使用Memory或者MyISAM存储引擎的表。

  • 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表

通过这两种方式查询的表称为常量表(constant tables)。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。比如：

SELECT * FROM table1 INNER JOIN table2
    ON table1.column1 = table2.column2 
    WHERE table1.primary_key = 1;

这个查询可以使用主键和常量值的等值匹配来查询table1表，也就是在这个查询中table1表相当于常量表，在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉，即上面的语句会被转换成这样：

SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 
    ON table1表column1列的常量值 = table2.column2;

二、外连接消除

连接查询中，内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃。

在SQL中，凡是不符合WHERE子句中条件的记录都不会参与连接。只要在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，即在这种情况下外连接和内连接没有什么区别。

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;

由于指定了被驱动表t2的n2列不允许为NULL，所以上面的t1和t2表的左（外）连接查询和内连接查询是一样一样的。当然，可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含这个意思即可。

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

-- 等价于

SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;

在这个例子中，WHERE子句中指定了被驱动表t2的m2列等于2，也就相当于间接的指定了m2列不为NULL值。

这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝(reject-NULL)。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

三、子查询优化

子查询学习：https://dev.mysql.com/doc/refman/8.0/en/subqueries.html

3.1 子查询注意事项

  • 子查询必须用小括号扩起来。
  • 在SELECT子句中的子查询必须是标量子查询（只返回一个单一值的子查询）。
  • 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。
  • 对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。
    • ORDER BY子句：子查询的结果其实就相当于一个集合，集合里的值排不排序不重要。
    • DISTINCT语句：集合里的值去不去重也没什么意义。
    • 没有聚集函数以及HAVING子句的GROUP BY子句：在没有聚集函数以及HAVING子句时，GROUP BY子句没有什么作用。
  • 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

3.2 子查询在MySQL中是怎么执行的

3.2.1 标量子查询、行子查询的执行方式

行子查询：返回一条记录的子查询

在下面两个场景中经常会使用到标量子查询或者行子查询：

  • SELECT子句中在查询列表中的子查询必须是标量子查询。
  • 子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。

对于上述两种场景中的不相关标量子查询或者行子查询来说，它们的执行方式是简单的，比方说下面这个查询语句：

SELECT * FROM s1 
    WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

执行方式：

  • 先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询
  • 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 = ...

即对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，当作两个单表查询。

对于相关的标量子查询或者行子查询来说，比如下面这个查询：

SELECT * FROM s1 WHERE 
    key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

执行方式：

  • 先从外层查询中获取一条记录
  • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，然后执行子查询
  • 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃
  • 再次执行第一步，获取第二条外层查询中的记录，依次类推

3.2.2 IN子查询优化

物化表的提出

对于不相关的IN子查询，比如这样：

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

  • 结果集太多，导致内存溢出
  • 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这就导致：
    • 无法有效的使用索引，只能对外层查询进行全表扫描。
    • 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

MySQL中不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程：

  • 该临时表的列就是子查询结果集中的列
  • 写入临时表的记录会被去重

在将结果集写入临时表时对记录进行去重可以让临时表变得更小，节省空间开支。

临时表如何对记录进行去重？

临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引即可。

  • 一般情况下子查询结果集不会太大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立希索引。
  • 如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

MySQL把这个将子查询结果集中的记录保存到临时表的过程称为物化(Materialize)。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

物化表转连接

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

当把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询其实可以从下面两种角度来看待：

  • 从表s1的角度来看待，整个查询的意思其实是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。

  • 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。

即上面的查询就相当于表s1和子查询物化表materialized_table进行内连接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

转化成内连接之后查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

分析一下上述查询中使用外层查询的表s1和物化表materialized_table进行内连接的成本都是由哪几部分组成的：

  • 如果使用s1表作为驱动表的话，总查询成本由下面几个部分组成：
    • 物化子查询时需要的成本
    • 扫描s1表时的成本
    • s1表中的记录数量 × 通过m_val = xxx对materialized_table表进行单表访问的成本（物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤是非常快的）。
  • 如果使用materialized_table表作为驱动表的话，总查询成本由下面几个部分组成：
    • 物化子查询时需要的成本
    • 扫描物化表时的成本
    • 物化表中的记录数量 × 通过key1 = xxx对s1表进行单表访问的成本（key1列上建立了索引，所以这个步骤是非常快的）。

MySQL查询优化器会通过运算来选择上述成本更低的方案来执行查询。

将子查询转换为semi-join

SELECT * FROM s1 
    WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

可以把这个查询理解成：对于s1表中的某条记录，如果能在s2表（准确的说是执行完WHERE s2.key3 = 'a'之后的结果集）中找到一条或多条记录，这些记录的common_field的值等于s1表记录的key1列的值，那么该条s1表的记录就会被加入到最终的结果集。这个过程其实和把s1和s2两个表连接起来的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 
    ON s1.key1 = s2.common_field 
    WHERE s2.key3 = 'a';

只不过不能保证对于s1表的某条记录来说，在s2表（准确的说是执行完WHERE s2.key3 = 'a'之后的结果集）中有多少条记录满足s1.key1 = s2.common_field这个条件，不过可以分三种情况讨论：

  • 情况一：对于s1表的某条记录来说，s2表中没有任何记录满足s1.key1 = s2.common_field这个条件，那么该记录自然也不会加入到最后的结果集。
  • 情况二：对于s1表的某条记录来说，s2表中有且只有记录满足s1.key1 = s2.common_field这个条件，那么该记录会被加入最终的结果集。
  • 情况三：对于s1表的某条记录来说，s2表中至少有2条记录满足s1.key1 = s2.common_field这个条件，那么该记录会被多次加入最终的结果集。

对于s1表的某条记录来说，我们只关心s2表中是否存在记录满足s1.key1 = s2.common_field这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，上述IN子查询和两表连接之间并不完全等价。

MySQL在这里提出了半连接(semi-join)的概念。将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

实现半连接的方法：

  • Table pullout （子查询中的表上拉）：当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中

    SELECT * FROM s1 
        WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

    由于key2列是s2表的唯一二级索引列，所以可以直接把s2表上拉到外层查询的FROM子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：

    SELECT s1.* FROM s1 INNER JOIN s2 
        ON s1.key2 = s2.key2 
        WHERE s2.key3 = 'a';

  • DuplicateWeedout execution strategy （重复值消除）

  • LooseScan execution strategy （松散索引扫描）

    SELECT * FROM s1 
        WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

    在子查询中，对于s2表的访问可以使用到key1列的索引，而恰好子查询的查询列表处就是key1列，这样在将该查询转换为半连接查询后，如果将s2作为驱动表执行查询的话，那么执行过程就是这样：

    如图所示，在s2表的idx_key1索引中，值为'aa'的二级索引记录一共有3条，那么只需要取第一条的值到s1表中查找s1.key3 = 'aa'的记录，如果能在s1表中找到对应的记录，那么就把对应的记录加入到结果集。依此类推，其他值相同的二级索引记录，也只需要取第一条记录的值到s1表中找匹配的记录，这种虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散索引扫描。

  • Semi-join Materialization （半连接物化）

    先把外层查询的IN子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

  • FirstMatch（首次匹配）

    FirstMatch是一种最原始的半连接执行方式，即先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上面这个过程。

semi-join的适用条件

只有符合下面这些条件的子查询才可以被转换为semi-join：

  • 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
  • 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
  • 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
  • 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。

比如：

SELECT ... FROM outer_tables 
    WHERE expr IN (SELECT ... FROM inner_tables ...) AND ...

-- 或者

SELECT ... FROM outer_tables 
    WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ...

不适用semi-join的情况

  • 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来
  • 使用NOT IN而不是IN的情况
  • 在SELECT子句中的IN子查询的情况
  • 子查询中包含GROUP BY、HAVING或者聚集函数的情况
  • 子查询中包含UNION的情况

MySQL有两个措施来优化不能转为semi-join查询的子查询：

  • 对于不相关子查询来说，可以尝试把它们物化之后再参与查询
  • 不管子查询是相关的还是不相关的，都可以把IN子查询尝试专为EXISTS子查询

⚠️注意：如果IN子查询不满足转换为semi-join的条件，又不能转换为物化表或者转换为物化表的成本太大，那么它就会被转换为EXISTS查询。

小结

  • 如果IN子查询符合转换为semi-join的条件，查询优化器会优先把该子查询为semi-join，然后再考虑下面5种执行半连接的策略中哪个成本最低：

    • Table pullout
    • DuplicateWeedout
    • LooseScan
    • Materialization
    • FirstMatch

    选择成本最低的那种执行策略来执行子查询。

  • 如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下面两种策略中找出一种成本更低的方式执行子查询：

    • 先将子查询物化之后再执行查询
    • 执行IN to EXISTS转换。

3.2.3 ANY / ALL子查询优化

如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行，比方说：

原始表达式 转换为
< ANY (SELECT inner_expr ...) < (SELECT MAX(inner_expr) ...)
> ANY (SELECT inner_expr ...) > (SELECT MIN(inner_expr) ...)
< ALL (SELECT inner_expr ...) < (SELECT MIN(inner_expr) ...)
> ALL (SELECT inner_expr ...) > (SELECT MAX(inner_expr) ...)

3.2.4 [NOT] EXISTS子查询的执行

如果[NOT] EXISTS子查询是不相关子查询，可以先执行子查询，得出该[NOT] EXISTS子查询的结果是TRUE还是FALSE，并重写原先的查询语句。如果[NOT] EXISTS子查询中如果可以使用索引的话，那查询速度会加快不少。

3.2.5 基于派生表的优化

把子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表：

SELECT * FROM  (
        SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a'
    ) AS derived_s1 WHERE d_key3 = 'a';

子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的结果就相当于一个派生表，这个表的名称是derived_s1，该表有两个列，分别是d_id和d_key3。

对于含有派生表的查询，MySQL提供了两种执行策略：

  • 把派生表物化：可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。当然，在对派生表进行物化时，MySQL使用了延迟物化的策略，也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。

    比如：

    SELECT * FROM (
            SELECT * FROM s1 WHERE key1 = 'a'
        ) AS derived_s1 INNER JOIN s2
        ON derived_s1.key1 = s2.key1
        WHERE s2.key2 = 1;

    如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到s1表中找出满足s1.key2 = 1的记录，如果找不到，说明参与连接的s1表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。

  • 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

    SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1;

    -- 等价于

    SELECT * FROM s1 WHERE key1 = 'a';


    SELECT * FROM (
            SELECT * FROM s1 WHERE key1 = 'a'
        ) AS derived_s1 INNER JOIN s2
        ON derived_s1.key1 = s2.key1
        WHERE s2.key2 = 1;

    -- 将派生表和外层查询的表合并，然后将派生表中的查询条件放到外层查询的搜索条件中：

    SELECT * FROM s1 INNER JOIN s2 
        ON s1.key1 = s2.key1
        WHERE s1.key1 = 'a' AND s2.key2 = 1;

    这样通过将外层查询和派生表合并的方式成功的消除了派生表，也就意味着没必要再付出创建和访问临时表的成本了。可是并不是所有带有派生表的查询都能被成功的和外层查询合并，当派生表中有这些语句就不可以和外层查询合并：

    • 聚集函数，比如MAX()、MIN()、SUM()等等
    • DISTINCT
    • GROUP BY
    • HAVING
    • LIMIT
    • UNION 或者 UNION ALL
    • 派生表对应的子查询的SELECT子句中含有另一个子查询

MySQL在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。

相关文章