Method, system and equipment for grabbing problem SQL (Structured Query Language) and medium

Abstract

The invention relates to the field of database processing, and provides a method, a system, equipment and a medium for capturing a problem SQL (Structured Query Language), and the method comprises the following steps: acquiring environment information of a to-be-processed database, and registering the environment information into a preset data source management component; monitoring all databases through the preset data source management component, and capturing all executed SQL statements; the SQL statement is evaluated, and a problem SQL is identified and exported; and comparing the question SQL with a question in a preset BUG library, and performing registration. According to the method, the SQL slow in response in the environment running process is obtained, so that the organization is helped to find the potential SQL slow in response in the product under the condition that the test investment is not additionally increased, namely, the potential performance problem in the product is found, the test coverage is improved, the product performance quality is finally promoted to be improved, the product problem detection rate is increased, and the test efficiency is improved. And the test coverage of the product to various environments is improved.

Description

Technical Field

[0001] The present invention relates to the field of database processing, and in particular to a method, system, device and medium for capturing problematic SQL statements. Background Art

[0002] As the types of databases supported by software products have evolved from the original mainstream Oracle and SqlServer databases to more than a dozen databases such as Oracle, SqlServer, Mysql, PG, DM, HighGo, Kingbase, ShenTong, Gauss, and OceanBase. Servers have also expanded from the X86 architecture to X86, ARM, MIPS, and Alpha, with major manufacturers including Zhaoxin, Haiguang, Kunpeng, Feiteng, Loongson, and Shenwei.

[0003] While the product's support for XC devices, operating systems, and databases provides a competitive advantage, it also multiplies the testing workload. Especially for some complex SQL statements, their performance is relatively poor on domestic databases and domestic servers. Figuring out how to expose these issues has become a difficult problem in testing.

[0004] According to traditional practices, to ensure product quality when adding support for each additional database, corresponding tests need to be added. Both functional testing and performance testing require investment. Currently, automated testing techniques can replace manual inspection for functional testing, but there are no relevant automated tools for measuring the performance of the product and the speed of functional response time. To identify points with slow response in the product, currently, only manual testing can be relied on. By clicking on the business function process at the front end and using a packet capture tool to capture relevant data for testing. With more than a dozen databases and different server combinations, the number of test environments can reach dozens. It is extremely difficult to cover all tests, and it will also result in a significant increase in operating costs. Summary of the Invention

[0005] Based on the above objectives, the present invention proposes a method for capturing problematic SQL statements, including:

[0006] Obtain the environmental information of the database to be processed and register the environmental information into a preset data source management component;

[0007] Monitor all databases through the preset data source management component and capture all executed SQL statements;

[0008] Evaluate the SQL statements, identify and export problematic SQL statements;

[0009] Compare the problematic SQL statements with the problems in a preset BUG library and register them.

[0010] In some embodiments, the steps of obtaining the environment information of the database to be processed and registering the environment information into a preset data source management component include:

[0011] Log in to the database to be processed using a database management tool;

[0012] Execute an SQL query statement and record the environment information from the query results;

[0013] Select a preset data source management component and its configuration method to create a data source configuration;

[0014] Fill in the environment information into the data source configuration and perform a test. When the test result is successful, the registration is completed.

[0015] In some embodiments, the steps of monitoring all databases through the preset data source management component and capturing all executed SQL statements include:

[0016] Monitor the activities of the database through the preset data source management component;

[0017] Capture the currently executed and historical SQL statements and the corresponding execution statistics;

[0018] Query the monitoring regularly and record the query results in a monitoring table.

[0019] In some embodiments, the steps of evaluating the SQL statements, identifying and exporting problematic SQL include:

[0020] Evaluate and classify the SQL statements through preset rules to obtain an evaluation classification result;

[0021] Process the evaluation classification result through preset filtering conditions, identify the SQL statements that meet the filtering conditions, and mark them as problematic SQL;

[0022] Export the problematic SQL.

[0023] In some embodiments, the preset rules include one or more of the single average execution time being greater than 1 second, the total execution time being greater than a preset time, the number of executions being greater than a preset number, and the IO consumption being greater than a preset threshold. The preset filtering conditions include one or more of syntax errors, logical errors, and performance issues.

[0024] In some embodiments, the steps of comparing the problematic SQL with the problems in a preset BUG library and registering them include:

[0025] Obtain a preset BUG library containing the historical records of the problem SQL, and compare the historical records of the problem SQL in the preset BUG library;

[0026] Execute a fuzzy matching function in the BUG library to search for records similar to the problem SQL;

[0027] In response to not finding similar records, create a new record in the BUG library based on the problem SQL for registration;

[0028] In response to finding similar records, associate the problem SQL with the similar records for registration.

