Structured query execution method and device, electronic equipment and storage medium

Abstract

The invention provides a structured query execution method and device, electronic equipment and a storage medium. The method comprises the steps that a connection request of a client is received, a communication channel with the client is established, service execution parameters uploaded by the client are obtained through the communication channel, the service execution parameters comprise structured query statements and state parameters, and the state parameters represent whether the client starts a transparent transmission function or not; the unvarnished transmission function represents that the database cluster executes unanalyzed structured query statements; in response to the condition that the state parameter represents that the client starts the transparent transmission function, sending the structured query statement to a database cluster so as to enable the database cluster to execute the structured query statement; and receiving an execution result of the structured query statement sent by the database cluster, and returning the execution result to the client through the communication channel. By means of the method and device, resource occupation caused by database query can be reduced, and the database query speed is increased.

Description

Technical Field

[0001] The present application relates to database technology, and in particular to a method, device, electronic device and storage medium for executing structured queries. Background Art

[0002] With the popularity of e-commerce, more and more users call the database in the server through the client. At present, when the client requests to obtain data in the database, it is necessary to establish a connection between the client and the database, obtain the structured query statement input from the client through the established connection, and then parse the structured query statement, and execute the parsed structured query statement to query the database to obtain the corresponding data in the database. Although this method can query the data requested by the client, as the number of connected clients gradually increases, the computing resources occupied by querying the database also increase, resulting in a decrease in query speed or query failure. Summary of the invention

[0003] The embodiments of the present application provide a method, device, electronic device, computer program product and computer-readable storage medium for executing a structured query, which can reduce the resource occupation caused by querying a database and improve the speed of querying a database.

[0004] The technical solution of the embodiment of the present application is implemented as follows:

[0005] The present invention provides a method for executing a structured query, the method comprising:

[0006] Receive a connection request from a client and establish a communication channel with the client;

[0007] Acquiring the service execution parameters uploaded by the client through the communication channel, wherein the service execution parameters include: a structured query statement and a state parameter, the state parameter indicates whether the client has enabled a transparent transmission function, and the transparent transmission function indicates that the database cluster executes an unparsed structured query statement;

[0008] In response to the state parameter indicating that the client has enabled the transparent transmission function, sending the structured query statement to the database cluster so that the database cluster executes the structured query statement;

[0009] The execution result of the structured query statement sent by the database cluster is received, and the execution result is returned to the client through the communication channel.

[0010] The present application embodiment provides a structured query execution device, the device comprising:

[0011] A channel establishment module, used to receive a connection request from a client and establish a communication channel with the client;

[0012] A parameter acquisition module, used to acquire the service execution parameters uploaded by the client through the communication channel, wherein the service execution parameters include: a structured query statement and a state parameter, wherein the state parameter indicates whether the client has enabled a transparent transmission function, and the transparent transmission function indicates that the database cluster executes an unparsed structured query statement;

[0013] A statement sending module, configured to send the structured query statement to the database cluster in response to the state parameter indicating that the client has enabled the transparent transmission function, so that the database cluster executes the structured query statement;

[0014] The result transmission module is used to receive the execution result of the structured query statement sent by the database cluster, and return the execution result to the client through the communication channel.

[0015] An embodiment of the present application provides an electronic device, the electronic device comprising:

[0016] A memory for storing computer executable instructions;

[0017] The processor is used to implement a structured query execution method provided by an embodiment of the present application when executing the computer executable instructions stored in the memory.

[0018] An embodiment of the present application provides a computer-readable storage medium storing a computer program or computer-executable instructions for implementing a structured query execution method provided in an embodiment of the present application when executed by a processor.

[0019] An embodiment of the present application provides a computer program product, including a computer program or computer executable instructions. When the computer program or computer executable instructions are executed by a processor, a method for executing a structured query provided by an embodiment of the present application is implemented.

[0020] The embodiments of the present application have the following beneficial effects:

[0021] In the case where the prior art database only executes parsed structured query statements, the parsing of structured query statements can be skipped by turning on the transparent transmission function on the client, and the unparsed structured query statements can be executed by the database cluster, thereby reducing the computing resources occupied when querying the database cluster and improving the efficiency of computing resource use. At the same time, since the step of parsing the structured query statement is skipped, the speed of querying the database is improved. Since there is no need to parse the structured query statement, the parsing failure caused by the high complexity of the structured query statement is avoided, and the efficiency of executing the structured query statement is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 It is a schematic diagram of the architecture of the execution system of the structured query provided in the embodiment of the present application;

[0023] Figure 2 It is a schematic diagram of the structure of a server for structured query provided in an embodiment of the present application;

[0024] Figure 3A It is a flowchart of a method for executing a structured query provided in an embodiment of the present application;

[0025] Figures 3B to 3C It is a detailed flowchart of the method for executing structured query provided in the embodiment of the present application;

[0026] Figure 4 A reference diagram of a paging correction and rewriting provided by an embodiment of the present application;

[0027] Figure 5 It is a schematic diagram of a data query framework provided in an embodiment of the present application;

[0028] Figure 6 It is a data query flow chart provided in an embodiment of the present application;

[0029] Figure 7 It is an execution flow chart of parsing SQL statements provided in an embodiment of the present application;

[0030] Figure 8 is an execution flow chart of the transparent transmission mode provided in an embodiment of the present application;

[0031] Fig. 9 This is an architecture diagram of a business direct connection database provided in an embodiment of the present application;

[0032] Fig.10 This is an architectural diagram of the transparent transmission mode provided in an embodiment of the present application. DETAILED DESCRIPTION

[0033] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings. The described embodiments should not be regarded as limiting the present application. All other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of this application.

[0034] In the following description, reference is made to “some embodiments”, which describe a subset of all possible embodiments, but it will be understood that “some embodiments” may be the same subset or different subsets of all possible embodiments and may be combined with each other without conflict.

