Database access methods, devices, equipment and storage media

Abstract

This application relates to the field of computer technology and provides a database access method, apparatus, device, and storage medium. The method acquires a business request and determines whether the business request is a query request. If the business request is a query request, the method acquires the replication delay value between the current master node and the auxiliary node based on the latency acquisition time interval. The method compares the replication delay value with a preset timeout delay threshold. If the replication delay value is not greater than the preset timeout delay threshold, the method acquires the time of the most recent execution of the data manipulation language of the business request. The method determines the routing mode of the business request based on the current time and the time of the most recent execution of the data manipulation language.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a database access method, apparatus, device, and storage medium. Background Technology

[0002] A database is a "repository for organizing, storing, and managing data according to a data structure." It is a large collection of organized, shareable, and centrally managed data that is stored permanently in a computer.

[0003] There are generally two methods for data synchronization in high-availability database architectures: data distribution technology and read / write splitting technology. In data distribution technology, after a master-slave switch, the online slave node becomes the new master node, handling the same amount of business. Since both master and slave nodes are high-configuration servers, this leads to resource waste. Traditional read / write splitting, which aims to overcome this resource waste, simply forwards read requests to slave nodes without considering data read / write consistency. Currently, to achieve data read / write consistency, existing technologies specify specific Structured Query Language (SQL) queries for routing to read nodes. For example, SQL queries that are not sensitive to data read / write consistency, or SQL queries that read from dead tables, are routed to read nodes.

[0004] However, existing technologies require manual inspection of each SQL statement to determine whether it has no requirement for data read / write consistency based on the business scenario, or whether the SQL statement only queries dead data tables. This results in a large workload for database access, low level of intelligence, and low database access efficiency. Summary of the Invention

[0005] This application provides a database access method, apparatus, device, and storage medium, thereby solving the technical problems of existing technologies that require manual inspection of each SQL statement, determining whether it has no requirement for data read / write consistency based on the business scenario, or determining whether the SQL statement only queries dead data tables, resulting in a large workload, low level of intelligence, and low database access efficiency.

[0006] Firstly, this application provides a database access method, including:

[0007] Obtain the business request and determine whether the business request is a query request;

[0008] If the business request is a query request, then the replication delay value between the current master node and the auxiliary node is obtained according to the delay acquisition time interval;

[0009] The replication delay value is compared with a preset timeout delay threshold;

[0010] If the replication delay value is not greater than the preset timeout delay threshold, then obtain the time of the most recent execution of the data manipulation language for the service request;

[0011] The routing method for the business request is determined based on the current time and the time of the most recent execution of the data manipulation language.

[0012] This application provides a database access method that automatically obtains the replication delay value between the current master node and the slave node based on the latency acquisition time interval when the database has a query request. If the replication delay value is not greater than a preset timeout threshold, it automatically obtains the time when the table involved in the business request last performed Data Manipulation Language (DML), i.e., the time of the last DML execution. This allows it to determine whether any table has undergone DML operations within the forwarding time threshold. Based on the current time and the time of the last DML execution, it determines whether to route the business request to the current master node or the replication node, ensuring read-write consistency in read-write separation scenarios. Furthermore, it eliminates the need for manual inspection of each SQL statement and manual judgment based on the business scenario regarding whether the SQL statement has no requirement for data read-write consistency or whether it only queries dead data tables. It automatically and intelligently routes as many query-type SQL statements as possible to the read nodes, maximizing the utilization of read nodes while meeting business read-write consistency requirements, thus improving database access efficiency.

[0013] Optionally, before obtaining the replication delay value between the current master node and the slave node based on the delay acquisition time interval, the method further includes:

[0014] Get the current replication delay value, the current time interval, and the previous replication delay value;

[0015] The delay acquisition time interval is determined based on the current replication delay value, the current time interval, and the previous replication delay value.

[0016] Here, the embodiments of this application can automatically determine the delay acquisition time interval by using the current replication delay value, the current time interval, and the previous replication delay value, and automatically adjust the frequency of acquiring the delay value, thereby further improving the efficiency of database access.

[0017] Optionally, determining the delay acquisition time interval based on the current replication delay value, the current time interval, and the previous replication delay value includes:

[0018] Compare the current replication delay value with the previous replication delay value;

[0019] If the current replication delay value is less than the previous replication delay value, then the first delay acquisition time interval is determined to be half of the current time interval;

[0020] If the current replication delay value is not less than the previous replication delay value, then the first delay acquisition time interval is determined to be the sum of the current time interval and the preset additional value;

[0021] Compare the first delay acquisition time interval with the preset minimum time interval;

[0022] If the first delay acquisition time interval is less than the preset minimum time interval, then the delay acquisition time interval is determined to be the preset minimum time interval;

