SQL statement performance prediction method, device, equipment, and computer program

Abstract

The present invention relates to the field of computer technology and discloses a method, apparatus, device, and computer program for predicting the performance of SQL statements. The present invention inputs the collected indicator data of SQL statements that have been launched and SQL statements to be launched into a trained SQL performance profile analysis model to obtain performance prediction results for the SQL statements that have been launched and the SQL statements to be launched. The final performance prediction results for the SQL statements to be launched are then determined by combining the similarity and performance prediction results of the two, thereby greatly improving the accuracy of SQL statement performance predictions. Furthermore, the present invention predicts the performance of SQL statements before they are launched, avoiding failures after the SQL statements are launched and significantly improving the performance and stability of the database.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a SQL statement performance prediction method, an SQL statement performance prediction device, an SQL statement performance prediction equipment, and a computer program. Background Art

[0002] With the rapid development of cloud computing, application systems are rapidly and continuously iterating, and business systems are placing increasingly higher demands on database platform performance. This is especially true for cloud computing in important industries such as telecommunications and finance, where core business systems run on cloud computing platform databases. Core business systems with high performance and stability requirements iterate quickly, requiring consideration of database performance and throughput. The vast majority of factors affecting database performance are due to the poor quality of SQL (Structured Query Language) statements. However, the accuracy of SQL statement performance prediction is currently very low, significantly reducing database stability.

[0003] Therefore, how to improve the performance prediction accuracy of SQL statements is an urgent problem to be solved. Summary of the Invention

[0004] The main purpose of the present invention is to provide a method and apparatus, device, and computer program for predicting SQL statement performance, aiming to improve the accuracy of performance prediction of SQL statements.

[0005] To achieve the above object, the present invention provides a method for predicting SQL statement performance, which includes the following steps:

[0006] Collect indicator data of SQL statements that have been launched and those to be launched;

[0007] Based on the indicator data and the trained SQL performance profile analysis model, performance prediction is performed on the SQL statements that have been launched and the SQL statements to be launched to obtain performance prediction results;

[0008] Performing similarity judgment on the SQL statement that has been put online and the SQL statement to be put online to obtain a similarity judgment result;

[0009] A target performance prediction result of the SQL statement to be put online is determined according to the similarity judgment result and the performance prediction result.

[0010] Optionally, the step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results includes:

[0011] Inputting the indicator data of the online SQL statement into the SQL performance profile analysis model to obtain a first performance prediction result; wherein the SQL performance profile analysis model includes static indicator features and dynamic indicator features of the SQL statement;

[0012] Input the indicator data of the SQL statement to be put online into the SQL performance profile analysis model to obtain a second performance prediction result;

[0013] The step of determining a target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result includes:

[0014] A target performance prediction result of the SQL statement to be put online is determined according to the similarity judgment result, the first performance prediction result, and the second performance prediction result.

[0015] Optionally, the step of determining a target performance prediction result of the SQL statement to be put online based on the similarity judgment result, the first performance prediction result, and the second performance prediction result includes:

[0016] If the similarity judgment result is within a first preset similarity range, determining the first performance prediction result as the target performance prediction result of the SQL statement to be put online;

[0017] If the similarity judgment result is within a second preset similarity range, an average value of the first performance prediction result and the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online;

[0018] If the similarity judgment result is within a third preset similarity range, determining the second performance prediction result as the target performance prediction result of the SQL statement to be put online;

[0019] The minimum value of the first preset similarity interval is greater than the maximum value of the second preset similarity interval, and the minimum value of the second preset similarity interval is greater than the maximum value of the third preset similarity interval.

[0020] Optionally, the step of performing similarity judgment on the online SQL statement and the SQL statement to be online to obtain a similarity judgment result includes:

[0021] Performing vectorization processing on the SQL statement that has been put online and the SQL statement that is to be put online respectively to obtain a vector expression of the SQL statement that has been put online and a vector expression of the SQL statement that is to be put online;

[0022] Obtaining a distance result between the online SQL statement and the to-be-online SQL statement based on the vector expression of the online SQL statement and the vector expression of the to-be-online SQL statement;

[0023] The similarity between the online SQL statement and the SQL statement to be online is determined according to the distance result to obtain the similarity judgment result.

[0024] Optionally, the indicator data includes:

[0025] At least one of static indicator data and dynamic indicator data;

[0026] The static indicator data includes: at least one of an object and a text writing method;

[0027] The dynamic indicator data includes at least one of physical reads, logical reads, concurrency, statistical information, execution plan, and time consumption.

[0028] Optionally, before performing the step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model, and obtaining the performance prediction results, the SQL statement performance prediction method further includes:

[0029] Obtain indicator data for SQL statement training samples;

[0030] The SQL performance profile analysis model is trained according to the indicator data of the SQL statement training sample to obtain a trained SQL performance profile analysis model.

[0031] Optionally, before performing the step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model, and obtaining the performance prediction results, the SQL statement performance prediction method further includes:

[0032] Cleaning abnormal indicator data in the indicator data to obtain cleaned indicator data;

[0033] Based on the identifiers of the online SQL statements, the online SQL statements with the same identifier are classified as the same online SQL statement;

[0034] According to the identifiers of the SQL statements to be put online, the SQL statements to be put online with the same identifiers are classified as the same SQL statements to be put online;

