Method and device for automatically optimizing ordering keys of OLAP database

Abstract

The invention discloses a method and device for automatically optimizing ordering keys of an OLAP database, and relates to the technical field of database query, and the method comprises the following steps: obtaining a query log, table metadata and a cluster state of the OLAP database; performing feature analysis on the query log, the table metadata and the cluster state to obtain a plurality of large-class features of the field; each large-class feature comprises a plurality of small-class features; performing quantitative scoring on each field according to each large-class feature and each small-class feature of the field, obtaining a dynamic score of each field, performing sorting, and selecting a plurality of fields with the maximum scores as candidate sorting keys; and determining whether to adjust the current sorting key to the candidate sorting key according to the dynamic score of the candidate sorting key and the dynamic score of the current sorting key. According to the method and the device, the sorting key can be automatically changed under the condition that the field with high query frequency is changed or the field is newly added due to service change, so that the query efficiency of the OLAP data system is improved.

Description

Technical Field

[0001] This application relates to the field of database query technology, and in particular to a method and apparatus for automatically optimizing sort keys in an OLAP database. Background Technology

[0002] With the rapid development of cloud services and lightweight architectures, OLAP databases (such as ClickHouse, StarRocks, and Doris) are gradually replacing traditional Online Transaction Databases (OLTP) and becoming the mainstream choice for multidimensional data analysis. The sort key in an OLAP database is one of the core factors determining query performance, even more important than the index design in traditional OLTP databases. Setting high-query, low-resource fields as the sort key can significantly improve query performance. For OLAP data systems with "large-scale data aggregation and high-frequency filtering queries" as their core scenarios, the design of the sort key directly affects query latency, resource consumption, and system scalability. If business changes cause changes or additions to frequently queried fields, the OLAP data table will not automatically change the sort key, leading to lower query performance and slowing down the overall query efficiency of the OLAP data system. Summary of the Invention

[0003] The purpose of this application is to provide a method and apparatus for automatically optimizing the sort key in an OLAP database. This method can automatically change the sort key when business changes cause changes in frequently queried fields or the addition of new fields, thereby improving the query efficiency of the OLAP data system.

[0004] To achieve the above objectives, this application provides the following solution: In a first aspect, this application provides a method for automatically optimizing the sort key in an OLAP database, including: Retrieve query logs, table metadata, and cluster status from the OLAP database; Feature analysis is performed on query logs, table metadata, and cluster status to derive several major categories of field features; each major category includes multiple subcategories of features. Each field is quantitatively scored based on its major and minor features to obtain a dynamic score for each field. Sort the dynamic scores of each field and select the fields with the highest scores as candidate sorting keys; Whether to adjust the current sort key to a candidate sort key is determined based on the dynamic scores of the candidate sort keys and the dynamic scores of the current sort key.

[0005] Secondly, this application provides an apparatus for automatically optimizing the sort key of an OLAP database, comprising: The data acquisition module is used to acquire query logs, table metadata, and cluster status of the OLAP database. The feature analysis module is used to perform feature analysis on query logs, table data, and cluster status to derive several major categories of features for the fields; each major category of features includes multiple subcategories of features. The quantitative scoring module is used to quantitatively score each field based on its major and minor features, and to obtain a dynamic score for each field. The filter sort key module is used to sort the dynamic scores of each field and select the fields with the highest dynamic scores as candidate sort keys. The sorting key adjustment module is used to determine whether to adjust the current sorting key to a candidate sorting key based on the dynamic scores of the candidate sorting keys and the dynamic score of the current sorting key.

[0006] Thirdly, this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the above-described method for automatically optimizing sort keys in an OLAP database.

[0007] According to the specific embodiments provided in this application, the following technical effects are disclosed: This application provides a method and apparatus for automatically optimizing sort keys in an OLAP database. The method includes: acquiring query logs, table metadata, and cluster status of the OLAP database; performing feature analysis on the query logs, table metadata, and cluster status to derive several major categories of field features; each major category of feature includes multiple subcategories of features; quantifying and scoring each field based on its major and subcategories of features to obtain a dynamic score for each field; sorting the dynamic scores of each field and selecting the fields with the highest dynamic scores as candidate sort keys; and determining whether to adjust the current sort key to a candidate sort key based on the dynamic scores of the candidate sort keys and the current sort key. This application, by collecting query logs, table metadata, and cluster status data from the OLAP database and performing periodic analysis, scoring, and dynamic adjustment of the table's sort key, ensures that OLAP data table queries are always in a high-performance state. It can automatically change the sort key when business changes cause changes or additions to frequently queried fields, thereby improving the query efficiency of the OLAP data system. Attached Figure Description

[0008] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0009] Figure 1 A flowchart illustrating a method for automatically optimizing sort keys in an OLAP database, provided as an embodiment of this application; Figure 2 A schematic diagram illustrating the technical concept of a method for automatically optimizing sort keys in an OLAP database according to an embodiment of this application; Figure 3 A schematic diagram of the functional modules of a strategy device for automatically optimizing sort keys in an OLAP database, provided in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of a computer device provided in an embodiment of this application. Detailed Implementation

[0010] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0011] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, the application will be further described in detail below with reference to the accompanying drawings and specific embodiments.

[0012] In one exemplary embodiment, such as Figure 1 and Figure 2 As shown, a method for automatically optimizing the sort key in an OLAP database is provided. This method relates to a data table sort key reordering technique for Online Analytical Processing (OLAP) databases, and is particularly suitable for query performance optimization of OLAP databases in cloud service environments. The method is executed by a computer device and includes steps 101 to 105.

[0013] Step 101: Obtain the query logs, table metadata, and cluster status of the OLAP database.

[0014] Deploy the data collection service on the server corresponding to the OLAP database. Collect operational data from the OLAP database using a hybrid real-time and offline approach, covering three types of data: query logs, table metadata, and cluster status, to provide a foundation for subsequent quantitative calculations, as shown in Table 1.

[0015] Table 1. Operational data of the OLAP database

[0016] As an example, the query logs for the `dwd_orders` table show that `WHERE dt=? AND channel=?` accounts for 40% of queries, `GROUP BY dt` accounts for 30%, and the average latency requirement is <2 seconds. Here, the question mark represents the variable value of the entity, and the value of the question mark is replaceable. For example, `WHERE dt='2024-01-01' AND channel='other'`.

[0017] Table metadata: dt (date, cardinality 365), channel (channel, cardinality 5), user_id (cardinality 100 million), partitioned by dt, data volume 10TB.

[0018] Cluster status: CPU / IO utilization is 60%, and the order table storage usage is 10TB.

[0019] Step 102 involves performing feature analysis on the query logs, table metadata, and cluster status to derive several major categories of field features; each major category includes multiple subcategories of features. As an example, the collected runtime data is divided into four major categories of features, as shown in Table 2.

[0020] Table 2 Feature Analysis Results

[0021] Because the number of original features is too large, resulting in high feature dimensionality, and some features may be redundant (e.g., the average storage length of a field is highly correlated with the compression rate), it is necessary to extract 3-5 latent factors from the original features. Each latent factor represents a core feature category. As shown in Table 2 above, the original 10 or so features are reduced to four major latent factors (i.e., four categories) based on feature categories: query features (F1), data characteristics (F2), cost resources (F3), and engine adaptation (F4).

[0022] Step 103: Quantify and score each field based on its major category features and sub-category features to obtain a dynamic score for each field.

[0023] Step 104: Sort the dynamic scores of each field and select the fields with the highest dynamic scores as candidate sorting keys.

[0024] Filter fields in descending order of dynamic score (e.g., keep the top 5). For example, if the 5th and 6th ranked fields have the same dynamic score, prioritize the field with the higher F1 score. Combine fields according to the "left prefix priority" rule (prioritize fields with higher F1 scores, as F1 represents query relevance), and ensure that the number of fields is less than or equal to the engine limit (e.g., 4). Exclude fields that are duplicates of the partition key (the F2 latent factor already includes partition synergy, so the score will be automatically reduced).

[0025] Step 105: Determine whether to adjust the current sort key to a candidate sort key based on the dynamic scores of the candidate sort keys and the dynamic scores of the current sort key.

[0026] By implementing steps 101 to 105 above, this application performs feature analysis on the OLAP database itself and employs a dynamic scheduling strategy aimed at ensuring query performance to automatically optimize the OLAP database sorting key. Compared with existing technologies, this application has the following advantages: (1) Improve resource utilization: By monitoring in real time, avoid the misuse or idleness of physical query resources, and improve the query performance of OLAP database.

[0027] (2) Multiple measurement factors: In addition to considering the frequency of field queries, the physical attributes of the data and the update frequency are also included in the dynamic adjustment algorithm to ensure the accuracy of adjusting the sorting key.

[0028] (3) High degree of automation: Reduces manual configuration costs and allows for permanent use after one-time access.

[0029] (4) High versatility: It is applicable to all popular OLAP databases on the market and is easy to integrate into existing OLAP data systems.

[0030] In another exemplary embodiment of this application, in step 103, query patterns (such as the query frequency of a field) often change over time (such as a surge in the query frequency of "Promotion ID" during e-commerce promotions), and simple static scoring cannot capture this temporal characteristic. Therefore, dynamic quantization is required. Dynamic score = static score (current feature value) × trend coefficient. This avoids overreacting to "short-term hot" features (such as a surge in queries caused by temporary promotions) and prioritizes supporting "long-term stable" high-frequency features. Specifically, in step 103, each field is quantitatively scored according to its major category features and sub-category features to obtain a score for each field, specifically including: (a1) For each field, calculate the static score of each field based on the major category features and sub-category features corresponding to the field.

[0031] (b1) For each field, the static scores of the step field at several time steps are input into the LSTM prediction model to obtain the predicted values ​​of major features in the future preset time period.

[0032] (c1) Calculate the trend coefficient of each major category of features based on the predicted values ​​of each major category of features within the future preset time period.

[0033] (d1) For each field, the dynamic score of each field is calculated based on the static score of the field at several time steps and the trend coefficient of each major category feature.

[0034] In another exemplary embodiment of this application, step (a1) involves calculating the static score of each field based on the major category features and minor category features corresponding to the field, specifically including: (a2) Calculate the correlation coefficient matrix corresponding to each sub-category feature based on the numerical values ​​of each sub-category feature corresponding to the field.

[0035] Each subclass of features belonging to the same major category corresponds to a correlation coefficient matrix. For example, for latent factor F1, which includes 4 subclasses of features, its correlation coefficient matrix is ​​a 4×4 matrix. For latent factor F2, which includes 5 subclasses of features, its correlation coefficient matrix is ​​a 5×5 matrix.

[0036] (b2) Perform eigenvalue decomposition on the correlation coefficient matrix and construct the factor loading matrix based on the decomposed eigenvalues ​​and eigenvectors; the elements of the factor loading matrix are the loadings of each subclass feature and each major class feature.

[0037] For each latent factor, the correlation coefficient matrix is ​​decomposed into eigenvalues ​​to obtain the corresponding eigenvalues ​​λ and eigenvectors v. Based on this, the corresponding elements in the factor loading matrix are calculated. The factor loading matrix is ​​a 14×4 matrix. In the factor loading matrix, only the elements corresponding to the features of their own subclasses within each major class have values; all others are 0.

[0038] (c2) Calculate the covariance matrix corresponding to each sub-category feature based on the numerical values ​​of each sub-category feature corresponding to the field.

[0039] (d2) Calculate the regression coefficient matrix of the field based on the factor loading matrix and covariance matrix of the field.

[0040] Among them, the regression coefficient matrix The calculation formula is: In the formula, Represents the factor loading matrix; Let represent the covariance matrix.

[0041] (e2) Calculate the static score of the field based on the regression coefficient matrix and factor loading matrix of the field.

[0042] Among them, static score The calculation formula is: .

[0043] As an example, let's illustrate the dynamic scoring process for each field using three subcategories of features: assume there is only one day in the time step. See Table 3 for details.

