EsgynDB如何建立索引

EsgynDB中的索引，其本质就是另一张表。这张新的表通过元数据与原表关联起来，并且需要保证数据与原表一致。举个例子来说明：

>>create table t1(c1 int primary key, c2 int, c3 int, c4 int, c5 int);

--- SQL operation complete.
>>create index i1 on t1(c2);

--- SQL operation complete.
>>invoke t1;

-- Definition of Trafodion table TRAFODION.SCH.T1
-- Definition current  Wed Aug 17 17:07:44 2022

  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  , C2                               INT DEFAULT NULL NOT SERIALIZED
  , C3                               INT DEFAULT NULL NOT SERIALIZED
  , C4                               INT DEFAULT NULL NOT SERIALIZED
  , C5                               INT DEFAULT NULL NOT SERIALIZED
  )
  PRIMARY KEY (C1 ASC)

-- Definition of Trafodion index I1
-- Definition current  Wed Aug 17 17:07:44 2022

  (
    C2                               INT DEFAULT NULL NOT SERIALIZED
  , C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
      SERIALIZED
  )
  PRIMARY KEY
  (
    C2 ASC
  , C1 ASC
  )
 ATTRIBUTES ALIGNED FORMAT

--- SQL operation complete.

建立一张表，创建索引，使用invoke命令查看具体信息，可以看到索引上有两列c2,c1,并且是主键组合列。实际这个索引的实现是在hbase中再建立一张表叫做TRAFODION.SCH.I1，c2和c1分别映射到原表的c2和c1上。这样基于c2列的查询就可以先查索引（因为索引表的c2列是主键第一列），然后用查到的c1再回原表查询。具体执行计划如下：

>>explain options 'f' select * from t1 where c2=1;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+000
1    2    3    nested_join                                           1.00E+000
.    .    2    trafodion_vsbb_scan             T1                    1.00E+000
.    .    1    trafodion_index_scan            I1                    1.00E+000

--- SQL operation complete.

建立索引最简单的用法，就是看查询落在哪些列上，就在哪些列上建索引。比如上面的例子，按照c2查询，那么就在c2列上建索引，就可以走索引扫描，性能非常好。但是有一点要注意的是，如果c2列的选择性非常差，那么即使建立了索引也不能走到索引扫描上。比如原表有100万行数据，根据性别=‘男’这个条件进行查询，预期结果会有50万行左右。此时即使在性别这一列上建立了索引，走索引会导致50万次回原表查询，这个效率远远低于直接在原表上全表扫描，于是就不会走到索引上。所以性别是一个不适合建立索引的列。身份证，手机号，会员号等等这些选择性通常很好的列，则是建索引比较好的选择。

建索引时需要注意的第二个问题是，如果有多个查询条件存在，通常要将选择性好的列放在前面，选择性较差的列放在后面。例如如下三条查询语句。通常name选择性比较好，birthday选择性较差。如果建索引时把birthday放在前面，那么只有最后一条查询语句可以走索引，前两条都不可以。但如果把name放在前面，则第一句第三句都可以走到索引，只有第二句不行。这样适用的场景更广。

explain options 'f' select * from t3 where name=?;
explain options 'f' select * from t3 where birthday=?;
explain options 'f' select * from t3 where name=? and birthday=？;

建索引时需要注意的第三个问题时，如果多个查询条件中，有的是等于，有的是大于小于，建索引时一定要让等于排在组合列前面，大于小于排在后面。例如如下的sql语句可以走到索引查询，使用explain命令查看beginkey和endkey（传递给hbase进行扫描的范围）。当等于在前，范围在后时，可以正确产生beginkey和endkey，反之则只有范围条件可以正常生成beginkey和endkey，导致选择性变差，多扫描数据，性能下降，甚至选择性太差时可能无法走到索引扫描。

>>explain options 'f' select * from t2 where name=? and birthday>=?;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

3    .    4    root                                                  1.00E+000
1    2    3    nested_join                                           1.00E+000
.    .    2    trafodion_vsbb_scan             T2                    1.00E+000
.    .    1    trafodion_index_scan            I2                    1.00E+000

--- SQL operation complete.

  begin_key .............. (TRAFODION.SCH.I2.NAME = ?),
                             (TRAFODION.SCH.I2.BIRTHDAY = ?),
                             (TRAFODION.SCH.I2.C1 = <min>)
  end_key ................ (TRAFODION.SCH.I2.NAME = ?),
                             (TRAFODION.SCH.I2.BIRTHDAY = <max>),
                             (TRAFODION.SCH.I2.C1 = <max>)

最后，有些查询条件选择性就是很差，这种sql语句如何加速？

一种是通过修改表为hbase format，走类似于列存的执行计划，不过也要看实际的应用场景，这个在另一篇文章中已经介绍了。

如果需要访问的列少于原表的列，即不是select *查询，也可以针对这种sql语句来建立一个专门的索引，实现加速效果。例如c2列选择性很差，并且c2>?这个条件过滤出非常多行，如果回表查询则会非常慢。但是因为只需要访问c2，c3，建立索引时可以通过with table columns(c3)语法将c3列带上，此时索引中包含所有sql语句需要访问的列，则可以直接在索引上完成查询，不需要回表。那么即使选择性差也是有一些提升的。同时因为索引数据量小于原表数据量，减少了IO，也会带来性能提升。这种索引在实际应用中也是较为常见的。

>>create table t5(c1 int primary key, c2 int, c3 int, c4 int, c5 int, c6 int);

--- SQL operation complete.
>>create index i5 on t5(c2) with table columns(c3);

--- SQL operation complete.
>>explain options 'f' select c2,c3 from t5 where c2>?;

LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
---- ---- ---- --------------------  --------  --------------------  ---------

1    .    2    root                                                  3.29E+001
.    .    1    trafodion_index_scan            I5                    3.29E+001

--- SQL operation complete.