Execution plan construction method and system

Abstract

The embodiment of the invention provides an execution plan construction method and system.The execution plan construction method is applied to a database server and comprises the steps that pre-compiled statements of a database are obtained, and a parameter distribution histogram corresponding to the database is determined; extracting at least one parameter value from the pre-compiled statement, and determining interval information corresponding to each parameter value in the parameter distribution histogram; determining an 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 the target execution plan to a target linked list; and under the condition that an access request which is submitted for the database and is associated with the pre-compiled statement is received, reading the target execution plan in the target linked list to execute a data processing task corresponding to the access request.

Description

Technical Field

[0001] The embodiments of this specification relate to the field of database technology, and in particular to a method and system for constructing an execution plan. Background Art

[0002] With the development of computer and Internet technology, database technology is applied in more and more scenarios. Through unified organization and management of data, corresponding databases and data warehouses can be established according to the specified structure; and by using database management system and data mining system, data management and data mining application systems can be designed to realize multiple functions such as adding, modifying, deleting, processing, analyzing, understanding, reporting and printing of data in the database, so that the application management system can be used to finally realize the processing, analysis and understanding of data. In the prior art, the database usually caches only one execution plan for each SQL. If the result set generated by different binding variables is too different, the execution plan corresponding to one SQL may cause some good effects and some bad effects at runtime. To solve this problem, if only a general plan is used, it will cause it to contain the parameter symbol $n, which will limit the number of parameters and cause parameter reference errors. If a customized plan is used, the provided parameter value will be replaced, so that the execution result cannot hit the data operation requirements, and the resource consumption problem will not be solved. Therefore, an effective solution is urgently needed to solve the above problems. Summary of the invention

[0003] In view of this, an embodiment of this specification provides 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 a first aspect of an embodiment of this specification, there is provided an execution plan construction method, which is applied to a database server and includes: 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, build the target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list; 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.

[0005] According to a second aspect of an embodiment of this specification, there is provided an execution plan construction device, which is applied to a database server and includes: An acquisition module is configured to acquire a precompiled statement of a database and determine a parameter distribution histogram corresponding to the database; a determination module configured to extract at least one parameter value from the precompiled statement and determine interval information corresponding to each parameter value in the parameter distribution histogram; A construction module is configured to determine the execution plan type of each parameter value according to the interval information, and to construct a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and cache it into a target linked list; The reading module is configured to, upon receiving an access request associated with the precompiled statement submitted to the database, execute the data processing task corresponding to the access request by reading the target execution plan from the target linked list.

[0006] According to a third aspect of an embodiment of this specification, there is provided an execution plan construction system, including a client and a 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, construct a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and cache it in a target linked list; and upon receiving an access request associated with the precompiled statement submitted to the database, execute a data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0007] According to a fourth aspect of an embodiment of this specification, a computing device is provided, including: 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 above-mentioned execution plan construction method are implemented.

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

[0009] According to the sixth aspect of the embodiments of this specification, a computer program product is provided, including a computer program or instructions, which implement the steps of the above-mentioned execution plan construction method when executed by a processor.

[0010] The execution plan construction method applied to the database server provided in this embodiment can first obtain the precompiled statement of the database and determine the parameter distribution histogram corresponding to the database in order to build a matching execution plan for the precompiled statement and avoid the execution plan from occupying more computing resources while ensuring data processing efficiency; 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 data proportion of each parameter value in the database can be reflected through the interval information. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, and then the target execution plan can be guaranteed to be more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 It is a schematic diagram of a method for constructing an execution plan provided by an embodiment of this specification; Figure 2 is a flowchart of a method for constructing an execution plan provided by an embodiment of this specification; Figure 3 It is a process flow chart of a method for constructing an execution plan provided by an embodiment of this specification; Figure 4 It is a structural diagram of an execution plan construction device provided by an embodiment of this specification; Figure 5 It is a structural diagram of an execution plan construction system provided by an embodiment of this specification; Figure 6 It is a structural block diagram of a computing device provided by an embodiment of this specification. DETAILED DESCRIPTION

[0012] Many specific details are described in the following description to facilitate a full understanding of this specification. However, this specification can be implemented in many other ways than those described herein, and those skilled in the art can make similar generalizations without violating the connotation of this specification, so this specification is not limited to the specific implementation disclosed below.

[0013] The terms used in one or more embodiments of this specification are only for the purpose of describing specific embodiments, and are not intended to limit one or more embodiments of this specification. The singular forms of "a", "said" and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. 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 associated listed items.

