Structured query statement optimization method, device, electronic device and medium

Abstract

The embodiment of the present invention discloses a structured query statement optimization method, device, electronic device and medium. The method includes: for a target structured query statement containing multiple grouping columns in a grouping clause, creating an initial linked list; determining the grouping columns belonging to the feature columns in the grouping clause, and adding the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list; determining the first identifiable grouping column among all the grouping columns of the grouping clause according to all the grouping columns of the grouping clause and the corresponding grouping column table, as well as the target linked list; determining the second identifiable grouping column among the target grouping columns contained in the target linked list according to the weight tag value corresponding to each node in the target linked list; optimizing the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column. Utilizing this method, the execution efficiency of SQL statements can be improved.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of database technology, and in particular to a structured query statement optimization method, device, electronic device and medium. Background Art

[0002] Structured Query Language (SQL), also known as SQL statement, is a database query and programming language, and can also refer to a relational database operation language; it can be used to access data as well as query, update and manage relational database systems; it is also the extension of database script files.

[0003] At present, in the current common database technology, in order to implement an SQL statement containing a GROUP BY clause, where the GROUP BY clause, also known as a grouping clause, refers to a clause used in an SQL statement to group data, it is necessary to fetch the data from the table of the database and calculate the grouping according to each grouping column specified by the GROUP BY clause. However, in some cases, it is not necessary to use all the grouping columns in the GROUP BY clause to implement an SQL statement containing a GROUP BY clause. At this time, if the grouping is still calculated according to each grouping column specified by the GROUP BY clause, the amount of calculation will increase, thereby reducing the execution efficiency of the SQL statement.

[0004] Therefore, how to improve the execution efficiency of SQL statements is a technical problem that needs to be solved urgently. Summary of the invention

[0005] The embodiment of the present invention provides a structured query statement optimization method, device, electronic device and medium to improve the execution efficiency of SQL statements.

[0006] In a first aspect, an embodiment of the present invention provides a structured query statement optimization method, comprising:

[0007] For a target structured query statement containing multiple grouping columns in the grouping clause, create an initial linked list;

[0008] Determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding table to which the target grouping column belongs, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column;

[0009] According to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list, determine a first identifiable grouping column among all the grouping columns of the grouping clause;

[0010] Determine, according to the weight tag value corresponding to each node in the target linked list, a second identifiable grouping column in the target grouping column included in the target linked list;

[0011] The target structured query statement is optimized according to the first identifiable grouping column and the second identifiable grouping column.

[0012] In a second aspect, an embodiment of the present invention further provides a structured query statement optimization device, comprising:

[0013] A creation module is used to create an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause;

[0014] A first determination module is used to determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding table to which the target grouping column belongs, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column;

[0015] A second determination module, configured to determine a first identifiable grouping column among all the grouping columns of the grouping clause according to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list;

[0016] A third determination module, configured to determine a second identifiable grouping column in the target grouping column included in the target linked list according to a weight tag value corresponding to each node in the target linked list;

[0017] An optimization module is used to optimize the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column.

[0018] In a third aspect, an embodiment of the present invention further provides an electronic device, including:

[0019] one or more processors;

[0020] A storage device for storing one or more programs;

[0021] The one or more programs are executed by the one or more processors, so that the one or more processors implement the structured query statement optimization method provided by the embodiment of the present invention.

[0022] In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium on which a computer program is stored, and when the program is executed by a processor, the structured query statement optimization method provided by an embodiment of the present invention is implemented.

[0023] The embodiment of the present invention provides a structured query statement optimization method, device, electronic device and medium. First, for a target structured query statement containing multiple grouping columns in a grouping clause, an initial linked list is created; secondly, the grouping columns belonging to the feature columns in the grouping clause are determined, and the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns are added to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding target grouping column belonging table, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; then, according to all the grouping columns of the grouping clause and the corresponding grouping column belonging table, and the target linked list, the first identifiable grouping column among all the grouping columns of the grouping clause is determined; then, according to the weight tag value corresponding to each node in the target linked list, the second identifiable grouping column among the target grouping columns contained in the target linked list is determined; finally, according to the first identifiable grouping column and the second identifiable grouping column, the target structured query statement is optimized. The above technical solution can effectively improve the execution efficiency of SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0024] Figure 1A A schematic diagram of a query result of SQL statement A provided in an embodiment of the present invention;

[0025] Figure 1B A schematic diagram of a query result of SQL statement B provided in an embodiment of the present invention;

[0026] Figure 2 A schematic diagram of a flow chart of a structured query statement optimization method provided in Embodiment 1 of the present invention;

[0027] Figure 3 A schematic diagram of the structure of a structured query statement optimization device provided in Embodiment 2 of the present invention;

[0028] Figure 4 A schematic diagram of the structure of an electronic device provided in Embodiment 3 of the present invention. DETAILED DESCRIPTION

[0029] The present invention will be further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the present invention, rather than to limit the present invention. It should also be noted that, for ease of description, only parts related to the present invention, rather than all structures, are shown in the accompanying drawings.

[0030] It should be mentioned before discussing exemplary embodiments in more detail that some exemplary embodiments are described as processes or methods depicted as flow charts. Although the flow charts describe various operations (or steps) as sequential processes, many operations therein can be implemented in parallel, concurrently or simultaneously. In addition, the order of various operations can be rearranged. The process can be terminated when its operation is completed, but can also have additional steps not included in the accompanying drawings. The process can correspond to methods, functions, procedures, subroutines, subprograms, etc. In addition, the embodiments in the present invention and the features in the embodiments can be combined with each other without conflict.

[0031] The term “including” and its variations used in the present invention are open inclusions, that is, “including but not limited to.” The term “based on” means “based at least in part on.” The term “one embodiment” means “at least one embodiment.”

[0032] It should be noted that the concepts such as "first" and "second" mentioned in the present invention are only used to distinguish the corresponding contents, and are not used to limit the order or interdependence.

[0033] It should be noted that the modifications of "one" and "plurality" mentioned in the present invention are illustrative rather than restrictive, and those skilled in the art should understand that, unless otherwise clearly indicated in the context, it should be understood as "one or more".

[0034] Structured query statements, namely SQL statements, are an important and commonly used relational database operation language. The GROUP BY clause is a clause in the SQL statement standard used to group data, which can be called a grouping clause.

[0035] The following is an exemplary description of the GROUP BY clause.

[0036] Assume that the test table (i.e., TEST table) contains two columns, named C1 and C2 respectively. Use SQL statement A to query the data in the TEST table. SQL statement A can be expressed as: SELECT C1, C2 FROM TEST. Figure 1A A schematic diagram of a query result of SQL statement A provided in an embodiment of the present invention.

[0037] Use SQL statement B containing a GROUP BY clause (i.e., a grouping clause) to query the data in the TEST table. SQL statement B can be expressed as: SELECT C1, C2 FROM TEST GROUP BY C1, C2. The grouping columns contained in the grouping clause in SQL statement B are C1 and C2. When performing group calculations in SQL statement B, it is required that C1 and C2 in each group must be the same. Figure 1BA schematic diagram of a query result of SQL statement B provided in an embodiment of the present invention. Figure 1B As shown, 1 and A are a group, 1 and B are a group, 2 and C are a group, 3 and C are a group, a total of 4 groups; on this basis, Figure 1B As shown, the query results of SQL statement B including the GROUP BY clause display the above four groups of query results.

