Dynamic sql access path risk identification method and device

Abstract

The application discloses a dynamic SQL access path risk identification method and device, relates to the fields of artificial intelligence and intelligent operation and maintenance, and can be applied to the financial field or other fields. The method comprises the following steps: performing parameterization processing on a dynamic structured query statement to obtain a parameterized statement structure and a corresponding statement type; performing access path analysis on the dynamic structured query statement to obtain access path information; determining a historical analysis result corresponding to the parameterized statement structure from a preset target database according to the statement type; comparing the access path information with the historical analysis result to obtain a comparison result; and generating an access path risk identification result according to the comparison result. The application realizes effective management of the access path of the dynamic SQL, accurately identifies the risk SQL without performing actual stress testing, avoids performance risks in a production environment, identifies potential risks without changing an existing production test process, and effectively guarantees stable operation of a production business system.

Description

Technical Field

[0001] This invention relates to the field of SQL technology, and more particularly to a method and apparatus for identifying risks in dynamic SQL access paths. Background Technology

[0002] Currently, lightweight databases, such as MySQL, are increasingly widely used in large organizations, which often have higher requirements for database response time and failure rate. When external conditions such as data volume and table structure change, the database access path may deteriorate, resulting in longer response times and transaction failures. Since most lightweight databases do not support static SQL, it is difficult to effectively manage the access path of dynamic SQL, making it even more difficult to analyze changes in dynamic SQL access paths through static analysis. Summary of the Invention

[0003] To address the problems existing in the prior art, the main objective of this invention is to provide a method and apparatus for identifying risks in dynamic SQL access paths, thereby effectively identifying the risks of dynamic SQL access paths.

[0004] To achieve the above objectives, embodiments of the present invention provide a method for identifying dynamic SQL access path risks, the method comprising:

[0005] The obtained dynamic structured query statement is parameterized to obtain the parameterized statement structure and its corresponding statement type;

[0006] The access path of the dynamic structured query statement is parsed to obtain the access path information corresponding to the dynamic structured query statement;

[0007] Based on the statement type corresponding to the parameterized statement structure, determine the historical parsing results corresponding to the parameterized statement structure from the preset target database;

[0008] The access path information corresponding to the dynamic structured query statement is compared with the historical parsing results to obtain the comparison results, and based on the comparison results, the access path risk identification results are generated.

[0009] Optionally, in one embodiment of the present invention, the method further includes:

[0010] The system searches the preset tool database for the statement type corresponding to the parameterized statement structure and obtains the search results.

[0011] If the search result indicates that the statement type does not exist, the statement type corresponding to the parameterized statement structure will be stored in the tool database.

[0012] Optionally, in one embodiment of the present invention, the obtained dynamic structured query statement is parameterized to obtain a parameterized statement structure and its corresponding statement type, including:

[0013] By replacing variables in a dynamic structured query statement with preset parameters, a parameterized statement structure can be obtained.

[0014] Based on the parameterized statement structure, determine the statement type corresponding to the parameterized statement structure.

[0015] Optionally, in one embodiment of the present invention, the access path information corresponding to the dynamic structured query statement is compared with the historical parsing results to obtain the comparison results, including:

[0016] The access method, number of scanned rows, and additional information in the access path information are compared with the historical parsing results to obtain the comparison results of access method, number of scanned rows, and additional information.

[0017] Optionally, in one embodiment of the present invention, generating access path risk identification results based on the comparison results includes:

[0018] By using preset change rules, the trend of change corresponding to the comparison results is determined;

[0019] Based on the changing trends of the comparison results, the access path risk identification results are generated.

[0020] Optionally, in one embodiment of the present invention, determining the change trend corresponding to the comparison results using preset change rules includes:

[0021] Using preset change rules, determine the change trends corresponding to the comparison results of access methods, the number of rows scanned, and additional information.

[0022] Optionally, in one embodiment of the present invention, generating access path risk identification results based on the changing trend corresponding to the comparison results includes:

[0023] If the trend of the access method comparison results is worsening, then the access path risk identification result is determined to be an access method risk.

[0024] If the trend of the comparison of scanned rows is worsening, then the access path risk identification result is determined to be a scan risk.

[0025] If the trend of change corresponding to the comparison results of additional information is worsening, then the access path risk identification result is determined to be an efficiency risk.

[0026] This invention also provides a dynamic SQL access path risk identification device, the device comprising:

[0027] The parameterization processing module is used to perform parameterization processing on the acquired dynamic structured query statement to obtain the parameterized statement structure and its corresponding statement type;

