Method and device for accurately matching database execution plan, equipment and storage medium

Abstract

The invention belongs to the technical field of big data and the field of medical treatment and health, and discloses a method, device and equipment for accurately matching a database execution plan and a storage medium. Generating a semantic tree corresponding to the to-be-executed statement; according to the semantic tree, generating a semantic identifier for representing the semantics of the semantic tree; obtaining an execution plan corresponding to the semantic identifier in a preset execution plan storage library; and determining the execution plan corresponding to the semantic identifier as a to-be-executed plan corresponding to the to-be-executed statement. The invention provides a method, a device and equipment for accurately matching a database execution plan and a storage medium. The problem that the execution plan corresponding to a query statement is difficult to determine by utilizing a plan caching mechanism is solved.

Description

Technical Field

[0001] The present invention relates to the fields of big data and healthcare, and particularly to a method, apparatus, device, and storage medium for accurately matching a database execution plan. Background Art

[0002] In today's digital age, industries' reliance on databases has become increasingly profound. Taking the healthcare insurance industry as an example, customers not only expect databases to achieve basic data storage and retrieval but also impose extremely high requirements on their security performance, operation efficiency, and reliability. For healthcare insurance companies, customer information, policy data, claim records, etc. are all stored in the database. Any data error or performance issue may affect customer rights and interests and the company's normal operation. Therefore, the accuracy of the execution plan is crucial for database performance.

[0003] When determining the execution plan, dynamic programming is a commonly used method. It breaks complex problems into small problems, solves the small problems, and remembers the answers to avoid repeated calculations and improve efficiency. The database will evaluate different execution steps and paths based on information such as the data volume of the table, index situation, query conditions of the query statement, and connection method, and select the plan with the lowest cost. However, if the data changes significantly, the statistical information is not updated in a timely manner, or the query statement is too complex and there are too many indexes, an incorrect execution plan may be calculated, resulting in deteriorated database performance. For example, when a healthcare insurance company queries a large amount of customers' health claim data, the database will generate an execution plan based on algorithms and accurate statistical information, such as the claim frequency of customers in different age groups and the distribution of claim amounts for common diseases. When the algorithm is reliable and the statistical information is accurate, a relatively accurate execution plan can usually be generated to complete data queries quickly and efficiently, providing strong support for insurance business decisions. However, if there are large-scale data changes in the database, such as the entry of a large amount of customer information or data updates due to claim policy adjustments, and the statistical information is not updated in a timely manner before scheduling, a time window with inaccurate statistical information will occur. In addition, if the query statement is too complex, for example, it is necessary to simultaneously associate multiple tables such as the customer information table, policy table, claim table, and medical expense details for complex claim data analysis, and there are too many indexes, resulting in a large number of available execution plan paths. In the case of poor algorithms or incorrect statistical information, the error in cost estimation will increase exponentially, and ultimately the selected optimal execution plan may be inaccurate, affecting the efficiency and accuracy of data analysis, and further affecting important operations such as insurance product pricing and risk assessment.

[0004] To address the problem of sub-optimal execution plans, most databases on the market currently adopt an execution plan caching mechanism. In the medical and health insurance scenario, when an insurance company frequently queries the health insurance participation status of customers in a specific region and age group, the execution plan generated during the first query is cached. This mechanism generally uses a dynamic replacement strategy, such as updating when there is a large increase in data, such as during a large-scale new business expansion activity when a large amount of new customer data floods in, or when there are permission changes, such as adjustments to data access permissions for different departments. However, there are certain limitations to the caching method. Plan caching typically uses a key-value pair method for retrieval, with the execution statement corresponding one-to-one to the execution plan. As long as there are minor changes to the execution statement, such as a change in the order of words in the statement, the corresponding execution plan cannot be matched. For example, when querying the claim amounts of customers in different age groups, changing the order of the query conditions may result in not hitting the cache. Summary of the Invention

[0005] The present invention provides a method, apparatus, device, and storage medium for accurately matching database execution plans to solve the problem of difficulty in determining the execution plan corresponding to a query statement using the plan caching mechanism.

[0006] In a first aspect, the present invention provides a method for accurately matching database execution plans, including:

[0007] Obtain a statement to be executed;

[0008] Generate a semantic tree corresponding to the statement to be executed;

[0009] Generate a semantic identifier for characterizing the semantics of the semantic tree based on the semantic tree;

[0010] Obtain the execution plan corresponding to the semantic identifier in a preset execution plan repository;

[0011] Determine the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed.

[0012] In a second aspect, the present invention provides an apparatus for accurately matching database execution plans, including:

[0013] A first acquisition module for obtaining a statement to be executed;

[0014] A first generation module for generating a semantic tree corresponding to the statement to be executed;

[0015] A second generation module for generating a semantic identifier for characterizing the semantics of the semantic tree based on the semantic tree;

[0016] A second acquisition module for obtaining the execution plan corresponding to the semantic identifier in a preset execution plan repository;

[0017] A determination module, configured to determine the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

[0018] In a third aspect, the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the steps of the method for accurately matching a database execution plan are implemented.

[0019] In a fourth aspect, the present invention provides a computer-readable storage medium storing a computer program, and when the computer program is executed by a processor, the steps of the method for accurately matching a database execution plan are implemented.

[0020] Moreover, for the solution implemented by the method for accurately matching a database execution plan, when querying data in a traditional database, the query statement (i.e., the to-be-executed statement) is used as the retrieval key value, but the limitations are very large. As long as there are some minor changes in the execution statement, such as a change in the order of words in the statement, the corresponding execution plan cannot be matched. In the embodiments of the present invention, when querying data using the to-be-executed statement, a semantic identifier capable of representing the semantics of the semantic tree corresponding to the to-be-executed statement is generated. Therefore, it can be understood that multiple to-be-executed statements with the same semantics will ultimately generate the same semantic identifier, and one semantics can be expressed by multiple to-be-executed statements. Also, the presence of dialects, synonyms, or context in the same statement may lead to inconsistent semantics, and spaces or wildcards such as '*' in the statement may lead to unclear semantics, resulting in inaccurate obtained execution plans. Therefore, using the semantic identifier that can reflect semantics as the retrieval value of the execution plan in the preset execution plan repository enables, when searching for the execution plan corresponding to the to-be-executed statement in the preset execution plan repository, as long as the to-be-executed statement and the execution plan in the execution plan repository express the same meaning, they can all be matched, and the matching is more extensive and accurate, thereby solving the problem that it is difficult to determine the execution plan corresponding to the query statement using the plan caching mechanism. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required to be used in the description of the embodiments of the present invention. Obviously, the following drawings are only some embodiments of the present invention, and those of ordinary skill in the art can also obtain other drawings without creative efforts based on these drawings.

[0022] Figure 1 is a flowchart of a method for accurately matching a database execution plan in an embodiment of the present invention;

[0023] Figure 2 isFigure 1 A flowchart of step S130 in

[0024] Figure 3 Another flowchart of the method for precisely matching database execution plans in an embodiment of the present invention;

[0025] Figure 4 is Figure 3 A flowchart of step S155 in

[0026] Figure 5 Another flowchart of the method for precisely matching database execution plans in an embodiment of the present invention;

[0027] Figure 6 Another flowchart of the method for precisely matching database execution plans in an embodiment of the present invention;

[0028] Figure 7 A structural diagram of the device for precisely matching database execution plans in an embodiment of the present invention;

[0029] Figure 8 A structural diagram of a computer device in an embodiment of the present invention;

[0030] Figure 9 Another structural diagram of a computer device in an embodiment of the present invention. Detailed implementation manners

[0031] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are some, rather than all, of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0032] Please refer to Figure 1 , the embodiments of the present invention provide a flowchart of a method for precisely matching database execution plans, including the following steps.

[0033] Step S110, obtain the statement to be executed.

[0034] It should be noted that in this step, the statement to be executed may be a query statement for querying data in the database.

[0035] As a specific example, in the field of medical and health insurance, the efficient operation of the database is crucial for business development, and the step S110 of obtaining the statement to be executed can play a key role in actual business. For example, when a medical and health insurance company needs to evaluate the health risks of customers in a specific age group, corresponding statements to be executed will be generated. If the company wants to understand the claims settlement situation of customers aged 30 to 40 due to major diseases in the past year, it needs to obtain relevant data from the database. First, it is necessary to associate the customer information table and the claims settlement record table, and establish a connection between the two through the customer identity information. Then, filter out the customer records of those aged 30 to 40 from the customer information table, and at the same time filter out the claims settlement records from the claims settlement record table where the claims settlement date is in the past year, that is, between January 1, 2024 and December 31, 2024, and the disease type belongs to the specific scope of major diseases, such as major disease 1, major disease 2, and major disease 3. Integrating these filtering conditions forms the statement to be executed. By executing this statement, the database can filter out the qualified data from the two tables, providing a data basis for the insurance company to analyze the health risks of customers in this age group and subsequent decisions such as insurance product pricing and risk control.

[0036] Step S120, generate a semantic tree corresponding to the statement to be executed.

[0037] Specifically, a semantic tree is a data structure constructed based on the statement to be executed. It uses a tree structure to clearly display various logical relationships and operation steps in the statement, which can help the database understand and execute the statement more efficiently and accurately. Among them, the semantic tree is organized in a tree structure and consists of nodes and edges. Nodes represent various semantic elements, such as concepts, entities, attributes, relationships, etc., and edges represent the semantic connections between these elements, intuitively showing the hierarchy and association of semantic information. The root node is the starting point of the semantic tree, representing the most general or top-level concept, and all other nodes are derived from the root node, reflecting the core or theme of the entire semantic structure. Starting from the root node, different branches are formed according to the subdivision and association of semantics. The nodes on each branch are the child nodes of the root node. The child nodes can further have their own child nodes, forming a multi-level structure for representing more specific and detailed semantic information, reflecting the gradual refinement and expansion of semantics. The leaf nodes are located at the end of the semantic tree and are nodes without child nodes, representing the most specific and bottom-level semantic elements, such as specific instances, specific values, or indivisible concepts, and are the specific bearers of semantic information in the semantic tree. In natural language processing, semantic trees can be used to analyze the semantic structure of sentences or texts, help understand the meaning of sentences, determine the semantic relationships between words, and perform grammar and semantic checks.

[0038] As a specific example, taking the field of medical and health insurance as an example, assume that the statement to be executed is to query the claim settlement situation of customers aged 30 - 40 due to major diseases in the past year. First, determine the subjects involved in the query. Here, there are two main parts, namely customer information and claim records. So, customer information and claim records can be used as the two main branch nodes of the semantic tree. Under the customer information branch, further refine the screening conditions. The age range of 30 - 40 years old is a key screening condition, which serves as a sub - node under the customer information branch. This means that when the database executes the query, it will first select customer records that meet this age range from the customer information. Under the claim record branch, also refine the conditions. The claim date range, that is, the past year (from January 1, 2024 to December 31, 2024), and the disease type being specific major diseases (Major Disease 1, Major Disease 2, Major Disease 3) are used as sub - nodes respectively. This indicates that the database will screen out claim information that meets the requirements in terms of time and disease type from the claim records. Then, since the query needs to associate customer information and claim records, there will be an association node in the semantic tree to represent connecting the information of the two branches through the customer identity information to ensure that the obtained claim records are for the corresponding customers.

[0039] Step S130, generate a semantic identifier for characterizing the semantics of the semantic tree according to the semantic tree.

[0040] Specifically, the semantic identifier is a kind of refined and encoded information that can concisely and accurately summarize the core semantic content contained in the semantic tree, facilitating subsequent storage, matching, and quick identification.

[0041] It should be noted that the method of generating a semantic identifier for characterizing the semantics of the semantic tree can be any implementable method, and no detailed limitation is made here.

[0042] As a specific example, taking the field of medical and health insurance as an example, assume that the semantic tree we constructed previously is used to query the claim settlement situation of customers aged 30 - 40 due to major diseases in the past year. First, sort out the key elements of the semantic tree. In this semantic tree, the key elements include the customer age range (30 - 40 years old), the claim settlement time range (from January 1, 2024 to December 31, 2024), the disease types (Major Disease 1, Major Disease 2, Major Disease 3), and the association relationship between customer information and claim settlement records (associated through customer identity information). Then, encode and integrate these key elements. A custom encoding rule can be adopted. For example: represent the age range with a specific code. Assume that "30 - 40 years old" is encoded as "AG3040"; the claim settlement time range "from January 1, 2024 to December 31, 2024" is encoded as "DT20240101 - 20241231"; the major disease types are combined with their first letters, and "Major Disease 1, Major Disease 2, Major Disease 3" are encoded as "DZ123"; the association relationship is represented by "CID_LINK" for customer identity information association. Finally, combine these encodings in a certain order to form a semantic identifier. For example: "AG3040_DT20240101 - 20241231_DZ123_CID_LINK". This semantic identifier concisely summarizes the core semantics of the semantic tree. When the database encounters a similar query requirement in subsequent processing, it can directly judge whether the previous processing results or execution plans can be reused by comparing the semantic identifier, greatly improving the efficiency of data processing. At the same time, when storing a large amount of information related to the semantic tree, the semantic identifier can also serve as an efficient index to facilitate quick positioning and management of the corresponding semantic content.

[0043] In some embodiments of the present invention, the semantic tree corresponding to the statement to be executed is the query tree corresponding to the statement to be executed.

[0044] Specifically, a query tree is a tree structure that focuses more on representing the statement to be executed from the perspective of database query execution. The nodes of the query tree usually correspond to database operations such as table scanning, data filtering, table joining, etc., and the edges represent the execution order and data flow of the operations. The query tree is an internal data structure constructed by the database query optimizer when processing query statements for planning the execution steps of the query.

[0045] In some embodiments of the present invention, as Figure 2 shown, step S130 includes:

[0046] Step S131, flatten the query tree and convert it into a first string;

[0047] Step S132: Perform a hash transformation on the first string to generate a hash value corresponding to the first string as the semantic identifier corresponding to the semantic tree.

[0048] Specifically, for step S131, converting the query tree into a flattened first string means organizing the information of the query tree in a tree structure into a linear string form in a specific order. In this process, it is necessary to retain the information of each node in the query tree and the relationships between them. For example, the traversal strategy of depth - first search or breadth - first search can be used to generate the first string. Taking depth - first search as an example, starting from the root node of the query tree, each node is recursively visited. When visiting a node, the identification information of the node (such as the operation, attribute, etc. represented by the node) is recorded in a certain format. If the node has child nodes, the information of the child nodes is recorded first, and then the connection relationship between the current node and the child nodes (for example, a specific symbol can be used to represent the parent - child relationship) is recorded. In this way, after traversing the entire query tree, a string containing all node information and their relationships, that is, the first string, can be obtained.

[0049] As a specific example, assume we have a query tree for querying the claim amount of customers aged 30 - 40 due to heart disease in 2024. The root node may be the "query" operation, and it has two child nodes, one is "customer information" and the other is "claim information". The "customer information" node has a child node "age range" with a value of "30 - 40 years old"; the "claim information" node has child nodes "claim time" with a value of "2024", "disease type" with a value of "heart disease", and a "claim amount" node. Using depth - first search for flattening transformation, first visit the root node "query" and record it as "Query"; then visit the "customer information" node and record it as "CustomerInfo", then visit the "age range" child node and record it as "AgeRange:30 - 40". After recording the child nodes, use a specific symbol (such as " / ") to represent the parent - child relationship, so this part is completely recorded as "CustomerInfo / AgeRange:30 - 40". Return to the "query" node, visit the "claim information" node and record it as "ClaimInfo", then sequentially visit its child nodes "claim time" and record it as "ClaimTime:2024", "disease type" and record it as "DiseaseType: heart disease", "claim amount" and record it as "ClaimAmount", and use " / " to represent the parent - child relationship. This part is recorded as "ClaimInfo / ClaimTime:2024 / DiseaseType: heart disease / ClaimAmount".

[0050] Specifically, for step S132, performing a hash transformation on the first string means using a hash function to map this string of any length to a hash value of a fixed length. The hash function is deterministic, that is, the same input string always results in the same hash value; at the same time, it is one-way, and it is very difficult to reverse-derive the original string from the hash value. Taking the first string obtained in step S131 as the input of the hash function, after calculation by the hash function, a hash value of a fixed length is output. This hash value is the semantic identifier corresponding to the semantic tree. For example, using the MD5 hash function, inputting the above first string "Query / CustomerInfo / AgeRange:30-40 / ClaimInfo / ClaimTime:2024 / DiseaseType: heart disease / ClaimAmount" into the MD5 function, the hash value "56f412f57a19a79f36c2c879d8d57a4f" is obtained, and this hash value can be used as the semantic identifier corresponding to the semantic tree.

[0051] It can be understood that since the query tree is the semantic tree of the statement to be executed, the query tree can reflect the semantics of the statement to be executed. Furthermore, the first string obtained by converting the query tree can also reflect the semantics of the statement to be executed. Therefore, it can be understood that as long as multiple statements to be executed have the same semantics, the first strings finally generated by them are the same. Therefore, the semantic identifiers finally obtained through hash transformation for statements to be executed with the same semantics are unique and determined, and can effectively identify the semantics of the statements to be executed.

[0052] Step S140, obtaining the execution plan corresponding to the semantic identifier in the preset execution plan repository.

