Query statement adjustment method and device of relational database based on large model

Abstract

The invention provides a query statement adjustment method and device for a relational database based on a large model, and the method comprises the steps: carrying out the persistence processing of a training set to obtain a model file, building a prediction analysis model based on a random forest algorithm according to the model file, and carrying out the query statement adjustment of the relational database according to a test set and the prediction analysis model. A to-be-adjusted query statement in the test set is determined, the execution speed of the to-be-adjusted query statement is smaller than a preset speed threshold value, a to-be-referenced query statement in the training set is obtained based on the to-be-adjusted query statement, and the execution speed of the to-be-referenced query statement is higher than the execution speed of the to-be-adjusted query statement; and based on the statement features of the to-be-referenced query statement, generating adjustment information of the to-be-adjusted query statement, and based on the adjustment information, adjusting the to-be-adjusted query statement. The to-be-adjusted query statement in the real-time query statement is determined by constructing the prediction analysis model, and the to-be-adjusted query statement is adjusted, so that the data query speed is increased, and the query time is shortened.

Description

Technical Field

[0001] The present invention relates to the technical field of relational databases, and in particular to a query statement adjustment method and device for a relational database based on a large model. Background Art

[0002] With the surge in data volume and the increasing complexity of business, enterprises are facing increasingly prominent database performance issues in their IT infrastructure. Modern enterprise operations rely on efficient data management and query response, so database performance has become one of the core competitiveness of enterprise information systems. However, traditional SQL tuning methods often rely on manual intervention by experienced database administrators (DBAs) and developers, which is particularly inefficient in complex business scenarios.

[0003] The traditional SQL tuning process usually includes the following steps: First, the DBA will identify potential performance bottlenecks based on the system's performance monitoring indicators (such as response time, CPU and memory usage); then, the DBA needs to manually analyze the query execution plan, evaluate the resource consumption of different SQL statements, and identify slow queries and unreasonable index usage. This process is not only time-consuming, but also due to the complexity of business rules and the diversity of data, manual tuning often cannot fully cover actual problems. Summary of the invention

[0004] In order to solve the above technical problems, the present invention is proposed. The embodiment of the present invention provides a query statement adjustment method for a relational database based on a large model, which solves the problem of low efficiency of manual SQL statement tuning in the prior art.

[0005] According to one aspect of the present invention, a query statement adjustment method for a relational database based on a large model is provided, comprising:

[0006] Acquire real-time query statements and historical query statements in a relational database; wherein the real-time query statements are a test set, and the historical query statements are a training set;

[0007] Persistently processing the training set to obtain a model file;

[0008] Based on the random forest algorithm and the model file, a prediction analysis model is constructed;

[0009] Determine, according to the test set and the prediction analysis model, a query statement to be adjusted in the test set; wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold;

[0010] Based on the query statement to be adjusted, obtaining a query statement to be referenced in the training set; wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted;

[0011] generating adjustment information of the query statement to be adjusted based on the statement feature of the query statement to be referenced;

[0012] The query statement to be adjusted is adjusted based on the adjustment information.

[0013] In one embodiment, before the training set is persisted to obtain a model file, the query statement adjustment method for a relational database based on a large model further includes:

[0014] Preprocessing the training set to obtain a preprocessed training set;

[0015] The step of performing persistence processing on the training set and the test set to obtain a model file of the training set includes:

[0016] The preprocessed training set is persisted to obtain a model file.

[0017] In one embodiment, preprocessing the training set to obtain a preprocessed training set includes:

[0018] Determining the statement type of the query statement in the training set;

[0019] Determining invalid statements in the query statements based on the statement types of the query statements in the training set;

[0020] Removing invalid statements from the query statements to obtain a training set after the removal;

[0021] Normalizing the removed training set to obtain a normalized training set;

[0022] Data annotation is performed on the normalized training set to obtain a preprocessed training set.

[0023] In one embodiment, the step of performing data labeling on the normalized training set to obtain a preprocessed training set includes:

[0024] Get system performance;

[0025] Based on the system performance, obtaining query execution time;

[0026] Determine that the statement in the normalized training set whose query execution time is greater than the preset execution time is the first query statement;

[0027] Determine in the normalized training set that the query execution time is less than the preset execution time as a second query statement; wherein the preprocessed training set includes the first query statement and the second query statement.

[0028] In one embodiment, determining the query statement to be adjusted in the test set according to the test set and the predictive analysis model includes:

[0029] Obtaining numerical features and trend features of each query statement to be tested in the test set;

[0030] If the trend feature of the query statement to be detected in the test set meets the first preset condition and the numerical feature of the query statement to be detected meets the second preset condition, the query statement to be detected is determined to be a query statement to be adjusted.

[0031] In one embodiment, if the trend feature of the query statement to be detected in the test set satisfies a first preset condition and the numerical feature of the query statement to be detected satisfies a second preset condition, determining that the query statement to be detected is a query statement to be adjusted includes:

[0032] Obtaining trend features and numerical features of the second query statement in the model file;

[0033] If the trend feature of the query statement to be detected matches the trend feature of the second query statement in the model file and the numerical feature of the query statement to be detected matches the numerical feature of the second query statement in the model file, the query statement to be detected is determined to be a query statement to be adjusted.

[0034] In one embodiment, the step of persisting the training set to obtain a model file includes:

[0035] Extracting a plurality of trend features from the training set;

[0036] Extracting multiple numerical features from the training set;

