Query statement quality assessment method, device and computer-readable storage medium

Abstract

The present application discloses a method, device and computer-readable storage medium for evaluating the quality of a query statement, the method comprising: in response to receiving a quality evaluation instruction for a query statement, obtaining a target query statement to be evaluated according to the quality evaluation instruction; determining the confidence of a historical statement template according to the historical quality evaluation results of each historical query statement in the acquired historical query statement set, the confidence representing the quality evaluation pass rate of the historical query statements in the same historical statement template; determining the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; determining the target quality evaluation result of the target query statement according to the target confidence of the target query statement. Thus, the efficiency and accuracy of the quality evaluation of the query statement are improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database processing, and in particular to a method for evaluating the quality of a query statement, an electronic device, and a computer-readable storage medium. Background Art

[0002] As one of the core tools for data management, the database provides a variety of functions to help users process data efficiently. However, with the explosive growth of big data information, the storage and retrieval capabilities of the database have also been increasingly challenged. During the query process, the quality of the query statement directly affects the reliability and stability of the database operation. An unreasonable query statement may cause the entire database to fail, seriously affecting the stability of the system. Therefore, the query statement can be evaluated to avoid unreasonable query statements affecting the stability of the system.

[0003] Currently, the main method for evaluating query statements is manual screening. However, faced with tens of thousands of query statements, manual screening not only consumes a lot of time and human resources, but also has low evaluation efficiency. In addition, there will be subjective biases in the control of evaluation quality and problems with inconsistent standards. Summary of the invention

[0004] The main technical problem solved by the present application is to provide a query statement quality assessment method, an electronic device and a computer-readable storage medium, which can improve the efficiency of query statement quality assessment.

[0005] To solve the above technical problems, a technical solution adopted in the present application is: a method for quality assessment of query statements is provided, the method comprising: in response to receiving a quality assessment instruction for a query statement, obtaining a target query statement to be evaluated according to the quality assessment instruction; determining the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the acquired historical query statement set, the confidence representing the quality assessment pass rate of the historical query statements in the same historical statement template; determining the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; and determining the target quality assessment result of the target query statement according to the target confidence of the target query statement.

[0006] In some embodiments, the quality assessment result includes a qualified quality assessment, and the step of determining the confidence of the historical statement template based on the historical quality assessment results of each historical query statement in the acquired historical query statement set, wherein the confidence represents the quality assessment qualified rate of the historical query statements in the same historical statement template, includes: counting the total number of historical query statements in the historical query statement set that belong to the same historical statement template and the number of qualified statements whose historical quality assessment results of the historical query statements in the same historical statement template are qualified quality assessment; and determining the ratio between the number of qualified statements and the total number of statements as the confidence of the corresponding historical statement template.

[0007] In some embodiments, the step of determining the target confidence of the target query statement based on the template matching result between the target sentence template of the target query statement and each historical sentence template includes: performing template matching processing on the target sentence template and each historical sentence template to obtain a template matching result; in response to the template matching result indicating a successful match, determining the confidence of the corresponding historical sentence template as the target confidence of the target query statement; in response to the template matching result indicating a failed match, determining the target confidence of the target query statement based on the template similarity between each historical sentence template and the target sentence template.

[0008] In some embodiments, in response to the template matching result indicating a matching failure, the step of determining the target confidence of the target query statement based on the template similarity between each historical sentence template and the target sentence template includes: normalizing the template similarity between each historical sentence template and the target sentence template, and determining the normalized template similarity as the weight value of the corresponding historical sentence template; and performing weighted summation processing on the weight value of each historical sentence template and the confidence of the corresponding historical sentence template to obtain the target confidence of the target query statement.

[0009] In some embodiments, the step of obtaining a target query statement to be evaluated according to the quality assessment instruction in response to receiving a quality assessment instruction for a query statement includes: obtaining an initial query statement to be evaluated according to the quality assessment instruction in response to receiving a quality assessment instruction for a query statement; performing a grammar check on the initial query statement to obtain a grammar check result; and determining the initial query statement as the target query statement in response to the grammar check result indicating that the grammar is correct.

[0010] In some embodiments, the quality assessment results include qualified quality assessment and unqualified quality assessment, and the step of determining the target quality assessment result of the target query statement according to the target confidence of the target query statement includes: in response to the target confidence being greater than a preset confidence threshold, determining the target quality assessment result of the target query statement as qualified quality assessment; in response to the target confidence being less than or equal to the preset confidence threshold, determining the target quality assessment result of the target query statement as unqualified quality assessment.

[0011] In some embodiments, after the step of determining the target quality assessment result of the target query statement based on the target confidence of the target query statement, the method further includes: parsing the target query statement to obtain at least one column information associated with the target query statement; generating at least one candidate index based on each column information; performing index benefit assessment processing on the target query statement based on each candidate index to obtain a recommended index set.

[0012] In some embodiments, the step of performing index benefit evaluation processing on the target query statement according to each candidate index to obtain a recommended index set includes: obtaining the index benefit value of each candidate index; in response to the index benefit value corresponding to each candidate index being greater than a preset index benefit threshold, determining the recommended index set according to the corresponding candidate index.

