Database query method and device, electronic equipment and storage medium

Abstract

The invention provides a database query method and device, electronic equipment and a storage medium, and the method comprises the steps: obtaining parameterized query statements, and generating a plurality of corresponding execution paths based on the parameterized query statements, the parameterized query statements being query statements with filtering conditions comprising parameters, the filtering condition of each execution path in the plurality of execution paths comprises a parameter; respectively configuring at least one parameter value into the parameter of each execution path to obtain the execution cost of each execution path, and selecting the execution path with the minimum execution cost from the plurality of execution paths as a first general execution plan; and comparing the execution cost of the first general execution plan with the execution cost of a default execution plan of the parameterized query statement, setting the default execution plan according to a comparison result, and executing the parameterized query statement based on the default execution plan to query the database.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a database query method, device, electronic device and storage medium. Background Art

[0002] In a database, the query optimizer is an important module that connects the previous and the next. It is responsible for performing logical equivalent transformation on the query tree generated by the syntax analysis module, screening the physical execution path, and selecting the execution path with the lowest execution cost to pass to the executor module of the database. In other words, the query optimizer is responsible for converting the query tree into an execution plan. If a SQL (Structured Query Language) query statement is executed repeatedly, the query optimization process will be executed repeatedly, that is, the execution plan will be generated repeatedly, resulting in unnecessary performance loss. In a related technology, the SQL query statement is executed by caching a general execution plan to avoid performance loss caused by the query optimization process being executed repeatedly. On the one hand, when processing the filtering conditions, the general execution plan usually estimates the execution cost by the default selection rate due to the lack of specific parameter values, and cannot accurately reflect the actual execution cost of the general execution plan, which may cause the general execution plan to be unable to be cached due to the high execution cost; on the other hand, the cached general execution plan may become less efficient as the data in the database changes and the user's query habits change, resulting in reduced execution efficiency. Summary of the invention

[0003] The embodiments of the present application provide a database query method, device, electronic device and storage medium to solve the problems existing in the related art.

[0004] In a first aspect, an embodiment of the present application provides a database query method, comprising: obtaining a parameterized query statement, and generating corresponding multiple execution paths based on the parameterized query statement, wherein the parameterized query statement is a query statement whose filtering conditions include parameters, and the filtering conditions of each execution path in the multiple execution paths include the parameters; configuring at least one parameter value to the parameters of each execution path respectively, obtaining the execution cost of each execution path, and selecting the execution path with the smallest execution cost from the multiple execution paths as a first general execution plan; comparing the execution cost of the first general execution plan with the execution cost of a default execution plan of the parameterized query statement, setting the default execution plan according to the comparison result, and executing the parameterized query statement based on the default execution plan to query the database.

[0005] In a second aspect, an embodiment of the present application provides a database query device, comprising: an execution path generation unit, configured to obtain a parameterized query statement, and generate corresponding multiple execution paths based on the parameterized query statement, wherein the parameterized query statement is a query statement whose filtering condition includes a parameter, and the filtering condition of each execution path in the multiple execution paths includes the parameter; an execution plan generation unit, configured to respectively configure at least one parameter value to the parameter of each execution path, obtain the execution cost of each execution path, and select the execution path with the smallest execution cost from the multiple execution paths as a first general execution plan; a query unit, configured to compare the execution cost of the first general execution plan with the execution cost of the default execution plan of the parameterized query statement, set the default execution plan according to the comparison result, and execute the parameterized query statement based on the default execution plan to query the database.

[0006] In a third aspect, an embodiment of the present application provides an electronic device, including a memory, a processor, and a computer program stored in the memory, wherein the processor implements any of the above methods when executing the computer program.

[0007] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described in any one of the above is implemented.

[0008] Compared with the prior art, this application has the following advantages:

[0009] According to the embodiment of the present application, by configuring the parameter value into the execution path, the execution cost of each execution path can be calculated according to the real user input information and the real-time data distribution in the database, and the execution path with the lowest execution cost is used as the general execution plan, and the accurate execution cost of the general execution plan can be obtained, and the general execution plan is more in line with the user's query habits. By comparing the execution cost of the general execution plan with the execution cost of the default execution plan of the parameterized query statement to set the default execution plan, the parameterized query statement can be executed based on the execution plan with a better cost, thereby improving the query efficiency of the database.

[0010] The above description is only an overview of the technical solution of the present application. In order to more clearly understand the technical means of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In the accompanying drawings, unless otherwise specified, the same reference numerals throughout the multiple drawings represent the same or similar parts or elements. These drawings are not necessarily drawn to scale. It should be understood that these drawings only depict some embodiments according to the present application and should not be regarded as limiting the scope of the present application.

[0012] Figure 1 is a schematic diagram showing the overall architecture of a database according to an embodiment of the present application;

[0013] Figure 2 is a flow chart showing a database query method according to an embodiment of the present application;

[0014] Figure 3 It is a schematic diagram showing a flow chart of setting a default execution plan for a parameterized query statement according to an embodiment of the present application;

[0015] Figure 4 is a schematic diagram showing a process of collecting parameter values ​​according to an embodiment of the present application;

[0016] Figure 5 is a schematic diagram showing a flow chart of optimizing a general execution plan according to an embodiment of the present application;

[0017] Figure 6 is a structural block diagram showing a database query device according to an embodiment of the present application; and

[0018] Figure 7 is a block diagram showing an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION

[0019] In the following, only some exemplary embodiments are briefly described. As those skilled in the art will appreciate, the described embodiments may be modified in various ways without departing from the concept or scope of the present application. Therefore, the drawings and descriptions are considered to be exemplary in nature and not restrictive.

[0020] To facilitate understanding of the technical solutions of the embodiments of the present application, the following describes the related technologies of the embodiments of the present application. The following related technologies can be combined with the technical solutions of the embodiments of the present application as optional solutions, and they all belong to the protection scope of the embodiments of the present application.

