Snova運維篇（十）：gp數據庫中數據操作-2
- 2019 年 12 月 31 日
- 筆記
本節繼續前篇數據操作的內容進一步學習相關運維工作。
目錄：
- 定義數據庫對象
- 管理數據
基本概念：
|
|
|
|---|---|
|
視圖 |
在 SQL 中，視圖是基於 SQL 語句的結果集的可視化的表。 視圖包含行和列，就像一個真實的表。視圖中的字段就是來自一個或多個數據庫中的真實的表中的字段。我們可以向視圖添加 SQL 函數、WHERE 以及 JOIN 語句，我們也可以提交數據，就像這些來自於某個單一的表。 |
1.定義數據庫對象：
（一）對大型表分區
- Greenplum數據庫支持：
範圍分區：基於一個數字型範圍劃分數據，例如按照日期或價格劃分。
列表分區：基於一個值列表劃分數據，例如按照銷售範圍或產品線劃分。
兩種類型的組合。
- 創建分區表
- 定義日期範圍分區表
komablog=# CREATE TABLE sales (id int, date date, amt decimal(10,2))DISTRIBUTED BY (id)PARTITION BY RANGE (date)( START (date '2016-01-01') INCLUSIVE END (date '2017-01-01') EXCLUSIVE EVERY (INTERVAL '1 day') );
逐個聲明定義：
CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( PARTITION Jan16 START (date '2016-01-01') INCLUSIVE , PARTITION Feb16 START (date '2016-02-01') INCLUSIVE , PARTITION Mar16 START (date '2016-03-01') INCLUSIVE , PARTITION Apr16 START (date '2016-04-01') INCLUSIVE , PARTITION May16 START (date '2016-05-01') INCLUSIVE , PARTITION Jun16 START (date '2016-06-01') INCLUSIVE , PARTITION Jul16 START (date '2016-07-01') INCLUSIVE , PARTITION Aug16 START (date '2016-08-01') INCLUSIVE , PARTITION Sep16 START (date '2016-09-01') INCLUSIVE , PARTITION Oct16 START (date '2016-10-01') INCLUSIVE , PARTITION Nov16 START (date '2016-11-01') INCLUSIVE , PARTITION Dec16 START (date '2016-12-01') INCLUSIVE END (date '2017-01-01') EXCLUSIVE );
- 定義數字範圍分區
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2006) END (2016) EVERY (1), DEFAULT PARTITION extra );
- 列表表分區
komablog=# CREATE TABLE rank (id int, rank int, year int, genderchar(1), count int )DISTRIBUTED BY (id)PARTITION BY LIST (gender)( PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other );
komablog=# d+ rank Table "public.rank" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Child tables: rank_1_prt_boys, rank_1_prt_girls, rank_1_prt_other Has OIDs: no Distributed by: (id) Partition by: (gender)
- 定義多級分區
CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), DEFAULT SUBPARTITION other_regions) (START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates );
- 驗證分區表
komablog=# EXPLAIN SELECT * FROM sales WHERE date='01-07-12' AND komablog-# region='usa'; QUERY PLAN ----------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=24) -> Sequence (cost=0.00..431.00 rows=1 width=24) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=50 width=4) Partitions selected: 1 (out of 52) -> Dynamic Table Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24) Filter: date = '2012-01-07'::date AND region = 'usa'::text Optimizer status: PQO version 3.59.0 (7 rows)
- 查看分區設計
komablog=# SELECT partitionboundary, partitiontablename, partitionname, komablog-# partitionlevel, partitionrank komablog-# FROM pg_partitions komablog-# WHERE tablename='sales'; partitionboundary | partitiontablename | partitionname | partitionlevel | partitionrank -------------------------------------------------------------------------------+------------------------------------------------+ ----------------+----------------+--------------- DEFAULT PARTITION outlying_dates | sales_1_prt_outlying_dates | outlying_dates | 0 | START ('2011-01-01'::date) END ('2011-02-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_2 | | 0 | 1 START ('2011-02-01'::date) END ('2011-03-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_3 | | 0 | 2 START ('2011-03-01'::date) END ('2011-04-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_4 | | 0 | 3 START ('2011-04-01'::date) END ('2011-05-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_5
- 維護分區表
（二）創建和使用一個序列
- 創建一個序列
CREATE SEQUENCE myserial START 101;
- 使用序列
INSERT INTO vendors VALUES (nextval('myserial'), 'acme');
- 重置序列
SELECT setval('myserial', 201);
komablog=# SELECT setval('myserial', 201); setval -------- 201 (1 row)
komablog=# SELECT * FROM myserial; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- myserial | 201 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t (1 row)
- 修改一個序列
^ komablog=# alter sequence myserial restart with 105; ALTER SEQUENCE
- 刪除一個序列
DROP SEQUENCE myserial;
（三）gp中索引的使用
- 中間表索引
CREATE TABLE new_table (LIKE old_table) AS SELECT * FROM old_table ORDER BY myixcolumn; DROP old_table; ALTER TABLE new_table RENAME TO old_table; CREATE INDEX myixcolumn_ix ON old_table; VACUUM ANALYZE old_table;
- 索引類型
Greenplum數據庫數據庫支持Postgres索引類型B-樹和GiST，不支持Hash和GIN索引
- 創建索引
CREATE INDEX gender_idx ON employee (gender);
CREATE INDEX title_bmp_id x ON films USING bitmap (title);
- 重建索引
REINDEX my_table;
DROP INDEX title_idx;
在載入數據時，刪除所有索引、載入數據然後重建索引會更快。
（四）創建和管理視圖
- 創建視圖
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'comedy';
- 刪除視圖
DROP VIEW topten; DROP VIEW ... CASCADE命令也可以移除所有依賴的對象。
2.管理數據
- 插入行
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);
- 更新數據
UPDATE products SET price = 10 WHERE price = 5;
- 刪除行
DELETE FROM products WHERE price = 10;
DELETE FROM products;
- 清空表
TRUNCATE mytable;
- 清理過期數據
VACUUM mytable;
用下列服務器配置參數調整空閑空間映射的尺寸：
max_fsm_pages
max_fsm_relations
未完待續；