[0035] Unless otherwise defined, all technical and scientific terms used in the embodiments of the present application have the same meanings as those commonly understood by those skilled in the art. The terms used in the embodiments of the present application are only for the purpose of describing the embodiments of the present application and are not intended to limit the present application.

[0036] Before further describing the embodiments of the present application in detail, the nouns and terms involved in the embodiments of the present application are explained. The nouns and terms involved in the embodiments of the present application are subject to the following interpretations.

[0037] 1) Structured Query Language (SQL): It is a special purpose programming language, a database query and programming language used to access data and query, update and manage database systems.

[0038] 2) Database middleware: Database middleware is a proxy between the application and the database. It serves as a buffer layer between the application and the database, allowing the application or client to access the database through the database middleware instead of communicating directly with the database.

[0039] 3) Communication channel: It is the information channel between the client and the database middleware, used to transfer data between the client and the database middleware.

[0040] 4) Transparent transmission function: The user determines whether to enable this function through the client. After enabling this function, the database middleware will not parse the structured query statements, and the database will directly execute the unparsed structured query statements.

[0041] 5) Database cluster: a cluster consisting of multiple databases.

[0042] 6) Database protocol: Client / server protocol is a request-response protocol based on TCP / IP protocol. The client sends a request to the server, and the server returns a response.

[0043] 7) Load balancing: distribute the business load to multiple database middleware for execution.

[0044] The embodiments of the present application provide a method, apparatus, device, computer-readable storage medium, and computer program product for executing a structured query, which can reduce the resource usage caused by querying a database and improve the speed of querying a database. The following describes an exemplary application of the execution device of the structured query provided by the embodiments of the present application. The device provided by the embodiments of the present application can be implemented as an exemplary application of database middleware in a server.

[0045] See also Figure 1 , Figure 1 It is a schematic diagram of the architecture of the execution system 100 of the structured query provided in the embodiment of the present application, in order to realize an application supporting a structured query, wherein the system 100 includes a server 200, a network 300, a terminal device 400 and a database cluster 500. The terminal (exemplarily showing the terminal 400-1 and the terminal 400-2) is connected to the server 200 via the network 300, and the network 300 can be a wide area network or a local area network, or a combination of the two. The terminal 400-1 and the terminal 400-2 in the embodiment of the present application can be various types of user terminals such as a laptop computer, a tablet computer, a desktop computer, a set-top box, a mobile device (for example, a mobile phone, a portable music player, a personal digital assistant, a dedicated messaging device, a portable gaming device), a smart phone, an intelligent speaker, a smart watch, a smart TV, and a vehicle-mounted terminal.

[0046] Terminal 400-1 and terminal 400-2 are used to display input interfaces for login account and login password on graphical interface 410 (graphical interface 410-1 and graphical interface 410-2 are shown as examples) so that the user can input the login account and login password, and send the login account and login password to server 200, so as to establish a communication channel between the client in terminal 400-1 and terminal 400-2 and the database middleware in server 200 after the login account and login password are verified.

[0047] The business execution parameter input interface is displayed to the user through the graphical interface 410 - 1 and the image interface 410 - 2 , so that the user can input the business execution parameters through the input interface, and the business execution parameters are transmitted to the database middleware through the communication channel.

[0048] Terminal 400 - 1 and terminal 400 - 2 receive the query result corresponding to the structured query statement included in the service execution parameter transmitted by server 200 through network 300 , so as to display the query result to the user.

[0049] The server 200 is used to receive the login account and login password transmitted by the terminal 400-1 and the terminal 400-2 through the network 300, and verify whether the login account and the login password are correct. If it is determined that the login account and the login password are correct, it is verified whether the login account has the right to access the database cluster 500. If it is determined that the login account has the right to access the database cluster 500, it is determined that the verification has passed, and the verification result is returned to the terminal 400-1 and the terminal 400-2, and a communication channel is established between the database middleware in the server 200 and the client in the terminal 400-1 and the terminal 400-2.

[0050] If the database middleware in the server 200 determines that the status parameter in the service execution parameter indicates that the client has enabled the transparent transmission function, the structured query statement in the service execution parameter is directly sent to the database cluster 500 so that the database cluster 500 executes the unparsed service execution parameter.

[0051] If the database middleware in the server 200 determines that the status parameter indicates that the client does not enable the transparent transmission function, the database middleware in the server 200 parses the structured query statement to obtain a parsed structured query statement, so that the database cluster 500 executes the parsed structured query statement.

[0052] The server 200 receives the execution result of the structured query statement sent by the database cluster 500 through the database middleware, and returns the execution result to the clients in the terminals 400 - 1 and 400 - 2 through the communication channel.

[0053] The database cluster 500 stores data such as instant messaging data, live broadcast data, game data, and online shopping data.

[0054] In some embodiments, the server 200 may be an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDN), and big data and artificial intelligence platforms. However, it is not limited to this. The terminal and the server can be directly or indirectly connected via wired or wireless communication, which is not limited in the embodiments of the present application.

[0055] The embodiments of the present application can be implemented through database technology. In short, a database can be regarded as an electronic file cabinet where electronic files are stored. Users can add, query, update, delete, and other operations on the data in the file. A database is a collection of data that is stored together in a certain way, can be shared with multiple users, has as little redundancy as possible, and is independent of the application program. Among them, a database management system is configured in the database.

[0056] A database management system (DBMS) is a computer software system designed for managing databases. It generally has basic functions such as storage, retrieval, security, and backup. Database management systems can be classified according to the database model it supports, such as relational, Extensible Markup Language (XML); or according to the type of computer it supports, such as server clusters, mobile phones; or according to the query language used, such as Structured Query Language (SQL), XQuery; or according to the performance focus, such as maximum scale, maximum operating speed; or other classification methods. Regardless of the classification method used, some DBMS can cross categories, for example, supporting multiple query languages ​​at the same time.

