A data processing method, device and storage medium based on relational database

Abstract

The present application provides a data processing method, device and storage medium based on a relational database. The method includes: establishing a new table and performing DDL statement operations on it. Then rewriting the table data, adding an exclusive lock to the original table, recording the start log sequence number, starting the WAL log tracking thread, and performing lock downgrade at the same time. Generate a temporary log table, generate new records from the original table records in sequence, insert them into the new table, and insert the mapping relationship between the original table record TID and the new table record TID into the temporary log table. At the end of migration, restore the lock level, record the end log sequence number, and close the WAL log tracking thread. Analyze the incremental log file to determine whether there is a new DML operation in the original table and its status is committed or rolled back. If it has been committed, merge the new DML operation into the new table, and the merge of the incremental DML operation is completed. Merge the original table with the new table, complete the data rewriting, and complete the DDL statement operation.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a data processing method, device and storage medium based on a relational database. Background Art

[0002] In the daily operation and maintenance of a relational database, Data Definition Language (DDL) statements can be executed to define relational schemas, delete relations, modify relational schemas, and create various data in the database.

[0003] Currently, executing a DDL statement involves creating a new table with the same structure as the original, copying the original table's data to the new table, and then renaming the table. When executing a DDL statement, an exclusive lock is required to block all operations related to the locked object.

[0004] Therefore, when executing DDL statements on a large table and rewriting table data (table rewrite), an exclusive lock must be held for a long time. Therefore, if the large table is frequently accessed, it may affect business operations for a long time, or even require business suspension to execute DDL, seriously affecting normal business operations. Summary of the Invention

[0005] The present application provides a data processing method, device and storage medium based on a relational database, which is used to solve the problem in the prior art that when executing a DDL operation, holding an exclusive lock on the original table for a long time will seriously affect the normal operation of the business.

[0006] In a first aspect, the present application provides a data processing method based on a relational database, comprising:

[0007] Obtaining an execution request for an online DDL statement for an original table in the relational database;

[0008] According to the execution request, a new table with the same structure as the original table is created, and an exclusive lock is added to the original table;

[0009] When monitoring and determining that a table data rewrite operation in an online DDL statement is being executed, recording a start log sequence number of a WAL log tracking thread, starting the WAL log tracking thread, and downgrading the exclusive lock;

[0010] Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TIDs corresponding to the migrated records in the original table and the TIDs corresponding to the same records in the new table into the temporary log table until the migration is completed;

[0011] Restore the exclusive lock level, record the ended log sequence number, and close the WAL log tracking thread;

[0012] The incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, and the table data rewriting operation is completed based on the original table and the merged new table.

[0013] In the above-mentioned data processing method based on a relational database, the incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including:

[0014] The incremental log file is analyzed to determine whether there is an incremental delete operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental delete operation in the original table and it is in a committed state, the TID of the incremental delete operation in the original table is obtained from the incremental log file, and a temporary table is queried to obtain a TID in the new table that has a mapping relationship with the TID of the incremental delete operation, and the record of the TID that has a mapping relationship with the TID of the incremental delete operation is deleted.

[0015] In the above-mentioned data processing method based on a relational database, the incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including:

[0016] Analyze the incremental log file to determine whether there is an incremental insert operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental insert operation in the original table and it is in a committed state, obtain the TID of the incremental insert operation in the original table from the incremental log file;

[0017] Scan the original table to obtain the record corresponding to the TID for the incremental insert operation in the original table, insert the record corresponding to the TID for the incremental insert operation into the new table, and insert the mapping relationship between the TID for the incremental insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table.

[0018] In the above-mentioned data processing method based on a relational database, the incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including:

[0019] Analyze the incremental log file to determine whether there is an incremental update operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental update operation in the original table and it is in a committed state, obtain the TID of the delete operation in the incremental update operation in the original table, the TID of the insert operation, and the record of the TID of the insert operation;

[0020] Query the temporary table to obtain the TID in the new table that has a mapping relationship with the TID for the incremental delete operation, and delete the record of the TID that has a mapping relationship with the TID for the incremental delete operation.

[0021] Scan the original table to obtain the record corresponding to the TID for the insert operation in the original table, insert the record corresponding to the TID for the insert operation into the new table, and insert the mapping relationship between the TID for the insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table.

[0022] In the above-mentioned data processing method based on a relational database, the downgrading of the exclusive lock includes:

[0023] Downgrading the exclusive lock from an access exclusive lock to a shared update exclusive lock;

[0024] Then, restoring the level of the exclusive lock includes:

[0025] Restore from the shared update exclusive lock to the access exclusive lock.

[0026] In the above-mentioned data processing method based on a relational database, if there is an incremental DML operation in the original table and it is in a rollback state, the data migration process is completed based on the original table and the new table.

