Method, device and electronic device for determining distribution key in distributed database

Abstract

The present invention provides a method, device and electronic device for determining a distribution key in a distributed database, which can be applied to the field of distributed database technology. The method includes: parsing multiple initial structured query statements from a code file of a distributed database; filtering multiple structured query statements of preset statement types from the multiple initial structured query statements without executing the multiple initial structured query statements; determining the redistribution cost of multiple candidate keys related to each structured query statement according to the query tree of each structured query statement, the structured query statement includes at least one database table, and each database table includes at least one candidate key; summarizing the redistribution costs of the same candidate keys of the same database table in the multiple structured query statements to obtain the total redistribution cost of each candidate key in each database table; taking the candidate key with the highest total redistribution cost in each database table as the distribution key of the database table.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed databases, and in particular to a method, device and electronic device for determining a distribution key in a distributed database. Background Art

[0002] In a distributed database, the distribution key setting of the database table is particularly critical. If it is not set properly, it will cause frequent data exchange between data nodes when executing business statements, resulting in increased system network load and decreased database performance. Among them, business statements can understand business SQL written in Structured Query Language (SQL).

[0003] At present, the solution to determine the optimal distribution key from each database table depends on the database implementation. For example, first build a database cluster, then input the business SQL to be analyzed into the database for execution. After a single business SQL is executed, the next business SQL is executed until all businesses are executed. When executing each business SQL, the analysis operation of the business SQL is injected into the SQL execution process through a plug-in. Therefore, after each business SQL is executed, the collection of distribution key information is also completed synchronously. After executing and collecting all business SQLs, the collected distribution key information is calculated to obtain the optimal distribution key.

[0004] However, the above distribution key determination scheme has the problems of cumbersome deployment, complex operation and low efficiency. On the one hand, the scheme needs to deploy a database system, which increases the difficulty of the pre-work of information collection. On the other hand, since the database system itself has its own internal processing logic, the business SQL to be executed needs to be a complete set of SQL, including the table creation SQL, query SQL, update SQL, etc. of the database table. For example, when querying a database table, the table must first exist, that is, the table creation SQL needs to be executed first, otherwise the query will report an error, which will lead to the inability to perform distribution key analysis. For the distribution key analysis process, the query SQL in the full set of SQL is meaningful. By counting the number of occurrences and positions of the table connection fields in the query SQL, it can be known which field is more reasonable as the distribution key. However, the table creation SQL in the full set of SQL is meaningless for distribution key analysis. However, the distribution key determination scheme relies on the database environment to run SQL and analyze SQL. In order to ensure the logical operation of the database, the table creation SQL cannot be avoided, which leads to an increase in the amount of invalid work in the whole process.

[0005] In addition, since the analysis of each business SQL in the above distribution key determination solution depends on the database environment, each business SQL analysis needs to go through the parser, semantic analysis, rewriting, distribution key analysis, optimizer, executor and other modules. However, the optimizer and executor work is not necessary for the information collection stage. These two steps are also the most time-consuming, accounting for most of the entire SQL processing time, which leads to low processing efficiency of a single SQL.

[0006] In summary, the current distribution key determination scheme has a strong dependence on the database environment, and has problems such as cumbersome deployment, complex operation, and low efficiency. Summary of the invention

[0007] In view of the above problems, the present invention provides a method for determining a distribution key in a distributed database.

[0008] According to a first aspect of the present invention, a method for determining a distribution key in a distributed database is provided, comprising: parsing a plurality of initial structured query statements from a coding file written when developing a distributed database; screening a plurality of structured query statements belonging to a preset statement type from the plurality of initial structured query statements without executing the plurality of initial structured query statements; determining, according to a query tree of each structured query statement, respective redistribution costs of a plurality of candidate keys associated with each structured query statement, wherein the structured query statement includes at least one database table, and each database table includes at least one candidate key; the redistribution cost is determined based on at least the number of occurrences of the candidate key in the structured query statement; summarizing the redistribution costs of the same candidate key of the same database table in the plurality of structured query statements to obtain a total redistribution cost of each candidate key in each database table; and using the candidate key with the highest total redistribution cost in each database table as the distribution key of the database table.

[0009] According to an embodiment of the present invention, based on the query tree of each structured query statement, the redistribution cost of each of the multiple candidate keys related to each structured query statement is determined, including: for each structured query statement, the number of occurrences of each candidate key in the query tree is used as the redistribution cost of each candidate key; or for each structured query statement, the redistribution cost of each candidate key is determined according to the position and number of occurrences of each candidate key in the query tree.

[0010] According to an embodiment of the present invention, the redistribution cost of each candidate key is determined according to the position and number of occurrences of each candidate key in the query tree, including: determining the position weight corresponding to the position according to the position of each candidate key in the query tree; and determining the redistribution cost of each candidate key according to the position weight and number of occurrences.

[0011] According to an embodiment of the present invention, the method further includes: for each structured query statement, caching the redistribution cost of each candidate key in each database table into a hash table; wherein the hash table includes multiple cache records, each cache record is associated with a candidate key in a database table, and the cache structure of the cache record includes a namespace, a database table name, an attribute name of the candidate key, and a redistribution cost.

[0012] According to an embodiment of the present invention, the redistribution costs of the same candidate keys of the same database table in multiple structured query statements are summarized to obtain the total redistribution cost of each candidate key in each database table, including: traversing the hash table, summarizing the redistribution costs of the same candidate keys of the same database table in multiple structured query statements, and obtaining the total redistribution cost of each candidate key in each database table.

[0013] According to an embodiment of the present invention, the statement type of the initial structured query statement includes an operation type and a definition type; without executing multiple initial structured query statements, multiple structured query statements of a preset statement type are determined from the multiple initial structured query statements, including: without executing multiple initial structured query statements, initial structured statements of an operation type are filtered out from the multiple initial structured query statements, and the initial structured statements of the operation type are determined as structured query statements.

