Question

nested loop和filter的区别

Answer 1

这种是 MySQL 里最简单、最容易理解的表关联算法。
比如，拿语句 select * from p1 join p2 using(r1) 来说，
先从表 p1 里拿出来一条记录 ROW1，完了再用 ROW1 遍历表 p2 里的每一条记录，并且字段 r1 来做匹配是否相同，以便输出；再次循环刚才的过程，直到两表的记录数对比完成为止。
那看下实际 SQL 的执行计划，
mysql> explain format=json select * from p1 inner join p2 as b using(r1)\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "1003179606.87" }, "nested_loop": [ { "table": { "table_name": "b", "access_type": "ALL", "rows_examined_per_scan": 1000, "rows_produced_per_join": 1000, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "100.00", "prefix_cost": "101.00", "data_read_per_join": "15K" }, "used_columns": [ "id", "r1", "r2" ] } }, { "table": { "table_name": "p1", "access_type": "ALL", "rows_examined_per_scan": 9979810, "rows_produced_per_join": 997981014, "filtered": "10.00", "cost_info": { "read_cost": "5198505.87", "eval_cost": "99798101.49", "prefix_cost": "1003179606.87", "data_read_per_join": "14G" }, "used_columns": [ "id", "r1", "r2" ], "attached_condition": "(`ytt_new`.`p1`.`r1` = `ytt_new`.`b`.`r1`)" } } ] }}1 row in set, 1 warning (0.00 sec)

从上面的执行计划来看，表 p2 为第一张表（驱动表或者叫外表），第二张表为 p1，那 p2 需要遍历的记录数为 1000，同时 p1 需要遍历的记录数大概 1000W 条，那这条 SQL 要执行完成，就得对表 p1（内表）匹配 1000 次，对应的 read_cost 为 5198505.87。那如何才能减少表 p1 的匹配次数呢？那这个时候 JOIN BUFFER 就派上用处了

Answer 2

filter 和 nest
loop的区别

SQL> select a.ename,a.deptno from emp a,dept b where
a.deptno=b.deptno;

Execution
Plan
----------------------------------------------------------
0 SELECT
STATEMENT Optimizer=CHOOSE (Cost=5 Card=1526 Bytes=30
520)
1 0 NESTED
LOOPS (Cost=5 Card=1526 Bytes=30520)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2
Card=14 Bytes=98)
3 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=327
Bytes=425
1)

SQL> select a.ename,a.deptno from emp a where
a.deptno=(select b.deptno from dept b where b.deptno=a.deptno);
0 SELECT
STATEMENT Optimizer=CHOOSE (Cost=12 Card=5 Bytes=35)
1 0 FILTER
2 1 TABLE
ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35)
3 1 TABLE ACCESS (FULL) OF
'DEPT' (Cost=2 Card=3 Bytes=39)

就拿你的例子来说, 第一种情况nested loop 时,
首先全表扫描emp 表,对于返回的每一条记录,然后全表扫描 dept 表, 找出 a.deptno=b.deptno 的记录;重复这一过程,直到 对于emp
表中的每条返回记录,都 通过 a.deptno=b.deptno 去访问了 dept 表. nest loop 可以看成是一个for
循环.

第二种 是filter , 这个计划是说,分别全表扫描 emp 表和 dept 表,然后对于这两个结果集求 笛卡尔集,最后再通过
条件来过滤出最终满足条件的结果集, 至于条件是什么,当然就是 a.deptno=b.deptno

filter是一种操作，累死Order BY
Nested
Loop是Join的一种