Version-based dynamic fast flashback query method in database

Abstract

The application discloses a version-based dynamic fast flashback query method in a database, and relates to the technical field of database query.The application uses the full-memory-state MVCC and the dynamic nonlinear flashback snapshot dotting mechanism constructed by the shared memory in the node to keep the specific data version in the memory for the fast flashback query at any time.In the main-backup cluster multi-node scene, each node has its own full-memory-state MVCC mechanism.The short-time flashback query after the misoperation and the long-time span query can all achieve the fast and stable consistent query efficiency.Especially for the long-time span flashback query, the long-time disk access and log rollback processing are avoided, and the query performance is greatly improved compared with the traditional mode.The dynamic nonlinear time interval reservation snapshot mechanism makes the snapshots in the recent time period dense and the snapshots in the long time period sparse, so that the memory of the MVCC can be well controlled and the rapid expansion of the memory is avoided.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database query, in particular to a version-based dynamic fast flashback query method in a database. BACKGROUND

[0002] In the evolution of database systems, how to efficiently support flashback query has always been the focus of the industry. Flashback query function can enable users to quickly obtain historical data versions at a specified time point, which not only has important value in the recovery scene of misoperation, but also provides convenience for data analysis and auditing. However, the existing technical path is generally based on the MVCC (Multi-Version Concurrency Control) mechanism and flashback log rollback technology. Although these schemes can meet the flashback demand to a certain extent, they have many shortcomings in large-scale data, high-concurrency access, and long-time span query scenarios. First, the basic principle of MVCC is to maintain multiple versions for each record, and a new version is generated every time a transaction is committed. These versions are usually stored on disk and the access efficiency is improved through the memory page buffer mechanism. However, both disk storage space and memory capacity are limited. When the number of historical versions reaches a threshold, the system will clean up old versions to maintain the balance of storage and performance. This leads to a significant problem: if a user needs to query historical data versions that have been cleaned up, they cannot directly obtain the results in the MVCC version chain, resulting in a failed flashback query. To alleviate the above problem, the flashback log archiving mechanism is usually introduced in existing technologies.

[0003] CN119046254A discloses a WAL log management method and system based on PostgreSQL, which realizes long-term preservation and disaster recovery of WAL files through archiving mode configuration, dynamic sharding processing, encryption protection, and uploading to an OSS storage bucket. This method has advantages in ensuring data integrity and security, but the core goal is still log archiving and storage optimization, and it fails to solve the problem of insufficient real-time performance of flashback query. In the query scenario, the archived logs still need to be scanned and played back sequentially.

[0004] CN117033398A discloses a data flashback query method and device based on a dolt database, which synchronizes incremental data from the source database to the dolt database, establishes an incremental data set and version identifier, and realizes query of data at any historical moment. Although this method expands the time traceable range of data, it does not clean up incremental data, which will cause long-term expansion of storage space and bring high maintenance cost. At the same time, this scheme relies on an external database to complete the flashback query, which increases the complexity of data synchronization and consistency management, and it is difficult to maintain the real-time performance and stability of the query in high-concurrency and large-scale data scenarios.

[0005] In addition, the version management of MVCC is mainly based on disk persistence, although the access efficiency can be partially improved by relying on the memory caching mechanism, but it is still limited by disk I / O delay, and for fast flashback query in large-scale data scenarios, it often shows performance bottleneck. Especially in long-running and frequently updated application environment, as the historical version chain gradually lengthens, the traditional traversal and backtracking method needs more computing and storage resources, further reducing the query efficiency. In addition, the organization and management of snapshots in the prior art lack dynamic and intelligent means, and most implementation methods rely on fixed log archiving strategies or simple version retention rules, making it difficult to achieve a better balance between query efficiency and storage cost.

[0006] The database flashback query method based on all-memory-state MVCC provided by the application effectively solves the performance bottleneck and storage overhead problems in the prior art through logical timestamp and snapshot tree structure management, dynamic snapshot density control and approximate query feedback mechanism. SUMMARY

[0007] The problem to be solved by the application is how to achieve fast flashback query and high-availability backtracking in a long time window under resource-limited conditions.

[0008] To solve the above technical problems, the application provides the following technical solutions:

