Database statement adjustment method and device, program product and storage medium

Abstract

Embodiments of the invention provide a database statement adjustment method and apparatus, a program product and a storage medium. The method comprises the steps of obtaining a first log; to-be-adjusted types of N first statements to be adjusted are determined according to the first log, an adjustment mode matched with each first statement is searched from an adjustment information table according to the to-be-adjusted types of the N first statements, the N first statements are data processing statements used when multiple processing nodes process the target task in parallel, and the N first statements are data processing statements used when multiple processing nodes process the target task in parallel; n is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements whose execution time is greater than a preset threshold in the training log; and based on the adjustment mode of each first statement, adjusting the N first statements to obtain N target statements. The problem that the database statements cannot be efficiently adjusted in the prior art is solved, and the effect of efficiently adjusting the database statements is achieved.

Description

Technical Field

[0001] The embodiments of the present application relate to the field of computer technology. Specifically, the embodiments of the present application relate to a method and device for adjusting database statements, a program product, and a storage medium. Background Art

[0002] In the current data-driven business environment, big data technology has become a key factor in promoting the competitiveness of enterprises. With the explosive growth of data volume, Massively Parallel Processing (MPP) database systems have been widely adopted by many large enterprises due to their unique parallel architecture and excellent processing capabilities to support big data analysis and complex query scenarios. However, when faced with complex and large SQL statements, non-standard SQL statements will not only lead to a significant decline in query performance, but may also cause resource waste, runtime errors, etc. In severe cases, it may even threaten the stability and reliability of the database. Traditional SQL statement optimization methods have limitations in the detection of SQL statement norms, and it is difficult to comprehensively capture and correct potential norm problems in development, resulting in the problem of being unable to efficiently adjust database statements. Summary of the Invention

[0003] The embodiments of the present application provide a method and device for adjusting database statements, a program product, and a storage medium to at least solve the problem of being unable to efficiently adjust database statements in related technologies.

[0004] According to an embodiment of the present application, a method for adjusting database statements is provided, including: obtaining a first log, where the first log is a log after performing a preprocessing operation on an initial log, and the initial log is a log generated when a database system processes a target task in parallel through multiple processing nodes; determining, according to the first log, the types of adjustments to be made to N first statements to be adjusted, and looking up, from an adjustment information table, an adjustment method that matches each of the first statements according to the types of adjustments to be made to the N first statements, where the N first statements are data processing statements used when the multiple processing nodes process the target task in parallel, N is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements in a training log whose execution time is greater than a preset threshold; and adjusting the N first statements based on the adjustment method of each of the first statements to obtain N target statements.

[0005] In an exemplary embodiment, before obtaining the first log, the method further includes: collecting logs generated when multiple processing nodes process the target task in parallel through multiple collection nodes to obtain the initial log, and storing the initial log in a cloud storage space; extracting initial log data from the initial log, where the initial log data includes M initial statements and initial execution data corresponding to each initial statement, and N of the first statements to be adjusted are included in the M initial statements, and M is greater than or equal to N; respectively performing a preprocessing operation on the initial execution data corresponding to each initial statement to obtain the execution data corresponding to each initial statement, where the preprocessing operation is used to process exception information in the initial execution data and convert the unstructured initial execution data into structured data; determining the M initial statements and the execution data corresponding to each initial statement as the first log.

[0006] In an exemplary embodiment, before determining the adjustment types of the N first statements to be adjusted according to the first log and looking up the adjustment methods matching each first statement from the adjustment information table according to the adjustment types of the N first statements, the method further includes: parsing the statement structure of the training statement and the table data and function data in the training execution data of the training statement to obtain the transaction item set of each training statement, where the transaction item set further includes multiple item sets of the training statement, and the multiple item sets are all used to represent the execution context information of the training statement; determining K target item sets based on the occurrence frequency of each item set in the N transaction item sets, where the target item set is an item set with an occurrence frequency greater than a preset frequency in the N transaction item sets, and K is a natural number less than or equal to N; generating the adjustment information table based on the K target item sets.

[0007] In an exemplary embodiment, determining the types of adjustments to be made for N first statements to be adjusted according to the above first log, and searching for adjustment methods matching each of the above first statements from the adjustment information table according to the types of adjustments to be made for the N first statements, includes: determining N first statements to be adjusted from M initial statements according to the execution time and resource usage information in the execution data corresponding to each of the above initial statements in the above first log, where the first execution time of the above first statement is greater than a preset time threshold and / or the first resource usage information of the above first statement is greater than a preset resource threshold; parsing the statement structure of the above first statement and the table data and function data in the first execution data of the above first statement to determine the types of adjustments to be made for the N first statements, where the execution data includes the above first execution data, and the table data is used to represent the database tables involved in executing the above first statement; searching for the above adjustment methods for each of the above first statements from the above adjustment information table according to the types of adjustments to be made.

