A database query optimization method and system based on large model enhancement

Abstract

The application provides a database query optimization method and system based on large model enhancement, and belongs to the technical field of database management systems. The application deeply embeds a large model into key processes of database query optimization, including SQL rewriting, cardinality estimation, cost estimation and execution plan enumeration, so as to improve the understanding and optimization ability of an optimizer for complex SQL. Through deep analysis of SQL semantics, large-scale statistical pattern recognition and pruning of a complex plan search space, the application significantly improves the quality of a query plan, reduces cardinality estimation errors, reduces a plan search space and improves the overall execution performance of a database. The application can be applied to any relational database system and has universality and high practical value.

Description

Technical Field

[0001] This invention relates to the field of database management system (DBMS) technology, and in particular to a database query optimization method and system based on large model enhancement. Background Technology

[0002] The database query optimizer is a core component of modern relational databases, its role being to transform SQL queries into efficient execution plans. Traditional query optimizers typically consist of a rule rewriting module, a cardinality estimation module, a cost model, and an execution plan enumeration module. Existing optimizers primarily rely on static rules, heuristic algorithms, and statistically based estimation models.

[0003] However, in complex SQL scenarios (such as multi-table joins, deeply nested subqueries, and non-independent filtering conditions), traditional optimizers have significant shortcomings: Large base estimation error Traditional cardinality estimation relies on histograms, MCV statistics, and simple independence assumptions. When there are correlations between columns or complex filtering conditions, the error can increase by tens of times, leading to incorrect plan selection.

[0004] Limited SQL rewriting capabilities Rule-based systems struggle to cover complex scenarios, such as subquery rewriting, predicate logic folding, and complex association analysis.

[0005] The planned search space is huge. The order of multi-table joins grows exponentially, making it difficult for traditional enumeration strategies to cover the global optimal solution.

[0006] Cost model is difficult to optimize The cost parameters such as IO, CPU, and memory rely on manual configuration and cannot adapt to complex workloads.

[0007] With the development of large model technology, its semantic understanding, reasoning, and structure generation capabilities far surpass those of traditional rule-based systems. However, in current technologies, large models are typically used for NL2SQL or SQL auto-completion, and no literature has yet deeply integrated large models into key optimizer processes, such as SQL rewriting, cardinality estimation, and plan search pruning. Therefore, a novel architecture is needed to enable large models to substantially improve database query optimization performance. Summary of the Invention

[0008] To address the above technical problems, this invention provides a database query optimization method based on large model enhancement, which overcomes the shortcomings of existing database query optimizers such as insufficient SQL rewriting, large cardinality estimation error, huge plan search space, and difficulty in accurately tuning the cost model.

[0009] The technical solution of this invention is: A database query optimization method based on large model enhancement includes the following steps: 1) Receive SQL queries and generate an initial logical query plan; 2) The large model is invoked to perform semantic-level rewriting of the logical query plan, generating multiple candidate logical plans; 3) Cardinality estimation of each operator in the candidate logical program is performed using a large model; 4) Based on the aforementioned base estimation results, call the large model to predict the cost of the physical operators; 5) Based on the cost prediction, candidate execution plans are generated or pruned from the large model; 6) Select the optimal execution plan based on the final cost and submit it to the executor for execution.

[0010] Furthermore, Semantic rewriting includes: predicate pushdown, subquery rewriting, projection pruning, join rearrangement, and CTE expansion or collapse.

[0011] The large model infers the correlation between tables based on column statistics, distribution information, and semantics when estimating cardinality.

[0012] Cost prediction includes predictions of I / O costs, CPU costs, memory usage costs, and operator-specific costs.

[0013] The large model generates candidate execution plans, including generating multiple join sequences.

[0014] The join order generation uses semantic inference rules to reduce the invalid search space.

[0015] The execution plan uses a combined scoring strategy of traditional cost model and large model cost model.

[0016] The candidate execution plans are pruned using a large model to reduce the plan search space.

