CN110413516B

CN110413516B - Method and device for identifying slow SQL codes and electronic equipment

Info

Publication number: CN110413516B
Application number: CN201910636921.XA
Authority: CN
Inventors: 王吉
Original assignee: Hangzhou Dt Dream Technology Co Ltd
Current assignee: Hangzhou Dt Dream Technology Co Ltd
Priority date: 2019-07-15
Filing date: 2019-07-15
Publication date: 2023-08-25
Anticipated expiration: 2039-07-15
Also published as: CN110413516A

Abstract

The embodiment of the application provides a method and a device for identifying slow SQL codes and electronic equipment, wherein the method is based on a distributed application system, and comprises the following steps: aiming at an interface in a data access layer in a multi-layer architecture of target software, creating test cases of each SQL code called by the interface for the interface, and constructing test data for the test cases; executing the test case, and obtaining an execution log generated after the test case is executed; screening target execution logs with execution time length larger than a threshold value from the execution logs; and determining the SQL code of the test case corresponding to the target execution log as a slow SQL code.

Description

Method and device for identifying slow SQL codes and electronic equipment

Technical Field

The embodiment of the application relates to the technical field of Internet, in particular to a method and a device for identifying slow SQL codes and electronic equipment.

Background

In software programming, slow SQL may be generated due to the programming capabilities of the developer and frequent iterations of the business. The slow SQL refers to that after the application runs a period of service, part of SQL frequently performs I/O operation on the database, so that the part of SQL is executed to occupy a CPU for a long time. For applications, a small amount of slow SQL may not affect the normal operation of the service, but as slow SQL increases to a certain extent, it eventually affects the execution of other service functions in the application.

Disclosure of Invention

The embodiment of the specification provides a method and a device for identifying slow SQL codes and electronic equipment:

according to a first aspect of embodiments of the present specification, there is provided a method of identifying slow SQL code, the method being based on a distributed application system, the method comprising:

aiming at an interface in a data access layer in a multi-layer architecture of target software, creating test cases of each SQL code called by the interface for the interface, and constructing test data for the test cases;

executing the test case, and obtaining an execution log generated after the test case is executed;

screening target execution logs with execution time length larger than a threshold value from the execution logs;

and determining the SQL code of the test case corresponding to the target execution log as a slow SQL code.

According to a second aspect of embodiments of the present specification, there is provided an apparatus for identifying slow SQL code, the apparatus being based on a distributed application system, the apparatus comprising:

the creating unit is used for creating test cases of each SQL code called by the interface for the interface in the data access layer in the multi-layer architecture of the target software and constructing test data for the test cases;

the test unit executes the test case and acquires an execution log generated after the test case is executed;

the screening unit is used for screening target execution logs with the execution time length larger than a threshold value from the execution logs;

and the determining unit is used for determining the SQL code of the test case corresponding to the target execution log as a slow SQL code.

According to a third aspect of embodiments of the present specification, there is provided an electronic device comprising:

a processor;

a memory for storing processor-executable instructions;

wherein the processor is configured to identify the method of slow SQL code according to any of the above.

The embodiment of the specification provides a scheme for identifying slow SQL, which takes an interface in a DAO layer as a unit test, and creates test cases and test data for the interface; because the DAO layer does not embody service attributes, unit test can be free from external system triggering and service data constraint, so that test flexibility and low cost can be realized; based on the DAO layer unit test mode, the test case design stage can freely construct a large amount of data, and the SQL code can be subjected to branch coverage test through the input of control parameters, so that the test process can be free from the limitation of test equipment; meanwhile, the identification efficiency and accuracy can be improved by adopting the automatic execution test case and the standardized screening (screening the target execution log with the length larger than the threshold value during the execution).

Drawings

FIG. 1 is a flow chart of a method of identifying slow SQL code provided by an embodiment of the application;

FIG. 2 is a diagram of a test framework for identifying slow SQL code provided by an embodiment of the present disclosure;

FIG. 3 is a hardware block diagram of an apparatus for recognizing slow SQL code according to an embodiment of the application;

FIG. 4 is a block diagram of an apparatus for identifying slow SQL code according to an embodiment of the application.

Detailed Description