[0021] The embodiments of the present application involve applications related to the database field. In order to better understand the solutions of the embodiments of the present application, the relevant terms and concepts in the database field that may be involved in the embodiments of the present application are first introduced below.

[0022] Extended query protocol: Extended query protocol is to split the simple query protocol into several steps. The simple query protocol is that the database client sends a data packet containing SQL (Structured Query Language) query statements to the database server, and the database server returns the query results after execution. The extended query protocol is to split the simple query protocol into several steps such as Parse, Bind, Describe, Execute, and Close. The results of these steps can be reused multiple times to improve query efficiency. In the extended query protocol, for SQL query statements that the database client needs to execute repeatedly, the values ​​of the filter conditions in the SQL query statement can be provided as independent parameters instead of having to insert them directly into a query string. For example, if the SQL query statement SELECT*FROM T1WHERE T1.num=3 needs to be executed repeatedly, the SQL query statement can be converted into a parameterized SQL query statement, that is, SELECT*FROM T1 WHERE T1.num=$1, and then the parameter values ​​can be provided to the parameterized SQL query statement to query the database to improve execution efficiency.

[0023] Customized execution plan: By configuring specific parameter values ​​into the parameters of the parameterized SQL query statement, the query optimizer can generate the corresponding customized execution plan.

[0024] General execution plan: NULL value is configured in the parameter of parameterized SQL query statement, and the query optimizer can generate the corresponding general execution plan. After the general execution plan is cached, it is used to execute the parameterized SQL query statement, that is, no matter what the parameter value passed in the parameterized SQL query statement is, the general execution plan is used to execute the parameterized SQL query statement.

[0025] Execution cost calculation: When the query optimizer generates an execution plan for a SQL query statement, it generates a large number of qualified execution paths (for example, scan paths and join paths). Each path has a corresponding execution cost. Then, the path with the lowest execution cost is selected as the execution plan through a dynamic programming algorithm or a genetic algorithm.

[0026] The following will refer to Figure 1 The overall architecture of the database of this application is described. Figure 1 Schematic diagram of the overall architecture of a database according to an embodiment of the present application. Figure 1As shown, in the database, the query optimization module is an important module that connects the previous and the next. It is responsible for performing logical equivalent transformation and physical execution path screening on the query tree generated by the syntax analysis module based on the query statement, and selecting the execution path with the lowest execution cost to pass to the executor module of the database. In a related technology before this application, for parameterized SQL query statements, the query optimizer of the database will generate five customized execution plans according to the five parameter values ​​actually passed in during the first five executions, and calculate the average execution cost of these five customized execution plans. At the sixth execution, the query optimizer of the database generates a corresponding general execution plan for the parameterized SQL statement. If the execution cost of the general execution plan is less than the average execution cost of the first five customized execution plans, the general execution plan is cached and used for subsequent database queries based on the general execution plan. There are two problems with using the above method for database query. On the one hand, the selectivity is one of the important factors for calculating the execution cost of the execution plan. The proportion of data filtered out based on the filter conditions in the SQL query statement to the total number is called the selectivity, that is, the selectivity = the number of tuples after filtering by the filter conditions / the total number of tuples before filtering by the filter conditions. When processing filter conditions, the general execution plan usually estimates the execution cost by using the default selectivity due to the lack of specific parameter values. The default selectivity is not calculated based on the actual data distribution in the database, so the accurate execution cost of the general execution plan cannot be obtained, which may cause the execution cost of the general execution plan to be higher than that of the customized execution plan, which in turn causes the general execution plan to be eliminated when compared with the customized execution plan, and cannot be cached and used for subsequent database queries. On the other hand, if the general execution plan is cached, it is necessary to execute parameterized SQL query statements with different parameter values ​​based on the general execution plan to query the database. Since the cached general execution plan lacks an update mechanism, as the data in the database changes, the cached general execution plan may become less efficient, resulting in reduced database execution efficiency. For example, at time A, there are one thousand data in the database, and the cached general execution plan is to perform a sequential scan of the table to query the database; at time B, there are one million data in the database, and if the cached general execution plan is still used to perform a sequential scan of the table, the query efficiency will be reduced. As user query habits change, the cached general execution plan may also become less efficient, resulting in reduced database execution efficiency. For example, at time A, the selection rate of data that users frequently query is higher than 80%. At this time, the cached general execution plan is to perform a sequential scan of the table to query the database; at time B, the selection rate of data that users frequently query is lower than 5%. At this time, if the cached general execution plan is still used to perform a sequential scan of the table, the query efficiency will be reduced.

[0027] In view of this, an embodiment of the present application provides a database query method, which is applied to a database server to completely or partially solve the problems existing in the above-mentioned related technologies. Figure 2 The database query method 200 of the present application is described. Figure 2 FIG. 2 is a flow chart showing a database query method 200 according to an embodiment of the present application. Figure 2 As shown, the database query method 200 may include the following steps S201 to S203. Steps S201 to S203 will be described in detail below in conjunction with a specific embodiment.

[0028] First, proceed to step S201. In step S201, a parameterized query statement is obtained, and corresponding multiple execution paths are generated based on the parameterized query statement, wherein the parameterized query statement is a query statement whose filtering condition includes a parameter, and the filtering condition of each execution path in the multiple execution paths includes the parameter.

