Data query optimization method and device, equipment storage medium and product

Abstract

The invention discloses a data query optimization method and device, an equipment storage medium and a product, and is applied to the technical field of data processing. Index definition configuration information is acquired, data associated with at least one index is extracted from a target data set, processing is performed according to the index definition configuration information, and a multi-dimensional index result set is generated; obtaining a plurality of historical query statements executed on the multi-dimensional index result set; the method comprises the following steps: performing query feature analysis on a historical query statement to determine initial field information for creating an index; determining index field information based on the initial field information and a preset index auditing strategy; on the basis of the index field information, one or more indexes are created for the multi-dimensional index result set, and therefore the data query efficiency can be improved.

Description

Technical Field

[0001] This application belongs to the field of data processing technology, and in particular relates to a data query optimization method, apparatus, device storage medium and product. Background Technology

[0002] With the rapid development of computer technology and the explosive growth of data, the operating environment of all industries, including the financial sector, has undergone tremendous changes. To adapt to the profound changes and challenges of the digital economy era, how to process and utilize massive amounts of data to provide decision support has become a core issue that all enterprises must learn. Among these, improving the efficiency of massive data utilization and helping users quickly and accurately obtain the data needed for decision-making from complex datasets is a critical problem that urgently needs to be solved.

[0003] In existing technologies, after acquiring source data through data acquisition tools and performing data preprocessing and processing, the datasets are stored in various databases. Technical personnel typically create indexes for each dataset in the database based on pre-defined business requirements or fixed technical design schemes. By pre-optimizing the data retrieval path through a pre-defined index structure, the data scanning scope is reduced during subsequent query operations, thereby improving data acquisition efficiency.

[0004] However, the existing datasets store an extremely large amount of data. Although indexes can reduce the scope of data scanning, they are still difficult to return query results quickly when faced with massive amounts of data. Moreover, the actual business scenarios change dynamically, and the preset indexes cannot adapt to the current business needs, ultimately leading to a decrease in data query efficiency. Summary of the Invention

[0005] This application provides a data query optimization method, apparatus, device storage medium, and product that can improve data query efficiency.

[0006] In a first aspect, embodiments of this application provide a data query optimization method, the method comprising: Obtain indicator definition configuration information, which includes indicator definition information for at least one indicator, indicator statistical method information, and dimension information for multiple dimensions; Extract data associated with at least one indicator from the target dataset, process it according to the indicator definition configuration information, and generate a multi-dimensional indicator result set; Retrieve multiple historical query statements executed on a multi-dimensional indicator result set; By parsing the query characteristics of historical query statements, the initial field information used to create the index can be determined; Based on the initial field information and the preset index audit strategy, determine the index field information; Based on the index field information, create one or more indexes for the multi-dimensional indicator result set, where each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

[0007] In some possible implementations, after creating one or more indexes for the multi-dimensional metric result set based on index field information, the following is also included: When a new query is received, the corresponding target index is matched based on the characteristics of the query, and data is queried on the data subset corresponding to the target index.

[0008] In some possible implementations, query feature parsing of historical query statements determines the initial field information used to create the index, including: Filter target historical query statements that match a preset time range; Classify target historical query statements with the same structure but different query parameters into the same query pattern; For each query pattern, obtain the target historical query statement corresponding to that query pattern, and use the number of times the target historical query statement appears as the frequency of that query pattern. For each query pattern, obtain the target historical query statement corresponding to that query pattern, and use the average execution time of the corresponding target historical query statement as the execution time of that query pattern; Extract query field information from each query pattern, and calculate the weight of the query field information based on the frequency and execution time of each query pattern. For each field, calculate the total weight of that field based on the weights calculated from the corresponding query patterns; The fields are sorted according to their total weight, and the sorted field sequence is used as the initial field information for creating the index.

[0009] In some possible implementations, the index field information is determined based on the initial field information and the preset index auditing strategy, including: Calculate the dispersion of each initial field information in the multi-dimensional index result set; The initial field information is filtered based on the dispersion, and the initial field information with a dispersion greater than or equal to the dispersion threshold is used as the target initial field information; Identify combinations of field information that appear in historical query statements; For the identified combinations of field information, the information is filtered and sorted based on the dispersion of each initial field information in each combination and the total weight, and then the composite index field information is generated. The target initial field information and the composite index field information are determined as the index field information.

[0010] In some possible implementations, after retrieving multiple historical query statements executed on the multi-dimensional metric result set, the following is also included: Cluster analysis is performed on historical query statements to aggregate historical query statements with similar query characteristics into different groups of historical query statements; For each group of historical query statements, the initial field information used to create the index is determined by parsing the query characteristics of the historical query statements within each group.

[0011] In some possible implementations, after creating one or more indexes for the multi-dimensional indicator result set based on index field information, the following are also included: Retrieve the existing set of indexes currently existing on the multi-dimensional indicator result set, and compare and analyze the indexes created for the multi-dimensional indicator result set with the existing indexes in the existing set of indexes. If the created index field combination includes the field combination of an existing index, then the created index will replace the corresponding existing index in the existing index set; If the fields in the created index overlap with the fields in an existing index, the created index and the existing index will be merged into a new composite index.

[0012] In some possible implementations, data associated with at least one indicator is extracted from the target dataset and processed according to the indicator definition configuration information to generate a multi-dimensional indicator result set, including: Based on the indicator definition configuration information, an indicator dependency graph is constructed. The indicator dependency graph stores the dependency relationships between various indicators, as well as the dependency relationships between indicators and dimension information. When a change in the metric definition configuration information is detected, the changed metric is obtained, and the dependent metrics and dependent dimension information that are dependent on the changed metric are determined from the metric dependency graph based on the changed metric. Based on dependency metrics, change metrics, and dependency dimension information, the range of data that needs to be recalculated is determined, and incremental calculations are performed on the data within the range of data that needs to be recalculated to generate a multi-dimensional metric result set.

[0013] In some possible implementations, after retrieving multiple historical query statements executed on the multi-dimensional metric result set, the following is also included: The system filters out the first historical query statements whose execution time is greater than or equal to a preset time threshold, parses the first historical query statements, identifies the aggregation operation features contained in the first historical query statements, and extracts the first target dimension information corresponding to the aggregation operation features; based on the first target dimension information, it aggregates the dimension information corresponding to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set. And / or, The process involves filtering out second historical query statements that failed to execute; parsing the second historical query statements and extracting field information from them as the second target dimension information; and based on the second target dimension information, extracting relevant data from the target dataset, processing it, and adding it to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

[0014] Secondly, embodiments of this application provide a data query optimization apparatus, the apparatus comprising: The first acquisition module is used to acquire indicator definition configuration information, which includes indicator definition information for at least one indicator, indicator statistical method information, and dimension information for multiple dimensions. The extraction module is used to extract data associated with at least one indicator from the target dataset, process it according to the indicator definition configuration information, and generate a multi-dimensional indicator result set. The second acquisition module is used to acquire multiple historical query statements executed on the multi-dimensional indicator result set; The parsing module is used to determine the initial field information for creating the index by parsing the query characteristics of historical query statements; The determination module is used to determine the index field information based on the initial field information and the preset index audit strategy; The module creates one or more indexes for a multi-dimensional indicator result set based on index field information. Each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

[0015] Thirdly, embodiments of this application provide an electronic device, the device comprising: A processor and a memory storing computer program instructions; a data query optimization method that implements any one of the above when the processor executes the computer program instructions.

[0016] Fourthly, embodiments of this application provide a computer-readable storage medium storing computer program instructions, which, when executed by a processor, implement a data query optimization method as described above.