Reference will now be made in detail to exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, the same numbers in different drawings refer to the same or similar elements, unless otherwise indicated. The implementations described in the following exemplary examples do not represent all implementations consistent with the present specification. Rather, they are merely examples of apparatus and methods consistent with some aspects of the present description as detailed in the accompanying claims.

The terminology used in the description presented herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the description. As used in this specification and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term "and/or" as used herein refers to and encompasses any or all possible combinations of one or more of the associated listed items.

It should be understood that, although the terms first, second, etc. may be used in this specification to describe various information, these information should not be limited by these terms. These terms are only used to distinguish one type of information from another. For example, the first information may also be referred to as second information, and similarly, the second information may also be referred to as first information, without departing from the scope of the present description. The word "if" as used herein may be interpreted as "at … …" or "at … …" or "responsive to a determination", depending on the context.

As previously mentioned, in software programming, slow SQL may be generated due to the programming capabilities of the developer and frequent iterations of the business. The slow SQL refers to that after the application runs a period of service, part of SQL frequently performs I/O operation on the database, so that the part of SQL is executed to occupy a CPU for a long time. For applications, a small amount of slow SQL may not affect the normal operation of the service, but as slow SQL increases to a certain extent, it eventually affects the execution of other service functions in the application.

Slow SQL is usually unrecognizable during the programming phase and typically comes out after the software is put on-line with an increase in business data. Thus, it is often not until a software application is brought online for a period of time in a distributed system, where it is necessary to locate and modify the slow SQL in the application source code before it is brought online. Thus, the service of the old application on line needs to be interrupted, and the new application is uploaded, which definitely causes harm to the normal operation of the service.

For this reason, a scheme for identifying slow SQL codes in advance is required.

In the related art, slow SQL is usually checked before application is brought online. Mainly relies on two methods, manual review and pressure testing.

The manual review is utilized to find the slow SQL, and the manual review is strongly dependent on the skills and experience of the reviewer for writing the SQL statement. Unified standards cannot be realized, and the manual existence efficiency is low and the cost is high; and the problem of instability is that missed examination and wrong examination are possible.

When the pressure test is utilized to find out the slow SQL, the test coverage rate is limited by the performance of the test equipment. The stronger the device performance, the more slow SQL can be found, and vice versa; since the test equipment cannot be increased without limit, the slow SQL cannot be completely identified through pressure test in general, and only a part of the slow SQL can be identified. And the period of the stress test is long, often requiring a period of months, which is unacceptable for applications where the application iterations are fast.

In order to solve the problems, the application provides a scheme for identifying the slow SQL codes, which can identify, early warn and collect the slow SQL codes without depending on the SQL skills of people and being limited by test equipment. Meanwhile, the scheme can be automatically executed, and repeated execution is carried out in the continuous release of the software to achieve the purpose of regression testing to ensure the quality baseline of the software.

The following description will be made with reference to a method for identifying slow SQL codes shown in FIG. 1, which may be based on a distributed application system, and which may include the steps of:

step 110: aiming at an interface in a data access layer in a multi-layer architecture of target software, creating test cases of each SQL code called by the interface for the interface, and constructing test data for the test cases.

In practical application, the slow SQL is generally caused by frequently operating the database, and it is seen that the business operation corresponding to the slow SQL is often SQL with more frequent use amount; therefore, the amount of data corresponding to slow SQL is typically not small. For this purpose, it is possible to identify only those interfaces with a large traffic data volume; without having to be directed to all interfaces; therefore, the service complexity is reduced, and the test resources can be correspondingly reduced.

In an embodiment, the interface of the data access layer is an interface meeting a preset condition;

the preset conditions include that the preset conditions include,

the amount of data in the data table called by the interface is greater than the threshold.

In the application, when designing a database for software, a developer usually evaluates which data tables have larger data volume according to service characteristics; the test environment can thus be configured for the DAOs (data access layers, data Access Object) to which these tables correspond.

The following may be understood in conjunction with the test framework diagram of FIG. 2 identifying a slow SQL code schema.

