CN109558452B - A Synchronous Method for Querying and Creating Tables - Google Patents

Abstract

The invention discloses a method for synchronizing a query table building operation, comprising the following steps: step S1, capturing the query table building operation of the source database, and extracting the query table building SQL statement of the query table building operation; step S2, judging Whether the query table building SQL statement satisfies the condition for synchronizing with the SQL statement, if so, go to step S3; Step S3, send the query table building SQL statement to the target database for synchronization, and the source database continues to track the Query the transaction log stream of the table-building SQL statement, directly discard the INSERT operation log related to the query table-building operation, and notify the target database to end the query when capturing the commit log in the transaction log stream Synchronization process of creating table SQL statements. The method for synchronizing the query table building operation provided by the present invention can effectively reduce the synchronization time-consuming of the query table building operation.

Description

A Synchronous Method for Querying and Creating Tables

technical field

The invention relates to the technical field of query table building operation synchronization in database synchronization, in particular to a synchronization method for query table building and pretending.

Background technique

In the software-based database data replication technology, the real-time data synchronization technology based on analyzing log files has the characteristics of small impact on the source database, supporting heterogeneous system replication, and readable and writable by the backup system of the replication system. , multi-business center, real-time heterogeneous resource integration, data migration and rolling upgrade, data warehouse architecture improvement and other scenarios are widely used.

The main technical principle of real-time data synchronization based on analysis log files is that in the source database, the data synchronization process reads the online or archived log files of the database in real time to obtain the transaction operations of INSERT, DELETE and UPDATE of the database, and then converts these transaction operations. It is an internal message in a specific format, which is directly transmitted to the data synchronization process of the target database through the TCP/IP network; after the data synchronization process of the target database receives the message sent by the source, it analyzes the message and operates the transaction of the source. Restore to SQL operation statement, use the database local interface to execute on the target database, realize the data synchronization between the source and the target database.

For DML operations on the database, the database directly records transaction operation information such as operation objects and modified data in the log file. However, for DDL operations, not all databases directly record DDL transaction information in the log file. Based on this, third-party tools can realize real-time synchronization of addition, deletion, and modification operations on the database by reading incremental log data in physical log files in real time and parsing them. For DDL synchronization, the current common technical solution is to use an intermediate table to parse out the information of the DDL operation from the log file in an indirect way. The query table creation operation is a special DDL operation. When the target data table is created using the query table creation method, the new table will be initialized with the queried data. When the amount of data in the table to be queried is very large, the query table creation operation will be very time-consuming, and the log file information stream will contain a large amount of table creation data to initialize the INSERT operation log. According to the conventional data synchronization method based on analysis log analysis, the DDL operation can only be synchronized when the DDL transaction is committed, including extracting the log information of the newly created table in the log file and the initial INSERT operation log of the table data, and then sending it to the target. For synchronization, if the data volume of the base table is very large, this will cause a large amount of data to be sent from the source to the target. When the target is synchronized, it will take a long time to complete the submission, and the synchronization delay will be very large.

SUMMARY OF THE INVENTION

The purpose of the present invention is to overcome the above technical deficiencies, provide a synchronization method for query table building operations, and solve the technical problems in the prior art that query table building operations take a long time to synchronize and have a large delay.

In order to achieve the above technical purpose, the technical solution of the present invention provides a synchronization method for querying table building operations, comprising the following steps:

Step S1, capturing the query table building operation of the source database, and extracting the query table building SQL statement of the query table building operation;

Step S2, determine whether the query table building SQL statement satisfies the condition for synchronizing with the SQL statement, and if it satisfies, then go to step S3;

Step S3, the query table building SQL statement is sent to the target database for synchronization, the source database continues to track the transaction log stream of the query table building SQL statement, and the INSERT operation log related to the query table building operation is directly discard, and when the commit log in the transaction log stream is captured, the target database is notified to end the synchronization process of the query and table creation SQL statement.

Compared with the prior art, the beneficial effects of the present invention include: the present invention identifies the transaction submission according to the submission log in the transaction log stream, realizes the end of the synchronization process, and avoids a large number of operations generated by caching and analyzing the query table building SQL statement. The INSERT log of the table avoids the transmission operation of delivering these logs and the warehousing operation of these operations in the target database. These operations will seriously slow down the synchronization speed. The optimized solution can minimize the synchronization delay.

Description of drawings

FIG. 1 is a flowchart of a method for synchronizing a query table building operation provided by the present invention.

Detailed ways

