Index recommendation method and device based on data features

Abstract

The embodiment of the application discloses a kind of index recommendation method and device based on data characteristics, multiple dimensions are obtained from the query history data of user, and aggregate group is constructed according to the dimension;Initial index is created according to the aggregate group, and the initial index is divided into multiple levels according to dimension combination;Candidate index set is obtained by pre-screening initial index of each level based on sampling algorithm;The index sub-set with the minimum generation value is searched from the candidate index set using genetic algorithm or greedy algorithm, as recommended index.The application can significantly improve the efficiency of pre-computation, save storage and computing cost.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and more specifically, to an indexing and recommendation method, apparatus, computer device, and storage medium based on data features. Background Technology

[0002] The concept of big data has taken root, and the demand for data analysis is increasing daily. Faced with the ever-growing volume of data, precomputation is clearly a crucial technology in the field of Online Analytical Processing (OLAP). By trading space for time, it significantly reduces the time cost of data analysis and effectively supports low-latency, high-concurrency data analysis scenarios.

[0003] Apache Kylin is a prime example of precomputation technology in the OLAP field, leveraging it to its fullest potential through the Cube system. When analyzing data, data can be configured with any number of dimensions, and a Cube acts like a multi-dimensional array of that data. Loading raw data into a Cube is Apache Kylin's precomputation process, primarily involving association and aggregation. Without any pruning optimization, Apache Kylin precomputs for every combination of dimensions, and the result of each dimension combination is called a Cuboid, which, broadly speaking, is also an index. These indexes form the Cube. As the number of dimensions increases, the number of indexes grows exponentially, resulting in significant overhead in computation and storage, reducing the practical usability of precomputation technology. Current solutions to this problem mostly rely on fixed filtering rules, such as necessary dimensions, hierarchical dimensions, and joint dimensions, to prune the Cube and reduce the number of indexes. This approach requires data analysts to have a deep understanding of multidimensional analysis theory and business scenarios. However, during the system cold start process, without experience in data analysis, it is almost impossible to set a reasonable filtering strategy. On the other hand, data in real scenarios is constantly changing, and it is difficult to dynamically adjust the filtering strategy in a timely manner.

[0004] Current indexing and filtering schemes in related technologies suffer from low automation efficiency, and no effective solution has yet been proposed. Summary of the Invention

[0005] This invention provides an index recommendation method, apparatus, computer device, and storage medium based on data features to address the problem of low automation efficiency in current index filtering schemes in related technologies.

[0006] To achieve the above objectives, a first aspect of the present invention provides an index recommendation method based on data features, comprising:

[0007] Multiple dimensions are obtained from the user's query history data, and aggregation groups are constructed based on the dimensions;

[0008] An initial index is created based on the aggregation group, and the initial index is divided into multiple levels according to the combination of dimensions;

[0009] A candidate index set is obtained by pre-screening the initial indexes at each level based on a sampling algorithm;

[0010] A genetic algorithm or a greedy algorithm is used to search for the subset of indexes with the smallest generation value from the candidate index set, which is then used as the recommended index.

[0011] Optionally, in one possible implementation of the first aspect, after obtaining the candidate index set, the method further includes:

[0012] Extract data features from all indexes in the candidate index set, including the column type referenced by the index, cardinality, and average row size.

[0013] Using an inexact deduplication algorithm, the cardinality of each dimension in the candidate index set, as well as the cardinality of each index, are calculated;

[0014] The average row size of each index in the candidate index set is estimated using a sampling algorithm.

[0015] Optionally, in one possible implementation of the first aspect, a candidate index set is obtained by pre-screening the initial index at each level based on a sampling algorithm, including:

[0016] Calculate the cosine distance between all pairs of initial indices at each level, excluding single-dimensional and full-dimensional indices.

[0017] If the cosine distance is less than a preset threshold, then the initial index is used as a candidate index.

[0018] Optionally, in one possible implementation of the first aspect, a genetic algorithm or a greedy algorithm is used to search for the subset of indices with the minimum generation value from the candidate index set, including:

[0019] The candidate index set is optimized using a cost function to obtain an index subset, which is as follows:

[0020] f(x) = αg(x) + βh(x)

[0021] Where g(x) is the storage cost of the index, which is determined by the cardinality of the index and the average size of the rows, h(x) is the query cost caused by missing index, and α and β are cost coefficients.

[0022] A second aspect of the present invention provides an index recommendation device based on data features, comprising:

[0023] The aggregation group building module is used to obtain multiple dimensions from the user's query history data and build aggregation groups based on the dimensions;

[0024] An initial index building module is used to create an initial index based on the aggregation group, wherein the initial index is divided into multiple levels according to the dimension combination;

[0025] The candidate index set determination module is used to pre-screen the initial indexes at each level based on a sampling algorithm to obtain a candidate index set.

[0026] The recommended index determination module is used to search for the subset of indexes with the smallest generation value from the candidate index set using a genetic algorithm or a greedy algorithm, and use it as the recommended index.

[0027] Alternatively, in one possible implementation of the second aspect, the apparatus further includes:

[0028] The index cardinality determination module is used to calculate the cardinality of each dimension in the candidate index set, as well as the cardinality of each index, using an inexact deduplication algorithm;

[0029] The index row average determination module is used to estimate the average row size of each index in the candidate index set using a sampling algorithm.

[0030] Optionally, in one possible implementation of the second aspect, the candidate index set determination module includes:

[0031] The cosine distance calculation unit is used to calculate the cosine distance between all pairs of initial indices at each level, where all initial indices do not include single-dimensional indices and full-dimensional indices.

[0032] The candidate index determination unit is used to select the initial index as a candidate index if the cosine distance is less than a preset threshold.

[0033] A third aspect of the present invention provides a computer device including a memory and a processor, wherein the memory stores a computer program executable on the processor, and the processor executes the computer program to implement the steps in the various method embodiments described above.

[0034] A fourth aspect of the present invention provides a readable storage medium storing a computer program, which, when executed by a processor, is used to implement the steps of the method described in the first aspect of the present invention and various possible designs of the first aspect.

[0035] The present invention provides a data feature-based index recommendation method, apparatus, computer device, and storage medium. This method involves obtaining multiple dimensions from a user's query history and constructing aggregation groups based on these dimensions; creating an initial index based on the aggregation groups, wherein the initial index is divided into multiple levels according to the dimension combinations; pre-screening the initial index at each level using a sampling algorithm to obtain a candidate index set; and using a genetic algorithm or a greedy algorithm to search for the subset of indexes with the minimum generation value from the candidate index set, which is then used as the recommended index. This invention significantly improves the efficiency of pre-computation and saves storage and computational costs. Attached Figure Description

[0036] Figure 1 A flowchart illustrating the data feature-based indexing and recommendation method provided in this embodiment of the invention;

[0037] Figure 2 This is a schematic diagram of the initial index generated by the combined group;

[0038] Figure 3 This is a diagram illustrating other indices containing dimension D, excluding ABCD.

[0039] Figure 4 A structural diagram of the data feature-based indexing and recommendation device provided in this embodiment of the invention. Detailed Implementation

[0040] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0041] The terms "first," "second," "third," "fourth," etc. (if present) in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that embodiments of the invention described herein can be implemented in sequences other than those illustrated or described herein.

[0042] It should be understood that in the various embodiments of the present invention, the sequence number of each process does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0043] It should be understood that in this invention, "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion, for example, a process, method, system, product, or device that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product, or device.

[0044] It should be understood that in this invention, "multiple" refers to two or more. "And / or" is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, "and / or B" can represent: A existing alone, A and B existing simultaneously, and B existing alone. The character " / " generally indicates that the preceding and following related objects are in an "or" relationship. "Contains A, B, and C", "Contains A, B, and C" means that all three A, B, and C are contained; "Contains A, B, or C" means that one of A, B, and C is contained; "Contains A, B, and / or C" means that any one, two, or three of A, B, and C are contained.

