CN114860757A

CN114860757A - Database query statement processing method and device, computer equipment and storage medium

Info

Publication number: CN114860757A
Application number: CN202210509464.XA
Authority: CN
Inventors: 严加昊; 秦小钟; 顾文文; 徐明力
Original assignee: Shencai Technology Co ltd
Current assignee: Shencai Technology Co ltd
Priority date: 2022-05-11
Filing date: 2022-05-11
Publication date: 2022-08-05

Abstract

The present disclosure relates to a database query statement processing method, apparatus, computer device, storage medium, and computer program product. The method comprises the following steps: reading a database slow query log, and acquiring statistical data of preset statistical indexes of slow query statements; calculating objective weight of the preset statistical index by using a first preset weight calculation method according to the statistical data; determining the combination weight of the preset statistical indexes according to the preset subjective weight and the preset objective weight of the preset statistical indexes; determining a first risk result of the slow query statement according to the combined weight and the statistical data; and determining a first processing priority result of the slow query statement according to the first risk result. By adopting the method, the processing priority guide of the slow query statement can be provided for relevant processing personnel.

Description

Database query statement processing method and device, computer equipment and storage medium

Technical Field

The present disclosure relates to the field of electronic digital data processing technologies, and in particular, to a method and an apparatus for processing a slow query statement, a computer device, and a storage medium.

Background

In the development of data query technology, slow query statements occur. In mysql (which is an abbreviation for My Structured Query Language, representing a relational database management system), a slow Query statement refers to a statement whose response time exceeds a threshold in a slow Query log. A large amount of slow Query SQL (SQL is an abbreviation for Structured Query Language, and represents a database Language with multiple functions such as data manipulation and data definition) affects the performance of a database, resulting in online system failure and user experience.

Aiming at the problem, most of the existing implementation schemes are used for constructing a slow query statement management platform, slow query statements are collected together for management, and a database manager and a developer can check, analyze and process the slow query statements conveniently.

However, as time goes on, the number of slow query statements accumulated in the management platform will increase, and database administrators and developers will have no way to know which slow query statement to solve in priority.

Disclosure of Invention

In view of the foregoing, there is a need to provide a slow query statement processing method, apparatus, computer device, computer readable storage medium and computer program product capable of providing slow query statement resolution priority guidance for relevant processing personnel.

In a first aspect, the present disclosure provides a database query statement processing method. The method comprises the following steps:

reading a database slow query log, and acquiring statistical data of preset statistical indexes of slow query statements;

calculating objective weight of the preset statistical index by using a first preset weight calculation method according to the statistical data;

determining the combination weight of the preset statistical indexes according to the preset subjective weight and the preset objective weight of the preset statistical indexes;

determining a first risk result of the slow query statement according to the combined weight and the statistical data;

and determining a first processing priority result of the slow query statement according to the first risk result.

In one embodiment, the first risk outcome comprises a first risk score, the method further comprising:

and performing descending order on the first risk results according to the first risk scores, and generating first early warning information according to the first risk results with the ranking meeting a first preset threshold value.

In one embodiment, the method further comprises:

obtaining an auditing result of the first processing priority result sent by a first auditing end;

when the audit result is that the audit is not passed, the preset subjective weight of the preset statistical index is obtained again, and the obtained subjective weight is different from the historical subjective weight;

and re-determining the combination weight, the first risk result, the first processing priority result and the auditing result of the first processing priority result sent by the first auditing end according to the objective weight and the re-acquired subjective weight of the preset statistical index until the auditing result of the first processing priority result sent by the first auditing end is approved.

In one embodiment, the method further comprises:

acquiring the weight of the service type;

determining a second risk result according to the business type weight and the first risk result;

and determining a second processing priority result of the slow query statement according to the second risk result.

In one embodiment, the second risk outcome comprises a second risk score, the method further comprising:

and the second risk results are sorted in a descending order according to the second risk scores, and second early warning information is generated according to the second risk results of which the ranking meets a second preset threshold value.

In one embodiment, the method further comprises:

and when the continuous occurrence frequency of any slow query statement in the continuously existing first early warning information reaches a third preset threshold value, determining third early warning information according to the first early warning information containing the slow query statement.

In one embodiment, the calculating the objective weight of the preset statistical indicator by using a first preset weight calculation method includes:

the objective weights are calculated using an entropy weight method.

In a second aspect, the present disclosure also provides a database query statement processing apparatus. The device comprises:

the data acquisition module is used for reading the database slow query log and acquiring the statistical data of the preset statistical indexes of the slow query statement;

