Database data synchronization method and database data query method and device

Abstract

The invention provides a database data synchronization method and device and a database data query method and device, and relates to the technical field of databases. The database data synchronization method comprises the following steps: in response to a received data change log aiming at a database host, identifying a plurality of host data tables with data change and respective processed types of the plurality of host data tables from the data change log; based on the plurality of host data tables, a standby machine data table corresponding to each host data table is identified from a database standby machine, and based on the plurality of processed types, a plurality of task instructions used for data change of data rows of the standby machine data tables are generated, and data stored in the host data tables and the standby machine data tables are the same; concurrently executing a plurality of task instructions by utilizing a plurality of threads configured on the database standby machine under the condition that the plurality of standby machine data tables are different; in response to a completed task instruction, a timestamp field is added in the data line, and the timestamp field represents a change moment of the data line.

Description

Technical Field

[0001] This application relates to the field of database technology, specifically to a database data synchronization method, a database data query method, and an apparatus. Background Technology

[0002] In a primary-secondary database architecture, the primary database server handles read and write requests, while the secondary database server acts as a redundant node, taking over services when the primary server fails to ensure data availability and service continuity. Achieving this high availability goal relies on the quality of data synchronization between the primary and secondary database servers. However, existing data synchronization mechanisms still have shortcomings in both the real-time performance of synchronization processing and the manageability of synchronized data. Summary of the Invention

[0003] In view of the above problems, this application provides a database data synchronization method, a database data query method, and an apparatus.

[0004] According to one aspect of this application, a database data synchronization method is provided, comprising: in response to receiving a data change log for a database host, identifying multiple host data tables with data changes and the processing type of each of the multiple host data tables from the data change log; based on the multiple host data tables, identifying standby data tables corresponding to each of the host data tables from a database standby machine, and generating multiple task instructions for data changes of data rows in the standby data tables based on the multiple processing types, wherein the host data tables and the standby data tables store the same data; in the case that the multiple standby data tables are different, concurrently executing the multiple task instructions using multiple threads configured on the database standby machine; and in response to the completion of the task instructions, adding a timestamp field to the data rows, wherein the timestamp field represents the time of change of the data rows.

[0005] According to one aspect of this application, a database data query method is provided, comprising: in response to receiving a target read request initiated by a client, determining a target data row of a target data table from the target read request, and determining a target time for the read request based on the value of a timestamp field added to the target data row at the current time; and reading target data from the target data row based on the target time and returning it to the client.

[0006] Another aspect of this application provides a database data synchronization apparatus, comprising: an identification module, configured to, in response to receiving a data change log for a database host, identify from the data change log multiple host data tables that have undergone data changes, and the processing type of each of the multiple host data tables; a generation module, configured to, based on the multiple host data tables, identify standby data tables corresponding to each of the host data tables on a database standby machine, and based on the multiple processing types, generate multiple task instructions for data changes of data rows in the standby data tables, wherein the host data tables and the standby data tables store the same data; a concurrency module, configured to, when the multiple standby data tables are different, concurrently execute the multiple task instructions using multiple threads configured on the database standby machine; and an adding module, configured to, in response to the completion of the task instructions, add a timestamp field to the data rows, wherein the timestamp field represents the time of change of the data rows.

[0007] Another aspect of this application provides a database data query apparatus, comprising: a determining module, configured to, in response to receiving a target read request initiated by a client, determine a target data row of a target data table from the target read request, and determine a target time for the read request based on the value of a timestamp field added to the target data row at the current time; and a reading module, configured to read target data from the target data row based on the target time, and return the data to the client.

[0008] In another aspect of this application, an electronic device is provided, comprising: one or more processors; and a memory for storing one or more computer programs, wherein the one or more processors execute the one or more computer programs to implement the steps of the database data synchronization method and the database data query method.

[0009] According to embodiments of this application, by identifying the standby data table corresponding to each host data table and its processing type, targeted data change task instructions can be generated. This leverages the independence between data change tasks for different standby data tables, combined with the concurrent execution of these tasks using multiple thread resources configured on the database standby, thereby improving data synchronization throughput and effectively reducing database standby data latency. Furthermore, by embedding timestamps into the changed data rows, precise time-series identifiers are provided for each data change. Efficient concurrent synchronization ensures the timeliness of database standby data versions, while the global time-series identifier provides a logical order for these data versions. Together, they not only achieve efficient synchronization and change traceability but also enable the database standby to build a multi-version data chain that is logically consistent with the database host and has strong timeliness. This transforms the database standby from a passive backup node into a peer service node that proactively provides consistent historical data version reads, achieving a performance leap from data redundancy to service redundancy in a high-availability database architecture. Attached Figure Description

[0010] The above-mentioned contents, other objects, features and advantages of this application will become clearer from the following description of embodiments of this application with reference to the accompanying drawings.

[0011] Figure 1 The diagram illustrates an application scenario of the database data synchronization method, database data query method, and apparatus according to embodiments of this application.

[0012] Figure 2 A flowchart of a database data synchronization method according to an embodiment of this application is shown.

[0013] Figure 3 This diagram illustrates the principle of accessing data rows based on visibility timestamps according to an embodiment of this application.

[0014] Figure 4 A flowchart of a database query synchronization method according to an embodiment of this application is shown.

