A materialized view incremental refresh method, device, equipment, medium and product

Abstract

The application discloses a kind of physical view incremental refreshing method, device, equipment, medium and product, it is related to database technical field.The method comprises: dividing base table, obtains at least two sub-tables of shard;Log temporary table and log main table of physical view are created for the sub-table of shard;In data shard, shard local physical view is created, and incremental refreshing control information is registered;According to incremental refreshing point, the log temporary table and the log main table determine incremental change set;According to the base table involved in the shard local physical view and the incremental change set, the shard local physical view is refreshed, obtains the new shard local physical view or intermediate result set of the sub-table of shard;At least two new shard local physical views or intermediate result sets of sub-table of shard are merged, and the target physical view of the base table is obtained.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, apparatus, device, medium, and product for incremental refresh of materialized views. Background Technology

[0002] In database systems, materialized views, as a pre-computed data abstraction layer, play a crucial role in improving the performance of complex queries and reducing real-time computing overhead. As a core component for accelerating complex queries, the update efficiency and data consistency of materialized views directly impact the real-time performance and reliability of business systems. With the continuous growth of data scale and business complexity, materialized view maintenance mechanisms face significant challenges. Full refresh mode, due to its enormous resource consumption, is insufficient to meet the real-time requirements of minute-level or even second-level updates. Traditional incremental refresh schemes mostly rely on global transaction sequence numbers or a single logical clock to coordinate cross-shard data changes, leading to intensified global lock contention in distributed scenarios, limited shard-level parallel processing efficiency, and difficulty in adapting to high-concurrency writes and high-frequency incremental refresh requirements. Furthermore, the full merging or global aggregation calculation of cross-shard incremental changes results in wasted network transmission and computing resources. Therefore, a materialized view incremental refresh method is urgently needed. Summary of the Invention

[0003] This invention provides a method, apparatus, device, medium, and product for incremental refresh of materialized views to accelerate the refresh efficiency of materialized views.

[0004] According to one aspect of the present invention, a method for incrementally refreshing a materialized view is provided, the method comprising:

[0005] The base table is partitioned to obtain at least two sub-tables; each sub-table has the same table structure.

[0006] Create a materialized view of the log temporary table and the log main table for the sharded sub-tables;

[0007] Create a corresponding shard-local materialized view in the data shard and register incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log master table;

[0008] The incremental change set is determined based on the incremental refresh point, the temporary log table, and the main log table;

[0009] The sharded local materialized view is refreshed based on the base table involved in the sharded local materialized view and the incremental change set to obtain a new sharded local materialized view or intermediate result set of the sharded sub-table;

[0010] Merge new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table.

[0011] According to another aspect of the present invention, a materialized view incremental refresh device is provided, the device comprising:

[0012] The base table partitioning module is used to partition the base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure.

[0013] The log creation module is used to create a temporary log table and a main log table for materialized views of the sharded sub-tables;

[0014] The materialized view generation module is used to create corresponding local materialized views for data shards and register incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh points of the change sequence number of the log master table;

[0015] The incremental change set determination module is used to determine the incremental change set based on the incremental refresh point, the temporary log table, and the main log table;

[0016] The materialized view refresh module is used to refresh the sharded local materialized view based on the base table involved in the sharded local materialized view and the incremental change set, so as to obtain a new sharded local materialized view or intermediate result set of the sharded sub-table;

[0017] The target materialized view determination module is used to merge new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table.

[0018] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0019] At least one processor; and

[0020] A memory communicatively connected to the at least one processor; wherein,

[0021] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the materialized view incremental refresh method according to any embodiment of the present invention.

[0022] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions for causing a processor to execute and implement the materialized view incremental refresh method according to any embodiment of the present invention.

[0023] According to another aspect of the present invention, a computer program product is provided, the computer program product comprising a computer program that, when executed by a processor, implements the materialized view incremental refresh method according to any embodiment of the present invention.

[0024] The technical solution of this invention involves dividing a base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure; creating a log temporary table and a log main table for materialized views of the sharded sub-tables; creating corresponding sharded local materialized views in the data shards and registering incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log main table; determining the incremental change set based on the incremental refresh point, the log temporary table, and the log main table; refreshing the sharded local materialized views based on the base table and the incremental change set involved in the sharded local materialized views to obtain new sharded local materialized views or intermediate result sets of the sharded sub-tables; merging the new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table. The above technical solution introduces a two-layer storage structure of materialized view log temporary table and main table, which reduces the intrusion on transactions, reduces the pressure on transaction processing and log persistence, and improves throughput in high-concurrency scenarios. The shard-level materialized view pre-computation optimization pre-generates local materialized views at the shard granularity of the base table, realizes efficient merging of incremental changes, reduces the amount of cross-shard data transmission, and accelerates materialized view calculation.

