CN-121009116-B - Database management method and device

CN121009116BCN 121009116 BCN121009116 BCN 121009116BCN-121009116-B

Abstract

The embodiment of the application provides a database management method and a database management device, which monitor database query actions and query time by using a De-Ruy architecture, determine a preset time consumption threshold of a slow query action according to historical query actions and corresponding historical query time consumption of a database, store query data into the De-Ruy architecture and synchronize to a preset sandbox environment for cost verification under the condition that the query time consumption exceeds the preset time consumption threshold, determine the priority of a cost verification result, and optimize the slow query action according to the priority by a preset optimization model. The method effectively solves the defects of the traditional technology in aspects of optimizing lag of query performance, affecting stability and availability of software and the like, remarkably improves the high efficiency and stability of the query performance and improves the use experience of users.

Inventors

Chen Beizhen
JIN HAO
ZHU WEIJIA

Assignees

杭州永融信息技术有限公司

Dates

Publication Date: 20260505
Application Date: 20250715

Claims (9)

1. A method of database management, the method comprising: Monitoring query actions of a database and query time consumption corresponding to the query actions through a Deruff architecture, acquiring historical query actions of the database and historical query time consumption corresponding to the historical query actions, and determining a preset time consumption threshold of a slow query action based on the historical query time consumption, wherein the method comprises the steps of classifying the slow query actions and the historical slow query time consumption corresponding to the slow query actions according to a preset time window to obtain classified data sets, wherein the classified data sets comprise a workday class data set and a holiday class data set; When the query time is greater than the preset time consumption threshold, determining the query action as a slow query action, and storing query data of the slow query action into the De-Ruy architecture; Synchronizing the query data to a preset sandbox environment, executing cost verification on the query data through the preset sandbox environment to obtain cost verification results of the structured data object, and determining the priority of each cost verification result; and optimizing the query data corresponding to the cost verification result with the priority from high to low through a preset optimization model to obtain an optimization mode of the slow query action, and optimizing the slow query action according to the optimization mode.
2. The method of claim 1, wherein the database load rate comprises a central processor usage rate and an input-output latency; Respectively adjusting preset time consumption thresholds of the classified data sets based on the historical database load rate and the inquiry time consumption basic threshold, wherein the method comprises the following steps: When the CPU utilization rate corresponding to the historical query action is greater than a preset utilization rate threshold, the query time consumption basic threshold is adjusted down according to a preset down-adjustment coefficient to obtain the preset time consumption threshold; and when the input/output waiting time corresponding to the historical query action is greater than the preset input/output waiting time, the query time consumption basic threshold is adjusted up according to a preset up-adjustment coefficient to obtain the preset time consumption threshold.
3. The method of claim 1, wherein performing cost verification on the query data through the preset sandbox environment to obtain a cost verification result of the structured data object, comprises: obtaining a query environment in which the query data are located, and re-etching the query environment in a preset sandbox environment, wherein the query environment comprises a table structure, index definition, column diagram distribution of column statistical information and configuration parameters of the database; And executing double-channel verification on the query data in the preset sandbox environment to obtain a cost verification result, wherein the double-channel verification comprises original query verification and optimized query verification.
4. The method of claim 1, wherein determining the priority of each of the cost verification results comprises: assigning an initial weight to each cost verification result, and receiving a preset service coefficient, wherein the preset service coefficient comprises a preset service criticality coefficient and a preset time period sensitivity coefficient; Adjusting initial weights of the cost verification results based on the preset service coefficients to obtain weights of the cost verification results; And determining the weight of each cost verification result from high to low as the corresponding priority of each cost verification result from high to low.
5. The method of claim 1, wherein the query data comprises a query statement, an execution statement, a query time consuming, a data source associated with the execution statement, and table information; optimizing the query data corresponding to the cost verification result from high priority to low priority through a preset optimization model to obtain an optimization mode of the slow query action, wherein the optimization mode comprises the following steps: analyzing the query data into a grammar tree, and extracting feature complexity in the query data according to preset conditions; And matching the query data in an optimization rule base based on the grammar tree and the feature complexity, and analyzing the optimal sequence of multi-table query through a graph neural network to generate a plurality of optimization modes of the slow query action.
6. The method of claim 1, further comprising, after optimizing the slow query action in the optimization manner: updating the optimized query statement corresponding to the slow query action into the database, and monitoring the time consumption of the query corresponding to the current query action; And when the query time is greater than the preset time consumption threshold, sending alarm information, wherein the alarm information is used for prompting the user that the current query action is a slow query action.
7. A database management apparatus, the apparatus comprising: The first processing module is used for monitoring query actions of a database and query time consumption corresponding to the query actions through a Deruff architecture, acquiring historical query actions of the database and historical query time consumption corresponding to the historical query actions, and determining a preset time consumption threshold of a slow query action based on the historical query time consumption, wherein the first processing module is used for classifying the slow query actions and the historical slow query time consumption corresponding to the slow query actions according to a preset time window to obtain a classification data set, and the classification data set comprises a workday class data set and a holiday class data set; determining the historical query time consumption corresponding to the P99 percentile in each classified data set as a query time consumption basic threshold corresponding to each classified data set, and taking the query action corresponding to the historical query time consumption as a historical slow query action under the condition that the historical query time consumption exceeds the query time consumption basic threshold; The second processing module is used for determining the query action as a slow query action and storing query data of the slow query action into the De-Ruy architecture when the query time is greater than the preset time consumption threshold; the third processing module is used for synchronizing the query data to a preset sandbox environment, executing cost verification on the query data through the preset sandbox environment to obtain cost verification results of the structured data object, and determining the priority of each cost verification result; And the optimizing module is used for optimizing the query data corresponding to the cost verification result from high priority to low according to a preset optimizing model to obtain an optimizing mode of the slow query action, and optimizing the slow query action according to the optimizing mode.
8. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the steps of the database management method of any of claims 1 to 6 when the program is executed by the processor.
9. A computer readable storage medium having stored thereon a computer program, characterized in that the computer program, when being executed by a processor, implements the steps of the database management method according to any of claims 1 to 6.

