Structured query statement analysis method and device, equipment and storage medium

Abstract

The invention discloses a structured query statement analysis method and device, equipment and a storage medium. Comprising the steps of obtaining a structured query statement, and performing regularization processing on the structured query statement to generate a regularization template; sequentially selecting two templates from the regularized templates as to-be-compared templates, and determining statement similarity between the to-be-compared templates; and taking each regularized template as a to-be-analyzed template, and performing time consumption prediction on the to-be-analyzed template to generate a prediction result. Through regularization processing, the non-semantic difference of the statements can be eliminated, and the statements with the same semantics but different writing forms are converted into a unified format. By determining the sentence similarity, the structured query sentences with the same or highly similar semantics can be accurately recognized, and a reliable basis is provided for de-duplication and classification of the structured query sentences. Reliable time-consuming reference can be provided by combining historical records without depending on the execution history of the to-be-analyzed statement, and a database system is assisted to reasonably schedule resources and check performance bottlenecks.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of databases, in particular to a structured query statement analysis method, device, equipment and storage medium. BACKGROUND

[0002] In the daily operation of a large-scale database system, structured query statements, as the core tool for interacting with the database, need to bear various operations such as data query, insertion, update, deletion, etc. The stable operation of the database system depends on the reasonable prediction of the time-consuming of the execution of the structured query statement, especially in the system scheduling and operation optimization scenarios. Accurate time-consuming prediction can help to reasonably allocate resources and avoid task congestion, but it is difficult to achieve time-consuming prediction for structured query statements lacking execution history, further increasing the difficulty of system management.

[0003] In the prior art, a traditional string matching method is usually used to identify and compare structured query statements, that is, the characters of the structured query statements are directly compared to determine whether the statements are consistent. For the prediction of the time-consuming of the execution of the structured query statement, the prior art usually only relies on historical structured query statements with completely identical execution records. If there is no completely matching statement in the historical records, the time-consuming prediction cannot be carried out, or only a rough estimate is made through simple statement structure analogy, without forming a systematic prediction system, which affects the scheduling efficiency of the database system, increases the work burden of the operation and maintenance personnel, and is difficult to support intelligent operation and optimization of the system. In addition, although some technologies attempt to simply regularize the format of the structured query statement, they do not establish a comprehensive regularization processing mechanism, and can only eliminate a small amount of format differences, and cannot cover multi-dimensional non-semantic differences. SUMMARY

[0004] The present application provides a structured query statement analysis method, device, equipment and storage medium, which solves the technical problems that the traditional string matching in a large-scale database system cannot accurately identify equivalent statements with the same semantics but different syntaxes, and that structured query statements lacking execution history are difficult to predict the execution time-consuming, affecting the system scheduling and operation efficiency, by proposing a structured query statement regularization method, a hybrid structured query statement similarity calculation method and a double-mode structured query statement execution time-consuming prediction method based on fuzzy matching and accurate matching.

[0005] According to an aspect of the present application, a structured query statement analysis method is provided, which comprises:

[0006] obtaining a structured query statement, regularizing the structured query statement, and generating a regularized template;

[0007] selecting two templates from the regularized template in turn as to-be-compared templates, and determining the statement similarity between the to-be-compared templates;

[0008] Each regularized template is used as a template to be analyzed, and the time consumption of the template to be analyzed is predicted to generate a prediction result.

[0009] Optionally, determining the sentence similarity between the templates to be compared includes: respectively determining the character length of the two templates to be compared, wherein the character length includes a first character length and a second character length; calculating a first length difference between the first character length and the second character length; judging whether the first length difference is greater than a preset difference threshold, and if so, determining the sentence similarity to be 0; otherwise, judging whether the two templates to be compared are exactly the same, and if so, determining the sentence similarity to be 1, and otherwise, calculating the character similarity and word segmentation similarity between the templates to be compared, substituting the character similarity and word segmentation similarity into a first preset similarity formula, and calculating the sentence similarity.

[0010] Optionally, the character similarity and word segmentation similarity between the templates to be compared are calculated, including: calculating the character-level edit distance between the templates to be compared based on the edit distance algorithm, substituting the character-level edit distance into a second preset similarity formula to obtain character similarity; segmenting the templates to be compared according to spaces to obtain two word segmentation sequences; calculating the word segmentation-level edit distance between the word segmentation sequences based on the edit distance algorithm, substituting the word segmentation-level edit distance into a third preset similarity formula to obtain word segmentation similarity.

[0011] Optionally, a time consumption prediction is performed on the template to be analyzed and a prediction result is generated, including: reading the matching parameter value; when the matching parameter value is the first specified value, a fuzzy matching mode is used to predict the time consumption of the template to be analyzed and a prediction result is generated; when the matching parameter value is the second specified value, an exact matching mode is used to predict the time consumption of the template to be analyzed and a prediction result is generated.

