Mysql工作原理——基于规则的优化

Mysql依据一些规则将很糟糕的语句转换成某种可以比较高效执行的形式，这个过程被称为查询重写。

---

条件化简

移除不必要的括号

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

#很明显，b = b这个表达式永远为TRUE，5 != 5这个表达式永远为FALSE，所以简化后的表达式就是这样的： 

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

#可以继续被简化为 

a < 1 OR a = 6

表达式计算

如果表达式中只包含常量的话，它的值会先被计算出来：

a = 5 + 1 
#因为5 + 1这个表达式只包含常量，所以就会被化简成：
a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

abs(a) >5 或者 -a < -8

优化器不会尝试对这些表达式进行化简，只有搜索条件中索引列和常数使用某些运算符连接起来才可能使用到索引，所以如果可以的话，最好让索引列以单独的形式出现在表达式中。

Having和Where 子句的合并

如果查询语句中没有出现如sum、max等聚集函数以及group by子句，优化器就把having子句和where 子句合并起来；

常量表检测，下边这两种查询运行的特别快：

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

把通过这两种方式查询的表称为常量表，优化器在分析一个查询语句时，首先执行常量表查询，然后把查询中涉及到该表的条件全部替换为常数，最后再分析其余表的查询成本。

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

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

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

外连接消除

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

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

所以，只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接在被驱动表中找不到符合ON子句条件的驱动表记录也就被排出最后的结果集了，这种情况下，外连接和内连接就没有什么区别了。

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

子查询优化

按返回结果集区分子查询

• 标量子查询：那些只返回一个单一值得子查询称为标量子查询。

• 行子查询：返回一条记录的子查询，这条记录需要包含多个列。

• 列子查询：列子查询就是查询出一列的数据，包含多条记录；

• 表子查询：包含很多条记录又包含很多列；

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

• 不相关子查询
  如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称为不相关子查询。

• 相关子查询：子查询的执行需要依赖外层查询的值：

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

对于n1=n2 n1是表t1的列，也就是外层查询的列。

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

• 使用=、>、<、>=、<=、<>、!=、<=>作为布尔表达式的操作符：操作数 comparison_operator （子查询）

操作数可以是某个列名，或者是一个常量，或者是一个个更复杂的表达式，甚至可以是另一个子查询，但是这里的子查询只能是标量子查询或行子查询，也就是子查询的结果只能返回一个单一的值或者一条记录。

• [NOT] IN/ANY/SOME/ALL子查询；
  对于列子查询和表子查询来说，它们的结果集中有很多记录，所以不能和另一个操作使用comparison_operator来组成布尔表达式了：

  • IN或者NOT IN 操作数 [NOT] IN (子查询)
  • ANY/SOME 操作数 comparison_operator ANY/SOME （子查询）
  • ALL 操作数 comparison_operator ALL(子查询)

• EXISTS子查询 [NOT] EXISTS (子查询)

子查询语法注意事项

• 子查询必须用小括号括起来；
• 在select 子句中的子查询必须是标量子查询；
• 在想得到标量子查询或行子查询，但是又不能保证子查询的结果集只有一条记录时，应该使用limit 1语句来限制记录数量；
• 对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。
• order by、distinct、没有聚集函数以及having的group by子句，对于这些子句都是冗余的，查询优化器一开始就把它们干掉了。
• 不允许在一条语句中增删改某个时间表时还对该表进行子查询。

子查询在Mysql中是怎么执行的

案例：

CREATE TABLE single_table ( 
	id INT NOT NULL AUTO_INCREMENT, 
	key1 VARCHAR(100), key2 INT, 
	key3 VARCHAR(100), key_part1 VARCHAR(100), 
	key_part2 VARCHAR(100), 
	key_part3 VARCHAR(100), 
	common_field VARCHAR(100), 
	PRIMARY KEY (id), 
	KEY idx_key1 (key1), 
	UNIQUE KEY idx_key2 (key2), 
	KEY idx_key3 (key3), 
	KEY idx_key_part(key_part1, key_part2, key_part3) 
	) Engine=InnoDB CHARSET=utf8;

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

对于不相关标量或行子查询

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

先单独执行行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)这个子查询；然后在将上一步子查询得到的结果当做外层查询的参数再执行外层查询。简单说就是Mysql会分别独立外层查询和子查询，当作两个单表查询就好了。

对于相关变量或行子查询：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 =
 s2.key3 LIMIT 1);

• 先从外层查询中获取一条记录，本例中也就是先从s1表中获取一条记录。
• 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。
• 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
• 再次执行第一步，获取第二条外层查询中的记录，依次类推～

IN子查询优化

物化表的提出

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层分别看成两个单独的单表查询效率还是很高的，但是如果单独执行子查询后的结果集太多就会导致这些问题：

1. 结果集太多内存放不下；
2. 对外层查询来说结果集太多会导致：
   1. 无法有效使用索引，只能对外层查询进行全表扫描；
   2. IN子句参数太多，导致检测一条记录是否符合和IN子句中的参数匹配花费时间太长。

为了解决这个问题，不直接将不相关子查询的结果集当做外层查询的参数，而是将该结果集写入另一个临时表里，写入过程是这样的：

