Data query statement optimization method and device, electronic equipment and storage medium

Abstract

The invention provides a data query statement optimization method and device, electronic equipment and a storage medium, and the method comprises the steps: when a statement optimization request for a target structured query statement is received, compiling the target structured query statement, and generating an execution plan tree suitable for a distributed database environment; in the process of traversing the execution plan tree based on the hierarchical structure, for each accessed plan node, based on at least one preset optimization rule corresponding to the plan node, performing statement optimization analysis on the execution plan of the plan node to determine at least one statement optimization suggestion; all optimization suggestions determined in the process of executing the plan tree are aggregated and traversed, an optimization point list is generated and output, and according to the scheme, automatic and refined performance optimization of distributed database query statements is achieved, and the optimization efficiency, the result precision and the adaptive capacity to a distributed architecture are improved.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a method, apparatus, electronic device, and storage medium for optimizing data query statements. Background Technology

[0002] Today, with the continuous expansion of enterprise data scale and the increase in business complexity, distributed databases are gradually becoming the data storage cornerstone of core business systems due to their good scalability, high availability and ability to process massive amounts of data. However, the complexity of data distribution, network communication and multi-replica consistency in distributed environments has brought unprecedented challenges to the performance optimization of Structured Query Language (SQL).

[0003] Currently, SQL performance optimization typically relies on the human experience of database administrators or developers, which includes manually identifying high-load SQL queries and proposing and implementing optimization suggestions based on personal experience. In addition, there are automated tuning tools for single-machine environments.

[0004] However, the aforementioned manual optimization methods heavily rely on personal experience and technical skills, resulting in high costs, long cycles, and difficulty in scaling. Existing automated tools, on the other hand, are designed based on single-machine environments for their optimization rules and analysis models, failing to fully consider core distributed factors such as data distribution and data movement overhead between nodes. Consequently, the optimization suggestions they generate are often partial or ineffective. Therefore, existing structured statement optimization schemes suffer from low optimization efficiency, poor accuracy, and difficulty in adapting to distributed architectures. Summary of the Invention

[0005] This application provides a data query statement optimization method, apparatus, electronic device, and storage medium to achieve automated and refined performance optimization of distributed database query statements, thereby improving optimization efficiency, result accuracy, and adaptability to distributed architectures.

[0006] In a first aspect, embodiments of this application provide a data query statement optimization method applied to a distributed database system, the method comprising:

[0007] When a statement optimization request for a target structured query statement is received, the target structured query statement is compiled to generate an execution plan tree suitable for a distributed database environment. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. The leaf nodes correspond to operations that read data from a data table, and the root nodes correspond to operations that output the final query results. The hierarchical structure is established based on the logical dependencies of the target structured query statement.

[0008] During the process of traversing the execution plan tree based on the hierarchical structure, for each plan node visited, the execution plan of the plan node is analyzed for statement optimization based on at least one preset optimization rule corresponding to the plan node, so as to determine at least one statement optimization suggestion;

[0009] Aggregate all optimization suggestions determined during the execution plan tree traversal, generate a list of optimization points, and output it.

[0010] Secondly, embodiments of this application also provide a data query statement optimization device, applied to a distributed database system, the device comprising:

[0011] The execution plan tree determination module 310 is used to compile the target structured query statement and generate an execution plan tree suitable for a distributed database environment when a statement optimization request for the target structured query statement is received. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. The leaf nodes correspond to operations that read data from a data table, and the root nodes correspond to operations that output the final query results. The hierarchical structure is established based on the logical dependencies of the target structured query statement.

[0012] The optimization suggestion determination module 320 is used to perform statement optimization analysis on the execution plan of each accessed plan node during the process of traversing the execution plan tree based on the hierarchical structure, based on at least one preset optimization rule corresponding to the plan node, so as to determine at least one statement optimization suggestion.

[0013] The optimization point list output module 330 is used to aggregate all optimization suggestions determined during the traversal of the execution plan tree, generate an optimization point list, and output it.

[0014] Thirdly, embodiments of this application also provide an electronic device, which includes:

[0015] One or more processors;

[0016] Storage device for storing one or more programs.

[0017] When one or more programs are executed by one or more processors, the one or more processors implement a data query statement optimization method as described in any of the embodiments of this application.

[0018] Fourthly, embodiments of this application also provide a storage medium containing computer-executable instructions, which, when executed by a computer processor, are used to perform a data query statement optimization method as described in any of the embodiments of this application.

[0019] This application provides a data query statement optimization method, which includes: when a statement optimization request for a target structured query statement is received, compiling the target structured query statement to generate an execution plan tree suitable for a distributed database environment; wherein the execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes, leaf nodes correspond to operations that read data from a data table, and root nodes correspond to operations that output the final query result. The hierarchical structure is established based on the logical dependencies of the target structured query statement. Thus, during the traversal of the execution plan tree based on the hierarchical structure, for each accessed plan node, statement optimization analysis is performed on the execution plan of the plan node based on at least one preset optimization rule corresponding to the plan node to determine at least one statement optimization suggestion. Finally, all optimization suggestions determined during the traversal of the execution plan tree are aggregated to generate an optimization point list and output it. The technical solution of this application generates a distributed-aware execution plan tree through compilation and performs traversal analysis on its nodes based on pre-set distributed optimization rules. This achieves automated and precise performance optimization of structured query statements in a distributed environment, thereby effectively improving the efficiency of the optimization process and the quality of the results, and significantly enhancing the adaptability of the optimization solution to complex distributed architectures. Attached Figure Description

[0020] To more clearly illustrate the technical solutions of the exemplary embodiments of this application, the accompanying drawings used in describing the embodiments are briefly introduced below. Obviously, the accompanying drawings described are only a portion of the embodiments to be described in this application, and not all of them. For those skilled in the art, other drawings can be obtained from these drawings without any creative effort.

[0021] Figure 1 A flowchart illustrating a data query statement optimization method provided in an embodiment of this application;

[0022] Figure 2 A flowchart illustrating another data query statement optimization method provided in this application embodiment;

[0023] Figure 3 This is a schematic diagram of a data query statement optimization device provided in an embodiment of this application;

[0024] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0025] The present application will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the application and not intended to limit it. Furthermore, it should be noted that, for ease of description, the accompanying drawings show only the parts relevant to the present application, not the entire structure.

