亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

PostgreSQL分區表(Table Partitioning)應用

系統 3666 0

一、簡介

  在數據庫日漸龐大的今天,為了方便對數據庫數據的管理,比如按時間,按地區去統計一些數據時,基數過于龐大,多有不便。很多商業數據庫都提供分區的概念,按不同的維度去存放數據,便于后期的管理,PostgreSQL也不例外。

  PostgresSQL分區的意思是把邏輯上的一個大表分割成物理上的幾塊兒。分區不僅能帶來訪問速度的提升,關鍵的是,它能帶來管理和維護上的方便。

  分區的具體好處是:

  • 某些類型的查詢性能可以得到極大提升。
  • 更新的性能也可以得到提升,因為表的每塊的索引要比在整個數據集上的索引要小。如果索引不能全部放在內存里,那么在索引上的讀和寫都會產生更多的磁盤訪問。
  • 批量刪除可以用簡單的刪除某個分區來實現。
  • 可以將很少用的數據移動到便宜的、轉速慢的存儲介質上。

  在PG里表分區是通過表繼承來實現的,一般都是建立一個主表,里面是空,然后每個分區都去繼承它。無論何時,都應保證主表里面是空的。

  小表分區不實際,表在多大情況下才考慮分區呢?PostgresSQL官方給出的建議是:當表本身大小超過了機器物理內存的實際大小時(the size of the table should exceed the physical memory of the database server),可以考慮分區。

  PG目前(9.2.2)僅支持范圍分區和列表分區,尚未支持散列分區。

二、環境

系統環境:CentOS release 6.3 (Final)

PostgreSQL版本:PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、實現分區

3.1 創建主表

      david
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition (

david(# id 
      
      
        integer
      
      
        ,

david(# name 
      
      
        varchar
      
      (
      
        20
      
      
        ),

david(# gender boolean,

david(# join_date date,

david(# dept 
      
      
        char
      
      (
      
        4
      
      
        ));


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      #
    

3.2 創建分區表

      david
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201211 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2012-11-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
      
         )       

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201212 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-01-01
      
      
        '
      
      
         )      

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201301 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-01-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-02-01
      
      
        '
      
      
         )      

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201302 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-02-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-03-01
      
      
        '
      
      
         )  

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201303 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-03-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
      
         ) 

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201304 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-05-01
      
      
        '
      
      
         )  

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201305 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-05-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-06-01
      
      
        '
      
      
         )  

) INHERITS (tbl_partition);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
    

3.3 分區鍵上建索引

      david
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201211_joindate 
      
        on
      
      
         tbl_partition_201211 (join_date);


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201212_joindate 
      
        on
      
      
         tbl_partition_201212 (join_date); 


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201301_joindate 
      
        on
      
      
         tbl_partition_201301 (join_date);    


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201302_joindate 
      
        on
      
      
         tbl_partition_201302 (join_date); 


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201303_joindate 
      
        on
      
      
         tbl_partition_201303 (join_date); 


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201304_joindate 
      
        on
      
      
         tbl_partition_201304 (join_date); 


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201305_joindate 
      
        on
      
      
         tbl_partition_201305 (join_date); 


      
      
        CREATE
      
      
        INDEX
      
      
        

david
      
      
        =
      
      # 
    

對于開發人員來說,希望數據庫是透明的,只管 insert into tbl_partition。對于數據插向哪個分區,則希望由DB決定。這點,ORACLE實現了,但是PG不行,需要前期人工處理下。

3.4 創建觸發器函數

      david
      
        =
      
      # 
      
        CREATE
      
      
        OR
      
      
        REPLACE
      
      
        FUNCTION
      
      
         tbl_partition_insert_trigger()                      


      
      
        RETURNS
      
      
        TRIGGER
      
      
        AS
      
      
         $$  


      
      
        BEGIN
      
      
        IF
      
       ( NEW.join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2012-11-01
      
      
        '
      
      
        AND
      
      
            

         NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201211 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );  

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
      
        AND
      
      
          

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-01-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201212 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );  

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-01-01
      
      
        '
      
      
        AND
      
      
          

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-02-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201301 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );  

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-02-01
      
      
        '
      
      
        AND
      
      
          

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-03-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201302 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );  

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-03-01
      
      
        '
      
      
        AND
      
      
          

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201303 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );  

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        AND
      
      
          

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-05-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201304 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );

    ELSIF ( NEW.join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-05-01
      
      
        '
      
      
        AND
      
      
        

            NEW.join_date 
      
      
        <
      
       DATE 
      
        '
      
      
        2013-06-01
      
      
        '
      
       ) 
      
        THEN
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201305 
      
        VALUES
      
       (NEW.
      
        *
      
      
        ); 

    
      
      
        ELSE
      
      
          

        RAISE EXCEPTION 
      
      
        '
      
      
        Date out of range. Fix the tbl_partition_insert_trigger() function!
      
      
        '
      
      
        ;  

    
      
      
        END
      
      
        IF
      
      
        ;  

    
      
      
        RETURN
      
      
        NULL
      
      
        ;  


      
      
        END
      
      
        ;  

