A database query pre-caching method and related apparatus

Abstract

This application discloses a database query pre-caching method and related apparatus, relating to the fields of data processing and cache optimization technology. First, by identifying query pattern groups through syntactic structure features and business scenario features, it achieves the goal of simultaneously pre-caching similar queries, improving cache coverage and query result accuracy. Second, by using a time series prediction model, it can timely and accurately predict high-frequency query situations that may occur, rationally plan the execution timing of pre-caching, and dynamically schedule pre-computation tasks through dual judgment of load awareness and time slices to ensure that the pre-caching process can be executed reliably and effectively. Ultimately, it achieves the goal of efficient database query caching and optimizes the pre-caching process.

Description

Technical Field

[0001] This invention relates to the field of data processing and caching optimization technology, specifically to a database query pre-caching method and related apparatus. Background Technology

[0002] In database service scenarios, SQL queries serve as the core entry point for data interaction, and their response efficiency directly determines the user experience and operational stability of the business system. For example, in e-commerce promotional events, the daily query log volume reaches millions, involving various business types such as order statistics, user profiling analysis, and real-time inventory queries. The business system faces enormous query pressure and needs to respond to user query requests quickly and accurately. According to business statistics, a large part of the performance bottleneck of database services stems from the repetitive calculations and data reads of high-frequency query requests, and caching technology is a key means to solve this problem.

[0003] Currently, the mainstream caching technology in the industry is passive caching. Passive caching uses query requests to trigger caching as its core logic. When a user initiates a query, if the cache is hit, the query result is returned; if not, the database is accessed, the result is returned, and then cached. However, this solution relies entirely on users actively initiating queries. For newly launched or long-unexecuted queries, the first call inevitably triggers a cache cold start, leading to response delays. Furthermore, passive caching typically uses an exact matching strategy, which cannot identify queries with similar structures but different parameters. This results in similar queries needing to be repeatedly calculated and cached, increasing resource consumption during the caching process and reducing cache space utilization. None of these solutions can meet the demands of efficient querying.

[0004] In summary, how to provide an efficient database query pre-caching method has become an urgent problem to be solved. Summary of the Invention

[0005] In view of the above problems, this application provides a database query pre-caching method and related apparatus to achieve efficient database query pre-caching. The specific solution is as follows:

[0006] The first aspect of this application provides a database query pre-caching method, including:

[0007] Upon receiving a query request, based on the syntactic structure features and business scenario features of the query request, determine the query pattern group to which the query request belongs. The query pattern group is pre-created and used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier.

[0008] Based on the pattern identifier, historical query data of the query pattern group is obtained, and a prediction feature vector is constructed. The prediction feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features, and business association features.

[0009] The predicted feature vector is input into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods.

[0010] When the prediction result is a high-frequency query pattern, the query pattern group is determined to be a pre-cached query pattern group, and the time slice in which the query frequency exceeds a preset high-frequency threshold is determined to be the pre-cached execution time slice.

[0011] When the real-time load of the business system meets the preset load conditions and the current time is before the pre-cached execution time slice, a pre-calculation task is performed on the query data corresponding to the pre-cached query mode group, and the result of the pre-calculation task is stored in the cache.

[0012] In one possible implementation, a query pattern group is created by following these steps:

[0013] The query request is standardized by replacing the dynamic parameters in the query request with placeholders to obtain a standardized query statement.

[0014] The standardized query statement is parsed to generate an abstract syntax tree;

[0015] Traverse the abstract syntax tree and extract syntactic structure features from it;

[0016] Determine the business system identifier associated with the query statement and identify the business scenario characteristics;

[0017] Construct a multidimensional query pattern feature vector based on the syntactic structure features and the business scenario features;

[0018] Cluster the feature vectors of the multidimensional query patterns to obtain multiple query pattern groups, and assign a pattern identifier to each query pattern group.

[0019] In one possible implementation, the multidimensional query pattern feature vector includes: aggregation function type, number of table names, core table name hash, condition keyword type, number of condition association fields, whether to group, number of grouping fields, and business system identifier.

[0020] In one possible implementation, inputting the predicted feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods includes:

[0021] The predicted feature vector is normalized to map the feature values ​​of each dimension to a preset numerical range to obtain a multi-dimensional feature vector.

[0022] The multidimensional feature vector is input into the input layer of the Long Short-Term Memory (LSTM) network model;

[0023] The LSTM model sequentially captures the temporal dependencies of the multidimensional feature vectors through multiple hidden layers, and outputs the hidden state vector.

[0024] The hidden state vector is mapped to the predicted frequency value of each time slice within a future preset time window through the output layer of the LSTM model.

[0025] The predicted frequency values ​​for each time slice are compared with the corresponding high-frequency thresholds, and the prediction results are output based on the comparison results.

[0026] In one possible implementation, the real-time load of the business system meets the preset load conditions, including: the CPU utilization rate of the business system load is lower than a first preset threshold, the memory utilization rate is lower than a second preset threshold, the number of database connections is lower than a third preset threshold, and the remaining cache space is greater than the space required by the pre-computation task.

[0027] In one possible implementation, before performing a pre-computation task on the query data corresponding to the pre-cached query pattern group, the method further includes:

[0028] The pre-computation tasks are prioritized based on at least one of the pre-cached execution time slice urgency, prediction frequency, or result data volume.

[0029] In one possible implementation, before performing a pre-computation task on the query data corresponding to the pre-cached query pattern group, the method further includes:

[0030] When the estimated result data volume of the pre-calculation task exceeds the preset data volume threshold, the pre-calculation task is split into multiple sub-tasks according to the parameter range, and the result data volume of each sub-task is lower than the preset data volume threshold.

[0031] In one possible implementation, performing a pre-computation task on the query data corresponding to the pre-cached query pattern group includes:

[0032] Obtain the typical query statement corresponding to the pre-cached query pattern group;

[0033] The dynamic parameters of the typical query statement are populated based on the time characteristics of the pre-cached execution time slice;

[0034] A query is initiated into the database based on the populated typical query statement to obtain the results of the pre-calculated task.

[0035] In one possible implementation, storing the pre-computed task results in a cache includes:

[0036] A cache key is constructed based on the pattern identifier of the pre-cached query pattern group, the hash value of the typical query statement after filling, and the cache expiration time;

[0037] The pre-calculated task result is stored as a cache value in the storage module corresponding to the pre-cached query mode identifier;

[0038] Construct an index between the pre-cached query pattern identifier and the cache key.

[0039] One possible implementation also includes:

[0040] The deviation rate between the predicted query frequency and the actual query frequency of the pre-cached query mode group, and the cache hit rate of the pre-calculated task results are statistically analyzed.

[0041] When the deviation rate exceeds a preset deviation threshold, the parameters of the time series prediction model are adjusted.

[0042] When the hit rate is lower than the preset hit threshold, adjust the preset high-frequency threshold, the pre-caching execution priority, or the cache expiration time.

[0043] A second aspect of this application provides a database query pre-caching device, comprising:

[0044] The query request receiving module is used to receive query requests and determine the query pattern group to which the query request belongs based on the syntactic structure features and business scenario features of the query request. The query pattern group is pre-created and used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier.

[0045] The predictive feature vector construction module is used to obtain historical query data of the query pattern group based on the pattern identifier and construct a predictive feature vector. The predictive feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features and business association features.

[0046] The prediction module is used to input the predicted feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods.