[0026] Example 1

[0027] Before introducing the technical solutions provided in this application, the application scenarios of the solutions can be described first. This embodiment is applicable to various scenarios that require automated and refined performance optimization of data query statements in distributed database systems. Currently, optimization methods relying on human experience or automated tuning tools for single-machine environments are widely used to improve query performance, but traditional methods have obvious limitations. In practical applications, due to the characteristics of distributed database systems such as complex data distribution, high communication overhead between nodes, and numerous constraints in execution plan generation, and the need to consider multiple conditions during the optimization process, traditional optimization methods are difficult to achieve accurate optimization at the global level, easily leading to problems such as invalid index suggestions, suboptimal execution plans, and low resource utilization. Therefore, there is an urgent need for a database query statement optimization method that can improve the accuracy and effectiveness of optimization suggestions and its adaptability to distributed environments.

[0028] Figure 1 This is a flowchart illustrating a data query statement optimization method provided in an embodiment of this application. This embodiment is applicable to various situations where automated and refined performance optimization of data query statements is required in a distributed database system. The method can be executed by a data query statement optimization device, which can be implemented in the form of software and / or hardware. The hardware can be a controller, such as a mobile terminal, a PC, or a server.

[0029] The data query statement optimization method provided in this invention is applied to a distributed database system. It can be understood that the design of this data query statement optimization method is based on the architecture and operating characteristics of distributed databases. Its processing object is the query statement running in a distributed database environment composed of multiple interconnected nodes. Moreover, each step of the method fully considers and utilizes the core factors unique to the distributed environment, such as data sharding, inter-node communication, parallel computing, and consistency constraints, thereby ensuring that the final optimization suggestions can effectively address and solve the performance bottleneck problem in the distributed scenario.

[0030] like Figure 1 As shown, the data query statement optimization method provided in this embodiment of the invention includes the following steps:

[0031] S110. When a statement optimization request for the target structured query statement is received, the target structured query statement is compiled to generate an execution plan tree suitable for the distributed database environment.

[0032] The target structured query statement refers to the original query instruction submitted in a distributed database system that requires performance optimization analysis. A statement optimization request is a processing application initiated for this target structured query statement, requesting improvements to its execution efficiency or resource consumption.

[0033] The execution plan tree is an internal execution blueprint generated by the database system specifically for a distributed environment after compiling the target structured query statement. It is presented in a hierarchical tree structure. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. Leaf nodes correspond to operations that read data from data shards on distributed data nodes, while the root node corresponds to operations that output the final query result after distributed computing. The hierarchical structure is established based on the dependencies of the target structured query statement in the distributed database environment. Plan nodes are the basic building blocks of the execution plan tree, and each plan node represents a specific operation step in the query execution process. The execution plan tree can be understood as consisting of multiple plan nodes representing specific execution steps. These nodes are organized into a hierarchical structure with a superior-subordinate relationship, from the bottom leaf nodes to the top root node. The bottom leaf nodes specifically refer to the operations of reading raw data directly from the local data shards stored on each distributed data node, which constitutes the data input starting point of the distributed query. The top root node specifically refers to the operations of aggregating and integrating the intermediate calculation results from multiple nodes and outputting the final query result, which represents the calculation endpoint and output of the distributed query. The specific construction logic of the entire hierarchical structure is not arbitrary, but strictly based on the data flow, calculation order, and node collaboration dependencies inherent in the target structured query statement in the distributed database environment, to ensure that the execution plan tree can correctly guide the collaborative execution of the query in the distributed cluster.

[0034] In practical applications, when a statement optimization request is received that aims to improve the execution efficiency of a target structured query statement in a distributed environment, the target structured query statement can be used as input to perform distributed computing-oriented parsing, semantic analysis, and internal transformation. Ultimately, an execution plan tree suitable for a distributed database environment is generated, which represents the specific collaborative execution steps, data flow across nodes, and division of labor among computing nodes in the distributed database environment.

[0035] For example, the data query statement optimization method provided in this embodiment of the invention can be integrated into the server of a distributed database system. When the server receives a target structured query statement for calculating sales rankings in various regions, along with a statement optimization request for performance optimization, the server's distributed query compiler can compile this target structured query statement. This process includes not only conventional syntax and semantic analysis, but also special consideration of the distribution of data across multiple geographical shards, network transmission costs between nodes, and the scheduling strategy of the parallel computing framework. Finally, an execution plan tree suitable for a distributed database environment is generated. This tree details how to scan order shards in parallel on various data nodes, shuffle and aggregate data between computing nodes, and finally summarize and generate a complete distributed execution path for global ranking results.

[0036] S120. During the process of traversing the execution plan tree based on the hierarchical structure, for each plan node visited, based on at least one preset optimization rule corresponding to the plan node, the execution plan of the plan node is analyzed for statement optimization to determine at least one statement optimization suggestion.

[0037] Among them, at least one preset optimization rule refers to a series of standard strategies or logical conditions that are predefined and stored to guide and automatically execute query performance analysis and improvement judgments. These rules cover multiple optimization dimensions, from syntax rewriting and structural transformation to resource utilization and risk identification. At least one statement optimization suggestion is a specific improvement plan or warning message generated and output after analyzing a specific plan node by applying the above at least one preset optimization rule during the traversal of the execution plan tree. Each suggestion aims to improve the execution efficiency of the target structured query statement, reduce resource consumption, or avoid potential risks.

[0038] In this embodiment, the execution plan tree is first traversed according to its inherent hierarchical structure from leaf nodes to root nodes. When any plan node is visited, a set of pre-configured optimization rules that match the type or operation of the plan node are activated and applied. These rules conduct an in-depth performance and rationality review of the local execution plan represented by the plan node. Through this review and analysis, potential optimization opportunities or problems are identified, and finally, one or more specific statement optimization suggestions are generated for the node, which aim to improve its execution efficiency or logic.

[0039] In this embodiment, the preset optimization rules may optionally include at least one of the following:

[0040] The first rule is to parse the data manipulation statements that are components of the target structured query statement, identify the nested subqueries within the data manipulation statements, and trigger an independent optimization analysis process for the subqueries to generate in-depth optimization suggestions for the subqueries.

