CN116089446A

CN116089446A - Optimization control method and device for structured query statement

Info

Publication number: CN116089446A
Application number: CN202211165246.5A
Authority: CN
Inventors: 唐梦杰
Original assignee: Qingdao Hisense Smart Life Technology Co Ltd
Current assignee: Qingdao Hisense Smart Life Technology Co Ltd
Priority date: 2022-09-23
Filing date: 2022-09-23
Publication date: 2023-05-09

Abstract

The application provides an optimization control method and device for a structured query statement, which are used for automatically identifying and modifying an SQL statement of the structured query statement with abnormal optimization, and reducing labor cost. The method comprises the following steps: acquiring the number of a plurality of service requests received in a set time and the execution time of each structured programming language SQL statement executed when any service request is responded; determining one or more abnormal SQL sentences in each SQL sentence according to the quantity and the execution duration of each SQL; the execution duration of the abnormal SQL statement is longer than a set threshold value; matching any abnormal SQL statement with a plurality of SQL statements contained in the SQL template library to obtain a target SQL statement successfully matched with any abnormal SQL statement; and optimizing and modifying any abnormal SQL statement by adopting an optimization scheme corresponding to the target SQL statement.

Description

Optimization control method and device for structured query statement

Technical Field

The present disclosure relates to the field of computer technologies, and in particular, to an optimization control method and apparatus for a structured query statement.

Background

At present, the application of micro services is wider and wider, and as the number of the micro services is increased along with the increase of the business and the user quantity, the difficulty in monitoring and operation and maintenance of the micro services is correspondingly increased, and particularly, the monitoring of structured program language (Structured Query Language, SQL) sentences of each micro service and the management of SQL sentence execution logs are also increased. The execution of the SQL statement is an important item of operation and maintenance, and an operator can timely locate the problem of the micro service through the monitoring of the process executed by the SQL statement and the management of the SQL statement execution log. In the related art, the monitoring of SQL sentences and the management of execution logs are realized through a file and other architectures, but the problem positioning and analysis optimization of SQL sentences for executing exception are lacked. In the related art, the execution log of the SQL statement is required to be analyzed and the abnormal SQL statement is optimized by an operation and maintenance personnel, so that the labor cost is high and the problem solving efficiency is low.

Disclosure of Invention

The embodiment of the application provides an optimization control method and device for a structured query statement, which are used for automatically identifying and modifying an SQL statement of the structured query statement with abnormal optimization, and reducing labor cost.

In a first aspect, an embodiment of the present application provides an optimization control method for a structured query statement, including:

acquiring the number of a plurality of service requests received in a set time and the execution time of each structured programming language SQL statement executed when any service request is responded;

determining one or more abnormal SQL sentences in each SQL sentence according to the quantity and the execution duration of each SQL; the execution duration of the abnormal SQL statement is longer than a set threshold;

matching any abnormal SQL statement with a plurality of SQL statements contained in an SQL template library to obtain a target SQL statement successfully matched with any abnormal SQL statement;

and optimizing and modifying any abnormal SQL statement by adopting an optimization scheme corresponding to the target SQL statement.

Based on the scheme, the method and the device provide an automatic SQL statement optimization scheme, and compared with the existing device and other architectures which can only realize the monitoring and management of SQL statements, the method and the device require operation and maintenance personnel to manually locate the problem of the SQL statements and modify the problem SQL statements, the method and the device provide automatic implementation of problem location of abnormal SQL statements on the basis of monitoring and management, and optimize the abnormal SQL statements by adopting a preconfigured SQL template library. Under the condition that excessive intervention of operation and maintenance personnel is not needed, automatic optimization of SQL sentences is realized, not only is manual workload reduced, but also abnormal SQL sentences can be repaired in time, so that the execution efficiency of the SQL sentences is improved.

In one or more embodiments, the determining one or more abnormal SQL statements in each SQL statement according to the number and the execution duration of each SQL statement includes:

and determining any SQL statement as an abnormal SQL statement when the number of the service requests is not greater than a preset value and the execution duration of the any SQL statement is greater than a set threshold value aiming at any SQL statement in the SQL statements.

Based on the scheme, whether the set time is the flow peak period or not is determined according to the number of the service requests, so that the influence factor of long SQL statement execution time caused by the flow peak period is eliminated, whether the SQL statement is abnormal or not is judged based on the execution time of the SQL statement, and the accuracy of abnormality judgment is improved.

In one or more embodiments, the method further comprises:

executing any optimized abnormal SQL statement;

determining an optimized result of any abnormal SQL statement according to a comparison result of the optimized execution duration of any abnormal SQL statement and the execution duration of any abnormal SQL statement before optimization; the optimization result is used for indicating whether the optimization of any abnormal SQL statement is successful or not;

And storing the optimized result of any abnormal SQL statement.