[0028] The path parsing module is used to parse the access path of dynamic structured query statements to obtain the access path information corresponding to the dynamic structured query statements.

[0029] The statement type module is used to determine the historical parsing results corresponding to the parameterized statement structure from the preset target database based on the statement type corresponding to the parameterized statement structure.

[0030] The identification results module is used to compare the access path information corresponding to the dynamic structured query statement with the historical parsing results, obtain the comparison results, and generate access path risk identification results based on the comparison results.

[0031] Optionally, in one embodiment of the present invention, the apparatus further includes:

[0032] The search results module is used to search for the statement type corresponding to the parameterized statement structure in the preset tool database and obtain the search results;

[0033] The data storage module is used to store the statement type corresponding to the parameterized statement structure in the tool database if the search result indicates that the statement type does not exist.

[0034] Optionally, in one embodiment of the present invention, the parameterization processing module includes:

[0035] The parameterization processing unit is used to replace the variables in the dynamic structured query statement with preset parameters to obtain the parameterized statement structure.

[0036] The statement type unit is used to determine the statement type corresponding to the parameterized statement structure based on the parameterized statement structure.

[0037] Optionally, in one embodiment of the present invention, the identification result module is further used to compare the access method, number of scanned rows, and additional information in the access path information with the historical parsing results to obtain the access method comparison result, the number of scanned rows comparison result, and the additional information comparison result.

[0038] Optionally, in one embodiment of the present invention, the identification result module includes:

[0039] The trend unit is used to determine the trend of change corresponding to the comparison results using preset change rules;

[0040] The identification result unit is used to generate access path risk identification results based on the changing trend corresponding to the comparison results.

[0041] Optionally, in one embodiment of the present invention, the change trend unit is further configured to determine the change trends corresponding to the access method comparison results, the scan line count comparison results, and the additional information comparison results using preset change rules.

[0042] Optionally, in one embodiment of the present invention, the identification result unit includes:

[0043] The access method subunit is used to determine the access path risk identification result as access method risk if the trend of the access method comparison result is worsening.

[0044] The scanning risk sub-unit is used to determine the access path risk identification result as a scanning risk if the trend of the comparison result of the number of scanned rows is worsening.

[0045] The efficiency risk subunit is used to determine the access path risk identification result as an efficiency risk if the trend corresponding to the comparison result of the additional information is worsening.

[0046] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described method.

[0047] The present invention also provides a computer-readable storage medium storing a computer program for performing the above-described methods.

[0048] The present invention also provides a computer program product, including a computer program / instructions, which, when executed by a processor, implement the steps of the above-described method.

[0049] This invention achieves effective management of dynamic SQL access paths by parameterizing dynamic structured query statements and parsing and comparing access paths. Without conducting actual stress tests, it accurately identifies risky SQL statements, avoids performance risks in the production environment, greatly improves test quality, identifies potential risks without changing the existing production testing process, and effectively ensures the stable operation of production business systems. Attached Figure Description

[0050] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0051] Figure 1 This is a flowchart of a dynamic SQL access path risk identification method according to an embodiment of the present invention;

[0052] Figure 2 This is a flowchart illustrating data storage in an embodiment of the present invention;

[0053] Figure 3 This is a flowchart of the parameterization process in an embodiment of the present invention;

[0054] Figure 4 This is a flowchart illustrating the generation of risk identification results in an embodiment of the present invention;

[0055] Figure 5 This is a flowchart illustrating the generation of risk identification results in another embodiment of the present invention;

[0056] Figure 6 This is a schematic diagram of SQL parameterization in a specific embodiment of the present invention;

[0057] Figure 7 This is a schematic diagram of access path parsing in a specific embodiment of the present invention;

[0058] Figure 8 This is a schematic diagram of access path risk identification in a specific embodiment of the present invention;

[0059] Figure 9 This is a schematic diagram of the structure of a dynamic SQL access path risk identification device according to an embodiment of the present invention;

[0060] Figure 10 This is a schematic diagram of the structure of the dynamic SQL access path risk identification device in another embodiment of the present invention;

[0061] Figure 11 This is a schematic diagram of the parameterization processing module in an embodiment of the present invention;

[0062] Figure 12 This is a schematic diagram of the structure of the recognition result module in an embodiment of the present invention;

[0063] Figure 13 This is a schematic diagram of the structure of the recognition result unit in an embodiment of the present invention;

[0064] Figure 14 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0065] This invention provides a method and apparatus for identifying dynamic SQL access path risks, which can be used in the financial field and other fields. It should be noted that the method and apparatus for identifying dynamic SQL access path risks of this invention can be used in the financial field, or in any field other than the financial field. The application field of the method and apparatus for identifying dynamic SQL access path risks of this invention is not limited.