[0013] To solve the above technical problems, another technical solution adopted in the present application is: to provide an electronic device, including a memory and a processor, wherein the memory stores program instructions, and the processor retrieves the program instructions from the memory to execute the above query statement quality assessment method.

[0014] In order to solve the above technical problems, another technical solution adopted by the present application is: providing a computer-readable storage medium including program data stored therein, wherein the program data is used to implement the above-mentioned query statement quality assessment method when executed by a processor.

[0015] The above scheme, in response to receiving a quality assessment instruction for a query statement, obtains a target query statement to be assessed according to the quality assessment instruction; determines the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the acquired historical query statement set, the confidence representing the quality assessment pass rate of the historical query statements in the same historical statement template; determines the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; determines the target quality assessment result of the target query statement according to the target confidence of the target query statement. Determining the target quality assessment result of the target query statement to be assessed according to the confidence of the historical statement template determined by the historical quality assessment results of the historical query statement can improve the efficiency of quality assessment, avoid the problem of low assessment efficiency caused by manual assessment of the query statement quality, and avoid the problem of inaccurate assessment caused by the subjectivity of manual assessment. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments are briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative work, among which:

[0017] Figure 1 is a flowchart of an exemplary embodiment of a quality assessment method shown in the present application;

[0018] Figure 2 is a schematic diagram of a framework of an exemplary embodiment of a quality assessment system shown in the present application;

[0019] Figure 3 yes Figure 1 FIG. 1 is a flow chart of an exemplary embodiment of step S120 in the quality assessment method shown;

[0020] Figure 4 is a flowchart of an exemplary embodiment of a confidence determination process of a historical sentence template shown in the present application;

[0021] Figure 5 is a flowchart of an exemplary embodiment of a target confidence determination process shown in the present application;

[0022] Figure 6 It is a flowchart of an exemplary embodiment of a grammar detection process shown in the present application;

[0023] Figure 7 is a flowchart of an exemplary embodiment of an index recommendation process shown in the present application;

[0024] Figure 8 It is a specific flow chart of an exemplary embodiment of the quality assessment method shown in the present application;

[0025] Fig. 9 is a structural schematic diagram of an exemplary embodiment of a quality assessment device shown in the present application;

[0026] Fig.10 It is a structural schematic diagram of an embodiment of an electronic device provided by the present application;

[0027] Fig.11 It is a structural schematic diagram of an embodiment of a computer-readable storage medium provided by the present application. DETAILED DESCRIPTION

[0028] The technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It will be appreciated that the specific embodiments described herein are only used to explain the present application, rather than to limit the present application. It should also be noted that, for ease of description, only some but not all structures related to the present application are shown in the drawings. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the art without making creative work are within the scope of protection of the present application.

[0029] First of all, it should be noted that in the current era of rapid growth of big data information, in order to ensure the stability of the database, the automatic evaluation of query statements is imminent. Based on this, this application proposes a query statement quality assessment method, referred to as the quality assessment method, which can support multiple database types, including MySQL (My Structured Query Language, literally translated as "My Structured Query Language"), Oracle (literally translated as "Oracle") database, Postgre SQL (transliterated as "Postal Structured Query Language"), Greenplum DB (Greenplum Database, transliterated as "Greenplum Database"), Doris DB (Doris Database, transliterated as "Doris Database"), which can automatically evaluate the writing quality of query statements without manual evaluation, effectively improving the evaluation efficiency and review quality of query statements. For details, please refer to Figure 1 , Figure 1 It is a flowchart of an exemplary embodiment of the quality assessment method shown in the present application.

[0030] The execution subject of the quality assessment method may be a terminal device or a server or other processing device, wherein the terminal device may be a user equipment (UE), a computer, a mobile device, a user terminal, a terminal, a cellular phone, a cordless phone, a personal digital assistant (PDA), a handheld device, a computing device, a vehicle-mounted device, a wearable device, etc. The execution subject of the quality assessment method may also be a quality assessment device. In some possible implementations, the quality assessment method may be implemented by a processor calling a computer-readable instruction stored in a memory.

[0031] Specifically, the quality assessment method of this embodiment includes the following steps:

[0032] S110: In response to receiving a quality evaluation instruction for a query statement, obtaining a target query statement to be evaluated according to the quality evaluation instruction.

[0033] A query statement is a SQL (Structured Query Language) language construct used to retrieve data in a database management system. Common query statements usually include the following parts: select (select, specify the columns to be retrieved), from (specify the table where the data is located), where (specify the conditions for selecting rows), etc. As the number of database query tasks increases, some query statements with low execution efficiency may appear. Query statements with low execution efficiency may have the following problems: lack of effective indexes, complex query logic, syntax errors, etc. These query statements with low execution efficiency can be intercepted or modified to ensure the stable operation of the database.