[0029] A parameterized query statement can be an SQL query statement that replaces constants in the filter condition with parameters. If an SQL query statement needs to be queried repeatedly, the constants in the filter condition of the SQL query statement can be replaced with parameters such as $1, $2, etc., so as to convert the SQL query statement into a parameterized query statement. For example, an SQL query statement is SELECT*FROM T1 WHERE T1.num=3. In the SQL query statement, T1.num=3 represents the filter condition. The purpose of the SQL query statement is to query all tuples with T1.num=3 in the T1 table. The parameterized query statement corresponding to the SQL statement is SELECT*FROM T1 WHERE T1.num=$1. T1.num=$1 is the filter condition of the parameterized query statement, and $1 is a parameter in the filter condition. The parameter can be assigned different values. Then, the parameter in the parameterized query statement can be configured as a NULL value to generate a general execution plan corresponding to the parameterized query statement. In the process of generating the execution plan, multiple execution paths that meet the conditions will be generated. For example, the purpose of an SQL query statement is to query the data in the T1 table. Multiple execution paths can be generated to complete the query operation. Execution path 1 can be to query the data in the T1 table through sequential scanning, and execution path 2 can be to query the data in the T1 table through index scanning, etc. The filtering conditions of the above execution paths are the filtering conditions of parameterized query statements, that is, the filtering conditions of these execution paths include parameters. For parameterized query statements for single-table queries, the execution path is also the scanning path for the data table to be queried (for example, sequential scanning, full table scanning, bitmap scanning, etc.). For parameterized query statements for multi-table connection queries, the execution path includes not only the scanning path, but also the connection paths between multiple data tables (for example, hash connection, nested loop connection, merge connection, etc.).

[0030] In one embodiment, before generating corresponding multiple execution paths based on the parameterized query statement, the database query method 200 also includes: generating at least one customized execution plan by configuring at least one input parameter value to the parameters of the parameterized query statement respectively; generating a second general execution plan by configuring a NULL value to the parameters of the parameterized query statement; and setting the second general execution plan as the default execution plan when the execution cost of the second general execution plan is less than the average execution cost of the at least one customized execution plan.

[0031] In one case, after receiving a parameterized query statement for the first time, the database server needs to determine a default execution plan corresponding to the parameterized query statement. First, the database client uses the extended query protocol, and the database server receives a Parse message and a Bind message sent by the database client. The Parse message includes the parameterized query statement, and the Bind message includes a specific parameter value. The database server generates at least one customized execution plan by configuring at least one parameter value input by the database client (i.e., the user) to the parameter of the parameterized query statement. For example, the parameterized query statement is SELECT*FROM T1 WHERE T1.num=$1, and the user enters 5 parameter values, which are 2, 4, 6, 8, and 10, respectively. The parameter value 2 is used for explanation, and the parameter value 2 is configured in $1. Then the parameterized query statement becomes a specific query statement, that is, SELECT*FROM T1 WHERE T1.num=2. The query optimizer of the database needs to convert the execution path with the lowest cost into a customized execution plan based on the distribution of data in the database at this time and the selectivity of the filter condition (that is, T1.num=2). Similarly, the query optimizer can generate customized execution plans for specific query statements configured with other parameter values, and the contents of different customized execution plans may be different. The number of customized execution plans is at least one, which can be 5 or 7, and this application does not limit this. Then, the database server configures the NULL value into the parameter of the parameterized query statement, and the query optimizer converts the execution path with the lowest cost into the second general execution plan based on the default selectivity. The execution cost of the second general execution plan is compared with the average execution cost of the at least one customized execution plan. If the execution cost of the second general execution plan is less than the average execution cost of the at least one customized execution plan, the second general execution plan is set as the default execution plan. The parameterized query statement is then executed using the default execution plan, that is, no matter what parameters the user inputs for the parameterized query statement, the parameterized query statement is executed using the default execution plan.

[0032] According to an embodiment of the present application, when the second general execution plan is less than the average execution cost of at least one customized execution plan, the second general execution plan is set as the default execution plan, and then the parameterized query statement can be executed based on the default execution plan. There is no need to generate a customized execution plan for each parameter value, thereby improving the query efficiency of the database.

[0033] In one embodiment, a cache plan table is created to store data related to the general execution plan. The cache plan table may include 7 columns (i.e., attributes), namely oid, key, query_tree, plan_tree, version, is_valid, and total_cost. Among them, oid represents the object identifier of the general execution plan, which may be a serial number such as 1, 2, 3, etc.; key represents the name of the parameterized query statement corresponding to the general execution plan, which is used to identify different parameterized query statements; query_tree represents the serialized query tree corresponding to the general execution plan; plan_tree represents the serialized general execution plan; version represents the version number of the general execution plan, which is initially 0. When the general execution plan is updated, the version number is also updated; is_valid represents whether the general execution plan is valid, which is initially false. When the execution cost of the general execution plan is less than the average execution cost of at least one customized execution plan, is_valid is updated to true, indicating that the general execution plan can be used to execute the parameterized query statement corresponding to key; total_cost represents the execution cost of the general execution plan.

[0034] In one embodiment, a cache plan parameter table is created to store data related to parameters in a general execution plan, and a cache plan parameter value table is created to store data related to parameter values. The cache plan parameter table may include four columns, namely oid, attr_name, attr_id, and attr_oid. Among them, oid is associated with oid in the cache plan table and is used to represent a general execution plan; attr_name represents the name of the parameter in the general execution plan; attr_id represents the parameter sequence number in the general execution plan, which can be a parameter sequence number such as $1, $2; attr_oid represents the object identifier of the parameter in the general execution plan, which can be a sequence number such as 1, 2, 3, etc. The cache plan parameter value table may include four columns, namely attr_oid, attr_value, count, and timestamp. Among them, attr_oid is associated with attr_oid in the cache plan parameter table and is used to represent a parameter; attr_value represents the parameter value entered by the user; count represents the number of times the parameter value is entered; and timestamp represents the time when the parameter value was last entered.