the objective weight module is used for calculating objective weight of the preset statistical index by using a first preset weight calculation method according to the statistical data;

the combination weight module is used for determining the combination weight of the preset statistical indexes according to the subjective weight and the objective weight of the preset statistical indexes;

a first risk result module, configured to determine a first risk result of the slow query statement according to the combination weight and the statistical data;

and the first priority module is used for determining a first processing priority result of the slow query statement according to the first risk result.

In a third aspect, the present disclosure also provides a computer device. The computer device comprises a memory storing a computer program and a processor implementing the following steps when executing the computer program:

In a fourth aspect, the present disclosure also provides a computer-readable storage medium. The computer-readable storage medium having stored thereon a computer program which, when executed by a processor, performs the steps of:

In a fifth aspect, the present disclosure also provides a computer program product. The computer program product comprising a computer program which when executed by a processor performs the steps of:

According to the database query statement processing method, the database query statement processing device, the database query statement processing computer equipment, the storage medium and the computer program product, the objective weight and the subjective weight of each preset statistical index of the slow query statement are calculated to obtain the combined weight of each preset statistical index, and then the risk size and the processing priority of the slow query statement are determined according to the combined weight of each preset statistical index and the statistical data of the slow query statement, so that the beneficial effect of providing processing priority guide of the slow query statement for related processing personnel can be achieved, and the utilization rate of human resources and material resources can be optimized.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present disclosure and, together with the description, serve to explain the principles of the disclosure and are not to be construed as limiting the disclosure.

FIG. 1 is a diagram of an application environment of a method for processing a database query statement in one embodiment;

FIG. 2 is a flowchart illustrating a method for processing a database query statement in one embodiment;

FIG. 3 is a sub-flow diagram illustrating a method for processing a database query statement in accordance with another embodiment;

FIG. 4 is a sub-flow diagram illustrating a method for processing a database query statement in accordance with another embodiment;

FIG. 5 is a block diagram showing the structure of a database query statement processing apparatus according to an embodiment;

FIG. 6 is a block diagram showing the sub-module structure of the database query statement processing apparatus in one embodiment;

FIG. 7 is a block diagram showing the sub-module structure of the database query statement processing apparatus in one embodiment;

FIG. 8 is a diagram illustrating an internal structure of a computer device according to an embodiment.

Detailed Description

In order to make the objects, technical solutions and advantages of the present disclosure more clearly understood, the present disclosure is further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the disclosure and are not intended to limit the disclosure.

It should be noted that the terms "first," "second," and the like in the description and claims of the present disclosure and in the foregoing drawings are used for distinguishing between similar elements and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used is interchangeable under appropriate circumstances such that the embodiments of the disclosure described herein are capable of operation in sequences other than those illustrated or otherwise described herein. The implementations described in the exemplary embodiments below are not intended to represent all implementations consistent with the present disclosure. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present disclosure, as detailed in the appended claims.

The database query statement processing method provided by the embodiment of the disclosure can be applied to an application environment as shown in fig. 1. Wherein the terminal 102 communicates with the server 104 via a network. The terminal 102 may be one or more terminals. The data storage system may store data that the server 104 needs to process. The data storage system may be integrated on the server 104 or may be placed on the cloud or other network server. The server 104 reads the database slow query log to obtain statistical data of preset statistical indexes of slow query statements. The server 104 calculates the objective weight of the preset statistical index according to the statistical data by using a first preset weight calculation method. The server 104 may obtain the preset subjective weight of the preset statistical index from the terminal 102. The server 104 determines a combination weight of the preset statistical index according to a preset subjective weight and the objective weight of the preset statistical index. The server 104 determines a first risk result of the slow query statement according to the combined weight and the statistical data. The server 104 determines a first processing priority result of the slow query statement according to the first risk result. The server 104 may send the first processing priority result to the terminal 102. The terminal 102 may be, but not limited to, various personal computers, notebook computers, smart phones, tablet computers, internet of things devices and portable wearable devices, and the internet of things devices may be smart speakers, smart televisions, smart air conditioners, smart car-mounted devices, and the like. The portable wearable device can be a smart watch, a smart bracelet, a head-mounted device, and the like. The server 104 may be implemented as a stand-alone server or as a server cluster comprised of multiple servers.

In one embodiment, as shown in fig. 2, a database query statement processing method is provided, which is described by taking the application environment in fig. 1 as an example, and includes the following steps:

s202, reading the database slow query log, and acquiring statistical data of preset statistical indexes of slow query statements.

