CN113468218A

CN113468218A - Method and device for monitoring and managing database slow SQL

Info

Publication number: CN113468218A
Application number: CN202110738747.7A
Authority: CN
Inventors: 申冠豪; 李卓
Original assignee: CCB Finetech Co Ltd
Current assignee: CCB Finetech Co Ltd
Priority date: 2021-06-30
Filing date: 2021-06-30
Publication date: 2021-10-01

Abstract

The invention discloses a method and a device for monitoring and managing database slow SQL, and relates to the field of automatic program design. One embodiment of the method comprises: parsing the SQL body from the SQL request of the first user; encrypting the SQL main body to obtain an identification encryption string, and judging whether the identification encryption string exists in an illegal slow SQL base database; if yes, intercepting the SQL request, and refusing to send the SQL request to a database; otherwise, the SQL request is sent to a database. The SQL main body is analyzed and encrypted, slow SQL sentences are effectively intercepted, manual investigation is replaced by an automatic mode, a set of forced optimization mechanism aiming at the slow SQL sentences is established, risk monitoring is moved forward, and measurement and interception can be carried out in development and test environments.

Description

Method and device for monitoring and managing database slow SQL

Technical Field

The invention relates to the technical field of automatic program design, in particular to a method and a device for monitoring and managing slow SQL (structured query language) of a database

Background

With the rapid development of the internet, the performance requirements of the software system are increasingly strict, and the performance of the software system is greatly reduced due to the overlong execution time of the slow SQL statement. There are many factors that cause the execution time of the SQL statement to be too long, including the writing problems of hardware, locks, SQL statement, etc., but the writing problem of the SQL statement is the most common.

At present, a database access management method for effectively intercepting slow SQL sentences does not exist, the slow SQL sentences cannot be quickly optimized after being effectively screened, the slow SQL sentences can be monitored as early as possible in a development or test environment and effectively intercepted, and the problem that the slow SQL sentences are brought to a production environment and even avoided is urgently needed to be solved.

Disclosure of Invention

In view of this, embodiments of the present invention provide a method and an apparatus for monitoring and managing slow SQL of a database, where an SQL body is analyzed and encrypted to effectively intercept slow SQL statements, an automated approach is used to replace manual troubleshooting, and a set of forced optimization mechanisms for slow SQL statements is established to advance risk monitoring and enable measurement and interception in development and test environments.

To achieve the above object, according to a first aspect of the embodiments of the present invention, a method for monitoring and managing slow SQL of a database is provided.

The method for monitoring and managing the slow SQL of the database comprises the following steps:

parsing the SQL body from the SQL request of the first user; encrypting the SQL main body to obtain an identification encryption string, and judging whether the identification encryption string exists in an illegal slow SQL base database; if yes, intercepting the SQL request, and refusing to send the SQL request to a database; otherwise, the SQL request is sent to a database.

Optionally, before parsing the SQL body from the SQL request of the first user, the method further includes: the method comprises the steps of collecting original data from a query log of a database, capturing slow SQL sentences with execution time exceeding a preset value, analyzing SQL main bodies from the slow SQL sentences, encrypting the SQL main bodies analyzed from the slow SQL sentences to obtain historical identification encryption strings, and storing the historical identification encryption strings in an illegal slow SQL base database.

Optionally, after intercepting the SQL request, the method further includes: and sending the SQL request to a second user to prompt the second user that the current SQL request is too long in execution time and needs to be optimized.

Optionally, after intercepting the SQL request, the method further includes: and sending alarm information to the second user in one or more modes of telephone, short message and mail to prompt the second user that the current SQL request execution time is too long and needs to be optimized.

Optionally, after prompting that the execution time of the current SQL request is too long for the second user to be optimized, the method further includes: and responding to an optimization request sent by a second user, and sending the SQL request to a third user to prompt the third user that the current SQL request is too long in execution time and needs to be optimized.

Optionally, after sending the SQL request to the third user, the method further includes: and receiving an optimized SQL request sent by a third user, and sending the optimized SQL request to a second user so as to prompt the second user to check the optimized SQL request.

Optionally, the method further comprises: after intercepting the SQL request, storing the link of the SQL main body to an internal cache; after the optimized SQL request is sent to a second user, responding to an optimization confirmation request sent by the second user, deleting a link of the SQL body from an internal cache, and sending the optimized SQL request to a database.

