查询优化
参考：https://www.cnblogs.com/Courage129/p/14199151.html
一、优化器的优化策略
静态优化
直接对解析树进行分析，并完成优化
动态优化
动态优化与查询的上下文有关，也可能跟取值、索引对应的行数有关
二、Mysql能够处理的优化类型：
1、重定义关联表的顺序: 数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能
2、将外连接转换成内连接,内连接的效率要高于外连接
3、使用等价变换规则，mysql可以使用一些等价变化来简化并规划表达式
4、优化count()、max()和min()
索引和列是否可以为空通常可以帮助mysql优化这类表达式：例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较
5、预估并转化为常数表达式，当mysql检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行处理
explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1
6、覆盖索引扫描， 当索引中的列包含所有查询中需要使用的列的时候，可以使用覆盖索引
7、子查询优化
mysql在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问，例如将经常查询的数据放入到缓存中
8、等值传播
如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上：
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500;
这里使用film_id字段进行等值关联，film_id这个列不仅适用于film表而且适用于film_actor表
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id > 500 and film_actor.film_id > 500;
9、提前终止查询
10、列表in()的比较
三、关联查询
mysql的关联查询很重要，但其实关联查询执行的策略比较简单：mysql对任何关联都执行嵌套循环关联操作，即mysql先在一张表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。mysql会尝试再最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行之后，mysql返回到上一层次关联表，看是否能够找到更多的匹配记录，以此类推迭代执行。整体的思路如此，但是要注意实际的执行过程中有多个变种形式.
join的实现方式原理
simple Nested-Loop Join:
===> 左边的r 为驱动表，右边的s 为匹配表，可以看到从 r 中分别取出每一条记录去匹配 s 表的列，然后在合并数据， 对 s 表进行 r 表的行数次访问，对数据库的开销比较大。
Index Nested-Loop Join:
====> 这个要求非驱动表（匹配表 s） 上有索引，可以通过索引来减少比较，加速查询。
在查询时，驱动表（r）会根据关联字段的索引进行查找，当在索引上找到符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表查询。
如果非驱动表（s）的关联健是主键的话，性能会非常高，如果不是主键，要进行多次回表查询，先关联索引，然后根据二级索引的主键ID进行回表操作，性能上比索引是主键要慢。
Block Nested-Loop Join:
====> 如果有索引，会选取第二种方式进行join，但是如果join列没有索引，就会采用Block Nested-Loop Join。
可以看到中间有个join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表（s）的访问频率。默认情况下join_buffer_size = 256k,在查询的时候
Mysql 会将所有的需要的列缓存到join buffer 当中，包括select的列， 而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL 当中会在执行的时候分配 N-1 个join buffer。
算子与列的关系：
Selection(Where 条件)、Projection(搜索的列)、Sort(排序列)、Join(等值连接)、Aggregation(Group By及相关聚合操作)
列裁剪：
列裁剪的算法就是自顶向下的把算子过一遍，某个节点需要用到的列就等于它自己需要用到的列加上它的父节点所需要用到的列。这样得到整个SQL语句所涉及到的列，从而再读取数据时只读取需要的列即可。
谓词下推(含join优化) 参考：https://www.cnblogs.com/Courage129/p/14175363.html
在SQL中，谓词就是返回boolean值即true或者false的函数，或是隐式转换为boolean的函数。SQL中的谓词主要有 LKIE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS
==> where 谓词下推场景：
对于查询：select * from t1 left join t2 on t1.id = t2.cid where t2.cname="kingsoft";
在SQL中，将where后面的条件下推；
谓词条件 inner join left join right join full join
left table right table left table right table left table right table left table right table
where predicate √ √ √ × × √ × ×
没有下推，在join后过滤，下推之后，在join前过滤。由于Join本身具有补null的逻辑，会导致最终数据错误。
==> join谓词下推场景：
对于查询：select * from t1 left join t2 on t1.id = t2.cid and t1.name="kingsoft";
在SQL中，将join的条件下推：
谓词条件 inner join left join right join full join
left table right table left table right table left table right table left table right table
join predicate √ √ x √ √ × × ×
/*************************************/
总结起来，关于join和where采用ppd 的规则如下：
谓词条件 inner join left join right join full join
left table right table left table right table left table right table left table right table
where predicate √ √ √ × × √ × ×
join predicate √ √ x √ √ × × ×
1、对于Join(Inner Join)、Full outer Join，条件写在on后面，还是where后面，性能上面没有区别；
2、对于Left outer Join ，右侧的表写在on后面、左侧的表写在where后面，性能上有提高；
3、对于Right outer Join，左侧的表写在on后面、右侧的表写在where后面，性能上有提高；
4、所谓下推，即谓词过滤在map端执行；所谓不下推，即谓词过滤在reduce端执行
注意：如果在表达式中含有不确定函数，整个表达式的谓词将不会被pushed，例如
select a.* from a join b on a.id = b.idwhere a.ds = '2019-10-09' and a.create_time = unix_timestamp();
因为unix_timestamp是不确定函数，在编译的时候无法得知，所以，整个表达式不会被pushed，即ds='2019-10-09'也不会被提前过滤。类似的不确定函数还有rand()等。
/*************************************/
====> 外链接优化
外连接的特点是返回的结果集只会包含外表的所有行，如果外表的某行通过连接列内表没有能够匹配的行，则在结果集的右边补NULL。
如果连接后的where谓词条件能够将包含NULL的行全部过滤，那么此外连接的结果集将等价于内连接，即可将此外连接查询改写为内连接。
综上，当where谓词条件能够满足以下三种能够过滤NULL条件的任意一种时，可进行外连接优化：
- 某个谓词的表达式用NULL值计算后会得到False或NULL。
select * from t1 left outer join t2 on t1.id = t2.id where t2.id is not null;
select * from t1 left outer join t2 on t1.id = t2.id where t2.value > 3;
- 多个谓词用AND连接，其中一个能够过滤NULL。
select * from t1 left outer join t2 on t1.id = t2.id where t2.value > 3 and t2.id is null;
- 多个谓词用OR连接，每一个都能够过滤NULL。
select * from t1 left outer join t2 on t1.id = t2.id where t2.id is not null or t2.value > 3;
