查询sql，结果返回需要30秒，怎么能优化一下呢

1. 版本2.0.4，表是天分区，一天2400万数据
2. 建表语句

CREATE TABLE `ods_kafka_vehicle_dynamic_info` (
  `date_time` datetime(6) NULL COMMENT '上报时间',
  `vin` varchar(255) NULL COMMENT '车辆vin',
  `vin_id` varchar(255) NULL,
  `ts` bigint(20) NULL COMMENT '时间戳',
  `longitude` double NULL COMMENT '经度',
  `latitude` double NULL COMMENT '纬度',
  `low_battery_voltage` double NULL,
  `battery_electricity` double NULL,
  `battery_electricity_front` double NULL,
  `speed` double NULL,
  `drive_mode` int(11) NULL,
  `control_mode` int(11) NULL,
  `use_battery` int(11) NULL,
  `network_delay` int(11) NULL,
  `remaining_battery_range` double NULL COMMENT '剩余续航里程 单位km',
  `rf_pressure` double NULL COMMENT '右前胎压',
  `lr_pressure` double NULL COMMENT '左后胎压',
  `lf_pressure` double NULL COMMENT '左前胎压',
  `rr_pressure` double NULL COMMENT '右后胎压',
  `azimuth` double NULL COMMENT '航向角',
  INDEX idx_vin (`vin`) USING INVERTED COMMENT '',
  INDEX idx_vin_id (`vin_id`) USING INVERTED COMMENT '',
  INDEX idx_control_mode (`control_mode`) USING INVERTED COMMENT '',
  INDEX index_ts (`ts`) USING INVERTED COMMENT ''
) ENGINE=OLAP
DUPLICATE KEY(`date_time`)
COMMENT '车辆动态信息上报表-来源kafka'
PARTITION BY RANGE(`date_time`)
(PARTITION p20231204 VALUES [('2023-12-04 00:00:00'), ('2023-12-05 00:00:00')),
PARTITION p20231205 VALUES [('2023-12-05 00:00:00'), ('2023-12-06 00:00:00')),
分区很多大概是290个  
分区很多大概是290个  
分区很多大概是290个  
PARTITION p20240627 VALUES [('2024-06-27 00:00:00'), ('2024-06-28 00:00:00')),
PARTITION p20240628 VALUES [('2024-06-28 00:00:00'), ('2024-06-29 00:00:00')),
PARTITION p20240629 VALUES [('2024-06-29 00:00:00'), ('2024-06-30 00:00:00')))
DISTRIBUTED BY RANDOM BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-2147483648",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 3",
"dynamic_partition.buckets" = "10",
"dynamic_partition.create_history_partition" = "false",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"storage_format" = "V2",
"compression" = "ZSTD",
"light_schema_change" = "true",
"compaction_policy" = "time_series",
"time_series_compaction_goal_size_mbytes" = "1024",
"time_series_compaction_file_count_threshold" = "2000",
"time_series_compaction_time_threshold_seconds" = "3600",
"time_series_compaction_empty_rowsets_threshold" = "5",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
); |

1. sql语句， ods库和app库，两个有join操作

WITH t1 AS (SELECT ts,a.vin,vin_id,longitude,latitude,azimuth,speed,battery_electricity,battery_electricity_front,use_battery
FROM ods.ods_kafka_vehicle_dynamic_info AS a
JOIN (
SELECT vin, MAX(ts) AS max_ts
FROM ods.ods_kafka_vehicle_dynamic_info
WHERE date_time >= '2024-06-25 00:00:00' and date_time <= '2024-06-25 23:59:59'
and vin in ('XXXXXX', 'XXXX', '大概430个vin')
GROUP BY vin
) AS b
ON a.vin = b.vin AND a.ts = b.max_ts
)
SELECT
COALESCE(a.ts, b.ts) AS ts,
COALESCE(a.vin, b.vin) AS vin,
COALESCE(a.vin_id, b.vin_id) AS vinId,
COALESCE(a.longitude, b.longitude) AS longitude,
COALESCE(a.latitude, b.latitude) AS latitude,
COALESCE(a.azimuth, b.azimuth) AS azimuth,
COALESCE(a.speed, b.speed) AS speed,
COALESCE(a.battery_electricity, b.battery_electricity) AS batteryElectricity,
COALESCE(a.battery_electricity_front, b.battery_electricity_front) AS batteryElectricityFront,
COALESCE(a.use_battery, b.use_battery) AS useBattery
FROM t1 a
FULL OUTER JOIN app.app_last_geoinfo_for_vehicle b ON a.vin = b.vin
where 1=1
and b.vin in ('XXXXXX', 'XXXX', '大概430个vin')
order by ts desc;