[0017] Fifthly, embodiments of this application provide a computer program product in which the instructions are executed by the processor of an electronic device, enabling the electronic device to perform any of the above-mentioned data query optimization methods.

[0018] This application provides a data query optimization method, apparatus, device storage medium, and product. Compared to the technical problems in the prior art where designing indexes for datasets results in slow query returns for massive amounts of data and the inability of indexes to adapt to dynamically changing business needs, leading to low data query efficiency, this application first obtains indicator definition configuration information, then extracts data associated with at least one indicator from the target dataset, and processes it according to the indicator definition configuration information to generate a multi-dimensional indicator result set. This avoids recalculation for each query, reducing query time and improving query efficiency. Then, by obtaining multiple historical query statements executed on the multi-dimensional indicator result set, and by parsing the historical query statements to infer data hotspots, index field information is determined. This ensures that the recommended index field information accurately reflects business query needs and dynamically adapts to scenario changes, thus improving the accuracy and timeliness of index recommendations, ultimately improving data query efficiency. Attached Figure Description

[0019] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments of this application will be briefly introduced below. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0020] Figure 1 A flowchart illustrating a data query optimization method according to an embodiment of this application is shown; Figure 2 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown; Figure 3 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown; Figure 4 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown; Figure 5 This diagram illustrates the overall process of a data query optimization method. Figure 6 A schematic diagram of the data query optimization device provided in an embodiment of this application is shown; Figure 7 A schematic diagram of the hardware structure of the electronic device provided in an embodiment of this application is shown. Detailed Implementation

[0021] The features and exemplary embodiments of various aspects of this application will be described in detail below. To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings and specific embodiments. It should be understood that the specific embodiments described herein are only intended to explain this application and not to limit it. For those skilled in the art, this application can be implemented without some of these specific details. The following description of the embodiments is merely to provide a better understanding of this application by illustrating examples.

[0022] It should be noted that, in this document, relational terms such as "first" and "second" are used merely to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising..." does not exclude the presence of additional identical elements in the process, method, article, or apparatus that includes the element.

[0023] It should be noted that the acquisition, storage, use, and processing of data in this application embodiment all comply with the relevant provisions of national laws and regulations.

[0024] It should be noted that in the embodiments of this application, certain software, components, models and other existing solutions in the industry may be mentioned. These should be regarded as exemplary and are only intended to illustrate the feasibility of implementing the technical solution of this application. However, they do not mean that the applicant has used or necessarily used the solution.

[0025] First, let me explain the terms used in this application: Metrics: Metrics refer to various standards or statistical values ​​that can be measured in actual business scenarios but are not exhaustive. They are data that can be calculated and statistically analyzed. Every business object, subject, or business activity with a defined scope has a set of corresponding metrics to describe it.

[0026] Indicator Dimension: In mathematics, dimension refers to the number of independent parameters. It is a perspective, a multi-faceted, multi-angled, and multi-layered condition and concept for judging, explaining, evaluating, and determining a thing (mainly indicators in this article). In this article, it mainly refers to attributes that are quantitatively defined within the statistical scope of indicators.

[0027] Currently, existing technologies improve data query efficiency by designing indexes for datasets. Indexes can pre-optimize the data retrieval path, reducing the data scanning scope during subsequent query operations and thus improving data retrieval efficiency. However, existing datasets store extremely large amounts of data. While indexes can reduce the data scanning scope, they still struggle to quickly return query results when faced with massive amounts of data. Furthermore, in practical applications, application scenarios are constantly changing, and pre-set indexes may not be suitable for current business needs, resulting in persistently low data query efficiency in actual applications.

[0028] To address the problems in existing technologies, embodiments of this application provide a data query optimization method, apparatus, device storage medium, and product. By acquiring indicator definition configuration information, extracting data associated with at least one indicator from the target dataset, and processing it according to the indicator definition configuration information, a multi-dimensional indicator result set is generated. This avoids recalculation for each query, reducing query time and improving query efficiency. Furthermore, by acquiring multiple historical query statements executed on the multi-dimensional indicator result set and parsing these statements to determine index field information, the recommended index field information accurately reflects business query requirements, thus improving data query efficiency.

[0029] The following section first introduces a data query optimization method provided in the embodiments of this application.

[0030] Figure 1 This illustration shows a flowchart of a data query optimization method according to an embodiment of this application. Figure 1 As shown, the method may include the following steps: S101: Obtain indicator definition configuration information. The indicator definition configuration information includes indicator definition information for at least one indicator, indicator statistical method information, and dimension information for multiple dimensions.

[0031] In this embodiment of the application, in order to reduce the computational load when querying data, the corresponding indicator definition configuration information can be obtained in advance according to the business scenario to clarify the scope and rules of data processing. The indicator definition configuration information includes: indicator definition information, indicator statistical method information, and dimension information of multiple dimensions.

[0032] The indicator definition information can include the indicator name and indicator metric; the indicator statistical method information can include summation, average calculation, or counting; and the dimension information can include time dimension, region dimension, product dimension, etc. The specific indicator definition configuration information changes continuously based on the business application scenario.

[0033] In one example, the system can receive user-inputted metric definition configuration information in real time via a user interface, and the multi-dimensional metric result processing engine can parse this information. This multi-dimensional metric result processing engine is a system that integrates storage, processing, and service; the processing mainly includes parsing the metric definition configuration information and generating a multi-dimensional metric result set.

[0034] S102: Extract data associated with at least one indicator from the target dataset, process it according to the indicator definition configuration information, and generate a multi-dimensional indicator result set.

[0035] In this embodiment, pre-calculating the summary data can accelerate queries and avoid the need for recalculation on each query, thus avoiding wasted time and resources. Based on the acquired indicator definition configuration information, the corresponding indicator-related data is extracted from the target dataset and processed according to the indicator definition configuration information. The target dataset can be a detailed dataset storing the original data. The multi-dimensional indicator result set is a multi-dimensional cube containing multiple dimension combinations from the indicator definition configuration information. Different dimension combinations can all yield corresponding summary results within this cube.

[0036] In one example, the target dataset can be located in the original dataset based on the indicator definition configuration information. For example, the relationship between the target dataset and the target data table in the target dataset can be located in the original dataset based on the indicator measurement and dimension information in the indicator definition information.

[0037] For example, if the indicator definition explicitly states that "the indicator measure is transaction amount, and the dimensions are time and region," then it is necessary to locate the original data table containing "transaction amount," "transaction time," and "transaction region" from the database storing the raw data. If these fields are distributed across different data tables, then it is necessary to further obtain all relevant data tables and clarify the related fields and logic between the tables in order to construct a complete data processing chain.

[0038] In one example, when processing data according to the indicator definition configuration information, a unified caliber can be predefined. Based on the indicator statistical method information and the caliber, relevant data in the target dataset can be processed to generate a multi-dimensional indicator result set. The unified caliber refers to a set of standardized business rules and data filtering conditions pre-established for indicator calculation. This ensures that the calculation logic and rules for the same indicator calculated from different sources and in different scenarios remain consistent, thereby avoiding data conflicts or inconsistent query results due to rule ambiguity. For example, for the "sales revenue" indicator, the unified caliber can specify that it only includes orders in the "paid" status, thus excluding records that have been cancelled or only placed but not paid for.

[0039] In one example, business dimension labels can be added to different multi-dimensional indicator result sets to identify their respective business domains, data characteristics, or application scenarios. This allows SQL statements to quickly identify the business attributes of their target data source during parsing, thereby achieving more accurate query feature extraction and index recommendation.

[0040] S103: Retrieves multiple historical query statements executed on the result set of multi-dimensional indicators.

