A data shard recommendation method, device and server

Abstract

The application provides a data fragmentation recommendation method, device and server, comprising: obtaining at least one data table to be fragmented, randomly generating P kinds of non-repeating fragmentation schemes, and generating an initial sample library according to the P kinds of non-repeating fragmentation schemes; determining the initial sample library as a current sample library, and determining the fitness of each fragmentation scheme included in the current sample library according to a preset fitness function; selecting, crossing and mutating the samples in the current sample library, and updating the current sample library; continuing to determine the fitness of each fragmentation scheme included in the current sample library according to the preset fitness function until a preset termination condition is reached; and determining the fragmentation schemes corresponding to the first N fitnesses as recommended fragmentation schemes. The recommended fragmentation schemes can be quickly determined by using a genetic algorithm, which is efficient and has better use effect to a certain extent.

Description

TECHNICAL FIELD

[0001] The present application relates to a distributed database data sharding technology, in particular to a data sharding recommendation method and device and server. BACKGROUND

[0002] At present, a distributed database can generally split data into multiple shards and store them in the following two ways. One is to shard data according to built-in row numbers; the other is to shard data according to a column or multiple columns (i.e., "sharding columns").

[0003] In related technologies, in the way of sharding data according to sharding columns, the selection of sharding columns is generally manually selected according to business scenarios and experience.

[0004] However, when the number of tables and business scenarios are involved, the above-mentioned manual selection of sharding columns is inefficient, and the use effect of the selected sharding columns needs to be further improved. SUMMARY

[0005] The present application provides a data sharding recommendation method, device and server to solve the problem of inefficient manual selection of sharding columns in related technologies, and the use effect of the selected sharding columns needs to be further improved.

[0006] According to a first aspect of the present application, a data sharding recommendation method is provided, comprising:

[0007] Generating P kinds of non-repeating sharding schemes randomly according to at least one data table to be sharded, and generating an initial sample library according to the P kinds of non-repeating sharding schemes; the sharding scheme includes sharding column information of the at least one data table; wherein P is a positive integer;

[0008] Determining the initial sample library as a current sample library, and determining the fitness of each sharding scheme included in the current sample library according to a predetermined fitness function;

[0009] Selecting, crossing and mutating the samples in the current sample library, and updating the current sample library;

[0010] Continuing to determine the fitness of each sharding scheme included in the current sample library according to the predetermined fitness function until a predetermined termination condition is reached;

[0011] Sorting the fitnesses in order of size, and determining the sharding schemes corresponding to the first N fitnesses as recommended sharding schemes; wherein N is a positive integer.

[0012] In one possible implementation, determining the fitness of each sharding scheme included in the current sample library according to a preset fitness function includes:

[0013] Based on the sharding schemes included in the current sample library, at least one real business scenario is simulated to obtain structured query language summary information corresponding to each real business scenario;

[0014] Obtain the table data volume information and field feature information of the data table, and process the table data volume information, field feature information, and structured query language summary information of the data table according to a preset fitness function to obtain the fitness corresponding to the sharding scheme.

[0015] In one possible implementation, the structured query language summary information includes the number of times the structured query language was executed, the execution time of the structured query language, and the number of rows of data scanned when the structured query language was executed.

[0016] In one possible implementation, the table data volume information of the data table includes the number of rows in the data table.

[0017] In one possible implementation, the field feature information includes one or more combinations of the following:

[0018] Field type, field length, field duplication rate, and field index information.

[0019] In one possible implementation, the data sharding recommendation method further includes: performing catastrophic processing on samples in the current sample library after selection, crossover, and mutation processing.

[0020] In one possible implementation, the preset termination condition includes any of the following:

[0021] Preset number of iterations, preset number of catastrophes, preset number of invalid genesis.

[0022] In one possible implementation, the data sharding recommendation method further includes:

[0023] Based on the number of data tables to be sharded and the number of fields in each data table, determine the number T of all possible sharding schemes;

[0024] The P is determined based on the number T of all possible partitioning schemes; where T is a positive integer.

[0025] According to a second aspect of this application, a data fragmentation recommendation apparatus is provided, comprising:

[0026] A sample library generation unit is used to acquire and randomly generate P unique sharding schemes based on at least one data table to be sharded, and generate an initial sample library based on the P unique sharding schemes; the sharding scheme includes sharding column information of the at least one data table; where P is a positive integer;