[0017] Further The execution results of historical execution plans are sampled and used to update the parameters of the large model.

[0018] Furthermore, this invention also provides a database query optimization system based on large model enhancement, comprising: The SQL rewriting module receives SQL queries and generates an initial logical query plan; it then calls a large model to perform semantic-level rewriting of the logical query plan, generating several candidate logical plans. The cardinality estimation module performs cardinality estimation on each operator in the candidate logical scheme using a large model. The cost estimation module, based on the baseline estimation results, calls the large model to predict the cost of the physical operators; The plan enumeration module generates or prunes candidate execution plans from a large model based on cost prediction. The execution plan selection module selects the optimal execution plan based on the final cost and submits it to the executor for execution.

[0019] The beneficial effects of this invention are: 1) The base estimate is more accurate. LLM can understand the semantic correlation between columns and significantly reduce errors compared to traditional histograms.

[0020] 2) Enhance SQL rewriting capabilities It can handle complex subqueries and predicate logic that cannot be covered by rule systems.

[0021] 3) Significantly reduce the planned search space LLM provides high-quality candidate join order, reducing search complexity from exponential to near linear.

[0022] 4) Adaptive cost model It can update the cost factor based on historical performance, thereby improving prediction accuracy.

[0023] 5) Overall query performance is significantly improved. In multi-table join scenarios, execution time can be reduced by an average of 10% to 70%. Attached Figure Description

[0024] Figure 1 This is a schematic diagram of the overall system architecture of the present invention; Figure 2 This is a schematic diagram of the query optimization process of the present invention. Detailed Implementation

[0025] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are some embodiments of the present invention, but not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of the present invention without creative effort are within the scope of protection of the present invention.

[0026] This invention discloses a database query optimization method and system based on Large Language Model (LLM) enhancement, which deeply embeds the large model into the query optimization process, significantly improves the quality of the execution plan, and enhances the database query performance.

[0027] Specific objectives include: LLM can be used for semantic-level SQL rewriting to generate more potential optimization opportunities.

[0028] LLM can be used to improve the accuracy of cardinality estimation, especially in scenarios involving column correlation and complex predicates.

[0029] By using a large model to adaptively estimate the cost model, the accuracy of plan selection can be improved.

[0030] Using LLM to enumerate and prune candidates for the execution plan significantly reduces the search space.

[0031] Build a learnable and iterative optimization system to enable the database to automatically adapt to dynamic workloads.

[0032] The specific workflow of this invention is as follows: 1) Receive user SQL 2) The SQL parser generates the initial logical plan. 3) Perform SQL semantic-level rewriting on large models 4) Large model cardinality estimation 5) Large-scale model for cost prediction and adjustment 6) Execution plan enumeration and large model pruning 7) Select the final execution plan and execute it. Overall Deployment and Data Channel Deployment Model: LLM can be deployed as an external inference service (REST / gRPC) or an embedded lightweight model (ONNX / TorchScript). A hybrid deployment is recommended: the online master server calls the low-latency small model to handle common tasks, and the larger model is called when there is off-peak activity or when deeper inference is required.

[0033] Data channel: Each module calls the LLM service through internal RPC (gRPC) or function interface; the data is serialized using JSON or Protobuf.

[0034] Caching layer: Establish an LRU cache for common LLM requests (same SQL, same schema snapshot). The cached objects include: rewrite results, cardinality estimates, and plan candidate sets.

[0035] Statistical data storage: Global statistics, execution history, and sampled data are stored in time-series / column-oriented storage (e.g., internal system table) for use by LLM prompts / fine-tuning.

[0036] SQL parsing and preparation The goal of this phase is to convert the raw SQL into a structured intermediate representation (IR) and collect the metadata (schema, statistics, constraints, indexes, hints) required for execution.

[0037] 1. The SQL Parser (or a general SQL AST) parses the SQL and outputs the AST → Logical Query Plan.