[0034] The quality assessment instruction is used to instruct the quality assessment device to perform quality assessment processing on the query statement to obtain a target quality assessment result. Exemplarily, the quality assessment instruction includes the target query statement to be assessed. Specifically, Figure 2 It can be seen from the quality assessment system that the quality assessment system includes a front-end layer, a quality assessment device and a database layer. The quality assessment instruction can be obtained by the quality assessment device from the Web (World Wide Web) front-end layer. After receiving the quality assessment instruction, the quality assessment device interacts with the database layer, obtains the target query statement to be evaluated from the quality assessment instruction and performs quality assessment processing on it to obtain the target quality assessment result, and feeds back the target quality assessment result to the Web front-end layer to inform the user.

[0035] The target query statement refers to the query statement to be evaluated. Exemplarily, after the quality evaluation device obtains the quality evaluation instruction from the Web front-end layer, it obtains the target query statement to be evaluated according to the quality evaluation instruction. The target query statement can be a query statement that has been syntax-checked or a query statement that has not been syntax-checked.

[0036] S120: Determine the confidence of the historical statement template according to the historical quality evaluation results of each historical query statement in the acquired historical query statement set, where the confidence represents the quality evaluation pass rate of the historical query statements in the same historical statement template.

[0037] The historical query statement set includes historical query statements. Exemplarily, the historical query statements may be query statements that have been quality evaluated and obtained from a database. The evaluation time of the historical query statements is earlier than the evaluation time of the target query statements. After the quality evaluation of the target query statements, the target query statements may be added to the historical query statement set, and the historical query statement set may be updated.

[0038] The historical quality evaluation result is the quality evaluation result of the historical query statement. Exemplarily, the historical quality evaluation result can also be obtained from a database. The evaluation method of the historical quality evaluation result can be manual evaluation or evaluation by the quality evaluation method of this embodiment, and the evaluation method of the historical query statement is not limited here.

[0039] The historical statement template is obtained by templating the historical query statements and extracting the template information. Exemplarily, a historical query statement set is obtained, and the template information of each historical query statement in the historical query statement set is extracted to obtain the historical statement template. The template information can be extracted by fuzzifying the parameter values ​​(values) in the historical query statements, and the parameter values ​​and columns in the historical query statements can also be fuzzified. Of course, after obtaining the historical query statement set, query the current database whether there is a template model corresponding to the historical query statement set. If so, load the model directly. If not, create an empty model and add the historical statement template to the model. As an example:

[0040] The historical query statement is: SELECT CAP_TOTAL FROM P_TABLE WHERE FEATURE_ID = '101'AND CHANNEL_CODE = '1000111';

[0041] Fuzzify the parameter values ​​'101' and '1000111' in the historical query statements, and obtain the historical statement template: SELECT CAP_TOTAL FROM P_TABLE WHERE FEATURE_ID = ? AND CHANNEL_CODE = ?;

[0042] The historical query statement in this example is expressed as querying the elements in the column CAP_TOTAL from the table named P_TABLE, and the queried elements must satisfy FEATURE_ID = 101 and CHANNEL_CODE = 1000111. After fuzzifying the parameter values ​​and, the resulting historical statement template is expressed as querying all elements in the column CAP_TOTAL from the table named P_TABLE.

[0043] The confidence level represents the quality assessment pass rate of historical query statements in the same historical statement template. For example, the confidence level of the historical statement template can be determined based on the historical quality assessment results of the historical query statements. The confidence level can be used to measure the reliability of the historical statement template. For example, when the confidence level of a historical statement template is 90%, it means that there is a 90% probability that the query statement belonging to the historical statement template is qualified.

[0044] The quality assessment pass rate is the proportion of historical quality assessment results that are qualified in the historical query statements under the same historical statement template. In some embodiments, the ratio between the number of historical query statements that are qualified in the quality assessment under the same historical statement template and the total number of historical query statements under the historical statement template can be determined as the quality assessment pass rate. In other embodiments, the historical query statements under the same historical statement template can also be preliminarily screened to remove historical query statements with grammatical errors, or the historical query statements can be verified to determine whether the historical query statements belong to the historical statement template; then, the historical query statements after the preliminarily screened are counted, and the ratio between the number of historical query statements that are qualified in the quality assessment and the total number of historical query statements under the historical statement template is determined as the quality assessment pass rate.

[0045] S130: Determine the target confidence of the target query statement according to the template matching result between the target statement template of the target query statement and each historical statement template.

[0046] The target sentence template is obtained by templating the target query sentence and extracting the template information. The extraction of the target sentence template can refer to the extraction process of the historical sentence template, which will not be repeated here.

[0047] The template matching result refers to the matching result between the target sentence template and each historical sentence template. Exemplarily, the template matching processing method includes: feature matching, regular expression matching, natural language processing tools (such as NLP tools), etc. The template matching result is used to determine whether there is a sentence template in the historical sentence template that is consistent with the target sentence template.

[0048] The target confidence can be used to measure the quality of the target query statement. For example, the target confidence of the target query statement can be determined based on the template matching result between the historical statement template and the target statement template.

[0049] S140: Determine a target quality assessment result of the target query statement according to the target confidence of the target query statement.

