Method, device and medium for sql optimization based on dynamic statistical feature perception

Abstract

The application relates to the technical field of data optimization, and discloses a SQL optimization method based on dynamic statistical feature perception, equipment and a medium. The method comprises the following steps: identifying and analyzing a to-be-optimized SQL, and extracting table fields in filtering, association, grouping or sorting conditions as predicate columns; calculating the selectivity of each predicate column, and determining a predicate column with selectivity lower than a threshold value or a single value proportion exceeding a preset proportion as a key column; extracting column-level statistical information including histogram boundary values, null value proportions, different value quantities and the frequency of the most common values for the key column, and converting the column-level statistical information into a natural language statistical summary through a statistical feature descriptor; constructing a prompt word context based on an original SQL statement, table structure information of the key column and the summary, and inputting a large language model to generate a candidate SQL. According to the application, key columns are dynamically screened, and a natural language description of data distribution perception is generated, so that the consumption of Token input of the large language model is reduced, irrelevant information interference is avoided, and the SQL optimization precision is improved.

Description

Technical Field

[0001] This invention relates to the field of data optimization technology, specifically to an SQL optimization method, device, and medium based on dynamic statistical feature perception. Background Technology

[0002] With the rapid development of large language model technology, SQL optimization methods based on large language models have gradually become a research hotspot. Existing methods typically input the table structure metadata and execution plan involved in the SQL statement into the model, and the model directly outputs the optimized SQL. However, in real-world business scenarios, query statements often involve multiple filtering, join, grouping, or sorting conditions, and data tables commonly exhibit phenomena such as data skew and concentration of null values. To address these issues, existing technologies, when optimizing SQL statements, require inputting a large amount of metadata, leading to high token (the input unit of the large language model) consumption and distracted model attention. Furthermore, the underlying statistical information of the database is not effectively utilized by the model, making it difficult for the optimization results to accurately reflect the actual data distribution, resulting in unstable optimization performance.

[0003] Therefore, the technical challenge lies in how to reduce input redundancy and enhance the model's ability to perceive data distribution characteristics in order to improve the accuracy and reliability of SQL optimization. Summary of the Invention

[0004] In view of the above problems, embodiments of the present invention provide a SQL optimization method, device and medium based on dynamic statistical feature perception, which is used to solve the technical problem of how to reduce input redundancy and improve the model's ability to perceive data distribution features in order to improve the accuracy and reliability of SQL optimization.

[0005] According to one aspect of the present invention, a method is provided to identify and parse the original SQL statement to be optimized, and extract the table fields corresponding to preset query conditions in the original SQL statement as predicate columns; the preset query conditions include query conditions for filtering, association, grouping or sorting. Calculate the selectivity of each predicate column, and determine the predicate columns whose selectivity is lower than a preset threshold or whose single value ratio exceeds a preset proportion as key columns; For the key columns, extract column-level statistical information that reflects the data distribution characteristics, and convert the column-level statistical information into a natural language statistical summary of the corresponding data distribution characteristics; Based on the original SQL statement, the table structure information of the corresponding key columns related to this query extracted from the database metadata, and the natural language statistical summary, a prompt word context for inputting into the large language model is constructed. The context of the prompt word is input into a large language model for parsing and reasoning, and candidate SQL statements are generated by rewriting. The candidate SQL statements are then output as the optimization result.

[0006] Furthermore, the step of outputting the candidate SQL statement as the optimization result also includes: Execution plans are pre-simulated for both the original SQL statement and the candidate SQL statement to obtain the estimated execution costs of the corresponding execution plans. The execution plan includes bottleneck feature information such as high-cost operator types and estimated number of rows scanned. The estimated execution cost is the quantified resource consumption value output by the database optimizer. The high-cost operator types are operators in the execution plan whose individual resource consumption values ​​exceed a preset threshold. Based on the estimated execution costs of the original SQL statement and the candidate SQL statement, the optimization rate of the candidate SQL statement relative to the original SQL statement is calculated to obtain the cost comparison results, and based on the cost comparison results, it is determined whether the optimization rate has reached the preset optimization threshold. If the preset optimization threshold is reached, the candidate SQL statement will be output as the optimization result.

[0007] Furthermore, after the step of determining whether the optimization rate has reached the preset optimization threshold based on the cost comparison result, the method further includes: If the preset optimization threshold is not reached, iterative optimization is initiated. The iterative optimization includes: taking the cost comparison results, bottleneck feature information in the execution plan that leads to increased execution cost, the original SQL statement, the candidate SQL statement generated before this iteration and its corresponding estimated execution cost as negative feedback information, inputting them into the large language model for iterative optimization, continuously iterating and optimizing within a preset number of iterations until the optimization rate reaches the preset optimization threshold, and then updating the candidate SQL statement.

[0008] Further, the step of identifying and parsing the original SQL statement to be optimized, and extracting the table fields corresponding to the preset query conditions in the original SQL statement as predicate columns, includes: Receive and recognize the SQL statement input by the user to obtain the original SQL statement to be optimized; The SQL syntax parser is used to perform lexical and syntactic analysis on the SQL statement to be optimized, generating an abstract syntax tree; Based on the abstract syntax tree, the WHERE filter condition, JOIN association condition, grouping field and sorting field in the SQL statement are identified to obtain the identification result; Extract all corresponding table fields from the recognition results and use them as predicate columns.

[0009] Further, the step of extracting column-level statistical information reflecting data distribution characteristics for the key column and converting the column-level statistical information into a natural language statistical summary of the corresponding data distribution characteristics includes: For the key column, column-level statistical information reflecting the data distribution characteristics is extracted from the system statistical view of the database. The column-level statistical information includes at least one of the following: histogram boundary values, proportion of null values, number of different values, most common values ​​and their frequency of occurrence, and inter-column correlation coefficient. The extracted column-level statistical information is semantically translated according to preset transformation rules using a statistical feature descriptor to obtain the translation result. Based on the corresponding data distribution characteristics and translation results, a natural language statistical summary is generated.

