Statement processing method of database system, storage medium and device

Abstract

The application provides a statement processing method of a database system, a storage medium and equipment. The statement processing method comprises the following steps: obtaining a data access statement sent by an application system of the database; determining a read-write attribute of the data access statement by using a statement recognition model, wherein the statement recognition model is obtained by training sample data of the data access statement and the read-write attribute; and sending the obtained data access statement to a corresponding node of the database system according to the read-write attribute of the data access statement, so as to be processed by the corresponding node. By using the pre-trained statement recognition model to determine the read-write attribute of the data access statement, the read-write attribute of the data access statement sent by the application system can be accurately identified. For a database system with little change in application system function, the use of the statement recognition model can help to simplify the program, thereby optimizing the performance.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a statement processing method, storage medium, and device for a database system. Background Technology

[0002] In a read-write split database cluster, specialized middleware or drivers are typically used to distribute SQL (Structured Query Language) statements sent by the application system to the primary or standby node. This is because only the primary node can process SQL statements that request write operations. The standby node, as a redundant backup node of the cluster, provides read-only services. Distributing SQL statement read and write operations can significantly reduce the pressure on the primary node, thereby improving the overall performance of the cluster.

[0003] Determining the read / write attribute of an SQL statement is crucial for achieving read / write separation. Currently, there are typically two methods for determining this attribute. One method involves using an SQL parser. This approach leads to excessively bloated middleware or drivers, and the large parser wastes performance. Furthermore, an external parser cannot properly parse database objects like view functions without performing queries. The other method combines keyword matching with configuration parameters to determine the read / write attribute. Keywords primarily use SQL syntax keywords such as INSERT, UPDATE, and DELETE. Configuration parameters can specifically mark certain SQL statements and table operations as read or write. This method, however, has rudimentary logic and is prone to misjudgment. Summary of the Invention

[0004] One object of the present invention is to provide a statement processing method, storage medium and device for a database system that can simplify the program.

[0005] A further objective of this invention is to facilitate timely updates to the sentence recognition model.

[0006] Specifically, the present invention provides a statement processing method for a database system, comprising:

[0007] Obtain the data access statement sent by the application system of the database;

[0008] The read and write attributes of the data access statement are determined using a statement recognition model, which is trained on sample data of the data access statement and its read and write attributes.

[0009] The obtained data access statement is sent to the corresponding node of the database system according to the read / write attributes of the data access statement, so that the corresponding node can process it.

[0010] Optionally, the database system includes:

[0011] At least one master node, which is used to process data access statements for write attributes;

[0012] At least one backup node is provided, which is used to process data access statements for reading attributes.

[0013] Optionally, the training steps of the statement recognition model include:

[0014] Obtain sample data of data access statements from the application system of the database;

[0015] The data access statements in the sample data are marked as read by default, and the data access statements are sent to the backup node for execution;

[0016] Determine whether the backup node reports an error. If so, determine that the data access statement is a write attribute and correct the statement recognition model. If not, determine that the data access statement is a read attribute and record it in the statement recognition model.

[0017] Optionally, the step of obtaining sample data access statements from the application system of the database includes:

[0018] Trigger all processes of the application system;

[0019] Obtain the logs of the database processing the process;

[0020] Extract the data access statements corresponding to each process from the logs, and use all the extracted data access statements as sample data.

[0021] Optionally, after the step of obtaining sample data access statements from the application system of the database, the following steps are included:

[0022] The data access statements are standardized.

[0023] Optionally, the statement recognition model is configured to identify unrecognizable data access statements as read attributes.

[0024] Optionally, after the step of sending the obtained data access statement to the corresponding node of the database system according to the read / write attributes of the data access statement, the following steps are included:

[0025] Record the error information of the backup node.

[0026] Optionally, after the step of recording the error information of the backup node, the following steps are included:

[0027] Determine whether the count of the error messages exceeds the threshold. If so, output a reminder to update the statement recognition model.

[0028] According to another aspect of the present invention, a machine-readable storage medium is also provided, on which a machine-executable program is stored, which, when executed by a processor, implements the statement processing method described in any of the preceding claims.

[0029] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor implements the statement processing method described in any of the preceding claims when executing the machine-executable program.

[0030] The statement processing method of this invention acquires data access statements sent by the application system of the database, uses a statement recognition model to determine the read / write attributes of the data access statements, and sends the acquired data access statements to the corresponding nodes of the database system for processing. The statement recognition model is pre-trained using sample data of data access statements from the application system. Therefore, the statement recognition model can accurately identify the read / write attributes of the data access statements sent by the application system, and using a pre-trained statement recognition model simplifies the judgment process. Thus, for database systems where the application system's functionality does not change significantly, using a statement recognition model helps simplify the database system's program, thereby optimizing the database system's performance and effectively reducing manual intervention.

