Method and device for updating materialized view, medium and electronic equipment

Abstract

The invention provides a materialized view updating method and device, a medium and electronic equipment. In the method, a temporary materialized view storage table can be introduced into a storage engine which only supports additional writing based on LSM-Tree and the like, so that after it is detected that a data table is changed, analysis and influence analysis can be carried out on change operation, and all materialized views stored in the temporary materialized view storage table are stored in the storage engine. And selecting the to-be-updated materialized view influenced by the change. Then, incremental updating can be performed on the to-be-updated materialized view according to the data change content, and an updating result of the to-be-updated materialized view is stored in the temporary materialized view storage table instead of directly modifying original materialized view data stored in the materialized view storage table. In the process, as the temporary materialized view storage table is not constrained by the additional write rule, efficient insertion, update and deletion operations can be supported, and incremental update of the materialized view can be realized on the premise of not damaging the performance of a bottom-layer storage structure.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of database technology, and in particular, to a materialized view updating method, device, medium, and electronic device. Background Art

[0002] In recent years, to address the performance challenges of massive data writes, storage engines based on an append-only storage model (such as LSM-Tree and Bitcask) have been widely used in database systems. A core feature of these storage engines is data immutability: once data is persisted to disk, its original physical location cannot be directly modified or deleted. Using an append-only storage model transforms random writes to disk into sequential writes, significantly improving write performance and simplifying concurrency control. This effectively mitigates performance bottlenecks in database systems facing high-concurrency write operations. However, this data immutability also limits certain database features that require efficient in-place updates, such as the incremental update mechanism for materialized views. The following article uses LSM-Tree as an example to further analyze this contradiction.

[0003] As a highly efficient storage structure, LSM-Tree has been widely used in database systems, particularly in scenarios with write-intensive workloads. Compared to traditional index structures such as B-Trees and B+Trees, LSM-Trees utilize an "append-only write" approach when writing data, effectively overcoming the performance bottlenecks caused by high write volume in high-concurrency scenarios. Specifically, when new data is inserted or existing data is updated, the database system does not directly modify existing data blocks on disk. Instead, it sequentially appends these changes to a new storage location. This design avoids the frequent random I / O operations associated with traditional B-Trees and B+Trees, reduces disk seek time, and significantly improves write throughput. Furthermore, since it does not involve in-place modifications to existing data, append-only writes reduce lock contention in concurrency control, further improving the system's performance in high-concurrency scenarios with high write volume.

[0004] Materialized views are a technology used to accelerate the execution of complex queries. By pre-calculating and persistently storing complex query results based on a table, subsequent identical queries can directly return the pre-stored results without having to repeat the complex query calculations. To ensure data consistency in the materialized views when table data changes, an incremental update strategy is often employed. Specifically, the materialized views affected by the table changes are identified as the materialized views to be updated. This allows for in-place updates of only those materialized views to be updated, rather than a full rebuild. This significantly reduces computational and I / O overhead.

[0005] However, if the container table used to store materialized views uses an LSM-Tree structure and therefore can only support data writing in an append-only manner, and the file becomes immutable once written, incremental updates of materialized views require in-place updates, that is, directly locating and modifying specific record rows in the materialized view affected by the data table changes (for example, updating the aggregate statistics of a user), this makes the incremental update mechanism of materialized views difficult to implement. Summary of the Invention

[0006] In view of this, one or more embodiments of this specification provide the following technical solutions: According to a first aspect of one or more embodiments of this specification, a method for updating a materialized view is provided, including: Receive data operation statements for the data table; Executing the data operation statement to change data in the data table; and A materialized view to be updated is selected from each materialized view stored in a temporary materialized view storage table; the temporary materialized view storage table is obtained by pre-copying at least part of the materialized views in the materialized view storage table; the materialized view storage table is configured to support only append writes; the temporary materialized view storage table is not subject to append write rules; the materialized view to be updated is a materialized view affected by the data change; The to-be-updated materialized view is updated according to data changes in the data table to obtain a temporary materialized view storage table after incremental update; wherein, when a data query is performed in response to a received data query statement, the temporary materialized view storage table after incremental update is used to replace the materialized view storage table to provide at least part of the query result.

[0007] According to a second aspect of one or more embodiments of this specification, a data query method is proposed, including: Receive data query statements for the data table; The data query statement is executed, and the materialized view involved in the data query statement is queried from the materialized view storage table and / or the temporary materialized view storage table, and a query result is obtained based on the materialized view involved in the data query statement; the temporary materialized view storage table contains at least part of the incrementally updated materialized view.

[0008] According to a third aspect of one or more embodiments of this specification, a device for updating a materialized view is provided, including: A receiving module, used for receiving data operation statements for a data table; a first execution module, configured to execute the data operation statement to modify data in the data table; and select a materialized view to be updated from each materialized view stored in a temporary materialized view storage table; the temporary materialized view storage table is obtained by pre-copying at least some of the materialized views in the materialized view storage table; the materialized view storage table is configured to support only append writes; the temporary materialized view storage table is not subject to append write rules; the materialized view to be updated is a materialized view affected by the data modification; A second execution module is configured to update the to-be-updated materialized view according to data changes in the data table to obtain a temporary materialized view storage table after incremental update; wherein, when performing a data query in response to a received data query statement, the temporary materialized view storage table after incremental update is used to replace the materialized view storage table to provide at least a portion of the query result.

[0009] According to a fourth aspect of one or more embodiments of this specification, an electronic device is proposed, comprising: a processor; a memory for storing processor-executable instructions; wherein the processor implements the steps of the above-mentioned materialized view update method by running the executable instructions.

