MySQL内核查询优化规则

        MySQL在执行SQL语句的过程中会有很多的优化，比如：索引下推，回表中的MRR，索引合并等。但是在执行之前MySQL会依据一些规则，把SQL语句进行转换，以期可以达到某种可以高效执行的形式，这个过程可以被称作查询重写或者查询优化，很多时候这些优化可以由我们自行完成以减少MySQL的优化时间。

        一般都是在查询引擎层优化，我们可以通过了解MySQL的优化方式，借此自行进行优化，避免给MySQL增加优化的成本。

        一、条件化简

        我们的查询语句的搜索条件，本质上是一个表达式，这些表达式可能比较繁杂，或者不能高效的执行，MySQL的查询优化器会为我们简化这些表达式。

        ① 移除表达式中不必要的括号 

        ② 移除没用的条件（有些sql里面的条件永远是true 或者false会被去除，例：1=1，对性能没用，作用是用于防止sql注入。）

         ③ 常量传递，a=5 and b>a ,就是 b>5;a =b and b=c and c=1,就是a =1 and b=1 and c=1;

         ④表达式计算，在查询开始执行之前，如果表达式之中只包含常量的话，它的值会被先计算出来，a=5+1,就是a=6

         ⑤常量表检测，使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表，MySQL觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（constant tables）,优化器在分析一个查询语句时，先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本

        二、外连接消除

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

 可以通过where子句来排除 为null的数据，凡是不符合where子句中条件的记录都不会参与连接。

 

         此时，结果其实跟inner join 没差别，此时这个外连接查询 跟内连接查询时等价的。我们把这种在外连接查询中，指定的where字句中包含被驱动表中列不为NULL值的条件称之为空值拒绝（reject-NULL）。此时外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

        三、子查询优化              

       子查询：在一个查询语句A里的某个位置也可以有另外一个查询语句B，这个出现在A语句的某个位置中的查询B就被称为子查询，A也被称之为外层查询。子查询可以在一个外层查询的各种位置出现。

SELECT (SELECT m1 FROM e1 LIMIT 1);

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;

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

 可以把子查询的查询结果当作是一个表，子查询后边的AS t 表明这个子查询的结果就相当于一个表，这个表 被称之为 派生表。

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

        1.标量子查询：那些只返回一个单一值（一个标量）的子查询称为标量子查询。

# 子查询语句返回的内容都只有一个单一值
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);

        2.行子查询：返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列，就成了标量子查询）。

# 子查询里返回的是一条行数据（虽然只包含两列）
SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);

        3.列子查询：查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录，就成了标量子查询）。

# 这个子查询返回的就是列子查询，返回多个列
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

        4.表子查询：子查询的结果既包含很多条记录，又包含很多个列。（行子查询中我们用limit 1来保证子查询的结果只有一条记录，表子查询中不需要这个限制）。

# 子查询 返回的结果包含多条几里路，又包含多个列
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

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

        1.不相关子查询 ：如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询，（上面的子查询全都可以看作不相关子查询）。

        2.相关子查询：如果子查询的执行需要依赖于外层查询的值，我们称之为相关子查询。

# 子查询的条件 n1=n2,n1是外层查询的列，执行的时候需要依赖于外层查询的值
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);

        [NOT] IN/ANY/SOME/ALL 子查询 （通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式）

        1.[NOT] IN :  这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中

# 子查询的结果集 作为条件判断，如果（m1,n1）在查询的结果集中，则外层查询语句进行按照此条件查询
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);

        2.ANY/SOME : 只要子查询结果集中存在某个值和给定的操作数做比较操作，比较结果为TRUE,那么整个表达式的结果就为TRUE，否则整个表达式的结果为FALSE

# m1大于任意一个 查询出来的结果（可理解为大于最小的 min()即可满足)
SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);

  如果 =ANY 相当于判断子查询结果集中是否存在某个值和给定的操作数相等，含义和IN是相同的

         3.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);

         4.EXISTS ：有的时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体是什么，可以使用EXISTS或者NOT EXISTS放在子查询语句前边     

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

---

       子查询的执行方式：       

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

        对于不相关的标量子查询或者行子查询来说，会先单独执行子查询，然后再将上一步子查询得到的结果当作外层查询的参数再执行外层查询，相当于分别独立的执行外层查询和子查询，如同两个单表查询。

SELECT * FROM s1 WHERE order_note = (SELECT order_note FROM s2 WHERE key3 = 'a' LIMIT 1);

        对于相关的标量子查询或者行子查询来说，先从外层查询中获取一条记录，然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，然后执行子查询。最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则丢弃。再执行第一步，获取第二条外层查询中的记录，依次类推。

MySQL对IN子查询的优化

        1.物化表 (将子查询结果集中的记录保存到临时表的过程称之为物化) ：对于不相关的IN子查询，会尝试把他们物化之后参与查询，将该结果集写入一个临时表里，写入临时表的记录会被去重，临时表也是个表，会为表中记录的所有列建立主键或者唯一索引。一般会建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引（如果临时表的数据量超过规定的字节数（tmp_table_size/max_heap_table_size），临时表会在磁盘上建，同时一样带有B+Tree）

        2.物化表转连接： 本质上变成了连接查询，优化为内连接查询

        3.子查询转换为semi-join(半连接优化）：只是MySQL内部采用的一种执行子查询的方式

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

 select * from user where name IN(select name2 from user_bak where user.age=user_bak.age) OR updateTime > '2022-04-08 10:22:07';
 
select * from user where EXISTS (select 1 from user_bak where user.age=user_bak.age and user.name=user_bak.name2) OR updateTime > '2022-04-08 10:22:07';