[0012] Optionally, a fuzzy matching mode is used to predict the time consumption of the template to be analyzed and generate a prediction result, including: parsing the template to be analyzed, obtaining a table list arranged in alphabetical order and a third character length; obtaining historical query records, and filtering the first historical query statement that is the same as the template to be analyzed in the historical query records based on the table list; taking each first historical query statement as a candidate statement, and determining the fourth character length of the candidate statement, and calculating the second length difference between the third character length and the fourth character length; when the second length difference is less than a preset difference threshold, retaining the candidate statement and generating a candidate historical record set; calculating the sentence similarity between each candidate statement in the candidate historical record set and the template to be analyzed, and filtering out target sentences whose sentence similarity is greater than a preset similarity threshold; judging whether the number of target statements is 0, and if so, returning the prediction result as unknown; otherwise, calculating the average value of the actual execution time of each target statement as the prediction result.

[0013] Optionally, an exact matching mode is used to predict the time consumption of the template to be analyzed and generate a prediction result, including: calculating the hash value of the template to be analyzed; obtaining historical query records, and filtering the second historical query statements that are the same as the template to be analyzed in the historical query records based on the hash value; calculating the average actual execution time of each second historical query statement as the prediction result.

[0014] Optionally, regularization processing includes removing blank characters, unifying upper and lower cases, standardizing field order, unifying conditional expressions, unifying the order and format of connected tables, unifying function formats, expanding implicit statements, removing comments and redundant line breaks, and replacing constants.

[0015] According to another aspect of the present invention, a structured query statement analysis device is provided, the device comprising:

[0016] A regularized template generation module is used to obtain structured query statements, perform regularized processing on the structured query statements, and generate regularized templates;

[0017] A sentence similarity determination module is used to select two templates from the regularized templates in turn as templates to be compared, and determine the sentence similarity between the templates to be compared;

[0018] The prediction result generation module is used to take each regularized template as a template to be analyzed, perform time consumption prediction on the template to be analyzed, and generate a prediction result.

[0019] According to another aspect of the present invention, an electronic device is provided, comprising:

[0020] at least one processor;

[0021] and a memory communicatively coupled to the at least one processor;

[0022] The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute a structured query statement analysis method described in any embodiment of the present invention.

[0023] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement a structured query statement analysis method according to any embodiment of the present invention when executed.

[0024] The technical solution of the embodiment of the present invention can eliminate the non-semantic differences of the original structured query statements by regularizing the processing of structured query statements, so that structured query statements with the same semantics but different writing forms are converted into a unified format, providing an accurate and unified comparison basis for subsequent similarity calculations. By determining the similarity of statements, structured query statements with the same or highly similar semantics can be accurately identified, providing a reliable basis for deduplication and classification of structured query statements. There is no need to rely on the execution history of the structured query statement to be analyzed. In combination with historical records, a reliable time-consuming reference can be provided, which helps the database system to reasonably schedule resources, troubleshoot performance bottlenecks, and improve system scheduling efficiency and targeted operation and maintenance.

[0025] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present invention, nor is it intended to limit the scope of the present invention. Other features of the present invention will become readily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0027] Figure 1 This is a flow chart of a structured query statement analysis method provided according to the first embodiment of the present invention;

[0028] Figure 2 is a flowchart of another structured query statement analysis method provided according to the second embodiment of the present invention;

[0029] Figure 3 1 is a schematic diagram of the structure of a structured query statement analysis device provided according to the third embodiment of the present invention;

[0030] Figure 4 The present invention is a schematic diagram of an electronic device for implementing a structured query statement analysis method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0031] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of the present invention.

[0032] It should be noted that the terms "first", "second", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the numbers used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0033] Example 1

[0034] Figure 1 A flowchart of a structured query statement analysis method is provided for the first embodiment of the present invention. This embodiment is applicable to database analysis scenarios. The method can be executed by a structured query statement analysis device. The structured query statement analysis device can be implemented in the form of hardware and / or software. The structured query statement analysis device can be configured in a computer controller. Figure 1 As shown, the method includes:

[0035] S110: Obtain a structured query statement, perform regularization processing on the structured query statement, and generate a regularization template.

[0036] Structured Query Language (SQL) is a standardized programming language used to interact with relational databases and perform operations such as data query and insertion. Obtaining structured query statements refers to collecting raw SQL statements to be processed from sources such as database system operation records, user input, and application requests, providing the foundational data for subsequent processing. Regularized processing involves converting raw SQL statements into a unified format using a set of pre-defined rules, such as removing whitespace and redundant formatting, unifying case, standardizing field order, standardizing conditional expressions and joins, standardizing function call formats and expanding implicit statements, replacing constants, and cleaning comments and formatting. This eliminates irrelevant interference and preserves the core semantic structure. Regularized templates are the final product of regularized processing of structured query statements. They are standardized SQL templates that eliminate non-semantic differences and preserve the core semantic structure. SQL statements with the same semantics but different written forms will generate the same regularized template, providing a basis for subsequent statement similarity calculation and execution time prediction.

[0037] Optionally, regularization processing includes removing blank characters, unifying upper and lower cases, standardizing field order, unifying conditional expressions, unifying the order and format of connected tables, unifying function formats, expanding implicit statements, removing comments and redundant line breaks, and replacing constants.

