impala表关联join优化1

基础环境:

impala表关联join优化1

 

 impala表关联join优化1

[cdh02.dongfeng.com:21000] default> show create table kudu_via_city_pdi10;
Query: show create table kudu_via_city_pdi10
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| result                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE default.kudu_via_city_pdi10 (                                                                                                                                                                    |
|   tid BIGINT NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                                 |
|   entry_time BIGINT NOT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                          |
|   pdt INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                                        |
|   longitude STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                               |
|   latitude STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                                |
|   city_code STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                               |
|   city_name STRING NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                               |
|   city_span INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                                  |
|   gps_mileage DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                             |
|   meter_mileage DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                           |
|   ecu_mileage DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                             |
|   diff_mileage DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                            |
|   std_mileage DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                             |
|   total_fuel_cons DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                         |
|   diff_fuel_cons DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                          |
|   std_fuel_cons DOUBLE NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                           |
|   operate_time INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                               |
|   idlepark_time INT NULL ENCODING AUTO_ENCODING COMPRESSION DEFAULT_COMPRESSION,                                                                                                                              |
|   PRIMARY KEY (tid, entry_time)                                                                                                                                                                               |
| )                                                                                                                                                                                                             |
| PARTITION BY RANGE (entry_time) (...)                                                                                                                                                                         |
| STORED AS KUDU                                                                                                                                                                                                |
| TBLPROPERTIES ('STATS_GENERATED'='TASK', 'impala.lastComputeStatsTime'='1638861939', 'kudu.master_addresses'='cdh01.dongfeng.com:7051,cdh02.dongfeng.com:7051,cdh03.dongfeng.com:7051', 'numRows'='16777216') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Fetched 1 row(s) in 0.01s
[cdh02.dongfeng.com:21000] default> show partitions kudu_via_city_pdi10;
Query: show partitions kudu_via_city_pdi10
+--------+------------------+------------------+-------------------------+------------+
| # Rows | Start Key        | Stop Key         | Leader Replica          | # Replicas |
+--------+------------------+------------------+-------------------------+------------+
| -1     | 8000000000000001 | 80000000000F4240 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 80000000000F4240 | 80000000001E8480 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 80000000001E8480 | 80000000002DC6C0 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 80000000002DC6C0 | 80000000003D0900 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 80000000003D0900 | 80000000004C4B40 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 80000000004C4B40 | 80000000005B8D80 | cdh03.dongfeng.com:7050 | 3          |
| -1     | 80000000005B8D80 | 80000000006ACFC0 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 80000000006ACFC0 | 80000000007A1200 | cdh03.dongfeng.com:7050 | 3          |
| -1     | 80000000007A1200 | 8000000000895440 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 8000000000895440 | 8000000000989680 | cdh03.dongfeng.com:7050 | 3          |
| -1     | 8000000000989680 | 8000000000A7D8C0 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 8000000000A7D8C0 | 8000000000B71B00 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 8000000000B71B00 | 8000000000C65D40 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 8000000000C65D40 | 8000000000D59F80 | cdh03.dongfeng.com:7050 | 3          |
| -1     | 8000000000D59F80 | 8000000000E4E1C0 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 8000000000E4E1C0 | 8000000000F42400 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 8000000000F42400 | 8000000001036640 | cdh02.dongfeng.com:7050 | 3          |
| -1     | 8000000001036640 | 800000000112A880 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 800000000112A880 | 800000000121EAC0 | cdh01.dongfeng.com:7050 | 3          |
| -1     | 800000000121EAC0 | 8000000001C9C380 | cdh01.dongfeng.com:7050 | 3          |
+--------+------------------+------------------+-------------------------+------------+
Fetched 20 row(s) in 0.02s
[cdh02.dongfeng.com:21000] default> 

 

问题描述:

两个单独进行查询,查询时间在可控范围内;但是通过表join或者子查询,查询时间立马翻倍。

 