[0029] In some embodiments, the method further includes:

[0030] Obtain the TOP SQL of the currently selected data source and display it in the form expected by the user on the front end.

[0031] The present invention provides a system for capturing problem SQL, including:

[0032] An acquisition unit configured to obtain the environment information of the database to be processed and register the environment information in a preset data source management component;

[0033] A capture unit configured to monitor all databases through the preset data source management component and capture all executed SQL statements.

[0034] An identification unit configured to evaluate the SQL statements, identify and export problem SQL;

[0035] A registration unit configured to compare the problem SQL with the problems in a preset BUG library and perform registration.

[0036] The present invention provides a computer device, including:

[0037] At least one processor; and a memory, where the memory stores a computer program that can run on the processor, and when the processor executes the program, it performs the steps of the method for capturing problem SQL.

[0038] The present invention provides a computer-readable storage medium, where the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, it performs the steps of the method for capturing problem SQL.

[0039] The present invention has at least the following beneficial technical effects:

[0040] The present invention provides a method, system, device and medium for capturing problematic SQL statements. The method includes: obtaining the environmental information of the database to be processed and registering the environmental information into a preset data source management component; monitoring all databases through the preset data source management component and capturing all executed SQL statements; evaluating the SQL statements, identifying and exporting the problematic SQL statements; comparing the problematic SQL statements with the problems in a preset BUG library and registering them.

[0041] The present invention captures SQL statements with slow responses during the operation of the environment, which helps an organization discover potential SQL statements with slow responses in different hardware devices and different database type environments in the product without additional test investment, that is, discover potential performance problems of the product in different environments, so as to improve the test coverage and ultimately promote the improvement of the product performance quality. By automatically capturing problematic SQL statements during the operation of the product in the background, it breaks through the traditional front-end and black-box testing methods, discovers deep-seated performance hidden dangers of the product, improves the problem detection rate of the product, and improves the test coverage of the product for various environments. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for the description of the embodiments or the prior art. Obviously, the following drawings are only some embodiments of the present invention. For those of ordinary skill in the art, other embodiments can be obtained based on these drawings without creative efforts.

[0043] Figure 1 It is a flowchart of a method for capturing problematic SQL statements provided by the present invention;

[0044] Figure 2 It is a system module diagram of a method for capturing problematic SQL statements provided by the present invention;

[0045] Figure 3 It is a management and maintenance diagram in an embodiment of a method for capturing problematic SQL statements provided by the present invention;

[0046] Figure 4 It is a SQL details diagram in an embodiment of a method for capturing problematic SQL statements provided by the present invention;

[0047] Figure 5 It is a display database diagram in an embodiment of a method for capturing problematic SQL statements provided by the present invention;

[0048] Figure 6 It is a flowchart in an embodiment of a method for capturing problematic SQL statements provided by the present invention;

[0049] Figure 7Schematic diagram of a structure of an embodiment of the computer device provided by the present invention;

[0050] Figure 8 Schematic diagram of a structure of an embodiment of the computer-readable storage medium provided by the present invention. Detailed implementation manners

[0051] To make the objectives, technical solutions and advantages of the present invention clearer and more understandable, the following further describes the embodiments of the present invention in detail with reference to specific embodiments and the accompanying drawings.

[0052] It should be noted that all the expressions using "first" and "second" in the embodiments of the present invention are used to distinguish two entities or parameters with the same name but different identities. It can be seen that "first" and "second" are only for the convenience of expression and should not be construed as a limitation on the embodiments of the present invention. This will not be elaborated one by one in the subsequent embodiments.

[0053] The present invention provides a method for capturing problematic SQL. Please refer to Figure 1 , including:

[0054] S1: Obtain the environment information of the database to be processed and register the environment information in a preset data source management component.

[0055] In step S1, specifically,

[0056] 1. Log in to the database and obtain the environment information

[0057] Use a database management tool (such as MySQL command line client, phpMyAdmin or programming interfaces such as JDBC, ODBC, etc.) to log in to the database to be processed.

[0058] Execute an SQL query statement to extract detailed environment information from the database, including but not limited to:

[0059] Database version

[0060] Character set setting

[0061] Storage engine type

[0062] Connection parameters (such as maximum connection number, timeout time, etc.)

[0063] Connection-related information such as database address, port number, service name, username, and password.

[0064] 2. Select and configure the data source management component

[0065] Confirm the preset data source management component (such as ODBC data source manager, JDBC connection pool configuration) and its configuration method.

[0066] Create or edit a data source configuration file according to the requirements of the data source management component. For example:

[0067] Fill in the database connection information (IP address, port, database name, username, password, etc.).

[0068] Add other necessary connection parameters to ensure they match the actual environment.

