Database management method and apparatus

Abstract

This application provides a database management method and apparatus. It monitors database query actions and query times using a Druid architecture. Based on historical query actions and their corresponding historical query times, a preset time threshold for slow query actions is determined. When a query time exceeds the preset threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and slow query actions are optimized according to priority using a preset optimization model. This method effectively solves the shortcomings of traditional techniques in optimizing query performance, which can lead to lag and affect software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience.

Description

Technical Field

[0001] This application relates to the field of computer technology, specifically to a database management method and apparatus. Background Technology

[0002] During the long-term operation of the software, as data continues to accumulate in the production environment, the software's query performance gradually declines, leading to longer response times. The main reason for the decline in query performance is that the growth in data scale has not been matched with corresponding query optimization strategies. When the amount of data in the software reaches a certain threshold, if the index design is unreasonable, the query execution plan is not optimized, or there is a lack of historical data archiving and partitioning strategies, the efficiency of database queries will drop significantly, resulting in reduced system throughput and increased user request latency.

[0003] In existing technologies, query performance optimization is mainly achieved through Structured Query Language (SQL). The speed of SQL statement execution directly affects the length of time resources are occupied. However, slow query actions are often passive fault feedback mechanisms, which are lengthy and inefficient, resulting in a lag in query performance optimization, affecting software stability and availability, and reducing user experience. Summary of the Invention

[0004] To address the problems in the prior art, this application provides a database management method and apparatus that can effectively solve the shortcomings of traditional technologies in terms of lagging query performance optimization, affecting software stability and usability, significantly improving the efficiency and stability of query performance, and enhancing the user experience.

[0005] To solve at least one of the above problems, this application provides the following technical solution:

[0006] Firstly, this application provides a database management method, including:

[0007] By monitoring database query actions and corresponding query times through the Druid architecture, and obtaining historical query actions and corresponding historical query times, a preset time threshold for slow query actions is determined based on the historical query times.

[0008] If the query time exceeds the preset time threshold, the query action will be identified as a slow query action, and the query data of the slow query action will be stored in the Druid architecture.

[0009] The query data is synchronized to a preset sandbox environment. Cost verification is performed on the query data through the preset sandbox environment to obtain the cost verification results of the structured data objects and determine the priority of each cost verification result.

[0010] By using a pre-defined optimization model, the query data corresponding to the verification results are optimized according to the priority from high to low cost to obtain the optimization method for slow query actions, and the slow query actions are optimized according to the optimization method.

[0011] Furthermore, it also includes: classifying slow query actions and their corresponding historical slow query times according to a preset time window to obtain a classification dataset, which includes a weekday category dataset and a holiday category dataset;

[0012] The historical query time corresponding to the P99 percentile in each category dataset is determined as the basic threshold for query time in each category dataset. If the historical query time exceeds the basic threshold, the query action corresponding to the historical query time is designated as a historical slow query action.

[0013] Obtain the historical database load rate corresponding to the historical slow query actions, and adjust the preset time threshold of each category dataset based on the historical database load rate and the basic threshold of query time.

[0014] Furthermore, database load factor includes CPU utilization and I / O latency; it also includes:

[0015] When the CPU utilization rate corresponding to the historical query action is greater than the preset utilization rate threshold, the basic threshold for query time is reduced by the preset reduction coefficient to obtain the preset time threshold.

[0016] If the input / output waiting time corresponding to the execution of a historical query action is greater than the preset input / output waiting time, the basic threshold for query time consumption is increased according to the preset adjustment coefficient to obtain the preset time consumption threshold.

[0017] Furthermore, it also includes: obtaining the query environment where the query data is located, replicating the query environment in a preset sandbox environment, the query environment including table structure and index definition, histogram distribution of column statistics information and database configuration parameters;

[0018] Dual-channel validation is performed on the query data in a preset sandbox environment to obtain cost validation results. Dual-channel validation includes original query validation and optimized query validation.

[0019] Furthermore, it also includes: assigning initial weights to each cost verification result and receiving preset business coefficients, which include preset business criticality coefficients and preset time period sensitivity coefficients;

[0020] The initial weights of each cost verification result are adjusted based on preset business coefficients to obtain the weights of each cost verification result.

[0021] The weights of each cost verification result from high to low are determined as the priorities corresponding to each cost verification result from high to low.

[0022] Furthermore, the query data includes the query statement, the executed statement, the query time, and the data source and table information associated with the executed statement; it also includes:

[0023] The query data is parsed into a syntax tree, and the feature complexity of the query data is extracted according to preset conditions;

[0024] Based on syntax trees and feature complexity, the system matches query data in the optimization rule base and analyzes the optimal order of multi-table queries through graph neural networks to generate optimization methods for various slow query actions.

[0025] Furthermore, it also includes: updating the query statements corresponding to the optimized slow query actions to the database, and monitoring the query time corresponding to the current query action;

[0026] If the query takes longer than a preset time threshold, an alarm message will be sent to notify the user that the current query is a slow query.

[0027] Secondly, this application provides a database management device, comprising:

[0028] The first processing module is used to monitor the database query actions and the corresponding query time through the Druid architecture, and to obtain the database historical query actions and the historical query time corresponding to the historical query actions, and to determine the preset time threshold for slow query actions based on the historical query time.

[0029] The second processing module is used to identify the query action as a slow query action when the query time is greater than a preset time threshold, and to store the query data of the slow query action in the Druid architecture.

[0030] The third processing module is used to synchronize the query data to a preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result.

[0031] The optimization module is used to optimize the query data corresponding to the verification results according to the priority from high to low using a preset optimization model, thereby obtaining the optimization method for slow query actions, and optimizing the slow query actions according to the optimization method.

[0032] Thirdly, this application provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the database management method described above.

[0033] Fourthly, this application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the database management method described above.

[0034] Fifthly, this application provides a computer program product, including a computer program / instructions that, when executed by a processor, implement the steps of the database management method described above.

[0035] As can be seen from the above technical solution, this application provides a database management method and apparatus. It innovatively uses a Druid architecture to monitor database query actions and query times. Based on historical query actions and corresponding historical query times, a preset time threshold for slow query actions is determined. When the query time exceeds the preset time threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and slow query actions are optimized according to priority using a preset optimization model. This method can monitor the database using a Druid architecture to identify slow query actions, avoiding the tediousness and inaccuracy of manual troubleshooting. It improves the targeting of database performance optimization. Cost verification of query data is performed in the preset sandbox environment, providing detailed cost verification results for the optimization model. This allows for verification of the feasibility and effectiveness of optimization methods without affecting business operations, reducing potential risks to business operations, ensuring stable business operation, and optimizing corresponding slow query actions according to the priority of cost verification results. This efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database. This method effectively addresses the shortcomings of traditional techniques in optimizing query performance, which can lead to delays and negatively impact software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience. Attached Figure Description

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