Optionally, after responding to the optimization confirmation request sent by the second user, the method further includes: and counting the quantity of SQL requests received by the third user, the quantity of SQL requests which are optimized and passed through the examination and the quantity of SQL requests which are not optimized or not passed through the optimization.

Optionally, after prompting that the execution time of the current SQL request is too long for the second user to be optimized, the method further includes: and displaying the label information of the SQL request to a second user, wherein the label information comprises: the system comprises an SQL main body, an identification encryption string corresponding to the SQL main body, a data connection user name, execution duration and statistical time; the data connection user name is the user name of the user sending the SQL request.

Optionally, the method further comprises: and (4) acquiring original data from the query log of the database regularly, and updating the illegal slow SQL base database.

Optionally, the database comprises: one or more of a mysql database, an oracle database, and a sqlserver database.

Optionally, encrypting the SQL body to obtain an identification encryption string includes: and encrypting the SQL body by adopting one or more of MD5, RSA, DES and SHA as an encryption mode.

To achieve the above object, according to a second aspect of the embodiments of the present invention, an apparatus for monitoring and managing database slow SQL is provided.

The device for monitoring and managing the slow SQL of the database comprises the following components: the analysis module is used for analyzing the SQL body from the SQL request of the first user; the encryption processing module is used for encrypting the SQL main body to obtain an identification encryption string and judging whether the identification encryption string exists in an illegal slow SQL base database or not; if yes, intercepting the SQL request, and refusing to send the SQL request to a database; otherwise, the SQL request is sent to a database.

Optionally, the parsing module is further configured to, before parsing the SQL body from the SQL request of the first user, acquire original data from a query log of the database, capture a slow SQL statement with an execution time exceeding a preset value, parse the SQL body from the slow SQL statement, encrypt the SQL body parsed from the slow SQL statement, obtain a history identifier encryption string, and store the history identifier encryption string in an illegal slow SQL base database.

Optionally, the apparatus for monitoring and managing slow SQL of the database further includes a sending module, configured to send the SQL request to the second user after intercepting the SQL request, so as to prompt the second user that the execution time of the current SQL request is too long and needs to be optimized.

Optionally, the sending module is further configured to send an alarm message to the second user in one or more of a telephone, a short message, and a mail, so as to prompt the second user that the execution time of the current SQL request is too long and needs to be optimized.

Optionally, the sending module is further configured to, after prompting that the current SQL request execution time of the second user is too long and needs to be optimized, respond to the optimization request sent by the second user, send the SQL request to a third user, so as to prompt the third user that the current SQL request execution time is too long and needs to be optimized.

Optionally, the sending module is further configured to receive the optimized SQL request sent by the third user after sending the SQL request to the third user, and send the optimized SQL request to the second user, so as to prompt the second user to check the optimized SQL request.

Optionally, the apparatus for monitoring and managing database slow SQL further includes a sending cache processing module, configured to store the link of the SQL body to an internal cache after intercepting the SQL request;

after the optimized SQL request is sent to a second user, responding to an optimization confirmation request sent by the second user, deleting a link of the SQL body from an internal cache, and sending the optimized SQL request to a database.

Optionally, the cache processing module is further configured to count the number of SQL requests received by a third user, the number of SQL requests that have been optimized and passed through the review, and the number of SQL requests that have not been optimized or failed through the optimization.

Optionally, the apparatus for monitoring and managing slow SQL of the database further includes a display module, configured to display, to the second user, tag information of the SQL request after prompting that the execution time of the current SQL request of the second user is too long and needs to be optimized, where the tag information includes: the system comprises an SQL main body, an identification encryption string corresponding to the SQL main body, a data connection user name, execution duration and statistical time; the data connection user name is the user name of the user sending the SQL request.

Optionally, the parsing module is further configured to periodically collect original data from a query log of the database, and update the illegal slow SQL base database.

To achieve the above object, according to a third aspect of the embodiments of the present invention, an apparatus for monitoring and managing slow SQL of a database is provided.

The device for monitoring and managing the slow SQL of the database comprises: one or more processors; a storage system for storing one or more programs; when the one or more programs are executed by the one or more processors, the one or more processors implement the method for monitoring and managing the database slow SQL according to the embodiment of the invention.

To achieve the above object, according to a fourth aspect of embodiments of the present invention, there is provided a computer-readable medium.

The computer readable medium of the embodiments of the present invention stores thereon a computer program, which when executed by a processor implements the method of monitoring and managing database slow SQL of the embodiments of the present invention.

