A method and apparatus for updating a materialized view, a medium and an electronic device

Abstract

The present specification provides a method, device, medium and electronic equipment for updating a materialized view. In the method, a temporary materialized view storage table is 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 from each materialized view stored in the temporary materialized view storage table, a to-be-updated materialized view affected by the change is selected. 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 is 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 constrained by 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.

Description

TECHNICAL FIELD

[0001] One or more embodiments of the present specification relate to the technical field of database, and in particular, to a method and apparatus for updating a materialized view, a medium and an electronic device. BACKGROUND

[0002] In recent years, to cope with the performance challenges in the scenario of mass data writing, storage engines based on the append-only storage model (such as LSM-Tree, Bitcask, etc.) have been widely used in database systems. The core feature of such storage engines is data immutability, that is, once the data is persisted to the disk, the content of its original physical location cannot be directly modified or deleted. By using the append-only storage model, random writes when writing data to the disk can be converted into sequential writes, which can significantly improve write performance and simplify concurrency control, thereby effectively improving the performance bottleneck of the database system in the scenario of high concurrency and a large number of write operations. However, this data immutability also limits some database functions that require efficient in-place updates, such as the incremental update mechanism of materialized views. The following takes LSM-Tree as an example to analyze this contradiction in depth.

[0003] LSM-Tree, as an efficient storage structure, has been widely used in database systems, especially in scenarios that handle write-intensive workloads. Compared with traditional index structures such as B-tree or B+ tree, LSM-Tree adopts an "append-only write" approach when writing data, thereby effectively overcoming the performance bottleneck caused by a large number of write operations in a high concurrency scenario. Specifically, when new data is inserted or existing data is updated, the database system does not directly modify the existing data block on the disk, but appends these changes to the new storage location in a sequential manner. This design avoids frequent random I / O operations in traditional B-tree / B+ tree structures, reduces disk seek time, and significantly improves write throughput. In addition, since there is no in-place modification of existing data, append-only writing also reduces lock contention problems in concurrency control, further improving the performance bottleneck of the system in the scenario of high concurrency and a large number of write operations.

[0004] Materialized view is a technique for accelerating complex query execution, which pre-computes and persistently stores the result of a complex query based on a data table, so that the same query can directly return the pre-stored query result without repeating the complex query computation. In this process, in order to ensure the consistency of the materialized view data, an incremental update strategy is usually adopted, that is, the materialized view affected by the change of the data table is determined as the materialized view to be updated, so that the in-place update can be performed only for the materialized view to be updated, instead of full reconstruction, thereby greatly saving the computation and I / O overhead.

[0005] However, if the container table used to store the materialized view adopts the LSM-Tree structure and can only support data writing in the form of append write, and the file is immutable once written, and the incremental update of the materialized view requires in-place update, that is, directly locating and modifying the specific record row in the materialized view affected by the change of the data table (such as updating the aggregation statistical value of a certain user), which leads to the difficulty in implementing the incremental update mechanism of the materialized view. SUMMARY

[0006] Therefore, one or more embodiments of the present specification provide technical solutions as follows:

[0007] According to a first aspect of one or more embodiments of the present specification, a materialized view updating method is provided, comprising:

[0008] receiving a data operation statement for a data table;

[0009] performing the data operation statement to make data change to the data table; and

[0010] selecting 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 part of the materialized view storage table; 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; the materialized view to be updated is the materialized view affected by the data change;

[0011] updating the materialized view to be updated according to the data change of the data table to obtain an incremental updated temporary materialized view storage table; wherein, in response to receiving a data query statement for 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.

[0012] According to a second aspect of one or more embodiments of the present specification, a data query method is provided, comprising:

[0013] receiving a data query statement for a data table;

[0014] 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, 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.

[0015] According to a third aspect of one or more embodiments of the present specification, an updating device of a materialized view is provided, comprising:

[0016] a receiving module configured to receive a data operation statement for a data table;