[0010] Further, the step of performing execution plan simulations for the original SQL statement and the candidate SQL statement respectively, and obtaining the estimated execution costs of the corresponding execution plans for the original SQL statement and the candidate SQL statement, includes: On the read-only standby instance of the database, the EXPLAIN command is executed on the original SQL statement and the candidate SQL statement respectively to perform execution plan simulation and obtain the corresponding execution plan; The execution plans are analyzed separately, and the quantitative resource consumption values ​​output by the database optimizer are extracted from the execution plans to obtain the estimated execution cost of the original SQL statement and the estimated execution cost of the candidate SQL statement.

[0011] Further, the step of calculating the optimization rate of the candidate SQL statement relative to the original SQL statement based on the estimated execution cost of the original SQL statement and the candidate SQL statement, obtaining a cost comparison result, and determining whether the optimization rate reaches a preset optimization threshold based on the cost comparison result includes: Compare the estimated execution cost reduction of the candidate SQL statement relative to the original SQL statement, calculate the percentage of the reduction relative to the estimated execution cost of the original SQL statement, and obtain the optimization rate of the candidate SQL statement relative to the original SQL statement. The calculated optimization rate is compared with a preset optimization threshold to determine whether the comparison result reaches the preset optimization threshold. If the preset optimization threshold is reached, the candidate SQL statement is output as the final optimization result.

[0012] Furthermore, if the optimization rate fails to reach the preset optimization threshold within the preset number of iterations, or if the optimization rate generated by n consecutive iterations fails to reach the preset growth target, the iterative optimization will be terminated, and the corresponding optimization prompt information will be output, where n is a preset positive integer.

[0013] According to another aspect of the present invention, a computer device is provided, including: a processor, a memory, a communication interface, and a communication bus, wherein the processor, the memory, and the communication interface communicate with each other through the communication bus; The memory is used to store at least one executable instruction, which causes the processor to perform the operation of the SQL optimization method based on dynamic statistical feature awareness described above.

[0014] According to another aspect of the present invention, a computer-readable storage medium is provided, the storage medium storing at least one executable instruction that causes a computer device to perform the operation of the SQL optimization method based on dynamic statistical feature awareness described in any one of the preceding embodiments.

[0015] This invention identifies predicate columns and filters key columns, extracting column-level statistical information reflecting data distribution characteristics only from key columns affecting performance. This information is then converted into a natural language statistical summary. Based on the original SQL statement, table structure information of the key columns, and the natural language statistical summary, a contextual prompt is constructed and input into a large language model to generate candidate SQL statements. Compared to existing technologies that indiscriminately input all table metadata, this embodiment significantly reduces the number of tokens input to the large language model by filtering key columns, lowering the call cost and avoiding interference from irrelevant column information on the model's attention. Furthermore, converting numerical statistical information into a natural language summary allows the model to directly understand data distribution characteristics, overcoming the shortcomings of existing technologies where statistical information is difficult for models to effectively utilize. Additionally, based on a concise input rich in data distribution awareness, the model can more accurately focus on performance bottlenecks, improving optimization accuracy and reliability. This addresses the problems of information redundancy and difficulty in utilizing statistical information in the SQL optimization process of existing technologies at a low cost.

[0016] The above description is merely an overview of the technical solutions of the embodiments of the present invention. In order to better understand the technical means of the embodiments of the present invention and to implement them in accordance with the contents of the specification, and to make the above and other objects, features and advantages of the embodiments of the present invention more apparent and understandable, specific embodiments of the present invention are described below. Attached Figure Description

[0017] The accompanying drawings are for illustrative purposes only and are not intended to limit the invention. Furthermore, the same reference numerals denote the same parts throughout the drawings. In the drawings: Figure 1 A flowchart illustrating the SQL optimization method based on dynamic statistical feature awareness provided in an embodiment of the present invention is shown. Figure 2 A schematic diagram of the structure of a computer device provided in an embodiment of the present invention is shown. Detailed Implementation

[0018] Exemplary embodiments of the invention will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the invention are shown in the drawings, it should be understood that the invention can be implemented in various forms and should not be limited to the embodiments set forth herein.

[0019] Figure 1 The flowchart illustrates a SQL optimization method based on dynamic statistical feature awareness provided in an embodiment of the present invention. This method is executed by a computer device. The computer device can be a desktop computer, laptop computer, tablet computer, smart terminal, distributed device, etc. Figure 1 As shown, the method includes the following steps: S110. Identify and parse the original SQL statement to be optimized, and extract the table fields corresponding to the preset query conditions in the original SQL statement as predicate columns; the preset query conditions include query conditions used for filtering, association, grouping or sorting.

[0020] In existing database SQL optimization techniques, a common practice is to input the complete metadata of the tables involved in the SQL statement to be optimized (including information such as the types of all columns and indexes) and the execution plan into a large language model, which then outputs the optimized SQL all at once. This full input method has significant drawbacks: for complex queries involving multiple table joins or containing a large number of fields, the metadata of the entire table may contain a large number of columns that are irrelevant to the current query. Inputting this irrelevant information into the large language model consumes a lot of tokens and may also distract the model, making it difficult for it to focus on the key columns that truly affect performance.

