【MySQL】 MySQL基于规则的优化

Metadata

title: 【MySQL】 MySQL基于规则的优化
date: 2023-06-23 16:19
tags:
  - 行动阶段/完成
  - 主题场景/数据存储
  - 笔记空间/KnowladgeSpace/ProgramSpace/BasicsSpace
  - 细化主题/数据存储
categories:
  - 数据存储
keywords:
  - 数据存储
description: 【MySQL】 MySQL基于规则的优化

概述

把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写

• 条件化简
  • 移除不必要的括号
  • 常量传递（constant_propagation）
  • 等值传递（equality_propagation）
  • 移除没用的条件（trivial_condition_removal）
  • 表达式计算
  • HAVING子句和WHERE子句的合并
  • 常量表检测
• 外连接消除
  • 在外连接查询中，指定的WHERE 子句中包含被驱动表中的列不为NULL 值的条件称之为空值拒绝
  • 符合空值拒绝的条件后，外连接和内连接可以相互转换。
  • 选出成本最低的那种连接顺序来执行查询
• 子查询优化
  • 按返回的结果集区分子查询
    • 标量子查询
    • 行子查询
    • 列子查询
    • 表子查询
  • 按与外层查询关系来区分子查询
    • 不相关子查询
    • 相关子查询
  • 标量子查询、行子查询的执行方式
    • 包含不相关的标量子查询或者行子查询的查询语句
      • 分别独立的执行外层查询和子查询
    • 相关的标量子查询或者行子查询
      • 先从外层查询中获取一条记录，本例中也就是先从s1 表中获取一条记录。
      • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1 表中获取的那条记录中找出s1.key3 列的值，然后执行子查询。
      • 最后根据子查询的查询结果来检测外层查询WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
      • 再次执行第一步，获取第二条外层查询中的记录，依次类推～
  • IN子查询优化
    • 物化表的提出
      • 将子查询结果集中的记录保存到临时表
    • 物化表转连接
    • 将子查询转换为semi-join
      • 只关心在s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配
      • 如果IN 子查询符合转换为semi-join 的条件，查询优化器会优先把该子查询为semi-join ，然后再考虑下边5种执行半连接的策略中哪个成本最低：
      • Table pullout （子查询中的表上拉）
        • 当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中
      • DuplicateWeedout execution strategy （重复值消除）
        • 可以建立一个临时表
      • LooseScan execution strategy （松散索引扫描）
        • 只取值相同的记录的第一条去做匹配操作的方式
      • FirstMatch execution strategy （首次匹配）
        • 先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录
      • semi-join的适用条件
        • 该子查询必须是和IN 语句组成的布尔表达式，并且在外层查询的WHERE 或者ON 子句中出现。
        • 外层查询也可以有其他的搜索条件，只不过和IN 子查询的搜索条件必须使用AND 连接起来。
        • 该子查询必须是一个单一的查询，不能是由若干查询由UNION 连接起来的形式。
        • 该子查询不能包含GROUP BY 或者HAVING 语句或者聚集函数。
        • … 还有一些条件比较少见，就不唠叨啦～
      • 不符合转换为semi-join 的条件
        • 先将子查询物化之后再执行查询
        • 执行IN to EXISTS 转换。
• ANY/ALL子查询优化
  
• [NOT] EXISTS子查询的执行
  • 可以先执行子查询，得出该[NOT] EXISTS 子查询的结果是TRUE 还是FALSE ，并重写原先的查询语句
• 对于派生表的优化
  • 对于含有派生表的查询， MySQL 提供了两种执行策略：
    • 最容易想到的就是把派生表物化。
    • 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式
• 当派生表中有这些语句就不可以和外层查询合并：
  • 聚集函数，比如MAX()、MIN()、SUM()啥的
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT
  • UNION 或者 UNION ALL
  • 派生表对应的子查询的SELECT 子句中含有另一个子查询
  • … 还有些不常用的情况就不多说了哈～

前言

设计MySQL 的大叔还是依据一些规则,竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写

条件化简

移除不必要的括号

优化器会把那些用不到的括号给干掉

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>((a = 5 AND b = c) OR ((a &gt; c) AND (c &lt; 5)))</p>
</div>