[0066] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0067] like Figure 1 The diagram shows a flowchart of a dynamic SQL access path risk identification method according to an embodiment of the present invention. The execution subject of the dynamic SQL access path risk identification method provided in this embodiment includes, but is not limited to, a computer. This invention achieves effective management of dynamic SQL access paths by parameterizing dynamic structured query statements and parsing and comparing access paths. It accurately identifies risky SQL statements without conducting actual stress tests, avoiding performance risks in the production environment, greatly improving test quality, and identifying potential risks without changing existing production testing processes, thus effectively ensuring the stable operation of production business systems. The method shown in the diagram includes:

[0068] Step S1: Parameterize the obtained dynamic structured query statement to obtain the parameterized statement structure and its corresponding statement type.

[0069] Unlike static structured query statements (SQL) with their fixed format, dynamic SQL is constructed from program logic. The first challenge in managing dynamic SQL lies in identifying the SQL that needs to be managed. While all SQL types in static SQL can be queried in the database, dynamic SQL requires processing and manually organizing a massive amount of SQL that has been executed in the database to obtain a list of SQL to be managed. This invention uses SQL parameterization to deduplicate massive amounts of SQL and then organizes them into a list of SQL to be managed. This eliminates the need for access path parsing of all SQL; each type of SQL only needs to be parsed once, saving resources and time.

[0070] Furthermore, dynamic structured query statements are obtained from databases and other sources, and then parameterized. Specifically, variables in the dynamic structured query statements are replaced with preset parameters, which can be wildcards. This completes the parameterization of the dynamic structured query statements, resulting in a parameterized statement structure.

[0071] Specifically, a parameterized diagram of a dynamic structured query statement is shown below. Figure 6 As shown, this preserves the basic parameterized statement structure of SQL, and the corresponding statement type can be determined from the parameterized statement structure of the dynamic structured query statement. The statement type can include query statements, etc.

[0072] Step S2: Parse the access path of the dynamic structured query statement to obtain the access path information corresponding to the dynamic structured query statement.

[0073] The access path of static SQL is explicitly recorded in the database performance capacity table, while the access path of dynamic SQL needs to be obtained from the database in real time based on the SQL content and recorded in the table. It needs to connect to the database and parse the source of the SQL before parameterization.

[0074] Furthermore, the parameterized SQL is loaded into the target database for access path resolution, and the resulting access path information is stored in the target database. This step does not actually execute the SQL; it only obtains detailed information on how the database optimizer will execute the SQL. Access path information includes the tables accessed by the SQL, the query type, the indexes used, the estimated number of rows scanned, and additional information. Access path information affects the execution efficiency of the SQL, with query type, indexes used, and the estimated number of rows scanned being particularly important indicators.

[0075] Step S3: Based on the statement type corresponding to the parameterized statement structure, determine the historical parsing results corresponding to the parameterized statement structure from the preset target database.

[0076] Specifically, for dynamic structured query statements of the same type, parameterization and access path parsing are performed each time risk identification is conducted. As a result, parameterized query structures of the same type have corresponding historical parsing results recorded in the preset target database. Therefore, the historical parsing results corresponding to the parameterized query structures are determined from the preset target database.

[0077] Step S4: Compare the access path information corresponding to the dynamic structured query statement with the historical parsing results to obtain the comparison results, and generate access path risk identification results based on the comparison results.

[0078] Specifically, the access path information corresponding to the dynamic structured query is compared with the historical parsing results obtained from the target database to obtain the comparison results. The comparison results specifically represent the trend of access path changes.

[0079] Furthermore, using preset change rules, the trend of change represented by the comparison results is determined. The change rules define the order of good and bad access path information. Specifically, for example, if the query type in the access path information changes from "system" to "ALL", and the change rules stipulate that "system" is better than "ALL", then the change trend corresponding to the query type in the access path information is worse.

[0080] Furthermore, after determining the changing trend of the access path information using the comparison results, if the changing trend of the access path information is worsening, the generated access path risk identification result is "risk has occurred." Specifically, for different access path information, a specific type of access path risk identification result is generated, such as access method risk.

[0081] Furthermore, the change rules can also define risk levels corresponding to different change trends, so that the access path risk identification results can also include specific risk levels. When the preset warning level is reached, a warning prompt will be issued for the risk.

[0082] As one embodiment of the present invention, such as Figure 2 As shown, the method also includes:

[0083] Step S21: Search for the statement type corresponding to the parameterized statement structure in the preset tool database to obtain the search results;

