PostgreSQLpg_qualstats解決索引缺失的方法

這篇文章主要講解了“PostgreSQL  pg_qualstats 解決索引缺失的方法”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“PostgreSQL  pg_qualstats 解決索引缺失的方法”吧！

公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)公司是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)公司推出崇川免費(fèi)做網(wǎng)站回饋大家。

商業(yè)數(shù)據(jù)庫中，很多新版本都可以自動(dòng)創(chuàng)建索引，給出索引創(chuàng)建的建議，并且以此作為賣點(diǎn)，ORACLE ，SQL SERVER 均有類似的功能，實(shí)際上通過查詢語句，與全表掃描的語句，與謂詞的比對(duì)，做出這樣的系統(tǒng)其實(shí)不不是一件很難的事情。

我們下面采用PG11 的版本來進(jìn)行相關(guān)的工作,(安裝中遇到很多問題，最終還算解決了)

1 需要安裝 PGDG的repo 環(huán)境，這樣能節(jié)省你很多的安裝中的麻煩以及依賴包問題。具體請(qǐng)移步PGDG中查看對(duì)應(yīng)你版本的安裝信息，并且安裝 pg-devel環(huán)境

2 可以通過下載rpm包的方式安裝

https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pg_qualstats11-1.0.9-1.rhel7.x86_64.rpm

也可以下載源碼包，進(jìn)行編譯安裝，這里就省略了安裝的過程。

最終的效果應(yīng)該在配置文件中，添加pg_stat_statements 和 pg_qualstats 以及創(chuàng)建 extension   pg_qualstats  pg_stat_statements 這2個(gè)插件。

首先我們要知道 pg_qualstats 到底能做什么，pg_qualstats是一個(gè)PostgreSQL擴(kuò)展，用于保存“WHERE”語句和“JOIN”子句中謂詞的統(tǒng)計(jì)信息。

看上圖可以看到相關(guān) pg_qualstats 的變量

另外需要將pg_qualstats.track_constants 關(guān)掉，否則會(huì)收集很多類似的查詢占用相關(guān)的存儲(chǔ)位置。

我們可以寫一個(gè)存儲(chǔ)過程，批量運(yùn)行一些查詢語句，在執(zhí)行完畢后，我們可以通過 pg_qualstats_indexes來查看當(dāng)前查詢中的謂詞，并且這些謂詞是沒有索引的，以及查詢的次數(shù)。

同時(shí)根據(jù) pg_qualstats_indexes_ddl 表可以看到 pg_qualstats 推薦你需要建立的索引，（因?yàn)镻G支持的索引多，所以提供了一種索引需求的多種建立方案）

故事到這里看似完了，其實(shí)不然。我們先的說說這個(gè)插件是從何而來，去往何處，在哪里打間，在哪里住店.....

實(shí)際上是有一個(gè)程序的組建，powa ，通過這個(gè)組建本身是可以動(dòng)態(tài)，WEB化查詢系統(tǒng)中缺失的索引，并給出相關(guān)信息的。這里我們僅僅是借用了這個(gè)軟件的一部分，也可以說叫 client。我們使用的這個(gè)插件僅僅是負(fù)責(zé)收集信息使用的。但其實(shí)對(duì)我們來說，已經(jīng)足夠了，如果你有幾十臺(tái)的PG 到是可以進(jìn)一步的安裝這個(gè)軟件，來講所有的PG 囊獲其中。軟件的名字叫 PostgreSQL Workload Analyzer。

使用了這個(gè)插件后能回答你對(duì)系統(tǒng)的幾個(gè)問題

平時(shí)系統(tǒng)是怎么進(jìn)行查詢的，經(jīng)常查詢的語句是什么，查詢中同一個(gè)查詢不同的值的分布式怎樣的，那些列會(huì)經(jīng)常在一起查詢。

