KingbaseES数据库分区表添加主键与索引的建议

最新推荐文章于 2024-09-12 15:26:20 发布
最新推荐文章于 2024-09-12 15:26:20 发布
阅读量445 收藏
点赞数
文章标签： 数据库 oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lyu1026/article/details/133643625
版权

一、初始化测试环境

# 数据库版本信息
KingbaseES V008R006C007B0012 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

1. 创建分区表：

create table tb(id bigint,stat date,no bigint,pdate date,info varchar2(50)) partition by range(pdate) INTERVAL ('1 MONTH'::INTERVAL)
(
    PARTITION tb_p0 VALUES LESS THAN ('2019-01-01'),
    PARTITION tb_p1 VALUES LESS THAN ('2019-02-01'),
    PARTITION tb_p2 VALUES LESS THAN ('2019-03-01'),
    PARTITION tb_p3 VALUES LESS THAN ('2019-04-01'),
    PARTITION tb_p4 VALUES LESS THAN ('2019-05-01')
);

2. 创建随机日期、数字函数：

# 随机数函数
CREATE OR REPLACE FUNCTION RANDOM_NUMBER(INTEGER, INTEGER) RETURNS INTEGER AS
$BODY$
DECLARE
    START_INT ALIAS FOR $1;
    END_INT ALIAS FOR $2;
BEGIN
    RETURN trunc(random() * (END_INT - START_INT + 1) + START_INT);
END;
$BODY$
LANGUAGE plpgsql;

# 随机日期函数
CREATE OR REPLACE FUNCTION RANDOM_DAYS(START_DATE DATE, END_DATE DATE) RETURNS DATE AS
$BODY$
DECLARE
    INTERVAL_DAYS INTEGER;
    RANDOM_DAYS INTEGER;
    RANDOM_DATE DATE;
BEGIN
    INTERVAL_DAYS := END_DATE - START_DATE;
    RANDOM_DAYS := RANDOM_NUMBER(0, INTERVAL_DAYS);
    RANDOM_DATE := START_DATE + RANDOM_DAYS;
    RETURN RANDOM_DATE;
END;
$BODY$
LANGUAGE plpgsql;

3. insert生成数据：

test=# insert into tb select generate_series(1,100000000),clock_timestamp(),RANDOM_NUMBER(1,100000000),RANDOM_DAYS('2019-01-01'::date,'2019-05-31'::date),md5(random()::text) from dual;

INSERT 0 100000000
Time: 2081789.125 ms (34:41.789)
test=# select sys_size_pretty(sys_relation_size('tb_tb_p0'));
 sys_size_pretty 
-----------------
 0 bytes
(1 row)
Time: 36.671 ms
test=# select sys_size_pretty(sys_relation_size('tb_tb_p1'));
 sys_size_pretty 
-----------------
 1980 MB
(1 row)
Time: 0.648 ms
test=# select sys_size_pretty(sys_relation_size('tb_tb_p2'));
 sys_size_pretty 
-----------------
 1788 MB
(1 row)
Time: 0.591 ms
test=# select sys_size_pretty(sys_relation_size('tb_tb_p3'));
 sys_size_pretty 
-----------------
 1981 MB
(1 row)
Time: 0.551 ms
test=# select sys_size_pretty(sys_relation_size('tb_tb_p4'));
 sys_size_pretty 
-----------------
 1916 MB
(1 row)
Time: 0.674 ms
test=# select sys_size_pretty(sys_relation_size('tb_p5'));   
 sys_size_pretty 
-----------------
 1980 MB
(1 row)
Time: 0.668 ms

4. 添加主键：

test=# alter table tb add constraint tb_pk primary key(id,no);                                                                                                                                             
ALTER TABLE
Time: 194581.732 ms (03:14.582)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

5. 创建索引：

# 创建local本地索引
test=# create index on tb(id,stat,no,info);                                                                                                                                                                
CREATE INDEX
Time: 248144.207 ms (04:08.144)

test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_id_stat_no_info_idx" btree (id, stat, no, info)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

test=# \d+ tb_tb_p2
                                           Table "public.tb_tb_p2"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-02-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-03-01 00:00:00'::date)))