[0017] a first executing module configured to execute the data operation statement to make data change to 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 copying at least part of the materialized view in a 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; the materialized view to be updated is the materialized view affected by the data change;

[0018] a second executing module configured to update the materialized view to be updated according to the data change of the data table, and obtain a temporary materialized view storage table after incremental update; wherein, when responding to the received data query statement to make data query, 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.

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

[0020] According to a fifth aspect of one or more embodiments of the present specification, a computer readable storage medium is provided, which stores computer instructions, the instructions being executed by a processor to implement the steps of the above-mentioned updating method of the materialized view.

[0021] According to a sixth aspect of one or more embodiments of the present specification, a computer program product is provided, comprising computer program / instructions, the computer program / instructions being executed by a processor to implement the steps of the above-mentioned updating method of the materialized view.

[0022] 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.

[0023] 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

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

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

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

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

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

[0029] Figure 6Fig. 1 is a schematic structural diagram of an apparatus provided by an example embodiment.

[0030] Figure 7 Fig. 2 is a block diagram of an update device of a materialized view provided by an example embodiment.

[0031] Figure 8 Fig. 3 is a block diagram of a data query device provided by an example embodiment. DETAILED DESCRIPTION

[0032] For the purpose of making the technical scheme, the technical solutions and the advantages of the present disclosure clearer, the technical solutions of the present disclosure will be described clearly and completely below with reference to the embodiments of the present disclosure and the corresponding drawings. Obviously, the described embodiments are only some of the embodiments of the present disclosure, but not all the embodiments. Based on the embodiments in the present disclosure, all other embodiments obtained by a person of ordinary skill in the art without creative work fall within the scope of protection of the present disclosure.

[0033] The user information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in the present disclosure are information and data authorized by the user or authorized by all parties, and the collection, use and processing of the relevant data need to comply with the relevant laws, regulations and standards of the relevant countries and regions, and provide corresponding operation portals for the user to choose authorization or refusal.

[0034] At present, the storage engine of the traditional database often uses data structures such as B-tree or B+ tree to organize and manage the data on the persistent storage devices such as hard disk and solid state disk. Such data structures allow data to be distributed in a balanced manner in a tree structure, thereby ensuring the logarithmic time complexity of query performance, and are very suitable for the random access mode of disk storage, and can effectively support point query and range query in transaction processing.

[0035] However, in a high concurrency scenario, especially when facing a large number of write operations (such as insertion, update, deletion), the B-tree or B+ tree structure has certain performance bottlenecks. Since each write operation may involve the modification of multiple tree level nodes, thereby causing page splitting, lock competition and other problems, leading to frequent disk random write and increasing the overhead of concurrency control, affecting the overall throughput and response delay.

[0036] To address these challenges, more and more database systems have started to adopt LSM-Tree (Log-Structured Merge-Tree) as the core data structure of the storage engine to improve performance in high concurrency and high write load scenarios. LSM-Tree converts random write operations into efficient append write operations, significantly reducing disk I / O overhead, thereby supporting fast data insertion and update. In addition, through the background merging and compression mechanism, LSM-Tree can further optimize storage space and improve read performance. Therefore, LSM-Tree structure is particularly suitable for write-intensive and large-scale data management scenarios, and has been widely applied in modern distributed databases, NoSQL systems and some new relational databases.

[0037] However, due to an important feature of append write, its immutability, that is, once the data is written to the disk, it cannot be directly modified or deleted. This feature, although helpful in improving write efficiency and system stability, also imposes limitations on certain database functions that require frequent updates, such as the incremental update mechanism of materialized views.

[0038] Specifically, in a database system, a materialized view is a technique used to speed up complex query execution, which precomputes and persistently stores the results of complex queries based on data tables, so that subsequent identical queries can directly return the pre-stored query results without repeating complex query calculations.