[0057] See also Figure 2 , Figure 2 is a schematic diagram of the structure of the server 200 for structured query provided in an embodiment of the present application, Figure 2 The server 200 shown includes: at least one processor 210, a memory 250, and at least one network interface 220. The various components in the server 200 are coupled together via a bus system 240. It is understood that the bus system 240 is used to achieve connection and communication between these components. In addition to the data bus, the bus system 240 also includes a power bus, a control bus, and a status signal bus. However, for the sake of clarity, the bus system 240 is not described in detail. Figure 2 Various buses are labeled as bus system 240 .

[0058] The processor 210 can be an integrated circuit chip with signal processing capabilities, such as a general-purpose processor, a digital signal processor (DSP), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc., where the general-purpose processor can be a microprocessor or any conventional processor, etc.

[0059] The memory 250 may be removable, non-removable, or a combination thereof. Exemplary hardware devices include solid-state memory, hard disk drives, optical disk drives, etc. The memory 250 may optionally include one or more storage devices that are physically remote from the processor 210.

[0060] The memory 250 includes a volatile memory or a non-volatile memory, and may also include both volatile and non-volatile memories. The non-volatile memory may be a read-only memory (ROM), and the volatile memory may be a random access memory (RAM). The memory 250 described in the embodiment of the present application is intended to include any suitable type of memory.

[0061] In some embodiments, memory 250 can store data to support various operations, examples of which include programs, modules, and data structures, or a subset or superset thereof, as exemplarily described below.

[0062] Operating system 251, including system programs for processing various basic system services and performing hardware-related tasks, such as a framework layer, a core library layer, a driver layer, etc., for implementing various basic services and processing hardware-based tasks;

[0063] A network communication module 252, used to reach other electronic devices via one or more (wired or wireless) network interfaces 220, exemplary network interfaces 220 include: Bluetooth, Wireless Compatibility Certification (WiFi), and Universal Serial Bus (USB), etc.;

[0064] In some embodiments, the device provided in the embodiments of the present application can be implemented in software. Figure 2 The execution device 253 of the structured query stored in the memory 250 is shown, which can be software in the form of a program and a plug-in, etc., including the following software modules: a channel establishment module 2531, a parameter acquisition module 2532, a statement sending module 2533 and a result transmission module 2534. These modules are logical, so they can be arbitrarily combined or further split according to the functions implemented. The functions of each module will be explained below.

[0065] In other embodiments, the device provided in the embodiments of the present application can be implemented in hardware. As an example, the device provided in the embodiments of the present application can be a processor in the form of a hardware decoding processor, which is programmed to execute the execution method of the structured query provided in the embodiments of the present application. For example, the processor in the form of a hardware decoding processor can adopt one or more application specific integrated circuits (Application Specific Integrated Circuit, ASIC), digital signal processors (Digital Signal Processor, DSP), programmable logic devices (Programmable Logic Device, PLD), complex programmable logic devices (Complex Programmable Logic Device, CPLD), field programmable gate arrays (Field-Programmable Gate Array, FPGA) or other electronic components.

[0066] The execution method of the structured query provided in the embodiment of the present application will be explained in combination with the exemplary application and implementation of the server provided in the embodiment of the present application.

[0067] See also Figure 3A , Figure 3A is a flow chart of the execution method of the structured query provided in the embodiment of the present application, which will be combined with Figure 3A The steps shown are explained, Figure 3A The execution subject can be Figure 1 The server 200 shown in the figure may specifically be a database middleware in the server 200 .

[0068] In step 101, a connection request from a client is received, and a communication channel with the client is established.

[0069] See also Figure 3B , Figure 3B The step 101 of "receiving a connection request from a client and establishing a communication channel with the client" can be implemented by the following steps 1011 to 1013, which are described in detail below:

[0070] In step 1011, a connection request from a client is received, and a database protocol carried in the connection request is parsed.

[0071] After receiving the connection request sent by the client, the server can parse out the database protocol carried in the connection request. In the embodiment of the present application, the database protocol can be a client / server protocol, wherein the client / server protocol may include: the client sends a message to the server, and the server returns a message after the connection is closed. Alternatively, the client initiates a request, and after the server responds, the connection is not disconnected, so that the server can continuously send new data to the client. Alternatively, the client sends a request every few seconds to ask the server if there is new information. Alternatively, the client initiates a request, and if the server's data has not changed, then the request is maintained until the server's data has changed, or it will return after a certain time. Alternatively, the server can automatically send updated data or events to the client, but the client must subscribe to these events. Alternatively, the client and the server can send and receive messages to each other at the same time and on the same connection. In actual applications, the database protocol may include one or more of the above-mentioned client / server protocols, and the specific database protocol selection can be set according to actual conditions.

[0072] After parsing the database protocol carried in the connection request, determine whether the parsed database protocol meets the standard database protocol of the server. If it is determined that the standard database protocol is met, execute the subsequent steps. If it is determined that the standard database protocol is not met, reject the connection request sent by the client.

[0073] In step 1012, the account to be verified carried in the connection request is verified.

[0074] The server parses the account to be verified and the password corresponding to the account to be verified carried by the connection request from the connection request. In an embodiment of the present application, the verification process may include, first verifying whether the account to be verified and the password corresponding to the account to be verified are the same as the login account and login password stored in the database for storing the login account and login password in the server. If it is determined that the account to be verified and the password to be verified corresponding to the account to be verified are the same as the stored login account and login password, the account to be verified is authenticated. In an embodiment of the present application, the authentication process may include, when entering the login account, recording the expiration time corresponding to the login account, if it is determined that the time of the login is not up to the expiration time, then determining that the authentication is passed. Or, when entering the login account, recording the number of logins allowed for the login account, if it is determined that the number of logins allowed is not reached during the login, then determining that the authentication is passed. It should be noted that the above is only a method of verifying the account to be verified and the password to be verified and an authentication method provided in the embodiment of the present application, and the specific verification method and authentication method can be set according to the actual situation, which is not limited here.

[0075] If it is determined that the account to be verified and the password corresponding to the account to be verified are different from the stored login account and login password, or if the authentication fails, the connection request of the client is rejected.

[0076] In step 1013, a communication channel with the client is established.