[0035] The step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results includes:

[0036] Based on the cleaned indicator data and the trained SQL performance profile analysis model, performance prediction is performed on multiple identical SQL statements that have been launched and multiple identical SQL statements to be launched to obtain performance prediction results.

[0037] In addition, to achieve the above-mentioned purpose, the present invention further provides a SQL statement performance prediction device, the SQL statement performance prediction device comprising:

[0038] The collection module is used to collect indicator data of SQL statements that have been launched and SQL statements to be launched;

[0039] A performance prediction module is used to perform performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results;

[0040] A judgment module, configured to perform similarity judgment on the SQL statement that has been put online and the SQL statement to be put online, and obtain a similarity judgment result;

[0041] A determination module is used to determine a target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result.

[0042] In addition, to achieve the above-mentioned purpose, the present invention also provides a SQL statement performance prediction device, which includes: a memory, a processor, and a SQL statement performance prediction program stored in the memory and running on the processor. When the SQL statement performance prediction program is executed by the processor, the steps of the SQL statement performance prediction method as described above are implemented.

[0043] In addition, to achieve the above-mentioned purpose, the present invention also provides a computer program, which stores a SQL statement performance prediction program. When the SQL statement performance prediction program is executed by a processor, it implements the steps of the SQL statement performance prediction method as described above.

[0044] The technical solution provided by the present invention collects indicator data of SQL statements that have been launched and SQL statements to be launched; and performs performance prediction on the SQL statements that have been launched and the SQL statements to be launched respectively based on the indicator data and the SQL performance portrait analysis model obtained through training to obtain performance prediction results; at the same time, similarity judgment is performed on the SQL statements that have been launched and the SQL statements to be launched to obtain similarity judgment results; and then, based on the similarity judgment results and the performance prediction results, a target performance prediction result for the SQL statements to be launched is determined; thereby solving the problem of low performance prediction accuracy of SQL statements in the prior art.

[0045] That is, the technical solution provided by the present invention inputs the collected indicator data of the SQL statements that have been put online and the SQL statements to be put online into the trained SQL performance portrait analysis model to obtain the performance prediction results of the SQL statements that have been put online and the SQL statements to be put online, and then combines the similarity and performance prediction results of the two to jointly determine the final performance prediction results of the SQL statements to be put online, thereby greatly improving the performance prediction accuracy of the SQL statements; at the same time, the present invention performs performance prediction on the SQL statements before they are put online, avoiding the phenomenon of failure of the SQL statements after they are put online, and greatly improving the performance and stability of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0046] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the structures shown in these drawings without paying any creative work.

[0047] Figure 1 This is a schematic diagram of the structure of an SQL statement performance prediction device in a hardware operating environment according to an embodiment of the present invention;

[0048] Figure 2 This is a flow chart of the first embodiment of the SQL statement performance prediction method of the present invention;

[0049] Figure 3 This is a flow chart of a second embodiment of the SQL statement performance prediction method of the present invention;

[0050] Figure 4 This is a flow chart of a third embodiment of the SQL statement performance prediction method of the present invention;

[0051] Figure 5 This is a flow chart of a fourth embodiment of the SQL statement performance prediction method of the present invention;

[0052] Figure 6This is a structural block diagram of the first embodiment of the SQL statement performance prediction device of the present invention.

[0053] The purpose, features and advantages of the present invention will be further described with reference to the accompanying drawings and in conjunction with the embodiments. DETAILED DESCRIPTION

[0054] It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention.

[0055] See Figure 1 As shown, Figure 1 This is a schematic diagram of the SQL statement performance prediction device structure in the hardware operating environment involved in the embodiment of the present invention.

[0056] The SQL statement performance prediction device includes: at least one processor 101, a memory 102, and an SQL statement performance prediction program stored in the memory and executable on the processor. The SQL statement performance prediction program is configured to implement the steps of the SQL statement performance prediction method of any of the following embodiments.

[0057] Processor 101 may include one or more processing cores, such as a quad-core processor or an octa-core processor. Processor 101 may be implemented in at least one of the following hardware forms: DSP (Digital Signal Processing), FPGA (Field-Programmable Gate Array), and PLA (Programmable Logic Array). Processor 101 may also include a main processor and a coprocessor. The main processor is a processor for processing data in the awake state, also known as a CPU (Central Processing Unit); the coprocessor is a low-power processor for processing data in the standby state. In some embodiments, processor 101 may be integrated with a GPU (Graphics Processing Unit), which is responsible for rendering and drawing the content to be displayed on the display screen. Processor 101 may also include an AI (Artificial Intelligence) processor, which is used to process operations related to the SQL statement performance prediction model training method, so that the SQL statement performance prediction model training method model can be trained and learned autonomously to improve efficiency and accuracy.

[0058] Memory 102 may include one or more computer programs, which may be non-transitory. Memory 102 may also include high-speed random access memory and non-volatile memory, such as one or more disk storage devices and flash storage devices. In some embodiments, the non-transitory computer program in memory 102 is used to store at least one instruction, which is executed by processor 101 to implement the SQL statement performance prediction model training method provided in the method embodiment of the present application.

[0059] In some examples, the SQL statement performance prediction device may optionally include a communication interface 103 and at least one peripheral device. The processor 101, memory 102, and communication interface 103 may be connected via a bus or signal lines. Each peripheral device may be connected to the communication interface 103 via a bus, signal lines, or circuit boards. Specifically, the peripheral device may include at least one of a radio frequency circuit 104, a display screen 105, and a power supply 106.