$$  

LANGUAGE plpgsql;


      
      
        CREATE
      
      
        FUNCTION
      
      
        

david
      
      
        =
      
      # 
    

說明: 如果不想丟失數據,上面的ELSE 條件可以改成?INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同時需要創建一張結構和tbl_partition 一樣的表tbl_partition_error_join_date,這樣,錯誤的join_date 數據就可以插入到這張表中而不是報錯了。

3.5 創建觸發器

      david
      
        =
      
      # 
      
        CREATE
      
      
        TRIGGER
      
      
         insert_tbl_partition_trigger

david
      
      
        -
      
      #     BEFORE 
      
        INSERT
      
      
        ON
      
      
         tbl_partition

david
      
      
        -
      
      #     
      
        FOR
      
       EACH ROW 
      
        EXECUTE
      
      
        PROCEDURE
      
      
         tbl_partition_insert_trigger();


      
      
        CREATE
      
      
        TRIGGER
      
      
        

david
      
      
        =
      
      # 
    

四、查看表

4.1 查看所有表

      david
      
        =
      
      
        # \dt

                List 
      
      
        of
      
      
         relations

 
      
      
        Schema
      
      
        |
      
               Name         
      
        |
      
       Type  
      
        |
      
      
          Owner   


      
      
        --
      
      
        ------+----------------------+-------+----------
      
      
        public
      
      
        |
      
       tbl_partition        
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201211 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201212 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201301 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201302 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201303 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201304 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

 
      
      
        public
      
      
        |
      
       tbl_partition_201305 
      
        |
      
      
        table
      
      
        |
      
      
         postgres

(
      
      
        8
      
      
         rows)



david
      
      
        =
      
      # 
    

4.2 查看主表

      david
      
        =
      
      
        # \d tbl_partition

         
      
      
        Table
      
       "
      
        public
      
      
        .tbl_partition"

  
      
      
        Column
      
      
        |
      
               Type          
      
        |
      
      
         Modifiers 


      
      
        --
      
      
        ---------+-----------------------+-----------
      
      

 id        
      
        |
      
      
        integer
      
      
        |
      
      
         

 name      
      
      
        |
      
      
        character
      
      
        varying
      
      (
      
        20
      
      ) 
      
        |
      
      
         

 gender    
      
      
        |
      
       boolean               
      
        |
      
      
         

 join_date 
      
      
        |
      
       date                  
      
        |
      
      
         

 dept      
      
      
        |
      
      
        character
      
      (
      
        4
      
      )          
      
        |
      
      
         

Triggers:

    insert_tbl_partition_trigger BEFORE 
      
      
        INSERT
      
      
        ON
      
       tbl_partition 
      
        FOR
      
       EACH ROW 
      
        EXECUTE
      
      
        PROCEDURE
      
      
         tbl_partition_insert_trigger()


      
      
        Number
      
      
        of
      
       child tables: 
      
        7
      
       (
      
        Use
      
       \d
      
        +
      
      
        to
      
      
         list them.)



david
      
      
        =
      
      # 
    

4.3 查看分區表

      david
      
        =
      
      
        # \d tbl_partition_201304

      
      
      
        Table
      
       "
      
        public
      
      
        .tbl_partition_201304"

  
      
      
        Column
      
      
        |
      
               Type          
      
        |
      
      
         Modifiers 


      
      
        --
      
      
        ---------+-----------------------+-----------
      
      

 id        
      
        |
      
      
        integer
      
      
        |
      
      
         

 name      
      
      
        |
      
      
        character
      
      
        varying
      
      (
      
        20
      
      ) 
      
        |
      
      
         

 gender    
      
      
        |
      
       boolean               
      
        |
      
      
         

 join_date 
      
      
        |
      
       date                  
      
        |
      
      
         

 dept      
      
      
        |
      
      
        character
      
      (
      
        4
      
      )          
      
        |
      
      
         

