Database slow query processing method and device, storage medium and electronic equipment

Abstract

This application provides a method, apparatus, storage medium, and electronic device for processing slow queries in a database, relating to the field of Internet technology. The method includes: acquiring M slow query statements from a first database, where M is a positive integer; and storing the slow query data corresponding to the M slow query statements in the second database based on slow query information in a second database, wherein the query speed of the second database is faster than that of the first database. The solution in this application allows for more convenient and efficient querying of slow query information about the first database, enabling technicians to optimize the first database promptly based on the slow query information.

Description

Technical Field

[0001] This application relates to the field of Internet technology, specifically to a method, apparatus, storage medium, and electronic device for processing slow database queries. Background Technology

[0002] Slow queries have a significant impact on database operations, and in severe cases, can even cause database crashes, paralyzing the entire system. For example, databases that do not have a dedicated slow query log file, or whose slow query log tables are of fixed size, often have long slow query analysis times, making it difficult to assess the current health of the database. Summary of the Invention

[0003] To address the aforementioned technical problems, this application is proposed. Embodiments of this application provide a method, apparatus, storage medium, and electronic device for processing slow database queries.

[0004] In a first aspect, one embodiment of this application provides a method for processing slow queries in a database. The method includes: obtaining M slow query statements from a first database, where M is a positive integer; and storing the slow query data corresponding to the M slow query statements in the second database based on slow query information in a second database, wherein the query speed of the second database is faster than that of the first database.

[0005] In conjunction with the first aspect, in some implementations of the first aspect, based on the slow query information in the second database, the slow query data corresponding to M slow query statements are stored in the second database, including:

[0006] If it is determined that the slow query information in the second database is unrelated to the M slow query statements, the M slow query statements are stored as slow query data in the second database; if it is determined that the slow query information in the second database is related to the M slow query statements, statistical analysis is performed on the M slow query statements to obtain analysis data, and the analysis data is stored as slow query data in the second database.

[0007] In conjunction with the first aspect, in some implementations of the first aspect, the slow query information in the second database includes the last entry timestamp of the slow query statement. Statistical analysis is performed on M slow query statements to obtain analysis data, including: based on the last entry timestamp and the generation timestamps corresponding to the M slow query statements, identifying N incremental slow query statements among the M slow query statements that are not stored in the second database; and performing statistical analysis on the N incremental slow query statements to obtain the analysis data.

[0008] In conjunction with the first aspect, in some implementations of the first aspect, statistical analysis is performed on N incremental slow query statements to obtain analysis data, including: determining P slow query fingerprints corresponding to the N incremental slow query statements, wherein the P slow query fingerprints are all different and P is a positive integer less than or equal to N; and using the P slow query fingerprints respectively, statistical analysis is performed on the N incremental slow query statements to obtain analysis data.

[0009] In conjunction with the first aspect, in some implementations of the first aspect, determining P slow query fingerprints corresponding to N incremental slow query statements includes: based on regular expressions, replacing the parameter variables of each of the N incremental slow query statements to obtain the slow query fingerprints corresponding to each of the N incremental slow query statements.

[0010] Perform deduplication on the slow query fingerprints corresponding to each of the N incremental slow query statements to obtain P slow query fingerprints.

[0011] In conjunction with the first aspect, in certain implementations of the first aspect, P slow query fingerprints are used to statistically analyze N incremental slow query statements to obtain analysis data. This includes: for each slow query fingerprint among the P slow query fingerprints, if the second database does not store slow query fingerprints, at least one of the following is used as the analysis data for the slow query fingerprint: the slow query fingerprint, the fingerprint identifier corresponding to the slow query fingerprint, and all incremental slow query statements corresponding to the slow query fingerprint; if the second database stores slow query fingerprints, at least one of the following is counted: the number of incremental slow query statements under the fingerprint identifier corresponding to the slow query fingerprint, the total execution time of all incremental slow query statements, and the generation timestamp of the last generated incremental slow query statement, to obtain the analysis data for the slow query fingerprint.

[0012] In conjunction with the first aspect, in some implementations of the first aspect, after obtaining M slow query statements from the first database, the method further includes: performing response analysis on the M slow query statements to obtain response analysis results; and issuing alarm information if the response analysis results meet preset alarm conditions.

[0013] In conjunction with the first aspect, in some implementations of the first aspect, obtaining M slow query statements in the first database includes: based on a preset time interval and metadata about the first database stored in the second database, driving the second database to access the first database and the instance corresponding to the first database to obtain M slow query statements.

[0014] In conjunction with the first aspect, in some implementations of the first aspect, the second database is configured with a slow query record table for the first database. The slow query record table includes a fingerprint identifier, a slow query fingerprint corresponding to the fingerprint identifier, the number of slow query statements corresponding to the slow query fingerprint, the total execution time of all slow query statements corresponding to the slow query fingerprint, the last generation timestamp of the slow query statement corresponding to the slow query fingerprint, and a sample of the slow query statement corresponding to the slow query fingerprint.

[0015] Secondly, one embodiment of this application provides a database slow query processing apparatus, comprising: an acquisition module, configured to acquire M slow query statements in a first database, wherein M is a positive integer; and a storage module, configured to store the slow query data corresponding to the M slow query statements in a second database based on the slow query information in a second database, wherein the query speed of the second database is faster than the query speed of the first database.