[0038] The embodiment of the present invention proposes a method for improving the execution efficiency of SQL statements by calculating only specific grouping columns. For an SQL statement containing multiple grouping columns, if the multiple grouping columns belong to different tables and a certain grouping column A can uniquely identify other grouping columns, the grouping column A can be directly used to replace other grouping columns that can be identified by the grouping column A for grouping. On this basis, the execution efficiency of the SQL statement can be improved by reducing the calculation of the number of grouping columns.

[0039] In one embodiment, assuming that the table used to record user information is the CUSTOMER table, the SQL statement for creating the CUSTOMER table can be expressed as:

[0040] CREATE TABLE CUSTOMER(ID INT PRIMARY KEY,NAME VARCHAR(20),

[0041] NATION_ID INT);

[0042] Among them, Identification (ID) can represent the user's identity ID, NAME can represent the user's name, and NATION_ID can represent the user's country ID. ID is the primary key (i.e., PRIMARY KEY) of the CUSTOMER table, and the primary key value (i.e., the value of the column where ID is located) cannot be repeated and cannot be empty; VARCHAR(20) can indicate that the characters used to represent NAME cannot exceed 20 characters.

[0043] The data in the CUSTOMER table (ID, 'NAME', NATION_ID) can include:

[0044] (1, 'Mr. Wang', 1), (2, 'Ms. Li', 1), and (3, 'Mr. Zhao', 2).

[0045] Assuming that the table used to record country information is NATION, the SQL statement to create the NATION table can be expressed as:

[0046] CREATE TABLE NATION(NATION_ID INT PRIMARY KEY,NATION_NAME VARCHAR(20));

[0047] NATION_ID can represent the country ID, and NATION_NAME can represent the country name. NATION_ID is the primary key of the NATION table, and the primary key value (that is, the value of the column where NATION_ID is located) cannot be repeated and cannot be empty.

[0048] The data of NATION table (NATION_ID, 'NATION_NAME') may include (1, 'China'), (2, 'United States').

[0049] Based on the above embodiment, the SQL statement C for searching the user ID and the name of the country to which the user ID satisfies the query condition (ie, CUSTOMER.NATION_ID=NATION.NATION_ID) belongs can be expressed as:

[0050] SELECT ID,NATION_NAME FROM CUSTOMER,NATION WHERE CUSTOMER.NATION_ID

[0051] =NATION.NATION_ID GROUP BY ID,NATION_NAME;

[0052] The query results of SQL statement C can be expressed as (1, 'China'), (2, 'China') and (3, 'United States').

[0053] The grouping columns of SQL statement C are ID and NATION_NAME, which belong to the CUSTOMER table and the NATION table respectively. The association condition between the CUSTOMER table and the NATION table can be expressed as "CUSTOMER.NATION_ID=NATION.NATION_ID". Since a NATION_ID can be uniquely determined based on the ID in the CUSTOMER table, and a NATION_NAME can be uniquely determined based on the NATION_ID in the NATION table, based on this, according to the association condition between the CUSTOMER table and the NATION table, a NATION_NAME can be uniquely determined based on the ID. For example, the user with ID 1 belongs to the country "China".

[0054] Therefore, when SQL statement C actually performs group calculation, it can be grouped only by ID without grouping NATION_NAME. By reducing the number of grouping columns, the data to be calculated and compared during group calculation can be reduced. When the amount of data involved is large or the number of grouping columns that can be reduced is large, reducing the data to be calculated and compared can effectively improve the execution efficiency of SQL statements.

[0055] It should be noted that, in this embodiment, a NATION_NAME can be uniquely determined based on an ID, but an ID cannot be uniquely determined based on a NATION_NAME. For example, it is impossible to determine whether the ID corresponding to "China" is 1 or 2. Therefore, the grouping column that can be ignored in actual grouping calculation is NATION_NAME. In other words, if there are two grouping columns, and one grouping column A can uniquely determine the other grouping column B, then grouping column B can be ignored in actual grouping calculation.

[0056] Embodiment 1

[0057] Figure 2 A flow chart of a structured query statement optimization method provided in Embodiment 1 of the present invention is provided. The method can be applied to optimizing a structured query statement containing multiple grouping columns in a grouping clause to improve the statement execution efficiency. The method can be executed by a structured query statement optimization method device, wherein the device can be implemented by software and / or hardware and is generally integrated on an electronic device. In this embodiment, the electronic device includes but is not limited to: desktop computers, laptop computers, servers and other devices.

[0058] like Figure 2 As shown, a structured query statement optimization method provided by the first embodiment of the present invention includes the following steps:

[0059] S110 . For a target structured query statement containing multiple grouping columns in a grouping clause, an initial linked list is created.

[0060] In this embodiment, the structured query statement may be referred to as an SQL statement. A linked list may refer to a non-continuous and non-sequential storage structure on a physical storage unit, and a linked list may be composed of a series of nodes (each element in the linked list may be referred to as a node).

[0061] For a target SQL statement containing a grouping clause, if the grouping clause contains multiple grouping columns (such as at least two grouping columns), an initial linked list can be created. The initial linked list can be understood as an initial linked list structure, in which no nodes have been added. When nodes need to be added in the subsequent process, corresponding additions are made to store corresponding data.

[0062] Optionally, for a target SQL statement containing multiple grouping columns in a grouping clause, creating an initial linked list includes: performing lexical analysis and syntax analysis on the acquired SQL statement to obtain a target SQL statement; determining whether the target SQL statement contains a grouping clause; if not, exiting optimization; if containing a grouping clause, determining whether the grouping clause has an outer join; if there is an outer join, exiting optimization; if there is no outer join, determining whether the number of grouping columns in the grouping clause is greater than 1; if the number of grouping columns is not greater than 1, exiting optimization; if the number of grouping columns is greater than 1, creating an initial linked list.

[0063] The obtained SQL statement can be understood as the SQL statement to be executed input by the user. An outer join can refer to a connection relationship between tables to which the grouping columns in the grouping clause belong.

[0064] Specifically, firstly, the acquired SQL statement can be subjected to lexical analysis and grammatical analysis by corresponding lexical analysis tools and grammatical analysis tools, and a memory structure statement of the SQL statement (a memory structure statement corresponding to the database memory structure), i.e., a target SQL statement, can be generated. After obtaining the target SQL statement, it is determined whether the target SQL statement contains a grouping clause. If the target SQL statement does not contain a grouping clause, then the optimization is exited, wherein exiting the optimization can be understood as exiting the optimization of the target SQL statement and executing the acquired SQL statement normally; if the target SQL statement contains a grouping clause, then it can be determined whether there is an outer join in the grouping clause. If there is an outer join in the grouping clause, then the optimization is exited; if there is no outer join in the grouping clause, then it can be determined whether the number of grouping columns in the grouping clause is greater than 1; if the number of grouping columns in the grouping clause is not greater than 1, then the optimization can be exited; if the number of grouping columns in the grouping clause is greater than 1 (such as two or more), then an initial linked list can be created.

[0065] S120, determining the grouping columns belonging to the feature columns in the grouping clause, and adding the target grouping columns that meet the preset conditions among the grouping columns belonging to the feature columns and the table to which the target grouping columns belong to the initial linked list to construct a target linked list.

[0066] In this embodiment, the data in the database can be represented in the form of a table, and a table can include multiple columns of data. If a non-empty column in a table has a unique constraint (i.e., a UNIQUE constraint), or the non-empty column is a primary key, then the non-empty column can be considered to be a feature column of the table to which it belongs. A table can include multiple feature columns.

