全局索引
最近更新时间: 2025-02-18 16:02:00
TDSQL PG是一个Share Nothing的MPP分布式数据，表数据通过Shard表的分布key散落到多个DN，DN间数据没有冗余。当业务查询或者DML操作有分布key条件时，CN上可以直接路由到目标DN。
全局索引存储了对应索引数据的存储目标节点信息，在业务查询缺少分布key条件时，也可以精确地定位对应索引key的数据在全局中的位置，不必扫描所有DN节点来获取数据，可以减少对资源的消耗，从而提高吞吐量（故全局索引一般创建在非分布key列）。
TDSQL PG全局索引支持的表类型：shard表，分区表。暂不支持复制表。
数据类型支持：能够用于shard列的类型，smallint, integer, bigint, real, double precision, binary_float, binary_double, smallserial, serial, bigserial, OID, boolean, char, name, varchar, text, varchar2, nvchar, oidvector, abstime, reltime, cash, bpchar, raw, byte, date, time, oracledate, timestamp, timestamptz, interval, numeric, jsonb, rid
（bfile，xml，bytea不支持）。
索引类型默认Btree（暂仅支持该类型）。
语法：
create global [unique] index [if not exists] index_name on table_name [using method] (column_name);
示例：
1、创建全局索引
postgres=#
create table gindex_test(a int,b int,c varchar,d numeric,e float);
postgres=#
create global index on gindex_test using btree (b);---查询创建结果
postgres=#
\d+ gindex_test
Table "public.gindex_test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
d | numeric | | | | main | |
e | double precision | | | | plain | |
Indexes:
"gindex_test_b_idx" GLOBAL, btree (b)
Distribute By: SHARD(a)
Location Nodes: ALL DATANODES2、创建全局唯一索引
postgres=#
create global unique index on gindex_test(c);---查询创建结果
postgres=#
\d+ gindex_test
Table "public.gindex_test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
d | numeric | | | | main | |
e | double precision | | | | plain | |
Indexes:
"gindex_test_c_idx" UNIQUE, GLOBAL, btree (c)
"gindex_test_b_idx" GLOBAL, btree (b)
Distribute By: SHARD(a)
Location Nodes: ALL DATANODES3、全局索引列的关联查询
---创建表并插入数据
postgres=#
create table gi_insert1(a int,f1 int,f2 varchar2(20),f3 numeric,f4 float,f5 char(30),f6 nchar(20),f7 raw(20),f8 clob,f9 blob,f10 timestamp with local time zone,f11 binary_double,f12 binary_float,f13 real,f14 nclob,f15 date,f16 timestamp,f17 interval day to second, f18 interval year to month,f19 xmltype,f20 long) ;
postgres=#
insert into gi_insert1 select t,t,t,t,t,to_char(t),to_char(t),hextoraw(t),to_char(t),to_char(t)::blob, timestamp '2021-06-22 16:02:07.067029'+t*interval '1' day, sin(t),t,t,to_clob(to_char(t)),date '2021-06-22 16:02:07'+ t*interval '1' day,timestamp '2021-06-22 16:02:07.067029'+t*interval '1' day, t*interval '1' day,interval '3-2' year to month,'<ROW>XMLType2</ROW>' ,to_char(t) from generate_series(1,10)t;
postgres=#
create table gi_insert(a int,f1 int,f2 varchar2(20),f3 numeric,f4 float,f5 char(30),f6 nchar(20),f7 raw(20),f8 clob,f9 blob,f10 timestamp with local time zone,f11 binary_double,f12 binary_float,f13 real,f14 nclob,f15 date,f16 timestamp,f17 interval day to second, f18 interval year to month,f19 xmltype,f20 long) ;
postgres=#
insert into gi_insert select t,t,t,t,t,to_char(t),to_char(t),hextoraw(t),to_char(t),to_char(t)::blob, timestamp '2021-06-22 16:02:07.067029'+t*interval '1' day, sin(t),t,t,to_clob(to_char(t)),date '2021-06-22 16:02:07'+ t*interval '1' day,timestamp '2021-06-22 16:02:07.067029'+t*interval '1' day, t*interval '1' day,interval '3-2' year to month,'<ROW>XMLType2</ROW>' ,to_char(t) from generate_series(1,10)t;---创建全局索引
create global index on gi_insert1 using btree (f1);
create global index on gi_insert1 using btree (f2);
create global unique index on gi_insert1 (f3);
create global index on gi_insert1(f4);
create global index if not exists index5 on gi_insert1 using btree (f13);
create global index on gi_insert using btree (f1);
create global index on gi_insert using btree (f2);
create global unique index on gi_insert (f3);
create global index on gi_insert(f4);
create global index if not exists index51 on gi_insert using btree (f13);
---连接查询
select a.f1
from gi_insert a
join gi_insert1 b
on a.f1=b.f2
where a.f3=(select floor(avg(f)) from (select max(decode(f4,4,f4*f4,f4,f4+1,f4+10)) f
from gi_insert
where f19='<ROW>XMLType2</ROW>'
group by f4
having f4>=any (select (case f3 when 1 then 2 when 2 then 3 else 5 end)
from gi_insert1
where f1 in (select f1
from gi_insert
where f13>=any(select t1.f13 from gi_insert t1
left join gi_insert1 t2
on t1.f13=t2.f13
group by t1.f13
)
group by f1,f13
having f1<=(select max(f3)
from (select t1.f3 f3
from gi_insert t1
left join gi_insert1 t2
on t1.f1=t2.f1
group by t1.f3
) a
where f3 >=1
)
)
)
))
group by a.f1
having a.f1>=1;--查询执行结果
f1
----
8
(1 row)4、支持分区表创建全局唯一索引
--创建表
create table t_order_range_20220721_76(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate ) distribute by shard(id);
--创建分区
create table t_order_range_20220721_76_202204 partition of t_order_range_20220721_76(id primary key,userid,product, createdate) for values from ('2022-04-01') to ('2022-05-01');
create table t_order_range_20220721_76_202205 partition of t_order_range_20220721_76(id primary key,userid,product, createdate) for values from ('2022-05-01') to ('2022-06-01');
create table t_order_range_20220721_76_202206 partition of t_order_range_20220721_76(id primary key,userid,product, createdate) for values from ('2022-06-01') to ('2022-07-01');
create table t_order_range_20220721_76_202207 partition of t_order_range_20220721_76(id primary key,userid,product, createdate) for values from ('2022-07-01') to ('2022-08-01');
--插入数据
insert into t_order_range_20220721_76 values(1,7,'123','2022-05-13');
insert into t_order_range_20220721_76 values(2,8,'234','2022-06-13');
insert into t_order_range_20220721_76 values(3,9,'345','2022-07-13');
insert into t_order_range_20220721_76 values(6,10,'456','2022-07-03');
insert into t_order_range_20220721_76 values(4,11,'567','2022-07-11');
insert into t_order_range_20220721_76 values(5,12,'678','2022-05-08');
--create global index
create global index on t_order_range_20220721_76(userid);
select a.* from t_order_range_20220721_76 a, t_order_range_20220721_76_userid_idx_idt b where a.userid = b.indexkey and a.ctid = b.location and a.shardid = b.shard and a.tableoid = b.subtableoid order by id;
--查询结果
id | userid | product | createdate
----+--------+---------+---------------------
1 | 7 | 123 | 2022-05-13 00:00:00
2 | 8 | 234 | 2022-06-13 00:00:00
3 | 9 | 345 | 2022-07-13 00:00:00
4 | 11 | 567 | 2022-07-11 00:00:00
5 | 12 | 678 | 2022-05-08 00:00:00
6 | 10 | 456 | 2022-07-03 00:00:00