The preset statistical index may be a preset statistical index related to the slow query statement. The database slow query log may refer to a log in the database that records slow query data.

Specifically, the preset statistical indexes may include one or more of the following indexes: the method comprises the following steps of near-day query times, average query time, average lock waiting time, average actual reading line number, longest query time and shortest query time. The number of times of query in the near day may be set arbitrarily according to actual needs, and may include, for example, the number of times of query in the near day, the number of times of query in the near two days, the number of times of query in the near three days, the number of times of query in the near N (N is an integer greater than 3) days, and the like. The average query time may refer to an average time of the same slow query statement over multiple queries. The average lock waiting time may be an average value of lock waiting times of the same slow query statement in multiple queries (the lock waiting time indicates a time for locking a table from the beginning to the end of query in the process of obtaining data in the slow query). The average actual reading line number may refer to an average value of actual reading line numbers of the same slow query statement in a plurality of query processes. The average query time, the average lock wait time, and the average actual number of read lines may be statistical indicators corresponding to the number of queries in the near day. For example, when the number of times of last-day queries includes the number of times of last-day queries, and the number of times of last-day queries of the slow query statement X (X represents one slow query statement) is M (M is an integer greater than or equal to 1), the average query time may include a value of a last-day M-time query time of the slow query statement X, the average lock latency may include an average of lock latencies of last-day M-time queries of the slow query statement X, and the average actual read line number may include an average of actual read line numbers of last-day M-time queries of the slow query statement X. The longest query time may refer to a longest query time of a slow query statement over multiple queries. The shortest query time may refer to a shortest query time of a slow query statement among multiple queries. The statistical data of the preset statistical indexes of the slow query statements can be directly obtained from the database slow query logs, or obtained by calculating the data in the database slow query logs. The reading of the database slow query log may comprise a timed reading of the database slow query log, which may be a daily timed reading or a monthly timed reading, for example.

And S204, calculating the objective weight of the preset statistical index by using a first preset weight calculation method according to the statistical data.

Specifically, the objective weight may refer to a weight determined according to a determined rule. The first preset weight calculation method may be any method capable of calculating the objective weight of the preset statistical indicator, and may be, for example, an entropy weight method, a standard deviation method or a CRITIC method (CRITIC is called criterion impact high intercritical Correlation in english, and CRITIC is a method for comprehensively measuring the objective weight of an indicator based on the contrast strength of the indicator and the conflict between indicators). The details of the entropy weight method, the standard deviation method and the CRITIC method are well known in the art and will not be described herein. And calculating the objective weight of the preset statistical index by using a first preset weight calculation method according to the statistical data.

S206, determining the combination weight of the preset statistical indexes according to the preset subjective weight and the preset objective weight of the preset statistical indexes.

Specifically, the subjective weight of the preset statistical index may be preset by a technician or an expert according to a subjective judgment result. And automatically determining the combination weight of each preset statistical index by the program according to the preset subjective weight and the preset objective weight of the preset statistical index, wherein the specific determination mode can be directly adding the subjective weight and the objective weight of the same preset statistical index, or other preset calculation modes.

S208, determining a first risk result of the slow query statement according to the combined weight and the statistical data.

Specifically, there may be a plurality of preset statistical indexes of a slow query statement, and each preset statistical index may have a corresponding combination weight, so that one slow query statement may have a plurality of corresponding combination weights. And calculating by using the combination weight of the preset statistical indexes and the statistical data of the preset statistical indexes of the slow query sentences to obtain the calculation result of each slow query sentence. The specific calculation method is set according to actual needs, and may be, for example: for a slow query statement, the statistical data of each preset statistical index of the slow query statement are multiplied by the corresponding combined weight respectively, and then the obtained products are added to obtain a summation value. The specific calculation method may also be other preset calculation methods, for example, various preprocessing including normalization may be performed on the statistical data. The sum value may be processed as a percentile or a tenths system value. The first risk result may include the calculation result. The first risk result may also include data in the statistical data that is helpful in processing slow queries, such as the average query time, the time-per-query. The computed results may be used to represent the ability of slow query statements to cause system failure or affect user experience. The larger the calculation result is, the greater the ability of the corresponding slow query statement to cause a system failure or affect the user experience is, the more the corresponding slow query statement needs to be preferentially processed. In an exemplary embodiment, the slow query statement W (W represents a specific slow query statement) has two preset statistical indicators, a and B, respectively. Where the combining weight of a is 0.45 and the combining weight of B is 0.55. When the statistical data of W is normalized, if the statistical data corresponding to a is 0.2 and the statistical data corresponding to B is 0.8, the sum of W is 0.53(0.45 × 0.2+0.55 × 0.8 — 0.53). 0.53 may be treated as a percentile value of 53. 0.53 or 53 may be taken as the calculation result of W.

