基于Phoenix对HBase建索引

最新推荐文章于 2023-05-14 21:17:24 发布

weixin_30879169

最新推荐文章于 2023-05-14 21:17:24 发布

阅读量228

点赞数

文章标签：大数据

原文链接：http://www.cnblogs.com/ratels/p/11203313.html

版权

参考：

Phoenix与HBase集成进行数据分析

HBase查询速度慢原因排查

操作1，执行查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> SELECT * FROM ASSET_RECORD WHERE ASSET_ID='设345-1149640126759047168';
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
|                ID                 |         ASSET_ID          | MANAGEMENT_TABLE  | INTRODUCTION  |           MANAGEMENT_ID           |        |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
| 0292ebbfdf3e4d97a6e9fc930ed126d4  | 设345-1149640126759047168  | ASSET_SEAL        |               | dd9ff0fc0ad4486bb0812e78fa53ce0e  | 2018-0 |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
1 row selected (0.081 seconds)

操作2，重复以上查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> SELECT * FROM ASSET_RECORD WHERE ASSET_ID='设345-1149640126759047168';
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
|                ID                 |         ASSET_ID          | MANAGEMENT_TABLE  | INTRODUCTION  |           MANAGEMENT_ID           |        |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
| 0292ebbfdf3e4d97a6e9fc930ed126d4  | 设345-1149640126759047168  | ASSET_SEAL        |               | dd9ff0fc0ad4486bb0812e78fa53ce0e  | 2018-0 |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
1 row selected (0.077 seconds)

操作3，使用explain重复以上查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> explain SELECT * FROM ASSET_RECORD WHERE ASSET_ID='设345-1149640126759047168';
+----------------------------------------------------------------------------------------------------+-----------------+----------------+--------+
|                                                PLAN                                                | EST_BYTES_READ  | EST_ROWS_READ  |  EST_I |
+----------------------------------------------------------------------------------------------------+-----------------+----------------+--------+
| CLIENT 1-CHUNK 453274 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ASSET_RECORD  | 314572800       | 453274         | 156335 |
|     SERVER FILTER BY ASSET_ID = '设345-1149640126759047168'                                         | 314572800       | 453274         | 156335 |
+----------------------------------------------------------------------------------------------------+-----------------+----------------+--------+
2 rows selected (0.015 seconds)

操作4，在表上建索引，如下：

0: jdbc:phoenix:node3:2181:/hbase> create index IDX_ASSET_RECORD on ASSET_RECORD(ASSET_ID,MANAGEMENT_TABLE);
17,137 rows affected (6.25 seconds)

操作5，强制使用索引执行查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> SELECT /*+ INDEX(ASSET_RECORD IDX_ASSET_RECORD)*/ * FROM ASSET_RECORD WHERE ASSET_ID='设345-1149640126759047168;
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
|                ID                 |         ASSET_ID          | MANAGEMENT_TABLE  | INTRODUCTION  |           MANAGEMENT_ID           |        |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
| 0292ebbfdf3e4d97a6e9fc930ed126d4  | 设345-1149640126759047168  | ASSET_SEAL        |               | dd9ff0fc0ad4486bb0812e78fa53ce0e  | 2018-0 |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
1 row selected (0.058 seconds)

操作6，强制使用索引执行查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> SELECT /*+ INDEX(ASSET_RECORD IDX_ASSET_RECORD)*/ * FROM ASSET_RECORD WHERE ASSET_ID='设345-1149640126759047168';
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
|                ID                 |         ASSET_ID          | MANAGEMENT_TABLE  | INTRODUCTION  |           MANAGEMENT_ID           |        |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
| 0292ebbfdf3e4d97a6e9fc930ed126d4  | 设345-1149640126759047168  | ASSET_SEAL        |               | dd9ff0fc0ad4486bb0812e78fa53ce0e  | 2018-0 |
+-----------------------------------+---------------------------+-------------------+---------------+-----------------------------------+--------+
1 row selected (0.033 seconds)

操作7，使用explain强制使用索引执行查询，如下：

0: jdbc:phoenix:node3:2181:/hbase> explain SELECT /*+ INDEX(ASSET_RECORD IDX_ASSET_RECORD)*/ * FROM ASSET_RECORD WHERE ASSET_ID='设345-114964012679047168';
+------------------------------------------------------------------------------------------------------------------+-----------------+-----------+
|                                                       PLAN                                                       | EST_BYTES_READ  | EST_ROWS_ |
+------------------------------------------------------------------------------------------------------------------+-----------------+-----------+
| CLIENT 1-CHUNK 453274 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ASSET_RECORD                | null            | null      |
|     SKIP-SCAN-JOIN TABLE 0                                                                                       | null            | null      |
|         CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN RANGE SCAN OVER IDX_ASSET_RECORD ['设345-1149640126759047168']  | null            | null      |
|             SERVER FILTER BY FIRST KEY ONLY                                                                      | null            | null      |
|     DYNAMIC SERVER FILTER BY "ASSET_RECORD.ID" IN ($11.$13)                                                      | null            | null      |
+------------------------------------------------------------------------------------------------------------------+-----------------+-----------+
5 rows selected (0.045 seconds)

操作8，删除索引，如下：

0: jdbc:phoenix:node3:2181:/hbase> drop index IDX_ASSET_RECORD on ASSET_RECORD;
No rows affected (3.688 seconds)

计算操作1和操作2的平均执行时间，建索引后，计算操作5和操作6的平均执行时间，经比较发现使用索引确实提高了查询的速度。

Phoenix具有索引同步更新机制，增删改一条或多条数据以后，索引会自动更新；但是，如果原来的表增加了字段，那就需要更新建在表上的索引。

表的属性越多，条目越多，建索引节约的时间越多，如下是82个属性和195821条记录的表：

0: jdbc:phoenix:node3:2181:/hbase> SELECT COUNT(*) FROM ASSET_NORMAL;
+-----------+
| COUNT(1)  |
+-----------+
| 195821    |
+-----------+
1 row selected (4.54 seconds)
0: jdbc:phoenix:node3:2181:/hbase> create index IDX_ASSET_NORMAL on ASSET_NORMAL(ASSET_ID,ASSET_NAME,USER_ID);
195,821 rows affected (8.887 seconds)
0: jdbc:phoenix:node3:2181:/hbase> SELECT /*+ INDEX(ASSET_NORMAL IDX_ASSET_NORMAL)*/ * FROM ASSET_NORMAL WHERE ASSET_ID='仪1-1151470269278326784';
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
|                ID                 |        ASSET_ID         | ASSET_NAME  | ASSET_FIRST_DEGREE_ID  | ASSET_FIRST_DEGREE_NAME  | ASSET_SECOND_D |
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
| 002e028151e24b07a21e0a0e9ce7f74c  | 仪1-1151470269278326784  | 测量仪器        | 1001001                | 仪表                       | 1001001011     |
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
1 row selected (0.209 seconds)
0: jdbc:phoenix:node3:2181:/hbase> SELECT * FROM ASSET_NORMAL WHERE ASSET_ID='仪1-1151470269278326784';
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
|                ID                 |        ASSET_ID         | ASSET_NAME  | ASSET_FIRST_DEGREE_ID  | ASSET_FIRST_DEGREE_NAME  | ASSET_SECOND_D |
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
| 002e028151e24b07a21e0a0e9ce7f74c  | 仪1-1151470269278326784  | 测量仪器        | 1001001                | 仪表                       | 1001001011     |
+-----------------------------------+-------------------------+-------------+------------------------+--------------------------+----------------+
1 row selected (4.306 seconds)