PostgreSQL 千萬級以上的數據模糊查詢、自動補全、分頁瀏覽技術-企業應用專場（30頁）.pdf

《PostgreSQL 千萬級以上的數據模糊查詢、自動補全、分頁瀏覽技術-企業應用專場（30頁）.pdf》由會員分享，可在線閱讀，更多相關《PostgreSQL 千萬級以上的數據模糊查詢、自動補全、分頁瀏覽技術-企業應用專場（30頁）.pdf（30頁珍藏版）》請在三個皮匠報告上搜索。

1、PostgreSQL 千萬級以上的數據模糊查詢、自動補全、分頁瀏覽技術李曉光（黑哥）模糊查詢、分頁是開發中是使用比較高頻的技術。本文介紹在大數據量時如何高效的使用模糊查詢和數據分頁瀏覽，同時根據后臺設計降低用戶界面的使用復雜程度。1.將全部數據讀取到內存,然后再內存中分頁.稍有經驗的人都不會使用；數據分頁瀏覽技術是開發中基本都會用到的技術,常用的分頁方法有以下4種：2.使用limit offset效果不是很好,翻頁越到后面速度越慢；3.使用“select*from t where id(傳入上一頁最后一個id)order by id limit 20”.效率比較好,適合手機屏幕向下滑動分頁取數

2、據的方式.因為只能逐頁瀏覽而不能跳頁,因此不適合應用系統；4.去除歷史數據,使用較少的較新的數據來實現,采用比較多的方式.基于PostgreSQL數據庫自主研發的分頁插件介紹使用簡單,只要創建普通的常規表,無需對表做特別的優化(無需分庫分表),1千萬級數據量時全表分頁(不帶條件)響應時間5秒,帶條件時500ms,關鍵字全表檢索時=016*$6:每頁顯示的記錄數,值范圍10-100017*$7:排序,升序或降序18*$8:最大頁數,設置為0表示不限制,讀取所有記錄,否則只返回指定的頁數19*返回值:只有一行三列的記錄集20*1列:指定頁的id范圍數組21*2列:符合條件的頁數22*3列:符合條件

3、記錄數,是準確的記錄數,不是評估記錄數23*/24 create function paging_calculate_v2(25 in text,in text,in text,in anyelement,in integer,in integer,in boolean,in integer default 0,26 out page_ids bigint,out page_count bigint,out row_count bigint27)returns setof record28 as pg_kmcb,paging_calculate_v229 language C;select*f

4、rom enterprises where objected=any(page_ids)order by objectid使用方法1.計算指定頁的所有ID、頁數、符合條件的記錄數2.輸出指定頁的數據創建數據庫建議 1#數據庫數據根目錄.這個目錄如果能直接mount比較好,和操作系統分開,這樣比較安全.操作系統出問題后重新mount這個目錄就可以快速恢復.2#Windows雙擊熱備也是這個原理,數據庫數據根目錄直接放到活動硬盤上 3/data 4#postgresql系統目錄,也是initdb時的目錄.PostgreSQL的系統配置如postgresql.conf,pg_hba.conf等都在這

5、個目錄中 5/data/pgdata 6#WAL歸檔目錄 7/data/archivedir 8#數據庫test的表空間目錄 9/data/test10#數據庫test的索引表空間目錄.索引表空間目錄一般建議設置,因為當使用SSD和機械硬盤混合時,可以直接修改索引表空間的目錄指至SSD11/data/idxtest 12#為每個數據庫單獨創建用戶而不是使用postgres用戶,同時在每個數據庫上回收public權限13 revoke all on database test from public;pg_hba.conf配置，配置完成后無需重啟數據庫，reload即可。1#本機允許所有 2 h

6、ost all all 127.0.0.1/32 md5 3#其它數據庫只允許指定的用戶登錄指定的數據庫,再把密碼設置復雜一點就可以了,怎么攻都沒用 4 host dbname dbuser 0.0.0.0/0 md5創建數據表（1）1 drop index if exists idx_enterprises_keys;2 drop table if exists enterprises;3 create table enterprises(4 objectid bigserial not null,-唯一編號 5 name text not null,-企業名稱 6 bank text,-開