[0015] Figure 5 A schematic diagram of the data query path between the database host and the database standby machine according to an embodiment of this application is shown.

[0016] Figure 6 A structural block diagram of a database data synchronization device according to an embodiment of this application is shown.

[0017] Figure 7 A structural block diagram of a database data query apparatus according to an embodiment of this application is shown.

[0018] Figure 8 A block diagram of an electronic device suitable for implementing a database data synchronization method and a database data query method according to an embodiment of this application is shown. Detailed Implementation

[0019] The embodiments of this application will now be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of this application. In the following detailed description, numerous specific details are set forth to provide a thorough understanding of the embodiments of this application for ease of explanation. However, it will be apparent that one or more embodiments may be implemented without these specific details. Furthermore, descriptions of well-known structures and technologies are omitted in the following description to avoid unnecessarily obscuring the concepts of this application.

[0020] The terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit the scope of this application. The terms “comprising,” “including,” etc., as used herein indicate the presence of the stated features, steps, operations, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, or components.

[0021] All terms used herein (including technical and scientific terms) have the meanings commonly understood by those skilled in the art, unless otherwise defined. It should be noted that the terms used herein are to be interpreted in a manner consistent with the context of this specification, and not in an idealized or overly rigid way.

[0022] When using expressions such as "at least one of A, B and C", they should generally be interpreted in accordance with the meaning that is commonly understood by those skilled in the art (e.g., "a system having at least one of A, B and C" should include, but is not limited to, a system having A alone, a system having B alone, a system having C alone, a system having A and B, a system having A and C, a system having B and C, and / or a system having A, B and C, etc.).

[0023] In the embodiments of this application, the collection, updating, analysis, processing, use, transmission, provision, application, and storage of data (e.g., including but not limited to user personal information) comply with relevant laws and regulations, are used for legitimate purposes, and do not violate public order and good morals. In particular, necessary measures have been taken to prevent unauthorized access to user personal information data and to maintain the security of user personal information and network security.

[0024] In the embodiments of this application, the user's authorization or consent was obtained before obtaining or collecting the user's personal information.

[0025] This application provides a database data synchronization method. It includes: responding to a data change log on a database host; identifying multiple host data tables with data changes and their respective processing types from the data change log; identifying standby data tables on a standby database host corresponding to each host data table based on the multiple host data tables; and generating multiple task instructions for data changes to data rows in the standby data tables based on the multiple processing types; if the multiple standby data tables are different, concurrently executing the multiple task instructions using multiple threads configured on the standby database host; and responding to completed task instructions by adding a timestamp field to the data rows, the timestamp field representing the time of the data row change.

[0026] Figure 1 The diagram illustrates an application scenario of the database data synchronization method, database data query method, and apparatus according to embodiments of this application.

[0027] like Figure 1 As shown, application scenario 100 according to this embodiment may include a database host 101 and a database standby 102. The database host 101 and the database standby 102 can communicate via a network. The network may include various connection types, such as wired or wireless communication links or fiber optic cables, etc.

[0028] Database host 101 may include multiple host data tables, and the data stored in these host data tables may be different. Database standby 102 may also include multiple standby data tables, and the data stored in these standby data tables is the same as the data stored in their respective host data tables.

[0029] Database host 101 can be used to handle read and write requests from clients. When processing a write request, database host 101 modifies the data stored in multiple host data tables based on the processing type of the write request, and generates a data change log which is sent to database standby 102. Database standby 102, as a redundant node of database host 101, replays the data change log and performs data synchronization after receiving it from database host 101, so as to take over the service in the event of a failure of database host 101.

[0030] It should be noted that the database data synchronization method and database data query method provided in this application embodiment can generally be executed by the database standby machine 102. Correspondingly, the database data synchronization device and database data query device provided in this application embodiment can generally be located in the database standby machine 102. The database data synchronization method and database data query method provided in this application embodiment can also be executed by a database or database cluster that is different from the database standby machine 102 and can communicate with the database host 101 and / or the database standby machine 102. Correspondingly, the database data synchronization device and database data query device provided in this application embodiment can also be located in a database or database cluster that is different from the database standby machine 102 and can communicate with the database host 101 and / or the database standby machine 102.

[0031] It should be understood that Figure 1 The number of database hosts and database standby machines shown is for illustrative purposes only. Depending on implementation needs, any number of database hosts and database standby machines can be used.

[0032] Figure 2 A flowchart of a database data synchronization method according to an embodiment of this application is shown.

[0033] like Figure 2 As shown, the database data synchronization method includes steps S210 to S240.

[0034] In step S210, in response to receiving the data change log for the database host, multiple host data tables with data changes are identified from the data change log, as well as the processing type of each of the multiple host data tables.

[0035] In step S220, based on multiple host data tables, standby data tables corresponding to each host data table are identified from the standby database, and based on multiple processing types, multiple task instructions for data changes of data rows in the standby data tables are generated.

[0036] In step S230, when multiple standby machine data tables are different, multiple task instructions are executed concurrently using multiple threads configured on the database standby machine.

[0037] In step S240, in response to the task completion instruction, a timestamp field is added to the data row, the timestamp field representing the time of change of the data row.

