Data query method

Abstract

The application provides a data query method, which is applied to adaptive optimization of retrieval performance database for big data storage and mainly comprises the following steps: step 1: inputting a query request, a query module accepting and analyzing the query request to obtain a query condition; step 2: judging whether the same query condition exists in a cache module, if yes, directly obtaining a query result from the cache module, and if not, entering step 3; step 3: adjusting query resources allocated to each block data in a storage module by the query module according to a reward and punishment function of an optimization module and performing query to obtain a query result; step 4: recording information of each queried block data in the query process, including the query condition, query time and query result, and merging into a query result set; and step 5: caching the query condition and the query result into the cache module. The application solves the problem that the prior art cannot improve the query efficiency according to the real-time query condition of massive data.

Description

[0001] This application is a divisional application of Chinese Patent Application No. 202111291885.1, filed with the Chinese Patent Office on November 3, 2021, entitled "Adaptive Optimization Retrieval Performance Database and Data Query Method". Technical Field

[0002] This invention relates to a data query method, belonging to the field of big data storage. Background Technology

[0003] Data processing can be broadly divided into two categories: Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP). OLTP is the primary application of traditional relational databases, mainly handling basic, routine transactions, such as banking transactions. OLAP is the primary application of data warehouse systems, supporting complex analytical operations, focusing on decision support (hence also known as a DSS decision support system), and providing intuitive and easy-to-understand query results.

[0004] In OLAP scenarios, the most basic and effective optimization for data storage is to switch from row-based storage to column-based storage. Data compression is a common optimization technique in the storage field, significantly reducing the storage space of data on disk with controllable CPU overhead. This saves costs and reduces I / O and the overhead of data transfer across threads and nodes in memory. A higher compression ratio is not always better; algorithms with higher compression ratios often have slower compression and decompression speeds. A trade-off between CPU and I / O needs to be struck based on hardware configuration and usage scenarios. Data encoding can be understood as lightweight compression, including Restricted Execution Language (RLE) and data dictionary encoding. In column-based storage, both data compression and encoding are far more efficient than in row-based storage.

[0005] When an OLAP system performs a large amount of data access, it is limited by the data storage method and cannot distinguish between frequently used and infrequently used data. The different resource consumption of different query operations is not uniformly optimized, and it is impossible to improve query efficiency based on the real-time query status of massive amounts of data.

[0006] In view of this, it is indeed necessary to propose a new data query method to solve the above problems. Summary of the Invention

[0007] The purpose of this invention is to provide a data query method to solve the problem that existing data storage systems cannot improve query efficiency based on real-time query conditions of massive amounts of data.

[0008] To achieve the above objectives, this invention provides a data query method applied to an adaptively optimized retrieval performance database for big data storage. The adaptively optimized retrieval performance database includes a query module, a caching module, an optimization module, and a storage module. The data query method mainly includes the following steps:

[0009] Step 1: Input a query request. The query module accepts and parses the query request to obtain the query conditions.

[0010] Step 2: Determine if the same query conditions exist in the cache module. If they do, retrieve the query results directly from the cache module. If not, proceed to Step 3.

[0011] Step 3: Adjust the query resources allocated by the query module to each block of data in the storage module according to the reward and penalty function of the optimization module, and perform the query to obtain the query results;

[0012] Step 4: Record the information of each data block retrieved during the query process, including query conditions, query time, and query results, and merge them into a query result set;

[0013] Step 5: Cache the query conditions and query results in the cache module;