[0014] It should be understood that although the terms first, second, etc. may be used to describe various information in one or more embodiments of this specification, this 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 may also be referred to as the second, and similarly, the second may also be referred to as the first. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0015] 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 used for analysis, stored data, displayed data, etc.) involved in one or more embodiments of this specification are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

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

[0017] SQL (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, allowing users to easily manage and operate databases by writing SQL statements.

[0018] Prepared Statements means compiling SQL statements before execution and generating an executable code or query plan. When executing, you only need to pass in specific parameter values ​​to directly execute the precompiled code.

[0019] 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 is designed to minimize resource consumption (such as CPU, memory, and I / O) to improve query performance.

[0020] In this specification, an execution plan construction method is provided. 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, which are described in detail one by one in the following embodiments.

[0021] In actual applications, if the SQL optimizer chooses a full table scan, for the case where the amount of data is small, if the concurrency is too high, it will cause a lot of I / O bottlenecks. If an index scan is selected, the performance of the nested loop scan will be significantly reduced for the case where the amount of data is large. Therefore, having only one execution plan for a SQL statement is not a good solution, which may cause the execution plan to fail to meet the problem of optimal query plans under various conditions. By generating separate execution plans for different parameter values ​​and caching these execution plans, the matching execution plan can be selected according to the handle and parameter value of the SQL statement in the application phase to adapt to different query requirements. For this processing, a relatively simple way is to generate an execution plan for each parameter value separately, so that the execution plan reuse set can be obtained directly according to the handle and parameter value. However, in actual scenarios, SQL statements usually contain multiple parameter values, and there will be thousands of possibilities after different parameter value combinations. Under the premise of limited resources, it is impossible to cache all parameter value combinations. Therefore, an effective solution is urgently needed to solve the above problems.

[0022] See also Figure 1As shown in the schematic diagram, the execution plan construction method applied to the database server provided in this embodiment can first obtain the precompiled statement of the database and determine the parameter distribution histogram corresponding to the database in order to build a matching execution plan for the precompiled statement and avoid the execution plan from occupying more computing resources while ensuring data processing efficiency; 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 data proportion of each parameter value in the database can be reflected through the interval information. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, and then the target execution plan can be guaranteed to be more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0023] See also Figure 2 , Figure 2 A flowchart of a method for constructing an execution plan provided according to an embodiment of the present specification is shown, which is applied to a database server and specifically includes the following steps.

[0024] Step S202, obtaining a precompiled statement of a database, and determining a parameter distribution histogram corresponding to the database.

[0025] The execution plan construction method provided in this embodiment is applied to a database management server, and is used to construct a reasonable execution plan for precompiled statements that consumes less computing resources, so that the execution plan can be used 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 transaction data management scenario, user data management scenario, shopping data management scenario, securities trading data management scenario, multimedia data management scenario, etc.; that is, 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 restrictions here.

[0026] Specifically, a precompiled statement refers to an executable SQL statement that is compiled and generated before the SQL statement is executed, so that in the application stage, only specific parameter values ​​need to be passed in to directly execute the SQL statement to complete the corresponding data processing task. Correspondingly, a parameter distribution histogram refers to a histogram of data corresponding to different parameters in the database that is determined by statistics, and the histogram can reflect the proportion of data corresponding to each parameter value in the database, and then decide on the corresponding execution plan type when creating the execution plan later, so that the final generated target execution plan is more in line with data processing requirements and consumes less computing resources.

[0027] In specific implementation, the preparation of precompiled statements, compilation of SQL, generation of cursors, and caching of cursor execution plan templates can be achieved through the database compiler. Specifically, the preparation stage mainly submits SQL statements with placeholders to the preprocessor of the database server. The purpose of this stage is to template the SQL statement, that is, to use placeholders (such as question marks?) to replace the dynamic part of the SQL statement (such as specific parameter values) to generate a reusable SQL statement template. When compiling SQL, the prepared SQL statement template can be compiled and optimized, and the SQL statement can be lexically analyzed, semantically analyzed, optimized, and other operations. After that, a cursor can be declared using the first statement (such as DECLARE CURSOR) and associated with a 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 line by line using the third statement (FETCH). Finally, the compiled precompiled statement can be cached for subsequent execution.

