Doris最新版本2.1.8，异步物化视图表的数据有时候会整张表的数据全没掉，需要重新REFESH后才能有。不管是增量更新还是全量更新都会这样，有大佬协助看一下吗？还有改成全量刷新的话，是不是会产生很多垃圾数据，看TrashUsedCapacity会慢慢增大。

Viewed 71
mtmv 2.1 
原始表： CREATE TABLE `ds_98f656ac_30d6_4728_a7de_bfca35d1469b` (

c_b80bb7740288fda1f201890375a60c8f varchar(65533) NULL,
c_5c69d5e8925072d43206f2c888757bd3 varchar(65533) NULL,
c_ecd57eb972c6e1f3f44f38c714d066e3 varchar(65533) NULL,
c_a05b867ae9cf45f30219cfe7bf333adb varchar(65533) NULL,
c_dbc02cde084e7174cab44c59b5f88db5 varchar(65533) NULL,
c_b5c3e75bd7437160d60da142b6cc40b6 varchar(65533) NULL,
c_b985b08ee2c2306a0de3919ee6d62a0a varchar(65533) NULL,
c_9bea82def865d4d0e499eb252805f127 varchar(65533) NULL,
c_521b20f586ac6d12148ada6e759cb5df varchar(65533) NULL,
c_1a8db4c996d8ed8289da5f957879ab94 varchar(65533) NULL,
c_c4820b0dbc62810e4462c6f28573011a varchar(65533) NULL,
c_5af84b1f5e4063bf804f7936b9a06207 varchar(65533) NULL,
c_4017580d301003490d27cd21b02fb903 varchar(65533) NULL,
c_3233c650e5d056f78af1efdab4ae3db0 varchar(65533) NULL,
c_a194085e66a64227302d9a35a58cbe48 varchar(65533) NULL,
c_357e8ec872b821d1544007985b4e7de5 varchar(65533) NULL,
c_dc8ddb686df2f8f9d1b33a16546fcb30 varchar(65533) NULL,
c_b7752ac5d05ea306d79b1cdb2027dc90 varchar(65533) NULL,
c_4e315cfcf41292aa6d5129cd04a8fedd varchar(65533) NULL,
c_d08993b50f4bea497f92cd446cfceb9d varchar(65533) NULL,
c_5dc6e1b7eb3a48c5a4cdd0805da71212 varchar(65533) NULL,
c_85e47ac07ac9d6416168a97e33fa969a varchar(65533) NULL,
c_f16f874cc63b51c4e0a08cb12d817a3e varchar(65533) NULL,
c_d62ff6c3a63f848da2581bd11a7bc9d9 varchar(65533) NULL,
c_f8a3193ab0978f2ab82a5007fa1a9e5e varchar(65533) NULL,
c_b59d33b19096ecc45432fb1c35dc1436 varchar(65533) NULL,
c_edf4489bbbe5c73925d0ee968c7aeb2a varchar(65533) NULL,
c_e0ba24bed78839ef94bfed4ee7eb249e varchar(65533) NULL,
c_2fb72c6e14e130caddb9652314b3b77c varchar(65533) NULL,
c_1a853c31fe56b847c75351c8313ad1db varchar(65533) NULL,
c_b69b5ec9856c5a0837c7dca95d81e464 varchar(65533) NULL,
c_fc57b040b9db154a22b26135b799817a varchar(65533) NULL,
c_fe2db8a755f73065da3cff1640a4019c varchar(65533) NULL,
c_ad7c74052a2bda10a611df4ea69f5518 varchar(65533) NULL,
c_c822d921c90628f68c78d48324534e7a varchar(65533) NULL,
c_5def2d9715c14d128632d3f0e87719fe varchar(65533) NULL,
c_ea503fde25ed5336f0017da3e36b0ce0 varchar(65533) NULL,
c_47f5ba29c6be9d4b357886dbb212f1ad varchar(65533) NULL,
c_b1e3a642621b193590e1a0701f355f46 varchar(50000) NULL,
c_a453ec26c45f66baf2beb722d2ba27f1 varchar(65533) NULL,
c_4f4bc167610e1d81710f66b795241a50 varchar(65533) NULL,
c_2a809d50c55f696bca494261b2e2e757 varchar(65533) NULL,
c_e2dc6c48c56de466f6d13781796abf3d varchar(65533) NULL,
c_59a39b8e2909f82652f347c515ce43ab varchar(65533) NULL,
c_7b8ce8ff31b207674c363b3cdf5d66fd varchar(65533) NULL
) ENGINE=OLAP
UNIQUE KEY(c_b80bb7740288fda1f201890375a60c8f)
DISTRIBUTED BY HASH(c_b80bb7740288fda1f201890375a60c8f) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);

异步物化视图表：

