A teaching performance driven database query plan selection method

Abstract

This invention discloses a database query plan selection method driven by teaching effectiveness, comprising: obtaining an exponential plan space based on SQL; and a processing method based on incremental TIPS, in the first... i It's my turn i The first candidate program space in the round, according to the first round i The first candidate program space of the round and the second i The first candidate program set in round -1 yielded the... i The first set of candidate plans in the round, based on the i The first candidate plan set is used to obtain the final first candidate plan set, or, based on the batch TIPS processing method, the second candidate plan space is obtained. The second candidate plan set is then updated according to the second candidate plan space and the execution plans in the second candidate plan set to obtain the final second candidate plan set. This invention can quickly obtain other candidate plans considered by the query optimizer during the decision-making process for the query the user wants to query.

Description

Technical Field

[0001] This invention belongs to the field of database query technology, specifically relating to a database query plan selection method driven by teaching effectiveness. Background Technology

[0002] With the rapid development of my country's database industry, the demand for database kernel development talent is growing dramatically. According to the China Academy of Information and Communications Technology, the compound annual growth rate of China's database market is projected to reach 11.99% by 2027, but senior database kernel development talent will account for less than one-tenth of the total workforce. Traditional database education cannot meet the needs of training database system developers, particularly in areas such as query optimization and execution engines, where significant gaps exist.

[0003] The query optimizer is a core component of a database system, responsible for transforming SQL (Structured Query Language) queries into efficient execution plans. During its operation, the query optimizer generates numerous candidate plans and selects the optimal solution through cost estimation. This complex decision-making process is of significant educational value for understanding the workings of the database kernel.

[0004] Existing commercial database systems (such as PostgreSQL and MySQL) query optimizers only show users the final query execution plan (QEP) they choose, and cannot show learners the alternative query plans (AQP) that the query optimizer considers during the decision-making process. This makes it difficult for learners to gain a deep understanding of the complete query optimization process. Summary of the Invention

[0005] To address the aforementioned technical problems in the existing technology, this invention provides a database query plan selection method driven by teaching effectiveness. The technical problem to be solved by this invention is achieved through the following technical solution:

[0006] This invention provides a database query plan selection method driven by teaching effectiveness, the database query plan selection method comprising:

[0007] The exponential plan space is obtained from the SQL, which includes the execution plan and multiple candidate plans;

[0008] The incremental TIPS-based processing method, in the first... i The first wheel is pruned according to a preset optimization method. i The first candidate program space in round -1 was obtained. i The first candidate program space in the round, according to the first round iThe first candidate program space of the round and the second i The first candidate plan set of round -1 yields the... i The first candidate program set for the round, and according to the... i The first candidate plan set in the round is used to obtain the final first candidate plan set. Alternatively, based on the batch TIPS processing method, the exponential plan space is pruned according to the preset optimization method to obtain the second candidate plan space. The second candidate plan set is updated according to the second candidate plan space and the execution plans in the second candidate plan set. The execution plans in the updated second candidate plan set are removed to obtain the final second candidate plan set.

[0009] The preset optimization method includes: pruning the preset plan space based on the number of join tables in the SQL and the preset plan space to obtain candidate plan spaces. In the incremental TIPS processing method, the preset plan space is the first... i The first candidate program space in round -1, the candidate program space is the first... i In the batch TIPS processing method, the first candidate plan space of the round is the default plan space as the exponential plan space, and the candidate plan space is the second candidate plan space.

[0010] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0011] This invention first obtains an exponential plan space based on the SQL, and then obtains the final first candidate plan set based on the incremental TIPS processing method, or the final second candidate plan set based on the batch TIPS processing method. In order to quickly filter out candidate plans with insufficient information from the huge AQPs space, both the incremental TIPS processing method and the batch TIPS processing method obtain the candidate plan space through a preset optimization method. This preset optimization method prunes the preset plan space according to the number of join tables in the SQL and the number of preset plan spaces, which can significantly reduce the number of candidate plans and improve the efficiency of subsequent selection. On this basis, other candidate plans considered by the query optimizer in the decision-making process can be quickly obtained for the query that the user wants to query, helping the user to deeply understand the complete process of query optimization.

[0012] The present invention will be further described in detail below with reference to the accompanying drawings and embodiments. Attached Figure Description

[0013] Figure 1 This is a flowchart of a database query plan selection method driven by teaching effectiveness provided in an embodiment of the present invention;

[0014] Figure 2 This is a schematic diagram illustrating the classification of candidate plans according to an embodiment of the present invention. Detailed Implementation

[0015] To further illustrate the technical means and effects adopted by the present invention to achieve the intended purpose, the following describes in detail a teaching effectiveness-driven database query plan selection method proposed according to the present invention, in conjunction with the accompanying drawings and specific embodiments.

[0016] The foregoing and other technical contents, features, and effects of the present invention will be clearly presented in the following detailed description of specific embodiments in conjunction with the accompanying drawings. Through the description of the specific embodiments, a more in-depth and concrete understanding can be gained of the technical means and effects adopted by the present invention to achieve its intended purpose. However, the accompanying drawings are for reference and illustration only and are not intended to limit the technical solutions of the present invention.

[0017] Currently, existing tools only display the query optimizer's final decision results, primarily targeting database users and administrators. Manual methods cannot traverse the vast plan space, making it difficult to obtain representative AQPs. These tools do not consider the specific needs of learning the database kernel, lack a mechanism for evaluating teaching value, and prevent learners from understanding the complete thought process of the query optimizer, hindering their grasp of the core principles of query optimization. Traditional teaching tools are mostly static displays, lacking dynamic interactive functions and the ability to dynamically adjust subsequent choices based on user feedback (such as ratings of the current plan). This prevents learners from deeply exploring and comparing different execution plans.

[0018] Based on this, the present invention provides a database query plan selection method driven by teaching effectiveness. Please refer to [link to relevant documentation]. Figure 1 , Figure 1 This is a flowchart illustrating a teaching effectiveness-driven database query plan selection method provided by an embodiment of the present invention. The teaching effectiveness-driven database query plan selection method provided by the present invention includes:

[0019] Step 1: Obtain the exponential plan space based on the SQL. The exponential plan space includes the execution plan and multiple candidate plans.