[0028] On this basis, in order to be able to build a matching execution plan for the precompiled statement and avoid the execution plan from occupying more computing resources while ensuring 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 proportion of each parameter value in the database. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, so as to ensure that the target execution plan is more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0029] That is to say, when building an execution plan for a precompiled statement, you can use the segmentation technology in statistics to use the database to collect parameter distribution histograms for each index field (which can be saved in the database's built-in system tables, such as pg_stats, and generated by analyze). Then, based on the interval information of each parameter value saved in the parameter distribution histogram, you can determine which type of execution plan is suitable for a given parameter value. Then, you can use the execution plan type to build a target execution plan for the precompiled statement and cache it for reuse in the application stage.

[0030] Furthermore, in order to enable the parameter distribution histogram to reflect the proportion of each parameter value in the database, after obtaining the histogram corresponding to the database, multiple index fields can be mapped to the histogram to obtain the parameter distribution histogram. In this embodiment, the specific implementation method is as follows: A histogram corresponding to the database is obtained, and a plurality of index fields associated with the database are determined; the plurality of index fields are mapped to the histogram according to distribution information, and a parameter distribution histogram corresponding to the database is generated according to the mapping result.

[0031] Specifically, the histogram refers to a histogram that records the data distribution information in the database. Correspondingly, the index field refers to a field that associates different parts of the data in the database. Different parts of the data can be read from the database for operation using different index fields, 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 value in the database.

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

[0033] In summary, by constructing a parameter distribution histogram for the database in advance, it can reflect the proportion of each parameter value in the corresponding data in the database, which can facilitate the subsequent decision-making of the execution plan type based on the proportion information, so that the most reasonable target execution plan can be quickly constructed and cached.

[0034] Step S204: extract at least one parameter value from the precompiled statement, and determine interval information corresponding to each parameter value in the parameter distribution histogram.

[0035] Specifically, after obtaining the precompiled statement as described above and determining the parameter distribution histogram corresponding to the database, further, in order to ensure that the execution plan constructed subsequently is more reasonable, the parameter values ​​contained in the precompiled 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 the corresponding data of each parameter value in the database. According to the proportion, a more reasonable way to construct an 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 precompiled statement.

[0036] The parameter value specifically refers to the parameter value of different data tables in the associated database extracted from the precompiled statement. 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 on the corresponding execution plan type to be used.

[0037] Furthermore, when determining the interval information corresponding to each parameter value in the precompiled statement, it can be implemented by a statement optimizer, thereby ensuring the efficiency of building the execution plan. In this embodiment, the specific implementation method is as follows: The precompiled statement is parsed to obtain at least one parameter value, and each parameter value is input into a statement optimizer of the database server; and the interval information corresponding to each parameter value in the parameter distribution histogram is calculated by the statement optimizer.

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

[0039] Based on this, after obtaining the parameter distribution histogram corresponding to the precompiled statement and the database, the precompiled 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; so that the statement optimizer can calculate the interval information corresponding to each parameter value in the parameter distribution histogram, so as to subsequently combine the interval information to construct a subsequent execution plan.

[0040] For example, in a securities trading scenario, in order to improve the query of the user's securities trading data in the database, SQL can be precompiled. After obtaining the precompiled SQL submitted by the client, the cursor handle of the precompiled 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 precompiled SQL in the corresponding histogram of the database under the given cursor condition. For example, the precompiled SQL contains parameter value A, parameter value B and parameter value C, wherein parameter value A corresponds to data table a storing large household data in the database, parameter value B corresponds to data table b storing retail household data in the database, and parameter value C corresponds to data table c storing small household data in the database. According to the histogram corresponding to the database, it is determined that the proportion of data recorded in data table a corresponding to parameter value A in the database is 1.5%, the proportion of data recorded in data table b corresponding to parameter value B in the database is 90%, and the proportion of data recorded in data table c corresponding to parameter value C in the database is 5%. Subsequently, an execution plan can be constructed for the precompiled 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.

[0041] 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, thereby improving the subsequent target execution plan construction speed.

[0042] Step S206, determining the execution plan type of each parameter value according to the interval information, constructing a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value, and caching it in a target linked list.

[0043] Specifically, after determining the interval information corresponding to each parameter value in the precompiled statement, the proportion of the data corresponding to each parameter value in the database can be reflected according to the interval information, so the execution plan type of each parameter value can be determined according to the proportion. On this basis, in order to be able to build a reasonable execution plan for the precompiled statement and reduce the occupancy of cache space, the target execution plan corresponding to the precompiled statement can be built according to the execution plan type corresponding to each parameter value and cached to the target linked list, so that the target execution plan meets the data operation requirements of the precompiled statement, and at the same time does not consume more computing resources, so that it is stored in the target linked list, which can be more convenient for reuse in the application stage.

[0044] Among them, the execution plan type specifically refers to the type of execution plan corresponding to each parameter value, such as a global execution plan type or an index execution plan type. Correspondingly, the target execution plan specifically refers to an execution plan constructed for a precompiled statement in combination with the execution plan types corresponding to each parameter value. The execution plan can combine the execution plans corresponding to each parameter value, thereby making the target execution plan more reasonable and reducing resource consumption. The target execution plan can make the sub-execution plan corresponding to each parameter value simultaneously established, thereby meeting usage requirements. Correspondingly, the target linked list specifically refers to a linked list that caches different target execution plans, and the target execution plans contained in the linked list are not repeated.

[0045] 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 parameter value hit data in the database, the execution plan type can be determined by comparing the parameter proportion with the threshold. In this embodiment, the determination of 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; calculate the parameter proportion corresponding to the target parameter value based on 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.

[0046] Specifically, the target parameter value specifically refers to any parameter value in the precompiled statement, and correspondingly, the target interval information specifically refers to the interval information corresponding to the target parameter value; correspondingly, the parameter proportion specifically refers to the proportion of the data corresponding to the target parameter value in the database. Correspondingly, the proportion threshold specifically refers to comparing the parameter proportions, and determining the threshold of the execution plan type corresponding to the target parameter value according to the comparison result, which can be set according to actual needs, and this embodiment does not make any limitation here. Correspondingly, the global execution plan type specifically refers to the target parameter value needing to access data in the database according to a global scan, and the index execution plan type specifically refers to the target parameter value needing to access data in the database according to an index scan; correspondingly, the global index execution plan type specifically refers to the type in which the target parameter value can access data in the database by both a global scan and an index scan.

[0047] 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; then the parameter proportion corresponding to the target parameter value can be calculated based on the target interval information and the parameter distribution histogram.

[0048] When the parameter ratio is greater than the preset ratio threshold, it means that the data corresponding to the target parameter value accounts for a high proportion in the database. If index scanning is used for access, it will consume more time. Therefore, a global scan method can be used to access the database, and the execution plan type of the target parameter value is determined to be a global execution plan type.

[0049] When the parameter proportion is less than the proportion threshold, it means that the data corresponding to the target parameter value in the database accounts for a low proportion. If a global scan is used, more resources and time will be consumed. Therefore, an index scan can be used to access the database, thereby determining that the execution plan type of the target parameter value is an index execution plan type.

[0050] When the parameter proportion is equal to the proportion threshold, it means that the proportion of the data corresponding to the target parameter value in the database is in the middle stream state, and the computing resources and time occupied by using global scan or index scan to access the database are basically the same. Therefore, either global scan or index scan can be selected, and the execution plan type of the target parameter value can be determined as a global index execution plan type.

[0051] Similarly, after determining the execution plan type corresponding to each parameter value, the target execution plan of the corresponding precompiled statement can be combined with the execution plan types corresponding to the various parameter values ​​to achieve database access when the execution plans corresponding to the various parameter values ​​are established at the same time, while reducing the consumption of computing resources.

[0052] Furthermore, when constructing a target execution plan that matches a precompiled statement according to the execution plan type corresponding to the parameter value, considering that the execution plan type corresponding to each parameter value may be different, in order to make the final 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: The plan combination value corresponding to the precompiled statement is calculated according to the execution plan type corresponding to each parameter value; the target execution plan corresponding to the precompiled statement is constructed according to the plan combination value; the target linked list is determined according to the cursor execution plan template of the precompiled statement, and the target execution plan is cached to the target linked list.

[0053] Specifically, the plan combination value specifically refers to a string obtained by combining the execution plan type corresponding to each parameter value, which can represent the target execution plan corresponding to the precompiled statement when the execution plan corresponding to each parameter value is established at the same time. Correspondingly, the cursor execution plan template specifically refers to a template pointing to a linked list storing the execution plan.

[0054] Based on this, in order to ensure that the target execution plan is more reasonable and save more computing resources, the plan combination value corresponding to the precompiled statement can be calculated according to the execution plan type corresponding to each parameter value; the plan combination value is used to represent that the execution plan corresponding to each parameter value can be established, and then the target execution plan corresponding to the precompiled statement can be constructed according to the plan combination value; thereafter, the target linked list is determined according to the cursor execution plan template of the precompiled statement, and the target execution plan is cached to the target linked list.

[0055] In actual applications, considering that each parameter value may correspond to a global scan and an index scan, if the precompiled statement contains two or more parameter values, there may be multiple possibilities for the target execution plan corresponding to the precompiled statement. For example, the parameter values ​​a and b in the precompiled SQL may both be indexes or global scans. At this time, there may be four execution plans constructed, which does not meet actual needs. Therefore, by calculating the combined value, it can be ensured that the optimal execution plan corresponding to each parameter value is established, thereby ensuring that there is only one most reasonable target execution plan for the precompiled statement. For example, the parameter value a in the precompiled SQL is determined to be an index scan after comparing the ratio threshold, and the parameter value b is determined to be a global scan after comparing the ratio threshold. At this time, the execution plan constructed is established at the same time that the parameter value a only passes the index and the parameter value b only passes the global scan, thereby making the target execution plan more reasonable and saving more computing resources.

[0056] In summary, by calculating the plan combination value, a string is determined for which the execution plan corresponding to each parameter value is valid, and the target execution plan is constructed based on this string. This can make the target execution plan more reasonable and will not occupy more computing resources and cache resources, so that it can be directly used in the reuse stage.

[0057] On this basis, considering that the execution plan construction of the precompiled statement occurs in the precompilation stage, in order to avoid repeatedly constructing the execution plan and occupying more cache, the target linked list can be traversed after obtaining the target execution plan, thereby ensuring that the target execution plan in the target linked list is unique. In this embodiment, the specific implementation method is as follows: The target linked list is traversed, and it is detected whether the target execution plan exists in the target linked list according to the traversal result; if so, a newly added precompiled statement is obtained, the newly added precompiled statement is used as the precompiled statement, and the step of extracting at least one parameter value from the precompiled statement is executed; if not, the plan combination value is used as a key and the target execution plan is used as a value, and the value is cached to the target linked list according to the key.

[0058] Specifically, the newly added prepared statement specifically refers to a prepared statement newly submitted by the client in the current scenario.

[0059] Based on this, after obtaining the target execution plan corresponding to the precompiled statement, in order to avoid repeatedly caching the execution plan of the same logic, the target linked list can be traversed, and whether the currently obtained target execution plan exists in the target linked list can be detected according to the traversal result; if so, the target execution plan can be not cached, and the subsequent precompiled statements can be continued to be processed, so the newly added precompiled statement can be obtained, and the newly added precompiled statement can be used as the precompiled statement, and the step of extracting at least one parameter value in the precompiled statement can be executed; if not, it means that the target execution plan corresponding to the precompiled statement does not exist in the target linked list at this time, so the plan combination value can be used as the key and the target execution plan as the value, and the value can be cached to the target linked list according to the key for subsequent access to the target linked list.

[0060] In actual applications, when constructing the target execution plan corresponding to the precompiled statement, the calculated plan combination value can be represented in 4-byte hexadecimal format. The smallest bit can represent the scanning method corresponding to the first parameter, the second bit can represent the scanning method corresponding to the second parameter, and so on. It should be noted that since the access to a table may use two indexes at the same time, 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, you can choose to ignore them. Since the scanning method corresponding to the parameter value cannot correspond to the index, no statement needs to pay attention to what the condition is.

[0061] In specific implementation, 1 can be used to represent a global scan and 2 to represent an index scan. For example, 0x00000012 indicates that the filter condition of the SQL statement involves two database access methods. The first one needs to be scanned according to the index, and the second one needs to be scanned according to the global scan. After that, the target linked list executed by the cursor plan execution template can be traversed. If 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 as the value, which are stored at the end of the target linked list to be passed to the executor for execution in the application phase.

[0062] Furthermore, considering that there may be multiple precompiled statements and each precompiled statement involves one or more parameter values, the above process can be repeated to complete the construction of a new execution plan and store the value target linked list. After n times of processing, the statement optimizer can cache various potential execution plans for the cursor into the target linked list, so that in the application stage, the execution plan suitable for the current data processing needs can be directly found in the linked list for reuse, so that no more time is spent on building the execution plan, thereby improving data operation efficiency.

[0063] Using 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 an index scan type, parameter value B is a global scan type, and parameter value C is an index scan type. Therefore, the calculated combination value corresponding to the precompiled SQL is calculated according to the scan type determined by the above proportion information. At this time, the combination value is 0x212. On this basis, the target execution plan corresponding to the precompiled SQL can be constructed. The first access in the execution plan is an index scan, the second access is a global scan, and the third access is an index scan. Then, the combination value 0x212 can be used as a key, and it is detected whether there is a value corresponding to the key in the target linked list. If it exists, the subsequent processing continues; 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.

[0064] 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 it can also ensure that the corresponding target execution plan is quickly found and used in the application stage.

[0065] Step S208, when receiving an access request associated with the precompiled statement submitted to the database, executing the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0066] Specifically, after the target linked list stores the target execution plan through 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 means 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, thereby quickly responding to the access request and making feedback.

