國(guó)產(chǎn)集中庫SQL能力評(píng)測(cè) - 視圖
原創(chuàng)
作者：
韓鋒
對(duì)于存在復(fù)雜視圖的查詢，優(yōu)化器可以有兩種方式來優(yōu)化查詢。一是創(chuàng)建一個(gè)用于聚集視圖合并結(jié)果集，并把這個(gè)結(jié)果集連接到基表中；另一個(gè)是展開視圖連接兩個(gè)基表并聚集這些連接。
這里談?wù)勔晥D。視圖其實(shí)就是一條查詢 SQL 語句，用于顯示一個(gè)或多個(gè)表或其他視圖中的相關(guān)數(shù)據(jù)。視圖將一個(gè)查詢的結(jié)果作為一個(gè)表來使用，因此視圖可以被看作是存儲(chǔ)的查詢或一個(gè)虛擬表。視圖來源于表，所有對(duì)視圖數(shù)據(jù)的修改最終都會(huì)被反映到視圖的基表中，這些修改必須服從基表的完整性約束，并同樣會(huì)觸發(fā)定義在基表上的觸發(fā)器。與表不同，視圖不會(huì)要求分配存儲(chǔ)空間，視圖中也不會(huì)包含實(shí)際的數(shù)據(jù)。視圖只是定義了一個(gè)查詢，視圖中的數(shù)據(jù)是從基表中獲取，這些數(shù)據(jù)在視圖被引用時(shí)動(dòng)態(tài)的生成。由于視圖基于數(shù)據(jù)庫中的其他對(duì)象，因此一個(gè)視圖只需要占用數(shù)據(jù)字典中保存其定義的空間，而無需額外的存儲(chǔ)空間。
1. 視圖概述
1).視圖作用
用戶可以通過視圖以不同形式來顯示基表中的數(shù)據(jù)，視圖的強(qiáng)大之處在于它能夠根據(jù)不同用戶的需要來對(duì)基表中的數(shù)據(jù)進(jìn)行整理。視圖常見的用途如下：
- 提供額外數(shù)據(jù)訪問安全，通過對(duì)用戶設(shè)置視圖訪問權(quán)限，約束用戶訪問指定的行或列。
- 隱藏?cái)?shù)據(jù)復(fù)雜性，例如視圖可將由多個(gè)表組成的查詢封裝為新的數(shù)據(jù)集，屏蔽其內(nèi)部復(fù)雜性。
- 簡(jiǎn)化用戶的SQL語句，復(fù)雜內(nèi)部邏輯通過視圖來封裝。
- 隔離應(yīng)用與數(shù)據(jù)表，可讓應(yīng)用不受表定義變更的影響進(jìn)而可封裝基表數(shù)據(jù)展現(xiàn)，使得應(yīng)用程序與數(shù)據(jù)庫表在一定程度上獨(dú)立。
2).視圖分類
簡(jiǎn)單視圖能夠被查詢優(yōu)化器較好地處理，但是復(fù)雜視圖不能被查詢優(yōu)化器很好地處理。一些商業(yè)數(shù)據(jù)庫，如Oracle，提供了一些視圖的優(yōu)化技術(shù)，如"復(fù)雜視圖合并"、"物化視圖查詢重寫"等。但從整體上看，復(fù)雜視圖優(yōu)化技術(shù)還有待繼續(xù)提高。
圖片
2. 視圖優(yōu)化
1).優(yōu)化手段
視圖合并
對(duì)于存在復(fù)雜視圖的查詢，優(yōu)化器可以有兩種方式來優(yōu)化查詢。一是創(chuàng)建一個(gè)用于聚集視圖合并結(jié)果集，并把這個(gè)結(jié)果集連接到基表中；另一個(gè)是展開視圖連接兩個(gè)基表并聚集這些連接。為了把視圖查詢合并到讀取查詢中，優(yōu)化器將讀取查詢中所使用視圖的名字替換為原始表名字，并把視圖查詢WHERE條件中的查詢條件添加到讀取查詢的WHERE條件中去。需要強(qiáng)調(diào)的是該合并中是以讀取查詢?yōu)榛鶞?zhǔn)，即把視圖查詢中的對(duì)應(yīng)查詢條件合并到讀取查詢中去。如果在讀取查詢語句中存在大量可以縮減查詢范圍的查詢條件，且將這些查詢條件添加到視圖中可以縮減整體的數(shù)據(jù)處理量。
條件推入
在無法執(zhí)行視圖合并的情況下，將讀取查詢中的查詢條件推入到視圖查詢中去。
視圖重寫
視圖重寫就是將對(duì)視圖的引用重寫為對(duì)基本表的引用。視圖重寫后的SQL多被子查詢進(jìn)行進(jìn)一步優(yōu)化。
2).Oracle 視圖優(yōu)化
-- 視圖合并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;通過隱含參數(shù)，禁止復(fù)雜視圖合并功能。
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)提前在表掃描中進(jìn)行了過濾
--視圖重寫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. 國(guó)產(chǎn)庫視圖優(yōu)化評(píng)測(cè)
1)整體情況
下面是針對(duì)上述測(cè)例，國(guó)產(chǎn)庫的行為如何？先來看看整體結(jié)果
圖片
2)國(guó)產(chǎn)庫評(píng)測(cè)
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責(zé)任編輯：武曉燕
來源：
韓鋒頻道