[0025] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

[0026] To more clearly illustrate the technical solutions in the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0027] Figure 1 This is a flowchart of a materialized view incremental refresh method provided by an embodiment of the present invention;

[0028] Figure 2 This is a flowchart of a materialized view incremental refresh method provided by an embodiment of the present invention;

[0029] Figure 3 This is a schematic diagram of the structure of a materialized view incremental refresh device according to an embodiment of the present invention;

[0030] Figure 4 This is a schematic diagram of the structure of an electronic device that implements the materialized view incremental refresh method according to an embodiment of the present invention. Detailed Implementation

[0031] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0032] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0033] Furthermore, it should be noted that the collection, storage, use, processing, transmission, provision, and disclosure of related data such as base tables and materialized views involved in the technical solution of this invention all comply with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0034] Figure 1 This is a flowchart illustrating a materialized view incremental refresh method according to an embodiment of the present invention. This embodiment is applicable to situations involving incremental materialized view refresh in distributed databases such as GoldenDB, and is particularly suitable for high-concurrency, distributed data analysis, and real-time processing scenarios. This method can be executed by a materialized view incremental refresh device, which can be implemented in hardware and / or software and can be configured in an electronic device that carries the materialized view incremental refresh function, such as a server. Figure 1 As shown, the method includes:

[0035] S110. Divide the base table to obtain at least two sub-tables.

[0036] In this embodiment, the base table refers to the original table that needs to be refreshed by the materialized view. The so-called fragmented sub-table refers to the sub-table obtained after fragmenting the base table.

[0037] Specifically, the base table can be divided based on a horizontal partitioning strategy, such as hash modulo, to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure.

[0038] S120. Create a materialized view of the log temporary table and the log main table for the sharded sub-tables.

[0039] In this embodiment, the temporary log table and the main log table are used to record incremental data change logs for the sharded sub-tables, respectively. Optionally, both the temporary log table and the main log table include fields such as change sequence number, operation type, data snapshot, and change transaction number. The change sequence number represents the order of data changes. The data snapshot refers to the data corresponding to each field in the sharded sub-table. The change transaction number refers to the ID of the change transaction, used to uniquely identify the change transaction.

[0040] Specifically, for each sharded sub-table, a temporary log table and a main log table for the materialized view of that sharded sub-table are constructed synchronously.

[0041] Optionally, invalid log data in the log master table of each sharded sub-table can be asynchronously and periodically cleaned up. Specifically, based on a preset cleanup strategy, such as at regular intervals (e.g., daily or every day), invalid log data in the log master table of each sharded sub-table can be cleaned up asynchronously in parallel. It is understandable that periodically cleaning up invalid log data in the log master table can reduce the storage pressure on the logs and improve the efficiency of subsequently determining incremental changes in the log master table.

[0042] S130. Create the corresponding sharded local materialized view in the data shard and register incremental refresh control information.

[0043] The incremental refresh control information includes the incremental refresh point of the change sequence number in the log master table. The incremental refresh point refers to the position recorded in the log where the incremental refresh ended at the last refresh. For example, if there are a total of 100 incremental refresh records in the log table, the last refresh was to the 50th record.

[0044] Specifically, in response to the incremental materialized view refresh creation command, a corresponding shard-local materialized view, i.e., an incremental refresh materialized view, is created in the data shard; at the same time, incremental refresh control information is automatically registered in the metadata management module, including the incremental refresh points of the log tables corresponding to each shard sub-table.

[0045] For example, if a materialized view is defined as a single-table operation logic, such as containing aggregation or projection operations, then a corresponding shard-local materialized view is created in the data shards. The data range corresponding to the shard-local materialized view is aligned with the sharded sub-table via the shard key. This ensures that the data in the sharded sub-table is consistent with the data in its corresponding materialized view.

