Data sorting method and device, equipment, storage medium and program product

By determining the target sorting field set based on the relationship tree and index field set in SQL, and sorting the remaining fields in groups, the performance degradation caused by some ordered fields is solved, and more efficient data sorting is achieved.

CN118939697APending Publication Date: 2024-11-12SHANGHAI DAMENG DATABASE
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410964231.8
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-18
Publication Date
2024-11-12

AI Technical Summary

Technical Problem

In SQL, when the fields specified by the ORDER BY clause are only partially ordered, sorting optimization cannot be eliminated, resulting in a degradation in execution performance.

Method used

By determining the sorted field set of the target node based on the relationship tree of the SQL statement to be optimized, and determining the target sorted field set based on the leading column of the indexed field set and the second sorted field set, the remaining sorted field set is then grouped and sorted, reducing memory consumption , improve data sorting efficiency.

Benefits of technology

It realizes reducing memory consumption, reducing the possibility of flashing disks, improving execution performance, and eliminating unnecessary sorting operations in the case of some ordered fields.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939697A_ABST
    Figure CN118939697A_ABST
Patent Text Reader

Abstract

The embodiment of the invention provides a data sorting method and device, equipment, a storage medium and a program product. The method comprises the steps of determining a first sorting field set and a second sorting field set of a target node based on a relation tree corresponding to a to-be-optimized SQL statement; obtaining an index field set based on the child nodes of the target node; determining a target sorting field set based on a leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to a leading column of the index field set, or the target sorting field set is the same as the index field set; determining a residual sorting field set according to the target sorting field set and the first sorting field set; and grouping and sorting the data corresponding to the residual sorting field sets according to the target sorting field set. According to the embodiment of the invention, the sorting operator does not need to process the data corresponding to the target sorting field set, so that the memory consumption can be reduced, the data sorting efficiency is improved, and the execution performance is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a data sorting method, device, equipment, storage medium and program product. Background Art

[0002] Sorting of Structured Query Language (SQL) statements refers to using the ORDER BY clause in SQL to reorder the result set so that it is ordered by the specified fields. When the fields specified in the ORDER BY clause are already ordered, the execution performance can usually be improved by eliminating the sorting optimization; however, if the fields specified in the ORDER BY clause are only partially ordered, this sorting cannot be eliminated. Summary of the invention

[0003] The present invention provides a data sorting method, device, equipment, storage medium and program product, which can reduce memory consumption and improve execution performance.

[0004] In a first aspect, an embodiment of the present disclosure provides a data sorting method, comprising: determining a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to a SQL statement to be optimized; wherein the relationship tree is obtained by a logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is a node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; obtaining an index field set based on a child node of the target node; determining a target sorting field set based on a leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to a leading column of the index field set, or the target sorting field set is the same as the index field set; determining a remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; and grouping and sorting data corresponding to the remaining sorting field set according to the target sorting field set.

[0005] In a second aspect, an embodiment of the present disclosure further provides a data sorting device, comprising: a sorting field set determination module, configured to determine a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to a SQL statement to be optimized; wherein the relationship tree is obtained by a logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is a node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; an index field set acquisition module, configured to acquire an index field set based on a child node of the target node; a target sorting field set determination module, configured to determine a target sorting field set based on a leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to a leading column of the index field set, or the target sorting field set is the same as the index field set; a remaining sorting field set determination module, configured to determine a remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; and a grouping and sorting module, configured to group and sort data corresponding to the remaining sorting field set according to the target sorting field set.

[0006] In a third aspect, an embodiment of the present disclosure further provides an electronic device, comprising: one or more processors; a storage device for storing one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the data sorting method as described in the embodiment of the present disclosure.

[0007] In a fourth aspect, an embodiment of the present disclosure further provides a storage medium comprising computer executable instructions, which, when executed by a computer processor, are used to execute the data sorting method as described in the embodiment of the present disclosure.

[0008] In a fifth aspect, an embodiment of the present disclosure further provides a computer program product, including a computer program, which, when executed by a processor, implements the data sorting method as described in the embodiment of the present disclosure.

[0009] The technical solution of the embodiment of the present disclosure is to determine the first sorting field set and the second sorting field set of the target node based on the relationship tree corresponding to the SQL statement to be optimized; wherein the relationship tree is obtained by the logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is the node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; an index field set is obtained based on the child nodes of the target node; a target sorting field set is determined based on the leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set; a remaining sorting field set is determined according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; and data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set. In the disclosed embodiment, a target sorting field set is determined based on the leading column of the index field set and the second sorting field set, and the data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set, so that the sorting operator does not need to process the data corresponding to the target sorting field set, thereby reducing memory consumption, improving data sorting efficiency, and enhancing execution performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0010] The above and other features, advantages and aspects of the embodiments of the present disclosure will become more apparent with reference to the following detailed description in conjunction with the accompanying drawings. Throughout the accompanying drawings, the same or similar reference numerals represent the same or similar elements. It should be understood that the drawings are schematic and the originals and elements are not necessarily drawn to scale.

[0011] Figure 1 A schematic diagram of a data sorting method flow chart provided by an embodiment of the present invention;

[0012] Figure 2 A schematic diagram of another data sorting method flow chart provided by an embodiment of the present invention;

[0013] Figure 3 A schematic diagram of the structure of a data sorting device provided by an embodiment of the present invention;

[0014] Figure 4 The present invention is a schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0015] Embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although certain embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be construed as being limited to the embodiments described herein, which are instead provided for a more thorough and complete understanding of the present disclosure. It should be understood that the drawings and embodiments of the present disclosure are only for exemplary purposes and are not intended to limit the scope of protection of the present disclosure.

