Execution Plan Construction Method and System

Abstract

The embodiments of this specification provide an execution plan construction method and system. The execution plan construction method is applied to a database server and includes: obtaining a pre-compiled statement of the database and determining a parameter distribution histogram corresponding to the database; extracting at least one parameter value from the pre-compiled statement and determining the interval information corresponding to each parameter value in the parameter distribution histogram; determining the execution plan type of each parameter value according to the interval information, constructing a target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value, and caching it to a target linked list; in the case of receiving an access request associated with the pre-compiled statement submitted for the database, executing the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

Description

Technical Field

[0001] The embodiments of this specification relate to the technical field of databases, and particularly to an execution plan construction method and system. Background Art

[0002] With the development of computer and Internet technologies, database technology has been applied in more and more scenarios. Through the unified organization and management of data, a corresponding database and data warehouse can be established according to a specified structure; and using a database management system and a data mining system, a data management and data mining application system that can realize various functions such as adding, modifying, deleting, processing, analyzing, understanding, reporting, and printing data in the database can be designed, so as to realize the processing, analysis, and understanding of data by using the application management system ultimately. In the prior art, a database usually caches only one execution plan for each SQL. If the result set differences generated by different bind variables are too skewed, one execution plan corresponding to one SQL may cause problems of good and bad effects during runtime. For this problem, if only a general plan is used, it will result in the inclusion of parameter symbols $n, which will limit the number of parameters and cause parameter reference error problems. If a customized plan is adopted, the provided parameter values will be substituted, which will cause the execution result to miss the data operation requirements and at the same time cannot solve the resource consumption problem. Therefore, an effective solution is urgently needed to solve the above problems. Summary of the Invention

[0003] In view of this, the embodiments of this specification provide an execution plan construction method. One or more embodiments of this specification also relate to an execution plan construction device, an execution plan construction system, a computing device, a computer-readable storage medium, and a computer program product to solve the technical defects existing in the prior art.

[0004] According to the first aspect of the embodiments of this specification, an execution plan construction method is provided, which is applied to a database server and includes:

[0005] Obtain a pre-compiled statement of the database, and determine a parameter distribution histogram corresponding to the database;

[0006] Extract at least one parameter value from the pre-compiled statement, and determine interval information corresponding to each parameter value in the parameter distribution histogram;

[0007] Determine the execution plan type of each parameter value according to the interval information, and construct a target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value and cache it in a target linked list;

[0008] In the case of receiving an access request associated with the pre-compiled statement submitted for the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0009] According to the second aspect of the embodiments of the present specification, an execution plan construction device is provided, which is applied to a database server and includes:

[0010] An acquisition module, configured to acquire the pre-compiled statement of the database and determine the parameter distribution histogram corresponding to the database;

[0011] A determination module, configured to extract at least one parameter value from the pre-compiled statement and determine the interval information corresponding to each parameter value in the parameter distribution histogram;

[0012] A construction module, configured to determine the execution plan type of each parameter value according to the interval information, construct the target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list;

[0013] A reading module, configured to, in the case of receiving an access request associated with the pre-compiled statement submitted for the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0014] According to the third aspect of the embodiments of the present specification, an execution plan construction system is provided, including a client and a database server, and includes:

[0015] The client is used to construct a pre-compiled statement for the database in response to a pre-compilation request; extract at least one parameter value from the pre-compiled statement and send each parameter value to the database server;

[0016] The database server is used to determine the parameter distribution histogram corresponding to the database and the interval information corresponding to each parameter value in the parameter distribution histogram; determine the execution plan type of each parameter value according to the interval information, construct the target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list; in the case of receiving an access request associated with the pre-compiled statement submitted for the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0017] According to the fourth aspect of the embodiments of the present specification, a computing device is provided, including:

[0018] A memory and a processor;

[0019] The memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions. When the computer-executable instructions are executed by the processor, the steps of the above-mentioned execution plan construction method are implemented.

[0020] According to the fifth aspect of the embodiments of the present specification, a computer-readable storage medium is provided, which stores computer-executable instructions. When the instructions are executed by a processor, the steps of the above-mentioned execution plan construction method are implemented.

[0021] According to the sixth aspect of the embodiments of the present specification, a computer program product is provided, including a computer program or instructions. When the computer program or instructions are executed by a processor, the steps of the above-mentioned execution plan construction method are implemented.

[0022] For the execution plan construction method applied to a database server provided in this embodiment, in order to be able to construct a matching execution plan for a pre-compiled statement, avoid the execution plan occupying more computing resources, and ensure data processing efficiency at the same time, the pre-compiled statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined; at this time, at least one parameter value can be extracted from the pre-compiled statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined; the interval information can reflect the data occupancy of each parameter value in the database. On this basis, the execution plan type that each parameter value optimally matches can be determined according to the interval information, and then the target execution plan corresponding to the pre-compiled statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list; when constructing the execution plan of the pre-compiled statement, it can be realized by analyzing the occupancy of the parameter value in the database, and then it can be ensured that the target execution plan more meets the execution requirements of the pre-compiled statement, and it can be ensured that the data processing operation corresponding to the statement is completed under the premise of consuming less resources. After that, if an access request for an associated pre-compiled statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then a pre-constructed and most reasonable execution plan can be realized for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources at the same time. Description of the Drawings

[0023] Figure 1 is a schematic diagram of an execution plan construction method provided by an embodiment of the present specification;

[0024] Figure 2 is a flowchart of an execution plan construction method provided by an embodiment of the present specification;

[0025] Figure 3 is a processing process flowchart of an execution plan construction method provided by an embodiment of the present specification;

[0026] Figure 4 It is a schematic structural diagram of an execution plan construction device provided by an embodiment of this specification;

[0027] Figure 5 It is a schematic structural diagram of an execution plan construction system provided by an embodiment of this specification;

[0028] Figure 6 It is a structural block diagram of a computing device provided by an embodiment of this specification. Detailed implementation manners

[0029] Many specific details are set forth in the following description in order to provide a thorough understanding of this specification. However, this specification can be implemented in many other ways different from those described herein, and those skilled in the art can make similar extensions without departing from the connotation of this specification. Therefore, this specification is not limited by the specific implementations disclosed below.

[0030] The terms used in one or more embodiments of this specification are for the purpose of describing specific embodiments only and are not intended to limit one or more embodiments of this specification. The singular forms "a", "the", and "said" used in one or more embodiments of this specification and the appended claims are also intended to include the plural forms unless the context clearly dictates otherwise. It should also be understood that the term "and / or" used in one or more embodiments of this specification refers to and includes any or all possible combinations of one or more of the associated listed items.

[0031] It should be understood that although the terms first, second, etc. may be used in one or more embodiments of this specification to describe various information, such information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, the first can also be referred to as the second, and similarly, the second can also be referred to as the first. Depending on the context, the word "if" as used herein can be interpreted as "when" or "while" or "in response to determining".