[0035] The following will refer to Figure 3 Explain how to set the default execution plan for parameterized query statements. Figure 3 FIG. 1 is a flow chart showing a default execution plan for setting a parameterized query statement according to an embodiment of the present application. Figure 3As shown, after receiving the parameterized query statement for the first time, the database server needs to determine whether the general execution plan of the parameterized query statement has been generated. If the general execution plan has not been generated, the parameters in the parameterized query statement are set to NULL to generate a general execution plan, serialize the general execution plan, and then initialize the cache plan table and insert the serialized general execution plan. Next, the position information of the parameters in the parameterized query statement and the corresponding field information are obtained, and a record is generated and inserted into the cache plan parameter table. If the general execution plan has been generated, it is necessary to determine whether the number of customized execution plans is not greater than 5. If the number of customized execution plans is not greater than 5, continue to generate customized execution plans, obtain parameter values ​​in the customized execution plans, generate records and insert them into the cache plan parameter value table. If the number of customized execution plans is greater than 5, it is necessary to determine whether the general execution plan is better, that is, determine whether the execution cost of the general execution plan is less than that of the customized execution plan. If the general execution plan is not better, the customized execution plan is used to execute the parameterized query statement. When the general execution plan is better, the cache plan table is updated, and the attribute is_valid of the general execution plan recorded in the cache plan table is set to true, and then the general execution plan is used to execute the parameterized query statement.

[0036] The following will refer to Figure 4 Explain the parameter values ​​collected in the Bind message. Figure 4 FIG. 1 is a schematic diagram showing a flow chart of collecting parameter values ​​according to an embodiment of the present application. Figure 4 As shown, after receiving the Bind message sent by the database client, the database server can obtain the name of the parameterized query statement (prepared stmt name) in the Bind message, and use the name of the parameterized query statement as the key to search the cache plan table to obtain the object identifier of the general execution plan corresponding to the parameterized query statement. Then, the object identifier is searched in the cache plan parameter table to obtain the object identifier (attr_oid) and parameter sequence number (attr_id) of the parameter, and the parameter value in the Bind message is found according to the parameter sequence number. Next, the found parameter value is inserted into the cache plan parameter value table. If the parameter value already exists, the value of the number of times the parameter value is used (count) is increased by 1.

[0037] Next, proceed to step S202. In step S202, at least one parameter value is respectively configured to the parameters of each execution path to obtain the execution cost of each execution path, and the execution path with the minimum execution cost is selected from the multiple execution paths as the first general execution plan.

[0038] The first general execution plan is the execution process of the parameterized query statement in the database. The data in the database and the query habits of the user are constantly changing. For a certain parameterized query statement, the execution efficiency of the general execution plan A generated at time A is relatively high. At time B, due to the change of data or the change of the query habits of the user, the execution efficiency of the general execution plan A may be reduced. For example, at time A, there are only 1,000 pieces of data in the database, and the content of the general execution plan A generated at this time includes sequential scanning. At time B, there are 1 million pieces of data in the database. If the sequential scanning in the general execution plan A is still used to execute the parameterized query statement to query the database, the execution efficiency will be seriously reduced. For example, at time A, the selection rate of the data frequently queried by the user is higher than 80%. The content of the general execution plan A generated at this time includes sequential scanning. At time B, the selection rate of the data frequently queried by the user is lower than 5%. If the sequential scanning in the general execution plan A is still used to execute the parameterized query statement to query the database, the execution efficiency will be seriously reduced. Therefore, it is necessary to regularly generate a general execution plan for the parameterized query statement.

[0039] In one case, the database query method 200 is executed regularly, for example, at 24:00 every day, a new general execution plan is generated for the parameterized query statement, the execution cost of the newly generated general execution plan is compared with the execution cost of the default execution plan, and the parameterized query statement is executed based on the execution plan with a better cost to query the database. Since the general execution plan lacks specific parameter values ​​when processing filtering conditions, the execution cost is usually estimated by the default selection rate. The default selection rate is not calculated based on the actual data distribution in the database, and it is impossible to select the execution path with the lowest cost as the general execution plan based on the precise execution cost. Therefore, it is necessary to calculate the execution cost of each execution path based on the parameters input by the user, so as to select the execution path with the lowest execution cost as the general execution plan.

[0040] In order to make the execution cost calculation of the execution path more accurate, the execution cost of the general execution plan can be estimated based on the parameter value input by the user. It is necessary to configure at least one parameter value to the parameters of each execution path respectively to obtain the execution cost of each execution path, and select the execution path with the smallest execution cost from the multiple execution paths as the first general execution plan, so that the execution cost of the first general execution plan is more accurate and the first general execution plan is more in line with the user's query habits.

[0041] In one embodiment, each execution path includes multiple layers of sub-execution paths, and configuring at least one parameter value to the parameters of each execution path includes: configuring the at least one parameter value to the parameters of the first layer of sub-execution paths, and obtaining the corresponding execution cost and output line number; inputting the execution cost and output line number into the next layer of sub-execution paths, and obtaining the execution cost and output line number of the next layer of sub-execution paths; repeatedly inputting the execution cost and output line number into the next layer of sub-execution paths, and obtaining the execution cost and output line number of the next layer of sub-execution paths, until the next layer of sub-execution paths is the last layer of sub-execution paths.

[0042] Each execution path may include multiple layers of sub-execution paths. For example, an execution path may include two layers of sub-execution paths. The first layer of sub-execution paths may be scan paths, and the second layer of sub-execution paths may be connection paths. First, at least one parameter may be configured into the parameters of the first layer of sub-execution paths to obtain the corresponding execution cost and number of output rows. The execution cost and number of output rows of the previous layer of sub-execution paths may be iteratively brought into the calculation of the next layer to obtain the execution cost of the execution path. Since the execution cost of each layer is the actual execution cost obtained based on the information input by the user, the accurate execution cost of the execution path may be obtained.