[0031] Furthermore, the statement processing method of the present invention enables the statement recognition model to determine unrecognizable data access statements as read attributes and records the error information of the backup node. That is, if the application system undergoes functional changes, causing the statement recognition model to acquire data access statements that have not been trained to recognize, it will determine these unrecognizable data access statements as read attributes and then send them to the backup node for execution. Because the backup node is used to process read attribute data access statements, when the statement recognition model cannot recognize a data access statement that is actually a write attribute, the backup node cannot process the actual write attribute data access statement, and the backup node will report an error. By recording the error information of the backup node, operators can manually query the error information records of the backup node, and can also output reminders when the error information exceeds a threshold, thereby enabling operators to update the statement recognition model in a timely manner, thus ensuring the normal operation of the database system.

[0032] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0033] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings:

[0034] Figure 1 This is a schematic diagram of a database system according to an embodiment of the present invention;

[0035] Figure 2 This is a schematic flowchart of a statement processing method for a database system according to an embodiment of the present invention;

[0036] Figure 3 This is a schematic flowchart illustrating the training steps of a statement recognition model in a statement processing method of a database system according to an embodiment of the present invention.

[0037] Figure 4 This is a schematic flowchart of a statement processing method for a database system according to another embodiment of the present invention;

[0038] Figure 5 This is a schematic flowchart of a statement processing method for a database system according to yet another embodiment of the present invention;

[0039] Figure 6 This is a schematic diagram of a machine-readable storage medium according to an embodiment of the present invention;

[0040] Figure 7 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0041] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0042] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0043] Figure 1 This is a schematic diagram of a database system according to an embodiment of the present invention. Figure 2 This is a schematic flowchart of a statement processing method for a database system according to an embodiment of the present invention.

[0044] Reference Figure 1 As shown, specifically, the database system in this embodiment includes an application system 100, a statement recognition model 200, a master node 300, and a backup node 400. The application system 100 can send data access statements, generally statements composed of SQL (Structured Query Language). Examples include INSERT statements (insert one or more rows into a table), DELETE statements (delete rows from a table), TRUNCATE statements (clear the contents of a table but not its definition), and DROP statements (delete both the contents and definition of a table). The statement recognition model 200 is used to identify the read / write attributes of the data access statements sent by the application system 100. Write attribute data access statements are those that modify data in the database, while read attribute data access statements are those that retrieve data from the database. The master node 300 processes write attribute data access statements. The backup node 400 processes read attribute data access statements.

[0045] Reference Figure 2 As shown, in this embodiment, the statement processing method of the database system generally includes:

[0046] Step S202: Obtain the data access statement sent by the application system of the database;

[0047] Step S204: Use the statement recognition model to determine the read / write attributes of the data access statement;

[0048] Step S206: Based on the read / write attributes of the data access statement, the obtained data access statement is sent to the corresponding node in the database system for processing.

[0049] Specifically, during the operation of the database system, the data access statements sent by the application system 100 are first obtained. Then, the statement recognition model 200 determines the read / write attribute of the data access statement. When the statement recognition model 200 determines that the data access statement is write-oriented, it sends the data access statement to the master node 300 for processing. When the statement recognition model 200 determines that the data access statement is read-oriented, it sends the data access statement to the backup node 400 for processing. Therefore, read-oriented and write-oriented data access statements can be processed by different nodes, reducing processing pressure.

[0050] The statement recognition model 200 is trained on sample data of data access statements and their read / write attributes. Specifically, through pre-training, the statement recognition model 200 stores the attributes of data access statements corresponding to each process of the application system 100, so that during actual operation, the statement recognition model 200 can determine the attributes of the acquired data access statements based on the previous training results.

[0051] In this embodiment, the read / write attributes of data access statements are determined using a pre-trained statement recognition model. Based on these attributes, the acquired data access statements are sent to the corresponding nodes in the database system for processing. Because a large number of data access statements were trained initially, the statement recognition model can accurately identify the read / write attributes of the data access statements sent by the application system.

[0052] Furthermore, using a pre-trained statement recognition model to identify the read and write attributes of data access statements can simplify the judgment process in actual work. Therefore, for database systems whose application system functions do not change much, using a statement recognition model helps to simplify the database system program, thereby optimizing the database system's operating performance and effectively reducing manual intervention.

[0053] It should be noted that a database system can also have multiple master nodes or multiple backup nodes.

[0054] Figure 3 This is a schematic flowchart illustrating the training steps of a statement recognition model in a statement processing method of a database system according to an embodiment of the present invention. The training steps of the statement recognition model include:

[0055] Step S302: Obtain sample data of data access statements from the database application system;

[0056] Step S304: Mark the data access statements in the sample data as read attributes by default, and send the data access statements to the backup node for execution;