[0016] In conjunction with the second aspect, in some implementations of the second aspect, the slow query processing device for the database also includes a Hypertext Markup Language page module for receiving screening requests and performing multi-dimensional screening of slow query information based on the screening requests.

[0017] Thirdly, one embodiment of this application provides a computer-readable storage medium storing a computer program for executing the database slow query processing method described in the first aspect.

[0018] Fourthly, one embodiment of this application provides an electronic device, the electronic device comprising: a processor; a memory for storing processor-executable instructions; the processor being configured to execute the database slow query processing method described in the first aspect.

[0019] The slow query processing method provided in this application stores slow query statements in the first database to a second database with faster query speed, so as to query slow query information about the first database more efficiently from the second database. This further facilitates technicians to analyze high-consumption statements about the first database in real time, so as to optimize the corresponding functions of the first database and related systems. Attached Figure Description

[0020] The above and other objects, features, and advantages of this application will become more apparent from the more detailed description of the embodiments of this application in conjunction with the accompanying drawings. The drawings are provided to further illustrate the embodiments of this application and form part of the specification. They are used together with the embodiments of this application to explain this application and do not constitute a limitation thereof. In the drawings, the same reference numerals generally represent the same components or steps.

[0021] Figure 1The diagram shown is a scenario applicable to an embodiment of this application.

[0022] Figure 2 The diagram shown is a flowchart illustrating a method for handling slow database queries provided in an exemplary embodiment of this application.

[0023] Figure 3 The diagram shown is a schematic representation of an exemplary embodiment of this application, illustrating the process of storing slow query data corresponding to M slow query statements into a second database.

[0024] Figure 4 The diagram shown is a flowchart illustrating the statistical analysis of M slow query statements to obtain analysis data, provided by an exemplary embodiment of this application.

[0025] Figure 5 The diagram shown is a flowchart illustrating the process of statistically analyzing N incremental slow query statements to obtain analysis data, as provided in an exemplary embodiment of this application.

[0026] Figure 6 The diagram shown is a flowchart illustrating the process of determining P slow query fingerprints corresponding to N incremental slow query statements, as provided in an exemplary embodiment of this application.

[0027] Figure 7 The diagram shown is a flowchart illustrating the statistical analysis of N incremental slow query statements to obtain analysis data, provided by another exemplary embodiment of this application.

[0028] Figure 8 The diagram shown is a flowchart illustrating a method for handling slow database queries provided in another exemplary embodiment of this application.

[0029] Figure 9 The diagram shown is a schematic representation of a database slow query processing apparatus provided in an exemplary embodiment of this application.

[0030] Figure 10 The diagram shown is a structural schematic of a database slow query processing apparatus provided in another exemplary embodiment of this application.

[0031] Figure 11 The diagram shown is a structural schematic of an electronic device provided in an embodiment of this application. Detailed Implementation

[0032] 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, and 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.

[0033] Slow queries have a significant impact on database operations, and in severe cases, can even cause database crashes. This is especially true for databases that lack a dedicated slow query log file or whose tables storing slow queries are of fixed size. The storage, statistical analysis, and querying of slow queries become extremely cumbersome, further complicating the analysis of the database's current health. For example, MongoDB lacks a dedicated slow query log file, and its tables storing slow query statements are of fixed size, resulting in lengthy analysis times and hindering the assessment of the MongoDB system's current health. Furthermore, current methods for analyzing slow queries in MongoDB typically require logging into the `mongodb.profile` collection or analyzing MongoDB logs using tools like `mtools`. Neither of these methods allows for flexible selection of slow query time periods for analysis, thus failing to provide alerts. Additionally, MongoDB automatically overwrites earlier records after storing multiple slow queries, making it impossible for users to retrieve historical slow query information. Therefore, the slow query information in the MongoDB database needs to be processed accordingly so that technical personnel can promptly query the relevant slow query information and optimize the database accordingly.

[0034] Figure 1 The diagram shown illustrates a scenario applicable to an embodiment of this application. This scenario includes a first database 110, a second database 120, and a server 130. Communication connections exist between the first database 110 and the second database 120, as well as between the second database 120 and the server 130.

[0035] Specifically, the first database can be a relational database or a non-relational database; this application does not specifically limit the type of the first database.

[0036] Server 130 is used to drive the second database to obtain M slow query statements about the first database, and based on the slow query information stored in the second database, to store the slow query data corresponding to the M slow query statements in the second database. The query speed of the second database is faster than that of the first database. The second database can be a relational database or a non-relational database; this application does not limit this.

[0037] Figure 2 The diagram shown is a flowchart illustrating a method for handling slow database queries provided in an exemplary embodiment of this application. Figure 2 As shown in the embodiments of this application, the method for handling slow database queries includes the following steps.

[0038] Step 10: Obtain the M slow query statements from the first database. Here, M is a positive integer.

[0039] Specifically, the first database can be any database, and the embodiments of this application do not specifically limit the type of the first database.

[0040] Furthermore, a slow query statement is a statement whose query time exceeds a specified time. Those skilled in the art can set the query timeout period as needed. For example, if the query timeout period is set to 8 seconds, the slow query log in the first database will record statements that take more than 8 seconds, and these statements will be considered slow query statements.