Fetched 10 row(s) in 0.92s
[cdh02.dongfeng.com:21000] default> select tid from kudu_via_city_pdi10 order by entry_time desc limit 10;
Query: select tid from kudu_via_city_pdi10 order by entry_time desc limit 10
Query submitted at: 2021-12-07 18:52:31 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=0e46e0100b1b4e15:e514b7e400000000
+----------+
| tid      |
+----------+
| 16777216 |
| 16711680 |
| 16252928 |
| 15728640 |
| 16515072 |
| 16646144 |
| 12582912 |
| 16760832 |
| 16744448 |
| 14680064 |
+----------+
Fetched 10 row(s) in 0.91s
[cdh02.dongfeng.com:21000] default>      select * from kudu_via_city_pdi10 as a where a.tid in ( 
                                  > 16777216,
                                  > 16711680,
                                  > 16252928,
                                  > 15728640,
                                  > 16515072,
                                  > 16646144,
                                  > 12582912,
                                  > 16760832,
                                  > 16744448,
                                  > 14680064);
Query: select * from kudu_via_city_pdi10 as a where a.tid in (
16777216,
16711680,
16252928,
15728640,
16515072,
16646144,
12582912,
16760832,
16744448,
14680064)
Query submitted at: 2021-12-07 18:53:46 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=074ef45b264f88f7:acf5bb0200000000
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16252928 | 2554       | 16252928 | a         | a        | f68406e3-8a41-4ed6-912c-2919aa03f865 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16515072 | 2554       | 16515072 | a         | a        | 2bef4bc9-f79f-497f-a548-22c619979056 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16646144 | 2554       | 16646144 | a         | a        | d08ea476-f576-4390-9e1c-487a35b71e4f | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16711680 | 2554       | 16711680 | a         | a        | f6be4eb8-3eaf-4091-b4b2-6122c91e7d3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16744448 | 2554       | 16744448 | a         | a        | 46686639-11d1-43de-b35f-bd7cb6310f8b | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16760832 | 2554       | 16760832 | a         | a        | 7db069d2-59e1-46f6-bfde-65b2c5e8ebf4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777216 | 2665       | 16777216 | a         | a        | edb2ef0e-2af5-4edf-99a8-3818dc21dd3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 14680064 | 2554       | 14680064 | a         | a        | 8f70f07f-783a-4e44-8ad9-e860d861fb5e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 15728640 | 2554       | 15728640 | a         | a        | a6ea6399-2daf-4218-b0a9-56c79ea1b491 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 12582912 | 2554       | 12582912 | a         | a        | 182e10b7-6b25-48aa-8ded-6d0247d2e822 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 0.22s[cdh02.dongfeng.com:21000] default> select * from (select tid from kudu_via_city_pdi10 order by entry_time desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid;
Query: select * from (select tid from kudu_via_city_pdi10 order by entry_time desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid
Query submitted at: 2021-12-07 18:54:36 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=044e010c07fbcc4f:bd31316300000000
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16252928 | 16252928 | 2554       | 16252928 | a         | a        | f68406e3-8a41-4ed6-912c-2919aa03f865 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16515072 | 16515072 | 2554       | 16515072 | a         | a        | 2bef4bc9-f79f-497f-a548-22c619979056 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16646144 | 16646144 | 2554       | 16646144 | a         | a        | d08ea476-f576-4390-9e1c-487a35b71e4f | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16711680 | 16711680 | 2554       | 16711680 | a         | a        | f6be4eb8-3eaf-4091-b4b2-6122c91e7d3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16744448 | 16744448 | 2554       | 16744448 | a         | a        | 46686639-11d1-43de-b35f-bd7cb6310f8b | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16760832 | 16760832 | 2554       | 16760832 | a         | a        | 7db069d2-59e1-46f6-bfde-65b2c5e8ebf4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777216 | 16777216 | 2665       | 16777216 | a         | a        | edb2ef0e-2af5-4edf-99a8-3818dc21dd3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 14680064 | 14680064 | 2554       | 14680064 | a         | a        | 8f70f07f-783a-4e44-8ad9-e860d861fb5e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 15728640 | 15728640 | 2554       | 15728640 | a         | a        | a6ea6399-2daf-4218-b0a9-56c79ea1b491 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 12582912 | 12582912 | 2554       | 12582912 | a         | a        | 182e10b7-6b25-48aa-8ded-6d0247d2e822 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 3.12s
[cdh02.dongfeng.com:21000] default> select * from (select tid from kudu_via_city_pdi10 order by entry_time desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid;
Query: select * from (select tid from kudu_via_city_pdi10 order by entry_time desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid
Query submitted at: 2021-12-07 18:54:44 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=e04aec43184c25cb:115de27800000000
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16252928 | 16252928 | 2554       | 16252928 | a         | a        | f68406e3-8a41-4ed6-912c-2919aa03f865 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16515072 | 16515072 | 2554       | 16515072 | a         | a        | 2bef4bc9-f79f-497f-a548-22c619979056 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16646144 | 16646144 | 2554       | 16646144 | a         | a        | d08ea476-f576-4390-9e1c-487a35b71e4f | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16711680 | 16711680 | 2554       | 16711680 | a         | a        | f6be4eb8-3eaf-4091-b4b2-6122c91e7d3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16744448 | 16744448 | 2554       | 16744448 | a         | a        | 46686639-11d1-43de-b35f-bd7cb6310f8b | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16760832 | 16760832 | 2554       | 16760832 | a         | a        | 7db069d2-59e1-46f6-bfde-65b2c5e8ebf4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777216 | 16777216 | 2665       | 16777216 | a         | a        | edb2ef0e-2af5-4edf-99a8-3818dc21dd3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 14680064 | 14680064 | 2554       | 14680064 | a         | a        | 8f70f07f-783a-4e44-8ad9-e860d861fb5e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 15728640 | 15728640 | 2554       | 15728640 | a         | a        | a6ea6399-2daf-4218-b0a9-56c79ea1b491 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 12582912 | 12582912 | 2554       | 12582912 | a         | a        | 182e10b7-6b25-48aa-8ded-6d0247d2e822 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 3.07s
[cdh02.dongfeng.com:21000] default> 

问题分析:

执行计划:

Fetched 10 row(s) in 3.07s
[cdh02.dongfeng.com:21000] default> 
[cdh02.dongfeng.com:21000] default> summary;
+------------------------+--------+----------+----------+--------+------------+----------+---------------+-------------------------------+
| Operator               | #Hosts | Avg Time | Max Time | #Rows  | Est. #Rows | Peak Mem | Est. Peak Mem | Detail                        |
+------------------------+--------+----------+----------+--------+------------+----------+---------------+-------------------------------+
| F03:ROOT               | 1      | 0ns      | 0ns      |        |            | 0 B      | 0 B           |                               |
| 06:EXCHANGE            | 1      | 0ns      | 0ns      | 10     | 10         | 80.00 KB | 16.00 KB      | UNPARTITIONED                 |
| F00:EXCHANGE SENDER    | 3      | 333.34us | 1.00ms   |        |            | 1.42 KB  | 0 B           |                               |
| 03:HASH JOIN           | 3      | 33.67ms  | 101.00ms | 10     | 10         | 2.45 MB  | 1.94 MB       | INNER JOIN, BROADCAST         |
| |--05:EXCHANGE         | 3      | 0ns      | 0ns      | 10     | 10         | 16.00 KB | 16.00 KB      | BROADCAST                     |
| |  F02:EXCHANGE SENDER | 1      | 0ns      | 0ns      |        |            | 22.57 KB | 0 B           |                               |
| |  04:MERGING-EXCHANGE | 1      | 0ns      | 0ns      | 10     | 10         | 16.00 KB | 16.00 KB      | UNPARTITIONED                 |
| |  F01:EXCHANGE SENDER | 3      | 0ns      | 0ns      |        |            | 7.52 KB  | 0 B           |                               |
| |  01:TOP-N            | 3      | 39.33ms  | 118.00ms | 10     | 10         | 32.00 KB | 160 B         |                               |
| |  00:SCAN KUDU        | 3      | 237.00ms | 703.01ms | 16.78M | 16.78M     | 72.00 KB | 6.00 MB       | default.kudu_via_city_pdi10   |
| 02:SCAN KUDU           | 3      | 1.40s    | 2.71s    | 4.19M  | 16.78M     | 2.91 MB  | 54.00 MB      | default.kudu_via_city_pdi10 b |
+------------------------+--------+----------+----------+--------+------------+----------+---------------+-------------------------------+

从执行计划来看,求排序并没有消耗太长时间,时间主要占用在全扫描default.kudu_via_city_pdi10上,占用了2.71秒,

疑点1:是不是impala对于大表和小表之间join性能就是比用in差,但是如果不排序,就很快,所以排除掉是impala自身hash join问题

[cdh02.dongfeng.com:21000] default> select * from (select tid from kudu_via_city_pdi10  limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid;
Query: select * from (select tid from kudu_via_city_pdi10  limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid
Query submitted at: 2021-12-07 19:04:18 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=2d4689d6883a941e:4531a70f00000000
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid | tid | entry_time | pdt | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 1   | 1   | 1          | 1   | aaaaa     | a        | cf66a3e3-03a7-4298-9fff-5898a196e9a4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 2   | 2   | 112        | 2   | a         | a        | dd2620c8-2545-4e1e-96d7-a3dd393874f6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 3   | 3   | 112        | 3   | a         | a        | 96885a13-19fd-4e91-9aef-bedfc50bfdff | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 4   | 4   | 223        | 4   | a         | a        | 18de7698-c539-448d-9d5d-9bb2ebfe4bf6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 5   | 5   | 112        | 5   | a         | a        | bf863770-8a95-48bc-86e5-a67322e14b95 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 6   | 6   | 223        | 6   | a         | a        | b6c5857e-24c4-48d7-8caf-3db7720991d6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 7   | 7   | 223        | 7   | a         | a        | d48f9c94-c8ef-448f-a73d-e99f2b09b43e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 8   | 8   | 334        | 8   | a         | a        | fac7094c-caa7-4d78-a0a6-34542984da09 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 9   | 9   | 112        | 9   | a         | a        | 525464bd-cb80-4e35-9e76-888185190344 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 10  | 10  | 223        | 10  | a         | a        | 07ae8d69-ea9c-4873-b99d-c190bbab17c3 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 0.23s

疑点2:怀疑是不是因为返回的结果tid都比较大,造成hash join,在创建hash table后,hash table数据量特别大,造成后面的hash join性能下降,但更改SQL,按照tid降序排列和升序,查询时间都不长

[cdh02.dongfeng.com:21000] default> select * from (select tid from kudu_via_city_pdi10 order by tid desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid;
Query: select * from (select tid from kudu_via_city_pdi10 order by tid desc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid
Query submitted at: 2021-12-07 19:11:08 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=ad4bb45bd3cf6184:c01baef200000000
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16777207 | 16777207 | 2443       | 16777207 | a         | a        | b0f7ced3-453a-4a08-b41a-35fc359a5d70 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777208 | 16777208 | 2554       | 16777208 | a         | a        | 07d05786-7184-4ce6-ba09-82cd55945edb | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777209 | 16777209 | 2332       | 16777209 | a         | a        | 07af41d1-7f44-4450-ac45-7ee848f2e7fd | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777210 | 16777210 | 2443       | 16777210 | a         | a        | ae3ed7e8-9454-4396-8e98-d27af0e43c69 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777211 | 16777211 | 2443       | 16777211 | a         | a        | 97c229ba-51b2-42e2-b9cc-17753227bc88 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777212 | 16777212 | 2554       | 16777212 | a         | a        | 29552d26-26cc-4634-a4b2-e487f637257f | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777213 | 16777213 | 2443       | 16777213 | a         | a        | 01f97e5a-b95f-4b38-9e35-397093073a4b | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777214 | 16777214 | 2554       | 16777214 | a         | a        | 2f8246ac-39d1-477b-9ff4-0b5f377088a6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777215 | 16777215 | 2554       | 16777215 | a         | a        | 15a16b90-63c9-401b-95f2-68e5b29c74fe | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777216 | 16777216 | 2665       | 16777216 | a         | a        | edb2ef0e-2af5-4edf-99a8-3818dc21dd3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 0.92s
[cdh02.dongfeng.com:21000] default> select * from (select tid from kudu_via_city_pdi10 order by tid asc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid;
Query: select * from (select tid from kudu_via_city_pdi10 order by tid asc limit 10) as a,kudu_via_city_pdi10 as b where a.tid=b.tid
Query submitted at: 2021-12-07 19:11:15 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=d548f7a214fd1b98:318e463d00000000
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid | tid | entry_time | pdt | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 1   | 1   | 1          | 1   | aaaaa     | a        | cf66a3e3-03a7-4298-9fff-5898a196e9a4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 2   | 2   | 112        | 2   | a         | a        | dd2620c8-2545-4e1e-96d7-a3dd393874f6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 3   | 3   | 112        | 3   | a         | a        | 96885a13-19fd-4e91-9aef-bedfc50bfdff | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 4   | 4   | 223        | 4   | a         | a        | 18de7698-c539-448d-9d5d-9bb2ebfe4bf6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 5   | 5   | 112        | 5   | a         | a        | bf863770-8a95-48bc-86e5-a67322e14b95 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 6   | 6   | 223        | 6   | a         | a        | b6c5857e-24c4-48d7-8caf-3db7720991d6 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 7   | 7   | 223        | 7   | a         | a        | d48f9c94-c8ef-448f-a73d-e99f2b09b43e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 8   | 8   | 334        | 8   | a         | a        | fac7094c-caa7-4d78-a0a6-34542984da09 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 9   | 9   | 112        | 9   | a         | a        | 525464bd-cb80-4e35-9e76-888185190344 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 10  | 10  | 223        | 10  | a         | a        | 07ae8d69-ea9c-4873-b99d-c190bbab17c3 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+-----+-----+------------+-----+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 0.72s
[cdh02.dongfeng.com:21000] default>

怀疑3:猜测impala自身子查询有问题(参考下面例子),更改子查询为常量,不查询表;但如果更改数据分布频率(160000改为16000002),响应时间立刻变短

 

[cdh02.dongfeng.com:21000] default>      select * from (
                                  >   select 160000 as tid union select  16000000 union select  16000003 union select  16000004 union select  16000005 union select  16000006 union select  16000007 union select  16000008 union select  16000009 union select  160000010) as b,kudu_via_city_pdi10 as a  where a.tid=b.tid;
Query: select * from (
  select 160000 as tid union select  16000000 union select  16000003 union select  16000004 union select  16000005 union select  16000006 union select  16000007 union select  16000008 union select  16000009 union select  160000010) as b,kudu_via_city_pdi10 as a  where a.tid=b.tid
Query submitted at: 2021-12-07 19:16:44 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=314d2d4b50db2c9c:f81a618600000000
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 160000   | 160000   | 1333       | 160000   | a         | a        | 1dd5e78c-6ee4-4407-9945-9179398de876 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000000 | 16000000 | 1777       | 16000000 | a         | a        | 212591cc-1143-4e9b-8262-d8baecac7cdd | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000003 | 16000003 | 889        | 16000003 | a         | a        | 7099c2c5-55d8-4041-841a-990febc39a3c | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000004 | 16000004 | 1000       | 16000004 | a         | a        | f378e7ca-b0f4-43ff-93d3-5ae932a719de | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000005 | 16000005 | 889        | 16000005 | a         | a        | bb9086e9-dd32-45da-8a08-fe685c45c6fb | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000006 | 16000006 | 1000       | 16000006 | a         | a        | d6efab84-a11c-4167-9c89-342fa03391d9 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000007 | 16000007 | 1000       | 16000007 | a         | a        | 2d1cdcea-c507-4105-891e-3b8800d2550e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000008 | 16000008 | 1111       | 16000008 | a         | a        | ffdd029f-1ea8-4e96-957d-82da3ee189b0 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000009 | 16000009 | 889        | 16000009 | a         | a        | 3d093105-a5a8-4bdd-b989-a4a36ef65970 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 9 row(s) in 8.60s
[cdh02.dongfeng.com:21000] default>    select * from (
                                  >   select 16000002 as tid union select  16000000 union select  16000003 union select  16000004 union select  16000005 union select  16000006 union select  16000007 union select  16000008 union select  16000009 union select  160000010) as b,kudu_via_city_pdi10 as a  where a.tid=b.tid;
Query: select * from (
  select 16000002 as tid union select  16000000 union select  16000003 union select  16000004 union select  16000005 union select  16000006 union select  16000007 union select  16000008 union select  16000009 union select  160000010) as b,kudu_via_city_pdi10 as a  where a.tid=b.tid
Query submitted at: 2021-12-07 19:17:01 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=e24b51362d788452:ea5cb9d100000000
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16000000 | 16000000 | 1777       | 16000000 | a         | a        | 212591cc-1143-4e9b-8262-d8baecac7cdd | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000002 | 16000002 | 889        | 16000002 | a         | a        | 2598b003-d573-43cd-a307-3503d8678201 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000003 | 16000003 | 889        | 16000003 | a         | a        | 7099c2c5-55d8-4041-841a-990febc39a3c | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000004 | 16000004 | 1000       | 16000004 | a         | a        | f378e7ca-b0f4-43ff-93d3-5ae932a719de | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000005 | 16000005 | 889        | 16000005 | a         | a        | bb9086e9-dd32-45da-8a08-fe685c45c6fb | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000006 | 16000006 | 1000       | 16000006 | a         | a        | d6efab84-a11c-4167-9c89-342fa03391d9 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000007 | 16000007 | 1000       | 16000007 | a         | a        | 2d1cdcea-c507-4105-891e-3b8800d2550e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000008 | 16000008 | 1111       | 16000008 | a         | a        | ffdd029f-1ea8-4e96-957d-82da3ee189b0 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16000009 | 16000009 | 889        | 16000009 | a         | a        | 3d093105-a5a8-4bdd-b989-a4a36ef65970 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 9 row(s) in 0.63s
[cdh02.dongfeng.com:21000] default> 

结论:

根据上面的实验,可以推断出,当小表进行hash join的字段,对应的数据分布不连续时,对应的hash table就比较大,以致影响和大表的hash操作,最终影响查询的时间。

解决方法:

对应上面的情况,可以分开表关联,通过客户端程序,进行多次的查询,绕开这个潜在的风险。

[cdh02.dongfeng.com:21000] default> select * from kudu_via_city_pdi10 as a where a.tid in (
                                  > 16777216,
                                  > 16711680,
                                  > 16252928,
                                  > 15728640,
                                  > 16515072,
                                  > 16646144,
                                  > 12582912,
                                  > 16760832,
                                  > 16744448,
                                  > 14680064);
Query: select * from kudu_via_city_pdi10 as a where a.tid in (
16777216,
16711680,
16252928,
15728640,
16515072,
16646144,
12582912,
16760832,
16744448,
14680064)
Query submitted at: 2021-12-07 19:22:22 (Coordinator: http://cdh02.dongfeng.com:25000)
Query progress can be monitored at: http://cdh02.dongfeng.com:25000/query_plan?query_id=7d4c593293499d27:22c3eb5d00000000
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| tid      | entry_time | pdt      | longitude | latitude | city_code                            | city_name | city_span | gps_mileage | meter_mileage | ecu_mileage | diff_mileage | std_mileage | total_fuel_cons | diff_fuel_cons | std_fuel_cons | operate_time | idlepark_time |
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
| 16252928 | 2554       | 16252928 | a         | a        | f68406e3-8a41-4ed6-912c-2919aa03f865 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16515072 | 2554       | 16515072 | a         | a        | 2bef4bc9-f79f-497f-a548-22c619979056 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16646144 | 2554       | 16646144 | a         | a        | d08ea476-f576-4390-9e1c-487a35b71e4f | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16711680 | 2554       | 16711680 | a         | a        | f6be4eb8-3eaf-4091-b4b2-6122c91e7d3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16744448 | 2554       | 16744448 | a         | a        | 46686639-11d1-43de-b35f-bd7cb6310f8b | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16760832 | 2554       | 16760832 | a         | a        | 7db069d2-59e1-46f6-bfde-65b2c5e8ebf4 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 16777216 | 2665       | 16777216 | a         | a        | edb2ef0e-2af5-4edf-99a8-3818dc21dd3e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 14680064 | 2554       | 14680064 | a         | a        | 8f70f07f-783a-4e44-8ad9-e860d861fb5e | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 15728640 | 2554       | 15728640 | a         | a        | a6ea6399-2daf-4218-b0a9-56c79ea1b491 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
| 12582912 | 2554       | 12582912 | a         | a        | 182e10b7-6b25-48aa-8ded-6d0247d2e822 | a         | 1         | 1           | 1             | 1           | 1            | 1           | 1               | 1              | 1             | 1            | 1             |
+----------+------------+----------+-----------+----------+--------------------------------------+-----------+-----------+-------------+---------------+-------------+--------------+-------------+-----------------+----------------+---------------+--------------+---------------+
Fetched 10 row(s) in 0.21s
[cdh02.dongfeng.com:21000] default>

 

上一篇:CUDA并行方式比较


下一篇:Mybatis-part11一对多