[0067] In the process of constructing the target linked list using the initial linked list, a table can only have one feature column stored in the constructed target linked list. On this basis, the preset condition can refer to that in the process of constructing the target linked list using the initial linked list, for the grouping column belonging to the feature column, the table to which it belongs does not exist in the current initial linked list. For example, if a grouping column A belonging to the feature column and its table A already exist in the current initial linked list, it can be indicated that before this, a grouping column B belonging to the feature column in table A has been stored in the initial linked list. At this time, according to the above principle that a table can only have one feature column stored in the constructed target linked list, the grouping column A cannot be added to the current initial linked list, that is, the grouping column A does not meet the preset condition.

[0068] The target grouping column may refer to a grouping column that satisfies a preset condition among multiple grouping columns belonging to feature columns. The table to which the target grouping column belongs may refer to the table to which the target grouping column belongs. For example, in the above embodiment, if ID is a target grouping column, the table to which ID belongs may be the CUSTOMER table.

[0069] Specifically, first determine the grouping columns (if there are multiple) that belong to the feature columns in the grouping clause, and then for the multiple grouping columns that belong to the feature columns, you can determine whether they meet the preset conditions one by one in order. If there is a grouping column that meets the preset conditions, the grouping column that meets the preset conditions can be used as the target grouping column, and a new node is added to the initial linked list, and the target grouping column and the table to which the target grouping column belongs are added to the newly added node. Among them, a node can include a target grouping column and a corresponding target grouping column table. After all the grouping columns that belong to the feature columns are judged, the current initial linked list has already included the corresponding nodes and the corresponding data contained in the nodes in this process, that is, the current initial linked list can be used as the target linked list.

[0070] Among them, each node in the target linked list can be used to store a target grouping column and the corresponding target grouping column belonging table, and each node in the target linked list can also include a weight tag value corresponding to the corresponding target grouping column. The weight tag value can refer to a value used to characterize the identification status of its corresponding target grouping column. Exemplarily, the identification status of the target grouping column can include three types: the first is that the target grouping column cannot uniquely identify other target grouping columns, nor can it be uniquely identified by other target grouping columns; the second is that the target grouping column can uniquely identify other target grouping columns; the third is that the target grouping column can be uniquely identified by other target grouping columns. For example, target grouping column A can be uniquely identified by target grouping column B, which can be understood as target grouping column A can be replaced by target grouping column B, and target grouping column A can be ignored.

[0071] It should be noted that one grouping column can uniquely identify one or more other grouping columns. Each target grouping column in the target linked list can uniquely identify other grouping columns in the table to which it belongs.

[0072] In one embodiment, it is assumed that there are two grouping columns A1 and B1, the table to which grouping column A1 corresponds can be recorded as table M, and grouping column A1 is a characteristic column of table M, and the table to which grouping column B1 corresponds can be recorded as table N. When the relationship between table M and table N satisfies the condition "M.A2=N.B2 and grouping column B2 is a characteristic column of table N" (where A2 can be any column in table M, and B2 can be any characteristic column in table N), it can be considered that grouping column A1 can uniquely identify grouping column B1.

[0073] Optionally, determine the grouping columns that belong to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns that belong to the feature columns to the initial linked list to construct a target linked list, including: traversing each grouping column in the grouping clause to determine whether the current grouping column is a feature column of the table to which it belongs; if the current grouping column is a feature column of the table to which it belongs, traversing each node in the current initial linked list to determine whether the current initial linked list contains the table to which the current grouping column belongs; if the current initial linked list does not contain the table to which the current grouping column belongs, taking the current grouping column as the target grouping column, and adding the target grouping column and the table to which the target grouping column belongs to the newly added node of the current initial linked list; taking the current initial linked list at the end of the traversal as the target linked list.

[0074] Among them, first, traverse each grouping column in the grouping clause to determine whether the current grouping column is a feature column of the table to which it belongs; if the current grouping column is a feature column of the table to which it belongs, traverse each node in the current initial linked list (at this time, since in the previous traversal process, the target grouping column and the table to which the target grouping column belongs that may have met the preset conditions may have been added to the newly added node of the initial linked list, so the current initial linked list may already contain the node), and determine whether the current initial linked list contains the table to which the current grouping column belongs. Then, if the current initial linked list does not contain the table to which the current grouping column belongs, the current grouping column can be used as the target grouping column, and the target grouping column and the table to which the target grouping column belongs are added to a newly added node of the current initial linked list. Finally, when the traversal is completed, it can be indicated that the process of constructing the target linked list using the initial linked list is completed, and the current initial linked list can be used as the target linked list.

[0075] S130: Determine a first identifiable grouping column among all the grouping columns of the grouping clause according to all the grouping columns of the grouping clause, the table to which the corresponding grouping columns belong, and the target linked list.

[0076] In this embodiment, the first identifiable grouping column may refer to a grouping column that can be uniquely identified by a target grouping column in the target linked list among all the grouping columns in the grouping clause.

[0077] Exemplarily, there may be some grouping columns and their tables in the grouping clause that are not added to the target linked list. Therefore, based on the constructed target linked list, the table to which the grouping columns that are not added to the target linked list belong and the grouping columns thereon can be found from the grouping clause, and the grouping columns in the table that are not added to the target linked list are called the first grouping columns to be identified. On this basis, for the multiple first grouping columns to be identified that are found, it is possible to sequentially search from the target linked list whether there is a target grouping column that can uniquely identify the first grouping column to be identified. If so, the corresponding first grouping column to be identified can be used as the first identifiable grouping column.

[0078] Optionally, determining the first identifiable grouping column among all the grouping columns of the grouping clause based on all the grouping columns of the grouping clause and the tables to which the corresponding grouping columns belong, as well as the target linked list, includes: traversing each grouping column in the grouping clause to determine whether the target linked list contains the table to which the current grouping column belongs; if the target linked list does not contain the table to which the current grouping column belongs, traversing each target grouping column contained in the target linked list to determine whether the target linked list contains a target grouping column that can uniquely identify the current grouping column; if so, determining that the current grouping column is the first identifiable grouping column.

[0079] Among them, according to all the grouping columns of the grouping clause and the corresponding grouping column belonging table, and the target linked list, the specific process of determining the first identifiable grouping column among all the grouping columns of the grouping clause is as follows: first, traverse each grouping column in the grouping clause to determine whether the target linked list contains the table to which the current grouping column belongs. Then, if the target linked list does not contain the table to which the current grouping column belongs, it can be indicated that the current grouping column is the first grouping column to be identified. At this time, each target grouping column contained in the target linked list can be traversed to determine whether the target linked list contains a target grouping column that can uniquely identify the current grouping column. Finally, if the target linked list contains a target grouping column that can uniquely identify the current grouping column, it can be determined that the current grouping column is the first identifiable grouping column.

[0080] S140: Determine a second identifiable grouping column in the target grouping columns included in the target linked list according to the weight tag value corresponding to each node in the target linked list.

[0081] In this embodiment, according to the three identification situations represented by the weight tag values ​​in the above embodiment, three weight tag values ​​can be set accordingly, such as 0, 1 and 2. Among them, the weight tag value of 0 corresponds to the first identification situation; the weight tag value of 1 corresponds to the second identification situation; and the weight tag value of 2 corresponds to the third identification situation.