[0041] Step 20: Based on the slow query information in the second database, store the slow query data corresponding to the M slow query statements in the second database.

[0042] It should be noted that the second database can be any database, as long as its query speed is faster than the first database, to facilitate technicians in quickly querying and analyzing slow query information about the first database from the second database. This application does not specifically limit the type of the second database.

[0043] The slow query information in the second database may be related to slow queries in the first database, slow queries in the second database, or slow queries in other databases besides the first and second databases.

[0044] Slow query information refers to slow query statements stored in the second database, or statistical analysis data about slow query statements.

[0045] Slow query data includes at least one of the following: M slow query statements to be stored, and statistical analysis data corresponding to the M slow query statements to be stored.

[0046] The technical solutions in this application embodiment provide a more convenient and faster way to query slow query information about the first database, thereby facilitating technical personnel to optimize the first database and further resolving performance issues in related systems. Furthermore, unlike storing the M slow query statements directly in the second database, the technical solution in this application embodiment stores the slow query data corresponding to the M slow query statements in the second database. This means that the corresponding M slow query statements can be directly stored in the second database based on the slow query information stored therein, or the statistical analysis data corresponding to the M slow query statements can be stored in the second database. This reduces the data storage volume of the second database and allows technical personnel to directly optimize the first database and related systems based on the slow query data about the first database stored in the second database, without needing to perform statistical analysis based on the initial M slow query statements each time to determine the source of database problems.

[0047] Figure 3 This is a schematic diagram illustrating a process for storing slow query data corresponding to M slow query statements in a second database, as provided in an exemplary embodiment of this application. Figure 2 Extending from the illustrated embodiment Figure 3 The illustrated embodiment will be described in detail below. Figure 3 The illustrated embodiments and Figure 2 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0048] like Figure 3 As shown, based on the slow query information in the second database, the slow query data corresponding to M slow query statements is stored in the second database, including the following steps.

[0049] Step 21: Determine whether the slow query information in the second database is related to the M slow query statements.

[0050] Specifically, the M slow query statements are slow query statements related to the first database. The determination of whether the slow query information in the second database is related to the M slow query statements is equivalent to determining whether there is slow query information related to the first database in the second database.

[0051] Furthermore, the presence of slow query information about the first database in the second database can be determined by using a pre-specified storage path for the slow query information of the first database in the second database. The storage path includes slow query log files in the second database, slow query record tables configured in the second database for the first database, etc. For example, the slow query record table for the first database can be queried in the second database. If the slow query record table records slow query information for the first database, then slow query information about the first database exists in the second database; if the slow query record table does not record slow queries for the first database, then slow query information about the first database does not exist in the second database. It should be noted that those skilled in the art can also choose other storage paths in the second database to store the slow query information of the first database, and this embodiment does not specifically limit this.

[0052] For example, in actual application, if the judgment result of step 21 is negative, that is, the slow query information in the second database is not related to the M slow query statements, then step 22 is executed; if the judgment result of step 21 is positive, that is, the slow query information in the second database is related to the M slow query statements, then step 23 is executed.

[0053] Step 22: Store the M slow query statements as slow query data in the second database.

[0054] Specifically, the M slow query statements from the first database are stored in the second database as slow query data.

[0055] Step 23: Perform statistical analysis on the M slow query statements to obtain analysis data, and store the analysis data as slow query data in the second database.

[0056] The analysis data is a more intuitive representation of slow query data obtained by organizing, classifying, and calculating M slow query statements.

[0057] The technical solutions in this application embodiment can reduce the data storage volume of the second database on the one hand, and facilitate technicians to directly retrieve analysis data on slow queries of the first database from the second database, thereby optimizing the first database.

[0058] Figure 4 The diagram illustrates a flowchart of an exemplary embodiment of this application, showing the statistical analysis of M slow query statements to obtain analysis data. Figure 3 Extending from the illustrated embodiment Figure 4 The illustrated embodiment will be described in detail below. Figure 4 The illustrated embodiments and Figure 3 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0059] like Figure 4 As shown, the slow query information in the second database includes the last entry timestamp of the slow query statement. Statistical analysis is performed on M slow query statements to obtain analysis data, including the following steps.

[0060] Step 231: Based on the last entry timestamp and the generation timestamps corresponding to the M slow query statements, determine the N incremental slow query statements that were not stored in the second database among the M slow query statements.

[0061] Specifically, based on the generation timestamps of the M slow query statements, the slow query statements generated after the last entry timestamp are selected as the N incremental slow query statements that are not stored in the second database.

[0062] For example, if M=5, the last entry timestamp is 8:00 AM on May 1, 2022, and the generation times of the five slow query statements are 7:00 AM on April 28, 2022, 6:00 AM on May 1, 2022, 9:00 AM on May 5, 2022, 9:05 AM on May 5, 2022, and 12:00 PM on May 5, 2022, then the three slow query statements generated at 9:00 AM on May 5, 2022, 9:05 AM on May 5, 2022, and 12:00 PM on May 5, 2022 are considered as three incremental slow query statements that are not stored in the second database.

[0063] Step 232: Perform statistical analysis on N incremental slow query statements to obtain the analysis data.