[0008] In an exemplary embodiment, adjusting N first statements based on the adjustment method for each of the above first statements to obtain N target statements, includes: determining the adjustment priorities of the N first statements according to the first execution time of the above first statement and / or the first resource usage information of the above first statement; adjusting the N first statements respectively based on the adjustment method for each of the above first statements and according to the above adjustment priorities of each of the above first statements to obtain the N target statements.

[0009] In an exemplary embodiment, after adjusting N first statements based on the adjustment method for each of the above first statements to obtain N target statements, the method further includes: processing the target task based on the N target statements to generate a target log; updating the adjustment information table based on each of the above target statements in the above target log and the target execution data of each of the above target statements.

[0010] According to an embodiment of the present application, there is also provided an adjustment device for database statements, including: a first acquisition module, configured to acquire a first log, where the first log is a log after performing a preprocessing operation on an initial log, and the initial log is a log generated when a database system processes a target task in parallel through multiple processing nodes; a first search module, configured to determine the adjustment types to be adjusted for N first statements to be adjusted according to the first log, and search for adjustment methods matching each of the first statements from an adjustment information table according to the adjustment types of the N first statements, where the N first statements are data processing statements used when the multiple processing nodes process the target task in parallel, N is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements in a training log whose execution time is greater than a preset threshold; a first adjustment module, configured to adjust the N first statements based on the adjustment method of each of the first statements to obtain N target statements.

[0011] In an exemplary embodiment, the device further includes: a first collection module, configured to, before acquiring the first log, collect the logs generated when the multiple processing nodes process the target task in parallel through multiple collection nodes to obtain the initial log, and store the initial log in a cloud storage space; a first extraction module, configured to extract initial log data from the initial log, where the initial log data includes M initial statements and initial execution data corresponding to each of the initial statements, and N of the M initial statements include the N first statements to be adjusted, and M is greater than or equal to N; a first execution module, configured to perform a preprocessing operation on the initial execution data corresponding to each of the initial statements respectively to obtain the execution data corresponding to each of the initial statements, where the preprocessing operation is used to process the abnormal information in the initial execution data and convert the unstructured initial execution data into structured data; a first determination module, configured to determine the M initial statements and the execution data corresponding to each of the initial statements as the first log.

[0012] In an exemplary embodiment, the above-mentioned device further includes: a first parsing module, configured to determine the types to be adjusted of N first statements to be adjusted according to the above-mentioned first log, and parse the statement structure of the above-mentioned training statement and the table data and function data in the training execution data of the above-mentioned training statement to obtain the transaction item set of each above-mentioned training statement before looking up the adjustment method matching each above-mentioned first statement from the adjustment information table according to the types to be adjusted of the N first statements, wherein the above-mentioned transaction item set further includes a plurality of item sets of the above-mentioned training statement, and the plurality of above-mentioned item sets are all used to represent the execution context information of the above-mentioned training statement; a second determination module, configured to determine K target item sets based on the occurrence frequency of each above-mentioned item set in the N above-mentioned transaction item sets, wherein the above-mentioned target item set is an item set with an occurrence frequency greater than a preset frequency in the N above-mentioned transaction item sets, and the above-mentioned K is a natural number less than or equal to the above-mentioned N; a first generation module, configured to generate the above-mentioned adjustment information table based on the K above-mentioned target item sets.

[0013] In an exemplary embodiment, the above-mentioned first lookup module includes: a first determination sub-module, configured to determine N first statements to be adjusted from M above-mentioned initial statements according to the execution time and resource usage information in the above-mentioned execution data corresponding to each above-mentioned initial statement in the above-mentioned first log, wherein the first execution time of the above-mentioned first statement is greater than a preset time threshold and / or the first resource usage information of the above-mentioned first statement is greater than a preset resource threshold; a first parsing sub-module, configured to parse the statement structure of the above-mentioned first statement and the table data and function data in the first execution data of the above-mentioned first statement to determine the types to be adjusted of the N above-mentioned first statements, wherein the above-mentioned execution data includes the above-mentioned first execution data, and the above-mentioned table data is used to represent the database tables involved in executing the above-mentioned first statement; a first lookup sub-module, configured to look up the above-mentioned adjustment method of each above-mentioned first statement from the above-mentioned adjustment information table according to the above-mentioned type to be adjusted.

[0014] In an exemplary embodiment, the above-mentioned first adjustment module includes: a second determination sub-module, configured to determine the adjustment priorities of the N above-mentioned first statements according to the first execution time of the above-mentioned first statement and / or the first resource usage information of the above-mentioned first statement; a first adjustment sub-module, configured to adjust the N above-mentioned first statements respectively based on the adjustment method of each above-mentioned first statement and according to the above-mentioned adjustment priorities of each above-mentioned first statement to obtain N above-mentioned target statements.

[0015] In an exemplary embodiment, the above device further includes: a second generation module, configured to adjust the N first statements based on the adjustment method of each of the first statements, and after obtaining N target statements, process the target task based on the N target statements to generate a target log; a first update module, configured to update the adjustment information table based on each of the target statements in the target log and the target execution data of each of the target statements.

[0016] According to another embodiment of the present application, there is also provided a computer program product, including a computer program, where the computer program is configured to be executed by a processor to perform the steps in any of the above method embodiments.

