Query optimization tracking method, device and computer equipment of database query optimizer

Abstract

This application relates to a query optimization tracking method, apparatus, and computer device for a database query optimizer. The method includes: acquiring a user's database query instruction, and allocating tracking adaptation resources for the optimizer based on the optimizer's query keywords in the database query instruction; controlling the optimizer to generate candidate execution paths based on the tracking adaptation resources, and generating optimization record information for the optimizer based on each candidate execution path, using the optimizer and a cost evaluation model; identifying execution path information and elimination reasons for each eliminated execution path based on the optimization record information, and generating a path selection tree for the optimizer based on the execution path information and elimination reasons for each eliminated execution path. This method improves the comprehensiveness of query optimization tracking for the database query optimizer for the user.

Description

Technical Field

[0001] This application relates to the field of artificial intelligence network technology, and in particular to a query optimization tracking method, apparatus and computer device for a database query optimizer. Background Technology

[0002] The database query optimizer is a core component of modern database management systems, responsible for selecting the most efficient execution plan for a given SQL query. However, in current mainstream database systems (such as PostgreSQL and TeleDB), the optimizer's internal decision-making mechanism is an opaque "black box" for users. When the optimizer makes a mistake and fails to select the optimal execution plan, instead generating a costly suboptimal plan, database administrators (DBAs) and developers face significant analytical challenges. They can observe the performance degradation, but due to the lack of access to the optimizer's internal decision-making details, they struggle to pinpoint the root cause of the problem. Therefore, improving the accuracy of obtaining the internal decision-making details of the database query optimizer is a current research focus.

[0003] In existing technologies, such as the standard EXPLAIN command, only the user can see the execution plan finally selected by the optimizer, while completely hiding all other candidate paths considered, evaluated and ultimately eliminated during the process of generating the plan, as well as their cost comparison information. This results in a poor comprehensiveness for the user in tracking the query optimization of the database query optimizer. Summary of the Invention

[0004] Therefore, it is necessary to provide a query optimization tracking method, apparatus, computer device, computer-readable storage medium, and computer program product for a database query optimizer to address the aforementioned technical problems.

[0005] Firstly, this application provides a query optimization tracing method for a database query optimizer, including:

[0006] Obtain the user's database query instruction, and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction;

[0007] Based on the tracking and adaptation resources, the optimizer is controlled to generate each candidate execution path, and based on each candidate execution path, the optimizer and the cost evaluation model are used to generate the optimizer's optimization record information.

[0008] Based on the optimization record information, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are identified, and the path selection tree of the optimizer is generated based on the execution path information of each elimination execution path and the elimination reason of each elimination execution path.

[0009] Optionally, after obtaining the user's database query command, the following may also be included:

[0010] Determine whether the database query instruction includes the optimizer's query keywords;

[0011] If the database query instruction does not include the optimizer's query keywords, the optimizer is controlled to perform a query optimization task based on the database query instruction to obtain optimized query results.

[0012] When the database query instruction includes the optimizer's query keywords, the marking information of each optimization tracking flag is adjusted.

[0013] Optionally, allocating the optimizer's tracking adaptation resources based on the optimizer's query keywords in the database query instruction includes:

[0014] Obtain the resource allocation strategy and break it down into sub-resource allocation strategies for each resource allocation type;

[0015] Based on the resource allocation strategy of each resource allocation type, resource configuration processing is performed on the resource configuration structure corresponding to each resource allocation type to obtain sub-tracking and adapting resources of each resource allocation type.

[0016] All sub-tracking adaptation resources of all resource allocation types are used as the tracking adaptation resources of the optimizer.

[0017] Optionally, before controlling the optimizer to generate candidate execution paths based on the tracking and adaptation resources, the method further includes:

[0018] Based on the database query command, the optimizer generates query optimization tasks and path monitoring tasks.

[0019] Optionally, based on the tracking and adaptation resources, the optimizer is controlled to generate candidate execution paths, including:

[0020] Based on the tracking and adaptation resources, the optimizer is controlled to execute the query optimization task to obtain each execution path;

[0021] Each candidate execution path is processed by path identification to obtain the candidate execution paths.

[0022] Optionally, before generating the optimizer's optimization record information based on each of the candidate execution paths, using the optimizer and the cost evaluation model, the method further includes:

[0023] Obtain the initial path information for each candidate execution path;

[0024] Based on the initial path information of each candidate execution path, the initial path information is processed by a cost evaluation model to obtain the cost evaluation result of each candidate execution path.

[0025] Optionally, the step of generating optimization record information for the optimizer based on each of the candidate execution paths, through the optimizer and the cost evaluation model, includes:

[0026] Based on the cost evaluation results of each candidate execution path, the optimizer generates the optimized execution results for each eliminated execution path and the query optimization task.

[0027] The elimination execution paths and the optimization execution results corresponding to the query optimization tasks are used as the optimization record information of the optimizer.

[0028] Optionally, after generating the optimizer's optimization record information based on each of the candidate execution paths, through the optimizer and the cost evaluation model, the method further includes:

[0029] Execute the path monitoring task to collect detailed path information for each of the elimination execution paths;

[0030] Based on the detailed path information of each elimination execution path, an elimination path generation structure diagram is generated through the elimination path linked list.

[0031] Optionally, after generating the optimizer's optimization record information based on each of the candidate execution paths, through the optimizer and the cost evaluation model, the method further includes:

[0032] Based on the optimized execution results, identify the expected execution steps of the database query instruction and the expected cost information of the database query instruction;

[0033] The expected execution steps and expected cost information of the database query instruction are used to generate a query optimization report corresponding to the database query instruction using an optimization report generation template.