[0020] Specifically, users edit the content they need to query into SQL, and then input the SQL into the query system to obtain exponential plan space. This exponential planning space includes the execution plan and other candidate plans.

[0021] like Figure 2 As shown, this embodiment divides the candidate plans in the exponential plan space into eight categories according to their relationship with the execution plan, namely: AP I , AP II , AP III , APIV , AP V , AP VI , AP VII , AP VIII The specific classification method is to compare the structure, content, and cost between each candidate plan and the execution plan with the structure threshold, content threshold, and cost threshold, respectively. If the content between them is less than the content threshold, it is recorded as small; otherwise, it is recorded as large. If the cost between them is less than the cost threshold, it is recorded as small; otherwise, it is recorded as large. This completes the classification of candidate plans in the exponential plan space. For example, the structure threshold, content threshold, and cost threshold are all 0.5.

[0022] Step 2: Based on the incremental TIPS (Informative Plan Selection Problem) approach, in the... i The first wheel is pruned according to a preset optimization method. i The first candidate program space in round -1 was obtained. i The first candidate program space in the round, according to the first round i The first candidate program space of the round and the second i The first candidate plan set of round -1 yields the... i The first candidate program set for the round, and according to the... i The first candidate plan set in the round is used to obtain the final first candidate plan set. Alternatively, based on the batch TIPS processing method, the exponential plan space is pruned according to the preset optimization method to obtain the second candidate plan space. The second candidate plan set is updated according to the second candidate plan space and the execution plans in the second candidate plan set. The execution plans in the updated second candidate plan set are removed to obtain the final second candidate plan set.

[0023] The preset optimization method includes: pruning the preset plan space based on the number of join tables in the SQL and the preset plan space to obtain candidate plan spaces. In the incremental TIPS processing method, the preset plan space is the first... i The first candidate program space in round -1, the candidate program space is the first... i The first candidate program space in round 1 is the exponential program space. After multiple rounds of pruning, the first candidate program space in round 2 is obtained. i In the batch TIPS processing method, the first candidate plan space of the round is the default plan space as the exponential plan space, and the candidate plan space is the second candidate plan space.

[0024] Specifically, this embodiment obtains the user's required data from the exponential planning space using two methods.k The most informative candidate plans are presented to users using two methods: incremental TIPS and batch TIPS. Incremental TIPS allows users to iteratively view AQPs, while batch TIPS allows users to specify the number of AQPs they want to view at once. k However, due to the enormous AQP space, both incremental TIPS-based and batch TIPS-based processing methods require pruning the preset plan space based on the number of join tables in the SQL and the preset plan space size before execution. This preprocessing step quickly filters out candidate plans with insufficient information from the vast AQP space. Only after this preprocessing step can it be determined whether the incremental TIPS-based and batch TIPS-based processing methods can be executed subsequently.

[0025] In one specific embodiment, the preset plan space is pruned based on the number of join tables in the SQL and the number of preset plan spaces to obtain candidate plan spaces, including:

[0026] The algorithm determines the relationship between the number of join tables in the SQL statement and the preset number of join tables, and also determines the relationship between the number of all plans in the preset plan space and the preset number of plans. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the preset plan space is less than or equal to the preset number of plans, then a candidate plan space is obtained according to the first preprocessing method. If the number of join tables in the SQL statement is less than or equal to the preset number of join tables and the number of all plans in the preset plan space is greater than the preset number of plans, then a candidate plan space is obtained according to the second preprocessing method. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the preset plan space is greater than the preset number of plans, then a candidate plan space is obtained according to the third preprocessing method.

[0027] The first preprocessing method includes: adding candidate plans with a first structural distance of 0 between them and the execution plan in the preset plan space to the first sampling set; updating the second sampling set according to the remaining candidate plans in the preset plan space; and obtaining the candidate plan space based on the updated first sampling set and the updated second sampling set.

[0028] The second preprocessing method includes: constructing a first MEMO structure (MEMO structure is the plan forest tree in the Cascade query optimizer, full name Memorization) using all candidate plans in the preset plan space; and obtaining the candidate plan space based on the relationship between the second structural distance and the structural distance threshold between any candidate plan and the execution plan in the first set of trees of the first MEMO structure.

[0029] The third preprocessing method includes: adding candidate plans whose third structural distance to the execution plan is 0 in the preset plan space to the third sampling set; updating the fourth sampling set according to the remaining candidate plans in the preset plan space; and obtaining the third plan set according to the updated third sampling set and the updated fourth sampling set; constructing a second MEMO structure using all candidate plans in the third plan set; and obtaining the candidate plan space according to the relationship between the fourth structural distance and the structural distance threshold between any candidate plan and the execution plan in the second set of trees of the second MEMO structure.

[0030] Specifically, when the query involves a large number of joins (the number of join tables in the SQL exceeds the preset number of join tables), the plan space grows exponentially (not polynomially), making enumeration of all AQPs infeasible. Therefore, this embodiment uses a first preprocessing method for screening. This first preprocessing method is implemented based on feedback from user surveys. Through importance sampling, it prioritizes retaining candidate plans deemed highly informative in the survey feedback to ensure efficient subsequent selection and coverage of key categories. For example, for AP II Candidate plans of a certain type, whose structure is similar to the execution plan but with a large cost difference, are therefore considered the most informative. The second preprocessing method, based on the MEMO structure and the structural and cost differences between candidate and execution plans, can significantly reduce the number of candidate plans and improve subsequent selection efficiency. The third preprocessing method combines the first and second methods. In summary, SQL contains many join tables, and the preset plan space also contains many candidate plans. Therefore, this embodiment combines these two aspects to obtain the final candidate plan space, thereby quickly filtering out candidate plans with insufficient information from the vast AQP space.

[0031] For example, the preset number of join tables is 10, and the preset number of plans is 50,000.

[0032] In an optional embodiment, the first preprocessing method specifically includes:

[0033] Step A1: Traverse all candidate plans in the preset plan space, calculate the first structural distance between each candidate plan and the execution plan in the preset plan space, and filter out several candidate plans whose first structural distance with the execution plan is 0, and add all the filtered candidate plans to the first sampling set.

[0034] Specifically, the first structural distance between each candidate plan in the preset plan space and the current execution plan is calculated using the structural distance calculation formula. If the first structural distance between the candidate plan and the current execution plan is 0, it means that the candidate plan and the current execution plan have the same structure, and such a candidate plan belongs to... APII type.