[0046] A concrete example is a distributed database with multiple data shards, such as g1, g2, and g3. The base table is split according to certain splitting rules, and the resulting data slices, or sharded sub-tables, are stored on each shard. For instance, consider two base tables: Base table T1 contains the final exam scores of all high school students. Records for first-year high school students in T1 can be stored on shard g1, forming sharded sub-table T1G1; records for second-year high school students in T1 can be stored on shard g2, forming sharded sub-table T1G2; and records for third-year high school students in T1 can be stored on shard g3, forming sharded sub-table T1G3. Base table 2 contains the personal information (such as personal email addresses) of all high school students. Base table 2 uses a similar splitting rule as base table 1: first-year high school students' personal information is stored on shard g1, forming sharded sub-table T2G1; second-year high school students' personal information is stored on shard g2, forming sharded sub-table T2G2; and third-year high school students' personal information is stored on shard g3, forming sharded sub-table T2G3. We are preparing to send commendation emails to the top 10 students in each grade of high school in the final exams. This might involve creating a materialized view V1. V1 stores the personal information (such as name, email address, etc.) and academic performance (such as grade ranking) of these top 10 students. V1 also stores the result of a join operation between base tables T1 and T2. Shard g1 has a local materialized view V1G1, shard g2 has a local materialized view V1G2, and shard g3 has a local materialized view V1G3. V1G1 is the result of the join operation between T1G1 and T2G1, V1G2 is the result of the join operation between T1G2 and T2G2, and V1G3 is the result of the join operation between T1G3 and T2G3. V1G1, V1G2, and V1G3 are then merged into V1.

[0047] S140. Determine the incremental change set based on the incremental refresh point, the temporary log table, and the main log table.

[0048] Incremental change set refers to a set containing changed data.

[0049] Specifically, data in the temporary log table can be scanned to the main log table, and incremental change sets can be extracted from the main log table based on incremental refresh points.

[0050] S150. Refresh the sharded local materialized view based on the base tables and incremental change sets involved in the sharded local materialized view to obtain a new sharded local materialized view or intermediate result set of the sharded sub-tables.

[0051] In this embodiment, the intermediate result set refers to the result of applying the base table incremental change set to the fragmented local materialized view, such as V1G1 in the example above.

[0052] Specifically, for each sharded local materialized view, the sharded local materialized view can be refreshed based on the update method of the base table involved in the sharded local materialized view and the incremental change set, to obtain a new sharded local materialized view or intermediate result set for the sharded local materialized view.

[0053] S160. Merge the new fragmented local materialized views or intermediate result sets of at least two fragmented sub-tables to obtain the target materialized view of the base table.

[0054] The target materialized view refers to the user-defined incremental materialized view.

[0055] Specifically, the new sharded local materialized views or intermediate result sets of at least two sharded sub-tables are merged to obtain the target materialized view of the base table.

[0056] The technical solution of this invention involves dividing a base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure; creating a log temporary table and a log main table for materialized views of the sharded sub-tables; creating corresponding sharded local materialized views in the data shards and registering incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log main table; determining the incremental change set based on the incremental refresh point, the log temporary table, and the log main table; refreshing the sharded local materialized views based on the base table and the incremental change set involved in the sharded local materialized views to obtain new sharded local materialized views or intermediate result sets of the sharded sub-tables; merging the new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table. The above technical solution introduces a two-layer storage structure of materialized view log temporary table and main table, which reduces the intrusion on transactions, reduces the pressure on transaction processing and log persistence, and improves throughput in high-concurrency scenarios. The shard-level materialized view pre-computation optimization pre-generates local materialized views at the shard granularity of the base table, realizes efficient merging of incremental changes, reduces the amount of cross-shard data transmission, and accelerates materialized view calculation.

[0057] Figure 2This is a flowchart of an incremental refresh method for a materialized view according to an embodiment of the present invention. Based on the above embodiments, this embodiment further optimizes the steps of "determining the incremental change set based on the incremental refresh point, the temporary log table, and the main log table" and "refreshing the sharded local materialized view based on the base tables involved in the sharded local materialized view and the incremental change set to obtain a new sharded local materialized view or intermediate result set of the sharded sub-tables," providing an optional implementation scheme. For example... Figure 2 As shown, the method includes:

[0058] S210. Divide the base table to obtain at least two sub-tables.

[0059] Each sharded sub-table has the same table structure.

[0060] S220. Create a materialized view of the log temporary table and the log main table for the sharded sub-tables.

[0061] S230. Create the corresponding local materialized view in the data shard and register incremental refresh control information.

[0062] The incremental refresh control information includes the incremental refresh points of the change sequence number in the log master table.