[0077] In response to the parsed database protocol satisfying the standard database protocol and the parameters to be verified (account to be verified and password to be verified) passing the verification, a communication channel is established between the database middleware and the client, wherein the communication channel is a data channel for transmitting data between the client and the database middleware.

[0078] It should be noted that in the embodiment of the present application, the order of step 1011 and step 1012 can be swapped, or step 1011 and step 1012 can be executed at the same time. The specific execution steps can be set according to actual conditions.

[0079] It can be seen from this that the embodiment of the present application requires parameter verification and protocol determination when establishing a communication channel between the client and the database middleware, which solves the technical problem of data leakage and achieves the technical effect of improving data security.

[0080] Continue to see Figure 3A , continuing with step 101 above, in step 102, the business execution parameters uploaded by the client are obtained through the communication channel, wherein the business execution parameters include: structured query statements and status parameters, the status parameters represent whether the client has enabled the transparent transmission function, and the transparent transmission function represents that the database cluster executes unparsed structured query statements.

[0081] After establishing the communication channel, the database middleware can obtain the business execution parameters uploaded by the client through the communication channel. The business execution parameters include: structured query statements and status parameters. The status parameters indicate whether the client has enabled the transparent transmission function. The transparent transmission function indicates that the database cluster executes unparsed structured query statements.

[0082] In the embodiment of the present application, the client may connect to multiple different database middlewares through multiple communication channels, and the service execution parameters may correspond to multiple different services executed by the client. That is, the service execution parameters may include service execution parameters corresponding to multiple different services.

[0083] Therefore, in an embodiment of the present application, the server can distribute multiple different services to multiple database middleware for execution based on the load when the client executes multiple different services, with the goal of evenly distributing the load of multiple different services, so as to achieve load balancing. The principle of distributing the load can be to distribute the load evenly. In actual applications, the load balancing method can be selected according to actual conditions, and is not limited here.

[0084] It can be seen from this that the embodiments of the present application can ensure that each database middleware can run stably through load balancing, and to a certain extent avoid the situation where the database middleware is overloaded.

[0085] In step 103, in response to the state parameter indicating that the client has enabled the transparent transmission function, the structured query statement is sent to the database cluster so that the database cluster executes the structured query statement.

[0086] In response to the state parameter indicating that the client has enabled the transparent transmission function, the database middleware can directly send the structured query statement to the database cluster, so that the database cluster executes the structured query statement.

[0087] It can be seen that by skipping the process of parsing the structured query statement, the parsing failure caused by the high complexity of the structured query statement during the parsing process is avoided, thereby improving the execution efficiency of the structured query statement. At the same time, since there is no need to parse the structured query statement, the computing resources occupied during the query database are reduced, and the performance of the database is improved.

[0088] In the embodiment of the present application, if the state parameter indicates that the client does not enable the transparent transmission function, the database intermediate library can parse and process the structured query statement so that the database cluster executes the structured query statement after the parsing process. Then, the execution result of the structured query statement sent by the database cluster is received, and the execution result of the structured query statement is returned to the client.

[0089] See also Figure 3C , Figure 3C The illustrated “parsing and processing the structured query statement, and receiving the execution result of the structured query statement sent by the database cluster” can be implemented through the following steps 1031 to 1034 .

[0090] In step 1031, the structured query statement is split into multiple words, and context information corresponding to the multiple words is determined.

[0091] In an embodiment of the present application, first, the structured query statement can be split into multiple words based on the lexical analysis of the structured query statement. In an embodiment of the present application, a lexical parser can be used to split the structured query statement into multiple words. For example, the structured query statement is "SELECT A FROM B", and the structured query statement can be split into "SELECT", "A", "FROM" and "B". After that, the context information corresponding to the multiple words can be determined based on the semantic analysis of the structured query statement. In an embodiment of the present application, a syntax parser can be used to determine the context information corresponding to the multiple words. For example, the words of the structured query statement are "SELECT", "A", "FROM" and "B". According to the syntax parser, it can be determined that "A" is the column name and "B" is the table name. After that, according to the structured query statements "SELECT" and "FROM", it can be known that the context information of the words of the structured query statement is to find the data column named "A" from the data table named "B".

[0092] In step 1032, a routing address of the structured query statement is extracted from the context information, wherein the routing address is an address of a database for executing the structured query statement.

[0093] In an embodiment of the present application, in order to ensure that the access node load of the database can be relatively balanced, it is necessary to shard the data in the database, that is, according to the sharding algorithm, split the data in the database into multiple shards. For example, the database includes three data "A", "B" and "C". According to the sharding algorithm, "A" is divided into shard 1, "B" is divided into shard 2, and "C" is divided into shard 3. It should be noted that each shard has a unique sharding key that is different from other shards, so that when querying, data can be queried from the shard corresponding to the sharding key carried by the structured query statement. For example, the sharding key of shard 1 is "tpch01", the sharding key of shard 2 is "tpch02", and the sharding key of shard 3 is "tpch03". Therefore, if the structured query statement carries "tpch02", it can be determined that the query result of the structured query statement is in shard 2.

[0094] In the embodiment of the present application, the sharding algorithm may be a range (RANGE) sharding algorithm, or a hash (HASH) sharding algorithm, etc. The specific sharding method may be set according to actual conditions and will not be described in detail here.

[0095] In an embodiment of the present application, the database middleware can extract the routing address of the structured query statement from the context information, wherein the routing address is the address of the database that executes the structured query statement. That is, the address of the execution result of the structured query statement in the database is extracted from the context information. For example, the context information is to find a data column named "A" from a data table named "B". Therefore, it can be known that the routing address of the context information is the data column "A" in the data table "B". It should be noted that if the routing address includes a sharding key, the sharding of the context information can be determined first according to the sharding key, and then the routing address of the context information can be determined.

[0096] In step 1033, the context information is rewritten into statements executed by the database cluster.