[0043] In one case, the parameterized query statement is a single-table query statement, and the execution path generated for the statement includes at least one scan path (for example, a sequential scan path, an index scan path, a bitmap scan path, etc.), and the scan path is a parameterized path (that is, the filtering condition of the scan path includes a parameter). At least one parameter value is configured in each scan path respectively, and at least one corresponding customized execution plan is generated. The actual execution time of the customized execution plan can be obtained through the ANALYZE operation of the database server, and the execution time is used as the execution cost of the corresponding scan path. If there are repeated parameter values, the previously obtained execution cost can be directly obtained to avoid repeated execution of the ANALYZE operation. The at least one execution cost corresponding to each scan path is averaged, the scan path with the lowest average execution cost is selected, and the corresponding first general execution plan is generated according to the path.

[0044] In one case, the parameterized query statement is a multi-table join query statement, and the execution path generated for the statement includes at least one scan path for each data table (for example, a sequential scan path, an index scan path, a bitmap scan path, etc.) and at least one connection path for a join query (for example, a hash join, a nested loop join, a merge join, etc.), and the scan path is a parameterized path (that is, the filter condition of the scan path includes a parameter). At least one parameter value is respectively configured to each scan path corresponding to each data table, and at least one corresponding sub-customized execution plan is generated. The actual execution time and the number of output rows of the sub-customized execution plan can be obtained through the ANALYZE operation on the database server, and the execution time is used as the execution cost of the corresponding scan path. If the connection path is a parameterized path, the scan path with the lowest execution cost for each table is combined by a dynamic programming algorithm or a genetic algorithm to generate a connection path, and at least one parameter value that meets the time filter condition is configured in the connection path by the same method as above to obtain the execution plan and output row number corresponding to each connection path; if the connection path is not a parameterized path, the execution cost and output row number of the connection path can be calculated by the execution cost and output row number transmitted by the scan path. By calculating layer by layer, the complete execution path with the lowest execution cost can be obtained, and the corresponding first general execution plan can be generated according to the path.

[0045] The following will refer to Figure 5 Explain the optimization of the general execution plan. Figure 5 1 is a flow chart showing an optimized general execution plan according to an embodiment of the present application. Figure 5 As shown, the asynchronous query optimization process can regularly optimize the general execution plan cached in the cache plan. First, it is necessary to determine whether the optimization execution cycle has timed out (i.e., whether the time from the last optimization has exceeded the preset time period). In the case of the optimization execution cycle timeout, the cache plan table can be traversed in order to obtain the general execution plan and its corresponding query tree, and then query optimization can be performed on the query tree. In the process of query optimization of the query tree, the execution path of the query tree can be generated first, and the parameter value can be obtained according to the parameter in the execution path. The parameter value is passed into the execution path, and the execution plan corresponding to the execution path can be generated, and the execution cost and output row number of the execution plan can be obtained. Then it is necessary to determine whether the number of set parameters exceeds the sampling threshold. If the number of parameters does not exceed the sampling threshold, the next parameter value is taken out and passed into the execution path to obtain the execution cost and output row number. In the case that the number of parameters exceeds the sampling threshold, the average execution cost and the average output row number are calculated and brought into the next layer of calculation to obtain the total execution cost of the execution path. The total execution cost is compared with the execution cost of the previous cached execution plan to update the corresponding record in the cache plan table.

[0046] In one embodiment, configuring at least one parameter value to the parameters of each execution path respectively includes: obtaining multiple parameter values ​​with time stamps, and filtering at least one parameter value that meets the time filtering condition from the multiple parameter values ​​according to the time stamps; and configuring at least one parameter value that meets the time filtering condition to the parameters of each execution path respectively.

[0047] The database server can store multiple parameter values ​​input by the user and the time when the parameter values ​​are input. After obtaining multiple parameter values ​​with time tags, at least one parameter value that meets the time filtering condition can be filtered from the multiple parameter values ​​according to the time tags, and then the at least one parameter value that meets the time filtering condition is respectively configured into the parameters of each execution path.

[0048] In one embodiment, filtering at least one parameter value that meets the time filtering condition from the multiple parameter values ​​according to the time stamp includes: filtering the parameter values ​​to be sampled that meet the time filtering condition from the multiple parameter values ​​according to the time stamp; sampling the parameter values ​​to be sampled to obtain at least one parameter value that meets the time filtering condition.

[0049] There may be a large number of parameter values ​​that meet the time filter condition. If all these parameter values ​​are configured in the parameters of the execution path, it may result in excessive calculation. The parameter values ​​that meet the time filter condition among multiple parameter values ​​can be used as parameter values ​​to be sampled, and the parameter values ​​to be sampled can be sampled. In one case, each parameter value to be sampled can be sorted according to the number of times it is input into the database server, and the total number of times multiple parameter values ​​to be sampled are input, and each parameter value to be sampled is numbered according to the number of times it is input. Then, at least one random number can be taken between 1 and the total number of times it is input, and the taken random number is used as the number of the parameter value to obtain the parameter value corresponding to the random number. For example, the parameters to be sampled include parameter values ​​1, 3, and 5. Parameter value 5 is passed in 10 times, parameter value 1 is passed in 15 times, and parameter value 3 is passed in 25 times. The total number of times passed in is 50. Parameter values ​​1, 3, and 5 are sorted in ascending order according to the number of times they are passed in. The sorting result is parameter value 5, parameter value 1, and parameter value 3. Parameter value 5 is numbered 1-10, parameter value 1 is numbered 11-25, and parameter value 3 is numbered 26-50. Two random numbers can be taken between 1 and 50, which are 16 and 39 respectively. 16 and 39 are used as the numbers of the parameter values. It can be obtained that random number 16 corresponds to parameter value 1, and random number 39 corresponds to parameter value 3. Through the above method, the parameter values ​​to be sampled are sampled to obtain at least one parameter value that meets the time filtering condition.

[0050] In one embodiment, filtering the parameter values ​​to be sampled that meet the time filtering condition from the multiple parameter values ​​according to the time stamp includes: calculating the difference between the current time and the preset time length; filtering the parameter values ​​to be sampled whose time stamp is after the difference from the multiple parameter values.