[0014] According to an embodiment of the present invention, the method further includes: generating a query tree for each structured query statement in a plurality of structured query statements through a parser, semantic analysis, and rewriting operations; generating a query tree for each structured query statement in a plurality of structured query statements through a parser, semantic analysis, and rewriting operations, including: parsing the structured query statement through a parser to obtain a syntax tree; performing semantic analysis on the syntax tree and generating a query tree through a rewriting operation.

[0015] A second aspect of the present invention provides a device for determining a distribution key in a distributed database, characterized in that the device includes: a reading module, used to parse multiple initial structured query statements from a coding file written when developing a distributed database; a determination module, used to filter out multiple structured query statements belonging to a preset statement type from the multiple initial structured query statements without executing the multiple initial structured query statements; a cost calculation module, used to determine the redistribution cost of each of the multiple candidate keys related to each structured query statement according to the query tree of each structured query statement, wherein the structured query statement includes at least one database table, each database table includes at least one candidate key, and the redistribution cost is determined based on at least the number of occurrences of the candidate key in the structured query statement; a summary module, used to summarize the redistribution costs of the same candidate key of the same database table in the multiple structured query statements to obtain the total redistribution cost of each candidate key in each database table; and a distribution key determination module, used to use the candidate key with the highest total redistribution cost in each database table as the distribution key of the database table.

[0016] The third aspect of the present invention provides an electronic device, comprising: one or more processors; a memory for storing one or more computer programs, which, when the one or more processors execute the one or more computer programs, implements the steps of the method for determining the distribution key in the above-mentioned distributed database.

[0017] The fourth aspect of the present invention also provides a computer-readable storage medium on which a computer program or instruction is stored. When the computer program or instruction is executed by a processor, the steps of the method for determining a distribution key in the above-mentioned distributed database are implemented.

[0018] The fifth aspect of the present invention also provides a computer program product, including a computer program or instructions, which, when executed by a processor, implements the steps of the method for determining a distribution key in the above-mentioned distributed database.

[0019] The technical effects of the method for determining the distribution key in a distributed database of the present invention are embodied in the following aspects: 1) the distribution key information collection process is decoupled from the database, eliminating the need for pre-deployment work for information collection. 2) static analysis of business SQL statements, the distribution key analysis phase does not require the participation of time-consuming operations such as optimizers and executors, and the analysis efficiency of a single SQL statement is greatly improved. 3) only business SQL statements related to the distribution key are processed, and irrelevant SQL statements are automatically filtered, reducing the overall workload of information collection.

[0020] In summary, the present invention performs distribution key analysis through an independent analysis tool, without the need to deploy the database in advance, thereby achieving decoupling from the database. In addition, when obtaining the distribution key information, each SQL statement does not need to pass through the optimizer and executor modules, which reduces the complexity of the operation and improves the efficiency of determining the distribution key. In addition, the present invention not only does not need to execute each business SQL statement, but can also determine the optimal distribution key in each database table, reducing the redistribution operation between database nodes, reducing the consumption of database resources, and improving the overall operation efficiency of the database. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] The above contents and other objects, features and advantages of the present invention will become more apparent through the following description of the embodiments of the present invention with reference to the accompanying drawings, in which:

[0022] Figure 1 The application scenario of the method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0023] Figure 2 A flow chart of a method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0024] Figure 3 A flow chart of obtaining distribution key information according to an embodiment of the present invention is shown.

[0025] Figure 4 A distribution key analysis flow chart according to an embodiment of the present invention is shown.

[0026] Figure 5 A flow chart of optimal distribution key calculation according to an embodiment of the present invention is shown.

[0027] Figure 6 A structural block diagram of a device for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0028] Figure 7 A block diagram of an electronic device suitable for a method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown. DETAILED DESCRIPTION

[0029] Below, embodiments of the present invention will be described with reference to the accompanying drawings. However, it should be understood that these descriptions are exemplary only and are not intended to limit the scope of the present invention. In the following detailed description, for ease of explanation, many specific details are set forth to provide a comprehensive understanding of embodiments of the present invention. However, it is apparent that one or more embodiments may also be implemented without these specific details. In addition, in the following description, descriptions of known structures and technologies are omitted to avoid unnecessary confusion of concepts of the present invention.

[0030] The terms used herein are only for describing specific embodiments and are not intended to limit the present invention. The terms "comprise", "include", etc. used herein indicate the existence of the features, steps, operations and / or components, but do not exclude the existence or addition of one or more other features, steps, operations or components.

[0031] All terms (including technical and scientific terms) used herein have the meanings commonly understood by those skilled in the art unless otherwise defined. It should be noted that the terms used herein should be interpreted as having a meaning consistent with the context of this specification and should not be interpreted in an idealized or overly rigid manner.

[0032] When using expressions such as "at least one of A, B, and C, etc.", they should generally be interpreted according to the meaning of the expression commonly understood by those skilled in the art (for example, "a system having at least one of A, B, and C" should include but is not limited to a system having A alone, B alone, C alone, A and B, A and C, B and C, and / or A, B, C, etc.).

[0033] In a distributed database, a database table is simply called a table. When two tables are queried in a relational manner, as long as the relational field of one of the tables is not a distributed field, a redistribution operation of the database will be triggered. Data needs to be moved between nodes to meet the relational conditions. This is data redistribution, or "redistribution" for short.

[0034] When performing association queries on different database tables, the fields used for association are all potential distribution key fields, called "candidate keys".

[0035] Redistribution will increase the network load between nodes and reduce the overall efficiency of the system. Although redistribution itself is difficult to avoid, it can be minimized, such as by setting a reasonable distribution key for each database table, so that related queries can be completed within the node as much as possible, reducing redistribution operations, and thus greatly improving system efficiency.