[0038] In one specific implementation, whitespace removal eliminates non-semantic formatting differences, such as extra spaces, line breaks, and tabs, from the original SQL statement to prevent these formatting issues from affecting the accuracy of subsequent statement matching. Specifically, this process removes extra spaces from the statement and separates keywords and symbols with a single space. For example, the original SQL statement "SELECT name, age FROM users WHERE age>30;" becomes "SELECT name, age FROM users WHERE age > 30;" after processing.

[0039] In one specific implementation, case uniformity eliminates non-semantic differences caused by case differences in keywords, function names, table names, and field names within SQL statements, ensuring that statements with the same semantics maintain consistent case. Specifically, keywords such as SELECT, FROM, and WHERE, function names such as Count, table names such as Users, and field names such as AGE are uniformly converted to fixed case. For example, the original SQL statement "SELECT name, AGE from Users WHERE age>30;" becomes "select name, age from users where age > 30;" after processing.

[0040] In one specific implementation, normalizing field order ensures that SQL statements with the same semantics, such as those with different ordering of specified fields after SELECT, GROUP BY, and ORDER BY, maintain consistent field order. This prevents differences in field order from affecting statement matching. Specifically, fields after SELECT, GROUP BY, and ORDER BY are sorted according to a fixed rule, such as alphabetical order. For example, the original SQL statement "SELECT name, age FROM users WHERE age> 30;" becomes "SELECT age, name FROM users WHERE age> 30;" after processing.

[0041] In one specific implementation, unifying conditional expressions eliminates non-semantic differences in logical expressions within specified field clauses, such as WHERE, HAVING, and ON clauses, caused by different ordering of conditions. This ensures that statements with the same conditional logic have a uniform format. Specifically, the logical expressions in the WHERE, HAVING, and ON clauses are reordered by field name. For example, the original SQL "SELECT name, age FROM users WHERE gender='M' and age> 30;" becomes "SELECT age, name FROM users WHERE age>30 and gender='M';" after processing.

[0042] In one specific implementation, unifying the order and format of joined tables means eliminating non-semantic differences caused by table order, non-standard JOIN type expressions, and different join condition orders when performing multi-table JOINs, thereby ensuring consistent statement formats for the same join structure. This involves specifying the JOIN type, which includes inner joins and left joins. Then, the inner join sorts the joined tables alphabetically, while also sorting the join conditions alphabetically. For example, the original SQL statement "SELECT name, age FROM users inner join orders on user.id = orders.idand orders.name = users.name;" becomes "SELECT name, age FROM orders INNERJOIN users on orders.name = users.name and user.id = orders.id;" after processing.

[0043] In one specific implementation, standardizing function formats eliminates non-semantic differences caused by different case names and parameter formats during function calls, ensuring a uniform function call format. This involves converting function names to fixed case and removing unnecessary parentheses or spaces from function parameters. For example, the original SQL "SELECT Count (*) FROM users;" becomes "SELECT count (*) FROM users;" after processing.

[0044] In one specific implementation, implicit statement expansion involves expanding SQL abbreviations, such as SELECT *, into their full form. This avoids ambiguous statement structure caused by the abbreviation and ensures that the core semantics of the statement are fully represented. Specifically, SELECT * is expanded into a list of corresponding fields. For example, if the original SQL statement "SELECT * FROM users ;" contains only age, gender, and name fields in the users table, it will become "SELECT age, gender, name FROM users;" after processing.

[0045] In one specific implementation, removing comments and extraneous line breaks refers to removing comments and extraneous line breaks that are irrelevant to the core semantics of SQL statements, simplifying the statement structure and preventing irrelevant information from interfering with subsequent statement processing. Specifically, this operation involves removing comments and extraneous line breaks from the statement. For example, the original SQL statement "SELECT name FROM users WHERE age >30; -- age greater than 30" becomes "SELECT name FROM users WHERE age > 30;" after processing.

[0046] In one specific implementation, constant replacement eliminates non-semantic differences in SQL statements caused by different constant values, such as numeric and string constants, ensuring that statements with the same semantics but different constant values ​​can match. Specifically, constant values ​​in statements are replaced with a unified marker. For example, the original SQL statement "SELECT name FROM users WHERE age > 30;" becomes "SELECT name FROM users WHERE age > #;" after processing.

[0047] S120 : Select two templates from the regularized templates in sequence as templates to be compared, and determine the sentence similarity between the templates to be compared.

[0048] The comparison templates are two regularized templates selected sequentially from the generated regularized templates for calculating their mutual similarity. By calculating the similarity between the two templates, we can determine whether their corresponding original SQL statements are equivalent or highly similar. Statement similarity is an indicator used to measure the degree of similarity between the core semantics of the two compared templates, ranging from 0 to 1, with larger values ​​indicating greater similarity.

[0049] Optionally, determining the sentence similarity between the templates to be compared includes: respectively determining the character length of the two templates to be compared, wherein the character length includes a first character length and a second character length; calculating a first length difference between the first character length and the second character length; judging whether the first length difference is greater than a preset difference threshold, and if so, determining the sentence similarity to be 0; otherwise, judging whether the two templates to be compared are exactly the same, and if so, determining the sentence similarity to be 1, and otherwise, calculating the character similarity and word segmentation similarity between the templates to be compared, substituting the character similarity and word segmentation similarity into a first preset similarity formula, and calculating the sentence similarity.

