An intelligent processing method and system of an SQL statement, a computer device and a medium

Abstract

The application relates to the fields of computer technology and digital medicine, and discloses an intelligent processing method and system of an SQL statement, a computer device and a medium. The method comprises the following steps: when a data operation request for a database is detected, the data operation request is intercepted and analyzed to generate database connection information; when it is determined that a target SQL script is a preset first type SQL, a target execution plan of the target SQL script is determined according to the database connection information, and a to-be-optimized parameter of the target SQL script is determined based on the plan to optimize the target SQL script. Through real-time interception of the data operation request for preset first type SQL judgment, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, a target execution plan at the current time can be created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, accurate reproduction and optimization of the preset first type SQL can be realized, and the stability of the system is improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer technology and the field of digital medicine, and in particular to an intelligent processing method and system for SQL statements, a computer device and a medium. BACKGROUND

[0002] Structured Query Language (SQL) is a database query and programming language, which is a language for operating a database system. In software programming, the preset first type of SQL phenomenon may be caused due to the programming ability of developers and frequent iteration of business. The preset first type of SQL phenomenon refers to that after a business runs for a period of time, part of SQL statements frequently perform I / O operations on a database, thereby causing the part of SQL statements to occupy CPU for a long time when executed. For an application, a small amount of preset first type of SQL may not affect the normal operation of the business, but as the preset first type of SQL statements increase to a certain extent, the execution of other business functions in the application will be ultimately affected. Therefore, tracking and optimizing the preset first type of SQL plays a crucial role in ensuring system operation.

[0003] In the prior art, the most important means for identifying and judging the preset first type of SQL is to query the execution plan of the SQL, and the execution plan is generated and executed by a database optimizer in real time before the execution of the SQL according to the current data condition, parameter condition, statistical information, etc. Due to the increasing complexity of the system and the increasing coupling between various functions, if other functions are mixed in the operation at the current time, the current data condition, parameter condition, and statistical information will change, resulting in changes in the execution plan corresponding to the changed data, so that the preset first type of SQL cannot be accurately reproduced and optimized, thereby reducing the stability of the system. SUMMARY

[0004] Therefore, it is necessary to provide an intelligent processing method and system for SQL statements, a computer device and a medium in view of the problem of low system stability.

[0005] The application discloses an intelligent processing method of an SQL statement, and the method comprises the following steps: when a data operation request for a database is detected, the data operation request is intercepted and parsed to generate database connection information; the execution duration of a target SQL script corresponding to the data operation request is recorded in real time, and it is determined whether the target SQL script is a preset first type SQL based on the execution duration; when the target SQL script is the preset first type SQL, a target execution plan of the target SQL script is determined according to the database connection information; the to-be-optimized parameters of the target SQL script are determined based on the target execution plan, and the to-be-optimized parameters are sent to an optimization server to optimize the SQL statement of the target SQL script.

[0006] In one of the embodiments, the determination of whether the SQL script belongs to the preset first type SQL based on the execution duration comprises: when the execution duration is greater than a preset duration threshold, the target SQL script is determined as the preset first type SQL; or when the execution duration is less than or equal to the preset duration threshold, the target SQL script is determined as a preset second type SQL.

[0007] In one of the embodiments, the determination of the target execution plan of the target SQL script according to the database connection information comprises: an original SQL script expression is obtained according to the database connection information; a target keyword used for generating an execution plan is obtained; the target keyword is spliced at a preset position of the original SQL script expression to obtain a target expression; the target execution plan of the target SQL script is generated in the database by executing the target expression and combining all parameters of the data operation request.

[0008] In one of the embodiments, the determination of the to-be-optimized parameters of the target SQL script based on the target execution plan comprises: a plurality of preset first type SQL execution plans are traversed in a preset first type SQL execution plan library generated in advance; when there is no preset first type SQL execution plan consistent with the target execution plan in the plurality of preset first type SQL execution plans, a plurality of historical execution plans corresponding to the target SQL script are obtained; the target execution plan is compared with the plurality of historical execution plans one by one to generate a comparison result of the target execution plan; the data structure in which the target execution plan is inconsistent with each historical execution plan is analyzed according to the comparison result; and the data structure in which the target execution plan is inconsistent with each historical execution plan is determined as the to-be-optimized parameters of the preset first type SQL.

[0009] In one of the embodiments, the target execution plan is compared with the plurality of historical execution plans one by one to generate a comparison result of the target execution plan, including: obtaining a first comparison parameter of each step in the target execution plan, the first comparison parameter including at least a first step sequence number, a first index key and a first table connection type; obtaining a second comparison parameter of each step in each of the plurality of historical execution plans, the second comparison parameter including at least a second step sequence number, a second index key and a second table connection type; comparing the first step sequence number with each second step sequence number to determine whether the first step sequence number and each second step sequence number are consistent, to generate a first result; comparing the first index key with each second index key to determine whether the first index key and each second index key are consistent, to generate a second result; comparing the first table connection type with each second table connection type to determine whether the first table connection type and each second table connection type are consistent, to generate a third result; and determining the first result, the second result and the third result as the comparison result of the target execution plan.