[0041] Data manipulation statements are the core components of the target structured query statement, directly expressing the specific intent to retrieve, insert, update, or delete data from the database. Subqueries are complete query units nested within data manipulation statements, existing as a condition or data source for the outer query. Deep optimization suggestions are the output of an independent and in-depth optimization analysis process initiated on the identified subqueries. They go beyond simple adjustments to surface-level statements, aiming to uncover deeper performance improvement potential through fine-tuning the internal structure of subqueries or fundamentally changing execution strategies.

[0042] Specifically, the first rule's processing procedure is as follows: First, the syntax and structure of each data operation statement in the target structured query statement are parsed to identify and locate subquery statements nested within the data operation statements. Once such subquery statements are identified, the optimization process does not stop at the level of optimizing the outer statement, but instead initiates an independent optimization analysis process focused on the subquery statement itself. This independent process will conduct in-depth review and rewriting analysis of the subquery statement, just like processing a complete target structured query statement, and finally generate a set of more refined and fundamental deep optimization suggestions specifically for the internal logic and execution efficiency of the subquery statement.

[0043] For example, by parsing the SELECT subquery in the INSERT / UPDATE statement and triggering its independent optimization process using the "optimize" keyword, optimization suggestions for embedded queries are mined. Finally, the suggestions are returned along with the execution results of the data manipulation statements, solving the problem that traditional optimization schemes cannot provide internal query optimization suggestions for DML operations.

[0044] The second rule is to analyze at least one of the following factors: the syntactic features of the target structured query statement, the type of data source involved, and its configuration mode, and to identify and generate distributed execution path suggestions suitable for large-scale parallel computation of the target structured query statement.

[0045] Syntactic features refer to the specific patterns and structural characteristics exhibited by the target structured query statement in terms of its writing style, keyword usage, and clause combination. Data source type refers to the category of the specific data storage medium for the query or operation, such as whether it is a relational database table or a non-relational dataset. Configuration mode refers to the deployment and organization of these data sources in a distributed environment, such as whether it is sharded storage or a replica set.

[0046] Among them, the distributed execution path suggestion refers to specific proposals on how to reasonably decompose the computation task of the target structured query statement and distribute it to multiple computing nodes for parallel execution, so as to make full use of cluster resources to improve processing speed.

[0047] In this embodiment, the processing of the second rule is as follows: by analyzing the syntactic features of the target structured query statement, analyzing the data source type of its query or operation, and examining at least one factor in the specific configuration mode of these data sources in the distributed system, it is determined whether the statement has and can adapt to the environment of large-scale parallel computing; thereby, potential opportunities that can be accelerated through a distributed computing framework are identified, and a specific and guiding distributed execution path suggestion is generated accordingly. This suggestion will detail how to decompose, allocate, and schedule the computational logic of the original statement to multiple computing nodes for collaborative execution, thereby achieving efficient parallel processing of massive amounts of data.

[0048] For example, a rule-based distributed parallel execution (MPP) path determination mechanism can be established, which can comprehensively consider various factors such as syntax compatibility, view type, heterogeneous data source (such as DBLINK) and specific configuration mode, automatically identify and suggest SQL statements suitable for large-scale parallel computing, and give full play to the computing performance advantages of distributed architecture.

[0049] The third rule: Based on the analysis of the conditional clauses in the target structured query statement, identify operations that may trigger a full table lock and generate risk warning suggestions.

[0050] In a structured query, the conditional clause is a component that specifies the data filtering logic. It defines the constraints that must be met for a data row to be selected or excluded. Operations that pose a full table lock risk refer to specific types of conditional clauses or join operations that may require an exclusive lock on the entire table during execution. This locking prevents other concurrent transactions from reading and writing to the table, leading to serious performance bottlenecks and availability issues.

[0051] Among them, the risk warning suggestion refers to a warning output generated after identifying that there are operations in the target structured query statement that may cause a full table lock risk. This suggestion aims to remind developers or administrators to pay attention to this potential risk and guide them to consider modifying the statement logic or adjusting the transaction isolation level to avoid table locking issues.

[0052] In this embodiment, the processing of the third rule is as follows: the conditional clauses in the target structured query statement can be analyzed to determine the operators used, the column attributes involved, and the way they interact with the data table. From this, specific operation patterns that are highly likely to cause locking of the entire data table under a specific transaction isolation level or execution engine in the database can be identified. Once such high-risk operations are detected, a risk warning suggestion can be generated. This suggestion will not only indicate the existence of the risk but also associate it with the specific conditional clause position to warn the user that this statement may cause a full table lock when executed concurrently. Then, it will suggest taking corresponding statement rewriting or configuration adjustment measures to eliminate or reduce the risk.

[0053] For example, by analyzing the WHERE conditions of SQL statements (such as tautological conditions or missing conditions), high-risk operations that may trigger full table locks can be identified in advance before the transaction is executed, and warnings can be issued to users, effectively improving the system's concurrency performance and operational stability.

[0054] Fourth rule: By pushing cost analysis requests for the target structured query statement down to the data nodes, we can obtain the real data distribution information in the distributed environment, and generate index optimization suggestions based on the real data distribution information.

[0055] The cost analysis request is an information collection instruction issued by the optimizer to the underlying data nodes in order to accurately evaluate the execution cost of the target structured query in a distributed environment. The actual data distribution information is detailed information returned by the data nodes after responding to the cost analysis request, showing the specific statistics and distribution characteristics of the data across actual storage shards or nodes. It reflects the actual degree of data skew and storage location.

[0056] Among them, the index optimization suggestions are targeted proposals on creating, modifying or deleting specific index structures based on the obtained real data distribution information and the access patterns of the target structured query statements. The aim is to improve query performance by optimizing the data retrieval path.

[0057] In this embodiment, the processing of the fourth rule is as follows: the cost analysis request for the target structured query statement can be pushed down to each data node storing the data, driving them to analyze the actual storage status of the data involved in the statement locally; by aggregating the feedback from all relevant data nodes, the real data distribution information of the statement in the distributed environment can be obtained, that is, the specific statistical characteristics of the data on each node, such as the quantity, range, and hotspots; subsequently, the matching relationship between this real data distribution information and the statement access pattern can be analyzed in depth, thereby determining whether the existing index is effective or missing, and generating specific index optimization suggestions accordingly. These suggestions aim to guide how to adjust the index strategy to better adapt to the real data distribution, thereby reducing cross-node data access and improving query efficiency.

