A database adaptive hybrid data partitioning method and system based on reinforcement learning

Abstract

A method and system for adaptive hybrid data partitioning of a database based on reinforcement learning, which belongs to the field of artificial intelligence. The present invention solves the problem that under the existing data partitioning scheme, a large amount of redundant data needs to be accessed when executing the query load and the data cannot be automatically adaptively partitioned according to the dynamic workload. The present invention establishes a workload prediction model, and predicts the frequency of various types of queries in the query workload of the database in the future based on the historical workload. The hybrid data partitioning problem is modeled as a Markov decision process, and the state, action and reward of the intelligent agent are defined. According to the predicted frequency of various types of queries and the trained reinforcement learning model, a suitable hybrid data partitioning scheme is adaptively recommended for the incoming query workload, thereby reducing the amount of data that needs to be accessed when the query load is executed. The method of the present invention can be applied to adaptive hybrid data partitioning of databases.

Description

Technical Field

[0001] The present invention belongs to the field of artificial intelligence, and in particular relates to a database adaptive hybrid data partitioning method and system based on reinforcement learning. Background Art

[0002] Database query workloads typically only involve a subset of data. The goal of database partitioning is to minimize the amount of irrelevant data that is not relevant to the query during workload execution. Existing data partitioning schemes include horizontal partitioning, vertical partitioning, and hybrid horizontal-vertical partitioning. However, these data partitioning strategies still require access to a large amount of redundant data during query execution, and cannot achieve adaptive data partitioning for dynamic query workloads.

[0003] Therefore, in order to solve the problem that under the existing data partitioning scheme, query load execution still requires access to a large amount of redundant data and cannot automatically perform adaptive data partitioning for dynamic workloads, it is necessary to propose a new database adaptive hybrid data partitioning method. Summary of the Invention

[0004] The purpose of the present invention is to solve the problem that under the existing data partitioning scheme, a large amount of redundant data needs to be accessed when executing the query load and the data partitioning cannot be automatically adaptively performed according to the dynamic workload. A database adaptive hybrid data partitioning method and system based on reinforcement learning is proposed.

[0005] The technical solution adopted by the present invention to solve the above technical problems is:

[0006] According to one aspect of the present invention, a method for adaptive hybrid data partitioning of a database based on reinforcement learning comprises the following steps:

[0007] Step 1: Collect information about each table in the database, historical query workload information, and the data partitioning scheme corresponding to the historical query workload information;

[0008] Step 2: Use the table information and historical query workload information in the database as the state of the reinforcement learning model agent. Use the table information and historical query workload information collected in step 1 to train the reinforcement learning model agent, and use the data partitioning scheme corresponding to the historical query workload information as the label for the agent training;

[0009] Step 3: Establish a query workload prediction model and train the established query workload prediction model using historical query workload information;

[0010] Step 4: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps.

[0011] The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'.

[0012] Step 5: Initialize k=1;

[0013] Step 6: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme;

[0014] If they are the same, use the current data partitioning scheme to query directly and then execute step 7;

[0015] If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step seven is continued;

[0016] Step 7: Determine whether the next time step is completed;

[0017] If the next time step has not ended, set k = k + 1 and return to step 6;

[0018] If the next time step ends, return to step 4.

[0019] According to another aspect of the present invention, a database adaptive hybrid data partitioning system based on reinforcement learning is provided, the system comprising an information collection module, a reinforcement learning model, a query workload prediction model, and a data partitioning module, wherein:

[0020] The information collection module is used to collect information of each table in the database, historical query workload information and data partitioning scheme corresponding to the historical query workload information;

[0021] The historical query workload information includes each query statement and the execution time of each query statement;

[0022] The reinforcement learning model is trained based on the information collected by the information collection module, and the trained agent of the reinforcement learning model is used to output a data partitioning scheme;

