A method, device and storage medium for grouping SQL tasks

Abstract

The application relates to the technical field of big data, and discloses a method and device for grouping SQL tasks and a storage medium, the method being as follows: obtaining a plurality of SQL tasks based on database operation log data, wherein each SQL task carries resource consumption information and time window information; grouping the SQL tasks based on the resource consumption information and the time window information to obtain a plurality of groups; and merging the SQL tasks included in the same group to obtain a plurality of merged SQL task strips, wherein the number of the task strips is the same as the number of the groups; establishing a processing matter for each merged SQL task strip; and submitting each processing matter to a corresponding server cluster for processing. The above scheme intelligently groups and merges a large number of SQL tasks, optimizes resource consumption balance and time window consistency, reduces the number of processing matters, and improves resource utilization efficiency.

Description

Technical Field

[0001] This application relates to the field of big data technology, and provides a method, apparatus and storage medium for grouping SQL tasks. Background Technology

[0002] Currently, in the big data field, the number of Structured Query Language (SQL) tasks involved in business operations is growing rapidly, with some data processing operations potentially involving thousands of SQL tasks. In existing processes, each SQL task generates a separate Flink processing item, which is then submitted to the server cluster for processing. The generation of these numerous Flink processing items increases the difficulty of operation and maintenance. Furthermore, the large number of Flink processing items consumes more computing resources (e.g., Central Processing Unit (CPU), memory, and input / output (I / O) ports), resulting in significant waste of computing resources.

[0003] Furthermore, the windowing mechanisms of SQL tasks are complex and varied (e.g., sliding windows, scrolling windows, etc.), resulting in inefficient grouping of SQL tasks and difficulty in meeting diverse business needs. Moreover, each Flink transaction requires reading all SQL tasks, and this repeated reading and processing not only increases I / O overhead but also places additional burden on computational and storage resources, ultimately impacting overall system performance. Summary of the Invention

[0004] This application provides a method, apparatus, and storage medium for grouping SQL tasks, which intelligently groups and merges SQL tasks, reducing the number of processing items and improving resource utilization efficiency.

[0005] The specific technical solution provided in this application is as follows:

[0006] In a first aspect, embodiments of this application provide a method for grouping SQL tasks, including:

[0007] Multiple SQL tasks are obtained from the database's runtime log data. Each SQL task carries resource consumption information and time window information.

[0008] Based on resource consumption information and time window information, each SQL task is grouped into multiple groups, and the SQL tasks included in the same group are merged to obtain multiple merged SQL task bars. The number of task bars is the same as the number of groups.

[0009] Each merged SQL task bar is assigned a processing item, and each processing item is submitted to the corresponding server cluster for processing.

[0010] Optionally, multiple SQL tasks can be retrieved based on the database's runtime log data, including:

[0011] Establish a Flink CDC and use the Flink CDC to monitor the database's runtime log data in real time to see if there are any changes.

[0012] If changes are detected in the runtime log data, multiple SQL tasks are extracted from the changed runtime log data.

[0013] Multiple SQL tasks are parsed separately, and the resource consumption information and time window information carried by the SQL tasks are extracted from the parsing results.

[0014] Optionally, each SQL task can be grouped into multiple groups based on resource consumption information and time window information, including:

[0015] For each SQL task, multiple initial solutions are randomly generated. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the SQL tasks in the corresponding position belong to the same group. Different numbers indicate that the SQL tasks in the corresponding positions belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number.

[0016] Multiple initial solutions randomly generated based on each SQL task are input into the model corresponding to the NSGA-II algorithm, so that the model uses an iterative method to filter the multiple initial solutions;

[0017] In each iteration, it is determined whether the initial solution selected in this round meets the convergence condition. If the convergence condition is met, the SQL tasks are grouped into multiple groups based on the selected initial solutions. The convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of selected initial solutions is one. The iteration process is as follows:

[0018] Based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined respectively;

[0019] Based on the ranking results and crowding values, parent solutions are determined from each initial solution, and multiple child solutions are obtained by crossover and mutation of the parent solutions.

[0020] The parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from the expanded initial solutions based on resource consumption information and time window information.

[0021] Optionally, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined based on resource consumption information and time window information, including:

[0022] Based on any initial solution before this iteration, each SQL task is divided into k groups represented by the initial solution, where k is a positive integer;

[0023] Calculate the variance of the resource consumption information of each SQL task in each of the k groups and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0024] Calculate the absolute value of the difference between the time window information of each SQL task in each group and the time window information of the remaining SQL tasks in each group, thereby obtaining multiple window values;

[0025] The ranking result is determined based on the variance and window value of each of the k groups, and the congestion value of multiple SQL tasks belonging to the same ranking result is determined separately.

[0026] Optionally, parent solutions are determined from each initial solution based on the ranking results and crowding values, including:

[0027] The comprehensive value of each group in k groups is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0028] Select the h group with the highest comprehensive value from the k groups, and determine the initial solution corresponding to the h group as the parent solution, where h is a positive integer less than k.

[0029] Optionally, crossover and mutation are performed on the parent solution to obtain multiple child solutions, including:

[0030] By simulating the binary crossover algorithm, the values ​​at any position in the parent solution are crossed to obtain multiple crossover parent solutions;

[0031] By randomly changing the values ​​at any position in multiple cross-parent solutions, multiple child solutions are obtained.

[0032] Optionally, the parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from these expanded initial solutions based on resource consumption and time window information, including:

[0033] By taking the union of the parent and child solutions, multiple extended initial solutions are obtained;

[0034] Each SQL task is divided into q groups represented by the initial solutions based on multiple extended initial solutions, where q is a positive integer;

[0035] Calculate the variance of the resource consumption information of each SQL task in each group of q and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0036] Calculate the absolute value of the difference between the time window information of each SQL task in each group of q and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0037] The ranking results are determined based on the variance and window value of each group in group q, and the congestion value of SQL tasks belonging to the same ranking result in group q is determined respectively.

[0038] The comprehensive value of each group in group q is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0039] Select the group with the highest comprehensive value from the group q, and determine the extended initial solution corresponding to the group f as multiple initial solutions, where f is a positive integer less than q.

[0040] Secondly, embodiments of this application also provide an apparatus for grouping SQL tasks, comprising:

[0041] The acquisition unit is used to acquire multiple SQL tasks based on the database's runtime log data. Each SQL task carries resource consumption information and time window information.

[0042] The grouping unit is used to group each SQL task into multiple groups based on resource consumption information and time window information, and to merge the SQL tasks included in the same group to obtain multiple merged SQL task bars. The number of task bars is the same as the number of groups.

[0043] The submission unit is used to create processing items for each merged SQL task bar and submit each processing item to the corresponding server cluster for processing.

[0044] Optionally, multiple SQL tasks are retrieved based on the database's runtime log data. The retrieval unit is used for:

[0045] Establish a Flink CDC and use the Flink CDC to monitor the database's runtime log data in real time to see if there are any changes.

[0046] If changes are detected in the runtime log data, multiple SQL tasks are extracted from the changed runtime log data.

[0047] Multiple SQL tasks are parsed separately, and the resource consumption information and time window information carried by the SQL tasks are extracted from the parsing results.

[0048] Optionally, each SQL task can be grouped into multiple groups based on resource consumption information and time window information. The grouping unit is used for:

[0049] For each SQL task, multiple initial solutions are randomly generated. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the SQL tasks in the corresponding position belong to the same group. Different numbers indicate that the SQL tasks in the corresponding positions belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number.

[0050] Multiple initial solutions randomly generated based on each SQL task are input into the model corresponding to the NSGA-II algorithm, so that the model uses an iterative method to filter the multiple initial solutions;

[0051] In each iteration, it is determined whether the initial solution selected in this round meets the convergence condition. If the convergence condition is met, the SQL tasks are grouped into multiple groups based on the selected initial solutions. The convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of selected initial solutions is one. The iteration process is as follows:

[0052] Based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined respectively;

[0053] Based on the ranking results and crowding values, parent solutions are determined from each initial solution, and multiple child solutions are obtained by crossover and mutation of the parent solutions.

[0054] The parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from the expanded initial solutions based on resource consumption information and time window information.

[0055] Optionally, based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined, and the grouping unit is used for:

[0056] Based on any initial solution before this iteration, each SQL task is divided into k groups represented by the initial solution, where k is a positive integer;

[0057] Calculate the variance of the resource consumption information of each SQL task in each of the k groups and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0058] Calculate the absolute value of the difference between the time window information of each SQL task in each group and the time window information of the remaining SQL tasks in each group, thereby obtaining multiple window values;

[0059] The ranking result is determined based on the variance and window value of each of the k groups, and the congestion value of multiple SQL tasks belonging to the same ranking result is determined separately.

[0060] Optionally, parent solutions are determined from each initial solution based on the ranking results and crowding values. The grouping units are used for:

[0061] The comprehensive value of each group in k groups is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0062] Select the h group with the highest comprehensive value from the k groups, and determine the initial solution corresponding to the h group as the parent solution, where h is a positive integer less than k.

[0063] Optionally, crossover and mutation are performed on the parent solution to obtain multiple child solutions. The grouping unit is used for:

[0064] By simulating the binary crossover algorithm, the values ​​at any position in the parent solution are crossed to obtain multiple crossover parent solutions;

[0065] By randomly changing the values ​​at any position in multiple cross-parent solutions, multiple child solutions are obtained.

[0066] Optionally, the parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from these expanded initial solutions based on resource consumption and time window information. The grouping unit is used for:

[0067] By taking the union of the parent and child solutions, multiple extended initial solutions are obtained;

[0068] Each SQL task is divided into q groups represented by the initial solutions based on multiple extended initial solutions, where q is a positive integer;

[0069] Calculate the variance of the resource consumption information of each SQL task in each group of q and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0070] Calculate the absolute value of the difference between the time window information of each SQL task in each group of q and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0071] The ranking results are determined based on the variance and window value of each group in group q, and the congestion value of SQL tasks belonging to the same ranking result in group q is determined respectively.

[0072] The comprehensive value of each group in group q is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0073] Select the group with the highest comprehensive value from the group q, and determine the extended initial solution corresponding to the group f as multiple initial solutions, where f is a positive integer less than q.

[0074] Thirdly, a server includes:

[0075] Memory, used to store executable instructions;

[0076] A processor for reading and executing executable instructions stored in memory to implement the method as described in any of the first aspects.

[0077] Fourthly, a computer-readable storage medium, when instructions in the storage medium are executed by a processor, enables the processor to perform the method described in any of the first aspects above.

[0078] The beneficial effects of this application are as follows:

[0079] In summary, the embodiments of this application provide a method, apparatus, and storage medium for grouping SQL tasks. The method includes: obtaining multiple SQL tasks based on database operation log data, wherein each SQL task carries resource consumption information and time window information; grouping each SQL task into multiple groups based on the resource consumption information and time window information; merging the SQL tasks included in the same group to obtain multiple merged SQL task bars, wherein the number of task bars is the same as the number of groups; establishing processing items for each merged SQL task bar; and submitting each processing item to the corresponding server cluster for processing. The above scheme intelligently groups and merges a large number of SQL tasks, optimizes resource consumption balance and time window consistency, reduces the number of processing items, improves resource utilization efficiency, and reduces the complexity of system operation and maintenance.

[0080] Other features and advantages of this application will be set forth in the description which follows, and will be apparent in part from the description, or may be learned by practicing the application. The objectives and other advantages of this application may be realized and obtained by means of the structures particularly pointed out in the written description, claims, and drawings. Attached Figure Description

[0081] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0082] Figure 1 This is a schematic diagram of the system architecture for grouping SQL tasks in an embodiment of this application;

[0083] Figure 2 This is a schematic diagram illustrating the process of grouping SQL tasks in an embodiment of this application;

[0084] Figure 3 This is a schematic diagram illustrating the process of obtaining multiple SQL tasks based on database runtime log data in an embodiment of this application.

[0085] Figure 4 This is a flowchart illustrating how each SQL task is grouped into multiple groups based on resource consumption information and time window information in an embodiment of this application.

[0086] Figure 5 This is a flowchart illustrating each iteration process in the embodiments of this application;

[0087] Figure 6 This is a flowchart illustrating the process of determining the ranking and congestion values ​​of multiple initial solutions before the current iteration based on resource consumption information and time window information, as described in this application embodiment.

[0088] Figure 7 This is a schematic diagram of the process for determining the parent solution from each initial solution based on the ranking result and the crowding value in an embodiment of this application.

[0089] Figure 8 This is a schematic diagram illustrating the process of obtaining multiple child solutions by crossover and mutation of the parent solution in an embodiment of this application;

[0090] Figure 9 This is a schematic diagram illustrating the process of selecting multiple initial solutions based on parent and child solutions in an embodiment of this application.

[0091] Figure 10 This is a schematic diagram of the logical architecture of a device for grouping SQL tasks according to an embodiment of this application;

[0092] Figure 11 This is a schematic diagram of the physical architecture of a server according to an embodiment of this application. Detailed Implementation

[0093] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of this application will be clearly and completely described below with reference to the accompanying drawings of the embodiments of this application. Obviously, the described embodiments are only some embodiments of the technical solutions of this application, and not all embodiments. Based on the embodiments recorded in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the technical solutions of this application.

[0094] The terms "first," "second," etc., used in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that embodiments of the invention described herein can be implemented in sequences other than those illustrated or described herein.

[0095] First, the following definitions will be provided:

[0096] (1) Non-dominated Sorting Genetic Algorithm II (NSGA-II): This is a classic multi-objective optimization algorithm used to solve optimization problems with two or more conflicting objectives. It is an improved version of the NSGA algorithm, with higher computational efficiency and the ability to solve multi-objective problems.

[0097] (2) Flink Change Data Capture (Flink cdc) is an open-source framework built on Apache Flink for capturing changed data in a database. It combines real-time data stream processing with database changes (such as insert, update, and delete operations) to efficiently capture and process these data changes.

[0098] (3) Structured Query Language (SQL) is a standard programming language used to manage and manipulate relational databases. It is used to perform various database operations, such as data querying, insertion, updating, and deletion. SQL is the primary tool in Relational Database Management Systems (RDBMS) for defining, manipulating, and controlling data.

[0099] The preferred embodiments of this application will now be described in detail with reference to the accompanying drawings.

