Method, device, equipment and medium for analyzing slow SQL statements in database

Abstract

The present application relates to a method, apparatus, device, and medium for analyzing slow SQL statements in a database, and relates to the field of database technology. The method comprises: obtaining execution data corresponding to multiple slow SQL statements within a preset period, the execution data comprising stage data of at least one execution stage and stage indicators corresponding to each execution stage; for each execution stage, performing indicator analysis on the stage data of the execution stage based on the stage indicators corresponding to the execution stage to obtain corresponding indicator change information; performing aggregate analysis processing on all indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period; and generating analysis data for assisting in analyzing a target stage based on the query rates and a preset benchmark query rate, thereby effectively optimizing the analysis process of slow SQL statements, enabling accurate analysis of optimization stages that affect the operation of the database system, and helping operation and maintenance personnel optimize the database system.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method, device, equipment, and medium for analyzing slow SQL statements in a database. Background Art

[0002] With the advent of the big data era, databases are encountering performance bottlenecks when processing queries, statistics, and analysis of terabytes, and especially petabytes, of data. Faced with large-scale data applications in telecommunications, finance, security, government, and enterprise sectors, including telecom call bills, detailed financial ledgers, business analysis, public security monitoring, and audit inspections, the user experience is often unacceptable. Driven by the impact of the internet and big data applications, the massive and diverse volume of data presents a significant challenge to database developers and operations personnel. However, in specific projects, hundreds or even thousands of Structured Query Language (SQL) statements are often distributed across code bases, performing extensive database access and operations in real time. This makes it difficult for operations personnel to identify code efficiency bottlenecks during project execution. Accurately and quickly locating slow SQL statements has become a major challenge in database operations.

[0003] Existing slow SQL statement analysis methods primarily rely on developers analyzing and screening the query characteristics of collected slow SQL statements, focusing on those with high query counts and long query times. However, slow SQL statements have multiple execution phases, each with varying durations. Existing slow SQL statement analysis methods cannot accurately identify the execution phases that can be optimized, relying instead on operations and maintenance personnel to expend time, effort, and valuable experience to identify the phases that can be optimized from massive amounts of data, resulting in a waste of manpower and resources. Summary of the Invention

[0004] The present application provides a method, apparatus, device, and medium for analyzing slow SQL statements in a database, thereby effectively optimizing the analysis process of slow SQL statements. This method enables accurate analysis of optimization phases that affect the operation of the database system, helping operations and maintenance personnel optimize the database system. This solves the problem that existing slow SQL statement analysis methods cannot accurately locate execution phases that can be optimized.

[0005] In a first aspect, the present application provides a method for analyzing slow SQL statements in a database, comprising:

[0006] Obtain execution data corresponding to multiple slow SQL statements within a preset period, wherein the execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage;

[0007] For each execution stage, performing indicator analysis on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information;

[0008] Performing aggregate analysis on all the indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period;

[0009] According to the query rate and a preset benchmark query rate, analysis data for assisting in analyzing a target phase is generated, wherein the target phase is a phase to be optimized in at least one of the execution phases.

[0010] Optionally, obtaining execution data corresponding to multiple slow SQL statements within a preset period includes:

[0011] Receive a data collection request corresponding to the slow SQL statement, where the data collection request carries a slow log collection task, and the slow log collection task includes a data collection frequency and a collection indicator corresponding to the slow SQL statement;

[0012] Obtaining an execution record table, wherein the execution record table records execution information of the slow SQL statement;

[0013] Based on the collection indicator, the execution data of the slow SQL statement is collected from the execution record table according to the data collection frequency.

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

[0015] Monitor the execution phase of each slow SQL statement to obtain accumulated value information, where the accumulated value information includes the accumulated number of slow SQL statements and the accumulated execution time;

[0016] The slow SQL statement execution information is generated based on the accumulated number of items and the accumulated execution time, and an execution record table is generated based on the execution information.

[0017] Optionally, for each execution stage, performing indicator parsing on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information includes:

[0018] extracting execution time from the stage data;

[0019] For each of the execution stages, a trend analysis is performed on the execution time based on the stage indicators to obtain the indicator time consumption corresponding to each of the execution stages, and the indicator time consumption is used as the indicator change information.

[0020] Optionally, performing aggregate analysis on all the indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period includes:

[0021] Counting the slow SQL statements to obtain the number of statements;

[0022] Perform aggregate analysis based on the indicator change information to obtain the total time consumption of the slow SQL statement;

[0023] The query rate is obtained by analyzing the number of statements and the total time consumption.

[0024] Optionally, before obtaining the query rate, the method further includes:

[0025] Determine the shard corresponding to the slow SQL statement, and obtain the number of executions based on the frequency statistics of the slow SQL statement;

[0026] For the shard, perform dimensional analysis based on the number of statements, the number of executions, and the total time consumed to obtain change trend information corresponding to the shard;

[0027] A graph is constructed based on the change trend information to obtain a dimensional trend graph of the slow SQL statement, and the graph is displayed based on the dimensional trend graph.

[0028] Optionally, generating analysis data for assisting in analyzing the target phase based on the query rate and a preset benchmark query rate includes:

[0029] Get the preset benchmark query rate;

[0030] composing a graph based on the query rate and the benchmark query rate to obtain a stage change trend graph, and using the stage change trend graph as the analysis data, the stage change trend graph including a target stage and trend change data corresponding to the target stage;

[0031] Key points are displayed based on the target stage and the trend change data.