[0037] Fusing multiple trend features to obtain a first fused feature;

[0038] Fusing multiple numerical features to obtain a second fused feature;

[0039] Constructing the first fused features and the second fused features into a feature set;

[0040] The feature set is persistently stored as a model file.

[0041] In one embodiment, extracting a plurality of numerical features from the training set includes:

[0042] Extracting structural features from the training set;

[0043] Extracting statistical features from the training set;

[0044] Extracting frequency features from the training set; wherein the multiple numerical features include the structural features, the statistical features and the frequency features.

[0045] In one embodiment, fusing the multiple trend features to obtain a first fused feature includes:

[0046] Get the trend characteristics of the lag time period of the current time period;

[0047] According to the trend characteristics of the current time period and the trend characteristics of the lag time period of the current time period, a first correlation value is calculated; wherein the calculation formula of the first correlation value is:

[0048] Among them, X t Indicates the trend characteristics of the current time period, X t+h It represents the trend characteristics of the lag time period of the current time period, h represents the lag time, that is, the time interval between two time points, represents the variance of the random process X, Cov(·) represents the covariance;

[0049] determining a first optimal correlation value among a plurality of first correlation values;

[0050] The trend feature corresponding to the first optimal correlation value and the trend feature of the current time period are fused to obtain a first fused feature.

[0051] According to another aspect of the present invention, there is provided a query statement adjustment device for a relational database based on a large model, comprising:

[0052] An acquisition module, used to acquire real-time query statements and historical query statements in a relational database; wherein the real-time query statements are a test set, and the historical query statements are a training set;

[0053] A construction module is used to persist the training set to obtain a model file; based on the random forest algorithm, a prediction analysis model is constructed according to the model file; based on the test set and the prediction analysis model, a query statement to be adjusted in the test set is determined; wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold;

[0054] A generating module, configured to obtain a query statement to be referenced in the training set based on the query statement to be adjusted; wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted; and based on the statement features of the query statement to be referenced, generate adjustment information of the query statement to be adjusted;

[0055] An adjustment module is used to adjust the query statement to be adjusted based on the adjustment information.

[0056] The query statement adjustment method and device of a relational database based on a large model provided by the present invention include: obtaining real-time query statements and historical query statements in a relational database, wherein the real-time query statements are test sets and the historical query statements are training sets, the training sets are persistently processed to obtain a model file, based on a random forest algorithm, a prediction analysis model is constructed according to the model file, and the query statements to be adjusted in the test set are determined according to the test set and the prediction analysis model, wherein the execution speed of the query statements to be adjusted is less than a preset speed threshold, based on the query statements to be adjusted, the query statements to be referenced in the training set are obtained, wherein the execution speed of the query statements to be referenced is higher than the execution speed of the query statements to be adjusted, based on the statement features of the query statements to be referenced, adjustment information of the query statements to be adjusted is generated, and based on the adjustment information, the query statements to be adjusted are adjusted. The present invention performs persistence processing on the training set, thereby directly obtaining the model file when applying the training set, without reloading the training set, thereby saving time and computing resources. Then, a prediction analysis model is constructed through the model file to determine the query statements to be adjusted in the real-time query statements, so that the query statements with slower execution speed can be quickly and accurately captured. The query statement to be adjusted is adjusted based on the query statement to be referenced, so that the query statement to be adjusted is executed faster, thereby improving the speed of querying data in the relational database and shortening the query time. BRIEF DESCRIPTION OF THE DRAWINGS

[0057] The above and other purposes, features and advantages of the present invention will become more apparent by describing the embodiments of the present invention in more detail in conjunction with the accompanying drawings. The accompanying drawings are used to provide a further understanding of the embodiments of the present invention and constitute a part of the specification. Together with the embodiments of the present invention, they are used to explain the present invention and do not constitute a limitation of the present invention. In the accompanying drawings, the same reference numerals generally represent the same components or steps.

[0058] Figure 1 It is a flowchart of a query statement adjustment method for a relational database based on a large model provided by an exemplary embodiment of the present invention.

[0059] Figure 2 It is a flowchart of a method for generating adjustment information provided by an exemplary embodiment of the present invention.

[0060] Figure 3 It is a structural diagram of a query statement adjustment device for a relational database based on a large model provided by an exemplary embodiment of the present invention.

[0061] Figure 4 It is a structural diagram of a query statement adjustment device for a relational database based on a large model provided by another exemplary embodiment of the present invention.

[0062] Figure 5 is a structural diagram of an electronic device provided by an exemplary embodiment of the present invention. DETAILED DESCRIPTION

[0063] Below, the exemplary embodiments according to the present invention will be described in detail with reference to the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments of the present invention, and it should be understood that the present invention is not limited to the exemplary embodiments described here.

[0064] Figure 1 FIG. 1 is a flow chart of a query statement adjustment method for a relational database based on a large model provided by an exemplary embodiment of the present invention. Figure 1 As shown, the query statement adjustment method of the relational database based on the large model includes:

[0065] Step 110: Acquire real-time query statements and historical query statements in the relational database, wherein the real-time query statements are the test set and the historical query statements are the training set.

[0066] In the embodiment of the present invention, since it is necessary to determine whether there are statements to be adjusted in the real-time query statements, it is necessary to obtain the real-time query statements and the historical query statements, and to analyze the historical query statements, so as to determine the statements to be adjusted from the real-time query statements as a reference, that is, to improve the statements that can improve the overall system performance in the process of querying relational data. By adjusting or optimizing the query statements to be adjusted, the performance of the query system can be automatically improved to prevent the risk of query business interruption.