[0063] S240. Determine the incremental change set based on the incremental refresh point, the temporary log table, and the main log table.

[0064] An optional approach involves determining the incremental change set based on the incremental refresh point, the temporary log table, and the main log table. This includes: scanning the data change records in the temporary log table to obtain the data change records; aggregating the data change records according to the transaction isolation granularity to obtain at least one transaction group; wherein the changed data within a transaction group is sorted in ascending order of change sequence number; wherein the transaction groups are sorted according to the transaction commit time sequence; migrating at least one transaction group to the main log table; obtaining the global consistency snapshot point of the base table; and extracting the incremental change set from the main log table based on the incremental refresh point and the global consistency snapshot point.

[0065] Among them, the globally consistent snapshot point refers to the state of the distributed sharded sub-tables at the same absolute time.

[0066] The system periodically scans the temporary log table for committed transaction data change records. From a transaction perspective, these records are aggregated according to transaction isolation granularity to obtain at least one transaction group. Within each transaction group, changed data is sorted in ascending order of change sequence number, while different transaction groups are sorted according to their transaction commit time. This at least one transaction group is then migrated to the main log table. The metadata service is queried to obtain the incremental refresh points of the sharded sub-tables and the globally consistent snapshot point of the base table, establishing a cross-shard logical clock benchmark. Based on the incremental refresh points and the globally consistent snapshot point, the incremental change set is extracted from the main log table. A shard-level locking coordination mechanism is then used to atomically update the latest incremental refresh point to the metadata storage.

[0067] Understandably, the asynchronous migration design of the double-buffered log, namely the main log table and the temporary log table, achieves physical isolation between transaction commit and log persistence. This architecture can adapt to dynamically changing distributed data environments and meet the combined requirements of data freshness, system stability, and cost efficiency in scenarios such as lake warehouse integration and real-time risk control.

[0068] S250. Refresh the fragmented local materialized view based on the base table and incremental change set involved in the fragmented local materialized view to obtain a new fragmented local materialized view or intermediate result set of the fragmented sub-tables.

[0069] Alternatively, if the sharded local materialized view involves a base table, the sharded local materialized view of the sharded sub-table is updated based on the incremental change set to obtain a new sharded local materialized view of the sharded sub-table. Specifically, data in the incremental change set is mapped to the sharded local materialized view of the sharded sub-table to obtain a new sharded local materialized view of the sharded sub-table.

[0070] Another option is to obtain an intermediate result set of the sharded sub-tables based on the incremental change set, the base table data at the globally consistent snapshot point, and the sharded local materialized view before the current refresh, if the sharded sub-table involves multiple base tables. Here, a relational materialized view refers to a sharded sub-table whose data update operation is related to other sharded sub-tables, such as a join operation.

[0071] Specifically, based on the base table data of the globally consistent snapshot point and the sharded local materialized view before this refresh, the data is updated according to the incremental change set to obtain the intermediate result set of the sharded sub-table.

[0072] Understandably, incremental change capture and cross-shard timing alignment are achieved by replacing the global lock dependency through the collaborative mechanism of sharded local sequences and globally consistent snapshots.

[0073] S260. Merge new fragmented local materialized views or intermediate result sets of at least two fragmented sub-tables to obtain the target materialized view of the base table.

[0074] The technical solution of this invention involves dividing a base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure; creating a log temporary table and a log main table for materialized views of the sharded sub-tables; creating corresponding sharded local materialized views in the data shards and registering incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log main table; determining the incremental change set based on the incremental refresh point, the log temporary table, and the log main table; refreshing the sharded local materialized views based on the base table and the incremental change set involved in the sharded local materialized views to obtain new sharded local materialized views or intermediate result sets of the sharded sub-tables; merging the new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table. The above technical solution introduces a two-layer storage structure of materialized view log temporary table and main table, which reduces the intrusion on transactions, reduces the pressure on transaction processing and log persistence, and improves throughput in high-concurrency scenarios. The shard-level materialized view pre-computation optimization pre-generates local materialized views at the shard granularity of the base table, realizes efficient merging of incremental changes, reduces the amount of cross-shard data transmission, and accelerates materialized view calculation.