[0069] 3. Test the connection and complete the registration

[0070] After completing the data source configuration, conduct a connection test to verify whether the configuration is correct.

[0071] If the test fails, adjust the configuration according to the error message and retest.

[0072] Once the test is successful, save the data source configuration and officially register it in the data source management component.

[0073] 4. Support multiple database types

[0074] The data source management component can uniformly manage different types of databases, including but not limited to Oracle, SqlServer, Mysql, PG, DM, HighGo, Kingbase, ShenTong, Gauss, OceanBase, etc.

[0075] This multi-database support capability provides flexibility and wide applicability for subsequent monitoring and problem SQL capture.

[0076] Centralize and register all the database environment information that needs to be monitored in the data source management component, which is convenient for unified management and maintenance, and avoids the chaos and inefficiency caused by decentralized management. There is no need to repeatedly connect to each database and execute data query operations. Through the pre-configured data source management component, the target database can be quickly accessed, greatly improving work efficiency. The automated data source registration process reduces the need for manual intervention, reduces the probability of problems caused by human errors, and saves a large amount of human resources at the same time. Supporting multiple database types can adapt to different business scenarios and technical requirements, laying a foundation for the scalability and future upgrade of the system. Through standardized configuration processes and testing mechanisms, ensure that all registered database environment information is accurate, thus guaranteeing the smooth progress of subsequent monitoring and problem SQL capture work. Incorporate all the databases involved in daily functional testing, automated testing, and performance testing environments into the monitoring scope, which helps to discover potential performance problems of the product in various environments and improve the test coverage.

[0077] S2: Monitor all databases through the preset data source management component and capture all executed SQL statements.

[0078] In step S2, specifically,

[0079] 1. Enable database activity monitoring

[0080] The data source management component captures database activities in real time through built-in monitoring tools or dynamic management views (such as the performance monitoring function provided by the database management system).

[0081] These activities include the currently executing SQL statements and the historically executed SQL statements.

[0082] 2. Configure query logs or slow query logs

[0083] Enable the query log or slow query log function at the database level to record all executed SQL statements or only the SQL statements whose execution time exceeds a specific threshold.

[0084] Ensure that the log files are properly managed and backed up to prevent excessive disk space occupation.

[0085] 3. Use third-party monitoring tools

[0086] If the built-in monitoring function of the database is insufficient to meet the requirements, third-party monitoring tools (such as Prometheus, Grafana, etc.) can be introduced to capture and analyze database activities.

[0087] Regularly analyze the captured SQL statements and record the results in the monitoring table for subsequent processing.

[0088] 4. Capture SQL statements and statistical information

[0089] Capture not only the SQL statements themselves but also their execution statistical information, such as:

[0090] Execution time

[0091] Call count

[0092] Memory consumption

[0093] IO operation volume

[0094] These statistical information provide important basis for subsequent evaluation of SQL performance.

[0095] 5. Support multiple database types

[0096] The data source management component can uniformly monitor multiple types of databases (such as Oracle, SqlServer, Mysql, PG, DM, Highgo, Kingbase, Shentong, etc.), ensuring that SQL statements in different database environments can be effectively captured.

[0097] By monitoring all databases in real time, it is ensured that no executed SQL statements are missed, thus achieving comprehensive coverage of database activities. After capturing SQL statements and their statistics, it is possible to quickly identify problematic SQL statements with long execution times and high resource consumption, and then discover potential performance hazards in the product. The automated SQL statement capture process avoids manual intervention, significantly improves test efficiency, and reduces the risk of errors caused by manual operations. Without additional test investment, performance test coverage of various environments is completed through automated means, greatly saving resource costs such as manpower and equipment. By continuously monitoring and capturing SQL statements, problems can be discovered and solved at the initial stage, avoiding system crashes or degraded user experiences due to performance issues. The ability to uniformly monitor multiple database types enables this method to adapt to different business scenarios and technical requirements, enhancing the compatibility and scalability of the system.

[0098] S3: Evaluate the SQL statements, identify and export problematic SQL.

[0099] Specifically, in step S3,

[0100] 1. Define evaluation rules

[0101] According to business requirements and performance standards, a series of rules are preset to evaluate the execution of SQL statements. Common evaluation rules include but are not limited to:

[0102] The average execution time per single time is greater than 1 second.

[0103] The total execution time exceeds a preset threshold (such as 5 seconds).

[0104] The number of executions is too high (such as more than 100 times per minute).

[0105] The IO consumption is too high (such as the amount of read and write operations exceeds a certain threshold).

[0106] The memory occupancy is abnormal.

[0107] 2. Classification and sorting