[0050] The target quality assessment result is a conclusion obtained after quality assessment of the target query statement. Exemplarily, the target quality assessment result of the target query statement can be determined according to the target confidence of the target query statement. In some embodiments, after obtaining the target quality assessment result, it is determined whether the target query statement needs to be intercepted; or whether the target query statement needs to be indexed and recommended.

[0051] It can be seen that the quality assessment method of the embodiment of the present application responds to receiving a quality assessment instruction of a query statement, obtains a target query statement to be evaluated according to the quality assessment instruction; determines the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the acquired historical query statement set, and the confidence represents the quality assessment pass rate of the historical query statements in the same historical statement template; determines the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; determines the target quality assessment result of the target query statement according to the target confidence of the target query statement. Determining the target quality assessment result of the target query statement to be evaluated according to the confidence of the historical statement template determined by the historical quality assessment results of the historical query statement can improve the efficiency of quality assessment, avoid the problem of low assessment efficiency caused by manual assessment of the query statement quality, and avoid the problem of inaccurate assessment caused by the subjectivity of manual assessment.

[0052] Based on the above embodiments, the embodiments of the present application adopt Figure 3 The flowchart details how to determine the confidence of the historical statement template based on the historical quality assessment results of the historical query statements. Figure 3 , Figure 3 yes Figure 1The flowchart of an exemplary embodiment of step S120 in the quality assessment method is shown. Specifically, step S120 determines the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the historical query statement set, and the process in which the confidence represents the quality assessment pass rate of the historical query statements in the same historical statement template specifically includes the following steps:

[0053] S310: Counting the total number of historical query statements in the historical query statement set that belong to the same historical query statement template and the number of qualified statements whose historical quality assessment results are qualified in the historical query statements in the same historical query statement template.

[0054] A qualified quality assessment indicates that the historical query statement can meet the operation requirements. For example, the historical query statement can be scored, and if the score is greater than or equal to the qualified score, the quality assessment result of the historical query statement is determined to be qualified; if the score is less than the qualified score, the quality assessment result of the historical query statement is determined to be unqualified.

[0055] The quality assessment device extracts template information from each historical query statement in the historical query statement set to obtain a historical statement template for each historical query statement; historical query statements corresponding to the same historical statement template are classified into one category to obtain historical query statements under each historical statement template. One historical statement template may correspond to multiple historical query statements, and one historical query statement only corresponds to one historical statement template.

[0056] In order to evaluate the quality pass rate of historical query statements under each historical statement template, the quality assessment device needs to count the total number of historical query statements in the same historical statement template and the number of qualified statements with historical quality assessment results in the same historical statement template.

[0057] In other embodiments, after each historical query statement is classified according to the historical statement template, the historical query statements in each historical statement template are screened to obtain the screened historical query statements; the screened historical query statements are statistically processed to obtain the total number of historical query statements in the same historical statement template and the number of qualified statements whose historical quality evaluation results of the historical query statements in the same historical statement template are qualified. Among them, the screening processing method includes: removing historical query statements with grammatical errors, removing historical query statements with scores lower than preset scores, etc. When there are manually evaluated query statements in the historical query statements, due to the subjectivity of manual evaluation, the historical quality evaluation results of the same historical query statements in the same historical statement template can be obtained. If the historical quality evaluation results of the same historical query statements are greatly different (the score difference is large, such as a difference of more than 30 points), these historical quality evaluation results can be removed.

[0058] S320: Determine the ratio between the number of qualified sentences and the total number of sentences as the confidence of the corresponding historical sentence template.

[0059] After the total number of sentences and the number of qualified sentences of the historical query sentences belonging to the same historical sentence template, the quality assessment device calculates the ratio between the number of qualified sentences and the total number of sentences, and determines the ratio as the confidence of the corresponding historical sentence template. The confidence determined by the ratio between the number of qualified sentences and the total number of sentences can better reflect the reliability of the historical sentence template, thereby more accurately determining the target quality assessment result of the target query sentence to be evaluated.

[0060] In order to elaborate on the determination of the confidence of the historical statement template through the historical quality evaluation results of the historical query statements, Figure 4 The flowchart shown further illustrates it, and the details are as follows:

[0061] First, a historical query statement set is obtained, where the historical query statement set includes historical query statements and corresponding historical quality assessment results;

[0062] Determining whether a corresponding template model already exists according to the historical query statement set, wherein the template model includes a historical statement template corresponding to each historical query statement in the historical query statement set;

[0063] If it exists, directly load the corresponding template model; if it does not exist, directly create an empty model;

[0064] Extracting template information from each historical query statement in the historical query statement set to obtain a historical statement template corresponding to each historical query statement, and classifying the historical query statements according to the historical statement template to obtain the historical query statements under each historical statement template;

[0065] The number of qualified statements and the total number of statements of historical query statements in each historical statement template are set in the model, and the confidence of each historical statement template is determined according to the ratio between the number of qualified statements and the total number of statements. It can be understood here that the total number of historical query statements belonging to the same historical statement template in the historical query statement set and the number of qualified statements whose historical quality assessment results of historical query statements belonging to the same historical statement template are qualified for quality assessment are counted;