[0023] The agent's actions are: after entering a query statement, the agent takes the following actions on the current state: add rows, add columns, delete rows, delete columns, merge partitions, split partitions, or delete partitions; the reward the agent receives after performing the action is:

[0024]

[0025] Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost t The cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 The size of the partition where the query result is located after entering the query statement under the data partitioning scheme at time t-1;

[0026] The query workload prediction model is trained based on the historical query workload information collected by the information collection module, and the trained query workload prediction model is used to predict the future frequency of various types of query workloads;

[0027] The working process of the query workload prediction model is as follows:

[0028] Step 1: Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps;

[0029] Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained;

[0030] Step 2: Cluster the time series data corresponding to various types of historical query workloads and obtain the time series data within each cluster;

[0031] For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster.

[0032] For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster;

[0033] Step 3: For the kth cluster, the number of occurrences of the query workload corresponding to the 1st time step to the pth time step on the fitted time series data corresponding to the kth cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1th time step on the fitted time series data corresponding to the kth cluster is used as the label of the training sample;

[0034] Where k = 1, 2, ..., K, K is the total number of clustering categories;

[0035] Step 4: Use all the training samples obtained in step 3 to train the query workload prediction model;

[0036] In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result;

[0037] The loss is then calculated based on the prediction results and labels. Training is stopped when the loss converges, and a trained query workload prediction model is obtained.

[0038] The data partitioning module is used to generate a data partitioning scheme based on the prediction results of the query workload prediction model and the intelligent agent of the trained reinforcement learning model.

[0039] The beneficial effects of the present invention are:

[0040] The present invention proposes an adaptive hybrid data partitioning technology based on reinforcement learning. The database data partitioning problem is established as a Markov decision process. The reinforcement learning model is trained using Q learning. At the same time, by constructing a query workload prediction model, the frequency of various types of queries in the query workload in the next time period is found, and the appropriate data partitioning scheme is intelligently recommended.

[0041] 1. Establish a workload prediction model to predict the frequency of various queries in the database query workload in the future based on historical workload, which is convenient for subsequent calls.

[0042] 2. Model the hybrid data partitioning problem as a Markov decision process, defining the agent's states, actions, and rewards. The trained reinforcement learning model can adaptively recommend an appropriate hybrid data partitioning scheme for incoming query workloads, reducing the amount of data required to execute the query workload. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] Figure 1 It is a flow chart of the method of the present invention. DETAILED DESCRIPTION

[0044] Specific implementation method 1: Combination Figure 1 This embodiment describes a method for adaptive hybrid data partitioning of a database based on reinforcement learning, and the method specifically includes the following steps:

[0045] Step 1: Collect information about each table in the database, historical query workload information, and the data partitioning scheme corresponding to the historical query workload information;

[0046] Step 2: Model the data partitioning process as a Markov decision process. Use the database table information and historical query workload information as the state of the reinforcement learning model (DDQN) agent. Use the table information and historical query workload information collected in step 1 to train the reinforcement learning model agent. Use the data partitioning scheme corresponding to the historical query workload information as the label for agent training.

[0047] The agent is a deep neural network that fits the Q function. The Q function describes the expected total reward from performing action a in a certain state s, thereby selecting the optimal action for the current state. In each round of training, the agent selects an action. The reward function generates a reward for each action, which in turn generates a corresponding loss value, allowing the agent to optimize its parameters. The trained agent can be used to generate a data partitioning scheme.

[0048] Step 3: Establish a query workload prediction model and train the established query workload prediction model using historical query workload information;

[0049] Step 4: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps.

[0050] The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'.

[0051] Step 5: Initialize k=1;

[0052] Step 6: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme;

[0053] If they are the same, use the current data partitioning scheme to query directly and then execute step 7;

[0054] If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step seven is continued;