[0084] Step S22: If the search result indicates that the statement type does not exist, then the statement type corresponding to the parameterized statement structure is stored in the tool database.

[0085] In this process, after parameterizing the dynamic structured query statement, the statement type is searched in the preset tool database to determine whether the current dynamic structured query statement is the first time it has undergone risk identification processing.

[0086] Furthermore, if the search result indicates that the statement type does not exist, it means that this is the first time the dynamic structured query statement has undergone risk identification processing, and the statement type corresponding to the parameterized statement structure will be stored in the tool database. If the search result indicates that the statement type exists, then no storage is required.

[0087] As one embodiment of the present invention, such as Figure 3 As shown, the obtained dynamic structured query statement is parameterized to obtain the parameterized statement structure and its corresponding statement type, including:

[0088] Step S31: Replace the variables in the dynamic structured query statement with preset parameters to obtain the parameterized statement structure;

[0089] Step S32: Determine the statement type corresponding to the parameterized statement structure based on the parameterized statement structure.

[0090] In this process, variables in the dynamic structured query statement are replaced with preset parameters, which can be wildcards. This completes the parameterization of the dynamic structured query statement and yields the parameterized statement structure.

[0091] Furthermore, such as Figure 6The diagram shows a parameterized representation of a dynamic structured query statement. After parameterization, the basic parameterized statement structure of SQL is preserved, and the corresponding statement type can be determined from the parameterized statement structure of the dynamic structured query statement. The statement type can include query statements, etc.

[0092] As an embodiment of the present invention, the access path information corresponding to the dynamic structured query statement is compared with the historical parsing results to obtain the comparison results, including: comparing the access method, number of rows scanned, and additional information in the access path information with the historical parsing results to obtain the access method comparison results, the number of rows scanned comparison results, and the additional information comparison results.

[0093] The access path information includes the table accessed by the SQL query, the query type, the indexes used, the estimated number of rows scanned, and additional information. Specifically, the query type refers to the method of SQL access, the number of rows scanned refers to how many rows of data the database optimizer estimates the SQL query needs to scan (an increase in this metric is normal), and additional information is a very important column in the access path information, displaying detailed information about the query process.

[0094] Furthermore, the access method, scanned rows, and additional information in the access path information are compared with those in the historical parsing results to obtain comparison results for access method, scanned rows, and additional information. Specifically, the comparison results for access method, scanned rows, and additional information represent changes in each piece of data, such as the query type, i.e., the access method changes from "system" to "ALL".

[0095] In this embodiment, as Figure 4 As shown, based on the comparison results, the generated access path risk identification results include:

[0096] Step S41: Use preset change rules to determine the change trend corresponding to the comparison results;

[0097] Step S42: Based on the changing trends corresponding to the comparison results, generate access path risk identification results.

[0098] Specifically, preset change rules are used to determine the trend of change represented by the comparison results. The change rules define the order of good and bad access path information. For example, if the query type in the access path information changes from "system" to "ALL", and the change rules stipulate that "system" is better than "ALL", then the change trend corresponding to the query type in the access path information is worse.

[0099] In this embodiment, determining the trend of change corresponding to the comparison results using preset change rules includes: determining the trend of change corresponding to the comparison results of access methods, the comparison results of scanned rows, and the comparison results of additional information using preset change rules.

[0100] In this embodiment, as Figure 5 As shown, based on the changing trends corresponding to the comparison results, the generated access path risk identification results include:

[0101] Step S51: If the trend of the access method comparison result is worsening, then the access path risk identification result is determined to be access method risk.

[0102] Step S52: If the trend of the comparison result of the number of scanned rows is worsening, then the access path risk identification result is determined to be a scan risk.

[0103] Step S53: If the trend of change corresponding to the comparison result of additional information is worsening, then the access path risk identification result is determined to be an efficiency risk.

[0104] Specifically, after determining the trend of change corresponding to the access path information using the comparison results, if the trend of change corresponding to the access path information is worsening, the generated access path risk identification result is that a risk has occurred.

[0105] Furthermore, for different access path information, a specific type of access path risk identification result is generated. For example, if the trend of the access method comparison result is worsening, it is identified as an access method risk; if the trend of the scan row count comparison result is worsening, it is identified as a scan risk; if the trend of the additional information comparison result is worsening, it is identified as an efficiency risk.

[0106] Furthermore, the change rules can also define risk levels corresponding to different change trends, so that the access path risk identification results can also include specific risk levels. When the preset warning level is reached, a warning prompt will be issued for the risk.