[0100] See Figure 1 As shown in the embodiments of this application, the system includes multiple servers and at least one server cluster. Figure 1In this example, servers 1, 2, and n are used to represent servers that group SQL tasks. Assume that server 1 runs business 1 and groups the SQL tasks in business 1; server 2 runs business 2 and groups the SQL tasks in business 2; and server n runs business n and groups the SQL tasks in business n. Server cluster A is used to represent the server cluster that interacts with the above servers, that is, server cluster A executes the processing items established for the grouped and merged SQL tasks.

[0101] In this embodiment of the application, a method for grouping SQL tasks is implemented, which is mainly executed on the server side, and will be described in detail below.

[0102] See Figure 2 As shown in the embodiments of this application, a specific process for grouping SQL tasks is as follows:

[0103] Step 201: Obtain multiple SQL tasks based on the database's runtime log data. Each SQL task carries resource consumption information and time window information.

[0104] When the processing business on the server is running, the data in the database corresponding to the processing business will change in real time as the processing business runs. In order to comprehensively monitor the running status of the processing business, the running log data of the database will be obtained, and the SQL task will be further obtained from the running log data.

[0105] The above-mentioned task retrieves multiple SQL statements based on database runtime log data; please refer to [link / reference]. Figure 3 As shown, it specifically includes:

[0106] Step 2011: Establish a Flink CDC and use the Flink CDC to monitor the database's runtime log data in real time to see if there are any changes.

[0107] To obtain the runtime log data of the database used for processing business operations, a Flink CDC (Center for Analyzing and Analyzing Data) is first established for this business process. When the business process starts running, the Flink CDC first retrieves all the data stored in the database, i.e., the initial data in the runtime log data. As the business process continues, at least some of the data in the database will change. Based on this, the server will use the Flink CDC to monitor whether the database's runtime log data has changed in real time. For example, Flink CDC11 is deployed to monitor the data in MySQL binlog11.

[0108] It should be noted that before the processing business begins, multiple SQL tasks are pre-configured for the processing business. Correspondingly, resource consumption information and time window information are configured for each SQL task. That is, the resource consumption and time window required for each SQL task during processing are estimated in advance, and the corresponding resource consumption information and time window information are pre-configured for each SQL task to ensure the execution of each SQL task. The aforementioned resource consumption information specifically includes the CPU, memory, I / O, etc. required for the execution of the SQL task. The aforementioned time window information first indicates which type of sliding window, scrolling window, cumulative window, and session window the SQL task corresponds to. In addition, the aforementioned time window information also includes the corresponding window length, etc.

[0109] Step 2012: If changes are detected in the runtime log data, extract multiple SQL tasks from the changed runtime log data.

[0110] If the runtime log data changes, it indicates that a certain SQL task in the business logic has been completed. If the runtime log data remains unchanged, it means that a certain SQL task has not yet been executed. Based on this, during implementation, if Flink CDC detects changes in the runtime log data, it captures the changed runtime log data and then extracts multiple SQL tasks from the changed runtime log data.

[0111] Step 2013: Parse multiple SQL tasks separately, and extract the resource consumption information and time window information carried by the SQL tasks from the parsing results.

[0112] Considering that the subsequent grouping of SQL tasks will be based on resource consumption information and time window information, during the implementation process, after extracting multiple SQL tasks, the following operations are performed for each SQL task: the SQL task is parsed, the pre-configured resource consumption information and time window information in the SQL task are determined through parsing, and the resource consumption information and time window information carried by the SQL task are extracted from the parsing results.

[0113] Example 1: Assuming resource consumption information is based on a maximum CPU resource allocation of 10, and the time window information is all of the sliding window type, 5 SQL tasks are obtained through Flink CDC and identified as shown in Table 1 below:

[0114] Table 1

[0115] SQL001 3 60 SQL002 8 150 SQL003 10 300 SQL004 6 90 SQL005 7 180

[0116] Step 202: Group each SQL task based on resource consumption information and time window information to obtain multiple groups, and merge the SQL tasks included in the same group to obtain multiple merged SQL task bars, wherein the number of task bars is the same as the number of groups.

[0117] In this embodiment, to improve resource utilization efficiency in processing business, after extracting multiple SQL tasks, each SQL task is further grouped according to resource consumption information and time window information, thus obtaining multiple groups. Unlike the related technology that processes SQL tasks one by one, this embodiment merges multiple SQL tasks belonging to the same group into one SQL task bar, thus obtaining multiple task bars with the same number as the groups. The aforementioned task bar is the smallest processing unit in processing business.

[0118] The above grouping of SQL tasks based on resource consumption and time window information yields multiple groups, see below. Figure 4 As shown, it includes:

[0119] Step 2021: Randomly generate multiple initial solutions for each SQL task. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the SQL tasks in the corresponding position belong to the same group. Different numbers indicate that the SQL tasks in the corresponding positions belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number.

[0120] In this embodiment, it is first assumed that each SQL task is randomly divided into any number of groups, that is, multiple initial solutions are randomly generated for each SQL task. The aforementioned initial solution represents one case of grouping the aforementioned SQL tasks.

[0121] It should be noted that each initial solution includes multiple numbers, and each number in each position represents the i-th number, with each number representing the i-th SQL task.

[0122] Referring to Example 1 above, in the initial solution 1 [1,1,2,2,1], the first 1 represents the first SQL task SQL001, the second 1 represents the second SQL task SQL002, the third 2 represents the third SQL task SQL003, the fourth 2 represents the fourth SQL task SQL004, and the fifth 1 represents the fifth SQL task SQL005.

[0123] In addition, SQL tasks with the same numerical representation belong to the same group, while SQL tasks with different numerical representations belong to different groups.

[0124] Referring to Example 1 above, the initial solution 1 [1,1,2,2,1] means that the numbers corresponding to SQL tasks SQL001, SQL002, and SQL005 are all 1, which means that SQL tasks SQL001, SQL002, and SQL005 all belong to group 1. The initial solution 1 [1,1,2,2,1] means that the numbers corresponding to SQL tasks SQL003 and SQL004 are all 2, which means that SQL tasks SQL003 and SQL004 both belong to group 2.

[0125] It should also be noted that the number of initial solutions generated by each SQL task is random. Continuing with Example 1 above, when initial solution 2 is [2,2,1,1,2], it means that the numbers corresponding to SQL tasks SQL001, SQL002, and SQL005 are all the number 2 in initial solution 2, meaning that SQL tasks SQL001, SQL002, and SQL005 all belong to group 2. Initial solution 2 [2,2,1,1,2] means that the numbers corresponding to SQL tasks SQL003 and SQL004 are both the number 1 in initial solution 2, meaning that SQL tasks SQL003 and SQL004 both belong to group 1.

[0126] Similarly, continuing with Example 1 above, when the initial solution 3 is [1,2,1,2,1], it means that the numbers corresponding to the SQL tasks SQL001, SQL003, and SQL005 are all 1 in the initial solution 3, meaning that SQL tasks SQL001, SQL003, and SQL005 all belong to group 1. The initial solution 3 [2,2,1,1,2] means that the numbers corresponding to the SQL tasks SQL002 and SQL004 are all 2 in the initial solution 3, meaning that SQL tasks SQL002 and SQL004 both belong to group 2.

[0127] Similarly, continuing with Example 1 above, when the initial solution 4 is [2,1,2,1,2], it means that the numbers corresponding to the SQL tasks SQL001, SQL003, and SQL005 are all 2, which means that SQL tasks SQL001, SQL003, and SQL005 all belong to group 2. The initial solution 4 [2,2,1,1,2] means that the numbers corresponding to the SQL tasks SQL002 and SQL004 are all 1, which means that SQL tasks SQL002 and SQL004 both belong to group 1.