[0009] In a first aspect, the application provides a dynamic fast flashback query method based on version in a database, which includes: S1: after a user submits a transaction, generating and managing an MVCC historical data version chain by using an all-memory-state MVCC mechanism, and assigning a logical timestamp to each historical data version in each MVCC; S2: generating a flashback snapshot containing a system timestamp and a logical timestamp mapping relationship by a background thread at a preset period while generating the MVCC historical data version chain, and inserting the flashback snapshot into a snapshot tree structure, balancing and deleting it according to a retention rule to control the snapshot density; S3: when receiving a flashback query request, searching for a matching system timestamp in the snapshot tree and extracting the corresponding logical timestamp, and locating the corresponding MVCC historical data version in the MVCC historical data version chain; S4: assembling the located MVCC historical data version into a query result and returning it, and prompting the user when approximate query, and feeding back the query result to S2 to dynamically adjust the flashback snapshot density.

[0010] As a preferred scheme of the version-based dynamic fast flashback query method in the database, the S1 comprises: the MVCC historical data version chain is implemented by using a memory engine, and multiple historical data versions generated when a DML transaction is committed are saved, and each historical data version records a logical timestamp at the time of generation; the logical timestamp is uniformly allocated by a global timestamp allocator; the current data version is saved in a buffer or a page, and is compatible with a disk-based storage engine; and if a certain MVCC historical data version in the MVCC historical data version chain is invisible to any transaction, instant cleaning is performed.

[0011] As a preferred scheme of the version-based dynamic fast flashback query method in the database, the flashback snapshot is generated by a background flashback thread at a fixed frequency, and the flashback snapshot is stored in a snapshot tree in a red-black tree structure, and the flashback snapshot records a mapping relationship between a system timestamp at the time of generation and the logical timestamp; after the flashback snapshot is generated, the MVCC historical data version with the same logical timestamp value in the flashback snapshot maintains transaction visibility in garbage collector recycling, and cannot be cleaned by the garbage collector until the corresponding flashback snapshot is deleted; the key value of the red-black tree is the system timestamp, and the snapshot query time complexity is O(logN), where N is the number of nodes in the tree.

[0012] As a preferred scheme of the version-based dynamic fast flashback query method in the database, the background flashback thread performs nonlinear interval deletion on the snapshot tree according to preset parameters while inserting a new flashback snapshot; the preset parameters comprise a full retention duration, a maximum retention duration and a retention interval; when the flashback snapshot is generated to a current time period less than the full retention duration, all are retained, when the flashback snapshot is generated to a current time period greater than the full retention duration and less than or equal to a double full retention duration interval, interval retention is performed according to the retention interval, when the flashback snapshot is generated to a current time period greater than a double full retention duration and less than or equal to a triple full retention duration interval, interval retention is performed according to a double retention interval, and so on, until the flashback snapshot retention duration exceeds the maximum retention duration and is deleted.

[0013] As a preferred scheme of the version-based dynamic fast flashback query method in the database, in the snapshot tree maintenance process, when the specified system timestamp corresponding to the fast flashback snapshot is deleted at an interval, an approximate query strategy is executed; the approximate query strategy comprises: automatically selecting an existing flashback snapshot closest to the system timestamp from the snapshot tree for version retrieval, extracting the corresponding logical timestamp in the flashback snapshot, returning the system timestamp of the approximate flashback snapshot to the user, and prompting the user that the system timestamp corresponding to the original query snapshot no longer exists.

[0014] As a preferred scheme of the version-based dynamic fast flashback query method in the database, when a user initiates a query through a SQL flashback query syntax, a snapshot node with a key value corresponding to a system timestamp is first searched in a snapshot tree, and a logical timestamp value is extracted; if there is no matching snapshot node, a snapshot node with a latest system timestamp is obtained according to the approximate query strategy, and a logical timestamp value is extracted.

[0015] As a preferred scheme of the version-based dynamic fast flashback query method in the database, after the logical timestamp value is obtained, a logical timestamp is traversed in a memory engine to locate an MVCC historical data version with the logical timestamp equal to the obtained logical timestamp value from the MVCC historical data version chain, and corresponding data is read and returned to the user; the MVCC historical data version chain traversal and the version writing of the DML transaction are avoided from concurrent conflicts through a read-write lock mechanism.

[0016] As a preferred scheme of the version-based dynamic fast flashback query method in the database, a logical relationship between the S1 and the S2 is that a mapping relationship between the system timestamp and the logical timestamp generated in the S1 is used as a unique index source for retrieval in the S2, and the logical timestamp value is only updated when a DML transaction is submitted, so as to ensure a one-to-one correspondence between the snapshot time point and the historical version chain.

