HK40097161A - A method for dynamically splitting read/write database - Google Patents

Description

A method for dynamic read-write separation in databases

Technical Field

This invention relates to the field of database technology, and more specifically, to a method for dynamic read-write separation of databases.

Background Technology

In modern application systems, the database, as the storage medium for data, is a crucial support system. Applications (also known as application services) need to access the database to read or modify data. To improve the database's disaster recovery capabilities, reliability, and recoverability, the same database is typically deployed across multiple servers, forming a database cluster composed of multiple nodes.

As shown in Figure 1, a typical database cluster will deploy one Primary node and one or more Slave nodes. That is:

The primary database node, also known as the "Read-Write (RW) node," can be accessed by both read and write operations. Database clients or applications can access this node for both read and write purposes; there is typically only one primary database node.

A slave node, also known as a "read-only RO node," is a node that can only be accessed by reads. Database clients or applications can only read from this node and cannot write to it. There is usually one or more slave nodes.

Read-write access, also known as RW (Read-Write) access, includes inserting, updating, and deleting data, as well as querying data. Read-only access, also known as RO (Read-Only) access, refers to querying data without modifying it.

The slave database is a mirror of the master database. The slave database obtains data from the master database in real time or near real time to ensure that the data in the slave database is consistent with that in the master database (there may be some delay in data consistency due to network and other reasons).

When deploying a database cluster, applications can access different nodes in the cluster using a read-write separation approach. If a certain business function of the application (such as changing a customer's balance) requires writing data, it should connect to the Primary database for access; if a certain business function of the application (such as querying a customer's balance) only requires querying data, it should connect to the Slaver database for access.

In practice, read-only queries often constitute the majority of database accesses by applications. Therefore, read-write separation can be used to allow different business logic to access different database nodes, achieving the following:

The primary database handles write operations and performs few or no read-only operations, thus reducing the load on the primary database.

The slave database handles read-only operations to reduce the access load on the primary database. Furthermore, by deploying multiple slave databases and distributing read-only operations across different slave databases, the access load on each slave database node can be reduced.

Existing database read/write separation solutions have the following two shortcomings:

On the one hand, the read/write separation method is fixed during the development phase and is difficult to change dynamically at runtime. Existing read/write separation solutions, whether by identifying keywords in the SQL text, adding hints to the SQL text, or distinguishing read/write for each business function by naming or other means in the code, all have a drawback: their read/write separation mechanism is fixed during the development phase. After the application is deployed and online, it is difficult to dynamically change the read/write behavior during runtime (dynamically changing the read/write separation without restarting the application) and make it effective in real time.

Example of a scenario requiring dynamic changes to read/write operations: For the function "reading the customer's current balance to determine sufficiency," during development, this function was assumed to have read-only (RO) access to the database. Therefore, the Slave database was permanently used as the data source in the source code. However, after deployment, it was found that: ① due to network factors, there was a delay in data synchronization between the Slave database and the Primary database, and strong real-time data consistency could not be guaranteed; or ② the Slave database node experienced a temporary failure, while the Primary database continued to function normally. In both cases, the data source for this function needs to be dynamically switched from read-only (RO) access to read-write (RW) access. Existing read/write separation solutions cannot support dynamic changes, requiring modification of the source code and redeployment before deployment.

On the other hand, the dynamic distribution of read-only request access among read-only requests is not considered. While existing read/write separation schemes consider the classification of reads and writes, they do not address how read-only requests are distributed. The database cluster should distribute the read request load across multiple slave nodes, thus allocating pressure. Furthermore, this distribution mechanism should be dynamically adaptable.

Summary of the Invention

To overcome the shortcomings of existing technologies, this invention provides a method for dynamic read-write separation of databases, which can support read-write separation of databases and dynamically change the read-write mechanism during database operation, with the adjustment taking effect immediately.

