在DRDS模式数据库下如何创建和使用全局二级索引功能_云原生数据库 PolarDB(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节点上有一份数据完全相同拷贝）。