[0010] According to a fifth aspect of one or more embodiments of this specification, a computer-readable storage medium is provided, on which computer instructions are stored. When the instructions are executed by a processor, the steps of the above-mentioned materialized view update method are implemented.

[0011] According to a sixth aspect of one or more embodiments of this specification, a computer program product is proposed, including a computer program / instruction, which implements the steps of the above-mentioned materialized view update method when executed by a processor.

[0012] From the above embodiments, the present specification first receives a data operation statement for a data table, executes the data operation statement to make data changes to the data table, and selects a to-be-updated materialized view from each materialized view stored in a temporary materialized view storage table, wherein the temporary materialized view storage table is obtained by copying at least part of the materialized view storage table in advance, the materialized view storage table is set to support only append write, the temporary materialized view storage table is not subject to the append write rule, and the to-be-updated materialized view is a materialized view affected by the data changes. Then, the to-be-updated materialized view is updated according to the data changes of the data table to obtain an incrementally updated temporary materialized view storage table, wherein the incrementally updated temporary materialized view storage table is used to replace the materialized view storage table to provide at least part of the query result when responding to the received data query statement.

[0013] In this method, a temporary materialized view storage table can be introduced in a storage engine that only supports append write based on LSM-Tree or the like, so that after detecting a change in a data table, the change operation can be parsed and analyzed, and the to-be-updated materialized view affected by the change can be selected from each materialized view stored in the preset temporary materialized view storage table. Subsequently, the to-be-updated materialized view can be incrementally updated according to the data change content, and the update result of the to-be-updated materialized view can be stored in the temporary materialized view storage table instead of directly modifying the original materialized view data stored in the materialized view storage table. In this process, since the temporary materialized view storage table is not subject to the append write rule, it can support efficient insertion, update and deletion operations, so that the incremental update of the materialized view can be realized without damaging the performance of the underlying storage structure. BRIEF DESCRIPTION OF DRAWINGS

[0014] Figure 1 FIG. 1 is a flowchart of a materialized view updating method according to an example embodiment.

[0015] Figure 2 FIG. 2 is a process diagram of batch delayed update based on a materialized view log according to an example embodiment.

[0016] Figure 3 FIG. 3 is a schematic diagram of a materialized view log according to an example embodiment.

[0017] Figure 4 FIG. 4 is a schematic diagram of a full update process according to an example embodiment.

[0018] Figure 5 FIG. 5 is a flowchart of a data query method according to an example embodiment.

[0019] Figure 6It is a schematic structural diagram of a device provided by an exemplary embodiment.

[0020] Figure 7 The present invention is a block diagram of a materialized view updating device provided by an exemplary embodiment.

[0021] Figure 8 It is a block diagram of a data query device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0022] To make the objectives, technical solutions, and advantages of this specification more clear, the following will clearly and completely describe the technical solutions of this specification in conjunction with the specific embodiments of this specification and the corresponding drawings. Obviously, the embodiments described are only part of the embodiments of this specification, not all of the embodiments. Based on the embodiments in this specification, all other embodiments obtained by ordinary technicians in this field without making any creative efforts are within the scope of protection of this specification.

[0023] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0024] Currently, traditional database storage engines often use data structures such as B-trees and B+-trees to organize and manage data on persistent storage devices like hard drives and solid-state drives. These structures allow data to be distributed in a balanced manner within the tree, ensuring logarithmic query performance. They are well-suited to the random access patterns of disk storage and can effectively support point and range queries in transaction processing.

[0025] However, in high-concurrency scenarios, especially when facing large numbers of write operations (such as inserts, updates, and deletes), B-tree and B+-tree structures experience certain performance bottlenecks. Because each write operation may involve modifying nodes at multiple tree levels, this can lead to page splits, lock contention, and other issues. This results in frequent random disk writes and increased concurrency control overhead, impacting overall throughput and response latency.

[0026] To address these challenges, an increasing number of database systems are adopting LSM-Tree (Log-Structured Merge-Tree) as the core data structure of their storage engines to improve performance in high-concurrency, high-write-load scenarios. By converting random write operations into efficient append writes, LSM-Tree significantly reduces disk I / O overhead, enabling fast data insertion and updates. Furthermore, through background merging and compression mechanisms, LSM-Tree further optimizes storage space and improves read performance. Therefore, the LSM-Tree structure is particularly well-suited for write-intensive and large-scale data management scenarios, and has been widely used in modern distributed databases, NoSQL systems, and some new relational databases.

[0027] However, a key feature of append-only writes is immutability: once data is written to disk, it cannot be directly modified or deleted. While this feature improves write efficiency and system stability, it also limits certain database functions that require frequent updates, such as the incremental update mechanism for materialized views.

[0028] Specifically, in database systems, materialized views are a technology used to accelerate the execution of complex queries. It pre-calculates and persistently stores complex query results based on data tables, so that subsequent identical queries can directly return the pre-stored query results without having to repeat the complex query calculation.

[0029] For example, for a complex query like "SELECT SUM(sales), region FROM orders GROUP BY regio," which groups the orders table by region and calculates the total sales of each region, the database can pre-execute this query and store the results as a materialized view. When the same or similar query is subsequently executed, the database system can directly utilize the results of this materialized view for fast responses, significantly improving query performance.

[0030] As can be seen, materialized views are essentially an optimization strategy that trades space for time. However, to ensure the accuracy of query results, materialized views must be updated accordingly when data in the table changes (such as inserts, updates, or deletes). Especially in scenarios that support incremental updates, materialized views often require fine-grained local updates rather than full rebuilds every time.

