今天我们来继续学习关于MySQL优化器的一些优化规则、特性。
除了上篇文章MySQL之基于规则的优化特性（一）介绍的特性之外，在SQL正真执行前，优化器还会帮我们对SQL语句进行“整容”，通过规则，优化器会把执行性能很差的SQL语句转化成可以高效执行的形式，这个过程就是查询重写。
还记得在查看完某条语句的执行计划后的SHOW WARNINGS;的展示内容吗——查询重写后的语句。虽然查询语句重写后的语句有时并不完全等价于原语句（非标准的查询语句），但是优化器也会根据这些规则进行SQL改写 ，今天就来学习一些比较重要的重写规则。
移除不必要的括号
有时候表达式里有许多无用的括号，优化器会把那些用不到的括号给干掉。
((a = 5 AND b = c) OR ((a > c) AND (c < 5))) → (a = 5 and b = c) OR (a > c AND c < 5)
表达式计算
表达式中只包含常量的话，它的值会被优化器先计算出来。
a = 5 + 1 → a = 6
某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，优化器是不会尝试对这些表达式进行化简的。我们前边说过只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。同时，如果表达式中字段被函数包裹，同时也会发生使用不到索引的情况。
ABS(a) > 5 或 -a < -8
常量传递
某个表达式是某个列和某个常量做等值匹配，当这个表达式和其他涉及列a的表达式使用AND连接起来时，可以将其他表达式中的a的值替换为那个常量值。
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) → (a < 1 and TRUE) OR (a = 6 OR FALSE) → a < 1 OR a = 6
HAVING子句和WHERE子句的合并
如果查询语句中没有出现如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。
常量表检测
MySQL认为以下两种查询运行的特别快，花费的时间特别少，少到可以忽略。把通过这两种方式查询的表称之为常量表。
查询的表中一条记录没有，或者只有一条记录。
使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表。
优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。
SELECT * FROM t1 INNER JOIN t2 ON t1.column1 = t2.column2 WHERE t1.primary_key = 1;
这个查询可以使用主键和常量值的等值匹配来查询t1表，也就是在这个查询中t1表相当于常量表，在分析对t2表的查询成本之前，就会执行对t1表的查询，并把查询中涉及t1表的条件都替换掉。
SELECT t1表记录的各个字段的常量值, t2.* FROM t1 INNER JOIN t2 ON t1表column1列的常量值 = t2.column2;
消除外连接
内连接的驱动表和被驱动表的位置可以相互转换，而左（外）连接和右（外）连接的驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接却无法优化表的连接顺序。我们准备一些测试数据。
CREATE DATABASE `test_optimizer` DEFAULT CHARACTER SET utf8;USE `test_optimizer`;CREATE TABLE t1 ( m1 int, n1 char(1)) Engine=InnoDB, CHARSET=utf8;CREATE TABLE t2 ( m2 int, n2 char(1)) Engine=InnoDB, CHARSET=utf8;INSERT INTO t1 VALUES(1,'a');INSERT INTO t1 VALUES(2,'b');INSERT INTO t1 VALUES(3,'c');INSERT INTO t2 VALUES(2,'b');INSERT INTO t2 VALUES(3,'c');INSERT INTO t2 VALUES(4,'d');SELECT * FROM t1;SELECT * FROM t2;
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
上边例子中的（左）外连接，由于驱动表t1中m1=1, n1='a'的记录无法在被驱动表t2中找到符合ON子句条件t1.m1 = t2.m2的记录，所以就直接把这条记录加入到结果集，对应的t2表的m2和n2列的值都设置为NULL。
但是我们又知道WHERE子句的杀伤力比较大，凡是不符合WHERE子句中条件的记录都不会参与连接。只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，也就是说：在这种情况下：外连接和内连接也就没有什么区别了。
SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
由于指定了被驱动表t2的n2列不允许为NULL，所以上边的t1和t2表的左（外）连接查询和内连接查询是一样的。当然，我们也可以不用显式的指定被驱动表的某个列IS NOT NULL，只要隐含的有这个意思就行，比如：
SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 WHERE t2.m2 = 2;
子查询简介
在一个查询语句里的某个位置也可以有另一个查询语句，这个出现在某个查询语句的某个位置中的查询就被称为子查询，外面的那个查询被称之为外层查询。子查询可以出现在外层查询的以下位置。
/* SELECT子句中 */SELECT (SELECT m1 FROM t1 LIMIT 1);/* FROM子句中 */SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;/* WHERE或ON子句中 */SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
ORDER BY子句中、GROUP BY子句中，虽然语法支持，但无意义。
子查询分类
子查询本身也算是一个查询，可以按照返回的结果集、与外层查询关系来进行分类。
标量子查询：只返回一个单一值的子查询称之为标量子查询。
SELECT (SELECT m1 FROM t1 LIMIT 1);SELECT m1 FROM t1 WHERE m1 IN (SELECT MIN(m2) FROM t2);
行子查询：返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。
SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
列子查询：返回一个列的数据，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
表子查询：子查询的结果既包含很多条记录，又包含很多个列。
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
这里需要和行子查询对比一下，行子查询中我们用了LIMIT 1来保证子查询的结果只有一条记录，表子查询中不需要这个限制。
不想关子查询：子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。上述例子都是不想关子查询。
相关子查询：子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);
例子中的子查询是(SELECT m2 FROM t2 WHERE n1 = n2)，可是这个查询中有一个搜索条件是n1 = n2，n1是表t1的列，也就是外层查询的列，也就是说子查询的执行需要依赖于外层查询的值，所以这个子查询就是一个相关子查询。
子查询在布尔表达式中的使用
SELECT (SELECT m1 FROM t1 LIMIT 1);
使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符
操作数 comparison_operator (子查询)
这里的操作数可以是某个列名，或者是一个常量，或者是一个更复杂的表达式，甚至可以是另一个子查询。但是需要注意的是，这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录。如：
SELECT m1 FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
[NOT] IN/ANY/SOME/ALL子查询
对于列子查询和表子查询来说，它们的结果集中包含很多条记录，这些记录相当于是一个集合，所以就不能单纯的和另外一个操作数使用comparison_operator来组成布尔表达式了，MySQL通过下面的语法来支持某个操作数和一个集合组成一个布尔表达式：
IN或者NOT IN
操作数 [NOT] IN (子查询)
这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中。
SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);
ANY/SOME（ANY和SOME是同义词）
操作数 comparison_operator ANY/SOME(子查询)
这个布尔表达式的意思是只要子查询结果集中存在某个值和给定的操作数做comparison_operator比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
这个查询的意思就是对于t1表的某条记录的m1列的值来说，如果子查询(SELECT m2 FROM t2)的结果集中存在一个小于m1列的值，那么整个布尔表达式的值就是TRUE，否则为FALSE，也就是说只要m1列的值大于子查询结果集中最小的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：
SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);
ALL
操作数 comparison_operator ALL(子查询)
这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做comparison_operator比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
这个查询的意思就是对于t1表的某条记录的m1列的值来说，如果子查询(SELECT m2 FROM t2)的结果集中的所有值都小于m1列的值，那么整个布尔表达式的值就是TRUE，否则为FALSE，也就是说只要m1列的值大于子查询结果集中最大的值，整个表达式的结果就是TRUE，所以上边的查询本质上等价于这个查询：
SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2);
EXISTS子查询
有时候仅需要判断子查询的结果集中是否有记录，而不在乎它的记录具体内容，可以使用把EXISTS或者NOT EXISTS放在子查询语句前边。
[NOT] EXISTS (子查询)
如：
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);
子查询注意事项
1、子查询必须用小括号括起来。
mysql> SELECT SELECT m1 FROM t1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT m1 FROM t1' at line 1
2、在SELECT子句中的子查询必须是标量子查询，如果子查询结果集中有多个列或者多个行，都不允许放在SELECT子句中。
mysql> SELECT (SELECT m1, n1 FROM t1);ERROR 1241 (21000): Operand should contain 1 column(s)
3、在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。
4、对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。
mysql> SELECT * FROM t1 WHERE m1 IN (SELECT * FROM t2 LIMIT 2);ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
正因为[NOT] IN/ANY/SOME/ALL子查询不支持LIMIT语句，所以子查询中的这些语句也就是多余的了：
ORDER BY子句：子查询的结果其实就相当于一个集合，集合里的值排不排序不重要。
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2);
ORDER BY画蛇添足。
DISTINCT语句：集合里的值去不去重也毫无意义。
SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2);
没有聚集函数以及HAVING子句的GROUP BY子句：在没有聚集函数以及HAVING子句时，GROUP BY子句就是个摆设。
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);
对于这些冗余的语句，查询优化器在一开始就把它们给删掉了。
5、不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询。
mysql> DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
子查询的执行方式
CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `key1` varchar(100) DEFAULT NULL, `key2` int(11) DEFAULT NULL, `key3` varchar(100) DEFAULT NULL, `key_part1` varchar(100) DEFAULT NULL, `key_part2` varchar(100) DEFAULT NULL, `key_part3` varchar(100) DEFAULT NULL, `common_field` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_key2` (`key2`), KEY `idx_key1` (`key1`), KEY `idx_key3` (`key3`), KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
常规认知中子查询的执行方式
不相关子查询的执行过程
SELECT * FROM t1 WHERE key1 IN (SELECT common_field FROM t2);
1、先执行(SELECT common_field FROM t2)这个查询；
2、将第一步得到的结果当作外层查询的参数再执行外层查询SELECT * FROM t1 WHERE key1 IN(...)。
相关子查询的执行过程
SELECT * FROM t1 WHERE key1 IN (SELECT common_field FROM t2 WHERE t1.key2 = t2.key2);
因为t1.key2 = t2.key2条件的存在，意味着该子查询的执行依赖着外层查询的值，常规认知中，相关子查询执行步骤应该是下面这样的：
1、先从外层查询中获取一条记录，本例中也就是先从t1表中获取一条记录。
2、从上一步骤中获取的那条记录中找出子查询中涉及到的值。就是从t1表中获取的那条记录中找出t1.key2列的值，然后执行子查询。
3、根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
4、重复上述步骤，依次类推，直到匹配到所有的记录。
MySQL中标量子查询、行子查询的执行方式
SELECT子句中，我们前边说过的在查询列表中的子查询必须是标量子查询。 子查询使用=、>、<、>=、<=、<>、!=、<=>等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。
SELECT * FROM t1 WHERE key1 = (SELECT common_field FROM t2 WHERE key3 = 'a' LIMIT 1);
SELECT * FROM t1 WHERE key1 = (SELECT common_field FROM t2 WHERE t1.key3 = t2.key3 LIMIT 1);
IN子查询优化
物化表（materialization=on）
还记得上篇文章MySQL之基于规则的优化特性（一）optimizer_switch剩余的开关参数吗。
「materialization=on（物化） subquery_materialization_cost_based=on（是否开启子查询物化的成本计算）」
对于不相关的IN子查询。
SELECT * FROM t1 WHERE key1 IN (SELECT common_field FROM t2 WHERE key3 = 'a');
无法有效的使用索引，只能对外层查询进行全表扫描。 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。
SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);
）；匹配条件化繁为简的规则有：移除不必要的括号、表达式计算、常量传递、等值传递、移除没用的条件、HAVING子句和WHERE子句的合并、常量表检测、消除外连接。 使用子查询时的注意事项：
物化表对IN子查询的优化：不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。
参考资料
小孩子4919《MySQL是怎样运行的：从根上理解MySQL》
end