One embodiment of the above invention has the following advantages or benefits: in the embodiment of the invention, the SQL body is analyzed and encrypted, the slow SQL statement is effectively intercepted, and manual investigation is replaced by automation, so that the cost is reduced to a certain extent. A set of forced optimization mechanism for slow SQL sentences is established, risk monitoring is moved forward, and measurement and interception can be performed in the development and test links. Through counting the occurrence times of slow SQL sentences, the processing quantity and the multi-dimensional consideration on the processing timeliness, a set of consideration standards is provided for the performance assessment of research and development teams.

Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.

Drawings

The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:

FIG. 1 is a schematic diagram of the main flow of a method for monitoring and managing database slow SQL according to the embodiment of the invention;

FIG. 2 is a schematic diagram of a main flow of obtaining an illegal slow SQL base database according to an embodiment of the present invention;

FIG. 3 is a schematic diagram of the main flow after an SQL request is denied to a database according to an embodiment of the invention;

FIG. 4 is a schematic diagram of tag information of an SQL body presented to a second user according to an embodiment of the present invention;

FIG. 5 is a schematic diagram of the main flow of a method and apparatus for monitoring and managing database slow SQL according to the embodiment of the present invention;

FIG. 6 is a schematic diagram of the main modules of an apparatus for monitoring and managing database slow SQL according to the embodiment of the invention;

FIG. 7 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;

fig. 8 is a schematic structural diagram of a computer system suitable for implementing a terminal device or a server according to an embodiment of the present invention.

Detailed Description

Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.

According to a first aspect of the embodiments of the present invention, a method for monitoring and managing slow SQL of a database is provided.

Fig. 1 is a schematic diagram of a main flow of a method for monitoring and managing slow SQL of a database according to an embodiment of the present invention. As shown in fig. 1, the method mainly includes:

step S101: parsing the SQL body from the SQL request of the first user; step S102: encrypting the SQL body to obtain an identification encryption string, and step S103: judging whether the identification encryption string exists in an illegal slow SQL base database or not; if yes, go to step S104: intercepting the SQL request and refusing to send the SQL request to the database; otherwise, step S105 is performed: the SQL request is sent to the database.

The database is a database supporting a general SQL statement, and in an alternative embodiment, the database includes: one or more of a mysql database, an oracle database, and a sqlserver database.

In an alternative embodiment, the first user's SQL request is an SQL statement that includes an SQL body and a condition value. For example, a selected from table where column1 is 1 and column2 is 2 and a selected from table column1 is 4 and column 5, although they are two SQL statements, they belong to the same SQL body and differ only in condition values. The slow SQL statement is caused by the SQL body and is irrelevant to the condition value, so the slow SQL statement can be effectively screened by intercepting according to the SQL body.

The SQL body may be identified in multiple ways, in an optional embodiment, the SQL body may be identified and a corresponding unique identifier encryption string digest may be generated according to one SQL body by using a tool pt-query-digest in the ProxySql, and one SQL body may be uniquely determined by using the identifier encryption string, so as to achieve the purpose of screening the slow SQL statement according to the identifier encryption string.

The encryption mode can be selected according to actual requirements. In an optional embodiment, one or more of MD5(Message Digest Algorithm 5), RSA (Rivest-Shamir-Adleman), des (data Encryption standard), and sha (secure Hash Algorithm) are used as an Encryption method to encrypt the SQL body, which not only can reduce the length of a string and improve data security, but also can reduce interference caused by special characters.

An illegal slow SQL base library needs to be built before the SQL body is parsed from the user's SQL request. In an alternative embodiment, as shown in fig. 2, the method includes:

step S201: collecting original data from a query log of a database; step S202: capturing a slow SQL statement with execution time exceeding a preset value, and step S203: analyzing the SQL body from the slow SQL statement; step S204: encrypting the SQL body parsed from the slow SQL statement to obtain a history identifier encryption string, step S205: and storing the historical identification encryption string into an illegal slow SQL base database.

The preset execution time can be set according to the system requirements, in an optional implementation manner, the preset execution time is set to 0.5s, and after the query log is started, the query log can collect the SQL statements with the execution time exceeding 0.5 s.

The encryption mode adopted for encrypting the SQL statement with the execution time exceeding the preset value is consistent with the encryption mode in the step S102, so that the identification encryption string obtained after the slow SQL statement executes the step S102 can be matched with the historical identification encryption string in the illegal slow SQL base database.