[0055] If the types of query workloads that have arrived previously are all the same as query workload type i', there is no need to update the current data partitioning scheme for the time being. The data partitioning scheme will be updated only when a different type of workload arrives. The workload type corresponding to the updated data partitioning scheme will be compared with the newly arrived workload type. If they are consistent, there is no need to update the data partitioning scheme this time. Otherwise, the data partitioning scheme will continue to be updated.

[0056] Step 7: Determine whether the next time step is completed;

[0057] If the next time step has not ended, set k = k + 1 and return to step 6;

[0058] If the next time step ends, the latest time step is used as the current time step and the process returns to step 4.

[0059] The database of the present invention can adaptively perform hybrid data partitioning based on query workload. It also predicts the frequency of each type of query in the query workload at the next time step and determines whether the query workload has significantly changed to determine whether the data partitioning scheme needs to be regenerated. This avoids the training and layout overhead associated with frequent data partitioning, while also enabling the generation of appropriate data partitioning schemes before new query workloads arrive, improving database query efficiency.

[0060] Specific implementation method 2: This implementation method is different from specific implementation method 1 in that the information of each table in the database includes the size of the table, the number of tuples, the number of attributes and the size of each attribute.

[0061] Other steps and parameters are the same as those in the first embodiment.

[0062] Specific implementation method three: This implementation method is different from specific implementation methods one or two in that the historical query workload information includes each query statement and the execution time of each query statement.

[0063] Other steps and parameters are the same as those in the first or second embodiment.

[0064] Specific implementation method 4: This implementation method is different from any one of specific implementation methods 1 to 3 in that the data partitioning scheme is the partitioning result of each table in the database.

[0065] The other steps and parameters are the same as those in the first to third embodiments.

[0066] Specific implementation method 5: This implementation method is different from the specific implementation methods 1 to 4 in that the

[0067] The action of the agent is: after the query statement is entered, the agent takes the following actions on the current state: add rows, add columns, delete rows, delete columns, merge partitions, split partitions, or delete partitions.

[0068] The other steps and parameters are the same as those in the first to fourth embodiments.

[0069] Specific embodiment 6: This embodiment differs from any one of specific embodiments 1 to 5 in that the reward obtained by the agent after performing an action in the reinforcement learning model is:

[0070]

[0071] Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost t The cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 It is the size of the partition where the query result is located after the query statement is entered under the data partitioning scheme at time t-1.

[0072] The other steps and parameters are the same as those in the first to fifth embodiments.

[0073] Specific embodiment seven: This embodiment differs from any one of specific embodiments one to six in that the query workload prediction model includes a DLinear model and a Transformer model.

[0074] The other steps and parameters are the same as those in the first to sixth embodiments.

[0075] Specific embodiment eight: This embodiment differs from any one of specific embodiments one to seven in that the specific process of step three is as follows:

[0076] Step 3.1. Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps;

[0077] For example, the time from 0 to T is the first time step, the time from T to 2T is the second time step, and so on;

[0078] Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained;

[0079] That is, each time step corresponds to I values, and each value is the number of times each type of historical query workload appears in a time step. For a type of query workload, the number of times this type of query workload appears in each time step constitutes a set of time series data of this type of query workload changing with time steps.

[0080] Step 32: Cluster the time series data corresponding to various types of historical query workloads to obtain the time series data within each cluster. That is, cluster the time series data with similar change trends and data at each time point into one category.

[0081] For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster.

[0082] For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster;

[0083] Step 3: For the k-th cluster, the number of occurrences of the query workload corresponding to the 1st time step to the p-th time step on the fitted time series data corresponding to the k-th cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1-th time step on the fitted time series data corresponding to the k-th cluster is used as the label of the training sample;

[0084] Where k = 1, 2, ..., K, K is the total number of clustering categories;