Indexes:

    "tbl_partition_201304_joindate" btree (join_date)


      
      
        Check
      
      
         constraints:

    "tbl_partition_201304_join_date_check" 
      
      
        CHECK
      
       (join_date 
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      ::date 
      
        AND
      
       join_date 
      
        <
      
      
        '
      
      
        2013-05-01
      
      
        '
      
      
        ::date)

Inherits: tbl_partition



david
      
      
        =
      
      #
    

五、測試

5.1 插入數據

      david
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        1
      
      , 
      
        '
      
      
        David
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2013-01-10
      
      
        '
      
      , 
      
        '
      
      
        TS
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        2
      
      , 
      
        '
      
      
        Sandy
      
      
        '
      
      , 
      
        '
      
      
        0
      
      
        '
      
      , 
      
        '
      
      
        2013-02-10
      
      
        '
      
      , 
      
        '
      
      
        TS
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        3
      
      , 
      
        '
      
      
        Eagle
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2012-11-01
      
      
        '
      
      , 
      
        '
      
      
        TS
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        4
      
      , 
      
        '
      
      
        Miles
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2012-12-15
      
      
        '
      
      , 
      
        '
      
      
        SD
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        5
      
      , 
      
        '
      
      
        Simon
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2012-12-10
      
      
        '
      
      , 
      
        '
      
      
        SD
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        6
      
      , 
      
        '
      
      
        Rock
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2012-11-10
      
      
        '
      
      , 
      
        '
      
      
        SD
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        7
      
      , 
      
        '
      
      
        Peter
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2013-01-11
      
      
        '
      
      , 
      
        '
      
      
        SD
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        8
      
      , 
      
        '
      
      
        Sally
      
      
        '
      
      , 
      
        '
      
      
        0
      
      
        '
      
      , 
      
        '
      
      
        2013-03-10
      
      
        '
      
      , 
      
        '
      
      
        BCSC
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        9
      
      , 
      
        '
      
      
        Carrie
      
      
        '
      
      , 
      
        '
      
      
        0
      
      
        '
      
      , 
      
        '
      
      
        2013-04-02
      
      
        '
      
      , 
      
        '
      
      
        BCSC
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        10
      
      , 
      
        '
      
      
        Lee
      
      
        '
      
      , 
      
        '
      
      
        1
      
      
        '
      
      , 
      
        '
      
      
        2013-01-05
      
      
        '
      
      , 
      
        '
      
      
        BMC
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        11
      
      , 
      
        '
      
      
        Nicole
      
      
        '
      
      , 
      
        '
      
      
        0
      
      
        '
      
      , 
      
        '
      
      
        2012-11-10
      
      
        '
      
      , 
      
        '
      
      
        PROJ
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
      
        insert
      
      
        into
      
       tbl_partition 
      
        values
      
       (
      
        12
      
      , 
      
        '
      
      
        Renee
      
      
        '
      
      , 
      
        '
      
      
        0
      
      
        '
      
      , 
      
        '
      
      
        2013-01-10
      
      
        '
      
      , 
      
        '
      
      
        TS
      
      
        '
      
      
        );


      
      
        INSERT
      
      
        0
      
      
        0
      
      
        

david
      
      
        =
      
      # 
    