[0097] In the embodiment of the present application, the process of rewriting the context information may be a correctness rewrite with the goal of enabling the database cluster to correctly execute the structured query statement.

[0098] In an embodiment of the present application, optimization rewriting may include: identifier rewriting, column supplementation rewriting, paging correction rewriting, and batch splitting rewriting. Among them, the identifier rewriting can be to rewrite the logical table name in the structured query statement into the corresponding physical shard table name after routing. For example, the logical structured query statement is "SELECT order_id FROM t_orderWHERE order_id=1", wherein the shard key of the logical structured query statement is oeder_id, and order_id=1, so the structured query statement is routed to shard 1, and the rewritten structured query statement is "SELECT order_idFROM t_order1 WHERE order_id=1". It should be noted that the above is only a table name rewriting method provided in an embodiment of the present application, and there may also be identifier rewriting methods such as index name rewriting and Schema name rewriting. The specific identifier rewriting method can be selected according to the actual situation, and is not specifically limited here.

[0099] In the embodiment of the present application, the supplementary column rewrite can be to supplement the column information that has not been input in the structured query statement during the logical query. For example, when querying the "order_id" in the data table "t_order", and arranging the obtained results in descending order according to "user_id". At this time, the structured query statement is "SELECT order_id FROM t_order ORDERBY user_id". Because the query result of the structured query statement only has "order_id" and the obtained "order_id" needs to be arranged according to "user_id", it is necessary to rewrite the structured query statement by supplementing the columns. The rewritten structured query statement can be "SELECT order_id, user_id FROM t_order ORDER BY user_id". It should be noted that the above is only a method of sorting and supplementing columns provided in the embodiment of the present application. There can also be supplementary column rewriting methods such as grouping supplementary columns, aggregated supplementary columns and self-incrementing primary key supplementary columns. The specific supplementary column rewriting method can be selected according to the actual situation and is not specifically limited here.

[0100] In the embodiment of the present application, the paging modification and rewriting may be performed when obtaining paging data from multiple shards. Figure 4 , Figure 4 A paging correction rewrite provided by an embodiment of the present application is shown in FIG. Figure 4 In the example, there are data tables 1 and 2. If you want to get the second and third data in the order of scores after combining the data in the two data tables, the structured query statement you enter can be "SELECT age FROM t_user ORDER BY ageDESC LIMIT 1, 2". Based on this structured query statement, you can determine that Figure 4 Data Table 1(a) and Data Table 2(a) in the table are finally merged into Table A. According to Table A, the query results are 85 and 80, while the correct query results should be 95 and 90. The reason for the query error is that the data selected by the structured query statement is the last two digits of each sub-table, that is, 90 and 80 in Table 1, and 85 and 75 in Table 2. Therefore, the structured query statement should be paginated and rewritten. The rewritten structured query statement is "SELECT age FROM t_user ORDER BY age DESC LIMIT 0, 3". The rewritten structured query statement is executed to obtain the merged table B. According to the merged table B, the correct execution results 95 and 90 are obtained.

[0101] In an embodiment of the present application, batch split rewrite can be a rewrite for processing batch data after splitting the batch data. Take batch insert splitting as an example. When processing a structured query statement for batch insertion, if the inserted data includes multiple fragments, the data needs to be split. For example, the input structured query statement is "INSERT INTO t_order (order_id, xxx) VALUES (1, xxx), (2, xxx), (3, xxx)". If the table "order_id" is divided into two fragments "order_id0" and "order_id1" after fragmentation, and "1, xxx" and "3, xxx" are to be inserted into the fragment "order_id1", and "2, xxx" is to be inserted into "order_id0", if the structured query statement is still executed, "1, xxx", "2, xxx" and "3, xxx" will be inserted into the fragments "order_id0" and "order_id1" at the same time, causing data redundancy. Therefore, it is necessary to perform batch splitting and rewriting of the structured query statements. The rewritten structured query statements are "INSERT INTO t_order1 (order_id, xxx) VALUES (1, xxx), (3, xxx)" and "INSERT INTO t_order0 (order_id, xxx) VALUES (2, xxx)". Of course, in actual applications, there can also be batch splitting and rewriting methods such as IN query splitting. The specific batch splitting and rewriting method can be selected according to the actual situation and is not limited here.

[0102] In step 1034, the execution result of the rewritten context information sent by the database is received, wherein the database is a database in the database cluster corresponding to the routing address.

[0103] The execution result of the rewritten context information sent by the database is received, wherein the database is a database in the database cluster corresponding to the routing address. That is, the execution result of the structured query statement executed by the database is received.

[0104] Continue to see Figure 3A , the description continues with step 103 above. In step 104, the execution result of the structured query statement sent by the database cluster is received, and the execution result is returned to the client through the communication channel.

[0105] In an embodiment of the present application, the database cluster can execute the structured query statement in sequence in time sequence, and then obtain the execution results of multiple structured query statements, and store the obtained execution results of multiple structured query statements in memory, and in response to obtaining all the execution results of the structured query statement, send the execution results of multiple structured query statements stored in memory to the database middleware. For example, in the process of executing a structured query statement once, the database cluster generates 10 execution results in sequence in time sequence. After the first execution result is generated, the execution result may not be returned to the database middleware, but the execution result may be saved in memory until all 10 execution results are generated, and the execution results in memory are returned to the database middleware together.

[0106] In some examples, before the database middleware sends the structured query statement to the database cluster, the database middleware needs to establish a database connection pool with the database cluster, wherein the database connection pool is responsible for allocating, managing and releasing database connections, and a queue of connection objects is maintained in the database connection pool. When a database connection is needed, the database middleware obtains a connection object from the database connection pool and returns it to the database connection pool after use. The database connection pool is used to transmit data between the database cluster and the database middleware. The number of database connection pools is positively correlated with the number of connection requests issued by the client, and the number of database connection pools is lower than the number of connection requests issued by the client. That is, the database middleware can establish a database connection pool with the database cluster, and the number of connection pools can increase as the number of businesses connected to the database middleware increases. The specific number of connection pools can be determined by the database middleware, rather than adding a database connection pool for each additional business.

