Production environment publishing method, device, equipment and medium

By acquiring and comparing low-performance SQL fingerprint databases and producing SQL fingerprint databases, detecting and optimizing new low-performance SQL in pre-issue environments, the problem of inability to effectively intercept low-performance SQL in the existing technology and improving the production environment database stability and performance.

CN119025543APending Publication Date: 2024-11-26SHENZHEN YISHIHUOLALA TECH CO LTD
View PDF 7 Cites 0 Cited by

Patent Information

Application Number
CN202411113392.2
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-14
Publication Date
2024-11-26

AI Technical Summary

Technical Problem

The existing production environment release method cannot be effectively intercepted after the impact of low-performance SQL, resulting in a decline in production database performance or failure, and the stability of the database cannot be guaranteed.

Method used

By obtaining low-performance SQL fingerprint database and production SQL fingerprint database, detecting whether there are new low-performance SQL in the pre-issue environment, and optimizing it when receiving production environment publishing requests to ensure that low-performance SQL is intercepted and optimized before publishing to production environment. Performance SQL.

Benefits of technology

It realizes early detection and intercepting of low-performance SQL, reducing its threat to production environment databases, and improving the stability and performance of production environment releases.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN119025543A_ABST
    Figure CN119025543A_ABST
Patent Text Reader

Abstract

The invention discloses a production environment publishing method and device, equipment and a medium, and the method comprises the steps: obtaining a low-performance structured query language (SQL) fingerprint database and a production SQL fingerprint database in response to a received production environment publishing request; comparing the low-performance SQL fingerprint database with the production SQL fingerprint database, and detecting whether a newly added low-performance SQL exists in the pre-sending environment or not; if it is detected that the newly-added low-performance SQL exists in the pre-sending environment, optimizing the newly-added low-performance SQL to obtain a target service code; and releasing the target service code to the production environment. Thus, when the production environment release request is received, the low-performance SQL in the pre-release environment is firstly obtained, the low-performance SQL is optimized, and then production environment release is performed, so that the low-performance SQL is discovered in advance, compared with postmortem slow SQL analysis in the prior art, the low-performance SQL can be intercepted outside a production environment database, and the production environment release efficiency is improved. The threat of the low-performance SQL to the stability of the production environment database is reduced to a certain extent, and the production environment publishing effect is improved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a production environment publishing method, device, equipment and medium. Background Art

[0002] Production environment release refers to the process of deploying a tested system or software to a production environment. In the early stages of system development, due to the small amount of database data, the performance of Structured Query Language (SQL) has little impact on system response speed. As the system is put into actual use, the amount of data in the database will continue to increase. At this time, low-performance SQL will cause system performance to degrade. In order to improve system availability, SQL will be analyzed to obtain low-performance SQL for optimization.

[0003] Currently, after the business code is released to the production environment, the SQL execution log of the production database is recorded, and the management personnel analyze the SQL execution log to extract low-performance SQL for optimization. However, during the production environment release process, the low-performance SQL that exists may affect the performance of the production database or even cause the production database to fail. In other words, the current production environment release method is ineffective, and the SQL execution log is analyzed after the low-performance SQL impact occurs, which cannot effectively intercept the low-performance SQL. Summary of the invention

[0004] The present application provides a production environment publishing method, device, equipment and medium, which can reduce the threat of low-performance SQL to the stability of the production environment database and improve the production environment publishing effect.

[0005] In a first aspect, the present application provides a production environment publishing method, the method comprising:

[0006] In response to receiving a production environment release request, a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database are obtained; the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprint of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprint of the production environment; the production environment release request is used to instruct to release the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces database performance;

[0007] Compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment;

[0008] If it is detected that the newly added low-performance SQL exists in the pre-release environment, the newly added low-performance SQL is optimized to obtain a target business code, where the target business code includes the optimized SQL;

[0009] The target business code is released to the production environment.

[0010] Optionally, obtaining the low-performance SQL fingerprint database and the production SQL fingerprint database includes:

[0011] Collect data from the pre-launch environment database and the production environment database to obtain the pre-launch environment SQL and the production environment SQL; the pre-launch environment database is used to store simulation test data; the production environment database is used to store real business data;

[0012] Performing fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint, and performing fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint;

[0013] If the pre-release environment SQL is a low-performance SQL in a newly added state, the pre-release environment SQL fingerprint is stored in the low-performance SQL fingerprint database;

[0014] If the production environment SQL is in a newly added state, the production environment SQL fingerprint is stored in the production SQL fingerprint database.

[0015] Optionally, the performing fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint includes:

[0016] Constructing an abstract syntax tree corresponding to the pre-release environment SQL;

[0017] Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database;

[0018] splicing the information of the database objects to obtain de-parameterized SQL;

[0019] A hash algorithm is performed on the de-parameterized SQL to obtain the pre-release environment SQL fingerprint.

[0020] Optionally, the performing fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint includes:

[0021] Construct an abstract syntax tree corresponding to the production environment SQL;

[0022] Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database;

[0023] splicing the information of the database objects to obtain de-parameterized SQL;

[0024] A hash algorithm is performed on the de-parameterized SQL to obtain the production environment SQL fingerprint.

[0025] Optionally, if the pre-release environment SQL is a low-performance SQL in a newly added state, storing the pre-release environment SQL fingerprint in the low-performance SQL fingerprint database includes:

[0026] Check whether the pre-launch environment SQL fingerprint exists in the pre-launch environment historical fingerprint database;

[0027] If it does not exist, the pre-release environment SQL is obtained as a newly added state;

[0028] Performing performance diagnosis on the pre-release environment SQL to obtain a diagnosis result;

[0029] When the diagnosis result indicates that the pre-release environment SQL is a low-performance SQL, the pre-release environment SQL fingerprint is stored in the low-performance SQL fingerprint database.

[0030] Optionally, the performing performance diagnosis on the pre-release environment SQL to obtain a diagnosis result includes:

[0031] The pre-release environment SQL fingerprint is subjected to performance diagnosis according to preset characteristic standards to obtain the diagnosis result; the preset characteristic standards include at least one of the number of scanned rows, the returned result set, the execution duration, whether the full table is scanned, whether the update semantic logic is correct, and whether the use of the hit index is not the best practice.

[0032] Optionally, if the production environment SQL is in a newly added state, storing the production environment SQL fingerprint in the production SQL fingerprint database includes:

[0033] Check whether the production environment SQL fingerprint exists in the production environment historical fingerprint database;

[0034] If it does not exist, the production environment SQL is obtained as a newly added state;

[0035] The production environment SQL fingerprint is stored in the production SQL fingerprint database.

[0036] In a second aspect, the present application also provides a production environment publishing system, the system comprising an SQL fingerprint acquisition module, an incremental SQL comparison module, and an interception module;

[0037] The SQL fingerprint acquisition module is used to obtain a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database in response to receiving a production environment release request; the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprints of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprints of the production environment; the production environment release request is used to instruct to release the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces database performance;

[0038] The incremental SQL comparison module is used to compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment;

[0039] The interception module is used for optimizing the newly added low-performance SQL to obtain a target business code if it is detected that the newly added low-performance SQL exists in the pre-release environment, and the target business code includes the optimized SQL;

[0040] The interception module is also used to publish the target business code to the production environment.

[0041] Optionally, the system further includes: the full SQL collection module, the SQL fingerprint calculation module and the SQL new addition judgment module;

[0042] The full SQL collection module is used to collect data from the pre-release environment database and the production environment database to obtain the pre-release environment SQL and the production environment SQL; the pre-release environment database is used to store simulation test data; the production environment database is used to store real business data;

[0043] The SQL fingerprint calculation module is used to perform fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint, and perform fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint;

[0044] The SQL addition judgment module is used to store the pre-launch environment SQL fingerprint in the low-performance SQL fingerprint database if the pre-launch environment SQL is a low-performance SQL in a newly added state;

[0045] The SQL new addition judgment module is further used to store the production environment SQL fingerprint in the production SQL fingerprint database if the production environment SQL is in a new addition state.

[0046] Optionally, the SQL fingerprint calculation module is specifically used to:

[0047] Constructing an abstract syntax tree corresponding to the pre-release environment SQL;

[0048] Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database;

[0049] splicing the information of the database objects to obtain de-parameterized SQL;

[0050] A hash algorithm is performed on the de-parameterized SQL to obtain the pre-release environment SQL fingerprint.

[0051] Optionally, the SQL fingerprint calculation module is specifically used to:

[0052] Construct an abstract syntax tree corresponding to the production environment SQL;

[0053] Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database;

[0054] splicing the information of the database objects to obtain de-parameterized SQL;

[0055] A hash algorithm is performed on the de-parameterized SQL to obtain the production environment SQL fingerprint.

[0056] Optionally, the SQL adds a new judgment module, which is specifically used to:

[0057] Check whether the pre-launch environment SQL fingerprint exists in the pre-launch environment historical fingerprint database;

[0058] If it does not exist, the pre-release environment SQL is obtained as a newly added state;

[0059] Performing performance diagnosis on the pre-release environment SQL to obtain a diagnosis result;

[0060] When the diagnosis result indicates that the pre-release environment SQL is a low-performance SQL, the pre-release environment SQL fingerprint is stored in the low-performance SQL fingerprint database.

[0061] Optionally, the system further includes a SQL performance diagnosis module;

[0062] The SQL performance diagnosis module is used to perform performance diagnosis on the pre-release environment SQL fingerprint according to preset feature standards to obtain the diagnosis results; the preset feature standards include at least one of the number of scanned rows, returned result sets, execution duration, whether a full table scan is performed, whether the update semantic logic is correct, and whether the use of hit index is not a best practice.

[0063] Optionally, the SQL adds a new judgment module, which is specifically used to:

[0064] Check whether the production environment SQL fingerprint exists in the production environment historical fingerprint database;

[0065] If it does not exist, the production environment SQL is obtained as a newly added state;

[0066] The production environment SQL fingerprint is stored in the production SQL fingerprint database.

[0067] In a third aspect, the present application further provides an electronic device, the electronic device comprising a processor and a memory:

[0068] The memory is used to store computer programs;

[0069] The processor is used to execute the method provided in the first aspect according to the computer program.

[0070] In a fourth aspect, the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium is used to store a computer program, and the computer program is used to execute the method provided in the first aspect above.

[0071] It can be seen that this application has the following beneficial effects:

[0072] The present application provides a production environment publishing method, device, equipment and medium, the method comprising: in response to receiving a production environment publishing request, obtaining a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database; wherein the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprint of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprint of the production environment; the production environment publishing request is used to instruct to publish the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces the database performance; comparing the low-performance SQL fingerprint database and the production SQL fingerprint database, detecting whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment; if it is detected that the pre-release environment has the newly added low-performance SQL, optimizing the newly added low-performance SQL to obtain the target business code, the target business code includes the optimized SQL; publishing the target business code to the production environment. In this way, when a production environment release request is received, the low-performance SQL fingerprint database and the production SQL fingerprint database are first compared to obtain the low-performance SQL in the pre-release environment, and the low-performance SQL is optimized before being released to the production environment, thereby realizing early detection of low-performance SQL. Compared with the post-slow SQL analysis of the prior art, the present application can intercept low-performance SQL outside the production environment database, which reduces the threat of low-performance SQL to the stability of the production environment database to a certain extent and improves the production environment release effect. BRIEF DESCRIPTION OF THE DRAWINGS

[0073] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in the present application. For ordinary technicians in this field, other drawings can also be obtained based on these drawings.

[0074] Figure 1 A schematic diagram of the structure of a production environment publishing system 100 provided in an embodiment of the present application;

[0075] Figure 2 A flowchart of a production environment publishing method in an embodiment of the present application;

[0076] Figure 3 This is a schematic diagram of the execution of a production environment release process in an embodiment of the present application;

[0077] Figure 4 It is a schematic diagram of the structure of an electronic device 400 in an embodiment of the present application. DETAILED DESCRIPTION

[0078] The multiple involved in the embodiments of the present application means greater than or equal to two. It should be noted that in the description of the embodiments of the present application, the words "first", "second", etc. are only used for the purpose of distinguishing the description, and cannot be understood as indicating or implying relative importance, nor can they be understood as indicating or implying an order.

[0079] In order to make the above-mentioned purposes, features and advantages of the present application more obvious and easy to understand, the embodiments of the present application are further described in detail below in conjunction with the accompanying drawings and specific implementation methods. It is understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. In addition, it should be noted that, for ease of description, only the parts related to the present application are shown in the accompanying drawings, not all structures.

[0080] First, the terms used in this application are explained:

[0081] The staging environment is an environment between the development environment and the production environment. Developers usually simulate problems and solutions in the production environment in the staging environment, test new functions and code changes, and ensure that business codes can reduce errors and problems as much as possible before they are officially deployed to the production environment.

[0082] The production environment is the environment where the software or system is actually deployed and runs for end users.

[0083] At present, the performance problems caused by SQL can only be dealt with after the fact, that is, after the business code is released to the production environment, the SQL execution log of the production database is recorded, and the management personnel analyze the SQL execution log to extract low-performance SQL for optimization. The applicant's research found that during the production environment release process, the low-performance SQL that exists may affect the performance of the production database and even cause the production database to fail. That is, the current production environment release method is ineffective. After the low-performance SQL impact occurs, the SQL execution log is analyzed, and it is impossible to foresee the inefficient SQL, and the post-analysis of the inefficient SQL cannot effectively intercept the low-performance SQL, and it is impossible to guarantee the stability of the production environment database.

[0084] Based on this, the present application provides a production environment publishing method, device, equipment and medium, the method comprising: in response to receiving a production environment publishing request, obtaining a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database; wherein the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprint of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprint of the production environment; the production environment publishing request is used to indicate that the target business code in the pre-release environment is released to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces the database performance; comparing the low-performance SQL fingerprint database and the production SQL fingerprint database, detecting whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment; if it is detected that the pre-release environment has a newly added low-performance SQL, the newly added low-performance SQL is optimized to obtain the target business code, and the target business code includes the optimized SQL; and publishing the target business code to the production environment.

[0085] In this way, when a production environment release request is received, the low-performance SQL fingerprint database and the production SQL fingerprint database are first compared to obtain the low-performance SQL in the pre-release environment, and the low-performance SQL is optimized before being released to the production environment, thereby realizing early detection of low-performance SQL. Compared with the post-slow SQL analysis of the prior art, the present application can intercept low-performance SQL outside the production environment database, which reduces the threat of low-performance SQL to the stability of the production environment database to a certain extent and improves the production environment release effect.

[0086] To facilitate understanding of the specific implementation of the production environment publishing method provided in the embodiment of the present application, the following will be described in conjunction with the accompanying drawings.

[0087] It should be noted that the subject implementing the production environment publishing method may be a production environment publishing system, and the production environment publishing system may be the production environment publishing system 100 provided in the embodiment of the present application, and the production environment publishing system 100 may be carried in an electronic device or a functional module of an electronic device. The electronic device in the embodiment of the present application may be any device capable of implementing the production environment publishing method in the embodiment of the present application, for example, an Internet of Things (IoT) device.

[0088] See also Figure 1 A production environment publishing system 100 provided in an embodiment of the present application includes an SQL fingerprint acquisition module 101, an incremental SQL comparison module 102, and an interception module 103;

[0089] The SQL fingerprint acquisition module 101 is used to obtain a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database in response to receiving a production environment release request; the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprints of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprints of the production environment; the production environment release request is used to instruct to release the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces the database performance;

[0090] The incremental SQL comparison module 102 is used to compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is a new low-performance SQL in the pre-release environment; the new low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment;

[0091] The interception module 103 is used to optimize the newly added low-performance SQL to obtain a target business code if it is detected that there is a new low-performance SQL in the pre-release environment, and the target business code includes the optimized SQL;

[0092] The interception module 103 is also used to publish the target business code to the production environment.

[0093] It should be noted that, by utilizing the incremental SQL comparison module 102, newly added low-performance SQL in the pre-release environment can be discovered before being released to the production environment, and the low-performance SQL can be intercepted outside the production environment database, thereby reducing the threat of low-performance SQL to the stability of the production environment database to a certain extent.

[0094] In a possible implementation, the production environment publishing system 100 further includes: a full SQL collection module 104, an SQL fingerprint calculation module 105, and an SQL new addition judgment module 106;

[0095] The full SQL collection module 104 is used to collect data from the pre-release environment database and the production environment database to obtain the pre-release environment SQL and the production environment SQL; the pre-release environment database is used to store simulation test data; the production environment database is used to store real business data;

[0096] SQL fingerprint calculation module 105, used to perform fingerprint calculation on pre-release environment SQL to obtain pre-release environment SQL fingerprint, and perform fingerprint calculation on production environment SQL to obtain production environment SQL fingerprint;

[0097] The SQL addition judgment module 106 is used to store the pre-release environment SQL fingerprint in the low-performance SQL fingerprint database if the pre-release environment SQL is a low-performance SQL in a newly added state;

[0098] The SQL addition judgment module 106 is further used to store the production environment SQL fingerprint in the production SQL fingerprint database if the production environment SQL is in a new state.

[0099] It should be noted that the use of the full SQL collection module 104 can ensure zero loss of SQL collection in the pre-release environment and the production environment, thereby improving the accuracy of obtaining low-performance SQL.

[0100] During the specific execution process, the database proxy middleware may be used to forward the request to the message queue, and then the full SQL collection module 104 may be called to collect the pre-release environment SQL and the production environment SQL. Normally, the database proxy middleware does not store SQL data. The full SQL collection module 104 is usually deployed on the database server, and the database proxy middleware is deployed between the application server and the database server as a communication intermediary between the two.

[0101] In a possible implementation, the SQL fingerprint calculation module 105 is specifically used to:

[0102] Build the abstract syntax tree corresponding to the pre-release environment SQL;

[0103] Extract features from the abstract syntax tree to obtain information about database objects; database objects represent the structure or elements of data stored in the database;

[0104] Splice the information of database objects to obtain de-parameterized SQL;

[0105] Perform hash algorithm calculation on the de-parameterized SQL to obtain the SQL fingerprint of the pre-release environment.

[0106] In a possible implementation, the SQL fingerprint calculation module 105 is specifically used to:

[0107] Build the abstract syntax tree corresponding to the production environment SQL;

[0108] Extract features from the abstract syntax tree to obtain information about database objects; database objects represent the structure or elements of data stored in the database;

[0109] Splice the information of database objects to obtain de-parameterized SQL;

[0110] Perform hash algorithm calculation on the de-parameterized SQL to obtain the SQL fingerprint of the production environment.

[0111] In a possible implementation, SQL adds a new judgment module 106, which is specifically used to:

[0112] Check whether the pre-release environment SQL fingerprint exists in the pre-release environment historical fingerprint database;

[0113] If it does not exist, obtain the pre-release environment SQL as a newly added state;

[0114] Perform performance diagnosis on the SQL in the pre-release environment and obtain the diagnosis results;

[0115] When the diagnosis result indicates that the pre-release environment SQL is a low-performance SQL, the pre-release environment SQL fingerprint is stored in a low-performance SQL fingerprint database.

[0116] In a possible implementation, the production environment publishing system 100 further includes a SQL performance diagnosis module 107;

[0117] The SQL performance diagnosis module 107 is used to perform performance diagnosis on the pre-release environment SQL fingerprint according to preset feature standards to obtain the diagnosis results; the preset feature standards include at least one of the number of scanned rows, the returned result set, the execution duration, whether the full table is scanned, whether the update semantic logic is correct, and whether the use of hit index is not the best practice.

[0118] In a possible implementation, SQL adds a new judgment module 106, which is specifically used to:

[0119] Check whether the production environment SQL fingerprint exists in the production environment historical fingerprint database;

[0120] If it does not exist, obtain the production environment SQL as a newly added state;

[0121] Store the production environment SQL fingerprint in the production SQL fingerprint database.

[0122] In this way, through the above-mentioned production environment release system, whether new low-performance SQL appears before each production environment release, and prevent further changes from being released to the production environment. Compared with the traditional post-analysis and management of slow SQL, the embodiment of the present application can achieve foresight and perceive risks in advance, which is the key to ensuring the stability of the production environment database.

[0123] Figure 2 A flowchart of a production environment publishing method provided in an embodiment of the present application. The method may include the following steps:

[0124] S1, in response to receiving a production environment release request, obtaining a low-performance SQL fingerprint database and a production SQL fingerprint database.

[0125] Among them, the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprints of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprints of the production environment; the production environment release request is used to instruct the target business code in the pre-release environment to be released to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces the database performance.

[0126] In a possible implementation, the process of obtaining a low-performance SQL fingerprint database and a production SQL fingerprint database may include:

[0127] S11, collect data from the pre-release environment database and the production environment database to obtain the pre-release environment SQL and the production environment SQL.

[0128] It should be noted that the pre-production environment database is used to store simulation test data; the production environment database is used to store real business data.

[0129] In a possible implementation, the embodiment of the present application may also obtain a pre-release environment SQL set and a production environment SQL set, wherein the pre-release environment SQL set includes at least one pre-release environment SQL, and the production environment SQL set includes at least one production environment SQL. Among them, one SQL has and has one fingerprint, and if there are multiple SQLs with the same features, the obtained SQL fingerprints are also the same.

[0130] S12, performing fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint, and performing fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint.

[0131] It should be noted that fingerprint calculation can be regarded as a special feature extraction method. It should be noted that the reason why SQL fingerprint is obtained in the embodiment of the present application is for the subsequent SQL fingerprint comparison. Because SQL is essentially a string, the SQL obtained under different SQL query conditions in the same business scenario is also different. Therefore, SQL is usually not directly comparable. Therefore, feature extraction can be performed on SQL to calculate the SQL fingerprint corresponding to SQL. Among them, the whole process of obtaining SQL fingerprint is also called de-parameterization.

[0132] In a possible implementation, S12 provided in the embodiment of the present application may include:

[0133] S121, construct an abstract syntax tree corresponding to the pre-release environment SQL.

[0134] In the specific implementation process, semantic analysis can be performed on the pre-release environment SQL to construct an abstract syntax tree corresponding to the pre-release environment SQL.

[0135] S122, extract features from the abstract syntax tree to obtain information about database objects; database objects represent structures or elements of data stored in the database.

[0136] The database object may include at least one of an SQL type, an SQL table, an SQL column, an SQL query condition, and an SQL update condition.

[0137] S123, splicing the information of the database objects to obtain de-parameterized SQL.

[0138] In the embodiment of the present application, the information of the database objects can be spliced ​​according to the preset splicing rules to obtain a character string, and the character string constitutes the de-parameterized SQL.

[0139] S124, performing hash algorithm calculation on the de-parameterized SQL to obtain a pre-release environment SQL fingerprint.

[0140] It should be noted that a hash algorithm is performed on the de-parameterized SQL to obtain a corresponding MD5 message-digest algorithm value, and the MD5 message-digest algorithm value is the SQL fingerprint of the pre-release environment.

[0141] In a possible implementation, S12 provided in the embodiment of the present application may further include:

[0142] S125, construct an abstract syntax tree corresponding to the production environment SQL.

[0143] In the specific implementation process, the production environment SQL can be semantically parsed to construct an abstract syntax tree corresponding to the production environment SQL.

[0144] S126, extract features from the abstract syntax tree to obtain information about database objects; database objects represent structures or elements of data stored in the database.

[0145] S127, concatenate the information of the database objects to obtain de-parameterized SQL.

[0146] S128, performing hash algorithm calculation on the de-parameterized SQL to obtain the SQL fingerprint of the production environment.

[0147] It should be noted that the description of S125 to S128 can refer to the above S121 to S124, which will not be repeated here. The execution process of S121 to S124 and the execution process of S125 to S128 can be executed simultaneously or successively. The embodiment of the present application does not limit the order of obtaining the pre-release environment SQL fingerprint and obtaining the production environment SQL fingerprint.

[0148] S13: If the pre-release environment SQL is a low-performance SQL in a newly added state, the pre-release environment SQL fingerprint is stored in a low-performance SQL fingerprint database.

[0149] In a possible implementation, S13 provided in the embodiment of the present application may include:

[0150] S131, detecting whether there is a pre-launch environment SQL fingerprint in the pre-launch environment historical fingerprint database.

[0151] It should be noted that the pre-release environment historical fingerprint database is used to store SQL fingerprints corresponding to all SQLs in the pre-release environment, including normal SQL fingerprints of normal SQLs and low-performance SQL fingerprints of low-performance SQLs.

[0152] S132, if it does not exist, obtaining the pre-release environment SQL is a newly added state.

[0153] S133, performing performance diagnosis on the pre-release environment SQL to obtain a diagnosis result.

[0154] In a possible implementation, S133 may include: performing performance diagnosis on the pre-release environment SQL fingerprint according to preset feature standards to obtain a diagnosis result; the preset feature standards include at least one of the number of scanned rows, the returned result set, the execution duration, whether a full table scan is performed, whether the update semantic logic is correct, and whether the use of the hit index is not a best practice.

[0155] As an example, the number of scanned rows can be set to 20,000 rows, the returned result set to 2,000 rows, and the execution duration to 300 milliseconds. Then, if a performance diagnosis is performed on the pre-release environment SQL, and at least one of the following diagnostic results is obtained: the number of scanned rows is greater than 20,000 rows, the returned result set is greater than 2,000 rows, the execution duration is greater than 300 milliseconds, there is a full table scan, an update semantic logic error, and the use of hit index is not the best practice, then the pre-release environment SQL is judged to be a low-performance SQL. Of course, the above is only an example, and the embodiment of the present application does not limit the threshold of the preset feature standard.

[0156] S134: When the diagnosis result indicates that the pre-release environment SQL is a low-performance SQL, the pre-release environment SQL fingerprint is stored in a low-performance SQL fingerprint database.

[0157] When the pre-launch environment SQL is obtained as a low-performance SQL, the pre-launch environment SQL fingerprint corresponding to the pre-launch environment SQL is stored in a low-performance SQL fingerprint database, so as to be compared with the production SQL fingerprint database to determine whether there is a new low-performance SQL in the pre-launch environment.

[0158] It should be noted that after S134, the pre-release environment SQL fingerprint can also be stored in the pre-release environment historical fingerprint database. When performing performance diagnosis of the next pre-release environment SQL, the diagnosis result can be directly obtained to avoid repeated performance diagnosis and reduce the amount of calculation.

[0159] In a possible implementation, S13 provided in the embodiment of the present application may include:

[0160] S135, detecting whether there is a production environment SQL fingerprint in the production environment history fingerprint database;

[0161] It should be noted that the production environment historical fingerprint database is used to store SQL fingerprints in the production environment during each production environment release process.

[0162] S136, if it does not exist, obtain the production environment SQL as a newly added state.

[0163] Among them, if the production environment SQL fingerprint does not exist in the production environment historical fingerprint database, the production environment SQL fingerprint is stored in the production environment historical fingerprint database to determine whether there is a new production environment SQL in the next production environment, reducing the amount of calculation.

[0164] S137, storing the production environment SQL fingerprint in the production SQL fingerprint database.

[0165] S14: If the production environment SQL is in a newly added state, the production environment SQL fingerprint is stored in the production SQL fingerprint database.

[0166] It should be noted that the production SQL fingerprint database stores the newly added production environment SQL in the production environment.

[0167] S2, compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is any new low-performance SQL in the pre-release environment.

[0168] It should be noted that the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment and does not exist in the production environment corresponding to the low-performance SQL fingerprint. That is, if the low-performance SQL is newly added in the pre-release environment and does not exist in the production environment, then the low-performance SQL is judged to be a newly added low-performance SQL.

[0169] S3: If it is detected that there is a new low-performance SQL in the pre-release environment, the new low-performance SQL is optimized to obtain the target business code, and the target business code includes the optimized SQL.

[0170] In one possible implementation, if it is detected that there is no new low-performance SQL in the pre-release environment, there is no need to execute the optimization process, and S3 is directly executed to realize the production environment release.

[0171] S4, release the target business code to the production environment.

[0172] In this way, through the production environment publishing method provided in the embodiment of the present application, low-performance SQL is discovered during the production environment publishing process, low-performance SQL is prevented from being published to the production environment, and the threat of low-performance SQL to database stability is reduced, which can effectively improve the performance and response speed of the database system to better meet the growing data processing needs.

[0173] To facilitate understanding of the specific implementation of the production environment publishing method provided in the embodiment of the present application, the production environment publishing method is described below in conjunction with the production environment publishing system 100.

[0174] See also Figure 3 , which is a schematic diagram of the execution of a production environment release process provided in an embodiment of the present application, including a processing stage, a new SQL discovery stage, and a new low-performance SQL interception stage.

[0175] The processing stages include the pre-production environment processing stage 1 and the production environment processing stage 2.

[0176] When the target business code of the pre-release environment is released to the production environment, the incremental SQL comparison module 102 is used to check whether there is any new low-performance SQL in the pre-release environment.

[0177] Specifically, execute the following process:

[0178] 1. The execution process of the pre-release environment processing phase includes the following steps:

[0179] 1.1 The full SQL collection module 104 collects the SQL requested by the application from the database proxy middleware.

[0180] 1.2 The SQL fingerprint calculation module 105 performs fingerprint calculation on SQL.

[0181] 1.3 Call the SQL new addition judgment module 106 to judge whether the SQL fingerprint exists in the historical fingerprint database, and the historical fingerprint database is the pre-release environment historical fingerprint database.

[0182] 1.4 If it does not exist, call the SQL performance diagnosis 107 module to perform SQL performance diagnosis.

[0183] 1.5 If the SQL is a low-performance SQL, the fingerprint of the low-performance SQL is stored in the low-performance SQL database.

[0184] 2. The execution process of the production environment includes the following steps:

[0185] 2.1 The full SQL collection module 104 collects the SQL requested by the application from the database proxy middleware.

[0186] 2.2 The SQL fingerprint calculation module 105 performs fingerprint calculation on SQL.

[0187] 2.3 Call the SQL new addition judgment module 106 to judge whether the SQL fingerprint exists in the historical fingerprint database, and the historical fingerprint database is the production environment historical fingerprint database.

[0188] 2.4 If it does not exist, store the SQL and fingerprint information in the production SQL fingerprint database.

[0189] 3. The incremental SQL comparison module 102 compares the low-performance SQL fingerprint database recorded in the pre-release environment with the SQL fingerprint database in the production environment to obtain the newly added low-performance SQL in the pre-release environment.

[0190] If there is low-performance SQL, the interception module 103 is called to prevent the release until the SQL is optimized.

[0191] In this way, through the production environment release process provided by the embodiment of the present application, low-performance SQL query statements can be discovered and effectively intercepted in advance, which to a certain extent reduces the threat of low-performance SQL to the stability of the production environment database and improves the production environment release effect.

[0192] In addition, the present application embodiment also provides an electronic device 400, such as Figure 4 As shown, the electronic device 400 includes a processor 401 and a memory 402:

[0193] The memory 402 is used to store computer programs;

[0194] The processor 401 is used to execute according to the computer program Figure 2 The method provided.

[0195] In addition, an embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium is used to store a computer program, and the computer program is used to execute the method provided by the embodiment of the present application.

[0196] Through the description of the above implementation methods, it can be known that those skilled in the art can clearly understand that all or part of the steps in the above-mentioned embodiment method can be implemented by means of software plus a general hardware platform. Based on such an understanding, the technical solution of the present application can be embodied in the form of a software product, which can be stored in a storage medium, such as a read-only memory (ROM) / RAM, a magnetic disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network communication device such as a router) to execute the methods described in each embodiment of the present application or some parts of the embodiments.

[0197] Each embodiment in this specification is described in a progressive manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment. The device embodiment described above is only schematic, in which the modules described as separate components may or may not be physically separated, and the components displayed as modules may or may not be physical modules, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules can be selected according to actual needs to achieve the goals of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative work.

[0198] The above is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. It should be noted that, for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the present application, and these improvements and modifications should also be regarded as the protection scope of the present application.

Claims

1. A production environment publishing method, characterized in that: The method comprises: In response to receiving a production environment release request, a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database are obtained; the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprint of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprint of the production environment; the production environment release request is used to instruct to release the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces database performance; Compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment; If it is detected that the newly added low-performance SQL exists in the pre-release environment, the newly added low-performance SQL is optimized to obtain a target business code, where the target business code includes the optimized SQL; The target business code is released to the production environment.

2. The method according to claim 1, characterized in that The step of obtaining the low-performance SQL fingerprint database and the production SQL fingerprint database includes: Collect data from the pre-launch environment database and the production environment database to obtain the pre-launch environment SQL and the production environment SQL; the pre-launch environment database is used to store simulation test data; the production environment database is used to store real business data; Performing fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint, and performing fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint; If the pre-release environment SQL is a low-performance SQL in a newly added state, the pre-release environment SQL fingerprint is stored in the low-performance SQL fingerprint database; If the production environment SQL is in a newly added state, the production environment SQL fingerprint is stored in the production SQL fingerprint database.

3. The method according to claim 2, characterized in that The performing fingerprint calculation on the pre-release environment SQL to obtain the pre-release environment SQL fingerprint includes: Constructing an abstract syntax tree corresponding to the pre-release environment SQL; Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database; splicing the information of the database objects to obtain de-parameterized SQL; A hash algorithm is performed on the de-parameterized SQL to obtain the pre-release environment SQL fingerprint.

4. The method according to claim 2, characterized in that: The performing fingerprint calculation on the production environment SQL to obtain the production environment SQL fingerprint includes: Construct an abstract syntax tree corresponding to the production environment SQL; Extracting features from the abstract syntax tree to obtain information about database objects; the database objects represent structures or elements of data stored in the database; splicing the information of the database objects to obtain de-parameterized SQL; A hash algorithm is performed on the de-parameterized SQL to obtain the production environment SQL fingerprint.

5. The method according to claim 2, characterized in that: If the pre-release environment SQL is a low-performance SQL in a newly added state, storing the pre-release environment SQL fingerprint in the low-performance SQL fingerprint database includes: Check whether the pre-launch environment SQL fingerprint exists in the pre-launch environment historical fingerprint database; If it does not exist, the pre-release environment SQL is obtained as a newly added state; Performing performance diagnosis on the pre-release environment SQL to obtain a diagnosis result; When the diagnosis result indicates that the pre-release environment SQL is a low-performance SQL, the pre-release environment SQL fingerprint is stored in the low-performance SQL fingerprint database.

6. The method according to claim 5, characterized in that The performing performance diagnosis on the pre-release environment SQL to obtain the diagnosis result includes: The pre-release environment SQL fingerprint is subjected to performance diagnosis according to preset characteristic standards to obtain the diagnosis result; the preset characteristic standards include at least one of the number of scanned rows, the returned result set, the execution duration, whether the full table is scanned, whether the update semantic logic is correct, and whether the use of the hit index is not the best practice.

7. The method according to claim 2, characterized in that If the production environment SQL is in a newly added state, storing the production environment SQL fingerprint in the production SQL fingerprint database includes: Check whether the production environment SQL fingerprint exists in the production environment historical fingerprint database; If it does not exist, the production environment SQL is obtained as a newly added state; The production environment SQL fingerprint is stored in the production SQL fingerprint database.

8. A production environment release system, characterized in that: The system includes a SQL fingerprint acquisition module, an incremental SQL comparison module and an interception module; The SQL fingerprint acquisition module is used to obtain a low-performance structured query language SQL fingerprint database and a production SQL fingerprint database in response to receiving a production environment release request; the low-performance SQL fingerprint database is used to store the low-performance SQL fingerprints of the pre-release environment; the production SQL fingerprint database is used to store the newly added SQL fingerprints of the production environment; the production environment release request is used to instruct to release the target business code in the pre-release environment to the production environment; the low-performance SQL corresponding to the low-performance SQL fingerprint is the SQL that reduces database performance; The incremental SQL comparison module is used to compare the low-performance SQL fingerprint database with the production SQL fingerprint database to detect whether there is a newly added low-performance SQL in the pre-release environment; the newly added low-performance SQL is a low-performance SQL that is newly added in the pre-release environment corresponding to the low-performance SQL fingerprint and does not exist in the production environment; The interception module is used for optimizing the newly added low-performance SQL to obtain a target business code if it is detected that the newly added low-performance SQL exists in the pre-release environment, and the target business code includes the optimized SQL; The interception module is also used to publish the target business code to the production environment.

9. An electronic device, characterized in that: The electronic device comprises a processor and a memory: The memory is used to store computer programs; The processor is configured to execute the method according to any one of claims 1 to 7 according to the computer program.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium is used to store a computer program, and the computer program is used to execute the method according to any one of claims 1 to 7.

Citation Information

Patent Citations

  • Determination method for abnormal SQL (structured query language) statement and server

    CN104778185A

  • SQL sentence processing method, device, computer device and storage medium

    CN108197306A

  • Slow SQL statement prediction method and system

    CN112613176A

  • SQL risk identification method, device and equipment

    CN112784273A

  • Method and device for monitoring and managing slow SQL of database

    CN113468218A