[0053] Specifically, an execution plan repository can be preset in advance. In the execution plan repository, the semantic identifier corresponding to the query statement, the execution plan corresponding to the query statement, and the correspondence between the query statement, the semantic identifier, and the execution plan can be prestored. In this way, after determining the semantic identifier, the execution plan corresponding to the semantic identifier can be obtained in the preset execution plan repository.

[0054] Step S150, determining the execution plan corresponding to the semantic identifier as the execution plan corresponding to the statement to be executed.

[0055] In some embodiments of the present invention, as Figure 3 shown, after step S140 and before step S150, it further includes:

[0056] Step S151, determining a plurality of first data tables that are called simultaneously when the execution plan is executed and the first connection relationship between the plurality of first data tables;

[0057] Step S152: Generate a first structure tree including the first data table and the first connection relationship.

[0058] Step S153: Determine multiple second data tables and the second connection relationship of the multiple second data tables that are called simultaneously when the to-be-executed statement is executed.

[0059] Step S154: Generate a second structure tree including the second data table and the second connection relationship.

[0060] Step S155: When the semantics corresponding to the first structure tree and the semantics corresponding to the second structure tree are the same, determine the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

[0061] Specifically, for step S151, in a database execution plan, executing a query statement usually requires retrieving data from multiple data tables, and there are specific connection relationships between these data tables, such as being associated through a certain common field. This step is to clarify all the data tables (first data tables) involved in the execution plan and how these data tables are connected (first connection relationship). Further, for step S152, based on the first data table and the first connection relationship determined in step S151, construct a tree structure (first structure tree). The nodes of the tree represent data tables, and the edges represent the connection relationships between the data tables. Through this tree structure, the data table calls and connection logic of the execution plan can be more intuitively displayed. Further, for step S153, this step is similar to step S151, but for the current to-be-executed statement. It is necessary to analyze the semantics of the to-be-executed statement and determine all the data tables (second data tables) that need to be called simultaneously when executing this statement and the connection relationships (second connection relationships) between these data tables. Further, for step S154, similar to step S152, according to the second data table and the second connection relationship determined in step S153, construct a tree structure (second structure tree) to intuitively display the data table calls and connection logic of the to-be-executed statement. Further, for step S155, compare whether the semantics represented by the first structure tree and the second structure tree are the same. The same semantics means that the two structure trees have the same logical meaning in terms of data table calls and connection relationships, indicating that the query logic of the current to-be-executed statement is consistent with the query logic of an execution plan in the execution plan repository. If the semantics are the same, subsequent operations can be performed.

[0062] As a specific example, assume that the execution plan obtained from the preset execution plan repository is used to query the total claim amount of customers in a specific age range. When executing this plan, two data tables will be called simultaneously: the Customers table, which stores basic customer information such as customer identification information, name, age, etc.; and the Claims table, which stores claim information such as claim identification numbers, customer identification information, claim amounts, etc. The first connection relationship between these two tables is associated through Customers.CustomerID = Claims.CustomerID, that is, the customer information and its corresponding claim information are connected through the customer identification information field. Taking the Customers table and the Claims table as examples, we can construct a simple first structure tree. The root node of the tree is the query target (such as "query of total claim amount of customers in a specific age range"), and the next layer has two child nodes, namely the Customers table and the Claims table. The edge connecting these two child nodes is labeled with Customers.CustomerID = Claims.CustomerID to represent the connection relationship between the two tables. The statement to be executed is to query the total claim amount of customers aged 30 - 40 in the past year. When this statement is executed, the Customers table and the Claims table will be called. Similarly, their second connection relationship is also associated through Customers.CustomerID = Claims.CustomerID. The root node of the second structure tree is the query target of the statement to be executed ("query of total claim amount of customers aged 30 - 40 in the past year"), and the next layer also has two child nodes, namely the Customers table and the Claims table, and the connecting edge is labeled with Customers.CustomerID = Claims.CustomerID. In the above example, although the specific query conditions (such as age range, time range) of the first structure tree and the second structure tree may be different, they are the same in terms of the data table calls (both call the Customers table and the Claims table) and the connection relationship (both are connected through Customers.CustomerID = Claims.CustomerID), so it can be considered that they have the same semantics, and thus the next step can be executed.

[0063] Optionally, the first structure tree in steps S151 - S155 can be the connection tree corresponding to the execution plan, and the second structure tree can be the connection tree corresponding to the statement to be executed.

[0064] It can be understood that, based on steps S151 - S155, it is possible to further verify whether the semantics of the pre - stored execution plan obtained from the preset execution plan repository correspond to the semantics of the current statement to be executed, thereby avoiding the situation where the pre - stored execution plan obtained from the preset execution plan repository cannot correctly achieve the purpose that the statement to be executed ultimately needs to achieve, and thus ensuring the accuracy rate of the execution plan corresponding to the statement to be executed obtained.

[0065] Optionally, before step S151, an enable field can be set. This enable field is used to control whether to execute the execution plan obtained from the preset execution plan repository. When the enable field is valid, it can control the execution of the obtained execution plan; when the enable field is invalid, it can control not to execute the obtained execution plan. By setting the enable field, the control granularity can reach each execution plan.

[0066] In some embodiments of the present invention, as Figure 4 shown, step S155 is specifically:

[0067] Step S1551, when the nodes in the first structure tree are the same as the nodes in the second structure tree, determine the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed.

[0068] Specifically, when comparing whether the nodes of the first structure tree and the second structure tree are the same, multiple aspects need to be considered. The nodes represent data tables, so the same nodes mean that these data tables are consistent in multiple key attributes.

[0069] Specifically, when comparing whether the nodes of the first structure tree and the second structure tree are the same, the order of the nodes does not need to be distinguished.