[0031] At this time, the immutability feature of the LSM-Tree becomes a limitation. Since the materialized view storage table in the LSM-Tree for storing the materialized view is immutable, once written to the disk, the records therein cannot be directly modified. This makes the materialized view face challenges when performing incremental updates: every time the materialized view is updated, the existing materialized view cannot be updated in place, but a new version of the materialized view storage table needs to be created, thereby bringing an additional write amplification problem.

[0032] The following detailed description of the concepts of data table, materialized view storage table and temporary materialized view storage table involved in the present specification is as follows: Data table: a basic structure for organizing, storing and managing data. It usually stores data in the form of rows and columns, similar to a spreadsheet (such as an Excel table), and is one of the most common data storage units in a database. Each row in the data table represents a record or an entity, for example: in a data table for storing student information, each row of data is the student information of a student. Each column represents an attribute or field, for example: in a data table for storing student information, the first column can be the name field in the student information, the second column can be the age field in the student information, and the third column can be the student ID field in the student information, etc.

[0033] Materialized view storage table: a table for storing the results of a materialized view derived from a data table. For example: if the execution results of the complex query statement "SELECT SUM(sales), region FROM orders GROUP BY regio" are stored as a materialized view, the table used to store this materialized view is a materialized view storage table.

[0034] Temporary materialized view storage table: a table for temporarily storing data during incremental updates of a materialized view.

[0035] Based on this, the present specification provides a method for updating a materialized view, and the technical solutions provided by the embodiments of the present specification are described in detail below with reference to the accompanying drawings.

[0036] Figure 1 FIG. 1 is a flowchart of a method for updating a materialized view provided by an exemplary embodiment, which includes: S100: receiving a data manipulation statement for a data table.

[0037] S102: executing the data manipulation statement to make data changes to the data table.

[0038] In the present specification, the execution subject for implementing the materialized view update method can be a database server, a computing node in a distributed database cluster, or a data processing unit of a cloud database service. For ease of description, the materialized view update method provided in the present specification will be described below by taking a database server as an example.

[0039] Specifically, after receiving a data operation statement (such as an INSERT statement for inserting new data into a data table, an UPDATE statement for modifying existing data in a data table, a DELETE statement for deleting existing data in a data table, etc.) sent by a user through a client, the database server first performs the data operation statement to change the corresponding data in the data table.

[0040] Subsequently, the database server can parse and analyze the change operation to identify the range of materialized views affected by the data change. For example, if the data change involves a record in an order table, it only affects the content of the materialized view related to the order.

[0041] Further, in the case where the database server determines that the materialized view needs to be updated, since the materialized view storage table used to store the materialized view adopts a storage structure (such as a storage engine based on LSM-Tree) that only supports append-only writing, the original materialized view cannot be directly modified or overwritten. To solve this limitation, the database server can access a pre-created temporary materialized view storage table for temporarily storing the update results of the materialized view.

[0042] Specifically, the database server can access the pre-created temporary materialized view storage table, filter out the materialized view entries affected by the current data change from the temporary materialized view storage table, and perform update calculation on at least part of the materialized view entries affected by the current data change according to the change content of the data table. The updated results are temporarily stored in the temporary materialized view storage table. When the database server subsequently receives a data query request, it can preferentially obtain the updated materialized view entries from the temporary materialized view storage table, and for the part that has not been updated, it can still read from the materialized view storage table, so as to maintain the query efficiency while realizing incremental update of the materialized view.

[0043] The materialized view storage table mentioned above can be a container table using LSM-Tree structure for persistently storing materialized views, which only allows writing new data in an append-only manner to ensure high write-throughput capability of the database system.

[0044] The temporary materialized view storage table described above can be formed in advance by copying at least part of the materialized view storage table, and the temporary materialized view storage table is not subject to the append write rule.

[0045] In actual application scenarios, the database server can also adopt a strategy of separating baseline data and incremental data for storage, that is, storing baseline data (that is, historical, stable, and low-frequency access data) in a disk, and temporarily storing incremental data (that is, frequently updated and frequently accessed latest data) in a memory. In this way, read-write separation is achieved, so that all data modification operations (DML, such as insertion, update, and deletion) only act on the incremental data in the memory, thereby converting these operations into pure memory operations, and significantly improving the write performance and response speed of the system.

[0046] Based on this, the database server can store the materialized view storage table in the above content in a disk to take advantage of the large capacity and persistence of the disk to provide reliability protection for the materialized view storage table. The temporary materialized view storage table is stored in the memory to quickly respond to data changes for incremental updates.

[0047] S104: selecting a to-be-updated materialized view from each materialized view stored in the temporary materialized view storage table; the temporary materialized view storage table is obtained by copying at least part of the materialized view storage table in advance; the materialized view storage table is set to support only append write; the temporary materialized view storage table is not subject to the append write rule; and the to-be-updated materialized view is a materialized view affected by the data change.

[0048] S106: updating the to-be-updated materialized view according to the data change of the data table, to obtain an incremental updated temporary materialized view storage table; wherein, when responding to a received data query statement to perform data query, the incremental updated temporary materialized view storage table is used to replace the materialized view storage table to provide at least part of the query result.

[0049] In this specification, when the database server performs incremental update on the materialized view, the database server can extract change information such as the type of data change corresponding to the data operation statement (for example: insertion, update, or deletion), the table name of the data table involved in the data change, the change field name of the materialized view storage table involved in the data change, and the values before and after the data change from the analysis result corresponding to the data operation statement, so as to identify the range of the materialized view affected by the data change according to the extracted change information.

[0050] Furthermore, the database server can select a materialized view to be updated from the materialized views stored in the preset temporary materialized view storage table based on the identified materialized view range affected by the data change, and update at least part of the data in the materialized view to be updated based on the data change to obtain the incrementally updated temporary materialized view storage table.