[0032] In a second aspect, the present application provides a device for analyzing slow SQL statements in a database, comprising:

[0033] An execution data acquisition module is used to acquire execution data corresponding to multiple slow SQL statements within a preset period, wherein the execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage;

[0034] An indicator parsing module is used to perform indicator parsing on the stage data of each execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information;

[0035] An aggregation analysis and processing module is used to perform aggregation analysis on all the indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period;

[0036] The analysis data generation module is used to generate analysis data for assisting in analyzing a target phase based on the query rate and a preset benchmark query rate, wherein the target phase is a phase to be optimized in at least one of the execution phases.

[0037] In a third aspect, the present application provides an electronic device, comprising a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other via the communication bus;

[0038] Memory for storing computer programs;

[0039] The processor is used to implement the steps of the slow SQL statement analysis method of the database as described in any embodiment of the first aspect when executing the program stored in the memory.

[0040] In a fourth aspect, the present application provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method for analyzing slow SQL statements in a database as described in any one of the embodiments of the first aspect.

[0041] In summary, the embodiment of the present application obtains execution data corresponding to multiple slow SQL statements within a preset period, and the execution data includes stage data of at least one execution stage and stage indicators corresponding to each execution stage. For each execution stage, the stage data of the execution stage is analyzed based on the stage indicators corresponding to the execution stage to obtain corresponding indicator change information. All indicator change information is aggregated and analyzed to obtain the query rate corresponding to multiple slow SQL statements within the period. Based on the query rate and the preset benchmark query rate, analysis data for assisting in analyzing the target stage is generated, thereby effectively optimizing the analysis process of slow SQL statements, being able to accurately analyze the optimization stage that affects the operation of the database system, helping operation and maintenance personnel to optimize the database system, and solving the problem that the existing slow SQL statement analysis method cannot accurately locate the execution stage that can be optimized. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0043] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0044] Figure 1 A flowchart of a method for analyzing slow SQL statements in a database provided in an embodiment of the present application;

[0045] Figure 2 This is a schematic flow chart of the steps of a method for analyzing slow SQL statements in a database provided by an optional embodiment of the present application;

[0046] Figure 3 This is a data collection flow chart provided by an optional embodiment of the present application;

[0047] Figure 4 This is an overview diagram of a data visualization interface provided by an optional embodiment of the present application;

[0048] Figure 5 This is an indicator change trend graph provided by an optional embodiment of the present application;

[0049] Figure 6 A structural block diagram of a slow SQL statement analysis device for a database provided in an embodiment of the present application;

[0050] Figure 7 This is a structural diagram of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0051] To make the purpose, technical solutions, and advantages of the embodiments of this application more clear, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0052] To improve database performance and stability, database operations and maintenance personnel must exercise extreme caution in database operations, diligently optimizing every SQL statement for additions, deletions, modifications, and queries. However, in specific projects, hundreds or even thousands of SQL statements are often distributed across the codebase, with numerous database accesses and operations performed in real time. This makes it difficult for operations and maintenance personnel to identify efficiency bottlenecks in the code during project execution. Therefore, accurately and quickly locating slow SQL statements becomes a primary challenge in database operations and maintenance.

[0053] To this end, the existing technology mainly collects database query logs and analyzes and locates slow SQL statements from the database query logs. Generally, the collection of database slow query logs requires the database to record the time consumed in each execution stage of each slow SQL statement that exceeds the execution time threshold, and to persist the collected content on the disk. In this process, the database's disk and IO resources are occupied by the slow query log collection process. With the exponential growth of data volume and concurrent access volume, a large number of key businesses have put forward higher requirements for the database's responsiveness. Enabling the slow query log collection task in a high-concurrency environment will generate a large number of slow SQL logs. Under the condition that the overall resource volume remains unchanged, the resources of the database itself will be affected. After consuming a part of the resources to obtain the slow query logs, the operation and maintenance personnel need to analyze and screen the massive slow SQL logs. SQL with a large number of queries and a long query time, SQL with large IO resource consumption, and SQL without index hits are usually the objects that need to be paid special attention to. During the analysis process, operations personnel typically use screening and analysis tools, such as the database's own tools or third-party tools like pt-query-digest, to filter out log content and SQL statement statistics that meet the above criteria, thereby identifying slow SQL statements that require optimization. However, existing technologies can only identify slow SQL statements, which have multiple execution phases and varying execution times. This requires operations personnel to spend time sifting through massive amounts of data to identify the execution phases that can be optimized, resulting in a significant waste of manpower.

[0054] To solve the above problems, the present application provides a method, device, equipment and medium for analyzing slow SQL statements of a database. By obtaining execution data corresponding to multiple slow SQL statements within a preset period, the execution data includes stage data of at least one execution stage and stage indicators corresponding to each execution stage. For each execution stage, the stage data of the execution stage is analyzed based on the stage indicators corresponding to the execution stage to obtain corresponding indicator change information. All indicator change information is aggregated and analyzed to obtain the query rate corresponding to multiple slow SQL statements within the period. Based on the query rate and the preset benchmark query rate, analysis data for assisting in analyzing the target stage is generated, thereby effectively optimizing the analysis process of slow SQL statements, being able to accurately analyze the optimization stage that affects the operation of the database system, helping operation and maintenance personnel to optimize the database system, solving the problem that the existing slow SQL statement analysis method cannot accurately locate the execution stage that can be optimized, effectively improving the work efficiency of operation and maintenance personnel, and saving time.