[0060] The communication interface 103 can be used to connect at least one I / O (Input / Output)-related peripheral device to the processor 101 and the memory 102. In some embodiments, the processor 101, the memory 102, and the communication interface 103 are integrated on the same chip or circuit board; in some other embodiments, any one or two of the processor 101, the memory 102, and the communication interface 103 can be implemented on separate chips or circuit boards, which is not limited in this embodiment.

[0061] The RF circuit 104 is used to receive and transmit RF (Radio Frequency) signals, also known as electromagnetic signals. The RF circuit 104 communicates with communication networks and other communication devices via electromagnetic signals. The RF circuit 104 converts electrical signals into electromagnetic signals for transmission, or converts received electromagnetic signals into electrical signals. Optionally, the RF circuit 104 includes: an antenna system, an RF transceiver, one or more amplifiers, a tuner, an oscillator, a digital signal processor, a codec chipset, a user identity module card, etc. The RF circuit 104 can communicate with other terminals via at least one wireless communication protocol. Such wireless communication protocols include, but are not limited to, metropolitan area networks, various generations of mobile communication networks (2G, 3G, 4G, and 5G), wireless local area networks, and / or WiFi (Wireless Fidelity) networks. In some embodiments, the RF circuit 104 may also include circuits related to NFC (Near Field Communication), which is not limited in this application.

[0062] Display screen 105 is used to display a user interface (UI). This UI may include graphics, text, icons, videos, or any combination thereof. When display screen 105 is a touchscreen display, it is also capable of collecting touch signals on or above the surface of display screen 105. These touch signals can be input as control signals to processor 101 for processing. Display screen 105 can also be used to provide virtual buttons and / or a virtual keyboard, also known as soft buttons and / or a soft keyboard. In some embodiments, display screen 105 can be a single display screen, serving as the front panel of the SQL statement performance prediction device. In other embodiments, display screen 105 can be at least two, positioned on different surfaces of the SQL statement performance prediction device or in a foldable design. In some embodiments, display screen 105 can be a flexible display screen, positioned on a curved or foldable surface of the SQL statement performance prediction device. Display screen 105 can also be configured as a non-rectangular, irregular shape, also known as a special-shaped screen. Display screen 105 can be made of materials such as LCD (Liquid Crystal Display) and OLED (Organic Light-Emitting Diode).

[0063] Power supply 106 is used to power various components in the SQL statement performance prediction device. Power supply 106 can be AC ​​power, DC power, a disposable battery, or a rechargeable battery. When power supply 106 includes a rechargeable battery, the rechargeable battery can support wired charging or wireless charging. The rechargeable battery can also be used to support fast charging technology.

[0064] Those skilled in the art will understand that Figure 1 The structure shown in the figure does not constitute a limitation on the SQL statement performance prediction device, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0065] Based on the above hardware structure, various embodiments of the present invention are proposed.

[0066] See Figure 2 As shown, Figure 2 This is a flow chart of a first embodiment of a method for predicting SQL statement performance according to the present invention. The method for predicting SQL statement performance includes the following steps:

[0067] Step S201: Collect indicator data of SQL statements that have been launched and SQL statements to be launched.

[0068] The online SQL statements in this embodiment refer to SQL statements that are put online after the application is developed and are now in the production environment.

[0069] The SQL statements to be launched in this embodiment refer to SQL statements that are waiting to be launched after application development is completed, that is, have not yet been launched and are not in the production environment.

[0070] The indicator data in this embodiment refers to data used to characterize the quality of SQL statements; wherein, the indicator data includes but is not limited to static indicator data and dynamic indicator data, specifically, it can be any one or more of static indicator data and dynamic indicator data. wherein, static indicator data includes but is not limited to objects and text writing, specifically, it can be any one or more of objects and text writing. wherein, dynamic indicator data includes but is not limited to physical reads, logical reads, concurrency, statistical information, execution plans and time consumption, specifically, it can be any one or more of physical reads, logical reads, concurrency, statistical information, execution plans and time consumption.

[0071] In some examples, static indicator data of SQL statements that have been launched may be collected, while static indicator data of SQL statements to be launched may be collected at the same time.

[0072] In some examples, dynamic indicator data of SQL statements that have been launched may be collected, while dynamic indicator data of SQL statements to be launched may be collected at the same time.

[0073] In some examples, dynamic indicator data of SQL statements that have been launched may be collected, while static indicator data of SQL statements to be launched may be collected.

[0074] In some examples, static indicator data and dynamic indicator data of SQL statements that have been launched may be collected, and static indicator data and dynamic indicator data of SQL statements to be launched may be collected at the same time.

[0075] Step S202: Based on the indicator data and the SQL performance profile analysis model obtained through training, performance prediction is performed on the SQL statements that have been put online and the SQL statements to be put online to obtain performance prediction results.

[0076] Furthermore, in this embodiment, after collecting indicator data of the SQL statements that have been launched and the SQL statements to be launched, it is necessary to perform performance prediction on the SQL statements that have been launched and the SQL statements to be launched respectively based on the indicator data and the SQL performance portrait analysis model obtained through training to obtain performance prediction results.