[0032] In addition, it should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in one or more embodiments of this specification are all information and data that have been authorized by the user or fully authorized by all parties, and the collection, use, and processing of the relevant data need to comply with the relevant laws, regulations, and standards of the relevant countries and regions, and corresponding operation entrances are provided for the user to choose to authorize or refuse.

[0033] First, the noun terms involved in one or more embodiments of this specification are explained.

[0034] SQL (Structured Query Language), that is, Structured Query Language, is a standard language for managing relational database systems. It allows users to perform various database operations, such as querying, inserting, updating, and deleting data. The SQL language has a concise and clear syntax structure, enabling users to conveniently manage and operate databases by writing SQL statements.

[0035] Prepared Statements refer to compiling SQL statements before execution and generating an executable code or query plan. During execution, only specific parameter values need to be passed in to directly execute this pre-compiled code.

[0036] The SQL optimizer is an intelligent component in the database. Its core function is to parse SQL query statements, evaluate different execution paths, and select the optimal execution plan to execute the query. This optimal plan aims to minimize resource consumption (such as CPU, memory, and I / O), thereby improving query performance.

[0037] In this specification, a method for constructing an execution plan is provided. One or more embodiments of this specification simultaneously relate to an execution plan construction device, an execution plan construction system, a computing device, a computer-readable storage medium, and a computer program product, which will be described in detail one by one in the following embodiments.

[0038] In practical applications, if the SQL optimizer selects a full table scan, in the case of a small amount of conforming data, if the concurrency is too high, it will cause a large number of I / O bottlenecks. If it selects an index scan, in the case of a large amount of conforming data, the performance of nested loop scans will significantly decline. Therefore, having only one execution plan for a SQL statement is not a good solution, which may lead to the problem that the execution plan cannot meet the requirement of having an optimal query plan under various conditions. By generating respective execution plans for different parameter values and caching these execution plans, and selecting a matching execution plan according to the handle and parameter values of the SQL statement during the application stage, different query requirements can be adapted. For this processing, a relatively simple way is to generate a separate execution plan for each parameter value, so as to directly obtain the execution plan reuse set according to the handle and parameter values. However, in actual scenarios, SQL statements usually contain multiple parameter values, and there will be thousands of possible combinations after different parameter values are combined. Under the premise of limited resources, it is impossible to cache all combinations of parameter values. Therefore, an effective solution is urgently needed to solve the above problems.

[0039] See Figure 1As shown in the schematic diagram, the execution plan construction method provided in this embodiment for a database server, in order to be able to construct a matching execution plan for a pre-compiled statement, avoid the execution plan occupying more computing resources, and at the same time ensure data processing efficiency, can first obtain the pre-compiled statement of the database and determine the parameter distribution histogram corresponding to the database. At this time, at least one parameter value can be extracted from the pre-compiled statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined. The interval information can reflect the data occupancy ratio of each parameter value in the database. On this basis, the type of execution plan that best matches each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the pre-compiled statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list. When constructing the execution plan of the pre-compiled statement, it can be realized by analyzing the occupancy ratio of the parameter value in the database, and then it can be ensured that the target execution plan better meets the execution requirements of the pre-compiled statement, and it can be ensured that the data processing operation corresponding to the statement is completed with less resource consumption. After that, if an access request for an associated pre-compiled statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then a pre-constructed and most reasonable execution plan can be used for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0040] See Figure 2 , Figure 2 shows a flowchart of an execution plan construction method according to an embodiment of the present specification, which is applied to a database server and specifically includes the following steps.

[0041] Step S202, obtain the pre-compiled statement of the database and determine the parameter distribution histogram corresponding to the database.

[0042] The execution plan construction method provided in this embodiment is applied to a database management server, and is used to construct a reasonable and less computationally resource-consuming execution plan for a pre-compiled statement, so that the execution plan can be reused in the application stage, thereby improving data processing efficiency while reducing resource consumption. Among them, the database deployed by the database server can be used in any scenario, such as a transaction data management scenario, a user data management scenario, a shopping data management scenario, a securities trading data management scenario, a multimedia data management scenario, etc.; that is to say, the database in this embodiment can be a database for storing associated business service data in any scenario, and the specific data content can be selected according to actual needs, and this embodiment does not make any limitations here.

[0043] Specifically, a prepared statement refers to an executable SQL statement that is compiled before the execution of an SQL statement. During the application stage, only specific parameter values need to be passed in to directly execute the SQL statement to complete the corresponding data processing tasks. Correspondingly, a parameter distribution histogram specifically refers to a histogram determined by statistics on the data corresponding to different parameters in the database. This histogram can reflect the proportion of the data corresponding to each parameter value in the database, and then determine the corresponding execution plan type when creating an execution plan subsequently, so that the finally generated target execution plan better meets the data processing requirements and consumes fewer computing resources.

[0044] During specific implementation, the preparation (Prepare), compilation of SQL, generation of a cursor, and caching of the cursor execution plan template of a prepared statement can be achieved through a database compiler. Specifically, in the preparation stage, an SQL statement with placeholders is submitted to the preprocessor of the database server. The purpose of this stage is to template the SQL statement, that is, use placeholders (such as question marks?) to replace the dynamic parts (such as specific parameter values) in the SQL statement, thereby generating a reusable SQL statement template. When compiling SQL, the prepared SQL statement template can be compiled and optimized, and operations such as lexical analysis, semantic analysis, and optimization are performed on the SQL statement. Subsequently, a cursor can be declared using the first statement (such as DECLARE CURSOR) and associated with a certain query result, and then the cursor can be opened using the second setting statement (OPEN CURSOR), and the data in the result set can be read row by row using the third statement (FETCH). Finally, the compiled prepared statement can be cached for subsequent execution.

[0045] On this basis, in order to be able to construct a matching execution plan for a prepared statement, avoid the execution plan occupying more computing resources, and ensure data processing efficiency, the prepared statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined; at this time, at least one parameter value can be extracted from the prepared statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined; the interval information can reflect the data occupancy of each parameter value in the database. On this basis, the type of execution plan that best matches each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the prepared statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list; it can be realized that when constructing the execution plan of the prepared statement, it can be achieved by analyzing the occupancy of the parameter value in the database, and then it can be ensured that the target execution plan more meets the execution requirements of the prepared statement, and it can be ensured that the data processing operation corresponding to the statement can be completed with less resources. After that, if an access request related to the prepared statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then the most reasonable execution plan can be constructed in advance for the data processing operation, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0046] That is to say, when constructing an execution plan for a prepared statement, the segmentation technique in statistics can be adopted, and the database is used to collect the parameter distribution histogram for each index field (which can be generated by analyze and saved in the built-in system table of the database, such as pg_stats), and then according to the interval information where each parameter value is saved in the parameter distribution histogram, it can be determined which type of execution plan is suitable for a given parameter value, and then the target execution plan can be constructed for the prepared statement according to the execution plan type and cached for reuse in the application stage.