[0067] The real-time query statements and historical query statements in the present invention are SQL statements. SQL (Structured Query Language) is a standard programming language for managing and operating relational databases.

[0068] Among them, the methods for obtaining real-time query statements and historical query statements in relational databases include: through MySQL plugins and agents, we can efficiently collect slow SQL queries, full SQL and other information in the database, and obtain SQL execution plan information by calling the API (Application Programming Interface) interface of the relational database.

[0069] Among them, the MySQL plug-in is a flexible plug-in mechanism that can monitor and collect all SQL information in the database in real time through specific audit plug-ins. These plug-ins can help us record the execution of SQL statements for subsequent analysis.

[0070] The agent is an independent application that is responsible for communicating with the database and collecting relevant data. By configuring the agent, we can specify the type of data to be collected and the frequency of collection, so that the agent can regularly collect slow SQL query information in the database.

[0071] The collected information includes key indicators such as the execution time, execution frequency, and resource consumption of SQL statements. These data are crucial for performance analysis and optimizing database queries, and can help identify performance bottlenecks and make corresponding adjustments.

[0072] Among them, slow query refers to SQL query that executes slowly in the database. Full SQL (SQL statement that queries all data stored in the database (i.e., full data). Full data refers to all data stored in the database, including newly added, modified, and deleted data), SQL execution plan (an execution plan generated by the database optimizer after parsing and optimizing SQL queries), etc.

[0073] Step 120: The training set is persisted to obtain a model file.

[0074] In the embodiment of the present invention, in order to ensure the consistency and reusability of data when model training is performed at different times or in different environments, the training set is persisted and saved, so that the same data set can be used for each model training, thereby reducing the model performance fluctuation caused by data changes. Persistence processing can speed up the process of model training and prediction. Especially when processing big data, persistent training sets can avoid reloading and processing data from the original data each time training, thereby saving time and computing resources.

[0075] Step 130: Based on the random forest algorithm and the model file, a prediction analysis model is constructed.

[0076] In an embodiment of the present invention, a prediction analysis model is constructed based on a random forest algorithm, so that the query statement to be adjusted in the test set can be predicted by the prediction analysis model.

[0077] Specifically, in order to ensure the quality of the training set, it is necessary to process missing values, outliers, and re-data to obtain a clean training set without other data interference problems. Construct a preset number of decision trees, and set the maximum depth, minimum number of sample splits, minimum number of sample leaves, maximum number of features, etc. of each decision tree. Input the training set into the constructed decision tree for training to obtain a predictive analysis model. Among them, the maximum depth represents the depth of the limit number. The minimum number of sample splits represents the minimum number of samples required for node splitting. The minimum number of sample leaves represents the minimum number of samples required for leaf nodes. The maximum number of features represents the number of features considered when each tree splits.

[0078] Step 140: Determine the query statements to be adjusted in the test set according to the test set and the prediction analysis model, wherein the execution speed of the query statements to be adjusted is less than a preset speed threshold.

[0079] In the embodiment of the present invention, by inputting the real-time query statement into the prediction analysis model, it is possible to determine the query statement to be adjusted in the test set, and the execution speed of the query statement to be adjusted is less than the preset speed threshold, that is, the execution speed of the query statement to be adjusted is slow and the execution time is long. Therefore, adjusting the query statement to be adjusted can make its execution speed faster and improve the query performance of the data in the query relational data.

[0080] Step 150: based on the query statement to be adjusted, obtaining the query statement to be referenced in the training set, wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted.

[0081] In an embodiment of the present invention, in order to adjust the query statement to be adjusted and make the execution speed of the statement to be adjusted faster and the execution time shorter, a query statement to be referenced can be obtained from a training set, and the query statement to be referenced is related to the statement to be adjusted, and the query statement to be referenced is adjusted through the statement structure of the query statement to be referenced.

[0082] Step 160: Generate adjustment information of the query statement to be adjusted based on the statement features of the query statement to be referenced.

[0083] In an embodiment of the present invention, an execution result of a query statement to be adjusted is determined, a query statement to be adjusted whose similarity with the execution result is greater than a preset similarity threshold is obtained from a training set, a query statement to be adjusted whose numerical feature corresponds to a numerical value less than a preset numerical threshold is selected as a target query statement, trend features in the target query statement are weighted averaged to obtain a first average value, numerical features in the target query statement are weighted averaged to obtain a second average value, and adjustment information of the query statement to be adjusted is generated based on the first average value and the second average value.

[0084] Specifically, generating adjustment information of the query statement to be adjusted according to the first average value and the second average value includes: replacing the first average value with the trend feature of the query statement to be adjusted, and replacing the second average value with the numerical feature of the query statement to be adjusted.

[0085] In the process of data query optimization, we first need to determine the query statement to be adjusted and analyze its execution results. Next, we extract the query statements whose similarity with the execution result is higher than the preset similarity threshold from the training set to find possible reference samples. In order to further refine the adjustment, we screen out the reference query statements whose numerical features correspond to values ​​less than the preset numerical threshold (the purpose is to select the reference query statements with shorter execution time and faster execution speed) as the target query statements. Next, we perform weighted average on the trend features in the target query statement to obtain a first average value, and perform weighted average on its numerical features to obtain a second average value. These two average values ​​will be used as the basis for adjustment to generate adjustment information for the query statement to be adjusted. Specifically, the adjustment information will include replacing the trend features in the query statement to be adjusted with the first average value, and replacing its numerical features with the second average value, so as to optimize the query performance and improve the execution efficiency. This process not only takes into account the numerical relationship between similarity and features, but also strengthens the significance of important features through weighted average, thereby ensuring that the generated adjustment information is highly targeted and effective.