[0107] The dynamic SQL access path method of this invention can be used to identify and predict the risks of dynamic SQL access paths. Combined with the differences in production and test data volumes, it can provide an accurate list of risky SQL statements without conducting actual stress tests. This allows for early detection of problems, avoidance of performance risks in the production environment, and greatly improves test quality. It provides additional protection without changing the existing production testing process.

[0108] Currently, because the data volume in test environments often differs significantly from that in production environments, simple stress measurements cannot uncover all potential problems, leading to performance issues in the production environment. Static access path risk identification can effectively fill this gap.

[0109] In a specific embodiment of the present invention, a system is provided that applies a dynamic SQL access path risk identification method. This system can effectively manage the access path of dynamic SQL, enabling it to use access path analysis methods similar to static SQL to statically discover hidden problems, identify potential risks, and effectively ensure the stable operation of production business systems.

[0110] In this embodiment, the system includes an SQL parameterization module, an access path parsing module, and a risk identification module.

[0111] Among them, such as Figure 6 The diagram illustrates the parameterization of the SQL parameterization module. Unlike static SQL, which has a fixed format, dynamic SQL is constructed from program logic. The first challenge in managing dynamic SQL lies in identifying the SQL types that need to be managed. While all SQL types in static SQL can be queried in the database, dynamic SQL requires processing and manually organizing a massive amount of SQL that has been executed in the database to obtain a list of SQL types to be managed. This invention uses SQL parameterization to deduplicate massive amounts of SQL and then organize it into a list of SQL types to be managed. This eliminates the need for access path parsing of all SQL types; each type of SQL only needs to be parsed once, saving resources and time.

[0112] Furthermore, "same SQL type" here refers to SQL statements with completely identical parameterized structures. For example, SQL1: "select * from T1 where A = 1 and B = 2"

[0113] SQL2: "select*from T1 where A=3and B=4"

[0114] The parameterized SQL is all "select * from T1 where A = ? and B = ?"

[0115] At this point, SQL1 and SQL2 are considered to be of the same type.

[0116] The specific implementation method of SQL parameterization is to replace variables in SQL with wildcards through the program, while preserving the basic statement structure of SQL.

[0117] Furthermore, the SQL parameterization module is used to deduplicate a large number of repetitive SQL statements in the slow log. Since the identical SQL statements only share the same structure, the actual data contained within them is dynamic. Therefore, the data in the SQL needs to be replaced with parameters, and finally, the parameterized SQL structure is stored as a unique identifier in the tool's database. These parameterized SQL statements will trigger the access path resolution module to resolve access paths. If the parameterized SQL already exists in the database, it does not need to be stored; the access path resolution module is triggered directly.

[0118] Specifically, taking MySQL as an example, when using the SQL parameterization module, the slow log configuration needs to be adjusted. Enable the `log_queries_not_using_indexes` switch; this will include all SQL queries that perform full table scans without using indexes in the slow log. Also, set `long_query_time` to a relatively small value, such as 1 second, to include as many potentially risky SQL queries as possible in the slow log. With this configuration, the amount of SQL queries in the slow log will be relatively large, requiring timely cleanup.

[0119] In this embodiment, as Figure 7 The diagram shows the access path resolution of the access path resolution module. The access path of static SQL is explicitly recorded in the database performance capacity table, while the access path of dynamic SQL needs to be obtained from the database in real time according to the SQL content and recorded in the table. It is necessary to connect to the database and parse it according to the source of the SQL before parameterization.

[0120] Specifically, examples are given below:

[0121] SQL1: "select*from T1 where A=1and B=2"

[0122] SQL2: "select*from T1 where A=3and B=4"

[0123] The parameterized SQL is "select * from T1 where A = ? and B = ?"

[0124] If parsing SQL1 is inefficient, then SQL1 is parameterized, and the parameterized SQL is searched in the parameterized SQL database. If the SQL does not exist in the database, then SQL1 is parsed, and the parameterized SQL is stored in the database. If parsing SQL2 is inefficient, then SQL2 is parameterized, and the parameterized SQL is searched in the parameterized SQL database. If the SQL of this type already exists, it is skipped.

[0125] Furthermore, the access path resolution module is responsible for loading the parameterized SQL into the target database for access path resolution. This step does not actually execute the SQL; it only obtains detailed information on how the database optimizer will execute the SQL. Taking MySQL as an example, `expain + SQL` can obtain the SQL execution plan. This information includes the tables accessed by the SQL, the query type, the indexes used, the estimated number of rows scanned, and additional information. All of this information affects the execution efficiency of the SQL, with the query type, the indexes used, and the estimated number of rows scanned being the most important indicators. If the access path information of a SQL statement changes compared to the previous scan, the comparison and identification module needs to be triggered to determine if the access path has deteriorated. If this is the first time access path resolution is performed for a SQL statement, the above access path information is recorded in the database.