[0067] Among them, the access request specifically refers to a request submitted to the database, which carries the parameter value of the precompiled statement to be inserted. The parameter value is obtained by parsing the access request, and the parameter value is directly inserted into the precompiled statement corresponding to the request, and the cached target execution plan is read in the linked list according to the precompiled statement to execute the data processing task, which can effectively improve the data processing efficiency. Correspondingly, the data processing task specifically refers to the task of determining the target data in the database in response to the access request and performing operations such as adding, deleting, modifying and checking, which can be set according to actual needs, and this embodiment does not make any restrictions here.

[0068] Using the above example, when receiving access requests for associated data tables a, b, and c, the parameter values ​​carried in the request can be added to the precompiled SQL, and then the execution plan associated with the precompiled SQL can be read in the target linked list. For example, the execution plan corresponding to the combination value 0x212 can be used to scan data table a according to the index information, and data table b can be globally scanned, and data table c can be scanned according to the index information. After determining the target data according to the scan results, the target data can be added, deleted, modified, and checked according to the access request. For example, in a securities trading scenario, the target data corresponds to multiple users who violated the operation rules, and the current access request is to read the data for risk detection.

[0069] Furthermore, in order to quickly abolish the execution plan in the target linked list when an execution plan failure event occurs in the database to avoid affecting subsequent business, event detection can be performed. In this embodiment, the specific implementation method is as follows: Event detection is performed on the database; when an execution plan failure 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.

