MySQL优化器的SQL重写规则

MySQL优化器的SQL重写规则

MySQL优化器的SQL重写规则：MySQL优化器会根据一定的规则对输入的SQL在保证含义不变的情况下进行SQL的优化重写。

1. 条件简化

1.1 移除不必要的括号

例如：

((a = 5 AND b =c) OR ((a > c) AND (c < 5)));
--优化后
(a = 5 and b =c) OR (a > c AND c < 5)

1.2 常量传递

例如：

a = 5 AND b >a;
--优化后
a = 5 AND b >5;  

1.3 等值传递

例如：

a = b and b = c and c = 5;
--优化后
a = 5 and b = 5 and c = 5;

1.4 移除没用的条件

例如：

a < 1 and b= b;
-- 优化后
a < 1;

1.5 表达式计算

例如：

a = 1 + 1;
--优化后
a = 2;

但是对于复杂的无法优化，例如：

-a < -8;
max(a) > 8;

1.6 常量表检测

在使用主键索引或则唯一性的二级索引进行等值匹配时候，MySql认为查询耗时很少，可以忽略。因此MySQL将这种条件的查询作为一个常量表来处理。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。例如：

select *
from table1 a
         left join table2 b
                   on a.id = b.id
where
  and a.id = 1;

这个查询可以使用主键和常量值的等值匹配来查询table1表，也就是在这个查询中table1表相当于常量表，在分析对table2表的查询成本之前，就会执行对table1表的查询，并把查询中涉及table1表的条件都替换掉:

SELECT table1表记录的各个字段的常量值,
       table2.*
FROM table1
         INNER JOIN table2 ON table2.a = 1;

1.7 外连接消除

内连接的驱动表和被驱动表的位置可以相互转换，而外连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。

如果外连接查询的列行数和内连接查询的行数相同，即查询内容相同，也就是说外连接中驱动表没有多余的列，那么MySQL就会将外连接转换为内连接来执行SQL，这就是外连接消除。

1.8 子查询优化

1.8.1 子查询类型

1. 按返回的结果集区分子查询，子查询分为以下几种：

1. 标量子查询：那些只返回一个单一值的子查询称之为标量子查询。
2. 行子查询：就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。
3. 列子查询：列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。
4. 表子查询：就是子查询的结果既包含很多条记录，又包含很多个列。

2. 按与外层查询关系来区分子查询,可分为：

1. 不相关子查询:如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。
2. 相关子查询：如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询，比如：SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2 WHERE n1 = n2);

3. [NOT] IN/ANY/SOME/ALL子查询

   对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用操作符来组成布尔表达式了，MySQL通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式。

1. IN或者NOT IN:例如：SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
2. ANY/SOME:例如：SELECT * FROM e1 WHERE m1 > ANY(SELECT m2 FROM e2);等价于SELECT * FROM e1 WHERE m1 > (SELECT MIN(m2) FROM e2);
3. ALL:例如：SELECT * FROM e1 WHERE m1 > ALL(SELECT m2 FROM e2);等价于SELECT * FROM e1 WHERE m1 > (SELECT MAX(m2) FROM e2);
4. EXISTS子查询：例如SELECT * FROM e1 WHERE EXISTS (SELECT 1 FROM e2);

1.8.2 子查询优化

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

1. 对于不相关标量子查询或者行子查询来说,先单独执行子查询，然后将子查询结果作为条件执行外出查询，也就是说，分别执行外层查询和子查询，两个单表操作。
2. 对于相关的标量子查询或者行子查询来说，先从外层查询取出一条数据，然后将某列作为条件去匹配子查询，如果成立放入结果集，如果不成立，舍弃。

2. 物化表

1. 对于行子查询或表子查询来说，子查询返回的结果集不止一条，外层去匹配子查询结果集效率极低。那么MySQL采用临时表的解决方法，该临时表的列就是子查询结果集中的列，也就是物化表。
2. 物化表建立方式：写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引。一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

3. 物化表转连接

所谓物化表转连接，就是外层表和物化表做连接查询，MySQL通过计算外层表作为驱动表和物化表作为驱动表进行连接查询的查询成本，然后使用成本较低的方式进行查询。