Database flashback query method, system, storage medium and device based on value log

Abstract

The present invention discloses a database flashback query method, system, storage medium and device based on value log, wherein the database flashback query method comprises: S1, when writing physical records in memory into value log file, the value log primary key index saves the offset address of the physical record in the value log file; S2, receiving and parsing the flashback query request, and determining the flashback point; S3, determining the flashback query cutoff offset address from the flashback point, and searching the value log through the index to find the data whose offset address is less than or equal to the flashback query cutoff offset address. The method enables the database to realize the flashback query of any valid flashback point from the initialization to the current moment.

Description

Technical Field

[0001] The present invention belongs to the technical field of databases, and in particular relates to a database flashback query method, system, storage medium and device based on value logs. Background Art

[0002] Flashback query can make the database roll back to the state at a specified point in time. In traditional databases such as Oracle, flashback query is implemented based on rollback segments. A rollback segment is a space on the disk that is used to store the value before the data is modified. If Oracle wants to support flashback query, you need to first set the rollback segment to automatic management status, and then set the maximum retention time of the rollback segment. Oracle's default maximum retention time for rollback segments is 15 minutes, which means that after setting the rollback segment to automatic management status, Oracle can support flashback queries within 15 minutes from the current system time by default. Due to the limited space of the rollback segment, only flashback queries within a limited time period are supported. If you need to modify the rollback segment settings, you also need to restart the database server. Summary of the invention

[0003] Purpose of the invention: The present invention provides a database flashback query method, system, storage medium and device based on value log, aiming to enable the database to implement flashback query of any valid flashback point from self-initialization to the current moment.

[0004] Technical solution: On one hand, the present invention provides a database flashback query method based on value log, comprising:

[0005] S1. When writing a physical record in the memory into a value log file, the value log primary key index stores the offset address of the physical record in the value log file;

[0006] S2, receiving and parsing the flashback query request, and determining the flashback point;

[0007] S3. Determine the flashback query cutoff offset address from the flashback point, and search the value log through the index to find data whose offset address is less than or equal to the flashback query cutoff offset address.

[0008] Preferably, when the flashback point contained in the flashback query request is the cutoff LSN, the value log file data block corresponding to the cutoff LSN is queried, and the physical records are sequentially scanned in the data block to obtain the offset address of the physical record corresponding to the cutoff LSN, which is the flashback query cutoff offset address.

[0009] Preferably, the value log system writes the time when the first physical record of the data block is written into the header of the value log file data block, and / or writes the time when the last physical record of the data block is written into the tail of the value log file data block;

[0010] When the flashback point included in the flashback query request is a deadline, a value log file data block corresponding to the deadline is searched, and the offset address of the searched data block is the flashback query deadline offset address.

[0011] As another preferred embodiment, the value log system establishes a time table corresponding to the value log file, and regularly inserts a time record into the time table, wherein the time record includes the current time and the physical record closest to the current time;

[0012] When the flashback point included in the flashback query request is a deadline, the time record closest to the deadline in the time table is searched, and the offset address of the physical record included in the time record is the flashback query deadline offset address.

[0013] As another preferred embodiment, the value log system first saves the current system time as the data insertion time into the physical record, and then writes the physical record into the value log file;

[0014] When the flashback point included in the flashback query request is a deadline, a physical record corresponding to the data insertion time and the deadline is searched, and the offset address of the found physical record is the flashback query deadline offset address.

[0015] On the other hand, the present invention also discloses a database flashback query system based on value logs for implementing the above query method, comprising:

[0016] An offset address saving module, used for saving the offset address of the physical record in the value log file when writing the physical record in the memory into the value log file;

[0017] A flashback point determination module, used to determine a flashback point according to a flashback query request;

[0018] A flashback query cutoff offset address determination module, used to determine the flashback query cutoff offset address from the flashback point;

[0019] The flashback query operation module is used to retrieve the value log through the index and find the data whose offset address is less than or equal to the flashback query cutoff offset address.

[0020] Preferably, when the flashback point included in the flashback query request is a cutoff LSN, the step of determining the flashback query cutoff offset address by the flashback query cutoff offset address determination module is:

[0021] The value log file data block corresponding to the cut-off LSN is queried, and the physical records are sequentially scanned in the data block to obtain the offset address of the physical record corresponding to the cut-off LSN, which is the flashback query cut-off offset address.

