A distributed database statement execution method, device, storage medium and electronic device

Abstract

This invention discloses a method, apparatus, storage medium, and electronic device for statement execution in a distributed database. The method includes: obtaining a statement to be executed, the statement to be executed including at least one sub-statement, each sub-statement including variable information; determining a statement template matched by each sub-statement in a statement template cache, the statement template including variable identifiers; determining execution plan cache data corresponding to the statement template, and determining execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifiers in the statement template; determining target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement; and executing the statement to be executed based on the target execution plan information. This simplifies the process of determining the target execution plan information of the statement to be executed and reduces the execution time of the statement to be executed.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method, apparatus, storage medium, and electronic device for executing statements in a distributed database. Background Technology

[0002] In a distributed database sharding scenario, it is necessary to determine the execution plan of the statement to be executed. The execution plan of the statement to be executed can be the routing information of the sharding.

[0003] In the process of realizing this invention, it was found that at least the following technical problems exist in the prior art: the process of determining the execution plan of the statement to be executed is time-consuming, especially when a large number of statements to be executed include repetitive or similar statements. Frequent execution of the execution plan of repetitive or similar statements leads to high execution time of the statements to be executed, which affects the access performance of the database. Summary of the Invention

[0004] This invention provides a method, apparatus, storage medium, and electronic device for executing statements in a distributed database, which simplifies the process of determining the execution plan information of the statements to be executed and reduces the execution time of the statements to be executed.

[0005] According to one aspect of the present invention, a method for executing statements in a distributed database is provided, comprising:

[0006] Obtain the statement to be executed, which includes at least one substatement, and each substatement includes variable information;

[0007] Determine the statement template that each sub-statement hits in the statement template cache library, the statement template including variable identifiers;

[0008] Determine the execution plan cache data corresponding to the statement template, and determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template;

[0009] The target execution plan information corresponding to the statement to be executed is determined based on the execution plan information corresponding to at least one sub-statement; the statement to be executed is executed based on the target execution plan information.

[0010] Optionally, determining the statement template matched by each sub-statement in the statement template cache includes: generating a syntax tree of the statement to be executed, wherein each subtree of the syntax tree corresponds to a sub-statement; traversing the sub-statements in the syntax tree to determine the statement template matched by each sub-statement in the statement template cache.

[0011] Optionally, the statement template cache library includes multiple statement templates arranged in sequence;

[0012] The step of determining the statement template that each sub-statement matches in the statement template cache library includes: for each sub-statement, matching the sub-statement with each statement template sequentially based on the arrangement order of the statement templates in the statement template cache library until the statement template that the sub-statement matches is determined.

[0013] Furthermore, the method further includes: calculating the hit frequency of each statement template, and updating the arrangement order of multiple statement templates in the statement template cache based on the hit frequency of the statement templates.

[0014] Optionally, the statement template is stored in association with the execution plan cache data, and the execution plan cache data includes the execution plan information corresponding to different variable information settings for the variable identifier of the statement template.

[0015] Optionally, when different sub-statements hit the same statement template, the variable identifiers in the statement template correspond to the variable information in different sub-statements; when one sub-statement hits one statement template, the variable identifiers in the statement template correspond to the variable information in the sub-statement.

[0016] The step of determining the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template includes: matching the execution plan information corresponding to the variable information in each sub-statement in the execution plan cache data corresponding to the statement template.

[0017] Optionally, the execution plan information includes the database name and the table name;

[0018] The method further includes: when the execution plan information of different sub-statements that hit the same statement template includes the same database name, the different sub-statements that hit the same statement template are joined to obtain a joined execution statement.

[0019] Optionally, the method further includes: statistically analyzing the access frequency of the physical table and the physical indicators of the physical table, and generating analysis information of the physical table based on the access frequency and physical indicators of the physical table. The analysis information includes one or more of the following: the rationality of the partitioning of the physical table, the demand information for expansion or reduction, and the demand information for read-write separation.

[0020] According to another aspect of the present invention, a statement execution apparatus for a distributed database is provided, comprising:

[0021] The statement acquisition module is used to acquire a statement to be executed, wherein the statement to be executed includes at least one substatement, and each substatement includes variable information;

[0022] The statement template matching module is used to determine the statement template matched by each sub-statement in the statement template cache library, wherein the statement template includes variable identifiers;

[0023] The target execution plan information determination module is used to determine the execution plan cache data corresponding to the statement template, determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template, and determine the target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement.