[0016] It should be understood that the various steps recorded in the method implementation of the present disclosure can be performed in different orders and / or in parallel. In addition, the method implementation may include additional steps and / or omit the steps shown. The scope of the present disclosure is not limited in this respect. The term "including" and its variations used herein are open inclusions, that is, "including but not limited to". It should be noted that the concepts of "first", "second", etc. mentioned in the present disclosure are only used to distinguish different devices, modules or units, and are not used to limit the order or interdependence of the functions performed by these devices, modules or units. It should be noted that the modifications of "one" and "multiple" mentioned in the present disclosure are illustrative and not restrictive, and those skilled in the art should understand that unless otherwise clearly stated in the context, it should be understood as "one or more". It is understandable that the data involved in the present technical solution (including but not limited to the data itself, the acquisition or use of the data) shall comply with the requirements of the corresponding laws, regulations and relevant provisions.

[0017] Figure 1 The present invention provides a flow chart of a data sorting method according to an embodiment of the present invention. The present invention is applicable to the case of sorting data. The method can be executed by a data sorting device, which can be implemented in the form of software and / or hardware. Optionally, it can be implemented by an electronic device, which can be a mobile terminal, a PC or a server. Figure 1 As shown, the method includes:

[0018] S110 . Determine a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to the SQL statement to be optimized.

[0019] The relationship tree is obtained from the logical execution plan of the SQL statement to be optimized.

[0020] The SQL statement to be optimized may include a sorting statement, such as an ORDER BY statement, and may include other SQL statements, such as a query statement, on the basis of the sorting statement. The target node is the node corresponding to the sorting statement in the relationship tree. The target node may also be understood as the node corresponding to the sorting statement in the execution phase, or the node corresponding to the sorting statement in the logical execution plan.

[0021] The first sorting field set is the same as the second sorting field set. However, in the process of executing the data sorting method of the embodiment of the present invention, the first sorting field set remains unchanged, and the second sorting field set can be changed.

[0022] Optionally, determining the sorting field of the target node based on the relationship tree corresponding to the SQL statement to be optimized includes: searching for a logical execution plan corresponding to the SQL statement to be optimized; determining the relationship tree corresponding to the SQL statement to be optimized according to the logical execution plan; determining the target node based on the relationship tree corresponding to the SQL statement to be optimized; and obtaining a sorting field set corresponding to the target node as a first sorting field set and a second sorting field set.

[0023] In the embodiment of the present invention, the logical execution plan corresponding to the SQL statement to be optimized can be found by using the setting command, so that the relationship tree corresponding to the SQL statement to be optimized can be determined according to the logical execution plan, and then the target node can be found. Before using the setting command to find the logical execution plan corresponding to the SQL statement to be optimized, data construction and index creation are also included. For example, data construction:

[0024] DROP TABLE T1; #Delete table T1;

[0025] CREATE TABLE T1(C1 INT, C2 INT, C3 INT); #Create a new table T1 containing three integer columns: C1, C2, C3;

[0026] INSERT INTO T1 SELECT DBMS_RANDOM.VALUE(1,10),DBMS_RANDOM.VALUE(1,10),DBMS_RANDOM.VALUE(1,10)FROM DUAL CONNECT BYLEVEL<=20; #Insert 20 rows of data into the T1 table. Each row of data consists of three random numbers generated by DBMS_RANDOM.VALUE(1,10).

[0027] COMMIT; #Commit the transaction to ensure that the previous insert operation is permanently saved in the database.

[0028] SELECT * FROM T1; #Query all data in table T1.

[0029] Create an index:

[0030] CREATE INDEX I1 ON T1(C1,C2,C3); #Create a composite index named I1 on table T1, which contains three columns: C1, C2 and C3;

[0031] The expected results when scanning data using this index are as follows:

[0032]

[0033]

[0034] The SQL statements to be optimized can be:

[0035] SELECT C1,C2,C3 FROM T1 ORDER BY C1,C3,C2; #Select all data in columns C1, C2, and C3 from table T1, and sort them in the order of C1, C3, C2.

[0036] For example, the setting command takes explain as an example, and the SQL statement to be optimized takes SELECT C1, C2, C3 FROM T1 ORDER BY C1, C3, C2 as an example.

[0037] View the logical execution plan of SELECT C1, C2, C3 FROM T1 ORDER BY C1, C3, C2 (which can also be understood as a relationship tree):

[0038] #NSET2:[1,20,24]

[0039] #PRJT2:[1,20,24];exp_num(4),is_atom(FALSE)

[0040] #SORT3:[1,20,24];key_num(3),partition_key_num(1),is_distinct(FALSE),top_flag(0),is_adaptive(0)

[0041] #SSCN:[1,20,24];I1(T1);btr_scan(1);is_global(0)

[0042] Among them, NSET2 can be understood as the root node, PRJT2 is the child node of NSET2, SORT3 is the child node of PRJT2, and SSCN is the child node of SORT3. SORT3 is the target node, which can also be understood as a sorting node (ORDER node). SSCN is a table scan node, which uses index I1 to scan the T1 table.

[0043] After the relationship tree is determined, the ORDER node corresponding to the sort statement can be found in the relationship tree, that is, the target node SORT3, and the sort field set of SORT3 is (C1, C3, C2). In this embodiment, the sort field set of SORT3 can also be used as the first sort field set and the second sort field set.