[0035] Here, the formula for calculating structural distance is expressed as:

[0036] (1);

[0037] in, To execute the plan With candidate programs The structural distance between them To execute the plan tree structure, For candidate plans tree structure, for and Subtree kernels between them for and subtree kernels between for and The subtree cores between. Assume there are two plans with tree structures as follows: and They have the same subtree structure and ,but substructure yes For what is missing, the topological differences of the planned tree can be calculated through the subtree kernel. The subtree kernel quantifies the similarity by counting the number of identical subtrees and converts it into a distance metric.

[0038] Given two tree structures and The subtree kernels of both are defined as follows:

[0039] (2);

[0040] in, for The set of nodes, for The set of nodes, for The nodes in for The nodes in , for and The set of proper subtrees, It is used to determine the proper subtree Is it rooted in the node? The function, It is used to determine the proper subtree Is it rooted in the node? The function.

[0041] Step A2: Randomly sample from the remaining candidate plans in the preset plan space. n One candidate plan was added to the second sampling set. n It is an integer greater than 0.

[0042] Specifically, in step A1, several candidate plans were selected and added to the first sampling set. Then, the selected candidate plans were removed, and random sampling was performed from the remaining candidate plans in the preset plan space. n A candidate program, and this n One candidate plan is added to the second sampling set.

[0043] Step A3: Merge the updated first sample set and the updated second sample set to obtain the first plan set, and use the first plan set as the candidate plan space.

[0044] The first preprocessing method provided in this embodiment retains candidate plans with structural distances equal to the execution plan as alternative plans, ensuring full coverage of effective alternative plans.

[0045] In an optional embodiment, the second preprocessing method specifically includes:

[0046] Step B1: Construct a first MEMO structure using all candidate plans in the preset plan space. The first MEMO structure includes several first groups of trees, and each first group of trees includes several candidate plans with the same structure.

[0047] Specifically, for the MEMO structure, each row represents a group, and each group contains multiple expressions representing logical / physical operators. Expressions are connected by subgroup IDs to form a tree structure. Different expressions within the same group represent different operators but can achieve the same function. By traversing downwards from the expressions in the root group, a group tree can be obtained. A group tree represents a set of candidate plans with the same structure. This embodiment constructs the first MEMO structure based on this characteristic of the MEMO structure, specifically targeting... AP VI and AP VIII These two types of candidate programs (which differ greatly in structure and cost) are considered uninformative in database education.

[0048] Step B2: Randomly select a candidate plan from the first group of trees as the first judgment candidate plan, and calculate the second structural distance between the first judgment candidate plan and the execution plan.

[0049] Specifically, since the structure of any candidate plan and the execution plan are the same in each first group of trees, for each first group of trees, only one candidate plan is selected as the first judgment candidate plan, and the second structural distance between the first judgment candidate plan and the execution plan is calculated. The second structural distance is calculated using the structural distance calculation formula provided by formula (1).

[0050] Step B3: Determine the relationship between the second structural distance and the structural distance threshold. If the second structural distance is greater than or equal to the structural distance threshold, then continue to determine whether the first cost distance between each candidate plan and the execution plan in the first group of trees where the first judgment candidate plan is located is greater than or equal to the cost distance threshold. If the first cost distance is greater than or equal to the cost distance threshold, then remove the first judgment candidate plan. All the remaining candidate plans in the first group of trees form the second plan set, and the second plan set is used as the candidate plan space.

[0051] Specifically, the relationship between the second structural distance and the structural distance threshold is first determined. When the second structural distance is greater than or equal to the structural distance threshold, the first cost distance between each candidate plan and the execution plan in the first group of trees containing the first candidate plan is calculated according to the cost distance calculation formula. It is then determined whether the first cost distance is greater than or equal to the cost distance threshold. If so, the first candidate plan is removed. After removing all unqualified candidate plans in this way, the remaining candidate plans in all the first group of trees form the second plan set, which is used as the candidate plan space. Based on survey feedback, using the structural distance threshold and the cost distance threshold to jointly filter invalid candidate plans in the first MEMO structure significantly reduces the processing load.

[0052] Here, the formula for calculating cost distance is expressed as:

[0053] (3);

[0054] in, To execute the plan With candidate programs Cost distance between them To execute the plan The cost, For candidate plans The cost, The highest cost among all candidate plans. The minimum cost among all candidate plans.

[0055] For example, the structural distance threshold is 0.5, and the cost distance threshold is 0.5.

[0056] In an optional embodiment, the third preprocessing method specifically includes:

[0057] Step C1: Traverse all candidate plans in the preset plan space, calculate the third structure distance between each candidate plan and the execution plan in the preset plan space, filter out several candidate plans with a third structure distance of 0 with the execution plan, and add all the filtered candidate plans to the third sampling set.

[0058] Step C2: Randomly sample from the remaining candidate plans in the preset plan space. n One candidate plan is added to the fourth sampling set.

[0059] Step C3: Merge the updated third sampling set with the updated fourth sampling set to obtain the third plan set.

[0060] Step C4: Construct a second MEMO structure using all candidate plans in the third plan set. The second MEMO structure includes several second sets of trees, and each second set of trees includes several candidate plans with the same structure.

[0061] Step C5: Randomly select a candidate plan from the second group of trees as the second judgment candidate plan, and calculate the fourth structural distance between the second judgment candidate plan and the execution plan.

[0062] Step C6: Determine the relationship between the fourth structural distance and the structural distance threshold. If the fourth structural distance is greater than or equal to the structural distance threshold, then continue to determine the second cost distance between each candidate plan in the second group of trees where the second judgment candidate plan is located and the current execution plan. If the second cost distance is greater than or equal to the cost distance threshold, then remove the second judgment candidate plan. All the remaining candidate plans in the second group of trees form the fourth plan set, and the fourth plan set is used as the candidate plan space.

[0063] Based on the first, second, and third preprocessing methods provided above, this embodiment provides specific execution methods for incremental TIPS-based processing and batch TIPS-based processing, respectively. The processing method based on incremental TIPS or batch TIPS can be used to obtain the desired preprocessing parameters. k One candidate program.

