开发指南—常见问题—如何使用全局二级索引-阿里云开发者社区

开发者社区> -技术小能手-> 正文

开发指南—常见问题—如何使用全局二级索引

简介: PolarDB-X支持全局二级索引,本文将在分库分表语法下介绍如何创建、使用全局二级索引功能。
+关注继续查看

使用限制

  • 如果在分区表下,该文档依然试用,只不过创建语法需要参考CREATE INDEX
  • 关于创建和使用GSI的相关限制,请参考

创建GSI

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

  • 建表时定义GSI1..png
  • 建表后添加GSI

2..png

说明

  • 索引名:作为索引表的名字,用于创建索引表。
  • 索引列:索引表的分库分表键,即索引分库分表子句中用到的所有列。
  • 覆盖列:索引表中的其他列,默认包含主键和主表的全部分库分表键。
  • 索引分库分表子句:索引表的分库分表算法,与CREATE TABLE中分库分表子句的语法一致。
  • 上述是在分库分表下的创建GSI语法,如果是在分区表下GSI语法只需要可以参考CREATE INDEX

示例:


# 建表时定义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
  • 说明 如果查询需要使用索引中未包含的列,则首先查询索引表取得所有记录的主键和主表分库分表键,然后回查主表中取得缺少列的值,详细说明请参见。
  • 直接查询索引表如果索引表中包含了查询需要的所有列,可以直接查询索引表获得结果。
  • 索引选择对于带有全局二级索引的主表查询,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';

注意事项

  • 创建GSI过程的约束
    • 不支持在单表或广播表上创建GSI。
    • 不支持在无主键的表上创建GSI。
    • 不支持在UNIQUE GSI中通过任何方式使用前缀索引。
    • 创建索引表时必须指定索引名。
    • 创建索引表时必须指定分库或分库加分表组合的规则,不允许仅指定分表规则或不指定任何拆分规则。
    • 索引表的INDEX列必须包含全部拆分键。
    • GSI定义子句中,索引列与覆盖列不可重复。
    • 索引表默认包含主表的全部主键和拆分键,如果没有显式包含在索引列中,默认添加到覆盖列。
    • 对主表中的每个局部索引,如果引用的所有列均包含在索引表中,默认添加该局部索引到索引表。
    • 对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不支持不支持支持不支持不支持不支持不支持
  • 说明
    • 考虑到全局二级索引的稳定性和性能情况,目前禁止直接使用DROP COLUMN命令删除全局二级索引中的列。如需删除全局二级索引中的某些列,您可以先使用DROP INDEX删除对应的全局二级索引,再重新创建一个新的二级索引,或提交工单联系技术支持进行删除。
    • 以上对列的分类存在重叠(如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禁止
  • 说明 考虑到全局二级索引的稳定性和性能情况,目前禁止直接使用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;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
函数返回值是否使用引用类型的问题:理解引用、返回值
  在《对象更有用的玻璃罩——常引用》一文中,介绍了对象作为函数的参数时,推荐使用引用的形式。并且,如果实际参数的值不允许改变时,声明为常引用更佳。   在《第8周-任务1-方案3-复数类中运算符重载(与实数运算)》中,又讨论了一个问题,结论是:在类似复数加法运算符重载这样的函数,形式参数用作为常引用最佳,如: friend Complex operator + (const
1141 0
Maven使用常见问题整理
Maven使用常见问题整理  1、更新eclipse的classpath加入新依赖  1、在dependencyManagement里面加入包括版本在内的依赖信息,如:      joda-time    joda-time    1.6.2     2、在同级的dependencies节点里面加入该依赖,如:      joda-time    joda-time     3、使用mvn eclipse:clean删除当前的工程配置文件,并用mvn eclipse:eclipse重新生成。
816 0
开发指南—数据类型—Json类型
PolarDB-X支持JSON类型。 与MySQL不同,PolarDB-X支持的JSON类型暂不支持作为分区键。 详细信息请参见MySQL Json类型。
14 0
静默授权与主动授权区别、使用场景以及常见问题。
静默授权与主动授权对于用户来说的区别 静默授权用户是没有感知的,实际商户是悄悄的就把用户的user_id(PID)获取到 主动授权用户是有感知的并且需要用户去进行点击授权确定按钮的,用户如果不经授权的话,商户是拿不到用户的信息的。
3333 0
如何监控索引的使用?
如何监控索引的使用?     研究发现,oracle数据库使用的索引不会超过总数的25%,或者不易他们期望被使用的方式使用。通过监控数据库索引的使用,释放那些未被使用的索引,从而节省维护索引的开销,优化性能。
682 0
1395
文章
0
问答
来源圈子
更多
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载