[0055] To facilitate understanding of the embodiments of the present application, further explanation will be given below in conjunction with the drawings and specific embodiments. The embodiments do not constitute a limitation on the embodiments of the present application.

[0056] Figure 1The following is a flow chart of a method for analyzing slow SQL statements in a database provided in an embodiment of the present application. Figure 1 As shown, the method for analyzing slow SQL statements in a database provided in an embodiment of the present application may specifically include the following steps:

[0057] Step 110: Obtain execution data corresponding to multiple slow SQL statements within a preset period.

[0058] The execution data includes phase data of at least one execution phase and phase indicators corresponding to each execution phase.

[0059] Specifically, a database SQL statement (including a slow SQL statement) may involve multiple execution stages during the execution process, and each execution stage has corresponding stage data. For example, the stage data may include execution time, etc. The embodiment of the present application does not limit this.

[0060] Specifically, the embodiment of the present application can select an execution stage that can fully reflect the optimization points of slow SQL statements from multiple execution stages as a stage indicator. When the database system executes SQL statements, by recording the stage data of the SQL statements at each execution stage, a database query log is generated. Subsequently, a tool is used to analyze the slow SQL statements from the database query log, so that the stage data corresponding to the stage indicator can be extracted from the query log of the slow SQL statements.

[0061] In a specific implementation, this embodiment can set a collection period to obtain execution data corresponding to multiple slow SQL statements within a preset period.

[0062] As an example, refer to Table 1 below, which shows the execution stages corresponding to slow SQL statements and their meanings. In this embodiment, the execution stages in Table 1 can be used as stage indicators. Based on the stage indicators, the corresponding time consumption indicator can be obtained from the query log of the slow SQL statement as the stage data corresponding to the slow SQL statement.

[0063]

[0064] Table 1 Execution phases and their corresponding meanings

[0065] Step 120 : For each execution stage, perform indicator analysis on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information.

[0066] Specifically, the indicator change information may include changes in all slow SQL statements in the current execution phase. For example, the changes may include the total time consumed by all slow SQL statements in the current execution phase. This example does not impose any limitation on this.

[0067] In specific implementations, for each execution phase, the time consumed by all slow SQL statements within that phase can be counted. The total time consumed by the slow SQL statements in each execution phase can be accumulated to obtain indicator change information. For example, if there are four slow SQL statements that need to be analyzed, using the indicator "net_write_time" in Table 1 as an example, the indicator change information for this indicator during the execution phase is the total time consumed by the four slow SQL statements during the "net_write_time" execution phase.

[0068] Step 130 : performing aggregate analysis on all the indicator change information to obtain query rates corresponding to the plurality of slow SQL statements within the period.

[0069] Specifically, the query rate corresponding to the slow SQL statement can be the slow SQL statement query rate per second, also known as SQPS (Slow Queries Per Second), which is not limited in the embodiment of the present application.

[0070] In a specific implementation, in order to further analyze the possible problems of the current system (such as a database system), the overall performance of the system can be optimized in a targeted manner. This embodiment can define multiple indicators for statistically analyzing the trend of slow SQL statements in the execution phase. For example, the multiple indicators may include but are not limited to SQPS and QPS (Queries Per Second). By analyzing the trend relationship between SQPS and QPS in the execution phase, operation and maintenance personnel can be assisted to quickly locate the stage where a failure or congestion occurs in the system. Specifically, all indicator change information can be aggregated, such as the time consumption corresponding to all indicator change information can be accumulated and calculated, combined with the number of statements of all slow SQL statements, to obtain the slow SQL statement query rate per second.

[0071] For example, you can count slow SQL statements within a preset period to determine the number of slow SQL statements, that is, the number of slow SQL statements executed within the preset period. You can also count the time taken by slow SQL statements within the preset period to obtain the total time taken. You can then use the total time taken and the number of executions to calculate the query rate, which will give you the slow SQL statement query rate per second. This slow SQL statement query rate per second can then be used to determine the stage of slow SQL statement execution that can be optimized.

[0072] Step 140: Generate analysis data for assisting in analyzing the target phase based on the query rate and a preset benchmark query rate.

[0073] The target stage is a stage to be optimized in at least one of the execution stages.

[0074] Specifically, the benchmark query rate can be the query rate per second between all SQL statements and the total time consumed within the preset period, also known as QPS, and the embodiments of the present application do not impose any restrictions on this; the analysis data can include a stage change trend chart, and the stage change trend chart can include a target stage and trend change data corresponding to the target stage, wherein the trend change data can be the relationship data between the query rate in the current target stage and the benchmark query rate, and the embodiments of the present application do not impose any restrictions on this.

[0075] Specifically, this embodiment can pre-aggregate and analyze all SQL statements within a preset period to obtain the number of executions and the total time consumed by all SQL statements, and then determine the query rate per second within the preset period as the benchmark query rate, i.e., QPS. Then, QPS and SQPS are combined for relationship analysis to obtain relationship data, and each execution stage is used as a benchmark to construct a stage change trend chart using the relationship data, i.e., the analysis data of the target stage. By showing the stage change trend chart to the operation and maintenance personnel, the operation and maintenance personnel can analyze the relationship data of QPS and SQPS to locate which execution stage has congestion or failure, so as to quickly determine the target stage that affects business execution, which helps the operation and maintenance personnel to systematically optimize the slow stage, effectively optimizes the analysis process of slow SQL statements, and can accurately analyze the optimization stage that affects the operation of the database system, which helps assist the operation and maintenance personnel in optimizing the database system.