[0064] It should be noted that if, based on the final entry timestamp and the generation timestamps of the M slow query statements, it is determined that all M slow query statements were generated before the final entry timestamp, then the number of incremental slow query statements not stored in the second database is zero. If the number of incremental slow query statements is zero, then no statistical analysis or storage processing is performed on the M slow query statements in the first database.

[0065] The solution in this embodiment can obtain N incremental slow query statements about the first database compared to those already stored in the second database, thereby reducing the number of slow query statements about the first database that the second database needs to store, and making it easier for people to quickly retrieve slow query statements related to the first database.

[0066] Figure 5 This is a flowchart illustrating the statistical analysis of N incremental slow query statements to obtain analysis data, provided as an exemplary embodiment of this application. Figure 4 Extending from the illustrated embodiment Figure 5 The illustrated embodiment will be described in detail below. Figure 5 The illustrated embodiments and Figure 4The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0067] like Figure 5 As shown, statistical analysis is performed on N incremental slow query statements to obtain analysis data, including the following steps.

[0068] Step 2321: Determine the P slow query fingerprints corresponding to the N incremental slow query statements.

[0069] For example, each of the P slow query fingerprints is unique, where P is a positive integer less than or equal to N. The slow query fingerprint represents statements with the same slow query SQL structure, used to indicate the structural information of the slow query statements.

[0070] Step 2322: Statistical analysis is performed on N incremental slow query statements using P slow query fingerprints to obtain analysis data.

[0071] In this embodiment, N incremental slow query statements are summarized and analyzed on a slow query fingerprint basis, so that technicians can more intuitively and clearly query the execution status under each slow query fingerprint and optimize the first database accordingly.

[0072] Figure 6 This is a flowchart illustrating the process of determining P slow query fingerprints corresponding to N incremental slow query statements, provided as an exemplary embodiment of this application. Figure 5 Extending from the illustrated embodiment Figure 6 The illustrated embodiment will be described in detail below. Figure 6 The illustrated embodiments and Figure 5 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0073] like Figure 6 As shown, determining the P slow query fingerprints corresponding to N incremental slow query statements includes the following steps.

[0074] Step 23211: Based on regular expressions, replace the parameter variables of each of the N incremental slow query statements to obtain the slow query fingerprints corresponding to each of the N incremental slow query statements.

[0075] For example, for the slow query statement db.find({'conversationId':1,'payload':Binary(″,0),'saslContinue':1}), replacing the parameter variables in the slow query statement will result in the slow query fingerprint db.find({'conversationId':'?','payload':'?','saslContinue':'?'}).

[0076] For the slow query statement db.find({'conversationId':2,'payload':Binary(″,1),'saslContinue':3}), replacing the parameter variables in the slow query fingerprint yields the slow query fingerprint db.find({'conversationId':'?','payload':'?','saslContinue':'?'}).

[0077] Step 23212: Perform deduplication on the slow query fingerprints corresponding to each of the N incremental slow query statements to obtain P slow query fingerprints.

[0078] Following the example in step 23211, the slow query fingerprints of the slow query statements db.find({'conversationId':1,'payload':Binary(″,0),'saslContinue':1}) and db.find({'conversationId':2,'payload':Binary(″,1),'saslContinue':3}) are the same. Therefore, a deduplication operation is performed on the slow query fingerprints, retaining the same slow query fingerprint corresponding to these two slow query statements.

[0079] Through the technical solutions in this application embodiment, the slow query fingerprint corresponding to the slow query statement can be obtained, and the same slow query fingerprint corresponding to N incremental slow query statements can be deduplicated to remove redundant analysis data corresponding to incremental slow query statements and simplify the slow query data to be stored in the second database.

[0080] Figure 7 The diagram shown is a flowchart illustrating the statistical analysis of N incremental slow query statements to obtain analysis data, provided in another exemplary embodiment of this application. Figure 5 Extending from the illustrated embodiment Figure 7 The illustrated embodiment will be described in detail below. Figure 7 The illustrated embodiments and Figure 5 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0081] like Figure 7 As shown, P slow query fingerprints are used to perform statistical analysis on N incremental slow query statements to obtain analysis data, including the following steps.

[0082] Step 23221: For each of the P slow query fingerprints, determine whether the slow query fingerprint is stored in the second database.

[0083] For example, if the execution result of step 23221 is negative, that is, the slow query fingerprint is not stored in the second database, then step 23222 is executed; if the execution result of step 23221 is positive, that is, the slow query fingerprint is stored in the second database, then step 23223 is executed.

[0084] Step 23222: Use at least one of the following as analysis data for the slow query fingerprint: slow query fingerprint, fingerprint identifier corresponding to the slow query fingerprint, and all incremental slow query statements corresponding to the slow query fingerprint.

[0085] Fingerprint identifiers are used to identify slow query fingerprints, allowing for quick and accurate determination of their corresponding slow query fingerprints. Specifically, a string-type fingerprint identifier for the slow query fingerprint is generated by performing a Message Digest Algorithm 5 (MD5) check on the slow query fingerprint and the database table name. Each slow query fingerprint has its own unique fingerprint identifier.

[0086] Step 23223: Count at least one of the following: the number of incremental slow query statements under the fingerprint identifier corresponding to the slow query fingerprint, the total execution time of all incremental slow query statements, and the generation timestamp of the last generated incremental slow query statement, to obtain the analysis data of the slow query fingerprint.

