CN111309753A - Method, device and equipment for optimizing structured query statement and storage medium - Google Patents
Method, device and equipment for optimizing structured query statement and storage medium Download PDFInfo
- Publication number
- CN111309753A CN111309753A CN202010070928.2A CN202010070928A CN111309753A CN 111309753 A CN111309753 A CN 111309753A CN 202010070928 A CN202010070928 A CN 202010070928A CN 111309753 A CN111309753 A CN 111309753A
- Authority
- CN
- China
- Prior art keywords
- grouping
- statement
- item
- eliminated
- determining
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 238000000034 method Methods 0.000 title claims abstract description 36
- 230000008030 elimination Effects 0.000 claims abstract description 24
- 238000003379 elimination reaction Methods 0.000 claims abstract description 24
- 230000014509 gene expression Effects 0.000 claims description 7
- 238000004590 computer program Methods 0.000 claims description 2
- 238000004364 calculation method Methods 0.000 abstract description 8
- 230000000694 effects Effects 0.000 abstract description 4
- 238000005265 energy consumption Methods 0.000 abstract description 4
- 238000005457 optimization Methods 0.000 description 12
- 230000006870 function Effects 0.000 description 5
- 238000010586 diagram Methods 0.000 description 4
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 238000010295 mobile communication Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000008707 rearrangement Effects 0.000 description 1
- 239000007787 solid Substances 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/244—Grouping and aggregation
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The invention discloses a method, a device, equipment and a storage medium for optimizing a structured query statement. The method comprises the following steps: acquiring a structured query SQL statement containing a grouping statement; determining the grouping items to be eliminated, which are contained in the grouping statement and meet the elimination condition; and eliminating the grouping item to be eliminated from the SQL statement to obtain a target SQL statement. The invention eliminates the to-be-eliminated grouped items from the SQL statement by determining the to-be-eliminated grouped items meeting the elimination condition contained in the grouped statement to obtain the target SQL statement, reduces the number of the grouped items when the target SQL statement is executed, simplifies the grouped calculation, solves the problems of excessive grouped items or complex calculation and energy consumption, and realizes the effect of quickly finishing the grouping.
Description
Technical Field
The embodiment of the invention relates to the technical field of databases, in particular to an optimization method, device, equipment and storage medium of a structured query statement.
Background
Structured Query Language (SQL), a database Query and programming Language for accessing data and querying, updating, and managing relational database systems.
In the database operation based on the SQL statement, the grouping is a common operation in the SQL statement, however, in practical applications, if too many grouping items are included in the formed SQL statement, or the grouping items included in the SQL statement require complex computation during execution, the grouping operation will occupy more computation resources, and affect the execution performance of the SQL statement.
Disclosure of Invention
The invention provides an optimization method, device, equipment and storage medium of a structured query statement, which are used for optimizing the calculated amount of grouping operation and improving the performance of the structured query statement.
In a first aspect, an embodiment of the present invention provides a method for optimizing a structured query statement, including:
acquiring a structured query SQL statement containing a grouping statement;
determining the grouping items to be eliminated, which are contained in the grouping statement and meet the elimination condition;
and eliminating the grouping item to be eliminated from the SQL statement to obtain a target SQL statement.
Optionally, the determining the to-be-eliminated grouping items meeting the elimination condition and included in the grouping statement includes:
traversing the grouping items contained in the grouping statement;
if a first grouping item with a primary key identification exists in the grouping statement, determining a target data table corresponding to the first grouping item;
and if a second grouping item which belongs to the target data table and is not a primary key exists in the grouping statement, determining the second grouping item as the grouping item to be eliminated when the second grouping item meets a set reference judgment condition.
Optionally, the step that the second grouping item satisfies the set reference determination condition includes:
determining whether the second grouping item is referenced in an execution logic layer above the grouping statement;
if not, determining that the second grouping item meets the reference judgment condition.
Optionally, the determining whether the second grouping item is referenced in an execution logic layer above the grouping statement includes:
analyzing the SQL statement to obtain a logic plan tree corresponding to the SQL statement;
determining an operator node corresponding to the grouping statement in the logic plan tree, and determining an upper layer operator node related to the operator node in the logic plan tree;
and judging whether the second packet item is contained in the execution expression corresponding to each upper-layer operator node.
Optionally, the removing the to-be-removed grouping item from the SQL statement to obtain a target SQL statement includes:
acquiring a selection item contained in the selected statement in the SQL statement, and determining the selection item matched with the grouped item to be eliminated as a selection item to be eliminated;
and eliminating the selection items to be eliminated in the selection statements, eliminating the grouping items to be eliminated in the grouping statements, and obtaining the target SQL statement.
In a second aspect, an embodiment of the present invention further provides an apparatus for optimizing a structured query statement, where the apparatus includes:
the statement acquisition module is used for acquiring a structured query SQL statement containing a grouped statement;
the elimination determining module is used for determining the grouping items to be eliminated, which meet the elimination condition and are contained in the grouping statement;
and the statement determining module is used for eliminating the grouping item to be eliminated from the SQL statement to obtain a target SQL statement.
In a third aspect, an embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor, when executing the program, implements the method for optimizing the structured query statement according to any embodiment of the present invention.
In a fourth aspect, embodiments of the present invention further provide a storage medium containing computer-executable instructions, which when executed by a computer processor, are configured to perform a method for optimizing a structured query statement according to any of the embodiments of the present invention.
The invention eliminates the to-be-eliminated grouped items from the SQL statement by determining the to-be-eliminated grouped items meeting the elimination condition contained in the grouped statement to obtain the target SQL statement, reduces the number of the grouped items when the target SQL statement is executed, simplifies the grouped calculation, solves the problems of excessive grouped items or complex calculation and energy consumption, and realizes the effect of quickly finishing the grouping.
Drawings
FIG. 1 is a flowchart of a method for optimizing a structured query statement according to an embodiment of the present invention;
FIG. 2 is a flowchart illustrating an implementation of determining a grouping item to be eliminated in the structured query optimization method according to an embodiment of the present invention;
FIG. 3 is a flowchart illustrating an implementation of determining that a grouping item satisfies a set reference determination condition in the method for optimizing a structured query according to an embodiment of the present invention;
FIG. 4 is a block diagram of an apparatus for optimizing a structured query statement according to a second embodiment of the present invention;
fig. 5 is a block diagram of a computer device according to a third embodiment of the present invention.
Detailed Description
The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.
Example one
Fig. 1 is a flowchart of a method for optimizing a structured query statement according to an embodiment of the present invention, where the embodiment is applicable to a case of optimizing an SQL statement that includes a grouped statement, and the method may be executed by an optimizing apparatus for a structured query statement, and the apparatus may be implemented by software and/or hardware.
As shown in fig. 1, the method specifically includes the following steps:
The data in the database is often queried, so the data query is a core operation of the database, in various database operations often implemented BY SQL statements in database applications, the data query may also be implemented BY SQL language, it can be known that the premise of various operations performed BY SQL statements is to SELECT a data object to be operated, and the selection of the data operation object may be implemented BY constructing a corresponding SELECT statement in combination with a specific scenario, and in a scenario in which a grouping operation needs to be performed, the constructed SELECT statement needs to include a GROUP BY clause for grouping implementation. In the present embodiment, the GROUP BY statement is referred to as a grouping statement.
Specifically, in this embodiment, in order to optimize the SQL statement including the GROUP statement, the currently formed SQL statement may be traversed through this step, so as to obtain the initial SQL statement including the GROUP BY statement.
Illustratively, the following provides an SQL statement related to a packet statement application scenario, which is specifically expressed as follows:
create table nn1(c1 int,c2 int,c3 int,primary key(c1))；
create table nn2(d1 int,d2 int,d3 int,primary key(d1))；
create table nn3(e1 int,e2 int,e3 int,primary key(e1))；
with v as(select abs(c2)cc1,c1,d1,c2+c3,count(*)cnt
from nn1 a,nn2 b,nn3 c
where c1＝d1 and d2＝e2
group by abs(c2),c1,d1,c2+c3
having count(*)>4)
select cnt from v；
the embodiment may perform optimization operation by using the SQL statement.
And step 120, determining the grouping items to be eliminated, which are contained in the grouping statement and meet the elimination condition.
The most common usage of the GROUP BY statement is GROUP BY + [ grouping item ], and there may be one or more grouping items, and when the grouping operation is performed, the data set will be divided into different GROUPs according to the values of the grouping items. In this embodiment, the grouping statement includes at least one grouping item, and when removing a certain grouping item does not affect the grouping result, the grouping item may be referred to as a to-be-removed grouping item. A removal condition may then be understood as a condition that determines whether the presence or absence of a certain grouping item affects the grouping result.
Specifically, when the existence of a certain grouping item does not affect the grouping result, the grouping item may be considered to satisfy the elimination condition, and may be determined as the grouping item to be eliminated.
Optionally, fig. 2 is a flowchart illustrating an implementation flow of determining a to-be-eliminated grouped item in the structured query optimization method according to an embodiment of the present invention, and as shown in fig. 2, the step 120 "determining a to-be-eliminated grouped item that meets an elimination condition included in a grouped statement" is further specifically optimized to be implemented by the following steps:
Illustratively, following the SQL statement example given above, the GROUP BY statement it contains may be traversed, thereby determining that the grouping entries in the GROUP BY statement are abs (c2), c1, d1, and c2+ c 3.
In this embodiment, the grouped item with the primary key identifier is referred to as a first grouped item, and the data table corresponding to the grouped item with the primary key identifier is referred to as a target data table.
Illustratively, in the above example, grouping item c1 is the primary key of data table nn1, and has a primary key identifier, so that c1 is determined as the first grouping item, and data table nn1 is the corresponding target data table.
Specifically, when a group item of a non-primary key and a first group item belong to the same target data table, the group item of the non-primary key may be determined as a second group item, and when the second group item satisfies the set reference determination condition, it may be considered that removing the second group item does not affect the grouping result, so that the second group item may be determined as a group item to be eliminated.
Optionally, the step that the second grouping item satisfies the set reference determination condition may include: determining whether the second group entry is referenced in an execution logic layer above the group statement; if not, determining that the second grouping item meets the reference judgment condition.
For example, in the above example, grouping items c1, c2, c3 are from the same data table, and c1 is a primary key, then c1 may identify c2 and c3, abs (c2) and c2+ c3 are second grouping items, and at the same time abs (c2) and c2+ c3 are not referenced in the upper layer, and v only needs cnt in the upper layer, then two grouping items, abs (c2) and c2+ c3, may be determined as grouping items to be eliminated.
Optionally, fig. 3 is a flowchart illustrating an implementation of determining that a grouping item satisfies a set reference determination condition in the structured query optimization method according to an embodiment of the present invention, and as shown in fig. 3, determining that a second grouping item satisfies the set reference determination condition may specifically be implemented by:
The SQL statement is converted into a logic plan tree, referred to as a rel tree for short, through syntax parsing and lexical parsing in the DBMS. The execution process of the logic plan tree comprises the following steps: the data stream passes from bottom to top.
Illustratively, as shown below, the SQL statement in step 110 is converted into a rel tree form through syntax parsing and lexical parsing.
In this embodiment, each step to be executed in the logic plan tree (as described in 1-9 above) includes a node to be executed, and the node to be executed corresponds to an operator node corresponding to the SQL statement. Generally, the execution order of the operator nodes in the logical plan tree is from bottom to top, but for operator nodes in the same level, such as # TAB (NN3 as C) and # TAB (NN2 as B) in this example, their execution order is controlled by the parent node (# CROSS).
To determine whether the second grouping item in this embodiment satisfies the reference condition, first, an operator node # group by the grouping statement corresponding to the rel tree is determined through this step, and a specific position of the operator node in the rel tree is determined to be processed, for example, the operator node # group is currently located at the layer 4; the associated upper operator nodes at the upper level of the operator node # GROUPBY can then be determined to be # PRJT2, # PRJT2, and # SLCT2 at levels 3, 2, and 1, respectively.
The above steps determine the upper layer operator nodes # PRJT2, # PRJT2 and # SLCT2 of the operator node # group, and may obtain the execution expression associated in the SQL statement corresponding to the rel tree by each upper layer operator node, thereby determining whether the execution expression corresponding to each upper layer operator node includes the second grouping item by executing the operation of this step.
For example, the execution expressions corresponding to abs (c2) and c2+ c3 as the second grouping items in the example at the upper operator nodes # PRJT2, # PRJT2 and # SLCT2 do not appear, and therefore, it can be considered that the second grouping items all satisfy the reference decision condition, that is, two second grouping items, abs (c2) and c2+ c3, can be determined as the grouping items to be eliminated.
And step 130, eliminating the grouping items to be eliminated from the SQL statements to obtain target SQL statements.
The target SQL statement can be understood as the SQL statement after the grouped items to be eliminated, which meet the elimination condition, are eliminated from the SQL statement.
Specifically, the to-be-eliminated grouped items meeting the elimination condition determined in step 120 are eliminated from the SQL statement, so as to obtain the target SQL statement.
Optionally, step 130 may be specifically implemented by the following method:
acquiring a selection item contained in a selection statement in an SQL statement, and determining the selection item matched with the grouping item to be eliminated as a selection item to be eliminated; and eliminating the selection items to be eliminated in the selection statements, eliminating the grouping items to be eliminated in the grouping statements, and obtaining the target SQL statement.
The selection of the data operation object in the SQL statement may be implemented by constructing a corresponding SELECT statement in combination with a specific scenario, and in this embodiment, the SELECT statement is referred to as a SELECT statement. The most common use of a SELECT statement is the SELECT [ SELECT ] FROM data source. Because the query terms can only be grouped terms and union functions, the grouped terms in the grouped statement have corresponding choices in the selection statement, e.g., in the above example, the grouped terms abs (c2) and c2+ c3 to be eliminated determined in step 120 correspond to the choices abs (c2) and c2+ c3 in the selection terms.
Specifically, after determining the grouping items to be eliminated, searching and confirming corresponding selection items to be eliminated in the selection statements, eliminating the selection items to be eliminated from the selection statements, eliminating the grouping items to be eliminated from the grouping statements, and obtaining the target SQL statements.
Illustratively, in the example given above, the selection items included in the selected statement in the SQL statement are obtained, according to the grouped items abs (c2) and c2+ c3 to be eliminated determined in step 120, the selected items to be eliminated determined in the selection items are abs (c2) and c2+ c3, the selected items abs (c2) and c2+ c3 to be eliminated are eliminated from the selected statement, and the grouped items abs (c2) and c2+ c3 to be eliminated are eliminated from the grouped statement, so as to obtain the converted target SQL statement:
with v as(select c1,d1,count(*)cnt
from nn1a,nn2 b,nn3 c
where c1＝d1 and d2＝e2
group by c1,d1
having count(*)>4)
select cnt from v；
in addition to what happens in the above example, there is another case when step 1203 is performed that when a GROUP BY statement occurs for a portion that is to be shared, then each used portion of the upper operator nodes needs to be checked, such as the following example:
create table nn1(c1 int,c2 int,c3 int,primary key(c1))；
create table nn2(d1 int,d2 int,d3 int,primary key(d1))；
create table nn3(e1 int,e2 int,e3 int,primary key(e1))；
with v as(select abs(c2)cc1,c1,d1,c2+c3,d3,count(*)cnt
from nn1 a,nn2 b,nn3 c
where c1＝d1 and d2＝e2
group by abs(c2),c1,d1,c2+c3,d3
having count(*)>4)
select c1 from vm vm1,vm vm2,vm vm3 where
vm1.cc1＝vm2.d3 and vm1.c1＝vm3.d1；
when the optimization method of this embodiment is used to optimize the SQL statement, first, the SQL statement may be obtained through step 110. Next, step 120 is performed to determine the to-be-eliminated grouped items included in the grouped statement in the SQL statement and satisfying the elimination condition. Specifically, it can be determined through step 1201 that the grouped items in the GROUP BY statement in the SQL statement are abs (c2), c1, d1, c2+ c3, and d 3; it is determined by step 1202 that c1 and d1 are first grouped items and abs (c2), c2+ c3 and d3 are second grouped items. In step 1203, since the group by statement of the part to be shared appears in the SQL statement, step 12031 converts the SQL statement into a pipe type through syntax parsing and lexical parsing:
the pipeline PIPE has the functions of: the right son (6# HTAB) is done first, then the left son (1# CROSS: c1 ═ d1) is executed, and the data of the left son is sent up until there is no more data in the left son. Wherein the HTAB (VM1), the HTAB (VM2) and the HTAB (VM3) are alias names of the HTAB and are essentially the HTAB.
From step 12033, it can be seen that the main plan (layers 1-5) is a join operation of three tables (temporary result table HTAB), and # group is in the shared part HTAB (temporary result table), so the upper layer of # group is required to check not only the three operator nodes # SLCT2, # PRJT2, # HTAB of layers 8, 7, 6, but also the upper layer using HTAB in the main plan, and therefore, the operator nodes at layers 2 and 1 need to be checked at the same time for # CROSS: cc1 ═ d3 and # CROSS: c1 ═ d 1. Since cc1 and d3 are referred to in the operator node # CROSS: cc1 ═ d3, and the second grouping item corresponding to cc1 is abs (c2), step 12034 may determine that the second grouping items abs (c2) and d3 do not satisfy the reference decision condition, and may determine that this grouping item of c2+ c3 is the to-be-eliminated grouping item. Finally, step 130 is performed, a selection item included in the selected statement in the SQL statement is obtained, according to the determined to-be-eliminated grouping item c2+ c3, the determined to-be-eliminated selection item in the selection item is c2+ c3, the to-be-eliminated selection item c2+ c3 is eliminated from the selected statement, the to-be-eliminated grouping item c2+ c3 is eliminated from the SQL statement, and the converted target SQL statement is obtained:
with v as(select abs(c2)cc1,c1,d1,d3,count(*)cnt
from nn1 a,nn2 b,nn3 c
where c1＝d1 and d2＝e2
group by abs(c2),c1,d1,d3
having count(*)>4)
select c1 from vm vm1,vm vm2,vm vm3 where
vm1.cc1＝vm2.d3 and vm1.c1＝vm3.d1；
according to the technical scheme of the embodiment, the grouped items to be eliminated, which meet the elimination condition, contained in the grouped sentences are determined, the grouped items to be eliminated are eliminated from the SQL sentences, the target SQL sentences are obtained, when the target SQL sentences are executed, the number of the grouped items is reduced, the grouped calculation is simplified, the problems of too many grouped items or complicated calculation and energy consumption are solved, and the effect of quickly finishing the grouping is achieved.
Example two
The optimization device for the structured query statement provided by the embodiment of the invention can execute the optimization method for the structured query statement provided by any embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method. Fig. 4 is a block diagram of an apparatus for optimizing a structured query statement according to a second embodiment of the present invention, and as shown in fig. 4, the apparatus includes: a sentence acquisition module 210, a cancellation determination module 220, and a sentence determination module 230.
The statement obtaining module 210 is configured to obtain a structured query SQL statement that includes a grouped statement.
And the elimination determining module 220 is used for determining the grouping items to be eliminated, which are contained in the grouping statement and meet the elimination condition.
And the statement determining module 230 is configured to eliminate the grouped items to be eliminated from the SQL statement, and obtain a target SQL statement.
Optionally, the cancellation determining module 220 includes:
the packet item traversing unit is used for traversing the packet items contained in the packet statements;
the data table determining unit is used for determining a target data table corresponding to a first grouping item if the first grouping item with the primary key identification exists in the grouping statement;
and the elimination item determining unit is used for determining the second grouping item as the grouping item to be eliminated when the second grouping item meets the set reference judgment condition if the second grouping item which belongs to the target data table and is not the primary key exists in the grouping statement.
Optionally, the step that the second grouping item satisfies the set reference determination condition includes:
determining whether the second group entry is referenced in an execution logic layer above the group statement;
if not, determining that the second grouping item meets the reference judgment condition.
Optionally, determining whether the second grouping item is referred to in an execution logic layer above the grouping statement includes:
analyzing the SQL statement to obtain a logic plan tree corresponding to the SQL statement;
determining an operator node corresponding to the grouping statement in the logic plan tree, and determining an upper operator node related to the operator node in the logic plan tree;
and judging whether the second packet item is contained in the execution expression corresponding to each upper-layer operator node.
Optionally, eliminating the to-be-eliminated grouped items from the SQL statement to obtain the target SQL statement, including:
acquiring a selection item contained in a selection statement in an SQL statement, and determining the selection item matched with the grouping item to be eliminated as a selection item to be eliminated;
and eliminating the selection items to be eliminated in the selection statements, eliminating the grouping items to be eliminated in the grouping statements, and obtaining the target SQL statement.
According to the technical scheme of the embodiment, the grouped items to be eliminated, which meet the elimination condition, contained in the grouped sentences are determined, the grouped items to be eliminated are eliminated from the SQL sentences, the target SQL sentences are obtained, when the target SQL sentences are executed, the number of the grouped items is reduced, the grouped calculation is simplified, the problems of too many grouped items or complicated calculation and energy consumption are solved, and the effect of quickly finishing the grouping is achieved.
EXAMPLE III
Fig. 5 is a block diagram of a computer apparatus according to a third embodiment of the present invention, as shown in fig. 5, the computer apparatus includes a processor 310, a memory 320, an input device 330, and an output device 340; the number of the processors 310 in the computer device may be one or more, and one processor 310 is taken as an example in fig. 5; the processor 310, the memory 320, the input device 330 and the output device 340 in the computer apparatus may be connected by a bus or other means, and the connection by the bus is exemplified in fig. 5.
The memory 320 is used as a computer-readable storage medium for storing software programs, computer-executable programs, and modules, such as program instructions/modules corresponding to the optimization method of the structured query statement in the embodiment of the present invention (for example, the statement acquisition module 210, the elimination determination module 220, and the statement determination module 230 in the optimization device of the structured query statement). The processor 310 executes various functional applications and data processing of the computer device by executing software programs, instructions and modules stored in the memory 320, namely, implements the above-described optimization method of the structured query statement.
The memory 320 may mainly include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application program required for at least one function; the storage data area may store data created according to the use of the terminal, and the like. Further, the memory 320 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, the memory 320 may further include memory located remotely from the processor 310, which may be connected to a computer device through a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The input device 330 may be used to receive input numeric or character information and generate key signal inputs related to user settings and function control of the computer apparatus. The output device 340 may include a display device such as a display screen.
Example four
A fourth embodiment of the present invention further provides a storage medium containing computer-executable instructions, which when executed by a computer processor, perform a method for optimizing a structured query statement, the method including:
acquiring a structured query SQL statement containing a grouping statement;
determining the grouping items to be eliminated which are contained in the grouping sentences and meet the elimination conditions;
and eliminating the grouping items to be eliminated from the SQL sentences to obtain the target SQL sentences.
Of course, the storage medium containing the computer-executable instructions provided by the embodiments of the present invention is not limited to the method operations described above, and may also perform related operations in the method for optimizing the structured query statement provided by any embodiment of the present invention.
From the above description of the embodiments, it is obvious for those skilled in the art that the present invention can be implemented by software and necessary general hardware, and certainly, can also be implemented by hardware, but the former is a better embodiment in many cases. Based on such understanding, the technical solutions of the present invention may be embodied in the form of a software product, which may be stored in a computer-readable storage medium, such as a floppy disk, a Read-Only Memory (ROM), a Random Access Memory (RAM), a FLASH Memory (FLASH), a hard disk or an optical disk of a computer, and includes several instructions for enabling a computer device (which may be a personal computer, a server, or a network device) to execute the methods according to the embodiments of the present invention.
It should be noted that, in the embodiment of the above search apparatus, each included unit and module are merely divided according to functional logic, but are not limited to the above division as long as the corresponding functions can be implemented; in addition, specific names of the functional units are only for convenience of distinguishing from each other, and are not used for limiting the protection scope of the present invention.
It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.
Claims (10)
1. A method for optimizing a structured query statement, comprising:
acquiring a structured query SQL statement containing a grouping statement;
determining the grouping items to be eliminated, which are contained in the grouping statement and meet the elimination condition;
and eliminating the grouping item to be eliminated from the SQL statement to obtain a target SQL statement.
2. The method for optimizing the structured query statement according to claim 1, wherein the determining the to-be-eliminated grouped items contained in the grouped statement that satisfy the elimination condition includes:
traversing the grouping items contained in the grouping statement;
if a first grouping item with a primary key identification exists in the grouping statement, determining a target data table corresponding to the first grouping item;
and if a second grouping item which belongs to the target data table and is not a primary key exists in the grouping statement, determining the second grouping item as the grouping item to be eliminated when the second grouping item meets a set reference judgment condition.
3. The method for optimizing a structured query statement according to claim 2, wherein the step of satisfying the set reference judgment condition by the second grouping item includes:
determining whether the second grouping item is referenced in an execution logic layer above the grouping statement;
if not, determining that the second grouping item meets the reference judgment condition.
4. The method of claim 3, wherein said determining whether the second grouped item is referenced in an execution logic layer above the grouped statement comprises:
analyzing the SQL statement to obtain a logic plan tree corresponding to the SQL statement;
determining an operator node corresponding to the grouping statement in the logic plan tree, and determining an upper layer operator node related to the operator node in the logic plan tree;
and judging whether the second packet item is contained in the execution expression corresponding to each upper-layer operator node.
5. The method for optimizing a structured query statement according to any one of claims 1 to 4, wherein said eliminating said to-be-eliminated grouped item from said SQL statement to obtain a target SQL statement comprises:
acquiring a selection item contained in the selected statement in the SQL statement, and determining the selection item matched with the grouped item to be eliminated as a selection item to be eliminated;
and eliminating the selection items to be eliminated in the selection statements, eliminating the grouping items to be eliminated in the grouping statements, and obtaining the target SQL statement.
6. An apparatus for optimizing a structured query statement, comprising:
the statement acquisition module is used for acquiring a structured query SQL statement containing a grouped statement;
the elimination determining module is used for determining the grouping items to be eliminated, which meet the elimination condition and are contained in the grouping statement;
and the statement determining module is used for eliminating the grouping item to be eliminated from the SQL statement to obtain a target SQL statement.
7. The apparatus of claim 6, wherein the cancellation determination module comprises:
the grouping item traversing unit is used for traversing the grouping items contained in the grouping statement;
a data table determining unit, configured to determine, if a first grouping item with a primary key identifier exists in the grouping statement, a target data table corresponding to the first grouping item;
a cancellation item determination unit, configured to, if a second grouping item that belongs to the target data table and is a non-primary key exists in the grouping statement, determine the second grouping item as the to-be-cancelled grouping item when the second grouping item satisfies a set reference determination condition.
8. The apparatus according to claim 7, wherein the step of the second grouping item satisfying the set reference determination condition includes:
determining whether the second grouping item is referenced in an execution logic layer above the grouping statement;
if not, determining that the second grouping item meets the reference judgment condition.
9. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements a method of optimizing a structured query statement as claimed in any one of claims 1 to 5 when executing the program.
10. A storage medium containing computer-executable instructions for performing the method for optimizing a structured query statement as claimed in any one of claims 1 to 5 when executed by a computer processor.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010070928.2A CN111309753B (en) | 2020-01-21 | 2020-01-21 | Optimization method, device, equipment and storage medium of structured query statement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010070928.2A CN111309753B (en) | 2020-01-21 | 2020-01-21 | Optimization method, device, equipment and storage medium of structured query statement |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN111309753A true CN111309753A (en) | 2020-06-19 |
| CN111309753B CN111309753B (en) | 2024-02-06 |
Family
ID=71144927
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010070928.2A Active CN111309753B (en) | 2020-01-21 | 2020-01-21 | Optimization method, device, equipment and storage medium of structured query statement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111309753B (en) |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112100198A (en) * | 2020-09-09 | 2020-12-18 | 上海达梦数据库有限公司 | Database SQL statement optimization method, device, equipment and storage medium |
| CN112506949A (en) * | 2020-12-03 | 2021-03-16 | 北京百度网讯科技有限公司 | Method and device for generating query statement of structured query language and storage medium |
| CN113204568A (en) * | 2021-05-26 | 2021-08-03 | 上海达梦数据库有限公司 | Method, device, equipment and storage medium for optimizing structured query statement |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20040064441A1 (en) * | 2002-09-27 | 2004-04-01 | Tow Daniel S. | Systems and methods for providing structured query language optimization |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
| CN108304505A (en) * | 2018-01-18 | 2018-07-20 | 上海达梦数据库有限公司 | A kind of processing method of SQL statement, device, server and storage medium |
| CN108628975A (en) * | 2018-04-25 | 2018-10-09 | 上海达梦数据库有限公司 | A kind of database SQL optimization and device |
-
2020
- 2020-01-21 CN CN202010070928.2A patent/CN111309753B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20040064441A1 (en) * | 2002-09-27 | 2004-04-01 | Tow Daniel S. | Systems and methods for providing structured query language optimization |
| CN107315790A (en) * | 2017-06-14 | 2017-11-03 | 腾讯科技（深圳）有限公司 | A kind of optimization method and device of irrelevant subquery |
| CN108304505A (en) * | 2018-01-18 | 2018-07-20 | 上海达梦数据库有限公司 | A kind of processing method of SQL statement, device, server and storage medium |
| CN108628975A (en) * | 2018-04-25 | 2018-10-09 | 上海达梦数据库有限公司 | A kind of database SQL optimization and device |
Non-Patent Citations (1)
| Title |
|---|
| 张克猛: "基于关系数据库的SQL查询语句的优化" * |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112100198A (en) * | 2020-09-09 | 2020-12-18 | 上海达梦数据库有限公司 | Database SQL statement optimization method, device, equipment and storage medium |
| CN112100198B (en) * | 2020-09-09 | 2023-12-08 | 上海达梦数据库有限公司 | Database SQL statement optimization method, device, equipment and storage medium |
| CN112506949A (en) * | 2020-12-03 | 2021-03-16 | 北京百度网讯科技有限公司 | Method and device for generating query statement of structured query language and storage medium |
| CN112506949B (en) * | 2020-12-03 | 2023-07-25 | 北京百度网讯科技有限公司 | Method, device and storage medium for generating structured query language query statement |
| CN113204568A (en) * | 2021-05-26 | 2021-08-03 | 上海达梦数据库有限公司 | Method, device, equipment and storage medium for optimizing structured query statement |
Also Published As
| Publication number | Publication date |
|---|---|
| CN111309753B (en) | 2024-02-06 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US10659467B1 (en) | Distributed storage and distributed processing query statement reconstruction in accordance with a policy | |
| CN111309753B (en) | Optimization method, device, equipment and storage medium of structured query statement | |
| Simitsis et al. | Optimizing ETL processes in data warehouses | |
| CN110515973B (en) | Data query optimization method, device, equipment and storage medium | |
| US9928113B2 (en) | Intelligent compiler for parallel graph processing | |
| CN110134714B (en) | Distributed computing framework cache index method suitable for big data iterative computation | |
| CN110471916A (en) | Querying method, device, server and the medium of database | |
| CN110909016B (en) | Repeated association detection method, device, equipment and storage medium based on database | |
| CN110968593B (en) | Database SQL statement optimization method, device, equipment and storage medium | |
| CN110502532B (en) | Method, device, equipment and storage medium for optimizing remote database object | |
| CN110019314B (en) | Dynamic data packaging method based on data item analysis, client and server | |
| CN102915344B (en) | SQL (structured query language) statement processing method and device | |
| CN109299101B (en) | Data retrieval method, device, server and storage medium | |
| US20120158698A1 (en) | Execution plans with different driver sources in multiple threads | |
| CN112100198B (en) | Database SQL statement optimization method, device, equipment and storage medium | |
| WO2019015364A1 (en) | Method and device for executing structured query language (sql) instruction | |
| CN105812164B (en) | Rule index management implementation method and device based on TCAM multilevel flow table | |
| CN110532282B (en) | Data query method and device | |
| CN112199390B (en) | Data query method, device, equipment and storage medium in database | |
| JP2024504322A (en) | Combining JavaScript Object Notation (JASON) queries across cloud resources | |
| CN110990423B (en) | SQL statement execution method, device, equipment and storage medium | |
| CN107229704A (en) | A kind of resource description framework querying method and system based on KSP algorithms | |
| CN116401277A (en) | Data processing method, device, system, equipment and medium | |
| CN110162574B (en) | Method and device for determining data redistribution mode, server and storage medium | |
| CN114637752A (en) | Connection query statement processing method, device, equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |