CN116662449A - OLAP query optimization method and system based on broadcast sub-query cache - Google Patents

Abstract

The invention discloses an OLAP query optimization method and system based on broadcast sub-query cache. The existing OLAP system query result caching scheme has a very low cache hit rate in time-series data analysis scenarios. The present invention proposes a new, more fine-grained, flexible caching scheme, that is, for partial results of subquery operators Caching avoids the design defects of query result caching, and can be directly applied to distributed big data analysis systems; the present invention can be directly applied in a cluster environment through a caching broadcast mechanism, so that subquery caching is available on all nodes, Improve the overall cache hit rate and make full use of distributed cluster capabilities to accelerate OLAP query performance.

Description

OLAP query optimization method and system based on broadcast subquery cache

technical field

The invention relates to the field of cloud network observable data analysis, in particular to a method and system for accelerating OLAP query performance.

Background technique

OLAP (Online Analytical Processing, Online Analytical Processing) is an online analytical processing system, and OLAP is mainly used for querying data. With the continuous development of OLAP, OLAP system products emerge in endlessly, most of which are ROLAP (Relational Database On-Line Analytical Processing) systems based on relational databases or single MOLAP (Multidimensional Database On-line Analytical Processing) systems.

With the rapid growth of commercial data volume, the traditional stand-alone OLAP database can no longer meet the needs of users, and modern OLAP systems basically adopt a distributed architecture. In the distributed OLAP system, the distributed optimization of SQL query is realized, so it supports the query and analysis of massive data.

In modern OLAP systems, the common SQL query optimization is mainly "Query Result Cache", which is used to cache the result set of a query statement. If there is the same query in the future, it will be read directly from the result set cache As a result, query performance is greatly improved without having to re-execute.

However, with the continuous growth of the query volume of "time series data", due to the design constraints of "Query Result Cache (Query Result Cache)", the SQL of each query must be completely consistent to hit the cache, but the query of time series data only The sub-query part of the layer is consistent, and the time parameters of the outer SQL are not consistent. As a result, each query cannot effectively hit the cache, which affects the query throughput performance of the entire system.

Contents of the invention

The object of the present invention is to provide an OLAP query optimization method and system based on broadcast sub-query cache to accelerate OLAP query performance. Compared with the existing OLAP system query result caching scheme, this scheme can be directly applied to the distributed big data analysis system with a more fine-grained and flexible caching algorithm, and fully utilizes the distributed cluster capability to accelerate the query performance of OLAP.

The purpose of the present invention is achieved by the following technical solutions:

According to the first aspect of this description, there is provided a method for optimizing an OLAP query based on broadcast subquery caching, the method comprising the following steps:

S1. When the management node receives a SQL query request for time series data, it splits it into subquery and final result aggregation query according to the operator, and uses the broadcast mechanism to cache the execution result of the subquery physical plan to the OLAP cluster for all work node;

S2. When the working node executes the SQL query for time series data, it directly performs the cache query of the subquery physical plan locally. If it hits the cache, it directly performs the next operator execution; Sub-execution, the execution result of the sub-query physical plan is cached locally, and the execution result is updated to all working nodes of the OLAP cluster through broadcasting.

Further, the management node receives an SQL query request for time series data, first parses the SQL into a logical plan, optimizes the logical plan into a physical plan, and then splits the physical plan into subqueries and The final result aggregates the query.

Further, the working node performs a hash operation on the received sub-query physical plan to obtain the identification ID, and uses the identification ID to try to obtain the cache of the sub-query physical plan from the local cache module; if the cache is obtained, the The cached result is loaded into the execution process and enters the operator calculation phase of the hash join; otherwise, the subquery physical plan is executed locally, the execution result is loaded into the execution process, and the hash join operator calculation phase is entered. The plan ID and execution result are combined into a cache structure and written into the local cache module.

Further, the local cache structure of the working node is expressed as HashMap<HashID, Result>, where HashID is the MD5 calculation value of the subquery physical plan, and Result is the data structure value in the memory of the working node.

Further, when the query process of the working node does not hit the cache, the sub-query physical plan will be executed on the working node, and the execution result will be written into the local cache structure HashMap<HashID, Result>; at the same time, all working nodes will be obtained from the management node PRC address, and broadcast HashMap<HashID,Result> to all working nodes.

According to the second aspect of this specification, a kind of OLAP query optimization system based on broadcast subquery caching is provided, and the system includes the following modules:

Sub-query module: deployed on the working nodes of the OLAP cluster, it is used to extract the sub-query physical plan, perform hash operation on the physical plan, obtain the identification ID, and use the identification ID to try to obtain the cache of the sub-query physical plan from the cache module; if obtained cache, the cached result is loaded into the execution process and enters the operator calculation stage of the hash join; otherwise, the physical plan of the subquery is executed locally, the execution result is loaded into the execution process, and the hash join operator calculation stage is entered. The ID of the query physical plan and the execution result are combined into a cache structure and written into the cache module;

Cache module: deployed on the working nodes of the OLAP cluster, it provides the ability of cache writing and parallel reading for the sub-query module; for the cache that is written for the first time, the broadcast module is called to broadcast the cache to all working nodes of the OLAP cluster;