[0041] In this embodiment of the application, by acquiring and analyzing multiple historical query statements executed on the result set of multi-dimensional indicators, it is possible to identify frequently accessed fields and commonly used query conditions based on actual query behavior data, thereby providing a reliable data foundation for subsequent accurate recommendation of index fields and ensuring that the recommended index is adapted to the current real business scenario.

[0042] The multi-dimensional metric result set can be accessed using Structured Query Language (SQL). In one example, multiple historical query statements can be retrieved from the metric query log. In another example, a collection task can be executed periodically based on preset time nodes. For instance, by configuring a background scheduled task, historical query statements can be automatically retrieved in batches from the metric query log during daily off-peak hours.

[0043] In one example, to avoid overlooking important but infrequently queried business scenarios, after step S103, the following is also included: Cluster analysis is performed on historical query statements to group them into different historical query statement groups with similar query characteristics.

[0044] In this embodiment of the application, in order to improve the query efficiency of queries that are not frequently queried but are crucial to specific business scenarios, cluster analysis is performed on the acquired historical query statements to aggregate historical query statements with similar query characteristics into different groups of historical query statements.

[0045] In one example, historical query statements can be aggregated into different groups based on semantic similarity by calculating their similarity. The similarity can be calculated using the cosine similarity algorithm.

[0046] In another example, the combination of dimension fields, measure fields, statistical aggregation methods and filtering conditions in the historical query statements can be vector-encoded, and the vector corresponding to each historical query statement can be input into the clustering algorithm. Based on the clustering algorithm, different groups of historical query statements are obtained. The vector encoding can be one-hot encoding, label encoding, or other encoding algorithms, and the clustering algorithm can be the k-means clustering algorithm (K-Means).

[0047] For each group of historical query statements, the initial field information used to create the index is determined by parsing the query characteristics of the historical query statements within each group.

[0048] In this embodiment of the application, after aggregating each group of historical query statements, query feature parsing is performed on the historical query statements within each group to extract the field information that the group commonly depends on and that appears frequently, which is then used as the initial field information specific to that group.

[0049] In this embodiment, by performing cluster analysis on the historical query statements, historical query statements with similar query characteristics are aggregated into different historical query statement groups. For each historical query statement group, the query characteristics of the historical query statements within each historical query statement group are parsed to determine the initial field information for creating the index. This ensures that various business scenarios, regardless of their execution frequency, can be identified, thereby avoiding the situation where these low-frequency but critical scenarios cannot be indexed due to not reaching the frequency threshold when filtering based on global frequency statistics in the future.

[0050] In one example, to further improve query efficiency, after step S103, the following steps are also included: The system filters out the first historical query statements whose execution time is greater than or equal to a preset time threshold, parses the first historical query statements, identifies the aggregation operation features contained in the first historical query statements, and extracts the first target dimension information corresponding to the aggregation operation features; based on the first target dimension information, it aggregates the dimension information corresponding to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

[0051] In this embodiment, from the acquired historical query statements, the first historical query statement with an execution time greater than or equal to a preset time threshold is selected. These statements may involve complex multi-dimensional aggregation operations, requiring extensive real-time calculations during the query. Therefore, they may reflect that the dimensional division of the current multi-dimensional indicator result set is too fine-grained to directly meet the performance requirements of such complex queries. By identifying these slow queries, the problem of slow query performance caused by excessively fine-grained dimensionality can be located.

[0052] At this point, by identifying the aggregation operation features contained in the first historical query statement, the first target dimension information corresponding to the aggregation operation features can be extracted. The first target dimension information indicates the specific dimension information that needs to be rolled up and aggregated because the current result set is divided too finely. Based on the extracted first target dimension information, the data corresponding to the existing multi-dimensional indicator result set is subjected to roll-up aggregation processing to aggregate the finer-grained dimension information into coarser-grained dimension information according to the target dimension information, so as to avoid repeatedly consuming resources in subsequent similar queries.

[0053] In one example, if the aggregation operation can be a GROUP BY clause, then the first target dimension information is the sequence of fields following the GROUP BY clause.

[0054] In one example, the system can add the aggregated dimensional information to the multi-dimensional indicator result set, thereby forming a modified multi-dimensional indicator result set.

[0055] To further optimize resource utilization, in a preferred example, a frequency threshold can be set. The above aggregation operation is only triggered when the frequency of the first historical query exceeds the preset threshold, avoiding unnecessary aggregation operations for occasional slow queries, preventing the result set from becoming excessively large, and thus avoiding waste of storage resources.

[0056] The process involves filtering out second historical query statements that failed to execute; parsing the second historical query statements and extracting field information from them as the second target dimension information; and based on the second target dimension information, extracting relevant data from the target dataset, processing it, and adding it to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

[0057] In this embodiment of the application, from the multiple historical query statements obtained, the second historical query statement with the execution result of failure is selected. Such query statements may fail because the multi-dimensional indicator result set lacks the dimension information required for the query. At this time, the field information in the second historical query statement can be extracted as the second target dimension information. Based on the second target dimension information, relevant data is extracted from the target dataset and processed, and added to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

[0058] In one example, by parsing the field information after the WHERE, GROUP BY, JOIN and other clauses in the second historical query statement, and comparing this field information with the dimensions that already exist in the current multi-dimensional indicator result set, if the parsed field information does not exist in the multi-dimensional indicator result set, it can be determined that the query failed because the multi-dimensional indicator result set lacks a dimension blind spot.

[0059] In this embodiment, by parsing the execution result characteristics of historical query statements, when the execution time exceeds a preset time threshold, the dimensional information of the multi-dimensional indicator result set is dynamically aggregated to improve query performance. When the execution result fails, the missing dimensional information is identified and determined, thereby dynamically expanding the dimensional coverage of the multi-dimensional indicator result set. Through this dynamic adjustment mechanism, when encountering the same or similar query request again, data can be quickly obtained directly from the optimized multi-dimensional indicator result set without performing time-consuming real-time calculations or facing query failures due to missing dimensions, thus optimizing the efficiency and success rate of data queries.

[0060] S104: By parsing the query characteristics of historical query statements, the initial field information used to create the index is determined.

[0061] In this embodiment of the application, since historical query statements contain a large amount of information, such as the final display fields and constant values, most of which are unrelated to index creation, the query features that directly affect the efficiency of data retrieval and processing are extracted through syntax parsing technology, and the query features of historical query statements are parsed to accurately identify key fields that are directly related to query performance from complex SQL statements, thereby providing an accurate basis for index recommendation.

[0062] In one example, query features may include at least one of the following: query condition features, table join features, grouping and aggregation features, and sorting features. For example: by parsing the WHERE clause in the query condition feature, the fields used as filtering conditions can be identified; by parsing the JOIN…ON clause in the table join feature, the fields used to join different tables can be identified; by parsing the GROUP BY clause in the grouping and aggregation feature, the fields used for grouping statistics can be identified; and by parsing the ORDER BY clause in the sorting feature, the fields used for sorting the results can be identified.

[0063] S105: Determine the index field information based on the initial field information and the preset index audit strategy.

[0064] In this embodiment, the initial field information is filtered and optimized based on the initial field information and a preset index review strategy to generate the final index field information. This review strategy effectively identifies and eliminates redundant fields and inefficient field combinations, ensuring that the recommended index has high business value and high query performance. This avoids the problem of low query efficiency in some business scenarios after deployment, which could lead to database performance degradation, cluster operation failures, or even system-level problems affecting other normal business scenarios.

[0065] S106: Based on the index field information, create one or more indexes for the multi-dimensional indicator result set, where each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

