SQL (Structured Query Language) statement analysis method and device, electronic equipment and computer storage medium

Abstract

The invention discloses an SQL statement analysis method and device, electronic equipment and a computer storage medium, and belongs to the technical field of databases. The method comprises the steps that structured query language (SQL) statements are collected from all nodes of a whole stack; the SQL statements collected from all the nodes are converted into standard formats, and unified storage is carried out; playing back and verifying the SQL statement in a target database to obtain a verification result; and performing analysis and adjustment according to the verification result, circulating playback and adjustment processes until the verification result meets a business target, and outputting optimal practice to form a knowledge base. According to the method, the service SQL is collected from the whole stack, systematic analysis of the service SQL is achieved, the problem of analysis fragmentation is solved, a knowledge base is formed by continuously and circularly playing back and adjusting the process, powerful reference and basis are provided for SQL statement analysis and optimization, and the execution efficiency and service performance of SQL statements are improved.

Description

Technical Field

[0001] The present application relates to the field of databases, and in particular to a method, device, electronic device and computer storage medium for analyzing SQL statements. Background Art

[0002] During business development, testing, data migration, system switching, upgrading, and problem location, it is often necessary to perform quality analysis on business SQL statements, especially in terms of SQL statement execution correctness, compatibility (such as syntax errors), and performance (such as latency and resource utilization).

[0003] During database switching, especially heterogeneous database switching, compatibility and performance verification consumes a lot of manpower and material resources, and is extremely costly. Although various databases currently have log records, there are problems such as large log volumes, scattered across various nodes, and lack of systematic analysis.

[0004] With respect to the above-mentioned problems existing in the related technologies, no effective solutions have been found so far. Summary of the invention

[0005] The present application provides a method, device, electronic device and computer storage medium for analyzing SQL statements to solve the above-mentioned technical problems existing in the related technologies.

[0006] According to an embodiment of the present application, a method for analyzing SQL statements is provided, including: collecting structured query language SQL statements from each node of the full stack; converting the SQL statements collected from each node into a standard format and storing them in a unified manner; replaying and verifying the SQL statements in a target database to obtain verification results; performing analysis and adjustment based on the verification results, looping the playback and adjustment process until the verification results meet the business objectives, outputting best practices, and forming a knowledge base.

[0007] According to another embodiment of the present application, a device for analyzing SQL statements is provided, including: a full-stack acquisition module, used to collect structured query language SQL statements from each node of the full stack; a data processing module, used to convert the SQL statements collected from each node into a standard format and store them uniformly; a playback verification module, used to playback and verify the SQL statements in a target database to obtain verification results; an analysis and tuning module, used to perform analysis and adjustment based on the verification results, loop the playback and adjustment process until the verification results meet the business goals, output the best tuning suggestions, and form a knowledge base.

[0008] Optionally, the playback verification module includes an acquisition unit for acquiring a target value of a set SQL quality indicator; a playback unit for replaying SQL statements in a target database and collecting corresponding execution indicator values; a comparison unit for comparing the execution indicator value with the target value; and a judgment unit for judging whether the execution indicator value reaches the target value to form a verification result.

[0009] Optionally, the analysis and tuning module includes a screening unit, which is used to screen out high-frequency SQL with an execution frequency higher than a first preset threshold, suspicious SQL with an error number exceeding a second preset threshold, and slow SQL with an execution time exceeding a third preset threshold if the verification result does not meet the business goal; and an adjustment unit, which is used to adjust the database parameters or SQL statements based on the high-frequency SQL, suspicious SQL and slow SQL, and record the adjustment process.

[0010] Optionally, the data processing module includes a cleaning unit for filtering and cleaning the collected SQL statements; a storage unit for converting the filtered and cleaned SQL statements into a standard format and sending them to a message center for centralized storage.

[0011] Optionally, the SQL statement analysis device also includes a model output module, which is used to obtain a pre-trained target machine learning model, and the target machine learning model is integrated into the target database using middleware; the SQL statement is input into the target machine learning model to obtain the optimal tuning suggestions output by the target machine learning model.

[0012] Optionally, the full-stack acquisition module includes a deployment unit, which is used to deploy a side-mounted plug-in between the business system and the target database; and a first acquisition unit, which is used to collect structured query language SQL statements from each node of the full stack through the side-mounted plug-in.

[0013] Optionally, the full-stack acquisition module includes a detection unit for detecting a configured acquisition mode, wherein the acquisition mode includes an agent mode or an agentless mode; a second acquisition unit for collecting data from a backup machine or a backup database of each node of the full-stack according to the acquisition mode, and setting read-only permissions for the collected SQL statements.

[0014] According to another embodiment of the present application, a computer storage medium is provided, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above-mentioned device embodiments when running.

[0015] According to another embodiment of the present application, there is also provided an electronic device, including a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus; wherein: the memory is used to store computer programs; the processor is used to execute the steps in the above method by running the program stored in the memory.

[0016] According to yet another embodiment of the present application, a computer program product comprising instructions is also provided, which enables the computer to execute the steps in the above method when the computer program product is executed on the computer.

[0017] Through the embodiments of the present application, by collecting SQL statements from various nodes of the full stack, a systematic analysis of business SQL statements is achieved, the problem of analysis fragmentation is solved, and a knowledge base is formed through continuous loop playback and adjustment processes, which provides a powerful reference and basis for SQL statement analysis and optimization, and improves the execution efficiency and business performance of SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0019] Figure 1 is a hardware structure block diagram of a computer according to an embodiment of the present application;

[0020] Figure 2 It is a flow chart of a method for analyzing SQL statements in an embodiment of the present application;

[0021] Figure 3 It is a schematic diagram of an analysis system for SQL statements in an embodiment of the present application;

[0022] Figure 4 It is a schematic diagram of the data collection method of an embodiment of the present application;

[0023] Figure 5 This is a flow chart of an application example of SQL quality analysis in an embodiment of the present application;

[0024] Figure 6 It is a structural block diagram of an analysis device for SQL statements in an embodiment of the present application. DETAILED DESCRIPTION

[0025] In order to enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only embodiments of a part of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in the field without creative work should fall within the scope of protection of the present application. It should be noted that the embodiments in the present application and the features in the embodiments can be combined with each other without conflict.

[0026] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0027] Example 1

[0028] The method embodiment provided in the first embodiment of the present application can be executed in a computer, a tablet or a similar computing device. Taking running on a computer as an example, Figure 1 is a hardware structure block diagram of a computer in an embodiment of the present application. Figure 1 As shown, the computer may include one or more ( Figure 1 Only one is shown in the figure) a processor 102 (the processor 102 may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA) and a memory 104 for storing data. Optionally, the computer may also include a transmission device 106 and an input / output device 108 for communication functions. It can be understood by those skilled in the art that Figure 1 The structure shown is only for illustration and does not limit the structure of the above-mentioned computer. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations shown.

[0029] The memory 104 can be used to store computer programs, for example, software programs and modules of application software, such as a computer program corresponding to a method for analyzing a SQL statement in an embodiment of the present application. The processor 102 executes various functional applications and data processing by running the computer program stored in the memory 104, that is, to implement the above method. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include a memory remotely arranged relative to the processor 102, and these remote memories may be connected to the computer via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0030] The transmission device 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of the computer. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, referred to as NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission device 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0031] In this embodiment, a method for analyzing SQL statements is provided. Figure 2 is a flow chart of a method for analyzing SQL statements according to an embodiment of the present application, such as Figure 2 As shown, the process includes the following steps:

[0032] Step S10, collecting structured query language SQL statements from each node of the full stack;

[0033] The full stack covers all aspects including front-end, back-end and database management, such as Figure 3As shown, the full stack nodes include application nodes, network nodes, database nodes and infrastructure nodes. Among them, application nodes include but are not limited to development clients, integrated development environments (IDE, Integrated Development Environment, such as IntelliJ IDEA, VisualStudio Code, etc.), DevOps tool sets (emphasizing the close collaboration between software developers (Development) and operation and maintenance personnel (Operations)), application APPs, JDBC / ODBC drivers; database nodes include computing nodes CN, data storage nodes DN and proxy nodes; infrastructure nodes include computing (such as servers, operating systems), storage and networks, and structured query language (SQL) statements are fully collected from these nodes.

[0034] Step S20, converting the SQL statements collected from each node into a standard format and storing them uniformly;

[0035] The SQL statements collected from the above nodes are standardized through the data extraction, transformation and loading (ETL) process, and stored centrally, and the data is associated from the application to the database.

[0036] Step S30, replaying and verifying the SQL statement in the target database to obtain the verification result;

[0037] Step S40, analyzing and adjusting according to the verification results, looping back and adjusting the process until the verification results meet the business objectives, outputting the best practices, and forming a knowledge base.

[0038] The standardized SQL statements are replayed and verified in the target database to obtain their execution results and performance. The SQL statements are analyzed and adjusted based on the verification results. The replay and adjustment process is repeated until the verification results meet the business objectives. In this process, the tuning experience of SQL statements is continuously accumulated and optimized to form a knowledge base. Finally, the best tuning suggestions are output.

[0039] Through the above steps, by collecting SQL statements from all nodes of the full stack, a systematic analysis of business SQL statements is achieved, the problem of fragmented analysis is solved, and the tuning process is stored through continuous loop playback and adjustment process, and the best tuning practices are output to form a knowledge base, which provides a strong reference and basis for future SQL statement analysis and optimization, and improves the execution efficiency of SQL statements and business performance.

[0040] In this embodiment, SQL statements are collected from each node of the full stack, and different collection plug-ins can be provided according to different application scenarios, such as Figure 4As shown, the collection plug-in includes but is not limited to APP collection plug-in, ORM collection plug-in, connection pool collection plug-in, driver collection plug-in and network collection plug-in, wherein the APP collection plug-in includes java collection plug-in, python collection plug-in and other language collection plug-ins. Collection also includes database log collection and data source management, wherein data source management includes manual input or import, APP (JAVA IDE, CI / CD, DevOps), database (such as Oracle, DB2, mysql, etc.). The collection components and deployment locations to be used are determined according to the actual networking and business requirements. In this embodiment, the collection plug-in is preferably deployed on the front-end driver side, that is, the throat key position between the business and the database, to ensure that the collected traffic is comprehensive and the business scenarios cover a wide range.

[0041] This embodiment adopts a variety of business traffic collection methods: including but not limited to application logs, ORM logs, connection pool logs, driver logs, driver proxy logs, network traffic capture, and database logs (such as WCR, general, binlog, etc.).

[0042] This implementation adopts multi-threaded concurrent collection to achieve efficient collection and extract business SQL and related indicators (such as latency, CPU, memory usage, etc.) at the same time.

[0043] Before collecting data, perform relevant configurations, including whether to enable data compression, network traffic control, proxy mode, non-proxy mode, data encryption, etc., and create a collection task.

[0044] In one implementation of this embodiment, collecting SQL statements from each node of the full stack includes: deploying a side-mounted plug-in between the business system and the target database; and collecting structured query language SQL statements from each node of the full stack through the side-mounted plug-in.

[0045] In this embodiment, the side-hanging plug-in collects SQL statements and other related information by intercepting and parsing the communication protocol between the business system and the database. By side-hanging, the impact on the existing architecture and the impact on performance are reduced.

[0046] In another implementation of this embodiment, collecting SQL statements from each node of the full stack includes:

[0047] Detect the configured collection mode, which includes agent mode or agentless mode; according to the collection mode, collect data from the backup machine or backup database of each node in the full stack, and set read-only permissions for the collected SQL statements. Ensure data security without causing additional load pressure on the main node.

[0048] In this embodiment, the SQL statements collected from each node are converted into a standard format and uniformly stored, including: filtering and cleaning the collected SQL statements; converting the filtered and cleaned SQL statements into a standard format, and then sending them to a message center for centralized storage.

[0049] In this implementation, the collected business SQL data is subjected to ETL (data extraction, transformation, loading) processing to filter, clean and classify, remove irrelevant data and noise, form formatted and standardized data, and load it into a unified storage center (such as a Kafka message center). The storage center is equivalent to an SQL data center, providing a high-performance, fault-tolerant, easy-to-use and flexible data flow centralized storage platform to provide data support for subsequent intelligent computing.

[0050] This implementation method stores the full stack data in a unified and centralized manner, rather than dispersing the data on various nodes, and centrally extracts, analyzes and processes the data to improve efficiency.

[0051] In another implementation of this embodiment, replaying and verifying the SQL statement in the target database, and obtaining the verification result includes:

[0052] S31, obtaining a target value of a set SQL quality indicator;

[0053] S32, replaying the SQL statement in the target database and collecting the corresponding execution index value;

[0054] S33, comparing the execution index value with the target value;

[0055] S34, determining whether the execution index value reaches the target value, and forming a verification result.

[0056] Set specific target values ​​for SQL quality indicators based on actual business requirements. SQL quality indicators include but are not limited to number of executions, total response time, average response time, maximum response time, average CPU time, execution plan generation time, maximum number of returned rows, and maximum application waiting time.

[0057] Establish business training models according to different business scenarios, including default models.

[0058] The default training model example is shown in Table 1 below:

[0059]

[0060] Replay SQL statements in the target database, collect actual indicator execution status (including infrastructure resource utilization), and compare the execution value with the target value. If the execution value does not reach the target value, it is determined that the verification result does not meet the business goal. At this time, analysis and tuning are performed, and replay is performed again after tuning. The playback and adjustment process is repeated until the verification result meets the business goal. During the tuning process, the content of each adjustment is recorded, and the best optimization point is found, and the best tuning suggestion is output to form a knowledge base.

[0061] In one example, the target value of the SQL quality indicator "average response time" is set to 100ms. If the average response time corresponding to executing SQL in the target database is 90ms, that is, the execution value reaches the target value, then the verification result is judged to meet the business goal; if the average response time is 150ms, it means that the execution value has not reached the target value, and the verification result is judged to not meet the business goal. At this time, tuning is required. After tuning, SQL is replayed and relevant indicators are collected again. The tuning cycle is repeated until the execution value reaches the target value within 100ms. The content of each adjustment is recorded. For example, when certain parameters are adjusted, the target value is reached, which is equivalent to the best optimization point. The optimal tuning suggestion is formed and pushed to the knowledge base.

[0062] In another implementation of this embodiment, performing analysis and adjustment according to the verification result includes:

[0063] S41, if the verification result does not meet the business goal, filter out high-frequency SQL with an execution frequency higher than a first preset threshold, suspicious SQL with an error number exceeding a second preset threshold, and slow SQL with an execution time exceeding a third preset threshold;

[0064] S42: Based on the high-frequency SQL, suspicious SQL and slow SQL, adjust the database parameters or SQL statements, and record the adjustment process.

[0065] In this implementation, if the verification result fails to meet the standard, further analysis is performed, which can be performed from three dimensions: high-frequency SQL (TopSQL), suspicious SQL, and slow SQL.

[0066] Among them, for high-frequency SQL: SQL statements with high execution frequency, occupying more system resources, and having a greater impact on database performance (relatively long total execution time). Analyze from several dimensions such as the number of executions, total response time, average response time, number of errors, maximum response time, average CPU time, CPU share, and plan generation time. For suspicious SQL: SQL statements with potential risks or abnormal behaviors. Analyze from several dimensions such as the number of executions, total response time, average response time, maximum response time, average CPU time, plan generation time, maximum number of returned rows, maximum application waiting time, and maximum physical read. For slow SQL: SQL statements with relatively long execution time, usually refers to SQL statements whose execution time exceeds a certain time threshold (such as the default 100ms). Analyze from several dimensions such as the number of executions, total response time, average response time, and average CPU time.

[0067] In this embodiment, the analysis includes: a. Whether the parameter settings are reasonable (such as cache size): Check whether the database parameter settings are reasonable, such as whether the cache size is large enough to accommodate commonly used data and indexes. Adjust parameters to optimize query performance, such as increasing the cache size to reduce disk IO;

[0068] b. Whether resources are sufficient (CPU, memory, disk IO): Monitor the usage of resources such as CPU, memory, disk IO, etc. to ensure that resources are sufficient. If resources are insufficient, consider upgrading hardware or optimizing queries to reduce resource consumption;

[0069] c. Whether the index or plan is relatively optimal: Check whether there is an optimal index to support the query, such as whether a suitable composite index has been created. Analyze the execution plan to ensure that the query uses the optimal index and connection strategy. If necessary, regenerate or adjust the execution plan to optimize performance;

[0070] d. Determine the number of affected records and the number of returned records, and determine whether the number of partitions and rows involved in writing or querying is large: Analyze the query conditions, determine the range of the number of affected records, and avoid unnecessary full table scans. Optimize the query to return as few records as possible to reduce data transmission and processing overhead. Determine whether the number of partitions and rows involved in writing or querying is large, and consider partition optimization;

[0071] e. Whether there is cross-city access or cross-machine access: Check whether there is cross-city access or cross-machine access, which will increase network latency and bandwidth consumption. Optimize data layout and access mode, such as storing data closer to users;

[0072] f. For more complex SQL statements, intermediate results may be dumped to disk, and you need to confirm whether they meet expectations.

[0073] g: System-level troubleshooting: confirm dump, disk IO usage, etc.

[0074] Adjustments include adjusting parameters at the operating system level, such as system kernel parameters, maximum number of connections, etc., as well as adjusting system resource parameters, such as CPU utilization, memory utilization, network card IO, etc.; adjusting SQL itself, such as rewriting SQL, optimizing query conditions, etc.; adjusting indexes, such as adding indexes to tables without indexes; adjusting execution plans, such as regenerating execution plans to optimize performance, until business goals are achieved. After each adjustment, replay the SQL statements to verify the optimization effect. Repeat the adjustment process until the verification results meet the business goals. After achieving the business goals, summarize the best practices and output tuning suggestions. Organize the parameters, SQL statements, and tuning suggestions recorded during the adjustment process into a knowledge base. Form standard specifications in the knowledge base to clarify which parameters are effective within which range.

[0075] Through loop tuning, SQL statements are repeatedly iterated and optimized, and finally the best practice SQL statements are output and a knowledge base is generated. This knowledge base not only contains SQL standards and specifications, but also provides practical guidance for the entire life cycle and all scenarios such as development, testing, deployment and release, and operation and maintenance. Through practical verification, the knowledge base is further enriched and continuously improved.

[0076] Examples of optimization suggestions for the SQL knowledge base are shown in Table 2:

[0077]

[0078] In this embodiment, after converting the SQL statements collected from each node into a standard format and storing them uniformly, the method also includes: obtaining a pre-trained target machine learning model, and the target machine learning model is integrated into the target database using middleware; inputting SQL statements into the target machine learning model to obtain the best tuning suggestions output by the target machine learning model.

[0079] This implementation introduces SQL machine learning algorithms, continuously improves the knowledge base, accumulates the tuning experience base, forms SQL quality standards and specifications, and outputs optimization suggestions, etc. The machine learning algorithm can be directly called; or integrated into the target database using middleware or extensions, or combined with an external machine learning framework.

[0080] Combine SQL with machine learning technology: You can call and execute machine learning algorithms directly in the database. Or use middleware or extensions: Through middleware or extensions, machine learning algorithms can be integrated into the database without changing the existing database architecture. Or combine with external machine learning frameworks: Users can also obtain data through SQL queries, and then train and evaluate models in external machine learning frameworks (such as Python's scikit-learn, TensorFlow, etc.).

[0081] This embodiment considers that the system may have a certain impact on the business, and considers supporting some auxiliary functions, such as network traffic control, data encryption, desensitization, operation and maintenance, etc. as shown in Table 3 below:

[0082]

[0083] The solution of this embodiment provides an example method for full-stack and full-scenario intelligent evaluation of SQL quality. Figure 3 and Figure 5 , Figure 5 It is an implementation flow chart of the embodiment of the present application, including:

[0084] 1. Determine the collection components to be used and the deployment location based on the actual networking and customer requirements.

[0085] 2. Perform relevant configurations and whether to enable related auxiliary functions, such as whether to compress data, whether to enable network flow control, whether to use proxy mode, whether to encrypt data, etc. Create a collection task.

[0086] 3. Filter and clean the collected business data, and convert it into standard format data through ETL.

[0087] 4. Classify the processed data and store them centrally.

[0088] 5. Intelligent computing (SQLAI): Develop business target models: Establish different training models, including a default model. Develop specific SQL quality indicator target values ​​based on actual business requirements.

[0089] 6. Verify the statement playback, collect the corresponding indicators, and compare them with the target values. If the target is not met, further analysis is performed.

[0090] 7. After tuning, replay and re-collect relevant indicators, cycle tuning and training, and iterate repeatedly until the business goals are achieved.

[0091] 8. After achieving business goals, output best practices and tuning suggestions. Integrate SQL machine learning.

[0092] 9. Generate knowledge base, output SQL standards and specifications, accumulate experience base, etc.

[0093] 10. Apply practice, appropriately introduce expert opinions or experience, and manually enrich and improve the knowledge base

[0094] 11. Replay and verify again, close the loop, and continuously improve the knowledge base.

[0095] The SQL statement analysis method of this embodiment is widely used in the following scenarios, greatly improving the work efficiency in application scenarios such as development, testing, data migration, problem location, and upgrades. 1. Feedback the execution status of business SQL statements to client personnel for timely analysis; 2. Push business SQL statements to database side personnel such as DBA to timely analyze the execution status (correctness, compatibility, performance, etc.); 3. Pull the business SQL statements to the database side for playback to verify their correctness, compatibility, performance, etc.; 4. After the database system is upgraded, replay the business SQL statements intercepted before the upgrade to verify the compatibility and performance of the version. 5. During the migration of heterogeneous or homogeneous databases, verify the compatibility and performance differences of the target library with the source library. 6. Analyze the intercepted business traffic and feedback it to the business client to timely understand the business SQL execution status. 7. Analyze the intercepted business traffic and push it to the database side for archiving, so as to facilitate subsequent compatibility use such as after the upgrade. 8. Continuous training, repeated iterations, improve the training model, continuously precipitate the experience knowledge base, and continuously improve standards and specifications.

[0096] Through the description of the above implementation methods, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0097] Example 2

[0098] In this embodiment, a device for analyzing SQL statements is also provided to implement the above-mentioned embodiments and preferred implementation modes, which have been described and will not be repeated. As used below, the term "module" can be a combination of software and / or hardware that implements a predetermined function. Although the device described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.

[0099] Figure 6 is a structural block diagram of an analysis device for SQL statements according to an embodiment of the present application, such as Figure 6 As shown, the device comprises:

[0100] The full stack collection module 61 is used to collect structured query language SQL statements from each node of the full stack;

[0101] The data processing module 62 is used to convert the SQL statements collected from each node into a standard format and store them uniformly;

[0102] A playback verification module 63 is used to playback and verify the SQL statement in the target database and obtain a verification result;

[0103] The analysis and tuning module 64 is used to perform analysis and adjustment according to the verification result, loop back and adjust the process until the verification result meets the business goal, output the best tuning suggestion, and form a knowledge base.

[0104] It should be noted that the above modules can be implemented by software or hardware. For the latter, it can be implemented in the following ways, but not limited to: the above modules are all located in the same processor; or the above modules are located in different processors in any combination.

[0105] Example 3

[0106] An embodiment of the present application further provides a computer storage medium, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above method embodiments when running.

[0107] Optionally, in this embodiment, the computer storage medium may be configured to store a computer program for performing the following steps:

[0108] S1, collects structured query language SQL statements from each node of the full stack;

[0109] S2, converts the SQL statements collected from each node into a standard format and stores them uniformly;

[0110] S3, replay and verify the SQL statement in the target database to obtain the verification result;

[0111] S4, analyzing and adjusting according to the verification results, looping the playback and adjustment process until the verification results meet the business objectives, outputting best practices, and forming a knowledge base.

[0112] Optionally, in this embodiment, the above-mentioned computer storage medium may include but is not limited to: a USB flash drive, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk, and other media that can store computer programs.

[0113] An embodiment of the present application further provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.

[0114] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.

[0115] Optionally, in this embodiment, the processor may be configured to perform the following steps through a computer program:

[0116] S1, collects structured query language SQL statements from each node of the full stack;

[0117] S2, converts the SQL statements collected from each node into a standard format and stores them uniformly;

[0118] S3, replay and verify the SQL statement in the target database to obtain the verification result;

[0119] S4, analyzing and adjusting according to the verification results, looping the playback and adjustment process until the verification results meet the business objectives, outputting best practices, and forming a knowledge base.

[0120] Optionally, the specific examples in this embodiment may refer to the examples described in the above embodiments and optional implementation modes, and this embodiment will not be described in detail here.

[0121] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0122] In the above embodiments of the present application, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, please refer to the relevant description of other embodiments.

[0123] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0124] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0125] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0126] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a computer storage medium, including a number of instructions to enable a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned computer storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), mobile hard disk, disk or CD-ROM and other media that can store program codes.

[0127] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A method for analyzing SQL statements, characterized in that: The method comprises: Collect SQL statements from all nodes in the full stack; Convert SQL statements collected from each node into a standard format and store them uniformly; Replay and verify the SQL statement in the target database to obtain the verification result; Analyze and adjust according to the verification results, loop back and adjust the process until the verification results meet the business objectives, output best practices, and form a knowledge base.

2. The method according to claim 1, characterized in that Replay and verify the SQL statement in the target database, and obtain the verification result including: Get the target value of the set SQL quality indicator; Replay SQL statements in the target database and collect corresponding execution index values; Comparing the execution index value with the target value; Determine whether the execution index value reaches the target value and form a verification result.

3. The method according to claim 1, characterized in that: The analysis and adjustment according to the verification results include: If the verification result does not meet the business goal, filter out high-frequency SQL with an execution frequency higher than a first preset threshold, suspicious SQL with an error count exceeding a second preset threshold, and slow SQL with an execution time exceeding a third preset threshold; Based on the high-frequency SQL, suspicious SQL and slow SQL, adjust the database parameters or SQL statements, and record the adjustment process.

4. The method according to claim 1, characterized in that Convert SQL statements collected from each node into a standard format and store them uniformly, including: Filter and clean the collected SQL statements; After filtering and cleaning, the SQL statements are converted into a standard format and sent to the message center for centralized storage.

5. The method according to claim 1, characterized in that: After converting the SQL statements collected from each node into a standard format and storing them uniformly, the method further includes: Obtaining a pre-trained target machine learning model, wherein the target machine learning model is integrated into the target database using middleware; Input the SQL statement into the target machine learning model to obtain the best tuning suggestion output by the target machine learning model.

6. The method according to claim 1, characterized in that Collecting SQL statements from various nodes in the full stack includes: Deploy side-mounted plug-ins between the business system and the target database; The side-mounted plug-in is used to collect structured query language SQL statements from each node of the full stack.

7. The method according to claim 1, characterized in that Collecting SQL statements from various nodes in the full stack includes: Detecting a configured collection mode, wherein the collection mode includes a proxy mode or a non-proxy mode; According to the collection mode, the data is collected from the backup machine or backup database of each node of the full stack, and read-only permission is set for the collected SQL statements.

8. An analysis device for SQL statements, characterized in that: include: The full-stack collection module is used to collect SQL statements from each node of the full stack; The data processing module is used to convert the SQL statements collected from each node into a standard format and store them uniformly; A playback verification module is used to playback and verify the SQL statement in the target database and obtain the verification result; The analysis and tuning module is used to perform analysis and adjustment according to the verification results, loop back and adjust the process until the verification results meet the business objectives, output the best tuning suggestions, and form a knowledge base.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other through the communication bus; wherein: Memory, used to store computer programs; A processor, configured to execute the SQL statement analysis method according to any one of claims 1 to 7 by running a program stored in a memory.

10. A computer storage medium, characterized in that: The computer storage medium includes a stored program, wherein the program executes the SQL statement analysis method according to any one of claims 1 to 7 when running.

Images