Postgresql 查看SQL語句執行效率的操作

Posted on by

Explain命令在解決數據庫性能上是第一推薦使用命令，大部分的性能問題可以通過此命令來簡單的解決，Explain可以用來查看 SQL 語句的執行效 果，可以幫助選擇更好的索引和優化查詢語句，寫出更好的優化語句。

Explain語法：

explain select … from … [where ...]

例如：

explain select * from dual;

這裡有一個簡單的例子，如下：

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

EXPLAIN引用的數據是：

1). 預計的啟動開銷(在輸出掃描開始之前消耗的時間，比如在一個排序節點裡做排續的時間)。

2). 預計的總開銷。

3). 預計的該規劃節點輸出的行數。

4). 預計的該規劃節點的行平均寬度(單位：字節)。

這裡開銷(cost)的計算單位是磁盤頁面的存取數量，如1.0將表示一次順序的磁盤頁面讀取。其中上層節點的開銷將包括其所有子節點的開銷。這裡的輸出行數(rows)並不是規劃節點處理/掃描的行數，通常會更少一些。一般而言，頂層的行預計數量會更接近於查詢實際返回的行數。

現在我們執行下面基於系統表的查詢：

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

從查詢結果中可以看出tenk1表占有358個磁盤頁面和10000條記錄，然而為瞭計算cost的值，我們仍然需要知道另外一個系統參數值。

postgres=# show cpu_tuple_cost;
   cpu_tuple_cost
  ----------------
   0.01
  (1 row)
cost = 458(磁盤頁面數) + 10000(行數) * 0.01(cpu_tuple_cost系統參數值

補充：postgresql SQL COUNT(DISTNCT FIELD) 優化

背景

統計某時段關鍵詞的所有總數，也包含null （statistics 有400w+的數據，表大小為 600M），故

寫出sql：

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

問題

雖然是後臺查詢，但是太慢瞭，執行時間為為 38.6s，那怎麼優化呢？

解決

方法1（治標）

把這個定時執行，然後把sql結果緩存下，然後程序訪問緩存結果，頁面訪問是快瞭些，但是本質上還沒有解決sql執行慢的問題。

方法2（治本）

優化sql，首先說說 count( distinct FIELD) 為啥這麼慢，此處不再贅述瞭，請看這篇：https://www.jb51.net/article/65680.htm

優化內容：

select count( distinct FIELD ) from table

修改為

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

比較

執行過程比對，可以使用 explian anaylze sql語句 查看

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：