[0037] Figure 1 This is a flowchart illustrating the database management method in an embodiment of this application;

[0038] Figure 2 This is a structural diagram of the database management device in the embodiments of this application;

[0039] Figure 3 This is a schematic diagram of the structure of the electronic device in the embodiments of this application.

[0040] Figure label:

[0041] Electronic device 9600, central processing unit 9100, memory 9140, communication module 9110, input unit 9120, audio processor 9130, display 9160, power supply 9170, buffer memory 9141, application / function storage unit 9142, data storage unit 9143, driver storage unit 9144, antenna 9111, speaker 9131, microphone 9132. Detailed Implementation

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

[0043] The acquisition, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.

[0044] To address the problems existing in current technologies, this application provides a database management method and apparatus. It innovatively uses a Druid architecture to monitor database query actions and query times. Based on historical query actions and their corresponding historical times, a preset time threshold for slow query actions is determined. When a query time exceeds the preset threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and a preset optimization model optimizes the slow query actions according to this priority. This method uses a Druid architecture to monitor the database and identify slow queries, avoiding the tedious and inaccurate manual investigation of slow queries. This improves the targeted nature of database performance optimization. Cost verification of query data is performed in the preset sandbox environment, providing detailed cost verification results for the optimization model. This allows for verification of the feasibility and effectiveness of optimization methods without affecting business operations, reducing potential risks to business operations and ensuring stable business operation. Furthermore, optimization of corresponding slow query actions based on the priority of cost verification results efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database.

[0045] To effectively address the shortcomings of traditional technologies in terms of query performance optimization lag, which affects software stability and usability, and to significantly improve query performance efficiency and stability while enhancing user experience, this application provides an embodiment of a database management method. See [link to embodiment]. Figure 1The database management method specifically includes the following:

[0046] Step S101: Monitor the database query actions and the corresponding query times through the Druid architecture, obtain the database historical query actions and the historical query times corresponding to the historical query actions, and determine the preset time threshold for slow query actions based on the historical query times.

[0047] Optionally, this embodiment monitors database query actions and corresponding query times using the Druid architecture. Druid is a high-performance and feature-rich database connection pool solution in the Java (a programming language) ecosystem, providing advanced features such as SQL monitoring, slow query analysis, and SQL execution statistics. It can effectively identify database performance bottlenecks. Druid has built-in SQL execution performance sampling and slow query logging functions, which allows developers to set the standard for slow queries (i.e., the preset time threshold in this application) through threshold configuration.

[0048] In addition, obtaining historical query actions and corresponding historical query times from the database can be achieved by accessing the database's historical operation log storage location or by obtaining the data from the Druid runtime log.

[0049] Furthermore, the granularity and frequency of collecting historical query actions and their corresponding historical query times can be defined. Based on the database load and business requirements, it can be determined whether to collect historical query actions and their times in real time, periodic polling, or based on specific trigger conditions, so as to ensure that the historical query actions and their times are obtained accurately without causing excessive impact on database performance.

[0050] Furthermore, a preset time threshold for slow queries is determined based on historical query times. This involves cleaning the collected historical query time data to remove obviously abnormal data points, such as excessively long query times caused by abnormal circumstances like network failures or sudden system malfunctions. Statistical analysis methods are used to determine statistical indicators such as the average, median, and standard deviation of historical query times, analyzing their distribution. Based on the business's sensitivity to database response speed and the characteristics of the historical query time distribution, a corresponding preset time threshold is determined. For example, if historical query times roughly follow a normal distribution, the preset time threshold can be determined by adding a certain number of standard deviations to the average. If historical query times show a skewed distribution, an appropriate quantile can be selected as the preset time threshold.

[0051] After determining the preset time consumption threshold, it is configured into the Druid architecture as a standard for judging whether a query action is a slow query action. Furthermore, a mechanism for regular evaluation and updating of the preset time consumption threshold can be established to adjust the preset time consumption threshold in a timely manner according to the development of database business and performance changes, so as to maintain its rationality and effectiveness.

[0052] This embodiment achieves accurate identification of slow query actions by monitoring database query actions and time consumption, and determining thresholds based on historical data. This avoids the inaccuracies and tediousness of manual investigation, improves the targeting of optimization methods, and dynamically sets preset time consumption thresholds based on historical query time consumption to ensure the rationality and effectiveness of preset time consumption thresholds.

[0053] Step S102: When the query time exceeds the preset time threshold, the query action is identified as a slow query action, and the query data of the slow query action is stored in the Druid architecture.

[0054] Optionally, in this embodiment, if Druid detects that the query time of the current query action is greater than a preset time threshold, the query action is identified as a slow query action. At the same time, the basic information of the current query action is recorded, including but not limited to the query statement, query initiation time, and query time. Contextual information related to the current query action is also collected, such as the application module to which the current query action belongs, the user's identity, and the database tables and fields involved in the query.

[0055] Furthermore, for identified slow query actions, they are moved from the temporary storage area to the slow query data storage area in the Druid architecture. The slow query data storage area has efficient data retrieval and analysis capabilities, can quickly respond to data query requests corresponding to optimization methods, and must ensure the integrity and security of the relevant data of the current query action to prevent data loss or leakage.

[0056] This embodiment stores slow query actions in a Druid architecture, facilitating centralized management of slow query actions. At the same time, the Druid architecture provides efficient data retrieval and analysis capabilities, and also protects the integrity and security of slow query actions.

[0057] Step S103: Synchronize the query data to the preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result.

[0058] Optionally, this embodiment establishes a secure and stable data transmission channel between the Druid architecture and the preset sandbox environment, and can completely and accurately synchronize the relevant query data of slow query actions stored in the Druid architecture to the preset sandbox environment through real-time synchronization.

[0059] Furthermore, during the data synchronization process, data consistency verification is performed to ensure that the query data of slow query actions received by the preset sandbox environment is consistent with the query data stored in the Druid architecture, thus avoiding deviations in subsequent cost verification due to data transmission errors.

[0060] In addition, in the preset sandbox environment, a database test instance for cost verification is built according to the same or similar database and configuration parameters as the actual production environment. The synchronized query data is loaded into the built database test instance. At the same time, the preset sandbox environment is isolated and protected to prevent the test operation from having any impact on the actual production environment.

