小小林熬夜学编程

这个屌丝很懒，什么也没留下！

热门标签

字节二面：你对Phoenix二级索引有了解吗？_hbase 和 phoenix 主键对应

作者：小小林熬夜学编程 | 2024-06-01 07:45:15

踩

hbase 和 phoenix 主键对应

Hbase默认只提供了基于字典序排序的rowKey索引，当使用rowKey来进行数据查询的时候速度较快，但是如果使用非rowKey来查询的话就会使用filter来对全表进行全表扫描操作，很大程度上降低了检索性能。针对Hbase rowKey之外的条件查询效率低问题，可以通过Phoenix提供的二级索引技术来解决这种检索的场景。

一、phoenix二级索引概念

Phoenix可以理解成HBase的开源SQL皮肤。通过操作SQL语句可以完美地降低学习Hbase语法的成本。可以使用标准JDBC API代替HBase客户端API来创建表、插入数据和查询数据。

Phoenix的一级索引是它的主键(对应Hbase中的rowkey，这个是默认的机制)，二级索引是主键索引之外，根据表的具体情况创建的其他索引。创建二级索引的目的就是为了加快Hbase地查询速度。

二、phoenix二级索引原理

HBase在0.92之后引入了协处理器，添加了一些新的特性，能够轻易建立二级索引、实现访问控制。Phoenix的二级索引其实就是使用了大量的协处理器来实现的。phoenix二级索引其实是在hbase额外建了一张表，该表数据无需我们维护,phoenix自动维护。Phoenix二级索引在写入数据时会针对创建的字段和rowkey进行绑定，查询时先根据这个字段查询到对应的rowkey，然后根据rowkey查询数据，二级索引也可以理解为查询数据时多次使用索引的情况。类似于ES的倒排索引，不同的是ES的倒排索引是针对全文分词的索引，二级索引是针对表字段的索引。

1. phoenix中创建测试表

create table if not exists student_info(

stu_id varchar primary key,

stu_name varchar ,

birthday varchar ,

sex varchar

) COLUMN_ENCODED_BYTES=0;

2. 添加测试数据

upsert into student_info values('001','WangWu','1995-04-30','男');

upsert into student_info values('002','ZhaoLiu','1998-08-28','男');

upsert into student_info values('003','MaSu','1993-09-10','男');

upsert into student_info values('004','TianQi','1992-11-12','男');

upsert into student_info values('005','ZhangSan','1993-01-14','女');

upsert into student_info values('006','LiSi','1990-06-18','女');

upsert into student_info values('007','KeLe','1992-07-04','女');

upsert into student_info values('008','XiaoMi','1993-09-24','女');

upsert into student_info values('009','BaiTao','1994-08-31','女');

upsert into student_info values('010','SuYa','1991-03-01','女');

3. 通过tables命令查看phoenix中所有表

0: jdbc:phoenix:> !tables

+------------+--------------+---------------+---------------+----------+------------+----------------------------+-----------------+--------------+-----------------+---------------+---------------+-----------+

4. primarykeys命令查看主键

0: jdbc:phoenix:> !primarykeys student_info

+------------+--------------+---------------+--------------+----------+----------+--------------+------------+------------+--------------+----------+----------------+

+------------+--------------+---------------+--------------+----------+----------+--------------+------------+------------+--------------+----------+----------------+

|| | STUDENT_INFO | STU_ID |1| | A | 12 | VARCHAR | null |12| |

+------------+--------------+---------------+--------------+----------+----------+--------------+------------+------------+--------------+----------+----------------+

5. indexes命令查看索引

0: jdbc:phoenix:> !indexes student_info

+------------+--------------+-------------+-------------+------------------+-------------+-------+-------------------+--------------+--------------+--------------+--------+-------------------+------------+---+

6. 注意事项

Phoenix二级索引需要Hbase的配置支持，需要在HBase的各HRegionserver节点的hbase-site.xml文件中添加如下配置：

<name>hbase.regionserver.wal.codec</name>

<value>org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec</value>

</property>

<name>hbase.region.server.rpc.scheduler.factory.class</name>

<value>org.apache.hadoop.hbase.ipc.PhoenixRpcSchedulerFactory</value>

<description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>

</property>

<name>hbase.rpc.controllerfactory.class</name>

<value>org.apache.hadoop.hbase.ipc.controller.ServerRpcControllerFactory</value>

<description>Factory to create the Phoenix RPC Scheduler that uses separate queues for index and metadata updates</description>

</property>

如果hbase-site.xml文件中没有配制如上信息，在phoenix中创建二级索引将会出现如下异常信息：

0: jdbc:phoenix:> CREATE INDEX idx_student_name ON student_info(stu_name);