[0039] For example, for the complex query statement "SELECT SUM(sales), region FROM orders GROUP BY region", that is, from the orders table, group by region, and calculate the total sales SUM(sales) of each region. For the above complex query statement, the database can pre-execute this query statement and store the result as a materialized view. When the same or similar query is executed again, the database system can directly use the result of the materialized view for fast response, thereby significantly improving query performance.

[0040] As can be seen, materialized view is essentially a space-time optimization strategy. However, in order to ensure the accuracy of the query result, when the data in the data table changes (such as insertion, update or deletion), the materialized view must also be updated accordingly. Especially in the scenario of supporting incremental refresh, the materialized view often needs to be updated in a fine-grained and partial manner, rather than being rebuilt in full every time.

[0041] 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.

[0042] 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:

[0043] 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.

[0044] 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.

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

[0046] 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.

[0047] Figure 1 FIG. 1 is a flowchart of a method for updating a materialized view provided by an exemplary embodiment, which includes:

[0048] S100: receiving a data operation statement for a data table.

[0049] S102: executing the data operation statement to make data changes to the data table.

[0050] 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.

[0051] 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.

[0052] 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.

[0053] 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.

[0054] 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.

[0055] 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.

[0056] 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.

[0057] 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.

[0058] Based on this, the database server can store the materialized view storage table in the above content in the 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.

[0059] 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.

[0060] 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.

[0061] 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.

[0062] Further, the database server can select the to-be-updated materialized view from each materialized view stored in the preset temporary materialized view storage table according to the identified range of the materialized view affected by the data change, and update at least part of the data in the to-be-updated materialized view according to the data change, to obtain an incrementally updated temporary materialized view storage table.

[0063] As can be seen from the above, the database server can introduce a temporary materialized view storage table in a storage engine that only supports append write based on LSM-Tree, etc., so that after detecting a data table change, the change operation can be parsed and analyzed, the range of the materialized view affected by the change can be identified, and the corresponding to-be-updated materialized view can be selected from the 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 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. Since the temporary materialized view storage table is not subject to the constraints of the append write rule, it can support efficient insertion, update and deletion operations, so that the incrementally updated materialized view can be realized without damaging the write performance of the underlying storage structure.

[0064] It should be noted that the materialized view data stored in the temporary materialized view storage table described above can be the entire content of the materialized view stored in the materialized view storage table, or a partial subset thereof, which can be determined according to actual needs. In actual application scenarios, in the initial stage of the creation of the materialized view storage table, the temporary materialized view storage table can completely copy the entire content of the materialized view stored in the materialized view storage table, to ensure that all possible data changes can be responded quickly in the initial stage. With the continuous update operation on the materialized view, the database server can identify at least part of the materialized view entries with extremely low change frequency or long-term no change from the materialized view stored in the temporary materialized view storage table through analysis and statistics of historical data changes. For these materialized view entries with extremely low change probability, they can be gradually removed from the temporary materialized view storage table and handed over to the materialized view storage table for persistent storage. At the same time, only the materialized view entries that are likely to change frequently will be retained in the temporary materialized view storage table, so as to realize dynamic updating and lightweight maintenance of the data in the temporary materialized view storage table.

[0065] In addition, in order to further improve the traceability and execution efficiency of the incrementally updated materialized view, the database server can first record the incrementally changed information corresponding to the data operation statement in the preset materialized view log, to perform delayed batch updating on the materialized view, as shown in Figure 2 .

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

[0067] In conjunction with Figure 2 As can be seen, the database server can record the incremental change information corresponding to the data manipulation statement into the preset materialized view log each time the data table has data change, so as to 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 when it is determined that the preset incremental update condition is met.

[0068] The incremental change information is used to represent the data change of the data table due to the data manipulation statement. The incremental change information can include the data value of the data change. Of course, in actual application scenarios, the incremental change information can also include the type of data change, the change time, the data row identifier involved, etc.

