A data processing performance analysis method, device, system, apparatus and medium

Abstract

Embodiments of the application disclose a data processing performance analysis method, device, system, equipment and medium, the method comprises: when determining that an application service sends a data processing instruction to a target database, acquiring the data processing instruction, wherein the application service realizes a target service function by sending a data processing instruction to a target database; acquiring associated information of the target database executing the data processing instruction according to the data processing instruction; sending the associated information to a performance analysis server, so that the performance analysis server determines a performance fault of a structured query language (SQL) corresponding to the data processing instruction based on the associated information. Through the technical scheme of the embodiments of the application, the data operation performance of the SQL is comprehensively and automatically analyzed, and the business quality requirement of the relevant staff is reduced.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a data processing performance analysis method, apparatus, system, electronic device and storage medium. Background Technology

[0002] SQL (Structured Query Language) is a database query and programming language used to access, query, update, and manage relational database systems. However, improper SQL design in an application system can severely impact data access speed, query speed, or update speed, thereby affecting the service performance of the application system.

[0003] Currently, the main method for analyzing SQL in application systems is as follows: After the application system goes live, the performance of SQL is monitored during its use through the database's built-in performance monitoring mechanisms, and relevant data is written to logs when necessary. Business development personnel or application system maintenance personnel use these logs to identify problems with the SQL, make corresponding improvements, and then redeploy the improved application system.

[0004] In the process of realizing this invention, the inventors discovered at least the following problems in the prior art:

[0005] The above-described SQL analysis method cannot comprehensively analyze all the problems existing in the SQL. Furthermore, business development personnel or application system maintenance personnel need to use their business experience combined with the logs to identify the problems in the SQL and make corresponding improvements. This requires a high level of professional competence from the development personnel or application system maintenance personnel. Summary of the Invention

[0006] This invention provides a data processing performance analysis method, apparatus, system, electronic device, and storage medium, which enables a more comprehensive and automated analysis of the data operation performance of application service SQL, reducing the professional skills requirements for relevant personnel.

[0007] In a first aspect, embodiments of the present invention provide a data processing performance analysis method, comprising:

[0008] When it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database;

[0009] According to the data processing instructions, obtain the associated information of the target database for executing the data processing instructions;

[0010] The association information is sent to the performance analysis server so that the performance analysis server can determine the performance faults of the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information.

[0011] Secondly, embodiments of the present invention provide a data processing performance analysis method, applied to a performance analysis server, wherein the performance analysis server is responsible for maintaining the application services of the application server, characterized in that it includes:

[0012] Receive associated information sent by the application server, which is the target database executing the target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service;

[0013] Based on the associated information, determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction.

[0014] Thirdly, embodiments of the present invention also provide a data processing performance analysis device, configured on an application server, comprising:

[0015] The first acquisition module is used to acquire the data processing instruction when it is determined that the application service sends a data processing instruction to the target database, wherein the application service implements the target service function by sending the data processing instruction to the target database;

[0016] The second acquisition module is used to acquire the associated information of the target database executing the data processing instruction according to the data processing instruction;

[0017] The sending module is used to send the association information to the performance analysis server, so that the performance analysis server can determine the performance fault of the structured query language (SQL) corresponding to the data processing instruction based on the association information.

[0018] Fourthly, embodiments of the present invention also provide a data processing performance analysis device, integrated into a performance analysis server, wherein the performance analysis server is responsible for maintaining the application services of the application server, including:

[0019] The receiving module is used to receive the associated information sent by the custom plugin of the application server, which is the target database executing the target data processing instruction. The target data processing instruction is sent by the application service to the target database to realize the target service function of the application service.

[0020] The analysis module is used to determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information.

[0021] Fifthly, embodiments of the present invention also provide a data processing performance analysis system, comprising:

[0022] Application server and performance analysis server;

[0023] The application server includes custom plugins independent of each application service, and the custom plugins are used to execute the data processing performance analysis method according to any one of claims 1-5.

[0024] The performance analysis server is used to execute the data processing performance analysis method according to any one of claims 6-14.

[0025] Sixthly, embodiments of the present invention also provide an electronic device, the electronic device comprising:

[0026] One or more processors;

[0027] Memory, used to store one or more programs;

[0028] When the one or more programs are executed by the one or more processors, the one or more processors implement the data processing performance analysis method steps provided in any embodiment of the present invention.

[0029] In a seventh aspect, embodiments of the present invention also provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the data processing performance analysis method steps provided in any embodiment of the present invention.

[0030] The embodiments of the above invention have the following advantages or beneficial effects:

[0031] When it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database; the association information of the target database executing the data processing instruction is obtained according to the data processing instruction; the association information is sent to the performance analysis server, so that the performance analysis server can determine the performance failure of the structured query language (SQL) corresponding to the data processing instruction based on the association information. This technical means realizes a more comprehensive and automated analysis of the data operation performance of the application service's SQL, and reduces the business quality requirements of relevant personnel. Attached Figure Description

[0032] Figure 1 This is a flowchart of a data processing performance analysis method provided in Embodiment 1 of the present invention;

[0033] Figure 2This is a flowchart of another data processing performance analysis method according to Embodiment 1 of the present invention;

[0034] Figure 3 This is a flowchart of a data processing performance analysis method provided in Embodiment 2 of the present invention;

[0035] Figure 4 This is a flowchart of another data processing performance analysis method involved in Embodiment 2 of the present invention;