[0128] It should also be noted that the time window information of SQL tasks in the same group represents the same window type. That is, when the time window information of two SQL tasks represents different window types, the SQL tasks will be assigned to different groups.

[0129] Example 2: Assuming resource consumption information is based on a maximum CPU resource allocation of 10, and the time window information includes both sliding and scrolling windows, 5 SQL tasks are obtained through Flink CDC and identified as shown in Table 2 below:

[0130] Table 2

[0131]

[0132]

[0133] Referring to Example 2 above, the initial solution 5 [1,2,3,2,1] means that the numbers corresponding to SQL tasks SQL001 and SQL005 are both 1, indicating that SQL tasks SQL001 and SQL005 belong to group 1. The initial solution 5 [1,2,3,2,1] means that the numbers corresponding to SQL tasks SQL002 and SQL004 are both 2, indicating that SQL tasks SQL002 and SQL004 belong to group 2. The initial solution 5 [1,2,3,2,1] means that the numbers corresponding to SQL task SQL003 are both 3, indicating that SQL task SQL003 belongs to group 3.

[0134] Step 2022: Input multiple initial solutions randomly generated based on each SQL task into the model corresponding to the NSGA-II algorithm, so that the model can iteratively filter the multiple initial solutions.

[0135] During implementation, after randomly generating multiple initial solutions for each SQL task, these multiple initial solutions are input into the model corresponding to the NSGA-II algorithm, so that the model uses an iterative approach to perform multiple rounds of screening of the multiple initial solutions.

[0136] It should be further explained that the model corresponding to the NSGA-II algorithm mentioned above is pre-trained based on historical solutions. For example, M historical solutions are input as input parameters into the initial model corresponding to the NSGA-II algorithm, and the initial model is continuously trained until the output parameters of the initial model are determined to be the maximum number of polling (or, a historical solution). At this point, the configuration parameters of the initial model are determined, and the initial model with determined configuration parameters is identified as the model corresponding to the NSGA-II algorithm. The model corresponding to the NSGA-II algorithm is then used to filter multiple initial solutions.

[0137] Step 2023: During each iteration, determine whether the initial solutions selected in this round meet the convergence criteria. If the convergence criteria are met, determine the multiple groups obtained after grouping each SQL task based on the selected initial solutions. The convergence criteria are either the number of iterations equals the preset maximum number of iterations or the number of selected initial solutions is one. (See [link to relevant documentation]). Figure 5 As shown, the iteration process for each round is as follows:

[0138] Since there are multiple initial solutions, multiple iterations are required to ensure that the selected initial solutions meet the convergence conditions when using the model corresponding to the NSGA-II algorithm to screen multiple initial solutions. For example, condition 1: the number of iterations after multiple iterations is equal to the preset maximum number of iterations, or condition 2: the number of initial solutions screened after multiple iterations is one. That is, when either condition 1 or condition 2 is met, at least one initial solution is selected from multiple initial solutions.

[0139] Clearly, the number of at least one initial solution here is less than the aforementioned multiple solutions. After selecting at least one initial solution, the SQL tasks are grouped according to this initial solution to obtain multiple groups. The specific grouping details here will not be elaborated further.

[0140] The following details the execution steps in each iteration, namely steps 20231 to 20233. The number of times steps 20231 to 20233 are repeatedly executed needs to be set according to the actual use case.

[0141] Step 20231: Based on resource consumption information and time window information, determine the ranking results and congestion values ​​of multiple initial solutions before this iteration.

[0142] During implementation, after inputting multiple initial solutions randomly generated by each SQL task into the model corresponding to the NSGA-II algorithm, the ranking results of the multiple initial solutions are first determined based on the resource consumption information of each SQL task, that is, the ranking results of the multiple initial solutions before this iteration are determined.

[0143] See Figure 6 As shown, based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined, including:

[0144] Step 202311: Based on any initial solution before this iteration, divide each SQL task into k groups represented by the initial solution, where k is a positive integer.

[0145] Since the number of initial solutions may change after each iteration, during implementation, multiple initial solutions from before the current iteration are first obtained, i.e., multiple initial solutions input into the model corresponding to the NSGA-II algorithm during the current iteration. After obtaining the above multiple initial solutions, each SQL task included in each initial solution is divided into k groups represented by the initial solution.

[0146] The process of dividing the SQL tasks into k groups is similar to the previous description. The initial solution 1 [1,1,2,2,1] represents that the numbers corresponding to the SQL tasks SQL001, SQL002, and SQL005 are all 1, meaning they all belong to group 1. Therefore, SQL001, SQL002, and SQL005 are all assigned to group 1. Similarly, the initial solution 1 [1,1,2,2,1] represents that the numbers corresponding to the SQL tasks SQL003 and SQL004 are both 2, meaning they both belong to group 2. Therefore, the value of k is 2.

[0147] Step 202312: Calculate the variance of the resource consumption information of each SQL task in each group of k groups and the variance of the resource consumption information of the remaining SQL tasks in each group, so as to obtain multiple variance values.

[0148] This will be illustrated using an example of randomly generated initial solutions, including initial solution 1 [1,1,2,2,1]. To calculate the variance of resource consumption corresponding to the initial solution, where S in formula (1) i R(S) represents each SQL task included in the initial solution above. i Var(R(S) represents the resource consumption information of the SQL task. i )) represents the variance of resource consumption belonging to the same group in the initial solution.

[0149] In the specific implementation process, the initial solution 1 [1,1,2,2,1] is used for illustration. SQL tasks SQL001, SQL002 and SQL005 all belong to group 1. The resource consumption variance of group 1 is calculated using formula (1) as f1 = Var(R(SQL001)) + Var(R(SQL002)) + Var(R(SQL005)), which is the sum of the variance of the resource consumption information of SQL001 and the resource consumption information of SQL002 and SQL005 in group 1, plus the variance of the resource consumption information of SQL002 and the resource consumption information of SQL005 in group 1. Assume that the above sum is a1. SQL tasks SQL003 and SQL004 both belong to group 2. Using formula (1), the variance of resource consumption in group 2 is f1 = Var(R(SQL003)) + Var(R(SQL004)), which is the variance between the resource consumption information of SQL003 and the resource consumption information of SQL004 in group 2. Let the variance be a2.

[0150] Step 202313: Calculate the absolute value of the difference between the time window information of each SQL task in each group and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values.