[0023] If the first delay acquisition time interval is not less than the preset minimum time interval, then the delay acquisition time interval is determined to be the first delay acquisition time interval.

[0024] Specifically, when the replication latency value decreases, the frequency of acquiring the latency value increases rapidly due to the exponential decrease in the interval time. This allows for faster acquisition of the latest latency value because the replication latency value is smaller than the previous one. Therefore, the time threshold for routing read nodes is replaced with the smaller replication latency value acquired in this detection, thereby forwarding more query statements to read nodes. When the replication latency value increases, the interval time increases by a fixed amount, which can quickly reduce the frequency of latency value acquisition, reduce meaningless latency value acquisition, further reduce the power consumption of database access, reduce workload, and improve the efficiency of database access.

[0025] Optionally, determining the routing method for the service request based on the current time and the time of the most recent execution of the data manipulation language includes:

[0026] Determine the difference between the current time and the time of the most recent execution of the data manipulation language;

[0027] If the difference is less than the preset routing read node time threshold, the service request will be routed to the current master node.

[0028] If the difference is not less than the preset routing read node time threshold, then the service request will be routed to the auxiliary node.

[0029] Here, the embodiments of this application can determine whether there has been a DML operation on the current business table within the forwarding time threshold based on the difference between the current time and the time of the most recent DML execution. This allows for direct read operations without the need for additional write operations, reducing workload, lowering power consumption, and further improving the efficiency of database access.

[0030] Optionally, the step of routing the service request to the current master node if the difference is less than a preset routing read node time threshold includes:

[0031] If the difference is less than the preset routing read node time threshold, the service request will be routed to the current master node.

[0032] Record the table name and operation completion time of the business statement.

[0033] Here, in this embodiment of the application, after routing the business request to the current master node to perform the write operation, the operation completion time is also recorded so as to process subsequent business requests based on the operation completion time.

[0034] Optionally, after comparing the replication delay value with a preset timeout delay threshold, the method further includes:

[0035] If the replication delay value is greater than the preset timeout delay threshold, the service request will be routed to the current master node.

[0036] Here, this embodiment of the application focuses on write operations, directly routing business requests to the current master node to achieve efficient, fast, and intelligent database access operations.

[0037] Optionally, obtaining the time of the most recent execution of the Data Manipulation Language (DML) for the business request includes:

[0038] Parse the business request and extract the tables involved in the business request, obtain the current time and record the time when the table operation is completed;

[0039] The time when the table operation is completed is recorded in a global record variable;

[0040] The time of the most recent execution of the data manipulation language for the business request is obtained based on the global record variable.

[0041] Secondly, this application provides a database access device, comprising:

[0042] The first acquisition module is used to acquire business requests and determine whether the business request is a query request;

[0043] The second acquisition module is used to acquire the replication delay value between the current master node and the auxiliary node based on the delay acquisition time interval if the business request is a query request.

[0044] The first comparison module is used to compare the replication delay value with a preset timeout delay threshold.

[0045] The third acquisition module is used to acquire the time of the most recent execution of the data manipulation language in the business request if the replication delay value is not greater than the preset timeout delay threshold.

[0046] The first determining module is used to determine the routing method of the business request based on the current time and the time of the most recent execution of the data manipulation language.

[0047] Optionally, before the second acquisition module acquires the replication delay value between the current master node and the slave node based on the delay acquisition time interval, the above-mentioned device further includes:

[0048] The fourth acquisition module is used to acquire the current replication delay value, the current time interval, and the previous replication delay value;

[0049] The second determining module is used to determine the delay acquisition time interval based on the current replication delay value, the current time interval, and the previous replication delay value.

[0050] Optionally, the second determining module is specifically used for:

[0051] Compare the current replication delay value with the previous replication delay value;

[0052] If the current replication delay value is less than the previous replication delay value, then the first delay acquisition time interval is determined to be half of the current time interval;

[0053] If the current replication delay value is not less than the previous replication delay value, then the first delay acquisition time interval is determined to be the sum of the current time interval and the preset additional value;

[0054] Compare the first delay acquisition time interval with the preset minimum time interval;

[0055] If the first delay acquisition time interval is less than the preset minimum time interval, then the delay acquisition time interval is determined to be the preset minimum time interval;

[0056] If the first delay acquisition time interval is not less than the preset minimum time interval, then the delay acquisition time interval is determined to be the first delay acquisition time interval.

[0057] Optionally, the first determining module is specifically used for:

[0058] Determine the difference between the current time and the time of the most recent execution of the data manipulation language;

[0059] If the difference is less than the preset routing read node time threshold, the service request will be routed to the current master node.

[0060] If the difference is not less than the preset routing read node time threshold, then the service request will be routed to the auxiliary node.

