A method and device for determining a mode to be optimized for a slow query statement

Abstract

The application discloses a method and device for determining an optimization mode of a slow query statement, which comprises the following steps: performing standardization processing on a plurality of slow query statements in a slow query log to obtain a plurality of standard slow query statements; performing fingerprinting processing on a syntax structure corresponding to the standard slow query statement according to the hierarchical order of each syntax level of the syntax structure to obtain a statement fingerprint of each standard slow query statement; performing performance aggregation processing on the statement fingerprint based on a query parameter corresponding to the standard slow query statement to obtain a performance parameter of each statement fingerprint and determine a target statement fingerprint; and performing frequency statistics on an execution mode of the target statement fingerprint to determine an optimization execution mode. Through the technical scheme, the same template slow query statement can be aggregated, the efficiency and accuracy of determining the optimization execution mode of the slow query statement are improved, and the timeliness of database performance optimization is improved.

Description

Technical Field

[0001] This application belongs to the field of computer technology, specifically relating to a method and apparatus for determining the optimization pattern of slow query statements. Background Technology

[0002] Slow query statements are structured query statements that take longer than a set threshold to execute in a database. In database management systems (DBMS), optimizing query performance is crucial for ensuring efficient system operation. Database performance bottlenecks often stem from slow queries. With the continuous growth of data volume and the increasing complexity of business scenarios in modern society, timely and accurate identification of slow query execution patterns that impact database performance has become an important aspect of database operation and development, ensuring the responsiveness of DBMS.

[0003] In existing technologies, the analysis of slow query statements usually involves reading the slow query logs provided by the database to identify slow query statements. By comparing the specific execution time and number of rows scanned corresponding to the slow query statement to see if they fall within the database performance impact threshold range, it is determined whether the statement can affect database performance. Then, statements that can affect database performance are filtered out, and the original execution mode of these statements is determined as the execution mode to be optimized.

[0004] However, slow query statements with the same template may generate different execution modes for different slow query statements due to different query parameters. Existing technology cannot effectively aggregate slow query statements with the same template. Not only is it necessary to analyze a large number of slow query statements, but it is also impossible to accurately locate the main causes affecting database performance. There are problems such as low efficiency in determining the execution mode to be optimized for slow query statements and inaccurate results, which leads to the inability to optimize database performance in a timely manner. Summary of the Invention

[0005] The purpose of this application is to provide a method and apparatus for determining the optimization mode of slow query statements, which solves the problems of low efficiency and inaccurate results in determining the optimization mode of slow query statements in the prior art. By fingerprinting the syntactic structure of the statement according to the syntactic hierarchy of the standard slow query statement, the fingerprint of the target statement is obtained. The frequency of the execution mode of the target statement fingerprint is statistically analyzed to determine the optimization mode. This can achieve the purpose of aggregating slow query statements with the same template, reducing the amount of data that needs to be analyzed for slow query statements, improving the efficiency and accuracy of determining the optimization mode of slow query statements, and helping to improve the timeliness of database performance optimization.

[0006] In a first aspect, embodiments of this application provide a method for determining the optimization pattern of a slow query statement, the method comprising: Obtain the slow query log from the database, and standardize multiple slow query statements in the slow query log to obtain multiple standard slow query statements. Determine the syntax structure corresponding to each standard slow query statement, and perform fingerprinting on the syntax structure according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement. Based on the query parameters corresponding to the standard slow query statement, the performance of the statement fingerprint is aggregated to obtain the performance parameters of each statement fingerprint, and the statement fingerprints corresponding to the performance parameters that are greater than the preset performance parameter threshold are determined as the target statement fingerprints. The execution patterns of the target statement fingerprint are statistically analyzed for frequency, and the execution pattern with the highest execution frequency is identified as the execution pattern to be optimized.

[0007] Optionally, the syntax structure is fingerprinted according to the hierarchical order of each syntax level to obtain the statement fingerprint of each standard slow query statement, including: Identify multiple independent grammatical levels and multiple related grammatical levels in the grammatical structure. Perform independent fingerprinting on the independent grammatical structure blocks corresponding to each independent grammatical level to obtain independent structure fingerprints. Perform overall fingerprinting on the multiple related grammatical structure blocks corresponding to each related grammatical level to obtain related structure fingerprints. By integrating the independent structure fingerprints and the associated structure fingerprints according to the hierarchical order of each grammatical level in the grammatical structure, the statement fingerprint of each standard slow query statement is obtained.

[0008] Optionally, identify multiple independent grammatical levels and multiple related grammatical levels in the grammatical structure, including: Perform structural logic parsing on adjacent syntactic blocks in the syntactic structure, and determine the parallel or progressive syntactic blocks associated with each syntactic block in the syntactic structure based on the results of the structural logic parsing. The levels corresponding to parallel grammatical structures are defined as independent grammatical levels, and the levels corresponding to each grammatical structure and the associated progressive grammatical structures as a whole are defined as associated grammatical levels.

[0009] Optionally, the number of target statement fingerprints can be multiple; Analyze the execution patterns of the target statement fingerprints by frequency, and identify the execution patterns with the highest execution frequency as those to be optimized, including: Multiple target statement fingerprints are grouped based on independent structure fingerprints and associated structure fingerprints to obtain multiple target fingerprint groups; Frequency statistics are performed on the execution patterns of the target statement fingerprints in each target fingerprint group, and the execution pattern with the highest execution frequency is identified as the execution pattern to be optimized for each target fingerprint group.

[0010] Optionally, multiple target statement fingerprints can be grouped based on independent structural fingerprints and associated structural fingerprints to obtain multiple target fingerprint groups, including: Multiple target statement fingerprints are initially grouped according to independent structure fingerprints to obtain multiple initial grouping results; Determine the number of associated syntax structure blocks corresponding to the associated structure fingerprint in each initial grouping result, and determine the associated structure fingerprint with the largest number of associated syntax structure blocks as the target associated structure fingerprint; Based on the target association structure fingerprint, the target statement fingerprint in each initial grouping result is finally grouped to obtain multiple target fingerprint groups.