[0061] Furthermore, by using a database test instance built in a pre-configured sandbox environment, the built-in query cost estimation function of the database test instance is enabled for the query data of each slow query action. This includes features such as a cost-based optimizer, analyzing the query execution plan, and calculating the estimated execution cost of the slow query action under the current database configuration and data state. The execution cost includes, but is not limited to, Central Processing Unit (CPU) utilization, memory usage, number of disk I / O operations, and network data transfer volume. These execution costs are quantified into specific numerical values, generating cost verification results for structured data objects.

[0062] Furthermore, in addition to using the built-in cost estimation function in the database test instance, the query statement of the slow query action can be run multiple times in conjunction with the actual test execution. The actual resource consumption of each run can be recorded, and the actual running data can be compared and analyzed with the estimated data to verify the accuracy of the estimation results. The cost verification results can be further refined and supplemented to make them closer to the actual running situation.

[0063] In addition, different slow query actions can be classified according to their importance and urgency. For example, slow query actions that affect core business processes and are directly related to user experience and business revenue can be classified as high priority, while slow query actions that have little impact on the business or are auxiliary to the business can be classified as low priority.

[0064] Furthermore, slow query actions within the same category can be further ranked based on factors such as the complexity of the query statement corresponding to the slow query action, the amount of data involved, and the execution frequency.

[0065] This embodiment synchronizes query data of slow query actions to a preset sandbox environment, which can perform cost verification without affecting actual business operations, reduce the potential risks of optimization operations, and obtain structured cost verification results by performing cost verification on query data through the preset sandbox environment, providing detailed and reliable data basis for optimization methods.

[0066] Step S104: Optimize the query data corresponding to the verification results according to the priority from high to low using the preset optimization model to obtain the optimization method for slow query actions, and optimize the slow query actions according to the optimization method.

[0067] Optionally, in this embodiment, based on the query data sorted by priority, the data with the highest priority is substituted into a preset optimization model. The preset optimization model may include various optimization strategies and algorithms, such as query rewriting, which optimizes the syntax structure of the query statement, including but not limited to adjusting the join order and adding query hints; index optimization, which creates, adjusts or rebuilds appropriate indexes to speed up queries; data partitioning optimization, which rationally partitions large-scale data tables to narrow the query scope; and materialized view optimization, which uses materialized views to pre-calculate and store frequently used query results to reduce the amount of real-time query computation.

[0068] The preset optimization model can determine one or more suitable combinations of optimization strategies based on the characteristics and cost of the query data, and generate specific optimization methods. These optimization methods include, but are not limited to, the required operation steps, expected effects, and potential impacts on the database, such as the increased space usage of index creation and the impact of query rewriting on other related queries. This facilitates the evaluation and implementation of optimization methods.

[0069] Furthermore, following the optimization approach, slow query actions can be optimized in a pre-defined sandbox environment. The optimized query execution process is simulated, and the database's query cost estimation function is combined with actual test execution to determine the query cost of the optimized slow query action. This cost is then compared with the cost before optimization to verify the effectiveness of the optimization scheme. If the optimized query cost does not achieve the expected reduction or other anomalies occur, such as inconsistent query results, the optimization method needs to be readjusted, and the optimization and verification repeated until a satisfactory optimization effect is achieved.

[0070] Furthermore, proven and effective optimization methods are applied to the actual production environment database to formally optimize slow query actions. After implementing the optimization operation in the production environment, the execution status of the query action and the overall performance indicators of the database are continuously monitored to ensure that the optimized query action can run stably and have a positive impact on database performance. At the same time, the execution effect data of the optimized query action can be recorded.

[0071] After optimization, the database query actions continue to be monitored in real time through the Druid architecture monitoring system, including optimized query actions and other new query actions, and new query data and query time are collected to update historical query actions and their corresponding historical query times, thereby updating the preset time threshold.

[0072] Furthermore, for newly emerging slow query actions or query actions whose performance has decreased after optimization, the cost verification, priority determination, and optimization operations are carried out again according to the above process, forming a continuous closed loop of database performance optimization, continuously improving the database query performance to meet the ever-evolving needs of the business.

[0073] This embodiment optimizes the database based on cost verification results from high to low priority, improving optimization efficiency. It also obtains and implements optimization methods through a preset optimization model, forming an optimization closed loop, continuously improving database query performance, adapting to business development needs, and enhancing the user experience.

[0074] This embodiment accurately identifies and optimizes slow query actions, reduces query time, improves overall database performance and response speed, enhances user experience, verifies optimization methods in a preset sandbox environment, reduces the impact of optimization operations on actual business, and improves optimization efficiency and effectiveness by determining the priority of cost verification results and rationally allocating optimization resources.

[0075] In some embodiments, determining a preset time threshold for slow query actions based on historical query times includes:

[0076] Slow query actions and their corresponding historical slow query times are classified according to a preset time window to obtain a categorized dataset, which includes a weekday category dataset and a holiday category dataset.

[0077] The historical query time corresponding to the P99 percentile in each category dataset is determined as the basic threshold for query time in each category dataset. If the historical query time exceeds the basic threshold, the query action corresponding to the historical query time is designated as a historical slow query action.

[0078] Obtain the historical database load rate corresponding to the historical slow query actions, and adjust the preset time threshold of each category dataset based on the historical database load rate and the basic threshold of query time.

[0079] Optionally, in this embodiment, slow query actions and their corresponding historical slow query times are classified according to a preset time window to obtain a categorized dataset. The preset time window can be set according to the cycle characteristics of business operations, such as weekdays and holidays, and corresponding weekday category datasets and holiday category datasets can be obtained.

[0080] Furthermore, while obtaining the categorized dataset, auxiliary information related to the current business operation status can also be collected, such as the identification of peak business periods and the real-time load of the database. This allows for a more accurate reflection of query characteristics under different business scenarios. At the same time, the categorized dataset can also be used to deeply analyze the impact of business operation modes on database query performance, thereby enabling more reasonable planning of business operations and optimization of database resource allocation.

[0081] This embodiment achieves the ability to accurately identify poorly performing query actions based on different business scenarios by classifying and processing historical query data and determining the basic threshold for query time. This avoids the misjudgment and omission problems caused by the traditional single threshold determination method, and improves the accuracy and targeting of slow query identification.

[0082] Furthermore, for each categorized dataset, a baseline threshold for query time is determined using the P99 percentile. The P99 percentile indicates that 99% of historical queries in that dataset have a query time lower than this threshold, while only 1% have a query time higher. Using the P99 percentile as the baseline threshold effectively filters out extreme and abnormal query times, ensuring the threshold's representativeness and stability. It accurately reflects the database query performance under normal business conditions. Historical queries exceeding the baseline threshold are classified as slow queries.

[0083] This embodiment realizes the periodic changes in business operations and the differences in database load under different business scenarios, so that the preset time threshold can dynamically adapt to the actual operating environment, better reflect the actual situation of database query performance, ensure that performance optimization measures can closely match business needs, and improve the performance of the database and user experience under different business scenarios.

