國(guó)產(chǎn)集中庫(kù)SQL能力評(píng)測(cè) - 子查詢
原創(chuàng)
作者：
韓鋒
優(yōu)化器需要考慮如何最有效地執(zhí)行嵌套查詢，這可能涉及到多個(gè)表的連接、復(fù)雜的條件邏輯等，這對(duì)于優(yōu)化器挑戰(zhàn)是很大的。
子查詢（Subquery），是SQL查詢中的一種，它允許一個(gè)查詢嵌套在另一個(gè)查詢中。子查詢通常用在SELECT、INSERT、UPDATE或DELETE語(yǔ)句中，作為一個(gè)單獨(dú)的查詢單元來(lái)返回?cái)?shù)據(jù)，這些數(shù)據(jù)可以被外部查詢使用。子查詢通常是數(shù)據(jù)庫(kù)開(kāi)發(fā)中自然邏輯的體現(xiàn)，但對(duì)于數(shù)據(jù)庫(kù)而言會(huì)帶來(lái)很大挑戰(zhàn)。一方面，子查詢可能使得數(shù)據(jù)庫(kù)的查詢優(yōu)化器難以生成高效的執(zhí)行計(jì)劃，優(yōu)化器需要考慮如何最有效地執(zhí)行嵌套查詢，這可能涉及到多個(gè)表的連接、復(fù)雜的條件邏輯等，這對(duì)于優(yōu)化器挑戰(zhàn)是很大的。另一方面，子查詢可能會(huì)降低SQL代碼的可讀性和維護(hù)性，使得優(yōu)化和調(diào)試變得更加困難，特別是層次嵌套很深的子查詢。此外，子查詢還可能會(huì)改變數(shù)據(jù)訪問(wèn)模式、若邏輯復(fù)雜還可能影響索引使用等等弊端。本文將對(duì)比不同數(shù)據(jù)庫(kù)對(duì)子查詢的處理方式差異。
1. 子查詢分類
1)子查詢分類
圖片
2)Oracle 示例
-- 【子查詢位置】-- 標(biāo)量子查詢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;-- 內(nèi)聯(lián)子查詢select * from (select * from emp where salary<1500) where dept_id <50;-- 嵌套子查詢select * from emp where salary=(select max(salary) from emp);
-- 【與主查詢關(guān)聯(lián)】-- 關(guān)聯(lián)子查詢select emp_id,emp_name,salaryfrom emp e1where salary=(select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 反關(guān)聯(lián)子查詢select emp_id,emp_name,salaryfrom emp e1where salary not in (select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 非關(guān)聯(lián)子查詢select count(*) from empwhere salary<(select avg(salary) from emp);
-- [子查詢結(jié)果集]-- 標(biāo)量子查詢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)國(guó)產(chǎn)庫(kù)支持情況
國(guó)產(chǎn)數(shù)據(jù)庫(kù)（含MySQL）都支持了上述子查詢寫法，除了MySQL需要稍微調(diào)整下寫法外，其他都可以無(wú)需修改直接使用。
圖片
2. 子查詢優(yōu)化
子查詢有多種優(yōu)化方式，下面以 Oracle 支持的子查詢優(yōu)化手段為目標(biāo)，看看國(guó)產(chǎn)數(shù)據(jù)庫(kù)（含MySQL）支持情況如何。特說(shuō)明，國(guó)產(chǎn)數(shù)據(jù)庫(kù)可能含有其他子查詢優(yōu)化手段，下文不代表國(guó)產(chǎn)數(shù)據(jù)庫(kù)針對(duì)子查詢的全部?jī)?yōu)化能力。
圖片
1)子查詢展開(kāi)/解嵌套
子查詢展開(kāi)是優(yōu)化器處理帶子查詢的目標(biāo)SQL的一種優(yōu)化手段，它是指優(yōu)化器不再將目標(biāo)SQL中的子查詢當(dāng)作一個(gè)獨(dú)立的處理單元來(lái)單獨(dú)執(zhí)行，而是將該子查詢轉(zhuǎn)換為它自身和外部查詢之間等價(jià)的表連接。從而獲得更優(yōu)的執(zhí)行計(jì)劃。子查詢展開(kāi)有兩種形式，一種是將子查詢拆開(kāi)(即將該子查詢中的表、視圖從子查詢拿出來(lái)，然后和外部查詢中的表、視圖做表連接)；一種是不拆開(kāi)但是會(huì)把該子查詢轉(zhuǎn)換為一個(gè)內(nèi)嵌視圖(Inline View)，然后再和外部查詢中的表、視圖做表連接。前者屬于啟發(fā)式查詢轉(zhuǎn)換，后者屬于基于代價(jià)的轉(zhuǎn)換。
子查詢展開(kāi)通常都會(huì)提高原SQL的執(zhí)行效率，因?yàn)槿绻璖QL不做子查詢展開(kāi)，那么通常情況下該子查詢就會(huì)在其執(zhí)行計(jì)劃的最后一步才執(zhí)行，并且會(huì)走FILTER類型的執(zhí)行計(jì)劃，這也意味著對(duì)于外部查詢所在結(jié)果集中的每一條記錄，該子查詢都會(huì)被當(dāng)作一個(gè)獨(dú)立的執(zhí)行單元來(lái)執(zhí)行一次，外部查詢所在的結(jié)果集有多少條記錄，該子查詢就會(huì)被執(zhí)行多少次(可以近似這么理解，實(shí)際上并不完全是這樣)。這種執(zhí)行方式的執(zhí)行效率通常都不會(huì)太高，尤其是在子查詢中包含兩個(gè)或者兩個(gè)以上表連接時(shí)，此時(shí)做子查詢展開(kāi)后的執(zhí)行效率往往會(huì)比走FILTER類型的執(zhí)行計(jì)劃高很多，因?yàn)榇藭r(shí)優(yōu)化器就會(huì)有其他更多、更高效的執(zhí)行路徑(比如哈希連接)可以選擇。
Oracle
-- IN/EXISTS轉(zhuǎn)換為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 |------------------------------------------------------------------------------* 優(yōu)化器將IN或EXISTS子句中的子查詢展開(kāi)(反嵌套)，使得優(yōu)化器選擇半關(guān)聯(lián)(SEMI-JOIN)操作。這種轉(zhuǎn)換屬于啟發(fā)式查詢轉(zhuǎn)換。
-- NOT IN/EXISTS轉(zhuǎn)換為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 |------------------------------------------------------------------------------* 優(yōu)化器將NOT IN或NOT EXISTS子句中的子查詢展開(kāi)(反嵌套)，使得優(yōu)化器選擇反關(guān)聯(lián)(ANTI-JOIN)操作。這種轉(zhuǎn)換屬于基于代價(jià)的查詢轉(zhuǎn)換。
-- NOT IN/NOT EXISTS轉(zhuǎn)換為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字段允許為空，優(yōu)化器將NOT IN/NOT EXISTS子句中的子查詢展開(kāi)(反嵌套)，使得優(yōu)化器能選擇對(duì)空值敏感的反關(guān)聯(lián)(Null-Aware ANTI-JOIN)操作。* 這種轉(zhuǎn)換屬于啟發(fā)式查詢轉(zhuǎn)換。對(duì)空值敏感的反關(guān)聯(lián)操作能在關(guān)聯(lián)數(shù)據(jù)時(shí)關(guān)注到空值的存在，從而避免使用代價(jià)昂貴的操作(如笛卡爾積關(guān)聯(lián))來(lái)獲取邏輯結(jié)果。
-- 互關(guān)聯(lián)子查詢轉(zhuǎn)換為內(nèi)聯(lián)視圖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 |--------------------------------------------------------------------------------* 示例中，關(guān)聯(lián)謂詞中存在子查詢，優(yōu)化器對(duì)互關(guān)聯(lián)子查詢的反嵌套，會(huì)將子查詢構(gòu)造出一個(gè)內(nèi)聯(lián)視圖，并將內(nèi)聯(lián)視圖與主查詢中的表進(jìn)行關(guān)聯(lián)。這種轉(zhuǎn)換屬于啟發(fā)式查詢轉(zhuǎn)換。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 |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+* 退化為嵌套循環(huán)表連接
-- 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 |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+* 嵌套循環(huán)表連接+物化子查詢
-- 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 |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+
-- 互關(guān)聯(lián)子查詢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)
-- 互關(guān)聯(lián)子查詢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) -- 互關(guān)聯(lián)子查詢 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轉(zhuǎn)換為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轉(zhuǎn)換為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轉(zhuǎn)換為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)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- 互關(guān)聯(lián)子查詢轉(zhuǎn)換為內(nèi)聯(lián)視圖 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) -- 互關(guān)聯(lián)子查詢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)標(biāo)量子查詢合并
針對(duì)含有標(biāo)量子查詢的情況，優(yōu)化器會(huì)嘗試與主查詢中的對(duì)象進(jìn)行合并關(guān)聯(lián)操作。
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)子查詢合并
當(dāng)優(yōu)化器未對(duì)子查詢做反嵌套的情況下，可以將兩個(gè)兼容的子查詢合并為一個(gè)子查詢。
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)子查詢推入
子查詢推入是一項(xiàng)對(duì)未能合并或者反嵌套的子查詢優(yōu)化的補(bǔ)充優(yōu)化技術(shù)。通常情況下，未能合并或者反嵌套的子查詢的子計(jì)劃會(huì)被放置在整個(gè)查詢計(jì)劃的最后步驟執(zhí)行，而子查詢推進(jìn)使得子查詢能夠提前被評(píng)估，使之可以出現(xiàn)在整體執(zhí)行計(jì)劃的較早步驟，從而獲得更優(yōu)的執(zhí)行計(jì)劃。
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 |-----------------------------------------------------------------------------------------* 如禁用子查詢推入功能，執(zhí)行計(jì)劃則退化為FILTER，子查詢會(huì)被最后執(zhí)行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)簡(jiǎn)單謂詞推入
簡(jiǎn)單過(guò)濾謂詞推入，即簡(jiǎn)單地將主查詢中作用于子查詢的過(guò)濾謂詞推入子查詢中。它是屬于啟發(fā)式查詢轉(zhuǎn)換技術(shù)，只要滿足條件就會(huì)進(jìn)行轉(zhuǎn)換。
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)子查詢謂詞遷移
謂詞遷移是指在含有多個(gè)子查詢的復(fù)雜查詢中，將其中一個(gè)子查詢的謂詞條件提取出來(lái)，并推入另外的子查詢中，成為謂詞的一部分。
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步的分組判斷中，已入后面子查詢中的謂詞條件，提前做了過(guò)濾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責(zé)任編輯：武曉燕
來(lái)源：
韓鋒頻道