S210, determining a first processing priority result of the slow query statement according to the first risk result.

Wherein the first processing priority result may refer to a processing result comprising a slow query statement processing prioritization. The first processing priority result may refer to a processing result that includes a processing priority of the slow query statement.

Specifically, the first risk results of each slow query statement may be sorted according to a certain index in the first risk results, and then the processing priority of each slow query statement is determined according to the sorted results. The processing priority may refer to an order in which slow query statements are preferentially processed. The first processing priority result can be timely pushed to the corresponding processing terminal, so that the first processing priority result is timely pushed to relevant processing personnel.

In the database query statement processing method, the objective weight and the subjective weight of each preset statistical index of the slow query statement are calculated to obtain the combined weight of each preset statistical index, and the risk size and the processing priority of the slow query statement are determined according to the combined weight of each preset statistical index and the statistical data of the slow query statement, so that the beneficial effect of providing processing priority guide of the slow query statement for related processing personnel can be achieved, and the utilization rate of human resources and material resources can be optimized.

Wherein the first risk score may refer to a risk score determined from the combined weights of the slow query statements. The first preset threshold may refer to a preset ranking threshold of the first risk score.

Specifically, the first risk score may refer to a calculation result obtained by calculating a plurality of combined weights of the same slow query statement. Each slow query statement corresponds to a first risk score. The first preset threshold is satisfied, and the rank is generally up to a specific ranking, for example, the rank is up to the top 10 or the top 20. According to actual needs, the first preset threshold may be met by ranking to a certain percentage, for example, the ranking may reach the top 1% or the top 10%. And performing descending order on the first risk results according to the first risk scores, and then generating first early warning information according to the first risk results with the ranking meeting a first preset threshold value. The first warning information may include first risk results ranked to meet a first preset threshold and corresponding ranks of the first risk results. The first early warning information can be timely pushed to the corresponding processing terminal, so that the first early warning information can be timely pushed to related processing personnel.

In this embodiment, the first risk results are ranked according to the first risk score, and the first early warning information is generated according to the first risk results whose ranks satisfy the preset threshold, so that early warning can be performed on part of slow query statements while providing processing priority guidance for the slow query processing personnel, and the slow query statements with a high risk can be ensured to be timely paid attention by the processing personnel.

In one embodiment, as shown in fig. 3, the method further comprises:

s302, obtaining an auditing result of the first processing priority result sent by a first auditing end;

s304, when the audit result is that the audit is not passed, the preset subjective weight of the preset statistical index is obtained again, and the obtained subjective weight is different from the historical subjective weight;

and S306, re-determining the combination weight, the first risk result, the first processing priority result and the auditing result of the first processing priority result sent by the first auditing end according to the objective weight and the subjective weight of the re-acquired preset statistical index until the auditing result of the first processing priority result sent by the first auditing end is approved.

The historical subjective weight may be a subjective weight corresponding to a first processing priority result that has been already reviewed and the review result is not passed.

Specifically, the first auditing terminal may obtain the first processing priority result, and an operator may audit the first processing priority result. In general, a professional technician audits the first processing priority result at the first audit end, and in a special case, the professional technician may also use a computer program or artificial intelligence to audit the first processing priority result at the first audit end. The audit result may include an audit pass or an audit fail. And when the audit result comprises that the audit is passed, the processing process of the slow query statement is not affected and is normally carried out. And when the audit result comprises that the audit is not passed, the preset subjective weight of the preset statistical index is obtained again, namely, relevant technicians or experts are enabled to reset the subjective weight of each preset statistical index. And for the statistical data of the same time, the obtained subjective weight is different from the historical subjective weight. It should be noted that, since the preset statistical index generally includes a plurality of statistical indexes, in the obtained subjective weight of the preset statistical index again, if the subjective weight of one statistical index is different from the historical subjective weight, it can be considered that the condition "the obtained subjective weight is different from the historical subjective weight" is satisfied. After the preset subjective weight of the preset statistical index is obtained again, according to the objective weight and the preset subjective weight of the preset statistical index, the combination weight, the first risk result, the first processing priority result and the auditing result of the first processing priority result sent by the first auditing end are determined again until the auditing result of the first processing priority result sent by the first auditing end is approved. The specific determination process is described above, and is not described herein again.