[0050] Specifically, the controller obtains two regularized templates to be compared and counts the total number of characters in each template character by character to determine the character lengths of the first and second templates to be compared. For example, if template 1 is "SELECT name FROM users WHERE age > #;," its total number of characters is counted to obtain the first character length; if template 2 is "SELECT name, id FROM users WHERE age > #;," its total number of characters is counted to obtain the second character length. The controller then calculates the degree of difference in the character lengths of the two templates to be compared using the formula: First length difference = abs(first character length - second character length) / first character length. This calculation is intended to quickly determine whether there is a significant length difference between the templates. If the difference is too large, it is likely a significant semantic difference, and no further complex calculation is required. For example, if the first character length is 35 and the second character length is 41, substituting this into the formula yields First length difference = abs(35 - 41) / 35 ≈ 17.1%.

[0051] Among them, the preset difference threshold can be 50%. When the difference in character length between two regularized templates exceeds 50%, it indicates that the core semantic structures between them are likely to be too different, and the subsequent calculation of similarity is meaningless. Therefore, if the calculated first length difference is greater than 50%, the statement similarity of the two templates to be compared can be directly determined to be 0, and when the length difference is less than or equal to 50%, the next step of judgment is entered. When the two regularized templates are completely consistent, it means that the semantics of the original SQL statements corresponding to them are exactly the same, and no subsequent similarity calculation is required at this time. The controller can directly compare the character sequences of the two templates to be compared. If each character is exactly the same, the statement similarity is determined to be 1. If there is any difference in any character, the subsequent similarity calculation step will be entered.

[0052] Specifically, the first preset similarity formula is: sentence similarity = token similarity * 0.7 + character similarity * 0.3. The principle of weight setting of the formula is that the token-level similarity is more focused on capturing the semantic information of the sentence, and is more critical for matching complex SQL statements, so a higher weight, i.e. 0.7, is given. The character-level similarity focuses on fine character differences, and a lower weight, i.e. 0.3, is given. The final sentence similarity is obtained after weighted fusion.

[0053] Optionally, the character similarity and the token similarity between the templates to be compared are calculated, including: calculating the character-level edit distance between the templates to be compared based on an edit distance algorithm, substituting the character-level edit distance into a second preset similarity formula to obtain the character similarity; tokenizing the templates to be compared according to spaces to obtain two token sequences; calculating the token-level edit distance between the token sequences based on the edit distance algorithm, and substituting the token-level edit distance into a third preset similarity formula to obtain the token similarity.

[0054] Wherein, the edit distance Levenshtein is the minimum number of edits required to convert one string into another string. In the character similarity calculation, the processing object is the complete character sequence of the two templates to be compared. First, initialize a two-dimensional matrix dp of (m+1) x (n+1), where m is the character length of the first template to be compared, and n is the character length of the second template to be compared. The matrix dp[i][j] represents the minimum edit distance of converting the first i characters of the first template to the first j characters of the second template. The initial conditions are set as dp[0][j]=j, indicating that j times of insertion is required to convert empty characters to target characters, and dp[i][0]=i, indicating that i times of deletion is required to convert source characters to empty characters. Then, according to the recursive formula, if the (i-1)th character and the (j-1)th character of the two templates are the same, then dp[i][j]=dp[i-1][j-1]; if they are different, then dp[i][j]=1+min(dp[i-1][j],dp[i][j-1],dp[i-1][j-1]). The final dp[m][n] in the lower right corner of the matrix is the character-level edit distance of the two templates to be compared. The second preset similarity formula is: character similarity = 1-character level edit distance d1 / max (first character length, second character length), which normalizes the edit distance to the interval [0,1], i.e. the smaller the edit distance, the smaller the difference at the character level, and the closer the similarity to 1.

[0055] Furthermore, the controller segments the template to be compared based on spaces, producing two segmented word sequences. By splitting the regularized template into semantic units based on spaces, it better aligns with the semantic structure of the SQL statement and avoids semantic associations that might be overlooked by character-level comparison. Specifically, the two templates to be compared are split based on spaces, removing any remaining blank characters, to produce two segmented word sequences. For example, if template 1 to be compared is "SELECT name FROM users WHERE age>#;," segmentation based on spaces yields segmented word sequence 1: ["SELECT", "name", "FROM", "users", "WHERE", "age", ">", "#;"]; if template 2 to be compared is "SELECT name, id FROM users WHERE age > #;," segmentation yields segmented word sequence 2: ["SELECT", "name,", "id", "FROM", "users", "WHERE", "age",">", "#;"]. The controller then calculates the segmented word-level edit distance using an edit distance algorithm. This process follows the same principles as the character-level edit distance algorithm, except that the processing changes from characters to segmented words. Among them, the third preset similarity formula is: word segmentation similarity = 1-word segmentation level edit distance / max (first word segmentation sequence length, second word segmentation sequence). The smaller the word segmentation level edit distance, the smaller the difference at the semantic unit level and the higher the semantic similarity.

