A database-based access processing method, device, system, and storage medium

Abstract

The present application provides an access processing method, device, system and storage medium based on a database. The method includes: for each backup database, obtaining the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statements corresponding to the session identifier; if it is determined that the data and / or attribute information in the first related table has changed, releasing the existing execution plan corresponding to the existing prepare statement, and respectively unbinding the existing prepare statement from the client and the backup database; based on the update log in the primary database, updating the first related table to obtain a second related table, and replacing the first related table in the existing prepare statement with the second related table; based on the existing prepare statement, establishing new sessions between the client and the backup database respectively, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement, and accesses the second related table of the existing prepare statement according to the new execution plan.

Description

Technical Field

[0001] This application relates to computer technology, and in particular, to an access processing method, device, system, and storage medium based on a database. Background Art

[0002] In the prior art, when a client interacts with a relational database, the client can send a PBE statement to the relational database. Specifically, the PBE statement includes three phases: the prepare phase, the bind phase, and the execute phase. In the prepare phase, the syntax of the Structured Query Language (SQL) statement is mainly parsed and optimized by an optimizer, and after the prepare statement is executed, a specific execution plan is generated and cached in the relational database, so that when the same SQL statement is executed again, the cached execution plan can be directly used to read data from the relational database to improve the query efficiency. In addition, in the bind phase and the execute phase, the placeholders of the corresponding prepare statement are bound with input parameters, and then the pre-compiled statement is executed to perform write and read operations on the relational database.

[0003] In addition, since the write efficiency of the relational database is lower than the read efficiency, and the read frequency between the client and the relational database is higher than the write frequency, the execution of the write statement by the relational database will affect the read performance. Based on this, in practical applications, a proxy middleware is generally introduced, that is, the client establishes a connection with the relational database (i.e., the primary database) and multiple backup databases through the proxy middleware to conduct a session, so as to achieve read-write separation through the proxy middleware, thereby improving the read performance.

[0004] However, since the PBE statement needs to be processed in the same session, when the client connects to the relational database through the proxy middleware for a long time and executes the PBE statement, as the data in the relational database changes, the execution plan generated in the prepare phase may become expired, resulting in a long query time in the bind phase and the execute phase, thereby leading to a relatively low query efficiency and reducing the real-time performance of the client accessing the data in the relational database. Summary of the Invention

[0005] This application provides an access processing method, device, system, and storage medium based on a database to solve the technical problem of low query efficiency caused by changes in the data in the relational database when the client connects to the relational database through the proxy middleware for a long time and executes a query.

[0006] In a first aspect, the present application provides a database-based access processing method, which is applied to a proxy middleware, and the proxy middleware is communicatively connected to a main database and multiple backup databases respectively. The method includes:

[0007] For each backup database, obtain the attribute information of the first related table corresponding to the backup database in the update log of the main database and the existing prepare statements corresponding to the session identifier;

[0008] At preset intervals, determine whether the data and / or attribute information in the first related table has changed; if it is determined that the data and / or attribute information in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement, and unbind the session corresponding to the session identifier between the client and the backup database respectively;

[0009] Based on the update log in the main database, perform an update process on the first related table to obtain a second related table, and replace the first related table in the existing prepare statement with the second related table;

[0010] Based on the existing prepare statement, establish new sessions corresponding to the session identifier between the client and the backup database respectively, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second related table of the existing prepare statement according to the new execution plan.

[0011] In a possible implementation manner, the determining whether the data and / or attribute information in the first related table has changed includes:

[0012] According to the update log of the main database, judge whether the number of rows in the first related table has changed to determine whether the data in the first related table has changed;

[0013] Then the releasing the existing execution plan corresponding to the existing prepare statement if it is determined that the data and / or attribute information in the first related table has changed includes:

[0014] If it is determined that the number of rows in the first related table has changed, release the existing execution plan corresponding to the changed existing prepare statement.

[0015] In a possible implementation manner, the determining whether the data and / or attribute information in the first related table has changed includes:

[0016] Determine the first duration executed during the bind phase and the execute phase corresponding to the existing execution plan obtained by monitoring, and determine whether the first duration is greater than a preset duration, so as to determine whether the attribute information corresponding to the first duration in the first related table has changed;

[0017] If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement, including:

[0018] If it is determined that the first duration is greater than the preset duration, then release the existing execution plan corresponding to the changed existing prepare statement.

[0019] In a possible implementation manner, determining whether the data and / or attribute information in the first related table has changed includes:

[0020] According to the update log of the master database, determine whether the number of invalid data in the first related table is greater than a preset number, so as to determine whether the data in the first related table has changed;

[0021] If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement, including:

[0022] If it is determined that the number of invalid data is greater than the preset number, then release the existing execution plan corresponding to the changed existing prepare statement.

[0023] In a possible implementation manner, determining whether the data and / or attribute information in the first related table has changed includes:

[0024] Determine whether the access volume of the backup database obtained by monitoring is greater than a preset access volume, and / or whether the time difference between the first time of executing the bind phase and the execute phase and the second time of executing the existing prepare statement is greater than a preset time interval, so as to determine whether the attribute information corresponding to the access volume and the time difference in the first related table has changed;

[0025] If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement, including:

[0026] If it is determined that the access volume is greater than the preset access volume and the time difference is greater than the preset time interval, then release the existing execution plan corresponding to the changed existing prepare statement.

[0027] In a possible implementation manner, after updating the first related table based on the update log in the master database to obtain a second related table and replacing the first related table in the existing prepare statement with the second related table, the following steps are further included:

[0028] Forward the optimization instruction sent by the client to the backup database, so that the backup database can clean up the fragmentation information of the second related table according to the optimization instruction.

[0029] In a possible implementation manner, before obtaining the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the update log and session identifier of the master database, the following steps are further included:

[0030] Obtain the first connection request sent by the client, establish a first connection with the client according to the first connection request, and create a first session in the first connection;

[0031] Send a second connection request to the backup database, establish a second connection with the backup database according to the second connection request, and create a second session in the second connection;

[0032] Combine the identifier of the first session and the identifier of the second session into the session identifier, so that the client can send the existing prepare statement to the backup database according to the session identifier.

[0033] In a second aspect, the present application provides an access processing device based on a database, including:

[0034] An acquisition module, configured to obtain, for each backup database, the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the update log and session identifier of the master database;

[0035] A processing module, configured to determine, at preset intervals, whether the data and / or attribute information in the first related table has changed; if it is determined that the data and / or attribute information in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement, and unbind the sessions corresponding to the session identifiers between the client and the backup database respectively;

[0036] The processing module is further configured to update the first related table based on the update log in the master database to obtain a second related table, and replace the first related table in the existing prepare statement with the second related table;

[0037] The processing module is also used to establish new sessions corresponding to the session identifier between the client and the backup database respectively based on the existing prepare statement, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second related table of the existing prepare statement according to the new execution plan.

[0038] In a third aspect, the present application provides a database access processing system, comprising a client, a proxy middleware, a primary database and multiple backup databases; wherein the proxy middleware is used to execute the database-based access processing method described in any one of the first aspects.

[0039] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions. When a processor executes the computer-executable instructions, the database-based access processing method described in any one of the first aspects is implemented.

[0040] The present application provides a database-based access processing method, device, system and storage medium. The method obtains, for each backup database, the attribute information of a first related table in an existing prepare statement corresponding to an update log of the main database and a session identifier corresponding to the backup database; determines at preset intervals whether the data and / or attribute information in the first related table has changed; if it is determined that the data and / or attribute information in the first related table has changed, releases the existing execution plan corresponding to the existing prepare statement, and unbinds the session corresponding to the session identifier between the client and the backup database respectively; based on the update log in the main database, The method comprises the steps of: updating the first related table, obtaining the second related table, and replacing the first related table in the existing prepare statement with the second related table; establishing new sessions corresponding to the session identifier with the client and the backup database based on the existing prepare statement, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accessing the second related table of the existing prepare statement according to the new execution plan, thereby improving the query efficiency of the client on the database, thereby improving the real-time performance of the client accessing the database data. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] To more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the accompanying drawings required for the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are some embodiments of the present application. For those of ordinary skill in the art, without creative efforts, other accompanying drawings can be obtained based on these drawings.

[0042] Figure 1 It is a schematic structural diagram of the first embodiment of a database access processing system provided by an embodiment of the present application;

[0043] Figure 2 It is a schematic flowchart of the first embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0044] Figure 3 It is a schematic flowchart of the second embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0045] Figure 4 It is a schematic flowchart of the third embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0046] Figure 5 It is a schematic flowchart of the fourth embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0047] Figure 6 It is a schematic flowchart of the fifth embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0048] Figure 7 It is a schematic flowchart of the sixth embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0049] Figure 8 It is a schematic flowchart of the seventh embodiment of an access processing method based on a database provided by an embodiment of the present application;