The technical solution adopted by this invention to solve its technical problem is: a method for dynamic read-write separation of a database, wherein the database consists of a primary master database and multiple slave databases, and the method includes the following steps:

Establish a read-write access connection between the application and the Primary master database, and simultaneously establish a read-only access connection between the application and the multiple Slaver slave databases;

When the application starts, it reads and parses the configuration file, and then selects and stores the rules for each data source in sequence.

By exposing an interface to the outside world, the system can receive external calls to modify the data source selection rules and store the received data source selection rules, thereby enabling dynamic changes to the data source selection rules.

Based on business requirements, read the dynamically changed data source selection rules, analyze each rule, and select the data source required for the business.

Furthermore, the data source selection rule can be one or more, and when there are multiple data source selection rules, these multiple data source selection rules are ordered.

Each rule includes database selection criteria and selection results. Database selection criteria include application name, function points, keywords that must appear in the SQL, and keywords that must not appear in the SQL; selection results include database category and database node.

Furthermore, the method of exposing an external interface to receive external calls to modify the data source selection rules and storing the received data source selection rules to dynamically change the data source selection rules includes:

S101, Externally exposed interface;

S102. The client generates a JOSN request according to the add/modify/delete interface specification and calls the corresponding interface;

S103. Parse the JOSN request and verify whether the operation password is consistent with the preset password and whether the content of the JOSN request is valid.

S104. After successful verification, add/modify/delete the data source selection rules according to the content of the JSON request, and finally complete the dynamic update of the data source selection rules and make them effective immediately.

Furthermore, the step of reading the dynamically changed data source selection rules according to business needs, and analyzing each rule to select the data source required for the business, also includes:

The data retrieved from the Slaver database will be placed into the result cache;

When the same SQL query request occurs, the data is retrieved directly from the existing result cache.

Furthermore, the step of reading the dynamically changed data source selection rules according to business needs, and analyzing each rule to select the data source required for the business, also includes:

S201. Obtain the current information of the application;

S202. Perform rule matching, read the content of a data source selection rule in sequence, and perform logical matching with the current information of the application and the selection conditions.

In the matching of steps S203 and S202, if the selection conditions are met, the selection result corresponding to the data source selection rule is obtained; if the selection conditions are not met, step S202 is repeated to read the next data source selection rule and perform rule matching.

S204. Obtain the library category and library node attributes from the selected results to determine the final node;

S205. Based on the library category and the final node, obtain the specific connection of the corresponding library;

S206. If the database type is RW (Read/Write), obtain a data source connection to the Primary database, or obtain a connection from an existing connection pool; through this data source connection, execute the SQL to be executed, and return the execution result to the application.

S207. If the library category is RO (Read-only), it includes:

Use either the entire text of the SQL statement to be executed or the hash value of the SQL statement text as the key to retrieve the corresponding cached value from the result cache; if the value can be retrieved from the result cache, the cache is hit; otherwise, the cache is missed.

If the cache is hit, the value that was hit in the cache is returned directly to the application as the result, without needing to obtain a data source connection or execute SQL; in this step, if the serialized string is used as the value, then deserialization is also required.

If the cache misses, a data source connection is obtained from the slave database, or a connection is obtained from an existing connection pool; the SQL to be executed is executed through the data source connection; if the execution is successful, the SQL execution result is saved to the result cache; regardless of whether the SQL execution result is successful or not, the execution result is returned to the application.

Furthermore, in step S201, the current information includes the current application name, function points, and SQL to be executed.

Furthermore, in step S204, if the library node is specified as "ANY", then one node is selected from multiple nodes under that library category in a random or round-robin manner as the final node.

If the library node is not "ANY" arbitrary but a specifically designated node, then the specified node is directly used as the final node.

This invention also provides a system for dynamic read-write separation of a database, the improvement of which is that the database includes a primary master database and multiple slave databases, and the system includes:

Load the initial rule component, parse the read content, and sequentially put the parsed data source selection rules into the rule chain storage component;