[0108] Classify the captured SQL statements according to the above rules and sort them according to indicators such as execution time, memory consumption, and IO operation volume to screen out the SQL statements that require the most attention.

[0109] 3. Apply filtering conditions

[0110] Use the preset filtering conditions to further process the evaluation results and identify problematic SQL that meet specific conditions. These filtering conditions may include:

[0111] Syntax error: Check whether there are spelling mistakes, unmatched brackets, etc. in the SQL statement.

[0112] Logical error: Verify whether the table name and column names are correct, whether the WHERE condition is reasonable, and whether the data types match, etc.

[0113] Performance issue: Analyze the query plan through the EXPLAIN or ANALYZE command to find problems such as improper index usage and low join efficiency.

[0114] 4. Mark the problematic SQL

[0115] Mark the SQL statements that meet the filtering conditions as "problematic SQL" for subsequent processing. The marking information can include the problem type (such as syntax error, logical error, performance issue), occurrence frequency, context environment, etc.

[0116] 5. Export the problematic SQL

[0117] Export the identified problematic SQL to a file or store it in a dedicated data table for further analysis and optimization by testers or developers.

[0118] 6. Support multiple database types

[0119] For different types of databases (such as Oracle, SqlServer, Mysql, PG, DM, HighGo, Kingbase, ShenTong, etc.), adopt adapted evaluation rules and filtering conditions to ensure the accuracy and comprehensiveness of the identification of problematic SQL.

[0120] Through the multi-dimensional evaluation and filtering of SQL statements, potential problematic SQL can be accurately identified, avoiding false positives or false negatives. The exported problematic SQL can directly point to the performance bottleneck in the system, helping developers quickly locate problems and optimize them, significantly improving the performance optimization efficiency. The automated evaluation and identification process reduces the dependence on manual experience, reduces the risk caused by human judgment errors, and saves a large amount of labor costs at the same time. By regularly evaluating and identifying problematic SQL, problems can be discovered and solved in the initial stage, effectively avoiding potential performance failures or crashes during system operation. By comprehensively evaluating all captured SQL statements, potential performance hidden dangers of the product in various environments can be discovered, greatly improving the test coverage. Providing adapted evaluation rules and filtering conditions for different types of databases enables this method to adapt to complex business scenarios and technical requirements, enhancing the compatibility and scalability of the system. The regularly exported problematic SQL can be accumulated as historical data, providing an important reference basis for subsequent product optimization and performance improvement, forming a virtuous cycle.

[0121] S4: Compare the problematic SQL with the problems in the preset BUG library and register them.

[0122] In step S4, specifically,

[0123] 1. Obtain the preset BUG library

[0124] Ensure that the preset BUG library exists and is available. This library contains the problem SQLs in the historical records and their detailed information, such as problem descriptions, error types, occurrence frequencies, repair solutions, etc.

[0125] The structure of the BUG library should be clear for quick querying and updating.

[0126] 2. Perform a fuzzy matching search

[0127] Execute a query operation in the BUG library to search for records similar to the current problem SQL. SQL's fuzzy matching functions (such as LIKE or REGEXP) can be used to find records containing similar patterns or keywords.

[0128] Compare the core logic, structure, keywords, etc. of the problem SQL, rather than just a complete text match.

[0129] 3. Conduct a detailed comparison and analysis

[0130] Conduct a detailed comparison of the similar records found, including comparisons in aspects such as problem descriptions, error types, and context environments.

[0131] Confirm whether the problem SQL matches a known problem in the BUG library or is a new variant of a problem.

[0132] 4. Register a new problem

[0133] If the problem SQL does not match any record in the BUG library, register it as a new problem in the BUG library. The following information needs to be filled in:

[0134] The problem SQL statement

[0135] The problem description

[0136] The error type

[0137] The discovery time

[0138] The occurrence context environment

[0139] If the problem SQL matches a known problem or is a variant of a known problem, associate this new instance with the known problem and update the record of the known problem, adding information such as the occurrence frequency and relevant environment of the problem.

[0140] 5. Classify and label the problem

[0141] Classify and label the problems according to factors such as the nature, severity, and scope of influence of the problems. This helps with subsequent problem management and prioritization.

[0142] 6. Regularly maintain the BUG library

[0143] Regularly review and update the BUG library, delete outdated or no longer relevant problem records, and ensure the accuracy and timeliness of the BUG library.

[0144] 7. Support diverse database environments

[0145] For different types of databases (such as Oracle, SqlServer, Mysql, PG, DM, Highgo, Kingbase, Shentong, etc.), ensure that the BUG library can adapt to and record the problem SQLs from various databases.