[0047] The determination module is used to determine the query pattern group as a pre-cached query pattern group when the prediction result is a high-frequency query pattern, and to determine the time slice in which the query frequency exceeds a preset high-frequency threshold as the pre-cached execution time slice.

[0048] The pre-caching module is used to perform a pre-calculation task on the query data corresponding to the pre-caching query mode group when the real-time load of the business system meets the preset load conditions and the current time is before the pre-caching execution time slice, and to store the pre-calculation task results in the cache.

[0049] A third aspect of this application provides an electronic device, comprising at least one processor and a memory connected to the processor, wherein:

[0050] The memory is used to store computer programs;

[0051] The processor is used to execute the computer program to enable the electronic device to implement the database query pre-caching method as described above.

[0052] A fourth aspect of this application provides a computer program product including computer-readable instructions that, when executed on an electronic device, cause the electronic device to implement the database query pre-caching method described above.

[0053] The fifth aspect of this application provides a computer storage medium carrying one or more computer programs, which, when executed by an electronic device, enable the electronic device to implement the database query pre-caching method described above.

[0054] By employing the above technical solutions, the database query pre-caching method and related apparatus provided in this application firstly identify query pattern groups through syntactic structure features and business scenario features, thereby achieving the goal of simultaneous pre-caching of similar queries, improving cache coverage, and enhancing the accuracy of query results. Secondly, through a time series prediction model, it can timely and accurately predict high-frequency query situations that may occur, rationally plan the execution timing of pre-caching, and dynamically schedule pre-computation tasks through dual judgment of load awareness and time slices, ensuring that the pre-caching process can be executed reliably and effectively. Ultimately, it achieves the goal of efficient database query caching and optimizes the pre-caching process. Attached Figure Description

[0055] The above and other features, advantages, and aspects of the embodiments of this disclosure will become more apparent from the accompanying drawings and the following detailed description. Throughout the drawings, the same or similar reference numerals denote the same or similar elements. It should be understood that the drawings are schematic, and the originals and elements are not necessarily drawn to scale.

[0056] Figure 1 A schematic diagram of a database query pre-caching method provided in this application;

[0057] Figure 2 A schematic diagram illustrating the creation process of a query pattern group provided in this application;

[0058] Figure 3 A schematic diagram illustrating the generation process of a predictive feature vector provided in this application;

[0059] Figure 4 A schematic diagram of a task breakdown process provided in this application;

[0060] Figure 5 A schematic diagram of the pre-computation task execution process provided for this application;

[0061] Figure 6 A schematic diagram illustrating the process of storing the pre-calculated task results provided for this application into the cache;

[0062] Figure 7 A schematic diagram illustrating the feedback optimization process of the time series prediction model provided in this application;

[0063] Figure 8 A schematic diagram of the structure of the database query pre-caching device provided in this application;

[0064] Figure 9 A schematic diagram of the structure of the electronic device provided in this application. Detailed Implementation

[0065] The embodiments of this application are described below with reference to the accompanying drawings. The terminology used in the implementation section of this application is for explaining specific embodiments only and is not intended to limit the scope of this application.

[0066] The embodiments of this application will now be described with reference to the accompanying drawings. Those skilled in the art will recognize that, with technological advancements and the emergence of new scenarios, the technical solutions provided in the embodiments of this application are equally applicable to similar technical problems.

[0067] The terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such terms are interchangeable where appropriate; this is merely a way of distinguishing objects with the same attributes in the embodiments of this application. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion, so that a process, method, business system, product, or apparatus that comprises a series of units is not necessarily limited to those units, but may include other units not explicitly listed or inherent to those processes, methods, products, or apparatuses.

[0068] like Figure 1As shown, this embodiment provides a database query pre-caching method. This method identifies the query pattern group to which a query request belongs, predicts high-frequency query patterns, and dynamically executes pre-computation tasks based on the business system load status and pre-caching execution time slices, thereby achieving efficient and accurate responses to database query requests. The method specifically includes the following steps:

[0069] Step S101: Receive a query request from the business system. Based on the syntactic structure features and business scenario features of the query request, determine the query pattern group to which the query request belongs. The query pattern group is pre-created and used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier.

[0070] Specifically, when a business system sends a query request to the database, the query request is first analyzed to extract its syntactic structure features and business scenario features. For example, the listening layer captures a query request initiated by the business system (e-commerce order management backend).

[0071] Syntactic structure features refer to the inherent structural attributes of a query statement that do not change with specific parameters, such as the type of aggregate functions, the data tables involved, and the types of condition keywords. Business scenario features reflect the business system identifier and business function category associated with the query request.

[0072] The extracted syntactic structure features are combined with business scenario features to form a pattern feature representation of the query request. This representation is then matched against multiple pre-created query pattern groups in the business system. For example, the feature vector of this query request is compared with the feature vector of the query module to determine the corresponding query pattern group. For instance, if the similarity between the pattern feature of this query request and the center vector of the query pattern group "PM_001" reaches 0.94, exceeding the preset matching threshold of 0.85, then the query request is determined to belong to the query pattern group "PM_001".

[0073] A query pattern group is a combination of query requests with similar characteristics. Query requests within the same pattern group have similar syntactic structures and business intents. For example, all query requests involving "counting the number of orders by time range," regardless of their specific time range parameters, can be grouped into the same query pattern group. Each query pattern group is configured with a unique pattern identifier, such as "PM_001" mentioned above, for subsequent data retrieval and predictive analysis.

[0074] By grouping query requests into pattern groups instead of matching each query exactly, the business system can pre-cache all query requests corresponding to its query pattern group when it receives a query request in subsequent processing. This improves the pre-caching coverage and the accuracy of the query results.

[0075] Step S102: Based on the pattern identifier, obtain the historical query data of the query pattern group and construct the prediction feature vector.

[0076] Specifically, after determining the query pattern group to which the query request belongs and its pattern identifier "PM_001", the business system retrieves the historical query data of the query pattern group from the historical data storage based on the pattern identifier and then constructs a predicted feature vector.

[0077] The predicted feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features, and business-related features. Time features are used to capture the periodic patterns of query patterns over time, such as time period features, date type features, monthly cycle features, and promotional cycle features. Historical frequency features reflect the activity level of query pattern groups in the same historical period, including the average frequency and the maximum frequency of the same period. Business-related features introduce external business indicators, including the number of active users and the update frequency of core table data.

[0078] By integrating these three types of features, the business system can construct a multi-dimensional feature vector that comprehensively reflects the current state and historical trends of the query pattern group, providing high-quality input data for subsequent predictive analysis.

[0079] Step S103: Input the predicted feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in the future time period.

[0080] Specifically, the business system inputs the constructed prediction feature vector into a pre-trained time series prediction model. This model can capture the temporal dependencies and periodic patterns contained in the feature vector and output the prediction results of the query pattern group in the future period.

[0081] The prediction results typically include the predicted frequency values ​​for each future time slice, reflecting the likelihood and frequency with which the query pattern group will be accessed in the future. For example, as shown in Table (1) below:

[0082] Table (1)

[0083]

[0084] Time series forecasting models can employ neural network models such as Long Short-Term Memory (LSTM) networks, which can capture long-term dependencies. Compared with traditional forecasting methods based on simple statistical rules, these models can adaptively learn complex periodic fluctuations and business-driven patterns, significantly improving forecast accuracy.