[0064] In one specific embodiment, the incremental TIPS-based processing method, in the first... i The first wheel is pruned according to a preset optimization method. i The first candidate program space in round -1 was obtained. i The first candidate program space in the round, according to the first round i The first candidate program space of the round and the second i The first candidate plan set of round -1 yields the... iThe first candidate program set for the round, and according to the... i The first candidate program set of the rounds yields the final first candidate program set, which includes:

[0065] Step D1, in the i Round, based on the number of join tables in the SQL and the first round i -1 round of first candidate plan space pruning i The first candidate program space in round -1 yields the... i The first candidate program space for the round.

[0066] Specifically, in the first i In the first round, determine the relationship between the number of join tables in the SQL statement and the preset number of join tables, and determine the first... i The relationship between the first candidate plan space in round -1 and the preset number of plans; if the number of join tables in the SQL is greater than the preset number of join tables and the first... i If the first candidate plan space in round -1 is less than or equal to the preset number of plans, then the first preprocessing method is used to obtain the plan for the next round. i The first candidate plan space in the round, if the number of join tables in the SQL is less than or equal to the preset number of join tables and the first candidate plan space in the round, is selected. i If the first candidate plan space in round -1 is greater than the preset number of plans, then the second preprocessing method is used to obtain the... i The candidate plan space for the round, if the number of join tables in the SQL is greater than the preset number of join tables and the round number is greater than the preset number of join tables, is determined by the availability of the candidate plan space for the round. i If the first candidate plan space in round -1 is greater than the preset number of plans, then the third preprocessing method is used to obtain the... i The first candidate program space for the round.

[0067] Here, the first preprocessing method may specifically include: traversing the... i All candidate plans in the first candidate plan space of round -1 are calculated, and the... i In the first round of the candidate program space, each candidate program is compared with the... i -1 round execution plans, the first structural distance, and filtering out those with the first round. i For several candidate plans whose first structural distance between the execution plans in round -1 is 0, add all the selected candidate plans to the first sampling set; from the -1 round... i Random sampling from the remaining candidate plans in the first candidate plan space of round -1 n The first sample set is added to the second sample set; the updated first sample set and the updated second sample set are merged to obtain the first sample set, and the first sample set is used as the first sample set. i The first candidate program space in the round. The second preprocessing method may specifically include: utilizing the first round's candidate program space. iIn round -1, all candidate plans in the first candidate plan space are used to construct the first MEMO structure; arbitrarily select one candidate plan from the first set of trees as the first judgment candidate plan, and calculate the relationship between the first judgment candidate plan and the first... i -1 round execution plans are compared to the second structural distance; the relationship between the second structural distance and the structural distance threshold is determined. If the second structural distance is greater than or equal to the structural distance threshold, the first structural distance is compared to the first structural distance threshold. The first structural distance is then compared to the first structural distance threshold. The relationship between each candidate plan in the first group of trees containing the candidate plan and the first structural distance threshold is then determined. i If the first cost distance between the execution plans in round -1 is greater than or equal to the cost distance threshold, then the first candidate plan is removed, and all remaining candidate plans in the first group of trees form the second plan set, which is then used as the first set of plans. i The first candidate plan space in the round. The third preprocessing method may specifically include: traversing the first... i All candidate plans in the first candidate plan space of round -1 are calculated according to the structural distance calculation formula. i In the first round of the candidate program space, each candidate program is compared with the... i The third structural distance between the execution plans of -1 round and the selection of those with the 1st round. i -1 rounds of execution plans with a third structural distance of 0 are selected as candidate plans, and all selected candidate plans are added to the third sampling set; from the -1 round... i Random sampling from the remaining candidate plans in the first candidate plan space of round -1 n The candidate plans are added to the fourth sampling set; the updated third sampling set and the updated fourth sampling set are merged to obtain the third plan set; the second MEMO structure is constructed using all the candidate plans in the third plan set; a candidate plan is arbitrarily selected from the second set of trees as the second judgment candidate plan, and the comparison between the second judgment candidate plan and the first... i -1 round execution plans' fourth structural distance; determine the relationship between the fourth structural distance and the structural distance threshold. If the fourth structural distance is greater than or equal to the structural distance threshold, continue to determine the second structural distance. For each candidate plan in the second group of trees containing the candidate plan, determine its relationship with the first structural distance. i If the second cost distance between the execution plans in round -1 is greater than or equal to the cost distance threshold, then the candidate plan for the second judgment is removed. All remaining candidate plans in the second group of trees form the fourth plan set, and the fourth plan set is used as the first... i The first candidate program space for the round.

[0068] Step D2, based on the first iThe candidate calculation plan is obtained by combining the candidate screening plan of round -1 with the multidimensional composite distance of each candidate plan in the high-information category, and then based on the candidate calculation plan and the first round... i -1 round of candidate screening plan adjustment update i The position of the execution plan in round -1, and the position updated to the [number]th [round]. i The execution plan for each round is based on each preset candidate plan and the first round. i The refined distance between the execution plans of each round, selecting the first round from all preset candidate plans. i The candidate selection program in the first round and joining the second round i The first candidate plan set in round -1 yields the... i The first candidate plan set in the first round is used to obtain the final first candidate plan set based on the preset selection, where the preset candidate plan is the first round. i The first candidate program space of the round and the second i The difference set of the first candidate plans in round -1.

[0069] In this embodiment, the first i The -1 round of candidate selection is planned for the 1st round. i The candidate plans selected in the final round of screening (-1) will be output in the... i After the final selection of candidate plans in round -1, users will target the... i Feedback is provided on the candidate screening plan in round -1. If the feedback result is a preference, it is recorded as r=1; if the feedback result is a dislike, it is recorded as r=0.

[0070] Step D2.1, calculate the first... i -1 round of candidate screening program and the multidimensional composite distance between each candidate program in the high-information category.

[0071] Specifically, when studying execution plans in conjunction with other methods, highly informative categories are those that enable learners to gain a deeper understanding of candidate plans for database query optimization. This highly informational category AP This was obtained based on survey feedback, and the high-information category was calculated using a multi-dimensional composite distance calculation formula. AP Each candidate plan in the process is related to the first i -1 round of candidate screening program multidimensional composite distance.

[0072] The formula for calculating the multidimensional composite distance is expressed as follows:

[0073] (4);