[0126] In this embodiment, as Figure 8 The diagram illustrates the overall risk identification process of the system. The risk identification module determines whether changes in the SQL access path are positive or negative. Unlike static SQL, which uses a fixed access path, dynamic SQL obtains its access path in real-time, which is significantly affected by environmental and resource conditions and exhibits a degree of randomness. Therefore, after determining that the access path has changed, it is necessary to confirm this using other SQL queries of the same type to avoid misjudgment.

[0127] This module compares each access path information. If a particular access path information shows a worsening trend, the SQL query is identified as an access path risk. Specifically, the query type, number of rows scanned, and additional information determine the risk type, and the risk level can be customized according to the degree of change in the access path. The following example using a MySQL database illustrates the criteria for judging access path information.

[0128] Among them, query type: query type refers to the access method of SQL. For example, query type is ALL, which means the worst case, full table scan.

[0129] Furthermore, a query type of "range" indicates a range scan, a restricted index scan. A query type of "eq-ref" indicates a lookup using a unique index or primary key. Specifically, the query type is a crucial indicator, and the default rules include ordering the result values ​​from best to worst, for example:

[0130] system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

[0131] If the query result changes from good to bad, then the SQL query needs to be identified.

[0132] The number of rows scanned refers to how many rows of data the database optimizer estimates the SQL query will need to scan. An increase in this metric is normal. However, if the number of rows scanned increases significantly, such as more than doubling, or exceeds a certain threshold, the SQL query can be identified as risky.

[0133] The "Additional Information" column is a crucial part of the access path information. It displays detailed information about the MySQL query process, providing important supplementary information to the query plan during execution by the MySQL query optimizer. For example, "Using filesort" indicates that the sorting operation in the SQL used memory or a temporary file, while "Using temporary" indicates that the sorting operation used a temporary table, often for larger sorting operations. It is less efficient than "Using filesort" and requires SQL optimization.

[0134] Therefore, through the above three modules, dynamic SQL access path management and risk identification can be achieved, ensuring the stability of the database to the greatest extent.

[0135] This invention specifically relates to identifying critical SQL queries and monitoring changes in SQL access paths, enabling the identification and prevention of SQL queries with performance risks and deteriorating access paths. It can be widely applied in the testing and acceptance processes of business systems in industries such as banking, especially in scenarios particularly sensitive to transaction response time, such as payment and transfer. It can statically identify risk points and provide risk alerts without conducting actual stress tests, ensuring the normal production and operation of the business system.

[0136] like Figure 9 The figure shows a schematic diagram of a dynamic SQL access path risk identification device according to an embodiment of the present invention. The device shown in the figure includes:

[0137] The parameterization processing module 10 is used to perform parameterization processing on the acquired dynamic structured query statement to obtain the parameterized statement structure and its corresponding statement type.

[0138] The path parsing module 20 is used to parse the access path of the dynamic structured query statement to obtain the access path information corresponding to the dynamic structured query statement.

[0139] The statement type module 30 is used to determine the historical parsing results corresponding to the parameterized statement structure from the preset target database based on the statement type corresponding to the parameterized statement structure.

[0140] The identification result module 40 is used to compare the access path information corresponding to the dynamic structured query statement with the historical parsing results, obtain the comparison results, and generate access path risk identification results based on the comparison results.

[0141] As one embodiment of the present invention, such as Figure 10 As shown, the device also includes:

[0142] The search results module 50 is used to search for the statement type corresponding to the parameterized statement structure in the preset tool database and obtain the search results;

[0143] The data storage module 60 is used to store the statement type corresponding to the parameterized statement structure in the tool database if the search result indicates that the statement type does not exist.

[0144] As one embodiment of the present invention, such as Figure 11 As shown, the parameterization processing module 10 includes:

[0145] The parameterization processing unit 11 is used to replace the variables in the dynamic structured query statement with preset parameters to obtain the parameterized statement structure.

[0146] Statement type unit 12 is used to determine the statement type corresponding to the parameterized statement structure based on the parameterized statement structure.

[0147] As an embodiment of the present invention, the identification result module 40 is also used to compare the access method, number of scanned rows, and additional information in the access path information with the historical parsing results to obtain the access method comparison result, the number of scanned rows comparison result, and the additional information comparison result.

[0148] In this embodiment, as Figure 12 As shown, the recognition result module 40 includes:

[0149] The trend unit 41 is used to determine the trend of change corresponding to the comparison results using preset change rules.

