526互联

MySQL学习(10)基于规则的优化

发布时间 2023-10-27 15:04:44作者: 哪过晓得

前言

MySQL为了更高的执行效率，会将客户端发送的SQL语句进行优化。

条件化简

MySQL优化器会对SQL语句中的表达式进行简化处理，以提高执行效率。

  1. 移除不必要的括号。

  2. 常量传递。a = 5 AND b > a可优化为a = 5 AND b > 5。

  3. 移除没用的条件。优化器会移除掉明显为TRUE或FALSE的表达式。

  4. 表达式计算。a = 5 + 1可以优化为a = 6，注意列必须以单独的形式出现在表达式，否则优化器不会对它进行优化。

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

  6. 常量表检测。当查询的表中无记录或仅有一条记录，又或是使用主键等值匹配或唯一二级索引等值匹配为搜索条件来查询，也就是const查询，这两种方式查询的表叫常量表。查询优化器会首先执行常量表查询，然后把查询中设计该表的条件全部替换为常数，最后再分析其他表的查询成本。

外连接消除

在外连接中，优化器会先把右连接查询转换成左连接查询。当WHERE子句中包含被驱动表中的列不为NULL的条件称为空值拒绝。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接也就是一回事，可以相互转换。优化器可以通过评估表的不同连接顺序的成本，选出成本最低的连接顺序来执行查询。

例如：

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
# 转换为
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;

 

隐式的条件：

SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 = 2;
# 转换为
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 = 2;

 

 

子查询优化

什么是子查询

在一个查询语句中的某个位置存在另一个查询，这个出现在某个位置的查询就称为子查询，包含子查询的这个查询叫外层查询。子查询可以在外层查询的任意位置出现，必须使用小括号括起来。

  • 出现在SELECT子句

SELECT (SELECT m1 FROM t1 LIMIT 1);

 

image-20231015175142285

SELECT子句汇总的子查询必须是标量子查询，不可以出现多列或多行结果。子查询结果为多行时执行结果为“Subquery returns more than 1 row”，子查询结果为多列时执行结果为“Operand should contain 1 column(s)”。要保证子查询的结果只有一条记录，应该使用LIMIT 1语句限制记录数量。

  • 出现在FROM子句中

SELECT m FROM (SELECT m2 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;

 

image-20231015175118335

FROM子句中的子查询称为派生表，每一个派生表都必须有一个自己的别名，上面这条语句中AS t除去后的执行结果是“Every derived table must have its own alias”。

  • 出现在WHERE或ON子句中

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

 

image-20231015175816281

子查询的结果将作为外层查询的IN语句参数。

另外在ORDER BY子句和GROUP BY子句中也可以使用子查询，但是无实际意义。

子查询的返回结果

  • 标量子查询：返回一个单一值的子查询。

SELECT (SELECT m1 FROM t1 LIMIT 1);

 

image-20231015175142285

  • 行子查询：返回一条记录的子查询，且记录包含至少2列。

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

 

image-20231015181756823

  • 列子查询：返回结果包含多条记录，每条记录只有一列。

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

 

image-20231015182031546

  • 表子查询：返回结果包含多条记录，每条记录包含多个列。

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

 

image-20231015182122097

行子查询需要使用LIMIT 1保证记录仅有一条，列子查询需要在确保查询列表中只有1个列名。

子查询与外层查询的关系

  • 不相关子查询

子查询可以单独运行出结果，不依赖外层查询的值。

  • 相关子查询

子查询的执行需要依赖于外层查询的值

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

 

image-20231015212903070

子查询在布尔表达式的使用

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

外层查询使用这些操作符与子查询链接时，子查询只能是标量子查询或行子查询。

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

 

image-20231015203900685

  • [NOT]IN/ANY/SOME/ALL子查询

表达式解释
IN 等于列表中的任何一个值。
ANY 比较子查询返回的每个值的值，可以与=、>、>=、结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。有任何一个满足就返回true。
SOME 是any的别名，不常用，早期的SQL仅仅允许使用【any】，后来的版本为了和英语的【any】相区分，引入了【some】，同时还保留了【any】关键词。
ALL 比较子查询返回的每个值的值，可以与=、>、>=、结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。使用ALL进行比较时，全部都满足才返回true，也就是说全部数据中只要有一个不对就返回false，查询值为空。

IN或NOT IN：

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

 

image-20231015205357120

ANY或SOME：

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

 

image-20231015205515214

本质上就是m1大于(SELECT m2 FROM t2)中的最小值，即可返回TRUE。

等价于

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

 

另外，=ANY的含义与IN相同，都表示等于子查询结果集中任意一个值即可返回TRUE。

ALL：

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

 

相当于m1要大于所有子查询结果，也就是MAX(m2)

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

 

IN、ANY、SOME、ALL子查询中不允许有LIMIT语句。

  • EXISTS子查询

EXISTS放在子查询语句的前面，表示如果子查询存在结果，则返回TRUE，无结果则为FALSE，NOT EXISTS反之。子查询中的结果是什么并不关心，只关心结果有没有记录，所以子查询的SELECT列表是什么都无私哦为，SELECT 1也行。

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

 

image-20231015210409892

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE m1 IS NULL);

 