In one or more embodiments, matching any abnormal SQL statement with a plurality of SQL statements included in an SQL template library to obtain a target SQL statement successfully matched with the any abnormal SQL statement, including:

splitting any abnormal SQL statement to obtain a plurality of characteristic fields included in the any abnormal SQL statement;

matching the feature fields with feature fields of a plurality of SQL sentences contained in the SQL template library one by one;

and outputting the SQL sentence which is the same as the characteristic fields included in any abnormal SQL sentence as the target SQL sentence.

In one or more embodiments, before determining one or more abnormal SQL statements in the SQL statements according to the number and the execution duration of the SQL statements, the method further comprises:

counting each SQL statement executed when responding to any service request;

adding a service identifier of any service request for each SQL statement; the service identifier is used to characterize the source and type of any service request.

In one or more embodiments, the method further comprises:

When a problem SQL statement exists in the SQL statements except the one or more abnormal SQL statements, determining a service request to which the problem SQL statement belongs according to a service identifier corresponding to the problem SQL statement;

acquiring a log generated in the process of responding to the service request to which the problem SQL statement belongs;

and extracting information related to executing the problem SQL statement from the log, and sending the extracted information to the operation and maintenance equipment.

In one or more embodiments, the method further comprises:

outputting service abnormality information when the number of the service requests is larger than a preset value and the execution duration of any SQL sentence is larger than the set threshold value; the service anomaly information is used for representing insufficient service resources.

In a second aspect, an embodiment of the present application provides an optimization control device for a structured query statement, including:

the acquisition unit is used for acquiring the number of the plurality of service requests received in the set time and the execution time of each structured programming language (SQL) statement executed when any service request is responded;

a processing unit configured to perform:

In one or more embodiments, the processing unit is specifically configured to:

In one or more embodiments, the processing unit is further configured to:

executing any optimized abnormal SQL statement;

and storing the optimized result of any abnormal SQL statement.

In one or more embodiments, the processing unit is specifically configured to:

In one or more embodiments, the processing unit is further configured to:

counting each SQL statement executed when responding to any service request;

In one or more embodiments, the processing unit is further configured to determine, when a problem SQL statement exists in the SQL statements other than the one or more abnormal SQL statements included in each SQL statement, a service request to which the problem SQL statement belongs according to a service identifier corresponding to the problem SQL statement;

the acquiring unit is further configured to acquire a log generated in a process of responding to a service request to which the problem SQL statement belongs;

the processing unit is further used for extracting information related to executing the problem SQL statement in the log and sending the extracted information to the operation and maintenance equipment.

In one or more embodiments, the processing unit is further configured to:

In a third aspect, the present application provides an electronic device, comprising:

a memory for storing program instructions;

a processor for invoking program instructions stored in the memory and executing the steps comprised by the method according to any of the first aspects in accordance with the obtained program instructions.

In a fourth aspect, the present application provides a computer readable storage medium storing a computer program comprising program instructions which, when executed by a computer, cause the computer to perform the method of any one of the first aspects.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, and it is obvious that the drawings in the following description are some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort to a person skilled in the art.

Fig. 1 is a schematic diagram of a system architecture according to an embodiment of the present application;

FIG. 2 is a schematic diagram of another system architecture according to an embodiment of the present disclosure;

FIG. 3 is a flowchart of an optimization control method for a structured query statement according to an embodiment of the present application;

fig. 4 is a flowchart of a method for information collection according to an embodiment of the present application;

FIG. 5 is a flowchart of another method for information collection according to an embodiment of the present disclosure;

FIG. 6 is a flowchart of a method for locating problems in SQL statements according to an embodiment of the present application;

FIG. 7 is a flowchart of another method for locating problems in SQL statements according to an embodiment of the present application;

FIG. 8 is a matching flow chart of an SQL statement provided in an embodiment of the present application;

FIG. 9 is a matching flow chart of another SQL statement provided in an embodiment of the present application;

FIG. 10 is a flowchart of an optimization verification method according to an embodiment of the present application;

FIG. 11 is a flowchart of another method for optimizing control of a structured query statement according to an embodiment of the present application;

fig. 12 is a schematic structural diagram of an optimizing control device for a structured query statement according to an embodiment of the present application;

fig. 13 is a schematic structural diagram of an electronic device according to an embodiment of the present application.

Detailed Description

For the purposes of making the objects, technical solutions and advantages of the present application more apparent, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application, and it is apparent that the described embodiments are only some embodiments of the present application, but not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art without undue burden from the present disclosure, are within the scope of the present disclosure. Embodiments and features of embodiments in this application may be combined with each other arbitrarily without conflict. Also, while a logical order of illustration is depicted in the flowchart, in some cases the steps shown or described may be performed in a different order than presented.

