CN112286961B - SQL optimization query method and device - Google Patents

Abstract

The invention discloses an SQL optimization query method and device, relates to the technical field of big data, and can avoid the starting of a fusing mechanism and improve the query efficiency. The method comprises the following steps: when the SQL query statement is analyzed to be a UNION set type query statement, counting the number of storage segments related to target data, wherein the UNION set type query statement comprises a plurality of sub query statements; when the number of related storage segments is larger than a threshold value, splitting the query cycle corresponding to the sub-query statement into a plurality of granularity cycles, summarizing target data dimensions corresponding to the sub-query statements, and adding the summarized target data dimensions as new conditions into the filtering conditions of the sub-query statement; and pushing the sub-query statements containing the filter conditions down to a computing engine, reading the target data corresponding to each granularity period and the dimension value in the target data, and returning a query result to the user after the target data is computed by the processing end. The device is applied with the method provided by the scheme.

Description

SQL optimization query method and device

technical field

The present invention relates to the technical field of big data, in particular to an SQL optimization query method and device.

Background technique

As the amount of business data continues to increase, the traffic model ingests a huge amount of data into the datasource of the Druid engine. Every time SQL performs a year-on-year query, the inner union all clause needs to check all the data within the specified period of SQL. In order to protect the effective query of other tasks by the druid engine, the druid engine will judge the total number of segments in the current SQL cycle, and start the fuse mechanism when the total number of segments exceeds the threshold. Even if the threshold is not exceeded, the full query method will also increase the query time , reducing the query efficiency.

Contents of the invention

The purpose of the present invention is to provide a method and device for SQL optimization query, which can avoid the activation of the fuse mechanism and improve the query efficiency at the same time.

In order to achieve the above object, the first aspect of the present invention provides a method for SQL optimization query, including:

When parsing the SQL query statement as the query statement of the UNION set type, counting the number of storage segments involved in the target data, the query statement of the UNION set type includes a plurality of sub-query statements;

When the number of the storage segments involved is greater than the threshold, the query period corresponding to the subquery statement is split into multiple granularity periods, and the target data dimensions corresponding to the multiple subquery statements are summarized and added as new conditions to all In the filter condition of the subquery statement;

Push down the sub-query statement containing the filter condition to the calculation engine and concurrently read the target data corresponding to each granularity period and the dimension value therein, and return the query result to the user after calculation by the processing end.

Preferably, when the number of stored segments is greater than a threshold, the method for splitting the query cycle corresponding to the sub-query statement into multiple granularity cycles includes:

The query period corresponding to each sub-query statement is extracted, and each query period is evenly split into multiple granularity periods according to the preset granularity.

Preferably, the method of summarizing the target data dimensions corresponding to multiple sub-queries and adding them as new conditions to the filter conditions of the sub-queries includes:

Identify the corresponding target data dimension of each sub-query statement, when the target data dimensions of each sub-query statement are consistent, then get the target data dimension of any one of the sub-query statements as a new condition and add each of the sub-query statements in the filter condition;

When the target data dimensions of each of the sub-query statements are inconsistent, the target data dimensions of each of the sub-query statements are aggregated and combined as new conditions and added to the filter conditions of each of the sub-query statements.

Preferably, the query statement of the UNION set type includes at least a first period subquery statement and a second period subquery statement, and the filter conditions include query metrics, dimension sorting and target data query in addition to the newly added conditions quantity.

Preferably, the method for the calculation engine to concurrently read the target data corresponding to each granularity period and the dimension values therein includes:

According to each sub-query statement and the filtering conditions therein, the calculation engine respectively queries the target data meeting the target data query quantity condition from the corresponding storage segment of the corresponding data source and extracts the dimension value therein.

Further, the method of returning query results to the user after being calculated by the processing terminal includes:

The processing end returns query results to the user after calculating according to the dimension values returned by each sub-query statement according to preset rules.

Preferably, in the step, the sub-query statement containing the filter condition is pushed down to the calculation engine and concurrently reads the target data corresponding to each granularity period and the dimension value therein, and returns the query result to the user after the processing terminal calculates. include:

Count the hot data sources and the hot data dimensions in the historical SQL query statements, and pre-extract the dimension values corresponding to the hot data dimensions for quick invocation of subsequent SQL query statements.

Preferably, in the step, the sub-query statement containing the filter condition is pushed down to the calculation engine and concurrently reads the target data corresponding to each granularity period and the dimension value therein, and returns the query result to the user after the processing terminal calculates. include:

Each subquery statement, corresponding target data and dimension values therein are cached in the database, so that the same subquery statement can be quickly called from the database when the same subquery statement appears subsequently.

Compared with the prior art, the method for SQL optimization query provided by the present invention has the following beneficial effects:

In the SQL optimization query method provided by the present invention, before the calculation engine queries the target data according to the SQL query statement, it is necessary to perform pre-statistics on the number of storage segments involved in the target data. When the number of storage segments involved is greater than the threshold, if the full query is directly performed It may trigger the fuse mechanism of the computing engine, so the solution adopted by the present invention for this situation is to split the query cycle corresponding to the sub-query statement into multiple granularity cycles, and at the same time, in order to avoid different target data dimensions corresponding to different sub-query statements The problem that the query results cannot be output. Before querying, it is necessary to summarize the target data dimensions corresponding to multiple subquery statements as new conditions and add them to the filter conditions of each subquery statement. After the above operations are completed, the subquery containing the filter conditions will eventually be The statement is pushed down to the calculation engine to concurrently read the target data corresponding to each granularity cycle and the dimension values in it, and return the query result to the user after calculation by the processing end.

It can be seen that the present invention not only solves the problem that the fuse mechanism of the computing engine is easily triggered during the full query process, but also ensures that the target data dimensions corresponding to each sub-query statement are unified, and the query results can be output.

The second aspect of the present invention provides a device for SQL optimized query, which is applied to the SQL optimized query method described in the above technical solution, and the device includes:

A statement parsing unit, configured to count the number of storage segments involved in the target data when parsing the SQL query statement as a query statement of the UNION set type, the query statement of the UNION set type including a plurality of sub-query statements;

An identification processing unit, configured to split the query cycle corresponding to the sub-query statement into multiple granularity cycles when the number of the storage segments involved is greater than a threshold, and after summarizing the target data dimensions corresponding to the multiple sub-query statements Adding it as a new condition into the filter condition of the subquery statement;

The query unit is configured to push down the sub-query statement containing the filter condition to the calculation engine and concurrently read the target data corresponding to each granularity period and the dimension value therein, and return the query result to the user after calculation by the processing terminal.

Compared with the prior art, the beneficial effect of the SQL optimized query device provided by the present invention is the same as that of the SQL optimized query method provided by the above technical solution, and will not be repeated here.

A third aspect of the present invention provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is run by a processor, the steps of the above-mentioned SQL optimization query method are executed.

Compared with the prior art, the beneficial effect of the computer-readable storage medium provided by the present invention is the same as the beneficial effect of the SQL optimization query method provided by the above technical solution, and will not be repeated here.

Description of drawings

The accompanying drawings described here are used to provide a further understanding of the present invention, and constitute a part of the present invention. The schematic embodiments of the present invention and their descriptions are used to explain the present invention, and do not constitute improper limitations to the present invention. In the attached picture:

Fig. 1 is a partial flow diagram of the SQL optimization query method in an embodiment of the present invention;

Fig. 2 is a schematic diagram of the whole process of the SQL optimization query method in the embodiment of the present invention;

FIG. 3 is an example diagram of an SQL optimization query method in an embodiment of the present invention.

Detailed ways

In order to make the above objects, features and advantages of the present invention more obvious and understandable, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Apparently, the described embodiments are only some of the embodiments of the present invention, but not all of them. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without creative efforts fall within the protection scope of the present invention.

Embodiment one

Referring to Fig. 1, the present embodiment provides a SQL optimization query method, including:

When parsing the SQL query statement as a query statement of the UNION collection type, count the number of storage segments involved in the target data. The query statement of the UNION collection type includes multiple sub-query statements; when the number of storage segments involved is greater than the threshold, the sub-query statement The corresponding query cycle is split into multiple granularity cycles, and the target data dimensions corresponding to multiple sub-query statements are summarized and added as new conditions to the filter conditions of the sub-query statement; the sub-query statement containing the filter condition is pushed down to the calculation The engine concurrently reads the target data corresponding to each granularity period and the dimension values in it, and returns the query result to the user after calculation by the processing end.

In the SQL optimization query method provided in this embodiment, before the calculation engine queries the target data according to the SQL query statement, it is necessary to perform pre-statistics on the number of storage segments involved in the target data. When the number of storage segments involved is greater than the threshold, if the full The query may trigger the fuse mechanism of the computing engine, so the solution adopted in this embodiment is to split the query period corresponding to the subquery statement into multiple granularity periods, and at the same time, in order to avoid the target data dimension corresponding to different subquery statements For the problem that the query results cannot be output because of the difference, before querying, it is necessary to summarize the target data dimensions corresponding to multiple subquery statements as new conditions and add them to the filter conditions of each subquery statement. After the above operations are completed, the filter conditions will eventually be included. The sub-query statement is pushed down to the calculation engine to concurrently read the target data corresponding to each granularity cycle and the dimension values in it, and return the query result to the user after calculation by the processing end.

It can be seen that this embodiment not only solves the problem that the fuse mechanism of the computing engine is easily triggered during the full query process, but also ensures that the target data dimensions corresponding to each sub-query statement are unified, so that the query result can be output.

In specific implementation, the query statement of the UNION collection type is usually expressed as UNION ALL, which includes multiple subquery statements, such as the first cycle subquery statement and the second cycle subquery statement, and the filter conditions (limit conditions) except the newly added conditions It also includes query measures, dimension sorting, and the number of target data queries. The calculation engine is druid, the data source is datasource, the storage segment is segment, and the processing end is spark. Among them, the number of target data queries in the filter condition is mainly used to limit the number of data read by the calculation engine target, in order to avoid triggering the fuse mechanism .

In the above embodiment, when the number of stored segments is greater than the threshold, the method for splitting the query cycle corresponding to the sub-query statement into multiple granularity cycles includes:

The query period corresponding to each sub-query statement is extracted, and each query period is evenly split into multiple granularity periods according to the preset granularity.

Exemplarily, the SQL query statement is composed of an inner statement and an outer statement. Taking the ring cycle query as an example, the inner statement includes the first period subquery statement and the second period subquery statement, that is, the current period subquery statement and The main function of the historical period subquery statement is to read the target data corresponding to the subquery statement of the first period and the subquery statement of the second period respectively from druid. The main function of the outer statement is to The target data dimension value read by the layer statement is calculated accordingly.

For specific implementation, please refer to Figure 2, obtain the logical plan tree by parsing the SQL query statement, and extract the metadata information that can be used for clipping, such as storage fragment query instructions involved in the target data, target data dimension query instructions, and target data metrics Query command, target data cycle query command, etc., and then check whether the number of storage segments involved in the target data in the query cycle is greater than the threshold, if not greater than the threshold, the existing technology can be used to push down the entire subquery of the SQL query statement to druid for execution Full query, if it is greater than the threshold, the query period can be split into multiple granularity periods on average for concurrent query, so as to avoid triggering druid's fuse mechanism. For example, when the datasource is ingested, the minimum granularity is days, and the current SQL query statement needs to query data for one month, then the one-month query period is divided into 30 granularity periods. Next, organize the SQL query statements corresponding to each granularity period, so that the SQL query statements corresponding to each granularity period only retain the required target dimension data, and filter out other irrelevant derived measurement data. After sorting out the SQL information corresponding to these granularity periods, convert it into a json statement that druid can receive, and then concurrently request druid to obtain the target data. After druid queries the target data corresponding to each granularity period, it will correspond to each granularity period. The dimension set and the corresponding dimension value are returned, and then the data sets returned by all granularity periods are merged, and a data set that is not more than the number of target data queries is obtained through the selection sorting algorithm.

