如何创建和使用全局二级索引功能_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB-X 1.0支持全局二级索引，本文将介绍如何创建、使用全局二级索引功能。

前提条件

MySQL版本需为5.7或以上，且内核小版本需为5.4.1或以上。

使用限制

关于创建和使用GSI的相关限制，请参见使用全局二级索引时的注意事项。

创建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';