[0021] To address the aforementioned issues, this step parses the SQL statement to extract table fields directly related to query performance, defining these fields as "predicate columns." These are called "predicate columns" because, in database theory, the conditional expressions in clauses such as WHERE and JOIN are called "predicates," and the table fields appearing in these predicate conditions are the predicate columns. Considering that while the GROUP BY and ORDER BY clauses do not directly constitute filtering predicates, their fields still significantly affect the cost of sorting and grouping operations in the execution plan, this step specifically limits the preset query conditions to the following four categories: WHERE filtering conditions, JOIN join conditions, GROUP BY grouping fields, and ORDER BY sorting fields. The table fields referenced by these four types of clauses are the primary reference objects when the database optimizer generates the execution plan and are also an important source of determining the query execution cost (common knowledge in this field). Other clauses (such as HAVING and LIMIT) either involve columns that already appear in GROUP BY or aggregate functions, or do not involve specific columns, and are therefore not included in the extraction scope to avoid duplicate or invalid extraction. The extraction process described above narrows the scope of columns to be analyzed from all columns in the entire table to only the predicate columns directly related to the core operation of the current query. This step helps reduce the token consumption of the large input language model, mitigates the potential interference of irrelevant column information on the model's attention, and provides a precise and minimized input range for subsequent dynamic filtering of "key columns."

[0022] Specifically, in an optional approach, the step of identifying and parsing the original SQL statement to be optimized, and extracting the table fields corresponding to the preset query conditions in the original SQL statement as predicate columns, includes: S1110: Receive and recognize the SQL statement input by the user to obtain the original SQL statement to be optimized; S1120. Use the SQL syntax parser to perform lexical and syntactic analysis on the SQL statement to be optimized, and generate an abstract syntax tree; S1130. Based on the abstract syntax tree, identify the WHERE filtering condition, JOIN association condition, grouping field and sorting field in the SQL statement, and obtain the identification result; S1140. Extract all corresponding table fields from the recognition results as predicate columns.

[0023] In this embodiment, the user-submitted SQL statement to be optimized is first received and preliminarily validated to ensure the validity of subsequent parsing. Then, an SQL syntax parser (such as ANTLR, JavaCC, etc.) is used to perform lexical and syntactic analysis on the SQL statement, converting it into an abstract syntax tree (AST). The AST is a standard structured representation of the SQL statement, clearly expressing the hierarchical relationships and field references between clauses. Subsequent extraction based on this tree is deterministic and reproducible. Next, the AST is traversed to locate and identify four types of nodes: WHERE filtering conditions, JOIN association conditions, grouping fields, and sorting fields, obtaining the identification results. Finally, all corresponding table fields are extracted from the identification results, forming a set of predicate columns. These steps achieve a precise conversion from the original SQL statement to predicate columns, excluding all fields not appearing in the core query conditions, thereby reducing the amount of information required for subsequent processing at the source.

[0024] S120. Calculate the selectivity of each predicate column, and determine the predicate columns whose selectivity is lower than a preset threshold or whose single value ratio exceeds a preset proportion as key columns.

[0025] In existing databases, the impact of different columns in a table on query performance varies greatly: some columns effectively filter most of the data and are the focus of index optimization; others suffer from severe data skew (e.g., a certain value dominates), and even with an index, performance may not improve. Indiscriminately extracting statistical features at the same depth for all columns introduces a large amount of meaningless computation and contextual noise.

[0026] To address the aforementioned issues, this step performs selectivity calculations and single-value percentage analysis on the predicate columns extracted in the previous step. Selectivity refers to the proportion of rows satisfying the predicate condition out of the total number of rows in the table, calculated as: Selectivity = Estimated number of rows satisfying the condition / Total number of rows in the table. Lower selectivity means the predicate filters out more data rows, potentially impacting query performance. Single-value percentage refers to the proportion of the most frequent single value in the column. When the single-value percentage of a column exceeds a preset percentage (e.g., 90%), it indicates severe data skew in that column. In this case, even if the column has an index, the cost of an index scan may be higher than a full table scan due to the large number of data rows accessed. Based on the above calculations, this step identifies predicate columns that meet either of the following conditions as "key columns": first, selectivity is below a preset threshold (e.g., 5%); second, single-value percentage exceeds a preset percentage (e.g., 90%). These two types of columns are the target predicate columns for subsequent in-depth statistical information extraction and focused analysis. Predicate columns that are neither low-selectivity nor severely skewed are excluded from the scope of key columns, thereby avoiding unnecessary extraction of statistical information.

[0027] The technical advantage of this step lies in its ability to dynamically identify the data columns in the predicate column that are truly likely to affect the execution plan through a dual screening process of selectivity and single-value proportion. This avoids information redundancy caused by the indiscriminate processing of all columns in traditional methods. It helps to further reduce the amount of statistical information required to input into large language models, reduce token consumption, and enable the model to focus on the true performance bottleneck columns, thereby improving optimization accuracy.

[0028] S130. For the key columns, extract column-level statistical information that reflects the data distribution characteristics, and convert the column-level statistical information into a natural language statistical summary of the corresponding data distribution characteristics.

[0029] Existing technologies that use large language models to assist SQL optimization typically only input table structure metadata (such as field types and index existence), lacking a deep understanding of data distribution characteristics. For example, the model cannot detect statistical features that cause indexes to become ineffective, such as "although an index is created, 99% of the data in that column has the same value." It also cannot identify key information affecting execution costs, such as data skewness, histogram distribution, and null value rate. This lack of deep statistical feature awareness often leads to optimization suggestions that are out of touch with the actual data distribution, resulting in optimization failures.

[0030] To address the aforementioned issues, this step first extracts column-level statistical information reflecting data distribution characteristics from the database's system statistical view, targeting the key columns identified in the previous step. This column-level statistical information includes, but is not limited to: histogram boundary values ​​(reflecting the interval characteristics of data distribution), the proportion of null values ​​(reflecting the data integrity of the column), the number of distinct values ​​(NDV, reflecting data discriminability), the most common values ​​and their frequency of occurrence (reflecting the degree of data skewness), and the inter-column correlation coefficient (reflecting the data association characteristics between multiple columns). Then, this step uses a statistical feature descriptor to semantically translate the aforementioned numerical column-level statistical information according to preset transformation rules, converting it into a natural language statistical summary of the corresponding data distribution characteristics. This statistical feature descriptor is a rule-based conversion module used to convert database column-level statistical values ​​into natural language text.

