phoenix 有时候不走二级索引 会 更快 /*+ NO_INDEX*/

0: jdbc:phoenix:192.168.199.154> select t1.seq_id as seq_id from (select seq_id,toy from T_EXTENSION_SHOW where show_date='2018-11-24' and email='wjc@45555.com') t1 where t1.toy='ambi' and t1.cam_site='cam4' order by t1.seq_id desc limit 1;
+---------+
| SEQ_ID  |
+---------+
| 45555   |
+---------+
1 row selected (0.663 seconds)


0: jdbc:phoenix:192.168.199.154> explain select t1.seq_id as seq_id from (select seq_id,toy from T_EXTENSION_SHOW where show_date='2018-11-24' and email='wjc@45555.com') t1 where t1.toy='ambi' and t1.cam_site='cam4' order by t1.seq_id desc limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
|                                                                    PLAN                                                                    | EST_BYTES_READ  | EST_ROWS_READ  |  EST_INFO_TS   |
+--------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
| CLIENT 1-CHUNK 4309216 ROWS 314572800 BYTES PARALLEL 1-WAY RANGE SCAN OVER IDX_T_EXTENSION_SHOW_CAM_SITE_TOY ['2018-11-24','cam4','ambi']  | 314572800       | 4309216        | 1543840687850  |
|     SERVER FILTER BY FIRST KEY ONLY AND "EMAIL" = 'wjc@45555.com'                                                                          | 314572800       | 4309216        | 1543840687850  |
|     SERVER 1 ROW LIMIT                                                                                                                     | 314572800       | 4309216        | 1543840687850  |
| CLIENT 1 ROW LIMIT                                                                                                                         | 314572800       | 4309216        | 1543840687850  |
+--------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
4 rows selected (0.082 seconds)

分析上面的sql，查询条件中email是最细粒度，查出来就1条数据。row key是由show_date + email + seq_id组成的。

phoenix自动优化，使用了二级索引IDX_T_EXTENSION_SHOW_CAM_SITE_TOY ，反而降低了查询效率。

 

实际上我不希望sql走二级索引，希望SQL走自带的row key，进行查询。可以这样做。

0: jdbc:phoenix:192.168.199.154> select /*+ NO_INDEX*/ t1.seq_id as seq_id from (select seq_id,toy from T_EXTENSION_SHOW where show_date='2018-11-24' and email='wjc@45555.com') t1 where t1.toy='ambi' and t1.cam_site='cam4' order by t1.seq_id desc limit 1;
+---------+
| SEQ_ID  |
+---------+
| 45555   |
+---------+
1 row selected (0.046 seconds)


0: jdbc:phoenix:192.168.199.154> explain select /*+ NO_INDEX*/ t1.seq_id as seq_id from (select seq_id,toy from T_EXTENSION_SHOW where show_date='2018-11-24' and email='wjc@45555.com') t1 where t1.toy='ambi' and t1.cam_site='cam4' order by t1.seq_id desc limit 1;
+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
|                                                         PLAN                                                          | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK 0 ROWS 0 BYTES PARALLEL 1-WAY REVERSE RANGE SCAN OVER T_EXTENSION_SHOW ['2018-11-24','wjc@45555.com']  | 0               | 0              | null         |
|     SERVER FILTER BY (TOY = 'ambi' AND CAM_SITE = 'cam4')                                                             | 0               | 0              | null         |
|     SERVER 1 ROW LIMIT                                                                                                | 0               | 0              | null         |
| CLIENT 1 ROW LIMIT                                                                                                    | 0               | 0              | null         |
+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
4 rows selected (0.126 seconds)

结果集够小的情况，FILTER BY很快。

0.663s VS 0.046s

 

END