explain select count(1) from alarm_record;1 row selected (0.07 seconds)0: jdbc:phoenix:192.168.10.10:2181> select count(1) from alarm_record;+-----------+| COUNT(1) |+-----------+| 13091 |+-----------+1 row selected (0.065 seconds)0: jdbc:phoenix:192.168.10.10:2181> explain select count(1) from alarm_record;+------------------------------------------------------------+-----------------+----------------+--------------+| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |+------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER ALARM_RECORD | null | null | null || SERVER FILTER BY FIRST KEY ONLY | null | null | null || SERVER AGGREGATE INTO SINGLE ROW | null | null | null |+------------------------------------------------------------+-----------------+----------------+--------------+3 rows selected (0.02 seconds)---0: jdbc:phoenix:192.168.10.10:2181> explain select * from alarm_record where row_key = '1001820331580712319000' ;+------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |+------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+| CLIENT 1-CHUNK 1 ROWS 1365 BYTES PARALLEL 1-WAY ROUND ROBIN POINT LOOKUP ON 1 KEY OVER ALARM_RECORD | 1365 | 1 | 0 |+------------------------------------------------------------------------------------------------------+-----------------+----------------+--------------+
执行计划 explain 详情
[]: http://phoenix.apache.org/explainplan.html[]: explain命令, 来查看这个SQL准备要做些什么1. 将要执行的所有HBase查询2. 预估要扫描的字节数3. 预估要遍历的行数4. 收集上述预估信息的时间5. 每次扫描会使用哪个HBase表6. 在客户端与服务端分别执行了哪些操作（排序，合并，扫描，limit限制）1. 除了必要的客户端操作外, 大多数操作应该尽可能的都在服务端, 因为服务端是一个集群, 处理速度快, 而客户端只是一个单独的机器.2. 尽可能使用RANGE SCAN或SKIP SCAN而不是TABLE SCAN, 也就是最好不要出现全表扫描3. 查询SQL中, where条件最好是包含所有的主键, 最次也要包含前几个, 前几个不能省略, 否则会大大的降低性能, 出现全表扫描的情况.4. 最好使用索引查询, 索引可以明显的提升查询速度5. 如果你的SQL包含了索引字段, 但是Phoenix协处理器并没有在执行SQL的时候使用索引, 可以强制指定索引, 语法为:SELECT /*+ INDEX(my_table my_index) */ v2 FROM my_table WHERE v1 = 'foo'
AGGREGATE INTO ORDERED DISTINCT ROWS使用诸如加法之类的操作聚合返回的行。使用有GROUP BY子句的聚合函数将结果聚合为不同的多行AGGREGATE INTO SINGLE RO使用没有GROUP BY子句的聚合函数将结果聚合为单行。例如，count（）语句返回一行，其中包含与查询匹配的总行数。CLIENT操作将在客户端执行。在服务器端执行大多数操作的速度更快，因此您应该考虑是否有办法重写SQL查询语句, 以便让更多的操作都在服务器执行, 提高查询效率.FILTER BY 表达式仅返回与表达式匹配的结果, 就是过滤器- FULL SCAN OVER tableName全表扫描, 出现了这种情况, 就要修改SQL了, 不然性能会很慢INNER-JOIN该操作将在满足连接条件的行上连接多个表MERGE SORT对结果执行合并排序RANGE SCAN OVER tableName [ … ]方括号中的信息表示查询中使用的每个主键的开始和停止。EST_BYTES_READ预估扫描的总字节数EST_ROWS_READ预估扫描的总行数EST_INFO_TS收集统计信息的时间点(毫秒), 其实就是时间戳
单主键查询
0: jdbc:phoenix:192.168.10.10:2181> select count(1) from alarm_record;+-----------+| COUNT(1) |+-----------+| 275960 |+-----------+1 row selected (1.567 seconds)0: jdbc:phoenix:192.168.10.10:2181> explain select count(1) from alarm_record;+----------------------------------------------------------------------------------------+-----------------+----------------+----------------+| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |+----------------------------------------------------------------------------------------+-----------------+----------------+----------------+| CLIENT 1-CHUNK 230456 ROWS 314572800 BYTES PARALLEL 1-WAY FULL SCAN OVER ALARM_RECORD | 314572800 | 230456 | 1585213754232 || SERVER FILTER BY FIRST KEY ONLY | 314572800 | 230456 | 1585213754232 || SERVER AGGREGATE INTO SINGLE ROW | 314572800 | 230456 | 1585213754232 |+----------------------------------------------------------------------------------------+-----------------+----------------+----------------+3 rows selected (0.025 seconds)0: jdbc:phoenix:192.168.10.10:2181> select * from alarm_record where alarm_type_id = 36 and vehicle_id in ('V100182') and start_time < TO_TIMESTAMP('2020-03-27 00:00:00') limit 10;+-------------------------+-------------+----------------+--------------------------+--------------------------+--------------------------+------------+------------------+-----------------+----------------+--------------+| ROW_KEY | VEHICLE_ID | ALARM_TYPE_ID | CREATE_DATE | START_TIME | END_TIME | TIME_SPAN | START_LONGITUDE | START_LATITUDE | END_LONGITUDE | END_LATITUDE |+-------------------------+-------------+----------------+--------------------------+--------------------------+--------------------------+------------+------------------+-----------------+----------------+--------------+| 1001820361582523029000 | V100182 | 36 | 2020-02-24 09:42:50.000 | 2020-02-24 05:43:49.000 | | 0 | 120.352538 | 32.499 | 0.0 | 0.0 || 1001820361582529123000 | V100182 | 36 | 2020-02-24 12:55:34.000 | 2020-02-24 07:25:23.000 | | 0 | 120.341108 | 32.43488 | 0.0 | 0.0 || 1001820361582535148000 | V100182 | 36 | 2020-02-24 16:03:32.000 | 2020-02-24 09:05:48.000 | | 0 | 120.341025 | 32.435011 | 0.0 | 0.0 || 1001820361582541133000 | V100182 | 36 | 2020-02-24 18:30:58.000 | 2020-02-24 10:45:33.000 | | 0 | 120.352527 | 32.498905 | 0.0 | 0.0 || 1001820361582541144000 | V100182 | 36 | 2020-02-24 18:31:10.000 | 2020-02-24 10:45:44.000 | | 0 | 120.352527 | 32.498905 | 0.0 | 0.0 || 1001820361582541205000 | V100182 | 36 | 2020-02-24 18:32:22.000 | 2020-02-24 10:46:45.000 | | 0 | 120.352527 | 32.498905 | 0.0 | 0.0 || 1001820361582586275000 | V100182 | 36 | 2020-02-25 01:51:10.000 | 2020-02-24 23:17:55.000 | | 0 | 120.264727 | 32.67873 | 0.0 | 0.0 || 1001820361582592885000 | V100182 | 36 | 2020-02-25 05:23:17.000 | 2020-02-25 01:08:05.000 | | 0 | 120.35285 | 32.499086 | 0.0 | 0.0 || 1001820361582592892000 | V100182 | 36 | 2020-02-25 05:23:28.000 | 2020-02-25 01:08:12.000 | 2020-02-27 09:39:58.000 | 203506 | 120.35285 | 32.499086 | 120.352819 | 32.49913 || 1001820361582796851000 | V100182 | 36 | 2020-02-27 12:25:01.000 | 2020-02-27 09:47:31.000 | 2020-02-27 21:02:12.000 | 40481 | 120.352819 | 32.49913 | 120.35278 | 32.499038 |+-------------------------+-------------+----------------+--------------------------+--------------------------+--------------------------+------------+------------------+-----------------+----------------+--------------+10 rows selected (0.203 seconds)0: jdbc:phoenix:192.168.10.10:2181> explain select * from alarm_record where alarm_type_id = 36 and vehicle_id in ('V100182') and start_time < TO_TIMESTAMP('2020-03-27 00:00:00') limit 10;+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+| CLIENT 1-CHUNK 230456 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER ALARM_RECORD | 314572800 | 230456 | 1585213754232 || SERVER FILTER BY (ALARM_TYPE_ID = 36 AND VEHICLE_ID = 'V100182' AND START_TIME < DATE '2020-03-27 00:00:00.000') | 314572800 | 230456 | 1585213754232 || SERVER 10 ROW LIMIT | 314572800 | 230456 | 1585213754232 || CLIENT 10 ROW LIMIT | 314572800 | 230456 | 1585213754232 |+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+4 rows selected (0.048 seconds)
- 单主键删除
0: jdbc:phoenix:192.168.10.10:2181> explain DELETE FROM alarm_record WHERE row_key = '1001820361582523029000';
+--------------------+-----------------+----------------+--------------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+--------------------+-----------------+----------------+--------------+
| DELETE SINGLE ROW | 0 | 0 | 0 |
+--------------------+-----------------+----------------+--------------+
1 row selected (0.031 seconds)
0: jdbc:phoenix:192.168.10.10:2181> explain DELETE FROM alarm_record WHERE row_key = '1001820361582523029000';
+--------------------+-----------------+----------------+--------------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+--------------------+-----------------+----------------+--------------+
| DELETE SINGLE ROW | 0 | 0 | 0 |
+--------------------+-----------------+----------------+--------------+
1 row selected (0.028 seconds)
0: jdbc:phoenix:192.168.10.10:2181> DELETE FROM alarm_record WHERE row_key = '1001820361582523029000';
1 row affected (0.072 seconds)
- 非主键删除
0: jdbc:phoenix:192.168.10.10:2181> explain DELETE FROM alarm_record_01 WHERE row_key = '1001820361582523029000';
+---------------------------------------------------------------+-----------------+----------------+--------------+
| PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS |
+---------------------------------------------------------------+-----------------+----------------+--------------+
| DELETE ROWS | null | null | null |
| CLIENT 1-CHUNK PARALLEL 1-WAY FULL SCAN OVER ALARM_RECORD_01 | null | null | null |
| SERVER FILTER BY ROW_KEY = '1001820361582523029000' | null | null | null |
+---------------------------------------------------------------+-----------------+----------------+--------------+
3 rows selected (0.025 seconds)
0: jdbc:phoenix:192.168.10.10:2181> DELETE FROM alarm_record_01 WHERE row_key = '1001820361582523029000';
1 row affected (0.951 seconds)
若有收获，就点个赞吧
0 人点赞