[0086] Optionally, the adjustment information includes directly replacing the query statement to be referenced with the query statement to be adjusted. Identify the features with excellent performance (such as efficient filtering conditions or aggregation functions) from the query statement to be referenced. Replace the features (such as conditions, sorting methods) in the query statement to be adjusted with the corresponding features in the query to be referenced. For example, if the query to be referenced uses a better index field or filtering condition, replace the corresponding part in the query to be adjusted.

[0087] Optionally, analyze the numerical features in the query statement to be referenced, especially those values ​​that are less than a preset threshold. Optimize the parameter value in the query statement to be adjusted based on the first average value and the second average value. For example, if the parameter value of the query statement to be referenced is 10, and the corresponding parameter of the query statement to be adjusted is 20, adjust it to 15.

[0088] Optionally, valid indexes are identified according to the execution plan of the query statement to be referenced, and corresponding indexes are added to the query statement to be adjusted. For example, if the query statement to be referenced uses a composite index on the target column, and the query statement to be adjusted lacks the index, the index is added to the query statement to be adjusted.

[0089] Step 170: Adjust the query statement to be adjusted based on the adjustment information.

[0090] In the embodiment of the present invention, the prediction analysis model is used to conduct in-depth analysis and prediction of SQL queries to evaluate their execution plans, resource consumption and index efficiency. According to the performance analysis results, targeted optimization suggestions are put forward, such as improving query statements, index optimization, database configuration adjustment and query cache application, and finally a detailed SQL performance analysis report is generated. In addition, through the database console, the results and analysis reports of SQL optimization can be intuitively displayed, so that users can easily understand and apply the optimization suggestions.

[0091] The query statement adjustment method of a relational database based on a large model provided by the present invention comprises: obtaining real-time query statements and historical query statements in a relational database, wherein the real-time query statements are test sets, and the historical query statements are training sets, and the training sets are persistently processed to obtain a model file, and based on a random forest algorithm, a prediction analysis model is constructed according to the model file, and the query statements to be adjusted in the test set are determined according to the test set and the prediction analysis model, wherein the execution speed of the query statements to be adjusted is less than a preset speed threshold, and based on the query statements to be adjusted, the query statements to be referenced in the training set are obtained, wherein the execution speed of the query statements to be referenced is higher than the execution speed of the query statements to be adjusted, and based on the statement features of the query statements to be referenced, adjustment information of the query statements to be adjusted is generated, and the query statements to be adjusted are adjusted based on the adjustment information. The present invention directly obtains the model file when applying the training set by persistently processing the training set, and does not need to reload the training set, thereby saving time and computing resources. Then, a prediction analysis model is constructed through the model file to determine the query statements to be adjusted in the real-time query statements, so that the query statements with slower execution speed can be quickly and accurately captured. The query statement to be adjusted is adjusted based on the query statement to be referenced, so that the query statement to be adjusted is executed faster, thereby improving the speed of querying data in the relational database and shortening the query time.

[0092] In one embodiment, before step 120, the query statement adjustment method of the relational database based on the large model can be specifically implemented as follows: preprocessing the training set to obtain a preprocessed training set; step 120 can be specifically implemented as follows: persisting the preprocessed training set to obtain a model file.

[0093] In an embodiment of the present invention, when preprocessing the training set, the data must first be cleaned to remove missing values ​​and outliers to ensure the integrity and consistency of the data. Next, the categorical features in the SQL query (such as connection type, index usage, etc.) are uniquely encoded to convert them into numerical features. In addition, the numerical features (such as execution time, CPU usage, etc.) are standardized to eliminate the impact of dimensions, and finally all features are integrated into a structured data frame to form a preprocessed training set. This process ensures that the model can effectively learn and predict the performance of SQL queries.

[0094] Specifically, the query statement adjustment method of the relational database based on the big model can be specifically implemented as follows: determining the statement type of the query statement in the training set; based on the statement type of the query statement in the training set, determining the invalid statements in the query statement; removing the invalid statements in the query statement to obtain a training set after removal; normalizing the training set after removal to obtain a normalized training set; and data labeling the normalized training set to obtain a preprocessed training set.

[0095] In an embodiment of the present invention, by parsing the query statements in the training set, the type of each statement (such as SELECT, INSERT, UPDATE, DELETE, etc.) is determined. Based on these types, invalid statements are identified, such as queries with grammatical errors or statements that do not conform to business logic, statements for JDBC connections, statements for client login, statements for transaction submission, etc., and removed from the training set to form a removed training set. Next, the removed training set is normalized to ensure that all features are on the same scale and eliminate dimensional effects. Finally, the normalized training set is data labeled according to preset standards, such as marking the performance indicators of each query (such as execution time), thereby obtaining the final preprocessed training set, ready for model training and prediction.

[0096] Among them, JDBC connection statements include incorrect connection strings and missing required parameters. Client login statements include invalid user names or passwords, operations with insufficient permissions, etc. Transaction commit statements include statements that commit transactions without starting them, statements that commit transactions in read-only mode, etc.

