這一章節(jié)里繼續(xù)討論表的內(nèi)容荧库,畢竟后面再高深的內(nèi)容都是建立在處理表數(shù)據(jù)的基礎(chǔ)之上的。
表繼承
表繼承其實是屬于表創(chuàng)建相關(guān)的內(nèi)容赵刑,但是如果這些內(nèi)容都放在表創(chuàng)建那一部分分衫,就顯得有點太長了,看的心煩般此,就在這里單獨來講講蚪战。表繼承,從字面意思上來看铐懊,就是從其他位置繼承一些東西過來邀桑,實際應(yīng)用上也是這樣,表繼承就是以其他表為模板來生成新的表科乎”诨基本命令格式如下:
create table table_child (like table_father);
我們來看一個示例:
postgres=# \d testdb1;
Table "public.testdb1"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null
comments | character varying(20) | default 'test'::character varying
parent_id | integer |
Indexes:
"testdb1_pkey" PRIMARY KEY, btree (id)
postgres=# select * from testdb1;
id | comments | parent_id
----+----------+-----------
1 | my test | 5
(1 row)
上面是一張表的結(jié)構(gòu)以及表中的數(shù)據(jù),我們以這張表為模板生成一張新表testdb2:
postgres=# create table testdb2 (like testdb1);
CREATE TABLE
postgres=# \d testdb2;
Table "public.testdb2"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
comments | character varying(20) |
parent_id | integer |
postgres=# select * from testdb2;
id | comments | parent_id
----+----------+-----------
(0 rows)
從上面的代碼中我們可以看到,生成的表testdb2和testdb1的結(jié)構(gòu)一模一樣捏萍,但是默認(rèn)情況下沒有繼承表testdb1中的數(shù)據(jù)以及comments字段的默認(rèn)值太抓。通過查詢官網(wǎng)文檔發(fā)現(xiàn),如果想繼承原表的各種屬性以及數(shù)據(jù)令杈,需要用到including和with關(guān)鍵字走敌,用法分別是:
- including defaults 繼承默認(rèn)值
- including constraint 繼承約束
- including indexes 繼承索引
- including storage 繼承存儲
- including comments 繼承注釋
- including all 繼承原表的所有屬性
- create table table_child as select * from table_father [with no data];
我們來嘗試一下,分別來生成一個繼承默認(rèn)值和一個繼承原表所有屬性的新表逗噩。
postgres=# create table testdb3 (like testdb1 including defaults);
CREATE TABLE
postgres=# \d testdb3
Table "public.testdb3"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null
comments | character varying(20) | default 'test'::character varying
parent_id | integer |
postgres=# select * from testdb3;
id | comments | parent_id
----+----------+-----------
(0 rows)
從上面可以看到繼承了默認(rèn)值掉丽,沒有繼承表中數(shù)據(jù)以及主鍵索引
postgres=# create table testdb4 (like testdb1 including all);
CREATE TABLE
postgres=# \d testdb4;
Table "public.testdb4"
Column | Type | Modifiers
-----------+-----------------------+-----------------------------------
id | integer | not null
comments | character varying(20) | default 'test'::character varying
parent_id | integer |
Indexes:
"testdb4_pkey" PRIMARY KEY, btree (id)
postgres=# select * from testdb4;
id | comments | parent_id
----+----------+-----------
(0 rows)
從上面可以看到,including all關(guān)鍵字會繼承所有屬性给赞,但是不會繼承原表的數(shù)據(jù)机打。
postgres=# create table testdb5 as select * from testdb1;
SELECT 1
postgres=# \d testdb5;
Table "public.testdb5"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer |
comments | character varying(20) |
parent_id | integer |
postgres=# select * from testdb5;
id | comments | parent_id
----+----------+-----------
1 | my test | 5
(1 row)
這個示例使用as關(guān)鍵字創(chuàng)建新表testdb5,不帶with子句片迅。從中可以看到默認(rèn)情況下残邀,as語句會將原來表里的數(shù)據(jù)復(fù)制到新表中,但是不會復(fù)制原來表中的屬性柑蛇,例如各種索引芥挣、約束等。那能不能通過把這兩種命令形式結(jié)合起來耻台,既可以繼承原表結(jié)構(gòu)空免,又可以復(fù)制原表數(shù)據(jù)呢?目前還是沒有這種命令的盆耽,只能通過完整的復(fù)制數(shù)據(jù)蹋砚,再添加索引、約束等摄杂,或者先復(fù)制完整的表結(jié)構(gòu)坝咐,再導(dǎo)入數(shù)據(jù)。
臨時表
PostgreSQL支持兩種類型的臨時表析恢,分別是會話級別臨時表和事務(wù)級別臨時表墨坚。同時,表中的數(shù)據(jù)也分為兩個級別映挂,一個是會話級別泽篮,一個是事務(wù)級別。因此就存在三種形式的臨時表柑船。分別是:
- 會話級別表帽撑、會話級別數(shù)據(jù)
- 會話級別表、事務(wù)級別數(shù)據(jù)
- 事務(wù)級別表鞍时、事務(wù)級別數(shù)據(jù)
第一種表和數(shù)據(jù)在會話周期都存在亏拉,會話銷毀,表和數(shù)據(jù)銷毀。第二種表在會話周期存在专筷,數(shù)據(jù)在事務(wù)周期存在弱贼,事務(wù)周期結(jié)束數(shù)據(jù)銷毀,會話結(jié)束表銷毀磷蛹。第三種表和數(shù)據(jù)在事務(wù)結(jié)束以后都會銷毀吮旅。
臨時表的創(chuàng)建
臨時表的創(chuàng)建和普通表的創(chuàng)建唯一的區(qū)別就是在創(chuàng)建語句中加了一個temporary關(guān)鍵字(也可以簡寫為temp),基本命令格式如下:
create temporary table table_name (field1 type1, field2 type...);
也可以使用我們上面表繼承的語法來創(chuàng)建臨時表:
create temp table table_name(like table1);
或
create temp table table_name as select * from table1;
根據(jù)前面說的味咳,臨時表是分成三種類型的庇勃,而采用上面的語句,默認(rèn)情況下創(chuàng)建的是會話級別的表和數(shù)據(jù)槽驶。而如果想創(chuàng)建其他級別的表责嚷,則需要使用下面幾個子句:
-
on commit perserve rows
這個子句和默認(rèn)情況相同,創(chuàng)建會話級別的表和數(shù)據(jù)掂铐,插入的數(shù)據(jù)保留到罕拂。 -
on commit delete rows
這個子句用來創(chuàng)建會話級別表和事務(wù)級別數(shù)據(jù),事務(wù)銷毀后表中數(shù)據(jù)銷毀全陨。 -
on commit drop
這個子句只能用在事務(wù)中創(chuàng)建臨時表爆班,在會話中創(chuàng)建只會顯示表格創(chuàng)建的提示消息,但是看不到表格辱姨,因為表格一創(chuàng)建成功就立即銷毀了柿菩。
通過下面幾個示例來看一下:
postgres=# create temp table temp_1 as select * from testdb1;
SELECT 1
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------+-------+----------
pg_temp_2 | temp_1 | table | postgres
public | testdb1 | table | postgres
public | testdb2 | table | postgres
(3 rows)
postgres=# create temp table temp_2 as select id, parent_id from testdb1;
SELECT 1
postgres=# \d temp_2;
Table "pg_temp_2.temp_2"
Column | Type | Modifiers
-----------+---------+-----------
id | integer |
parent_id | integer |
postgres=# create temp table temp_3 (like testdb1);
CREATE TABLE
postgres=# \d temp_3;
Table "pg_temp_2.temp_3"
Column | Type | Modifiers
-----------+-----------------------+-----------
id | integer | not null
comments | character varying(20) |
parent_id | integer |
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------+-------+----------
pg_temp_2 | temp_1 | table | postgres
pg_temp_2 | temp_2 | table | postgres
pg_temp_2 | temp_3 | table | postgres
public | testdb1 | table | postgres
public | testdb2 | table | postgres
(5 rows)
從上面可以看到,三張臨時表都是處在一個特殊的模式下雨涛,模式名稱是pg_temp_2枢舶。而在postgresql中,每個會話中創(chuàng)建的臨時表都是處在特殊的模式下替久,模式名稱一般都是pg_temp_x凉泄,x是根據(jù)不同的會話來分配的數(shù)字。我們來嘗試創(chuàng)建一下其他級別的表:
- 創(chuàng)建會話級別臨時表和事務(wù)級別數(shù)據(jù)侣肄。
postgres=# create temp table temp_4 (like temp_1 including all) on commit delete rows;
CREATE TABLE
postgres=# select * from temp_4;
id | comments | parent_id
----+----------+-----------
(0 rows)
postgres=# insert into temp_4 values(1, 'test4', 4);
INSERT 0 1
postgres=# select * from temp_4;
id | comments | parent_id
----+----------+-----------
(0 rows)
postgres=# begin;
BEGIN
postgres=# insert into temp_4 values(1, 'test4', 4);
INSERT 0 1
postgres=# select * from temp_4;
id | comments | parent_id
----+----------+-----------
1 | test4 | 4
(1 row)
postgres=# end;
COMMIT
postgres=# select * from temp_4;
id | comments | parent_id
----+----------+-----------
(0 rows)
上面的示例中旧困,我們操作依次是:
(1) 創(chuàng)建臨時表temp_4醇份,表是會話級別稼锅,數(shù)據(jù)是事務(wù)級別
(2)查看temp_4表,沒有任何數(shù)據(jù)
(3)插入一條數(shù)據(jù)僚纷,再看表矩距,還是沒有數(shù)據(jù)
(4)begin啟動一個事務(wù),往表中插入數(shù)據(jù)怖竭,查看表锥债,發(fā)現(xiàn)有一條數(shù)據(jù)
(5)end結(jié)束事務(wù),再查看表,表中數(shù)據(jù)消失哮肚。
示例清楚地解釋了這種臨時表的特性
- 事務(wù)臨時表和事務(wù)數(shù)據(jù)
postgres=# create temp table temp_5 (like temp_1 including all) on commit drop;
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------+-------+----------
pg_temp_2 | temp_1 | table | postgres
pg_temp_2 | temp_2 | table | postgres
pg_temp_2 | temp_3 | table | postgres
pg_temp_2 | temp_4 | table | postgres
public | testdb1 | table | postgres
public | testdb2 | table | postgres
(6 rows)
上面代碼中我們創(chuàng)建了一個事務(wù)級別表登夫,提示創(chuàng)建成功,但是看不到這個臨時表允趟。說明事務(wù)級別表在會話中沒法存在恼策,或者說創(chuàng)建之后就銷毀。我們在事務(wù)中再來看:
postgres=# begin;
BEGIN
postgres=# create temp table temp_5 (like temp_1 including all) on commit drop;
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------+-------+----------
pg_temp_2 | temp_1 | table | postgres
pg_temp_2 | temp_2 | table | postgres
pg_temp_2 | temp_3 | table | postgres
pg_temp_2 | temp_4 | table | postgres
pg_temp_2 | temp_5 | table | postgres
public | testdb1 | table | postgres
public | testdb2 | table | postgres
(7 rows)
postgres=# select * from temp_5;
id | comments | parent_id
----+----------+-----------
(0 rows)
postgres=# insert into temp_5 values(1, 'test5', 5);
INSERT 0 1
postgres=# select * from temp_5;
id | comments | parent_id
----+----------+-----------
1 | test5 | 5
(1 row)
postgres=# end;
COMMIT
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+---------+-------+----------
pg_temp_2 | temp_1 | table | postgres
pg_temp_2 | temp_2 | table | postgres
pg_temp_2 | temp_3 | table | postgres
pg_temp_2 | temp_4 | table | postgres
public | testdb1 | table | postgres
public | testdb2 | table | postgres
(6 rows)
上面的代碼是一個完整的事務(wù)潮剪,代碼功能依次是:
(1)在事務(wù)中先創(chuàng)建臨時表temp_5涣楷,查看表,確實存在抗碰。
(2)查看表中沒有任何數(shù)據(jù)狮斗,往表中插入一條數(shù)據(jù),再看表弧蝇,有一條數(shù)據(jù)
(3)退出事務(wù)碳褒,再看表信息,temp_5這個臨時表已經(jīng)消失了看疗。
相信經(jīng)過這幾個例子骤视,大家對臨時表的特性已經(jīng)有所了解了。
表分區(qū)
表分區(qū)是通過某種標(biāo)準(zhǔn)將一個大表劃分成若干個小表鹃觉,然后創(chuàng)建觸發(fā)器专酗,在插入數(shù)據(jù)的時候,觸發(fā)器根據(jù)指定的標(biāo)準(zhǔn)盗扇,將數(shù)據(jù)插入到不同的小表中去祷肯。可以提高性能疗隶,以及查詢速度佑笋。標(biāo)準(zhǔn)可以是日期、時間斑鼻、地區(qū)等等蒋纬。
表分區(qū)的實現(xiàn)依賴于表繼承,這里的表繼承和上面所說的表繼承略有區(qū)別坚弱,上面的表繼承是通過復(fù)制父表的結(jié)構(gòu)生成子表蜀备,而這里的表繼承,指的是完全繼承父表的表結(jié)構(gòu)荒叶,同時還可以添加自己的字段來生成一張新的子表碾阁。如下所示:
postgres=# create table father(name text, age int);
CREATE TABLE
postgres=# create table child(language text) inherits (father);
CREATE TABLE
postgres=# \d father;
Table "public.father"
Column | Type | Modifiers
--------+---------+-----------
name | text |
age | integer |
Number of child tables: 1 (Use \d+ to list them.)
postgres=# \d child;
Table "public.child"
Column | Type | Modifiers
----------+---------+-----------
name | text |
age | integer |
language | text |
Inherits: father
在上面的示例種,子表child繼承父表father些楣,同時添加了自己字段language脂凶。
而且這種表繼承還具有不同的特性宪睹,這種類型的表繼承中,查詢父表蚕钦,可以查詢到所有子表中對應(yīng)字段的內(nèi)容亭病。我們通過幾個例子來看一下這種表繼承的特性:
postgres=# select * from father;
name | age
------+-----
(0 rows)
postgres=# slect * from child
postgres-# ;
ERROR: syntax error at or near "slect"
LINE 1: slect * from child
^
postgres=# select * from child;
name | age | language
------+-----+----------
(0 rows)
postgres=# insert into child values('張三', 16, '中文');
INSERT 0 1
postgres=# insert into child values('tom', 19, 'English');
INSERT 0 1
postgres=# select * from father;
name | age
------+-----
張三 | 16
tom | 19
(2 rows)
postgres=# select * from child;
name | age | language
------+-----+----------
張三 | 16 | 中文
tom | 19 | English
(2 rows)
從上面的例子中可以看到,往子表中插入的數(shù)據(jù)嘶居,在父表中可以看到對應(yīng)字段的數(shù)據(jù)命贴。再看下面這個例子:
postgres=# insert into father values('李四', 25);
INSERT 0 1
postgres=# select * from father;
name | age
------+-----
李四 | 25
張三 | 16
tom | 19
(3 rows)
postgres=# select * from child;
name | age | language
------+-----+----------
張三 | 16 | 中文
tom | 19 | English
(2 rows)
可以看到,往父表中插入的數(shù)據(jù)食听,在子表中卻看不到胸蛛。
而這種類型的表繼承的特性恰好是表分區(qū)實現(xiàn)的基礎(chǔ),可以通過查詢主表來查詢主表下所有分區(qū)表的內(nèi)容樱报。往分區(qū)表里插入數(shù)據(jù)葬项,相當(dāng)于是往主表中插入數(shù)據(jù)。在這里迹蛤,主表相當(dāng)于是子表民珍,分區(qū)表相當(dāng)于是父表。同時盗飒,還要通過觸發(fā)器來實現(xiàn)往主表里插入數(shù)據(jù)或查詢數(shù)據(jù)時嚷量,自動轉(zhuǎn)到對應(yīng)的分區(qū)表上。因此還要了解觸發(fā)器的創(chuàng)建規(guī)則逆趣。因此這部分內(nèi)容在學(xué)完觸發(fā)器的知識后再來看蝶溶。
表的存儲屬性
表的存儲屬性指的是
TOAST用于存儲大字段的技術(shù),在理解這個技術(shù)之前宣渗,先了解一下頁的概念抖所。頁在PostgreSQL中是數(shù)據(jù)在文件存儲中的單位,其大小是固定的且只能在編譯時指定痕囱,之后無法修改田轧,默認(rèn)的大小為8 KB 。同時鞍恢,PG 不允許一行數(shù)據(jù)跨頁存儲傻粘,那么對于超長的行數(shù)據(jù),PG 就會啟動 TOAST 帮掉,具體就是采用壓縮和切片的方式弦悉。如果啟用了切片,實際上行數(shù)據(jù)存儲在另一張系統(tǒng)表的多個行中旭寿,這張表就叫 TOAST 表警绩,這種存儲方式叫行外存儲崇败。
而一般情況下盅称,只有變長類型才會支持TOAST技術(shù)肩祥,在變長類型中,前4個字節(jié)是長度字缩膝。長度字的前2位是標(biāo)志位混狠,后30位是長度值。長度值包含自身占用的4個字節(jié)疾层,因此将饺,TOAST數(shù)據(jù)類型的長度最大應(yīng)該是30b,及1GB(2^30-1)個字節(jié)痛黎。
前2位標(biāo)志位予弧,第一個表示是否壓縮,第二個表示是否是行外存儲湖饱。兩個都是0表示不壓縮掖蛤,不是行外存儲。第一個為1表示壓縮過井厌,使用之前必須解壓蚓庭。第二個為1表示行外存儲,此時仅仆,長度字后面存儲的只是一個指針器赞。不管數(shù)據(jù)是否壓縮,長度位后30bit都表示的是數(shù)據(jù)的真實大小墓拜,而不會是壓縮以后的大小港柜。
如果一張表A有對應(yīng)的toast表,那么在系統(tǒng)表pg_class里面就會有一個表名A對應(yīng)一個toast表的OID咳榜,然后根據(jù)這個oid就能找到對應(yīng)的toast表pg_toast_oid潘懊。如果么某個大字段的數(shù)據(jù)存放在toast表中,那么這個字段的數(shù)據(jù)就會被切片成頁面大小1/4大小的塊贿衍。然后將這些數(shù)據(jù)塊存放在toast表中授舟。每個toast表有chunk_id、chunk_seq和chunk_data字段贸辈,數(shù)據(jù)塊就放在chunk_data字段中释树。在chunk_id和chunk_seq上有一個唯一的索引,用于對數(shù)據(jù)的快速檢索擎淤。因此在前面變長類型的字段的長度位中奢啥,如果存儲的是指針的話,那么該指針就包含toast表的OID和特定數(shù)值的chunk_id嘴拢,以及數(shù)據(jù)的實際長度桩盲。
在 PG 中每個表字段有四種 TOAST 的策略:
- PLAIN :避免壓縮和行外存儲。只有那些不需要 TOAST 策略就能存放的數(shù)據(jù)類型允許選擇(例如 int 類型)席吴,而對于 text 這類要求存儲長度超過頁大小的類型赌结,是不允許采用此策略的捞蛋。
- EXTENDED :允許壓縮和行外存儲。一般會先壓縮柬姚,如果還是太大拟杉,就會行外存儲
- EXTERNA :允許行外存儲,但不許壓縮量承。類似字符串這種會對數(shù)據(jù)的一部分進行操作的字段搬设,采用此策略可能獲得更高的性能,因為不需要讀取出整行數(shù)據(jù)再解壓撕捍。
- MAIN :允許壓縮拿穴,但不許行外存儲。不過實際上忧风,為了保證過大數(shù)據(jù)的存儲贞言,行外存儲在其它方式(例如壓縮)都無法滿足需求的情況下,作為最后手段還是會被啟動阀蒂。因此理解為:盡量不使用行外存儲更貼切该窗。
在PG中,會為每個允許TOAST的字段設(shè)置一個默認(rèn)的策略蚤霞。但是可以使用ALTER TABLE SET STORAGE
命令來修改系統(tǒng)設(shè)置的默認(rèn)策略酗失。
默認(rèn)情況下,只有當(dāng)一個字段數(shù)據(jù)的大小超過頁大小的1/4的時候昧绣,才會觸發(fā)壓縮策略规肴。即對于8k頁大小的數(shù)據(jù)庫來說,字段數(shù)據(jù)大小超過2k時才會觸發(fā)壓縮策略夜畴。在這種情況下拖刃,如果兩張結(jié)構(gòu)相同的表A和B,A中某個字段存儲的數(shù)據(jù)小于2k贪绘,而B中該字段的數(shù)據(jù)大于2k兑牡,會出現(xiàn)B表所占用的空間反而小于A表。
從上面這些內(nèi)容來看税灌,所謂的存儲屬性均函,應(yīng)該指的就是針對字段設(shè)置的各種策略。此外菱涤,還可以針對表設(shè)置fillfactor和toast.fillfactor這兩個值苞也。這兩個值表示表的填充因子和toast表的填充因子,填充因子表示的是一個數(shù)據(jù)塊的填充比例粘秆,即一個數(shù)據(jù)庫填充到多少以后就不填充數(shù)據(jù)了如迟,剩下的空間留做數(shù)據(jù)更新時使用。
在PG中,更新舊數(shù)據(jù)時殷勘,不會刪除舊數(shù)據(jù)此再,而是在保留的空間里寫入一條新數(shù)據(jù),然后在舊數(shù)據(jù)和新數(shù)據(jù)之間創(chuàng)建一個鏈表劳吠,這樣就不用更新索引引润,索引找到舊數(shù)據(jù)時巩趁,會根據(jù)鏈表找到新數(shù)據(jù)痒玩。但是如果填充因子設(shè)置的過大,更新舊數(shù)據(jù)時议慰,保留的空間不足以存放更新的數(shù)據(jù)蠢古,且數(shù)據(jù)不能跨頁存放,因此必須放在新的數(shù)據(jù)塊别凹,此時舊需要更新索引草讶。會更耗時,因此炉菲,對于數(shù)據(jù)更新頻繁的表堕战,這個值可以設(shè)置的小一點。