[0017] According to another embodiment of the present application, there is also provided a computer-readable storage medium, in which a computer program is stored, where the computer program is configured to be executed by a processor to perform the steps in any of the above method embodiments.

[0018] According to another embodiment of the present application, there is also provided an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor is configured to execute the computer program to perform the steps in any of the above method embodiments.

[0019] Through the present application, based on the first log, it is possible to automatically identify which database statements have problems in terms of standardization, execution efficiency, or resource consumption, and classify them into different types to be adjusted, and it is possible to quickly determine different adjustment methods according to the adjustment information table for different types to be adjusted. Moreover, the adjustment information table is constructed based on the database statements whose execution time in the training log exceeds a preset threshold, which means that the adjustment information table has learned a large number of actual performance bottleneck cases and their solutions. This data-driven dynamic optimization strategy greatly reduces the analysis and decision-making time and improves the adjustment efficiency. Therefore, the problem of inefficient task execution in the related art is solved, and the effect of efficient task execution is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 is a hardware structure block diagram of a mobile terminal for a method of adjusting database statements according to an embodiment of the present application;

[0021] Figure 2 is a flowchart of a method of adjusting database statements according to an embodiment of the present application;

[0022] Figure 3 is a flowchart of a method of adjusting database statements in a specific embodiment of the present application;

[0023] Figure 4It is a structural block diagram of an adjustment device for database statements according to an embodiment of the present application. Detailed implementation manners

[0024] In the following, embodiments of the present application will be described in detail with reference to the accompanying drawings and in combination with the embodiments.

[0025] It should be noted that the terms "first", "second", etc. in the specification, claims and above-mentioned drawings of the present application are used to distinguish similar objects, and do not necessarily need to be used to describe a specific order or sequence.