[0146] By comparing with the existing problems in the BUG library, it is possible to effectively avoid registering the same or similar problems repeatedly, reducing redundant work. Associating the problem SQLs with the records in the BUG library facilitates developers and testers to track the historical records and repair progress of the problems. Continuously update the BUG library, accumulate historical data, and provide important reference bases for subsequent product optimization and performance improvement. If the problem SQL matches a known problem, the existing solution can be directly reused, thus accelerating the problem-solving speed. The automated comparison and registration process reduces the need for manual intervention, reduces the risks caused by human errors, and saves a large amount of labor costs at the same time. Through continuous tracking and management of the problem SQLs, problems can be discovered and solved in a timely manner at the initial stage of their occurrence, avoiding potential performance failures or crashes during the system operation. Providing adapted comparison rules for different types of databases enables this method to adapt to complex business scenarios and technical requirements, enhancing the compatibility and scalability of the system. By continuously updating and improving the BUG library, a virtuous cycle is formed, promoting the continuous improvement and performance optimization of the product.

[0147] Using the present invention, without increasing the test and manual input, automated means can be used to automatically collect slow SQLs in the background of various test environments such as daily functional tests, automated tests in different database and different server combination environments, performance tests, and SQLs with abnormal resource occupation. Without conducting special tests, it can achieve the effect equivalent to carrying out tests, complete the performance test coverage of various environments, find slow-response and resource-occupation-abnormal SQLs by automatically capturing the operation data of other environments, discover potential performance problems of the product without additional test input, complete the performance test coverage of the product for different environments, improve the efficiency of problem discovery in testing, and effectively ensure the performance quality of the company's products.

[0148] By capturing and analyzing problem SQL, potential performance hidden trouble problems of the product are discovered from the database side, breaking through the traditional front-end and box testing methods that can only find that a certain function is slow. The discovered problems are more comprehensive and in-depth. Solving the problems in the budding stage avoids performance quality accidents after going live.

[0149] By monitoring and capturing daily internal functions, performance, and automated test environments, potential problems of slow response and abnormal resource occupation of the product in various environments are discovered, so as to improve the test coverage, effectively guarantee the product performance quality, reduce the R & D cost investment, and save a large amount of manpower and material resources. For each additional environment covered, a corresponding amount of resource investment can be saved. The more environments, the greater the benefits obtained.

[0150] Problems SQL of multiple data sources and different database types can be centrally managed and obtained, without repeatedly connecting to the database and performing data query operations, greatly improving work efficiency and saving labor costs.

[0151] In some embodiments, refer to Figure 1 and Figure 3 , the steps of obtaining the environment information of the database to be processed and registering the environment information into a preset data source management component include:

[0152] Use a database management tool to log in to the database to be processed;

[0153] Execute an SQL query statement and record the environment information from the query results;

[0154] Select a preset data source management component and its configuration method to create a data source configuration;

[0155] Fill the environment information into the data source configuration and conduct a test. When the test result is successful, the registration is completed.

[0156] Use a database management tool such as the MySQL command-line client, phpMyAdmin (PHP database management), or programming interfaces such as JDBC (Java Database Connectivity), ODBC (Open Database Connectivity) to log in to the database to be processed. Execute an SQL query statement to obtain the environment information of the database.

[0157] Query the database to obtain more detailed database and table information, including occupied space, number of records, etc.

[0158] Record key environmental information from the query results, such as database version, character set settings, storage engine, connection parameters, etc. Confirm the preset data source management components such as ODBC data source manager, JDBC connection pool configuration, and their configuration methods. Create or edit the data source configuration according to the requirements of the data source management component. Fill in the database connection information, including database address, port number, database name, username, and password, etc. Add or modify other connection parameters as needed, such as character set, connection timeout, maximum number of connections, etc., and these parameters should match the environmental information obtained previously. If the data source management component supports directly registering environmental information, the obtained environmental information can be added to the connection string or configuration file in an appropriate way. If it does not support direct registration, the environmental information can be associated with the data source configuration as a note or document for subsequent management and maintenance.

[0159] After completing the data source configuration, perform a connection test to ensure that the configuration is correct. If the test fails, adjust the configuration according to the error message and retest. After confirming that the connection test is successful, save the data source configuration for subsequent use.

[0160] To solve the problem of being able to discover potential performance problems in products without additionally increasing the workload of performance testing, it is necessary to utilize the environments for functional testing by all daily testers and the automated testing environments with different combinations of databases and servers. The data source management component can register different environmental information for unified management, and the data source can be all database types supported by products such as Oracle, SqlServer, Mysql, PG, DM, Highgo, Kingbase, Shentong, Gauss, OceanBase (multi-model database), etc.

[0161] The data source management component implements functions such as adding, modifying, and deleting data sources of different database types. The content included in the data source involves the database type used by the data source, device IP, database service name, port number, connection user and password, etc. Once the relevant environmental information is registered in the data source management component, the running data of this environment can be monitored and captured, specifically as Figure 3 shown.