[0069] The incremental update condition can be a judgment standard for triggering the incremental update of the materialized view. The condition judgment can avoid resource waste caused by frequent refreshing. The incremental update condition can be set according to actual needs. For example, when it is determined that the number of unprocessed incremental change information in the materialized view log exceeds a set first threshold and is less than a set second threshold, it can be considered that the incremental update condition is met. For another example, when it is determined that the time interval from the last incremental update meets a preset time threshold, it can be considered that the incremental update condition is met.

[0070] It should be noted that the materialized view log can also be provided with a processing state identifier field. Each field value under the processing state identifier field is the processing state identifier of each incremental change information, as shown in Figure 3 .

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

[0072] In conjunction with Figure 3 As can be seen, for the data table t1 including two columns c1 and c2, and the materialized view m1 created on the data table t1, m1 records the incremental update information through the materialized view log mlog$_t1. If the following data manipulation statement is received:

[0073] Insert into t1 value (3, 4)

[0074] Insert into t1 value (5, 6)

[0075] Insert into t1 value (7, 8)

[0076] When the data table is changed according to the above data operation statement, three rows of incremental update information can be recorded in the materialized view log corresponding to column c1, that is, the data value 3 of the data change after executing Insert into t1 value (3, 4), the corresponding processing state 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), the corresponding processing state identifier is N, and the data value 7 of the data change after executing Insert into t1 value (7, 8), the corresponding processing state identifier is N.

[0077] On this basis, the database server can determine the number of unprocessed incremental change information in the materialized view log according to the processing state identifier of each incremental change information contained in the materialized view log as the first target value, and then when the first target value exceeds the preset incremental update condition value, the unprocessed incremental change information in the materialized view log is selected from each materialized view stored in the preset temporary materialized view storage table as the to-be-updated materialized view.

[0078] In addition, in actual application scenarios, in order to improve the efficiency of incremental update of the materialized view, the above-mentioned materialized view log can further include a plurality of incremental change information partitions, wherein different incremental change information partitions are obtained by dividing each incremental change information according to a preset division rule, and the unprocessed incremental change information contained in different incremental change information partitions can be updated to the to-be-updated materialized view in parallel (that is, the task of incrementally updating the unprocessed incremental change information contained in each incremental change information partition to the to-be-updated materialized view can be independently executed and does not interfere with each other).

[0079] Among them, the above-mentioned preset division rule is used to divide the change operation with the same attribute into the same incremental change information partition, and the above-mentioned preset division rule can include: dividing according to at least one dimension of data change time range, data table partition key, and data change operation type.

[0080] Specifically, when the data table is changed, the database server can divide the incremental change information into different incremental change information partitions according to a preset division rule. For example, when the data change time range is divided, the changes in the same hour are classified into the same partition; when the data table partition key is divided, the changes involving the same primary key range can be classified into the same partition. Each partition maintains an independent processing state identifier. When the incremental refresh condition is met, the database server can start multiple processing threads to read the unprocessed change information in different partitions in parallel. Each thread selects the updated materialized view from the preset temporary materialized view storage table according to the incremental change information in the corresponding incremental change information partition, and then updates the updated materialized view.

[0081] The update operation of updating the unprocessed incremental change information in different incremental change information partitions into the updated materialized view is isolated at the physical storage layer, and parallel processing does not cause data competition or lock conflict, thereby improving the update efficiency.

[0082] In addition, in order to reduce the storage overhead of the materialized view log, the database server can also compress and store the above-mentioned materialized view log under the premise of ensuring data integrity and analyzability, so as to reduce the storage space occupation of the log file.

[0083] Further, when the database server detects that the preset data merging condition is met, the current data in the materialized view storage table can be copied to the storage device where the temporary materialized view storage table is located to form an original table, and then the original table and the temporary materialized view storage table can be merged to obtain a merged temporary materialized view storage table, and then the materialized view storage table can be replaced by the merged temporary materialized view storage table after the merging is completed.