[0056] S130 , taking each regularized template as a template to be analyzed, performing time consumption prediction on the template to be analyzed, and generating a prediction result.

[0057] The template to be analyzed is a single regularized template selected sequentially from all regularized templates, and the execution time of its corresponding original SQL statement needs to be predicted. The corresponding original SQL statement usually lacks execution history, making it difficult to directly predict the execution time. Time prediction is achieved by matching the regularized template or its hash value with the historical SQL statement. The prediction result refers to the final output obtained after the time prediction of the template to be analyzed, that is, the estimated execution time of the original SQL statement corresponding to the template to be analyzed. The prediction result can provide a reference for database performance analysis, intelligent operation and maintenance, and automatic optimization.

[0058] The technical solution of the embodiment of the present invention can eliminate the non-semantic differences of the original structured query statements by regularizing the processing of structured query statements, so that structured query statements with the same semantics but different writing forms are converted into a unified format, providing an accurate and unified comparison basis for subsequent similarity calculations. By determining the similarity of statements, structured query statements with the same or highly similar semantics can be accurately identified, providing a reliable basis for deduplication and classification of structured query statements. There is no need to rely on the execution history of the structured query statement to be analyzed. In combination with historical records, a reliable time-consuming reference can be provided, which helps the database system to reasonably schedule resources, troubleshoot performance bottlenecks, and improve system scheduling efficiency and targeted operation and maintenance.

[0059] Example 2

[0060] Figure 2 This is a flowchart of a structured query statement analysis method provided in the second embodiment of the present invention. This embodiment adds a specific process of predicting the time consumption of the template to be analyzed and generating the prediction result on the basis of the above-mentioned first embodiment. Among them, the specific contents of steps S210-S220 are roughly the same as those of steps S110-S120 in the first embodiment, so they will not be repeated in this embodiment. Figure 2 As shown, the method includes:

[0061] S210: Obtain a structured query statement, perform regularization processing on the structured query statement, and generate a regularization template.

[0062] Optionally, regularization processing includes removing blank characters, unifying upper and lower cases, standardizing field order, unifying conditional expressions, unifying the order and format of connected tables, unifying function formats, expanding implicit statements, removing comments and redundant line breaks, and replacing constants.

[0063] S220 , selecting two templates from the regularized templates in sequence as templates to be compared, and determining the sentence similarity between the templates to be compared.

[0064] Optionally, determining the sentence similarity between the templates to be compared includes: respectively determining the character length of the two templates to be compared, wherein the character length includes a first character length and a second character length; calculating a first length difference between the first character length and the second character length; judging whether the first length difference is greater than a preset difference threshold, and if so, determining the sentence similarity to be 0; otherwise, judging whether the two templates to be compared are exactly the same, and if so, determining the sentence similarity to be 1, and otherwise, calculating the character similarity and word segmentation similarity between the templates to be compared, substituting the character similarity and word segmentation similarity into a first preset similarity formula, and calculating the sentence similarity.

[0065] Optionally, the character similarity and the segmentation similarity between the templates to be compared are calculated, including: calculating a character-level edit distance between the templates to be compared based on an edit distance algorithm, substituting the character-level edit distance into a second preset similarity formula to obtain the character similarity; performing segmentation on the templates to be compared according to spaces to obtain two segmentation sequences; calculating a segmentation-level edit distance between the segmentation sequences based on the edit distance algorithm, and substituting the segmentation-level edit distance into a third preset similarity formula to obtain the segmentation similarity.

[0066] S230, respectively, each regularized template is taken as a template to be analyzed.

[0067] S240, read the matching parameter value.

[0068] Specifically, the matching parameter value is a key configuration item for specifying the matching mode adopted by the time-consuming prediction. In actual operation, the controller will read the specific value of this parameter from the database configuration or the preset parameter list. The parameter value has two possibilities, namely a first specified value and a second specified value. Exemplarily, the first specified value can be 0, and the second specified value can be 1. Different values correspond to different time-consuming prediction modes, which provide a basis for subsequent selection of fuzzy matching or precise matching.

[0069] S250, when the matching parameter value is the first specified value, a fuzzy matching mode is adopted to predict the time-consuming of the template to be analyzed, and a prediction result is generated.

[0070] Specifically, when the matching parameter value is 0, a fuzzy matching mode can be adopted to predict the time-consuming of the template to be analyzed, and a prediction result is generated. The fuzzy matching mode is based on a statement similarity matching algorithm, and filters SQL statements highly similar to the template to be analyzed in the historical record, and uses the average execution time thereof as the prediction result. It is suitable for scenarios without complete matching historical records and needs to estimate the overall trend.

[0071] Optionally, the fuzzy matching mode is adopted to predict the time-consuming of the template to be analyzed, and a prediction result is generated, including: analyzing the template to be analyzed to obtain a table list arranged in alphabetical order and a third character length; obtaining a historical query record, and filtering the same first historical query statement as the template to be analyzed in the historical query record based on the table list; respectively taking each first historical query statement as a candidate statement, determining a fourth character length of the candidate statement, and calculating a second length difference between the third character length and the fourth character length; when the second length difference is less than a preset difference threshold, the candidate statement is retained to generate a candidate historical record set; calculating the statement similarity between each candidate statement in the candidate historical record set and the template to be analyzed, and filtering out a target statement with a statement similarity greater than a preset similarity threshold; judging whether the number of target statements is 0, if yes, returning the prediction result as unknown; otherwise, calculating the average value of the actual execution time of each target statement as the prediction result.