[0162] In some embodiments, please refer to Figure 1 and Figure 3 , the steps of monitoring all databases through the preset data source management component and capturing all executed SQL statements include:

[0163] Monitor the activities of the database through the preset data source management component;

[0164] Capture the currently executed and historical SQL statements and the corresponding execution statistics;

[0165] Regularly query and monitor, and record the query results in the monitoring table.

[0166] Use built-in monitoring tools or dynamic management views to capture and analyze database activities, including executed SQL statements. Enable query logging or slow query logging functions at the database level to capture all executed SQL statements or only those that exceed a specific threshold in execution time. Ensure that the log files are properly managed and backed up to prevent excessive disk space occupation. Use third-party monitoring tools to capture database activities, regularly analyze the captured SQL statements, and record them in the monitoring table.

[0167] In some embodiments, refer to Figure 1 and Figure 4 The steps of evaluating the SQL statements, identifying and exporting problematic SQLs include:

[0168] Evaluate and classify the SQL statements according to preset rules to obtain an evaluation and classification result;

[0169] Process the evaluation and classification result through preset filtering conditions, identify the SQL statements that meet the filtering conditions, and mark them as problematic SQLs;

[0170] Export the problematic SQLs.

[0171] After daily testers register the environments for functional testing and automated environments shown in Figure 6 to the data source management component, the most core work remaining is to find the problematic SQLs in these environments for improvement. Each problematic SQL corresponds to a certain front-end function in the product. Once the problematic SQLs are solved, the problems of slow product business functions or potential resource occupation are also correspondingly solved.

[0172] The problematic SQL acquisition component implements data sources for different database types and sorts and filters according to certain rules, such as the average execution time per single time being greater than 1 second, the total execution time being greater than XX, the number of executions being greater than XX, the IO consumption being greater than XX, etc. According to these rules, it sorts and filters by SQL execution time consumption, SQL memory consumption, IO consumption, etc., and captures all eligible SQLs that we consider to have potential problems. Obtain the definition of the SQL and the definition of the final display style, specifically as shown in Figure 4 shown.

[0173] In some embodiments, refer to Figure 1, the preset rules include one or more of the single - time average execution time being greater than 1 second, the total execution time being greater than the preset time, the number of executions being greater than the preset number, and the IO consumption being greater than the preset threshold. The preset filtering conditions include one or more of syntax errors, logical errors, and performance issues.

[0174] For syntax checking, use the SQL editor or the built - in syntax checker of the IDE to identify syntax errors, ensure that all keywords are spelled correctly, parentheses are paired correctly, and there are no missing semicolons, etc. Check variables. If variables are used in the query, ensure that the variables have been correctly declared and assigned.

[0175] For logical checking, verify table names and column names to confirm that the tables and columns referenced in the query exist and are spelled correctly. Verify conditions, carefully check the WHERE statement and other conditions in the query to ensure they are correct and meet expectations. Consider data types and check whether the data types involved in the query match to avoid data type conversion errors. Check permissions to ensure that the database user executing the query has sufficient permissions.

[0176] For performance checking, use the EXPLAIN or ANALYZE command to provide information about the query execution plan and help identify performance bottlenecks. In MySQL, you can use EXPLAIN or EXPLAIN ANALYZE; in PostgreSQL (open - source object - relational database system), you can use EXPLAIN; in Oracle, you can use EXPLAIN PLAN, etc. Check indexes to ensure that the tables used in the query have appropriate indexes and these indexes are used effectively. Evaluate query optimization and consider whether the performance can be optimized by rewriting the query, using more efficient join types, predicates, or aggregate functions.

[0177] In some embodiments, refer to Figure 1 and Figure 3 , the step of comparing the problematic SQL with the problems in the preset BUG library and registering includes:

[0178] Obtain the preset BUG library containing the historical records of the problematic SQL and compare the historical records of the problematic SQL in the preset BUG library;

[0179] Execute a fuzzy matching function in the BUG library to search for records similar to the problematic SQL;

[0180] In response to not finding similar records, create a new record in the BUG library based on the problematic SQL for registration;

[0181] In response to finding similar records, associate the problematic SQL with the similar records for registration.

[0182] After the SQL issues in the daily function tests and automated test environments are captured, the remaining task is to display these issues, export them to files or corresponding data management tables, and let the testers confirm the issues. For issues clearly identified as performance problems, the testers register the relevant issues in the BUG management system.

[0183] Confirm that the preset BUG library exists and is available, including historical SQL issues and their detailed information. Ensure that the structure of the BUG library is clear, including fields such as problem SQL, problem description, error type, occurrence frequency, and repair solutions. Collect the problem SQL statements identified through evaluation and prepare to compare them with the records in the BUG library.