Error: ERROR 1029 (42Y88): Mutable secondary indexes must have the hbase.regionserver.wal.codec property set to org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec in the hbase-sites.xml of every region server. tableName=IDX_STUDENT_NAME (state=42Y88,code=1029)

java.sql.SQLException: ERROR 1029 (42Y88): Mutable secondary indexes must have the hbase.regionserver.wal.codec property set to org.apache.hadoop.hbase.regionserver.wal.IndexedWALEditCodec in the hbase-sites.xml of every region server. tableName=IDX_STUDENT_NAME

at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494)

at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)

at org.apache.phoenix.schema.MetaDataClient.createIndex(MetaDataClient.java:1528)

at org.apache.phoenix.compile.CreateIndexCompiler$1.execute(CreateIndexCompiler.java:85)

at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:408)

at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:391)

at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)

at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:390)

at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:378)

at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1825)

at sqlline.Commands.execute(Commands.java:822)

at sqlline.Commands.sql(Commands.java:732)

at sqlline.SqlLine.dispatch(SqlLine.java:813)

at sqlline.SqlLine.begin(SqlLine.java:686)

at sqlline.SqlLine.start(SqlLine.java:398)

at sqlline.SqlLine.main(SqlLine.java:291)

phoenix的二级索引分为四种，有全局索引、本地索引、覆盖索引、函数索引。

三、全局索引

全局索引适合那些读多写少的场景。如果使用全局索引，读数据基本不损耗性能，所有的性能损耗都来源于写数据。数据表的添加、删除和修改都会更新相关的索引表（数据删除了，索引表中的数据也会删除；数据增加了，索引表的数据也会增加）。而查询数据的时候，Phoenix会通过索引表来快速低损耗地获取数据。

1. 没有二级索引检索性能

默认情况下，phoenix只提供主键即一级索引，除主键外如果你的查询语句中还有除主键之外的列的时候，Phoenix不会使用索引，而是使用Filter进行全表扫描。

0: jdbc:phoenix:> explain select stu_id,stu_name from student_info where stu_name = 'WangWu';

+------------------------------------------------------------------------+-----------------+----------------+--------------+

+------------------------------------------------------------------------+-----------------+----------------+--------------+

+------------------------------------------------------------------------+-----------------+----------------+--------------+

2 rows selected (0.049 seconds)

2. 创建全局二级索引

0: jdbc:phoenix:> create index idx_student_name on student_info(stu_name) ;

10 rows affected (6.695 seconds)

3. indexes命令查看索引

0: jdbc:phoenix:> !indexes student_info;

+------------+--------------+---------------+-------------+------------------+-------------------+-------+-------------------+--------------+--------------+--------------+--------+-------------------+--------+

|| | STUDENT_INFO | true || IDX_STUDENT_NAME |3| 1 |0:STU_NAME| A || || 12 |

| | | STUDENT_INFO | true | | IDX_STUDENT_NAME | 3 | 2 | :STU_ID | A | | | | 12 |

4. hbase shell的list命令查看表

#在hbase shell中通过list命令可以看到phoenix创建的全局索引就是hbase中的一张表

hbase(main):009:0> list

TABLE

IDX_STUDENT_NAME

STUDENT_INFO

SYSTEM.CATALOG

SYSTEM.FUNCTION

SYSTEM.LOG

SYSTEM.MUTEX

SYSTEM.SEQUENCE

SYSTEM.STATS

8 row(s)

Took 0.0458 seconds

=> ["IDX_STUDENT_NAME", "STUDENT_INFO", "SYSTEM.CATALOG", "SYSTEM.FUNCTION", "SYSTEM.LOG", "SYSTEM.MUTEX", "SYSTEM.SEQUENCE", "SYSTEM.STATS"]

5. scan查看hbase表内容

#在hbase shell中，通过scan查看全局索引表IDX_STUDENT_NAME中的内容

hbase(main):011:0> scan "IDX_STUDENT_NAME"

ROW COLUMN+CELL

BaiTao\x00009 column=0:_0, timestamp=1673778191981, value=x

KeLe\x00007 column=0:_0, timestamp=1673778191981, value=x

LiSi\x00006 column=0:_0, timestamp=1673778191981, value=x

MaSu\x00003 column=0:_0, timestamp=1673778191981, value=x

SuYa\x00010 column=0:_0, timestamp=1673778191981, value=x

TianQi\x00004 column=0:_0, timestamp=1673778191981, value=x

WangWu\x00001 column=0:_0, timestamp=1673778191981, value=x

XiaoMi\x00008 column=0:_0, timestamp=1673778191981, value=x

ZhangSan\x00005 column=0:_0, timestamp=1673778191981, value=x

ZhaoLiu\x00002 column=0:_0, timestamp=1673778191981, value=x

10 row(s)

Took 0.0274 seconds