[0027] The processing unit is used to determine the initial sample library as the current sample library, and to determine the fitness of each sharding scheme included in the current sample library according to a preset fitness function.

[0028] The processing unit is also used to select, cross-reference, and mutate samples in the current sample library, and update the current sample library.

[0029] The processing unit is further configured to continue executing the step of determining the fitness of each sharding scheme included in the current sample library according to a preset fitness function, until a preset termination condition is reached;

[0030] The recommendation unit is used to sort the fitness values ​​in order of size and determine the sharding schemes corresponding to the top N fitness values ​​as the recommended sharding schemes; where N is a positive integer.

[0031] According to a third aspect of this application, a server is provided, including a memory and a processor; wherein,

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

[0033] The processor is configured to read a computer program stored in the memory and execute the recommended method for data sharding as described in the first aspect, based on the computer program in the memory.

[0034] According to a fourth aspect of this application, a computer-readable storage medium is provided, wherein computer-executable instructions are stored therein, and when a processor executes the computer-executable instructions, the recommended method for data fragmentation as described in the first aspect is implemented.

[0035] According to a fifth aspect of this application, a computer program product is provided, including a computer program that, when executed by a processor, implements the recommended method for data sharding as described in the first aspect.

[0036] This application provides a data sharding recommendation method, apparatus, and server, comprising: acquiring and randomly generating P unique sharding schemes based on at least one data table to be sharded, and generating an initial sample library based on the P unique sharding schemes; the sharding scheme includes sharding column information of at least one data table; where P is a positive integer; determining the initial sample library as the current sample library, and determining the fitness corresponding to each sharding scheme included in the current sample library according to a preset fitness function; performing selection, crossover, and mutation processing on samples in the current sample library, and updating the current sample library; continuing to execute the step of determining the fitness corresponding to each sharding scheme included in the current sample library according to the preset fitness function, until a preset termination condition is reached; sorting each fitness in order of size, and determining the sharding schemes corresponding to the top N fitnesss as recommended sharding schemes; where N is a positive integer. The data sharding recommendation method, apparatus, and server provided in this solution can quickly determine recommended sharding schemes using a genetic algorithm, which is efficient and, to a certain extent, yields better performance of the recommended sharding schemes. Attached Figure Description

[0037] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0038] Figure 1 This is a flowchart illustrating a data fragmentation recommendation method as an exemplary embodiment of this application;

[0039] Figure 2 A flowchart illustrating a recommended method for data fragmentation, as shown in another exemplary embodiment of this application;

[0040] Figure 3 A structural diagram of a recommended device for data slicing, illustrating an exemplary embodiment of this application;

[0041] Figure 4 A structural diagram of a recommended apparatus for data fragmentation, as illustrated in another exemplary embodiment of this application;

[0042] Figure 5 This is a structural diagram of a server shown as an exemplary embodiment of this application. Detailed Implementation

[0043] Currently, distributed databases generally split data into multiple shards and store it in two ways: one is to shard the data based on built-in row numbers; the other is to shard the data based on one or more columns (i.e., "sharding columns").

[0044] In related technologies, when data is sharded using a sharding column approach, the selection of the sharding column is generally done manually based on the business scenario and experience.

[0045] However, when there are many tables and a large number of business scenarios, the above-mentioned method of manually selecting sharding columns is inefficient. Manually selecting sharding columns makes it difficult to adequately consider the overall sharding scheme. There may be situations where some tables have a good sharding scheme, while others may have a poor one. Furthermore, the probability of data table changes or additions is high in the later stages of business operations, and it is difficult to promptly correct the sharding scheme using manual selection. Therefore, the effectiveness of the selected sharding columns needs further improvement.

[0046] To address the aforementioned technical issues, the solution provided in this application can randomly generate P sharding schemes. Then, a genetic algorithm can be used to process the samples in the sample library generated by the P sharding schemes to quickly determine the recommended sharding scheme, which is highly efficient. Furthermore, it can well consider the overall integrity of the sharding scheme and can repeatedly calculate the overall sharding scheme when needed, resulting in a better performance of the recommended sharding scheme to a certain extent.

[0047] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0048] The technical solution of this application and how the technical solution of this application solves the above-mentioned technical problems are described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described again in some embodiments. The embodiments of this application will now be described with reference to the accompanying drawings.

[0049] Figure 1 This is a flowchart illustrating a recommended method for data sharding, as shown in an exemplary embodiment of this application.

[0050] like Figure 1 As shown, the recommended data sharding method provided in this embodiment includes:

[0051] Step 101: Obtain and randomly generate P unique sharding schemes based on at least one data table to be sharded. Each sharding scheme includes sharding column information of at least one data table; where P is a positive integer.

[0052] The method provided in this application can be executed by a server.

[0053] The server can obtain at least one data table for data sharding. Based on this data table, it can randomly generate P unique sharding schemes using a preset algorithm.

[0054] Each sharding scheme includes sharding column information for each data table in at least one of the data tables to be sharded.

[0055] Specifically, the number of sharding schemes for at least one data table to be sharded can be calculated in advance. The value of P can be less than or equal to this number. Where P is a positive integer.

[0056] Step 102: Generate an initial sample library based on P non-repeating sharding schemes; and determine the initial sample library as the current sample library.

[0057] Specifically, the P unique sharding schemes can be used to form an initial sample library. Each sharding scheme is a sample in the initial sample library. Then, this initial sample library can be used as the current sample library.

[0058] Iteratively execute step 103 until the preset termination condition is met.

[0059] Specifically, step 103 can be executed iteratively until a preset termination condition is reached. For example, the preset termination condition could be that the number of iterations reaches a preset number of iterations. For example, the preset number of iterations could be 1024.

[0060] Step 103: Determine the fitness of each partitioning scheme included in the current sample library according to the preset fitness function; then, perform selection, crossover and mutation processing on the samples in the current sample library, and update the current sample library.

[0061] Specifically, a preset fitness function can be obtained, and then this preset fitness function can be used to determine the fitness of each sharding scheme included in the current sample library. A higher fitness value indicates a better sharding scheme performance.

[0062] Then, the samples in the current sample library can be selected, crossovered, and mutated to obtain new samples (i.e., a new sharding scheme), and the current sample library can be updated using the new samples. Specifically, the total number of samples in the updated current sample library remains unchanged, still P samples.

[0063] Step 104: Sort the fitness values ​​in order of size and determine the sharding schemes corresponding to the top N fitness values ​​as the recommended sharding schemes; where N is a positive integer.

[0064] All fitnesss obtained during the iteration process can be sorted in order of size, and the sharding schemes corresponding to the top N fitnesss can be determined as the recommended sharding schemes.

[0065] Therefore, one of the recommended sharding schemes can be used as the sharding scheme for at least one data table in the distributed database that needs to be sharded.

[0066] Where N is an integer. For example, N can be 3.

[0067] The data sharding recommendation method provided in this application includes: acquiring and randomly generating P unique sharding schemes based on at least one data table to be sharded, and generating an initial sample library based on the P unique sharding schemes; the sharding scheme includes sharding column information of at least one data table; where P is a positive integer; determining the initial sample library as the current sample library, and determining the fitness corresponding to each sharding scheme included in the current sample library according to a preset fitness function; performing selection, crossover, and mutation processing on the samples in the current sample library, and updating the current sample library; continuing to execute the step of determining the fitness corresponding to each sharding scheme included in the current sample library according to the preset fitness function, until a preset termination condition is reached; sorting each fitness in order of size, and determining the sharding schemes corresponding to the first N fitnesss as recommended sharding schemes; where N is a positive integer. The method used in this application can randomly generate P sharding schemes, and then use a genetic algorithm to process the samples in the sample library generated by the P sharding schemes to quickly determine the recommended sharding scheme, which is highly efficient. It can also take into account the overall sharding scheme well, and can repeat the calculation of the overall sharding scheme when needed (e.g., when data tables are changed or added in the later stages of business), so that the recommended sharding scheme obtained to a certain extent has a better effect.

[0068] Figure 2 This is a flowchart illustrating a recommended method for data fragmentation, as shown in another exemplary embodiment of this application.

[0069] like Figure 2 As shown, the recommended data sharding method provided in this embodiment includes:

[0070] Step 201: Determine the number of all possible sharding schemes T based on the number of data tables to be sharded and the number of fields in each data table; determine P based on the number of all possible sharding schemes T; where T is a positive integer and P is a positive integer; obtain and randomly generate P non-repeating sharding schemes based on at least one data table to be sharded, wherein the sharding scheme includes sharding column information of at least one data table.

[0071] Specifically, the data tables to be sharded can include sharded tables and global tables.

[0072] For example, if there are 4 data tables (including one global table and three shard tables), and each data table has 2 fields, then the data for all possible sharding schemes is T = (2+1)*(2+1)*(2+1)*(2+1) = 81.

[0073] Specifically, for example, if T is greater than 50, then P can be set to 50; if T is less than or equal to 50, then P can be set to T.

[0074] Where P and T are both positive integers.

[0075] Then, obtain at least one data table for data sharding, and based on the at least one data table, use a preset algorithm to randomly generate P non-repeating sharding schemes.

[0076] Each sharding scheme includes sharding column information for each data table in at least one of the data tables to be sharded.

[0077] Optionally, hash algorithms or RANGE (specifying field range - distribution nodes) algorithms can be used to determine the sharding nodes for each data table.

[0078] Step 202: Generate an initial sample library based on P non-repeating sharding schemes; and determine the initial sample library as the current sample library.

[0079] Specifically, the implementation and principle of step 202 are similar to those of step 102, and will not be repeated here.

[0080] Specifically, a sharding scheme can be used as a genetic gene in a genetic algorithm. All the sharding column information corresponding to a data table is the phenotype of a genetic gene. The sharding column information of all data tables to be sharded can be combined to obtain a sharding scheme, which can then be used as the genetic gene in the algorithm.

[0081] Iteratively execute steps 203-205 until the preset termination condition is met.

[0082] In one possible implementation, the preset termination condition includes any of the following: a preset number of iterations, a preset number of catastrophic events, or a preset number of invalid inheritances.

[0083] Specifically, steps 203-205 can be executed iteratively until a preset termination condition is reached. For example, the preset termination condition could be reaching a preset number of iterations, such as 1024 iterations. Alternatively, it could be reaching a preset number of catastrophes, such as 5 catastrophes. Another preset termination condition could be reaching a preset number of invalid genes. Invalid genes refer to a fitness obtained in this iteration that is not improved compared to the fitness obtained in the previous iteration.

[0084] Step 203: Based on the sharding schemes included in the current sample library, simulate at least one real business scenario to obtain the structured query language summary information corresponding to each real business scenario.

[0085] Specifically, a simulated scenario can be built in advance, specifically a distributed database environment can be simulated, and the same database tables as the real environment can be created (including at least one data table to be sharded). Then, real business scenarios can be simulated in this simulated scenario.

[0086] Optionally, to improve data processing speed and quickly obtain a recommended sharding scheme, the data volume of at least one table in the data to be sharded can be reduced by a preset ratio. The simulated load test concurrency can also be reduced by this preset ratio.

[0087] A single data table can encompass multiple business scenarios, and a single business scenario may involve multiple data tables. Examples of business scenarios include student course selection, student medical treatment, and student club activities.

[0088] Specifically, for each sharding scheme included in the current sample library, at least one real business scenario can be simulated to obtain the structured query language summary information corresponding to each real business scenario under each sharding scheme.

[0089] In one possible implementation, the structured query language summary information includes the number of times the structured query language was executed, the execution time of the structured query language, and the number of rows of data scanned during the execution of the structured query language.

[0090] Specifically, the Structured Query Language (SQL) profile information may include one or more of the following combinations: the number of times the structured query language was executed, the execution time of the structured query language (i.e., the execution time of each execution of the structured query language), and the number of rows of data scanned when the structured query language was executed (i.e., the number of rows of data scanned each time the structured query language was executed).

[0091] Step 204: Obtain the table data volume information and field feature information of the data table, and process the table data volume information, field feature information, and structured query language summary information of the data table according to the preset fitness function to obtain the fitness corresponding to the sharding scheme.

[0092] Specifically, it is possible to obtain the table data volume information and field characteristic information of at least one data table to be sharded. The table data volume information refers to the amount of data contained in the table. The field characteristic information refers to the characteristic information of the fields contained in the table.

[0093] In one possible implementation, the table data volume information includes the number of rows in the table.

[0094] Specifically, the data volume information of a data table can include the number of rows in the data table.

[0095] In one possible implementation, the field feature information includes one or more combinations of the following:

[0096] Field type, field length, field duplication rate, and field index information.

[0097] Specifically, the field characteristics of a data table can include one or more of the following combinations: field type, field length, field repetition rate, and field index information. The field repetition rate refers to the percentage of times a field value is repeated in the data table; specifically, it can be represented by dividing the number of rows by the number of unique values ​​in the field. Field index information includes primary keys, unique keys, foreign keys, and others.

[0098] Then, the table data volume information, field feature information, and structured query language summary information of the data table can be processed according to the preset fitness function to obtain the fitness corresponding to the sharding scheme.

