文档

全局索引

更新时间:

全局索引(GLOBAL INDEX)是分区表上的一种索引技术,可以创建在分区表的非分区键上,也支持提供唯一约束。

前提条件

支持的PolarDB PostgreSQL版(兼容Oracle)的版本如下:

Oracle 2.0(内核小版本2.0.14.4.0及以上)

说明

您可通过如下语句查看PolarDB PostgreSQL版(兼容Oracle)的内核小版本的版本号:

SHOW polar_version;

背景信息

随着业务数据规模的增长,数据分区作为重要的企业级数据库特性,按维度拆分数据成为减小数据规模的重要手段。分区表可以将一个表按照维度(分区键)拆分为若干个独立的子表,通过对子表的分别管理,达到提高可管理性、整体性能和负载均衡的效果。

PolarDB PostgreSQL版(兼容Oracle)的许多用户大量的使用分区表来管理自己的数据,其中较为典型的用法是以时间为维度对分区进行管理:

  • 分区表使用时间作为分区键。

  • 定期(每周或每月)创建新的子分区,新数据进入新的子分区中。

  • 定期对旧的子分区进行归档,减轻分区表的运维成本。

在以上场景中,时间通常被用作分区键,而一般不会被用作主键或unique ID。因此带来了以下两个问题:

  • 对非分区键的查询,由于无法确定数据位于哪个分区中,将不得不扫描所有的子分区。

  • 对非分区键的数据修改,无法保证在整个分区表中的唯一性。

因此,PolarDB PostgreSQL版(兼容Oracle)提供了全局索引功能。全局索引(Global Index)是一种在分区表上创建的索引。不同于默认在每个子分区上创建的局部索引(Local Index,一个索引对应一个子分区),全局索引通过一个索引来索引整个分区表的数据(一个索引对应多个子分区),从而可以提供非分区键上的全局唯一约束,也可以大幅提升非分区键的查询性能。

使用限制

  • 在带有全局索引的分区表上,依然支持ATTACH/DETACH子分区。

  • 在创建索引的语法中,通过指定GLOBAL关键字来创建全局索引。如果不指定,则默认创建局部索引。

  • 全局索引支持CONCURRENTLY并发创建。

  • 非分区表、带有子分区的子表上不支持创建全局索引。

  • 全局索引不支持表达式索引。

  • 无法在分区表的分区列上创建全局索引。

语法

创建全局索引。

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
[ GLOBAL/LOCAL ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

    示例

    加速非分区键查询

    1. 创建以时间列作为分区键的分区表。

      CREATE TABLE partition_range (
          id INT,
          a INT,
          b INT,
          created_date TIMESTAMP WITHOUT TIME ZONE
      ) PARTITION BY RANGE (created_date);
      
      CREATE TABLE partition_range_part01 PARTITION OF partition_range FOR VALUES FROM (MINVALUE) TO ('2020-01-01 00:00:00');
      CREATE TABLE partition_range_part02 PARTITION OF partition_range FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-02-01 00:00:00');
      CREATE TABLE partition_range_part03 PARTITION OF partition_range FOR VALUES FROM ('2020-02-01 00:00:00') TO ('2020-03-01 00:00:00');
    2. 对该分区表进行非分区键的条件查询。

      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      结果显示如下:将会对所有的子分区进行扫描,无法利用到分区表的分区裁剪功能。

                              QUERY PLAN
      ------------------------------------------------------------
       Append
         ->  Seq Scan on partition_range_part01 partition_range_1
               Filter: (id = 1)
         ->  Seq Scan on partition_range_part02 partition_range_2
               Filter: (id = 1)
         ->  Seq Scan on partition_range_part03 partition_range_3
               Filter: (id = 1)
      (7 rows)
    3. 对该分区表创建局部索引,并重新执行查询。

      CREATE INDEX partition_range_idx_local ON partition_range(id);
      
      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      结果显示如下:由于局部索引建立在每一个子分区上,所以依然需要扫描所有子分区的局部索引。

                                                  QUERY PLAN
      --------------------------------------------------------------------------------------------------
       Append
         ->  Index Scan using partition_range_part01_id_idx on partition_range_part01 partition_range_1
               Index Cond: (id = 1)
         ->  Index Scan using partition_range_part02_id_idx on partition_range_part02 partition_range_2
               Index Cond: (id = 1)
         ->  Index Scan using partition_range_part03_id_idx on partition_range_part03 partition_range_3
               Index Cond: (id = 1)
      (7 rows)

    4. 使用GLOBAL关键字对该分区表创建全局索引,并重新执行查询。

      CREATE INDEX partition_range_idx_global ON partition_range(id) GLOBAL;
      
      EXPLAIN (COSTS OFF) SELECT * FROM partition_range WHERE id = 1;

      结果显示如下:数据库将使用全局索引直接找到数据所在的子分区。

                                    QUERY PLAN
      -----------------------------------------------------------------------
       Global Index Scan using partition_range_idx_global on partition_range
         Index Cond: (id = 1)
      (2 rows)

    非分区键的唯一约束

    依然使用上述示例中的分区表,其分区键为时间created_date,但其真正的唯一约束列为id

    CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id);
    ERROR:  unique constraint on partitioned table must include all partitioning columns
    DETAIL:  UNIQUE constraint on table "partition_range" lacks column "created_date" which is part of the partition key.
    说明

    对非分区键创建局部索引的唯一约束将会报错,要求分区键必须被包含在索引中。

    而对全局索引添加唯一约束则不会有这个限制,如下所示。

    CREATE UNIQUE INDEX partition_range_id_unique_idx ON partition_range(id) GLOBAL;

    性能测试

    使用pgbench工具产生scale为80000的数据,分别创建分区表与非分区表。

    非分区键上的点查性能

    类别

    TPS

    Prepared Statement

    不使用

    使用

    并发数

    1

    32

    64

    1

    32

    64

    普通表

    27,732

    494,433

    430,848

    53,935

    985,880

    886,882

    分区表+局部索引

    367

    4,155

    3,688

    856

    8,742

    6,790

    分区表+全局索引

    19,006

    308,128

    262,941

    45,090

    820,924

    731,557

    非分区键上的TPC-B性能

    说明

    包含了点查和DML。

    类别

    TPS

    Prepared Statement

    不使用

    使用

    并发数

    1

    32

    64

    1

    32

    64

    普通表

    1,115

    51,025

    60,409

    4,822

    90,312

    100,802

    分区表+局部索引

    271

    2,903

    2,524

    550

    5,276

    4,237

    分区表+全局索引

    暂不支持

    4,334

    69,040

    75,232

    结论

    全局索引对分区表的点查和DML能够带来一个数量级的性能提升。