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

条件化简

移除不必要的括号

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

案例

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
优化为
(a = 5 and b = c) OR (a > c AND c < 5)

常量传递

有时候某个表达式是某个列和某个常量做等值匹配

案例
a = 5
当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中a的值替换为5，比如这样：
a = 5 AND b > a
就可以被转换为：
a = 5 AND b > 5

等值传递
有时候多个列之间存在等值匹配的关系
案例
a = b and b = c and c = 5
这个表达式可以被简化为：
a = 5 and b = 5 and c = 5

移除没用的条件
对于一些明显永远为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 ====>a = 6

如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，优化器是不会尝试对这些表达式进行化简的。
案例
ABS(a) > 5 或者：-a < -8

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

常量表检测
**MySQL**认为查询查询的表中一条记录没有，或者只有一条记录的表花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表 。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本
案例
```
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;
```
子查询优化

子查询类型

标量子查询
只返回一个单一值的子查询称之为标量子查询
行子查询
返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）
列子查询
查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）
表子查询
子查询的结果既包含很多条记录，又包含很多个列
不相关子查询
如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询
相关子查询
如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。
子查询在MySQL中是怎么执行的
标量子查询/行子查询

不相关子查询

-- 案例1
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2); 
-- 案例2
SELECT * FROM s1
WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

先单独执行(SELECT common_field FROM s2/SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询。
然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询SELECT * FROM s1 WHERE key1 IN/= (...)。

相关子查询

--  案例1
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);
--  案例2
SELECT * FROM s1 WHERE
key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

这个查询中的子查询中出现了s1.key2 = s2.key2/s1.key3 = s2.key3这样的条件，意味着该子查询的执行依赖着外层查询的值，这个查询的执行方式是这样的：

先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key2/s1.key3列的值，然后执行子查询。
最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
再次执行第一步，获取第二条外层查询中的记录，依次类推
IN子查询优化
临时表
子查询结果集不是太大的情况下， MysSQL会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立。
如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。
物化
把子查询结果集中的记录保存到临时表的过程称之为物化，它基于磁盘的存储引擎来保存结果，物化表的索引类型为B+树索引

案例
```
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
```
这种不相关的IN查询方式，MySQL不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里
注意:子查询进行物化之后再执行查询都会有建立临时表的成本
物化表转连接
```
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
```
当把子查询进行物化之后，假设子查询物化表的名称为materialized_table，该物化表存储的子查询结果集的列为m_val，那么这个查询其实可以从下边两种角度来看待：

从表s1的角度来看待，整个查询的意思其实是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集 (s1全量查询)
从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1列的值与该值相等的记录，那么就把这些记录加入到最终的结果集 (物化表全量查询)也就是说相当于表s1和子查询物化表materialized_table进行内连接：
SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;
将子查询转换为semi-join

案例
```
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
```
可以把这个查询理解成：对于s1表中的某条记录，如果能在s2表（准确的说是执行完WHERE s2.key3 = 'a'之后的结果集）中找到一条或多条记录，这些记录的common_field的值等于s1表记录的key1列的值，那么该条s1表的记录就会被加入到最终的结果集。
这个过程其实和把s1和s2两个表连接起来的效果很像：
```
SELECT s1.* FROM s1 INNER JOIN s2
ON s1.key1 = s2.common_field
WHERE s2.key3 = 'a';
```
只不过我们不能保证对于s1表的某条记录来说，在s2表（准确的说是执行完WHERE s2.key3 = ‘a’之后的结果集）中有多少条记录满足s1.key1 = s2.common_field这个条件，可以分三种情况讨论：

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

对于s1表的某条记录来说，由于我们只关心s2表中是否存在记录满足s1.key1 = s2.common_field这个条件，而不关心具体有多少条记录与之匹配，又因为有情况三的存在，上边所说的IN子查询和两表连接之间并不完全等价。但是将子查询转换为连接又真的可以充分发挥优化器的作用，所以设计MySQL提出了一个新概念 —- 半连接（英文名：semi-join）。
将s1表和s2表进行半连接的意思就是：对于s1表的某条记录来说，只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录。