[0036] In order to at least partially solve the technical problems that the efficiency of determining a distribution key in a distributed database is low, the distribution key determination requires the deployment of a database in advance, the deployment is cumbersome, and the operation is complicated, an embodiment of the present invention provides a method for determining a distribution key in a distributed database, comprising: parsing a plurality of initial structured query statements from a coding file written when developing a distributed database; screening a plurality of structured query statements belonging to a preset statement type from the plurality of initial structured query statements without executing the plurality of initial structured query statements; determining, according to a query tree of each structured query statement, respective redistribution costs of a plurality of candidate keys related to each structured query statement, wherein the structured query statement includes at least one database table, each database table includes at least one candidate key, and the redistribution cost is determined based on at least the number of occurrences of the candidate key in the structured query statement; summarizing the redistribution costs of the same candidate key of the same database table in the plurality of structured query statements to obtain a total redistribution cost of each candidate key in each database table; and taking the candidate key with the highest total redistribution cost in each database table as the distribution key of the database table.

[0037] Figure 1 The application scenario of the method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0038] like Figure 1 As shown, the application scenario 100 according to this embodiment may include a first terminal device 101, a second terminal device 102, a third terminal device 103, a network 104 and a server 105. The network 104 is used to provide a medium for a communication link between the first terminal device 101, the second terminal device 102, the third terminal device 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links or optical fiber cables, etc. The server 105 may be a server deployed with a distributed database. The server 105 may also be a server cluster with multiple servers, and a distributed database may be deployed on each server in the server cluster.

[0039] In addition, the server 105 may also be a storage server for storing the encoding files of the distributed database. For example, the server 105 is used to store the business SQL files of the distributed database.

[0040] The user may use at least one of the first terminal device 101, the second terminal device 102, and the third terminal device 103 to interact with the server 105 through the network 104 to receive or send messages, etc. For example, a business SQL file is obtained.

[0041] It should be noted that the method for determining a distribution key in a distributed database provided in an embodiment of the present invention can be implemented by being integrated in an analysis tool that is independent of the distributed database, and the analysis tool is decoupled from the distributed database.

[0042] Correspondingly, the analysis tool of the present invention can be installed in one of the first terminal device 101, the second terminal device 102, and the third terminal device 103, and the distribution key is determined by obtaining the business SQL file from the server 105 and analyzing the SQL file. Alternatively, the analysis tool of the embodiment of the present invention can also be installed in a server independent of the distributed database, and the server can obtain the business SQL file from the distributed database or storage server, and analyze the business SQL file to determine the optimal distribution key for each database table.

[0043] It should be understood that Figure 1 The number of terminal devices, networks and servers in the embodiment is only for illustration. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0044] The following will be based on Figure 1 The scene described by Figure 2~Figure 5 The method for determining the distribution key in the distributed database of the embodiment of the invention is described in detail.

[0045] Figure 2 A flow chart of a method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0046] like Figure 2 As shown, the method 200 includes operations S210 to S250.

[0047] In operation S210, a plurality of initial structured query statements are read from a coding file written when developing a distributed database.

[0048] In operation S220, without executing the multiple initial structured query statements, multiple structured query statements belonging to a preset statement type are screened out from the multiple initial structured query statements.

[0049] In operation S230 , the redistribution costs of the plurality of candidate keys associated with each structured query statement are determined according to the query tree of each structured query statement.

[0050] In operation S240, the redistribution costs of the same candidate key in the same database table in multiple structured query statements are aggregated to obtain a total redistribution cost of each candidate key in each database table.

[0051] In operation S250 , the candidate key with the highest total redistribution cost in each database table is used as the distribution key of the database table.

[0052] According to an embodiment of the present invention, the encoding file of the distributed database, that is, the business SQL file of the distributed database, can be a collection of SQL statements written by developers when developing the distributed database. The business SQL file can include multiple initial structured query statements, that is, multiple initial SQL statements. The initial SQL statement refers to the conventional operations when the distributed database is running normally, such as adding, deleting, modifying, and checking operations.

[0053] For the convenience of description, the initial structured query statement in the business SQL file is referred to as the initial SQL statement, and the initial structured query statement after being screened by a preset statement type is referred to as an SQL statement.

[0054] When determining the optimal distribution key for each database table, the process of obtaining the distribution key information is very critical. The more complete the business SQL statement is, the higher the match between the distribution key information and the actual operation scenario of the database.

[0055] However, when analyzing the initial SQL statements, not all initial SQL statements are meaningful for distribution key determination. For example, the table creation SQL statement is meaningless for distribution key analysis. Therefore, in order to reduce the amount of calculation in the distribution key analysis process, the meaningless initial SQL statements can be eliminated through the screening operation, that is, multiple SQL statements of preset statement types can be screened out from multiple initial SQL statements. The preset statement type can be understood as a statement type that is meaningful for distribution key analysis, for example, an SQL statement related to table query.

[0056] After the data acquisition and screening operations of operation S210 and operation S220, an analysis basis of the distribution key is obtained.

[0057] According to an embodiment of the present invention, the structured query statement includes at least one database table, and each of the database tables includes at least one candidate key.

[0058] Specifically, the query tree of each SQL statement refers to a tree data structure that represents the internal information and execution logic of the SQL statement. For example, the SQL statement used for the association query may include the table name of at least one database table. Each database table may include at least one field, and one or more fields in the at least one field may be used as a candidate key.

[0059] According to an embodiment of the present invention, a query tree for each SQL statement may be generated through a parser, semantic analysis, and rewriting operations.

[0060] Since the query tree of each SQL structure can represent the execution logic of the SQL statement, by analyzing the query tree of the SQL statement, it is possible to determine the database resources required for the distributed database to execute an associated query when each candidate key is used as a distribution key, that is, the redistribution cost of each candidate key.

[0061] The redistribution cost can be understood as the database resources consumed by the distributed database when executing the associated query operation, such as the number of queries, the number of accesses, etc. The higher the redistribution cost, the fewer database resources are consumed; the lower the redistribution cost, the more database resources are consumed. The redistribution cost is at least determined based on the number of occurrences of the candidate key in the structured query statement.

[0062] In one embodiment, the redistribution cost may also be understood as the distribution key information obtained in the distribution key analysis phase.

[0063] According to an embodiment of the present invention, multiple SQL statements may involve the same database table. Therefore, after calculating the redistribution cost of each candidate key in each database table for each SQL statement, it is necessary to integrate the distribution key information of multiple SQL statements, summarize the redistribution costs of the same candidate keys in the same database table, and obtain the total redistribution cost of each candidate key in each database table.

