Method for matching SQL (Structured Query Language) statements

Abstract

The invention relates to the technical field of database statement matching methods, in particular to an SQL statement matching method. According to the technical scheme, the SQL statement matching method comprises a method for performing a capture request on a program side; compared with the traditional SQL statement matching method which is usually used for directly matching based on the text content of the SQL statement, the method provided by the invention has the advantages that the matching precision is limited because the SQL statements with similar semantics but different literal expressions cannot be distinguished due to the direct matching only depending on the text content, and meanwhile, for a large number of or complex SQL requests, the text matching is very time-consuming and the matching efficiency is high. According to the SQL statement matching method, multi-dimensional and probabilistic matching calculation is carried out on the basis of text content of SQL statements and in combination with execution time, client identification, program identification and parameters of the SQL statements, meanwhile, rapid matching is carried out by utilizing hash values, and a statistical matching algorithm is combined, so that the matching efficiency of the SQL statements is improved. The matching efficiency can be improved while the matching precision is ensured.

Description

Technical Field

[0001] The present invention relates to the technical field of database statement matching methods, and in particular to a SQL statement matching method. Background Art

[0002] The SQL statement matching method is a technical means for solving the matching and association between SQL statement execution requests and responses between the program side and the database side.

[0003] Existing SQL statement matching methods usually directly match based on the text content of SQL statements. Since they only rely on direct matching of text content, they may not be able to distinguish SQL statements with similar semantics but different literal expressions, resulting in limited matching accuracy. At the same time, for large or complex SQL requests, text matching may be very time-consuming, affecting system performance.

[0004] In view of the problem that text matching of existing SQL statements may be very time-consuming for a large number or complex SQL requests, affecting system performance, this SQL statement matching method is not only based on the text content of the SQL statement, but also combines the execution time, client identifier, program identifier and SQL statement parameters to perform multi-dimensional and probabilistic matching calculations. At the same time, it also uses hash values ​​for fast matching and combines with statistical matching algorithms to improve matching efficiency while ensuring matching accuracy. Summary of the invention

[0005] In order to overcome the problem that the existing SQL statement matching methods are usually based on direct matching of the text content of the SQL statement, since they only rely on direct matching of the text content, they may not be able to distinguish SQL statements with similar semantics but different literal expressions, resulting in limited matching accuracy. At the same time, for a large number or complex SQL requests, text matching may be very time-consuming, affecting system performance.

[0006] The technical solution of the present invention is: a method for matching SQL statements, comprising:

[0007] S11: The program terminal captures the code's request to the database;

[0008] S12: parse the parameter s1 to be used in the SQL statement in the captured request;

[0009] S13: record the time s2 when the request occurs;

[0010] S14: Record the requested SQL statement and generate a unique Hash value s3 for the SQL statement;

[0011] S15: The database side records the execution time of the SQL statement, the client identifier, and the program identifier d1;

[0012] S16: Generate a unique Hash value d2 from the SQL statement executed on the database side;

[0013] S17: Parsing parameter d3 of the SQL statement on the database side;

[0014] S18: Record the performance indicators d4 of all SQL statement executions;

[0015] S19: Through the statistical matching algorithm, the probability matching calculation is performed between s1, s2, s3 captured by the program end and d1, d2, d3 recorded by the database end;

[0016] S20: The one with the largest matching rate forms an effective association, and finally generates an association result between the program-side SQL statement and the database-side performance indicator d4.

[0017] Preferably, when making a capture request to the program terminal, the following steps are included:

[0018] S21: Capture the request to the database on the program side;

[0019] S22: Listen to and capture database requests from applications.

[0020] Preferably, when parsing SQL parameters, the following steps are included:

[0021] S31: parsing the parameters used by the SQL statement in the captured request;

[0022] S32: Extract the SQL statement from the request and parse out all the used parameters s1.

[0023] Preferably, when recording the request time, the following steps are included:

[0024] S41: Record the specific time when the request occurs;

[0025] S42: Use system time or timestamp to record the time s2 when the request occurs.

[0026] Preferably, when generating the SQL statement Hash, the following steps are included:

[0027] S51: Generate a unique hash value for the requested SQL statement;

[0028] S52: Perform hash processing on the SQL statement to generate a unique identifier s3.

[0029] Preferably, when recording the database execution information, the following steps are included:

[0030] S61: Recording SQL statement execution related information on the database side;

[0031] S62: Record specific execution related information d1.

[0032] Preferably, when generating the SQL Hash on the database side, the following steps are included:

[0033] S71: Generate a unique hash value for the SQL statement executed on the database side;