[0034] Optionally, the step of identifying the execution path information of each elimination execution path and the elimination reason of each elimination execution path based on the optimization record information includes:

[0035] Traverse each optimization tracking flag, identify the current marking information of each optimization tracking flag, and identify the optimization elimination process of the optimizer based on the current marking information of each optimization tracking flag;

[0036] Based on the optimized elimination process, a structure graph is generated by traversing the elimination paths using a priority-order recursive strategy, and the elimination reasons and execution path information of each elimination execution path are identified.

[0037] Optionally, generating the optimizer's path selection tree based on the execution path information of each of the elimination execution paths and the elimination reason of each of the elimination execution paths includes:

[0038] Get the path selection tree template;

[0039] By using a tree-shaped indentation strategy, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are filled into the path selection tree template to obtain the path selection tree of the optimizer.

[0040] Secondly, this application also provides a query optimization tracking device for a database query optimizer, comprising:

[0041] The acquisition module is used to acquire the user's database query instruction and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction.

[0042] The control module is used to control the optimizer to generate candidate execution paths based on the tracking and adaptation resources, and to generate optimization record information of the optimizer based on each candidate execution path through the optimizer and the cost evaluation model.

[0043] The generation module is used to identify the execution path information of each elimination execution path and the elimination reason of each elimination execution path based on the optimization record information, and generate the path selection tree of the optimizer based on the execution path information of each elimination execution path and the elimination reason of each elimination execution path.

[0044] Optionally, the device further includes:

[0045] The judgment module is used to determine whether the database query instruction includes the optimizer's query keywords;

[0046] The first judgment and processing module is used to control the optimizer to perform a query optimization task based on the database query instruction when the database query instruction does not include the optimizer's query keywords, so as to obtain an optimized query result;

[0047] The second judgment and processing module is used to judge the module and adjust the marking information of each optimization tracking flag when the database query instruction includes the optimizer's query keywords.

[0048] Optionally, the acquisition module is specifically used for:

[0049] Obtain the resource allocation strategy and break it down into sub-resource allocation strategies for each resource allocation type;

[0050] Based on the resource allocation strategy of each resource allocation type, resource configuration processing is performed on the resource configuration structure corresponding to each resource allocation type to obtain sub-tracking and adapting resources of each resource allocation type.

[0051] All sub-tracking adaptation resources of all resource allocation types are used as the tracking adaptation resources of the optimizer.

[0052] Optionally, the device further includes:

[0053] The task generation module is used to generate query optimization tasks and path monitoring tasks for the optimizer based on the database query instructions.

[0054] Optionally, the control module is specifically used for:

[0055] Based on the tracking and adaptation resources, the optimizer is controlled to execute the query optimization task to obtain each execution path;

[0056] Each candidate execution path is processed by path identification to obtain the candidate execution paths.

[0057] Optionally, the device further includes:

[0058] The acquisition module is used to obtain the initial path information for each candidate execution path;

[0059] Based on the initial path information of each candidate execution path, the initial path information is processed by a cost evaluation model to obtain the cost evaluation result of each candidate execution path.

[0060] Optionally, the device further includes:

[0061] The result generation module is used to generate the optimized execution results for each eliminated execution path and the query optimization task based on the cost evaluation results of each candidate execution path through the optimizer.

[0062] The recording module is used to record the optimization record information of the optimizer as each of the elimination execution paths and the optimization execution results corresponding to the query optimization tasks.

[0063] Optionally, the device further includes:

[0064] The data acquisition module is used to perform the path monitoring task and collect detailed path information for each of the elimination execution paths;

[0065] The structure diagram generation module is used to generate an elimination path generation structure diagram based on the path details of each elimination execution path and through the elimination path linked list.

[0066] Optionally, the device further includes:

[0067] The identification module is used to identify the expected execution steps of the database query instruction and the expected cost information of the database query instruction based on the optimized execution result.

[0068] The report generation module is used to generate a query optimization report corresponding to the database query instruction by taking the expected execution steps of the database query instruction and the expected cost information of the database query instruction and optimizing the report generation template.

[0069] Optionally, the control module is specifically used for:

[0070] Traverse each optimization tracking flag, identify the current marking information of each optimization tracking flag, and identify the optimization elimination process of the optimizer based on the current marking information of each optimization tracking flag;

[0071] Based on the optimized elimination process, a structure graph is generated by traversing the elimination paths using a priority-order recursive strategy, and the elimination reasons and execution path information of each elimination execution path are identified.

[0072] Optionally, the generation module is specifically used for:

[0073] Get the path selection tree template;

[0074] By using a tree-shaped indentation strategy, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are filled into the path selection tree template to obtain the path selection tree of the optimizer.

[0075] Thirdly, this application provides a computer device. The computer device includes a memory and a processor, the memory storing a computer program, and the processor executing the computer program to implement the steps of the method described in any one of the first aspects.

[0076] Fourthly, this application provides a computer-readable storage medium having a computer program stored thereon that, when executed by a processor, implements the steps of the method described in any one of the first aspects.

[0077] Fifthly, this application provides a computer program product. The computer program product includes a computer program that, when executed by a processor, implements the steps of the method described in any one of the first aspects.