[0070] It can be understood that when the nodes of the first structure tree and the second structure tree are the same, it means that there is a high degree of consistency in the use of data tables between the current statement to be executed and the existing execution plan. The database can directly reuse the existing execution plan, avoiding the overhead caused by regenerating the execution plan for new query statements. For example, in the medical and health insurance business, there are often various queries based on customer information and claim information. If an execution plan is regenerated for each query, it will consume a large amount of system resources and time. By reusing the execution plan through node comparison, the query efficiency can be significantly improved. Since the verified execution plan is reused, the consistency of query results can be ensured under the same data tables and connection relationships. This is particularly important in fields such as medical and health insurance that require extremely high data accuracy. For example, when calculating the customer claim amount, if different execution plans are used for each query, the calculation results may be inconsistent due to differences in the execution plans, thus affecting the normal operation of the insurance business. In a database, the optimization of the execution plan is a complex process that requires considering multiple factors. When the nodes are the same, the database can directly utilize the existing optimization experience and strategies without having to perform complex optimization analysis again. This not only reduces the workload of database administrators but also lowers the risk of errors during the optimization process, improving the stability and reliability of the database system.

[0071] In some embodiments of the present invention, as Figure 5 shown, after step S155, it further includes:

[0072] Step S156, when the semantics corresponding to the first structure tree and the semantics corresponding to the second structure tree are different, generate an execution plan to be executed corresponding to the statement to be executed based on a preset dynamic programming algorithm;

[0073] Step S157, obtain the tables and table indexes involved during the execution of the execution plan to be executed for the statement to be executed;

[0074] Step S158, flatten the second structure tree into a second string;

[0075] Step S159, store the second string, the execution plan to be executed for the statement to be executed, and the tables and table indexes in the preset execution plan repository.

[0076] Specifically, for step S156, when the semantic meanings of the first structure tree and the second structure tree are different, it means that the existing execution plan cannot be directly applied to the current statement to be executed. At this time, it is necessary to generate an execution plan suitable for the statement to be executed with the help of a preset dynamic programming algorithm. The dynamic programming algorithm decomposes a complex problem into a series of sub-problems, and by solving the sub-problems and recording their solutions, it avoids repeated calculations and thus efficiently finds the optimal solution. In the database, it is to comprehensively consider various possible execution paths based on information such as the query conditions of the statement to be executed and the data tables involved, calculate the cost of each path, and finally select the path with the lowest cost as the execution plan to be executed.

[0077] For step S157, after generating the execution plan to be executed, it is necessary to clarify which data tables will be involved in the execution process of this plan and the indexes used on these tables. The data table is the carrier for data storage, while the index is a data structure created to improve the data query efficiency. Understanding the involved tables and indexes helps with subsequent optimization and management of the execution plan, and also provides a basis for database performance tuning.

[0078] For step S158, flattening the second structure tree into the second string is to facilitate the storage and management of the information of the structure tree. The flattening process is to convert the node information and the relationships between nodes in the tree structure into a linear string according to certain rules. The tree structure can be traversed in a depth-first search or breadth-first search manner, record the names, attributes of the nodes, and the connection relationships between the nodes, and finally combine them into a string.

[0079] For step S159, storing the flattened second string, the execution plan to be executed, and the involved tables and table indexes into a preset execution plan repository is for the purpose of reusing this information during subsequent queries. When a new query statement arrives, the database can first check in the repository to see if there is an execution plan with similar semantics. If there is, it can be directly reused or appropriately adjusted, avoiding repeated generation of execution plans and improving query efficiency.

[0080] As a specific example, assume that the statement to be executed is to query the detailed health records and claim records of customers who have rare diseases and are over 60 years old. The query corresponding to the existing execution plan may be the statistical amount of claims for customers with common diseases and aged between 30 and 40 years old. The semantics of these two queries are significantly different. The dynamic programming algorithm will analyze the statement to be executed, considering the Customers table (storing basic customer information), the HealthRecords table (storing customer health records), and the Claims table (storing claim records). The algorithm will evaluate different join orders (such as joining the Customers table and the HealthRecords table first, and then joining the Claims table; or joining the Customers table and the Claims table first, and then joining the HealthRecords table, etc.) and different filtering orders (filtering by age first and then by disease type; or vice versa), calculate the cost of each combination, and finally determine the optimal execution order and operation steps to generate the plan to be executed. For the plan to be executed for the above query of the detailed health records and claim records of customers who have rare diseases and are over 60 years old, the tables involved are the Customers table, the HealthRecords table, and the Claims table. There may be an index based on the Age field on the Customers table to quickly filter out customers over 60 years old; there may be an index based on the DiseaseType field on the HealthRecords table to facilitate quickly locating customer records with rare diseases; there may be an index based on the CustomerID field on the Claims table to improve efficiency when performing join operations with other tables. For the second structure tree containing the Customers table, the HealthRecords table, and the Claims table, depth-first search is used for flattening. Assume that the root node is the query target "query the detailed health records and claim records of customers who have rare diseases and are over 60 years old". First, visit the Customers table node, recorded as "Customers", then visit its filtering condition (age > 60), recorded as "Age60", separated by a specific symbol (such as " / "), that is, "Customers / Age>60". Then visit the HealthRecords table node, recorded as "HealthRecords", and its filtering condition (disease type = rare disease) is recorded as "DiseaseType = rare disease", connected with the previous information as "Customers / Age>60 / HealthRecords / DiseaseType = rare disease".Re-access the Claims table node and related connection conditions (such as joining by CustomerID), and finally obtain a second string, such as "Customers / Age>60 / HealthRecords / DiseaseType = rare disease / Claims / Join:CustomerID". Store the above-generated second string, the to-be-executed plan for querying the detailed health records and claim records of customers aged 60 and above with rare diseases, and the involved Customers table, HealthRecords table, Claims table, and corresponding index information (such as the Customers.Age index, the HealthRecords.DiseaseType index, the Claims.CustomerID index) together in the execution plan repository. Subsequently, for similar queries, such as querying the relevant information of customers aged 70 and above with another rare disease, the database can look up in the repository. If semantic similarities are found, it can refer to the existing execution plan and quickly execute the query after appropriate modification.

