条件化简

移除不必要的括号

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

常量传递（constant_propagation）

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

a = 5

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

a = 5 AND b > a
a = 5 AND b > 5

等值传递（equality_propagation）

a = b and b = c and c = 5
a = 5 and b = 5 and c = 5

移除没用的条件（trivial_condition_removal）

对于一些明显永远为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

表达式计算

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来，比如这个：

a = 5 + 1
a = 6

如果某个列并不是以单独的形式作为表达式的操作数时, 优化器是不会尝试对这些表达式进行化简的:

ABS(a) > 5
-a < -8

HAVING子句和WHERE子句的合并

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

常量表检测

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

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

设计MySQL的大叔觉得这两种查询花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表（英文名：constant tables）。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本，比方说这个查询语句：

SELECT * FROM table1 INNER JOIN table2
    ON table1.column1 = table2.column2 
    WHERE table1.primary_key = 1;

# 转换后
SELECT table1表记录的各个字段的常量值, table2.* FROM table1 INNER JOIN table2 
    ON table1表column1列的常量值 = table2.column2;

外连接消除

示例表:

CREATE TABLE t1 (
    m1 int, 
    n1 char(1)
) Engine=InnoDB, CHARSET=utf8;

CREATE TABLE t2 (
    m2 int, 
    n2 char(1)
) Engine=InnoDB, CHARSET=utf8;

数据:

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

内连接与外连接对比:

mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

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

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.01 sec)

我们把这种在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

子查询优化

子查询语法

子查询可以在一个外层查询的各种位置出现:

SELECT子句中

mysql> SELECT (SELECT m1 FROM t1 LIMIT 1);
+-----------------------------+
| (SELECT m1 FROM t1 LIMIT 1) |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set (0.00 sec)

FROM子句中
- 设计MySQL的大叔把这种由子查询结果集组成的表称之为派生表

SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM t2 WHERE m2 > 2) AS t;
+------+------+
| m    | n    |
+------+------+
|    4 | c    |
|    5 | d    |
+------+------+
2 rows in set (0.00 sec)

WHERE或ON子句中

mysql> SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

ORDER BY子句中

虽然语法支持，但没啥子意义，不唠叨这种情况了。

GROUP BY子句中

同上～

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

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

标量子查询

只返回一个单一值:

SELECT (SELECT m1 FROM t1 LIMIT 1);
SELECT * FROM t1 WHERE m1 = (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);

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

不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。我们前边介绍的那些子查询全部都可以看作不相关子查询，所以也就不举例子了哈。

相关子查询

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

SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

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

使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符

使用格式:

这里的操作数可以是某个列名，或者是一个常量，或者是一个更复杂的表达式，甚至可以是另一个子查询。但是需要注意的是，这里的子查询只能是标量子查询或者行子查询

操作数 comparison_operator (子查询)

例子:

# 标量子查询
SELECT * 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子查询

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

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

操作数 comparison_operator ALL(子查询)

这个布尔表达式的意思是子查询结果集中所有的值和给定的操作数做comparison_operator比较结果为TRUE，那么整个表达式的结果就为TRUE，否则整个表达式的结果就为FALSE。比方说下边这个查询：

SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);

EXISTS子查询

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

[NOT] EXISTS (子查询)

例子:

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

只要(SELECT 1 FROM t2)这个查询中有记录，那么整个EXISTS表达式的结果就为TRUE。

子查询语法注意事项

子查询必须用小括号扩起来
在SELECT子句中的子查询必须是标量子查询
在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量
对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句
- 子查询的结果其实就相当于一个集合，集合里的值排不排序一点儿都不重要，比如下边这个语句中的ORDER BY子句简直就是画蛇添足：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 ORDER BY m2);
- 集合里的值去不去重也没啥意义，比如这样：SELECT * FROM t1 WHERE m1 IN (SELECT DISTINCT m2 FROM t2);
- 在没有聚集函数以及HAVING子句时，GROUP BY子句就是个摆设，比如这样：SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 GROUP BY m2);
不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询

MySQL 是怎样运行的

基于规则的优化