[0036] Figure 5 This is a schematic diagram of the structure of a data processing performance analysis device provided in Embodiment 3 of the present invention;

[0037] Figure 6 This is a schematic diagram of the structure of a data processing performance analysis device provided in Embodiment 4 of the present invention;

[0038] Figure 7 This is a schematic diagram of the structure of a data processing performance analysis system provided in Embodiment 5 of the present invention;

[0039] Figure 8 This is a schematic diagram of the structure of an electronic device provided in Embodiment Six of the present invention. Detailed Implementation

[0040] The present invention will now be described in further detail with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative of the invention and not intended to limit it. Furthermore, it should be noted that, for ease of description, the accompanying drawings show only the parts relevant to the present invention, and not all of the structures.

[0041] Example 1

[0042] Figure 1 This is a flowchart of a data processing performance analysis method provided in Embodiment 1 of the present invention. This embodiment is applicable to the performance analysis of database operation logic, specifically SQL statement operation logic, in application services of various application servers. The aim is to proactively identify SQL statements with low processing performance in application services and provide corresponding optimization schemes to improve their processing performance, thereby improving the system performance of the application server and enhancing the user experience. The SQL statements with low processing performance are, for example, SQL statements with low query efficiency. This method can be executed by a data processing performance analysis device, which can be implemented in software and / or hardware and integrated into a custom plugin. This plugin can be easily plugged into various application servers to monitor the performance of SQL statements in the application services of each application server.

[0043] like Figure 1 As shown, the method specifically includes the following steps:

[0044] Step 110: When it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database.

[0045] Specifically, the application service could be a service for querying the list of students in a class who scored above 90, and the corresponding data processing instruction could be an instruction for querying the list of students in a class who scored above 90. The data processing instruction could specifically be an SQL statement. The purpose of this embodiment is to analyze the processing performance of the SQL statements in the application service to determine whether the operational logic design of the SQL statements is reasonable and whether the data processing performance obtained by executing the SQL statements is superior. For example, for SQL statements used for querying, the quality of their data processing performance can be determined by the query time or query efficiency.

[0046] For example, the data processing instructions include at least one of the following:

[0047] Data query commands, data insertion commands, and data modification commands.

[0048] Step 120: Obtain the associated information of the target database for executing the data processing instruction according to the data processing instruction.

[0049] For example, obtaining the associated information for executing the data processing instruction in the target database according to the data processing instruction includes:

[0050] Generate an associated command to obtain the associated information based on the data processing instructions;

[0051] The association command is sent to the target database so that the target database obtains the association information by executing the association command.

[0052] Receive the association information fed back from the target database.

[0053] The associated commands include at least one of the following: Structured Query Language (SQL) execution plan commands, associated table structure query commands, and associated table index query commands.

[0054] For example, the SQL statement corresponding to the data processing instruction is: `select * from user` (query target * from user). The SQL execution plan command generated for this SQL statement is specifically `explain select * from user`. The database executes the statement `explain select * from user` and returns the result set of the execution plan.

[0055]

[0056] The associated information includes at least one of the following: the data processing instruction, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the associated table structure query command, the third result obtained by executing the associated table index query command, and the identification information of the application service.

[0057] Step 130: Send the association information to the performance analysis server so that the performance analysis server can determine the performance fault of the structured query language (SQL) corresponding to the data processing instruction based on the association information.

[0058] The performance analysis server is used to analyze the performance faults of the SQL corresponding to the data processing instruction based on the correlation information fed back by the application server, such as analyzing whether the SQL has a performance problem of low query efficiency.

[0059] For example, if analyzing the associated information determines that the index type "type" used by the SQL is ALL, then the corresponding SQL has a performance problem. Index type "type" ALL is the lowest performing index type in database queries and is generally not used unless there is only one data record in the database. Of course, this situation does not exist in actual business systems. Therefore, by analyzing the associated information and determining that the SQL's index type "type" is ALL, it can be determined that the SQL has a performance problem, and optimization solutions or suggestions can be provided for this performance problem. For example, the optimization suggestion could be "please change the index type 'type', changing ALL to ref or range". By actively analyzing the SQL corresponding to the data processing instructions, performance problems can be identified in a timely manner, and optimization suggestions can be automatically provided. This greatly reduces the business skills requirements for relevant personnel, and at the same time, it achieves a more comprehensive and automated analysis of the data operation performance of SQL, promptly identifying and correcting problems, ensuring the system performance of the application server, and providing a guarantee for the normal operation of application services. Understandably, this data processing performance analysis method can be performed before or after the application service goes live. Preferably, it is performed before launch to ensure stable service performance, improve user experience, and avoid repeated deployment processes. For example, if the data processing performance analysis method provided in this embodiment is performed after launch and reveals performance issues with the application service's SQL, the optimized application service needs to be re-deployed after addressing these issues to ensure its effectiveness and usability. Furthermore, the results of the data processing performance analysis method provided in this embodiment can be used as quality management indicators in the R&D process to standardize business development processes and improve the performance of business systems.

[0060] For example, sending the associated information to the performance analysis server includes:

[0061] The associated information is sent to the performance analysis server via a message queue (MQ).

[0062] Message queue middleware is an important component in distributed systems, mainly used to solve asynchronous message problems. In the scenario of this embodiment, the data processing instructions can be obtained in an asynchronous task manner by accessing a custom plugin on the application server, and the associated information of the target database to execute the data processing instructions can be obtained according to the data processing instructions, so as not to affect the normal operation of the application service.