[0061] Optionally, the first determining module is further specifically used for:

[0062] If the difference is less than the preset routing read node time threshold, the service request will be routed to the current master node.

[0063] Record the table name and operation completion time of the business statement.

[0064] Optionally, after the first comparison module compares the replication delay value with a preset timeout delay threshold, the above apparatus further includes:

[0065] The processing module is configured to route the service request to the current master node if the replication delay value is greater than the preset timeout delay threshold.

[0066] Optionally, the third processing module is specifically used for:

[0067] Parse the business request and extract the tables involved in the business request, obtain the current time and record the time when the table operation is completed;

[0068] The time when the table operation is completed is recorded in a global record variable;

[0069] The time of the most recent execution of the data manipulation language for the business request is obtained based on the global record variable.

[0070] Thirdly, this application provides a database access device, including: at least one processor and a memory;

[0071] The memory stores computer-executed instructions;

[0072] The at least one processor executes computer execution instructions stored in the memory, causing the at least one processor to perform the database access method as described in the first aspect and various possible designs of the first aspect.

[0073] Fourthly, the present invention provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the database access method described in the first aspect and various possible designs of the first aspect.

[0074] Fifthly, the present invention provides a computer program product, including a computer program that, when executed by a processor, implements the database access method described in the first aspect and various possible designs of the first aspect.

[0075] The database access method, apparatus, device, and storage medium provided in this application, wherein when there is a query request in the database, the method can automatically obtain the replication delay value between the current master node and the auxiliary node according to the delay acquisition time interval, and automatically obtain the time when the table involved in the business request last performed DML, i.e., the time of the last DML execution, if the replication delay value is not greater than a preset timeout delay threshold. This can determine whether there is a table that has undergone DML operation within the forwarding time threshold, and thus determine whether to route the business request to the current master node or the replication node based on the current time and the time of the last DML execution. This ensures read and write consistency in the context of read and write separation of database access, and eliminates the need for manual inspection of each SQL statement, and eliminates the need for manual judgment based on the business scenario whether the SQL statement has no requirements for data read and write consistency, or whether the SQL statement only queries dead data tables. The method automatically and intelligently routes as many query-type SQL statements as possible to the read nodes, thereby maximizing the utilization rate of read nodes while meeting the business read and write consistency requirements and improving the efficiency of database access. Attached Figure Description

[0076] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0077] Figure 1 A schematic diagram of a database access system architecture provided in this application embodiment;

[0078] Figure 2 A schematic flowchart illustrating a database access method provided in an embodiment of this application;

[0079] Figure 3 A flowchart illustrating another database access method provided in an embodiment of this application;

[0080] Figure 4 This is a schematic diagram of the structure of a database access device provided in an embodiment of this application;

[0081] Figure 5 This is a schematic diagram of the structure of a database access device provided in an embodiment of this application.

[0082] The accompanying drawings have illustrated specific embodiments of this disclosure, which will be described in more detail below. These drawings and descriptions are not intended to limit the scope of the concept in any way, but rather to illustrate the concepts of this disclosure to those skilled in the art through reference to particular embodiments. Detailed Implementation

[0083] Exemplary embodiments will now be described in detail, examples of which are illustrated in the accompanying drawings. When the following description relates to the drawings, unless otherwise indicated, the same numerals in different drawings denote the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with this disclosure. Rather, they are merely examples of apparatuses and methods consistent with some aspects of this disclosure as detailed in the appended claims.

[0084] The terms “first,” “second,” “third,” and “fourth,” etc. (if present), in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a particular order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms “comprising” and “having,” and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0085] First, the terms used in the embodiments of this application will be explained:

[0086] Replication and distribution: A technique used in databases to synchronize data between primary and secondary nodes. The primary replica sends operation logs to the secondary replicas, and the secondary replicas replay the operation logs to achieve data synchronization between the primary and secondary replicas.

[0087] Read-write separation: a way of accessing a database where query operations and update operations (insert, update, delete) are handled by different machines.

[0088] Structured Query Language (SQL) is a special-purpose programming language, a database query and programming language used to access, query, update, and manage relational database systems.

[0089] High-availability database architectures typically use replication and distribution technology for data synchronization, employing a master node and multiple slave nodes. Only the master node can write, while slave nodes can only read. Slave nodes serve as online hot standby for failover in case of master node failure. After a failover, the online slave node must become the new master node, handling the same amount of business. Therefore, it is configured with the same high-performance server as the master node. If it is only used as a hot standby node and not for other purposes, it will result in resource waste. Therefore, in most cases, a certain amount of read traffic is allocated to slave nodes. Traditional read-write separation simply forwards read requests to slave nodes without considering data read-write consistency. For example, in business processes involving read-after-write operations, due to the data synchronization delay on slave nodes, directly distributing query SQL to slave nodes will lead to data read-write inconsistency. Read-write separation solutions are used in database access to improve the resource utilization of standby servers. However, when business operations have read-write consistency requirements, such as read-after-write scenarios, the business may encounter errors because it cannot read newly written data.