[0076] It can be seen that the embodiment of the present application obtains execution data corresponding to multiple slow SQL statements within a preset period, and the execution data includes stage data of at least one execution stage and stage indicators corresponding to each execution stage. For each execution stage, the stage data of the execution stage is analyzed based on the stage indicators corresponding to the execution stage to obtain corresponding indicator change information, and all indicator change information is aggregated and analyzed to obtain the query rate corresponding to multiple slow SQL statements within the period. According to the query rate and the preset benchmark query rate, analysis data for assisting in analyzing the target stage is generated, thereby effectively optimizing the analysis process of slow SQL statements, being able to accurately analyze the optimization stage that affects the operation of the database system, helping to assist operation and maintenance personnel in optimizing the database system, and solving the problem that the existing slow SQL statement analysis method cannot accurately locate the execution stage that can be optimized.

[0077] Reference Figure 2 , shows a schematic flow chart of the steps of a method for analyzing slow SQL statements in a database provided by an optional embodiment of the present application. The method for analyzing slow SQL statements in a database may specifically include the following steps:

[0078] Step 210: Obtain execution data corresponding to multiple slow SQL statements within a preset period.

[0079] The execution data includes phase data of at least one execution phase and phase indicators corresponding to each execution phase.

[0080] In a specific implementation, in order to realize the storage of slow query log content and reduce resource consumption, and assist operation and maintenance personnel in efficiently locating slow SQL statements, this embodiment can pre-build an operation and maintenance monitoring system, and quickly obtain the query log of the slow SQL statements of the database through the operation and maintenance monitoring system, and can realize the analysis of slow SQL statements, and display the optimizable points based on the analysis results, thereby assisting operation and maintenance personnel to quickly locate slow SQL statements and quickly locate system optimization points, and improve operation and maintenance efficiency. Specifically, the operation and maintenance monitoring system can include an operation and maintenance server (InsightServer, IS) and an operation and maintenance acquisition terminal agent (InsightAgent, IA), and this embodiment of the application does not limit this. Among them, IS can send a periodic slow log collection task to IA, and IA executes a specified SQL to collect data every corresponding period through the long link channel of the connection pool between the database data node (Data Node, DN), thereby obtaining the execution data corresponding to multiple slow SQL statements within the preset period. After IA obtains the execution result, it sends the slow log content to IS according to the data dictionary agreed with IS, and IS puts the data into the metadata database (Redis DataBase, RDB) for persistent storage.

[0081] It should be noted that in this embodiment, the IS is the primary operator of operation and maintenance work, consisting of a web interface displaying the database's operating status and a server supporting operation and maintenance tasks. Within the process, it issues collection tasks, stores collected data, and displays database status. The IA is the "collector" of operation and maintenance-related data, responsible for receiving scheduled collection tasks from the IS, executing task commands at designated locations, and acquiring collected data. The DN is the "executor" of the database's primary operations, responsible for fulfilling database responsibilities, executing operations, and recording its own operating status. The RDB is the metadata repository, responsible for storing data required for database system operation.

[0082] In an optional embodiment, the embodiment of the present application obtains execution data corresponding to multiple slow SQL statements within a preset period, which may specifically include: receiving a data collection request corresponding to the slow SQL statement, the data collection request carries a slow log collection task, the slow log collection task includes a data collection frequency and a collection indicator corresponding to the slow SQL statement; obtaining an execution record table, the execution record table records the execution information of the slow SQL statement; based on the collection indicator, data is collected on the execution stage of the slow SQL statement according to the data collection frequency to obtain the execution data.

[0083] In an optional embodiment, before obtaining the execution record table, the embodiment of the present application may further include: monitoring the execution stage of each of the slow SQL statements to obtain cumulative value information, wherein the cumulative value information includes the cumulative number of the slow SQL statements and the cumulative execution time; generating the slow SQL statement execution information based on the cumulative number and the cumulative execution time, and generating the execution record table based on the execution information.

[0084] Specifically, the memory of the database node (DN) stores the cumulative duration of each SQL statement execution phase. Each time the DN executes a slow SQL statement, the duration of each corresponding execution phase increases. The IA executes commands provided by the DN to view these cumulative values ​​at regular collection intervals, obtains these cumulative values, and pushes them to the IS for subsequent analysis.

[0085] Furthermore, for the execution status corresponding to each execution stage of the slow SQL statement, an execution record table can be set to record the execution status. If the execution record table can be a system table of DN, the embodiment of the present application does not limit this, that is, the system table can store records of slow SQL execution status. Among them, the execution status table can contain multiple field contents, such as the field content can contain slow SQL statements, numbers, last appearance time, total number of items (cumulative value) and total execution time (cumulative value), etc. The slow SQLs of the same number or patterned slow SQL statements are accumulated through the cumulative value. Similarly, the IA executes the SQL statement collection content every certain collection period and pushes it to the IS for subsequent processing. By adopting this method of recording cumulative values, which is different from the traditional collection method, the burden of DN when slow SQL collection is turned on is reduced, and the performance is improved. Moreover, by using this method of recording cumulative values, when performing data collection, the consumption of system resources can be effectively reduced to avoid the impact on the business.

[0086] In practice, to facilitate rapid analysis by operations personnel, an operations interface (also known as a data visualization page) can be built based on the operations monitoring system. This interface allows operations personnel to interact with the system, enabling them to enable or disable execution data collection for slow SQL statements and view subsequent slow SQL statement analysis results. The process for locating slow SQL statements can be as follows: enter the operations interface, then -> Statistics Monitoring, then -> Diagnosis, then -> Slow Daily Analysis.