[0058] For example, for pass-through queries, by automatically rewriting and pushing the EXPLAIN statement down to the data nodes, the system collects real data distribution information in a distributed environment, thereby providing accurate index creation suggestions.

[0059] Fifth rule: Based on the semantic layer rewriting rules, perform logical optimization on the query structure of the target structured query statement to generate semantic optimization suggestions.

[0060] Semantic rewriting rules refer to a series of guidelines for transforming the structure and expression of query statements under the premise of logical equivalence. Semantic optimization suggestions are specific improvement proposals generated after analyzing and rewriting the target structured query statement using these semantic rewriting rules.

[0061] In this embodiment, the processing of the fifth rule is as follows: based on the preset semantic layer rewriting rules, the inherent query structure of the target structured query statement is analyzed. Without changing the semantics of the final returned result, various equivalent transformations and reconstructions of its logical components and expressions are explored and attempted. This process focuses on logical-level optimization, aiming to eliminate redundant calculations, simplify complex conditions, reorganize the connection order, or change the form of nested queries. By performing such logical optimization on the query structure, corresponding semantic optimization suggestions are generated. These suggestions specifically indicate how to guide the database engine to generate a more efficient and concise execution plan by rewriting the logical structure of the statement.

[0062] Based on the above embodiments, optionally, the query structure of the target structured query statement can be logically optimized based on semantic layer rewriting rules, including at least one of the following operations:

[0063] The first method involves extracting the association conditions of existing subqueries in the target structured query statement to the outer query to generate query rewriting suggestions.

[0064] Specifically, for a particular type of nested query in the target structured query statement, namely existential subqueries, we can analyze the association conditions between the inner subquery and the outer main query, identify those association conditions that can be safely moved out of the subquery and promoted to the outer query semantically, and flatten the query structure by performing this logical adjustment. Finally, the query rewriting suggestions generated based on this analysis results will clearly indicate how to rewrite the original statement by extracting association conditions, thereby helping the database optimizer generate a more understandable and optimized execution plan.

[0065] The second approach is to rewrite the row number-based classification and statistical operations in the target structured query statement into row number calculation operations based on window functions, in order to generate logical structure optimization suggestions.

[0066] Specifically, this approach targets a particular operational pattern in the target structured query statement: classification and statistics achieved through methods such as correlated subqueries or self-joins, relying on the relative position (row number) of data rows in the result set. The aim is to identify this pattern and use semantic layer rewriting rules to logically and equivalently convert it into a representation that uses window functions for row number calculation and classification statistics. This logical structure transformation eliminates inefficient recursion or redundant calculations that may exist in the original implementation. The resulting logical structure optimization suggestions will provide specific guidance on how to rewrite the original statement using window functions, thereby achieving clearer and more efficient query logic.

[0067] For example, semantic intelligent rewriting rules may include: extracting the association conditions of the EXISTS subquery to the outer layer, and converting the ROWNUM grouping statistics into a more efficient ROWNUM() OVER window function, thereby optimizing the query structure from a logical perspective.

[0068] S130. Aggregate all optimization suggestions determined during the traversal of the execution plan tree, generate a list of optimization points, and output it.

[0069] The optimization point list is a collection of all collected statement optimization suggestions, summarized, integrated, and organized after traversing the execution plan tree and applying all relevant preset optimization rules to complete the analysis.

[0070] Specifically, after traversing the entire execution plan tree and determining the corresponding statement optimization suggestions for each plan node, all optimization suggestions that may be duplicated or related and scattered across various plan nodes can be collected, organized, and merged to generate an optimization point list. After generating the optimization point list, it can be used as the final output of this statement optimization request, providing users with a comprehensive and actionable report on query performance improvement solutions.

[0071] Based on the above embodiments, optionally, the specific implementation steps for generating the list of optimization points may include:

[0072] (1) Collect at least one statement optimization suggestion determined by each plan node during the traversal of the execution plan tree.

[0073] In this embodiment, during the traversal of the execution plan tree, whenever a plan node is visited and a preset optimization rule is applied to analyze it, at least one statement optimization suggestion generated by that node is obtained in real time.

[0074] (2) Send all collected statement optimization suggestions to the central aggregator and deduplicate the statement optimization suggestions through the central aggregator.

[0075] In this embodiment, after completing the traversal and suggestion collection of all planned nodes, the original statement optimization suggestions from different nodes can be uniformly transmitted to a central aggregator that is specifically responsible for summarizing and processing. After receiving all suggestions, the central aggregator will compare and analyze their contents, identify and merge duplicate suggestions that describe the same problem or propose the same improvement measures, eliminate redundant information, and ensure that each suggestion in the final list is an independent and unique optimization point.

[0076] (3) Based on the preset priority rules, sort the optimization suggestions of each deduplicated statement to form an ordered list of optimization points.

[0077] Specifically, after the central aggregator completes the deduplication of statement optimization suggestions, it can sort, compare, and classify all these independent and unique optimization suggestions according to a series of predefined priority rules used to evaluate the importance and urgency of implementation. By applying these priority rules, the deduplicated suggestions are organized into an ordered sequence from high priority to low priority, or arranged in other logical orders, thereby forming a clear, focused list of optimization points that is easy for users to process and implement in sequence.

[0078] This application provides a data query statement optimization method, which includes: when a statement optimization request for a target structured query statement is received, compiling the target structured query statement to generate an execution plan tree suitable for a distributed database environment; wherein the execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes, leaf nodes correspond to operations that read data from a data table, and root nodes correspond to operations that output the final query result. The hierarchical structure is established based on the logical dependencies of the target structured query statement. Thus, during the traversal of the execution plan tree based on the hierarchical structure, for each accessed plan node, statement optimization analysis is performed on the execution plan of the plan node based on at least one preset optimization rule corresponding to the plan node to determine at least one statement optimization suggestion. Finally, all optimization suggestions determined during the traversal of the execution plan tree are aggregated to generate an optimization point list and output it. The technical solution of this application generates a distributed-aware execution plan tree through compilation and performs traversal analysis on its nodes based on pre-set distributed optimization rules. This achieves automated and precise performance optimization of structured query statements in a distributed environment, thereby effectively improving the efficiency of the optimization process and the quality of the results, and significantly enhancing the adaptability of the optimization solution to complex distributed architectures.

[0079] Example 2