[0027] In the above-mentioned data processing method based on a relational database, a checkpoint operation is performed on the database according to the pre-added minimum start log sequence number of the global variable DDL operation, and when the start log sequence number in the WAL log file is less than the minimum start log sequence number, it is determined to clear the WAL log file;

[0028] The WAL log file includes an incremental log file.

[0029] In a second aspect, the present application provides a data processing device based on a relational database, comprising:

[0030] An acquisition module is used to obtain an execution request for an online DDL statement for an original table in the relational database;

[0031] a processing module, configured to create a new table having the same structure as the original table according to the execution request, and add an exclusive lock to the original table;

[0032] The processing module is further configured to record a start log sequence number of a WAL log tracking thread when monitoring and determining the execution of a table data rewrite operation in an online DDL statement, start the WAL log tracking thread, and downgrade the exclusive lock;

[0033] The processing module is further configured to generate a temporary log table and start scanning the original table to sequentially migrate records in the original table to the new table, and insert a mapping relationship between TIDs corresponding to migrated records in the original table and TIDs corresponding to identical records in the new table into the temporary log table until migration is completed;

[0034] The processing module is further configured to restore the level of the exclusive lock, record the end log sequence number of the original table, and close the WAL log tracking thread;

[0035] The processing module is further configured to analyze the incremental log files obtained by the WAL log tracking thread to determine whether there are incremental DML operations in the original table and whether the operation is in a committed state or a rollback state. If there are incremental DML operations in the original table and the operation is in a committed state, the incremental DML operations are merged into the new table, and the table data rewriting operation is completed based on the original table and the merged new table.

[0036] In a third aspect, the present application provides a server, comprising: at least one processor and a memory;

[0037] The memory stores computer-executable instructions;

[0038] The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

[0039] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are used to implement the method described above when executed by a processor.

[0040] The present application provides a data processing method, device and storage medium based on a relational database. In this method, when performing online DDL statement operations on the original table of the database, a new table with the same table structure as the original table is established, and the DDL statement operations are performed on the new table. Then, the table data rewriting operation is performed. First, an exclusive lock is added to the original table, the starting log sequence number of the WAL log tracking thread is recorded, and the WAL log tracking thread is started, and the exclusive lock is downgraded at the same time. Then a temporary log table is generated, and the original table is scanned. New records are generated for the obtained original table records in sequence and inserted into the new table. At the same time, the mapping relationship between the original table record TID and the new table record TID is inserted into the temporary log table until all records on the original table are migrated. The exclusive lock level is restored, the end log sequence number is recorded, and the WAL log tracking thread is closed at the same time. The incremental log files obtained by the WAL log tracking thread tracking are analyzed to determine whether there are new DML operations in the original table, and the type of the new DML operation is analyzed to determine whether the new DML operation is in the committed state or the rollback state. If the new DML operation is in the committed state, the new DML operation is merged into the new table, and the merging of the incremental DML operation is completed. The original table and the new table are merged and processed to complete the rewriting of the data operation, and the DDL statement operation is completed. Compared with the existing technology, the exclusive lock added in the DDL statement operation will block all operations related to the locked object, thereby seriously affecting the normal operation of the business. In this application, the exclusive lock is downgraded in the DDL statement operation, allowing DML operations to be performed on the original table. At the same time as rewriting the table data, the WAL log tracking thread is started to record and obtain the incremental log file, and based on the incremental log file, the consistency of the original table and the new table data is achieved. Therefore, this application not only ensures the consistency of the migration, but also ensures that the normal operation of the business can be effectively maintained during the migration process, thereby effectively improving the performance and efficiency of database operation. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0042] Figure 1 A flowchart of a first embodiment of a data processing method based on a relational database provided in an embodiment of the present application;

[0043] Figure 2 A flowchart of a second embodiment of a data processing method based on a relational database provided in an embodiment of the present application;

[0044] Figure 3 A flowchart of a third embodiment of a data processing method based on a relational database provided in an embodiment of the present application;

[0045] Figure 4 A flowchart of a fourth embodiment of a data processing method based on a relational database provided in an embodiment of the present application;

[0046] Figure 5 A schematic diagram of the structure of an embodiment of a data processing device based on a relational database provided in an embodiment of the present application;

[0047] Figure 6 A schematic diagram of the structure of a server device embodiment provided in an embodiment of the present application.

[0048] The above drawings illustrate specific embodiments of the present application, which will be described in more detail below. These drawings and the textual description are not intended to limit the scope of the present application in any way, but rather to illustrate the concepts of the present application to those skilled in the art by reference to specific embodiments. DETAILED DESCRIPTION