image-20231015210345398

注意：

子查询中使用ORDER BY子句、DISTINCT子句，以及没有聚集函数和HAVING子句的GROUP BY子句是无意义的。优化器会自动去掉。子查询的结果集合里，排序，唯一性都并不重要。

不允许在一条语句中增删改某个表的记录时，同时还对该表进行子查询。

子查询执行过程

不相关标量子查询、行子查询

SELECT * FROM single_table WHERE key1 = (SELECT key_part1 FROM single_table2 WHERE key3 = 'a' LIMIT 1);

 

image-20231016090040573

  1. 单独执行子查询(SELECT key_part1 FROM single_table2 WHERE key3 = 'a' LIMIT 1)。

  2. 把子查询的结果当作常数赋值给外层查询，再执行外层查询SELECT * FROM single_table WHERE key1 = ?。

子查询是一个标量子查询，执行结果是a，也就是外层查询的搜索条件为key1 = 'a'。

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

相关标量子查询、行子查询

SELECT * FROM single_table AS s1 WHERE key1 = (SELECT key_part1 FROM single_table2 AS s2 WHERE s1.key3 = s2.key3 LIMIT 1);

 

image-20231016094137805

  1. 先从外层查询获取一条记录。

  2. 根据这条记录找出子查询中涉及的值。s1.key3 = s2.key3，找到这条记录的s1种的key3。

  3. 将这条记录的值作为常数代入子查询，把子查询的结果与外层查询搜索条件key1 = ?判断是否成立，若是，则加入到结果集，否则丢弃。

  4. 重复1、2、3，直到外层查询获取不到记录。

IN子查询

什么是物化表

将子查询结果集中的记录保存到临时表的过程叫做物化，这个存储子查询结果集的临时表叫做物化表。物化表的记录已经被去重。

如果子查询结果集不大，会使用MEMORY存储引擎的临时表，并为该表建立哈希索引；如果子查询结果集很大，超过了系统变量tmp_table_size或max_heap_table_size的值，临时表会使用基于磁盘的存储引擎，并建立B+树索引。

注意：只有不相关子查询才可以被物化。

物化表转连接

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

 

这个查询中的子查询转换为物化表，假设物化表的名字是m_table，物化表的列卫m_val，该查询可以看作是外层查询表与物化表的内连接。

SELECT s1.* FROM single_table AS s1 INNER JOIN m_tabl ON key1 = m_val;

 

既然是内连接，计算执行成本时，需要考虑不同连接顺序，然后选择成本最低的连接顺序和访问方法。

子查询转半连接

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

 

对于s1表中的某条记录来说，只要在s2表中存在，既满足条件，并不关心出现几次。这种连接方式叫做半连接。半连接是MySQL内部实现的方法，不提供给客户端使用。

SELECT s1.* FROm single_table AS s1 SEMI JOIN single_tabl2 AS s2 ON s1.key1 = s2.common_field WHERE key3 = 'a';

 

半连接有几种实现方法：

  • Table pullout子查询中的表上拉

当子查询的查询列表处只有主键或唯一二级索引时，由于值不重复，可以直接把子查询中的表上拉到外层查询到FROM子句中，并把子查询中的搜索条件合并到外层查询到搜索条件中。