[0017] In a second aspect, the present application provides a computer device, comprising a memory and a processor, and the memory stores a computer program, wherein the computer program instructions are executed by the processor to realize the steps of the version-based dynamic fast flashback query method in the database according to the first aspect of the present application.

[0018] In a third aspect, the present application provides a computer readable storage medium, which stores a computer program, wherein the computer program instructions are executed by the processor to realize the steps of the version-based dynamic fast flashback query method in the database according to the first aspect of the present application.

[0019] The application has the advantages that: the application adds an additional fast flashback query method on the basis of the traditional flashback query. The full memory state MVCC and the dynamic nonlinear flashback snapshot dotting mechanism constructed by the shared memory in the node keep the specific data version in the memory for the fast flashback query at any time. In the master-slave cluster multi-node scene, each node has its own full memory state MVCC mechanism. Whether it is a short-time flashback query after a misoperation or a long-time span query, the query efficiency is fast, stable and consistent. Especially for long-time span flashback query, the long-time disk access and log rollback processing are avoided, and the query performance is greatly improved compared with the traditional method. The dynamic nonlinear time interval snapshot mechanism makes the snapshots in the recent time period dense and the snapshots in the long time period sparse. The MVCC memory can be well controlled to avoid rapid expansion of the memory; at the same time, the snapshot interval density can be dynamically adjusted according to the user's flashback query effect to improve the flashback query efficiency. BRIEF DESCRIPTION OF DRAWINGS

[0020] In order to more clearly illustrate the technical solutions of the embodiments of the application, the following will briefly introduce the drawings needed to be used in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the application, and for those skilled in the art, other drawings can also be obtained from these drawings without creative labor.

[0021] Figure 1 It is a schematic diagram of the principle of the traditional flashback query technology.

[0022] Figure 2 It is a schematic diagram of the mapping relationship between the system timestamp timestamp and the logical timestamp tswrite.

[0023] Figure 3 It is a schematic diagram of the snapshot insertion snapshot tree.

[0024] Figure 4 It is a schematic diagram of the snapshot tree after the snapshots that do not need to be reserved after the reserved rule calculation.

[0025] Figure 5 It is a schematic diagram of the snapshot tree after the snapshot adjustment.

[0026] Figure 6 It is a schematic diagram of the snapshot interval deletion rule in the snapshot tree.

[0027] Figure 7 It is a flowchart of the dynamic fast flashback query method based on version. DETAILED DESCRIPTION

[0028] In order to make the above objectives, characteristics and advantages of the present application more apparent, a detailed description of the specific embodiments of the present application will be given below with reference to the accompanying drawings.

[0029] In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present application. The present application, however, can be practiced in other ways different from those described herein without departing from the scope of the present application. It is understood that variations can be made in view of what is described and understood that it would be within the skill of an artisan to make additional changes without departing from the scope of the present application.

[0030] Secondly, the "one embodiment" or "embodiment" referred to herein means a specific feature, structure or characteristic that can be included in at least one implementation of the present application. The "in one embodiment" appearing in different places in the specification does not mean the same embodiment, nor is it an embodiment that is independent of or mutually exclusive with other embodiments.

[0031] Embodiment 1

[0032] The flashback query technology currently used in the industry inevitably involves accessing disk pages and files. The specific scenarios are as follows Figure 1 As shown, a certain table has generated versions 1-6 from old to new in the time dimension due to transaction operations. Due to the MVCC space limit, versions 1-3 are moved into the flashback archive log. Although the MVCC can be accelerated through the memory cache page, when the memory cache page is not enough or is swapped out, the disk access cannot be avoided. For example, the MVCC stores versions 6, 5 and 4 of a certain row record. However, version 4 is stored in the disk. When the user needs to perform the flashback query on version 4, the version 4 needs to be loaded from the disk to the memory through the cache mechanism and then returned to the user, which causes the IO at this time.

[0033] The access of the flashback log file also involves a large amount of IO. If the user performs the flashback query on version 2, the version 2 is too old in time and has been moved to the disk-based flashback archive log, so the disk IO needs to be performed to obtain the version 2. This causes the poor performance of the flashback query, especially when the table data is queried for a long time, a large amount of file access and rollback processing will be performed. The present application increases an additional fast flashback query method on the basis of the traditional flashback query to solve the above problems.

[0034] Figure 7 A flowchart of a version-based dynamic fast flashback query method in a database according to an embodiment of the present application is shown in FIG. 1. Figure 7 As shown in FIG. 1, the version-based dynamic fast flashback query method in the database includes the following steps.

