往期分享

RDS MySQL 小版本更新最佳實踐 RDS MySQL 執行個體空間問題 RDS MySQL 記憶體使用問題 RDS MySQL 活躍線程數高問題 RDS MySQL 慢SQL問題 RDS MySQL 執行個體IO高問題

概述

慢SQL會消耗較多的資料庫資源，很容易造成資料庫的負載增加，慢SQL的問題直接影響着資料庫的性能，排查慢SQL問題，首先要找到RDS PG中的慢SQL，在學習EXPLAIN的文法，學會閱讀執行計劃，并對SQL進行優化。

找到慢SQL

控制慢日志時長的參數為log_min_duration_statement，機關為ms，預設值為超過1秒的SQL會收集在慢SQL日志中進行展示。

慢SQL日志檢視

通過開啟auto_explain.log_analyze、auto_explain.log_buffers、auto_explain.log_min_duration，可以在慢日志中列印慢SQL的執行計劃。

EXPLAIN文法

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

這裡 option可以是：

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

這個指令顯示PostgreSQL計劃器為提供的語句所生成的執行計劃。該執行計劃會顯示将怎樣掃描語句中引用的表 — 普通的順序掃描、索引掃描等等 — 以及在引用多個表時使用何種連接配接算法來把來自每個輸入表的行連接配接在一起。

postgres=# explain select * from test;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on test  (cost=0.00..1.01 rows=1 width=4)
(1 row)

顯示中最重要的部分是估計出的語句執行代價，它是計劃器對于該語句要運作多久的猜測（以任意的代價機關度量，但是習慣上表示取磁盤頁面的次數）。事實上會顯示兩個數字：在第一行能被傳回前的啟動代價，以及傳回所有行的總代價。對于大部分查詢來說總代價是最重要的，但是在一些情景中（如EXISTS中的子查詢），計劃器将選擇更小的啟動代價來代替最小的總代價（因為執行器将在得到一行後停止）。此外，如果你用一個LIMIT子句限制傳回行的數量，計劃器會在終端代價之間做出适當的插值來估計到底哪個計劃是真正代價最低的。

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; 
QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1) 
  Sort Key: t1.fivethous 
  Sort Method: quicksort Memory: 77kB 
  -> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1) 
       Hash Cond: (t2.unique2 = t1.unique2) 
       -> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1) 
       -> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1) 
            Buckets: 1024 Batches: 1 Memory Usage: 28kB 
            -> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1) 
                 Recheck Cond: (unique1 < 100) 
                 -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1) 
                      Index Cond: (unique1 < 100) 
Planning time: 0.194 ms 
Execution time: 8.008 ms

analyze:執行指令并且顯示實際的運作時間和其他統計資訊。參數預設值為FALSE
verbose:顯示關于計劃的額外資訊。特别是：計劃樹中每個結點的輸出列清單、模式限定的表和函數名、總是把表達式中的變量标上它們的範圍表别名，以及總是列印統計資訊被顯示的每個觸發器的名稱。這個參數預設被設定為FALSE。
costs:包括每一個計劃結點的估計啟動和總代價，以及估計的行數和每行的寬度。這個參數預設被設定為TRUE。
buffers:包括緩沖區使用的資訊。特别是：共享塊命中、讀取、标記為髒和寫入的次數、本地塊命中、讀取、标記為髒和寫入的次數、以及臨時塊讀取和寫入的次數。一次命中表示避免了一次讀取，因為需要的塊已經在緩存中找到了。共享塊包含着來自于正常表和索引的資料，本地塊包含着來自于臨時表和索引的資料，而臨時塊包含着在排序、哈希、物化計劃結點和類似情況中使用的短期工作資料。髒塊的數量表示被這個查詢改變的之前未被修改塊的數量，而寫入塊的數量表示這個背景在查詢處理期間從緩存中替換出去的髒塊的數量。為一個較高層結點顯示的塊數包括它的所有子結點所用到的塊數。在文本格式中，隻會列印非零值。隻有當ANALYZE也被啟用時，這個參數才能使用。它的預設被設定為FALSE。
timing:在輸出中包括實際啟動時間以及在每個結點中花掉的時間。反複讀取系統時鐘的負荷在某些系統上會顯著地拖慢查詢，是以在隻需要實際的行計數而不是實際時間時，把這個參數設定為FALSE可能會有用。即便用這個選項關閉結點層的計時，整個語句的運作時間也總是會被度量。隻有當ANALYZE也被啟用時，這個參數才能使用。它的預設被設定為TRUE。
summary:在查詢計劃之後包含摘要資訊（例如，總計的時間資訊）。當使用ANALYZE 時預設包含摘要資訊，但預設情況下不包含摘要資訊，但可以使用此選項啟用摘要資訊。使用EXPLAIN EXECUTE中的計劃時間包括從緩存中擷取計劃所需的時間以及重新計劃所需的時間（如有必要）。
format:指定輸出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本輸出包含和文本輸出格式相同的資訊，但是更容易被程式解析。這個參數預設被設定為TEXT。

