异步物化视图分区更新后自动触发一次全量更新

Viewed 17
3.0 mtmv

物化视图：
CREATE MATERIALIZED VIEW vc_voucher_entry_1_mv
(accounting_period,acbook_id,node_code)
BUILD IMMEDIATE REFRESH AUTO ON COMMIT
DUPLICATE KEY(accounting_period,acbook_id,node_code)
PARTITION BY (accounting_period)
DISTRIBUTED BY HASH(accounting_period, acbook_id) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"bloom_filter_columns" = "accounting_period, acbook_id, node_code",
"is_being_synced" = "false",
"storage_medium" = "ssd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "30000",
"group_commit_data_bytes" = "268435456",
"excluded_trigger_tables" = "jsy_finance.vc_voucher_entry"
)
AS SELECT
internal.jsy_finance.vv.accounting_period,
internal.jsy_finance.vv.acbook_id,
internal.jsy_finance.vve.node_code
FROM
internal.jsy_finance.vc_voucher vv
LEFT JOIN
internal.jsy_finance.vc_voucher_entry vve ON internal.jsy_finance.vv.id = internal.jsy_finance.vve.vc_id
and internal.jsy_finance.vv.accounting_period = internal.jsy_finance.vve.accounting_period
and internal.jsy_finance.vv.acbook_id = internal.jsy_finance.vve.acbook_id
WHERE
internal.jsy_finance.vv.tempsave_flag = FALSE
AND internal.jsy_finance.vv.cancel_flag = FALSE
AND internal.jsy_finance.vv.valid = 0
AND internal.jsy_finance.vve.valid = 0
GROUP BY
internal.jsy_finance.vv.accounting_period,
internal.jsy_finance.vv.acbook_id,
internal.jsy_finance.vve.node_code;
物化视图信息：
image.png
基表：
CREATE TABLE vc_voucher (
id varchar(32) NULL COMMENT '主键ID',
accounting_period varchar(7) NULL COMMENT '会计期间',
acbook_id varchar(32) NULL COMMENT '核算账簿id',
make_date date NULL COMMENT '制单日期',
vc_type varchar(32) NULL COMMENT '凭证类别',
vc_code int NULL COMMENT '凭证号',
attach_num int NULL COMMENT '附单据数',
debit_local_sum decimal(20,2) NULL DEFAULT "0.00" COMMENT '组织本币(借方)',
credit_local_sum decimal(20,2) NULL DEFAULT "0.00" COMMENT '组织本币(贷方)',
remark_entry varchar(700) NULL COMMENT '分录摘要',
adjust_flag tinyint NULL DEFAULT "0" COMMENT '数量调整标志',
diff_flag tinyint NULL DEFAULT "0" COMMENT '差异凭证标志',
source_system varchar(20) NULL DEFAULT "5" COMMENT '来源业务系统',
source_bussiness varchar(60) NULL COMMENT '来源业务单据',
source_buss_code varchar(120) NULL COMMENT '来源单据号',
tempsave_flag tinyint NULL DEFAULT "0" COMMENT '暂存标志',
cancel_flag tinyint NULL DEFAULT "0" COMMENT '作废标志',
fund_flag tinyint NULL DEFAULT "0" COMMENT '含资金科目标志',
loss_flag tinyint NULL DEFAULT "0" COMMENT '损益标志',
fix_flag tinyint NULL DEFAULT "0" COMMENT '固定资产标志',
reversal_flag tinyint NULL DEFAULT "0" COMMENT '冲销标志',
error_flag tinyint NULL DEFAULT "0" COMMENT '标错标志',
error_msg varchar(200) NULL COMMENT '标错信息',
sign_status char(1) NULL DEFAULT "0" COMMENT '签字状态(0-初始，1-已签字)',
review_status char(1) NULL DEFAULT "0" COMMENT '审核状态(0-初始，1-已审核)',
keep_status char(1) NULL DEFAULT "0" COMMENT '记账状态(0-初始，1-已记账)',
maker varchar(32) NULL COMMENT '制单人',
signer varchar(32) NULL COMMENT '签字人',
reviewer varchar(32) NULL COMMENT '审核人',
remark varchar(100) NULL COMMENT '备注',
book_keeper varchar(32) NULL COMMENT '记账人',
vc_code_import int NULL COMMENT '导入凭证号',
create_time datetime NULL COMMENT '创建时间',
create_by varchar(32) NULL COMMENT '创建人',
update_time datetime NULL COMMENT '修改时间',
update_by varchar(32) NULL COMMENT '修改人',
valid int NOT NULL DEFAULT "0" COMMENT '删除标志',
INDEX idx_acbook_id (acbook_id) USING INVERTED,
INDEX idx_make_date (make_date) USING INVERTED,
INDEX idx_accounting_period (accounting_period) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(id, accounting_period, acbook_id)
AUTO PARTITION BY LIST (accounting_period)
(PARTITION p20212d067 VALUES IN ("2021-06"),
PARTITION p20212d077 VALUES IN ("2021-07"),
PARTITION p20212d087 VALUES IN ("2021-08"),
PARTITION p20212d097 VALUES IN ("2021-09"),
PARTITION p20222d027 VALUES IN ("2022-02"),
PARTITION p20222d077 VALUES IN ("2022-07"),
PARTITION p20232d017 VALUES IN ("2023-01"),
PARTITION p20232d027 VALUES IN ("2023-02"),
PARTITION p20232d037 VALUES IN ("2023-03"),
PARTITION p20232d047 VALUES IN ("2023-04"),
PARTITION p20232d057 VALUES IN ("2023-05"),
PARTITION p20232d067 VALUES IN ("2023-06"),
PARTITION p20232d077 VALUES IN ("2023-07"),
PARTITION p20232d087 VALUES IN ("2023-08"),
PARTITION p20232d097 VALUES IN ("2023-09"),
PARTITION p20232d107 VALUES IN ("2023-10"),
PARTITION p20232d117 VALUES IN ("2023-11"),
PARTITION p20232d127 VALUES IN ("2023-12"),
PARTITION p20242d017 VALUES IN ("2024-01"),
PARTITION p20242d027 VALUES IN ("2024-02"),
PARTITION p20242d037 VALUES IN ("2024-03"),
PARTITION p20242d047 VALUES IN ("2024-04"),
PARTITION p20242d057 VALUES IN ("2024-05"),
PARTITION p20242d067 VALUES IN ("2024-06"),
PARTITION p20242d077 VALUES IN ("2024-07"),
PARTITION p20242d087 VALUES IN ("2024-08"),
PARTITION p20242d097 VALUES IN ("2024-09"),
PARTITION p20242d107 VALUES IN ("2024-10"),
PARTITION p20242d117 VALUES IN ("2024-11"),
PARTITION p20242d127 VALUES IN ("2024-12"),
PARTITION p20252d017 VALUES IN ("2025-01"),
PARTITION p20252d027 VALUES IN ("2025-02"),
PARTITION p20252d037 VALUES IN ("2025-03"),
PARTITION p20252d047 VALUES IN ("2025-04"),
PARTITION p20252d057 VALUES IN ("2025-05"),
PARTITION p20252d067 VALUES IN ("2025-06"),
PARTITION p20252d077 VALUES IN ("2025-07"),
PARTITION p20252d087 VALUES IN ("2025-08"),
PARTITION p20252d097 VALUES IN ("2025-09"),
PARTITION p20252d127 VALUES IN ("2025-12"),
PARTITION p20262d017 VALUES IN ("2026-01"))
DISTRIBUTED BY HASH(accounting_period, acbook_id) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"bloom_filter_columns" = "acbook_id, make_date, accounting_period",
"is_being_synced" = "false",
"storage_medium" = "ssd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"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 TABLE vc_voucher_entry (
id varchar(32) NOT NULL COMMENT '主键ID',
accounting_period varchar(7) NULL COMMENT '会计期间',
acbook_id varchar(32) NULL COMMENT '核算账簿id',
vc_id varchar(32) NOT NULL COMMENT '凭证id',
order_num int NOT NULL COMMENT '行号',
remark varchar(700) NULL COMMENT '摘要',
node_code varchar(40) NULL COMMENT '会计科目编码',
currency varchar(10) NULL COMMENT '币种',
debit_local decimal(20,2) NULL DEFAULT "0.00" COMMENT '组织本币(借方)',
credit_local decimal(20,2) NULL DEFAULT "0.00" COMMENT '组织本币(贷方)',
debit_amount decimal(17,4) NULL DEFAULT "0.0000" COMMENT '借方数量',
credit_amount decimal(17,4) NULL DEFAULT "0.0000" COMMENT '贷方数量',
unit_price decimal(20,6) NULL DEFAULT "0.000000" COMMENT '单价',
unit varchar(20) NULL COMMENT '单位',
bill_id varchar(64) NULL COMMENT '备注单号',
busi_date date NULL COMMENT '业务日期',
error_flag tinyint NOT NULL DEFAULT "0" COMMENT '标错标志',
error_msg varchar(200) NULL COMMENT '标错信息',
create_time datetime NOT NULL COMMENT '创建时间',
create_by varchar(32) NOT NULL COMMENT '创建人',
update_time datetime NULL COMMENT '修改时间',
update_by varchar(32) NULL COMMENT '修改人',
valid int NOT NULL COMMENT '删除标志',
INDEX idx_id (id) USING INVERTED,
INDEX idx_vc_id (vc_id) USING INVERTED,
INDEX idx_node_code (node_code) USING INVERTED,
INDEX idx_accounting_period (accounting_period) USING INVERTED,
INDEX idx_acbook_id (acbook_id) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(id, accounting_period, acbook_id)
AUTO PARTITION BY LIST (accounting_period)
(PARTITION p20212d067 VALUES IN ("2021-06"),
PARTITION p20212d077 VALUES IN ("2021-07"),
PARTITION p20212d087 VALUES IN ("2021-08"),
PARTITION p20212d097 VALUES IN ("2021-09"),
PARTITION p20222d027 VALUES IN ("2022-02"),
PARTITION p20222d077 VALUES IN ("2022-07"),
PARTITION p20232d017 VALUES IN ("2023-01"),
PARTITION p20232d027 VALUES IN ("2023-02"),
PARTITION p20232d037 VALUES IN ("2023-03"),
PARTITION p20232d047 VALUES IN ("2023-04"),
PARTITION p20232d057 VALUES IN ("2023-05"),
PARTITION p20232d067 VALUES IN ("2023-06"),
PARTITION p20232d077 VALUES IN ("2023-07"),
PARTITION p20232d087 VALUES IN ("2023-08"),
PARTITION p20232d097 VALUES IN ("2023-09"),
PARTITION p20232d107 VALUES IN ("2023-10"),
PARTITION p20232d117 VALUES IN ("2023-11"),
PARTITION p20232d127 VALUES IN ("2023-12"),
PARTITION p20242d017 VALUES IN ("2024-01"),
PARTITION p20242d027 VALUES IN ("2024-02"),
PARTITION p20242d037 VALUES IN ("2024-03"),
PARTITION p20242d047 VALUES IN ("2024-04"),
PARTITION p20242d057 VALUES IN ("2024-05"),
PARTITION p20242d067 VALUES IN ("2024-06"),
PARTITION p20242d077 VALUES IN ("2024-07"),
PARTITION p20242d087 VALUES IN ("2024-08"),
PARTITION p20242d097 VALUES IN ("2024-09"),
PARTITION p20242d107 VALUES IN ("2024-10"),
PARTITION p20242d117 VALUES IN ("2024-11"),
PARTITION p20242d127 VALUES IN ("2024-12"),
PARTITION p20252d017 VALUES IN ("2025-01"),
PARTITION p20252d027 VALUES IN ("2025-02"),
PARTITION p20252d037 VALUES IN ("2025-03"),
PARTITION p20252d047 VALUES IN ("2025-04"),
PARTITION p20252d057 VALUES IN ("2025-05"),
PARTITION p20252d067 VALUES IN ("2025-06"),
PARTITION p20252d077 VALUES IN ("2025-07"),
PARTITION p20252d087 VALUES IN ("2025-08"),
PARTITION p20252d097 VALUES IN ("2025-09"),
PARTITION p20252d127 VALUES IN ("2025-12"),
PARTITION p20262d017 VALUES IN ("2026-01"))
DISTRIBUTED BY HASH(accounting_period, acbook_id) BUCKETS 16
PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"min_load_replica_num" = "1",
"bloom_filter_columns" = "vc_id, acbook_id, node_code, accounting_period",
"is_being_synced" = "false",
"storage_medium" = "ssd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"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"
);
物化视图与表的分区是对齐的，每次更新数据后先刷新对应分区数据，然后再刷新全量数据
image.png
到底是什么原因，有没有什么优化方法

gongjiayun1
1 Answers

internal.jsy_finance.vc_voucher_entry 这个表的数据变化会导致触发全量刷新，有没有可能这个表的数据发生了变化？

seawinde101