5.2 查看主表數據

      david
      
        =
      
      # 
      
        select
      
      
        *
      
      
        from
      
      
         tbl_partition;

 id 
      
      
        |
      
        name  
      
        |
      
       gender 
      
        |
      
       join_date  
      
        |
      
      
         dept 


      
      
        --
      
      
        --+--------+--------+------------+------
      
      
        3
      
      
        |
      
       Eagle  
      
        |
      
       t      
      
        |
      
      
        2012
      
      
        -
      
      
        11
      
      
        -
      
      
        01
      
      
        |
      
      
         TS  

  
      
      
        6
      
      
        |
      
       Rock   
      
        |
      
       t      
      
        |
      
      
        2012
      
      
        -
      
      
        11
      
      
        -
      
      
        10
      
      
        |
      
      
         SD  

 
      
      
        11
      
      
        |
      
       Nicole 
      
        |
      
       f      
      
        |
      
      
        2012
      
      
        -
      
      
        11
      
      
        -
      
      
        10
      
      
        |
      
      
         PROJ

  
      
      
        4
      
      
        |
      
       Miles  
      
        |
      
       t      
      
        |
      
      
        2012
      
      
        -
      
      
        12
      
      
        -
      
      
        15
      
      
        |
      
      
         SD  

  
      
      
        5
      
      
        |
      
       Simon  
      
        |
      
       t      
      
        |
      
      
        2012
      
      
        -
      
      
        12
      
      
        -
      
      
        10
      
      
        |
      
      
         SD  

  
      
      
        1
      
      
        |
      
       David  
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        10
      
      
        |
      
      
         TS  

  
      
      
        7
      
      
        |
      
       Peter  
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        11
      
      
        |
      
      
         SD  

 
      
      
        10
      
      
        |
      
       Lee    
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        05
      
      
        |
      
      
         BMC 

 
      
      
        12
      
      
        |
      
       Renee  
      
        |
      
       f      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        10
      
      
        |
      
      
         TS  

  
      
      
        2
      
      
        |
      
       Sandy  
      
        |
      
       f      
      
        |
      
      
        2013
      
      
        -
      
      
        02
      
      
        -
      
      
        10
      
      
        |
      
      
         TS  

  
      
      
        8
      
      
        |
      
       Sally  
      
        |
      
       f      
      
        |
      
      
        2013
      
      
        -
      
      
        03
      
      
        -
      
      
        10
      
      
        |
      
      
         BCSC

  
      
      
        9
      
      
        |
      
       Carrie 
      
        |
      
       f      
      
        |
      
      
        2013
      
      
        -
      
      
        04
      
      
        -
      
      
        02
      
      
        |
      
      
         BCSC

(
      
      
        12
      
      
         rows)



david
      
      
        =
      
      # 
    

5.3 查看分區表數據

      david
      
        =
      
      # 
      
        select
      
      
        *
      
      
        from
      
      
         tbl_partition_201301 ;

 id 
      
      
        |
      
       name  
      
        |
      
       gender 
      
        |
      
       join_date  
      
        |
      
      
         dept 


      
      
        --
      
      
        --+-------+--------+------------+------
      
      
        1
      
      
        |
      
       David 
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        10
      
      
        |
      
      
         TS  

  
      
      
        7
      
      
        |
      
       Peter 
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        11
      
      
        |
      
      
         SD  

 
      
      
        10
      
      
        |
      
       Lee   
      
        |
      
       t      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        05
      
      
        |
      
      
         BMC 

 
      
      
        12
      
      
        |
      
       Renee 
      
        |
      
       f      
      
        |
      
      
        2013
      
      
        -
      
      
        01
      
      
        -
      
      
        10
      
      
        |
      
      
         TS  

(
      
      
        4
      
      
         rows)



david
      
      
        =
      
      # 
    

六、管理分區

6.1 移除數據/分區

實現分區表之后,我們就可以很容易地移除不再使用的舊數據了,最簡單的方法就是:

      david
      
        =
      
      # 
      
        drop
      
      
        table
      
       tbl_partition_201304;
    

這樣可以快速移除大量數據,而不是逐條刪除數據。

另一個推薦做法是將分區從分區表中移除,但是保留訪問權限。

      david
      
        =
      
      # 
      
        alter
      
      
        table
      
      
         tbl_partition_201304 no inherit tbl_partition;


      
      
        ALTER
      
      
        TABLE
      
      
        

david
      
      
        =
      
      #
    

和直接DROP 相比,該方式僅僅是使子表脫離了原有的主表,而存儲在子表中的數據仍然可以得到訪問,因為此時該表已經被還原成一個普通的數據表了。這樣對于數據庫的DBA來說,就可以在此時對該表進行必要的維護操作,如數據清理、歸檔等,在完成諸多例行性的操作之后,就可以考慮是直接刪除該表(DROP TABLE),還是先清空該表的數據(TRUNCATE TABLE),之后再讓該表重新繼承主表。

      david
      
        =
      
      # 
      
        alter
      
      
        table
      
      
         tbl_partition_201304 inherit tbl_partition;                                                            


      
      
        ALTER
      
      
        TABLE
      
      
        

david
      
      
        =
      
      # 
    

6.2 增加分區

我們可以像之前那樣增加一個分區

      david
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201306 (


      
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-06-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-07-01
      
      
        '
      
      
         )  

) INHERITS (tbl_partition);
      
    
      david
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201306_joindate 
      
        on
      
       tbl_partition_201306 (join_date);
    

同時,需要修改觸發器函數,將插入條件改成相應的值。

說明: 創建觸發器函數時,最好把插入條件寫更未來一點,比如多寫十年,這樣以后增加新分區時就不需要重新創建觸發器函數了,也可以避免一些不必要的錯誤。