In this embodiment, the first processing priority result is audited, and when the audit result is that the audit is not passed, the modification of the first processing priority result is realized by modifying the subjective weight, so that the beneficial effect of ensuring that the processing priority order of the slow query statement meets the actual processing requirement is achieved, and further the more urgent and/or important slow query statement is preferentially processed is ensured.

In one embodiment, as shown in fig. 4, the method further comprises:

s402, acquiring the weight of the service type;

s404, determining a second risk result according to the business type weight and the first risk result;

s406, determining a second processing priority result of the slow query statement according to the second risk result.

In particular, the processing of slow query statements may involve different business lines, which may have different degrees of importance, and slow query statements generated by important business lines generally need to be processed preferentially. The service type weight is the weight representing the important type of the service line. And determining a second risk result according to the acquired business type weight and the first risk result. The specific determination method may be to add all the combination weights in the first risk result to the corresponding service type weights to obtain a comprehensive weight including the service type weights. The specific determination method may also be to determine the comprehensive weight including the service type weight according to other preset calculation rules. The second risk result may include the composite weight. The second risk result may also contain all or part of the first risk result. The second risk result may contain other line-of-business related risk content. And according to the second risk result and according to the risk size, determining a second processing priority result of the slow query statement. The second processing priority result can be timely pushed to the corresponding processing terminal, so that the second processing priority result is timely pushed to relevant processing personnel.

In the embodiment, in the process of determining the processing priority of the slow query statement, the business type factor is added, the importance degree of the business line is considered, the second risk result is determined on the basis of the first risk result, and then the second processing priority result is determined, so that the beneficial effect of more comprehensively and reasonably determining the processing priority of the slow query statement can be achieved.

Wherein the second risk score may refer to a risk score determined according to the composite weight. The second preset threshold may refer to a preset ranking threshold of the second risk score.

Specifically, the composite weight may be directly used as the second risk score, or a result of performing a certain mathematical operation on the composite weight may be used as the second risk score, for example, the composite weight may be processed into a numerical value of a percentage system or a tenth system. Each slow query statement corresponds to a second risk score. The second preset threshold is satisfied, which is generally ranked to a specific ranking, for example, the ranking may be the top 10 or the top 20. According to actual needs, the second preset threshold may be met by ranking to a certain percentage, for example, the ranking may reach the top 1% or the top 10%. And performing descending order on the second risk results according to the second risk scores, and then generating second early warning information according to the second risk results of which the ranking meets a second preset threshold value. The second early warning information may include a second risk result whose ranking satisfies a second preset threshold and a corresponding ranking of the second risk result. The second early warning information can be pushed to the corresponding processing terminal in time, so that the second early warning information is pushed to relevant processing personnel in time.

In this embodiment, the second risk results are ranked according to the second risk score, and the second early warning information is generated according to the second risk results with the ranking satisfying the preset threshold, so that early warning can be performed on part of slow query statements while providing processing priority guidance for the slow query processing personnel, and the slow query statements with higher risk can be ensured to be timely paid attention by the processing personnel.

In one embodiment, the method further comprises:

The third preset threshold may be a threshold of the number of continuous occurrences of the slow query statement in the continuously existing plurality of pieces of first early warning information.

Specifically, the slow query log of the database may be updated continuously, the obtained statistical data may also be updated continuously, and each time the statistical data is obtained, a piece of first warning information may be determined, so that the content of the first warning information may also be updated continuously. One slow query statement can continuously appear in a plurality of pieces of first early warning information, and when the continuous appearance times reach a third preset threshold value, an early warning mechanism is triggered. The early warning mechanism is as follows: and determining third early warning information according to a plurality of pieces of early warning information containing the slow query early warning. The plurality of pieces of first early warning information can be directly aggregated into one piece of early warning information to be used as third early warning information. The plurality of pieces of first early warning information may also be refined or summarized by using a preset data processing method to obtain third early warning information. The third early warning information can be timely pushed to the corresponding processing terminal, so that the third early warning information can be timely pushed to relevant processing personnel.

In this embodiment, for the slow query statement that continuously appears in the plurality of pieces of first early warning information that continuously exist, the third early warning information is generated to perform early warning, so that the beneficial effect that the corresponding slow query statement timely draws attention of the processing personnel and is further processed timely is achieved.

the objective weights are calculated using an entropy weight method.