[0087] For example, refer to Figure 3 , Figure 3This is a data collection flow chart provided by an optional embodiment of the present application. The user can select the instance to be viewed through the operation and maintenance interface to determine the collection switch status of the slow log (i.e., the execution data of the slow SQL statement). By turning on the switch in the DN configuration under the instance and the slow log collection task switch on the operation and maintenance server, the execution data collection of the slow SQL statement can be turned on / off. The operation and maintenance personnel can set the data collection frequency and the collection indicators corresponding to the slow SQL statement through the operation and maintenance interface. After the operation and maintenance personnel turn on the collection switch, the operation and maintenance interface constructs the slow log collection task according to the data collection frequency and collection indicators set by the operation and maintenance personnel, and then generates a data collection request based on the slow log collection task and sends the data collection request to the IS. After the IS receives the data collection request, the slow log collection task carried by the data collection request sends a collection command to the IA (the collection command may carry a scheduled collection task). After receiving the collection command sent by the IS, the IA periodically executes the SQL command according to the scheduled collection task carried by the collection command, obtains the slow SQL information (i.e., the slow log) from the RDB bottom layer, and can send the collection results as the execution data corresponding to multiple slow SQL statements within a preset period to the IS for subsequent parsing and other processing by the IS.

[0088] Step 220: extract the execution time from the stage data.

[0089] This embodiment may extract the execution time from the stage data. The execution time may be the time taken by the slow SQL statement to complete the execution stage, which is not limited in this embodiment.

[0090] Step 230 : For each of the execution stages, a trend analysis is performed on the execution time based on the stage indicator to obtain the indicator time consumption corresponding to each of the execution stages, and the indicator time consumption is used as the indicator change information.

[0091] In a specific implementation, this embodiment can divide the periodically collected stage data, distinguish each execution stage and its corresponding execution time according to the stage index, and obtain all the execution times corresponding to each execution stage. Subsequently, the execution time can be trend analyzed. For example, all the execution times of the execution stage can be accumulated and calculated through IS to obtain the total execution time corresponding to all slow SQL statements in the current execution stage, which is used as the indicator time. By accumulating the time consumption for each execution stage, the time consumption of the slow SQL statements in each execution stage can be clearly known, and the execution stages that can be optimized can be effectively analyzed.

[0092] Step 240: Count the slow SQL statements to obtain the number of statements.

[0093] In a specific implementation, the embodiment of the present application can accumulate all slow SQL statements to obtain the total number of slow SQL statements, that is, the total number of all executed slow SQL statements in the cycle. For example, for periodically collected slow SQL statement log data, all executed slow SQL statements can be determined based on the slow SQL statement log data, and the number of all executed slow SQL statements can be counted to obtain the total number of slow SQL statements executed as the statement count.

[0094] Step 250: Perform aggregate analysis based on the indicator change information to obtain the total time consumption of the slow SQL statement.

[0095] In a specific implementation, this embodiment can aggregate and analyze the cumulative execution time of each execution phase. For example, the total execution time of all execution phases can be accumulated to obtain the total execution time of all slow SQL statements in all execution phases within the cycle. By analyzing the execution of slow SQL statements from multiple dimensions, key information such as the number of statements, execution times, and total execution time can be obtained. This key information can then be used to further analyze slow SQL statements and identify areas where optimization is possible in the database system.

[0096] Step 260: Analyze based on the number of statements and the total time consumption to obtain the query rate.

[0097] In a specific implementation, this embodiment can analyze and calculate the number of statements and the total time consumption to determine the query rate per second (i.e., SQPS) of slow SQL statements in the current cycle, and obtain the query rate, so that it can be combined with the benchmark query rate for further analysis, thereby assisting operation and maintenance personnel in locating and analyzing slow SQL statements.

[0098] Furthermore, in order to assist operation and maintenance personnel in locating slow SQL statements that are executed many times and take a long time during the unstable period of the system, this embodiment can further analyze the number of executions of slow SQL statements within the cycle after analyzing the slow SQL statements to obtain the number of statements and the total time consumed. For example, slow SQL statements may include but are not limited to "COMMIT" and "SHOWMASTER STATUS". Within a cycle, the number of executions of each slow SQL statement may be uncertain. This embodiment can aggregate all executed slow SQL statements, aggregate the same slow SQL statements together, and perform cumulative calculations to obtain the number of executions of each slow SQL statement. By performing multi-dimensional analysis and statistics on slow SQL statements from dimensions such as total time consumed, number of statements, and number of executions, the execution status of slow SQL statements can be displayed in multiple dimensions on the interface when it is necessary to display them, thereby helping operation and maintenance personnel to perform analysis.

[0099] In an optional embodiment, before obtaining the query rate, the embodiment of the present application may further include: determining the shard corresponding to the slow SQL statement, and performing frequency statistics based on the slow SQL statement to obtain the number of executions; for the shard, performing dimensional analysis based on the number of statements, the number of executions, and the total time consumed to obtain change trend information corresponding to the shard; composing a graph based on the change trend information to obtain a dimensional trend graph of the slow SQL statement, and displaying it based on the dimensional trend graph.

