Search

CN-115221187-B - Processing method and device for slow database query, storage medium and electronic equipment

CN115221187BCN 115221187 BCN115221187 BCN 115221187BCN-115221187-B

Abstract

The application provides a processing method and device for slow database query, a storage medium and electronic equipment, and relates to the technical field of Internet. The method comprises the steps of obtaining M slow query sentences in a first database, wherein M is a positive integer, and storing slow query data corresponding to the M slow query sentences to the second database based on slow query information in the second database, wherein the query speed of the second database is faster than that of the first database. By the scheme of the application, the slow query information about the first database can be queried more conveniently and efficiently, so that technicians can optimize the first database according to the slow query information in time.

Inventors

  • ZHENG LINGYI

Assignees

  • 科大讯飞股份有限公司
  • 科大讯飞股份有限公司

Dates

Publication Date
20260421
Application Date
20220620
Priority Date
20220620

Claims (12)

  1. 1. A method for processing a slow database query, comprising: obtaining M slow query sentences in a first database, wherein M is a positive integer; if the slow query information in the second database is determined to be irrelevant to the M slow query sentences, the M slow query sentences are used as slow query data and stored into the second database; If the slow query information in the second database is determined to be related to the M slow query sentences, carrying out statistical analysis on the M slow query sentences to obtain analysis data, and storing the analysis data as slow query data to the second database, wherein the analysis data comprises incremental slow query sentences and analysis data of slow query fingerprints corresponding to the incremental slow query sentences, and the query speed of the second database is faster than that of the first database; optimizing the first database based on the slow query data of the first database invoked in the second database.
  2. 2. The method for processing slow query in database according to claim 1, wherein the slow query information in the second database includes a last entry time stamp of a slow query statement, and the performing statistical analysis on the M slow query statements to obtain analysis data includes: Determining N increment slow query sentences which are not stored by the second database in the M slow query sentences based on the last warehousing time stamp and the generation time stamps corresponding to the M slow query sentences; and carrying out statistical analysis on the N increment slow query sentences to obtain the analysis data.
  3. 3. The method for processing slow database query according to claim 2, wherein the performing statistical analysis on the N incremental slow query sentences to obtain the analysis data includes: Determining P slow query fingerprints corresponding to the N increment slow query sentences, wherein the P slow query fingerprints are different from each other, and P is a positive integer less than or equal to N; And respectively utilizing the P slow query fingerprints to carry out statistical analysis on the N increment slow query sentences to obtain the analysis data.
  4. 4. The method for processing a slow query in a database according to claim 3, wherein determining P slow query fingerprints corresponding to the N incremental slow query sentences comprises: based on a regular expression, replacing parameter variables of each of the N increment slow query sentences to obtain slow query fingerprints corresponding to each of the N increment slow query sentences; and performing deduplication operation on slow query fingerprints corresponding to the N increment slow query sentences respectively to obtain the P slow query fingerprints.
  5. 5. The method for processing slow query in database according to claim 3, wherein said performing statistical analysis on said N incremental slow query sentences using said P slow query fingerprints, respectively, to obtain said analysis data comprises: For each of the P slow query fingerprints; If the slow query fingerprint is not stored in the second database, taking at least one of the slow query fingerprint, a fingerprint identifier corresponding to the slow query fingerprint and all increment slow query sentences corresponding to the slow query fingerprint as analysis data of the slow query fingerprint; If the slow query fingerprint is stored in the second database, counting at least one of the number of increment slow query sentences under the fingerprint identification corresponding to the slow query fingerprint, the total execution time of all increment slow query sentences and the generation time stamp of the finally generated increment slow query sentences to obtain the analysis data of the slow query fingerprint.
  6. 6. The method for processing a slow query of a database according to any one of claims 1 to 5, further comprising, after the storing the slow query data corresponding to the M slow query sentences in the second database: performing response analysis on slow query data corresponding to the M slow query sentences to obtain response analysis results; and if the response analysis result meets the preset alarm condition, sending out alarm information.
  7. 7. The method for processing slow query of databases according to any one of claims 1 to 5, wherein the obtaining M slow query sentences in the first database comprises: And driving the second database to access the first database and the corresponding examples of the first database based on a preset time interval and metadata about the first database stored in the second database, and acquiring the M slow query sentences.
  8. 8. The method according to any one of claims 1 to 5, wherein a slow query record table related to the first database is configured in the second database, and the slow query record table includes a fingerprint identifier, a slow query fingerprint corresponding to the fingerprint identifier, a number of slow query sentences corresponding to the slow query fingerprint, a total execution duration of all slow query sentences corresponding to the slow query fingerprint, a last generated timestamp of a slow query sentence corresponding to the slow query fingerprint, and a slow query sentence sample corresponding to the slow query fingerprint.
  9. 9. A processing apparatus for slow database query, comprising: The acquisition module is used for acquiring M slow query sentences in the first database, wherein M is a positive integer; If the slow query information in the second database is determined to be related to the M slow query sentences, carrying out statistical analysis on the M slow query sentences to obtain analysis data, and storing the analysis data into the second database, wherein the analysis data comprises incremental slow query sentences and analysis data of slow query fingerprints corresponding to the incremental slow query sentences, and the query speed of the second database is faster than that of the first database; And the optimizing module is used for optimizing the first database based on the slow query data of the first database which is called in the second database.
  10. 10. The database slow query processing apparatus of claim 9, further comprising: and the hypertext markup language page module is used for receiving a screening request and carrying out multi-dimensional screening on the slow query information based on the screening request.
  11. 11. A computer-readable storage medium, characterized in that the storage medium stores a computer program for executing the method of processing a slow database query according to any one of the preceding claims 1 to 8.
  12. 12. An electronic device, the electronic device comprising: A processor; a memory for storing the processor-executable instructions; the processor configured to perform the method of processing a slow database query according to any one of claims 1 to 8.

