CN116842037A

CN116842037A - Method, device, equipment and medium for analyzing slow SQL statement of database

Info

Publication number: CN116842037A
Application number: CN202310789690.2A
Authority: CN
Inventors: 董卓灵
Original assignee: Jinzhuan Xinke Co Ltd
Current assignee: Jinzhuan Xinke Co Ltd
Priority date: 2023-06-29
Filing date: 2023-06-29
Publication date: 2023-10-03

Abstract

The application relates to a method, a device, equipment and a medium for analyzing slow SQL sentences of a database, which relate to the technical field of databases, and comprise the following steps: acquiring execution data corresponding to a plurality of slow SQL sentences in a preset period, wherein the execution data comprises phase data of at least one execution phase and phase indexes corresponding to each execution phase, carrying out index analysis on the phase data of the execution phase according to the phase indexes corresponding to the execution phases for each execution phase to obtain corresponding index change information, carrying out aggregation analysis processing on all the index change information to obtain query rates corresponding to the plurality of slow SQL sentences in the period, and generating analysis data for assisting in analyzing a target phase according to the query rates and the preset reference query rate, thereby effectively optimizing the analysis flow of the slow SQL sentences, carrying out accurate analysis on the optimization phase affecting the operation of a database system, and helping operation and maintenance personnel to optimize the database system.

Description

Method, device, equipment and medium for analyzing slow SQL statement of database

Technical Field

The present application relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a medium for analyzing a slow SQL statement of a database.

Background

With the advent of the big data age, databases have encountered performance bottlenecks in processing applications such as querying, statistics, analysis, etc., of data above the TB level, and particularly at the PB level. In the face of large data volume applications such as telecommunications, finance, security, government enterprises and the like, including scenes such as telecommunications ticket, finance fine account, operation analysis, public security monitoring, audit inspection and the like, user experience is often unacceptable. Under the impact of the Internet and big data application, massive and diverse data challenges database development and operation and maintenance personnel. However, in a specific project, hundreds of thousands of structured query language (Structured Query Language, SQL) sentences are often distributed in a code base, and a large number of accesses and operations are performed on the database in real time, so that it is difficult for an operation and maintenance person to find out efficiency bottlenecks of codes in the project operation process, and in the database operation and maintenance link, how to accurately and quickly locate slow SQL sentences becomes a significant problem.

The existing slow SQL statement analysis method mainly comprises the steps of analyzing and screening collected slow SQL statement query constitutions by developers, and carrying out focus analysis on the slow SQL statements which are more in query times and long in query occupation time. However, the slow SQL statement analysis method has the advantages that the execution stages are more, the time consumption of different execution stages is different, the existing slow SQL statement analysis method cannot accurately position the optimized execution stages, only the stages which can be optimized can be determined from massive data by relying on time and energy spent by operation and maintenance personnel and precious experience, and manpower and material resources are wasted.

Disclosure of Invention

The application provides a method, a device, equipment and a medium for analyzing a slow SQL sentence of a database, thereby effectively optimizing the analysis flow of the slow SQL sentence, being capable of accurately analyzing the optimizing stage affecting the operation of the database system, being beneficial to assisting operation and maintenance personnel in optimizing the database system and solving the problem that the traditional method for analyzing the slow SQL sentence can not accurately position the executing stage which can be optimized.

In a first aspect, the present application provides a method for analyzing a slow SQL statement of a database, including:

acquiring execution data corresponding to a plurality of slow SQL sentences in a preset period, wherein the execution data comprises phase data of at least one execution phase and phase indexes corresponding to each execution phase;

aiming at each execution stage, carrying out index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage to obtain corresponding index change information;

performing aggregation analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL sentences in the period;

and generating analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate, wherein the target stage is a stage to be optimized in at least one execution stage.

Optionally, the acquiring the execution data corresponding to the plurality of slow SQL statements in the preset period includes:

receiving a data acquisition request corresponding to the slow SQL statement, wherein the data acquisition request carries a slow log acquisition task, and the slow log acquisition task comprises data acquisition frequency and an acquisition index corresponding to the slow SQL statement;

acquiring an execution record table, wherein the execution record table records the execution information of the slow SQL statement;

and based on the acquisition index, acquiring the execution data of the slow SQL sentence from the execution record table according to the data acquisition frequency.

Optionally, before the obtaining the execution record table, the method further includes:

monitoring the execution stage of each slow SQL sentence to obtain accumulated value information, wherein the accumulated value information comprises the accumulated number of the slow SQL sentences and accumulated execution time;

and generating the slow SQL statement execution information based on the accumulated number of pieces and the accumulated execution time, and generating an execution record table based on the execution information.

Optionally, for each execution stage, performing index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage to obtain corresponding index change information, including:

Extracting execution time from the phase data;

and aiming at each execution stage, carrying out trend analysis on the execution time based on the stage indexes to obtain index time consumption corresponding to each execution stage, and taking the index time consumption as the index change information.

Optionally, the performing aggregate analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL statements in the period includes:

counting the slow SQL sentences to obtain the sentence quantity;

performing aggregation analysis based on the index change information to obtain the total time consumption of the slow SQL statement;

