A method for high-risk query SQL based on database middleware

Abstract

The present invention discloses a method for high-risk query SQL based on database middleware, specifically including: S1, the middleware receives the query SQL sent by the business program, and determines whether the query SQL is a slow SQL query; S2, statistics the slow SQL query and caches the record; S3, requests to obtain the execution plan of the slow SQL query, and analyzes whether the index is used in the execution plan; S4, analyzes the query conditions of the slow SQL, and optimizes the slow SQL query; the present invention does not require manual participation of development or operation and maintenance personnel in the optimization, each step is automatically implemented, each optimization has a data system basis, and after the optimization is completed, the problem SQL is replayed for verification. For some SQL that cannot be optimized by adding indexes due to business needs, this patented method also makes management and control, on the one hand, it limits the concurrency of the problem SQL, and on the other hand, it forwards the problem SQL to the database slave database to protect the stability of the database master database, thereby achieving the durability of the business system.

Description

Technical Field

[0001] The present invention relates to the technical field of database middleware, and in particular to a method for high-risk query SQL based on database middleware. Background Art

[0002] Existing database middleware primarily implements read-write splitting, database sharding, and high availability. However, it lacks effective management capabilities for high-risk SQL queries generated by business systems. In the era of 5G, AI, and big data, high-concurrency scenarios place significant pressure on databases. If database middleware only manages the business data layer, it's completely inadequate. High-risk SQL queries can cause database downtime, rendering business systems unresponsive and unusable, ultimately negatively impacting these internet companies. We often see internet companies experiencing website or app downtime, emphasizing the importance of intelligently managing high-risk SQL queries within database middleware.

[0003] Therefore, this application is designed based on the above problems. The main purpose is to strengthen the indirect functions in the database, and to intelligently perform statistics, analysis, and optimization management on high-risk query SQL, thereby protecting the company's database and improving the stability of the company's business system. Summary of the Invention

[0004] The purpose of the present invention is to provide a method for high-risk query SQL based on database middleware to solve the problems raised in the above background technology.

[0005] To achieve the above object, the present invention provides the following technical solutions:

[0006] A method for high-risk SQL queries based on database middleware is designed to enhance the functionality of database middleware. This method can perform statistics, analysis, and optimization management on high-risk SQL queries, thereby protecting the company's database and improving the stability of the company's business system. Specifically, the method includes the following steps:

[0007] S1. The middleware receives the query SQL sent by the business program and determines whether the query SQL is a slow SQL query;

[0008] The middleware pre-sets a threshold for slow SQL queries. If the received SQL query duration exceeds this value, it is considered a slow SQL query.

[0009] S2. Collect statistics on slow SQL queries and cache records;

[0010] In S2, the statistical data includes the number of executions of the slow SQL query, the average execution time, the query conditions, and the associated data tables and other data information;

[0011] S3: Request the execution plan for this slow SQL query and analyze whether the execution plan uses the index. If so, proceed to S6; if not, proceed to S4.

[0012] S4. Analyze the query conditions of slow SQL and optimize the slow SQL query;

[0013] Among them, S41, obtain the slow SQL query condition from the information collected in S2, if the slow SQL query condition can be obtained, proceed to the next step, otherwise proceed to S6;

[0014] S42. Perform data sampling and analysis on the SQL field corresponding to the obtained slow SQL query condition, calculate the field repetition rate, and calculate a comparison value based on the total amount of SQL database and the total length of the field;

[0015] S43, comparing the field repetition rate with the comparison value. If the repetition rate is less than the comparison value, proceed to the next step. If the repetition rate is greater than the comparison value, proceed to S6.

[0016] S44. Generate an index using the fields of this SQL query condition, add this index to the database slave, replay the sampling log related to this slow SQL query in the database master, and calculate the average execution time after adding the index.

[0017] S5. Calculate the optimized SQL query efficiency. If the SQL query efficiency is improved, send an optimization completion report. If the SQL query efficiency is not improved, proceed to S6.

[0018] Specifically, to determine whether the efficiency of the optimized SQL query is improved, the average execution time after adding the index in S44 is compared with the threshold. If the average execution time after the index is less than the threshold, it is determined that the efficiency of the slow SQL query is optimized and improved. Otherwise, the process proceeds to S6.