[0082] Optionally, the weight tag value includes a first set value, a second set value and a third set value; when the weight tag value is the first set value, the corresponding target grouping column does not uniquely identify other target grouping columns, nor is it uniquely identified by other target grouping columns; when the weight tag value is the second set value, the corresponding target grouping column can uniquely identify one or more other target grouping columns; when the weight tag value is the third set value, the corresponding target grouping column is uniquely identified by one or more other target grouping columns.

[0083] Among them, the first set value, the second set value and the third set value can be set to 0, 1 and 2 respectively. Specifically, when the weight mark value is the first set value (i.e. 0), the corresponding target grouping column cannot uniquely identify other target grouping columns (other target grouping columns can refer to other target grouping columns other than the target grouping column in the target linked list), nor can it be uniquely identified by other target grouping columns. When the weight mark value is the second set value (i.e. 1), the corresponding target grouping column can uniquely identify one or more other target grouping columns. When the weight mark value is the third set value (i.e. 2), the corresponding target grouping column can be uniquely identified by one or more other target grouping columns.

[0084] It should be noted that when a target grouping column uniquely identifies other target grouping columns, it can also be uniquely identified by other target grouping columns. If a target grouping column and another target grouping column can uniquely identify each other, then you can select any one of the target grouping columns, set its corresponding weight tag value to 1, and set the weight tag value of the other target grouping column to 2.

[0085] Optionally, each node of the target linked list also includes a replacement table corresponding to the corresponding target group column; before determining the second identifiable group column in the target group column contained in the target linked list based on the weight tag value corresponding to each node in the target linked list, it also includes: initializing the weight tag value corresponding to each node in the target linked list to a first set value; initializing the replacement table corresponding to each node in the target linked list to be empty.

[0086] Among them, the replacement table may refer to a table to which a target grouping column belongs that can uniquely identify the current target grouping column. The replacement table corresponding to the target grouping column can be determined according to the corresponding weight tag value. Exemplarily, when the weight tag value corresponding to the target grouping column A is 0, the corresponding replacement table is empty; when the target grouping column A can uniquely identify a target grouping column B (that is, the weight tag value corresponding to the target grouping column A is 1), the corresponding replacement table is the table to which it belongs, that is, the table to which the target grouping column A belongs; when the target grouping column A is uniquely identified by a target grouping column B (that is, the weight tag value corresponding to the target grouping column A is 2), the corresponding replacement table is the table to which the target grouping column B belongs.

[0087] Exemplarily, before determining the second identifiable grouping column in the target grouping column contained in the target linked list based on the weight tag value corresponding to each node in the target linked list, the weight tag value corresponding to each node in the target linked list can be initialized to a first set value, that is, 0, and the replacement table corresponding to each node in the target linked list can be initialized to empty (the replacement table being empty can be understood as the current replacement table is empty data, waiting for corresponding additions in subsequent processes).

[0088] According to the weight tag value corresponding to each node in the target linked list, the second identifiable grouping column in the target grouping column contained in the target linked list is determined. Specifically, it can be: according to the weight tag value corresponding to each node in the target linked list, for the target grouping columns corresponding to any two nodes in the target linked list, determine whether there is a unique identification situation, such as whether there is a target grouping column that can uniquely identify another target grouping column; if so, the corresponding uniquely identified target grouping column can be determined as the second identifiable grouping column, and according to the weight tag value corresponding to the two nodes, the weight tag value and replacement table corresponding to the two nodes are reset. On this basis, all target grouping columns in the target linked list that are determined to be uniquely identified target grouping columns are used as second identifiable grouping columns.

[0089] S150: Optimize the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column.

[0090] In this embodiment, when executing the target SQL statement to perform actual grouping calculation, the grouping columns determined as the first identifiable grouping columns and the second identifiable grouping columns in the grouping clause can be ignored, and the target SQL statement is executed only based on the remaining grouping columns except the first identifiable grouping columns and the second identifiable grouping columns, so as to reduce the number of grouping columns, optimize the target SQL statement, and thus improve the execution efficiency of the target SQL statement.

[0091] A structured query statement optimization method provided by the first embodiment of the present invention first creates an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause; secondly, determines the grouping columns belonging to the feature columns in the grouping clause, and adds the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding target grouping column table, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; then, according to all the grouping columns of the grouping clause and the corresponding grouping column table, and the target linked list, determines the first identifiable grouping column among all the grouping columns of the grouping clause; then, according to the weight tag value corresponding to each node in the target linked list, determines the second identifiable grouping column in the target grouping column contained in the target linked list; finally, according to the first identifiable grouping column and the second identifiable grouping column, optimizes the target structured query statement. Using the above method, the execution efficiency of SQL statements can be effectively improved.

[0092] Optionally, according to the weight tag value corresponding to each node in the target linked list, the second identifiable grouping column in the target grouping column included in the target linked list is determined, including: selecting any two different nodes in the target linked list, and judging whether the target grouping column in one of the nodes can uniquely identify the target grouping column in the other node; if so, recording one of the nodes as the first node and the other node as the second node, and determining the second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag value corresponding to the first node and the second node; if not, returning to execute the operation of selecting any two different nodes in the target linked list until there are no two nodes in the target linked list that are not selected at the same time, wherein at least one of the nodes selected each time is different.

[0093] In this embodiment, the specific process of determining the second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag values ​​corresponding to the first node and the second node is as follows:

[0094] First, any two different nodes in the target linked list are selected to determine whether the target grouping column in one of the nodes can uniquely identify the target grouping column in the other node; if the target grouping column in one of the nodes can uniquely identify the target grouping column in the other node, then one of the nodes can be recorded as the first node, and the other node can be recorded as the second node, and the second identifiable grouping column in the target grouping column contained in the target linked list can be determined according to the weight tag values ​​corresponding to the first node and the second node respectively; if both nodes cannot uniquely identify each other, then return to execute the operation of selecting any two different nodes in the target linked list until there are no two nodes in the target linked list that are not selected at the same time, wherein at least one of the nodes selected each time is different, so as to ensure that the above-mentioned judgment operation is performed on any two nodes in the target linked list.

[0095] Optionally, according to the weight tag values ​​corresponding to the first node and the second node respectively, the second identifiable grouping column in the target grouping column included in the target linked list is determined, including: if the weight tag value corresponding to the first node is the first set value and the weight tag value corresponding to the second node is the first set value or the second set value, then the weight tag values ​​corresponding to the first node and the second node are set to the second set value and the third set value respectively, and the replacement table corresponding to the first node and the second node is set to the table belonging to the target grouping column of the first node; if the weight tag value corresponding to the first node is the second set value or the third set value, and the weight tag value corresponding to the second node is the first set value, then the weight tag value corresponding to the second node is set to the third set value, and the replacement table corresponding to the second node is set to the replacement table corresponding to the first node; if the weight tag value corresponding to the first node is the second set value, and the weight tag value corresponding to the second node is the second set value, then the second node is set to the second set value. The weight tag value corresponding to the point is the third set value, and the replacement table corresponding to the second node is set to the replacement table corresponding to the first node; if the weight tag value corresponding to the first node is the third set value, and the weight tag value corresponding to the second node is the second set value, then determine whether the replacement table corresponding to the first node is the table to which the target grouping column in the second node belongs; if so, keep the current settings of the weight tag value and the replacement table in the first node and the second node unchanged; if not, set the weight tag value corresponding to the second node to the third set value, and set the replacement table corresponding to the second node to the replacement table corresponding to the first node; if the weight tag value corresponding to the second node is the third set value, keep the current settings of the weight tag value and the replacement table in the second node unchanged; after setting the weight tag value and the replacement table corresponding to each node in the target linked list, determine the target grouping column in the node in the target linked list whose weight tag value is the third set value as the second identifiable grouping column.