The rule chain storage component is used to store the rules for selecting data sources;

The dynamic adjustment rule component exposes an interface to receive external calls and puts the received data source selection rules into the rule chain storage component, thereby enabling dynamic changes to the data source selection rules.

Select the engine component, read the data source selection rules in the rule chain storage component according to business needs, and select the data source required for the business after analysis;

The RW data source management component enables the management of data source connections or connection pools for the Primary database.

The RO data source management component enables the management of data source connections or connection pools for multiple slave databases.

Furthermore, the dynamic adjustment rule component is linked and synchronized with the central configuration unit to automatically obtain the latest rules from the central configuration unit;

The central configuration unit can be any one of Nacos, Spring Config Server, and ZooKeeper.

Furthermore, the initial loading rule component reads loading rules from the configuration file and forms data source selection rules after parsing;

The rule chain storage component stores data source selection rules in the application and loads these rules into the application's memory when the application starts.

The beneficial effects of the present invention are: the database dynamic read-write separation method and system of the present invention support the read-write separation mode to be dynamically changed at runtime, and the adjustment takes effect immediately; it can also flexibly adjust the dynamic between reads.

Attached Figure Description

Figure 1 is a schematic diagram of the structure of a database cluster in the prior art.

Figure 2 is a schematic diagram of a database dynamic read/write separation system according to the present invention.

Figure 3 is a schematic diagram of the workflow for loading the initial rule component in this invention.

Figure 4 is a schematic diagram of the workflow of the dynamic adjustment rule component in this invention.

Detailed Implementation

The present invention will be further described below with reference to the accompanying drawings and embodiments.

The following will clearly and completely describe the concept, specific structure, and technical effects of the present invention in conjunction with embodiments and accompanying drawings, so as to fully understand the purpose, features, and effects of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, not all of them. Other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are all within the scope of protection of the present invention. Furthermore, all connections/linkages involved in the patent do not simply refer to direct contact between components, but rather to the ability to form a better connection structure by adding or reducing connecting accessories according to specific implementation conditions. The various technical features in this invention can be combined interactively without contradicting each other.

Example 1

Referring to Figure 2, this invention discloses a system for dynamic read-write separation of a database. The database includes a primary master database and multiple slave databases. In this embodiment, there are three slave databases: Slave Database 01, Slave Database 02, and Slave Database 03. The system includes an initial rule loading component, a rule chain storage component, a dynamic rule adjustment component, a selection engine component, an RW data source management component, and an RO data source management component.

The RW (Read-Write) data source management component manages the data source connections or connection pools for the Primary master database (for RW read/write access), including connection configuration information such as IP address, port, username, and password, as well as connection pool management. The RO (Read-Only) data source management component manages the data source connections or connection pools for the Slaver database (for RO read-only access), including connection configuration information such as IP address, port, username, and password, as well as connection pool management. Multiple Slaver databases can be accessed; in this embodiment, three Slaver databases are accessed through the RO data source management component.

Furthermore, the rule chain storage component stores data source selection rules within the application (e.g., in memory). There can be one or more data source selection rules, which are ordered. Storing these rules in memory speeds up rule retrieval. The initial rule loading component parses the read content and sequentially places each parsed data source selection rule into the rule chain storage component. For the dynamically adjusted rule component, it exposes an interface to receive external calls and places the received data source selection rules into the rule chain storage component, thus dynamically changing the data source selection rules. For example, in this embodiment, the dynamically adjusted rule component exposes an HTTP protocol interface. Additionally, the dynamically adjusted rule component can synchronize with a central configuration unit to automatically retrieve the latest rules from the central configuration unit. In this embodiment, the central configuration unit can be any one of Nacos, Spring ConfigServer, or ZooKeeper, or other configuration libraries.

The selection engine component reads the data source selection rules from the rule chain storage component according to business needs, and selects the data source required for the business after analysis. For RO data sources, it is also necessary to determine whether there is a cache, and whether the data obtained from the database query has been put into the cache.