[0078] The aforementioned query optimization tracking method, apparatus, and computer device for database query optimizers acquire the user's database query command and allocate tracking adaptation resources for the optimizer based on the optimizer's query keywords in the database query command. Based on the tracking adaptation resources, the optimizer is controlled to generate candidate execution paths, and based on each candidate execution path, optimization record information of the optimizer is generated through the optimizer and a cost evaluation model. Based on the optimization record information, the execution path information of each eliminated execution path and the elimination reason of each eliminated execution path are identified, and a path selection tree of the optimizer is generated based on the execution path information and the elimination reason of each eliminated execution path. This solution, by completely recording and structurally storing detailed information of all eliminated paths during the query optimization process (such as access method, join order, algorithm, and cost), solves the problem of the lack of in-depth tracking of the optimizer's decision-making process in existing technologies, greatly enhancing the transparency of optimizer behavior and the interpretability of decision logic. Secondly, this solution uses depth-first traversal and hierarchical indentation to intuitively output a complete path selection tree, enabling developers to quickly locate suboptimal plans caused by statistical biases or inaccurate cost estimations. This provides a direct basis for accurate index creation and configuration optimization, shortening the tuning cycle. Finally, by systematically collecting and analyzing these discarded paths, this solution allows developers to proactively discover hidden logical bugs or cost calculation defects in the optimizer that are difficult to reproduce in conventional testing, thus enabling targeted algorithm improvements and model optimizations. This completely changes the previous inefficient troubleshooting model that relied on developers manually reproducing and analyzing code line by line, freeing development personnel from tedious deep debugging work and significantly saving human resource costs. This comprehensively improves the user's ability to track query optimization in the database query optimizer. Attached Figure Description

[0079] To more clearly illustrate the technical solutions in the embodiments or related technologies of this application, the accompanying drawings used in the description of the embodiments or related technologies will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0080] Figure 1 This is a flowchart illustrating the query optimization tracing method of a database query optimizer in one embodiment;

[0081] Figure 2 This is a flowchart illustrating an example of query optimization tracing for a database query optimizer in one embodiment;

[0082] Figure 3 This is a structural block diagram of the query optimization tracking device of a database query optimizer in one embodiment;

[0083] Figure 4 This is an internal structural diagram of a computer device in one embodiment. Detailed Implementation

[0084] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0085] The query optimization tracing method for database query optimizers provided in this application can be applied to a query optimization tracing system for database query optimizers. This system can be applied to a terminal, which can be, but is not limited to, various personal computers, laptops, mid-range computers, etc. Specifically, by completely recording and structurally storing detailed information (such as access methods, join orders, algorithms, and costs) of all eliminated paths during the query optimization process, the terminal solves the problem of insufficient in-depth tracking of the optimizer's decision-making process in existing technologies, greatly enhancing the transparency of optimizer behavior and the interpretability of decision logic. Secondly, this solution uses depth-first traversal and hierarchical indentation to intuitively output a complete path selection tree, enabling developers to quickly locate suboptimal plans caused by statistical information bias or inaccurate cost estimation, providing a direct basis for accurate index creation and configuration optimization, and shortening the tuning cycle. Finally, by systematically collecting and analyzing these eliminated paths, this solution allows developers to proactively discover hidden logical bugs or cost calculation defects in the optimizer that are difficult to reproduce in conventional testing, thereby enabling targeted algorithm improvements and model optimization. This completely changes the inefficient troubleshooting model that previously relied on developers manually reproducing and analyzing code line by line, freeing up development personnel from tedious deep debugging work and significantly saving human resource costs. This, in turn, comprehensively improves the user's ability to track query optimizations using the database query optimizer.

[0086] In one exemplary embodiment, such as Figure 1 As shown, a query optimization tracing method for a database query optimizer is provided. Taking the application of this method to a terminal as an example, the method includes the following steps S101 to S103. Wherein:

[0087] Step S101: Obtain the user's database query instruction, and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction.

[0088] In this embodiment, in response to the user's information upload operation, the terminal obtains the user's input EXPLAIN query statement and uses it as a database query instruction. Then, based on the optimizer's query keywords in the database query instruction, the terminal allocates optimizer tracking adaptation resources. These tracking adaptation resources are memory resources allocated for tracking and monitoring the optimizer's optimization path, including but not limited to: 1. initializing a linked list structure for storing elimination paths; 2. allocating a sufficiently large output buffer; and 3. setting various necessary tracking status variables. The specific allocation process will be described in detail later.

[0089] Step S102: Based on tracking and adapting resources, control the optimizer to generate each candidate execution path, and based on each candidate execution path, generate the optimizer's optimization record information through the optimizer and the cost evaluation model.

[0090] In this embodiment, the terminal controls the optimizer to generate candidate execution paths based on resource tracking and adaptation. Based on these candidate execution paths, the optimizer, along with a cost evaluation model, generates optimization record information. The candidate execution paths are generated by the optimizer. Furthermore, the optimization record information also includes the optimizer's decision to eliminate a candidate execution path during the evaluation process based on the cost evaluation model. The terminal records the entire optimization, elimination, and selection process of the optimizer, obtaining the optimizer's optimization record information. The specific recording process will be explained in detail later.

[0091] Step S103: Based on the optimization record information, identify the execution path information of each elimination execution path and the elimination reason of each elimination execution path, and generate the optimizer's path selection tree based on the execution path information of each elimination execution path and the elimination reason of each elimination execution path.

[0092] In this embodiment, the terminal identifies the execution path information and elimination reasons for each eliminated execution path based on optimization record information. Based on these information, it generates the optimizer's path selection tree. This path selection tree is a tree diagram of the optimizer's entire elimination process, obtained by printing the detailed information and elimination reasons for each eliminated path in an easily understandable tree-like indented format. The specific generation method will be explained in detail later. The execution path information includes complete detailed information about the eliminated path, including its specific access method, explicit connection order, the name of the connection algorithm used, and complete cost estimation details. The elimination reason is the specific reason why the optimizer eliminates the path.