[0087] The number of incremental slow query statements under the fingerprint identifier corresponding to the slow query fingerprint is the number of times the slow query fingerprint is executed. The total execution time of all incremental slow query statements is the total execution time of the slow query fingerprint. The generation timestamp of the last generated incremental slow query statement is the last execution time of the slow query fingerprint.

[0088] In this embodiment, the incremental slow query statements can be statistically analyzed based on whether the corresponding slow query fingerprint is stored in the second database, making the results of the slow query information about the first database stored in the second database clearer and more intuitive.

[0089] Figure 8 The diagram shown is a flowchart illustrating a method for handling slow database queries provided in another exemplary embodiment of this application. Figure 2 Extending from the illustrated embodiment Figure 8 The illustrated embodiment will be described in detail below. Figure 8 The illustrated embodiments and Figure 2 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0090] like Figure 8 As shown, after storing the slow query data corresponding to the M slow query statements in the second database, the following steps are also included.

[0091] Step 30: Perform response analysis on the slow query data corresponding to the M slow query statements to obtain the response analysis results.

[0092] Step 40: Determine whether the response analysis results meet the preset alarm conditions.

[0093] Specifically, the slow query data corresponding to the M slow query statements can be analyzed according to preset alarm conditions. For example, preset alarm conditions could be that the query timeout is greater than 20 seconds, the slow query fingerprint is executed more than 10 times within a preset time period, or the number of slow query statements generated by the first database within a preset time period is greater than 100, etc. After determining the alarm conditions in the example, the slow query data can be analyzed according to the alarm conditions in the aforementioned example.

[0094] For example, if the execution result of step 40 is negative, that is, the response analysis result does not meet the preset alarm conditions, then step 50 is executed; if the execution result of step 40 is positive, that is, the response analysis result meets the preset alarm conditions, then step 60 is executed.

[0095] Step 50, End.

[0096] Step 60: Issue an alarm message.

[0097] Unlike technicians who proactively query and analyze slow query information about the first database after discovering an anomaly, this application passively receives alarm information about the first database. This solution can receive the corresponding alarm information as soon as the first database becomes abnormal and perform corresponding maintenance and optimization on the first database.

[0098] In an exemplary embodiment of this application, obtaining M slow query statements from the first database includes: based on a preset time interval and metadata about the first database stored in the second database, driving the second database to access the first database and the instance corresponding to the first database to obtain the M slow query statements.

[0099] Specifically, the metadata of the first database includes the first database cluster name, node Internet Protocol (IP) address, node port, query timeout threshold for slow queries, first database user login information, etc.

[0100] Furthermore, the preset time interval can be specifically set according to the actual situation. For example, if the preset time interval is 10 minutes, the server will drive the second database to access the first database and its instance once every 10 minutes to obtain the slow query statements of the first database.

[0101] The technical solution in this application embodiment can more conveniently obtain slow query statements from the first database at regular intervals, so as to prevent slow query statements from accumulating in the first database over a long period of time and overwriting earlier slow query records, making it inconvenient for users to search.

[0102] In an exemplary embodiment of this application, the second database is configured with a slow query record table related to the first database. The slow query record table includes a fingerprint identifier, a slow query fingerprint corresponding to the fingerprint identifier, the number of slow query statements corresponding to the slow query fingerprint, the total execution time of all slow query statements corresponding to the slow query fingerprint, the last generation timestamp of the slow query statement corresponding to the slow query fingerprint, and a sample of the slow query statement corresponding to the slow query fingerprint.

[0103] In addition, the slow query record table also includes the host IP address, the first database port, the name of the slow query set, the source of the slow query user, the application IP address, and the collection time of the slow query statement.

[0104] Through the technical solution in this embodiment, the slow query data obtained from the first database can be classified and recorded in the second database according to the slow query record table, so as to display the slow query information of the first database more intuitively and clearly.

[0105] The following section uses the example of the first database being the mongo database under a mongo database cluster and the second database being the mysql database to illustrate the solution in this application.

[0106] First, configure a slow query log table for all MongoDB databases under the MongoDB cluster in the MySQL database and configure login information. The login table records metadata related to each MongoDB database under the MongoDB cluster that needs to be inspected by the MySQL database, including the login method for each MongoDB database, such as the MongoDB cluster name, node IP, node port, slow query timeout threshold, and user login information for the MongoDB cluster. The slow query log table fields include fingerprint identifier, the corresponding slow query fingerprint, a sample query statement for the slow query fingerprint, the total execution time of the slow query fingerprint, the number of times the slow query fingerprint was executed, the last execution time of the slow query fingerprint, the host IP address, the MongoDB database port, the slow query collection name, the source of the slow query user, the application IP address, and the slow query collection collection collection time.

[0107] Then, the server-driven second database polls all mongo libraries and their corresponding instances to obtain the M slow query statements from the slow query set of the corresponding mongo library. For example, M=5, to further illustrate the handling of slow queries in the mongo library.