[0074] in, For the plan With the plan The multidimensional composite distance between them, given a plan With the plan , The combined distance between the two in three dimensions can be calculated. In step D2.1, the plan... and plans The first i -1 round of candidate screening plan and a candidate plan in a high-information category, For the plan With the plan The structural distance between them is calculated using formula (1). For the plan With the plan Distance between nodes with different content For the plan With the plan Cost distance between them and All are weights. , .

[0075] In this embodiment, structural distance The subtree kernel used only compares the differences between tree structures, without considering the differences in node content. Therefore, the definition... The edit distance is used to obtain the node content difference distance between two plans. The formula for calculating the node content difference distance is as follows:

[0076] (5);

[0077] in, For the plan The string, For the plan The string, To make the string Convert to string The minimum number of single-character editing operations required.

[0078] Step D2.2, Select the... i The candidate plan with the smallest multidimensional composite distance in the -1 round of candidate screening is selected as the candidate calculation plan.

[0079] Specifically, from the high-information category AP Selected from the first i The candidate plan with the smallest multidimensional composite distance among the candidate plans in round -1 is selected as the candidate calculation plan.

[0080] Step D2.3: Based on the candidate calculation plan and the... iThe candidate screening plan for round -1 has been adjusted.

[0081] Here, the formula for calculating the adjustment amount is expressed as:

[0082] (6);

[0083] in, To adjust the amount, As candidate computation plans, For the first i -1 round of candidate screening plan, For predefined parameters, For example, take 0.05.

[0084] Step D2.4, if for the first i User feedback on the candidate selection plan in round -1 is a preference (r=1), then according to the... i -1 round of execution plan and adjustment amount update the first round i The position of the execution plan in round -1, to obtain the first round. i The execution plan of the round, that is , For the first i The execution plan for the first round, if for the... i If the user feedback for the candidate selection plan in round -1 is negative (r=0), then according to the... i -1 round of execution plan and adjustment amount, update the first round i The position of the execution plan in round -1, to obtain the first round. i The execution plan of the round, that is .

[0085] This embodiment is based on the coordinate system origin update rule and adjusts the position of the execution plan in real time based on user feedback r, realizing personalized exploration for users.

[0086] Step D2.5: For each preset candidate plan, calculate the relationship between the preset candidate plan and the first... i The refined distance between the execution plans of each round is used to select the preset candidate plan corresponding to the maximum refined distance as the first round. i The candidate selection program of the round was added to the third round. i The first candidate plan set in round -1 yields the... i The first set of candidate plans for the round.

[0087] Specifically, take the first i The first candidate program space of the round and the second i The difference set of the first candidate plans in round -1 is the set of candidate plans in this difference set, which is the preset candidate plan. The minimum refining distance is calculated using the formula to determine the difference between each preset candidate plan and the first candidate plan.i The refinement distance between the execution plans of each round is calculated, and then the preset candidate plan corresponding to the maximum refinement distance is selected as the first round. i The candidate selection plan for the first round, and the resulting candidates... i The candidate selection program of the round was added to the third round. i -1 round's first candidate plan set, thus obtaining the th round i The first set of candidate plans for the round.

[0088] Here, the formula for calculating the minimum refining distance is expressed as:

[0089] (7);

[0090] in, For the plan With the plan The refining distance between them, in step D2.5, is planned. and plans These are the preset candidate plans and the first i The execution plan of the round, To balance correlation and distance, the default value is 0.5. For the plan With the plan The multidimensional composite distance between them is calculated using formula (4), and its role in refining distance is to prevent the two from being too similar. Multidimensional composite distance This approach can only prevent selected candidate plans from being too similar to previously viewed candidate plans, but it cannot distinguish between informative and non-informative plans. Therefore, a relevance measurement function was proposed. This correlation measurement function To evaluate the value of a given plan so that informative plans can achieve higher relevance scores, the relevance metric function is expressed as:

[0091] (8);

[0092] in, For the plan The correlation measurement function, t Pick m or n , for and Structured distance, for and Content distance, for and The cost of distance.

[0093] Step D2.6: Based on the preset selection, according to the... i The first candidate plan set of the rounds is used to obtain the final first candidate plan set.

[0094] Specifically, the default selection is the user's choice. Therefore, in step D2.6, the user can choose to continue iterating or terminate. If the user chooses to continue iterating or terminate, the process jumps to step D1 to continue execution until the user chooses to terminate. The set of first candidate plans obtained in the last round at the time of termination is the final set of first candidate plans. If the user chooses to terminate, the set of first candidate plans obtained in the second round is the final set of first candidate plans. i The first set of candidate plans in each round is the final set of first candidate plans. The final set of first candidate plans includes... k The candidate plans are the ones that users need to view.

[0095] In a specific embodiment, the batch TIPS-based processing method prunes the exponential plan space according to a preset optimization method to obtain a second candidate plan space, updates the second candidate plan set based on the second candidate plan space and the execution plans in the second candidate plan set, and removes the execution plans from the updated second candidate plan set to obtain the final second candidate plan set, including:

[0096] Step E1: Prune the exponential plan space according to the number of join tables and the number of exponential plan spaces in the SQL to obtain the second candidate plan space.

[0097] Specifically, the relationship between the number of join tables in the SQL statement and the preset number of join tables is determined, as is the relationship between the number of all plans in the exponential plan space and the preset number of plans. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the exponential plan space is less than or equal to the preset number of plans, then a second candidate plan space is obtained according to the first preprocessing method. If the number of join tables in the SQL statement is less than or equal to the preset number of join tables and the number of all plans in the exponential plan space is greater than the preset number of plans, then a second candidate plan space is obtained according to the second preprocessing method. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the exponential plan space is greater than the preset number of plans, then a second candidate plan space is obtained according to the third preprocessing method.

[0098] Here, the first preprocessing method may specifically include: adding candidate plans with a first structural distance of 0 between them and the execution plan in the exponential plan space to a first sampling set; updating the second sampling set based on the remaining candidate plans in the exponential plan space; and obtaining a second candidate plan space based on the updated first sampling set and the updated second sampling set. The second preprocessing method may specifically include: constructing a first MEMO structure using all candidate plans in the exponential plan space; and obtaining a second candidate plan space based on the relationship between the second structural distance and a structural distance threshold between any candidate plan and the execution plan in the first set of trees of the first MEMO structure. The third preprocessing method may specifically include: adding candidate plans with a third structural distance of 0 between them and the execution plan in the exponential plan space to a third sampling set; updating the fourth sampling set based on the remaining candidate plans in the exponential plan space; and obtaining a third plan set based on the updated third sampling set and the updated fourth sampling set; constructing a second MEMO structure using all candidate plans in the third plan set; and obtaining a second candidate plan space based on the relationship between the fourth structural distance and a structural distance threshold between any candidate plan and the execution plan in the second set of trees of the second MEMO structure.