Description

Processing method and device for slow database query, storage medium and electronic equipment Technical Field The application relates to the technical field of internet, in particular to a processing method and device for slow database query, a storage medium and electronic equipment. Background The slow query of the database has great influence on the database service, and even the database is down when serious, so that the whole system is paralyzed. For example, some tables without independent slow query log storage files and storing slow query logs are databases with fixed sizes, and the analysis time of the slow query is long, so that the current running health condition of the databases is difficult to grasp. Disclosure of Invention The present application has been made to solve the above-mentioned technical problems. The embodiment of the application provides a processing method and device for slow database query, a storage medium and electronic equipment. In a first aspect, an embodiment of the present application provides a method for processing a slow query of a database, where the method includes obtaining M slow query sentences in a first database, where M is a positive integer, and storing slow query data corresponding to the M slow query sentences in a second database based on slow query information in the second database, where a query speed of the second database is faster than a query speed of the first database. With reference to the first aspect, in some implementations of the first aspect, based on the slow query information in the second database, storing slow query data corresponding to the M slow query statements to the second database includes: If the slow query information in the second database is determined to be related to the M slow query sentences, carrying out statistical analysis on the M slow query sentences to obtain analysis data, and storing the analysis data as slow query data to the second database. With reference to the first aspect, in some implementations of the first aspect, the slow query information in the second database includes a last entry timestamp of the slow query statement, and performing statistical analysis on the M slow query statements to obtain analysis data, where the statistical analysis is performed on the N incremental slow query statements, which are not stored in the second database, of the M slow query statements based on the last entry timestamp and the respective corresponding generation timestamps of the M slow query statements to obtain the analysis data. With reference to the first aspect, in some implementations of the first aspect, performing statistical analysis on the N incremental slow query sentences to obtain analysis data includes determining P slow query fingerprints corresponding to the N incremental slow query sentences, where the P slow query fingerprints are different from each other, and P is a positive integer less than or equal to N, and performing statistical analysis on the N incremental slow query sentences by using the P slow query fingerprints respectively to obtain analysis data. With reference to the first aspect, in some implementations of the first aspect, determining P slow query fingerprints corresponding to the N incremental slow query sentences includes replacing parameter variables of each of the N incremental slow query sentences based on a regular expression to obtain slow query fingerprints corresponding to each of the N incremental slow query sentences; And performing deduplication operation on slow query fingerprints corresponding to the N increment slow query sentences respectively to obtain P slow query fingerprints. With reference to the first aspect, in some implementations of the first aspect, the statistical analysis is performed on the N incremental slow query sentences by using the P slow query fingerprints to obtain analysis data, where the statistical analysis includes, for each slow query fingerprint of the P slow query fingerprints, taking at least one of the slow query fingerprint, a fingerprint identifier corresponding to the slow query fingerprint, and all the incremental slow query sentences corresponding to the slow query fingerprint as the analysis data of the slow query fingerprint if the slow query fingerprint is not stored in the second database, and counting at least one of the number of the incremental slow query sentences under the fingerprint identifier corresponding to the slow query fingerprint, a total execution duration of all the incremental slow query sentences, and a generation timestamp of the last generated incremental slow query sentence if the slow query fingerprint is stored in the second database, to obtain the analysis data of the slow query fingerprint. With reference to the first aspect, in some implementation manners of the first aspect, after obtaining the M slow query sentences in the first database, the method further includes performing response analysis on the