==>

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>(a = 5 and b = c) OR (a &gt; c AND c &lt; 5)</p>
</div>

常量传递（constant_propagation）

有时候某个表达式是某个列和某个常量做等值匹配. 当这个表达式和其他涉及列a 的表达式使用AND 连接起来时，可以将其他表达式中的a 的值替换为5

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>a = 5 AND b &gt; a</p>
</div>



<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>a = 5 AND b &gt; 5</p>
</div>

等值传递（equality_propagation）

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>a = b and b = c and c = 5</p>
</div>

==>

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>a = 5 and b = 5 and c = 5</p>
</div>

移除没用的条件（trivial_condition_removal）

对于一些明显永远为TRUE 或者FALSE 的表达式，优化器会移除掉它们

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>(a &lt; 1 and b = b) OR (a = 6 OR 5 != 5)</p>
</div>

==>

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>a &lt; 1 OR a = 6</p>
</div>

表达式计算

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>a = 5 + 1</p>
</div>

==>

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>a = 6</p>
</div>

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

HAVING子句和WHERE子句的合并

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

常量表检测

设计MySQL 的大叔觉得下边这两种查询运行的特别快：

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

把通过主键等值匹配或者唯一二级索引列等值匹配这两种方式查询的表称之为常量表（英文名： constant tables ）

优化器在分析一个查询语句时，

• 先首先执行常量表查询
• 然后把查询中涉及到该表的条件全部替换成常数
• 最后再分析其余表的查询成本

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>SELECT * FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2
WHERE table1.primary_key = 1;</p>
</div>

==>

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2
ON table1表column1列的常量值 = table2.column2;</p>
</div>

外连接消除

外连接和内连接的本质区别就是：
对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃

凡是不符合WHERE子句中条件的记录都不会参与连接
只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL ，那么外连接中在被驱动表中找不到符合ON 子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了！

我们把这种在外连接查询中，指定的WHERE 子句中包含被驱动表中的列不为NULL 值的条件称之为空值拒绝（英文名： reject-NULL ）。

在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

按返回的结果集区分子查询

因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型

• 标量子查询
  只返回一个单一值的子查询称之为标量子查询

• 行子查询
  返回一条记录的子查询，不过这条记录需要包含多个列

• 列子查询
  列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录

• 表子查询
  子查询的结果既包含很多条记录，又包含很多个列

按与外层查询关系来区分子查询

• 不相关子查询
  如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询

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

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

• 使用= 、> 、< 、>= 、<= 、<> 、!= 、<=> 作为布尔表达式的操作符
  这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录

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

  • IN 或者NOT IN
    • 操作数 [NOT] IN (子查询)
    • 判断某个操作数在不在由子查询结果集组成的集合中
  • ANY/SOME （ ANY 和SOME 是同义词）
    • 操作数 comparison_operator ANY/SOME(子查询)
    • ANY相当于判断子查询结果集中是否存在某个值和给定的操作数相等，它的含义和IN是相同的。
  • ALL
    • 操作数 comparison_operator ALL(子查询)

• EXISTS子查询

  • 仅仅需要判断子查询的结果集中是否有记录
  • [NOT] EXISTS (子查询)

子查询语法注意事项