[0031] Specifically, in one embodiment, the step of extracting column-level statistical information reflecting data distribution characteristics for the key column and converting the column-level statistical information into a natural language statistical summary corresponding to the data distribution characteristics includes: S1301. For the key column, extract column-level statistical information reflecting the data distribution characteristics from the system statistical view of the database. The column-level statistical information includes at least one of the following: histogram boundary value, proportion of null values, number of different values, most common value and its frequency of occurrence, and inter-column correlation coefficient. S1302. Using a statistical feature descriptor, the extracted column-level statistical information is semantically translated according to preset transformation rules to obtain the translation result. S1303. Based on the corresponding data distribution characteristics and translation results, integrate and generate a natural language statistical summary.

[0032] In this embodiment, the aforementioned statistical information for key columns is first extracted from the database system's statistical views (e.g., PostgreSQL's pg_stats or MySQL's information_schema). This statistical information is the core basis for the database optimizer's cost estimation and accurately reflects the data distribution. Then, a statistical feature descriptor is used to semantically translate the extracted numerical statistics. Examples of preset transformation rules include: if the proportion of null values ​​exceeds 70%, it is translated as "the column has a high proportion of null values"; if the frequency of the most common value exceeds 90%, it is translated as "the column has severe data skew"; if the number of distinct values ​​is less than 10, it is translated as "the column has extremely low discrimination". Finally, the translation results are integrated into a coherent natural language text according to a logical order (e.g., first describing the null value situation, then describing data skew, and finally describing discrimination), forming a natural language statistical summary. This summary is directly input into the large language model as part of the context of subsequent prompt words.

[0033] For example, if the original statistical data is "null_frac=0.8, most_common_val='A', freq=0.9, NDV=3", the converted natural language summary would be: "The 'status' column has severe data skew, with 80% being null values, and 90% of the non-null values ​​being 'A'. The data has extremely low discriminative power (only 3 distinct values). Creating a regular B-tree index on this column may be ineffective. It is recommended to ignore this filter condition or refactor the query logic." This statistical feature descriptor can be implemented based on rule templates or fine-tuned based on a lightweight language model.

[0034] This step extracts deep column-level statistical information and converts it into natural language summaries, enabling the large language model to accurately perceive key factors affecting the execution plan, such as data skew, null value distribution, and discrimination, thus avoiding mis-optimization due to a lack of understanding of data distribution. At the same time, extracting only key columns avoids redundancy in full statistical information and further reduces token consumption. The statistical feature descriptor bridges the semantic gap between database statistical information and the understanding ability of the large language model, enabling the model to fully understand the data distribution.

[0035] S140. Based on the original SQL statement, the table structure information of the corresponding key columns related to this query extracted from the database metadata, and the natural language statistical summary, construct the prompt word context for inputting into the large language model.

[0036] This step constructs a prompt word context for inputting into the large language model based on the original SQL statement, table structure information of key columns, and the natural language statistical summary generated in the previous step. This avoids inputting the entire table structure metadata (including all column field types, index information, constraints, etc.) and execution plan into the large language model, which would lead to context redundancy and a large amount of column information irrelevant to performance analysis, distracting the model and making it difficult to focus on the key columns that truly affect the execution plan. Therefore, this step constructs a concise, high signal-to-noise ratio input context containing only the core information necessary for optimization.

[0037] The table structure information is extracted from database metadata only for key columns, including field names, data types, whether null values ​​are allowed, and existing index types, excluding any metadata for non-key columns. The prompt context consists of three parts: the original SQL statement, a table structure summary of the key columns, and a natural language statistical summary. This context does not contain information about all columns of the entire table, nor does it contain a complete execution plan, thus forming a lightweight input focused on performance bottlenecks.

[0038] Compared to the traditional approach of inputting all table metadata and execution plans, this step significantly reduces the number of input tokens by constructing a prompt word context. It also eliminates a large amount of irrelevant information from interfering with the model's attention, allowing the large language model to focus on processing the core information that truly affects performance, thereby improving optimization accuracy and response speed.

[0039] S150. Input the context of the prompt word into the large language model for parsing and reasoning, rewrite and generate candidate SQL statements, and output the candidate SQL statements as the optimization result.

[0040] In this step, the context of the prompt words constructed in step S140 is input into the large language model. The large language model can be a model with code understanding and generation capabilities, such as GPT-4, Qwen, or CodeLlama. Based on the data distribution truth revealed by the natural language statistical summary in the prompt word context (such as data skew, high null value ratio, etc.), the model combines the original SQL statement and table structure information of key columns to perform semantically equivalent rewriting. Rewriting directions include, but are not limited to: removing invalid filter conditions, adjusting the JOIN order, rewriting subqueries as JOINs, suggesting the creation of composite indexes and recommending the order of index columns, or suggesting the use of materialized views, etc. The candidate SQL statements generated by the model are output as preliminary optimization results. This step, based on real data distribution characteristics driving the large language model to rewrite SQL, makes the optimization suggestions targeted and implementable. Compared with traditional methods that rely solely on static metadata, it can effectively avoid erroneous optimizations caused by a lack of understanding of data distribution, significantly improving the success rate of the first optimization.

