Hubble数据库普通索引使用

• 加速对表中数据行的检索而创建的一种分散的存储结构
• 作用：Hubble 数据库的索引可以帮助 SQL 查找数据，而不必遍历表中的每一行，从而提高数据库的性能

数据准备

create table general_index (id int PRIMARY KEY ,name string,tel string,sex string ,index(name));
insert into general_index values(1,'杨洋','1111','男'),(2,'刘亦菲','2222','女');
复制

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


Time: 185ms total (execution 184ms / network 1ms)
hubble@hadoop1:35436/defaultdb> insert into general_index values(1,'杨洋','1111','男'),(2,'刘亦菲','2222','女');
INSERT 2


Time: 49ms total (execution 48ms / network 1ms)

hubble@hadoop1:35436/defaultdb>
复制

索引字段可以插入重复数据

索引字段插入重复数据不会报错，而唯一索引则不能插入重复数据

insert into general_index values(3,'杨洋','1111','男'),(4,'刘亦菲','2222','女');
复制

hubble@hadoop1:35436/defaultdb> insert into general_index values(3,'杨洋','1111','男'),(4,'刘亦菲','2222','女');
INSERT 2


Time: 7ms total (execution 6ms / network 1ms)

hubble@hadoop1:35436/defaultdb>
复制

查看逻辑执行计划

explain select * from general_index where name='杨洋';

hubble@hadoop1:35436/defaultdb> explain select * from general_index where name='杨洋';
                                                             info
------------------------------------------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 1
  │ table: general_index@general_index_pkey
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 4 minutes ago)
        table: general_index@general_index_name_idx
        spans: [/e'\U00006768\U00006D0B' - /e'\U00006768\U00006D0B']

  index recommendations: 1
  1. type: index replacement
     SQL commands: CREATE INDEX ON general_index (name) STORING (tel, sex); DROP INDEX general_index@general_index_name_idx;
(15 rows)


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

hubble@hadoop1:35436/defaultdb> explain select name  from general_index where name='杨洋';
                                     info
-------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 1 (100% of the table; stats collected 4 minutes ago)
    table: general_index@general_index_name_idx
    spans: [/e'\U00006768\U00006D0B' - /e'\U00006768\U00006D0B']
(7 rows)


Time: 2ms total (execution 2ms / network 0ms)

hubble@hadoop1:35436/defaultdb>
复制