[0044] S120: Acquire an index field set based on the child nodes of the target node.

[0045] In this embodiment, after the target node is found, the child nodes of the target node may be found, so that the index field set of the child nodes of the target node may be directly acquired.

[0046] Exemplarily, still taking the above target node SORT3 as an example, the child node of SORT3 is SSCN, and the index field set of SSCN is (C1, C2, C3).

[0047] Optionally, obtaining an index field set based on the child nodes of the target node includes: detecting the child nodes of the target node; if the child node is a table scan node, obtaining the index field set corresponding to the child node; if the child node is a data order maintaining node, continuing to detect the child nodes of the target node; if the child node is a data order changing node, stopping detection.

[0048] Among them, detection can be understood as traversing and checking the nodes in the database query logic execution plan. Among them, the data order maintenance node can be understood as a node whose data order (or sorting) will not change during the execution process, such as a projection node (PRJT), a filter node (SLCT), etc. The data order change node can be understood as a node that changes the original order of the data during the execution process, such as a sort (SORT) node or a connection (JOIN) node, which will also change the order of the data in some cases. In this embodiment, it can be determined whether to continue to detect or stop detecting according to the type of the node (whether it is a table scan node, whether it is a data order maintenance node, and a data order change node). For example, in the query logic execution plan, if the detected child node is a table scan node, the index field set corresponding to the child node is obtained. If a data order maintenance node is detected, the child nodes of the target node continue to be detected downward; if the child node is detected as a data order change node, the detection is stopped.

[0049] S130: Determine a target sorting field set based on the leading column of the index field set and the second sorting field set.

[0050] The target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set.

[0051] In this embodiment, the target sorting field set can be determined by judging whether the second sorting field set belongs to the leading column of the index field set, or whether it is the same as the index field set. For example, if the second sorting field set belongs to the leading column of the index field set, or is the same as the index field set, the second sorting field set can be used as the target sorting field set. If the second sorting field set does not belong to the leading column of the index field set and is not the same as the index field set, the target sorting field set is determined by removing the last field in the second sorting field set. If the set after removing the last field in the second sorting field set belongs to the leading column of the index field set, or is the same as the index field set, the set after removing the last field in the second sorting field set can be used as the target sorting field set.

[0052] Optionally, determining a target sorting field set based on the leading column of the index field set and the second sorting field set includes: if the second sorting field set belongs to the leading column of the index field set, or the second sorting field set is the same as the index field set, then the detection ends, and the second sorting field set is used as the target sorting field set; if the second sorting field set does not belong to the leading column of the index field set and the second sorting field set is different from the index field set, then removing the trailing field of the second sorting field set to obtain a new second sorting field set; if the new second sorting field set is not empty, then taking the new second sorting field set as the second sorting field set, returning to the step of obtaining the index field set based on the child node of the target node, and re-executing.

[0053] Exemplarily, the second sorting field set still takes the sorting field set (C1, C3, C2) of SORT3 as an example, and the index field set still takes (C1, C2, C3) as an example. The leading columns of the index field set (C1, C2, C3) are (C1), (C1, C2), (C1, C2, C3) respectively. The second sorting field set (C1, C3, C2) does not belong to the leading column of the index field set (C1, C2, C3), and the second sorting field set (C1, C3, C2) is different from the index field set (C1, C2, C3), so it can be considered that the second sorting field set (C1, C3, C2) is out of order with respect to the index field set (C1, C2, C3). Remove the last field of the second sorting field set (C1, C3, C2), that is, remove C2, and obtain the remaining sorting field set (C1, C3), that is, the new second sorting field set (C1, C3).

[0054] Since the new second sorting field set (C1, C3) is not empty, the child nodes can be recursively detected starting from the ORDER node, and the new second sorting field set is used as the second sorting field set. The step of obtaining the index field set based on the child nodes of the target node is returned: the child nodes of the target node are detected; if the child node is a table scan node, the index field set corresponding to the child node is obtained;

[0055] If the child node is a data order maintaining node, continue to detect the child nodes of the target node; if the child node is a data order changing node, stop detecting. And perform the step of determining the target sorting field set based on the leading column of the index field set and the second sorting field set.

[0056] Since the child node is SSCN, it is a table scan node, its used index is I1, and the index field set is (C1, C2, C3). The second sort field set (C1, C3) does not belong to the leading column of the index field set (C1, C2, C3), and the second sort field set (C1, C3) is different from the index field set (C1, C2, C3), so it is considered that the second sort field set (C1, C3) is out of order for the index field set (C1, C2, C3). Remove the last field of the second sort field set (C1, C3), that is, remove C3, and get the remaining sort field set (C1), which is the new second sort field set (C1).

[0057] Since the new second sorting field set (C1) is not empty, the child nodes can be recursively detected starting from the ORDER node, and the new second sorting field set is used as the second sorting field set, and the step of obtaining the index field set based on the child nodes of the target node is returned to execute: the child nodes of the target node are detected; if the child node is a table scan node, the index field set corresponding to the child node is obtained;

[0058] If the child node is a data order maintaining node, continue to detect the child nodes of the target node; if the child node is a data order changing node, stop detecting. And perform the step of determining the target sorting field set based on the leading column of the index field set and the second sorting field set.