Specifically, the entropy weight method is an objective weighting method, and in a specific use process, the entropy weight of each index is calculated by using the information entropy according to the dispersion degree of data of each index, and then the entropy weight is corrected to a certain extent according to each index, so that objective index weight is obtained. The objective weight of the preset statistical index is calculated by using an entropy weight method, so that the objectivity and the accuracy of objective weight calculation can be improved. The weight determined by the entropy weight method can be corrected, so that the objective weight calculation requirement under different service environments can be met.

In one embodiment, the preset subjective weight of the preset statistical indicator includes a multi-level subjective weight.

The multi-level subjective weight may refer to a subjective weight determined by multiple levels of slow query processing staff.

Specifically, in the processing process of the slow query statement, processing personnel at the level of multi-line what may be involved may include, for example, each service line technician, a database administrator (the english name of the database administrator is database administrator, DBA for short), a chief technical officer (the english name of the chief technical officer is totally known as chief technology office, CTO for short), and the like. The processing personnel of different levels can have different personnel weights, and each processing personnel can subjectively determine the level subjective weight of the preset statistical index. And for a certain preset statistical index of a slow query statement, multiplying the level subjective weight determined by all related processing personnel by the corresponding personnel weight, and adding the obtained products to obtain the subjective weight of the preset statistical index of the slow query statement. For example, where a slow query statement X (X representing a slow query statement) involves a line of business technician a, a line of business technician B, a database administrator, and a head office technician, the assignment of the human weights may be: the staff weight of the service line technician a is 0.1, the staff weight of the service line technician B is 0.1, the staff weight of the database administrator is 0.3, and the staff weight of the chief technical officer is 0.5. The slow query statement X has two preset statistical indicators, including an indicator M and an indicator N. For the index M of the slow query statement X, the subjective weights of the levels given by the line of business technician a, the line of business technician B, the database administrator, and the chief technician are 0.1, 0.2, 0.3, and 0.4, respectively, and the subjective weight of the index M of the slow query statement X is 0.32 (the calculation process is 0.1 × 0.1+0.2 × 0.1+0.3 × 0.3+0.4 × 0.5 — 0.32). For index N of slow query statement X, the subjective weights given by line of business technician a, line of business technician B, database administrator, and chief technician at the levels are 0.9, 0.8, 0.7, and 0.6, respectively, and the subjective weight of index N of slow query statement X is 0.68 (the calculation process is 0.9 × 0.1+0.8 × 0.1+0.7 × 0.3+0.6 × 0.5 — 0.68). According to actual needs, constraints can be set: the subjective weight of the rank of all the statistical indicators of the same slow query sentence must satisfy the condition that the sum is equal to 1, for example, 0.1+0.9 ═ 1, 0.2+0.8 ═ 1, 0.3+0.7 ═ 1, and 0.4+0.6 ═ 1 in the above example, and such a constraint condition may not be set.

In this embodiment, the subjective weight of the preset statistical index of the slow query statement includes multiple levels of subjective weights, so that the personal will of each level of slow query processing personnel can be fully reflected in the subjective weights, and the subjective weights can be determined more reasonably.

In one embodiment, the method comprises:

and determining a slow query statement preset statistical index and a calculation method thereof, wherein the slow query statement preset statistical index comprises the query times in the last day, the query times in the last three days, the query times in the last seven days, the average query time, the average lock waiting time and the average actual reading line number. And (5) starting mysql slow query log configuration, recording related indexes of slow query statements and storing the related indexes in a database. And calculating an index result by the daily timing scheduling program. And calculating objective weight of the preset statistical index by using an entropy weight method. Subjectively weighting each index (i.e. determining subjective weight), and calculating combined weight in combination with the objective weight. Each slow query statement score is calculated as a first risk score based on the combined weights. Whether the first risk score meets the requirement is audited, if yes, 10 slow query sentences before the score of each service line are selected to generate early warning information, and the early warning information is pushed to each person in charge and relevant technical staff; if not, the subjective empowerment and the subsequent steps are carried out again until the requirements are met. And setting business type weight according to the importance degree of each business line, and multiplying the business type weight by the first risk score to obtain a second risk score. And selecting the corresponding slow query sentences of the first 10 risk scores to generate early warning information of the company level and pushing the early warning information to the chief technical officer. When a certain slow query statement appears in the early warning information pushed to each service line responsible person for three times continuously, the early warning information related to the slow query statement is copied to the chief technical officer.

It should be understood that, although the steps in the flowcharts related to the embodiments as described above are sequentially displayed as indicated by arrows, the steps are not necessarily performed sequentially as indicated by the arrows. The steps are not performed in the exact order shown and described, and may be performed in other orders, unless explicitly stated otherwise. Moreover, at least a part of the steps in the flowcharts related to the embodiments described above may include multiple steps or multiple stages, which are not necessarily performed at the same time, but may be performed at different times, and the execution order of the steps or stages is not necessarily sequential, but may be rotated or alternated with other steps or at least a part of the steps or stages in other steps.