[0066] Determine whether all historical query statements in the historical query statement set have been processed, and if so, save the template model.

[0067] After obtaining the confidence of each historical sentence template, it can be matched in the template model of the historical query sentence, and the target sentence template of the target query sentence is subjected to template matching processing with each historical sentence template, so as to determine the target confidence of the target query sentence according to the confidence of each historical sentence template. Specifically, the target sentence template is subjected to template matching processing with each historical sentence template to obtain a template matching result; in response to the template matching result indicating a successful match, the confidence of the corresponding historical sentence template is determined as the target confidence of the target query sentence; in response to the template matching result indicating a failed match, the target confidence of the target query sentence is determined according to the template similarity between each historical sentence template and the target sentence template.

[0068] Template matching is essentially to obtain the template similarity between the target sentence template and each historical sentence template. For example, if there is a template similarity greater than or equal to a preset similarity threshold, the template matching result is determined as a successful match; if the template similarity is less than the preset similarity threshold, the template matching result is determined as a failed match. The preset similarity threshold can be set to 99%-100%.

[0069] The quality assessment device matches the sentence template that matches the target sentence template of the target query sentence in the template model of the historical query sentence; if a match is found, the corresponding historical sentence template is determined as the matching template. If there are multiple historical sentence templates that match the target sentence template, the historical sentence template with the highest template similarity is selected as the matching template; or multiple historical sentence templates are verified to determine the matching historical sentence template. The verification process can use other methods different from the first similarity calculation method to calculate the template similarity.

[0070] If no match is found, that is, after the match fails, the quality assessment device obtains the template similarity between each historical sentence template and the target sentence template; normalizes the template similarity between each historical sentence template and the target sentence template, and determines the normalized template similarity as the weight value of the corresponding historical sentence template; performs weighted summation processing on the weight value of each historical sentence template and the confidence of the corresponding historical sentence template to obtain the target confidence of the target query sentence. In this way, the target confidence of the target sentence template can still be determined when the historical sentence template cannot be matched, thereby improving the availability of quality assessment.

[0071] Among them, normalization processing refers to mapping the template similarity to the interval [0, 1]. In some embodiments, the normalization processing method may include: calculating the sum of the template similarities corresponding to each historical sentence template; determining the ratio between the template similarity corresponding to each historical sentence template and the sum of the similarities as the weight value of each historical sentence template. After obtaining the weight value, the confidence of the corresponding historical sentence template is weighted and summed based on the weight value to obtain the target query statement. The specific steps of the weighted summation process are: calculating the product of the weight value of each historical sentence template and the confidence of the corresponding historical sentence template to obtain the first product of each historical sentence template; calculating the sum of the first products of each historical sentence template to obtain the target confidence of the target sentence template.

[0072] In other embodiments, after the template matching result indicates that the match fails, the template similarity between each historical sentence template and the target sentence template is obtained; the template similarities are sorted from large to small, and the historical sentence templates corresponding to the top N template similarities are obtained; the top N template similarities are normalized to obtain the normalized template similarities, and the normalized template similarities are determined as the weight value of the corresponding historical sentence template; the weight values ​​of the top N historical sentence templates and the confidence of the corresponding historical sentence template are weighted and summed to obtain the target confidence of the target query statement. In addition, the KNN algorithm (K-Nearest Neighbors, K nearest neighbor algorithm) can also be used to obtain the historical sentence templates of the most K nearest neighbors, and the target confidence of the target query statement is determined based on the obtained K nearest neighbor historical query templates.

[0073] In order to elaborate on the target confidence of the target query sentence determined according to the template similarity between each historical sentence template and the target sentence template, Figure 5 The flowchart shown further illustrates it, and the details are as follows:

[0074] Get the target query statement;

[0075] Extract template information from the target query sentence to obtain the target sentence template;

[0076] Perform template matching processing on each historical sentence template and the target sentence template to obtain a template matching result, which can be matched in the template model of the historical query sentence;

[0077] Determine whether there is a matching template, that is, whether the template matching result indicates a successful match;

[0078] If it exists, the confidence of the corresponding historical sentence template is determined as the target confidence of the target query sentence;

[0079] If it does not exist, use the K nearest neighbor algorithm to obtain the K nearest neighbor historical query template;

[0080] The target confidence of the target query statement is determined based on the historical query templates of the acquired K nearest neighbors. Specifically, the template similarity of the historical query templates of the K nearest neighbors is normalized to obtain the weight value of each historical query template, and the corresponding historical query templates are weighted and summed using the weight value to obtain the target confidence of the target query statement.

[0081] In order to ensure the effectiveness of the evaluation and the stability of the system, after receiving the quality evaluation instruction of the query statement, the initial query statement to be evaluated is obtained according to the quality evaluation instruction; the initial query statement is grammatically checked to obtain the grammatical check result; in response to the grammatical check result indicating that the grammar is correct, the initial query statement is determined as the target query statement. Thus, the grammar of the initial query statement is verified before the quality evaluation, which can not only avoid the occurrence of operational errors caused by grammatically incorrect query statements in the system, but also improve the efficiency of quality evaluation.