[0096] Based on the above embodiment, when the target grouping column in the first node can uniquely identify the target grouping column in the second node, the second identifiable grouping column in the target grouping column included in the target linked list can be determined according to the weight tag values ​​corresponding to the first node and the second node respectively.

[0097] Specifically, if the weight tag value corresponding to the first node is 0 and the weight tag value corresponding to the second node is 0 or 1, the weight tag values ​​corresponding to the first node and the second node can be set to 1 and 2 respectively, and the replacement table corresponding to the first node and the second node can be set to the table to which the target group column of the first node belongs.

[0098] If the weight tag value corresponding to the first node is 1 or 2, and the weight tag value corresponding to the second node is 0, then the weight tag value and the replacement table corresponding to the first node remain unchanged, and only the weight tag value corresponding to the second node can be set to 2, and the replacement table corresponding to the second node can be set to the replacement table corresponding to the first node;

[0099] If the weight tag value corresponding to the first node is 1, and the weight tag value corresponding to the second node is 1, it can be determined that the target group column in the second node is the second identifiable group column. At this time, the weight tag value and the replacement table corresponding to the first node remain unchanged, and only the weight tag value corresponding to the second node is set to 2, and the replacement table corresponding to the second node is set to the replacement table corresponding to the first node;

[0100] If the weight tag value corresponding to the first node is 2, and the weight tag value corresponding to the second node is 1, it can be determined whether the replacement table corresponding to the first node is the table to which the target group column in the second node belongs; if so, the current settings of the weight tag value and the replacement table in the first node and the second node can be kept unchanged; if not, the weight tag value corresponding to the second node can be set to 2, and the replacement table corresponding to the second node can be set to the replacement table corresponding to the first node;

[0101] If the weight tag value corresponding to the second node is 2, no matter what the weight tag value corresponding to the first node is, the current settings of the weight tag value and the replacement table in the first node and the second node can be kept unchanged;

[0102] After the weight tag value and the replacement table corresponding to each node in the target linked list are set using the above setting method, the target grouping column in the node with a weight tag value of 2 in the target linked list can be determined as the second identifiable grouping column.

[0103] Optionally, when the weight tag value corresponding to the second node is a second set value, determining the second identifiable grouping column in the target grouping column contained in the target linked list according to the weight tag values ​​corresponding to the first node and the second node, also includes: before setting the weight tag value and replacement table corresponding to the first node and the second node, setting the replacement table of the specified node in the target linked list to the replacement table corresponding to the first node, wherein the specified node includes a node whose replacement table is the replacement table of the second node.

[0104] Among them, when the weight tag value corresponding to the second node is 2, before setting the weight tag value and replacement table corresponding to the first node and the second node, the replacement table corresponding to the node in the target linked list whose replacement table is the replacement table of the second node can also be set to the replacement table corresponding to the first node.

[0105] In one embodiment, a specific implementation process of a structured query statement optimization method is as follows:

[0106] Step 1: After performing lexical and grammatical analysis on the SQL statement input by the user, an SQL memory structure statement (ie, target SQL statement) is generated.

[0107] Step 2: Determine whether the target SQL statement contains a GROUP BY clause (i.e., a grouping clause). If not, proceed to step 13; otherwise, determine whether the grouping clause of the target SQL statement has an outer join. If there is an outer join, proceed to step 13; if there is no outer join, determine the number of grouping columns in the grouping clause. If the number of grouping columns is less than or equal to 1, proceed to step 13; if the number of grouping columns is greater than 1, proceed to step 3;

[0108] Step 3: Create an initial linked list LST and proceed to step 4;

[0109] Step 4: traverse each grouping column in the grouping clause to determine whether the current grouping column COL is a characteristic column of the table T to which it belongs. If so, execute step 5; if not, take the next grouping column and continue to execute step 4. After the traversal of step 4 is completed, the current initial linked list at the end of the traversal is used as the target linked list LST, and then execute step 6;

[0110] Step 5: traverse the current initial linked list LST to determine whether the table T to which the current grouping column COL belongs already exists in the current initial linked list LST. If not, add a new node to the current initial linked list LST to record the current grouping column COL and its table T, and continue to execute step 4.

[0111] Step 6: traverse each grouping column in the grouping clause, and determine whether the table T to which the current grouping column COL belongs exists in the target linked list LST. If so, take the next grouping column and continue to return to the operation of traversing each grouping column in the grouping clause in step 6; otherwise, execute step 7. After the traversal of step 6 is completed, execute step 8.

[0112] It should be noted that the target grouping columns in the target linked list LST can all uniquely identify other grouping columns of the table to which they belong.

[0113] Step 7, traverse the target linked list LST, and determine whether there is a target grouping column of a certain node in the target linked list LST that can uniquely identify the current grouping column COL. If there is a target grouping column of a certain node on the target linked list LST that can uniquely identify the current grouping column COL, then mark the current grouping column COL so that it can be ignored during the actual grouping calculation. Continue to return to execute the operation of traversing each grouping column in the grouping clause in step 6.

[0114] It should be noted that the relationship between the table to which the target grouping column recorded by each node on the target linked list LST belongs and the table to which the current grouping column belongs is analyzed. The target grouping column recorded by the node on the target linked list LST and its table are C1 and A respectively, and the current grouping column and its table are D1 and B respectively. If there is a relationship between the two tables "A.C2=B.D2 and the current grouping column D2 is the feature column of table B (where C2 can be any column in table A, and D2 can be any feature column in table B)", it is considered that the target grouping column C1 of the node on the target linked list LST can uniquely identify the current grouping column COL, that is, D1.

[0115] Step 8: Initialize the weight tag value (the weight tag value can be represented by FLAG) corresponding to each node on the target linked list LST to 0, and initialize the replacement table (the replacement table can be represented by TAB) corresponding to each node to empty. On this basis, start traversing each node in the target linked list LST, record the currently traversed node as node c, and execute step 9. After the traversal of step 8 is completed, execute step 12.

[0116] Step 9, traverse each node in the target linked list LST, record the currently traversed node as node d, if node c is the same as node d, then continue to return to execute the operation of traversing each node in the target linked list LST in step 9. If node c is not the same as node d, then execute step 10. After the traversal of step 9 is completed, continue to return to execute the traversal of step 8;

[0117] Step 10: Determine whether the grouping column of node c can uniquely identify the grouping column of node d. If not, return to step 9 to continue traversing each node in the target linked list LST; otherwise, execute step 11.

[0118] Step 11: Determine the values ​​of the weight tag value FLAG_C of node c and the weight tag value FLAG_D of node d, which can be divided into the following 7 cases:

[0119] a) If FLAG_C and FLAG_D are both 0, FLAG_C can be set to 1 and FLAG_D to 2. At the same time, the replacement table TAB_C of node c is set to the target group column table C of node c, and the replacement table TAB_D of node d is set to the target group column table C of node c;

[0120] b) If FLAG_C is 1 and FALG_D is 0, FLAG_D can be set to 2, and the substitution table TAB_D of node d is set to the substitution table TAB_C corresponding to node c;