[0041] In summary, the SQL optimization method based on dynamic statistical feature awareness provided in this application identifies and extracts predicate columns from the SQL statement to be optimized, narrowing the analysis scope from all columns of the entire table to a subset of columns directly related to the core query operation. It then calculates the selectivity and single-value ratio of each predicate column, dynamically filtering out key columns that may truly affect the execution plan, avoiding indiscriminate processing of all columns. For key columns, it extracts deep column-level statistical information such as histogram boundary values, null value ratio, number of distinct values, and frequency of the most common value, and converts this information into a natural language statistical summary using a statistical feature descriptor. This enables the large language model to accurately perceive data distribution characteristics such as data skew, null value concentration, and low discriminative power. Finally, based on the original SQL statement, the table structure information of the key columns, and the natural language statistical summary, it constructs a concise prompt context, inputs it into the large language model, and generates candidate SQL statements. Compared to existing techniques that input all table metadata and execution plans into a large language model, this method significantly reduces input token consumption, thereby decreasing call costs and improving response speed. Simultaneously, through dynamic statistical feature awareness, it effectively avoids mis-optimizations caused by a lack of data distribution awareness (e.g., suggesting indexes on severely skewed columns), improving the accuracy of optimization suggestions. Furthermore, it excludes a large amount of column information irrelevant to query performance, allowing the large language model to focus its attention on the true performance bottleneck columns, reducing the risk of attention diversion. Based on standard SQL syntax parsing and database system statistical views, this method is adaptable to various mainstream relational databases and has good versatility.

[0042] Furthermore, in one embodiment, the step of outputting the candidate SQL statement as the optimization result further includes: S151. Perform execution plan pre-playing on the original SQL statement and the candidate SQL statement respectively to obtain the estimated execution cost of the corresponding execution plans of the original SQL statement and the candidate SQL statement; the execution plan includes bottleneck feature information including high-cost operator type and estimated number of rows scanned, and the estimated execution cost is the quantitative resource consumption value output by the database optimizer; the high-cost operator type is the operator in the execution plan whose sub-item resource consumption value exceeds the preset occupation threshold; S152. Based on the estimated execution cost of the original SQL statement and the candidate SQL statement, calculate the optimization rate of the candidate SQL statement relative to the original SQL statement, obtain the cost comparison result, and determine whether the optimization rate reaches the preset optimization threshold based on the cost comparison result. S153. If the preset optimization threshold is reached, the candidate SQL statement is output as the optimization result.

[0043] In the process of generating SQL using a large language model, there is a risk of "illusion," meaning the model may generate syntactically correct but logically flawed or inefficient SQL statements (known as "illusion SQL statements"). Directly using candidate SQL statements generated by the model in a production environment may introduce new performance bottlenecks or even cause system failures. Current technologies lack a quantitative verification mechanism for optimization results and cannot automatically determine whether candidate SQL statements are truly superior to the original SQL statements. Therefore, it is necessary to introduce cost evaluation of the database optimizer as an objective verification method.

[0044] To address the aforementioned issues, this embodiment introduces a cost evaluation mechanism for the database optimizer. First, the estimated execution costs of the original SQL statement and candidate SQL statements, as well as bottleneck characteristics in the execution plan, are obtained through execution plan pre-drafting. Then, the optimization rate is calculated based on the estimated execution cost and compared with a preset optimization threshold. Finally, candidate SQL statements are output only when the optimization rate reaches the threshold; otherwise, they are not output or subsequent iterations are triggered.

[0045] Specifically, in an optional approach, the step of performing execution plan simulations on the original SQL statement and the candidate SQL statement respectively, and obtaining the estimated execution costs of the corresponding execution plans for the original SQL statement and the candidate SQL statement, includes: S1511. On the read-only standby instance of the database, execute the EXPLAIN command (execution plan viewing command) on the original SQL statement and the candidate SQL statement respectively to perform execution plan preview and obtain the corresponding execution plan; S1512. Analyze the execution plans respectively, extract the quantitative resource consumption value output by the database optimizer from the execution plans, and obtain the estimated execution cost of the original SQL statement and the estimated execution cost of the candidate SQL statement.

[0046] In this embodiment, the EXPLAIN command (e.g., EXPLAIN(FORMATJSON)) is first executed on the original SQL statement and the candidate SQL statement on the read-only standby instance of the database. This command only generates an execution plan and does not actually run the query, so it will not affect production data. Then, the obtained execution plans are parsed, and the quantitative resource consumption value output by the database optimizer is extracted as the estimated execution cost (e.g., total_cost in PostgreSQL or cost in MySQL). At the same time, the bottleneck feature information in the execution plan is recorded, including the high-cost operator type (e.g., full table scan, nested loop join, sort overflow, etc.) and the estimated number of rows scanned. The high-cost operator type is defined as the operator in the execution plan whose individual resource consumption value exceeds a preset threshold (e.g., exceeds 30% of the total plan cost).

[0047] Next, based on the estimated execution costs of the original SQL statement and the candidate SQL statement, the optimization rate of the candidate SQL statement relative to the original SQL statement is calculated.

[0048] In one embodiment, the step of calculating the optimization rate of the candidate SQL statement relative to the original SQL statement based on the estimated execution cost of the original SQL statement and the candidate SQL statement, obtaining a cost comparison result, and determining whether the optimization rate reaches a preset optimization threshold based on the cost comparison result includes: S1521. Compare the estimated execution cost reduction of the candidate SQL statement relative to the original SQL statement, calculate the proportion of the reduction to the estimated execution cost of the original SQL statement, and obtain the optimization rate of the candidate SQL statement relative to the original SQL statement. S1522. Compare the calculated optimization rate with the preset optimization threshold to determine whether the comparison result reaches the preset optimization threshold; if the preset optimization threshold is reached, output the candidate SQL statement as the final optimization result.