[0011] Optionally, multiple slow query statements in the slow query log can be standardized to obtain multiple standard slow query statements, including: Identify the non-logical parameters corresponding to multiple slow query statements in the slow query log and filter out the non-logical parameters; The filtered results are formatted to produce multiple standard slow query statements.

[0012] Optional query parameters include query duration and query frequency; Based on the query parameters corresponding to standard slow query statements, the performance of the statement fingerprints is aggregated to obtain the performance parameters of each statement fingerprint, including: Identify multiple standard slow query statements corresponding to the same statement fingerprint, and calculate the overall query parameters of the same statement fingerprint based on the query duration and query frequency of each standard slow query statement. By iterating through the overall query parameters of all identical statement fingerprints, the performance parameters of each statement fingerprint can be obtained.

[0013] Secondly, embodiments of this application provide an apparatus for determining the optimization pattern of a slow query statement, the apparatus comprising: The statement standardization processing module is used to obtain the slow query log of the database, standardize multiple slow query statements in the slow query log, and obtain multiple standard slow query statements. The statement fingerprinting module is used to determine the syntax structure corresponding to each standard slow query statement, and to perform fingerprinting on the syntax structure according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement. The target fingerprint determination module is used to perform performance aggregation processing on the statement fingerprint based on the query parameters corresponding to the standard slow query statement, obtain the performance parameters of each statement fingerprint, and determine the statement fingerprint corresponding to the performance parameter that is greater than the preset performance parameter threshold as the target statement fingerprint. The module for determining the execution mode to be optimized is used to perform frequency statistics on the execution modes of the target statement fingerprint and determine the execution mode with the highest execution frequency as the execution mode to be optimized.

[0014] Thirdly, embodiments of this application provide an electronic device including a processor, a memory, and a program or instructions stored in the memory and executable on the processor, wherein the program or instructions, when executed by the processor, implement the steps of the method described in the first aspect.

[0015] Fourthly, embodiments of this application provide a readable storage medium on which a program or instructions are stored, which, when executed by a processor, implement the steps of the method described in the first aspect.

[0016] Fifthly, embodiments of this application also provide a computer program product comprising a computer program stored in a computer-readable storage medium, wherein at least one processor of the device reads from the computer-readable storage medium and executes the computer program, causing the device to perform the method described in the first aspect.

[0017] In this embodiment, slow query logs from the database are obtained, and multiple slow query statements in the slow query logs are standardized to obtain multiple standard slow query statements. The syntax structure corresponding to each standard slow query statement is determined, and the syntax structure is fingerprinted according to the hierarchical order of each syntax level to obtain the statement fingerprint of each standard slow query statement. Based on the query parameters corresponding to the standard slow query statements, the statement fingerprints are aggregated to obtain the performance parameters of each statement fingerprint, and the statement fingerprints corresponding to the performance parameters that are greater than a preset performance parameter threshold are determined as target statement fingerprints. The execution modes of the target statement fingerprints are frequency-statistically analyzed, and the execution mode with the highest execution frequency is determined as the execution mode to be optimized. The method described above for determining the optimization patterns of slow query statements solves the problems of low efficiency and inaccurate results in the existing technology. By fingerprinting the syntactic structure of the statement according to the syntactic hierarchy of the standard slow query statement, the target statement fingerprint is obtained. Frequency statistics are performed on the execution patterns of the target statement fingerprint to determine the optimization patterns. This method can aggregate slow query statements with the same template, reduce the amount of data that needs to be analyzed for slow query statements, improve the efficiency and accuracy of determining the optimization patterns of slow query statements, and help improve the timeliness of database performance optimization. Attached Figure Description

[0018] Figure 1 This is a flowchart of a method for determining the optimization pattern of a slow query statement according to an embodiment of this application; Figure 2 This is a flowchart of generating slow query statement fingerprints provided in an embodiment of this application; Figure 3 This is a schematic diagram of the syntax hierarchy of the standard slow query statement provided in this application; Figure 4 This is a flowchart of the screening of available fault handling units provided in the embodiments of this application; Figure 5 This is a structural block diagram of a device for determining the optimization mode of a slow query statement according to an embodiment of this application; Figure 6 This is a structural block diagram of the electronic device provided in the embodiments of this application. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of this application clearer, specific embodiments of this application are described in detail below with reference to the accompanying drawings. It is understood that the specific embodiments described herein are merely for explaining this application and not for limiting it. Furthermore, it should be noted that, for ease of description, only the parts relevant to this application are shown in the drawings, not all of them. Before discussing exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although the flowcharts describe operations (or steps) as sequential processes, many of these operations can be performed in parallel, concurrently, or simultaneously. Furthermore, the order of the operations can be rearranged. The process can be terminated when its operation is completed, but may also have additional steps not included in the drawings. The process can correspond to a method, function, procedure, subroutine, subprogram, etc.

[0020] The technical solutions of the embodiments of this application will be clearly described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this application. All other embodiments obtained by those skilled in the art based on the embodiments of this application are within the scope of protection of this application.

[0021] The terms "first," "second," etc., used in the specification and claims of this application are used to distinguish similar objects and not to describe a specific order or sequence. It should be understood that such use of data can be interchanged where appropriate so that embodiments of this application can be implemented in orders other than those illustrated or described herein, and the objects distinguished by "first," "second," etc., are generally of the same class and the number of objects is not limited; for example, a first object can be one or more. Furthermore, in the specification and claims, "and / or" indicates at least one of the connected objects, and the character " / " generally indicates that the preceding and following objects are in an "or" relationship.