[0063] Further reference Figure 2The flowchart of another data processing performance analysis method is shown below. Specifically, it includes: a Java application service ORM framework executing SQL, whereby the application service sends data processing instructions to the target database. A custom interceptor intercepts the SQL; that is, a custom plugin obtains the data processing instructions sent by the application service to the target database, parses the obtained SQL statement through an asynchronous task, and generates three types of execution commands: an SQL execution plan command, a related table structure query command, and a related table index query command. These three commands are then sent to the target database. The target database obtains the corresponding execution results by executing these three commands and feeds back the execution result set to the custom plugin. The custom plugin further sends the execution result set to the performance analysis server via MQ. The execution result set specifically includes the first result obtained from executing the structured query language SQL execution plan command, the second result obtained from executing the related table structure query command, the third result obtained from executing the related table index query command, the data processing instructions, and the identification information of the application service.

[0064] The custom plugin is implemented based on the MyBatis interceptor custom plugin principle. By customizing the MyBatis interceptor, all SQL query requests can be intercepted. Furthermore, the processing after interception is an asynchronous task, which does not affect the query performance of the main business logic.

[0065] The technical solution in this embodiment involves integrating a custom plugin into the application server. This plugin intercepts SQL statements from the application service, performs SQL execution plans and index queries on relevant tables, and sends the execution plan analysis results and index information to a performance analysis server. The performance analysis server analyzes the execution plans, marks the SQL statements that need optimization, and then developers can use the management interface of this performance analysis server platform to identify which SQL statements in their assigned business systems require optimization, including optimization strategies. The performance analysis server can also provide relevant suggestions. This achieves a more comprehensive and automated analysis of the data operation performance of SQL statements in application services, reducing the professional skills required of relevant personnel.

[0066] Example 2

[0067] Figure 3 This is a flowchart of a data processing performance analysis method provided in Embodiment 2 of the present invention. The method in this embodiment is applied to a performance analysis server, which is responsible for maintaining the application services of the aforementioned application server, promptly identifying problems in the application services, and making improvements to ensure the stability of the application services. Explanations of terms that are the same as or corresponding to those in the above embodiments will not be repeated here.

[0068] Based on the above embodiments, see Figure 3 The data processing performance analysis method provided in this embodiment specifically includes the following steps:

[0069] Step 310: Receive the associated information sent by the application server, which is the target database executing the target data processing instruction. The target data processing instruction is sent by the application service to the target database to realize the target service function of the application service.

[0070] The target data processing instructions include at least one of the following:

[0071] Data query commands, data insertion commands, and data modification commands.

[0072] Step 320: Determine the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information.

[0073] For example, the associated information includes at least one of the following:

[0074] The data processing instructions, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the related table structure query command, the third result obtained by executing the related table index query command, and the identification information of the application service;

[0075] The target data processing instruction includes at least one of the following:

[0076] Data query commands, data insertion commands, and data modification commands.

[0077] The step of determining the performance faults in the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information includes:

[0078] The first result included in the associated information is analyzed by a preset dynamic analysis chain to obtain the analysis result;

[0079] Based on the analysis results, the performance faults of the SQL were identified.

[0080] Furthermore, determining the performance faults in the SQL based on the analysis results includes:

[0081] The index type used is determined by the index type field included in the analysis results;

[0082] The performance issues with the SQL can be determined based on the type of index used.

[0083] For example, if analyzing the associated information determines that the index type "type" used by the SQL is ALL, then the corresponding SQL has a performance problem. Index type "type" ALL is the lowest performing index type in database queries and is generally not used unless there is only one data record in the database. Of course, this situation does not exist in actual business systems. Therefore, by analyzing the associated information and determining that the SQL's index type "type" is ALL, it can be determined that the SQL has a performance problem, and optimization solutions or suggestions can be provided for this performance problem. For example, the optimization suggestion could be "Please change the index type 'type' from ALL to ref or range". In this example, an index type of "type" of ALL indicates that all contents of the data table are traversed to obtain the query target. This query method has the lowest query efficiency and is generally not used in actual business systems. However, it may be used by some developers with limited business experience. Therefore, the data processing performance analysis method provided in this embodiment can promptly identify and mark SQL statements with low data processing performance in the business system, and provide optimization suggestions for developers' reference. This greatly reduces the business experience requirements for developers, helps improve their work efficiency, and importantly, ensures the performance stability of the business system. By actively analyzing the SQL corresponding to the data processing instructions, performance problems can be identified in a timely manner, and optimization suggestions can be automatically provided. This greatly reduces the business skills requirements for relevant personnel, and at the same time, it achieves a more comprehensive and automated analysis of the data operation performance of SQL, promptly identifies and corrects problems, ensures the system performance of the application server, and provides a guarantee for the normal operation of the application service. Understandably, this data processing performance analysis method can be performed before or after the application service goes live. Preferably, it is performed before launch to ensure stable service performance, improve user experience, and avoid repeated deployment processes. For example, if the data processing performance analysis method provided in this embodiment is performed after launch and reveals performance issues with the application service's SQL, the optimized application service needs to be re-deployed after addressing these issues to ensure its effectiveness and usability. Furthermore, the results of the data processing performance analysis method provided in this embodiment can be used as quality management indicators in the R&D process to standardize business development processes and improve the performance of business systems.

[0084] Furthermore, the step of determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information also includes:

[0085] The analysis results are obtained by analyzing the first result, second result, and / or third result included in the associated information through a custom script added to the dynamic analysis chain.