[0099] Step E2: Traverse each candidate plan in the second candidate plan space and calculate the distance between each candidate plan and the execution plan in the second candidate plan set. The initialized second candidate plan set includes execution plans.

[0100] Specifically, the max heap is initialized, and the set of second candidate plans is initialized. , The execution plan is then executed. Then, each candidate plan in the second candidate plan space is traversed, and the refined distance between each candidate plan and the execution plan is calculated using formula (7).

[0101] Step E3: Push the refined distance between the candidate plan and the execution plan, as well as the tuple composed of the candidate plans, into the max heap.

[0102] Specifically, the second candidate program space in the second candidate program space j Each candidate plan is denoted as , No. j The refined distance between each candidate plan and the execution plan is denoted as . Therefore, the tuple they form is represented as , tuple Pushing into a max-heap is based on the property that the top element of a max-heap has the largest refining distance.

[0103] Step E4: If the max-heap is not empty, pop the top tuple from the max-heap and remove the popped tuple from the max-heap.

[0104] Here, initialization , , This is a set of tuple records used to record the tuples that have been traversed. This is the set of tuples with the largest distance, used to record the tuples with the largest refined distance. Only one tuple is recorded at most, namely the tuple with the largest current refining distance. It is an empty set. It is none.

[0105] Step E5: Update the second candidate plan set based on the relationship between the refining distance in the top tuple of the max heap and the maximum refining distance in the set of maximum distance tuples.

[0106] Here, the second candidate plan set is used to store the candidate plans that the user ultimately needs.

[0107] Step E5.1: Determine whether the refined distance of the top tuple in the max-heap is less than the maximum refined distance in the set of maximum distance tuples. If yes, jump to step E4 and re-extract the top tuple in the current max-heap. If no, jump to step E5.2.

[0108] Step E5.2: Update the second candidate plan set based on the refined distance between the candidate plans in the currently selected top tuple and the execution plans in the second candidate plan set.

[0109] Step E5.21: Determine whether the refinement distance between the candidate plan in the currently selected top tuple of the heap and the execution plan in the second candidate plan set is equal to the refinement distance in the currently selected top tuple of the heap. If yes, proceed directly to step E5.22. If no, update the refinement distance in the currently selected top tuple of the heap based on the refinement distance between the candidate plan in the currently selected top tuple of the heap and the execution plan in the second candidate plan set, and then proceed to step E5.22.

[0110] Specifically, firstly, the refinement distance between the candidate plans in the previously selected top tuple and the execution plans in the second candidate plan set is calculated using formula (7), and this refinement distance is recorded as the pre-selected refinement distance. Then, it is determined whether the pre-selected refinement distance is equal to the refinement distance in the currently selected top tuple. If so, step E5.22 is executed directly; otherwise, the refinement distance in the currently selected top tuple needs to be updated to the pre-selected refinement distance.

[0111] Step E5.22: Determine whether the refined distance in the currently selected top tuple of the heap is greater than the maximum refined distance in the maximum distance tuple set. If yes, update the maximum distance tuple set to the currently selected top tuple of the heap, and then execute step E5.23. If no, directly execute step E5.23.

[0112] Step E5.23: Add the currently selected top tuple of the heap to the tuple record set.

[0113] Step E5.24: Add the candidate plans from the maximum distance tuple set obtained in step E5.22 to the second candidate plan set to update the second candidate plan set, and push the difference between the tuple record set and the maximum distance tuple set back into the max heap.

[0114] Step E6: Determine whether the number of plans in the updated second candidate plan set is equal to... k +1. If yes, proceed to step E7; otherwise, jump to step E4.

[0115] Specifically, k The number of candidate plans to be filtered, as specified by the user; therefore, when the number of candidate plans in the second candidate plan set reaches... k Therefore, when the number of plans in the updated second candidate plan set is equal to k When +1 is added, the conditions for the second candidate plan set to be met have been satisfied. k If there is one candidate plan and the other is the execution plan, then terminate; otherwise, continue to step E4.

[0116] Step E7: Remove the execution plans from the updated second candidate plan set to obtain the final second candidate plan set.

[0117] A max-heap is a complete binary tree in which the value of each node is greater than or equal to the value of its child nodes. This means that the root node is the largest element in the max-heap. Therefore, this embodiment uses a max-heap to filter candidate plans with very small refining distances and only updates the refining distance of the tuple at the top of the heap when it becomes invalid, thus avoiding unnecessary computation.

[0118] The database query plan selection method provided by this invention enables a teaching system that can demonstrate the complete decision-making process of the query optimizer, including the generation, evaluation, and selection of candidate execution plans, establishing a teaching effectiveness evaluation mechanism, efficiently selecting the most valuable candidate plans for display, supporting the simultaneous display and in-depth comparative analysis of multiple candidate plans, and dynamically adjusting the complexity and focus of teaching content based on learners' knowledge background and learning objectives. Specifically, this invention aims to design a method that maximizes information content through automatic selection... kThe study identifies representative candidate plans and introduces a quantitative method for "plan informationality." It combines user interests (such as structural similarity, cost differences, and cost differences) to enhance educational value and supports two modes: incremental TIPS processing and batch TIPS processing, to adapt to the personalized needs and learning progress of different learners.

[0119] The database query plan selection method provided by this invention selects information-rich alternative candidate plans for a given SQL query by quantifying the information content of the plan and developing an efficient algorithm, thereby helping learners understand the decision-making process of the query optimizer.

[0120] This invention addresses the bottleneck of AQP exploration in database education by quantifying information (combining inter-plan differences and correlations), employing pruning strategies, and using efficient algorithms. It can improve learners' comprehension efficiency and academic performance, providing a practical tool for database education.