[0022] First, this solution can be used in scenarios that optimize database query performance, especially in situations where slow query statements are identified and their execution patterns are optimized for timely performance improvement. By fingerprinting the syntax structure of standard slow query statements according to their hierarchical order, a target statement fingerprint is obtained. Frequency analysis of the execution patterns of this fingerprint is then performed to determine the execution patterns to be optimized. This allows for the aggregation of slow query statements with the same template, reducing the amount of data required for slow query analysis and improving the efficiency and accuracy of identifying the execution patterns to be optimized. This, in turn, enhances the timeliness of database performance optimization. Based on these use cases, it is understandable that the server can be the primary implementer of this solution.

[0023] The following description, in conjunction with the accompanying drawings, details a method and apparatus for determining the optimization mode of a slow query statement provided in this application, through specific embodiments and application scenarios.

[0024] Figure 1 This is a flowchart illustrating a method for determining the optimization pattern of a slow query statement, as provided in an embodiment of this application. Figure 1 As shown, the specific steps include the following: S101: Obtain the slow query log from the database, and standardize multiple slow query statements in the slow query log to obtain multiple standard slow query statements.

[0025] Slow query statements can be structured queries whose execution time exceeds a preset time. A database slow query log is a file in the database used to record slow query statements and their corresponding query parameters. The slow query log includes the slow query statement, its corresponding query duration, number of rows scanned, and execution mode. Standardization involves removing variables and spaces from slow query statements to extract their logical structure. A standard slow query statement is one that retains only its syntax. Standard slow query statements with the same logic have a unified format. Standardization allows slow query statements with the same syntax to be grouped into the same fingerprint, thus unifying slow query statements with the same logic but different parameters.

[0026] In one embodiment, slow query logs stored in the database can be read to obtain multiple slow query statements. The variable positions can be determined according to the structure of the slow query statements. The parameters corresponding to the variable positions can be replaced with preset characters, and spaces and newlines in the slow query statements can be removed to obtain multiple standard slow query statements.

[0027] In one embodiment, multiple slow query statements in the slow query log are standardized to obtain multiple standard slow query statements. This includes: identifying non-logical parameters corresponding to the multiple slow query statements in the slow query log, filtering the non-logical parameters, and standardizing the format of the filtering results to obtain multiple standard slow query statements. The non-logical parameters can be parameters that do not affect the query logic of the slow query statements. Examples include statement comment parameters, spaces, and newlines.

[0028] In one embodiment, non-logical parameters corresponding to multiple slow query statements in the slow query log can be determined based on the parameter types corresponding to each specific parameter in the slow query statement. Non-logical parameters are then deleted for parameter filtering. The filtered statements are then standardized to the same format to obtain multiple standard slow query statements.

[0029] This solution identifies non-logical parameters corresponding to slow query statements and filters and standardizes their format, thereby avoiding the influence of non-logical parameters on the syntax structure of standard slow query statements and improving the accuracy of the extracted results.

[0030] S102, determine the syntax structure corresponding to each standard slow query statement, and perform fingerprinting processing on the syntax structure according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement.

[0031] The syntax structure can be a structure describing the query logic of a standard slow query statement. It includes the components of each standard slow query statement, such as keywords, table names, column names, operators, join conditions, subqueries, etc., as well as the order and arrangement of these components within the standard slow query statement. The syntax hierarchy can be the structural levels obtained by breaking down each component of the syntax structure. The hierarchy order can be the corresponding position of each syntax level within the entire syntax structure. Fingerprinting is a process that distinguishes standard slow query statements with the same syntax structure from those with other syntax structures. A statement fingerprint can be an identifier representing a standard slow query statement with the same syntax structure.

[0032] In one embodiment, the syntax structure corresponding to each standard slow query statement can be determined based on the components of each standard slow query statement and the order in which the components are arranged in the standard slow query statement. The syntax structure as a whole is hashed according to the components corresponding to each syntax level in the syntax structure and the hierarchical order between the components to generate a unique identifier for each standard slow query statement, thus obtaining the statement fingerprint of each standard slow query statement.

[0033] S103, based on the query parameters corresponding to the standard slow query statement, the performance of the statement fingerprint is aggregated to obtain the performance parameters of each statement fingerprint, and the statement fingerprint corresponding to the performance parameter that is greater than the preset performance parameter threshold is determined as the target statement fingerprint.

[0034] Query parameters can be parameters used to describe the execution status of each standard slow query statement. Query parameters can be calculated by considering the query duration and number of rows scanned for multiple slow queries corresponding to each standard slow query statement. Performance aggregation processing can be an operation that integrates the query parameters of multiple standard slow queries corresponding to the same statement fingerprint. Performance parameters can be parameters describing the overall query execution status of each statement fingerprint. Preset performance parameter thresholds can be pre-set minimum performance parameters for statement fingerprints that can impact database performance. Target statement fingerprints can be statement fingerprints whose execution status can impact database performance.

[0035] In one embodiment, since standard slow query statements with the same structure have the same statement fingerprint, performance aggregation processing can be performed on the same statement fingerprints based on the query parameters and statement fingerprints corresponding to each standard slow query statement to obtain the performance parameters of each statement fingerprint, and the statement fingerprints with performance parameters greater than a preset performance parameter threshold are determined as target statement fingerprints.

[0036] In one embodiment, query parameters include query duration and query frequency. Performance aggregation processing is performed on statement fingerprints based on the query parameters corresponding to standard slow query statements to obtain performance parameters for each statement fingerprint. This includes: identifying multiple standard slow query statements corresponding to the same statement fingerprint; calculating the overall query parameters for the same statement fingerprint based on the query duration and query frequency of each standard slow query statement; and iterating through the overall query parameters of all identical statement fingerprints to obtain the performance parameters for each statement fingerprint. The query duration can be the execution time of each standard slow query statement. The query frequency can be the number of times each standard slow query statement is repeatedly queried within a preset duration. The query parameters in this scheme mainly include total query duration, average query duration, and total number of queries.

