Method for optimizing SQL (Structured Query Language) execution efficiency in data warehouse

Abstract

The invention discloses an SQL execution efficiency optimization method in a data warehouse, and relates to the technical field of database management and data processing. Comprising the following steps: based on a database protocol, according to rule configuration, identifying a data source, carrying out data source adaptation, sequentially carrying out SQL grammar analysis, SQL routing identification and SQL rewriting, forming an execution plan, caching the execution plan for repeated execution and calling, mapping data to a plurality of data storage partitions during write operation, and avoiding data inclination during write; during reading operation, a data scanning algorithm is utilized to automatically adapt to a data source, fragment query is rewritten, and empty scanning is reduced; submitting the execution plan to an SQL (Structured Query Language) execution engine, performing standard grammar execution and complex grammar analysis by the execution engine, generating a secondary execution plan, and if write operation is performed, performing partition storage of hot data and regularly performing cold data archiving by the execution plan; if the read operation is carried out, partition pruning optimization is carried out, and data merging and assembling are carried out through an SQL execution engine: returned data is merged, and if the read operation is carried out, an execution plan is dynamically analyzed, memory distribution of index data is adjusted, and high-frequency data is cached; and if the operation is the write operation, compressing and storing the data, and adjusting the index.

Description

Technical Field

[0001] The invention discloses a method for optimizing SQL execution efficiency in a data warehouse, and relates to the technical field of database management and data processing. Background Art

[0002] With the rapid development of information technology, the amount of data in data warehouses has exploded, and reaching the PB level has become the norm. Existing data processing methods face many problems, such as low efficiency of SQL statement execution, limitations of data compression and caching technology, low efficiency of high-frequency SQL execution, and bottlenecks in data storage engines when processing massive data. These problems have seriously affected the performance of data warehouses and data processing efficiency. Summary of the invention

[0003] In view of the problems of the prior art, the present invention provides a method for optimizing SQL execution efficiency in a data warehouse, which improves the overall data management efficiency and query performance by reducing storage space usage, optimizing index structure, quickly querying and efficiently processing data, and archiving infrequently accessed data.

[0004] The specific scheme proposed by the present invention is:

[0005] The present invention provides a method for optimizing SQL execution efficiency in a data warehouse, comprising:

[0006] Based on the database protocol, according to the rule configuration, the data source is identified, the data source is adapted, and SQL syntax parsing, SQL routing identification and SQL rewriting are performed in sequence to form an execution plan.

[0007] The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans.

[0008] Submit the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and stored, and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed.

[0009] Data merging and assembly are performed through the SQL execution engine: the returned data is merged and processed. If it is a read operation, the execution plan is dynamically analyzed and the memory distribution of the index data is adjusted to cache high-frequency data; if it is a write operation, the data is compressed and stored, and the index is adjusted.

[0010] Furthermore, in the method for optimizing SQL execution efficiency in a data warehouse, data storage partitions are performed according to actual business needs and data characteristics and in combination with time, geographic location, user ID, and hash value, and secondary partitioning and multiple partitioning can be performed through different dimensions.

[0011] Furthermore, in the method for optimizing SQL execution efficiency in a data warehouse, when executing data storage through a SQL execution engine, two strategies, online archiving and offline archiving, are adopted to realize partition storage and data archiving, wherein online archiving is used to store data on a storage device directly connected to the network, and offline archiving is used to transfer infrequently used data to a storage medium not connected to the network.

[0012] Furthermore, the dynamic analysis execution plan in the method for optimizing SQL execution efficiency in a data warehouse includes: using multiple indexing methods to perform execution plan analysis to provide an optimal path for data query, the indexing methods include multi-level indexing, hash indexing and full-text indexing, hierarchical classification of data through multi-level indexing, organizing and managing data according to different levels, mapping data to an address space of a fixed size through hash indexing using the characteristics of hash functions, using hash functions to calculate hash values ​​to directly locate the location of data, analyzing and indexing text content through full-text indexing, and identifying and extracting keywords in the text.