[0034] 72l: Perform hashing on the SQL statement executed on the database side to generate another unique identifier d2.

[0035] Preferably, when parsing the SQL parameters on the database side, the following steps are included:

[0036] S81: parsing the parameters of the SQL statement on the database side;

[0037] S82: Extract and parse parameter d3 from the SQL statement executed on the database side.

[0038] Preferably, when recording the performance indicators, the following steps are included:

[0039] S91: Recording the performance indicators of SQL statement execution;

[0040] S92: Performance indicators include execution time and resource consumption d4.

[0041] Preferably, when matching and associating the probabilities, the following steps are included:

[0042] S1001: Match and calculate s1, s2, s3 on the program side with d1, d2, d3 on the database side;

[0043] S1002: Calculate the matching rate and find the associated pair with the highest matching rate;

[0044] S1003: According to the matching result, the SQL statement on the program side is associated with the performance indicator d4 executed on the database side.

[0045] Preferably, a more comprehensive and accurate match is achieved by combining the text content, execution time, client identifier, program identifier and parameter information of the SQL statement, and hash values ​​are used for fast matching, which reduces the overhead of direct text comparison and improves matching efficiency. At the same time, accurate matching and performance optimization can reduce user waiting time and improve system response speed and user experience.

[0046] Beneficial effects of the present invention:

[0047] 1. Compared with the traditional SQL statement matching method, which usually directly matches based on the text content of the SQL statement, it may not be able to distinguish SQL statements with similar semantics but different literal expressions due to relying solely on direct matching of the text content, resulting in limited matching accuracy. At the same time, for a large number or complex SQL requests, text matching may be very time-consuming, affecting system performance. The SQL statement matching method is not only based on the text content of the SQL statement, but also combines the execution time, client identifier, program identifier and SQL statement parameters to perform multi-dimensional and probabilistic matching calculations. At the same time, it also uses hash values ​​for fast matching and combines with statistical matching algorithms to improve matching efficiency while ensuring matching accuracy.

[0048] 2. By combining the text content, execution time, client ID, program ID and parameter information of the SQL statement, a more comprehensive and accurate match can be achieved. The hash value is used for fast matching, which reduces the overhead of direct text comparison and improves matching efficiency. At the same time, accurate matching and performance optimization can reduce user waiting time, improve system response speed and user experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] Figure 1 What is shown is a workflow diagram of a matching method for SQL statements of the present invention;

[0050] Figure 2 What is shown is a schematic diagram of a SQL statement matching method program end capturing request flow chart of the present invention;

[0051] Figure 3 What is shown is a schematic diagram of the SQL parameter parsing process of a SQL statement matching method of the present invention. DETAILED DESCRIPTION

[0052] The present invention will be further described below in conjunction with the accompanying drawings and embodiments.

[0053] See also Figure 1-3 The present invention provides an embodiment: a method for matching SQL statements, comprising:

[0054] S11: The program terminal captures the code's request to the database;

[0055] S12: parse the parameter s1 to be used in the SQL statement in the captured request;

[0056] S13: record the time s2 when the request occurs;

[0057] S14: Record the requested SQL statement and generate a unique Hash value s3 for the SQL statement;

[0058] S15: The database side records the execution time of the SQL statement, the client identifier, and the program identifier d1;

[0059] S16: Generate a unique Hash value d2 from the SQL statement executed on the database side;

[0060] S17: Parsing parameter d3 of the SQL statement on the database side;

[0061] S18: Record the performance indicators d4 of all SQL statement executions;

[0062] S19: Through the statistical matching algorithm, the probability matching calculation is performed between s1, s2, s3 captured by the program end and d1, d2, d3 recorded by the database end;

[0063] S20: The one with the largest matching rate forms an effective association, and finally generates an association result between the program-side SQL statement and the database-side performance indicator d4.

[0064] Preferably, when making a capture request to the program terminal, the following steps are included:

[0065] S21: Capture the request to the database on the program side;

[0066] S22: Listen to and capture database requests from applications.

[0067] Preferably, when parsing SQL parameters, the following steps are included:

[0068] S31: parsing the parameters used by the SQL statement in the captured request;

[0069] S32: Extract the SQL statement from the request and parse out all the used parameters s1.

[0070] Preferably, when recording the request time, the following steps are included:

[0071] S41: Record the specific time when the request occurs;

[0072] S42: Use system time or timestamp to record the time s2 when the request occurs.

[0073] Preferably, when generating the SQL statement Hash, the following steps are included:

[0074] S51: Generate a unique hash value for the requested SQL statement;

[0075] S52: Perform hash processing on the SQL statement to generate a unique identifier s3.