[0097] In one embodiment, a query statement adjustment method for a relational database based on a large model can be specifically implemented as follows: obtaining system performance; based on the system performance, obtaining query execution time; determining that the statement in the normalized training set whose query execution time is greater than the preset execution time is the first query statement; determining that the statement in the normalized training set whose query execution time is less than the preset execution time is the second query statement; wherein the preprocessed training set includes the first query statement and the second query statement.

[0098] In the embodiment of the present invention, in order to facilitate the model to identify the query statements that need to be adjusted, it is necessary to perform data annotation on the normalized training set, thereby helping the model to identify the query statements to be adjusted.

[0099] Specifically, the system performance of the query system is obtained, and the query execution time is obtained based on the system performance. Then, a preset execution time is set to classify the statements in the training set into slow query statements and fast query statements, that is, the first query statement and the second query statement. The first query statement has a faster execution speed and is a fast query statement, while the second query statement has a slower execution speed and is a slow query statement. Through this classification, queries with poor performance and queries with good performance can be effectively identified, providing a basis for subsequent performance optimization and model training.

[0100] Figure 2 It is a flowchart of a method for generating adjustment information provided by an exemplary embodiment of the present invention.

[0101] like Figure 2 As shown, step 140 may include:

[0102] Step 141: Obtain the numerical features and trend features of each query statement to be tested in the test set.

[0103] In an embodiment of the present invention, numerical features (such as query execution time, CPU usage, memory consumption, etc.) can provide specific quantitative indicators of the resources consumed by the query during execution, and help analyze the performance bottleneck of the current query. Trend features (such as the trend of execution time over time, the impact of different data volumes on execution time, etc.) can reveal the dynamic changes in query performance and help identify potential problems, such as whether performance decreases significantly when the amount of data increases. Through numerical features, you can intuitively see which queries execute slowly under specific circumstances, thereby providing a basis for determining optimization priorities. Trend features help find specific patterns or periodic problems, such as performance fluctuations of certain queries within a specific time period, so that targeted adjustments can be made.

[0104] Step 142: If the trend feature of the query statement to be detected in the test set meets the first preset condition and the numerical feature of the query statement to be detected meets the second preset condition, then determine that the query statement to be detected is a query statement to be adjusted.

[0105] In the embodiment of the present invention, when both the trend feature and the value feature in the query statement to be detected meet the preset conditions, it means that the query statement to be detected is a query statement to be adjusted, that is, a query statement with a slower execution speed.

[0106] In one embodiment, step 142 can be specifically implemented as follows: obtaining the trend characteristics and numerical characteristics of the second query statement in the model file; if the trend characteristics of the query statement to be detected match the trend characteristics of the second query statement in the model file and the numerical characteristics of the query statement to be detected match the numerical characteristics of the second query statement in the model file, then determining that the query statement to be detected is a query statement to be adjusted.

[0107] In an embodiment of the present invention, in order to determine whether the query statement to be detected in the test set is a query statement to be adjusted, it is necessary to obtain the trend feature and the numerical feature of the second query statement in the model file. If the trend feature in the query statement to be detected matches the trend feature of the second query statement in the model file (or the similarity between the trend feature in the query statement to be detected and the trend feature of the second query statement in the model file is greater than a first preset similarity threshold), and the numerical feature in the query statement to be detected matches the numerical feature of the second query statement in the model file (or the similarity between the numerical feature in the query statement to be detected and the numerical feature of the second query statement in the model file is greater than a second preset similarity threshold), then it is determined that the query statement to be detected is a query statement to be adjusted.

[0108] In one embodiment, step 120 can be specifically implemented as follows: extracting multiple trend features from a training set; extracting multiple numerical features from a training set; fusing multiple trend features to obtain a first fused feature; fusing multiple numerical features to obtain a second fused feature; constructing the first fused feature and the second fused feature into a feature set; and persistently storing the feature set as a model file.

[0109] In an embodiment of the present invention, when processing a training set, it is first necessary to extract multiple trend features from it. These features may include the changing trend of query execution time, the fluctuation of CPU and memory usage over time, the change of query frequency, etc., to capture the dynamic behavior of query performance. At the same time, it is also necessary to extract multiple numerical features, which may cover specific numerical indicators of query execution, such as average execution time, maximum execution time, response time, number of rows scanned, number of rows returned, etc. Next, the extracted multiple trend features are fused to generate a new feature vector, called the first fusion feature, which can comprehensively reflect the time variation law of query performance. Similarly, multiple numerical features are fused to generate a second fusion feature to provide quantitative performance during query execution. Finally, the first fusion feature and the second fusion feature are combined to construct a complete feature set, which will contain a comprehensive description of query performance. In order to facilitate subsequent model training and application, this feature set will be persistently stored as a model file to ensure that it can be quickly loaded and called in future use, thereby achieving efficient performance analysis and optimization.

[0110] Before converting a feature set to a model file, you first need to format the feature set. Formatting methods include CSV, JSON, or dedicated binary formats (such as Pickle, Joblib, etc.). Choosing the right formatting method can ensure the readability and ease of use of the data. Use machine learning frameworks (such as TensorFlow, PyTorch, scikit-learn, etc.) to build the model. During the training process, the formatted feature set is used as input data to train the model to learn the relationship between the features and the target variable. After training is completed, use the save function provided by the corresponding framework to persist the model as a file.