[0121] c) If FLAG_C is 2 and FLAG_D is 0, FLAG_D can be set to 2, and the substitution table TAB_D of node d is set to TAB_C (TAB_C is the substitution table corresponding to node c);

[0122] d) If FLAG_C is 0 and FLAG_D is 1, FLAG_C can be set to 1, FLAG_D can be set to 2, and at the same time, the replacement table TAB_C of node c is set to the target group column table C of node c, and the replacement table TAB_D of node d is set to the target group column table C of node c; in addition, the target linked list LST can be traversed, and if there is a node whose replacement table recorded is the replacement table TAB_D corresponding to node d, the replacement table of the node can be set to the target group column table C of node c;

[0123] e) If FLAG_C is 1 and FLAG_D is 1, then FLAG_D can be set to 2, and the replacement table TAB_D of node d is table C. In addition, traverse the linked list LST, and if there is a node record whose replacement table is table D, then set the replacement table of the node to the replacement table TAB_C corresponding to node c;

[0124] f) If FLAG_C is 2 and FLAG_D is 1, determine whether TAB_C is table D. If so, keep FLAG_C, FLAG_D, TAB_C and TAB_D unchanged; if not, set FLAG_D to 2, and the replacement table TAB_D of node d is the replacement table TAB_C corresponding to node c. In addition, traverse the target linked list LST. If there is a node record whose replacement table is the replacement table TAB_D corresponding to node d, then set the replacement table of the node to the replacement table TAB_C corresponding to node c.

[0125] g) If FLAG_D is 2, it means that the target group column in node d corresponding to FLAG_D has been uniquely identified by the target group column in other nodes, and no processing is required. Continue to return to the operation of traversing each node in the target linked list LST in step 9.

[0126] Step 12, traverse the target linked list LST, for all nodes with a weight mark value of 2, mark the target grouping column of the node to be ignored in the actual grouping calculation, and continue to step 13;

[0127] Step 13: The optimization is completed (i.e., the optimization of the target SQL statement is completed).

[0128] It should be noted that the SELECT item in the target SQL statement may contain grouping columns, so the grouping columns that can be ignored cannot be directly removed from the target SQL statement. The grouping columns can be ignored during the actual grouping calculation.

[0129] On the basis of the above embodiment, the purpose of step 4 and step 5 is to find out the characteristic column in the grouping column contained in the grouping clause, and add it to the initial linked list LST to construct the target linked list LST. Among them, each table (the table can be understood as the table to which the grouping column belongs) has at most one characteristic column in the target linked list LST. Steps 6 and 7 can find out the grouping columns in all tables not recorded in the target linked list LST by analyzing the relationship between the tables, and can be identified by the characteristic column (i.e., the target grouping column) in the target linked list LST. Steps 8, 9, 10, and 11 can find out the target grouping columns in all tables recorded in the target linked list LST by analyzing the relationship between the tables, and can simplify the relationship between each target grouping column according to the weight tag value FLAG and the replacement table TAB.

[0130] Embodiment 2

[0131] Figure 3 This is a schematic diagram of the structure of a structured query statement optimization device provided in the second embodiment of the present invention. The device can be implemented by software and / or hardware. Figure 3 As shown, the device includes: a creation module 310, a first determination module 320, a second determination module 330, a third determination module 340 and an optimization module 350;

[0132] The creation module 310 is used to create an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause;

[0133] The first determination module 320 is used to determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding table to which the target grouping column belongs, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column;

[0134] A second determination module 330 is configured to determine a first identifiable grouping column among all the grouping columns of the grouping clause according to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list;

[0135] A third determination module 340 is used to determine a second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag value corresponding to each node in the target linked list;

[0136] The optimization module 350 is configured to optimize the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column.

[0137] In this embodiment, the device first creates an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause through a creation module; secondly, through a first determination module, the grouping columns belonging to the feature columns in the grouping clause are determined, and the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns are added to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding target grouping column table, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; then, through a second determination module, according to all the grouping columns of the grouping clause and the corresponding grouping column table, and the target linked list, the first identifiable grouping column in all the grouping columns of the grouping clause is determined; then, through a third determination module, according to the weight tag value corresponding to each node in the target linked list, the second identifiable grouping column in the target grouping columns contained in the target linked list is determined; finally, through an optimization module, according to the first identifiable grouping column and the second identifiable grouping column, the target structured query statement is optimized. Using this device, the execution efficiency of SQL statements can be effectively improved.

[0138] Optionally, the creation module 310 specifically includes:

[0139] An analysis unit, used for performing lexical analysis and grammatical analysis on the acquired structured query statement to obtain a target structured query statement;

[0140] A grouping clause judgment unit, used to judge whether the target structured query statement contains a grouping clause;

[0141] A first exit unit is used to exit the optimization if the grouping clause is not included;

[0142] an outer join judgment unit, for judging whether the grouping clause has an outer join if the grouping clause is included;

[0143] The second exit unit is used to exit the optimization if an outer connection exists;

[0144] a grouping column number judging unit, used for judging whether the number of grouping columns in the grouping clause is greater than 1 if there is no outer join;

[0145] A third exit unit, configured to exit the optimization if the number of grouping columns is not greater than 1;

[0146] A creation unit is used to create an initial linked list if the number of grouping columns is greater than 1.

[0147] Optionally, the first determining module 320 specifically includes:

[0148] A feature column judgment unit, used to traverse each grouping column in the grouping clause and judge whether the current grouping column is a feature column of the table to which it belongs;

[0149] A first belonging table judgment unit is used for traversing each node in the current initial linked list to judge whether the current initial linked list contains the table to which the current grouping column belongs if the current grouping column is a feature column of the table to which the current grouping column belongs;

[0150] an adding unit, configured to use the current grouping column as a target grouping column if the current initial linked list does not contain the table to which the current grouping column belongs, and add the target grouping column and the table to which the target grouping column belongs to a newly added node of the current initial linked list;

[0151] The target linked list determining unit is used to use the current initial linked list at the end of the traversal as the target linked list.

[0152] Optionally, the second determining module 330 specifically includes:

[0153] A second belonging table judgment unit, used for traversing each grouping column in the grouping clause, and judging whether the target linked list contains the table to which the current grouping column belongs;

[0154] A first identification judgment unit is used for traversing each target grouping column included in the target linked list to judge whether the target linked list contains a target grouping column that can uniquely identify the current grouping column if the target linked list does not contain the table to which the current grouping column belongs;

[0155] The first determining unit is configured to determine that the current grouping column is a first identifiable grouping column.

[0156] Optionally, the weight tag value includes a first set value, a second set value and a third set value; when the weight tag value is the first set value, the corresponding target grouping column does not uniquely identify other target grouping columns, nor is it uniquely identified by the other target grouping columns; when the weight tag value is the second set value, the corresponding target grouping column can uniquely identify one or more other target grouping columns; when the weight tag value is the third set value, the corresponding target grouping column is uniquely identified by one or more other target grouping columns.

[0157] Optionally, each node of the target linked list further includes a replacement table corresponding to the corresponding target grouping column;

[0158] Based on the above embodiment, the device further includes:

[0159] A first initialization module, configured to initialize the weight tag value corresponding to each node in the target linked list to a first set value before determining a second identifiable grouping column in the target grouping column contained in the target linked list according to the weight tag value corresponding to each node in the target linked list;

[0160] The second initialization module is used to initialize the replacement table corresponding to each node in the target linked list to be empty.