[0049] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The embodiments described in the following exemplary embodiments are not intended to represent all embodiments consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present application, as detailed in the appended claims.

[0050] DDL is a language that is responsible for defining data structures and database objects using the Structured Query Language (SQL). It consists of four syntaxes: CREATE, ALTER, DROP, and TRUNCATE. The definition language is used to define relationship models, delete relationships, modify relationship models, and create various objects in the database, such as tables, indexes, views, functions, stored procedures, triggers, etc.

[0051] Data Manipulation Language (DML) is used to insert, delete, and modify tuples. It mainly consists of the insert, update, and delete syntax.

[0052] Write-Ahead Logging (WAL) is a historical record of all changes and actions in a database system. The transaction log contains information about each executed transaction. The database server can recover the database by replaying the changes and actions in the transaction log.

[0053] In existing technologies, when performing DDL operations on a table structure, the exclusive lock added to the operation table blocks all operations on the operation table. This means that the operation table is in a suspended state during the DDL operation. Therefore, performing DDL operations on a frequently accessed table can cause widespread business downtime, severely reducing business system availability and significantly hindering database operations and maintenance.

[0054] In order to solve the above technical problems, the invention of this application is to effectively improve the data processing efficiency of the relational database.

[0055] The following specific embodiments describe in detail the technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0056] Figure 1 This is a flow chart of a data processing method based on a relational database provided in this embodiment. Figure 1 As shown, the method includes:

[0057] Step S101: Obtain an online DDL statement execution request for an original table in a relational database.

[0058] In this embodiment, the relational database includes databases represented by PostgreSQL, MySQL, OpenGauss, and Oracle.

[0059] Step S102: Create a new table with the same structure as the original table according to the execution request, and add an exclusive lock to the original table.

[0060] In this embodiment, the database executes the DDL statement request, creates a new empty table with the same table structure as the original table, performs the DDL operation on the new empty table, and then performs the table data rewrite operation specified in the online DDL statement. At the beginning of the table data rewrite, an exclusive lock is added to the original table.

[0061] The exclusive lock held by the original table at this time may be an access exclusive lock.

[0062] Step S103: When monitoring and determining that the table data rewrite operation in the online DDL statement is executed, the start log sequence number of the WAL log tracking thread is recorded, the WAL log tracking thread is started, and the exclusive lock is downgraded.

[0063] In this embodiment, when the database detects and determines that an online DDL statement is executing a table data rewrite operation, it records the start log sequence number of the WAL log tracking thread and simultaneously starts the WAL log tracking thread. It then downgrades the exclusive lock by adding a shared update exclusive lock to the original table and simultaneously releasing the access exclusive lock.

[0064] In addition, for example, before the exclusive lock is downgraded, the variable start log sequence number is recorded, and then the WAL log tracking thread is started. This thread is responsible for tracking the WAL log, analyzing the transaction log, generating the operation log on the original table, and recording the operation log on the original table in the incremental log file.

[0065] Step S104: Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TID corresponding to the migrated record in the original table and the TID corresponding to the same record in the new table into the temporary log table until the migration is completed.

[0066] In this embodiment, after the exclusive lock is downgraded, the database generates a temporary log table. Simultaneously, it begins scanning the original table. For each successfully retrieved record, a corresponding new record is generated and inserted into the new table. Each time a new record is successfully inserted, a mapping between the original table record's TID and the new table record's TID is inserted into the temporary log table. The next record is then scanned, and the above process repeats until all records in the original table have been migrated to the new table.

[0067] The table structure of the temporary log table includes the original table block number, the original table row offset, the new table block number, the new table row offset, and the primary key.

[0068] The block number and row offset are combined to form the record physical address (TID), which can be used to quickly locate the record.

[0069] The temporary log table records the mapping relationship between the original table record TID and the new table record TID. The original table record TID can quickly locate the new table record TID through the mapping recorded in the temporary log table.

[0070] In addition, when searching for the TID of a new table record using the TID of the original table record, you need to create a primary key index on the block number and row offset columns of the original table. This is because the primary key is unique and can ensure that only the required primary key values ​​are included in the search results. Using the primary key can improve query efficiency and thus query performance.

[0071] Optionally, the temporary log table is a temporary table that only records the mapping from the original table record TID to the new table record TID in the online DDL data rewriting operation. After the table data rewriting operation is completed, the database will automatically delete the temporary log table.

[0072] Step S105: Restore the exclusive lock level, record the end log sequence number, and close the WAL log tracking thread.

[0073] In this embodiment, after all records in the original table are migrated to the new table, the original table scan is completed and a new index is created for the new table. After the index is created, the exclusive lock level is restored and a lock upgrade is performed, that is, an access exclusive lock is re-established on the original table. The end log sequence number is then recorded, and the WAL log tracking thread is notified and waits for completion.