Based on the same inventive concept, the embodiment of the present disclosure further provides a database query statement processing apparatus for implementing the above-mentioned database query statement processing method. The implementation scheme for solving the problem provided by the device is similar to the implementation scheme described in the method, so that the specific limitations in one or more embodiments of the database query statement processing device provided below may refer to the limitations on the database query statement processing method in the foregoing, and are not described herein again.

In one embodiment, as shown in fig. 5, there is provided a database query statement processing apparatus including: a data acquisition module 502, an objective weighting module 504, a combination weighting module 506, a first risk result module 508, and a first priority module 510, wherein:

a data obtaining module 502, configured to read a database slow query log and obtain statistical data of preset statistical indexes of slow query statements;

an objective weight module 504, configured to calculate an objective weight of the preset statistical indicator by using a first preset weight calculation method according to the statistical data;

a combination weight module 506, configured to determine a combination weight of the preset statistical indicator according to a preset subjective weight and the preset objective weight of the preset statistical indicator;

a first risk result module 508, configured to determine a first risk result of the slow query statement according to the combination weight and the statistical data;

a first priority module 510, configured to determine a first processing priority result of the slow query statement according to the first risk result.

In one embodiment, the apparatus further comprises:

and the first early warning module is used for performing descending arrangement on the first risk results according to the first risk scores and generating first early warning information according to the first risk results of which the ranking meets a first preset threshold.

In one embodiment, as shown in fig. 6, the apparatus further comprises a sub-module 600, the sub-module 600 comprising:

the auditing module 602 is configured to obtain an auditing result of the first processing priority result sent by the first auditing end;

a subjective weight module 604, configured to, when the audit result is that the audit is not passed, re-obtain the subjective weight of the preset statistical indicator, where the re-obtained subjective weight is different from the historical subjective weight;

and a circulation module 606, configured to re-determine the combination weight, the first risk result, the first processing priority result, and the review result of the first processing priority result sent by the first reviewing terminal according to the objective weight and the re-obtained subjective weight of the preset statistical index until the review result of the first processing priority result sent by the first reviewing terminal is that review is passed.

In one embodiment, as shown in fig. 7, the apparatus further comprises a sub-module 700, the sub-module 700 comprising:

a service weight module 702, configured to obtain a service type weight;

a second risk result module 704, configured to determine a second risk result according to the service type weight and the first risk result;

a second priority module 706, configured to determine a second processing priority result of the slow query statement according to the second risk result.

In one embodiment, the apparatus further comprises:

and the second early warning module is used for performing descending order arrangement on the second risk results according to the second risk scores and generating second early warning information according to the second risk results of which the ranking meets a second preset threshold value.

In one embodiment, the apparatus further comprises:

and the third early warning module is used for determining third early warning information according to the plurality of pieces of first early warning information containing the slow query statement when the continuous occurrence frequency of any slow query statement in the plurality of pieces of first early warning information continuously existing reaches a third preset threshold value.

The modules in the database query statement processing device can be wholly or partially implemented by software, hardware and a combination thereof. The modules can be embedded in a hardware form or independent from a processor in the computer device, and can also be stored in a memory in the computer device in a software form, so that the processor can call and execute operations corresponding to the modules.

In one embodiment, a computer device is provided, which may be a server, and its internal structure diagram may be as shown in fig. 8. The computer device includes a processor, a memory, and a network interface connected by a system bus. Wherein the processor of the computer device is configured to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of an operating system and computer programs in the non-volatile storage medium. The database of the computer device is used for storing slow query related data. The network interface of the computer device is used for communicating with an external terminal through a network connection. The computer program is executed by a processor to implement a database query statement processing method.

Those skilled in the art will appreciate that the configuration shown in fig. 8 is a block diagram of only a portion of the configuration associated with the disclosed aspects and does not constitute a limitation on the computing devices to which the disclosed aspects apply, as a particular computing device may include more or fewer components than shown, or combine certain components, or have a different arrangement of components.

In one embodiment, a computer device is provided, comprising a memory and a processor, the memory having a computer program stored therein, the processor implementing the following steps when executing the computer program:

In one embodiment, the processor, when executing the computer program, further performs the steps of:

acquiring the weight of the service type;

the objective weights are calculated using an entropy weight method.