[0045] It should be understood that in this invention, "B corresponding to A", "B corresponding to A", "A and B correspond", or "B and A correspond" means that B is associated with A, and B can be determined based on A. Determining B based on A does not mean determining B solely based on A; B can also be determined based on A and / or other information. Matching A and B is defined as a similarity between A and B that is greater than or equal to a preset threshold.

[0046] Depending on the context, "if" as used here can be interpreted as "when," "when," "in response to determination," or "in response to detection."

[0047] The technical solution of the present invention will be described in detail below with reference to specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments.

[0048] Example 1:

[0049] This invention provides an index recommendation method based on data features, such as... Figure 1 The flowchart shown includes:

[0050] Step S110: Obtain multiple dimensions from the user's query history data and construct an aggregation group based on the dimensions.

[0051] In this step, relevant dimension information is extracted based on the user's past query history. Aggregate groups are then constructed based on this extracted dimension information to facilitate the creation of the initial index. For example: Dimensions A, B, C, and D are extracted from the user's query history data. Then, aggregate groups are created based on these dimensions, and each aggregate group contains dimensions A, B, C, and D.

[0052] Step S120: Create an initial index based on the aggregation group, wherein the initial index is divided into multiple levels according to the dimension combination.

[0053] In step S120, continuing with the above example, the dimensions of the aggregation group are A, B, C, and D. The initial index generated by an aggregation group is as follows: Figure 2 As shown (except for *), the indexes are divided into four layers according to dimensional combinations: [A, B, C, D], [AB, AC, AD, BC, BD, CD], [ABC, ABD, ACD, BCD], and [ABCD]. In this application, index construction evaluates the overall value of an index based on the storage cost and query cost required for each index. The constructed indexes are used to serve queries; therefore, the storage cost of the indexes can be viewed from a query perspective: 1) the breadth of queries covered by the index, and 2) the query time acceleration ratio.

[0054] Specifically, the breadth of query coverage refers to how many types of queries the constructed index can answer, while the query speedup ratio is the ratio of the time spent on indexed queries to the time spent on non-indexed queries. For example, suppose there is a clustered index Index1 containing dimensions A, B, and C, and measures M1, M2, and M3. In terms of query breadth, any query that queries any combination of these dimensions and measures can be covered by this index. In this example, suppose the time spent hitting the index is t1, and the time spent not hitting the index is t2. The speedup ratio is (t2-t1) / t2. When t1 > t2, it's a negative speedup, meaning it's actually slower than the case where the index is not hit.

[0055] Step S130: Based on the sampling algorithm, the initial index of each level is pre-screened to obtain a candidate index set.

[0056] In this step, after creating the initial index based on the aggregation group, considering the potential for dimensionality explosion due to excessive dimensions during index generation, a stratified sampling algorithm is used to pre-screen the initial indexes at each level during the search, resulting in a smaller set of indexes. During this pre-screening process, the cosine distance between all pairs of initial indexes within each level is calculated (excluding single-dimensional and full-dimensional indexes). If the cosine distance is less than a preset threshold, the initial index is considered a candidate index. A smaller cosine distance indicates less similarity between two indexes. To include indexes with low similarity in the candidate set as much as possible, a preset threshold is set (this threshold can be manually set according to actual conditions). This ensures that the final total number of retained indexes does not exceed 2n^2, while the original total number of indexes is 2^n. When n is large, 2n^2 is much smaller than 2^n. The indexes obtained through the above steps serve as an initial candidate set.

[0057] Step S140: Use a genetic algorithm or a greedy algorithm to search for the subset of indexes with the smallest generation value from the candidate index set, and use it as the recommended index.

[0058] In step S140, after pre-screening each level to obtain a candidate index set, a subset with the minimum cost value is searched based on a genetic algorithm or a greedy algorithm. This subset is obtained by continuously optimizing the candidate index set according to a cost function, which is defined as follows:

[0059] f(x) = αg(x) + βh(x)

[0060] Where g(x) is the storage cost of the index, which is determined by the cardinality of the index and the average size of the rows, h(x) is the query cost caused by missing index, and α and β are cost coefficients.

[0061] Specifically, the storage cost of an index can be estimated by the number of bytes it occupies, while the query cost of an index can be the time required to build it. The cardinality and average row size of an index are data characteristics that can be estimated using inexact deduplication (HLL) methods and sampling algorithms.

[0062] More specifically, "continuously optimizing the candidate index set based on the cost function" essentially optimizes the candidate index set based on the storage cost and query cost of the index. Let's illustrate this with the following example: Suppose the estimated cost of index D is 100, while the cost of index ABCD is 110. Clearly, their costs are very close. When retaining ABCD, it can definitely cover the index with dimension D. However, retaining only the index with dimension D makes it impossible to query both dimensions simultaneously, resulting in very high query costs. Therefore, ABCD will ultimately be retained. Compared to using the ABCD index alone, other indexes containing dimension D (such as...) Figure 3 If the storage overhead of the portion within the box exceeds the query benefit, these indexes will be excluded from the optimal index list. Whether the remaining indexes are needed depends on the trade-off between their query and storage costs and will be optimized through continuous iteration. In summary, this implements an algorithm that automatically filters data based on characteristics without business knowledge, using index cost modeling. Furthermore, in actual use, it checks for data changes at a certain frequency; if changes occur, new indexes are recommended based on the above method, thus preventing index performance degradation caused by data changes.

[0063] In one embodiment, after obtaining the candidate index set, the method further includes:

[0064] Extract data features from all indexes in the candidate index set, including the column type referenced by the index, cardinality, and average row size.

[0065] Using an inexact deduplication algorithm, the cardinality of each dimension in the candidate index set, as well as the cardinality of each index, are calculated;

[0066] The average row size of each index in the candidate index set is estimated using a sampling algorithm.

[0067] In this embodiment, after obtaining a candidate index set by pre-screening the indexes at each level, it is necessary to sample all indexes in the candidate index set to determine the cardinality of the indexes. Specifically, first, the cardinality of the four dimensions A, B, C, and D is estimated using a partially redundant (HLL) algorithm. Then, for each level of indexes, a sampling algorithm is used to obtain a small dataset, and based on this dataset, the sample cardinality of A, B, C, and D, as well as the cardinality of each index, is calculated, thus deriving the estimated cardinality of the index. To control the number of samplings, data sampling can be performed once for each level, with the number of samplings corresponding to the number of dimensions. Except for single-dimensional indexes [A, B, C, D] and full-dimensional indexes [ABCD], the number of samplings for each level is controlled to be within twice the total number of dimensions. Similarly, in this step, the average row size of each index can be estimated from the candidate index set using a sampling algorithm.

[0068] The present invention provides a data feature-based index recommendation method. This method obtains multiple dimensions from a user's query history and constructs aggregation groups based on these dimensions. An initial index is created based on the aggregation groups, and this initial index is divided into multiple levels according to the dimension combinations. A sampling algorithm is used to pre-screen the initial indexes at each level to obtain a candidate index set. A genetic algorithm or a greedy algorithm is then used to search for the subset of indexes with the lowest generation value from the candidate index set, which is then used as the recommended index. This invention significantly improves the efficiency of pre-computation and saves storage and computational costs.

[0069] Technical effects:

[0070] (1) This application makes recommendations based on the characteristics of the original data, without requiring any other input, and automatically completes the index recommendation. No business knowledge is required, which lowers the entry threshold for the pre-computation system during the cold start process.

[0071] (2) In this application, the initial index of each level is pre-screened according to the hierarchical sampling algorithm during the search to obtain a smaller set of indexes, thereby effectively solving the problem of dimension explosion caused by too many dimensions.

