MySql--基于规则的优化

具体细节 请去掘金购买《MySQL 是怎样运行的：从根儿上理解 MySQL》

mysql会优化我们的查询条件进行优化，称之为查询重写

where子句必须放在group子句之前；而having子句必须在group子句之后

where子句只可以处理数据表中的数据,having只能处理在group by子句中出现的字段、select的列的字段或聚合函数处理过的列、外部查询中的字段

mysql会化简我们的sql语句

• 1.移除不必要的括号
• 2.常量传递：a = 5 AND b > a--->a = 5 AND b > 5,用OR的时候这个条件就不能替换了
• 3.等值传递：a = b and b = c and c = 5 --->a = 5 and b = 5 and c = 5
• 4.移除没用的条件:对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除掉它们
• 5.表达式计算:a = 5 + 1-->a=6,函数是不会优化的。
• 6.HAVING子句和WHERE子句的合并：如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来。
• 7.常量表检测:查询的表中一条记录没有，或者只有一条记录(不适合innodb)和使用主键等值匹配或者唯一二级索引列等值匹配（常量）作为搜索条件来查询某个表
  优化器在分析查询语句时候，先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本
• 8.外连接消除--（通过外连接加一个where语句限制链接的记录不为null，可以把外连接消除，进而优化器根据查询成本选择合适的驱动表）内连接的驱动表和被驱动表的位置可以相互转换，而左外和右外的驱动表和被驱动表是固定的
• 9.外连接和内连接的本质区别就是：对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；
  而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃
• 10.子查询优化

子查询的二三事

• 1.这种由子查询结果集组成的表称之为派生表。

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

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

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

• 1.不相关子查询:子查询可以单独运行出结果，而不依赖于外层查询的值
• 2.相关子查询:查询的执行需要依赖于外层查询的值

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

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

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

• 3.EXISTS子查询

子查询语法注意事项

• 1.子查询必须用小括号扩起来。
• 2.在SELECT子句中的子查询必须是标量子查询。
• 3.在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用LIMIT 1语句来限制记录数量。
• 4.对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT语句。
• 5.ORDER BY子句无意义：子查询的结果其实就相当于一个集合，集合里的值排不排序一点儿都不重要
• 6.DISTINCT语句无意义：集合里的值去不去重也没啥意义
• 7.在没有聚集函数以及HAVING子句时，GROUP BY子句就是个摆设
• 8.不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询

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

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

• 1.不相关标量子查询或者行子查询来说,先执行子查询再执行外层查询。
• 2.对于相关的标量子查询或者行子查询来说：先从外层查询获取一条记录，然后从该记录中找出子查询涉及到的列
  ，最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
• 3.再次执行第一步，获取第二条外层查询中的记录，依次类推～

IN子查询优化

• 1.对于不相关的IN子查询:子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询
  如果子查询的结果集的记录条数很多，采用临时表。
• 2.临时表中的记录会被去重，如果子查询结果集不是大的离谱，可以基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引
• 3.一旦子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引
• 4.MySQL把这个将子查询结果集中的记录保存到临时表的过程称之为物化
• 5.因此通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

物化表转连接

• 1.最终in查询在变为物化表的时候整个查询就变为内连接

将子查询转换为semi-join（半连接）

• 1.可以能不进行物化操作直接把子查询转换为连接
• 2.对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留s1表的记录
• 3.这样的意思就是当s1有记录符合in中的子查询即可，无需关心符合几个in的参数。
• 4.SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
  可以转变（不是真正的转变，只是为了表达意思）SELECT s1.* FROM s1 SEMI JOIN s2
  ON s1.key1 = s2.common_field
  WHERE key3 = 'a';

实现子查询的方式

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

• 1.当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的表上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中
• 2.因为这个时候可以确保子查询只有一条记录符合要求，因此可以拿到外部

DuplicateWeedout execution strategy （重复值消除）

• 1.1表中的某条记录可能在s2表中有多条匹配的记录，所以该条记录可能多次被添加到最后的结果集中，为了消除重复，我们可以建立一个临时表

LooseScan execution strategy （松散索引扫描）

• 1.SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');
• 2.在子查询中，对于s2表的访问可以使用到key1列的索引，而恰好子查询的查询列表处就是key1列，这样在将该查询转换为半连接查询后，可以将s2作为驱动表执行查询的话
• 3.扫描索引，但只取值相同的记录的第一条去做匹配操作的方式称之为松散索引扫描。

Semi-join Materialization execution strategy

• 1.我们之前介绍的先把外层查询的IN子句中的不相关子查询进行物化，然后再进行外层查询的表和物化表的连接本质上也算是一种semi-join，只不过由于物化表中没有重复的记录，所以可以直接将子查询转为连接查询。

FirstMatch execution strategy （首次匹配）

• 1.先取一条外层查询的中的记录，然后到子查询的表中寻找符合匹配条件的记录，如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找更多匹配的记录，如果找不到则把该外层查询的记录丢弃掉；然后再开始取下一条外层查询中的记录，重复上边这个过程

如何使用半连接

• 1.原始语句：SELECT * FROM s1
  WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3);
• 2.修改后的语句：ELECT s1.* FROM s1 SEMI JOIN s2
  ON s1.key1 = s2.common_field AND s1.key3 = s2.key3;
• 3.这样就可以使用我们上述说道的几种半连接查询方式。
• 4.对于相关子查询 其并不是一个独立的查询，所以不能转换为物化表来执行查询。

semi-join的适用条件

• 1.该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现
• 2.外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
• 3.该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
• 4.该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。

不适用于semi-join的情况

• 1.外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用OR连接起来
• 2.使用NOT IN而不是IN的情况
• 3.在SELECT子句中的IN子查询的情况
• 4.子查询中包含GROUP BY、HAVING或者聚集函数的情况
• 4.子查询中包含UNION的情况