[0066] In this embodiment, based on the index field information determined after review, one or more indexes are automatically created for the multi-dimensional indicator result set. Each index is essentially an access path for data hotspot areas deduced by analyzing user query behavior, used to quickly locate a specific subset of data in the multi-dimensional indicator result set. By constructing an index set based on query hotspots, effective logical trimming of the original result set is achieved. By establishing a targeted index structure, redundant indexes for the full data are avoided, saving storage space. Furthermore, during query execution, query efficiency is improved by transforming a full table scan into the rapid location and access of multiple small result sets.

[0067] Pruning typically refers to reducing or filtering the result set of a database query or data processing session to obtain a data set that better meets the requirements. This process can be achieved through various methods, including but not limited to using conditional statements, subqueries, join operations, and aggregate functions in SQL statements.

[0068] Recommendation algorithms, as an important computer analysis technique, primarily rely on algorithmic calculations and data analysis to predict content or services that users may be interested in. This technology has been widely applied in internet fields such as e-commerce, news feeds, and streaming media services. By analyzing users' historical behavioral data, personalized recommendation models are built to achieve accurate information matching. This application applies the core ideas of recommendation algorithms to the field of database index optimization. By treating query statements as the analysis object and data fields as recommendation targets, and analyzing historical query behavior patterns, the most relevant data hotspots for indexing are intelligently inferred.

[0069] In one example, to maximize online query efficiency, a real-time triggered index optimization mechanism was introduced on top of the regular optimization of the recommendation algorithm: users can configure SQL execution time thresholds. When the query execution time exceeds the threshold, the regular recommendation algorithm analysis cycle is bypassed immediately, and an emergency optimization process is automatically triggered to quickly generate index schemes for the data fields involved in the slow query and prioritize their deployment to the query cluster.

[0070] In one example, index optimization can be performed during off-peak hours (such as evening), and during peak hours, optimization jobs can be paused as appropriate to reduce cluster load based on cluster load.

[0071] In one example, after step S106, the following is also included: When a new query is received, the corresponding target index is matched based on the characteristics of the query, and data is queried on the data subset corresponding to the target index.

[0072] In this embodiment of the application, after an index is created for the multi-dimensional indicator result set, when a new query statement is received, its query characteristics will be actively parsed and matched with the created index, thereby routing the same type of query statement to the optimal index path, ensuring that the query can query data on the data subset corresponding to the target index, thereby obtaining a fast query response.

[0073] In one example, the query features of a new query statement can be parsed to extract relevant query feature fields, such as query condition fields, join fields, grouping fields, or sorting fields. These extracted fields are then compared with existing indexes. If an index exists that perfectly matches the query feature fields, it is directly designated as the target index. If no exact match exists, the degree of matching is calculated based on field overlap, and the index with the highest overlap is selected as the target index. For example, if the parsed query feature fields are "transaction amount, transaction time, and transaction region," and only indexes (transaction amount and transaction time) exist, data can be queried primarily on these indexes to improve query efficiency.

[0074] In this embodiment of the application, when a new query statement is received, the query characteristics of the query statement are parsed to match the corresponding target index. This allows the system to automatically route the query to the optimal index path when new query characteristics appear, and directly query the data subset corresponding to that index. This effectively avoids a full table scan, thereby reducing the data retrieval range and improving data query efficiency.

[0075] In this embodiment, compared to the technical problems in the prior art where designing indexes for datasets results in slow query returns for massive amounts of data and the inability of indexes to adapt to dynamically changing business needs, leading to low data query efficiency, this application first obtains indicator definition configuration information, then extracts data associated with at least one indicator from the target dataset, and processes it according to the indicator definition configuration information to generate a multi-dimensional indicator result set. This avoids recalculation for each query, reducing query time and improving query efficiency. Then, by obtaining multiple historical query statements executed on the multi-dimensional indicator result set, and by parsing these historical query statements to infer data hotspots, index field information is determined. This ensures that the recommended index field information accurately reflects business query needs and dynamically adapts to changing scenarios, thus improving the accuracy and timeliness of index recommendations and ultimately increasing data query efficiency.

[0076] Figure 2 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown. Figure 2 As shown above, in the above Figure 1Based on the illustrated embodiment, one specific implementation of step S104 is as follows: S201: Filter target historical query statements that meet the preset time range.

[0077] In this application embodiment, the historical query statements contain a large number of queries for outdated business scenarios. The field information corresponding to these statements does not match the current business requirements. If the time range is not filtered, the fields extracted later will contain outdated field information, which will not only occupy index storage resources, but also reduce the adaptability of the index to the existing business.

[0078] In one example, the preset time range can be determined based on the business iteration cycle. For instance, in scenarios where business requirements change rapidly, such as in the financial industry, business systems typically iterate monthly, with each iteration potentially introducing new query patterns or changing the popularity of existing patterns. In this case, the preset time range can be configured to 30 days to ensure that index optimization always focuses on query behavior under the current business version.

[0079] In another example, the preset time range can be adaptively adjusted based on the frequency of historical query statements. If the frequency of historical query statements is detected to be too high, a shorter preset time range can be set to avoid field redundancy due to an excessively large preset time range, enabling a rapid response to dynamic changes in high-frequency scenarios and ensuring that the filtered fields accurately match the most popular business needs. Conversely, if the frequency of historical query statements is detected to be too low, a longer preset time range can be set to avoid insufficient historical query sample size and biased analysis due to an excessively small preset time range.

[0080] S202: Classify target historical query statements with the same structure but different query parameters into the same query mode.

[0081] In this embodiment, query parameters refer to variable constant values ​​in SQL statements. In the target query statements, many statements differ only in query parameters but share the same query pattern, essentially constituting repeated queries within the same business scenario. If query patterns are not differentiated and frequency statistics are directly performed based on the target historical query statements, queries corresponding to the same business scenario will be calculated separately, dispersing the actual access popularity of the business scenario and distorting the frequency calculation, thus affecting the accuracy of subsequent field weight calculations.

[0082] In one example, by structuring the target historical query statement and replacing the query parameters with placeholders, a structured statement can be obtained, and the same structured statements can be grouped into the same query pattern.

[0083] S203: For each query pattern, obtain the target historical query statement corresponding to the query pattern, and use the number of occurrences of the corresponding target historical query statement as the frequency of the query pattern.

[0084] In this embodiment of the application, the frequency of the query pattern directly reflects the popularity of the corresponding business scenario. The higher the frequency of the query pattern, the higher the query demand of the corresponding scenario. Therefore, by obtaining the target historical query statement corresponding to each query pattern, the number of times the target historical query statement appears is taken as the frequency of the query pattern.

[0085] In one example, to improve the accuracy of query pattern frequency statistics and more accurately reflect the actual access intensity of business scenarios, deduplication can be further performed on the identified target historical query statements. This improves the ability to identify completely identical query statements and extract their corresponding query subjects (such as the user identifier or application identifier that initiated the query) and query times. If multiple identical query statements are initiated consecutively by the same query subject within a short period of time, and the time interval between adjacent queries does not exceed a preset threshold (e.g., 5 seconds), these queries are merged into a single valid query to avoid duplicate queries caused by non-real business needs such as user errors, duplicate submissions from the front end, or network retries.

[0086] S204: For each query pattern, obtain the target historical query statement corresponding to the query pattern, and use the average execution time of the corresponding target historical query statement as the execution time of the query pattern.

[0087] In this embodiment of the application, the execution time corresponding to the query mode reflects the query efficiency of the corresponding business scenario. The longer the execution time, the lower the query efficiency in the corresponding scenario. Therefore, by obtaining the target historical query statement corresponding to each query mode, the average execution time of the target historical query statement is used as the execution time of the query mode.

[0088] In one example, to improve the accuracy of query execution time and identify outliers caused by network fluctuations and cluster load, the execution time of each corresponding historical query is first filtered before calculating the average execution time to remove outliers. For example, the standard deviation method can be used to calculate the mean and standard deviation of the execution time series, and values ​​deviating from the mean by more than 3 standard deviations are considered outliers.