[0057] Step S306: Determine if the backup node reports an error. If yes, proceed to step S308; otherwise, proceed to step S310.

[0058] Step S308: Determine that the data access statement is a write attribute and correct the statement recognition model;

[0059] Step S310: Determine that the data access statement is a read attribute and record it in the statement recognition model.

[0060] Step S302 includes: triggering all processes of the application system; obtaining the logs of the database processing flow; extracting the data access statements corresponding to each process from the logs, and using all the extracted data access statements as sample data. Specifically, all processes of the application system 100 can be triggered using automated tools or manually, thus extracting the data access statements corresponding to each process from the logs of the database processing flow, thereby obtaining a sample dataset consisting of the data access statements corresponding to all processes of the application system 100.

[0061] Then, during training, all data access statements in the acquired sample dataset are marked as read statements by default. Thus, the statement recognition model 200 sends these read-marked data access statements to the backup node 400 for execution. Because the backup node 400 can only process read-marked data access statements and cannot process write-marked data access statements, it will report an error when attempting to process a write-marked data access statement.

[0062] Therefore, if the data access statement is correctly labeled, meaning the data access statement is indeed a read attribute, then backup node 400 will not report an error. At this point, the statement recognition model 200 remembers that the corresponding data access statement is a read attribute.

[0063] If the data access statement is incorrectly marked, meaning the data access statement is actually a write attribute, the backup node 400 will report an error because it cannot process it. In this case, the statement recognition model 200 modifies the attribute marking of the corresponding data access statement to a write attribute based on the error message and stores it.

[0064] Once all data access statements in the sample data have been executed, the statement recognition model 200 has stored the corresponding attributes of the data access statements for all processes in the application system 100. During actual use of the database system, the statement recognition model 200 can then determine the attributes of the data access statements.

[0065] Preferably, after obtaining sample data of data access statements from the application system of the database, the process includes: standardizing the data access statements. Specifically, the data access statements are uniformly converted to lowercase or uniformly converted to uppercase.

[0066] Figure 4 This is a schematic flowchart of a statement processing method for a database system according to another embodiment of the present invention. In this embodiment, the statement processing method for the database system generally includes:

[0067] Step S402: Obtain the data access statement sent by the application system of the database;

[0068] Step S404: Use the statement recognition model to determine the read / write attributes of the data access statement;

[0069] Step S406: Based on the read / write attributes of the data access statement, the obtained data access statement is sent to the corresponding node of the database system for processing by the corresponding node.

[0070] Step S408: Record the error information of the backup node.

[0071] In this embodiment, the statement recognition model 200 is configured to classify unrecognizable data access statements as read attributes. Specifically, in some database systems, the application system 100 may undergo functional changes. For application systems 100 that have undergone significant functional changes, some data access statements may appear that the previously trained statement recognition model 200 cannot identify. For the aforementioned data access statements that the current statement recognition model 200 cannot identify, the statement recognition model 200 classifies them all as read attributes.

[0072] In other words, during the operation of the database system, the data access statements sent by the application system 100 are first obtained. Then, the statement recognition model 200 determines the read / write attribute of the data access statements. For data access statements that the statement recognition model 200 can recognize, they are processed normally. For data access statements that the statement recognition model 200 cannot recognize, it determines them to be read-oriented and then sends the data access statements to the backup node 400 for processing.

[0073] Furthermore, if the unrecognized data access statement is indeed a read attribute, then the backup node 400 can process it normally. If the unrecognized data access statement is actually a write attribute, then the backup node 400 cannot process the data access statement that is actually a write attribute, and the backup node 400 will report an error. In this case, the error message of the backup node 400 is recorded.

[0074] In this embodiment, the statement recognition model 200 determines unrecognizable data access statements as read attributes and records the error information of the backup node 400. That is, if the application system 100 undergoes a significant functional change, causing the statement recognition model 200 to acquire data access statements that it has not previously trained to recognize, it will determine these unrecognizable data access statements as read attributes and then send them to the backup node 400 for execution.

[0075] Because the backup node 400 is used to process read-attribute data access statements, if the statement recognition model 200 cannot recognize a data access statement that is actually a write-attribute statement, the backup node 400 cannot process it and will report an error. By recording the error information of the backup node 400, operators can manually query the error information records of the backup node 400, and can also output a reminder when the error information count exceeds a threshold. This allows operators to update the statement recognition model 200 in a timely manner, thereby ensuring the normal operation of the database system.

[0076] Figure 5 This is a schematic flowchart of a statement processing method for a database system according to another embodiment of the present invention. In this embodiment, the statement processing method for the database system generally includes:

[0077] Step S502: Obtain the data access statement sent by the application system of the database;