[0064] Since the higher the redistribution cost, the less database resources are consumed, the higher the total redistribution cost, the less database resources are consumed. For each database table, the candidate key with the highest total redistribution cost can be used as the optimal distribution key for the database table, so as to minimize the redistribution operation and reduce the consumption of database resources during associated queries.

[0065] In another specific embodiment, when two or two candidate keys with the highest total redistribution cost appear in a database table at the same time, one of them can be randomly selected as the distribution key. Alternatively, manual intervention analysis can be performed later; or, in a database environment, actual business data is loaded for performance comparison testing to obtain test results, and one of them is selected as the distribution key of the database table based on the test results. The test results are related to the data volume, data content, etc.

[0066] The embodiments of the present invention decouple the distribution key determination from the database environment. There is no need to deploy a database. The redistribution cost of each candidate key in each SQL statement can be determined simply by analyzing the query tree of the SQL statement in advance. Then, the total redistribution cost of each candidate key is determined by combining multiple SQL statements, thereby achieving the determination of the optimal distribution key in each data table in advance.

[0067] The technical effects of the method for determining the distribution key in a distributed database of the present invention are as follows: 1) The distribution key information collection process is decoupled from the database, eliminating the need for pre-deployment work for information collection. 2) Static analysis of business SQL statements, the distribution key analysis phase does not require the participation of time-consuming operations such as optimizers and executors, and the analysis efficiency of a single SQL statement is greatly improved. 3) Only business SQL statements related to the distribution key are processed, and irrelevant SQL statements are automatically filtered, reducing the overall workload of information collection.

[0068] In summary, the present invention performs distribution key analysis through an independent analysis tool, without the need to deploy the database in advance, thereby achieving decoupling from the database. In addition, when obtaining the distribution key information, each SQL statement does not need to pass through the optimizer and executor modules, which reduces the complexity of the operation and improves the efficiency of determining the distribution key. In addition, the present invention not only does not need to execute each business SQL statement, but can also determine the optimal distribution key in each database table, reducing the redistribution operation between database nodes, reducing the consumption of database resources, and improving the overall operation efficiency of the database.

[0069] According to an embodiment of the present invention, for operation S220, without executing multiple initial structured query statements, multiple structured query statements of a preset statement type are determined from multiple initial structured query statements, including: without executing multiple initial structured query statements, initial structured statements of an operation type are filtered out from multiple initial structured query statements, and the initial structured statements of the operation type are determined as structured query statements.

[0070] According to an embodiment of the present invention, the statement type of the initial structured query statement includes an operation type and a definition type. The operation type structured query statement is a DML statement written based on the Data Manipulation Language (DML), and the definition type structured query statement is a DDL statement written based on the Data Definition Language (DDL). DML statements include SELECT statements, UPDATE statements, DETELE statements, and INSERT INTO statements. DDL statements are used to create various objects in the database, such as tables, views, indexes, synonyms, clusters, etc., and may include CREATE TABLE / VIEW / INDEX / SYN / CLUSTER statements, etc. The statement type of the initial structured query statement can be determined according to the definition at the time of writing without execution.

[0071] According to an embodiment of the present invention, since DDL statements are used for various objects in a database and do not contribute to associated queries, the initial structured statement of the preset statement type may be a DML statement.

[0072] Figure 3 A flow chart of obtaining distribution key information according to an embodiment of the present invention is shown.

[0073] like Figure 3 As shown, obtaining distribution key information includes operations S301 to S306.

[0074] In operation S301, a business SQL file is read.

[0075] In operation S302, it is determined whether data is read. Specifically, the data determined here refers to the initial SQL statement in the business SQL file. In the case where the initial SQL statement is read, the read initial SQL statement is input into the parser M1. In the case where the initial SQL statement is not read, operation S306 is performed. In the embodiment of the present invention, if the initial SQL statement is not read, it can be considered that all initial SQL statements in the business SQL file have been read.

[0076] In operation S303, semantic analysis and rewriting, the parser M1 performs lexical analysis and grammatical analysis on the initial SQL statement. Then, a query tree is generated through semantic analysis, and a rewrite operation is performed on the generated query tree for the convenience of subsequent processing.

[0077] In operation S304, determine whether it is a DDL statement. If the initial SQL statement is not a DDL statement, perform operation S305; if the initial SQL statement is a DDL statement, skip the current initial SQL statement, return to operation S301, and continue to read the next initial SQL statement from the business SQL file.

[0078] In operation S305, distribution key analysis is performed. Specifically, the distribution key analysis may include determining the redistribution cost of each of multiple candidate keys related to the current initial SQL statement according to the query tree of the current initial SQL statement.

[0079] In operation S306, the collected information is stored in a file. Specifically, the distribution key information of the DML statement in the business SQL file may be stored in a preset file to facilitate subsequent reference to the distribution key of each database table.

[0080] The embodiment of the present invention determines the statement type of the initial SQL statement, thereby filtering out the DML statement. Therefore, during the distribution key analysis, only the DML statements related to the distribution key are processed, and irrelevant DDL statements are automatically filtered out, thereby reducing the workload of the distribution key analysis and improving the efficiency of the distribution key determination.

[0081] According to an embodiment of the present invention, the method further comprises: generating a query tree for each structured query statement in a plurality of structured query statements through a parser, semantic analysis, and rewriting operations. Generating a query tree for each structured query statement in a plurality of structured query statements through a parser, semantic analysis, and rewriting operations comprises: parsing the structured query statement through a parser to obtain a syntax tree; performing semantic analysis on the syntax tree and generating a query tree through a rewriting operation.

[0082] According to an embodiment of the present invention, an SQL statement can be input into a parser, and the parser can be used to parse the SQL statement to generate a syntax tree. After that, a query tree of the SQL statement is generated by semantically analyzing the syntax and execution logic of the SQL statement in the syntax tree.

[0083] In another embodiment, in order to ensure the accuracy of the query tree, the query tree may also be updated through a query rewriting operation.