[0084] In addition, obtain the historical database load rate corresponding to the historical slow query actions. The historical database load rate reflects the resource usage of the database during operation, including but not limited to CPU utilization, memory usage, and disk input / output (I / O) operation frequency.

[0085] Because the database needs to handle a large number of operation requests simultaneously, increased resource contention leads to a corresponding increase in historical query time when the historical database is under high load. By combining the historical database load rate and the basic query time threshold, a mathematical model can be established to dynamically adjust the preset time threshold for each category dataset.

[0086] For example, taking a weekday category dataset, assuming a normal load condition (load rate below 70%), the basic threshold for query time is T0. By analyzing historical query times and historical database load rates, it is found that for every 10% increase in historical database load rate, the average historical query time increases by [missing information]. Given a load rate of L, the preset time threshold T can be adjusted using the following formula: T = T0 × (1 + (L - L0) / 10 × ... T / T0), where L0 is the baseline load rate. Similarly, the preset time threshold for the holiday category dataset can be adjusted.

[0087] Furthermore, the impact of query time on real-time query complexity can be assessed. For highly complex queries involving numerous data joins, complex subqueries, or full-text searches, even under normal load, their execution time may approach or even exceed a preset time threshold. To more accurately adjust the preset time threshold, a query complexity assessment model can be introduced to score the complexity of each query action. Based on the score results, the preset time threshold can be adjusted accordingly. For example, for queries with high complexity scores, the corresponding preset time threshold can be appropriately increased to avoid misjudgment.

[0088] This embodiment implements a dynamic adjustment mechanism based on historical database load rate and real-time query complexity, which makes the preset time threshold more closely match the actual operating environment and query characteristics, thereby more accurately identifying the real slow query actions and improving the pertinence and effectiveness of database performance optimization.

[0089] This embodiment classifies and processes historical query actions and determines the basic query time threshold, accurately identifies poorly performing queries in various scenarios, avoids false positives and false negatives, and dynamically adjusts the preset time threshold according to business cycles and load differences, making it more in line with the actual operating environment and improving the user experience.

[0090] In some embodiments, database load rate includes CPU utilization and I / O latency;

[0091] The preset time thresholds for each category dataset are adjusted based on historical database load rate and query time thresholds, including:

[0092] When the CPU utilization rate corresponding to the historical query action is greater than the preset utilization rate threshold, the basic threshold for query time is reduced by the preset reduction coefficient to obtain the preset time threshold.

[0093] If the input / output waiting time corresponding to the execution of a historical query action is greater than the preset input / output waiting time, the basic threshold for query time consumption is increased according to the preset adjustment coefficient to obtain the preset time consumption threshold.

[0094] Optionally, in this embodiment, when the CPU utilization rate corresponding to the historical query action is greater than the preset CPU utilization rate threshold, it indicates that the database is experiencing a shortage of computing resources when processing the query action, resulting in increased query time. The basic threshold for query time is then lowered according to a preset adjustment coefficient to obtain the corresponding preset time threshold.

[0095] For example, if the preset CPU utilization threshold is 80% and the CPU utilization corresponding to historical query actions is 85%, then the new preset time consumption threshold is dynamically determined based on the preset reduction coefficient, which can be expressed by the following formula: Preset time consumption threshold = Basic query time consumption threshold × Reduction coefficient.

[0096] When the I / O wait time corresponding to the execution of a historical query action is greater than the preset I / O wait time threshold, it indicates that there is a delay in the database during data read and write operations, which leads to an increase in query time. The basic threshold for query time is adjusted upward according to the preset adjustment coefficient to obtain the corresponding preset time threshold.

[0097] For example, if the preset I / O wait time threshold is 100 milliseconds and the I / O wait time corresponding to the historical query action is 120 milliseconds, then the new preset time threshold is dynamically determined according to the preset adjustment coefficient, which can be expressed by the following formula: Preset time threshold = Basic query time threshold × Adjustment coefficient.

[0098] Furthermore, in practical applications, CPU utilization and I / O latency are usually interrelated and jointly affect query time. A comprehensive adjustment model can be established to integrate their effects, allowing for more accurate dynamic adjustment of the preset time threshold. This comprehensive adjustment model can be expressed by the following formula:

[0099] Preset time threshold = query time base threshold × (1 + weight 1 × (CPU utilization - preset CPU utilization threshold) / preset CPU utilization threshold + weight 2 × (IO wait time - preset IO wait time threshold) / preset IO wait time threshold);

[0100] Weight 1 and Weight 2 represent the relative importance of CPU utilization and I / O wait time in affecting query time, respectively, and can be obtained based on historical query data analysis and business characteristics.

[0101] Furthermore, analyzing the correlation between database load and slow query actions involves statistically analyzing the frequency and distribution characteristics of slow query actions under different load conditions to determine the sensitivity of load to query performance. For example, it was found that when CPU utilization exceeds 85%, the time taken for some types of query actions increases significantly, such as complex queries involving a large amount of computation. When I / O wait time exceeds 150 milliseconds, other types of query actions tend to become slow queries, such as queries involving a large amount of data scanning. Based on the correlation analysis results, more targeted preset time threshold adjustment strategies can be developed for different types of query actions.

[0102] This embodiment enables dynamic adjustment of the preset time consumption threshold based on factors affecting database load rate, making the preset time consumption threshold more closely match the actual operating environment, improving the accuracy of slow query identification, thereby enabling rapid response to database load fluctuations, ensuring the rationality of the preset time consumption threshold, and improving the database performance under different load conditions.

[0103] In some embodiments, cost verification is performed on the query data in a preset sandbox environment to obtain the cost verification result of the structured data object, including:

[0104] Obtain the query environment where the query data is located, and replicate the query environment in the preset sandbox environment. The query environment includes the table structure and index definition, the histogram distribution of column statistics information, and the database configuration parameters.

[0105] Dual-channel validation is performed on the query data in a preset sandbox environment to obtain cost validation results. Dual-channel validation includes original query validation and optimized query validation.

[0106] Optionally, this embodiment obtains the query environment where the query data is located and replicates the query environment in a preset sandbox. The preset sandbox environment is a test environment isolated from the actual production environment, used to provide a safe and controllable space to simulate and evaluate the execution of query actions.

[0107] The query environment affects database performance and query execution. The query environment includes, but is not limited to, table structure and index definitions, histogram distribution of column statistics, and database configuration parameters. Specifically, the table structure defines how data is organized, including but not limited to columns, data types, primary keys, and foreign keys. Index definitions determine the indexes created on each column, their types, and structures, helping to understand the data accessed in the query. The histogram distribution of column statistics provides detailed information about the data distribution within the table, displaying the frequency of different values ​​or value ranges in a column. Database configuration parameters include, but are not limited to, memory allocation, cache size, and concurrency control settings.

[0108] In addition, the original query action, i.e. the unoptimized query action, is executed in the preset sandbox environment. The execution time, resource consumption and execution plan generated by the database optimizer are recorded. Resource consumption includes but is not limited to CPU utilization, memory usage and disk I / O operation count.

[0109] The original query is initially optimized. The optimization measures include, but are not limited to, rewriting the query statement, adding or adjusting indexes, and using materialized views to reduce real-time computation. The optimized query is then executed in a preset sandbox environment, and the execution time, resource consumption, and execution plan are recorded.

[0110] Furthermore, the results of the original query validation and the optimized query validation are compared and analyzed to determine the percentage improvement in execution time, resource consumption, etc., achieved by the optimized query. Simultaneously, the two execution plans are compared to analyze the impact of optimization measures on the query action execution path, yielding the cost verification results for structured data objects. This provides optimization direction for optimizing query actions through a pre-defined optimization model.

[0111] This embodiment achieves cost verification results in a preset sandbox environment that are closer to the production environment by replicating the real query environment, thus improving verification accuracy and reducing optimization bias. By comparing the original query verification and the optimized query through dual-channel verification, potential problems can be discovered in advance, avoiding invalid or negative optimization.

[0112] In some embodiments, determining the priority of each cost verification result includes:

[0113] Assign initial weights to each cost verification result and receive preset business coefficients, which include preset business criticality coefficients and preset time period sensitivity coefficients;

[0114] The initial weights of each cost verification result are adjusted based on preset business coefficients to obtain the weights of each cost verification result.

[0115] The weights of each cost verification result from high to low are determined as the priorities corresponding to each cost verification result from high to low.

[0116] Optionally, in this embodiment, an initial weight is assigned to each cost verification result based on the quantitative indicators in the cost verification results, such as the length of query execution time and the required resource consumption. The initial weight indicates the urgency of optimizing the query action. In some cases, query actions with long execution time and high resource consumption will be given a higher initial weight.

[0117] While assigning initial weights to the verification results of each cost, the system also receives preset business coefficients. These preset business coefficients are parameters that quantify the importance of the query action from a business perspective. These preset business coefficients include, but are not limited to, preset business criticality coefficients and preset time-period sensitivity coefficients.

[0118] The preset business criticality coefficient is used to indicate the importance of the business functions supported by the query action to the overall business objectives. For example, query actions that support core business processes and directly affect user experience or business revenue can be assigned a higher preset business criticality coefficient. The preset time period sensitivity coefficient is used to indicate the difference in business needs of the query action at different time periods. For example, the preset time period sensitivity coefficient corresponding to the query action executed during peak business periods or critical business periods can be increased.

[0119] In addition, the initial weights can be adjusted by combining them with preset business coefficients. This can be achieved by establishing a weight adjustment model, which multiplies the initial weights by a comprehensive factor of the business criticality coefficient and the time-sensitivity coefficient to obtain the cost verification result weights.

[0120] The cost verification result weight can be expressed by the following formula: Cost verification result weight = initial weight × business criticality coefficient × time period sensitivity coefficient. The resulting cost verification result weight can reflect the technical optimization needs of the query action, and also incorporates business considerations, making the optimization focus more in line with business objectives.

[0121] In addition, all cost verification results are sorted according to their weights. The higher the weight of the cost verification result, the higher the priority of the query action in terms of both business and technical aspects, and the more priority it needs to be optimized. This can guide the optimization of resource allocation and optimization order based on priority.

[0122] This embodiment incorporates preset business criticality coefficients and preset time period sensitivity coefficients to accurately locate the optimization endpoint, prioritize the optimization of critical query actions, and sort the results according to the weight of cost verification results, thereby improving optimization efficiency and overall efficiency and enhancing the user experience.

[0123] In some embodiments, the query data includes the query statement, the execution statement, the query time, the data source and table information associated with the execution statement;

[0124] By using a pre-defined optimization model, the query data corresponding to the verification results are optimized according to priority from high to low cost, resulting in optimization methods for slow query actions, including:

[0125] The query data is parsed into a syntax tree, and the feature complexity of the query data is extracted according to preset conditions;

[0126] Based on syntax trees and feature complexity, the system matches query data in the optimization rule base and analyzes the optimal order of multi-table queries through graph neural networks to generate optimization methods for various slow query actions.

[0127] Optionally, the query data involved in this embodiment includes, but is not limited to, query statements, execution statements, query time, and data source and table information associated with the execution statements. Parsing the query data into a syntax tree allows for understanding the foundation of the query structure and logic. A syntax tree is a tree-like data structure that clearly represents the syntactic components and hierarchical relationships of a query statement. For example, the Select, From, and Where clauses in a query statement constitute different nodes in the syntax tree. By parsing the query data, complex query statements can be transformed into a syntax tree form that is easy to analyze and process.

[0128] Furthermore, after parsing the query data to obtain the syntax tree, the feature complexity of the query data is extracted according to preset conditions. Feature complexity is an indicator that measures the complexity of query actions. Feature complexity can represent multiple aspects, including but not limited to the number of tables involved in the query, the complexity of join conditions, the nesting level of subqueries, and the frequency of function calls. For example, a query statement involving multiple table joins, complex subqueries, and a large number of function calls will have high feature complexity. Extracting these feature complexities can quickly assess the complexity of the query.

[0129] Furthermore, the optimization rule base matches optimization rules that are similar to or related to the current query data. This rule base is a database containing various optimization strategies and patterns. For example, if the syntax tree of the query data shows that the query involves Cartesian product joins across multiple tables, the corresponding rules in the optimization rule base can suggest introducing join conditions or adding indexes. Matching through the optimization rule base allows for the rapid determination of optimization strategies suitable for the current query data, improving optimization efficiency.

[0130] Furthermore, for queries involving multiple tables, the query order may affect execution efficiency. The relationship between multi-table queries can be represented as a graph structure, where tables are nodes and the connections between tables are edges. The optimal order of multi-table queries can be analyzed using graph neural networks.

[0131] Graph neural networks (GNNs) are powerful machine learning models suitable for processing graph-structured data. GNNs can learn the impact of different table join orders on query performance, thereby analyzing and generating multiple possible optimal query sequences. For example, under specific data distribution and join conditions, GNNs can learn that joining tables with smaller data volumes first can reduce the amount of data in subsequent join operations, thus improving query efficiency.

