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