优化器的功能
- 不改变语义的情况下，重写sql。重写后的sql更简单，更方便制定执行计划。
- 根据成本分析，制定执行计划。
对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写。
MySQL开发人员对优化器进行了大量的优化措施，主要干的也就是这个事儿。可见我们就算开发功底不咋样，写的烂sql经过优化器也能优化成还不错的样子进入InnoDB引擎。那么这些优化规则就让我们一起探索一下。
条件化简
我们编写的查询语句的搜索条件本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL 的查询优化器会为我们简化这些表达式。
移除不必要的括号
有时候表达式里有许多无用的括号，比如这样一条sql：
((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
优化器就会对其进行优化成这样：
(a = 5 and b = c) OR (a > c AND c < 5)
常量传递（constant_propagation）
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 = 5
移除没用的条件（trivial_condition_removal）
对于一些明显永远为 TRUE 或者 FALSE 的表达式，优化器会移除掉它们，比如这个表达式：
(a < 1 and b = b) OR (a = 6 OR 5 != 5)
很明显，b = b 这个表达式永远为 TRUE，5 != 5 这个表达式永远为 FALSE，所以简化后的表达式就是这样的：
(a < 1 and TRUE) OR (a = 6 OR FALSE)
可以继续被简化为
a < 1 OR a = 6
表达式计算
表达式在右边
在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如：
a = 5 + 1
因为 5 + 1 这个表达式只包含常量
所以就会被化简成：
a = 6
表达式在左边（索引GG）
但是这里需要注意的是，如果某个索引列列并不是以单独的形式作为表达式的操作数时，索引效果会失效。比如：
ABS(a) > 5
或者
-a < -8
因此，尽量避免这种写法。
常量表检测
MySQL 觉得下边这种查询运行的特别快：
使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。因为对应的查询IO成本与CPU成本基本上都是可控且很小的。因此，在优化器分析SQL时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。
SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2 WHERE table1.primary_key = 1;
那么这条sql的table1的查询条件table1.primary_key = 1，使得table1在这个查询中扮演着常量表的角色。因为他一定只对应着一条结果集。因此，优化器会先把table1.primary_key = 1作为条件，把table1中能替换成常量的值全部换成常量。衍变成了下面这种格式：
SELECT table1 表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 ON 常量（table1 表 column1 列的常量值） = table2.column2;
外连接消除 (外链接也能由优化器选择驱动，被驱动表了)
我们前边说过，内连接的驱动表和被驱动表的位置可以相互转换，而左（外） 连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优 化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。
但有这么一种情况，可以让优化器帮我我们选择外链接的。
要解释清除这个原因，我们还是继续展开讨论一下为什么内连接可以由优化器选择驱动表与被驱动表。
内连接与外连接的区别
内连接
SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;
外连接
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;
区别
可以发现，内连接就是将驱动表和被驱动表的结果均不为null的条件加入结果集。
而外连接就是驱动表的值在被驱动表中无法查询到结果后，但却想保留驱动表的结果。此时只能将该驱动表对应的被驱动表值全部填充NULL。
如何使外连接可以让优化器选择驱动表被驱动表
理论上来说，外连接的驱动，被驱动表都是由我们的开发人员指定的。那么想要优化器可以做到像对内连接一样自动区分驱动，被驱动表的效果，我们就要把外连接尽量往内连接靠拢。
想想上面的例子，如何能出现使用外连接，但被驱动表中不为NULL的效果呢？答案很简单。在SQL条件中加上被驱动的列不允许为NULL即可。
如下面这种写法：
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.n2 IS NOT NULL;
当然，正常人很少这么写SQL。
那么如果是这样呢？
SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;
直接限定被驱动表的列必须等于某个值，这简直再常见不过了。此时e2.m2=2,变相的表达了e2.m2 IS NOT NULL;
当然，如果where中有条件加到驱动表中，那当然是没有效果的，如下图：
我们把这种在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不 为 NULL 值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的 WHERE 子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好 处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种 连接顺序来执行查询。
子查询优化
什么是子查询
在一个查询语句 A 里的某个位置也可以有另一个查询语句 B，这个出现在 A 语句的某个位置中包含的查询 B 就被称为子查询，A 也被称之为外层查询。子查询可以在一个外层查询的各种位置出现，比如：
子查询出现的位置代表的含义
SELECT 子句中
也就是我们平时说的查询列表中，比如这样
SELECT (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;
这个写法可能不太常见。
子查询后边的 AS t 表明这个子 查询的结果就相当于一个名称为 t 的表，这个名叫 t 的表的列就是子查询结果中的列（m和n）。这个放在 FROM 子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，MySQL 把这种由子查询结果集组成的表称之为派生表。
WHERE 或 ON 子句中
最常用的子查询方式。如：
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)就是一个表子查询、此sql必须要在m1，n1都满足的条件下方可成立
按与外层查询关系来区分子查询
不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询。
相关子查询
如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。比如：
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);
其中n1属于外层查询e1表的列名，也就是说单靠e2表无法执行SELECT m2 FROM e2 WHERE n1 = n2这条sql。
[NOT] IN/ANY/SOME/ALL/EXISTS子查询
对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL 通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：
IN 或者 NOT IN
意思是用来判断某个操作数在不在由子查询结果集组成的集合中，比如下边的查询的意思是找出 e1 表中的某些记录，这些记录存在于子查询的结果集中：
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
ANY/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。因此等价于下面这条sql
SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);
另外，=ANY 相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和 IN 是相同的。
ALL
子查询结果集中所有的值和给定的操作数做比较操作比较结果为 TRUE，那么整个表达式的结果就为 TRUE，否则整个表达式的结 果就为 FALSE。比方说下边这个查询：
SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);
等价于下面这条sql
SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);
EXISTS 子查询
有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是个啥，可以用的EXISTS，如下：
SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);
对于子查询(SELECT 1 FROM e2)来说，我们并不关心这个子查询最后到底查询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓， 我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记录，那么整个 EXISTS 表达式的结果就为TRUE。
对于这个语句，如果为TRUE，则相当于查询全表。
SELECT * FROM e1
如果为false，则相当于返回空的结果集。
子查询语法注意事项
- 子查询必须用小括号扩起来。
- 在 SELECT 子句中的子查询必须是标量子查询，如果子查询结果集中有多个 列或者多个行，都不允许放在 SELECT 子句中，在想要得到标量子查询或者行子 查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来 限制记录数量。
- 对于[NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句
- 对于[NOT] IN/ANY/SOME/ALL 子查询来说，子查询出现 ORDER BY、DISTINCT、没有聚集函数以及 HAVING 子句的 GROUP BY这些语法是没有意义的，因为子查询的目的就是仅仅是服务外层查询而已，自己排序，分组没啥意义。
- 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。（就是update 某个表，然后这个语句又存在对这个表的子查询。）
子查询在 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 子句的条件是否成立， 如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
然后继续这个步骤反复执行，直到所有数据查询完毕。
这是我们理所当然的逻辑，但实际上是这样处理的么？
标量子查询、行子查询的执行方式（把in换成=）
不相关子查询
SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1);
和我们预计的流程是一样的，直接执行子查询，拿着唯一的结果去外层查询中执行即可。
相关子查询
SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE s1.order_no= s2.order_no LIMIT 1);
步骤和我们常规想法一致。大家可以按照常规思维的相关子查询逻辑记性推倒。
也就是谁标量子查询，MySQL优化器不需要做什么优化。
那感情，优化器区分相关子查询和不相关子查询也没什么区别嘛！别急，他们的概念将在IN查询中大放异彩。
MySQL 对 IN 子查询的优化
物化表
不相关的IN子查询中的条件过多怎么办
对于不相关的 IN 子查询，比如这样：
show index from order_exp;
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');
MySQL 为了优化 IN 子查询下了很大力气，并不像标量子查询那样理所当然。
对于不相关的 IN 子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率很高，但是如果单独执 行子查询后的结果集太多的话，就会导致这些问题：
- 结果集太多，可能内存中都放不下。
- 对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，由于order_note不是索引列，每个IN语句的条件都会全表扫描进行遍历。
结果集过多的处理方案
我们发现，IN子句中的结果集可能存在着大量的重复字段。这些字段对于获取最后的查询结果而言，都是浪费资源的无用功，因此，结果集过多的第一个处理方案，就是思考去重。
如果结果集中确实过大，导致即使结果去重后，内存存放仍然有压力，因此转存到磁盘当中。
外层查询全表扫描处理方案
order_note不是索引，你怎么滴还能让他不进行全表扫描不成？当然，直接加索引是不成的。但是我们可以通过物化表的方式对sql进行改造，由优化器再次判断是否使用全表扫描。
IN语句将子查询转换为物化表
当IN的结果集过大时，我们会将IN子句升级为物化表。升级流程如下：
- 该临时表的列就是子查询结果集中的列。
- 写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。
物化表是基于磁盘的么？不，这个表在不是特别大的时候是基于内存的。
一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。
如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引。
这样的转变有什么用呢？我们重新解读一下我们的sql，原sql如下：
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
思考一下，为什么可以进行这样的转换。因为现在需要将两个表都完全进行字段连接匹配。并且驱动表用了IN，那么肯定不需要像外链接一样持有被驱动表中不存在的数据。因此可以直接转换为内连接。
内连接的优化策略
转化成内连接之后就有意思了，查询优化器可以评估不同连接顺序需要的成 本是多少，选取成本最低的那种查询方式执行查询。我们分析一下上述查询中使 用外层查询的表 s1 和物化表 materialized_table 进行内连接的成本都是由哪几部分组成的：
s1作为驱动表
- 物化子查询时需要的成本
- 全表扫描 s1 表时的成本
- s1 表中的记录数量 × 通过 m_val = xxx 对 materialized_table 表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。
materialized_table作为驱动表
- 物化子查询时需要的成本
- 全表扫描物化表时的成本
- 物化表中的记录数量 × 通过 order_note= xxx 对 s1 表进行单表访问的成本 （这里的order_note 没有建立索引，因此访问速度大打折扣。这将被作为优化器的筛选因素。如果 order_note 列上建立了索引，这个步骤还是非常快的）。
MySQL 查询优化器会通过运算来选择上述成本更低的方案来执行查询
半连接查询 semi-join 的介绍
什么是半连接查询。首先，我们来看一下这个sql：
SELECT * FROM s1 WHERE order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');
这个sql查询的结果集，和s2表有关系么？答案是没有关系。子查询只是告诉我们，我们s1的order_note字段是否在子查询的结果集中是否存在罢了。因此，我们可以转换我们的sql如下：
SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.order_note = s2.order_note WHERE order_no= 'a';
注意，SEMI JOIN并不可以主动调用，而是在此处写一下帮助大家理解的。
我们发现我们真正的结果集只是查询s1.*，和s2没有半毛钱关系。那么发现了这个特性，我们每次在s2中查询出诸多的重复值，再与s1.order_note进行匹配，本身就是一个极大影响查询速度的操作。因此我们引入了半连接这个概念来加速处理 In 子句的处理速度。
注意：子查询order_note相同的结果集可能对应很多。这种半连接查询和优化可以和外链接查询一起记忆。
- 外链接查询的目的是把驱动表中存在，被驱动表中不存在的数据显示出来。
- 半连接查询的目的是把驱动表中存在，被驱动表中存在多条的数据去重显示，和物化表的目的一致。（当然得符合仅查询驱动表数据的前提）。
Table pullout （子查询中的表上拉）
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的 表上拉到外层查询的 FROM 子句中，并把子查询中的搜索条件合并到外层查询的 搜索条件中，比如假设 s2 中存在这个一个 key2 列，列上有唯一性索引：
SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');
此时的查询结果也是显而易见的。我们可以查出多个key2，但绝对不会出现重复的key2。于是。此sql就可以改写为：
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 order_note IN (SELECT order_no FROM s2 WHERE order_no> 'a' AND order_no< 'b');
在子查询中，对于 s2 表的访问可以使用到 order_no 列的索引，而恰好子查询的查询列表处就是 order_no 列，于是查询的结果集也是排好序的。因此我们可以利用这个特性做一些事情。这样在将该查询转换为半连接查询后的查询流程是这样的：
假设在 s2 表的 idx_order_no 索引中，值为'aa'的二级索引记录一共有 3 条，那么只需要取第一条的值到 s1 表中查找 s1.order_note= 'aa'的记录，如果能在 s1 表中 找到对应的记录，那么就把对应的记录加入到结果集。当我们再次遍历到后面两个s2表中的'aa'的记录时，直接跳过就好，不需要去打扰s2了。
我们就不需要依此类推，其他值相同的二级索引记录，也只需要取第一条记录的值到 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 查询的子查询优化
- 对于不相关子查询来说，可以尝试把它们物化之后再参与查询
SELECT * FROM s1 WHERE order_note NOT IN (SELECT order_note FROM s2 WHERE order_no= 'a')
如上面这个例子，先将子查询物化，然后再判断 order_note 是否在物化表的结果集中可以加快查询执行的速度。
- 不管子查询是相关的还是不相关的，都可以把 IN 子查询尝试转为 EXISTS 子查询。其实对于任意一个 IN 子查询来说，都可以被转为 EXISTS 子查询，通用的例子如下
为什么需要转换呢？比如：
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’;
其中order_note均不为s1，s2的索引。但我们发现s1表的order_no的列是索引列。
我们将其转换为EXISTS子查询后的sql如下：
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;
我们看到，直接把s1.order_no，扔到了子查询语句了。这样转为 EXISTS 子查询时便可能使用到 s1 表的 idx_order_no 索引了。
也就是说，当IN子查询时，当我们的子查询索引用不着的时候，就可以把外查询的条件丢到子查询中，并将IN子查询变为EXISTS子查询尝试使用索引。可以增加一个子查询中的条件。
IN子查询的结果类似（1，2，3，.......），EXISTS子查询类似（true，false，true，........）
IN子句的优化流程总结
第一步：尝试半连接优化
如果 IN 子查询符合转换为 semi-join 的条件，查询优化器会优先把该子查询 转换为 semi-join，然后再考虑下边 5 种执行半连接的策略中哪个成本最低：
- Table pullout
- DuplicateWeedout
- LooseScan
- Materialization
- FirstMatch
第二步：无法半连接优化，则变为EXISTS子查询
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;
直接执行子查询，EXISTS就可以看做要么全部为true，要么全部为false。假设子查询结果为true，优化器将会优化子查询为下面这样：
SELECT * FROM s1 WHERE TRUE OR order_no> ‘2021-03-22 18:28:28;
or再次取并集，优化成这样：
SELECT * FROM s1 WHERE TRUE;
相关子查询
对于相关的[NOT] EXISTS 子查询来说，比如这个查询：
SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.order_note = s2.order_note);
很不幸，只能把s1的order_note全部代入子查询中进行查询。但是如果子查询的被驱动中包含索引，那么查询效率也会大大提升。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