[0050] Figure 9 It is the connection relationship among the proxy middleware, the client, and the database;

[0051] Figure 10 It is a signaling flowchart of a database access processing method provided by an embodiment of the present application;

[0052] Figure 11 It is a schematic structural diagram of the first embodiment of an access processing device based on a database provided by an embodiment of the present application. Detailed implementation manners

[0053] Exemplary embodiments will be described in detail herein, and examples thereof are shown in the accompanying drawings. When the following description refers to the accompanying drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the present application. On the contrary, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims, rather than all embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments in the present application without creative efforts shall fall within the scope of protection of the present application.

[0054] In the prior art, a proxy middleware is used to separate the read and write operations of the client to the database, thereby improving the read performance. However, since the data in the database is constantly changing, the execution plan generated in the prepare phase expires, resulting in low efficiency of database queries in the bind phase and execute phase according to this execution plan, thus reducing the real-time performance of the client accessing the database data through the proxy middleware.

[0055] Based on this, to solve the above technical problems, the technical concept of the present application lies in: how to provide a new database-based access processing method to achieve real-time access of the client to the data in the database.

[0056] Figure 1 The following is a schematic structural diagram of Embodiment 1 of a database access processing system provided by an embodiment of the present application. As Figure 1 shown, the system includes: a client 11, a proxy middleware 12, a primary database 13, and multiple backup databases 14.

[0057] Among them, the proxy middleware 12 is respectively communicatively connected to a primary database 13 and multiple backup databases 14. Each backup database 14 obtains the update log of the primary database 13 and the attribute information of the first related table corresponding to the backup database 14 in the existing prepare statement corresponding to the session identifier. And if the proxy middleware 12 determines that the data and / or attribute information in the first related table has changed, it releases the existing execution plan corresponding to the existing prepare statement, and respectively unbinds the sessions corresponding to the session identifiers between the client and the backup database.

[0058] Those of ordinary skill in the art can understand that all or part of the steps of implementing the above method embodiments can be completed by hardware related to program instructions. The foregoing program can be stored in a computer-readable storage medium. When the program is executed, it executes the steps including the above method embodiments; and the foregoing storage medium includes: various media such as ROM, RAM, magnetic disk, or optical disk that can store program codes.

[0059] It should be noted that Figure 1 This is only a structural diagram of a database access processing system provided by the embodiments of the present application. The embodiments of the present application do not Figure 1 limit the actual forms of various devices included therein, nor do they Figure 1 limit the interaction methods between the devices therein. In the specific application of the solution, it can be set according to actual needs.

[0060] Next, the technical solution of the present application will be described in detail through specific embodiments. It should be noted that the following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in some embodiments.

[0061] Figure 2 This is a schematic flowchart of the first embodiment of a database-based access processing method provided by the embodiments of the present application. As Figure 2 shown, specifically, the method includes:

[0062] S201. For each backup database, obtain the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statements corresponding to the session identifier.

[0063] In this embodiment, for example, for each backup database, the proxy middleware obtains the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statements corresponding to the session identifier at preset intervals.

[0064] S202. Determine whether the data and / or attribute information in the first related table has changed at preset intervals; if it is determined that the data and / or attribute information in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement, and unbind the sessions corresponding to the session identifiers between the client and the backup database respectively.

[0065] In this embodiment, the existing execution plan corresponding to the existing prepare statement is stored in the memory of the database. If it is determined that the data and / or attribute information in the first related table has changed, the proxy middleware sends a prepare statement release instruction to the backup database. After receiving the instruction, the backup database deletes the existing execution plan corresponding to the existing prepare statement stored in the memory. Then the proxy middleware unbinds the sessions corresponding to the session identifiers between the client and the backup database respectively.

[0066] S203. Based on the update log in the primary database, perform an update process on the first related table to obtain a second related table, and replace the first related table in the existing prepare statements with the second related table.

[0067] In this embodiment, the proxy middleware obtains the update log in the primary database and sends the update log to the backup database. The backup database updates the first related table according to the update log, obtains the second related table, and replaces the first related table in the existing prepare statement with the second related table.

[0068] S204. Based on the existing prepare statement, new sessions corresponding to the session identifier are respectively established between the client and the backup database, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second related table of the existing prepare statement according to the new execution plan.