[0108] For five slow query statements retrieved from a specific MongoDB database, the process first checks if slow query information for that MongoDB database is stored in the MySQL database. If not, all five retrieved slow query statements are stored in the MySQL database. If they are, incremental slow query statements are determined from the five slow query statements based on the timestamp of the last slow query statement stored in the MySQL database and the timestamps of the five slow query statements. For example, if the number of determined incremental slow query statements is zero, the five retrieved slow query statements from the MongoDB database are not stored. If the number of identified incremental slow query statements is 3, then extract the slow query fingerprints of these 3 incremental slow query statements and determine whether the corresponding slow query fingerprints of these 3 incremental slow query statements are stored in the MySQL database. If not, store the unstored slow query fingerprints, the fingerprint identifiers corresponding to the slow query fingerprints, and the incremental slow query statements corresponding to the slow query fingerprints in the MySQL database. If they are stored, count the fingerprint identifiers corresponding to the stored slow query fingerprints, the number of incremental slow query statements under the fingerprint identifiers, the total execution time of the slow query fingerprints, and the last generation timestamp of the slow query fingerprints, and store the statistical results in the MySQL database.

[0109] This concludes the processing of M slow query statements from a specific mongo database within the obtained mongo database cluster.

[0110] Furthermore, the slow query records of the mongo database under the mongo database cluster can be polled once according to a preset time interval, such as 5 minutes, and the slow query processing method of the database mentioned in the embodiments of this application can be executed on the obtained slow query statements about the mongo database, and the slow query data corresponding to the slow query statements of the mongo database can be stored in the mysql database.

[0111] It is understood that this application only uses MongoDB and MySQL databases as examples to illustrate the method for handling slow database queries in this application embodiment. Those skilled in the art can arbitrarily choose the specific database types corresponding to the first and second databases according to the actual application situation, and this application does not limit them.

[0112] The above text combined Figures 2 to 8 This paper describes in detail an embodiment of the database slow query processing method of this application. The following is in conjunction with... Figures 9 to 10 This application provides a detailed description of embodiments of the database slow query processing apparatus. It should be understood that the descriptions of the database slow query processing method embodiments correspond to the descriptions of the database slow query processing apparatus embodiments; therefore, any parts not described in detail can be referred to the preceding method embodiments.

[0113] Figure 9 The diagram shown is a structural schematic of a database slow query processing apparatus provided in an exemplary embodiment of this application. Figure 9 As shown, the database slow query processing apparatus provided in this application embodiment includes:

[0114] The acquisition module 910 is used to acquire M slow query statements from the first database, where M is a positive integer;

[0115] Storage module 920 is used to store the slow query data corresponding to M slow query statements in the second database based on the slow query information in the second database, wherein the query speed of the second database is faster than the query speed of the first database.

[0116] In one embodiment of this application, the storage module 920 is further configured to: if it is determined that the slow query information in the second database is unrelated to the M slow query statements, store the M slow query statements as slow query data in the second database; if it is determined that the slow query information in the second database is related to the M slow query statements, perform statistical analysis on the M slow query statements to obtain analysis data, and store the analysis data as slow query data in the second database.

[0117] In one embodiment of this application, the storage module 920 is further configured to determine N incremental slow query statements that are not stored in the second database among the M slow query statements based on the last entry timestamp and the generation timestamps corresponding to the M slow query statements; and to perform statistical analysis on the N incremental slow query statements to obtain the analysis data.

[0118] In one embodiment of this application, the storage module 920 is further configured to determine P slow query fingerprints corresponding to N incremental slow query statements, wherein the P slow query fingerprints are all different and P is a positive integer less than or equal to N; and to perform statistical analysis on the N incremental slow query statements using the P slow query fingerprints respectively to obtain analysis data.

[0119] In one embodiment of this application, the storage module 920 is further configured to replace the parameter variables of each of the N incremental slow query statements based on regular expressions to obtain the slow query fingerprints corresponding to each of the N incremental slow query statements; and to perform a deduplication operation on the slow query fingerprints corresponding to each of the N incremental slow query statements to obtain P slow query fingerprints.

[0120] In one embodiment of this application, the storage module 920 is further configured to, for each of the P slow query fingerprints, if the second database does not store the slow query fingerprint, use at least one of the following as the analysis data of the slow query fingerprint: the slow query fingerprint, the fingerprint identifier corresponding to the slow query fingerprint, and all incremental slow query statements corresponding to the slow query fingerprint; if the second database stores the slow query fingerprint, count at least one of the following: the number of incremental slow query statements under the fingerprint identifier corresponding to the slow query fingerprint, the total execution time of all incremental slow query statements, and the generation timestamp of the last generated incremental slow query statement, to obtain the analysis data of the slow query fingerprint.

[0121] In one embodiment of this application, the acquisition module 910 is further configured to drive the second database to access the first database and the instance corresponding to the first database based on a preset time interval and metadata about the first database stored in the second database, and acquire M slow query statements.

[0122] In one embodiment of this application, the second database is configured with a slow query record table for the first database. The slow query record table includes a fingerprint identifier, a slow query fingerprint corresponding to the fingerprint identifier, the number of slow query statements corresponding to the slow query fingerprint, the total execution time of all slow query statements corresponding to the slow query fingerprint, the last generation timestamp of the slow query statement corresponding to the slow query fingerprint, and a sample of the slow query statement corresponding to the slow query fingerprint.

[0123] Figure 10 The diagram shown is a structural schematic of a database slow query processing apparatus provided in another exemplary embodiment of this application. Figure 9 Extending from the illustrated embodiment Figure 10 The illustrated embodiment will be described in detail below. Figure 10 The illustrated embodiments and Figure 9 The differences between the embodiments shown are not repeated here, and the similarities are not repeated here.