The terms first and second in the description and claims of the present application and in the above-described figures are used for distinguishing between different objects and not for describing a particular sequential order. For example, a process, method, system, article, or apparatus that comprises a list of steps or elements is not limited to only those listed steps or elements but may include other steps or elements not listed or inherent to such process, method, article, or apparatus. The term "plurality" in the present application may mean at least two, for example, two, three or more, and embodiments of the present application are not limited.

Next, a system architecture to which the present application relates is described. Referring to fig. 1, a schematic system architecture is provided in an embodiment of the present application. It should be understood that the embodiments of the present application are not limited to the system shown in fig. 1, and the apparatus in fig. 1 may be hardware, software that is functionally divided, or a combination of the two. As shown in fig. 1, the system architecture provided in the present application includes a client and a server.

Alternatively, the client shown in fig. 1 may be a terminal device used by a user, and examples of some clients are: a mobile phone, a tablet, a notebook, a palm, a mobile internet device (Mobile Internet Device, MID), a wearable device, a Virtual Reality (VR) device, an augmented Reality (Augmented Reality, AR) device, a wireless terminal in industrial control (Industrial Control), a wireless terminal in unmanned (self driving), a wireless terminal in teleoperation (remote medical surgery), a wireless terminal in smart grid (smart grid), a wireless terminal in transportation security (transportation safety), a wireless terminal in smart city (smart city), a wireless terminal in smart home (smart home), and the like.

The server shown in fig. 1 may be a server for providing a micro service, and may include a processor, a hard disk, a memory, a system bus, etc. for responding to a service request from a client, performing a corresponding operation according to the service request, and providing a service for the client. Alternatively, the functions of the server shown in fig. 1 may be implemented by one server, or may be implemented by a server cluster, which is not limited in this application.

It should be noted that, the number of the clients and the server included in the system architecture is not limited in this application, for example, the server may provide services for a plurality of clients. Fig. 1 is provided as an example only.

Optionally, the system architecture related to the application can further include a terminal device of an operation and maintenance personnel. For example, referring to fig. 2, another system architecture diagram provided for the present application may be referred to. Optionally, the terminal device of the operation and maintenance personnel shown in fig. 2 may include a display screen, configured to display an execution log of the service end when the service end provides the service, so that the operation and maintenance personnel can analyze the log positioning problem. It should be noted that, the number of the service end, the client end and the terminal device of the operation and maintenance personnel included in the system architecture is not limited in this application, and fig. 2 is only an example.

With the popularity of micro-service frameworks and the rise of containerized deployments, more and more enterprises choose micro-service frameworks as business supports. The micro-service can meet the requirements of rapid development and efficient development of the service, and by using the container technology, a virtual space which is isolated from each other and does not affect each other can be created in one machine, and the micro-service is deployed through the container, so that the cost and the cost of deployment of the micro-service application can be greatly reduced. The micro-service architecture brings convenience to enterprises and also brings some disadvantages. As the number of services and users increases, the number of micro services increases, and the difficulty in maintenance and monitoring increases, especially in the monitoring and management of SQL statements executed by each micro service in response to a service request from a client. The operation index of the SQL statement is an important item of system operation and maintenance, and the operation and maintenance personnel can analyze whether each micro service operates normally or not through real-time SQL statement monitoring and log management, so that the problems can be positioned in time. At present, a mature framework can be realized on the monitoring and management of SQL sentences of micro services, such as a drive database connection pool, and the monitoring and log management of the SQL sentences are provided. But only realizes the automatic monitoring and management of SQL sentences, and also needs to manually assist in locating the problems of SQL sentences and modifying and optimizing. Thus, the related art lacks automatic analysis and optimization of SQL statements.

In order to solve the above problems, the present application proposes an optimization control method of a structured query statement, and proposes a method for obtaining an SQL statement in the execution process by a micro service when responding to a request from a client. Judging whether any SQL statement is abnormal according to the execution time of the SQL statement, and if the execution time exceeds a threshold value, determining that the SQL statement is abnormal. Further, the abnormal SQL statement is matched with a preconfigured SQL template library, and a target SQL statement similar to the abnormal SQL statement in the SQL template library is determined. And finally, modifying and optimizing the abnormal SQL statement by adopting a stored optimization scheme corresponding to the target SQL statement. The SQL template library may be supplemented with extensions and maintenance. Compared with the existing architectures such as drud and the like, the method and the device can only realize monitoring and management of SQL sentences, and the scheme of the application also provides problem positioning of abnormal SQL sentences on the basis of monitoring and management and optimizes the abnormal SQL sentences by adopting a preconfigured SQL template library. Under the condition that excessive intervention of operation and maintenance personnel is not needed, automatic optimization of SQL sentences is realized, not only is the manual workload reduced, but also the period of repairing SQL problems by the system is shortened.

