A data processing method of a database, a storage medium and an apparatus

Abstract

The application provides a database data processing method, a storage medium and equipment. The database data processing method comprises the following steps: obtaining SQL information; performing analysis on the SQL information to obtain an analysis result; obtaining cache information of a client; judging whether the SQL information contains a shard key corresponding to the cache information according to the analysis result; if yes, processing the SQL information according to the shard key, and sending the processed SQL information to a corresponding node. The database data processing method processes the SQL information according to the shard key, and sends the processed SQL information to the corresponding node, so as to reduce the pressure of a coordination node and improve the performance of a database.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, in particular to a database data processing method, a storage medium and an equipment. BACKGROUND

[0002] With the development of information technology, the access volume of existing systems increases geometrically, and more and more businesses use a distributed manner to reduce the storage and access pressure of a single node.

[0003] The existing distributed data architecture usually uses a combination of multiple nodes, each node being an independent database with separate hardware resources. These nodes are combined to provide an external logical database similar to a centralized database, transparent to the application. However, since each data node only has a part of the data, a coordination node is needed to store cluster meta information, such as shard key information, data shard distribution information on the node, etc.

[0004] As shown in the architecture of Figure 1 When the client (application) accesses the database, it must route the statement through the coordination node, the coordination node needs to send the SQL to the real data node for execution, then get the data returned by the data node for result aggregation, and finally return to the application.

[0005] When the data access volume is large, the pressure on the coordination node will also increase, and the coordination node needs to connect the data node when executing the statement, which will have a connection network transmission loss. SUMMARY

[0006] An object of the present application is to provide a database data processing method, a storage medium and an equipment capable of solving any of the above problems.

[0007] A further object of the present application is to improve the performance of the database.

[0008] Another further object of the present application is to reduce the pressure on the coordination node.

[0009] In particular, the present application provides a database data processing method, the database data processing method comprising:

[0010] obtaining SQL information;

[0011] parsing the SQL information to obtain a parsing result;

[0012] obtaining cache information of the client;

[0013] determining whether the SQL information contains a shard key corresponding to the cache information according to the parsing result;

[0014] If yes, the SQL information is processed according to the shard key, and the processed SQL information is sent to the corresponding node.

[0015] Optionally, the step of processing the SQL information according to the shard key and sending the processed SQL information to the corresponding node comprises:

[0016] According to the shard key, the working node involved by the SQL information is obtained;

[0017] It is judged whether the SQL information involves multiple working nodes;

[0018] If yes, the SQL information is sent to the coordination node;

[0019] If no, the SQL information is rewritten, and the rewritten SQL information is sent to the single working node involved by the SQL information.

[0020] Optionally, the step of rewriting the SQL information comprises:

[0021] The logical table in the SQL information is rewritten into the corresponding real table.

[0022] Optionally, the cache information is configured to be updated according to the meta information of the coordination node.

[0023] Optionally, the step of updating the cache information according to the meta information of the coordination node comprises:

[0024] The meta information is queried at intervals, and the cache information is updated according to the query result.

[0025] Optionally, the step of updating the cache information according to the meta information of the coordination node further comprises:

[0026] The meta information change signal is obtained;

[0027] The cache information is updated.

[0028] Optionally, after the step of judging whether the SQL information contains the shard key corresponding to the cache information according to the parsing result, the method further comprises:

[0029] If the SQL information does not contain the shard key corresponding to the cache information, the SQL information is sent to the coordination node.

[0030] Optionally, the parsing comprises lexical analysis and syntax analysis, the lexical analysis divides the SQL information into multiple words, the syntax analysis analyzes the semantics in the words, and the parsing result comprises table name, field information and query condition information.

[0031] According to another aspect of the present application, there is also provided a machine readable storage medium having stored thereon a machine executable program which, when executed by a processor, implements any of the above database data processing methods.

[0032] According to another aspect of the present application, there is also provided 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 any of the above database data processing methods when executing the machine executable program.

[0033] The database data processing method of the present application comprises obtaining SQL information; parsing the SQL information to obtain a parsing result; obtaining cache information of a client; judging whether the SQL information contains a shard key corresponding to the cache information according to the parsing result; if yes, processing the SQL information according to the shard key, and sending the processed SQL information to a corresponding node. The database data processing method of the present application directly sends the SQL information related to a single node to a data node, so as to reduce the pressure on a coordination node and improve the performance of a database.

[0034] Further, the cache information of the database data processing method of the present application is also configured to be updated according to meta information of the coordination node. The cache information updating comprises two modes. Querying the meta information at a fixed interval and updating the cache information according to the query result; and obtaining a meta information change signal and updating the cache information. The database data processing method of the present application updates the cache information through the above two modes, so as to ensure the accuracy of SQL data processing.

[0035] The above and other objects, advantages and features of the present application will become more apparent from the following detailed description of some embodiments thereof, when taken in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF DRAWINGS

[0036] Some embodiments of the present application will be described in detail with reference to the drawings, wherein the same or like components have the same reference numerals, below. It should be understood by those skilled in the art that the drawings are not necessarily drawn to scale. In the drawings:

[0037] Figure 1 is a distributed cluster architecture diagram in the prior art;

[0038] Figure 2 is a distributed cluster architecture diagram of a database data processing method according to an embodiment of the present application;

[0039] Figure 3 is a schematic flow chart of a database data processing method according to an embodiment of the present application;

[0040] Figure 4 This is a schematic diagram of the data transmission process of a database data processing method according to another embodiment of the present invention;

[0041] Figure 5 This is a schematic flowchart of a database data processing method according to yet another embodiment of the present invention;

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

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

[0044] Figure 2 This is a distributed cluster architecture diagram of a database data processing method according to an embodiment of the present invention. Figure 2 As shown, the distributed cluster architecture in this embodiment includes a client 100, a coordinating node 200, and multiple worker nodes 300. Because the client 100 needs to distribute statements, it needs to cache the corresponding table, sharding key, sharding algorithm, and other information. The coordinating node 200 queries the corresponding information, and the client 100 organizes the cache in the corresponding format. Because the tables are dynamically changing, the cached information needs to be dynamically updated.

[0045] Figure 3 This is a schematic flowchart of a database data processing method according to an embodiment of the present invention. The data processing method of this embodiment includes:

[0046] Step S202: Obtain SQL information.

[0047] Step S204: Parse the SQL information to obtain the parsing results. Parsing includes lexical analysis and syntactic analysis. Lexical analysis segments the SQL information into multiple words, while syntactic analysis analyzes the semantics of these words. The parsing results may include table names, field information, query condition information, etc.

[0048] Step S206: Obtain the client's cache information.

[0049] Step S208: Determine whether the SQL information contains a sharding key corresponding to the cached information based on the parsing result. If yes, proceed to step S210. If no, proceed to step S212.

[0050] Step S210: Process the SQL information according to the sharding key and send the processed SQL information to the corresponding node.

[0051] Step S212: Send the SQL information to the coordination node.

[0052] Figure 4 The data transmission process is a data processing method of a database according to still another embodiment of the present application. In the prior art, the client 100 sends a SQL statement containing a shard key: select * from test where t1 = 1 to the coordination node 200, that is, queries all fields of data of the field t1 = 1 in the table test, and the coordination node 200 executes and sends a statement: select * from test_0001 where t1 = 1, that is, queries all fields of data of the field t1 = 1 in the table test_0001, to the corresponding worker node 300. In the data processing method of the database of the present embodiment, the client 100 directly sends a SQL statement containing a shard key: select * from test_0001 t1 = 1 to the corresponding worker node 300, that is, queries all fields of data of the field t1 = 1 in the table test.

[0053] After the client (application, driver) obtains the SQL information, the client first parses the SQL information. The parsing includes syntax parsing and lexical parsing. The statement is first divided into multiple words through lexical parsing, and the semantics thereof is analyzed through syntax parsing to parse the table name, field information, query condition information and the like. Then, the corresponding shard key of the table is queried in the cache information, and it is checked whether the corresponding shard key is contained in the SQL information. The data processing method of the database of the present embodiment directly sends the SQL information involving a single node to the data node to reduce the pressure of the coordination node and improve the performance of the database.

[0054] The cache information is configured to be updated according to the meta information of the coordination node. The step of updating the cache information according to the meta information of the coordination node includes querying the meta information at a fixed interval and updating the cache information according to the query result. A monitoring thread is established to query the meta information at a fixed interval to update the local cache. If a new table is established within the interval time, the client does not have the cache of the shard information of the corresponding table at this time, and the SQL information is sent to the coordination node for execution. After the cache information is updated, the client can distribute according to the cache information, and the method is simple to implement.

[0055] The step of updating the cache information according to the meta information of the coordination node further includes acquiring a meta information change signal and updating the cache information. When the meta information of the coordination node changes, the server notifies the client that the cache information has been changed, so that the update of the local cache information is performed. The method can perceive the change of the cache information in real time without actively and frequently interacting with the database.

[0056] Figure 5is a schematic flow chart of a data processing method of a database according to an embodiment of the present application. The step of processing SQL information according to a shard key and sending the processed SQL information to a corresponding node in the data processing method of the embodiment includes:

[0057] In step S302, a worker node involved by the SQL information is obtained according to the shard key.

[0058] In step S304, it is judged whether the SQL information involves multiple worker nodes. If yes, step S306 is executed. If no, step S308 is executed.

[0059] In step S306, the SQL information is sent to a coordination node.

[0060] In step S308, the SQL information is rewritten, and the rewritten SQL information is sent to a single worker node involved. Since only statement distribution involving a single worker node is designed, only rewriting of a table name is designed. The step of rewriting the SQL information includes rewriting a logical table in the SQL information into a corresponding real table.

[0061] If no corresponding shard key is contained, full database routing is needed, and the SQL information is sent to a coordination node for execution. If the shard key is contained, a corresponding shard algorithm is obtained, and a server to which the SQL is routed is obtained according to a shard key condition. If multiple servers are involved, the SQL information is sent to a coordination node. If a single server is sent, the SQL information needs to be rewritten according to a rule, and then the rewritten SQL information is sent to a single node.