[0051] As can be seen from the above, database servers can introduce temporary materialized view storage tables in storage engines that only support append-only writes, such as those based on LSM-Tree. This allows them to parse and analyze the changes after detecting a data table change, identify the range of materialized views affected by the change, and filter the corresponding materialized views to be updated from the temporary materialized view storage table. Subsequently, these materialized views to be updated can be incrementally updated based on the data changes, and the updated results can be stored in the temporary materialized view storage table, rather than directly modifying the original materialized view data stored in the materialized view storage table. Because the temporary materialized view storage table is not subject to append-only write rules, it can support efficient insert, update, and delete operations, thereby enabling incremental updates of materialized views without compromising the write performance of the underlying storage structure.

[0052] It should be noted that the materialized view data stored in the temporary materialized view storage table can be either the entire content of the materialized view stored in the materialized view storage table or a subset thereof, depending on actual needs. In actual application scenarios, during the initial creation of the materialized view storage table, the temporary materialized view storage table can completely replicate the entire content of the materialized view stored in the materialized view storage table to ensure rapid response to all possible data changes during this initial phase. As materialized view updates continue, the database server can analyze and collect statistics on historical data changes to identify at least some materialized view entries in the materialized view stored in the temporary materialized view storage table that have extremely low change frequency or have not changed for a long time. These materialized view entries with extremely low change probability can be gradually removed from the temporary materialized view storage table and transferred to the materialized view storage table for persistent storage. At the same time, the temporary materialized view storage table will only retain those materialized view entries that are likely to change frequently, thereby achieving dynamic updates and lightweight maintenance of the temporary materialized view storage table data.

[0053] In addition, in order to further improve the traceability and execution efficiency of incremental updates of materialized views, the database server can also first record the incremental change information corresponding to the data operation statement into the preset materialized view log to perform delayed batch updates on the materialized view, as shown in the following example: Figure 2 shown.

[0054] Figure 2 The present invention is a schematic diagram of a process for performing batch delayed updates based on materialized view logs provided by an exemplary embodiment.

[0055] Combine Figure 2 It can be seen that the database server can record the incremental change information corresponding to the data operation statement in the preset materialized view log every time the data in the data table is changed. When it is determined that the preset incremental update conditions are met, the materialized view to be updated is selected from the materialized views stored in the preset temporary materialized view storage table based on the unprocessed incremental change information in the materialized view log.

[0056] Incremental change information represents data changes in a table caused by a data manipulation statement. This incremental change information may include the data value of the change. In practical applications, incremental change information may also include information such as the type of data change, the time of the change, and the identifiers of the affected rows.

[0057] The aforementioned incremental update conditions may refer to criteria for triggering incremental updates of materialized views. This conditional judgment can avoid resource waste caused by frequent refreshes. The incremental update conditions can be configured based on actual needs. For example, the incremental update condition is considered satisfied when the number of unprocessed incremental changes in the materialized view log exceeds a first threshold and is less than a second threshold. Another example is when the time interval since the last incremental update meets a preset time threshold.

[0058] It should be noted that a processing status identification field may also be set in the materialized view log. Each field value under the processing status identification field is a processing status identification of each incremental change information. Figure 3 shown.

[0059] Figure 3 The figure is a schematic diagram of a materialized view log provided by an exemplary embodiment.

[0060] Combine Figure 3 It can be seen that for data table t1 containing two columns c1 and c2, and materialized view m1 created on data table t1, m1 records incremental update information through the materialized view log mlog$_t1. If the following data operation statement is received: Insert into t1 value (3, 4) Insert into t1 value (5, 6) Insert into t1 value (7, 8) When data in the data table is changed according to the above data operation statement, the materialized view log corresponding to column c1 can record three rows of incremental update information, namely, the data value 3 of the data change after executing Insert into t1 value (3, 4), and its corresponding processing status identifier is N (that is, non-unprocessed incremental change information), the data value 5 of the data change after executing Insert into t1 value (5, 6), and its corresponding processing status identifier is N, and the data value 7 of the data change after executing Insert into t1 value (7, 8), and its corresponding processing status identifier is N.

[0061] On this basis, the database server can determine the amount of unprocessed incremental change information in the materialized view log according to the processing status identifier of each incremental change information contained in the materialized view log, and use it as the first target value. Then, when it is determined that the first target value exceeds the preset incremental update condition value, the database server can select the materialized view to be updated from the materialized views stored in the preset temporary materialized view storage table according to the unprocessed incremental change information in the materialized view log.

[0062] In addition, in actual application scenarios, in order to improve the efficiency of incremental updates of materialized views, the above-mentioned materialized view log can also include multiple incremental change information partitions, where different incremental change information partitions are obtained by dividing each incremental change information according to preset division rules, and the unprocessed incremental change information contained in different incremental change information partitions can be updated in parallel to the materialized view to be updated (that is, the tasks for incrementally updating the unprocessed incremental change information contained in each incremental change information partition to the materialized view to be updated can be executed independently and without interfering with each other).

[0063] Among them, the above-mentioned preset division rules are used to divide change operations with the same attributes into the same incremental change information partition. The above-mentioned preset division rules may include: division according to at least one dimension of data change time range, data table partition key, and data change operation type.

[0064] Specifically, when a data table changes, the database server can divide the incremental change information into different incremental change information partitions according to the preset division rules. For example: when divided according to the data change time range, changes within the same hour are classified into the same partition; when divided according to the data table partition key, changes involving the same primary key range can be classified into the same partition. Among them, each partition maintains an independent processing status identifier. When the incremental refresh conditions are met, the database server can start multiple processing threads to read the unprocessed change information of different partitions in parallel. Each thread selects the materialized view to be updated from the materialized views stored in the preset temporary materialized view storage table based on the incremental change information in the corresponding incremental change information partition, and then updates the materialized view to be updated.