CREATE MATERIALIZED VIEW ds_7a1f665d_8882_4113_b919_5d5989068d84
(c_b80bb7740288fda1f201890375a60c8f,c_5c69d5e8925072d43206f2c888757bd3,c_ecd57eb972c6e1f3f44f38c714d066e3,c_a05b867ae9cf45f30219cfe7bf333adb,c_dbc02cde084e7174cab44c59b5f88db5,c_b5c3e75bd7437160d60da142b6cc40b6,c_b985b08ee2c2306a0de3919ee6d62a0a,c_9bea82def865d4d0e499eb252805f127,c_521b20f586ac6d12148ada6e759cb5df,c_1a8db4c996d8ed8289da5f957879ab94,c_c4820b0dbc62810e4462c6f28573011a,c_5af84b1f5e4063bf804f7936b9a06207,c_4017580d301003490d27cd21b02fb903,c_3233c650e5d056f78af1efdab4ae3db0,c_a194085e66a64227302d9a35a58cbe48,c_357e8ec872b821d1544007985b4e7de5,c_dc8ddb686df2f8f9d1b33a16546fcb30,c_b7752ac5d05ea306d79b1cdb2027dc90,c_4e315cfcf41292aa6d5129cd04a8fedd,c_d08993b50f4bea497f92cd446cfceb9d,c_5dc6e1b7eb3a48c5a4cdd0805da71212,c_85e47ac07ac9d6416168a97e33fa969a,c_f16f874cc63b51c4e0a08cb12d817a3e,c_d62ff6c3a63f848da2581bd11a7bc9d9,c_f8a3193ab0978f2ab82a5007fa1a9e5e,c_b59d33b19096ecc45432fb1c35dc1436,c_edf4489bbbe5c73925d0ee968c7aeb2a,c_e0ba24bed78839ef94bfed4ee7eb249e,c_2fb72c6e14e130caddb9652314b3b77c,c_1a853c31fe56b847c75351c8313ad1db,c_b69b5ec9856c5a0837c7dca95d81e464,c_fc57b040b9db154a22b26135b799817a,c_fe2db8a755f73065da3cff1640a4019c,c_ad7c74052a2bda10a611df4ea69f5518,c_c822d921c90628f68c78d48324534e7a,c_5def2d9715c14d128632d3f0e87719fe,c_ea503fde25ed5336f0017da3e36b0ce0,c_47f5ba29c6be9d4b357886dbb212f1ad,c_b1e3a642621b193590e1a0701f355f46,c_a453ec26c45f66baf2beb722d2ba27f1,c_4f4bc167610e1d81710f66b795241a50,c_2a809d50c55f696bca494261b2e2e757,c_e2dc6c48c56de466f6d13781796abf3d,c_59a39b8e2909f82652f347c515ce43ab,c_7b8ce8ff31b207674c363b3cdf5d66fd)
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DUPLICATE KEY(c_b80bb7740288fda1f201890375a60c8f)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_nondeterministic_function" = "true"
)
AS (
SELECT
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b80bb7740288fda1f201890375a60c8f,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_5c69d5e8925072d43206f2c888757bd3,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_ecd57eb972c6e1f3f44f38c714d066e3,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_a05b867ae9cf45f30219cfe7bf333adb,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_dbc02cde084e7174cab44c59b5f88db5,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b5c3e75bd7437160d60da142b6cc40b6,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b985b08ee2c2306a0de3919ee6d62a0a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_9bea82def865d4d0e499eb252805f127,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_521b20f586ac6d12148ada6e759cb5df,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_1a8db4c996d8ed8289da5f957879ab94,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_c4820b0dbc62810e4462c6f28573011a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_5af84b1f5e4063bf804f7936b9a06207,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_4017580d301003490d27cd21b02fb903,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_3233c650e5d056f78af1efdab4ae3db0,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_a194085e66a64227302d9a35a58cbe48,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_357e8ec872b821d1544007985b4e7de5,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_dc8ddb686df2f8f9d1b33a16546fcb30,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b7752ac5d05ea306d79b1cdb2027dc90,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_4e315cfcf41292aa6d5129cd04a8fedd,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_d08993b50f4bea497f92cd446cfceb9d,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_5dc6e1b7eb3a48c5a4cdd0805da71212,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_85e47ac07ac9d6416168a97e33fa969a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_f16f874cc63b51c4e0a08cb12d817a3e,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_d62ff6c3a63f848da2581bd11a7bc9d9,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_f8a3193ab0978f2ab82a5007fa1a9e5e,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b59d33b19096ecc45432fb1c35dc1436,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_edf4489bbbe5c73925d0ee968c7aeb2a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_e0ba24bed78839ef94bfed4ee7eb249e,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_2fb72c6e14e130caddb9652314b3b77c,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_1a853c31fe56b847c75351c8313ad1db,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b69b5ec9856c5a0837c7dca95d81e464,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_fc57b040b9db154a22b26135b799817a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_fe2db8a755f73065da3cff1640a4019c,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_ad7c74052a2bda10a611df4ea69f5518,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_c822d921c90628f68c78d48324534e7a,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_5def2d9715c14d128632d3f0e87719fe,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_ea503fde25ed5336f0017da3e36b0ce0,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_47f5ba29c6be9d4b357886dbb212f1ad,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_b1e3a642621b193590e1a0701f355f46,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_a453ec26c45f66baf2beb722d2ba27f1,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_4f4bc167610e1d81710f66b795241a50,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_2a809d50c55f696bca494261b2e2e757,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_e2dc6c48c56de466f6d13781796abf3d,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_59a39b8e2909f82652f347c515ce43ab,
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b.c_7b8ce8ff31b207674c363b3cdf5d66fd
FROM
internal.qs.ds_98f656ac_30d6_4728_a7de_bfca35d1469b
)

weiyf5
4 Answers

用户上千物化视图都是commit模式刷新，然后数据变更有比较多，资源一直都是打满状态，有可能是这方面的原因。然后现在改为手动刷新之后，没有这个问题了。

徐振超@SelectDB6961

这个表 ds_98f656ac_30d6_4728_a7de_bfca35d1469b是内表还是外表？数据量大概多少？

需要重新refresh 才会有数据，这个命令是refresh materialized view auto 还是
refresh materialized view complete?

seawinde101

是refresh materialized view complete

weiyf5

原表是否会有时被删掉数据，导致物化视图没有数据？
无论增量还是全量，目前刷新物化视图后都会占用回收站的空间，在新版本，物化视图的历史数据已经改为不在进入回收站了

zhangdong251