[0089] S205: Extract query field information from each query pattern, and calculate the weight of the query field information based on the frequency and execution time corresponding to each query pattern.

[0090] In this embodiment, for each query mode, the weight of each query field is calculated based on the frequency and execution time. The higher the frequency, the higher the business scenario requirement of the field, and the greater the weight should be. The longer the execution time, the lower the query efficiency of the field, and the greater the weight should be. The weight value comprehensively reflects the optimization value and urgency of the field in the corresponding business scenario. The greater the weight, the more significant the impact of the field on query performance, and the more likely it is to be indexed.

[0091] In one example, the frequency weight and execution time weight can be obtained by normalizing the frequency value and execution time respectively.

[0092] S206: For each field, calculate the total weight of that field based on the weights calculated from the corresponding query patterns.

[0093] In this embodiment, since the same field may appear in multiple different query modes, considering only the weight in a single scenario cannot comprehensively assess its global importance. For each field, the total weight of that field is calculated based on the weights obtained from its respective query mode. For example, the weights of each field in different query modes can be added together to obtain the total weight.

[0094] S207: Sort the fields according to their total weight, and use the sorted field sequence as the initial field information for creating the index.

[0095] In this embodiment, the total weight of a field quantifies its optimization value in the global business scenario. By sorting all fields in descending order of their total weight, an index creation priority sequence can be obtained. In the sorting results, high-weight fields typically correspond to core business scenarios in the system that are both high-frequency and have performance bottlenecks. Prioritizing the creation of indexes for these fields can maximize the efficiency of data querying.

[0096] In this embodiment, by filtering target historical query statements that meet a preset time range, outdated field information is avoided in the filtered field information, which reduces the adaptability of the index to existing business. Then, target historical query statements with the same structure but different query parameters are divided into the same query mode, thereby avoiding the separate calculation of the frequency of queries corresponding to the same business scenario, which would cause the frequency calculation of business scenario to be distorted. Then, the frequency and execution time of each query mode are calculated, and the weight of each query field information is calculated based on the frequency and execution time. The fields are sorted based on the weight to obtain the initial field information for creating the index. This ensures that the recommended initial field information for creating the index covers both high-frequency access scenarios and targets serious query performance bottlenecks, thereby maximizing the efficiency of data query.

[0097] In one example, to improve the efficiency of the final created index field information, a specific implementation of step S105 is as follows: Calculate the dispersion of each initial field information in the multi-dimensional index result set.

[0098] In this embodiment, dispersion is an indicator that measures the uniformity of field data distribution. Higher dispersion indicates more unique values ​​for the field, resulting in a stronger ability to filter data when used as an index. Therefore, by calculating the dispersion of each initial field in the multi-dimensional index result set, invalid indexes for low-discrimination fields are avoided, thereby improving the utilization efficiency of index resources.

[0099] In one example, the dispersion of each initial field can be determined by counting the number of unique values ​​in each initial field.

[0100] The initial field information is filtered based on the dispersion, and the initial field information with a dispersion greater than or equal to the dispersion threshold is used as the target initial field information.

[0101] In this embodiment, the initial field information is filtered based on the dispersion, and only fields with a dispersion greater than or equal to a preset dispersion threshold are retained as target initial field information. This avoids the bias caused by recommending indexes based solely on weights, and avoids creating indexes for fields that have high weights but low data distinguishability, thus avoiding the problem of wasting index resources.

[0102] In one example, the dispersion threshold can be preset based on data characteristics. For example, the dispersion threshold can be set to: the number of unique values ​​in a field accounts for more than 5% of the total number of rows in the multi-dimensional indicator result set, to ensure that the field has sufficient distinguishing ability.

[0103] Identify combinations of field information that appear in historical query statements.

[0104] In this embodiment of the application, in actual business scenarios, a large number of queries rely on multi-field combination filtering conditions. A single field index is difficult to meet the optimization needs of such combined queries. Therefore, by identifying the field information combinations that appear in historical query statements, an index is created for each field information combination.

[0105] In one example, by calculating the frequency of each field information combination, only field information combinations with a frequency greater than a threshold are retained, and an index is created for those field information combinations, thus avoiding index redundancy caused by creating indexes for low-frequency combinations.

[0106] For each identified combination of field information, the information is filtered and sorted based on the dispersion of each initial field information in the combination and the total weight, and then composite index field information is generated.

[0107] In the embodiments of this application, there may be fields with low dispersion and low total weight in the combination of field information. If the composite index field information is directly created on the combination of field information, it will also lead to a decrease in index efficiency.

[0108] In one example, for each combination of field information, the dispersion of each field information is checked one by one to see if it meets the preset dispersion threshold. Field information with low dispersion threshold is excluded. Then, the remaining field information is sorted in descending order according to the corresponding total weight. The sorted field sequence is used as the composite index field information to improve index hit efficiency.

[0109] In another example, a weighted composite score of the total weight and dispersion of each field can be calculated, and the order of each field in the composite index field information can be determined based on the weighted composite score.

[0110] The target initial field information and the composite index field information are determined as the index field information.

[0111] In this embodiment, the target initial field information and the composite index field information respectively cover single-field query and multi-field combined query scenarios. If only the target initial field information is indexed, the scenario information corresponding to the composite field information will not be covered. For example, if the target initial field information is (A, B, C, D), based on the leftmost prefix matching rule of the database index, creating only a single-field index cannot provide a query optimization path for combined queries such as (B, C) or (C, D).

[0112] In one example, after identifying the target initial field information and the composite index field information as the index field information, it can be recommended to the operations department for secondary approval via report or email. Once approved, it can be fed back to the index optimization engine, which will automatically optimize the dataset.

[0113] In this embodiment, the initial field information is filtered based on the dispersion, and the initial field information with a dispersion greater than or equal to the dispersion threshold is selected as the target initial field information. This avoids creating indexes for fields with low distinguishability, thereby effectively improving the query efficiency of a single index. Then, in order to meet the performance optimization needs of multi-field combined queries in actual business, this application identifies the field information combinations that appear in historical query statements, and then filters and sorts the identified field information combinations based on the dispersion and total weight of the initial field information, thereby generating composite index field information. This can establish the optimal composite index scheme for high-frequency and important multi-field query scenarios, improving the query optimization efficiency in actual business scenarios.

[0114] Figure 3 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown. Figure 3 As shown above, in the above Figure 1 Based on the illustrated embodiment, after step S106, the method further includes: S301: Obtain the current set of existing indexes on the multi-dimensional indicator result set, and compare and analyze the indexes created for the multi-dimensional indicator result set with the existing indexes in the existing index set.

[0115] In this embodiment, after creating a new index based on a multi-dimensional indicator result set, it is necessary to first obtain the existing set of indexes, which refers to the indexes created in the previous optimization cycle. As business scenarios iterate, historical indexes may experience issues such as functional overlap or scenario mismatch. If both new and old indexes are retained simultaneously, it will lead to a waste of storage resources. Therefore, by comparing and analyzing the index created for the multi-dimensional indicator result set with the existing indexes in the existing set, it is ensured that the indexes adapt to the current business scenario without wasting storage space.

[0116] In one example, by comparing the leftmost prefix field sequence of each index, indexes in the existing set that have the same leftmost prefix as the newly created index can be filtered out as candidate comparison targets.

[0117] S302: If the created index field combination includes the field combination of an existing index, then the created index will replace the corresponding existing index in the existing index set.