[0065] Among them, since the update operation of updating the unprocessed incremental change information contained in different incremental change information partitions to the materialized view to be updated is isolated from each other at the physical storage level, no data competition or lock conflict will occur during parallel processing, thereby achieving improved update efficiency.

[0066] In addition, to reduce the storage overhead of materialized view logs, the database server can also compress and store the materialized view logs while ensuring data integrity and parsability, thereby reducing the storage space occupied by the log files.

[0067] Furthermore, when the database server detects that the preset data merge conditions are met, the current data in the materialized view storage table can be completely copied to the storage device where the temporary materialized view storage table is located to form the original table. The original table and the temporary materialized view storage table can then be merged to obtain a merged temporary materialized view storage table. After the merge is completed, the merged temporary materialized view storage table can be used to replace the materialized view storage table.

[0068] The above data merge conditions can be set based on actual needs. For example, the data merge condition is considered satisfied when the preset daily merge time window is reached. This time window can be when the database server is not busy, such as from 12:00 to 18:00 every day. Another example is when the amount of data in the temporary materialized view storage table exceeds a preset data volume threshold.

[0069] The database server merges the original table with the temporary materialized view storage table to obtain the merged temporary materialized view storage table by comparing the data version differences between the two tables, overwriting the new or modified data in the temporary materialized view storage table to the corresponding position of the original table copy, and finally generating the merged temporary materialized view storage table.

[0070] From the above, the database server can integrate the incremental update data in the temporary materialized view storage table with the original data into a new materialized view storage table version through the merge operation, ensuring the consistency of the materialized view data and the data table. Thus, the system load caused by frequent full refresh can be reduced, and the resource utilization can be further optimized and the query response efficiency can be improved by performing the merge operation during a low load period.

[0071] In addition, since the incremental update needs to analyze the impact range of the received data query statement each time to determine the materialized view to be updated, when the update frequency of the materialized view exceeds the preset frequency threshold (it can be understood that when a large number of update operations of the materialized view occur in a short time), or when the materialized view log is too large, the efficiency of the incremental update can be reduced. Based on this, the database server can also perform full update on the materialized view storage table, as shown in Figure 4

[0072] Figure 4 FIG. 1 is a schematic diagram of a full update process provided by an example embodiment.

[0073] In combination with Figure 4 It can be seen that when it is determined that the full update condition is met, the database server can copy the materialized view storage table to the storage device where the temporary materialized view storage table is located to obtain a full update table. Then, at least part of the data query statements used to create the materialized view can be executed again to query the updated query results from the data table, and the updated query results can be overwritten and written into the full update table to update the full update table, and the updated full update table can be used to replace the materialized view storage table.

[0074] The full update condition can be set according to actual needs, for example, if the update frequency of the materialized view exceeds the preset frequency threshold, it can be considered that the preset full update condition is met. For another example, if the number of unprocessed incremental change information contained in the materialized view log exceeds the preset number threshold, it can be considered that the preset full update condition is met. For another example, if the proportion of the number of updated materialized view entries in the temporary materialized view storage table exceeds the preset proportion threshold, it can be considered that the preset full update condition is met.

[0075] Further, when the database server receives a data query statement (such as a SELECT statement) for the data table, the data query statement can be executed to query the materialized view involved in the data query statement from the materialized view storage table and / or the temporary materialized view storage table for storing the materialized view, and obtain the query result according to the materialized view involved in the data query statement. ​

[0076] In practical application scenarios, if the data query statement received by the database server is exactly the same as the data query statement used when creating the materialized view, the materialized view involved in the data query statement can be directly returned as the query result of the data query statement without additional calculation.

[0077] On the other hand, if the data query statement received by the database server is further data processing (such as adding filtering conditions, sorting, aggregation function nesting, etc.) based on the data query statement used when creating the materialized view, further query processing is needed on the basis of querying the materialized view involved in the data query statement to obtain the final query result.

[0078] For example, the data query statement used when creating the materialized view is: CREATE MATERIALIZED VIEW mv_order_stats AS SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY customer_id.

[0079] That is, from the orders table (order table), group the data according to customer_id (customer number), use COUNT(*) to count the number of orders (order_count) for each customer, and use SUM(amount) to count the total order amount (total_amount) for each customer, and then store these statistics in the form of customer_id, order_count, and total_amount fields in the materialized view mv_order_stats.

[0080] The subsequent received data query statement is: SELECT customer_id, order_count, total_amount FROM mv_order_stats ORDER BY total_amount DESC LIMIT 10.

[0081] That is, read the order quantity and total amount of all customers from the materialized view, sort by total_amount in descending order, and return only the top 10 records (i.e., the top 10 customers with the highest total amount).

[0082] As can be seen from the above, the data query statement received by the database server adds sorting and paging operations on the basis of the materialized view. Therefore, the database server needs to perform subsequent sorting and paging processing on the basis of the materialized view involved in the data query statement to obtain the final query result.

[0083] It should be noted that there can be three cases in the process of executing the data query statement by the database server. The first case is that the materialized views involved in the execution of the data query statement all exist in the temporary materialized view storage table. The second case is that the materialized views involved in the execution of the data query statement all exist in the materialized view storage table. The third case is that the materialized views involved in the execution of the data query statement partially exist in the temporary materialized view storage table and partially exist in the materialized view storage table. The following will be described in detail with respect to these three cases.