[0035] When the DML transaction produces a modification to the table, the MVCC in the full in-memory state saves each data version produced by the transaction in the table in the in-memory state. At the same time, a background thread performs the flashback snapshot puncturing, generates a flashback snapshot at each time point and saves it in the snapshot tree structure. The snapshot contains the current system timestamp timestamp and the logical timestamp tswrite of the current version corresponding to the current timestamp in the MVCC. When the user performs a flashback query at a specific time, the specified timestamp is brought into the snapshot tree for searching. The logical timestamp tswrite in the found snapshot is extracted, the version is searched in the in-memory state MVCC, and the final result set is generated.

[0036] S1: After the user submits a transaction, the MVCC history data version chain is generated and managed by using the full in-memory state MVCC mechanism, and a logical timestamp is allocated for each historical data version in the MVCC history data version chain.

[0037] S1.1: The MVCC history data version chain is implemented by using the in-memory engine, saves a plurality of historical data versions produced when the DML transaction is submitted, each historical data version records a logical timestamp generated at the time; wherein the logical timestamp is uniformly allocated by a global timestamp allocator.

[0038] It should be noted that the historical (multi) version information exists in the in-memory engine (HUB), and better multi-version query performance can be obtained; the current (unique) version exists in the data page cache, and is compatible with the storage engine based on a disk; the write operation updates the current version and produces the historical version, and the read operation reads the current version and the historical version; the multi-version loses the visibility, and then no transaction needs to read, and the GC is responsible for cleaning up in time, that is, the current data version is saved in the buffer or the page and is compatible with the storage engine based on a disk; if the MVCC historical data version is not visible to any transaction, instant cleaning is performed.

[0039] S1.2: A flashback thread generates a flashback snapshot at a fixed frequency (for example, 1 second), and stores the flashback snapshot in a red-black tree structure in a snapshot tree, the flashback snapshot records a mapping relationship between a system timestamp generated at the time and the logical timestamp.

[0040] It should be noted that the MVCC produces multiple versions, which is the basis of flashback query, and makes the data at each time into a version in time dimension. The next step of the flashback query is to query the data in the table by obtaining the version in the MVCC corresponding to the system timestamp timestamp. However, the system timestamp timestamp cannot be directly used to find a specific version in the MVCC, and the data saved in the MVCC version also saves the corresponding logical timestamp tswrite. The background snapshot thread produces a flashback snapshot in time dimension, and saves the mapping of the system timestamp timestamp and the logical time tswrite in the version in the MVCC at the time of producing the snapshot.

[0041] As shown in the mapping relationship as Figure 2 The specific contents are as follows:

[0042] The logical timestamp tswrite is globally and uniformly managed, and the initial value is 0. When the user performs a DML transaction submission, the logical timestamp tswrite is increased by 1, and the data version corresponding to the transaction is generated at the same time, and the modified logical timestamp tswrite is saved in the version. When the flashback snapshot is generated, the current value is obtained from the global logical timestamp tswrite, and the current system timestamp timestamp is saved in the snapshot. In this way, a set of mapping relationships of the system timestamp timestamp and the logical timestamp tswirte are obtained. Figure 2 As can be seen from the above table, only after the DML transaction is submitted, tswrite is modified. Therefore, the logical timestamp tswrite of the flashback snapshot A and the flashback snapshot B is 1, that is, the two snapshots can see the same data version 1, and there is no other transaction to change the data between the two snapshots. The logical timestamp tswrite of the flashback snapshot C is 3, and two transactions have occurred between the snapshot B, so only version 3 has visibility to it. In addition, the modification and query of the logical timestamp tswrite are avoided through the lock mechanism to avoid concurrent conflicts. In subsequent flashback query, the user specifies the system timestamp timestamp, and only by finding the corresponding flashback snapshot through the snapshot tree and obtaining tswrite therein, the corresponding visible version in the MVCC can be queried.

[0043] Further, when the flashback snapshot is generated, the MVCC historical data version with the same logical timestamp value tswrite in the flashback snapshot remains visible (that is, the version exists and cannot be deleted) in the garbage collector GC (Garbage Collecter) version recycling until the corresponding flashback snapshot is deleted.

[0044] Among them, the key value of the red-black tree is the system timestamp, and the snapshot query time complexity is O (logN), wherein N is the number of nodes in the red-black tree.

[0045] In addition, read and write of the snapshot tree avoid concurrent conflicts through read-write shared locks.