[0059] Since the child node is SSCN, it is a table scan node, and its used index is I1, and the index field set is (C1, C2, C3). The second sort field set (C1) is different from the index field set (C1, C2, C3), but the second sort field set (C1) belongs to the leading column of the index field set (C1, C2, C3), so it is considered that the second sort field set (C1) is ordered for the index field set (C1, C2, C3), the detection is completed, and the second sort field set (C1) is used as the target sort field set. At the same time, it is considered that the data received by the ORDER node is ordered according to the target sort field set (C1), and the data received by the ORDER node is the data output by its child node, which is the data output by the SSCN node in this embodiment. From the expected results of the index scanning data in the above embodiment, it can be seen that it is indeed ordered according to the C1 column.

[0060] Optionally, the method further includes: if the new second sorting field set is empty, performing a sorting operation on the data corresponding to the sorting field set according to the original sorting method.

[0061] The original sorting method may be understood as sorting the specified fields according to the ORDER BY statement, without using the data sorting method provided by the embodiment of the present invention.

[0062] In this embodiment, if the new second sorting field set is empty, it indicates that the new second sorting field set cannot be optimized, and the entire optimization process is terminated and sorting is performed in a normal manner, that is, the sorting operation is performed on the data corresponding to the sorting field set according to the original sorting method, and the data sorting method provided in the embodiment of the present invention is not executed.

[0063] Optionally, after determining the target sorting field set based on the leading column of the index field set and the sorting field set, it also includes: recording the number of sorting fields in the target sorting field set; if the number of sorting fields in the target sorting field set is the same as the number of sorting fields in the first sorting field set, removing the sorting statement so as not to perform any sorting operation on the data corresponding to the sorting field.

[0064] In this embodiment, recording the number of sorting fields in the target sorting field set can also be considered as recording the number of ordered leading columns. If the target sorting field set is (C1) as an example, the number of ordered leading columns is 1.

[0065] In this embodiment, if the number of sorting fields in the target sorting field set is the same as the number of sorting fields in the first sorting field set, it is considered that the sorting can be eliminated, and the sorting statement can be directly removed, that is, the ORDER node can be directly removed, and no subsequent sorting operation needs to be performed.

[0066] Exemplarily, sorting can be eliminated. The index field set is taken as (C1, C2, C3) as an example, the SQL statement to be optimized is taken as SELECT C1 FROM T1 ORDER BY C1, C2, C3 as an example, and the target sorting field set is taken as (C1, C2, C3) as an example. Since the C1, C2, C3 fields (including the order of the fields) specified by ORDER BY, that is, the sorting field set (C1, C2, C3) is consistent with the index field set (C1, C2, C3), not making an ORDER BY clause has no effect on the result set, so sorting can be eliminated.

[0067] S140: Determine a remaining sorting field set according to the target sorting field set and the first sorting field set.

[0068] The first sorting field set includes the target sorting field set and the remaining sorting field set.

[0069] For example, if the first sorting field set is (C1, C3, C2) and the target sorting field set is (C1), then the remaining sorting field set can be expressed as (C3, C2).

[0070] S150: Group and sort the data corresponding to the remaining sorting field set according to the target sorting field set.

[0071] In this embodiment, the data corresponding to the remaining sort field set can be grouped and sorted according to the target sort field set. If the two adjacent rows of data corresponding to the sort fields in the target sort field set are the same, then the two adjacent rows of data corresponding to the remaining sort field set (the same as the two adjacent rows corresponding to the sort fields in the target sort field set) are the same group. If the two adjacent rows of data corresponding to the sort fields in the target sort field set are not the same, then the two adjacent rows of data corresponding to the remaining sort field set are different groups, and the latter row of data or the former row of data in the corresponding two adjacent rows of data belongs to a new group. In this embodiment, after obtaining the new group of data, the saved previous group of data can be sorted. After the sorting is completed, the sorted data of the previous group is output. And the grouping and sorting are continued until all data are processed, the last group of data is sorted and output, and the grouping and sorting are ended.

[0072] Optionally, grouping and sorting the data corresponding to the remaining sorting field set according to the target sorting field set includes: comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer, initially 1; when N is 1, saving the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; if the N+1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are in the same group, and saving the N+1th row of data corresponding to the remaining sorting field set, and the N+1th row of data corresponding to the remaining sorting field set belongs to the previous group; if If the N+1th row of data corresponding to the sorting field is different from the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N+1th row of data corresponding to the remaining sorting field set is a new group; sort the saved previous group of data, and output the sorted previous group of data; save the N+1th row of data corresponding to the remaining sorting field set; if N+1 is not the first set value, take N+1 as N, the new group as the previous group, return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute; if N+1 is the first set value, sort the saved previous group of data, and output the sorted new group of data.

[0073] Exemplarily, the target sorting field set (C1) is still taken as an example. The remaining sorting field set is taken as (C3, C2) and the first set value is taken as 20. The number of ordered leading columns recorded by the ORDER node is 1, so it is considered that the first column of the received data is ordered, that is, the C1 column is ordered in this embodiment, and the C1 data needs to be grouped. Initially, N is 1. The first row of data corresponding to the remaining sorting field set is saved, and the first row of data corresponding to the remaining sorting field set belongs to the previous group.