[0085] Step S104: When the prediction result is a high-frequency query pattern, determine the query pattern group as a pre-cached query pattern group, and determine the time slice in which the query frequency exceeds the preset high-frequency threshold as the pre-cached execution time slice.

[0086] Specifically, the business system compares the predicted frequency values ​​for each time slice in the prediction results with a preset high-frequency threshold. The high-frequency threshold can be flexibly set according to the business scenario, for example, set to a fixed value, such as 2000 times, or set to a preset multiple of the historical average frequency of the query pattern group, such as 1.5 times. If the predicted frequency of a certain time slice exceeds the high-frequency threshold, the query pattern group is determined to be a high-frequency query pattern in that time slice, and the business system identifies the query pattern group as a pre-cached query pattern group and designates that time slice as a pre-cached execution time slice.

[0087] The pre-cached execution time slice refers to the time window during which the business system should complete pre-computation tasks and cache writing before the arrival of the specified time slice. For example, as shown in the table above, if the high-frequency threshold is 2000 times, and a certain query pattern group is predicted to have a high-frequency period from 10:00 to 11:00 AM, then the business system will determine "10:00-11:00" as the pre-cached execution time slice and must complete pre-caching preparation before the arrival of this period.

[0088] This step can identify query patterns that may be accessed frequently in the future, thereby providing a precise target list and time plan for the execution of pre-caching, effectively solving the problem of cache cold start.

[0089] Step S105: In response to the real-time load of the business system meeting the preset load conditions and the current time being before the pre-cached execution time slice, perform a pre-calculation task on the query data corresponding to the pre-cached query mode group and store the pre-calculation task results in the cache.

[0090] After identifying high-frequency query patterns and determining the pre-cached query module, this step requires further judgment. First, the real-time load of the business system must meet preset load conditions to ensure that the pre-computation task is executed only when the business system is under low load, avoiding resource contention with normal business requests. Second, the current time must be before the pre-cached execution time slice to ensure that the pre-computation task results are ready before the business peak. Only when these conditions are simultaneously met will the business system trigger the execution of the pre-computation task, ensuring that the execution process of the pre-computation task does not affect normal business operations and guaranteeing the reliable storage of the pre-computation task results.

[0091] Pre-computation tasks refer to generating corresponding query statements based on pre-cached query pattern groups and submitting them to the database for execution, thereby pre-calculating the query results. Once the pre-computation task is completed, the business system stores the results in a cache storage module, such as a Redis cluster. By pre-caching the results of pre-computation tasks, when a user actually initiates such a query, the business system can directly read the results from the cache and return them without needing to access the database again for real-time calculation, thus significantly reducing query response latency.

[0092] In this step, the load status of the business system is comprehensively judged through multiple dimensions of indicators. The preset load conditions usually include CPU utilization below the first threshold, memory utilization below the second threshold, database connection count below the third threshold, and the remaining cache space greater than the space required by the pre-computed tasks.

[0093] Specifically, the values ​​of the first, second, and third preset thresholds can be dynamically adjusted based on the server's hardware configuration and historical business load characteristics. For example, in a typical e-commerce business scenario, CPU utilization during peak business hours typically fluctuates between 60% and 80%. To reserve sufficient resource buffer, the first preset threshold can be set to 40%, meaning that when the average CPU utilization of a node is below 40%, the CPU resource is considered to be in a low-load range. Similarly, the second preset threshold can be set to 50%, and the third preset threshold can be set to 60% of the maximum number of database connections. It should be understood that the above values ​​are only illustrative examples. For high-performance server clusters, the first preset threshold can be appropriately increased to 50% or 60%, while for resource-constrained single-machine deployment environments, the first preset threshold can be decreased to 30% to adapt to different hardware environments. Furthermore, the determination of remaining cache space is to prevent the write failure of pre-computed task results due to insufficient cache space, ensuring the effectiveness of pre-caching.

[0094] Furthermore, if the real-time load of the business system does not meet the preset load conditions—that is, if the business system is under high load and cannot immediately execute the pre-computed tasks—then the pre-computed tasks are marked as pending execution. The business system continuously monitors load changes, and once the business system enters a low-load period, such as the low traffic period at 3 AM, and the time is before the pre-cached execution time slice, the pre-computed tasks are immediately triggered for execution. This process ensures that pre-computed tasks are dynamically triggered when resource availability is valid, further improving the balance and timeliness of pre-computed task scheduling.

[0095] This embodiment first identifies query pattern groups by using syntactic structure features and business scenario features, enabling similar queries to be pre-cached simultaneously, thus improving cache coverage and query result accuracy. Second, it uses a time series prediction model to pre-determine high-frequency query patterns and pre-caching execution time slices, enabling timely and accurate prediction of potential high-frequency queries, reasonable planning of pre-caching execution timing, and flexible and autonomous response to query demands. Finally, it dynamically schedules pre-computation tasks through load awareness and time slice-based judgment, ensuring reliable and effective execution of the pre-caching process, ultimately achieving comprehensive optimization of database query performance.

[0096] In the database query pre-caching method disclosed in this embodiment, in addition to the pre-caching process described above, the server also responds to the query request and determines whether the data requested by the query request has been pre-cached. If so, it directly sends the feedback to the business system; if not, it searches for the data and then sends the feedback. This process runs parallel to the pre-caching process, meaning that the database determines whether to perform pre-caching in parallel while responding to the query request normally.

[0097] In the database query pre-caching method disclosed in this application, the query pattern group is created in advance before the execution of the pre-caching process. By classifying and associating query requests or query statements, unified pattern recognition and predictive analysis can be achieved.

[0098] This embodiment details the process of creating query pattern groups, specifically how to abstract specific query requests into syntactic structure features and business scenario features, and how to construct query pattern groups through clustering. The process is as follows: Figure 2 As shown,

[0099] Step S201: Standardize the query request by replacing the dynamic parameters in the query request with placeholders to obtain a standardized query statement.

[0100] Standardization is a crucial step in eliminating unstructured differences in query requests. In real-world business scenarios, user-initiated queries often differ only in their parameters—that is, their dynamic parameters—while the query logic remains completely consistent. For example, in e-commerce order queries, dynamic parameters can include string parameters such as merchant identifiers, user identifiers, region names, and brand names, as well as date and time parameters such as time ranges and specific times.

[0101] The core purpose of standardization is to remove dynamic parameter values ​​in query statements that vary due to differences in specific business scenarios, while retaining the common features of query statements in terms of syntax and logical patterns, thereby providing a basis for comparability for subsequent pattern clustering.

[0102] For example, the query statements "SELECT * FROM orders WHERE id=1" and "SELECT * FROM orders WHERE id=2" both intend to "retrieve order information based on ID". However, if exact matching is used, the business system will recognize them as two completely different queries, leading to duplicate calculations and wasted cache space. This embodiment replaces the dynamic parameter (such as "1" in "id=1") with a uniform placeholder (such as "id=?"), unifying the two statements into "SELECT * FROM orders WHERE id=?". This eliminates the differences caused by different dynamic parameter values ​​while preserving the query request structure.

[0103] It is understood that the replacement method for dynamic parameters is not limited to the above examples. For date type parameters (such as "create_time BETWEEN '2024-05-01' AND '2024-05-31'"), it can be replaced with "create_timeBETWEEN #{start_date} AND #{end_date}". For user identifier parameters, it can be replaced with "user_id=#{user_id}", etc. The above description is only an example, and the specific replacement rules can be flexibly configured according to the business scenario.

[0104] Step S202: Perform syntax parsing on the standardized query statement to generate an abstract syntax tree.