[0084] For example, the query tree may be generated with the help of a database; or, the query tree may be generated by the above-mentioned analysis tool that is independent of the distributed database.

[0085] The general process of SQL processing by the database includes: 1. parser (generating syntax tree); 2. semantic analysis (generating query tree); 3. query rewriting (rewriting query tree); 4. optimizer; 5. executor. The present invention modifies the redistribution cost calculation method, and optimizes the redistribution cost from the execution cost during actual execution to a calculated value that can be obtained without executing SQL. Therefore, the optimizer and executor are avoided, and the implementation is separated from the database framework and the customizable process, thereby improving efficiency.

[0086] According to an embodiment of the present invention, for operation S230, the redistribution cost of each of the multiple candidate keys related to each structured query statement is determined based on the query tree of each structured query statement, including: for each structured query statement, the number of occurrences of each candidate key in the query tree is used as the redistribution cost of each candidate key; or, for each structured query statement, the redistribution cost of each candidate key is determined based on the position and number of occurrences of each candidate key in the query tree.

[0087] In the embodiment of the present invention, the more times a candidate key appears in the query tree of the SQL statement, the more times the data of the candidate key is retrieved in an associated query. Therefore, if the candidate key is not a distribution key, the number of redistribution operations is also greater. Therefore, if the SQL statement is not executed in a database environment, the redistribution cost of each candidate key can be determined based on the number of times the candidate key appears in the query tree of each SQL statement.

[0088] In another embodiment, considering that the positions of candidate keys in the query tree are different, their influence on the redistribution operation is also different. For example, the constraint field in the query tree has a greater probability of being a distribution key, so the weight of the candidate key appearing there is higher. For the candidate key at the query position, it is not very meaningful for the distribution key analysis, so the weight at this position is low, and setting the weight to 0 is equivalent to ignoring the influence of the candidate key. In this way, the redistribution cost of the candidate key is reflected through the two dimensions of the number of occurrences and the weight.

[0089] According to an embodiment of the present invention, the redistribution cost of each candidate key is determined according to the position and number of occurrences of each candidate key in the query tree, including: determining the position weight corresponding to the position according to the position of each candidate key in the query tree; and determining the redistribution cost of each candidate key according to the position weight and number of occurrences.

[0090] According to an embodiment of the present invention, the query tree mainly includes information such as a range table, a target column, a connection tree, and a subquery involved in an SQL statement. Among them, the range table includes the table that can be queried by the SQL statement and the detailed information of the table, the target column is the column attribute field queried by the SQL statement, and the connection tree is a tree structure, which mainly involves information such as connection fields when multiple tables are connected.

[0091] In the process of determining the redistribution cost, the query tree of each SQL statement can be recursively traversed to obtain the location of the candidate key and its occurrence count. Specifically, during the traversal process, if a field is detected as a column attribute field that is a candidate key, the corresponding column attribute structure information records the table in the range table to which the candidate key belongs, the number in the table, the column attribute type, and other information. The range table can be used to query the information of the table to which the candidate key belongs, such as the table name, attribute name, etc.

[0092] For example, for column attribute fields A and B, when both column attribute fields A and B are candidate keys, after traversing the query tree of a certain SQL statement, column attribute fields A and B both appear 10 times in the query tree. Column attribute field A appears 3 times in position 1 and 7 times in position 2; column attribute field B appears 4 times in position 1 and 6 times in position 2. The weights of position 1 and position 2 are 1 and 10 respectively. The redistribution cost of column attribute field A is 3*1+7*10=73, and the redistribution cost of column attribute field B is 4*1+6*10=64. The redistribution cost of column attribute field A is greater than that of B, so column attribute field A is selected as the distribution key.

[0093] The embodiment of the present invention can more accurately analyze the influence of column attribute fields at different positions in an SQL statement on the distribution key of the database table by introducing positions and weights.

[0094] In another embodiment, the distribution key information obtained in the distribution key analysis phase includes not only the redistribution cost of a candidate key, but also the column attribute field as the candidate key. In the query tree, the column attribute field is usually represented by a Var structure, and the column attribute field generally appears in multiple locations of the query tree, such as the range table, the target column, the connection tree, etc.

[0095] For example, for the following SQL statement: Select st.name, sc.degree FROM STUDENT st INNERJOIN SCORE sc ON st.sno = sc.sno. The query tree generated mainly includes the following three parts: 1. Range table: 3, namely STUDENT, SCORE and the connection between the two tables; 2. Join tree: join constraint st.sno = sc.sno; 3. Target column: name in STUDENT, degree in SCORE.

[0096] The biggest influence on the database table distribution key is the connection constraint in the connection tree. If the distribution key of STUDENT and SCORE is set according to the constraint, there will be no data redistribution when the two tables are connected. Therefore, the weight of the sno column attribute field of the two database tables STUDENT and SCORE is high. The name and degree fields in the target column are query columns and have no influence on the distribution key setting. They can be set to a lower weight to reduce their influence.

[0097] When traversing the query tree, when the column attribute field is traversed, it is cached according to the weight of the corresponding position. After the traversal is completed, all the column attribute fields involved in the query tree will be saved. Therefore, for the query tree of each SQL statement, the redistribution cost of each candidate key of each database table is accumulated according to the weight and the number of occurrences.

[0098] According to an embodiment of the present invention, the method further includes: for each structured query statement, caching the redistribution cost of each candidate key in each database table into a hash table; wherein the hash table includes multiple cache records, each cache record is associated with a candidate key in a database table, and the cache structure of the cache record includes a namespace, a database table name, an attribute name of the candidate key, and a redistribution cost.

[0099] After traversing the query tree, the redistribution cost of each candidate key in each database table is cached in a hash table, such as an in-memory HASH table.

[0100] Figure 4 A distribution key analysis flow chart according to an embodiment of the present invention is shown.

[0101] like Figure 4As shown, the distribution key analysis process includes operations S401 to S404.

[0102] In operation S401, the query tree is traversed. Initially, the query tree of each SQL statement is traversed.