[0085] It should be noted that the value of p can be set according to actual conditions. For example, when p = 10, the number of occurrences of the query workload corresponding to the 1st time step to the 10th time step can be used as a training sample, and the number of occurrences of the query workload corresponding to the 11th time step can be used as a label. Then, using a sliding window method, the number of occurrences of the query workload corresponding to the 2nd time step to the 11th time step can be used as a training sample, and the number of occurrences of the query workload corresponding to the 12th time step can be used as a label; and so on.

[0086] Step 34: train the query workload prediction model using all the training samples obtained in step 33;

[0087] In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result;

[0088] The loss is then calculated based on the prediction results and labels, and training is stopped when the loss converges to obtain a trained query workload prediction model.

[0089] The other steps and parameters are the same as those in the first to seventh embodiments.

[0090] The hybrid partitioning strategy proposed in this invention is compared with the partitioning strategies obtained by other classic algorithms under different data sets and workloads. A large number of comparative experiments prove that the method proposed in this invention has good performance. Compared with the most advanced methods, its running time is shortened by an average of 35.67% and the scanning size is reduced by an average of 46.67%.

[0091] Specific embodiment 9: This embodiment describes a database adaptive hybrid data partitioning system based on reinforcement learning, the system includes an information collection module, a reinforcement learning model, a query workload prediction model and a data partitioning module, wherein:

[0092] The information collection module is used to collect information of each table in the database, historical query workload information and data partitioning scheme corresponding to the historical query workload information;

[0093] The historical query workload information includes each query statement and the execution time of each query statement;

[0094] The reinforcement learning model is trained based on the information collected by the information collection module, and the trained agent of the reinforcement learning model is used to output a data partitioning scheme;

[0095] The agent's actions are: after entering a query statement, the agent takes the following actions on the current state: add rows, add columns, delete rows, delete columns, merge partitions, split partitions, or delete partitions; the reward the agent receives after performing the action is:

[0096]

[0097] Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost tThe cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 The size of the partition where the query result is located after entering the query statement under the data partitioning scheme at time t-1;

[0098] The query workload prediction model is trained based on the historical query workload information collected by the information collection module, and the trained query workload prediction model is used to predict the future frequency of various types of query workloads;

[0099] The working process of the query workload prediction model is as follows:

[0100] Step 1: Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps;

[0101] Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained;

[0102] Step 2: Cluster the time series data corresponding to various types of historical query workloads and obtain the time series data within each cluster;

[0103] For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster.

[0104] For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster;

[0105] Step 3: For the kth cluster, the number of occurrences of the query workload corresponding to the 1st time step to the pth time step on the fitted time series data corresponding to the kth cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1th time step on the fitted time series data corresponding to the kth cluster is used as the label of the training sample;

[0106] Where k = 1, 2, ..., K, K is the total number of clustering categories;

[0107] Step 4: Use all the training samples obtained in step 3 to train the query workload prediction model;

[0108] In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result;

[0109] The loss is then calculated based on the prediction results and labels. Training is stopped when the loss converges, and a trained query workload prediction model is obtained.

[0110] The data partitioning module is used to generate a data partitioning scheme based on the prediction results of the query workload prediction model and the intelligent agent of the trained reinforcement learning model.

[0111] Specific embodiment ten: This embodiment differs from specific embodiment nine in that the working process of the data partition module is as follows:

[0112] Step 1: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps.

[0113] The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'.

[0114] Step 2: Initialize k=1;

[0115] Step 3: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme;

[0116] If they are the same, use the current data partitioning scheme to query directly and then execute step 4;

[0117] If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step 4 is continued;

[0118] Step 4: Determine whether the next time step is completed;

[0119] If the next time step has not ended, set k = k + 1 and return to step 3;

[0120] If the next time step ends, return to step 1.

[0121] Other steps and parameters are the same as those in the ninth embodiment.

[0122] Example