[0026] The method embodiments provided in the embodiments of the present application can be executed on a mobile terminal, a computer terminal or a similar computing device. Taking running on a mobile terminal as an example, Figure 1 It is a hardware structural block diagram of a mobile terminal for an adjustment method of database statements according to an embodiment of the present application. As Figure 1 shown, the mobile terminal may include one or more ( Figure 1 only one is shown in Figure 1 the 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. Among them, the above-mentioned mobile terminal may further include a transmission device 106 for communication functions and an input / output device 108. Those of ordinary skill in the art can understand that Figure 1 the structure shown in Figure 1 is only schematic and does not limit the structure of the above-mentioned mobile terminal. For example, the mobile terminal may further include more or fewer components than

[0027] shown in

[0028] Figure 1 shown, or have a different configuration from

[0027] the one shown in

[0028] The memory 104 can be used to store computer programs. For example, software programs and modules of application software, such as the computer program corresponding to an adjustment method of database statements in the embodiments 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, implements the above-mentioned 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 memories, or other non-volatile solid-state memories. In some instances, the memory 104 may further include a memory remotely provided with respect to the processor 102, and these remote memories may be connected to the mobile terminal through a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an enterprise intranet, a local area network, a mobile communication network, and combinations thereof.

[0028] The transmission device 106 is used to receive or send data via a network. Specific examples of the above-mentioned network may include a wireless network provided by a communication provider of a mobile terminal. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, abbreviated 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.

[0029] In this embodiment, a method for adjusting database statements is provided. Figure 2 It is a flowchart of a method for adjusting database statements according to an embodiment of the present application, as Figure 2 shown, the process includes the following steps:

[0030] Step S202, obtain a first log, where the first log is a log after performing a preprocessing operation on an initial log, and the initial log is a log generated when a database system processes a target task in parallel through multiple processing nodes;

[0031] Optionally, the first log and the initial log include but are not limited to running logs, error logs, and performance logs.

[0032] Optionally, the initial log is a raw log file directly generated by each processing node during the operation of the database system. It contains detailed information about the system operation, such as SQL statements, execution details of SQL statements, error information, performance data, etc.

[0033] Optionally, the target task includes but is not limited to data query, data update, and system status monitoring.

[0034] Optionally, the first log can be stored in cloud storage space or local storage space, such as an MPP database.

[0035] Optionally, the database system includes but is not limited to an MPP database system, such as Greenplum, SQL Server, Oracle.

[0036] Step S204, determine the types of adjustments to be made to N first statements to be adjusted according to the first log, and look up the adjustment methods matching each of the first statements from the adjustment information table according to the types of adjustments to be made to the N first statements, where the N first statements are data processing statements used when multiple processing nodes process the target task in parallel, N is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements in a training log whose execution time is greater than a preset threshold;

[0037] Optionally, the training log includes but is not limited to running logs, error logs, and performance logs.

[0038] Optionally, the first statement may be an SQL statement.

[0039] Step S206: Based on the adjustment method of each of the above first statements, adjust the N above first statements to obtain N target statements.

[0040] In this embodiment, the execution subject of the above steps may be a terminal, a server, a specific processor set in the terminal or the server, or a processor or processing device set relatively independently of the terminal or the server, but is not limited thereto.

[0041] Through the above steps, based on the first log, it is possible to automatically identify which database statements have problems in terms of standardization, execution efficiency, or resource consumption, and classify them into different types to be adjusted. It is possible to quickly determine different adjustment methods according to the adjustment information table for different types to be adjusted. Moreover, the adjustment information table is constructed based on the database statements in the training log whose execution time exceeds the preset threshold, which means that the adjustment information table has learned a large number of actual performance bottleneck cases and their solutions. This data-driven dynamic optimization strategy greatly reduces the analysis and decision-making time and improves the adjustment efficiency. Therefore, the problem of inefficient task execution in the related art is solved, and the effect of efficient task execution is achieved.

[0042] In an exemplary embodiment, before obtaining the first log, the above method further includes: parallelly collecting logs generated when multiple above processing nodes process the above target task through multiple collection nodes to obtain the above initial log, and storing the above initial log in the cloud storage space; extracting initial log data from the above initial log, where the above initial log data includes M initial statements and the initial execution data corresponding to each of the above initial statements, and N of the above first statements to be adjusted are included in the M above initial statements, and M is greater than or equal to N; respectively performing a preprocessing operation on the initial execution data corresponding to each of the above initial statements to obtain the execution data corresponding to each of the above initial statements, where the above preprocessing operation is used to process the abnormal information in the above initial execution data and convert the unstructured above initial execution data into structured data; determining the M above initial statements and the execution data corresponding to each of the above initial statements as the above first log.

[0043] Optionally, multiple collection nodes are dedicated components or services for parallelly collecting the runtime logs of the database system. Each collection node is responsible for obtaining log information from one or more processing nodes within the database system. Through this parallel approach, the efficiency and speed of log collection can be significantly improved, ensuring the real-time nature and integrity of a large amount of log data. During the collection process, a checksum mechanism is adopted to verify the integrity of the transmitted logs and initiate resume from breakpoint in case of interruption to avoid data loss.

[0044] Optionally, the initial statement is an SQL statement for implementing the target task.

[0045] Optionally, the initial execution data is used to indicate the execution information of the initial statement. For example, the processor usage rate, memory usage, number of input / output operations, etc.

[0046] Optionally, the exception information includes but is not limited to non-critical data and error data in the initial execution data. Processing the exception information includes but is not limited to removing non-critical data and modifying error data.

[0047] In this embodiment, by deploying multiple collection nodes for parallel collection, the purpose of significantly improving the log collection speed and efficiency is achieved. At the same time, by performing preprocessing operations on the initial execution data, and performing exception information processing and formatting processing on the initial execution data of each initial statement, useless or incorrect data can be timely discovered and eliminated, avoiding wasting resources in subsequent analysis to process invalid information, facilitating subsequent SQL specification detection and exception rule identification, and achieving the purpose of improving the analysis accuracy and efficiency.

[0048] In an exemplary embodiment, before determining the adjustment types of the N first statements to be adjusted according to the above first log and searching for the adjustment methods matching each of the above first statements from the adjustment information table according to the adjustment types of the N first statements, the method further includes: parsing the statement structure of the above training statement and the table data and function data in the training execution data of the above training statement to obtain the transaction item set of each of the above training statements, where the transaction item set further includes multiple item sets of the above training statement, and the multiple item sets are all used to represent the execution context information of the above training statement; determining K target item sets based on the occurrence frequency of each of the above item sets in the N transaction item sets, where the target item set is an item set with an occurrence frequency greater than a preset frequency in the N transaction item sets, and the K is a natural number less than or equal to the N; generating the above adjustment information table based on the K target item sets.

[0049] Optionally, the training statements can be SQL statements that are selectively extracted from historical data based on their execution history and marked as representative based on their execution results (such as execution time, resource consumption, etc.). These statements typically cover various query types, data scales, and execution environments that the MPP database may encounter during actual operation, and serve as the basis for constructing optimization rules and adjustment strategies.

[0050] Optionally, the training execution data can be the detailed execution information recorded by the database system when the training statements are executed in the MPP database, including but not limited to execution time, processor usage, memory consumption, number of input / output operations, error status, etc.

[0051] Optionally, the statement structure of the training statements is used to indicate the syntax composition and logical framework of the training statements. For example, the types of training statements (such as SELECT, INSERT, UPDATE, DELETE, etc.), various operators (such as WHERE, JOIN, GROUP BY, ORDER BY, etc.). The table data of the training statements is used to indicate the metadata and content data of the database tables involved during the execution of the training statements. For example, the metadata includes the name of the table, the definition of fields (columns), data types, index information, and the size of the table, etc. The content data refers to the specific data in the table, including the number of stored records, data distribution, data values, etc. The functional data of the training statements indicates the specific data or information related to the implementation of the statement function during the execution of the training statements. For example, the execution result of the SQL statement, the number of rows returned, the number of records affected, execution time, resource consumption (such as CPU, memory, I / O operations), etc.

[0052] Optionally, the transaction item set is used to represent the execution context information of the training statements, which contains a set of items related to a specific training statement, such as table names, field names, SQL operation types (such as SELECT, JOIN, WHERE conditions, etc.), and execution status (such as execution time, resource consumption, etc.). For example, the transaction item set T i = {SELECT, TableA, ColumnX>10, JOIN} represents an SQL query that retrieves data from TableA using SELECT, applies the filter condition ColumnX>10, and involves a JOIN operation between tables.

[0053] Optionally, the frequency of occurrence is used to indicate the ratio of the number of transaction item sets containing this item set among N above-mentioned transaction item sets to the total number of transaction item sets.

[0054] Optionally, based on the above-mentioned target item sets, generate the above-mentioned adjustment information table: based on the frequently occurring target item sets, generate optimization operations. For example, if {SELECT}, {TableA}, {JOIN} are target item sets, it may be possible to generate a suggestion to optimize the JOIN operation of TableA, such as reducing resource consumption and query time by improving the data distribution strategy, adding appropriate indexes, adjusting the JOIN type, etc. Specifically, optimization operations can be generated through methods such as expert rule refinement, machine learning model training, data mining, and statistical analysis.

[0055] Optionally, for example, in an MPP database system, after running for a period of time, a large amount of training logs are accumulated, which contain thousands of SQL statements and their execution data. After preprocessing, parse the structure and execution data of these training statements to identify the following target item sets: Target item set 1: SELECT * FROM big_table1, which frequently appears in the context of full table scans. Target item set 2: table1 LEFT JOIN table2 ON table1.id = table2.id, which frequently appears in inefficient join queries. Target item set 3: WHERE column1 IN (SELECT column2 FROM another_table), which frequently appears in inefficient subqueries. The adjustment information table may include the following adjustment strategies: for SQL statements with full table scans, it is recommended to add filtering conditions or use partition pruning; for join queries with low performance, it is recommended to use INNER JOIN or optimize the join conditions; for inefficient subqueries, it is recommended to use LEFT JOIN combined with GROUP BY for replacement.

[0056] In this embodiment, by parsing the statement structure of the training statements and the table data and functional data in the execution data, the key features and execution context of each training statement can be identified, and this information is transformed into transaction item sets, achieving the purpose of facilitating subsequent pattern mining and frequency analysis. At the same time, based on these target item sets, an adjustment information table is generated, which contains optimization strategies and adjustment methods for various common problems, achieving the purpose of learning from historical data and generating intelligent optimization suggestions.

[0057] In an exemplary embodiment, the adjustment types of N first statements to be adjusted are determined according to the above-mentioned first log, and the adjustment methods matching each of the above-mentioned first statements are found from the adjustment information table according to the adjustment types of the N first statements, including: determining N first statements to be adjusted from M initial statements according to the execution time and resource usage information in the execution data corresponding to each of the above-mentioned initial statements in the above-mentioned first log, where the first execution time of the above-mentioned first statement is greater than a preset time threshold and / or the first resource usage information of the above-mentioned first statement is greater than a preset resource threshold; parsing the statement structure of the above-mentioned first statement and the table data and function data in the first execution data of the above-mentioned first statement to determine the adjustment types of the N first statements, where the above-mentioned execution data includes the above-mentioned first execution data, and the above-mentioned table data is used to represent the database tables involved in executing the above-mentioned first statement; finding the above-mentioned adjustment methods of each of the above-mentioned first statements from the above-mentioned adjustment information table according to the above-mentioned adjustment types.

[0058] Optionally, the resource usage information includes, but is not limited to, execution time, processor usage rate, memory usage, number of disk input / output operations, and network input / output volume.

[0059] Optionally, the statement structure of the first statement is used to indicate the syntax composition and logical framework of the first statement. For example, the type of the first statement (such as SELECT, INSERT, UPDATE, DELETE, etc.), various operators (such as WHERE, JOIN, GROUPBY, ORDER BY, etc.). The table data of the first statement is used to indicate the metadata and content data of the database tables involved in the execution process of the first statement. For example, the metadata includes the name of the table, the definition of fields (columns), data types, index information, and the size of the table, etc. The content data refers to the specific data in the table, including the number of stored records, data distribution, data values, etc. The function data of the first statement indicates the specific data or information related to the implementation of the statement function during the execution process of the first statement. For example, the execution result of the SQL statement, the number of rows returned, the number of records affected, execution time, resource consumption (such as CPU, memory, I / O operations), etc.

[0060] By analyzing the execution time and resource usage information in the first log in this embodiment, it is possible to accurately identify those SQL statements with too long execution time or too much resource consumption, that is, the first statements to be adjusted. This makes the optimization work more targeted, achieving the purpose of avoiding blindly optimizing all SQL statements and saving resources and time in the optimization process.

[0061] In an exemplary embodiment, based on the adjustment method for each of the above first statements, N of the above first statements are adjusted to obtain N target statements, including: determining the adjustment priorities of the N first statements according to the first execution time of the first statements and / or the first resource usage information of the first statements; and adjusting the N first statements respectively based on the adjustment method for each of the first statements and in accordance with the adjustment priorities of each of the first statements to obtain the N target statements.

[0062] Optionally, the adjustment priorities of the first statements are determined according to preset weights. For example, the execution time of the first first statement is 120 seconds, the CPU usage rate is 90%, and the memory usage is 900 MB. According to the preset weight algorithm, this statement obtains the highest priority score and is marked as priority 1. The priorities are determined based on the criticality of the first statements to the business process and the possible business impacts caused by their execution failures or inefficiencies. For example, an SQL statement for real-time transaction processing may have a higher optimization priority than a statement for background data analysis because the latency or errors in real-time transactions have a greater impact on the business. This method requires close cooperation with the business department to understand the business logic and importance of each SQL statement. The adjustment priorities of the first statements are determined based on cost-benefit. For example, although an SQL statement does not have a particularly long execution time, if it consumes a large amount of disk I / O and causes the disk to become a system bottleneck, then it should be optimized first.

[0063] In this embodiment, by determining the adjustment priorities based on the execution time and resource usage information of the first statements, it is ensured that the SQL statements that have the greatest impact on system performance and consume the most resources can be processed first, achieving the purpose of avoiding indiscriminate optimization of all SQL statements, thereby improving the optimization efficiency and pertinence.

[0064] In an exemplary embodiment, after adjusting N of the above first statements based on the adjustment method for each of the above first statements to obtain N target statements, the method further includes: processing the target task based on the N target statements to generate a target log; and updating the adjustment information table based on each of the target statements in the target log and the target execution data of each of the target statements.

[0065] The present invention will be described below with reference to specific embodiments:

[0066] This embodiment is described by taking an MPP operation and maintenance optimization system based on SQL analysis as an example. The system mainly includes: a data collection unit, a data processing unit, an SQL specification detection unit, an automatic feedback unit, and a visualization report unit, as Figure 3 shown Figure 3It is a flowchart of a method for adjusting database statements in a specific embodiment of the present application, including the following steps:

[0067] S302, the data collection unit obtains operation logs, error logs, performance logs, etc. (corresponding to the above initial logs) from the MPP cluster (including multiple database systems). The logs mainly contain information related to the execution of SQL statements, such as SQL statement text, execution time, resource consumption, and running status. Among them, the data collection unit uses a distributed collection architecture, parallel collection of multiple nodes, and adopts a checksum mechanism (MD5) during the collection process to verify the integrity of the transmitted logs, and starts breakpoint resumption in case of interruption to avoid loss of log data.

[0068] S304, the data processing unit parses and preprocesses the collected logs, converts the unstructured logs into structured logs, and extracts key information from the logs, including SQL statements, execution time, error status, etc. to obtain the first log, and stores it in the database.

[0069] S306, analyze whether the SQL statements in the first log conform to the MPP development specifications: first, identify those SQL statements with too long execution time or too large resource consumption, that is, the first statements to be adjusted, and then determine the adjustment type of the first statements to be adjusted. According to the adjustment type to be adjusted, look up the above adjustment methods for each first statement in the adjustment information table to obtain the adjusted first statements (corresponding to the above first statements), as shown in Table 1.

[0070] Table 1:

[0071]

[0072]

[0073]

[0074]

[0075] S308, the abnormal rule recognition unit performs high-frequency pattern mining on the SQL statements with high consumption time that are not adjusted through the adjustment information table: first, extract SQL features from it, such as SELECT, JOIN, WHERE conditions, table names, field names. At the same time, record information such as the nodes where SQL is executed, partition information, and resource consumption. Represent each SQL statement as a transaction item set T i , for example: T i = {SELECT, TableA, ColumnX>10, JOIN}. Then, calculate the support degree of each item set in the transaction item set (such as SQL operation type, table name, field condition combination, etc.):

[0076]

[0077] According to the set minimum support threshold, frequent item sets are screened out. Then, based on the frequent item set L k-1 Candidate k-item sets C k : C k = {X ∪ Y | X, Y ∈ L k-1 , |X ∩ Y| = k - 2}. Optimization suggestions are determined based on the frequent item sets.

[0078] S310, the automatic feedback unit processes the target task based on the optimized first statement and generates a target log. The visualization report unit generates a visualization report based on each target statement and the target execution data of each target statement in the target log, and updates the optimization adjustment information table based on the visualization report.

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

[0080] In this embodiment, a database statement adjustment device is further provided. This device is used to implement the above embodiments and preferred implementation manners, and those that have been described will not be repeated. As used below, the term "module" can be a combination of software and / or hardware that can achieve a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware is also possible and contemplated.

[0081] Figure 4 is a structural block diagram of a database statement adjustment device according to an embodiment of the present application. As Figure 4 shown, this device includes:

[0082] The first acquisition module 402 is used to acquire a first log, where the above first log is a log after performing a preprocessing operation on the initial log, and the above initial log is a log generated when the database system processes the target task in parallel through multiple processing nodes;

[0083] The first search module 404 is configured to determine the types to be adjusted of N first statements to be adjusted according to the above-mentioned first log, and search for adjustment methods matching each of the above-mentioned first statements from the adjustment information table according to the types to be adjusted of the N first statements. The N first statements are data processing statements used when multiple above-mentioned processing nodes process the above-mentioned target task in parallel. The above-mentioned N is a natural number greater than or equal to 1. The above-mentioned adjustment information table is an information table obtained based on training statements in the training log whose execution time is greater than a preset threshold.

[0084] The first adjustment module 406 is configured to adjust the N first statements based on the adjustment methods of each of the above-mentioned first statements to obtain N target statements.

[0085] In an exemplary embodiment, the above-mentioned device further includes: a first acquisition module, configured to, before acquiring the first log, acquire logs generated when multiple above-mentioned processing nodes process the above-mentioned target task in parallel through multiple acquisition nodes to obtain the above-mentioned initial log, and store the above-mentioned initial log in the cloud storage space; a first extraction module, configured to extract initial log data from the above-mentioned initial log, where the above-mentioned initial log data includes M initial statements and initial execution data corresponding to each of the above-mentioned initial statements. The M initial statements include the N first statements to be adjusted, and the above-mentioned M is greater than or equal to the above-mentioned N; a first execution module, configured to perform preprocessing operations on the initial execution data corresponding to each of the above-mentioned initial statements to obtain execution data corresponding to each of the above-mentioned initial statements, where the above-mentioned preprocessing operation is used to process abnormal information in the above-mentioned initial execution data and convert the unstructured above-mentioned initial execution data into structured data; a first determination module, configured to determine the M initial statements and the execution data corresponding to each of the above-mentioned initial statements as the above-mentioned first log.

[0086] In an exemplary embodiment, the above-mentioned device further includes: a first parsing module, configured to parse the statement structure of the above-mentioned training statement and the table data and function data in the training execution data of the above-mentioned training statement to obtain a transaction item set for each of the above-mentioned training statements before determining the types to be adjusted of N first statements to be adjusted according to the above-mentioned first log and searching for adjustment methods matching each of the above-mentioned first statements from the adjustment information table according to the types to be adjusted of the N first statements. The above-mentioned transaction item set further includes multiple item sets of the above-mentioned training statement, and the multiple above-mentioned item sets are all used to represent the execution context information of the above-mentioned training statement; a second determination module, configured to determine K target item sets based on the occurrence frequency of each of the above-mentioned item sets in the N above-mentioned transaction item sets, where the above-mentioned target item set is an item set in the N above-mentioned transaction item sets whose occurrence frequency is greater than a preset frequency, and the above-mentioned K is a natural number less than or equal to the above-mentioned N; a first generation module, configured to generate the above-mentioned adjustment information table based on the K above-mentioned target item sets.

[0087] In an exemplary embodiment, the above-mentioned first lookup module includes: a first determination sub-module, configured to determine N first statements to be adjusted from M initial statements according to the execution time and resource usage information in the execution data corresponding to each initial statement in the first log, where the first execution time of the first statement is greater than a preset time threshold and / or the first resource usage information of the first statement is greater than a preset resource threshold; a first parsing sub-module, configured to parse the statement structure of the first statement and the table data and function data in the first execution data of the first statement to determine the types to be adjusted of the N first statements, where the execution data includes the first execution data, and the table data is used to represent the database tables involved in executing the first statement; a first lookup sub-module, configured to look up the adjustment methods of each first statement from the adjustment information table according to the types to be adjusted.

[0088] In an exemplary embodiment, the above-mentioned first adjustment module includes: a second determination sub-module, configured to determine the adjustment priorities of the N first statements according to the first execution time of the first statement and / or the first resource usage information of the first statement; a first adjustment sub-module, configured to adjust the N first statements respectively according to the adjustment methods of each first statement and in accordance with the adjustment priorities of each first statement to obtain N target statements.

[0089] In an exemplary embodiment, the above-mentioned device further includes: a second generation module, configured to, after adjusting the N first statements based on the adjustment methods of each first statement to obtain N target statements, process the target task based on the N target statements to generate a target log; a first update module, configured to update the adjustment information table based on each target statement in the target log and the target execution data of each target statement. An embodiment of the present application provides a computer program product, including a computer program, where when the computer program is executed by a processor, the steps in any one of the above method embodiments are implemented.

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

[0091] In an exemplary embodiment, the above-mentioned computer-readable 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 disc, and other media that can store computer programs.

[0092] An embodiment of the present application further provides an electronic device, including a memory and a processor. 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.

[0093] In an exemplary embodiment, the above electronic device may further include a transmission device and an input / output device. The transmission device is connected to the processor, and the input / output device is connected to the processor.

[0094] Specific examples in this embodiment may refer to the examples described in the above embodiments and exemplary embodiments, and will not be repeated here.

[0095] Obviously, those skilled in the art should understand that the above modules or steps of the present application can be implemented by a general-purpose computing device. They can be concentrated on a single computing device or distributed on a network composed of multiple computing devices. They can be implemented by program codes executable by the computing device, so that they can be stored in a storage device and executed by the computing device. And in some cases, the steps shown or described can be executed in a different order from here, or they can be separately made into individual integrated circuit modules, or multiple modules or steps among them can be made into a single integrated circuit module to implement. In this way, the present application is not limited to any specific combination of hardware and software.

[0096] The above are only the preferred embodiments of the present application and are not used to limit the present application. For those skilled in the art, the present application can have various changes and modifications. Any modification, equivalent replacement, improvement, etc. made within the principle of the present application shall be included in the protection scope of the present application.

Claims

1. A method for adjusting a database statement, characterized in that: include: Obtaining a first log, wherein the first log is a log after a preprocessing operation is performed on an initial log, and the initial log is a log generated when the database system processes a target task in parallel through multiple processing nodes; Determine, according to the first log, the types of N first statements to be adjusted, and search, from the adjustment information table, for an adjustment method matching each first statement according to the types of N first statements to be adjusted, wherein the N first statements are data processing statements used when a plurality of the processing nodes process the target task in parallel, N is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements whose execution time in the training log is greater than a preset threshold; Based on the adjustment method of each of the first statements, N first statements are adjusted to obtain N target statements.

2. The method according to claim 1, characterized in that Before obtaining the first log, the method further includes: Collecting logs generated when multiple processing nodes process the target task in parallel through multiple collection nodes in parallel, obtaining the initial logs, and storing the initial logs in a cloud storage space; Extracting initial log data from the initial log, wherein the initial log data includes M initial statements and initial execution data corresponding to each of the initial statements, the M initial statements include N first statements to be adjusted, and the M is greater than or equal to the N; Performing a preprocessing operation on the initial execution data corresponding to each of the initial statements to obtain the execution data corresponding to each of the initial statements, wherein the preprocessing operation is used to process abnormal information in the initial execution data and convert the unstructured initial execution data into structured data; The M initial statements and the execution data corresponding to each of the initial statements are determined as the first log.

3. The method according to claim 1, characterized in that Before determining the types of the N first statements to be adjusted according to the first log, and searching the adjustment method matching each of the first statements from the adjustment information table according to the types of the N first statements to be adjusted, the method further includes: Parsing the sentence structure of the training statement and the table data and function data in the training execution data of the training statement to obtain a transaction item set of each training statement, wherein the transaction item set also includes multiple item sets of the training statement, and the multiple item sets are all used to represent the execution context information of the training statement; Based on the occurrence frequency of each item set in the N transaction item sets, K target item sets are determined, wherein the target item set is an item set in the N transaction item sets whose occurrence frequency is greater than a preset frequency, and K is a natural number less than or equal to N; Based on the K target item sets, the adjustment information table is generated.

4. The method according to claim 2, characterized in that: Determining, according to the first log, types of the N first statements to be adjusted, and searching, from an adjustment information table, an adjustment method matching each of the first statements according to the types of the N first statements to be adjusted, including: Determine, according to the execution time and resource usage information in the execution data corresponding to each of the initial statements in the first log, N first statements to be adjusted from the M initial statements, wherein the first execution time of the first statement is greater than a preset time threshold and / or the first resource usage information of the first statement is greater than a preset resource threshold; Parsing the statement structure of the first statement and the table data and the function data in the first execution data of the first statement to determine the types to be adjusted of the N first statements, wherein the execution data includes the first execution data, and the table data is used to represent the database table involved in executing the first statement; According to the type to be adjusted, the adjustment method of each first statement is searched from the adjustment information table.

5. The method according to claim 2, characterized in that: Adjusting N first statements based on the adjustment mode of each first statement to obtain N target statements includes: determining adjustment priorities of the N first statements according to the first execution time of the first statement and / or the first resource usage information of the first statement; Based on the adjustment method of each of the first statements and according to the adjustment priority of each of the first statements, the N first statements are adjusted respectively to obtain N target statements.

6. The method according to claim 1, characterized in that After adjusting N first statements based on the adjustment mode of each first statement to obtain N target statements, the method further includes: Process the target task based on the N target statements to generate a target log; The adjustment information table is updated based on each of the target statements in the target log and the target execution data of each of the target statements.

7. A database statement adjustment device, characterized in that: include: A first acquisition module, configured to acquire a first log, wherein the first log is a log after a preprocessing operation is performed on an initial log, and the initial log is a log generated when the database system processes a target task in parallel through multiple processing nodes; a first search module, configured to determine, according to the first log, types of the N first statements to be adjusted, and to search an adjustment method matching each of the first statements from an adjustment information table according to the types of the N first statements to be adjusted, wherein the N first statements are data processing statements used when a plurality of the processing nodes process the target task in parallel, N is a natural number greater than or equal to 1, and the adjustment information table is an information table obtained based on training statements in the training log whose execution time is greater than a preset threshold; The first adjustment module is used to adjust N first statements based on the adjustment method of each first statement to obtain N target statements.

8. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the steps of the method described in any one of claims 1 to 6 are implemented.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, wherein the computer program implements the steps of the method described in any one of claims 1 to 6 when executed by a processor.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the steps of the method described in any one of claims 1 to 6 are implemented.

Images