[0105] The business system uses a syntax parser (such as ANTLR4) to parse the standardized query statements and generate an abstract syntax tree (AST). The AST is a tree-like representation of the query statement, clearly showing its syntactic components and hierarchical relationships. The root node represents the query type, such as SELECT, INSERT, or UPDATE, and each level of child nodes corresponds to a different syntactic component of the query statement.

[0106] By using an abstract syntax tree, the business system transforms query statements, originally existing as strings, into a structured syntactic representation that can be traversed and extracted by the program. This transformation is the foundation for subsequent feature extraction, because only by parsing the query statement into a tree structure can various syntactic elements be accurately located and extracted.

[0107] Step S203: Traverse the abstract syntax tree and extract syntactic structure features from it.

[0108] Syntactic structural features include: the type of aggregate functions, the set of table names, the type of condition keywords, and the grouping fields. Specifically:

[0109] Identify and extract the types of aggregate functions from the selection clause of the abstract syntax tree. For example, if the SELECT clause contains aggregate functions such as SUM, COUNT, AVG, MAX, and MIN, record the function type (e.g., COUNT) and parameters (e.g., order_id). The types of these aggregate parameters represent the statistical intent of the query and the method of aggregation.

[0110] Iterate through all table nodes in the source clause and extract a set of all table names involved in the query. For example, extract all table names involved in the source clause (such as orders). If there are join queries (such as JOIN), record all related table names (such as orders, users). These tables represent the source of the query data, that is, where the data corresponding to the query statement is obtained from.

[0111] Extract the condition keyword type from the condition clause, traverse all condition nodes in the condition clause, and extract the condition keyword type (i.e., operator type) used for each condition, such as BETWEEN, IN, >, etc., as well as the condition association fields, such as "create_time" and "user_id", which represent what kind of query logic.

[0112] If a grouping field (such as user_id) extracted from a grouping clause does not exist, it is marked as "no grouping".

[0113] In some examples, syntactic structural features may also include: a sorting field (such as total) extracted from a sorting clause, marked as "no sorting" if no sorting clause exists, and marked as "sorted" if a sorting clause exists.

[0114] Step S204: Determine the business system identifier associated with the query statement and determine the business scenario characteristics.

[0115] While extracting syntactic structure features, the business system identifier associated with each query record is read. Business scenario features reflect the business system identifier associated with the query request and its business function category. For example, a query request from the order business system usually involves order statistics-related business scenarios, while a query request from the user business system usually involves user analysis-related business scenarios. Therefore, the order business system can be assigned an identifier of 1, and the user business system can be assigned an identifier of 2 to distinguish the two business scenarios.

[0116] By introducing business scenario features, business systems can distinguish query requests from different business systems that have similar syntax structures, thus avoiding misclassifying queries with different business intentions into the same pattern group.

[0117] Step S205: Construct a multi-dimensional query pattern feature vector based on syntactic structure features and business scenario features.

[0118] Analyzing the output of the above steps reveals that the following syntactic and structural features have been obtained: the type of aggregate function, the set of table names, the type of condition keywords, and the grouping fields; as well as the business scenario feature: the business system identifier.

[0119] In order to apply the dimensions corresponding to these features in the subsequent clustering process, they need to be converted into feature vectors that can be processed by clustering or models. To this end, the inventors further subdivided these dimensions and set value rules for each dimension.

[0120] The detailed feature dimensions include: aggregate function type, number of table names, core table name hash, condition keyword type, number of condition key fields, whether grouped, number of segment fields, and business system identifier.

[0121] The rules for determining the values ​​of each dimension can be found in Table (2) below:

[0122] Table (2)

[0123]

[0124] Among them, the aggregate function type can be mapped to a numerical code (e.g., COUNT corresponds to 1, SUM corresponds to 2, AVG corresponds to 3, etc.); the number of table names is the number of tables in the FROM clause (including related tables); the core table name hash is calculated by concatenating the table name set after sorting it alphabetically and taking the first 8 bits of the MD5 hash value; the condition key type can be mapped to a preset identifier (e.g., containing BETWEEN corresponds to 1, containing IN corresponds to 2, etc.); the number of condition association fields is the number of fields involved in the WHERE clause; whether to group is 0 (no GROUP BY) or 1 (with GROUP BY); the number of grouping fields is the number of grouping fields or 0; the business system identifier is encoded in the business_system field (e.g., order business system corresponds to 1, user business system corresponds to 2).

[0125] Using the above rules, each query request can be transformed into an 8-dimensional feature vector, realizing the transformation from "query statement to feature vector". For example, the feature vector is [1,1,a3f2d4e5,1,1,1,1,1].

[0126] Step S206: Cluster the feature vectors of the multidimensional query patterns to obtain multiple query pattern groups, and assign a pattern identifier to each query pattern group.

[0127] For example, the query "SELECT COUNT(*) FROM orders WHERE create_time BETWEEN'2024-11-10 00:00:00' AND '2024-11-10 01:00:00'" is standardized by replacing the dynamic time parameter with a placeholder, resulting in the standardized query "SELECT COUNT(*) FROM orders WHERE create_time BETWEEN #{start} AND #{end}". Syntactic features such as the aggregate function type "COUNT", the data table involved being "orders", the condition keyword type being "BETWEEN", and the condition association field being "create_time" are extracted through syntax parsing. These features are combined with business system identifiers to determine business scenario characteristics, constructing an 8-dimensional query pattern feature vector and clustering it. All query requests involving "counting the number of orders by time interval" are grouped into the same query pattern group, labeled "PM_001: Order Time Period Statistics Pattern", and assigned a pattern identifier.

[0128] When a business system receives a new query request, it determines the query mode group to which the request belongs as "PM_001" based on the syntactic structure and business scenario characteristics of the query request.

[0129] This embodiment uses a clustering algorithm (such as K-Means) to cluster the feature vectors of all query requests. The goal of the clustering algorithm is to group vectors that are close in distance in the feature space into the same class. Since query requests with similar structures tend to have feature vectors that are close in space, the clustering algorithm can automatically group query requests with similar business intentions into the same query pattern group.

[0130] First, determine the number of clusters, K. The optimal value of K can be determined using the "elbow rule." This process includes: calculating the clustering error as K ranges from 5 to 50, plotting the error-K curve, and identifying the point on the curve where the error decreases sharply as the optimal K value. Then, randomly select K feature vectors as initial cluster centers. Calculate the Euclidean distance from each feature vector to each cluster center and assign it to the group containing the nearest cluster center. Recalculate the average value of all feature vectors within each group, using this as the new cluster center. Stop iteration when the change in cluster centers is less than a threshold (e.g., 0.001) or when the number of iterations reaches an upper limit (e.g., 100). This yields the final K query pattern groups. Assign a unique "pattern ID" to each group, such as PM_001, PM_002, etc., for subsequent data retrieval and predictive analysis.

[0131] To more clearly characterize the features of each query pattern group, a business scenario tag can be added to each group. This tag consists of a business system identifier and a core function description. The core function description can be generated based on the feature vector of the query pattern group.

[0132] Through the above steps, massive query requests are categorized into a limited number of query pattern groups. Each pattern group represents a set of query statements with similar structures and business intentions. This process breaks through the limitations of exact matching while ensuring the accuracy of query results, achieving more accurate and faster queries. This provides a high-quality data foundation for subsequent predictive analysis and effectively improves the performance of pre-caching.

