phoenix 排序优化，order by

0: jdbc:phoenix:192.168.199.154> select email from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' order by email desc limit 1;
+-----------------+
|      EMAIL      |
+-----------------+
| zrq@999999.com  |
+-----------------+
1 row selected (0.018 seconds)



0: jdbc:phoenix:192.168.199.154> explain select email from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' order by email desc limit 1;
+------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
|                                                       PLAN                                                       | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
+------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK 1 ROWS 634 BYTES SERIAL 1-WAY REVERSE RANGE SCAN OVER T_EXTENSION_ALL_DATAS_LOGIN ['2018-11-24']  | 634             | 1              | 0            |
|     SERVER FILTER BY FIRST KEY ONLY                                                                              | 634             | 1              | 0            |
|     SERVER 1 ROW LIMIT                                                                                           | 634             | 1              | 0            |
| CLIENT 1 ROW LIMIT                                                                                               | 634             | 1              | 0            |
+------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
4 rows selected (0.022 seconds)

倒序排序走：SERVER FILTER BY FIRST KEY ONLY，就会很快。

因为这张表的row key是，login_date + email，所以根据login_date条件，加上email排序是很快的。

 

 

比如我想要根据时间字段倒序排序，怎么处理呢？

select login_date,email,created_date from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' and country='China' order by created_date desc limit 1;

执行结果是：

Error: org.apache.phoenix.exception.PhoenixIOException: org.apache.hadoop.hbase.DoNotRetryIOException: T_EXTENSION_ALL_DATAS_LOGIN,,1543044197700.fcde65017c2514f6502514900dba5fe2.: null
    at org.apache.phoenix.util.ServerUtil.createIOException(ServerUtil.java:114)
    at org.apache.phoenix.util.ServerUtil.throwIOException(ServerUtil.java:80)
    at org.apache.phoenix.iterate.RegionScannerFactory$1.nextRaw(RegionScannerFactory.java:213)
    at org.apache.phoenix.iterate.RegionScannerResultIterator.next(RegionScannerResultIterator.java:61)
    at org.apache.phoenix.iterate.OrderedResultIterator.getResultIterator(OrderedResultIterator.java:255)
    at org.apache.phoenix.iterate.OrderedResultIterator.next(OrderedResultIterator.java:199)
    at org.apache.phoenix.iterate.NonAggregateRegionScannerFactory.getTopNScanner(NonAggregateRegionScannerFactory.java:322)
    at org.apache.phoenix.iterate.NonAggregateRegionScannerFactory.getRegionScanner(NonAggregateRegionScannerFactory.java:168)
    at org.apache.phoenix.coprocessor.ScanRegionObserver.doPostScannerOpen(ScanRegionObserver.java:81)
    at org.apache.phoenix.coprocessor.BaseScannerRegionObserver$RegionScannerHolder.overrideDelegate(BaseScannerRegionObserver.java:225)
    at org.apache.phoenix.coprocessor.BaseScannerRegionObserver$RegionScannerHolder.nextRaw(BaseScannerRegionObserver.java:273)
    at org.apache.hadoop.hbase.regionserver.RSRpcServices.scan(RSRpcServices.java:3119)
    at org.apache.hadoop.hbase.regionserver.RSRpcServices.scan(RSRpcServices.java:3356)
    at org.apache.hadoop.hbase.shaded.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java:42002)
    at org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:409)
    at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:130)
    at org.apache.hadoop.hbase.ipc.RpcExecutor$Handler.run(RpcExecutor.java:324)
    at org.apache.hadoop.hbase.ipc.RpcExecutor$Handler.run(RpcExecutor.java:304)
Caused by: java.lang.NullPointerException
    at org.apache.phoenix.execute.TupleProjector.projectResults(TupleProjector.java:282)
    at org.apache.phoenix.iterate.RegionScannerFactory$1.nextRaw(RegionScannerFactory.java:203)
    ... 15 more (state=08000,code=101)

 

我们来为他，创建一个索引，带排序的。

CREATE INDEX IDX_T_EXTENSION_ALL_DATAS_LOGIN_DATE_COUNTRY_CREATED ON T_EXTENSION_ALL_DATAS_LOGIN(LOGIN_DATE,COUNTRY,CREATED_DATE DESC);

 

0: jdbc:phoenix:192.168.199.154> select login_date,email,created_date from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' and country='China' order by created_date desc limit 1;
+-------------+-----------------+----------------------+
| LOGIN_DATE  |      EMAIL      |     CREATED_DATE     |
+-------------+-----------------+----------------------+
| 2018-11-24  | zrq@500000.com  | 2018-11-24 15:26:57  |
+-------------+-----------------+----------------------+
1 row selected (0.032 seconds)



0: jdbc:phoenix:192.168.199.154> explain select login_date,email,created_date from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' and country='China' order by created_date desc limit 1;
+------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
|                                                                   PLAN                                                                   | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
+------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
| CLIENT 1-CHUNK 1 ROWS 65 BYTES SERIAL 1-WAY RANGE SCAN OVER IDX_T_EXTENSION_ALL_DATAS_LOGIN_DATE_COUNTRY_CREATED ['2018-11-24','China']  | 65              | 1              | 0            |
|     SERVER FILTER BY FIRST KEY ONLY                                                                                                      | 65              | 1              | 0            |
|     SERVER 1 ROW LIMIT                                                                                                                   | 65              | 1              | 0            |
| CLIENT 1 ROW LIMIT                                                                                                                       | 65              | 1              | 0            |
+------------------------------------------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
4 rows selected (0.018 seconds)

创建带排序的索引后，也是走SERVER FILTER BY FIRST KEY ONLY，速度快~

 

注意：

order by 字段 desc limit 10;

order by 的字段，必须要求是 数字或者时间。

0: jdbc:phoenix:192.168.199.154> select email from T_EXTENSION_ALL_DATAS_LOGIN where login_date='2018-11-24' order by email asc limit 10;
+-----------------+
|      EMAIL      |
+-----------------+
| wjc@1.com       |
| wjc@10.com      |
| wjc@100.com     |
| wjc@1000.com    |
| wjc@10000.com   |
| wjc@100000.com  |
| wjc@100001.com  |
| wjc@100002.com  |
| wjc@100003.com  |
| wjc@100004.com  |
+-----------------+