and analyzing based on the statement number and the total time consumption to obtain the query rate.

Optionally, before the obtaining the query rate, the method further includes:

determining the fragments corresponding to the slow SQL sentences, and counting the frequency based on the slow SQL sentences to obtain the execution times;

performing dimension analysis on the fragments based on the statement number, the execution times and the total time consumption to obtain change trend information corresponding to the fragments;

and patterning based on the change trend information to obtain a dimension trend graph of the slow SQL statement, and displaying based on the dimension trend graph.

Optionally, the generating the analysis data for assisting in analyzing the target stage according to the query rate and the preset reference query rate includes:

acquiring a preset reference query rate;

patterning based on the query rate and the reference query rate to obtain a phase change trend graph, and taking the phase change trend graph as the analysis data, wherein the phase change trend graph comprises a target phase and trend change data corresponding to the target phase;

and displaying key points based on the target stage and the trend change data.

In a second aspect, the present application provides a slow SQL statement analysis device for a database, comprising:

the execution data acquisition module is used for acquiring execution data corresponding to a plurality of slow SQL sentences in a preset period, wherein the execution data comprises phase data of at least one execution phase and phase indexes corresponding to each execution phase;

the index analysis module is used for carrying out index analysis on the phase data of the execution phases based on the phase index corresponding to the execution phases for each execution phase to obtain corresponding index change information;

the aggregation analysis processing module is used for carrying out aggregation analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL sentences in the period;

The analysis data generation module is used for generating analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate, wherein the target stage is a stage to be optimized in at least one execution stage.

In a third aspect, the present application provides an electronic device, including a processor, a communication interface, a memory, and a communication bus, where the processor, the communication interface, and the memory complete communication with each other through the communication bus;

a memory for storing a computer program;

a processor, configured to implement the steps of the method for analyzing a slow SQL statement of a database according to any embodiment of the first aspect when executing a program stored on a memory.

In a fourth aspect, the present application provides a computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of the method for slow SQL statement analysis of a database according to any of the embodiments of the first aspect.

In summary, the embodiment of the application obtains the execution data corresponding to the plurality of slow SQL sentences in the preset period, wherein the execution data comprises the stage data of at least one execution stage and the stage index corresponding to each execution stage, carries out index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage for each execution stage to obtain corresponding index change information, carries out aggregation analysis processing on all index change information to obtain the query rate corresponding to the plurality of slow SQL sentences in the period, and generates the analysis data for assisting in analyzing the target stage according to the query rate and the preset reference query rate, thereby effectively optimizing the analysis flow of the slow SQL sentences, being capable of carrying out accurate analysis on the optimization stage influencing the operation of a database system, being beneficial to assisting operation staff in optimizing the database system, and solving the problem that the conventional analysis method of the slow SQL sentences cannot accurately position the optimized execution stage.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the application and together with the description, serve to explain the principles of the application.

In order to more clearly illustrate the embodiments of the application or the technical solutions of the prior art, the drawings which are used in the description of the embodiments or the prior art will be briefly described, and it will be obvious to a person skilled in the art that other drawings can be obtained from these drawings without inventive effort.

FIG. 1 is a schematic flow chart of a method for analyzing slow SQL statements in a database according to an embodiment of the application;

FIG. 2 is a schematic diagram of a method for analyzing a slow SQL statement in a database according to an alternative embodiment of the application;

FIG. 3 is a flow chart of data acquisition provided in an alternative embodiment of the present application;

FIG. 4 is an overview of a data visualization interface provided by an alternative embodiment of the present application;

FIG. 5 is a graph of index trend provided by an alternative embodiment of the present application;

FIG. 6 is a block diagram of a device for analyzing a slow SQL statement in a database according to an embodiment of the application;

Fig. 7 is a schematic structural diagram of an electronic device according to an embodiment of the present application.

Detailed Description

For the purpose of making the objects, technical solutions and advantages of the embodiments of the present application more apparent, the technical solutions of the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application, and it is apparent that the described embodiments are some embodiments of the present application, but not all embodiments of the present application. All other embodiments, which can be made by those skilled in the art based on the embodiments of the application without making any inventive effort, are intended to be within the scope of the application.

In the related art, in order to improve the performance and stability of the database, the database operation staff needs to make caution on the database operation to optimize each add-delete-check SQL statement. However, in a specific project, hundreds of thousands of SQL sentences are often distributed in a code base, and a large number of accesses and operations are performed on the database in real time, so that an operator can hardly find the efficiency bottleneck of the code in the project operation process. Therefore, how to accurately and quickly locate the slow SQL statement becomes the primary solution in the database operation and maintenance link.