[0019] S6. Determine whether the SQL query requires data timeliness. If so, mark the SQL as a current-limiting SQL, limit the concurrency of the SQL, and send an optimization completion report. If not, mark the SQL as a slave database SQL, send the SQL request to the slave database, and send an optimization completion report.

[0020] Specifically, it includes the following two processing results:

[0021] (1) After being marked as a current-limiting SQL, this current-limiting SQL is recorded in the middleware cache and the concurrency of this current-limiting SQL is limited to protect the backend database master database;

[0022] (2) After being marked as a slave SQL, the slave SQL is recorded in the middleware cache, and each subsequent SQL query requested by the business that is the same as the slave SQL is forwarded to the database slave.

[0023] In summary, the beneficial effects of the present invention are as follows:

[0024] (1) In the present invention, statistics are first collected on this SQL, including the number of times the database is requested through the middleware, the average execution time, the query conditions, the associated data tables, and other information. Then, the execution plan of this SQL is obtained from the database through a program. The situation in which the index is not used in the execution plan is further analyzed to analyze the query conditions of the SQL (detailed step 5), and the program is used to add intelligent analysis and matching indexes to optimize the SQL. For situations where there are no query conditions or the SQL is still slow even when using an index, on the one hand, the concurrency of this SQL is limited (detailed steps 13 and 14), and on the other hand, this SQL is routed to the database slave library for execution, thereby intelligently managing this slow SQL. Adding an index can optimize the execution efficiency of this SQL so that it is not a slow SQL. Intelligent management of the concurrency and routing of SQL is to prevent the main library from being attacked by slow SQL when index optimization cannot be used, ultimately protecting the performance of the database master library and ensuring the stability of the production system.

[0025] (2) The present invention does not require manual optimization by development or operation and maintenance personnel. Each step is automated and each optimization is based on data statistics. After the optimization is completed, the problem SQL is replayed and verified. For some SQLs that cannot be optimized by adding indexes due to business needs, this method also provides management and control. On the one hand, it limits the concurrency of the problem SQL, and on the other hand, it forwards the problem SQL to the database slave to protect the stability of the database master, thereby achieving the durability of the business system. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following is a brief introduction to the drawings required for use in the embodiments. It should be understood that the following drawings only illustrate certain embodiments of the present invention and therefore should not be regarded as limiting the scope of protection. For those skilled in the art, other relevant drawings can be obtained based on these drawings without paying any creative work.

[0027] Figure 1 Schematic diagram of the system composition of the present invention;

[0028] Figure 2 Schematic diagram of the workflow of the present invention. DETAILED DESCRIPTION

[0029] To make the purpose, technical solutions, and advantages of this application more clear, the present invention is further described below with reference to the accompanying drawings and examples. Implementation methods of the present invention include, but are not limited to, the following examples. All other embodiments obtained by persons of ordinary skill in the art based on the examples in this application without creative effort are within the scope of protection of this application.

[0030] Example

[0031] like Figure 1-2 As shown, this embodiment provides a method for high-risk query SQL based on database middleware. The main purpose of the method is to enhance the function of the database middleware, and to perform statistics, analysis, and optimization management on high-risk query SQL, thereby protecting the company's database and improving the stability of the company's business system. Specifically, the method includes the following steps:

[0032] S1. The middleware receives the query SQL sent by the business program and determines whether the query SQL is a slow SQL query;

[0033] Set a slow SQL time threshold in the middleware in advance. If the execution time of the query SQL sent by the business program exceeds this value, it is considered a slow query.

[0034] S2. Collect statistics on slow SQL queries and cache records;

[0035] Mainly count the number of executions of this slow SQL query, average execution time, query conditions, and related data tables, and record them in the cache;

[0036] S3: Request the execution plan for this slow SQL query and analyze whether the execution plan uses the index. If so, proceed to S6; if not, proceed to S4.

[0037] Request the database to obtain the execution plan for this slow SQL query, and use the execution plan to determine whether this SQL has used index query;

[0038] S4. Analyze the query conditions of slow SQL and optimize the slow SQL query;

[0039] The specific steps for optimizing the SQL query method are as follows:

[0040] S41: Determine whether the slow SQL query condition can be obtained from the information collected in S2. If so, proceed to the next step; otherwise, proceed to S6.

[0041] S42. Perform data sampling and analysis on the SQL field corresponding to the query condition in S41, and calculate the field repetition rate. Specifically, according to this embodiment, a certain sampling rate is used to sample several values (query parameters) in this field, and then these values are aggregated and statistically analyzed to calculate the repetition rate of these values in this field. A comparison value is then calculated based on the total amount of SQL database and the total length of the field.

[0042] S43. Compare the repetition rate with the comparison value. If the repetition rate is less than the comparison value, generate an index using the field of the SQL query condition. Add this index to the database slave. Replay the sampling log related to the slow SQL query in the database master. Calculate the average execution time after adding the index. If the repetition rate is greater than the comparison value, proceed to S6.

[0043] S5. Calculate the optimized SQL query efficiency. If the SQL query efficiency is improved, send an optimization completion report. If the SQL query efficiency is not improved, proceed to S6.

[0044] Among them, whether the SQL query efficiency is improved is mainly determined by whether the average execution time after adding the index in S43 is lower than the threshold in S2. If it is lower than the threshold, it indicates that the SQL query efficiency has been improved.

[0045] S6. Determine whether the SQL query requires data timeliness. If so, mark the SQL as a current-limiting SQL, limit the concurrency of the SQL, and send an optimization completion report. If not, mark the SQL as a slave database SQL, send the SQL request to the slave database, and send an optimization completion report.

[0046] Specifically, it includes the following two processing results:

[0047] (1) The program will generate a metadata table in the middleware component. This table records the data that the business cannot query from the slave database. It will determine whether the table associated with the execution plan in S2 appears in this metadata table. If it does, the program will generate a metadata table in the middleware component, record this slow SQL query in this table, and mark it as a current-limiting SQL. This will record this SQL in the middleware cache, limit the concurrency of this slow SQL query, and thus protect the backend database master database;

[0048] (2) If it does not appear, the program will generate a metadata table in the middleware component, record this slow SQL query in this table, mark it as a slave SQL query, and record this slave SQL query in the middleware cache to forward the same SQL to the database slave for each subsequent business request.

[0049] The above embodiments are only preferred embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any changes that adopt the design principles of the present invention and any changes made through non-creative work on this basis should fall within the scope of protection of the present invention.

Claims

1. A method for high-risk query SQL based on database middleware, characterized in that: The following steps are involved: S1. The middleware receives the query SQL sent by the business program and determines whether the query SQL is a slow SQL query; S2. Collect statistics on slow SQL queries and cache records; S3: Request the execution plan for this slow SQL query and analyze whether the execution plan uses the index. If so, proceed to S6; if not, proceed to S4. S4. Analyze the query conditions of slow SQL and optimize the slow SQL query; S5. Calculate the optimized SQL query efficiency and determine whether the optimized SQL query efficiency is improved. If the SQL query efficiency is improved, send an optimization completion report; if the SQL query efficiency is not improved, proceed to S6. S6. Determine whether the SQL query requires data timeliness. If so, mark the SQL as a current-limiting SQL, limit the concurrency of the SQL, and send an optimization completion report. If not, mark the SQL as a slave database SQL, send the SQL request to the slave database, and send an optimization completion report. In S4, the specific steps of optimizing the SQL query method are: S41. Obtain the slow SQL query condition from the information collected in S2. If the slow SQL query condition can be obtained, proceed to the next step; otherwise, proceed to S6. S42. Perform data sampling and analysis on the SQL field corresponding to the obtained slow SQL query condition, calculate the field repetition rate, and calculate a comparison value based on the total amount of SQL database and the total length of the field; S43, comparing the field repetition rate with the comparison value. If the repetition rate is less than the comparison value, proceed to the next step. If the repetition rate is greater than the comparison value, proceed to S6. S44. Generate an index using the fields of this SQL query condition, add this index to the database slave, replay the sampling log related to this slow SQL query in the database master, and calculate the average execution time after adding the index.

2. A method for high-risk query SQL based on database middleware according to claim 1, characterized in that: In S2, the statistical data includes the number of executions of the slow SQL query, the average execution time, the query conditions, and the data information of the associated data tables.

Images