[0133] After determining the query pattern group corresponding to the query request based on the scheme described in the above embodiments, the next step is to obtain historical query data according to the query pattern group, and then construct a prediction feature vector for prediction, which is then input into the trained time series prediction model for prediction.

[0134] First, we will introduce the training process of the time series prediction model. The purpose of the time series prediction model is to predict high-frequency query patterns that may occur in the future, thereby providing accurate information for pre-caching. Therefore, the input features of the model need to fully reflect the temporal correlation and business relevance of the query pattern groups to ensure prediction accuracy.

[0135] Specifically, the predicted feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features, and business-related features.

[0136] Among them, time features are used to capture the periodic patterns of query patterns over time, including:

[0137] The time period feature divides a day into multiple time periods according to a preset time period division rule, maps the query initiation time to the corresponding time period, and obtains the time period identifier. For example, 24 hours are divided into 8 time periods, 0:00-3:00 corresponds to identifier 0, 3:00-6:00 corresponds to identifier 1, and so on up to 21:00-24:00 corresponds to identifier 7.

[0138] Date type characteristics: Based on calendar information, the query initiation time is mapped to the corresponding date type, resulting in a date type identifier. For example, a value of 0 represents a weekday, 1 represents a weekend, and 2 represents a public holiday.

[0139] Monthly cycle characteristics map the query initiation time to the corresponding monthly cycle based on calendar month information, resulting in a monthly cycle identifier. For example, a value of 0 represents non-end of the month or 1 represents the last 3 days of the month, capturing periodic needs such as monthly financial queries.

[0140] Promotion cycle characteristics: Based on the business activity calendar, the query initiation time is mapped to the corresponding promotion cycle, resulting in a promotion cycle identifier. For example, a value of 0 represents a non-promotion period or 1 represents a promotion period.

[0141] It should be understood that the granularity of time feature division can be flexibly adjusted according to the actual business scenario. For example, for high-frequency trading business systems, the time period division can be more refined, such as down to the minute level.

[0142] Historical frequency characteristics reflect the activity level of query pattern groups during the same historical period, including:

[0143] The average frequency during the same period is calculated by averaging the number of times the query pattern group is executed within the historical time window, in time slices with the same combination of time characteristics as the current time. For example, if the current time is Monday morning at 10:00 AM, time slice 3, and it is a weekday, then the average number of times the pattern group is executed is calculated in all time slices that meet the condition of "time slice 3 + weekday" in the past 7 days.

[0144] The maximum frequency within a given period is calculated by counting the number of queries executed within the same time slice as the current time feature combination within a historical time window, and then taking the maximum value. This characteristic provides a benchmark reference for the model and helps identify abnormal fluctuations.

[0145] Business association features are used to reflect the activity status of business systems associated with query pattern groups, including:

[0146] The number of active users is obtained within a historical time window from the number of active users of the business systems associated with the query pattern group. For example, for the order statistics pattern group, the query frequency is often positively correlated with the number of active users of the order business system.

[0147] The core table data update frequency is the number of times the core data tables involved in this query pattern group are updated within a historical time window. For example, the higher the update frequency of the order table, the more frequent the data changes and the more urgent the related statistical query needs.

[0148] Introducing business-related features enables the model to capture the impact of business-driven factors on query frequency, further improving the robustness of prediction.

[0149] Based on the characteristics of the aforementioned feature vectors, training data is prepared first. Data is extracted from historical query data, for example, from query pattern logs of the past 3 months, aggregated by pattern ID + time slice, to generate feature vector-actual frequency sample pairs for each pattern ID + time slice. The actual frequency is the actual number of times that pattern group was executed in that time slice.

[0150] The sample set is divided into a training set and a validation set according to a preset ratio. For example, the sample set can be divided into a training set (70%) and a validation set (30%) in a 7:3 ratio. The training set is used for learning model parameters, and the validation set is used to evaluate the model's generalization ability. In addition, a test set can be set to test the trained model and determine whether it meets expectations. If a test set is set, the ratio of training set:validation set:test set = 8:1:1 can be used as a reference.

[0151] Because the dimensions and numerical ranges of different features vary greatly—for example, frequency might be in the thousands while time period identifiers are only 0 to 7—directly inputting them into the model would affect training convergence speed and accuracy. Therefore, feature vector normalization is necessary. For input features such as average frequency and number of active users, Min-Max normalization is performed, mapping them to the [0,1] interval to avoid the impact of feature magnitude differences on model training. The normalization formula is:

[0152] (Formula 1);

[0153] Where x is the original feature value, x min For the eigenvalue minimum, x max This represents the maximum value of the characteristic.

[0154] In terms of model selection, this embodiment chooses a Long Short-Term Memory (LSTM) network as the prediction model because it can effectively capture long-term dependencies in time series data, such as periodic fluctuations in query patterns, and can meet the prediction scenario requirements of this application. The model can be implemented based on the TensorFlow 2.x framework.

[0155] The specific structure and training process of the model will be given as an example below.

[0156] The LSTM model in this embodiment adopts a 3-layer network structure, including: an input layer, a hidden layer, and an output layer. The parameter design of each layer can be referred to in Table (3) below:

[0157] Table (3)

[0158]

[0159] The loss function uses mean squared error (MSE) to measure the difference between predicted and actual frequencies. The formula is as follows:

[0160] (Formula 2);

[0161] This refers to the actual frequency. The prediction frequency is n, where n is the number of samples.

[0162] In one example, the optimizer used is the Adam optimizer, with a learning rate set to 0.001, adaptively adjusting the learning rate to accelerate convergence. The batch size is set to 64, the number of training epochs is set to 100, and an early stopping strategy is employed: training stops when the validation set MSE does not decrease for 5 consecutive epochs to avoid overfitting.

[0163] After training according to the above design, save the optimal model, that is, the model with the smallest MSE on the validation set, to a local file to obtain the trained time-based prediction model for subsequent prediction.

[0164] In the prediction phase, historical query data for query pattern groups is obtained based on pattern identifiers. This data is then processed to obtain a prediction feature vector, which is a multi-dimensional feature vector generated based on time features, historical frequency features, and business relevance features. The specific process is as follows: Figure 3 As shown, it includes:

[0165] Step S301: Normalize the predicted feature vector and map the feature values ​​of each dimension to a preset numerical range to obtain a multi-dimensional feature vector.

[0166] Step S302: Input the multidimensional feature vector into the input layer of the Long Short-Term Memory (LSTM) network model.

[0167] Step S303: Capture the temporal dependencies of the multidimensional feature vectors sequentially through the multiple hidden layers of the LSTM model, and output the hidden state vector.

[0168] The model structure includes multiple LSTM hidden layers. For example, as shown above, a three-layer LSTM hidden layer structure can be used, with the number of neurons set to 64, 64, and 32 respectively. Through layer-by-layer abstraction, the temporal dependency relationship from short-term fluctuations to long-term trends can be captured.

[0169] Step S304: Map the hidden state vector to the predicted frequency value of each time slice within the future preset time window through the output layer of the LSTM model.

[0170] The output layer maps the hidden state vector to the predicted frequency value of each time slice within a future preset time window, such as predicting the frequency of each 30-minute time slice in the next 12 hours, with an output dimension of 24.

[0171] Step S305: Compare the predicted frequency values ​​of each time slice with the corresponding high-frequency thresholds, and output the prediction results based on the comparison results.