[0010] In one of the embodiments, the data operation request is intercepted and parsed to generate the database connection information, including: intercepting the data operation request; determining an identifier of a target database of the data operation request; locally parsing a plurality of target fields in a packet header of the data operation request; determining a database type according to the identifier, and calling a corresponding parsing program in a cloud service program library based on the database type; parsing fields in a packet body of the data operation request according to the parsing program and in combination with the plurality of target fields; and mapping out the database connection information from the fields in the packet body.

[0011] In one of the embodiments, the method further includes: after the execution of the target SQL script ends, obtaining an execution result of the target SQL script; mapping out a client encoding from the fields in the packet body; converting the client encoding to obtain a physical address of the client; and encapsulating the execution result into a response packet, and responding the response packet to the client according to the physical address.

[0012] An intelligent processing system of an SQL statement, the system including: a database connection information generation module, configured to intercept and parse a data operation request for a database when the data operation request is detected, and generate database connection information; a preset first type SQL determination module, configured to record an execution duration of a target SQL script corresponding to the data operation request in real time, and determine whether the target SQL script is a preset first type SQL based on the execution duration; a target execution plan determination module, configured to determine a target execution plan of the target SQL script according to the database connection information when the target SQL script is the preset first type SQL; and an SQL statement optimization module, configured to determine a to-be-optimized parameter of the target SQL script based on the target execution plan, and send the to-be-optimized parameter to an optimization server to optimize an SQL statement of the target SQL script.

[0013] A computer device comprising a memory and a processor, the memory storing computer readable instructions, the computer readable instructions being executed by the processor to cause the processor to perform the steps of the intelligent processing method of the SQL statement.

[0014] A medium storing computer readable instructions, the computer readable instructions being executed by one or more processors to cause the one or more processors to perform the steps of the intelligent processing method of the SQL statement.

[0015] The intelligent processing method, system, device and medium of the SQL statement, the intelligent processing system of the SQL statement first intercepts and analyzes the data operation request when detecting the data operation request for the database, generates the database connection information, then records the execution time of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution time, secondly when the target SQL script is the preset first type SQL, determines the target execution plan of the target SQL script according to the database connection information, and finally determines the to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script. Since the preset first type SQL judgment is performed by real-time interception of the data operation request, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, the target execution plan at the current time can be created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, thereby realizing accurate reproduction and optimization of the preset first type SQL, and further improving the stability of the system. BRIEF DESCRIPTION OF DRAWINGS

[0016] The accompanying drawings, which are incorporated into and form part of the specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the application.

[0017] Figure 1 An implementation environment diagram of the intelligent processing method of the SQL statement provided in an embodiment of the present application;

[0018] Figure 2 An internal structure diagram of the computer device in an embodiment of the present application;

[0019] Figure 3 A method diagram of the intelligent processing method of the SQL statement provided in an embodiment of the present application;

[0020] Figure 4 A data structure diagram of the execution plan record provided in an embodiment of the present application;

[0021] Figure 5 Figure 1 is a process schematic diagram of an intelligent processing process of an SQL statement provided in an embodiment of the present application;

[0022] Figure 6 Figure 2 is a method schematic diagram of another intelligent processing method of an SQL statement provided in an embodiment of the present application;

[0023] Figure 7 Figure 3 is a system structure schematic diagram of an intelligent processing system of an SQL statement provided in an embodiment of the present application. DETAILED DESCRIPTION

[0024] In order to make the objectives, technical solutions and advantages of the present application clearer, the present application is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and do not limit the present application.

[0025] It can be understood that the terms "first", "second", etc. used in the present application can be used herein to describe various elements, but these elements are not limited by these terms. These terms are only used to distinguish the first element from another element.

[0026] Figure 1 Figure 4 is an implementation environment diagram of the intelligent processing method of the SQL statement provided in an embodiment, as shown in the figure, in the implementation environment, a server 110 and a client 120 are included. Figure 1

[0027] The server 110 can be a server, which can be a stand-alone server, or a cloud server providing cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms, etc. basic cloud computing services, for example, a server device for optimizing target SQL scripts. The client 120 connects the server 110, and when detecting a data operation request for a database, the client 120 intercepts and parses the data operation request, generates database connection information, and the client 120 records the execution time of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution time. When the target SQL script is the preset first type SQL, the client 120 determines the target execution plan of the target SQL script according to the database connection information, determines the to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to the server 110 to optimize the SQL statement of the target SQL script.

[0028] ​It should be noted that the client 120 can be a smartphone, tablet, laptop, desktop computer, etc., but is not limited to these. The server 110 and the client 120 can connect via Bluetooth, USB (Universal Serial Bus), or other communication connection methods, which are not limited in this invention.

