Query statement optimization method and related device
By optimizing the grouping aggregation clauses in the database query statement and rearranging their grouping order to be consistent with the index order of the data table, the high-cost execution plan problem caused by the inconsistent grouping aggregation clauses and index order is solved, and the query performance is significantly improved.
Patent Information
- Application Number
- CN202411783399.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-12-05
- Publication Date
- 2025-03-18
AI Technical Summary
In the database, when the optimizer processes grouped query statements, if the grouped key value order of the grouped aggregate sub-statement is inconsistent with the index key value order, the generated execution plan cost is high, affecting the query performance.
By obtaining the pending query statement, determining whether it contains grouped aggregation sub-statements, and optimizing to rearrange the key-value order of the grouped aggregation sub-statements and query statements according to the consistency of the order of the data table indexes according to the order of the key-value order of the grouped aggregation sub-statements, so that they are consistent with the index order, thereby generating a lower cost execution plan.
By correcting the grouping order of the grouping aggregation clause to be consistent with the index order of the data table to be queried, the sort nodes in the generated grouping aggregation path are eliminated, the execution cost is reduced, and the performance of the query statement is significantly improved.
Smart Images
Figure CN119646032A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a query statement optimization method and related devices. Background Art
[0002] When optimizing a query statement, the optimizer can analyze the query statement, generate multiple execution plans, and select the execution plan with the lowest cost as the final execution plan. Currently, when the optimizer processes a grouped query statement, if the key value order of the grouped aggregation sub-statement of the grouped query statement is inconsistent with the key value order of the index, the cost of the execution plan generated by the optimizer is still high. Summary of the invention
[0003] The embodiments of the present application provide a query statement optimization method and related devices, which can effectively reduce the cost of execution plan and improve query performance.
[0004] In a first aspect, an embodiment of the present application provides a query statement optimization method, comprising:
[0005] Get the query statement to be processed;
[0006] In the case where the query statement includes a grouping and aggregation sub-statement, determining whether the query statement is an optimizable query statement according to the grouping and aggregation sub-statement and the query statement;
[0007] If yes, optimize the group-by-group aggregation sub-statement to obtain a target query statement, so that the grouping order of the group-by-group aggregation sub-statement is consistent with the index order of the data table to be queried;
[0008] Execute the execution plan generated based on the target query statement.
[0009] In some embodiments, the optimizing the grouping and aggregation sub-statements to obtain the target query statement includes:
[0010] According to the index sequence of the data table, the key values of the grouping and aggregation in the grouping and aggregation sub-statement are rearranged to obtain the target query statement.
[0011] In some embodiments, the rearranging the grouping and aggregation key values in the grouping and aggregation sub-statement according to the index order of the data table to obtain the target query statement includes:
[0012] Obtain the key value order of the grouping aggregation in the grouping aggregation sub-statement, and the key value order of the index of the data table;
[0013] The key value order of the grouping and aggregation sub-statements is modified to the key value order of the index to obtain the target query statement.
[0014] In some embodiments, determining whether the query statement is a query statement to be optimized according to the grouping and aggregation sub-statements and the query statement includes:
[0015] Obtaining the data table corresponding to the query statement;
[0016] Determine whether the key values of the grouping and aggregation sub-statements belong to the same data table;
[0017] If yes, determining whether the query statement is a query statement to be optimized according to the key value of the data table and the grouping and aggregation sub-statement;
[0018] If not, it is determined that the query statement is not a query statement to be optimized.
[0019] In some embodiments, determining whether the query statement is a query statement to be optimized according to the key value of the data table and the grouping and aggregation sub-statement includes:
[0020] Determine whether the data table has an index and whether the type of the index is the target index type;
[0021] If so, determining whether the query statement is a query statement to be optimized according to the key value of the grouping and aggregation sub-statement and the key value of the index;
[0022] If not, it is determined that the query statement is not a query statement to be optimized.
[0023] In some embodiments, determining whether the query statement is a query statement to be optimized according to the key value of the grouping and aggregation sub-statement and the key value of the index includes:
[0024] Determine whether the key value of the grouped aggregation is a subset of the key value of the index, and whether the order of the key values of the grouped aggregation is inconsistent with the order of the key values of the corresponding key values in the index;
[0025] If yes, determining that the query statement is a query statement to be optimized;
[0026] If not, it is determined that the query statement is not a query statement to be optimized.
[0027] In some embodiments, obtaining the key value sequence of the index of the data table includes:
[0028] Obtaining metadata for the index;
[0029] Reading an ordered list of key values of the index from the metadata;
[0030] The key value order of the index is determined based on the key value order list.
[0031] In a second aspect, an embodiment of the present application provides a statement optimization device, including:
[0032] The acquisition module is used to obtain the query statement to be processed.
[0033] A processing module is used to determine whether the query statement is an optimizable query statement based on the grouping and aggregation sub-statement and the query statement when the query statement includes a grouping and aggregation sub-statement.
[0034] The optimization module is used to optimize the grouping and aggregation sub-statements to obtain a target query statement so that the grouping order of the grouping and aggregation sub-statements is consistent with the index order of the data table to be queried.
[0035] An execution module is used to execute an execution plan generated based on the target query statement.
[0036] In a third aspect, the present application provides an electronic device, including: a memory and a processor;
[0037] The memory is used to store computer instructions; the processor is used to execute the computer instructions stored in the memory to implement any method in the first aspect.
[0038] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, and the computer program is executed by a processor to implement any one of the methods in the first aspect.
[0039] In a fifth aspect, the present application provides a computer program product, comprising a computer program, which implements any one of the methods in the first aspect when executed by a processor.
[0040] The query statement optimization method and related device provided in the embodiment of the present application obtain the query statement to be processed; when the query statement includes a grouping aggregation sub-statement, determine whether the query statement is a query statement that can be optimized based on the grouping aggregation sub-statement and the query statement; if so, optimize the grouping aggregation sub-statement to obtain a target query statement so that the grouping order of the grouping aggregation sub-statement is consistent with the index order of the data table to be queried; and execute the execution plan generated based on the target query statement. The above method can eliminate the sort node in the generated grouping aggregation path by correcting the grouping order of the grouping aggregation sub-statement to be consistent with the index order of the data table to be queried, thereby reducing the execution cost and greatly improving the performance of the query statement. BRIEF DESCRIPTION OF THE DRAWINGS
[0041] Figure 1 This is a diagram showing the execution result of a query statement. Figure 1 ;
[0042] Figure 2 This is a diagram showing the execution result of a query statement. Figure 2 ;
[0043] Figure 3 A flowchart of a query statement optimization method provided in an embodiment of the present application Figure 1 ;
[0044] Figure 4 A flowchart of a query statement optimization method provided in an embodiment of the present application Figure 2 ;
[0045] Figure 5 A schematic diagram of an execution result provided in an embodiment of the present application;
[0046] Figure 6 A schematic diagram of the structure of a query statement optimization device provided in an embodiment of the present application;
[0047] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION
[0048] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0049] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects, and do not limit their order. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.
[0050] It should be noted that in the embodiments of the present application, words such as "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.
[0051] In the related art, for the query statement shown below:
[0052] CREATE INDEX t1_idx ON t1(a,b);
[0053] EXPLAIN SELECT count(*) FROM t1 GROUP BY b,a limit 10; (The purpose of this query statement is to group by columns b and a from table t1 and calculate the number of rows in each group).
[0054] When the optimizer processes this statement, most optimizers will generate a statement containing Figure 1 The execution path of the group aggregation shown and a Figure 2 The execution path that includes hash aggregation is shown. The optimizer then selects the execution path with the lowest execution cost as the final execution plan. In the above example, since the cost of hash aggregation is 21,925, which is less than the cost of grouping aggregation, 147,757, hash aggregation is selected to create the final execution plan and handed over to the executor for execution.
[0055] like Figure 1 As shown in the figure, for the execution path of group aggregation, it can be seen that the main time consumption occurs on the lower-level Sort node, and the time consumption of the upper-level GroupAgg node is very low. If the time consumption of the Sort node can be reduced or the Sort node can be eliminated, the cost of the execution path of group aggregation can be greatly reduced, and the performance of the query statement can be greatly improved.
[0056] In view of this, an embodiment of the present application provides a query statement optimization method, which can reduce the cost of the execution path of group aggregation by rearranging the key value order of the Group by sub-statement, thereby greatly improving the performance of the query statement.
[0057] The following specific embodiments are used to describe in detail the technical solution of the present application and how the technical solution of the present application solves the above technical problems. The following specific embodiments can be implemented independently or in combination with each other, and the same or similar concepts or processes may not be described in detail in some embodiments.
[0058] Figure 3 A flowchart of a query statement optimization method provided in an embodiment of the present application is shown in FIG. Figure 3 As shown, the steps include:
[0059] S301: Obtain a query statement to be processed.
[0060] The execution subject of the embodiment of the present application may be an optimizer that processes the query statement. The optimizer may obtain the query statement input by the user from the outside.
[0061] S302: When the query statement includes a grouping and aggregation sub-statement, determine whether the query statement is an optimizable query statement according to the grouping and aggregation sub-statement and the query statement.
[0062] In some embodiments, when the optimizer obtains a query statement, it can perform a grammatical analysis on the query statement to determine whether the query statement includes a grouping aggregation sub-statement (i.e., a Group by sub-statement). If not, it is determined that alignment cannot be performed for grouping optimization, and the optimizer can execute the optimization method in the prior art.
[0063] If the optimizer determines that the query statement includes a grouping aggregation sub-statement, then the query statement is confirmed to initially meet the conditions for grouping optimization. The query optimizer can further judge the query statement to determine whether the query statement can be grouped and optimized.
[0064] In some embodiments, when the query statement includes a grouping and aggregation sub-statement, the query optimizer may further analyze the grouping and aggregation sub-statement and the index of the data table corresponding to the query statement to determine whether the grouping and aggregation sub-statement and the index of the data table corresponding to the query statement meet the preset conditions. If so, it is determined as a query statement that can be optimized by grouping. If not, it is determined as a query statement that cannot be optimized by grouping.
[0065] Exemplarily, the preset conditions may include one or more of the key values of the grouping aggregation sub-statements coming from the same data table, the index of the data table to be queried being an index of the target type, and the key value of the grouping aggregation sub-statement being a subset of the index key value.
[0066] If the grouping aggregation sub-statements and the index of the data table corresponding to the query statement meet the above preset conditions, it is determined to be a query statement that can be grouped and optimized.
[0067] S303: If yes, optimize the group-by-group aggregation sub-statement to obtain a target query statement, so that the grouping order of the group-by-group aggregation sub-statement is consistent with the index order of the data table to be queried.
[0068] In some embodiments, when determining a query statement that can be grouped and optimized, the query optimizer can modify the grouping order of the grouping aggregation sub-statement based on the index order of the data table to be queried, so that the grouping order of the grouping aggregation sub-statement is consistent with the index order of the data table to be queried, thereby obtaining the target query statement.
[0069] For example, for the following query:
[0070] CREATE INDEX t1_idx ON t1(a,b);
[0071] EXPLAIN SELECT count(*) FROM t1 GROUP BY b,a limit 10;
[0072] The grouping and aggregation sub-statements are optimized to obtain the target query statement as follows:
[0073] CREATE INDEX t1_idx ON t1(a,b);
[0074] EXPLAIN SELECT count(*) FROM t1 GROUP BY a,b limit 10;
[0075] S304: Execute an execution plan generated based on the target query statement.
[0076] When the optimizer obtains the target query statement, it may generate at least one execution plan based on the target query statement, and select an execution plan from the at least one execution plan for execution. For example, the execution plan with the lowest cost among the execution plans may be selected for execution.
[0077] The query statement optimization method provided in the embodiment of the present application obtains the query statement to be processed; when the query statement includes a grouping aggregation sub-statement, determines whether the query statement is a query statement that can be optimized based on the grouping aggregation sub-statement and the query statement; if so, optimizes the grouping aggregation sub-statement to obtain a target query statement so that the grouping order of the grouping aggregation sub-statement is consistent with the index order of the data table to be queried; and executes the execution plan generated based on the target query statement. The above method can eliminate the sort node in the generated grouping aggregation path by correcting the grouping order of the grouping aggregation sub-statement to be consistent with the index order of the data table to be queried, thereby reducing the execution cost and greatly improving the performance of the query statement.
[0078] Figure 4 A flowchart of a query statement optimization method provided in an embodiment of the present application Figure 2 ,like Figure 4 As shown, including:
[0079] S401: Obtain a query statement to be processed.
[0080] S402: Determine whether the query statement includes a grouping aggregation sub-statement. If so, execute the step S403. If not, end the grouping optimization of the query statement.
[0081] The specific implementation of steps S401 and S402 in the embodiment of the present application can be referred to Figure 3 The specific implementation methods shown in S301 and S302 in the illustrated embodiment will not be described in detail here.
[0082] S403, determine whether the key values of the group aggregation sub-statements are from the same data table. If so, execute the step shown in S404. If not, end the group optimization of the query statement.
[0083] For example, for the following query statement:
[0084] CREATE INDEX t1_idx ON t1(a,b);
[0085] EXPLAIN SELECT count(*) FROM t1 GROUP BY b,a limit 10;
[0086] The key value of the grouping aggregation sub-statement in this statement can refer to the two key values (b, a) of the GROUP BY sub-statement. From the above query statement, it can be seen that the two key values (b, a) are from the same data table t1, so the subsequent steps can be executed.
[0087] S404: Determine whether the data table corresponding to the query statement has an index of a preset type. If so, execute the step shown in S405. If not, end the grouping optimization of the query statement.
[0088] The preset type of index may refer to a Btree index.
[0089] In some embodiments, the optimizer can check whether there is an index in the data table corresponding to the query statement. If there is an index, it can be determined whether the index is a Btree index. If there is an index in the data table corresponding to the query statement, and the index is a Btree index, the optimizer can execute subsequent steps. If there is no index in the data table corresponding to the query statement, or if there is an index, but the index is not a Btree index, the grouping optimization of the query statement is terminated. The optimizer can determine whether there is an index of a preset type in the data table corresponding to the query statement by querying the data dictionary corresponding to the query statement.
[0090] S405, determine whether the key value of the group aggregation sub-statement is a subset of the index key value. If so, determine that the query statement can be grouped and optimized, and execute the step shown in S406. If not, end the group optimization of the query statement.
[0091] In some embodiments, as in the query statement shown in S403 , the index key value may refer to the key value (a, b) in the index t1_idx (t1_idx ON t1(a, b)) of the data table t1.
[0092] If the Group by key value is a subset of the Btree index key value, it is determined that the query statement can be optimized by grouping, otherwise, the query statement cannot be optimized by grouping. For example, if the Btree index key value includes (a, b, c) and the Group by key value includes (a, b), then the Group by key value is a subset of the Btree index key value; if the Btree index key value includes (a, b, c) and the Group by key value includes (a, d), then the Group by key value is not a subset of the Btree index key value.
[0093] S406: Obtain metadata of the index.
[0094] When the optimizer determines that the group aggregation sub-statement and the query statement meet the above conditions, it can obtain the metadata of the index of the data table corresponding to the query statement from the data dictionary.
[0095] S407: Determine the key value order of the index according to the metadata.
[0096] After obtaining the metadata of the index, the optimizer can load the metadata into the memory, parse and read the list of key value sequences of the index saved in the metadata, and determine the order of key values in the index corresponding to the grouping and aggregation sub-statements according to the list of key value sequences of the index. For example, if the key values in the grouping and aggregation sub-statements include (a, b), then the key value order of the key value (a, b) in the index is queried according to the list of key value sequences.
[0097] S408. Modify the key value sequence of the grouping and aggregation sub-statements according to the key value sequence of the index to obtain a target query statement.
[0098] In some embodiments, if the key value order of the queried index is inconsistent with the key value order of the grouping aggregation sub-statement, the key value order of the grouping aggregation sub-statement is modified to the key value order of the index. For example, if the key value order of the grouping aggregation sub-statement is (b, a) and the key value order in the index is (a, b), the key value order of the grouping aggregation sub-statement is modified to (a, b).
[0099] For example, for the Group by statement, Group by a,b and Group by b,a are actually equivalent. Therefore, the order of the key values in Group by can be exchanged arbitrarily, and the order exchange does not affect the query results. After the key value exchange, when executing the corresponding grouping and aggregation plan, the Sort node can be eliminated, and the order of the Btree index or the order of other nodes in the lower layer can be effectively used to improve the efficiency of the query.
[0100] S409: Execute the execution plan generated based on the target query statement.
[0101] When the optimizer obtains the target query statement, it may generate at least one execution plan based on the target query statement, and select an execution plan from the at least one execution plan for execution. For example, the execution plan with the lowest cost among the execution plans may be selected for execution.
[0102] The technical solution of the present application is described below with a specific example.
[0103] For example, for a hierarchical data table t1 with 1 million records and an existing index:
[0104] CREATE INDEX t1_idx ON t1(a,b);
[0105] The query statement is as follows:
[0106] SELECT count(*) FROM t1 GROUP BY b,a limit 10;
[0107] like Figure 5 As shown, the execution time before applying the technical solution provided in the embodiment of the present application is: 1039.337 milliseconds.
[0108] Since table t1 has a Btree joint index t1_idx, the key value sequence of the joint index is {a,b}, which means that the data of the index is sorted in ascending order according to columns a and b.
[0109] Since the order of GROUP BY key values can be swapped arbitrarily, the order of GROUP BY key values is rearranged to {a, b}, and the t1_idx index is used for index scanning, so that the scanned tuples are output in ascending order of columns a and b, thereby eliminating the Sort node and improving query performance. Figure 5 As shown, after applying the technical solution of the embodiment of the present application, the execution time is: 0.504 milliseconds. It can be seen that after adopting the technical solution provided by the embodiment of the present application, the query performance is improved by several orders of magnitude.
[0110] Based on the above embodiments, the embodiments of the present application also provide a statement optimization device.
[0111] Figure 6 A schematic diagram of the structure of a statement optimization device provided in an embodiment of the present application is shown in FIG. Figure 6 As shown, including:
[0112] The acquisition module 601 is used to acquire the query statement to be processed.
[0113] The processing module 602 is used to determine whether the query statement is an optimizable query statement according to the grouping and aggregation sub-statement and the query statement when the query statement includes a grouping and aggregation sub-statement.
[0114] The optimization module 603 is used to optimize the grouping and aggregation sub-statements to obtain a target query statement so that the grouping order of the grouping and aggregation sub-statements is consistent with the index order of the data table to be queried.
[0115] The execution module 604 is used to execute the execution plan generated based on the target query statement.
[0116] In some embodiments, the optimization module 603 is further used to rearrange the grouping and aggregation key values in the grouping and aggregation sub-statement according to the index sequence of the data table to obtain the target query statement.
[0117] In some embodiments, the optimization module 603 is also used to obtain the key value order of the grouping aggregation in the grouping aggregation sub-statement, and the key value order of the index of the data table; modify the key value order of the grouping aggregation sub-statement to the key value order of the index to obtain the target query statement.
[0118] In some embodiments, the processing module 602 is also used to obtain the data table corresponding to the query statement; determine whether the key values of the grouping and aggregation sub-statements belong to the same data table; if so, determine whether the query statement is a query statement to be optimized based on the data table and the key values of the grouping and aggregation sub-statements; if not, determine that the query statement is not a query statement to be optimized.
[0119] In some embodiments, processing module 602 is also used to determine whether there is an index on the data table and whether the type of the index is the target index type; if so, determine whether the query statement is a query statement to be optimized based on the key value of the grouping aggregation sub-statement and the key value of the index; if not, determine that the query statement is not a query statement to be optimized.
[0120] In some embodiments, the processing module 602 is also used to determine whether the key value of the grouped aggregation is a subset of the index key value, and whether the key value order of the grouped aggregation is inconsistent with the key value order of the corresponding key value in the index; if so, it is determined that the query statement is a query statement to be optimized; if not, it is determined that the query statement is not a query statement to be optimized.
[0121] In some embodiments, the processing module 602 is further used to obtain metadata of the index; read a key value sequence list of the index from the metadata; and determine a key value sequence of the index based on the key value sequence list.
[0122] The statement optimization device provided in the embodiment of the present application can execute the statement optimization method provided in any of the above embodiments, and its principles and technical effects are similar, which will not be repeated here.
[0123] An embodiment of the present application also provides an electronic device.
[0124] Figure 7 A schematic diagram of the structure of an electronic device 70 provided in an embodiment of the present application is shown in FIG. Figure 7 As shown, including:
[0125] Processor 701.
[0126] The memory 702 is used to store executable instructions of the terminal device.
[0127] Specifically, the program may include program codes, and the program codes include computer operation instructions. The memory 702 may include a high-speed RAM memory, and may also include a non-volatile memory (non-volatile memory), such as at least one disk memory.
[0128] The processor 701 is used to execute the computer execution instructions stored in the memory 702 to implement the technical solution of the database statistical information collection method embodiment described in the above method embodiment.
[0129] The processor 701 may be a central processing unit (CPU), or an application specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application.
[0130] Optionally, the electronic device 70 may further include a communication interface, so that it can communicate and interact with an external device through the communication interface, and the external device may be, for example, a user terminal (e.g., a mobile phone, a tablet). In a specific implementation, if the communication interface, the memory 702, and the processor 701 are implemented independently, the communication interface, the memory 702, and the processor 701 may be interconnected through a bus and communicate with each other.
[0131] The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus, or an Extended Industry Standard Architecture (EISA) bus, etc. The bus can be divided into an address bus, a data bus, a control bus, etc., but it does not mean that there is only one bus or one type of bus.
[0132] Optionally, in a specific implementation, if the communication interface, the memory 702 and the processor 701 are integrated on a chip, the communication interface, the memory 702 and the processor 701 can communicate through an internal interface.
[0133] A computer-readable storage medium is also provided in an embodiment of the present application, on which a computer program is stored. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. The implementation principle and technical effect are similar and will not be repeated here.
[0134] In one possible implementation, a computer-readable medium may include a random access memory (RAM), a read-only memory (ROM), a compact disc read-only memory (CD-ROM) or other optical disk storage, a magnetic disk storage or other magnetic storage device, or any other medium that is intended to carry or store the required program code in the form of instructions or data structures and can be accessed by a computer. Moreover, any connection is appropriately referred to as a computer-readable medium. For example, if the software is transmitted from a website, server or other remote source using a coaxial cable, a fiber optic cable, a twisted pair, a digital subscriber line (DSL) or wireless technologies such as infrared, radio and microwave, then the coaxial cable, fiber optic cable, twisted pair, DSL or wireless technologies such as infrared, radio and microwave are included in the definition of the medium. Disks and optical disks as used herein include optical disks, laser disks, optical disks, digital versatile disks (DVD), floppy disks and Blu-ray disks, where disks usually reproduce data magnetically, while optical disks reproduce data optically using lasers. Combinations of the above should also be included in the scope of computer-readable media.
[0135] A computer program product is also provided in an embodiment of the present application, including a computer program. When the computer program is executed by a processor, the technical solution of the statistical information collection method embodiment of the above-mentioned database is implemented. Its implementation principle and technical effect are similar and will not be repeated here.
[0136] In the specific implementation of the above-mentioned terminal device or server, it should be understood that the processor can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor can be a microprocessor or the processor can be any conventional processor, etc. The steps of the method disclosed in the embodiments of the present application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor.
[0137] Those skilled in the art will appreciate that all or part of the steps of any of the above method embodiments may be completed by hardware associated with program instructions. The aforementioned program may be stored in a computer-readable storage medium, and when the program is executed, all or part of the steps of the above method embodiments are executed.
[0138] If the technical solution of the present application is implemented in the form of software and sold or used as a product, it can be stored in a computer-readable storage medium. Based on this understanding, all or part of the technical solution of the present application can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including a computer program or several instructions. The computer software product enables a computer device (which can be a personal computer, a server, a network device, or a similar electronic device) to perform all or part of the steps of the method described in the embodiment of the present application.
[0139] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.
[0140] It should be further noted that, although the various steps in the flowchart are displayed in sequence according to the indication of the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless there is a clear description in this article, the execution of these steps is not strictly limited in order, and these steps can be executed in other orders. Moreover, at least a portion of the steps in the flowchart may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and the execution order of these sub-steps or stages is not necessarily to be carried out in sequence, but can be executed in turn or alternately with other steps or at least a portion of the sub-steps or stages of other steps.
[0141] It should be understood that the above-mentioned device embodiments are only illustrative, and the device of the present application can also be implemented in other ways. For example, the division of units / modules in the above-mentioned embodiments is only a logical function division, and there may be other division methods in actual implementation. For example, multiple units, modules or components can be combined, or can be integrated into another system, or some features can be ignored or not executed.
[0142] In addition, unless otherwise specified, each functional unit / module in each embodiment of the present application may be integrated into one unit / module, each unit / module may exist physically separately, or two or more units / modules may be integrated together. The above-mentioned integrated unit / module may be implemented in the form of hardware or in the form of a software program module.
[0143] If the integrated unit / module is implemented in the form of hardware, the hardware may be a digital circuit, an analog circuit, etc. The physical implementation of the hardware structure includes but is not limited to transistors, memristors, etc. Unless otherwise specified, the processor may be any appropriate hardware processor, such as a CPU, a GPU, an FPGA, a DSP, an ASIC, etc. Unless otherwise specified, the storage unit may be any appropriate magnetic storage medium or magneto-optical storage medium, such as a resistive random access memory (RRAM), a dynamic random access memory (DRAM), a static random access memory (SRAM), an enhanced dynamic random access memory (EDRAM), a high-bandwidth memory (HBM), a hybrid memory cube (HMC), etc.
[0144] If the integrated unit / module is implemented in the form of a software program module and sold or used as an independent product, it can be stored in a computer-readable memory. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a memory, including a number of instructions to enable a computer device (which can be a personal computer, server or network device, etc.) to execute all or part of the steps of the various embodiments of the present application. The aforementioned memory includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or optical disk and other media that can store program codes.
[0145] In the above embodiments, the description of each embodiment has its own emphasis. For the part not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not 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.
[0146] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.
Claims
1. A query statement optimization method, characterized in that: Get the query statement to be processed; In the case where the query statement includes a grouping and aggregation sub-statement, determining whether the query statement is an optimizable query statement according to the grouping and aggregation sub-statement and the query statement; If yes, optimize the group-by-group aggregation sub-statement to obtain a target query statement, so that the grouping order of the group-by-group aggregation sub-statement is consistent with the index order of the data table to be queried; Execute the execution plan generated based on the target query statement.
2. The method according to claim 1, characterized in that The optimization of the grouping and aggregation sub-statements to obtain the target query statement includes: According to the index sequence of the data table, the key values of the grouping and aggregation in the grouping and aggregation sub-statement are rearranged to obtain the target query statement.
3. The method according to claim 2, characterized in that The step of rearranging the grouping and aggregation key values in the grouping and aggregation sub-statement according to the index sequence of the data table to obtain the target query statement includes: Obtain the key value order of the grouping aggregation in the grouping aggregation sub-statement, and the key value order of the index of the data table; The key value order of the grouping and aggregation sub-statements is modified to the key value order of the index to obtain the target query statement.
4. The method according to any one of claims 1 to 3, characterized in that: The determining, according to the grouping and aggregation sub-statements and the query statement, whether the query statement is a query statement to be optimized includes: Obtaining the data table corresponding to the query statement; Determine whether the key values of the grouping and aggregation sub-statements belong to the same data table; If yes, determining whether the query statement is a query statement to be optimized according to the key value of the data table and the grouping and aggregation sub-statement; If not, it is determined that the query statement is not a query statement to be optimized.
5. The method according to claim 4, characterized in that The determining whether the query statement is a query statement to be optimized according to the key value of the data table and the grouping and aggregation sub-statement includes: Determine whether the data table has an index and whether the type of the index is the target index type; If so, determining whether the query statement is a query statement to be optimized according to the key value of the grouping and aggregation sub-statement and the key value of the index; If not, it is determined that the query statement is not a query statement to be optimized.
6. The method according to claim 5, characterized in that The determining whether the query statement is a query statement to be optimized according to the key value of the grouping aggregation sub-statement and the key value of the index includes: Determine whether the key value of the grouped aggregation is a subset of the key value of the index, and whether the order of the key values of the grouped aggregation is inconsistent with the order of the key values of the corresponding key values in the index; If yes, determining that the query statement is a query statement to be optimized; If not, it is determined that the query statement is not a query statement to be optimized.
7. The method according to claim 3, characterized in that The obtaining of the key value sequence of the index of the data table includes: Obtaining metadata for the index; Reading an ordered list of key values of the index from the metadata; The key value order of the index is determined based on the key value order list.
8. An electronic device, characterized in that: include: Memory for storing computer programs; A processor, configured to execute the computer program to implement the method according to any one of claims 1 to 7.
9. A computer-readable storage medium, characterized in that: A computer program is stored thereon, and the computer program is executed by a processor to implement the method according to any one of claims 1 to 7.
10. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a controller.