[0100] As an example, see Figure 4 After confirming the presence of slow log data, operations personnel can visually view the overview of slow SQL statements within a specified time span in the Overview section of the Slow SQL data visualization page. Using the Overview status bar, operations personnel can directly locate the top 10 shards with the highest number of slow SQL statements or the highest execution time, as well as the most frequently executed or time-consuming patterned slow SQL statements. Specifically, multi-dimensional analysis and statistics can be performed on the total execution time, number of statements, and number of executions of slow SQL statements at each execution stage to generate analytical data, which can then be used for presentation. Different slow SQL statements may correspond to different shards. To compare slow SQL statements from different shards and comprehensively identify slow SQL statement execution issues, a dimensional trend chart (referred to as a trend chart) can be viewed. The trend chart primarily displays the overall trend of slow SQL statements for the current shard, using the number, duration, and number of rows scanned as dimensions. The trend chart supports zooming in and out of the time range for easier analysis. Clicking a data point in the chart displays the top 15 patterned SQL statements sorted by time or number for the selected time period. By using the mutation points in the trend chart, operation and maintenance personnel can quickly locate slow SQL statements that are executed frequently and take a long time during periods of system instability.

[0101] In its implementation, the IS converts the accumulated execution time data for each stage of the slow query log into incremental values ​​for each collection period and stores them in a table. SQL content data is stored separately from statistical data and linked using an auto-incrementing ID associated with each schematized SQL statement when it is entered into the table. After completing statistics and analysis, operations and maintenance personnel can conveniently analyze and locate slow SQL statements using a pre-designed page displaying relevant slow query log data.

[0102] Step 270: Generate analysis data for assisting in analyzing the target phase based on the query rate and a preset benchmark query rate.

[0103] The target stage is a stage to be optimized in at least one of the execution stages.

[0104] In a specific implementation, in order to further analyze the possible problems of the current system in order to optimize the overall performance of the system in a targeted manner, the embodiment of the present application can analyze the execution stages that affect the system performance from the relationship trend of SQPS and QPS quality inspection. Specifically, by comparing the relationship trend between SPQS and QPS with the time consumption trend of each execution stage in each time period, the execution stage of the fault is statistically located. Generally speaking, QPS and SQPS are proportional. If the relationship between the two changes, the execution stage statistical trend chart can be used to locate which stage of the system has congestion or failure, thereby effectively determining the target stage, so as to assist operation and maintenance personnel in optimizing the target stage and optimizing the stage that affects business execution.

[0105] Optionally, the generating of analysis data for assisting in analyzing the target phase based on the query rate and a preset benchmark query rate may include the following sub-steps:

[0106] Sub-step 2701: obtaining a preset benchmark query rate.

[0107] Specifically, for QPS, this embodiment can periodically obtain database query logs, determine the total number of SQL data executions and the corresponding total time consumption within the period through statistical analysis of the database query logs, and then obtain QPS based on the total number of SQL data executions and the total time consumption as a preset benchmark query rate.

[0108] Sub-step 2702, composing a graph based on the query rate and the benchmark query rate to obtain a stage change trend graph, and using the stage change trend graph as the analysis data, the stage change trend graph including the target stage and the trend change data corresponding to the target stage.

[0109] In a specific implementation, the embodiment of the present application can perform statistical analysis on SQPS and QPS to obtain the relationship between QPS and SPQS, so that system anomaly information can be obtained by comparing the relationship between the current system QPS and SPQS. Specifically, this embodiment can use the relationship between SQPS and QPS as the basis for the execution stage to construct a graph to obtain a stage change trend graph, as shown in Figure 5.

[0110] Sub-step 2703: Display key points based on the target stage and the trend change data

[0111] Specifically, the embodiments of the present application compare the time taken for each execution phase to show the key points of the phase change trend graph to the operation and maintenance personnel. By using key points to characterize slow phases, the operation and maintenance personnel can quickly locate slow phases and perform systematic optimization for these phases. Linking the overall trends can provide the operation and maintenance personnel with guidance for system optimization from a system perspective.

[0112] Furthermore, the solution provided by this embodiment can effectively improve operation and maintenance efficiency. When operation and maintenance personnel locate slow SQL or analyze the slow SQL status of the system, they do not need to use other tools. They can directly obtain relevant information on the operation and maintenance interface, thereby improving the human-computer interaction experience. In addition, the impact on the business is also relatively small. After analyzing the performance of the underlying database, the impact of slow SQL collection using this solution during the business execution process does not exceed 1%. The performance analysis of the stress test is shown in Table 2 below, which is universal. The present invention can be applied to the slow SQL collection process of databases such as MySQL and Oracle, and has a wide range of applicability.

[0113]

[0114]

[0115] Table 2 SQL performance analysis

[0116] In summary, the embodiments of the present application obtain execution data corresponding to multiple slow SQL statements within a preset period. The execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage. The execution time is extracted from the stage data. For each execution stage, a trend analysis of the execution time is performed based on the stage indicator to obtain the indicator time consumption corresponding to each execution stage. The indicator time consumption is used as indicator change information. Aggregate analysis is performed based on the indicator change information to obtain the total time consumption of the slow SQL statements. The slow SQL statements are then counted to obtain the number of statements. Analysis is performed based on the number of statements and the total time consumption to obtain the query rate. Analysis data for assisting in analyzing the target stage is generated based on the query rate and a preset benchmark query rate. Compared with existing slow SQL statement analysis methods, the database slow SQL statement analysis method provided in this embodiment can effectively optimize the analysis process of slow SQL statements, reduce the impact on database system services during analysis, and has universal applicability. Moreover, by accurately analyzing the optimization stages that affect the operation of the database system, the target stage to be optimized can be accurately located, which helps assist operation and maintenance personnel in optimizing the database system and solves the problem that existing slow SQL statement analysis methods cannot accurately locate the execution stages that can be optimized.