[0084] Specifically, the database server can parse the data query statement to determine whether the materialized views involved in the expected return result of the data query statement all exist in the temporary materialized view storage table. If so, the database server can execute the data query statement to query the materialized views involved in the expected return result of the data query statement from the temporary materialized view storage table.

[0085] In addition, in the case where the database server determines that the materialized views involved in the expected return result of the data query statement partially exist in the temporary materialized view storage table, the database server can execute the data query statement to query the partial materialized views involved in the expected return result of the data query statement from the temporary materialized view storage table, and further query the remaining materialized views involved in the expected return result of the data query statement from the materialized view storage table.

[0086] In the case where the database server determines that the materialized views involved in the expected return result of the data query statement all exist in the materialized view storage table, the database server can execute the data query statement to query the materialized views involved in the expected return result of the data query statement from the materialized view storage table.

[0087] It should be noted that in actual application scenarios, data changes occurring in the data table can be recorded in the materialized view log in the form of incremental change information for delayed batch updating (i.e., part of the data changes occurring in the data table can not be updated to the temporary materialized view storage table in a timely manner).

[0088] Therefore, in the above, when the database server queries the at least part of the materialized view involved in the expected return result of the data query statement from the temporary materialized view storage table, the materialized view involved in the expected return result of the data query statement can also be queried from the temporary materialized view storage table as an intermediate query result, and the unprocessed incremental change information in the preset materialized view log can be read, and then the queried intermediate query result and the unprocessed incremental change information can be combined for processing to obtain the materialized view involved in the expected return result of the data query statement.

[0089] As can be seen from the above, the database server maintains a temporary materialized view storage table in memory to support in-place updating for temporarily storing frequently changed materialized view data, avoiding directly modifying the materialized view storage table in the disk which only supports append write. At the same time, the incremental update information of the data table is recorded through the materialized view log, so that when the incremental update of the materialized view stored in the temporary materialized view storage table is performed, the delay batch update and change tracking are supported, and the update efficiency can be improved in combination with the log partitioning and parallel processing mechanism. In addition, the database server can dynamically switch between incremental update and full update according to actual needs to ensure data consistency in large-scale changes or log backlog.

[0090] For ease of understanding, the following describes in detail the process of querying data based on the above temporary materialized view storage table and the materialized view stored in the materialized view storage table, as shown in the specific embodiments. Figure 5

[0091] Figure 5 FIG. 1 is a flow diagram of a data query method according to an example embodiment, including the following steps: S500: receiving a data query statement for the data table.

[0092] S502: executing the data query statement to query the materialized view involved in the data query statement from the materialized view storage table and / or the temporary materialized view storage table for storing the materialized view, and obtaining a query result according to the materialized view involved in the data query statement; the temporary materialized view storage table contains at least part of the materialized view after incremental update.

[0093] In this specification, when the database server receives a data query statement for a data table, the data query statement can be executed to query the materialized view involved in the data query statement from the materialized view storage table and / or the temporary materialized view storage table for storing the materialized view, and a query result can be obtained according to the materialized view involved in the data query statement.

[0094] ​It should be noted that there can be three cases in the process of the database server executing the data query statement, the first case is that all the materialized views involved in the execution of the data query statement exist in the temporary materialized view storage table, the second case is that all the materialized views involved in the execution of the data query statement exist in the materialized view storage table, and the third case is that part of the materialized views involved in the execution of the data query statement exist in the temporary materialized view storage table and part of the materialized views exist in the materialized view storage table. The following will be described in detail for the three cases.

[0095] Specifically, the database server can parse the data query statement, determine whether the materialized views involved in the expected return result of the data query statement all exist in the temporary materialized view storage table, if so, the data query statement can be executed, and the materialized views involved in the expected return result of the data query statement can be queried from the temporary materialized view storage table.

[0096] In addition, in the case where the database server determines that part of the materialized views involved in the expected return result of the data query statement exist in the temporary materialized view storage table, the data query statement can be executed, part of the materialized views involved in the expected return result of the data query statement can be queried from the temporary materialized view storage table, and the remaining materialized views involved in the expected return result of the data query statement can be queried from the materialized view storage table.

[0097] In the case where the database server determines that all the materialized views involved in the expected return result of the data query statement exist in the materialized view storage table, the data query statement can be executed, and the materialized views involved in the expected return result of the data query statement can be queried from the materialized view storage table.

[0098] It should be noted that in actual application scenarios, data changes occurring in the data table can be recorded in the materialized view log in the form of incremental change information for delayed batch updating (i.e., part of the data changes occurring in the data table can not be updated to the temporary materialized view storage table in time).

[0099] Therefore, in the above content, when the database server queries at least part of the materialized views involved in the expected return result of the data query statement from the temporary materialized view storage table, it can also query the materialized views involved in the expected return result of the data query statement from the temporary materialized view storage table as an intermediate query result, and read the unprocessed incremental change information from the preset materialized view log. Furthermore, the intermediate query result and the unprocessed incremental change information can be merged to obtain the materialized views involved in the expected return result of the data query statement, and the query result of the data query statement can be obtained based on the materialized views obtained by merging the intermediate query result and the unprocessed incremental change information.

[0100] As can be seen from the above content, when the database server performs a data query in response to a received data query statement, it can automatically select the optimal path based on the distribution of materialized views in the temporary materialized view storage table and the materialized view storage table, and merge the results based on the unprocessed change information contained in the materialized view log to ensure the real-time and accuracy of the query results.