The DAO layer is a data access layer, and mainly collects basic database operations by calling SQL sentences (one or a group) and encapsulates the basic database operations to provide a calling interface for the outside. Meanwhile, the DAO layer does not embody service attributes. Any slow SQL problem can find an interface to the DAO layer.

The DAO layer interface is typically tested and test cases may not be constrained by external system triggers and business data.

For this purpose, test cases of the respective SQL code can be created for the interfaces in the DAO layer and test data can be constructed for the test cases for the target software. One interface may correspond to multiple test cases, but one test case may correspond to only one interface. The test data can be extracted from a database of the service; after the test case is executed, a corresponding SQL execution log is generated; based on the SQL execution time of the SQL execution log record, which SQL codes are slow SQL codes can be identified and determined.

In one embodiment, the test cases for SQL code that creates the interface call for the interface are presented:

and adding test cases covering the SQL codes called by the interface for each interface by taking the interface provided by the DAO layer as a unit. Each interface operation needs to call SQL codes; to this end, test cases may be created for each SQL code that the interface executes the required call.

For example, a service for real-name authentication result query; the inputs to the test case may be the user's name and identification number.

Test case design: the test case calls a query interface of the DAO layer to query a system real-name authentication account library. The framework of the DAO layer directly calls the SQL statement as the test target to query the database. And inquiring whether the user passes the real-name authentication according to the ID card number and the name. If the real-name information is not found, returning a return code of 'real-name authentication failed'; if the real-name information is found, a return code of 'real-name authentication passed' is returned.

In one embodiment, the description is directed to building test data for the test case:

after the test case is created, test data also needs to be built for execution of the test case. Specifically, a part of service data may be pulled from the historical service data stored in the database as test data through an automation script.

The test data comprises normal service data and abnormal service data;

when executing the test case, if the interface calls normal service data, returning to a normal test result; if the interface calls the abnormal service data, a test result indicating the abnormality is returned.

Still for the service of real-name authentication result query, for example: service data of user real-name authentication with the weight of more than 100W can be pulled in a system real-name authentication account library. The traffic data may contain normal traffic data (personnel information for which 50W has passed real name authentication), and abnormal traffic data (data for which 50W has not passed real name authentication).

Step 120: and executing the test case, and obtaining an execution log generated after the test case is executed.

After test cases of the respective SQL codes are created for the interfaces of the target software and test data required for test case testing are constructed, testing can be started. Specifically, each test case is executed through a test script, and a corresponding execution log is generally generated after the execution of each test case is finished. That is, each execution log uniquely corresponds to a test case. One test case may correspond to multiple execution logs. It should be noted that, the unique identifier of the test case is recorded in the execution log to locate the corresponding test case.

Step 130: and screening target execution logs with execution time length larger than a threshold value from the execution logs.

Step 140: and determining the SQL code of the test case corresponding to the target execution log as a slow SQL code.

After the execution logs generated after the test case is executed are obtained, whether the slow SQL exists or not can be judged according to the execution duration of the execution logs.

Generally, the execution time of the slow SQL is long, so that the filtering can be performed by setting a threshold value. When the execution duration of a certain execution log is greater than the threshold value, the SQL code corresponding to the execution log is a slow SQL code; and when the execution duration of a certain execution log is not greater than the threshold value, the SQL code corresponding to the execution log is not a slow SQL code.

The threshold value may be an empirical value set in advance.

With the continued development of computer technology, and in particular with the advancement of artificial intelligence, the threshold may also be calculated by machine learning. For example, based on the historically determined slow SQL, the execution time of these slow SQL is counted, and then an optimal threshold can be calculated by a machine learning algorithm.

Furthermore, the threshold may also be calculated based on big data techniques. For example, with massive data, it is found that most of the slow SQL is executed for longer than 3 seconds, and then the threshold in the present application can be set to 3 seconds.

After determining which SQL codes are slow SQL codes, any one or a combination of the following steps can also be performed:

1. collecting the slow SQL codes and providing the slow SQL codes for a tester to process.

In this step, after the system identifies the slow SQL codes, the system may record information of these slow SQL codes, for example, record location information (which part of code blocks are located) of the slow SQL codes, slow SQL execution duration, etc., and send the slow SQL codes and/or related information to the testing party, so that the testing personnel can process the slow SQL codes.