[0077] In this embodiment, step S202 performs performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results, which may include the following steps:

[0078] First, the indicator data of the online SQL statement is input into the SQL performance profile analysis model to obtain a first performance prediction result; wherein the SQL performance profile analysis model includes static indicator features and / or dynamic indicator features of the SQL statement;

[0079] Then, the indicator data of the SQL statement to be launched is input into the SQL performance profile analysis model to obtain the second performance prediction result.

[0080] That is, in this embodiment, the indicator data of the SQL statements that have been put online and the indicator data of the SQL statements to be put online are respectively input into the trained SQL performance portrait analysis model, so that the performance prediction results of the SQL statements that have been put online (i.e., the first performance prediction results) and the performance prediction results of the SQL statements to be put online (i.e., the second performance prediction results) can be obtained.

[0081] It should be noted that the SQL performance profile analysis model in this embodiment includes static indicator features and dynamic indicator features of SQL statements. That is, when the indicator data obtained is static indicator data, the SQL performance profile analysis model can be used to analyze the static indicator data, thereby obtaining a performance prediction result; when the indicator data obtained is dynamic indicator data, the SQL performance profile analysis model can be used to analyze the dynamic indicator data, thereby obtaining a performance prediction result; when the indicator data obtained is both static indicator data and dynamic indicator data, the SQL performance profile analysis model can be used to simultaneously analyze the static indicator data and the dynamic indicator data, thereby obtaining a performance prediction result. In this way, by combining both static and dynamic indicator data, the comprehensiveness of performance prediction for SQL statements can be greatly improved.

[0082] For example:

[0083] Let static indicator data be denoted as staticScore, which includes object obj and text writing syntax; dynamic indicator data be denoted as dynamicScore, which includes physical read phy, logical read logic, concurrency number concur, statistical information statis, execution plan expl and time elap; the weights are w1, w2...wn, W1, W2 respectively.

[0084] The calculation formula for the static feature score of the SQL performance profile analysis model is:

[0085] Formula 1:

[0086] The static feature prediction results of the SQL statements that have been put online and the static feature prediction results of the SQL statements to be put online can be obtained through the above formula 1.

[0087] The calculation formula for the dynamic feature score of the SQL performance profile analysis model is:

[0088] Formula 2:

[0089]

[0090] The above formula 2 can be used to obtain the dynamic feature prediction results of the SQL statements that have been launched and the dynamic feature prediction results of the SQL statements to be launched.

[0091] The formula for calculating the total score is:

[0092] Formula 3: score=staticScore*W1+dynamicScore*W2.

[0093] The performance prediction results of the SQL statements that have been launched and the performance prediction results of the SQL statements to be launched can be obtained through the above formula 3.

[0094] Among them, the weight values ​​w1, w2...wn, W1, W2 can be flexibly adjusted, for example, W1 takes a value of 0.7, and W2 takes a value of 0.3.

[0095] It is worth noting that the above example is only a specific example. In actual application, it can be flexibly adjusted according to the specific application scenario.

[0096] It is understandable that in this embodiment, it is possible to determine in which preset score range the score corresponding to the performance prediction result falls, wherein different preset score ranges correspond to different performance levels; for example, see Table 1 below:

[0097] Table 1

[0098] Preset score range Performance level (a2, a1] excellent (a3, a2] good (a4, a3] qualified [a5, a4] Unqualified

[0099] It is worth noting that, in actual applications, the preset score intervals and the mapping relationship between the preset score intervals and performance levels can be flexibly adjusted according to specific application scenarios.

[0100] Step S203: performing similarity judgment on the SQL statements that have been put online and the SQL statements to be put online to obtain a similarity judgment result.

[0101] The similarity judgment in this embodiment refers to judging the similarity between the SQL statements that have been put online and the SQL statements to be put online.

[0102] It should be noted that, in this embodiment, step S203 performs similarity judgment on the SQL statements that have been put online and the SQL statements to be put online to obtain similarity judgment results, and step S202 performs performance prediction on the SQL statements that have been put online and the SQL statements to be put online based on the indicator data and the SQL performance portrait analysis model obtained through training to obtain performance prediction results. The execution order can be arbitrarily exchanged, or can be executed in parallel. In actual applications, they can be flexibly adjusted according to the specific application scenarios.

[0103] In this embodiment, step S203 performs similarity determination on the SQL statements that have been put online and the SQL statements to be put online, and the step of obtaining the similarity determination result may include the following steps:

[0104] First, vectorize the SQL statements that have been launched and the SQL statements to be launched to obtain vector expressions of the SQL statements that have been launched and the SQL statements to be launched.

[0105] Then, based on the vector expression of the SQL statement that has been put online and the vector expression of the SQL statement to be put online, the distance result between the SQL statement that has been put online and the SQL statement to be put online is obtained;

[0106] Thirdly, based on the distance result, the similarity between the SQL statement that has been put online and the SQL statement to be put online is determined to obtain a similarity judgment result.

[0107] That is, in this embodiment, the online SQL statements and the SQL statements to be put online are vectorized and then distance calculation is performed, and then the similarity between the online SQL statements and the SQL statements to be put online is determined based on the calculated distance results, thereby obtaining a similarity judgment result.

[0108] For example:

[0109] Assume that the SQL statement that has been launched is A. Vectorize the SQL statement A to obtain the vector expression: A = (a1, a2, a3 ... an); and assume that the SQL statement to be launched is B. Vectorize the SQL statement B to obtain the vector expression: B = (b1, b2, b3 ... bn);

[0110] Furthermore, based on the vector expression of the SQL statement A that has been launched and the vector expression of the SQL statement B that is to be launched, the distance result between the SQL statement A that has been launched and the SQL statement B that is to be launched is obtained:

[0111]

[0112] Furthermore, based on the distance result, the similarity between the SQL statement that has been launched and the SQL statement to be launched is determined:

[0113]

[0114] It is understandable that the denominator 1 is used to ensure that the maximum value of the similarity is 1.

[0115] It is worth noting that the above example is only a specific example. In actual application, it can be flexibly adjusted according to the specific application scenario.

[0116] Step S204: Determine the target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result.

[0117] Furthermore, in this embodiment, after obtaining the performance prediction results of the SQL statements that have been put online and the SQL statements to be put online, as well as the similarity judgment results of the SQL statements that have been put online and the SQL statements to be put online, it is necessary to determine the target performance prediction results of the SQL statements to be put online based on the similarity judgment results and the performance prediction results.

[0118] In this embodiment, step S204, determining the target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result, includes:

[0119] A target performance prediction result of the SQL statement to be put online is determined based on the similarity judgment result, the first performance prediction result, and the second performance prediction result.

[0120] That is, in this embodiment, the final performance prediction result (i.e., the target performance prediction result) for the SQL statement to be launched is determined based on the similarity between the SQL statement that has been launched and the SQL statement to be launched, the performance prediction result for the SQL statement that has been launched, and the performance prediction result for the SQL statement to be launched. This multi-faceted approach to determining the final performance prediction result for the SQL statement to be launched greatly improves the accuracy of performance prediction for SQL statements.

[0121] In this embodiment, the step of determining the target performance prediction result of the SQL statement to be put online based on the similarity judgment result, the first performance prediction result, and the second performance prediction result may include three cases:

[0122] Case 1: If the similarity judgment result is within the first preset similarity range, the first performance prediction result is determined as the target performance prediction result of the SQL statement to be put online.

[0123] Among them, the first preset similarity interval is an interval with high similarity. If the similarity between the online SQL statement and the SQL statement to be launched is in the first preset similarity interval, it means that the similarity between the online SQL statement and the SQL statement to be launched is very high; therefore, at this time, there is no need to consider the performance prediction result of the SQL statement to be launched (that is, the first performance prediction result), and the performance prediction result of the online SQL statement (that is, the second performance prediction result) can be directly determined as the target performance prediction result of the SQL statement to be launched.

[0124] For example, assume that the similarity between SQL statement A that has been launched and SQL statement B that is to be launched is 0.95, which is within the first preset similarity interval (0.8, 1]; wherein, the performance prediction result of SQL statement A that has been launched is 0.98, and the performance prediction result of SQL statement B that is to be launched is 0.88. At this time, the performance prediction result 0.98 is determined as the target performance prediction result of SQL statement B that is to be launched.

[0125] Case 2: If the similarity judgment result is within the second preset similarity range, the average of the first performance prediction result and the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online.

[0126] Among them, the second preset similarity interval is the interval in the similarity. If the similarity between the online SQL statement and the SQL statement to be online is in the second preset similarity interval, it means that the similarity between the online SQL statement and the SQL statement to be online is moderate; therefore, at this time, it is necessary to consider the performance prediction results of the online SQL statement (that is, the first performance prediction result) and the performance prediction results of the SQL statement to be online (that is, the second performance prediction result) at the same time. The first performance prediction result and the second performance prediction result can be averaged, and then the average value can be determined as the target performance prediction result of the SQL statement to be online.

[0127] For example, assume that the similarity between SQL statement A that has been launched and SQL statement B that is to be launched is 0.5, which is within the first preset similarity range (0.4, 0.8]; wherein, the performance prediction result of SQL statement A that has been launched is 0.9, and the performance prediction result of SQL statement B that is to be launched is 0.8. At this time, the performance prediction result 0.85 is determined as the target performance prediction result of SQL statement B that is to be launched.

[0128] Case 3: If the similarity judgment result is in the third preset similarity range, the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online.

[0129] Among them, the third preset similarity interval is a low similarity interval. If the similarity between the online SQL statement and the SQL statement to be launched is in the third preset similarity interval, it means that the similarity between the online SQL statement and the SQL statement to be launched is low; therefore, at this time, there is no need to consider the performance prediction result of the online SQL statement (that is, the second performance prediction result), and the performance prediction result of the SQL statement to be launched (that is, the first performance prediction result) can be directly determined as the target performance prediction result of the SQL statement to be launched.

[0130] For example: For example, suppose the similarity between the online SQL statement A and the SQL statement to be launched B is 0.2, which is within the first preset similarity range [0, 0.4]; among them, the performance prediction result of the online SQL statement A is 0.6, and the performance prediction result of the SQL statement to be launched B is 0.3. At this time, the performance prediction result 0.3 is determined as the target performance prediction result of the SQL statement to be launched B.