[0051] Since the data in the database and the user's query habits are constantly changing, the parameter values ​​to be sampled that meet the time filtering conditions can be the parameter values ​​recently entered by the user, for example, the parameter values ​​entered in the last 8 hours or 24 hours. The general execution plan generated based on the parameter values ​​recently entered by the user is more in line with the user's query habits and has higher query efficiency. First, the difference between the current time and the preset duration can be calculated, and then the parameter values ​​to be sampled whose time stamps are after the difference can be filtered from multiple parameter values. For example, the current time is 0:00 on July 31, 2023, the preset duration is 24 hours, and the difference between the current time and the preset duration is 0:00 on July 30, 2023. Therefore, the parameter values ​​to be sampled whose time stamps are after 0:00 on July 30, 2023 can be filtered from multiple parameter values.

[0052] According to the embodiment of the present application, the parameter values ​​recently input by the user are used as parameter values ​​that meet the time filtering conditions, so that a general execution plan that is more in line with the user's query habits can be generated, thereby improving the execution efficiency of the user's query.

[0053] Next, the process proceeds to step S203. In step S203, the execution cost of the first general execution plan is compared with the execution cost of the default execution plan of the parameterized query statement, the default execution plan is set according to the comparison result, and the parameterized query statement is executed based on the default execution plan to query the database.

[0054] In one embodiment, setting a default execution plan based on the comparison result includes: when the execution cost of the first general execution plan is less than the execution cost of the default execution plan, determining whether the first general execution plan is different from the default execution plan, and if so, setting the first general execution plan as the default execution plan.

[0055] When the execution cost of the first general execution plan is less than the execution cost of the default execution plan, it is also necessary to determine whether the first general execution plan is different from the default execution plan. If different, the first general execution plan is set as the default execution plan, and the parameterized query statement is executed based on the default execution plan to query the database.

[0056] In one embodiment, determining whether the first general execution plan is different from the default execution plan includes: serializing the first general execution plan and generating a corresponding first hash value; serializing the default execution plan and generating a corresponding second hash value; and determining whether the first hash value is different from the second hash value.

[0057] Serializing the first general execution plan is to convert the first general execution plan into a form that can be stored or transmitted, and then a first hash value can be generated for the serialized first general execution plan. Serializing the default execution plan is to convert the default execution plan into a form that can be stored or transmitted, and then a second hash value can be generated for the serialized default execution plan. The hash value is obtained by operating a hash function. The hash function can convert an input value of any length into an output value of a fixed length. The hash function has collision resistance. The input value of the hash function is different, and its output value is also different. Therefore, when the first hash value is different from the second hash value, it can be determined that the first general execution plan is different from the default execution plan.

[0058] According to an embodiment of the present application, by setting a general execution plan with a better execution cost as a default execution plan, and then querying the database through the updated default execution plan, data query has higher efficiency.

[0059] In one embodiment, the database query method 200 further includes: calibrating the execution cost factor used to calculate the execution cost according to the central processing unit performance and input / output performance of the hardware environment.

[0060] The hardware environment of the database server is very different. In order to use more accurate real execution costs to estimate the execution cost of the execution calculation, it is necessary to calibrate the default execution cost factor of the database to obtain a more accurate execution cost factor. The default execution cost factors include: the cost of sequential read and write pages is 1, the cost of random read and write pages is 4, the cost of processing a tuple is 0.01, the cost of processing a tuple is 0.005, etc.

[0061] Corresponding to the application scenario and method of the method provided in the embodiment of the present application, the embodiment of the present application further provides a database query device, which is deployed on the database server. The following will describe the database query device 600 of the present application with reference to 6. Figure 6 1 is a block diagram showing a database query device according to an embodiment of the present application. Figure 6 As shown, the database query device 600 may include: an execution path generation unit 601, an execution plan generation unit 602 and a query unit 603.

[0062] The execution path generation unit 601 is configured to obtain a parameterized query statement and generate corresponding multiple execution paths based on the parameterized query statement. The parameterized query statement is a query statement whose filtering condition includes a parameter, and the filtering condition of each execution path in the multiple execution paths includes the parameter.

[0063] The execution path generation unit 601 may be a component in the database server, and the database server generates multiple execution paths corresponding to parameterized query statements through the component.

[0064] The execution plan generation unit 602 is configured to respectively configure at least one parameter value to the parameters of each execution path, obtain the execution cost of each execution path, and select the execution path with the smallest execution cost from the multiple execution paths as the first general execution plan.

[0065] The execution plan generating unit 602 may be a component in the database server, and the database server selects the execution path with the minimum execution cost as the first general execution plan through the component.

[0066] The query unit 603 is configured to compare the execution cost of the first general execution plan with the execution cost of the default execution plan of the parameterized query statement, set the default execution plan according to the comparison result, and execute the parameterized query statement based on the default execution plan to query the database.

[0067] The query unit 603 may be a component in the database server, and the database server queries the database through the component.

[0068] In one embodiment, the execution plan generation unit 602 is further configured to obtain multiple parameter values ​​with time tags, and filter at least one parameter value that meets the time filtering condition from the multiple parameter values ​​according to the time tag; and configure the at least one parameter value that meets the time filtering condition to the parameters of each execution path respectively.

[0069] In one embodiment, the execution plan generation unit 602 is further configured to configure the at least one parameter value into the parameters of the first-level sub-execution path respectively, and obtain the corresponding execution cost and output number of rows; input the execution cost and output number of rows into the next-level sub-execution path, and obtain the execution cost and output number of rows of the next-level sub-execution path; repeat the step of inputting the execution cost and output number of rows into the next-level sub-execution path, and obtaining the execution cost and output number of rows of the next-level sub-execution path, until the next-level sub-execution path is the last-level sub-execution path.

