國產集中庫SQL能力評測 - 子查詢
原創
作者：
韓鋒
優化器需要考慮如何最有效地執行嵌套查詢，這可能涉及到多個表的連接、復雜的條件邏輯等，這對于優化器挑戰是很大的。
子查詢（Subquery），是SQL查詢中的一種，它允許一個查詢嵌套在另一個查詢中。子查詢通常用在SELECT、INSERT、UPDATE或DELETE語句中，作為一個單獨的查詢單元來返回數據，這些數據可以被外部查詢使用。子查詢通常是數據庫開發中自然邏輯的體現，但對于數據庫而言會帶來很大挑戰。一方面，子查詢可能使得數據庫的查詢優化器難以生成高效的執行計劃，優化器需要考慮如何最有效地執行嵌套查詢，這可能涉及到多個表的連接、復雜的條件邏輯等，這對于優化器挑戰是很大的。另一方面，子查詢可能會降低SQL代碼的可讀性和維護性，使得優化和調試變得更加困難，特別是層次嵌套很深的子查詢。此外，子查詢還可能會改變數據訪問模式、若邏輯復雜還可能影響索引使用等等弊端。本文將對比不同數據庫對子查詢的處理方式差異。
1. 子查詢分類
1)子查詢分類
圖片
2)Oracle 示例
-- 【子查詢位置】-- 標量子查詢select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_namefrom emp e where e.emp_id=1;-- 內聯子查詢select * from (select * from emp where salary<1500) where dept_id <50;-- 嵌套子查詢select * from emp where salary=(select max(salary) from emp);
-- 【與主查詢關聯】-- 關聯子查詢select emp_id,emp_name,salaryfrom emp e1where salary=(select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 反關聯子查詢select emp_id,emp_name,salaryfrom emp e1where salary not in (select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 非關聯子查詢select count(*) from empwhere salary<(select avg(salary) from emp);
-- [子查詢結果集]-- 標量子查詢select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_namefrom emp e where e.emp_id=1;-- 列子查詢select * from emp where dept_id in (select dept_id from dept where dept_name like 'dept1%');-- 行子查詢select * from emp a where a.dept_id in (select b.dept_id from dept b);-- 表子查詢select a.emp_id,a.dept_id,a.salary from emp a where (a.dept_id ,a.salary) in (select b.dept_id,b.salary from emp b where b.salary<1300);
-- [子查詢謂詞]-- IN select * from emp where dept_id in (select dept_id from dept where dept_id <20);-- EXISTSselect * from emp e where exists ( select 1 from dept d where d.dept_id=e.dept_id);-- ANYselect emp_name,salary from emp where salary > any(select avg(salary) from emp group by dept_id);-- ALLselect emp_name,salary from emp where salary < all(select avg(salary) from emp group by dept_id);-- SOMEselect emp_name,salary from emp where salary > some(select avg(salary) from emp group by dept_id);3)國產庫支持情況
國產數據庫（含MySQL）都支持了上述子查詢寫法，除了MySQL需要稍微調整下寫法外，其他都可以無需修改直接使用。
圖片
2. 子查詢優化
子查詢有多種優化方式，下面以 Oracle 支持的子查詢優化手段為目標，看看國產數據庫（含MySQL）支持情況如何。特說明，國產數據庫可能含有其他子查詢優化手段，下文不代表國產數據庫針對子查詢的全部優化能力。
圖片
1)子查詢展開/解嵌套
子查詢展開是優化器處理帶子查詢的目標SQL的一種優化手段，它是指優化器不再將目標SQL中的子查詢當作一個獨立的處理單元來單獨執行，而是將該子查詢轉換為它自身和外部查詢之間等價的表連接。從而獲得更優的執行計劃。子查詢展開有兩種形式，一種是將子查詢拆開(即將該子查詢中的表、視圖從子查詢拿出來，然后和外部查詢中的表、視圖做表連接)；一種是不拆開但是會把該子查詢轉換為一個內嵌視圖(Inline View)，然后再和外部查詢中的表、視圖做表連接。前者屬于啟發式查詢轉換，后者屬于基于代價的轉換。
子查詢展開通常都會提高原SQL的執行效率，因為如果原SQL不做子查詢展開，那么通常情況下該子查詢就會在其執行計劃的最后一步才執行，并且會走FILTER類型的執行計劃，這也意味著對于外部查詢所在結果集中的每一條記錄，該子查詢都會被當作一個獨立的執行單元來執行一次，外部查詢所在的結果集有多少條記錄，該子查詢就會被執行多少次(可以近似這么理解，實際上并不完全是這樣)。這種執行方式的執行效率通常都不會太高，尤其是在子查詢中包含兩個或者兩個以上表連接時，此時做子查詢展開后的執行效率往往會比走FILTER類型的執行計劃高很多，因為此時優化器就會有其他更多、更高效的執行路徑(比如哈希連接)可以選擇。
Oracle
-- IN/EXISTS轉換為SEMI JOINSQL> explain plan for select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 332K| 15 (0)| 00:00:01 || 1 | NESTED LOOPS SEMI | | 10000 | 332K| 15 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 302K| 15 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN| DEPT_PK | 100 | 300 | 0 (0)| 00:00:01 |------------------------------------------------------------------------------* 優化器將IN或EXISTS子句中的子查詢展開(反嵌套)，使得優化器選擇半關聯(SEMI-JOIN)操作。這種轉換屬于啟發式查詢轉換。
-- NOT IN/EXISTS轉換為ANTI-JOINSQL> explain plan for select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 3400 | 15 (0)| 00:00:01 || 1 | NESTED LOOPS ANTI | | 100 | 3400 | 15 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 302K| 15 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN| DEPT_PK | 100 | 300 | 0 (0)| 00:00:01 |------------------------------------------------------------------------------* 優化器將NOT IN或NOT EXISTS子句中的子查詢展開(反嵌套)，使得優化器選擇反關聯(ANTI-JOIN)操作。這種轉換屬于基于代價的查詢轉換。
-- NOT IN/NOT EXISTS轉換為Null-Aware ANTI-JOINSQL> explain plan for select * from emp e where e.dept_id not in (select dept_id from dept d);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 3400 | 17 (6)| 00:00:01 ||* 1 | HASH JOIN RIGHT ANTI SNA| | 100 | 3400 | 17 (6)| 00:00:01 || 2 | INDEX FULL SCAN | DEPT_PK | 100 | 300 | 1 (0)| 00:00:01 || 3 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |------------------------------------------------------------------------------------* 示例中EMP表的DEPT_ID字段允許為空，優化器將NOT IN/NOT EXISTS子句中的子查詢展開(反嵌套)，使得優化器能選擇對空值敏感的反關聯(Null-Aware ANTI-JOIN)操作。* 這種轉換屬于啟發式查詢轉換。對空值敏感的反關聯操作能在關聯數據時關注到空值的存在，從而避免使用代價昂貴的操作(如笛卡爾積關聯)來獲取邏輯結果。
-- 互關聯子查詢轉換為內聯視圖SQL> explain plan for select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 500 | 28500 | 32 (7)| 00:00:01 ||* 1 | HASH JOIN | | 500 | 28500 | 32 (7)| 00:00:01 || 2 | VIEW | VW_SQ_1 | 10000 | 253K| 16 (7)| 00:00:01 || 3 | HASH GROUP BY | | 10000 | 90000 | 16 (7)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 10000 | 90000 | 15 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |--------------------------------------------------------------------------------* 示例中，關聯謂詞中存在子查詢，優化器對互關聯子查詢的反嵌套，會將子查詢構造出一個內聯視圖，并將內聯視圖與主查詢中的表進行關聯。這種轉換屬于啟發式查詢轉換。MySQL
-- IN/EXISTSmysql> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+* 退化為嵌套循環表連接
-- NOT IN/EXISTSmysql> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.e.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | d | NULL | index | PRIMARY | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+* 嵌套循環表連接+物化子查詢
-- NOT IN/NOT EXISTS（NULL AWare）mysql> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.e.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | d | NULL | index | PRIMARY | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+
-- 互關聯子查詢mysql> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| 1 | PRIMARY | e1 | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | e2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e1.emp_id | 1 | 100.00 | NULL |+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+DM
-- IN/EXISTSSQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 10000, 163]2 #PRJT2: [3, 10000, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 10000, 163]; n_keys(1) KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1)
-- NOT IN/EXISTSSQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 1, 163]2 #PRJT2: [3, 1, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 1, 163]; n_keys(1) (ANTI), KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1)
-- NOT IN/NOT EXISTS（NULL AWare）SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 1, 163]2 #PRJT2: [3, 1, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 1, 163]; n_keys(1) (ANTI), KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1)
-- 互關聯子查詢SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);1 #NSET2: [6, 500, 223]2 #PRJT2: [6, 500, 223]; exp_num(6), is_atom(FALSE)3 #SLCT2: [6, 500, 223]; DMTEMPVIEW_889193621.colname < E1.SALARY4 #HASH2 INNER JOIN: [6, 500, 223]; RKEY_UNIQUE KEY_NUM(1); KEY(DMTEMPVIEW_889193621.colname=E1.EMP_ID) KEY_NULL_EQU(0)5 #PRJT2: [2, 10000, 60]; exp_num(2), is_atom(FALSE)6 #HAGR2: [2, 10000, 60]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(E2.EMP_ID)7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP as E2); btr_scan(1)8 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E1); btr_scan(1)Kingbase
-- IN/EXISTS SQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- NOT IN/EXISTS SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ------------------------------------------------------------------------------------ Gather (cost=1003.25..17935.13 rows=9901 width=42) Workers Planned: 2 -> Hash Anti Join (cost=3.25..15945.03 rows=4125 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Parallel Seq Scan on emp e (cost=0.00..14342.67 rows=416667 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- NOT IN/NOT EXISTS（NULL AWare） SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ------------------------------------------------------------------------------------ Gather (cost=1003.25..17935.13 rows=9901 width=42) Workers Planned: 2 -> Hash Anti Join (cost=3.25..15945.03 rows=4125 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Parallel Seq Scan on emp e (cost=0.00..14342.67 rows=416667 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- 互關聯子查詢 SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); QUERY PLAN ----------------------------------------------------------------------------------- Seq Scan on emp e1 (cost=0.00..8480176.00 rows=333333 width=42) Filter: (salary > (SubPlan 1)) SubPlan 1 -> Aggregate (cost=8.45..8.46 rows=1 width=8) -> Index Scan using EMP_PK on emp e2 (cost=0.42..8.44 rows=1 width=8) Index Cond: (emp_id = e1.emp_id)YashanDB
-- IN/EXISTS轉換為SEMI JOIN SQL> explain plan for select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS SEMI | | | 10000| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- NOT IN/EXISTS轉換為ANTI-JOIN SQL> explain plan for select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS ANTI | | | 1| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- NOT IN/NOT EXISTS轉換為Null-Aware ANTI-JOIN SQL> explain plan for select * from emp e where e.dept_id not in (select dept_id from dept d); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS ANTI | | | 1| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- 互關聯子查詢轉換為內聯視圖 SQL> explain plan for select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS INNER | | | 6650| 61( 0)| | | 2 | VIEW | | | 10000| 57( 0)| | | 3 | HASH GROUP | | | 10000| 57( 0)| | | 4 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 5 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| | |* 6 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Vastbase
-- IN/EXISTSSQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Join (cost=3.25..345.26 rows=9901 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- NOT IN/EXISTSSQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Anti Join (cost=3.25..253.43 rows=99 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- NOT IN/NOT EXISTS（NULL AWare）SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Anti Join (cost=3.25..253.43 rows=99 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- 互關聯子查詢SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); QUERY PLAN------------------------------------------------------------------------- Hash Join (cost=611.00..998.50 rows=3333 width=44) Hash Cond: (e2.emp_id = e1.emp_id) Join Filter: (e1.salary > (avg(e2.salary))) -> HashAggregate (cost=268.00..393.00 rows=10000 width=48) Group By Key: e2.emp_id -> Seq Scan on emp e2 (cost=0.00..218.00 rows=10000 width=16) -> Hash (cost=218.00..218.00 rows=10000 width=44) -> Seq Scan on emp e1 (cost=0.00..218.00 rows=10000 width=44)2)標量子查詢合并
針對含有標量子查詢的情況，優化器會嘗試與主查詢中的對象進行合并關聯操作。
Oracle
SQL> explain plan for select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 10 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 2 (0)| 00:00:01 ||* 4 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------MySQL
mysql> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | PRIMARY | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL || 2 | DEPENDENT SUBQUERY | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+DM
SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;1 #NSET2: [1, 1, 72]2 #PIPE2: [1, 1, 72]3 #PRJT2: [1, 1, 72]; exp_num(4), is_atom(FALSE)4 #BLKUP2: [1, 1, 72]; INDEX33555485(E)5 #SSEK2: [1, 1, 72]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(1),exp_cast(1)], is_global(0)6 #SPL2: [1, 1, 78]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)7 #PRJT2: [1, 1, 78]; exp_num(1), is_atom(TRUE)8 #BLKUP2: [1, 1, 78]; INDEX33555481(D)9 #SSEK2: [1, 1, 78]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[var1,var1], is_global(0)Kingbase
SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1; QUERY PLAN----------------------------------------------------------------------- Index Scan using EMP_PK on emp e (cost=0.42..10.69 rows=1 width=229) Index Cond: (emp_id = '1'::numeric) SubPlan 1 -> Seq Scan on dept d (cost=0.00..2.25 rows=1 width=13) Filter: (dept_id = e.dept_id)YashanDB
SQL> explain plan for select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | SUBQUERY | QUERY[1] | | | | || 2 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| ||* 5 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Vastbase
SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1; QUERY PLAN------------------------------------------------------------------------------- Index Scan using emp_pk on emp e (cost=0.00..16.54 rows=1 width=16) Index Cond: (emp_id = 1::number) SubPlan 1 -> Index Scan using dept_pk on dept d (cost=0.00..8.27 rows=1 width=12) Index Cond: (dept_id = e.dept_id)3)子查詢合并
當優化器未對子查詢做反嵌套的情況下，可以將兩個兼容的子查詢合并為一個子查詢。
Oracle
SQL> explain plan forselect * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100) and exists( select 1 from emp e2 where e2.emp_name like 'emp2%'); SQL> select * from table(dbms_xplan.display);-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 6 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_EMP_NAME | 2 | 16 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | 2 | 10 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------MySQL
mysql> explain select * from dual_tab d -> where exists -> ( select 1 from emp e1 where e1.salary<1100) -> and exists -> ( select 1 from emp e2 where e2.emp_name like 'emp2%');+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL || 1 | SIMPLE | e1 | NULL | range | idx_emp_salary | idx_emp_salary | 5 | NULL | 459 | 100.00 | Using where; Using index; FirstMatch(d); Using join buffer (hash join) || 1 | SIMPLE | e2 | NULL | range | idx_emp_name | idx_emp_name | 33 | NULL | 1111 | 100.00 | Using where; Using index; FirstMatch(e1); Using join buffer (hash join) |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+DM
SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like 'emp2%');1 #NSET2: [1, 1, 48]2 #PIPE2: [1, 1, 48]3 #PIPE2: [1, 1, 48]4 #PRJT2: [1, 1, 48]; exp_num(1), is_atom(FALSE)5 #SLCT2: [1, 1, 48]; (NOREFED_EXISTS_SSS AND NOREFED_EXISTS_SSS)6 #CSCN2: [1, 1, 48]; SYSINDEXSYSDUAL2(SYSDUAL2 as D); btr_scan(1)7 #SPL2: [1, 1111, 48]; key_num(1), spool_num(1), is_atom(FALSE), has_var(0), sites(-)8 #PRJT2: [1, 1111, 48]; exp_num(1), is_atom(FALSE)9 #SSEK2: [1, 1111, 48]; scan_type(ASC), IDX_EMP_NAME(EMP as E2), scan_range['emp2','emp3'), is_global(0)10 #SPL2: [1, 885, 30]; key_num(1), spool_num(0), is_atom(FALSE), has_var(0), sites(-)11 #PRJT2: [1, 885, 30]; exp_num(1), is_atom(FALSE)12 #SSEK2: [1, 885, 30]; scan_type(ASC), IDX_EMP_SALARY(EMP as E1), scan_range(null2,exp_cast(1100)), is_global(0)Kingbase
SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like 'emp2%'); QUERY PLAN---------------------------------------------------------------------- Result (cost=227.01..228.02 rows=1 width=2) One-Time Filter: ($0 AND $1) InitPlan 1 (returns $0) -> Seq Scan on emp e1 (cost=0.00..22676.00 rows=91272 width=0) Filter: (salary < '1100'::double precision) InitPlan 2 (returns $1) -> Seq Scan on emp e2 (cost=0.00..22676.00 rows=100 width=0) Filter: ((emp_name)::text ~~ 'emp2%'::text) -> Seq Scan on dual d (cost=227.01..228.02 rows=1 width=2)YashanDB
SQL> explain plan forselect * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100) and exists( select 1 from emp e2 where e2.emp_name like 'emp2%'); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED LOOPS SEMI | | | 1| 12( 0)| || 2 | NESTED LOOPS SEMI | | | 1| 10( 0)| || 3 | TABLE ACCESS FULL | X$DUAL | SYS | 1| 8( 0)| ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 2( 0)| ||* 5 | INDEX RANGE SCAN | IDX_EMP_NAME | TESTUSER | 1094| 2( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Vastbase
SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like 'emp2%'); QUERY PLAN------------------------------------------------------------------ Result (cost=243.27..243.29 rows=1 width=32) One-Time Filter: ($0 AND $1) InitPlan 1 (returns $0) -> Seq Scan on emp e1 (cost=0.00..243.00 rows=900 width=0) Filter: (salary < 1100::double precision) InitPlan 2 (returns $1) -> Seq Scan on emp e2 (cost=0.00..243.00 rows=1 width=0) Filter: ((emp_name)::text ~~ 'emp2%'::text) -> Result (cost=0.00..0.01 rows=1 width=0)4)子查詢推入
子查詢推入是一項對未能合并或者反嵌套的子查詢優化的補充優化技術。通常情況下，未能合并或者反嵌套的子查詢的子計劃會被放置在整個查詢計劃的最后步驟執行，而子查詢推進使得子查詢能夠提前被評估，使之可以出現在整體執行計劃的較早步驟，從而獲得更優的執行計劃。
Oracle
SQL> explain plan for select * from emp e where salary >(select avg(salary) from emp);SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 500 | 15500 | 24 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 500 | 15500 | 14 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_EMP_SALARY | 81 | | 2 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 5 | | || 4 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |----------------------------------------------------------------------------------------------
SQL> explain plan for select /*+ no_push_subq(@inv)*/ * from emp e where salary >(select /*+ qb_name(inv)*/ avg(salary) from emp);SQL> select * from table(dbms_xplan.display);-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 302K| 25 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 5 | | || 4 | INDEX FAST FULL SCAN| IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |-----------------------------------------------------------------------------------------* 如禁用子查詢推入功能，執行計劃則退化為FILTER，子查詢會被最后執行MySQL
mysql> explain select * from emp e where salary >(select avg(salary) from emp);+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+| 1 | PRIMARY | e | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10117 | 44.77 | Using where || 2 | SUBQUERY | emp | NULL | index | NULL | idx_emp_salary | 5 | NULL | 10117 | 100.00 | Using index |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+DM
SQL> explain select * from emp e where salary >(select avg(salary) from emp);1 #NSET2: [1, 500, 163]2 #PIPE2: [1, 500, 163]3 #PRJT2: [1, 500, 163]; exp_num(6), is_atom(FALSE)4 #BLKUP2: [1, 500, 163]; IDX_EMP_SALARY(E)5 #SSEK2: [1, 500, 163]; scan_type(ASC), IDX_EMP_SALARY(EMP as E), scan_range(exp48,max], is_global(0)6 #SPL2: [1, 1, 30]; key_num(1), spool_num(0), is_atom(TRUE), has_var(0), sites(-)7 #PRJT2: [1, 1, 30]; exp_num(1), is_atom(TRUE)8 #AAGR2: [1, 1, 30]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)9 #SSCN: [1, 10000, 30]; IDX_EMP_SALARY(EMP); btr_scan(1); is_global(0)Kingbase
SQL> explain select * from emp e where salary >(select avg(salary) from emp); QUERY PLAN----------------------------------------------------------------------------------------------- Bitmap Heap Scan on emp e (cost=24352.32..38694.98 rows=333333 width=42) Recheck Cond: (salary > $1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=16384.55..16384.56 rows=1 width=8) -> Gather (cost=16384.33..16384.54 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=15384.33..15384.34 rows=1 width=32) -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=8) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..7884.42 rows=333333 width=0) Index Cond: (salary > $1)YashanDB
SQL> explain plan for select * from emp e where salary >(select avg(salary) from emp);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | SUBQUERY | QUERY[1] | | | | || 2 | AGGREGATE | | | 1| 27( 0)| || 3 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 3301| 54( 0)| ||* 5 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 3301| 9( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Vastbase
SQL> explain select * from emp e where salary >(select avg(salary) from emp); QUERY PLAN----------------------------------------------------------------------- Seq Scan on emp e (cost=243.01..486.01 rows=3333 width=44) Filter: (salary > $0) InitPlan 1 (returns $0) -> Aggregate (cost=243.00..243.01 rows=1 width=40) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)5)簡單謂詞推入
簡單過濾謂詞推入，即簡單地將主查詢中作用于子查詢的過濾謂詞推入子查詢中。它是屬于啟發式查詢轉換技術，只要滿足條件就會進行轉換。
Oracle
SQL> explain plan for select * from (select * from emp where salary<1100) v where dept_id <10;SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 80 | 2480 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 80 | 2480 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("EMP"."DEPT_ID"<10 AND "SALARY"<1100)MySQL
mysql> explain select * from (select * from emp where salary<1100) v where dept_id <10;+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+| 1 | SIMPLE | emp | NULL | range | idx_emp_salary | idx_emp_salary | 5 | NULL | 459 | 33.33 | Using index condition; Using where |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`dept_id` < 10) and (`testdb`.`emp`.`salary` < 1100)) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+DM
SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10;1 #NSET2: [1, 44, 163]2 #PRJT2: [1, 44, 163]; exp_num(6), is_atom(FALSE)3 #SLCT2: [1, 44, 163]; EMP.DEPT_ID < var14 #BLKUP2: [1, 885, 163]; IDX_EMP_SALARY(EMP)5 #SSEK2: [1, 885, 163]; scan_type(ASC), IDX_EMP_SALARY(EMP), scan_range(null2,exp_cast(1100)), is_global(0)Kingbase
SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10; QUERY PLAN------------------------------------------------------------------------------------ Bitmap Heap Scan on emp (cost=2162.79..13707.87 rows=7293 width=42) Recheck Cond: (salary < '1100'::double precision) Filter: (dept_id < '10'::numeric) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..2160.97 rows=91272 width=0) Index Cond: (salary < '1100'::double precision)YashanDB
SQL> explain plan for select * from (select * from emp where salary<1100) v where dept_id <10; +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | |* 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 277| 7( 0)| | |* 2 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 3( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ Operation Information (identified by operation id): --------------------------------------------------- 1 - Predicate : filter("EMP"."DEPT_ID" < 10) 2 - Predicate : access("EMP"."SALARY" < 1100)Vastbase
SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10; QUERY PLAN -------------------------------------------------------------------------------- Bitmap Heap Scan on emp (cost=23.02..154.52 rows=76 width=44) Recheck Cond: (salary < 1100::double precision) Filter: (dept_id < 10::number) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0) Index Cond: (salary < 1100::double precision) explain select * from (select * from emp where salaryBitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0)\n Index Cond: (salary6)子查詢謂詞遷移
謂詞遷移是指在含有多個子查詢的復雜查詢中，將其中一個子查詢的謂詞條件提取出來，并推入另外的子查詢中，成為謂詞的一部分。
Oracle
SQL> explain plan for select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; SQL> select * from table(dbms_xplan.display); ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 132 | 20 (5)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 4 | 132 | 20 (5)| 00:00:01 | | 3 | VIEW | | 2 | 52 | 16 (7)| 00:00:01 | | 4 | HASH GROUP BY | | 2 | 14 | 16 (7)| 00:00:01 | |* 5 | TABLE ACCESS FULL | EMP | 199 | 1393 | 15 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_EMP_DEPTID | 2 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 14 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("DEPT_ID"=10 OR "DEPT_ID"=20) 6 - access("V1"."DEPT_ID"="DEPT_ID") filter("DEPT_ID"=10 OR "DEPT_ID"=20) * 在第5步的分組判斷中，已入后面子查詢中的謂詞條件，提前做了過濾MySQL
mysql> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 20.00 | Using where || 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | testdb.emp.dept_id | 10 | 100.00 | NULL || 2 | DERIVED | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary |+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+
mysql> show warnings;+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `v1`.`dept_id` AS `dept_id`,`v1`.`min(emp_id)` AS `min(emp_id)`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_id` AS `emp_id` from (/* select#2 */ select `testdb`.`emp`.`dept_id` AS `dept_id`,min(`testdb`.`emp`.`emp_id`) AS `min(emp_id)` from `testdb`.`emp` group by `testdb`.`emp`.`dept_id`) `v1` join `testdb`.`emp` where ((`v1`.`dept_id` = `testdb`.`emp`.`dept_id`) and (`testdb`.`emp`.`dept_id` in (10,20))) |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+DM
SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;1 #NSET2: [6, 54, 162]2 #PRJT2: [6, 54, 162]; exp_num(4), is_atom(FALSE)3 #HAGR2: [6, 54, 162]; grp_num(4), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(TMP_PHA_ALIAS_16778408.DEPT_ID, EMP.EMP_ID, EMP.DEPT_ID, EMP.ROWID)4 #HASH RIGHT SEMI JOIN2: [5, 73, 162]; n_keys(1) KEY(DMTEMPVIEW_889193644.colname=EMP.DEPT_ID) KEY_NULL_EQU(0)5 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)6 #HASH2 INNER JOIN: [5, 73, 162]; KEY_NUM(1); KEY(TMP_PHA_ALIAS_16778408.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)7 #HASH2 INNER JOIN: [2, 500, 90]; KEY_NUM(1); KEY(DMTEMPVIEW_889193642.colname=TMP_PHA_ALIAS_16778408.DEPT_ID) KEY_NULL_EQU(0)8 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)9 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP as TMP_PHA_ALIAS_16778408); btr_scan(1)10 #CSCN2: [1, 10000, 72]; INDEX33555484(EMP); btr_scan(1)Kingbase
SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; QUERY PLAN---------------------------------------------------------------------------------------------------------------- Hash Join (cost=18458.26..35835.65 rows=19400 width=48) Hash Cond: (emp.dept_id = emp_1.dept_id) -> Gather (cost=1000.00..18324.33 rows=19400 width=11) Workers Planned: 2 -> Parallel Seq Scan on emp (cost=0.00..15384.33 rows=8083 width=11) Filter: (dept_id = ANY ('{10,20}'::numeric[])) -> Hash (cost=17457.00..17457.00 rows=101 width=37) -> Finalize GroupAggregate (cost=17430.40..17455.99 rows=101 width=37) Group Key: emp_1.dept_id -> Gather Merge (cost=17430.40..17453.97 rows=202 width=37) Workers Planned: 2 -> Sort (cost=16430.37..16430.63 rows=101 width=37) Sort Key: emp_1.dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=37) Group Key: emp_1.dept_id -> Parallel Seq Scan on emp emp_1 (cost=0.00..14342.67 rows=416667 width=11)YashanDB
SQL> explain plan for select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN INNER | | | 204| 96( 0)| || 2 | JOIN FILTER USE | | | 100| 48( 0)| || 3 | VIEW | | | 100| 48( 0)| || 4 | HASH GROUP | | | 100| 48( 0)| ||* 5 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| ||* 6 | JOIN FILTER CREATE | | | 201| 46( 0)| ||* 7 | TABLE ACCESS FULL | EMP | TESTUSER | 201| 46( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):--------------------------------------------------- 1 - Predicate : access("V1"."DEPT_ID" = "EMP"."DEPT_ID") 4 - Group Expression: ("EMP"."DEPT_ID") 5 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "EMP"."DEPT_ID") 6 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "EMP"."DEPT_ID") 7 - Predicate : filter("EMP"."DEPT_ID" IN [10, 20])Vastbase
SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; QUERY PLAN-------------------------------------------------------------------------- Nested Loop (cost=243.90..492.38 rows=181 width=56) Join Filter: (testuser.emp.dept_id = testuser.emp.dept_id) -> Seq Scan on emp (cost=0.00..243.00 rows=181 width=16) Filter: (dept_id = ANY ('{10,20}'::number[])) -> Materialize (cost=243.90..243.95 rows=2 width=40) -> HashAggregate (cost=243.90..243.92 rows=2 width=48) Group By Key: testuser.emp.dept_id責任編輯：武曉燕
來源：
韓鋒頻道