[0099] Specifically, the table data volume information and field feature information of the data table, as well as the structured query language summary information obtained from a simulated real business scenario under a certain sharding scheme, can be processed according to the preset fitness function to obtain the fitness corresponding to the real business scenario under the sharding scheme. Then, based on the fitness corresponding to all real business scenarios under the sharding scheme, the fitness corresponding to the sharding scheme can be determined.

[0100] For example, you can first obtain the scoring parameters of the execution plan.

[0101] Specifically, a score is preset for each execution plan, taking into account the table's data size and the number of executions. The higher the number of executions, the greater the weight. The score can be stored in an Extensible Markup Language (XML) file. For example, parameters are: full table scan -1 point, non-index scan 1 point, primary key range scan 10 points, primary key value scan 20 points, and scan where the primary key is also a sharded field 30 points (which can be pruned by partitioning the sharded field first). The actual score setting is still related to the execution efficiency and consumption of the current execution plan in the distributed database.

[0102] Then, obtain the structured query language summary information for each real business scenario.

[0103] Specifically, the structured query language summary information of the simulated stress test parameters is preprocessed to obtain key recommendation factors, which are stored in a MySQL database, allowing for quick retrieval of relevant information via a database connection.

[0104] Then, for each Structured Query Language summary, its execution plan is calculated for each sharding scheme.

[0105] Specifically, the SQL parser extracts the syntax tree and generates a complete execution plan. Based on the initialized sample database, it reads the structured query language summary information and execution plan scoring parameters for all real-world business scenarios. For each sharding scheme, it iterates through the structured query language summary information for all real-world business scenarios and analyzes the execution plan.

[0106] Then, the implementation plan is scored.

[0107] Specifically, the current execution plan score is calculated based on pre-set scoring parameters. Even with the same structured query language summary information in a real-world business scenario, the execution plan can be completely different under different sharding schemes, taking into account the specific indexes and data volume of the tables involved in the SQL query. This can result in varying execution efficiency and system resource consumption.

[0108] Then, summarize the total SQL scores for all business operations for each sharding scheme.

[0109] Specifically, based on the execution plan scoring parameters and the table data size, the score for each execution plan can be determined. Accumulating the scores of all business SQL queries yields the fitness of the sharding scheme, allowing the calculation of the fitness for all samples.

[0110] For example, SQL summary information may include the following:

[0111] 1. SQL summary: Retains table names, field names, and keyword information, replaces some constants in the original SQL, and omits some identical parts. For example, replaces character constants in the statement with 'S', replaces numeric constants with 'N', omits values ​​in the IN(val,val1,...) expression, and omits 'VALUES' in INSERT VALUES.

[0112] As in the original SQL: select*from student where id=123and name='zhang san'and agein(20,18,19,21,22);

[0113] The SQL sentence pattern is: select*from student where id=N and name='S'and age in(N);

[0114] 2. Number of SQL executions

[0115] Count the number of executions by grouping SQL statement types

[0116] 3. Number of rows inserted in SQL or number of rows returned in the result set.

[0117] Global table: No sharding, all data nodes have the entire table data.

[0118] Sharded table: A table is sharded based on a certain field, and each data node contains only a portion of the data from that table.

[0119] The samples in the sample library are a sharding scheme, which can be represented as follows: sharding column information of the first table, ..., sharding column information of the i-th table, ..., sharding column information of the N-th table.

[0120] The fitness function can be expressed as follows:

[0121] fit(x)=score(1)*ratio(1)+...+score(i)*ratio(i)+...+score(N)*ratio(N)

[0122] Here, it is assumed that there are N data tables in total, score(i) represents the score of the sharding scheme of the i-th table, and ratio(i) represents the score coefficient of the i-th table.

[0123] The ratio(i) is mainly calculated based on the number of rows (M) of the i-th table, the index definition, and the sharding scheme.

[0124] ratio(i)=ratio_row(i)*ratio_index(i)*ratio_shard_column(i).

[0125] Among them, the row rating coefficient ratio_row(i) is calculated based on the number of rows according to the sharding scheme.

[0126] For global tables:

[0127] If M < 100,000, then the value is 2;

[0128] If 1 million <= M <= 10 million, then the value is 0.5;

[0129] If M >= 10 million, then the value is 0.25;

[0130] For fragmented tables:

[0131] If M < 100,000, then the value is 0.25;