[0150] The identification result unit 42 is used to generate access path risk identification results based on the changing trend corresponding to the comparison results.

[0151] In this embodiment, the trend unit 41 is also used to determine the trend of the access method comparison result, the scan line number comparison result and the additional information comparison result by using preset change rules.

[0152] In this embodiment, as Figure 13 As shown, the recognition result unit 42 includes:

[0153] Access method subunit 421 is used to determine the access path risk identification result as access method risk if the trend of the access method comparison result is worsening.

[0154] The scanning risk subunit 422 is used to determine the access path risk identification result as a scanning risk if the trend of the comparison result of the number of scanned rows is worsening.

[0155] The efficiency risk subunit 423 is used to determine the access path risk identification result as an efficiency risk if the trend of change corresponding to the comparison result of the additional information is worsening.

[0156] Based on the same concept as the aforementioned dynamic SQL access path risk identification method, this invention also provides a dynamic SQL access path risk identification device. Since the principle by which this dynamic SQL access path risk identification device solves the problem is similar to that of the dynamic SQL access path risk identification method, the implementation of this dynamic SQL access path risk identification device can refer to the implementation of the dynamic SQL access path risk identification method; repeated details will not be elaborated further.

[0157] This invention achieves effective management of dynamic SQL access paths by parameterizing dynamic structured query statements and parsing and comparing access paths. Without conducting actual stress tests, it accurately identifies risky SQL statements, avoids performance risks in the production environment, greatly improves test quality, identifies potential risks without changing the existing production testing process, and effectively ensures the stable operation of production business systems.

[0158] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the above-described method.

[0159] The present invention also provides a computer program product, including a computer program / instructions, which, when executed by a processor, implement the steps of the above-described method.

[0160] The present invention also provides a computer-readable storage medium storing a computer program for performing the above-described methods.

[0161] like Figure 14 As shown, the electronic device 600 may also include: a communication module 110, an input unit 120, an audio processor 130, a display 160, and a power supply 170. It is worth noting that the electronic device 600 does not necessarily need to include these components. Figure 14 All components shown; in addition, the electronic device 600 may also include Figure 14 For components not shown, please refer to existing technologies.

[0162] like Figure 14 As shown, the central processing unit 100, sometimes also referred to as a controller or operating control, may include a microprocessor or other processor device and / or logic device. The central processing unit 100 receives inputs and controls the operation of various components of the electronic device 600.

[0163] The memory 140 may be, for example, one or more of a cache, flash memory, hard drive, removable media, volatile memory, non-volatile memory, or other suitable devices. It may store the aforementioned failure-related information, and also store a program for executing that information. The central processing unit 100 may execute the program stored in the memory 140 to perform information storage or processing, etc.

[0164] Input unit 120 provides input to central processing unit 100. Input unit 120 may be, for example, a keypad or touch input device. Power supply 170 provides power to electronic device 600. Display 160 displays images and text. Display may be, for example, an LCD display, but is not limited thereto.

[0165] The memory 140 can be a solid-state memory, such as a read-only memory (ROM), random access memory (RAM), a SIM card, etc. It can also be a memory that retains information even when power is off, can be selectively erased, and contains more data; examples of this type of memory are sometimes referred to as EPROMs. The memory 140 can also be some other type of device. The memory 140 includes a buffer memory 141 (sometimes referred to as a buffer). The memory 140 may include an application / function storage unit 142 for storing application programs and function programs or processes for executing the operation of the electronic device 600 via the central processing unit 100.

[0166] The memory 140 may also include a data storage unit 143 for storing data, such as contacts, digital data, pictures, sounds, and / or any other data used by the electronic device. The driver storage unit 144 of the memory 140 may include various drivers for the electronic device's communication functions and / or for performing other functions of the electronic device (such as messaging applications, address book applications, etc.).

[0167] The communication module 110 is a transmitter / receiver 110 that transmits and receives signals via antenna 111. The communication module (transmitter / receiver) 110 is coupled to the central processing unit 100 to provide input signals and receive output signals, which can be the same as in a conventional mobile communication terminal.

[0168] Based on different communication technologies, multiple communication modules 110 can be configured in the same electronic device, such as cellular network modules, Bluetooth modules, and / or wireless LAN modules. The communication module (transmitter / receiver) 110 is also coupled to a speaker 131 and a microphone 132 via an audio processor 130 to provide audio output via the speaker 131 and receive audio input from the microphone 132, thereby enabling typical telecommunications functions. The audio processor 130 may include any suitable buffer, decoder, amplifier, etc. Additionally, the audio processor 130 is coupled to a central processing unit 100, enabling on-device recording via the microphone 132 and on-device playback of stored audio via the speaker 131.