[0070] Specifically, an execution plan invalidation 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 statistical information collection. At this time, the cached execution plan will become invalid. In order to avoid the subsequent use of the invalid execution plan and affect data processing, this can be achieved by updating the template to an invalid state.

[0071] Based on this, in order to avoid the invalidation of the execution plan and the impact on data processing, event detection can be performed on the database; when the invalidation event of the execution plan of the database is detected, it means that the event occurring at this time will affect the use of the execution plan, so the cursor execution plan template can be updated to an invalid state, making the execution plan contained in the target linked list invalid.

[0072] That is to say, after caching the target execution plan through the target linked list, the operation here is only to convert the single element into a linked list storage. If DDL or statistical information occurs, the cached execution plan will be invalid. Therefore, the execution plan template of the cursor can be updated to an invalid state according to the original logic, thereby ensuring that the execution plan is not reused without the need to clean up the execution plan separately.

[0073] In summary, timely updating of the execution plan by detecting failure events can effectively prevent subsequent data processing operations from being affected.

[0074] In addition, in order to save cache, the execution times of the execution plans in the target linked list can be regularly detected to delete the execution plans with fewer execution times from the target linked list, leaving more cache space for the execution plans with more execution times. In this embodiment, the specific implementation is as follows: The execution times of the execution plans included in the target linked list are counted 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.