[0047] Furthermore, in order to enable the parameter distribution histogram to reflect the occupancy of each parameter value in the database, after obtaining the histogram corresponding to the database, multiple index fields can be mapped to the histogram, and then the parameter distribution histogram can be obtained. In this embodiment, the specific implementation method is as follows:

[0048] Obtain the histogram corresponding to the database, and determine multiple index fields associated with the database; map the multiple index fields to the histogram according to the distribution information, and generate the parameter distribution histogram corresponding to the database according to the mapping result.

[0049] Specifically, the histogram specifically refers to the histogram that records the data distribution information in the database. Correspondingly, the index field specifically refers to the field that associates different parts of the data in the database. Different index fields can be used to read different parts of the data in the database for operations, and the parameter values carried in the statement are associated with different index fields, which can be used to map the interval information of the parameter values in the database.

[0050] Based on this, in order to quickly complete the construction of the target execution plan for the statement during the pre-compiled statement stage, a parameter distribution histogram of the database can be pre-constructed, so as to quickly determine the type of execution plan corresponding to the parameter values involved in the statement subsequently. Therefore, the histogram corresponding to the database can be obtained first, and multiple index fields associated with the database can be determined; since each index field has an index relationship with the data in the database, multiple index fields can be mapped to the histogram according to the distribution information, so that a parameter distribution histogram corresponding to the database can be generated according to the mapping result, so as to reflect the proportion of each parameter value corresponding to the data in the database through the parameter distribution histogram, which is convenient for subsequent use in constructing the target execution plan.

[0051] In summary, by pre-constructing a parameter distribution histogram for the database, it can reflect the proportion of each parameter value corresponding to the data in the database, and then it can be convenient to combine the proportion information to decide the type of execution plan subsequently, so that the most reasonable target execution plan can be quickly constructed and cached.

[0052] Step S204, extract at least one parameter value from the pre-compiled statement, and determine the interval information corresponding to each parameter value in the parameter distribution histogram.

[0053] Specifically, after obtaining the pre-compiled statement and determining the parameter distribution histogram corresponding to the database, further, in order to ensure that the subsequent constructed execution plan is more reasonable, the parameter values included in the pre-compiled statement can be extracted, and then the interval information corresponding to each parameter value in the parameter distribution histogram can be determined. The interval information can reflect the proportion of each parameter value corresponding to the data in the database. According to this proportion, a more reasonable way to construct the execution plan for it can be decided, so as to facilitate the subsequent combination of the interval information corresponding to multiple parameter values to construct the target execution plan of the pre-compiled statement.

[0054] Among them, the parameter value specifically refers to the parameter value extracted from the pre-compiled statement that associates different data tables in the database. Correspondingly, the interval information specifically refers to the histogram range information corresponding to each parameter value in the parameter distribution histogram, which is used to reflect the proportion of the data table corresponding to each parameter value in the database, and then decide the type of execution plan corresponding to it for use.

[0055] Further, when determining the interval information corresponding to each parameter value in the pre-compiled statement, it can be achieved through a statement optimizer, thereby ensuring the construction efficiency of the execution plan. In this embodiment, the specific implementation method is as follows:

[0056] Parse the pre-compiled statement to obtain at least one parameter value, and input each parameter value into the statement optimizer of the database server; calculate the interval information corresponding to each parameter value in the parameter distribution histogram through the statement optimizer.

[0057] Specifically, the statement optimizer specifically refers to an intelligent component in the database, whose core function is to parse SQL query statements, evaluate different execution paths, and calculate the interval information corresponding to the parameter values in the parameter distribution histogram for subsequent execution plan construction.

[0058] Based on this, after obtaining the pre-compiled statement and the corresponding parameter distribution histogram of the database, the pre-compiled statement can be parsed to obtain at least one parameter value, and then each parameter value can be input into the statement optimizer of the database server; to calculate the interval information corresponding to each parameter value in the parameter distribution histogram through the statement optimizer, so as to construct the subsequent execution plan in combination with this interval information.

[0059] For example, in the securities trading scenario, in order to improve the query of users' securities trading data in the database, the SQL can be pre-compiled. After obtaining the pre-compiled SQL submitted by the client, the cursor handle of the pre-compiled SQL can be submitted to the SQL optimizer of the database server using parameter 1. At this time, the SQL optimizer can calculate the interval information of each parameter value in the pre-compiled SQL in the corresponding histogram of the database under the given cursor condition. For example, the pre-compiled SQL contains parameter value A, parameter value B, and parameter value C. Among them, parameter value A corresponds to table a in the database that stores data of large investors, parameter value B corresponds to table b in the database that stores data of retail investors, and parameter value C corresponds to table c in the database that stores data of small investors. According to the corresponding histogram of the database, it is determined that the proportion of the data recorded in table a corresponding to parameter value A in the database is 1.5%, the proportion of the data recorded in table b corresponding to parameter value B in the database is 90%, and the proportion of the data recorded in table c corresponding to parameter value C in the database is 5%. Subsequently, the execution plan can be constructed for the pre-compiled SQL according to the proportion information corresponding to each parameter value, thereby improving the efficiency of querying securities trading data in the securities trading scenario.

[0060] In summary, by using the statement optimizer to calculate the interval information corresponding to each parameter value, the statement parsing efficiency can be effectively improved, and further the construction speed of the subsequent target execution plan can be improved.

[0061] Step S206: Determine the execution plan type of each parameter value according to the interval information, and construct the target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list.

[0062] Specifically, after determining the interval information corresponding to each parameter value in the pre-compiled statement, the proportion of the data corresponding to each parameter value in the database can be reflected according to the interval information. Therefore, the execution plan type of each parameter value can be determined according to this proportion. On this basis, in order to construct a reasonable execution plan for the pre-compiled statement and reduce the occupancy of the cache space, the target execution plan corresponding to the pre-compiled statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list, so that the target execution plan meets the data operation requirements of the pre-compiled statement, and at the same time does not consume more computing resources. Storing it in the target linked list can make it more convenient for reuse in the application stage.

[0063] Among them, the execution plan type specifically refers to the type to which the execution plan corresponding to each parameter value belongs. For example, it can be a global execution plan type or an index execution plan type. Correspondingly, the target execution plan specifically refers to the execution plan constructed for the pre-compiled statement by combining the execution plan types corresponding to each parameter value. This execution plan can combine the execution plans corresponding to each parameter value, making the target execution plan more reasonable and reducing resource consumption. Among them, the target execution plan can make the sub-execution plans corresponding to each parameter value hold simultaneously, thus meeting the usage requirements. Correspondingly, the target linked list specifically refers to a linked list that caches different target execution plans, and the target execution plans included in the linked list are not repeated.

[0064] Furthermore, when determining the execution plan type corresponding to each parameter value, considering that the interval information of each parameter value can reflect the proportion of the parameter value hitting data in the database, the execution plan type can be determined by comparing the parameter proportion with a threshold. In this embodiment, the determination of the execution plan type corresponding to any one of at least one parameter value includes:

[0065] Determine the target parameter value and the target interval information corresponding to the target parameter value; calculate the parameter proportion corresponding to the target parameter value according to the target interval information and the parameter distribution histogram; when the parameter proportion is greater than the preset proportion threshold, determine that the execution plan type of the target parameter value is the global execution plan type; when the parameter proportion is less than the proportion threshold, determine that the execution plan type of the target parameter value is the index execution plan type; when the parameter proportion is equal to the proportion threshold, determine that the execution plan type of the target parameter value is the global index execution plan type.

[0066] Specifically, the target parameter value specifically refers to any parameter value in the pre-compiled statement. Correspondingly, the target interval information specifically refers to the interval information corresponding to the target parameter value. Correspondingly, the parameter ratio specifically refers to the ratio of the data corresponding to the target parameter value in the database. Correspondingly, the ratio threshold specifically refers to the threshold for comparing the parameter ratio and determining the execution plan type corresponding to the target parameter value according to the comparison result, which can be set according to actual needs and is not limited in this embodiment. Correspondingly, the global execution plan type specifically means that the target parameter value needs to access data in the database by global scanning, and the index execution plan type specifically means that the target parameter value needs to access data in the database by index scanning. Correspondingly, the global index execution plan type specifically means that the target parameter value can access data in the database by global scanning and can also access data in the database by index scanning.

[0067] Based on this, when determining the execution plan type corresponding to each parameter value, for any parameter value, the target parameter value and the target interval information corresponding to the target parameter value can be determined first. Thereafter, the parameter ratio corresponding to the target parameter value can be calculated according to the target interval information and the parameter distribution histogram.

[0068] In the case where the parameter ratio is greater than the preset ratio threshold, it indicates that the data corresponding to the target parameter value has a relatively high proportion in the database. If index scanning is used for access, more time will be consumed. Therefore, the database can be accessed by global scanning, and then the execution plan type of the target parameter value is determined to be the global execution plan type.

[0069] In the case where the parameter ratio is less than the ratio threshold, it indicates that the data corresponding to the target parameter value has a relatively low proportion in the database. If global scanning is used, more resources and time will be consumed. Therefore, the database can be accessed by index scanning, and then the execution plan type of the target parameter value is determined to be the index execution plan type.

[0070] In the case where the parameter ratio is equal to the ratio threshold, it indicates that the data corresponding to the target parameter value is in the middle state in the database. The computing resources and time occupied by accessing the database by global scanning or index scanning are basically the same. Therefore, either global scanning or index scanning can be selected, and then the execution plan type of the target parameter value can be determined to be the global index execution plan type.

[0071] And so on. After determining the execution plan type corresponding to each parameter value, the subsequent combination of the execution plan types corresponding to each parameter value can be used to combine the target execution plan corresponding to the pre-compiled statement, so as to achieve access to the database when the execution plans corresponding to each parameter value are all established, and at the same time reduce the consumption of computing resources.

[0072] Further, when constructing the target execution plan that matches the pre-compiled statement according to the execution plan type corresponding to the parameter value, considering that the execution plan types corresponding to each parameter value may not be the same, in order to make the finally obtained target execution plan more reasonable and occupy less computing resources, it can be achieved by calculating the combined value. In this embodiment, the specific implementation method is as follows:

[0073] Calculate the plan combined value corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value; construct the target execution plan corresponding to the pre-compiled statement according to the plan combined value; determine the target linked list according to the cursor execution plan template of the pre-compiled statement, and cache the target execution plan to the target linked list.

[0074] Specifically, the plan combined value specifically refers to a string obtained by combining the execution plan types corresponding to each parameter value, which can represent the target execution plan corresponding to the pre-compiled statement when the execution plans corresponding to each parameter value are all established. Correspondingly, the cursor execution plan template specifically refers to a template pointing to the linked list storing the execution plan.

[0075] Based on this, in order to ensure that the target execution plan is more reasonable and saves more computing resources, the plan combined value corresponding to the pre-compiled statement can be calculated according to the execution plan type corresponding to each parameter value; the execution plans corresponding to each parameter value can be represented by the plan combined value, and then the target execution plan corresponding to the pre-compiled statement can be constructed according to the plan combined value; then, the target linked list can be determined according to the cursor execution plan template of the pre-compiled statement, and the target execution plan can be cached to the target linked list.

[0076] In practical applications, considering that each parameter value may correspond to a full table scan and an index scan, if the pre-compiled statement contains two or more parameter values, it may lead to multiple possibilities for the target execution plan corresponding to the pre-compiled statement. For example, the parameter values a and b in the pre-compiled SQL may both be indexes or full table scans. At this time, there may be 4 types of execution plans constructed, which does not meet the actual requirements. Therefore, by calculating the combined value, it can be ensured that the optimal execution plan corresponding to each parameter value is established, and thus it is ensured that there is only one most reasonable target execution plan for the pre-compiled statement. For example, if the parameter value a in the pre-compiled SQL is determined to be an index scan after comparing the proportion threshold, and the parameter value b is determined to be a full table scan after comparing the proportion threshold, then the constructed execution plan is established with the parameter value a only through the index and the parameter value b only through the full table scan, thereby making the target execution plan more reasonable and saving more computing resources.

[0077] In summary, by calculating the planned combination value to determine the string for which the execution plan corresponding to each parameter value holds, and constructing the target execution plan based on this, the target execution plan can be made more reasonable and will not occupy more computing resources and cache resources, so as to be directly used in the reuse phase.

[0078] On this basis, considering that the construction of the execution plan for the pre-compiled statement occurs in the pre-compilation phase, in order to avoid occupying more cache by repeatedly constructing the execution plan, after obtaining the target execution plan, the target linked list can be traversed, thereby ensuring that the target execution plan in the target linked list is unique. In this embodiment, the specific implementation method is as follows:

[0079] Traverse the target linked list, and detect whether the target execution plan exists in the target linked list according to the traversal result; if so, obtain the newly added pre-compiled statement, use the newly added pre-compiled statement as the pre-compiled statement, and execute the step of extracting at least one parameter value from the pre-compiled statement; if not, use the planned combination value as the key and the target execution plan as the value, and cache the value into the target linked list according to the key.

[0080] Specifically, the newly added pre-compiled statement specifically refers to the pre-compiled statement newly submitted by the client in the current scenario.

[0081] Based on this, after obtaining the target execution plan corresponding to the pre-compiled statement, in order to avoid repeatedly caching the execution plans with the same logic, the target linked list can be traversed, and it can be detected whether the currently obtained target execution plan exists in the target linked list according to the traversal result; if so, the target execution plan can not be cached, but continue to process the subsequent pre-compiled statements, so the newly added pre-compiled statement can be obtained, use the newly added pre-compiled statement as the pre-compiled statement, and execute the step of extracting at least one parameter value from the pre-compiled statement; if not, it means that the target execution plan corresponding to the pre-compiled statement does not exist in the target linked list at this time, so the planned combination value can be used as the key and the target execution plan as the value, and the value can be cached into the target linked list according to the key for subsequent access to the target linked list.

