版權(quán)說(shuō)明:本文檔由用戶(hù)提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡(jiǎn)介
1、技術(shù)創(chuàng)新,變革未來(lái)斗魚(yú) POSTGRESQL分區(qū)表實(shí)踐與思考1分區(qū)表場(chǎng)景與意義2pg各版本分區(qū)表實(shí)現(xiàn)3生產(chǎn)環(huán)境中pg分區(qū)實(shí)現(xiàn)4分區(qū)表實(shí)踐與問(wèn)題01 分區(qū)表場(chǎng)景與意義分區(qū)表的業(yè)務(wù)需求 數(shù)據(jù)庫(kù)業(yè)務(wù)數(shù)據(jù)配置表:數(shù)據(jù)量小,易管理與優(yōu)化狀態(tài)表:數(shù)據(jù)量大,性能問(wèn)題流水表:有明顯的時(shí)間變化,每天數(shù)據(jù)量都很大,定期歸檔統(tǒng)計(jì)表: 加載后數(shù)據(jù)不變,數(shù)據(jù)量很大,需統(tǒng)計(jì)分析性能問(wèn)題,歷史數(shù)據(jù)歸檔問(wèn)題,空間問(wèn)題 分區(qū)表 OLTP需要用到分區(qū)表,OLAP中分區(qū)表是大多數(shù)表的存在狀態(tài)定義:把一個(gè)大的物理表分成若干個(gè)小的物理表,并使得這些小物理表在邏輯上可 以被當(dāng)成一張表來(lái)使用。主表/父表/Master Table主表是創(chuàng)建
2、字表的模板,是一個(gè)正常的普通表,但正常情況下,主表并不存在任何數(shù)據(jù)子表/分區(qū)表/Chlid Table/Partition Table子表繼承并屬于一個(gè)主表,與主表是一對(duì)多的關(guān)系,子表中存儲(chǔ)所有的數(shù)據(jù)分區(qū)表意義性能提升特定場(chǎng)景,查詢(xún)更新轉(zhuǎn)為分區(qū)操作提升性能歷史數(shù)據(jù)歷史數(shù)據(jù)歸檔和清理,通過(guò)drop分區(qū)實(shí)現(xiàn),非常高效存儲(chǔ)拆分單個(gè)表只能在一個(gè)位置,分區(qū)表冷熱分離分區(qū)表的意義分區(qū)表的定義表名字段約束索引主表和分區(qū)表關(guān)系實(shí)現(xiàn)分區(qū)字段和分區(qū)策略范圍分區(qū) 列 表 分 區(qū) 哈希分區(qū)pg各版本分區(qū)表實(shí)現(xiàn)02pg官方分區(qū)表的演進(jìn) Postgresql 官方分區(qū)表功能的演進(jìn)傳統(tǒng)分區(qū)表 postgresql 9.x及
3、之前的版本內(nèi)置分區(qū)表 postgresql 10版本開(kāi)始支持增強(qiáng)分區(qū)表 postgresql 11版本中分區(qū)表功能的增強(qiáng)高性能分區(qū)表postgresql 12版本中,分區(qū)表性能提升主表和分區(qū)表需分別創(chuàng)建,各自定義,數(shù)據(jù)不能自動(dòng)關(guān)聯(lián)主表和分區(qū)表關(guān)系:通過(guò)繼承和觸發(fā)器實(shí)現(xiàn) 實(shí)現(xiàn)方式:定義父表定義子表: 用 inherits 創(chuàng)建分區(qū)表定義子表約束 :約束數(shù)據(jù)對(duì)應(yīng)分區(qū)的規(guī)則創(chuàng)建子表索引 :子表不會(huì)繼承父表的索引創(chuàng)建分區(qū)插入、修改、刪除函數(shù)和觸發(fā)器啟用分區(qū)查詢(xún)參數(shù):設(shè)置 constraint_exclusion 參數(shù)傳統(tǒng)分區(qū)表 pg 9.5 之前版本傳統(tǒng)分區(qū)表 pg 9.5 之前版本CREATE TA
4、BLE pg_9_tab( id serial,uid int4,username varchar,create_time bigint); CREATE INDEX idx_pg_9_tab_ctime ON pg_9_tab USING btree (create_time);CREATE TABLE pg_9_tab_p_hisotry(CHECK ( create_time = 1569859200 and create_time = 1572537600 and create_time = 1575129600 and create_time 1577808000 ) ) INHER
5、ITS(pg_9_tab);CREATE INDEX idx_pg_9_tab_p_hisotry_ctime ON pg_9_tab_p_hisotry USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201910_ctime ON pg_9_tab_p_201910 USING btree (create_time); CREATE INDEX idx_pg_9_tab_p_201911_ctime ON pg_9_tab_p_201911 USING btree (create_time); CREATE INDEX idx_
6、pg_9_tab_p_201912_ctime ON pg_9_tab_p_201912 USING btree (create_time);CREATE OR REPLACE FUNCTION pg_9_tab_insert_trigger() RETURNS triggerLANGUAGE plpgsql AS $function$BEGIN END;$function$;CREATE TRIGGER insert_pg_9_tab_trigger BEFORE INSERT ON pg_9_tab FOR EACH ROW EXECUTE PROCEDURE pg_9_tab_inser
7、t_trigger();傳統(tǒng)分區(qū)表 pg 9.5 之前版本內(nèi)置分區(qū)表(斗魚(yú)線上目前使用的分區(qū)表之一)Postgresql 10提供了內(nèi)置分區(qū)表,不需要預(yù)先在父表上定義觸發(fā)器, 對(duì)父表的DML操作也會(huì)自動(dòng)路由到相應(yīng)分區(qū)。僅支持范圍分區(qū)和列表分區(qū)。Pg 10內(nèi)置分區(qū)表創(chuàng)建步驟:創(chuàng)建主表:指定分區(qū)鍵和分區(qū)策略創(chuàng)建分區(qū)表:指定分區(qū)表的主表和分區(qū)鍵的取值范圍,范圍不能有重疊在分區(qū)表上創(chuàng)建索引:分區(qū)鍵一般必須創(chuàng)建索引,其他列根據(jù)實(shí)際場(chǎng)景選擇是 否場(chǎng)景索引內(nèi)置分區(qū)表 pg 10 支持版本CREATE TABLE pg_10_tab_p_hisotry PARTITION OF pg_10_tab FOR V
8、AlUES FROM (1546272000) TO (1569859200); CREATE TABLE pg_10_tab_p_201910 PARTITION OF pg_10_tab FOR VAlUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_10_tab_p_201911 PARTITION OF pg_10_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_10_tab_p_201912 PARTITION OF pg_10_tab FO
9、R VAlUES FROM (1575129600) TO (1577808000);CREATE INDEX idx_pg_10_tab_p_hisotry_ctime ON pg_10_tab_p_hisotry USING btree (create_time); CREATE INDEX idx_pg_10_tab_p_201910_ctime ON pg_10_tab_p_201910 USING btree (create_time); CREATE INDEX idx_pg_10_tab_p_201911_ctime ON pg_10_tab_p_201911 USING btr
10、ee (create_time); CREATE INDEX idx_pg_10_tab_p_201912_ctime ON pg_10_tab_p_201912 USING btree (create_time);INSERT INTO pg_10_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_10_tab; SELECT count(*
11、) FROM ONLY pg_10_tab;dt+ pg_10_tab*內(nèi)置分區(qū)表 pg 10 支持版本CREATE TABLE pg_10_tab( id serial, uid int4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_10_tab_ctime ON pg_10_tab USING btree (create_time); #不能執(zhí)行內(nèi)置分區(qū)表 pg 10 支持版本分區(qū)表可以繼承主表的字段、索引信息支持hash分區(qū),rang/list 支
12、持默認(rèn)分區(qū),多級(jí)分區(qū)分區(qū)索引增強(qiáng): 分區(qū)主表可以創(chuàng)建主鍵,可以創(chuàng)建外鍵, DML增強(qiáng): 可以分區(qū)間移動(dòng)行,可以路由到外部分區(qū),性能方面:數(shù)據(jù)搜索訪問(wèn)優(yōu)化,提升分區(qū)檢索性能Pg 10內(nèi)置分區(qū)表創(chuàng)建步驟:創(chuàng)建主表:指定分區(qū)鍵和分區(qū)策略,創(chuàng)建相關(guān)索引創(chuàng)建分區(qū)表:指定分區(qū)表的主表和分區(qū)鍵的取值范圍,范圍不能有重疊增強(qiáng)分區(qū)表 pg 11 支持版本 功能增強(qiáng):CREATE TABLE pg_11_tab_p_hisotry PARTITION OF pg_11_tab DEFAULT;CREATE TABLE pg_11_tab_p_201910 PARTITION OF pg_11_tab FOR VA
13、lUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_11_tab_p_201911 PARTITION OF pg_11_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_11_tab_p_201912 PARTITION OF pg_11_tab FOR VAlUES FROM (1575129600) TO (1577808000);CREATE INDEX idx_pg_11_tab_p_hisotry_ctime ON pg_11_tab_p_h
14、isotry USING btree (create_time); #不用執(zhí)行 CREATE INDEX idx_pg_11_tab_p_201910_ctime ON pg_11_tab_p_201910 USING btree (create_time);CREATE INDEX idx_pg_11_tab_p_201911_ctime ON pg_11_tab_p_201911 USING btree (create_time);CREATE INDEX idx_pg_11_tab_p_201912_ctime ON pg_11_tab_p_201912 USING btree (cre
15、ate_time); #不用執(zhí)行INSERT INTO pg_11_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_11_tab; SELECT count(*) FROM ONLY pg_11_tab;dt+ pg_11_tab*增強(qiáng)分區(qū)表 pg 11 支持版本CREATE TABLE pg_11_tab( id serial, uid i
16、nt4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_11_tab_ctime ON pg_11_tab USING btree (create_time); #可以執(zhí)行增強(qiáng)分區(qū)表 pg 11 支持版本高性能分區(qū)表 pg 12 支持版本 高性能分區(qū)表功能方面:增加更多查詢(xún)函數(shù),便于快速查看分區(qū)表狀態(tài)性能方面:分區(qū)表的性能得到大幅提升,尤其是在分區(qū)表數(shù)量非 常多時(shí),DML性能提升更加明顯CREATE TABLE pg_12_tab_p_hisotry PAR
17、TITION OF pg_12_tab DEFAULT;CREATE TABLE pg_12_tab_p_201910 PARTITION OF pg_12_tab FOR VAlUES FROM (1569859200) TO (1572537600); CREATE TABLE pg_12_tab_p_201911 PARTITION OF pg_12_tab FOR VAlUES FROM (1572537600) TO (1575129600); CREATE TABLE pg_12_tab_p_201912 PARTITION OF pg_12_tab FOR VAlUES FROM
18、 (1575129600) TO (1577808000);INSERT INTO pg_12_tab(uid,username,create_time)SELECT round(1000*random(),chr(int4(random()*26)+65),generate_series( 1568476800, 1576339200, 360);SELECT count(*) FROM pg_12_tab; SELECT count(*) FROM ONLY pg_12_tab;dt+ pg_12_tab*高性能分區(qū)表 pg 12 支持版本CREATE TABLE pg_12_tab( i
19、d serial, uid int4, username varchar, create_time bigint ) PARTITION BY RANGE(create_time); CREATE INDEX idx_pg_12_tab_ctime ON pg_11_tab USING btree (create_time); #可以執(zhí)行高性能分區(qū)表 pg 12 支持版本pg分區(qū)表各版本主要功能對(duì)比對(duì)比項(xiàng)Pg 9.x及之前Pg 10.xPg 11.xPg 12.x分區(qū)特點(diǎn)傳統(tǒng)分區(qū)內(nèi)置分區(qū)加強(qiáng)分區(qū)高性能分區(qū)數(shù)據(jù)路由自建路由觸發(fā)器內(nèi)置分區(qū)內(nèi)置分區(qū)內(nèi)置分區(qū)分區(qū)表索引創(chuàng)建子表逐個(gè)單建子表逐個(gè)單建子表繼
20、承父表子表繼承父表父表全局主鍵不支持不支持支持支持分區(qū)策略Range,listRange,listRange,list,hashRange,list,hash默認(rèn)分區(qū)不支持不支持支持支持Update跨分區(qū)不支持不支持支持支持分區(qū)性能差較差范圍策略?xún)?yōu)化大量dml優(yōu)化維護(hù)難易度難較難容易容易生產(chǎn)環(huán)境中分區(qū)實(shí)現(xiàn)03生產(chǎn)環(huán)境分區(qū)表管理方式 分區(qū)表的管理方式使用 pg_pathman 管理 postgresql 分區(qū)表greenplum 分區(qū)表的實(shí)現(xiàn)與管理原理:PostgreSQL傳統(tǒng)的分區(qū)方法,使用約束來(lái)區(qū)分不同分區(qū)存放的數(shù)據(jù)(配置constraint_exclusion=partition),執(zhí)行s
21、elect/delete/update時(shí)執(zhí)行計(jì)劃根據(jù)約 束和查詢(xún)條件排除不需要查詢(xún)的分區(qū)表。調(diào)用COPY或插入數(shù)據(jù)時(shí)使用觸發(fā)器或規(guī) 則,將數(shù)據(jù)插入對(duì)應(yīng)的分區(qū)表。傳統(tǒng)的做法,無(wú)論是查詢(xún)還是插入,對(duì)性能的影響 都較大。pg_pathman與傳統(tǒng)的繼承分區(qū)表做法有一個(gè)不同的地方,分區(qū)的定義存放在一張 元數(shù)據(jù)表中,表的信息會(huì)cache在內(nèi)存中,同時(shí)使用HOOK來(lái)實(shí)現(xiàn)RELATION的替 換,所以效率非常高。用 pg_pathman 管理pg分區(qū)表 優(yōu)勢(shì):Pg第三方插件,分區(qū)表性能更高、便于管理和維護(hù)pg_pathman 使用函數(shù)來(lái)維護(hù)分區(qū)表,并且創(chuàng)建了一些視圖,可以查看分區(qū)表的 狀態(tài)。分區(qū)表的定義則存
22、在一張表中,定義數(shù)據(jù)緩存在內(nèi)存中。四個(gè)管理表:pathman_config - main config storagepathman_config_params - optional parameterspathman_concurrent_part_tasks - currently running partitioning workerspathman_partition_list - list of all existing partitions用 pg_pathman 管理pg分區(qū)表 實(shí)現(xiàn):創(chuàng)建主表插入測(cè)試數(shù)據(jù)創(chuàng)建分區(qū)調(diào)用 create_range_partitions 函數(shù)創(chuàng)建分區(qū),指定起始值、間隔、分區(qū)個(gè)數(shù)遷移數(shù)據(jù)到分區(qū)表中調(diào)用 partition_table_concurrently 函數(shù)非阻塞遷移數(shù)據(jù)stop_concurrent_part_task停止遷移任務(wù)pathman_concurrent_
溫馨提示
- 1. 本站所有資源如無(wú)特殊說(shuō)明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶(hù)所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽,若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間,僅對(duì)用戶(hù)上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對(duì)用戶(hù)上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請(qǐng)與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶(hù)因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。
最新文檔
- 2025年度公共場(chǎng)所窗簾清洗與保養(yǎng)服務(wù)合同3篇
- 2025年度離婚后子女撫養(yǎng)權(quán)協(xié)商服務(wù)合同3篇
- 2025年度稅收籌劃與稅務(wù)籌劃合規(guī)性審查合同2篇
- 2025年度恐怖劇本定制與特效設(shè)計(jì)合同3篇
- 2024版輕鋼房屋建造協(xié)議模板協(xié)議
- 二零二四商鋪?zhàn)赓U合作協(xié)議:教育培訓(xùn)機(jī)構(gòu)商鋪?zhàn)赓U合同3篇
- 2025年度餐飲品牌連鎖拓展合同范本3篇
- 二零二四年家居裝飾團(tuán)購(gòu)合同3篇
- 2025年度材料墊資供應(yīng)鏈金融服務(wù)合同3篇
- 2024年鐵礦石采購(gòu)中介服務(wù)合同樣本
- 使用錯(cuò)誤評(píng)估報(bào)告(可用性工程)模版
- 公司章程(二個(gè)股東模板)
- GB/T 19889.7-2005聲學(xué)建筑和建筑構(gòu)件隔聲測(cè)量第7部分:樓板撞擊聲隔聲的現(xiàn)場(chǎng)測(cè)量
- 世界奧林匹克數(shù)學(xué)競(jìng)賽6年級(jí)試題
- 藥用植物學(xué)-課件
- 文化差異與跨文化交際課件(完整版)
- 國(guó)貨彩瞳美妝化消費(fèi)趨勢(shì)洞察報(bào)告
- 云南省就業(yè)創(chuàng)業(yè)失業(yè)登記申請(qǐng)表
- UL_標(biāo)準(zhǔn)(1026)家用電器中文版本
- 國(guó)網(wǎng)三個(gè)項(xiàng)目部標(biāo)準(zhǔn)化手冊(cè)(課堂PPT)
- 快速了解陌生行業(yè)的方法論及示例PPT課件
評(píng)論
0/150
提交評(píng)論