[0124] like Figure 10 As shown, the database slow query processing device also includes a Hypertext Markup Language page module 930, which is used to receive screening requests and perform multi-dimensional screening of slow query information based on the screening requests.

[0125] For example, multi-dimensional screening includes query time period, IP address of the first database, port of the first database, number of times the slow query fingerprint is executed, and execution time of the slow query fingerprint.

[0126] It is understandable that those skilled in the art can choose specific screening methods for slow query information based on actual application conditions.

[0127] Through the technical solutions in the embodiments of this application, technicians and database management programs can more conveniently observe the slow query information of the first database in real time, and promptly grasp the operational health status of the first data.

[0128] Below, for reference Figure 11 This describes an electronic device according to embodiments of the present application. Figure 11 The diagram shown is a structural schematic of an electronic device provided in an exemplary embodiment of this application.

[0129] like Figure 11 As shown, the electronic device 1100 includes one or more processors 1101 and memory 1102.

[0130] The processor 1101 may be a central processing unit (CPU) or other form of processing unit with data processing capabilities and / or instruction execution capabilities, and may control other components in the electronic device 110 to perform desired functions.

[0131] The memory 1102 may include one or more computer program products, which may include various forms of computer-readable storage media, such as volatile memory and / or non-volatile memory. The volatile memory may include, for example, random access memory (RAM) and / or cache memory. The non-volatile memory may include, for example, read-only memory (ROM), hard disk, flash memory, etc. One or more computer program instructions may be stored on the computer-readable storage medium, and the processor 1101 may execute the program instructions to implement the database slow query processing methods of the various embodiments of this application described above, and / or other desired functions. The computer-readable storage medium may also store various contents such as M slow query statements including a first database, slow query information in a second database, and slow query data corresponding to the M slow query statements.

[0132] In one example, the electronic device 110 may also include an input device 1103 and an output device 1104, which are interconnected via a bus system and / or other forms of connection mechanism (not shown).

[0133] The input device 1103 may include, for example, a keyboard, a mouse, etc.

[0134] The output device 1104 can output various information to the outside, including M slow query statements in the first database, slow query information in the second database, and slow query data corresponding to the M slow query statements. The output device 1104 may include, for example, a display, a speaker, a printer, and a communication network and its connected remote output devices, etc.

[0135] Of course, for the sake of simplicity, Figure 11 Only some of the components of the electronic device 110 relevant to this application are shown in this illustration; components such as buses, input / output interfaces, etc., are omitted. In addition, the electronic device 110 may include any other suitable components depending on the specific application.

[0136] In addition to the methods and devices described above, embodiments of this application may also be computer program products, which include computer program instructions that, when executed by a processor, cause the processor to perform the steps in the database slow query processing methods according to various embodiments of this application described above.

[0137] The computer program product can be written in any combination of one or more programming languages ​​to perform the operations of the embodiments of this application. The programming languages ​​include object-oriented programming languages ​​such as Java and C++, as well as conventional procedural programming languages ​​such as C or similar languages. The program code can be executed entirely on the user's computing device, partially on the user's computing device, as a standalone software package, partially on the user's computing device and partially on a remote computing device, or entirely on a remote computing device or server.

[0138] Furthermore, embodiments of this application may also be computer-readable storage media storing computer program instructions thereon, which, when executed by a processor, cause the processor to perform the steps in the database slow query processing methods according to various embodiments of this application described above.

[0139] The computer-readable storage medium may be any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may, for example, include, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or devices, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: electrical connections having one or more wires, portable disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0140] The basic principles of this application have been described above with reference to specific embodiments. However, it should be noted that the advantages, benefits, and effects mentioned in this application are merely examples and not limitations, and should not be considered as essential features of each embodiment of this application. Furthermore, the specific details disclosed above are for illustrative and facilitative purposes only, and are not limitations. These details do not limit the application to the necessity of employing the aforementioned specific details for implementation.

[0141] The block diagrams of devices, apparatuses, devices, and systems involved in this application are merely illustrative examples and are not intended to require or imply that they must be connected, arranged, or configured in the manner shown in the block diagrams. As those skilled in the art will recognize, these devices, apparatuses, devices, and systems can be connected, arranged, and configured in any manner. Words such as “comprising,” “including,” “having,” etc., are open-ended terms meaning “including but not limited to,” and are used interchangeably with them. The terms “or” and “and” as used herein refer to the terms “and / or,” and are used interchangeably with them unless the context clearly indicates otherwise. The term “such as” as used herein refers to the phrase “such as but not limited to,” and is used interchangeably with it.

[0142] It should also be noted that in the apparatus, equipment, and methods of this application, the components or steps can be disassembled and / or recombined. These disassemblies and / or recombinations should be considered as equivalent solutions of this application.

[0143] The above description of the disclosed aspects is provided to enable any person skilled in the art to make or use this application. Various modifications to these aspects will be readily apparent to those skilled in the art, and the general principles defined herein can be applied to other aspects without departing from the scope of this application. Therefore, this application is not intended to be limited to the aspects shown herein, but rather to be accorded the widest scope consistent with the principles and novel features disclosed herein.