This embodiment also includes a result caching component. This component stores the data already retrieved from the RO data source SQL query as a result cache in the application's memory. Subsequent requests for the same SQL query will directly retrieve the data from the existing result cache, eliminating the need to actually access the Slave database and reducing the load on the Slave database. Therefore, this invention provides a result caching function, which can further reduce database query pressure.

This invention provides a dynamic read/write splitting system for databases, which supports dynamic changes to the read/write splitting mode at runtime, with the changes taking effect immediately. It also allows for flexible dynamic adjustments between read and write operations, for example, by dynamically adjusting the request load of each node based on the load pressure of each slave database in the database cluster or during database cluster expansion/contraction.

Example 2

Referring again to Figure 2, this embodiment discloses a system for dynamic read/write separation of a database. The database includes a primary master database and multiple slave databases. In this embodiment, there are three slave databases: Slave Database 01, Slave Database 02, and Slave Database 03. Similarly, the system for dynamic read/write separation of the database includes an initial rule loading component, a rule chain storage component, a dynamic rule adjustment component, a selection engine component, an RW data source management component, and an RO data source management component.

Based on Embodiment 1, more specific embodiments are provided for the rule chain storage component, the initial rule loading component, the dynamic rule adjustment component, and the selection engine component.

In the aforementioned rule chain storage component, the rule chain refers to the data source selection rules. There can be one or more data source selection rules, and these rules are ordered. When the application starts, it loads the data source selection rules into memory to speed up rule reading. In this invention, the data source selection rules can be dynamically modified, and the modifications take effect immediately.

In this embodiment, the data source selection rule format and content examples are as follows (the rule chain in the table below represents the data source selection rule):

Example rule chains in the table above:

Rule 1: If the current application is AcctMgr and the current function point is specified as QueryAcctInfo (without judging the SQL statement content), then any Slaver node in the RO read-only data source will be selected for access.

Rule 2: If the current application is AcctMgr and the current function point is specified as QueryBalance (without judging the SQL statement content), then the specified Slaver01 database node will be selected from the RO read-only data source for access.

Rule 3: If the current application is AcctMgr and the current function point is specified as AddValue (without judging the SQL statement content), then the Primary master database node of the RW read/write data source will be selected for access.

Rule 4: If the current application is AcctMgr and the SQL statement contains the "select" keyword and does not contain any of the "insert|update|delete" keywords (without judging the function point), then any Slaver node in the RO read-only data source can be selected for access.

Rule 5: If the current application is AcctMgr and the SQL statement contains any of the keywords "insert|update|delete" (regardless of the function point), then the Primary database node of the RW read/write data source will be selected for access.

Rule 6: If the current application is RptQry (without judging function points or SQL statement content), then any Slaver node can be selected from the RO read-only data source for access.

Rule 7: If the current application is StoreMgr and the current function point is specified as InventoryBalance (without judging the SQL statement content), then the Primary master database node of the RW read/write data source will be selected for access.

For example, in this embodiment, the function is "Remaining Inventory Query." Although this function only involves read operations on the database and does not involve data modification, considering the strong real-time requirement for the query results, the Primary master database node of the RW read/write data source is selected for access. This avoids the problem of inaccurate query results from the Slave database due to the data synchronization delay between the Slave database and the Primary master database.

Rule 8: If the current application is StoreMgr and the SQL statement contains the "select" keyword and does not contain any of the "insert|update|delete" keywords (without judging the function point), then any Slaver node in the RO read-only data source can be selected for access.

Rule 9: If the current application is StoreMgr and the SQL statement contains any of the keywords "insert|update|delete" (regardless of the function point), then the Primary database node of the RW read/write data source will be selected for access.

Referring to Figure 3, the present invention provides a specific embodiment for the loading initial rule component. In this embodiment, the loading initial rule component reads loading rules from the configuration file and forms data source selection rules after parsing.