[0013] Furthermore, the write operation in the method for optimizing SQL execution efficiency in a data warehouse compresses and stores the data, including: compressing and storing the data in a manner combining row compression and column compression, wherein when performing row compression storage, the differences between data rows are analyzed, row difference information is obtained, and the row difference information is encoded and stored; when performing column compression storage, the differences between data columns are analyzed, column difference information is obtained, and the column difference information is encoded and stored.

[0014] The present invention also provides a device for optimizing SQL execution efficiency in a data warehouse, comprising a SQL management module and an execution module,

[0015] The SQL management module is based on the database protocol and rules configuration, identifies the data source, adapts the data source, and performs SQL syntax parsing, SQL routing identification and SQL rewriting in sequence to form an execution plan.

[0016] The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans.

[0017] The execution module submits the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and stored, and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed.

[0018] Data merging and assembly are performed through the SQL execution engine: the returned data is merged and processed. If it is a read operation, the execution plan is dynamically analyzed and the memory distribution of the index data is adjusted to cache high-frequency data; if it is a write operation, the data is compressed and stored, and the index is adjusted.

[0019] Furthermore, the SQL management module in the SQL execution efficiency optimization device in the data warehouse partitions data storage according to actual business needs and data characteristics and in combination with time, geographic location, user ID, and hash value, and can perform secondary partitioning and multiple partitioning through different dimensions.

[0020] Furthermore, when the execution module in the SQL execution efficiency optimization device in the data warehouse executes data storage through the SQL execution engine, two strategies, online archiving and offline archiving, are adopted to realize partition storage and data archiving, wherein the data is saved on a storage device directly connected to the network through online archiving, and the data that is not frequently used is transferred to a storage medium that is not connected to the network through offline archiving.

[0021] Furthermore, the execution module in the SQL execution efficiency optimization device in the data warehouse dynamically analyzes the execution plan, including: using multiple indexing methods to analyze the execution plan to provide the best path for data query, the indexing methods include multi-level indexing, hash indexing and full-text indexing, hierarchical classification of data through multi-level indexing, organizing and managing data according to different levels, mapping data to an address space of a fixed size through hash indexing using the characteristics of hash functions, using hash functions to calculate hash values ​​to directly locate the location of data, analyzing and indexing text content through full-text indexing, and identifying and extracting keywords in the text.

[0022] Furthermore, the execution module in the SQL execution efficiency optimization device in the data warehouse performs a write operation to compress and store the data, including: using a combination of row compression and column compression to perform compressed storage, wherein when performing row compression storage, the differences between data rows are analyzed, row difference information is obtained, and the row difference information is encoded and stored; when performing column compression storage, the differences between data columns are analyzed, column difference information is obtained, and the column difference information is encoded and stored.

[0023] The benefits of the method of the present invention are:

[0024] 1. Improve data access efficiency: Through data partitioning, index optimization, partition pruning and other technologies, the amount of data scanning is reduced, the data location speed is accelerated, and the query operation can be completed faster. For example, when querying small tables and dimension tables, the use of index and partition technology can quickly obtain data and improve query response time.

[0025] 2. Improve data processing efficiency: Data compression reduces the amount of data transmission and processing. At the same time, reasonable partitioning and sharding technology enables data processing to be carried out in parallel, improving overall processing efficiency.

[0026] 3. Reduce storage costs: Compression technology reduces storage space usage, and data archiving stores low-frequency access data in a reasonable manner, reducing the demand and cost of storage devices.

[0027] 4. Reduce risks: Partition pruning technology improves data reliability and availability. By distributing and storing data on different nodes, it reduces the risk of data loss due to a single node failure. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 It is a schematic diagram of the application process of the method of the present invention. DETAILED DESCRIPTION

[0029] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it, but the embodiments are not intended to limit the present invention.

[0030] Example 1

[0031] The present invention provides a method for optimizing SQL execution efficiency in a data warehouse, comprising:

[0032] Step 1: Based on the database protocol and rule configuration, identify the data source, adapt the data source, perform SQL syntax parsing, SQL routing identification, and SQL rewriting in sequence to form an execution plan.

[0033] The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans.