Exemplarily, please refer to Figure 3, the year-on-year growth results of this month and last month are summarized through SQL query statements, and the target data query quantity in the filter condition is set to 500 pieces of data. If you directly perform a full-scale data query with a period of one month in Druid, there are a total of 200 million pieces of target data in the Druid large table. In this embodiment, a month is divided into 30 granularity periods, and each granularity period corresponds to about 100,000 pieces of data. For target data, use druid to concurrently read the data sets corresponding to each granularity cycle of this month and last month. Since the number of target data queries in the filter condition is 500, only the first 500 pieces of target data are filtered out from each granularity cycle, and then Then aggregate the target data belonging to the same granularity cycle of this month and last month into 500 target data, and then use druid to scan the 500 target data of each granularity cycle, and after filtering again, finally get 500 query results and return them to the user . Compared with the full query scheme adopted in the prior art, this embodiment only sets the number of target data queries in the filter condition to 500 according to needs, and then only performs the query on the top 500 target data according to the filter condition in druid. Computational reading greatly reduces the computational pressure of druid.

In the above embodiment, the method of summarizing the target data dimensions corresponding to multiple sub-query statements and adding them as new conditions to the filter conditions of the sub-query statements includes:

Identify the target data dimension corresponding to each sub-query statement. When the target data dimensions of each sub-query statement are consistent, take the target data dimension of any sub-query statement as a new condition and add it to the filter condition of each sub-query statement; when each sub-query statement If the target data dimensions of the query statements are inconsistent, the target data dimensions of each sub-query statement are aggregated and merged and added as new conditions to the filter conditions of each sub-query statement.

During the specific implementation, in order to prevent the problem that the spark side cannot perform corresponding calculations based on the target data obtained in the first cycle and the target data obtained in the second cycle based on the target data acquired in the first cycle and the second cycle due to the inconsistency of the corresponding target data dimensions in the first cycle and the second cycle, For example, the number of target data dimensions corresponding to the first period is 100, and the number of target data dimensions corresponding to the second period is 150. At this time, the target data dimensions corresponding to the first period and the second period need to be combined as new conditions and added to each In the filter conditions of subquery statements, the merged target data dimension can cover the dimensions corresponding to the target data in the first period and the second period.

In the above embodiment, the method for the calculation engine to concurrently read the target data corresponding to each granularity period and the dimension values therein includes:

According to each sub-query statement and the filter conditions therein, the computing engine queries the target data that meets the target data query quantity condition from the corresponding storage segment of the corresponding data source and extracts the dimension values therein.

In the above-mentioned embodiment, the method for returning the query result to the user after being calculated by the processing terminal includes:

The processing end returns the query result to the user after calculation according to the dimension value returned by each sub-query statement according to the preset rules. For example, for the calculation that the preset rule is the growth rate, the dimension value obtained in the first cycle can be divided by the dimension value obtained in the second cycle to obtain the growth rate result.

In the above-mentioned embodiment, in the step, the sub-query statement containing the filter condition is pushed down to the calculation engine and concurrently reads the target data corresponding to each granularity period and the dimension value therein, and returns the query result to the user after calculation by the processing terminal Then also include:

Count the hot data sources and the hot data dimensions in the historical SQL query statements, and pre-extract the dimension values corresponding to the hot data dimensions for quick invocation of subsequent SQL query statements.

During the specific implementation, statistics are kept during the execution of SQL query statements, the most frequent data source and its dimension fields will be used, and the druid statement will be dynamically constructed during the low query period, and the dimension and dimension value will be pre-extracted, similar to It is pre-stored like a materialized view, and later found that if there is the same data source and dimension value query requirements, it can be quickly extracted from the materialized view without sending a request to druid to fetch the corresponding dimension value set data, so as to This implements fast calls.

