第 14 章 不好看就要多整容——MySQL 基于规则的优化（内含关于子查询优化二三事儿）

沉登c
3 阅读6分钟

14.1 条件化简

14.1.1 移除不必要的括号

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

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

14.1.2 常量传递（constant_propagation）

a = 5 AND b > a

a = 5 AND b > 5

14.1.3 等值传递（equality_propagation）

a = b and b = c and c = 5

a = 5 and b = 5 and c = 5

14.1.4 移除没有的条件（trivial_condition_removal）

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

(a < 1 and TRUE) OR (a = 6 OR FALSE)

a < 1 OR a = 6

14.1.5 表达式计算

a = 5 + 1

a = 6

注意：如果某个列不是以单独的形式作为表达式的操作数时，比如出现在函数或某个更复杂的表达式中，优化器是不会尝试对这些表达式进行化简的。所以最好让索引列以单独的形式出现在表达式中。

14.1.6 HAVING 子句和 WHERE 子句的合并

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

14.1.7 常量表检测

常量表（constant tables）

  1. 查询的表中一条记录没有，或只有一条记录
  2. 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件

14.2 外连接消除

在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称为 空值拒绝（reject-NULL）。在被驱动表的 WHERE 子句符合空值拒绝的条件后，外连接和内连接可以相互转换。

14.3 子查询优化

14.3.1 子查询语法

子查询可能出现的位置：

  1. SELECT 子句中
  2. FROM 子句中
  3. WHERE 或 ON 子句中
  4. ORDER BY 子句中
  5. GROUP BY 子句中
14.3.1.1 按返回的结果集区分子查询
名称含义样例
标题子查询只返回一个单一值的子查询SELECT (SELECT m1 FROM t1 LIMIT 1);
行子查询返回一条记录的子查询，包含多列SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
列子查询查询出一个列的数据，包含多条数据SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
表子查询查询结果包含既多条记录，又包含很多个列SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
14.3.1.2 按与外层查询关系来区分子查询
名称含义样例
不相关子查询子查询可以单独运行出结果，而不依赖于外层查询的值
相关子查询子查询的执行需要依赖于外层查询的值SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
14.3.1.3 子查询在布尔表达式中的使用

  1. 使用 =、>、<、>=、<=、<>、!=、<=> 作为布尔表达式的操作符

    子查询只能是标量子查询或者行子查询。

    SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);

SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

  2. [NOT] IN / ANY / SOME / ALL 子查询

    • IN 或 NOT IN

      判断某个操作数在不在子查询结果集中

      SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);

    • ANY/SOME

      SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);

SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);

    • ALL

      SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);

  3. EXISTS 子查询

    仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是啥

    SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

TIPS:

在MySQL中，`<>`、`!=` 和 `<=>` 是比较运算符，它们的含义如下：

- `<>` 和 `!=` 都表示“不等于”。这两个运算符在功能上是等价的，可以互换使用。例如，`SELECT * FROM users WHERE age <> 30;` 和 `SELECT * FROM users WHERE age != 30;` 都会返回年龄不等于30的用户。

- `<=>` 是一个NULL-safe的等于运算符。它用于比较包含NULL值的列。如果比较的两个值都是NULL或者都是相同的值，那么 `<=>` 运算符返回1（真），否则返回0（假）。例如，`SELECT * FROM users WHERE age <=> NULL;` 会返回年龄为NULL的用户。这与常规的等于运算符（`=`）不同，因为在SQL中，NULL与任何值（包括NULL本身）的比较都会返回NULL，而不是真或假。
14.3.1.4 子查询语法注意事项
  1. 子查询必须用小括号括起来
  2. SELECT 子句中的子查询必须是标题子查询
  3. 在想要得到标题子查询或者行子查询但又不能保证子查询结果唯一时，应该使用 LIMIT 1 语句限制数量
  4. 对于 [NOT] IN / ANY / SOME / ALL 子查询来说，不允许有 LIMIT 语句
  5. 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询

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

14.3.2.1 小白们眼中子查询的执行方式

略

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

  1. 不相关子查询

    SELECT * FROM s1WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);
    • 先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)
    • 然后将上一步得到的结果作为外层查询的参数

  2. 相关子查询

    SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
    • 先从外层查询中获取一条记录
    • 然后将获取的记录带入子查询
    • 最后根据子查询的结果来检测外层查询是否成立，成立就把获取的那条记录加入到结果集，否则直接丢弃
    • 重复
14.3.2.3 IN 子查询优化

  1. 对于不相关的 IN 子查询

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

    物化表的提出

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

    物化表转连接

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

    将子查询转换为 semi-join（半连接）

    对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录。

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

    实现半连接的几种方式：

    • Table pullout（子查询中的表上拉）
    • DuplicateWeedout execution strategy（重复消除）
    • LooseScan execution strategy（松散索引扫描）
    • Semi-join Materialization execution strategy
    • FisrtMatch execution strategy（首次匹配）

  2. 对于相关 IN 子查询

    不能转换为物化表。使用半连接查询。

14.3.2.4 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) ...)
14.3.2.5 [NOT] EXISTS 子查询

先执行子查询

14.3.2.6 对于派生表的优化
SELECT * FROM (
    SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a'
) AS derived_s1 WHERE d_key3 = 'a';
  1. 物化派生表
  2. 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

14.4 总结

  1. MySQL 会对用户编写的 SQL 执行一些重写操作
  2. 在被驱动表的 WHERE 子句符合空值拒绝条件时，外连接和内连接可以相互转换
  3. 子查询按照不同的维度可以进行分类
  4. 子查询的优化策略及半连接查询
avatar
文章
阅读
粉丝