7、戶銀行 7 registered text,-納稅人登記號 8 number text,-賬號 9 address text,-企業地址10 post text,-企業郵編11 legal text,-法人代表12 contact text,-聯系人13 tel text,-聯系電話14 fax text,-傳真15 mail text,-郵箱16 other jsonb,-企業備注或其它(至少包含other-備注)17 constraint pk_enterprisess_objectid primary key(objectid)with(fillfactor=80)using index

8、 tablespace idxtest18)with(fillfactor=80,19 autovacuum_enabled=true,toast.autovacuum_enabled=true,20 autovacuum_vacuum_threshold=500,autovacuum_analyze_threshold=1000,21 toast.autovacuum_vacuum_threshold=500);22-為了便于查看驗證,id從1開始23 select setval(pg_get_serial_sequence(enterprises,objectid),1,false);創建

9、查詢條件表（2）1 create schema cond;2 create table cond.enterprises(3 objectid bigint not null,-唯一編號,外鍵enterprises-objectid 4 divid bigint not null,-行政區唯一代碼,外鍵divisions-objectid,divisions由程序員維護，因此不需要外鍵 5 keys tsvector not null,-關鍵字(name,legal,contact,tel)6 constraint pk_cond_enterprises_objectid primary ke

10、y(objectid)with(fillfactor=80)using index tablespace idxtest,7 constraint fk_cond_enterprises_objectid foreign key(objectid)references enterprises(objectid)on delete cascade 8)with(fillfactor=80,9 autovacuum_enabled=true,toast.autovacuum_enabled=true,10 autovacuum_vacuum_threshold=500,autovacuum_ana

11、lyze_threshold=1000,11 toast.autovacuum_vacuum_threshold=500);12-創建索引13-之所以沒用rum是因為rum寫太實在是太慢了,相對來說rum在大多數應用查詢速度不是那么明顯14 create index idx_cond_enterprises_keys on cond.enterprises using gin(keys tsvector_ops)tablespace idxtest;分為數據表和查詢條件表，keys關鍵字包含的“企業名稱法定代表人聯系人電話號碼”分詞后的內容，分開的目的是更新主表非企業名稱法定代表人聯系人電話號

12、碼字段時,無需更新關鍵字表,同時便于管理.當然不分也可以.創建表和索引時要設置fillfactor,同時注意指明索引的索引表空間。創建表和索引時的fillfactor參數fillfactor參數是PostgreSQL最重要的參數之一，表fillfactor默認為100%，B-樹索引fillfactor默認為90%。我們都知道PostgreSQL最小存儲單位為頁，每頁默認大小為8K（可以在編譯時修改）。如果不在創建表時根據需求（主要是update和delete頻繁程度）設置fillfactor參數，當你使用一段時間后修改這個參數對歷史數據是無效的。如果需要對歷史數據也生效的話，你不得不做VACU

13、UMFULL，FULL會獲取表上的獨占鎖，阻止所有操作(包括SELECT)，FULL實際上創建了一個表的副本(也就是重新復制了一個表)，如果歷史數據比較多的話復制表非常慢（可能是幾小時或幾天）。同時同時fillfactorfillfactor結合結合autovacuumautovacuum可以最大程度可以最大程度上上避免表膨脹、提升查詢效率避免表膨脹、提升查詢效率。表中包含char或varchar類型字段（長度較?。r，在創建表完成后注意修改char或varchar的存儲方式（默認為EXTENDED，修改為plain）否則事后修改不得不VACUUM FULL。測試數據組成測試數據全部由數字組成

14、,全由數字組成的測試數據重覆概率比較高,相同的條件值返回的數據最多,因為這是最惡劣的情況,在此情況測試的結果到生產環境會好很多.唯一編號唯一編號:自動增長行政區行政區唯一唯一代碼代碼:隨機在1-17內生成,1-17是行政區表北京市及下級轄區的唯一編號.企業名稱企業名稱:8-32位隨機的數字組成開戶銀行開戶銀行:8-32位隨機位的數字組成納稅人納稅人登記登記號號:12-13位隨機的數字組成賬號賬號:12位數字組成地址地址:8-32位隨機的數字組成郵編郵編:6位的數字組成法定法定代表代表人人:2-5位隨機的數字組成聯系人聯系人:2-5位隨機的數字組成聯系電話聯系電話:11位的數字組成關鍵字關鍵字:

15、由由“企業企業名稱名稱 法定代表人法定代表人 聯系人聯系人 聯系聯系電話電話”切分組成，切分組成，每每2 2個字做為一個個字做為一個詞處理。當然你也詞處理。當然你也可以可以使用分詞技術生成關鍵字使用分詞技術生成關鍵字(例如結巴例如結巴).).測試時使用數字查詢返回的結果最多測試時查詢使用非數字沒有返回的結果生產環境各種情況都有，返回的結果相對均衡好差插入測試數據timing ondo$declarebeginfor i in 1.1000 loopinsert into enterprises(name,bank,registered,number,address,post,legal,con

16、tact,tel,fax)selectgenerate_rand_string(8,32,1)as name,generate_rand_string(8,32,1)as bank,generate_rand_string(12,13,1)as registered,generate_rand_string(12,12,1)as number,generate_rand_string(8,32,1)as address,generate_rand_string(6,6,1)as post,generate_rand_string(2,5,1)as legal,generate_rand_str

17、ing(2,5,1)as contact,format(%s%s,1,generate_rand_string(10,10,1)as tel,format(087%s-%s,generate_rand_string(1,1,1),generate_rand_string(7,7,1)as faxfrom generate_series(1,1000);raise notice%,i;end loop;end;$;開10個進程,每個進程插入100w數據,1千萬數據全部插入完成約1分鐘。關鍵字切分示例 with cte as(select vals from regexp_matches(fill

18、factor參數是參數是PostgreSQL最重要的參數，表最重要的參數，表fillfactor默認為默認為100%，B樹索引樹索引fillfactor默認為默認為90%。電話：。電話：13579246801 0871-68337822,(+86|86)?(10-910)|(0-93,4)|(（0-93,4）)|(0-94,5-)?(0-97,8)|(-+?0-9*.?0-9+(eE-+?0-9+)?)|(a-zA-Z0-9_3,)|(a-zA-Z1,u3007u3400-u4db5u4e00-u9fcbuf900-ufa2d1,),g)as vals),repeat1 as(select u

19、nnest(case when vals1(+86|86)?(10-910)$then-判斷手機號 split_string(vals3,2)-只切11位手機號，忽略區號 when vals4(0-93,4)|(（0-93,4）)|(0-94,5-)?(0-97,8)$then-判斷固定電話或傳真號碼 split_string(vals9,2)-只切7-8位電話號碼，忽略區號 when vals10(-+?0-9*.?0-9+(eE-+?0-9+)?)$then-判斷帶符號數字整數|浮點數|指數 split_string(vals10,2)-只切整數，小數不切 when vals12(a-zA

20、-Z0-9_+)$then-判斷英文數字下劃線 split_string(lower(vals12),2)when vals13(a-zA-Z1,u3007u3400-u4db5u4e00-u9fcbuf900-ufa2d+)$then-判斷漢字 split_string(lower(vals13),2)end)as val from cte)select array_agg(val)from(select val from repeat1 where val is not null group by val order by val)as tmp;正則表達式切分后的關鍵字：00,01,10,

21、13,22,24,33,35,37,46,57,68,78,79,80,82,83,90,92,ac,b樹,ct,es,fa,fi,gr,il,lf,ll,l最,or,os,po,ql,re,r參,r默,sq,st,tg,to,參數,的參,默認,認為,數是,樹索,索引,要的,重要,最重插入關鍵字數據關鍵字用正則提取并切分后，用一個進程寫入查詢條件表，單進程的目的是評估gin和rum索引的寫入速度，所有數據隨機分布在北京市或北京市下轄的地區。因為索引和正則提取的原因，在本案例一個進程提取并保存1千萬數據的關鍵字用時為Time:3300734.480 ms(55:00.734)，約為3000條/每

22、秒。createindexidx_cond_enterprises_keysoncond.enterprisesusinggin(keystsvector_ops)tablespaceidxtest;這里特別指出如果用rum索引寫入比gin索引速度更慢。關鍵字自動補全關鍵字自動補全根據用戶輸入的關鍵字顯示最新的10條數據.實際上就是執行下面的sqlselect name from enterprises where(name like%任意內容%)or(legal like%任意內容%)or(contact like%任意內容%)or(tel like%任意內容%)order by id de

23、sc limit 10看到這個大家第一反應肯定覺得很簡單吧但是系統設計要求在1kw數據量時關鍵字自動補全響應時間要求100ms,但是limit 10某些關鍵字會對執行計劃有比較大的影響,上面的sql很難達到設計要求的指標。關鍵字自動補全（使用Limit）輸入的值存在且數量較多,查詢較快explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(139)order by objectid desc limit 10;輸入的值存在返回的數量較少,查詢較慢

24、explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(YNHX)order by objectid desc limit 10;輸入的值不存在,查詢較慢explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(中國)order by objectid desc limit 10;關鍵字自動補全（不使

25、用Limit）輸入的值存在且返回的數量較多,查詢較慢explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(139);輸入的值存在返回的數量較少,查詢較快explain(analyze,verbose,costs,buffers,timing)select objectid from cond.enterprises where keystoTsquery(YNHX);輸入的值不存在,查詢較快explain(analyze,verbose,cos

26、ts,buffers,timing)select objectid from cond.enterprises where keystoTsquery(中國);關鍵字自動補全（兩種方案對比）使用使用limit不使用不使用limitkeys列中包含用戶輸入的數據且較多時,響應速度非?？?。keys列中包含用戶輸入的數據且較多時,響應速度非常慢。keys列中不包含用戶輸入的數據或數據較少時，響應非常慢。keys列中不包含用戶輸入的數據或數據較少時，響應非?？?。實測結果包含limit和不包含limit它們完全是相互矛盾的，執行計劃也是完全不同的。用戶輸入的關鍵字是不可控的我們可能遇到過同一SQL因關鍵

27、字不同忽快忽慢的問題,可能的原因一是輸入的關鍵字可選擇性太差（數量比較多），還有就是limit的問題，因此開發人員在設計時應充分考慮各種可能性，避免發生此類問題。關鍵字自動補全優化方案一方案一在程序中執行自動補全功能前設置稍微大一點statement_timeout時間,例如設置setstatement_timeoutto100（單位為毫秒）,當超過這個時間時就認為keys列中不包含用戶輸入的數據.statement_timeout超時后拋出一個SQLstate:57014的異常,忽略這個異常.statement_timeout需要在程序中實現,執行完自動補全功能后需要確?；謴驮瓉碓O置stat

28、ement_timeout時間.特點：開發簡單,但有很大的概率不會返回任何數據，換句說也就是查詢成功率非常低。數據量和成功率成反比。關鍵字自動補全優化方案二方案二在程序中使用多線程同時執行如下二個sql,誰先完成用誰的數據,同時取消執行另一個sql.select objectid from cond.enterprises where keys(13:tsquery)order by objectid desc limit 10;select objectid from cond.enterprises where keys(13:tsquery)order by objectid desc;

29、它們之間的區別是否包含limit,其它一樣.缺點：在響應速度和準確性方面均可滿足需求,但是程序復雜度呈幾何陪數提升。關鍵字自動補全優化方案二藍色背景部是由應用程序控制(無需控制或不可控),從白色背景開始由程序員處理.對象池大小和處理進程數量可能不一至,對象池中的對象全部使用時要注意處理.由于自定義對象池中的連接是長連接,在設置數據庫連接參數字符串時不要設置連接池,同時為防止PostgreSQL被OOM killer(Out Of Memory killer),有效利用PostgreSQL服務器的內存,自定義對象池中的連接每使用一定的次數時(例如1000次)關閉連接并重新打開.在前面的視頻中可以

30、看到，同一關鍵字第一次搜索時需要20秒左右，但第二次搜索不到200毫秒，這種情況說明了二個問題：1.數據沒有加載至shared_buffers中，第一次使用時需要從磁盤中讀取這個數據。解決方法是數據庫啟動時預熱數據，同時索引表空間設置至SSD可加快預熱速度；2.服務器物理內存太小shared_buffers中的一部份數據一段時間沒有使用后被丟棄了，再次使用需要從磁盤讀取。解決方法是加大服務器物理內存并合理配置shared_buffers、temp_buffers,work_mem參數可以緩解此問題。關于PostgreSQL開發中其它注意事項在決定使用什么技術之前結合需求測試很重要;如果單表數據沒達到一定的規模建議不要分庫分區，不正確的使用分區表帶來的不是性能提升而是下降，千萬級以下的數據量不需要考慮分區；數據庫連接數不是越多越好，是和您的服務器硬件密切相關。Web程序一般連接數采用cpu內核數量的2倍或4倍即可(連接數最好為cpu內核數的倍數)，開幾百上千的連接性能反而下降；數據庫和sql優化請結合需求和具體的執行計劃優化。