[0038] In this embodiment, the primary database table and the standby database table store the same data. When the database primary database table stores data, the corresponding data is also stored on the database standby database table. Therefore, when data changes occur on the primary database table, a corresponding data change log is recorded. This data change log is then reliably transmitted to the database standby database table. The standby database table can parse the data change logs in the order they are received, obtaining multiple primary database tables and their respective processing types. This allows the same data change logic to be executed on the standby database table, ensuring that from initial storage to every subsequent change, the content of the standby database table strictly follows the changes in the primary database table. Therefore, at any stable state point after a synchronization cycle is completed, the data content of all corresponding primary and standby database tables is completely consistent.

[0039] The format of the data change log can be designed according to the specific needs of the synchronization mechanism, and is not specifically limited in this application. For example, in an optional example, the data change log may include multiple log entries, each of which may record information such as the changed data table and the processing type. By parsing each log entry, this information can be extracted and aggregated to accurately identify which data tables have been changed and the specific processing type corresponding to each table. The processing type may include, but is not limited to, at least one of the following: write operation, structure maintenance, metadata change, etc.

[0040] For example, standby data tables corresponding to each primary data table can be identified from the database standby machine through predefined mapping relationships or naming conventions. For instance, a mapping configuration between primary data table names and standby data table names can be established and maintained through configuration files or metadata tables, and this mapping configuration explicitly records the correspondence between the two.

[0041] For example, task instructions can be generated through the mapping relationship between the processed type and the instruction logic. For instance, for an insert operation, an insert instruction containing complete new row data can be generated; for an update operation, a change instruction containing the primary key identifier and the updated field value can be generated; for a delete operation, a delete instruction based on the primary key or unique condition can be generated. These instructions can be encapsulated in conjunction with the table structure, constraints, and data format requirements of the standby data table to obtain the task instructions.

[0042] When there are multiple independent standby database tables that need to be synchronized, in order to improve data synchronization efficiency, the database standby server can schedule multiple pre-configured threads to process task instructions for different standby database tables in parallel. Each thread can target the same standby database table, so the concurrent execution of task instructions by each thread does not interfere with each other.

[0043] For example, a database standby server can maintain a thread pool, routing task instructions to different threads based on distribution rules. For instance, if the standby database tables include customer information table A, order information table B, and product information table C, all task instructions for customer information table A are distributed to thread one, all task instructions for order information table B are distributed to thread two, and all task instructions for product information table C are distributed to thread three. Threads one, two, and three execute their respective task instructions concurrently. Since different standby database tables are handled by different threads, and there is no shared resource contention between threads, concurrent execution of task instructions corresponding to multiple standby database tables is achieved.

[0044] According to embodiments of this application, by identifying the standby data table corresponding to each host data table and its processing type, targeted data change task instructions can be generated. This leverages the independence between data change tasks for different standby data tables, combined with the concurrent execution of these tasks using multiple thread resources configured on the database standby, thereby improving data synchronization throughput and effectively reducing database standby data latency. Furthermore, by embedding timestamps into the changed data rows, precise time-series identifiers are provided for each data change. Efficient concurrent synchronization ensures the timeliness of database standby data versions, while the global time-series identifier provides a logical order for these data versions. Together, they not only achieve efficient synchronization and change traceability but also enable the database standby to build a multi-version data chain that is logically consistent with the database host and has strong timeliness. This transforms the database standby from a passive backup node into a peer service node that proactively provides consistent historical data version reads, achieving a performance leap from data redundancy to service redundancy in a high-availability database architecture.

[0045] According to an embodiment of this application, the database data synchronization method further includes: identifying a flag bit from a task instruction, determining the processing type of the standby data table based on the flag bit; and allocating additional storage space for the standby data table in the case that the processing type is a write operation, so as to execute the task instruction.

[0046] In this embodiment, the flag bit can be understood as a specific identification information on the task instruction, used to indicate the processing type of the standby database table in the instruction. For example, one or more binary bits or enumerated values ​​can be reserved as flag bits in the instruction header or a specific field of the task instruction. The database standby reads the values ​​corresponding to these flag bits and translates them into the corresponding processing type through table lookup or conditional judgment. The write operation type can be understood as the task category for modifying database content, including at least one of insert, delete, and update operations. For example, for the write operation type, "01" can represent an insert operation, "10" can represent a delete operation, and "11" can represent an update operation, etc.

[0047] For example, the specific operation of allocating additional storage space for the standby data table for write operation types may include the following steps: based on the specific operation of the write operation type and the associated data change content, estimate the storage requirements for recording the data change version, and allocate or associate storage space for the data row of the specific operation to store the data change version information on the basis of the original storage structure of the standby data table.

[0048] According to embodiments of this application, by identifying a flag bit from the task instruction, the processing type of the standby data table is determined based on the flag bit, and additional storage space is allocated to the standby data table when the processing type is a write operation. This avoids processing delays or failures caused by insufficient storage space or contention, thereby ensuring that data change tasks can be executed continuously and reliably, and improving the overall stability of the data synchronization process.

[0049] In another example, allocating additional storage space for the standby data table may include the steps of: allocating additional storage space for the data rows in the standby data table to record data change versions.