[0117] It should be noted that, for the purpose of simple description, the method embodiments are expressed as a series of action combinations, but those skilled in the art should know that the embodiments of the present application are not limited to the described order of actions, because according to the embodiments of the present application, certain steps can be performed in other orders or simultaneously.

[0118] like Figure 6 As shown, the embodiment of the present application further provides a slow SQL statement analysis device 600 for a database, comprising:

[0119] An execution data acquisition module 610 is configured to acquire execution data corresponding to multiple slow SQL statements within a preset period, wherein the execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage;

[0120] An indicator parsing module 620 is configured to perform indicator parsing on the stage data of each execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information;

[0121] Aggregation analysis processing module 630, configured to perform aggregation analysis on all the indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period;

[0122] The analysis data generation module 640 is configured to generate analysis data for assisting in analyzing a target phase based on the query rate and a preset benchmark query rate, wherein the target phase is a phase to be optimized in at least one of the execution phases.

[0123] Optionally, the execution data acquisition module 610 includes:

[0124] A receiving submodule, configured to receive a data collection request corresponding to the slow SQL statement, wherein the data collection request carries a slow log collection task, and the slow log collection task includes a data collection frequency and a collection indicator corresponding to the slow SQL statement;

[0125] An execution record table acquisition submodule is used to obtain an execution record table, wherein the execution record table records the execution information of the slow SQL statement;

[0126] A data collection submodule is configured to collect the execution data of the slow SQL statement from the execution record table based on the collection indicator and at the data collection frequency.

[0127] Optionally, the database slow SQL statement analysis device 600 further includes:

[0128] A monitoring module, configured to monitor the execution phase of each slow SQL statement to obtain accumulated value information, wherein the accumulated value information includes the accumulated number of slow SQL statements and the accumulated execution time;

[0129] A generation module is used to generate the slow SQL statement execution information based on the accumulated number of items and the accumulated execution time, and to generate an execution record table based on the execution information.

[0130] Optionally, the indicator analysis module 620 includes:

[0131] An execution time extraction submodule, configured to extract execution time from the stage data;

[0132] The trend analysis submodule is used to perform trend analysis on the execution time based on the stage indicator for each of the execution stages, obtain the indicator time consumption corresponding to each of the execution stages, and use the indicator time consumption as the indicator change information.

[0133] Optionally, the aggregation analysis processing module 630 includes:

[0134] A statistics submodule is used to count the slow SQL statements and obtain the number of statements;

[0135] An aggregation analysis submodule is used to perform aggregation analysis based on the indicator change information to obtain the total time consumption of the slow SQL statement;

[0136] The analysis submodule is configured to perform analysis based on the number of statements and the total time consumption to obtain the query rate.

[0137] Optionally, the database slow SQL statement analysis device 600 further includes:

[0138] A shard determination module is used to determine the shard corresponding to the slow SQL statement, and to obtain the number of executions based on the frequency statistics of the slow SQL statement;

[0139] A temperature analysis module is used to perform dimensional analysis on the shard based on the number of statements, the number of executions, and the total time consumed to obtain change trend information corresponding to the shard;

[0140] A display module is used to compose a graph based on the change trend information, obtain a dimensional trend graph of the slow SQL statement, and display it based on the dimensional trend graph.

[0141] Optionally, the analysis data generation module 640 includes:

[0142] A benchmark query rate acquisition submodule is used to obtain a preset benchmark query rate;

[0143] a mapping submodule, configured to perform mapping based on the query rate and the benchmark query rate to obtain a stage change trend graph, and use the stage change trend graph as the analysis data, wherein the stage change trend graph includes a target stage and trend change data corresponding to the target stage;

[0144] The display submodule is used to display key points based on the target stage and the trend change data.

[0145] It should be noted that the slow SQL statement analysis device for a database provided in an embodiment of the present application can execute the slow SQL statement analysis method for a database provided in any embodiment of the present application, and has the corresponding functions and beneficial effects of executing the slow SQL statement analysis method for a database.

[0146] In a specific implementation, the slow SQL statement analysis device of the above-mentioned database can be integrated into the device, so that the device can analyze based on the acquired execution data, obtain the query rate corresponding to the slow SQL statement, and generate analysis data for assisting the analysis template stage based on the query rate and the benchmark query rate. As an electronic device, it realizes the analysis process of optimizing the slow SQL statement and assists operation and maintenance personnel in locating the optimization stage. The electronic device can be composed of two or more physical entities, or it can be composed of one physical entity. For example, the electronic device can be a personal computer (PC), a computer, a server, etc., and the embodiments of the present application do not impose specific restrictions on this.

[0147] like Figure 7 As shown, 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, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114; the memory 113 is used to store a computer program; the processor 111 is used to implement the steps of the slow SQL statement analysis method for a database provided by any of the above method embodiments when executing the program stored in the memory 113. Exemplarily, the steps of the slow SQL statement analysis method for a database may include the following steps: obtaining execution data corresponding to multiple slow SQL statements within a preset period, the execution data including stage data of at least one execution stage and a stage indicator corresponding to each of the execution stages; for each execution stage, performing an indicator analysis on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information; performing an aggregate analysis on all the indicator change information to obtain a query rate corresponding to the multiple slow SQL statements within the period; generating analysis data for assisting in analyzing a target stage based on the query rate and a preset benchmark query rate, wherein the target stage is a stage to be optimized in at least one of the execution stages.