[0069] In this embodiment, based on the existing prepare statement, new sessions corresponding to the session identifier are respectively established between the client and the backup database. Then, the client sends an analysis instruction to the backup database through the proxy middleware to obtain statistical information. The backup database executes the existing prepare statement and generates a parameterized execution plan corresponding to the existing prepare statement according to the statistical information. Then, the client sends a build statement to the database through the proxy middleware to perform parameter binding according to the parameterized execution plan and obtain a new execution plan. The client sends an execute statement to the database through the proxy middleware to access the second related table of the existing prepare statement according to the new execution plan.

[0070] In this embodiment, for each backup database, obtain the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the session identifier; at preset intervals, determine whether the data and / or attribute information in the first related table has changed; if it is determined that the data and / or attribute information in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement, and unbind the sessions corresponding to the session identifiers between the client and the backup database respectively; based on the update log in the primary database, perform an update process on the first related table to obtain a second related table, and replace the first related table in the existing prepare statement with the second related table; based on the existing prepare statement, establish new sessions corresponding to the session identifiers between the client and the backup database respectively, so as to enable the backup database to generate a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and access the second related table of the existing prepare statement according to the new execution plan. Compared with the prior art in which the execution plan generated by the prepare statement expires due to data changes in the database, resulting in low efficiency of the client accessing the data in the database, in this application, at preset intervals, for each backup database, obtain the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the session identifier, and when it is determined that the attribute information has changed, release the existing execution plan corresponding to the existing prepare statement, and unbind the sessions corresponding to the session identifiers between the client and the backup database respectively, and then, based on the existing prepare statement, establish new sessions corresponding to the session identifiers between the client and the backup database respectively, so as to enable the backup database to generate a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and access the second related table of the existing prepare statement according to the new execution plan, thereby improving the efficiency of the client accessing the data in the database.

[0071] Figure 3 FIG. is a schematic flowchart of Embodiment 2 of an access processing method based on a database provided by an embodiment of the present application. On the basis of the above embodiment, as Figure 2 shown, a specific implementation manner of step S202 is:

[0072] S301. According to the update log of the primary database, determine whether the number of rows of the first related table has changed to determine whether the data in the first related table has changed.

[0073] In this embodiment, according to the update log of the main database, obtain the data addition or deletion information related to the first related table in the update log, determine whether the number of rows in the first related table has changed, and further determine whether the data in the first related table has changed.

[0074] S302. If it is determined that the number of rows in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement that has changed.

[0075] In this embodiment, if it is determined that the number of rows in the first related table has changed, the proxy middleware sends a prepare statement release instruction to the backup database. After receiving the prepare statement release instruction, the backup database deletes the existing execution plan corresponding to the prepare statement from the memory.

[0076] In this embodiment, according to the update log of the main database, determine whether the number of rows in the first related table has changed to determine whether the data in the first related table has changed; if it is determined that the number of rows in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement that has changed. In this embodiment, by determining whether the number of rows in the first related table has changed according to the update log of the main database, and if it has changed, releasing the existing execution plan corresponding to the existing prepare statement, the execution plan is updated in a timely manner, and further the data of the client accessing the database in real time is realized.

[0077] Figure 4 It is a schematic flowchart of the third embodiment of a database-based access processing method provided by an embodiment of the present application. On the basis of the above embodiment, as Figure 4 shown, another specific implementation manner of step S202 is:

[0078] S401. Determine the first duration executed based on the bind phase and the execute phase corresponding to the existing execution plan obtained by monitoring, and determine whether the first duration is greater than the preset duration to determine whether the attribute information corresponding to the first duration in the first related table has changed.

[0079] In this embodiment, for example, take the time when the database receives the bind statement and the execute statement as the start time, take the time after the database executes the bind statement and the execute statement as the end time, and take the interval between the start time and the end time as the first duration.

[0080] S402. If it is determined that the first duration is greater than the preset duration, release the existing execution plan corresponding to the existing prepare statement that has changed.

[0081] In this embodiment, if it is determined that the first duration is greater than the preset duration, the proxy middleware sends a release prepare statement instruction to the backup database. After receiving the release prepare statement instruction, the backup database deletes the existing execution plan corresponding to the prepare statement from the memory.