[0082] The initial query statement can be the original input statement directly obtained from the Web front end; or it can be a statement that preprocesses the obtained original input statement. After obtaining the initial query statement, the initial query statement can be subjected to grammar detection to obtain a grammar detection result; if the grammar detection result indicates that the grammar is correct, the initial query statement is determined as the target query statement, and the target query statement is subjected to quality assessment; if the grammar detection result indicates that the grammar is wrong, the initial query statement is rejected and no quality assessment is performed.

[0083] Exemplarily, the contents of syntax checking include: keyword verification, confirming whether the keywords used in the initial query statement are spelled and used correctly; symbol checking, verifying whether symbols such as brackets, quotation marks, semicolons, etc. are paired correctly; statement structure verification, checking whether the overall structure of the initial query statement conforms to syntax specifications; parameter verification, for parameterized initial query statements, verifying whether the type and number of parameters are correct; table name and column name verification: confirming that the table name and column name referenced in the initial query statement exist and are spelled correctly; permission checking: in some cases, it may also be necessary to verify whether the user has permission to execute a specific initial query statement.

[0084] In order to elaborate on how to perform syntax detection on the initial query statement, Figure 6 The flowchart shown further illustrates it, and the details are as follows:

[0085] First, after receiving the quality assessment instruction of the query statement, the corresponding database type service is queried at the database layer according to the database type in the quality assessment instruction, that is, the corresponding database engine is searched for syntax detection;

[0086] Afterwards, the initial query statement is directly subjected to grammar detection at the database layer to obtain a grammar detection result, and the grammar detection result is returned to the quality assessment device.

[0087] Of course, in order to save quality assessment time, the initial query statement may not be subjected to syntax check, and the initial query statement may be directly determined as the target query statement.

[0088] After obtaining the target confidence, the target quality assessment result of the target query statement is determined according to the target confidence. In some embodiments, the quality assessment result includes a qualified quality assessment and an unqualified quality assessment. In response to the target confidence being greater than a preset confidence threshold, the target quality assessment result of the target query statement is determined as a qualified quality assessment; in response to the target confidence being less than or equal to the preset confidence threshold, the target quality assessment result of the target query statement is determined as an unqualified quality assessment.

[0089] Among them, the preset confidence threshold can be a preset value, for example, it can be set to any value between 0.7 and 1. If the target confidence threshold is greater than the preset confidence threshold, it means that the reliability of the target query statement is relatively high, and the target quality assessment result of the target query statement can be determined as qualified in the quality assessment; if the target confidence threshold is less than or equal to the preset confidence threshold, it means that the reliability of the target query statement is relatively low, and the target quality assessment result of the target query statement can be determined as unqualified in the quality assessment. Target query statements that fail the quality assessment will be rejected and corresponding optimization suggestions will be given.

[0090] For target query statements that pass the quality assessment, in order to further improve query efficiency, the target query statements are parsed to obtain at least one column information associated with the target query statement; at least one candidate index is generated based on each column information; and the target query statement is index benefit evaluated based on each candidate index to obtain a recommended index set.

[0091] Among them, column information refers to the information of the column associated with the target query statement. Exemplarily, the column corresponding to the query condition in the target query statement can be used as the column information associated with the target query statement. Usually, the column information appearing in the WHERE clause, JOIN condition, ORDER BY or GROUP BY can be determined as the column information associated with the target query statement. As an example, if the target query statement is: SELECT CAP_TOTAL FROM P_TABLE WHERE FEATURE_ID = 101AND CHANNEL_CODE ='1000111', the column information associated with the target query statement can be determined from the query condition in the WHERE clause, and the column FEATURE_ID and the column CHANNEL_CODE are used as the column information associated with the target query statement.

[0092] Then, at least one candidate index is generated based on the determined column information. The index is a data structure that pre-sorts a column or multiple columns of information in the database. Through the index, the database management system can quickly locate the location of the data record without having to scan the entire table row by row.

[0093] The quality assessment device traverses each column of information and generates at least one candidate index. The candidate index may refer to the index obtained by combining the indexes of each column of information in different ways. As an example, when the column FEATURE_ID and the column CHANNEL_CODE are the column information associated with the target query statement, there are three combinations of the two column information, the first is to generate only the index of the column FEATURE_ID, the second is to generate only the index of the column CHANNEL_CODE, and the third is to generate the index of the column FEATURE_ID and the column CHANNEL_CODE. These three combinations are determined as three candidate indexes.

[0094] Too many indexes will reduce write performance, so it is necessary to evaluate the index benefit of each candidate index, determine the recommended index set, and decide the column information that needs to generate the index. The index benefit evaluation process can be performed by executing the explain command to view the explain statements before and after the index is created to display the execution plan planned by the optimizer, check whether the index to be created is adopted by the database, and whether there is a performance improvement, thereby determining the index benefit value of each candidate index. Exemplarily, the optimizer in the database can execute the target query statement to obtain the first execution cost of the target query statement recorded by the optimizer; perform a cost test on the target query statement carrying the candidate index to obtain the second execution cost of the target query statement; determine whether the second execution cost is less than the first execution cost, and if so, determine the index benefit value of each candidate index based on the second execution cost and the first execution cost.