[0084] The above-mentioned data merging condition can be set according to actual needs, for example, when it is determined that the preset daily merging time window is reached, it can be considered that the data merging condition is met, and the time window can be when the database server is not busy, for example, 0:00 to 6:00 every day. For another example, if the data amount in the temporary materialized view storage table exceeds the preset data amount threshold, it can be considered that the data merging condition is met.

[0085] The method of merging the original table and the temporary materialized view storage table to obtain the merged temporary materialized view storage table can be to compare the data version difference of the two tables, and to cover the newly added or modified data in the temporary materialized view storage table to the corresponding position of the original table copy, and finally to generate the merged temporary materialized view storage table.

[0086] 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.

[0087] 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

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

[0089] In combination with Figure 4 As can be seen, 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.

[0090] 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.

[0091] 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. ​

[0092] 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.

[0093] 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 needs to be performed on the basis of querying the materialized view involved in the data query statement to obtain the final query result.

[0094] For example, the data query statement used when creating the materialized view is:

[0095] CREATE MATERIALIZED VIEW mv_order_stats AS

[0096] SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_amount

[0097] FROM orders

[0098] GROUP BY customer_id.

[0099] That is, from the table orders (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 in the materialized view mv_order_stats.

[0100] The subsequent received data query statement is:

[0101] SELECT customer_id, order_count, total_amount

[0102] FROM mv_order_stats

[0103] ORDER BY total_amount DESC LIMIT 10.

[0104] 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).

[0105] 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.

[0106] 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 the three cases.

[0107] 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.

[0108] 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.

[0109] 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.

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

[0111] Therefore, in the above, when the database server queries 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 can be read from the preset materialized view log, and then the intermediate query result obtained by querying can be combined with the unprocessed incremental change information for processing to obtain the materialized view involved in the expected return result of the data query statement.

[0112] As can be seen from the above, the database server maintains a temporary materialized view storage table supporting in-place updating in memory for temporarily storing high-frequency changed materialized view data, avoiding directly modifying the materialized view storage table on the disk supporting only 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 supported, 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 also dynamically switch between incremental update and full update according to actual needs to guarantee data consistency in large-scale changes or log backlog.

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

[0114] Figure 5 is a flow diagram of a data query method provided by an example embodiment, including the following steps:

[0115] S500: receiving a data query statement for the data table.

[0116] 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.

[0117] In the present 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 views involved in the data query statement from the materialized view storage table for storing the materialized views and / or the temporary materialized view storage table, and obtain the query result according to the materialized views involved in the data query statement.

[0118] 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, and 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 for the three cases.

[0119] 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, and if so, the data query statement can be executed to query the materialized views involved in the expected return result of the data query statement from the temporary materialized view storage table.

[0120] 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 data query statement can be executed 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 query the remaining materialized views involved in the expected return result of the data query statement from the materialized view storage table.

[0121] 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 data query statement can be executed to query the materialized views involved in the expected return result of the data query statement from the materialized view storage table.

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

[0123] 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 merged to obtain the materialized view involved in the expected return result of the data query statement, and then the query result of the data query statement can be obtained based on the materialized view obtained by merging the queried intermediate query result and the unprocessed incremental change information.

[0124] As can be seen from the above, when responding to the received data query statement, the database server can automatically select the optimal path according to the distribution of the materialized view in the temporary materialized view storage table and the materialized view storage table, and combine the unprocessed change information contained in the materialized view log to ensure the real-time and accuracy of the query result.

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

[0126] Please refer to Figure 7 The updating apparatus of the materialized view can be applied to an apparatus as shown in Figure 6 , to implement the technical solutions of the present specification. The updating apparatus of the materialized view can include:

[0127] The receiving module 701 is configured to receive a data operation statement for a data table;

[0128] The first execution module 702 is configured to execute the data operation statement to perform data change on the data table, and select 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 materialized views in a materialized view storage table in advance, the materialized view storage table is configured to support only append write, and 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.