另外,還可以如下增加新的分區:

      david
      
        =
      
      # 
      
        create
      
      
        table
      
      
         tbl_partition_201307

david
      
      
        -
      
      # (
      
        LIKE
      
      
         tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS);


      
      
        CREATE
      
      
        TABLE
      
      
        

david
      
      
        =
      
      
        #



david
      
      
        =
      
      # 
      
        alter
      
      
        table
      
       tbl_partition_201307 
      
        add
      
      
        constraint
      
      
         tbl_partition_201307_join_date_check 

david
      
      
        -
      
      # 
      
        check
      
       ( join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-07-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-08-01
      
      
        '
      
      
         );


      
      
        ALTER
      
      
        TABLE
      
      
        

david
      
      
        =
      
      
        #



david
      
      
        =
      
      # 
      
        create
      
      
        index
      
       tbl_partition_201307_joindate 
      
        on
      
      
         tbl_partition_201307 (join_date);
        

david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql'; //從文件中拷貝數據,這些數據可以是事前準備的 david
= # alter table tbl_partition_201307 inherit tbl_partition;

七、約束排除

約束排除(Constraint exclusion)是一種查詢優化技巧,它改進了用上面方法定義的表分區的性能。

確保postgresql.conf 里的配置參數constraint_exclusion 是打開的。沒有這個參數,查詢不會按照需要進行優化。這里我們需要做的是確保該選項在配置文件中沒有被注釋掉。

如果沒有約束排除,查詢會掃描tbl_partition 表中的每一個分區。打開了約束排除之后,規劃器將檢查每個分區的約束然后再試圖證明該分區不需要被掃描,因為它不能包含任何符合WHERE子句條件的數據行。如果規劃器可以證明這個,它就把該分區從查詢規劃里排除出去。

可以使用EXPLAIN 命令顯示一個規劃在constraint_exclusion 關閉和打開情況下的不同:

7.1 約束排除關閉

      david
      
        =
      
      # 
      
        set
      
       constraint_exclusion 
      
        =
      
      
        off
      
      
        ;                                                 


      
      
        SET
      
      
        

david
      
      
        =
      
      # explain 
      
        select
      
      
        count
      
      (
      
        *
      
      ) 
      
        from
      
       tbl_partition 
      
        where
      
       join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ;

                                           QUERY 
      
      
        PLAN
      
      
        --
      
      
        -----------------------------------------------------------------------------------------------
      
      

 Aggregate  (cost
      
        =
      
      
        172.80
      
      ..
      
        172.81
      
       rows
      
        =
      
      
        1
      
       width
      
        =
      
      
        0
      
      
        )

   
      
      
        ->
      
        Append  (cost
      
        =
      
      
        0.00
      
      ..
      
        167.62
      
       rows
      
        =
      
      
        2071
      
       width
      
        =
      
      
        0
      
      
        )

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        0.00
      
       rows
      
        =
      
      
        1
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201211 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201212 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201301 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201302 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201303 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201305 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201304 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201306 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201307 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

(
      
      
        22
      
      
         rows)



david
      
      
        =
      
      # 
    

從上面的查詢計劃中可以看出,PostgreSQL 掃描了所有分區。下面我們再看一下打開約束排除之后的查詢計劃: ?

7.2 約束排除開啟

      david
      
        =
      
      # 
      
        set
      
       constraint_exclusion 
      
        =
      
      
        on
      
      
        ;                                                  


      
      
        SET
      
      
        

david
      
      
        =
      
      # explain 
      
        select
      
      
        count
      
      (
      
        *
      
      ) 
      
        from
      
       tbl_partition 
      
        where
      
       join_date 
      
        >=
      
       DATE 
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ;

                                           QUERY 
      
      
        PLAN
      
      
        --
      
      
        -----------------------------------------------------------------------------------------------
      
      

 Aggregate  (cost
      
        =
      
      
        76.80
      
      ..
      
        76.81
      
       rows
      
        =
      
      
        1
      
       width
      
        =
      
      
        0
      
      
        )

   
      
      
        ->
      
        Append  (cost
      
        =
      
      
        0.00
      
      ..
      
        74.50
      
       rows
      
        =
      
      
        921
      
       width
      
        =
      
      
        0
      
      
        )

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        0.00
      
       rows
      
        =
      
      
        1
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201305 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201304 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201306 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

         
      
      
        ->
      
        Seq Scan 
      
        on
      
       tbl_partition_201307 tbl_partition  (cost
      
        =
      
      
        0.00
      
      ..
      
        18.62
      
       rows
      
        =
      
      
        230
      
       width
      
        =
      
      
        0
      
      
        )

               Filter: (join_date 
      
      
        >=
      
      
        '
      
      
        2013-04-01
      
      
        '
      
      
        ::date)