[0080] Figure 2 This is a schematic diagram of a data query statement optimization method provided in an embodiment of this application. Based on the foregoing embodiments, this embodiment provides a more detailed description of steps S110 and S120. For specific implementation details, please refer to the technical solution of this embodiment. Technical terms that are the same as or corresponding to those in the above embodiments will not be repeated here.

[0081] like Figure 2 As shown, the method specifically includes the following steps:

[0082] S210, Receive a statement optimization request containing an optimization preamble instruction.

[0083] The optimization pre-instruction is a special control command or identifier that is pre-placed or appended to the original query input to indicate that subsequent content needs to be subjected to performance optimization analysis rather than direct execution. The statement optimization request is a complete request package containing the optimization pre-instruction and the target structured query statement to be processed. It serves as the trigger signal and input carrier for the entire optimization process, and its core purpose is to request the system to initiate a complete optimization analysis process on the target structured query statement it carries.

[0084] Specifically, the data query statement optimization method provided in this embodiment of the invention can be integrated into the server of a distributed database system. Based on this, the server can listen to and capture complete request packets from external inputs. When a specific request containing an optimization preamble is detected, it is identified as a statement optimization request.

[0085] S220. Parse the statement optimization request, identify and extract the target structured query statement.

[0086] Specifically, the structure and content of statement optimization requests containing optimization pre-directions can be broken down and analyzed. The parsing process first identifies the optimization pre-direction part that identifies the optimization intention, and then determines the main body of the remaining content. Then, the core content that needs to be optimized, namely the target structured query statement, can be accurately separated and extracted from the request packet, thus preparing a clear input object for subsequent compilation and optimization analysis.

[0087] S230. Perform syntax parsing and logical compilation on the target structured query statement to generate a preliminary execution plan that reflects the query logic.

[0088] The preliminary execution plan refers to the original execution scheme that has not been deeply optimized, which represents the basic execution steps and data flow of the target structured query statement. It reflects the original logical structure of the query.

[0089] In this embodiment, the extracted target structured query statement can first be parsed to check its structural correctness, and then a deep logical compilation can be performed to understand its semantic connotation and data operation intention. Based on this, a preliminary execution plan that reflects the original execution steps, data dependencies and calculation order of the query statement can be generated. This plan constitutes the direct operation object and logical basis for all subsequent optimization analysis activities.

[0090] S240. Inject optimization rule checkers into each plan node of the initial execution plan to form an execution plan tree.

[0091] The optimization rule checker is a functional module that is dynamically injected into each plan node of the execution plan tree during the optimization process. It encapsulates specific optimization analysis logic and has at least one preset optimization rule pre-configured. The optimization rule checker can be triggered and invoked through an interface. Its core responsibility is to perform performance and rationality reviews on the plan nodes associated with each node during traversal of the execution plan tree, based on the preset rules and the obtained execution context information, to determine whether there are optimization opportunities and generate corresponding statement optimization suggestions.

[0092] Specifically, based on the preliminary execution plan, each plan node representing a specific operation step is structurally modified according to preset interface definition rules. One or more optimization rule checkers encapsulating specific analysis logic are dynamically attached or associated with it. After this annotation operation, the preliminary execution plan evolves into an enhanced structure that not only contains the original execution logic but also embeds distributed optimization analysis capabilities, namely the execution plan tree, so that subsequent traversal can trigger independent optimization review of each node.

[0093] In this embodiment, optionally, injecting an optimization rule checker into each plan node of the initial execution plan to form an execution plan tree may include the following steps:

[0094] (1) Based on the preset interface definition rules, add an interface call entry point to the data structure of each node in the preliminary execution plan.

[0095] The interface definition rules specify how to create a standardized access point in the data structure of a planning node for accessing external functional modules; the interface call entry point is the standardized access point specifically created in the actual data structure of the planning node according to the interface definition rules.

[0096] Specifically, based on the pre-configured interface definition rules, the internal data structure corresponding to each plan node in the preliminary execution plan can be modified and expanded. A standard access point conforming to the specification, i.e. the interface call entry point, can be added to the data structure of each node. This prepares the structure for the subsequent association and binding of the external optimization rule checker logic to the node, enabling it to be accessed and called by external functional modules through this standard entry point.

[0097] (2) Associate the interface call entry with one or more preset optimization rule checkers so that when accessing any plan node during the traversal of the execution plan tree, the associated optimization rule checker can be triggered through the interface call entry to perform statement optimization analysis on the plan node.

[0098] Specifically, after creating an interface call entry point for each plan node, one or more predefined optimization rule checkers can be logically bound to that interface call entry point based on the plan node's type or operational characteristics, establishing a relationship between them. Once this relationship is established, when traversing the execution plan tree and accessing any plan node, calling that node's interface call entry point will automatically trigger and execute all associated optimization rule checkers, thereby performing a detailed statement optimization analysis of the current plan node's execution plan.

[0099] S250, Invoke the optimization rule checker registered on the plan node type.

[0100] In this embodiment, when traversing the execution plan tree and accessing a specific type of plan node, one or more optimization rule checkers that are pre-registered on that type of plan node can be actively activated and executed through the interface call entry of the node based on the previously established relationship, thereby initiating a targeted optimization review process for that node.

[0101] S260. Obtain the execution context information corresponding to the plan node by optimizing the rule checker.

[0102] Execution context information is a collection of various data describing the execution environment and resource status of the current plan node, which the optimization rule checker relies on during analysis. Execution context information includes at least one of the following: metadata of the data tables operated on by the plan node, statistical information of the data tables, and a cost model used to evaluate the estimated cost of different query execution paths to select the optimal path. Specifically, the metadata of the data tables operated on by the plan node describes the structure definition of the data table itself, such as which columns it contains and their data types; the statistical information of the data tables is a quantitative description of the actual state of the data in the table, such as the total number of rows, the number of unique values, and the data distribution histogram; the cost model is a mathematical evaluation system used to estimate the computational resources and time required for different query execution paths, which helps the optimizer compare and select the optimal execution plan.

[0103] Specifically, after the optimization rule checker is activated, it can proactively request and obtain various environmental and status data directly related to the current node based on the specific operations being performed by the currently analyzed plan node. These data are collectively referred to as execution context information.

[0104] S270. Based on the execution context information, determine whether the execution plan of the plan node conforms to the corresponding preset optimization rules.