[0131] It should be noted that, in this embodiment, the first preset similarity interval is an interval with high similarity, the second preset similarity interval is an interval with moderate similarity, and the first preset similarity interval is an interval with low similarity; accordingly, the minimum value of the first preset similarity interval is greater than the maximum value of the second preset similarity interval, and the minimum value of the second preset similarity interval is greater than the maximum value of the third preset similarity interval.

[0132] It is worth noting that in actual applications, the first preset similarity interval, the second preset similarity interval, the third preset similarity interval, and the obtained first performance prediction result, second performance prediction result, and similarity result can be flexibly adjusted according to specific application scenarios.

[0133] In this embodiment, the collected indicator data of the SQL statements that have been put online and the SQL statements to be put online are input into the trained SQL performance portrait analysis model to obtain the performance prediction results of the SQL statements that have been put online and the SQL statements to be put online, and then the similarity and performance prediction results of the two are combined to jointly determine the final performance prediction results of the SQL statements to be put online, which greatly improves the performance prediction accuracy of the SQL statements; and, the indicator data of the SQL statements that have been put online and the SQL statements to be put online are input into the SQL performance portrait analysis model that includes static indicator features and dynamic indicator features, which makes the performance prediction of the SQL statements more comprehensive, thereby further improving the performance prediction accuracy of the SQL statements; at the same time, in this embodiment, the performance of the SQL statements is predicted before they are put online, avoiding the phenomenon of failure of the SQL statements after they are put online, and greatly improving the performance and stability of the database.

[0134] See Figure 3 As shown, Figure 3This is a flow chart of a second embodiment of the SQL statement performance prediction method of the present invention. In this embodiment, step S202 inputs the indicator data of the online SQL statement into the SQL performance profile analysis model. Before obtaining the first performance prediction result, the SQL statement performance prediction method may further include the following steps:

[0135] Step S301: Obtain indicator data of SQL statement training samples.

[0136] In this embodiment, before using the trained SQL performance profile analysis model to analyze the collected indicator data of the online SQL statements and the indicator data of the SQL statements to be launched, so as to obtain the performance prediction results of the online SQL statements and the performance prediction results of the SQL statements to be launched, it is necessary to first train the SQL performance profile analysis model.

[0137] Therefore, in this embodiment, it is necessary to first obtain training samples to train the SQL performance profile analysis model, thereby obtaining a trained SQL performance profile analysis model. It is understandable that in this embodiment, the training samples refer to the static and dynamic indicator data of the SQL statements that have been launched, as well as the static and dynamic indicator data of the SQL statements that are yet to be launched.

[0138] Step S302: Train the SQL performance profile analysis model based on the indicator data of the SQL statement training sample to obtain a trained SQL performance profile analysis model.

[0139] Furthermore, the obtained static indicator data and dynamic indicator data of the SQL statements that have been put online, as well as the static indicator data and dynamic indicator data of the SQL statements to be put online, are used to train the SQL performance profile analysis model to obtain the trained SQL performance profile analysis model.

[0140] In this embodiment, the SQL performance profile analysis model is trained based on the indicator data of the SQL statement training sample to obtain the trained SQL performance profile analysis model. In this way, the SQL performance profile analysis model obtained by training is more comprehensive by combining the static and dynamic indicator data of the SQL statement. Therefore, the subsequent use of the SQL performance profile analysis model makes the performance prediction accuracy of the SQL statement higher. At the same time, the SQL performance profile analysis model obtained by training is used to obtain the performance prediction results of the SQL statements that have been put online and the performance prediction results of the SQL statements to be put online. No human participation is required, and the performance prediction of the entire SQL statement is more intelligent and automated, which reduces labor costs and avoids the phenomenon of low accuracy of manual prediction.

[0141] See Figure 4 As shown, Figure 4This is a flow chart of a third embodiment of the SQL statement performance prediction method of the present invention. In this embodiment, step S202 performs performance prediction on both the launched SQL statements and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model. Before obtaining the performance prediction results, the SQL statement performance prediction method may further include the following steps:

[0142] Step S401: Clean abnormal indicator data in the indicator data to obtain cleaned indicator data;

[0143] Step S402: Classifying online SQL statements with the same identifier as the same online SQL statement based on the identifiers of the online SQL statements;

[0144] Step S403: Based on the identifiers of the SQL statements to be put online, the SQL statements to be put online with the same identifier are classified as the same SQL statement to be put online;

[0145] Accordingly, step S202 performs performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model, and obtains performance prediction results, which may include:

[0146] Based on the cleaned indicator data and the trained SQL performance profile analysis model, performance prediction is performed on multiple identical SQL statements that have been launched and multiple identical SQL statements to be launched to obtain performance prediction results.

[0147] That is, in this embodiment, after collecting the indicator data for both the live SQL statements and the SQL statements to be released, it is necessary to first identify any abnormal indicator data within the indicator data and then cleanse it to obtain cleaned indicator data. This cleaned indicator data is then used to perform performance predictions on both the live SQL statements and the SQL statements to be released, resulting in more accurate performance predictions for both the live SQL statements and the SQL statements to be released. This further improves the accuracy of SQL statement performance predictions and avoids the interference of abnormal indicator data on performance predictions.

[0148] It is understandable that in this embodiment, the data is collected in units of one SQL statement. Therefore, when collecting multiple SQL statements that have been released and multiple SQL statements to be released, it is necessary to use identifiers to classify the same SQL statement that has been released into one category and the same SQL statement that is to be released into another category. Subsequently, performance prediction is performed on the same SQL statement that has been released, obtaining a performance prediction result for the same SQL statement that has been released (i.e., a first prediction result), and performance prediction is performed on the same SQL statement that is to be released, obtaining a performance prediction result for the same SQL statement that is to be released (i.e., a second prediction result).