[0029] Figure 2 This is a schematic diagram of the internal structure of a computer device in one embodiment. For example... Figure 2 As shown, the computer device includes a processor, media, memory, and network interface connected via a system bus. The media of the computer device stores an operating system, a database, and computer-readable instructions. The database may store a sequence of control information. When the computer-readable instructions are executed by the processor, they enable the processor to implement an intelligent processing method for SQL statements. The processor of the computer device provides computing and control capabilities to support the operation of the entire device. The memory of the computer device may store computer-readable instructions, which, when executed by the processor, enable the processor to execute an intelligent processing method for SQL statements. The network interface of the computer device is used for communication with a terminal. Those skilled in the art will understand that... Figure 2 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. A specific computer device may include more or fewer components than shown in the figure, or combine certain components, or have different component arrangements. The medium is a readable storage medium.

[0030] The following will be combined with the appendix Figures 3-6 This application provides a detailed description of the intelligent SQL statement processing method provided in its embodiments. This method can be implemented using a computer program and can run on an intelligent SQL statement processing system based on the von Neumann architecture. This computer program can be integrated into an application or run as a standalone utility application.

[0031] The embodiments of this application can acquire and process relevant data based on artificial intelligence technology. Artificial intelligence (AI) refers to the theories, methods, technologies, and application systems that use digital computers or machines controlled by digital computers to simulate, extend, and expand human intelligence, perceive the environment, acquire knowledge, and use that knowledge to obtain optimal results.

[0032] The artificial intelligence basic technology generally includes technologies such as sensors, special artificial intelligence chips, cloud computing, distributed storage, intelligent processing technology of large SQL statements, operation / interaction systems, mechatronics, and the like. The artificial intelligence software technology mainly includes computer vision technology, robot technology, biometric identification technology, speech processing technology, natural language processing technology, and machine learning / deep learning, and the like.

[0033] See Figure 3 A flowchart of an intelligent processing method of an SQL statement is provided for the embodiments of the present application. As shown in Figure 3 The method of the embodiments of the present application can include the following steps:

[0034] S101, when a data operation request for a database is detected, the data operation request is intercepted and parsed to generate database connection information;

[0035] The database is a "warehouse for organizing, storing and managing data according to data structure". It is a long-term storage in a computer, an organized, shareable and unified management of a large amount of data collection. For example, the database of the medical platform. The data operation request is generated after the user triggers a certain function on the system platform, for example, the data query request generated after the user triggers the login function on the medical platform. The database connection information can include database address, database password and other database parameters.

[0036] Generally, when intercepting the data operation request, the detector and the interceptor are jointly intercepted and processed by the pre-set detector and the interceptor in the present application. The detector filters and analyzes the data stream in the system platform in real time to find the data operation request for the database. After the detector finds the data operation request, the interceptor is called to intercept and process the data operation request.

[0037] In the embodiments of the present application, when the SQL statement is intelligently optimized, the detector first filters and analyzes the data stream in the system platform in real time. When the data operation request for the database is detected, the detector generates an interception command and sends it to the interceptor to call the interceptor. The interceptor intercepts and parses the data operation request to generate the database connection information.

[0038] Specifically, when the data operation request is intercepted and parsed, the data operation request is first intercepted, and the identifier of the target database of the data operation request is determined. Then, the multiple target fields in the header of the data operation request are parsed locally. Then, the database type is determined according to the identifier, and the corresponding parsing program in the cloud service program library is called based on the database type. Then, the fields in the body of the data operation request are parsed according to the parsing program and in combination with the multiple target fields. Finally, the database connection information is mapped and associated in the fields in the body.

[0039] In a possible implementation, after a user triggers a login function on a medical platform, a data query request corresponding to the login function is generated, the data query request reaches a corresponding action code segment through a destination address, a detector function in the action code segment detects the data query request, and then the data query request is intercepted and parsed by an interceptor in the action code segment to generate database connection information.

[0040] S102, record the execution duration of the target SQL script corresponding to the data operation request in real time, and determine whether the target SQL script is a preset first type SQL based on the execution duration;

[0041] In the embodiment of the application, when the execution duration of the target SQL script corresponding to the data operation request is recorded in real time, the SQL request parameters and the dynamic SQL identifier in the data operation request are first obtained, the dynamic SQL expression is determined in the SQL library according to the dynamic SQL identifier, the SQL request parameters are associated with the dynamic SQL expression to generate the final target SQL script, the final target SQL script is executed, and the execution duration of the final target SQL script is timed in real time by a timer to generate the execution duration of the target SQL script.

[0042] In the embodiment of the application, when it is determined whether the target SQL script is a preset first type SQL based on the execution duration, when the execution duration is greater than a preset duration threshold, the target SQL script is determined to be the preset first type SQL; or when the execution duration is less than or equal to the preset duration threshold, the target SQL script is determined to be a preset second type SQL.