[0050] Data change versions can include a pre-change data version and a post-change data version. When the write operation type is insert, the pre-change data version is empty, and the post-change data version contains the data to be inserted. When the write operation type is delete, the pre-change data version contains the data to be deleted, and the post-change data version is empty. When the write operation type is update, the pre-change data version contains the data before the update, and the post-change data version contains the updated data.

[0051] In this embodiment, the additional storage space can be understood as being different from the original storage space of the data rows in the standby data table. This way, each time a write operation is performed on a data row, the pre-modification and post-modification versions of the data can be recorded in the additional storage space.

[0052] When the write operation type is insert, since the data row did not exist before the change task, its pre-change data version is empty, while its post-change data version is the data to be inserted into the standby data table. When the write operation type is delete, since the data row does not exist after the change task, its pre-change data version is the data to be deleted from the standby data table, while its post-change data version is empty because the data row has been deleted. When the write operation type is update, since the data row exists both before and after the change task, its pre-change data version is the data already stored in the standby data table and awaiting update, while its post-change data version is the updated data.

[0053] For example, consider a customer information table in the standby data table, where each data row contains "Customer ID" and "Name" fields. When performing an insert operation, a new customer is added (Customer ID: 101, Name: Zhang San). In this case, the data version before the change is empty, and the data version after the change is {Customer ID: 101, Name: "Zhang San"}. When performing a delete operation, the record with Customer ID 101 is deleted. In this case, the data version before the change is {Customer ID: 101, Name: "Zhang San"}, and the data version after the change is empty. When performing an update operation, the name of Customer ID 101 is changed from "Zhang San" to "Li Si". In this case, the data version before the change is {Customer ID: 101, Name: "Zhang San"}, and the data version after the change is {Customer ID: 101, Name: "Li Si"}.

[0054] In another example, in response to a task completion instruction, adding a timestamp field to a data row may include: associating a timestamp field with each modified data version stored in the storage space; and, if there are multiple modified data versions in a data row, sorting them chronologically according to the values ​​of the corresponding timestamp fields to form a version chain.

[0055] In this embodiment, the value of the timestamp field can be the time corresponding to when the database host processes the write operation. The time corresponding to when the database host processes the write operation can be identified from the data change log. When there are multiple modified data versions of a data row, the corresponding multiple values ​​monotonically increase over time, thus forming an ascending order and a version chain. This application does not specifically limit the format of the timestamp field value.

[0056] This application does not specify the exact form of the version chain, but it may include, but is not limited to: a list or array arranged in timestamp order, a combination of key-value pairs arranged in timestamp order, or a set of row versions maintained using a database multi-versioning mechanism. In the key-value pair combination, each key-value pair uses a timestamp as the key and the data version as the value. Any method that establishes a clear temporal order association between different data versions of the same data row and allows for retrieval based on the timestamp field falls within the scope of this application.

[0057] For example, a list or linked list structure of modified data versions can be stored in the storage space for each data row. When a modified data version is generated for a corresponding data row, it can be written to the correct temporal position in the list or linked list based on the value of the timestamp field.

[0058] Taking a specific data row in the standby database table as an example, for the corresponding data row in the master database table, an insert operation was performed at timestamp 1. As time progresses, an update operation was performed at timestamp 2 (after timestamp 1), and a delete operation was performed at timestamp 3 (after timestamp 2). Correspondingly, for the data row in the standby database table, upon receiving the data change log generated after each change operation by the master database, each modified data version will be obtained based on the aforementioned database data synchronization method. After associating each modified data version with its corresponding timestamp, a version chain for that data row can be obtained based on the order of the timestamps, such as "[Timestamp field: Timestamp 1, Data version: Inserted data]", "[Timestamp field: Timestamp 2, Data version: Updated data]", "[Timestamp field: Timestamp 3, Data version: Empty]", etc.

[0059] According to the embodiments of this application, by allocating additional storage space for the data rows of the database standby machine to record data change versions, storage guarantee is provided for creating a data version with a timestamp for each change task. While achieving stable execution of change tasks, the changed data has accurate historical traceability, thereby transforming the database standby machine from a passive backup node into a peer service node that can actively provide consistent historical data version reading.

[0060] According to an embodiment of this application, the database data synchronization method further includes: in response to the arrival of a read request for a data row, assigning a visibility timestamp to the read request; when executing the read request, only accessing the data row corresponding to the timestamp before the visibility timestamp; wherein changes to the data row after the visibility timestamp are not visible to the read request.

[0061] In this embodiment, when a read request for a data row arrives at the database standby server, the standby server can determine a timestamp for the read request based on its arrival time. This timestamp can be called a visibility timestamp. The visibility timestamp determines which version of the data row the read request can access. After accessing the data row corresponding to a timestamp prior to the visibility timestamp, the target data from the data row whose change association timestamp value is no greater than the visibility timestamp and is the data version closest to the visibility timestamp can be returned to the client.

[0062] Figure 3 This diagram illustrates the principle of accessing data rows based on visibility timestamps according to an embodiment of this application.

[0063] For example, during the data synchronization process for a specific data row, the database standby machine obtains different data versions corresponding to different timestamps, such as... Figure 3As shown, before the database standby machine receives the read request, the corresponding data version 1, data version 2 and data version 3 can be obtained at timestamp 1, timestamp 2 and timestamp 3 respectively.

