《PostgreSQL索引優化案例分析》要點：
本文介紹了PostgreSQL索引優化案例分析，希望對您有用。如果有疑問，可以聯系我們。

相關主題：PostgreSQL教程

[來自IT168]

【IT168 專稿】本文根據【2016 第七屆中國數據庫技術大會】現場演講嘉賓竇賢明老師分享內容整理而成.錄音整理及文字編輯IT168@胡晴.

講師簡介

竇賢明(執白)

竇賢明老師是阿里云資深研發工程師,有六年程序開發經驗,關注分布式數據庫、關系型數據庫、云計算、分布式存儲等技術,PostgreSQL信徒.現就職于阿里云數據庫技術組內核服務組,負責ApsaraDB(RDS) For PostgreSQL/Greenplum的內核、自動化運維等研發工作,旨在實現用戶在云數據庫上的良好體驗.

正文

大家好,我是竇賢明,在阿里云做工程師.本日給大家簡單介紹在PostgreSQL上做索引優化的簡單思路,不做過多的深入,就是大概的介紹,讓大家了解一下.

在接觸客戶過程中,我們遇到非常多的性能問題,是他們在公開執行方面的問題.辦理過程中發現一些通用的東西.

性能問題遇到最多的情況在哪里?比如一個查詢時間長,可能幾秒或者更長時間結束.客戶用的處理技術參差不齊,互聯網客戶比較多,數據也有些參差不齊,所以會遇到各種各樣的性能問題.然后他們會經常問,我的CPU怎么占滿了?我的IO為什么這么高?內存值也這么高?

性能問題：

其實有一個辦理思路,給大家簡單介紹我們主要面臨的問題.

查詢時間長

資源占用：這里提的是兩個比較典型的CPU高和IO高.

1、CPU高：數據在內存里

2、IO高：數據在磁盤里

這是兩個非常典型的性能出現瓶頸的地方.

其實都是索引的問題.當出現所有問題的時候,往往面臨全盤掃描.而當數據出現在內存中時就耗CPU,在磁盤里的時候就耗IO.怎么辦理這個問題?就是建立一個合適的索引.

選擇一個索引通常有兩步,第一步選擇對哪些列進行索引;第二步是創造什么類型的索引.

列的選擇：

第一步就是怎樣選擇這一列,當我們選擇列的時候,往往看SQL語句.

1、where子句：所以首先會看到后面的where子句,where子句用于過濾所查詢的結果,代表我們要查詢多少行、查詢多少數據,這個就是我們的核心.

2、order by子句

3、group by子句

4、函數參數

還有order by、group by、函數參數,這就是函數索引.

索引的選擇：

第二步是索引的選擇,但一般不可能對所有列建立索引.大家都知道索引是有代價的,比如插入速度的減緩、空間占有都會有代價.選擇一個比較關鍵的字段就可以了.

1、基數：判斷的標準就是索引字段值的基數

2、相關性：索引和磁盤相關性的問題

3、代價(選擇性、直方圖、MCV\MCF)：最終評判標準就是看哪個代價更低

通過這個基本原理,我們可以找到針對哪些列做索引,再選擇做什么樣的索引.

PostgreSQL提供了很多信息來輔助我們UA、應用者,和開發者.我們關注幾個系統表里跟索引相關的：

1、stat_user_tables：記錄索引表的所有相關信息,包含進行多少次全表掃描、更新情況.

2、stat_all_indexes：記錄索引的掃描情況,也可以用于判斷建立這個索引對于索引數據的有效性.

3、pg_stats / pg_statistics：這個是后面主要用到的地方,會記錄非常多的統計信息.

這是它的表結構,涉及到null_frac、avg_width、n_distinct幾個比較重要的字段.后面我們會用,大家要記住.

第一個叫n_distinct,很多情況下是一個比值,基本上來算基數.當基數個數并不多的時候,它會是一個正整數,表示有多少個基數.比如最典型的性別,有兩個,沒有三個,所以它的基數就是2;當基數數值都不一樣的時候就是-1;當它是一個比值的時候,基數是一定范圍,大概是30%—50%.

第二個是比較重要的是most_common_vals,就是哪些字段出現的頻率最高.most_common_vals也是一個數字類型叫數組,下面的most_common_freqs是它占用的比例,這個比例表示一個字段在所有基數中占有的比例是多少.

還有一個histogram_bounds,就是直方圖.

辦法：

第一步應該把SQL拆解,拆開之后看寫法.

倒數第二條的filter條件,是指過濾條件.

上面的Seq Scan on vtbl是指這個地方沒有索引,所以只能全盤掃描.

后面還有一個cost,是很關鍵的一個數字.它會贊助我們決定這個索引有沒有作用、有沒有贊助、贊助有多大.所有產業計劃的核心就是cost,這個代價是不是夠低?代價比較少,掃描就比較少,占用時間就比較少.

要注意一點,pg的數據都是采樣,它的數據只能說達到一定比例,不是百分之百精確.所以cost值也是估算的,不是絕對精確的值.

案例一：

這是一個非常典型的例子,也是非常復雜的一張表,因為涉及到ID、key.

key相當于一個值,不過沒有什么意義.

shape是我自己構造的一個多維向量,是一個非常復雜的數據類型,可以任意組合任何數據,如IP數據、點數據和幾何數據.它一個三維的數據,可以用來表現它的三維位置.

Location是一個geography的數據,location_geometry是一個幾何數據,comment是test,是隨機的狀態.

這邊看完以后我們看一下上面的語句,這條語句其實非常典型,就是一個簡單的查詢.然后有兩個條件,一個是key,一個是shape.

剛才例子中的where語句分成兩個字段,key和shape.我們第一反應看這個數據是不是應該在key和shape上做過濾,在這個上面去索引.