[0043] S103, when the target SQL script is the preset first type SQL, determining a target execution plan of the target SQL script according to the database connection information;

[0044] In the embodiment of the application, when the target SQL script is the preset first type SQL, the target execution plan of the target SQL script needs to be determined according to the database connection information. First, the original SQL script expression is obtained according to the database connection information, and then the target keyword used to generate the execution plan is obtained. Next, the target keyword is spliced at a preset position of the original SQL script expression to obtain a target expression. Finally, the target expression is executed, and the target execution plan of the target SQL script is generated in the database in combination with all parameters of the data operation request. The target keyword is "explain".

[0045] S104, determine the to-be-optimized parameter of the target SQL script based on the target execution plan, and send the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script.

[0046] In the embodiment of the present application, when the to-be-optimized parameter of the target SQL script is determined based on the target execution plan, first, the plurality of preset first type SQL execution plans in the preset first type SQL execution plan library generated in advance are traversed, then when there is no preset first type SQL execution plan consistent with the target execution plan in the plurality of preset first type SQL execution plans, the plurality of historical execution plans corresponding to the target SQL script are obtained, secondly, the target execution plan and the plurality of historical execution plans are compared one by one to generate a comparison result of the target execution plan, and finally, the data structure inconsistent between the target execution plan and each historical execution plan is analyzed according to the comparison result, and the data structure inconsistent between the target execution plan and each historical execution plan is determined as the to-be-optimized parameter of the preset first type SQL.

[0047] Specifically, when the target execution plan and the plurality of historical execution plans are compared one by one to generate the comparison result of the target execution plan, first, the first comparison parameter of each step in the target execution plan is obtained, the first comparison parameter at least includes the first step sequence number, the first index key and the first table connection type, then the second comparison parameter of each step in each historical execution plan in the plurality of historical execution plans is obtained, the second comparison parameter at least includes the second step sequence number, the second index key and the second table connection type, secondly, the first step sequence number and each second step sequence number are compared to determine whether the first step sequence number and each second step sequence number are consistent, a first result is generated, and the first index key and each second index key are compared to determine whether the first index key and each second index key are consistent, a second result is generated, finally, the first table connection type and each second table connection type are compared to determine whether the first table connection type and each second table connection type are consistent, a third result is generated, and the first result, the second result and the third result are determined as the comparison result of the target execution plan. The data structure of the execution plan record is shown in the table. Figure 4

[0048] Further, after the execution of the target SQL script is completed, first, the execution result of the target SQL script is obtained, and the client code is mapped in the field in the message body, secondly, the client code is converted to obtain the physical address of the client, and finally, the execution result is encapsulated into a response message, and the response message is responded to the client according to the physical address. For example, when the client code is a binary code, the physical address can be obtained after decimal conversion.

[0049] For example Figure 5 as shown in the table, Figure 5 ​is a process schematic block diagram of an intelligent processing process of an SQL statement provided by the present application. First, when a data query is performed in an application system, a data query request is generated. When the data query request is used to query corresponding data in a database as a normal request, an interceptor intercepts the request after detecting the normal request, parses the intercepted request information, obtains SQL information, i.e., database connection information, records an execution duration of a target SQL script corresponding to the data query request, determines that the target SQL script is a preset first type SQL when the execution duration exceeds a preset duration, and then a plan comparison unit determines a target execution plan of the target SQL script according to the database connection information, determines a to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to an optimization server to optimize the SQL statement of the target SQL script. Meanwhile, the interceptor executes the target SQL script to obtain corresponding data in the database and obtain a response message. Finally, the response message is returned to the application system as an execution result.

[0050] In the embodiment of the present application, the intelligent processing system of the SQL statement first intercepts and parses a data operation request when detecting the data operation request for a database, generates database connection information, then records an execution duration of a target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution duration. Secondly, when the target SQL script is the preset first type SQL, a target execution plan of the target SQL script is determined according to the database connection information. Finally, a to-be-optimized parameter of the target SQL script is determined based on the target execution plan, and the to-be-optimized parameter is sent to an optimization server to optimize the SQL statement of the target SQL script. Since the preset first type SQL is determined by real-time interception of the data operation request in the present application, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, a target execution plan at the current time can be created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, thereby accurately reproducing and optimizing the preset first type SQL, and further improving the stability of the system.

[0051] Please refer to Figure 6 A flowchart of an intelligent processing method of an SQL statement is provided in the embodiment of the present application. As shown in Figure 6 The method of the embodiment of the present application can include the following steps:

[0052] S201, when a data operation request for a database is detected, the data operation request is intercepted;

[0053] S202, the identification of a target database of the data operation request is determined, and a plurality of target fields in a message header of the data operation request are parsed locally;

[0054] S203, determining a database type according to the identifier, and calling a corresponding parsing program in a cloud service program library based on the database type;