[0121] The database query plan selection method provided by this invention can display the complete process of query optimization, including multiple candidate schemes and selection criteria, overcoming the limitations of traditional methods that only display the final execution plan and have incomplete information; based on teaching value assessment, the most representative content can be selected to improve learning efficiency, solving the problem that traditional methods have fixed content and cannot adapt to different learning needs; it supports in-depth exploration and comparative learning, improving the shortcomings of traditional methods that are only suitable for static display and have poor interactivity.

[0122] It should be noted that the terms "first," "second," etc., are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. The embodiments described in the above exemplary embodiments do not represent all embodiments consistent with the present invention. Rather, they are merely examples of apparatuses and methods consistent with some aspects of the present invention.

[0123] Although the invention has been described herein in conjunction with various embodiments, those skilled in the art will understand and implement other variations of the disclosed embodiments by reviewing the accompanying drawings and the disclosure in carrying out the claimed invention. In the description of the invention, the word "comprising" does not exclude other components or steps, "a" or "an" does not exclude a plurality, and "a plurality" means two or more, unless otherwise explicitly specified. Furthermore, while different embodiments may describe certain measures, this does not mean that these measures cannot be combined to produce good results.

[0124] The above description, in conjunction with specific preferred embodiments, provides a further detailed explanation of the present invention. It should not be construed that the specific implementation of the present invention is limited to these descriptions. For those skilled in the art, various simple deductions or substitutions can be made without departing from the concept of the present invention, and all such modifications and substitutions should be considered within the scope of protection of the present invention.

Claims

1. A database query plan selection method driven by teaching effectiveness, characterized in that, The database query plan selection method includes: The exponential plan space is obtained from the SQL, which includes the execution plan and multiple candidate plans; The incremental TIPS-based processing method, in the first... i The first wheel is pruned according to a preset optimization method. i The first candidate program space in round -1 was obtained. i The first candidate program space in the round, according to the first round i The first candidate program space of the round and the second i The first candidate plan set of round -1 yields the... i The first candidate program set for the round, and according to the... i The first candidate plan set in the round is used to obtain the final first candidate plan set. Alternatively, based on the batch TIPS processing method, the exponential plan space is pruned according to the preset optimization method to obtain the second candidate plan space. The second candidate plan set is updated according to the second candidate plan space and the execution plans in the second candidate plan set. The execution plans in the updated second candidate plan set are removed to obtain the final second candidate plan set. The preset optimization method includes: pruning the preset plan space based on the number of join tables in the SQL and the preset plan space to obtain candidate plan spaces. In the incremental TIPS processing method, the preset plan space is the first... i The first candidate program space in round -1, the candidate program space is the first... i In the batch TIPS processing method, the first candidate plan space of the round is the default plan space as the exponential plan space, and the candidate plan space is the second candidate plan space. Based on the number of join tables in the SQL and the amount of preset plan space, the preset plan space is trimmed to obtain candidate plan spaces, including: The algorithm determines the relationship between the number of join tables in the SQL statement and the preset number of join tables, and also determines the relationship between the number of all plans in the preset plan space and the preset number of plans. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the preset plan space is less than or equal to the preset number of plans, then a candidate plan space is obtained according to the first preprocessing method. If the number of join tables in the SQL statement is less than or equal to the preset number of join tables and the number of all plans in the preset plan space is greater than the preset number of plans, then a candidate plan space is obtained according to the second preprocessing method. If the number of join tables in the SQL statement is greater than the preset number of join tables and the number of all plans in the preset plan space is greater than the preset number of plans, then a candidate plan space is obtained according to the third preprocessing method. The first preprocessing method includes: adding candidate plans with a first structural distance of 0 between them and the execution plan in the preset plan space to the first sampling set; updating the second sampling set according to the remaining candidate plans in the preset plan space; and obtaining the candidate plan space according to the updated first sampling set and the updated second sampling set. The second preprocessing method includes: constructing a first MEMO structure using all candidate plans in a preset plan space, and obtaining a candidate plan space based on the relationship between a second structural distance and a structural distance threshold between any candidate plan and an execution plan in the first set of trees of the first MEMO structure; The third preprocessing method includes: adding candidate plans with a third structural distance of 0 between them and the execution plan in the preset plan space to the third sampling set; updating the fourth sampling set according to the remaining candidate plans in the preset plan space; and obtaining the third plan set based on the updated third sampling set and the updated fourth sampling set; constructing a second MEMO structure using all the candidate plans in the third plan set; and obtaining the candidate plan space based on the relationship between the fourth structural distance between any candidate plan and the execution plan in the second set of trees of the second MEMO structure and the structural distance threshold. The incremental TIPS-based processing method, in the first... i The first wheel is pruned according to a preset optimization method. i The first candidate program space in round -1 was obtained. i The first candidate program space in the round, according to the first round i The first candidate program space of the round and the second i The first candidate plan set of round -1 yields the... i The first candidate program set for the round, and according to the... i The first candidate program set of the rounds yields the final first candidate program set, which includes: Step D1, in the i Round, based on the number of join tables in the SQL and the first round i -1 round of first candidate plan space pruning i The first candidate program space in round -1 yields the... i The first candidate program space for the round; Step D2, based on the first i The candidate calculation plan is obtained by combining the candidate screening plan of round -1 with the multidimensional composite distance of each candidate plan in the high-information category, and then based on the candidate calculation plan and the first round... i -1 round of candidate screening plan adjustment update number i The position of the execution plan in round -1, and the position updated to the [number]th [round]. i The execution plan for each round is based on each preset candidate plan and the first round. i The refined distance between the execution plans of each round, selecting the first from all preset candidate plans. i The candidate selection program in the first round and joining the second round i The first candidate plan set in round -1 yields the... i The first candidate plan set in the first round is used to obtain the final first candidate plan set based on the preset selection, where the preset candidate plan is the first round. i The first candidate program space of the round and the second i The difference set of the first candidate plans in round -1; Step D2 includes: Step D2.1 Calculate the first i -1 round of candidate screening plans and the multidimensional composite distance between each candidate plan in the high-information category; Step D2.2, Select and match the first i The candidate plan with the smallest multidimensional composite distance in the -1 round of candidate screening is selected as the candidate calculation plan; Step D2.3: Based on the candidate calculation plan and the... i The candidate screening plan for round -1 has been adjusted. Step D2.4, if for the first i If user feedback on the candidate selection plan in round -1 is positive, then according to the... i -1 round of execution plan and adjustment amount update the first round i The position of the execution plan in round -1, to obtain the first round. i The execution plan for the first round, if for the... i If user feedback for the -1 round of candidate screening is negative, then according to the... i -1 round of execution plan and adjustment amount, update the first round i The position of the execution plan in round -1, to obtain the first round. i The execution plan for the round; Step D2.5: For each preset candidate plan, calculate the distance between the preset candidate plan and the first candidate plan according to the minimum refining distance formula. i The refinement distance between the execution plans of each round is used to select the preset candidate plan corresponding to the maximum refinement distance as the first round. i The candidate selection program of the round was added to the third round. i The first candidate plan set in round -1 yields the... i The first set of candidate plans for the round; Step D2.6: Based on the preset selection, according to the... i The first candidate plan set of the rounds is used to obtain the final first candidate plan set.

