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

前提条件

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

使用限制

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

创建GSI

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

  • 建表时定义GSI1
  • 建表后添加GSI2
说明
  • 索引名:作为索引表的名字,用于创建索引表。
  • 索引列:索引表的分库分表键,即索引分库分表子句中用到的所有列。
  • 覆盖列:索引表中的其他列,默认包含主键和主表的全部分库分表键。
  • 索引分库分表子句:索引表的分库分表算法,与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
说明 更多关于创建二级索引的详情,请参见t1825031.html#multiTask6981

使用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会自动选择出优化器认为代价最低的索引表(目前只支持覆盖索引选择)。

    下面SQL查询的主表是t_order,带有seller_id等值过滤条件,同时涉及的idorder_snapshotseller_id等列被全局二级索引g_i_seller覆盖。选择了覆盖索引g_i_seller既可以不回表,又可以明确减少分表的扫描数目(seller_idg_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';