基础环境：
[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>