After intercepting the SQL body and rejecting sending the SQL request to the database, in an optional embodiment, steps S301 to S303 are performed, as shown in fig. 3, including:

step S301: sending the SQL request to a second user to prompt the second user that the current SQL request is too long in execution time and needs to be optimized; step S302: responding to an optimization request sent by a second user, sending the SQL request to a third user to prompt the third user that the current SQL request is too long in execution time and needs to be optimized; step S303: and receiving an optimized SQL request sent by a third user, and sending the optimized SQL request to the second user so as to prompt the second user to check the optimized SQL request.

In an optional implementation manner, after intercepting the SQL request, the method further includes: and sending alarm information to the second user in one or more modes of telephone, short message and mail to prompt the second user that the current SQL request execution time is too long and needs to be optimized.

For the selection of the second user and the third user, in an optional implementation manner, a top-bottom relationship may be adopted, that is, the second user is an auditor, the third user is a handler, and the auditor is responsible for managing the handler.

In an optional implementation manner, when the SQL request needs to be optimized, the auditor may only be notified of the alarm information, and after the auditor performs the task allocation for optimization according to the workload arrangement of the processors, the SQL request that needs to be optimized is directly sent to different processors, so that the sending frequency of the SQL request is reduced, and the optimization efficiency is improved.

In another optional implementation, the alarm information and the SQL request to be optimized may be sent to the auditor at the same time, and the auditor performs pre-audit on the SQL request to determine whether the SQL request is really a request of a slow SQL statement, thereby avoiding secondary labor caused by system errors on the SQL request that does not need to be optimized. And after the pre-audit passes, responding to an optimization request sent by the auditor, and sending the SQL request needing to be optimized to the processor. Therefore, repeated labor can be reduced in a multi-gate-keeping mode.

After the optimization of the processor is completed, the optimized SQL request needs to be sent to the auditor for auditing, and the optimized SQL request is determined to be no longer a slow SQL statement, so that the accuracy of SQL statement optimization can be effectively improved by a double auditing system.

In an optional implementation, when the second user fails to review after the review, the optimized SQL request is sent to the third user again in response to the rollback request sent by the second user, so as to prompt the third user that the current SQL request cannot be optimized and needs to be optimized.

The alarm modes of the alarm information can be classified according to the importance degree and the level of the SQL request to be optimized. For example, for the SQL request to be optimized which needs urgent processing, the alarm information is sent by selecting telephone and mail at the same time, so that the second user can receive the alarm information in time and process the alarm information quickly; for the SQL request to be optimized with lower importance degree, the alarm information selects the short message and the mail to be sent simultaneously, only the task to be processed is informed to the second user, and the subsequent optimization processing is carried out according to the actual work arrangement.

In an optional embodiment, the method further comprises: after the SQL request is intercepted and sent to a database, storing a link of the SQL main body into an internal cache; after the optimized SQL request is sent to the second user, the link of the SQL body is deleted from the internal cache in response to the optimization confirmation request sent by the second user, and the optimized SQL request is sent to the database.

The SQL body link comprises a path from the SQL body to each level of optimization completion before optimization, the optimization process of each slow SQL statement is recorded, so that the optimization process can be quickly traced after problems occur subsequently, the optimization nodes of the slow SQL statements can be conveniently searched, and the link is deleted after the optimization is completed, so that the internal storage space is saved.

The internal cache can select a memory database according to actual requirements, and in an optional implementation mode, links of the illegal slow SQL base database and the SQL main body are stored in a redis cache so as to reduce the pressure of the persistent layer database, and meanwhile, the expiration time can be set, so that the reading efficiency is improved, and the retrieval efficiency of the identification encryption string is improved.

After responding to the optimization confirmation request sent by the second user, the method further comprises: and counting the quantity of SQL requests received by the third user, the quantity of SQL requests which are optimized and passed through the examination and the quantity of SQL requests which are not optimized or not passed through the optimization.

In a preferred embodiment, the optimization work of a third user can be checked in a performance assessment mode, the efficiency of the third user in the SQL request optimizing process is guaranteed, the phenomenon of repeated optimization of the same SQL request for multiple times is reduced, and meanwhile, the task quantity is recorded, so that fair and fair enterprise management is facilitated.