[0151] After calculating the variance of resource consumption information corresponding to each initial solution, further... To calculate the absolute value of the difference in time window information of the initial solution, where S in formula (2) i and S k W(S) represents each SQL task included in the initial solution above. i ) and W(S k () represents the time window information for the SQL task.

[0152] Taking the initial solution 1 [1,1,2,2,1] as an example, SQL tasks SQL001, SQL002, and SQL005 all belong to group 1. Using formula (2), the multiple window values ​​of group 1 are calculated as f2 = |W(SQL001) - W(SQL002)| + |W(SQL002) - W(SQL005)|, which is the sum of the absolute values ​​of the differences between the time window information of SQL001 and the time window information of SQL002 and SQL005 in group 1. Assume that the sum is b1. SQL tasks SQL003 and SQL004 both belong to group 2. Using formula (2), the window value of group 2 is calculated as f2 = |W(SQL003) - W(SQL004)|, which is the absolute value of the difference between the time window information of SQL003 and the time window information of SQL004 in group 2. Assume that the absolute value is b2.

[0153] Step 202314: Determine the ranking result based on the variance and window value of each group in k groups, and determine the congestion value of multiple SQL tasks belonging to the same ranking result.

[0154] During implementation, after obtaining the variance value (or the sum of multiple variance values) of each group in each initial solution, the variance values ​​are further added together to obtain the equilibrium value of resource consumption information corresponding to the initial solution.

[0155] Using the initial solution 1 [1,1,2,2,1] as an example, the equilibrium value of resource consumption information for initial solution 1 is f1 = a1 + a2, and we assume that the value of f1 is 36. Similarly, we assume that the value of f1 for initial solution 2 [2,2,1,1,2] is 32, the value of f1 for initial solution 3 [1,2,1,2,1] is 34, and the value of f1 for initial solution 4 [2,2,1,1,2] is 30.

[0156] During implementation, after obtaining multiple window values ​​for each group in each initial solution, the equilibrium value of the time window information corresponding to the initial solution is further calculated.

[0157] Using the initial solution 1 [1,1,2,2,1] as an example, the equilibrium value of the time window information of the initial solution 1 is f2 = b1 + b2, and the value of f2 is assumed to be 50.56.

[0158] Similarly, assume that the f2 value of the initial solution 2 [2,2,1,1,2] is 58.33, the f2 value of the initial solution 3 [1,2,1,2,1] is 45.25, and the f2 value of the initial solution 4 [2,2,1,1,2] is 48.67, calculated using the above method.

[0159] Thus, the values ​​of f1 and f2 for the initial solutions 1[1,1,2,2,1], 2[2,2,1,1,2], 3[1,2,1,2,1] and 4[2,2,1,1,2] are shown in Table 3 below.

[0160] Table 3

[0161] Initial solution 1 36 50.56 Initial solution 2 32 58.33 Initial solution 3 34 45.25 Initial solution 4 30 48.67

[0162] From Table 3 above, we can see that the value of f1 in initial solution 2 (32) is less than the value of f1 in initial solution 1 (36), and the value of f2 in initial solution 2 (58.33) is greater than the value of f2 in initial solution 1 (50.56). Therefore, initial solutions 2 and 1 are mutually dependent. The value of f1 in initial solution 3 (34) is greater than the value of f1 in initial solution 4 (30), and the value of f2 in initial solution 3 (45.25) is less than the value of f2 in initial solution 4 (48.67). Therefore, initial solutions 3 and 4 are mutually dependent. Furthermore, the value of f1 in initial solution 3 (34) is less than the value of f1 in initial solution 1 (36), and the value of f2 in initial solution 3 (45.25) is less than the value of f2 in initial solution 1 (50.56). Therefore, initial solutions 3 and 1 are mutually dependent. The value of f1 in initial solution 4 is 30, which is less than the value of f1 in initial solution 2 is 32. The value of f2 in initial solution 4 is 48.67, which is less than the value of f2 in initial solution 2 is 58.33. Therefore, initial solution 2 and initial solution 4 are mutually dominable.

[0163] Furthermore, given that the resource consumption and time window information corresponding to the initial solutions 3 and 4 are relatively small, the ranking results are determined as follows: the ranking results include ranking 1: initial solutions 3 and 4, and the ranking results include ranking 2: initial solutions 1 and 2.

[0164] After determining the ranking of the initial solutions before this iteration, formula (3) is used. To calculate the crowding values ​​of multiple initial solutions. Where, f k (j+1) represents the value of f1 or f2 of the initial solution preceding the current initial solution, f k (j-1) represents the value of f1 or f2 of the initial solution that follows the current initial solution. This represents the value f1 or f2 that is the largest in the sorted sequence. This represents the value of f1 or f2, which is the smallest value in the sort.

[0165] It should be noted that formula (4) above is only used to calculate the congestion value of the initial solution located in the middle position. Each ranking result uniquely corresponds to one congestion value. Furthermore, when a ranking result includes two initial solutions, both of the above initial solutions are initial solutions at the boundary, and the congestion value of the initial solutions at the boundary is infinite. The position of the initial solution in the ranking result is determined by the magnitude of the above f1 or f2 values.

[0166] In the example above, the ranking results include ranking 1: initial solution 3 and initial solution 4, and ranking results include ranking 2: initial solution 1 and initial solution 2. Since there are two initial solutions in both ranking 1 and ranking 2, that is, both initial solutions are initial solutions at the boundary, the crowding values ​​corresponding to ranking 1 and ranking 2 are both infinite.

[0167] Assuming that initial solutions 1, 3, and 4 in Table 3 are all in the above sorting 1, then the initial solution in the middle position is determined as initial solution 3 according to the value of f1, and formula (3) is used. To calculate the congestion values ​​of multiple initial solutions, when k equals 1, C1 is calculated based on the value of f1. When k equals 2 The crowding degree of initial solution 1 is C1 + C2 = 2. Furthermore, the crowding degree of sort 1 is equal to the sum of the crowding degrees of initial solution 1, initial solution 3, and initial solution 4, which is 2 plus infinity plus infinity. Therefore, the crowding degree of sort 1 is infinity.

[0168] Step 20232: Based on the ranking results and crowding values, determine the parent solutions from each initial solution, and perform crossover and mutation on the parent solutions to obtain multiple child solutions.

[0169] Considering that the above initial solutions are randomly generated for each SQL task, that is, the above initial solutions are not necessarily the optimal solutions, based on this, in the embodiments of this application, after determining the ranking result and the congestion value, the above initial solutions will be optimized and expanded by means of crossover and mutation.

[0170] See Figure 7 As shown, the above method for determining the parent solution from each initial solution specifically includes:

[0171] Step 202321: Calculate the comprehensive value of each group in the k groups based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0172] Since different application scenarios place different emphasis on ranking results and congestion values, when determining the parent solution from the randomly generated initial solutions, priority can be given based on the actual emphasis. During implementation, the ranking result or congestion value in the intermediate results is determined by setting the specific proportion of ranking weight and congestion weight.

[0173] To facilitate computation, during implementation, each SQL task is grouped using a different initial solution, resulting in k groups. After calculating the ranking result and congestion value for each group, a ranking value is obtained by multiplying the ranking result by a preset ranking weight, and a congestion value is obtained by multiplying the congestion value by a preset congestion weight. The sum of the ranking value and the congestion value is called the comprehensive value. A comprehensive value is calculated for each of the k groups represented by an initial solution.

[0174] Step 202322: Select the h group with the highest comprehensive value from the k groups, and determine the initial solution corresponding to the h group as the parent solution, where h is a positive integer less than k.

[0175] After calculating the comprehensive value of each group, the comprehensive values ​​are sorted in descending order, and h groups with the highest comprehensive values ​​are selected from the k groups, thus obtaining h groups. The initial solutions corresponding to the above h groups are then determined as the parent solutions.

[0176] The method described above, which selects h initial solutions from k groups as parent solutions, effectively filters the initial solutions randomly generated for the SQL task, thereby improving the effectiveness and quality of each initial solution group. This leads to a more convergent final initial solution, thus providing an effective guarantee for the establishment of subsequent initial solutions.