• 子查询必须用小括号扩起来。不扩起来的子查询是非法的
• 在SELECT 子句中的子查询必须是标量子查询。
• 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1 语句来限制记录数量。
• 对于[NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有LIMIT 语句。
• ORDER BY 子句 子查询的结果其实就相当于一个集合，集合里的值排不排序一点儿都不重要
• DISTINCT 语句 集合里的值去不去重也没啥意义
• 没有聚集函数以及HAVING 子句的GROUP BY 子句。
• 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。

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

前期准备

CREATE TABLE single_table (
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

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

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

对于相关的标量子查询或者行子查询来说

• 先从外层查询中获取一条记录，本例中也就是先从s1 表中获取一条记录。
• 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1 表中获取的那条记录中找出s1.key3 列的值，然后执行子查询。
• 最后根据子查询的查询结果来检测外层查询WHERE 子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
• 再次执行第一步，获取第二条外层查询中的记录，依次类推～

IN子查询优化

物化表的提出

为了优化IN 子查询倾注了太多心血

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

写入临时表的过程

• 该临时表的列就是子查询结果集中的列。
• 写入临时表的记录会被去重。
• 一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory 存储引擎的临时表，而且会为该表建立哈希索引。

把这个将子查询结果集中的记录保存到临时表的过程称之为物化（英文名：Materialize ）

物化表转连接

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

当我们把子查询进行物化之后，假设子查询物化表的名称为materialized_table ，该物化表存储的子查询结果集的列为m_val ，那么这个查询其实可以从下边两种角度来看待

• 从表s1 的角度来看待，整个查询的意思其实是：对于s1 表中的每条记录来说，如果该记录的key1 列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。画个图表示一下就是这样：
  

• 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1 表中找到对应的key1 列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。画个图表示一下就是这样：

也就是说其实上边的查询就相当于表s1 和子查询物化表materialized_table 进行内连接

分析一下上述查询中使用外层查询的表s1 和物化表materialized_table 进行内连接的成本都是由哪几部分组成的：

• 如果使用s1 表作为驱动表的话，总查询成本由下边几个部分组成：
  • 物化子查询时需要的成本
  • 扫描s1 表时的成本
  • s1表中的记录数量 × 通过m_val = xxx 对materialized_table 表进行单表访问的成本（我们前边说过物化表中的记录是不重复的，并且为物化表中的列建立了索引，所以这个步骤显然是非常快的）。
• 如果使用materialized_table 表作为驱动表的话，总查询成本由下边几个部分组成：
  • 物化子查询时需要的成本
  • 扫描物化表时的成本
  • 物化表中的记录数量 × 通过key1 = xxx 对s1 表进行单表访问的成本（非常庆幸key1 列上建立了索引，所以这个步骤是非常快的）。

将子查询转换为semi-join

虽然将子查询进行物化之后再执行查询都会有建立临时表的成本，但是不管怎么说，我们见识到了将子查询转换为连接的强大作用

能不能不进行物化操作直接把子查询转换为连接呢？

我们可以分三种情况讨论：

• 情况一：对于s1 表的某条记录来说， s2 表中没有任何记录满足s1.key1 = s2.common_field 这个条件，那么该记录自然也不会加入到最后的结果集。
• 情况二：对于s1 表的某条记录来说， s2 表中有且只有记录满足s1.key1 = s2.common_field 这个条件，那么该记录会被加入最终的结果集。
• 情况三：对于s1 表的某条记录来说， s2 表中至少有2条记录满足s1.key1 = s2.common_field 这个条件，那么该记录会被多次加入最终的结果集。

由于我们只关心s2 表中是否存在记录满足s1.key1 = s2.common_field 这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，我们上边所说的IN 子查询和两表连接之间并不完全等价。

提出了一个新概念 — 半连接（英文名： semi-join ）。将s1 表和s2 表进行半连接的意思就是

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

怎么实现这种所谓的半连接呢？

Table pullout （子查询中的表上拉）

当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM 子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">原始</p><p>SELECT * FROM s1
WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');</p>
</div>

由于key2 列是s2 表的唯一二级索引列，所以我们可以直接把s2 表上拉到外层查询的FROM 子句中，并且把子查询中的搜索条件合并到外层查询的搜索条件中，上拉之后的查询就是这样的：

<style>.admonition {
  margin: 1.5625em 0;
  padding: .6rem;
  overflow: hidden;
  font-size: 1.00rem;
  page-break-inside: avoid;
  border-left: .3rem solid #42b983;
  border-radius: .3rem;
  box-shadow: 0 0.1rem 0.4rem rgba(0,0,0,.05), 0 0 0.05rem rgba(0,0,0,.1);
  background-color: #fafafa;
}

p.admonition-title {
  position: relative;
  margin: -.6rem -.6rem .8em -.6rem !important;
  padding: .4rem .6rem .4rem 2.5rem;
  font-weight: 700;
  background-color:rgba(66, 185, 131, .1);
}

.admonition-title::before {
  position: absolute;
  top: .9rem;
  left: 1rem;
  width: 12px;
  height: 12px;
  background-color: #42b983;
  border-radius: 50%;
  content: ' ';
}

.info>.admonition-title, .todo>.admonition-title {
  background-color: rgba(0,184,212,.1);
}

.warning>.admonition-title, .attention>.admonition-title, .caution>.admonition-title {
  background-color: rgba(255,145,0,.1);
}

.failure>.admonition-title, .missing>.admonition-title, .fail>.admonition-title, .error>.admonition-title {
  background-color: rgba(255,82,82,.1);
}

.admonition.info, .admonition.todo {
  border-color: #00b8d4;
}

.admonition.warning, .admonition.attention, .admonition.caution {
  border-color: #ff9100;
}

.admonition.failure, .admonition.missing, .admonition.fail, .admonition.error {
  border-color: #ff5252;
}

.info>.admonition-title::before, .todo>.admonition-title::before {
  background-color: #00b8d4;
  border-radius: 50%;
}

.warning>.admonition-title::before, .attention>.admonition-title::before, .caution>.admonition-title::before {
  background-color: #ff9100;
  border-radius: 50%;
}

.failure>.admonition-title::before,.missing>.admonition-title::before,.fail>.admonition-title::before,.error>.admonition-title::before{
  background-color: #ff5252;;
  border-radius: 50%;
}

.admonition>:last-child {
  margin-bottom: 0 !important;
}
</style>

<div class="admonition note"><p class="admonition-title">优化</p><p>SELECT s1.* FROM s1 INNER JOIN s2
ON s1.key2 = s2.key2
WHERE s2.key3 = 'a';</p>
</div>

主键或者唯一索引列中的数据本身就是不重复

DuplicateWeedout execution strategy （重复值消除）

s1 表中的某条记录可能在s2 表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表

LooseScan execution strategy （松散索引扫描）

在子查询中，对于s2 表的访问可以使用到key1 列的索引，而恰好子查询的查询列表处就是key1 列，这样在将该查询转换为半连接查询后，如果将s2 作为驱动表执行查询的话，那么执行过程就是这样

虽然是扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散索引扫描。

Semi-join Materialization execution strategy

先把外层查询的IN 子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join ，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

FirstMatch execution strategy （首次匹配）

先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程。

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 语句或者聚集函数。
• … 还有一些条件比较少见，就不唠叨啦～

不适用于semi-join的情况

• 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR 连接起来

```sql
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
OR key2 > 100;
```

• 使用NOT IN 而不是IN 的情况

```sql
SELECT * FROM s1
WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')
```

• 在SELECT 子句中的IN子查询的情况

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

• 子查询中包含GROUP BY 、HAVING 或者聚集函数的情况

```sql
SELECT * FROM s1
WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1);
```

• 子查询中包含UNION 的情况

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

MySQL 仍然留了两手绝活来优化不能转为semi-join 查询的子查询

• 对于不相关子查询来说，可以尝试把它们物化之后再参与查询

• 不管子查询是相关的还是不相关的，都可以把IN 子查询尝试转为EXISTS 子查询

小结一下

如果IN 子查询符合转换为semi-join 的条件，查询优化器会优先把该子查询为semi-join ，然后再考虑下边5种执行半连接的策略中哪个成本最低：

• Table pullout
• DuplicateWeedout
• LooseScan
• Materialization
• FirstMatch
  选择成本最低的那种执行策略来执行子查询。

如果IN 子查询不符合转换为semi-join 的条件，那么查询优化器会从下边两种策略中找出一种成本更低的方式执行子查询：

• 先将子查询物化之后再执行查询
• 执行IN to EXISTS 转换。

ANY/ALL子查询优化

[NOT] EXISTS子查询的执行

如果[NOT] EXISTS 子查询是不相关子查询，可以先执行子查询，得出该[NOT] EXISTS 子查询的结果是TRUE 还是FALSE ，并重写原先的查询语句

对于派生表的优化

我们前边说过把子查询放在外层查询的FROM 子句后，那么这个子查询的结果相当于一个派生表

对于含有派生表的查询， MySQL 提供了两种执行策略：

• 最容易想到的就是把派生表物化。
• 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

当派生表中有这些语句就不可以和外层查询合并：

• 聚集函数，比如MAX()、MIN()、SUM()啥的
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION 或者 UNION ALL
• 派生表对应的子查询的SELECT 子句中含有另一个子查询
• … 还有些不常用的情况就不多说了哈～