實(shí)際上我們可以問自己幾個(gè)問題，我為什么要用這個(gè)軟件，會(huì)使用這個(gè)插件，并且通過他來建立一些索引，自然是好的，但這也透露出一個(gè)問題，開發(fā)在開發(fā)系統(tǒng)的時(shí)候，并不知道自己的SQL 語句，或無法提供，并且DBA 也在系統(tǒng)上線前對(duì)此一無所知，這其實(shí)就是一個(gè)BUG，而通過這個(gè)工具來彌補(bǔ)，那只能是虎狼の藥。另外的就是不要迷信這個(gè)軟件，認(rèn)為頭痛醫(yī)頭，腳痛醫(yī)腳，其實(shí)病根在心。（以上啰嗦幾句，不感興趣的客官可以移步 NEXT STATION）

我們回來看看這個(gè)插件里面的一些表

1  pg_qualstats 這個(gè)表本身包含了執(zhí)行語句的用戶，表所處的數(shù)據(jù)庫是那個(gè)，以及表名（可以通過和其他表連接后獲得相關(guān)信息），另外關(guān)鍵的execution_count 和 nbfiltered 這里面的意思是這個(gè)語句執(zhí)行了多少次，并且多少次是重復(fù)的，另外也包含的queryid, 可以追溯你的查詢語句。

2 

select * from pg_qualstats_by_query ;

這個(gè)表重要的地方在于queryid 通過這個(gè)表才能關(guān)聯(lián)你要的查詢語句，最后通過關(guān)聯(lián)其他表將其顯示出來

3 

 select * from pg_qualstats_indexes  這個(gè)表也是關(guān)鍵，他給出了你查詢中需要建立索引的建議

另外還有一些其他的表，這里就不在介紹了，直接給一些自己寫的列子來闡述一下這個(gè)工具到底可以解決實(shí)際什么問題

1  如何確認(rèn)語句運(yùn)行的時(shí)間，下面這個(gè)SQL 可以定期的運(yùn)行，來獲取系統(tǒng)中運(yùn)行的SQL 以及每條SQL的平均運(yùn)行時(shí)間。

with table_info as (select pc.relname,pgq.execution_count,pgq.uniquequalnodeid

from pg_qualstats as pgq

left join pg_class as pc on pgq.lrelid = pc.relfilenode),

query_info as (

select pss.total_time/calls as average_time,pss.query,pgb.uniquequalnodeid

from pg_qualstats_by_query as pgb 

left join pg_stat_statements as pss on pgb.queryid = pss.queryid)

select *

from table_info as t 

right join query_info as q on t.uniquequalnodeid = q.uniquequalnodeid

2

select pqi.relid,pqi.attnames,pqi.execution_count,pqd.idxtype,pqd.ddl 

from pg_qualstats_indexes_ddl as pqd

left join pg_qualstats_indexes as pqi on pqd.relid = pqi.relid and pqd.attnames = pqi.attnames

上面這個(gè)SQL 可以查看到底那個(gè)表上需要建立什么樣的索引，配合上面的表可以通過查詢語句來確認(rèn)添加索引的正確性。

最后說說他的想法是什么

第一步是獲取查詢中所有的謂詞，并分析這個(gè)查詢中提取的謂詞是否有益于查詢，這個(gè)信息存儲(chǔ)在pg_qualstats中，在這其中會(huì)去重一些同樣的語句，但會(huì)記錄相關(guān)的次數(shù)，當(dāng)然這其中也和查詢的方式有關(guān)，如果你是多個(gè)條件加and的操作，則這些條件會(huì)進(jìn)行記錄。根據(jù)查詢的次數(shù)，和頻繁度，查詢數(shù)據(jù)的分布，等推薦需要建立的索引的方式。最終生成相關(guān)的DDL 語句。

感謝各位的閱讀，以上就是“PostgreSQL  pg_qualstats 解決索引缺失的方法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對(duì)PostgreSQL  pg_qualstats 解決索引缺失的方法這一問題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

本文名稱：PostgreSQLpg_qualstats解決索引缺失的方法
轉(zhuǎn)載源于：http://muchs.cn/article48/jioohp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供用戶體驗(yàn)、網(wǎng)站設(shè)計(jì)、服務(wù)器托管、網(wǎng)站排名、微信小程序、商城網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)