[0074] First, N is 1, N+1 is 2, and the C1 columns of the 1st row and the 2nd row are compared. The values ​​are 1 and 2 respectively. The data of the 1st row and the 2nd row corresponding to the C1 field are different. It is considered that the 2nd row of data corresponding to the remaining sorting field set belongs to the new group. The previous group (such as the first row of data corresponding to C2 is 9, and the first row of data corresponding to the C3 column is 5) is sorted according to the remaining sorting field set. After the previous group is sorted, it is output and the subsequent data is processed. The second row of data corresponding to the remaining sorting field set is saved; since N+1 is 2 and not 20, that is, it is not the last row, N+1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0075] Next, N is 2, N+1 is 3, and the 3rd row of data in the C1 column is compared with the 2nd row of data. The values ​​are 2 and 2 respectively. The 2nd and 3rd rows of data corresponding to the C1 field are the same. It is considered that the 2nd and 3rd rows of data corresponding to the sorting field in the target sorting field set belong to the same group, and the 3rd row of data corresponding to the remaining sorting field set is saved. The 3rd row of data corresponding to the remaining sorting field set belongs to the previous group; continue to process subsequent data. Since N+1 is 3 and not 20, that is, it is not the last row, N+1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0076] Next, N is 3, N+1 is 4, and the 4th row of data in the C1 column is compared with the 3rd row of data. The values ​​are 2 and 2 respectively. The 3rd row and 4th row of data corresponding to the C1 field are the same. It is considered that the 3rd row and 4th row of data corresponding to the sorting field in the target sorting field set belong to the same group, and the 4th row of data corresponding to the remaining sorting field set is saved. The 4th row of data corresponding to the remaining sorting field set belongs to the previous group; continue to process subsequent data. Since N+1 is 4 and not 20, that is, it is not the last row, N+1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0077] Next, N is 4, N+1 is 5, and the 5th row of data in the C1 column is compared with the 4th row of data. The values ​​are 3 and 2 respectively. The 5th row of data corresponding to the C1 field is different from the 4th row of data. It is considered that the 5th row of data corresponding to the remaining sorting field set belongs to the new group. The previous group (i.e., the group with the C1 column value of 2) is sorted and output according to the remaining sorting field set, and the subsequent data is processed. Since N+1 is 5 and not 20, that is, it is not the last row, N+1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0078] Similarly, the data in the N+1th row is compared with the C1 column value of the Nth row to determine whether the data in the N+1th row is a new group until the 20th row is processed. Since there is no subsequent data, the group with the C1 column value of 10 (such as the 19th and 20th rows of data corresponding to the C2 field are 4 and 6 respectively, and the 19th and 20th rows of data corresponding to the C3 field are 3 and 10 respectively) is sorted and output according to the remaining sorting field set (C3, C2), and the sorting is completed.

[0079] Exemplarily, the sorting result after grouping and sorting the data corresponding to the remaining sorting field set according to the target sorting field set is as follows:

[0080]

[0081]

[0082] In this embodiment, since the previous group of data is sorted according to the remaining sorting field set and the sorted previous group of data is output after each new group is generated, there is no need to retain the previous group of data, which saves memory, reduces storage space, and improves data sorting efficiency.

[0083] Optionally, grouping and sorting the data corresponding to the remaining sorting field set according to the target sorting field set includes: comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer, initially the first set value; when N is the first set value, saving the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; if the N-1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the Nth row of data corresponding to the remaining sorting field set and the N-1th row of data are in the same group, and saving the N-1th row of data corresponding to the remaining sorting field set, and the N-1th row of data corresponding to the remaining sorting field set belongs to the previous group; if the target sorting field set If the N-1th row of data corresponding to the sorting field in the sorting field set is different from the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N-1th row of data are in different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N-1th row of data corresponding to the remaining sorting field set is a new group; sort the saved previous group of data, and output the sorted previous group of data; save the N-1th row of data corresponding to the remaining sorting field set; if N-1 is not the second set value, take N-1 as N, the new group as the previous group, return to the step of comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute; if N-1 is the second set value, sort the saved new group of data, and output the sorted new group of data.

[0084] Exemplarily, the target sorting field set (C1) is still taken as an example. The remaining sorting field set is taken as (C3, C2) as an example, the first set value is taken as 20 as an example, and the second set value is taken as 1 as an example. The number of ordered leading columns recorded by the ORDER node is 1, so it is considered that the first column of the received data is ordered, that is, the C1 column is ordered in this embodiment, and the C1 data needs to be grouped. Initially, N is 20. The 20th row of data corresponding to the remaining sorting field set is saved, and the 20th row of data corresponding to the remaining sorting field set belongs to the previous group.

[0085] First, N is 20, N-1 is 19, and the C1 columns of the 20th row and the 19th row are compared. The values ​​are 10 and 10 respectively. The data of the 19th row and the 20th row corresponding to the C1 field are the same. It is considered that the 19th row and the 20th row corresponding to the sorting field in the target sorting field set belong to the same group, and the 19th row of data corresponding to the remaining sorting field set is saved. The 19th row of data corresponding to the remaining sorting field set belongs to the previous group; continue to process subsequent data. Since N-1 is 19 and not 1, that is, it is not the first row, N-1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0086] Next, N is 19, N-1 is 18, and the 18th row of data in the C1 column is compared with the 19th row of data. The values ​​are 9 and 10 respectively. The 18th row of data corresponding to the C1 field is different from the 19th row of data. It is considered that the 18th row of data corresponding to the remaining sorting field set belongs to the new group. According to the remaining sorting field set, the group with a C1 column value of 10 (including the 19th and 20th rows of data corresponding to the C2 field, which are 4 and 6 respectively, and the 19th and 20th rows of data corresponding to the C3 field, which are 3 and 10 respectively) is sorted. After the previous group is sorted, it is output and the subsequent data is processed. The 18th row of data corresponding to the remaining sorting field set is saved. Since N-1 is 18 and not 1, that is, it is not the first row, N-1 is taken as N, and the new group is taken as the previous group. Return to the step of comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute.