[0149] It should be noted that, in this embodiment, step S402 classifies the online SQL statements with the same identifier as the same online SQL statement based on the identifier of the online SQL statement, and step S403 classifies the to-be-launched SQL statements with the same identifier as the to-be-launched SQL statement based on the identifier of the to-be-launched SQL statement. The execution order can be arbitrarily exchanged, or they can be executed in parallel. In actual applications, they can be flexibly adjusted according to the specific application scenario.

[0150] In this embodiment, by using the cleaned indicator data to perform performance prediction on the SQL statement, the phenomenon of abnormal indicator data interfering with the performance prediction is avoided, thereby further improving the performance prediction accuracy of the SQL statement.

[0151] See Figure 5 As shown, Figure 5 2 is a flow chart of a fourth embodiment of the SQL statement performance prediction method of the present invention. In this embodiment, after step S204 of determining the target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result, the SQL statement performance prediction method may further include the following steps:

[0152] Step S501: Compare the target performance prediction result of the SQL statement to be put online with the preset threshold; if the target performance prediction result is greater than or equal to the preset threshold, execute step S502; if the target performance prediction result is less than the preset threshold, execute step S503.

[0153] Step S502: Perform online operations on the SQL statement to be online;

[0154] Step S503: performing an adjustment operation on the SQL statement to be put online so that the target performance prediction result of the SQL statement to be put online after the adjustment operation is greater than or equal to a preset threshold.

[0155] That is, in this embodiment, after obtaining the target performance prediction result for the SQL statement to be launched, it can be further compared with a preset threshold. If the target performance prediction result is greater than or equal to the preset threshold, the SQL statement to be launched is excellent, and therefore, the SQL statement to be launched can be launched. If the target performance prediction result is less than the preset threshold, the SQL statement to be launched is unqualified, and therefore, it cannot be launched. The SQL statement to be launched needs to be adjusted to avoid the phenomenon of poor database stability caused by directly launching the unqualified SQL statement to be launched.

[0156] It is worth noting that in actual applications, the preset threshold can be flexibly adjusted according to the specific application scenario.

[0157] In this embodiment, the target performance prediction result of the SQL statement to be put online is compared with a preset threshold, and then corresponding operations are performed on the SQL statement to be put online according to the comparison result, which is more flexible.

[0158] Also, see Figure 6 As shown, based on the above-mentioned SQL statement performance prediction method, the embodiment of the present invention further proposes a SQL statement performance prediction device, which includes:

[0159] The collection module 601 is used to collect indicator data of SQL statements that have been launched and SQL statements to be launched;

[0160] The performance prediction module 602 is used to perform performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results;

[0161] A judgment module 603 is used to perform similarity judgment on the SQL statements that have been put online and the SQL statements to be put online, and obtain a similarity judgment result;

[0162] The determination module 604 is configured to determine a target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result.

[0163] It should be noted that the SQL statement performance prediction device in this embodiment may optionally include other corresponding modules to implement the steps of the above-mentioned SQL statement performance prediction method.

[0164] The SQL statement performance prediction device of the present invention adopts all the technical solutions of all the embodiments of the above-mentioned SQL statement performance prediction method, and therefore has at least all the beneficial effects brought about by the technical solutions of all the embodiments of the above-mentioned SQL statement performance prediction method, which will not be described one by one here.

[0165] In addition, this embodiment also proposes a computer program, which stores a SQL statement performance prediction program. When the SQL statement performance prediction program is executed by a processor, the steps of the above-mentioned SQL statement performance prediction method are implemented.

[0166] The computer program includes volatile or non-volatile, removable or non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, computer program modules or other data. Computer programs include, but are not limited to, RAM (Random Access Memory), ROM (Read-Only Memory), EEPROM (Electrically Erasable Programmable read only memory), flash memory or other memory technology, CD-ROM (Compact Disc Read-Only Memory), digital versatile disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage, or any other medium that can be used to store the desired information and that can be accessed by a computer.

[0167] It can be seen that those skilled in the art should understand that all or some of the steps, systems, and functional modules / units in the integrated stove disclosed in the above method can be implemented as software, firmware, hardware, and appropriate combinations thereof. In hardware implementations, the division between the functional modules / units mentioned in the above description does not necessarily correspond to the division of physical components; for example, a physical component can have multiple functions, or a function or step can be performed by several physical components in cooperation. Some or all physical components can be implemented as software executed by a processor, such as a central processing unit, a digital signal processor, or a microprocessor, or implemented as hardware, or implemented as an integrated circuit, such as an application-specific integrated circuit.