[0034] For data storage partitioning, data storage partitioning is performed based on actual business needs and data characteristics and in combination with time, geographic location, user ID, and hash value, and secondary and multiple partitioning can be performed through different dimensions. Taking time partitioning as an example, assuming a database containing sales records for many years, if you need to query the sales data of a certain year, the traditional approach may require traversing the entire database, which will undoubtedly consume a lot of time and computing resources. However, after partitioning using algorithms such as Hash hashing and Bloom filtering, the system can vertically locate the data area corresponding to the year, greatly shortening the addressing time.

[0035] In addition, reasonable data partitioning can also help the system use the policy caching mechanism more efficiently, including caching of execution policy parsing result data and data caching. When data is stored according to certain rules, queries of the same type often repeatedly access the same data blocks, which makes it easier for these data blocks to be cached, thereby reducing the number of accesses to disk or remote storage and further improving query performance. At the same time, by partitioning data, more sophisticated permission control and security management can be achieved. Data in different partitions can be set with different access permissions based on their sensitivity and importance, ensuring that only authorized users can access specific data areas, thereby enhancing data security.

[0036] Data partitioning also helps simplify data maintenance and management. When a specific type of data needs to be updated or backed up, only the corresponding partition needs to be operated on, without having to process the entire data set. This greatly reduces the burden of data management and improves the flexibility of data processing.

[0037] Step 2: Submit the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and stored, and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed.

[0038] When executing data storage through the SQL execution engine, two strategies, online archiving and offline archiving, are adopted to achieve partition storage and data archiving.

[0039] Online archiving saves data on a storage device directly connected to the network. This method is suitable for historical data that may need to be frequently accessed or quickly retrieved. For example, a company's financial statements, customer transaction records, or recent project documents, these data usually need to be called up in a short time to support daily operational decisions. The advantage of online archiving is its efficient data recovery speed and convenient accessibility, but correspondingly, it may take up more storage space and incur higher maintenance costs.

[0040] Offline archiving moves infrequently used data to non-network-attached storage media, such as tapes, optical disks, or external hard drives. This approach is particularly suitable for storing data that will not be accessed for a long time but still needs to be retained to meet compliance requirements or future research needs. Offline archiving can significantly reduce storage costs because offline storage devices are generally cheaper than online storage solutions and do not require a continuous power supply to maintain the data state. In addition, offline archiving provides an additional layer of security because it reduces the risk of data being exposed to cyber attacks.

[0041] By combining online and offline archiving strategies, cost-effectiveness can be maximized while ensuring data integrity and accessibility. Online archiving ensures instant access to active data, while offline archiving provides an economical and secure storage solution for infrequently used data. This dual-track data management approach not only improves resource utilization efficiency, but also enhances the ability to respond to different data needs.

[0042] In addition, when performing partition pruning optimization, two methods, horizontal sharding and vertical sharding, are provided, and the process involves cache optimization of the execution strategy.

[0043] Horizontal sharding is a method of dividing data according to certain rules, such as data range, hash value, etc. Specifically, horizontal sharding divides the entire data set into multiple subsets, each of which contains a portion of the data. These subsets can be distributed on different nodes, thereby reducing the pressure on a single node. For example, if there is a table containing millions of records, it will be horizontally sharded according to the value of a certain field, so that each shard contains only a portion of the records. In this way, when querying this table, you only need to operate on the corresponding shard, without having to perform a full table scan on the entire table, thereby improving query efficiency.

[0044] Vertical sharding divides the data. Unlike horizontal sharding, vertical sharding divides the columns in the table. Specifically, vertical sharding assigns different columns of a table to different nodes. The advantage of this is that different columns can be optimized to improve query performance. For example, if a table has many columns, but only a few columns are frequently queried, these columns can be placed on one node, and other infrequently used columns can be placed on another node. When the query engine executes, it only needs to access the nodes containing commonly used columns, and does not need to access the nodes containing infrequently used columns, thereby reducing I / O operations and improving query speed.

[0045] Step 3: Merge and assemble data through the SQL execution engine: merge the returned data. If it is a read operation, dynamically analyze the execution plan and adjust the memory distribution of the index data to cache high-frequency data. If it is a write operation, compress and store the data and adjust the index.