[0022] Preferably, the value log system first saves the current system time as the data insertion time into the physical record, and then writes the physical record into the value log file;

[0023] The flashback query cutoff offset address determination module determines the flashback query cutoff offset address in the following steps: when the flashback point included in the flashback query request is the cutoff time, search for the physical record corresponding to the data insertion time and the cutoff time, and the offset address of the found physical record is the flashback query cutoff offset address.

[0024] On the other hand, the present invention further discloses a computer-readable storage medium on which computer instructions are stored. When the computer instructions are executed, the above database flashback query method is executed.

[0025] On the other hand, the present invention also discloses a database flashback query device, including a processor and a storage medium, wherein the storage medium is the above-mentioned computer-readable storage medium; the processor loads and executes instructions and data in the storage medium to implement the above-mentioned database flashback query method.

[0026] Beneficial effect: The database flashback query method based on the value log disclosed in the present invention performs flashback query according to the offset address physically recorded in the value log file, and can flash back to the time corresponding to any valid cutoff offset address from the initialization of the database to the current appropriate time. At the same time, no special settings are required for the system and it is not limited by the rollback segment space. BRIEF DESCRIPTION OF THE DRAWINGS

[0027] Figure 1 The present invention discloses a flow chart of a database flashback query method based on a value log;

[0028] Figure 2 A schematic diagram of the composition of a database flashback query system based on a value log disclosed by the present invention;

[0029] Figure 3 The present invention discloses a schematic diagram of the composition of a database flashback query device based on a value log. DETAILED DESCRIPTION

[0030] The present invention is further explained below in conjunction with the accompanying drawings and specific implementation methods.

[0031] Embodiment 1:

[0032] The Chinese invention patent application with application number 2021107280253 discloses a method for implementing a value log that integrates data and logs, wherein the value log achieves the advantage of only sequential writing and avoiding writing multiple copies of data during the persistence process of the database and data processing system. Based on the aforementioned invention patent application, the present invention proposes a database flashback query method based on a value log, such asFigure 1 As shown, including:

[0033] S1. When writing a physical record in the memory into a value log file, the value log primary key index stores the offset address of the physical record in the value log file;

[0034] In the Chinese invention patent application with application number 2021107280253, the implementation process of the value log is as follows: (1) the working thread or process in the data processing system parses the received operation request and determines the operation type; (2) when the operation type is a data update operation, the working thread or process converts the deletion and modification operations into insert operations; (3) the working thread or process performs the insert operation in the memory and generates and finally writes the physical records in the memory in the same format as the physical records in the disk storage device, and then writes the physical records in the memory directly to the value log file, or first writes them to the value log buffer, waiting for the disk writing thread or process to flush the disk, and after the disk flushing is completed, sends the operation results to the client; (4) when the value log buffer is enabled, the disk writing thread or process continuously writes the physical records of the value log buffer sequentially to the end of the value log file, and notifies the working thread or process to complete the disk flushing task. After the above steps 1-4 are executed, the modified data in the memory completes the persistence operation of writing to the disk. In an embodiment of the present invention, when the physical records in the memory are written into the value log file in step (3), the value log primary key index saves the offset address of the physical record in the value log file. Since the physical record is written to the end of the log file, the offset address of the physical record increases as data is continuously written.

[0035] S2, receiving and parsing the flashback query request, and determining the flashback point;

[0036] The working thread / process receives a flashback query request sent by a user, and the flashback query request includes a flashback point; in this embodiment, the flashback point is an offset address.

[0037] S3. Determine the flashback query cutoff offset address from the flashback point, and search the value log through the index to find data whose offset address is less than or equal to the flashback query cutoff offset address.

[0038] The working thread / process determines the offset address corresponding to the flashback point as the flashback query cutoff offset address; since the primary key index saves the offset address of the physical record, the data with an offset address less than or equal to the flashback query cutoff offset address is found and returned through the index retrieval value log; the data with a physical record offset address greater than the flashback query cutoff offset address is deemed to be invisible to the current query. For details of index retrieval data, see Chinese patent application number 2021109183737. In order to quickly retrieve the results, the primary key index in this embodiment performs data retrieval through binary search.

