该研究主要是由几句对大数据量表的SQL的join引出的
1. 问题引出
现有数据量较小的表:connection info，数据量较大的表ip_info（均为测试环境下的表，没有商业价值）
第一次SQL写法的执行：
执行时间：2000s内执行不完
经过一步修改：
将where中的一个条件放到了on上面，发现45s内执行完了
这里我错误的认为把条件放到on上会提前筛选元组（但是后来研究后发现原因不是因为这个），这里留意一下Record总数是2454条
下面我顺着我的思路，如果where条件放在on上面都会加速查询，那我就把剩下的where条件也放到on上面，得到下面的执行结果：
可以对比一下跟前面的结果，发现它没有变快，反而变慢了。而且神奇的是，它查出来的record数量跟前一次差距甚大。这就让我非常懵逼了。
于是我开始研究，然后首先对下面两个问题得出结论：
（1）mysql连接时的where和on条件到底执行顺序是怎样的？
（2）为什么两次查询的结果集是不一样的？
下面是两个研究结果：
2. mysql连接时where和on的连接顺序
验证如下结论：
where里可能有关于每张表的筛选条件，不同表的条件生效时期不同。对于驱动表，在执行一开始就会通过where上关于词表的条件筛选一条或者一批记录，然后通过on条件关联下一张表，将得到的结果集再用where上第二张表的条件做过滤，然后重复此过程直到所有表关联完毕。也就是对于驱动表，因为只有where生效，对于其他被驱动表，先被on关联，也就是on先生效，然后再用where****过滤关联的结果集。
现在有两张表：现有sys_user表和sys_user_role表：
Sys_user:
Sys_user_role:
现在执行第一条sql（验证被驱动表是on先执行：）:
从结果可以看到，on条件是在连接的时候，外层循环拿一个tuple，内层被驱动表中每取出一个tuple都会做条件匹配，条件匹配不到也会填入tuple。
现在执行第二条sql（验证被驱动表where是在连接后的临时表中执行筛选的）
连接之后的三条数据，只有一条满足sys_user_role.user_id为null，执行结果符合预期
3. mysql对left join的逻辑优化
源代码位置：
Mysql-server/sql/sql_resolver.cc/simplify_joins()
结合源代码的两段注释：
（1）
(A LEFT JOIN B ON JC) WHERE W ,
we'll "confront W with A LEFT JOIN B": this will, recursively,
- confront W with B,
- confront W with A.
Because W is external to the nest, if W would be false when B is
NULL-complemented we know we can change LEFT JOIN to JOIN.
（2）
If a join condition JC is attached to the table,
check all null rejected predicates in this condition.
If such a predicate over an attribute belonging to
an inner table of an embedded outer join is found,
the outer join is converted to an inner join and
the corresponding join condition is added to JC.其中第一段是举了一个例子，第二段中指出了如果遇到控制拒绝谓词……就把outer join转化为inner join
于是猜想：对于(A LEFT JOIN B ON JC) WHERE W什么时候left join转化为inner join的条件是如果where W中的W是B表的条件，并且该条件不会包含NULL值（比如where id > 2是拒绝id为NULL的），则会将(A LEFT JOIN B ON JC) WHERE W转化为A JOIN B ON JC AND W
验证：
现有sys_user表和sys_user_role表：
Sys_user:
Sys_user_role:
现在执行第一条sql，并通过warnings：
WARNINGS：
/* select#1 */ select ruoyi.sys_user.user_id AS user_id,ruoyi.sys_user.dept_id AS dept_id,ruoyi.sys_user.user_name AS user_name,ruoyi.sys_user.nick_name AS nick_name,ruoyi.sys_user.user_type AS user_type,ruoyi.sys_user.email AS email,ruoyi.sys_user.phonenumber AS phonenumber,ruoyi.sys_user.sex AS sex,ruoyi.sys_user.avatar AS avatar,ruoyi.sys_user.password AS password,ruoyi.sys_user.status AS status,ruoyi.sys_user.del_flag AS del_flag,ruoyi.sys_user.login_ip AS login_ip,ruoyi.sys_user.login_date AS login_date,ruoyi.sys_user.create_by AS create_by,ruoyi.sys_user.create_time AS create_time,ruoyi.sys_user.update_by AS update_by,ruoyi.sys_user.update_time AS update_time,ruoyi.sys_user.remark AS remark,ruoyi.sys_user_role.user_id AS user_id,ruoyi.sys_user_role.role_id AS role_id from ruoyi.sys_user join ruoyi.sys_user_role where ((ruoyi.sys_user.user_id = ruoyi.sys_user_role.user_id) and (ruoyi.sys_user_role.role_id = 2))
可以看到left join被优化成了join（inner join）
通过explain format=tree指令查看：
发现果然变成了inner join
下面我们将条件从role_id = 2改成role_id is NULL:
WARNINGS：
/* select#1 */ select ruoyi.sys_user.user_id AS user_id,ruoyi.sys_user.dept_id AS dept_id,ruoyi.sys_user.user_name AS user_name,ruoyi.sys_user.nick_name AS nick_name,ruoyi.sys_user.user_type AS user_type,ruoyi.sys_user.email AS email,ruoyi.sys_user.phonenumber AS phonenumber,ruoyi.sys_user.sex AS sex,ruoyi.sys_user.avatar AS avatar,ruoyi.sys_user.password AS password,ruoyi.sys_user.status AS status,ruoyi.sys_user.del_flag AS del_flag,ruoyi.sys_user.login_ip AS login_ip,ruoyi.sys_user.login_date AS login_date,ruoyi.sys_user.create_by AS create_by,ruoyi.sys_user.create_time AS create_time,ruoyi.sys_user.update_by AS update_by,ruoyi.sys_user.update_time AS update_time,ruoyi.sys_user.remark AS remark,ruoyi.sys_user_role.user_id AS user_id,ruoyi.sys_user_role.role_id AS role_id from ruoyi.sys_user left join ruoyi.sys_user_role on((ruoyi.sys_user_role.user_id = ruoyi.sys_user.user_id)) where (ruoyi.sys_user_role.role_id is null)
可以看到上面还是left join，证明当B表WHERE筛选条件可以包含NULL值字段时不会将left join转化为inner join
这里的包含NULL值我认为只是形式上的，从上面的测试结果来看这确实是形式上的，并且该代码的位置是sql引擎的逻辑查询阶段，没有到执行的阶段，是通过启发式规则匹配来完成的优化，从这点来看也应该是形式上的。
下面再通过explain format=tree语句（或者show warnings也可以验证）验证一下他们的树状执行计划：
可以看到第一个计划执行树中的两个join（分别是Nest loop inner join和Inner hash join）都是inner join，说明outer join已经被mysql优化成了inner join。
而下面的例子（where条件放到on上面）
在这里可以看到最顶层的join已经不再是inner join而是outer join，而底层的join还是inner join。
但是仅靠上面的结论我们只能知道mysql将left join优化成了inner join，但是还是解释不清楚为什么这两个join时间差距会这么大（应该都是调用相同的Iterator或者说物理算子）。
于是我又进行了下面的研究：
下面是workbench给出的执行计划树状图：
上面是转化为inner join的情况
上面是不转化为inner join的情况
从上面的图中很容易看出来，两个sql的连接顺序有很大的不同。Inner join中mysql将连接顺序优化为先执行两个ip_info_table的连接，再执行与connection_info的连接（两个ip_info_table的连接没有连接条件，会不会变成了完全的笛卡尔积了？）。这个问题到这里就可以解决了，我们可以使用straight_join强制按照ci作为第一个的连接顺序来执行（或者mysql 8.0以后的hint方式手动指定连接顺序）。
执行时间44s，explain查看执行计划，是按照ci开始的连接顺序：
但是为什么left join和inner join，mysql对执行顺序的优化会如此大的差距，还要看下面的研究：
4. mysql执行流程
下面给出一张图，表示mysql执行引擎中的查询优化器的执行流程：
这里简单说一下流程，一开始起点位置是经过mysql的解析器解析的AST抽象语法树，数据结构为st_select_lex
一开始经过JOIN::prepare()和JOIN::optimize()的逻辑优化部分，即基于规则匹配的逻辑优化，其中的simplify_joins()函数就包括了基于规则简化左连接为内连接。
紧接着进入make_join_statistics()生成join顺序，分为两种顺序生成方式：optimize_straight_join()和greedy_search()。第一种是使用默认的表的连接顺序，而不用mysql对其顺序进行优化，第二种是贪心+dfs算法，基于物理代价估算选出一种最好的连接顺序。注意这里的join顺序是一种左深树，所以其实其顺序可以用一个数组来直接表示。
关于greedy_search()算法的贪心性和dfs两者结合的具体细节，可以参考这个文章：https://zhuanlan.zhihu.com/p/542499821
所以在上面提到的解决办法中采用了straight_join的连接顺序的方法，强制mysql按照我们的默认表的连接顺序进行连接而不是尝试对其进行优化。但是为什么mysql优化器会给我们选择这么一种时间花销这么大的join顺序，还有待继续研究。