[0075] Specifically, the plan execution information specifically refers to the execution count statistics corresponding to each execution plan included in the target linked list. Correspondingly, the execution plan to be deleted specifically refers to the execution plan that needs to be deleted in the target linked list. Correspondingly, the time interval is the time for executing the deletion operation, which can be set according to actual needs, and this embodiment does not make any limitation here.

[0076] Based on this, considering that the target linked list contains a large number of execution plans, 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 situations where precompiled statements are reused less frequently, and their corresponding execution plans will also be used in small quantities. Therefore, in order to avoid occupying more cache space, execution plan cleanup operations can be performed regularly.

[0077] That is to say, the execution times of the execution plans included in the target linked list can be counted to obtain the plan execution information; the reuse rate of each execution plan can be determined according to the execution information, and then the execution plans to be deleted with low reuse rate can be screened in the target linked list based on the plan execution information, such as sorting them from small to large according to the reuse rate, selecting a set number of execution plans that are sorted first as the execution plans to be deleted, and marking the execution plans to be deleted. In this way, the execution plans to be deleted in the target linked list can be deleted according to the preset time interval, thereby saving more cache space.

[0078] In summary, in order to be able to build a matching execution plan for a precompiled statement and avoid the execution plan from occupying more computing resources while ensuring 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 proportion of each parameter value in the database. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, so as to ensure that the target execution plan is more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0079] The following combination Figure 3 , taking the application of the execution plan construction method provided in this specification in the scenario of inserting data into a database as an example, the execution plan construction method is further described. Figure 3 A processing flow chart of a method for constructing an execution plan provided in an embodiment of the present specification is shown, which specifically includes the following steps.

[0080] Step S302, obtaining the precompiled statements of the database, and determining the parameter distribution histogram corresponding to the database.

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

[0082] Step S306: The statement optimizer calculates the interval information corresponding to each parameter value in the parameter distribution histogram, and determines the execution plan type of each parameter value according to the interval information.

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