[0064] Meanwhile, if it is determined that a read request sent by the client has arrived at the database standby server, the current timestamp can be obtained and used as the visibility timestamp for the read request, such as... Figure 3 The timestamp corresponding to the read request is displayed. Subsequently, during the execution of this read request by the database standby, if a data version 4 is generated for the same data row at timestamp 4, data version 4 generated for the same data row is not visible to this read request because timestamp 4 occurs after the visibility timestamp. However, timestamps 1, 2, and 3 all occur before the visibility timestamp, so data versions 1, 2, and 3 generated for the same data row are all visible to this read request. Therefore, the target data in data version 3 can be returned to the client.

[0065] According to embodiments of this application, since data row changes after the visibility timestamp are not visible to read requests, changes to data rows in the standby database table are not visible during read operations on the standby database. Therefore, when data changes and read operations are executed concurrently, it ensures that read operations obtain a consistent data version without being affected by the changed data. This effectively avoids read-write conflicts and guarantees the consistency of read data.

[0066] According to an embodiment of this application, identifying standby data tables corresponding to each host data table from a database standby machine based on multiple host data tables may include: identifying standby data tables with the same identifier as each host data table from the database standby machine to obtain multiple standby data tables.

[0067] In this embodiment, the database standby machine searches for standby data tables that correspond one-to-one with or have the exact same identifier as the host data tables by comparing predefined mapping relationships or querying the database standby machine directory. Specifically, the database standby machine queries and matches in its standby data tables or metadata storage based on a known list of host data tables, such as a list of table names or configured replication source table information. It identifies all standby data tables that exist on the database standby machine and whose identifiers are completely identical to the identifiers of any host data table. Finally, these successfully matched data tables are determined as the target standby data tables that need to be synchronized.

[0068] For example, suppose the primary database host has a customer information table named `customer_info`, which exists as a primary table on the primary database host. When scanning all standby tables on the standby database host, if a standby table named `customer_info` is found, it's confirmed that the two tables share the same identifier, thus identifying that standby table as one of the target standby tables. If the primary database host also has primary tables such as `order_info`, the same process is repeated on the standby database host, ultimately resulting in multiple standby tables containing `customer_info`, `order_info`, and other similar tables.

[0069] According to the embodiments of this application, since the identifier is unique, identifying the standby data table on the database standby machine that is the same as the identifier of each master data table can accurately direct the task instructions of each master data table to the corresponding standby data table, ensuring the consistency and integrity of data changes, and avoiding problems such as disordered change order or data inconsistency caused by inter-table dependencies.

[0070] Figure 4 A flowchart of a database query synchronization method according to an embodiment of this application is shown.

[0071] like Figure 4 As shown, the method includes steps S410 to S420.

[0072] In step S410, in response to receiving a target read request initiated by the client, the target data row of the target data table is determined from the target read request, and the target time of the read request is determined based on the value of the timestamp field that has been added to the target data row at the current time.

[0073] In step S420, based on the target time, target data is read from the target data row and returned to the client.

[0074] For example, a user can perform a data query on the client. After the user inputs the data query statement into the client, the client can generate a target read request based on the query statement and send the target read request to the database standby server or the database master server. After receiving the target read request, the database standby server or the database master server can parse the intent information in the target read request and extract the target data table name or target data table identifier and the query conditions from the intent information. The target data table's metadata, such as table structure and index information, can be accessed based on the target data table name or target data table identifier, and the target data rows that meet the query conditions can be found from the target data table index.

[0075] The timestamp field added to the target data row at the current time is added during the database data synchronization process. Please refer to the database data synchronization method section in the above embodiments of this application, which will not be repeated here.

[0076] For example, determining the target time for a read request based on the value of the timestamp field already added to the target data row at the current time may include the following steps: selecting the timestamp with the largest value from the timestamp fields already added to the target data row at the current time as a reference time; and obtaining the target time by summing the reference time and a predetermined duration. For instance, when multiple data change versions exist, when the database standby or database master receives a read request, the read request will find the global timestamp of the current data change version, increment it by 1, and record it in the thread corresponding to the read request; this value is called the database read timestamp. Among the multiple data change versions in the database, the first data change version with a timestamp smaller than the database read timestamp is found; the content recorded on this data change version is the queried data change version. Specifically, if no data change version exists, the current data can be returned to the client.

[0077] For example, reading target data from a target data row based on a target time may include the steps of: filtering multiple timestamp fields that are less than the target time from the version chain corresponding to the target data row; and determining the modified data version corresponding to the timestamp field with the largest value among the multiple timestamp fields as the target data. Since the target data is obtained after performing database data synchronization, please refer to the database data synchronization method section in the above embodiments of this application, which will not be repeated here.

[0078] For example, suppose a client initiates a read request targeting the record with customer ID 101 in the `customer_info` table, intending to retrieve information about that customer at "3 PM yesterday". The database standby server first determines that the target table is `customer_info` and the target row is the record with ID=101. Based on the timestamp field of the target row, it finds versions in the version chain with timestamps of "2 PM yesterday" and "5 PM yesterday". Based on the values ​​of these timestamp fields and the implicit intent of "3 PM yesterday" in the request, the database standby server can determine that the target time of this read request is "3 PM yesterday" and find the appropriate data version accordingly.

