使用全域二級索引 - PolarDB

PolarDB-X 1.0支援全域二級索引，本文將介紹如何建立、使用全域二級索引功能。

前提條件

MySQL版本需為5.7或以上，且核心小版本需為5.4.1或以上。

建立GSI

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

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

說明

索引名：作為索引表的名字，用於建立索引表。
索引列：索引表的分庫分表鍵，即索引分庫分表子句中用到的所有列。
覆蓋列：索引表中的其他列，預設包含主鍵和主表的全部分庫分表鍵。
索引分庫分表子句：索引表的分庫分表演算法，與CREATE TABLE中分庫分表子句的文法一致。

樣本

# 建表時定義 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

說明更多關於建立全域二級索引的詳情，請參見CREATE INDEX。

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