[0014] In step 3, the optimization module evaluates the weight α of block data i during the query process using a reward and penalty function, and calculates the information entropy of the query instruction. Where p i For query command a m Information entropy of class i, j represents a m There are j categories in total, and then the conditional information entropy of each queried resource is calculated. Among them, query resource r n There are k distinct attribute values. Query resource r n ={r n1 ,r n2 ,…r nk}, E(a m |r n ) for querying resource r n Next a m The conditional information entropy is then calculated, and the information gain G of n query resources for m query commands is then calculated. m (r n )=I(a m )-E(a m |r n Finally, normalization is used to obtain the m-th query instruction in the query resource r. n weight The query resources allocated to each data block during querying are adjusted in real time based on the weight α of data block i. The reward and penalty function is as follows:

[0015]

[0016] Where n represents the total number of data blocks, and E(d) represents the average time complexity of querying data blocks. i Let λ represent the time complexity of querying block i, where λ is the penalty coefficient and α is the weight of block i.

[0017] As a further improvement of the present invention, the adaptive optimization retrieval performance database also includes an index module that records the block information of each block of data. In this case, step 3 specifically includes:

[0018] Step 31: Filter the block feature information in the query conditions concurrently to the index module, and summarize the filtered block data of the query features;

[0019] Step 32: Perform multi-threaded concurrent filtering of the feature block data to be queried in the storage module to obtain the row index of the filtered block;

[0020] Step 33: Return the query results.

[0021] As a further improvement of the present invention, in step 3, when the weight α of the block data in the reward and punishment function is greater than 1, the positive allocation weight formula of the query resource is:

[0022]

[0023] Among them, w mn For the m-th query instruction in querying resource r n The weight.

[0024] As a further improvement of the present invention, in step 3, when the weight α of the block data in the reward and punishment function is 1, the query resources allocated to the block data are not changed.

[0025] As a further improvement of the present invention, in step 3, when the weight α of the block data in the reward and punishment function is less than 1, the reverse allocation weight formula for the queried resource is:

[0026]

[0027] Where wmn is the weight of the m-th query instruction in the queried resource rn.

[0028] As a further improvement of the present invention, the adaptive optimization retrieval performance database also includes a data segmentation module. The segmented data is obtained by the data segmentation module through multi-threading or multi-processing to segment the data to be stored and stored in the storage module.

[0029] As a further improvement of the present invention, the data segmentation module first scans the data to be stored and determines the data type of the data to be stored, and then performs segmentation processing according to the data type.

[0030] As a further improvement of the present invention, the resource allocation optimization of the query module for each block of data in the storage module is mainly based on calculating the gain of each query resource on the query instruction, wherein the set of query resources is R = {r1, r2, ..., r...} n} indicates that there are n query resources, and the set of query instructions is A = {a1, a2, ..., a...} m} indicates that there are m query commands.

[0031] As a further improvement of the present invention, the query resources include, but are not limited to, the number of threads, the number of CPU cores, memory, and hard disk cache.

[0032] As a further improvement of the present invention, the query instruction includes, but is not limited to, the number of rows scanned, the execution time, and the number of results returned.

[0033] The beneficial effects of this invention are: by using a reward and penalty function to optimize and update the query module through an optimization module, the query resources allocated to each block during the query are adjusted in real time, and the query time complexity of each block is changed, thereby improving query efficiency. This adaptive optimization of the retrieval and query process solves the problem that existing data storage systems cannot improve query efficiency based on the real-time query situation of massive amounts of data. Attached Figure Description

[0034] Figure 1 This is a structural block diagram of the adaptive optimization retrieval performance database of the present invention.

[0035] Figure 2 This is a flowchart of the data query method of the present invention.

[0036] Figure 3 This is a flowchart illustrating the specific process of the query module executing a query in this invention. Detailed Implementation

[0037] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be described in detail below with reference to the accompanying drawings and specific embodiments.

[0038] like Figure 1As shown, this invention discloses an adaptive optimization retrieval performance database 100, applied to big data storage, specifically including the following modules:

[0039] Data segmentation module 1 is used to segment the data to be stored into blocks using multiple threads or processes to obtain segmented data;

[0040] Storage module 2 is used to store the block data in data block module 1;

[0041] Index module 3 is used to record the block information of each block of data when the data to be stored is divided into blocks, and to form a data index table;

[0042] Query module 4 is used to query the stored block data;

[0043] Cache module 5 is used to cache query conditions and query results within a preset time period;

[0044] Optimization module 6 evaluates the query process and results using a reward and penalty function, and optimizes and updates query module 4 accordingly. It adjusts the query resources allocated to each data block in real time during the query process. The specific reward and penalty function is as follows:

[0045]

[0046] Where n represents the total number of data blocks, and E(d) represents the average time complexity of querying data blocks. i Let λ represent the time complexity of querying block i, where λ is the penalty coefficient and α is the weight of block i.

[0047] For a set of data to be stored, the data segmentation module 1 is used to scan the data in the data to be stored in a multi-threaded or multi-process manner and determine the data type of the data to be stored. Then, according to the data type, the appropriate segmentation method is selected to segment the data to be stored into blocks.

[0048] The data types to be stored specifically include: structured data and unstructured data.

[0049] When the data to be stored is structured data, i.e. tabular data, the data to be stored is logically divided into blocks. First, the field content in the data to be stored is identified, and then the identified field content is divided into blocks according to numerical characteristics or encoding format.

[0050] Numerical features include, but are not limited to: time, location, ID number, transaction account, amount, contact information, IP address, and other preset basic data attributes. Encoding formats include, but are not limited to: numeric, string, time (date), ACSII, UTF-8, and other preset data types.

