HashData 复合主键

HashData 复合主键

HashData 是支持复合主键的。

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

示例

• 创建一个简单的测试表

  ---create table 
  create table t_coposite_keys (
  id int,
  name varchar,
  age int,
  primary key (id,name));
  

• 查看一下默认创建的索引

  ---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)
  

• 插入一些测试数据，过程就不详细解释了

  ---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';
  

检测复合索引的作用

详细的测试过程和测试结果在下面代码中有展示。在此我们仅仅描述一下测试结果：

• 在 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