[0084] Step S308, calculating the plan combination value corresponding to the prepared statement according to the execution plan type corresponding to each parameter value.

[0085] Step S310: construct a target execution plan corresponding to the precompiled statement according to the plan combination value.

[0086] Step S312, determining a target linked list according to the cursor execution plan template of the precompiled statement, and caching the target execution plan into the target linked list.

[0087] Step S314: receiving an access request for an associated precompiled statement submitted to the database.

[0088] Step S316, executing the data processing task corresponding to the access request by reading the target execution plan in the target linked list.

[0089] In summary, in order to be able to build a matching execution plan for a precompiled statement and avoid the execution plan from occupying more computing resources while ensuring 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 proportion of each parameter value in the database. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, so as to ensure that the target execution plan is more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0090] Corresponding to the above method embodiment, this specification also provides an execution plan construction device embodiment, Figure 4 FIG. 1 is a schematic diagram showing a structure of an execution plan construction device provided by an embodiment of the present specification. Figure 4 As shown, the device is applied to a database server and includes: The acquisition module 402 is configured to acquire a precompiled statement of a database and determine a parameter distribution histogram corresponding to the database; A determination module 404 is configured to extract at least one parameter value from the precompiled statement and determine interval information corresponding to each parameter value in the parameter distribution histogram; A construction module 406 is configured to determine the execution plan type of each parameter value according to the interval information, construct a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value, and cache it to a target linked list; The reading module 408 is configured to, upon receiving an access request associated with the precompiled statement submitted to the database, execute the data processing task corresponding to the access request by reading the target execution plan from the target linked list.

[0091] In an optional embodiment, the device further comprises: A generation module is configured to obtain a histogram corresponding to the database and determine multiple index fields associated with the database; map the multiple index fields to the histogram according to distribution information, and generate a parameter distribution histogram corresponding to the database according to the mapping result.

[0092] In an optional embodiment, the determining module 404 is further configured to: The precompiled statement is parsed to obtain at least one parameter value, and each parameter value is input into a statement optimizer of the database server; and the interval information corresponding to each parameter value in the parameter distribution histogram is calculated by the statement optimizer.

[0093] In an optional embodiment, 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; calculate the parameter proportion corresponding to the target parameter value based on 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.

[0094] In an optional embodiment, the construction module 406 is further configured to: The plan combination value corresponding to the precompiled statement is calculated according to the execution plan type corresponding to each parameter value; the target execution plan corresponding to the precompiled statement is constructed according to the plan combination value; the target linked list is determined according to the cursor execution plan template of the precompiled statement, and the target execution plan is cached to the target linked list.

[0095] In an optional embodiment, the construction module 406 is further configured to: The target linked list is traversed, and it is detected whether the target execution plan exists in the target linked list according to the traversal result; if so, a newly added precompiled statement is obtained, the newly added precompiled statement is used as the precompiled statement, and the step of extracting at least one parameter value from the precompiled statement is executed; if not, the plan combination value is used as a key and the target execution plan is used as a value, and the value is cached to the target linked list according to the key.

[0096] In an optional embodiment, the device further comprises: The detection module is configured to perform event detection on the database; when an execution plan failure 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 invalid.

[0097] In an optional embodiment, the device further comprises: The deletion module is configured to count the number of executions of the execution plans contained in the target linked list to obtain plan execution information; based on the plan execution information, the execution plans to be deleted are screened in the target linked list, and the execution plans to be deleted in the target linked list are deleted at preset time intervals.

[0098] In summary, in order to be able to build a matching execution plan for a precompiled statement and avoid the execution plan from occupying more computing resources while ensuring 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 proportion of each parameter value in the database. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, so as to ensure that the target execution plan is more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0099] The above is a schematic scheme of an execution plan construction device of this embodiment. It should be noted that the technical scheme of the execution plan construction device and the technical scheme of the execution plan construction method mentioned above belong to the same concept, and the details not described in detail in the technical scheme of the execution plan construction device can be found in the description of the technical scheme of the execution plan construction method mentioned above.

[0100] Corresponding to the above method embodiment, this specification also provides an execution plan construction system embodiment, Figure 5 FIG. 1 shows a schematic diagram of a structure of an execution plan construction system provided by an embodiment of this specification. Figure 5As shown, the execution plan construction system 500 includes a client 510 and a database server 520, including: The client 510 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 520 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 the execution plan type of each parameter value according to the interval information, and construct a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and cache it to a target linked list; 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.

