全域二級索引 - Hologres

Hologres自V4.0版本開始支援全域二級索引，適用於在非主鍵列上進行高效Key-Value查詢的情境。與主鍵索引不同，二級索引不要求資料唯一，但可顯著提升特定列的查詢效率。

前提條件

Hologres執行個體為V4.0及以上版本。如果您的執行個體是V4.0以下版本請參見執行個體升級。

使用限制

全域二級索引列僅支援 TEXT、INTEGER、BIGINT 和 VARCHAR 資料類型。
不支援修改全域二級索引。
索引列和INCLUDE列不允許重複列。
原表必須有主鍵。
原表不支援設定time_to_live_in_seconds參數。
全域二級索引的索引列和與 INCLUDE 列之和最多 512 列。
僅普通內表支援建立全域二級索引。物理分區表和邏輯分區表都不支援建立全域二級索引。
全域二級索引中的列，原表不支援刪除和修改列。
建立了全域二級索引的原表不支援修改Table Group和Resharding。
全域二級索引預設只支援使用標準儲存（熱存）。
全域二級索引的儲存方式（行存、列存、行列共存）和原表一致，詳情如下：
- 原表使用行存，那麼它的全域二級索引預設使用行存。
- 原表使用列存，那麼它的全域二級索引預設使用列存。
- 原表使用行列共存，那麼它的全域二級索引預設使用行列共存。

建立全域二級索引

文法

CREATE GLOBAL INDEX [ IF NOT EXISTS ] index_name
  ON [schema_name.]table_name (index_column_name [, ...])
  [ INCLUDE (include_column_name[, ...]) ]

參數說明

參數	是否必填	說明
index_name	是	全域二級索引的索引名稱。
schema_name	否	原表的Schema名稱。若未填寫，將使用預設的Schema名稱。
table_name	是	原表的表名。
index_column_name	是	全域二級索引的索引列。推薦設定為非主鍵點查的過濾列。
include_column_name	否	全域二級索引中需要包含的列。

使用說明
- 提交建立SQL後，系統將開始構建索引。待索引構建完成且可見後，CREATE GLOBAL INDEX才會執行完成。
- 由於構建索引相當於寫入了多份資料，因此構建索引會影響寫入效能。原表的資料量越多，寫入效能受到的影響越大；此外，索引中包含的列越多，影響亦越大。
- 在建立全域二級索引時，不支援指定Schema，所建立的全域二級索引將與原表位於同一Schema。
- 查詢中涉及的列，全域二級索引必須要完全覆蓋（出現在索引列或者包含列中），才能使用索引。

刪除全域二級索引

文法
```
DROP INDEX [schema_name.]index_name
```
參數說明
參數名稱
是否必填
說明
schema_name
否
局二級索引的Schema名稱。若未填寫，將使用預設Schema。
index_name
是
全域二級索引的索引名稱。

查看全域二級索引

查看當前資料庫下的全域二級索引

SELECT 
    n.nspname AS table_namespace,
    t.relname AS table_name,
    i.relname AS index_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_am am ON am.oid = i.relam
JOIN
    pg_namespace n ON n.oid = t.relnamespace 
WHERE 
    t.relkind = 'r'  -- 只查普通表
    AND am.amname = 'globalindex'

查看全域二級索引佔用的儲存
其中global_index_name為全域二級索引的索引名稱。
```
SELECT pg_relation_size('schema_name.global_index_name');
```

查看全域二級索引包含的列

SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

使用樣本

假設希望訂單應用程式需要高頻查詢某種訂單優先順序的資料的功能。樣本orders表如下所示：

欄位名	類型	含義
O_ORDERKEY	BIGINT	訂單號（主鍵）。
O_CUSTKEY	INT	客戶編碼（外部索引鍵關聯CUSTOMER表）。
O_ORDERSTATUS	CHAR(1)	訂單狀態（''F''=已完成, ''O''=未完成, ''P''=處理中）。
O_TOTALPRICE	DECIMAL(15,2)	訂單總金額。
O_ORDERDATE	DATE	訂單建立日期。
O_ORDERPRIORITY	TEXT	訂單優先順序（''1-URGENT'', ''2-HIGH''等）。
O_CLERK	TEXT	處理訂單的員工編號。
O_SHIPPRIORITY	INT	發貨優先順序（數值越大優先順序越高）。
O_COMMENT	TEXT	訂單備忘資訊。

建立樣本orders表的SQL如下所示。

CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   CHAR(1)         NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
) WITH (
  orientation='row,column',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);
COMMENT ON TABLE ORDERS IS '訂單主表，記錄訂單基本資料和狀態';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS '訂單號（主鍵）';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS '客戶編碼（外部索引鍵關聯CUSTOMER表）';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS '訂單狀態（''F''=已完成, ''O''=未完成, ''P''=處理中）';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS '訂單總金額';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS '訂單建立日期';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS '訂單優先順序（''1-URGENT'', ''2-HIGH''等）';
COMMENT ON COLUMN ORDERS.O_CLERK IS '處理訂單的員工編號';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS '發貨優先順序（數值越大優先順序越高）';
COMMENT ON COLUMN ORDERS.O_COMMENT IS '訂單備忘資訊';

若需要高頻查詢某種訂單優先順序的資料的SQL，如下所示：

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

您可以使用EXPLAIN進行SQL語句執行計畫檢查：

EXPLAIN
SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

返回結果如下。

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

通過增加O_ORDERPRIORITY列的索引，進行更加高效的查詢。
基於上述返回的計劃中查看上述查詢使用了Bitmap Index。由於Bitmap Index對於查詢效能的提升較為有限，若您需要追求更高的QPS，可以在 orders表增加O_ORDERPRIORITY列的索引。
```
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
  );
```
添加索引後，再次運行 EXPLAIN 語句檢查執行計畫：
```
QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0
```
此時可以看到計劃中Index Scan using Clustering_index on的對象已經是全域二級索引idx_orders了，且使用了Shard裁剪了，此時QPS可以得到有效提升。
使用Fixed Plan，更進一步提升QPS。
```
SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
```
查看執行計畫，可以看到使用Fixed Plan的點查最佳化。

參數名稱	是否必填	說明
schema_name	否	局二級索引的Schema名稱。若未填寫，將使用預設Schema。
index_name	是	全域二級索引的索引名稱。