[0072] The table list is formed by extracting all table names involved in the template, and the table list is sorted in alphabetical order. The total number of characters in the template is counted, and the total number is the third character length. The historical query records are stored in the clusterdb.long_time_sql_history table, which contains the regularized statements of historical SQL, the table list involved, and the actual execution time. After obtaining all the historical records in the table, the historical query statements in the table_list field of the historical records are filtered according to the table list, that is, the first historical query statement. Since the SQL statements with different table lists have large differences in business logic, the execution time has low reference value, so the candidate range can be reduced by filtering through the table list, which can reduce the subsequent calculation amount.

[0073] Specifically, the controller will take each first historical query statement as a candidate statement, first count the total number of characters of each candidate statement to obtain the fourth character length, and then calculate the length difference between the third character length and the fourth character length. By calculating the length difference, candidate statements with large structural differences from the template can be quickly excluded, and invalid similarity calculation can be avoided. The preset difference threshold can be 0.5. When the second length difference of the candidate statement is less than 0.5, the candidate statement is retained, and finally, all retained candidate statements together form a candidate historical record set. If the second length difference is greater than or equal to 0.5, the candidate statement is directly excluded and not included in subsequent processing. Then, the controller calculates the similarity of each candidate statement in the candidate historical record set to the template, and selects the candidate statement with a similarity greater than a preset similarity threshold, that is, the target statement. Finally, the controller traverses the filtered target statement and counts the number of target statements. If the number is 0, it means that there is no matching historical record, and the prediction result returned is unknown. If the number of target statements is not 0, the controller can extract the actual execution time corresponding to each target statement from the historical record, and calculate the average execution time, which is the execution time prediction result of the original SQL statement corresponding to the template.

[0074] S260、When the matching parameter value is the second specified value, the exact matching mode is used to predict the execution time of the template to be analyzed, and a prediction result is generated.

[0075] Specifically, when the match parameter value is 1, the exact match mode can be used to predict the execution time of the template being analyzed and generate a prediction result. Exact match mode uses hash value matching based on regularized SQL. It calculates the standardized hash value of the template being analyzed, searches the historical records for records with identical hash values, and uses the average execution time of these records as the prediction result. This mode is suitable for scenarios that require detailed verification of execution time to ensure prediction accuracy.

[0076] Optionally, an exact matching mode is used to predict the time consumption of the template to be analyzed and generate a prediction result, including: calculating the hash value of the template to be analyzed; obtaining historical query records, and filtering the second historical query statements that are the same as the template to be analyzed in the historical query records based on the hash value; calculating the average actual execution time of each second historical query statement as the prediction result.

[0077] Specifically, the controller generates a standardized hash value based on the template to be analyzed, denoted as "query_hash_key." By calculating this hash value, the regularized SQL statement is converted into a unique identifier, providing an efficient basis for quickly searching for exact matches in the historical records and avoiding the inefficiencies associated with directly comparing long SQL statements. First, the controller retrieves all historical query records from the table and constructs the query condition "hash_key = query_hash_key," where query_hash_key is the hash value of the template to be analyzed. The controller then filters out historical queries whose "hash_key" matches the hash value of the template to be analyzed, representing the second historical query. Since the hash values ​​of the template to be analyzed and the second historical query are identical, the regularized SQL statements are identical, with identical semantics and execution logic. Therefore, the execution time of the second historical query provides a valuable reference for the current template to be analyzed. The controller then extracts the actual execution time of each statement from the historical records corresponding to the filtered second historical query and calculates the average execution time, which serves as the predicted execution time for the template to be analyzed.

[0078] The technical solution of the embodiment of the present invention can clarify the specific mode adopted for time consumption prediction by matching parameter values, providing a clear basis for the subsequent selection of fuzzy matching or precise matching. When the matching parameter value is the first specified value, the fuzzy matching mode is used to predict the time consumption of the template to be analyzed and generate results. In the scenario where there is no complete matching historical record, the time consumption reference can be obtained by screening highly similar historical statements, providing a reliable basis for time consumption prediction for the template to be analyzed that lacks a complete matching record. When the matching parameter value is the second specified value, the precise matching mode is used to predict the time consumption of the template to be analyzed and generate results. The hash value can be used to accurately locate the complete matching historical statement, and its average time consumption is used as the prediction result, ensuring the accuracy of the time consumption prediction of the template to be analyzed with a complete matching historical record.

[0079] Example 3

[0080] Figure 3 This is a schematic diagram of a structured query statement analysis device provided by the third embodiment of the present invention. Figure 3 As shown, the apparatus includes: a regularized template generating module 310 for acquiring a structured query statement, performing regularized processing on the structured query statement, and generating a regularized template;

[0081] A sentence similarity determination module 320 is used to sequentially select two templates from the regularized templates as templates to be compared, and determine the sentence similarity between the templates to be compared;

