postgresql分割槽表實現方式

1. 概述分割槽表    
分割槽的意思是把邏輯上的一個大表分割成物理上的幾塊兒，分割槽可以提供若干好處：    
1). 某些型別的查詢效能可以得到極大提升。    
2). 更新的效能也可以得到提升，因為表的每塊的索引要比在整個資料集上的索引要小。如果索引不能全部放在記憶體裡，那麼在索引上的讀和寫都會產生更多的磁碟訪問。    
3). 批次刪除可以用簡單地刪除某個分割槽來實現。    
4). 將很少用的資料可以移動到便宜的、慢一些地儲存介質上。     
假設當前的資料庫並不支援分割槽表，而我們的應用所需處理的資料量也非常大，對於這種應用場景，我們不得不人為的將該大表按 照一定的規則，手工拆分成多個小表，讓每個小表包含不同區間的資料。這樣一來，我們就必須在資料插入、更新、刪除和查詢之前，先計算本次的指令需要操作的小表。對於有些查詢而言，由於查詢區間可能會跨越多個小表，這樣我們又不得不將多個小表的查詢結 果進行 union 操作，以合併來自多個表的資料，並最終形成一個結果集返回給客戶端。可見，如果我們正在使用的資料庫不支援分割槽 表，那麼在適合其應用的場景下，我們就需要做很多額外的程式設計工作以彌補這一缺失。然而需要說明的是，儘管功能可以勉強應付， 但是效能卻和分割槽表無法相提並論。    
目前 PostgreSQL 支援的分割槽形式主要為以下兩種：    
1).範圍分割槽: 表被一個或者多個鍵字欄位分割槽成"範圍"，在這些範圍之間沒有重疊的數值分佈到不同的分割槽裡。比如，我們可以為特定的商業物件根據資料範圍分割槽，或者根據識別符號範圍分割槽。    
2).列表分割槽: 表是透過明確地列出每個分割槽裡應該出現那些鍵字值實現的。  

2.測試案例
--登入到資料庫
postgres=# select current_database();
 current_database
------------------
 postgres
(1 row)
--建立資料庫
postgres=# create database mytest;
--切換資料庫
postgres=# \c mytest;
You are now connected to database "mytest" as user "postgres".
--建立父表
mytest=# create table parent(dpart_id bigserial,person_id bigserial,name varchar(20),birth date);
CREATE TABLE
mytest=# select * from parent;
 dpart_id | person_id | name | birth
----------+-----------+------+-------
(0 rows)

--建立子表
pgsql本身像oracle那樣的分割槽定義什麼的，透過子表對父表的繼承定義實現的，所以有多少個分割槽，我們就必須建多少個子表。
mytest=# create table child_t01(check(dpart_id > 0 and dpart_id < 20)) inherits(parent);
CREATE TABLE
mytest=# create table child_t02(check(dpart_id >= 20 and dpart_id < 40)) inherits(parent);
CREATE TABLE
mytest=# create table child_t03(check(dpart_id >= 40 and dpart_id < 60)) inherits(parent);
CREATE TABLE
mytest=# create table child_t04(check(dpart_id >= 60 and dpart_id < 80)) inherits(parent);
CREATE TABLE
mytest=# create table child_t05(check(dpart_id >= 80 and dpart_id < 100)) inherits(parent);
CREATE TABLE
mytest=# create table child_t06(check(dpart_id >= 100)) inherits(parent);

--建立規則
建立好子表與父表的繼承關係後，不必須定義規則，然後才能把相應的資料插入到指定的子表中，具體過程如下：
mytest=# CREATE OR REPLACE RULE t01_qualified AS
ON INSERT TO parent WHERE dpart_id > 0 and dpart_id < 20
DO INSTEAD
INSERT INTO child_t01 VALUES(NEW.*);
CREATE RULE
其它幾個子表也按這種方式建立。

--插入資料測試
mytest=# insert into parent(dpart_id,person_id,name,birth) values(10,10011,'TOM',date'1984-05-04');
INSERT 0 0
mytest=# select * from parent;
 dpart_id | person_id | name |   birth   
----------+-----------+------+------------
       10 |     10011 | TOM  | 1984-05-04
(1 row)

mytest=# select * from only parent;
 dpart_id | person_id | name | birth
----------+-----------+------+-------
(0 rows)
可以看到資料並沒有儲存到父表中，而儲存在子表中

mytest=# select count(1) from parent;
 count
-------
    23
(1 row)

mytest=# select count(*) from only parent;
 count
-------
     0
(1 row)
總共插入了23條，我們可以看到父表中沒有資料，都透過子表的。
mytest=#  explain select * from parent where dpart_id = 120;
                           QUERY PLAN                          
----------------------------------------------------------------
 Append  (cost=0.00..1.05 rows=2 width=78)
   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=78)
         Filter: (dpart_id = 120)
   ->  Seq Scan on child_t06  (cost=0.00..1.05 rows=1 width=78)
         Filter: (dpart_id = 120)
從子表中查詢出來的。
mytest=# explain select * from parent where dpart_id > 20 and dpart_id <=70;
                           QUERY PLAN                          
----------------------------------------------------------------
 Append  (cost=0.00..3.18 rows=4 width=78)
   ->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=78)
         Filter: ((dpart_id > 20) AND (dpart_id <= 70))
   ->  Seq Scan on child_t02  (cost=0.00..1.06 rows=1 width=78)
         Filter: ((dpart_id > 20) AND (dpart_id <= 70))
   ->  Seq Scan on child_t03  (cost=0.00..1.06 rows=1 width=78)
         Filter: ((dpart_id > 20) AND (dpart_id <= 70))
   ->  Seq Scan on child_t04  (cost=0.00..1.06 rows=1 width=78)
         Filter: ((dpart_id > 20) AND (dpart_id <= 70))
(9 rows)

3、其它方式
在pgsql中，分割槽表透過父表與子表的繼承關係，再加上定義的規則實現。當然也可以透過其它方式實現，如觸發器
首先建立一個函式
CREATE OR REPLACE FUNCTION parent_insert_f()
RETURNS TRIGGER AS
$$
BEGIN
     IF(NEW.dpart_id > 0 and NEW.dpart_id < 20) THEN
         INSERT INTO child_t01 VALUES (NEW.*);
     END IF;
     IF(NEW.dpart_id >= 20 and NEW.dpart_id < 40) THEN
         INSERT INTO child_t02 VALUES (NEW.*);
     END IF;
..............   --以此推算
     RETURN NULL;
END;
$$
LANGUAGE plpgsql ;

透過trigger呼叫函式
CREATE TRIGGER parent_insert_tr
    BEFORE INSERT ON parent
     FOR EACH row
    EXECUTE PROCEDURE parent_insert_f() ;
   
4、總結
總的說來，分割槽表實現還是比較麻煩的，不如oracle靈活，如果遇到複雜情況，就比較麻煩了。並且支援的分割槽也相對少一些。