注意

semi-join只是在MySQL内部采用的一种执行子查询的方式， MySQL并没有提供面向用户的semi-join语法.

不适用semi-join的情况

因为有时半连接查询会导致查询变慢，所以希望避免查询优化器优化成半连接，参考 案例实战-千万级用户场景下的sql调优

外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来(案例中的解决方案就是这个)
```
SELECT * FROM s1
WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
OR key2 > 100;
```

使用NOT IN而不是IN的情况

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

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

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

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

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

子查询中包含UNION的情况

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

EXISTS子查询的执行

如果[NOT] EXISTS子查询是不相关子查询，可以先执行子查询，得出该[NOT] EXISTS子查询的结果是TRUE还是FALSE，并重写原先的查询语句，比如对这个查询来说：
```
SELECT * FROM s1
WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a')
OR key2 > 100;
```
因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS子查询的结果为TRUE，那么接着优化器会重写查询为：
```
SELECT * FROM s1
WHERE TRUE OR key2 > 100;
```
进一步简化后就变成了：SELECT * FROM s1 WHERE TRUE;

对于相关的[NOT] EXISTS子查询来说，比如这个查询：

SELECT * FROM s1
WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field);

这个查询只能按照

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

不过如果[NOT] EXISTS子查询中如果可以使用索引的话，那查询速度也会加快不少

IN与EXISTS比较

select * from A where a_id in(select b_id from B)

以上查询使用了in语句，in()只执行一次，它查出B表中的所有id字段并缓存起来之后（物化临时表），检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。换句话说，使用IN关键字会遍历主表，同时还会物化子查询结果，再遍历物化表，（物化会增加成本）换成程序语言就是双层循环

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

select a.* from A a where exists(select 1 from B b where a.a_id=b.b_id)

以上查询使用了exists语句，exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是结果集中是否有记录，如果有则返回true，没有则返回false。换句话说，使用EXISTS会先查询主表，然后再判断主表记录是否满足exists条件，换成程序语言就是单层循环加判断

List resultSet=[];
Array A=(select * from A)

for(int i=0;i<A.length;i++) {
   //执行select 1 from B b where b.id=a.id是否有记录返回
   if(exists(A[i].id) {    
       resultSet.add(A[i]);
   }
}
return resultSet;

注意：这里的Exists是相关子查询，如果换成不相关子查询，MySQL会先执行子查询（参考 EXISTS子查询的执行 第一种情况)

总结

select * from A where a_id in(select b_id from B )

select a.* from A a where exists(select 1 from B b where a.a_id=b.b_id)

针对上面的两条SQL中EXISTS这种方式，是需要遍历A表中所有数据行N，并且判断EXISTS条件N次；并且如果B表的b_id有索引，EXISTS子查询可以使用连接关系（也就是b_id）上的索引；所以EXISTS方式适合 A表的结果集小，子查询的结果集大的情况；

一句话总结：子查询可以使用关联关系列上的索引，所以效率高，故内表大的适合使用exists;

IN是把外表A结果集和内表B结果集做semi_join连接，先查询内表B结果集，再把内表结果集与外表结果集匹配，对外表可以使用关系索引（也就是a_id列上的索引），遍历全部的B表，故外表大的使用in，可加快效率。

一句话总结：主查询可以使用关联关系列上的索引，所以效率高，故外表结果集合大的适合使用in;

MySQL学习笔记

MySQL查询优化

条件化简

移除不必要的括号

常量传递

等值传递

移除没用的条件

表达式计算

HAVING子句和WHERE子句的合并

常量表检测

子查询优化

子查询类型

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

标量子查询/行子查询

IN子查询优化

临时表

物化

物化表转连接

将子查询转换为semi-join

不适用semi-join的情况

EXISTS子查询的执行

IN与EXISTS比较

总结