校园网站制作,php网站开发工资多少钱,建设网站公司需要哪些证件,营销型网站建设中坚站5.5. MySQL 的查询重写规则
对于一些执行起来十分耗费性能的语句#xff0c;MySQL 还是依据一些规则#xff0c;竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式#xff0c;这个过程也可以 被称作查询重写。
5.5.1. 条件化简
我们编写的查询语句的搜索条件…5.5. MySQL 的查询重写规则
对于一些执行起来十分耗费性能的语句MySQL 还是依据一些规则竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式这个过程也可以 被称作查询重写。
5.5.1. 条件化简
我们编写的查询语句的搜索条件本质上是一个表达式这些表达式可能比较繁杂或者不能高效的执行MySQL 的查询优化器会为我们简化这些表达式。
5.5.1.1. 移除不必要的括号
有时候表达式里有许多无用的括号比如这样 ((a 5 AND b c) OR ((a c) AND (c 5))) 看着就很烦优化器会把那些用不到的括号给干掉就是这样 (a 5 and b c) OR (a c AND c 5)
5.5.1.2. 常量传递constant_propagation
有时候某个表达式是某个列和某个常量做等值匹配比如这样
a 5
当这个表达式和其他涉及列 a 的表达式使用 AND 连接起来时可以将其他
表达式中的 a 的值替换为 5比如这样
a 5 AND b a
就可以被转换为
a 5 AND b 5
等值传递equality_propagation
有时候多个列之间存在等值匹配的关系比如这样
a b and b c and c 5
这个表达式可以被简化为
a 5 and b 5 and c 55.5.1.3. 移除没用的条件trivial_condition_removal
对于一些明显永远为 TRUE 或者 FALSE 的表达式优化器会移除掉它们比如这个表达式
(a 1 and b b) OR (a 6 OR 5 ! 5)
很明显b b 这个表达式永远为 TRUE5 ! 5 这个表达式永远为 FALSE所
以简化后的表达式就是这样的
(a 1 and TRUE) OR (a 6 OR FALSE)
可以继续被简化为
a 1 OR a 65.5.1.4. 表达式计算
在查询开始执行之前如果表达式中只包含常量的话它的值会被先计算出来比如这个 a 5 1 因为 5 1 这个表达式只包含常量所以就会被化简成 a 6 但是这里需要注意的是如果某个列并不是以单独的形式作为表达式的操作数时比如出现在函数中出现在某个更复杂表达式中就像这样ABS(a) 5 或者 -a -8 优化器是不会尝试对这些表达式进行化简的。我们前边说过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引所以如果可以的话 最好让索引列以单独的形式出现在表达式中。
5.5.1.5. 常量表检测
MySQL 觉得下边这种查询运行的特别快 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。 MySQL 觉得这两种查询花费的时间特别少少到可以忽略所以也把通过这两种方式查询的表称之为常量表英文名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;5.5.2. 外连接消除
我们前边说过内连接的驱动表和被驱动表的位置可以相互转换而左外连接和右外连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本而外连接却无法优化表的连接顺序。 我们之前说过外连接和内连接的本质区别就是对于外连接的驱动表的记录来说如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录那么该记录仍然会被加入到结果集中对应的被驱动表记录的各个字段使用 NULL 值填充而内连接的驱动表的记录如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录那么该记录会被舍弃。查询效果就是这样
SELECT * FROM e1 INNER JOIN e2 ON e1.m1 e2.m2;SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 e2.m2;对于上边例子中的左外连接来说由于驱动表 e1 中 m11, n1a’的记录无法在被驱动表 e2 中找到符合 ON 子句条件 e1.m1 e2.m2 的记录所以就直接把这条记录加入到结果集对应的 e2 表的 m2 和 n2 列的值都设置为 NULL。
因为凡是不符合 WHERE 子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为 NULL那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了也就是说在这种情况下外连接和内连接也就没有什么区别了比方说这个查询
mysql SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 e2.m2 WHERE e2.n2 IS
NOT NULL;由于指定了被驱动表 e2 的 n2 列不允许为 NULL所以上边的 e1 和 e2 表的左外连接查询和内连接查询是一样的。当然我们也可以不用显式的指定被驱动表的某个列 IS NOT NULL只要隐含的有这个意思就行了比方说这样
mysql SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 e2.m2 WHERE e2.m2 2;在这个例子中我们在 WHERE 子句中指定了被驱动表 e2 的 m2 列等于 2也就相当于间接的指定了 m2 列不为 NULL 值所以上边的这个左外连接查询其实和下边这个内连接查询是等价的
mysql SELECT * FROM e1 INNER JOIN e2 ON e1.m1 e2.m2 WHERE e2.m2 2;我们把这种在外连接查询中指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为空值拒绝英文名reject-NULL。在被驱动表的 WHERE子句符合空值拒绝的条件后外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本选出成本最低的那种连接顺序来执行查询。
5.5.3. 子查询优化
5.5.3.1. 子查询语法
在一个查询语句 A 里的某个位置也可以有另一个查询语句 B这个出现在 A语句的某个位置中的查询 B 就被称为子查询A 也被称之为外层查询。子查询可以在一个外层查询的各种位置出现比如 SELECT 子句中 也就是我们平时说的查询列表中比如这样
mysql SELECT (SELECT m1 FROM e1 LIMIT 1);其中的(SELECT m1 FROM e1 LIMIT 1)就是子查询。 FROM 子句中 比如
SELECT m, n FROM (SELECT m2 1 AS m, n2 AS n FROM e2 WHERE m2 2) AS
t;这个例子中的子查询是
(SELECT m2 1 AS m, n2 AS n FROM e2 WHERE m2 2)这里可以把子查询的查询结果当作是一个表子查询后边的 AS t 表明这个子查询的结果就相当于一个名称为 t 的表这个名叫 t 的表的列就是子查询结果中的列比如例子中表 t 就有两个列m 列和 n 列。这个放在 FROM 子句中的子查询本质上相当于一个表但又和我们平常使用的表有点儿不一样MySQL 把这种由子查询结果集组成的表称之为派生表。 WHERE 或 ON 子句中 把子查询放在外层查询的 WHERE 子句或者 ON 子句中可能是我们最常用的一种使用子查询的方式了比如这样
mysql SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);这个查询表明我们想要将(SELECT m2 FROM e2)这个子查询的结果作为外层查询的 IN 语句参数整个查询语句的意思就是我们想找 e1 表中的某些记录这些记录的 m1 列的值能在 e2 表的 m2 列找到匹配的值。 ORDER BY 子句、GROUP BY 子句中 虽然语法支持但没啥意义。 按返回的结果集区分子查询 因为子查询本身也算是一个查询所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型 标量子查询 那些只返回一个单一值的子查询称之为标量子查询比如这样
SELECT (SELECT m1 FROM e1 LIMIT 1);
或者这样
SELECT * FROM e1 WHERE m1 (SELECT MIN(m2) FROM e2);
SELECT * FROM e1 WHERE m1 (SELECT MIN(m2) FROM e2);这两个查询语句中的子查询都返回一个单一的值也就是一个标量。这些标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。 行子查询 顾名思义就是返回一条记录的子查询不过这条记录需要包含多个列只包含一个列就成了标量子查询了。比如这样
SELECT * FROM e1 WHERE (m1, n1) (SELECT m2, n2 FROM e2 LIMIT 1);其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一个行子查询整条语句的含义就是要从 e1 表中找一些记录这些记录的 m1 和 n1 列分别等于子查询结果中的m2 和 n2 列。 列子查询 列子查询自然就是查询出一个列的数据喽不过这个列的数据需要包含多条记录只包含一条记录就成了标量子查询了。比如这样
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);其中的(SELECT m2 FROM e2)就是一个列子查询表明查询出 e2 表的 m2 列 的值作为外层查询 IN 语句的参数。 表子查询 顾名思义就是子查询的结果既包含很多条记录又包含很多个列比如这样
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);其中的(SELECT m2, n2 FROM e2)就是一个表子查询这里需要和行子查询对比一下行子查询中我们用了 LIMIT 1 来保证子查询的结果只有一条记录表子 查询中不需要这个限制。
按与外层查询关系来区分子查询
不相关子查询 如果子查询可以单独运行出结果而不依赖于外层查询的值我们就可以把 这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不 相关子查询。相关子查询 如果子查询的执行需要依赖于外层查询的值我们就可以把这个子查询称之 为相关子查询。比如
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 n2);例子中的子查询是(SELECT m2 FROM e2 WHERE n1 n2)可是这个查询中有一个搜索条件是 n1 n2别忘了 n1 是表 e1 的列也就是外层查询的列也就是说子查询的执行需要依赖于外层查询的值所以这个子查询就是一个相关子查询。 [NOT] IN/ANY/SOME/ALL 子查询 对于列子查询和表子查询来说它们的结果集中包含很多条记录这些记录相当于是一个集合所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了MySQL 通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式 IN 或者 NOT IN 具体的语法形式如下 操作数 [NOT] IN (子查询) 这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成 的集合中比如下边的查询的意思是找出 e1 表中的某些记录这些记录存在于 子查询的结果集中
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);ANY/SOMEANY 和 SOME 是同义词 具体的语法形式如下 操作数 比较符 ANY/SOME(子查询) 这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数 做比较操作比较结果为 TRUE那么整个表达式的结果就为 TRUE否则整个表达式的结果就为 FALSE。比方说下边这个查询
SELECT * FROM e1 WHERE m1 ANY(SELECT m2 FROM e2);这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说如果子查询(SELECT m2 FROM e2)的结果集中存在一个小于 m1 列的值那么整个布尔表达式的值就是 TRUE否则为 FALSE也就是说只要 m1 列的值大于子查询结果集中最小的值整个表达式的结果就是 TRUE所以上边的查询本质上等价于这个查询
SELECT * FROM e1 WHERE m1 (SELECT MIN(m2) FROM e2);另外ANY 相当于判断子查询结果集中是否存在某个值和给定的操作数相等它的含义和 IN 是相同的。 ALL 具体的语法形式如下 操作数 比较操作 ALL(子查询) 这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较 操作比较结果为 TRUE那么整个表达式的结果就为 TRUE否则整个表达式的结 果就为 FALSE。比方说下边这个查询
SELECT * FROM e1 WHERE m1 ALL(SELECT m2 FROM e2);这个查询的意思就是对于 e1 表的某条记录的 m1 列的值来说如果子查询 (SELECT m2 FROM e2)的结果集中的所有值都小于 m1 列的值那么整个布尔表达式的值就是 TRUE否则为 FALSE也就是说只要 m1 列的值大于子查询结果集中最大的值整个表达式的结果就是 TRUE所以上边的查询本质上等价于这个查询
SELECT * FROM e1 WHERE m1 (SELECT MAX(m2) FROM e2);EXISTS 子查询 有的时候我们仅仅需要判断子查询的结果集中是否有记录而不在乎它的记录具体是个啥可以使用把 EXISTS 或者 NOT EXISTS 放在子查询语句前边就像这样
SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);对于子查询(SELECT 1 FROM e2)来说我们并不关心这个子查询最后到底查询出的结果是什么所以查询列表里填*、某个列名或者其他啥东西都无所谓我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记录那么整个 EXISTS 表达式的结果就为 TRUE。 子查询语法注意事项 子查询必须用小括号扩起来。 在 SELECT 子句中的子查询必须是标量子查询如果子查询结果集中有多个列或者多个行都不允许放在 SELECT 子句中在想要得到标量子查询或者行子查询但又不能保证子查询的结果集只有一条记录时应该使用 LIMIT 1 语句来 限制记录数量。
对于[NOT] IN/ANY/SOME/ALL 子查询来说子查询中不允许有 LIMIT 语句而且这类子查询中 ORDER BY 子句、DISTINCT 语句、没有聚集函数以及 HAVING子句的 GROUP BY 子句没有什么意义。因为子查询的结果其实就相当于一个集合集合里的值排不排序等一点儿都不重要。
不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。
5.5.3.2. 子查询在 MySQL 中是怎么执行的
想象子查询的执行方式 想象中子查询的执行方式是这样的 如果该子查询是不相关子查询比如下边这个查询
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2);先单独执行(SELECT order_note FROM s2)这个子查询。然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询
SELECT * FROM s1 WHERE order_note IN (...)。如果该子查询是相关子查询比如下边这个查询
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
s1.order_no s2.order_no);这个查询中的子查询中出现了 s1.order_no s2.order_no 这样的条件意味着该子查询的执行依赖着外层查询的值先从外层查询中获取一条记录本例中也就是先从 s1 表中获取一条记录然后执行子查询。 最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立如果成立就把外层查询的那条记录加入到结果集否则就丢弃。、
再次执行第一步获取第二条外层查询中的记录依次类推。 但真的是这样吗其实 MySQL 用了一系列的办法来优化子查询的执行大部分情况下这些优化措施其实挺有效的下边我们来看看各种不同类型的子查询具体是怎么执行的。 标量子查询、行子查询的执行方式 对于不相关标量子查询或者行子查询来说它们的执行方式很简单比方说 下边这个查询语句
SELECT * FROM s1 WHERE order_note (SELECT order_note FROM s2 WHERE
key3 a LIMIT 1);它的执行方式和我们前面想象的一样先单独执行(SELECT order_note FROM s2 WHERE key3 ‘a’ LIMIT 1)这个子查询。然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询 SELECT * FROM s1 WHERE order_note …。 也就是说对于包含不相关的标量子查询或者行子查询的查询语句来说MySQL 会分别独立的执行外层查询和子查询就当作两个单表查询就好了。
对于相关的标量子查询或者行子查询来说比如下边这个查询
SELECT * FROM s1 WHERE
order_note (SELECT order_note FROM s2 WHERE s1.order_no
s2.order_no LIMIT 1);事情也和我们前面想象的一样它的执行方式就是这样的
先从外层查询中获取一条记录本例中也就是先从 s1 表中获取一条记录。然后从上一步骤中获取的那条记录中找出子查询中涉及到的值本例中就是从 s1 表中获取的那条记录中找出 s1.order_no 列的值然后执行子查询。最后根据子查询的查询结果来检测外层查询 WHERE 子句的条件是否成立如果成立就把外层查询的那条记录加入到结果集否则就丢弃。再次执行第一步获取第二条外层查询中的记录依次类推。 也就是说对于两种使用标量子查询以及行子查询的场景中MySQL 优化器的执行方式并没有什么新鲜的。 MySQL 对 IN 子查询的优化 物化表 对于不相关的 IN 子查询比如这样
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
order_no a);我们最开始的感觉就是这种不相关的 IN 子查询和不相关的标量子查询或者 行子查询是一样一样的都是把外层查询和子查询当作两个独立的单表查询来对待。但是 MySQL 为了优化 IN 子查询下了很大力气所以整个执行过程并不像我们想象的那么简单。
对于不相关的 IN 子查询来说如果子查询的结果集中的记录条数很少那么把子查询和外层查询分别看成两个单独的单表查询效率很高但是如果单独执行子查询后的结果集太多的话就会导致这些问题 1、结果集太多可能内存中都放不下。 2、对于外层查询来说如果子查询的结果集太多那就意味着 IN 子句中的参数特别多这就导致无法有效的使用索引只能对外层查询进行全表扫描。 在对外层查询执行全表扫描时由于 IN 子句中的参数太多这会导致检测一条记录是否符合和 IN 子句中的参数匹配花费的时间太长。 比如说 IN 子句中的参数只有两个
SELECT * FROM tbl_name WHERE column IN (a, b);这样相当于需要对 tbl_name 表中的每条记录判断一下它的 column 列是否符合 column a OR column b。在 IN 子句中的参数比较少时这并不是什么问题 如果 IN 子句中的参数比较多时比如这样
SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);那么这样每条记录需要判断一下它的 column 列是否符合 column a OR column b OR column c OR …这样性能耗费可就多了。 MySQL 的改进是不直接将不相关子查询的结果集当作外层查询的参数而是 将该结果集写入一个临时表里。写入临时表的过程是这样的 1、该临时表的列就是子查询结果集中的列。 2、写入临时表的记录会被去重临时表也是个表只要为表中记录的所有 列建立主键或者唯一索引。 一般情况下子查询结果集不会大的离谱所以会为它建立基于内存的使用Memory 存储引擎的临时表而且会为该表建立哈希索引。
如果子查询的结果集非常大超过了系统变量 tmp_table_size 或者 max_heap_table_size临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录索引类型也对应转变为 B树索引。
MySQL 把这个将子查询结果集中的记录保存到临时表的过程称之为物化英文名Materialize。为了方便起见我们就把那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引基于内存的物化表有哈希索引基于磁盘的有 B树索引通过索引执行 IN 语句判断某个操作数在不在子查询结果集中变得非常快从而提升了子查询语句的性能。 物化表转连接 事情到这就完了我们还得重新审视一下最开始的那个查询语句
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
order_no a);当我们把子查询进行物化之后假设子查询物化表的名称为 materialized_table该物化表存储的子查询结果集的列为 m_val那么这个查询就相当于表 s1 和子查询物化表 materialized_table 进行内连接
SELECT s1.* FROM s1 INNER JOIN materialized_table ON order_note m_val;转化成内连接之后就有意思了查询优化器可以评估不同连接顺序需要的成本是多少选取成本最低的那种查询方式执行查询。我们分析一下上述查询中使用外层查询的表 s1 和物化表 materialized_table 进行内连接的成本都是由哪几部分组成的
1、如果使用 s1 表作为驱动表的话总查询成本由下边几个部分组成 物化子查询时需要的成本 扫描 s1 表时的成本 s1 表中的记录数量 × 通过 m_val xxx 对 materialized_table 表进行单表访问的成本我们前边说过物化表中的记录是不重复的并且为物化表中的列建立了索引所以这个步骤显然是非常快的。
2、如果使用 materialized_table 表作为驱动表的话总查询成本由下边几个部分组成 物化子查询时需要的成本 扫描物化表时的成本 物化表中的记录数量 × 通过 order_note xxx 对 s1 表进行单表访问的成本如果 order_note 列上建立了索引这个步骤还是非常快的。 MySQL 查询优化器会通过运算来选择上述成本更低的方案来执行查询。 将子查询转换为 semi-join 虽然将子查询进行物化之后再执行查询都会有建立临时表的成本但是不管怎么说我们见识到了将子查询转换为连接的强大作用MySQL 继续开脑洞能不能不进行物化操作直接把子查询转换为连接呢让我们重新审视一下上边的 查询语句
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
order_no a);我们可以把这个查询理解成对于 s1 表中的某条记录如果我们能在 s2 表准确的说是执行完 WHERE s2.order_no a’之后的结果集中找到一条或多条记录这些记录的 order_note 的值等于 s1 表记录的 order_note 列的值那么该条s1 表的记录就会被加入到最终的结果集。这个过程其实和把 s1 和 s2 两个表连接 起来的效果很像
SELECT s1.* FROM s1 INNER JOIN s2
ON s1.order_note s2.order_note
WHERE s2.order_no a;只不过我们不能保证对于 s1 表的某条记录来说在 s2 表准确的说是执行完 WHERE s2.order_no a’之后的结果集中有多少条记录满足 s1.order_no s2.order_no 这个条件不过我们可以分三种情况讨论
情况一对于 s1 表的某条记录来说s2 表中没有任何记录满足 s1.order_note s2.order_note 这个条件那么该记录自然也不会加入到最后的结果集。情况二对于 s1 表的某条记录来说s2 表中有且只有 1 条记录满足 s1.order_note s2.order_note 这个条件那么该记录会被加入最终的结果集。情况三对于 s1 表的某条记录来说s2 表中至少有 2 条记录满足 s1.order_note s2.order_note 这个条件那么该记录会被多次加入最终的结果集。 对于 s1 表的某条记录来说由于我们只关心 s2 表中是否存在记录满足 s1.order_no s2.order_note 这个条件而不关心具体有多少条记录与之匹配又因为有情况三的存在我们上边所说的 IN 子查询和两表连接之间并不完全等价。 但是将子查询转换为连接又真的可以充分发挥优化器的作用所以 MySQL 在这里提出了一个新概念 — 半连接英文名semi-join。
将 s1 表和 s2 表进行半连接的意思就是对于 s1 表的某条记录来说我们只关心在 s2 表中是否存在与之匹配的记录而不关心具体有多少条记录与之匹配最终的结果集中只保留 s1 表的记录。为了让大家有更直观的感受我们假设 MySQL 内部是这么改写上边的子查询的
SELECT s1.* FROM s1 SEMI JOIN s2
ON s1.order_note s2.order_note
WHERE order_no a;注意 semi-join 只是在 MySQL 内部采用的一种执行子查询的方式MySQL 并没有提供面向用户的 semi-join 语法。 概念是有了怎么实现这种所谓的半连接呢MySQL 准备了好几种办法。 Table pullout 子查询中的表上拉 当子查询的查询列表处只有主键或者唯一索引列时可以直接把子查询中的表上拉到外层查询的 FROM 子句中并把子查询中的搜索条件合并到外层查询的搜索条件中比如假设 s2 中存在这个一个 key2 列列上有唯一性索引
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;为啥当子查询的查询列表处只有主键或者唯一索引列时就可以直接将子查询转换为连接查询呢因为主键或者唯一索引列中的数据本身就是不重复的嘛 所以对于同一条 s1 表中的记录你不可能找到两条以上的符合 s1.key2 s2.key2的记录。 DuplicateWeedout execution strategy 重复值消除 对于这个查询来说
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE
order_no a);转换为半连接查询后s1 表中的某条记录可能在 s2 表中有多条匹配的记录 所以该条记录可能多次被添加到最后的结果集中为了消除重复我们可以建立一个临时表比方说这个临时表长这样
CREATE TABLE tmp (
id PRIMARY KEY
);这样在执行连接查询的过程中每当某条 s1 表中的记录要加入结果集时 就首先把这条记录的 id 值加入到这个临时表里如果添加成功说明之前这条 s1 表中的记录并没有加入最终的结果集现在把该记录添加到最终的结果集 如果添加失败说明之前这条 s1 表中的记录已经加入过最终的结果集这里直接把它丢弃就好了这种使用临时表消除 semi-join 结果集中的重复值的方式称 之为 DuplicateWeedout。 LooseScan execution strategy 松散扫描 大家看这个查询
SELECT * FROM s1 WHERE order_note IN (SELECT order_no FROM s2 WHERE
order_no a AND order_no b);在子查询中对于 s2 表的访问可以使用到 order_no 列的索引而恰好子查询的查询列表处就是 order_no 列这样在将该查询转换为半连接查询后如果 将 s2 作为驱动表执行查询的话那么执行过程就是这样
在 s2 表的 idx_order_no 索引中值为’aa’的二级索引记录一共有 3 条那么只需要取第一条的值到 s1 表中查找 s1.order_note aa’的记录如果能在 s1 表中找到对应的记录那么就把对应的记录加入到结果集。依此类推其他值相同的二级索引记录也只需要取第一条记录的值到 s1 表中找匹配的记录这种虽然是扫描索引但只取值相同的记录的第一条去做匹配操作的方式称之为松散扫描。
当然除了我们上面所说的MySQL 中的半连接方式还有好几种比如 Semi-join Materializationa 半连接物化、FirstMatch execution strategy 首次匹配等等我们就不更深入的讨论了。
semi-join 的适用条件 当然并不是所有包含 IN 子查询的查询语句都可以转换为 semi-join只有形如这样的查询才可以被转换为 semi-join
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 该子查询必须是和 IN 语句组成的布尔表达式并且在外层查询的 WHERE 或者 ON 子句中出现。 外层查询也可以有其他的搜索条件只不过和 IN 子查询的搜索条件必须使用 AND 连接起来。
该子查询必须是一个单一的查询不能是由若干查询由 UNION 连接起来的形式。 该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数。 MySQL 对不能转为 semi-join 查询的子查询优化 1、对于不相关子查询来说可以尝试把它们物化之后再参与查询 比如我们上边提到的这个查询
SELECT * FROM s1 WHERE order_note NOT IN (SELECT order_note FROM s2
WHERE order_no a)先将子查询物化然后再判断 order_note 是否在物化表的结果集中可以加快查询执行的速度。 2、不管子查询是相关的还是不相关的都可以把 IN 子查询尝试转为 EXISTS 子查询 其实对于任意一个 IN 子查询来说都可以被转为 EXISTS 子查询通用的例子如下
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)可以被转换为
EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND
outer_exprinner_expr)为啥要转换呢这是因为不转换的话可能用不到索引比方说下边这个查询
SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where
s1.order_note s2.order_note) OR insert_time ‘2021-03-22 18:28:28’;这个查询中的子查询是一个相关子查询而且子查询执行的时候不能使用到索引但是将它转为 EXISTS 子查询后却可以使用到索引
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 where s1.order_note
s2.order_note AND s2.order_no s1.order_no) OR insert_time ‘2021-03-22
18:28:28’00;转为 EXISTS 子查询时便可能使用到 s2 表的 idx_order_no 索引了。
需要注意的是如果 IN 子查询不满足转换为 semi-join 的条件又不能转换为物化表或者转换为物化表的成本太大那么它就会被转换为 EXISTS 查询。 在 MySQL5.5 以及之前的版本没有引进 semi-join 和物化的方式优化子查询时优化器都会把 IN 子查询转换为 EXISTS 子查询所以当时好多声音都是建议大家 把子查询转为连接不过随着 MySQL 的发展最近的版本中引入了非常多的子查询优化策略内部的转换工作优化器会为大家自动实现。
小结
如果 IN 子查询符合转换为 semi-join 的条件查询优化器会优先把该子查询转换为 semi-join然后再考虑下边 5 种执行半连接的策略中哪个成本最低 Table pullout DuplicateWeedout LooseScan Materialization FirstMatch 选择成本最低的那种执行策略来执行子查询。 如果 IN 子查询不符合转换为 semi-join 的条件那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询 先将子查询物化之后再执行查询 执行 IN to 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) ...)[NOT] EXISTS 子查询的执行 如果[NOT] EXISTS 子查询是不相关子查询可以先执行子查询得出该[NOT]EXISTS 子查询的结果是 TRUE 还是 FALSE并重写原先的查询语句比如对这个 查询来说
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE expire_time a)
OR order_no ‘2021-03-22 18:28:28’0;因为这个语句里的子查询是不相关子查询所以优化器会首先执行该子查询假设该 EXISTS 子查询的结果为 TRUE那么接着优化器会重写查询为
SELECT * FROM s1 WHERE TRUE OR order_no ‘2021-03-22 18:28:28’0;进一步简化后就变成了
SELECT * FROM s1 WHERE TRUE;对于相关的[NOT] EXISTS 子查询来说比如这个查询
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.order_note
s2.order_note);很不幸这个查询只能按照我们想象中的那种执行相关子查询的方式来执行。 不过如果[NOT] EXISTS 子查询中如果可以使用索引的话那查询速度也会加快不少比如
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE
s1.order_note s2.order_no);上边这个 EXISTS 子查询中可以使用 idx_order_no 来加快查询速度。