• 该临时表的列就是子查询结果中的列；
• 写入临时表的记录会被去重，去重是为了让临时表变得更小，省地方；
• 一般情况下子查询的结果集都不会大的离谱，所以会为它建立基于内存的使用memory存储引擎的临时表，而且会为该表建立哈希索引。
  如果子查询的结果集非常大，超过系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应变为B+树索引。

把这个子查询结果集中的记录保存到临时表的过程称为物化，把那个存储子查询结果集的临时表称为物化表，正是因为物化表中的记录都建立了索引（基于内存的物化有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作树在不在子查询结果中变得非常快。

物化表转连接

• 从s1的角度来看，整个查询的意思就是：对于s1表中的每条记录来说，如果该记录的key1列的值在子查询对应的物化表中，则该记录会被加入最终的结果集。

• 从子查询物化表的角度来看待，整个查询的意思其实是：对于子查询物化表的每个值来说，如果能在s1表中找到对应的key1列的值与该值相等的记录，那么就把这些记录加入到最终的结果集。

相当于s1与物化表进行内连接；

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

转换成内连接后，查询优化器可以评估不同连接顺序需要的成本，选取成本最低的那种方式执行查询。

将子查询转换为semi-join

能不能不进行物化操作直接把子查询转换为连接呢？

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我们可以把这个查询理解成：对于s1表中的某条记录，如果我们能在s2表（准确的说是执行完WHERE s2.key3 = 'a’之后的结果集）中找到一条或多条记录，这些记录的common_field的值等于s1表记录 的key1列的值，那么该条s1表的记录就会被加入到最终的结果集。这个过程其实和把s1和s2两个表连接起来的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a';

心s2表中是否存在记录满足s1.key1 = s2.common_field这个条件，而不关心具体有多少条记录与之匹配，又因为有可能该记录会被多次加入最终结果集，但是
子查询转换为连接又真的可以充分发挥优化器的作用。所以设计者提出一个新的概念半连接（semi-join）。对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体有多少条记录与之匹配，最后的结果集中只保留s1表的记录。

如何实现半连接

• Table pullout（子查询中的表上拉）

当子查询的查询列表处只有主键或唯一索引列时，可以直接把子查询中的表上拉到外层查询的from子句中。并把子查询中的搜索条件合并到外层查询的搜索条件中。因为主键和唯一索引不可能查询出重复的记录

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

# 上拉后

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

• Duplicate Weedout execution strategy（重复值消除）

转换为半连接查询后，s1表中的某条记录可能在s2表中有多条匹配的记录，所以该记录可能多次被添加到最后结果集中，为了消除重复，可以建立一个临时表，这样在执行连接查询过程中，每当某条s1表中的记录要加入结果集时，就首先把这条记录的id值加入到这个临时表，如果添加成功说明之前这条s1表中的记录并没有加入最终结果集，如果添加失败说明添加过了，直接丢弃。

• LooseScan execution strategy(松散索引扫描)

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

对于这种子查询，条件中使用的索引列和子查询的查询列表列一样都是key1，这样在将该查询转换为半连接查询后，如果将s2作为驱动表执行查询的话，那么执行过程是这样的：

如图，如果该值有多条相同记录，则只匹配第一条记录去做匹配放入结果集中，这种方式称为松散索引扫描。

• Semi-join Materiallization execution strategy

如果物化表中没有重复的记录，则直接将子查询转为连接查询。

• FirstMatch execution strategy（首次匹配）

对于相关子查询，先取一条外层查询中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终结果集并且停止查找更多的匹配记录，如果找不到则把2爱层查询的记录丢弃掉，然后再开始取下一条外层查询中的记录，重复上面过程。

值得注意的是，由于相关子查询不是一个独立的查询，所以不能转换为物化表来执行查询。

semi-join的使用场景

• 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的where或者on子句中出现。
• 外层查询也可以有其他搜索条件，只不过和IN子查询的搜索条件必须使用and连接起来。
• 该子查询必须是一个单一的查询，不能由若干查询由UNION连接起来的形式。
• 该子查询不能包含group by或having语句或聚集函数；

不适用于semi-join的情况

• 外层查询的where 条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来。
• 使用NOT IN而不是IN的情况；
• 在select子句中的IN子查询情况；
• 子查询中包含group by、HAVING或聚集函数；
• 子查询中包含UNION的情况；

如果IN子查询不符合转换为semi-join的条件，那么查询优化器会从下边良好总策略中找出一种成本更低的方式执行子查询。

• 先将子查询物化后再执行查询；
• 执行IN to EXISTS转换。

ANY/ALL 子查询执行

如果ANY/ALL子查询是不相关子查询的话，它们在很多场合都能转换成我们熟悉的方式去执行，比方说：

原表达式	转换为
< ANY(SELECT inner_expr …)	< (SELECTMAX(inner_expr) …)
> ANY(SELECT inner_expr …)	> (SELECTMIN(inner_expr) …)
< ALL (SELECT inner_expr …)	< (SELECTMIN(inner_expr) …)
> ALL (SELECT inner_expr …)	> (SELECTMAX(inner_expr) …)

[NOT] EXISTS子查询的执行

如果[NOT] EXISTS 子查询是不相关子查询，可以先执行子查询，得出该子查询的结果是true还是false 并重写原先的查询语句。