(
      
      
        12
      
      
         rows)



david
      
      
        =
      
      #
    

可以看到,PostgreSQL 只掃描四月份以后的分區表。

八、可選的分區方式

還可以通過建立規則的方式進行分區。

      
        CREATE
      
      
        RULE
      
       insert_tbl_partition_201211 
      
        AS
      
      
        ON
      
      
        INSERT
      
      
        TO
      
       tbl_partition 
      
        WHERE
      
      
        

    ( join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2012-11-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
      
         )

DO INSTEAD

    
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201211 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );




      
      
        CREATE
      
      
        RULE
      
       insert_tbl_partition_201212 
      
        AS
      
      
        ON
      
      
        INSERT
      
      
        TO
      
       tbl_partition 
      
        WHERE
      
      
        

    ( join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2012-12-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-01-01
      
      
        '
      
      
         )

DO INSTEAD

    
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201212 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );




      
      
        ...

    


      
      
        CREATE
      
      
        RULE
      
       insert_tbl_partition_201306 
      
        AS
      
      
        ON
      
      
        INSERT
      
      
        TO
      
       tbl_partition 
      
        WHERE
      
      
        

    ( join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-06-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-07-01
      
      
        '
      
      
         )

DO INSTEAD

    
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201306 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );

    


      
      
        CREATE
      
      
        RULE
      
       insert_tbl_partition_201307 
      
        AS
      
      
        ON
      
      
        INSERT
      
      
        TO
      
       tbl_partition 
      
        WHERE
      
      
        

    ( join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-07-01
      
      
        '
      
      
        AND
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2013-08-01
      
      
        '
      
      
         )

DO INSTEAD

    
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_201307 
      
        VALUES
      
       (NEW.
      
        *
      
      
        );

    


      
      
        CREATE
      
      
        RULE
      
       insert_tbl_partition_error_join_date 
      
        AS
      
      
        ON
      
      
        INSERT
      
      
        TO
      
       tbl_partition 
      
        WHERE
      
      
        

    ( join_date 
      
      
        >=
      
       DATE 
      
        '
      
      
        2013-08-01
      
      
        '
      
      
        OR
      
       join_date 
      
        <
      
       DATE 
      
        '
      
      
        2012-11-01
      
      
        '
      
      
         )

DO INSTEAD

    
      
      
        INSERT
      
      
        INTO
      
       tbl_partition_error_join_date 
      
        VALUES
      
       (NEW.
      
        *
      
      );
    

九、注意事項

VACUUM?或 ANALYZE tbl_partition?只會對主表起作用,要想分析表,需要分別分析每個分區表。

十、參考資料

PostgreSQL分區表(Table Partitioning)應用


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!??!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 色综色天天综合网 | 成人免费毛片一区二区三区 | 伊人精品影院一本到欧美 | 国产精品www视频免费看 | 天天爱天天干天天操 | 亚洲国产成人精品一区91 | 久久久成人网 | 在线成人毛片 | 一级成人生活片免费看 | 写真福利 第 页 在线视频 | 久久加勒比 | 大片毛片女女女女女女女 | 男女一级毛片免费视频看 | 久久免费视频在线 | 国产精品国产色综合色 | 美女一级毛片免费观看 | 亚洲欧洲日韩国产aa色大片 | 四虎影视永久免费视频观看 | 桃子在线观看 | 奇米影视亚洲春色 | 视频在线观看一区 | 国产精品一区二区三区免费视频 | 欧美亚洲国产日韩综合在线播放 | 国产成人一区二区三区精品久久 | 色综合久久久久综合体桃花网 | 四虎在线视频免费观看 | 亚洲精品14p | 国产精品视频偷伦精品视频 | 亚洲欧美日韩国产vr在线观 | 免费国产视频在线观看 | 国产日韩精品视频一区二区三区 | 色综合天 | 99热这里只有精品一区二 | 亚洲视频国产精品 | 性欧美暴力猛交xxxxx高清 | 再猛点深使劲爽日本免费视频 | 伊人久久久综在合线久久在播 | 久久综合久久自在自线精品自 | 综合久久99久久99播放 | 欧美特黄aaaaaa | 香蕉视频伊人 |