[0038] 2. Collect relevant metadata: table structure, indexes, statistics, constraints, etc. 3. Generate a "structured Prompt carrier" (described in detail below as a JSON schema), and submit the Logical Plan as input to the AI ​​SQL rewriting module, for example: { "query_id":"q123", "sql":"SELECT ...", "logical_plan": {...}, "schema": [{"table":"A","cols":[{"name":"id","type":"int", "ndv":100000}, ...]}], "stats_snapshot_ts":"2025-11-01T12:00:00Z", "hints":["USE_INDEX(A_idx)"] } AI SQL Rewrite Module The goal of this phase is to generate semantically equivalent and more efficient candidate logical plans / SQL. Key functionalities include predicate pushdown, subquery rewriting, CTE expansion / merging, projection pruning, equivalence propagation, complex predicate decomposition, and materialized view matching rewriting. The main focus is on the following points: Prompt Design: Use structured prompts (JSON + command statements). The main body of the prompt contains a logical plan, schema, stats, and rewrite goals (such as minimizing I / O, prioritizing latency, and resource constraints).

[0039] Output format: LLM returns a candidate list, each item containing the candidate SQL / Logical Plan, rewrite type, confidence score, and change summary.

[0040] Verifiability: Each candidate must also return an "equivalence assertion" suggestion (e.g., logical equivalence checks or comparisons based on small table samples can be used to verify consistency of results).

[0041] pseudocode: function ai_rewrite(logical_plan, schema, stats, hints): prompt = build_structured_prompt(logical_plan, schema, stats,hints, task="rewrite") responses = call_llm(prompt, top_k=K) candidates = parse_responses_to_plans(responses) For candidate c: if quick_sanity_check(c): include return candidates Example Prompt: TASK: Rewrite the logical plan to reduce expected IO and improve joinlocality. INPUT: {logical_plan:..., schema:..., stats:...} CONSTRAINTS: Preserve result order for ORDER BY X; do not use materialized view unless index exists. RETURN: JSON list [{rewritten_sql:"...", plan:"...", rewrite_type:"subquery_to_join", score:0.95, notes:"..."}] AI cardinality estimation module The goal of this phase is to provide a more accurate cardinality estimate for each operator node of the logical program, especially under column relevance and complex predicates.

[0042] Input data: node description (type: Filter, Join, Project), left and right table statistics, samples (if necessary), historical execution samples (the number of actual intermediate rows sampled).

[0043] Model Parallelism: Two parallel strategies are used. A Prompted LLM serializes the node and stats data, allowing the LLM to output selectivity (0-1). A specialized small model, such as a tabular model (GBDT / NN), is used to fit the selectivity, and the model is periodically fine-tuned using execution history. The output format is as follows: {"node_id":"n1","estimated_rows":12345,"confidence":0.87,"explanation":"colA and colB correlated due to ..."} Handling correlations: When multiple correlations are detected (such as date and region), sample association (joint histogram or sketches) is used as an auxiliary input. If there are no samples, the LLM provides a prior estimate based on schema / hints / business metadata and marks it as low confidence.

[0044] Online calibration: Implement an online feedback loop—collect actual intermediate results after actual execution, calculate the estimation bias and update the model / adjust the LLM prompt (prompt for error correction).

[0045] AI cost estimation module The goal of this phase is to estimate the execution cost (peak I / O, CPU, memory usage, and estimated execution time) given the physical operators (HashJoin, MergeJoin, Sort, SeqScan) and hardware / runtime configuration. The following points will be the focus: Cost componentization: The cost is broken down into IO_cost, CPU_cost, mem_cost, and network_cost (in distributed scenarios). LLM outputs the values ​​and confidence levels of each component.

[0046] Hardware parameters: Inputs include machine IOPS, network bandwidth, average bytes per line, concurrency settings, etc.

[0047] Comparative prediction: LLM can provide a relative ranking of multiple operators (HashJoin may be better than MergeJoin) to avoid absolute error sensitivity.

[0048] Adjustment logic: Integrate the LLM output with a traditional cost model (such as a mathematical model based on the number of rows and operational complexity) using Bayesian fusion or weighted averaging, and the weights can be tuned online via A / B testing.