[0082] In this embodiment, the first duration executed based on the bind phase and the execute phase corresponding to the existing execution plan obtained by monitoring is judged, and it is judged whether the first duration is greater than the preset duration to determine whether the attribute information corresponding to the first duration in the first related table has changed; if it is judged that the first duration is greater than the preset duration, the existing execution plan corresponding to the existing prepare statement that has changed is released. According to the present application, by obtaining the first duration executed in the monitored bind phase and execute phase and determining that the first duration is greater than the preset duration, the existing execution plan corresponding to the existing prepare statement that has changed is released, so that the expired execution plan can be released through this judgment, thereby realizing the data for the client to access the database in real time.

[0083] Figure 5 It is a schematic flowchart of Embodiment 4 of a database-based access processing method provided by an embodiment of the present application. On the basis of the above embodiment, as Figure 5 shown, another specific implementation manner of step S202 is:

[0084] S501. According to the update log of the primary database, judge whether the number of invalid data in the first related table is greater than the preset number to determine whether the data in the first related table has changed.

[0085] In this embodiment, according to the update log of the primary database, the number of invalid data in the first related table is obtained, and it is judged whether the number of the invalid data is greater than the preset number.

[0086] S502. If it is judged that the number of invalid data is greater than the preset number, release the existing execution plan corresponding to the existing prepare statement that has changed.

[0087] In this embodiment, if it is determined that the number of invalid data is greater than the preset number, the proxy middleware sends a release prepare statement instruction to the backup database. After receiving the release prepare statement instruction, the backup database deletes the existing execution plan corresponding to the prepare statement from the memory.

[0088] In this embodiment, according to the update log of the main database, it is determined whether the number of invalid data in the first related table is greater than a preset number to determine whether the data in the first related table has changed; if it is determined that the number of invalid data is greater than the preset number, the existing execution plan corresponding to the existing prepare statement that has changed is released. By releasing the existing execution plan corresponding to the existing prepare statement that has changed when determining whether the number of invalid data in the first related table is greater than the preset number, this application realizes releasing the expired prepare statement, thereby improving the execution efficiency of the build stage and the execute stage.

[0089] Figure 6 FIG. is a schematic flowchart of Embodiment 5 of a database-based access processing method provided by an embodiment of the present application. On the basis of the above embodiment, as Figure 6 shown, another specific implementation manner of step S202 is:

[0090] S601. Determine whether the access volume of the backup database obtained by monitoring is greater than a preset access volume, and / or whether the time difference between the first time of executing the bind stage and the execute stage and the second time of executing the existing prepare statement is greater than a preset time interval, to determine whether the attribute information corresponding to the access volume and the time difference in the first related table has changed.

[0091] In this embodiment, the time for the current backup database to execute the bind stage and the execute stage is used as the first time, and the time for executing the existing prepare statement is used as the second time.

[0092] S602. If it is determined that the access volume is greater than the preset access volume and the time difference is greater than the preset time interval, release the existing execution plan corresponding to the existing prepare statement that has changed.

[0093] In this embodiment, for example, if it is determined that the access volume is greater than the preset access volume and the time difference is greater than the preset time interval, the proxy middleware sends a prepare statement release instruction to the backup database. After receiving the prepare statement release instruction, the backup database deletes the existing execution plan corresponding to the prepare statement from the memory.

[0094] In this embodiment, it is determined whether the access volume of the backup database obtained by monitoring is greater than a preset access quantity, and / or whether the time difference between the first time for executing the bind phase and the execute phase and the second time for executing the existing prepare statement is greater than a preset time interval, so as to determine whether the attribute information corresponding to the access volume and the time difference in the first related table has changed; if it is determined that the access volume is greater than the preset access quantity and the time difference is greater than the preset time interval, the existing execution plan corresponding to the existing prepare statement that has changed is released. In this application, by determining that the access volume of the backup database is greater than the preset access quantity and the time difference between the first time for executing the bind phase and the execute phase and the second time for executing the existing prepare statement is greater than the preset time interval, the existing execution plan corresponding to the existing prepare statement that has changed is released, so as to update the execution plan in a timely manner, thereby ensuring that the client can access the data in the database in real time.

[0095] Figure 7 FIG. is a schematic flowchart of Embodiment 6 of a database-based access processing method provided by an embodiment of this application. On the basis of the above embodiment, as Figure 7 shown, after step S203, the method further includes:

[0096] S701. Forward the optimization instruction sent by the client to the backup database, so that the backup database can clean up the fragmentation information of the second related table according to the optimization instruction.

[0097] In this embodiment, for example, the client sends an optimize statement to the backup database through the proxy middleware, and after the backup database executes the optimize statement, the fragmentation information of the second related table is cleaned up.