[0037] In one embodiment, standard slow query statements can be classified according to statement fingerprints to obtain multiple standard slow query statements corresponding to the same statement fingerprint. Based on the query duration and query frequency of each standard slow query statement, the total query duration, average query duration, and total number of queries for a given statement fingerprint are calculated to obtain the overall query parameters for the same statement fingerprint. The overall query parameters for all identical statement fingerprints are calculated separately, and these overall query parameters are used as the performance parameters for each statement fingerprint.

[0038] This solution calculates the overall query parameters of the same statement fingerprint based on the query duration and query frequency corresponding to each standard slow query statement, thereby obtaining the performance parameters of each statement fingerprint. This achieves the goal of determining the performance parameters of each statement fingerprint by combining multiple factors that affect database performance, thus improving the accuracy of the performance parameter evaluation of statement fingerprints.

[0039] S104, perform frequency statistics on the execution patterns of the target statement fingerprint, and determine the execution pattern with the highest execution frequency as the execution pattern to be optimized.

[0040] The execution mode can be the method used when executing the query task corresponding to the target statement fingerprint. The execution mode includes all steps from parsing to returning the query results for the slow query statement corresponding to the target statement fingerprint. The execution mode to be optimized is the one that has the greatest impact on query time among all the execution modes corresponding to the target statement fingerprint.

[0041] In one embodiment, the execution frequency of each execution mode within a preset time period can be statistically analyzed based on the number of executions of multiple execution modes corresponding to the target statement fingerprint, and the execution mode with the highest execution frequency can be determined as the execution mode to be optimized.

[0042] The technical solution provided in this application involves obtaining slow query logs from a database, standardizing multiple slow query statements in the logs to obtain multiple standard slow query statements, determining the syntax structure corresponding to each standard slow query statement, fingerprinting the syntax structure according to the hierarchical order of each syntax level to obtain a statement fingerprint for each standard slow query statement, performing performance aggregation processing on the statement fingerprints based on the query parameters corresponding to the standard slow query statements to obtain performance parameters for each statement fingerprint, and determining the statement fingerprints corresponding to performance parameters greater than a preset performance parameter threshold as target statement fingerprints, and performing frequency statistics on the execution modes of the target statement fingerprints to determine the execution mode with the highest execution frequency as the execution mode to be optimized. The method described above for determining the optimization patterns of slow query statements solves the problems of low efficiency and inaccurate results in the existing technology. By fingerprinting the syntactic structure of the statement according to the syntactic hierarchy of the standard slow query statement, the target statement fingerprint is obtained. Frequency statistics are performed on the execution patterns of the target statement fingerprint to determine the optimization patterns. This method can aggregate slow query statements with the same template, reduce the amount of data that needs to be analyzed for slow query statements, improve the efficiency and accuracy of determining the optimization patterns of slow query statements, and help improve the timeliness of database performance optimization.

[0043] Figure 2 This is a flowchart illustrating the generation of slow query statement fingerprints provided in an embodiment of this application. For example... Figure 2As shown, the specific steps include the following: S201, identify multiple independent grammatical levels and multiple related grammatical levels in the grammatical structure, perform independent fingerprinting on the independent grammatical structure blocks corresponding to each independent grammatical level to obtain independent structure fingerprints, and perform overall fingerprinting on the multiple related grammatical structure blocks corresponding to each related grammatical level to obtain related structure fingerprints.

[0044] In this context, an independent syntactic level can be the structural hierarchy of the basic components within a syntactic structure. A related syntactic level can be the overall structural hierarchy of the basic components and their sub-components within a syntactic structure. For example, a standard slow query statement is represented as "SELECT * FROM orders WHERE user_id = ?". In this case, "SELECT", "FROM", and "WHERE" correspond to independent syntactic levels, while "FROM orders" and "WHERE user_id" correspond to related syntactic levels. An independent syntactic structure block can be the specific content corresponding to an independent syntactic level within the syntactic structure, such as "SELECT" in the example above. Independent fingerprinting can be the process of encrypting and generating a unique identifier for an independent syntactic structure block. An independent structural fingerprint can be an identifier used to distinguish the same independent syntactic structure block from other syntactic structure blocks. A related syntactic structure block can be the specific content corresponding to a related syntactic level within the syntactic structure. Overall fingerprinting can be the process of encrypting and generating a unique identifier for the entire content of a related syntactic structure block. A related structural fingerprint can be an identifier used to distinguish the same related syntactic structure block from other syntactic structure blocks.

[0045] In one embodiment, multiple independent grammatical levels and multiple associated grammatical levels in the grammatical structure can be determined based on whether each grammatical structure in the grammatical structure has substructures. Each independent grammatical structure block corresponding to each independent grammatical level is fingerprinted independently using a first preset encryption algorithm to generate a unique identifier for each independent grammatical structure block, thus obtaining an independent structure fingerprint. The multiple associated grammatical structure blocks corresponding to each associated grammatical level are fingerprinted as a whole using a second preset encryption algorithm to generate a unique identifier for each associated grammatical structure block, thus obtaining an associated structure fingerprint. The first preset encryption algorithm and the second preset encryption algorithm can be the same or different.

[0046] S202, integrate the independent structure fingerprints and related structure fingerprints according to the hierarchical order of each grammatical level in the grammatical structure to obtain the statement fingerprint of each standard slow query statement.

[0047] In one embodiment, the independent structure fingerprint and the associated structure fingerprint can be concatenated according to the hierarchical order of each grammatical level in the grammatical structure to generate a holistic fingerprint about the grammatical structure. The holistic fingerprint is then mapped to a preset length according to the preset length of the statement fingerprint to obtain the statement fingerprint of each standard slow query statement.

