在DRDS模式資料庫下如何建立和使用全域二級索引功能 - PolarDB

本文將詳細介紹在DRDS模式資料庫下如何建立和使用全域二級索引（Global Secondary Index，簡稱GSI）功能。

說明

本文的內容同樣適用於AUTO模式資料庫，建立文法請參見CREATE INDEX（AUTO模式文法）。

GSI文法結構說明

PolarDB-X對MySQL DDL文法進行了擴充，增加定義GSI的文法。使用方式與在MySQL上建立索引一致。

建表時定義GSI
建表後添加GSI

說明

索引名：GSI的名字。
主表名：GSI的宿主表。
索引列：GSI的分庫分表鍵，即索引分庫分表子句中用到的所有列。
覆蓋列：GSI中的其他列，預設包含主鍵和主表的全部分庫分表鍵。
全域二級索引分庫分表子句：GSI的分庫分表演算法，與CREATE TABLE中分庫分表子句的文法一致。
上述是在DRDS模式下的建立GSI文法。如果是在AUTO模式下，請參見CREATE TABLE（AUTO模式）。

約束項

建立GSI約束項

不支援在單表或廣播表上建立GSI。
不支援在UNIQUE GSI中通過任何方式使用首碼索引。
建立GSI時必須指定索引名。
建立GSI時必須指定分庫或分庫加分表組合的規則，不允許僅指定分表規則或不指定任何拆分規則。
GSI的索引列必須包含全部拆分鍵。
GSI定義子句中，索引列與覆蓋列不可重複。
GSI預設包含主表的全部主鍵和拆分鍵，如果沒有顯式包含在索引列中，預設添加到覆蓋列。
在DRDS模式資料庫中，對主表中的每個局部索引，如果引用的所有列均包含在GSI中，預設添加該局部索引到GSI。
對GSI的每個索引列，如果沒有已經存在的索引，預設單獨建立一個索引。
對包含多個索引列的GSI，預設建立一個聯合局部索引，包含所有索引列。
索引定義中，索引列的Length參數僅用於在GSI拆分鍵上建立局部索引。
建表後建立GSI時，會在GSI建立結束時自動進行資料校正，只有通過校正，建立GSI的DDL語句才能執行成功。
說明
您也可以使用CHECK GLOBAL INDEX對索引資料進行校正或訂正。

ALTER TABLE語句約束項

語句	是否支援變更主表拆分鍵	是否支援變更主表主鍵（也即GSI主鍵）	是否支援變更本地唯一索引列	是否支援變更GSI拆分鍵	是否支援變更Unique Index列	是否支援變更索引列	是否支援變更覆蓋列
ADD COLUMN	無該情境	不支援	無該情境	無該情境	無該情境	無該情境	無該情境
ALTER COLUMN SET DEFAULT和ALTER COLUMN DROP DEFAULT	支援	支援	支援	支援	支援	支援	支援
CHANGE COLUMN	不支援	不支援	支援	不支援	支援*	支援*	支援*
DROP COLUMN	不支援	不支援	僅當唯一鍵中只有1列時支援	不支援	支援*	支援*	支援*
MODIFY COLUMN	支援*（僅支援AUTO模式）	支援*	支援	支援*（僅支援AUTO模式）	支援*	支援*	支援*

說明

支援*：表示僅滿足支援無鎖列類型變更條件的執行個體。
考慮到全域二級索引的穩定性和效能情況，目前禁止直接使用DROP COLUMN命令刪除全域二級索引中的列。如需刪除全域二級索引中的某些列，您可以先使用DROP INDEX刪除對應的全域二級索引，再重新建立一個新的二級索引，或聯絡我們進行支援人員。
以上對列的分類存在重疊（如索引列包含全域二級索引拆分鍵，覆蓋列包含主表拆分鍵、主鍵以及指定的列），若存在支援情況衝突情況，不支援的優先順序高於支援。

ALTER TABLE語句變更索引的支援情況：

