这里谈谈视图。视图其实就是一条查询 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