但是那怎么建呢?是這兩個都建嗎?很多時候是沒有必要的,往往針對某一個做到比較高的過濾性的時候,這個索引就已經足夠了,建兩個的時候空間是有一點浪費.

correlation就是一個典型的磁盤和他的數據的相關性.

如果是完全順序的話比較好,就是一個索引.如果不是順序,放在磁盤中間比較有效.例如不是1或者-1的時候,它的隨機IO比較高,但如果它是零點幾的時候,就不是完全順序的.但這個時候最好的地方是相關性,因為都涉及到IO,這樣會IO效果會更好一點.

n_distinct是一個基數,等于-1是說這個key每一條基數都是唯一的,大家如果有經驗就會知道這個非常適合檢索.correlation并不是很高,索引對我們來說是有一定贊助的.mcv、mcf沒有值,因為所有頻率都是一樣的.

而shape不一樣,大約是幾十萬條構成的,是一個三維的值.

n_distinct其實并不是2000個,大約是有幾十萬個n_distinct的基數,因為它也是估計值,是根據采樣出來的結果.但是n_distinct大約是幾十萬條,所以這個大家要注意一點.一般來講為正數的時候是有限度的,可以這樣理解.

correlation非常低,它的行和行的隨機排列比較多,這個時候的索引屬于有贊助的.

mcv的取值跟它的采樣數據有關,重新進行采樣或計算時,每次采樣都不一樣,mcv肯定每次都會變.correlation可能會有點變化,n_distinct變化不會太大,但是mcv會變化.

其中一個值70,103,206,它是第一條數據,大家可以看到頻率并不算高,相對來講頻率比較低,是它上面的值對應的一個頻率.這個很簡單,是來計算代價的.大家了解之后就知道這個的代價是什么,后面就會講代價計算的問題.

選擇性：

大家看選擇性的問題,n_distinct基數的個數是-1,就意味著6990419這條語句一定只有一條,一條數據其實就是他的selectivity.

selectivity是一段過濾的長度,實際上就是過濾性.如果有這個條件和沒有條件之間的差別非常大,說明這個條件的過濾性非常高.如果最后根據索引找到那個地方的值,那么價值就非常大,這種情況是價值最大的情況.

那么再看shape,一千萬條基數很容易就找到這個值,這個值的選擇性就是70103206.

計算有索引情況的代價：

有索引情況首先會進行全盤掃描,它的代價就是所有的全盤掃描的的IO,加上每次操作符判斷的本錢,然后再把那條記錄去掃描.

回頭看一下前面的執行計劃,cost其實就兩個,一個是Filter,一個是Seq Scan.回到這個問題,這個地方有什么意義呢?當有索引的時候,只要找到對應的索引,找到對應的文件就可以.所以它只有一條記錄的IO,加上一個索引的查找,這個本錢就非常低了,尤其在基數非常多的情況下.

有一個很有意思的情況,當你基數不是特別多的時候,加索引也沒有意義了,因為可能整個全盤掃描的本錢,還低于進行隨機掃描再加上取IO的本錢,基本上這個本錢是無所謂的.所以在基數比較少的時候,它的本錢比較低.

這個索引之前有一個條件是key和shape共同的條件,本錢是33萬,這個本錢的單位是它自己的單位,是執行前的本錢.

要注意幾個問題,第一個是Filter條件;第二個是Seq Scan的類型;第三個是cost的值;還有一個是rows,這個是最后的值.這個值比較大的時候,這個索引建不建無所謂.

但是索引之后的情況,一定要注意本錢,前面是0.33到8.46,就是33萬到8的差別.但shape就沒有這么高,我沒有把例子放在這里,大家可以自己試一下,這個值不多的時候怎樣計算.rows是一樣的值,為1.

執行計劃其實不一定是執行的情況,不一定準,因為cost畢竟是一個估計值.

建索引前,time是 1673毫秒,就是1.6秒.這個聽起來也不長,但是到手機上肯定成問題.

再看建索引之后,28毫秒,大約是50分.一般這個地方比例比較大,最好前面都有ID,效果比較明顯.而且記錄的數據越多,下面8.46的值基本上不會變化.當mcv、mvf變化,并不是完全唯一時,這個值就不一樣.

這是第一個結構,我們最終選擇的也是這個.

剛才運氣比較好,key的值正好是唯一的.如果key值不一樣怎么辦?比如key值是0.1、0.008,這個算作大家的一個思考題吧!當這個key的值也不唯一的時候,可以把key和shape結合起來.

案例二：

我們看看第二個,這個看起來很復雜,其實也挺簡單,select*from后面是一個子查詢,子查詢里面又有一個from vtbl,而這vtbl和前面的一樣,只不過我把兩個不同的字段換了一個標準.

我們注意有幾個標識,第一個是ST_distance是一個計算距離的函數,所以這里涉及到函數的關系.

location_geometry這個值用于計算distance,distance的過濾性和選擇性就依賴于as distance的計算結果,但是其實這個特別難估計,這個先放著.

我們看order by、where條件、函數等條件,他們都指向location_geometry,那么肯定要看location_geometry.

基數、相關性：

n_distinct是-1,是近似值,那么我們說n_distinct這個值不可靠.我們需要把n_distinct轉換成可讀的值.

這里遇到一個比較特殊的gist索引,針對幾何類型的索引類型.

我們直接看一下結果.

思考題：

然后我們留了幾個思考題,大家可以想一下.

這個是大家經常用到的工具.

這里就是我們本日講的內容.

《PostgreSQL索引優化案例分析》是否對您有啟發，歡迎查看更多與《PostgreSQL索引優化案例分析》相關教程，學精學透。維易PHP學院為您提供精彩教程。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/9620.html