条件简化&子查询（1）--Mysql基于规则优化（四十四)

上篇文章说了统计数据可以用show table status和show index from 表名查看，可以存着永久性和非永久性，innodb_stats_persistent控制着是否开启永久性，innoDb_stats_persistent_sample_pages和innoDB_stats_transient_sample_pages可以控制采集页面统计，innoDb_stats_auto_recalc控制是否自动计算，都可以在创建表的时候设置。

innoDB_stats_method控制如何对待null值。

innoDB数据收集方式—永久性&非永久性（四十三)

基于规则优化

有的时候，会有一些不是很好的sql语句，但mysql依然会把这些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 AND b > a

改为

a = 5 AND b > 5

还有等值传递，

a = b and b = c and c = 5

改为

a = 5 and b = 5 and c = 5

还有移除没用条件，

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

很明显这里面有两个条件已经知道是true和false了，所以可以优化成

(a < 1 and TRUE) OR (a = 6 OR FALSE)

继续优化

a < 1 OR a = 6

表达式计算：

a = 5 + 1

会直接修改为：

a = 6

如果是复杂一点的则不会优化，比如有函数什么的。

Having和where语句合并，如果查询语句里没有sum，max等聚集函数及其group by，优化器会合并他们。

常量表检测：

1. 查询表中只有一条或者没有数据。(虽然没查，但怎么知道有多少数据呢，别忘了我们在查询之前计算成本会用到数据统计，但innoDB查询出来的不准确，是估值，所以这里一条只使用memory和myISAM)
2. 使用主键等值匹配或者唯一二级索引等值匹配查询表。

也就是如果连接查询中，如何其中一个是常量查询，const方法访问数据库，会直接查询出来，然后在计算成本。

外连接消除

我们回顾一下，内连接和外连接的区别，内连接用on查询出过滤条件如果无法满足是会直接舍弃的，而且驱动表和被驱动表是可以优化的，而外连接分为左连接和右连接，若on的条件不满足，则被驱动表的数据也会查询出来，显示为null。

但如果我们在外连接里面，在加一个where条件，比如说where 某列不等于null，这时候null就会移除，也就是说，这时候查询的数据内连接和外连接其实返回的都是一样的，我们也不用明确的指明不为null，也可以where 某列= 2，这时候也不会查询出某列为null。这时候会优化表的查询顺序，选出最优执行计划。

子查询语法

说子查询优化之前，我们先说一下子查询，在一个查询的某一个位子页可以有另一个查询，这就是子查询，而外层的查询我们称为外查询，

SELECT (SELECT m1 FROM t1 LIMIT 1);

这里就是子查询，

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

这里也是子查询，吧子查询看做一个表，表名为t，表字段为m和n。

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

吧子查询放在外层查询用where和on最常用，

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

这个查询m1属于哪些值。

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

因为子查询本身也是查询，可以吧子查询返回的不同数据分为不同的子查询类型。

1、标量子查询，SELECT (SELECT m1 FROM t1 LIMIT 1);

SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

返回单一值的查询。

1. 行子查询，顾名思义，返回一条记录的子查询

SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

1. 列子查询，就是返回一列的 子查询，

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

1. 表子查询，返回很多行很多列

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_operation，所以子查询用布尔表达式可以是这样，

这是标量子查询，

SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);

这是行子查询

SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);

注意：这里只能返回标量和行子查询，否则这里会报错。

IN/NOT IN/ANY/SOME/ALL子查询

In和not in，这个布尔表达式的意思是用来判断某个操作数在不在由子查询结果集组成的集合中，

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

ANY/SOME

Any和some是同义词，

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

这里的意思就是子查询返回的值如果小于m1就是true，否则就是false，所以这里查询等价于

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

注意这是min(m2)

All:

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

这个sql的意思是m1必须大于子查询里的所有返回集，所以他等价于大于他返回的最大数：

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

注意这是max(m2)

Exists子查询：

有时候我们需要判断数据是否有记录，可以使用exists

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

对于子查询来说，我们并不关系子查询返回的结果集，我们关系的是子查询结果集是否存在记录，只要子查询有记录，那么整个表达式就是true。

当子查询没数据的时候，就直接返回false。

mysql> SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 where t2.m2 = '5');

Empty set (0.00 sec)