The step of loading rules from the configuration file when the application starts is selected:

1. The configuration file contains one or more selection rules stored in an agreed format (stored in an orderly manner); the configuration file can be manually edited and set; or it can be the result saved from the last time the application was run.

2. Typically, when the application starts, the initial rules loading component reads the configuration file and parses the text content of each line in sequence;

Third, the parsed results are sequentially placed into the rule chain storage component.

Furthermore, referring to Figure 4, this invention provides a specific embodiment for the dynamically adjusting rule component. The dynamically adjusting rule component exposes an interface to receive external calls and places the received data source selection rules into a rule chain storage component, thereby dynamically changing the data source selection rules, including:

S101, External interfaces exposed by the dynamic adjustment rule component;

The following is an example of the interface specification for exposing external interfaces, using the HTTP+JSON protocol:

The URL for adding a rule is: http://IP:PORT/addOneRule; the body of the rule addition interface (JSON format) is as follows:

{

"password": "Operation password (must match the preset to avoid accidental operation)",

"addBeforeRuleNo": "1",

"chooseCondition": {

"applicationName": "Application name (letters or numbers)",

"FunctionName": "Function name (letters or numbers)",

"sqlContainKeywords": "Keywords that must appear in the SQL (multiple keywords separated by vertical bars)",

"sqlNotContainKeywords": "Keywords that must not appear in SQL (multiple keywords separated by vertical bars)"

}

"chooseResult": {

"type": "Library type (values: RW or RO)",

"NodeName": "Library node name (if no specific node name is specified, enter ANY or any name from the tender document)",

}

}

The addBeforeRuleNo field specifies which rule should be added before this new rule.

The URL for the rule modification interface is: http://IP:PORT/modifyOneRule. The body content for the rule modification interface (in JSON format) is as follows:

{

"password": "Operation password (must match the preset to avoid accidental operation)",

"RuleNo": "1",

"chooseCondition": {

"applicationName": "Application name (letters or numbers)",

"FunctionName": "Function name (letters or numbers)",

"sqlContainKeywords": "Keywords that must appear in the SQL (multiple keywords separated by vertical bars)",

"sqlNotContainKeywords": "Keywords that must not appear in SQL (multiple keywords separated by vertical bars)"

}

"chooseResult": {

"type": "Library type (values: RW or RO)",

"NodeName": "Library node name (if no specific node name is specified, enter ANY or any name from the tender document)",

}

}

RuleNo specifies which rule to modify, and the JSON content represents the new rule.

The URL for the rule deletion interface is: http://IP:PORT/deleteOneRule. The body of the rule deletion interface (in JSON format) is as follows:

{

"password": "Operation password (must match the preset to avoid accidental operation)",

"RuleNo": "1",

}

RuleNo specifies which rule to delete.

S102. The client generates a JSON request according to the add/modify/delete interface specification, calls the corresponding interface, and transmits the JSON request content to the dynamic adjustment rule component.

S103. After receiving the request, the dynamic adjustment rule component parses the request, verifies whether the operation password is consistent with the preset, and verifies whether the request content is legal.

S104. After the dynamic adjustment rule component is verified, add/modify/delete rules for the data source selection in the rule chain storage component according to the JSON request content; finally, the content of the data source selection rules in the rule chain storage component is dynamically updated and takes effect immediately.

Therefore, through this step, the present invention not only supports dynamic read-write separation, but also allows for flexible dynamic adjustment between reads and writes. Furthermore, the present invention is low-intrusive, requiring no extensive modifications to individual applications. The present invention has broad applicability, is not dependent on specific database products or programming languages, and is not limited to application systems in specific fields, thus possessing broad application value.

This invention provides a specific embodiment of the selection engine component. In business scenarios such as flash sales, a large number of identical query requests occur over a period of time. These requests might involve high-frequency SQL queries for basic information such as the code, name, details, specifications, and price of a flash sale product. While the selection engine can dynamically select data source connections according to rule chains, allowing the slave database to handle these queries in a read-write separation manner, the high frequency of these SQL queries still places a significant load on the slave database. Within the slave database, each SQL query request requires various operations, including parsing the SQL text, syntax and lexical analysis, permission verification, execution plan generation, execution plan selection, I/O to disk data, data filtering, sorting, and table joins, before the final result is obtained and returned to the application.

In response, this invention proposes an improved solution: for the same SQL query request, the data obtained from the previous database query can be cached in the application's memory as a result cache. Subsequent requests for the same SQL query can directly retrieve the data from the existing result cache without actually accessing the slave database, thus reducing the pressure on the slave database.

In this embodiment, the storage structure for the result cache is implemented as follows: Since mainstream programming languages such as C, C++, Java, Python, C#, Go, and JavaScript all support Map structures, a Map structure can be used to store the result cache; each data item in the Map structure includes a Key-Value pair; specifically, it is as follows:

Key: Use the entire SQL statement text as the key (or use the hash value of the SQL statement text as the key);

Value: The result object of the successfully executed SQL statement query is used as Value (or the serialized string of the query result object is used as Value).

Therefore, the improved selection engine component processing flow is as follows:

S201. Obtain the current information of the application; the current information includes the current application name, function points, and SQL to be executed;

S202. Perform rule matching: Read the content of a data source selection rule sequentially from the rule chain storage component, and perform logical matching with the current information of the application and the selection conditions; in this embodiment, the sequence is from smallest to largest.

In the matching of steps S203 and S202, if the selection conditions are met, the selection result corresponding to the data source selection rule is obtained; if the selection conditions are not met, step S202 is repeated to read the next data source selection rule and perform rule matching.

S204. Obtain the library category and library node attributes from the selected results to determine the final node;

In step S204, if the library node is specified as "ANY", then one node is selected from multiple nodes under that library category in a random or round-robin manner as the final node; if the library node is not "ANY" but a specifically specified node, then the specified node is directly used as the final node.

S205. Based on the library category and the final node, RW data source management or RO data source management obtains the specific connection of the corresponding library;

S206. If the database type is RW read/write, obtain the data source connection for the Primary database from the RW data source management, or obtain a connection from an existing connection pool; execute the SQL to be executed through the data source connection, and return the execution result to the application.

S207. If the library category is RO (Read-only), it includes:

Use either the entire text of the SQL statement to be executed or the hash value of the SQL statement text as the key to retrieve the corresponding cached value from the result cache; if the value can be retrieved from the result cache, the cache is hit; otherwise, the cache is missed.

If the cache is hit, the value that was hit in the cache is returned directly to the application as the result, without needing to obtain a data source connection or execute SQL; in this step, if the serialized string is used as the value, then deserialization is also required.

If the cache misses, obtain the Slaver data source connection from the RO data source management or obtain a connection from an existing connection pool; execute the SQL to be executed through the data source connection; if the execution is successful, save the SQL execution result to the result cache; regardless of whether the SQL execution result is successful or not, return the execution result to the application.

Example 3

This invention discloses a method for dynamic read-write separation in a database, wherein the database includes a primary master database and multiple slave databases. The method includes the following steps:

S10. Establish a read-write access connection between the application and the Primary master database, and simultaneously establish a read-only access connection between the application and the multiple Slaver slave databases.

S20. When the application starts, it reads the configuration file and parses it, and then selects and stores the rules for each data source in sequence.

S30. By exposing an interface to the outside world, external calls are received and the received data source selection rules are stored to dynamically change the data source selection rules.

S40. Based on business requirements, read the dynamically changed data source selection rules, analyze each rule, and select the data source required for the business.

