Slow query operation processing method and device

Abstract

The embodiment of the present invention provides a method and device for processing a slow query operation. The method includes: detecting whether a slow query switch is in an on state, and executing a slow query operation when the slow query switch is in an on state; determining whether it is a slow query according to the query duration of the slow query operation, and recording the operation determined to be a slow query into a slow query log; writing the slow query log into a message queue, and asynchronously writing the slow query log into a search engine through the message queue. Through the present invention, the slow query operation is executed after determining that the slow query switch is in an on state, and the log data of the slow query is asynchronously written into the search engine. Therefore, the problem that the slow query collection cannot be turned on as needed in the related technology, and the log data is directly persisted in the log file, which affects the positioning and analysis of the slow query, can be solved, so as to achieve the effect of ensuring the statistical efficiency of the slow query.

Description

Technical Field

[0001] The embodiments of the present invention relate to the field of database technology, and in particular, to a method and device for processing a slow query operation. Background Art

[0002] In the related art, whether a query operation is a slow query is determined by comparing the query operation duration with a preset query time threshold, and the query operation determined to be a slow query is stored as a slow query statement. This slow query statistics method has the following shortcomings: there is a lack of a logical switch for slow query collection. If the plug-in is always in the collection state, it will slow down the response speed of the production environment interface; the log data is directly persisted in the log file, which is not conducive to the location and analysis of slow queries; there is a lack of a slow query alarm function, and production environment problems cannot be perceived in real time; there is a lack of a slow query statistical line chart with timestamp as the dimension, which is not convenient for analyzing slow query scenarios according to specific businesses. Summary of the invention

[0003] The embodiments of the present invention provide a method and device for processing slow query operations, so as to at least solve the problem in the related art that slow query collection cannot be enabled on demand, and that log data is directly persisted in log files, which affects the location and analysis of slow queries.

[0004] According to an embodiment of the present invention, a method for processing a slow query operation is provided, comprising: detecting whether a slow query switch is in an on state, and executing a slow query operation when the slow query switch is in an on state; determining whether it is a slow query according to a query duration of the slow query operation, and recording the operation determined to be a slow query in a slow query log; writing the slow query log into a message queue, and asynchronously writing the slow query log to a search engine through the message queue.

[0005] In an exemplary embodiment, before detecting whether the switch of the slow query is turned on, the method further includes: setting the state of the switch, the execution frequency of the slow query operation, and a first threshold of the query duration.

[0006] In an exemplary embodiment, a leaky bucket algorithm is used to control the execution frequency of the set slow query operation.

[0007] In an exemplary embodiment, after determining that the slow query operation is a slow query, the method further includes: initiating an alarm when the frequency and / or quantity of the slow queries exceeds a second threshold.

[0008] In an exemplary embodiment, determining whether it is a slow query based on the query duration of the slow query operation includes: determining the query duration based on a first timestamp of starting to execute the slow query operation and a second timestamp of completing the slow query operation; when the query duration exceeds the first threshold, determining that the slow query operation is a slow query.

[0009] In an exemplary embodiment, after the slow query log is asynchronously written into the search engine, the method further includes: obtaining slow query retrieval information from the search engine, and visually presenting a slow query statistical line chart with time nodes as dimensions.

[0010] According to another embodiment of the present invention, a slow query operation device is provided, including: an execution module, used to detect whether a slow query switch is in an on state, and perform a slow query operation when the slow query switch is in the on state; a determination module, used to determine whether it is a slow query according to the query duration of the slow query operation, and record the operation determined to be a slow query in a slow query log; a writing module, used to write the slow query log into a message queue, and asynchronously write the slow query log to a search engine through the message queue.

[0011] In an exemplary embodiment, it further includes: a setting module, which is used to set the state of the switch, the execution frequency of the slow query operation and the first threshold of the query duration.

[0012] According to yet another embodiment of the present invention, a computer-readable storage medium is provided, in which a computer program is stored, wherein the computer program is configured to execute the steps of any one of the above method embodiments when run.

[0013] According to yet another embodiment of the present invention, there is provided an electronic device, including a memory and a processor, wherein the memory stores a computer program, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.

[0014] Through the present invention, the slow query operation is executed after determining that the switch of the slow query is in the on state, and the log data of the slow query is asynchronously written to the search engine. Therefore, it can solve the problem in the related art that the slow query collection cannot be turned on on demand, and the log data is directly persisted in the log file, which affects the slow query positioning and analysis, thereby achieving the effect of ensuring the statistical efficiency of the slow query. BRIEF DESCRIPTION OF THE DRAWINGS