語句	是否支援
ALTER TABLE ADD PRIMARY KEY	支援
ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	支援，您可以同時在主表和全域二級索引上添加局部索引，索引名稱不可與GSI重複。
ALTER TABLE ALTER INDEX index_name {VISIBLE \| INVISIBLE}	支援，僅在主表執行（禁止變更GSI狀態）。
ALTER TABLE {DISABLE \| ENABLE} KEYS	支援，僅在主表執行（禁止變更GSI狀態）。
ALTER TABLE DROP PRIMARY KEY	禁止
ALTER TABLE DROP INDEX	僅支援刪除普通索引或全域二級索引。
ALTER TABLE DROP FOREIGN KEY fk_symbol	支援，僅在主表執行。
ALTER TABLE RENAME INDEX	支援

ALTER GSI TABLE文法約束項

不支援在GSI上執行DDL和DML語句。
不支援帶有NODE HINT的DML語句更新主表和GSI。

其他約束項

語句	含有GSI的表是否支援
DROP TABLE	支援
DROP INDEX	支援
TRUNCATE TABLE	支援
RENAME TABLE	支援
ALTER TABLE RENAME	支援

樣本

建立GSI：

-- 建表時定義GSI
CREATE TABLE t_order (
 `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
 `order_id` VARCHAR(20) DEFAULT NULL,
 `buyer_id` VARCHAR(20) DEFAULT NULL,
 `seller_id` VARCHAR(20) DEFAULT NULL,
 `order_snapshot` LONGTEXT DEFAULT NULL,
 `order_detail` LONGTEXT DEFAULT NULL,
 PRIMARY KEY (`id`),
 GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition BY hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition BY hash(`order_id`);
-- 添加GSI
CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`) 
    COVERING(`seller_id`, `order_snapshot`) 
    dbpartition BY hash(`buyer_id`) tbpartition BY hash(`buyer_id`) tbpartitions 3

使用GSI查詢
- 通過HINT指定索引
  可以選擇以下兩種HINT語句中的任意一種指定使用目標索引進行查詢。
  - FORCE INDEX
```
FORCE INDEX({index_name})
```
    樣本：
```
SELECT a.order_id FROM t_order a FORCE INDEX(g_i_seller) WHERE a.buyer_id = 123;
```
  - HINT
```
/*+TDDL:INDEX({table_name/table_alias}, {index_name})*/
```
    樣本：
```
/*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123
```
    說明
    如果查詢需要使用索引中未包含的列，則首先查詢GSI取得所有記錄的主鍵和主表分庫分表鍵，然後回查主表取得缺少列的值，詳細說明請參見INDEX HINT。
- 索引選取查詢樣本：
  對於帶有GSI的主表查詢，PolarDB-X會自動選擇出最佳化器認為代價最低的GSI（只支援覆蓋索引選擇）。
```
EXPLAIN SELECT t_order.id,t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';
```
  執行計畫結果：
```
IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")          
```
  說明
  - 上述SQL查詢的主表為t_order，帶有seller_id等值過濾條件，且同時涉及的id、order_snapshot和seller_id等列被g_i_seller覆蓋。選擇覆蓋索引g_i_seller既可以不回表，又可以明確減少分表的掃描數目（seller_id是g_i_seller的拆分鍵）。
  - 執行計畫結果中可以看到PolarDB-X最佳化器選擇了g_i_seller。
- IGNORE INDEX
  文法：
```
IGNORE INDEX({index_name},...)
```
  樣本：
```
SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';
```
- USE INDEX
  文法：
```
USE INDEX({index_name},...)
```
  樣本：
```
SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';
```

常見問題

建立GSI時，提示錯誤：Does not support create Global Secondary Index on single or broadcast table？

提示此錯誤時，請檢查您操作的資料表是否為單表或廣播表。GSI不支援在單表或廣播表上建立。

單表與廣播表：

單表：
- 單庫單表（DRDS模式）：是指建一張單庫單表的邏輯表，不做任何拆分。
- 單表（AUTO模式）：是指添加關鍵字SINGLE來建立的表（不進行任何分區的表）。
廣播表（DRDS模式）或廣播表（AUTO模式）是指添加關鍵字BROADCAST來建立的表（該表將在所有DN節點上有一份資料完全相同拷貝）。