[0074] For example, after the exclusive lock is upgraded, the variable end log sequence number is recorded, and the WAL log tracking thread tracks to the position of the end log sequence number and ends tracking.

[0075] In this embodiment, optionally, downgrading the exclusive lock includes:

[0076] Downgrade the exclusive lock from an access exclusive lock to a shared update exclusive lock. This adds a shared update exclusive lock to the original table and releases the access exclusive lock.

[0077] Correspondingly, the levels of exclusive lock recovery can also include:

[0078] Recover from a shared update exclusive lock to an access exclusive lock.

[0079] Among them, you only need to re-add the access exclusive lock. The shared update exclusive lock will be released together with the access exclusive lock after the DDL statement is executed.

[0080] Step S106: Analyze the incremental log files obtained by the WAL log tracking thread to determine whether there are incremental DML operations in the original table and whether it is in a committed state or a rollback state. If there are incremental DML operations in the original table and they are in a committed state, merge the incremental DML operations into the new table, and complete the table data rewriting operation based on the original table and the merged new table.

[0081] In this embodiment, the WAL log tracking thread tracks and collects all incremental operation information on the original table from the start log sequence number to the end log sequence number, and aggregates this incremental operation information to generate an incremental log file. By analyzing the incremental log file, it is possible to determine whether there is an incremental DML operation in the original table, and to determine the operation type of the incremental DML operation, as well as whether the incremental DML operation is in a committed state or a rollback state after committing. When it is determined that there is an incremental DML operation in the original table, the operation type of the incremental DML operation is determined, and the incremental DML operation is in a committed state, then the incremental DML operation is merged into the new table. The above operation is repeated until all incremental DML operations performed on the original table between the start log sequence number and the end log sequence number are merged into the new table, completing the merging of the incremental DML operations and completing the table data rewrite operation.

[0082] Optionally, the contents of the incremental log file include: block number, row offset, operation type, and transaction ID. Thus, the original table record TID can be obtained based on the combination of the block number and row offset. Furthermore, the operation type of the incremental DML operation can also be determined based on the operation type. The transaction ID can be used to determine whether the incremental DML operation is in a committed or rolled back state. In this embodiment, when performing an online DDL statement operation on the original database table, a new table with the same table structure as the original table is created, and the DDL statement operation is performed on the new table. Then, the table data is rewritten. First, an exclusive lock is added to the original table, the starting log sequence number of the WAL log tracking thread is recorded, the WAL log tracking thread is started, and the exclusive lock is downgraded. A temporary log table is then generated, and the original table is scanned. New records are generated from the acquired original table records and inserted into the new table. The mapping between the original table record TID and the new table record TID is simultaneously inserted into the temporary log table. This process continues until all records in the original table are migrated. The exclusive lock level is restored, the ending log sequence number is recorded, and the WAL log tracking thread is shut down. The incremental log files obtained by the WAL log tracking thread tracking are analyzed to determine whether there are new DML operations in the original table, and the type of the new DML operation is analyzed to determine whether the new DML operation is in the committed state or the rollback state. If the new DML operation is in the committed state, the new DML operation is merged into the new table, and the merging of the incremental DML operation is completed. The original table and the new table are merged and processed to complete the rewriting of the data operation, and the DDL statement operation is completed. Compared with the existing technology, the exclusive lock added in the DDL statement operation will block all operations related to the locked object, thereby seriously affecting the normal operation of the business. In this application, the exclusive lock is downgraded in the DDL statement operation, allowing DML operations to be performed on the original table. At the same time as rewriting the table data, the WAL log tracking thread is started to record and obtain the incremental log file, and based on the incremental log file, the consistency of the original table and the new table data is achieved. Therefore, this application not only ensures the consistency of the migration, but also ensures that the normal operation of the business can be effectively maintained during the migration process, thereby effectively improving the performance and efficiency of database operation.

[0083] Figure 2 This is a flow chart of a second embodiment of a data processing method based on a relational database provided in this embodiment, as shown in FIG. Figure 2 As shown, the method includes:

[0084] Step S201: Obtain an execution request for an online DDL statement for an original table in a relational database.

[0085] Step S202: Create a new table with the same structure as the original table according to the execution request, and add an exclusive lock to the original table.

[0086] Step S203: When monitoring and determining that the table data rewrite operation in the online DDL statement is executed, the start log sequence number of the WAL log tracking thread is recorded, the WAL log tracking thread is started, and the exclusive lock is downgraded.

[0087] Step S204: Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TID corresponding to the migrated record in the original table and the TID corresponding to the same record in the new table into the temporary log table until the migration is completed.