[0098] In this embodiment, the optimization instruction sent by the client is forwarded to the backup database, so that the backup database can clean up the fragmentation information of the second related table according to the optimization instruction. In this application, by forwarding the optimization instruction sent by the client to the backup database to clean up the fragmentation information of the second related table, the physical space of the related table of the existing prepare statement is sorted out, and file holes and fragmentation are reduced.

[0099] Figure 8 FIG. is a schematic flowchart of Embodiment 7 of a database-based access processing method provided by an embodiment of this application. On the basis of the above embodiment, as Figure 8 shown, before step S201, the method further includes:

[0100] S801. Obtain a first connection request sent by the client, establish a first connection with the client according to the first connection request, and create a first session in the first connection.

[0101] In this embodiment, for example, the proxy middleware obtains the first connection request sent by the client, connects to an idle connection instance in the connection pool of the proxy middleware, and then creates a first session in this connection instance to implement the binding between the client and the proxy middleware, and combines the identifier of the proxy middleware: proxy 1 and the idle connection instance conn2 into key information: proxy 1conn2.

[0102] S802. Send a second connection request to the backup database, establish a second connection with the backup database according to the second connection request, and create a second session in the second connection.

[0103] In this embodiment, for example, the proxy middleware sends the second connection request to the backup database, connects to an idle connection instance in the connection pool of the backup database, and then creates a second session in this idle connection instance to implement the binding between the proxy middleware and the backup database, and combines the identifier information of the backup database: standby database 2 and the idle connection instance conn4 into key value information: standby database 2conn4.

[0104] S803. Combine the identifier of the first session and the identifier of the second session into a session identifier for the client to send an existing prepare statement to the backup database according to the session identifier.

[0105] In this embodiment, for example, combine the key information: proxy 1conn2 and the key value information: standby database 2conn4 into proxy 1conn2: standby database 2conn4, and use this as the session identifier for the client to send an existing prepare statement to the backup database according to the session identifier.

[0106] In this embodiment, for example, the connection relationship among the proxy middleware, the client, and the database in this embodiment is as Figure 9 shown.

[0107] In Figure 9 , the client connects to an idle connection conn2 in the connection pool of the proxy middleware, and the proxy middleware connects to the idle connection conn4 in the primary database, the idle connection conn3 in backup database 1, and the idle connection conn1 in backup database 2 respectively through this idle connection conn2.

[0108] In this embodiment, a first connection request sent by a client is obtained to establish a first connection with the client according to the first connection request and create a first session in the first connection; a second connection request is sent to a backup database to establish a second connection with the backup database according to the second connection request and create a second session in the second connection; the identifier of the first session and the identifier of the second session are combined into a session identifier for the client to send an existing prepare statement to the backup database according to the session identifier. In this application, the client is bound to the proxy middleware to generate key information, and then the proxy middleware is bound to the backup database to generate key value information, and the key information and the key value information are combined into a session identifier for the client to send an existing prepare statement to the backup database according to the session identifier, realizing that the client can select different backup databases for data query, thereby achieving load balancing and improving database access efficiency.

[0109] Figure 10 It is a signaling flowchart of a database access processing method provided by an embodiment of this application. As Figure 10 shown, the method includes:

[0110] S901. The client sends a first connection request to the proxy middleware.

[0111] S902. The proxy middleware sends a second connection request to the backup database according to the first connection request.

[0112] S903. The client sends an existing prepare statement to the proxy middleware.

[0113] S904. The proxy middleware forwards the existing prepare statement to the backup database.

[0114] S905. The backup database sends the attribute information of the first related table and the update log of the primary database to the proxy middleware.

[0115] S906. The proxy middleware determines that the data and / or attribute information of the first related table has changed.

[0116] In this embodiment, every preset time, the proxy middleware receives the attribute information of the first related table sent by the backup database and determines whether the data and / or attribute information of the first related table has changed.

[0117] S907. The proxy middleware sends a prepare statement release instruction and an unbinding request to the backup database.

[0118] In this embodiment, when the proxy middleware determines that the data and / or attribute information of the first related table has changed, it sends a release prepare statement instruction, so that the backup database releases the existing execution plan corresponding to the existing prepare statement, and sends an unbind request to the backup database to unbind from the backup database.

[0119] S908. The proxy middleware sends an unbind request to the client.