[0132] Furthermore, during the implementation of optimization methods, a real-time data monitoring mechanism can be established to track query execution and changes in database performance metrics. If the optimized query performance fails to meet expectations or negatively impacts other parts of the database, timely feedback should be provided, and the optimization plan adjusted according to the actual situation. For example, if a performance bottleneck is detected in the optimized query under a specific database load, the query execution plan can be re-analyzed, the table join order adjusted, or the index usage strategy optimized to ensure the effectiveness and adaptability of the optimization methods.

[0133] This embodiment parses query data into a syntax tree and extracts feature complexity, enabling rapid assessment of query complexity. It then matches and optimizes the rule base based on the syntax tree and feature complexity to quickly find applicable strategies and improve optimization efficiency. Furthermore, it can generate various optimization methods through graph neural networks and the optimization rule base to further enhance optimization efficiency.

[0134] In some embodiments, after optimizing slow query actions according to the optimization method, the method further includes:

[0135] Update the query statements corresponding to the optimized slow query actions to the database, and monitor the query time corresponding to the current query action;

[0136] If the query takes longer than a preset time threshold, an alarm message will be sent to notify the user that the current query is a slow query.

[0137] Optionally, this embodiment applies the optimized slow query statements to the actual production database environment. During the update process, the correctness and completeness of the query statements are ensured to avoid database malfunctions due to syntax errors or data inconsistencies. Simultaneously, relevant information about the optimized query statements is recorded, including but not limited to optimization time and optimization method.

[0138] To monitor the query time corresponding to currently optimized query actions, a real-time monitoring mechanism can be established to continuously track the execution status of optimized query actions in the database. This can be done through the database's built-in performance monitoring tools or third-party monitoring software, by setting monitoring indicators and frequencies to obtain the query time of query actions in a timely manner.

[0139] In addition, the monitored query time is compared with the preset time threshold in real time. When the monitored query time exceeds the preset time threshold, an alarm mechanism is triggered and the alarm information is sent to relevant personnel, such as database administrators and developers, through various channels.

[0140] The alarm information is used to notify the user that the current query is a slow query. The alarm information can also include the content of the query statement, execution time, query time, and the degree to which it exceeds the preset time threshold, so that the recipient can quickly understand the severity and specific situation of the problem. Alarm information can be sent through various means such as email, SMS, and instant messaging tools to ensure that the information can be delivered in a timely manner.

[0141] Furthermore, after receiving the alarm information, relevant personnel can analyze and process the query action, obtain the analysis results, and further optimize or adjust the query action based on the analysis results.

[0142] This embodiment updates the optimized query statement to the database and monitors the query time, which facilitates real-time evaluation of the optimization effect, timely detection of situations that do not meet expectations, and sends an alarm message when the query time exceeds a preset time threshold, so that the query action can be processed quickly, reducing the performance impact and ensuring database performance.

[0143] To effectively address the shortcomings of traditional technologies, such as lagging query performance optimization and impacting software stability and usability, this application provides an embodiment of a database management device for implementing all or part of the aforementioned database management functions. See [link to embodiment]. Figure 2 The database management device specifically includes the following components:

[0144] The first processing module 10 is used to monitor the query actions and query time corresponding to the database through the Druid architecture, and to obtain the historical query actions and historical query time corresponding to the database, and to determine the preset time threshold of slow query actions based on the historical query time.

[0145] The second processing module 20 is used to determine the query action as a slow query action when the query time is greater than a preset time threshold, and to store the query data of the slow query action in the Druid architecture.

[0146] The third processing module 30 is used to synchronize the query data to a preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result.

[0147] The optimization module 40 is used to optimize the query data corresponding to the cost verification results according to the priority from high to low through a preset optimization model, obtain the optimization method of slow query action, and optimize the slow query action according to the optimization method.

[0148] As described above, the database management device provided in this application embodiment can monitor database query actions and query times through the innovative use of a Druid architecture. Based on historical query actions and corresponding historical query times, it determines a preset time threshold for slow query actions. When the query time exceeds the preset time threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and slow query actions are optimized according to priority using a preset optimization model. By monitoring the database through the Druid architecture to identify slow query actions, the tedious and inaccurate manual investigation of slow query actions is avoided, improving the targeting of database performance optimization. Cost verification of query data is performed in the preset sandbox environment, providing detailed cost verification results for the optimization model. This allows for verification of the feasibility and effectiveness of optimization methods without affecting business operations, reducing potential risks to business operations, ensuring stable business operation, and optimizing corresponding slow query actions according to the priority of cost verification results. This efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database. This method effectively addresses the shortcomings of traditional techniques in optimizing query performance, which can lead to delays and negatively impact software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience.

[0149] From a hardware perspective, in order to effectively address the shortcomings of traditional technologies in terms of query performance optimization lag, impacting software stability and usability, significantly improving query performance efficiency and stability, and enhancing user experience, this application provides an embodiment of an electronic device for implementing all or part of the database management method, wherein the electronic device specifically includes the following:

[0150] The system comprises a processor, memory, a communications interface, and a bus; wherein the processor, memory, and communications interface communicate with each other via the bus; the communications interface is used to realize information transmission between the database management device and core business systems, user terminals, and related databases and other related devices; the logic controller can be a desktop computer, tablet computer, or mobile terminal, etc., and this embodiment is not limited to these. In this embodiment, the logic controller can be implemented with reference to the embodiments of the database management method and the database management device in the embodiments, the contents of which are incorporated herein, and repeated details will not be described again.

[0151] It is understood that the user terminal may include smartphones, tablet computers, network set-top boxes, portable computers, desktop computers, personal digital assistants (PDAs), in-vehicle devices, smart wearable devices, etc. Among these, the smart wearable devices may include smart glasses, smartwatches, smart bracelets, etc.

[0152] In practical applications, the database management method can be partially executed on the electronic device side as described above, or all operations can be completed on the client device. The choice can be made based on the processing power of the client device and the limitations of the user's usage scenario. This application does not impose any limitations on this. If all operations are completed on the client device, the client device may further include a processor.

[0153] The aforementioned client device may have a communication module (i.e., a communication unit) that can communicate with a remote server to achieve data transmission with the server. The server may include a server on the task scheduling center side; in other implementation scenarios, it may also include a server on an intermediate platform, such as a server on a third-party server platform that has a communication link with the task scheduling center server. The server may include a single computer device, a server cluster consisting of multiple servers, or a distributed server structure.

[0154] Figure 3 This is a schematic block diagram illustrating the system configuration of the electronic device 9600 according to an embodiment of this application. Figure 3 As shown, the electronic device 9600 may include a central processing unit 9100 and a memory 9140; the memory 9140 is coupled to the central processing unit 9100. It is worth noting that... Figure 3 This is an example; other types of structures can also be used to supplement or replace this structure to achieve telecommunications functions or other functions.