[0044] Table 3 Examples of three subcategories of features

[0045] When calculating the dynamic score, the static score must first be calculated. The static score is derived from the regression coefficient matrix and the factor loading matrix. The factor loading matrix is ​​calculated first. Definition: Elements of the factor loading matrix. = The Pearson correlation coefficient between the i-th original feature and the j-th latent factor reflects "the degree to which the original feature can be explained by the latent factor". Original features refer to subclass features. For example, "the loading of prefix query frequency (X1) and F1 (query relevance factor) = 0.92" indicates that the two are highly positively correlated.

[0046] The calculation process for the elements of the factor loading matrix is ​​as follows: Calculate the correlation coefficient matrix R of the above three subcategories of features.

[0047] R=[[1.00, 0.92, 0.88], [0.92, 1.00, 0.85], [0.88, 0.85, 1.00]] Where [1.00, 0.92, 0.88] represent the correlation coefficients between X1 and X2, and between X1 and X3; [0.92, 1.00, 0.85] represent the correlation coefficients between X2 and X1, X2 and X2, and X2 and X3; [0.88, 0.85, 1.00] represents the correlation coefficients between X3 and X1, X3 and X2, and X3 and X3.

[0048] The correlation coefficients between X1 and X2 and X3 are all >0.85, indicating that the three are highly correlated and suitable for aggregation into a single latent factor (F1 = query relevance factor).

[0049] Factor loadings The essence is "eigenvector × square root of eigenvalue", so it is necessary to first perform eigenvalue decomposition on the correlation coefficient matrix R to obtain the eigenvalue λ and eigenvector v.

[0050] Eigenvalue λ: reflects the variance contribution of the corresponding principal component (latent factor) (the larger the value, the more important the factor). Feature vector v: reflects the direction of the principal components (i.e., the weights of the original features on the principal components).