[0072] (3) This application uses the idea of ​​index recommendation based on data features, which gets rid of the limitations of business input and paves the way for rapid automated data analysis.

[0073] Example 2:

[0074] Embodiments of the present invention also provide an indexing and recommendation device based on data features, such as... Figure 4 As shown, it includes:

[0075] The aggregation group building module is used to obtain multiple dimensions from the user's query history data and build aggregation groups based on the dimensions;

[0076] An initial index building module is used to create an initial index based on the aggregation group, wherein the initial index is divided into multiple levels according to the dimension combination;

[0077] The candidate index set determination module is used to pre-screen the initial indexes at each level based on a sampling algorithm to obtain a candidate index set.

[0078] The recommended index determination module is used to search for the subset of indexes with the smallest generation value from the candidate index set using a genetic algorithm or a greedy algorithm, and use it as the recommended index.

[0079] In one embodiment, the apparatus further includes:

[0080] The index cardinality determination module is used to calculate the cardinality of each dimension in the candidate index set, as well as the cardinality of each index, using an inexact deduplication algorithm;

[0081] The index row average determination module is used to estimate the average row size of each index in the candidate index set using a sampling algorithm.

[0082] In one embodiment, the candidate index set determination module includes:

[0083] The cosine distance calculation unit is used to calculate the cosine distance between all pairs of initial indices at each level, where all initial indices do not include single-dimensional indices and full-dimensional indices.

[0084] The candidate index determination unit is used to select the initial index as a candidate index if the cosine distance is less than a preset threshold.

[0085] Example 3:

[0086] Embodiments of the present invention also provide an index recommendation algorithm based on data features. Based on this algorithm, when the OLAP engine performs pre-computation, it can automatically select the indexes that need to be pre-computed according to data features, thereby reducing the storage and computational overhead of pre-computation.

[0087] This algorithm consists of three parts: index cost modeling, data feature collection, and optimal index search. The following sections will describe these three parts in detail.

[0088] Index cost modeling comprehensively evaluates the value of each index based on its storage cost and computational overhead. Index storage cost is the estimated number of bytes occupied by the index, while the computational overhead refers to the time required to build the index. Since the constructed index serves queries, the storage cost of the index can be viewed from the query perspective: 1) the breadth of queries covered by the index, and 2) the query speedup ratio. The breadth of queries covered by the index refers to how many types of queries the constructed index can answer, while the query speedup ratio is the ratio of the time spent on indexed queries to that of non-indexed queries. For example, suppose there is a clustered index Index1 containing dimensions A, B, and C, and measures M1, M2, and M3. From the perspective of query breadth, any query that queries any combination of these dimensions and measures can be covered by this index. In this example, suppose the time taken to hit the index is t1, and the time taken to miss the index is t2. The speedup ratio is (t2-t1) / t2. When t1>t2, it is a negative speedup, meaning it is actually slower than the case where the index is not hit.

[0089] Data feature collection gathers statistical information including column types referenced by indexes, cardinality, and the average size of a row. In many big data systems, the original data volume can be very large, so inexact deduplication (HLL) methods are used to estimate it, while the average row size is estimated using sampling methods.

[0090] Optimal index search involves searching for the optimal set of indexes, and it is performed in the following steps:

[0091] First, relevant dimensional information is extracted based on the user's past query history.

[0092] Secondly, an initial index is created based on the aggregation group. Considering that too many dimensions will lead to the dimensionality explosion problem when generating the index, a sampling algorithm is used to pre-filter within each layer during the search, thereby obtaining a smaller set of indexes.

[0093] Finally, within this small set of indexes, a cost model is defined, and a genetic or greedy algorithm is used to search for a subset of indexes with the lowest cost, which is then used as the final recommended index.

[0094] The approach to index cost modeling in this application focuses on evaluating the storage cost of the index and the query benefits it brings.

