CN115328939A - Method for processing query statement by database, storage medium and computer equipment - Google Patents

Abstract

The invention provides a method for processing query sentences by a database, a storage medium and computer equipment. Wherein the method comprises the following steps: acquiring a query statement of a database; judging whether the query statement contains query operation for counting non-repeated results; if so, judging whether the data column aimed at by the query operation meets preset optimization conditions; if so, performing equivalent logic transformation on the query operation to obtain an equivalent operation; the equivalent operations are optimized by an optimizer of the database to generate an execution plan. According to the scheme, the logic optimization rule is added, and the query operation is converted into the equivalent operation under the condition that the optimization condition is met, so that the query performance is improved. Through actual verification, the processing performance of the database is greatly improved under the condition of meeting the optimization conditions.

Description

Database processing query statement method, storage medium and computer equipment

technical field

The invention relates to database technology, in particular to a database processing query statement method, storage medium and computer equipment.

Background technique

For a given query statement, there are usually many possible strategies, and query optimization is the process of finding the most effective query execution plan. For the same query statement, the cost of different execution plans may be very different. The task of the optimizer of the database system is to generate optimal query execution.

In the database system in the prior art, when the optimizer processes the query operation (count (distinct) operation) containing statistical non-duplicate results, it generally scans the data table sequentially, and then uses the aggregation operation to deduplicate the scanned results .

However, the above-mentioned count (distinct) operation, due to the existence of the distinct keyword, the optimizer cannot operate in parallel, so the performance improvement brought by the multi-core CPU cannot be effectively utilized. In addition, the optimizer uses Aggregate to achieve deduplication and aggregation operations, and cannot choose the appropriate HashAggregate or GroupAggregate based on the cost.

Based on the above two reasons, the processing performance of the count (distinct) operation of the existing database is low, which affects the improvement of the database query performance.

Contents of the invention

One object of the present invention is a database processing query statement method, storage medium and computer equipment that at least solve any of the above-mentioned technical problems.

A further object of the present invention is to improve the processing performance for the database count(distinct) operation.

In particular, the present invention provides a method for database processing query statements, which includes:

Obtain the query statement of the database;

Determine whether the query statement contains a query operation that counts non-repeated results;

If yes, determine whether the data column targeted by the query operation satisfies the preset optimization condition;

If so, perform an equivalent logical transformation on the query operation to obtain an equivalent operation;

Equivalent operations are optimized by the database's optimizer to generate an execution plan.

Optionally, the steps of obtaining the query statement of the database include:

Accept the operation statement on the database;

Determine whether the operation statement contains a selection query;

If so, extract the statement including the selection query as the query statement.

Optionally, the step of judging whether the query statement includes a query operation for counting non-repeated results includes:

Perform semantic analysis on the query statement to obtain the analysis result. The analysis result includes any one or more of the following: the view of the query statement, the function of the query statement, the parent query of the query statement or the subquery nested in the query statement;

Determine whether the analysis results contain both counting functions and keywords of non-repeated query operations;

If yes, it is determined that the query statement includes a query operation for counting non-repeated results.

The keyword of the non-repetitive query operation is distinct, and the grouping aggregation subquery function is groupby.

Optionally, the step of performing an equivalent logical transformation on the query operation includes:

Create a new subquery or view to replace the data table of the query operation;

Set the aggregate function parameter of the subquery or view to No;

Project data columns to subqueries or views as projection columns;

Convert distinct query operations into group-by-aggregate subqueries;

Update the column position information in the group-by-aggregate subquery.

Optionally, the step of judging whether the data column targeted by the query operation satisfies a preset optimization condition includes:

Query whether the data repetition degree of the data column is within the preset threshold range;

If yes, it is determined that the data column satisfies the optimization condition.

Optionally, the threshold range is set from 60% to 100%.

Optionally, when the query statement does not include a query operation for counting non-repeated results or the data column targeted by the query operation does not meet the optimization condition, the optimizer of the database directly optimizes the query statement.

According to another aspect of the present invention, there is also provided a machine-readable storage medium, on which a machine-executable program is stored. When the machine-executable program is executed by a processor, any one of the above methods for processing query statements in a database is implemented.

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 when the processor executes the machine-executable program, any of the above-mentioned A database method for processing query statements.

In the method for database processing query statement of the present invention, for the query statement containing the query operation of statistical non-repetitive results, it is judged whether it can meet the preset optimization condition, and in the case of satisfying the optimization condition; perform equivalent logic on the query operation The equivalent operation is obtained by transformation; the equivalent operation is further optimized by the optimizer of the database. By adding logic optimization rules, query operations are transformed into equivalent operations when the optimization conditions are met, thereby improving query performance.