[0168] The above are only preferred embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent structure or equivalent process transformation made using the contents of the present invention description and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A method for predicting SQL statement performance, characterized in that: The SQL statement performance prediction method comprises the following steps: Collecting indicator data of SQL statements that have been launched and SQL statements to be launched, wherein the indicator data is used to characterize the quality of the SQL statements and includes at least one of static indicator data and dynamic indicator data; Based on the indicator data and the trained SQL performance profile analysis model, performance prediction is performed on the SQL statements that have been launched and the SQL statements to be launched, respectively, to obtain performance prediction results, wherein the performance prediction results include a first performance prediction result corresponding to the SQL statements that have been launched and a second performance prediction result corresponding to the SQL statements to be launched; Performing similarity judgment on the SQL statement that has been put online and the SQL statement to be put online to obtain a similarity judgment result; Determining a target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result, specifically including: If the similarity judgment result is within a first preset similarity range, determining the first performance prediction result as the target performance prediction result of the SQL statement to be put online; If the similarity judgment result is within a second preset similarity interval, an average of the first performance prediction result and the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online, and the minimum value of the first preset similarity interval is greater than the maximum value of the second preset similarity interval; If the similarity judgment result is in a third preset similarity interval, the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online, and the minimum value of the second preset similarity interval is greater than the maximum value of the third preset similarity interval.

2. The SQL statement performance prediction method according to claim 1, wherein: The step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results includes: Inputting the indicator data of the online SQL statement into the SQL performance profile analysis model to obtain a first performance prediction result; wherein the SQL performance profile analysis model includes static indicator features and dynamic indicator features of the SQL statement; The indicator data of the SQL statement to be put online is input into the SQL performance profile analysis model to obtain a second performance prediction result.

3. The SQL statement performance prediction method according to claim 1, wherein: The step of performing similarity determination on the SQL statement that has been put online and the SQL statement to be put online to obtain a similarity determination result includes: Performing vectorization processing on the SQL statement that has been put online and the SQL statement that is to be put online respectively to obtain a vector expression of the SQL statement that has been put online and a vector expression of the SQL statement that is to be put online; Obtaining a distance result between the online SQL statement and the to-be-online SQL statement based on the vector expression of the online SQL statement and the vector expression of the to-be-online SQL statement; The similarity between the online SQL statement and the SQL statement to be online is determined according to the distance result to obtain the similarity judgment result.

4. The SQL statement performance prediction method according to claim 1, wherein: The static indicator data includes: at least one of an object and a text writing method; The dynamic indicator data includes at least one of physical reads, logical reads, concurrency, statistical information, execution plan, and time consumption.

5. The SQL statement performance prediction method according to any one of claims 1 to 4, characterized in that: Before the step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model, and obtaining performance prediction results, the SQL statement performance prediction method further includes: Obtain indicator data for SQL statement training samples; The SQL performance profile analysis model is trained according to the indicator data of the SQL statement training sample to obtain a trained SQL performance profile analysis model.

6. The SQL statement performance prediction method according to any one of claims 1 to 4, characterized in that: Before the step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model, and obtaining performance prediction results, the SQL statement performance prediction method further includes: Cleaning abnormal indicator data in the indicator data to obtain cleaned indicator data; Based on the identifiers of the online SQL statements, the online SQL statements with the same identifier are classified as the same online SQL statement; According to the identifiers of the SQL statements to be put online, the SQL statements to be put online with the same identifiers are classified as the same SQL statements to be put online; The step of performing performance prediction on the SQL statements that have been launched and the SQL statements to be launched based on the indicator data and the trained SQL performance profile analysis model to obtain performance prediction results includes: Based on the cleaned indicator data and the trained SQL performance profile analysis model, performance prediction is performed on multiple identical SQL statements that have been launched and multiple identical SQL statements to be launched to obtain performance prediction results.

7. A SQL statement performance prediction device, characterized in that: The SQL statement performance prediction device includes: A collection module, configured to collect indicator data of SQL statements that have been launched and SQL statements to be launched, wherein the indicator data is used to characterize the quality of the SQL statements and includes at least one of static indicator data and dynamic indicator data; a performance prediction module, configured to perform performance prediction on the SQL statements that have been launched and the SQL statements to be launched, respectively, based on the indicator data and the trained SQL performance profile analysis model, to obtain performance prediction results, the performance prediction results including a first performance prediction result corresponding to the SQL statements that have been launched and a second performance prediction result corresponding to the SQL statements to be launched; A judgment module, configured to perform similarity judgment on the SQL statement that has been put online and the SQL statement to be put online, and obtain a similarity judgment result; A determination module, configured to determine a target performance prediction result of the SQL statement to be put online based on the similarity judgment result and the performance prediction result; The determining module is further configured to determine the first performance prediction result as the target performance prediction result of the SQL statement to be put online if the similarity judgment result is within a first preset similarity range; If the similarity judgment result is within a second preset similarity interval, an average of the first performance prediction result and the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online, and the minimum value of the first preset similarity interval is greater than the maximum value of the second preset similarity interval; If the similarity judgment result is in a third preset similarity interval, the second performance prediction result is determined as the target performance prediction result of the SQL statement to be put online, and the minimum value of the second preset similarity interval is greater than the maximum value of the third preset similarity interval.

8. A SQL statement performance prediction device, characterized in that: The SQL statement performance prediction device includes: a memory, a processor, and an SQL statement performance prediction program stored in the memory and running on the processor. When the SQL statement performance prediction program is executed by the processor, the steps of the SQL statement performance prediction method according to any one of claims 1 to 6 are implemented.

9. A computer program product, characterized in that The computer program product stores an SQL statement performance prediction program, which, when executed by a processor, implements the steps of the SQL statement performance prediction method according to any one of claims 1 to 6.

Images