2. Counting the quantity of the slow SQL codes, and sending an early warning notice to a testing party when the quantity of the slow SQL codes reaches a quantity threshold.

Typically, a small amount of slow SQL may not have a significant impact on the normal execution of the business. In the step, the system notifies the testing party not by identifying the slow SQL, but when the accumulated and identified slow SQL code quantity reaches a quantity threshold value, the system notifies the testing party; uniformly processing the batch of slow SQL by a tester; therefore, the processing efficiency of the testers can be improved, and the situation that the testers need to process slow SQL at any time can be avoided.

3. And counting the total time length of the execution time length of the slow SQL codes, and sending an early warning notice to a testing party when the total time length reaches a time length threshold value.

Typically, the execution time of each slow SQL, while timed out, is not significant for the entire business process. In the step, the system notifies the testing party not by identifying the slow SQL, but when the total time length of the accumulated identified slow SQL code execution time length reaches a time length threshold value, notifying the testing party again; uniformly processing the batch of slow SQL by a tester; therefore, the processing efficiency of the testers can be improved, and the situation that the testers need to process slow SQL at any time can be avoided.

In one embodiment, after testing and identifying slow SQL for the target software, test-related test cases may be persisted and used for subsequent slow SQL identification for the same software.

The system can persist test cases of interfaces within the data access layer in the multi-layer architecture of the target software.

Typically, each piece of software may contain a unique identification and version number; namely, the target software comprises a unique identifier and a version number;

when the test cases are persisted, each test case may be associated with a unique identifier of the target software; or a unique identification and version number of the target software are associated;

when the target software is iteratively updated, slow SQL identification is required to be carried out on the new version of the target software; at this time, since the old version has been previously identified, the unique identification of the new version and the old version is unchanged;

therefore, after receiving the software with the same unique identification, judging whether the version number is the latest version number;

if the version number is the latest version number, obtaining a persistent test case corresponding to the unique identifier;

when the version number of the current target software is the latest version number, directly acquiring a persistent test case with the same unique identifier;

and executing the test cases by using the test cases, and obtaining an execution log generated after the test cases are executed. Thereby omitting the process of re-creating test case construction test data.

In the embodiment, the persistent test case is reused before the target software of the subsequent new version is released, so that the same slow SQL generated by the change of the new service can be found in time.

By the embodiment, the identification, early warning and collection of the slow SQL can be performed without depending on the SQL skill of a person and without being limited by test equipment. Meanwhile, the scheme can be automatically executed, and repeated execution is carried out in the continuous release of the software to achieve the purpose of regression testing to ensure the quality baseline of the software.

On one hand, the slow SQL code recognition scheme provided by the application can be automatically executed without being limited by test equipment; not only improves the recognition efficiency, but also reduces the recognition cost.

On the other hand, in a mode of taking the interface in the DAO layer as a unit test, since the DAO layer does not embody service attributes, the unit test case is not constrained by external system triggering and service data; thus providing flexibility and low cost advantages.

On the other hand, based on the DAO layer unit test mode, the test case design stage can freely construct a large amount of data, and the SQL code can be subjected to branch coverage test through the input of control parameters. In particular, the method can cover the situation of strong dependence on external factors, such as dependence on massive user registration numbers, SQL branches which can be called under abnormal conditions, and the like. These are not built up scenarios during the performance testing phase. Therefore, the scheme of the application has higher recognition coverage rate for the slow SQL, and can generally find the slow SQL with the rate of more than 90 percent.

In yet another aspect, the DAO layer-based unit test essence is a set of test codes. The test case can be used as a technical asset repeatedly. Multiplexing in subsequent project iterations thereby shortens the period of slow SQL discovery. Meanwhile, the automatic test case can be integrated in a continuously integrated system, so that the quality of software is ensured not to be degraded due to iteration of requirements.

It should be noted that the process of identifying slow SQL codes provided by the application can be in the encoding stage or the testing stage. When the process of identifying the slow SLQ advances to the encoding stage (i.e., before code is submitted), a part of system test contents are advanced, so that the time cost and the test cost of the target software in the system test stage can be greatly reduced by the advanced test.