[0051] When segmenting data based on numerical features, the data blocks are divided according to the primary data attribute corresponding to the numerical feature. The primary data attribute refers to the data attribute with the largest proportion in the data to be stored. For example, if the primary data attribute is a time value, the data field can be segmented by day; if the primary data attribute is geographic coordinates, the data field can be segmented by geographic region. The granularity of data segmentation selected during data segmentation changes according to the characteristics of the data attribute itself. If the amount of data in the segmented data after segmentation according to the preset granularity is still large, the granularity can be further reduced, and this large amount of data in the segmented data can be further divided into multiple smaller data segments.

[0052] For example, in a corporate employee data database, all employees are processed as employee data row by row according to specific attributes such as department, gender, year of employment, and identity information. The structured data can be divided into blocks and stored according to employee identity information (numerical features such as ID number) or department code (encoding format).

[0053] When the data to be stored is unstructured data, i.e. text information, the data to be stored is divided into dimensions. The data cube is cut according to the different dimensions of the data to be stored to obtain multiple data blocks. Each data block includes at least one unstructured data of a preset dimension, which is at least one dimension.

[0054] The data blocks that have been divided by the data block module 1 are stored in the storage module 2. The storage module 2 includes multiple distributed storage nodes, and each distributed storage node stores at least one data block.

[0055] Index module 3 is used to record the block information of each block of data when the data to be stored is divided into blocks, and to form a data index table.

[0056] Specifically, when dividing the data to be stored into blocks, the block information of each block is recorded. The block information includes, but is not limited to, block name, block number, and block characteristics. This block information is recorded in the block index table associated with the block data, and a new index record is added for each piece of data entered.

[0057] If the data type of the partitioned data is structured data, a tree index will be created when the index is created.

[0058] If the data type of the partitioned data is unstructured, then an inverted index is created when creating the index. The index creation process is as follows: Index module 3 -- Cache module 5 -- Storage module 2.

[0059] The current total set of indexes, i.e., the data index table, is obtained by summing up the various partitioned index tables that have been established.

[0060] The query module 4 is used to query the stored data.

[0061] The caching module 5 is used to cache query conditions and query results within a preset time period. The caching module 5 stores query conditions and results from at least one query. The preset caching time is determined by the customer and is not limited here. Specifically, in this embodiment, the preset caching time is preferably seven days. The caching module 5 will cache query conditions and results from queries performed within seven days. When the query module 4 performs a query, it compares the parsed actual query conditions with the query conditions stored in the caching module 5. When the query conditions are the same, the corresponding query results can be directly obtained from the caching module 5 without scanning the storage module 2, which can effectively improve the speed and efficiency of the query.

[0062] When the size of the data to be stored is 8-256GB, the cache module 5 also serves as a storage function, directly storing the data in the cache module 5. When the size of the data to be stored is greater than 256GB, it only caches the query conditions and query results within a preset time period. Of course, the range of "8-256GB" is only an example of a preferred embodiment. In other embodiments, this range can be adjusted according to the actual situation, and no limitation is made here.

[0063] Please see Figure 2 As shown, the present invention also provides a data query method applied to the aforementioned adaptive optimization retrieval performance database 100, which mainly includes the following steps:

[0064] Step 1: Input the query request. Query module 4 accepts and parses the query request to obtain the query conditions.

[0065] Step 2: Determine if the same query conditions exist in cache module 5. If they do, retrieve the query results directly from cache module 5. If not, proceed to step 3.

[0066] Step 3: Adjust the query resources allocated by the query module 4 to each block of data in the storage module 2 according to the reward and punishment function of the optimization module 6, and perform the query to obtain the query results;

[0067] Step 4: Record the information of each data block retrieved during the query process, including query conditions, query time, and query results, and merge them into a query result set;

[0068] Step 5: Cache the query conditions and query results in cache module 5.

[0069] Please see Figure 3 As shown, the specific steps for the query in step 3 include:

[0070] Step 31: Filter the block feature information in the query conditions concurrently to index module 3, and summarize the filtered block data of the features to be queried;

[0071] Step 32: Execute the filtering of the feature blocks to be queried concurrently in storage module 2 using multiple threads, and obtain the row index of the filtered blocks;

[0072] Step 33: Return the query results.

[0073] The optimization module 6 evaluates the query process and results through a reward and penalty function, optimizes and updates the query module 4, and adjusts the query resources allocated to each data block in real time when the query module 4 performs a query, thereby improving query efficiency.

[0074] The optimization module 6 obtains the query result set, which includes query conditions, query time, and query results from step 4, and then establishes a query reward and penalty function for the corresponding data blocks. Based on the reward and penalty function, it optimizes the resources allocated when each data block performs a query operation. The goal of the reward and penalty function is to make the query time complexity of each data block similar, thereby obtaining the optimal solution for the overall query efficiency.

