PolarDB：如何使用全域二級索引

使用限制

在AUTO模式的分區表中，該文檔依然適用。僅建立文法需要參考CREATE TABLE（AUTO模式）。

注意事項

• 建立GSI過程的約束

  • 不支援在單表或廣播表上建立GSI。

  • 不支援在UNIQUE GSI中通過任何方式使用首碼索引。

  • 建立索引表時必須指定索引名。

  • 建立索引表時必須指定分庫或分庫加分表組合的規則，不允許僅指定分表規則或不指定任何拆分規則。

  • 索引表的INDEX列必須包含全部拆分鍵。

  • GSI定義子句中，索引列與覆蓋列不可重複。

  • 索引表預設包含主表的全部主鍵和拆分鍵，如果沒有顯式包含在索引列中，預設添加到覆蓋列。

  • 在DRDS模式資料庫中，對主表中的每個局部索引，如果引用的所有列均包含在索引表中，預設添加該局部索引到索引表。

  • 對GSI的每個索引列，如果沒有已經存在的索引，預設單獨建立一個索引。

  • 對包含多個索引列的GSI，預設建立一個聯合局部索引，包含所有索引列。

  • 索引定義中，索引列的length參數僅用於在索引表拆分鍵上建立局部索引。

  • 建表後建立GSI時，會在GSI建立結束時自動進行資料校正，只有通過校正，建立GSI的DDL語句才能執行成功。

  說明

  您也可以使用CHECK GLOBAL INDEX對索引資料進行校正或訂正。

• Alter Table過程的約束

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

  說明

  以上對列的分類存在重疊（如Index列包含索引表拆分鍵，Covering列包含主表拆分鍵、主鍵以及指定的列），若存在支援情況衝突情況，不支援的優先順序高於支援。

  下表匯總了使用ALTER TABLE語句變更索引的支援情況：

  語句	是否支援
  ALTER TABLE ADD PRIMARY KEY	支援
  ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY	支援，您可以同時在主表和索引表上添加局部索引，索引名稱不可與GSI重複。
  ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE}	禁止
  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	禁止

  說明

  考慮到全域二級索引的穩定性和效能情況，目前禁止直接使用DROP COLUMN命令重新命名全域二級索引。如需修改全域二級索引名，您可以先使用DROP INDEX刪除對應的全域二級索引，再重新建立一個新的二級索引。

• Alter GSI Table的約束條件

  • 不支援在索引表上執行DDL、DML語句。

  • 不支援帶有NODE HINT的DML語句更新主表、索引表。

• 在包含GSI的表上使用其他DDL時的約束

  語句	是否支援
  DROP TABLE	支援
  DROP INDEX	支援
  TRUNCATE TABLE	不支援
  RENAME TABLE	不支援
  ALTER TABLE RENAME	不支援

  說明

  • 考慮主表與索引表的資料一致性，目前禁止執行TRUNCATE TABLE語句 。如需清空主表與索引表資料，您可以使用DELETE語句刪除對應的資料，或強制使用HINT /*+TDDL:CMD_EXTRA(TRUNCATE_TABLE_WITH_GSI=TRUE)*/。

  • 考慮到全域二級索引的穩定性和效能情況，目前禁止直接使用RENAME TABLE或ALTER TABLE RENAME命令重新命名全域二級索引。如需修改全域二級索引名，您可以先使用DROP INDEX刪除全域二級索引，修改表名後再重新建立新的二級索引。

• 在包含GSI的表上使用DML語句時的約束

  • 不支援在索引表上執行DML語句。

  • 在主表上執行DML語句的有如下限制：寫索引失敗後，不允許繼續執行其他語句或提交事務。

  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`),
    UNIQUE KEY `l_i_order` (`order_id`),
    GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
    GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
  
  SET DRDS_TRANSACTION_POLICY='XA';
  INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
  # 失敗
  INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
  # 失敗不允許繼續執行
  INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
  # 失敗後不允許提交事務
  COMMIT;

建立GSI

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

• 建表時定義GSI

• 建表後添加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

GSI建立完成後，可以通過如下方式指定查詢使用的索引表：

• 通過HINT指定索引

  您可以選擇以下兩種HINT語句中的任意一種指定使用目標索引進行查詢。

  • FORCE INDEX({index_name})

    樣本：

    SELECT a.*, b.order_id 
     FROM t_seller a 
       JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id 
     WHERE a.seller_nick="abc";

  • /*+TDDL:INDEX({table_name/table_alias}, {index_name})*/

    樣本：

    /*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123

  說明

  如果查詢需要使用索引中未包含的列，則首先查詢索引表取得所有記錄的主鍵和主表分庫分表鍵，然後回查主表取得缺少列的值，詳細說明請參見INDEX HINT。

• 直接查詢索引表

  如果索引表中包含了查詢需要的所有列，可以直接查詢索引表獲得結果。

• 索引選擇

  對於帶有全域二級索引的主表查詢，PolarDB-X會自動選擇出最佳化器認為代價最低的索引表（目前只支援覆蓋索引選擇）。下面SQL查詢的主表是t_order，帶有seller_id等值過濾條件，同時涉及的id、order_snapshot和seller_id等列被全域二級索引g_i_seller覆蓋。選擇了覆蓋索引g_i_seller既可以不回表，又可以明確減少分表的掃描數目（seller_id是g_i_seller的拆分鍵）。通過EXPLAIN可以看到PolarDB-X最佳化器確實選擇了g_i_seller。

  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` = ?)")          

• IGNORE INDEX與USE INDEX

  您可以通過以下HINT指定最佳化器使用或不使用某些索引。

  • 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({index_name},...)

    樣本：

    SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';