[0075] Existing materialized view incremental refresh technologies face significant bottlenecks in high-concurrency, distributed scenarios. Traditional solutions rely on global transaction sequences to coordinate cross-shard change pipelines, leading to intensified global lock contention and limited shard parallel efficiency. Furthermore, cross-shard incremental merging often requires full data transmission or complex global aggregation, resulting in wasted network and computing resources. In addition, mainstream single-level materialized view logs are prone to transaction commit delays and persistence performance imbalances under high concurrency. This invention proposes a collaborative mechanism between shard-local sequences and globally consistent snapshots to replace global lock dependencies, achieving incremental change capture and cross-shard timing alignment. Combined with a shard-level pre-computation model and multi-level aggregation operators, it reduces cross-node transmission overhead while accelerating materialized view computation. Simultaneously, it introduces a double-buffered log asynchronous migration design to isolate transaction processing and log persistence pressure, improving throughput. This invention achieves high-performance, low-latency distributed materialized view updates through double-buffered log design, shard-level timing control, shard-level incremental computation, and multi-level aggregation optimization.

[0076] Figure 3This is a schematic diagram of a materialized view incremental refresh device according to an embodiment of the present invention. This embodiment is applicable to situations involving incremental materialized view refresh in distributed databases such as GoldenDB, and is particularly suitable for high-concurrency, distributed data analysis, and real-time processing scenarios. This materialized view incremental refresh device can be implemented in hardware and / or software, and can be configured in an electronic device that carries the materialized view incremental refresh function, such as a server. Figure 3 As shown, the device includes:

[0077] The base table partitioning module 310 is used to partition the base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure.

[0078] Log creation module 320 is used to create materialized views of temporary log tables and main log tables for sharded sub-tables;

[0079] The materialized view generation module 330 is used to create corresponding shard-local materialized views in data shards and register incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh points of the change sequence number of the log master table;

[0080] The incremental change set determination module 340 is used to determine the incremental change set based on the incremental refresh point, the temporary log table, and the main log table.

[0081] The materialized view refresh module 350 is used to refresh the sharded local materialized view based on the base table and incremental change set involved in the sharded local materialized view, so as to obtain a new sharded local materialized view or intermediate result set of the sharded sub-tables.

[0082] The target materialized view determination module 360 ​​is used to merge new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table.

[0083] The technical solution of this invention involves dividing a base table to obtain at least two sharded sub-tables; each sharded sub-table has the same table structure; creating a log temporary table and a log main table for materialized views of the sharded sub-tables; creating corresponding sharded local materialized views in the data shards and registering incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log main table; determining the incremental change set based on the incremental refresh point, the log temporary table, and the log main table; refreshing the sharded local materialized views based on the base table and the incremental change set involved in the sharded local materialized views to obtain new sharded local materialized views or intermediate result sets of the sharded sub-tables; merging the new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table. The above technical solution introduces a two-layer storage structure of materialized view log temporary table and main table, which reduces the intrusion on transactions, reduces the pressure on transaction processing and log persistence, and improves throughput in high-concurrency scenarios. The shard-level materialized view pre-computation optimization pre-generates local materialized views at the shard granularity of the base table, realizes efficient merging of incremental changes, reduces the amount of cross-shard data transmission, and accelerates materialized view calculation.

[0084] Optionally, both the temporary log table and the main log table include at least one of the following fields: change sequence number, operation type, data snapshot, and change transaction number; whereby the change sequence number represents the order of data changes.

[0085] Optionally, the incremental change set determination module 340 is used for:

[0086] Scan the temporary log table to obtain the data change records;

[0087] Data change records are aggregated according to transaction isolation granularity to obtain at least one transaction group; within a transaction group, changed data is sorted in ascending order of change sequence number; and among transaction groups, they are sorted according to transaction commit time.

[0088] Migrate at least one transaction group to the log master table;

[0089] Obtain a globally consistent snapshot of the base table;

[0090] Based on the incremental refresh point and the global consistency snapshot point, extract the incremental change set from the log master table.

[0091] Optionally, the materialized view refresh module 350 is used for:

[0092] If the sharded local materialized view involves a base table, then the sharded local materialized view of the sharded sub-table is updated according to the incremental change set to obtain a new sharded local materialized view of the sharded sub-table.

[0093] If the sharded local materialized view involves multiple base tables, an intermediate result set of the sharded sub-tables is obtained based on the incremental change set, the base table data of the globally consistent snapshot point, and the sharded local materialized view before this refresh.

[0094] Optionally, the device also includes a log cleanup module for:

[0095] Asynchronously and periodically clean up invalid log data in the main log table of the sharded sub-tables.

[0096] Optionally, the data range corresponding to a sharded local materialized view is aligned with the sharded sub-table via the sharding key.