[0075] The cost function for query optimization is as follows; the smaller the function value, the more efficient the query.

[0076]

[0077] Where n represents the total number of data blocks, and E(d) represents the average time complexity of querying data blocks. i Let λ represent the time complexity of querying block i, where λ is the penalty coefficient and α is the weight of block i.

[0078] The optimization objective of the reward / penalty function is to minimize the cost function. The formula for the reward / penalty function is:

[0079]

[0080] The weight value α of each data block is calculated by the reward and penalty function to determine whether to perform resource allocation optimization. If α > 1, positive resource optimization is performed to reduce the time complexity of data block query. If α = 1, no resource optimization operation is performed. If α < 1, reverse resource optimization is performed to increase the time complexity of data block query.

[0081] The resource allocation optimization of query module 4 for each block of data in storage module 2 is mainly based on calculating the gain of each query resource on the query instruction. The query resource set R = {r1, r2, ..., r...} n} represents the existence of n query resources, which include, but are not limited to, the number of threads, the number of CPU cores, memory and / or disk cache, and the query instruction set A = {a1, a2, ..., a...}. m} indicates that there are m query commands, which include, but are not limited to, commands for scanning rows, execution time, and number of results returned.

[0082] First, calculate the information entropy of the query command:

[0083]

[0084] Where, p i For query command a m Information entropy of class i, j represents a m There are j categories. In this embodiment, taking the number of scanned rows in the query command as an example, the number of scanned rows is classified into less than 5000 rows, 5000-1000 rows, and greater than 10000 rows. Therefore, in this embodiment, j=3.

[0085] Then calculate the conditional information entropy for each queried resource:

[0086]

[0087] Among them, for querying resource r n There are k distinct attribute values, therefore the resource r to be queried is... n ={r n1 ,r n2 ,…r nk}, E(a m |r n ) for querying resource r n Next a m The conditional information entropy.

[0088] Query resource r n The corresponding information gain can be expressed as:

[0089] G m (r n )=I(a m )-E(a m |r n )