[0090] Current solutions involve routing specific SQL queries to read nodes, such as SQL queries that are not sensitive to data read / write consistency or SQL queries that read dead tables. However, this approach requires manual inspection of each SQL query to determine whether it is not sensitive to data read / write consistency or whether it only queries dead tables, which is labor-intensive. Furthermore, new business SQL queries need to be inspected again, making it not intelligent enough.

[0091] To address the aforementioned issues, embodiments of this application provide a database access method, apparatus, device, and storage medium. This method, by periodically comparing the replication latency values ​​between the current master node and auxiliary nodes, and determining the current time and the time of the most recent DML execution, automatically and intelligently routes as many query-type SQL queries as possible to read nodes. This maximizes the utilization rate of read nodes while meeting the business read-write consistency requirements, thereby improving the efficiency of database access.

[0092] Optional, Figure 1 This is a schematic diagram of a database access system architecture provided in an embodiment of this application. Figure 1 In the above architecture, at least one of receiving device 101, processor 102 and display device 103 is included.

[0093] It is understood that the structures illustrated in the embodiments of this application do not constitute a specific limitation on the architecture of a database access system. In other feasible embodiments of this application, the above architecture may include more or fewer components than illustrated, or combine some components, or split some components, or arrange different components, which can be determined according to the actual application scenario and is not limited here. Figure 1The components shown can be implemented in hardware, software, or a combination of both.

[0094] In the specific implementation process, the receiving device 101 can be an input / output interface or a communication interface.

[0095] Processor 102 can periodically compare the replication latency values ​​between the current master node and the auxiliary node, and determine the current time and the time of the most recent DML execution. It can automatically and intelligently route as many query-type SQL queries as possible to the read nodes, thereby maximizing the utilization of read nodes and improving the efficiency of database access while meeting the business read-write consistency requirements.

[0096] The display device 103 can be used to display the above results, etc.

[0097] The display device can also be a touch screen, used to receive user commands while displaying the above content, so as to achieve interaction with the user.

[0098] It should be understood that the aforementioned processor can be implemented by reading instructions from memory and executing those instructions, or it can be implemented through chip circuitry.

[0099] Furthermore, the network architecture and business scenarios described in the embodiments of this application are for the purpose of more clearly illustrating the technical solutions of the embodiments of this application, and do not constitute a limitation on the technical solutions provided in the embodiments of this application. As those skilled in the art will know, with the evolution of network architecture and the emergence of new business scenarios, the technical solutions provided in the embodiments of this application are also applicable to similar technical problems.

[0100] The technical solution of this application will be described in detail below with reference to specific embodiments:

[0101] Optionally, Figure 2 This is a flowchart illustrating a database access method provided in an embodiment of this application. The execution entity of this embodiment can be... Figure 1 The processor 102 in the code can be specifically executed based on the actual application scenario. For example... Figure 2 As shown, the method includes the following steps:

[0102] S201: Obtain the business request and determine whether the business request is a query request.

[0103] Optionally, the business request can be DML SQL or Data Definition Language (DDL) SQL.

[0104] Optionally, for DML SQL and DDL SQL, the SQL statement is parsed, the tables involved are extracted, and the completion time of these table operations is recorded in the global record variable map_dml_records(string,uint64_t).

[0105] S202: If the business request is a query request, then obtain the replication delay value between the current master node and the auxiliary node based on the delay acquisition time interval.

[0106] Here, in this embodiment of the application, the replication delay value between the current master node and the slave node is obtained after each delay acquisition time interval.

[0107] The primary node is used to perform write operations, while the secondary node is used to perform read operations.

[0108] Optionally, keyword matching can be performed based on the SQL statement in the business request to determine whether the business request is a query request.

[0109] Optionally, the business request can be determined by matching the statements in the preset query request statement library with the SQL statement of the business request.

[0110] Optionally, the replication latency value between the current master node and the slave node can be obtained through historical data from the database.

[0111] Optionally, the current time is inserted into the table on the current master node, and the time inserted by the master node is compared with the current time on the auxiliary node to determine the replication delay value.

[0112] Optionally, the current master node and slave node of the database can be tested. The replication delay value between the current master node and slave node can be obtained by measuring the time it takes for the current master node to complete a transaction, the time it takes for the slave node to receive the transaction, and the time it takes for the slave node to complete the transaction.

[0113] S203: Compare the replication delay value with the preset timeout delay threshold.