如何閱讀執行計劃

從上面的例子來看，EXPLAIN 指令的輸出可以看做是一個樹形結構，我們稱之為查詢計劃樹，樹的每個節點包括對應的節點類型，作用對象以及其他屬性例如cost、rows、width 等。如果隻顯示節點類型，上面的例子可以簡化為如下結構：

Sort 
└── Hash Join 
          ├── Seq Scan
          └── Hash
                     └── Bitmap Heap Scan
                                └── Bitmap Index Scan

閱讀執行計劃是查詢計劃從下往上閱讀，每個節點執行傳回的結果會傳遞給父節點，比如這個例子，首先是進行Bitma Index Scan，然後将結果傳遞給Bitmap Heap Scan，然後傳遞給Hash節點。Seq Scan是與Hash平級的節點，Hash和Seq Scan兩個節點将結果傳遞給Hash Join。Hash Join将結果傳遞給Sort。

在EXPLAIN 指令的輸出結果中可能包含多種類型的執行節點，可以大體分為幾大類：

1. 控制節點（Control Node)
2. 掃描節點（ScanNode)
3. 物化節點（Materialization Node)
4. 連接配接節點（Join Node)

下面詳細介紹一下掃描節點，掃描節點狹義的講指的是掃描表中的資料，實際也包含掃描函數結果集、子查詢結果等。目前支援的掃描節點如下:

- Seq Scan，順序掃描
- Index Scan，基于索引掃描，但不隻是傳回索引列的值
- IndexOnly Scan，基于索引掃描，并且隻傳回索引列的值，簡稱為覆寫索引
- BitmapIndex Scan，利用Bitmap 結構掃描
- BitmapHeap Scan，把BitmapIndex Scan 傳回的Bitmap 結構轉換為元組結構
- Tid Scan，用于掃描一個元組TID 數組
- Subquery Scan，掃描一個子查詢
- Function Scan，處理含有函數的掃描
- TableFunc Scan，處理tablefunc 相關的掃描
- Values Scan，用于掃描Values 連結清單的掃描
- Cte Scan，用于掃描WITH 字句的結果集
- NamedTuplestore Scan，用于某些命名的結果集的掃描
- WorkTable Scan，用于掃描Recursive Union 的中間資料
- Foreign Scan，用于外鍵掃描
- Custom Scan，用于使用者自定義的掃描

下面重點介紹常用的幾個：Seq Scan、Index Scan、IndexOnly Scan、BitmapIndex Scan、BitmapHeap Scan

Seq Scan 是全表順序掃描，一般查詢沒有索引的表需要全表順序掃描，例如下面的EXPLAIN 輸出：

postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2; 
QUERY PLAN 
--------------------------------------------------------------------------------------------------
Seq Scan on public.class (cost=0.00..26.00 rows=1 width=35) (actual time=0.136..0.141 rows=1 loops=1) 
    Output: st_no, name 
    Filter: (class.st_no = 2) 
    Rows Removed by Filter: 1199 
    Buffers: shared hit=11 

Planning time: 0.066 ms 
Execution time: 0.160 ms