[0079] For example, the database standby server has determined that the target time for a read request for customer ID 101 is "yesterday at 4 PM". The standby server checks the version chain of the data row, with timestamps as follows: [Data Version A: Yesterday at 1 PM, Name "Zhang San"], [Data Version B: Yesterday at 2:30 PM, Name "Li Si"], [Data Version C: Yesterday at 5 PM, Name "Wang Wu"]. The standby server first filters the version chain for all data versions with timestamps less than the target time (4 PM), resulting in data version A (1 PM) and data version B (2:30 PM). Next, the database master or standby server selects the modified data version corresponding to data version B (2:30 PM) with the largest timestamp value from these two matching timestamps as the target data, and finally returns {Customer ID: 101, Name: "Li Si"} as the target data to the client.

[0080] According to embodiments of this application, by constructing a data version with a timestamp in the database data synchronization method, when a client initiates a target read request, the target data can be obtained not only on the database host but also on the database standby. This achieves load balancing among multiple databases providing external services in a database cluster, improving the cluster's external service capabilities. Furthermore, based on the target time intended for access by the target read request, the synchronized data versions carrying timestamps are traversed, and the data version with a timestamp no greater than the target time is precisely located. This ensures that write operations occurring during the response to the read request do not affect the current response operation, thereby enabling concurrent read and write operations in the database without lock conflicts, improving the database's concurrent processing capabilities and response efficiency.

[0081] Figure 5 A schematic diagram of the data query path between the database host and the database standby machine according to an embodiment of this application is shown.

[0082] According to embodiments of this application, for example, Figure 4 In step S410, if the number of read requests in the read queue of the database host exceeds a predetermined threshold, the target data row of the target data table targeted by the target read request is determined from the database standby machine.

[0083] When the number of pending read requests on the database host exceeds a predetermined threshold, the query execution path of newly arriving or some pending read requests can be redirected to alleviate the load on the database host. Specifically, instead of waiting to execute the request on the database host, the database backup server is used to determine the specific target data row in the target data table specified by the request.

[0084] For example, suppose the predetermined threshold is 100 requests. When the number of pending read requests in the database host's read queue reaches 150, exceeding the threshold, a new target read request arrives, intending to query the record with customer ID 101 in the customer_info table. This request will not be placed in the already congested read queue of the database host; instead, a decision is made immediately to route it to the database standby server. On the standby server, the target data table with the same name, customer_info, is identified, and the target data row with customer ID 101 is found. Finally, the data is read from the standby server and returned to the client.

[0085] According to embodiments of this application, when the read pressure on the database host exceeds a predetermined threshold, subsequent read requests are routed to the database standby machine for processing. This achieves load balancing between the primary and standby nodes, reduces the concurrent processing pressure on the database host, and avoids performance bottlenecks caused by excessive load on a single database node. Furthermore, it utilizes the idle computing resources of the database standby machine, improving the overall throughput and resource utilization of the database cluster, enhancing the database system's ability to handle high-concurrency read requests, and improving overall service responsiveness.

[0086] Based on the above-described database data synchronization method, this application also provides a database data synchronization device. The following will be combined with... Figure 6 The device is described in detail.

[0087] Figure 6 A structural block diagram of a database data synchronization device according to an embodiment of this application is shown.

[0088] like Figure 6 As shown, the database data synchronization device 600 of this embodiment includes an identification module 610, a generation module 620, a concurrency module 630, and an addition module 640.

[0089] The identification module 610 is used to identify, in response to receiving a data change log for a database host, multiple host data tables that have undergone data changes and the processing type of each of the multiple host data tables from the data change log.

[0090] The generation module 620 is used to identify the standby data table corresponding to each host data table from the database standby machine based on multiple host data tables, and generate multiple task instructions for data modification of data rows in the standby data table based on multiple processing types. The host data table and the standby data table store the same data.

[0091] The concurrency module 630 is used to execute multiple task instructions concurrently using multiple threads configured on the database standby machine when multiple standby machine data tables are different.

[0092] Add module 640 to add a timestamp field to the data row in response to a completed task instruction. The timestamp field represents the time when the data row was modified.

[0093] According to embodiments of this application, the database data synchronization device 600 may further include a processing module and an execution module.

[0094] The processing module is used to identify the flag bits from the task instructions and determine the processing type of the standby data table based on the flag bits.

[0095] The execution module is used to allocate additional storage space for the standby data table when the processing type is write operation, so as to execute the change task.

[0096] According to embodiments of this application, the execution module may include a modification submodule.

[0097] The Change submodule allocates additional storage space for data rows in the standby data table to record data change versions. These versions include the data before the change and the data after the change. When the write operation type is Insert, the data before the change is empty, and the data after the change is the data to be inserted. When the write operation type is Delete, the data before the change is the data to be deleted, and the data after the change is empty. When the write operation type is Update, the data before the change is the data before the update, and the data after the change is the data after the update.

[0098] According to an embodiment of this application, the adding module 640 may include a storage submodule and a sorting submodule.

[0099] The storage submodule is used to associate a timestamp field with each modified version of data stored in the storage space.

[0100] The sorting submodule is used to sort data rows in chronological order according to the value of the corresponding timestamp field, forming a version chain, when there are multiple modified versions of the data row.