[0049] First, compare the estimated execution cost reduction of the candidate SQL statement relative to the original SQL statement, and calculate the percentage of this reduction relative to the estimated execution cost of the original SQL statement. The formula is: Optimization Rate = (Original Cost - Candidate Cost) / Original Cost × 100%. Compare the calculated optimization rate with a preset optimization threshold (e.g., 15%) to determine if the threshold has been reached. Finally, if the optimization rate reaches or exceeds the threshold, the optimization is considered successful, and the candidate SQL statement is output as the final optimization result. If the threshold is not reached, the candidate SQL statement is not output, and further iterative optimization can be triggered, or the original SQL statement can be retained. It should be noted that even if the calculated optimization rate reaches the preset optimization threshold, if the execution plan of the candidate SQL statement introduces a high-cost operator that is not present in the execution plan of the original SQL statement and whose resource consumption exceeds the preset threshold (e.g., the original SQL statement does not have a full table scan, but the candidate SQL statement does), the optimization is still considered unsuccessful, and the candidate SQL statement is not output. This rule is used to avoid introducing more serious performance bottlenecks due to local cost reductions.

[0050] Through the aforementioned cost verification mechanism, this embodiment can quickly and losslessly quantify and evaluate the performance improvement of candidate SQL statements without actually running the query, effectively eliminating the risk of "illusion SQL statements" being directly deployed, and ensuring that the final output SQL statement is indeed superior to the original SQL statement in terms of estimated cost, thereby providing users with quantifiable performance assurance.

[0051] In one embodiment, after the step of determining whether the optimization rate has reached a preset optimization threshold based on the cost comparison result, the method further includes: S160. If the preset optimization threshold is not reached, iterative optimization is initiated. The iterative optimization includes: taking the cost comparison result, the bottleneck feature information in the execution plan that leads to increased execution cost, the original SQL statement, the candidate SQL statement generated before this iteration and its corresponding estimated execution cost as negative feedback information, inputting them into the large language model for iterative optimization, continuously iterating and optimizing within a preset number of iterations until the optimization rate reaches the preset optimization threshold, and then updating the candidate SQL statement.

[0052] In this embodiment, when the optimization rate of a candidate SQL statement fails to reach the preset optimization threshold after cost verification, it indicates that the performance improvement of the currently generated candidate SQL statement is insufficient or degraded, requiring the initiation of an iterative optimization mechanism. Specifically, the system first constructs negative feedback information, which includes the following: cost comparison results (e.g., the specific value of the optimization rate and its difference from the threshold), bottleneck feature information in the execution plan that leads to increased execution costs (e.g., full table scan operators, nested loop joins, etc.), the original SQL statement, and the candidate SQL statements generated before this iteration and their corresponding estimated execution costs. This negative feedback information, along with the historical dialogue context, is input into the large language model to trigger a new round of optimization generation. Based on the negative feedback information, the large language model can clearly understand the reasons for the failure of the previous round of candidate SQL statements (e.g., the introduction of a full table scan leading to increased costs), and adjust the optimization strategy accordingly to generate new candidate SQL statements. The system continuously loops the process of "generating candidate SQL statements → cost verification → constructing negative feedback if the target is not met → regenerating" within a preset number of iterations (e.g., 5 times) until the optimization rate reaches the preset optimization threshold. Once the optimization rate reaches a threshold, the candidate SQL statements for the current round are updated to the final optimized result and output. Through this iterative optimization mechanism, this embodiment achieves an automated closed loop of "generation—verification—feedback—regeneration," enabling the large language model to make targeted improvements based on the previous round, avoiding blind retries, significantly improving optimization efficiency and convergence speed, thereby adaptively approximating the optimal execution plan and further eliminating the risk of phantom SQL statements.

[0053] To prevent the iterative optimization process from looping indefinitely and wasting computational resources, and to ensure that the system can terminate in a timely manner when no significant benefits are obtained, clear iterative termination conditions are set.

[0054] Specifically, in one embodiment, if the optimization rate fails to reach the preset optimization threshold within a preset number of iterations, or if the optimization rate generated by n consecutive iterations fails to reach the preset growth target, the iterative optimization is terminated and the corresponding optimization prompt information is output, where n is a preset positive integer.

[0055] In this embodiment, the system presets a maximum number of iterations (e.g., 5 times) and a target for the optimization rate increase over n consecutive iterations (e.g., the optimization rate improvement for two consecutive iterations is less than 1%). Iterative optimization terminates when either of the following conditions is met: First, within the preset number of iterations, the optimization rate of the candidate SQL statements generated in each round does not reach the preset optimization threshold; second, although the maximum number of iterations has not been reached, the optimization rate improvement for n consecutive iterations does not reach the preset growth target, indicating that the optimization effect has converged and further iterations are unlikely to yield significant improvement. After terminating the iteration, the system outputs corresponding optimization prompts, such as: "After 5 rounds of iterative optimization, the optimal optimization rate is 12%, which does not reach the preset threshold of 15%. The original SQL statement has been returned. It is recommended to manually adjust the optimization threshold or check whether the statistics are accurate." This prompt helps users understand the reason for the optimization failure and provides a basis for manual intervention. Through this termination condition mechanism, this embodiment ensures that the optimization process ends within a finite number of steps, avoiding the consumption of computing resources by invalid iterations, and enhancing the interpretability and user experience of the system by outputting prompts.

[0056] In one embodiment, an optimization report is generated along with the final optimization result. The optimization report includes: a comparison of SQL statements before and after optimization, a comparison of execution plans and cost changes before and after optimization, an iterative optimization log (including candidate SQL statements generated in each round, estimated execution costs, and feedback information), and additional optimization suggestions (such as suggestions to create new indexes, suggestions to refactor business queries, etc.).

