Slow query associated data acquisition method and device, equipment and storage medium
By receiving user commands and utilizing execution plan retrieval functions and database table parsing components, the associated data of slow query statements is automatically obtained, solving the problem of low efficiency in manually searching for associated data in existing technologies and achieving more efficient analysis.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- SHENZHEN LEXIN SOFTWARE TECH CO LTD
- Filing Date
- 2023-09-27
- Publication Date
- 2026-07-24
Smart Images
Figure CN117349307B_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of data processing technology, and in particular to a method, apparatus, device and storage medium for obtaining related data for slow queries. Background Technology
[0002] Slow query statements are Structured Query Language (SQL) statements whose execution time exceeds a specified time. The system calculates the execution time of each SQL statement before and after command execution. When the execution time exceeds a preset threshold, the SQL statement is recorded as a slow query statement.
[0003] To identify the cause of slow queries, analysts need to analyze the generated slow query statements. In the process of analyzing slow query statements, analysts need to rely on the associated data related to the slow query statement, such as database table metadata and execution plans.
[0004] However, in the existing analysis process for slow query statements, after the analyst obtains the slow query statement, they need to manually search for related data of the slow query statement from other places, which results in low analysis efficiency. Summary of the Invention
[0005] This application provides a method, apparatus, device, and storage medium for obtaining associated data of slow queries, which can automatically obtain associated data corresponding to slow query statements, thereby improving the analysis efficiency of slow query statements.
[0006] In a first aspect, embodiments of this application provide a method for obtaining associated data of slow queries, which includes: receiving a first operation instruction from a user for a target fingerprint slow query statement in a first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface;
[0007] In response to the first operation instruction, the target execution plan corresponding to the target fingerprint slow query statement is obtained through a preset execution plan acquisition function, and the target fingerprint slow query statement is parsed through a preset database table parsing component to obtain the target database table keywords;
[0008] The target database table metadata corresponding to the target database table keyword is determined based on the preset correspondence between database table keywords and database table metadata.
[0009] The second display interface is displayed, which shows the target database table metadata and the target execution plan.
[0010] Secondly, embodiments of this application also provide a device for obtaining related data for slow queries, comprising:
[0011] The transceiver unit is used to receive a first operation instruction from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface.
[0012] The processing unit is configured to respond to the first operation instruction by obtaining the target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan acquisition function, and performing database table parsing processing on the target fingerprint slow query statement through a preset database table parsing component to obtain target database table keywords; determining the target database table metadata corresponding to the target database table keywords according to the preset correspondence between database table keywords and database table metadata; and displaying a second display interface, in which the target database table metadata and the target execution plan are displayed.
[0013] Thirdly, embodiments of this application also provide a computer device, which includes a memory and a processor, wherein the memory stores a computer program, and the processor executes the computer program to implement the above-described method.
[0014] Fourthly, embodiments of this application also provide a computer-readable storage medium storing a computer program, the computer program including program instructions that, when executed by a processor, can implement the above-described method.
[0015] This application provides a method, apparatus, device, and storage medium for acquiring associated data of slow queries. The method includes: receiving a first operation instruction from a user regarding a target fingerprint slow query statement displayed on a first display interface, the first display interface displaying at least one fingerprint slow query statement; responding to the first operation instruction, acquiring a target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan acquisition function, and performing database table parsing processing on the target fingerprint slow query statement through a preset database table parsing component to obtain target database table keywords; determining target database table metadata corresponding to the target database table keywords based on a preset correspondence between database table keywords and database table metadata; and displaying a second display interface, the second display interface displaying the target database table metadata and the target execution plan. In this application embodiment, when a user (analyst) needs to analyze a slow query statement, they can directly obtain the associated data (target database table metadata and target execution plan) corresponding to the slow query statement from the display interface by clicking on the target fingerprint slow query statement to be analyzed, without manually searching for the associated data of the slow query statement elsewhere, thus improving the efficiency of acquiring associated data of slow query statements and consequently improving the efficiency of analyzing slow query statements. Attached Figure Description
[0016] To more clearly illustrate the technical solutions of the embodiments of this application, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0017] Figure 1 A schematic diagram illustrating an application scenario for the method for obtaining associated data in slow queries provided in this application embodiment;
[0018] Figure 2 A flowchart illustrating the method for obtaining associated data for slow queries provided in this application embodiment;
[0019] Figure 3 A schematic diagram of a display interface for a method of obtaining associated data for slow queries provided in an embodiment of this application;
[0020] Figure 4 Another schematic diagram of the display interface for the method of obtaining associated data for slow queries provided in the embodiments of this application;
[0021] Figure 5 Another schematic diagram of the display interface for the method of obtaining associated data for slow queries provided in the embodiments of this application;
[0022] Figure 6 A schematic block diagram of a device for obtaining associated data for slow queries provided in an embodiment of this application;
[0023] Figure 7 A schematic block diagram of a computer device provided in an embodiment of this application. Detailed Implementation
[0024] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0025] It should be understood that, when used in this specification and the appended claims, the terms "comprising" and "including" indicate the presence of the described features, integrals, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.
[0026] It should also be understood that the terminology used in this specification is for the purpose of describing particular embodiments only and is not intended to limit the scope of the application. As used in this specification and the appended claims, the singular forms “a,” “an,” and “the” are intended to include the plural forms unless the context clearly indicates otherwise.
[0027] It should also be further understood that the term “and / or” as used in this application specification and the appended claims means any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.
[0028] This application provides a method, apparatus, device, and storage medium for obtaining associated data for slow queries.
[0029] The execution subject of the method for obtaining associated data of slow queries can be the associated data acquisition device for slow queries provided in the embodiments of this application, or a computer device that integrates the associated data acquisition device for slow queries. The associated data acquisition device for slow queries can be implemented in hardware or software, and the computer device can be a terminal or a server.
[0030] When the computer device is a server, the server is configured with a display platform (terminal). The server can display fingerprint slow query statements and related information through the display platform, and users can send commands to the server through the display platform.
[0031] When the computer device is a terminal, it directly obtains the user's input commands and displays the fingerprint query statement and related information.
[0032] The method for obtaining associated data of slow queries provided in this embodiment can more conveniently analyze slow query statements generated by MySQL, thereby improving analysis efficiency.
[0033] The following is an explanation of some terms mentioned in this embodiment:
[0034] Slow query statements: These are SQL statements in MySQL that take longer than a specified time to execute. The MySQL system calculates the execution time of each command before and after execution, and records the relevant information of the command when it exceeds a preset threshold.
[0035] SQL statements are a database query and programming language used to access, query, update, and manage relational database systems.
[0036] MySQL: A relational database management system. Relational databases store data in different tables instead of putting all the data in one large repository, which increases speed and improves flexibility.
[0037] Fingerprint slow query statement: refers to replacing variable parameters in an SQL statement with other fixed symbols (or performing descaling to eliminate variable parameters in the SQL statement), which can be used for SQL desensitization or SQL classification.
[0038] Slow query database: This can be ClickHouse (a data analysis database) where users store the fingerprints of slow queries they monitor, as well as the execution data of each slow query.
[0039] Slow query execution data includes at least one of the following: the number of times the fingerprint slow query statement was executed, the first execution time, the last execution time, the maximum execution time, the minimum execution time, the average execution time, the total execution time, the maximum number of scans, the minimum number of scans, the average number of scans, and the total number of scans.
[0040] Please see Figure 1 , Figure 1 This diagram illustrates an application scenario for the method of obtaining associated data for slow queries provided in this embodiment. The method for obtaining associated data for slow queries is applied to... Figure 1 In server 10, the server 10 receives a first operation command from a user regarding a target fingerprint slow query statement displayed on a first display interface via a display platform 20. The first display interface displays at least one fingerprint slow query statement. Then, in response to the first operation command, the server 10 obtains the target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan retrieval function, and performs database table parsing processing on the target fingerprint slow query statement using a preset database table parsing component to obtain target database table keywords. Based on a preset correspondence between database table keywords and database table metadata, the server determines the target database table metadata corresponding to the target database table keywords. Finally, the server displays a second display interface via the display platform 20, which displays the target database table metadata and the target execution plan. Furthermore, server 10 monitors MySQL 30 and updates the slow query database based on the monitored slow query statements.
[0041] The following describes in detail the method for obtaining associated data for slow queries provided in this application, with the server as the executing entity. This server can interact with users through a display platform (displaying data, obtaining user commands, etc.).
[0042] Figure 2 This is a flowchart illustrating the method for obtaining associated data for slow queries provided in an embodiment of this application. For example... Figure 2 As shown, the method includes the following steps S110-S150.
[0043] S110, Receive the user's first operation instruction for the target fingerprint slow query statement in the first display interface.
[0044] In this embodiment, the server displays a first display interface through a display platform. The first display interface displays at least one fingerprint slow query statement. When a user wants to analyze the target fingerprint slow query statement in the first display interface, the user can click on the target fingerprint slow query statement in the first display interface using a mouse or touch screen. At this time, the server receives the first operation command input by the user for the target fingerprint query statement through the display platform.
[0045] In some embodiments, before displaying the first display interface, the display platform displays a third display interface, which includes an entry point (slow query display control) to the first display interface. Users can enter the first display interface by clicking the slow query display control.
[0046] Specifically, the system receives a second operation command from the user for the slow query display control in the third display interface; then, in response to the second operation command, it generates interface information for the first display interface based on the slow query database, wherein the slow query database stores at least one of the fingerprint slow query statements and slow query execution data corresponding to each of the fingerprint slow query statements; and displays the first display interface based on the interface information.
[0047] When the executing entity is a server, after obtaining the interface information, it sends the interface information to the display platform, so that the display platform displays the first display interface according to the interface information.
[0048] In some embodiments, to make the first display interface more concise, the first display interface only displays fingerprint slow query statements in the slow query database. In other embodiments, to enable users to obtain more information about fingerprint slow query statements and to more specifically select the fingerprint slow query statements to be analyzed, the first display interface displays not only fingerprint slow query statements but also slow query execution data corresponding to each fingerprint slow query statement.
[0049] That is, a first display interface can be displayed according to the following steps: generating first interface information based on fingerprint slow query statements in the slow query database, the first interface information instructing the first display interface to display at least one fingerprint slow query statement; or, generating second interface information based on fingerprint slow query statements in the slow query database and slow query execution data, the second interface information instructing the first display interface to display at least one fingerprint slow query statement and slow query execution data corresponding to each of the fingerprint slow query statements (e.g., ...). Figure 3 (As shown).
[0050] In some embodiments, since there are a large number of fingerprint slow query statements displayed on the first display interface, the displayed fingerprint slow query statements can be further filtered by the execution time, execution node and / or execution user corresponding to the fingerprint slow query statement. At this time, the slow query execution data further includes the execution user and execution node.
[0051] In this embodiment, slow query statements are collected in real time through the following steps: Monitoring whether MySQL generates slow query statements using a preset slow query monitoring component (pmm-slowlog-agent); if the slow query statement is generated, obtaining the sub-slow query execution data corresponding to the slow query statement; determining whether the slow query statement exists in the slow query database; if the slow query statement does not exist in the slow query database, using the sub-slow query execution data as the slow query execution data corresponding to the slow query statement, and storing the fingerprint slow query statement and its corresponding slow query execution data in the slow query database; if the slow query statement exists in the slow query database, updating the slow query execution data corresponding to the slow query statement in the slow query database based on the sub-slow query execution data.
[0052] Specifically, the slow query monitoring component can determine whether MySQL is generating slow query statements by monitoring SQL files in real time. When a MySQL instance generates a slow query, the corresponding slow query statement is recorded in the MySQL instance.
[0053] To avoid repeatedly collecting slow query statements, this embodiment first determines whether the slow query statement has already been collected after it is obtained. Only when it is determined that the slow query statement does not exist in the slow query database will the slow query statement be collected. At the same time, the corresponding sub-slow query execution data is recorded. The sub-slow query execution data includes at least one of execution time, execution duration, and number of scans. The number of executions of the sub-slow query execution data recorded in the slow query database is 1. The first execution time and the last execution time are both execution times of the fingerprint slow query statement. The maximum execution duration, minimum execution duration, average execution duration, and total execution duration are all execution durations of the fingerprint slow query statement. The maximum number of scans, minimum number of scans, average number of scans, and total number of scans are all the number of scans of the fingerprint slow query statement.
[0054] When it is determined that the slow query statement exists in the slow query database, the slow query statement is stored in the slow query database through aggregation. The slow query execution data corresponding to the slow query statement in the slow query database is updated according to the sub-slow query execution data. For example, the first execution time, last execution time, maximum execution time, minimum execution time, average execution time, total execution time, maximum number of scans, minimum number of scans, average number of scans, and total number of scans corresponding to the slow query statement in the slow query execution data are updated according to the execution time, execution duration, and number of scans in the sub-slow query execution data, and the execution count is incremented by 1.
[0055] In some embodiments, an identifier set is introduced to check whether a corresponding slow query statement exists in the slow query database. In this case, before determining whether the slow query statement exists in the slow query database, the method further includes: generating a fingerprint slow query statement corresponding to the slow query statement; performing hash processing on the fingerprint slow query statement corresponding to the slow query statement to obtain a target slow query identifier corresponding to the fingerprint slow query statement; and storing the target slow query identifier in a preset identifier set, wherein the identifier set stores the slow query identifiers corresponding to each fingerprint slow query statement in the slow query database.
[0056] At this time, the presence of the slow query statement in the slow query database is determined by the following method: determining whether the target slow query identifier is present in the identifier set; if the target slow query identifier is present in the identifier set, then the slow query statement is present in the slow query database; if the target slow query identifier is not present in the identifier set, then the slow query statement is not present in the slow query database.
[0057] In some embodiments, the fingerprint slow query statement corresponding to the slow query statement can be generated in either of the following two ways: replacing the variable parameters in the slow query statement with preset uniform characters to obtain the fingerprint slow query statement; or, performing a descaling process on the slow query statement (i.e. removing the variable parameters in the slow query statement) to obtain the fingerprint slow query statement.
[0058] S120. In response to the first operation instruction, obtain the target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan acquisition function.
[0059] In this embodiment, after the server obtains the first operation instruction through the display platform, it will respond to the first operation instruction by automatically obtaining the associated data of the target fingerprint slow query statement specified by the first operation instruction. The associated data includes target database table metadata and target execution plan. The target database table metadata includes database metadata and table metadata.
[0060] The execution plan is an important basis for SQL statement tuning, as it shows how MySQL processes SQL statements.
[0061] After receiving the first operation command, the server automatically triggers the preset execution plan retrieval function to obtain the corresponding execution plan by accessing MySQL. This execution plan retrieval function can be the MySQL explain statement or other functions that can obtain the execution plan. The specific function type is not limited here.
[0062] S130. In response to the first operation instruction, the target fingerprint slow query statement is parsed using a preset database table parsing component to obtain the target database table keywords.
[0063] In this embodiment, after the server obtains the first operation instruction through the display platform, when determining the target database table metadata, it first performs database table parsing processing on the target fingerprint slow query statement through a preset database table parsing component (such as the SQL parsing tool of tidb-parser) to obtain the target database table keywords.
[0064] Specifically, once the server identifies the target fingerprint slow query statement, it passes the target fingerprint slow query statement to the database table parsing component for database table parsing. After parsing, it returns the extracted target database table keywords, which include database keywords and table keywords. The database keyword can be the database name (db_name), and the table keyword can be a list of table names (table_names).
[0065] S140. Determine the target database table metadata corresponding to the target database table keyword based on the preset correspondence between database table keywords and database table metadata.
[0066] In this embodiment, after the database table keyword is determined, the target database table metadata corresponding to the target database table keyword will be determined according to the preset correspondence between the database table keyword and the database table metadata.
[0067] The correspondence between database and table keywords and database and table metadata includes the correspondence between database keywords and database metadata, as well as the correspondence between table keywords and table metadata.
[0068] S150. Display a second display interface, which displays the target database table metadata and the target execution plan.
[0069] In this embodiment, after the target execution plan and database table metadata corresponding to the target fingerprint slow query statement are found, the current display page will be switched from the first display page to the second display interface, which displays the target database table metadata and the target execution plan.
[0070] In some embodiments, such as Figure 4 As shown, in order to better distinguish between the target database table metadata and the target execution plan, the entry points for the target database table metadata and the target execution plan can be displayed on the second display interface respectively. Clicking the target database table metadata entry point will display the target database table metadata, and clicking the target execution plan entry point will display the target execution plan.
[0071] When the execution subject of this embodiment is a server, this embodiment needs to display a second display interface through a display platform.
[0072] In some embodiments, in order to further improve the analysis efficiency of target fingerprint slow query statements, the second display interface also displays detailed indicators of target fingerprint slow query statements, which include target fingerprint slow query statements and corresponding slow query execution data.
[0073] like Figure 5 As shown, the second display interface can display detailed indicators, target database table metadata, and the target execution plan entry points respectively. Clicking the detailed indicator entry point will display the corresponding detailed indicators, clicking the target database table metadata entry point will display the target database table metadata, and clicking the target execution plan entry point will display the target execution plan.
[0074] In summary, in this embodiment, as long as the user clicks on the target fingerprint slow query statement to be analyzed in the display interface, the server provided in this embodiment can automatically determine the target database table metadata and target execution plan corresponding to the target fingerprint slow query statement, and automatically display the target database table metadata and target execution plan on the display interface of the display platform. Therefore, through this embodiment, when the user needs to analyze the slow query statement, they can directly obtain the associated data (target database table metadata and target execution plan) corresponding to the slow query statement from the display interface, without having to manually search for the associated data of the slow query statement from other places, which improves the efficiency of obtaining the associated data of the slow query statement, and thus improves the efficiency of analyzing the slow query statement.
[0075] Figure 6 This is a schematic block diagram of a device for obtaining associated data for slow queries, provided in an embodiment of this application. Figure 6As shown, corresponding to the above-described method for obtaining associated data for slow queries, this application also provides a device 600 for obtaining associated data for slow queries. This device 600 includes a unit for executing the above-described method for obtaining associated data for slow queries, and can be configured in a terminal or server. Specifically, please refer to... Figure 6 The associated data acquisition device 600 for slow queries includes a transceiver unit 601 and a processing unit 602.
[0076] The transceiver unit 601 is used to receive a first operation instruction from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface.
[0077] The processing unit 602 is configured to respond to the first operation instruction by obtaining the target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan acquisition function, and performing database table parsing processing on the target fingerprint slow query statement through a preset database table parsing component to obtain target database table keywords; determining the target database table metadata corresponding to the target database table keywords according to the preset correspondence between database table keywords and database table metadata; and displaying a second display interface, in which the target database table metadata and the target execution plan are displayed.
[0078] In some embodiments, before the transceiver unit 601 executes the first operation instruction step of receiving the user's slow query statement for the target fingerprint in the first display interface, the processing unit 602 is further configured to:
[0079] The transceiver unit 601 receives a second operation command from the user for the slow query display control in the third display interface; in response to the second operation command, it generates interface information of the first display interface according to the slow query database, wherein the slow query database stores at least one of the fingerprint slow query statements and slow query execution data corresponding to each of the fingerprint slow query statements; and displays the first display interface according to the interface information.
[0080] In some embodiments, when the processing unit 602 executes the step of generating the interface information of the first display interface based on the slow query database, it is specifically used for:
[0081] A first interface information is generated based on the fingerprint slow query statements in the slow query database, and the first interface information indicates that the first display interface displays at least one fingerprint slow query statement; or, a second interface information is generated based on the fingerprint slow query statements in the slow query database and the slow query execution data, and the second interface information indicates that the first display interface displays at least one fingerprint slow query statement and the slow query execution data corresponding to each fingerprint slow query statement.
[0082] In some embodiments, the processing unit 602 is further configured to: monitor whether MySQL generates a slow query statement through a preset slow query monitoring component; if the slow query statement is generated, obtain the sub-slow query execution data corresponding to the slow query statement; determine whether the slow query statement exists in the slow query database; if the slow query statement does not exist in the slow query database, use the sub-slow query execution data as the slow query execution data corresponding to the slow query statement, and store the fingerprint slow query statement and the corresponding slow query execution data in the slow query database; if the slow query statement exists in the slow query database, update the slow query execution data corresponding to the slow query statement in the slow query database according to the sub-slow query execution data.
[0083] In some embodiments, before performing the step of determining whether the slow query statement exists in the slow query database, the processing unit 602 is further configured to:
[0084] Generate a fingerprint slow query statement corresponding to the slow query statement; perform hash processing on the fingerprint slow query statement corresponding to the slow query statement to obtain the target slow query identifier corresponding to the fingerprint slow query statement; store the target slow query identifier in a preset identifier set, the identifier set storing the slow query identifiers corresponding to each fingerprint slow query statement in the slow query database.
[0085] At this time, when the processing unit 602 performs the step of determining whether the slow query statement exists in the slow query database, it is specifically used for:
[0086] Determine whether the target slow query identifier exists in the identifier set; if the target slow query identifier exists in the identifier set, then determine that the slow query statement exists in the slow query database; if the target slow query identifier does not exist in the identifier set, then determine that the slow query statement does not exist in the slow query database.
[0087] In some embodiments, when the processing unit 602 performs the step of generating the fingerprint slow query statement corresponding to the slow query statement, it is specifically used for:
[0088] Replace the variable parameters in the slow query statement with preset uniform characters to obtain the fingerprint slow query statement corresponding to the slow query statement; or, perform a de-draining process on the slow query statement to obtain the fingerprint slow query statement corresponding to the slow query statement.
[0089] In some embodiments, the slow query execution data includes at least one of the following: number of executions, first execution time, last execution time, maximum execution duration, minimum execution duration, average execution duration, total execution duration, maximum number of scans, minimum number of scans, average number of scans, and total number of scans.
[0090] In summary, in this embodiment, as long as the user clicks on the target fingerprint slow query statement to be analyzed in the display interface, the slow query related data acquisition device 600 can automatically determine the target database table metadata and target execution plan corresponding to the target fingerprint slow query statement, and automatically display the target database table metadata and target execution plan on the display interface of the display platform. Therefore, through this embodiment, when the user needs to analyze the slow query statement, they can directly obtain the related data (target database table metadata and target execution plan) corresponding to the slow query statement from the display interface, without having to manually search for the related data of the slow query statement from other places, which improves the efficiency of obtaining the related data of the slow query statement, and thus improves the efficiency of analyzing the slow query statement.
[0091] It should be noted that those skilled in the art can clearly understand that the specific implementation process of the above-mentioned slow query related data acquisition device and each unit can be referred to the corresponding description in the foregoing method embodiments. For the sake of convenience and brevity, it will not be repeated here.
[0092] The aforementioned device for retrieving associated data for slow queries can be implemented as a computer program, which can, for example... Figure 7 It runs on the computer device shown.
[0093] Please see Figure 7 , Figure 7 This is a schematic block diagram of a computer device provided in an embodiment of this application. The computer device 700 can be a terminal or a server.
[0094] See Figure 7 The computer device 700 includes a processor 702, a memory, and a network interface 705 connected via a system bus 701. The memory may include a non-volatile storage medium 703 and internal memory 704.
[0095] The non-volatile storage medium 703 may store an operating system 7031 and a computer program 7032. The computer program 7032 includes program instructions that, when executed, cause the processor 702 to perform a slow-query, correlated data retrieval method.
[0096] The processor 702 provides computing and control capabilities to support the operation of the entire computer device 700.
[0097] The internal memory 704 provides an environment for the execution of the computer program 7032 in the non-volatile storage medium 703. When the computer program 7032 is executed by the processor 702, the processor 702 can execute a slow query related data acquisition method.
[0098] This network interface 705 is used for network communication with other devices. Those skilled in the art will understand that... Figure 7 The structure shown is merely a block diagram of a portion of the structure related to the present application and does not constitute a limitation on the computer device 700 to which the present application is applied. The specific computer device 700 may include more or fewer components than those shown in the figure, or combine certain components, or have different component arrangements.
[0099] The processor 702 is used to run a computer program 7032 stored in the memory to perform the following steps:
[0100] Receive a first operation command from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface;
[0101] In response to the first operation instruction, the target execution plan corresponding to the target fingerprint slow query statement is obtained through a preset execution plan acquisition function, and the target fingerprint slow query statement is parsed through a preset database table parsing component to obtain the target database table keywords;
[0102] The target database table metadata corresponding to the target database table keyword is determined based on the preset correspondence between database table keywords and database table metadata.
[0103] The second display interface is displayed, which shows the target database table metadata and the target execution plan.
[0104] It should be understood that in the embodiments of this application, the processor 702 may be a central processing unit (CPU), or it may be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or any conventional processor.
[0105] It will be understood by those skilled in the art that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware. The computer program includes program instructions and can be stored in a storage medium, which is a computer-readable storage medium. The program instructions are executed by at least one processor in the computer system to implement the process steps of the embodiments of the above methods.
[0106] Therefore, this application also provides a storage medium. This storage medium can be a computer-readable storage medium. The storage medium stores a computer program, wherein the computer program includes program instructions. When executed by a processor, the program instructions cause the processor to perform the following steps:
[0107] Receive a first operation command from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface;
[0108] In response to the first operation instruction, the target execution plan corresponding to the target fingerprint slow query statement is obtained through a preset execution plan acquisition function, and the target fingerprint slow query statement is parsed through a preset database table parsing component to obtain the target database table keywords;
[0109] The target database table metadata corresponding to the target database table keyword is determined based on the preset correspondence between database table keywords and database table metadata.
[0110] The second display interface is displayed, which shows the target database table metadata and the target execution plan.
[0111] The storage medium can be any computer-readable storage medium capable of storing program code, such as a USB flash drive, portable hard drive, read-only memory (ROM), magnetic disk, or optical disk.
[0112] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this application.
[0113] In the several embodiments provided in this application, it should be understood that the disclosed apparatus and methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative. For example, the division of each unit is merely a logical functional division, and there may be other division methods in actual implementation. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed.
[0114] The steps in the methods of this application embodiment can be adjusted, merged, or deleted according to actual needs. The units in the apparatus of this application embodiment can be merged, divided, or deleted according to actual needs. Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit.
[0115] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, a terminal, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application.
[0116] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in this application, and these modifications or substitutions should all be covered within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.
Claims
1. A method for obtaining related data in slow queries, characterized in that, include: Receive a first operation command from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface; In response to the first operation instruction, the target execution plan corresponding to the target fingerprint slow query statement is obtained through a preset execution plan acquisition function, and the target fingerprint slow query statement is parsed through a preset database table parsing component to obtain the target database table keywords; The target database table metadata corresponding to the target database table keyword is determined based on the preset correspondence between database table keywords and database table metadata. A second display interface is displayed, which shows the target database table metadata and the target execution plan; The method further includes: The system uses a pre-defined slow query monitoring component to monitor whether MySQL generates slow query statements. If the slow query statement is generated, the execution data of the sub-slow query corresponding to the slow query statement is obtained; Determine whether the slow query statement exists in the slow query database; If the slow query statement is not stored in the slow query database, the sub-slow query execution data is used as the slow query execution data corresponding to the slow query statement, and the fingerprint slow query statement and the corresponding slow query execution data of the slow query statement are stored in the slow query database. If the slow query statement exists in the slow query database, then the slow query execution data corresponding to the slow query statement in the slow query database is updated according to the sub-slow query execution data.
2. The method according to claim 1, characterized in that, Before receiving the user's first operation instruction for a slow fingerprint query statement in the first display interface, the method further includes: Receive the user's second operation command for the slow query display control in the third display interface; In response to the second operation instruction, the interface information of the first display interface is generated according to the slow query database. The slow query database stores at least one of the fingerprint slow query statements and slow query execution data corresponding to each of the fingerprint slow query statements. The first display interface is displayed based on the interface information.
3. The method according to claim 2, characterized in that, The step of generating the interface information for the first display interface based on the slow query database includes: First interface information is generated based on the fingerprint slow query statements in the slow query database, and the first interface information indicates that the first display interface displays at least one fingerprint slow query statement; or... The second interface information is generated based on the fingerprint slow query statements and slow query execution data in the slow query database. The second interface information instructs the first display interface to display at least one fingerprint slow query statement and the slow query execution data corresponding to each fingerprint slow query statement.
4. The method according to claim 1, characterized in that, Before determining whether the slow query statement exists in the slow query database, the method further includes: Generate the fingerprint slow query statement corresponding to the slow query statement; Hash the fingerprint slow query statement corresponding to the slow query statement to obtain the target slow query identifier corresponding to the fingerprint slow query statement; The target slow query identifier is stored in a preset identifier set, which stores the slow query identifiers corresponding to each fingerprint slow query statement in the slow query database. Determining whether the slow query statement exists in the slow query database includes: Determine whether the target slow query identifier exists in the identifier set; If the target slow query identifier is stored in the identifier set, then it is determined that the slow query statement is stored in the slow query database; If the target slow query identifier is not present in the identifier set, then it is determined that the slow query statement is not present in the slow query database.
5. The method according to claim 4, characterized in that, The step of generating the fingerprint slow query statement corresponding to the slow query statement includes: Replace the variable parameters in the slow query statement with preset uniform characters to obtain the fingerprint slow query statement corresponding to the slow query statement; or, The slow query statement is subjected to a watering-out process to obtain the fingerprint slow query statement corresponding to the slow query statement.
6. The method according to any one of claims 2 to 5, characterized in that, The slow query execution data includes at least one of the following: execution count, first execution time, last execution time, maximum execution duration, minimum execution duration, average execution duration, total execution duration, maximum number of scans, minimum number of scans, average number of scans, and total number of scans.
7. A device for acquiring related data for slow queries, characterized in that, include: The transceiver unit is used to receive a first operation instruction from the user for a target fingerprint slow query statement in the first display interface, wherein at least one fingerprint slow query statement is displayed in the first display interface. The processing unit is configured to respond to the first operation instruction, obtain the target execution plan corresponding to the target fingerprint slow query statement through a preset execution plan acquisition function, and perform database table parsing processing on the target fingerprint slow query statement through a preset database table parsing component to obtain the target database table keywords; The target database table metadata corresponding to the target database table keyword is determined based on the preset correspondence between database table keywords and database table metadata. A second display interface is displayed, which shows the target database table metadata and the target execution plan; The processing unit is also used to monitor whether MySQL generates slow query statements through a preset slow query monitoring component; if the slow query statement is generated, the sub-slow query execution data corresponding to the slow query statement is obtained. Determine whether the slow query statement exists in the slow query database; If the slow query statement is not stored in the slow query database, the sub-slow query execution data is used as the slow query execution data corresponding to the slow query statement, and the fingerprint slow query statement and the corresponding slow query execution data of the slow query statement are stored in the slow query database. If the slow query statement exists in the slow query database, then the slow query execution data corresponding to the slow query statement in the slow query database is updated according to the sub-slow query execution data.
8. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method for obtaining associated data for slow queries as described in any one of claims 1-6.
9. A storage medium, characterized in that, The storage medium stores a computer program, which includes program instructions that, when executed by a processor, cause the processor to perform the method for retrieving associated data for slow queries as described in any one of claims 1-6.