[0024] The statement execution module is used to execute the statement to be executed based on the target execution plan information.

[0025] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0026] At least one processor; and

[0027] A memory communicatively connected to the at least one processor; wherein,

[0028] The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute the statement execution method of the distributed database according to any embodiment of the present invention.

[0029] According to another aspect of the present invention, a computer-readable storage medium is provided, the computer-readable storage medium storing computer instructions, the computer instructions being configured to cause a processor to execute the statement execution method of the distributed database according to any embodiment of the present invention.

[0030] The technical solution of this invention, for a statement to be executed, determines the statement template that each sub-statement in the statement to be executed matches. The statement template includes variable identifiers, which can improve the hit rate of the statement template. Multiple sub-statements can match the same statement template, allowing for statement logic optimization of the statement to be executed. For each statement template matched by the statement to be executed, the execution plan information of the sub-statement is obtained by matching the variable information in the sub-statement with the execution plan cache data corresponding to the statement template. The execution plan information of at least one sub-statement can form the target execution plan information of the statement to be executed. This simplifies the process of determining the target execution plan information of the statement to be executed and reduces the execution time of the statement to be executed.

[0031] It should be understood that the description in this section is not intended to identify key or essential features of the embodiments of the present invention, nor is it intended to limit the scope of the invention. Other features of the invention will become readily apparent from the following description. Attached Figure Description

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

[0033] Figure 1 This is a flowchart of a statement execution method for a distributed database provided in an embodiment of the present invention;

[0034] Figure 2 This is a schematic diagram of the structure of a statement execution device for a distributed database provided in an embodiment of the present invention;

[0035] Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0036] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0037] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0038] In this embodiment, the distributed database includes multiple database shards, and each database shard may include multiple tables. The same physical table may include multiple tables, and these tables can be distributed across at least one database shard. By splitting a large physical table into multiple smaller tables and distributing these smaller tables across multiple database shards, the load on a single database shard or table is reduced, improving query performance and concurrency capabilities. Common database sharding and table partitioning strategies include horizontal partitioning and vertical partitioning. Horizontal partitioning distributes data across different tables or databases based on the value of a specific field, while vertical partitioning splits a field from a single table across different tables or databases. No specific database sharding and table partitioning strategy is limited here.

[0039] The process of executing a SQL (Structured Query Language) statement in a database typically includes several stages: lexical analysis, syntax analysis, semantic analysis, query rewriting, execution plan generation, and actual execution. In this embodiment, the execution process of SQL statements is simplified and execution time is reduced through statement logic optimization and physical execution optimization. Statement logic optimization is achieved by setting up a statement template cache library to cache statement templates, and physical execution optimization is achieved by caching the execution plan corresponding to the statement templates. This reduces the execution time of statements without modifying physical tables or distributed rules.

[0040] Figure 1 This is a flowchart of a statement execution method for a distributed database provided by an embodiment of the present invention. This embodiment is applicable to situations where execution plan cache data corresponding to statement templates is pre-cached, and upon receiving a statement to be executed, variable identifiers in the statement template are replaced, and the corresponding execution plan information is quickly matched in the execution plan cache data corresponding to the statement template based on the variable identifiers. This method can be executed by a statement execution device for a distributed database, which can be implemented in hardware and / or software, and can be configured in a computer or server. Figure 1 As shown, the method includes:

[0041] S110. Obtain the statement to be executed, wherein the statement to be executed includes at least one sub-statement, and each sub-statement includes variable information.

[0042] S120. Determine the statement template that each sub-statement hits in the statement template cache library, wherein the statement template includes a variable identifier.

[0043] S130. Determine the execution plan cache data corresponding to the statement template, and determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template.

[0044] S140. Determine the target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement; execute the statement to be executed based on the target execution plan information.

[0045] In this embodiment, the statement to be executed can be an SQL statement, such as an access statement for distributed data, including but not limited to read and write statements, where the read statement can be a query statement. The specific content of the statement to be executed can be determined based on the content of the physical tables in the distributed database.

[0046] It is understandable that the statement to be executed can be a long statement, including at least one sub-statement. Each sub-statement can include the statement core and variable information. For example, the sub-statement is `elect name from t_user where id=1`, where "elect name from t_user" is the core statement and "where id=1" is the variable information. The sub-statement can include one or more variable information. The execution plan information of the sub-statement is the access routing information of the distributed database, including the access location of the sub-statement, such as the database shard name and table shard name.

[0047] The target execution plan information for a long statement is determined based on the execution plan information corresponding to at least one sub-statement included in the long statement. Accordingly, the target execution plan information for a long statement can be a set of routing information for the statement to be executed. Specifically, the target execution plan information for the statement to be executed is determined by splitting the statement to be executed into at least one sub-statement and determining the execution plan information corresponding to each sub-statement.

[0048] Optionally, the statement to be executed is parsed to generate a syntax tree. Each subtree of the syntax tree corresponds to a substatement. By parsing the statement to be executed into a syntax tree, the substatements corresponding to each subtree are identified, thus obtaining at least one substatement included in the statement to be executed. Each subtree of the syntax tree is traversed to obtain the corresponding substatement. The statement template matched by each substatement is determined from the statement template cache.

[0049] The statement template cache includes multiple statement templates used to simplify sub-statements within the statement to be executed. Each sub-statement is iterated over, matched against the statement template cache, to determine the matching statement template.

[0050] It is understandable that the statement template cache library is pre-created. The process of creating the statement template cache library can be as follows: retrieving historical execution statements, parsing the historical execution statements, generating a syntax tree corresponding to the historical execution statements, pruning the syntax tree, and generating a statement template based on each subtree in the pruned syntax tree. The pruning process can be used to remove unnecessary structure and variable information. Variable information is located in the leaf nodes of the syntax tree. Optionally, the leaf nodes of the syntax tree are judged; if a leaf node contains variable information, the leaf node is removed. Optionally, pruningable fields are pre-defined, and these pruningable fields are matched against the syntax tree to determine the pruningable fields. The syntax tree is then pruned based on the successfully matched pruningable fields. For example, the historical statement `electname from t_user where id=1` is pruned to "elect name from t_user". The variable information in the historical statement is replaced with variable identifiers, for example, `where id=vtg_0`. The specific form of the variable identifiers is not limited and can be determined according to the setting requirements.

[0051] For example, sub-statement 1 is `select name from t_user where id=1`, and sub-statement 2 is `select name from t_user where id=2`. Sub-statements 1 and 2 share the same statement template, which could be `select name from t_user where id=vtg_0`. By determining the statement template corresponding to each sub-statement, sub-statements with the same core structure but different variable information can all be matched to the same template. The execution plan information for these sub-statements with the same core structure but different variable information is determined using the same template, eliminating the need to repeatedly determine the execution plan information for each different sub-statement. Furthermore, sub-statements with the same core structure but different variable information share the same statement logic; matching the same statement template optimizes the statement logic and reduces the time consumed by repeatedly determining the execution plan information for identical or similar statements.

[0052] In some embodiments, different variable identifiers correspond to different types of variable information, and the variable identifiers corresponding to each type of variable information are pre-set. Accordingly, the statement template includes variable identifiers corresponding to different types of variable information. For example, the sub-statement `select user.id,order.*,user.col1 from t_order order join t_user user on order.user_id=user.id and name="1234"` will be transformed into: `select t_vtg_1.id,t_vtg_0.*,t_vtg_1.col1 from t_vtg_0join t_vtg_1on t_vtg_0.user_id=t_vtg_1.id and t_vtg_0.name=vtg_0`. Here, the scalar identifier corresponding to the variable information "user.id" is "t_vtg_1.id", and the scalar identifier corresponding to the variable information "order" is "t_vtg_0". By setting different variable identifiers to distinguish different types of variable information, the process of quickly matching can be achieved in determining execution plan information.

[0053] In some embodiments, the matching process of a substatement in the statement template cache may be as follows: identifying the variable information and statement core of the substatement, matching the statement core of the substatement in the statement template cache, and determining the matching statement template. Alternatively, the matching process of a substatement in the statement template cache may be as follows: identifying the variable information and statement core of the substatement, replacing the variable information with variable identifiers, matching the replaced substatement in the statement template cache, and determining the matching statement template.

[0054] Optionally, the statement template cache includes multiple statement templates arranged sequentially. Correspondingly, determining the statement template that matches each sub-statement in the statement template cache includes: for each sub-statement, based on the arrangement order of the statement templates in the statement template cache, sequentially matching the sub-statement with each statement template until a matching statement template for the sub-statement is determined. Specifically, for any sub-statement, the sub-statement is matched with the first statement template in the statement template cache; if the match fails, the sub-statement is matched with the next statement template in the statement template cache until a matching statement template for the sub-statement is determined, and the matching process for the sub-statement stops.

[0055] In some embodiments, multiple statement templates in the statement template cache are arranged based on the hit frequency of each statement template. The higher the hit frequency of a statement template, the earlier it is arranged, in order to improve the matching efficiency of substatements in the statement template cache and reduce the matching time of substatements.

[0056] Optionally, the hit frequency of each statement template is statistically analyzed, and the arrangement order of multiple statement templates in the statement template cache is updated based on the hit frequency. For example, the hit frequency of each statement template can be statistically analyzed according to a preset time interval to update the arrangement order of multiple statement templates in the statement template cache. The preset time interval can be 1 hour or 1 day, etc., and can be determined according to the update requirements of the statement template cache. The update frequency of the statement template cache can be positively correlated with the access volume of the distributed database; the higher the access volume of the distributed database, the smaller the preset time interval. By increasing the update frequency of the statement template cache, the accuracy of the hit frequency of statement templates in the statement template cache is improved, and the matching time of sub-statements is reduced.

[0057] If any sub-statement does not have a matching statement template in the statement template cache, a statement template for the sub-statement is generated based on lexical analysis, syntax analysis, semantic analysis, and query rewriting. Alternatively, a prompt message is generated to display the sub-statement to the operator, and the operator can input their own statement template. The newly generated statement template is then added to the statement template cache to continuously update the cache.

[0058] After determining the statement template matched by each sub-statement in the statement to be executed, the matched statement templates are deduplicated to avoid repeating the process for duplicate statement templates. When different sub-statements match the same statement template, the variable identifiers in the statement template correspond to the variable information in different sub-statements; when one sub-statement matches one statement template, the variable identifiers in the statement template correspond to the variable information in that sub-statement. For example, sub-statement 1 is `select name from t_user where id = 1`, and sub-statement 2 is `select name from t_user where id = 2`. Sub-statements 1 and 2 have the same statement template. Accordingly, the variable information corresponding to `vtg_0` in the statement template `selectname from t_user where id = vtg_0` includes 1 and 2.

[0059] Execution plan cache data is pre-configured for each statement template. This cache data includes execution plan information corresponding to different variable identifiers for each statement template. The statement templates and execution plan cache data are stored in association, for example, via key-value pairs, where the statement template is the key and the execution plan cache data is the value. Both can be stored in a statement template cache library. By caching the execution plan cache data for each statement template, the execution plan information corresponding to different variable identifiers within the statement template can be quickly determined from the cache data. This replaces the calculation process for execution plan information, simplifying the determination process and reducing the time spent on determining execution plan information.

[0060] For example, the cached execution plan data corresponding to the statement template select name from t_user where id=vtg_0 includes execution plan information when vtg_0 represents different variable information, such as the execution plan information corresponding to vtg_0=1, the execution plan information corresponding to vtg_0=2, etc.

[0061] The execution plan information may include one or more database names and table names. When the statement template includes multiple variable identifiers, different variable identifiers correspond to different local information. The local information corresponding to different variable identifiers can form the execution plan information.

[0062] Optionally, determining the execution plan information for each sub-statement based on the variable information corresponding to the variable identifiers in the statement template within the execution plan cache data includes: matching the execution plan information corresponding to the variable information in each sub-statement in the execution plan cache data corresponding to the statement template. For example, if the variable information corresponding to vtg_0 in a sub-statement is 1, the execution plan information corresponding to vtg_0 = 1 is determined in the execution plan cache data corresponding to the statement target, and used as the execution plan information for the sub-statement. For example, the statement template includes multiple variable identifiers, such as "vtg_0" and "t_vtg_1.id", and the execution plan cache data corresponding to the statement template includes the local execution plan information corresponding to each variable identifier under different variable information. The variable information in the sub-statement is used to replace the corresponding variable identifier to determine the local execution plan information corresponding to the variable information in that sub-statement. For example, in the substatement, `vtg_0` can be `1234`, and the local execution plan information corresponding to `1234` is determined from the multiple local execution plan information corresponding to `vtg_0`; in the substatement, `t_vtg_1.id` can be `user.id`, and the local execution plan information corresponding to `user.id` is determined from the multiple local execution plan information corresponding to `t_vtg_1.id`. The local execution plan information corresponding to the multiple variables in the substatement forms the execution plan information of the substatement.

[0063] The execution plan cache data corresponding to each statement template is pre-set. Optionally, the execution plan cache data corresponding to the statement template can be set by: obtaining multiple historical statements corresponding to each statement template, wherein the historical statements include different variable information, establishing a correspondence between the variable information in the historical statements and the historical routing information of the historical statements, and forming the execution plan cache data corresponding to that statement template.

[0064] Optionally, the execution plan cache data corresponding to the statement template can be set by: obtaining multiple variable information identified by variable identifiers in the statement template. Here, multiple variable information refers to configurable variable information identified by variable identifiers, such as samples obtained within the parameter range of the variable identifier or values ​​determined by the variable identifier in historical statements. Through the calculation rules of the execution plan information, the execution plan information corresponding to each of the multiple variable identifiers is obtained. A correspondence is established between the variable information and the execution plan information to obtain the execution plan cache data corresponding to the statement template. The calculation rules for the execution plan information are not limited here; for example, they can be hash processing rules or pre-configured calculation rules.

[0065] The execution plan information of multiple sub-statements is used to generate target self-plan information for the statement to be executed. This target self-plan information is then used to execute the statement, yielding corresponding response data. Specifically, the access location of each sub-statement is determined based on its execution plan information, and the sub-statement is executed at that location in the distributed database. If the statement to be executed is a query, the response data is the data retrieved from the distributed database. For example, if the statement to be executed is a write statement, the response data could be feedback indicating whether the content to be written in the write statement has been successfully written to the distributed database.

[0066] Based on the above embodiments, if the execution plan information of different sub-statements that hit the same statement template includes the same database name, it indicates that the access locations of the different sub-statements that hit the same statement template are located in the same database. The different sub-statements that hit the same statement template are joined to obtain a single joined execution statement, reducing network I / O. Specifically, different sub-statements that hit the same statement template are joined using join fields, where join fields include, but are not limited to, union and join.

[0067] For example, in the statement template `select * from t_vtg_0`, the variable parameter is `t_user_0` in sub-statement 1 and `t_user_1` in sub-statement 2. These two variable parameters indicate that the two sub-statements access the same database shard; that is, the execution plan information determined by these two variable parameters includes the same database shard name. The two sub-statements are joined using the `union` field to obtain the joined execution statement, such as `select * from t_user_0 union select * from t_user_1`. By joining sub-statements with the same database shard name, the number of database accesses can be reduced, thus reducing statement execution time.

[0068] During the execution of a join statement, to avoid Cartesian products, the join tables to be eliminated are determined based on the table information in the sharded database accessed by the join statement, and the processing of these join tables is canceled. A join table to be processed is generated based on the table names accessed by the two sub-statements in the join statement. This join table includes either table accessed by the first sub-statement or either table accessed by the second sub-statement. For a join table to be processed, if there is no join relationship between the two tables and both tables are not empty, then it is determined to be a join table to be eliminated. The join relationship can be determined based on table information, which may include, but is not limited to, table names and field contents. The fields in the table can be the query fields in the sub-statements. It is understandable that if there is no join relationship between the join tables, the execution result of the join statement is empty, meaning the join statement execution process is invalid, which affects the efficiency of the statement execution. For example, if the table names contain specific name information, it can indicate that the two tables have a join relationship; or, if each table contains specific information fields, it can indicate that the two tables have a join relationship.

[0069] For example, in the join tables t_1Xo_1, t_1Xo_2, t_2Xo_1, and t_2Xo_2 to be processed, t_1Xo_2 and t_2Xo_1 are identified as join tables to be eliminated. Accordingly, the join tables processed by the join execution statement are optimized to t_1Xo_1 and t_2Xo_2. Here, t_1 and t_2 can be the table names accessed by the first sub-statement in the join execution statement, and o_1 and o_2 can be the table names accessed by the second sub-statement in the join execution statement. Here, it is determined by the table names that t_1 and o_2 do not have a join relationship, and t_2 and o_1 do not have a join relationship. Therefore, t_1Xo_2 and t_2Xo_1 are identified as join tables to be eliminated, thereby reducing the number of accesses during the execution of the join execution statement and improving the execution efficiency of the statement.

[0070] The technical solution of this embodiment addresses the issue that the sub-statements constituting the statement to be executed include repetitive or similar statements. For the statement to be executed, a statement template matching each sub-statement is determined. This statement template includes variable identifiers, which improves the hit rate of the statement template. Multiple sub-statements can hit the same statement template, allowing for statement logic optimization of the statement to be executed. For each statement template matched by the statement to be executed, the execution plan information of the sub-statement is obtained by matching the variable information in the execution plan cache data corresponding to the statement template. The execution plan information of at least one sub-statement can form the target execution plan information of the statement to be executed. This simplifies the process of determining the target execution plan information of the statement to be executed and reduces the execution time of the statement to be executed.

[0071] Based on the above embodiments, the access frequency of the physical table and the physical indicators of the physical table are statistically analyzed. Based on the access frequency and physical indicators of the physical table, analysis information of the physical table is generated. The analysis information includes one or more of the following: the rationality of the physical table splitting, the demand information for expansion or reduction, and the demand information for read-write separation.

[0072] The access frequency of each physical table is statistically analyzed based on a preset time interval. The access frequency of a physical table can be determined by analyzing the target execution plan information of the access statements within the preset time period. This involves identifying the physical table to which the sub-table name belongs in the target execution plan information, counting the physical tables corresponding to the access statements, and thus determining the access frequency of each physical table. Optionally, the access frequency of each physical table can be generated into a visual graph for intuitive display.

[0073] Physical table metrics include, but are not limited to, table size, disk I / O, and distribution across database shards. Analysis information for the physical table is generated based on the match between its access frequency and these physical metrics. A higher access frequency indicates greater access pressure on the physical table. A mismatch between the physical metrics and access frequency negatively impacts access performance. Multiple access frequency ranges are pre-defined, and each range corresponds to a range of physical metrics. The access frequency range to which the physical table's access frequency belongs and the range to which its physical metrics belong are determined, and the two values ​​are checked for a match. If not, the analysis result for the physical metrics is "mismatch."

[0074] For example, if the access frequency of a physical table does not match the form size, expansion or contraction request information is generated. The expansion request information prompts the physical table to expand, while the contraction request information prompts the physical table to contract. Correspondingly, expansion or contraction time periods can be determined based on the access frequency of the physical table at different times, and the physical table can be expanded or contracted during these time periods.

[0075] For example, if the access frequency of a physical table does not match the distribution of data across database shards, that is, if the access frequency of a physical table does not match the number of database shards to which the physical table is distributed (e.g., the physical table has a high access frequency but a small number of database shards), an analysis result indicating that the physical table splitting is unreasonable will be generated, prompting the physical table to be split again.

[0076] For example, if the access frequency of a physical table exceeds a preset threshold, it is determined whether the physical table is in a read-write separation state. If not, a read-write separation analysis result is generated to prompt the material table to perform read-write analysis and reduce the access pressure on the material table.

[0077] Figure 2This is a schematic diagram of the structure of a statement execution device for a distributed database provided in an embodiment of the present invention. Figure 2 As shown, the device includes:

[0078] The statement acquisition module 210 is used to acquire a statement to be executed, wherein the statement to be executed includes at least one sub-statement, and each sub-statement includes variable information.

[0079] The statement template matching module 220 is used to determine the statement template matched by each of the sub-statements in the statement template cache library, wherein the statement template includes variable identifiers;

[0080] The target execution plan information determination module 230 is used to determine the execution plan cache data corresponding to the statement template, determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template, and determine the target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement.

[0081] The statement execution module 240 is used to execute the statement to be executed based on the target execution plan information.

[0082] The technical solution of this embodiment, for a statement to be executed, determines the statement template that each sub-statement in the statement to be executed matches. The statement template includes variable identifiers, which can improve the hit rate of the statement template. Multiple sub-statements can match the same statement template, and the statement logic of the statement to be executed is optimized. For each statement template matched by the statement to be executed, the execution plan information of the sub-statement is obtained by matching the variable information in the sub-statement with the execution plan cache data corresponding to the statement template. The execution plan information of at least one sub-statement can form the target execution plan information of the statement to be executed. This simplifies the process of determining the target execution plan information of the statement to be executed and reduces the execution time of the statement to be executed.

[0083] Based on the above embodiments, optionally, the statement template matching module 220 is used to: generate a syntax tree of the statement to be executed, wherein each subtree of the syntax tree corresponds to a substatement; traverse the substatements in the syntax tree, and determine the statement template that is matched in the statement template cache library for each substatement.

[0084] Optionally, the statement template cache library includes multiple statement templates arranged in sequence;

[0085] The statement template matching module 220 is further configured to: for each sub-statement, based on the arrangement order of statement templates in the statement template cache library, sequentially match the sub-statement with each statement template until the statement template to which the sub-statement is matched is determined.

[0086] Optionally, the device further includes a cache library update module, used to count the hit frequency of each statement template and update the arrangement order of multiple statement templates in the statement template cache library based on the hit frequency of the statement templates.

[0087] Based on the above embodiments, optionally, the statement template is stored in association with the execution plan cache data, and the execution plan cache data includes the execution plan information corresponding to different variable information settings for the variable identifier of the statement template.

[0088] Optionally, when different sub-statements hit the same statement template, the variable identifiers in the statement template correspond to the variable information in different sub-statements; when one sub-statement hits one statement template, the variable identifiers in the statement template correspond to the variable information in the sub-statement.

[0089] Optionally, the target execution plan information determination module 230 is used to: match the execution plan information corresponding to the variable information in each sub-statement from the execution plan cache data corresponding to the statement template.

[0090] Based on the above embodiments, optionally, the execution plan information includes the database name and the table name;

[0091] The statement execution module 240 is also used to: when the execution plan information of different sub-statements that hit the same statement template includes the same database name, perform connection processing on the different sub-statements that hit the same statement template to obtain a connection execution statement.

[0092] Based on the above embodiments, optionally, the device further includes an access analysis module, used to: count the access frequency of the physical table and the physical indicators of the physical table, and generate analysis information of the physical table based on the access frequency and the physical indicators of the physical table, the analysis information including one or more of the rationality of the partitioning of the physical table, the demand information for expansion or reduction, and the demand information for read-write separation.

[0093] The statement execution device for distributed databases provided in this embodiment of the invention can execute the statement execution method for distributed databases provided in any embodiment of the invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0094] Figure 3This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. The electronic device 10 is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (such as helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0095] like Figure 3 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12 or a random access memory (RAM) 13, communicatively connected to the at least one processor 11. The memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes based on the computer program stored in the ROM 12 or loaded from storage unit 18 into the RAM 13. The RAM 13 may also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0096] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0097] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as statement execution methods in a distributed database.

[0098] In some embodiments, the statement execution method for a distributed database may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the statement execution method for a distributed database described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the statement execution method for a distributed database by any other suitable means (e.g., by means of firmware).

[0099] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0100] Computer programs for implementing the statement execution method of the distributed database of the present invention can be written in any combination of one or more programming languages. These computer programs can be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be implemented. The computer programs can be executed entirely on the machine, partially on the machine, as a standalone software package partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0101] This invention also provides a computer-readable storage medium storing computer instructions for causing a processor to execute a statement execution method for a distributed database, the method comprising:

[0102] Obtain the statement to be executed, which includes at least one sub-statement, and each sub-statement includes variable information; determine the statement template that each sub-statement matches in the statement template cache, the statement template including variable identifiers; determine the execution plan cache data corresponding to the statement template, and determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifiers in the statement template; determine the target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement; execute the statement to be executed based on the target execution plan information.

[0103] In the context of this invention, a computer-readable storage medium can be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination thereof. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0104] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0105] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or computing systems that include middleware components (e.g., application servers), or computing systems that include frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0106] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.

[0107] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0108] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for executing statements in a distributed database, characterized in that, include: Obtain the statement to be executed, which includes at least one substatement, and each substatement includes variable information; In the statement template cache library, the statement template to which each sub-statement is matched is determined. The statement template includes variable identifiers. The statement template cache library includes multiple statement templates arranged in sequence. Sub-statements with the same statement core but different variable information match the same statement template. The execution plan cache data corresponding to the statement template is determined. Based on the variable information corresponding to the variable identifier in the statement template, the execution plan information corresponding to each sub-statement is determined in the execution plan cache data. The same statement template is used to determine the execution plan information of sub-statements with the same statement trunk but different variable information. The target execution plan information corresponding to the statement to be executed is determined based on the execution plan information corresponding to at least one sub-statement; the statement to be executed is then executed based on the target execution plan information. The creation process of the statement template cache library is as follows: obtain historical execution statements, parse the historical execution statements, generate a syntax tree corresponding to the historical execution statements, perform pruning on the syntax tree corresponding to the historical execution statements, and generate a statement template based on each subtree in the pruned syntax tree. The pruning is used to remove unnecessary structure and variable information. The step of determining the statement template matched by each sub-statement in the statement template cache library includes: For each sub-statement, based on the arrangement order of statement templates in the statement template cache library, the sub-statement is matched with each statement template in turn until the statement template that the sub-statement matches is determined. The multiple statement templates in the statement template cache library are arranged based on the hit frequency of each statement template. The hit frequency is obtained by statistics based on a preset time interval. The higher the access volume of the distributed database, the smaller the preset time interval. The method further includes: The system collects statistics on the access frequency and physical metrics of the physical table. Based on these statistics, it generates analysis information for the physical table, including one or more of the following: the rationality of the physical table's partitioning, the need for expansion or contraction, and the need for read-write separation. The access frequency of the physical table is determined based on the target execution plan information of access statements within a preset time period.

2. The method according to claim 1, characterized in that, The step of determining the statement template matched by each sub-statement in the statement template cache library includes: Generate a syntax tree for the statement to be executed, wherein each subtree of the syntax tree corresponds to a substatement; Traverse the sub-statements in the syntax tree and determine the statement template that is matched in the statement template cache for each sub-statement.

3. The method according to claim 1 or 2, characterized in that, The method further includes: calculating the hit frequency of each statement template, and updating the arrangement order of multiple statement templates in the statement template cache based on the hit frequency of the statement templates.

4. The method according to claim 1, characterized in that, The statement template is stored in association with the execution plan cache data, which includes the execution plan information corresponding to different variable information settings for the variable identifiers of the statement template.

5. The method according to claim 4, characterized in that, When different sub-statements hit the same statement template, the variable identifiers in the statement template correspond to the variable information in different sub-statements; when one sub-statement hits one statement template, the variable identifiers in the statement template correspond to the variable information in the sub-statement. The step of determining the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template includes: In the execution plan cache data corresponding to the statement template, the execution plan information corresponding to the variable information in each sub-statement is obtained by matching.

6. The method according to claim 1, characterized in that, The execution plan information includes one or more of the database name and table name; The method further includes: when the execution plan information of different sub-statements that hit the same statement template includes the same database name, the different sub-statements that hit the same statement template are joined to obtain a joined execution statement.

7. A statement execution device for a distributed database, characterized in that, include: The statement acquisition module is used to acquire a statement to be executed, wherein the statement to be executed includes at least one substatement, and each substatement includes variable information; The statement template matching module is used to determine the statement template matched by each sub-statement in the statement template cache library. The statement template includes variable identifiers. The statement template cache library includes multiple statement templates arranged in sequence. Sub-statements with the same statement core but different variable information match the same statement template. The target execution plan information determination module is used to determine the execution plan cache data corresponding to the statement template, and to determine the execution plan information corresponding to each sub-statement in the execution plan cache data based on the variable information corresponding to the variable identifier in the statement template; and to determine the target execution plan information corresponding to the statement to be executed based on the execution plan information corresponding to at least one sub-statement respectively, wherein the same statement template is used to determine the execution plan information of sub-statements with the same statement trunk but different variable information; The statement execution module is used to execute the statement to be executed based on the target execution plan information; The creation process of the statement template cache library is as follows: obtain historical execution statements, parse the historical execution statements, generate a syntax tree corresponding to the historical execution statements, perform pruning on the syntax tree corresponding to the historical execution statements, and generate a statement template based on each subtree in the pruned syntax tree. The pruning is used to remove unnecessary structure and variable information. The statement template matching module is also used for: For each sub-statement, based on the arrangement order of statement templates in the statement template cache library, the sub-statement is matched with each statement template in turn until the statement template that the sub-statement matches is determined. The multiple statement templates in the statement template cache library are arranged based on the hit frequency of each statement template. The hit frequency is obtained by statistics based on a preset time interval. The higher the access volume of the distributed database, the smaller the preset time interval. The device further includes an access analysis module, used to: count the access frequency of the physical table and the physical indicators of the physical table, and generate analysis information of the physical table based on the access frequency and physical indicators of the physical table. The analysis information includes one or more of the following: the rationality of the partitioning of the physical table, the demand information for expansion or reduction, and the demand information for read-write separation. The access frequency of the physical table is determined according to the target execution plan information of the access statements within a preset time period.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the statement execution method of the distributed database according to any one of claims 1-6.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement the statement execution method of the distributed database according to any one of claims 1-6.

Images