[0120] S909. The proxy middleware updates the first related table based on the update log of the primary database, obtains the second related table, and replaces the first related table in the existing prepare statement with the second related table.

[0121] S910. The client sends a first connection request to the proxy middleware.

[0122] In this embodiment, the client re-sends a first connection request to the proxy middleware to bind to the proxy middleware.

[0123] S911. The proxy middleware sends a second connection request to the backup database.

[0124] In this embodiment, the proxy middleware re-sends a second connection request to the backup database to bind to the backup database.

[0125] S912. The client sends the existing prepare statement to the proxy middleware.

[0126] S913. The proxy middleware forwards the existing prepare statement to the backup database.

[0127] S914. The backup database regenerates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement, and accesses the second related table of the existing prepare statement according to the new execution plan.

[0128] In this embodiment, the client sends a first connection request to the proxy middleware; the proxy middleware sends a second connection request to the backup database according to the first connection request; the client sends an existing prepare statement to the proxy middleware; the proxy middleware forwards the existing prepare statement to the backup database; the backup database sends the attribute information of the first related table and the update log of the primary database to the proxy middleware; the proxy middleware determines that the data and / or attribute information of the first related table has changed; the proxy middleware sends a prepare statement release instruction and an unbinding request to the backup database; the proxy middleware sends an unbinding request to the client; the proxy middleware updates the first related table based on the update log of the primary database, obtains a second related table, and replaces the first related table in the existing prepare statement with the second related table; the client sends a first connection request to the proxy middleware; the proxy middleware sends a second connection request to the backup database; the client sends an existing prepare statement to the proxy middleware; the proxy middleware forwards the existing prepare statement to the backup database; the backup database regenerates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement, and accesses the second related table of the existing prepare statement according to the new execution plan. In this embodiment, when it is determined that the data and / or attribute information of the first related table has changed, the connection with the backup database and the client is unbound, and then the backup database updates the first related table based on the update log of the primary database, obtains a second related table, and replaces the first related table in the existing prepare statement with the second related table. After the client reconnects to the proxy middleware and the proxy middleware reconnects to the backup database, the backup database regenerates an execution plan according to the existing prepare statement forwarded by the proxy to achieve fast access to the data in the database by the client through the proxy middleware.

[0129] Figure 11 FIG. 4 is a schematic structural diagram of Embodiment 1 of an access processing device based on a database provided by an embodiment of the present application. As Figure 11 shown, the device includes: an acquisition module 101 and a processing module 102. Among them,

[0130] The acquisition module 101 is configured to obtain, for each backup database, the update log of the primary database and the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the session identifier.

[0131] The processing module 102 is configured to determine whether the data and / or attribute information in the first correlation table has changed every preset time; if it is determined that the data and / or attribute information in the first correlation table has changed, the existing execution plan corresponding to the existing prepare statement is released, and the sessions corresponding to the session identifiers between the client and the backup database are respectively unbound. The processing module 102 is further configured to update the first correlation table based on the update log in the primary database, obtain the second correlation table, and replace the first correlation table in the existing prepare statement with the second correlation table. The processing module 102 is further configured to establish new sessions corresponding to the session identifiers between the client and the backup database respectively based on the existing prepare statement, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second correlation table of the existing prepare statement according to the new execution plan.

[0132] The access processing device based on a database provided in this embodiment can execute the technical solutions shown in the above method embodiments, and its implementation principle and beneficial effects are similar, and will not be elaborated here.

[0133] This embodiment also provides a computer-readable storage medium, in which computer-executable instructions are stored. When the processor executes the computer-executable instructions, the embodiments shown in the above method are implemented, and details will not be repeated here.

[0134] Those of ordinary skill in the art can understand that all or part of the steps of implementing the above method embodiments can be completed by hardware related to program instructions. The foregoing program can be stored in a computer-readable storage medium. When the program is executed, it executes the steps including the above method embodiments; and the foregoing storage medium includes: various media such as ROM, RAM, magnetic disk, or optical disc that can store program codes.

[0135] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements on some or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A database-based access processing method, characterized in that, The method is applied to a proxy middleware, and the proxy middleware is communicatively connected to a main database and multiple backup databases respectively. The method includes: For each backup database, obtain the attribute information of the first related table corresponding to the backup database in the update log of the main database and the existing prepare statements corresponding to the session identifier; At every preset time, determine whether the data and / or attribute information in the first related table has changed; if it is determined that the data and / or attribute information in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement, and separately unbind the session corresponding to the session identifier between the client and the backup database; Based on the update log in the main database, perform an update process on the first related table to obtain a second related table, and replace the first related table in the existing prepare statement with the second related table; Based on the existing prepare statement, establish new sessions corresponding to the session identifier between the client and the backup database respectively, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second related table of the existing prepare statement according to the new execution plan.