In the above-mentioned embodiment, in the step, the sub-query statement containing the filter condition is pushed down to the calculation engine and concurrently reads the target data corresponding to each granularity period and the dimension value therein, and returns the query result to the user after calculation by the processing terminal Then also include:

Each subquery statement, corresponding target data and dimension values therein are cached in the database, so that the same subquery statement can be quickly called from the database when the same subquery statement appears subsequently.

In specific implementation, when the query result of this SQL query statement comes out, each subquery statement and the corresponding target data and the dimension values in it can be cached in the database, stored in Hbase or other cache libraries, and then the same When querying, you can directly modify the execution plan based on the cached results without re-executing. Specifically, if the current SQL query statement includes the query results of the historical cycle, you can split the query cycle of the current SQL query statement, including The historical cycle query results are directly obtained from the cache library, and the current period of the historical cycle not included is obtained from the druid query. For example, dimension data from 1st to 20th has been cached. If the current query is from 1st to 30th dimension, you only need to press down on the granularity period between 21st and 30th to query druid, which greatly reduces druid pressure.

After obtaining the combined target data of the current period and the historical period, you can start to organize the subquery statements corresponding to the SQL query statement:

1. Sort the group by dimension field of the subquery statement;

2. Sort the derived indicators that need to be calculated by the subquery statement;

3. Summarize the target data dimensions corresponding to multiple subquery statements and add them as new conditions to the filter conditions of the subquery statements;

After sorting out each subquery statement, convert it into a json statement of the druid computing engine. Because the filter condition is added, the dimension information to be queried by the subquery statement this time is greater than the filter condition. That is to say, Druid does not need to scan all the data in the query cycle, and there will be no fusing and query timeout exceptions, which reduces the query pressure of Druid.

And because the amount of data returned by druid is filtered, the spark side does not need to receive all the data in the query cycle, which reduces the amount of data processing on the spark side, and the overall efficiency will be greatly improved.

Embodiment two

This embodiment provides a SQL optimization query device, including:

A statement parsing unit, configured to count the number of storage segments involved in the target data when parsing the SQL query statement as a query statement of the UNION set type, the query statement of the UNION set type including a plurality of sub-query statements;

An identification processing unit, configured to split the query cycle corresponding to the sub-query statement into multiple granularity cycles when the number of the storage segments involved is greater than a threshold, and after summarizing the target data dimensions corresponding to the multiple sub-query statements Adding it as a new condition into the filter condition of the subquery statement;

The query unit is configured to push down the sub-query statement containing the filter condition to the calculation engine and concurrently read the target data corresponding to each granularity period and the dimension value therein, and return the query result to the user after calculation by the processing terminal.

Compared with the prior art, the beneficial effect of the SQL optimized query device provided in the embodiment of the present invention is the same as that of the SQL optimized query method provided in the first embodiment above, and will not be repeated here.

Embodiment Three

This embodiment provides a computer-readable storage medium. A computer program is stored on the computer-readable storage medium. When the computer program is run by a processor, the steps of the above SQL optimization query method are executed.

Compared with the prior art, the beneficial effect of the computer-readable storage medium provided by this embodiment is the same as the beneficial effect of the SQL optimized query method provided by the above technical solution, which will not be repeated here.

Those of ordinary skill in the art can understand that all or part of the steps in the above-mentioned inventive method can be completed by instructing related hardware through a program. The above-mentioned program can be stored in a computer-readable storage medium. When the program is executed, it includes: For each step of the method in the above embodiments, the storage medium may be: ROM/RAM, magnetic disk, optical disk, memory card, and the like.

The above is only a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Anyone familiar with the technical field can easily think of changes or replacements within the technical scope disclosed in the present invention, and should cover all Within the protection scope of the present invention. Therefore, the protection scope of the present invention should be determined by the protection scope of the claims.

Claims (9)

1. An SQL optimization query method is characterized by comprising the following steps:

when the SQL query statement is analyzed to be a UNION set type query statement, counting the number of storage segments related to target data, wherein the UNION set type query statement comprises a plurality of sub query statements;