[0105] In this embodiment, after obtaining detailed execution context information, the optimization rule checker can use it as input to compare and analyze the trigger conditions or judgment logic set by each of the preset optimization rules it encapsulates, thereby evaluating whether the specific execution plan of the current plan node meets the optimization opportunities or problem patterns defined by one or more preset optimization rules in the existing context, and thus draw a conclusion on whether it conforms to the rules.

[0106] S280. When it is detected that the execution plan conforms to at least one preset optimization rule, at least one statement optimization suggestion for the plan node is generated according to the optimization logic corresponding to the at least one preset optimization rule.

[0107] Specifically, when the optimization rule checker confirms through comparison and analysis that the execution plan of the current plan node meets the triggering conditions of one or more preset optimization rules, it can construct and generate one or more statement optimization suggestions with improvement directions and content for the plan node based on the specific optimization processing logic defined within these triggered rules.

[0108] S290. Aggregate all optimization suggestions determined during the traversal of the execution plan tree, generate a list of optimization points, and output it.

[0109] The technical solution of this application embodiment, when compiling the target structured query statement to generate an execution plan tree, optimizes the pre-execution instructions to clearly distinguish optimization requests from ordinary execution requests, ensuring that the optimization analysis process can be initiated in a targeted manner without interfering with normal query execution; by parsing the request and extracting the target statement for compilation, a preliminary execution plan representing the original logic is constructed, laying an accurate foundation for subsequent optimization; by dynamically injecting an optimization rule checker with at least one preset optimization rule pre-configured into each plan node of the preliminary execution plan, the optimization capability is deeply integrated into the structure of the execution plan tree, so that optimization analysis is no longer an independent stage added after the fact, but is transformed into a distributed, modular, pluggable real-time analysis capability that is deeply integrated with the execution plan traversal process and can perform fine-grained review of each node, greatly improving the coverage granularity, flexibility and automation level of optimization analysis.

[0110] The technical solution of this application embodiment, when performing statement optimization analysis on the execution plan of the plan node to determine at least one statement optimization suggestion, ensures accurate matching between the analysis logic and the specific operation type by calling the optimization rule checker registered on the plan node type, thereby improving the targeting and effectiveness of the optimization. By acquiring multi-dimensional execution context information including metadata, statistical information, and cost models, the optimization analysis can be based on real data characteristics and system state, overcoming the limitations of relying solely on syntax analysis, and greatly improving the accuracy and practicality of the optimization suggestions. On this basis, based on the intelligent comparison of context information and preset rules, the execution plan pattern that meets the optimization conditions can be reliably identified, and finally, specific and operable statement optimization suggestions are automatically generated according to the optimization logic of the rules. This tightly integrates the preset rules with the execution context, realizing fully automated and intelligent decision-making from rule triggering to suggestion generation, significantly improving the efficiency and quality of query optimization.

[0111] Example 3

[0112] Figure 3 This is a schematic diagram of a data query statement optimization device provided in an embodiment of this application, applied to a distributed database system. The device includes:

[0113] The execution plan tree determination module 310 is used to compile the target structured query statement and generate an execution plan tree suitable for a distributed database environment when a statement optimization request for the target structured query statement is received. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. The leaf nodes correspond to operations that read data from a data table, and the root nodes correspond to operations that output the final query results. The hierarchical structure is established based on the logical dependencies of the target structured query statement.

[0114] The optimization suggestion determination module 320 is used to perform statement optimization analysis on the execution plan of each accessed plan node during the process of traversing the execution plan tree based on the hierarchical structure, based on at least one preset optimization rule corresponding to the plan node, so as to determine at least one statement optimization suggestion.

[0115] The optimization point list output module 330 is used to aggregate all optimization suggestions determined during the traversal of the execution plan tree, generate an optimization point list, and output it.

[0116] This application provides a data query statement optimization device. When the device receives a statement optimization request for a target structured query statement, it compiles the target structured query statement to generate an execution plan tree suitable for a distributed database environment. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. Leaf nodes correspond to operations that read data from a data table, and root nodes correspond to operations that output the final query result. The hierarchical structure is established based on the logical dependencies of the target structured query statement. Thus, during the traversal of the execution plan tree based on the hierarchical structure, for each accessed plan node, statement optimization analysis is performed on the execution plan of the plan node based on at least one preset optimization rule corresponding to the plan node to determine at least one statement optimization suggestion. Finally, all optimization suggestions determined during the traversal of the execution plan tree are aggregated to generate an optimization point list, which is then output. The technical solution of this application generates a distributed-aware execution plan tree through compilation and performs traversal analysis on its nodes based on pre-set distributed optimization rules. This achieves automated and precise performance optimization of structured query statements in a distributed environment, thereby effectively improving the efficiency of the optimization process and the quality of the results, and significantly enhancing the adaptability of the optimization solution to complex distributed architectures.

[0117] Based on the above-mentioned device, optionally, the execution plan tree determination module 310 is specifically used to receive a statement optimization request containing optimization pre-instructions; parse the statement optimization request, identify and extract the target structured query statement; perform syntax parsing and logical compilation on the target structured query statement to generate a preliminary execution plan reflecting the query logic; inject an optimization rule checker into each of the plan nodes of the preliminary execution plan to form the execution plan tree; wherein, the optimization rule checker is pre-configured with at least one preset optimization rule that can be invoked.

[0118] Based on the above-mentioned device, optionally, the execution plan tree determination module 310 is further used to add an interface call entry to the data structure of each node of the preliminary execution plan based on preset interface definition rules;

[0119] The interface call entry is associated with one or more preset optimization rule checkers, so that when any of the plan nodes is accessed during the traversal of the execution plan tree, the associated optimization rule checker can be triggered through the interface call entry to perform statement optimization analysis on the plan node.

[0120] Based on the above-described apparatus, optionally, the optimization suggestion determination module 320 is specifically used to call the optimization rule checker registered on the plan node type; through the optimization rule checker, obtain the execution context information corresponding to the plan node; wherein, the execution context information includes: metadata of the data table operated by the plan node, statistical information of the data table, and at least one of the following: estimated cost for evaluating different query execution paths to select the optimal path; based on the execution context information, determine whether the execution plan of the plan node conforms to the corresponding preset optimization rules; when it is detected that the execution plan conforms to at least one preset optimization rule, generate at least one statement optimization suggestion for the plan node according to the optimization logic corresponding to the at least one preset optimization rule.