[0172] The business system compares the prediction frequency with a preset high-frequency threshold. The high-frequency threshold can be set to a fixed value, such as 2000 times, or 1.5 times the historical average frequency of the pattern group. If the prediction frequency of a certain time slice exceeds the threshold, the pattern group is determined to be a high-frequency query mode for that time slice, and the corresponding prediction result identifier is output.

[0173] In this embodiment, the model training and prediction processes integrate multi-dimensional features such as time, historical frequency, and business relevance, and utilize an LSTM model to capture temporal dependencies, achieving accurate prediction of high-frequency query patterns. This significantly improves prediction accuracy and provides a reliable foundation for the pre-caching process.

[0174] In the embodiments disclosed in this application, after obtaining the prediction results and determining the pre-cached query mode group, if the real-time load of the current business system meets the preset load conditions and the current time is also before the pre-cached execution time slice, the pre-computation task process can be started to perform pre-computation tasks on the query data corresponding to the pre-cached query mode group.

[0175] To ensure the pre-computation process is executed more efficiently and smoothly, the pre-computation tasks can be sorted before executing them on the query data corresponding to the pre-cached query pattern group. For example, the pre-computation tasks can be prioritized based on at least one of the following: the urgency of the pre-cached execution time slice of the pre-cached query pattern group, the prediction frequency, or the amount of result data.

[0176] Prioritization is designed to execute the most valuable pre-computed tasks first, especially during periods of low business system load. Specifically, the urgency of a pre-cached execution time slice refers to the distance between the current time and the current time for the pre-cached execution time slice of a pre-computed task; the closer the time slice, the higher the urgency. For example, if a query pattern is predicted to be most frequent between 10 AM and 12 PM, and the current time is 8 AM, then this task has high urgency and should be prioritized to ensure pre-caching is completed before the business peak. Prediction frequency reflects the popularity of the query pattern; tasks with higher prediction frequencies yield greater pre-caching benefits and should be prioritized. The amount of result data affects the task's execution time; tasks with smaller data volumes can be completed quickly and are suitable for interleaving. This multi-level prioritization strategy ensures that urgent and high-value tasks are processed first, maximizing the business benefits of pre-caching.

[0177] In practical applications, situations may arise where the estimated result data volume of the pre-calculation task is too large, leading to excessively long execution times, prolonged occupation of business system resources, increased business system load risk, and in severe cases, disruption of normal business system operation. To address this issue, before executing the pre-calculation task, additional steps can be added, such as... Figure 4 Task breakdown process shown:

[0178] Step S401: Determine the amount of estimated result data for the pre-calculation task.

[0179] Step S402: When the estimated result data volume of the pre-calculation task exceeds the preset data volume threshold, the pre-calculation task is split into multiple sub-tasks according to the parameter range.

[0180] The resulting data volume of each subtask is lower than the preset data volume threshold. The preset data volume threshold can be set according to the memory size and I / O performance of the business system, for example, 5GB.

[0181] The task splitting mechanism is designed to address the issue of large pre-computation tasks consuming significant resources over extended periods. If the estimated result data volume of a pre-computation task exceeds 5GB, direct execution may lead to prolonged database I / O blocking, increasing the risk of overload on the business system. In such cases, the business system will split the task. The splitting logic can be based on query parameter ranges. For example, a large task like "querying annual order statistics" can be split into 12 sub-tasks, such as "querying January order statistics" and "querying February order statistics," with the result data volume of each sub-task controlled within 2GB.

[0182] In addition, it also includes:

[0183] Step S403: Monitor the business system load during the execution of the split sub-tasks in real time. If the business system load is detected to rise above the threshold, suspend the execution of the remaining sub-tasks until the load is below the threshold, and then continue to execute the remaining sub-tasks.

[0184] This step allows for real-time monitoring of the load pressure on the business system, thereby further reducing interference with the business system.

[0185] The results of the split subtasks can be merged in the cache or stored separately for later querying.

[0186] The following examples will detail the specific implementation process of pre-computation task execution and cache storage, that is, how to generate pre-computation task results and efficiently store them in the cache.

[0187] Specifically, the process of performing pre-computation tasks on the query data corresponding to the pre-cached query pattern group is as follows: Figure 5 As shown, it includes:

[0188] Step S501: Obtain the typical query statement corresponding to the pre-cached query mode group.

[0189] Typical query statements refer to representative query templates within a pattern group. They are typically selected from the historical query logs of that pattern group, choosing the most frequently executed query statement as the typical query statement. Typical query statements retain the structural framework of the query, but their dynamic parameters are not yet populated.

[0190] Step S502: Fill the dynamic parameters of typical query statements based on the time characteristics of the pre-cached execution time slice.

[0191] Parameter population involves replacing placeholders in a typical query statement with specific parameter values ​​based on the time characteristics of the pre-cached execution time slice. For example, if the pre-cached execution time slice is "2024-11-11 10:00-12:00", and the time parameters in the typical query statement are placeholders #{start_date} and #{end_date}, then the business system will populate the placeholders with "2024-11-11 00:00:00" and "2024-11-11 12:00:00" based on the time characteristics of this time slice. It should be understood that the parameter population method can be flexibly configured according to the business scenario. For user identifier parameters, the identifiers of active users can be selected from the active user list of the business system for population, covering high-probability query requirements.

[0192] Step S503: Initiate a query to the database based on the filled typical query statement to obtain the pre-calculated task results.

[0193] The business system uses a pre-filled typical query statement to send a simulated query request to the database. After the database executes the query, it returns the result data, which is the pre-calculated task result. The pre-calculated task result is usually stored in JSON format for easy subsequent serialization and deserialization.

[0194] Furthermore, the process of storing the pre-computed task results into the cache is as follows: Figure 6 As shown, it includes:

[0195] Step S601: Construct a cache key based on the pattern identifier of the pre-cached query pattern group, the hash value of the typical query statement after filling, and the cache expiration time.

[0196] The cache key uses a combination of pattern identifier, statement hash, and expiration date to ensure accurate matching of pre-cached results during user queries. The pattern identifier is a unique identifier for the query pattern group, such as PM_001. The statement hash is the SHA-256 hash value of the padded typical query statement, taking the first 16 bits to ensure an exact match. The cache expiration date is the expiration time stamp before the pre-calculated task results are valid in the cache. It should be understood that the cache key design is not limited to the above combination format; other combinations that uniquely identify the query pattern and specific parameters can also be used, as long as the requirement for accurate matching is met.

[0197] Step S602: Store the pre-calculated task result as a cache value in the storage module corresponding to the pre-cache query mode identifier.

[0198] The storage module can employ high-performance caching storage systems such as Redis clusters, leveraging their high performance and high availability to achieve rapid storage and retrieval of pre-computed task results. Cache values ​​are stored as String types, supporting fast GET / SET operations.

[0199] Step S603: Construct an index of the pre-cached query pattern identifier and cache key.

[0200] To accelerate cache lookup speed, the scheme disclosed in this embodiment constructs an index of pattern identifiers and cache keys, stored in a Redis hash data structure. When a user initiates a query request, the business system first calculates the query pattern group to which the query request belongs and its pattern identifier. Then, it quickly locates the corresponding cache key list in the index using the pattern identifier, and then matches the specific cache result. This indexing mechanism significantly shortens the cache hit path, enabling user queries to complete cache matching and result return within milliseconds.