[0082] The prediction result generating module 330 is used to respectively use each regularized template as a template to be analyzed, perform time consumption prediction on the template to be analyzed, and generate a prediction result.

[0083] Optionally, the sentence similarity determination module 320 is specifically used to: respectively determine the character length of the two templates to be compared, wherein the character length includes a first character length and a second character length; calculate a first length difference between the first character length and the second character length; determine whether the first length difference is greater than a preset difference threshold, and if so, determine that the sentence similarity is 0; otherwise, determine whether the two templates to be compared are exactly the same, and if so, determine that the sentence similarity is 1, otherwise, calculate the character similarity and word segmentation similarity between the templates to be compared, substitute the character similarity and word segmentation similarity into the first preset similarity formula, and calculate the sentence similarity.

[0084] Optionally, the sentence similarity determination module 320 specifically includes: a similarity determination unit, used to: calculate the character-level edit distance between the templates to be compared based on the edit distance algorithm, substitute the character-level edit distance into the second preset similarity formula to obtain character similarity; segment the templates to be compared according to spaces to obtain two segmentation sequences; calculate the segmentation-level edit distance between the segmentation sequences based on the edit distance algorithm, substitute the segmentation-level edit distance into the third preset similarity formula to obtain segmentation similarity.

[0085] Optionally, the prediction result generation module 330 specifically includes: a matching parameter value reading unit, used to: read the matching parameter value; a fuzzy matching prediction unit, used to: when the matching parameter value is a first specified value, use the fuzzy matching mode to perform time consumption prediction on the template to be analyzed and generate a prediction result; an exact matching prediction unit, used to: when the matching parameter value is a second specified value, use the exact matching mode to perform time consumption prediction on the template to be analyzed and generate a prediction result.

[0086] Optionally, a fuzzy matching prediction unit is specifically used to: parse the template to be analyzed, obtain a table list arranged in alphabetical order and a third character length; obtain historical query records, and filter the first historical query statement that is the same as the template to be analyzed in the historical query records based on the table list; take each first historical query statement as a candidate statement, and determine the fourth character length of the candidate statement, and calculate the second length difference between the third character length and the fourth character length; when the second length difference is less than a preset difference threshold, retain the candidate statement and generate a candidate historical record set; calculate the statement similarity between each candidate statement in the candidate historical record set and the template to be analyzed, and filter out the target statement whose statement similarity is greater than the preset similarity threshold; determine whether the number of target statements is 0, and if so, return the prediction result as unknown; otherwise, calculate the average value of the actual execution time of each target statement as the prediction result.

[0087] Optionally, the exact match prediction unit is specifically used to: calculate the hash value of the template to be analyzed; obtain historical query records, and filter the second historical query statements that are the same as the template to be analyzed in the historical query records based on the hash value; calculate the average actual execution time of each second historical query statement as the prediction result.

[0088] The technical solution of the embodiment of the present invention can eliminate the non-semantic differences of the original structured query statements by regularizing the processing of structured query statements, so that structured query statements with the same semantics but different writing forms are converted into a unified format, providing an accurate and unified comparison basis for subsequent similarity calculations. By determining the similarity of statements, structured query statements with the same or highly similar semantics can be accurately identified, providing a reliable basis for deduplication and classification of structured query statements. There is no need to rely on the execution history of the structured query statement to be analyzed. In combination with historical records, a reliable time-consuming reference can be provided, which helps the database system to reasonably schedule resources, troubleshoot performance bottlenecks, and improve system scheduling efficiency and targeted operation and maintenance.

[0089] A structured query statement analysis device provided by an embodiment of the present invention can execute a structured query statement analysis method provided by any embodiment of the present invention, and has corresponding functional modules and beneficial effects of the execution method.

[0090] Example 4

[0091] Figure 4 A schematic diagram of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital assistants, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0092] like Figure 4 As shown, electronic device 10 includes at least one processor 11 and memory, such as read-only memory (ROM) 12 and random access memory (RAM) 13, communicatively connected to at least one processor 11. The memory stores computer programs executable by the at least one processor. Processor 11 can perform various appropriate actions and processes based on the computer programs stored in ROM 12 or loaded from storage unit 18 into RAM 13. RAM 13 can also store various programs and data required for the operation of electronic device 10. Processor 11, ROM 12, and RAM 13 are interconnected via bus 14. An input / output (I / O) interface 15 is also connected to bus 14.

[0093] Multiple components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0094] Processor 11 can be any general-purpose and / or specialized processing component with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various specialized artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any other suitable processor, controller, microcontroller, etc. Processor 11 executes the various methods and processes described above, such as a structured query statement analysis method.

[0095] In some embodiments, a structured query statement analysis method can be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the structured query statement analysis method described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to execute a structured query statement analysis method in any other appropriate manner (e.g., via firmware).

[0096] Various embodiments of the systems and techniques described above can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), system-on-chip systems (SOCs), programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments can include being implemented in one or more computer programs that are executable and / or interpreted on a programmable system that includes at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0097] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when the computer program is executed by the processor, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The computer program may be executed entirely on the machine, partially on the machine, as a stand-alone software package, partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0098] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, device, or apparatus. A computer-readable storage medium may include, but is not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, device, or apparatus, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media may include an electrical connection based on one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0099] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device that has: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, the feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, voice input, or tactile input).