[0082] In practical applications, when constructing the target execution plan corresponding to the pre-compiled statement, the calculated planned combination value can be represented in hexadecimal with 4 bytes. The least significant bit can represent the scanning method corresponding to the first parameter, the second bit represents the scanning method corresponding to the second parameter, and so on; and it should be noted that since the access to a table may use two indexes simultaneously, such as BitAnd and Bit0r, each bit needs to represent an access method rather than the data table corresponding to the parameter value. In addition, for non-index filtering conditions, they can be selected to be ignored. Since the scanning method corresponding to this parameter value cannot correspond to an index, any statement does not need to pay attention to what the condition is.

[0083] In specific implementation, 1 can be used to represent global scanning, and 2 can be used to represent index scanning. For example, 0x00000012 indicates that there are two database access methods involved in the filtering conditions of the SQL statement. The first one needs to perform index scanning, and the second one needs to perform global scanning. After that, the target linked list executed by traversing the cursor plan execution template can be traversed. And when the target in the target linked list does not exist in the currently constructed target execution plan, the plan combination value corresponding to the target execution plan can be used as the key, and the target execution plan can be used as the value, and stored at the end of the target linked list to be passed to the executor for execution in the application stage.

[0084] Furthermore, considering that there may be multiple pre-compiled statements, and each pre-compiled statement involves one or more parameter values, the above processing can be repeated to complete the construction of a new execution plan and store the value in the target linked list until after n times of processing, and all potential execution plans of the cursor can be cached in the target linked list by the statement optimizer. Thus, in the application stage, an execution plan suitable for the current data processing requirements can be directly found and reused in the linked list, so that more time does not need to be spent on constructing the execution plan, thereby improving the data operation efficiency.

[0085] Continuing with the above example, after obtaining that the proportion corresponding to parameter value A is 1.5%, the proportion corresponding to parameter value B is 90%, and the proportion corresponding to parameter value C is 5%, by comparing the proportion information corresponding to each parameter value with the proportion threshold, it is determined that parameter value A is of the index scanning type, parameter value B is of the global scanning type, and parameter value C is of the index scanning type. Therefore, the calculation combination value corresponding to the pre-compiled SQL is calculated according to the scanning types determined by the above proportion information. At this time, the combination value obtained is 0x212. On this basis, the target execution plan corresponding to the pre-compiled SQL can be constructed. The first access in this execution plan is index scanning, the second access is global scanning, and the third access is index scanning. Then, the combination value 0x212 can be used as the key, and it is detected whether there is a value corresponding to this key in the target linked list. If it exists, the subsequent processing can be continued; if it does not exist, the key-value pair can be stored at the end of the target linked list so that the target execution plan stored in the target linked list can be reused in the application stage.

[0086] In summary, by detecting the execution plans stored in the linked list, it can be ensured that the execution plans stored in the linked list are not repeated, thereby saving cache space and also ensuring that the corresponding target execution plan can be quickly found and used in the application stage.

[0087] Step S208, when receiving an access request related to the pre-compiled statement submitted for the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0088] Specifically, after the above process of storing the target execution plan in the target linked list by continuous iteration, if an access request submitted to the database is received and the access request is associated with a precompiled statement of the database, it indicates that the execution plan corresponding to the access request has been cached at this time. Therefore, the target execution plan can be read from the target linked list to execute the data processing task corresponding to the access request through the target execution plan, so as to quickly respond to the access request and give feedback.

[0089] Among them, the access request specifically refers to a request submitted to the database, which carries parameter values for inserting a precompiled statement. By parsing the access request to obtain the parameter values, and directly inserting the parameter values into the precompiled statement corresponding to the request, and reading the cached target execution plan in the linked list according to the precompiled statement to execute the data processing task, the data processing efficiency can be effectively improved. Correspondingly, the data processing task specifically refers to a task of determining target data in the database in response to an access request and performing operations such as addition, deletion, modification, and query. It can be set according to actual needs, and this embodiment does not make any limitation here.

[0090] Continuing with the above example, in the case of receiving an access request associated with data tables a, b, and c, the parameter values carried in the request can be added to the precompiled SQL at this time, and then the execution plan associated with this precompiled SQL can be read from the target linked list. For example, if the obtained execution plan corresponds to the combined value 0x212, then through this execution plan, data table a can be scanned according to the index information, data table b can be scanned globally, and data table c can be scanned according to the index information. After determining the target data based on the scan results, the target data can be processed for addition, deletion, modification, and query according to the access request. For example, in a securities trading scenario, the target data corresponds to multiple users with operation violations, and the current access request is to read the data for risk detection.

[0091] Furthermore, in order to quickly abolish the execution plan in the target linked list in the case of an execution plan failure event occurring in the database and avoid affecting subsequent services, event detection can be performed. In this embodiment, the specific implementation method is as follows:

[0092] Perform event detection on the database; in the case of detecting an execution plan failure event of the database, update the cursor execution plan template to a failure state, so that the execution plan included in the target linked list becomes invalid.

[0093] Specifically, the execution plan failure event refers to an event that causes the execution plan in the target linked list to become invalid, such as the occurrence of DDL or statistics information collection, etc. At this time, the cached execution plan will become invalid. In order to avoid affecting data processing by using the invalid execution plan subsequently, it can be achieved by updating the template to a failure state.

[0094] Based on this, in order to avoid the execution plan from becoming invalid and affecting data processing, event detection can be performed on the database; in the case where an execution plan invalidation event of the database is detected, it indicates that the event occurring at this time will affect the use of the execution plan. Therefore, the cursor execution plan template can be updated to an invalid state, rendering the execution plans included in the target linked list invalid.

[0095] That is to say, after caching the target execution plan through the target linked list, the operation here is merely to change from a single element to a linked list storage. If DDL or statistics information occurs, etc., it will render the cached execution plan invalid. Therefore, according to the original logic, the cursor execution plan template can be updated to an invalid state, thereby ensuring that without the need for separate cleaning of the execution plan, the execution plan will not be reused again.

[0096] In summary, by detecting invalidation events to update the execution plan in a timely manner, it is possible to effectively avoid subsequent data processing operations from being affected.

[0097] In addition, in order to save cache, the execution times of the execution plans in the target linked list can be detected regularly to delete the execution plans with fewer execution times from the target linked list, leaving more cache space for those with more execution times. In this embodiment, the specific implementation method is as follows:

[0098] Statistically count the execution times of the execution plans included in the target linked list to obtain plan execution information; based on the plan execution information, screen the execution plans to be deleted in the target linked list, and delete the execution plans to be deleted in the target linked list at a preset time interval.