[0046] S2: During generation of the MVCC history data version chain, a background thread generates a flashback snapshot containing a system timestamp and a logical timestamp mapping relationship at a preset period, and inserts the flashback snapshot into a snapshot tree structure, and balances and deletes according to a retention rule to control snapshot density.

[0047] Specifically, during maintenance of the snapshot tree, when a specified system timestamp corresponding to a user query has been deleted at intervals, an approximate query strategy is executed.

[0048] The approximate query strategy includes: automatically selecting an existing flashback snapshot closest to the system timestamp from the snapshot tree for version retrieval, extracting the corresponding logical timestamp in the flashback snapshot, and returning the system timestamp of the approximate flashback snapshot to the user, while prompting the user that the flashback snapshot corresponding to the original query system timestamp no longer exists.

[0049] Preferably, insertion of the snapshot tree is as shown in Figure 3 The generated snapshot is saved in a red-black tree structure, and the key value of the red-black tree is the system timestamp timestamp of the snapshot. That is, the tree structure is sorted according to the system timestamp timestamp of the snapshot, which can improve the subsequent snapshot search performance.

[0050] Preferably, maintenance of the snapshot tree is as shown in Figures 4-5 During the process of continuously entering the snapshot into the snapshot tree, the background snapshot thread will also delete some snapshots from the tree according to certain rules. For example, although snapshot 2 is still within the retention time, snapshot 2 needs to be deleted by interval deletion rule calculation. After snapshot 2 is deleted, the snapshot tree is adjusted to a balanced state to maintain query performance.

[0051] Further, the background flashback thread performs non-linear interval deletion on the snapshot tree according to preset parameters while inserting a new flashback snapshot. The preset parameters include a full retention duration, a maximum retention duration, and a retention interval; when the flashback snapshot generation is less than the full retention duration, all are retained, when the flashback snapshot generation is greater than the full retention duration and less than or equal to twice the full retention duration interval, the snapshots are retained at the retention interval, when the flashback snapshot generation is greater than twice the full retention duration and less than or equal to three times the full retention duration interval, the snapshots are retained at twice the retention interval, and so on, until the flashback snapshot retention duration exceeds the maximum retention duration.

[0052] Exemplarily, the snapshot specific deletion rule is as shown in Figure 6As shown, `cur_time` is the current time; `full_ret_val` is the full retention period, which starts from the time the snapshot is generated and will be retained without deletion. Snapshots exceeding this period will enter an interval deletion phase, during which they may be deleted.

[0053] `max_ret_val` is the maximum retention period. If the retention time exceeds the maximum retention period from the time the snapshot is generated, the snapshot will definitely be deleted. `pro_time = cur_time - full_ret_val` is the starting point for interval deletion. `delete_time = cur_time - max_ret_val` is the starting point for direct deletion.

[0054] like Figure 6 As shown, snapshots 1-24 were generated along the time dimension. During each maintenance cycle, the snapshot thread determines which stage the system timestamp of a snapshot in the snapshot tree meets based on the parameters `full_ret_val` and `max_ret_val`. If it's greater than `pro_time`, it's retained; if it's between `pro_time` and `delete_time`, it's deleted at intervals; and if it's less than `delete_time`, it's deleted directly. The full retention time `full_ret_val` is 3 minutes, and the default value for the maximum retention time `max_ret_val` is 1440 minutes. The user can reset the `max_ret_val` parameter. The retention interval parameter `interval` defaults to 5 seconds.

[0055] For interval deletion rules Figure 6 This is for illustrative purposes only; the specific rules are shown in Table 1 below:

[0056] Table 1. Illustration of Interval Deletion Rules:

[0057]

[0058] The snapshot time period represents the time period from the generation start time of the snapshot to the current time. That is, as time goes on, each generated snapshot will constantly move to each time period. The snapshot in each time period will be determined whether to be deleted according to the corresponding retention time interval. For example, the snapshot within 3 minutes will not be deleted (the snapshot is generated in seconds granularity), the snapshot within (3, 6] minutes retains one snapshot every 5 seconds (the interval is 5 seconds by default), and the other snapshots are deleted. After the snapshot within (3, 6] minutes that is not deleted enters (6, 9] minutes, one is retained every 10 seconds (2 x interval), and the other snapshots are deleted. In turn, when the retention time of the snapshot that is not deleted exceeds the maximum retention time max_ret_val, it is completely deleted. Through non-linear interval deletion, the entire snapshot tree will exhibit the characteristics that the snapshot point density of the newer time period is larger, and the snapshot point density of the older time period is smaller. The purpose of this is to provide the user with a fast flash-back function while also ensuring that the memory does not expand rapidly.