After prompting that the execution time of the current SQL request of the second user is too long and needs to be optimized, in an optional embodiment, the method further includes: and displaying the label information of the SQL request to a second user, wherein the label information comprises: the system comprises an SQL main body, an identification encryption string corresponding to the SQL main body, a data connection user name, execution duration and statistical time; the data connection username is the username of the user from which the SQL request was sent, as shown in fig. 4.

According to the label information, a second user can conveniently obtain the related information of the SQL main body to be optimized, the execution time length is known, and the optimization direction and the optimization degree are conveniently determined.

Fig. 5 is a schematic diagram of a main flow of a method and an apparatus for monitoring and managing slow SQL in a database according to an embodiment of the present invention. As shown in fig. 5, any application client may initiate an SQL request, analyze the SQL body from the SQL request through the proxy module, encrypt to obtain an identifier encryption string digest, and query whether the current identifier encryption string digest exists in the illegal slow SQL base database; if yes, intercepting the SQL request; if not, the method directly releases the data, and allows the SQL request to access a database, such as a slowlog base database.

When an optimizer needs to optimize the slow SQL request, a slowlog base database stored in the storage module is called, the checking operation is carried out through a console of the application client, and the whole optimization process is completed at the application client until the optimized SQL request is sent to the agent module.

After receiving the optimized SQL request, the agent module needs to check the optimized SQL request, and can judge through a system program or submit to manual checking. And for the audit result, if the audit is passed, releasing the optimized SQL request, allowing the optimized SQL request to access the database, and if the audit is not passed, performing interception optimization again until the audit is passed, and allowing the optimized SQL request to access the database.

In an optional embodiment, the method further comprises: and (4) acquiring original data from the query log of the database regularly, and updating the illegal slow SQL base database. Considering the rapid development of the internet industry, the illegal slow SQL base database needs to be updated regularly, so that the existing slow SQL statements can be intercepted more effectively.

According to the method for monitoring and managing the slow SQL of the database, the SQL main body is analyzed and encrypted, the slow SQL statements are effectively intercepted, manual investigation is replaced by automation, and cost is reduced to a certain extent. A set of forced optimization mechanism for slow SQL sentences is established, risk monitoring is moved forward, and measurement and interception can be performed in the development and test links. Through counting the occurrence times of slow SQL sentences, the processing quantity and the multi-dimensional consideration on the processing timeliness, a set of consideration standards is provided for the performance assessment of research and development teams.

According to a second aspect of the embodiments of the present invention, a device for monitoring and managing slow SQL of a database is provided.

Fig. 6 is a schematic diagram of main modules of an apparatus 600 for monitoring and managing database slow SQL according to a second aspect of the embodiment of the present invention. As shown in fig. 6, includes:

the analysis module 601 is used for analyzing the SQL body from the SQL request of the first user;

the encryption processing module 602 is configured to encrypt the SQL body to obtain an identifier encryption string, and determine whether the identifier encryption string exists in the illegal slow SQL base database; if yes, intercepting the SQL request and refusing to send the SQL request to the database; otherwise, sending the SQL request to the database.

In an optional embodiment, the parsing module 601 is further configured to, before parsing the SQL body from the SQL request of the first user, collect original data from a query log of the database, capture a slow SQL statement with an execution time exceeding a preset value, parse the SQL body from the slow SQL statement, encrypt the SQL body parsed from the slow SQL statement, obtain a history identifier encryption string, and store the history identifier encryption string in an illegal slow SQL base database.

Optionally, the sending module is further configured to send an alarm message to the second user in one or more of a telephone, a short message, and a mail after the SQL request is intercepted, so as to prompt the second user that the execution time of the current SQL request is too long and needs to be optimized.

Optionally, the sending module is further configured to, after prompting that the current SQL request execution time of the second user is too long and needs to be optimized, send the SQL request to a third user in response to the optimization request sent by the second user, so as to prompt the third user that the current SQL request execution time is too long and needs to be optimized.

In an optional embodiment, the sending module is further configured to receive the optimized SQL request sent by the third user after sending the SQL request to the third user, and send the optimized SQL request to the second user, so as to prompt the second user to check the optimized SQL request.

In an optional embodiment, the apparatus for monitoring and managing slow SQL of a database further includes a sending cache processing module, configured to store a link of the SQL body to an internal cache after intercepting the SQL request;

after the optimized SQL request is sent to the second user, the link of the SQL body is deleted from the internal cache in response to the optimization confirmation request sent by the second user, and the optimized SQL request is sent to the database.