[0129] The second execution module 703 is configured to update the to-be-updated materialized view according to the data change 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 query results in response to a received data query statement.

[0130] Optionally, the first execution module 702 is specifically configured to record increment change information corresponding to the data operation statement into a preset materialized view log, wherein the increment change information is used to represent the data change of the data table caused by the data operation statement, and the to-be-updated materialized view is selected from each materialized view stored in the temporary materialized view storage table according to unprocessed increment change information in the materialized view log when it is determined that an increment update condition is met.

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

[0132] Optionally, the materialized view log contains a plurality of increment change information partitions, and the unprocessed increment change information contained in different increment change information partitions is updated into the to-be-updated materialized view in parallel.

[0133] Optionally, the plurality of increment change information partitions are obtained by dividing each increment change information according to a preset division rule, and 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.

[0134] Optionally, the second execution module 703 is specifically configured to, when it is determined that the data merging condition is met, copy 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.

[0135] 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.

[0136] Please refer to Figure 8 The data query apparatus can be applied to a device as shown in Figure 6 The data query apparatus can include:

[0137] The receiving module 801 is configured to receive a data query statement for a data table.

[0138] The execution module 802 is configured to execute the data query statement, query a 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 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.

[0139] Optionally, the execution module 802 is specifically configured to parse the data query statement, determine whether the materialized view involved in the expected return result of the data query statement is in the temporary materialized view storage table, and if so, execute the data query statement to query the materialized view involved in the expected return result of the data query statement from the temporary materialized view storage table.

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

[0141] Optionally, the execution module 802 is specifically configured 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; read the unprocessed incremental change information from the preset materialized view log; and 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.

[0142] Based on the same idea as the above method, the specification also provides an electronic device, comprising: a processor; a memory for storing processor executable instructions; wherein the processor implements the steps of the method according to any one of the above embodiments by running the executable instructions.

[0143] Based on the same idea as the above method, the specification also provides a computer readable storage medium having stored thereon computer instructions, which, when executed by a processor, implement the steps of the method according to any one of the above embodiments.

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

Claims