[0177] See Figure 8 As shown, crossover and mutation of the parent solution yield multiple child solutions, including:

[0178] Step 202321': Cross the values ​​at any position in the parent solution by simulating the binary crossover algorithm to obtain multiple crossover parent solutions.

[0179] In this embodiment of the application, to ensure the comprehensiveness of the initial solution, after determining the parent solution from each initial solution, the parent solution is further expanded. During implementation, after determining the specific number of SQL tasks included in an initial solution, this number is used as the specific number of SQL tasks included in the expanded cross-parent solution, thereby ensuring the consistency of each initial solution.

[0180] Typically, the simulated binary crossover algorithm randomly selects a crossover point, partially swaps two parent individuals at that point, and generates two child individuals. If no crossover is performed, the parent individual is directly returned as the child. In implementation, the simulated binary crossover algorithm is used to expand the parent solutions, that is, to crossover the values ​​at any position in the parent solutions. The specific crossover method is as indicated by the simulated binary crossover algorithm. The multiple parent solutions obtained after crossover are determined as multiple crossover parent solutions.

[0181] Step 202322': Randomly change the values ​​at any position in the multiple cross-parent solutions to obtain multiple child solutions.

[0182] During implementation, to enrich the child solutions, after obtaining multiple cross-parent solutions, the values ​​of any one of the cross-parent solutions are modified. This involves randomly changing the values ​​at any position within the cross-parent solution, thus generating multiple child solutions from a single cross-parent solution. It should be noted that, to ensure greater convergence of the child solutions, the random modification of these values ​​is primarily limited to altering values ​​already included in the initial solution.

[0183] For example, the initial solution 1 [1,1,2,2,1] is used as a cross parent solution. When the value at any position in the initial solution 1 is randomly changed, multiple child solutions are obtained, including but not limited to [2,1,2,2,1], [1,2,2,2,1], [1,1,1,2,1], [1,1,1,2,1], and [2,2,1,1,2], etc.

[0184] Step 20233: Merge the parent solution and the child solution to obtain multiple extended initial solutions, and select multiple initial solutions from the extended initial solutions based on resource consumption information and time window information.

[0185] During implementation, after obtaining the parent and child solutions, the parent and child solutions are further merged to obtain multiple expanded initial solutions, which is the set of optimal initial solutions matched for each SQL task.

[0186] See Figure 9 As shown, the steps described above for obtaining multiple initial solutions from the parent and child solutions specifically include:

[0187] Step 202331: Find the union of the parent solution and the child solution to obtain multiple extended initial solutions.

[0188] During implementation, the number of parent and child solutions obtained from the above steps is uncertain, but the parent and child solutions have the same function and status. Based on this, the union of the parent and child solutions is taken to obtain a set that includes all parent and child solutions. This set as a whole is used as the extended initial solution for multiple SQL tasks, that is, each parent solution and each child solution is used as an extended initial solution.

[0189] In this embodiment of the application, in order to make the randomly obtained initial solution more accurate, the initial solution is optimized according to resource consumption information and time window information. That is, the expanded initial solution is obtained by determining the parent solution, crossover and mutation to obtain the child solution, etc., thereby providing an effective filtering range for determining the grouping of SQL tasks.

[0190] After obtaining a valid range of initial solutions, further optimization is performed using the resource consumption and time window information of the SQL task. This involves determining the parent solution, crossover and mutation of child solutions from the expanded initial solutions to obtain the final initial solution.

[0191] Step 202332: Divide each SQL task into q groups represented by the initial solutions based on multiple expanded initial solutions, where q is a positive integer.

[0192] Since each expanded initial solution includes a group representing the SQL task at the corresponding position, during implementation, the following operations are performed for each expanded initial solution: that is, the specific number at each position in the expanded initial solution is determined, and the same number is found. The same number indicates that the SQL task at the corresponding position belongs to the same group. Conversely, if the above numbers are different, it means that the SQL task at the corresponding position belongs to different groups. Furthermore, the expanded initial solution is divided into multiple groups, namely the above q groups, by the sameness or non-identity of the above numbers.

[0193] For example, suppose an expanded initial solution is [3,1,2,3,1]. In this expanded initial solution, the first digit 3 is the same as the fourth digit 3, the second digit 1 is the same as the fifth digit 1, and the third digit 2 is different from the other digits. Thus, the resulting SQL task is divided into q groups, which are three groups: the first group includes SQL2 and SQL5, the second group includes SQL3, and the third group includes SQL1 and SQL4.

[0194] Step 202333: Calculate the variance of the resource consumption information of each SQL task in each group of q and the resource consumption information of the remaining SQL tasks in each group, so as to obtain multiple variance values.

[0195] During implementation, after dividing the SQL tasks into q groups, formula (1) is used. We use the formula j = 1, 2, ..., m to calculate the variance between the resource consumption information of each SQL task within each group of group q and the resource consumption information of the remaining SQL tasks within that group, thus obtaining multiple variance values. The specific calculation process here is similar to that described above and will not be repeated here.

[0196] Let's continue with the expanded initial solution [3,1,2,3,1] from the above steps. That is, we calculate the two variances of the resource consumption information of the remaining SQL tasks in the first group, the second group, and the third group, respectively, and calculate the one variance of the resource consumption information of the remaining SQL tasks in the second group and the third group.

[0197] Step 202334: Calculate the absolute value of the difference between the time window information of each SQL task in each group of q and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values.

[0198] During implementation, after dividing the SQL tasks into q groups, formula (2) is used. Calculate the absolute value of the difference between the time window information of each SQL task within each group of q and the time window information of the remaining SQL tasks within each group, thus obtaining multiple window values. The specific calculation process here is similar to that described above and will not be repeated here.

[0199] Let's continue with the expanded initial solution [3,1,2,3,1] from the above steps. This means calculating the absolute values ​​of the two differences between the time window information of the first group and the second group, as well as the other SQL tasks in the third group, which are the two window values. It also means calculating the absolute value of the difference between the time window information of the other SQL tasks in the second group and the third group, which is the one window value.

[0200] During implementation, after obtaining the variance value (or the sum of multiple variance values) for each group in each expanded initial solution, the variance values ​​are further summed to obtain the equilibrium value of resource consumption information corresponding to the expanded initial solution. Additionally, after obtaining multiple window values ​​for each group in each expanded initial solution, the equilibrium value of time window information corresponding to the expanded initial solution is further calculated. The specific calculation process here is similar to that described above and will not be repeated here.

[0201] Step 202335: Determine the ranking result based on the variance and window value of each group in group q, and determine the congestion value of SQL tasks belonging to the same ranking result in group q.

[0202] During implementation, after obtaining the variance and window value of the expanded initial solution for each group in group q, the ranking of the expanded initial solutions for each group is determined based on whether the magnitude of the variance and window value can dominate. The specific calculation process here is similar to that described above and will not be repeated here.

[0203] During implementation, after determining the ranking results of multiple extended initial solutions, formula (3) was used. We calculate the congestion values ​​of multiple expanded initial solutions to determine the congestion values ​​of q groups of SQL tasks belonging to the same sorting level. The specific calculation process here is similar to that described above and will not be repeated here.

[0204] Step 202336: Calculate the comprehensive value of each group in group q based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0205] Similarly, during implementation, the ranking results or crowding values ​​related to the above-mentioned expanded initial solution are determined by setting specific percentage values ​​for ranking weight and crowding weight.