Indexes:
    "tb_tb_p2_id_stat_no_info_idx" btree (id, stat, no, info)
Access method: heap

# 使用global创建全局索引
# 查询表信息，发现使用global声明创建的索引也是local本地索引

test=# create index on tb(id,stat) global;  
CREATE INDEX
Time: 151558.143 ms (02:31.558)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_id_stat_idx" btree (id, stat)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

test=# \d+ tb_tb_p4
                                           Table "public.tb_tb_p4"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-04-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-05-01 00:00:00'::date)))
Indexes:
    "tb_tb_p4_id_stat_idx" btree (id, stat)
Access method: heap

# 使用unique加分区键pdate创建全局索引(global声明)
# 查询表信息，创建的索引依然是local本地索引

test=# create unique index ON tb(id,pdate) global;                                                                                                                                                          
CREATE INDEX
Time: 87232.640 ms (01:27.233)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_id_pdate_idx" UNIQUE, btree (id, pdate)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

# 使用unique加非分区键创建全局索引(global声明)
# 查询表信息，全局索引创建成功

test=# create unique index ON tb(id,info) global;
CREATE INDEX
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           |          |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           |          |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_id_info_idx" UNIQUE, btree (id, info) INCLUDE (tableoid) GLOBAL 
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

总结：

1 添加主键的同时会创建主键列(字段)唯一索引(但是有唯一索引的列不一定是主键)。

2 主键字段不允许空值，添加主键过程中会自动添加not null非空约束，保证主键列值的唯一性。