[0132] If 100,000 <= M <= 1,000,000, then the value is 0.5;

[0133] If M >= 10 million, then the value is 2;

[0134] The index rating coefficient ratio_index(i) is calculated based on the table's index definition.

[0135] For the global table: the initial value ratio_index(i) = 1;

[0136] For a sharded table: the initial value ratio_index(i) = 1;

[0137] If the table has a primary key: if the primary key contains a sharding field, ratio_index(i)* = 2; otherwise, ratio_index(i)* = 0.5.

[0138] If the table has a unique key: if the unique key contains a sharding field, ratio_index(i) * = 1.5; otherwise, ratio_index(i) * = 0.75.

[0139] If a foreign key exists: the referenced table is the j-th table; if table j is a global table, then ratio_index(j)* = 2; if both tables i and j are sharded tables, for the referenced table i, if the sharding field is exactly the referenced field, then ratio_index(i)* = 4; for the referenced table j, if the sharding field is exactly the referenced field, then ratio_index(j)* = 2.

[0140] The sharding field type rating coefficient ratio_shard_column(i) is calculated based on the sharding field type.

[0141] If it is a numeric type, then it can take the value 1.2;

[0142] If the character type has a length of less than 32, then the value can be 1;

[0143] If the character type is 32 < length < 256, then the value can be 0.8;

[0144] If the character type has a length greater than 256, then the value can be 0.5;

[0145] If it is a date type, the value can be 0.1.

[0146] The score(i) is calculated based on all the SQL summary information related to this table, combined with the sharding scheme.

[0147] Initially, score(i) = 0. If there are L SQL summary records related to table i, loop through all SQL summary records.

[0148] For the Kth summary information, if the number of executions is c(K), and the number of rows inserted or returned in the result set is r(K), calculate the SQL execution plan for the SQL under this sharding scheme based on the SQL summary (the execution plan for the same SQL may be different under different sharding schemes), and score it based on the quality of the execution plan (the quality of the execution plan is determined by the resource consumption and execution time). Assuming the score is p(K), then score(i) = p(1)*c(1)*r(1) + ... + p(k)*c(k)*r(k) + ... + p(L)*c(L)*r(L).

[0149] Example of execution plan score p(k):

[0150] For a single-node table scan, the value is 1; otherwise, for a multi-node scan, the value is -1.

[0151] For joins involving multiple tables, if it is a cross-database join (for large tables with few filtering conditions, it may be necessary to scan a large amount of data from the data nodes to the distributed computing node layer before performing the join operation, which consumes a lot of network I / O, disk I / O, memory, CPU and other resources of the computing node server), then the value is -100, otherwise it is 100.

[0152] If it contains a subquery, the value is -100 if it is a cross-database subquery, and 100 otherwise.

[0153] This application is not limited to the rules mentioned above; there are many other similar rules that are not listed here.

[0154] For ratio(i), all constant values ​​in score(i) can be used as preset parameters.

[0155] Step 205: Select, crossover, and mutate the samples in the current sample library, and update the current sample library.

[0156] Specifically, the samples in the current sample library (i.e., the sharding scheme) can be selected, crossovered, and mutated to obtain new samples (i.e., a new sharding scheme), and the current sample library can be updated using the new samples. The total number of samples in the updated sample library remains unchanged, still P samples.

[0157] This involves sorting all the partitioning schemes in the population by fitness and randomly selecting two samples based on a specific selection algorithm. Common selection algorithms include Fitness Selection (the probability of an individual being selected in the population is proportional to the value of the individual's corresponding fitness function) and Rank Selection (the probability of an individual being selected in the population is proportional to the sequence value of the individual's corresponding fitness ranking).

[0158] Among these options, there is a certain probability that two selected samples will be cross-referenced. The sharding schemes of one or more tables from the two samples can be swapped.

[0159] The crossover probability, as a preset parameter, can also be adaptively adjusted based on the fitness of the sample library. For example, if the average fitness of the sample library differs significantly from the historical best fitness, we should increase the crossover probability.

[0160] In one possible approach, after selecting, crossing, and mutating samples in the current sample library, a catastrophe process is performed.

[0161] Specifically, if the preset conditions are met, after selecting, cross-referencing, and mutating the samples in the current sample library, disaster recovery can also be performed.

[0162] For example, the preset condition could be that after M iterations, the fitness has not improved. M is a positive integer, for example, M could be 3.

