前言

作業中遇到千萬級單表資料查詢優化，博主第一時間想到表磁區，特此記錄問題的解決程序，避免大家采坑，
資料庫表磁區把一個大的物理表分成若干個小的物理表，并使得這些小物理表在邏輯上可以被當成一張表來使用，
PostgreSQL資料庫中對表做磁區，與11g之前的Oracle類似，需要手動創建磁區表、索引等，通過繼承，比如按時間，每月創建一個表磁區，資料記錄到對應磁區中，

1. 創建主表

創建主表結構, 表名稱 three_height_data_value, 其中的時間欄位名: gather_time

CREATE TABLE "public"."three_height_data_value" (
  "data_id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "data_time" timestamp(6) NOT NULL,
  "data_value" float8 NOT NULL
);

2. 創建存盤程序

創建存盤程序，使用動態SQL, 將大表磁區的運維操作實作自動化，

CREATE OR REPLACE FUNCTION auto_insert_into_three_height_data_value()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name 	text ;			-- 父表中用于磁區的時間欄位的名稱[必須首先初始化!!]
    curMM 		varchar(6);		-- 'YYYYMM'字串,用做磁區子表的后綴
    isExist 		boolean;		-- 磁區子表,是否已存在
    startTime 		text;
    endTime		text;
    strSQL  		text;
    
BEGIN
    -- 呼叫前,必須首先初始化(時間欄位名):time_column_name [直接從呼叫引數中獲取!!]
    time_column_name := TG_ARGV[0];
   
    -- 判斷對應磁區表 是否已經存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);
 
    -- 若不存在, 則插入前需 先創建子磁區
    IF ( isExist = false ) THEN  
        -- 創建子磁區表
        startTime := curMM||'01 00:00:00';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
 
        -- 創建索引
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curMM||'_INDEX_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
        EXECUTE strSQL;
       
    END IF;
 
    -- 插入資料到子磁區!
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;

說明:
(1) 代碼中使用了 TG_ARGV[0] 來獲取呼叫時傳入的引數: 用于磁區的時間欄位名.

(2) 代碼中,通過內置引數 TG_RELNAME 獲得了父表的表名稱.

(3) 首先根據插入時間, 判斷對應磁區表是否存在? 若存在, 直接插入對應磁區子表

(4) 若磁區表還不存在, 先創建磁區子表和索引, 然后插入資料到所建的子表中.

以上代碼, 在PostgreSQL v9.4 中除錯通過. 理論上, v8.4以上均支持.

3. 創建觸發器

CREATE TRIGGER insert_three_height_data_value_trigger
  BEFORE INSERT
  ON three_height_data_value
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_into_three_height_data_value('data_time');

4. 踩過的坑

（1）constraint_exclusion屬性設定

確保postgresql.conf中的constraint_exclusion配置項沒有被disable ，這一點非常重要，如果該引數項被disable，則基于磁區表的查詢性能無法得到優化，甚至比不使用磁區表直接使用索引性能更低，

（2）linux如何執行postgresql的sql腳本

方法一：首先通過psql連接到對應的db：

psql -d db1 -U userA

接著輸入密碼，進入資料庫后，輸入：

\i /pathA/xxx.sql

方法二：直接通過psql命令執行SQL檔案

這種方式無需先登錄資料庫，直接用一個命令就可以了

psql -d db1 -U userA -f /pathA/xxx.sql

（3）如何查詢表磁區

–查詢指定磁區表

SELECT nmsp_parent.nspname AS parent_schema , parent.relname AS parent , nmsp_child.nspname AS child , child.relname AS child_schema FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname = 'plc_import_data_value';

查詢所有區表

SELECT nspname , relname ,COUNT(*) AS partition_num FROM pg_class c , pg_namespace n , pg_inherits i WHERE c.oid = i.inhparent AND c.relnamespace = n.oid AND c.relhassubclass AND c.relkind = 'r' GROUP BY 1,2 ORDER BY partition_num DESC;