[0057] The following uses a PostgreSQL database as an example to illustrate the complete implementation process of this invention in a specific business scenario. An e-commerce platform's `orders` table contains 100 million records. The `status` field stores the order status (of which 'COMPLETED' accounts for 95%), and the `created_at` field is a timestamp with a B-tree index. The original SQL statement is: `SELECT * FROM orders WHERE status = 'COMPLETED' AND created_at > '2026-01-01'`. The system first parses the original SQL statement, extracting the predicate columns `status` and `created_at`. Calculating the selectivity, it finds that `status` has a selectivity of 95% (extremely low filtering effect), and `created_at` has a selectivity of 8% (medium filtering effect), marking `status` as a key column. Then, it extracts statistical information from the `pg_stats` column of the `status` column and generates a natural language summary: "The `status` column has a 95% share, indicating a severely skewed column. Using an index on this column may be ineffective; it is recommended to prioritize using the `created_at` column index." The LLM generates candidate SQL statements based on this summary (such as removing the `status` condition or creating a composite index). The estimated cost is then obtained through EXPLAIN simulation, and the optimization rate is calculated. If the target is not met, negative feedback is constructed for iterative optimization until the threshold is met or the maximum number of iterations is reached. Finally, the optimized candidate SQL statement and optimization report are output. This example demonstrates how this method effectively avoids misuse of indexes on severely skewed columns, significantly improving query performance.

[0058] Figure 2 The diagram shows a structural schematic of a computer device provided in an embodiment of the present invention. The specific embodiments of the present invention do not limit the specific implementation of the computer device.

[0059] like Figure 2 As shown, the computer device may include: a processor 402, a communications interface 404, a memory 406, and a communications bus 408.

[0060] The processor 402, communication interface 404, and memory 406 communicate with each other via communication bus 408. Communication interface 404 is used to communicate with other network elements such as clients or other servers. The processor 402 executes program 410, specifically performing the relevant steps described in the embodiment of the SQL optimization method based on dynamic statistical feature awareness.

[0061] Specifically, program 410 may include program code, which includes computer-executable instructions.

[0062] Processor 402 may be a central processing unit (CPU), an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement embodiments of the present invention. The computer device includes one or more processors, which may be processors of the same type, such as one or more CPUs; or processors of different types, such as one or more CPUs and one or more ASICs.

[0063] Memory 406 is used to store program 410. Memory 406 may include high-speed RAM memory, and may also include non-volatile memory, such as at least one disk storage device.

[0064] Specifically, program 410 can be called by processor 402 to cause the computer device to perform the relevant steps in the embodiment of the SQL optimization method based on dynamic statistical feature awareness.

[0065] This invention provides a computer-readable storage medium storing at least one executable instruction that, when executed on a computer device, causes the computer device to perform the SQL optimization method based on dynamic statistical feature awareness as described in any of the above method embodiments.

[0066] This invention provides a computer program that can be called by a processor to cause a computer device to execute the SQL optimization method based on dynamic statistical feature awareness in any of the above method embodiments.

[0067] This invention provides a computer program product, which includes a computer program stored on a computer-readable storage medium. The computer program includes program instructions, which, when executed on a computer, cause the computer to perform the SQL optimization method based on dynamic statistical feature awareness in any of the above method embodiments.

[0068] The algorithms or displays provided herein are not inherently related to any particular computer, virtual system, or other device. Various general-purpose systems can also be used in conjunction with the teachings herein. The required structure for constructing such systems is apparent from the above description. Furthermore, the embodiments of the present invention are not directed to any particular programming language. It should be understood that the content of the invention described herein can be implemented using various programming languages, and the above description of specific languages ​​is for the purpose of disclosing the best mode of implementation of the invention.

[0069] Numerous specific details are set forth in the specification provided herein. However, it will be understood that embodiments of the invention may be practiced without these specific details. In some instances, well-known methods, structures, and techniques have not been shown in detail so as not to obscure the understanding of this specification.

[0070] Similarly, it should be understood that, in order to streamline the invention and aid in understanding one or more of the various aspects of the invention, features of the embodiments of the invention are sometimes grouped together in a single embodiment, figure, or description thereof in the above description of exemplary embodiments of the invention. However, this disclosure should not be construed as reflecting an intention that the claimed invention requires more features than are expressly recited in each claim.

[0071] Those skilled in the art will understand that modules in the device of the embodiments can be adaptively changed and placed in one or more devices different from that embodiment. Modules, units, or components in the embodiments can be combined into a single module, unit, or component, and can be divided into multiple sub-modules, sub-units, or sub-components. Except where at least some of such features and / or processes or units are mutually exclusive, any combination can be used to combine all features disclosed in this specification (including the accompanying claims, abstract, and drawings) and all processes or units of any method or device so disclosed. Unless expressly stated otherwise, each feature disclosed in this specification (including the accompanying claims, abstract, and drawings) may be replaced by an alternative feature that serves the same, equivalent, or similar purpose.

[0072] It should be noted that the above embodiments are illustrative of the invention and not restrictive, and that those skilled in the art can devise alternative embodiments without departing from the scope of the appended claims. In the claims, any reference signs placed between parentheses should not be construed as limiting the claims. The word "comprising" does not exclude the presence of elements or steps not listed in the claims. The word "a" or "an" preceding an element does not exclude the presence of a plurality of such elements. The invention can be implemented by means of hardware comprising several different elements and by means of a suitably programmed computer. In the unit claims enumerating several means, several of these means may be embodied by the same item of hardware. The use of the words first, second, and third, etc., does not indicate any order. These words can be interpreted as names. The steps in the above embodiments, unless otherwise specified, should not be construed as limiting the order of execution.

Claims