[0101] In summary, in order to be able to build a matching execution plan for a precompiled statement and avoid the execution plan from occupying more computing resources while ensuring 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 proportion of each parameter value in the database. On this basis, the execution plan type 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 to the target linked list; when constructing the execution plan of the precompiled statement, it can be realized by analyzing the proportion of parameter values ​​in the database, so as to ensure that the target execution plan is more in line with the execution requirements of the precompiled statement, and the data processing operation corresponding to the statement can be completed under 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, thereby pre-building the most reasonable execution plan for data processing operations, which can better improve the accuracy of the execution plan and save more computing resources.

[0102] The above is a schematic scheme of an execution plan construction system of this embodiment. It should be noted that the technical scheme of the execution plan construction system and the technical scheme of the execution plan construction method described above belong to the same concept, and the details not described in detail in the technical scheme of the execution plan construction system can be found in the description of the technical scheme of the execution plan construction method described above.

[0103] Figure 6The block diagram of a computing device 600 according to an embodiment of the present specification is shown. 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 via a bus 630, and the database 650 is used to store data.

[0104] 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 these networks include a public switched telephone network (PSTN), a local area network (LAN), a wide area network (WAN), a 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 network interface (e.g., a network interface card (NIC)) that is wired or wireless, such as an IEEE 802.11 wireless local area network (WLAN) wireless interface, a world-wide interoperability for microwave access (Wi-MAX) interface, an Ethernet interface, a universal serial bus (USB) interface, a cellular network interface, a Bluetooth interface, and a near field communication (NFC).

[0105] In one embodiment of the present specification, the above components of the computing device 600 and Figure 6 Other components not shown in the figure may also be connected to each other, for example, via a bus. It should be understood that Figure 6 The computing device structure block diagram shown is only for the purpose of illustration, and is not intended to limit the scope of this specification. Those skilled in the art can add or replace other components as needed.

[0106] The computing device 600 may 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 smart phone), a wearable computing device (e.g., a smart watch, 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 may also be a mobile or stationary server.

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

[0108] The above is a schematic scheme of a computing device of this embodiment. It should be noted that the technical scheme of the computing device and the technical scheme of the execution plan construction method described above are of the same concept, and the details not described in detail in the technical scheme of the computing device can be found in the description of the technical scheme of the execution plan construction method described above.

[0109] An embodiment of the present specification also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the steps of the above-mentioned execution plan construction method.

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

[0111] An embodiment of the present specification also provides a computer program product, including a computer program or instructions, which implement the steps of the above-mentioned execution plan construction method when executed by a processor.

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

[0113] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0114] The computer instructions include computer program codes, which may be in source code form, object code form, 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, mobile hard disk, magnetic disk, optical disk, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium, etc. It should be noted that the content contained in the computer-readable medium may be appropriately increased or decreased according to the requirements of patent practice. For example, in some regions, according to patent practice, computer-readable media do not include electric carrier signals and telecommunication signals.

[0115] It should be noted that, for the above-mentioned method embodiments, for the sake of simplicity of description, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the embodiments of this specification are not limited by the order of the actions described, because according to the embodiments of this specification, some steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all preferred embodiments, and the actions and modules involved are not necessarily required by the embodiments of this specification.

[0116] In the above embodiments, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0117] The preferred embodiments of this specification disclosed above are only used to help explain this specification. The optional embodiments do not describe all the details in detail, nor do they limit the invention to the specific implementation methods 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 in order to better explain the principles and practical applications of the embodiments of this specification, so that technicians in the relevant technical field can understand and use this specification well.

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, build the target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value, and cache it in the target linked list; 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 the execution plan type of the target parameter value to be 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 step of constructing a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and caching the plan in a target linked list includes: Calculate the plan combination value corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value; Constructing a target execution plan corresponding to the precompiled statement according to the plan combination value; A target linked list is determined according to a cursor execution plan template of the precompiled statement, and the target execution plan is cached in the target linked list.

6. The execution plan construction method according to claim 5, 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.

7. The execution plan construction method according to claim 5 or 6, characterized in that: After the step of constructing a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and caching it in the target linked list is executed, the step further includes: Performing event detection on the database; When an execution plan failure 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.

8. The execution plan construction method according to any one of claims 1 to 6, characterized in that: After the step of constructing a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and caching it in the target linked list is executed, the step 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.

9. 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, construct a target execution plan corresponding to the precompiled statement according to the execution plan type corresponding to each parameter value and cache it in a target linked list; and upon receiving an access request associated with the precompiled statement submitted to the database, execute a data processing task corresponding to the access request by reading the target execution plan in the target linked list.

10. 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 8 are implemented.

11. 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 8.

12. 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 8.

Images