The following specifically describes an optimization control method and device for the structured query statement proposed in the present application. In the following embodiments of the present application, "and/or" describes an association relationship of an association object, which means that there may be three relationships, for example, a and/or B may mean: a alone, a and B together, and B alone, wherein a, B may be singular or plural. The character "/" generally indicates that the context-dependent object is an "or" relationship. "at least one of" or the like means any combination of these items, including any combination of single item(s) or plural items(s). For example, at least one (one) of a, b or c may represent: a, b, c, a-b, a-c, b-c, or a-b-c, wherein a, b, c may be single or plural. The singular expressions "a", "an", "the" and "the" are intended to include, for example, also "one or more" such expressions, unless the context clearly indicates the contrary. And, unless specified to the contrary, the embodiments of the present application refer to the ordinal terms "first," "second," etc., as used to distinguish between multiple objects, and are not to be construed as limiting the order, timing, priority, or importance of the multiple objects. For example, the first task execution device and the second task execution device are only for distinguishing different task execution devices, and are not indicative of the difference in priority or importance level or the like of the two task execution devices.

Reference in the specification to "one embodiment" or "some embodiments" or the like means that a particular feature, structure, or characteristic described in connection with the embodiment is included in one or more embodiments of the application. Thus, appearances of the phrases "in one embodiment," "in some embodiments," "in other embodiments," and the like in the specification are not necessarily all referring to the same embodiment, but mean "one or more but not all embodiments" unless expressly specified otherwise. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless expressly specified otherwise.

Based on the system architecture, the present application provides an optimization control method for a structured query statement, referring to fig. 3, which is a flowchart of an optimization control method for a structured query statement provided in an embodiment of the present application, and optionally, the method flowchart may be executed by a server in the system architecture shown in fig. 1 or fig. 2. The method shown in fig. 3 specifically includes:

301, the number of the plurality of service requests received in the set time and the execution duration of each SQL statement executed in response to any service request are acquired.

Alternatively, the plurality of service requests may come from one client or from a plurality of clients. The server may count the number of service requests received within a set period of time, and count the execution duration of each SQL statement executed in response to the service requests.

302, determining one or more abnormal SQL sentences in each SQL sentence according to the number of service requests and the execution duration of each SQL sentence.

The execution duration of the abnormal SQL statement is longer than a set threshold.

Optionally, when judging whether any one of the SQL statements is an abnormal SQL statement, it may first judge whether the number of service requests received in the set time is greater than a preset value, if so, it indicates that the set time belongs to a data flow peak period, and the execution time of each SQL statement may be affected. If not, whether the execution duration of any SQL statement exceeds the set threshold can be further judged, and if so, any SQL statement can be judged to be an abnormal SQL statement.

Alternatively, the reason that the execution time of the abnormal SQL statement is long is mostly that the SQL statement is not reasonably designed, so that the execution is slow, for example, when the index fails, the whole table is scanned, and the slow execution SQL statement is one of the abnormal SQL statements. For ease of understanding, the description will follow with respect to the abnormal SQL statement.

303, matching any abnormal SQL statement with a plurality of SQL statements included in the SQL template library to obtain a target SQL statement successfully matched with any abnormal SQL statement.

Optionally, any abnormal SQL statement and a plurality of SQL statements included in the SQL template library can be sequentially matched, and the SQL statement containing the same field as any abnormal SQL statement in the SQL template library is used as a target SQL statement.

304, adopting an optimization scheme corresponding to the target SQL statement to perform optimization modification on any abnormal SQL statement.

Optionally, the SQL template library may further include an optimization scheme corresponding to each of the plurality of SQL statements, and after any abnormal SQL statement is matched with the target SQL statement in the SQL template library, any abnormal SQL statement may be modified according to the optimization scheme of the target SQL statement stored in the SQL template library.

For example, assume that any one of the abnormal SQL statements is a query statement for querying a table IN the database, the table having tens of millions of levels of data, query conditions IN the SQL statement are improperly applied, such as using left fuzzy matching, using "NULL" as a condition, using "OR" as a connection condition, using "IN" as an inclusion condition, using "≡! As the condition, index failure, etc., the SQL statement scans the full table when executed, resulting in its execution duration exceeding the set threshold. Firstly, the SQL template library is used for matching query conditions of the full-table scanning caused by the abnormal SQL statement, a matched target SQL statement is found, and then the query conditions of the abnormal SQL statement are optimized according to an optimization scheme of the target SQL statement, so that the full-table scanning is avoided, and the execution efficiency of the query statement can be improved.