[0118] In this embodiment, when the field combination of the newly created index includes an existing index, the new index not only fully covers all query scenarios of the existing index but also supports more complex combined queries. Therefore, storage waste can be reduced by replacing the corresponding existing index within the existing index set with the newly created index. "The created index field combination includes the field combination of the existing index" means that the field combination of the new index contains all the fields of the existing index, and the first index field of the new index is the same as the first index field of the existing index.

[0119] In one example, if the complete field sequence of the selected candidate index is a subset of the field sequence of the newly created index, it can be determined that the field combination of the created index includes the field combination of the existing index. For example, if the field combination of the newly created index is (time, region, product), and the field combination of the existing index is (time, region), then it is determined to be an inclusion relationship. In this case, the corresponding index in the existing index set is automatically deleted to ensure that there are no redundant indexes with overlapping functions in the index set, while retaining the new index with a wider query coverage.

[0120] S303: If the fields of the created index overlap with the fields of the existing index, the created index and the existing index will be merged into a new composite index.

[0121] In this embodiment, if the created index field overlaps with an existing index field (e.g., the existing index is "time, region", and the new index is "region, product type"), retaining both individually can only cover their respective query scenarios and cannot cover cross-scenario combined queries such as "time, region, product type". Furthermore, the simultaneous existence of both indexes increases storage costs. Therefore, by merging them into a new composite index, both the original two types of scenarios can be covered, cross-scenario coverage capability can be added, and the storage cost of the index can be reduced.

[0122] In one example, if a newly created index has the same leftmost prefix as an existing index, but the subsequent fields are different and do not contain each other, it can be determined that there is field overlap between the fields of the newly created index and the fields of the existing index.

[0123] In this embodiment of the application, by obtaining the existing set of indexes currently existing on the multi-dimensional indicator result set, the index created for the multi-dimensional indicator result set is compared and analyzed with the existing indexes in the existing set. When the combination of fields of the newly created index completely covers the functions of the existing index, the index is replaced to eliminate redundant indexes and reduce storage overhead. When there is an intersection between the fields of the newly created index and the existing index, the indexes are merged and reorganized into a composite index with broader coverage to save storage space.

[0124] Figure 4 A flowchart illustrating a data query optimization method according to another embodiment of this application is shown. Figure 4 As shown above, in the above Figure 1 Based on the illustrated embodiment, one specific implementation of step S102 is as follows: S401: Based on the indicator definition configuration information, construct an indicator dependency graph. The indicator dependency graph stores the dependencies between indicators, as well as the dependencies between indicators and dimension information.

[0125] In this embodiment of the application, to further conserve computing resources, an indicator dependency graph is constructed based on the indicator definition configuration information. The indicator dependency graph stores the dependencies between all indicators and dimensions in a graph structure. For example, nodes may include indicator nodes and dimension nodes, and edges may include dependency edges between indicators and dependency edges between indicators and dimensions. In one example, the indicator dependency graph may be a directed graph structure.

[0126] S402: When a change in the metric definition configuration information is detected, the changed metric is obtained, and the dependent metrics and dependent dimension information that are dependent on the changed metric are determined from the metric dependency graph based on the changed metric.

[0127] In this embodiment of the application, when a change in the indicator definition configuration information is detected, such as when a user modifies the calculation formula or caliber of an indicator, the dependent indicators and dependent dimension information that are dependent on the changed indicator are determined in the indicator dependency relationship graph based on the changed indicator.

[0128] In one example, a graph traversal algorithm can be used to determine the dependent metrics and dependent dimension information that are dependent on the changed metric. For instance, starting from the node corresponding to the changed metric, a depth-first search or breadth-first search is performed along the dependency edges to traverse all reachable metric nodes and dimension nodes, and these nodes are marked as dependent metrics and dependent dimension information that are dependent on the changed metric.

[0129] S403: Based on dependency metrics, change metrics, and dependency dimension information, determine the range of data that needs to be recalculated, and perform incremental calculations on the data within the range of data that needs to be recalculated to generate a multi-dimensional metric result set.

[0130] In this embodiment, the minimum recalculation range is determined based on dependency metrics, change metrics, and dependency dimensions. Incremental calculations are performed only on the affected data, which can improve computational efficiency and reduce resource consumption while ensuring data accuracy.

[0131] In this embodiment, an indicator dependency graph is constructed based on the indicator definition configuration information. When a change in the indicator definition configuration information is detected, the dependent indicators and dependent dimension information that are dependent on the changed indicator are determined from the indicator dependency graph based on the changed indicator. Incremental calculation is performed only on the affected subset of data. This allows computing resources to be concentrated on the necessary update scope while ensuring data consistency, thereby improving the update efficiency of the indicator result set and reducing computing load and storage overhead.

[0132] Figure 5 This diagram illustrates the overall process of a data query optimization method, as follows: Figure 5 As shown, S501: Obtain business activity data; S502: Store in the data warehouse; S503: Copy business data to the HDP cluster through the fact table; S504: Process the business data by building a model to generate structured business data; S505: Extract multi-dimensional indicator summary results from the structured business data; S506: Construct an N-dimensional cube based on the multi-dimensional indicator summary results; S507: Obtain the SQL statement for querying the N-dimensional cube; S508: Store the SQL statement in the component database; S509: The recommendation system extracts the SQL statement from the component database for analysis; S510: Generate an index for the N-dimensional cube.

[0133] S501: Acquire business activity data; acquire raw business activity data in real time or in batches from various business systems of the enterprise (such as transaction systems, user behavior collection systems, etc.) to form a raw data set to be processed.

[0134] S502: Store in the data warehouse; After the acquired business activity data is cleaned, transformed, and loaded, it is stored in the data warehouse to provide a standardized and unified data foundation for subsequent analysis.

[0135] S503: Business data is copied to the HDP cluster via the fact table. The core fact table data in the data warehouse is copied to the Hadoop Distributed Computing and Storage Cluster (Hortonworks Data Platform, HDP cluster). To ensure system stability and query performance, the HDP cluster adopts a read-write separation architecture, logically divided into a write cluster responsible for data processing and a read cluster responsible for online services. Business data is first imported into the write cluster, where it is processed using the cluster's distributed storage and parallel computing capabilities. Subsequent data model building, metric processing, and index optimization are all completed on the write cluster. After processing is complete and a stable result set and index set are generated, they are then synchronized to the read cluster. This mechanism ensures that high-load data processing jobs do not interfere with the real-time query service running on the read cluster. In one example, components such as Hive and Spark can also be used for data result analysis, statistics, and execution.

[0136] S504: By establishing a model, business data is processed to generate structured business data; in the HDP cluster, a data processing model is established through the indicator processing engine, and business data is aggregated and correlated according to predefined indicator rules to generate structured business data that meets the needs of multidimensional analysis.

[0137] S505: Extract multi-dimensional summary results from structured business data; extract multi-dimensional summary results of key business indicators from processed structured business data to form an indicator dataset containing different combinations of dimensions.

[0138] S506: Construct an N-dimensional cube based on the summary results of multi-dimensional indicators; organize the summary results of multi-dimensional indicators into an N-dimensional cube structure, supporting fast data query and analysis from any combination of dimensions. The N-dimensional cube is the multi-dimensional indicator result set generated by extracting data associated with at least one indicator from the target dataset and processing it according to the indicator definition configuration information.

[0139] S507: Obtain the SQL statement for querying the N-dimensional cube; record various query requests initiated by business users or application systems for the N-dimensional cube, and obtain the corresponding historical SQL query statements.

[0140] S508: Store SQL statements in the component database; persistently store the obtained SQL query statements, their execution time, the number of rows returned, etc., in a dedicated component database.