1. A method for updating a materialized view, comprising: receiving a data operation statement for a data table; executing the data operation statement to make a data change to the data table; and selecting a materialized view to be updated from each materialized view stored in a temporary materialized view storage table, wherein all or part of materialized views in a materialized view storage table are pre-copied in the temporary materialized view storage table, wherein the materialized view storage table is configured to support only append write, wherein the temporary materialized view storage table is not subject to the append write rule, wherein the materialized view to be updated is a materialized view affected by the data change, and wherein the materialized view to be updated is updated according to the data change of the data table to obtain an incrementally updated temporary materialized view storage table, and wherein the incrementally updated temporary materialized view storage table is used to replace the materialized view storage table to provide at least part of query results in response to a received data query statement. 2.The method of claim 1, wherein the selecting a materialized view to be updated from each materialized view stored in a temporary materialized view storage table comprises: recording incrementally changed information corresponding to the data operation statement into a preset materialized view log, wherein the incrementally changed information is used to represent the data change of the data table caused by the data operation statement; and determining that an incrementally updated condition is met, and selecting a materialized view to be updated from each materialized view stored in a temporary materialized view storage table according to unprocessed incrementally changed information in the materialized view log. 3.The method of claim 2, wherein the selecting a materialized view to be updated from each materialized view stored in a temporary materialized view storage table according to unprocessed incrementally changed information in the materialized view log when the incrementally updated condition is met comprises: determining a first target value as a number of unprocessed incrementally changed information in the materialized view log according to a processing state identifier of each incrementally changed information contained in the materialized view log; and determining that the first target value exceeds a preset incrementally updated condition value, and selecting a materialized view to be updated from each materialized view stored in a temporary materialized view storage table according to unprocessed incrementally changed information in the materialized view log. 4.The method of claim 2, wherein the materialized view log contains a plurality of incrementally changed information partitions, and wherein unprocessed incrementally changed information contained in different incrementally changed information partitions is updated into the materialized view to be updated in parallel, and wherein the incrementally changed information is divided according to at least one of a data change time range, a data table partition key, and a data change operation type. 6.The method of any one of claims 1-5, further comprising: determining that a data merging condition is met, and copying data in the materialized view storage table to a storage device in which the temporary materialized view storage table is located to obtain an original table; and merging the original table and the temporary materialized view storage table to obtain a merged temporary materialized view storage table, and using the merged temporary materialized view storage table to replace the materialized view storage table. ​ ​ ​ ​ ​ ​ ​ ​ ​ 5. The method of claim 4, wherein the multiple delta change information partitions are obtained by partitioning the delta change information according to a preset partition rule; and the preset partition rule comprises: ​ ​ ​ ​ 7.The method of any one of claims 1-5, further comprising: copying the materialized view storage table to a storage device where the temporary materialized view storage table is located to obtain a full update table upon determining that the full update condition is satisfied; re-executing at least part of the data query statements used to generate the materialized views to query the data table to obtain updated query results, and updating the full update temporary materialized view storage table according to the updated query results; replacing the materialized view storage table with the updated full update temporary materialized view storage table. 8.A data query method, comprising: receiving a data query statement for a data table; executing the data query statement to query materialized views involved in the data query statement from a materialized view storage table and / or a temporary materialized view storage table, and obtaining query results according to the materialized views involved in the data query statement; the temporary materialized view storage table pre-copies all or part of materialized views in the materialized view storage table, and at least part of the materialized views are obtained after incremental update; the materialized view storage table is configured to support only append write; the temporary materialized view storage table is not subject to the append write rule. 9.The method of claim 8, wherein the executing the data query statement to query materialized views involved in the data query statement from the materialized view storage table and / or the temporary materialized view storage table comprises: parsing the data query statement to 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 yes, executing the data query statement to query the materialized views involved in the expected return results of the data query statement from the temporary materialized view storage table. 10.The method of claim 9, further comprising: when it is determined that part of the materialized views involved in the expected return results of the data query statement are in the temporary materialized view storage table, executing the data query statement to query the part of the materialized views involved in the expected return results of the data query statement from the temporary materialized view storage table, and querying the remaining materialized views involved in the expected return results of the data query statement 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, executing the data query statement to query the materialized views involved in the expected return results of the data query statement from the materialized view storage table. 11.The method of claim 9, wherein the executing the data query statement to query materialized views involved in the expected return results of the data query statement from the temporary materialized view storage table comprises: executing the data query statement to query the materialized views involved in the expected return results of the data query statement from the temporary materialized view storage table as intermediate query results; and ​ reading, from a preset materialized view log, unprocessed incremental change information; merging the intermediate query result and the unprocessed incremental change information to obtain a materialized view related to an expected return result of the data query statement.

12. An updating apparatus of a materialized view, comprising: a receiving module configured to receive a data operation statement for a data table; a first executing module configured to execute the data operation statement to make a data change to the data table; and select a to-be-updated materialized view from each materialized view stored in a temporary materialized view storage table; the temporary materialized view storage table pre-copies all or part of materialized views in a materialized view storage table; the materialized view storage table is configured to support only append write; the temporary materialized view storage table is not subject to the append write rule; the to-be-updated materialized view is a materialized view affected by the data change; a second executing module configured to update the to-be-updated materialized view according to the data change of the data table to obtain an incrementally updated temporary materialized view storage table; wherein, in response to a received data query statement for data query, 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.

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

14. A computer readable storage medium having computer instructions stored thereon, the instructions being executed by a processor to implement the steps of the method of any one of claims 1-11.

15. A computer program product comprising computer program / instructions, the computer program / instructions being executed by a processor to implement the steps of the method of any one of claims 1-11.

Images