[0163] Specifically, the fitness scores obtained in each iteration can be sorted in order of magnitude, and the sharding schemes corresponding to the top A fitness scores can be saved to generate a historical excellent sample library. Optionally, before saving a sharding scheme to the historical excellent sample library, the similarity between the sharding scheme and the samples in the historical excellent sample library can be calculated. If the similarity meets the requirement (for example, similarity less than 50%), it is saved to the historical excellent sample library; if the requirement does not meet the requirement (for example, similarity greater than or equal to 50%), it is discarded. Here, A is a positive integer, for example, A is 3.

[0164] Optionally, it can simultaneously statistically analyze and record a few of the least efficient business SQL queries for each sharding scheme in a historical database of excellent samples. Database administrators can then use this recorded information to correct input parameters.

[0165] Specifically, during disaster recovery, B sharding schemes can be randomly selected from a historical best-practice sample library. These B sharding schemes will then randomly replace the B best-practice sharding schemes in the current sample library. If the selected historical best-practice schemes are too similar to the schemes in the current sample library that need to be replaced, a new scheme will be randomly generated to replace them, thus obtaining an updated current sample library.

[0166] Step 206: Sort the fitness values ​​in order of size and determine the sharding schemes corresponding to the top N fitness values ​​as the recommended sharding schemes; where N is a positive integer.

[0167] All fitnesss obtained during the iteration process can be sorted in order of size, and the sharding schemes corresponding to the top N fitnesss can be determined as the recommended sharding schemes.

[0168] Alternatively, the N best sharding schemes from a historical database of excellent samples can be selected as the recommended sharding schemes.

[0169] Therefore, one of the recommended sharding schemes can be used as the sharding scheme for at least one data table in the distributed database that needs to be sharded.

[0170] Figure 3 This is a structural diagram of a recommended device for data sharding, illustrating an exemplary embodiment of this application.

[0171] like Figure 3 As shown, the recommended data fragmentation device 300 provided in this application includes:

[0172] The sample library generation unit 310 is used to acquire and randomly generate P unique sharding schemes based on at least one data table to be sharded, and generate an initial sample library based on the P unique sharding schemes; the sharding scheme includes sharding column information of at least one data table; where P is a positive integer;

[0173] The processing unit 320 is used to determine the initial sample library as the current sample library, and to determine the fitness of each sharding scheme included in the current sample library according to the preset fitness function.

[0174] The processing unit 320 is also used to select, crossover, and mutate samples in the current sample library, and update the current sample library;

[0175] The processing unit 320 is also used to continue executing the step of determining the fitness of each sharding scheme included in the current sample library according to the preset fitness function, until the preset termination condition is reached.

[0176] Recommendation unit 330 is used to sort the fitness values ​​in order of size and determine the sharding schemes corresponding to the top N fitness values ​​as the recommended sharding schemes; where N is a positive integer.

[0177] Figure 4 This is a structural diagram of a recommended apparatus for data slicing, illustrating another exemplary embodiment of this application.

[0178] like Figure 4 As shown, based on the above embodiments, the data sharding recommendation device 400 provided in this application further includes a processing unit 320 that includes:

[0179] The simulation module 321 is used to simulate at least one real business scenario based on the sharding scheme included in the current sample library, and obtain the structured query language summary information corresponding to each real business scenario.

[0180] The fitness determination module 322 is used to obtain the table data volume information and field feature information of the data table, and process the table data volume information, field feature information and structured query language summary information of the data table according to the preset fitness function to obtain the fitness corresponding to the sharding scheme.

[0181] In one possible implementation, the structured query language summary information includes the number of times the structured query language was executed, the execution time of the structured query language, and the number of rows of data scanned during the execution of the structured query language.

[0182] In one possible implementation, the table data volume information includes the number of rows in the table.

[0183] In one possible implementation, the field feature information includes one or more combinations of the following:

[0184] Field type, field length, field duplication rate, and field index information.

[0185] The processing unit 320 is also used to perform catastrophic processing after selecting, cross-referencing, and mutating samples in the current sample library.

[0186] In one possible implementation, the preset termination condition includes any of the following:

[0187] Preset number of iterations, preset number of catastrophes, preset number of invalid genesis.

[0188] The sample library generation unit 310 is specifically used to determine the number T of all possible sharding schemes based on the number of data tables to be sharded and the number of fields in each data table;

[0189] Determine P based on the number T of all possible partitioning schemes; where T is a positive integer.