For this reason, the prior art mainly collects database query logs, and analyzes and locates slow SQL sentences from the database query logs. Typically, collection of a database slow query log requires the database to record the time elapsed for each execution phase of each slow SQL statement exceeding an execution time threshold and persist the collection in disk. In this process, the disk and IO resources of the database are occupied by the slow query log acquisition process. Along with the exponential increase of the data volume and the concurrent access volume, a large number of key businesses put higher requirements on the response capability of the database, a large number of slow SQL logs can be formed by starting a slow query log acquisition task in a high-concurrency environment, and the resources of the database can be influenced under the condition that the total resource volume is unchanged. After a part of resources are consumed to acquire the slow query logs, an operator needs to analyze and screen massive slow SQL logs, and SQL with more query times, long query occupation time, large IO resource consumption and SQL with missed indexes are usually objects needing important attention. During the analysis, the operation and maintenance personnel usually employ corresponding screening analysis tools, such as: the database is provided with tools or a third party tool pt-query-digest and the like to screen out log content meeting the conditions and statistical information related to SQL sentences and locate slow SQL sentences needing to be optimized. However, in the prior art, only slow SQL sentences can be positioned, the execution stages of the slow SQL sentences are more, the time consumption is different, and only the execution stages which can be optimized can be positioned from massive data by relying on the time spent by operation and maintenance personnel, so that the manpower is seriously wasted.

In order to solve the problems, the application provides a method, a device, equipment and a medium for analyzing slow SQL sentences of a database, which are characterized in that by acquiring execution data corresponding to a plurality of slow SQL sentences in a preset period, the execution data comprise phase data of at least one execution phase and phase indexes corresponding to each execution phase, aiming at each execution phase, carrying out index analysis on the phase data of the execution phase based on the phase indexes corresponding to the execution phase to obtain corresponding index change information, carrying out aggregation analysis processing on all index change information to obtain query rates corresponding to a plurality of slow SQL sentences in the period, and generating analysis data for assisting in analyzing a target phase according to the query rates and the preset reference query rate, thereby effectively optimizing the analysis flow of the slow SQL sentences, being beneficial to assisting operation staff in optimizing a database system, solving the problem that the conventional slow SQL sentence analysis method cannot accurately position the execution phase which can be optimized, effectively improving the work efficiency of the operation staff and saving time.

For the purpose of facilitating an understanding of the embodiments of the present application, reference will now be made to the drawings and specific examples, which are not intended to limit the embodiments of the application.

Fig. 1 is a flow chart of a method for analyzing a slow SQL statement in a database according to an embodiment of the present application. As shown in fig. 1, the method for analyzing a slow SQL statement of a database provided by the embodiment of the application specifically includes the following steps:

step 110, obtaining execution data corresponding to a plurality of slow SQL sentences in a preset period.

The execution data comprises stage data of at least one execution stage and stage indexes corresponding to each execution stage.

In particular, the database SQL statement (including the slow SQL statement) may involve multiple execution phases during execution, each of which has corresponding phase data, e.g., the phase data may include execution time, etc., which is not limited in this embodiment of the application.

Specifically, the embodiment of the application can select the execution stage which can fully reflect the optimization point of the slow SQL sentence from a plurality of execution stages to be used as a stage index. When the database system executes the SQL sentence, the database query log is generated by recording the phase data of the SQL sentence in each execution phase, and then the slow SQL sentence is analyzed from the database query log by using a tool, so that the phase data corresponding to the phase index can be extracted from the query log of the slow SQL sentence.

In a specific implementation, the embodiment may set an acquisition period, and obtain execution data corresponding to a plurality of slow SQL statements in a preset period.

As an example, referring to table 1 below, table 1 is an execution phase corresponding to a slow SQL statement and a meaning of the execution phase. In this embodiment, the execution stage in table 1 may be used as a stage index, so as to obtain, according to the stage index, the time consumption of the index corresponding to the stage index from the query log of the slow SQL statement, so as to be used as the stage data corresponding to the slow SQL statement.

TABLE 1 execution stage and corresponding meanings

Step 120, for each execution stage, performing index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage, to obtain corresponding index change information.

Specifically, the index change information may include a change condition of all the slow SQL statements in the current execution stage, e.g., the change condition may include a total time consumption of all the slow SQL statements in the current execution stage, which is not limited in this example.

In a specific implementation, for each execution stage, the time consumption of all the slow SQL sentences in the execution stage can be counted, and the total time consumption of the slow SQL sentences in each execution stage is recorded in an accumulated form to obtain index change information. For example, the existing 4 slow SQL sentences need to be analyzed, taking the index "net_write_time" in table 1 as an example, the index change information of the index in the execution stage is the total time consumption of the 4 slow SQL sentences in the execution stage of "net_write_time".

And 130, performing aggregation analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL sentences in the period.

Specifically, the query rate corresponding to the slow SQL statement may be a slow SQL statement query rate per second, which is also referred to as SQPS (Slow Queries Per Second, slow SQL statement query rate per second), which is not limited by the embodiment of the present application.

In particular implementations, problems that may exist with current systems (e.g., database systems) are further analyzed so that overall system performance may be targeted. The present embodiment may define a plurality of metrics for counting the trend of the slow SQL statement in the execution phase, for example, the plurality of metrics may include, but not limited to, SQPS and QPS (Queries Per Second, query rate per second), and by analyzing the trend relationship between SQPS and QPS in the execution phase, assist the operation and maintenance personnel in quickly locating the phase where the fault or congestion occurs in the system. Specifically, all index change information can be aggregated, for example, time consumption corresponding to all index change information can be accumulated and calculated, and the query rate of the slow SQL sentences per second can be obtained by calculating the sentence quantity of all the slow SQL sentences.