[0087] Similarly, the data in the N-1th row is compared with the C1 column value of the Nth row to determine whether the data in the N-1th row is a new group until the first row is processed. Since there is no subsequent data, the group with the C1 column value of 1 (such as the first row of data corresponding to C2 is 9, and the first row of data corresponding to C3 is 5) is sorted and output according to the remaining sorting field set (C3, C2), and the sorting is completed.

[0088] In this embodiment, since the previous group of data is sorted according to the remaining sorting field set and the sorted previous group of data is output after each new group is generated, there is no need to retain the previous group of data, which saves memory, reduces storage space, and improves data sorting efficiency.

[0089] The technical solution of the embodiment of the present disclosure is to determine the first sorting field set and the second sorting field set of the target node based on the relationship tree corresponding to the SQL statement to be optimized; wherein the relationship tree is obtained by the logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is the node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; an index field set is obtained based on the child nodes of the target node; a target sorting field set is determined based on the leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set; a remaining sorting field set is determined according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; and data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set. In the disclosed embodiment, a target sorting field set is determined based on the leading column of the index field set and the second sorting field set, and the data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set, so that the sorting operator does not need to process the data corresponding to the target sorting field set. Compared with the prior art method that requires sorting the entire amount of data, the embodiment of the present invention can reduce memory consumption, improve data sorting efficiency, reduce the possibility of disk flushing, and improve execution performance.

[0090] Figure 2 A schematic flow chart of another data sorting method provided by an embodiment of the present invention.

[0091] S201. Generate a relationship tree by analyzing the SQL statement to be optimized.

[0092] S202: Find the target node on the relationship tree, obtain its sorting field set, and use the sorting field set as the first sorting field set and the second sorting field set.

[0093] S203: Detect child nodes of the target node.

[0094] S204: If the child node is a table scan node, obtain the index field set corresponding to the child node.

[0095] Among them, the data corresponding to the target sorting field set is ordered.

[0096] S205. If the child node is a data sequence maintenance node, continue to jump to S203.

[0097] S206: If the child node is a data sequence change node, stop detecting.

[0098] Among them, the data corresponding to the sorting field set corresponding to the data sequence change node is out of order.

[0099] S207: If the second sorting field set belongs to the leading column of the index field set, or the second sorting field set is the same as the index field set, the detection ends, and the second sorting field set is used as the target sorting field set.

[0100] Among them, the target sorting field set can be used as the ordered leading column.

[0101] S208: If the second sorting field set does not belong to the leading column of the index field set and the second sorting field set is different from the index field set, remove the last field of the second sorting field set to obtain a new second sorting field set.

[0102] S209: If the new second sorting field set is not empty, use the new second sorting field set as the second sorting field set, and return to S203.

[0103] S210: If the new second sorting field set is empty, a sorting operation is performed on the data corresponding to the sorting field set according to the original sorting method.

[0104] S211. Record the number of sort fields in the target sort field set.

[0105] S212: If the number of sorting fields in the target sorting field set is the same as the number of sorting fields in the first sorting field set, remove the sorting statement so as not to perform any sorting operation on the data corresponding to the sorting field.

[0106] S213: Group and sort the data corresponding to the remaining sorting field sets according to the target sorting field set.

[0107] The embodiment of the present invention detects the target sorting field set, groups the data corresponding to the remaining sorting field set according to the target sorting field set, and sorts each group of data independently. Compared with the method in the prior art that needs to sort the entire data, the embodiment of the present invention only needs to sort the data within the group, which reduces memory consumption, reduces the possibility of disk flushing, and improves execution performance.

[0108] Figure 3 A schematic diagram of the structure of a data sorting device provided by an embodiment of the present disclosure is shown in FIG. Figure 3 As shown, the apparatus includes: a sorting field set determination module 310, an index field set acquisition module 320, a target sorting field set determination module 330, a remaining sorting field set determination module 340, and a grouping sorting module 350;

[0109] The sorting field set determination module 310 is used to determine the first sorting field set and the second sorting field set of the target node based on the relationship tree corresponding to the SQL statement to be optimized; wherein the relationship tree is obtained by the logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is the node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set;

[0110] An index field set acquisition module 320, configured to acquire an index field set based on a subnode of the target node;

[0111] A target sorting field set determining module 330 is used to determine a target sorting field set based on the leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set;

[0112] The remaining sorting field set determination module 340 is used to determine the remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set;

[0113] The grouping and sorting module 350 is used to group and sort the data corresponding to the remaining sorting field set according to the target sorting field set.

[0114] The technical solution of the embodiment of the present disclosure is as follows: a sorting field set determination module determines a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to a SQL statement to be optimized; wherein the relationship tree is obtained by a logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is a node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; an index field set acquisition module acquires an index field set based on a child node of the target node; a target sorting field set determination module determines a target sorting field set based on a leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to a leading column of the index field set, or the target sorting field set is the same as the index field set; a remaining sorting field set determination module determines a remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; and a grouping and sorting module groups and sorts data corresponding to the remaining sorting field set according to the target sorting field set. In the disclosed embodiment, a target sorting field set is determined based on the leading column of the index field set and the second sorting field set, and the data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set, so that the sorting operator does not need to process the data corresponding to the target sorting field set. Compared with the prior art method that requires sorting the entire amount of data, the embodiment of the present invention can reduce memory consumption, improve data sorting efficiency, reduce the possibility of disk flushing, and improve execution performance.

[0115] Optionally, the sorting field set determination module is specifically used to: search for the logical execution plan corresponding to the SQL statement to be optimized; determine the relationship tree corresponding to the SQL statement to be optimized according to the logical execution plan; determine the target node based on the relationship tree corresponding to the SQL statement to be optimized; obtain the sorting field set corresponding to the target node as the first sorting field set and the second sorting field set.

[0116] Optionally, the index field set acquisition module is specifically used to: detect the child nodes of the target node; if the child node is a table scan node, obtain the index field set corresponding to the child node; if the child node is a data order maintenance node, continue to detect the child nodes of the target node; if the child node is a data order change node, stop detecting.

[0117] Optionally, the target sorting field set determination module is specifically used to: if the second sorting field set belongs to the leading column of the index field set, or the second sorting field set is the same as the index field set, then the detection ends, and the second sorting field set is used as the target sorting field set; if the second sorting field set does not belong to the leading column of the index field set and the second sorting field set is different from the index field set, then the tail field of the second sorting field set is removed to obtain a new second sorting field set; if the new second sorting field set is not empty, then the new second sorting field set is used as the second sorting field set, and the step of obtaining the index field set based on the child node of the target node is returned to and re-executed.

[0118] Optionally, the above device further includes an original sorting module, and the original sorting module is specifically used to: if the new second sorting field set is empty, perform a sorting operation on the data corresponding to the sorting field set according to the original sorting method.

[0119] Optionally, the above-mentioned device also includes a sorting removal module, which is specifically used to: record the number of sorting fields in the target sorting field set; if the number of sorting fields in the target sorting field set is the same as the number of sorting fields in the first sorting field set, remove the sorting statement to not perform any sorting operation on the data corresponding to the sorting field.

[0120] Optionally, the grouping and sorting module is specifically used to: compare the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer, initially 1; when N is 1, save the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; if the N+1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are in the same group, and save the N+1th row of data corresponding to the remaining sorting field set, and the N+1th row of data corresponding to the remaining sorting field set belongs to the previous group; if the N+1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the N+1th row of data corresponding to the remaining sorting field set is the same group, and save the N+1th row of data corresponding to the remaining sorting field set, and the N+1th row of data corresponding to the remaining sorting field set belongs to the previous group; is different from the Nth row of data, then the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N+1th row of data corresponding to the remaining sorting field set is a new group; sort the saved previous group of data, and output the sorted previous group of data; save the N+1th row of data corresponding to the remaining sorting field set; if N+1 is not the first set value, take N+1 as N, the new group as the previous group, return to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute; if N+1 is the first set value, sort the saved previous group of data, and output the sorted new group of data.

[0121] Optionally, the grouping and sorting module is further used to: compare the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer, initially the first set value; when N is the first set value, save the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; if the N-1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the N-1th row of data corresponding to the remaining sorting field set is the same group as the N-1th row of data, and save the N-1th row of data corresponding to the remaining sorting field set, and the N-1th row of data corresponding to the remaining sorting field set belongs to the previous group; if the N-1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, then the N-1th row of data corresponding to the remaining sorting field set is the same group as the N-1th row of data, and save the N-1th row of data corresponding to the remaining sorting field set, and the N-1th row of data corresponding to the remaining sorting field set belongs to the previous group; If the N-1th row of data is different from the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N-1th row of data are in different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N-1th row of data corresponding to the remaining sorting field set is a new group; sort the saved previous group of data, and output the sorted previous group of data; save the N-1th row of data corresponding to the remaining sorting field set; if N-1 is not the second set value, take N-1 as N, the new group as the previous group, return to the step of comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and re-execute; if N-1 is the second set value, sort the saved new group of data, and output the sorted new group of data.

[0122] The data sorting device provided in the embodiments of the present disclosure can execute the data sorting method provided in any embodiment of the present disclosure, and has the corresponding functional modules and beneficial effects of the execution method.

[0123] Figure 4 A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention 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 electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0124] like Figure 4As 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.

[0125] 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.

[0126] 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 dedicated artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as method data sorting.

[0127] In some embodiments, the method data sorting 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 method data sorting described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to perform the method data sorting in any other suitable manner (e.g., by means of firmware).

[0128] 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.

[0129] Computer programs for implementing the methods of the present invention 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 and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0130] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination 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.

[0131] 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).

[0132] 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.

[0133] 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.

[0134] An embodiment of the present invention also provides a computer program product, including a computer program, which, when executed by a processor, implements the data sorting method provided in any embodiment of the present application.

[0135] In the process of implementation, the computer program product can be written in one or more programming languages ​​or a combination thereof to perform the computer program code of the present invention, including object-oriented programming languages, such as Java, Smalltalk, C++, and 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 a separate 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 (for example, using an Internet service provider to connect through the Internet).

[0136] Note that the above are only preferred embodiments of the present invention and the technical principles used. Those skilled in the art will understand that the present invention is 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 scope of protection of the present invention. Therefore, although the present invention has been described in more detail through the above embodiments, the present invention is not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. A data sorting method, characterized in that: include: Determine a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to the SQL statement to be optimized; wherein the relationship tree is obtained from a logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is a node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; Acquire an index field set based on the child nodes of the target node; Determine a target sorting field set based on the leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set; Determine a remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; The data corresponding to the remaining sorting field set are grouped and sorted according to the target sorting field set.