[0086] Preferably, the associated information is analyzed using a pre-defined dynamic analysis chain pattern, which facilitates the expansion and addition of new analysis strategies. The dynamic analysis chain may, for example, first analyze the SQL execution plan results, using the `select_type` field to determine whether the query is a single-table query or a join query. Then, it primarily analyzes the `type` field to confirm the index types used, including: `system`, `const`, `eq-ref`, `ref`, `fulltext`, `ref_or_null`, `index_merge`, `unique_subquery`, `range`, `index`, and `All`. This is then combined with the query table and filter condition fields extracted from the pre-parsed SQL, as well as the index information of the data table, to analyze whether indexes are used, whether composite indexes for multi-condition queries are hit, and whether the leftmost matching principle is followed. After a period of testing, if a link in the dynamic analysis chain is found to be unnecessary, it can be dynamically deleted; or if a new and more effective analysis strategy is discovered, a custom script analysis chain can be dynamically added. Custom Groovy scripts are loaded using the Groovy Script Engine and added as dynamically added analysis strategies to appropriate positions in the pre-defined dynamic analysis chain. For example, as technology advances and analytical dimensions become more comprehensive, indexed fields in a table can be dynamically added to assess whether the index size affects the overall performance of SQL queries. As business processes become more complex, a table's indexes may grow larger and contain more indexed fields. However, if the number of indexes reaches a certain point, it can negatively impact overall query performance, potentially requiring index simplification.

[0087] Furthermore, for example, when the index type "type" of the SQL execution plan is ALL, it indicates a full table scan. This scan has extremely low performance and requires urgent optimization. The optimization suggestion may be to create indexes on the relevant fields. Simultaneously, the extra field can be analyzed to determine if temporary tables are used, as using temporary tables can drastically degrade performance when the table data is large. Intelligent suggestions can be provided based on the table data volume. For example, if analysis determines that temporary tables are used, the optimization suggestion is: "Due to the large table data volume, it is recommended to avoid the use of temporary tables." These optimization suggestions can be output to the performance analysis server's visual control interface. Developers and testers can view the performance analysis tables of SQL generated by various business systems (specifically application services) on this interface, along with relevant SQL optimization solutions.

[0088] For example, determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information includes:

[0089] The target data processing instructions are parsed using SQL syntax to extract the query fields;

[0090] The third result included in the associated information is analyzed by a pre-set dynamic analysis chain to obtain the index field;

[0091] The performance faults of the SQL are determined based on the query fields and the index fields.

[0092] For example, the SQL statement corresponding to the target data processing instruction is: `select * from user where name='xx'and age=25and sex='male'`. SQL syntax parsing reveals the query fields as `name`, `age`, and `sex`. Analyzing the third result included in the associated information using a pre-defined dynamic analysis chain reveals the index fields as: single-field indexes for `name`, `age`, and `sex`. The SQL statement corresponding to the target data processing instruction confirms that using a composite index would achieve faster query speeds. However, the target SQL query logic does not use a composite index, therefore, the current SQL has a performance issue. The recommended optimization is to create a composite index for `name`, `age`, and `sex`.

[0093] Furthermore, before determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information, the process further includes:

[0094] The identification information of the application service is determined based on the associated information;

[0095] Calculate the corresponding message digest MD5 value based on the target data processing instruction and the identification information;

[0096] Based on the MD5 value, determine whether the SQL corresponding to the target data processing instruction has been analyzed for performance faults. If not, continue to execute the operation of determining the performance faults of the structured query language SQL corresponding to the target data processing instruction based on the association information.

[0097] To facilitate management and avoid redundant performance fault analysis, performance fault analysis is performed only once for the SQL corresponding to the same target data processing instruction for each application service. Therefore, before performing performance fault analysis, the corresponding message digest MD5 value is calculated based on the target data processing instruction and the identification information of the application service corresponding to the instruction. The MD5 value is used to determine whether the SQL corresponding to the target data processing instruction has been analyzed for performance faults. If it has not been analyzed, the operation of determining the performance faults of the structured query language SQL corresponding to the target data processing instruction based on the association information continues. If it has already been analyzed, the analysis is not repeated.

[0098] Further reference Figure 4 The flowchart of another data processing performance analysis method shown includes: receiving a dataset sent by the application server, which specifically includes SQL execution plan results, data table structure information, index information, SQL statements, and the application service app's identifier ID. Calculating the corresponding MD5 value based on the string generated from the SQL statement and the application service app's identifier ID; storing the SQL execution result, data table structure information, index information, SQL statement, and appID in a table; determining whether the SQL corresponding to the current data processing instruction has been analyzed and whether there are any performance faults based on the MD5 value; if not, parsing the SQL statement and analyzing the execution plan results; executing a pre-set analyzer chain to analyze various parameters of the execution plan, such as select-type, type, key, key_len, and extra parameters; executing a custom dynamic script analysis chain; summarizing the analysis results of the analysis chain; and outputting optimization solutions to the front-end interface for developers to use.

[0099] The technical solution of this embodiment, by receiving the association information sent by the application server and the target database executing the target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service; and by determining the performance fault of the structured query language (SQL) corresponding to the target data processing instruction based on the association information, a more comprehensive and automated analysis of the data operation performance of the application service's SQL is achieved, reducing the business skills requirements of relevant personnel.

[0100] The following are embodiments of the data processing performance analysis device provided in this invention. This device and the data processing performance analysis method of the above embodiments belong to the same inventive concept. For details not described in detail in the embodiments of the data processing performance analysis device, please refer to the embodiments of the above data processing performance analysis method.