For example, statistics can be performed on the slow SQL statements in a preset period, the statement number of the slow SQL statements is determined, that is, the execution number of the slow SQL statements in the preset period, time consumption statistics can be performed on the slow SQL statements in the preset period to obtain total time consumption, and then query rate calculation can be performed by combining the total time consumption with the execution number to obtain the query rate of the slow SQL statements per second. The query rate of the slow SQL statement per second can then be used to determine the optimizable stage of the slow SQL statement in the execution stage.

And 140, generating analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate.

Wherein the target phase is a phase to be optimized of at least one of the execution phases.

Specifically, the reference query rate may be a query rate per second between all SQL statements and the total time consumption, also referred to as QPS, in the preset period, which is not limited in the embodiment of the present application; the analysis data may include a phase change trend graph, where the phase change trend graph may include a target phase and trend change data corresponding to the target phase, where the trend change data may be relationship data between a query rate and a reference query rate in the current target phase, which is not limited in the embodiment of the present application.

Specifically, in this embodiment, aggregate analysis may be performed on all SQL statements in a preset period in advance to obtain the execution number and total consumption time of all SQL statements, so as to determine the query rate per second in the preset period as a reference query rate, that is, QPS. And then carrying out relation analysis by combining QPS and SQPS to obtain relation data, and constructing a phase change trend graph, namely analysis data of a target phase by using the relation data based on each execution phase. By displaying the phase change trend graph to the operation and maintenance personnel, the operation and maintenance personnel can analyze according to the relation data of the QPS and the SQPS so as to locate which execution phase has congestion or faults, thereby being capable of rapidly determining the target phase affecting service execution, being beneficial to the operation and maintenance personnel to systematically optimize aiming at the slow phase, effectively optimizing the analysis flow of the slow SQL sentence, being capable of accurately analyzing the optimization phase affecting the operation of the database system and being beneficial to assisting the operation and maintenance personnel to optimize the database system.

It can be seen that, in the embodiment of the application, by acquiring the execution data corresponding to the plurality of slow SQL statements in the preset period, the execution data includes the stage data of at least one execution stage and the stage index corresponding to each execution stage, for each execution stage, the stage data of the execution stage is subjected to index analysis based on the stage index corresponding to the execution stage, corresponding index change information is obtained, all index change information is subjected to aggregation analysis processing, the query rate corresponding to the plurality of slow SQL statements in the period is obtained, and the analysis data for assisting in analyzing the target stage is generated according to the query rate and the preset reference query rate, so that the analysis flow of the slow SQL statements is effectively optimized, the optimization stage affecting the operation of the database system can be accurately analyzed, the database system is helped to be assisted in optimizing, and the problem that the conventional analysis method for the slow SQL statements cannot accurately locate the execution stage which can be optimized is solved.

Referring to fig. 2, a schematic flow chart of steps of a method for analyzing a slow SQL statement in a database according to an alternative embodiment of the present application is shown. The slow SQL statement analysis method of the database specifically comprises the following steps:

Step 210, obtaining execution data corresponding to a plurality of slow SQL sentences in a preset period.

In a specific implementation, in order to achieve the purposes of reducing resource consumption by storing the content of the slow query log and assisting an operation and maintenance person in efficiently locating the slow SQL statement, the embodiment can construct an operation and maintenance monitoring system in advance, quickly acquire the query log of the slow SQL statement of the database through the operation and maintenance monitoring system, analyze the slow SQL statement and display optimizable points based on analysis results, thereby assisting the operation and maintenance person in quickly locating the slow SQL statement and quickly locating system optimization points and improving operation and maintenance efficiency. In particular, the operation and maintenance monitoring system may include an operation and maintenance server (InsightServer, IS) and an operation and maintenance collection end proxy (InsightAgent, IA), which is not limited in this embodiment of the present application. The IS can issue a periodic slow log acquisition task to the IA, and the IA acquires the Data by executing appointed SQL every corresponding period through a long link channel of a connecting pool between the IA and a database Data Node (Data Node, DN), thereby realizing acquisition of execution Data corresponding to a plurality of slow SQL sentences in a preset period. After the IA obtains the execution result, according to the data dictionary agreed with the IS, slow log content IS sent to the IS, and the IS puts the data into a metadata base (RDB) for persistence.

It should be noted that in this embodiment, the IS may be a main carrier of the operation and maintenance work, and IS formed by a Web interface for displaying the operation state of the database and a server for supporting the operation and maintenance task. The method plays roles of issuing acquisition tasks, storing acquisition data and displaying database states in the process; the IA can be an 'acquirer' of operation and maintenance related data and IS responsible for receiving a timed acquisition task from the IS to a designated position and executing a task command to acquire acquisition data; the DN can be an 'executor' of the main business of the database and is responsible for fulfilling the responsibility of the database, executing the business and recording the running state of the database; the RDB may be a metadata repository responsible for storing data required for the operation of the database system.