[0107] From this, we can see that since a database connection pool is not added for each additional business, the number of database connection pools can be controlled by the database middleware, thus avoiding the technical problem of too many database connection pools as the number of connected businesses continues to increase, and achieving the technical effect of controlling the convergence of the number of database connection pools by the database middleware.

[0108] In the embodiment of the present application, the database middleware responds to the migration of any database in the database cluster, obtains the address of the database after the migration, and updates the database address recorded by the database middleware to the address after the database migration. That is, after any database migration, there is no need to update the database address recorded in each client, only the address of the database in the database middleware needs to be updated, which improves the efficiency of business execution.

[0109] In the embodiment of the present application, in response to any database in the database cluster switching between the primary database and the standby database, the switched database is reconnected. That is, after the database switches between the primary database and the standby database, there is no need for the client to trigger the reconnection, and the reconnection only needs to be triggered by the database middleware, thereby improving the efficiency of business execution.

[0110] In the embodiment of the present application, in response to any database in the database cluster shrinking or expanding its capacity, the configuration corresponding to the database is adjusted according to the capacity after shrinking or expanding. That is, after any database shrinks or expands its capacity, there is no need to adjust the configuration of the shrinking or expanding capacity recorded in each client, and only the configuration of the database in the database middleware needs to be adjusted, thereby improving the efficiency of business execution.

[0111] The following describes an application of a structured query execution method provided in an embodiment of the present application in a data query process.

[0112] See also Figure 5 , Figure 5 It is a schematic diagram of a data query framework provided in an embodiment of the present application.

[0113] exist Figure 5 In the client: compatible with MySQL protocol (database protocol), for example: MySQL command line, JDBC connection in Java, MySQL library in Python, MySQL library in C++, etc.

[0114] Proxy (database middleware): The Proxy kernel uses the Netty framework as the server programming framework. When the client initiates a connection request, the Proxy will perform MySQL protocol parsing during the Netty connection request. When it complies with the standard MySQL protocol, a communication channel between the client and the Proxy will be established. After the communication channel is established, the client operates by passing in standard SQL (structured query statements). The Proxy obtains the SQL statements passed in by the business through network transmission, performs SQL parsing through the SQL parsing engine, and executes the SQL after parsing on the specific database instance to obtain the SQL execution result. The result processing engine processes the obtained SQL execution result.

[0115] MySQL cluster (database cluster): The database cluster is used to provide data storage services. By using Proxy, users do not need to worry about the deployment of the database. Client access to Proxy is a stateless service. The high availability of the database cluster is perceived by Proxy.

[0116] See also Figure 6 , Figure 6 It is a data query flow chart provided in an embodiment of the present application.

[0117] like Figure 6 As shown, in step 201, the Proxy loads the configuration.

[0118] The Proxy can obtain the business execution parameters passed in by the client, including status parameters and SQL statements.

[0119] In step 202, the proxy determines whether the client turns on the transparent transmission mode.

[0120] If the state parameter indicates that the transparent transmission mode (transparent transmission function) is turned on, step 203 is executed; if the state parameter indicates that the transparent transmission mode is not turned on, step 204 is executed.

[0121] In step 203, the Proxy executes SQL statement transparent transmission.

[0122] The Proxy transparently transmits the SQL statement to the database cluster, and the process jumps to step 205 .

[0123] In step 204, the Proxy completely parses the SQL statement.

[0124] After the Proxy completely parses the SQL statement, it sends the parsed SQL statement to the database cluster.

[0125] In step 205, the database cluster executes the SQL statement.

[0126] When jumping from step 203 to step 205, the database cluster executes the SQL statement. When jumping from step 204 to step 205, the database cluster executes the parsed SQL statement.

[0127] Whether to enable the transparent transmission mode can be determined by the user. The user can decide whether to enable the transparent transmission mode (since transparent transmission cannot be divided into databases and tables, it can only be applied to businesses with a small amount of data) or not to enable the transparent transmission mode based on actual business needs. The server can recommend the use of the transparent transmission mode to businesses that do not use the database and table division feature. In this mode, there is no need to parse SQL statements, and the request is directly transmitted from the Proxy to the downstream MySQL database. This way, the Proxy has the highest compatibility and the best performance. For businesses that need to divide databases and tables, or that need to merge result sets through Proxy, the server recommends not using the transparent transmission mode. Executing business through Proxy can shield the underlying database and table division logic from users, making the process of using the database for business simpler.

[0128] See also Figure 7 , Figure 7 It is an execution flow chart of parsing SQL statements provided in an embodiment of the present application.

[0129] In step 301, the client transmits SQL to the Proxy.

[0130] Proxy can obtain SQL passed in by the client.

[0131] In step 302, the Proxy parses the SQL.

[0132] The Proxy can parse the SQL to split the SQL into multiple words and determine the context information corresponding to the multiple words.

[0133] In step 303, the Proxy determines the routing address of the SQL.

[0134] Proxy can extract the SQL routing address from the context information.

[0135] In step 304, the Proxy rewrites the SQL.

[0136] Proxy can rewrite SQL into statements executed by the database cluster.

[0137] In step 305, the data cluster executes the SQL.

[0138] The Proxy transmits the rewritten SQL to the database cluster, and the database cluster executes the SQL.

[0139] In step 306, the Proxy merges the results.

[0140] The proxy merges the execution results generated by the database cluster and returns them to the client.

[0141] See also Figure 8 , Figure 8 It is an execution flow chart of the transparent transmission mode provided in an embodiment of the present application.

[0142] In step 401, the client transmits SQL to the Proxy.

[0143] The Proxy obtains the SQL passed in by the client.

[0144] In step 402, the database cluster executes SQL.

[0145] The Proxy transmits the SQL to the database cluster, and the database cluster executes the SQL.

[0146] In step 403, the Proxy returns the result to the client.

[0147] The Proxy returns the execution results generated by the database cluster to the client.