2. The method according to claim 1, characterized in that Determine the sorting field of the target node based on the relationship tree corresponding to the SQL statement to be optimized, including: Find the logical execution plan corresponding to the SQL statement to be optimized; Determine the relationship tree corresponding to the SQL statement to be optimized according to the logical execution plan; Determine the target node based on the relationship tree corresponding to the SQL statement to be optimized; A sorting field set corresponding to the target node is obtained as a first sorting field set and a second sorting field set.

3. The method according to claim 1, characterized in that Acquiring an index field set based on the child nodes of the target node includes: Detecting child nodes of the target node; If the child node is a table scan node, obtaining the index field set corresponding to the child node; If the child node is a data order maintaining node, continue to detect the child nodes of the target node; If the child node is a data sequence change node, the detection is stopped.

4. The method according to claim 3, characterized in that Determining a target sorting field set based on the leading column of the index field set and the second sorting field set includes: If the second sorting field set belongs to the leading column of the index field set, or the second sorting field set is the same as the index field set, the detection ends, and the second sorting field set is used as the target sorting field set; If the second sorting field set does not belong to the leading column of the index field set and the second sorting field set is different from the index field set, then remove the last field of the second sorting field set to obtain a new second sorting field set; If the new second sorting field set is not empty, the new second sorting field set is used as the second sorting field set, and the process returns to the step of obtaining the index field set based on the child nodes of the target node and executes the step again.

5. The method according to claim 4, characterized in that Also includes: If the new second sorting field set is empty, a sorting operation is performed on the data corresponding to the sorting field set according to the original sorting method.

6. The method according to claim 1, characterized in that After determining the target sorting field set based on the leading column of the index field set and the sorting field set, the method further includes: Record the number of sorting fields in the target sorting field set; If the number of sorting fields in the target sorting field set is the same as the number of sorting fields in the first sorting field set, the sorting statement is removed so as not to perform any sorting operation on the data corresponding to the sorting fields.

7. The method according to claim 1, characterized in that Grouping and sorting the data corresponding to the remaining sorting field set according to the target sorting field set includes: Compare the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer, initially 1; when N is 1, save the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; If the N+1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are in the same group, and the N+1th row of data corresponding to the remaining sorting field set is saved, and the N+1th row of data corresponding to the remaining sorting field set belongs to the previous group; If the N+1th row of data corresponding to the sorting field in the target sorting field set is different from the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N+1th row of data are in different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N+1th row of data corresponding to the remaining sorting field set is a new group; Sorting the stored previous set of data, and outputting the sorted previous set of data; Save the N+1th row of data corresponding to the remaining sorting field set; If N+1 is not the first set value, N+1 is taken as N, the new group is taken as the previous group, and the process returns to the step of comparing the N+1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and the process is executed again; If N+1 is the first set value, the stored previous set of data is sorted, and the sorted new set of data is output.

8. The method according to claim 7, characterized in that Grouping and sorting the data corresponding to the remaining sorting field set according to the target sorting field set includes: Compare the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data; wherein N is a positive integer and is initially the first set value; when N is the first set value, save the Nth row of data corresponding to the remaining sorting field set, and the Nth row of data corresponding to the remaining sorting field set belongs to the previous group; If the N-1th row of data corresponding to the sorting field in the target sorting field set is the same as the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N-1th row of data are in the same group, and the N-1th row of data corresponding to the remaining sorting field set is saved, and the N-1th row of data corresponding to the remaining sorting field set belongs to the previous group; If the N-1th row of data corresponding to the sorting field in the target sorting field set is different from the Nth row of data, the Nth row of data corresponding to the remaining sorting field set and the N-1th row of data are in different groups, the Nth row of data corresponding to the remaining sorting field set belongs to the previous group, and the N-1th row of data corresponding to the remaining sorting field set is a new group; Sorting the stored previous set of data, and outputting the sorted previous set of data; Save the N-1th row of data corresponding to the remaining sorting field set; If N-1 is not the second set value, N-1 is taken as N, the new group is taken as the previous group, and the process returns to the step of comparing the N-1th row of data corresponding to the sorting field in the target sorting field set with the Nth row of data, and the process is executed again; If N-1 is the second set value, the stored new group data is sorted, and the sorted new group data is output.

9. A data sorting device, characterized in that: include: A sorting field set determination module is used to determine a first sorting field set and a second sorting field set of a target node based on a relationship tree corresponding to the SQL statement to be optimized; wherein the relationship tree is obtained from a logical execution plan of the SQL statement to be optimized; the SQL statement to be optimized includes a sorting statement; the target node is a node corresponding to the sorting statement in the relationship tree; wherein the first sorting field set is the same as the second sorting field set; An index field set acquisition module, used to acquire an index field set based on a sub-node of the target node; a target sorting field set determining module, configured to determine a target sorting field set based on the leading column of the index field set and the second sorting field set; wherein the target sorting field set belongs to the leading column of the index field set, or the target sorting field set is the same as the index field set; A remaining sorting field set determination module, configured to determine a remaining sorting field set according to the target sorting field set and the first sorting field set; wherein the first sorting field set includes the target sorting field set and the remaining sorting field set; The grouping and sorting module is used to group and sort the data corresponding to the remaining sorting field set according to the target sorting field set.

10. An electronic device, characterized in that: The electronic device comprises: one or more processors; a storage device for storing one or more programs, When the one or more programs are executed by the one or more processors, the one or more processors implement the data sorting method as described in any one of claims 1 to 8.

11. A storage medium comprising computer executable instructions, wherein the computer executable instructions are used to perform the data sorting method according to any one of claims 1 to 8 when executed by a computer processor.

12. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the computer program implements the data sorting method according to any one of claims 1 to 8.