[0070] In one embodiment, the query unit 603 is further configured to determine whether the first general execution plan is different from the default execution plan when the execution cost of the first general execution plan is less than the execution cost of the default execution plan, and if so, set the first general execution plan as the default execution plan.

[0071] In one embodiment, the execution plan generation unit 602 is further configured to filter the parameter values ​​to be sampled that meet the time filtering condition from the multiple parameter values ​​according to the time mark; sample the parameter values ​​to be sampled to obtain at least one parameter value that meets the time filtering condition.

[0072] In one embodiment, the execution plan generation unit 602 is further configured to calculate the difference between the current time and the preset duration; and filter the parameter values ​​to be sampled whose time stamps are after the difference from the multiple parameter values.

[0073] In one embodiment, the database query device 600 further includes a default execution plan setting unit. The default execution plan setting unit is configured to generate at least one second customized execution plan by configuring at least one input parameter value to the parameters of the parameterized query statement respectively; generate a second general execution plan by configuring a NULL value to the parameters of the parameterized query statement; and set the second general execution plan as the default execution plan when the execution cost of the second general execution plan is less than the average execution cost of the at least one second customized execution plan.

[0074] In one embodiment, the query unit 603 is further configured to serialize the first general execution plan and generate a corresponding first hash value; serialize the default execution plan and generate a corresponding second hash value; and determine whether the first hash value is different from the second hash value.

[0075] In one embodiment, the database query device 600 further includes an execution cost factor calibration unit. The execution cost factor calibration unit is configured to calibrate the execution cost factor used to calculate the execution cost according to the CPU performance and input / output performance of the hardware environment.

[0076] According to the embodiment of the present application, by configuring the parameter value that meets the time filtering condition into the execution path, the execution cost of each execution path can be calculated according to the real user input information and the real-time data distribution in the database, and the execution path with the lowest execution cost is used as the general execution plan, and the accurate execution cost of the general execution plan can be obtained, and the general execution plan is more in line with the user's query habits. By comparing the execution cost of the general execution plan with the execution cost of the default execution plan of the parameterized query statement to set the default execution plan, the parameterized query statement can be executed based on the execution plan with a better cost, thereby improving the query efficiency of the database.

[0077] According to an embodiment of the present application, when the second general execution plan is less than the average execution cost of at least one customized execution plan, the second general execution plan is set as the default execution plan, and then the parameterized query statement can be executed based on the default execution plan. There is no need to generate a customized execution plan for each parameter value, thereby improving the query efficiency of the database.

[0078] According to the embodiment of the present application, the parameter values ​​recently input by the user are used as parameter values ​​that meet the time filtering conditions, so that a general execution plan that is more in line with the user's query habits can be generated, thereby improving the execution efficiency of the user's query.

[0079] According to the embodiment of the present application, the parameter values ​​recently input by the user are used as parameter values ​​that meet the time filtering conditions, so that a general execution plan that is more in line with the user's query habits can be generated, thereby improving the execution efficiency of the database.

[0080] Figure 7 FIG. 1 is a block diagram of an electronic device used to implement an embodiment of the present application. Figure 7 As shown, the electronic device includes: a memory 701 and a processor 702. The memory 701 stores a computer program that can be run on the processor 702. When the processor 702 executes the computer program, the method in the above embodiment is implemented. The number of the memory 701 and the processor 702 can be one or more.

[0081] The electronic device also includes:

[0082] The communication interface 703 is used to communicate with external devices and perform data exchange transmission.

[0083] If the memory 701, the processor 702 and the communication interface 703 are implemented independently, the memory 701, the processor 702 and the communication interface 703 can be connected to each other through a bus and communicate with each other. The bus can be an Industry Standard Architecture (ISA) bus, a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 7 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0084] Optionally, in a specific implementation, if the memory 701, the processor 702 and the communication interface 703 are integrated on a chip, the memory 701, the processor 702 and the communication interface 703 can communicate with each other through an internal interface.

[0085] An embodiment of the present application provides a computer-readable storage medium storing a computer program, which implements the method provided in the embodiment of the present application when the program is executed by a processor.

[0086] An embodiment of the present application also provides a chip, which includes a processor for calling and executing instructions stored in the memory from the memory, so that a communication device equipped with the chip executes the method provided by the embodiment of the present application.

[0087] An embodiment of the present application also provides a chip, including: an input interface, an output interface, a processor and a memory, wherein the input interface, the output interface, the processor and the memory are connected via an internal connection path, and the processor is used to execute the code in the memory. When the code is executed, the processor is used to execute the method provided in the embodiment of the application.

[0088] It should be understood that the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor may be a microprocessor or any conventional processor, etc. It is worth noting that the processor may be a processor supporting the Advanced RISC Machines (ARM) architecture.

[0089] Further, optionally, the above-mentioned memory may include a read-only memory and a random access memory. The memory may be a volatile memory or a non-volatile memory, or may include both volatile and non-volatile memory. Among them, the non-volatile memory may include a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), or a flash memory. The volatile memory may include a random access memory (RAM), which is used as an external cache. By way of exemplary but not limiting description, many forms of RAM are available. For example, static random access memory (SRAM), dynamic random access memory (DRAM), synchronous dynamic random access memory (SDRAM), double data rate synchronous dynamic random access memory (DDR SDRAM), enhanced synchronous dynamic random access memory (ESDRAM), synchronous link dynamic random access memory (SLDRAM) and direct memory bus random access memory (DR RAM).

[0090] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function according to the present application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium.

[0091] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine different embodiments or examples described in this specification and the features of different embodiments or examples, unless they are contradictory.

[0092] In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include at least one of the features. In the description of this application, the meaning of "plurality" is two or more, unless otherwise clearly and specifically defined.