[0111] In one embodiment, step 120 may be specifically implemented as follows: extracting structural features from the training set; extracting statistical features from the training set; extracting frequency features from the training set; wherein the multiple numerical features include structural features, statistical features, and frequency features.

[0112] In the embodiment of the present invention, structural features are extracted from the structure of the SQL statements in the training set, such as the depth of the query, the number of table connections, the number of subqueries, etc. Statistical features are calculated during the execution of the SQL statements, such as the execution time, the number of rows returned, the number of rows scanned, etc. Frequency features are calculated from the SQL statements, for example, the number of times each query is executed within a specific time window is calculated.

[0113] Similarly, the structural features, statistical features, frequency features and trend features in the test set can be extracted and input into the predictive analysis model to predict the test set and determine the query statements to be adjusted.

[0114] In one embodiment, step 120 can be specifically implemented as follows: obtaining trend characteristics of a lag time period of the current time period; calculating a first correlation value based on the trend characteristics of the current time period and the trend characteristics of the lag time period of the current time period; determining a first optimal correlation value among multiple first correlation values; and fusing the trend characteristics corresponding to the first optimal correlation value and the trend characteristics of the current time period to obtain a first fusion feature.

[0115] In the embodiment of the present invention, in data analysis, it is first necessary to obtain the trend features of the current time period, which may include dynamic changes in some time series data, such as sales, user visits or system load. Next, we also need to extract the trend features of the lag time period of the current time period, which reflect the data performance in the previous time period, thereby helping to identify potential patterns and changing trends. By comparing the trend features of the current time period with the trend features of the lag time period, we can calculate the first correlation value, which is usually evaluated by statistical methods such as correlation coefficients or regression analysis. The first optimal correlation value is then determined from multiple calculated first correlation values, which represents the most significant correlation between the current time period feature and the lag time period feature. Finally, the trend features of the lag time period corresponding to the first optimal correlation value are fused with the trend features of the current time period to generate a new feature vector, namely the first fused feature. This fused feature not only integrates the data performance of the current time period, but also takes into account the past trend information, thereby providing a richer and more comprehensive feature basis for subsequent analysis or modeling, which helps to improve the accuracy of prediction and the performance of the model.

[0116] Among them, the calculation formula of the first related value is: Among them, X t Indicates the trend characteristics of the current time period, X t+h It indicates the trend characteristics of the lag time period of the current time period. h indicates the lag time (lag), which indicates the time interval between two time points. The variance of the random process X is represented by Cov(·), which represents the degree of dispersion of the random process. The autocorrelation function R X (h) is defined as the variance of the sequence when h = 0, and is defined as the covariance of the sequence and its time series delayed by h units divided by the variance of the sequence when h > 0. The calculation formula for the second correlation value is the same as the calculation formula for the first correlation value, and will not be repeated here.

[0117] In one embodiment, step 120 can be specifically implemented as follows: obtaining the numerical characteristics of the lag time period of the current time period; calculating the second correlation value based on the numerical characteristics of the current time period and the numerical characteristics of the lag time period of the current time period; determining the second optimal correlation value among multiple second correlation values; fusing the numerical characteristics corresponding to the second optimal correlation value and the numerical characteristics of the current time period to obtain a second fused feature.

[0118] In an embodiment of the present invention, when performing time series analysis, it is first necessary to obtain the numerical features of the current time period, which may include statistical indicators such as average value, maximum value, minimum value, standard deviation, etc., reflecting the basic characteristics of the data in the time period. Next, the numerical features of the lag time period of the current time period are extracted, and these features can help us understand the performance of the data in the past period. By comparing the numerical features of the current time period with the numerical features of the lag time period, we can calculate the second correlation value, for example, using the Pearson correlation coefficient or other correlation metrics to evaluate the strength of the linear relationship between the two. Subsequently, from a plurality of calculated second correlation values, the second optimal correlation value is identified, which represents the most significant correlation between the current time period feature and the lag time period feature. Finally, the numerical features of the lag time period corresponding to the second optimal correlation value are fused with the numerical features of the current time period to form a new feature vector, namely the second fused feature. This fused feature not only integrates current and past information, improves the integrity and depth of the data, but also enhances the accuracy of the model in predicting future trends, thereby providing stronger support for subsequent analysis, decision-making or prediction.

[0119] Figure 3 FIG. 1 is a schematic diagram of a query statement adjustment device for a relational database based on a large model provided by an exemplary embodiment of the present invention. Figure 3 As shown, the query statement adjustment device of the relational database based on the big model includes: an acquisition module 201, which is used to acquire real-time query statements and historical query statements in the relational database; wherein the real-time query statements are the test set and the historical query statements are the training set; a construction module 202, which is used to persist the training set to obtain a model file; based on the random forest algorithm, a prediction analysis model is constructed according to the model file; according to the test set and the prediction analysis model, the query statement to be adjusted in the test set is determined; wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold; a generation module 203, which is used to acquire the query statement to be referenced in the training set based on the query statement to be adjusted; wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted; based on the statement features of the query statement to be referenced, adjustment information of the query statement to be adjusted is generated; an adjustment module 204, which is used to adjust the query statement to be adjusted based on the adjustment information.