[0141] S509: The recommendation system extracts and analyzes SQL statements from the component database; the index recommendation system periodically extracts historical SQL statements from the component database and analyzes query behavior characteristics through techniques such as query feature parsing and pattern recognition. The extracted SQL statements can be analyzed using an index optimization engine to perform index recommendations.

[0142] S510: Generates an index for the N-dimensional cube; based on the SQL analysis results and combined with the data characteristics of multi-dimensional indicators, it automatically generates an optimized index strategy for the N-dimensional cube and feeds it back to the data storage layer to execute index creation.

[0143] Based on the data query optimization method provided in the above embodiments, this application also provides a specific implementation of a data query optimization device. Please refer to the following embodiments.

[0144] First see Figure 6 , Figure 6 The diagram shows a schematic of the data query optimization device provided in an embodiment of this application. The data query optimization device 600 provided in this embodiment includes: a first acquisition module 601, an extraction module 602, a second acquisition module 603, a parsing module 604, a determination module 605, and a creation module 606.

[0145] The first acquisition module 601 is used to acquire indicator definition configuration information, which includes indicator definition information of at least one indicator, indicator statistical method information, and dimension information of multiple dimensions. The extraction module 602 is used to extract data associated with at least one indicator from the target dataset, process it according to the indicator definition configuration information, and generate a multi-dimensional indicator result set. The second acquisition module 603 is used to acquire multiple historical query statements executed on the multi-dimensional indicator result set; Parsing module 604 is used to determine the initial field information for creating the index by parsing the query features of historical query statements; Module 605 is used to determine the index field information based on the initial field information and the preset index audit strategy; Create module 606, which is used to create one or more indexes for a multi-dimensional indicator result set based on index field information. Each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

[0146] In one example, the data query optimization device 600 also includes: The feature matching module is used to match the corresponding target index based on the features of a new query statement when it is received, and then query the data on the data subset corresponding to the target index.

[0147] In one example, parsing module 604 includes: The first filtering submodule is used to filter target historical query statements that meet the preset time range; Sub-modules are used to divide target historical query statements with the same structure but different query parameters into the same query pattern; The first acquisition submodule is used to acquire the target historical query statement corresponding to each query pattern, and use the number of occurrences of the corresponding target historical query statement as the frequency of the query pattern. The second acquisition submodule is used to acquire the target historical query statement corresponding to each query mode, and use the average execution time of the corresponding target historical query statement as the execution time of the query mode. The extraction submodule is used to extract query field information from each query pattern and calculate the weight of the query field information based on the frequency and execution time of each query pattern. The first calculation submodule is used to calculate the total weight of each field based on the weights calculated from the corresponding query pattern. The sorting submodule is used to sort the fields according to their total weight, and the sorted field sequence is used as the initial field information for creating the index.

[0148] In one example, module 605 is identified as including: The second calculation submodule is used to calculate the dispersion of each initial field information in the initial field information in the multi-dimensional index result set; The second filtering submodule is used to filter the initial field information based on the dispersion, and obtain the initial field information with a dispersion greater than or equal to the dispersion threshold as the target initial field information; The identification submodule is used to identify combinations of field information that appear in historical query statements; The generation submodule is used to filter and sort the identified combinations of field information based on the dispersion of each initial field information in each combination and the total weight, and generate the composite index field information. The first determination submodule is used to determine the target initial field information and the composite index field information as index field information.

[0149] In one example, the data query optimization device 600 also includes: The clustering analysis module is used to perform clustering analysis on historical query statements, aggregating historical query statements with similar query characteristics into different groups of historical query statements. The feature parsing module is used to determine the initial field information for creating an index by parsing the query features of historical query statements within each historical query statement group.

[0150] In one example, the data query optimization device 600 also includes: The third acquisition module is used to acquire the existing set of indexes currently existing on the multi-dimensional indicator result set, and compare and analyze the indexes created for the multi-dimensional indicator result set with the existing indexes in the existing set of indexes. The replacement module is used to replace the corresponding existing index within the existing index set if the created index field combination includes the field combination of an existing index. The merge module is used to merge the created index and the existing index into a new composite index if the fields of the created index and the field combination of the existing index overlap.

[0151] In one example, extraction module 602 includes: The construction submodule is used to build an indicator dependency graph based on the indicator definition configuration information. The indicator dependency graph stores the dependency relationships between each indicator, as well as the dependency relationships between indicators and dimension information. The third acquisition submodule is used to acquire the changed indicator when a change in the indicator definition configuration information is detected, and to determine the dependent indicators and dependent dimension information that are dependent on the changed indicator from the indicator dependency relationship graph based on the changed indicator. The second determination submodule is used to determine the range of data that needs to be recalculated based on dependency metrics, change metrics, and dependency dimension information, and to perform incremental calculations on the data within the range of data that needs to be recalculated to generate a multi-dimensional metric result set.

[0152] In one example, the data query optimization device 600 also includes: The processing module is used to filter the first historical query statements whose execution time is greater than or equal to a preset time threshold, parse the first historical query statements, identify the aggregation operation features contained in the first historical query statements, and extract the first target dimension information corresponding to the aggregation operation features; based on the first target dimension information, it performs aggregation processing on the dimension information corresponding to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set. The processing module is also used to filter out second historical query statements that failed to execute; parse the second historical query statements and extract the field information from them as the second target dimension information; based on the second target dimension information, extract relevant data from the target dataset, process it, and add it to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

[0153] Figure 7 A schematic diagram of the hardware structure of the electronic device provided in an embodiment of this application is shown.

[0154] An electronic device may include a processor 701 and a memory 702 storing computer program instructions.

[0155] Specifically, the processor 701 may include a central processing unit (CPU), an application specific integrated circuit (ASIC), or one or more integrated circuits that can be configured to implement the embodiments of this application.

[0156] Memory 702 may include mass storage for data or instructions. For example, and not limitingly, memory 702 may include a hard disk drive (HDD), a floppy disk drive, flash memory, optical disk, magneto-optical disk, magnetic tape, or a Universal Serial Bus (USB) drive, or a combination of two or more of these. In one instance, memory 702 may include removable or non-removable (or fixed) media, or memory 702 may be a non-volatile solid-state memory.

[0157] In one instance, memory 702 may be read-only memory (ROM). In one instance, the ROM may be a mask-programmed ROM, a programmable ROM (PROM), an erasable PROM (EPROM), an electrically erasable PROM (EEPROM), an electrically rewritable ROM (EAROM), or flash memory, or a combination of two or more of these.

[0158] Memory 702 may include read-only memory (ROM), random access memory (RAM), disk storage media device, optical storage media device, flash memory device, electrical, optical, or other physical / tangible memory storage device. Therefore, generally, memory includes one or more tangible (non-transitory) computer-readable storage media (e.g., memory devices) encoded with software including computer-executable instructions, and when the software is executed (e.g., by one or more processors), it is operable to perform the operations described with reference to the method according to one aspect of this disclosure.

[0159] The processor 701 reads and executes computer program instructions stored in the memory 702 to implement a data query optimization method in the above embodiment.

[0160] In one example, the electronic device may also include a communication interface 703 and a bus 704. Wherein, as... Figure 7 As shown, the processor 701, memory 702, and communication interface 703 are connected through bus 704 and complete communication with each other.

[0161] The communication interface 703 is mainly used to realize communication between various modules, devices, units and / or equipment in the embodiments of this application.

[0162] Bus 704 includes hardware, software, or both, that couples components of an online data traffic metering device together. For example, and not limitingly, the bus may include an Accelerated Graphics Port (AGP) or other graphics bus, an Extended Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), a Hyper Transport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an Infinite Bandwidth Interconnect, a Low Pin Count (LPC) bus, a memory bus, a Microchannel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-X) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local (VLB) bus, or other suitable buses, or combinations of two or more of these. Where appropriate, bus 704 may include one or more buses. Although specific buses are described and illustrated in embodiments of this application, this application contemplates any suitable bus or interconnect.