Corresponding to the foregoing method embodiments for identifying slow SQL codes, the present specification also provides embodiments of an apparatus for identifying slow SQL codes. The embodiment of the device can be implemented by software, or can be implemented by hardware or a combination of hardware and software. Taking a software implementation as an example, the device in a logic sense is formed by reading corresponding computer service program instructions in the nonvolatile memory into the memory by the processor of the device where the device is located for operation. In terms of hardware, as shown in fig. 3, a hardware structure diagram of a device where a device for identifying a slow SQL code in this specification is located is shown, and in addition to a processor, a network interface, a memory and a nonvolatile memory shown in fig. 3, the device where the device is located in an embodiment generally includes other hardware according to an actual function of identifying the slow SQL code, which is not described herein again.

Referring to fig. 4, a block diagram of an apparatus for identifying slow SQL codes according to an embodiment of the present disclosure corresponds to the embodiment shown in fig. 1, and includes:

the creating unit 310 creates test cases of each SQL code when the interface is called for the interface aiming at the interface in the data access layer in the multi-layer architecture of the target software, and builds test data for the test cases; the SQL codes are SQL codes which are associated with the calling interface and positioned in a service logic layer;

a test unit 320, configured to execute the test case, and obtain an execution log generated after the test case is executed;

a screening unit 330 for screening the execution log of the target whose execution time length is greater than a threshold value from the execution logs;

the determining unit 340 determines the SQL code of the test case corresponding to the target execution log as a slow SQL code.

Optionally, the apparatus further includes:

the collection unit is used for collecting the slow SQL codes and providing the slow SQL codes for a testing party for processing.

Optionally, the apparatus further includes:

and the quantity counting unit counts the quantity of the slow SQL codes, and when the quantity of the slow SQL codes reaches a quantity threshold value, the quantity counting unit sends an early warning notice to the testing party.

Optionally, the apparatus further includes:

and the duration statistics unit is used for counting the total duration of the execution duration of the slow SQL codes, and sending an early warning notice to the testing party when the total duration reaches a duration threshold value.

Optionally, the interface of the data access layer is an interface meeting preset conditions;

the preset conditions include that the preset conditions include,

Optionally, the test data includes normal service data and abnormal service data;

in the test unit, when the test case is executed, if the interface calls normal service data, returning a normal test result; if the interface calls the abnormal service data, a test result indicating the abnormality is returned.

Optionally, the apparatus further includes:

and the persistence unit is used for persistence of the test case.

Optionally, the target software includes a unique identifier and a version number; before the creation unit, further comprising:

the judging subunit judges whether the version number is the latest version number or not after receiving the software with the same unique identifier;

the obtaining subunit is used for obtaining the persistent test case corresponding to the unique identifier if the version number is the latest version number;

and the multiplexing subunit repeatedly executes the execution unit by using the persistent test case.

The system, apparatus, module or unit set forth in the above embodiments may be implemented in particular by a computer chip or entity, or by a product having a certain function. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular telephone, camera phone, smart phone, personal digital assistant, media player, navigation device, email device, game console, tablet computer, wearable device, or a combination of any of these devices.

The implementation process of the functions and roles of each unit in the above device is specifically shown in the implementation process of the corresponding steps in the above method, and will not be described herein again.

For the device embodiments, reference is made to the description of the method embodiments for the relevant points, since they essentially correspond to the method embodiments. The apparatus embodiments described above are merely illustrative, wherein the elements illustrated as separate elements may or may not be physically separate, and the elements shown as elements may or may not be physical elements, may be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules may be selected according to actual needs to achieve the purposes of the present description. Those of ordinary skill in the art will understand and implement the present application without undue burden.

FIG. 4 above depicts an internal functional block and architectural illustration of an apparatus for identifying slow SQL code, the substantial execution subject of which may be an electronic device comprising:

a processor;

a memory for storing processor-executable instructions;

wherein the processor is configured to:

aiming at an interface in a data access layer in a multi-layer architecture of target software, creating test cases of each SQL code when the interface is called for the interface, and constructing test data for the test cases; the SQL codes are SQL codes which are associated with the calling interface and positioned in a service logic layer;

