工作中遇到千萬級單表資料查詢優化,博主第一時間想到表分割區,特此記錄問題的解決過程,避免大家採坑。
資料庫表分割區把一個大的物理表分成若干個小的物理表,並使得這些小物理表在邏輯上可以被當成一張表來使用。
PostgreSQL資料庫中對錶做分割區,與11g之前的Oracle類似,需要手動建立分割區表、索引等。通過繼承,比如按時間,每月建立一個表分割區,資料記錄到對應分割區中。
建立主表結構, 表名稱 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
);
建立儲存過程,使用動態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以上均支援.
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');
確保postgresql.conf中的constraint_exclusion設定項沒有被disable 。這一點非常重要,如果該引數項被disable,則基於分割區表的查詢效能無法得到優化,甚至比不使用分割區表直接使用索引效能更低。
psql -d db1 -U userA
接著輸入密碼,進入資料庫後,輸入:
\i /pathA/xxx.sql
這種方式無需先登入資料庫,直接用一個命令就可以了
psql -d db1 -U userA -f /pathA/xxx.sql
–查詢指定分割區表
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;