[0161] Optionally, the third determining module 340 specifically includes:

[0162] A selection unit, used for selecting any two different nodes in the target linked list, and determining whether a target grouping column in one of the nodes can uniquely identify a target grouping column in another node;

[0163] A second determining unit is configured to, if yes, record one of the nodes as a first node, record the other node as a second node, and determine a second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag values ​​respectively corresponding to the first node and the second node;

[0164] The return unit is used to return to the operation of selecting any two different nodes in the target linked list, until there are no two nodes in the target linked list that are not selected at the same time, wherein at least one node is different in each selected node.

[0165] Optionally, the second determining unit specifically includes:

[0166] A first setting subunit is used for setting the weight tag values ​​corresponding to the first node and the second node to the second setting value and the third setting value respectively if the weight tag value corresponding to the first node is the first setting value and the weight tag value corresponding to the second node is the first setting value or the second setting value, and setting the replacement table corresponding to the first node and the second node to the target group column belonging table of the first node;

[0167] A second setting subunit is used for setting the weight tag value corresponding to the second node to the third setting value if the weight tag value corresponding to the first node is the second setting value or the third setting value, and the weight tag value corresponding to the second node is the first setting value, and setting the replacement table corresponding to the second node to the replacement table corresponding to the first node;

[0168] A third setting subunit is used for setting the weight tag value corresponding to the second node to a third setting value if the weight tag value corresponding to the first node is the second setting value and the weight tag value corresponding to the second node is the second setting value, and setting the replacement table corresponding to the second node to the replacement table corresponding to the first node;

[0169] A judging subunit, configured to judge whether the replacement table corresponding to the first node is the table to which the target group column in the second node belongs if the weight mark value corresponding to the first node is the third set value and the weight mark value corresponding to the second node is the second set value;

[0170] a fourth setting subunit, configured to, if yes, keep the current settings of the weight tag values ​​and the replacement table in the first node and the second node unchanged;

[0171] a fifth setting subunit, configured to, if no, set the weight tag value corresponding to the second node to a third set value, and set the replacement table corresponding to the second node to the replacement table corresponding to the first node;

[0172] a sixth setting subunit, configured to keep the current settings of the weight tag value and the replacement table in the second node unchanged if the weight tag value corresponding to the second node is a third set value;

[0173] A determination subunit is used to determine the target grouping column in the node whose weight tag value is a third set value in the target linked list as the second identifiable grouping column after the weight tag value and the replacement table corresponding to each node in the target linked list are set.

[0174] Optionally, when the weight mark value corresponding to the second node is a second set value, the second determining unit further includes:

[0175] The seventh setting sub-unit is used to set the replacement table of the specified node in the target linked list to the replacement table corresponding to the first node before setting the weight tag value and replacement table corresponding to the first node and the second node, wherein the specified node includes a node whose replacement table is the replacement table of the second node.

[0176] The above-mentioned structured query statement optimization device can execute the structured query statement optimization method provided by any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0177] Embodiment 3

[0178] Figure 4 This is a schematic diagram of the structure of an electronic device provided by Embodiment 3 of the present invention. Figure 4 As shown, the electronic device provided by the third embodiment of the present invention includes: a storage device 42 and one or more processors 41; the processor 41 in the electronic device may be one or more, Figure 4 A processor 41 is taken as an example; the storage device 42 is used to store one or more programs; the one or more programs are executed by the one or more processors 41, so that the one or more processors 41 implement the structured query statement optimization method as described in any one of the embodiments of the present invention.

[0179] The electronic device may further include: a communication device 43 , an input device 44 and an output device 45 .

[0180] The processor 41, storage device 42, communication device 43, input device 44 and output device 45 in the electronic device may be connected via a bus or other means. Figure 4 The example of connecting through bus is taken in the following.

[0181] The storage device 42 in the electronic device is a computer-readable storage medium, which can be used to store one or more programs, and the program can be a software program, a computer executable program, and a module, such as the program instructions / modules corresponding to the structured query statement optimization method provided in the first embodiment of the present invention (for example, the attached Figure 3 The modules in the structured query statement optimization device shown include: a creation module 310, a first determination module 320, a second determination module 330, a third determination module 340 and an optimization module 350). The processor 41 executes various functional applications and data processing of the electronic device by running the software programs, instructions and modules stored in the storage device 42, that is, the structured query statement optimization method in the above method embodiment is implemented.

[0182] The storage device 42 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and at least one application required for a function; the data storage area may store data created according to the use of the electronic device, etc. In addition, the storage device 42 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some instances, the storage device 42 may further include a memory remotely arranged relative to the processor 41, and these remote memories may be connected to the electronic device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0183] The communication device 43 may include a receiver and a transmitter. The communication device 43 is configured to perform information transmission and reception communication according to the control of the processor 41.

[0184] The input device 44 may be used to receive input digital or character information and generate key signal input related to user settings and function control of the electronic device. The output device 45 may include a display device such as a display screen.

[0185] Furthermore, when one or more programs included in the above-mentioned electronic device are executed by the one or more processors 41, the program performs the following operations: for a target structured query statement containing multiple grouping columns in a grouping clause, an initial linked list is created; the grouping columns belonging to the feature columns in the grouping clause are determined, and the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns are added to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding target grouping column table, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; according to all the grouping columns of the grouping clause and the corresponding grouping column table, and the target linked list, a first identifiable grouping column among all the grouping columns of the grouping clause is determined; according to the weight tag value corresponding to each node in the target linked list, a second identifiable grouping column among the target grouping columns contained in the target linked list is determined; according to the first identifiable grouping column and the second identifiable grouping column, the target structured query statement is optimized.

[0186] Embodiment 4

[0187] Embodiment 4 of the present invention provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, it is used to execute a structured query statement optimization method. The method includes: for a target structured query statement containing multiple grouping columns in a grouping clause, create an initial linked list; determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding target grouping column table, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; according to all the grouping columns of the grouping clause and the corresponding grouping column table, and the target linked list, determine a first identifiable grouping column among all the grouping columns of the grouping clause; according to the weight tag value corresponding to each node in the target linked list, determine a second identifiable grouping column among the target grouping columns contained in the target linked list; according to the first identifiable grouping column and the second identifiable grouping column, optimize the target structured query statement.

[0188] Optionally, when the program is executed by a processor, it can also be used to execute the structured query statement optimization method provided by any embodiment of the present invention.

[0189] The computer storage medium of the embodiment of the present invention may adopt any combination of one or more computer-readable media. The computer-readable medium may be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples (non-exhaustive list) of computer-readable storage media include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable CD-ROM, an optical storage device, a magnetic storage device, or any suitable combination of the above. The computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, device or device.

[0190] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, which carry computer-readable program code. Such propagated data signals may take a variety of forms, including but not limited to: electromagnetic signals, optical signals, or any suitable combination of the above. Computer-readable signal media may also be any computer-readable medium other than a computer-readable storage medium, which may send, propagate, or transmit a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0191] The program code contained on the computer-readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, optical cable, or radio frequency (RF), etc., or any suitable combination of the above.

