mysql join 循环,关于mysql联表的内嵌循环操作nested loop join中on和where执行顺序问题...

mysql的理论依据没找到，个人理解是先执行where的过滤条件，先关联再过滤明显做的是无用功。

oracle中倒是能在执行计划中看到，先执行的是过滤条件(下面代码中最后一行)。

explain plan for SELECT * FROM tmp_t2 t2 LEFT JOIN tmp_t1 t1 ON t2.id = t1.id AND t1.good_id = 'A'

-----------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 3 | 147 | 6 (0)| 00:00:01 |

|* 1 | HASH JOIN | | 3 | 147 | 6 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| TMP_T2 | 3 | 60 | 3 (0)| 00:00:01 |

|* 3 | TABLE ACCESS FULL| TMP_T1 | 3 | 87 | 3 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("T2"."ID"="T1"."ID")

3 - filter("T1"."GOOD_ID"='A')