[0201] As can be seen from the descriptions of all the above embodiments, timely and accurate prediction of potential high-frequency queries is key to this application. Therefore, the performance of the time series prediction model directly determines the accuracy of the prediction results. To ensure that the performance of the time series prediction model meets the requirements, it needs to be continuously optimized and updated. This embodiment will detail the specific implementation process of feedback optimization of the time series prediction model, specifically, how to dynamically optimize model parameters and pre-caching strategies through feedback of bias rate and hit rate to ensure the long-term effectiveness of the business system. The process is as follows: Figure 7 As shown:

[0202] Step S701: Calculate the deviation rate between the predicted query frequency and the actual query frequency of the pre-cached query mode group, as well as the cache hit rate of the pre-calculated task results.

[0203] Prediction bias rate is a core metric for measuring the accuracy of time series forecasting models. After executing a pre-caching task, the business system continuously monitors the actual query frequency of the corresponding pre-cached query pattern group within the pre-cached execution time slice. The difference between the predicted query frequency and the actual query frequency directly reflects the accuracy with which the model captures business patterns. Specifically, the prediction bias rate can be obtained by calculating the ratio of the absolute value of the difference between the predicted query frequency and the actual query frequency to the actual query frequency.

[0204] Step S702: When the deviation rate exceeds the preset deviation threshold, adjust the parameters of the time series prediction model.

[0205] The preset deviation threshold can be set according to the business's tolerance for prediction accuracy, for example, 30%. When the prediction deviation rate of a certain pre-cached query pattern group exceeds 30%, it indicates that the business patterns of that pattern group have changed, and the original model parameters can no longer accurately fit the current data distribution. At this time, the model parameter adjustment mechanism is triggered. Model parameter adjustment can be carried out using incremental training, using the latest historical data from a recent period, such as the past week, to fine-tune the existing time series prediction model parameters, rather than retraining the entire model. This mechanism enables the model to quickly adapt to business fluctuations, such as promotional activities and holidays, avoiding prediction failure due to model obsolescence, thereby ensuring the long-term accuracy of prediction results.

[0206] Step S703: When the hit rate is lower than the preset hit threshold, adjust the preset high-frequency threshold, the pre-cached execution priority, or the cache expiration time.

[0207] Cache hit rate is a key metric for measuring the effectiveness of a pre-caching strategy. Hit rate is the ratio of the number of times a pre-cached result is hit by an actual query request to the total number of pre-cached requests. If the hit rate is lower than a preset hit threshold, such as below 20%, it indicates that the target query pattern for pre-caching is not a high-frequency query, resulting in a waste of computing resources and cache space. In this case, the business system needs to adjust its pre-caching strategy.

[0208] Specifically, the adjustments include: increasing the preset high-frequency threshold to reduce the probability of pre-caching triggering and decrease invalid pre-caching; lowering the execution priority of pre-caching, placing pre-computation tasks for low-efficiency pattern groups in a lower priority position; and shortening the cache expiration time to accelerate the eviction of invalid cache and free up cache space for other high-frequency queries. It should be understood that the above adjustments can be used in combination according to actual business scenarios. For example, for pattern groups with high deviation rates and low hit rates, both the high-frequency threshold and the cache expiration time can be adjusted simultaneously.

[0209] This embodiment constructs a dual optimization adjustment strategy by using deviation rate feedback for model parameter adjustment and hit rate feedback for pre-caching strategy adjustment, which ensures the accuracy of prediction results. This method can adapt to changes in business scenarios and improves the reliability of the model.

[0210] The above describes the database query pre-caching method provided by the embodiments of this application. The following describes the apparatus for performing the above database query pre-caching method.

[0211] Please see Figure 8 , Figure 8 This is a schematic diagram of a database query pre-caching device provided in an embodiment of this application. Figure 8 As shown, the database query pre-caching device 800 includes:

[0212] The query request receiving module 801 is used to receive query requests and determine the query pattern group to which the query request belongs based on the syntactic structure features and business scenario features of the query request. The query pattern group is pre-created and is used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier.

[0213] The predictive feature vector construction module 802 is used to obtain historical query data of the query pattern group based on the pattern identifier and construct a predictive feature vector. The predictive feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features and business association features.

[0214] Prediction module 803 is used to input the prediction feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in the future time period;

[0215] The determination module 804 is used to determine the query pattern group as a pre-cached query pattern group when the prediction result is a high-frequency query pattern, and to determine the time slice in which the query frequency exceeds a preset high-frequency threshold as a pre-cached execution time slice.

[0216] The pre-caching module 805 is used to perform a pre-calculation task on the query data corresponding to the pre-caching query mode group when the real-time load of the business system meets the preset load conditions and the current time is before the pre-caching execution time slice, and to store the pre-calculation task result in the cache.

[0217] The database query pre-caching device disclosed in this embodiment identifies query pattern groups through syntactic structure features and business scenario features in the query request receiving module. This enables similar query statements to be pre-cached simultaneously, improving cache coverage and query result accuracy. Next, the predictive feature vector construction module constructs predictive feature vectors. The time series prediction model of the prediction module can accurately predict potentially high-frequency queries. After the determination module identifies the pre-cached query pattern group, the pre-caching module rationally plans the execution timing of pre-caching and dynamically schedules pre-computation tasks through load awareness and time slice dual judgment, ensuring reliable and effective execution of the pre-caching process. Ultimately, this achieves the goal of efficient database query caching and optimizes the pre-caching process.

[0218] The above example illustrates one implementation process of the database query pre-caching device. For the specific process, please refer to the above method embodiment, which will not be repeated here.

[0219] This application also provides an electronic device in its embodiments. (See reference...) Figure 9The diagram illustrates a structural schematic suitable for implementing the electronic device in the embodiments of this application. The electronic device in the embodiments of this application may include, but is not limited to, fixed terminals such as mobile phones, laptops, PDAs (personal digital assistants), PADs (tablet computers), desktop computers, etc. Figure 9 The electronic device shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of this application.

[0220] like Figure 9 As shown, the electronic device may include a processing unit (e.g., a central processing unit, a graphics processing unit, etc.) 901, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 902 or a program loaded from a storage device 908 into a random access memory (RAM) 903. When the electronic device is powered on, the RAM 903 also stores various programs and data required for the operation of the electronic device. The processing unit 901, ROM 902, and RAM 903 are interconnected via a bus 904. An input / output (I / O) interface 905 is also connected to the bus 904.

[0221] Typically, the following devices can be connected to I / O interface 905: input devices 906 including, for example, touchscreens, touchpads, keyboards, mice, cameras, microphones, accelerometers, gyroscopes, etc.; output devices 907 including, for example, liquid crystal displays (LCDs), speakers, vibrators, etc.; storage devices 908 including, for example, memory cards, hard drives, etc.; and communication devices 909. Communication device 909 allows electronic devices to exchange data via wireless or wired communication with other devices. Although Figure 9 Electronic devices with various devices are shown, but it should be understood that it is not required to implement or have all of the devices shown. More or fewer devices may be implemented or have alternatively.

[0222] This application also provides a computer program product including computer-readable instructions, which, when executed on an electronic device, cause the electronic device to implement any of the database query pre-caching methods provided in this application.

[0223] This application also provides a computer storage medium that carries one or more computer programs. When the one or more computer programs are executed by an electronic device, the electronic device can implement any of the database query pre-caching methods provided in this application.

[0224] It should also be noted that the device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. In addition, in the device embodiment drawings provided in this application, the connection relationship between modules indicates that they have a communication connection, which can be implemented as one or more communication buses or signal lines.