[0114] It is understood that the preset timeout delay threshold here can be determined according to the actual situation, and this application embodiment does not impose specific restrictions on it.

[0115] Optionally, after comparing the replication delay value with a preset timeout threshold, the process includes: if the replication delay value is greater than the preset timeout threshold, then routing the service request to the current master node.

[0116] In one possible implementation, the replication delay value between the current master node and the slave node is checked to see if it exceeds a preset timeout threshold. If it does, the SQL is directly routed to the current master node.

[0117] Here, this embodiment of the application focuses on write operations, directly routing business requests to the current master node to achieve efficient, fast, and intelligent database access operations.

[0118] S204: If the replication delay value is not greater than the preset timeout delay threshold, then obtain the time of the most recent execution of the data manipulation language for the business request.

[0119] It is understood that the timeout delay threshold here can be determined according to the actual situation, and this application embodiment does not impose specific restrictions on it.

[0120] Optionally, obtain the time of the most recent execution of the Data Manipulation Language (DML) for the business request, including:

[0121] Parse the business request and extract the tables involved in the business request, obtain the current time and record the time when the table operation is completed; record the time when the table operation is completed in a global record variable; obtain the time of the most recent execution of the data manipulation language for the business request based on the global record variable.

[0122] In one possible implementation, for a SELECT SQL query, the SQL statement is parsed, the relevant tables are extracted, and the current time is obtained. Based on the tables involved in the current SELECT SQL, the time when the table last performed a DML operation is obtained from the global record variable map_dml_records. The time difference is obtained by subtracting the time when the last DML operation was performed from the current time.

[0123] S205: Determine the routing method for business requests based on the current time and the time of the most recent execution of the data manipulation language.

[0124] Optionally, the routing method for business requests can be determined based on the current time and the time of the most recent execution of the Data Manipulation Language, including:

[0125] Determine the difference between the current time and the time of the most recent execution of the data manipulation language; if the difference is less than the preset routing read node time threshold, the business request is routed to the current master node; if the difference is not less than the preset routing read node time threshold, the business request is routed to the auxiliary node.

[0126] In one possible implementation, it is determined whether the difference is less than the routing read node time threshold. If it is less than the time threshold, the SELECT SQL is forwarded to the current master node; if it is greater than the routing read node time threshold, the SELECT SQL is forwarded to the auxiliary node.

[0127] Here, the embodiments of this application can determine whether there has been a DML operation on the current business table within the forwarding time threshold based on the difference between the current time and the time of the most recent DML execution. This allows for direct read operations without the need for additional write operations, reducing workload, lowering power consumption, and further improving the efficiency of database access.

[0128] Using the above method, the global table operation record variable is updated only after a successful DML execution. This variable records the DML completion time for that table. When a SELECT statement is received, the global record variable is queried to retrieve the DML completion time for the response table. Therefore, it is only necessary to check if the replication delay exceeds the routing time threshold and forward the data accordingly. In business scenarios requiring read-write consistency, as many query-type SQL statements as possible are routed to read nodes to maximize the utilization of standby physical resources. Under normal conditions, this ensures that data reading delays due to replication and distribution latency will never occur.

[0129] Optionally, if the difference is less than the preset routing read node time threshold, the business request is routed to the current master node, including: if the difference is less than the preset routing read node time threshold, the business request is routed to the current master node; and the table name and operation completion time of the business statement are recorded.

[0130] Here, in this embodiment of the application, after routing the business request to the current master node to perform the write operation, the operation completion time is also recorded so as to process subsequent business requests based on the operation completion time.

[0131] Optionally, the replication latency values ​​of the current master node and auxiliary nodes can be checked periodically, and the preset route read node time threshold can be dynamically adjusted.

[0132] This application provides a database access method. When a database query request is received, the replication delay value between the current master node and the slave node can be automatically obtained based on the latency acquisition time interval. If the replication delay value is not greater than a preset timeout threshold, the method automatically obtains the time of the last DML operation performed on the table involved in the business request (i.e., the time of the last DML execution). This allows it to determine whether any table has undergone DML operations within the forwarding time threshold. Based on the current time and the time of the last DML execution, it determines whether to route the business request to the current master node or the replication node. This ensures read-write consistency in read-write separation scenarios and eliminates the need for manual inspection of each SQL statement. It also eliminates the need for manual judgment based on the business scenario regarding whether data read-write consistency is not required or whether the SQL statement only queries dead data tables. The method automatically and intelligently routes as many query-type SQL statements as possible to the read nodes, maximizing the utilization of read nodes while meeting business read-write consistency requirements, thus improving database access efficiency.

[0133] Optionally, embodiments of this application can also automatically adjust the acquisition frequency of the replication delay value, accordingly, Figure 3 A flowchart illustrating another database access method provided in this application embodiment is shown below. Figure 3 As shown, the method includes:

[0134] S301: Obtain the business request and determine whether the business request is a query request.

[0135] S302: If the business request is a query request, then obtain the current replication delay value, the current time interval, and the previous replication delay value.

[0136] S303: Determine the delay acquisition time interval based on the current replication delay value, the current time interval, and the previous replication delay value.

[0137] Optionally, the delay acquisition time interval is determined based on the current replication delay value, the current time interval, and the previous replication delay value, including:

[0138] The current replication delay value is compared with the previous replication delay value. If the current replication delay value is less than the previous replication delay value, the first delay acquisition time interval is determined to be half of the current time interval. If the current replication delay value is not less than the previous replication delay value, the first delay acquisition time interval is determined to be the sum of the current time interval and a preset additional value. The first delay acquisition time interval is compared with a preset minimum time interval. If the first delay acquisition time interval is less than the preset minimum time interval, the delay acquisition time interval is determined to be the preset minimum time interval. If the first delay acquisition time interval is not less than the preset minimum time interval, the delay acquisition time interval is determined to be the first delay acquisition time interval.

[0139] It is understood that the preset minimum time interval here can be determined according to the actual situation, and the embodiments of this application do not impose specific restrictions on it.

[0140] Specifically, when the replication latency value decreases, the frequency of acquiring the latency value increases rapidly due to the exponential decrease in the interval time. This allows for faster acquisition of the latest latency value because the replication latency value is smaller than the previous one. Therefore, the time threshold for routing read nodes is replaced with the smaller replication latency value acquired in this detection, thereby forwarding more query statements to read nodes. When the replication latency value increases, the interval time increases by a fixed amount, which can quickly reduce the frequency of latency value acquisition, reduce meaningless latency value acquisition, further reduce the power consumption of database access, reduce workload, and improve the efficiency of database access.

[0141] In one possible implementation, the automatic adjustment method for the latency value acquisition frequency is as follows: acquire the current replication latency value and compare it with the previous replication latency value; if the current replication latency value is less than the previous latency value, adjust the time interval for acquiring the replication latency value to 1 / 2 of the current interval value; if the current replication latency value is greater than the previous replication latency value, increase the time interval for acquiring the replication latency value by a fixed N seconds; determine whether the new time interval value is less than the minimum time interval value; if it is less than the minimum time interval value, set the time interval value to the minimum time interval value; after waiting for the time interval value, continue to acquire the next replication latency value.

[0142] Wherein, N is any positive number, which can be determined according to the actual situation, and the embodiments of this application do not impose specific restrictions on it. The minimum time interval value can be determined according to the actual situation, and the embodiments of this application do not impose specific restrictions on it.

[0143] S304: Based on the latency acquisition time interval, obtain the replication latency value between the current master node and the slave node.

[0144] S305: Compare the replication delay value with the preset timeout delay threshold.

[0145] S306: If the replication delay value is not greater than the preset timeout delay threshold, then obtain the time of the most recent execution of the data manipulation language for the business request.

[0146] S307: Determine the routing method for business requests based on the current time and the time of the most recent execution of the data manipulation language.

[0147] The implementation methods of steps S304-S307 are similar to those of steps S202-S205, and will not be described in detail here.

[0148] Here, the embodiments of this application can automatically determine the delay acquisition time interval by using the current replication delay value, the current time interval, and the previous replication delay value, and automatically adjust the frequency of acquiring the delay value, thereby further improving the efficiency of database access.

[0149] Figure 4 This is a schematic diagram of the structure of a database access device provided in an embodiment of this application, as shown below. Figure 4As shown, the apparatus in this embodiment includes: a first acquisition module 401, a second acquisition module 402, a first comparison module 403, a third acquisition module 404, and a first determination module 405. The database access device here can be the processor itself, or a chip or integrated circuit that implements the processor's functions. It should be noted that the division of the first acquisition module 401, second acquisition module 402, first comparison module 403, third acquisition module 404, and first determination module 405 is only a logical functional division; physically, they can be integrated or independent.

[0150] The first acquisition module is used to acquire business requests and determine whether the business request is a query request.

[0151] The second acquisition module is used to acquire the replication delay value between the current master node and the auxiliary node based on the delay acquisition time interval if the business request is a query request.

[0152] The first comparison module is used to compare the replication delay value with a preset timeout delay threshold.

[0153] The third acquisition module is used to acquire the time of the most recent execution of the data manipulation language in the business request if the replication delay value is not greater than the preset timeout delay threshold.

[0154] The first determination module is used to determine the routing method for business requests based on the current time and the time of the most recent execution of the data manipulation language.