Optionally, the method further comprises:

collecting the slow SQL codes and providing the slow SQL codes for a tester to process.

Optionally, the method further comprises:

counting the quantity of the slow SQL codes, and sending an early warning notice to a testing party when the quantity of the slow SQL codes reaches a quantity threshold.

Optionally, the method further comprises:

and counting the total time length of the execution time length of the slow SQL codes, and sending an early warning notice to a testing party when the total time length reaches a time length threshold value.

the preset conditions include that the preset conditions include,

Optionally, the method further comprises:

and persisting the test case.

Optionally, the target software includes a unique identifier and a version number;

after receiving the software with the same unique identifier, judging whether the version number is the latest version number;

and executing the test case and obtaining an execution log generated after the test case is executed.

In the above embodiment of the electronic device, it should be understood that the processor may be a central processing unit (english: central Processing Unit, abbreviated as CPU), or may be other general purpose processors, digital signal processors (english: digital Signal Processor, abbreviated as DSP), application specific integrated circuits (english: application Specific Integrated Circuit, abbreviated as ASIC), or the like. A general-purpose processor may be a microprocessor or the processor may be any conventional processor, etc., and the aforementioned memory may be a read-only memory (ROM), a random access memory (random access memory, RAM), a flash memory, a hard disk, or a solid state disk. The steps of a method disclosed in connection with the embodiments of the present application may be embodied directly in a hardware processor for execution, or in a combination of hardware and software modules in the processor for execution.

In this specification, each embodiment is described in a progressive manner, and identical and similar parts of each embodiment are all referred to each other, and each embodiment mainly describes differences from other embodiments. In particular, for the electronic device embodiments, since they are substantially similar to the method embodiments, the description is relatively simple, and reference is made to the description of the method embodiments in part.

Other embodiments of the present disclosure will be apparent to those skilled in the art from consideration of the specification and practice of the application disclosed herein. This specification is intended to cover any variations, uses, or adaptations of the specification following, in general, the principles of the specification and including such departures from the present disclosure as come within known or customary practice within the art to which the specification pertains. It is intended that the specification and examples be considered as exemplary only, with a true scope and spirit of the specification being indicated by the following claims.

It is to be understood that the present description is not limited to the precise arrangements and instrumentalities shown in the drawings, which have been described above, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present description is limited only by the appended claims.

Claims

1. A method of identifying slow SQL code, the method being based on a distributed application system, the method comprising:

aiming at an interface meeting preset conditions in a data access layer in a multi-layer architecture of target software, creating test cases of each SQL code called by the interface for the interface, and constructing test data for the test cases; the interface meeting the preset conditions comprises an interface with the data quantity in the called data table being larger than a threshold value;

2. The method according to claim 1, wherein the method further comprises:

3. The method according to claim 1, wherein the method further comprises:

4. The method according to claim 1, wherein the method further comprises:

5. The method of claim 1, wherein the test data comprises normal traffic data and abnormal traffic data;

6. The method according to claim 1, wherein the method further comprises:

and persisting the test case.

7. The method of claim 6, wherein the target software includes a unique identifier and a version number;

8. An apparatus for identifying slow SQL code, the apparatus being based on a distributed application system, the apparatus comprising:

the method comprises the steps that a creating unit is used for creating test cases of each SQL code called by an interface according to preset conditions in a data access layer of a multi-layer architecture of target software for the interface and building test data for the test cases; the interface meeting the preset conditions comprises an interface with the data quantity in the called data table being larger than a threshold value;

9. The apparatus of claim 8, wherein the apparatus further comprises:

10. The apparatus of claim 8, wherein the apparatus further comprises:

11. The apparatus of claim 8, wherein the apparatus further comprises:

12. The apparatus of claim 8, wherein the test data comprises normal traffic data and abnormal traffic data;

13. The apparatus of claim 8, wherein the apparatus further comprises:

and the persistence unit is used for persistence of the test case.

14. The apparatus of claim 13, wherein the target software includes a unique identifier and a version number; before the creation unit, further comprising:

15. An electronic device, comprising:

a processor;

a memory for storing processor-executable instructions;

wherein the processor is configured to the method of any of the preceding claims 1-7.