[0184] Execute a query operation in the BUG library to search for records similar to the current problem SQL. You can use fuzzy matching functions such as LIKE or REGEXP in SQL to search for records containing similar patterns or keywords. Pay attention to comparing the core logic, structure, keywords, etc. of the problem SQL, rather than just a complete text match. For the similar records found, conduct a detailed comparison, including aspects such as problem description, error type, and context environment. Confirm whether the problem SQL matches a known problem in the BUG library or is a new variant of a problem.

[0185] If the problem SQL does not match any records in the BUG library, it indicates that it is a new problem. At this time, a new record needs to be created in the BUG library to register this problem. Fill in basic information such as the problem SQL, problem description, error type, discovery time, and record the context environment in which the problem occurred as detailed as possible. If the problem SQL matches a known problem in the BUG library or is a variant of a known problem, this new instance can be associated with the known problem. Update the record of the known problem by adding information such as the occurrence frequency of the problem and the relevant environment to better track and solve the problem.

[0186] Classify and label the problems based on factors such as the nature, severity, and impact scope of the problems. This helps with subsequent problem management and priority ranking. Regularly review and update the BUG library, deleting outdated or no longer relevant problem records to ensure the accuracy and timeliness of the BUG library.

[0187] In some embodiments, refer to Figure 1 and Figure 5 , the method further includes:

[0188] According to the user's selection, obtain the TOP SQL of the currently selected data source and display it to the front end in the form expected by the user. Through the front-end display window, the user can have an intuitive understanding of the TOP SQL corresponding to the environment of the currently selected data source. Specifically, as Figure 5 shown.

[0189] The present invention proposes a system for capturing problematic SQL. Please refer to Figure 2 , including:

[0190] An acquisition unit 100, configured to obtain the environment information of the database to be processed and register the environment information in a preset data source management component;

[0191] A capture unit 200, configured to monitor all databases through the preset data source management component and capture all executed SQL statements.

[0192] An identification unit 300, configured to evaluate the SQL statements, identify and export problematic SQL;

[0193] A registration unit 400, configured to compare the problematic SQL with the problems in a preset BUG library and register them.

[0194] The present invention obtains the SQL with slow response during the operation of the environment, so as to help the organization discover potential problematic SQL with slow response in the product without additional test investment, that is, discover potential performance problems in the product, so as to improve the test coverage and ultimately promote the improvement of the product performance quality. By automatically capturing the problematic SQL during the operation of the product in the background, it breaks through the traditional front-end and black-box testing methods, discovers deep-seated performance hidden dangers in the product, improves the problem detection rate of the product, and improves the test coverage of the product for various environments.

[0195] Based on the same inventive concept, according to another aspect of the present invention, as Figure 7 shown, an embodiment of the present invention further provides a computer device 30. In this computer device 30, it includes a processor 310 and a memory 320. The memory 320 stores a computer program 321 that can run on the processor. When the processor 310 executes the program, it executes the steps of the above method.

[0196] Based on the same inventive concept, according to another aspect of the present invention, as Figure 8 shown, an embodiment of the present invention further provides a computer-readable storage medium 40. The computer-readable storage medium 40 stores a computer program 410 that, when executed by a processor, executes the above method.

[0197] The embodiment of the present invention may also include a corresponding computer device. The computer device includes a memory, at least one processor, and a computer program stored in the memory and executable on the processor, and the processor executes any one of the above methods when executing the program.

[0198] The memory, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions / modules in the embodiments of the present application. The processor executes various functional applications and data processing of the device by running the non-volatile software programs, instructions and modules stored in the memory, that is, implementing the above method.

[0199] The memory may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the device, etc. In addition, the memory may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In an embodiment, the memory may optionally include a memory remotely arranged relative to the processor, and these remote memories may be connected to the local module via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0200] Finally, it should be noted that a person of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through a computer program, and the program can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the storage medium of the program can be a disk, an optical disk, a read-only storage memory (ROM) or a random access memory (RAM), etc. The above-mentioned computer program embodiments can achieve the same or similar effects as the corresponding above-mentioned arbitrary method embodiments.

[0201] It will also be appreciated by those skilled in the art that various exemplary logic blocks, modules, circuits and algorithm steps described in conjunction with the disclosure herein can be implemented as electronic hardware, computer software or a combination of the two. In order to clearly illustrate this interchangeability of hardware and software, a general description has been given to the functions of various schematic components, blocks, modules, circuits and steps. Whether this function is implemented as software or hardware depends on specific applications and the design constraints imposed on the entire system. Those skilled in the art can implement the function in various ways for each specific application, but this implementation decision should not be interpreted as causing a departure from the disclosed scope of the embodiments of the present invention.