[0101] According to embodiments of this application, the database data synchronization device 600 may further include an allocation module and an access module.

[0102] The allocation module is used to assign a visibility timestamp to a read request in response to the arrival of a read request for a data row.

[0103] The access module is used to access only the data rows corresponding to timestamps prior to the visibility timestamp when executing a read request. Changes to data rows after the visibility timestamp are not visible to the read request.

[0104] According to an embodiment of this application, the generation module 620 includes a comparison submodule.

[0105] The comparison submodule is used to identify standby data tables on the database standby machine that have the same identifier as the data tables on each master machine, thus obtaining multiple standby data tables.

[0106] Based on the above-described database data query method, this application also provides a database data query device. The following will be combined with... Figure 7 The device is described in detail.

[0107] Figure 7 A structural block diagram of a database data query apparatus according to an embodiment of this application is shown.

[0108] like Figure 7 As shown, the database data query device 700 of this embodiment includes a determination module 710 and a reading module 720.

[0109] The determination module 710 is used to respond to receiving a target read request initiated by the client, determine the target data row of the target data table from the target read request, and determine the target time of the read request based on the value of the timestamp field that has been added to the target data row at the current time.

[0110] The reading module 720 is used to read target data from the target data row based on the target time and return it to the client.

[0111] According to an embodiment of this application, the determining module 710 includes a determining submodule.

[0112] The judgment submodule is used to determine the target data row of the target data table targeted by the target read request from the database standby machine when the number of read requests in the read queue of the database host exceeds a predetermined threshold.

[0113] It should be noted that the database data synchronization device part in the embodiments of the present invention corresponds to the database data synchronization method part in the embodiments of the present invention. The description of the database data synchronization device part is specifically referred to in the database data synchronization method part, and will not be repeated here.

[0114] Figure 8 A block diagram of an electronic device suitable for implementing a database data synchronization method and a database data query method according to an embodiment of this application is shown.

[0115] like Figure 8As shown, an electronic device 800 according to an embodiment of this application includes a processor 801, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 802 or a program loaded from a storage portion 808 into a random access memory (RAM) 803. The processor 801 may include, for example, a general-purpose microprocessor (e.g., a CPU), an instruction set processor and / or an associated chipset and / or a special-purpose microprocessor (e.g., an application-specific integrated circuit (ASIC)), etc. The processor 801 may also include onboard memory for caching purposes. The processor 801 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of this application.

[0116] RAM 803 stores various programs and data required for the operation of electronic device 800. Processor 801, ROM 802, and RAM 803 are interconnected via bus 804. Processor 801 executes various operations of the method flow according to embodiments of this application by executing programs in ROM 802 and / or RAM 803. It should be noted that programs may also be stored in one or more memories other than ROM 802 and RAM 803. Processor 801 may also execute various operations of the method flow according to embodiments of this application by executing programs stored in one or more memories.

[0117] According to embodiments of this application, the electronic device 800 may further include an input / output (I / O) interface 805, which is also connected to a bus 804. The electronic device 800 may also include one or more of the following components connected to the input / output (I / O) interface 805: an input section 806 including a keyboard, mouse, etc.; an output section 807 including a cathode ray tube (CRT), liquid crystal display (LCD), etc., and a speaker, etc.; a storage section 808 including a hard disk, etc.; and a communication section 809 including a network interface card such as a LAN card, modem, etc. The communication section 809 performs communication processing via a network such as the Internet. A drive 810 is also connected to the input / output (I / O) interface 805 as needed. A removable medium 811, such as a disk, optical disk, magneto-optical disk, semiconductor memory, etc., is installed on the drive 810 as needed so that computer programs read from it can be installed into the storage section 808 as needed.

[0118] This application also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiments; or it may exist independently and not assembled into the device / apparatus / system. The computer-readable storage medium carries one or more programs, which, when executed, implement the method according to the embodiments of this application.

[0119] According to embodiments of this application, the computer-readable storage medium can be a non-volatile computer-readable storage medium, such as including but not limited to: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this application, the computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. For example, according to embodiments of this application, the computer-readable storage medium may include ROM 802 and / or RAM 803 and / or one or more memories other than ROM 802 and RAM 803 described above.

[0120] Embodiments of this application also include a computer program product comprising a computer program containing program code for performing the methods shown in the flowchart. When the computer program product is run on a computer system, the program code is used to cause the computer system to implement the methods provided in the embodiments of this application.

[0121] When the computer program is executed by the processor 801, it performs the functions defined in the system / apparatus of this application embodiment. According to the embodiments of this application, the systems, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0122] In one embodiment, the computer program may rely on a tangible storage medium such as an optical storage device or a magnetic storage device. In another embodiment, the computer program may also be transmitted and distributed in the form of signals over a network medium, and may be downloaded and installed via the communication section 809, and / or installed from a removable medium 811. The program code contained in the computer program can be transmitted using any suitable network medium, including but not limited to: wireless, wired, etc., or any suitable combination thereof.

[0123] In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 809, and / or installed from the removable medium 811. When the computer program is executed by the processor 801, it performs the functions defined in the system of this application embodiment. According to the embodiments of this application, the systems, devices, apparatuses, modules, units, etc., described above can be implemented by computer program modules.

