有一张天分区表,存储了较多的数据,随着数据越来越大,发现列统计的sql扫描的数据、内存消耗、cpu消耗、查询消耗都特别大,频次还比较高,基本上一分钟一次,消耗资源特别大,影响到集群的使用。这个表使用flinkcdc实时更新,可能会有新数据不断写入,不存在数据更新,主键表。
doris版本:2.1.5
有一张天分区表,存储了较多的数据,随着数据越来越大,发现列统计的sql扫描的数据、内存消耗、cpu消耗、查询消耗都特别大,频次还比较高,基本上一分钟一次,消耗资源特别大,影响到集群的使用。这个表使用flinkcdc实时更新,可能会有新数据不断写入,不存在数据更新,主键表。
doris版本:2.1.5
补充一下相关信息,审计日志信息如下
审计日志中sql如下:
关键信息隐藏了,使用xxxxxx代替
SELECT CONCAT('16640847', '-', '-1', '-', 'input') AS `id`, 0 AS `catalog_id`, 10007 AS `db_id`, 16640847 AS `tbl_id`, -1 AS `idx_id`, 'input' AS `col_id`, NULL AS `part_id`, 15002430 AS `row_count`, SUM(`t1`.`count`) * COUNT(1) / (SUM(`t1`.`count`) - SUM(IF(`t1`.`count` = 1, 1, 0)) + SUM(IF(`t1`.`count` = 1, 1, 0)) * SUM(`t1`.`count`) / 15002430) as `ndv`, IFNULL(SUM(IF(`t1`.`column_key` IS NULL, `t1`.`count`, 0)), 0) * 3.5768580436706543 as `null_count`, SUBSTRING(CAST('xxxxxxxxxxxxxxxxxxx' AS STRING), 1, 1024) AS `min`, SUBSTRING(CAST('xxxxxxxxxxxxxxxxx' AS STRING), 1, 1024) AS `max`, SUM(LENGTH(`column_key`) * count) * 3.5768580436706543 AS `data_size`, NOW() FROM ( SELECT t0.`colValue` as `column_key`, COUNT(1) as `count` FROM (SELECT SUBSTRING(CAST(`input` AS STRING), 1, 1024) AS `colValue` FROM `internal`.`xxxxxx`.`xxxxxxxxx` TABLET(16647454, 16647458, 16647462, 16648001, 16647993, 16647997, 16648087, 16648091, 16648095, 16648264, 16648268, 16648323, 16648327, 16648319, 16648398, 16648402, 16648394, 16648489, 16648493, 16648485, 16648569, 16648573, 16648577, 16648631, 16648635, 16648639, 16648661, 16648665, 16648752, 16648756, 16648748, 16648773, 16648777, 16648910, 16648914, 16648918, 16649018, 16649022, 16649014, 16649036, 16649040, 16649032, 16649596, 16649588, 16649592, 16649657, 16649661, 16649653, 16649797, 16649801, 16649793, 16649827, 16649831, 16649823, 16649846, 16649850, 16649842, 16649927, 16649931, 16649935, 16650007, 16650011, 16650015, 16650105, 16650109, 16650113, 16650163, 16650167, 16650171, 16650194, 16650198, 16650202, 16650273, 16650265, 16650372, 16650376, 16650368, 16650431, 16650435, 16650439, 16650504, 16650496, 16650500, 16650589, 16650593, 16650597, 16650652, 16650656, 16650648, 16650738, 16650730, 16650734, 16650788, 16650792, 16650784, 16650813, 16650817, 16650809, 16650951, 16650955, 16650959, 16651497, 16651489, 16651493, 16651539, 16651531, 16651535, 16651773, 16651765, 16651769, 16651728, 16651732, 16651830, 16651822, 16651826, 16651847, 16651851, 16651843, 16651969, 16651973, 16651977, 16652052, 16652056, 16652060, 16652125, 16652129, 16652121, 16652163, 16652167, 16652159, 16652237, 16652229, 16652233, 16652265, 16652269, 16652273, 16652310, 16652314, 16652318, 16652378, 16652382, 16652386, 16652414, 16652418, 16652422, 16652457, 16652461, 16652453, 16653247, 16653251, 16653243, 16653698, 16653702, 16653694, 16654230, 16654234, 16654226, 16654303, 16654307, 16654311, 16654408, 16654412, 16654404, 16654429, 16654421, 16654586, 16654590, 16654594, 16654617, 16654609, 16654613, 16654673, 16654665, 16654669, 16654761, 16654765, 16654769, 16654822, 16654826, 16655113, 16655105, 16655584, 16655588, 16655580, 16655728, 16655732, 16655724, 16655745, 16655749, 16655741, 16656083, 16656087, 16656181, 16656185, 16656177, 16656311, 16656303, 16656412, 16656404, 16656445, 16656449, 16656557, 16657180, 16657254, 16657287, 16657355, 16657575, 16657579, 16657975, 16657979, 16658021, 16658837, 16659112, 16659353, 16659500, 16659637, 16659641, 1