[0062] The embodiment further provides a machine readable storage medium and a computer device. Figure 6 is a schematic diagram of a machine readable storage medium according to an embodiment of the present application, Figure 7 is a schematic diagram of a computer device according to an embodiment of the present application.

[0063] The machine readable storage medium 40 has a machine executable program 41 stored thereon, and the machine executable program 41 is executed by a processor to implement the data processing method of the database of any of the above embodiments.

[0064] The computer device 50 can include a memory 520, a processor 510, and a machine executable program 41 stored on the memory 520 and running on the processor 510, and the processor 510 executes the machine executable program 41 to implement the data processing method of the database of any of the above embodiments.

[0065] It is to be appreciated that the logical and / or steps represented in the flow diagrams, or otherwise described herein, can be considered as a list of executable instructions for implementing logic functions, and can be embodied in any machine-readable storage medium for use by an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions, or in conjunction with which the instructions can be executed.

[0066] For the description of the present embodiments, the machine-readable storage medium 40 can be any device that can contain, store, communicate, propagate or transport the program for use by or in connection with the instruction execution system, apparatus or device. More specific examples (a non-exhaustive list) of the computer readable medium include the following: an electrical connection having one or more wires (electrical devices), a portable computer diskette (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). In addition, the computer readable medium 40 can even be paper or another suitable medium upon which the program can be printed, as the program can be electronically captured, for example by optically scanning the paper or other suitable medium, then electronically converted into a form that can be further processed by the computer system, and then stored in the computer memory.

[0067] It should be understood that parts of the present application can be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0068] The computer device 50 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smart phone. In some examples, the computer device 50 can be a cloud computing node. The computer device 50 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and the like, that perform particular tasks or implement particular abstract data types. The computer device 50 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules can be located in local or remote computer system storage media including memory storage devices.

[0069] The computer device 50 can include a processor 510 adapted to execute instructions stored in memory 520, which in turn can provide instructions for the operations of the processor 510 during operation. The processor 510 can be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 520 can include random access memory (RAM), read only memory, flash memory, or any other suitable memory systems.

[0070] The processor 510 can also be linked through the system interconnect to a display interface adapted to connect the computer device 50 to a display device. The display device can include a display screen that is a built-in component of the computer device 50. The display device can also include a computer monitor, television, or projector that is externally connected to the computer device 50, among others. Further, a network interface controller (NIC) can be adapted to connect the computer device 50 to a network through the system interconnect. In some embodiments, the NIC can use any suitable interface or protocol, such as Internet Small Computer Systems Interface, among others, to transfer data. The network can be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, among others. Remote devices can be connected to the computing device through the network.

[0071] The flow diagrams provided herein are not intended to indicate that the operations of the methods are to be executed in any particular order, or that all of the operations of a method are re quired, in all cases. Further, the method can include additional operations. Additional changes can be made to the above described methods within the scope of the technology provided herein.

[0072] To this end, those skilled in the art will realize that the foregoing preferred embodiments and examples are illustrative only and not in a limiting sense, for they are intended to teach those skilled in the art the best way to make and use the application. Thus, although the application has been described in some detail to provide a thorough understanding thereof, it will be apparent that certain mino r changes can be made in the details, especially in matters of procedure and the like, without departing from the spirit of the application. Therefore, the scope of the application should be measured by reference to the appended claims rather than by reference to the foregoing description of the preferred embodiments and examples.

Claims

1. A data processing method of a database, comprising: obtaining SQL information; parsing the SQL information to obtain a parsing result; obtaining cache information of a client; judging whether the SQL information contains a shard key corresponding to the cache information according to the parsing result; if not, sending the SQL information to a coordination node; if yes, obtaining a worker node involved by the SQL information according to the shard key; judging whether the SQL information involves multiple worker nodes; if yes, sending the SQL information to the coordination node; if not, rewriting the SQL information, and sending the rewritten SQL information to a single worker node involved by the SQL information.

2. The data processing method of a database according to claim 1, wherein, The step of rewriting the SQL information comprises: rewriting a logical table in the SQL information into a corresponding real table.

3. The data processing method of a database according to claim 1, wherein, The cache information is configured to be updated according to meta information of the coordination node.

4. The data processing method of a database according to claim 3, wherein, The step of updating the cache information according to the meta information of the coordination node comprises: inquiring the meta information at intervals of a fixed time, and updating the cache information according to an inquiry result.

5. The data processing method of a database according to claim 3, wherein, The step of updating the cache information according to the meta information of the coordination node further comprises: obtaining a meta information change signal; updating the cache information.

6. The data processing method of a database according to Claim 1, wherein, The parsing comprises lexical parsing and syntax parsing, the lexical parsing divides the SQL information into multiple words, the syntax parsing analyzes semantics in the words, and the parsing result comprises table names, field information and query condition information. 7.A machine readable storage medium, having stored thereon a machine executable program, which, when executed by a processor, implements the data processing method of the database 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 data processing method of the database according to any one of claims 1 to 6 when executing the machine executable program.

Images