[0049] Output example: {"plan_id":"p1", "cost":1023.4, "components": {"io":800.0, "cpu":150.0, "mem":73.4}} AI project enumeration and pruning module The goal of this phase is to generate high-quality candidate execution plans within a large search space and prune low-quality / unreasonable plans, thereby reducing enumeration costs. The following points are the key focus: Using a hybrid strategy: LLM directly provides a set of join orders (e.g., top-K) based on the logical plan and cardinality estimation; and uses a traditional enumeration method to locally select physical operators (join operators, index usage, etc.) for each join order given by LLM.

[0050] Pruning rules: Prune according to LLM confidence level or estimated cost threshold; prune schemes that do not meet resource constraints (memory limits); remove a candidate if it contains an unallowed operation (e.g., using a non-existent index).

[0051] Hybrid search is used: first, LLM is used to generate a coarse-grained space, and then traditional dynamic programming / heuristic search is run on each candidate to form the final candidate set.

[0052] Parallelization: candidate-level parallel scoring utilizes a multi-threaded / multi-core concurrent computation cost model.

[0053] Plan selection, execution and online learning 1) Dual-path scoring: The final candidate is calculated using combined_score = alpha * llm_cost_score + (1-alpha) * traditional_cost_score, where alpha can be adjusted online; 2) Execution: Select the plan to be executed by the Executor and monitor the actual execution statistics (actual time, IO, memory usage, number of rows). Write the real-time data back to the statistics storage; 3) Online learning: Periodic (or trigger-based) use of execution results: 4) Update the tabular cardinality model; 5) Adjust the LLM prompt template or fine-tune the small model (if permitted); 6) Update the fusion weight alpha.

[0054] The above description is merely a preferred embodiment of the present invention and is used only to illustrate the technical solution of the present invention, and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention are included within the scope of protection of the present invention.

Claims

1. A database query optimization method based on large model enhancement, characterized in that, Includes the following steps: 1) Receive SQL queries and generate an initial logical query plan; 2) The large model is invoked to perform semantic-level rewriting of the logical query plan, generating several candidate logical plans; 3) Cardinality estimation of each operator in the candidate logical program is performed using a large model; 4) Based on the baseline estimation results, call the large model to predict the cost of the physical operators; 5) Based on cost prediction, candidate execution plans are generated or tailored from the large model; 6) Select the optimal execution plan based on the final cost and submit it to the executor for execution.

2. The method according to claim 1, characterized in that, Semantic rewriting includes: predicate pushdown, subquery rewriting, projection pruning, join rearrangement, and CTE expansion or collapse.

3. The method according to claim 1, characterized in that, Large models infer inter-table correlations based on column statistics, distribution information, and semantics when estimating cardinality.

4. The method according to claim 1, characterized in that, Cost prediction includes predictions of I / O costs, CPU costs, memory usage costs, and operator-specific costs.

5. The method according to claim 1, characterized in that, The large model generates candidate execution plans, including generating several join sequences.

6. The method according to claim 5, characterized in that, The join order generation uses semantic inference rules to reduce the invalid search space.

7. The method according to claim 1, characterized in that, The execution plan adopts a combined scoring strategy of traditional cost model and large model cost model.

8. The method according to claim 1, characterized in that, It also includes sampling the execution results of historical execution plans and using them to update the parameters of the large model.

9. A database query optimization system based on large model enhancement, comprising: The SQL rewriting module receives SQL queries and generates an initial logical query plan. The large model is invoked to perform semantic-level rewriting of the logical query plan, generating several candidate logical plans; The cardinality estimation module performs cardinality estimation on each operator in the candidate logical scheme using a large model. The cost estimation module, based on the baseline estimation results, calls the large model to predict the cost of the physical operators; The plan enumeration module generates or prunes candidate execution plans from a large model based on cost prediction. The execution plan selection module selects the optimal execution plan based on the final cost and submits it to the executor for execution.

Images