[0123] In the IoT, sensors and devices collect vast amounts of data on machine status, production line operations, temperature, humidity, and vibration. These data, such as device status data (each device reports status information every minute, including temperature, vibration, and energy consumption), and environmental monitoring data (sensors record ambient temperature, humidity, and air pressure every five seconds), are large in volume, frequently updated, and possess time-series characteristics, placing high demands on storage and scheduling. This data is stored in a database, and when device status anomalies require detection, the database data must be queried and accessed.

[0124] Taking device status data as an example, the data partitioning scheme for device status data query is as follows:

[0125] Step 1: The database stores device status data. First, collect information about each table in the database, historical query workload information, and the data partitioning scheme corresponding to the historical query workload information; wherein:

[0126] The information of each table in the database includes the size of the table, the number of tuples, the number of attributes, and the size of each attribute.

[0127] Historical query workload information includes each query statement and the execution time of each query statement.

[0128] The data partitioning scheme is the partitioning result of each table in the database.

[0129] Step 2: Model the data partitioning process as a Markov decision process. Use the database table information and historical query workload information as the state of the reinforcement learning model (DDQN) agent. Use the table information and historical query workload information collected in step 1 to train the reinforcement learning model agent. Use the data partitioning scheme corresponding to the historical query workload information as the label for agent training.

[0130] The agent is a deep neural network that fits the Q function. The Q function describes the expected total reward from performing action a in a certain state s, thereby selecting the optimal action for the current state. In each round of training, the agent selects an action. The reward function generates a reward for each action, which in turn generates a corresponding loss value, allowing the agent to optimize its parameters. The trained agent can be used to generate a data partitioning scheme.

[0131] The action of the agent is: after the query statement is entered, the agent takes the following actions on the current state: add rows, add columns, delete rows, delete columns, merge partitions, split partitions, or delete partitions.

[0132] The reward the agent receives after performing an action is:

[0133]

[0134] Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost t The cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 It is the size of the partition where the query result is located after the query statement is entered under the data partitioning scheme at time t-1.

[0135] Step 3: Establish a query workload prediction model and train the established query workload prediction model using historical query workload information;

[0136] The query workload prediction model includes the DLinear model and the Transformer model. The training process of the query workload prediction model is as follows:

[0137] Step 3.1. Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps;

[0138] For example, the time from 0 to T is the first time step, the time from T to 2T is the second time step, and so on;

[0139] Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained;

[0140] That is, each time step corresponds to I values, and each value is the number of times each type of historical query workload appears in a time step. For a type of query workload, the number of times this type of query workload appears in each time step constitutes a set of time series data of this type of query workload changing with time steps.

[0141] Step 32: Cluster the time series data corresponding to various types of historical query workloads to obtain the time series data within each cluster. That is, cluster the time series data with similar change trends and data at each time point into one category.

[0142] For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster.

[0143] For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster;

[0144] Step 3: For the k-th cluster, the number of occurrences of the query workload corresponding to the 1st time step to the p-th time step on the fitted time series data corresponding to the k-th cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1-th time step on the fitted time series data corresponding to the k-th cluster is used as the label of the training sample;

[0145] Where k = 1, 2, ..., K, K is the total number of clustering categories;

[0146] It should be noted that the value of p can be set according to actual conditions. For example, when p = 10, the number of occurrences of the query workload corresponding to the 1st time step to the 10th time step can be used as a training sample, and the number of occurrences of the query workload corresponding to the 11th time step can be used as a label. Then, using a sliding window method, the number of occurrences of the query workload corresponding to the 2nd time step to the 11th time step can be used as a training sample, and the number of occurrences of the query workload corresponding to the 12th time step can be used as a label; and so on.

[0147] Step 34: train the query workload prediction model using all the training samples obtained in step 33;

[0148] In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result;

[0149] The loss is then calculated based on the prediction results and labels, and training is stopped when the loss converges to obtain a trained query workload prediction model.

[0150] Step 4: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps.

[0151] The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'.

[0152] Step 5: Initialize k=1;

[0153] Step 6: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme;

[0154] If they are the same, use the current data partitioning scheme to query directly and then execute step 7;