[0101] Example 3

[0102] Figure 5 This is a schematic diagram of a data processing performance analysis device provided in Embodiment 3 of the present invention. This device is integrated into a custom plugin on the application server and can be used to perform performance analysis on the database operation logic, specifically the SQL statement operation logic, within the application services of various application servers. Its aim is to proactively identify SQL statements with low processing performance in the application services and provide corresponding optimization schemes to improve their processing performance, thereby improving the system performance of the application server and enhancing the user experience. The SQL statements with low processing performance are, for example, SQL statements with low query efficiency.

[0103] like Figure 5 As shown, the device includes: a first acquisition module 510, a second acquisition module 520, and a sending module 530;

[0104] The first acquisition module 510 is used to acquire the data processing instruction when it is determined that the application service sends a data processing instruction to the target database, wherein the application service implements the target service function by sending the data processing instruction to the target database; the second acquisition module 520 is used to acquire the association information of the target database executing the data processing instruction according to the data processing instruction; and the sending module 530 is used to send the association information to the performance analysis server so that the performance analysis server can determine the performance fault of the structured query language (SQL) corresponding to the data processing instruction based on the association information.

[0105] Furthermore, the second acquisition module 520 includes:

[0106] A generation unit is configured to generate an association command for obtaining the association information based on the data processing instructions.

[0107] A sending unit is configured to send the association command to the target database, so that the target database obtains the association information by executing the association command;

[0108] A receiving unit is used to receive the association information fed back by the target database.

[0109] Furthermore, the associated commands include at least one of the following: Structured Query Language (SQL) execution plan commands, associated table structure query commands, and associated table index query commands.

[0110] Furthermore, the sending module 530 is specifically used for:

[0111] The associated information is sent to the performance analysis server via a message queue (MQ).

[0112] Furthermore, the associated information includes at least one of the following:

[0113] The data processing instructions, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the related table structure query command, the third result obtained by executing the related table index query command, and the identification information of the application service;

[0114] The data processing instructions include at least one of the following:

[0115] Data query commands, data insertion commands, and data modification commands.

[0116] The technical solution in this embodiment involves integrating a custom plugin into the application server. This plugin intercepts SQL statements from the application service, performs SQL execution plans and index queries on relevant tables, and sends the execution plan analysis results and index information to a performance analysis server. The performance analysis server analyzes the execution plans, marks the SQL statements that need optimization, and then developers can use the management interface of this performance analysis server platform to identify which SQL statements in their assigned business systems require optimization, including optimization strategies. The performance analysis server can also provide relevant suggestions. This achieves a more comprehensive and automated analysis of the data operation performance of SQL statements in application services, reducing the professional skills required of relevant personnel.

[0117] Example 4

[0118] Figure 6 This is a schematic diagram of a data processing performance analysis device provided in Embodiment 4 of the present invention. The device is integrated into a performance analysis server, which is responsible for maintaining the application services of the aforementioned application server.

[0119] like Figure 6 As shown, the device includes a receiving module 610 and an analysis module 620.

[0120] The receiving module 610 is used to receive the associated information sent by the custom plugin of the application server, which is the target database executing the target data processing instruction. The target data processing instruction is sent by the application service to the target database to realize the target service function of the application service.

[0121] Analysis module 620 is used to determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information.

[0122] Furthermore, the associated information includes at least one of the following:

[0123] The data processing instructions, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the related table structure query command, the third result obtained by executing the related table index query command, and the identification information of the application service;

[0124] The target data processing instruction includes at least one of the following:

[0125] Data query commands, data insertion commands, and data modification commands.

[0126] Furthermore, the analysis module 620 includes:

[0127] The first analysis unit is used to analyze the first result included in the associated information through a preset dynamic analysis chain to obtain the analysis result;

[0128] The second analysis unit is used to determine the performance faults of the SQL based on the analysis results.

[0129] Furthermore, the second analysis unit is specifically used for:

[0130] The index type used is determined by the index type field included in the analysis results;

[0131] The performance issues with the SQL can be determined based on the type of index used.

[0132] Furthermore, the analysis module 620 also includes:

[0133] The third analysis unit is used to analyze the first result, the second result, and / or the third result included in the associated information through a custom script added to the dynamic analysis chain, and obtain the analysis result.

[0134] Furthermore, the analysis module 620 includes:

[0135] The parsing unit is used to parse the target data processing instructions using SQL syntax to extract query fields;

[0136] The fourth analysis unit is used to analyze the third result included in the associated information through a preset dynamic analysis chain to obtain the index field;

[0137] The determining unit is used to determine the performance faults of the SQL based on the query field and the index field.

[0138] Furthermore, the device also includes:

[0139] The optimization scheme generation module is used to generate optimization schemes based on the performance faults existing in the SQL.

[0140] Furthermore, the device also includes: a verification module, used to determine, before determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information,

[0141] The identification information of the application service is determined based on the association information; the corresponding message digest MD5 value is calculated based on the target data processing instruction and the identification information; the performance fault of the SQL corresponding to the target data processing instruction has been analyzed based on the MD5 value; if not, the operation of determining the performance fault of the structured query language SQL corresponding to the target data processing instruction based on the association information continues.

[0142] The technical solution of this embodiment, by receiving the association information sent by the application server and the target database executing the target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service; and by determining the performance fault of the structured query language (SQL) corresponding to the target data processing instruction based on the association information, a more comprehensive and automated analysis of the data operation performance of the application service's SQL is achieved, reducing the business skills requirements of relevant personnel.