[0095] The readable storage medium can be a computer storage medium or a communication medium. A communication medium includes any medium that facilitates the transfer of computer programs from one location to another. A computer storage medium can be any available medium accessible to a general-purpose or special-purpose computer. For example, a readable storage medium is coupled to a processor, enabling the processor to read information from and write information to the readable storage medium. Of course, the readable storage medium can also be a component of the processor. The processor and the readable storage medium can reside in an Application-Specific Integrated Circuit (ASIC). Alternatively, the ASIC can be located in a user device. Of course, the processor and the readable storage medium can also exist as discrete components in a communication device. The readable storage medium can be a read-only memory (ROM), random access memory (RAM), CD-ROM, magnetic tape, floppy disk, and optical data storage device, etc.

[0096] The present invention also provides a program product including executable instructions stored in a readable storage medium. At least one processor of the device can read the executable instructions from the readable storage medium, and the at least one processor executes the executable instructions to cause the device to implement the methods provided in the various embodiments described above.

[0097] In the above-described terminal or server embodiments, it should be understood that the processor can be a Central Processing Unit (CPU), or other general-purpose processors, digital signal processors (DSPs), etc. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the method disclosed in this invention can be directly manifested as being executed by a hardware processor, or executed by a combination of hardware and software modules within the processor.

[0098] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data feature-based indexing and recommendation method, characterized in that, include: Multiple dimensions are obtained from the user's query history data, and aggregation groups are constructed based on the dimensions; An initial index is created based on the aggregation group, and the initial index is divided into multiple levels according to the combination of dimensions; The initial indexes at each level are pre-screened using a sampling algorithm to obtain a candidate index set, including: calculating the cosine distance between all pairs of initial indexes at each level, where all initial indexes do not include single-dimensional indexes and full-dimensional indexes; if the cosine distance is less than a preset threshold, the initial index is used as a candidate index. Data features are extracted from all indexes in the candidate index set, including the column type referenced by the index, cardinality, and average row size. A non-exact deduplication algorithm is used to calculate the cardinality of each dimension in the candidate index set, as well as the cardinality of each index. A sampling algorithm is used to estimate the average row size of each index in the candidate index set. The algorithm uses a genetic algorithm or a greedy algorithm to search for the index subset with the minimum generation value from the candidate index set, and uses it as the recommended index. This includes optimizing the candidate index set according to a cost function to obtain the index subset. The cost function is as follows: f(x) = αg(x) + βh(x), where g(x) is the storage cost of the index, which is determined by the cardinality of the index and the average size of the rows, h(x) is the query cost caused by missing indexes, and α and β are cost coefficients.

2. A data feature-based indexing and recommendation device, characterized in that, include: The aggregation group building module is used to obtain multiple dimensions from the user's query history data and build aggregation groups based on the dimensions; An initial index building module is used to create an initial index based on the aggregation group, wherein the initial index is divided into multiple levels according to the dimension combination; The candidate index set determination module is used to pre-screen the initial indexes of each level based on a sampling algorithm to obtain a candidate index set, including: calculating the cosine distance between all pairs of initial indexes at each level, wherein all initial indexes do not include single-dimensional indexes and full-dimensional indexes; if the cosine distance is less than a preset threshold, then the initial index is used as a candidate index; The index cardinality determination module is used to calculate the cardinality of each dimension in the candidate index set, as well as the cardinality of each index, using an inexact deduplication algorithm; The index row average determination module is used to estimate the average row size of each index in the candidate index set using a sampling algorithm; The recommended index determination module is used to search for the index subset with the minimum generation value from the candidate index set using a genetic algorithm or a greedy algorithm, and use it as the recommended index. The module includes: optimizing the candidate index set according to a cost function to obtain the index subset. The cost function is as follows: f(x) = αg(x) + βh(x), where g(x) is the storage cost of the index, which is determined by the cardinality of the index and the average size of the rows, h(x) is the query cost caused by missing indexes, and α and β are cost coefficients.

3. A computer device comprising a memory and a processor, wherein the memory stores a computer program executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method of claim 1.

4. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by the processor, it implements the steps of the method of claim 1.

Images