[0099] Specifically, the plan execution information specifically refers to the execution times statistical information corresponding to each execution plan included in the target linked list. Correspondingly, the execution plans to be deleted specifically refer to the execution plans that need to be deleted and processed in the target linked list. Correspondingly, the time interval is the time for performing the deletion operation, which can be set according to actual requirements, and this embodiment does not make any limitation here.

[0100] Based on this, considering that there are a large number of execution plans included in the target linked list, although the above processing can reduce the number of cached execution plans and make the execution plans more reasonable, in actual application scenarios, there will still be cases where the number of times a precompiled statement is reused is small, and at this time, the corresponding execution plan will also be used less. Therefore, in order to avoid occupying more cache space, the execution plan cleaning operation can be performed regularly.

[0101] That is to say, the execution times of the execution plans included in the target linked list can be counted to obtain plan execution information; based on the execution information, the reuse rate of each execution plan can be determined, and then the to-be-deleted execution plans with relatively low reuse rates can be screened out from the target linked list according to the execution information. For example, they can be sorted in ascending order according to the reuse rate, and a set number of execution plans sorted earlier can be selected as the to-be-deleted execution plans, and the to-be-deleted execution plans can be marked. Thus, the to-be-deleted execution plans in the target linked list can be deleted at preset time intervals, thereby saving more cache space.

[0102] In summary, in order to be able to construct a matching execution plan for a precompiled statement, avoid the execution plan occupying more computing resources, and ensure data processing efficiency at the same time, the precompiled statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined; at this time, at least one parameter value can be extracted from the precompiled statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined; the interval information can reflect the data occupancy of each parameter value in the database. On this basis, the type of the execution plan that best matches each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the precompiled statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list; it can be realized that when constructing the execution plan of the precompiled statement, it can be achieved by analyzing the occupancy of the parameter value in the database, and then it can be ensured that the target execution plan better meets the execution requirements of the precompiled statement, and it can be ensured that the data processing operation corresponding to the statement can be completed on the premise of consuming less resources. After that, if an access request for an associated precompiled statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then a most reasonable execution plan can be pre-constructed for the data processing operation, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0103] The following combines the attached Figure 3 , taking the application of the execution plan construction method provided in this specification in the scenario of inserting data into the database as an example, to further illustrate the execution plan construction method. Among them, Figure 3 FIG. shows the processing procedure flowchart of an execution plan construction method provided by an embodiment of this specification, which specifically includes the following steps.

[0104] Step S302, obtain the precompiled statement of the database, and determine the parameter distribution histogram corresponding to the database.

[0105] Step S304, parse the precompiled statement to obtain at least one parameter value, and input each parameter value into the statement optimizer of the database server.

[0106] Step S306: Calculate the interval information corresponding to each parameter value in the parameter distribution histogram through a statement optimizer, and determine the execution plan type of each parameter value according to the interval information.

[0107] Specifically, the target parameter value and the target interval information corresponding to the target parameter value can be determined; according to the target interval information and the parameter distribution histogram, calculate the parameter proportion corresponding to the target parameter value; when the parameter proportion is greater than the preset proportion threshold, determine that the execution plan type of the target parameter value is the global execution plan type; when the parameter proportion is less than the proportion threshold, determine that the execution plan type of the target parameter value is the index execution plan type; when the parameter proportion is equal to the proportion threshold, determine that the execution plan type of the target parameter value is the global index execution plan type.

[0108] Step S308: Calculate the plan combination value corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value.

[0109] Step S310: Construct the target execution plan corresponding to the pre-compiled statement according to the plan combination value.

[0110] Step S312: Determine the target linked list according to the cursor execution plan template of the pre-compiled statement, and cache the target execution plan to the target linked list.

[0111] Step S314: Receive an access request for an associated pre-compiled statement submitted to the database.

[0112] Step S316: Execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0113] In summary, in order to construct a matching execution plan for a prepared statement, avoid the execution plan occupying more computing resources, and ensure data processing efficiency, the prepared statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined. At this time, at least one parameter value can be extracted from the prepared statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined. The interval information can reflect the data occupancy ratio of each parameter value in the database. On this basis, the execution plan type optimally matching each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the prepared statement can be constructed according to the execution plan type corresponding to each parameter value and cached in the target linked list. When constructing the execution plan of the prepared statement, it can be realized by analyzing the occupancy ratio of the parameter value in the database, and then it can be ensured that the target execution plan better meets the execution requirements of the prepared statement, and it can be ensured that the data processing operation corresponding to the statement can be completed with less resource consumption. After that, if an access request related to the prepared statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, so as to realize the pre-construction of the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0114] Corresponding to the above method embodiments, this specification also provides embodiments of an execution plan construction device. Figure 4 The structural schematic diagram of an execution plan construction device provided by an embodiment of this specification is shown. As Figure 4 shown, this device is applied to a database server and includes:

[0115] An obtaining module 402, configured to obtain a prepared statement of a database and determine the parameter distribution histogram corresponding to the database;

[0116] A determining module 404, configured to extract at least one parameter value from the prepared statement and determine the interval information corresponding to each parameter value in the parameter distribution histogram;

[0117] A constructing module 406, configured to determine the execution plan type of each parameter value according to the interval information, construct the target execution plan corresponding to the prepared statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list;

[0118] A reading module 408, configured to, when receiving an access request related to the prepared statement submitted to the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0119] In an alternative embodiment, the apparatus further comprises:

[0120] A generation module, configured to obtain a histogram corresponding to the database, and determine a plurality of index fields associated with the database; map the plurality of index fields to the histogram according to distribution information, and generate a parameter distribution histogram corresponding to the database according to the mapping result.

[0121] In an alternative embodiment, the determination module 404 is further configured to:

[0122] Parse the pre-compiled statement to obtain at least one parameter value, and input each parameter value into a statement optimizer of the database server; calculate interval information corresponding to each parameter value in the parameter distribution histogram through the statement optimizer.

[0123] In an alternative embodiment, the determination of the execution plan type corresponding to any one of the at least one parameter values includes:

[0124] Determine a target parameter value and target interval information corresponding to the target parameter value; calculate a parameter proportion corresponding to the target parameter value according to the target interval information and the parameter distribution histogram; when the parameter proportion is greater than a preset proportion threshold, determine that the execution plan type of the target parameter value is a global execution plan type; when the parameter proportion is less than the proportion threshold, determine that the execution plan type of the target parameter value is an index execution plan type; when the parameter proportion is equal to the proportion threshold, determine that the execution plan type of the target parameter value is a global index execution plan type.

[0125] In an alternative embodiment, the construction module 406 is further configured to:

[0126] Calculate a plan combination value corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value; construct a target execution plan corresponding to the pre-compiled statement according to the plan combination value; determine a target linked list according to a cursor execution plan template of the pre-compiled statement, and cache the target execution plan to the target linked list.

[0127] In an alternative embodiment, the construction module 406 is further configured to:

[0128] Traverse the target linked list, and detect whether the target execution plan exists in the target linked list according to the traversal result; if so, obtain a new precompiled statement, use the new precompiled statement as the precompiled statement, and perform the step of extracting at least one parameter value from the precompiled statement; if not, use the plan combination value as the key and the target execution plan as the value, and cache the value into the target linked list according to the key.

[0129] In an optional embodiment, the device further includes:

[0130] A detection module, configured to perform event detection on the database; in the case of detecting an execution plan failure event of the database, update the cursor execution plan template to a failure state, so that the execution plan included in the target linked list fails.

[0131] In an optional embodiment, the device further includes:

[0132] A deletion module, configured to count the number of executions of the execution plans included in the target linked list to obtain plan execution information; filter the execution plans to be deleted in the target linked list based on the plan execution information, and delete the execution plans to be deleted in the target linked list at a preset time interval.

[0133] In summary, in order to be able to construct a matching execution plan for a precompiled statement, avoid the execution plan occupying more computing resources, and at the same time ensure data processing efficiency, the precompiled statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined; at this time, at least one parameter value can be extracted from the precompiled statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined; the interval information can reflect the data occupancy of each parameter value in the database. On this basis, the type of the execution plan that best matches each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the precompiled statement can be constructed according to the type of the execution plan corresponding to each parameter value and cached into the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the occupancy of the parameter value in the database, and then it can be ensured that the target execution plan better meets the execution requirements of the precompiled statement, and it can be ensured that the data processing operation corresponding to the statement is completed with less resources. After that, if an access request for an associated precompiled statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then a pre-constructed and most reasonable execution plan can be used for the data processing operation, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0134] The above is a schematic solution of an execution plan construction device according to this embodiment. It should be noted that the technical solution of the execution plan construction device belongs to the same concept as the technical solution of the above-mentioned execution plan construction method. For the details not described in the technical solution of the execution plan construction device, reference can be made to the description of the technical solution of the above-mentioned execution plan construction method.

[0135] Corresponding to the above method embodiment, this specification also provides an embodiment of an execution plan construction system. Figure 5 FIG. shows a schematic structural diagram of an execution plan construction system provided by an embodiment of this specification. As Figure 5 shown, the execution plan construction system 500 includes a client 510 and a database server 520, including:

[0136] The client 510 is configured to construct a pre-compiled statement for the database in response to a pre-compilation request; extract at least one parameter value from the pre-compiled statement, and send each parameter value to the database server;

[0137] The database server 520 is configured to determine a parameter distribution histogram corresponding to the database, and interval information corresponding to each parameter value in the parameter distribution histogram; determine an execution plan type for each parameter value according to the interval information, construct a target execution plan corresponding to the pre-compiled statement according to the execution plan type corresponding to each parameter value, and cache it in a target linked list; in the case of receiving an access request associated with the pre-compiled statement submitted to the database, execute the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0138] In summary, in order to construct a matching execution plan for a prepared statement, avoid the execution plan occupying more computing resources, and ensure data processing efficiency, the prepared statement of the database can be obtained first, and the parameter distribution histogram corresponding to the database can be determined. At this time, at least one parameter value can be extracted from the prepared statement, and the interval information corresponding to each parameter value in the parameter distribution histogram can be determined. The interval information can reflect the data occupancy of each parameter value in the database. On this basis, the type of execution plan that best matches each parameter value can be determined according to the interval information, and then the target execution plan corresponding to the prepared statement can be constructed according to the type of execution plan corresponding to each parameter value and cached in the target linked list. When constructing the execution plan of the prepared statement, it can be realized by analyzing the occupancy of the parameter value in the database, and then it can be ensured that the target execution plan better meets the execution requirements of the prepared statement, and it can be ensured that the data processing operation corresponding to the statement is completed with less resources consumed. After that, if an access request for an associated prepared statement submitted to the database is received, the data processing task corresponding to the access request can be executed by reading the target execution plan in the target linked list, and then a pre-constructed most reasonable execution plan can be used for the data processing operation, which can better improve the accuracy of the execution plan and save more computing resources at the same time.

[0139] The above is a schematic solution of an execution plan construction system according to this embodiment. It should be noted that the technical solution of this execution plan construction system and the technical solution of the above execution plan construction method belong to the same concept. For the details not described in the technical solution of the execution plan construction system, reference can be made to the description of the technical solution of the above execution plan construction method.

[0140] Figure 6 FIG. shows a structural block diagram of a computing device 600 according to an embodiment of this specification. The components of the computing device 600 include, but are not limited to, a memory 610 and a processor 620. The processor 620 is connected to the memory 610 through a bus 630, and the database 650 is used to store data.

[0141] The computing device 600 also includes an access device 640 that enables the computing device 600 to communicate via one or more networks 660. Examples of such networks include the Public Switched Telephone Network (PSTN), Local Area Network (LAN), Wide Area Network (WAN), Personal Area Network (PAN), or a combination of communication networks such as the Internet. The access device 640 may include one or more of any type of wired or wireless network interface (e.g., a network interface card (NIC)), such as an IEEE 802.11 Wireless Local Area Network (WLAN) wireless interface, a Worldwide Interoperability for Microwave Access (Wi-MAX) interface, an Ethernet interface, a Universal Serial Bus (USB) interface, a cellular network interface, a Bluetooth interface, or a Near Field Communication (NFC) interface.

[0142] In one embodiment of the present specification, the above components of the computing device 600, as well as Figure 6 other components not shown, may also be connected to each other, for example, via a bus. It should be understood that Figure 6 the block diagram of the computing device shown is for illustrative purposes only and is not a limitation on the scope of the present specification. Those skilled in the art can add or replace other components as needed.

[0143] The computing device 600 can be any type of stationary or mobile computing device, including a mobile computer or mobile computing device (e.g., a tablet computer, a personal digital assistant, a laptop computer, a notebook computer, a netbook, etc.), a mobile phone (e.g., a smartphone), a wearable computing device (e.g., a smartwatch, smart glasses, etc.), or other types of mobile devices, or a stationary computing device such as a desktop computer or a personal computer (PC). The computing device 600 can also be a mobile or stationary server.

[0144] Among them, the processor 620 is used to execute the following computer-executable instructions, which, when executed by the processor, implement the steps of the above execution plan construction method.

[0145] The above is a schematic solution of a computing device according to this embodiment. It should be noted that the technical solution of this computing device and the technical solution of the above execution plan construction method belong to the same concept. For the details not described in detail in the technical solution of the computing device, reference can be made to the description of the technical solution of the above execution plan construction method.

[0146] An embodiment of this specification also provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed by a processor, the steps of the above execution plan construction method are implemented.

[0147] The above is a schematic solution of a computer-readable storage medium according to this embodiment. It should be noted that the technical solution of this storage medium and the technical solution of the above execution plan construction method belong to the same concept. For the details not described in detail in the technical solution of the storage medium, reference can be made to the description of the technical solution of the above execution plan construction method.

[0148] An embodiment of this specification also provides a computer program product, including a computer program or instructions. When the computer program or instructions are executed by a processor, the steps of the above execution plan construction method are implemented.

