國產(chǎn)集中庫SQL能力評(píng)測 - 排序和分組聚合
原創(chuàng)
作者：
韓鋒
排序是數(shù)據(jù)庫內(nèi)比較消耗資源的一類操作，特別是在結(jié)果比較大的情況下。因此在數(shù)據(jù)庫處理上，應(yīng)盡量規(guī)避排序的行為。這里講的排序，不僅僅是指 ORDER BY 的操作，很多操作都會(huì)引發(fā)排序行為。
這里談?wù)勁判蚺c分組聚合。這兩類操作往往存在一定相關(guān)性。
1. 排序
排序是數(shù)據(jù)庫內(nèi)比較消耗資源的一類操作，特別是在結(jié)果比較大的情況下。因此在數(shù)據(jù)庫處理上，應(yīng)盡量規(guī)避排序的行為。這里講的排序，不僅僅是指 ORDER BY 的操作，很多操作都會(huì)引發(fā)排序行為。常見操作包括：生成索引的操作（因?yàn)樗饕怯行蚪Y(jié)構(gòu)）、某些SQL（如帶有DISTINCT、ORDER BY、GROUP BY、UNION、MINUS、INTERSET、CONNECT BY和CONNECT BY ROLLUP子句）、排序合并連接（兩個(gè)結(jié)果集排序后關(guān)聯(lián)）、收集統(tǒng)計(jì)信息、其他如位圖變換、分析函數(shù)等。在上面這些操作中，有些是為了進(jìn)行排序，有些是為了其他目的（如去重等）；因此數(shù)據(jù)庫是可以考慮優(yōu)化此類排序行為的。例如Oracle數(shù)據(jù)庫，在10g以前的版本是通過SORT GROUP BY完成分組的，但在10g之后默認(rèn)提供了HASH GROUP BY，這樣效率更高，當(dāng)然其結(jié)果集不保證有序了。
（1）常見排序操作
下面針對(duì)常見的排序類操作，抽象出一組測例，看看Oracle和國產(chǎn)數(shù)據(jù)庫的行為如何。
SORT UNIQUE
排序去重類，把查詢語句的輸出結(jié)果變?yōu)槲ㄒ患系倪^程。使用場景包括：語句中使用了DISTINCT、子查詢向主查詢提供執(zhí)行結(jié)果。在Oracle 10g以后的版本中，SORT UNIQUE 變成 HASH UNIQUE，利用新的HASH算法代替了傳統(tǒng)的排序。但在使用子查詢的場景下，因?yàn)閮?yōu)先執(zhí)行子查詢，子查詢放在主查詢之前。由于主查詢的結(jié)果必須存在于子查詢的結(jié)果中。在這里要將作為"M"集合的子查詢轉(zhuǎn)換為不允許重復(fù)元素存在的"1"集合，所以執(zhí)行了SORT(UNIQUE)。
SORT AGGREGATE
這是指在沒有 GROUP BY 的前提下，使用統(tǒng)計(jì)函數(shù)對(duì)全部數(shù)據(jù)對(duì)象進(jìn)行計(jì)算時(shí)所顯示出來的執(zhí)行計(jì)劃。在使用SUM、COUNT、MIN、MAX、AVG等統(tǒng)計(jì)函數(shù)時(shí)并不執(zhí)行一般排序動(dòng)作。實(shí)際上是讀取每一行數(shù)據(jù)為對(duì)象進(jìn)行求和、計(jì)數(shù)、比較大小等操作，可通過一個(gè)全局變量+全表/全索引掃描來實(shí)現(xiàn)。
SORT GROUP BY
該操作是將數(shù)據(jù)行向不同分組中聚集的操作，即依據(jù)查詢語句中所使用的GROUP BY而進(jìn)行相關(guān)操作，為了進(jìn)行分組就只能排序。需要分組的數(shù)據(jù)量越大，代價(jià)就越大。在10gR2以后的版本中，哈希分組-HASH (GROUPBY)。在處理海量數(shù)據(jù)時(shí)使用哈希處理比使用排序處理更有效。
SORT ORDER BY
當(dāng)對(duì)一個(gè)不能滿足索引列進(jìn)行排序時(shí)，就需要一個(gè)SORT ORDER BY。這里可以有個(gè)優(yōu)化，針對(duì)取出排序結(jié)果前幾條的場景，是可以提前結(jié)束排序動(dòng)作，節(jié)省資源。
SORT JOIN
在表關(guān)聯(lián)的場景中，如果行按照連接鍵排序，在排序合并連接時(shí)將會(huì)發(fā)生SORT JOIN。SORT JOIN 發(fā)生在出現(xiàn)MERGE JOIN的情況下，兩張關(guān)聯(lián)的表要各自做SORT，然后在MERGE。
（2）Oracle 測試示例
-- SORT UNIQUESQL> explain plan for select distinct dept_id,emp_name from emp where salary<1100;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 898 | 14368 | 16 (7)| 00:00:01 || 1 | HASH UNIQUE | | 898 | 14368 | 16 (7)| 00:00:01 ||* 2 | TABLE ACCESS FULL| EMP | 898 | 14368 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary<1100);SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 2300 | 18 (6)| 00:00:01 || 1 | MERGE JOIN SEMI | | 100 | 2300 | 18 (6)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 ||* 4 | SORT UNIQUE | | 898 | 7184 | 16 (7)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMP | 898 | 7184 | 15 (0)| 00:00:01 |----------------------------------------------------------------------------------------
-- SORT AGGREGATESQL> explain plan for select sum(salary) from emp;SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 10 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 5 | | || 2 | INDEX FAST FULL SCAN| IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |----------------------------------------------------------------------------------------
SQL> explain plan for select min(salary),max(salary) from emp;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 15 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 5 | | || 2 | TABLE ACCESS FULL| EMP | 10000 | 50000 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
-- SORT GROUP BYSQL> explain plan for select dept_id,count(*) from emp group by dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 300 | 16 (7)| 00:00:01 || 1 | HASH GROUP BY | | 100 | 300 | 16 (7)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 30000 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
-- SORT(ORDER BY)SQL> explain plan for select * from emp order by dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 273K| 16 (7)| 00:00:01 || 1 | SORT ORDER BY | | 10000 | 273K| 16 (7)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 273K| 15 (0)| 00:00:01 |---------------------------------------------------------------------------
SQL> explain plan for select * from (select * from emp order by salary desc) where rownum<=10;SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10 | 650 | 16 (7)| 00:00:01 ||* 1 | COUNT STOPKEY | | | | | || 2 | VIEW | | 10000 | 634K| 16 (7)| 00:00:01 ||* 3 | SORT ORDER BY STOPKEY| | 10000 | 273K| 16 (7)| 00:00:01 || 4 | TABLE ACCESS FULL | EMP | 10000 | 273K| 15 (0)| 00:00:01 |--------------------------------------------------------------------------------
-- SORT JOINSQL> explain plan for select /*+ use_merge(e d)*/ e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id;SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 205K| 20 (15)|| 1 | MERGE JOIN | | 10000 | 205K| 20 (15)|| 2 | SORT JOIN | | 100 | 1000 | 4 (50)|| 3 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)||* 4 | HASH JOIN | | | | || 5 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)|| 6 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)||* 7 | SORT JOIN | | 10000 | 107K| 16 (7)|| 8 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)|----------------------------------------------------------------------------------（3）國產(chǎn)庫測試示例
下面是針對(duì)上述測例，國產(chǎn)庫的行為如何？先來看看整體結(jié)果。
MySQL
-- SORT UNIQUEmysql> explain select distinct dept_id,emp_name from emp where salary<1100;+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+| 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 | 100.00 | Using index condition; Using temporary |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+----------------------------------------+
mysql> explain select * from dept where dept_id in (select dept_id from emp where salary<1100);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-----------------------+| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | NULL || 2 | MATERIALIZED | emp | NULL | range | idx_emp_salary | idx_emp_salary | 5 | NULL | 459 | 100.00 | Using index condition |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-----------------------+
-- SORT AGGREGATEmysql> explain select sum(salary) from emp;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_salary | 5 | NULL | 10117 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+
mysql> explain select min(salary),max(salary) from emp;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
-- SORT GROUP BYmysql> explain select dept_id,count(*) from emp group by dept_id;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
-- SORT(ORDER BY)mysql> explain select * from emp order by dept_id;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using filesort |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
mysql> explain select * from (select * from emp order by salary desc) a limit 10;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+---------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+---------------------+| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_salary | 5 | NULL | 10 | 100.00 | Backward index scan |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+---------------------+
-- SORT JOINmysql> explain select e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.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 | 10117 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+DM
-- SORT UNIQUESQL> explain select distinct dept_id,emp_name from emp where salary<1100;1 #NSET2: [2, 885, 108]2 #PRJT2: [2, 885, 108]; exp_num(2), is_atom(FALSE)3 #DISTINCT: [2, 885, 108]4 #BLKUP2: [1, 885, 108]; IDX_EMP_SALARY(EMP)5 #SSEK2: [1, 885, 108]; scan_type(ASC), IDX_EMP_SALARY(EMP), scan_range(null2,exp_cast(1100)), is_global(0)
SQL> explain select * from dept where dept_id in (select dept_id from emp where salary<1100);1 #NSET2: [1, 100, 138]2 #PRJT2: [1, 100, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [1, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #BLKUP2: [1, 885, 60]; IDX_EMP_SALARY(EMP)6 #SSEK2: [1, 885, 60]; scan_type(ASC), IDX_EMP_SALARY(EMP), scan_range(null2,exp_cast(1100)), is_global(0)
-- SORT AGGREGATESQL> explain select sum(salary) from emp;1 #NSET2: [1, 1, 30]2 #PRJT2: [1, 1, 30]; exp_num(1), is_atom(FALSE)3 #AAGR2: [1, 1, 30]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)4 #SSCN: [1, 10000, 30]; IDX_EMP_SALARY(EMP); btr_scan(1); is_global(0)
SQL> explain select min(salary),max(salary) from emp;1 #NSET2: [1, 1, 30]2 #PRJT2: [1, 1, 30]; exp_num(2), is_atom(FALSE)3 #FAGR2: [1, 1, 30]; sfun_num(2), IDX_EMP_SALARY
-- SORT GROUP BYSQL> explain select dept_id,count(*) from emp group by dept_id;1 #NSET2: [2, 100, 30]2 #PRJT2: [2, 100, 30]; exp_num(2), is_atom(FALSE)3 #HAGR2: [2, 100, 30]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID)4 #CSCN2: [1, 10000, 30]; INDEX33555484(EMP); btr_scan(1)
-- SORT(ORDER BY)SQL> explain select * from emp order by dept_id;1 #NSET2: [2, 10000, 163]2 #PRJT2: [2, 10000, 163]; exp_num(6), is_atom(FALSE)3 #SORT3: [2, 10000, 163]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)4 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
SQL> explain select * from (select * from emp order by salary desc) a limit 10;1 #NSET2: [4, 10, 205]2 #PRJT2: [4, 10, 205]; exp_num(6), is_atom(FALSE)3 #SORT3: [4, 10, 205]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)4 #HASH2 INNER JOIN: [3, 10, 205]; LKEY_UNIQUE KEY_NUM(1); KEY(DMTEMPVIEW_889193478.colname=EMP.ROWID) KEY_NULL_EQU(0)5 #NEST LOOP INDEX JOIN2: [3, 10, 205]6 #ACTRL: [3, 10, 205]7 #DISTINCT: [3, 10, 42]8 #PRJT2: [2, 10, 42]; exp_num(1), is_atom(FALSE)9 #PRJT2: [2, 10, 42]; exp_num(1), is_atom(FALSE)10 #SORT3: [2, 10, 42]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(1), is_adaptive(0)11 #SSCN: [1, 10000, 42]; IDX_EMP_SALARY(EMP); btr_scan(1); is_global(0)12 #CSEK2: [1, 1, 0]; scan_type(ASC), INDEX33555484(EMP), scan_range[DMTEMPVIEW_889193478.colname,DMTEMPVIEW_889193478.colname]13 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)
-- SORT JOINSQL> explain select e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id;1 #NSET2: [2, 10000, 156]2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)
SQL> explain select /*+ use_merge(e d)*/ e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id;1 #NSET2: [638801, 10000, 156]2 #PRJT2: [638801, 10000, 156]; exp_num(2), is_atom(FALSE)3 #SLCT2: [638801, 10000, 156]; E.DEPT_ID = D.DEPT_ID4 #NEST LOOP INNER JOIN2: [638801, 10000, 156]5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)6 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)KingBase
-- SORT UNIQUETEST=# explain select distinct dept_id,emp_name from emp where salary<1100; QUERY PLAN------------------------------------------------------------------------------------------------ Unique (cost=22893.03..23577.57 rows=91272 width=20) -> Sort (cost=22893.03..23121.21 rows=91272 width=20) Sort Key: dept_id, emp_name -> Bitmap Heap Scan on emp (cost=2183.78..13500.68 rows=91272 width=20) Recheck Cond: (salary < '1100'::double precision) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..2160.97 rows=91272 width=0) Index Cond: (salary < '1100'::double precision)
TEST=# explain select * from dept where dept_id in (select dept_id from emp where salary<1100); QUERY PLAN------------------------------------------------------------------------------------------------------ Hash Join (cost=13731.14..13734.51 rows=100 width=29) Hash Cond: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Hash (cost=13729.87..13729.87 rows=101 width=5) -> HashAggregate (cost=13728.86..13729.87 rows=101 width=5) Group Key: emp.dept_id -> Bitmap Heap Scan on emp (cost=2183.78..13500.68 rows=91272 width=5) Recheck Cond: (salary < '1100'::double precision) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..2160.97 rows=91272 width=0) Index Cond: (salary < '1100'::double precision)
-- SORT AGGREGATETEST=# explain select sum(salary) from emp; QUERY PLAN--------------------------------------------------------------------------------------- Finalize Aggregate (cost=16384.55..16384.56 rows=1 width=8) -> Gather (cost=16384.33..16384.54 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=15384.33..15384.34 rows=1 width=8) -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=8)
TEST=# explain select min(salary),max(salary) from emp; QUERY PLAN--------------------------------------------------------------------------------------------------------------------- Result (cost=1.01..1.02 rows=1 width=16) InitPlan 1 (returns $0) -> Limit (cost=0.42..0.50 rows=1 width=8) -> Index Only Scan using idx_emp_salary on emp (cost=0.42..71128.49 rows=904233 width=8) Index Cond: (salary IS NOT NULL) InitPlan 2 (returns $1) -> Limit (cost=0.42..0.50 rows=1 width=8) -> Index Only Scan Backward using idx_emp_salary on emp emp_1 (cost=0.42..71128.49 rows=904233 width=8) Index Cond: (salary IS NOT NULL)
-- SORT GROUP BYTEST=# explain select dept_id,count(*) from emp group by dept_id; QUERY PLAN--------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=17430.40..17455.99 rows=101 width=13) Group Key: dept_id -> Gather Merge (cost=17430.40..17453.97 rows=202 width=13) Workers Planned: 2 -> Sort (cost=16430.37..16430.63 rows=101 width=13) Sort Key: dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=13) Group Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=5)
-- SORT(ORDER BY)TEST=# explain select * from emp order by dept_id; QUERY PLAN---------------------------------------------------------------------------------- Gather Merge (cost=67056.00..164285.09 rows=833334 width=42) Workers Planned: 2 -> Sort (cost=66055.98..67097.65 rows=416667 width=42) Sort Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=42)
TEST=# explain select * from (select * from emp order by salary desc) a limit 10; QUERY PLAN---------------------------------------------------------------------------------------------------- Limit (cost=0.42..1.24 rows=10 width=42) -> Index Scan Backward using idx_emp_salary on emp (cost=0.42..71848.41 rows=1000000 width=42)
-- SORT JOINTEST=# explain select e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)YashanDB
-- SORT UNIQUESQL> explain select distinct dept_id,emp_name from emp where salary<1100;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | HASH DISTINCT | | | 920| 9( 0)|| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 920| 7( 0)||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 3( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
SQL> explain select * from dept where dept_id in (select dept_id from emp where salary<1100);+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | NESTED INDEX LOOPS INNER | | | 50| 8( 0)|| 2 | SORT DISTINCT | | | 50| 8( 0)|| 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 920| 7( 0)||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 3( 0)|| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)||* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- SORT AGGREGATESQL> explain select sum(salary) from emp;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | AGGREGATE | | | 1| 27( 0)|| 2 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
SQL> explain select min(salary),max(salary) from emp;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | AGGREGATE | | | 1| 27( 0)|| 2 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- SORT GROUP BYSQL> explain select dept_id,count(*) from emp group by dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | HASH GROUP | | | 100| 48( 0)|| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- SORT(ORDER BY)SQL> explain select * from emp order by dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | SORT | | | 10000| 224( 0)|| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
SQL> explain select * from (select * from emp order by salary desc) a limit 10;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | WINDOW | | | 10| 47( 0)|| 2 | VIEW | | | 10000| 47( 0)|| 3 | ORDER BY STOPKEY | | | 10000| 47( 0)|| 4 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- SORT JOINSQL> explain select e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)|| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
SQL> explain select /*+ use_merge(e d)*/ e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | ||* 1 | MERGE JOIN INNER | | | 10000| 2295( 0)|| 2 | MERGE SORT | | | | || 3 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|| 4 | MERGE SORT | | | | || 5 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+Vastbase
-- SORT UNIQUEvastbase=> explain select distinct dept_id,emp_name from emp where salary<1100; QUERY PLAN-------------------------------------------------------------------------------------- HashAggregate (cost=156.97..165.97 rows=900 width=20) Group By Key: dept_id, emp_name -> Bitmap Heap Scan on emp (cost=23.23..152.47 rows=900 width=20) Recheck Cond: (salary < 1100::double precision) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0) Index Cond: (salary < 1100::double precision)
vastbase=> explain select * from dept where dept_id in (select dept_id from emp where salary<1100); QUERY PLAN-------------------------------------------------------------------------------------------------- Hash Join (cost=154.95..157.30 rows=100 width=30) Hash Cond: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=154.82..154.82 rows=10 width=8) -> HashAggregate (cost=154.72..154.82 rows=10 width=8) Group By Key: emp.dept_id -> Bitmap Heap Scan on emp (cost=23.23..152.47 rows=900 width=8) Recheck Cond: (salary < 1100::double precision) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0) Index Cond: (salary < 1100::double precision)
-- SORT AGGREGATEvastbase=> explain select sum(salary) from emp; QUERY PLAN--------------------------------------------------------------- Aggregate (cost=243.00..243.01 rows=1 width=16) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)
vastbase=> explain select min(salary),max(salary) from emp; QUERY PLAN----------------------------------------------------------------------------------------------------------- Result (cost=0.17..0.18 rows=1 width=0) InitPlan 1 (returns $0) -> Limit (cost=0.00..0.09 rows=1 width=8) -> Index Only Scan using idx_emp_salary on emp (cost=0.00..785.75 rows=9000 width=8) Index Cond: (salary IS NOT NULL) InitPlan 2 (returns $1) -> Limit (cost=0.00..0.09 rows=1 width=8) -> Index Only Scan Backward using idx_emp_salary on emp (cost=0.00..785.75 rows=9000 width=8) Index Cond: (salary IS NOT NULL)
-- SORT GROUP BYvastbase=> explain select dept_id,count(*) from emp group by dept_id; QUERY PLAN--------------------------------------------------------------- HashAggregate (cost=268.00..269.01 rows=101 width=16) Group By Key: dept_id -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)
-- SORT(ORDER BY)vastbase=> explain select * from emp order by dept_id; QUERY PLAN---------------------------------------------------------------- Sort (cost=882.39..907.39 rows=10000 width=44) Sort Key: dept_id -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=44)
vastbase=> explain select * from (select * from emp order by salary desc) a limit 10; QUERY PLAN------------------------------------------------------------------------------------------------ Limit (cost=0.00..0.89 rows=10 width=44) -> Index Scan Backward using idx_emp_salary on emp (cost=0.00..794.25 rows=10000 width=44)
-- SORT JOINvastbase=> explain select e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
vastbase=> explain select /*+ use_merge(e d)*/ e.emp_name,d.dept_name from emp e,dept d where e.dept_id=d.dept_id; QUERY PLAN------------------------------------------------------------------------ Merge Join (cost=887.71..1036.99 rows=9901 width=24) Merge Cond: (d.dept_id = e.dept_id) -> Sort (cost=5.32..5.57 rows=100 width=20) Sort Key: d.dept_id -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20) -> Sort (cost=882.39..907.39 rows=10000 width=20) Sort Key: e.dept_id -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)2.分組聚合
數(shù)據(jù)庫中的分組聚合是兩類操作：分組操作是指用SQL語句將一個(gè)結(jié)果集分為若干組，并對(duì)這樣每一組進(jìn)行聚合計(jì)算；聚合操作則是基于多行記錄返回?cái)?shù)據(jù)數(shù)據(jù):平均、最大、最小值等，聚合操作必須處理輸入數(shù)據(jù)的每一行記錄，因此通常和全表掃描聯(lián)系在一起。
（1）常見分組聚合操作
針對(duì)常見的分組聚合類操作，抽象出一組測例，看看Oracle和國產(chǎn)庫的行為如何。
聚合：一般聚合
對(duì)整個(gè)結(jié)果集進(jìn)行計(jì)算，一般都是走的全表掃描，如果有索引則會(huì)走索引快速全掃描。
聚合：極值
針對(duì)結(jié)果集的最大、最小值等計(jì)算，如果是索引列，可采用一些更優(yōu)的做法，因?yàn)楹笳呤怯行虻摹?/span>
聚合：計(jì)數(shù)
對(duì)整個(gè)結(jié)果集進(jìn)行計(jì)數(shù)，一般走全表掃描，如果有不可為空的列索引，優(yōu)化器也是可以采用的。
分組：一般分組
一般分組下，可采用排序分組的方式，也可采用更為推薦的哈希分組，這樣代價(jià)更小。
分組：分組+排序
如果針對(duì)分組后的結(jié)果還需要排序操作，上面說的哈希分組就不太合適。如果通過 SORT GROUP BY能解決分組問題的同時(shí)，還能提供有序的結(jié)果集輸出，無疑效率是要比 HASH GROUP BY 更高的。
分組：分組過濾
針對(duì)分組數(shù)據(jù)進(jìn)行過濾，可以有兩種方式 WHERE 或 HAVING。如果記錄可以通過WHERE來排除，應(yīng)該在聚合發(fā)生之前就已經(jīng)被排除。相比之下，HAVING在聚合完成之后對(duì)記錄進(jìn)行排除。參與聚合的記錄越少，效果就越好，所以一般情況下WHERE子句在這方面比HAVING子句更可取。
（2）Oracle 測試示例
-- 聚合：一般聚合SQL> explain plan for select sum(salary) from emp;SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 10 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 5 | | || 2 | INDEX FAST FULL SCAN| IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |----------------------------------------------------------------------------------------
-- 聚合：極值SQL> explain plan for select max(salary),min(salary) from emp;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 15 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 5 | | || 2 | TABLE ACCESS FULL| EMP | 10000 | 50000 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
-- 聚合：計(jì)數(shù)SQL> explain plan for select count(*) from emp;SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | | || 2 | INDEX FAST FULL SCAN| EMP_PK | 10000 | 7 (0)| 00:00:01 |------------------------------------------------------------------------
-- 分組：一般分組SQL> explain plan for select dept_id,avg(salary) from emp group by dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 800 | 16 (7)| 00:00:01 || 1 | HASH GROUP BY | | 100 | 800 | 16 (7)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 80000 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
-- 分組：分組+排序SQL> explain plan for select dept_id,avg(salary) from emp group by dept_id order by dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 800 | 16 (7)| 00:00:01 || 1 | SORT GROUP BY | | 100 | 800 | 16 (7)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 80000 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
-- 分組：分組過濾（WHERE/HAVING）SQL> explain plan for select dept_id,count(*) from emp where salary<1500 group by dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 800 | 16 (7)| 00:00:01 || 1 | HASH GROUP BY | | 100 | 800 | 16 (7)| 00:00:01 ||* 2 | TABLE ACCESS FULL| EMP | 4499 | 35992 | 15 (0)| 00:00:01 |---------------------------------------------------------------------------
SQL> explain plan for select dept_id,count(*) cnt from emp group by dept_id having count(*) >2;SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5 | 15 | 16 (7)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | HASH GROUP BY | | 5 | 15 | 16 (7)| 00:00:01 || 3 | TABLE ACCESS FULL| EMP | 10000 | 30000 | 15 (0)| 00:00:01 |----------------------------------------------------------------------------（3）國產(chǎn)庫測試示例
下面是針對(duì)上述測例，國產(chǎn)庫的行為如何？先來看看整體結(jié)果
MySQL
-- 聚合：一般聚合mysql> explain select sum(salary) from emp;+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_salary | 5 | NULL | 10117 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+----------------+---------+------+-------+----------+-------------+
-- 聚合：極值mysql> explain select max(salary),min(salary) from emp;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
-- 聚合：計(jì)數(shù)mysql> explain select count(*) from emp;+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_birthday | 4 | NULL | 10117 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+------------------+---------+------+-------+----------+-------------+
-- 分組：一般分組mysql> explain select dept_id,avg(salary) from emp group by dept_id;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+
-- 分組：分組+排序mysql> explain select dept_id,avg(salary) from emp group by dept_id order by dept_id;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary; Using filesort |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
-- 分組：分組過濾（WHERE/HAVING）mysql> explain select dept_id,count(*) from emp where salary<1500 group by dept_id;+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+------------------------------+| 1 | SIMPLE | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10117 | 39.28 | Using where; Using temporary |+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+------------------------------+
mysql> explain select dept_id,count(*) cnt from emp group by dept_id having count(*) >2;+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary |+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-----------------+DM
-- 聚合：一般聚合SQL> explain select sum(salary) from emp;1 #NSET2: [1, 1, 30]2 #PRJT2: [1, 1, 30]; exp_num(1), is_atom(FALSE)3 #AAGR2: [1, 1, 30]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)4 #SSCN: [1, 10000, 30]; IDX_EMP_SALARY(EMP); btr_scan(1); is_global(0)
-- 聚合：極值SQL> explain select max(salary),min(salary) from emp;1 #NSET2: [1, 1, 30]2 #PRJT2: [1, 1, 30]; exp_num(2), is_atom(FALSE)3 #FAGR2: [1, 1, 30]; sfun_num(2), IDX_EMP_SALARY
-- 聚合：計(jì)數(shù)SQL> explain select count(*) from emp;1 #NSET2: [1, 1, 0]2 #PRJT2: [1, 1, 0]; exp_num(1), is_atom(FALSE)3 #FAGR2: [1, 1, 0]; sfun_num(1)
-- 分組：一般分組SQL> explain select dept_id,avg(salary) from emp group by dept_id;1 #NSET2: [2, 100, 60]2 #PRJT2: [2, 100, 60]; exp_num(2), is_atom(FALSE)3 #HAGR2: [2, 100, 60]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID)4 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
-- 分組：分組+排序SQL> explain select dept_id,avg(salary) from emp group by dept_id order by dept_id;1 #NSET2: [3, 100, 60]2 #PRJT2: [3, 100, 60]; exp_num(2), is_atom(FALSE)3 #SORT3: [3, 100, 60]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)4 #HAGR2: [2, 100, 60]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID)5 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
-- 分組：分組過濾（WHERE/HAVING）SQL> explain select dept_id,count(*) from emp where salary<1500 group by dept_id;1 #NSET2: [2, 45, 60]2 #PRJT2: [2, 45, 60]; exp_num(2), is_atom(FALSE)3 #HAGR2: [2, 45, 60]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID)4 #SLCT2: [1, 4543, 60]; EMP.SALARY < var15 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
SQL> explain select dept_id,count(*) cnt from emp group by dept_id having count(*) >2;1 #NSET2: [2, 5, 30]2 #PRJT2: [2, 5, 30]; exp_num(2), is_atom(FALSE)3 #SLCT2: [2, 5, 30]; exp_sfun9 > var14 #HAGR2: [2, 100, 30]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID)5 #CSCN2: [1, 10000, 30]; INDEX33555484(EMP); btr_scan(1)KingBase
-- 聚合：一般聚合TEST=# explain select sum(salary) from emp; QUERY PLAN--------------------------------------------------------------------------------------- Finalize Aggregate (cost=16384.55..16384.56 rows=1 width=8) -> Gather (cost=16384.33..16384.54 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=15384.33..15384.34 rows=1 width=8) -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=8)
-- 聚合：極值TEST=# explain select max(salary),min(salary) from emp; QUERY PLAN--------------------------------------------------------------------------------------------------------------- Result (cost=1.01..1.02 rows=1 width=16) InitPlan 1 (returns $0) -> Limit (cost=0.42..0.50 rows=1 width=8) -> Index Only Scan Backward using idx_emp_salary on emp (cost=0.42..71128.49 rows=904233 width=8) Index Cond: (salary IS NOT NULL) InitPlan 2 (returns $1) -> Limit (cost=0.42..0.50 rows=1 width=8) -> Index Only Scan using idx_emp_salary on emp emp_1 (cost=0.42..71128.49 rows=904233 width=8) Index Cond: (salary IS NOT NULL)
-- 聚合：計(jì)數(shù)TEST=# explain select count(*) from emp; QUERY PLAN--------------------------------------------------------------------------------------- Finalize Aggregate (cost=16384.55..16384.56 rows=1 width=8) -> Gather (cost=16384.33..16384.54 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=15384.33..15384.34 rows=1 width=8) -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=0)
-- 分組：一般分組TEST=# explain select dept_id,avg(salary) from emp group by dept_id; QUERY PLAN---------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=17430.40..17456.24 rows=101 width=13) Group Key: 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: dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=37) Group Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=13)
-- 分組：分組+排序TEST=# explain select dept_id,avg(salary) from emp group by dept_id order by dept_id; QUERY PLAN---------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=17430.40..17456.24 rows=101 width=13) Group Key: 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: dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=37) Group Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=13)
-- 分組：分組過濾（WHERE/HAVING）TEST=# explain select dept_id,count(*) from emp where salary<1500 group by dept_id; QUERY PLAN--------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=17332.58..17358.17 rows=101 width=13) Group Key: dept_id -> Gather Merge (cost=17332.58..17356.15 rows=202 width=13) Workers Planned: 2 -> Sort (cost=16332.56..16332.81 rows=101 width=13) Sort Key: dept_id -> Partial HashAggregate (cost=16328.18..16329.19 rows=101 width=13) Group Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..15384.33 rows=188770 width=5) Filter: (salary < '1500'::double precision)
TEST=# explain select dept_id,count(*) cnt from emp group by dept_id having count(*) >2; QUERY PLAN--------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=17430.40..17456.74 rows=34 width=13) Group Key: dept_id Filter: (count(*) > 2) -> Gather Merge (cost=17430.40..17453.97 rows=202 width=13) Workers Planned: 2 -> Sort (cost=16430.37..16430.63 rows=101 width=13) Sort Key: dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=13) Group Key: dept_id -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=5)YashanDB
-- 聚合：一般聚合SQL> explain select sum(salary) from emp;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | AGGREGATE | | | 1| 27( 0)|| 2 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- 聚合：極值SQL> explain select max(salary),min(salary) from emp;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | AGGREGATE | | | 1| 27( 0)|| 2 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- 聚合：計(jì)數(shù)SQL> explain select count(*) from emp;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | AGGREGATE | | | 1| 14( 0)|| 2 | INDEX FAST FULL SCAN | EMP_PK | TESTUSER | 10000| 14( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- 分組：一般分組SQL> explain select dept_id,avg(salary) from emp group by dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | HASH GROUP | | | 100| 48( 0)|| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- 分組：分組+排序SQL> explain select dept_id,avg(salary) from emp group by dept_id order by dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | SORT | | | 100| 49( 0)|| 2 | HASH GROUP | | | 100| 48( 0)|| 3 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
-- 分組：分組過濾（WHERE/HAVING）SQL> explain select dept_id,count(*) from emp where salary<1500 group by dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | || 1 | HASH GROUP | | | 50| 37( 0)|| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4509| 36( 0)||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4509| 12( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+
SQL> explain select dept_id,count(*) cnt from emp group by dept_id having count(*) >2;+----+--------------------------------+----------------------+------------+----------+-------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) |+----+--------------------------------+----------------------+------------+----------+-------------+| 0 | SELECT STATEMENT | | | | ||* 1 | HASH GROUP | | | 33| 48( 0)|| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)|+----+--------------------------------+----------------------+------------+----------+-------------+Vastbase
-- 聚合：一般聚合vastbase=> explain select sum(salary) from emp; QUERY PLAN--------------------------------------------------------------- Aggregate (cost=243.00..243.01 rows=1 width=16) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)
-- 聚合：極值vastbase=> explain select max(salary),min(salary) from emp; QUERY PLAN----------------------------------------------------------------------------------------------------------- Result (cost=0.17..0.18 rows=1 width=0) InitPlan 1 (returns $0) -> Limit (cost=0.00..0.09 rows=1 width=8) -> Index Only Scan Backward using idx_emp_salary on emp (cost=0.00..785.75 rows=9000 width=8) Index Cond: (salary IS NOT NULL) InitPlan 2 (returns $1) -> Limit (cost=0.00..0.09 rows=1 width=8) -> Index Only Scan using idx_emp_salary on emp (cost=0.00..785.75 rows=9000 width=8) Index Cond: (salary IS NOT NULL)
-- 聚合：計(jì)數(shù)vastbase=> explain select count(*) from emp; QUERY PLAN--------------------------------------------------------------- Aggregate (cost=243.00..243.01 rows=1 width=8) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=0)
-- 分組：一般分組vastbase=> explain select dept_id,avg(salary) from emp group by dept_id; QUERY PLAN---------------------------------------------------------------- HashAggregate (cost=268.00..269.26 rows=101 width=48) Group By Key: dept_id -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=16)
-- 分組：分組+排序vastbase=> explain select dept_id,avg(salary) from emp group by dept_id order by dept_id; QUERY PLAN---------------------------------------------------------------------- Sort (cost=272.62..272.88 rows=101 width=48) Sort Key: dept_id -> HashAggregate (cost=268.00..269.26 rows=101 width=48) Group By Key: dept_id -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=16)
-- 分組：分組過濾（WHERE/HAVING）vastbase=> explain select dept_id,count(*) from emp where salary<1500 group by dept_id; QUERY PLAN-------------------------------------------------------------- HashAggregate (cost=265.67..266.12 rows=46 width=16) Group By Key: dept_id -> Seq Scan on emp (cost=0.00..243.00 rows=4533 width=8) Filter: (salary < 1500::double precision)
vastbase=> explain select dept_id,count(*) cnt from emp group by dept_id having count(*) >2; QUERY PLAN--------------------------------------------------------------- HashAggregate (cost=293.00..294.26 rows=101 width=24) Group By Key: dept_id Filter: (count(*) > 2) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)責(zé)任編輯：姜華
來源：
韓鋒頻道