[0081] In some embodiments of the present invention, such as Figure 6 shown, after step S150, it further includes:

[0082] Step S160, monitoring the tables and table indexes associated with the to-be-executed plan during the execution process of the to-be-executed plan;

[0083] Step S170, if the tables and / or table indexes associated with the to-be-executed plan change, invalidating the to-be-executed plan stored in the preset execution plan repository.

[0084] Specifically, for step S160, after determining the to-be-executed plan corresponding to the to-be-executed statement, to ensure the effectiveness and accuracy of the plan during the execution process, it is necessary to monitor the tables and table indexes associated with it in real time. Because the data in the database is dynamically changing, the data in the table may be inserted, updated, or deleted, the structure of the table may also change, and the index may need to be rebuilt due to data changes. By monitoring these associated tables and indexes, changes in data and structure can be discovered in a timely manner so that the execution plan can be adjusted when necessary. Specifically, the method of monitoring the tables and table indexes associated with the to-be-executed plan during the execution process of the to-be-executed plan can be any achievable method. For example, an event trigger can be set to monitor the tables and table indexes associated with the to-be-executed plan during the execution process of the to-be-executed plan.

[0085] Regarding step S170, when it is monitored that the table or the index of the table associated with the to-be-executed plan has changed, the original execution plan may no longer be applicable. Because the change of table data may affect the cost estimation of the query, the change of table structure may cause the query statement to fail to execute properly, and the change of index may make the original query path based on this index no longer the optimal one. At this time, it is necessary to mark the to-be-executed plan stored in the preset execution plan repository as invalid. In this way, when the same or similar query is executed again later, the database will no longer use this invalid execution plan, but will regenerate a new execution plan to ensure the accuracy and efficiency of the query.

[0086] As a specific example, assume that the to-be-executed plan is to query the claim situation of customers with a specific chronic disease, which involves the Customers table (storing customer basic information), the HealthRecords table (storing customer health records), and the Claims table (storing claim information). The Customers table may have a primary key index based on CustomerID, the HealthRecords table has an index based on DiseaseType, and the Claims table has an index based on ClaimDate. During the execution of this plan, the monitoring system will continuously monitor the data change situations of these tables. For example, when new customer information is added to the Customers table, or the health record of a certain customer is updated in the HealthRecords table, or new claim records are inserted into the Claims table, the monitoring system can detect it in time. At the same time, it will also monitor the status of the index. For example, when the data distribution of DiseaseType in the HealthRecords table changes greatly, which may lead to a decline in the efficiency of this index, the monitoring system can also detect this change. Continuing with the above example of the to-be-executed plan to query the claim situation of customers with a specific chronic disease. Suppose during the monitoring process, it is found that a large number of data insert operations have been performed on the Claims table, resulting in a significant increase in the amount of data in the table, or the index of DiseaseType in the HealthRecords table needs to be rebuilt due to frequent data updates. In this case, the original execution plan may not accurately reflect the current data distribution and query cost, and continuing to use it may lead to low query efficiency or even incorrect results. At this time, the system will mark the to-be-executed plan in the execution plan repository as invalid. When there is a request to query the claim situation of customers with a specific chronic disease again next time, the database will re-analyze the query statement and the current data status, and use dynamic programming algorithms, etc. to regenerate an execution plan suitable for the current data situation.

[0087] Optionally, relevant data of the execution plan can be stored in a persistent storage medium (such as a disk), which can avoid the problems of memory space and data loss due to power failure caused by caching the execution plan in volatile memory in the prior art.

[0088] It can be understood that when querying data in a traditional database, the query statement (i.e., the statement to be executed) is used as the retrieval key value, but the limitation is very large. As long as there are some minor changes in the execution statement, such as the order of words in the statement changes, the corresponding execution plan cannot be matched. However, in the embodiment of the present invention, when querying data using the statement to be executed, a semantic identifier that can represent the semantics of the semantic tree corresponding to the statement to be executed is generated. Therefore, it can be understood that multiple statements to be executed with the same semantics will finally generate the same semantic identifier, and one semantics can be expressed by multiple statements to be executed, and the same statement may also lead to inconsistent semantics due to dialects, synonyms or context, spaces in the statement, wildcards such as '*' in the statement may lead to unclear semantics, resulting in inaccurate execution plans obtained. Therefore, using the semantic identifier that can reflect the semantics as the retrieval value of the execution plan in the preset execution plan repository, when looking for the execution plan corresponding to the statement to be executed in the preset execution plan repository, as long as the statement to be executed and the execution plan in the execution plan repository express the same meaning, they can all be matched, and the matching is more extensive and accurate, thus solving the problem that it is difficult to determine the execution plan corresponding to the query statement using the plan caching mechanism.

[0089] It should be understood that the magnitudes of the sequence numbers of the steps in the above embodiments do not mean the order of execution. The execution order of each process should be determined according to its function and internal logic, and should not constitute any limitation to the implementation process of the embodiments of the present invention. The non-company software tools or components appearing in the embodiments of the present application are only for example introduction and do not represent actual use.

[0090] In one embodiment, a device for accurately matching the database execution plan is provided. The device for accurately matching the database execution plan corresponds one-to-one with the method for accurately matching the database execution plan in the above embodiment. As Figure 7 shown, the device for accurately matching the database execution plan includes a first acquisition module 710, a first generation module 720, a second generation module 730, a second acquisition module 740, and a determination module 750. The detailed description of each functional module is as follows:

[0091] The first acquisition module 710 is used to acquire the statement to be executed;

[0092] The first generation module 720 is used to generate a semantic tree corresponding to the statement to be executed;

[0093] The second generation module 730 is configured to generate a semantic identifier for characterizing the semantics of the semantic tree according to the semantic tree;

[0094] The second acquisition module 740 is configured to acquire an execution plan corresponding to the semantic identifier in a preset execution plan repository;