In step S10, establishing a read-write access connection between the application and the Primary database, and establishing a read-only access connection between the application and the multiple Slaves, includes managing connection configuration information such as the IP address, port, username, and password for the data source connection. The data source selection rule can be one or more, and when there are multiple rules, they are ordered. Furthermore, when there are multiple data source selection rules, they include multiple rules, and these rules are the same as in Embodiment 2, and will not be described in detail in this embodiment.

Furthermore, in this embodiment, step S30 includes:

S301, External interfaces exposed by the dynamic adjustment rule component;

S302. By calling the corresponding interface according to the add/modify/delete interface specification through the client, the JSON request content is transmitted to the dynamic adjustment rule component;

S303. After receiving the request, the dynamic adjustment rule component parses the request, verifies whether the operation password is consistent with the preset, and verifies whether the request content is legal.

S304. After the dynamic adjustment rule component is verified, add/modify/delete rules for the data source selection in the rule chain storage component according to the JSON request content; finally, the content of the data source selection rules in the rule chain storage component is dynamically updated and takes effect immediately.

Furthermore, in step S40, for the RO data source, it is determined whether a cache exists; the results already obtained from the SQL query of the RO data source are saved as a cache, and when the same SQL query request occurs, the results are directly retrieved from the existing cache. Specifically, step S40 includes the following steps:

S401. Obtain the current information of the application; in step S401, the current information includes the current application name, function points, and SQL to be executed;

S402. Perform rule matching: sequentially read the content of a data source selection rule from the rule chain storage component, and logically match it with the current information of the application and the selection conditions.

In the matching of steps S403 and S402, if the selection conditions are met, the selection result corresponding to the data source selection rule is obtained; if the selection conditions are not met, step S402 is repeated to read the next data source selection rule and perform rule matching.

S404. Obtain the library category and library node attributes from the selection results to determine the final node. In step S404, if the library node is specified as "ANY", then select one node from multiple nodes under the library category in a random or round-robin manner as the final node. If the library node is not "ANY" but a specifically specified node, then directly use the specified node as the final node.

S405. Based on the library category and the final node, RW data source management or RO data source management obtains the specific connection to the corresponding library.

S406. If the database type is RW read/write, obtain the primary database data source connection from the RW data source management, or obtain a connection from an existing connection pool; through this data source connection, execute the SQL to be executed, and return the execution result to the application.

S407. If the library category is RO (Read-only), it includes:

Use either the entire text of the SQL statement to be executed or the hash value of the SQL statement text as the key to retrieve the corresponding cached value from the result cache; if the value can be retrieved from the result cache, the cache is hit; otherwise, the cache is missed.

If the cache is hit, the value that was hit in the cache is returned directly to the application as the result, without needing to obtain a data source connection or execute SQL; in this step, if the serialized string is used as the value, then deserialization is also required.

If the cache misses, obtain the Slaver data source connection from the RO data source management or obtain a connection from an existing connection pool; execute the SQL to be executed through the data source connection; if the execution is successful, save the SQL execution result to the result cache; regardless of whether the SQL execution result is successful or not, return the execution result to the application.

Based on this, the present invention discloses a system and method for dynamic read-write separation of databases. Compared with existing database read-write separation schemes, it has the following characteristics: First, it supports dynamic changes in the read-write separation mode at runtime, with the adjustments taking effect immediately. Second, it not only supports dynamic read-write separation but also allows for flexible dynamic adjustments between reads; for example, it can dynamically adjust the request load of each node in a timely manner based on the load pressure of each slave database in the database cluster or during the expansion/shrinkage of the database cluster structure. Third, the present invention has low invasiveness and requires no extensive modifications to applications. Fourth, it has broad applicability, is not dependent on specific database products or programming languages, and is not limited to application systems in specific fields; it has broad application value. Fifth, it provides a result caching function, which can further reduce database query pressure.

The above is a detailed description of the preferred embodiments of the present invention. However, the present invention is not limited to the embodiments described. Those skilled in the art can make various equivalent modifications or substitutions without departing from the spirit of the present invention. All such equivalent modifications or substitutions are included within the scope defined by the claims of this application.

