mysql11

移除不必要的括号

• 有时表达式中有许多无用的括号，例如：

  ((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

  当这个表达式和其他列涉及时 a 表达式使用 AND 连接时，可以连接其他东西

  表达式中的 a 的值替换为 例如：

  a = 5 AND b > a

• 有时多列之间存在等值匹配关系，如：

  a = b and b = c and c = 5

  这种表达式可以简化为：

  a = 5 and b = 5 and c = 5

去除无用的条件（trivial_condition_removal）

• (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

• 但这里需要注意的是，如果一列不以单独的形式作为表达式操作

  例如，出现在函数中，出现在更复杂的表达式中，就像这样：

  ABS(a) > 5

  或者：

  -a < -8

  优化器不会试图简化这些表达式。我们前面说过，只有在搜索条件下

  在使用索引之前，索引列和常数可以通过某些操作符连接起来，所以如果可以的话，

  最好让索引列以单独的形式出现在表达式中。

常量表检测

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

• 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;

正如我们前面所说，内连接的驱动表和被驱动表的位置可以相互转换，而左（外） 连接到右（外）的驱动表和被驱动表是固定的。这导致内部连接可通过优化表的连接顺序来降低整体查询成本，而外部连接不能优化表的连接顺序。

外连接与内连接的本质区别在于：对于外部连接驱动表的记录，如果在驱动表中找不到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充；如果在驱动表中找不到内部连接驱动表的记录 ON 如果在句子中记录过滤条件，则该记录将被遗弃。查询效果如下：

• SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2;

  • m1	n1	m2	n2
    2	b	2	b
    3	c	3	c

• SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2;

  • m1	n1	m2	n2
    2	b	2	b
    3	c	3	c
    1	a	null	null

• 对于上述例子中的(左)外部连接，驱动表 e1 中 m1=1, n1='a不能记录 在被驱动表 e2 中找到符合 ON 子句条件 e1.m1 = e2.m2 因此，将此记录直接添加到结果集中，对应 e2 表的 m2 和 n2 列值均设置为 NULL。

因为一切都不符合 WHERE 条件记录不参与连接。只要我们在搜索 索条件中指定的关于驱动表相关列的值不是 NULL，外部连接在驱动表中 找不到符合ON最终的结果集被排除在子句条件的驱动表记录之外，即：在这种情况下：==外连接和内连接也就没有什么区别了！==例如，此查询：

• 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，只要隐含这个意思，比如:

  SELECT * FROM e1 LEFT JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;

• 在这个例子中，我们在 WHERE 子句中指定了被驱动表 e2 的 m2 列等于 ， 也就相当于间接的指定了 m2 列不为 NULL 值，所以上边的这个左（外）连接查询其实和下边这个内连接查询是等价的：

  SELECT * FROM e1 INNER JOIN e2 ON e1.m1 = e2.m2 WHERE e2.m2 = 2;

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

把子查询放在外层查询的 WHERE 子句或者 ON 子句中可能是我们最常用的 一种使用子查询的方式了，比如这样：

• SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

这个查询表明我们想要将(SELECT m2 FROM e2)这个子查询的结果作为外层 查询的 IN 语句参数，整个查询语句的意思就是我们想找 e1 表中的某些记录，这 些记录的 m1 列的值能在 e2 表的 m2 列找到匹配的值。

那些只返回一个单一值的子查询称之为标量子查询，比如这样：

• 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 * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

• SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之 为相关子查询。比如：

• 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 (子查询)

这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成 的集合中，比如下边的查询的意思是找出 e1 表中的某些记录，这些记录存在于 子查询的结果集中：

• SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

具体的语法形式如下：

• 操作数 比较符 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(子查询)

这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做比较 操作比较结果为 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 或者 NOT EXISTS 放在子查询语句前边，就像 这样：

• SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);

对于子查询(SELECT 1 FROM e2)来说，我们并不关心这个子查询最后到底查 询出的结果是什么，所以查询列表里填*、某个列名，或者其他啥东西都无所谓， 我们真正关心的是子查询的结果集中是否存在记录。也就是说只要(SELECT 1 FROM e2)这个查询中有记录，那么整个 EXISTS 表达式的结果就为 TRUE。

前提，对于in查询，in中数量小于200个，是精确统计页面，大于200个时，是桶index divive去统计的

想象中子查询的执行方式是这样的：

如果该子查询是不相关子查询，比如下边这个查询：

• 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 优化器的 执行方式并没有什么新鲜的。

物化表就是临时表，进内存还是进磁盘，取决于表大小，还有系统变量和堆大小可以控制

对于不相关的 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 查询优化器会通过运算来选择上述成本更低的方案来执行查询。

虽然将子查询进行物化之后再执行查询都会有建立临时表的成本，但是不管 怎么说，我们见识到了将子查询转换为连接的强大作用，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 这个条件，那么该记录会被多次加入最终的结果集。 而in里面是个集合，是去重的，不会有多个
• 对于 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_expr=inner_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 (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 子查询的结果是 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 来加快查询速度。

• 1.客户端发送一条查询（通讯协议<半双工>）给服务器。
• 2.服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的 结果。否则进入下一阶段。
• 3.服务器端通过解析器进行 SQL 解析，生成解析树，再预处理，得到进一步的解析树，再由查询优化器生成对应的查询执行计划。
• 4.MySQL 根据查询优化器生成的查询执行计划，调用查询存储引擎的（innodb、myisam、etc） API 来执行查询。
• 5.将结果返回给客户端。

一般来说，不需要去理解 MySQL 通信协议的内部实现细节，只需要大致理 解通信协议是如何工作的。MySQL 客户端和服务器之间的通信协议是“半双工” 的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由 客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。

这种协议让 MySQL 通信简单快速，但是也从很多地方限制了 MySQL。一个 明显的限制是，这意味着没法进行流量控制。一旦一端开始发生消息，另一端要 接收完整个消息才能响应它。这就像来回抛球的游戏﹔在任何时刻，只有一个人 能控制球，而且只有控制球的人才能将球抛回去（发送消息)。

客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句 很长的时候，参数 max_allowed_packet 就特别重要了。一旦客户端发送了请求， 它能做的事情就只是等待结果了。

相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服 务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单 地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收 完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地 断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上 LIMIT 限制（分页） 的原因。

换一种方式解释这种行为:当客户端从服务器取数据时，看起来是一个拉数 据的过程，但实际上是 MySQL 在向客户端推送数据的过程。客户端不断地接收 从服务器推送的数据，客户端也没法让服务器停下来。 多数连接 MySQL 的库函数都可以获得全部结果集并缓存到内存里，还可以 逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL 通常 需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接 收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放 相应的资源。

当使用库函数从 MySQL 获取数据时，其结果看起来都像是从 MySQL 服务器 获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问 题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会 花很多时间和内存来存储所有的结果集。

对于 Java 程序来说，很有可能发生 OOM，所以 MySQL 的 JDBC 里提供了 setFetchSize() 之类的功能，来解决这个问题：

• 1、当 statement 设置以下属性时，采用的是流数据接收方式，每次只从服 务器接收部份数据，直到所有数据处理完毕，不会发生 JVM OOM。 setResultSetType(ResultSet.TYPE_FORWARD_ONLY); setFetchSize(Integer.MIN_VALUE);

• 2、调用 statement 的 enableStreamingResults 方法，实际上 enableStreamingResults 方法内部封装的就是第 1 种方式。

• 3、设置连接属性 useCursorFetch=true (5.0 版驱动开始支持)，statement 以 TYPE_FORWARD_ONLY 打开，再设置 fetch size 参数，表示采用服务器端游标，每 次从服务器取 fetch_size 条数据。

  con = DriverManager.getConnection(url); 
  ps = (PreparedStatement) con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
  ps.setFetchSize(Integer.MIN_VALUE); 
  ps.setFetchDirection(ResultSet.FETCH_REVERSE); 
  rs = ps.executeQuery(); 
  while (rs.next()) { 
              ……实际的业务处理}
  

The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation is Copying to tmp table on disk.

该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁 盘表，则该操作期间的状态将为 Copying to tmp table on disk

select @@profiling;

set profiling=1;