Seq Scan on public.class 表明了這個節點的類型和作用對象，即在class 表上進行了全表掃描。
(cost=0.00..26.00 rows=1 width=35) 表明了這個節點的代價估計，0.00是節點啟動成本，26.00為該節點的代價，rows是該節點輸出行的估計值，width是該節點輸出行的平均寬度。
(actual time=0.136..0.141 rows=1 loops=1) 表明了這個節點的真實執行資訊，0.136表示該節點啟動時間機關是ms。0.141表示該節點耗時機關是ms。rows代表實際輸出行。loops代表節點循環次數。
Output: st_no, name 表明了SQL 的輸出結果集的各個列，當EXPLAIN 指令中的選項VERBOSE 為on時才會顯示。
Filter: (class.st_no = 2) 表明了Seq Scan 節點之上的Filter 操作，即全表掃描時對每行記錄進行過濾操作，過濾條件為class.st_no = 2
Rows Removed by Filter: 1199 表明了過濾操作過濾了多少行記錄，屬于Seq Scan 節點的VERBOSE 資訊，隻有EXPLAIN 指令中的VERBOSE 選項為on 時才會顯示
Buffers: shared hit=11 表明了從共享緩存中命中了11 個BLOCK，屬于Seq Scan 節點的BUFFERS 資訊，隻有EXPLAIN 指令中的BUFFERS 選項為on 時才會顯示
Planning time: 0.066 ms 表明了生成查詢計劃的時間
Execution time: 0.160 ms 表明了實際的SQL 執行時間，其中不包括查詢計劃的生成時間

Index Scan 是索引掃描，主要用來在WHERE 條件中存在索引列時的掃描，如上面Seq Scan 中的查詢如果在st_no 上建立索引，則EXPLAIN 輸出如下：

QUERY PLAN 
--------------------------------------------------------------------------------------------------
Index Scan using no_index on public.class (cost=0.28..8.29 rows=1 width=35) (actual time=0.022..0.023 rows=1 loops=1) 
     Output: st_no, name 
     Index Cond: (class.st_no = 2) 
     Buffers: shared hit=3 
Planning time: 0.119 ms 
Execution time: 0.060 ms 
(6 rows)

Index Scan using no_index on public.class 表明是使用的public.class 表的no_index 索引對表進行索引掃描的

Index Cond: (class.st_no = 2) 表明索引掃描的條件是class.st_no = 2

可以看出，使用了索引之後，對相同表的相同條件的掃描速度變快了。這是因為從全表掃描變為索引掃描，通過Buffers: shared hit=3 可以看出，需要掃描的BLOCK（或者說元組）少了，是以需要的代價也就小了，速度也就快了。

IndexOnly Scan 是覆寫索引掃描，所需的傳回結果能被所掃描的索引全部覆寫，如上面Index Scan中的SQL 把“select * ” 修改為“select st_no” ，其EXPLAIN 結果輸出如下：

postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select st_no from class where st_no=2; 
QUERY PLAN 
--------------------------------------------------------------------------------------------------
Index Only Scan using no_index on public.class (cost=0.28..4.29 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1) 
    Output: st_no Index Cond: (class.st_no = 2) 
    Heap Fetches: 0 
    Buffers: shared hit=3 
Planning time: 0.058 ms 
Execution time: 0.036 ms 
(7 rows)

Index Only Scan using no_index on public.class 表明使用public.class 表的no_index 索引對表進行覆寫索引掃描。Heap Fetches 表明需要掃描資料塊的個數。雖然Index Only Scan 可以從索引直接輸出結果。但是因為PostgreSQL MVCC 機制的實作，需要對掃描的元組進行可見性判斷，即檢查visibility MAP 檔案。當建立表之後，如果沒有進行過vacuum和autovacuum操作，這時還沒有VM檔案，而索引并沒有儲存記錄的版本資訊，索引Index Only Scan 還是需要掃描資料塊（Heap Fetches 代表需要掃描的資料塊個數）來擷取版本資訊，這個時候可能會比Index Scan 慢。

Bitmap Index Scan 與Index Scan 很相似，都是基于索引的掃描，但BitmapIndex Scan 節點每次執行傳回的是一個位圖而不是一個元組，位圖中每位代表了一個掃描到的資料塊。而BitmapHeap Scan一般會作為BitmapIndex Scan 的父節點，将BitmapIndex Scan 傳回的位圖轉換為對應的元組。這樣做最大的好處就是把Index Scan 的随機讀轉換成了按照資料塊的實體順序讀取，在資料量比較大的時候，這會大大提升掃描的性能。EXPLAIN結果輸出如下：

