Postgresql 查看SQL語(yǔ)句執(zhí)行效率的操作

Explain命令在解決數(shù)據(jù)庫(kù)性能上是第一推薦使用命令，大部分的性能問(wèn)題可以通過(guò)此命令來(lái)簡(jiǎn)單的解決，Explain可以用來(lái)查看 SQL 語(yǔ)句的執(zhí)行效 果，可以幫助選擇更好的索引和優(yōu)化查詢(xún)語(yǔ)句，寫(xiě)出更好的優(yōu)化語(yǔ)句。

Explain語(yǔ)法：

explain select … from … [where ...]

例如：

explain select * from dual;

這里有一個(gè)簡(jiǎn)單的例子，如下：

EXPLAIN SELECT * FROM tenk1;
               QUERY PLAN
----------------------------------------------------------------
   Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)

EXPLAIN引用的數(shù)據(jù)是：

1). 預(yù)計(jì)的啟動(dòng)開(kāi)銷(xiāo)(在輸出掃描開(kāi)始之前消耗的時(shí)間，比如在一個(gè)排序節(jié)點(diǎn)里做排續(xù)的時(shí)間)。

2). 預(yù)計(jì)的總開(kāi)銷(xiāo)。

3). 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)輸出的行數(shù)。

4). 預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度(單位：字節(jié))。

這里開(kāi)銷(xiāo)(cost)的計(jì)算單位是磁盤(pán)頁(yè)面的存取數(shù)量，如1.0將表示一次順序的磁盤(pán)頁(yè)面讀取。其中上層節(jié)點(diǎn)的開(kāi)銷(xiāo)將包括其所有子節(jié)點(diǎn)的開(kāi)銷(xiāo)。這里的輸出行數(shù)(rows)并不是規(guī)劃節(jié)點(diǎn)處理/掃描的行數(shù)，通常會(huì)更少一些。一般而言，頂層的行預(yù)計(jì)數(shù)量會(huì)更接近于查詢(xún)實(shí)際返回的行數(shù)。

現(xiàn)在我們執(zhí)行下面基于系統(tǒng)表的查詢(xún)：

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';

從查詢(xún)結(jié)果中可以看出tenk1表占有358個(gè)磁盤(pán)頁(yè)面和10000條記錄，然而為了計(jì)算cost的值，我們?nèi)匀恍枰懒硗庖粋€(gè)系統(tǒng)參數(shù)值。

postgres=# show cpu_tuple_cost;
   cpu_tuple_cost
  ----------------
   0.01
  (1 row)
cost = 458(磁盤(pán)頁(yè)面數(shù)) + 10000(行數(shù)) * 0.01(cpu_tuple_cost系統(tǒng)參數(shù)值

補(bǔ)充：postgresql SQL COUNT(DISTNCT FIELD) 優(yōu)化

背景

統(tǒng)計(jì)某時(shí)段關(guān)鍵詞的所有總數(shù)，也包含null （statistics 有400w+的數(shù)據(jù)，表大小為 600M），故

寫(xiě)出sql：

select count(distinct keyword) +1 as count from statistics;

問(wèn)題

雖然是后臺(tái)查詢(xún)，但是太慢了，執(zhí)行時(shí)間為為 38.6s，那怎么優(yōu)化呢？

解決

方法1（治標(biāo)）

把這個(gè)定時(shí)執(zhí)行，然后把sql結(jié)果緩存下，然后程序訪問(wèn)緩存結(jié)果，頁(yè)面訪問(wèn)是快了些，但是本質(zhì)上還沒(méi)有解決sql執(zhí)行慢的問(wèn)題。

方法2（治本）

優(yōu)化sql，首先說(shuō)說(shuō) count( distinct FIELD) 為啥這么慢，此處不再贅述了，請(qǐng)看這篇：https://www.jb51.net/article/65680.htm

優(yōu)化內(nèi)容：

select count( distinct FIELD ) from table

修改為

select count(1) from (select distinct FIELD from table) as foo;

比較

執(zhí)行過(guò)程比對(duì)，可以使用 explian anaylze sql語(yǔ)句 查看

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。