CN109558452B

CN109558452B - Synchronization method for query table building operation

Info

Publication number: CN109558452B
Application number: CN201811377399.XA
Authority: CN
Inventors: 孙峰; 付铨; 余院兰
Original assignee: Wuhan Dameng Database Co Ltd
Current assignee: Wuhan Dream Database Co ltd
Priority date: 2018-11-19
Filing date: 2018-11-19
Publication date: 2020-12-08
Anticipated expiration: 2038-11-19
Also published as: CN109558452A

Abstract

The invention discloses a synchronization method for inquiring table building operation, which comprises the following steps: step S1, the query and table building operation of the source database is captured, and the query and table building SQL sentence of the query and table building operation is extracted; step S2, judging whether the query table building SQL sentence meets the condition of SQL sentence synchronization, if yes, turning to step S3; step S3, sending the query table building SQL statement to a target database for synchronization, where the source database continues to track transaction log streams of the query table building SQL statement, directly discards an INSERT operation log related to the query table building operation, and notifies the target database to end the synchronization process of the query table building SQL statement when capturing a commit log in the transaction log streams. The synchronization method for the query and table building operation can effectively reduce the time consumption for the synchronization of the query and table building operation.

Description

Synchronization method for query table building operation

Technical Field

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

Background

In the database data replication technology based on software, the data real-time synchronization technology based on analysis log files has the characteristics of small influence on a source-end database, support of heterogeneous system replication, read-write of a replication system standby machine and the like, and is widely applied in a plurality of scenes such as emergency disaster recovery, a multi-service center, real-time heterogeneous resource integration, data migration and rolling upgrade, data warehouse architecture improvement and the like.

The method comprises the steps that a data synchronization process reads online or archived log files of a database in real time to acquire INSERT, DELETE and UPDATE transaction operations of the database in a source database, converts the transaction operations into messages with internal specific formats and directly transmits the messages to a data synchronization process of a target database through a TCP/IP network; after receiving the message sent by the source end, the data synchronization process of the target end database analyzes the message and restores the transaction operation of the source end into an SQL operation statement, and the SQL operation statement is executed on the target end database by using a local database interface, so that the data synchronization of the source end and the target database is realized.

For a DML operation on a database, the database directly records transaction operation information, such as an operation object, modified data, and the like, in a log file, but for a DDL operation, not all databases directly record DDL transaction information in the log file. Based on the method, the third-party tool can read incremental log data in the physical log file in real time and analyze the incremental log data to realize the real-time synchronization of the operations of adding, deleting and changing on the database. For DDL synchronization, a common technical solution at present is to use an intermediate table and use an indirect method to analyze information of DDL operation from a log file. The query table-building operation is a special DDL operation, and creates a target data table by using a query table-building method, and at the same time, initializes a new table by using the queried data. When the amount of data in the table to be queried is very large, the table query and creation operation is very time-consuming, and the log file information flow contains a large amount of data for creating the table to initialize the INSERT operation log. According to a conventional data synchronization mode based on analysis log analysis, the DDL operation can be started to be synchronized only when the DDL transaction is submitted, including extracting new table log information in a log file and an initialized INSERT operation log of the table data, and then sending the table log information and the initialized INSERT operation log to a target end for synchronization.

Disclosure of Invention

The invention aims to overcome the technical defects and provide a synchronization method for querying table building operation, which solves the technical problems of long time consumption and large time delay of the synchronization of the table building operation query in the prior art.

In order to achieve the above technical object, a technical solution of the present invention provides a synchronization method for querying a table building operation, including the following steps:

step S1, the query and table building operation of the source database is captured, and the query and table building SQL sentence of the query and table building operation is extracted;

step S2, judging whether the query table building SQL sentence meets the condition of SQL sentence synchronization, if yes, turning to step S3;

step S3, sending the query table building SQL statement to a target database for synchronization, where the source database continues to track transaction log streams of the query table building SQL statement, directly discards an INSERT operation log related to the query table building operation, and notifies the target database to end the synchronization process of the query table building SQL statement when capturing a commit log in the transaction log streams.

Compared with the prior art, the invention has the beneficial effects that: according to the method, the synchronization process is finished by submitting the logs in the object log stream and identifying the object submission, the INSERT logs of a large number of operation tables generated by caching and analyzing query table-building SQL sentences are avoided, the transmission operations of the logs and the warehousing operations of the operations in the target database are avoided, the operations can slow down the synchronization speed seriously, and the optimized scheme can reduce the synchronization delay to the maximum extent.

Drawings

FIG. 1 is a flow chart of a method for synchronizing a query tabulation operation provided by the present invention.

Detailed Description

In order to make the objects, technical solutions and advantages of the present invention more apparent, the present invention is described in further detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.

Example 1:

as shown in fig. 1, embodiment 1 of the present invention provides a synchronization method for a query table creation operation, which includes the following steps:

The synchronization method for the query and table building operation provided by the invention comprises the steps of firstly capturing the query and table building operation, extracting a query and table building SQL statement, further judging whether the query and table building SQL statement uses the synchronization method provided by the invention, namely the synchronization condition of the SQL statement, if the query and table building SQL statement uses the synchronization method provided by the invention, sending the query and table building SQL to a target end for synchronization, continuing to track the transaction log stream, directly discarding INSERT operation logs belonging to the DDL operation table until a commit log is captured, which shows that the source end query and table building operation is normally committed, ending the transaction synchronization, and marking the transaction synchronization completion by a data synchronization process.

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

Preferably, the step S1 specifically includes:

step S11, creating 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 recording auxiliary table through log analysis, and extracting an SQL statement;

and S14, screening the query table building SQL sentences from the SQL sentences.

And capturing SQL sentences of DDL operation by using the DDL record auxiliary table as an intermediate table, and screening out query table building SQL sentences from the SQL sentences.

Specifically, for convenience of description, the query tabulation 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, T2 is a lookup table.

A DDL record auxiliary table is created in a source database, and the specific table structure is as follows:

name of field	Type (B)	Description of the invention
			OBJ_SCHNAME	VARchar(128)	Object schema name
OBJ_NAME	VARchar(128)	Object name
			OP_SQL	CLOB	SQL statement of DDL operation
OP_TIME	DATETIME(6)	Time of operation

Creating a DDL operation trigger in a source database, and when CREATE, ALTER, DROP and TRUNCATE operations of the source database are captured, performing the following operations:

(1) judging whether the object of the operation is a DDL recording auxiliary table, if so, directly returning; if not, proceed to (2).

(2) And if the operation object type of the database is a table, inserting the object name of the operation, the mode name of the object, the SQL statement of the operation and the operation time into the DDL record auxiliary table for identifying the object in the DDL operation when synchronization is realized by analyzing the log file subsequently.

Preferably, the step S14 specifically includes:

and carrying out syntax analysis on the SQL sentences, and screening out the query table building SQL sentences.

Whether the SQL sentence is the query table-building SQL sentence is judged according to the grammar of the SQL sentence, the judging method is realized by the prior art, and is simple and is not described herein again.

Preferably, the step S2 specifically includes:

step S21, extracting a new table name, a query item and a filtering condition of the query table building SQL statement;

step S22, according to the synchronization rule, judging whether the new table and the query table of the query table building SQL sentence need to be synchronized, if yes, turning to step S23, otherwise, not satisfying the condition of synchronization of the SQL sentence;

step S23, judging whether the query table building SQL sentence is a single table query, if so, turning to step S24, otherwise, not satisfying the condition of synchronization of the SQL sentence;

step S24, judging whether the query item in the query table building SQL statement contains other items except the column name, if yes, not meeting the condition of synchronization of the SQL statement, otherwise, turning to step S25;

step S25, determining whether the query table-building SQL statement contains a filtering condition, if yes, the condition for synchronization of the SQL statement is not satisfied, otherwise, the condition for synchronization of the SQL statement is satisfied.

Judging whether a new table and a query table in a query table building SQL sentence need to be synchronized or not according to a synchronization rule, and if the new table does not need to be synchronized, ignoring the synchronization operation; if the new table needs to be synchronized and the query table does not need to be synchronized, the condition for synchronization by the SQL statement is not satisfied, because the target-side database may not have a query table definition. Whether the query in the query table building SQL statement is a single-table query is further judged, if not, the condition for synchronization of the SQL statement is not satisfied, because the multi-table query relates to the processing of multiple tables, the execution states of the multiple tables need to be judged when the target end database is synchronized, and mutual exclusion protection and other operations are performed, so that the processing complexity of the target end database synchronization system can be increased. And further judging whether the query items in the query tabulation SQL statement contain other items except column names, such as functions, sequences and the like, if so, not meeting the condition of synchronization of the SQL statement, because function calculation or sequence value definition of the source end and the target end may not be consistent, thereby obtaining inconsistent initialization data. Judging whether the query in the query table building SQL statement contains a filtering condition, if so, not meeting the condition for synchronizing the SQL statement, because the filtering condition may involve various complex conditions, such as containing a self-defined function, and the like, and the actual application environments of the source end and the target end may be inconsistent, so that inconsistent initialization data occurs.

Preferably, the step S2 further includes:

and if the query table building SQL sentence is not satisfied with the condition for synchronizing the SQL sentence, synchronizing the query table building SQL sentence by analyzing the log file.

If the query table-building SQL statement does not satisfy the condition for synchronization of the SQL statement, the synchronization of the query table-building SQL statement is implemented by analyzing the log file in the prior art, which is described in the background.

Preferably, the step S3 specifically includes:

step S31, detecting whether the things executed on the query table before the query table building SQL sentence are submitted or the rollback is completed, if the submission is completed, directly switching to step S32, if the rollback is completed, directly switching to step S33, otherwise, waiting for the submission or the rollback is completed;

step S32, finishing the tracking of the corresponding DDL affairs and informing the target end database to finish the DDL synchronization;

step S33, tracking the corresponding DDL affairs, constructing a deleting operation of the new build table corresponding to the query build table SQL operation, and sending the deleting operation to the target end database so as to empty the synchronous new build table created by the query build table SQL sentence and complete DDL synchronization.

And after the data synchronization process receives the query table building SQL statement in the target end database, executing the query table building SQL statement through a data access interface of the target end database according to the transaction synchronization rule, and realizing the synchronization of the query table building DDL operation. Before executing a query table building SQL statement, a target end database needs to wait for all transactions on a query table before the query table building operation to be submitted and completed, so as to ensure that data of the query table queried in the query table building operation is complete; the newly created table created this time needs to be protected mutually exclusive, so as to prevent subsequent transactions from operating the table, because if the data volume of the query table-creating SQL statement is large, the execution time of the query table-creating SQL statement may be long, and it is considered that after the source database completes the query table-creating 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 may not complete the query table-creating SQL statement, so that subsequent transaction operations for the newly created table need to be waited for, and when the target end executes the query table-creating SQL statement, the newly created table needs to be protected mutually exclusive until the query table-creating SQL operation is complete.

Preferably, the step S3 further includes: and the source end database stops the extraction operation of the query and table building SQL statement and the transmission operation of the related log of the query and table building SQL statement while tracking the transaction log stream of the query and table building SQL statement.

According to the method and the device, the completion of the synchronous flow of the query and table building SQL statement is realized by capturing the submitted log in the transaction log stream, so that the INSERT log of the operation table generated by the query and table building SQL statement operation does not need to be cached and analyzed, other related logs generated by the query and table building SQL statement do not need to be transmitted, the time consumed for the synchronization of the query and table building operation is reduced, and the synchronization delay of the query and table building operation is reduced.

Preferably, the step S3 further includes:

and the source end database continuously tracks the object log stream of the query and table building SQL statement, and informs the target end database to delete the target table of the query and table building SQL statement when capturing a rollback log in the object log stream.

In a source database, after a data synchronization process sends a query table building SQL statement to a target end, a log capture analysis module continues to capture a log information stream of data inserted in the same query table building operation according to a transaction ID value until a COMMIT log of the query table building transaction, namely a COMMIT log, is tracked, and the execution of the query table building transaction is completed; or tracking a ROLLBACK log, i.e. a ROLLBACK log, of the query table creation transaction, which indicates that the query table creation transaction fails to be executed, and the source-side database executes a ROLLBACK operation. When the query TABLE-building transaction writes a rollback log in a log file, namely the query TABLE-building transaction fails to execute, a data synchronization process of the source-end database constructs a DROP TABLE operation according to the operation object name and sends the DROP TABLE operation to the target-end database so as to inform the target-end database to delete the target TABLE of the query TABLE-building SQL statement, so that the source end and the target end are kept synchronous and consistent.

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, the computer program implements the synchronization method for the query table creation operation provided in any of the above embodiments.

The computer storage medium provided by the invention is based on the synchronization method for querying and table building operations, so that the technical effect of the synchronization method for querying and table building operations is achieved by the computer storage medium, and the details are not repeated herein.

The above-described embodiments of the present invention should not be construed as limiting the scope of the present invention. Any other corresponding changes and modifications made according to the technical idea of the present invention should be included in the protection scope of the claims of the present invention.

Claims

1. A method for synchronizing a lookup table creation operation, comprising the steps of:

step S3, the query table building SQL sentence is sent to a target end database for synchronization, a source end database continues to track an object log stream of the query table building SQL sentence, an INSERT operation log related to the query table building operation is directly discarded, and the target end database is informed to finish the synchronization process of the query table building SQL sentence when a submission log in the object log stream is captured;

the step S2 specifically includes:

2. The method for synchronizing query table creation operations according to claim 1, wherein the step S1 specifically includes:

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;

3. The method for synchronizing query table creation operations according to claim 2, wherein the step S14 specifically comprises:

4. The method for synchronizing query table creation operations according to claim 1, wherein said step S2 further comprises:

5. The method for synchronizing query table creation operations according to claim 1, wherein the step S3 specifically includes:

6. The method for synchronizing query table creation operations according to claim 1, wherein said step S3 further comprises: and the source end database stops the extraction operation of the query and table building SQL statement and the transmission operation of the related log of the query and table building SQL statement while tracking the transaction log stream of the query and table building SQL statement.

7. The method for synchronizing query table creation operations according to claim 1, wherein said step S3 further comprises:

8. A computer storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements a method of synchronizing a look-up table creation operation according to any one of claims 1 to 6.