[0051] The correlation coefficient matrix R was subjected to eigenvalue decomposition (using Python's numpy and scipy library tools), and the eigenvalue decomposition results are shown in Table 4.

[0052] Table 4. Eigenvalue decomposition results of the correlation coefficient matrix R The eigenvalue of latent factor F1 is 2.72, and its proportion of the total variance is approximately 91% (2.72 / (2.72+0.25+0.03)). This indicates that F1 can explain 91% of the original eigenvariant variance and is the core latent factor.

[0053] Elements of the factor loading matrix The calculation formula is: in, The loading of the i-th original feature and the j-th latent factor; is the feature vector element of the i-th original feature on the j-th latent factor; It is the square root of the eigenvalue of the j-th latent factor (standardized factor variance, ensuring factor variance = 1).

[0054] Calculate the loading of latent factor F1 using the prefix query frequency X1: The eigenvectors of X1 in F1 =0.58; Eigenvalues ​​of F1 =2.72, ; Therefore, the loads of X1 and F1 =0.58×1.65≈0.96.

[0055] Similarly, calculate the loads of X2, X3, and F1: Loads of X2 and F1 =0.57×1.65≈0.94; Loads of X3 and F1 =0.58×1.65≈0.96.

[0056] The latent factor F1 (eigenvalue > 1, variance contribution 91%) and the loadings of latent factor F1 with X1, X2, and X3 are shown in Table 5 below.

[0057] Table 5 Loadings of latent factor F1 with X1, X2, and X3

[0058] Next, calculate the regression coefficient matrix B. Matrix B is a 14-row × 4-column matrix, with each element... "This represents the weight of the i-th original feature in relation to the score of the j-th latent factor." Calculating the regression coefficient matrix B also requires the covariance matrix S of each sub-class feature. The covariance matrix S is a 14×14 matrix reflecting the correlation between sub-class features. First, calculate the mean of each sub-class feature X → then calculate the covariance between any two sub-class features (including the variance of the sub-class feature itself) → finally arrange them in matrix structure. The covariance matrix S improves the accuracy of the regression coefficient matrix B by weighting the fluctuations and correlations of the sub-class features themselves.

[0059] After the features of each subclass are standardized in the range [0, 1], the factor loading matrix and regression coefficient matrix are calculated through the above process, and then the static score is calculated.

[0060] As an example, the sub-category features are (X1 = prefix query frequency, X2 = aggregation field occurrence frequency), and the loading and regression coefficient of each sub-category feature with query feature F1 should be calculated.

[0061] Λ=[[0.89],[0.85]]; Where [0.89] represents the load of X1 on F1 (the two are strongly correlated), and [0.85] represents the load of X2 on F1 (the two are strongly correlated).

[0062] Substituting into the formula for calculating the regression coefficient matrix B, we get: B = [[0.547], [0.603]]; Where [0.547] represents the regression coefficient of X1 on F1; and [0.603] represents the regression coefficient of X2 on F1.

[0063] After obtaining the regression coefficient matrix B, multiply it by the standardized factor loading matrix Λ to obtain the static score. The static score for one time step is a 14×4 matrix. If there are 30 time steps (days), then there are 30 static scores.

[0064] By inputting the static score sequence corresponding to each time step into the LSTM prediction model, the factor trend (the predicted value of the four potential factors for the next 7 days) can be predicted. This is used to determine the "short-term importance change" of the field, that is, to determine whether the field is about to become a high-frequency query field.

[0065] The trend coefficient is calculated using the predicted values ​​of four potential factors over a predetermined number of days. The calculation formula is as follows: .

[0066] Each latent factor corresponds to a trend coefficient. The static score of a time step is multiplied by the trend coefficient (for example, the first column of the matrix corresponding to the static score is multiplied by the trend coefficient of F1, the second column by the trend coefficient of F2, the third column by the trend coefficient of F3, and the fourth column by the trend coefficient of F4). This yields the comprehensive score of the field at a time step. The comprehensive scores of each time step are then added together. Based on the elements of each column of the added matrix (a 14×4 matrix) (e.g., by summation), the F1 score, F2 score, F3 score, and F4 score of the field are obtained. Finally, the final dynamic score is obtained based on the F1 score, F2 score, F3 score, and F4 score of the field.

[0067] In another exemplary embodiment of this application, step 105, determining whether to adjust the current sorting key to a candidate sorting key based on the dynamic score of the candidate sorting key and the dynamic score of the current sorting key, specifically includes: (a3) Determine whether the score of the candidate sorting key is greater than the preset score (e.g., 7 points, which can be set according to business needs) to obtain the first judgment result.

[0068] (b3) Determine whether the score of the current sorting key is less than the preset percentage of the score of the candidate sorting key (e.g., 80%, to ensure that the adjustment has a significant benefit) and obtain the second judgment result.

[0069] (c3) Determine whether the ratio of query benefit to query cost is greater than a preset ratio (e.g., 1.5, which can be set according to business needs) after adjusting the candidate sort key to the current sort key, and obtain the third judgment result.

[0070] Query benefits refer to the estimated improvement in query performance; query costs refer to the consumption of query resources. This step helps avoid operations with "small benefits but high costs."

[0071] (d3) If the first judgment result, the second judgment result and the third judgment result are all yes, then the current sorting key is adjusted to the candidate sorting key.

[0072] As an example: if the current sort key is user_id with a dynamic score of 3.2, the candidate sort key is channel with a dynamic score of 7.66, and the ratio of query benefit to query cost is 2.3 (>1.5), then the current sort key will be adjusted.

[0073] In another exemplary embodiment of this application, the method for automatically optimizing the sort key in an OLAP database further includes: When the current sort key is changed to a candidate sort key, for partitioned tables, cold partitions (such as data from 90 days ago) are adjusted first based on the candidate sort key, and then hot partitions (such as data from the last 30 days) are adjusted during off-peak periods. For non-partitioned tables, data re-sorting is performed using a "double-write temporary table" mode based on the candidate sort key. The "double-write temporary table" mode means creating a new temporary table with a new sort key, synchronizing incremental data, and then atomically switching the table name after completion.

[0074] In another exemplary embodiment of this application, the method for automatically optimizing the sort key in an OLAP database further includes: verifying the effect of adjusting the sort key. Specifically, after reordering data based on the candidate sort key, the core query latency is monitored; it is determined whether the core query latency reaches a preset standard; if not, the operation of using the candidate sort key as the current sort key is revoked, and the system dynamically rolls back to the original sort key.

[0075] The following example illustrates the automatic optimization process of the sorting key in this application: Table: dwd_orders (partitioned by dt, current sort key is user_id, high query latency).

[0076] New scenario: The business has added "refined channel operation", and the frequency of channel queries is gradually increasing, but there are daily peak fluctuations between 10:00 and 12:00.

[0077] The sort key optimization process is as follows: Latent factor analysis: F1 (query relevance), F2 (data fit), and F3 (cost efficiency) were extracted. The F1 score of channel (0.8) was much higher than that of user_id (0.3).

[0078] LSTM prediction: F1 trend coefficient of channel = 1.3 (the peak of queries will be more significant in the next 7 days), F2 trend coefficient = 1.0 (stable at low base).

[0079] Dynamic scores: channel scored 7.56 points, user_id scored 3.2 points, triggering a sorting key adjustment.

[0080] Execution results: After changing the sort key to channel, the query latency during peak hours decreased from 10 seconds to 1.5 seconds, and the adjustment was performed during the low-load period predicted by LSTM, with no significant fluctuation in cluster load.

[0081] This application also provides an application scenario in which the above-described method for automatically optimizing sort keys in OLAP databases is applied. Specifically, the method for automatically optimizing sort keys in OLAP databases provided in this embodiment can be applied in an OLAP database sort key optimization scenario. This scenario includes a data acquisition stage and an optimization stage; the data acquisition stage is used to collect query logs, table data, and cluster status of the OLAP database; the optimization stage is used to determine candidate sort keys based on the collected data and to determine whether to trigger sort key adjustment. The method for automatically optimizing sort keys in OLAP databases provided in this embodiment belongs to the optimization stage.

[0082] Based on the same inventive concept, this application also provides an apparatus for automatically optimizing the sorting key of an OLAP database to implement the method for automatically optimizing the sorting key of an OLAP database as described above. The solution provided by this apparatus is similar to the solution described in the above method. Therefore, the specific limitations of one or more apparatus embodiments for automatically optimizing the sorting key of an OLAP database provided below can be found in the limitations of the method for automatically optimizing the sorting key of an OLAP database described above, and will not be repeated here.

[0083] In one exemplary embodiment, such as Figure 3As shown, an apparatus for automatically optimizing the sort key in an OLAP database includes: The data acquisition module M1 is used to acquire query logs, table metadata, and cluster status of the OLAP database.

[0084] The feature analysis module M2 is used to perform feature analysis on query logs, table data, and cluster status to derive several major categories of features for the fields; each major category of features includes multiple subcategories of features.

[0085] The quantitative scoring module M3 is used to quantitatively score each field based on its major and minor features, and to obtain a dynamic score for each field.

[0086] The filter sort key module M4 is used to sort the scores of each field and select the fields with the highest scores as candidate sort keys.

[0087] The sorting key adjustment module M5 is used to determine whether to adjust the current sorting key to a candidate sorting key based on the scores of the candidate sorting keys and the score of the current sorting key.

[0088] In one exemplary embodiment, a computer device is provided, which may be a server or a terminal, and its internal structure diagram may be as follows. Figure 4 As shown, this computer device includes a processor, memory, input / output (I / O) interfaces, and a communication interface. The processor, memory, and I / O interfaces are connected via a system bus, and the communication interface is also connected to the system bus via the I / O interfaces. The processor provides computational and control capabilities. The memory includes non-volatile storage media and internal memory. The non-volatile storage media stores the operating system, computer programs, and a database. The internal memory provides the environment for the operation of the operating system and computer programs stored in the non-volatile storage media. The database stores the execution flow and results of automatic sort key optimization for OLAP databases. The I / O interfaces are used for exchanging information between the processor and external devices. The communication interface is used for communication with external terminals via a network connection. When executed by the processor, the computer program implements a method for automatically optimizing sort keys in an OLAP database.

[0089] Those skilled in the art will understand that Figure 4The structures shown are merely block diagrams of some structures related to the present application and do not constitute a limitation on the computer device to which the present application is applied. Specific computer devices may include more or fewer components than shown in the figures, or combine certain components, or have different component arrangements. In an exemplary embodiment, a computer device is provided, including a memory and a processor. The memory stores a computer program, and the processor executes the computer program to implement the steps in the above-described method embodiments.

[0090] In one exemplary embodiment, a computer-readable storage medium is provided storing a computer program that, when executed by a processor, implements the steps in the above-described method embodiments.

[0091] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of the relevant data must comply with relevant regulations.

[0092] Those skilled in the art will understand that all or part of the processes in the above embodiments can be implemented by a computer program instructing related hardware. The computer program can be stored in a non-volatile computer-readable storage medium, and when executed, it can include the processes of the embodiments described above. Any references to memory, databases, or other media used in the embodiments provided in this application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM).