[0088] Step S205: Restore the exclusive lock level, record the end log sequence number, and close the WAL log tracking thread.

[0089] Step S206: Analyze the incremental log file to determine whether there is an incremental delete operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental delete operation in the original table and it is in a committed state, obtain the TID of the incremental delete operation in the original table from the incremental log file, query the temporary table, obtain the TID in the new table that has a mapping relationship with the TID of the incremental delete operation, delete the record of the TID that has a mapping relationship with the TID of the incremental delete operation, and complete the table data rewrite operation based on the original table and the new table after the merge processing.

[0090] In this embodiment, the incremental log file is read and analyzed. When the incremental DML operation record is successfully obtained, it indicates that an incremental DML operation exists in the original table between the start log sequence number and the end log sequence number. The incremental DML operation record is analyzed. When the incremental DML operation is determined to be a delete operation based on the operation type, it is determined based on the transaction ID whether the incremental delete operation is in the committed state or the committed and rolled back state. If the incremental delete operation is in the committed state, it is determined that the incremental delete operation needs to be merged. If the incremental delete operation is in the committed and rolled back state, it is determined that the incremental delete operation does not need to be merged. Then, the next incremental DML operation is obtained and the above analysis and judgment are performed until all incremental DML operations are obtained.

[0091] Optionally, when it is determined that the incremental deletion operation needs to be merged, the TID of the incremental deletion operation is obtained in the incremental log file, and the temporary log table is queried based on the TID. According to the mapping relationship between the original table record TID and the new table record TID recorded in the temporary log table, the TID corresponding to the TID of the incremental deletion operation in the new table is queried, and the new table record corresponding to it is found in the new table according to the new table TID and deleted.

[0092] Optionally, after all incremental DML operations are merged, the underlying storage files of the original table and the merged new table are exchanged to complete the table data rewriting operation.

[0093] In this embodiment, the temporary log table records the mapping relationship between the original table record TID and the new table record TID, so that only the original table operation record TID needs to be known to quickly locate the corresponding record in the new table, thereby improving query efficiency.

[0094] Figure 3 This is a flow chart of a third embodiment of a data processing method based on a relational database provided in this embodiment, as shown in FIG. Figure 3 As shown, the method includes:

[0095] Step S301: Obtain an online DDL statement execution request for an original table in a relational database.

[0096] Step S302: Create a new table with the same structure as the original table according to the execution request, and add an exclusive lock to the original table.

[0097] Step S303: When monitoring and determining that the table data rewrite operation in the online DDL statement is executed, the start log sequence number of the WAL log tracking thread is recorded, the WAL log tracking thread is started, and the exclusive lock is downgraded.

[0098] Step S304: Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TID corresponding to the migrated record in the original table and the TID corresponding to the same record in the new table into the temporary log table until the migration is completed.

[0099] Step S305: Restore the exclusive lock level, record the end log sequence number, and close the WAL log tracking thread.

[0100] Step S306: Analyze the incremental log file to determine whether there is an incremental insert operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental insert operation in the original table and it is in a committed state, obtain the TID of the incremental insert operation in the original table from the incremental log file.

[0101] In this embodiment, the incremental log file is read and analyzed. When the incremental DML operation record is successfully obtained, it indicates that there is an incremental DML operation in the original table between the start log sequence number and the end log sequence number. The incremental DML operation record is analyzed. When the incremental DML operation is determined to be an insert operation based on the operation type, it is determined based on the transaction ID whether the incremental insert operation is in the committed state or the committed and rolled back state. If the incremental insert operation is in the committed state, it is determined that the incremental insert operation needs to be merged. If the incremental insert operation is in the committed and rolled back state, it is determined that the incremental insert operation does not need to be merged. Then, the next incremental DML operation is obtained and the above analysis and judgment are performed until all incremental DML operations are obtained.

[0102] Optionally, when it is determined that the incremental insert operation needs to be merged, the TID of the incremental insert operation is obtained from the incremental log file.

[0103] Step S307: Scan the original table to obtain the record corresponding to the TID of the incremental insert operation in the original table, insert the record corresponding to the TID of the incremental insert operation into the new table, and insert the mapping relationship between the TID of the incremental insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table. Based on the original table and the new table after the merge process, the table data rewriting operation is completed.

[0104] In this embodiment, after obtaining the TID of the incremental insert operation from the incremental log file, the original table is scanned based on the TID to obtain the original table record corresponding to the TID, and a new table record is generated. The new table record is then inserted into the new table. At the same time, the mapping relationship between the TID of the original table record and the TID of the new table record is inserted into the temporary log table.

[0105] Optionally, after all incremental DML operations are merged, the underlying storage files of the original table and the merged new table are exchanged to complete the table data rewriting operation.