1. A SQL optimization method based on dynamic statistical feature awareness, characterized in that, The method includes: Identify and parse the original SQL statement to be optimized, and extract the table fields corresponding to the preset query conditions in the original SQL statement as predicate columns; the preset query conditions include query conditions used for filtering, association, grouping or sorting. Calculate the selectivity of each predicate column, and determine the predicate columns whose selectivity is lower than a preset threshold or whose single value ratio exceeds a preset proportion as key columns; For the key columns, extract column-level statistical information that reflects the data distribution characteristics, and convert the column-level statistical information into a natural language statistical summary of the corresponding data distribution characteristics; Based on the original SQL statement, the table structure information of the corresponding key columns related to this query extracted from the database metadata, and the natural language statistical summary, a prompt word context for inputting into the large language model is constructed. The context of the prompt word is input into a large language model for parsing and reasoning, and candidate SQL statements are generated by rewriting. The candidate SQL statements are then output as the optimization result.

2. The method according to claim 1, characterized in that, The step of outputting the candidate SQL statement as the optimization result further includes: Execution plans are pre-simulated for both the original SQL statement and the candidate SQL statement to obtain the estimated execution costs of the corresponding execution plans. The execution plan includes bottleneck feature information such as high-cost operator types and estimated number of rows scanned. The estimated execution cost is the quantified resource consumption value output by the database optimizer. The high-cost operator types are operators in the execution plan whose individual resource consumption values ​​exceed a preset threshold. Based on the estimated execution costs of the original SQL statement and the candidate SQL statement, the optimization rate of the candidate SQL statement relative to the original SQL statement is calculated to obtain the cost comparison results, and based on the cost comparison results, it is determined whether the optimization rate has reached the preset optimization threshold. If the preset optimization threshold is reached, the candidate SQL statement will be output as the optimization result.

3. The method according to claim 2, characterized in that, After the step of determining whether the optimization rate has reached the preset optimization threshold based on the cost comparison result, the method further includes: If the preset optimization threshold is not reached, iterative optimization is initiated. The iterative optimization includes: taking the cost comparison results, bottleneck feature information in the execution plan that leads to increased execution cost, the original SQL statement, the candidate SQL statement generated before this iteration and its corresponding estimated execution cost as negative feedback information, inputting them into the large language model for iterative optimization, continuously iterating and optimizing within a preset number of iterations until the optimization rate reaches the preset optimization threshold, and then updating the candidate SQL statement.

4. The method according to claim 1, characterized in that, The step of identifying and parsing the original SQL statement to be optimized, and extracting the table fields corresponding to the preset query conditions in the original SQL statement as predicate columns, includes: Receive and recognize the SQL statement input by the user to obtain the original SQL statement to be optimized; The SQL syntax parser is used to perform lexical and syntactic analysis on the SQL statement to be optimized, generating an abstract syntax tree; Based on the abstract syntax tree, the WHERE filter condition, JOIN association condition, grouping field and sorting field in the SQL statement are identified to obtain the identification result; Extract all corresponding table fields from the recognition results and use them as predicate columns.

5. The method according to claim 1, characterized in that, The step of extracting column-level statistical information reflecting data distribution characteristics for the key columns and converting the column-level statistical information into a natural language statistical summary of the corresponding data distribution characteristics includes: For the key column, column-level statistical information reflecting the data distribution characteristics is extracted from the system statistical view of the database. The column-level statistical information includes at least one of the following: histogram boundary values, proportion of null values, number of different values, most common values ​​and their frequency of occurrence, and inter-column correlation coefficient. The extracted column-level statistical information is semantically translated according to preset transformation rules using a statistical feature descriptor to obtain the translation result. Based on the corresponding data distribution characteristics and translation results, a natural language statistical summary is generated.

6. The method according to claim 2, characterized in that, The step of performing execution plan simulations on the original SQL statement and the candidate SQL statement respectively, and obtaining the estimated execution costs of the corresponding execution plans for the original SQL statement and the candidate SQL statement, includes: On the read-only standby instance of the database, the EXPLAIN command is executed on the original SQL statement and the candidate SQL statement respectively to perform execution plan simulation and obtain the corresponding execution plan; The execution plans are analyzed separately, and the quantitative resource consumption values ​​output by the database optimizer are extracted from the execution plans to obtain the estimated execution cost of the original SQL statement and the estimated execution cost of the candidate SQL statement.

7. The method according to claim 2, characterized in that, The steps of calculating the optimization rate of the candidate SQL statement relative to the original SQL statement based on the estimated execution cost of the original SQL statement and the candidate SQL statement, obtaining the cost comparison result, and determining whether the optimization rate reaches the preset optimization threshold based on the cost comparison result include: Compare the estimated execution cost reduction of the candidate SQL statement relative to the original SQL statement, calculate the percentage of the reduction relative to the estimated execution cost of the original SQL statement, and obtain the optimization rate of the candidate SQL statement relative to the original SQL statement. The calculated optimization rate is compared with a preset optimization threshold to determine whether the comparison result reaches the preset optimization threshold. If the preset optimization threshold is reached, the candidate SQL statement is output as the final optimization result.

8. The method according to claim 3, characterized in that, If the optimization rate fails to reach the preset optimization threshold within the preset number of iterations, or if the optimization rate generated by n consecutive iterations fails to reach the preset growth target, the iteration optimization will be terminated and the corresponding optimization prompt information will be output, where n is a preset positive integer.

9. A computer device, characterized in that, include: The processor, memory, communication interface, and communication bus are provided, wherein the processor, memory, and communication interface communicate with each other via the communication bus. The memory is used to store at least one executable instruction that causes the processor to perform the operation of the SQL optimization method based on dynamic statistical feature awareness as described in any one of claims 1-8.

10. A computer-readable storage medium, characterized in that, The storage medium stores at least one executable instruction, which, when executed on a computer device, causes the computer device to perform the operation of the SQL optimization method based on dynamic statistical feature awareness as described in any one of claims 1-8.

Images