[0015] Figure 1 It is a hardware structure block diagram of a computer terminal for executing a processing method for a slow query operation according to an embodiment of the present invention;

[0016] Figure 2 is a flowchart of a method for processing a slow query operation according to an embodiment of the present invention;

[0017] Figure 3 is a structural block diagram of a processing device for slow query operations according to an embodiment of the present invention;

[0018] Figure 4 is a structural block diagram of a processing device for slow query operations according to another embodiment of the present invention;

[0019] Figure 5 is a network architecture diagram of a method and device for processing a slow query operation according to an embodiment of the present invention;

[0020] Figure 6 is a flowchart of a method for processing a slow query operation according to yet another embodiment of the present invention;

[0021] Figure 7 This is a schematic diagram of the structure of the deployment and use of the Kafka cluster according to an embodiment of the present invention;

[0022] Figure 8 It is a schematic diagram of implementing the leaky bucket algorithm according to an embodiment of the present invention. DETAILED DESCRIPTION

[0023] Hereinafter, embodiments of the present invention will be described in detail with reference to the accompanying drawings and in combination with the embodiments.

[0024] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.

[0025] The method embodiments provided in the embodiments of the present application can be executed in a mobile terminal, a computer terminal or a similar computing device. Taking running on a computer terminal as an example, Figure 1 1 is a hardware structure block diagram of a computer terminal for executing a slow query operation processing method according to an embodiment of the present invention. Figure 1 As shown, the computer terminal may include one or more ( Figure 1 Only one is shown in the figure) a processor 102 (the processor 102 may include but is not limited to a processing device such as a microprocessor (Central Processing Unit, MCU) or a programmable logic device (Field Programmable Gate Array, FPGA)) and a memory 104 for storing data, wherein the above-mentioned computer terminal may also include a transmission device 106 and an input and output device 108 for communication functions. It can be understood by those skilled in the art that Figure 1 The structure shown is only for illustration and does not limit the structure of the above-mentioned computer terminal. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations shown.

[0026] The memory 104 can be used to store computer programs, for example, software programs and modules of application software, such as the computer program corresponding to the processing method of the slow query operation in the embodiment of the present invention. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, that is, the above method is implemented. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include a memory remotely arranged relative to the processor 102, and these remote memories can be connected to the computer terminal via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0027] The transmission device 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of a computer terminal. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, referred to as NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission device 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0028] Figure 2 is a flowchart of a method for processing a slow query operation according to an embodiment of the present invention. Figure 2 As shown, the process includes the following steps:

[0029] Step S202, detecting whether the switch of the slow query is in the on state, and performing the slow query operation when the switch of the slow query is in the on state;

[0030] Step S204, determining whether it is a slow query according to the query duration of the slow query operation, and recording the operation determined to be a slow query in a slow query log;

[0031] Step S206: write the slow query log into a message queue, and asynchronously write the slow query log into a search engine through the message queue.

[0032] Before step S202 of this embodiment, the method may further include: setting a state of the switch, an execution frequency of the slow query operation, and a first threshold of the query duration.

[0033] In this embodiment, a leaky bucket algorithm is used to control the execution frequency of the set slow query operation.

[0034] In step S204 of this embodiment, after determining that the slow query operation is a slow query, the method may further include: initiating an alarm when the frequency and / or number of the slow queries exceeds a second threshold.

[0035] In step S204 of this embodiment, it may also include: determining the query duration according to a first timestamp of starting to execute the slow query operation and a second timestamp of completing the slow query operation; when the query duration exceeds the first threshold, determining that the slow query operation is a slow query.

[0036] After step S206 of this embodiment, the method may further include: obtaining slow query retrieval information from the search engine, and visually presenting a slow query statistical line chart with time nodes as dimensions.

[0037] Through the above steps, the query operation can be enabled on demand by using the slow query switch function; the log data of the slow query is asynchronously written to the search engine to avoid the impact of the slow query location and analysis due to the log data being directly persisted in the log file. This solves the problem in the related technology that slow query collection cannot be enabled on demand, and the log data is directly persisted in the log file, which affects the slow query location and analysis, and improves the statistical efficiency of slow queries.

[0038] Through the description of the above implementation methods, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by means of software plus a necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention, in essence or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as a read-only memory / random access memory (ROM / RAM), a magnetic disk, or an optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present invention.