[0206] During implementation, after calculating the ranking result and congestion value corresponding to the expanded initial solution for each group, the ranking result is multiplied by a preset ranking weight to obtain the ranking value, and the congestion value is multiplied by a preset congestion weight to obtain the congestion value. The sum of the ranking value and the congestion value is called the comprehensive value. The comprehensive value is calculated separately for each group represented by an expanded initial solution.

[0207] Step 202337: Select the group f with the highest comprehensive value from the group q, and determine the extended initial solution corresponding to the group f as multiple initial solutions to be selected, where f is a positive integer less than q.

[0208] Similarly, during implementation, after calculating the comprehensive value of each group, the comprehensive values ​​are sorted in descending order, and the f groups with the highest comprehensive values ​​are selected from the q groups, thus obtaining f groups. Then, the extended initial solutions corresponding to the above f groups are selected as multiple initial solutions.

[0209] During implementation, after grouping each SQL task into multiple groups, the SQL tasks included in the same group are merged. The merging method is not specifically limited, resulting in multiple merged SQL task bars, that is, each group is converted into an SQL task bar.

[0210] Step 203: Create processing items for each merged SQL task bar and submit each processing item to the corresponding server cluster for processing.

[0211] During implementation, after obtaining multiple merged SQL task bars, a processing item is created for each SQL task bar, for example, a Flink processing item. To ensure that the SQL task bars are executed sequentially, the created processing items are submitted to the corresponding server cluster, which then processes the SQL task bars.

[0212] It should be noted that, in this embodiment of the application, if the above processing item fails to be successfully submitted to the corresponding server cluster, the processing item is submitted to the corresponding server cluster again until the submission is successful, thereby ensuring the integrity of the SQL task execution.

[0213] Based on the same inventive concept, see [reference] Figure 10 As shown in the figure, this application provides an apparatus for grouping SQL tasks, including:

[0214] The acquisition unit 1001 is used to acquire multiple SQL tasks based on the database's runtime log data. Each SQL task carries resource consumption information and time window information.

[0215] Grouping unit 1002 is used to group each SQL task based on resource consumption information and time window information to obtain multiple groups, and to merge the SQL tasks included in the same group to obtain multiple merged SQL task bars, wherein the number of task bars is the same as the number of groups;

[0216] Submission unit 1003 is used to create processing items for each merged SQL task bar and submit each processing item to the corresponding server cluster for processing.

[0217] Optionally, multiple SQL tasks are retrieved based on the database's runtime log data. The retrieval unit 1001 is used for:

[0218] Establish a Flink CDC and use the Flink CDC to monitor the database's runtime log data in real time to see if there are any changes.

[0219] If changes are detected in the runtime log data, multiple SQL tasks are extracted from the changed runtime log data.

[0220] Multiple SQL tasks are parsed separately, and the resource consumption information and time window information carried by the SQL tasks are extracted from the parsing results.

[0221] Optionally, each SQL task can be grouped into multiple groups based on resource consumption information and time window information. The grouping unit 1002 is used for:

[0222] For each SQL task, multiple initial solutions are randomly generated. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the SQL tasks in the corresponding position belong to the same group. Different numbers indicate that the SQL tasks in the corresponding positions belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number.

[0223] Multiple initial solutions randomly generated based on each SQL task are input into the model corresponding to the NSGA-II algorithm, so that the model uses an iterative method to filter the multiple initial solutions;

[0224] In each iteration, it is determined whether the initial solution selected in this round meets the convergence condition. If the convergence condition is met, the SQL tasks are grouped into multiple groups based on the selected initial solutions. The convergence condition is that the number of iterations is equal to the preset maximum number of iterations or the number of selected initial solutions is one. The iteration process is as follows:

[0225] Based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined respectively;

[0226] Based on the ranking results and crowding values, parent solutions are determined from each initial solution, and multiple child solutions are obtained by crossover and mutation of the parent solutions.

[0227] The parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from the expanded initial solutions based on resource consumption information and time window information.

[0228] Optionally, based on resource consumption information and time window information, the ranking results and congestion values ​​of multiple initial solutions before this iteration are determined respectively, and the grouping unit 1002 is used for:

[0229] Based on any initial solution before this iteration, each SQL task is divided into k groups represented by the initial solution, where k is a positive integer;

[0230] Calculate the variance of the resource consumption information of each SQL task in each of the k groups and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0231] Calculate the absolute value of the difference between the time window information of each SQL task in each group and the time window information of the remaining SQL tasks in each group, thereby obtaining multiple window values;

[0232] The ranking result is determined based on the variance and window value of each of the k groups, and the congestion value of multiple SQL tasks belonging to the same ranking result is determined separately.

[0233] Optionally, parent solutions are determined from each initial solution based on the ranking results and crowding values. Grouping unit 1002 is used for:

[0234] The comprehensive value of each group in k groups is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0235] Select the h group with the highest comprehensive value from the k groups, and determine the initial solution corresponding to the h group as the parent solution, where h is a positive integer less than k.

[0236] Optionally, multiple child solutions are obtained by crossover and mutation of the parent solution, and the grouping unit 1002 is used for:

[0237] By simulating the binary crossover algorithm, the values ​​at any position in the parent solution are crossed to obtain multiple crossover parent solutions;

[0238] By randomly changing the values ​​at any position in multiple cross-parent solutions, multiple child solutions are obtained.

[0239] Optionally, the parent and child solutions are merged to obtain multiple expanded initial solutions. Multiple initial solutions are then selected from these expanded initial solutions based on resource consumption information and time window information. The grouping unit 1002 is used for:

[0240] By taking the union of the parent and child solutions, multiple extended initial solutions are obtained;

[0241] Each SQL task is divided into q groups represented by the initial solutions based on multiple extended initial solutions, where q is a positive integer;

[0242] Calculate the variance of the resource consumption information of each SQL task in each group of q and the resource consumption information of the remaining SQL tasks in each group to obtain multiple variance values.

[0243] Calculate the absolute value of the difference between the time window information of each SQL task in each group of q and the time window information of the remaining SQL tasks in each group, so as to obtain multiple window values;

[0244] The ranking results are determined based on the variance and window value of each group in group q, and the congestion value of SQL tasks belonging to the same ranking result in group q is determined respectively.

[0245] The comprehensive value of each group in group q is calculated based on the preset sorting weight, the level sorting result, the preset crowding weight, and the crowding value.

[0246] Select the group with the highest comprehensive value from the group q, and determine the extended initial solution corresponding to the group f as multiple initial solutions, where f is a positive integer less than q.

[0247] Based on the same inventive concept, see [reference] Figure 11 As shown, this application embodiment provides a server, including: a memory 1101 for storing executable instructions; and a processor 1102 for reading and executing the executable instructions stored in the memory, and executing any of the methods described above.

[0248] Based on the same inventive concept, embodiments of this application provide a computer-readable storage medium that, when the instructions in the storage medium are executed by a processor, enables the processor to perform the method described in any of the first aspects above.

