問題的提出

在項目中，有些表的記錄增長非常快，記錄數(shù)過大時會使得查詢變得困難，導致整個數(shù)據(jù)庫處理性能下降。此時，我們會考慮按一定的規(guī)則進行分表存儲。

常用的分表方式是按時間周期，如每月一張，每天一張等。當每月或每天首條記錄到達時，根據(jù)表結(jié)構(gòu)創(chuàng)建該周期為后綴的表進行存儲。

相關(guān)考慮

這其中主要考慮兩個問題：

（1）如何復制表

采用分表機制，通常會建立一個模板表。所謂模板表，是只定義結(jié)構(gòu)不存儲數(shù)據(jù)的，也可稱之為類表，而分表，通常會以增加后綴的方式命名，如 log_201901，分表實際存儲數(shù)據(jù)，可稱之為實例表。

表存在關(guān)聯(lián)、鍵、索引、約束等，這讓表的復制聽起來比較繁瑣，即便通過元數(shù)據(jù)得到這些信息，還需要自己考慮如索引的命名沖突等問題。而 PostgreSQL 為我們提供了極其便捷的方式。

CREATE TABLE [IF NOT EXISTS] 實例表 (LIKE 模板表 [INCLUDING ALL]);

其中 [ ] 內(nèi)表示可選項，INCLUDING 除了 ALL 還要其它細分的選項，具體可參考幫助文檔。

（2）數(shù)據(jù)存儲的邏輯過程

首先得知道表存不存在，不存在則要創(chuàng)建，然后執(zhí)行數(shù)據(jù)操作語句。

由于表名是動態(tài)的，在應用系統(tǒng)中可以先取得表名形成 SQL 語句再執(zhí)行，在數(shù)據(jù)庫存儲過程中則可以使用 EXECUTE 執(zhí)行動態(tài)SQL語句。

分表實例

下邊，本文以日志記錄表為例來完整地實踐分表處理過程。

功能描述：日志數(shù)量大，當前日志查詢頻繁，歷史日志需要全部保存。要求每天一個分表，日志主鍵要求全局保持唯一性（即多個分表間不重復），日志到達自動根據(jù)當前的時間進行分表存儲。

首先創(chuàng)建日志模板表，命名為 log_template，并為其建立相關(guān)索引，主鍵序列。

-- 創(chuàng)建模板表，log_id 主鍵，log_at 日志時間， log_content 日志內(nèi)容 CREATE TABLE log_template (log_id bigint PRIMARY KEY, log_at timestamp, log_content varchar(1000)); -- 對日志時間索引 CREATE INDEX idx_log_at on log_template (log_at); -- 用于主鍵的序列（各分表使用同一序列） CREATE SEQUENCE seq_log_id;

我們通過一個過程來完成日志的自動分表存儲。

CREATE OR REPLACE FUNCTION func_log(v_conent varchar) RETURNS bool LANGUAGE 'plpgsql' AS $$ DECLARElv_log_at timestamp := current_timestamp;lv_suffix_tname varchar; -- 帶后綴的分表名lv_dsql text; -- 動態(tài)SQL BEGIN-- 根據(jù)時間得到應使用的分表名稱lv_suffix_tname := 'log_' || to_char(lv_log_at, 'YYYYMMDD');-- 判斷是否存在，不存在時復制模板創(chuàng)建分表lv_dsql := 'CREATE TABLE IF NOT EXISTS ' || lv_suffix_tname || ' (LIKE log_template INCLUDING ALL)';EXECUTE lv_dsql; -- 將數(shù)據(jù)保存至分表lv_dsql := 'INSERT INTO ' || lv_suffix_tname || '(log_id, log_at, log_content) VALUES($1, $2, $3)';EXECUTE lv_dsql USING nextval('seq_log_id'), lv_log_at, v_conent;RETURN true; END $$;

執(zhí)行以下語句來看看預期的結(jié)果。

SELECT func_log('hello, the first log!'); SELECT func_log('toady is a nice day!'); SELECT func_log('每天都有新的開始，不再擔心爆表!');

結(jié)束語

分表能夠避免單表記錄過于龐大，提高查詢性能。但同時，分表也會給部分查詢或數(shù)據(jù)處理帶有復雜性，因此是否分表應該根據(jù)業(yè)務(wù)需要來，同時應盡早規(guī)劃，后期更改相對繁瑣。

在 MySQL 中也有類似的 CREATE TABLE LIKE 語法，我想都是應運而生，簡單就是美。

轉(zhuǎn)載于:https://www.cnblogs.com/timeddd/p/10874007.html

總結(jié)

以上是生活随笔為你收集整理的PostgreSQL 务实应用（三/5）分表复制的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。