Further, the database processing query statement method of the present invention converts count (distinct) into a grouping aggregation operation (group by deduplication), thereby avoiding the problem that the optimizer cannot operate in parallel due to the distinct keyword, and realizes the parallel operation through parallel operation. , HashAggregate/GroupAggregate and indexes to improve query performance.

Furthermore, the method for processing query statements in the database of the present invention has been verified in practice, and the processing performance of the database has been greatly improved in the scenario where the optimization conditions are met.

Those skilled in the art will be more aware of the above and other objects, advantages and features of the present invention according to the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings.

Description of drawings

Hereinafter, some specific embodiments of the present invention will be described in detail by way of illustration and not limitation with reference to the accompanying drawings. The same reference numerals in the drawings designate the same or similar parts or parts. Those skilled in the art will appreciate that the drawings are not necessarily drawn to scale. In the attached picture:

1 is a schematic diagram of a method for processing a query statement in a database according to an embodiment of the present invention;

Fig. 2 is a flow chart of judging optimization conditions in the method for database processing query statement according to an embodiment of the present invention;

Fig. 3 is a schematic diagram of an algebraic form of an equivalent logical transformation in a method for processing a query statement in a database according to an embodiment of the present invention;

Fig. 4 is the flow chart of equivalent logical conversion in the method for database processing query statement according to one embodiment of the present invention;

Figure 5 is a schematic diagram of a machine-readable storage medium according to one embodiment of the present invention; and

Figure 6 is a schematic diagram of a computer device according to one embodiment of the present invention.

Detailed ways

FIG. 1 is a schematic diagram of a method for processing a query statement by a database according to an embodiment of the present invention. The method for processing a query statement by the database may generally include:

In step S102, the query statement of the database is obtained. In the case of using SQL (Structured Query Language, Structured Query Language), the query statement can be determined by checking the keyword of select in the SQL statement.

For example, step S102 may specifically include: accepting an operation statement to the database; judging whether the operation statement contains a select query; if so, extracting the statement containing the select query as a query statement. For example, the SQL operation statement can determine the select query by checking the select query.

Step S104, judging whether the query sentence includes query operations for counting non-repeated results. That is, it is judged whether the query statement contains count(distinct).

Step S104 may first perform semantic analysis on the query statement to obtain an analysis result. The analysis result includes any one or more of the following: the view of the query statement, the function of the query statement, the parent query of the query statement, or the subquery nested in the query statement. After the analysis result is obtained, it is determined whether the analysis result contains both the counting function and the keyword of the non-repeated query operation; if so, it is determined that the query statement contains the query operation of counting the non-repeated results. That is to say, the counting function can be determined by checking whether the above view, function, parent query or nested subquery has an aggregate function and the aggregate function is a count function; at the same time, it can be determined by checking whether there is a distinct aggregation operation (aggdistinct operation) to determine the uniqueness Inquire.

Step S106, if yes, determine whether the data column targeted by the query operation satisfies a preset optimization condition. The optimization condition can be judged by the data repetition degree of the data column. That is to say, the judgment in step S106 may specifically be: whether the data repetition degree of the query data column is within a preset threshold range; if yes, determine that the data column satisfies the optimization condition. The threshold range is set from 60% to 100%, that is, in the case of 60% to 100% repetition of data, it is considered that the implementation of the newly added optimization rules in the method of this embodiment can wait for a better optimization result. This point is also proved by the actual verification of the method of this embodiment.

Step S108, if yes, perform an equivalent logical transformation on the query operation to obtain an equivalent operation. The equivalent operation is used to eliminate the distinct in count(distinct), so as to solve the problem that cannot be operated in parallel.

In step S110, the optimizer of the database optimizes equivalent operations to generate an execution plan.

Step S112, if any of the judgment results of the above-mentioned step S106 and step S108 is negative, that is, if the query statement does not contain a query operation that counts non-repeated results or the data column targeted by the query operation does not meet the optimization conditions, the database The optimizer directly optimizes the query statement.

The method for processing a query statement by a database in the above embodiment can also be understood as performing an equivalent logical transformation on the query operation when four conditions are met. For SQL statements, the 4 conditions include:

(1) The SQL statement is a select query;

(2) In the view, function, parent query or nested subquery of the query statement, there is an aggregate function and it is a count function;

(3) There is an aggdistinct operation;

(4) The data repetition degree of the query aggregation column is within a preset threshold range, which can be set by the database user, for example, 60% to 100%.

FIG. 2 is a flow chart of judging optimization conditions in a method for processing query statements in a database according to an embodiment of the present invention. The flowchart can include:

Step S202, judging whether the SQL statement is a select query;

Step S204, judging whether there is an aggregate function and it is a count function in the view, function, parent query or nested subquery of the query statement;

