Slow SQL processing method and related equipment
By extracting and analyzing SQL statements during the development phase and using a pre-trained model to identify slow SQL statements, the stability issues in the online environment were resolved, enabling timely detection and handling, and improving the system's operational stability and user experience.
Patent Information
- Application Number
- CN202410801169.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-06-20
- Publication Date
- 2025-12-26
AI Technical Summary
In existing technologies, slow SQL statements can easily cause system crashes when running in online environments, and problem management is lagging behind, affecting user experience and system stability.
During the development phase, SQL statements are extracted from the code to be verified, executable SQL statements are constructed, execution plans are obtained, and pre-trained analysis models are used for processing. A third prediction score is determined by fusing the first and second prediction scores, and slow SQL statements are identified and alerted.
By promptly identifying and addressing slow SQL statements during the development phase, system stability and user experience were improved, preventing online environment crashes and governance delays.
Smart Images
Figure CN121210291A_ABST
Abstract
Description
Technical Field
[0001] This disclosure relates to the field of computer technology, and in particular to a slow SQL processing method, a slow SQL processing device, an electronic device, a computer-readable storage medium, and a computer program product. Background Technology
[0002] The current development process involves submitting the completed program code from the development tools to the automated compilation and deployment system for testing and verification. After verifying that the functionality is normal, the program code is deployed to the production environment in the same way to complete the feature launch. During user use, continuous access pressure causes the database to generate a large number of Structured Query Language (SQL) statements with excessively long query times, resulting in slow SQL statements and degrading the user experience.
[0003] In related technologies, additional monitoring programs are typically used to statistically analyze slow SQL queries. However, identifying slow SQL queries during online operation can easily lead to system crashes, service outages, poor efficiency, and processing delays.
[0004] It should be noted that the information disclosed in the background section above is only used to enhance the understanding of the background of this disclosure, and therefore may include information that does not constitute prior art known to those skilled in the art. Summary of the Invention
[0005] This disclosure provides a method and related equipment for processing slow SQL, which at least to some extent overcomes the problems of poor efficiency and slow processing speed in related technologies.
[0006] Other features and advantages of this disclosure will become apparent from the following detailed description, or may be learned in part from practice of this disclosure.
[0007] According to one aspect of this disclosure, a slow SQL processing method is provided, comprising: extracting SQL statements from code to be verified before running the program code online, constructing an executable SQL statement corresponding to the SQL statement; obtaining an execution plan for the executable SQL statement, and obtaining a first prediction score for the SQL statement based on the execution plan; processing the executable SQL statement using a pre-trained analysis model to obtain a second prediction score for the SQL statement; obtaining a third prediction score for the SQL statement based on the first prediction score and the second prediction score, and identifying SQL statements whose third prediction scores satisfy preset conditions as slow SQL statements.
[0008] In one embodiment, obtaining a third predicted score for the SQL statement based on the first and second predicted scores includes: fusing the first and second predicted scores to obtain a third predicted score for the SQL statement, wherein the weights of the first and second predicted scores are determined based on the amount of data in the training dataset of the analysis model.
[0009] In one embodiment, obtaining the execution plan of the executable SQL statement and obtaining a first prediction score of the SQL statement based on the execution plan includes: determining a rule threshold for the SQL statement based on the execution plan of the executable SQL statement; and determining a first prediction score of the SQL statement based on the relationship between the SQL statement and the rule threshold.
[0010] In one embodiment, the step of processing the executable SQL statement using a pre-trained analysis model to obtain a second prediction score for the SQL statement includes: obtaining SQL features based on the executable SQL statement; obtaining table creation statements and environment parameters; and using the analysis model to perform model calculation and classification on the SQL features based on the table creation statements and environment parameters to obtain slow SQL statements and their corresponding second prediction scores.
[0011] In one embodiment, extracting the SQL statement from the code to be verified and constructing an executable SQL statement corresponding to the SQL statement includes: custom configuration, the configuration information including at least one of establishing a free tenant, constructing a tenant private configuration, and rules; dividing and defining the namespace according to the information; matching code scanning according to the rules; extracting the SQL statement from the code to be verified, and constructing parameter simulation for the input parameters of the SQL statement to obtain the executable SQL statement; the method further includes: using multi-threading to process the executable SQL statement corresponding to the code to be verified, wherein the code to be verified corresponds to multiple executable SQL statements.
[0012] In one embodiment, the analysis model is trained by: acquiring a training dataset, which includes slow SQL statements collected in the R&D environment and slow SQL statements collected in the production environment, along with their corresponding prediction score labels; and training the analysis model to be trained based on the training dataset until convergence, thereby obtaining the pre-trained analysis model.
[0013] In one embodiment, determining an SQL statement whose third predicted score meets a preset condition as a slow SQL statement includes: if the third predicted score is greater than or equal to a preset score threshold, then determining the SQL statement as a slow SQL statement.
[0014] In one embodiment, the method further includes: analyzing the slow SQL statement to obtain optimization suggestions; and broadcasting an early warning report and the optimization suggestions.
[0015] According to another aspect of this disclosure, a slow SQL processing apparatus is also provided, comprising: a statement construction module, configured to extract SQL statements from the code to be verified before running on the code line to be verified, and construct an executable SQL statement corresponding to the SQL statements; a first prediction module, configured to obtain the execution plan of the executable SQL statement and obtain a first prediction score of the SQL statement based on the execution plan; a second prediction module, configured to process the executable SQL statement using a pre-trained analysis model to obtain a second prediction score of the SQL statement; and a result processing module, configured to obtain a third prediction score of the SQL statement based on the first prediction score and the second prediction score, and identify SQL statements whose third prediction scores meet preset conditions as slow SQL statements.
[0016] According to another aspect of this disclosure, an electronic device is provided, comprising: a processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to perform the above-described slow SQL processing method by executing the executable instructions.
[0017] According to another aspect of this disclosure, a computer-readable storage medium is provided having a computer program stored thereon that, when executed by a processor, implements the above-described slow SQL processing method.
[0018] According to another aspect of this disclosure, a computer program product is provided, the computer program product comprising a computer program or computer instructions, the computer program or computer instructions being loaded and executed by a processor to enable a computer to implement the slow SQL processing method described above.
[0019] In this embodiment, before the code to be verified runs online, the SQL statements in the code to be verified are extracted, and an executable SQL statement corresponding to the SQL statement is constructed; the execution plan of the executable SQL statement is obtained, and a first prediction score of the SQL statement is obtained based on the execution plan; the executable SQL statement is processed using a pre-trained analysis model to obtain a second prediction score of the SQL statement; a third prediction score of the SQL statement is obtained based on the first prediction score and the second prediction score, and the SQL statement whose third prediction score meets the preset conditions is identified as a slow SQL statement. This disclosure can promptly detect slow SQL in the program code during the development stage, promptly remind developers to handle it, and bring the discovery and management of slow SQL forward before the program code runs online, ensuring the quality of online code and the stability of program operation.
[0020] It should be understood that the above general description and the following detailed description are exemplary and explanatory only, and are not intended to limit this disclosure. Attached Figure Description
[0021] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this disclosure and, together with the description, serve to explain the principles of this disclosure. It is obvious that the drawings described below are merely some embodiments of this disclosure, and those skilled in the art can obtain other drawings based on these drawings without any inventive effort.
[0022] Figure 1 This diagram illustrates a system architecture for a slow SQL processing method provided in an embodiment of this disclosure.
[0023] Figure 2 A flowchart of a slow SQL processing method provided in an embodiment of this disclosure is shown.
[0024] Figure 3 A flowchart of another slow SQL processing method provided in an embodiment of this disclosure is shown.
[0025] Figure 4 A flowchart illustrating the training data collection method for the analysis model provided in this embodiment is shown.
[0026] Figure 5 A flowchart of another slow SQL processing method provided in an embodiment of this disclosure is shown.
[0027] Figure 6 This diagram illustrates a flowchart of yet another slow SQL processing method provided in an embodiment of this disclosure.
[0028] Figure 7 The flowchart illustrates an example of a slow SQL processing method provided in this embodiment of the present disclosure.
[0029] Figure 8 The flowchart illustrates Example 2 of a slow SQL processing method provided in this disclosure.
[0030] Figure 9 This diagram illustrates the structure of a slow SQL processing system provided in an embodiment of the present disclosure.
[0031] Figure 10 This diagram illustrates the structure of a slow SQL processing device provided in an embodiment of the present disclosure.
[0032] Figure 11 A structural block diagram of an electronic device provided in an embodiment of this disclosure is shown. Detailed Implementation
[0033] Exemplary embodiments will now be described more fully with reference to the accompanying drawings. However, these exemplary embodiments can be implemented in many forms and should not be construed as limited to the examples set forth herein; rather, they are provided so that this disclosure will be more comprehensive and complete, and will fully convey the concept of the exemplary embodiments to those skilled in the art. The described features, structures, or characteristics may be combined in any suitable manner in one or more embodiments.
[0034] Furthermore, the accompanying drawings are merely illustrative of this disclosure and are not necessarily drawn to scale. The same reference numerals in the drawings denote the same or similar parts, and therefore repeated descriptions of them will be omitted. Some block diagrams shown in the drawings are functional entities and do not necessarily correspond to physically or logically independent entities. These functional entities may be implemented in software, in one or more hardware modules or integrated circuits, or in different network and / or processor devices and / or microcontroller devices.
[0035] Figure 1 An exemplary system architecture 100 is shown that can be applied to the slow SQL processing method or slow SQL processing apparatus of the present disclosure embodiments.
[0036] like Figure 1 As shown, system architecture 100 may include terminal device 110, network 120 and server 130.
[0037] Network 120 is a medium used to provide a communication link between terminal device 110 and server 130, and can be a wired network or a wireless network. Network 120 can include various connection types, such as wired or wireless communication links or fiber optic cables, etc.
[0038] Users can use terminal device 110 to interact with server 130 via network 120 to receive or send messages.
[0039] Terminal device 110 can be any electronic device that supports web browsing, including but not limited to smartphones, tablets, displays, and desktop computers.
[0040] Various communication client applications can be installed on the terminal device 110, such as shopping applications, web browser applications, search applications, instant messaging tools, email clients, social platform software, etc. The verification code can be obtained through the communication client application.
[0041] It should be noted that the client applications installed on terminal devices 110 are the same, or are clients of the same type of application based on different operating systems. Depending on the terminal platform, the specific form of the application client can also differ; for example, the application client can be a mobile client, a PC client, etc.
[0042] Server 130 can be a server that provides various services, such as a backend management server that supports the verification code uploaded by the user using terminal device 110. The backend management server can also analyze and process the received verification code, and feed back the processing results (such as slow SQL statements in the verification code) to terminal device 110.
[0043] Optionally, server 130 can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDN (Content Delivery Network), and big data and artificial intelligence platforms.
[0044] It should be noted that the slow SQL processing method provided in this disclosure is generally executed by the server, and correspondingly, the slow SQL processing device is generally located in the server.
[0045] Those skilled in the art will know that Figure 1 The number of terminal devices, networks, and servers shown is merely illustrative; any number of terminal devices, networks, and servers can be included depending on actual needs. This disclosure does not limit the scope of the embodiments.
[0046] In related technologies, after the code is deployed and the functionality is online in the production environment, the continuous access pressure during user use can cause the database to generate a large number of SQL statements with excessively long query times, forming slow SQL statements and degrading the user experience. Typically, additional monitoring programs are used to statistically summarize slow SQL statements, and when the number of slow SQL statements reaches a preset threshold, an alert is issued to the developers.
[0047] However, existing slow SQL alerting methods have the following problems: On the one hand, the alerts are delayed. Slow SQL statements are usually discovered late in the online environment. Once the traffic surges, the system may crash and become unavailable in a short time before the developers can react. On the other hand, problem management is delayed. Slow SQL statements generated in the online environment require investigation and location of the problem in the code, which is often difficult and leads to low problem handling efficiency.
[0048] To at least partially solve the above-mentioned technical problems, this disclosure provides a slow SQL processing method. The purpose is to promptly detect slow SQL statements in the code during the development phase and provide early warning services, optimization suggestions, etc. The detection and management of slow SQL is brought forward to the online execution of the program code, thereby ensuring the quality of online code and the stability of program operation.
[0049] Specifically, before the code to be verified runs online, the SQL statements in the code to be verified are extracted, and an executable SQL statement corresponding to the SQL statement is constructed; the execution plan of the executable SQL statement is obtained, and the first prediction score of the SQL statement is obtained based on the execution plan; the executable SQL statement is processed using a pre-trained analysis model to obtain the second prediction score of the SQL statement; based on the first prediction score and the second prediction score, the third prediction score of the SQL statement is obtained, and the SQL statement whose third prediction score meets the preset conditions is identified as a slow SQL statement. This disclosure can promptly detect slow SQL in the program code during the development stage, promptly remind developers to handle it, and bring the discovery and management of slow SQL forward before the program code runs online, ensuring the quality of online code and the stability of program operation.
[0050] It should be noted that, unless otherwise specified, the embodiments of the present invention and the technical features thereof can be combined with each other.
[0051] The following detailed description of this exemplary implementation method is provided in conjunction with the accompanying drawings and embodiments.
[0052] First, this disclosure provides a slow SQL processing method, which can be executed by any system with computing power. The slow SQL processing method provided in this disclosure can be executed by a server, for example, by a slow SQL processing device configured on the server.
[0053] Figure 2 This document illustrates a flowchart of a slow SQL processing method provided in an embodiment of the present disclosure, as follows: Figure 2 As shown in the embodiments of this disclosure, the slow SQL processing method includes the following steps:
[0054] S202. Before running the code to be verified online, extract the SQL statement in the code to be verified and construct the executable SQL statement corresponding to the SQL statement.
[0055] In one embodiment, "before the code to be verified runs online" refers to any time before the code is deployed to the production environment, for example, placing the detection of SQL statements in a pre-production verification environment that matches online data.
[0056] The code to be verified can be a source file written by technicians using a language supported by development tools. It is a set of explicit rules that represent information in discrete form using characters, symbols, or signal elements. The code to be verified can use Structured Query Language (SQL), a database query and programming language used to access, query, update, and manage relational database systems. SQL statements can be written to query, insert, update, delete, or create tables. This disclosure uses a query SQL statement in the code to be verified as an example; the verification process for other types of SQL statements is similar.
[0057] Before validating the code, you can extract fragments of the SELECT query SQL statement from the Object Relational Mapping (ORM) configuration file for dynamic SQL by parsing the file. Typically, these fragments contain placeholders within the ORM specification, which help identify the specific fragment of the query statement.
[0058] Each query statement fragment can include two calling context scenarios: one where the code to be verified provides the fill parameters for the SQL statement to be queried; and another where the fill parameter fields for the SQL statement to be queried are determined by other calling entities, and are not explicitly specified. In the first case, the fill parameters can be directly extracted from the code to be verified, while in the second case, the parameters need to be combined before extraction.
[0059] Based on the query SQL statement and the obtained parameters, parameter mocking can be performed to construct a complete executable SQL statement.
[0060] In one embodiment, an executable SQL statement can be constructed as follows: Parse the ORM configuration file, extract the query Statement interface from the configuration file, obtain the context of the current Statement interface calling the code to be verified, determine whether the current Statement interface passes parameters; if so, extract the parameter object fields through adaptation and combination; if not, extract the parameters from the code to be verified, and after extracting the parameters, simulate the input parameter data to construct the executable SQL statement. The Statement interface can send SQL statements to the database, and the Statement interface is used to execute SQL statements without parameters.
[0061] S204. Obtain the execution plan of the executable SQL statement and get the first prediction score of the SQL statement based on the execution plan.
[0062] In SQL, an execution plan is a sequence of operation steps determined by the database based on the complexity and statistics of the query. It describes how the database processes the SQL query, including the data retrieval method, the retrieval used, the join type, and the order of operations. The database can display the execution plan through specific statements or functions.
[0063] For example, for SQL Server, you can use SET SHOWPLAN_TEXT ON to display the query execution plan; for SQLite, you can use EXPLAIN QUERY PLAN to display the query execution plan; and for PostgreSQL, you can use EXPLAIN or EXPLAIN ANALYZE to display the query execution plan.
[0064] In one embodiment, the first prediction score of the SQL statement is used to characterize the probability that the SQL statement is identified as a slow SQL statement, or it can characterize the probability that the SQL statement is identified as a non-slow SQL statement.
[0065] The first prediction score is determined by comparing the execution time of the SQL statement with a preset time interval. The preset time interval can be divided into multiple sub-intervals, each corresponding to a first prediction score. For example, the preset time interval may include two sub-intervals: (0, t1) and [t1, t2), where t2 > t1. The values of t1 and t2 can be determined based on the actual situation. When the execution time t ∈ (0, t1), the first prediction score is 0, indicating that the SQL statement is not a slow SQL statement; when the execution time t ∈ [t1, t2), the first prediction score is 100, indicating that the SQL statement is a slow SQL statement.
[0066] The preset duration range and the value of the first predicted score can be pre-configured in the slow SQL processing device.
[0067] It should be noted that the length of the preset duration interval, the number of duration sub-intervals, and the value of the first prediction score corresponding to each duration sub-interval are merely examples provided to illustrate the embodiments of this disclosure and should not be regarded as a limitation on the scope of protection of this disclosure. The values of each quantity can be determined according to the actual situation, and this disclosure does not make any specific limitations.
[0068] S206. The executable SQL statement is processed using a pre-trained analysis model to obtain the second prediction score of the SQL statement.
[0069] In one embodiment, the pre-trained analysis model is a fully trained analysis model capable of establishing a correspondence between executable SQL statements and second predicted scores. The analysis model can be a machine learning model built using a regression algorithm, or it can be a machine learning model such as an Artificial Neural Network (ANN), Support Vector Machine (SVM), Relevance Vector Machine (RVM), k-Nearest Neighbor (kNN), or Random Forest Regression (RFR).
[0070] The second prediction score is used to characterize the probability of identifying an SQL statement as a slow SQL statement, or it can characterize the probability of identifying an SQL statement as a non-slow SQL statement.
[0071] The executable SQL statement is input into a pre-trained analysis model, and a second prediction score is used as the output to identify the SQL statement as a slow SQL statement.
[0072] In one embodiment, the analysis model is trained based on slow SQL statements collected from the R&D environment and slow SQL statements collected from the production environment, along with their corresponding predicted score labels.
[0073] S208. Based on the first and second prediction scores, obtain the third prediction score of the SQL statement, and identify the SQL statement whose third prediction score meets the preset conditions as a slow SQL statement.
[0074] The first and second predicted scores can be expressed in percentage form or other forms, as long as the dimensions of the first and second predicted scores are consistent.
[0075] In one embodiment, the third predicted score can be obtained by directly summing and averaging the first and second predicted scores, or by weighting and summing the first and second predicted scores with corresponding weights, or by normalizing the first and second predicted scores.
[0076] The third prediction score can serve as the final basis for identifying an SQL statement as a slow SQL statement, thereby improving the reliability of SQL validation.
[0077] In one embodiment, when the first predicted score and the second predicted score characterize the SQL statement as a slow SQL statement, the above-mentioned characterization of the SQL statement whose third predicted score meets the preset condition as a slow SQL statement includes: if the third predicted score is greater than or equal to the preset score threshold, then the SQL statement is determined to be a slow SQL statement.
[0078] It should be noted that the aforementioned preset score threshold can be pre-configured in the slow SQL processing device. The preset score threshold can be configured to 90, 85, etc., depending on the actual situation. This disclosure does not impose any specific limitations.
[0079] When the first and second predicted scores indicate that an SQL statement is a non-slow SQL statement, the value of the preset score threshold can be adjusted appropriately, or an intermediate value can be determined by the first and second predicted scores, and the difference between the total score and the intermediate value can be used as the third predicted score value.
[0080] In this embodiment, before the code to be verified runs online, the SQL statements in the code to be verified are extracted, and an executable SQL statement corresponding to the SQL statement is constructed; the execution plan of the executable SQL statement is obtained, and a first prediction score of the SQL statement is obtained based on the execution plan; the executable SQL statement is processed using a pre-trained analysis model to obtain a second prediction score of the SQL statement; a third prediction score of the SQL statement is obtained based on the first prediction score and the second prediction score, and the SQL statement whose third prediction score meets the preset conditions is identified as a slow SQL statement. This disclosure can promptly detect slow SQL in the program code during the development stage, promptly remind developers to handle it, and bring the discovery and management of slow SQL forward before the program code runs online, ensuring the quality of online code and the stability of program operation.
[0081] Figure 3 A flowchart illustrating another slow SQL processing method provided in an embodiment of this disclosure is shown. Figure 2 Based on the embodiment, S208 is further refined into S2082 to limit the method for determining the third predicted score. In one embodiment, such as Figure 3 As shown, the slow SQL processing method provided in this embodiment includes S202-S206 and S2082, wherein S208, obtaining a third prediction score for the SQL statement based on a first prediction score and a second prediction score, includes:
[0082] S2082. The first and second predicted scores are merged to obtain the third predicted score of the SQL statement.
[0083] It should be noted that the specific implementation methods of S202 to S206 in this embodiment are the same as those of S202 to S206 in the previous embodiment, and will not be repeated here.
[0084] In one embodiment, the above fusion process can be a weighted summation of the first prediction score S1 and the second prediction score S2.
[0085] The third prediction score S3 can be expressed by the following formula:
[0086] S3 = αS1 + βS2 (Formula 1)
[0087] Where α is the weight of the first predicted score and β is the weight of the second predicted score.
[0088] In one embodiment, the weights of the first and second predicted scores are determined based on the amount of data in the training dataset of the analysis model. When the amount of data in the training dataset of the analysis model is small, the weight of the first predicted score can be set slightly higher and the weight of the second predicted score can be set slightly lower; as the amount of data in the training dataset of the analysis model increases and the prediction results of the analysis model become more accurate, the weight of the first predicted score can be reduced and the weight of the second predicted score can be increased.
[0089] For example, when the prediction accuracy of the analysis model is less than 90%, the weight of the first preset score can be configured as 0.8 and the weight of the second preset score can be configured as 0.2; when the prediction accuracy of the analysis model is greater than or equal to 90%, the weight of the first preset score can be configured as 0.2 and the weight of the second preset score can be configured as 0.8.
[0090] In some embodiments, the weights of the first prediction score and the second prediction score can be determined based on the data value range in which the prediction accuracy of the analysis model falls.
[0091] In this embodiment of the disclosure, a third prediction score is obtained by fusing the first prediction score and the second prediction score, and the weights of the first prediction score and the second prediction score are dynamically adjusted according to the prediction accuracy of the analysis model, thereby ensuring the accuracy of SQL statement validation and improving the processing efficiency of slow SQL.
[0092] In one embodiment, the above-mentioned S204 of obtaining the execution plan of the executable SQL statement and obtaining the first prediction score of the SQL statement based on the execution plan includes: determining the rule threshold of the SQL statement based on the execution plan of the executable SQL statement; and determining the first prediction score of the SQL statement based on the relationship between the SQL statement and the rule threshold.
[0093] The execution plan of each SQL statement is analyzed, the analysis results are compiled and summarized, the execution time of each SQL statement is determined, and the execution time of each SQL statement is classified and scored according to the matching rules to obtain the first prediction score of each SQL statement, so as to provide users with a more user-friendly result display.
[0094] In one embodiment, S206 above uses a pre-trained analysis model to process the executable SQL statement and obtain a second prediction score for the SQL statement, including: obtaining SQL features based on the executable SQL statement; obtaining table creation statements and environment parameters; and using the analysis model to perform model calculation and classification on the SQL features based on the table creation statements and environment parameters to obtain slow SQL statements and their corresponding second prediction scores.
[0095] The SQL statement `CREATE TABLE` is used to create tables in a database. Different databases have different extensions for the `CREATE TABLE` statement to support advanced usage.
[0096] The environmental parameters can be either the pre-release verification environment parameters in the aforementioned embodiments or the online environment parameters, and the two are the same during the verification process.
[0097] In one embodiment, the process of extracting the SQL statement from the code to be verified in step S202 and constructing an executable SQL statement corresponding to the SQL statement includes: custom configuration, the configuration information including at least one of establishing a free tenant, constructing a tenant private configuration, and rules; dividing and defining the namespace according to the information; matching code scanning according to the rules; extracting the SQL statement from the code to be verified, and constructing parameter simulation for the SQL statement input parameters to obtain the executable SQL statement.
[0098] After the engine's external interface is connected, users can create free tenants, build private tenant configurations and rules, customize settings, and isolate the needs of different tenants. Users can configure the above information through the SelfDefinitionProcessor of the preprocessor engine. The aforementioned rule thresholds may include pre-configured thresholds and other information in this embodiment.
[0099] Namespaces are divided according to custom configurations, with each user assigned at least one namespace, and a correspondence is established between namespaces and users or tenants.
[0100] Based on custom rules, the code and code snippets in each tenant's namespace are actively scanned and read. This incremental code snippet scanning is completed based on the event monitoring bus provided by the engine. The aforementioned incremental code snippets can be used as code to be verified.
[0101] After code scanning, the preprocessor engine automatically extracts the SQL statements from the code to be verified. Based on the context, it constructs mock parameters for the SQL statements, fills in the parameters, and then obtains the executable SQL statement.
[0102] Connecting the system to the database data source specified by the application allows for the provision of configurable data sources to the system based on the needs of each application.
[0103] In one embodiment, multi-threading is used to process the executable SQL statements corresponding to the code to be verified, and the code to be verified corresponds to multiple executable SQL statements. It should be noted that after scanning the code to be verified, multiple SQL statements may be obtained, resulting in multiple executable SQL statements. In this case, the SQL statements are isolated from each tenant. By using multi-threading to process multiple executable SQL statements, performance can be improved, detection time can be reduced, and multi-threaded concurrent services can be provided for detection within a tenant.
[0104] In one embodiment, the analysis model is trained by: acquiring a training dataset, which includes slow SQL statements collected from the R&D environment and slow SQL statements collected from the production environment, along with their corresponding prediction score labels; and training the analysis model to be trained based on the training dataset until convergence, thereby obtaining a pre-trained analysis model.
[0105] It should be noted that during the training process of the model to be trained, the training results are output. The training results include the current loss function value and the number of training iterations, or the current loss function value and the training time. The model convergence conditions may include, but are not limited to: if the current loss function value is less than or equal to the preset loss function threshold and the number of training iterations reaches the preset number of training iterations threshold, or if the current loss function value is less than or equal to the preset loss function threshold and the training time reaches the preset training time threshold, then the preset training stop condition is determined to be met.
[0106] If the model fails to converge, you can continue training the model by adjusting its parameters until it converges.
[0107] The loss function of the analysis model can be the mean squared error loss function, the root mean squared error loss function, or the mean absolute error loss function, whichever is appropriate for the specific circumstances.
[0108] It should be noted that the preset loss function threshold, preset training number threshold, and preset duration threshold can be determined according to the actual situation. For example, the preset loss function threshold can be 0.2, the preset number threshold can be 1000 times, and the preset duration threshold can be 12 hours, etc. This disclosure does not impose specific limitations.
[0109] like Figure 4As shown, in one embodiment, the process of obtaining the training dataset for the analysis model mainly includes the following steps:
[0110] S401. Submit code to the tenant's private namespace.
[0111] The development tools can connect to the slow SQL processing device via plugins, thereby submitting the code to be verified to the tenant's private namespace of the slow SQL processing device.
[0112] S402, Namespace Event Bus Listener Dispatch.
[0113] Full-scale code analysis and verification is initiated by the user through the platform management interface. Incremental code analysis is initiated by the user-configured rules, based on the time points, frequencies, and scanned directories defined by the rules, and is actively triggered after the directory changes are detected by the namespace event bus. The slow SQL processing device (or slow SQL early warning platform, slow SQL processing platform, etc.) provides verification or detection services as the server.
[0114] S403, the slow SQL processing device performs detection and analysis.
[0115] The slow SQL processing unit provides a detection and analysis engine kernel and has the ability to provide services to external parties.
[0116] S404, Early Warning Report.
[0117] S405, Provide optimization suggestions.
[0118] The slow SQL processing device analyzes the acquired code to be verified, generates an early warning report, and provides optimization suggestions. The early warning report includes the slow SQL statement and its corresponding predicted score.
[0119] S406, Multi-channel result notification.
[0120] Announcements will be broadcast through various channels, including corporate email and corporate instant messaging tools.
[0121] S407. Determine if the deployment requirements are met. If yes, execute S408. If no, prevent the application from being deployed online, require developers to continue modifying the code, and return to S401 until the deployment requirements are met to ensure deployment quality.
[0122] S408, Deploy code to the production environment.
[0123] S409, Generating environment generates slow SQL statements.
[0124] The production platform promptly detects the execution effect of online SQL queries and sends the generated slow SQL statements to the slow SQL processing device. The detection and analysis engine kernel of the slow SQL processing device updates the local SQL database in a timely manner. Based on the slow SQL statements and their corresponding prediction scores in the warning report, as well as the slow SQL statements and their corresponding prediction scores obtained from the generation environment, the analysis model is trained to provide better and more accurate slow SQL analysis services.
[0125] Figure 5 A flowchart illustrating another slow SQL processing method provided in an embodiment of this disclosure is shown. Figure 5 As shown, in one embodiment, after S208 determines the SQL statement whose third predicted score meets the preset condition as a slow SQL statement, the method further includes:
[0126] S210. Update the training dataset of the analysis model based on slow SQL statements.
[0127] In one embodiment, when it is determined that there are slow SQL statements in the code to be verified, the slow SQL statements can be added to the training dataset to increase the amount of data in the training dataset and improve the training accuracy of the analysis model.
[0128] Figure 6 This diagram illustrates a flowchart of yet another slow SQL processing method provided in an embodiment of this disclosure. Figure 2 Based on the previous embodiment, S212 is added after S208 to limit the warning to slow SQL statements. In one embodiment, such as Figure 6 As shown, the slow SQL processing method provided in this embodiment includes S202-S208 and S212, wherein,
[0129] S212. Analyze slow SQL statements, obtain optimization suggestions, and broadcast warning reports and optimization suggestions.
[0130] It should be noted that the specific implementation methods of S202 to S208 in this embodiment are the same as the specific implementation processes of S202 to S208 in the foregoing embodiments, and will not be repeated here.
[0131] An Abstract Syntax Tree (AST) is a data structure used in the program compilation process to represent the syntactic structure of program code. Analyzing the AST facilitates semantic analysis, optimization, and transformation of the code to be verified.
[0132] The optimization suggestions mentioned above may include, but are not limited to, how to modify the SQL structure, how to create appropriate indexes for the SQL statement, and how to modify the structure of the table containing the index.
[0133] In one embodiment, after the code to be verified is verified, an early warning report and optimization suggestions can be generated based on the slow SQL statement. The early warning report may include information such as the slow SQL statement and its position in the code to be verified. The early warning can be issued through various channels, such as broadcasting notifications through corporate email, corporate instant messaging tools, etc.
[0134] In this embodiment, the warning report and optimization suggestions generated based on the slow SQL statement are broadcast to the user, which can notify the user to deal with or pay attention to the SQL statement in a timely manner. The discovery and management of slow SQL is brought forward before the program code runs online, so as to ensure the quality of online code and the stability of program operation.
[0135] To enhance understanding of the embodiments of this disclosure, the following description is provided in conjunction with... Figures 7-9 Please provide a detailed explanation.
[0136] Figure 7 The flowchart illustrates an example of a slow SQL processing method provided in this disclosure. Figure 7 The aforementioned method for handling slow SQL queries mainly includes the following steps:
[0137] S701, Custom Configuration. In S701, after the engine's external interface is connected, users can create free tenants, build tenant-specific configurations and rules, customize settings, and isolate the needs of different tenants. Users can configure the above information through the SelfDefinitionProcessor of the preprocessor engine. The rules mentioned above may include pre-configured thresholds and other information from the aforementioned embodiments.
[0138] S702. Namespace Partitioning and Definition. Namespaces are partitioned according to custom configurations, with each user allocated at least one namespace, and a mapping relationship is established between namespaces and users or tenants.
[0139] S703, Rule-based Code Scanning. Based on the rules defined in S701, the code and code snippets in each tenant's namespace are actively scanned and read. This incremental code snippet scanning is completed using the event monitoring bus provided by the engine. These incremental code snippets can be used as code to be verified.
[0140] In S702 and S703, the scanning code processor is mainly executed by the ScanCodeProcessor of the preprocessor engine.
[0141] S704 Extract Executable SQL Statements: After code scanning, the preprocessor engine automatically extracts SQL statements from the code to be verified.
[0142] S705, SQL Parameter Mock Construction: Based on the context, the SQL statement input parameters are simulated and mocked. After filling in the parameters, an executable SQL statement is obtained.
[0143] In S704 and S705, the code scanning processor is primarily executed by the ExtractSQLProcessor of the preprocessor engine.
[0144] S706, Connect to Data Source: Connects the system to the database data source specified by the application. It can provide configurable data sources to the system according to the needs of each application.
[0145] S707, Multi-threaded execution: Since SQL statements are isolated between tenants, multi-threaded concurrent services are provided for the verification of the code to be verified within a tenant, thereby improving performance, reducing verification time, and enhancing user experience.
[0146] S708. Obtain SQL Execution Plan: Analyze the execution plan for each SQL statement, summarize the analysis results, determine the execution time of each SQL statement, classify and score the execution time of each SQL statement according to the matching rules, obtain the first prediction score of each SQL statement, and provide users with a more user-friendly result display.
[0147] S709, Model Training and Matching: This process, as a step in the processor engine that can provide accurate slow SQL analysis, collects pre-deployment warning information (such as slow SQL statements and corresponding predicted score labels collected in the R&D environment as mentioned above) and actual slow SQL information after deployment (such as slow SQL statements and corresponding predicted score labels collected in the production environment as mentioned above). The above data can be used as the training dataset for the analysis model to train the analysis model, such as an analysis model built using a regression algorithm.
[0148] Before the code to be verified goes live, it is connected to the slow SQL platform. This allows for execution plan analysis, further calculation by the analysis model, and classification and scoring, providing users with more accurate results. As the amount of data used to train the analysis model accumulates, the prediction results become increasingly accurate.
[0149] S710 Slow SQL Optimization Suggestions: Perform AST (Abstract Syntax Tree) analysis on slow SQL statements to obtain the SQL execution results, and provide optimization suggestions based on the SQL statement execution process.
[0150] It should be noted that the S706 to S710 mentioned above can be executed by the processor engine.
[0151] S711, Format verification and matching.
[0152] S712, Output the results.
[0153] It should be noted that the above S711 to S712 can be executed by the post-processor engine. This part mainly consists of configurable output format verification, result output, broadcast distribution, etc., providing result output capability.
[0154] Figure 8 The flowchart illustrates Example 2 of a slow SQL processing method provided in this disclosure. Figure 8 As shown, the slow SQL processing method mainly includes the following steps:
[0155] S801, Parse the ORM configuration file;
[0156] S802, Extract the SELECT query Statement;
[0157] S803, Get the current Statement invocation context;
[0158] S804. Determine if the interface is passing parameters; if not, proceed to S805; if yes, proceed to S806.
[0159] S805, extract parameters, then execute S807;
[0160] S806, Input parameter object field adaptation and combination extraction;
[0161] S807, Mock input parameter data;
[0162] S808, Construct executable SQL statements;
[0163] S809. Obtain the execution plan;
[0164] S810. Determine whether the execution time of the SQL statement meets the rule threshold. If not, determine that the SQL statement is a non-slow SQL statement and save it in the database. If yes, execute S811. The above rule threshold can be the preset time interval, time sub-interval, etc. in the aforementioned embodiment.
[0165] S811, Output the first predicted score;
[0166] S812, Obtain SQL characteristics;
[0167] S813, Obtain the table creation statement;
[0168] S814, Obtain environmental parameters;
[0169] S815. Use the analysis model to calculate the classification. If it is a slow SQL statement, then execute S816; if it is a non-slow SQL statement, then save it to the database.
[0170] S816, Output the second predicted score;
[0171] S817, Weighted calculation: The third predicted score is obtained based on the first and second predicted scores.
[0172] S818. Determine whether the warning conditions are met. If yes, proceed to S819; otherwise, proceed to S822 and discard the warning. The aforementioned warning conditions can be the preset conditions in the previous embodiments.
[0173] S819, Analyze the abstract syntax tree;
[0174] S820, Provide optimization suggestions;
[0175] S821. Result Output and Early Warning.
[0176] It should be noted that S809 to S811 are the execution steps for classifying SQL statements using the execution plan analysis method; S812 to S816 are the execution steps for classifying SQL statements using the model calculation analysis method.
[0177] An Abstract Syntax Tree (AST) is a data structure used in the program compilation process to represent the syntactic structure of program code. By analyzing the AST, semantic analysis, optimization, and transformation of the code to be verified can be easily performed.
[0178] The optimization suggestions mentioned above may include, but are not limited to, how to modify the SQL structure, how to create appropriate indexes for the SQL statement, and how to modify the structure of the table containing the index.
[0179] The SQL statement `CREATE TABLE` is used to create tables in a database. Different databases have different extensions for the `CREATE TABLE` statement to support advanced usage.
[0180] The environmental parameters can be either the pre-release verification environment parameters in the aforementioned embodiments or the online environment parameters, and the two are the same during the verification process.
[0181] Figure 9 This diagram illustrates the structure of a slow SQL processing system provided in an embodiment of this disclosure. Figure 9 As shown, the slow SQL processing system provided in this embodiment includes a data access layer, a data storage layer, an engine execution layer, a basic capability layer, and an external service layer.
[0182] The data access layer includes REST, WebSocket, and JSF interfaces, which are used to obtain user-uploaded verification codes.
[0183] The data storage layer can be used for database storage, full-text search storage, cloud storage, etc., and can store data at the corresponding storage address according to the actual situation.
[0184] The engine execution layer can include a real-time analyzer, an offline analyzer, a report generator, etc. The real-time analyzer can perform real-time analysis and verification of the code to be verified and obtain the verification results; the offline analyzer can perform offline analysis and verification of the code to be verified and obtain the verification results; the report generator is used to generate a verification report based on the verification results obtained by the real-time analyzer and the offline analyzer.
[0185] The basic capability layer has functions such as namespace, rule definition, event distribution, dynamic management, broadcast notification, and model training.
[0186] The external service layer has functions such as process management, monitoring and analysis, reporting and early warning, optimization suggestions, result presentation, and data docking. The external service layer can display the verification report generated by the engine execution layer to the user as a window for interaction with the user.
[0187] Based on the same inventive concept, this disclosure also provides a slow SQL processing device, as shown in the following embodiments. Since the principle by which this device solves the problem is similar to that of the above-described method embodiments, the implementation of this device embodiment can refer to the implementation of the above-described method embodiments, and repeated details will not be elaborated further.
[0188] Figure 10 A schematic diagram of a slow SQL processing apparatus provided in an embodiment of this disclosure is shown. Figure 10 As shown, the slow SQL processing device in this embodiment includes a statement construction module 1010, a first prediction module 1020, a second prediction module 1030, and a result processing module 1040.
[0189] Among them, the statement construction module 1010 is used to extract the SQL statement in the code to be verified before it is put into operation online, and construct the executable SQL statement corresponding to the SQL statement.
[0190] The first prediction module 1020 is used to obtain the execution plan of the executable SQL statement and obtain the first prediction score of the SQL statement based on the execution plan;
[0191] The second prediction module 1030 is used to process the executable SQL statement using a pre-trained analysis model to obtain a second prediction score for the SQL statement.
[0192] The result processing module 1040 is used to obtain the third prediction score of the SQL statement based on the first prediction score and the second prediction score, and to identify the SQL statement whose third prediction score meets the preset conditions as a slow SQL statement.
[0193] In one embodiment, the result processing module 1040 is used to fuse the first predicted score and the second predicted score to obtain a third predicted score for the SQL statement.
[0194] In one embodiment, the first prediction module 1020 is further configured to determine a rule threshold for the SQL statement based on the execution plan of the executable SQL statement; and to determine a first prediction score for the SQL statement based on the relationship between the SQL statement and the rule threshold.
[0195] In one embodiment, the second prediction module 1030 is further configured to obtain SQL features based on executable SQL statements; obtain table creation statements and environment parameters; and perform model calculation and classification on the SQL features based on the table creation statements and environment parameters to obtain slow SQL statements and the second prediction score corresponding to the slow SQL statements.
[0196] In one embodiment, the statement construction module 1010 is also used for custom configuration, the configuration information including at least one of the following: establishing a free tenant, constructing a tenant private configuration, and rules; dividing and defining the namespace according to the information; matching code scanning according to the rules; extracting SQL statements from the code to be verified; performing parameter simulation construction on the input parameters of the SQL statements to obtain executable SQL statements;
[0197] In one embodiment, the apparatus further includes a multi-threaded processing module (not shown in the figures) for multi-threaded processing of executable SQL statements corresponding to the code to be verified, wherein the code to be verified corresponds to multiple executable SQL statements.
[0198] In one embodiment, the apparatus further includes a model training module (not shown in the figures), which is used to train the analysis model by: acquiring a training dataset, which includes slow SQL statements collected in the R&D environment and slow SQL statements collected in the production environment, as well as corresponding prediction score labels; and training the analysis model to be trained based on the training dataset until convergence, thereby obtaining a pre-trained analysis model.
[0199] In one embodiment, the result processing module 1040 is used to determine that the SQL statement is a slow SQL statement if the third predicted score is greater than or equal to a preset score threshold.
[0200] In one embodiment, the device is also used to analyze slow SQL statements, obtain optimization suggestions, and broadcast warning reports and optimization suggestions.
[0201] In this embodiment, before the code to be verified runs online, the SQL statements in the code to be verified are extracted, and an executable SQL statement corresponding to the SQL statement is constructed; the execution plan of the executable SQL statement is obtained, and a first prediction score of the SQL statement is obtained based on the execution plan; the executable SQL statement is processed using a pre-trained analysis model to obtain a second prediction score of the SQL statement; a third prediction score of the SQL statement is obtained based on the first prediction score and the second prediction score, and the SQL statement whose third prediction score meets the preset conditions is identified as a slow SQL statement. This disclosure can promptly detect slow SQL in the program code during the development stage, promptly remind developers to handle it, and bring the discovery and management of slow SQL forward before the program code runs online, ensuring the quality of online code and the stability of program operation.
[0202] Those skilled in the art will understand that various aspects of the present invention can be implemented as systems, methods, or program products. Therefore, various aspects of the present invention can be specifically implemented in the following forms: entirely hardware implementations, entirely software implementations (including firmware, microcode, etc.), or implementations combining hardware and software aspects, collectively referred to herein as “circuits,” “modules,” or “systems.”
[0203] The following reference Figure 11 To describe an electronic device 1100 according to this embodiment of the present invention. Figure 11 The electronic device 1100 shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of the present invention.
[0204] like Figure 11 As shown, the electronic device 1100 is manifested in the form of a general-purpose computing device. The components of the electronic device 1100 may include, but are not limited to: at least one processing unit 1110, at least one storage unit 1120, and a bus 1130 connecting different system components (including storage unit 1120 and processing unit 1110).
[0205] The storage unit stores program code, which can be executed by the processing unit 1110 to perform the steps described in the "Exemplary Methods" section of this specification according to various exemplary embodiments of the present invention. For example, the processing unit 1110 can perform actions such as... Figure 2The process shown involves extracting SQL statements from the code to be verified before execution on the line, constructing an executable SQL statement corresponding to the SQL statement, obtaining the execution plan of the executable SQL statement, and obtaining a first prediction score for the SQL statement based on the execution plan. The executable SQL statement is then processed using a pre-trained analysis model to obtain a second prediction score for the SQL statement. Based on the first and second prediction scores, a third prediction score for the SQL statement is obtained, and SQL statements whose third prediction scores meet preset conditions are identified as slow SQL statements.
[0206] Storage unit 1120 may include a readable medium in the form of a volatile storage unit, such as random access memory (RAM) 11201 and / or cache memory 11202, and may further include a read-only memory (ROM) 11203.
[0207] Storage unit 1120 may also include a program / utility 11204 having a set (at least one) of program modules 11205, such program modules 11205 including but not limited to: operating system, one or more application programs, other program modules and program data, each or some combination of these examples may include an implementation of a network environment.
[0208] Bus 1130 can represent one or more of several types of bus structures, including a memory cell bus or memory cell controller, a peripheral bus, a graphics acceleration port, a processing unit, or a local bus using any of the various bus structures.
[0209] Electronic device 1100 can also communicate with one or more external devices 1140 (e.g., keyboard, pointing device, Bluetooth device, etc.), and with one or more devices that enable a user to interact with the system, and / or with any device that enables the electronic device 1100 to communicate with one or more other computing devices (e.g., router, modem, etc.). This communication can be performed via input / output (I / O) interface 1150. Furthermore, the system can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 1160. Figure 11 As shown, network adapter 1160 communicates with other modules of electronic device 1100 via bus 1130. It should be understood that, although... Figure 11 As not shown, other hardware and / or software modules can be used in conjunction with electronic device 1100, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.
[0210] From the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of this disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) or on a network, including several instructions to cause a computing device (such as a personal computer, server, terminal device, or network device, etc.) to execute the methods according to the embodiments of this disclosure.
[0211] In exemplary embodiments of this disclosure, a computer-readable storage medium is also provided, which may be a readable signal medium or a readable storage medium. In exemplary embodiments of this disclosure, a computer program product is also provided, comprising a computer program or computer instructions, which are loaded and executed by a processor to cause a computer to implement the steps of the methods disclosed in the above embodiments.
[0212] More specific examples of computer-readable storage media in this disclosure may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.
[0213] In this disclosure, a computer-readable storage medium may include a data signal propagated in baseband or as part of a carrier wave, carrying readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A readable signal medium may also be any readable medium other than a readable storage medium, capable of transmitting, propagating, or transmitting a program for use by or in connection with an instruction execution system, apparatus, or device.
[0214] Optionally, the program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to wireless, wired, optical fiber, RF, etc., or any suitable combination thereof.
[0215] In practical implementation, program code for performing the operations of this disclosure can be written in any combination of one or more programming languages, including object-oriented programming languages such as Java and C++, and conventional procedural programming languages such as C or similar languages. The program code can execute entirely on the user's computing device, partially on the user's computing device, as a standalone software package, partially on the user's computing device and partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing device can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computing device (e.g., via the Internet using an Internet service provider).
[0216] It should be noted that although several modules or units for the device used to perform actions have been mentioned in the detailed description above, this division is not mandatory. In fact, according to embodiments of this disclosure, the features and functions of two or more modules or units described above can be embodied in one module or unit. Conversely, the features and functions of one module or unit described above can be further divided and embodied by multiple modules or units.
[0217] Furthermore, although the steps of the method in this disclosure are described in a specific order in the accompanying drawings, this does not require or imply that the steps must be performed in that specific order, or that all the steps shown must be performed to achieve the desired result. Additional or alternative steps may be omitted, multiple steps may be combined into one step, and / or a step may be broken down into multiple steps.
[0218] From the above description of the embodiments, those skilled in the art will readily understand that the exemplary embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of this disclosure can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, USB flash drive, external hard drive, etc.) or on a network, including several instructions to cause a computing device (such as a personal computer, server, mobile terminal, or network device, etc.) to execute the methods according to the embodiments of this disclosure.
[0219] Other embodiments of this disclosure will readily occur to those skilled in the art upon consideration of the specification and practice of the invention disclosed herein. This disclosure is intended to cover any variations, uses, or adaptations of this disclosure that follow the general principles of this disclosure and include common knowledge or customary techniques in the art not disclosed herein. The specification and examples are to be considered exemplary only, and the true scope and spirit of this disclosure are indicated by the appended claims.
Claims
1. A method for processing slow SQL queries, characterized in that, include: Before the code to be verified is run online, the SQL statement in the code to be verified is extracted, and an executable SQL statement corresponding to the SQL statement is constructed. Obtain the execution plan of the executable SQL statement, and obtain the first prediction score of the SQL statement based on the execution plan; The executable SQL statement is processed using a pre-trained analysis model to obtain a second prediction score for the SQL statement; Based on the first and second prediction scores, a third prediction score is obtained for the SQL statement, and SQL statements whose third prediction scores meet preset conditions are identified as slow SQL statements.
2. The slow SQL processing method according to claim 1, characterized in that, The step of obtaining the third predicted score of the SQL statement based on the first and second predicted scores includes: The first predicted score and the second predicted score are fused together to obtain the third predicted score of the SQL statement.
3. The slow SQL processing method according to claim 1, characterized in that, The step of obtaining the execution plan of the executable SQL statement and obtaining the first prediction score of the SQL statement based on the execution plan includes: Based on the execution plan of the executable SQL statement, determine the rule threshold of the SQL statement; A first prediction score for the SQL statement is determined based on the relationship between the SQL statement and the rule threshold.
4. The slow SQL processing method according to claim 1, characterized in that, The step of processing the executable SQL statement using a pre-trained analysis model to obtain a second prediction score for the SQL statement includes: Obtain SQL characteristics based on the executable SQL statement; Retrieve the table creation statement and environment parameters; Based on the table creation statement and environmental parameters, the analysis model is used to perform model calculation and classification on the SQL features to obtain slow SQL statements and their corresponding second prediction scores.
5. The slow SQL processing method according to claim 1, characterized in that, The step of extracting the SQL statement from the code to be verified and constructing an executable SQL statement corresponding to the SQL statement includes: Custom configuration, which includes at least one of the following: creating a free tenant, building a tenant's private configuration, and rules; The namespace is divided and defined based on the information provided. Code scanning is performed according to the rules; Extract the SQL statement from the code to be verified, simulate the parameters of the SQL statement to construct the executable SQL statement; The method further includes: The executable SQL statement corresponding to the code to be verified is processed using multi-threading, and the code to be verified corresponds to multiple executable SQL statements.
6. The slow SQL processing method according to claim 1, characterized in that, The analytical model was trained in the following manner: Obtain the training dataset, which includes slow SQL statements collected from the R&D environment and slow SQL statements collected from the production environment, along with their corresponding predicted score labels. The analysis model to be trained is trained based on the training dataset until convergence, thus obtaining the pre-trained analysis model.
7. The slow SQL processing method according to claim 1, characterized in that, The step of identifying SQL statements whose third predicted scores meet preset conditions as slow SQL statements includes: If the third predicted score is greater than or equal to a preset score threshold, the SQL statement is determined to be a slow SQL statement.
8. A slow SQL statement processing device, characterized in that, include: The statement construction module is used to extract the SQL statement in the code to be verified before it is put into operation online, and to construct the executable SQL statement corresponding to the SQL statement. The first prediction module is used to obtain the execution plan of the executable SQL statement and obtain the first prediction score of the SQL statement based on the execution plan; The second prediction module is used to process the executable SQL statement using a pre-trained analysis model to obtain a second prediction score for the SQL statement. The result processing module is used to obtain a third predicted score for the SQL statement based on the first predicted score and the second predicted score, and to identify SQL statements whose third predicted scores meet preset conditions as slow SQL statements.
9. An electronic device, characterized in that, include: processor; and a memory for storing executable instructions of the processor; wherein the processor is configured to perform the slow SQL processing method as described in any one of claims 1-7 by executing the executable instructions.
10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the slow SQL processing method as described in any one of claims 1-7.