[0039] You can also specify the cutoff offset address for only some tables. In the primary key index, the key value is composed of the actual key value and the table name. When you need to query only some tables, enter the actual primary key value and the table to be queried and directly perform a binary search in the primary key index to locate the maximum offset of the corresponding table. The system will check the offset address when accessing only the index of this part of the table. Finally, the latest version of visible data within the cutoff offset address specified by the user is returned.

[0040] The database flashback query system that implements the query method in this embodiment is as follows: Figure 2 As shown, including:

[0041] An offset address saving module, used for saving the offset address of the physical record in the value log file when writing the physical record in the memory into the value log file;

[0042] A flashback point determination module, used to determine a flashback point according to a flashback query request;

[0043] A flashback query cutoff offset address determination module, used to determine the flashback query cutoff offset address from the flashback point;

[0044] The flashback query operation module is used to retrieve the value log through the index and find the data whose offset address is less than or equal to the flashback query cutoff offset address.

[0045] Embodiment 2:

[0046] The difference between this embodiment and the first embodiment is that the flashback point included in the flashback query request is the cutoff LSN instead of the cutoff offset address.

[0047] The entire value log is composed of data blocks and physical blocks, wherein the physical blocks are composed of a certain number of data blocks, and the data blocks record LSN information. In the value log system, the value log file can be simply understood as being composed of at least one data block in ascending order of LSN, and the physical records in the data block are saved in ascending order of LSN. Therefore, a binary search method is used to determine the corresponding data block in the physical block according to the LSN, and then the physical records are sequentially scanned in the data block to obtain the offset address of the physical record corresponding to the cut-off LSN, and the offset address is the flashback query cut-off offset address. Then, according to the steps in Example 1, the value log is retrieved through the index to find the data whose offset address is less than or equal to the flashback query cut-off offset address.

[0048] Embodiment three:

[0049] The difference between this embodiment and the first embodiment is that the value log system writes the time when the first physical record of the data block is written at the header of the value log file data block, and may also write the time when the last physical record of the data block is written at the tail of the value log file data block; that is, each data block records the time when the data block is first written or last written, and may also record the time of the first write and the last write at the same time.

[0050] When the flashback point included in the flashback query request is a deadline, search for the value log file data block corresponding to the deadline. Here, you can search based on the time when the data block was first written or the time when it was last written, that is, the data block whose first write time or the last write time is less than or equal to the deadline, and is closest to the deadline; the offset address of the data block found is the flashback query deadline offset address, and then search the value log through the index according to the steps in Example 1 to find data with an offset address less than or equal to the flashback query deadline offset address. The method of this embodiment is suitable for situations where time accuracy is not required.

[0051] Embodiment 4:

[0052] This embodiment is similar to the third embodiment, and both use the expiration time as the flashback point.

[0053] The difference between this embodiment and the first embodiment is that: the value log system establishes a timetable corresponding to the value log file, and regularly inserts a time record into the timetable, wherein the time record includes the current time and the physical record closest to the current time;

[0054] When the flashback point included in the flashback query request is a deadline, the time record closest to the deadline in the search time table is searched, and the offset address of the physical record included in the time record is the flashback query deadline offset address. Then, according to the steps in Example 1, the value log is retrieved through the index to find the data whose offset address is less than or equal to the flashback query deadline offset address.

[0055] Embodiment five:

[0056] The difference between this embodiment and the first embodiment is that the value log system first saves the current system time as the data insertion time in the physical record, and then writes the physical record into the value log file; thus, each physical record in the value log file contains the data insertion time of the physical record;

[0057] When the flashback point included in the flashback query request is the deadline, the physical record corresponding to the data insertion time and the deadline is searched, and the offset address of the found physical record is the flashback query deadline offset address.

[0058] The method in this embodiment can accurately find the physical record that is closest to the flashback point deadline and is less than or equal to the deadline, and is suitable for situations where the time accuracy requirement is not high.

[0059] Embodiment six:

[0060] This embodiment assumes that the keyword of the flashback query is flashback (indicating that it contains flashback query), followed by a flashback point, which can be an offset (indicating a flashback end offset address), or an LSN (indicating a flashback end LSN), or a timestamp (indicating a flashback time point).

[0061] In the value log system, there is a table called account whose column information is shown in Table 1. The page size of the system is assumed to be 10,000 bytes:

[0062] Table 1 Column information of the account table

[0063] Column Name Column Type Column Meaning id varchar(20) Account ID, primary key, the system time information at the time of record insertion is included in the index name varchar(20) Account Name balance int Account Balance

[0064] Three records are inserted into the account table, as shown in Table 2:

[0065] Table 2 Data operation history of the account table

[0066] Operation Time Operation Type Corresponding SQL 2021-07-01 13:00:00 Insert insert into account values('A001','张三',110); 2021-07-15 14:00:00 Insert insert into account values('A002','李四',120); 2021-07-16 17:30:00 Insert insert into account values('A003','王五',130);

[0067] After the insertion, the user can query all the data information, data offset address, LSN information and the timestamp of the insertion in the account table (the physical record contains the insertion time) through the following statement:

[0068] Select offset,lsn,inserting_time,id,name,balance from account;

[0069] The query results are shown in Table 3:

[0070] Table 3 Data information of the account table

[0071] offset lsn Inserting_time id name balance 640100 101 2021-07-01 13:00:00 A001 张三 110 640200 102 2021-07-15 14:00:00 A002 李四 120 640300 103 2021-07-16 17:30:00 A003 王五 130

[0072] According to the offset in Table 3, we can know that the data of the account table is all on page 65. The specific steps of this method to execute the flashback query are:

[0073] S101: The user executes the following query statement on the client:

[0074] select*from account flashback timesamp'2021-07-01 13:00:00.000';

[0075] The data processing system such as database / KV receives the query request.

[0076] S102: The working thread / process parses the user's data query statement.

[0077] S103: The working thread / process determines the flashback point based on the result of parsing the data query statement in S102. The query statement has the flashback query keyword flashback, indicating that a flashback query is to be performed, and has the timestamp keyword, indicating that the flashback time point is limited according to the deadline. The deadline to be queried is '2021-07-01 13:00:00.000'. This statement can query the latest data visible in the account table.

[0078] S104: The working thread / process first uses the binary search method to find that the data block corresponding to the cutoff time point 2021-07-01 13:00:00.000 is page 65, and sequentially scans the records on this page to find that the corresponding cutoff offset address is 640100, that is, the offset address of the query result of the account table must be less than or equal to 640100. The flashback query time is 2021-07-01 13:00:00.000, and the records with offset addresses 640200 and 640300 are not visible at this time point.

[0079] Then the worker thread / process searches the account table based on the primary key. The offset address in the index record needs to be less than or equal to 640100. After traversing the index, it can be found that only the record with the primary key value of 'A001' meets the conditions. The query results are shown in Table 4:

[0080] Table 4 Account table flashback query results

[0081] id name balance 'A001' '张三' 110

[0082] S105: The working thread / process returns the data retrieved in the above steps to the client.

[0083] The steps for flashback query from the perspective of database flashback query system are as follows:

[0084] When the value log system writes the physical record in the memory into the value log file, the offset address storage module stores the offset address of the physical record in the value log file in the value log primary key index;

[0085] The user executes the following query statement on the client:

[0086] select*from account flashback offset 640250;

[0087] The flashback point determination module determines the flashback point according to the query statement;

[0088] The flashback query cutoff offset address determination module determines the value of the flashback query cutoff offset address from the flashback point to be 640250;

[0089] The flashback query operation module searches the account table based on the primary key. The offset address in the index record must be less than or equal to 640250. After traversing the index, it can be found that only the records with primary key values ​​'A001' and 'A002' meet the conditions. The query results are shown in Table 5:

[0090] Table 5 Account table flashback query results

[0091] id name balance 'A001' '张三' 110 'A002' '李四' 120

[0092] The above query results are returned to the client, realizing flashback query.

[0093] The checkpoint implementation device disclosed in the present invention is as follows: Figure 3 As shown, it includes a processor 31 and a storage medium 32. The storage medium 32 is a computer-readable storage medium on which computer instructions are stored. When the computer instructions are run, the steps of the database flashback query method disclosed in the present invention are executed; the processor 31 loads and executes the instructions and data in the storage medium 32 to implement the above-mentioned database flashback query method.

Claims