[0100] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer with a graphical user interface or web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0101] A computing system may include clients and servers. The clients and servers are typically remote from each other and typically interact via a communication network. This client-server relationship arises through computer programs running on the respective computers, creating a client-server relationship. The server may be a cloud server, also known as a cloud computing server or cloud host. This server is a hosting product within the cloud computing service ecosystem, addressing the management difficulties and limited scalability of traditional physical hosting and VPS services.

[0102] It should be understood that the various forms of the processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the present invention can be performed in parallel, sequentially, or in a different order, as long as the desired results of the technical solution of the present invention can be achieved. This is not limited herein.

[0103] The above specific embodiments do not limit the scope of protection of the present invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions may be made based on design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the present invention are intended to be included within the scope of protection of the present invention.

Claims

1. A structured query statement analysis method, characterized in that: include: Obtaining a structured query statement, performing regularization processing on the structured query statement, and generating a regularization template; Selecting two templates from the regularized templates in turn as templates to be compared, and determining the sentence similarity between the templates to be compared; Each regularized template is used as a template to be analyzed, and a time consumption prediction is performed on the template to be analyzed to generate a prediction result.

2. The method according to claim 1, characterized in that Determining the sentence similarity between the templates to be compared includes: Determining the character lengths of the two templates to be compared respectively, wherein the character lengths include a first character length and a second character length; Calculate a first length difference between the first character length and the second character length; Determining whether the first length difference is greater than a preset difference threshold, and if so, determining that the sentence similarity is 0; Otherwise, determine whether the two templates to be compared are exactly the same. If so, determine that the sentence similarity is 1. Otherwise, calculate the character similarity and word segmentation similarity between the templates to be compared, substitute the character similarity and word segmentation similarity into the first preset similarity formula to calculate the sentence similarity.

3. The method according to claim 2, characterized in that The calculating of character similarity and word segmentation similarity between the templates to be compared includes: Calculating the character-level edit distance between the templates to be compared based on the edit distance algorithm, and substituting the character-level edit distance into a second preset similarity formula to obtain character similarity; Segment the comparison template by spaces to obtain two segmentation sequences; The segmentation level edit distance between the segmentation sequences is calculated based on the edit distance algorithm, and the segmentation level edit distance is substituted into the third preset similarity formula to obtain the segmentation similarity.

4. The method according to claim 1, wherein The step of predicting the time consumption of the template to be analyzed and generating a prediction result includes: Read matching parameter values; When the matching parameter value is the first specified value, a fuzzy matching mode is used to perform a time consumption prediction on the template to be analyzed to generate a prediction result; When the matching parameter value is the second specified value, the time consumption of the template to be analyzed is predicted using the exact matching mode to generate a prediction result.

5. The method according to claim 4, characterized in that The step of using a fuzzy matching mode to predict the time consumption of the template to be analyzed and generating a prediction result includes: Parsing the template to be analyzed to obtain a table list arranged in alphabetical order and a third character length; Obtaining historical query records, and screening the historical query records for a first historical query statement that is identical to the template to be analyzed based on the table list; Taking each first historical query statement as a candidate statement, determining the fourth character length of the candidate statement, and calculating the second length difference between the third character length and the fourth character length; When the second length difference is less than a preset difference threshold, retaining the candidate sentence and generating a candidate history record set; Calculating the sentence similarity between each candidate sentence in the candidate history record set and the template to be analyzed, and screening out target sentences whose sentence similarity is greater than a preset similarity threshold; Determine whether the number of target sentences is 0. If so, return the prediction result as unknown; Otherwise, the average actual execution time of each target statement is calculated as the prediction result.

6. The method according to claim 4, characterized in that The step of using the exact matching mode to predict the time consumption of the template to be analyzed and generating a prediction result includes: Calculating a hash value of the template to be analyzed; Obtaining historical query records, and screening the historical query records for a second historical query statement that is identical to the template to be analyzed based on the hash value; The average actual execution time of each second historical query statement is calculated as the prediction result.

7. The method according to claim 1, characterized in that The regularization processing includes removing blank characters, unifying upper and lower cases, standardizing field order, unifying conditional expressions, unifying the order and format of connection tables, unifying function formats, expanding implicit statements, removing comments and redundant line breaks, and replacing constants.

8. A structured query statement analysis device, characterized in that: include: A regularized template generation module is used to obtain a structured query statement, perform regularized processing on the structured query statement, and generate a regularized template; A sentence similarity determination module is used to select two templates from the regularized templates in turn as templates to be compared, and determine the sentence similarity between the templates to be compared; The prediction result generating module is used to respectively use each regularized template as a template to be analyzed, perform time consumption prediction on the template to be analyzed, and generate a prediction result.

9. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to perform the method according to any one of claims 1 to 7.

10. A computer storage medium, characterized in that The computer storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the method according to any one of claims 1 to 7 when executed.

Images