[0046] The dynamic analysis execution plan includes: using multiple indexing methods to perform execution plan analysis to provide the best path for data query, the indexing methods include multi-level indexing, hash indexing and full-text indexing, hierarchical classification of data through multi-level indexing, organizing and managing data according to different levels, mapping data to an address space of a fixed size through hash indexing using the characteristics of hash functions, directly locating the location of data by calculating hash values ​​using hash functions, analyzing and indexing text content through full-text indexing, and identifying and extracting keywords in the text.

[0047] It uses multiple indexing methods such as multi-level indexing, hash indexing, and full-text indexing, combined with a cost-based optimizer to perform execution plan analysis and provide the best path for data query. Multi-level indexing classifies data in layers, hash indexing uses hash functions to quickly locate data, and full-text indexing meets the complex query requirements of text data.

[0048] In addition, when the write operation compresses and stores data, it may include: using a combination of row compression and column compression for compressed storage, wherein when performing row compression storage, the differences between data rows are analyzed, row difference information is obtained, and the row difference information is encoded and stored; when performing column compression storage, the differences between data columns are analyzed, column difference information is obtained, and the column difference information is encoded and stored. For example, when analyzing the row data and column data of a data table in a data warehouse, if some column data contains a large number of duplicate values, column compression is used for these columns, and row compression optimization is performed on the row data of the entire table. This can effectively reduce storage space while maintaining data integrity and accessibility.

[0049] Example 2

[0050] The present invention also provides a device for optimizing SQL execution efficiency in a data warehouse, comprising a SQL management module and an execution module,

[0051] The SQL management module is based on the database protocol and rules configuration, identifies the data source, adapts the data source, and performs SQL syntax parsing, SQL routing identification and SQL rewriting in sequence to form an execution plan.

[0052] The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans.

[0053] The execution module submits the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and stored, and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed.

[0054] Data merging and assembly are performed through the SQL execution engine: the returned data is merged and processed. If it is a read operation, the execution plan is dynamically analyzed and the memory distribution of the index data is adjusted to cache high-frequency data; if it is a write operation, the data is compressed and stored, and the index is adjusted.

[0055] As the contents of information interaction between modules in the above-mentioned device, execution of readable program process, etc. are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention, and will not be repeated here.

[0056] Likewise, the benefits of the device of the present invention are:

[0057] 1. Improve data access efficiency: Through data partitioning, index optimization, partition pruning and other technologies, the amount of data scanning is reduced, the data location speed is accelerated, and the query operation can be completed faster. For example, when querying small tables and dimension tables, the use of index and partition technology can quickly obtain data and improve query response time.

[0058] 2. Improve data processing efficiency: Data compression reduces the amount of data transmission and processing. At the same time, reasonable partitioning and sharding technology enables data processing to be carried out in parallel, improving overall processing efficiency.

[0059] 3. Reduce storage costs: Compression technology reduces storage space usage, and data archiving stores low-frequency access data in a reasonable manner, reducing the demand and cost of storage devices.

[0060] 4. Reduce risks: Partition pruning technology improves data reliability and availability. By distributing and storing data on different nodes, it reduces the risk of data loss due to a single node failure.

[0061] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.

[0062] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A method for optimizing SQL execution efficiency in a data warehouse, characterized by: include: Based on the database protocol, according to the rule configuration, the data source is identified, the data source is adapted, and SQL syntax parsing, SQL routing identification and SQL rewriting are performed in sequence to form an execution plan. The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans. Submit the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed. Data merging and assembly are performed through the SQL execution engine: the returned data is merged and processed. If it is a read operation, the execution plan is dynamically analyzed and the memory distribution of the index data is adjusted to cache high-frequency data; if it is a write operation, the data is compressed and stored, and the index is adjusted.

2. The method for optimizing SQL execution efficiency in a data warehouse according to claim 1, characterized in that For data storage partitioning, data storage partitioning is performed based on actual business needs and data characteristics and combined with time, geographic location, user ID, and hash value. Secondary partitioning and multiple partitioning can be performed through different dimensions.