[0148] The above is an embodiment of the present application. Figure 7 and Figure 8 The differences in the processes when the transparent transmission mode is not enabled and when the transparent transmission mode is enabled are shown respectively. The differences when the transparent transmission mode is not enabled and when the transparent transmission mode is enabled are further explained according to Table 1.

[0149]

[0150] Table 1

[0151] As shown in Table 1, when the SQL parsing mode is turned on (the transparent transmission mode is not turned on), the compatibility of SQL is that it is compatible with most SQL in the SQL parsing mode and is compatible with all SQL in the transparent transmission mode. Therefore, the compatibility of SQL in the transparent transmission mode is stronger.

[0152] As for performance loss, the performance loss in SQL parsing mode is medium, and the performance loss in transparent transmission mode is low. Therefore, the performance loss corresponding to transparent transmission mode is relatively small.

[0153] As for whether to support database and table sharding and whether to support read-write separation, database and table sharding and read-write separation are supported in SQL parsing mode, but not in transparent transmission mode.

[0154] The following describes the differences between using a proxy and not using a proxy in the embodiments of the present application.

[0155] See also Fig. 9 , Fig. 9 This is an architecture diagram of the business direct connection database provided in an embodiment of the present application.

[0156] Fig. 9 The present invention introduces the method of transmitting data between the client and the database when executing business in the prior art, specifically, the client is directly connected to the database. In the prior art, the client and the database are directly connected, so there may be the following defects:

[0157] When the database is migrated, the business side needs to be aware of the migration of the database, and then needs to cooperate with the database administrator (Database Administrator, DBA) to adjust the database access address. Among them, the business side is the functional module in the client for executing the business.

[0158] When the database is switched, the business side needs to be aware of the database switch. When the primary database is switched to the standby database, the business side needs to reacquire the connection to the standby database to complete the database switch.

[0159] When the database is expanded or reduced in capacity, the business side needs to be aware of the expansion and reduction of the database. When the database capacity reaches a bottleneck, the business side needs to cooperate with the DBA to complete the database splitting work.

[0160] In terms of database account management, the business side needs to be aware of changes in account management. For example, when the business needs to expand capacity, it is necessary to apply for database account authorization.

[0161] When it comes to business access to database network policies, the business needs to be aware of changes in database network policies, for example, when business expansion applies to the database network policy.

[0162] In terms of database connection pool management, since the business side is directly connected to the database, as many database connection pools will be created as there are businesses. For example, if there are 100 clients on the business side and each client establishes a database connection pool of 10 connections, there will be a database connection pool of 1,000 connections connected to the database, and the size of the database connection pool cannot be converged.

[0163] See also Fig.10 , Fig.10 This is an architectural diagram of the transparent transmission mode provided in an embodiment of the present application.

[0164] Depend on Fig.10 It can be seen that the business side does not need to connect directly to the database, which has the following advantages:

[0165] During database migration, the business side does not need to be aware of the database migration. Since the business side accesses the database through the Proxy, the address of the business side accessing the Proxy remains unchanged. The DBA only needs to adjust the access address of the Proxy to the database. The business side no longer needs to be aware of the database migration.

[0166] When the database is switched, the business side does not need to be aware of the database switch. After the database is switched between the primary and standby levels, it only needs to proxy to the database layer to trigger a reconnection.

[0167] When the database is expanded or reduced in capacity, the business side does not need to be aware of the expansion or reduction of the database. The proxy and database can adjust the corresponding configuration.

[0168] In terms of database account management, the business side only needs to care about the Proxy account, not the Proxy to database account.

[0169] In terms of the network policy for business access to the database, the business side only needs to be concerned about the network policy of the Proxy, and does not need to be concerned about the network policy from the Proxy to the database.

[0170] In terms of database connection pool management, the business side only needs to connect to the Proxy. The size of the database connection pool is controlled by the Proxy. The database connection pool will not be expanded indefinitely as the business increases. The size of the database connection pool can be converged through the Proxy.

[0171] At the same time, Fig.10 In the process, load balancing can be used to reasonably utilize multiple proxies to improve the efficiency of querying the database.

[0172] It can be seen from this that in the execution method of the structured execution statement provided in the embodiment of the present application, the step of parsing the structured query statement can be skipped by opening the transparent transmission function on the client, and the unparsed structured query statement is executed by the database. By skipping the step of parsing the structured query statement, the computing resources occupied when querying the database are reduced, and the use efficiency of the computing resources is improved. At the same time, since the step of parsing the structured query statement is skipped, the speed of querying the database is improved. Further, since there is no need to parse the structured query statement, the situation of parsing failure caused by the high complexity of the structured query statement will not occur, and the efficiency of executing the structured query statement is improved.

[0173] The embodiment of the present application provides a computer program product, which includes a computer program or a computer executable instruction, and the computer program or the computer executable instruction is stored in a computer-readable storage medium. The processor of the electronic device reads the computer executable instruction from the computer-readable storage medium, and the processor executes the computer executable instruction, so that the electronic device executes the execution method of the structured query in the embodiment of the present application.

[0174] The present application embodiment provides a computer-readable storage medium storing computer-executable instructions, wherein the computer-executable instructions or computer programs are stored. When the computer-executable instructions or computer programs are executed by a processor, the processor will be caused to execute the structured query execution method provided by the present application embodiment, for example, Figure 3A The execution method of the structured query is shown.

[0175] In some embodiments, the computer-readable storage medium may be a memory such as RAM, ROM, flash memory, magnetic surface memory, optical disk, or CD-ROM; or may be various devices including one or any combination of the above memories.

[0176] In some embodiments, computer executable instructions may be in the form of a program, software, software module, script or code, written in any form of programming language (including compiled or interpreted languages, or declarative or procedural languages), and may be deployed in any form, including as a stand-alone program or as a module, component, subroutine or other unit suitable for use in a computing environment.