[0093] Any process or method described in the flow chart or otherwise described herein can be understood as a module, fragment or portion of a code representing one or more executable instructions for implementing the steps of a specific logical function or process. And the scope of the preferred embodiment of the present application includes other implementations, in which the functions may not be performed in the order shown or discussed, including in a substantially simultaneous manner or in a reverse order according to the functions involved.

[0094] The logic and / or steps described in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, which can be specifically implemented in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute instructions), or used in combination with these instruction execution systems, devices or apparatuses.

[0095] It should be understood that the various parts of the present application can be implemented with hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented with software or firmware stored in a memory and executed by a suitable instruction execution system. All or part of the steps of the above embodiment method can be completed by instructing the relevant hardware through a program, which can be stored in a computer-readable storage medium, and when the program is executed, it includes one of the steps of the method embodiment or a combination thereof.

[0096] In addition, each functional unit in each embodiment of the present application can be integrated into a processing module, or each unit can exist physically separately, or two or more units can be integrated into one module. The above-mentioned integrated module can be implemented in the form of hardware or in the form of a software functional module. If the above-mentioned integrated module is implemented in the form of a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. The storage medium can be a read-only memory, a disk or an optical disk, etc.

[0097] The above is only an exemplary embodiment of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the technical field can easily think of various changes or substitutions within the technical scope recorded in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.

Claims

1. A database query method, comprising: Obtaining a parameterized query statement, and generating corresponding multiple execution paths based on the parameterized query statement, wherein the parameterized query statement is a query statement whose filtering condition includes a parameter, and the filtering condition of each execution path in the multiple execution paths includes the parameter; configuring at least one parameter value to the parameters of each execution path respectively, obtaining the execution cost of each execution path, and selecting the execution path with the smallest execution cost from the multiple execution paths as the first general execution plan; The execution cost of the first general execution plan is compared with the execution cost of the default execution plan of the parameterized query statement, the default execution plan is set according to the comparison result, and the parameterized query statement is executed based on the default execution plan to query the database.

2. The method according to claim 1, wherein: The configuring at least one parameter value to the parameters of each execution path respectively includes: Acquire multiple parameter values ​​with time stamps, and filter at least one parameter value that meets a time filtering condition from the multiple parameter values ​​according to the time stamp; At least one parameter value satisfying the time filtering condition is respectively configured into the parameters of each execution path.

3. The method according to claim 1, wherein: Each execution path includes multiple layers of sub-execution paths, and configuring at least one parameter value to the parameters of each execution path includes: Configure the at least one parameter value to the parameters of the first-layer sub-execution path respectively, and obtain the corresponding execution cost and output row number; Input the execution cost and the number of output rows into the next layer of sub-execution path, and obtain the execution cost and the number of output rows of the next layer of sub-execution path; Repeat the steps of inputting the execution cost and the number of output rows into the next layer of sub-execution path and obtaining the execution cost and the number of output rows of the next layer of sub-execution path until the next layer of sub-execution path is the last layer of sub-execution path.

4. The method according to claim 1 or 2, wherein: The step of setting a default execution plan according to the comparison result includes: In the case that the execution cost of the first general execution plan is less than the execution cost of the default execution plan, it is determined whether the first general execution plan is different from the default execution plan, and if so, the first general execution plan is set as the default execution plan.

5. The method according to claim 2, wherein: The filtering at least one parameter value satisfying a time filtering condition from the multiple parameter values ​​according to the time tag comprises: Filtering the parameter values ​​to be sampled that meet the time filtering condition from the multiple parameter values ​​according to the time mark; The parameter value to be sampled is sampled to obtain at least one parameter value that meets a time filtering condition.

6. The method according to claim 5, wherein: The filtering, according to the time tag, the parameter values ​​to be sampled that meet the time filtering condition from the multiple parameter values ​​includes: Calculate the difference between the current time and the preset duration; The parameter values ​​to be sampled whose time stamps are after the difference value are filtered from the multiple parameter values.

7. The method according to claim 1 or 2, wherein: Before generating corresponding multiple execution paths based on the parameterized query statement, the method further includes: Generating at least one customized execution plan by respectively configuring at least one input parameter value into the parameters of the parameterized query statement; Generate a second general execution plan by configuring a NULL value into the parameter of the parameterized query statement; When the execution cost of the second general execution plan is less than the average execution cost of the at least one customized execution plan, the second general execution plan is set as the default execution plan.

8. The method according to claim 4, wherein: The determining whether the first general execution plan is different from the default execution plan includes: Serializing the first general execution plan and generating a corresponding first hash value; Serializing the default execution plan and generating a corresponding second hash value; A determination is made as to whether the first hash value is different from the second hash value.

9. The method according to claim 1 or 2, further comprising: The execution cost factor used to calculate the execution cost is calibrated according to the CPU performance and I / O performance of the hardware environment.

10. A database query device, comprising: an execution path generation unit configured to obtain a parameterized query statement and generate a corresponding plurality of execution paths based on the parameterized query statement, wherein the parameterized query statement is a query statement whose filtering condition includes a parameter, and the filtering condition of each execution path in the plurality of execution paths includes the parameter; an execution plan generating unit, configured to respectively configure at least one parameter value to the parameters of each execution path, obtain an execution cost of each execution path, and select an execution path with the smallest execution cost from the multiple execution paths as a first general execution plan; A query unit is configured to compare the execution cost of the first general execution plan with the execution cost of the default execution plan of the parameterized query statement, set the default execution plan according to the comparison result, and execute the parameterized query statement based on the default execution plan to query the database.

11. An electronic device comprising a memory, a processor and a computer program stored in the memory, wherein the processor implements the method according to any one of claims 1 to 9 when executing the computer program.

12. A computer-readable storage medium, wherein a computer program is stored in the computer-readable storage medium, and when the computer program is executed by a processor, the method according to any one of claims 1 to 9 is implemented.

Images