[0148] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the method for analyzing slow SQL statements in a database provided in any of the aforementioned method embodiments are implemented.

[0149] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of other identical elements in the process, method, article, or device comprising the element.

[0150] The foregoing is merely a list of specific embodiments of the present application, intended to enable those skilled in the art to understand or implement the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application is not limited to the embodiments shown herein, but is intended to conform to the broadest scope consistent with the principles and novel features of the present application.

Claims

1. A method for analyzing slow SQL statements in a database, characterized in that: include: Monitor the execution phase of each slow SQL statement to obtain accumulated value information, where the accumulated value information includes the accumulated number and accumulated execution time of the slow SQL statements with the same number or the same pattern; Generate execution information of the slow SQL statement based on the accumulated number of entries and the accumulated execution time, generate an execution record table based on the execution information, and collect execution data of the slow SQL statement from the execution record table; Obtain execution data corresponding to multiple slow SQL statements within a preset period, wherein the execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage; For each execution stage, perform indicator analysis on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information, which includes the total time consumed by all slow SQL statements in the current execution stage; Aggregate and analyze all of the indicator change information to obtain query rates corresponding to multiple slow SQL statements within the period, including: counting the slow SQL statements to obtain the number of statements; performing aggregate analysis based on the indicator change information to obtain the total time consumed by the slow SQL statements; and analyzing based on the number of statements and the total time consumed to obtain the query rate; Based on the query rate and a preset benchmark query rate, analysis data for assisting in analyzing the target stage is generated, wherein the target stage is a stage to be optimized in at least one of the execution stages, and the benchmark query rate includes the query rate per second between all SQL statements and the total time consumed within a preset period.

2. The method according to claim 1, characterized in that The obtaining of execution data corresponding to multiple slow SQL statements within a preset period includes: Receive a data collection request corresponding to the slow SQL statement, where the data collection request carries a slow log collection task, and the slow log collection task includes a data collection frequency and a collection indicator corresponding to the slow SQL statement; Obtaining an execution record table, wherein the execution record table records execution information of the slow SQL statement; Based on the collection indicator, the execution data of the slow SQL statement is collected from the execution record table according to the data collection frequency.

3. The method according to claim 1, characterized in that For each execution stage, performing indicator analysis on the stage data of the execution stage based on the stage indicator corresponding to the execution stage to obtain corresponding indicator change information includes: extracting execution time from the stage data; For each of the execution stages, a trend analysis is performed on the execution time based on the stage indicators to obtain the indicator time consumption corresponding to each of the execution stages, and the indicator time consumption is used as the indicator change information.

4. The method according to claim 1, wherein Before obtaining the query rate, the method further includes: Determine the shard corresponding to the slow SQL statement, and obtain the number of executions based on the frequency statistics of the slow SQL statement; For the shard, perform dimensional analysis based on the number of statements, the number of executions, and the total time consumed to obtain change trend information corresponding to the shard; A graph is constructed based on the change trend information to obtain a dimensional trend graph of the slow SQL statement, and the graph is displayed based on the dimensional trend graph.

5. The method according to claim 1, wherein The generating of analysis data for assisting in analyzing the target phase based on the query rate and a preset benchmark query rate includes: Get the preset benchmark query rate; composing a graph based on the query rate and the benchmark query rate to obtain a stage change trend graph, and using the stage change trend graph as the analysis data, the stage change trend graph including a target stage and trend change data corresponding to the target stage; Key points are displayed based on the target stage and the trend change data.

6. A slow SQL statement analysis device for a database, characterized in that: include: A monitoring module is configured to monitor the execution phase of each slow SQL statement to obtain accumulated value information, wherein the accumulated value information includes the accumulated number and accumulated execution time of the slow SQL statements with the same number or the same pattern; a generating module, configured to generate execution information of the slow SQL statement based on the accumulated number of entries and the accumulated execution time, generate an execution record table based on the execution information, and collect execution data of the slow SQL statement from the execution record table; An execution data acquisition module is used to acquire execution data corresponding to multiple slow SQL statements within a preset period, wherein the execution data includes stage data of at least one execution stage and a stage indicator corresponding to each execution stage; An indicator parsing module is used to perform indicator parsing on the stage data of each execution stage based on the stage indicator corresponding to the execution stage, and obtain corresponding indicator change information, which includes the total time consumed by all slow SQL statements in the current execution stage; an aggregation analysis processing module, configured to perform aggregation analysis on all the indicator change information to obtain query rates corresponding to the plurality of slow SQL statements within the period, including: counting the slow SQL statements to obtain the number of statements; performing aggregation analysis based on the indicator change information to obtain the total time consumed by the slow SQL statements; and performing analysis based on the number of statements and the total time consumed to obtain the query rate; An analysis data generation module is used to generate analysis data for assisting in analyzing a target phase based on the query rate and a preset benchmark query rate, wherein the target phase is a phase to be optimized in at least one of the execution phases, and the benchmark query rate includes the query rate per second between all SQL statements and the total time consumed within a preset period.

7. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus; Memory for storing computer programs; The processor is configured to implement the steps of the method for analyzing slow SQL statements in a database according to any one of claims 1 to 5 when executing a program stored in a memory.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the steps of the method for analyzing slow SQL statements in a database according to any one of claims 1 to 5 are implemented.

Images