我在Hbase上用phoenix做了两张桌子。
一个是ORIGIN_LOG,另一个是Original_LOG_INDEX。
在ORIGIN_LOG中,密钥是info_key。在ORIGIN_LOG_INDEX中,密钥是(LOG_t,区域)
我们将log_t、zone、info_key保存在ORIGIN_log_INDEX中,这样我们就可以通过ORIGIN_log_INDEX的log_t和zone快速搜索info_key。然后使用info_key,我们可以通过info_key从ORIGIN_log获得详细的日志信息,因为info_key是ORIGIN_log的密钥。
但当我们解释以下sql时。我们发现它将在ORIGIN_LOG上进行全扫描。
explain select "log_t", "app_ver", "device_id", "mobage_uid", "param1","param2","param3", "param4" , "param5", "user_id", "a_typ", "a_tar", "a_rst" from "ORIGIN_LOG" where "info_key" in (select distinct "info_key" from "ORIGIN_LOG_INDEX" where "log_t">='1423956600' and "log_t"<'1423956601' and "zone" ='18')
CLIENT 4-CHUNK PARALLEL 4-WAY FULL SCAN OVER ORIGIN_LOG
CLIENT MERGE SORT |
| SKIP-SCAN-JOIN TABLE 0 |
| CLIENT 2-CHUNK PARALLEL 2-WAY SKIP SCAN ON 2 RANGES OVER
ORIGIN_LOG_INDEX [0,'1423956600','18'] - [1,'1423956601','18'] |
| SERVER FILTER BY FIRST KEY ONLY |
| SERVER AGGREGATE INTO DISTINCT ROWS BY [info_key] |
| CLIENT MERGE SORT |
| DYNAMIC SERVER FILTER BY info_key IN ($5.$7) |
如果我们只使用条件为LOG_t和zone的ORIGIN_LOG,如下所示:
select "log_t", "app_ver", "device_id", "mobage_uid", "param1","param2","param3", "param4" , "param5", "user_id", "a_typ", "a_tar", "a_rst" from "ORIGIN_LOG" where "log_t">='1423956600' and "log_t"<'1423956601' and "zone" ='18';
我们还可以进行全扫描。
CLIENT 4-CHUNK PARALLEL 4-WAY FULL SCAN OVER ORIGIN_LOG |
| SERVER FILTER BY (log_t >= '1423956600' AND log_t < '1423956601' AND zone = '18') |
| CLIENT MERGE SORT |
那么两个sql之间的区别是什么呢。以及哪种sql的性能更好。
谢谢。
BRs
您的第一个查询是对ORIGIN_LOG_INDEX上的HBase进行基于范围的扫描,然后在ORIGIN_LOG上获取。您的第二个查询是HBase中基于范围的扫描,您将为扫描提供"startkey"one_answers"endkey"。第二个查询要好得多,因为您避免了查找另一个表,而且也不执行不同的操作
但是,startKey和endkey的范围可能跨越整个表。所以,最糟糕的扫描情况是"全表"扫描。因此,我认为,解释计划将其显示为全表扫描。也许,你可以在邮件列表上询问进一步的澄清。