[0059] In the production scene, the user will immediately perform a flash-back operation after a misoperation, so the density of the snapshot point of the time period close to the present time is larger (the snapshot point within 3 minutes is all retained). In this way, the user can accurately flash back to query the time point before the misoperation. For the snapshot point of the time period far from the present time (for example, several days ago, several months ago), the user only needs an approximate time point, so only a few points are retained, for example, only one day retains one snapshot point for the snapshot point more than 1 day ago.

[0060] When the user queries a certain specified timestamp, the timestamp snapshot may have been deleted in the interval interval, causing the query to fail. In view of this scenario, an approximate query and snapshot density dynamic feedback strategy are introduced. When the user specifies a timestamp snapshot that does not exist, the nearest adjacent snapshot to the timestamp is used for version query, and the user is prompted to return the approximate timestamp result. At the same time, the background will count the number of times of the user's specified timestamp query failure.

[0061] The snapshot density dynamic feedback will use the number of times of the user's flash-back query failure counted by the background to divide it into weak, medium, and strong three feedback levels according to the number of times of failure within each hour [0-5], [5-20] and [20- +∞]. The weak level interval is the default value 5s, the medium level interval is set to 3s, and the strong level interval is set to 1s. That is, the feedback level counted every hour will reset the interval value in the next hour to achieve the effect of dynamically adjusting the snapshot density in each time period. Thus, when the query error rate is high, the snapshot density is improved, and the user's query efficiency is improved. After the user stops querying for a period of time, the snapshot density is automatically reduced to the minimum, thereby reducing the consumption of memory.

[0062] The logical relationship between S1 and S2 is that the mapping relationship between the system timestamp and the logical timestamp generated in S1 is used as the unique index source for searching in S2, and the logical timestamp value is only updated when the DML transaction is committed, thereby ensuring one-to-one correspondence between the flashback snapshot time point and the historical version chain.

[0063] S3: When receiving a flashback query request, the matching system timestamp is searched in the snapshot tree, and the corresponding logical timestamp is extracted, and the corresponding MVCC historical data version is located in the MVCC historical data version chain.

[0064] When a user initiates a query through a SQL flashback query syntax, the key value is first searched in the snapshot tree as the corresponding system timestamp, and the logical timestamp value is extracted. Specifically, the user can use the SQL flashback query syntax as follows:

[0065] SELECT<column_name_list>FROM<table_name>AS OF TIMESTAMP <timestamp3>;

[0066] like Figure 7 As shown, when querying table data with a specific system timestamp (timestamp3), the system will search for snapshots with the same timestamp3 in the snapshot tree maintained in step S1. Since step S1 maintains and balances this tree every cycle, the query efficiency is high.

[0067] If no matching flashback snapshot node is found, the flashback snapshot node with the most recent system timestamp is obtained according to the approximate query strategy, and the logical timestamp value is extracted.

[0068] After obtaining the logical timestamp value, the memory engine iterates based on the logical timestamp to locate the MVCC historical data version with the logical timestamp equal to the obtained logical timestamp value from the MVCC historical data version chain, and reads the corresponding data and returns it to the user.

[0069] The MVCC historical data version chain traversal and DML transaction version writing are protected from concurrency conflicts through a read-write lock mechanism.

[0070] S4: Assemble the located MVCC historical data versions into query results and return them. Prompt the user when performing an approximate query. At the same time, feed the query results back to S2 to dynamically adjust the flashback snapshot density.

[0071] Specifically, after finding a specific snapshot, the logical timestamp within it is obtained, i.e. Figure 7 tswrite3, via Figure 7 In step S1, tswirte3 searches the data version chain within the HUB to find VER3 (data version 3 in the HUB). Once the version is found, the data is returned to the client for user use. Conflicts between version modifications (DML transactions) within the HUB and snapshot version lookups are resolved through a read-write lock mechanism to ensure consistency.

[0072] Example 2

[0073] To further verify the effectiveness of the present invention, the prior art was compared with our invention, as shown in Table 2 below:

[0074] Table 2 Comparison with Existing Technologies:

[0075]

[0076] The existing technology suffers from low query performance due to the fact that the version is stored on the disk, which involves disk I / O operations. This is especially true when flashback queries for versions that have been out of date, as the technology requires long-term I / O and rollback processing in the flashback log file.