[0055] S204, mapping and correlating database connection information in the fields in the message body of the data operation request according to the parsing program and in combination with a plurality of target fields;

[0056] S205, recording the execution duration of the target SQL script corresponding to the data operation request in real time, and determining the target SQL script as a preset first type SQL when the execution duration is greater than a preset duration threshold;

[0057] S206, when the target SQL script is the preset first type SQL, obtaining an original SQL script expression according to the database connection information;

[0058] S207, obtaining a target keyword used to generate an execution plan, splicing the target keyword at a preset position of the original SQL script expression to obtain a target expression;

[0059] S208, executing the target expression and generating a target execution plan of the target SQL script in the database in combination with all parameters of the data operation request;

[0060] S209, determining the to-be-optimized parameter of the target SQL script based on the target execution plan, and sending the to-be-optimized parameter to an optimization server to optimize the SQL statement of the target SQL script.

[0061] In the embodiments of the present application, the intelligent processing system of the SQL statement first intercepts and parses the data operation request when detecting the data operation request for the database, generates the database connection information, then records the execution duration of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is the preset first type SQL based on the execution duration, secondly when the target SQL script is the preset first type SQL, determines the target execution plan of the target SQL script according to the database connection information, and finally determines the to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script. Since the present application determines the to-be-optimized parameter for SQL optimization by creating the target execution plan at the current time to determine the to-be-optimized parameter when it is determined that the request corresponds to the target SQL script of the preset first type SQL, each data request corresponds to the current latest execution plan, so that the preset first type SQL can be accurately reproduced and optimized, thereby improving the stability of the system.

[0062] The following is an embodiment of the system of the application, which can be used to execute the embodiment of the method of the application. For details not disclosed in the embodiment of the system of the application, please refer to the embodiment of the method of the application.

[0063] Please refer to Figure 7 , which shows the structural schematic diagram of the intelligent processing system of the SQL statement provided by an exemplary embodiment of the application. The intelligent processing system of the SQL statement can be realized by software, hardware or a combination of both to become all or part of the device. The system 1 comprises a database connection information generation module 10, a preset first type SQL determination module 20, a target execution plan determination module 30 and a SQL statement optimization module 40.

[0064] The database connection information generation module 10 is used to intercept and parse the data operation request when detecting the data operation request for the database, and generate the database connection information;

[0065] The preset first type SQL determination module 20 is used to record the execution time of the target SQL script corresponding to the data operation request in real time, and determine whether the target SQL script is the preset first type SQL based on the execution time;

[0066] The target execution plan determination module 30 is used to determine the target execution plan of the target SQL script according to the database connection information when the target SQL script is the preset first type SQL;

[0067] The SQL statement optimization module 40 is used to determine the to-be-optimized parameter of the target SQL script based on the target execution plan, and send the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script.

[0068] It should be noted that the intelligent processing system of the SQL statement provided by the above embodiment in executing the intelligent processing method of the SQL statement is only exemplified by the division of the above functional modules, and in actual application, the above functions can be completed by different functional modules according to needs, that is, the internal structure of the device is divided into different functional modules to complete all or part of the functions described above. In addition, the intelligent processing system of the SQL statement and the embodiment of the intelligent processing method of the SQL statement provided by the above embodiment belong to the same concept, and the implementation process is described in detail in the method embodiment, which will not be repeated here.

[0069] The above sequence numbers of the embodiments of the application are only for description, and do not represent the advantages and disadvantages of the embodiments.

[0070] In the embodiment of the present application, the intelligent processing system of the SQL statement first intercepts and parses the data operation request when detecting the data operation request for the database, generates the database connection information, then records the execution duration of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution duration, secondly when the target SQL script is the preset first type SQL, determines the target execution plan of the target SQL script according to the database connection information, and finally determines the to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script. Since the preset first type SQL judgment is performed by real-time interception of the data operation request in the present application, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, the target execution plan at the current time can be created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, thereby accurate reproduction and optimization of the preset first type SQL can be realized, and the stability of the system is improved.

[0071] In one embodiment, a computer device is provided, the device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, the processor implementing the following steps when executing the computer program:

[0072] intercepting and parsing the data operation request when detecting the data operation request for the database, and generating database connection information;

[0073] recording the execution duration of the target SQL script corresponding to the data operation request in real time, and determining whether the target SQL script is a preset first type SQL based on the execution duration;

[0074] when the target SQL script is the preset first type SQL, determining the target execution plan of the target SQL script according to the database connection information;

[0075] determining the to-be-optimized parameter of the target SQL script based on the target execution plan, and sending the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script.

[0076] In one embodiment, when the processor executes the step of determining whether the SQL script belongs to the preset first type SQL based on the execution duration, the following operations are specifically performed:

[0077] when the execution duration is greater than a preset duration threshold, the target SQL script is determined to be the preset first type SQL;

[0078] or,

