Greenplum+Hadoop学习笔记-14-定义数据库对象之创建与管理序列、索引以及视图
6.5.创建与管理序列
序列常用于在新增记录时自动生成唯一标识符，序列的管理包括创建序列、使用序列、修改序列以及删除序列。
6.5.1.创建序列
使用CREATESEQUENCE命令来创建并初始化一个给定名称的单列序列表；
|
devdw=# \h CREATE SEQUENCE 查看创建序列的帮助 Command: CREATE SEQUENCE Description: define a new sequence generator Syntax: CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table.column | NONE } ]
devdw=# \ds 通过\ds命令查看当前数据库中存在的序列 No relations found. devdw=# create sequence myseq start with 100; 创建名为myseq的序列 CREATE SEQUENCE devdw=# \ds 通过\ds命令查看当前数据库中存在的序列 List of relations Schema | Name | Type | Owner | Storage -------+-------+----------+---------+--------- public | myseq | sequence | gpadmin | heap (1 row) |
6.5.2.使用序列
1) 可以使用nextval函数对序列进行操作。例如：获取序列的下一个值并插入表中：
2) 重置一个序列计数器的值，函数nextval是不回滚的，一旦使用nextval中生成这个序列时下次使用时不能使用这个值，不管该序列创建是否成功还是失败；
注意：如果启用了镜像功能，nextval不允许在UPDATE和DELETE语句中被使用。
3) 检查序列当前的计数设置：SELECT * FROM SEQ_NAME;
|
devdw=# select setval(‘myseq‘,200); 通过设置myseq的序列值为20 setval ------------- 200 (1 row) devdw=# select nextval(‘myseq‘); 查看当前序列的下一个值 nextval --------- 201 (1 row) devdw=# select * from myseq; 查看当前序列的信息 sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- myseq | 200 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t |
6.5.3.修改序列
使用ALTERSEQUENCE命令修改已有的序列表。
|
devdw=# \h alter sequence 查看修改序列的帮助 Command: ALTER SEQUENCE Description: change the definition of a sequence generator Syntax: ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table.column | NONE } ] ALTER SEQUENCE name SET SCHEMA new_schema
devdw=# alter sequence myseq start with 205; 修改当前序列的开始值为205 ALTER SEQUENCE |
6.5.4.删除序列
使用DROPSEQUENCE命令删除已有的序列表。
|
devdw=# \h drop sequence 查看修改序列的帮助 Command: DROP SEQUENCE Description: remove a sequence Syntax: DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
devdw=# drop sequence myseq; 删除当前序列 DROP SEQUENCE devdw=# \ds 再次查看当前序列时提示无序列 No relations found. |
6.6.创建与管理索引
6.6.1.关于索引的使用说明
1) 在分布式数据库如GP中，应保守使用索引，原因在于GP属于分布式数据库，不等同于传统数据库
2) 在返回一定量结果的情况下，索引同样可以有效改善压缩AO（Append-Only）表上的查询性能，索引本质上是随机的寻址操作，而数据仓库返回的数据量是海量级别的；
3) GP会自动为主键建立主键索引，如果在父表中创建好索引，则GP默认会为分区表创建好索引；但在修改父表索引时不能默认修改分区表的索引；
4) 需确保索引的创建在查询工作负载中真正被使用到，需要在实际环境中验证；
5) 创建索引时需综合考虑的问题：
a) 查询工作负载，同第4)点中描述一样；
b) 压缩表：当数据量达到一定量级别时会影响效率的查询；
c) 避免在频繁更新的列上使用索引；
d) 创建选择性B-tree索引，是指对某一列的总行数/该表的总行数，当不等以1的时候创建选择性B-tree索引；
e) 低选择性列上使用位图索引；
f) 索引列用于关联；
g) 索引列经常用在查询条件中；
6) 索引类型：GP中常用的两种：B-tree和Bitmap索引，GP中使用唯一索引时必须包含DK，唯一索引不支持AO表，创建 AO表时一般是创建分区表使用，唯一索引在每个分区中是唯一存在的；
7) 关于位图索引：适合于数据仓库应用；每个位图对应一组数据表中相同值行的ID记录；Bitmap索引空间占用小；位图的每一位对应源数据的标识符，
被设置的位对应的记录包含该位图相同的值；创建速度快；允许键值为空；表记录的高效访问；
a) 何时使用位图索引
Bitmap索引在DISTINCT值数量在100和100000之间时可以有较好的表现；
在特征合适的列上使用Bitmap索引好于B-tree索引；
位图索引在分析查询方面性能较好；
b) 何时不宜使用位图索引
位图索引不适合用于唯一性列和DISTINCT值很高的列；
位图索引不适合大量并发事务更新数据的OLTP类型应用；
6.6.2.创建索引
1) 使用CREATE INDEX在表中定义索引，缺省创建B-tree索引。
2) 通过EXPLAIN命令来检查查询是否使用了索引，在输出结果中查找下面的查询节点以确认索引的使用
a) Index Scan – 扫描索引
b) Bitmap Heap Scan – 从BitmapAnd,BitmapOr或BitmapIndexScan和数据文件生成的记录所产生的Bitmap中检索数据
c) Bitmap Index Scan – 从索引底层扫描那些与查询相匹配的位图索引
d) BitmapAnd or BitmapOr – 将来自多个位图索引扫描的节点进行And或Or连接，生成一个新的位图作为输出。
3) 很难通过一个通用的程序来决定哪些场景要使用索引，大量的测试是必要的：
a) 在创建和更新索引后运行ANALYZE
b) 使用真实数据测试
c) 使用很小的数据量来测试是致命的错误
d) 当索引没有被使用，必要情况下可以强制使用
|
devdw=# \h create index 查看创建索引的帮助 Command: CREATE INDEX Description: define a new index Syntax: CREATE [UNIQUE] INDEX name ON table [USING btree|bitmap|gist] ( {column | (expression)} [opclass] [, ...] ) [ WITH ( FILLFACTOR = value ) ] [TABLESPACE tablespace] [WHERE predicate]
devdw=# \d tb_cp_02 查看tb_cp_02表信息 Table "public.tb_cp_02" Column | Type | Modifiers --------+--------------+----------- id | integer | rank | integer | year | integer | gender | character(1) | count | integer | Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id)
devdw=# create index idx_01 on tb_cp_02(id); 为tb_cp_02表中(id)字段创建索引 NOTICE: building index for child partition "tb_cp_02_1_prt_extra" NOTICE: building index for child partition "tb_cp_02_1_prt_2" NOTICE: building index for child partition "tb_cp_02_1_prt_3" NOTICE: building index for child partition "tb_cp_02_1_prt_4" NOTICE: building index for child partition "tb_cp_02_1_prt_5" CREATE INDEX devdw=# \d tb_cp_02 查看tb_cp_02表信息 Table "public.tb_cp_02" Column | Type | Modifiers --------+--------------+----------- id | integer | rank | integer | year | integer | gender | character(1) | count | integer | Indexes: "idx_01" btree (id) 默认是btree索引 Number of child tables: 5 (Use \d+ to list them.) Distributed by: (id)
devdw=# \d+ tb_cp_02; 查看tb_cp_02表以及其子表信息 Table "public.tb_cp_02" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Indexes: "idx_01" btree (id) Child tables: tb_cp_02_1_prt_2, tb_cp_02_1_prt_3, tb_cp_02_1_prt_4, tb_cp_02_1_prt_5, tb_cp_02_1_prt_extra Has OIDs: no Distributed by: (id)
devdw=# create index bmidx_01 on tb_cp_02 using bitmap(count); 创建位图索引 NOTICE: building index for child partition "tb_cp_02_1_prt_extra" NOTICE: building index for child partition "tb_cp_02_1_prt_2" NOTICE: building index for child partition "tb_cp_02_1_prt_3" NOTICE: building index for child partition "tb_cp_02_1_prt_4" NOTICE: building index for child partition "tb_cp_02_1_prt_5" CREATE INDEX devdw=# \d+ tb_cp_02; 查看tb_cp_02表以及其子表信息 Table "public.tb_cp_02" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Indexes: "bmidx_01" bitmap (count) "idx_01" btree (id) Child tables: tb_cp_02_1_prt_2, tb_cp_02_1_prt_3, tb_cp_02_1_prt_4, tb_cp_02_1_prt_5, tb_cp_02_1_prt_extra Has OIDs: no Distributed by: (id)
devdw=# explain select * from tb_cp_02 where count = 0; 查看tb_cp_02表的执行计划 QUERY PLAN ------------------------------------------------------------------------------------------ Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..0.00 rows=3 width=24) -> Append (cost=0.00..0.00 rows=3 width=24) -> Seq Scan on tb_cp_02_1_prt_extra tb_cp_02 (cost=0.00..0.00 rows=1 width=24) Filter: count = 0 -> Seq Scan on tb_cp_02_1_prt_2 tb_cp_02 (cost=0.00..0.00 rows=1 width=24) Filter: count = 0 -> Seq Scan on tb_cp_02_1_prt_3 tb_cp_02 (cost=0.00..0.00 rows=1 width=24) Filter: count = 0 -> Seq Scan on tb_cp_02_1_prt_4 tb_cp_02 (cost=0.00..0.00 rows=1 width=24) Filter: count = 0 -> Seq Scan on tb_cp_02_1_prt_5 tb_cp_02 (cost=0.00..0.00 rows=1 width=24) Filter: count = 0 (12 rows)
devdw=# show enable_seqscan; GP中默认进行序列扫描 enable_seqscan ---------------- on (1 row)
devdw=# set enable_seqscan=off; 将序列扫描的方式关闭 SET devdw=# explain select * from tb_cp_02 where count = 0; 再次执行tb_cp_02的查询计划 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------- -- Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..1001.35 rows=3 width=24) -> Append (cost=0.00..1001.35 rows=3 width=24) -> Index Scan using bmidx_01_1_prt_extra on tb_cp_02_1_prt_extra tb_cp_02 (cost=0.00..200.27 rows=1 width=24 ) Index Cond: count = 0 -> Index Scan using bmidx_01_1_prt_2 on tb_cp_02_1_prt_2 tb_cp_02 (cost=0.00..200.27 rows=1 width=24) Index Cond: count = 0 -> Index Scan using bmidx_01_1_prt_3 on tb_cp_02_1_prt_3 tb_cp_02 (cost=0.00..200.27 rows=1 width=24) Index Cond: count = 0 -> Index Scan using bmidx_01_1_prt_4 on tb_cp_02_1_prt_4 tb_cp_02 (cost=0.00..200.27 rows=1 width=24) Index Cond: count = 0 -> Index Scan using bmidx_01_1_prt_5 on tb_cp_02_1_prt_5 tb_cp_02 (cost=0.00..200.27 rows=1 width=24) Index Cond: count = 0 Settings: enable_seqscan=off (13 rows) |
6.6.3.重建索引
1) 某些情况下，性能变差可以通过REINDEX来重建索引；重建索引将使用存储在索引表中的数据建立新的索引取代旧的索引；
更新和删除操作不更新位图索引；
|
devdw=# \h reindex 查看reindex的帮助 Command: REINDEX Description: rebuild indexes Syntax: REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ] devdw=# reindex table tb_cp_02; REINDEX devdw=# reindex index bmidx_01; REINDEX |
6.6.4.删除索引
在装载数据时，通常先删除索引，再装载数据，再重建索引，使用DROP INDEX命令删除特定索引，
|
devdw=# \h drop index 查看drop index的帮助 Command: DROP INDEX Description: remove an index Syntax: DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] devdw=# drop index idx_01; 删除索引idx_01 WARNING: Only dropped the index "idx_01" HINT: To drop other indexes on child partitions, drop each one explicitly. DROP INDEX devdw=# \d+ tb_cp_02; 查看tb_cp_02表的索引，此时已经没有idx_01索引 Table "public.tb_cp_02" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Indexes: "bmidx_01" bitmap (count) Child tables: tb_cp_02_1_prt_2, tb_cp_02_1_prt_3, tb_cp_02_1_prt_4, tb_cp_02_1_prt_5, tb_cp_02_1_prt_extra Has OIDs: no Distributed by: (id) |
6.7.创建与管理视图
6.7.1.关于视图的使用说明
1) 对于那些使用频繁或比较复杂的查询，通过创建视图(VIEW)可以把其当作访问表一样使用SELECT语句来访问；
2) 视图不能存在于物理介质上；
6.7.2.创建视图
视图会忽略ORDER BY或者排序操作；使用CREATEVIEW命令将查询语句定义为一个视图。
|
devdw=# \h create view 查看创建视图的帮助 Command: CREATE VIEW Description: define a new view Syntax: CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query devdw=# \d tb_cp_04 查看tb_cp_04表结构信息 Table "public.tb_cp_04" Column | Type | Modifiers --------+--------------+----------- id | integer | rank | integer | year | integer | gender | character(1) | count | integer | Number of child tables: 3 (Use \d+ to list them.) Distributed by: (id)
devdw=# create view vv_01 as select * from tb_cp_04 where gender = ‘F‘; 创建视图 CREATE VIEW devdw=# \dv 使用“\dv”命令查看当前视图 List of relations Schema | Name | Type | Owner | Storage --------+-------+------+---------+--------- public | vv_01 | view | gpadmin | none (1 row) |
6.7.3.删除视图
使用DROP VIEW命令删除已有的视图。
|
devdw=# \h drop view 查看删除视图的帮助 Command: DROP VIEW Description: remove a view Syntax: DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
devdw=# drop view vv_01; 删除视图vv_01 DROP VIEW devdw=# \dv No relations found. |
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