[0039] In this embodiment, a processing device for slow query operation is also provided, which is used to implement the above-mentioned embodiments and preferred implementation modes, and will not be repeated hereafter. As used below, the term "module" can implement a combination of software and / or hardware of a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.

[0040] Figure 3 is a structural block diagram of a processing device for slow query operations according to an embodiment of the present invention. Figure 3As shown, the device includes: an execution module 10, a determination module 20 and a writing module 30.

[0041] An execution module 10 is used to detect whether a slow query switch is in an on state, and to perform a slow query operation when the slow query switch is in an on state;

[0042] A determination module 20, configured to determine whether the slow query is a slow query according to the query duration of the slow query operation, and record the operation determined to be a slow query in a slow query log;

[0043] The writing module 30 is used to write the slow query log into a message queue, and asynchronously write the slow query log into a search engine through the message queue.

[0044] Figure 4 is a structural block diagram of a processing device for slow query operations according to another embodiment of the present invention. Figure 4 As shown, the device includes Figure 3 All modules shown, plus:

[0045] The setting module 40 is used to set the state of the switch, the execution frequency of the slow query operation and the first threshold of the query duration.

[0046] It should be noted that the above modules can be implemented by software or hardware. For the latter, it can be implemented in the following ways, but not limited to: the above modules are all located in the same processor; or the above modules are located in different processors in any combination.

[0047] In order to facilitate the understanding of the technical solution provided by the present invention, the following will be described in detail with reference to embodiments of specific scenarios.