[0225] Through the above description of the embodiments, those skilled in the art can clearly understand that this application can be implemented by means of software plus necessary general-purpose hardware, or it can be implemented by special-purpose hardware including application-specific integrated circuits, special-purpose CPUs, special-purpose memory, special-purpose components, etc. Generally, any function performed by a computer program can be easily implemented by corresponding hardware, and the specific hardware structure used to implement the same function can also be diverse, such as analog circuits, digital circuits, or special-purpose circuits. However, for this application, software program implementation is more often the preferred implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product is stored in a readable storage medium, such as a computer floppy disk, USB flash drive, mobile hard disk, ROM, RAM, magnetic disk, or optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, training equipment, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0226] In the above embodiments, implementation can be achieved, in whole or in part, through software, hardware, firmware, or any combination thereof. When implemented in software, it can be implemented, in whole or in part, as a computer program product.

[0227] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions may be transmitted from one website, computer, training device, or data center to another website, computer, training device, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium may be any available medium that a computer can store or a data storage device such as a training device or data center that integrates one or more available media. The available media may be magnetic media (e.g., floppy disks, hard disks, magnetic tapes), optical media (e.g., DVDs), or semiconductor media (e.g., solid-state drives (SSDs)).

Claims

1. A database query pre-caching method, characterized in that, include: Upon receiving a query request, based on the syntactic structure features and business scenario features of the query request, determine the query pattern group to which the query request belongs. The query pattern group is pre-created and used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier. Based on the pattern identifier, historical query data of the query pattern group is obtained, and a prediction feature vector is constructed. The prediction feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features, and business association features. The predicted feature vector is input into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods. When the prediction result is a high-frequency query pattern, the query pattern group is determined to be a pre-cached query pattern group, and the time slice in which the query frequency exceeds a preset high-frequency threshold is determined to be the pre-cached execution time slice. When the real-time load of the business system meets the preset load conditions and the current time is before the pre-cached execution time slice, a pre-calculation task is performed on the query data corresponding to the pre-cached query mode group, and the result of the pre-calculation task is stored in the cache.

2. The method according to claim 1, characterized in that, To create a query pattern group, follow these steps: The query request is standardized by replacing the dynamic parameters in the query request with placeholders to obtain a standardized query statement. The standardized query statement is parsed to generate an abstract syntax tree; Traverse the abstract syntax tree and extract syntactic structure features from it; Determine the business system identifier associated with the query statement and identify the business scenario characteristics; Construct a multidimensional query pattern feature vector based on the syntactic structure features and the business scenario features; Cluster the feature vectors of the multidimensional query patterns to obtain multiple query pattern groups, and assign a pattern identifier to each query pattern group.

3. The method according to claim 2, characterized in that, The multidimensional query pattern feature vector includes: aggregate function type, number of table names, core table name hash, condition keyword type, number of condition association fields, whether to group, number of grouping fields, and business system identifier.

4. The method according to claim 1, characterized in that, The step of inputting the predicted feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods includes: The predicted feature vector is normalized to map the feature values ​​of each dimension to a preset numerical range to obtain a multi-dimensional feature vector. The multidimensional feature vector is input into the input layer of the Long Short-Term Memory (LSTM) network model; The LSTM model sequentially captures the temporal dependencies of the multidimensional feature vectors through multiple hidden layers, and outputs the hidden state vector. The hidden state vector is mapped to the predicted frequency value of each time slice within a future preset time window through the output layer of the LSTM model. The predicted frequency values ​​for each time slice are compared with the corresponding high-frequency thresholds, and the prediction results are output based on the comparison results.

5. The method according to claim 1, characterized in that, The real-time load of the business system meets the preset load conditions as follows: the CPU utilization rate of the business system load is lower than the first preset threshold, the memory utilization rate is lower than the second preset threshold, the number of database connections is lower than the third preset threshold, and the remaining cache space is greater than the space required by the pre-computation task.

6. The method according to claim 5, characterized in that, Before performing a pre-computation task on the query data corresponding to the pre-cached query pattern group, the following steps are also included: The pre-computation tasks are prioritized based on at least one of the pre-cached execution time slice urgency, prediction frequency, or result data volume.

7. The method according to claim 5, characterized in that, Before performing a pre-computation task on the query data corresponding to the pre-cached query pattern group, the following steps are also included: When the estimated result data volume of the pre-calculation task exceeds the preset data volume threshold, the pre-calculation task is split into multiple sub-tasks according to the parameter range, and the result data volume of each sub-task is lower than the preset data volume threshold.

8. The method according to claim 1, characterized in that, The step of performing pre-computation tasks on the query data corresponding to the pre-cached query pattern group includes: Obtain the typical query statement corresponding to the pre-cached query pattern group; The dynamic parameters of the typical query statement are populated based on the time characteristics of the pre-cached execution time slice; A query is initiated into the database based on the populated typical query statement to obtain the results of the pre-calculated task.

9. The method according to claim 8, characterized in that, The step of storing the pre-calculated task results in the cache includes: A cache key is constructed based on the pattern identifier of the pre-cached query pattern group, the hash value of the typical query statement after filling, and the cache expiration time; The pre-calculated task result is stored as a cache value in the storage module corresponding to the pre-cached query mode identifier; Construct an index between the pre-cached query pattern identifier and the cache key.

10. The method according to claim 9, characterized in that, Also includes: The deviation rate between the predicted query frequency and the actual query frequency of the pre-cached query mode group, and the cache hit rate of the pre-calculated task results are statistically analyzed. When the deviation rate exceeds a preset deviation threshold, the parameters of the time series prediction model are adjusted. When the hit rate is lower than the preset hit threshold, adjust the preset high-frequency threshold, the pre-caching execution priority, or the cache expiration time.

11. A database query pre-caching device, characterized in that, include: The query request receiving module is used to receive query requests and determine the query pattern group to which the query request belongs based on the syntactic structure features and business scenario features of the query request. The query pattern group is pre-created and used to represent a combination of query requests with similar features. The query pattern group is configured with a pattern identifier. The predictive feature vector construction module is used to obtain historical query data of the query pattern group based on the pattern identifier and construct a predictive feature vector. The predictive feature vector is a multi-dimensional feature vector generated based on time features, historical frequency features and business association features. The prediction module is used to input the predicted feature vector into the trained time series prediction model to obtain the prediction results of the query pattern group in future time periods. The determination module is used to determine the query pattern group as a pre-cached query pattern group when the prediction result is a high-frequency query pattern, and to determine the time slice in which the query frequency exceeds a preset high-frequency threshold as the pre-cached execution time slice. The pre-caching module is used to perform a pre-calculation task on the query data corresponding to the pre-caching query mode group when the real-time load of the business system meets the preset load conditions and the current time is before the pre-caching execution time slice, and to store the pre-calculation task results in the cache.

12. An electronic device, characterized in that, It includes at least one processor and a memory connected to the processor, wherein: The memory is used to store computer programs; The processor is used to execute the computer program to enable the electronic device to implement the database query pre-caching method as described in any one of claims 1 to 10.

13. A computer program product, characterized in that, Includes computer-readable instructions that, when executed on an electronic device, cause the electronic device to implement the database query pre-caching method as described in any one of claims 1 to 10.

14. A computer storage medium, characterized in that, The storage medium carries one or more computer programs that, when executed by an electronic device, enable the electronic device to implement the database query pre-caching method as described in any one of claims 1 to 10.

Images