postgres=> explain(ANALYZE,VERBOSE,BUFFERS) select * from class where st_no=2; 
QUERY PLAN 
--------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.class (cost=4.29..8.30 rows=1 width=35) (actual time=0.025..0.025 rows=1 loops=1) Output: st_no, name 
Recheck Cond: (class.st_no = 2) 
Heap Blocks: exact=1 
Buffers: shared hit=3 
-> Bitmap Index Scan on no_index (cost=0.00..4.29 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (class.st_no = 2) 
Buffers: shared hit=2 
Planning time: 0.088 ms 
Execution time: 0.063 ms 
(10 rows)

Bitmap Index Scan on no_index 表明使用no_index 索引進行位圖索引掃描
Index Cond: (class.st_no = 2) 表明位圖索引的條件為class.st_no = 2
Bitmap Heap Scan on public.class 表明對public.class 表進行Bitmap Heap 掃描
Recheck Cond: (class.st_no = 2) 表明Bitmap Heap Scan 的Recheck操作的條件是class.st_no = 2，這是因為Bitmap Index Scan 節點傳回的是位圖，位圖中每位代表了一個掃描到的資料塊，通過位圖可以定位到一些符合條件的資料塊（這裡是3，Buffers: shared hit=3），而Bitmap Heap Scan 則需要對每個資料塊的元組進行Recheck
Heap Blocks: exact=1 表明準确掃描到資料塊的個數是1
一般來說：
大多數情況下，Index Scan 要比 Seq Scan 快。但是如果擷取的結果集占所有資料的比重很大時，這時Index Scan 因為要先掃描索引再讀表資料反而不如直接全表掃描來的快。
如果擷取的結果集的占比比較小，但是元組數很多時，可能Bitmap Index Scan 的性能要比Index Scan 好。
如果擷取的結果集能夠被索引覆寫，則Index Only Scan 因為不用去讀資料，隻掃描索引，性能一般最好。但是如果VM 檔案未生成，可能性能就會比Index Scan 要差。

SQL優化示例

無索引

canno=> explain analyze select * from t1 where id =1;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..97331.31 rows=1 width=4) (actual time=0.217..302.316 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on t1  (cost=0.00..96331.21 rows=1 width=4) (actual time=191.208..289.240 rows=0 loops=3)
         Filter: (id = 1)
         Rows Removed by Filter: 3333333
 Planning Time: 0.030 ms
 Execution Time: 302.381 ms
(8 rows)

通過計劃可以看出是全表掃描的t1表，過濾條件是id=1；
針對t1(id)建立索引
create index on t1(id)

canno=> explain analyze select * from t1 where id =1;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t1_id_idx on t1  (cost=0.43..2.45 rows=1 width=4) (actual time=0.035..0.036 rows=1 loops=1)
   Index Cond: (id = 1)
   Heap Fetches: 1
 Planning Time: 0.134 ms
 Execution Time: 0.052 ms
(5 rows)

檢視新的執行計劃，時間縮短至0.052ms。

不是最優索引

create table t2(id int,name int);
insert into t2 select random()*(id/100),random()*(id/100) from generate_series(1,1000000) t(id);

create index on t2(id)

canno=> explain analyze select * from t2 where id=10 and name=13;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using t2_id_idx on t2  (cost=0.42..12.12 rows=1 width=8) (actual time=0.098..2.631 rows=88 loops=1)
   Index Cond: (id = 10)
   Filter: (name = 13)
   Rows Removed by Filter: 4461
 Planning Time: 0.081 ms
 Execution Time: 2.655 ms
(6 rows)

檢視計劃發現已經做了索引掃描，但是還是存在name字段的條件過濾。可以增加一個id+name的索引。
create index on t2(id,name);

canno=> explain analyze select * from t2 where id=10 and name=13;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t2_id_name_idx on t2  (cost=0.42..15.48 rows=18 width=8) (actual time=0.028..0.134 rows=88 loops=1)
   Index Cond: ((id = 10) AND (name = 13))
   Heap Fetches: 88
 Planning Time: 0.198 ms
 Execution Time: 0.157 ms
(5 rows)

可以看出時間從2.6ms降低至0.157ms

【巡檢問題分析與最佳實踐】RDS PostgreSQL 慢SQL問題往期分享概述找到慢SQLEXPLAIN文法如何閱讀執行計劃SQL優化示例

往期分享

概述

找到慢SQL

EXPLAIN文法

如何閱讀執行計劃

SQL優化示例

無索引

不是最優索引

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述