[0249] In summary, the embodiments of this application provide a method, apparatus, and storage medium for grouping SQL tasks. The method includes: obtaining multiple SQL tasks based on database operation log data, wherein each SQL task carries resource consumption information and time window information; grouping each SQL task into multiple groups based on the resource consumption information and time window information; merging the SQL tasks included in the same group to obtain multiple merged SQL task bars, wherein the number of task bars is the same as the number of groups; establishing processing items for each merged SQL task bar; and submitting each processing item to the corresponding server cluster for processing. The above scheme intelligently groups and merges a large number of SQL tasks, optimizes resource consumption balance and time window consistency, reduces the number of processing items, improves resource utilization efficiency, and reduces the complexity of system operation and maintenance.

[0250] Those skilled in the art will understand that embodiments of this application can be provided as methods, systems, or computer program product systems. Therefore, this application can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, this application can take the form of a computer program product system implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0251] This application is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program product systems according to this application. It should be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0252] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0253] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0254] Obviously, those skilled in the art can make various modifications and variations to this application without departing from the spirit and scope of this application. Therefore, if such modifications and variations fall within the scope of the claims of this application and their equivalents, this application also intends to include such modifications and variations.

Claims

1. A method for grouping SQL tasks, characterized in that, The method includes: Multiple SQL tasks are obtained from the database's runtime log data, each of which carries resource consumption information and time window information; Multiple initial solutions are randomly generated for each of the SQL tasks. These initial solutions are then input into the model corresponding to the NSGA-II algorithm. The model iteratively filters the initial solutions, determining whether the selected solutions meet the convergence criteria in each iteration. If the convergence criteria are met, the model parameters are determined, and multiple groups are formed based on the selected initial solutions after grouping each SQL task. The convergence criteria are either the number of iterations equals the preset maximum number of iterations or the number of selected initial solutions is one. Each iteration process is as follows: Based on the resource consumption information and the time window information, the ranking results and congestion values ​​of the initial solutions before the current iteration are determined. Based on the ranking results and congestion values, the solutions are selected from each initial solution... The parent solution is determined from the solution. The parent solution is cross-crossed and mutated to obtain multiple child solutions. The parent solution and the child solutions are merged to obtain multiple extended initial solutions. Based on the resource consumption information and the time window information, multiple initial solutions are selected from the extended initial solutions. The SQL tasks included in the same group are merged to obtain multiple merged SQL task entries. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the SQL tasks in the corresponding position belong to the same group. Different numbers indicate that the SQL tasks in the corresponding position belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number. The number of task entries is the same as the number of groups. Each of the merged SQL task bars is assigned a processing item, and each processing item is submitted to the corresponding server cluster for processing.

2. The method as described in claim 1, characterized in that, The database-based operation log data acquisition involves multiple SQL tasks, including: Establish a Flink CDC and use the Flink CDC to monitor in real time whether the running log data of the database has changed; If a change is detected in the runtime log data, then multiple SQL tasks are extracted from the changed runtime log data. The SQL tasks are parsed separately, and the resource consumption information and time window information carried by the SQL tasks are extracted from the parsing results.

3. The method as described in claim 2, characterized in that, The step of determining the ranking results and congestion values ​​of multiple initial solutions before the current iteration based on the resource consumption information and the time window information includes: Based on any initial solution before this iteration, each SQL task is divided into k groups represented by the initial solution, where k is a positive integer; Calculate the variance between the resource consumption information of each SQL task in each of the k groups and the resource consumption information of the remaining SQL tasks in each group, thereby obtaining multiple variance values. Calculate the absolute value of the difference between the time window information of each SQL task in each of the k groups and the time window information of the remaining SQL tasks in each group, thereby obtaining multiple window values; The ranking result is determined based on the variance value and the window value of each of the k groups, and the congestion value of multiple groups of SQL tasks belonging to the same ranking result is determined respectively.

4. The method as described in claim 3, characterized in that, The process of determining the parent solution from each of the initial solutions based on the ranking result and the crowding value includes: The comprehensive value of each group in k groups is calculated based on the preset sorting weight, the ranking result, the preset crowding weight, and the crowding value. Select the h groups with the highest comprehensive values ​​from the k groups, and determine the initial solution corresponding to the h groups as the parent solution, where h is a positive integer less than k.

5. The method as described in claim 4, characterized in that, The process of crossover and mutation of the parent solution to obtain multiple child solutions includes: By simulating a binary crossover algorithm, the values ​​at any position in the parent solution are crossed to obtain multiple crossover parent solutions; By randomly changing the values ​​at any position in the multiple parent solutions, multiple child solutions are obtained.

6. The method as described in claim 1, characterized in that, The process of merging the parent solution and the child solution to obtain multiple expanded initial solutions, and selecting multiple initial solutions from the expanded initial solutions based on the resource consumption information and the time window information, includes: By taking the union of the parent solution and the child solution, multiple extended initial solutions are obtained; Each SQL task is divided into q groups represented by the initial solutions based on multiple expanded initial solutions, where q is a positive integer; Calculate the variance of the resource consumption information of each SQL task in each group of the q groups with the variance of the resource consumption information of the remaining SQL tasks in each group, thereby obtaining multiple variance values; Calculate the absolute value of the difference between the time window information of each SQL task in each group of the q groups and the time window information of the remaining SQL tasks in each group, thereby obtaining multiple window values; The ranking result is determined based on the variance value and the window value of each group in the q groups, and the congestion value of the SQL tasks belonging to the same ranking result in the q groups is determined respectively. The comprehensive value of each group in group q is calculated based on the preset sorting weight, the ranking result, the preset crowding weight, and the crowding value. From the q groups, select the f groups with the highest comprehensive values, and determine the extended initial solutions corresponding to the f groups as multiple initial solutions, where f is a positive integer less than q.

7. An apparatus for grouping SQL tasks, characterized in that, include: The acquisition unit is used to acquire multiple SQL tasks based on the database's runtime log data, wherein each SQL task carries resource consumption information and time window information; A grouping unit is used to randomly generate multiple initial solutions for each of the SQL tasks. These initial solutions are then input into the model corresponding to the NSGA-II algorithm. The model iteratively filters the initial solutions, determining whether each selected solution meets the convergence condition in each iteration. If the convergence condition is met, the model parameters are determined, and multiple groups are formed based on the selected initial solutions. The convergence condition is either the number of iterations equals a preset maximum number of iterations or the number of selected initial solutions is one. Each iteration process is as follows: Based on the resource consumption information and the time window information, the ranking and congestion values ​​of the initial solutions before the current iteration are determined. Based on the ranking and congestion values, the solutions are selected from each... The parent solution is determined from the initial solution. Multiple child solutions are obtained by crossover and mutation of the parent solution. The parent solution and the child solutions are merged to obtain multiple extended initial solutions. Multiple initial solutions are selected from the extended initial solutions based on the resource consumption information and the time window information. The SQL tasks included in the same group are merged to obtain multiple merged SQL task entries. Each initial solution includes multiple numbers in the i-th position. Each number represents the i-th SQL task. The same number indicates that the corresponding SQL tasks belong to the same group. Different numbers indicate that the corresponding SQL tasks belong to different groups. The time window information of the SQL tasks in the same group represents the same window type. i is a natural number. The number of task entries is the same as the number of groups. The submission unit is used to create processing items for each of the merged SQL task bars and submit each processing item to the corresponding server cluster for processing.

8. A server, characterized in that, include: Memory, used to store executable instructions; A processor for reading and executing executable instructions stored in the memory to implement the method as described in any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that, When the instructions in the storage medium are executed by the processor, the processor is able to perform the method as described in any one of claims 1 to 6.

Images