Hubble数据库Hash-sharded索引

Hubble数据库Hash-sharded索引

• Hubble数据库处理一个必须按顺序键建立索引的表，则应该使用散列索引。哈希索引将顺序流量均匀分布在不同的范围内，消除了单一范围的热点，并以较小的读性能代价提高了顺序键索引上的写性能
• 哈希索引是哈希分区的实现，而不是哈希索引

数据准备

create table hash_index (id int PRIMARY KEY ,name string,tel string,sex string ,remark jsonb,index(name) using hash);
insert into hash_index values(1,'杨x','1111','男',jsonb '{"id":"1","name": "杨x","tel": "1111","sex": "男","birthdate":"1991-9-9"}' ),(2,'刘x','2222','女',jsonb '{"id":"2","name": "刘x","tel": "2222","sex": "女","birthdate":"1987-8-25"}' ); 

hubble@hadoop1:35436/defaultdb> create table hash_index (id int PRIMARY KEY ,name string,tel string,sex string ,remark jsonb,index(name) using hash);
CREATE TABLE


Time: 92ms total (execution 89ms / network 3ms)
hubble@hadoop1:35436/defaultdb> insert into hash_index values(1,'杨x','1111','男',jsonb '{"id":"1","name": "杨x","tel": "1111","sex": "男","birthdate":"1991-9-9"}' ),(2,'刘x','2222','女',jsonb '{"id":"2","name": "刘x","tel": "2222","sex": "女","birthdate":"1987-8-25"}' ); 
INSERT 2


Time: 12ms total (execution 12ms / network 1ms)

数据查询

select * from hash_index where name='刘x';
hubble@hadoop1:35436/defaultdb> select * from hash_index where name='刘x';
  id |  name  | tel  | sex |                                                           remark
-----+--------+------+-----+------------------------------------------------------------------------------------------------------------------------------
   2 | 刘x | 2222 | 女  | {"birthdate": "1987-8-25", "id": "2", "name": "\xe5\x88\x98\xe4\xba\xa6\xe8\x8f\xb2", "sex": "\xe5\xa5\xb3", "tel": "2222"}
(1 row)


Time: 4ms total (execution 2ms / network 1ms)

查看索引、查看列

show index from hash_index;
show columns from hash_index;
hubble@hadoop1:35436/defaultdb> show index from hash_index;
  table_name |     index_name      | non_unique | seq_in_index |           column_name           | direction | storing | implicit
-------------+---------------------+------------+--------------+---------------------------------+-----------+---------+-----------
  hash_index | hash_index_name_idx |    true    |            1 | hubbledb_internal_name_shard_16 | ASC       |  false  |   true
  hash_index | hash_index_name_idx |    true    |            2 | name                            | ASC       |  false  |  false
  hash_index | hash_index_name_idx |    true    |            3 | id                              | ASC       |  false  |   true
  hash_index | hash_index_pkey     |   false    |            1 | id                              | ASC       |  false  |  false
  hash_index | hash_index_pkey     |   false    |            2 | name                            | N/A       |  true   |  false
  hash_index | hash_index_pkey     |   false    |            3 | tel                             | N/A       |  true   |  false
  hash_index | hash_index_pkey     |   false    |            4 | sex                             | N/A       |  true   |  false
  hash_index | hash_index_pkey     |   false    |            5 | remark                          | N/A       |  true   |  false
(8 rows)


Time: 46ms total (execution 45ms / network 1ms)

hubble@hadoop1:35436/defaultdb> show columns from hash_index;
            column_name           | data_type | is_nullable | column_default |                  generation_expression                  |                indices                | is_hidden
----------------------------------+-----------+-------------+----------------+---------------------------------------------------------+---------------------------------------+------------
  id                              | INT8      |    false    | NULL           |                                                         | {hash_index_name_idx,hash_index_pkey} |   false
  name                            | STRING    |    true     | NULL           |                                                         | {hash_index_name_idx,hash_index_pkey} |   false
  tel                             | STRING    |    true     | NULL           |                                                         | {hash_index_pkey}                     |   false
  sex                             | STRING    |    true     | NULL           |                                                         | {hash_index_pkey}                     |   false
  remark                          | JSONB     |    true     | NULL           |                                                         | {hash_index_pkey}                     |   false
  hubbledb_internal_name_shard_16 | INT8      |    false    | NULL           | mod(fnv32(hubbledb_internal.datums_to_bytes(name)), 16) | {hash_index_name_idx}                 |   true
(6 rows)


Time: 127ms total (execution 126ms / network 1ms)

hubble@hadoop1:35436/defaultdb> 

执行计划

hubble@hadoop1:35436/defaultdb> explain select * from hash_index where name='刘x';
                                             info
----------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 1
  │ table: hash_index@hash_index_pkey
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 16 minutes ago)
        table: hash_index@hash_index_name_idx
        spans: [/4/e'\U00005218\U00004EA6\U000083F2' - /4/e'\U00005218\U00004EA6\U000083F2']

  index recommendations: 1
  1. type: index creation
     SQL command: CREATE INDEX ON hash_index (name) STORING (tel, sex, remark);
(15 rows)


Time: 3ms total (execution 2ms / network 1ms)

####### 转载 Hubble数据库Hash-sharded索引