[0090] The information gain G of n query resources for m query instructions is calculated. m (r n This allows us to determine the degree of influence of each queried resource on m query commands.

[0091] The m-th query instruction is obtained by normalization in the query resource r. n Weights:

[0092]

[0093] When the weight α of the block data in the reward / penalty function is greater than 1, the formula for the positive allocation weight of the query resource is:

[0094]

[0095] Optimized positive allocation of resource resources will reduce the time complexity d of block queries. i If the weight α of the block in the reward and punishment function is greater than 1, then positive resource allocation optimization is performed on the query resources, that is, the query resources allocated to the block data are increased. The increase in the number of query resources allocated can make the query process take less time, reduce the query time complexity of the block, and improve the query speed of the block data.

[0096] When the weight α of the data in the reward / penalty function is 1, the query resources allocated to each data block are not changed.

[0097] When the weight α of the block data in the reward / penalty function is less than 1, the formula for the reverse allocation weight of the query resource is:

[0098]

[0099] The optimized reverse allocation of resource allocation will increase the query time complexity d of the block-based query. i If the weight α of the block data in the reward and punishment function is less than 1, then reverse resource allocation optimization is performed on the query resources, that is, the query resources allocated to the block data are reduced. The reduction in the number of query resources allocated can make the query process take more time, increase the query time complexity of the block data, and reduce the query speed of the block data.

[0100] By changing the weight of query resource allocation for segmented data, the query time of segmented data can be increased or decreased, thereby dynamically balancing the query time between different segments and maintaining a small time difference, thus improving query efficiency.

[0101] The adaptive optimization retrieval performance database 100 of this invention, as an OLAP database, employs a data chunking method when retrieving data within the database. This allows for simultaneous execution of retrieval tasks using multiple threads or processes. Each thread can execute one query instruction and record the result set separately. The more threads there are, the more query tasks the system can allocate. For example, to obtain data for each day, with a sufficient number of threads, each thread can handle hourly data, and the query results can be concatenated and returned.

[0102] While a single query command may be fast, the overall return time is not necessarily the fastest. Therefore, optimal planning of the query process is necessary. Different threads should be allocated different numbers of CPU cores, memory, and / or disk caches when executing different query commands. By dynamically optimizing the query resources allocated to each block of data, the query of multiple blocks of data can be dynamically allocated according to the system load. This changes the time required for each thread to complete its respective query command, making the final completion times of each thread similar, thereby optimizing the overall query efficiency and making full use of query resources while reducing the overall time spent.

[0103] In summary, the adaptive optimization retrieval performance database 100 of the present invention optimizes and updates the query module 4 using a reward and penalty function through the optimization module 6, adjusting the query resources allocated to each data block in real time, changing the query time complexity of each data block, thereby improving query efficiency. This adaptive optimization of the retrieval query process solves the problem in existing data storage systems that cannot improve query efficiency based on the real-time query situation of massive amounts of data. The data block module 1 divides the data into blocks, allowing for multi-threaded or multi-process processing and querying of the data blocks. The index module 3 creates an index for each data block and aggregates them into a data index table, simplifying the query process, accelerating query speed, and enabling parallel query execution based on the index information of multiple data blocks, further improving query efficiency.

[0104] The above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit it. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can be made to the technical solutions of the present invention without departing from the spirit and scope of the technical solutions of the present invention.

Claims

1. A data query method applied to an adaptive optimization retrieval performance database for big data storage, the adaptive optimization retrieval performance database comprising a query module, a caching module, an optimization module, and a storage module, characterized in that, Data query methods mainly include the following steps: Step 1: Input a query request. The query module accepts and parses the query request to obtain the query conditions. Step 2: Determine if the same query conditions exist in the cache module. If they do, retrieve the query results directly from the cache module. If not, proceed to Step 3. Step 3: Adjust the query resources allocated by the query module to each block of data in the storage module according to the reward and penalty function of the optimization module, and perform the query to obtain the query results; Step 4: Record the information of each data block retrieved during the query process, including query conditions, query time, and query results, and merge them into a query result set; Step 5: Cache the query conditions and query results in the cache module; In step 3, the optimization module evaluates the weight α of block data i during the query process using a reward and penalty function, and calculates the information entropy of the query instruction. Where p x For query command a m Information entropy of class x, j represents a m There are j categories in total, and then the conditional information entropy of each queried resource is calculated. Among them, query resource r n There are k distinct attribute values. Query resource r n ={r n1 ,r n2 ,…r nk }, E(a m |r n ) for querying resource r n Next a m The conditional information entropy is then calculated, and the information gain G of n query resources for m query commands is then calculated. m (r n ) = I(a m )-E(a m |r n Finally, normalization is used to obtain the m-th query instruction in the query resource r. n weight The query resources allocated to each data block during querying are adjusted in real time based on the weight α of data block i. The reward and penalty function is as follows: Where n' represents the total number of data blocks, and E(d) represents the average time complexity of querying data blocks. i Let λ represent the time complexity of querying block i, λ be the penalty coefficient, and α be the weight of block i. When the weight α of the block in the reward / penalty function is greater than 1, the formula for the positive allocation weight of the query resource is: Among them, w mn For the m-th query instruction in querying resource r n The weights of the data blocks in the reward / penalty function are as follows: When the weight α of the data blocks in the reward / penalty function is 1, the query resources allocated to the data blocks remain unchanged; when the weight α of the data blocks in the reward / penalty function is <1, the reverse weight allocation formula for the query resources is: Among them, w mn For the m-th query instruction in querying resource r n The weight.

2. The data query method according to claim 1, characterized in that, The adaptive optimization retrieval performance database also includes an index module that records the block information of each block of data. Step 3 then specifically includes: Step 31: Filter the block feature information in the query conditions concurrently to the index module, and summarize the filtered block data of the features to be queried; Step 32: Perform multi-threaded concurrent filtering of the feature block data to be queried in the storage module to obtain the row index of the filtered block; Step 33: Return the query results.

3. The data query method according to claim 1, characterized in that: The adaptive optimization retrieval performance database also includes a data segmentation module. The segmented data is obtained by the data segmentation module through multi-threading or multi-processing to segment the data to be stored and then stored in the storage module.

4. The data query method according to claim 3, characterized in that: The data segmentation module first scans the data to be stored and determines the data type of the data to be stored, and then performs segmentation processing according to the data type.

5. The data query method according to claim 1, characterized in that: The optimization of resource allocation for each data block in the storage module by the query module is mainly based on calculating the gain of each query resource on the query instruction, where the set of query resources is R = {r1, r2, ..., r...}. n } indicates that there are n query resources, and the set of query instructions is A = {a1, a2, ..., a...} m } indicates that there are m query commands.

6. The data query method according to claim 5, characterized in that: The queried resources include, but are not limited to, the number of threads, the number of CPU cores, memory, and disk cache.

7. The data query method according to claim 5, characterized in that: The query command includes, but is not limited to, the number of rows to scan, the execution time, and the number of results to return.

Images