[0155] Optionally, before the second acquisition module acquires the replication delay value between the current master node and the slave node based on the delay acquisition time interval, the above-mentioned device further includes:

[0156] The fourth acquisition module is used to acquire the current replication delay value, the current time interval, and the previous replication delay value;

[0157] The second determining module is used to determine the delay acquisition time interval based on the current replication delay value, the current time interval, and the previous replication delay value.

[0158] Optionally, the second determining module is specifically used for:

[0159] Compare the current replication delay value with the previous replication delay value;

[0160] If the current replication delay value is less than the previous replication delay value, then the first delay acquisition time interval is determined to be half of the current time interval;

[0161] If the current replication delay value is not less than the previous replication delay value, then the first delay acquisition time interval is determined to be the sum of the current time interval and the preset additional value;

[0162] Compare the first delay acquisition time interval with the preset minimum time interval;

[0163] If the first delayed acquisition time interval is less than the preset minimum time interval, then the delayed acquisition time interval is determined to be the preset minimum time interval;

[0164] If the first delayed acquisition time interval is not less than the preset minimum time interval, then the delayed acquisition time interval is determined to be the first delayed acquisition time interval.

[0165] Optionally, the first determining module is specifically used for:

[0166] Determine the difference between the current time and the time of the most recent execution of the Data Manipulation Language;

[0167] If the difference is less than the preset routing read node time threshold, the business request will be routed to the current master node;

[0168] If the difference is not less than the preset routing read node time threshold, the business request will be routed to the auxiliary node.

[0169] Optionally, the first determining module is also specifically used for:

[0170] If the difference is less than the preset routing read node time threshold, the business request will be routed to the current master node;

[0171] Record the table name and operation completion time of the business statement.

[0172] Optionally, after the first comparison module compares the replication delay value with a preset timeout delay threshold, the above apparatus further includes:

[0173] The processing module is used to route business requests to the current master node if the replication delay value is greater than the preset timeout delay threshold.

[0174] Optionally, the third processing module is specifically used for:

[0175] Parse the business request and extract the tables involved in the business request, obtain the current time and record the time when the table operation is completed;

[0176] Record the time when the table operation is completed in a global record variable;

[0177] The time of the most recent execution of the data manipulation language for the business request is obtained based on the global record variables.

[0178] Figure 5 This is a schematic diagram of the structure of a database access device provided in an embodiment of this application. The database access device can be... Figure 1The processor 102 in the document. The components shown herein, their connections and relationships, and their functions are merely examples and do not limit the implementation of the application described and / or required herein.

[0179] like Figure 5 As shown, the database access device includes a processor 501 and a memory 502. These components are interconnected via different buses and can be mounted on a common motherboard or otherwise installed as needed. The processor 501 can process instructions executed within the database access device, including instructions stored in or on memory for displaying graphical information on external input / output devices (such as display devices coupled to an interface). In other embodiments, multiple processors and / or multiple buses can be used with multiple memories and multiple memory modules, if desired. Figure 5 Take a processor 501 as an example.

[0180] Memory 502, as a non-transitory computer-readable storage medium, can be used to store non-transitory software programs, non-transitory computer-executable programs, and modules, such as the program instructions / modules corresponding to the database access device method in the embodiments of this application (e.g., attached...). Figure 4 As shown, there are a first acquisition module 401, a second acquisition module 402, a first comparison module 403, a third acquisition module 404, and a first determination module 405. The processor 501 executes various functional applications and database access methods by running non-transient software programs, instructions, and modules stored in the memory 502, that is, it implements the database access device method in the above method embodiments.

[0181] The database access device may further include an input device 503 and an output device 504. The processor 501, memory 502, input device 503, and output device 504 can be connected via a bus or other means. Figure 5 Taking the example of a connection between China and Israel via a bus.

[0182] Input device 503 can receive input numerical or character information, and generate key signal inputs related to user settings and function control of the database access device, such as a touch screen, keypad, mouse, or multiple mouse buttons, trackball, joystick, etc. Output device 504 can be an output device such as a display device of the database access device. This display device can include, but is not limited to, liquid crystal displays (LCDs), light-emitting diode (LED) displays, and plasma displays. In some embodiments, the display device can be a touch screen.

[0183] The database access device in this application embodiment can be used to execute the technical solutions in the above-described method embodiments of this application. Its implementation principle and technical effect are similar, and will not be repeated here.

[0184] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, are used to implement the database access method described above.

[0185] This application also provides a computer program product, including a computer program, which, when executed by a processor, is used to implement the database access method described above.

[0186] In the several embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative; for instance, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces, or indirect coupling or communication connection between apparatuses or units, and may be electrical, mechanical, or other forms.

[0187] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0188] Other embodiments of this disclosure will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This application is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this disclosure are indicated by the following claims.

