Optimization method, device, equipment and product for eliminating sequencing of SQL (Structured Query Language) statement
By identifying and eliminating unnecessary sorting operations in database queries, the performance overhead problem caused by the ORDER BY clause is solved, and the query statement is optimized, and the database query performance is improved.
Patent Information
- Application Number
- CN202510163309.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-02-14
- Publication Date
- 2025-06-06
AI Technical Summary
In database queries, sorting operations caused by the ORDER BY clause can bring huge performance overhead, especially when processing large-scale data, not all sorting operations can be indexed.
By determining the current node based on the logical plan relationship tree, if the current node is a child node of the sorting node and is a table scan node, the target sorting field corresponding to the sorting node is determined based on the linked list status of the target linked list, the target column in the target linked list, and the index field of the current node index. Then, based on the index field and the target sort field, determine whether to eliminate the sort clause corresponding to the sorting node.
Reduce the resource usage and consumption by the sorting step, realize the optimization of query statements, and improve database query performance.
Smart Images
Figure CN120104671A_ABST
Abstract
Description
Technical Field
[0001] The embodiments of the present disclosure relate to the field of database technology, and in particular to an optimization method, device, equipment and product for eliminating sorting of SQL statements. Background Art
[0002] Structured Query Language (SQL) sorting refers to using the ORDER BY clause in SQL to reorder the query result set so that it is sorted by the specified field. When the field specified by the ORDER BY clause is already sorted, the sorting can be eliminated, such as in the following scenario:
[0003] DROP TABLE T1;
[0004] CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
[0005] CREATE INDEX I1 ON T1(C1,C2,C3);
[0006] EXPLAIN SELECT C1 FROM T1 ORDER BY C1,C2,C3;
[0007] 1 #NSET2:[1,1,24]
[0008] 2 #PRJT2:[1,1,24];exp_num(2),is_atom(FALSE)
[0009] 3 #SSCN:[1,1,24];I1(T1);btr_scan(1);is_global(0)
[0010] In this scenario, if the I1 index is selected when scanning T1, the order of its C1, C2, and C3 fields is guaranteed by the I1 index. Not making an ORDER BY clause has no effect on the result set, so sorting can be eliminated in the execution plan.
[0011] In database queries, the ORDER BY clause is used to sort query results, but sorting operations may bring huge performance overhead, especially when processing large-scale data. In order to optimize query performance, database management systems (DBMS) usually use indexes to speed up sorting operations. However, not all sorting operations can be optimized by indexes, for example: DROP TABLE T1;
[0012] CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
[0013] CREATE INDEX I1 ON T1(C1,C2,C3);
[0014] EXPLAIN SELECT C1 FROM T1 WHERE C2=1ORDER BY C1,C3;
[0015] 1 #NSET2:[1,1,24]
[0016] 2 #PRJT2:[1,1,24];exp_num(2),is_atom(FALSE)
[0017] 3 #SORT3:[1,1,24];key_num(2),partition_key_num(0),is_distinct(FALSE),top_flag(0),is_adaptive(0)
[0018] 4 #SLCT2:[1,1,24]; T1.C2=1
[0019] 5 #SSCN:[1,1,24];I1(T1);btr_scan(1);is_global(0)
[0020] In this scenario, if the I1 index is selected when scanning T1, the sorting cannot be eliminated because the fields C1 and C3 specified in the ORDER BY clause are inconsistent with the index fields. The two fields C1 and C3 still need to be sorted when the sorting is actually performed. How to identify and eliminate unnecessary sorting operations in the query execution plan has become an urgent problem to be solved. Summary of the invention
[0021] The embodiments of the present disclosure provide an optimization method, device, equipment and product for eliminating sorting of SQL statements, thereby reducing the occupation and consumption of resources by the sorting step and achieving optimization of query statements.
[0022] In a first aspect, a method for optimizing SQL statements to eliminate sorting is provided, comprising:
[0023] Determine a current node based on a logical plan relationship tree; the current node is a child node of a sorting node; the logical plan relationship tree is determined based on a structured query language SQL statement to be processed;
[0024] If the node type of the current node is a table scan node, the target sorting field corresponding to the sorting node is determined based on the linked list state of the target linked list, the target column in the target linked list, and the index field of the current node index; the target column is a column in the target linked list that meets the equal value filtering condition;
[0025] Based on the index field and the target sort field, it is determined whether to eliminate the sort corresponding to the sort clause corresponding to the sort node, so as to optimize the SQL statement to be processed.
[0026] In a second aspect, an optimization device for eliminating sorting of SQL statements is provided, comprising:
[0027] A current node determination module, used to determine a current node based on a logical plan relationship tree; the current node is a child node of a sorting node; the logical plan relationship tree is determined based on a structured query language SQL statement to be processed;
[0028] A target sorting field determination module is used to determine the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list and the index field of the current node index if the node type of the current node is a table scan node; the target column is a column in the target linked list that meets the equal value filtering condition;
[0029] An optimization module is used to determine whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed.
[0030] In a third aspect, an electronic device is provided, including:
[0031] at least one processor; and
[0032] a memory communicatively connected to the at least one processor; wherein,
[0033] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the optimization method for eliminating sorting of SQL statements as described in the first aspect above.
[0034] In a fourth aspect, a computer-readable storage medium is provided, on which a computer program is stored, and when the program is executed by a processor, the optimization method for eliminating sorting of SQL statements as described in the first aspect above is implemented.
[0035] In a fifth aspect, a computer program product is provided, the computer program product comprising a computer program, and when the computer program is executed by a processor, the optimization method for eliminating sorting of SQL statements as described in the first aspect above is implemented.
[0036] The disclosed embodiment discloses an optimization method, device, equipment and product for eliminating sorting of SQL statements, the method comprising: determining the current node based on a logical plan relationship tree; the current node is a child node of a sorting node; the logical plan relationship tree is determined based on a structured query language SQL statement to be processed; if the node type of the current node is a table scan node, then determining the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list and the index field of the current node index; the target column is a column in the target linked list that satisfies an equal value filtering condition; determining whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed. The technical solution provided in this embodiment determines the target column according to the equal value filtering condition, and determines whether to eliminate the sorting based on the index field and the target sorting field, thereby reducing the occupation and consumption of resources by the sorting step and optimizing the query statement.
[0037] It should be understood that the content described in this section is not intended to identify the key or important features of the embodiments of the present disclosure, nor is it intended to limit the scope of the embodiments of the present disclosure. Other features of the embodiments of the present disclosure will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS
[0038] In order to more clearly illustrate the technical solutions in the embodiments of the present disclosure, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.
[0039] Figure 1 It is a flow chart of an optimization method for eliminating sorting of SQL statements provided in the first embodiment of the present disclosure;
[0040] Figure 2 It is a structural schematic diagram of an optimization device for eliminating sorting of SQL statements provided in the second embodiment of the present disclosure;
[0041] Figure 3 It is a structural diagram of an electronic device provided in Embodiment 3 of the present disclosure. DETAILED DESCRIPTION
[0042] In order to enable those skilled in the art to better understand the solutions of the embodiments of the present disclosure, the technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are only embodiments of a part of the embodiments of the present disclosure, rather than all of the embodiments. Based on the embodiments in the embodiments of the present disclosure, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the embodiments of the present disclosure.
[0043] It should be noted that the terms "first", "second", etc. in the specification and claims of the embodiments of the present disclosure and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present disclosure described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products, or devices.
[0044] Embodiment 1
[0045] Figure 1 This is a flow chart of an optimization method for eliminating and sorting SQL statements provided in the first embodiment of the present disclosure. This embodiment is applicable to the case of eliminating and sorting SQL statements. The method can be executed by an optimization device for eliminating and sorting SQL statements. The optimization device for eliminating and sorting SQL statements can be implemented in the form of hardware and / or software. The optimization device for eliminating and sorting SQL statements can be configured in electronic devices, including but not limited to computers, computers, terminals, servers and other devices with data processing capabilities. Figure 1 As shown, the method includes:
[0046] S110, determining a current node based on a logical plan relationship tree; the current node is a child node of a sorting node; and the logical plan relationship tree is determined based on a structured query language SQL statement to be processed.
[0047] In this embodiment, the logical plan relationship tree (Logical Plan Tree) can be a tree data structure generated by the database query optimizer, which can be used to describe how to execute SQL query statements step by step. The logical plan relationship tree can usually include various operation nodes (such as scanning, filtering, projection, connection, sorting, etc.), which are organized in a specific order and reflect the logical execution steps of the query. The logical plan relationship tree can be generated by performing grammatical analysis and semantic analysis on the SQL statement to be processed input by the user.
[0048] Following the above description, after the logical plan relationship tree is generated, the current node can be determined based on the generated logical plan relationship tree, and the current node is the child node of the sorting node. That is, the sorting node (i.e., the node corresponding to the ORDER BY clause) can be found in the logical plan relationship tree, and then the child node of the sorting node can be found according to the logical plan relationship tree, and the child node is set as the current node. Among them, the sorting node (SORT node) is a logical node in the database query execution plan, which represents the operation of sorting data. It can correspond to the ORDER BY clause in the SQL query. The ORDER BY clause is part of the SQL query statement and can be used to specify the sorting order of the query results, allowing the user to sort the result set in ascending or descending order according to one or more fields.
[0049] S120. If the node type of the current node is a table scan node, determine the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list, and the index field of the current node index; the target column is a column in the target linked list that meets the equal value filtering condition.
[0050] Specifically, after determining the child node of the sorting node as the current node, the node type of the current node can be determined. If the node type of the current node is a table scan node, the target sorting field corresponding to the sorting node can be determined based on the linked list state of the target linked list, the target column in the target linked list, and the index field of the current node index. Among them, the table scan node is a node in the execution plan, which is responsible for reading data from the table. It usually corresponds to the FROM clause in the SQL query, which means getting data from a certain table. Among them, the FROM clause can be used to specify the table or combination of tables involved in the query.
[0051] Continuing with the above description, the linked list state of the target linked list may include the linked list state being empty and the linked list state being not empty. Based on the linked list state of the target linked list, it can be determined whether to complete the sorting field corresponding to the sorting node. If the linked list state of the target linked list is not empty, the sorting field corresponding to the sorting node needs to be completed based on the target column in the target linked list and the index field of the current node index, and the completed sorting field is the target sorting field; if the linked list state of the target linked list is empty, the sorting field corresponding to the sorting node can be used as the target sorting field.
[0052] S130: Determine whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed.
[0053] It can be known that after the target sorting field is determined, it can be determined whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field. It can be determined whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node by judging whether the index field is consistent with the target sorting field. Exemplarily, if the index field is consistent with the target sorting field, the sorting corresponding to the sorting clause corresponding to the sorting node can be eliminated; if the index field is inconsistent with the target sorting field, the sorting corresponding to the sorting clause corresponding to the sorting node is not eliminated.
[0054] This embodiment provides an optimization method for eliminating sorting of SQL statements, including: determining a current node based on a logical plan relationship tree; the current node is a child node of a sorting node; the logical plan relationship tree is determined based on a structured query language SQL statement to be processed; if the node type of the current node is a table scan node, determining a target sorting field corresponding to the sorting node based on a linked list state of a target linked list, a target column in the target linked list, and an index field of an index of the current node; the target column is a column in the target linked list that satisfies an equal value filtering condition; determining whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed, reduce the occupation and consumption of resources by the sorting step, and optimize the query statement.
[0055] As an optional implementation of this embodiment, the optimization method for eliminating sorting of SQL statements provided in this embodiment, after determining the current node based on the logical plan relationship tree, further includes:
[0056] 1) Determine the node type of the current node.
[0057] Specifically, after determining the child node of the sorting node as the current node, the node type of the current node may be determined. The node type of the current node may be a selection node or a table scan node.
[0058] 2) If the node type of the current node is a selection node, the child node of the current node is designated as the new current node, and the process returns to the step of determining the node type of the current node.
[0059] It can be known that if the node type of the current node is a selection node, the child node of the current node can be designated as a new current node, and the step of determining the node type of the current node can be returned.
[0060] It should be noted that if the node type of the current node is neither a selection node nor a table scan node, but other node types, it is also necessary to determine the child node of the node as the new current node and continue to determine the node type of the new current node.
[0061] As an optional implementation of this embodiment, the optimization method for eliminating sorting of SQL statements provided in this embodiment further includes:
[0062] 1) If the node type of the current node is a selection node, a target filtering condition is determined from a preset filtering condition set, and the target filtering condition is an equal value filtering condition.
[0063] Specifically, after determining the child node of the sorting node as the current node, the node type of the current node can be determined. If the node type of the current node is a selection node, the target filtering condition can be determined from the preset filtering conditions. Among them, the selection node can be a node in the execution plan, which is responsible for filtering data according to the conditions in the WHERE clause, and usually corresponds to the WHERE clause in the SQL query. Among them, the WHERE clause can be used to specify query conditions to filter out rows that meet specific conditions. The target filtering condition can be a specified filtering condition. Exemplarily, the target filtering condition can be an equal value filtering condition in the form of "column = constant". The equal value filtering condition can refer to the use of an equal sign (=) in the WHERE clause to specify that a field (column) must be equal to a specific value.
[0064] 2) Store the target column corresponding to the target filter condition into the target linked list.
[0065] Specifically, after the target filtering condition is determined, the column corresponding to the determined target filtering condition can be determined as the target column, and the target column can be stored in the target linked list, where the linked list can be a common data structure for storing and managing a series of elements. Unlike an array, the elements in a linked list are not stored continuously, but are dynamically connected together through pointers (or references). Each element (called a "node") contains two parts: data and a pointer to the next node.
[0066] As an optional implementation of this embodiment, determining the target sorting field corresponding to the sorting node based on the link list state of the target link list, the target column in the target link list, and the index field of the current node index includes:
[0067] 1) When the linked list state of the target linked list is not empty, the sorting field corresponding to the sorting node is completed based on the target column in the target linked list and the index field of the current node index, and the sorting field corresponding to the completed sorting node is determined as the target sorting field.
[0068] Specifically, when the linked list state of the target linked list is not empty, the sorting field corresponding to the sorting node can be completed based on the target column in the target linked list and the index field of the current node index. Because the final decision is whether the sorting field and the index field are consistent, the sorting field and the equivalent condition column (target column) in the target linked list need to be as close to the form of the index field as possible when performing the completion operation here. Exemplarily, the sorting fields corresponding to the sorting node are C1 and C3, the equivalent condition column (target column) is C2, and the index fields of the current node index are C1, C2, and C3. When completing, C2 is not placed in any position, but is placed between C1 and C3 with reference to the index field, and is C1, C2, and C3 after completion.
[0069] 2) When the linked list state of the target linked list is empty, the sorting field corresponding to the sorting node is determined as the target sorting field.
[0070] Specifically, if the linked list state of the target linked list is empty, it can be understood that the target linked list has not collected an equal value filtering condition of the form "column = constant", so there is no need to perform a completion operation, and the sorting field corresponding to the sorting node can be determined as the target sorting field.
[0071] As an optional implementation of this embodiment, the determining whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field includes:
[0072] 1) When the index field is consistent with the target sort field, eliminate the sort corresponding to the sort clause corresponding to the sort node.
[0073] Specifically, when the index field is consistent with the target sort field, the sort corresponding to the sort clause corresponding to the sort node is eliminated, that is, the sort corresponding to the ORDER BY clause can be eliminated.
[0074] 2) When the index field is inconsistent with the target sort field, the sort corresponding to the sort clause corresponding to the sort node is not eliminated.
[0075] Specifically, when the index field is inconsistent with the target sort field, the sort corresponding to the sort clause corresponding to the sort node is not eliminated.
[0076] Optionally, the optimization method for eliminating sorting of SQL statements provided in this embodiment, after eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the method further includes:
[0077] The sorting node is removed from the logical plan relationship tree, and the SQL statement to be processed is executed based on the logical plan relationship tree after the sorting node is removed.
[0078] It can be known that after eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the sorting node can also be removed from the logical plan relationship tree, and the SQL to be processed is executed based on the logical plan relationship tree after the sorting node is removed, so as to optimize the SQL statement to be processed.
[0079] Optionally, after not eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the method further includes:
[0080] The SQL statement to be processed is executed based on the logical plan relationship tree.
[0081] It can be known that after the sorting corresponding to the sorting clause corresponding to the sorting node is not eliminated, the SQL statement to be processed can be further executed based on the logical plan relationship tree.
[0082] This embodiment also provides an application example of an optimization method for eliminating sorting of SQL statements. The SQL statement test case involved in introducing the application example and the execution plan before optimization are as follows:
[0083] DROP TABLE T1;
[0084] CREATE TABLE T1(C1 INT,C2 INT,C3 INT);
[0085] CREATE INDEX I1 ON T1(C1,C2,C3);
[0086] EXPLAIN SELECT C1 FROM T1 WHERE C2=1ORDER BY C1,C3;
[0087] 1 #NSET2:[1,1,24]
[0088] 2 #PRJT2:[1,1,24];exp_num(2),is_atom(FALSE)
[0089] 3 #SORT3:[1,1,24];key_num(2),partition_key_num(0),is_distinct(FALSE),top_flag(0),is_adaptive(0)
[0090] 4 #SLCT2:[1,1,24]; T1.C2=1
[0091] 5 #SSCN:[1,1,24];I1(T1);btr_scan(1);is_global(0)
[0092] The steps of this optimization method are as follows:
[0093] 1. Perform syntax analysis and semantic analysis on the SQL statement (SQL statement to be processed) input by the user to generate a logical plan relationship tree and proceed to step 2.
[0094] 2. Find the SORT node (sort node, that is, the node corresponding to the ORDER BY clause (sort clause)) in the logical plan relationship tree and go to step 3.
[0095] 3. Find the child node of the SORT node (sort node) according to the logical plan relationship tree, set the child node as the current node, and judge according to the type of the current node:
[0096] a) If the current node is a SLCT node (a selection node, i.e., a node corresponding to the WHERE filter condition), jump to step 4;
[0097] b) If the current node is a table scan node (i.e., the node corresponding to the FROM item, such as
[0098] CSCN / SSCN, etc.), then jump to step 5;
[0099] c) In other cases, execute step 3 again.
[0100] 4. The current node is a SLCT node (selection node). Collect the equivalent filter conditions in the form of "column = constant" in the filter conditions, put the columns involved into the target linked list, and the target linked list is used to store the equivalent filter conditions, and jump to step 3.
[0101] 5. The current node is a table scan node. If the target linked list is empty, it means that no data in the form of "column =
[0102] If the target list is not empty, the sorting field (ie ORDER BY) of the SORT node is set according to the target column in the target list.
[0103] The corresponding columns in the clause are completed according to the index field of the index used by the current node. Because the final decision is whether the sort field and the index field are consistent, the completion operation here needs to make the sort field and the equivalent condition column in the target linked list as close to the form of the index field as possible.
[0104] (For example, the sorting field is C1 C3, the equal value condition column is C2, and the index field is C1 C2
[0105] C3, when completing, C2 is not placed at any position, but is placed between C1 and C3 with reference to the index field. After completion, it is C1 C2 C3), jump to step 6.
[0106] 6. Determine whether the index field of the current node index is consistent with the sort field of the current SORT node. If they are consistent, the sort can be eliminated and the SORT node can be removed from the logical plan relationship tree. If they are inconsistent, the sort cannot be eliminated, the optimization is exited, and jump to step 7.
[0107] 7. Continue to execute SQL (pending SQL statements) according to the current logical plan relationship tree until it ends.
[0108] Based on the above operation steps, the occupation and consumption of resources in the sorting step are reduced, and the query statement is optimized. In this application example, the filter condition T1.C2=1 in the SLCT node is collected, so T1.C2 is put into the linked list LIST. The sorting fields are C1 and C3, and the index fields are C1, C2, and C3. The two are inconsistent, so the sorting cannot be eliminated directly. After the sorting field is completed according to the C2 column in the linked list LIST, it is consistent with the index field, and the sorting can be eliminated at this time. The way to complete the sorting field is not limited to the missing field being in the middle of the index field. It can also be completed when it belongs to the first column or the first few columns of the index field. For example, the sorting fields are C2 and C3, the index fields are C1, C2, and C3, and the equal value filtering condition column is C1; the sorting field is C3, the index fields are C1, C2, and C3, and the equal value filtering condition column is C1 and C2; the sorting fields are C2 and C4, the index fields are C1, C2, C3, and C4, and the equal value filtering condition column is C1 and C3, and so on. In the above cases, after the sorting fields are completed according to the equal-value filtering condition columns in the linked list LIST, they are all consistent with the index fields, so the sorting can be eliminated.
[0109] The expected execution plan after eliminating the sort is as follows:
[0110] 1 #NSET2:[1,1,24]
[0111] 2 #PRJT2:[1,1,24];exp_num(2),is_atom(FALSE)
[0112] 3 #SLCT2:[1,1,24]; T1.C2=1
[0113] 4 #SSCN:[1,1,24];I1(T1);btr_scan(1);is_global(0)
[0114] Embodiment 2
[0115] Figure 3 is a structural diagram of an optimization device for eliminating sorting of SQL statements provided in the second embodiment of the present disclosure; Figure 3 As shown, the device includes: a current node determination module 210, a target sorting field determination module 220, and an optimization module 230.
[0116] The current node determination module 210 is used to determine the current node based on the logical plan relationship tree; the current node is a child node of the sorting node; the logical plan relationship tree is determined based on the structured query language SQL statement to be processed;
[0117] The target sorting field determination module 220 is used to determine the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list and the index field of the current node index if the node type of the current node is a table scan node; the target column is a column in the target linked list that meets the equal value filtering condition;
[0118] The optimization module 230 is used to determine whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed.
[0119] The second embodiment of the present disclosure provides an optimization device for eliminating sorting of SQL statements, thereby reducing the occupation and consumption of resources by the sorting step and optimizing the query statements.
[0120] Furthermore, the target sorting field determination module 220 is further configured to:
[0121] When the linked list state of the target linked list is not empty, the sorting field corresponding to the sorting node is completed based on the target column in the target linked list and the index field of the current node index, and the sorting field corresponding to the completed sorting node is determined as the target sorting field;
[0122] When the linked list state of the target linked list is empty, the sorting field corresponding to the sorting node is determined as the target sorting field.
[0123] Furthermore, the optimization module 230 is also used for:
[0124] When the index field is consistent with the target sort field, eliminating the sort corresponding to the sort clause corresponding to the sort node;
[0125] In the case that the index field is inconsistent with the target sort field, the sort corresponding to the sort clause corresponding to the sort node is not eliminated.
[0126] Further, after eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the device further includes:
[0127] The removal execution module is used to remove the sorting node from the logical plan relationship tree, and execute the SQL statement to be processed based on the logical plan relationship tree after the sorting node is removed.
[0128] After not eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the method further includes:
[0129] An execution module is used to execute the SQL statement to be processed based on the logical plan relationship tree.
[0130] Furthermore, the device also includes:
[0131] A node type determination unit, used to determine the node type of the current node;
[0132] The execution unit is returned, and is used to designate the child node of the current node as the new current node if the node type of the current node is a selection node, and return to the step of determining the node type of the current node.
[0133] Furthermore, the device also includes:
[0134] A target filtering condition determination module, configured to determine a target filtering condition from a preset filtering condition set if the node type of the current node is a selection node, wherein the target filtering condition is an equal value filtering condition;
[0135] A storage module is provided, and the target column corresponding to the target filtering condition is stored in the target linked list.
[0136] The SQL statement elimination sorting optimization device provided in the embodiments of the present disclosure can execute the SQL statement elimination sorting optimization method provided in any embodiment of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method.
[0137] Embodiment 3
[0138] Figure 3A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the embodiments of the present disclosure described and / or claimed herein.
[0139] like Figure 3 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0140] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.
[0141] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microprocessor, etc. The processor 11 executes the various methods and processes described above, such as an optimization method for eliminating sorting of SQL statements.
[0142] In some embodiments, the optimization method for eliminating and sorting SQL statements may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the optimization method for eliminating and sorting SQL statements described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to execute the optimization method for eliminating and sorting SQL statements in any other appropriate manner (e.g., by means of firmware).
[0143] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.
[0144] The computer programs for implementing the methods of the embodiments of the present disclosure may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine as a stand-alone software package and partially on a remote machine, or entirely on a remote machine or server.
[0145] In the context of the disclosed embodiments, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, device, or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.
[0146] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).
[0147] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.
[0148] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.
[0149] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps recorded in the embodiments of the present disclosure can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solutions of the embodiments of the present disclosure can be achieved, and this document does not limit this.
[0150] The above specific implementations do not constitute a limitation on the protection scope of the embodiments of the present disclosure. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the embodiments of the present disclosure shall be included in the protection scope of the embodiments of the present disclosure.
[0151] The embodiments of the present disclosure also provide a computer program product, including a computer program and / or instructions, which, when executed by a processor, implements the optimization method for eliminating sorting of SQL statements as provided in any embodiment of the present application.
[0152] In the process of implementation, the computer program product can be written in one or more programming languages or a combination thereof to write computer program codes for performing the operation of the disclosed embodiments, the programming languages including object-oriented programming languages, such as Java, Smalltalk, C++, and also including conventional procedural programming languages, such as "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as an independent software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the case of a remote computer, the remote computer can be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computer (e.g., using an Internet service provider to connect through the Internet).
[0153] Note that the above are only preferred embodiments of the embodiments of the present disclosure and the technical principles used. Those skilled in the art will understand that the embodiments of the present disclosure are not limited to the specific embodiments herein, and that various obvious changes, readjustments and substitutions can be made by those skilled in the art without departing from the protection scope of the embodiments of the present disclosure. Therefore, although the embodiments of the present disclosure are described in more detail through the above embodiments, the embodiments of the present disclosure are not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of the embodiments of the present disclosure, and the scope of the embodiments of the present disclosure is determined by the scope of the attached claims.
Claims
1. An optimization method for eliminating sorting of SQL statements, characterized in that: include: Determine the current node based on the logical plan relationship tree; The current node is a child node of the sorting node; The logical plan relationship tree is determined based on the structured query language SQL statement to be processed; If the node type of the current node is a table scan node, determining the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list, and the index field of the current node index; The target column is a column in the target linked list that satisfies an equal value filtering condition; Based on the index field and the target sort field, it is determined whether to eliminate the sort corresponding to the sort clause corresponding to the sort node, so as to optimize the SQL statement to be processed.
2. The method according to claim 1, characterized in that The determining the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list, and the index field of the current node index includes: When the linked list state of the target linked list is not empty, the sorting field corresponding to the sorting node is completed based on the target column in the target linked list and the index field of the current node index, and the sorting field corresponding to the completed sorting node is determined as the target sorting field; When the linked list state of the target linked list is empty, the sorting field corresponding to the sorting node is determined as the target sorting field.
3. The method according to claim 1, characterized in that The determining whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field includes: When the index field is consistent with the target sort field, eliminating the sort corresponding to the sort clause corresponding to the sort node; In the case that the index field is inconsistent with the target sort field, the sort corresponding to the sort clause corresponding to the sort node is not eliminated.
4. The method according to claim 3, characterized in that After eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the method further includes: Removing the sorting node from the logical plan relationship tree, and executing the SQL statement to be processed based on the logical plan relationship tree after removing the sorting node; After not eliminating the sorting corresponding to the sorting clause corresponding to the sorting node, the method further includes: The SQL statement to be processed is executed based on the logical plan relationship tree.
5. The method according to claim 1, characterized in that: After determining the current node based on the logical plan relationship tree, the method further includes: Determine the node type of the current node; If the node type of the current node is a selection node, the child node of the current node is designated as a new current node, and the process returns to the step of determining the node type of the current node.
6. The method according to claim 1, characterized in that The method further comprises: If the node type of the current node is a selection node, a target filtering condition is determined from a preset filtering condition set, and the target filtering condition is an equal value filtering condition; The target column corresponding to the target filtering condition is stored in the target linked list.
7. An optimization device for eliminating sorting of SQL statements, characterized in that: include: A current node determination module, used to determine the current node based on the logical plan relationship tree; The current node is a child node of the sorting node; The logical plan relationship tree is determined based on the structured query language SQL statement to be processed; A target sorting field determination module is used to determine the target sorting field corresponding to the sorting node based on the linked list state of the target linked list, the target column in the target linked list and the index field of the current node index if the node type of the current node is a table scan node; The target column is a column in the target linked list that satisfies an equal value filtering condition; An optimization module is used to determine whether to eliminate the sorting corresponding to the sorting clause corresponding to the sorting node based on the index field and the target sorting field, so as to optimize the SQL statement to be processed.
8. An electronic device, characterized in that: include: at least one processor; as well as a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the optimization method for eliminating sorting of SQL statements as described in any one of claims 1-6.
9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the optimization method for eliminating sorting of SQL statements as described in any one of claims 1 to 6 is implemented.
10. A computer program product, characterized in that The computer program product comprises a computer program, and when the computer program is executed by a processor, the computer program implements the optimization method for eliminating sorting of SQL statements as claimed in any one of claims 1 to 6.