[0103] In operation S402, it is determined whether the traversal is completed. If the traversal is completed, the query tree traversal of the SQL statement is completed, and the query tree traversal of the next SQL statement is continued. If the traversal is not completed, the process proceeds to operation S403.

[0104] In operation S403, it is determined whether it is a column attribute field. If the currently traversed field is a column attribute field, operation S404 is performed; if the currently traversed field is not a column attribute field, the currently traversed field is skipped, and operation S401 is returned to continue traversing the query tree.

[0105] In operation S404, the collected information is cached in the memory hash table. After operation S404, the process returns to operation S401 to continue traversing the query tree.

[0106] Since multiple SQL statements may have duplicate database tables, calculating the redistribution cost of each candidate key in each database table only in the dimension of a single SQL statement may not be accurate. Therefore, it is necessary to summarize the redistribution costs of the same candidate key in the same database table in multiple structured query statements to obtain the total redistribution cost of each candidate key in each database table.

[0107] According to an embodiment of the present invention, the aggregation operation includes: traversing the hash table, aggregating the redistribution costs of the same candidate key of the same database table in multiple structured query statements, and obtaining the total redistribution cost of each candidate key in each database table.

[0108] In an embodiment of the present invention, when traversing the memory HASH table, the structure data in the table is written into the distribution key information file in the form of records, and each structure corresponds to a row of records. The distribution key information file format supports text, csv, excel, etc.

[0109] For example, the format of each record in the distribution key information file is: namespace s + database table name t + attribute name a + total redistribution cost v.

[0110] During the traversal, for performance reasons, cached information is recorded in a memory HASH table. After all SQL statements have been traversed, the memory HASH table is traversed to summarize the total redistribution cost of each candidate key in each database table.

[0111] It should be noted that when caching the redistribution cost of each candidate key, for candidate keys with the same namespace, table name, and attribute name, only one copy can be cached, and the redistribution cost can be accumulated. Similarly, when summarizing the total redistribution cost, for database tables that appear in multiple SQL statements, for candidate keys with the same namespace, table name, and attribute name, only one copy is stored in the cache linked list, and the total redistribution cost can be accumulated.

[0112] Thus, for operation S250, the distribution key information file generated by the above operation can be read to compare the total redistribution cost of each candidate key in each database table. For each database table, the candidate key with the highest total redistribution cost can be used as the distribution key and cached in the memory linked list. There may be more than one optimal distribution key for each database table.

[0113] For example, the distribution key information file includes two records, stav1 and stbv2, which respectively represent the total redistribution cost v1 of candidate key a in table t under namespace s and the total redistribution cost v2 of candidate key b in table t under namespace s. Compare the values ​​of total redistribution costs v1 and v2, and cache the larger record in the memory linked list; if they are equal, cache both. After reading the distribution key information file, write the distribution key record of each database table cached in the memory linked list into a new file, such as the distribution key design file, for subsequent reference.

[0114] The distribution key information file stored in the form of namespace, table name, attribute name, and total redistribution cost is in a standard format and can be processed using general tools, such as database, excel, etc. In addition, for ease of use, the analysis tool of the present invention also supports statistics on data records in the distribution key information file, reads the distribution key information one by one, and compares the total redistribution cost of each candidate key in each database table.

[0115] Figure 5 A flow chart of optimal distribution key calculation according to an embodiment of the present invention is shown.

[0116] like Figure 5 As shown, the optimal distribution key calculation process includes operations S501 to S508.

[0117] In operation S501, a distribution key information file is read.

[0118] In operation S502, it is determined whether the distribution key information is read. If the distribution key information is read, operation S503 is executed; if the distribution key information is not read, it indicates that the distribution keys in the distribution key information file have been read, and operation S508 is executed to end the reading operation of the distribution key information file.

[0119] In operation S503, it is determined whether the memory linked list is empty. The memory linked list is used to store the distribution key information related to all database tables. If the memory linked list is empty, it means that the memory linked list is operated for the first time, and the read distribution key information can be directly stored in the memory linked list; if the memory linked list is not empty, it is necessary to determine whether the current distribution key information is the optimal distribution key of the corresponding database table through subsequent query and comparison operations.

[0120] Therefore, when the memory linked list is not empty, operation S504 is performed; when the memory linked list is empty, operation S507 is performed.

[0121] In operation S504, the memory linked list is traversed to query the distribution key information of other candidate keys in the same database table.

[0122] In operation S505, whether the distribution key information of other candidate keys in the same database table is found, operation S506 is performed; if the distribution key information of other candidate keys in the same database table is not found, the candidate key corresponding to the current distribution key information can be temporarily used as the distribution key, and operation S507 is performed.

[0123] In operation S506, the total redistribution costs are compared, and the distribution key information with a larger total redistribution cost is retained in the memory linked list. Specifically, the total redistribution cost in the current distribution key information is compared with the redistribution costs of other candidate keys under the same database table in the memory linked list, and the distribution key information with a larger total redistribution cost is retained in the memory linked list.

[0124] In operation S507, the distribution key information is cached in a memory linked list.

[0125] In operation S508, the distribution key information in the memory linked list is stored in the distribution key design file.

[0126] According to an embodiment of the present invention, after determining the optimal distribution key in each database table, it can be applied to the database to make it effective, thereby minimizing the number of redistribution times of the distributed database and improving system efficiency.

[0127] The technical effects of the method for determining the distribution key in the distributed database of the present invention are embodied in the following aspects: 1) The distribution key information collection process is decoupled from the database, eliminating the pre-deployment work of information collection. 2) Static analysis of business SQL statements, the distribution key analysis stage does not require the participation of time-consuming operations such as optimizers and executors, and the analysis efficiency of a single SQL statement is greatly improved. 3) Only business SQL statements related to the distribution key are processed, and irrelevant SQL is automatically filtered, reducing the overall workload of information collection. Therefore, the present invention solves the problems of complex operations and low efficiency in the current distribution key optimization through the database, and can quickly select the optimal distribution key, greatly improving the execution efficiency.

[0128] Figure 6 A structural block diagram of a device for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0129] like Figure 6 As shown, the device 600 for determining a distribution key in a distributed database of this embodiment includes a reading module 610 , a determining module 620 , a cost calculation module 630 , a summarizing module 640 and a distribution key determining module 650 .