Step S206, judging whether there is an aggdistinct operation;

Step S208, judging whether the data repetition degree of the query aggregation column is within a preset threshold range;

Step S210, performing count (distinct) equivalent logic conversion;

In step S212, the optimizer continues the optimization work.

If the above four conditions are met, it is considered to meet the count (distinct) equivalent logical transformation rule, the optimizer eliminates the distinct keyword, and uses subqueries or views to perform deduplication operations through group by. In order to ensure that the equivalent transformation brings performance benefits, it can be further determined that the repetition degree of the query column is in the range of 60% to 100%, and an equivalent logical transformation is performed. That is to say, increase the threshold range setting of column data duplication, and only when the duplication of the query column reaches the set threshold range, the equivalent logical transformation will be performed.

For example, for the query: select count(distinct name) from t1, the equivalent SQL statement after equivalent logical transformation is: select count(name) from(select name from t1 group by name)ν. count(distinct) is converted into a group aggregation operation (group by deduplication), which avoids the problem that the optimizer cannot operate in parallel due to the distinct keyword.

Fig. 3 is a schematic diagram of an algebraic form of an equivalent logical transformation in a method for processing a query statement in a database according to an embodiment of the present invention. Still taking select count(distinct name) from t1 as an example, in the algebraic form 30 before conversion, table t1 first performs selection ρ _(name) , and then performs aggregation G _{count_distinct(name)} . In the converted algebraic form 31, the algebraic form of select count(name)from(select name from t1 group by name)ν is: table t1 first selects ρ _ν(name) , then performs projection П _name , Group by _name G, and finally perform aggregation G _count(name) .

Fig. 4 is a flow chart of equivalent logic transformation in the method for processing query statements in a database according to an embodiment of the present invention. Equivalent logical transformation includes the following steps:

Step S402, create a new subquery or view to replace the data table of the query operation; that is, replace t1 with the new subquery or view.

Step S404, setting the aggregate function parameter of the subquery or view to No; that is, setting hasAggs=false as the aggregate function parameter of the subquery or view.

Step S406, project the data column to the subquery or view as the projection column; that is, set the subquery or view projection column, and project the required data of t1 to the subquery or view.

In step S408, the non-repeated query operation is converted into a grouped aggregation subquery; that is, t1 is replaced by a newly created subquery or view, and aggdistinct is converted into group by.

Step S410, updating the column position information in the grouping aggregation subquery, including grouping the grouping aggregation subquery groupby and the column position information in the parent query, etc., to eliminate the column position misalignment in the above conversion process.

In step S412, the optimizer continues the optimization work.

The inventor has conducted a large number of tests on the method of this embodiment, and obtained excellent test results. For example, the query statement is select count(distinct name) from t1. For the case where the data volume of t1 table is 15 million and the data repetition degree of name column is 96%; the execution time before applying this scheme is: 165.243s; apply this scheme After the equivalent logic transformation, the time is shortened to: 7.11s. This shows that the query performance of the database is greatly improved.

This embodiment also provides a machine-readable storage medium and computer equipment. FIG. 5 is a schematic diagram of a machine-readable storage medium 50 according to one embodiment of the present invention, and FIG. 6 is a schematic diagram of a computer device 60 according to one embodiment of the present invention.

The machine-readable storage medium 50 stores a machine-executable program 51 thereon. When the machine-executable program 51 is executed by a processor, the method for processing a query statement in a database in any of the above-mentioned embodiments is implemented.

The computer device 60 may include a memory 620, a processor 610, and a machine-executable program 51 stored on the memory 620 and run on the processor 610, and when the processor 610 executes the machine-executable program 51, the database of any of the above-mentioned embodiments may be realized Method for processing query statements.

It should be noted that the logic and/or steps shown in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be embodied in any machine In a readable storage medium for use by an instruction execution system, device or device (such as a computer-based system, a system including a processor, or other systems that can fetch instructions from an instruction execution system, device or device and execute instructions), or in combination with These instructions are used to execute systems, devices, or equipment.

As far as the description of this embodiment is concerned, the machine-readable storage medium 50 can be any program that can contain, store, communicate, disseminate or transmit programs for instruction execution systems, devices, or devices or for use in combination with these instruction execution systems, devices, or devices. installation. More specific examples (non-exhaustive list) of computer-readable media include the following: electrical connection with one or more wires (electronic device), portable computer disk case (magnetic device), random access memory (RAM), Read Only Memory (ROM), Erasable and Editable Read Only Memory (EPROM or Flash Memory), Fiber Optic Devices, and Portable Compact Disc Read Only Memory (CDROM). In addition, the computer readable medium 50 may even be paper or other suitable medium on which the program may be printed, as it may be possible, for example, by optically scanning the paper or other medium, followed by editing, interpretation, or other processing as necessary. Processed in a suitable manner to obtain the program electronically and store it in a computer memory.