[0093] Based on the above solution, by fully recording and structurally storing detailed information (such as access methods, join order, algorithms, and costs) of all eliminated paths during query optimization, this solution solves the problem of insufficient in-depth tracking of the optimizer's decision-making process in existing technologies, greatly enhancing the transparency of optimizer behavior and the interpretability of decision logic. Secondly, this solution uses depth-first traversal and hierarchical indentation to intuitively output a complete path selection tree, enabling developers to quickly locate suboptimal plans caused by statistical biases or inaccurate cost estimations. This provides a direct basis for accurate index creation and configuration optimization, shortening the tuning cycle. Finally, by systematically collecting and analyzing these eliminated paths, this solution allows developers to proactively discover hidden logical bugs or cost calculation defects in the optimizer that are difficult to reproduce in conventional testing, thus enabling targeted algorithm improvements and model optimization. This completely changes the inefficient troubleshooting model that previously relied on developers manually reproducing and analyzing code line by line, freeing development personnel from tedious deep debugging work and significantly saving human resource costs. Therefore, it comprehensively improves the user's ability to track the query optimization of the database query optimizer.

[0094] Optionally, after obtaining the user's database query instruction, the method further includes: determining whether the database query instruction contains the optimizer's query keywords; if the database query instruction does not contain the optimizer's query keywords, controlling the optimizer to perform query optimization tasks based on the database query instruction to obtain optimized query results; and if the database query instruction contains the optimizer's query keywords, adjusting the marking information of each optimization tracking flag.

[0095] In this embodiment, the terminal determines whether the database query instruction includes the optimizer's query keywords. If the database query instruction does not include the optimizer's query keywords, the terminal controls the optimizer to execute a query optimization task based on the database query instruction, thereby obtaining an optimized query result. This optimized query result is the standard EXPLAIN execution plan result output by the optimizer when normally executing the database query instruction. This result displays the optimizer's expected steps after optimizing the query execution, as well as the cost estimate corresponding to those expected steps, in a traditional format.

[0096] Then, when the database query command includes the optimizer's query keywords, the terminal adjusts the marking information of each optimization tracking flag. Each optimization tracking flag is located at a node position in the database, representing a fork in the road between different paths. When the optimizer passes through this node, the marking information of this optimization tracking flag changes, automatically triggering a query optimization tracking output sub-process. This sub-process recursively traverses the elimination path list of each level of relationship in depth-first order. In this adjustment, the terminal marks each optimization tracking flag as TRUE.

[0097] Based on the above scheme, by checking whether the query instruction contains the query keyword, the tracking operation of the optimizer's optimization process can be intelligently controlled, thereby improving the controllability and autonomy of the tracking operation.

[0098] Optionally, based on the optimizer's query keywords in the database query command, the optimizer's tracking adaptation resources are allocated, including: obtaining the resource allocation strategy and breaking it down into sub-resource allocation strategies for each resource allocation type; performing resource configuration processing on the resource configuration structure corresponding to each resource allocation type based on the resource allocation strategy for each resource allocation type to obtain sub-tracking adaptation resources for each resource allocation type; and using all sub-tracking adaptation resources of all resource allocation types as the optimizer's tracking adaptation resources.

[0099] In this embodiment, the terminal obtains the resource allocation policy and breaks it down into sub-resource allocation policies for each resource allocation type. This resource allocation policy represents the strategy for tracking resource allocation to different memory resources. The tracking resource allocation policy for each hardware device is a sub-resource allocation policy for each resource allocation type, which includes eviction path storage structure type, buffer type, and tracking state variable type, etc.

[0100] Then, based on the resource allocation strategy for each resource allocation type, the terminal performs resource configuration processing on the resource configuration structure corresponding to each resource allocation type to obtain sub-tracking adaptation resources for each resource allocation type. Finally, the terminal uses the sub-tracking adaptation resources of all resource allocation types as the tracking adaptation resources for the optimizer.

[0101] Based on the above scheme, by allocating all necessary memory resources to the optimizer during the optimizer initialization phase, the efficiency of terminal query tracking and the normal operation of the database are ensured.

[0102] Optionally, before the optimizer generates each candidate execution path based on tracking and adapting resources, it also includes: generating the optimizer's query optimization task and path monitoring task based on database query instructions.

[0103] In this embodiment, the terminal generates an optimizer query optimization task and a path monitoring task based on a database query command. The query optimization task is the optimizer's normal execution task, while the path monitoring task is a tracking task where the terminal tracks the optimizer. This tracking task includes the triggering conditions for different optimization tracking flags and the tracking record process for each optimization tracking flag.

[0104] Based on the above scheme, by automatically generating the optimizer's query optimization tasks and path monitoring tasks, it is possible to ensure the normal execution of the optimizer and the normal execution of the path monitoring tasks by the terminal, thus ensuring the terminal's tracking efficiency of the optimizer.

[0105] Optionally, based on tracking and adapting resources, the control optimizer generates each candidate execution path, including: based on tracking and adapting resources, the control optimizer executes query optimization tasks to obtain each execution path; and performs path identification processing on each candidate execution path to obtain each candidate execution path.

[0106] In this embodiment, the terminal controls the optimizer to execute query optimization tasks based on tracking and adapting resources, thereby obtaining various execution paths. Then, the terminal performs path identification processing on each candidate execution path to obtain all candidate execution paths. The path identification processing is used to distinguish between execution paths and avoid discrepancies when recording path information later. This identification method can be a custom identification method such as named identifiers, symbol identifiers, or letter identifiers.