[0121] Based on the above-described apparatus, optionally, the at least one preset optimization rule includes at least one of the following:

[0122] First rule: Based on parsing the data manipulation statements that are components of the target structured query statement, identify the nested subquery statements within the data manipulation statements, and trigger an independent optimization analysis process for the subquery statements to generate in-depth optimization suggestions for the subquery statements;

[0123] The second rule is to analyze at least one of the following factors: the syntactic features of the target structured query statement, the type of data source involved, and its configuration mode, and to identify and generate a distributed execution path suggestion suitable for large-scale parallel computation of the target structured query statement.

[0124] Third rule: Based on the analysis of the conditional clauses in the target structured query statement, identify operations that may trigger a full table lock and generate risk warning suggestions;

[0125] Fourth rule: By pushing cost analysis requests for the target structured query statement down to the data nodes, the actual data distribution information of the target structured query statement is obtained, and index optimization suggestions are generated based on the actual data distribution information.

[0126] Fifth rule: Based on the semantic layer rewriting rules, perform logical optimization on the query structure of the target structured query statement to generate semantic optimization suggestions.

[0127] Based on the above-mentioned device, optionally, the logical optimization of the query structure of the target structured query statement based on semantic layer rewriting rules includes at least one of the following operations:

[0128] Extract the association conditions of the existence subqueries in the target structured query statement to the outer query to generate query rewriting suggestions;

[0129] The row number-based classification and statistics operation in the target structured query statement is rewritten as a window function-based row number calculation operation to generate logical structure optimization suggestions.

[0130] Based on the above-mentioned device, optionally, the optimization point list output module 330 is used to collect at least one statement optimization suggestion determined by each of the plan nodes during the traversal of the execution plan tree; send all the collected statement optimization suggestions to the central aggregator, and perform deduplication processing on the statement optimization suggestions through the central aggregator; and sort the deduplicated statement optimization suggestions based on preset priority rules to form an ordered optimization point list.

[0131] The data query statement optimization device provided in this application embodiment can execute the data query statement optimization method provided in any embodiment of this application, and has the corresponding functional modules and beneficial effects of the method execution.

[0132] It is worth noting that the various units and modules included in the above system are only divided according to functional logic, but are not limited to the above division, as long as the corresponding functions can be achieved; in addition, the specific names of each functional unit are only for easy differentiation and are not used to limit the protection scope of the embodiments of this application.

[0133] Example 4

[0134] Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Figure 4 A block diagram is shown of an exemplary electronic device 40 suitable for implementing embodiments of the present application. Figure 4 The electronic device 40 shown is merely an example and should not impose any limitations on the functionality and scope of use of the embodiments of this application.

[0135] like Figure 4 As shown, the electronic device 40 is represented in the form of a general-purpose computing device. The components of the electronic device 40 may include, but are not limited to: one or more processors or processing units 401, system memory 402, and bus 403 connecting different system components (including system memory 402 and processing unit 401).

[0136] Bus 403 represents one or more of several bus architectures, including memory buses or memory electronics, peripheral buses, graphics acceleration ports, processors, or local buses using any of the various bus architectures. Examples of these architectures include, but are not limited to, the Industry Standard Architecture (ISA) bus, the Micro Channel Architecture (MAC) bus, the Enhanced ISA bus, the Video Electronics Standards Association (VESA) local bus, and the Peripheral Component Interconnect (PCI) bus.

[0137] Electronic device 40 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by electronic device 40, including volatile and non-volatile media, removable and non-removable media.

[0138] System memory 402 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 404 and / or cache memory 405. Electronic device 40 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 406 may be used to read and write non-removable, non-volatile magnetic media (… Figure 4 Not shown; usually referred to as a "hard drive"). Although Figure 4As not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk") and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 403 via one or more data media interfaces. Memory 402 may include at least one program product having a set (e.g., at least one) of program modules configured to perform the functions of the embodiments of this application.

[0139] A program / utility 408 having a set (at least one) of program modules 407 may be stored, for example, in memory 402. Such program modules 407 include, but are not limited to, an operating system, one or more application programs, other program modules, and program data. Each or some combination of these examples may include an implementation of a network environment. Program modules 407 typically perform the functions and / or methods described in the embodiments of this application.

[0140] Electronic device 40 can also communicate with one or more external devices 409 (e.g., keyboard, pointing device, display 410, etc.), and with one or more devices that enable a user to interact with electronic device 40, and / or with any device that enables electronic device 40 to communicate with one or more other computing devices (e.g., network card, modem, etc.). This communication can be performed via input / output (I / O) interface 411. Furthermore, electronic device 40 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 412. As shown, network adapter 412 communicates with other modules of electronic device 40 via bus 403. It should be understood that, although... Figure 4 As not shown, other hardware and / or software modules may be used in conjunction with electronic device 40, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0141] The processing unit 401 executes various functional applications and page processing by running programs stored in the system memory 402, such as implementing the data query statement optimization method provided in the embodiments of this application.

[0142] Example 5

[0143] This application also provides a storage medium containing computer-executable instructions, which, when executed by a computer processor, are used to perform a data query statement optimization method, the method comprising:

[0144] When an aggregate query request for a target data table is received, at least one index page that meets the query criteria is located; wherein each index page has pre-stored page-level statistics, which include at least the number of page records, the most recently updated transaction commit sequence number, and the range of page index key values.

[0145] For each index page, the effective count contribution value of the index page is determined based on the comparison result between the most recently updated transaction commit sequence number and the current minimum cleanable transaction commit sequence number, the matching relationship between the page index key value range and the query conditions, and the number of page records.

[0146] Based on the effective count contribution value of each index page, an aggregate query result is generated for the aggregate query request.

[0147] The computer storage medium in this application embodiment can be any combination of one or more computer-readable media. A computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. A computer-readable storage medium can be, for example,—but not limited to—an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this document, a computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0148] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, capable of sending, propagating, or transmitting programs for use by or in connection with an instruction execution system, apparatus, or device.

[0149] Program code contained on a computer-readable medium may be transmitted using any suitable medium, including—but not limited to—wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.

