全部产品
云市场

DRDS 全局二级索引使用文档

更新时间:2020-05-22 19:25:03

版本限制:MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1

基本原理

DRDS 全局二级索引 (Global Secondary Index, GSI) 基本原理请参考 DRDS 全局二级索引文档

如何使用

创建

DRDS 对 MySQL DDL 语法进行了扩展,增加定义 GSI 的语法。使用方式与在 MySQL 上创建索引一致:

  • 建表时定义 GSI

Create Table with GSI

  • 建表后添加 GSI

Create GSI

其中:

  • 索引名:作为索引表的名字,用于创建索引表
  • 索引列:索引表的分库分表键,即索引分库分表子句中用到的所有列
  • 覆盖列:索引表中的其他列,默认包含主键和主表的全部分库分表键
  • 索引分库分表子句:索引表的分库分表算法,与 CREATE TABLE 中分库分表子句的语法一致,支持范围参考 CREATE TABLE 文档

示例:

  1. # 建表时定义 GSI
  2. CREATE TABLE t_order (
  3. `id` bigint(11) NOT NULL AUTO_INCREMENT,
  4. `order_id` varchar(20) DEFAULT NULL,
  5. `buyer_id` varchar(20) DEFAULT NULL,
  6. `seller_id` varchar(20) DEFAULT NULL,
  7. `order_snapshot` longtext DEFAULT NULL,
  8. `order_detail` longtext DEFAULT NULL,
  9. PRIMARY KEY (`id`),
  10. GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition by hash(`seller_id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
  12. # 添加 GSI
  13. CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`)
  14. COVERING(`seller_id`, `order_snapshot`)
  15. dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3

注意: 详细说明参考 CREATE TABLE / ALTER TABLE / CREATE INDEX 文档

使用

GSI 创建完成后,可以通过以下两种方式指定查询使用索引表

1. 通过 HINT 指定索引

DRDS 支持指定查询使用某个索引,为了兼容MySQL,包含两种语法

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

示例:

  1. # FORCE INDEX
  2. SELECT a.*, b.order_id
  3. FROM t_seller a
  4. JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id
  5. WHERE a.seller_nick="abc";
  6. # INDEX()
  7. /*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123

注意: 如果查询需要使用索引中未包含的列,则首先查询索引表取得所有记录的主键和主表分库分表键,然后回查主表中取得缺少列的值,详细说明参考 INDEX HINT 文档

2. 直接查询索引表

如果索引表中包含了查询需要的所有列,可以直接查询索引表获得结果

3. 索引选择

对于带有全局二级索引的主表查询,DRDS会自动选择出优化器认为代价最低的索引表。(目前只支持覆盖索引选择)

下面Sql查询的主表是t_order,带有seller_id的等值过滤条件,同时涉及的列id, order_snapshot, seller_id被全局二级索引g_i_seller覆盖。选择了覆盖索引g_i_seller既可以不回表,又可以明确减少分表的扫描数目(seller_id是g_i_seller的拆分键)。通过explain可以看到DRDS优化器确实选择了g_i_seller。

  1. EXPLAIN SELECT t_order.id,t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';
  2. IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")

4. IGNORE INDEX与USE INDEX

通过hint可以告知优化器使用哪些索引,不使用哪些索引

  • IGNORE INDEX({index_name},...)
  • USE INDEX({index_name},...)
  1. // IGNORE INDEX
  2. SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';
  3. // USE INDEX
  4. SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

限制与约定

创建

不支持

  • 不支持 在 MySQL 5.6 版本的 RDS 上创建 GSI
  • 不支持 在单表/广播表上创建 GSI
  • 不支持 在无主键的表上创建 GSI
  • 不支持 索引表中包含 ON UPDATE CURRENT_TIMESTAMP 或 DEFAULT CURRENT_TIMESTAMP 的列
  • 不支持 索引表中包含 类型为TIMESTAMP 且未指定 CURRENT_TIMESTAMP 以外的 DEFAULT VALUE 的列
  • 不支持 在 UNIQUE GSI 中使用前缀索引(指定缀长度)

限制

  • 限制 索引表的 INDEX 列 必须包含全部拆分键
  • 限制 GSI 名称 不可与 主表上的其它局部索引名重复
  • 限制 GSI 定义子句中,索引列与覆盖列不可重复

约定

  • 索引表默认包含主表的全部主键和拆分键,如果没有显式包含在索引列中,默认添加到覆盖列
  • 对主表中的每个局部索引,如果引用的所有列均包含在索引表中,默认添加该局部索引到索引表
  • 对 GSI 的每个索引列,如果没有已经存在的索引,默认单独创建一个索引
  • 对 包含多个索引列的 GSI,默认创建一个联合局部索引,包含所有索引列
  • 索引定义中,索引列的 length 参数仅用于在索引表拆分键上创建局部索引

变更主表

  • ALTER TABLE 变更列的限制
语句 主表拆分键 主表主键
(也是索引表主键)
本地唯一索引列 索引表拆分键 Unique Index 列 Index 列 Covering 列
ADD COLUMN 禁止增加主键
ALTER COLUMN SET DEFAULT
/DROP DEFAULT
禁止 禁止 支持 禁止 禁止 禁止 禁止
CHANGE COLUMN 禁止 禁止 支持 禁止 禁止 禁止 禁止
DROP COLUMN 禁止 禁止 禁止
(唯一键中只有一列时支持)
禁止 禁止1 禁止1 禁止1
MODIFY COLUMN 禁止 禁止 支持 禁止 禁止 禁止 禁止

1 考虑到全局二级索引的稳定性和性能原因,目前禁止删除全局二级索引中的列。如需删除全局二级索引中的某些列,可以先使用 DROP INDEX 删除对应的全局二级索引,再重新创建,或者咨询 DRDS 技术支持。

2 以上对列的分类存在重叠(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} 支持,仅在主表执行(禁止变更 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 禁止1

1 考虑到全局二级索引的稳定性和性能原因,目前禁止重命名主表或全局二级索引。如需修改全局二级索引名或主表名,可以先使用 DROP INDEX 删除全局二级索引,改名后再重新创建,或者咨询 DRDS 技术支持。

变更索引表

  • 不支持 索引表上执行 DDL/DML 语句
  • 不支持 带有 NODE HINT 的 DML 语句更新主表/索引表

其他 DDL

DDL 行为
DROP TABLE 删除主表和索引表
DROP INDEX 删除全局二级索引
TRUNCATE TABLE 禁止1
RENAME TABLE 禁止2
ALTER TABLE RENAME 同 RENAME TABLE

1 考虑主表与索引表的数据一致性,目前禁止执行 TRUNCATE 。如需清空主表与索引表数据,可以使用 DELETE 代替,或者咨询 DRDS 技术支持。

2 考虑到全局二级索引的稳定性和性能原因,目前禁止重命名主表或全局二级索引。如需修改全局二级索引名或主表名,可以先使用 DROP INDEX 删除全局二级索引,改名后再重新创建,或者咨询 DRDS 技术支持。

DML 语句

索引数据校验及订正

  • 建表后创建 GSI 时,会在在创建结束时自动进行数据校验,只有通过校验,创建语句才会返回成功
  • 使用语句 CHECK GLOBAL INDEX 可主动对索引数据进行校验或订正