[0202] The above are exemplary embodiments disclosed by the present invention. However, it should be noted that various changes and modifications can be made without departing from the scope of the embodiments disclosed by the present invention as defined by the claims. The functions, steps, and / or actions of the method claims according to the disclosed embodiments herein need not be performed in any particular order. The serial numbers of the disclosed embodiments of the present invention above are only for description and do not represent the superiority or inferiority of the embodiments. In addition, although the elements disclosed by the embodiments of the present invention can be described or claimed in an individual form, they can also be understood as plural unless explicitly limited to the singular form.

[0203] It should be understood that, as used herein, unless the context clearly supports the exception, the singular form "a" is also intended to include the plural form. It should also be understood that the "and / or" used herein refers to any and all possible combinations including one or more of the related listed items.

[0204] Those of ordinary skill in the art should understand that: the discussion of any of the above embodiments is only exemplary and is not intended to imply that the scope of the disclosure of the embodiments of the present invention (including the claims) is limited to these examples; under the concept of the embodiments of the present invention, the technical features between the above embodiments or different embodiments can also be combined, and there are many other variations in different aspects of the embodiments of the present invention as above, which are not provided in detail for the sake of brevity. Therefore, any omission, modification, equivalent replacement, improvement, etc. made within the spirit and principle of the embodiments of the present invention shall be included in the protection scope of the embodiments of the present invention.

Claims

1. A method for capturing problematic SQL, characterized in that: include: Obtaining environmental information of the database to be processed, and registering the environmental information in a preset data source management component; Monitor all databases through the preset data source management component and capture all executed SQL statements; Evaluate the SQL statements, identify and export problematic SQL statements; The problematic SQL is compared with the problems in the preset BUG library and registered.

2. A method for capturing problematic SQL according to claim 1, characterized in that: The step of obtaining the environment information of the database to be processed and registering the environment information in the preset data source management component includes: Use the database management tool to log in to the database to be processed; Execute SQL query statements and record environmental information from the query results; Select the preset data source management component and its configuration method to create a data source configuration; Fill the environment information into the data source configuration, perform a test, and complete the registration when the test result is successful.

3. A method for capturing problematic SQL according to claim 1, characterized in that: The step of monitoring all databases through the preset data source management component and capturing all executed SQL statements includes: Monitoring the activities of the database through the preset data source management component; Capture currently executed and historical SQL statements and corresponding execution statistics; Query monitoring regularly and record the query results in the monitoring table.

4. The method for capturing problematic SQL according to claim 1, characterized in that: The step of evaluating the SQL statement, identifying and exporting problematic SQL statements comprises: Evaluate and classify the SQL statements according to preset rules to obtain evaluation and classification results; Processing the evaluation classification results according to preset filtering conditions, identifying SQL statements that meet the filtering conditions, and marking them as problematic SQL statements; Export the problematic SQL.

5. A method for capturing problematic SQL according to claim 4, characterized in that: The preset rules include one or more of the following: the single average execution time is greater than 1 second, the total execution time is greater than the preset time, the number of executions is greater than the preset number, and the IO consumption is greater than the preset threshold; the preset filtering conditions include one or more of the following: syntax errors, logical errors, and performance issues.

6. A method for capturing problematic SQL according to claim 1, characterized in that: The step of comparing the problematic SQL with the problems in the preset BUG library and registering them comprises: Obtain a preset BUG library containing historical records of problematic SQL, and compare the historical records of the problematic SQL in the preset BUG library; Execute fuzzy matching function in the BUG database to search for records similar to the problematic SQL; In response to the search failing to find a similar record, a new record is created in the BUG database based on the problem SQL for registration; In response to searching for similar records, the question SQL is associated with the similar records for registration.

7. The method for capturing problematic SQL according to claim 1, characterized in that: The method also includes: Get the TOP SQL of the currently selected data source and display it to the front end in the form expected by the user.

8. A system for capturing problematic SQL, characterized in that: include: An acquisition unit configured to acquire environment information of a database to be processed and register the environment information into a preset data source management component; A capture unit, configured to monitor all databases through the preset data source management component and capture all executed SQL statements; An identification unit, configured to evaluate the SQL statement, identify and derive problematic SQL; The registration unit is configured to compare the problem SQL with the problems in the preset BUG library and register them.

9. A computer device comprising: at least one processor; and a memory storing a computer program executable on the processor, wherein the processor executes the steps of a method for capturing problematic SQL as claimed in any one of claims 1 to 7 when executing the program.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the steps of the method for crawling problematic SQL as described in any one of claims 1 to 7 are performed.

Images