Broadcast module: Provide cache broadcast service. After receiving the cache broadcast request from the cache module, obtain the PRC addresses of all working nodes from the management node, and broadcast the execution results of the subquery physical plan to all working nodes.

Further, the cache module provides a cache service based on the LRU strategy, and the cache structure is expressed as HashMap<HashID, Result>, where HashID is the MD5 calculation value of the sub-query physical plan, and Result is the data structure value in the memory of the working node .

Furthermore, the system can be deployed on various forms of computing node platforms, including ECS, Docker, and physical machine environments.

Compared with the prior art, the present invention has the following advantages:

First, a new, finer-grained, and flexible caching algorithm is proposed for "time-series type" data queries, which avoids the design flaw of "Query Result Cache" and can be directly applied to distributed big data In the analysis system, make full use of the distributed cluster capability to accelerate the query performance of OLAP.

Second, through the cache broadcast mechanism, it can be directly applied in the cluster environment, making the subquery cache available on all nodes, improving the overall cache hit rate, and making full use of the distributed cluster capabilities to accelerate OLAP query performance.

Description of drawings

Fig. 1 is the flow chart of the OLAP query optimization method based on the broadcast subquery cache provided by the embodiment of the present invention;

FIG. 2 is a structural diagram of an OLAP query optimization system based on broadcast subquery caching provided by an embodiment of the present invention.

Detailed ways

In order to make the above objects, features and advantages of the present invention more comprehensible, specific implementations of the present invention will be described in detail below in conjunction with the accompanying drawings.

In the following description, a lot of specific details are set forth in order to fully understand the present invention, but the present invention can also be implemented in other ways different from those described here, and those skilled in the art can do without departing from the connotation of the present invention. By analogy, the present invention is therefore not limited to the specific examples disclosed below.

As shown in FIG. 1 , an OLAP query optimization method based on broadcast subquery caching proposed by the embodiment of the present application can be divided into three steps for specific implementation.

(1) Design a new caching scheme, not for caching SQL query results, but for local results of subquery operators.

The cache solution is specifically implemented in the Worker node of the OLAP cluster. The Worker node is responsible for the execution of the physical plan of SQL. The traditional "SQL query result cache (Query Result Cache)" is to cache the query results of SQL in the proxy layer Proxy. In the time series data analysis scenario, the cache hit rate is very low; and this solution is for SQL The operator level of the physical plan performs local result caching, which is more fine-grained and flexible, and can greatly improve the cache hit rate in time-series data analysis scenarios, and make full use of distributed cluster capabilities to accelerate OLAP query performance.

(2) When the management node Master receives an SQL query request for time series data, it parses the SQL into a logical plan, optimizes the logical plan into a physical plan, splits the physical plan into subqueries by operator, and aggregates the final results Inquire. The execution results of the subquery physical plan are cached in each Worker node of the distributed system using the broadcast mechanism.

The operator splitting is mainly for the splitting of the physical plan, and the subquery is generally optimized as a hash join HashJoin. The Worker node performs a hash operation on the received subquery physical plan to obtain the identification ID, and uses the identification ID to try to obtain the cache of the subquery physical plan from the local cache module of the Worker node; if the cache is obtained, the cached result is loaded into Execute the pipeline and enter the operator calculation stage of HashJoin; if the cache is not obtained, execute the subquery physical plan locally, load the execution result into the pipeline, enter the operator calculation stage of HashJoin, and at the same time query the physical plan of the subquery The combination of the identification ID and the execution result becomes the cache structure HashMap<HashID,Result> and is written into the local cache module of the Worker node.

In the cache structure HashMap<HashID, Result>, HashID is the MD5 calculation value of the subquery physical plan, and Result is the data structure value in the memory of the Worker node.

(3) When the Worker node executes the SQL query for time series data, it directly executes the subquery physical plan cache query locally. If it hits the cache, it directly performs the next operator execution; if it does not hit the cache, it executes the subquery Operator execution, locally caches the execution results of the subquery physical plan, and updates the execution results to all Worker nodes in the OLAP cluster through broadcasting.

When the query process does not hit the cache, the subquery physical plan will be executed on the Worker node, and the result after execution will be written into the local cache structure HashMap<HashID,Result>; at the same time, all Worker nodes will be obtained from the Master node PRC address, and broadcast HashMap<HashID,Result> to all Worker nodes. In this way, the sub-query cache is available on all nodes, the overall cache hit rate is improved, and the distributed cluster capability is fully utilized to accelerate the query performance of OLAP.

As shown in Figure 2, a kind of OLAP query optimization system based on the broadcast sub-query cache proposed by the embodiment of the present application includes the following modules:

Sub-query module: deploy on the Worker node, extract the physical plan of the sub-query, perform a hash operation on the physical plan, obtain the identification ID, and use the identification ID to try to obtain the cache of the sub-query physical plan from the cache module; if the cache is obtained, then Load the cached results into the Pipeline and enter the operator calculation phase of HashJoin; if the cache is not obtained, execute the subquery physical plan locally, load the execution results into the Pipeline, enter the operator calculation phase of HashJoin, and at the same time subquery the physical plan The ID of the plan and the execution result are combined into a cache structure HashMap<HashID,Result> and written into the cache module.