Description

Database management method and device Technical Field The application relates to the technical field of computers, in particular to a database management method and device. Background In the process of long-term running of software, as data in a production environment is continuously accumulated, the query performance of the software is gradually reduced, so that the response time is prolonged, the main reason for the decline of the query performance is that the increase of the data scale fails to synchronously match with a corresponding query optimization strategy, and when the data volume of the software reaches a certain threshold, if index design is unreasonable, a query execution plan is not optimized or historical data archiving and partitioning strategies are lacked, the efficiency of database query is greatly reduced, so that the system throughput is reduced, and the delay of a user request is increased. In the prior art, the optimization of the query performance mainly utilizes a structured query language (structured query language, SQL), the execution speed of SQL sentences directly influences the occupied time of resources, however, slow query actions are often passive fault feedback mechanisms, the feedback time is long and low-efficiency, the optimization of the query performance is delayed, the stability and the usability of software are influenced, and the use experience of users is reduced. Disclosure of Invention Aiming at the problems in the prior art, the application provides the database management method and the database management device, which can effectively solve the defects of the traditional technology in aspects of optimizing lag of query performance, influencing stability and availability of software and the like, remarkably improve the high efficiency and stability of the query performance and improve the use experience of users. In order to solve at least one of the problems, the application provides the following technical scheme: in a first aspect, the present application provides a database management method, including: monitoring query actions of a database and query time consumption corresponding to the query actions through a Deruff architecture, acquiring historical query actions of the database and historical query time consumption corresponding to the historical query actions, and determining a preset time consumption threshold of the slow query actions based on the historical query time consumption; when the query time is greater than a preset time consumption threshold, determining the query action as a slow query action, and storing query data of the slow query action into a De-Ruy architecture; Synchronizing the query data to a preset sandbox environment, executing cost verification on the query data through the preset sandbox environment to obtain cost verification results of the structured data object, and determining the priority of each cost verification result; And optimizing the query data corresponding to the cost verification result with the priority from high to low through a preset optimization model to obtain an optimization mode of the slow query action, and optimizing the slow query action according to the optimization mode. Further, the method further comprises the steps of classifying slow query actions and historical slow query time consumption corresponding to the slow query actions according to a preset time window to obtain a classification data set, wherein the classification data set comprises a workday class data set and a holiday class data set; Respectively determining the historical query time consumption corresponding to the P99 percentile determined in each classified data set as a query time consumption basic threshold corresponding to each classified data set, and taking the query action corresponding to the historical query time consumption as a historical slow query action under the condition that the historical query time consumption exceeds the query time consumption basic threshold; and acquiring a historical database load rate corresponding to the historical slow query action, and respectively adjusting preset time consumption thresholds of the classified data sets based on the historical database load rate and the query time consumption basic threshold. Further, the database load rate includes CPU utilization and input/output latency, and further includes: When the CPU utilization rate corresponding to the historical query action is greater than a preset utilization rate threshold, the query time consumption basic threshold is investigated according to a preset down-regulating coefficient to obtain a preset time consumption threshold; And when the input/output waiting time corresponding to the execution of the historical query action is longer than the preset input/output waiting time, the query time consumption basic threshold is investigated according to the preset up-regulation coefficient, and the preset time consumption thr