[0093] The databases involved in the embodiments provided in this application may include at least one type of relational database and non-relational database. Non-relational databases may include, but are not limited to, blockchain-based distributed databases. The processors involved in the embodiments provided in this application may be general-purpose processors, central processing units, graphics processing units, digital signal processors, programmable logic devices, quantum computing-based data processing logic devices, etc., and are not limited to these.

[0094] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0095] This document uses specific examples to illustrate the principles and implementation methods of this application. The descriptions of the above embodiments are only for the purpose of helping to understand the methods and core ideas of this application. Furthermore, those skilled in the art will recognize that, based on the ideas of this application, there will be changes in the specific implementation methods and application scope. Therefore, the content of this specification should not be construed as a limitation of this application.

Claims

1. A method for automatically optimizing the sort key in an OLAP database, characterized in that, include: Retrieve query logs, table metadata, and cluster status from the OLAP database; Feature analysis is performed on query logs, table metadata, and cluster status to derive several major categories of field features; each major category includes multiple subcategories of features. Each field is quantitatively scored based on its major and minor features to obtain a dynamic score for each field. Sort the dynamic scores of each field and select the fields with the highest dynamic scores as candidate sorting keys; Whether to adjust the current sort key to a candidate sort key is determined based on the dynamic scores of the candidate sort keys and the dynamic scores of the current sort key.