[0101] Figure 6 This is a schematic structural diagram of a device provided by an exemplary embodiment. Figure 6 At the hardware level, the device includes a processor 602, an internal bus 604, a network interface 606, a memory 608, and a non-volatile memory 610. Of course, it may also include hardware required for other functions. One or more embodiments of this specification can be implemented based on software, such as the processor 602 reading the corresponding computer program from the non-volatile memory 610 into the memory 608 and then running it. Of course, in addition to software implementation, one or more embodiments of this specification do not exclude other implementation methods, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0102] Please refer to Figure 7 , the update mechanism of materialized views can be applied to Figure 6 The device shown in FIG. 1 is used to implement the technical solution of this specification. The materialized view updating device may include: Receiving module 701, used for receiving data operation statements for a data table; The first execution module 702 is configured to execute the data operation statement to modify data in the data table; and select a materialized view to be updated from the materialized views stored in a temporary materialized view storage table; the temporary materialized view storage table is obtained by pre-copying at least some of the materialized views in the materialized view storage table; the materialized view storage table is configured to support only append writes; the temporary materialized view storage table is not subject to append write rules; the materialized view to be updated is the materialized view affected by the data modification; The second execution module 703 is configured to update the materialized view to be updated based on the data changes in the data table to obtain a temporary materialized view storage table after incremental update; wherein, when performing a data query in response to the received data query statement, the temporary materialized view storage table after incremental update is used to replace the materialized view storage table to provide at least part of the query result.

[0103] Optionally, the first execution module 702 is specifically configured to record the incremental change information corresponding to the data operation statement into a preset materialized view log; the incremental change information is used to represent the data change of the data table due to the data operation statement; when it is determined that the incremental update condition is met, the unprocessed incremental change information in the materialized view log is used to select the to-be-updated materialized view from the temporary materialized view storage table.

[0104] Optionally, the first execution module 702 is specifically configured to determine the number of unprocessed incremental change information in the materialized view log as a first target value according to the processing state identifier of each incremental change information contained in the materialized view log; when it is determined that the first target value exceeds a preset incremental update condition value, the unprocessed incremental change information in the materialized view log is used to select the to-be-updated materialized view from the temporary materialized view storage table.

[0105] Optionally, the materialized view log contains a plurality of incremental change information partitions; the unprocessed incremental change information in different incremental change information partitions is updated into the to-be-updated materialized view in parallel.

[0106] Optionally, the plurality of incremental change information partitions are obtained by dividing each incremental change information according to a preset division rule; the preset division rule includes at least one of the following dimensions: a data change time range, a data table partition key, and a data change operation type.

[0107] Optionally, the second execution module 703 is specifically configured to, when it is determined that the data merging condition is met, copy the data in the materialized view storage table to a storage device where the temporary materialized view storage table is located to obtain an original table; merge the original table and the temporary materialized view storage table to obtain a merged temporary materialized view storage table, and replace the materialized view storage table with the merged temporary materialized view storage table.

[0108] Optionally, the second execution module 703 is specifically configured to, when it is determined that the full update condition is met, copy the materialized view storage table to a storage device where the temporary materialized view storage table is located to obtain a full update table; re-execute at least part of the data query statements used to generate each materialized view to query the data table to obtain an updated query result, and update the full update temporary materialized view storage table according to the updated query result; and replace the materialized view storage table with the updated full update temporary materialized view storage table.

[0109] Please refer to Figure 8 , the data query device can be applied to, for exampleFigure 6 The device shown in the figure is used to implement the technical solution of this specification. The data query device may include: Receiving module 801, used for receiving a data query statement for a data table; An execution module 802 is configured to execute the data query statement, obtain the materialized view involved in the data query statement from a materialized view storage table and / or a temporary materialized view storage table, and obtain a query result based on the materialized view involved in the data query statement; the temporary materialized view storage table contains at least a portion of the incrementally updated materialized view.

[0110] Optionally, the execution module 802 is specifically used to parse the data query statement and determine whether the materialized views involved in the expected return results of the data query statement are all in the temporary materialized view storage table; if so, execute the data query statement and obtain the materialized views involved in the expected return results of the data query statement from the temporary materialized view storage table.

[0111] Optionally, the execution module 802 is specifically used to, when it is determined that part of the materialized views involved in the expected return results of the data query statement is in the temporary materialized view storage table, execute the data query statement, query from the temporary materialized view storage table to obtain part of the materialized views involved in the expected return results of the data query statement, and query from the materialized view storage table to obtain the remaining materialized views involved in the expected return results of the data query statement; when it is determined that all the materialized views involved in the expected return results of the data query statement are in the materialized view storage table, execute the data query statement, query from the materialized view storage table to obtain the materialized views involved in the expected return results of the data query statement.

[0112] Optionally, the execution module 802 is specifically used to execute the data query statement, query the materialized view involved in the expected return result of the data query statement from the temporary materialized view storage table as an intermediate query result; and read unprocessed incremental change information from a preset materialized view log; merge the intermediate query result with the unprocessed incremental change information to obtain the materialized view involved in the expected return result of the data query statement.

[0113] Based on the same concept as the above method, this specification also provides an electronic device, including: a processor; a memory for storing processor-executable instructions; wherein the processor implements the steps of the method described in any of the above embodiments by running the executable instructions.

[0114] Based on the same idea as the above method, the specification also provides a computer readable storage medium, which stores computer instructions, and the instructions are executed by a processor to implement the steps of the method according to any one of the above embodiments.

[0115] Based on the same idea as the above method, the specification also provides a computer program product, which includes computer program / instructions, and the instructions are executed by a processor to implement the steps of the method according to any one of the above embodiments.

Claims