[0048] In one embodiment, identifying multiple independent grammatical levels and multiple associated grammatical levels in a grammatical structure includes: performing structural logic parsing on adjacent grammatical structure blocks in the grammatical structure; determining, based on the structural logic parsing results, parallel or progressive grammatical structure blocks associated with each grammatical structure block in the grammatical structure; determining the level corresponding to the parallel grammatical structure block as an independent grammatical level; and determining the level corresponding to each grammatical structure block and the associated progressive grammatical structure block as an associated grammatical level.

[0049] Adjacent syntactic blocks can be syntactic blocks that are adjacent in position within the syntactic structure. Structural logic parsing can be an operation that determines whether there is a logical relationship between adjacent syntactic blocks. For example, semantic parsing. Parallel syntactic blocks can be adjacent syntactic blocks that do not have a progressive logical relationship. Progressive syntactic blocks can be adjacent syntactic blocks that have a progressive logical relationship. For example, in the example above, "orders" in "FROM orders" is a further restriction on "FROM," therefore, there is a progressive logical relationship between "orders" and "FROM," and "FROM orders" is a progressive syntactic block.

[0050] In one embodiment, a semantic parsing tool can be used to perform structural logic parsing on adjacent syntactic blocks in the syntactic structure to obtain the logical relationships between adjacent syntactic blocks. Based on the logical relationships, parallel or progressive syntactic blocks associated with each syntactic block in the syntactic structure can be determined. The level corresponding to the parallel syntactic blocks is determined as an independent syntactic level, and the level corresponding to each syntactic block and the associated progressive syntactic blocks as a whole is determined as an associated syntactic level.

[0051] Figure 3 This is a schematic diagram of the syntax hierarchy of the standard slow query statement provided in this application.

[0052] like Figure 3As shown, the syntax structure of a standard slow query statement includes syntax structure A, syntax structure B, syntax structure B1, syntax structure B2, syntax structure C, syntax structure C1, and syntax block D. Syntax structures A, B, C, and D do not have progressive logic; they are parallel syntax structure blocks, each located at an independent syntax level. Syntax structure B and syntax structure B1 have progressive logic; the syntax structure block formed by syntax structures B and B1 is a progressive syntax structure block. Similarly, there is no progressive logic between syntax structures B1 and B2, but there is progressive logic between syntax structure B2 and syntax structure B; the syntax structure block formed by syntax structures B, B1, and B2 is a progressive syntax structure block, located at an associated syntax level. Likewise, the syntax structure block formed by syntax structures C and C1 is a progressive syntax structure block, located at an associated syntax level.

[0053] The technical solution provided in this application obtains an independent structure fingerprint by performing independent fingerprinting on the independent grammatical structure blocks corresponding to each independent grammatical level in the grammatical structure, and obtains an associated structure fingerprint by performing overall fingerprinting on multiple associated grammatical structure blocks corresponding to each associated grammatical level. The independent structure fingerprint and the associated structure fingerprint are then integrated according to the hierarchical order of each grammatical level in the grammatical structure to obtain the statement fingerprint of each standard slow query statement. This achieves the goal of generating statement fingerprints while preserving the grammatical structure logic of slow query statements, which is beneficial to the accuracy of aggregate analysis of the query performance of slow query statements based on statement fingerprints.

[0054] Figure 4 This is a flowchart of the screening of available fault handling units provided in an embodiment of this application. For example... Figure 4 As shown, the processing parameters include a processing identifier, and the specific steps include the following: S401: Obtain the slow query log from the database, standardize multiple slow query statements in the slow query log, and obtain multiple standard slow query statements.

[0055] S402, determine the syntax structure corresponding to each standard slow query statement, identify multiple independent syntax levels and multiple related syntax levels in the syntax structure, perform independent fingerprinting on the independent syntax structure blocks corresponding to each independent syntax level to obtain independent structure fingerprints, and perform overall fingerprinting on the multiple related syntax structure blocks corresponding to each related syntax level to obtain related structure fingerprints.

[0056] S403 integrates the independent structure fingerprints and the associated structure fingerprints according to the hierarchical order of each grammatical level in the grammatical structure to obtain the statement fingerprint of each standard slow query statement.

[0057] S404: Based on the query parameters corresponding to the standard slow query statement, the performance of the statement fingerprint is aggregated to obtain the performance parameters of each statement fingerprint. The statement fingerprints corresponding to the performance parameters that are greater than the preset performance parameter threshold are determined as the target statement fingerprints. There are multiple target statement fingerprints.

[0058] S405, group multiple target statement fingerprints based on independent structure fingerprints and associated structure fingerprints to obtain multiple target fingerprint groups.

[0059] Among them, the target fingerprint group can be a fingerprint group obtained by combining different target fingerprints with similar grammatical structures.

[0060] In one embodiment, multiple different target statements having the same independent structural fingerprint and / or the same associated structural fingerprint can be grouped into the same group based on independent structural fingerprints and associated structural fingerprints, resulting in multiple target fingerprint groups. The first and second preset numbers can be the same or different, and can be automatically set according to the grouping precision of the system.

[0061] In one embodiment, grouping multiple target statement fingerprints based on independent structural fingerprints and associated structural fingerprints to obtain multiple target fingerprint groups includes: initially grouping multiple target statement fingerprints according to independent structural fingerprints to obtain multiple initial grouping results; determining the number of associated grammatical structure blocks corresponding to the associated structural fingerprints in each initial grouping result, and determining the associated structural fingerprint with the most associated grammatical structure blocks as the target associated structural fingerprint; and finally grouping the target statement fingerprints in each initial grouping result based on the target associated structural fingerprints to obtain multiple target fingerprint groups. The initial grouping can be an operation of grouping target statement fingerprints with the same independent structural fingerprint into the same group. A target statement fingerprint may include multiple independent structural fingerprints and multiple associated structural fingerprints. The initial grouping can be an operation of grouping different target statement fingerprints with a preset number of identical independent structural fingerprints into the same group. The target associated structural fingerprint can be the associated structural fingerprint referenced when finally grouping multiple target statement fingerprints in the same initial grouping result. The final grouping can be an operation of grouping target statement fingerprints with the same target statement fingerprint in the initial grouping result into the same group.