[0095] The quality assessment device obtains the index benefit value of each candidate index; in response to the index benefit value corresponding to each candidate index being greater than a preset index benefit threshold, a recommended index set is determined based on the corresponding candidate index. The preset index benefit threshold can be predetermined, and a recommended index set is generated based on the candidate indexes that meet the preset index benefit threshold. In addition, the candidate index with the largest index benefit value among the candidate indexes can also be selected as the recommended index. In this way, the query efficiency of the database can be improved through index recommendation.

[0096] In order to elaborate on how to recommend indexes for target query statements, Figure 7 The flowchart shown further illustrates it, and the details are as follows:

[0097] First, parse the target query statement and analyze the syntax tree of the query statement to obtain the column information of the query condition in the target query statement;

[0098] Generate candidate indexes based on column information;

[0099] Performing index benefit evaluation on the candidate indexes to obtain index benefit values;

[0100] Finally, according to the index benefit value, the index with better benefit is selected from each candidate index as the final recommended index set.

[0101] Finally, the quality assessment method of this application is described in detail in combination with the above embodiments. Figure 8 The flowchart shown further illustrates it, and the details are as follows:

[0102] The quality assessment device receives the quality assessment instruction and obtains the initial query statement to be assessed from the quality assessment instruction;

[0103] Performing a grammar check on the initial query statement and determining whether the grammar check result indicates that the grammar is correct;

[0104] Otherwise, the request for quality assessment of the initial query statement is rejected;

[0105] If yes, the initial query statement is determined as the target query statement, and the confidence of the target query statement is determined. Specifically, the template information of the target query statement is extracted to obtain the target statement template, and the confidence of each historical statement template is determined according to the historical quality evaluation results of each historical query statement in the historical query statement set; the target confidence of the target query statement is determined according to the template matching results between the target statement template and the historical statement template. Among them, the target statement template is matched with each historical statement template. If the historical statement template can be matched, the confidence of the corresponding historical statement template is determined as the target confidence of the target query statement. If not, the historical statement template is weighted and summed using the similarity between each target statement template and the historical statement template to obtain the target confidence of the target query statement.

[0106] Determine whether the confidence is greater than a preset confidence threshold, if not, reject the target query statement, if yes, perform index recommendation processing on the target query statement;

[0107] Finally, the index recommendation optimization suggestions and the target quality assessment results that meet the quality assessment standards are output.

[0108] See also Fig. 9 , Fig. 9 It is a structural diagram of an exemplary embodiment of the quality assessment device shown in the present application. The quality assessment device 900 includes an acquisition module 910, a first determination module 920, a second determination module 930 and a third determination module 940. The acquisition module 910 is used to respond to the received quality assessment instruction of the query statement and obtain the target query statement to be evaluated according to the quality assessment instruction; the first determination module 920 is used to determine the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the acquired historical query statement set, and the confidence represents the quality assessment pass rate of the historical query statements in the same historical statement template; the second determination module 930 is used to determine the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; the third determination module 940 is used to determine the target quality assessment result of the target query statement according to the target confidence of the target query statement.

[0109] In the above scheme, the quality assessment device responds to receiving the quality assessment instruction of the query statement, obtains the target query statement to be evaluated according to the quality assessment instruction; determines the confidence of the historical statement template according to the historical quality assessment results of each historical query statement in the acquired historical query statement set, and the confidence represents the quality assessment pass rate of the historical query statements in the same historical statement template; determines the target confidence of the target query statement according to the template matching results between the target statement template of the target query statement and each historical statement template; determines the target quality assessment result of the target query statement according to the target confidence of the target query statement. Determining the target quality assessment result of the target query statement to be evaluated according to the confidence of the historical statement template determined by the historical quality assessment result of the historical query statement can improve the efficiency of quality assessment, avoid the problem of low assessment efficiency caused by manual assessment of the query statement quality, and avoid the problem of inaccurate assessment caused by the subjectivity of manual assessment.

[0110] The functions of each module can be found in the quality assessment method embodiment, which will not be described in detail here.

[0111] In order to implement the quality assessment method of the above embodiment, this application proposes another electronic device, which can be found in detail. Fig.10 , Fig.10 It is a structural schematic diagram of an embodiment of an electronic device provided by the present application.

[0112] The electronic device 1000 includes a memory 1001 and a processor 1002 , wherein the memory 1001 and the processor 1002 are coupled.

[0113] The memory 1001 is used to store program data, and the processor 1002 is used to execute the program data to implement the quality assessment method of the above embodiment.

[0114] In this embodiment, the processor 1002 may also be referred to as a CPU (Central Processing Unit). The processor 1002 may be an integrated circuit chip having signal processing capabilities. The processor 1002 may also be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components. A general-purpose processor may be a microprocessor or the processor 1002 may also be any conventional processor, etc.