[0107] Based on the above scheme, by identifying and processing the information of each path, the efficiency of identifying the path information and the accuracy of recording are improved.

[0108] Optionally, before generating the optimizer's optimization record information based on each candidate execution path using the optimizer and the cost evaluation model, the process further includes: obtaining the initial path information for each candidate execution path; and performing cost evaluation processing on the initial path information using the cost evaluation model based on the initial path information for each candidate execution path to obtain the cost evaluation result for each candidate execution path.

[0109] In this embodiment, the terminal obtains the initial path information for each candidate execution path. This initial path information includes the path access method and the connection order of the candidate execution path.

[0110] Then, based on the initial path information of each candidate execution path, the terminal performs cost evaluation processing on the initial path information through a cost evaluation model to obtain the cost evaluation result for each candidate execution path. This cost evaluation model is a preset evaluation model in the optimizer, used to evaluate the execution cost of each candidate execution path.

[0111] Based on the above scheme, by combining the initial path information to perform a fine cost estimation for each candidate execution path, the optimizer can perform path selection among the candidate execution paths and can track the optimizer's selection logic for each candidate execution path.

[0112] Optionally, based on each candidate execution path, the optimizer's optimization record information is generated through the optimizer and the cost evaluation model, including: based on the cost evaluation results of each candidate execution path, the optimizer generates the optimization execution results corresponding to each eliminated execution path and the query optimization task; and the optimization execution results corresponding to each eliminated execution path and the query optimization task are used as the optimizer's optimization record information.

[0113] In this embodiment, based on the cost evaluation results of each candidate execution path, the terminal generates, through the optimizer, the optimized execution results for each eliminated execution path and the corresponding optimization task. The optimized execution results here contain the same content as those described above. Specifically, during monitoring, once the terminal detects that the optimizer has decided to eliminate a candidate execution path during the evaluation process, the terminal immediately captures and generates complete detailed information about that path. This complete detailed information includes its specific access method, the explicit connection order, the name of the connection algorithm used, and complete cost estimation details.

[0114] Then, the terminal uses the elimination execution paths and the optimization execution results corresponding to the query optimization tasks as optimization record information for the optimizer.

[0115] Based on the above scheme, by recording the entire selection process of the optimizer and comprehensively recording the selection logic of the optimizer, the comprehensiveness of the recording of the optimizer task execution process is improved.

[0116] Optionally, after generating the optimizer's optimization record information based on each candidate execution path through the optimizer and cost evaluation model, the process also includes: an execution path monitoring task to collect detailed path information for each eliminated execution path; and generating an elimination path generation structure diagram based on the detailed path information for each eliminated execution path through the elimination path linked list.

[0117] In this embodiment, the terminal performs a path monitoring task, collecting detailed path information for each elimination execution path. Based on the detailed path information of each elimination execution path, an elimination path generation structure diagram is generated through the elimination path linked list. This elimination path linked list is the linked list structure allocated to the terminal for tracking and storing elimination paths during resource allocation. The elimination path generation structure diagram includes detailed path information for each elimination path, the time point of the elimination path, and the corresponding optimized tracking flag for that elimination path.

[0118] Based on the above scheme, by storing each elimination path in a linked list, it is possible to obtain detailed path information for each elimination path, as well as record the time point of elimination and the location of elimination, thereby improving the comprehensiveness of global elimination in the optimization area.

[0119] Optionally, after generating the optimizer's optimization record information based on each candidate execution path, through the optimizer and cost evaluation model, the method further includes: identifying the expected execution steps and expected cost information of the database query instruction based on the optimization execution results; and generating a query optimization report corresponding to the database query instruction by using the expected execution steps and expected cost information of the database query instruction through an optimization report generation template.

[0120] In this embodiment, the terminal identifies the expected execution steps and expected cost information of a database query instruction based on the optimized execution results. Then, the terminal generates a query optimization report corresponding to the database query instruction using an optimization report generation template. This optimization report generation template is a pre-set report template on the terminal used to provide feedback on the optimizer's optimization execution results. The terminal fills the expected execution steps and expected cost information of the database query instruction into this report template to obtain the query optimization report.

[0121] Based on the above solution, by generating a query optimization report from the optimized execution results, the readability of the feedback optimization execution results is effectively improved.

[0122] Optionally, based on the optimization record information, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are identified, including: traversing each optimization tracking flag, identifying the current marking information of each optimization tracking flag, and identifying the optimizer's optimization elimination process based on the current marking information of each optimization tracking flag; based on the optimization elimination process, generating a structure graph by traversing the elimination path through a priority recursive strategy, and identifying the elimination reason of each elimination execution path and the execution path information of each elimination execution path.

[0123] In this embodiment, the terminal traverses each optimization tracking flag, identifies the current marking information of each optimization tracking flag, and identifies the optimizer's optimization and elimination process based on the current marking information of each optimization tracking flag. Then, based on the optimization and elimination process, the terminal traverses the elimination paths to generate a structure graph through a priority-order recursive strategy, identifying the elimination reason for each elimination execution path and the execution path information for each elimination execution path. The elimination reason for each elimination execution path is the complete cost estimation details for each elimination execution path, while the execution path information for each elimination execution path includes its specific access method, explicit connection order, path elimination time point, and its corresponding optimization tracking flag.

[0124] Based on the above scheme, by recursively traversing the elimination path linked list of each level of relationship in priority order, the elimination reasons of each elimination execution path and the execution path information of each elimination execution path are identified, thereby improving the comprehensiveness of the record acquisition and analysis of each elimination execution path.