3 分区表添加主键同时会在主键列创建一个全局索引(索引有GLOBAL。

4 在分区表创建索引(默认为local本地索引，全局索引需要global声明)，会自动在每个分区上创建一个本地索引。

5 分区表唯一约束必须包含分区键。

6 分区表成功创建全局索引必须满足条件：索引类型是唯一索引(unique)并且不包含分区键 。

二、通用方法给分区表添加主键、创建索引

1. 设置主键字段非空约束：

最好先设置主键字段非空not null约束，然后再添加主键。

test=# alter table tb alter column id set not null;
ALTER TABLE
Time: 37.610 ms
test=# alter table tb alter column no set not null;
ALTER TABLE
Time: 3.629 ms
test=# alter table tb add constraint tb_pk primary key(id,no);
ALTER TABLE
Time: 161859.979 ms (02:41.860)

test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

2. 设置maintenance_work_mem参数：

test=# set work_mem ='100MB';                                                         
SET
test=# set maintenance_work_mem ='500MB';
SET
test=# set temp_buffers ='100MB';
SET

# 然后设置非空约束not null添加主键
test=# alter table tb alter column id set not null;
ALTER TABLE
Time: 20.714 ms
test=# alter table tb alter column no set not null;
ALTER TABLE
Time: 2.202 ms
test=# alter table tb add constraint tb_pk primary key(id,no);
ALTER TABLE
Time: 170640.065 ms (02:50.640)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

3. 创建索引：

# 创建本地local索引
test=# create index on tb(id,stat,no,info);                                                                                                                                                                
CREATE INDEX
Time: 250377.917 ms (04:10.378)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_id_stat_no_info_idx" btree (id, stat, no, info)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

# 使用global创建全局索引
test=# create index on tb(id,stat) global; 
CREATE INDEX
Time: 160475.453 ms (02:40.475)
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_id_stat_idx" btree (id, stat)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

maintenance_work_mem参数是数据库在进行维护性操作( VACUUM 、 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY )中使用的最大的内存量。

参数作用有以下几点：

  • 影响维护性操作的速度和效率，如果设置得太低，可能导致多次索引扫描或内存溢出到磁盘。
  • 影响自动垃圾回收(autovacuum)的触发和执行，如果设置得太高，可能导致内存不足或垃圾回收延迟。
  • 可以通过SET命令在本地会话中临时增加，以适应不同的维护性操作的需求。

分区表不支持以下添加主键语法：

create unique index tb_idx on tb(id);
alter table tb ADD CONSTRAINT tb_pk PRIMARY KEY using index tb_idx;

三、建议方法

使用on only关键字创建索引：

优点：

  • 创建索引时不会阻塞对分区表的并发插入、更新或者删除(INSERT，UPDATE，DELETE)操作。
  • 可以减少对分区表被锁定的时间。

缺点：

  • 分区表父表不支持CONCURRENTLY、parallel_workers选项，子分区支持CONCURRENTLY、parallel_workers选项。
  • 需要更多的工作量、花费较多的时间才能完成。
1.使用on only创建失效invalid索引，使所有的子分区不会自动应用该索引：
test=# create index tb_idx on only tb(id,stat,no,info);
CREATE INDEX
Time: 21.135 ms
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_idx" btree (id, stat, no, info) INVALID
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')
2.然后手动创建分区表子分区索引：
# 手动给分区表子分区创建索引
test=# create index CONCURRENTLY tb_tb_p0_idx on tb_tb_p0(id,stat,no,info);
CREATE INDEX
Time: 5.469 ms
test=# create index CONCURRENTLY tb_tb_p1_idx on tb_tb_p1(id,stat,no,info);
CREATE INDEX
Time: 50423.600 ms (00:50.424)
test=# create index CONCURRENTLY tb_tb_p2_idx on tb_tb_p2(id,stat,no,info);
CREATE INDEX
Time: 46672.063 ms (00:46.672)
test=# create index CONCURRENTLY tb_tb_p3_idx on tb_tb_p3(id,stat,no,info);
CREATE INDEX
Time: 50621.904 ms (00:50.622)
test=# create index CONCURRENTLY tb_tb_p4_idx on tb_tb_p4(id,stat,no,info);
CREATE INDEX
Time: 49248.159 ms (00:49.248)
test=# create index CONCURRENTLY tb_p5_idx on tb_p5(id,stat,no,info);
CREATE INDEX
Time: 52901.838 ms (00:52.902)

# 然后使用ALTER INDEX .. ATTACH PARTITION到父索引
test=# alter index tb_idx attach partition tb_tb_p0_idx;
ALTER INDEX
Time: 5.326 ms
test=# alter index tb_idx attach partition tb_tb_p1_idx;
ALTER INDEX
Time: 2.688 ms
test=# alter index tb_idx attach partition tb_tb_p2_idx;
ALTER INDEX
Time: 2.908 ms
test=# alter index tb_idx attach partition tb_tb_p3_idx;
ALTER INDEX
Time: 3.608 ms
test=# alter index tb_idx attach partition tb_tb_p4_idx;
ALTER INDEX
Time: 2.332 ms
test=# alter index tb_idx attach partition tb_p5_idx;
ALTER INDEX
Time: 37.518 ms

# 所有的子分区索引都附加到父索引后，父索引将自动标记为有效
test=# \d+ tb
                                        Partitioned table "public.tb"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition key: RANGE (pdate)
Range interval: INTERVAL ('0-1'::pg_catalog.interval)
Indexes:
    "tb_pk" PRIMARY KEY, btree (id, no) INCLUDE (tableoid) GLOBAL 
    "tb_idx" btree (id, stat, no, info)
Partitions: tb_p5 FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00'),
            tb_tb_p0 FOR VALUES FROM (MINVALUE) TO ('2019-01-01 00:00:00'),
            tb_tb_p1 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-02-01 00:00:00'),
            tb_tb_p2 FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00'),
            tb_tb_p3 FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00'),
            tb_tb_p4 FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00')

test=# \d+ tb_tb_p3
                                           Table "public.tb_tb_p3"
 Column |            Type            | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                     |           | not null |         | plain    |              | 
 stat   | date                       |           |          |         | plain    |              | 
 no     | bigint                     |           | not null |         | plain    |              | 
 pdate  | date                       |           |          |         | plain    |              | 
 info   | character varying(50 char) |           |          |         | extended |              | 
Partition of: tb FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00')
Partition constraint: (((pdate IS NULL) OR ((pdate)::timestamp without time zone >= '2019-03-01 00:00:00'::date)) AND ((pdate IS NOT NULL) AND ((pdate)::timestamp without time zone < '2019-04-01 00:00:00'::date)))
Indexes:
    "tb_tb_p3_idx" btree (id, stat, no, info)
Access method: heap
3.总结：

分区表父表不支持CONCURRENTLY、parallel_workers选项，但是子分区支持CONCURRENTLY、parallel_workers选项。

  • 使用on only方式在父表创建失效的索引。
  • 子分区使用CONCURRENTLY选项，手动创建子分区索引。
  • 然后使用ALTER INDEX .. ATTACH PARTITION到父索引，父索引会自动标记为有效。

四、分区表创建索引建议：

1.设置maintenance_work_mem参数：

通过在会话级别(session)设置maintenance_work_mem可以提升创建索引的效率。例如(set maintenance_work_mem='1024MB')

2.使用on only关键字+并发(CONCURRENTLY)进行分区表索引的创建：

优点：在创建索引时不阻塞DML操作，在生产环境进行分区表创建索引不会影响到业务。

缺点：会带来额外 CPU 和 I/O 开销，可能会拖慢其他操作。死锁或者违反唯一约束索引创建将会失败(INVALID)，重新create 或者reindex就可以。

并发创建索引的过程可以分为三个阶段：第一阶段是创建无效的索引并加锁，第二阶段是扫描表中的所有可见元组构建索引并更新标志位，第三阶段是为缺少的元组补充索引并等待所有早于当前快照的事务结束。

Kingbase 研究院
关注
kingbase数据库 SQL优化——索引
txy_1999的博客
12-12 1174
对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。与执行节点的Bitmap索引的区别是：执行节点的Bitmap索引是将计算过程需要的数据以位图的形式进行运算，而索引AM的Bitmap索引是将索引信息压缩存储在磁盘上。
基于Linux系统命令行安装KingbaseES数据库
07-05
### 基于Linux系统命令行安装KingbaseES数据库知识点详解 #### 一、KingbaseES简介 人大金仓KingbaseES是一款具有自主知识产权的关系型数据库管理系统，它提供了丰富的SQL功能，支持多种操作系统平台，包括Linux。...
KingbaseES的索引使用技巧
Leo_Sui的博客
10-01 454
对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。字符串"foo|bar"中的三元模型的集合是 "f"，"fo"，"foo"，"oo"，"b"，"ba"，"bar"和"ar"。由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。最常见的就是创建基于函数的索引。
金仓数据库KingbaseES-索引介绍和使用技巧
最新发布
arthemis_14的博客
09-12 558
在这种极端的情况下，由于每个索引页面的利用率极低，一旦数据量显著增加，将会导致索引文件变得极为庞大，不仅降低了查询效率，而且还存在整个磁盘空间被完全填满的危险。对于重建后的索引还存在另外一个性能上的优势，因为在新建立的索引上，逻辑上相互连接的页面在物理上往往也是连在一起的，这样可以提高磁盘页面被连续读取的几率，从而提高整个操作的IO效率。由于不是在所有的情况下都需要更新索引，只有新增的符合局部索引条件表达式的数据才会写入到索引里，因此局部索引会提高数据插入和数据更新的效率，减少了索引的维护成本。
人大金仓数据库KingbaseES 主键和索引
arthemis_14的博客
11-27 1229
总的来说，主键和索引在sql中都是用于提高查询操作的效率和数据完整性的重要工具。主键用于确保每一行都有一个唯一的标识符，而索引用于加快查询操作的速度。根据具体的需求和情况，选择合适的主键和索引策略可以提高数据库的性能和可靠性。在sql中，主键和索引是通过创建约束来实现的。主键约束用于定义主键，而索引约束用于定义索引。创建索引约束时，可以选择在一个或多个列上创建索引。在sql中，主键（Primary Key）是一种用于唯一标识表中每一行的一列或一组列。它的主要目的是确保表中的每一行都有一个唯一的标识符。
人大金仓数据库KingbaseES 索引使用
arthemis_14的博客
09-18 1150
如果我们想查阅书中的某个知识点，我们是会一页一页翻找还是在书中目录去找呢？我们会先在书的目录中找，然后在看对应页的内容，因为这样能够节省时间加快效率。而索引就是帮助存储系统快速获取信息的一种数据结构，形象的说就是数据的目录。1、索引就是帮助数据库系统来快速访问特定信息的数据结构，可以把它看作数据的目录。2、KES支持Btree索引、Hash索引、GiST索引、SP-GiST索引、GIN索引。3、索引会增加数据库系统开销，并不是越多越好，需要合理规划。4、常用索引方法包括多列索引、唯一索引和表达式索引。
全局索引与分区索引对于SQL性能影响的比较
lyu1026的博客
06-01 984
KingbaseES 提供了对于分区表 global index 的支持。global index 不仅提供了对于唯一索引功能的改进(无需包含分区键)，而且在性能上相比非global index (local index)有很大的提升(无法提供分区条件情况下)。以下举例说明二者在性能方面的差异。 1、准备数据 create table t1(id1 integer,id2 integer,na...
C#访问kingbasees数据库的DEMO，实现C#对国产金仓数据库的访问
10-17
在IT行业中，数据库是数据存储和管理的核心，而金仓数据库（KingbaseES）作为一款国产的高性能关系型数据库管理系统，被广泛应用于政府、金融、电信等领域。C#作为.NET框架的主要开发语言，自然需要与各种数据库进行...
人大金仓KingbaseES 数据库性能调优指南 V8
07-21
### 人大金仓KingbaseES 数据库性能调优指南 V8 #### 一、性能概述 **1.1 什么是性能** 性能是指数据库系统在处理数据时的速度与效率。对于数据库而言，性能主要体现在响应时间、吞吐量、资源利用率等方面。良好...
[系统管理]KingbaseES数据库维护指南.pdf
04-22
通过遵循《[系统管理]KingbaseES数据库维护指南》中的建议，数据库管理员可以有效地管理KingbaseES数据库，确保其长期稳定运行的同时，还能不断提升性能。这些维护任务虽然繁琐，但却是保障数据库高效运行不可或缺的...
KingbaseES_V008R003C002B0160_Win64_install.tar
05-26
版本：V008R003C002B0160 针对于有些用户客户端和服务端版本不一致，服务端版本是V008R003C002B0160 即可以使用此工具
KingbaseES大数据量分区表添加主键与索引
lyu1026的博客
06-06 1086
KingbaseES大数据量分区表添加主键与索引 一、环境信息： 系统信息： $ cat /etc/centos-release CentOS Linux release 8.2.2004 (Core) $ lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Li...
KingbaseES如何更改现有表的主键
lyu1026的博客
04-22 1706
更改主键 假设您必须更改现有表的主键。您可以通过执行以下语句来实现此目的： ALTER TABLE tablename DROP CONSTRAINT tablename_pkey , ADD PRIMARY KEY (column_newkey); 如果主键在其他表中用作外键，则可以添加该关键字。这将自动删除依赖于主键的任何外键：CASCADE ALTER TABLE tablename DR...
kingbase主键自增
yw1290484082的博客
01-08 844
alter table "表名" alter column "主键名" set default。
【金仓数据库设置主键自增】
weixin_45817985的博客
09-14 1665
金仓数据库主键自增
人大金仓 KingBase 表添加自增主键 duplicate key value violates unique constraint
Xiaoweidumpb
01-16 2553
【代码】人大金仓 KingBase 表添加自增主键 duplicate key value violates unique constraint。
kingbase-组合索引
hhy_iiiii的博客
07-30 449
组合索引
KingbaseES 局部索引
lyu1026的博客
09-03 242
一个列要不要建立btree索引，判断条件是其键值分布是否够离散，比如主键、唯一键，可以建立索引。如果这个列有大量重复的值，则建立索引没有意义。 在生产环境中常会碰到键值分布不均匀的列，如表t1有一个名为FLAG的列，有0,1,2三个值，其中值为0的记录占95%，值为1的占3%，2占2%。在FLAG上建立索引，搜索FLAG=1或2可利用到此索引，而搜索FLAG=0 则因有大量的重复值而利用不到此索...
人大金仓KingbaseES数据库Linux安装与使用指南
本指南旨在帮助初学者快速掌握KingbaseES数据库的基本操作，包括安装、卸载、启动与停止数据库、客户端连接以及各种工具的使用。 1. 数据库安装 - 安装前期：在安装前，需要确保系统满足硬件和软件的最低要求，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值