[0155] If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step seven is continued;

[0156] If the types of query workloads that have arrived previously are all the same as query workload type i', there is no need to update the current data partitioning scheme for the time being. The data partitioning scheme will be updated only when a different type of workload arrives. The workload type corresponding to the updated data partitioning scheme will be compared with the newly arrived workload type. If they are consistent, there is no need to update the data partitioning scheme this time. Otherwise, the data partitioning scheme will continue to be updated.

[0157] Step 7: Determine whether the next time step is completed;

[0158] If the next time step has not ended, set k = k + 1 and return to step 6;

[0159] If the next time step ends, the latest time step is used as the current time step and the process returns to step 4.

[0160] The above examples are merely illustrative of the calculation model and process of the present invention and are not intended to limit the embodiments of the present invention. Persons skilled in the art will readily appreciate that other variations or modifications based on the above description are possible. This list of embodiments is not exhaustive; however, any obvious variations or modifications derived from the technical solution of the present invention remain within the scope of protection of the present invention.

Claims

1. A database adaptive hybrid data partitioning method based on reinforcement learning, characterized in that: The method specifically comprises the following steps: Step 1: Collect information about each table in the database, historical query workload information, and the data partitioning scheme corresponding to the historical query workload information; Step 2: Use the table information and historical query workload information in the database as the state of the reinforcement learning model agent. Use the table information and historical query workload information collected in step 1 to train the reinforcement learning model agent, and use the data partitioning scheme corresponding to the historical query workload information as the label for the agent training; Step 3: Establish a query workload prediction model and train the established query workload prediction model using historical query workload information; Step 4: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps. The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'. Step 5: Initialize k=1; Step 6: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme; If they are the same, use the current data partitioning scheme to query directly and then execute step 7; If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step seven is continued; Step 7: Determine whether the next time step is completed; If the next time step has not ended, set k = k + 1 and return to step 6; If the next time step ends, return to step 4.

2. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 1, characterized in that: The information of each table in the database includes the size of the table, the number of tuples, the number of attributes and the size of each attribute.

3. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 2, characterized in that: The historical query workload information includes each query statement and the execution time of each query statement.

4. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 3, characterized in that: The data partitioning scheme is the partitioning result of each table in the database.

5. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 4, characterized in that: The actions of the agent are: after the query statement is input, the agent takes the operation of adding rows, adding columns, deleting rows, deleting columns, merging partitions, splitting partitions or deleting partitions on the current state.

6. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 5, characterized in that: The reward obtained by the agent after performing an action in the reinforcement learning model is: Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost t The cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 It is the size of the partition where the query result is located after the query statement is entered under the data partitioning scheme at time t-1.

7. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 6, characterized in that: The query workload prediction model includes a DLinear model and a Transformer model.

8. The method for adaptive hybrid data partitioning of a database based on reinforcement learning according to claim 7, characterized in that: The specific process of step three is: Step 3.

1. Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps; Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained; Step 32: Cluster the time series data corresponding to various types of historical query workloads and obtain the time series data within each cluster; For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster. For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster; Step 3: For the k-th cluster, the number of occurrences of the query workload corresponding to the 1st time step to the p-th time step on the fitted time series data corresponding to the k-th cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1-th time step on the fitted time series data corresponding to the k-th cluster is used as the label of the training sample; Where k = 1, 2, ..., K, K is the total number of clustering categories; Step 34: train the query workload prediction model using all the training samples obtained in step 33; In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result; The loss is then calculated based on the prediction results and labels, and training is stopped when the loss converges to obtain a trained query workload prediction model.