In one embodiment, a computer-readable storage medium is provided, having a computer program stored thereon, which when executed by a processor, performs the steps of:

In one embodiment, the computer program when executed by the processor further performs the steps of:

the first risk result comprises a first risk score, the method further comprising:

acquiring the weight of the service type;

the objective weights are calculated using an entropy weight method.

In an embodiment, a computer program product is provided, comprising a computer program which, when being executed by a processor, carries out the steps of the above-mentioned method embodiments.

It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, presented data, etc.) referred to in the present disclosure are information and data that are authorized by the user or sufficiently authorized by each party.

It will be understood by those skilled in the art that all or part of the processes of the methods of the embodiments described above can be implemented by hardware instructions of a computer program, which can be stored in a non-volatile computer-readable storage medium, and when executed, can include the processes of the embodiments of the methods described above. Any reference to memory, databases, or other media used in the embodiments provided by the present disclosure may include at least one of non-volatile and volatile memory. The nonvolatile Memory may include Read-Only Memory (ROM), magnetic tape, floppy disk, flash Memory, optical Memory, high-density embedded nonvolatile Memory, resistive Random Access Memory (ReRAM), Magnetic Random Access Memory (MRAM), Ferroelectric Random Access Memory (FRAM), Phase Change Memory (PCM), graphene Memory, and the like. Volatile Memory can include Random Access Memory (RAM), external cache Memory, and the like. By way of illustration and not limitation, RAM can take many forms, such as Static Random Access Memory (SRAM) or Dynamic Random Access Memory (DRAM), among others. The databases involved in embodiments provided by the present disclosure may include at least one of relational and non-relational databases. The non-relational database may include, but is not limited to, a block chain based distributed database, and the like. The processors referred to in the embodiments provided in this disclosure may be general purpose processors, central processing units, graphics processors, digital signal processors, programmable logic, quantum computing based data processing logic, etc., without limitation.

The technical features of the above embodiments can be arbitrarily combined, and for the sake of brevity, all possible combinations of the technical features in the above embodiments are not described, but should be considered as the scope of the present specification as long as there is no contradiction between the combinations of the technical features.

The above-mentioned embodiments only express several embodiments of the present disclosure, and the description thereof is more specific and detailed, but not construed as limiting the scope of the present disclosure. It should be noted that, for those skilled in the art, various changes and modifications can be made without departing from the concept of the present disclosure, and these changes and modifications are all within the scope of the present disclosure. Therefore, the protection scope of the present disclosure should be subject to the appended claims.

Claims

1. A method for processing a database query statement, the method comprising:

2. The method of claim 1, wherein the first risk result comprises a first risk score, the method further comprising:

3. The method of claim 1, further comprising:

4. The method of claim 1, further comprising:

acquiring the weight of the service type;

5. The method of claim 4, wherein the second risk result comprises a second risk score, the method further comprising:

6. The method of claim 2, further comprising:

7. The method according to claim 1, wherein the calculating the objective weight of the preset statistical indicator using a first preset weight calculation method comprises:

the objective weights are calculated using an entropy weight method.

8. The method according to claim 1, wherein the predetermined subjective weighting of the predetermined statistical indicator comprises a multi-level subjective weighting.

9. An apparatus for processing a slow query statement, the apparatus comprising:

10. The apparatus of claim 9, further comprising:

11. The apparatus of claim 9, further comprising:

the auditing module is used for acquiring an auditing result of the first processing priority result sent by the first auditing end;

the subjective weight module is used for obtaining the subjective weight of the preset statistical index again when the audit result is that the audit is not passed, and the obtained subjective weight is different from the historical subjective weight;

and the circulating module is used for re-determining the combination weight, the first risk result, the first processing priority result and the auditing result of the first processing priority result sent by the first auditing end according to the objective weight and the re-acquired subjective weight of the preset statistical index until the auditing result of the first processing priority result sent by the first auditing end is approved.

12. The apparatus of claim 9, further comprising:

the service weight module is used for acquiring the weight of the service type;

a second risk result module, configured to determine a second risk result according to the service type weight and the first risk result;

and the second priority module is used for determining a second processing priority result of the slow query statement according to the second risk result.

13. The apparatus of claim 12, further comprising:

14. The apparatus of claim 10, further comprising:

15. A computer device comprising a memory and a processor, the memory storing a computer program, characterized in that the processor, when executing the computer program, implements the steps of the method of any of claims 1 to 8.

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

17. A computer program product comprising a computer program, characterized in that the computer program realizes the steps of the method of any one of claims 1 to 8 when executed by a processor.