3. The method for optimizing SQL execution efficiency in a data warehouse according to claim 1, characterized in that When performing data storage through the SQL execution engine, two strategies, online archiving and offline archiving, are adopted to realize partition storage and data archiving. Online archiving saves data on storage devices directly connected to the network, and offline archiving transfers infrequently used data to storage media not connected to the network.

4. The method for optimizing SQL execution efficiency in a data warehouse according to claim 1, characterized in that The dynamic analysis execution plan includes: using multiple indexing methods to perform execution plan analysis to provide the best path for data query, the indexing methods include multi-level indexing, hash indexing and full-text indexing, hierarchical classification of data through multi-level indexing, organizing and managing data according to different levels, mapping data to an address space of a fixed size through hash indexing using the characteristics of hash functions, directly locating the location of data by calculating hash values ​​using hash functions, analyzing and indexing text content through full-text indexing, and identifying and extracting keywords in the text.

5. The method for optimizing SQL execution efficiency in a data warehouse according to claim 1, characterized in that The write operation compresses and stores the data, including: compressing and storing the data in a manner combining row compression and column compression, wherein when performing row compression storage, the differences between data rows are analyzed, row difference information is obtained, and the row difference information is encoded and stored; when performing column compression storage, the differences between data columns are analyzed, column difference information is obtained, and the column difference information is encoded and stored.

6. A device for optimizing SQL execution efficiency in a data warehouse, characterized in that Including SQL management module and execution module, The SQL management module is based on the database protocol and rules configuration, identifies the data source, adapts the data source, and performs SQL syntax parsing, SQL routing identification and SQL rewriting in sequence to form an execution plan. The execution plan is cached for repeated execution and calling. During write operations, data is mapped to multiple data storage partitions to avoid data skew during write operations. During read operations, the data scanning algorithm is used to automatically adapt the data source, rewrite the shard query, and reduce empty scans. The execution module submits the execution plan to the SQL execution engine, which performs standard syntax execution and complex syntax analysis and generates a secondary execution plan. If a write operation is performed, hot data is partitioned and cold data is archived regularly according to the execution plan. If a read operation is performed, partition pruning optimization is performed. Data merging and assembly are performed through the SQL execution engine: the returned data is merged and processed. If it is a read operation, the execution plan is dynamically analyzed and the memory distribution of the index data is adjusted to cache high-frequency data; if it is a write operation, the data is compressed and stored, and the index is adjusted.

7. The device for optimizing SQL execution efficiency in a data warehouse according to claim 6, wherein SQL The management module partitions data storage based on actual business needs and data characteristics and combines time, geographic location, user ID, and hash value. It can also perform secondary and multiple partitions through different dimensions.

8. The device for optimizing SQL execution efficiency in a data warehouse according to claim 6, characterized in that When the execution module executes data storage through the SQL execution engine, it adopts two strategies, online archiving and offline archiving, to achieve partition storage and data archiving. Online archiving saves data on a storage device directly connected to the network, and offline archiving transfers infrequently used data to a storage medium that is not connected to the network.

9. The device for optimizing SQL execution efficiency in a data warehouse according to claim 6, characterized in that The execution module dynamically analyzes the execution plan, including: using multiple indexing methods to analyze the execution plan and provide the best path for data query. The indexing methods include multi-level indexing, hash indexing and full-text indexing. The data is hierarchically classified through multi-level indexing, and the data is organized and managed according to different levels. The hash index uses the characteristics of the hash function to map the data to an address space of a fixed size, and uses the hash function to calculate the hash value to directly locate the location of the data. The full-text index is used to analyze and index the text content, and identify and extract keywords in the text.

10. The method for optimizing SQL execution efficiency in a data warehouse according to claim 6, characterized in that When the execution module performs a write operation, the data is compressed and stored, including: compressed storage using a combination of row compression and column compression, wherein when row compression storage is performed, the differences between data rows are analyzed, row difference information is obtained, and the row difference information is encoded and stored; when column compression storage is performed, the differences between data columns are analyzed, column difference information is obtained, and the column difference information is encoded and stored.

Images