[0077] And the flash query of the application is processed in memory without any IO. Especially when the version of the flash query is long, the efficiency is consistent with the query of the current version, and the performance is efficient and stable.

[0078] Further, in order to verify the beneficial effects of the application, the following is a performance comparison experiment of the application in a simple scenario.

[0079] Specifically, an example table MXYT1 is selected, and an automatic script is pre-written to perform fixed process addition, deletion and modification operations on the table every 1 second, and the modified content of the data in the table is random. Simulate the continuous update behavior of the data table in the long-time running process of the database.

[0080] After the above experiment runs for 24 hours, the flash query operation is performed on the MXYT1 table to retrieve the historical data 24 hours ago as a test index.

[0081] During the experiment, all tests are completed in the same hardware environment to ensure the comparability of the results. In the GBASE database system using the application technology, the execution time of the flash query is 1.095 seconds; while in other database systems without using the technology, the same query task takes 2.22 seconds. The comparison results show that the application technology can significantly shorten the query time and achieve nearly one-time performance improvement.

[0082] Therefore, the application can still quickly complete the flash query of historical data after processing long-time continuous high-frequency data updates, ensuring the stability and efficiency of the system in a long-time running scenario. This effect verifies the advantages of the application in the efficiency of database flash query, and shows that it can still effectively reduce query delay and improve the availability and real-time performance of data rollback after facing long-time high-frequency data operations, which has practical application value for large-scale data management scenarios.

[0083] The embodiment also provides a computer device suitable for the case of the dynamic fast flash query method based on version in the database, including a memory and a processor; the memory is used to store computer executable instructions, and the processor is used to execute the computer executable instructions to realize the dynamic fast flash query method based on version in the database as proposed in the above embodiment.

[0084] The computer device can be a terminal, and the computer device includes a processor, a memory, a communication interface, a display screen and an input device connected by a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operating system and the computer program in the non-volatile storage medium to run. The communication interface of the computer device is used to communicate with external terminals in a wired or wireless manner. The wireless manner can be achieved by WIFI, an operator network, NFC (near field communication) or other technologies. The display screen of the computer device can be a liquid crystal display screen or an electronic ink display screen. The input device of the computer device can be a touch layer overlaid on the display screen, or a key, trackball or touchpad arranged on the shell of the computer device, or an external keyboard, touchpad or mouse, etc.

[0085] The embodiment also provides a storage medium having a computer program stored thereon, and the program is executed by a processor to implement the version-based dynamic fast flashback query method in a database as proposed in the above embodiment.

[0086] In summary, the application adds an additional fast flashback query method on the basis of the traditional flashback query. The all-memory-state MVCC and the dynamic nonlinear flashback snapshot dotting mechanism constructed by the shared memory in the node retain a specific data version in the memory for long-term fast flashback query at any time. In the master-slave cluster multi-node scenario, each node has its own all-memory-state MVCC mechanism. Whether it is a short-time flashback query after a misoperation or a long-time span query, the query efficiency is fast, stable and consistent. Especially for long-time span flashback query, the long-time disk access and log rollback processing are avoided, and the query performance is greatly improved compared with the traditional method. The dynamic nonlinear time interval snapshot retention mechanism makes the snapshots in the recent time period dense and the snapshots in the long time period sparse. The MVCC memory can be well controlled to avoid rapid expansion of the memory; at the same time, the snapshot interval density can be dynamically fed back and adjusted according to the effect of the user's flashback query, and the flashback query efficiency is improved.

[0087] It should be noted that the above embodiments are only used to illustrate the technical solutions of the present application but not limit the present application. Although the present application has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solutions of the present application can be modified or replaced equivalently without departing from the spirit and scope of the present application, and all modifications and equivalent replacements should be covered in the scope of the claims of the present application.

Claims

