PolarDB：自動索引推薦

背景資訊

資料庫效能最佳化往往是資料庫管理的關鍵任務之一，而對於SQL查詢的效能提升，索引起著至關重要的作用。合理的索引可以使得資料庫快速定位到表中特定的行，直接擷取到已經排序好的資料，極大縮短查詢時間。

不過，並非所有索引都能有效提升效能。一個合適的索引能夠大幅提升查詢速度，而不恰當的索引不僅無法提升效能，反而可能由於維護索引本身的開銷而降低資料庫的整體效能。此外，索引還佔用儲存空間，過多的無效索引會浪費儲存資源。因此，確定何時以及在哪些列上建立索引是一個技術性和經驗性都很強的領域，這通常需要深入分析查詢的執行路徑和資料的分布特徵。

為了協助使用者解決這一技術難題並最佳化資料庫的效能，PolarDB PostgreSQL版（相容Oracle）推出自動索引推薦功能。該功能可以簡化索引的建立流程，降低開發和維護資料庫索引的難度。使用者僅需通過EXPLAIN命令對慢SQL語句進行效能分析，系統便能基於該分析提供合適的索引建立建議，並估計出預期的查詢加速效果。

自動索引推薦功能的主要優勢如下：

• 精準推薦：利用資料庫自動搜集的統計資訊和最佳化器代價模型，能夠為具體的SQL查詢推薦合適的索引配置，避免了盲目地添加索引可能帶來的效能負擔和試錯成本。

• 效率提升：減少手動分析慢查詢的時間，協助快速識別並針對性地解決效能瓶頸，縮短問題定位和解決的周期。

• 易於操作：通過一個簡單的EXPLAIN命令就能獲得索引推薦資訊，同時提供建立索引的SQL命令以及相應的效能提升預測，最佳化了使用者後續操作流程。

使用指南

配置自動索引推薦

1. 設定所有使用者載入polar_advisor，即在session_preload_libraries中增加polar_advisor。

   -- session_preload_libraries預設為空白，此處添加polar_advisor
   alter role all set session_preload_libraries to 'polar_advisor';

2. 設定所有使用者在特定節點上開啟索引推薦，推薦僅在RO節點開啟，避免消耗RW節點的計算資源。

   -- 設定在RO節點上開啟索引推薦【推薦】
   alter role all set polar_advisor_type to 'index(ro)';

   -- 設定在所有節點上開啟索引推薦
   alter role all set polar_advisor_type to 'index';

使用自動索引推薦

使用不帶ANALYZE語句的EXPLAIN命令為慢SQL產生執行計畫，自動索引推薦將會自動運行，檢測到推薦的索引後會自動輸出，包括如下資訊：

• advise：建立推薦索引的命令。

• old cost：原計劃即現在計劃的cost。

• new cost：預估的建立推薦索引後的計劃的cost。

• saved cost：新計劃較老計劃節省的cost。

• estimate：預估的加速效果。

自動索引推薦現在能識別以下類型的運算元，目前僅支援推薦BTREE索引，樣本如下。

構造表及資料。

create table t( a int,b int);
insert into t select i,i from generate_series(1,10000)i;
analyze t;

• 根據等值條件推薦單列索引。

  /*FORCE_SLAVE*/ explain select * from t where a = 1;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 0.00..170.00
  NOTICE:  IND ADV:   new cost 0.04..2.25
  NOTICE:  IND ADV: saved cost -0.04..167.75
  NOTICE:  IND ADV: estimate 75.5 times faster
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=1 width=8)
     Filter: (a = 1)
  (2 rows)

• 根據比較條件推薦單列索引。

   /*FORCE_SLAVE*/ explain select * from t where a > 10; -- 索引篩選不了多少資料因此沒有推薦索引
                        QUERY PLAN
  ------------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=9990 width=8)
     Filter: (a > 10)
  (2 rows)
  
   /*FORCE_SLAVE*/ explain select * from t where a < 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 0.00..170.00
  NOTICE:  IND ADV:   new cost 0.04..2.39
  NOTICE:  IND ADV: saved cost -0.04..167.61
  NOTICE:  IND ADV: estimate 71.1 times faster
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
     Filter: (a < 10)
  (2 rows)
  

• 根據多個等值或比較條件推薦複合式索引。

  /*FORCE_SLAVE*/ explain select * from t where a = 1 and b = 1;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (b, a)"
  NOTICE:  IND ADV:   old cost 0.00..195.00
  NOTICE:  IND ADV:   new cost 0.04..1.16
  NOTICE:  IND ADV: saved cost -0.04..193.84
  NOTICE:  IND ADV: estimate 168.8 times faster
                      QUERY PLAN
  ---------------------------------------------------
   Seq Scan on t  (cost=0.00..195.00 rows=1 width=8)
     Filter: ((a = 1) AND (b = 1))
  (2 rows)

• 根據Order BY運算元推薦單列索引。

   /*FORCE_SLAVE*/ explain select * from t order by a limit 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 361.10..361.12
  NOTICE:  IND ADV:   new cost 0.04..0.26
  NOTICE:  IND ADV: saved cost 361.06..360.86
  NOTICE:  IND ADV: estimate 1366.8 times faster
                              QUERY PLAN
  -------------------------------------------------------------------
   Limit  (cost=361.10..361.12 rows=10 width=8)
     ->  Sort  (cost=361.10..386.10 rows=10000 width=8)
           Sort Key: a
           ->  Seq Scan on t  (cost=0.00..145.00 rows=10000 width=8)
  (4 rows)

• 根據Group BY運算元推薦單列索引。

  /*FORCE_SLAVE*/ explain select a,sum(b) from t group by a having a < 10 ;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 170.14..170.30
  NOTICE:  IND ADV:   new cost 0.04..2.53
  NOTICE:  IND ADV: saved cost 170.11..167.77
  NOTICE:  IND ADV: estimate 67.4 times faster
                            QUERY PLAN
  ---------------------------------------------------------------
   GroupAggregate  (cost=170.14..170.30 rows=9 width=12)
     Group Key: a
     ->  Sort  (cost=170.14..170.17 rows=9 width=8)
           Sort Key: a
           ->  Seq Scan on t  (cost=0.00..170.00 rows=9 width=8)
                 Filter: (a < 10)
  (6 rows)

• 根據等值串連操作推薦單列索引。

  /*FORCE_SLAVE*/ explain select * from t t1,t t2 where t1.a = t2.a limit 10;
  INFO:  IND ADV: advise "CREATE INDEX CONCURRENTLY ON public.t USING btree (a)"
  NOTICE:  IND ADV:   old cost 270.00..270.28
  NOTICE:  IND ADV:   new cost 0.07..0.70
  NOTICE:  IND ADV: saved cost 269.93..269.58
  NOTICE:  IND ADV: estimate 384.3 times faster
                                   QUERY PLAN
  ----------------------------------------------------------------------------
   Limit  (cost=270.00..270.28 rows=10 width=16)
     ->  Hash Join  (cost=270.00..552.50 rows=10000 width=16)
           Hash Cond: (t1.a = t2.a)
           ->  Seq Scan on t t1  (cost=0.00..145.00 rows=10000 width=8)
           ->  Hash  (cost=145.00..145.00 rows=10000 width=8)
                 ->  Seq Scan on t t2  (cost=0.00..145.00 rows=10000 width=8)
  (6 rows)