[0177] As an example, computer-executable instructions may, but need not, correspond to a file in a file system, may be stored as part of a file that stores other programs or data, such as in one or more scripts in a HyperText Markup Language (HTML) document, in a single file dedicated to the program in question, or in multiple coordinated files (e.g., files storing one or more modules, subroutines, or code portions).

[0178] As an example, computer executable instructions may be deployed to be executed on one electronic device, or on multiple electronic devices located at one site, or on multiple electronic devices distributed at multiple sites and interconnected by a communication network.

[0179] In summary, the embodiments of the present application can reduce resource usage caused by querying the database and increase the speed of querying the database.

[0180] The above is only an embodiment of the present application and is not intended to limit the protection scope of the present application. Any modifications, equivalent substitutions and improvements made within the spirit and scope of the present application are included in the protection scope of the present application.

Claims

1. A method for executing a structured query, characterized in that: The method comprises: Receive a connection request from a client and establish a communication channel with the client; Acquiring the service execution parameters uploaded by the client through the communication channel, wherein the service execution parameters include: a structured query statement and a state parameter, the state parameter indicates whether the client has enabled a transparent transmission function, and the transparent transmission function indicates that the database cluster executes an unparsed structured query statement; In response to the state parameter indicating that the client has enabled the transparent transmission function, sending the structured query statement to the database cluster so that the database cluster executes the structured query statement; The execution result of the structured query statement sent by the database cluster is received, and the execution result is returned to the client through the communication channel.

2. The method according to claim 1, characterized in that The establishing of a communication channel with the client comprises: Receiving a connection request sent by the client, and parsing the database protocol carried in the connection request; In response to the database protocol carried in the connection request satisfying a standard database protocol, a communication channel is established with the client.

3. The method according to claim 1, characterized in that After obtaining the service execution parameters uploaded by the client, the method further includes: In response to the state parameter indicating that the client does not enable the transparent transmission function, parsing and processing the structured query statement, so that the database cluster executes the parsed structured query statement; The execution result of the structured query statement sent by the database cluster is received, and the execution result of the structured query statement is returned to the client.

4. The method according to claim 3, characterized in that: The parsing and processing of the structured query statement includes: Splitting the structured query statement into multiple words, and determining context information corresponding to the multiple words; Extracting a routing address of the structured query statement from the context information, wherein the routing address is an address of a database for executing the structured query statement; rewriting the context information into statements executed by the database cluster; The receiving the execution result of the structured query statement sent by the database cluster includes: Receive an execution result of the rewritten context information sent by a database, wherein the database is a database in the database cluster corresponding to the routing address.

5. The method according to any one of claims 1, 3 or 4, characterized in that: The database cluster executes the structured query statement in the following manner: Executing the structured query statements in sequence according to the time sequence to obtain a plurality of execution results of the structured query statements, and storing the obtained execution results of the plurality of structured query statements in a memory; In response to acquiring all execution results of the structured query statements, the execution results of the multiple structured query statements stored in the memory are sent to the database middleware.

6. The method according to any one of claims 1 to 5, characterized in that: The client is connected to a plurality of different database middlewares through a plurality of the communication channels; The service execution parameters correspond to a plurality of different services executed by the client; The acquiring, through the communication channel, the service execution parameters uploaded by the client includes: Based on the loads of the multiple different services executed by the client, with the goal of balancing the loads of the multiple different services, the multiple different services are allocated to the multiple database middlewares for execution; The service execution parameters corresponding to the multiple different services uploaded by the client are received through the multiple communication channels.

7. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: In response to the migration of any database in the database cluster, an address of the database after the migration is obtained, and the database address recorded by the database middleware is updated to the address of the database after the migration.

8. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: In response to any database in the database cluster switching between a primary database and a standby database, reconnecting to the switched database.

9. The method according to any one of claims 1 to 5, characterized in that: The method further comprises: In response to any database in the database cluster performing capacity reduction or expansion, a configuration corresponding to the database is adjusted according to the capacity after the reduction or expansion.

10. The method according to any one of claims 1 to 5, characterized in that: The connection request carries the account parameters to be verified, wherein the parameters to be verified include: the account to be verified and the password to be verified corresponding to the account to be verified; The establishing of a communication channel with the client comprises: Verifying the parameter to be verified carried in the connection request; In response to the parameter to be verified passing verification, a communication channel is established with the client.

11. The method according to any one of claims 1 to 5, characterized in that: Before sending the structured query statement to the database cluster, the method further includes: A database connection pool is established with the database cluster, wherein the database connection pool is used to transfer data between the database cluster and the database middleware, the number of the database connection pools is positively correlated with the number of the connection requests issued by the client, and the number of the database connection pools is lower than the number of the connection requests issued by the client.

12. A structured query execution device, characterized in that: The device comprises: A channel establishment module, used to receive a connection request from a client and establish a communication channel with the client; A parameter acquisition module, used to acquire the service execution parameters uploaded by the client through the communication channel, wherein the service execution parameters include: a structured query statement and a state parameter, wherein the state parameter indicates whether the client has enabled a transparent transmission function, and the transparent transmission function indicates that the database cluster executes an unparsed structured query statement; A statement sending module, configured to send the structured query statement to the database cluster in response to the state parameter indicating that the client has enabled the transparent transmission function, so that the database cluster executes the structured query statement; The result transmission module is used to receive the execution result of the structured query statement sent by the database cluster, and return the execution result to the client through the communication channel.

13. An electronic device, characterized in that: The electronic device comprises: A memory for storing computer executable instructions; A processor, configured to implement the structured query execution method according to any one of claims 1 to 11 when executing the computer executable instructions or computer programs stored in the memory.

14. A computer-readable storage medium storing computer-executable instructions or a computer program, characterized in that: When the computer executable instructions or computer program are executed by a processor, the method for executing a structured query according to any one of claims 1 to 11 is implemented.

15. A computer program product comprising computer executable instructions or a computer program, characterized in that: When the computer executable instructions or computer program are executed by a processor, the method for executing a structured query according to any one of claims 1 to 11 is implemented.

Images