Logical plan reuse method for distributed database

Abstract

The invention discloses a method for reusing a logic plan of a distributed database, and relates to the technical field of database optimization. According to a distributed scenario of a distributed database application, an SQL statement is parsed to generate an abstract syntax tree AST, constant items of the SQL statement and corresponding positions of the constant items in the AST are synchronously saved, and a conversion relationship is recorded. The conversion relationship includes the relative position and conversion method of the constant item when a memo expression is generated through the AST, and the relative position and conversion method of the constant item when a logic plan is generated through the memo expression. According to the conversion relationship, the text of the SQL statement is matched to a corresponding logic plan, and the logic plan is reused.

Description

Technical Field

[0001] The invention discloses a method, which relates to the technical field of database optimization, and in particular to a logic plan reuse method of a distributed database. Background Art

[0002] The SQL statement execution of a relational database includes several stages. When the client passes in the SQL statement, the SQL statement will undergo syntax parsing and semantic parsing to generate an abstract syntax tree (AST). The AST will then be parsed to generate a memo expression, which represents the equivalent operation of the user's query statement. For example, when a user queries some columns of a table, it is a projection operation in relational algebra, and corresponds to a scan operation in the memo expression. The generated memo expression is equivalent to the AST, but not optimal. The database uses a heuristic method to optimize the memo, discover the optimal expression, and then build a logical plan. Then, based on the data distribution of each node, a physical plan is generated from the logical plan to execute it in a distributed manner on each node.

[0003] In the above process, there is a one-to-one correspondence from SQL statement to AST, AST to memo, optimized memo to logical plan, and logical plan to physical plan. The system does not need to perform other special operations and conversions, so the time and resources occupied will not change much. However, depending on the statement, the heuristic optimization of memo will take too long. At the same time, due to the database system settings or other optimization plan information and other constantly changing factors, the memo expression changes frequently, which easily leads to excessive time consumption and difficulty in reuse when executing this part. Summary of the invention

[0004] Databases are mainly divided into online transaction processing databases (Online Transaction Processing) and online analytical processing databases (Online Analytical Processing) according to usage scenarios. Transaction processing databases are mainly based on transactions, and the statements executed are mainly small-scale transactions and simple query statements, accompanied by a large number of execution frequencies. Analytical databases mainly analyze historical data, and the statements executed are often more complex, involving more tables and data volumes, and the execution time is longer, but the execution frequency is lower. Common scenarios include the application of daily scheduled reports, weekly reports, etc. In view of the problems of the prior art, the present invention provides a logical plan reuse method for a distributed database, which greatly saves the time of building a logical plan in a TP scenario (transaction processing database) and improves the efficiency of system operation.

[0005] The specific scheme proposed by the present invention is:

[0006] The present invention provides a logic plan reuse method for a distributed database, which parses SQL statements to generate an abstract syntax tree AST according to distributed scenarios of distributed database applications, and synchronously saves constant items of the SQL statements and corresponding positions of the constant items in the AST.

[0007] Record the transformation relationship, which includes the relative position and transformation method of the constant terms when generating memo expressions through AST, and the relative position and transformation method of the constant terms when generating logical plans through memo expressions,

[0008] Match the text of the SQL statement to the corresponding logical plan based on the conversion relationship, and reuse the logical plan.

[0009] Furthermore, the record conversion relationship in the distributed database logic plan reuse method includes:

[0010] When a SQL statement is executed for the first time, the process of generating a memo expression based on AST conversion records the relative position and conversion method of constant terms when generating a memo expression through AST.

[0011] The process of generating a logical plan based on a memo expression records the relative position and conversion method of constant terms when generating a logical plan through a memo expression.

[0012] Furthermore, the method for reusing a logical plan in a distributed database includes:

[0013] Copy the logical plan and use the placeholder PlaceHolder to replace the constant item in the copy result of the logical plan. When the SQL statement is executed, the constant item in the AST is filled into the corresponding logical plan according to the position of the placeholder PlaceHolder and the transformation relationship.

[0014] Furthermore, the logical plan reuse method of the distributed database includes:

[0015] Implement expiration processing for logical plans.

[0016] The present invention also provides a logic plan reuse device for a distributed database, including an analysis module and a reuse module.

[0017] The analysis module parses SQL statements to generate an abstract syntax tree AST according to the distributed scenario of the distributed database application, and simultaneously saves the constant items of the SQL statements and the corresponding positions of the constant items in the AST.

[0018] Record the transformation relationship, which includes the relative position and transformation method of the constant terms when generating memo expressions through AST, and the relative position and transformation method of the constant terms when generating logical plans through memo expressions,

[0019] The reuse module matches the text of the SQL statement to the corresponding logical plan according to the conversion relationship and reuses the logical plan.

[0020] Furthermore, the analysis module in the distributed database logic plan reuse device records the conversion relationship, including:

[0021] When a SQL statement is executed for the first time, the process of generating a memo expression based on AST conversion records the relative position and conversion method of constant terms when generating a memo expression through AST.

[0022] The process of generating a logical plan based on a memo expression records the relative position and conversion method of constant terms when generating a logical plan through a memo expression.

[0023] Furthermore, the multiplexing module in the logic plan multiplexing device of the distributed database performs logic plan multiplexing, including:

[0024] Copy the logical plan and use the placeholder PlaceHolder to replace the constant item in the copy result of the logical plan. When the SQL statement is executed, the constant item in the AST is filled into the corresponding logical plan according to the position of the placeholder PlaceHolder and the transformation relationship.

[0025] Furthermore, the multiplexing module in the logic plan multiplexing device of the distributed database performs logic plan multiplexing, including:

[0026] Implement expiration processing for logical plans.

[0027] The benefits of the present invention are:

[0028] The present invention provides a logic plan reuse method for a distributed database, which implements the storage of execution plans, the conversion and preservation of constant values ​​and corresponding relational algebras in statements, and the filling of constant items in execution plans in distributed scenarios, thereby implementing the reuse of execution plans. In TP scenarios, a large number of statements with the same sentence structure are frequently executed. This method can greatly reduce the resource consumption for creating execution plans in this scenario and improve execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0030] Figure 1 Schematic diagram comparing the logic plan reuse process of the present invention with the prior art process.

[0031] Figure 2 It is the first execution of the logical plan interaction flow chart.

[0032] Figure 3 It is a schematic diagram of the interaction process of reused logic plan. DETAILED DESCRIPTION

[0033] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it, but the embodiments are not intended to limit the present invention.

[0034] The present invention provides a logic plan reuse method for a distributed database, which parses SQL statements to generate an abstract syntax tree AST according to distributed scenarios of distributed database applications, and synchronously saves constant items of the SQL statements and corresponding positions of the constant items in the AST.

[0035] Record the transformation relationship, which includes the relative position and transformation method of the constant terms when generating memo expressions through AST, and the relative position and transformation method of the constant terms when generating logical plans through memo expressions,

[0036] Match the text of the SQL statement to the corresponding logical plan based on the conversion relationship, and reuse the logical plan.

[0037] refer to Figure 1In the execution life cycle of an SQL statement, the SQL statement text of the user (USER) is passed to the database through the database client. The database parses the statement through the parser and generates an abstract syntax tree (AST). The AST generates the corresponding memo expression through the optimizer (optbuilder). In the optimizer optbuilder, the AST is first converted into the corresponding memo expression according to its structure, and then the expression is explored and the memo expression is optimized by comparing the rules defined in the database to generate the optimal memo. The relational expression parser (relational biulder) converts the optimal memo expression into the corresponding logical plan (planNode). The logical plan represents the execution logic of the statement, such as which table to read the data from, what kind of filtering conditions to pass, and what operator the output data is output to. The processor (processor) converts the logical plan into the corresponding physical plan (physicalplan). When generating the physical plan, the processor needs to read the system metadata, including the data distribution of each node, the current system load and other real-time information, and generate the physical plan based on this information. The execution of the physical plan is carried out by the executor (executor). The executor publishes the physical plan to each node for distributed execution.

[0038] The method provided by the present invention can directly obtain the corresponding logical plan in the plan cache (planCache) from the SQL statement, and fill and reuse it through the numerical value in the AST, thereby skipping the generation of memo, and exploring the steps of optimizing memo with optimization rules, greatly reducing time consumption. That is, the method of the present invention can realize the storage of execution plans, the conversion and preservation of constant values ​​and corresponding relationship algebras in statements, and the filling of logical plan constant terms in distributed scenarios, thereby realizing the reuse of logical plans. In the tp scenario, a large number of statements with the same sentence structure will be frequently executed. The method of the present invention skips the expression optimization step, greatly improves the plan reuse degree, and improves the performance of the plan reuse method, which can greatly reduce the resource consumption for creating a logical plan in this scenario and improve execution efficiency.

[0039] In some embodiments of the method of the present invention, such as a distributed database based on CockroachDB, logical plan reuse is performed.

[0040] By applying the method of the present invention, according to the distributed scenario of the distributed database application, the SQL statement is parsed to generate an abstract syntax tree AST, and the constant items of the SQL statement and the corresponding positions of the constant items in the AST are synchronously saved, wherein the constant items in the user input statement refer to constant values ​​and can be replaced and filled. For example, when the user input statement is 'select * from twhere ta=1', 1 in 'ta=1' is the constant item that needs to be saved.

[0041] Store constant values ​​and transformation relationships, including the relative position and transformation method of constant terms when generating memo expressions through AST, and the relative position and transformation method of constant terms when generating logical plans through memo expressions. When a statement is executed for the first time, since no plan has been built, refer to Figure 2 When AST generates a memo expression, it records the relative position changes before and after the constant term is transformed, as well as the transformation method performed.