[0155] In one embodiment, the database management method functionality can be integrated into the central processing unit 9100. The central processing unit 9100 can be configured to perform the following controls:

[0156] Step S101: Monitor the database query actions and the corresponding query time through the Druid architecture, and obtain the database historical query actions and the historical query time corresponding to the historical query actions. Determine the preset time threshold for slow query actions based on the historical query time.

[0157] Step S102: When the query time exceeds the preset time threshold, the query action is identified as a slow query action, and the query data of the slow query action is stored in the Druid architecture.

[0158] Step S103: Synchronize the query data to the preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result;

[0159] Step S104: Optimize the query data corresponding to the verification results according to the priority from high to low using the preset optimization model to obtain the optimization method for slow query actions, and optimize the slow query actions according to the optimization method.

[0160] As described above, the electronic device provided in this application innovatively uses a Druid architecture to monitor database query actions and query times. Based on historical database query actions and corresponding historical query times, it determines a preset time threshold for slow query actions. When the query time exceeds the preset time threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and a preset optimization model optimizes the slow query actions according to priority. This allows for database monitoring using a Druid architecture to identify slow query actions, avoiding the tediousness and inaccuracy of manual troubleshooting. It improves the targeting of database performance optimization. By performing cost verification on query data in a preset sandbox environment and providing detailed cost verification results for the optimization model, the feasibility and effectiveness of optimization methods can be verified without affecting business operations. This reduces the potential risks of optimization methods to the business, ensures stable business operation, and optimizes corresponding slow query actions based on the priority of cost verification results. This efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database. This method effectively addresses the shortcomings of traditional techniques in optimizing query performance, which can lead to delays and negatively impact software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience.

[0161] In another embodiment, the database management device can be configured separately from the central processing unit 9100. For example, the database management device can be configured as a chip connected to the central processing unit 9100, and the database management method function can be implemented through the control of the central processing unit.

[0162] like Figure 3 As shown, the electronic device 9600 may further include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is worth noting that the electronic device 9600 does not necessarily need to include these components. Figure 3 All components shown; in addition, the electronic device 9600 may also include Figure 3 For components not shown, please refer to existing technologies.

[0163] like Figure 3As shown, the central processing unit 9100, sometimes also referred to as a controller or operating control, may include a microprocessor or other processor device and / or logic device, which receives inputs and controls the operation of various components of the electronic device 9600.

[0164] The memory 9140 may be, for example, one or more of a cache, flash memory, hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices. It may store the aforementioned failure-related information, and also store a program for executing that information. The central processing unit 9100 may execute the program stored in the memory 9140 to perform information storage or processing, etc.

[0165] Input unit 9120 provides input to central processing unit 9100. Input unit 9120 may be, for example, a keypad or touch input device. Power supply 9170 provides power to electronic device 9600. Display 9160 displays images and text. Display may be, for example, an LCD display, but is not limited thereto.

[0166] The memory 9140 can be a solid-state memory, such as a read-only memory (ROM), random access memory (RAM), a SIM card, etc. It can also be a memory that retains information even when power is off, can be selectively erased, and contains more data; examples of this type of memory are sometimes referred to as EPROMs. The memory 9140 can also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application / function storage unit 9142 for storing application programs and function programs or processes for executing the operation of the electronic device 9600 via the central processing unit 9100.

[0167] The memory 9140 may also include a data storage unit 9143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 9144 of the memory 9140 may include various drivers for the electronic device for communication functions and / or for performing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0168] The communication module 9110 is a transmitter / receiver that sends and receives signals via the antenna 9111. The communication module 9110 (transmitter / receiver) is coupled to the central processing unit 9100 to provide input signals and receive output signals, which is the same as in a conventional mobile communication terminal.

[0169] Based on different communication technologies, multiple communication modules 9110 can be configured in the same electronic device, such as cellular network modules, Bluetooth modules, and / or wireless LAN modules. The communication module 9110 (transmitter / receiver) is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide audio output via the speaker 9131 and receive audio input from the microphone 9132, thereby realizing typical telecommunications functions. The audio processor 9130 may include any suitable buffer, decoder, amplifier, etc. Additionally, the audio processor 9130 is coupled to a central processing unit 9100, enabling on-device recording via the microphone 9132 and on-device playback of stored audio via the speaker 9131.

[0170] Embodiments of this application also provide a computer-readable storage medium capable of implementing all steps of the database management method with a server or client as the execution subject in the above embodiments. The computer-readable storage medium stores a computer program that, when executed by a processor, implements all steps of the database management method with a server or client as the execution subject in the above embodiments. For example, when the processor executes the computer program, it implements the following steps:

[0171] Step S101: Monitor the database query actions and the corresponding query time through the Druid architecture, and obtain the database historical query actions and the historical query time corresponding to the historical query actions. Determine the preset time threshold for slow query actions based on the historical query time.

[0172] Step S102: When the query time exceeds the preset time threshold, the query action is identified as a slow query action, and the query data of the slow query action is stored in the Druid architecture.

[0173] Step S103: Synchronize the query data to the preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result;

[0174] Step S104: Optimize the query data corresponding to the verification results according to the priority from high to low using the preset optimization model to obtain the optimization method for slow query actions, and optimize the slow query actions according to the optimization method.

[0175] As described above, the computer-readable storage medium provided in this application embodiment innovatively uses a Druid architecture to monitor database query actions and query times. Based on historical database query actions and corresponding historical query times, a preset time threshold for slow query actions is determined. When the query time exceeds the preset time threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and slow query actions are optimized according to priority using a preset optimization model. This allows for database monitoring via the Druid architecture to identify slow query actions, avoiding the tediousness and inaccuracy of manual troubleshooting. It improves the targeting of database performance optimization. Cost verification of query data is performed in the preset sandbox environment, providing detailed cost verification results for the optimization model. This allows for verification of the feasibility and effectiveness of optimization methods without affecting business operations, reducing potential risks to business operations, ensuring stable business operation, and optimizing corresponding slow query actions based on the priority of cost verification results. This efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database. This method effectively addresses the shortcomings of traditional techniques in optimizing query performance, which can lead to delays and negatively impact software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience.

[0176] Embodiments of this application also provide a computer program product capable of implementing all steps of the database management method in the above embodiments, where the execution subject is a server or a client. When executed by a processor, this computer program / instruction implements the steps of the database management method. For example, the computer program / instruction implements the following steps:

[0177] Step S101: Monitor the database query actions and the corresponding query time through the Druid architecture, and obtain the database historical query actions and the historical query time corresponding to the historical query actions. Determine the preset time threshold for slow query actions based on the historical query time.