In an optional embodiment, the method for obtaining execution data corresponding to a plurality of slow SQL statements in a preset period may specifically include: receiving a data acquisition request corresponding to the slow SQL statement, wherein the data acquisition request carries a slow log acquisition task, and the slow log acquisition task comprises data acquisition frequency and an acquisition index corresponding to the slow SQL statement; acquiring an execution record table, wherein the execution record table records the execution information of the slow SQL statement; and based on the acquisition index, carrying out data acquisition on the execution stage of the slow SQL sentence according to the data acquisition frequency to obtain the execution data.

In an optional implementation manner, before the embodiment of the present application obtains the execution record table, the method specifically may further include: monitoring the execution stage of each slow SQL sentence to obtain accumulated value information, wherein the accumulated value information comprises the accumulated number of the slow SQL sentences and accumulated execution time; and generating the slow SQL statement execution information based on the accumulated number of pieces and the accumulated execution time, and generating an execution record table based on the execution information.

Specifically, in the memory of the database node, i.e., DN, there may be time spent by each execution stage of the SQL statement, which is an accumulated value. Every time the DN executes a slow SQL sentence, the time consumption of each corresponding execution stage increases, the IA executes commands provided by the DN for checking the accumulated values at regular acquisition periods, acquires the accumulated values and pushes the accumulated values to the IS so that the IS can perform subsequent analysis.

Further, for the execution situation corresponding to each execution stage of the slow SQL statement, an execution record table may be set to record the execution situation. If the execution record table may be a system table of DN, the embodiment of the present application is not limited thereto, i.e. the system table may store a record slow SQL execution condition. The execution case table may contain a plurality of field contents, for example, the field contents may contain a slow SQL statement, a number, a last occurrence time, a total number (accumulated value), a total execution time (accumulated value), and the like. The slow SQL of the same numbered or patterned slow SQL statement is accumulated by the accumulated value. Similarly, the IA executes the SQL statement collection content at regular collection period intervals, pushes the SQL statement collection content to the IS, and carries out subsequent processing. The mode of recording the accumulated value is adopted to be different from the traditional acquisition mode, so that the load of DN under the condition of starting slow SQL acquisition is reduced, the performance is improved, and the consumption of system resources can be effectively reduced and the influence on business is avoided when data acquisition is carried out by adopting the mode of recording the accumulated value.

In actual processing, in order to facilitate operation and maintenance personnel to rapidly analyze, an operation and maintenance interface, which is also called a data visualization page, can be constructed based on an operation and maintenance monitoring system. The operation and maintenance interface can interact with operation and maintenance personnel, the operation and maintenance personnel can start/close the execution data acquisition of the slow SQL statement through the operation and maintenance interface, and the operation and maintenance personnel can also check the subsequent analysis result of the slow SQL statement through the operation and maintenance interface, wherein the positioning flow of the slow SQL statement can be as follows: entering an operation and maintenance interface, carrying out statistical monitoring, diagnosing and carrying out slow day analysis.

For example, referring to fig. 3, fig. 3 is a flow chart of data acquisition provided by an alternative embodiment of the present application. The user can select an instance to be checked through the operation and maintenance interface to determine the acquisition switch state of the slow log (namely the execution data of the slow SQL statement), and can start/stop the execution data acquisition of the slow SQL statement by starting a switch in DN configuration under the instance and an operation and maintenance server side slow log acquisition task switch, wherein an operation and maintenance person can set data acquisition frequency and acquisition indexes corresponding to the slow SQL statement through the operation and maintenance interface. After the operation and maintenance personnel turn on the acquisition switch, the operation and maintenance interface can construct a slow log acquisition task according to the data acquisition frequency and the acquisition index set by the operation and maintenance personnel, then generate a data acquisition request based on the slow log acquisition task, and send the data acquisition request to the IS. After the IS receives the data acquisition request, a slow log acquisition task carried by the data acquisition request sends an acquisition command to the IA (the acquisition command can carry a timing acquisition task), after the IA receives the acquisition command sent by the IS, the IS periodically executes an SQL command according to the timing acquisition task carried by the acquisition command, slow SQL information (namely slow logs) IS obtained from an RDB bottom layer, and an acquisition result can be used as execution data corresponding to a plurality of slow SQL sentences in a preset period to be sent to the IS so as to be convenient for subsequent processing such as analysis by the IS.

Step 220, extracting the execution time from the phase data.

The present embodiment may extract the execution time from the phase data, which may be the time used by the slow SQL statement to complete the execution phase, which is not limited by the present embodiment.

Step 230, for each execution stage, performing trend analysis on the execution time based on the stage index, to obtain an index time consumption corresponding to each execution stage, and using the index time consumption as the index change information.

In a specific implementation, the embodiment may divide the periodically collected phase data, and differentiate each execution phase and its corresponding execution time according to the phase index, so as to obtain all execution times corresponding to each execution phase. And then, carrying out trend analysis on the execution time, for example, carrying out accumulated calculation on all the execution time of the execution stage through IS to obtain the total execution time consumption corresponding to all the slow SQL sentences in the current execution stage, wherein the total execution time consumption IS taken as index time consumption. By accumulating the time consumption of each execution stage, the time consumption of the slow SQL sentence in each execution stage can be clearly known, and the execution stage which can be optimized can be effectively analyzed.