1. A database flashback query method based on value log, characterized in that: include: S1. When the physical records in the memory are written into the value log file in ascending order of LSN, the value log primary key index stores the offset address of the physical records in the value log file; S2, receiving and parsing the flashback query request, and determining the flashback point; S3, determine the flashback query cutoff offset address from the flashback point, and search the value log through the index to find the data whose offset address is less than or equal to the flashback query cutoff offset address; The physical records in the memory are written into the value log file in ascending order of LSN, including: The value log system writes the time when the first physical record of the data block is written into the header of the data block of the value log file, and / or writes the time when the last physical record of the data block is written into the tail of the data block of the value log file; The step of determining the flashback query cutoff offset address from the flashback point includes: When the flashback point included in the flashback query request is a deadline, searching for a value log file data block corresponding to the deadline, and the offset address of the found data block is the flashback query deadline offset address; The value log system establishes a timetable corresponding to the value log file, and regularly inserts a time record into the timetable, wherein the time record includes the current time and the physical record closest to the current time; Alternatively, the determining of the flashback query cut-off offset address from the flashback point includes: when the flashback point included in the flashback query request is a cut-off time, searching for a time record closest to the cut-off time in the time table, and the offset address of the physical record included in the time record is the flashback query cut-off offset address; The value log system first saves the current system time as the data insertion time into the physical record, and then writes the physical record into the value log file; Alternatively, determining the flashback query cutoff offset address from the flashback point includes: when the flashback point included in the flashback query request is a cutoff time, searching for a physical record whose data insertion time corresponds to the cutoff time, and the offset address of the found physical record is the flashback query cutoff offset address.

2. The database flashback query method according to claim 1, characterized in that: When the flashback point included in the flashback query request is the cutoff LSN, the value log file data block corresponding to the cutoff LSN is queried, and the physical records are sequentially scanned in the data block to obtain the offset address of the physical record corresponding to the cutoff LSN, which is the flashback query cutoff offset address.

3. A database flashback query system based on value log, characterized in that: include: An offset address saving module, used for saving the offset address of the physical record in the value log file by the value log primary key index when writing the physical record in the memory into the value log file in ascending order of LSN. When writing the physical record in the memory into the value log file, saving the offset address of the physical record in the value log file; A flashback point determination module is used to receive and parse the flashback query request, determine the flashback point and determine the flashback point according to the flashback query request; A flashback query cutoff offset address determination module, used to determine the flashback query cutoff offset address from the flashback point; A flashback query operation module is used to retrieve the value log through the index and find the data whose offset address is less than or equal to the flashback query cutoff offset address; In the offset address storage module, the physical records in the memory are written into the value log file in ascending order of LSN, including: writing the time when the first physical record of the data block is written into the header of the data block of the value log file, and / or writing the time when the last physical record of the data block is written into the tail of the data block of the value log file; The value log system first saves the current system time as the data insertion time into the physical record, and then writes the physical record into the value log file; The flashback query cutoff offset address determination module determines the flashback query cutoff offset address by: when the flashback point included in the flashback query request is the cutoff time, searching for a physical record whose data insertion time corresponds to the cutoff time, and the offset address of the found physical record is the flashback query cutoff offset address; Alternatively, when the flashback point included in the flashback query request is a deadline, a time record closest to the deadline in the time table is searched, and the offset address of the physical record included in the time record is the flashback query deadline offset address; Alternatively, when the flashback point included in the flashback query request is a deadline, a physical record corresponding to the data insertion time and the deadline is searched, and the offset address of the found physical record is the flashback query deadline offset address.

4. The database flashback query system according to claim 3, characterized in that: When the flashback point included in the flashback query request is a cutoff LSN, the step of determining the flashback query cutoff offset address by the flashback query cutoff offset address determination module is as follows: The value log file data block corresponding to the cut-off LSN is queried, and the physical records are sequentially scanned in the data block to obtain the offset address of the physical record corresponding to the cut-off LSN, which is the flashback query cut-off offset address.

5. A computer-readable storage medium having computer instructions stored thereon, characterized in that: When the computer instructions are executed, the database flashback query method according to any one of claims 1 to 2 is executed.

6. A database flashback query device, characterized in that: It comprises a processor and a storage medium, wherein the storage medium is the computer-readable storage medium as described in claim 5; the processor loads and executes instructions and data in the storage medium to implement the database flashback query method as described in any one of claims 1 to 2.

Images