[0178] Step S102: When the query time exceeds the preset time threshold, the query action is identified as a slow query action, and the query data of the slow query action is stored in the Druid architecture.

[0179] Step S103: Synchronize the query data to the preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result;

[0180] Step S104: Optimize the query data corresponding to the verification results according to the priority from high to low using the preset optimization model to obtain the optimization method for slow query actions, and optimize the slow query actions according to the optimization method.

[0181] As described above, the computer program product provided in this application innovatively uses a Druid architecture to monitor database query actions and query times. Based on historical database query actions and corresponding historical query times, it determines a preset time threshold for slow query actions. When the query time exceeds the preset threshold, the query data is stored in the Druid architecture and synchronized to a preset sandbox environment for cost verification. The priority of the cost verification results is determined, and slow query actions are optimized according to priority using a preset optimization model. This allows for database monitoring using a Druid architecture to identify slow query actions, avoiding the tediousness and inaccuracy of manual troubleshooting. It improves the targeting of database performance optimization. By performing cost verification on query data in a preset sandbox environment and providing detailed cost verification results for the optimization model, it verifies the feasibility and effectiveness of optimization methods without affecting business operations. This reduces the potential risks of optimization methods to the business, ensures stable business operation, and optimizes corresponding slow query actions based on the priority of cost verification results. This efficiently solves the slow query problem, reduces query time, and improves the overall performance and response speed of the database. This method effectively addresses the shortcomings of traditional techniques in optimizing query performance, which can lead to delays and negatively impact software stability and usability. It significantly improves the efficiency and stability of query performance and enhances the user experience.

[0182] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, apparatus, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0183] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (devices), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0184] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0185] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0186] Specific embodiments have been used to illustrate the principles and implementation methods of this invention. The descriptions of the embodiments above are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. A database management method, characterized in that, The method includes: The database query actions and corresponding query times are monitored using a Druid architecture. Historical query actions and their corresponding historical query times are obtained. Based on these historical query times, a preset time threshold for slow query actions is determined. This includes: classifying the slow query actions and their corresponding historical slow query times according to a preset time window to obtain categorized datasets, including weekday and holiday datasets; determining the historical query time corresponding to the P99 percentile in each categorized dataset as the basic query time threshold for each categorized dataset; and classifying the query action corresponding to the historical query time as a historical slow query action if the historical query time exceeds the basic query time threshold; obtaining the historical database load rate corresponding to the historical slow query action; and adjusting the preset time threshold for each categorized dataset based on the historical database load rate and the basic query time threshold. If the query time exceeds the preset time threshold, the query action is identified as a slow query action, and the query data of the slow query action is stored in the Druid architecture. The query data is synchronized to a preset sandbox environment, and cost verification is performed on the query data through the preset sandbox environment to obtain the cost verification results of the structured data objects, and the priority of each cost verification result is determined. By using a preset optimization model, the query data corresponding to the cost verification results are optimized according to the priority from high to low to obtain the optimization method for the slow query action, and the slow query action is optimized according to the optimization method.

2. The method according to claim 1, characterized in that, The database load rate includes CPU utilization and input / output latency; Based on the historical database load rate and the basic query time threshold, the preset time thresholds for each of the classification datasets are adjusted, including: When the CPU utilization rate corresponding to the historical query action is greater than a preset utilization rate threshold, the query time base threshold is reduced by a preset reduction coefficient to obtain the preset time threshold. When the input / output waiting time corresponding to the historical query action is greater than the preset input / output waiting time, the basic threshold for query time consumption is increased according to the preset adjustment coefficient to obtain the preset time consumption threshold.

3. The method according to claim 1, characterized in that, Cost verification is performed on the query data in the preset sandbox environment to obtain the cost verification results of the structured data object, including: Obtain the query environment where the query data is located, and replicate the query environment in a preset sandbox environment. The query environment includes the table structure and index definition, the histogram distribution of column statistics information, and the configuration parameters of the database. The query data is subjected to dual-channel verification in the preset sandbox environment to obtain cost verification results. The dual-channel verification includes original query verification and optimized query verification.

4. The method according to claim 1, characterized in that, Determine the priority of each cost verification result, including: Assign initial weights to each of the cost verification results and receive preset business coefficients, which include preset business criticality coefficients and preset time period sensitivity coefficients; The initial weights of each cost verification result are adjusted based on the preset business coefficients to obtain the weights of each cost verification result. The weights of each cost verification result from high to low are determined as the priorities corresponding to each cost verification result from high to low.

5. The method according to claim 1, characterized in that, The query data includes the query statement, the execution statement, the query time, and the data source and table information associated with the execution statement; The optimization method for the slow query action is obtained by optimizing the query data corresponding to the cost verification results according to the priority from high to low using a preset optimization model, including: The query data is parsed into a syntax tree, and the feature complexity in the query data is extracted according to preset conditions; Based on the syntax tree and the feature complexity, the query data is matched in the optimization rule base, and the optimal order of multi-table queries is analyzed through graph neural networks to generate various optimization methods for the slow query actions.

6. The method according to claim 1, characterized in that, After optimizing the slow query action according to the optimization method described above, the process also includes: Update the query statement corresponding to the optimized slow query action to the database, and monitor the query time corresponding to the current query action; If the query time exceeds the preset time threshold, an alarm message is sent to notify the user that the current query action is a slow query action.

7. A database management device, characterized in that, The device includes: The first processing module is used to monitor database query actions and corresponding query times through a Druid architecture, and to obtain historical query actions and corresponding historical query times of the database. Based on the historical query times, it determines a preset time threshold for slow query actions, including: classifying the slow query actions and their corresponding historical slow query times according to a preset time window to obtain a categorized dataset, which includes a weekday category dataset and a holiday category dataset; determining the historical query time corresponding to the P99 percentile in each categorized dataset as a basic query time threshold for each categorized dataset; and classifying the query action corresponding to the historical query time as a historical slow query action if the historical query time exceeds the basic query time threshold; obtaining the historical database load rate corresponding to the historical slow query action; and adjusting the preset time threshold for each categorized dataset based on the historical database load rate and the basic query time threshold. The second processing module is used to determine the query action as a slow query action and store the query data of the slow query action in the Druid architecture when the query time is greater than the preset time threshold. The third processing module is used to synchronize the query data to a preset sandbox environment, perform cost verification on the query data through the preset sandbox environment, obtain the cost verification results of the structured data objects, and determine the priority of each cost verification result. The optimization module is used to optimize the query data corresponding to the cost verification results according to the priority from high to low using a preset optimization model, thereby obtaining the optimization method for the slow query action, and optimizing the slow query action according to the optimization method.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the database management method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the steps of the database management method according to any one of claims 1 to 6.

Images