[0120] The query statement adjustment device of the relational database based on the large model provided by the present invention comprises: an acquisition module acquires real-time query statements and historical query statements in the relational database, wherein the real-time query statements are test sets and the historical query statements are training sets, a construction module performs persistence processing on the training sets to obtain a model file, a prediction analysis model is constructed based on the random forest algorithm and the model file, and a query statement to be adjusted in the test set is determined according to the test set and the prediction analysis model, wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold, a generation module acquires a query statement to be referenced in the training set based on the query statement to be adjusted, wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted, and adjustment information of the query statement to be adjusted is generated based on the statement features of the query statement to be referenced, and an adjustment module adjusts the query statement to be adjusted based on the adjustment information. The present invention performs persistence processing on the training set, thereby directly acquiring the model file when applying the training set, without reloading the training set, thereby saving time and computing resources. Then, a prediction analysis model is constructed through the model file to determine the query statement to be adjusted in the real-time query statement, so that the query statement with a slower execution speed can be quickly and accurately captured. The query statement to be adjusted is adjusted based on the query statement to be referenced, so that the query statement to be adjusted is executed faster, thereby improving the speed of querying data in the relational database and shortening the query time.

[0121] An embodiment of the present invention provides a device for adjusting query statements in a relational database of a large model. The device embodiment can be implemented by software, or by hardware or a combination of software and hardware. From a hardware perspective, in addition to the CPU, memory, network interface, and non-volatile memory, the device in the embodiment where the device is located can generally include other hardware, such as a forwarding chip responsible for processing messages, etc. Taking software implementation as an example, as a device in a logical sense, it is formed by the CPU of the device where it is located reading the corresponding computer program instructions in the non-volatile memory into the memory for execution.

[0122] like Figure 4 As shown, before constructing module 202, the query statement adjustment device of the relational database based on the large model can be specifically configured as: preprocessing the training set to obtain a preprocessed training set; wherein, the constructing module 202 can be specifically configured as: persisting the preprocessed training set to obtain a model file.

[0123] In one embodiment, a query statement adjustment device for a relational database based on a large model can be specifically configured as follows: determining the statement type of the query statements in a training set; determining invalid statements in the query statements based on the statement type of the query statements in the training set; removing invalid statements in the query statements to obtain a training set after removal; normalizing the training set after removal to obtain a normalized training set; and data labeling the normalized training set to obtain a preprocessed training set.

[0124] In one embodiment, a query statement adjustment device for a relational database based on a large model can be specifically configured as follows: obtaining system performance; based on the system performance, obtaining query execution time; determining that the statement in the normalized training set whose query execution time is greater than the preset execution time is the first query statement; determining that the statement in the normalized training set whose query execution time is less than the preset execution time is the second query statement; wherein the preprocessed training set includes the first query statement and the second query statement.

[0125] In one embodiment, the construction module 202 may include: an acquisition unit 2021, used to acquire the numerical characteristics and trend characteristics of each query statement to be detected in the test set; a judgment unit 2022, used to determine that the query statement to be detected is a query statement to be adjusted if the trend characteristics of the query statement to be detected in the test set meet the first preset condition and the numerical characteristics of the query statement to be detected meet the second preset condition.

[0126] In one embodiment, the judgment unit 2022 can be specifically configured to: obtain the trend characteristics and numerical characteristics of the second query statement in the model file; if the trend characteristics of the query statement to be detected match the trend characteristics of the second query statement in the model file and the numerical characteristics of the query statement to be detected match the numerical characteristics of the second query statement in the model file, then determine that the query statement to be detected is a query statement to be adjusted.

[0127] In one embodiment, the construction module 202 can be specifically configured as: a first extraction unit 2023, used to extract multiple trend features in the training set; a second extraction unit 2024, used to extract multiple numerical features in the training set; a first fusion unit 2025, used to fuse multiple trend features to obtain a first fused feature; a second fusion unit 2026, used to fuse multiple numerical features to obtain a second fused feature; a construction subunit 2027, used to construct the first fused feature and the second fused feature into a feature set; a persistence unit 2028, used to persist the feature set as a model file.

[0128] In one embodiment, the second extraction unit 2024 can be specifically configured to: extract structural features in the training set; extract statistical features in the training set; extract frequency features in the training set; wherein the multiple numerical features include structural features, statistical features and frequency features.

[0129] In one embodiment, the first fusion unit 2025 can be specifically configured to: obtain the trend characteristics of the lag time period of the current time period; calculate the first correlation value according to the trend characteristics of the current time period and the trend characteristics of the lag time period of the current time period; wherein the calculation formula of the first correlation value is:

[0130] Among them, X t Indicates the trend characteristics of the current time period, X t+h It represents the trend characteristics of the lag time period of the current time period, h represents the lag time, that is, the time interval between two time points, represents the variance of the random process X, and Cov(·) represents the covariance; determining a first optimal correlation value among multiple first correlation values; fusing the trend feature corresponding to the first optimal correlation value and the trend feature of the current time period to obtain a first fusion feature.

[0131] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the storage medium stores a computer program for executing the query statement adjustment method for a relational database based on a large model according to any of the above embodiments.

[0132] In addition to the above-mentioned methods and devices, an embodiment of the present invention may also be a computer program product, which includes computer program instructions, which, when executed by a processor, enable the processor to execute the steps of the query statement adjustment method for a relational database based on a large model according to various embodiments of the present invention described in the above "Exemplary Method" section of this specification.

[0133] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising: a processor; a memory for storing instructions executable by the processor; and a processor for executing the query statement adjustment method for a relational database based on a large model according to any of the above embodiments.

[0134] In addition, an embodiment of the present invention may also be a computer-readable storage medium having computer program instructions stored thereon, which, when executed by a processor, enables the processor to execute the steps of the query statement adjustment method for a relational database based on a large model according to various embodiments of the present invention described in the above “Exemplary Method” section of this specification.