[0169] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0170] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0171] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0172] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0173] Specific embodiments have been used to illustrate the principles and implementation methods of this invention. The descriptions of the embodiments above are only for the purpose of helping to understand the method and core ideas of this invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of this invention. Therefore, the content of this specification should not be construed as a limitation of this invention.

Claims

1. A method for identifying risks in dynamic SQL access paths, characterized in that, The method includes: The obtained dynamic structured query statement is parameterized to obtain the parameterized statement structure and its corresponding statement type; The access path of the dynamic structured query statement is parsed to obtain the access path information corresponding to the dynamic structured query statement; wherein, the process of obtaining the access path information is to obtain detailed information on how the database optimizer will execute the dynamic structured query statement. Based on the statement type corresponding to the parameterized statement structure, the historical parsing results corresponding to the parameterized statement structure are determined from the preset target database; wherein, the historical parsing results are the results generated and recorded in the target database after parameterizing and parsing the dynamic structured query statements of the same statement type during the historical risk identification process. The access path information corresponding to the dynamic structured query statement is compared with the historical parsing results to obtain the comparison results, and an access path risk identification result is generated based on the comparison results. Specifically, the access path information corresponding to the dynamic structured query statement is compared with the historical parsing results to obtain the following comparison results: The access method, number of rows scanned, and additional information in the access path information are compared with the historical parsing results to obtain the access method comparison results, the number of rows scanned comparison results, and the additional information comparison results; wherein, the additional information is supplementary information in the query process; The results of generating access path risk identification based on the comparison results include: Using preset change rules, determine the change trend corresponding to the comparison results; Based on the changing trend corresponding to the comparison results, the access path risk identification results are generated.

2. The method according to claim 1, characterized in that, The method further includes: In the preset tool database, the statement type corresponding to the parameterized statement structure is searched to obtain the search results; If the search result indicates that the statement type does not exist, then the statement type corresponding to the parameterized statement structure is stored in the tool database.

3. The method according to claim 1, characterized in that, The parameterization process performed on the acquired dynamic structured query statement yields the parameterized statement structure and its corresponding statement type, including: The parameterized statement structure is obtained by replacing the variables in the dynamic structured query statement with preset parameters. Based on the parameterized statement structure, determine the statement type corresponding to the parameterized statement structure.

4. The method according to claim 1, characterized in that, The step of determining the trend of change corresponding to the comparison results using preset change rules includes: Using preset change rules, determine the change trends corresponding to the comparison results of access methods, the number of rows scanned, and additional information.

5. The method according to claim 4, characterized in that, Based on the changing trends corresponding to the comparison results, the access path risk identification results are generated as follows: If the trend of the comparison results of the access methods is worsening, then the access path risk identification result is determined to be an access method risk. If the trend of the comparison result of the number of scanned rows is worsening, then the access path risk identification result is determined to be a scan risk. If the trend of change corresponding to the comparison results of the additional information is worsening, then the access path risk identification result is determined to be an efficiency risk.

6. A dynamic SQL access path risk identification device, characterized in that, The device includes: The parameterization processing module is used to perform parameterization processing on the acquired dynamic structured query statement to obtain the parameterized statement structure and its corresponding statement type; The path resolution module is used to parse the access path of the dynamic structured query statement to obtain the access path information corresponding to the dynamic structured query statement; wherein, the process of obtaining the access path information is to obtain detailed information on how the database optimizer will execute the dynamic structured query statement. The statement type module is used to determine the historical parsing results corresponding to the parameterized statement structure from a preset target database based on the statement type corresponding to the parameterized statement structure; wherein, the historical parsing results are the results generated and recorded in the target database after parameterizing and parsing the dynamic structured query statements of the same statement type during the historical risk identification process. The identification result module is used to compare the access path information corresponding to the dynamic structured query statement with the historical parsing results to obtain the comparison results, and generate access path risk identification results based on the comparison results. The identification result module is further configured to compare the access method, number of scanned rows, and additional information in the access path information with the historical parsing results to obtain access method comparison results, number of scanned rows comparison results, and additional information comparison results; wherein, the additional information is supplementary information during the query process. The recognition result module includes: The trend unit is used to determine the trend of change corresponding to the comparison result using preset change rules; The identification result unit is used to generate the access path risk identification result based on the changing trend corresponding to the comparison result.

7. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method according to any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that performs the method according to any one of claims 1 to 5.

9. A computer program product, characterized in that, The computer program product includes a computer program / instruction that, when executed by a processor, implements the method of any one of claims 1 to 5.

Images