In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention, but not to limit the present invention.

Example 1:

As shown in FIG. 1 , Embodiment 1 of the present invention provides a synchronization method for querying table building operations, including the following steps:

Step S1, capturing the query table building operation of the source database, and extracting the query table building SQL statement of the query table building operation;

Step S2, determine whether the query table building SQL statement satisfies the condition for synchronizing with the SQL statement, and if it satisfies, then go to step S3;

Step S3, the query table building SQL statement is sent to the target database for synchronization, the source database continues to track the transaction log stream of the query table building SQL statement, and the INSERT operation log related to the query table building operation is directly discard, and when the commit log in the transaction log stream is captured, the target database is notified to end the synchronization process of the query and table creation SQL statement.

The method for synchronizing the query table building operation provided by the present invention first captures the query table building operation, extracts the query table building SQL statement, and then judges whether the query table building SQL statement uses the conditions of the synchronization method provided by the present invention, that is, executes the query table building operation using the SQL statement. If the synchronization conditions are met, the query table SQL will be sent to the target for synchronization, and the transaction log stream will continue to be tracked, and the INSERT operation logs belonging to the DDL operation table will be discarded directly until the commit log is captured, indicating the source If the client query table creation operation is normally submitted and completed, the transaction synchronization ends, and the data synchronization process marks the transaction synchronization completion.

The synchronization method for query table building operation provided by the present invention can skip the analysis, transmission and synchronization of a large number of INSERT logs in the log file, and directly identify the synchronization end of transactions by capturing and submitting logs, which greatly shortens the synchronization time of query table building operation.

Preferably, the step S1 specifically includes:

Step S11, create a DDL record auxiliary table and a DDL operation trigger in the source database;

Step S12: Capture the DDL operation of the source database through the DDL operation trigger, and write the DDL operation into the DDL record auxiliary table.

Step S13, capturing the insertion operation of the DDL record auxiliary table through log analysis, and extracting the SQL statement;

Step S14: Filter out the query table building SQL statement from the SQL statement.

The DDL record auxiliary table is used as an intermediate table to capture the SQL statements of DDL operations, and filter out the query and table building SQL statements in the SQL statements.

Specifically, for the convenience of description, the query table building SQL statement template described in the present invention is as follows:

CREATE TABLE T1 AS SELECT C1,C2,...,CN FROM T2

T1 is a new table, and T2 is a query table.

Create a DDL record auxiliary table in the source database. The specific table structure is as follows:

field name type illustrate OBJ_SCHNAME VARchar(128) object schema name OBJ_NAME VARchar(128) object name OP_SQL CLOB SQL statement for DDL operation OP_TIME DATETIME(6) Operation time

Create a DDL operation trigger in the source database. When the CREATE, ALTER, DROP, and TRUNCATE operations of the source database are captured, perform the following operations:

(1) Determine whether the object of the operation is the DDL record auxiliary table. If so, return directly; if not, proceed to (2).

(2) If the operation object type of the database is a table, insert the object name of the operation, the schema name of the object, the SQL statement of the operation, and the operation time into the DDL record auxiliary table, which is used to identify the DDL when the synchronization is realized by analyzing the log file later. object in action.

Preferably, the step S14 is specifically:

Syntax parsing is performed on the SQL statement, and the query table building SQL statement is filtered out.

According to the syntax of the SQL statement, it is judged whether it is an SQL statement for querying and building a table.

Preferably, the step S2 specifically includes:

Step S21, extracting the new table name, query table name, query item and filter condition of the query table building SQL statement;

Step S22, according to the synchronization rule, determine whether the new table and the query table of the query table building SQL statement both need to be synchronized, and if so, go to step S23, otherwise the condition for the synchronization of the SQL statement is not satisfied;

Step S23, judging whether the query table building SQL statement is a single-table query, if so, go to step S24, otherwise it does not meet the conditions for the SQL statement to synchronize;

Step S24, determine whether the query item in the query table building SQL statement contains other items except the column name, if so, the condition for synchronization of the SQL statement is not satisfied, otherwise go to step S25;

Step S25, judging whether the query table-building SQL statement contains a filter condition, if so, it does not satisfy the condition for synchronizing with the SQL statement, otherwise it satisfies the condition for synchronizing with the SQL statement.