[0062] In one embodiment, multiple target statement fingerprints with the same independent structural fingerprint and a preset amount of data can be initially grouped according to their independent structural fingerprints to obtain multiple initial grouping results. The associated structural fingerprint of each target statement fingerprint in each initial grouping result is identified, and the sum of the number of basic components and the number of substructure components in the associated grammar hierarchy corresponding to the associated structural fingerprint is determined to obtain the number of associated grammar structure blocks corresponding to each associated structural fingerprint. The associated structural fingerprint with the most associated grammar structure blocks is determined as the target associated structural fingerprint. The same target associated structural fingerprints in each initial grouping result are grouped into the same group to obtain multiple target fingerprint groups.

[0063] This scheme improves the accuracy of grouping multiple target fingerprints by initially grouping them according to independent structural fingerprints, determining the target associated structural fingerprints based on the number of associated syntax blocks corresponding to the associated structural fingerprints, and finally grouping the target statement fingerprints in each initial grouping result according to the target associated structural fingerprints. This ensures that target fingerprints in the same group have similar execution logic.

[0064] S406, perform frequency statistics on the execution modes of the target statement fingerprints in each target fingerprint group, and determine the execution mode with the highest execution frequency as the execution mode to be optimized for each target fingerprint group.

[0065] In one embodiment, the execution patterns of target statement fingerprints within each target fingerprint group can be statistically analyzed to determine the execution patterns to be optimized for each target fingerprint group. Since the execution patterns corresponding to multiple target fingerprint groups may differ, identifying the main execution patterns causing database performance degradation requires grouping multiple target statement fingerprints according to the similarity of their syntactic structures. Target statement fingerprints within the same group have similar syntactic structures, and their corresponding execution patterns also have similar execution logic. Therefore, it is only necessary to statistically analyze the execution frequency of multiple execution patterns with similar logic within the same group to determine the execution pattern most likely to cause database performance degradation in each group. Multiple execution patterns to be optimized corresponding to multiple target fingerprint groups can be used as the final execution patterns to be optimized.

[0066] The technical solution provided in this application grouped multiple target statement fingerprints based on independent structural fingerprints and associated structural fingerprints to obtain multiple target fingerprint groups. Frequency statistics were performed on the execution modes of the target statement fingerprints in each target fingerprint group, and the execution mode with the highest execution frequency was determined as the execution mode to be optimized for each target fingerprint group. This can achieve the purpose of determining the execution mode to be optimized when there are multiple target fingerprints, and improve the accuracy of the determination result of the execution mode to be optimized.

[0067] Figure 5 This is a structural block diagram of a device for determining the optimization pattern of a slow query statement, as provided in an embodiment of this application. Figure 5 As shown, it specifically includes the following: The statement standardization processing module 501 is used to obtain the slow query log of the database, standardize multiple slow query statements in the slow query log, and obtain multiple standard slow query statements. The statement fingerprinting module 502 is used to determine the syntax structure corresponding to each standard slow query statement, and to perform fingerprinting on the syntax structure according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement. The target fingerprint determination module 503 is used to perform performance aggregation processing on the statement fingerprint based on the query parameters corresponding to the standard slow query statement, to obtain the performance parameters of each statement fingerprint, and to determine the statement fingerprint corresponding to the performance parameter that is greater than the preset performance parameter threshold as the target statement fingerprint. The module 504 for determining the execution mode to be optimized is used to perform frequency statistics on the execution modes of the target statement fingerprint and determine the execution mode with the highest execution frequency as the execution mode to be optimized.

[0068] Optional, the statement fingerprinting processing module 502 is specifically used for: Identify multiple independent grammatical levels and multiple related grammatical levels in the grammatical structure. Perform independent fingerprinting on the independent grammatical structure blocks corresponding to each independent grammatical level to obtain independent structure fingerprints. Perform overall fingerprinting on the multiple related grammatical structure blocks corresponding to each related grammatical level to obtain related structure fingerprints. By integrating the independent structure fingerprints and the associated structure fingerprints according to the hierarchical order of each grammatical level in the grammatical structure, the statement fingerprint of each standard slow query statement is obtained.

[0069] Optional, the statement fingerprinting processing module 502 is specifically used for: Perform structural logic parsing on adjacent syntactic blocks in the syntactic structure, and determine the parallel or progressive syntactic blocks associated with each syntactic block in the syntactic structure based on the results of the structural logic parsing. The levels corresponding to parallel grammatical structures are defined as independent grammatical levels, and the levels corresponding to each grammatical structure and the associated progressive grammatical structures as a whole are defined as associated grammatical levels.

[0070] Optionally, the number of target statement fingerprints can be multiple; The module 504 for determining the mode to be optimized is specifically used for: Multiple target statement fingerprints are grouped based on independent structure fingerprints and associated structure fingerprints to obtain multiple target fingerprint groups; Frequency statistics are performed on the execution patterns of the target statement fingerprints in each target fingerprint group, and the execution pattern with the highest execution frequency is identified as the execution pattern to be optimized for each target fingerprint group.

[0071] Optionally, the optimization mode determination module 504 is specifically used for: Multiple target statement fingerprints are initially grouped according to independent structure fingerprints to obtain multiple initial grouping results; Determine the number of associated syntax structure blocks corresponding to the associated structure fingerprint in each initial grouping result, and determine the associated structure fingerprint with the largest number of associated syntax structure blocks as the target associated structure fingerprint; Based on the target association structure fingerprint, the target statement fingerprint in each initial grouping result is finally grouped to obtain multiple target fingerprint groups.