6. 验证全局索引性能

再次执行，由全表扫描变成范围检索，提高查询效率：

0: jdbc:phoenix:> explain select stu_id,stu_name from student_info where stu_name = 'WangWu';

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

2 rows selected (0.059 seconds)

需要注意的是，当查询的时候出现非索引字段时，还会变成FULL SCAN的全表扫描，因为非索引字段不在rowkey中，索引中没有它所以无法走索引。

0: jdbc:phoenix:> explain select stu_id,stu_name,birthday,sex from student_info where stu_name = 'WangWu';

+------------------------------------------------------------------------+-----------------+----------------+--------------+

+------------------------------------------------------------------------+-----------------+----------------+--------------+

+------------------------------------------------------------------------+-----------------+----------------+--------------+

2 rows selected (0.037 seconds)

四、本地索引

本地索引适合写多读少或者存储空间有限的场景。和全局索引一样，Phoenix也会在查询的时候自动选择是否使用本地索引。本地索引之所以是本地，只要是因为索引数据和真实数据存储在同一台机器上，这样做主要是为了避免网络数据传输的开销。

1. 创建表

#为了与全局索引好区分，我们重新创建测试表

create table if not exists teacher_info(

tea_id varchar primary key,

tea_name varchar

) COLUMN_ENCODED_BYTES=0;

2. 添加测试数据

upsert into teacher_info values('1001','Immen');

upsert into teacher_info values('1002','Lucy' );

upsert into teacher_info values('1003','Sady' );

upsert into teacher_info values('1004','Coco' );

3. 查看hbase中teacher_info表内容

hbase(main):013:0> scan "TEACHER_INFO"

ROW COLUMN+CELL

1001 column=0:TEA_NAME, timestamp=1673780586772, value=Immen

1001 column=0:_0, timestamp=1673780586772, value=x

1002 column=0:TEA_NAME, timestamp=1673780586808, value=Lucy

1002 column=0:_0, timestamp=1673780586808, value=x

1003 column=0:TEA_NAME, timestamp=1673780586836, value=Sady

1003 column=0:_0, timestamp=1673780586836, value=x

1004 column=0:TEA_NAME, timestamp=1673780588452, value=Coco

1004 column=0:_0, timestamp=1673780588452, value=x

4 row(s)

Took 0.0630 seconds

4. 创建本地索引

0: jdbc:phoenix:> create local index idx_teacher_name on teacher_info(tea_name);

4 rows affected (6.376 seconds)

5. indexes命令查看表索引

0: jdbc:phoenix:> !indexes teacher_info;

| | | TEACHER_INFO | true | | IDX_TEACHER_NAME | 3 | 1 | _INDEX_ID | A | | | | 5 |

| | | TEACHER_INFO | true | | IDX_TEACHER_NAME | 3 | 2 | 0:TEA_NAME | A | | | | 12 |

| | | TEACHER_INFO | true | | IDX_TEACHER_NAME | 3 | 3 | :TEA_ID | A | | | | 12 |

6. 查看hbase中表

通过hbase中的list命令是找不到在phoenix中创建的本地二级索引表的。当通过scan命令查看teacher_info表内容时，会发现多了一些内容。实际上本地索引的数据会与表中的数据放在一起。

hbase(main):020:0> list

TABLE

IDX_STUDENT_NAME

STUDENT_INFO

SYSTEM.CATALOG

SYSTEM.FUNCTION

SYSTEM.LOG

SYSTEM.MUTEX

SYSTEM.SEQUENCE

SYSTEM.STATS

TEACHER_INFO

9 row(s)

Took 0.0226 seconds

=> ["IDX_STUDENT_NAME", "STUDENT_INFO", "SYSTEM.CATALOG", "SYSTEM.FUNCTION", "SYSTEM.LOG", "SYSTEM.MUTEX", "SYSTEM.SEQUENCE", "SYSTEM.STATS", "TEACHER_INFO"]

#查看TEACHER_INFO表中数据

hbase(main):021:0> scan "TEACHER_INFO"

ROW COLUMN+CELL

\x00\x00Coco\x001004 column=L#0:_0, timestamp=1673780588452, value=_0

\x00\x00Immen\x001001 column=L#0:_0, timestamp=1673780586772, value=_0

\x00\x00Lucy\x001002 column=L#0:_0, timestamp=1673780586808, value=_0

\x00\x00Sady\x001003 column=L#0:_0, timestamp=1673780586836, value=_0

1001 column=0:TEA_NAME, timestamp=1673780586772, value=Immen

1001 column=0:_0, timestamp=1673780586772, value=x

1002 column=0:TEA_NAME, timestamp=1673780586808, value=Lucy

1002 column=0:_0, timestamp=1673780586808, value=x