According to the synchronization rules, it is judged whether the newly created table and the query table in the query table creation SQL statement need to be synchronized. If the newly created table does not need to be synchronized, this synchronization operation is ignored; The conditions for synchronization of the SQL statement are not satisfied, because the query table definition may not exist in the target database. Further determine whether the query in the query table creation SQL statement is a single-table query. If not, it is not sufficient for the SQL statement to synchronize. Because the multi-table query involves the processing of multiple tables, it is necessary to determine the number of tables when synchronizing the target database. The execution state of the table, and mutual exclusion protection are performed, which will increase the processing complexity of the target database synchronization system. Further judge whether the query item in the query table creation SQL statement contains other items other than the column name, such as including functions, sequences, etc. If it does, it does not meet the conditions for the synchronization of the SQL statement, because the function calculation on the source side and the target side or Sequence value definitions may be inconsistent, resulting in inconsistent initialization data. Determines whether the query in the query table creation SQL statement contains filter conditions. If so, it is not sufficient for the synchronization conditions of the SQL statement, because the filter conditions may involve a variety of complex situations, such as including custom functions. The application environment may be inconsistent, resulting in inconsistent initialization data.

Preferably, the step S2 further includes:

If the query table building SQL statement does not meet the conditions for synchronization of the SQL statement, the synchronization of the query table building SQL statement is realized by analyzing the log file.

If the query table building SQL statement does not meet the conditions for synchronization of the SQL statement, the synchronization of the query table building SQL statement is realized by analyzing the log file in the prior art. For the synchronization method of analyzing the log file in the prior art, please refer to the background. Introduced in technology.

Preferably, the step S3 specifically includes:

Step S31, detect whether the thing executed on the query table before the query table building SQL statement has been submitted or rolled back is completed, if the submission is completed, then directly go to step S32, if the rollback is completed, then directly go to step S33, otherwise wait Commit or rollback is complete;

Step S32, end the tracking of the corresponding DDL transaction, and notify the target database to complete the DDL synchronization;

Step S33, ending the tracking of the corresponding DDL transaction, and constructing the delete operation of the new table corresponding to the query table building SQL operation and sending it to the target database, so as to clear the synchronous new table created by the query table building SQL statement, Complete DDL synchronization.

In the target database, after receiving the query table building SQL statement, the data synchronization process executes the query table building SQL statement through the data access interface of the target database according to the transaction synchronization rules, so as to realize the synchronization of the query table building DDL operation. Before executing the query table creation SQL statement, the target database needs to wait for the completion of all transactions on the query table before the query table creation operation. This is to ensure that the data in the query table queried in the query table creation operation is complete. ; It is also necessary to protect the newly created table this time by mutual exclusion to prevent subsequent transactions from operating the table, because if the query table data volume of the query table creation SQL statement is large, the execution time of the query table creation SQL statement may be longer. After the source database completes the query table creation operation, there will be a transaction that continues to operate the newly created table, and when the transaction information is transmitted to the target end, the target end query table creation SQL statement may not be submitted yet, so the subsequent transaction operations for the newly created table It needs to wait. When the target side executes the query table creation SQL statement, it needs to set the mutual exclusion protection for the newly created table until the query table creation SQL operation is submitted.

Preferably, the step S3 further includes: while tracking the transaction log stream of the query table building SQL statement, the source database stops the extraction operation of the query table building SQL statement and the correlation of the query table building SQL statement Log transfer operations.

Because the present invention realizes the end of the synchronization process of the query table building SQL statement by capturing the commit log in the transaction log stream, there is no need to cache and analyze the INSERT log for the operation table generated by the query table building SQL statement operation. , there is no need to transmit other related logs generated by the query table creation SQL statement, which reduces the synchronization time of the query table creation operation and the synchronization delay of the query table creation operation.

Preferably, the step S3 further includes:

The source-end database continues to track the transaction log stream of the query table-building SQL statement, and notifies the target-end database to delete the target table of the query table-building SQL statement when capturing the rollback log in the transaction log stream.

In the source database, after the data synchronization process sends the query table creation SQL statement to the target end, the log capture and analysis module continues to capture the log information flow of data inserted in the same query table creation operation according to the transaction ID value, until the query creation operation is traced. The commit log of the table transaction, that is, the COMMIT log, indicates that the execution of the query table-building transaction is completed; or the rollback log of the query table-building transaction is tracked, that is, the ROLLBACK log, indicating that the query table-building transaction fails to execute, and the source database executes it. Rollback operation. When the query table creation transaction writes the rollback log in the log file, that is, when the query table creation transaction fails to execute, the data synchronization process of the source database constructs a DROP TABLE operation according to the operation object name and sends it to the target database to notify the target end The database deletes the target table of the query table-building SQL statement, so as to maintain the synchronization and consistency between the source end and the target end.