And step 240, counting the slow SQL sentences to obtain the sentence number.

In a specific implementation, the embodiment of the application can accumulate all the slow SQL sentences to obtain the total number of the slow SQL sentences, namely the total number of all executed slow SQL sentences in the period. For example, for the periodically collected log data of the slow SQL statements, all executed slow SQL statements may be determined based on the log data of the slow SQL statements, and the number of all executed slow SQL statements may be counted, so as to obtain the total execution number of the slow SQL statements as the statement number.

And step 250, performing aggregation analysis based on the index change information to obtain the total time consumption of the slow SQL sentence.

In a specific implementation, the present embodiment may perform aggregate analysis on the time consumption condition of each execution stage obtained by accumulation calculation, for example, may be to accumulate the total time consumption of execution of all execution stages, so as to obtain the total time consumption of all slow SQL statements in all execution stages in the period. By analyzing the execution condition of the slow SQL sentences from multiple dimensions, key information such as the number of sentences, the execution times, the total time consumption and the like is obtained, so that the slow SQL sentences can be further analyzed by utilizing the key information, and the optimizable points of the database system can be determined.

And step 260, analyzing based on the statement number and the total time consumption to obtain the query rate.

In a specific implementation, the embodiment can analyze and calculate the number of sentences and total time consumption to determine the query rate (namely SQPS) of each second of the slow SQL sentences in the current period, and obtain the query rate, so that the subsequent analysis can be further performed by combining the reference query rate, thereby realizing the positioning analysis of the slow SQL sentences by auxiliary operation and maintenance personnel.

In addition, in order to realize the purpose of assisting the operation and maintenance personnel in locating the slow SQL sentences with more execution times and long time consumption in the unstable time of the system, the embodiment can further analyze the execution times of the slow SQL sentences in the period after analyzing the slow SQL sentences to obtain the sentence number and the total time consumption. For example, slow SQL statements may include, but are not limited to, "COMMIT" and "SHOW MASTER STATUS" types. In one period, the execution times of each slow SQL statement may be variable, and this embodiment may aggregate all executed slow SQL statements, aggregate the same slow SQL statement together, and perform accumulation calculation to obtain the execution times of each slow SQL statement. The slow SQL sentences are analyzed and counted in multiple dimensions from the dimensions such as total time consumption, the number of sentences and the execution times, and the execution conditions of the slow SQL sentences can be displayed in multiple dimensions on an interface when the slow SQL sentences need to be displayed, so that analysis by operation and maintenance personnel is facilitated.

In an optional embodiment, before the query rate is obtained according to the embodiment of the present application, the method may further include: determining the fragments corresponding to the slow SQL sentences, and counting the frequency based on the slow SQL sentences to obtain the execution times; performing dimension analysis on the fragments based on the statement number, the execution times and the total time consumption to obtain change trend information corresponding to the fragments; and patterning based on the change trend information to obtain a dimension trend graph of the slow SQL statement, and displaying based on the dimension trend graph.

As an example, referring to fig. 4, after confirming that there is slow log data, the operator can visually see the overview state of slow SQL within a specified time span from the overview portion of the slow SQL data visualization page. According to the overview status bar, the operator can directly locate the TOP10 fragment with the most slow SQL number or time consumption currently executed and the patterned slow SQL statement with the most times or time consumption executed. Specifically, multidimensional analysis statistics can be performed on total time consumption, statement number, execution times and the like of the slow SQL statements in each execution stage at the same time, analysis data is obtained, and then the analysis data is used for displaying. Different slow SQL sentences may correspond to different fragments, and under the condition that the slow SQL sentences of different fragments need to be compared to comprehensively compare and check the execution problem of the slow SQL sentences, a dimension trend graph (short trend graph) of the slow SQL sentences can be checked. The trend graph mainly takes the number, time consumption and scanning line number as dimensions, and commonly shows the overall change trend of the current slicing slow SQL statement. The trend graph supports scaling of the time region for ease of analysis, and clicking on the data points in the graph may look at the top 15 patterned SQL statements that select time periods ordered by time or number. Through the trend graph mutation points, operation and maintenance personnel can quickly locate slow SQL with more execution times and long time consumption in the unstable time of the system.

In a specific implementation, the IS executes time-consuming data of each stage of the received slow log, converts the time-consuming data into increment values of the acquisition period by the accumulated value, and then stores the increment values into a table. And for SQL content data, the SQL content data and the statistical data are stored separately, and the SQL content data and the statistical data are associated through self-increment IDs when each piece of patterned SQL is entered into a table. After statistics and analysis are completed, operation and maintenance personnel can conveniently analyze and position the slow SQL statement through the designed data display page related to the slow query log.

And step 270, generating analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate.

In a specific implementation, in order to further analyze the possible problems of the current system so as to purposefully optimize the overall performance of the system, the embodiment of the application can influence the execution stage of the system performance from the relational trend analysis of the quality tests of the SQPS and the QPS. Specifically, by comparing the trend of the relationship between the SPQS and the QPS with the trend of the time consumption of each execution stage of each time period, the execution stages of the fault are statistically located. Generally speaking, the QPS and the SQPS are in a direct proportion relationship, if the relationship between the QPS and the SQPS is changed, the stage statistical trend graph can be executed to locate which stage of the system has congestion or failure, so that the target stage can be effectively determined, and the operation and maintenance personnel can be assisted to optimize the target stage, so that the stage affecting the service execution is optimized.