[0149] The above is a schematic solution of a computer program product according to this embodiment. It should be noted that the technical solution of this computer program product and the technical solution of the above execution plan construction method belong to the same concept. For the details not described in detail in the technical solution of the computer program product, reference can be made to the description of the technical solution of the above execution plan construction method.

[0150] The above describes specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recited in the claims may be performed in a different order than in the embodiments and still achieve the desired result. Additionally, the processes depicted in the figures do not necessarily require the particular order or sequential order shown to achieve the desired result. In certain implementations, multitasking and parallel processing are also possible or may be advantageous.

[0151] The computer instructions include computer program code, which may be in the form of source code, object code, executable files or some intermediate forms, etc. The computer-readable medium may include: any entity or device capable of carrying the computer program code, recording medium, USB flash drive, removable hard disk, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately increased or decreased according to the requirements of patent practice. For example, in some regions, according to patent practice, the computer-readable medium does not include electrical carrier signals and telecommunication signals.

[0152] It should be noted that for the foregoing method embodiments, for the sake of simplicity of description, they are all expressed as a series of action combinations. However, those skilled in the art should know that the embodiments of this specification are not limited by the described action sequence, because according to the embodiments of this specification, some steps can be performed in other sequences or simultaneously. Secondly, those skilled in the art should also know that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily essential for the embodiments of this specification.

[0153] In the above embodiments, the descriptions of each embodiment have their own emphases. For the parts not detailed in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0154] The preferred embodiments of this specification disclosed above are only used to help explain this specification. The optional embodiments do not elaborate on all details and do not limit the invention to the specific embodiments described. Obviously, many modifications and changes can be made according to the content of the embodiments of this specification. This specification selects and specifically describes these embodiments to better explain the principles and practical applications of the embodiments of this specification, so that those skilled in the art can well understand and utilize this specification.

Claims

1. A method for constructing an execution plan, characterized in that: Applicable to database servers, including: Obtaining a precompiled statement of a database, and determining a parameter distribution histogram corresponding to the database; Extracting at least one parameter value from the precompiled statement, and determining interval information corresponding to each parameter value in the parameter distribution histogram; Determine the execution plan type of each parameter value according to the interval information, calculate the plan combination value corresponding to the prepared statement according to the execution plan type corresponding to each parameter value; construct a target execution plan corresponding to the prepared statement according to the plan combination value, determine a target linked list according to a cursor execution plan template of the prepared statement, and cache the target execution plan to the target linked list, wherein the plan combination value is used to make the execution plan corresponding to each parameter value valid; When an access request associated with the precompiled statement submitted to the database is received, the data processing task corresponding to the access request is executed by reading the target execution plan in the target linked list.

2. The execution plan construction method according to claim 1, characterized in that: Before the step of obtaining the precompiled statement of the database is executed, the following step is also included: Obtaining a histogram corresponding to the database, and determining a plurality of index fields associated with the database; The multiple index fields are mapped to the histogram according to the distribution information, and a parameter distribution histogram corresponding to the database is generated according to the mapping result.

3. The execution plan construction method according to claim 1, characterized in that: The step of extracting at least one parameter value from the precompiled statement and determining interval information corresponding to each parameter value in the parameter distribution histogram includes: Parsing the precompiled statement to obtain at least one parameter value, and inputting each parameter value into a statement optimizer of the database server; The statement optimizer calculates the interval information corresponding to each parameter value in the parameter distribution histogram.

4. The execution plan construction method according to claim 1, characterized in that: Determining the execution plan type corresponding to any one of the at least one parameter value includes: Determine a target parameter value and target interval information corresponding to the target parameter value; Calculating the parameter proportion corresponding to the target parameter value according to the target interval information and the parameter distribution histogram; When the parameter proportion is greater than a preset proportion threshold, determining that the execution plan type of the target parameter value is a global execution plan type; When the parameter proportion is less than the proportion threshold, determining that the execution plan type of the target parameter value is an index execution plan type; When the parameter proportion is equal to the proportion threshold, it is determined that the execution plan type of the target parameter value is a global index execution plan type.

5. The execution plan construction method according to claim 1, characterized in that: The method further comprises: Traversing the target linked list, and detecting whether the target execution plan exists in the target linked list according to the traversal result; If yes, obtain a newly added precompiled statement, use the newly added precompiled statement as the precompiled statement, and execute the step of extracting at least one parameter value from the precompiled statement; If not, use the plan combination value as the key and the target execution plan as the value, and cache the value into the target linked list according to the key.

6. The execution plan construction method according to claim 1 or 5, characterized in that: After the steps of calculating the plan combination value corresponding to the prepared statement according to the execution plan type corresponding to each parameter value; constructing the target execution plan corresponding to the prepared statement according to the plan combination value, determining the target linked list according to the cursor execution plan template of the prepared statement, and caching the target execution plan to the target linked list are executed, the method further includes: Performing event detection on the database; When an execution plan invalidation event of the database is detected, the cursor execution plan template is updated to an invalid state, so that the execution plan included in the target linked list is invalidated.

7. The execution plan construction method according to any one of claims 1 to 5, characterized in that: After the steps of calculating the plan combination value corresponding to the prepared statement according to the execution plan type corresponding to each parameter value; constructing the target execution plan corresponding to the prepared statement according to the plan combination value, determining the target linked list according to the cursor execution plan template of the prepared statement, and caching the target execution plan to the target linked list are executed, the method further includes: Counting the number of executions of the execution plans contained in the target linked list to obtain plan execution information; The execution plans to be deleted are screened in the target linked list based on the plan execution information, and the execution plans to be deleted in the target linked list are deleted at preset time intervals.

8. An execution plan construction system, characterized in that: Includes client and database server, including: The client is used to construct a precompiled statement for the database in response to the precompiled request; extract at least one parameter value from the precompiled statement, and send each parameter value to the database server; The database server is used to determine a parameter distribution histogram corresponding to the database and interval information corresponding to each parameter value in the parameter distribution histogram; determine an execution plan type for each parameter value according to the interval information, and calculate a plan combination value corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value; construct a target execution plan corresponding to the precompiled statement according to the plan combination value, determine a target linked list according to a cursor execution plan template of the precompiled statement, and cache the target execution plan to the target linked list, wherein the plan combination value is used to make the execution plan corresponding to each parameter value valid; and when an access request associated with the precompiled statement submitted to the database is received, execute a data processing task corresponding to the access request by reading the target execution plan in the target linked list.

9. A computing device, characterized in that include: Memory and processor; The memory is used to store computer-executable instructions, and the processor is used to execute the computer-executable instructions. When the computer-executable instructions are executed by the processor, the steps of the method described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium, characterized in that: It stores computer executable instructions, which, when executed by a processor, implement the steps of the method described in any one of claims 1 to 7.

11. A computer program product, characterized in that The method comprises a computer program or an instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 7.

Images