[0097] The materialized view incremental refresh device provided in this embodiment of the invention can execute the materialized view incremental refresh method provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the method execution.

[0098] According to embodiments of the present invention, the present invention also provides an electronic device, a readable storage medium, and a computer program product.

[0099] Figure 4 This is a schematic diagram of the structure of an electronic device that implements the materialized view incremental refresh method according to an embodiment of the present invention. Figure 4 A schematic diagram of an electronic device 10, which can be used to implement embodiments of the present invention, is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0100] like Figure 4 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0101] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0102] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as the materialized view incremental refresh method.

[0103] In some embodiments, the materialized view incremental refresh method may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the materialized view incremental refresh method described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to perform the materialized view incremental refresh method by any other suitable means (e.g., by means of firmware).

[0104] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0105] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0106] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0107] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0108] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0109] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0110] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0111] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for incrementally refreshing a materialized view, characterized in that, include: Divide the base table to obtain at least two sub-tables; Each shard sub-table has the same table structure; Create a materialized view of the log temporary table and the log main table for the sharded sub-tables; Create a corresponding shard-local materialized view in the data shard and register incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh point of the change sequence number of the log master table; Scan the temporary log table to obtain the data change records; Data change records are aggregated according to transaction isolation granularity to obtain at least one transaction group; within a transaction group, changed data is sorted in ascending order of change sequence number; and among transaction groups, they are sorted according to transaction commit time. Migrate at least one transaction group to the log master table; Obtain a globally consistent snapshot of the base table; Based on the incremental refresh point and the global consistency snapshot point, extract the incremental change set from the log master table; The sharded local materialized view is refreshed based on the base table involved in the sharded local materialized view and the incremental change set to obtain a new sharded local materialized view or intermediate result set of the sharded sub-table; Merge new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table.

2. The method according to claim 1, characterized in that, Both the temporary log table and the main log table include at least one of the following fields: change sequence number, operation type, data snapshot, and change transaction number; wherein, the change sequence number represents the order of data changes.

3. The method according to claim 1, characterized in that, The sharded local materialized view is refreshed based on the base tables involved in the sharded local materialized view and the incremental change set to obtain a new sharded local materialized view or intermediate result set of the sharded sub-tables, including: If the sharded local materialized view involves a base table, then the sharded local materialized view of the sharded sub-table is updated according to the incremental change set to obtain a new sharded local materialized view of the sharded sub-table. If the sharded local materialized view involves multiple base tables, an intermediate result set of the sharded sub-tables is obtained based on the incremental change set, the base table data of the global consistency snapshot point, and the sharded local materialized view before this refresh.

4. The method according to claim 1, characterized in that, Also includes: Asynchronously and periodically clean up the invalid log data in the main log table of the sharded sub-tables.

5. The method according to any one of claims 1-4, characterized in that, The data range corresponding to the sharded local materialized view is aligned with the sharded sub-table via the sharding key.

6. A materialized view incremental refresh device, characterized in that, include: The base table partitioning module is used to partition the base table to obtain at least two sub-tables. Each shard sub-table has the same table structure; The log creation module is used to create a temporary log table and a main log table for materialized views of the sharded sub-tables; The materialized view generation module is used to create corresponding shard-local materialized views in data shards and register incremental refresh control information; wherein, the incremental refresh control information includes the incremental refresh points of the change sequence number of the log master table; The incremental change set determination module is used to scan the data change records in the temporary log table to obtain the data change records; Data change records are aggregated according to transaction isolation granularity to obtain at least one transaction group; within a transaction group, changed data is sorted in ascending order of change sequence number; and among transaction groups, they are sorted according to transaction commit time. Migrate at least one transaction group to the log master table; Obtain a globally consistent snapshot of the base table; Based on the incremental refresh point and the global consistency snapshot point, extract the incremental change set from the log master table; The materialized view refresh module is used to refresh the sharded local materialized view based on the base table involved in the sharded local materialized view and the incremental change set, so as to obtain a new sharded local materialized view or intermediate result set of the sharded sub-table; The target materialized view determination module is used to merge new sharded local materialized views or intermediate result sets of at least two sharded sub-tables to obtain the target materialized view of the base table.

7. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the materialized view incremental refresh method according to any one of claims 1-5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the materialized view incremental refresh method according to any one of claims 1-5.

9. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the materialized view incremental refresh method according to any one of claims 1-5.

Images