Claims (5)

1. A method for dynamic read-write separation of a database, characterized in that the database includes a primary master database and multiple slave databases, the method comprising the following steps: Establish a read-write access connection between the application and the Primary master database, and simultaneously establish a read-only access connection between the application and the multiple Slaver slave databases; When the application starts, it reads and parses the configuration file, and then selects and stores the rules for each data source in sequence. By exposing an interface to the outside world, the system can receive external calls to modify the data source selection rules and store the received data source selection rules, thereby enabling dynamic changes to the data source selection rules. Based on business needs, read the dynamically changed data source selection rules, analyze each rule, and select the data source required for the business. The step of reading dynamically changed data source selection rules according to business needs, and analyzing each rule to select the data source required for the business, also includes: The data retrieved from the Slaver database will be placed into the result cache; When the same SQL query request occurs, the data is retrieved directly from the existing result cache; The step of reading dynamically changed data source selection rules according to business needs, and analyzing each rule to select the data source required for the business, also includes: S201. Obtain the current information of the application; S202. Perform rule matching, read the content of a data source selection rule in sequence, and perform logical matching with the current information of the application and the selection conditions. In the matching of steps S203 and S202, if the selection conditions are met, the selection result corresponding to the data source selection rule is obtained; if the selection conditions are not met, step S202 is repeated to read the next data source selection rule and perform rule matching. S204. Obtain the library category and library node attributes from the selected results to determine the final node; S205. Based on the library category and the final node, obtain the specific connection of the corresponding library; S206. If the database type is RW (Read/Write), obtain a data source connection to the Primary database, or obtain a connection from an existing connection pool; through this data source connection, execute the SQL to be executed, and return the execution result to the application. S207. If the library category is RO (Read-only), it includes: Use either the entire text of the SQL statement to be executed or the hash value of the SQL statement text as the key to retrieve the corresponding cached value from the result cache; if the value can be retrieved from the result cache, the cache is hit; otherwise, the cache is missed. If the cache is hit, the value that was hit in the cache is returned directly to the application as the result, without needing to obtain a data source connection or execute SQL; in this step, if the serialized string is used as the value, then deserialization is also required. If the cache misses, a data source connection is obtained from the slave database, or a connection is obtained from an existing connection pool; the SQL to be executed is executed through the data source connection; if the execution is successful, the SQL execution result is saved to the result cache; regardless of whether the SQL execution result is successful or not, the execution result is returned to the application. 2. The method for dynamic read-write separation of a database according to claim 1, characterized in that the data source selection rule is one or more, and when there are multiple data source selection rules, the multiple data source selection rules are ordered; Each rule includes database selection criteria and selection results. Database selection criteria include application name, function points, keywords that must appear in the SQL, and keywords that must not appear in the SQL; selection results include database category and database node. 3. The method for dynamic read-write separation of a database according to claim 1, characterized in that, by exposing an interface to receive external calls to modify the data source selection rules and storing the received data source selection rules, the method for dynamically changing the data source selection rules includes: S101, Externally exposed interface; S102. The client generates a JOSN request according to the add/modify/delete interface specification and calls the corresponding interface; S103. Parse the JOSN request and verify whether the operation password is consistent with the preset password and whether the content of the JOSN request is valid. S104. After successful verification, add/modify/delete the data source selection rules according to the content of the JSON request, and finally complete the dynamic update of the data source selection rules and make them effective immediately. 4. The method for dynamic read-write separation of a database according to claim 1, wherein in step S201, the current information includes the current application name, function points, and SQL to be executed. 5. The method for dynamic read-write separation of a database according to claim 1, characterized in that, in step S204, if the database node is specified as "ANY", then one node is selected from multiple nodes under the database category in a random or round-robin manner as the final node. If the library node is not "ANY" arbitrary but a specifically designated node, then the specified node is directly used as the final node.