[0130] The reading module 610 is used to parse a plurality of initial structured query statements from a coding file written when developing a distributed database.

[0131] The determination module 620 is used to filter out multiple structured query statements belonging to a preset statement type from the multiple initial structured query statements without executing the multiple initial structured query statements.

[0132] The cost calculation module 630 is used to determine the redistribution cost of each of the multiple candidate keys related to each structured query statement according to the query tree of each structured query statement, wherein the structured query statement includes at least one database table, each database table includes at least one candidate key, and the redistribution cost is determined based on at least the number of occurrences of the candidate key in the structured query statement.

[0133] The aggregation module 640 is used to aggregate the redistribution costs of the same candidate key in the same database table in multiple structured query statements to obtain the total redistribution cost of each candidate key in each database table.

[0134] The distribution key determination module 650 is used to use the candidate key with the highest total redistribution cost in each database table as the distribution key of the database table.

[0135] It should be noted that the device part in the embodiment of the present invention corresponds to the method part in the embodiment of the present invention. The description of the device part specifically refers to the method part, which will not be repeated here.

[0136] According to an embodiment of the present invention, any multiple modules among the reading module 610, the determining module 620, the cost calculating module 630, the summarizing module 640 and the distribution key determining module 650 may be combined into one module for implementation, or any one of the modules may be split into multiple modules. Alternatively, at least part of the functions of one or more of these modules may be combined with at least part of the functions of other modules and implemented in one module.

[0137] According to an embodiment of the present invention, at least one of the reading module 610, the determination module 620, the cost calculation module 630, the summary module 640 and the distribution key determination module 650 can be at least partially implemented as a hardware circuit, such as a field programmable gate array (FPGA), a programmable logic array (PLA), a system on a chip, a system on a substrate, a system on a package, an application-specific integrated circuit (ASIC), or can be implemented by hardware or firmware such as any other reasonable way of integrating or packaging the circuit, or implemented in any one of the three implementation methods of software, hardware and firmware or in an appropriate combination of any of them. Alternatively, at least one of the reading module 610, the determination module 620, the cost calculation module 630, the summary module 640 and the distribution key determination module 650 can be at least partially implemented as a computer program module, and when the computer program module is run, the corresponding function can be performed.

[0138] Figure 7 A block diagram of an electronic device suitable for a method for determining a distribution key in a distributed database according to an embodiment of the present invention is shown.

[0139] like Figure 7 As shown, the electronic device 700 according to an embodiment of the present invention includes a processor 701, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 702 or a program loaded from a storage part 708 to a random access memory (RAM) 703. The processor 701 may include, for example, a general-purpose microprocessor (such as a CPU), an instruction set processor and / or a related chipset and / or a special-purpose microprocessor (for example, an application-specific integrated circuit (ASIC)), etc. The processor 701 may also include an onboard memory for caching purposes. The processor 701 may include a single processing unit or multiple processing units for performing different actions of the method flow according to an embodiment of the present invention.

[0140] In RAM 703, various programs and data required for the operation of electronic device 700 are stored. Processor 701, ROM 702 and RAM 703 are connected to each other via bus 704. Processor 701 performs various operations of the method flow according to the embodiment of the present invention by executing the programs in ROM 702 and / or RAM 703. It should be noted that the program can also be stored in one or more memories other than ROM 702 and RAM 703. Processor 701 can also perform various operations of the method flow according to the embodiment of the present invention by executing the programs stored in the one or more memories.

[0141] According to an embodiment of the present invention, the electronic device 700 may further include an input / output (I / O) interface 705, which is also connected to the bus 704. The electronic device 700 may further include one or more of the following components connected to the input / output I / O interface 705: an input portion 706 including a keyboard, a mouse, etc.; an output portion 707 including a cathode ray tube (CRT), a liquid crystal display (LCD), etc., and a speaker, etc.; a storage portion 708 including a hard disk, etc.; and a communication portion 709 including a network interface card such as a LAN card, a modem, etc. The communication portion 709 performs communication processing via a network such as the Internet. A drive 710 is also connected to the I / O interface 705 as needed. A removable medium 711, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 710 as needed, so that a computer program read therefrom is installed into the storage portion 708 as needed.

[0142] The present invention also provides a computer-readable storage medium, which may be included in the device / apparatus / system described in the above embodiment; or may exist independently without being assembled into the device / apparatus / system. The above computer-readable storage medium carries one or more programs, and when the above one or more programs are executed, the method according to the embodiment of the present invention is implemented.

[0143] According to an embodiment of the present invention, the computer-readable storage medium may be a non-volatile computer-readable storage medium, for example, it may include but is not limited to: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In the present invention, the computer-readable storage medium may be any tangible medium containing or storing a program, which may be used by or in combination with an instruction execution system, an apparatus or a device. For example, according to an embodiment of the present invention, the computer-readable storage medium may include the ROM 702 and / or RAM 703 described above and / or one or more memories other than ROM 702 and RAM 703.

[0144] The embodiment of the present invention also includes a computer program product, which includes a computer program, and the computer program contains program code for executing the method shown in the flowchart. When the computer program product is run in a computer system, the program code is used to enable the computer system to implement the above method provided by the embodiment of the present invention.

[0145] The computer program executes the above functions defined in the system / device of the embodiment of the present invention when it is executed by the processor 701. According to the embodiment of the present invention, the system, device, module, unit, etc. described above can be implemented by a computer program module.

[0146] In one embodiment, the computer program may rely on tangible storage media such as optical storage devices, magnetic storage devices, etc. In another embodiment, the computer program may also be transmitted and distributed in the form of signals on a network medium, and downloaded and installed through the communication part 709, and / or installed from the removable medium 711. The program code contained in the computer program may be transmitted using any appropriate network medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0147] In such an embodiment, the computer program can be downloaded and installed from the network through the communication part 709, and / or installed from the removable medium 711. When the computer program is executed by the processor 701, the above functions defined in the system of the embodiment of the present invention are performed. According to the embodiment of the present invention, the system, device, means, module, unit, etc. described above can be implemented by a computer program module.