Optionally, the generating the analysis data for assisting in analyzing the target stage according to the query rate and the preset reference query rate may include the following substeps:

sub-step 2701, a predetermined reference query rate is obtained.

Specifically, for QPS, the embodiment may periodically obtain a database query log, and determine the total execution number of the SQL data and the corresponding total consumption time in the period by statistically analyzing the database query log, so as to obtain the QPS based on the total execution number of the SQL data and the total consumption time, which is used as a preset reference query rate.

In a substep 2702, a phase change trend graph is obtained by patterning based on the query rate and the reference query rate, and the phase change trend graph is used as the analysis data, where the phase change trend graph includes a target phase and trend change data corresponding to the target phase.

In a specific implementation, the embodiment of the application can carry out statistical analysis on the SQPS and the QPS to obtain the relationship between the QPS and the SPQS, so that the system anomaly information can be obtained by comparing the relationship between the current system QPS and the SPQS. Specifically, the present embodiment can perform patterning using the relationship between SQPS and QPS on the basis of the execution phase to obtain a phase change trend chart, as shown in fig. 5.

Substep 2703, performing keypoint display based on the target phase and the trend change data

Specifically, the embodiment of the application displays the key points of the phase change trend graph to the operation and maintenance personnel by comparing the time consumption of each execution phase, so that the key points are utilized to represent the slow phase, thereby being beneficial to the operation and maintenance personnel to quickly locate the slow phase and systematically optimize the slow phase. Linking the overall trends appears to provide the direction that the operation and maintenance personnel optimize for the system from the perspective of the system.

Furthermore, the scheme provided by the embodiment can effectively improve the operation and maintenance efficiency. When an operation and maintenance person positions the slow SQL or analyzes the slow SQL state of the system, other tools are not needed, related information can be directly obtained on an operation and maintenance interface, and man-machine interaction experience is improved. In addition, the influence on the business is small, and through the performance analysis of the bottom database, the slow SQL acquisition is carried out by the scheme in the business execution process, and the influence on the business is not more than 1%. The test results are shown in Table 2 below, and are generic. The method can be applied to the slow SQL acquisition process of databases such as MySQL, oracle and the like, and has wide applicability.

Table 2SQL Performance analysis

In summary, the embodiment of the application acquires the execution data corresponding to a plurality of slow SQL sentences in a preset period, wherein the execution data comprises at least one stage data of an execution stage and a stage index corresponding to each execution stage, so as to extract the execution time from the stage data, and aiming at each execution stage, trend analysis is performed on the execution time based on the stage index to obtain index time consumption corresponding to each execution stage, and the index time consumption is used as index change information, so as to perform aggregate analysis based on the index change information to obtain total time consumption of the slow SQL sentences, then the slow SQL sentences are counted to obtain the sentence number, analysis is performed based on the sentence number and the total time consumption to obtain the query rate, and the analysis data for assisting in analyzing the target stage is generated according to the query rate and the preset reference query rate.

It should be noted that, for simplicity of description, the method embodiments are shown as a series of acts, but it should be understood by those skilled in the art that the embodiments are not limited by the order of acts, as some steps may occur in other orders or concurrently in accordance with the embodiments.

As shown in fig. 6, an embodiment of the present application further provides a slow SQL statement analysis device 600 of a database, including:

an execution data obtaining module 610, configured to obtain execution data corresponding to a plurality of slow SQL statements in a preset period, where the execution data includes phase data of at least one execution phase and a phase index corresponding to each execution phase;

the index analysis module 620 is configured to, for each execution stage, perform index analysis on stage data of the execution stage based on a stage index corresponding to the execution stage, to obtain corresponding index change information;

the aggregation analysis processing module 630 is configured to perform aggregation analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL statements in the period;

the analysis data generation module 640 is configured to generate analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate, where the target stage is a stage to be optimized in at least one execution stage.

Optionally, the execution data acquisition module 610 includes:

the receiving sub-module is used for receiving a data acquisition request corresponding to the slow SQL statement, wherein the data acquisition request carries a slow log acquisition task, and the slow log acquisition task comprises data acquisition frequency and an acquisition index corresponding to the slow SQL statement;

the execution record table acquisition sub-module is used for acquiring an execution record table, and the execution record table records the execution information of the slow SQL statement;

and the data acquisition sub-module is used for acquiring the execution data of the slow SQL statement from the execution record table according to the data acquisition frequency based on the acquisition index.

Optionally, the slow SQL statement analysis device 600 of the database further includes:

the monitoring module is used for monitoring the execution stage of each slow SQL sentence to obtain accumulated value information, wherein the accumulated value information comprises the accumulated number of the slow SQL sentences and accumulated execution time;

and the generation module is used for generating the slow SQL statement execution information based on the accumulation number and the accumulation execution time and generating an execution record table based on the execution information.

Optionally, the index parsing module 620 includes:

An execution time extraction sub-module for extracting an execution time from the phase data;

and the trend analysis sub-module is used for carrying out trend analysis on the execution time based on the stage indexes for each execution stage to obtain index time consumption corresponding to each execution stage, and taking the index time consumption as the index change information.

Optionally, the aggregate analysis processing module 630 includes:

the statistics sub-module is used for counting the slow SQL sentences to obtain the sentence quantity;

the aggregation analysis sub-module is used for conducting aggregation analysis based on the index change information to obtain the total time consumption of the slow SQL sentence;

and the analysis sub-module is used for analyzing based on the statement number and the total time consumption to obtain the query rate.

the slicing determination module is used for determining slicing corresponding to the slow SQL statement and counting the frequency based on the slow SQL statement to obtain the execution times;

the temperature analysis module is used for carrying out dimension analysis on the fragments based on the statement number, the execution times and the total time consumption to obtain change trend information corresponding to the fragments;

And the display module is used for composing based on the change trend information to obtain a dimension trend graph of the slow SQL statement, and displaying based on the dimension trend graph.

Optionally, the analysis data generating module 640 includes:

the reference query rate acquisition sub-module is used for acquiring a preset reference query rate;

the composition sub-module is used for composition based on the query rate and the reference query rate to obtain a phase change trend graph, and the phase change trend graph is used as the analysis data, wherein the phase change trend graph comprises a target phase and trend change data corresponding to the target phase;

and the display sub-module is used for displaying the key points based on the target stage and the trend change data.

It should be noted that, the slow SQL statement analysis device of the database provided by the embodiment of the application can execute the slow SQL statement analysis method of the database provided by any embodiment of the application, and has the corresponding functions and beneficial effects of executing the slow SQL statement analysis method of the database.

In a specific implementation, the slow SQL statement analysis device of the database can be integrated in equipment, so that the equipment can analyze according to the acquired execution data to obtain the query rate corresponding to the slow SQL statement, and analysis data for assisting in analyzing a template stage is generated according to the query rate and the reference query rate and used as electronic equipment, so that an analysis flow for optimizing the slow SQL statement and assisting an operation and maintenance personnel in positioning and optimizing stages are realized. The electronic device may be formed of two or more physical entities or may be formed of one physical entity, for example, the electronic device may be a personal computer (Personal Computer, PC), a computer, a server, or the like, which is not particularly limited in the embodiment of the present application.

As shown in fig. 7, an embodiment of the present application provides an electronic device, including a processor 111, a communication interface 112, a memory 113, and a communication bus 114, where the processor 111, the communication interface 112, and the memory 113 perform communication with each other through the communication bus 114; a memory 113 for storing a computer program; the processor 111 is configured to implement the steps of the method for analyzing a slow SQL statement of a database provided in any one of the foregoing method embodiments when executing a program stored in the memory 113. Illustratively, the steps of the slow SQL statement analysis method of the database may comprise the steps of: acquiring execution data corresponding to a plurality of slow SQL sentences in a preset period, wherein the execution data comprises phase data of at least one execution phase and phase indexes corresponding to each execution phase; aiming at each execution stage, carrying out index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage to obtain corresponding index change information; performing aggregation analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL sentences in the period; and generating analysis data for assisting in analyzing a target stage according to the query rate and a preset reference query rate, wherein the target stage is a stage to be optimized in at least one execution stage.

The embodiment of the application also provides a computer readable storage medium, on which a computer program is stored, which when executed by a processor, implements the steps of the slow SQL statement analysis method of the database provided in any one of the method embodiments described above.

It should be noted that in this document, relational terms such as "first" and "second" and the like are used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Moreover, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.

The foregoing is only a specific embodiment of the application to enable those skilled in the art to understand or practice the application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for analyzing slow SQL statements of a database, comprising:

2. The method of claim 1, wherein the obtaining the execution data corresponding to the plurality of slow SQL statements in the preset period comprises:

3. The method of claim 2, wherein prior to the obtaining the execution record table, further comprising:

4. The method according to claim 1, wherein for each execution stage, performing index analysis on the stage data of the execution stage based on the stage index corresponding to the execution stage to obtain corresponding index change information, includes:

extracting execution time from the phase data;

5. The method of claim 1, wherein the performing aggregate analysis processing on all the index change information to obtain query rates corresponding to a plurality of slow SQL statements in the period comprises:

counting the slow SQL sentences to obtain the sentence quantity;

6. The method of claim 5, wherein prior to obtaining the query rate, further comprising:

7. The method of claim 1, wherein generating analysis data for assisting in analyzing a target phase in accordance with the query rate and a preset reference query rate comprises:

acquiring a preset reference query rate;

and displaying key points based on the target stage and the trend change data.

8. A slow SQL statement analysis device for a database, comprising:

9. The electronic equipment is characterized by comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory are communicated with each other through the communication bus;

A memory for storing a computer program;

a processor for implementing the steps of the method for analyzing slow SQL statements of a database according to any one of claims 1 to 7 when executing a program stored on a memory.

10. A computer readable storage medium having stored thereon a computer program, which when executed by a processor performs the steps of the slow SQL statement analysis method of a database according to any of claims 1-7.