[0072] Optional, statement standardization processing module 501, specifically used for: Identify the non-logical parameters corresponding to multiple slow query statements in the slow query log and filter out the non-logical parameters; The filtered results are formatted to produce multiple standard slow query statements.

[0073] Optional query parameters include query duration and query frequency; The target fingerprint determination module 503 is specifically used for: Identify multiple standard slow query statements corresponding to the same statement fingerprint, and calculate the overall query parameters of the same statement fingerprint based on the query duration and query frequency of each standard slow query statement. By iterating through the overall query parameters of all identical statement fingerprints, the performance parameters of each statement fingerprint can be obtained.

[0074] The technical solution provided in this application includes a statement standardization processing module, used to obtain slow query logs from the database, standardize multiple slow query statements in the slow query logs to obtain multiple standard slow query statements; a statement fingerprinting processing module, used to determine the syntax structure corresponding to each standard slow query statement, and perform fingerprinting processing on the syntax structure according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement; a target fingerprint determination module, used to perform performance aggregation processing on the statement fingerprints based on the query parameters corresponding to the standard slow query statements to obtain the performance parameters of each statement fingerprint, and determine the statement fingerprints corresponding to the performance parameters that are greater than a preset performance parameter threshold as target statement fingerprints; and a mode to be optimized determination module, used to perform frequency statistics on the execution modes of the target statement fingerprints, and determine the execution mode with the highest execution frequency as the execution mode to be optimized. The aforementioned device for determining the optimization patterns of slow query statements solves the problems of low efficiency and inaccurate results in the existing technology for determining the optimization patterns of slow query statements. By fingerprinting the syntactic structure of the statement according to the syntactic hierarchy of the standard slow query statement, the target statement fingerprint is obtained. Frequency statistics are performed on the execution patterns of the target statement fingerprint to determine the optimization patterns. This achieves the purpose of aggregating slow query statements with the same template, reducing the amount of data that needs to be analyzed for slow query statements, improving the efficiency and accuracy of determining the optimization patterns of slow query statements, and helping to improve the timeliness of database performance optimization.

[0075] The device for determining the optimization mode of a slow query statement in this embodiment can be configured in a device, or in a component, integrated circuit, or chip in a terminal. The device can be a mobile electronic device or a non-mobile electronic device. For example, mobile electronic devices can be mobile phones, tablets, laptops, PDAs, in-vehicle electronic devices, wearable devices, ultra-mobile personal computers (UMPCs), netbooks, or personal digital assistants (PDAs), etc., while non-mobile electronic devices can be servers, network-attached storage (NAS), personal computers (PCs), televisions (TVs), ATMs, or self-service machines, etc. This embodiment does not impose specific limitations.

[0076] The device for determining the optimization mode of a slow query statement in this embodiment can be an operating system. The operating system can be Android, iOS, or other possible operating systems; this embodiment does not specifically limit the specific operating system.

[0077] The device for determining the optimization mode of slow query statements provided in this application embodiment can implement the various processes implemented in the above method embodiments, and will not be described again here to avoid repetition.

[0078] like Figure 6 As shown, this application embodiment also provides an electronic device 600, including a processor 601, a memory 602, and a program or instructions stored in the memory 602 and executable on the processor 601. When the program or instructions are executed by the processor 601, they implement the various processes of the above-described method embodiment for determining the optimization mode of a slow query statement and achieve the same technical effect. To avoid repetition, they will not be described again here.

[0079] It should be noted that the electronic devices in the embodiments of this application include the mobile electronic devices and non-mobile electronic devices described above.

[0080] This application also provides a readable storage medium storing a program or instructions. When the program or instructions are executed by a processor, they implement the various processes of the above-described method for determining the optimization mode of a slow query statement and achieve the same technical effect. To avoid repetition, they will not be described again here.

[0081] The processor is the processor in the electronic device described in the above embodiments. The readable storage medium includes computer-readable storage media, such as computer read-only memory (ROM), random access memory (RAM), magnetic disk, or optical disk.

[0082] This application also provides a program product including program code. When the program product is run on a computer device, the program code causes the computer device to perform the steps of the methods described above according to various exemplary embodiments of this application. For example, the computer device can execute a method for determining the optimization pattern of a slow query statement as described in an embodiment of this application. The program product can be implemented using any combination of one or more readable media.

[0083] It should be noted that, in this document, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitations, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes that element. Furthermore, it should be noted that the scope of the methods and apparatuses in the embodiments of this application is not limited to performing functions in the order shown or discussed, but may also include performing functions substantially simultaneously or in the reverse order, depending on the functions involved. For example, the described methods may be performed in a different order than described, and various steps may be added, omitted, or combined. Additionally, features described with reference to certain examples may be combined in other examples.

[0084] Through the above description of the embodiments, those skilled in the art can clearly understand that the methods of the above embodiments can be implemented by means of software plus necessary general-purpose hardware platforms. Of course, they can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, can be embodied in the form of a computer software product. This computer software product is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk) and includes several instructions to cause a terminal (which may be a mobile phone, computer, server, or network device, etc.) to execute the methods described in the various embodiments of this application.

[0085] The embodiments of this application have been described above with reference to the accompanying drawings. However, this application is not limited to the specific embodiments described above. The specific embodiments described above are merely illustrative and not restrictive. Those skilled in the art can make many other forms under the guidance of this application without departing from the spirit and scope of the claims, and all of these forms are within the protection scope of this application.

[0086] The above description is merely a preferred embodiment and the technical principles employed in this application. This application is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions that can be made by those skilled in the art will not depart from the scope of protection of this application. Therefore, although this application has been described in detail through the above embodiments, this application is not limited to the above embodiments, and may include more other equivalent embodiments without departing from the concept of this application, the scope of which is determined by the scope of the claims.

Claims