[0144] The above description has been given for purposes of illustration and description. Furthermore, this description is not intended to limit the embodiments of this application to the forms disclosed herein. Although numerous exemplary aspects and embodiments have been discussed above, those skilled in the art will recognize certain variations, modifications, alterations, additions, and sub-combinations thereof.

Claims

1. A method for handling slow queries in a database, characterized in that, include: Retrieve the M slow query statements from the first database, where M is a positive integer; If it is determined that the slow query information in the second database is unrelated to the M slow query statements, the M slow query statements are stored as slow query data in the second database. If it is determined that the slow query information in the second database is related to the M slow query statements, statistical analysis is performed on the M slow query statements to obtain analysis data. The analysis data is stored in the second database as slow query data. The analysis data includes analysis data of incremental slow query statements and slow query fingerprints corresponding to the incremental slow query statements. The query speed of the second database is faster than that of the first database. Based on the slow query data retrieved from the first database in the second database, the first database is optimized.

2. The method for handling slow database queries according to claim 1, characterized in that, The slow query information in the second database includes the last entry timestamp of the slow query statement. The statistical analysis of the M slow query statements yields analytical data, including: Based on the last entry timestamp and the generation timestamps corresponding to the M slow query statements, determine the N incremental slow query statements that were not stored in the second database among the M slow query statements; The N incremental slow query statements are statistically analyzed to obtain the analysis data.

3. The method for handling slow database queries according to claim 2, characterized in that, The statistical analysis of the N incremental slow query statements yields the following analysis data: Determine P slow query fingerprints corresponding to the N incremental slow query statements, wherein the P slow query fingerprints are all different and P is a positive integer less than or equal to N; The P slow query fingerprints are used to statistically analyze the N incremental slow query statements to obtain the analysis data.

4. The method for handling slow database queries according to claim 3, characterized in that, Determining the P slow query fingerprints corresponding to the N incremental slow query statements includes: Based on regular expressions, the parameter variables of each of the N incremental slow query statements are replaced to obtain the slow query fingerprint corresponding to each of the N incremental slow query statements. The slow query fingerprints corresponding to the N incremental slow query statements are deduplicated to obtain the P slow query fingerprints.

5. The method for handling slow database queries according to claim 3, characterized in that, The step involves statistically analyzing the N incremental slow query statements using the P slow query fingerprints to obtain the analysis data, including: For each of the P slow query fingerprints; If the slow query fingerprint is not stored in the second database, at least one of the slow query fingerprint, the fingerprint identifier corresponding to the slow query fingerprint, and all incremental slow query statements corresponding to the slow query fingerprint shall be used as the analysis data of the slow query fingerprint. If the slow query fingerprint is stored in the second database, at least one of the following is counted: the number of incremental slow query statements under the fingerprint identifier corresponding to the slow query fingerprint, the total execution time of all incremental slow query statements, and the generation timestamp of the last generated incremental slow query statement, to obtain the analysis data of the slow query fingerprint.

6. The method for handling slow database queries according to any one of claims 1 to 5, characterized in that, After storing the slow query data corresponding to the M slow query statements in the second database, the method further includes: Response analysis is performed on the slow query data corresponding to the M slow query statements to obtain the response analysis results; If the response analysis result meets the preset alarm conditions, an alarm message is issued.

7. The method for handling slow database queries according to any one of claims 1 to 5, characterized in that, The step of obtaining the M slow query statements in the first database includes: Based on a preset time interval and the metadata about the first database stored in the second database, the second database is driven to access the first database and the instance corresponding to the first database to obtain the M slow query statements.

8. The method for processing slow database queries according to any one of claims 1 to 5, characterized in that, The second database is configured with a slow query record table related to the first database. The slow query record table includes a fingerprint identifier, a slow query fingerprint corresponding to the fingerprint identifier, the number of slow query statements corresponding to the slow query fingerprint, the total execution time of all slow query statements corresponding to the slow query fingerprint, the last generation timestamp of the slow query statement corresponding to the slow query fingerprint, and a sample of the slow query statement corresponding to the slow query fingerprint.

9. A processing device for slow database queries, characterized in that, include: The acquisition module is used to acquire M slow query statements from the first database, where M is a positive integer; The storage module is configured to: if it is determined that the slow query information in the second database is unrelated to the M slow query statements, store the M slow query statements as slow query data in the second database; if it is determined that the slow query information in the second database is related to the M slow query statements, perform statistical analysis on the M slow query statements to obtain analysis data, and store the analysis data as slow query data in the second database. The analysis data includes analysis data of incremental slow query statements and the slow query fingerprints corresponding to the incremental slow query statements. The query speed of the second database is faster than that of the first database. An optimization module is used to optimize the first database based on the slow query data retrieved from the second database.

10. The database slow query processing apparatus according to claim 9, characterized in that, Also includes: The Hypertext Markup Language (HTML) page module is used to receive screening requests and perform multi-dimensional screening of the slow query information based on the screening requests.

11. A computer-readable storage medium, characterized in that, The storage medium stores a computer program for executing the database slow query processing method according to any one of claims 1 to 8.

12. An electronic device, characterized in that, The electronic device includes: processor; Memory used to store the processor's executable instructions; The processor is configured to execute the database slow query processing method according to any one of claims 1 to 8.

Images