[0106] In this embodiment, new table records can be randomly inserted at any position in the new table. Since the mapping relationship between the original table record TID and the new table record TID is recorded in the temporary log table, the query speed is fast, the query results are accurate, and the query efficiency is high.

[0107] Figure 4 This is a flow chart of a third embodiment of a data processing method based on a relational database provided in this embodiment, as shown in FIG. Figure 3 As shown, the method includes:

[0108] Step S401: Obtain an online DDL statement execution request for an original table in a relational database.

[0109] Step S402: Create a new table with the same structure as the original table according to the execution request, and add an exclusive lock to the original table.

[0110] Step S403: When monitoring and determining that the table data rewrite operation in the online DDL statement is executed, the start log sequence number of the WAL log tracking thread is recorded, the WAL log tracking thread is started, and the exclusive lock is downgraded.

[0111] Step S404: Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TID corresponding to the migrated records in the original table and the TID corresponding to the same record in the new table into the temporary log table until the migration is completed.

[0112] Step S405: Restore the exclusive lock level, record the end log sequence number, and close the WAL log tracking thread.

[0113] Step S406: Analyze the incremental log file to determine whether there is an incremental update operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental update operation in the original table and it is in a committed state, obtain the TID of the delete operation in the incremental update operation in the original table, as well as the TID of the insert operation and the TID of the insert operation.

[0114] In this embodiment, the incremental log file is read and analyzed. When the incremental DML operation record is successfully obtained, it indicates that an incremental DML operation exists in the original table between the start log sequence number and the end log sequence number. The incremental DML operation record is analyzed. When the incremental DML operation is determined to be an update operation based on the operation type, it is determined based on the transaction ID whether the incremental update operation is in the committed state or the committed and rolled back state. If the incremental update operation is in the committed state, it is determined that the incremental update operation needs to be merged. If the incremental update operation is in the committed and rolled back state, it is determined that the incremental update operation does not need to be merged. Then, the next incremental DML operation is obtained and the above analysis and judgment are performed until all incremental DML operations are obtained.

[0115] Optionally, when it is determined that the incremental update operation needs to be merged, the TID of the delete operation and the TID of the insert operation in the incremental update operation are obtained from the incremental log file. Based on the TID of the insert operation, the original table is scanned to obtain the original table record corresponding to the TID of the insert operation.

[0116] Among them, in DML operations, an update operation is equivalent to a delete operation + an insert operation. Therefore, if an incremental DML operation is determined to be an update operation, it is converted to a delete record + an insert record.

[0117] Step S407: query the temporary table to obtain the TID in the new table that has a mapping relationship with the TID for the incremental delete operation, and delete the record of the TID that has a mapping relationship with the TID for the incremental delete operation.

[0118] In this embodiment, the TID of the incremental deletion operation is obtained from the incremental log file, and the temporary log table is queried based on the TID. Based on the mapping relationship between the original table record TID and the new table record TID recorded in the temporary log table, the TID corresponding to the TID of the incremental deletion operation in the new table is queried. In the new table, the corresponding new table record is found based on the new table TID and deleted.

[0119] Step S408: Scan the original table to obtain the record corresponding to the TID of the insert operation in the original table, insert the record corresponding to the TID of the insert operation into the new table, and insert the mapping relationship between the TID of the insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table. Then, based on the original table and the merged new table, complete the table data rewrite operation.

[0120] In this embodiment, after obtaining the TID of the incremental insert operation from the incremental log file, the original table is scanned based on the TID to obtain the original table record corresponding to the TID, and a new table record is generated. The new table record is then inserted into the new table. At the same time, the mapping relationship between the TID of the original table record and the TID of the new table record is inserted into the temporary log table.

[0121] Optionally, after all incremental DML operations are merged, the underlying storage files of the original table and the merged new table are exchanged to complete the table data rewriting operation.

[0122] In this embodiment, a temporary log table records the mapping relationship between the TID of the original table record and the TID of the new table record. This allows rapid location of the corresponding record in the new table, simply by knowing the TID of the original table operation record, thus improving query efficiency. Furthermore, new table records can be randomly inserted at any position in the new table. Because the temporary log table records the mapping relationship between the TID of the original table record and the TID of the new table record, queries are fast, accurate, and efficient.

[0123] Furthermore, based on the above embodiments, the method may further include:

[0124] If there is an incremental DML operation in the original table and it is in the rollback state, the table data rewriting operation is completed based on the original table and the new table.

[0125] Furthermore, based on the above embodiments, the method may further include:

[0126] A checkpoint operation is performed on the database based on the minimum start log sequence number of the pre-incremented global variable DDL operation, and the WAL log file is cleaned up when the start log sequence number in the WAL log file is less than the minimum start log sequence number.