[0163] The data query optimization method described in the above embodiments can be implemented using a computer storage medium. This computer storage medium stores computer program instructions; when these instructions are executed by a processor, they implement any of the data query optimization methods described in the above embodiments.

[0164] This application also provides a computer program product, including a computer program, which, when executed, implements any of the data query optimization methods described in the above embodiments.

[0165] It should also be noted that the exemplary embodiments mentioned in this application describe methods or systems based on a series of steps or apparatus. However, this application is not limited to the order of the above steps; that is, the steps can be performed in the order mentioned in the embodiments, or in a different order, or several steps can be performed simultaneously.

[0166] The aspects of this disclosure have been described above with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of this disclosure. It should be understood that each block in the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus to produce a machine such that these instructions, executable via the processor of the computer or other programmable data processing apparatus, enable the implementation of the functions / actions specified in one or more blocks of the flowchart illustrations and / or block diagrams. Such a processor can be, but is not limited to, a general-purpose processor, a special-purpose processor, a special application processor, or a field-programmable logic circuit. It is also understood that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can also be implemented by special-purpose hardware performing the specified functions or actions, or can be implemented by a combination of special-purpose hardware and computer instructions.

[0167] The above are merely specific embodiments of this application. Those skilled in the art will clearly understand that, for the sake of convenience and brevity, the specific working processes of the systems, modules, and units described above can be referred to the corresponding processes in the foregoing method embodiments, and will not be repeated here. It should be understood that the protection scope of this application is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in this application, and these modifications or substitutions should all be covered within the protection scope of this application.

Claims

1. A data query optimization method, characterized in that, include: Obtain indicator definition configuration information, which includes indicator definition information for at least one indicator, indicator statistical method information, and dimension information for multiple dimensions; Extract data associated with at least one indicator from the target dataset, and process the data according to the indicator definition configuration information to generate a multi-dimensional indicator result set; Retrieve multiple historical query statements executed on the multi-dimensional indicator result set; By parsing the query features of the historical query statements, the initial field information used to create the index is determined; Based on the initial field information and the preset index audit strategy, the index field information is determined; Based on the index field information, one or more indexes are created for the multi-dimensional indicator result set, wherein each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

2. The method according to claim 1, characterized in that, After creating one or more indexes for the multi-dimensional indicator result set based on the index field information, the method further includes: When a new query statement is received, the corresponding target index is matched according to the characteristics of the query statement, and data is queried on the data subset corresponding to the target index.

3. The method according to claim 1, characterized in that, The step of determining the initial field information for creating the index by parsing the query features of the historical query statements includes: Filter target historical query statements that match a preset time range; Classify target historical query statements with the same structure but different query parameters into the same query pattern; For each query pattern, obtain the target historical query statement corresponding to that query pattern, and use the number of occurrences of the corresponding target historical query statement as the frequency of that query pattern; For each query pattern, obtain the target historical query statement corresponding to that query pattern, and use the average execution time of the corresponding target historical query statement as the execution time of that query pattern; Extract query field information from each query pattern, and calculate the weight of the query field information based on the frequency and execution time of each query pattern. For each field, calculate the total weight of that field based on the weights calculated from the corresponding query patterns; The fields are sorted according to their total weight, and the sorted field sequence is used as the initial field information for creating the index.

4. The method according to claim 3, characterized in that, The process of determining the index field information based on the initial field information and the preset index audit strategy includes: Calculate the dispersion of each initial field information in the multi-dimensional index result set; The initial field information is filtered based on the dispersion to obtain initial field information with a dispersion greater than or equal to the dispersion threshold, which is then used as the target initial field information. Identify combinations of field information that appear in common in the historical query statements; For the identified combinations of field information, the information is filtered and sorted based on the dispersion of each initial field information in each combination and the total weight, and then the composite index field information is generated. The target initial field information and the composite index field information are determined as the index field information.

5. The method according to claim 1, characterized in that, After obtaining multiple historical query statements executed on the multi-dimensional indicator result set, the process also includes: Cluster analysis is performed on the historical query statements to aggregate historical query statements with similar query characteristics into different groups of historical query statements; For each group of historical query statements, the initial field information for creating the index is determined by parsing the query features of the historical query statements within each group.

6. The method according to claim 1, characterized in that, After creating one or more indexes for the multi-dimensional indicator result set based on the index field information, the process further includes: Obtain the existing set of indexes currently existing on the multi-dimensional indicator result set, and compare and analyze the indexes created for the multi-dimensional indicator result set with the existing indexes in the existing set of indexes. If the created index field combination includes the field combination of an existing index, then the created index will replace the corresponding existing index in the existing index set; If the fields of the created index overlap with the fields of the existing index, the created index and the existing index will be merged into a new composite index.

7. The method according to claim 1, characterized in that, The step of extracting data associated with at least one indicator from the target dataset and processing it according to the indicator definition configuration information to generate a multi-dimensional indicator result set includes: Based on the indicator definition configuration information, an indicator dependency graph is constructed. The indicator dependency graph stores the dependency relationships between indicators and the dependency relationships between indicators and dimension information. When a change in the metric definition configuration information is detected, the changed metric is obtained, and the dependent metrics and dependent dimension information that are dependent on the changed metric are determined from the metric dependency graph based on the changed metric. Based on the dependency metrics, the change metrics, and the dependency dimension information, the range of data that needs to be recalculated is determined, and incremental calculations are performed on the data within the range of data that needs to be recalculated to generate a multi-dimensional metric result set.

8. The method according to claim 1, characterized in that, After obtaining multiple historical query statements executed on the multi-dimensional indicator result set, the process also includes: First historical query statements with execution times greater than or equal to a preset time threshold are selected, the first historical query statements are parsed, the aggregation operation features contained in the first historical query statements are identified, and the first target dimension information corresponding to the aggregation operation features is extracted; based on the first target dimension information, the dimension information corresponding to the multi-dimensional indicator result set is aggregated to obtain the changed multi-dimensional indicator result set. And / or, The second historical query statement that failed to execute is filtered; the second historical query statement is parsed, and the field information in the second historical query statement is extracted as the second target dimension information; based on the second target dimension information, relevant data is extracted from the target dataset and processed, and added to the multi-dimensional indicator result set to obtain the changed multi-dimensional indicator result set.

9. A data query optimization device, characterized in that, The device includes: The first acquisition module is used to acquire indicator definition configuration information, which includes indicator definition information of at least one indicator, indicator statistical method information, and dimension information of multiple dimensions. The extraction module is used to extract data associated with at least one indicator from the target dataset, and process it according to the indicator definition configuration information to generate a multi-dimensional indicator result set. The second acquisition module is used to acquire multiple historical query statements executed on the multi-dimensional indicator result set; The parsing module is used to determine the initial field information for creating the index by parsing the query features of the historical query statements; The determination module is used to determine the index field information based on the initial field information and the preset index audit strategy; A creation module is used to create one or more indexes for the multi-dimensional indicator result set based on the index field information, wherein each index is used to quickly locate a subset of data in the multi-dimensional indicator result set.

10. An electronic device, characterized in that, The device includes: a processor and a memory storing computer program instructions; when the processor executes the computer program instructions, it implements a data query optimization method as described in any one of claims 1-8.

11. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer program instructions, which, when executed by a processor, implement a data query optimization method as described in any one of claims 1-8.

12. A computer program product, characterized in that, When the instructions in the computer program product are executed by the processor of the electronic device, the electronic device is able to perform a data query optimization method as described in any one of claims 1-8.

Images