In some embodiments, when the server obtains the number of the plurality of service requests in the set time and the execution duration of each SQL statement executed in response to the service request, the server may obtain the number according to the device log in the set time. As a possible implementation manner, the mybatis can be intercepted by a custom SQL statement interceptor, and related information of the SQL statement is collected before and after the mybatis executes each SQL statement, for example, parameters of the SQL statement, execution time of the SQL statement, execution results of the SQL statement, information of SQL statement execution abnormality and the like can be included. And, the interface section may be customized, for collecting the received related information of the plurality of service requests, for example, the name of the service request, the source of the service request, the type of the service request, and the parameters of the service request may be included.

Further, the number of service requests in the set time and the execution duration of executing each SQL sentence can be determined according to the acquired information. As an example, referring to fig. 4, a flowchart of a method for information collection according to an embodiment of the present application specifically includes:

401, setting interface section and custom SQL statement interceptor.

Optionally, an interface section can be applied to a controller method, and a custom SQL statement interceptor intercepts mybatis and is used for collecting information before and after SQL execution.

And 402, collecting related information of the received service request in a set time through the interface section.

The content of the relevant information of the specific service request may be referred to the description in the above embodiment, and will not be repeated here.

403, collecting related information of each SQL sentence executed when responding to the service request by the SQL sentence interceptor.

404, determining the number of service requests received in a set time and the execution duration of each SQL statement according to the collected related information of the service requests and the related information of each SQL statement.

In some embodiments, in order to ensure the efficiency of the micro-service to respond to the service request, the collected service request and the related information of each SQL statement may be stored asynchronously, so that the normal service of the micro-service is not affected. Alternatively, the related information collected by some middleware may be stored in a database of a third party, for example, the collected information may be asynchronously stored in a database of a third party, such as an elastiscearch, by the middleware kafka.

In one possible implementation manner, in order to record the situation of service call in the process of responding to a service request, a micro service open source link tracking component skywalking may also be adopted to record the call process between services. Alternatively, describing a service request as an example, a service identifier, such as a traceID, may be set for the service request to characterize the set of all execution threads in response to the service request. A thread identification, such as a segment id, may be set for each thread to characterize the set of all operations performed under one thread. An operation identifier, such as a span id, may be set for each operation to characterize the particular operation being performed.

Further, the processes of responding to a plurality of service requests by the service end described in the above embodiments can be combined based on the link tracking process, and the operation identifier corresponding to each SQL statement, the thread identifier to which the operation identifier belongs, and the service request identifier to which the thread identifier belongs are recorded, so that each service request can be clearly connected, and the subsequent analysis is facilitated. Specifically, referring to fig. 5, a flowchart of another method for information collection provided in an embodiment of the present application specifically includes:

501, a plurality of service requests received in a set time are acquired, and a corresponding service request identifier is added for each service request.

502, determining a plurality of threads included in response to any service request, and adding a corresponding thread identification for each thread.

503, determining a plurality of operations when executing any thread, and adding a corresponding operation identifier for each operation.

504, determining the SQL sentence executed by at least one operation in the plurality of operations, and storing the related information of the collected SQL sentence in association with the identification of the corresponding operation.

Based on the scheme, the operation corresponding to each SQL statement, the belonged thread and the belonged service request can be determined.

In some embodiments, after the number of the plurality of service requests and the execution duration of each SQL statement executed in response to the service requests are obtained, the obtained information may be further used to determine one or more abnormal SQL statements included in each SQL statement. Optionally, for any one of the SQL statements, when the number of the plurality of service requests is not greater than a preset value and the execution duration of the any one SQL statement is greater than a set threshold, it may be determined that the any one SQL statement is an abnormal SQL statement.

In some embodiments, if the number of the plurality of service requests received in the set time is greater than a preset value, the current traffic peak period may be determined, in this case, if the execution time of each SQL statement is greater than a set threshold, it indicates that the service resource of the service end is insufficient, and the service end may output service abnormality information, where the service abnormality information is used to characterize that the service resource is insufficient. For example, the server may send the service abnormality information to the terminal device of the operation and maintenance personnel, so that the problem of insufficient service resources may be solved by adjusting the load of the server, adding a server, or using a message middleware to decouple the service, and the like.

In the following, in order to further understand the problem locating manner of the SQL statement provided in the embodiment of the present application, the following description is provided with reference to specific embodiments. Referring to fig. 6, a flowchart of a problem positioning method of an SQL statement provided in an embodiment of the present application specifically includes:

601, the number of the plurality of service requests received in the set time and the execution duration of the SQL statement executed in response to the plurality of service requests are acquired.

602, judging whether the execution duration of any SQL statement is larger than a set threshold.

If so, proceed to step 603.

And if the SQL statement is not greater than the normal SQL statement, determining any SQL statement as the normal SQL statement.

603, determining whether the number of the plurality of service requests is greater than a preset value.

If so, step 604 is continued.

If not, step 605 is continued.

And 604, generating service abnormality information and sending the service abnormality information to the terminal equipment of the operation and maintenance personnel.

The service abnormality information is used for representing insufficient service resources of the service end.

605, any SQL statement is determined to be an abnormal SQL statement.

Alternatively, the reason for the long execution time of the abnormal SQL statement may be that the design of the abnormal SQL statement is not reasonable, resulting in the long execution time thereof.

In some embodiments, when the related information of the SQL statement is collected, the execution result of the SQL statement, that is, the execution success or failure of the SQL statement, may also be obtained. Further, more specific SQL statement problem positioning can be performed in combination with the execution result of the SQL statement. For easy understanding, referring to fig. 7, a flowchart of another method for locating a problem of an SQL statement according to an embodiment of the present application specifically includes:

701, the number of the plurality of service requests received in the set time and the execution duration and the execution result of the SQL statement executed in response to the plurality of service requests are acquired.

702, it is determined whether the execution result of any SQL statement indicates an execution failure.

Alternatively, the reasons for the failure of the SQL statement to execute may include a grammar error of the SQL statement writing, a database connection exception, etc.

If yes, step 703 is continued.

If not, then step 704 is continued.

703, extracting an execution log of any SQL sentence, and sending the execution log to the terminal equipment of the operation and maintenance personnel.

The operation and maintenance personnel can locate and repair the problem of any SQL statement according to the execution log.

704, judging whether the execution duration of any SQL statement is larger than a set threshold.

If so, step 705 continues.

705, determining whether the number of the plurality of service requests is greater than a preset value.

If so, step 706 is continued.

If not, step 707 is continued.

And 706, generating service abnormality information and sending the service abnormality information to the terminal equipment of the operation and maintenance personnel.

707, determining any SQL statement as an abnormal SQL statement.

In the above, the procedure of the server automatically determining the abnormal SQL sentence based on the collected service request and the related information of the SQL sentence is introduced. The following describes the process of automatic modification optimization of an abnormal SQL statement.

Optionally, the determined abnormal SQL statement may be matched with a preconfigured SQL template library, and the abnormal SQL statement is modified based on an optimization scheme corresponding to the target SQL statement obtained by matching.

In some embodiments, when matching the abnormal SQL statement with a plurality of SQL statements included in the SQL template library, the abnormal SQL statement may be split to obtain a plurality of feature fields, and match the feature fields one by one, and output, as the target SQL statement, an SQL statement identical to the plurality of feature fields included in the abnormal SQL statement. For example, referring to fig. 8, a matching flowchart of an SQL statement provided for an embodiment of the present application specifically includes:

801, splitting the abnormal SQL statement to obtain a plurality of characteristic fields included in the abnormal SQL statement.

Optionally, the plurality of feature fields may include key fields such as table names, data manipulation language (Data Manipulation Language, DML) types, where conditions and parameters, group by, order by, and left join.

The DML type is used for representing the function of the abnormal SQL statement, namely, query, addition, update or deletion. The sphere condition and parameters are used for screening data; the group by is used for grouping data and is generally used with an aggregation function; order by is used for ordering the data; left join is used for inquiring left connection of the main table and the association table, and the driving table is the main table; the corresponding right join is used for right connection query, and the driving table is a slave table; inner join is used to look up the intersection result of the master table and the associated table.

802, matching the obtained feature fields with feature fields of a plurality of SQL sentences included in the SQL template library one by one.

803, the same SQL statement as the plurality of fields included in the abnormal SQL statement is used as the target SQL statement.

In some embodiments, if the abnormal SQL statement fails to match with the plurality of SQL statements included in the SQL template library, a prompt message for indicating that the matching fails may be sent to a terminal device of the operation and maintenance personnel, to instruct the operation and maintenance personnel to perform manual modification optimization on the abnormal SQL statement. For ease of understanding, referring to fig. 9, a matching flowchart of another SQL statement provided for an embodiment of the present application specifically includes:

901, matching the abnormal SQL statement with a plurality of SQL statements contained in the SQL template library one by one.

902, it is determined whether there is a successfully matched SQL statement.

If so, step 903 is continued.

If not, then step 904 is continued.

903, taking the SQL sentence which is the same as the plurality of fields included in the abnormal SQL sentence as a target SQL sentence, and obtaining an optimization scheme corresponding to the target SQL sentence stored in the SQL template library.

And 904, sending prompt information and abnormal SQL sentences to terminal equipment of the operation and maintenance personnel.

The prompt information is used for indicating that the abnormal SQL statement fails to be matched with the SQL statement database.

905, receiving an optimization scheme of the abnormal SQL statement returned by the terminal equipment of the operation and maintenance personnel.

906, storing the abnormal SQL statement and the optimization scheme returned by the terminal equipment of the operation and maintenance personnel in the SQL template library in a correlated mode.