[0048] The embodiment of the present invention can be run on Figure 5 In the network architecture shown in Figure 5 As shown in the figure, the network architecture includes: query time calculation and slow query log collection modules based on the mybatis framework plug-in mechanism; log persistence module based on elasticsearch search engine; visualization of query results through kibana; buffering and asynchronous submission mechanism implemented by kafka queue; application part, using the front-end and back-end separation mode to implement visualized line chart statistics and data statistics interface (third-party applications can call the interface to implement their own extended functions.

[0049] The mybatis framework supports users to extend or change the functions of the original framework by providing interceptors, that is, the plug-in mechanism in the mybatis framework.

[0050] Specifically, four levels of interception are provided:

[0051] 1. Executor (update, query, commit, rollback and other methods): Executor is the SQL executor.

[0052] 2. StatementHandler (prepare, parameterize, batch, update, query and other methods): StatementHandler is responsible for operating the Statement object to communicate with the database, calling ParameterHandler and ResultSetHandler to map parameters, and binding the results to entity classes.

[0053] 3. ParameterHandler (getParameterObject, setParameters methods): ParameterHandler is used to process parameters in SQL statements.

[0054] 4. ResultSetHandler (handleResultSets, handleOutputParameters and other methods): ResultSetHandler is used to process the result set mapping returned after SQL execution is completed.

[0055] The SQL query time statistics function is mainly implemented by intercepting the Executor level. The current timestamp will be recorded before entering the query, and the query duration will be calculated after the execution is completed.

[0056] Figure 6 FIG. 4 is a flowchart of a method for processing a slow query operation according to another embodiment of the present invention. Figure 6 As shown, the method comprises the following steps:

[0057] Step S601, start the slow query operation process.

[0058] Step S602, detecting whether the switch is turned on.

[0059] Specifically, detect whether the slow query switch is turned on.

[0060] Step S603, record the current timestamp of entering the query.

[0061] Step S604, executing query logic.

[0062] Step S605, record the timestamp of query completion.

[0063] Step S606, determining whether it is a slow query.

[0064] Specifically, whether the query is a slow query is determined based on the query duration determined by the timestamp of entering the query and the timestamp of completing the query.

[0065] Step S607, write the slow query log to Kafka.

[0066] Specifically, Figure 7 This is a schematic diagram of the structure of the Kafka cluster deployment and use, such as Figure 7 As shown in the figure, each application corresponds to its own topic, and the mybatis plug-in acts as a producer to write slow query logs to the kafka queue.

[0067] Step S608, asynchronously writing into Elasticsearch.

[0068] Specifically, Figure 7 As shown, the application consumes queue messages asynchronously through consumer groups and writes them to elasticsearch.

[0069] Step S609, ending the slow query operation process.

[0070] In this embodiment, a scheduled task can be started in a third-party service, and the corresponding slow query can be obtained through the data retrieval interface; when the frequency and number of slow queries exceed the threshold within the time window, an alarm will be initiated.

[0071] In this embodiment, a line graph of the number of slow queries with time nodes as the dimension can also be provided. Specifically, the front end uses frameworks such as vue and echart, and the back end uses the springboot framework combined with the elasticsearch java api to index elasticsearch.

[0072] In this embodiment, when the mybatis plug-in collects slow logs, if the query time is calculated for each request, the operation efficiency of normal business will be slowed down. Therefore, this embodiment adopts the leaky bucket algorithm to control the query time collection frequency. Figure 8 This is a schematic diagram of implementing the leaky bucket algorithm, such as Figure 8 As shown, the collection frequency can be flexibly configured to minimize the impact on normal business logic.

[0073] In this embodiment, the switch, time threshold, and collection frequency of slow query collection can be flexibly set through the business background management system. A data persistence plug-in interface is provided, and the collected slow query logs can be collected into the database, and the slow query logs can be imported into the log platform to facilitate the storage, indexing, and analysis of the logs. The system can query the logs regularly, analyze the time and frequency of slow queries, and then send the alarm information to the R&D personnel. And the log collection mechanism can be flexibly turned on and off to prevent the collection plug-in from slowing down the response speed of the production environment interface.

[0074] An embodiment of the present invention further provides a computer-readable storage medium, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above method embodiments when running.

[0075] In an exemplary embodiment, the computer-readable storage medium may include, but is not limited to, various media that can store computer programs, such as a USB flash drive, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk.

[0076] An embodiment of the present invention further provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.

[0077] In an exemplary embodiment, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.

[0078] Obviously, those skilled in the art should understand that the above modules or steps of the present invention can be implemented by a general computing device, they can be concentrated on a single computing device, or distributed on a network composed of multiple computing devices, they can be implemented by a program code executable by a computing device, so that they can be stored in a storage device and executed by the computing device, and in some cases, the steps shown or described can be executed in a different order than here, or they can be made into individual integrated circuit modules, or multiple modules or steps therein can be made into a single integrated circuit module for implementation. Thus, the present invention is not limited to any specific combination of hardware and software.

[0079] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for processing slow query operations, characterized in that: include: Detecting whether a slow query switch is in an on state, and executing a slow query operation when the slow query switch is in an on state; Determine whether it is a slow query according to the query duration of the slow query operation, and record the operation determined to be a slow query in a slow query log; Writing the slow query log into a message queue, and asynchronously writing the slow query log into a search engine through the message queue; Before detecting whether the switch of the slow query is turned on, the method further includes: setting the state of the switch, the execution frequency of the slow query operation, and a first threshold of the query duration; wherein the execution frequency of the set slow query operation is controlled by using a leaky bucket algorithm; After the slow query log is asynchronously written to the search engine, the method further includes: Slow query retrieval information is obtained from the search engine, and a slow query statistical line chart is visualized with time nodes as dimensions.

2. The method according to claim 1, characterized in that After determining that the slow query operation is a slow query, the method further includes: When the frequency and / or quantity of the slow queries exceeds a second threshold, an alarm is initiated.

3. The method according to claim 1, characterized in that Determining whether the slow query is a slow query according to the query duration of the slow query operation includes: Determine the query duration according to a first timestamp of starting to execute the slow query operation and a second timestamp of completing the slow query operation; When the query duration exceeds the first threshold, the slow query operation is determined to be a slow query.

4. A processing device for slow query operation, characterized in that: include: An execution module, used to detect whether the switch of the slow query is in an on state, and to execute a slow query operation when the switch of the slow query is in an on state; A determination module, used to determine whether it is a slow query according to the query duration of the slow query operation, and record the operation determined to be a slow query in a slow query log; A writing module, used to write the slow query log into a message queue, and asynchronously write the slow query log into a search engine through the message queue; A setting module, used to set the state of the switch, the execution frequency of the slow query operation and the first threshold of the query duration; wherein the leaky bucket algorithm is used to control the execution frequency of the set slow query operation; The processing device is also used to obtain slow query retrieval information from the search engine, and visually present a slow query statistical line chart with time nodes as dimensions.

5. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, wherein the computer program is configured to execute the method according to any one of claims 1 to 3 when executed.

6. An electronic device comprising a memory and a processor, characterized in that: A computer program is stored in the memory, and the processor is configured to run the computer program to perform the method according to any one of claims 1 to 3.

Images