2. The method according to claim 1, characterized in that, The determination of whether the data and / or attribute information in the first related table has changed includes: According to the update log of the main database, judge whether the number of rows in the first related table has changed to determine whether the data in the first related table has changed; Then, if it is determined that the data and / or attribute information in the first related table has changed, releasing the existing execution plan corresponding to the existing prepare statement includes: If it is determined that the number of rows in the first related table has changed, release the existing execution plan corresponding to the existing prepare statement that has changed.

3. The method according to claim 1, characterized in that, The determination of whether the data and / or attribute information in the first related table has changed includes: Judge the first duration executed based on the bind phase and execute phase corresponding to the existing execution plan obtained by monitoring, and judge whether the first duration is greater than the preset duration to determine whether the attribute information corresponding to the first duration in the first related table has changed; Then, if it is determined that the data and / or attribute information in the first related table has changed, releasing the existing execution plan corresponding to the existing prepare statement includes: If it is judged that the first duration is greater than the preset duration, release the existing execution plan corresponding to the existing prepare statement that has changed.

4. The method according to claim 1, characterized in that, The determination of whether the data and / or attribute information in the first related table has changed includes: According to the update log of the main database, judge whether the number of invalid data in the first related table is greater than the preset number to determine whether the data in the first related table has changed; If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement, including: If it is determined that the number of invalid data is greater than the preset number, then release the existing execution plan corresponding to the existing prepare statement that has changed.

5. The method according to claim 1, characterized in that, The determination of whether the data and / or attribute information in the first related table has changed includes: Judging whether the access volume of the backup database obtained by monitoring is greater than the preset access volume, and / or whether the time difference between the first time of the bind phase and the execute phase and the second time of executing the existing prepare statement is greater than the preset time interval, so as to determine whether the attribute information corresponding to the access volume and the time difference in the first related table has changed; If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement, including: If it is determined that the access volume is greater than the preset access volume and the time difference is greater than the preset time interval, then release the existing execution plan corresponding to the existing prepare statement that has changed.

6. The method according to any one of claims 1 to 5, characterized in that, After updating the first related table based on the update log in the primary database to obtain a second related table and replacing the first related table in the existing prepare statement with the second related table, the method further includes: Forwarding the optimization instruction sent by the client to the backup database for the backup database to clean up the fragmentation information of the second related table according to the optimization instruction.

7. The method according to any one of claims 1 to 5, characterized in that, Before obtaining the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the update log of the primary database and the session identifier, the method further includes: Obtaining a first connection request sent by the client to establish a first connection with the client according to the first connection request and creating a first session in the first connection; Sending a second connection request to the backup database to establish a second connection with the backup database according to the second connection request and creating a second session in the second connection; Combining the identifier of the first session and the identifier of the second session as the session identifier for the client to send the existing prepare statement to the backup database according to the session identifier.

8. A database-based access processing device, characterized in that, Including: An acquisition module, configured to acquire, for each backup database, the attribute information of the first related table corresponding to the backup database in the existing prepare statement corresponding to the update log of the primary database and the session identifier; A processing module, configured to determine whether the data and / or attribute information in the first related table has changed at preset time intervals; If it is determined that the data and / or attribute information in the first related table has changed, then release the existing execution plan corresponding to the existing prepare statement and unbind the sessions corresponding to the session identifier between the client and the backup database respectively; The processing module is further configured to update the first related table based on the update log in the main database, obtain a second related table, and replace the first related table in the existing prepare statement with the second related table; The processing module is further configured to respectively establish new sessions corresponding to the session identifier with the client and the backup database based on the existing prepare statement, so that the backup database generates a new execution plan corresponding to the existing prepare statement according to the existing prepare statement sent by the client, and accesses the second related table of the existing prepare statement according to the new execution plan.

9. A database access processing system, characterized in that, It includes a client, a proxy middleware, a main database, and multiple backup databases; wherein, the proxy middleware is configured to execute the database-based access processing method according to any one of claims 1-7 above.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, and when the processor executes the computer-executable instructions, the database-based access processing method according to any one of claims 1-7 above is implemented.

Images