CN117271576A - Query optimization method, storage medium and computer equipment - Google Patents
Query optimization method, storage medium and computer equipment Download PDFInfo
- Publication number
- CN117271576A CN117271576A CN202311352920.5A CN202311352920A CN117271576A CN 117271576 A CN117271576 A CN 117271576A CN 202311352920 A CN202311352920 A CN 202311352920A CN 117271576 A CN117271576 A CN 117271576A
- Authority
- CN
- China
- Prior art keywords
- query
- sub
- union
- merging
- statement
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 56
- 238000005457 optimization Methods 0.000 title claims abstract description 46
- 238000012545 processing Methods 0.000 claims abstract description 22
- 238000005516 engineering process Methods 0.000 abstract description 2
- 238000012360 testing method Methods 0.000 description 8
- 238000010586 diagram Methods 0.000 description 7
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000001413 cellular effect Effects 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
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/245—Query processing
- G06F16/2453—Query optimisation
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The present invention relates to database technologies, and in particular, to a query optimization method, a storage medium, and a computer device. The query optimization method comprises the following steps: acquiring a query statement and constraint conditions of the query statement; judging whether the query statement contains and operates under the condition that the constraint condition is range query; if so, judging whether the union operation in the query statement meets the sub-query merging condition; if yes, sub-query merging processing is carried out on the query statement. The invention carries out sub-query merging processing on the query sentences which have and operate in the range query and meet the sub-query merging condition, simplifies the query content, avoids the execution process of scanning repeated sub-queries by the parallel operation in the range query, and improves the overall performance of the database.
Description
Technical Field
The present invention relates to database technologies, and in particular, to a query optimization method, a storage medium, and a computer device.
Background
The UNION/UNION ALL clause is a query clause that is commonly found in relational database query statements. Unicon is used to combine results from multiple SELECT statements into one result set and prune duplicate records from the result set to achieve deduplication. Unicon ALL is used to combine results from multiple SELECT statements into one result set and will return ALL records in the result set. When the existing database optimizer performs query optimization, if an operator is encountered as an associated single-table query, the algorithm automatically decomposes the query SQL into two sub-queries associated by UNION/UNION AL for query so as to avoid full-table scanning.
However, when a UNION/UNION ALL clause appears in the query statement, the optimizer generates query nodes for sub-queries on both sides of the UNION/UNION ALL, respectively. In the range query, the UNION/UNION ALL is used as an inner node, and if the sub-query contents are basically consistent, the scheme of generating the query nodes for the same scan table item is complicated, and the execution mode of the UNION/UNION ALL operation may slow down the query mode, so that the efficiency of the database is reduced. In addition, in the range query, the effects of the UNION and the UNION ALL are basically consistent, the essence is that different data information has certain requirements, the range query is not careful about whether repeated elements exist, and the UNION has more de-duplication operations than the UNION ALL, so that the performance is reduced.
Disclosure of Invention
In view of the above, the present invention proposes a query optimization method, a storage medium and a computer device that overcome or at least partially solve the above-mentioned problems.
It is an object of the present invention to avoid scanning repeated sub-queries for concurrent operations in a range query to improve the efficiency of execution of query statements containing concurrent operations.
It is a further object of the invention to further improve the execution efficiency of query statements containing and operating.
In particular, the present invention provides a query optimization method comprising:
acquiring a query statement and constraint conditions of the query statement;
judging whether the query statement contains and operates under the condition that the constraint condition is range query;
if so, judging whether the union operation in the query statement meets the sub-query merging condition;
if yes, sub-query merging processing is carried out on the query statement.
Optionally, before the step of determining whether the combined operation in the query statement satisfies the sub-query merging condition, the query optimization method further includes:
judging whether the operator in the operation is a UNION operator or not;
if the UNION operator is a UNION operator, the UNION operator is converted into a UNION ALL operator.
Optionally, determining whether the union operation in the query statement satisfies the sub-query merge condition includes:
judging and operating whether the query table items of the sub-query are consistent;
if the query statement is consistent, determining that the union operation in the query statement meets the sub-query merging condition;
if not, determining that the union operation in the query statement does not meet the sub-query merging condition.
Optionally, determining and operating whether the lookup table entries of the sub-queries are consistent includes:
judging and operating whether the table names or view names in the from clauses of the sub-query are the same;
if the query table entries are the same, determining and operating whether the query table entries of the sub-query are consistent;
if not, determining and operating that the query table entries of the sub-query are inconsistent.
Optionally, performing sub-query merging processing on the query statement includes:
the and operation in the query statement is converted into a multi-conditional query operation for a single table.
Optionally, the step of converting the sum operation in the query statement into a multiple conditional query operation for a single table comprises:
merging sub-queries consistent with the query table items in the union operation in the query statement, and connecting query conditions by using a logic operator or.
Optionally, after the step of obtaining the query statement and the constraint condition of the query statement, the query optimization method further includes:
judging whether the UNION clause in the query statement meets the sub-query merging condition under the condition that the UNION clause is contained in the query statement;
if yes, sub-query merging processing is carried out on the query statement.
Optionally, performing sub-query merging processing on the query statement includes:
converting the UNION clause in the query statement into a query clause using a logical operator or to connect the query conditions;
and adding a group by clause or adding a distict mark in the inquiry clause to perform the de-duplication operation.
According to another aspect of the present invention, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements a query optimization method of any of the above.
According to yet another aspect of the present invention, there is also provided a computer device including a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implementing a query optimization method of any of the above when executing the machine executable program.
The query optimization method is suitable for optimizing the query statement containing the UNION/UNION ALL parallel operation in the scene of the range query, and the query statement is subjected to sub-query parallel processing after the fact that whether the parallel operation in the query statement meets the sub-query parallel operation condition is determined, so that the UNION/UNION ALL parallel operation is converted into relatively simple sub-query content, the problem that the query statement requiring the UNION/UNION ALL parallel operation is reduced by utilizing the sub-query parallel operation condition is solved, the problem that the repeated sub-query is scanned by the parallel operation in the range query is avoided, and the performance of a database is further improved.
Further, in the query optimization method, under the condition that the operator in the determination and operation is the UNION operator, the UNION operator is converted into the UNION ALL operator, so that any UNION operation in the range query is converted into the UNION ALL operation, the UNION ALL operation reduces the duplicate removal operation relative to the UNION operation, the execution efficiency is improved, and the performance of the database is further improved.
The above, as well as additional objectives, advantages, and features of the present invention will become apparent to those skilled in the art from the following detailed description of a specific embodiment of the present invention when read in conjunction with the accompanying drawings.
Drawings
Some specific embodiments of the invention will be described in detail hereinafter by way of example and not by way of limitation with reference to the accompanying drawings. The same reference numbers will be used throughout the drawings to refer to the same or like parts or portions. It will be appreciated by those skilled in the art that the drawings are not necessarily drawn to scale. In the accompanying drawings:
FIG. 1 is a schematic diagram of a query optimization method according to one embodiment of the invention;
FIG. 2 is a flow chart of a query optimization method according to another embodiment of the invention;
FIG. 3 is a flow chart of a query optimization method according to yet another embodiment of the invention;
FIG. 4 is a schematic diagram of a machine-readable storage medium according to one embodiment of the invention; and
FIG. 5 is a schematic diagram of a computer device according to one embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. While exemplary embodiments of the present invention are shown in the drawings, it should be understood that the present invention may be embodied in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the invention to those skilled in the art.
FIG. 1 is a schematic diagram of a query optimization method according to one embodiment of the invention. As shown in fig. 1, the query optimization method may generally include:
step S102, acquiring a query sentence and constraint conditions of the query sentence.
Step S104, if the constraint condition is the range query, judging whether the query statement contains and operates, if yes, executing step S106. And operations include UNION operations and UNION ALL operations. The UNION operation is used to combine results from multiple SELECT statements into one result set and prune duplicate records from the result set to achieve deduplication. The UNION ALL operation is used to combine results from multiple SELECT statements into one result set and will return ALL records in the result set.
Step S106, judging whether the union operation in the query statement meets the sub-query merging condition, if yes, executing step S108.
Step S108, sub-query merging processing is carried out on the query statement.
If the determination result in step S104 or step S106 is no, the database optimizer skips the step of executing the sub-query merging process on the query sentence.
The query optimization method of the embodiment of the invention is suitable for optimizing the query statement containing the UNION/UNION ALL merging operation in the scene of the range query, and the problem that repeated sub-queries are scanned by the merging operation in the range query is avoided by judging whether the merging operation in the query statement meets the sub-query merging condition and after determining that the merging operation meets the sub-query merging condition, so that the UNION/UNION ALL merging operation is converted into relatively simple sub-query content, the problem that the query statement needing to execute the UNION/UNION ALL merging operation is reduced by utilizing the sub-query merging condition is solved, and the overall performance of a database is further improved.
In some embodiments, before the step S106, the query optimization method of the present invention may further include the steps of: judging whether the operator in the operation is a UNION operator or not; if the UNION operator is a UNION operator, the UNION operator is converted into a UNION ALL operator. That is, any UNION operation may be converted to UNION ALL in a range query. The scheme of the embodiment of the invention makes the execution efficiency higher because the UNION ALL operation reduces the duplicate removal operation compared with the UNION operation.
According to the query optimization method, under the condition that the operator in the determination and operation is the UNION operator, the UNION operator is converted into the UNION ALL operator, any UNION operation in the range query is converted into the UNION ALL operation, the duplicate removal operation is reduced by the UNION ALL operation relative to the UNION operation, the execution efficiency is improved, and therefore the overall performance of a database is further improved.
In some embodiments, the step S106 may include the following steps: judging and operating whether the query table items of the sub-query are consistent; if the query statement is consistent, determining that the union operation in the query statement meets the sub-query merging condition; if not, determining that the union operation in the query statement does not meet the sub-query merging condition. That is, the embodiment of the invention determines whether the union operation in the query statement satisfies the sub-query merging condition by judging whether the query entries of the sub-query are consistent or not.
In one embodiment, the step of determining and operating whether the lookup table entries of the sub-query are consistent may be specifically performed as follows: judging and operating whether the table names or view names in the from clauses of the sub-query are the same; if the query table entries are the same, determining and operating whether the query table entries of the sub-query are consistent; if not, determining and operating that the query table entries of the sub-query are inconsistent. That is, in the range query, if two or more sub-query content query entries associated with the UNION/UNION ALL are consistent, that is, the query entries after the from clause are consistent, it is determined that sub-query merging processing can be performed. By using the method, the query statement which can be subjected to sub-query merging processing can be found more conveniently and accurately, and the execution efficiency is improved.
In some embodiments, the step S108 may include the following steps: the and operation in the query statement is converted into a multi-conditional query operation for a single table. That is, the UNION/UNION ALL UNION operation is converted to a single table lookup operation that contains only simple table lookup.
Specifically, the step of converting the sum operation in the query statement into the multi-conditional query operation for the single table may include the steps of: merging sub-queries consistent with the query table items in the union operation in the query statement, and connecting query conditions by using a logic operator or.
In a specific embodiment, in the context of the range query, the UNION clause is modified to be a UNION ALL clause, and then similar sub-query terms of the UNION ALL are combined, for example:
(1) in the context of a range query, unicon changes to unicon ALL:
Select * from test where id (not)in/(not)exists
(select * from t1 where a < 10 union select * from t2 where b > 8)；
the optimization is as follows:
Select * from test where id (not)in/(not)exists
(select * from t1 where a < 10 union all select * from t2 where b > 8)；
in the range query, any UNION operation can be converted into UNION ALL, and the UNION ALL reduces the duplicate removal operation relative to the UNION operation, so that the execution efficiency is improved, and the performance is improved.
(2) In the context of a range query, UNION ALL homogeneous items merge:
Select * from test where id (not)in/(not)exists
(select * from t1 where a < 10 union all select * from t1 where b > 8)；
the optimization is as follows:
Select * from test where id (not)in/(not)exists
(select * from t1 where a<10 or b>8)；
by using the method, repeated scanning of the t1 table is avoided by optimizing, UNION ALL operation is omitted after merging, sub-query is directly used, realization of other optimizing methods is facilitated, and performance is improved. The invention aims to avoid scanning repeated sub-queries by UNION ALL in range query, and can convert UNION ALL operation into relatively simple sub-query content to a certain extent, thereby improving execution efficiency.
In some embodiments, after the step S102, the query optimization method of the present invention may further include the steps of: judging whether the UNION clause in the query statement meets the sub-query merging condition under the condition that the UNION clause is contained in the query statement; if yes, sub-query merging processing is carried out on the query statement.
Specifically, the step of performing sub-query merging processing on the query statement may include the following steps: converting the UNION clause in the query statement into a query clause using a logical operator or to connect the query conditions; and adding a group by clause or adding a distict mark in the inquiry clause to perform the deduplication operation.
That is, for the uni on optimization in any query, when a uni on clause appears in the query statement and two or even more sub-query content query entries associated with uni on are consistent, that is, when the query entries after the from clause are consistent, sub-query merging processing can be performed, multiple sub-queries of uni on are merged, and the condition content is connected by or, so that the execution process of redundant sub-queries is omitted. If the UNION operation is completely eliminated, a group by clause is additionally added or a distict mark is added to ensure that the output result is accurate.
For example, select a, b, c from test where a <10 UNION Select a,b,c from test where b>8; equivalent to:
select a, b, c from test where a <10 or b >8 group by a, b, c; or (b)
Select distinct a,b,c from test where a < 10 or b > 8 group by a,b,c；
After conversion, merging processing of the same contents of sub-query from can be reduced, searching contents of repeated table entries are reduced, and the single sub-query contents after merging can be processed by other optimization methods, so that the overall performance of the database is improved.
FIG. 2 is a flow chart of a query optimization method according to another embodiment of the invention. The method comprises the following steps:
step S202, acquiring a query statement;
step S204, traversing constraint conditions of the query statement;
step S206, judging whether the constraint condition is a range query and whether the query statement contains and operates, if yes, executing step S208, and if not, executing step S216;
step S208, judging whether the operator in the operation is a UNION operator, if so, executing step S210, and if not, executing step S212;
step S210, converting the UNION operator into a UNION ALL operator, and proceeding to step S212;
step S212, judging and operating whether the table names or the view names in the from clauses of the neutron query are the same, if so, executing step S214, and if not, executing step S216;
step S214, merging sub-queries consistent with the query table items in the union operation in the query statement, and connecting the query conditions by using a logical operator or. Thus, the present flow ends.
Step S216, the step of performing sub-query merging processing on the query sentence is skipped. Thus, the present flow ends.
The query optimization method of the embodiment of the invention is suitable for optimizing the query statement containing the UNION/UNION ALL merging operation in the scene of the range query, and the problem that repeated sub-queries are scanned by the merging operation in the range query is avoided by judging whether the merging operation in the query statement meets the sub-query merging condition and after determining that the merging operation meets the sub-query merging condition, so that the UNION/UNION ALL merging operation is converted into relatively simple sub-query content, the problem that the query statement needing to execute the UNION/UNION ALL merging operation is reduced by utilizing the sub-query merging condition is solved, and the overall performance of a database is further improved.
Further, in the query optimization method provided by the embodiment of the invention, under the condition that the operator in the determination and operation is the UNION operator, the UNION operator is converted into the UNION ALL operator, so that any UNION operation in the range query is converted into the UNION ALL operation, the duplicate removal operation of the UNION ALL operation is reduced relative to the UNION operation, the execution efficiency is improved, and the performance of the database is further improved.
FIG. 3 is a flow chart of a query optimization method according to yet another embodiment of the invention. The method comprises the following steps:
step S302, acquiring a query statement;
step S304, positioning all UNION clauses in the query statement;
step S306, judging whether the table names or view names in the from clause in the UNION clause are the same, if yes, executing step S308, and if not, executing step S316;
step S308, converting the UNION clause in the query statement into a query clause using a logical operator or to connect the query condition;
step S310, judging whether sub-query items of UNION can be combined, if so, executing step S312, and if not, executing step S314;
in step S312, a group by clause is added or a distict flag is added in the query clause to perform the deduplication operation. Thus, the present flow ends.
Step S314, skip the re-operation. I.e., the operation of adding a group by clause or adding a distict flag in the query clause is skipped. Thus, the present flow ends.
Step S316, the step of performing sub-query merging processing on the query sentence is skipped. Thus, the present flow ends.
The query optimization method of the embodiment of the invention is suitable for optimizing the query statement containing the UNION operation in any query scene, and by judging whether the sub-query items of the UNION can be combined, after determining that the sub-query items of the UNION can be combined, the sub-query merging processing is carried out on the query statement, and after determining that the sub-query items of the UNION can be combined, the group by clause is additionally added or the distict mark is additionally added, thereby ensuring the accuracy of the output result and further improving the overall performance of the database.
The present embodiment also provides a machine-readable storage medium and a computer device. Fig. 4 is a schematic diagram of a machine-readable storage medium 10 according to one embodiment of the invention, and fig. 5 is a schematic diagram of a computer device 20 according to one embodiment of the invention.
The machine-readable storage medium 10 has stored thereon a machine-executable program 11, which when executed by a processor, implements the query optimization method of any of the embodiments described above.
The computer device 20 may include a memory 220, a processor 210, and a machine executable program 11 stored on the memory 220 and running on the processor 210, and the processor 210 implements the query optimization method of any of the embodiments described above when executing the machine executable program 11.
It should be noted that the logic and/or steps represented in the flow diagrams or otherwise described herein, e.g., a ordered listing of executable instructions for implementing logical functions, can be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
For the purposes of this description of embodiments, a machine-readable storage medium 10 can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable medium would include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the machine-readable storage medium 10 may even be paper or other suitable medium upon which the program is printed, as the program may be electronically captured, via, for instance, optical scanning of the paper or other medium, then compiled, interpreted, or otherwise processed in a suitable manner, if necessary, and then stored in a computer memory.
It is to be understood that portions of the present invention may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.
The computer device 20 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 20 may be a cloud computing node. The computer device 20 may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer device 20 may be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computing system storage media including memory storage devices.
Computer device 20 may include a processor 210 adapted to execute stored instructions, a memory 220 providing temporary storage for the operation of the instructions during operation. Processor 210 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 220 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 210 may be connected through a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) adapted to connect computer device 20 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, which may include a touch pad or touch screen, among others. The I/O device may be a built-in component of the computer device 20 or may be a device externally connected to the computing device.
The processor 210 may also be linked by a system interconnect to a display interface suitable for connecting the computer device 20 to a display device. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, television, projector, or the like, that is externally connected to the computer device 20. Further, a network interface controller (network interface controller, NIC) may be adapted to connect the computer device 20 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an internet small computer system interface, etc.) to transfer data. The network may be a cellular network, a radio network, a Wide Area Network (WAN), a Local Area Network (LAN), or the internet, among others. The remote device may be connected to the computing device through a network.
The flowcharts provided by this embodiment are not intended to indicate that the operations of the method are to be performed in any particular order, or that all of the operations of the method are included in all of each case. Furthermore, the method may include additional operations. Additional variations may be made to the above-described methods within the scope of the technical ideas provided by the methods of the present embodiments.
By now it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the invention have been shown and described herein in detail, many other variations or modifications of the invention consistent with the principles of the invention may be directly ascertained or inferred from the present disclosure without departing from the spirit and scope of the invention. Accordingly, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.
Claims (10)
1. A method of query optimization, comprising:
acquiring a query statement and a constraint condition of the query statement;
judging whether the query statement contains and operates under the condition that the constraint condition is range query;
if so, judging whether the union operation in the query statement meets the sub-query merging condition;
if yes, sub-query merging processing is carried out on the query statement.
2. The query optimization method of claim 1, wherein,
before the step of determining whether the sum operation in the query statement satisfies a sub-query merging condition, the query optimization method further includes:
judging whether the operator in the UNION operation is a UNION operator or not;
if the UNION operator is the UNION operator, the UNION operator is converted into a UNION ALL operator.
3. The query optimization method of claim 1, wherein,
judging whether the union operation in the query statement meets the sub-query merging condition comprises the following steps:
judging whether the query table entries of the union operation sub-query are consistent;
if yes, determining that the sum operation in the query statement meets the sub-query merging condition;
if the sub-query merging conditions are not consistent, determining that the merging operation in the query statement does not meet the sub-query merging conditions.
4. The query optimization method of claim 3, wherein,
judging whether the query table items of the union operation sub-query are consistent or not comprises the following steps:
judging whether the table names or view names in the from clauses of the sub-query are the same or not;
if the query table entries are the same, determining whether the query table entries of the sub-query in the combined operation are consistent;
if the query terms are not identical, determining that the query terms of the union operation sub-queries are inconsistent.
5. The query optimization method of claim 1, wherein,
the sub-query merging processing of the query statement comprises the following steps:
the and operation in the query statement is converted into a multi-conditional query operation for a single table.
6. The query optimization method of claim 5, wherein,
the step of converting said and operation in said query statement into a multi-conditional query operation for a single table comprises:
merging the sub-queries with consistent query table items in the merging operation in the query statement, and connecting query conditions by using a logical operator or.
7. The query optimization method of claim 1, wherein,
after the step of obtaining the query statement and the constraint condition of the query statement, the query optimization method further includes:
judging whether the UNION clause in the query statement meets a sub-query merging condition under the condition that the query statement contains the UNION clause;
if yes, sub-query merging processing is carried out on the query statement.
8. The query optimization method of claim 7, wherein,
the sub-query merging processing of the query statement comprises the following steps:
converting the UNION clause in the query statement into a query clause using a logical operator or to connect query conditions;
and adding a group by clause or adding a distict mark in the inquiry clause to perform the duplication removing operation.
9. A machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements the query optimization method of any one of claims 1 to 8.
10. A computer device comprising a memory, a processor and a machine executable program stored on the memory and running on the processor, and the processor implementing a query optimization method according to any one of claims 1 to 8 when executing the machine executable program.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311352920.5A CN117271576A (en) | 2023-10-19 | 2023-10-19 | Query optimization method, storage medium and computer equipment |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311352920.5A CN117271576A (en) | 2023-10-19 | 2023-10-19 | Query optimization method, storage medium and computer equipment |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117271576A true CN117271576A (en) | 2023-12-22 |
Family
ID=89221467
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311352920.5A Pending CN117271576A (en) | 2023-10-19 | 2023-10-19 | Query optimization method, storage medium and computer equipment |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117271576A (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130006965A1 (en) * | 2011-06-30 | 2013-01-03 | International Business Machines Corporation | Database Query Optimization |
| CN110515973A (en) * | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
| CN115878654A (en) * | 2022-10-27 | 2023-03-31 | 北京奥星贝斯科技有限公司 | Data query method, device, equipment and storage medium |
| WO2023124729A1 (en) * | 2021-12-31 | 2023-07-06 | 北京大学 | Data query method and apparatus, and device and storage medium |
-
2023
- 2023-10-19 CN CN202311352920.5A patent/CN117271576A/en active Pending
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20130006965A1 (en) * | 2011-06-30 | 2013-01-03 | International Business Machines Corporation | Database Query Optimization |
| CN110515973A (en) * | 2019-08-30 | 2019-11-29 | 上海达梦数据库有限公司 | A kind of optimization method of data query, device, equipment and storage medium |
| CN112559554A (en) * | 2020-12-24 | 2021-03-26 | 北京百家科技集团有限公司 | Query statement optimization method and device |
| WO2023124729A1 (en) * | 2021-12-31 | 2023-07-06 | 北京大学 | Data query method and apparatus, and device and storage medium |
| CN115878654A (en) * | 2022-10-27 | 2023-03-31 | 北京奥星贝斯科技有限公司 | Data query method, device, equipment and storage medium |
Non-Patent Citations (2)
| Title |
|---|
| XIAO_A_TONG: "T04 集合运算", pages 1 - 2, Retrieved from the Internet <URL:https://blog.csdn.net/xiao_a_tong/article/details/123720329> * |
| 未知: "Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营", pages 2 - 3, Retrieved from the Internet <URL:https://zhuanlan.zhihu.com/p/554961143> * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108519967B (en) | Chart visualization method and device, terminal and storage medium | |
| US7246114B2 (en) | System and method for presenting a query expressed in terms of an object model | |
| EP2605158A1 (en) | Mixed join of row and column database tables in native orientation | |
| US20190138522A1 (en) | Query translation for searching complex structures of objects | |
| US20140046928A1 (en) | Query plans with parameter markers in place of object identifiers | |
| CN112579610A (en) | Multi-data source structure analysis method, system, terminal device and storage medium | |
| CN109241100B (en) | Query method, device, equipment and storage medium | |
| US20080307013A1 (en) | Updating an inverted index | |
| CN109815240A (en) | For managing method, apparatus, equipment and the storage medium of index | |
| CN116257552A (en) | Database query statement optimization method, storage medium and device | |
| CN117076491A (en) | Data processing method, storage medium and equipment | |
| CN116431672A (en) | Predicate logic optimization method, storage medium and equipment of database operation statement | |
| CN116467310A (en) | Lock-free marking method for invalid index, storage medium and computer equipment | |
| CN117271576A (en) | Query optimization method, storage medium and computer equipment | |
| CN116719843A (en) | Query method, storage medium and device for database system | |
| CN116662355A (en) | Marking method for invalid index, storage medium and computer device | |
| CN115757479A (en) | Database query optimization method, machine-readable storage medium and computer device | |
| CN115391346A (en) | Database aggregation index generation method, storage medium and computer equipment | |
| CN115934760A (en) | Database query statement optimization method, storage medium and computer device | |
| CN115391424A (en) | Database query processing method, storage medium and computer equipment | |
| JP5488792B2 (en) | Database operation device, database operation method, and program | |
| CN113886425A (en) | Data processing method, device, equipment and storage medium | |
| CN117149821B (en) | Query optimization method, storage medium and computer equipment | |
| CN117235118B (en) | Query optimization method, storage medium and computer equipment | |
| CN110716949A (en) | Method, device, medium and electronic equipment for automatically optimizing structured query statement |
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 |