It should be understood that various parts of the present invention can be realized by hardware, software, firmware or their combination. In the embodiments described above, various steps or methods may be implemented by software or firmware stored in memory and executed by a suitable instruction execution system.

The computer device 60 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer or a smartphone. In some examples, computer device 60 may be a cloud computing node. Computer device 60 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 60 can be practiced in a distributed cloud computing environment 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 storage devices.

The computer device 60 may include a processor 610 adapted to execute stored instructions, a memory 620 providing temporary storage for operations of the instructions during operation. Processor 610 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 620 may include random access memory (RAM), read only memory, flash memory, or any other suitable storage system.

Processor 610 may be connected by a system interconnect (e.g., PCI, PCI-Express, etc.) to an I/O interface (input/output device) suitable for connecting computer device 60 to one or more I/O devices (input/output device). interface). I/O devices may include, for example, a keyboard and a pointing device, where the pointing device may include a touch pad or a touch screen, among others. The I/O devices may be built-in components of the computing device 60, or may be devices externally connected to the computing device.

Processor 610 may also be linked through a system interconnect to a display interface suitable for connecting computer device 60 to a display device. The display device may include a display screen as a built-in component of the computer device 60 . The display device may also include a computer monitor, a television, or a projector, etc. externally connected to the computer device 60 . Additionally, a network interface controller (NIC) may be adapted to connect computer device 60 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as 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 can be connected to the computing device through a network.

The flowcharts provided in the present embodiments are not intended to indicate that the operations of the methods will be performed in any particular order, or that all operations of the methods are included in every instance. Additionally, the methods may include additional operations. Within the scope of the technical idea provided by the method of this embodiment, additional changes can be made to the above method.

So far, those skilled in the art should appreciate that, although a number of exemplary embodiments of the present invention have been shown and described in detail herein, without departing from the spirit and scope of the present invention, the disclosed embodiments of the present invention can still be used. Many other variations or modifications consistent with the principles of the invention are directly identified or derived from the content. 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 for database processing query statements, comprising: Obtain the query statement of the database; Judging whether the query statement includes a query operation for counting non-repeated results; If so, determine whether the data column targeted by the query operation satisfies a preset optimization condition; If so, performing an equivalent logical transformation on the query operation to obtain an equivalent operation; The equivalent operation is optimized by an optimizer of the database to generate an execution plan. 2. The method for database processing query statement according to claim 1, wherein the step of obtaining the query statement of the database comprises: accepting an operation statement on the database; Determine whether the operation statement contains a selection query; If yes, then extract the statement including the selection query as the query statement. 3. the method for database processing query statement according to claim 1, wherein, the step of described whether described query statement comprises the query operation of statistics non-repetitive result comprises: Perform semantic analysis on the query statement to obtain an analysis result, and the analysis result includes any one or more of the following: the view of the query statement, the function of the query statement, the parent query of the query statement, or the The nested subqueries in the above query statement; Judging whether the analysis result contains the counting function and the keyword of the non-repeated query operation at the same time; If yes, it is determined that the query statement includes a query operation for counting non-repeated results. 4. The method for processing a query statement in a database according to claim 3, wherein the step of carrying out an equivalent logical transformation to the query operation comprises: Create a new subquery or view to replace the data table of the query operation; Set the aggregate function parameter of the subquery or the view to No; Projecting the data column to the subquery or the view as a projection column; converting the distinct query operation into a grouping and aggregation subquery; Updating the column position information of the grouping aggregation subquery. 5. the method for database processing query statement according to claim 4, wherein, The keyword of the non-repetitive query operation is distinct, and the grouping and aggregation sub-query function is group by. 6. The method for processing a query statement in a database according to claim 3, wherein the step of judging whether the data column targeted by the query operation satisfies a preset optimization condition comprises: Query whether the data repetition degree of the data column is within a preset threshold range; If yes, it is determined that the data column satisfies the optimization condition. 7. the method for database processing query statement according to claim 6, wherein, The threshold range is set from 60% to 100%. 8. the method for database processing query statement according to claim 1, wherein, In the case that the query statement does not include a query operation for counting non-repeated results or the data column targeted by the query operation does not satisfy the optimization condition, the optimizer of the database directly optimizes the query statement. 9. A machine-readable storage medium, on which a machine-executable program is stored, and when the machine-executable program is executed by a processor, the method for processing query statements in a database according to any one of claims 1 to 8 is implemented. 10. A computer device, comprising a memory, a processor, and a machine-executable program stored on the memory and running on the processor, and when the processor executes the machine-executable program, it realizes the following: The database processing query statement method described in any one of 1 to 8.

Images