[0042] When memo expressions are heuristically mined to generate logical plans, some optimization rules may cause the relative positions of constant terms and different operator structures in the plan to change. The database records the relative positions of constant terms, how the constant terms are transformed into the logical plan, and the relative positions of the constant terms in the logical plan.

[0043] The conversion relationships recorded in the above two stages can be merged. The conversion method from constant items in AST to memo expressions recorded in the first stage can be merged into the record content of the second stage.

[0044] After recording, the obtained logical plan is deeply copied through the database, and all constant items in the copy result are replaced with the placeholder PlaceHolder. Since the AST, the relative position, corresponding relationship and conversion method of the constant items in the logical plan have been stored, the constant items in the AST can be easily filled into the corresponding logical plan. When the user executes the statement, the SQL statement text can be directly matched to the corresponding logical plan, and the database still parses the SQL statement to obtain the structured constant item value for reuse in the logical plan. When subsequent SQL statements are executed, the SQL statement entered by the user directly hits the execution plan from the plan cache (PlanCache) and is executed directly, skipping the steps of building expressions and exploring optimized expressions. At this time, the execution is as follows Figure 3 shown.

[0045] The expiration of the logical plan should have the same or higher expiration mechanism as memo. The expired logical plan is processed, and the number of expiration of the logical plan may be slightly more than that of memo, but the resource consumption saved by skipping the memo stage far exceeds this part of the shortfall.

[0046] The present invention also provides a logic plan reuse device for a distributed database, including an analysis module and a reuse module.

[0047] The analysis module parses SQL statements to generate an abstract syntax tree AST according to the distributed scenario of the distributed database application, and simultaneously saves the constant items of the SQL statements and the corresponding positions of the constant items in the AST.

[0048] Record the transformation relationship, which includes the relative position and transformation method of the constant terms when generating memo expressions through AST, and the relative position and transformation method of the constant terms when generating logical plans through memo expressions,

[0049] The reuse module matches the text of the SQL statement to the corresponding logical plan according to the conversion relationship and reuses the logical plan.

[0050] The information interaction, execution process and other contents between the modules in the above-mentioned device are based on the same concept as the embodiment of the method of the present invention. For the specific contents, please refer to the description in the embodiment of the method of the present invention, and no further description is given here.

[0051] Similarly, the device of the present invention implements the storage of execution plans, the conversion and preservation of constant values ​​and corresponding relational algebra in statements, and the filling of constant items in execution plans in distributed scenarios, thereby realizing the reuse of execution plans. In TP scenarios, a large number of statements with the same sentence structure are frequently executed. The device can greatly reduce the resource consumption for creating execution plans in this scenario and improve execution efficiency.

[0052] It should be noted that not all steps and modules in the above-mentioned processes and devices are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.

[0053] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A logical plan reuse method for a distributed database, characterized by: According to the distributed scenario of the distributed database application, the SQL statement is parsed to generate an abstract syntax tree AST, and the constant items of the SQL statement and the corresponding positions of the constant items in the AST are saved synchronously. Record the transformation relationship, including: when the SQL statement is executed for the first time, the process of generating a memo expression based on AST transformation, recording the relative position and transformation method of constant terms when generating a memo expression through AST; the process of generating a logical plan based on memo expression, recording the relative position and transformation method of constant terms when generating a logical plan through memo expression; The transformation relationship includes the relative position and transformation method of constant terms when generating memo expressions through AST, and the relative position and transformation method of constant terms when generating logical plans through memo expressions. The text of the SQL statement is matched to the corresponding logical plan according to the transformation relationship, and the logical plan is reused, including: copying the logical plan and replacing the constant items with the placeholder PlaceHolder in the copy result of the logical plan. When the SQL statement is executed, the constant items in the AST are filled into the corresponding logical plan according to the transformation relationship based on the position of the placeholder PlaceHolder, and the logical plan is also expired.

2. A logical plan reuse device for a distributed database, characterized in that Including analysis module and reuse module, The analysis module parses SQL statements to generate an abstract syntax tree AST according to the distributed scenario of the distributed database application, and simultaneously saves the constant items of the SQL statements and the corresponding positions of the constant items in the AST. The analysis module records the transformation relationship, including: when the SQL statement is executed for the first time, the process of generating a memo expression based on AST transformation, recording the relative position and transformation method of constant terms when generating a memo expression through AST; the process of generating a logical plan based on memo expression, recording the relative position and transformation method of constant terms when generating a logical plan through memo expression; The transformation relationship includes the relative position and transformation method of constant terms when generating memo expressions through AST, and the relative position and transformation method of constant terms when generating logical plans through memo expressions. The reuse module matches the text of the SQL statement to the corresponding logical plan according to the transformation relationship and reuses the logical plan, including: copying the logical plan and replacing the constant items with the placeholder PlaceHolder in the copy result of the logical plan. When executing the SQL statement, the constant items in the AST are filled into the corresponding logical plan according to the transformation relationship based on the position of the placeholder PlaceHolder, and the logical plan is expired.

Images