[0127] When the database performs a checkpoint, the cleanup function determines the end point of the WAL log file cleanup based on the minimum start log sequence number of the global variable DDL operation. In other words, if the start log sequence number in the WAL log file is less than the minimum start log sequence number, the WAL log file is cleaned up.

[0128] The WAL log file includes an incremental log file.

[0129] The following will be combined Figure 5 A data processing device based on a relational database provided in an embodiment is introduced in detail. Figure 5 This is a structural diagram of an embodiment of a data processing device based on a relational database provided by the present application; Figure 5 As shown, the device includes: an acquisition module 51 and a processing module 52.

[0130] The acquisition module 51 is used to acquire an execution request for an online DDL statement for an original table in a relational database.

[0131] Processing module 52 is configured to create a new table with the same structure as the original table based on the execution request and to apply an exclusive lock to the original table. It is also configured to record the starting log sequence number of the WAL log tracking thread, start the WAL log tracking thread, and downgrade the exclusive lock when monitoring and determining the execution of a table data rewrite operation in an online DDL statement. It is also configured to generate a temporary log table and begin scanning the original table to sequentially migrate records from the original table to the new table. The mapping relationship between the TIDs corresponding to the migrated records in the original table and the TIDs corresponding to the same records in the new table is inserted into the temporary log table until the migration is complete. It is also configured to restore the exclusive lock level, record the ending log sequence number of the original table, and close the WAL log tracking thread. It is also configured to analyze the incremental log files obtained by the WAL log tracking thread to determine whether there are incremental DML operations in the original table and whether they are in the committed or rollback state. If there are incremental DML operations in the original table and they are in the committed state, the incremental DML operations are merged into the new table, and the table data rewrite operation is completed based on the original table and the merged new table.

[0132] Figure 6 A schematic diagram of the structure of a server device embodiment provided in an embodiment of the present application; Figure 6As shown, the server includes: at least one processor 61 and a memory 62. The processor 61 and the memory 62 may be connected via a bus 63.

[0133] The specific implementation process of the processor 61 can be found in the above-mentioned method embodiment. Its implementation principle and technical effects are similar and will not be repeated here in this embodiment.

[0134] In the above Figure 6 In the illustrated embodiment, it should be understood that the processor may be a central processing unit (CPU), other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. The general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in the present invention may be directly executed by a hardware processor or by a combination of hardware and software modules in the processor.

[0135] The memory may include a high-speed memory (Random Access Memory, referred to as RAM), and may also include a non-volatile memory (NVM), such as at least one disk memory.

[0136] A bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus. Buses can be categorized as address buses, data buses, and control buses. For ease of illustration, the buses shown in the drawings of this application are not limited to just one bus or just one type of bus.

[0137] The present application also provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the above-mentioned data processing based on a relational database.

[0138] The computer-readable storage medium may be implemented by any type of volatile or non-volatile memory device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk, or optical disk. The computer-readable storage medium may be any available medium that can be accessed by a general-purpose or special-purpose computer.

[0139] An exemplary readable storage medium is coupled to a processor so that the processor can read information from the readable storage medium and write information to the readable storage medium. Of course, the readable storage medium can also be an integral part of the processor. The processor and the readable storage medium can be located in an application specific integrated circuit (ASIC). Of course, the processor and the readable storage medium can also exist in the device as discrete components.

[0140] The division of units is merely a logical functional division; actual implementations may employ alternative divisions, such as combining or integrating multiple units or components into another system, or omitting or disabling certain features. Furthermore, any direct coupling or communication connection shown or discussed may be an indirect coupling or communication connection between devices or units, either through an interface, electrical, mechanical, or other means.

[0141] Units described as separate components may or may not be physically separate, and components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of these units may be selected to achieve the purpose of this embodiment according to actual needs.

[0142] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0143] If the function is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the existing technology, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes a number of instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the various embodiments of the method of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), magnetic disk or optical disk, and other media that can store program code.

[0144] Finally, it should be noted that those skilled in the art will readily identify other embodiments of the present invention after considering the specification and practicing the invention disclosed herein. The present invention is intended to cover any variations, uses, or adaptations of the present invention that follow the general principles of the present invention and include common knowledge or customary techniques in the art not disclosed herein. The present invention is not limited to the precise structure described above and illustrated in the accompanying drawings, and various modifications and variations may be made without departing from the scope thereof. The scope of the present invention is limited solely by the appended claims.