In an optional embodiment, the cache processing module is further configured to count, after responding to the optimization confirmation request sent by the second user, the number of SQL requests received by the third user, the number of SQL requests that are optimized and that pass the review, and the number of SQL requests that are not optimized or that do not pass the optimization.

Optionally, the apparatus for monitoring and managing slow SQL of the database further includes a display module, configured to display, to the second user, tag information of the SQL request after prompting that the execution time of the current SQL request of the second user is too long and needs to be optimized, where the tag information includes: the SQL server comprises an SQL main body, an identification encryption string corresponding to the SQL main body, a data connection user name, execution duration and statistical time; the data connection user name is the user name of the user sending the SQL request.

In an optional embodiment, the parsing module is further configured to periodically collect original data from a query log of the database, and update the illegal slow SQL base database.

In an alternative embodiment, the database comprises: one or more of a mysql database, an oracle database, and a sqlserver database.

Optionally, encrypting the SQL body to obtain an identification encryption string includes: and one or more of MD5, RSA, DES and SHA are used as an encryption mode to encrypt the SQL body.

The device for monitoring and managing the database slow SQL of the embodiment of the invention monitors, alarms and intercepts the database slow SQL sentences through the agent technology. A set of forced optimization mechanism for slow SQL sentences is established, risk monitoring is moved forward, and measurement and interception can be performed in the development and test links.

The device for monitoring and managing the database slow SQL can execute the method provided by the embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method. For technical details that are not described in detail in this embodiment, reference may be made to the method provided by the embodiment of the present invention.

Fig. 7 shows an exemplary system architecture 700 to which the method for monitoring and managing database slow SQL or the apparatus for monitoring and managing database slow SQL according to the embodiments of the invention may be applied.

As shown in fig. 7, the system architecture 700 may include

terminal devices

701, 702, 703, a network 704, and a server 705. The network 704 serves to provide a medium for communication links between the

terminal devices

701, 702, 703 and the server 705. Network 704 may include various connection types, such as wired, wireless communication links, or fiber optic cables, to name a few.

A user may use the

terminal devices

701, 702, 703 to interact with a server 705 over a network 704, to receive or send messages or the like. Various communication client applications, such as shopping applications, web browser applications, search applications, instant messaging tools, mailbox clients, social platform software, and the like, may be installed on the

terminal devices

701, 702, and 703.

The

terminal devices

701, 702, 703 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.

The server 705 may be a server that provides various services, such as a background management server that supports shopping websites browsed by users using the

terminal devices

701, 702, and 703. The background management server may analyze and perform other processing on the received data such as the product information query request, and feed back a processing result (e.g., target push information and product information) to the terminal device.

It should be noted that the method for monitoring and managing the database slow SQL provided by the first aspect of the embodiment of the present invention is generally executed by the server 705, and accordingly, the apparatus for monitoring and managing the database slow SQL provided by the second aspect of the embodiment of the present invention is generally disposed in the server 705.

It should be understood that the number of terminal devices, networks, and servers in fig. 7 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.

Referring now to FIG. 8, shown is a block diagram of a computer system 800 suitable for use with a terminal device implementing an embodiment of the present invention. The terminal device shown in fig. 8 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.

As shown in fig. 8, the computer system 800 includes a Central Processing Unit (CPU)801 that can perform various appropriate actions and processes in accordance with a program stored in a Read Only Memory (ROM)802 or a program loaded from a storage section 808 into a Random Access Memory (RAM) 803. In the RAM 803, various programs and data necessary for the operation of the system 800 are also stored. The CPU 801, ROM 802, and RAM 803 are connected to each other via a bus 804. An input/output (I/O) interface 805 is also connected to bus 804.

The following components are connected to the I/O interface 805: an input portion 806 including a keyboard, a mouse, and the like; an output section 805 including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage portion 808 including a hard disk and the like; and a communication section 809 including a network interface card such as a LAN card, a modem, or the like. The communication section 809 performs communication processing via a network such as the internet. A drive 807 is also connected to the I/O interface 805 as necessary. A removable medium 811 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 807 as necessary, so that a computer program read out therefrom is mounted on the storage portion 808 as necessary.

In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program can be downloaded and installed from a network through the communication section 809 and/or installed from the removable medium 811. The computer program executes the above-described functions defined in the system of the present invention when executed by the Central Processing Unit (CPU) 801.

It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.

The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