Example 2:

Embodiment 2 of the present invention provides a computer storage medium on which a computer program is stored, and when the computer program is executed by a processor, implements the synchronization method for query table building operation provided by any of the above embodiments.

The computer storage medium provided by the present invention is based on the above synchronization method for query table building operation. Therefore, the technical effects of the above synchronization method for query table building operation are also provided by the computer storage medium, which will not be repeated here.

The specific embodiments of the present invention described above do not limit the protection scope of the present invention. Any other corresponding changes and modifications made according to the technical concept of the present invention shall be included in the protection scope of the claims of the present invention.

Claims (8)

1. a synchronizing method of query table building operation, is characterized in that, comprises the following steps: Step S1, capturing the query table building operation of the source database, and extracting the query table building SQL statement of the query table building operation; Step S2, determine whether the query table building SQL statement satisfies the condition for synchronizing with the SQL statement, and if it satisfies, then go to step S3; Step S3, the query table building SQL statement is sent to the target database for synchronization, the source database continues to track the transaction log stream of the query table building SQL statement, and the INSERT operation log related to the query table building operation is directly Discard, and notify the target database to end the synchronization process of the query table building SQL statement when the commit log in the transaction log stream is captured; The step S2 specifically includes: Step S21, extracting the new table name, query table name, query item and filter condition of the query table building SQL statement; Step S22, according to the synchronization rule, determine whether the new table and the query table of the query table building SQL statement both need to be synchronized, and if so, go to step S23, otherwise the condition for the synchronization of the SQL statement is not satisfied; Step S23, judging whether the query table building SQL statement is a single-table query, if so, go to step S24, otherwise it does not meet the conditions for the SQL statement to synchronize; Step S24, determine whether the query item in the query table building SQL statement contains other items except the column name, if so, the condition for synchronization of the SQL statement is not satisfied, otherwise go to step S25; Step S25, judging whether the query table-building SQL statement contains a filter condition, if so, it does not satisfy the condition for synchronizing with the SQL statement, otherwise it satisfies the condition for synchronizing with the SQL statement. 2. The synchronization method of query table building operation according to claim 1, wherein the step S1 specifically comprises: Step S11, create a DDL record auxiliary table and a DDL operation trigger in the source database; Step S12, capturing the DDL operation of the source database through the DDL operation trigger, and writing the DDL operation into the DDL record auxiliary table; Step S13, capturing the insertion operation of the DDL record auxiliary table through log analysis, and extracting the SQL statement; Step S14: Filter out the query table building SQL statement from the SQL statement. 3. The synchronization method of query table building operation according to claim 2, wherein the step S14 is specifically: Syntax parsing is performed on the SQL statement, and the query table building SQL statement is filtered out. 4. The synchronization method of query table building operation according to claim 1, wherein the step S2 further comprises: If the query table building SQL statement does not meet the conditions for synchronization of the SQL statement, the synchronization of the query table building SQL statement is realized by analyzing the log file. 5. The synchronization method of query table building operation according to claim 1, wherein the step S3 specifically comprises: Step S31, detect whether the thing executed on the query table before the query table building SQL statement has been submitted or rolled back is completed, if the submission is completed, then directly go to step S32, if the rollback is completed, then directly go to step S33, otherwise wait Commit or rollback is complete; Step S32, end the tracking of the corresponding DDL transaction, and notify the target database to complete the DDL synchronization; Step S33, ending the tracking of the corresponding DDL transaction, and constructing the delete operation of the new table corresponding to the query table building SQL operation and sending it to the target database, so as to clear the synchronous new table created by the query table building SQL statement, Complete DDL synchronization. 6. The method for synchronizing query table building operation according to claim 1, wherein said step S3 further comprises: while the source database continues to track the transaction log stream of the query table building SQL statement, it stops the The extraction operation of the query table-building SQL statement and the transmission operation of the relevant log of the query table-building SQL statement. 7. The synchronization method of query table building operation according to claim 1, wherein the step S3 further comprises: The source-end database continues to track the transaction log stream of the query table-building SQL statement, and notifies the target-end database to delete the target table of the query table-building SQL statement when capturing the rollback log in the transaction log stream. 8 . A computer storage medium having a computer program stored thereon, characterized in that, when the computer program is executed by a processor, the method for synchronizing a query table building operation according to any one of claims 1 to 6 is implemented.

Images