2. The method for automatically optimizing the sort key in an OLAP database according to claim 1, characterized in that, Each field is quantitatively scored based on its major and minor feature categories, resulting in a dynamic score for each field. Specifically, this includes: For each field, calculate the static score for each field based on the major category features and sub-category features corresponding to the field; For each field, the static scores of the field at several time steps are input into the LSTM prediction model to obtain the predicted values ​​of major features in the future preset time period. The trend coefficient of each major category of features is calculated based on the predicted value of each major category of features within a preset future time period. For each field, the dynamic score is calculated based on the static scores of the field at several time steps and the trend coefficient of each major category feature.

3. The method for automatically optimizing the sort key in an OLAP database according to claim 2, characterized in that, The static score for each field is calculated based on the major category features and subcategory features corresponding to the field, specifically including: Calculate the correlation coefficient matrix corresponding to each sub-category feature based on the numerical values ​​of each sub-category feature corresponding to the field; The correlation coefficient matrix is ​​subjected to eigenvalue decomposition, and a factor loading matrix is ​​constructed based on the decomposed eigenvalues ​​and eigenvectors; the elements of the factor loading matrix are the loadings of each subclass feature and each major class feature. Calculate the covariance matrix corresponding to each sub-category feature based on the numerical values ​​of each sub-category feature corresponding to the field; Calculate the regression coefficient matrix of the field based on the factor loading matrix and covariance matrix of the field; The static score of a field is calculated based on its regression coefficient matrix and factor loading matrix.

4. The method for automatically optimizing the sort key in an OLAP database according to claim 3, characterized in that, The formula for calculating the static score is: in, In the formula, This indicates the static score of the field; Represents the regression coefficient matrix; Represents the factor loading matrix; Let represent the covariance matrix.