[0078] Step S504: Use the statement recognition model to determine the read / write attributes of the data access statement;

[0079] Step S506: Based on the read / write attributes of the data access statement, the obtained data access statement is sent to the corresponding node of the database system for processing by the corresponding node.

[0080] Step S508: Record the error information of the backup node;

[0081] Step S510: Determine whether the count of error messages exceeds the threshold. If so, proceed to step S512.

[0082] Step S512: Output a reminder for updating the statement recognition model.

[0083] Specifically, if the statement recognition model 200 cannot recognize a data access statement that is actually a write attribute, then the backup node 400 cannot process the data access statement that is actually a write attribute, and the backup node 400 will report an error. Each time the backup node 400 reports an error, it counts the errors. When the count exceeds a threshold, it outputs a reminder to update the statement recognition model. If the error count does not exceed the threshold, the entire process proceeds normally without additional operations. For example, if the threshold is 3, the process proceeds normally until the number of error messages exceeds 3. When the backup node 400's error message count exceeds 3, it issues a reminder.

[0084] In this embodiment, by recording the error messages of the backup node 400 and outputting a reminder when the count of error messages exceeds a threshold, the operator can update the statement recognition model 200 in a timely manner, thus reducing the operator's workload.

[0085] This application also provides a machine-readable storage medium and a computer device. Figure 6 This is a schematic diagram of a machine-readable storage medium 10 according to an embodiment of the present invention. Figure 7 This is a schematic diagram of a computer device 20 according to an embodiment of the present invention.

[0086] The machine-readable storage medium 10 stores a machine-executable program 11 thereon, which, when executed by a processor, implements the statement processing method of the database system of any of the above embodiments.

[0087] The computer device 20 may include a memory 210, a processor 220, and a machine-executable program 11 stored on the memory 210 and running on the processor 220. When the processor 220 executes the machine-executable program 11, it implements the statement processing method of the database system of any of the above embodiments.

[0088] For the purposes of this embodiment, the machine-readable storage medium 10 can be any means capable of containing, storing, communicating, propagating, or transmitting a program for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable media include: an electrical connection (electronic device) having one or more wires, a portable computer disk drive (magnetic device), random access memory (RAM), read-only memory (ROM), erasable and editable read-only memory (EPROM or flash memory), fiber optic devices, and portable optical disc read-only memory (CDROM). Furthermore, the computer-readable medium 10 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other medium, followed by editing, interpreting, or otherwise processing as necessary, and then stored in a computer memory.

[0089] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods can be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0090] Computer device 20 may include a processor 220 adapted to execute stored instructions and a memory 210 that provides temporary storage space for the operation of said instructions during operation. Processor 220 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. Memory 210 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0091] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1.A statement processing method of a database system, comprising: obtaining a data access statement sent by an application system of the database; determining a read-write attribute of the data access statement using a statement recognition model, the statement recognition model being trained by sample data of the data access statement and its read-write attribute; and sending the obtained data access statement to a corresponding node of the database system according to the read-write attribute of the data access statement, for processing by the corresponding node; wherein the database system comprises: at least one master node for processing data access statements with a write attribute; and at least one backup node for processing data access statements with a read attribute; and the training of the statement recognition model comprises: obtaining sample data of data access statements of the application system of the database; marking the data access statements in the sample data as having a read attribute by default, and sending the data access statements to the backup node for execution; determining whether the backup node has an error, and if so, determining that the data access statement has a write attribute and correcting the statement recognition model, and if not, determining that the data access statement has a read attribute and recording in the statement recognition model. 2.The statement processing method of the database system according to claim 1, wherein the step of obtaining sample data of data access statements of the application system of the database comprises: triggering all processes of the application system; obtaining logs of the database processing the processes; and extracting data access statements corresponding to each of the processes from the logs, and taking all the extracted data access statements as sample data. 3.The statement processing method of the database system according to claim 1, wherein the step of obtaining sample data of data access statements of the application system of the database is followed by: standardizing the data access statements. 4.The statement processing method of the database system according to claim 1, wherein the statement recognition model is configured to determine a data access statement that cannot be recognized as having a read attribute. 5.The statement processing method of the database system according to claim 4, wherein the step of sending the obtained data access statement to the corresponding node of the database system according to the read-write attribute of the data access statement is followed by: recording error information of the backup node. 6.The statement processing method of the database system according to claim 5, wherein the step of recording error information of the backup node is followed by: determining whether the count of the error information exceeds a threshold, and if so, outputting a prompt to update the statement recognition model. 7.A machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements the statement processing method according to any one of claims 1 to 6. 8.A computer device comprising a memory, a processor, and a machine-executable program stored on the memory and running on the processor, and the processor implements the statement processing method according to any one of claims 1 to 6 when executing the machine-executable program. ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​

Images