2. The database query plan selection method according to claim 1, characterized in that, The first preprocessing method specifically includes: Traverse all candidate plans in the preset plan space, calculate the first structural distance between each candidate plan and the execution plan in the preset plan space, and filter out several candidate plans whose first structural distance with the execution plan is 0, and add all the filtered candidate plans to the first sampling set; Randomly sample from the remaining candidate plans in the preset plan space. n One candidate plan is added to the second sampling set; The updated first sample set and the updated second sample set are merged to obtain the first plan set, and the first plan set is used as the candidate plan space.

3. The database query plan selection method according to claim 1, characterized in that, The second preprocessing method specifically includes: Construct a first MEMO structure using all candidate plans in the preset plan space. The first MEMO structure includes several first sets of trees, and each first set of trees includes several candidate plans with the same structure. Select any candidate plan from the first set of trees as the first judgment candidate plan, and calculate the second structural distance between the first judgment candidate plan and the execution plan; Determine the relationship between the second structural distance and the structural distance threshold. If the second structural distance is greater than or equal to the structural distance threshold, then continue to determine whether the first cost distance between each candidate plan and the execution plan in the first group of trees where the first judgment candidate plan is located is greater than or equal to the cost distance threshold. If the first cost distance is greater than or equal to the cost distance threshold, then remove the first judgment candidate plan. All the remaining candidate plans in the first group of trees form the second plan set, and the second plan set is used as the candidate plan space.

4. The database query plan selection method according to claim 1, characterized in that, The third preprocessing method specifically includes: Traverse all candidate plans in the preset plan space, calculate the third structure distance between each candidate plan and the execution plan in the preset plan space, filter out several candidate plans with a third structure distance of 0 with the execution plan, and add all the filtered candidate plans to the third sampling set; Randomly sample from the remaining candidate plans in the preset plan space. n One candidate plan is added to the fourth sampling set; The updated third sample set is merged with the updated fourth sample set to obtain the third plan set; A second MEMO structure is constructed using all candidate plans in the third plan set. The second MEMO structure includes several second sets of trees, and each second set of trees includes several candidate plans with the same structure. Select any candidate plan from the second set of trees as the second judgment candidate plan, and calculate the fourth structural distance between the second judgment candidate plan and the execution plan; Determine the relationship between the fourth structural distance and the structural distance threshold. If the fourth structural distance is greater than or equal to the structural distance threshold, then continue to determine the second cost distance between each candidate plan in the second group of trees where the second judgment candidate plan is located and the current execution plan. If the second cost distance is greater than or equal to the cost distance threshold, then remove the second judgment candidate plan. All the remaining candidate plans in the second group of trees form the fourth plan set, and the fourth plan set is used as the candidate plan space.

5. The database query plan selection method according to claim 1, characterized in that, The batch TIPS-based processing method prunes the exponential plan space according to a preset optimization method to obtain a second candidate plan space. The second candidate plan set is then updated based on the second candidate plan space and the execution plans in the second candidate plan set. Finally, the execution plans in the updated second candidate plan set are removed to obtain the final second candidate plan set, which includes: Step E1: Prune the exponential plan space based on the number of join tables and the number of exponential plan spaces in the SQL to obtain the second candidate plan space; Step E2: Traverse each candidate plan in the second candidate plan space and calculate the refined distance between each candidate plan and the execution plan in the second candidate plan set. The initialized second candidate plan set includes execution plans. Step E3: Push the refined distance between the candidate plan and the execution plan, as well as the tuple composed of the candidate plans, into the max heap; Step E4: If the max heap is not empty, pop the top tuple from the max heap. Step E5: Update the second candidate plan set based on the relationship between the refining distance in the tuple at the top of the max heap and the maximum refining distance in the set of tuples with the maximum distance. Step E6: Determine whether the number of plans in the updated second candidate plan set is equal to... k +1, if yes, proceed to step E7; otherwise, jump to step E4. Step E7: Remove the execution plans from the updated second candidate plan set to obtain the final second candidate plan set.

6. The database query plan selection method according to claim 5, characterized in that, Step E5 includes: Step E5.1: Determine whether the refined distance in the top tuple of the max heap is less than the maximum refined distance in the set of maximum distance tuples. If yes, jump to step E4; otherwise, jump to step E5.

2. Step E5.2: Update the second candidate plan set based on the refined distance between the candidate plans in the currently selected top tuple and the execution plans in the second candidate plan set.

7. The database query plan selection method according to claim 6, characterized in that, Step E5.2 includes: Step E5.21: Determine whether the refinement distance between the candidate plan in the currently selected top tuple of the heap and the execution plan in the second candidate plan set is equal to the refinement distance in the currently selected top tuple of the heap. If yes, proceed directly to step E5.

22. If no, update the refinement distance in the currently selected top tuple of the heap based on the refinement distance between the candidate plan in the currently selected top tuple of the heap and the execution plan in the second candidate plan set, and then proceed to step E5.

22. Step E5.22: Determine whether the refined distance in the currently selected top heap tuple is greater than the maximum refined distance in the maximum distance tuple set. If yes, update the maximum distance tuple set to the currently selected top heap tuple, and then execute step E5.

23. If no, directly execute step E5.

23. Step E5.23: Add the currently selected top tuple of the heap to the tuple record set; Step E5.24: Add the candidate plans from the maximum distance tuple set obtained in step E5.22 to the second candidate plan set to update the second candidate plan set, and push the difference between the tuple record set and the maximum distance tuple set back into the max heap.

Images