[0125] Optionally, based on the execution path information of each elimination execution path and the elimination reason of each elimination execution path, the optimizer's path selection tree is generated, including: obtaining the path selection tree template; and using a tree indentation strategy, filling the path information of each elimination execution path and the elimination reason of each elimination execution path into the path selection tree template to obtain the optimizer's path selection tree.

[0126] In this embodiment, the terminal obtains a path selection tree template. Then, using a tree-based indentation strategy, the terminal fills the template with the execution path information and elimination reasons for each eliminated execution path, thus obtaining the optimizer's path selection tree. This path selection tree template is a tree diagram based on the global structure of the database being executed by the optimizer. The terminal then fills this template with the details and elimination reasons for each eliminated execution path using a tree-based indentation strategy, resulting in the optimizer's path selection tree. This tree-based indentation strategy prints detailed information and elimination reasons for all eliminated paths one by one in an easily understandable tree-based indentation format. This path selection tree provides a more intuitive and comprehensive feedback on the optimizer's entire optimization process. Finally, the terminal actively releases all its occupied memory resources, including clearing all linked list contents, releasing the output buffer, and resetting the global tracking flag to FALSE, officially ending the entire processing flow.

[0127] Based on the above scheme, generating a path selection tree can provide more intuitive and comprehensive feedback on the entire optimization process of the optimizer.

[0128] This application also provides an example of query optimization tracing for a database query optimizer, such as... Figure 2 As shown, the specific processing procedure includes the following steps:

[0129] Step S201: Obtain the user's database query command.

[0130] Step S202: Determine whether the database query instruction includes the optimizer's query keywords.

[0131] Step S203: If the database query instruction does not include the optimizer's query keywords, the optimizer is controlled to perform a query optimization task based on the database query instruction to obtain the optimized query result.

[0132] Step S204: If the database query instruction includes the optimizer's query keywords, adjust the marking information of each optimization tracking flag bit.

[0133] Step S205: Obtain the resource allocation strategy and break it down into sub-resource allocation strategies for each resource allocation type.

[0134] Step S206: Based on the resource allocation strategy of each resource allocation type, perform resource configuration processing on the resource configuration structure corresponding to each resource allocation type to obtain the sub-tracking adaptation resources of each resource allocation type.

[0135] Step S207: Use all sub-tracking adaptation resources of all resource allocation types as tracking adaptation resources for the optimizer.

[0136] Step S208: Based on the database query instructions, generate the optimizer's query optimization task and path monitoring task.

[0137] Step S209: Based on the tracking and adaptation resources, control the optimizer to execute query optimization tasks and obtain each execution path.

[0138] Step S210: Perform path identification processing on each candidate execution path to obtain each candidate execution path.

[0139] Step S211: Obtain the initial path information for each candidate execution path.

[0140] Step S212: Based on the initial path information of each candidate execution path, the initial path information is processed by a cost evaluation model to obtain the cost evaluation result of each candidate execution path.

[0141] Step S213: Based on the cost evaluation results of each candidate execution path, the optimizer generates the optimized execution results of each eliminated execution path and the corresponding query optimization task.

[0142] Step S214: The elimination execution paths and the optimization execution results corresponding to the query optimization tasks are used as optimization record information of the optimizer.

[0143] Step S215: Execute the path monitoring task and collect detailed path information for each elimination execution path.

[0144] Step S216: Based on the detailed path information of each elimination execution path, generate an elimination path generation structure diagram through the elimination path linked list.

[0145] Step S217: Based on the optimized execution results, identify the expected execution steps of the database query instruction and the expected cost information of the database query instruction.

[0146] Step S218: The expected execution steps of the database query instruction and the expected cost information of the database query instruction are used to generate a query optimization report corresponding to the database query instruction through the optimization report generation template.

[0147] Step S219: Traverse each optimization tracking flag, identify the current marking information of each optimization tracking flag, and identify the optimization elimination process of the optimizer based on the current marking information of each optimization tracking flag.

[0148] Step S220: Based on the optimized elimination process, a structure graph is generated by traversing the elimination paths using a priority recursive strategy, and the elimination reasons and execution path information of each elimination execution path are identified.

[0149] Step S221: Obtain the path selection tree template.

[0150] Step S222: Using a tree-shaped indentation strategy, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are filled into the path selection tree template to obtain the optimizer's path selection tree.

[0151] It should be understood that although the steps in the flowcharts of the embodiments described above are shown sequentially according to the arrows, these steps are not necessarily executed in the order indicated by the arrows. Unless explicitly stated herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least some steps in the flowcharts of the embodiments described above may include multiple steps or multiple stages. These steps or stages are not necessarily completed at the same time, but can be executed at different times. The execution order of these steps or stages is not necessarily sequential, but can be performed alternately or in turn with other steps or at least some of the steps or stages of other steps.

[0152] Based on the same inventive concept, this application also provides a query optimization tracing apparatus for implementing the query optimization tracing method of the database query optimizer described above. The solution provided by this apparatus is similar to the implementation described in the above method. Therefore, the specific limitations of one or more embodiments of the query optimization tracing apparatus for the database query optimizer provided below can be found in the limitations of the query optimization tracing method for the database query optimizer described above, and will not be repeated here.

[0153] In one exemplary embodiment, such as Figure 3 As shown, a query optimization tracking device for a database query optimizer is provided, comprising: an acquisition module 310, a control module 320, and a generation module 330, wherein:

[0154] The acquisition module 310 is used to acquire the user's database query instruction and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction.

[0155] The control module 320 is used to control the optimizer to generate each candidate execution path based on the tracking and adaptation resources, and to generate the optimizer's optimization record information based on each candidate execution path through the optimizer and the cost evaluation model;

[0156] The generation module 330 is used to identify the execution path information of each elimination execution path and the elimination reason of each elimination execution path based on the optimization record information, and generate the path selection tree of the optimizer based on the execution path information of each elimination execution path and the elimination reason of each elimination execution path.

[0157] Optionally, the device further includes:

[0158] The judgment module is used to determine whether the database query instruction includes the optimizer's query keywords;

[0159] The first judgment and processing module is used to control the optimizer to perform a query optimization task based on the database query instruction when the database query instruction does not include the optimizer's query keywords, so as to obtain an optimized query result;

[0160] The second judgment and processing module is used to judge the module and adjust the marking information of each optimization tracking flag when the database query instruction includes the optimizer's query keywords.

[0161] Optionally, the acquisition module 310 is specifically used for:

[0162] Obtain the resource allocation strategy and break it down into sub-resource allocation strategies for each resource allocation type;

[0163] Based on the resource allocation strategy of each resource allocation type, resource configuration processing is performed on the resource configuration structure corresponding to each resource allocation type to obtain sub-tracking and adapting resources of each resource allocation type.

[0164] All sub-tracking adaptation resources of all resource allocation types are used as the tracking adaptation resources of the optimizer.

[0165] Optionally, the device further includes:

[0166] The task generation module is used to generate query optimization tasks and path monitoring tasks for the optimizer based on the database query instructions.

[0167] Optionally, the control module 320 is specifically used for:

[0168] Based on the tracking and adaptation resources, the optimizer is controlled to execute the query optimization task to obtain each execution path;

[0169] Each candidate execution path is processed by path identification to obtain the candidate execution paths.

[0170] Optionally, the device further includes:

[0171] The acquisition module is used to obtain the initial path information for each candidate execution path;

[0172] Based on the initial path information of each candidate execution path, the initial path information is processed by a cost evaluation model to obtain the cost evaluation result of each candidate execution path.

[0173] Optionally, the device further includes:

[0174] The result generation module is used to generate the optimized execution results for each eliminated execution path and the query optimization task based on the cost evaluation results of each candidate execution path through the optimizer.

[0175] The recording module is used to record the optimization record information of the optimizer as each of the elimination execution paths and the optimization execution results corresponding to the query optimization tasks.

[0176] Optionally, the device further includes:

[0177] The data acquisition module is used to perform the path monitoring task and collect detailed path information for each of the elimination execution paths;

[0178] The structure diagram generation module is used to generate an elimination path generation structure diagram based on the path details of each elimination execution path and through the elimination path linked list.

[0179] Optionally, the device further includes:

[0180] The identification module is used to identify the expected execution steps of the database query instruction and the expected cost information of the database query instruction based on the optimized execution result.

[0181] The report generation module is used to generate a query optimization report corresponding to the database query instruction by taking the expected execution steps of the database query instruction and the expected cost information of the database query instruction and optimizing the report generation template.

[0182] Optionally, the control module 320 is specifically used for:

[0183] Traverse each optimization tracking flag, identify the current marking information of each optimization tracking flag, and identify the optimization elimination process of the optimizer based on the current marking information of each optimization tracking flag;

[0184] Based on the optimized elimination process, a structure graph is generated by traversing the elimination paths using a priority-order recursive strategy, and the elimination reasons and execution path information of each elimination execution path are identified.

[0185] Optionally, the generation module 330 is specifically used for:

[0186] Get the path selection tree template;

[0187] By using a tree-shaped indentation strategy, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are filled into the path selection tree template to obtain the path selection tree of the optimizer.

[0188] The various modules in the query optimization tracking device of the aforementioned database query optimizer can be implemented entirely or partially through software, hardware, or a combination thereof. These modules can be embedded in or independent of the processor in a computer device, or stored in the memory of a computer device as software, so that the processor can call and execute the operations corresponding to each module.

[0189] In one exemplary embodiment, a computer device is provided, which may be a terminal, and its internal structure diagram may be as follows: Figure 4As shown, the computer device includes a processor, memory, input / output interfaces, a communication interface, a display unit, and an input device. The processor, memory, and input / output interfaces are connected via a system bus, and the communication interface, display unit, and input device are also connected to the system bus via the input / output interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system and computer programs. The internal memory provides an environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The input / output interfaces are used for exchanging information between the processor and external devices. The communication interface is used for wired or wireless communication with external terminals; wireless communication can be achieved through Wi-Fi, mobile cellular networks, NFC (Near Field Communication), or other technologies. When executed by the processor, the computer program implements a query optimization tracing method for a database query optimizer. The display unit is used to form a visually visible image and can be a display screen, a projection device, or a virtual reality imaging device. The display screen can be an LCD screen or an e-ink screen. The input device of the computer device can be a touch layer covering the display screen, or buttons, trackballs, or touchpads set on the casing of the computer device, or external keyboards, touchpads, or mice, etc.

[0190] Those skilled in the art will understand that Figure 4 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.

[0191] In one exemplary embodiment, a computer device is provided, including a memory and a processor, the memory storing a computer program, the processor executing the computer program to implement the steps of a query optimization tracing method of a database query optimizer.

[0192] In one embodiment, a computer-readable storage medium is provided having a computer program stored thereon, which, when executed by a processor, implements the steps of a query optimization tracing method of a database query optimizer.

[0193] In one embodiment, a computer program product is provided, including a computer program that, when executed by a processor, implements the steps of a query optimization tracing method of a database query optimizer.