[0076] Preferably, when recording the database-side execution information, the following steps are included:

[0077] S61: Recording SQL statement execution related information on the database side;

[0078] S62: Record specific execution related information d1.

[0079] Preferably, when generating the SQL Hash on the database side, the following steps are included:

[0080] S71: Generate a unique hash value for the SQL statement executed on the database side;

[0081] 72l: Perform hashing on the SQL statement executed on the database side to generate another unique identifier d2.

[0082] Preferably, when parsing the SQL parameters on the database side, the following steps are included:

[0083] S81: parsing the parameters of the SQL statement on the database side;

[0084] S82: Extract and parse parameter d3 from the SQL statement executed on the database side.

[0085] Preferably, when recording the performance indicators, the following steps are included:

[0086] S91: Recording the performance indicators of SQL statement execution;

[0087] S92: Performance indicators include execution time and resource consumption d4.

[0088] Preferably, when matching and associating the probabilities, the following steps are included:

[0089] S1001: Match and calculate s1, s2, s3 on the program side with d1, d2, d3 on the database side;

[0090] S1002: Calculate the matching rate and find the associated pair with the highest matching rate;

[0091] S1003: According to the matching result, the SQL statement on the program side is associated with the performance indicator d4 executed on the database side.

[0092] The embodiments of the present invention are described in detail above with reference to the accompanying drawings, but the present invention is not limited to the above embodiments, and various changes can be made within the knowledge scope of those skilled in the art without departing from the spirit of the present invention.

Claims

1. A method for matching SQL statements; characterized in that: Included are: S11: The program terminal captures the code's request to the database; S12: parse the parameter s1 to be used in the SQL statement in the captured request; S13: record the time s2 when the request occurs; S14: Record the requested SQL statement and generate a unique Hash value s3 for the SQL statement; S15: The database side records the execution time of the SQL statement, the client identifier, and the program identifier d1; S16: Generate a unique Hash value d2 from the SQL statement executed on the database side; S17: Parsing parameter d3 of the SQL statement on the database side; S18: Record the performance indicators d4 of all SQL statement executions; S19: Through the statistical matching algorithm, the probability matching calculation is performed between s1, s2, s3 captured by the program end and d1, d2, d3 recorded by the database end; S20: The one with the largest matching rate forms an effective association, and finally generates an association result between the program-side SQL statement and the database-side performance indicator d4.

2. The SQL statement matching method according to claim 1, characterized in that: When making a capture request to the terminal, the following steps are included: S21: Capture the request to the database on the program side; S22: Listen to and capture database requests from applications.

3. The SQL statement matching method according to claim 2, characterized in that: When parsing SQL parameters, the following steps are included: S31: parsing the parameters used by the SQL statement in the captured request; S32: Extract the SQL statement from the request and parse out all the used parameters s1.

4. The SQL statement matching method according to claim 3, characterized in that: When recording the request time, the following steps are included: S41: Record the specific time when the request occurs; S42: Use system time or timestamp to record the time s2 when the request occurs.

5. The SQL statement matching method according to claim 4, characterized in that: When generating the SQL statement Hash, the following steps are included: S51: Generate a unique hash value for the requested SQL statement; S52: Perform hash processing on the SQL statement to generate a unique identifier s3.

6. The SQL statement matching method according to claim 5, characterized in that: When recording the execution information on the database side, the following steps are included: S61: Recording SQL statement execution related information on the database side; S62: Record specific execution related information d1.

7. The SQL statement matching method according to claim 6, characterized in that: When generating SQLHash on the database side, the following steps are included: S71: Generate a unique hash value for the SQL statement executed on the database side; 72l: Perform hashing on the SQL statement executed on the database side to generate another unique identifier d2.

8. The SQL statement matching method according to claim 7, characterized in that: When parsing the SQL parameters on the database side, the following steps are included: S81: Parsing the parameters of the SQL statement on the database side; S82: Extract and parse parameter d3 from the SQL statement executed on the database side.

9. The SQL statement matching method according to claim 8, characterized in that: When recording performance indicators, the following steps are included: S91: Recording the performance indicators of SQL statement execution; S92: Performance indicators include execution time and resource consumption d4.

10. A SQL statement matching method according to claim 9, characterized in that: When matching and associating probabilities, the following steps are included: S1001: Match and calculate s1, s2, s3 on the program side with d1, d2, d3 on the database side; S1002: Calculate the matching rate and find the associated pair with the highest matching rate; S1003: According to the matching result, the SQL statement on the program side is associated with the performance indicator d4 executed on the database side.

Images