Cache module: Deployed on the Worker node, it provides cache services based on the LRU strategy, and provides cache write and parallel read capabilities for the subquery module. Its core is a hash table HashTable, which is controlled by a read-write lock. The cache structure is HashMap<HashID,Result>. For the cache that is written for the first time, the broadcast module will be called to broadcast the cache to all Worker nodes in the OLAP cluster.

Broadcast module: Provide cache broadcast service. After receiving the cache broadcast request from the cache module, it will obtain the PRC addresses of all Worker nodes from the Master node, and broadcast HashMap<HashID,Result> to all Worker nodes. In this way, the sub-query cache is available on all nodes, the overall cache hit rate is improved, and the distributed cluster capability is fully utilized to accelerate OLAP query performance.

The embodiment of the present application implements a prototype system based on the Alibaba Cloud ECS platform, and tests the effect of the method. However, according to different hardware devices used by the computing nodes, the embodiment of the present application can also be popularized and deployed on platforms such as physical machines and Docker.

The above descriptions are only preferred implementations of the present invention. Although the present invention has been disclosed as above with preferred embodiments, it is not intended to limit the present invention. Any person familiar with the art, without departing from the scope of the technical solution of the present invention, can use the method and technical content disclosed above to make many possible changes and modifications to the technical solution of the present invention, or modify it into an equivalent of equivalent change Example. Therefore, any simple modifications, equivalent changes and modifications made to the above embodiments according to the technical essence of the present invention, which do not deviate from the technical solution of the present invention, still fall within the protection scope of the technical solution of the present invention.

Claims (8)

1. a kind of OLAP query optimization method based on broadcast subquery cache, it is characterized in that, comprising: S1. When the management node receives a SQL query request for time series data, it splits it into subquery and final result aggregation query according to the operator, and uses the broadcast mechanism to cache the execution result of the subquery physical plan to the OLAP cluster for all work node; S2. When the working node executes the SQL query for time series data, it directly performs the cache query of the subquery physical plan locally. If it hits the cache, it directly performs the next operator execution; Sub-execution, the execution result of the sub-query physical plan is cached locally, and the execution result is updated to all working nodes of the OLAP cluster through broadcasting. 2. The method according to claim 1, wherein the management node receives the SQL query request for time series data, first parses the SQL into a logical plan, optimizes the logical plan into a physical plan, and then converts the logical plan into a physical plan. The above physical plan is divided into subqueries and final result aggregation queries by operators. 3. The method according to claim 1, wherein the working node performs a hash operation on the received sub-query physical plan to obtain an identification ID, and uses the identification ID to try to obtain the sub-query from the local cache module Cache of the physical plan; if the cache is obtained, the cached result is loaded into the execution process and enters the operator calculation stage of the hash join; otherwise, the sub-query physical plan is executed locally, and the execution result is loaded into the execution process and entered into the hash In the operator calculation stage of the connection, at the same time, the identification ID of the subquery physical plan and the execution result are combined into a cache structure and written into the local cache module. 4. The method according to claim 1, wherein the local cache structure of the working node is represented as HashMap<HashID, Result>, wherein HashID is the MD5 calculation value carried out to the subquery physical plan, and Result is the working node Data structure values in memory. 5. The method according to claim 4, wherein, when the query process of the working node does not hit the cache, the sub-query physical plan will be executed at the working node, and the execution result is written into the local cache structure HashMap<HashID, Result>; at the same time, obtain the PRC addresses of all working nodes from the management node, and broadcast HashMap<HashID,Result> to all working nodes. 6. A kind of OLAP query optimization system based on broadcast subquery cache, it is characterized in that, comprising: Sub-query module: deployed on the working nodes of the OLAP cluster, it is used to extract the sub-query physical plan, perform hash operation on the physical plan, obtain the identification ID, and use the identification ID to try to obtain the cache of the sub-query physical plan from the cache module; if obtained cache, the cached result is loaded into the execution process and enters the operator calculation stage of the hash join; otherwise, the physical plan of the subquery is executed locally, the execution result is loaded into the execution process, and the hash join operator calculation stage is entered. The ID of the query physical plan and the execution result are combined into a cache structure and written into the cache module; Cache module: deployed on the working nodes of the OLAP cluster, it provides the ability of cache writing and parallel reading for the sub-query module; for the cache that is written for the first time, the broadcast module is called to broadcast the cache to all working nodes of the OLAP cluster; Broadcast module: Provide cache broadcast service. After receiving the cache broadcast request from the cache module, obtain the PRC addresses of all working nodes from the management node, and broadcast the execution results of the subquery physical plan to all working nodes. 7. The system according to claim 6, wherein the cache module provides a cache service based on an LRU strategy, and the cache structure is represented as HashMap<HashID, Result>, where HashID is the MD5 calculation performed on the subquery physical plan value, Result is the data structure value in the working node memory. 8. The system according to claim 6, wherein the system can be deployed on various forms of computing node platforms, including ECS, Docker, and physical machine environments.