1. A method for determining the optimization pattern of a slow query statement, characterized in that, The method includes: Obtain the slow query log of the database, and perform standardization processing on multiple slow query statements in the slow query log to obtain multiple standard slow query statements. The standardization processing includes removing variables and spaces from the slow query statements and extracting the logical structure of the slow query statements. The syntax structure corresponding to each standard slow query statement is determined. Structural logic parsing is performed on adjacent syntax blocks within the syntax structure. Based on the parsing results, parallel or progressive syntax blocks associated with each syntax block in the syntax structure are determined. The levels corresponding to the parallel syntax blocks are defined as independent syntax levels, and the levels corresponding to each syntax block and the associated progressive syntax blocks as a whole are defined as associated syntax levels. Independent fingerprinting is performed on the independent syntax blocks corresponding to each independent syntax level to obtain independent structure fingerprints. Multiple associated syntax blocks corresponding to each associated syntax level are fingerprinted as a whole to obtain associated structure fingerprints. The independent structure fingerprints and associated structure fingerprints are integrated according to the hierarchical order of each syntax level in the syntax structure to obtain the statement fingerprint of each standard slow query statement. Based on the query parameters corresponding to the standard slow query statement, the statement fingerprint is subjected to performance aggregation processing to obtain the performance parameters of each statement fingerprint, and the statement fingerprint corresponding to the performance parameter greater than the preset performance parameter threshold is determined as the target statement fingerprint. The execution patterns of the target statement fingerprint are statistically analyzed, and the execution pattern with the highest execution frequency is identified as the execution pattern to be optimized.

2. The method for determining the optimization mode of slow query statements according to claim 1, characterized in that, The number of target statement fingerprints is multiple; The step of performing frequency statistics on the execution patterns of the target statement fingerprint and identifying the execution pattern with the highest execution frequency as the execution pattern to be optimized includes: Based on the independent structural fingerprint and the associated structural fingerprint, multiple target statement fingerprints are grouped to obtain multiple target fingerprint groups; Frequency statistics are performed on the execution modes of the target statement fingerprints in each target fingerprint group, and the execution mode with the highest execution frequency is determined as the execution mode to be optimized for each target fingerprint group.

3. The method for determining the optimization mode of slow query statements according to claim 2, characterized in that, The process of grouping multiple target statement fingerprints based on the independent structural fingerprint and the associated structural fingerprint yields multiple target fingerprint groups, including: Multiple target statement fingerprints are initially grouped according to the independent structure fingerprint, resulting in multiple initial grouping results; Determine the number of associated syntax structure blocks corresponding to the associated structure fingerprint in each of the initial grouping results, and determine the associated structure fingerprint with the largest number of associated syntax structure blocks as the target associated structure fingerprint; Based on the target association structure fingerprint, the target statement fingerprint in each of the initial grouping results is finally grouped to obtain multiple target fingerprint groups.

4. The method for determining the optimization mode of slow query statements according to claim 1, characterized in that, The standardization process is performed on multiple slow query statements in the slow query log to obtain multiple standard slow query statements, including: Identify the non-logical parameters corresponding to multiple slow query statements in the slow query log, and filter the non-logical parameters; The filtered results are formatted to produce multiple standard slow query statements.

5. The method for determining the optimization mode of slow query statements according to claim 1, characterized in that, The query parameters include query duration and query frequency; The performance aggregation process is performed on the statement fingerprint based on the query parameters corresponding to the standard slow query statement to obtain the performance parameters of each statement fingerprint, including: Identify multiple standard slow query statements corresponding to the same statement fingerprint, and calculate the overall query parameters of the same statement fingerprint based on the query duration and query frequency of each standard slow query statement; By iterating through the overall query parameters of all identical statement fingerprints, the performance parameters of each statement fingerprint are obtained.

6. A device for determining the optimization pattern of a slow query statement, characterized in that, The device includes: The statement standardization processing module is used to obtain the slow query log of the database, and to standardize multiple slow query statements in the slow query log to obtain multiple standard slow query statements. The standardization processing includes removing variables and spaces from the slow query statements and extracting the logical structure of the slow query statements. The statement fingerprinting module is used to determine the grammatical structure corresponding to each of the standard slow query statements, perform structural logic parsing on adjacent grammatical structure blocks in the grammatical structure, and determine the parallel or progressive grammatical structure blocks associated with each grammatical structure block based on the structural logic parsing results; determine the level corresponding to the parallel grammatical structure blocks as independent grammatical levels, and determine the level corresponding to each grammatical structure block and the associated progressive grammatical structure block as an associated grammatical level; perform independent fingerprinting on the independent grammatical structure blocks corresponding to each independent grammatical level to obtain independent structure fingerprints, and perform overall fingerprinting on multiple associated grammatical structure blocks corresponding to each associated grammatical level to obtain associated structure fingerprints; and integrate the independent structure fingerprints and the associated structure fingerprints according to the hierarchical order of each grammatical level in the grammatical structure to obtain the statement fingerprint of each of the standard slow query statements. The target fingerprint determination module is used to perform performance aggregation processing on the statement fingerprint based on the query parameters corresponding to the standard slow query statement, to obtain the performance parameters of each statement fingerprint, and to determine the statement fingerprint corresponding to the performance parameter that is greater than a preset performance parameter threshold as the target statement fingerprint. The module for determining the execution mode to be optimized is used to perform frequency statistics on the execution modes of the target statement fingerprint and determine the execution mode with the highest execution frequency as the execution mode to be optimized.

7. An electronic device, characterized in that, The method includes a processor, a memory, and a program or instructions stored in the memory and executable on the processor, wherein the program or instructions, when executed by the processor, implement the steps of a method for determining the optimization pattern of a slow query statement as described in any one of claims 1-5.

8. A readable storage medium, characterized in that, The readable storage medium stores a program or instructions, which, when executed by a processor, implement the steps of the method for determining the optimization mode of a slow query statement as described in any one of claims 1-5.

Images