[0115] The present application also provides a computer-readable storage medium, such as Fig.11 As shown, the computer-readable storage medium 1100 is used to store program data 1101. When the program data 1101 is executed by the processor, it is used to implement the quality assessment method in the method embodiment of the present application.

[0116] The method involved in the embodiment of the quality assessment method of the present application, when implemented in the form of a software functional unit and sold or used as an independent product, can be stored in a device, such as a computer-readable storage medium. Based on this understanding, the technical solution of the present application is essentially or part of the contribution to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including several instructions for a computer device (which can be a personal computer, server, or network device, etc.) or a processor (processor) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk and other media that can store program code.

[0117] The above description is only an implementation method of the present application, and does not limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly used in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. A method for evaluating the quality of a query statement, characterized in that: The query statement quality assessment method comprises: In response to receiving a quality assessment instruction for a query statement, obtaining a target query statement to be assessed according to the quality assessment instruction; Determining the confidence of the historical statement template according to the historical quality evaluation results of each historical query statement in the acquired historical query statement set, wherein the confidence represents the quality evaluation pass rate of the historical query statements in the same historical statement template; Determining a target confidence of the target query statement according to a template matching result between a target statement template of the target query statement and each historical statement template; Determining a target quality assessment result of the target query statement according to the target confidence of the target query statement; The step of determining the target confidence of the target query statement according to the template matching result between the target statement template of the target query statement and each historical statement template comprises: Performing template matching processing on the target sentence template and each historical sentence template to obtain a template matching result; In response to the template matching result indicating a successful match, the confidence of the corresponding historical sentence template is determined as the target confidence of the target query sentence; In response to the template matching result indicating a matching failure, the step of determining a target confidence of the target query statement according to the template similarity between each historical statement template and the target statement template comprises: In response to the template matching result indicating a match failure, weighted sum processing is performed on each historical sentence template using the template similarity between the target sentence template and each historical sentence template to obtain a target confidence of the target query sentence.

2. The method for evaluating the quality of a query statement according to claim 1, characterized in that: The quality assessment result includes a qualified quality assessment, and the step of determining the confidence of the historical statement template according to the historical quality assessment result of each historical query statement in the acquired historical query statement set, wherein the confidence represents the quality assessment qualified rate of the historical query statements in the same historical statement template, comprises: Counting the total number of historical query statements in the historical query statement set that belong to the same historical statement template and the number of qualified statements whose historical quality assessment results are qualified in the historical query statements in the same historical statement template; The ratio between the number of qualified sentences and the total number of sentences is determined as the confidence of the corresponding historical sentence template.

3. The method for evaluating the quality of a query statement according to claim 1, characterized in that: In response to the template matching result indicating a matching failure, the step of determining the target confidence of the target query statement according to the template similarity between each historical statement template and the target statement template comprises: Normalizing the template similarity between each historical sentence template and the target sentence template, and determining the normalized template similarity as the weight value of the corresponding historical sentence template; The weight value of each historical sentence template and the confidence of the corresponding historical sentence template are weighted and summed to obtain the target confidence of the target query sentence.

4. The method for evaluating the quality of a query statement according to claim 1, wherein: The step of obtaining a target query statement to be evaluated according to the quality evaluation instruction in response to receiving the query statement quality evaluation instruction includes: In response to receiving a quality assessment instruction for a query statement, obtaining an initial query statement to be assessed according to the quality assessment instruction; Performing a grammar check on the initial query statement to obtain a grammar check result; In response to the grammar detection result indicating that the grammar is correct, the initial query statement is determined as the target query statement.

5. The method for evaluating the quality of a query statement according to claim 1, characterized in that: The quality assessment result includes a qualified quality assessment and an unqualified quality assessment. The step of determining the target quality assessment result of the target query statement according to the target confidence of the target query statement includes: In response to the target confidence being greater than a preset confidence threshold, determining the target quality assessment result of the target query statement as qualified in the quality assessment; In response to the target confidence being less than or equal to the preset confidence threshold, the target quality assessment result of the target query statement is determined as failing the quality assessment.

6. The method for evaluating the quality of a query statement according to claim 1, characterized in that: After the step of determining the target quality assessment result of the target query sentence according to the target confidence of the target query sentence, the method further includes: Parsing the target query statement to obtain at least one piece of column information associated with the target query statement; Generate at least one candidate index according to each column information; The target query statement is evaluated for index benefits according to each candidate index to obtain a recommended index set.

7. The method for evaluating the quality of a query statement according to claim 6, characterized in that: The step of performing index benefit evaluation processing on the target query statement according to each candidate index to obtain a recommended index set includes: Obtain the index benefit value of each candidate index; In response to the index benefit value corresponding to each candidate index being greater than a preset index benefit threshold, the recommended index set is determined according to the corresponding candidate index.

8. An electronic device, characterized in that: include: A memory and a processor, wherein the memory stores program instructions, and the processor retrieves the program instructions from the memory to execute the method according to any one of claims 1 to 7.

9. A computer-readable storage medium, characterized in that: include: Program data is stored, and when the program data is executed by a processor, it is used to implement the method according to any one of claims 1 to 7.

Images