1. A version-based dynamic fast flashback query method in a database, characterized by: The method comprises the following steps: S1: after a user submits a transaction, a MVCC history data version chain is generated and managed by using a full-memory state MVCC mechanism, and a logical timestamp is allocated for each history data version in the MVCC history data version chain; S2: while the MVCC history data version chain is generated, a background thread generates a flashback snapshot containing a mapping relationship between a system timestamp and a logical timestamp at a preset period, and inserts the flashback snapshot into a snapshot tree structure, and balances and deletes the snapshot tree structure according to a retention rule to control the snapshot density; S3: when a flashback query request is received, a matching system timestamp is searched in the snapshot tree, and a corresponding logical timestamp is extracted, and a corresponding MVCC history data version is located in the MVCC history data version chain; S4: the located MVCC history data version is assembled as a query result and returned, and a user is prompted during approximate query, and the query result is fed back to S2 to dynamically adjust the flashback snapshot density; The retention rule comprises: while a new flashback snapshot is inserted, the snapshot tree is deleted at a nonlinear interval according to preset parameters; the preset parameters comprise a full-quantity retention time length, a maximum retention time length and a retention interval; when a time period from a generation time of the flashback snapshot to a current time is less than the full-quantity retention time length, all the snapshots are retained; when the time period is greater than the full-quantity retention time length and less than or equal to a double of the full-quantity retention time length, the snapshots are retained at the retention interval; when the time period is greater than a double of the full-quantity retention time length and less than or equal to a triple of the full-quantity retention time length, the snapshots are retained at a double of the retention interval; and the process is repeated until the retention time length of the flashback snapshot exceeds the maximum retention time length and all the snapshots are deleted.

2. The version-based dynamic fast flashback query method in a database as described in claim 1, characterized in that: The S1 comprises: The MVCC history data version chain is implemented by using a memory engine, and a plurality of history data versions generated when a DML transaction is submitted are saved, and each history data version records a logical timestamp at the generation time; The logical timestamp is uniformly allocated by a global timestamp allocator; A current data version is saved in a buffer or a page, and is compatible with a disk-based storage engine; When a certain MVCC history data version in the MVCC history data version chain is invisible to any transaction, the MVCC history data version is cleaned immediately.

3. The version-based dynamic fast flashback query method in a database as described in claim 1, characterized in that: Flashback snapshots are generated by a background flashback thread at a fixed frequency, and the flashback snapshots are stored in a snapshot tree in a red-black tree structure, and the flashback snapshots record a mapping relationship between a system timestamp and a logical timestamp at the generation time; When the flashback snapshot is generated, the MVCC history data version with the same logical timestamp value as the flashback snapshot is kept transactionally visible in a garbage collector version recycling, and cannot be cleared by the garbage collector until the corresponding flashback snapshot is deleted; The key value of the red-black tree is the system timestamp, and the snapshot query time complexity is O(logN), wherein N is the number of nodes in the tree.

4. The version-based dynamic fast flashback query method in the database as described in claim 1, characterized in that: During the maintenance of the snapshot tree, when a specified system timestamp corresponding to a snapshot has been deleted at an interval, an approximate query strategy is executed. The approximate query strategy comprises: automatically selecting a closest existing flashback snapshot from the snapshot tree to the system timestamp for version retrieval, and extracting the corresponding logical timestamp in the flashback snapshot, while returning the system timestamp of the approximate flashback snapshot to the user and prompting the user that the system timestamp corresponding to the original query has not existed.

5. The version-based dynamic fast flashback query method in a database as described in claim 4, characterized in that: When the user initiates a query through the SQL flashback query syntax, first, the key value of the flashback snapshot node corresponding to the system timestamp is retrieved in the snapshot tree, and the logical timestamp value is extracted; If there is no matching flashback snapshot node, the approximate query strategy is used to obtain the flashback snapshot node of the nearest system timestamp, and the logical timestamp value is extracted.

6. The method as claimed in claim 5, wherein the version based dynamic fast flashback query in a database is based on the following steps: After obtaining the logical timestamp value, the logical timestamp is traversed in the in-memory engine to locate the MVCC historical data version chain with the logical timestamp equal to the extracted logical timestamp value, and the corresponding data is read and returned to the user; The MVCC historical data version chain traversal and the version writing of the DML transaction avoid concurrent conflicts through the read-write lock mechanism.

7. A version-based dynamic fast flashback query method for a database as described in claim 6, characterized in that: The logical relationship between the S1 and the S3 is that the mapping relationship between the system timestamp and the logical timestamp generated in the S1 is used as the unique index source for retrieval in the S3, and the logical timestamp value is only updated when the DML transaction is submitted, so that the one-to-one correspondence between the flashback snapshot time point and the historical version chain is ensured.

8. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that: The processor executes the computer program to implement the steps of the database-based dynamic fast flashback query method according to any one of claims 1-7.

9. A computer readable storage medium having stored thereon a computer program, characterized in that: The computer program is executed by the processor to implement the steps of the database-based dynamic fast flashback query method according to any one of claims 1-7.

Images