# 子查询到查询列表为主键或二级索引
SELECT * FROM single_table AS s1 WHERE key2 IN (SELECT key2 FROM single_table AS s2 WHERE key3 = 'a');
# 直接转换为内连接
SELECT s1.* FROM single_table AS s1 INNER JOIN single_table AS s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

 

  • Duplicate Weedout重复值消除

为了消除重复，建立一个临时表。每当某条s1表中的记录要加入结果集时，首先把这条记录的id加入到这个临时表中。

CREATE TABLE tmp (id INT PRIMARY KEY);

如果添加成功，说明这条记录第一次尝试加入结果集中，那就把这条记录添加到结果集落入过添加失败，表示不是第一次添加了，就舍弃。

  • LooseScan松散扫描

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

 

子查询的列为普通二级索引时，s2表中的索引记录中，值相同的二级索引记录，只需要取第一条记录的值到s1表中找匹配记录。

  • Semi_join Materialization半连接 物化

先把外层查询到IN子句中的不相关子查询进行物化，然后将外层查询的表与物化表进行连接。

  • FirstMatch首次匹配

先取一条外层查询中的记录，然后到子查询到表中寻找符合匹配条件的记录。如果找到一条，则将该外层查询的记录加入到最终的结果集并停止继续匹配；如果招呼道，就继续外层查询下一条记录。

半连接的适用条件

  • 子查询必须是与IN操作符组成的布尔表达式，且处于外层查询的WHERE或ON子句中，不可以使用NOT IN；

  • 外层查询的其他搜素条件必须使用AND与IN子句搜索条件连接，不可以使用OR；

  • 子查询必须是一个单一的查询，不能使用UNION连接；

  • 子查询不能包含GROUP BY、HAVING语句或者聚集函数。

如果子查询不能转为半连接查询，可以考虑一下两种方式：

  • 不相关子查询可以转换为物化表。

  • 将IN子查询转换为EXISTS子查询。

对于任意一个IN子查询，都可以转换EXISTS子查询。

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where);
# 转换为
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr = inner_expr);

 

有一个前提是IN子查询要出现在WHERE或ON子句中。

SELECT * FROM single_table AS s1 WHERE key1 IN (SELECT key3 FROM single_table2 AS s2 WHERE s1.common_field = s2.common_field) OR key2 > 1000;
# 转换
SELECT * FROM single_table AS s1 WHERE EXISTS (SELECT 1 FROM single_table AS s2 WHERE s1.common_field = s2.common_field AND s2.key3 = s1.key1) OR key2 > 1000;

 

转换成EXISTS子查询后，可以使用single_table2表的idx_ley3索引。

如果IN子查询不满足转换为半连接的条件，又不能转换为物化表，或者转换成物化表的成本太高，那么它就会被转换成EXISTS子查询。

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)...)

EXISTS子查询

如果EXISTS子查询或NOT EXISTS子查询是不相关子查询，可以先执行子查询，得出EXISTS子查询的结果布尔值，然后重写外层语句。

SELECT * FROM single_table AS s1 WHERE EXISTS (SELECT 1 FROM single_table AS s2 WHERE key1 = 'a');
# 先执行子查询
SELECT * FROM single_table AS s1 WHERE TRUE OR key2 > 100;
# 进一步优化
SELECT * FROM single_table WHERE TRUE;

 

如果EXISTS子查询是相关子查询，则不可以了。

派生表优化

把子查询放在外层查询的FROM子句后，这个子查询相当于一个派生表。对于派生表，有两种执行策略。

  • 物化派生表

将派生表的结果集写到一个内部的临时表，然后把这个物化表当作普通表一样参与查询。在实际查询过程中，只有真正使用到派生表时才会物化派生表，而不是在查询执行之前就先把派生表进行物化。例如：

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

 

在执行子查询之前，首先执行外层查询，找到s2.key2 = 1的所有记录，若没有符合条件的记录，则参与连接的s2表是空的，就不需要进一步执行物化子查询了，没有必要。

  • 将派生表与外层查询合并

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;

 

注意：当派生表中有下面函数或语句时，不可以语外层查询合并：

  • 聚集函数

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION/UNION ALL

  • 派生表对应的子查询的SELECT子句中含有另一个子查询

MySQL在处理带有派生表的语句时，优先尝试把派生表和外层查询进行合并；如果不行，再把派生表物化，然后执行查询。

哈哈