1003 column=0:TEA_NAME, timestamp=1673780586836, value=Sady

1003 column=0:_0, timestamp=1673780586836, value=x

1004 column=0:TEA_NAME, timestamp=1673780588452, value=Coco

1004 column=0:_0, timestamp=1673780588452, value=x

8 row(s)

Took 0.0274 seconds

五、覆盖索引

当全局索引查询出现非索引字段时，还会出现FULL SCAN的全表扫描的场景时，我们可以通过覆盖索引来解决这种场景。即在创建索引时使用关键字include将非索引字段包含过来。

1. 删除idx_student_name索引

#删除之前创建的idx_student_name索引

drop index idx_student_name on student_info;

2. 创建idx_student_name覆盖索引

#重新创建idx_student_name覆盖索引

create index idx_student_name on student_info(stu_name) include(birthday,sex);

3. hbase通过scan查数据

hbase(main):029:0> scan "IDX_TEACHER_NAME"

ROW COLUMN+CELL

Coco\x001004 column=0:0:TEA_AGE, timestamp=1673782115335, value=19

Coco\x001004 column=0:_0, timestamp=1673782115335, value=x

Immen\x001001 column=0:0:TEA_AGE, timestamp=1673782115335, value=19

Immen\x001001 column=0:_0, timestamp=1673782115335, value=x

Lucy\x001002 column=0:0:TEA_AGE, timestamp=1673782115335, value=19

Lucy\x001002 column=0:_0, timestamp=1673782115335, value=x

Sady\x001003 column=0:0:TEA_AGE, timestamp=1673782115335, value=19

Sady\x001003 column=0:_0, timestamp=1673782115335, value=x

4 row(s)

Took 0.0190 seconds

4. 验证覆盖索引

0: jdbc:phoenix:> explain select stu_id,stu_name,birthday,sex from student_info where stu_name = 'WangWu';

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+----------------------------------------------------------------------------------------+-----------------+----------------+--------------+

1 row selected (0.179 seconds)

六、函数索引

函数索引从4.3版本就有，这种索引的内容不局限于列，还能在表达式上建立索引。基于Phoenix封装的函数(function)表达式构建索引，以改函数表达式结果和原rowkey拼接作为索引表rowkey。函数索引的特点是能根据表达式创建索引，适用于对查询表过滤条件是表达式的表创建索引。

1. 删除索引

#删除student_info表之前创建的索引

0: jdbc:phoenix:> drop index idx_student_name on student_info;

2. 创建函数索引

0: jdbc:phoenix:> create index idx_student_fun on student_info(upper(stu_name));

10 rows affected (6.425 seconds)

3. 查看student_info表对应的函数索引

0: jdbc:phoenix:> !indexes student_info;

+------------+--------------+---------------+-------------+------------------+------------------+-------+-------------------+--------------------+--------------+--------------+--------+-------------------+---+

| | | STUDENT_INFO | true | | IDX_STUDENT_FUN | 3 | 1 | : UPPER(STU_NAME) | A | | | | 1 |

| | | STUDENT_INFO | true | | IDX_STUDENT_FUN | 3 | 2 | :STU_ID | A | | | | 1 |

4. 查看IDX_STUDENT_FUN函数索引在hbase 中的表内容

hbase(main):050:0> scan "IDX_STUDENT_FUN"

ROW COLUMN+CELL

BAITAO\x00009 column=0:_0, timestamp=1673792058388, value=x

KELE\x00007 column=0:_0, timestamp=1673792058388, value=x

LISI\x00006 column=0:_0, timestamp=1673792058388, value=x

MASU\x00003 column=0:_0, timestamp=1673792058388, value=x

SUYA\x00010 column=0:_0, timestamp=1673792058388, value=x

TIANQI\x00004 column=0:_0, timestamp=1673792058388, value=x

WANGWU\x00001 column=0:_0, timestamp=1673792058388, value=x

XIAOMI\x00008 column=0:_0, timestamp=1673792058388, value=x

ZHANGSAN\x00005 column=0:_0, timestamp=1673792058388, value=x

ZHAOLIU\x00002 column=0:_0, timestamp=1673792058388, value=x

10 row(s)

Took 0.1377 seconds

5. 验证函数索引

0: jdbc:phoenix:> explain select upper(stu_name) from student_info where upper(stu_name)='BAITAO';

+---------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+---------------------------------------------------------------------------------------+-----------------+----------------+--------------+

+---------------------------------------------------------------------------------------+-----------------+----------------+--------------+

2 rows selected (0.045 seconds)

七、总结

在索引范围上，Phoenix的索引可以分为全局索引和本地索引，两种索引适合的场景不同。全局索引适合多读少写的业务场景，本地索引适用于写操作频繁的场景，可以结合实际业务选择适合的索引方式。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/656694