[0194] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0195] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium. When executed, the computer program can include the processes of the embodiments described above. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM). The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0196] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0197] The embodiments described above are merely illustrative of several implementation methods of this application, and while the descriptions are specific and detailed, they should not be construed as limiting the scope of this patent application. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of this application, and these all fall within the protection scope of this application. Therefore, the protection scope of this application should be determined by the appended claims.

Claims

1. A query optimization trace method of a database query optimizer, characterized by, The method includes: Obtain the user's database query instruction, and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction; Based on the tracking and adaptation resources, the optimizer is controlled to generate each candidate execution path, and based on each candidate execution path, the optimizer and the cost evaluation model are used to generate the optimizer's optimization record information. Based on the optimized record information, identify the execution path information of each elimination execution path and the elimination reason of each elimination execution path; Obtain the path selection tree template; the path selection tree template is a tree diagram based on the global structure of the database executed by the optimizer; By using a tree-shaped indentation strategy, the execution path information of each elimination execution path and the elimination reason of each elimination execution path are filled into the path selection tree template to obtain the path selection tree of the optimizer.

2. The method of claim 1, wherein, After obtaining the user's database query command, it also includes: Determine whether the database query instruction includes the optimizer's query keywords; If the database query instruction does not include the optimizer's query keywords, the optimizer is controlled to perform a query optimization task based on the database query instruction to obtain optimized query results. When the database query instruction includes the optimizer's query keywords, the marking information of each optimization tracking flag is adjusted.

3. The method of claim 1, wherein, The allocation of the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction includes: Obtain the resource allocation strategy and break it down into sub-resource allocation strategies for each resource allocation type; Based on the resource allocation strategy of each resource allocation type, resource configuration processing is performed on the resource configuration structure corresponding to each resource allocation type to obtain sub-tracking and adapting resources of each resource allocation type. All sub-tracking adaptation resources of all resource allocation types are used as the tracking adaptation resources of the optimizer.

4. The method of claim 1, wherein, Before controlling the optimizer to generate candidate execution paths based on the tracking and adaptation resources, the process further includes: Based on the database query command, the optimizer generates query optimization tasks and path monitoring tasks.

5. The method of claim 4, wherein, Based on the tracking and adaptation resources, the optimizer is controlled to generate candidate execution paths, including: Based on the tracking and adaptation resources, the optimizer is controlled to execute the query optimization task to obtain each execution path; Each candidate execution path is processed by path identification to obtain the candidate execution paths.

6. The method of claim 1, wherein, Before generating the optimizer's optimization record information based on each of the candidate execution paths, using the optimizer and the cost evaluation model, the method further includes: Obtain the initial path information for each candidate execution path; Based on the initial path information of each candidate execution path, the initial path information is processed by a cost evaluation model to obtain the cost evaluation result of each candidate execution path.

7. The method of claim 6, wherein, The step of generating optimization record information for the optimizer based on each of the candidate execution paths, through the optimizer and the cost evaluation model, includes: Based on the cost evaluation results of each candidate execution path, the optimizer generates the optimized execution results for each eliminated execution path and the corresponding query optimization task. The elimination execution paths and the optimization execution results corresponding to the query optimization tasks are used as the optimization record information of the optimizer.

8. The method according to claim 4, characterized in that, After generating the optimizer's optimization record information based on each of the candidate execution paths, through the optimizer and the cost evaluation model, the process further includes: Execute the path monitoring task to collect detailed path information for each of the elimination execution paths; Based on the detailed path information of each elimination execution path, an elimination path generation structure diagram is generated through the elimination path linked list.

9. The method of claim 7, wherein, After generating the optimizer's optimization record information based on each of the candidate execution paths, through the optimizer and the cost evaluation model, the process further includes: Based on the optimized execution results, identify the expected execution steps of the database query instruction and the expected cost information of the database query instruction; The expected execution steps and expected cost information of the database query instruction are used to generate a query optimization report corresponding to the database query instruction using an optimization report generation template.

10. The method of claim 9, wherein, The step of identifying the execution path information of each elimination execution path and the elimination reason of each elimination execution path based on the optimized record information includes: Iterate through each optimization tracking flag, identify the current marking information of each optimization tracking flag, and identify the optimization elimination process of the optimizer based on the current marking information of each optimization tracking flag; Based on the optimized elimination process, a structure graph is generated by traversing the elimination paths using a priority-order recursive strategy, and the elimination reasons and execution path information of each elimination execution path are identified.

11. A query optimization trace apparatus of a database query optimizer, characterized by, The device includes: The acquisition module is used to acquire the user's database query instruction and allocate the optimizer's tracking and adaptation resources based on the optimizer's query keywords in the database query instruction. The control module is used to control the optimizer to generate candidate execution paths based on the tracking and adaptation resources, and to generate optimization record information of the optimizer based on each candidate execution path through the optimizer and the cost evaluation model. The generation module is used to identify the execution path information of each elimination execution path and the elimination reason of each elimination execution path based on the optimization record information; obtain a path selection tree template; the path selection tree template is a tree diagram with the global structure of the database executed by the optimizer as the template; and fill the path selection tree template with the execution path information of each elimination execution path and the elimination reason of each elimination execution path through a tree indentation strategy to obtain the path selection tree of the optimizer.

12. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that, When the processor executes the computer program, it implements the method of any one of claims 1 to 10.

13. A computer readable storage medium having stored thereon a computer program, characterized in that When the computer program is executed by a processor, it implements the method of any one of claims 1 to 10.

14. A computer program product comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the method of any one of claims 1 to 10.

Images