[0095] The determination module 750 is configured to determine the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

[0096] In one embodiment, the second acquisition module 750 is specifically configured to:

[0097] Determine a plurality of first data tables that are called simultaneously when the execution plan is executed and a first connection relationship of the plurality of first data tables;

[0098] Generate a first structure tree including the first data tables and the first connection relationship;

[0099] Determine a plurality of second data tables that are called simultaneously when the to-be-executed statement is executed and a second connection relationship of the plurality of second data tables;

[0100] Generate a second structure tree including the second data tables and the second connection relationship;

[0101] When the semantics corresponding to the first structure tree are the same as the semantics corresponding to the second structure tree, determine the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

[0102] In one embodiment, the second acquisition module 750 is further configured to:

[0103] When the nodes in the first structure tree are the same as the nodes in the second structure tree, determine the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

[0104] In one embodiment, the second generation module 730 is specifically configured to:

[0105] Flatten and convert the query tree into a first string;

[0106] Perform a hash transformation on the first string, and generate a hash value corresponding to the first string as the semantic identifier corresponding to the semantic tree.

[0107] In one embodiment, the second generation module 730 is further configured to:

[0108] When the semantics corresponding to the first structure tree are different from the semantics corresponding to the second structure tree, generate a to-be-executed plan corresponding to the to-be-executed statement based on a preset dynamic programming algorithm;

[0109] Obtain the tables and the indexes of the tables involved in the process of executing the to-be-executed plan of the to-be-executed statement;

[0110] Flatten the second structure tree into a second string;

[0111] Store the second string, the to-be-executed plan of the to-be-executed statement, the tables and the indexes of the tables into the preset execution plan repository.

[0112] In one embodiment, the determining module 750 is further configured to:

[0113] Monitor the tables and the indexes of the tables associated with the to-be-executed plan during the execution process of the to-be-executed plan;

[0114] If the tables and / or the indexes of the tables associated with the to-be-executed plan change, invalidate the to-be-executed plan stored in the preset execution plan repository.

[0115] The present invention provides a device for accurately matching a database execution plan. When querying data in a traditional database, the query statement (i.e., the to-be-executed statement) is used as the retrieval key value, but the limitation is very large. As long as there are some minor changes in the execution statement, such as the change in the order of words in the statement, the corresponding execution plan cannot be matched. In the embodiment of the present invention, when querying data using the to-be-executed statement, a semantic identifier that can represent the semantics of the semantic tree corresponding to the to-be-executed statement is generated. Therefore, it can be understood that multiple to-be-executed statements with the same semantics will finally generate the same semantic identifier, and one semantics can be expressed by multiple to-be-executed statements. And the same statement may also lead to inconsistent semantics due to dialects, synonyms or contexts, spaces in the statement, wildcards such as '*' in the statement, which may lead to unclear semantics and inaccurate obtained execution plans. Therefore, the semantic identifier that can reflect the semantics is used as the retrieval value of the execution plan in the preset execution plan repository, so that when looking for the execution plan corresponding to the to-be-executed statement in the preset execution plan repository, as long as the to-be-executed statement and the execution plan in the execution plan repository express the same meaning, they can all be matched, and the matching is more extensive and accurate, thus solving the problem that it is difficult to determine the execution plan corresponding to the query statement using the plan cache mechanism.

[0116] For the specific limitations of the device for audio-driven digital humans, reference can be made to the limitations of the method for audio-driven digital humans in the above text, which will not be elaborated here. Each module in the above device for audio-driven digital humans can be implemented in whole or in part by software, hardware and their combination. The above-mentioned modules can be embedded in the processor in the computer device in the form of hardware or independent of it, or stored in the memory in the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0117] Based on the above method for driving a digital human by audio, as Figure 8 shown, an embodiment of the present invention further provides a schematic structural diagram of a device for driving a digital human by audio. The device includes a processor 81 and a memory 82 coupled to the processor 81. The memory 82 stores a computer program. When the computer program is executed by the processor 81, the processor 81 is caused to execute the steps of the method for driving a digital human by audio in the above embodiment.

[0118] Regarding other details of the implementation of the above technical solution by the processor 81 in the device for driving a digital human by audio, reference can be made to the description in the method for driving a digital human by audio provided in the above embodiment of the invention, which will not be elaborated here.

[0119] Among them, the processor 81 can also be referred to as a CPU (Central Processing Unit), and the processor 81 may be an integrated circuit chip with signal processing capabilities; the processor 81 can also be a general-purpose processor, a DSP (Digital Signal Process), an ASIC (Application Specific Integrated Circuit), an FPGA (Field Programmable Gate Array), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components. Among them, the general-purpose processor can be a microprocessor, or the processor 81 can also be any conventional processor, etc.

[0120] As Figure 9 shown, an embodiment of the present invention further provides a schematic structural diagram of a computer-readable storage medium. A readable computer program 91 is stored on the storage medium. Among them, the computer program 91 can be stored in the above storage medium in the form of a software product, including several instructions to cause a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to execute all or part of the steps of the methods described in various embodiments of the present invention. The aforementioned storage medium includes: various media that can store program codes such as USB flash drives, mobile hard disks, magnetic disks, or optical discs, ROM (Read-Only Memory), RAM (Random Access Memory), etc., or terminal devices such as computers, servers, mobile phones, and tablets.

[0121] In several embodiments provided by the present invention, it should be understood that the disclosed systems, devices, and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of the modules is only a logical function division. In actual implementation, there may be other division methods. For example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the displayed or discussed coupling or direct coupling or communication connection to each other can be through some interfaces, and the indirect coupling or communication connection of the device or module can be in electrical, mechanical, or other forms.