[0189] It should be understood that this disclosure is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from its scope. The scope of this disclosure is limited only by the appended claims.

Claims

1. A database access method, characterized by, The method comprises the following steps: acquiring a service request and judging whether the service request is a query request; if the service request is a query request, acquiring a replication delay value between a current primary node and a secondary node according to a delay acquisition time interval; comparing the replication delay value with a preset timeout delay threshold value; if the replication delay value is greater than the preset timeout delay threshold value, routing the service request to the current primary node; if the replication delay value is not greater than the preset timeout delay threshold value, acquiring a time of a latest data manipulation language execution of the service request; determining a difference value between a current time and the time of the latest data manipulation language execution of the service request; if the difference value is less than a preset routing read node time threshold value, routing the service request to the current primary node, wherein the preset routing read node time threshold value is dynamically set according to the currently acquired replication delay value; if the difference value is not less than the preset routing read node time threshold value, routing the service request to the secondary node; before the step of acquiring the replication delay value between the current primary node and the secondary node according to the delay acquisition time interval, the method further comprises the following steps: acquiring a current replication delay value, a current time interval and a last replication delay value; comparing the current replication delay value with the last replication delay value; if the current replication delay value is less than the last replication delay value, determining a first delay acquisition time interval as one half of the current time interval; if the current replication delay value is not less than the last replication delay value, determining the first delay acquisition time interval as a sum of the current time interval and a preset additional value; comparing the first delay acquisition time interval with a preset minimum time interval; if the first delay acquisition time interval is less than the preset minimum time interval, determining the delay acquisition time interval as the preset minimum time interval; if the first delay acquisition time interval is not less than the preset minimum time interval, determining the delay acquisition time interval as the first delay acquisition time interval.

2. The method of claim 1, wherein, if the difference value is less than the preset routing read node time threshold value, routing the service request to the current primary node, the method further comprises the following steps: if the difference value is less than the preset routing read node time threshold value, routing the service request to the current primary node; recording a table name of the service request and an operation completion time.

3. The method of claim 1, wherein, after the step of comparing the replication delay value with the preset timeout delay threshold value, the method further comprises the following step: if the replication delay value is greater than the preset timeout delay threshold value, routing the service request to the current primary node.

4. The method of claim 1, wherein, the step of acquiring the time of the latest data manipulation language execution of the service request, the method further comprises the following steps: parsing the service request and extracting a table involved in the service request, acquiring a current time and recording a table operation completion time; recording the table operation completion time in a global record variable; acquiring the time of the latest data manipulation language execution of the service request according to the global record variable.

5. A database access apparatus characterized by comprising: The method comprises the following steps: a first acquiring module is configured to acquire a service request and judge whether the service request is a query request; The second obtaining module is configured to, if the service request is a query request, obtain a replication delay value between the current primary node and the auxiliary node according to a delay obtaining time interval. The first comparison module is configured to compare the replication delay value with a preset timeout delay threshold. The third obtaining module is configured to, if the replication delay value is not greater than the preset timeout delay threshold, obtain a time of a latest execution of a data manipulation language of the service request. The first determining module is configured to determine a difference between a current time and the time of the latest execution of the data manipulation language. If the difference is less than a preset routing read node time threshold, the service request is routed to the current primary node, and the preset routing read node time threshold is dynamically set according to the currently obtained replication delay value. If the difference is not less than the preset routing read node time threshold, the service request is routed to the auxiliary node. The first determining module is further configured to: If the replication delay value is greater than the preset timeout delay threshold, the service request is routed to the current primary node. The second obtaining module is specifically configured to: Obtain a current replication delay value, a current time interval and a last replication delay value. Compare the current replication delay value with the last replication delay value. If the current replication delay value is less than the last replication delay value, determine a first delay obtaining time interval as one half of the current time interval. If the current replication delay value is not less than the last replication delay value, determine the first delay obtaining time interval as a sum of the current time interval and a preset additional value. Compare the first delay obtaining time interval with a preset minimum time interval. If the first delay obtaining time interval is less than the preset minimum time interval, determine a delay obtaining time interval as the preset minimum time interval. If the first delay obtaining time interval is not less than the preset minimum time interval, determine the delay obtaining time interval as the first delay obtaining time interval.

6. A database access device, characterized by Comprise: At least one processor; And The memory is connected in communication with the at least one processor; wherein The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the database access method of any one of claims 1 to 4.

7. A computer readable storage medium characterized in that, The computer readable storage medium stores computer execution instructions, and the computer execution instructions are executed by the processor to implement the database access method of any one of claims 1 to 4.

8. A computer program product comprising a computer program, characterized in that, The computer program is executed by the processor to implement the method of any one of claims 1 to 4.

Images