Optionally, after the matching is completed, if the abnormal SQL statement is successfully matched with the target SQL statement included in the SQL template library, an optimization scheme corresponding to the target SQL statement stored in the SQL template library can be obtained, and the abnormal SQL statement is modified and optimized by adopting the optimization scheme.

For example, if the abnormal SQL statement is a query statement, the reason for the abnormality is that the association table is more, which results in a long time consuming query process, the association relationship between the association tables queried by the query statement may be modified by using an optimization scheme corresponding to the query statement, so as to reduce the number of association tables, thereby improving the efficiency of the query statement.

In some embodiments, after optimizing the exception statement, the results of the optimization may be further validated. Optionally, the optimized abnormal SQL statement may be executed, the execution duration is obtained, and the obtained execution duration and the execution duration of the abnormal SQL statement before optimization are compared to determine whether the optimization is successful. Specifically, referring to fig. 10, a flowchart of an optimization verification method provided for an embodiment of the present application specifically includes:

1001, executing the optimized abnormal SQL statement, and acquiring the execution duration.

1002, calculating a time difference between the acquired execution time length and the execution time length of the abnormal SQL statement before optimization.

1003, determining an optimization result of the abnormal SQL statement according to the time difference, and storing the optimization result.

Optionally, if the time difference is greater than a preset time, the determined optimization result indicates that the optimization is successful. If the time difference is smaller than the preset time, determining that the optimization result indicates that the optimization fails.

In order to further understand the optimized control method of the SQL statement provided in the embodiment of the present application, the following description is provided with reference to specific embodiments. Referring to fig. 11, an optimization control method for an SQL statement according to an embodiment of the present application specifically includes:

1101, obtaining the number of the plurality of service requests received in the set time and the execution duration and the execution result of the SQL sentence executed when any service request is responded.

1102, it is determined whether the execution result of any SQL statement indicates an execution failure.

If yes, proceed to step 1103.

If not, then step 1104 is continued.

1103, extracting an execution log of any SQL sentence, and sending the execution log to the terminal equipment of the operation and maintenance personnel.

1104, judging whether the execution duration of any SQL statement is larger than a set threshold.

If so, step 1105 continues.

1105, determining whether the number of the plurality of service requests is greater than a preset value.

If so, step 1106 is continued.

If not, step 1107 continues.

1106, generating service abnormality information, and transmitting the service abnormality information to the terminal device of the operation and maintenance personnel.

1107, determining any SQL statement as an abnormal SQL statement.

1108, matching the abnormal SQL statement with a plurality of SQL statements included in the SQL template library one by one.

1109, judging whether there is SQL statement successfully matched.

If so, step 1110 is continued.

If not, step 1113 is continued.

1110, taking the SQL sentence which is the same as a plurality of characteristic fields included in the abnormal SQL sentence as a target SQL sentence, and acquiring an optimization scheme corresponding to the target SQL sentence stored in the SQL template library.

1111, modifying and optimizing the abnormal SQL statement by adopting the obtained optimization scheme.

1112, executing the optimized abnormal SQL statement, and determining an execution result of the abnormal SQL statement according to the execution time.

1113, sending prompt information and abnormal SQL sentences to the terminal equipment of the operation and maintenance personnel.

1114, receiving an optimization scheme of the abnormal SQL statement returned by the terminal equipment of the operation and maintenance personnel.

1115, storing the abnormal SQL statement and the optimization scheme returned by the terminal equipment of the operation and maintenance personnel in the SQL template library in a correlated manner.

Based on the same concept as the above method, referring to fig. 12, for an optimization control device 1200 of a structured query statement provided in an embodiment of the present application, the device 1200 is configured to execute each step in the above method, and in order to avoid repetition, a detailed description is omitted here. The apparatus 1200 includes: an acquisition unit 1201 and a processing unit 1202.

An obtaining unit 1201, configured to obtain the number of the plurality of service requests received in the set time, and the execution duration of each structured program language SQL statement executed in response to any service request;

a processing unit 1202 configured to perform:

In one or more embodiments, the processing unit 1202 is specifically configured to:

In one or more embodiments, the processing unit 1202 is further configured to:

executing any optimized abnormal SQL statement;

and storing the optimized result of any abnormal SQL statement.

In one or more embodiments, the processing unit 1202 is further configured to:

counting each SQL statement executed when responding to any service request;

In one or more embodiments, the processing unit 1202 is further configured to determine, when a problem SQL statement exists in the SQL statements other than the one or more abnormal SQL statements included in each SQL statement, a service request to which the problem SQL statement belongs according to a service identifier corresponding to the problem SQL statement;

The acquiring unit 1201 is further configured to acquire a log generated in a process of responding to a service request to which the problem SQL statement belongs;

the processing unit 1202 is further configured to extract information related to executing the problem SQL statement in the log, and send the extracted information to an operation and maintenance device.

In one or more embodiments, the processing unit 1202 is further configured to:

Fig. 13 shows a schematic structural diagram of an electronic device 1300 according to an embodiment of the present application. The electronic device 1300 in the embodiment of the present application may further include a communication interface 1303, where the communication interface 1303 is, for example, a network port, and the electronic device may transmit data through the communication interface 1303.

In the embodiment of the present application, the memory 1302 stores instructions executable by the at least one processor 1301, and the at least one processor 1301 may be configured to perform each step in the above method by executing the instructions stored in the memory 1302, for example, the processor 1301 may implement the functions of the acquiring unit 1201 and the processing unit 1202 in fig. 12.

Where processor 1301 is a control center of an electronic device, various interfaces and lines may be utilized to connect various portions of the entire electronic device by executing or executing instructions stored in memory 1302 and invoking data stored in memory 1302. In the alternative, processor 1301 may include one or more processing units, and processor 1301 may integrate an application controller that primarily handles operating systems and applications, etc., with a modem controller that primarily handles wireless communications. It will be appreciated that the modem controller described above may not be integrated into the processor 1301. In some embodiments, processor 1301 and memory 1302 may be implemented on the same chip, and in some embodiments they may be implemented separately on separate chips.

The processor 1301 may be a general-purpose controller, such as a central processing unit (english: central Processing Unit; abbreviated as CPU), digital signal controller, application specific integrated circuit, field programmable gate array or other programmable logic device, discrete gate or transistor logic device, discrete hardware components, that may implement or perform the methods, steps, and logic blocks disclosed in the embodiments of the present application. The general purpose controller may be a microcontroller or any conventional controller or the like. The steps performed by the data statistics platform disclosed in connection with the embodiments of the present application may be performed directly by a hardware controller, or performed by a combination of hardware and software modules in the controller.

The memory 1302, which is a non-volatile computer-readable storage medium, may be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. The Memory 1302 may include at least one type of storage medium, and may include, for example, flash Memory, a hard disk, a multimedia card, a card-type Memory, a random access Memory (english: random Access Memory, abbreviated as "RAM"), a static random access Memory (english: static Random Access Memory, abbreviated as "SRAM"), a programmable Read-Only Memory (english: programmable Read Only Memory, abbreviated as "PROM"), a Read Only Memory (english: ROM), a charged erasable programmable Read-Only Memory (english: electrically Erasable Programmable Read-Only Memory, abbreviated as "EEPROM"), a magnetic Memory, a magnetic disk, an optical disk, and the like. Memory 1302 is any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer, but is not limited to such. The memory 1302 in the present embodiment may also be circuitry or any other device capable of implementing a memory function for storing program instructions and/or data.

By programming the processor 1301, for example, codes corresponding to the neural network model training method described in the foregoing embodiment may be cured into the chip, so that the chip can execute the steps of the neural network model training method when running, and how to program the processor 1301 is a technology known to those skilled in the art will not be repeated here.

It will be appreciated by those skilled in the art that embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems) and computer program products according to the application. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a controller of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the controller of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

While preferred embodiments of the present application have been described, additional variations and modifications in those embodiments may occur to those skilled in the art once they learn of the basic inventive concepts. It is therefore intended that the following claims be interpreted as including the preferred embodiments and all such alterations and modifications as fall within the scope of the application.

It will be apparent to those skilled in the art that various modifications and variations can be made in the present application without departing from the spirit or scope of the application. Thus, if such modifications and variations of the present application fall within the scope of the claims and the equivalents thereof, the present application is intended to cover such modifications and variations.

Claims

1. An optimization control method for a structured query statement is characterized by comprising the following steps:

2. The method of claim 1, wherein the determining one or more abnormal SQL statements in each SQL statement based on the number and the execution duration of each SQL statement comprises:

3. The method according to claim 1 or 2, characterized in that the method further comprises:

executing any optimized abnormal SQL statement;

and storing the optimized result of any abnormal SQL statement.

4. The method according to claim 1 or 2, wherein matching any abnormal SQL statement with a plurality of SQL statements included in an SQL template library to obtain a target SQL statement successfully matched with the any abnormal SQL statement comprises:

5. The method of claim 1 or 2, wherein prior to determining one or more abnormal SQL statements in the SQL statements based on the number and the execution duration of the SQL statements, the method further comprises:

counting each SQL statement executed when responding to any service request;

6. The method of claim 5, wherein the method further comprises:

7. The method according to claim 2, wherein the method further comprises:

8. An optimization control device for structured query statement, comprising:

a processing unit configured to perform:

9. The apparatus according to claim 8, wherein the processing unit is specifically configured to:

10. An electronic device, comprising:

a memory for storing program instructions;

a processor for invoking program instructions stored in the memory and for performing the steps comprised in the method according to any of claims 1-7 in accordance with the obtained program instructions.