[0143] The data processing performance analysis device provided in the embodiments of the present invention can execute the data processing performance analysis method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects for executing the data processing performance analysis method.

[0144] Example 5

[0145] Figure 7 A data processing performance analysis system provided in Embodiment 5 of the present invention includes:

[0146] Application server 710 and performance analysis server 720;

[0147] The application server 710 includes a custom plugin 711 independent of each application service, which is used to execute the data processing performance analysis method described in Embodiment 1 above; the performance analysis server 720 is used to execute the data processing performance analysis method described in Embodiment 2 above.

[0148] Specifically, the custom plugin 711 can be plugged into the application server 710 to obtain the SQL instructions sent by the app service to the database DB. It also obtains the association information for executing the data processing instructions in the target database through an asynchronous task and remotely sends this association information to the performance analysis server 720. The performance analysis server 720 calculates the corresponding MD5 value based on the SQL statement and the string generated by the app service's identifier ID. It stores the SQL execution result, data table structure information, index information, SQL statement, and appID in a table. Based on the MD5 value, it determines whether the SQL corresponding to the current data processing instruction has been analyzed and whether there are any performance faults. If it has not been analyzed, it performs syntax parsing of the SQL statement, analyzes the execution plan results, executes the dynamic script of the analysis chain, analyzes various parameters of the execution plan, such as select-type, type, key, key_len, and extra parameters, summarizes the analysis results of the analysis chain, and outputs optimization solutions to the front-end interface for developers to use.

[0149] The technical solution in this embodiment uses a pluggable SQL-explain plugin to intercept SQL commands, perform SQL execution plans and related table index queries, and send the execution plan analysis results and index information to a performance analysis server. The performance analysis server analyzes the performance faults of the SQL based on the dataset sent by the plugin. The principle behind this technology is that the MyBatis application component supports custom plugins, which are embedded into the business system through custom SQL execution plan plugins. The implementation principle of MyBatis custom plugins is through an interceptor mechanism. SQL performance analysis combines the results of the SQL execution plan with relevant indexing techniques. For example, when the index type is ALL, performance optimization is required, and the optimization solution is to create an index for the corresponding filter condition field. The key point of this technology is that it unifies the handling of database index-related knowledge and optimization details through analysis tools, providing reasonable optimization solutions, lowering the technical threshold for SQL optimization, improving the efficiency of business development, and allowing for a greater focus on business needs. A dynamic script analysis chain is implemented using the Groovy ScriptEngine to load Groovy scripts, enabling the dynamic addition of analyzers in real time, and the added dynamic scripts take effect without restarting the service. Innovation in R&D process quality control: The introduction of this maintenance service platform and data processing performance analysis process can monitor and supervise the quality of development and testing. Testers can use this maintenance service platform to issue early warnings for non-functional performance indicator bugs, improve efficiency, and ensure that performance risks are exposed in a timely manner.

[0150] Example 6

[0151] Figure 8This is a schematic diagram of the structure of an electronic device provided in Embodiment Six of the present invention. Figure 8 A block diagram is shown of an exemplary electronic device 12 suitable for implementing embodiments of the present invention. Figure 8 The electronic device 12 shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of the present invention.

[0152] like Figure 8 As shown, the electronic device 12 is represented in the form of a general-purpose computing electronic device. The components of the electronic device 12 may include, but are not limited to: one or more processors or processing units 16, system memory 28, and bus 18 connecting different system components (including system memory 28 and processing unit 16).

[0153] Bus 18 represents one or more of several bus architectures, including a memory bus or memory controller, a peripheral bus, a graphics acceleration port, a processor, or a local bus using any of the various bus architectures. For example, these architectures include, but are not limited to, the Industry Standard Architecture (ISA) bus, the Micro Channel Architecture (MAC) bus, the Enhanced ISA bus, the Video Electronics Standards Association (VESA) local bus, and the Peripheral Component Interconnect (PCI) bus.

[0154] Electronic device 12 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by electronic device 12, including volatile and non-volatile media, removable and non-removable media.

[0155] System memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. Electronic device 12 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, storage system 34 may be used to read and write non-removable, non-volatile magnetic media (… Figure 8 Not shown; usually referred to as a "hard drive"). Although Figure 8 Not shown, a disk drive for reading and writing to a removable non-volatile disk (e.g., a "floppy disk") and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 via one or more data media interfaces. System memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to perform the functions of the embodiments of the present invention.

[0156] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in system memory 28. Such program modules 42 include, but are not limited to, an operating system, one or more application programs, other program modules, and program data. Each or some combination of these examples may include an implementation of a network environment. Program modules 42 typically perform the functions and / or methods described in the embodiments of the present invention.

[0157] Electronic device 12 can also communicate with one or more external electronic devices 14 (e.g., keyboard, pointing electronic devices, display 24, etc.), and with one or more electronic devices that enable a user to interact with it, and / or with any electronic device (e.g., network card, modem, etc.) that enables it to communicate with one or more other computing electronic devices. This communication can be performed via input / output (I / O) interface 22. Furthermore, electronic device 12 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via network adapter 20. As shown, network adapter 20 communicates with other modules of electronic device 12 via bus 18. It should be understood that, although not shown in the figures, other hardware and / or software modules can be used in conjunction with electronic device 12, including but not limited to: microcode, electronic device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0158] Processing unit 16 executes various functional applications and data processing performance analysis by running programs stored in system memory 28, such as implementing the steps of a data processing performance analysis method provided in this embodiment, the method including:

[0159] Specifically, when it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database.

[0160] According to the data processing instructions, obtain the associated information of the target database for executing the data processing instructions;

[0161] The association information is sent to the performance analysis server so that the performance analysis server can determine the performance faults of the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information.

[0162] For use with a performance analysis server, the performance analysis server is responsible for maintaining the application services of the application server, specifically including:

[0163] Receive association information sent by the custom plugin of the application server, which is the target database executing the target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service;

[0164] Based on the associated information, determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction.

[0165] Of course, those skilled in the art will understand that the processor can also implement the technical solutions of the data processing performance analysis method provided in any embodiment of the present invention.

[0166] Example 7

[0167] This embodiment seven provides a computer-readable storage medium storing a computer program thereon. When executed by a processor, the program implements the data processing performance analysis method steps provided in any embodiment of the present invention. The method includes:

[0168] Specifically, when applied to the application server, the following applies: when it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database;

[0169] According to the data processing instructions, obtain the associated information of the target database for executing the data processing instructions;

[0170] The association information is sent to the performance analysis server so that the performance analysis server can determine the performance faults of the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information.

[0171] For use with a performance analysis server, the performance analysis server is responsible for maintaining the application services of the application server, specifically including:

[0172] Receive association information sent by the custom plugin of the application server, which is the target database executing the target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service;

[0173] Based on the associated information, determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction.

[0174] The computer storage medium of this invention can be any combination of one or more computer-readable media. A computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. For example, a computer-readable storage medium can be, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples (a non-exhaustive list) of computer-readable storage media include: an electrical connection having one or more wires, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage device, magnetic storage device, or any suitable combination thereof. In this document, a computer-readable storage medium can be any tangible medium that contains or stores a program that can be used by or in conjunction with an instruction execution system, apparatus, or device.

[0175] Computer-readable signal media may include data signals propagated in baseband or as part of a carrier wave, carrying computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. Computer-readable signal media may also be any computer-readable medium other than computer-readable storage media, capable of sending, propagating, or transmitting programs for use by or in connection with an instruction execution system, apparatus, or device.

[0176] Program code contained on a computer-readable medium may be transmitted using any suitable medium, including but not limited to: wireless, wire, optical fiber, RF, etc., or any suitable combination thereof.

[0177] Computer program code for performing the operations of this invention can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, as well as conventional procedural programming languages—such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network, including a local area network (LAN) or a wide area network (WAN), or it can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0178] Those skilled in the art will understand that the modules or steps of the present invention described above can be implemented using general-purpose computing devices. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Optionally, they can be implemented using computer-executable program code, thereby allowing them to be stored in a storage device for execution by a computing device, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. Thus, the present invention is not limited to any particular combination of hardware and software.

[0179] Note that the above description is merely a preferred embodiment of the present invention and the technical principles employed. Those skilled in the art will understand that the present invention is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments, and may include many other equivalent embodiments without departing from the concept of the present invention, the scope of which is determined by the scope of the appended claims.

Claims

1. A data processing performance analysis method, applied to a custom plugin accessing an application server, wherein the custom plugin is implemented based on the MyBatis interceptor custom plugin principle, characterized in that... The data processing performance analysis method includes: When it is determined that the application service sends a data processing instruction to the target database, the data processing instruction is obtained, wherein the application service implements the target service function by sending the data processing instruction to the target database; According to the data processing instruction, obtain the association information of the target database in executing the data processing instruction; wherein, the association information includes: the third result obtained by executing the index query command of the associated table; The association information is sent to the performance analysis server so that the performance analysis server can determine the performance faults of the structured query language (SQL) corresponding to the data processing instruction based on the association information. Before determining the performance fault of the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information, the performance analysis server is also used for: The identification information of the application service is determined based on the associated information; Calculate the corresponding message digest MD5 value based on the data processing instructions and the identification information; Based on the MD5 value, determine whether the SQL corresponding to the data processing instruction has been analyzed for any existing performance faults. If not, continue to execute the operation of determining the performance faults of the Structured Query Language SQL corresponding to the data processing instruction based on the association information. The determination of performance faults in the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information includes: The data processing instructions are parsed using SQL syntax to extract the query fields; The third result included in the associated information is analyzed by a pre-set dynamic analysis chain to obtain the index field; The performance faults of the SQL are determined based on the query fields and the index fields.

2. The method according to claim 1, characterized in that, The step of obtaining the associated information for executing the data processing instruction in the target database according to the data processing instruction includes: Generate an associated command to obtain the associated information based on the data processing instructions; The association command is sent to the target database so that the target database obtains the association information by executing the association command. Receive the association information fed back from the target database.

3. The method according to claim 2, characterized in that, The associated commands include at least one of the following: Structured Query Language (SQL) execution plan commands, associated table structure query commands, and associated table index query commands.

4. The method according to claim 3, characterized in that, The associated information also includes at least one of the following: The data processing instructions, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the related table structure query command, and the identification information of the application service; The data processing instructions include at least one of the following: Data query commands, data insertion commands, and data modification commands.

5. The method according to claim 1, characterized in that, Sending the associated information to the performance analysis server includes: The associated information is sent to the performance analysis server via a message queue (MQ).