[0079] When the execution time is less than or equal to the preset time threshold, the target SQL script is determined as a preset second type SQL.

[0080] In one embodiment, when the processor executes the target SQL script according to the database connection information to determine the target execution plan of the target SQL script, the following operations are specifically performed:

[0081] According to the database connection information, an original SQL script expression is obtained.

[0082] A target keyword used to generate an execution plan is obtained.

[0083] The target keyword is spliced at a preset position of the original SQL script expression to obtain a target expression.

[0084] The target expression is executed, and a target execution plan of the target SQL script is generated in the database in combination with all parameters of the data operation request.

[0085] In one embodiment, when the processor executes the target SQL script based on the target execution plan to determine the to-be-optimized parameter of the target SQL script, the following operations are specifically performed:

[0086] A plurality of preset first type SQL execution plans in a preset first type SQL execution plan library generated in advance are traversed.

[0087] When there is no preset first type SQL execution plan consistent with the target execution plan in the plurality of preset first type SQL execution plans, a plurality of historical execution plans corresponding to the target SQL script are obtained.

[0088] The target execution plan is compared with the plurality of historical execution plans one by one to generate a comparison result of the target execution plan.

[0089] According to the comparison result, a data structure inconsistent with each historical execution plan is analyzed.

[0090] The data structure inconsistent with each historical execution plan is determined as a to-be-optimized parameter of the preset first type SQL.

[0091] In one embodiment, when the processor executes the target SQL script based on the target execution plan to determine the to-be-optimized parameter of the target SQL script, the following operations are specifically performed:

[0092] A first comparison parameter of each step in the target execution plan is obtained, and the first comparison parameter at least includes a first step sequence number, a first index key, and a first table connection type.

[0093] obtaining a second comparison parameter of each step in each historical execution plan in the plurality of historical execution plans, the second comparison parameter comprising at least a second step sequence number, a second index key, and a second table connection type;

[0094] comparing the first step sequence number with each second step sequence number to determine whether the first step sequence number and each second step sequence number are consistent, and generating a first result;

[0095] comparing the first index key with each second index key to determine whether the first index key and each second index key are consistent, and generating a second result;

[0096] comparing the first table connection type with each second table connection type to determine whether the first table connection type and each second table connection type are consistent, and generating a third result;

[0097] determining the first result, the second result, and the third result as a comparison result of the target execution plan.

[0098] In one embodiment, the processor performs the following operations when intercepting and parsing the data operation request to generate the database connection information:

[0099] intercepting the data operation request;

[0100] determining an identifier of a target database of the data operation request;

[0101] locally parsing a plurality of target fields in a packet header of the data operation request;

[0102] determining a database type according to the identifier, and calling a corresponding parsing program in a cloud service program library based on the database type;

[0103] parsing a field in a packet body of the data operation request according to the parsing program and in combination with the plurality of target fields;

[0104] mapping and associating the database connection information from the field in the packet body.

[0105] In one embodiment, the processor further performs the following operations:

[0106] obtaining an execution result of the target SQL script after execution of the target SQL script ends;

[0107] mapping a client encoding from the field in the packet body;

[0108] converting the client encoding to obtain a physical address of the client;

[0109] encapsulating the execution result into a response packet, and responding the response packet to the client according to the physical address.

[0110] In the embodiment of the present application, the intelligent processing system of the SQL statement first intercepts and parses the data operation request when detecting the data operation request for the database, generates the database connection information, then records the execution duration of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution duration, secondly when the target SQL script is the preset first type SQL, determines the target execution plan of the target SQL script according to the database connection information, and finally determines the to-be-optimized parameter of the target SQL script based on the target execution plan, and sends the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script. Since the preset first type SQL judgment is performed by real-time interception of the data operation request in the present application, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, the target execution plan at the current time can be created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, thereby realizing accurate reproduction and optimization of the preset first type SQL, and further improving the stability of the system.

[0111] In one embodiment, a medium storing computer readable instructions is presented, which when executed by one or more processors, causes the one or more processors to perform the following steps:

[0112] When detecting the data operation request for the database, intercepting and parsing the data operation request to generate the database connection information;

[0113] Recording the execution duration of the target SQL script corresponding to the data operation request in real time, and determining whether the target SQL script is a preset first type SQL based on the execution duration;

[0114] When the target SQL script is the preset first type SQL, determining the target execution plan of the target SQL script according to the database connection information;

[0115] Determining the to-be-optimized parameter of the target SQL script based on the target execution plan, and sending the to-be-optimized parameter to the optimization server to optimize the SQL statement of the target SQL script.

[0116] In one embodiment, when the processor determines whether the SQL script belongs to the preset first type SQL based on the execution duration, the processor specifically performs the following operations:

[0117] When the execution duration is greater than the preset duration threshold, the target SQL script is determined as the preset first type SQL;

[0118] Or,