[0190] Figure 5 This is a structural diagram of a server shown as an exemplary embodiment of this application.

[0191] like Figure 5 As shown, the server provided in this embodiment includes:

[0192] Memory 501;

[0193] Processor 502; and

[0194] Computer programs;

[0195] The computer program is stored in memory 501 and configured to be executed by processor 502 to implement any of the recommended methods for data sharding as described above.

[0196] This embodiment also provides a computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement any of the recommended methods for data sharding as described above.

[0197] This embodiment also provides a computer program product, including a computer program that, when executed by a processor, implements any of the recommended methods for data sharding described above.

[0198] Those skilled in the art will understand that all or part of the steps of the above-described method embodiments can be implemented by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When executed, the program performs the steps of the above-described method embodiments; and the aforementioned storage medium includes various media capable of storing program code, such as ROM, RAM, magnetic disks, or optical disks.

[0199] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data sharding recommendation method, characterized in that, include: Obtain and randomly generate P unique sharding schemes based on at least one data table to be sharded, and generate an initial sample library based on the P unique sharding schemes. The sharding scheme includes sharding column information for at least one data table; where P is a positive integer; The initial sample library is determined as the current sample library, and the fitness of each sharding scheme included in the current sample library is determined according to the preset fitness function. The samples in the current sample library are selected, cross-referenced, and mutated, and the current sample library is updated. Continue executing the step of determining the fitness of each sharding scheme included in the current sample library according to the preset fitness function, until the preset termination condition is reached; The fitness values ​​are sorted in order of size, and the sharding schemes corresponding to the top N fitness values ​​are determined as the recommended sharding schemes; where N is a positive integer. The step of determining the fitness of each sharding scheme included in the current sample library according to a preset fitness function includes: Based on the sharding schemes included in the current sample library, at least one real business scenario is simulated to obtain the structured query language summary information corresponding to each real business scenario. The structured query language summary information includes the number of times the structured query language is executed, the execution time of executing the structured query language, and the number of rows of data scanned when executing the structured query language. Obtain the table data volume information and field feature information of the data table, and process the table data volume information, field feature information, and structured query language summary information of the data table according to a preset fitness function to obtain the fitness corresponding to the sharding scheme.

2. The method according to claim 1, characterized in that, The table data volume information of the data table includes the number of rows in the data table.

3. The method according to claim 1, characterized in that, The field feature information includes one or more combinations of the following: Field type, field length, field duplication rate, and field index information.

4. The method according to any one of claims 1-3, characterized in that, Also includes: After selecting, cross-referencing, and mutating the samples in the current sample library, a catastrophe process is performed.

5. The method according to any one of claims 1-3, characterized in that, The preset termination condition includes any of the following: Preset number of iterations, preset number of catastrophes, preset number of invalid genesis.

6. A data sharding recommendation device, characterized in that, include: The sample library generation unit is used to acquire and randomly generate P non-repeating sharding schemes based on at least one data table to be sharded, and generate an initial sample library based on the P non-repeating sharding schemes. The sharding scheme includes sharding column information for at least one data table; where P is a positive integer; The processing unit is configured to determine the initial sample library as the current sample library, and determine the fitness of each sharding scheme included in the current sample library according to a preset fitness function, including: Based on the sharding schemes included in the current sample library, at least one real business scenario is simulated to obtain the structured query language summary information corresponding to each real business scenario. The structured query language summary information includes the number of times the structured query language is executed, the execution time of executing the structured query language, and the number of rows of data scanned when executing the structured query language. Obtain the table data volume information and field feature information of the data table, and process the table data volume information and field feature information of the data table, as well as the structured query language summary information, according to the preset fitness function to obtain the fitness corresponding to the sharding scheme; The processing unit is also used to select, cross-reference, and mutate samples in the current sample library, and update the current sample library. The processing unit is further configured to continue executing the step of determining the fitness of each sharding scheme included in the current sample library according to a preset fitness function, until a preset termination condition is reached; The recommendation unit is used to sort the fitness values ​​in order of size and determine the sharding schemes corresponding to the top N fitness values ​​as the recommended sharding schemes; where N is a positive integer.

7. A server, characterized in that, Includes memory and processor; among which, The memory is used to store computer programs; The processor is configured to read the computer program stored in the memory and execute the method described in any one of claims 1-5 according to the computer program in the memory.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer-executable instructions, which, when executed by a processor, implement the method described in any one of claims 1-5.

Images