[0145] So far, the technical solution of the present application has been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it is easy for those skilled in the art to understand that the scope of protection of the present application is obviously not limited to these specific embodiments. The above embodiments are only used to illustrate the technical solution of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, ordinary technicians in this field should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some or all of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solution to deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A data processing method based on a relational database, characterized in that: include: Obtaining an execution request for an online DDL statement for an original table in the relational database; According to the execution request, a new table with the same structure as the original table is created, and an exclusive lock is added to the original table; When monitoring and determining that a table data rewrite operation in an online DDL statement is being executed, recording a start log sequence number of a WAL log tracking thread, starting the WAL log tracking thread, and downgrading the exclusive lock; Generate a temporary log table and start scanning the original table to migrate the records in the original table to the new table in sequence, and insert the mapping relationship between the TIDs corresponding to the migrated records in the original table and the TIDs corresponding to the same records in the new table into the temporary log table until the migration is completed; Restore the exclusive lock level, record the end log sequence number, and close the WAL log tracking thread; The incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, and the table data rewriting operation is completed based on the original table and the merged new table.

2. The method according to claim 1, characterized in that The incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including: The incremental log file is analyzed to determine whether there is an incremental delete operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental delete operation in the original table and it is in a committed state, the TID of the incremental delete operation in the original table is obtained from the incremental log file, and a temporary table is queried to obtain a TID in the new table that has a mapping relationship with the TID of the incremental delete operation, and the record of the TID that has a mapping relationship with the TID of the incremental delete operation is deleted.

3. The method according to claim 1, characterized in that The incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including: Analyze the incremental log file to determine whether there is an incremental insert operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental insert operation in the original table and it is in a committed state, obtain the TID of the incremental insert operation in the original table from the incremental log file; Scan the original table to obtain the record corresponding to the TID for the incremental insert operation in the original table, insert the record corresponding to the TID for the incremental insert operation into the new table, and insert the mapping relationship between the TID for the incremental insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table.

4. The method according to claim 1, wherein The incremental log file obtained by the WAL log tracking thread is analyzed to determine whether there is an incremental DML operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental DML operation in the original table and it is in a committed state, the incremental DML operation is merged into the new table, including: Analyze the incremental log file to determine whether there is an incremental update operation in the original table and whether it is in a committed state or a rollback state. If there is an incremental update operation in the original table and it is in a committed state, obtain the TID of the delete operation in the incremental update operation in the original table, the TID of the insert operation, and the record of the TID of the insert operation; Query the temporary log table to obtain a TID in the new table that has a mapping relationship with the TID for performing the incremental deletion operation, and delete the record of the TID that has a mapping relationship with the TID for performing the incremental deletion operation; Scan the original table to obtain the record corresponding to the TID of the insert operation in the original table, insert the record corresponding to the TID of the insert operation in the new table, and insert the mapping relationship between the TID of the insert operation in the original table and the TID corresponding to the same record in the new table into the temporary log table.

5. The method according to any one of claims 1 to 4, characterized in that: The downgrading process of the exclusive lock includes: Downgrading the exclusive lock from an access exclusive lock to a shared update exclusive lock; Then, restoring the level of the exclusive lock includes: Restore from the shared update exclusive lock to the access exclusive lock.

6. The method according to any one of claims 1 to 4, characterized in that: Also includes: If there is an incremental DML operation in the original table and it is in a rollback state, the table data rewriting operation is completed based on the original table and the new table.

7. The method according to claim 6, characterized in that Also includes: Perform a checkpoint operation on the database according to the pre-incremented minimum start log sequence number of the global variable DDL operation, and determine to clear the WAL log file when the start log sequence number in the WAL log file is less than the minimum start log sequence number; The WAL log file includes an incremental log file.

8. A data processing device based on a relational database, characterized in that: include: An acquisition module is used to obtain an execution request for an online DDL statement for an original table in the relational database; a processing module, configured to create a new table having the same structure as the original table according to the execution request, and add an exclusive lock to the original table; The processing module is further configured to record a start log sequence number of a WAL log tracking thread when monitoring and determining the execution of a table data rewrite operation in an online DDL statement, start the WAL log tracking thread, and downgrade the exclusive lock; The processing module is further configured to generate a temporary log table and start scanning the original table to sequentially migrate records in the original table to the new table, and insert a mapping relationship between TIDs corresponding to migrated records in the original table and TIDs corresponding to identical records in the new table into the temporary log table until migration is completed; The processing module is further configured to restore the level of the exclusive lock, record the end log sequence number of the original table, and close the WAL log tracking thread; The processing module is further configured to analyze the incremental log files obtained by the WAL log tracking thread to determine whether there are incremental DML operations in the original table and whether the operation is in a committed state or a rollback state. If there are incremental DML operations in the original table and the operation is in a committed state, the incremental DML operations are merged into the new table, and the table data rewriting operation is completed based on the original table and the merged new table.

9. A server, characterized in that: include: at least one processor and memory; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

Images