[0119] When the execution time is less than or equal to the preset time threshold, the target SQL script is determined as a preset second type SQL.

[0120] In one embodiment, when the processor executes the target SQL script according to the database connection information to determine the target execution plan of the target SQL script, the following operations are specifically performed:

[0121] According to the database connection information, an original SQL script expression is obtained.

[0122] A target keyword used to generate an execution plan is obtained.

[0123] The target keyword is spliced at a preset position of the original SQL script expression to obtain a target expression.

[0124] The target expression is executed, and a target execution plan of the target SQL script is generated in the database in combination with all parameters of the data operation request.

[0125] In one embodiment, when the processor executes the target SQL script based on the target execution plan to determine the to-be-optimized parameter of the target SQL script, the following operations are specifically performed:

[0126] A plurality of preset first type SQL execution plans in a preset first type SQL execution plan library generated in advance are traversed.

[0127] When there is no preset first type SQL execution plan consistent with the target execution plan in the plurality of preset first type SQL execution plans, a plurality of historical execution plans corresponding to the target SQL script are obtained.

[0128] The target execution plan is compared with the plurality of historical execution plans one by one to generate a comparison result of the target execution plan.

[0129] According to the comparison result, a data structure inconsistent with each historical execution plan is analyzed.

[0130] The data structure inconsistent with each historical execution plan is determined as a to-be-optimized parameter of the preset first type SQL.

[0131] In one embodiment, when the processor executes the target SQL script based on the target execution plan to determine the to-be-optimized parameter of the target SQL script, the following operations are specifically performed:

[0132] A first comparison parameter of each step in the target execution plan is obtained, and the first comparison parameter at least includes a first step sequence number, a first index key, and a first table connection type.

[0133] obtaining a second comparison parameter of each step in each historical execution plan in the plurality of historical execution plans, the second comparison parameter comprising at least a second step sequence number, a second index key, and a second table connection type;

[0134] comparing the first step sequence number with each second step sequence number to determine whether the first step sequence number and each second step sequence number are consistent, and generating a first result;

[0135] comparing the first index key with each second index key to determine whether the first index key and each second index key are consistent, and generating a second result;

[0136] comparing the first table connection type with each second table connection type to determine whether the first table connection type and each second table connection type are consistent, and generating a third result;

[0137] determining the first result, the second result, and the third result as a comparison result of the target execution plan.

[0138] In one embodiment, the processor performs the following operations when intercepting and parsing the data operation request to generate the database connection information:

[0139] intercepting the data operation request;

[0140] determining an identifier of a target database of the data operation request;

[0141] locally parsing a plurality of target fields in a packet header of the data operation request;

[0142] determining a database type according to the identifier, and calling a corresponding parsing program in a cloud service program library based on the database type;

[0143] parsing a field in a packet body of the data operation request according to the parsing program and in combination with the plurality of target fields;

[0144] mapping and associating the database connection information from the field in the packet body.

[0145] In one embodiment, the processor further performs the following operations:

[0146] obtaining an execution result of the target SQL script after execution of the target SQL script ends;

[0147] mapping a client encoding from the field in the packet body;

[0148] converting the client encoding to obtain a physical address of the client;

[0149] encapsulating the execution result into a response packet, and responding the response packet to the client according to the physical address.

[0150] In the embodiment of the present application, the intelligent processing system of the SQL statement first intercepts and analyzes the data operation request when detecting the data operation request for the database, generates the database connection information, then records the execution time length of the target SQL script corresponding to the data operation request in real time, and determines whether the target SQL script is a preset first type SQL based on the execution time length. Secondly, when the target SQL script is the preset first type SQL, the target execution plan of the target SQL script is determined according to the database connection information, and finally the to-be-optimized parameter of the target SQL script is determined based on the target execution plan, and the to-be-optimized parameter is sent to the optimization server to optimize the SQL statement of the target SQL script. Since the preset first type SQL is determined by real-time interception of the data operation request in the present application, when it is determined that the target SQL script corresponding to the request is the preset first type SQL, the target execution plan at the current time is created to determine the to-be-optimized parameter for SQL optimization, so that each data request corresponds to the current latest execution plan, thereby realizing accurate reproduction and optimization of the preset first type SQL, and further improving the stability of the system.

[0151] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by a computer program instructing related hardware. The computer program can be stored in a computer readable medium. When the program is executed, it can include the processes of the above-mentioned embodiments of each method. The aforementioned medium can be a non-volatile medium such as a magnetic disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0152] The technical features of the above embodiments can be combined in any way. To make the description concise, not all possible combinations of the technical features in the above embodiments are described, but as long as the combinations of the technical features do not exist contradictory, they should be considered as the scope of the present application.

[0153] The above embodiments only express several implementation manners of the present application, and the description is more specific and detailed, but it should not be understood as a limitation on the scope of the present application. It should be pointed out that for those skilled in the art, without departing from the concept of the present application, some modifications and improvements can be made, which are all within the scope of the present application. Therefore, the protection scope of the present application should be subject to the appended claims.