[0124] According to embodiments of this application, program code for executing the computer programs provided in the embodiments of this application can be written in any combination of one or more programming languages. Specifically, these computational programs can be implemented using high-level procedural and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, languages ​​such as Java, C++, Python, "C", or similar programming languages. The program code can be executed entirely on the user's computing device, partially on the user's device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0125] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in a block diagram or flowchart, and combinations of blocks in a block diagram or flowchart, may be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0126] Those skilled in the art will understand that the features described in the various embodiments of this application can be combined and / or combined in various ways, even if such combinations or combinations are not explicitly described in this application. In particular, the features described in the various embodiments of this application can be combined and / or combined in various ways without departing from the spirit and teachings of this application. All such combinations and / or combinations fall within the scope of this application.

[0127] The embodiments of this application have been described above. However, these embodiments are merely illustrative and not intended to limit the scope of this application. Although various embodiments have been described above, this does not mean that the measures in the various embodiments cannot be used advantageously in combination. Without departing from the scope of this application, those skilled in the art can make various substitutions and modifications, all of which should fall within the scope of this application.

Claims

1. A database data synchronization method, characterized by, The method comprises: in response to receiving a data change log of a database host, identifying a plurality of host data tables in which data changes exist and a plurality of processed types of each of the plurality of host data tables from the data change log; based on the plurality of host data tables, identifying a plurality of standby data tables corresponding to each of the plurality of host data tables on a database standby host, and generating a plurality of task instructions for data changes of data rows of the plurality of standby data tables based on the plurality of processed types, the host data tables and the standby data tables storing the same data; in the case that the plurality of standby data tables are different, concurrently executing the plurality of task instructions by using a plurality of threads configured on the database standby host; in response to the task instructions being completed, adding a timestamp field in the data rows, the timestamp field representing a change time of the data rows.

2. The method of claim 1, wherein, The method further comprises: identifying a flag bit from the task instructions, and determining a processing type of the standby data table based on the flag bit; in the case that the processing type is a write operation type, additionally allocating storage space for the standby data table in order to execute the task instructions.

3. The method of claim 2, wherein, The additionally allocating storage space for the standby data table comprises: Additionally allocating the storage space for the data rows in the standby data table to record data change versions, the data change versions comprising a pre-change data version and a post-change data version; wherein, in the case that the write operation type is an insertion operation, the pre-change data version is empty and the post-change data version is data to be inserted; in the case that the write operation type is a deletion operation, the pre-change data version is data to be deleted and the post-change data version is empty; in the case that the write operation type is an update operation, the pre-change data version is data before update and the post-change data version is data after update.

4. The method of claim 3, wherein, The adding a timestamp field in the data rows in response to the task instructions being completed comprises: associating the timestamp field with each of the post-change data versions stored in the storage space; in the case that there are a plurality of post-change data versions in the data rows, sequentially sorting the post-change data versions according to values of corresponding timestamp fields to form a version chain.

5. The method of claim 1, wherein, The method further comprises: in response to a read request for the data rows arriving, assigning a visibility timestamp for the read request; when executing the read request, only accessing data rows corresponding to timestamps before the visibility timestamp; wherein, data row changes after the visibility timestamp are invisible to the read request.

6. The method of claim 1, wherein, The identifying a plurality of standby data tables corresponding to each of the plurality of host data tables on a database standby host based on the plurality of host data tables comprises: identifying the plurality of standby data tables from the database standby host which have the same identifier as each of the plurality of host data tables.

7. A database data query method, characterized by, The method comprises: in response to receiving a target read request initiated by a client, determining a target data row of a target data table from the target read request, and determining a target time to which the read request is directed based on a value of a timestamp field added in the target data row at a current time. reading target data from the target data row based on the target time, and returning to the client; wherein the timestamp field and the target data are obtained by the database data synchronization method of any one of claims 1-6.

8. The method of claim 7, wherein, The target data row of the target data table to which the target read request is directed is determined from the target read request, including: In a case where the number of read requests in the read queue of the database master host exceeds a predetermined threshold, the target data row of the target data table to which the target read request is directed is determined from the database standby host.

9. A database data synchronization apparatus, characterized by comprising: The apparatus comprises: The identification module is configured to, in response to receiving a data change log of a database master host, identify a plurality of host data tables in which data changes exist and a processed type of each of the plurality of host data tables from the data change log; The generation module is configured to, based on the plurality of host data tables, identify a standby data table corresponding to each of the host data tables from a database standby host, and based on the plurality of processed types, generate a plurality of task instructions for data changes of data rows of the standby data table, the host data table and the standby data table storing the same data; The concurrent module is configured to, in a case where the plurality of standby data tables are different, use a plurality of threads configured on the database standby host to concurrently execute the plurality of task instructions; The addition module is configured to, in response to the task instructions being completed, add a timestamp field in the data row, the timestamp field representing a change time of the data row.

10. A database data query apparatus, characterized by comprising: The apparatus comprises: The determination module is configured to, in response to receiving a target read request initiated by a client, determine a target data row of a target data table to which the target read request is directed from the target read request, and determine a target time to which the read request is directed based on a value of a timestamp field added in the target data row at a current time; The reading module is configured to read target data from the target data row based on the target time, and return to the client; wherein the timestamp field and the target data are obtained by the database data query method of any one of claims 7-8.

Images