when the number of the related storage segments is larger than a threshold value, splitting the query cycle corresponding to the sub-query statement into a plurality of granularity cycles, summarizing target data dimensions corresponding to the sub-query statements, and adding the summarized target data dimensions as newly added conditions into the filtering conditions of the sub-query statement;

pushing down the sub-query statements containing the filtering conditions to a computing engine, reading target data corresponding to each granularity period and a dimension value in the target data, and returning a query result to a user after the target data are computed by a processing end;

the method for adding the collected target data dimensions corresponding to the sub-query statements serving as the newly added conditions into the filter conditions of the sub-query statements comprises the following steps:

identifying a target data dimension corresponding to each sub-query statement, and when the target data dimensions of the sub-query statements are consistent, adding the target data dimension of any sub-query statement as a newly added condition into the filtering condition of each sub-query statement;

and when the target data dimensions of the sub-query statements are not consistent, summarizing and combining the target data dimensions of the sub-query statements respectively, and adding the combined target data dimensions as newly added conditions into the filtering conditions of each sub-query statement.

2. The method of claim 1, wherein splitting the query cycle corresponding to the sub-query statement into a plurality of granularity cycles when the number of the storage segments is greater than a threshold value comprises:

extracting a query cycle corresponding to each sub-query statement, and averagely dividing each query cycle into a plurality of granularity cycles according to preset granularity.

3. The method of claim 1, wherein the query statements of UNION set type include at least a first periodic sub-query statement and a second periodic sub-query statement, and wherein the filter conditions include, in addition to the new conditions, query metrics, dimensional ordering, and target data query quantity.

4. The method of claim 3, wherein the method for the compute engine to concurrently read the target data and the dimension value thereof corresponding to each granularity period comprises:

and respectively querying the target data meeting the target data query quantity condition from the corresponding storage segment of the corresponding data source and extracting the dimension value of the target data according to each sub-query statement and the filter condition in the sub-query statement by the computing engine.

5. The method of claim 4, wherein the method for returning the query result to the user after the processing end computing comprises:

and the processing terminal returns a query result to the user after calculating according to a preset rule according to the dimension value returned by each sub-query statement.

6. The method according to claim 1, wherein the pushing down the sub-query statement containing the filter condition to the compute engine and reading the target data and the dimension value in the target data corresponding to each granularity period in step, and after the query result is returned to the user after the computation by the processing end, the method further comprises:

and counting hot data sources in the historical SQL query statement and hot data dimensions in the historical SQL query statement, and pre-extracting dimensional values corresponding to the hot data dimensions for quick calling of subsequent SQL query statements.

7. The method according to claim 1, wherein the pushing down the sub-query statement containing the filter condition to the compute engine and reading the target data and the dimension value in the target data corresponding to each granularity period in step, and after the query result is returned to the user after the computation by the processing end, the method further comprises:

and caching each sub-query statement, the corresponding target data and the dimension value in the sub-query statement in the database so as to be quickly called from the database when the same sub-query statement appears in the follow-up process.

8. An apparatus for implementing the SQL optimized query method of claim 1, comprising:

the statement analyzing unit is used for counting the number of storage segments related to target data when an SQL query statement is analyzed to be a UNION set type query statement, and the UNION set type query statement comprises a plurality of sub query statements;

the identification processing unit is used for splitting the query cycle corresponding to the sub-query statement into a plurality of granularity cycles when the number of the related storage segments is larger than a threshold value, summarizing target data dimensions corresponding to the sub-query statements and adding the summarized target data dimensions as new conditions into the filter conditions of the sub-query statements;

and the query unit is used for pushing down the sub-query statements containing the filter conditions to a calculation engine, reading the target data corresponding to each granularity period and the dimension value in the target data, calculating by the processing end and returning a query result to the user.

9. A computer-readable storage medium, on which a computer program is stored, which, when being executed by a processor, carries out the steps of the method according to any one of the claims 1 to 7.

Images