[0135] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A query statement adjustment method for a relational database based on a large model, characterized in that: include: Acquire real-time query statements and historical query statements in a relational database; wherein the real-time query statements are a test set, and the historical query statements are a training set; Persistently processing the training set to obtain a model file; Based on the random forest algorithm and the model file, a prediction analysis model is constructed; Determine, according to the test set and the prediction analysis model, a query statement to be adjusted in the test set; wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold; Based on the query statement to be adjusted, obtaining a query statement to be referenced in the training set; wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted; generating adjustment information of the query statement to be adjusted based on the statement feature of the query statement to be referenced; The query statement to be adjusted is adjusted based on the adjustment information.

2. The query statement adjustment method for a relational database based on a large model according to claim 1, characterized in that: Before the training set is persisted to obtain the model file, the method further includes: Preprocessing the training set to obtain a preprocessed training set; The step of performing persistence processing on the training set and the test set to obtain a model file of the training set includes: The preprocessed training set is persisted to obtain a model file.

3. The query statement adjustment method for a relational database based on a large model according to claim 2 is characterized in that: The preprocessing of the training set to obtain a preprocessed training set includes: Determining the statement type of the query statement in the training set; Determining invalid statements in the query statements based on the statement types of the query statements in the training set; Removing invalid statements from the query statements to obtain a training set after the removal; Normalizing the removed training set to obtain a normalized training set; Data annotation is performed on the normalized training set to obtain a preprocessed training set.

4. The query statement adjustment method for a relational database based on a large model according to claim 3 is characterized in that: The step of labeling the normalized training set to obtain a preprocessed training set includes: Get system performance; Based on the system performance, obtaining query execution time; Determine that the statement in the normalized training set whose query execution time is greater than the preset execution time is the first query statement; Determine in the normalized training set that the query execution time is less than the preset execution time as a second query statement; wherein the preprocessed training set includes the first query statement and the second query statement.

5. The query statement adjustment method for a relational database based on a large model according to claim 4 is characterized in that: The determining, according to the test set and the prediction analysis model, the query statement to be adjusted in the test set comprises: Obtaining numerical features and trend features of each query statement to be tested in the test set; If the trend feature of the query statement to be detected in the test set meets the first preset condition and the numerical feature of the query statement to be detected meets the second preset condition, the query statement to be detected is determined to be a query statement to be adjusted.

6. The query statement adjustment method for a relational database based on a large model according to claim 5 is characterized in that: If the trend feature of the query statement to be detected in the test set satisfies a first preset condition and the numerical feature of the query statement to be detected satisfies a second preset condition, determining that the query statement to be detected is a query statement to be adjusted includes: Obtaining trend features and numerical features of the second query statement in the model file; If the trend feature of the query statement to be detected matches the trend feature of the second query statement in the model file and the numerical feature of the query statement to be detected matches the numerical feature of the second query statement in the model file, the query statement to be detected is determined to be a query statement to be adjusted.

7. The query statement adjustment method for a relational database based on a large model according to claim 1, characterized in that: The step of performing persistence processing on the training set to obtain a model file includes: Extracting a plurality of trend features from the training set; Extracting multiple numerical features from the training set; Fusing multiple trend features to obtain a first fused feature; Fusing multiple numerical features to obtain a second fused feature; Constructing the first fused features and the second fused features into a feature set; The feature set is persistently stored as a model file.

8. The query statement adjustment method for a relational database based on a large model according to claim 7, characterized in that: The extracting of multiple numerical features from the training set comprises: Extracting structural features from the training set; Extracting statistical features from the training set; Extracting frequency features from the training set; wherein the multiple numerical features include the structural features, the statistical features and the frequency features.

9. The query statement adjustment method for a relational database based on a large model according to claim 7, characterized in that: The step of fusing the plurality of trend features to obtain a first fused feature includes: Get the trend characteristics of the lag time period of the current time period; According to the trend characteristics of the current time period and the trend characteristics of the lag time period of the current time period, a first correlation value is calculated; wherein the calculation formula of the first correlation value is: Among them, X t Indicates the trend characteristics of the current time period, X t+h It represents the trend characteristics of the lag time period of the current time period, h represents the lag time, that is, the time interval between two time points, represents the variance of the random process X, Cov(·) represents the covariance; determining a first optimal correlation value among a plurality of first correlation values; The trend feature corresponding to the first optimal correlation value and the trend feature of the current time period are fused to obtain a first fused feature.

10. A query statement adjustment device for a relational database based on a large model, characterized in that: include: An acquisition module, used to acquire real-time query statements and historical query statements in a relational database; wherein the real-time query statements are a test set, and the historical query statements are a training set; A construction module is used to persist the training set to obtain a model file; based on the random forest algorithm, a prediction analysis model is constructed according to the model file; based on the test set and the prediction analysis model, a query statement to be adjusted in the test set is determined; wherein the execution speed of the query statement to be adjusted is less than a preset speed threshold; A generating module, configured to obtain a query statement to be referenced in the training set based on the query statement to be adjusted; wherein the execution speed of the query statement to be referenced is higher than the execution speed of the query statement to be adjusted; and based on the statement features of the query statement to be referenced, generate adjustment information of the query statement to be adjusted; An adjustment module is used to adjust the query statement to be adjusted based on the adjustment information.

Images