The modules described in the embodiments of the present invention may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor includes a sending module, an obtaining module, a determining module, and a first processing module. The names of these modules do not form a limitation on the modules themselves in some cases, and for example, the sending module may also be described as a "module sending a picture acquisition request to a connected server".

As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to comprise:

parsing the SQL body from the SQL request of the first user; encrypting the SQL body to obtain an identification encryption string, and judging whether the identification encryption string exists in an illegal slow SQL base database or not; if yes, intercepting the SQL request and refusing to send the SQL request to the database; otherwise, sending the SQL request to the database.

According to the method and the device for monitoring and managing the slow SQL of the database, the SQL body is analyzed and encrypted, the slow SQL statements are effectively intercepted, manual investigation is replaced by automation, and the cost is reduced to a certain extent. A set of forced optimization mechanism for slow SQL sentences is established, risk monitoring is moved forward, and measurement and interception can be performed in the development and test links. Through counting the occurrence times of slow SQL sentences, the processing quantity and the multi-dimensional consideration on the processing timeliness, a set of consideration standards is provided for the performance assessment of research and development teams.

The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for monitoring and managing database slow SQL, the method comprising:

parsing the SQL body from the SQL request of the first user;

encrypting the SQL main body to obtain an identification encryption string, and judging whether the identification encryption string exists in an illegal slow SQL base database; if yes, intercepting the SQL request, and refusing to send the SQL request to a database; otherwise, the SQL request is sent to a database.

2. The method of claim 1, further comprising, prior to parsing the SQL body from the SQL request of the first user:

the method comprises the steps of collecting original data from a query log of a database, capturing slow SQL sentences with execution time exceeding a preset value, analyzing SQL main bodies from the slow SQL sentences, encrypting the SQL main bodies analyzed from the slow SQL sentences to obtain historical identification encryption strings, and storing the historical identification encryption strings in an illegal slow SQL base database.

3. The method of claim 1, after intercepting the SQL request, further comprising:

and sending the SQL request to a second user to prompt the second user that the current SQL request is too long in execution time and needs to be optimized.

4. The method of claim 3, after intercepting the SQL request, further comprising:

and sending alarm information to the second user in one or more modes of telephone, short message and mail to prompt the second user that the current SQL request execution time is too long and needs to be optimized.

5. The method according to claim 3 or 4, after prompting the second user that the execution time of the current SQL request is too long and needs to be optimized, further comprising:

and responding to an optimization request sent by a second user, and sending the SQL request to a third user to prompt the third user that the current SQL request is too long in execution time and needs to be optimized.

6. The method of claim 5, further comprising, after sending the SQL request to a third user:

and receiving an optimized SQL request sent by a third user, and sending the optimized SQL request to a second user so as to prompt the second user to check the optimized SQL request.

7. The method of claim 6, further comprising:

after intercepting the SQL request, storing the link of the SQL main body to an internal cache;

8. The method of claim 7, further comprising, after responding to the optimization confirmation request sent by the second user:

and counting the quantity of SQL requests received by the third user, the quantity of SQL requests which are optimized and passed through the examination and the quantity of SQL requests which are not optimized or not passed through the optimization.

9. The method according to claim 3 or 4, after prompting the second user that the execution time of the current SQL request is too long and needs to be optimized, further comprising:

displaying label information of the SQL body to a second user, wherein the label information comprises: the system comprises an SQL main body, an identification encryption string corresponding to the SQL main body, a data connection user name, execution duration and statistical time; the data connection user name is the user name of the user sending the SQL request.

10. The method of claim 2, further comprising:

and (4) acquiring original data from the query log of the database regularly, and updating the illegal slow SQL base database.

11. The method of claim 1, wherein the database comprises: one or more of a mysql database, an oracle database, and a sqlserver database.

12. The method of claim 1, wherein encrypting the SQL body to obtain an identification encryption string comprises:

and encrypting the SQL body by adopting one or more of MD5, RSA, DES and SHA as an encryption mode.

13. An apparatus for monitoring and managing slow SQL of a database, comprising:

the analysis module is used for analyzing the SQL body from the SQL request of the first user;

the encryption processing module is used for encrypting the SQL main body to obtain an identification encryption string and judging whether the identification encryption string exists in an illegal slow SQL base database or not; if yes, intercepting the SQL request, and refusing to send the SQL request to a database; otherwise, the SQL request is sent to a database.

14. An apparatus for monitoring and managing database slow SQL, comprising: one or more processors;

a storage system for storing one or more programs,

when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-12.

15. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-12.