9. A database adaptive hybrid data partitioning system based on reinforcement learning, characterized in that: The system includes an information collection module, a reinforcement learning model, a query workload prediction model, and a data partitioning module, wherein: The information collection module is used to collect information of each table in the database, historical query workload information and data partitioning scheme corresponding to the historical query workload information; The historical query workload information includes each query statement and the execution time of each query statement; The reinforcement learning model is trained based on the information collected by the information collection module, and the trained agent of the reinforcement learning model is used to output a data partitioning scheme; The agent's actions are: after entering a query statement, the agent takes the following actions on the current state: add rows, add columns, delete rows, delete columns, merge partitions, split partitions, or delete partitions; the reward the agent receives after performing the action is: Among them, reward t is the reward obtained by the agent after executing the action at the current time t; cost t The cost is the size of the partition where the query result is located after entering the query statement under the data partitioning scheme at the current time t; t-1 The size of the partition where the query result is located after entering the query statement under the data partitioning scheme at time t-1; The query workload prediction model is trained based on the historical query workload information collected by the information collection module, and the trained query workload prediction model is used to predict the future frequency of various types of query workloads; The working process of the query workload prediction model is as follows: Step 1: Set the time step T, and then count the number of times each type of historical query workload appears in each time step. The number of times the i-th type of historical query workload appears in the j-th time step is recorded as a ij , i=1,2,…,I,j=1,2,…,J, I is the number of types of historical query workloads, J is the total number of time steps; Then, for each type of historical query workload, the time series data of the number of occurrences of the query workload changing with the time step is obtained; Step 2: Cluster the time series data corresponding to various types of historical query workloads and obtain the time series data within each cluster; For a cluster that contains only one set of time series data, there is no need to process the time series data within the cluster. That is, the time series data within the cluster is directly used as the fitting time series data of the cluster. For clusters containing two or more groups of time series data, each group of time series data in a cluster is fitted into a group of time series data, and the time series data obtained by fitting is used as the fitted time series data of the cluster; Step 3: For the kth cluster, the number of occurrences of the query workload corresponding to the 1st time step to the pth time step on the fitted time series data corresponding to the kth cluster is used as a training sample, and the number of occurrences of the query workload corresponding to the p+1th time step on the fitted time series data corresponding to the kth cluster is used as the label of the training sample; Where k = 1, 2, ..., K, K is the total number of clustering categories; Step 4: Use all the training samples obtained in step 3 to train the query workload prediction model; In the query workload prediction model, the training samples are passed through the DLinear model and the Transformer model respectively, and then the output of the DLinear model and the output of the Transformer model are fused, and the fusion result is used as the query workload prediction result; The loss is then calculated based on the prediction results and labels. Training is stopped when the loss converges, and a trained query workload prediction model is obtained. The data partitioning module is used to generate a data partitioning scheme based on the prediction results of the query workload prediction model and the intelligent agent of the trained reinforcement learning model.

10. The database adaptive hybrid data partitioning system based on reinforcement learning according to claim 9, characterized in that: The working process of the data partition module is as follows: Step 1: Based on the number of times each type of query workload appears in the current time step and the number of times each type of query workload appears in p-1 historical time steps, obtain time series data of the number of times each type of query workload appears as a function of time steps. The time series data corresponding to each type of query workload is then input into the trained query workload prediction model. The trained query workload prediction model outputs the predicted frequency of occurrence of each type of query workload in the next time step, and the query workload type i' corresponding to the predicted maximum frequency is obtained. At the start of the next time step, the data partitioning scheme is initialized to the data partitioning scheme corresponding to the query workload type i'. Step 2: Initialize k=1; Step 3: Determine whether the k-th query workload arriving in the next time step is of the same type as the query workload corresponding to the current data partitioning scheme; If they are the same, use the current data partitioning scheme to query directly and then execute step 4; If they are not the same, the kth query workload is input into the trained reinforcement learning model, and the query is performed again after the trained agent outputs the new data partitioning scheme, and then step 4 is continued; Step 4: Determine whether the next time step is completed; If the next time step has not ended, set k = k + 1 and return to step 3; If the next time step ends, return to step 1.

Images