[0150] Computer program code for performing the operations of the embodiments of this application can be written in one or more programming languages ​​or a combination thereof. Programming languages ​​include object-oriented programming languages—such as Java, Smalltalk, and C++—and conventional procedural programming languages—such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0151] Note that the above description is merely a preferred embodiment and the technical principles employed in this application. Those skilled in the art will understand that this application is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the scope of protection of this application. Therefore, although this application has been described in detail through the above embodiments, this application is not limited to the above embodiments. Many other equivalent embodiments may be included without departing from the concept of this application, and the scope of this application is determined by the scope of the appended claims.

Claims

1. A method for optimizing data query statements, characterized in that, Applied to distributed database systems, including: When a statement optimization request for a target structured query statement is received, the target structured query statement is compiled to generate an execution plan tree suitable for a distributed database environment. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. The leaf nodes correspond to operations that read data from data shards on distributed data nodes, and the root nodes correspond to operations that output the final query result after distributed computing processing. The hierarchical structure is established based on the dependencies of the target structured query statement in the distributed database environment. During the process of traversing the execution plan tree based on the hierarchical structure, for each plan node visited, the execution plan of the plan node is analyzed for statement optimization based on at least one preset optimization rule corresponding to the plan node, so as to determine at least one statement optimization suggestion; Aggregate all optimization suggestions determined during the execution plan tree traversal, generate a list of optimization points, and output it.

2. The method according to claim 1, characterized in that, When a statement optimization request for a target structured query statement is received, the target structured query statement is compiled to generate an execution plan tree suitable for a distributed database environment, including: Receive a statement optimization request that includes an optimization preamble instruction; The statement optimization request is parsed to identify and extract the target structured query statement; The target structured query statement is parsed and logically compiled to generate a preliminary execution plan that reflects the query logic; An optimization rule checker is injected into each of the plan nodes of the preliminary execution plan to form the execution plan tree; wherein the optimization rule checker is pre-configured with at least one preset optimization rule that can be invoked.

3. The method according to claim 2, characterized in that, The step of injecting optimization rule checkers at each of the plan nodes of the preliminary execution plan to form the execution plan tree includes: Based on the preset interface definition rules, an interface call entry point is added to the data structure of each node in the preliminary execution plan; The interface call entry is associated with one or more preset optimization rule checkers, so that when any of the plan nodes is accessed during the traversal of the execution plan tree, the associated optimization rule checker can be triggered through the interface call entry to perform statement optimization analysis on the plan node.

4. The method according to claim 1, characterized in that, The step of performing statement optimization analysis on the execution plan of the plan node based on at least one preset optimization rule corresponding to the plan node, to determine at least one statement optimization suggestion, includes: Invoke the optimization rule checker registered on the plan node type; The optimization rule checker obtains the execution context information corresponding to the plan node; wherein, the execution context information includes at least one of the following: metadata of the data table operated by the plan node, statistical information of the data table, and a cost model used to evaluate the estimated cost of different query execution paths in order to select the optimal path; Based on the execution context information, determine whether the execution plan of the plan node conforms to the corresponding preset optimization rules; When the execution plan is detected to conform to at least one preset optimization rule, at least one statement optimization suggestion for the plan node is generated according to the optimization logic corresponding to the at least one preset optimization rule.

5. The method according to claim 1 or 4, characterized in that, The at least one preset optimization rule includes at least one of the following: First rule: Based on parsing the data operation statements that are components of the target structured query statement, identify the nested subquery statements within the data operation statements, and trigger an independent optimization analysis process for the subquery statements to generate in-depth optimization suggestions for the subquery statements; The second rule is to analyze at least one of the following factors: the syntactic features of the target structured query statement, the type of data source involved, and its configuration mode, and to identify and generate a distributed execution path suggestion suitable for large-scale parallel computation of the target structured query statement. Third rule: Based on the analysis of the conditional clauses in the target structured query statement, identify operations that may trigger a full table lock and generate risk warning suggestions; Fourth rule: By pushing cost analysis requests for the target structured query statement down to the data nodes, the actual data distribution information of the target structured query statement is obtained, and index optimization suggestions are generated based on the actual data distribution information. Fifth rule: Based on the semantic layer rewriting rules, perform logical optimization on the query structure of the target structured query statement to generate semantic optimization suggestions.

6. The method according to claim 5, characterized in that, The logical optimization of the query structure of the target structured query statement based on semantic layer rewriting rules includes at least one of the following operations: Extract the association conditions of the existence subqueries in the target structured query statement to the outer query to generate query rewriting suggestions; The row number-based classification and statistics operation in the target structured query statement is rewritten as a window function-based row number calculation operation to generate logical structure optimization suggestions.

7. The method according to claim 1, characterized in that, The aggregation traversal of the execution plan tree generates a list of optimization points, including all determined optimization suggestions: Collect the at least one statement optimization suggestion determined by each of the plan nodes during the traversal of the execution plan tree; All collected statement optimization suggestions are sent to a central aggregator, which then performs deduplication on the suggestions. Based on preset priority rules, the optimization suggestions for each of the deduplicated statements are sorted to form an ordered list of optimization points.

8. A data query statement optimization device, characterized in that, The apparatus, used in distributed database systems, includes: The execution plan tree determination module is used to compile the target structured query statement when a statement optimization request for the target structured query statement is received, and generate an execution plan tree suitable for a distributed database environment. The execution plan tree contains multiple plan nodes, which form a hierarchical structure from leaf nodes to root nodes. The leaf nodes correspond to operations that read data from a data table, and the root nodes correspond to operations that output the final query results. The hierarchical structure is established based on the logical dependencies of the target structured query statement. The optimization suggestion determination module is used to perform statement optimization analysis on the execution plan of each accessed plan node during the process of traversing the execution plan tree based on the hierarchical structure, based on at least one preset optimization rule corresponding to the plan node, so as to determine at least one statement optimization suggestion. The optimization point list output module is used to aggregate all optimization suggestions determined during the traversal of the execution plan tree, generate an optimization point list, and output it.

9. An electronic device, characterized in that, The electronic device includes: One or more processors; Storage device for storing one or more programs. When the one or more programs are executed by the one or more processors, the one or more processors implement the data query statement optimization method as described in any one of claims 1-7.

10. A storage medium containing computer-executable instructions, characterized in that, The computer-executable instructions, when executed by a computer processor, are used to perform the data query statement optimization method as described in any one of claims 1-7.

Images