[0192] Computer program code for performing the operation of the present invention may be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may 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 may 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 may be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0193] 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 described 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 structured query statement optimization method, characterized in that: The method comprises: For a target structured query statement containing multiple grouping columns in the grouping clause, create an initial linked list; Determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding table to which the target grouping column belongs, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; According to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list, determine a first identifiable grouping column among all the grouping columns of the grouping clause; Determine, according to the weight tag value corresponding to each node in the target linked list, a second identifiable grouping column in the target grouping column included in the target linked list; optimizing the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column; The step of determining, according to the weight tag value corresponding to each node in the target linked list, a second identifiable grouping column in the target grouping column included in the target linked list comprises: Select any two different nodes in the target linked list, and determine whether the target grouping column in one of the nodes can uniquely identify the target grouping column in the other node; If yes, one of the nodes is recorded as the first node, the other node is recorded as the second node, and according to the weight tag values ​​respectively corresponding to the first node and the second node, a second identifiable grouping column in the target grouping column included in the target linked list is determined; If not, the operation of selecting any two different nodes in the target linked list is returned to be executed until there are no two nodes in the target linked list that are not selected at the same time, wherein at least one of the nodes selected each time is different.

2. The method according to claim 1, characterized in that The step of creating an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause includes: Performing lexical analysis and grammatical analysis on the acquired structured query statement to obtain a target structured query statement; Determining whether the target structured query statement contains a grouping clause; If the grouping clause is not included, then the optimization is exited; If a grouping clause is included, determine whether the grouping clause has an outer join; If there is an outer join, exit the optimization; If there is no outer join, determine whether the number of grouping columns in the grouping clause is greater than 1; If the number of grouping columns is not greater than 1, then exit the optimization; If the number of grouping columns is greater than 1, an initial linked list is created.

3. The method according to claim 1, characterized in that The step of determining the grouping columns belonging to the feature columns in the grouping clause, and adding the target grouping columns that meet the preset conditions and the table to which the target grouping columns belong in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list includes: Traverse each grouping column in the grouping clause to determine whether the current grouping column is a characteristic column of the table to which it belongs; If the current grouping column is a feature column of the table to which it belongs, traverse each node in the current initial linked list to determine whether the current initial linked list contains the table to which the current grouping column belongs; If the current initial linked list does not contain the table to which the current grouping column belongs, the current grouping column is used as the target grouping column, and the target grouping column and the table to which the target grouping column belongs are added to the newly added node of the current initial linked list; The current initial linked list at the end of the traversal is used as the target linked list.

4. The method according to claim 1, characterized in that: The step of determining, according to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list, a first identifiable grouping column among all the grouping columns of the grouping clause comprises: Traverse each grouping column in the grouping clause to determine whether the target linked list contains the table to which the current grouping column belongs; If the target linked list does not contain the table to which the current grouping column belongs, traverse each target grouping column contained in the target linked list to determine whether the target linked list contains a target grouping column that can uniquely identify the current grouping column; If so, the current grouping column is determined to be the first identifiable grouping column.

5. The method according to claim 1, characterized in that The weight mark value includes a first setting value, a second setting value and a third setting value; When the weight mark value is the first set value, the corresponding target grouping column does not uniquely identify other target grouping columns, nor is it uniquely identified by the other target grouping columns; When the weight mark value is the second set value, the corresponding target grouping column can uniquely identify one or more other target grouping columns; When the weight tag value is the third set value, the corresponding target grouping column is uniquely identified by one or more other target grouping columns.

6. The method according to claim 5, characterized in that Each node of the target linked list also includes a replacement table corresponding to the corresponding target grouping column; Before determining the second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag value corresponding to each node in the target linked list, the method further includes: Initialize the weight mark value corresponding to each node in the target linked list to a first set value; Initialize the replacement table corresponding to each node in the target linked list to be empty.

7. The method according to claim 1, characterized in that The determining, according to the weight tag values ​​respectively corresponding to the first node and the second node, a second identifiable grouping column in the target grouping column included in the target linked list comprises: If the weight tag value corresponding to the first node is the first set value and the weight tag value corresponding to the second node is the first set value or the second set value, the weight tag values ​​corresponding to the first node and the second node are respectively set to the second set value and the third set value, and the replacement table corresponding to the first node and the second node is set to the table to which the target group column of the first node belongs; If the weight mark value corresponding to the first node is the second set value or the third set value, and the weight mark value corresponding to the second node is the first set value, then the weight mark value corresponding to the second node is set to the third set value, and the replacement table corresponding to the second node is set to the replacement table corresponding to the first node; If the weight tag value corresponding to the first node is the second set value, and the weight tag value corresponding to the second node is the second set value, then the weight tag value corresponding to the second node is set to the third set value, and the replacement table corresponding to the second node is set to the replacement table corresponding to the first node; If the weight mark value corresponding to the first node is the third set value, and the weight mark value corresponding to the second node is the second set value, determining whether the replacement table corresponding to the first node is the table to which the target group column in the second node belongs; If yes, keeping the current settings of the weight tag values ​​and the replacement table in the first node and the second node unchanged; If not, setting the weight tag value corresponding to the second node to a third set value, and setting the replacement table corresponding to the second node to the replacement table corresponding to the first node; If the weight mark value corresponding to the second node is the third set value, keeping the current settings of the weight mark value and the replacement table in the second node unchanged; After the weight mark value and the replacement table corresponding to each node in the target linked list are set, the target grouping column in the node in the target linked list whose weight mark value is the third set value is determined as the second identifiable grouping column.

8. The method according to claim 7, characterized in that In a case where the weight tag value corresponding to the second node is a second set value, determining the second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag values ​​respectively corresponding to the first node and the second node, further includes: Before setting the weight tag values ​​and replacement tables corresponding to the first node and the second node, the replacement table of the specified node in the target linked list is set to the replacement table corresponding to the first node, wherein the specified node includes a node whose replacement table is the replacement table of the second node.

9. A structured query statement optimization device, characterized in that: The device comprises: A creation module is used to create an initial linked list for a target structured query statement containing multiple grouping columns in a grouping clause; A first determination module is used to determine the grouping columns belonging to the feature columns in the grouping clause, and add the target grouping columns and the table to which the target grouping columns belong that meet the preset conditions in the grouping columns belonging to the feature columns to the initial linked list to construct a target linked list, wherein each node in the target linked list is used to store a target grouping column and a corresponding table to which the target grouping column belongs, and each node in the target linked list includes a weight tag value corresponding to the corresponding target grouping column; A second determination module, configured to determine a first identifiable grouping column among all the grouping columns of the grouping clause according to all the grouping columns of the grouping clause and the table to which the corresponding grouping columns belong, and the target linked list; A third determination module, configured to determine a second identifiable grouping column in the target grouping column included in the target linked list according to a weight tag value corresponding to each node in the target linked list; an optimization module, configured to optimize the target structured query statement according to the first identifiable grouping column and the second identifiable grouping column; The third determining module comprises: A selection unit, used for selecting any two different nodes in the target linked list, and determining whether a target grouping column in one of the nodes can uniquely identify a target grouping column in another node; A second determining unit is configured to, if yes, record one of the nodes as a first node, record the other node as a second node, and determine a second identifiable grouping column in the target grouping column included in the target linked list according to the weight tag values ​​respectively corresponding to the first node and the second node; The return unit is used to return to the operation of selecting any two different nodes in the target linked list, until there are no two nodes in the target linked list that are not selected at the same time, wherein at least one node is different in each selected node.

10. An electronic device, characterized in that: include: 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 structured query statement optimization method as described in any one of claims 1 to 8.

11. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the program is executed by a processor, the structured query statement optimization method as described in any one of claims 1 to 8 is implemented.

Images