6. A data processing performance analysis method, applied to a performance analysis server, wherein the performance analysis server is responsible for maintaining the application services of the application server, the application server includes custom plugins independent of each application service, the custom plugins being implemented based on the MyBatis interceptor custom plugin principle, characterized in that... include: Receive associated information sent by a custom plugin, in which the target database executes a target data processing instruction, wherein the target data processing instruction is sent by the application service to the target database to realize the target service function of the application service; Based on the associated information, determine the performance faults in the Structured Query Language (SQL) corresponding to the target data processing instruction; The associated information includes: the third result obtained by executing the index query command of the associated table; Before determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information, the method further includes: The identification information of the application service is determined based on the associated information; Calculate the corresponding message digest MD5 value based on the target data processing instruction and the identification information; Based on the MD5 value, determine whether the SQL corresponding to the target data processing instruction has been analyzed for performance faults. If not, continue to execute the operation of determining the performance faults of the structured query language SQL corresponding to the target data processing instruction based on the association information. The step of determining the performance faults in the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information includes: The target data processing instructions are parsed using SQL syntax to extract the query fields; The third result included in the associated information is analyzed by a pre-set dynamic analysis chain to obtain the index field; The performance faults of the SQL are determined based on the query fields and the index fields.

7. The method according to claim 6, characterized in that, The associated information also includes at least one of the following: The data processing instructions, the first result obtained by executing the structured query language (SQL) execution plan command, the second result obtained by executing the related table structure query command, and the identification information of the application service; The target data processing instruction includes at least one of the following: Data query commands, data insertion commands, and data modification commands.

8. The method according to claim 7, characterized in that, The step of determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information also includes: The first result included in the associated information is analyzed by a preset dynamic analysis chain to obtain the analysis result; Based on the analysis results, the performance faults of the SQL were identified.

9. The method according to claim 8, characterized in that, The determination of performance faults in the SQL based on the analysis results includes: The index type used is determined by the index type field included in the analysis results; The performance issues with the SQL can be determined based on the type of index used.

10. The method according to claim 8, characterized in that, The step of determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information also includes: The analysis results are obtained by analyzing the first result, second result, and / or third result included in the associated information through a custom script added to the dynamic analysis chain.

11. The method according to any one of claims 6-10, characterized in that, Also includes: An optimization plan is generated based on the performance issues identified in the SQL statement.

12. A data processing performance analysis device, comprising a custom plugin installed on the access application server, wherein the custom plugin is implemented based on the MyBatis interceptor custom plugin principle, characterized in that, include: The first acquisition module is used to acquire the data processing instruction when it is determined that the application service sends a data processing instruction to the target database, wherein the application service implements the target service function by sending the data processing instruction to the target database; The second acquisition module is used to acquire the association information of the target database executing the data processing instruction according to the data processing instruction; wherein, the association information includes: the third result obtained by executing the index query command of the associated table; The sending module is used to send the association information to the performance analysis server, so that the performance analysis server can determine the performance fault of the structured query language SQL corresponding to the data processing instruction based on the association information; Before determining the performance fault of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information, the performance analysis server is further used for: The identification information of the application service is determined based on the associated information; Calculate the corresponding message digest MD5 value based on the target data processing instruction and the identification information; Based on the MD5 value, determine whether the SQL corresponding to the target data processing instruction has been analyzed for performance faults. If not, continue to execute the operation of determining the performance faults of the structured query language SQL corresponding to the target data processing instruction based on the association information. The determination of performance faults in the Structured Query Language (SQL) corresponding to the data processing instruction based on the association information includes: The data processing instructions are parsed using SQL syntax to extract the query fields; The third result included in the associated information is analyzed by a pre-set dynamic analysis chain to obtain the index field; The performance faults of the SQL are determined based on the query fields and the index fields.

13. A data processing performance analysis device, integrated into a performance analysis server, wherein the performance analysis server is responsible for maintaining the application services of the application server, and the application server includes custom plugins independent of each application service, the custom plugins being implemented based on the MyBatis interceptor custom plugin principle, characterized in that... include: The receiving module is used to receive the associated information sent by the custom plugin, which is the target database executing the target data processing instruction. The target data processing instruction is sent by the application service to the target database to realize the target service function of the application service. The analysis module is used to determine the performance faults of the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information. The associated information includes: the third result obtained by executing the index query command of the associated table; The verification module is used to determine the performance faults in the Structured Query Language (SQL) corresponding to the target data processing instruction based on the association information. The identification information of the application service is determined based on the associated information; Calculate the corresponding message digest MD5 value based on the target data processing instruction and the identification information; Based on the MD5 value, determine whether the SQL corresponding to the target data processing instruction has been analyzed for performance faults. If not, continue to execute the operation of determining the performance faults of the structured query language SQL corresponding to the target data processing instruction based on the association information. The analysis module includes: The parsing unit is used to parse the target data processing instructions using SQL syntax to extract query fields; The fourth analysis unit is used to analyze the third result included in the associated information through a preset dynamic analysis chain to obtain the index field; The determining unit is used to determine the performance faults of the SQL based on the query field and the index field.

14. A data processing performance analysis system, characterized in that, include: Application server and performance analysis server; The application server includes custom plugins independent of each application service, and the custom plugins are used to execute the data processing performance analysis method according to any one of claims 1-5. The performance analysis server is used to execute the data processing performance analysis method according to any one of claims 6-11.

15. An electronic device, characterized in that, The electronic device includes: One or more processors; Memory, used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the data processing performance analysis method steps as described in any one of claims 1-5 and 6-11.

16. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by the processor, the program implements the data processing performance analysis method steps as described in any one of claims 1-5 and 6-11.

Images