國產集中庫SQL能力評測 - 視圖
原創
作者：
韓鋒
對于存在復雜視圖的查詢，優化器可以有兩種方式來優化查詢。一是創建一個用于聚集視圖合并結果集，并把這個結果集連接到基表中；另一個是展開視圖連接兩個基表并聚集這些連接。
這里談談視圖。視圖其實就是一條查詢 SQL 語句，用于顯示一個或多個表或其他視圖中的相關數據。視圖將一個查詢的結果作為一個表來使用，因此視圖可以被看作是存儲的查詢或一個虛擬表。視圖來源于表，所有對視圖數據的修改最終都會被反映到視圖的基表中，這些修改必須服從基表的完整性約束，并同樣會觸發定義在基表上的觸發器。與表不同，視圖不會要求分配存儲空間，視圖中也不會包含實際的數據。視圖只是定義了一個查詢，視圖中的數據是從基表中獲取，這些數據在視圖被引用時動態的生成。由于視圖基于數據庫中的其他對象，因此一個視圖只需要占用數據字典中保存其定義的空間，而無需額外的存儲空間。
1. 視圖概述
1).視圖作用
用戶可以通過視圖以不同形式來顯示基表中的數據，視圖的強大之處在于它能夠根據不同用戶的需要來對基表中的數據進行整理。視圖常見的用途如下：
- 提供額外數據訪問安全，通過對用戶設置視圖訪問權限，約束用戶訪問指定的行或列。
- 隱藏數據復雜性，例如視圖可將由多個表組成的查詢封裝為新的數據集，屏蔽其內部復雜性。
- 簡化用戶的SQL語句，復雜內部邏輯通過視圖來封裝。
- 隔離應用與數據表，可讓應用不受表定義變更的影響進而可封裝基表數據展現，使得應用程序與數據庫表在一定程度上獨立。
2).視圖分類
簡單視圖能夠被查詢優化器較好地處理，但是復雜視圖不能被查詢優化器很好地處理。一些商業數據庫，如Oracle，提供了一些視圖的優化技術，如"復雜視圖合并"、"物化視圖查詢重寫"等。但從整體上看，復雜視圖優化技術還有待繼續提高。
圖片
2. 視圖優化
1).優化手段
視圖合并
對于存在復雜視圖的查詢，優化器可以有兩種方式來優化查詢。一是創建一個用于聚集視圖合并結果集，并把這個結果集連接到基表中；另一個是展開視圖連接兩個基表并聚集這些連接。為了把視圖查詢合并到讀取查詢中，優化器將讀取查詢中所使用視圖的名字替換為原始表名字，并把視圖查詢WHERE條件中的查詢條件添加到讀取查詢的WHERE條件中去。需要強調的是該合并中是以讀取查詢為基準，即把視圖查詢中的對應查詢條件合并到讀取查詢中去。如果在讀取查詢語句中存在大量可以縮減查詢范圍的查詢條件，且將這些查詢條件添加到視圖中可以縮減整體的數據處理量。
條件推入
在無法執行視圖合并的情況下，將讀取查詢中的查詢條件推入到視圖查詢中去。
視圖重寫
視圖重寫就是將對視圖的引用重寫為對基本表的引用。視圖重寫后的SQL多被子查詢進行進一步優化。
2).Oracle 視圖優化
-- 視圖合并SQL> create view v_emp_group_by_deptas select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_salfrom empgroup by dept_id;
SQL> select d.dept_name,v.avg_salfrom dept d,v_emp_group_by_dept vwhere d.dept_id=v.dept_id and v.dept_id<20;-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 242 | 5808 | 19 (11)| 00:00:01 || 1 | HASH GROUP BY | | 242 | 5808 | 19 (11)| 00:00:01 ||* 2 | HASH JOIN | | 351 | 8424 | 18 (6)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 19 | 285 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | DEPT_PK | 19 | | 1 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMP | 1827 | 16443 | 15 (0)| 00:00:01 |-----------------------------------------------------------------------------------------
SQL> alter session set "_complex_view_merging"=false;通過隱含參數，禁止復雜視圖合并功能。
SQL> select d.dept_name,v.avg_salfrom dept d,v_emp_g 2 roup_by_dept vwhere d.dept_id=v.dept_id and v.d 3 ept_id<20;----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 17 | 612 | 19 (11)| 00:00:01 ||* 1 | HASH JOIN | | 17 | 612 | 19 (11)| 00:00:01 || 2 | VIEW | V_EMP_GROUP_BY_DEPT | 18 | 468 | 16 (7)| 00:00:01 || 3 | HASH GROUP BY | | 18 | 162 | 16 (7)| 00:00:01 ||* 4 | TABLE ACCESS FULL | EMP | 1827 | 16443 | 15 (0)| 00:00:01 || 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 19 | 190 | 2 (0)| 00:00:01 ||* 6 | INDEX RANGE SCAN | DEPT_PK | 19 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------
--條件推入SQL> select * from V_EMP_GROUP_BY_DEPT where dept_id<5;-------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 156 | 16 (7)| 00:00:01 || 1 | VIEW | V_EMP_GROUP_BY_DEPT | 3 | 156 | 16 (7)| 00:00:01 || 2 | HASH GROUP BY | | 3 | 27 | 16 (7)| 00:00:01 ||* 3 | TABLE ACCESS FULL| EMP | 317 | 2853 | 15 (0)| 00:00:01 |-------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("DEPT_ID"<5)提前在表掃描中進行了過濾
--視圖重寫SQL> create view v_emp_dept10 as select * from emp where dept_id=10;SQL> select * from v_emp_dept10 where salary<1200;--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 18 | 504 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 18 | 504 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("DEPT_ID"=10 AND "SALARY"<1200)3. 國產庫視圖優化評測
1)整體情況
下面是針對上述測例，國產庫的行為如何？先來看看整體結果
圖片
2)國產庫評測
MySQL
--視圖合并mysql> create view v_emp_group_by_dept -> as select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_sal -> from emp -> group by dept_id;
mysql> explain select d.dept_name,v.avg_sal -> from dept d,v_emp_group_by_dept v -> where d.dept_id=v.dept_id and v.dept_id<20;+----+-------------+------------+------------+-------+---------------+---------------+---------+------------------+-------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+-------+---------------+---------------+---------+------------------+-------+----------+------------------------------+| 1 | PRIMARY | d | NULL | index | PRIMARY | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index || 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | testdb.d.dept_id | 33 | 100.00 | NULL || 2 | DERIVED | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 33.33 | Using where; Using temporary |+----+-------------+------------+------------+-------+---------------+---------------+---------+------------------+-------+----------+------------------------------+
--條件推入mysql> explain select * from v_emp_group_by_dept where dept_id<5;+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3371 | 100.00 | NULL || 2 | DERIVED | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 33.33 | Using where; Using temporary |+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `testdb`.`v_emp_group_by_dept`.`dept_id` AS `dept_id`,`testdb`.`v_emp_group_by_dept`.`avg_sal` AS `avg_sal`,`testdb`.`v_emp_group_by_dept`.`min_sal` AS `min_sal`,`testdb`.`v_emp_group_by_dept`.`max_sal` AS `max_sal` from `testdb`.`v_emp_group_by_dept` |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--視圖重寫mysql> create view v_emp_dept10 as select * from emp where dept_id=10;
mysql> explain select * from v_emp_dept10 where salary<1200;+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+| 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 | 1.31 | Using where |+----+-------------+-------+------------+------+----------------+------+---------+------+-------+----------+-------------+1 row in set, 1 warning (0.01 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` < 1200)) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+DM
--視圖合并SQL> create view v_emp_group_by_deptas select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_salfrom empgroup by dept_id;
SQL> explain select d.dept_name,v.avg_salfrom dept d,v_emp_group_by_dept vwhere d.dept_id=v.dept_id and v.dept_id<20;1 #NSET2: [3, 94, 150]2 #PRJT2: [3, 94, 150]; exp_num(2), is_atom(FALSE)3 #HAGR2: [3, 94, 150]; grp_num(3), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(EMP.DEPT_ID, D.DEPT_NAME, D.ROWID) ; real_keys(1)4 #HASH2 INNER JOIN: [2, 495, 150]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)5 #BLKUP2: [1, 19, 90]; INDEX33555481(D)6 #SSEK2: [1, 19, 90]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range(null2,exp_cast(20)), is_global(0)7 #SLCT2: [1, 500, 60]; EMP.DEPT_ID < var18 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
--條件推入SQL> explain select * from v_emp_group_by_dept where dept_id<5;1 #NSET2: [2, 5, 60]2 #PRJT2: [2, 5, 60]; exp_num(4), is_atom(FALSE)3 #PRJT2: [2, 5, 60]; exp_num(4), is_atom(FALSE)4 #HAGR2: [2, 5, 60]; grp_num(1), sfun_num(3), distinct_flag[0,0,0]; slave_empty(0) keys(EMP.DEPT_ID)5 #SLCT2: [1, 500, 60]; EMP.DEPT_ID < var16 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)
--視圖重寫SQL> create view v_emp_dept10 as select * from emp where dept_id=10;
SQL> explain select * from v_emp_dept10 where salary<1200;1 #NSET2: [1, 44, 163]2 #PRJT2: [1, 44, 163]; exp_num(6), is_atom(FALSE)3 #SLCT2: [1, 44, 163]; (EMP.DEPT_ID = var1 AND EMP.SALARY < var2) SLCT_PUSHDOWN(TRUE)4 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP) NEED_SLCT(TRUE); btr_scan(1)KingBase
--視圖合并TEST=# create view v_emp_group_by_deptTEST-# as select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_salTEST-# from empTEST-# group by dept_id;CREATE VIEW
TEST=# explain select d.dept_name,v.avg_salTEST-# from dept d,v_emp_group_by_dept vTEST-# where d.dept_id=v.dept_id and v.dept_id<20; QUERY PLAN--------------------------------------------------------------------------------------------------- Hash Join (cost=16769.67..16796.80 rows=100 width=21) Hash Cond: (emp.dept_id = d.dept_id) -> Finalize GroupAggregate (cost=16766.42..16792.26 rows=101 width=29) Group Key: emp.dept_id -> Gather Merge (cost=16766.42..16789.99 rows=202 width=37) Workers Planned: 2 -> Sort (cost=15766.40..15766.65 rows=101 width=37) Sort Key: emp.dept_id -> Partial HashAggregate (cost=15762.02..15763.03 rows=101 width=37) Group Key: emp.dept_id -> Parallel Seq Scan on emp (cost=0.00..15384.33 rows=75538 width=13) Filter: (dept_id < '20'::numeric) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
--條件推入TEST=# explain select * from v_emp_group_by_dept where dept_id<5; QUERY PLAN--------------------------------------------------------------------------------------------- Finalize GroupAggregate (cost=16516.15..16543.00 rows=101 width=29) Group Key: emp.dept_id -> Gather Merge (cost=16516.15..16539.72 rows=202 width=53) Workers Planned: 2 -> Sort (cost=15516.13..15516.38 rows=101 width=53) Sort Key: emp.dept_id -> Partial HashAggregate (cost=15511.75..15512.76 rows=101 width=53) Group Key: emp.dept_id -> Parallel Seq Scan on emp (cost=0.00..15384.33 rows=12742 width=13) Filter: (dept_id < '5'::numeric)--視圖重寫TEST=# create view v_emp_dept10 as select * from emp where dept_id=10;CREATE VIEWTEST=# explain select * from v_emp_dept10 where salary<1200; QUERY PLAN------------------------------------------------------------------------------------------- Gather (cost=5351.92..16859.72 rows=1826 width=42) Workers Planned: 2 -> Parallel Bitmap Heap Scan on emp (cost=4351.92..15677.12 rows=761 width=42) Recheck Cond: (salary < '1200'::double precision) Filter: (dept_id = '10'::numeric) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..4351.47 rows=183872 width=0) Index Cond: (salary < '1200'::double precision)YashanDB
--視圖合并SQL> create view v_emp_group_by_deptas select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_salfrom empgroup by dept_id;
SQL> explain select d.dept_name,v.avg_salfrom dept d,v_emp_group_by_dept vwhere d.dept_id=v.dept_id and v.dept_id<20;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 19| 48( 0)| || 2 | VIEW | | | 19| 48( 0)| || 3 | HASH GROUP | | | 19| 48( 0)| ||* 4 | TABLE ACCESS FULL | EMP | TESTUSER | 1917| 46( 0)| ||* 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):--------------------------------------------------- 3 - Group Expression: ("EMP"."DEPT_ID") 4 - Predicate : filter("EMP"."DEPT_ID" < 20) 5 - Predicate : filter("D"."DEPT_ID" < 20) 6 - Predicate : access("D"."DEPT_ID" = "V"."DEPT_ID") --條件推入SQL> explain select * from v_emp_group_by_dept where dept_id<5;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | SORT GROUP | | | 4| 47( 0)| ||* 2 | TABLE ACCESS FULL | EMP | TESTUSER | 425| 46( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):--------------------------------------------------- 1 - Group Expression: ("EMP"."DEPT_ID") 2 - Predicate : filter("EMP"."DEPT_ID" < 5)
--視圖重寫SQL> explain select * from v_emp_dept10 where salary<1200;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 43| 15( 0)| ||* 2 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 1880| 5( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):--------------------------------------------------- 1 - Predicate : filter("EMP"."DEPT_ID" = 10) 2 - Predicate : access("EMP"."SALARY" < 1200)Vastbase
--視圖合并vastbase=> create view v_emp_group_by_deptas select dept_id,avg(salary) avg_sal,min(salary) min_sal,max(salary) max_salfrom empgroup by dept_id;vastbase-> vastbase-> vastbase->CREATE VIEWvastbase=> explain select d.dept_name,v.avg_salfrovastbase-> m dept d,v_emp_group_by_dept vwvastbase-> here d.dept_id=v.dept_id and v.dept_id<20; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=261.83..264.32 rows=19 width=20) Hash Cond: (d.dept_id = v.dept_id) -> Seq Scan on dept d (cost=0.00..2.25 rows=19 width=20) Filter: (dept_id < 20::number) -> Hash (cost=261.59..261.59 rows=19 width=16) -> Subquery Scan on v (cost=261.16..261.59 rows=19 width=16) -> HashAggregate (cost=261.16..261.40 rows=19 width=64) Group By Key: emp.dept_id -> Seq Scan on emp (cost=0.00..243.00 rows=1816 width=16) Filter: (dept_id < 20::number) --條件推入vastbase=> explain select * from v_emp_group_by_dept where dept_id<5; QUERY PLAN責任編輯：武曉燕
來源：
韓鋒頻道