5. The method for automatically optimizing the sort key in an OLAP database according to claim 1, characterized in that, The decision to adjust the current sorting key to a candidate sorting key is made based on the dynamic scores of the candidate sorting keys and the dynamic scores of the current sorting key. This includes: Determine whether the dynamic score of the candidate sorting key is greater than the preset score to obtain the first judgment result; Determine whether the dynamic score of the current sorting key is less than a preset percentage of the dynamic scores of the candidate sorting keys to obtain a second judgment result; Determine whether the ratio of query benefit to query cost is greater than a preset ratio after adjusting the candidate sort key to the current sort key, and obtain the third judgment result; If the first, second, and third judgment results are all yes, then the current sorting key is adjusted to a candidate sorting key.

6. The method for automatically optimizing the sort key in an OLAP database according to claim 5, characterized in that, The method for automatically optimizing sort keys in OLAP databases also includes: When the current sort key is changed to a candidate sort key, for partitioned tables, the cold partitions are adjusted first and then the hot partitions according to the candidate sort key. For non-partitioned tables, the data is re-sorted using the "double-write temporary table" mode according to the candidate sort key.

7. The method for automatically optimizing the sort key in an OLAP database according to claim 6, characterized in that, The method for automatically optimizing sort keys in OLAP databases also includes: When data is reordered based on the candidate sort key, the core query latency is monitored. Determine if the core query delay has reached the preset standard; if not, cancel the operation of using the candidate sort key as the current sort key.

8. The method for automatically optimizing the sort key in an OLAP database according to claim 1, characterized in that, The major characteristics of fields include: query characteristics, data characteristics, cost resources, and engine adaptation; The subcategories of query characteristics include: prefix query frequency, aggregation field occurrence frequency, high SLA query match count, and query coverage row percentage. Prefix query frequency refers to the frequency with which a field is used as a prefix in the query's WHERE clause; aggregation field occurrence frequency refers to the frequency with which a field is used as a GROUP BY / ORDER BY field; high SLA query match count refers to the number of times a field is relied upon by core queries with high latency requirements; high latency requirements refer to latency times less than a preset time; and query coverage row percentage refers to the proportion of rows scanned by queries containing that field out of the total number of rows in the table. The subcategories of data characteristics include: field cardinality, average daily update frequency, value distribution skewness, average field storage length, and field compression ratio. Here, field cardinality refers to the number of distinct values ​​in a field; average daily update frequency refers to the number of times a field is updated daily; value distribution skewness refers to the percentage of maximum values ​​in a field; average field storage length refers to the average number of bytes per value in a field; and field compression ratio refers to the compression ratio of a field stored column-wise. The subcategories of cost resources include: sort key adjustment time and resource utilization during adjustment; where sort key adjustment time refers to the estimated time for data reordering if this field is added as a sort key; and resource utilization during adjustment refers to the estimated cluster CPU / IO utilization during sort key adjustment. The subcategories of engine adaptation features include: sort key length compliance, partition key synergy, and index compatibility. Among them, sort key length compliance refers to whether the total length of the sort key meets the engine's limits after the field is added; partition key synergy refers to whether the field is duplicated with the partition key; and index compatibility refers to whether the field already has a bitmap index.

9. An apparatus for automatically optimizing sort keys in an OLAP database, characterized in that, include: The data acquisition module is used to acquire query logs, table metadata, and cluster status of the OLAP database. The feature analysis module is used to perform feature analysis on query logs, table data, and cluster status to derive several major categories of features for the fields; each major category of features includes multiple subcategories of features. The quantitative scoring module is used to quantitatively score each field based on its major and minor features, and to obtain a dynamic score for each field. The filter sort key module is used to sort the dynamic scores of each field and select the fields with the highest dynamic scores as candidate sort keys. The sorting key adjustment module is used to determine whether to adjust the current sorting key to a candidate sorting key based on the dynamic scores of the candidate sorting keys and the dynamic score of the current sorting key.

10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the method for automatically optimizing the sort key of an OLAP database as described in any one of claims 1-8.

Images