HashData 复合主键

HashData 复合主键
HashData 是支持复合主键的。

在创建过程中，会同时创建一个性复合索引。

示例
创建一个简单的测试表

---create table
create table t_coposite_keys (
id int,
name varchar,
age int,
primary key (id,name));
1
2
3
4
5
6
查看一下默认创建的索引

---select index on table t_coposite_keys
hashdata=# select * from pg_indexes where schemaname='public';
schemaname | tablename | indexname | tablespace | indexdef

------------+-----------------+----------------------+------------ +---------------------------------------------------------------
---------------------
public | t_coposite_keys | t_coposite_keys_pkey | | CREATE UNIQUE INDEX t_coposite_keys_pkey ON t_coposite_keys US
ING btree (id, name)
(1 row)
1
2
3
4
5
6
7
8
9
插入一些测试数据，过程就不详细解释了

---create sequence seq_composite_id
CREATE SEQUENCE seq_composite_id START 1;
CREATE SEQUENCE seq_composite_id2 START 1;
---insert test data
insert into t_coposite_keys
select nextval('seq_composite_id'),'A',20 from generate_series(1,1000);

insert into t_coposite_keys
select nextval('seq_composite_id2'),'B',20 from generate_series(1,1000);

update t_coposite_keys set age=10 where id=1 and name='A';
1
2
3
4
5
6
7
8
9
10
11
检测复合索引的作用
详细的测试过程和测试结果在下面代码中有展示。在此我们仅仅描述一下测试结果：

在 where 条件中必须存在复合索引的列，才能使索引在查询中生效
复合索引中字段，在 where 条件中出现的顺序是不会影响查询结果的
where 条件中，可以只存在复合索引中个字段，或者包含个字段在内的多个字段
---test coposite keys
hashdata=# set optimizer=on;
SET
# 通过复合索引的个字段进行查询
hashdata=# explain analyze select * from t_coposite_keys where id=1 and age=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..3.00 rows=1 width=10)
Rows out: 1 rows at destination with 1.490 ms to first row, 1.491 ms to end, start offset by 17 ms.
-> Index Scan using t_coposite_keys_pkey on t_coposite_keys (cost=0.00..3.00 rows=1 width=10)
Index Cond: id = 1
Filter: age = 10
Rows out: 1 rows (seg1) with 0.054 ms to first row, 0.062 ms to end, start offset by 18 ms.
Slice statistics:
(slice0) Executor memory: 346K bytes.
(slice1) Executor memory: 177K bytes avg x 2 workers, 177K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=on
Optimizer status: PQO version 1.694
Total runtime: 18.901 ms
(14 rows)

Time: 56.020 ms

# 通过复合索引的第二个字段进行查询
hashdata=# explain analyze select * from t_coposite_keys where name='A' and age=10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.04 rows=1 width=10)
Rows out: 1 rows at destination with 1.213 ms to end, start offset by 0.380 ms.
-> Table Scan on t_coposite_keys (cost=0.00..431.04 rows=1 width=10)
Filter: name::text = 'A'::text AND age = 10
Rows out: 1 rows (seg1) with 0.238 ms to first row, 0.240 ms to end, start offset by 0.590 ms.
Slice statistics:
(slice0) Executor memory: 346K bytes.
(slice1) Executor memory: 163K bytes avg x 2 workers, 163K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=on
Optimizer status: PQO version 1.694
Total runtime: 1.876 ms
(13 rows)

Time: 40.551 ms

# 通过复合索引的全部字段，并在 where 条件中进行顺序查询
hashdata=# explain analyze select * from t_coposite_keys where id=1 and name='A' and age=10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..3.00 rows=1 width=10)
Rows out: 1 rows at destination with 1.066 ms to end, start offset by 0.362 ms.
-> Index Scan using t_coposite_keys_pkey on t_coposite_keys (cost=0.00..3.00 rows=1 width=10)
Index Cond: id = 1
Filter: name::text = 'A'::text AND age = 10
Rows out: 1 rows (seg1) with 0.035 ms to first row, 0.039 ms to end, start offset by 0.665 ms.
Slice statistics:
(slice0) Executor memory: 346K bytes.
(slice1) Executor memory: 145K bytes avg x 2 workers, 145K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=on
Optimizer status: PQO version 1.694
Total runtime: 1.735 ms
(14 rows)

Time: 41.391 ms

# 通过复合索引的所有字段，并在 where 条件中进行反序查询
hashdata=# explain analyze select * from t_coposite_keys where name='A' and id=1 and age=10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..3.00 rows=1 width=10)
Rows out: 1 rows at destination with 1.894 ms to first row, 1.982 ms to end, start offset by 20 ms.
-> Index Scan using t_coposite_keys_pkey on t_coposite_keys (cost=0.00..3.00 rows=1 width=10)
Index Cond: id = 1
Filter: name::text = 'A'::text AND age = 10
Rows out: 1 rows (seg1) with 0.076 ms to first row, 0.078 ms to end, start offset by 20 ms.
Slice statistics:
(slice0) Executor memory: 346K bytes.
(slice1) Executor memory: 177K bytes avg x 2 workers, 177K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=on
Optimizer status: PQO version 1.694
Total runtime: 21.805 ms
(14 rows)

Time: 61.371 ms
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
————————————————
版权声明：本文为CSDN博主「GaryZhang000」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zhang50303/article/details/86678067