1. A method for updating a materialized view, comprising: Receive data operation statements for the data table; Executing the data operation statement to change data in the data table; as well as Select the materialized view to be updated from the materialized views stored in the temporary materialized view storage table; The temporary materialized view storage table is obtained by pre-copying at least part of the materialized views in the materialized view storage table; The materialized view storage table is set to support only append writes; The temporary materialized view storage table is not subject to the append write rule; The materialized view to be updated is the materialized view affected by the data change; The to-be-updated materialized view is updated according to data changes in the data table to obtain a temporary materialized view storage table after incremental update; wherein, when a data query is performed in response to a received data query statement, the temporary materialized view storage table after incremental update is used to replace the materialized view storage table to provide at least part of the query result.

2. The method according to claim 1, wherein the materialized view to be updated is selected from the materialized views stored in the temporary materialized view storage table, specifically comprising: Recording incremental change information corresponding to the data operation statement in a preset materialized view log; the incremental change information is used to represent the data changes in the data table caused by the data operation statement; When it is determined that the incremental update condition is met, the materialized view to be updated is selected from the materialized views stored in the temporary materialized view storage table according to the unprocessed incremental change information in the materialized view log.

3. The method of claim 2, wherein, when it is determined that the incremental update condition is met, selecting the materialized view to be updated from the materialized views stored in the temporary materialized view storage table based on the unprocessed incremental change information in the materialized view log, specifically comprises: determining, according to a processing status identifier of each incremental change information contained in the materialized view log, the amount of unprocessed incremental change information in the materialized view log as a first target value; When it is determined that the first target value exceeds the preset incremental update condition value, a materialized view to be updated is selected from the materialized views stored in the temporary materialized view storage table according to the unprocessed incremental change information in the materialized view log.

4. The method according to claim 2, wherein the materialized view log contains multiple incremental change information partitions; and unprocessed incremental change information contained in different incremental change information partitions is updated in parallel to the materialized view to be updated.

5. The method according to claim 4, wherein the plurality of incremental change information partitions are obtained by dividing each incremental change information according to a preset division rule; the preset division rule comprises: Divide data by at least one of the following dimensions: data change time range, data table partition key, and data change operation type.

6. The method according to any one of claims 1 to 5, further comprising: When it is determined that the data merging condition is met, the data in the materialized view storage table is copied to the storage device where the temporary materialized view storage table is located to obtain the original table; The original table and the temporary materialized view storage table are merged to obtain a merged temporary materialized view storage table, and the materialized view storage table is replaced by the merged temporary materialized view storage table.

7. The method according to any one of claims 1 to 5, further comprising: When it is determined that the full update condition is met, the materialized view storage table is copied to the storage device where the temporary materialized view storage table is located to obtain a full update table; Re-executing at least part of the data query statements used when generating each materialized view to query the data table, obtaining an updated query result, and updating the full update temporary materialized view storage table according to the updated query result; The updated full-update temporary materialized view storage table is used to replace the materialized view storage table.

8. A data query method, comprising: Receive data query statements for the data table; Executing the data query statement, querying the materialized view involved in the data query statement from the materialized view storage table and / or the temporary materialized view storage table, and obtaining a query result based on the materialized view involved in the data query statement; The temporary materialized view storage table contains at least a portion of the materialized view after incremental update.

9. The method according to claim 8, wherein executing the data query statement and querying from the materialized view storage table and / or the temporary materialized view storage table to obtain the materialized view involved in the data query statement specifically comprises: Parsing the data query statement to determine whether all materialized views involved in the expected return results of the data query statement are in the temporary materialized view storage table; If so, the data query statement is executed, and the materialized view involved in the expected return result of the data query statement is obtained from the temporary materialized view storage table.

10. The method of claim 9, further comprising: When it is determined that the materialized view portion involved in the expected return result of the data query statement is in the temporary materialized view storage table, the data query statement is executed, and the partial materialized views involved in the expected return result of the data query statement are obtained from the temporary materialized view storage table, and the remaining materialized views involved in the expected return result of the data query statement are obtained from the materialized view storage table; When it is determined that the materialized views involved in the expected return results of the data query statement are all in the materialized view storage table, the data query statement is executed, and the materialized views involved in the expected return results of the data query statement are queried from the materialized view storage table.

11. The method according to claim 9, wherein executing the data query statement and querying from the temporary materialized view storage table to obtain the materialized view involved in the expected return result of the data query statement specifically comprises: Execute the data query statement, and query the temporary materialized view storage table to obtain the materialized view involved in the expected return result of the data query statement as an intermediate query result; as well as Read unprocessed incremental change information from the preset materialized view log; The intermediate query result and the unprocessed incremental change information are merged to obtain the materialized view involved in the expected return result of the data query statement.

12. A materialized view updating device, comprising: A receiving module, used for receiving data operation statements for a data table; A first execution module, configured to execute the data operation statement to modify data in the data table; and, selecting a materialized view to be updated from the materialized views stored in the temporary materialized view storage table; The temporary materialized view storage table is obtained by pre-copying at least part of the materialized views in the materialized view storage table; The materialized view storage table is set to support only append writes; The temporary materialized view storage table is not subject to the append write rule; The materialized view to be updated is the materialized view affected by the data change; A second execution module is configured to update the to-be-updated materialized view according to data changes in the data table to obtain a temporary materialized view storage table after incremental update; wherein, when performing a data query in response to a received data query statement, the temporary materialized view storage table after incremental update is used to replace the materialized view storage table to provide at least a portion of the query result.

13. An electronic device comprising: processor; A memory for storing processor-executable instructions; wherein the processor implements the steps of the method according to any one of claims 1 to 11 by executing the executable instructions.

14. A computer-readable storage medium having computer instructions stored thereon, wherein when the instructions are executed by a processor, the steps of the method according to any one of claims 1 to 11 are implemented.

15. A computer program product comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 11.

Images