[0122] The modules described as separate components may or may not be physically separated. The components displayed as modules may or may not be physical modules, that is, they can be located in one place or distributed to multiple network modules. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0123] In addition, in each embodiment of the present invention, the functional modules can be integrated in a processing module, or each module can exist physically alone, or two or more modules can be integrated in one module. The above integrated modules can be implemented in the form of hardware or in the form of software functional modules. If the integrated modules are implemented in the form of software functional modules and sold or used as independent products, they can be stored in a computer-readable storage medium.

[0124] In the above embodiments, they can be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented using software, they can be implemented in whole or in part in the form of a computer program product.

[0125] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, they generate, wholly or partly, the processes or functions described in the embodiments of the present invention. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions may be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions may be transmitted from a website, a computer, a server, or a data center to another website, computer, server, or data center in a wired manner (such as coaxial cable, optical fiber, digital subscriber line) or a wireless manner (such as infrared, wireless, microwave, etc.). The computer-readable storage medium may be any available medium that can be stored by a computer or a data storage device such as a server or a data center integrating one or more available media. The available medium may be a magnetic medium (such as a floppy disk, a hard disk, a magnetic tape), an optical medium, or a semiconductor medium (such as an SSD (solid state disk)).

[0126] The above has introduced in detail the technical solution provided by the present invention. Specific examples are used in the present invention to elaborate on the principle and implementation manner of the present invention. The description of the above embodiments is only used to help understand the method and its core idea of the present invention; at the same time, for those of ordinary skill in the art, according to the idea of the present invention, there will be changes in the specific implementation manner and application scope. In summary, the content of this specification should not be construed as a limitation to the present invention.

[0127] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system, or a computer program product. Therefore, the present invention can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, optical storage, etc.) containing computer-usable program code.

[0128] The present invention is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, and the combination of processes and / or blocks in the flowchart and / or block diagram, can be realized by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing devices generate for realizing in the process Figure 1 one process or multiple processes and / or blocksFigure 1 means for the functions specified in one or more boxes.

[0129] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to operate in a particular manner, such that the instructions stored in the computer-readable memory produce a manufacture including an instruction means that implements the functions specified in one Figure 1 or more processes and / or boxes Figure 1 means for the functions specified in one or more boxes.

[0130] These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer-implemented process, so that the instructions executed on the computer or other programmable apparatus provide steps for implementing the functions specified in one Figure 1 or more processes and / or boxes Figure 1 means for the functions specified in one or more boxes.

[0131] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalent technologies, the present invention is also intended to include these changes and modifications. The non-company software tools or components appearing in the embodiments of the present invention are only introduced by way of example and do not represent actual use.

Claims

1. A method for accurately matching a database execution plan, characterized in that including: Obtain the statement to be executed; Generate a semantic tree corresponding to the statement to be executed; Generate a semantic identifier for characterizing the semantics of the semantic tree according to the semantic tree; Obtain the execution plan corresponding to the semantic identifier in the preset execution plan repository; Determine the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed.

2. The method for precisely matching a database execution plan according to claim 1, wherein The step of determining the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed further includes: Determine a plurality of first data tables called simultaneously when the execution plan is executed and a first connection relationship of the plurality of first data tables; Generate a first structure tree including the first data tables and the first connection relationship; Determine a plurality of second data tables called simultaneously when the statement to be executed is executed and a second connection relationship of the plurality of second data tables; Generate a second structure tree including the second data tables and the second connection relationship; When the semantics corresponding to the first structure tree are the same as the semantics corresponding to the second structure tree, determine the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed.

3. The method for accurately matching a database execution plan according to claim 2, wherein The step of, when the semantics corresponding to the first structure tree are the same as the semantics corresponding to the second structure tree, determining the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed specifically is: When the nodes in the first structure tree are the same as the nodes in the second structure tree, determine the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed.

4. The method for accurately matching a database execution plan according to claim 1, characterized in that The semantic tree corresponding to the statement to be executed is the query tree corresponding to the statement to be executed.

5. The method for precisely matching a database execution plan according to claim 4, characterized in that, The step of generating a semantic identifier for characterizing the semantics of the semantic tree according to the semantic tree includes: Flatten and convert the query tree into a first string; Perform a hash transformation on the first string, and generate the hash value corresponding to the first string as the semantic identifier corresponding to the semantic tree.

6. The method for precisely matching a database execution plan according to claim 2, characterized in that, After the step of, when the semantics corresponding to the first structure tree are the same as the semantics corresponding to the second structure tree, determining the execution plan corresponding to the semantic identifier as the execution plan to be executed corresponding to the statement to be executed, further includes: When the semantics corresponding to the first structure tree are different from the semantics corresponding to the second structure tree, generate an execution plan to be executed corresponding to the statement to be executed based on a preset dynamic programming algorithm; Obtain the tables and table indexes involved in the process of executing the execution plan to be executed of the statement to be executed; Flatten the second structure tree into a second string; Store the second string, the execution plan to be executed of the statement to be executed, and the tables and table indexes in the preset execution plan repository.

7. The method for accurately matching a database execution plan according to claim 1, wherein After the step of determining the execution plan as the execution plan to be executed corresponding to the statement to be executed, further includes: Monitor the tables and table indexes associated with the execution plan to be executed during the execution process of the execution plan to be executed; If the tables and / or table indexes associated with the execution plan to be executed change, invalidate the execution plan to be executed stored in the preset execution plan repository.

8. An apparatus for accurately matching a database execution plan, characterized in that including: A first acquisition module for obtaining the statement to be executed; A first generation module for generating a semantic tree corresponding to the to-be-executed statement; A second generation module for generating a semantic identifier for characterizing the semantics of the semantic tree according to the semantic tree; A second acquisition module for acquiring an execution plan corresponding to the semantic identifier in a preset execution plan repository; A determination module for determining the execution plan corresponding to the semantic identifier as the to-be-executed plan corresponding to the to-be-executed statement.

9. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, the steps of the method for accurately matching the database execution plan according to any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by the processor, the steps of the method for accurately matching the database execution plan according to any one of claims 1 to 7 are implemented.

Images