[0148] According to an embodiment of the present invention, the program code for executing the computer program provided by the embodiment of the present invention can be written in any combination of one or more programming languages. Specifically, these computing programs can be implemented using high-level process and / or object-oriented programming languages, and / or assembly / machine languages. Programming languages ​​include, but are not limited to, Java, C++, python, "C" language or similar programming languages. The program code can be executed entirely on the user computing device, partially on the user device, partially on the remote computing device, or entirely on the remote computing device or server. In the case of a remote computing device, the remote computing device can be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or can be connected to an external computing device (e.g., using an Internet service provider to connect through the Internet).

[0149] The flow chart and block diagram in the accompanying drawings illustrate the possible architecture, function and operation of the system, method and computer program product according to various embodiments of the present invention. In this regard, each box in the flow chart or block diagram can represent a module, a program segment, or a part of a code, and the above-mentioned module, program segment, or a part of a code contains one or more executable instructions for realizing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the box can also occur in a different order from the order marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flow chart, and the combination of the boxes in the block diagram or flow chart can be implemented with a dedicated hardware-based system that performs a specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0150] It will be appreciated by those skilled in the art that the features described in the various embodiments of the present invention may be combined and / or combined in various ways, even if such combinations or combinations are not explicitly described in the present invention. In particular, the features described in the various embodiments of the present invention and in the present invention may be combined and / or combined in various ways without departing from the spirit and teachings of the present invention. All of these combinations and / or combinations fall within the scope of the present invention.

[0151] The specific embodiments described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for determining a distribution key in a distributed database, characterized in that: The method comprises: Parse multiple initial structured query statements from the encoding files written when developing the distributed database; Without executing the plurality of initial structured query statements, screening out a plurality of structured query statements belonging to a preset statement type from the plurality of initial structured query statements; Determine, according to the query tree of each structured query statement, respective redistribution costs of a plurality of candidate keys associated with each structured query statement, wherein the structured query statement includes at least one database table, and each database table includes at least one candidate key; the redistribution cost is determined based at least on the number of occurrences of the candidate key in the structured query statement; Aggregating the redistribution costs of the same candidate key in the same database table in the multiple structured query statements to obtain a total redistribution cost of each candidate key in each database table; and The candidate key with the highest total redistribution cost in each of the database tables is used as the distribution key of the database table.

2. The method according to claim 1, characterized in that Determining the respective redistribution costs of a plurality of candidate keys associated with each structured query statement according to the query tree of each structured query statement includes: For each of the structured query statements, the number of occurrences of each of the candidate keys in the query tree is used as the redistribution cost of each of the candidate keys; or, For each of the structured query statements, the redistribution cost of each of the candidate keys is determined according to the position and the number of occurrences of each of the candidate keys in the query tree.

3. The method according to claim 2, characterized in that The step of determining the redistribution cost of each candidate key according to the position and the number of occurrences of each candidate key in the query tree includes: According to the position of each candidate key in the query tree, determining a position weight corresponding to the position; and The redistribution cost of each of the candidate keys is determined according to the position weight and the number of occurrences.

4. The method according to claim 1, characterized in that The method further comprises: For each of the structured query statements, cache the redistribution cost of each of the candidate keys in each of the database tables into a hash table; The hash table includes multiple cache records, each of which is associated with a candidate key in a database table, and the cache structure of the cache record includes a namespace, a database table name, an attribute name of the candidate key, and a redistribution cost.

5. The method according to claim 4, characterized in that The redistribution costs of the same candidate key of the same database table in the multiple structured query statements are aggregated to obtain the total redistribution cost of each candidate key in each database table, including: The hash table is traversed, and the redistribution costs of the same candidate key of the same database table in multiple structured query statements are summarized to obtain the total redistribution cost of each candidate key in each database table.

6. The method according to claim 1, characterized in that The statement type of the initial structured query statement includes an operation type and a definition type; The step of selecting a plurality of structured query statements belonging to a preset statement type from the plurality of initial structured query statements without executing the plurality of initial structured query statements comprises: In the case of not executing the multiple initial structured query statements, an initial structured statement of an operation type is screened out from the multiple initial structured query statements, and the initial structured statement of the operation type is determined as the structured query statement.

7. The method according to claim 1, characterized in that The method further comprises: Generate a query tree for each of the structured query statements in the plurality of structured query statements through a parser, semantic analysis, and rewriting operations; The step of generating a query tree of each of the structured query statements in the plurality of structured query statements through a parser, semantic analysis, and rewriting operations includes: Parsing the structured query statement by the parser to obtain a syntax tree; The syntax tree is semantically analyzed, and the query tree is generated by a rewriting operation.

8. A device for determining a distribution key in a distributed database, characterized in that: The device comprises: The reading module is used to parse multiple initial structured query statements from the encoding file written when developing the distributed database; A determination module, configured to filter out a plurality of structured query statements belonging to a preset statement type from the plurality of initial structured query statements without executing the plurality of initial structured query statements; A cost calculation module, configured to determine, according to a query tree of each structured query statement, respective redistribution costs of a plurality of candidate keys associated with each structured query statement, wherein the structured query statement includes at least one database table, and each database table includes at least one candidate key; and the redistribution cost is determined based at least on the number of occurrences of the candidate key in the structured query statement; a summarizing module, used to summarize the redistribution costs of the same candidate key in the same database table in multiple structured query statements to obtain the total redistribution cost of each candidate key in each database table; and The distribution key determination module is used to use the candidate key with the highest total redistribution cost in each of the database tables as the distribution key of the database table.

9. An electronic device, comprising: one or more processors; a memory for storing one or more computer programs, It is characterized in that the one or more processors execute the one or more computer programs to implement the steps of the method according to any one of claims 1 to 7.

10. A computer-readable storage medium having a computer program or instruction stored thereon, characterized in that: When the computer program or instruction is executed by a processor, the steps of the method according to any one of claims 1 to 7 are implemented.

Images