Claims

1. An intelligent processing method for SQL statements, characterized in that, The method includes: When a data operation request for the database is detected, the data operation request is intercepted and parsed to generate database connection information; The execution time of the target SQL script corresponding to the data operation request is recorded in real time, and the execution time is used to determine whether the target SQL script is a preset first type of SQL. When the target SQL script is a preset first type of SQL, the target execution plan of the target SQL script is determined according to the database connection information; Based on the target execution plan, the parameters to be optimized for the target SQL script are determined, and the parameters to be optimized are sent to the optimization server to optimize the SQL statements of the target SQL script. The step of determining the parameters to be optimized for the target SQL script based on the target execution plan includes: In a pre-generated library of preset first-type SQL execution plans, multiple preset first-type SQL execution plans are traversed. When no preset first-type SQL execution plan that matches the target execution plan is found among the multiple preset first-type SQL execution plans, multiple historical execution plans corresponding to the target SQL script are obtained. The target execution plan is compared with each of the multiple historical execution plans one by one to generate a comparison result of the target execution plan. Based on the comparison result, the data structures that are inconsistent between the target execution plan and each historical execution plan are analyzed. The data structures that are inconsistent between the target execution plan and each historical execution plan are determined as the parameters to be optimized for the preset first-type SQL.

2. The method according to claim 1, characterized in that, The step of determining whether the SQL script belongs to a preset first type of SQL based on the execution time includes: When the execution time exceeds a preset time threshold, the target SQL script is identified as a preset first type of SQL. or, When the execution time is less than or equal to a preset time threshold, the target SQL script is determined to be a preset second type of SQL.

3. The method according to claim 1, characterized in that, Determining the target execution plan for the target SQL script based on the database connection information includes: Obtain the original SQL script expression based on the database connection information; Obtain the target keywords used to generate the execution plan; The target keyword is concatenated at a preset position in the original SQL script expression to obtain the target expression; The target expression is executed, and a target execution plan is generated in the database using all parameters of the data operation request.

4. The method according to claim 1, characterized in that, The step of comparing the target execution plan with each of the multiple historical execution plans to generate a comparison result for the target execution plan includes: Obtain the first comparison parameter for each step in the target execution plan, wherein the first comparison parameter includes at least the first step number, the first index key, and the first table join type; Obtain the second comparison parameter for each step in each of the multiple historical execution plans, wherein the second comparison parameter includes at least the second step number, the second index key, and the second table join type; The first step number is compared with each second step number to determine whether the first step number and each second step number are consistent, and a first result is generated. The first index key is compared with each second index key to determine whether the first index key and each second index key are consistent, and a second result is generated. The first table join type is compared with each second table join type to determine whether the first table join type and each second table join type are consistent, and a third result is generated. The first, second, and third results are determined as the comparison results of the target execution plan.

5. The method according to claim 1, characterized in that, The interception and parsing of the data operation request to generate database connection information includes: Intercept the data operation request; Determine the identifier of the target database for the data operation request; Locally parse multiple target fields in the header of the data operation request; The database type is determined based on the identifier, and the corresponding parsing program is called from the cloud service program library based on the database type; According to the parsing procedure, the fields in the message body of the data operation request are parsed in combination with multiple target fields; Database connection information is mapped and associated in the fields of the message body.

6. The method according to claim 5, characterized in that, The method further includes: After the target SQL script has finished executing, obtain the execution result of the target SQL script; The client encoding is mapped into the fields in the message body; The client encoding is converted to obtain the client's physical address; The execution result is encapsulated into a response message, and the response message is sent to the client according to the physical address.

7. An intelligent processing system for SQL statements implemented using the method described in any one of claims 1-6, characterized in that, The system includes: The database connection information generation module is used to intercept and parse the data operation request when a data operation request for the database is detected, and generate database connection information. A preset first type SQL determination module is used to record the execution time of the target SQL script corresponding to the data operation request in real time, and determine whether the target SQL script is a preset first type SQL based on the execution time; The target execution plan determination module is used to determine the target execution plan of the target SQL script based on the database connection information when the target SQL script is a preset first type of SQL. The SQL statement optimization module is used to determine the parameters to be optimized of the target SQL script based on the target execution plan, and send the parameters to be optimized to the optimization server to optimize the SQL statements of the target SQL script.

8. A computer device, characterized in that, It includes a memory and a processor, wherein the memory stores computer-readable instructions that, when executed by the processor, cause the processor to perform the steps of the intelligent processing method for the SQL statement as claimed in any one of claims 1 to 6.

9. A medium storing computer-readable instructions, characterized in that, When the computer-readable instructions are executed by one or more processors, the one or more processors perform the steps of the intelligent processing method for the SQL statement as claimed in any one of claims 1 to 6.

Images