Database query optimization method and device, equipment and medium

Abstract

The invention relates to the technical field of data query, in particular to a database query optimization method and device, equipment and a medium. The method comprises the following steps: acquiring a query plan corresponding to a query statement, applying available resources for executing the query plan by the query statement in the process of executing the query plan, if the application fails, acquiring N execution paths corresponding to continuing to execute the query plan, N being an integer greater than zero, evaluating the execution cost of the query plan under each execution path, the execution cost of each execution path is obtained, the execution path corresponding to the minimum execution cost value is selected as the optimal execution path, and the query plan is executed according to the optimal execution path. According to the method and the device, when resources are insufficient, the execution cost of the query statement under different execution paths is evaluated, the execution path with the minimum execution cost is determined as the optimal path, the query statement is executed by using the optimal path, and the execution efficiency is improved.

Description

Technical Field

[0001] The present invention relates to the field of data query technology, and in particular to a database query optimization method, device, equipment and medium. Background Art

[0002] In a database management system, the execution path is mainly selected based on the cost in the optimization phase, and the execution is executed according to the execution path specified in the plan in the execution phase. However, in the actual operation process, the resources used by the database have a great impact on the execution of query statements. The execution time varies greatly in scenarios with sufficient resources and insufficient resources. The resources here include CPU, memory, IO and other resources. The amount of resources a query statement obtains from the system depends not only on the system resources, but also on how many other query statements compete with it for resources. When the database management system is short of resources, the execution efficiency of different execution paths is very different. Therefore, when the database management system is short of resources, how to improve the execution efficiency of query statements becomes an urgent problem to be solved. Summary of the invention

[0003] In view of this, an embodiment of the present invention provides a method, apparatus, device and medium for optimizing database query to solve the problem of low execution efficiency of query statements when the database management system has insufficient resources.

[0004] In a first aspect, an embodiment of the present invention provides a method for optimizing database query, the optimization method comprising: Get the query plan corresponding to the query statement; During the execution of the query plan, the query statement applies for available resources for executing the query plan. If the application fails, N execution paths corresponding to continuing to execute the query plan are obtained, where N is an integer greater than zero. Evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path; An execution path corresponding to a minimum execution cost value is selected as an optimal execution path, and the query plan is executed according to the optimal execution path.

[0005] In a second aspect, an embodiment of the present invention provides a database query optimization device, the optimization device comprising: A first acquisition module is used to acquire a query plan corresponding to a query statement; A second acquisition module is used for, during the process of executing the query plan, the query statement applies for available resources for executing the query plan, and if the application fails, obtains N execution paths corresponding to continuing to execute the query plan, where N is an integer greater than zero; An evaluation module, used to evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path; The first execution module is used to select an execution path corresponding to a minimum execution cost as an optimal execution path, and execute the query plan according to the optimal execution path.

[0006] In a third aspect, an embodiment of the present invention provides a computer device, comprising a processor, a memory, and a computer program stored in the memory and executable on the processor, wherein the processor implements the optimization method described in the first aspect when executing the computer program.

[0007] In a fourth aspect, an embodiment of the present invention 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 optimization method as described in the first aspect is implemented.

[0008] Compared with the prior art, the present invention has the following beneficial effects: Obtain the query plan corresponding to the query statement. During the execution of the query plan, the query statement applies for available resources for executing the query plan. If the application fails, obtain N execution paths corresponding to the continued execution of the query plan, where N is an integer greater than zero. Evaluate the execution cost of the query plan under each execution path, obtain the execution cost under each execution path, select the execution path corresponding to the minimum execution cost as the optimal execution path, and execute the query plan according to the optimal execution path. In this application, when resources are insufficient, evaluate the execution costs of the query statement under different execution paths, determine the execution path with the minimum execution cost as the optimal path, and use the optimal path to execute the query statement, thereby improving execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0009] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments of the present invention will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.

[0010] Figure 1 It is a flowchart of a database query optimization method provided by the first embodiment of the present invention; Figure 2 It is a flowchart of a database query optimization method provided by Embodiment 2 of the present invention; Figure 3 It is a flowchart of a database query optimization method provided by Embodiment 3 of the present invention; Figure 4It is a flowchart of a database query optimization method provided by Embodiment 4 of the present invention; Figure 5 It is a structural block diagram of a database query optimization device provided by Embodiment 5 of the present invention; Figure 6 It is a structural diagram of a computer device provided in Embodiment 6 of the present invention. DETAILED DESCRIPTION

[0011] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0012] In the following description, specific details such as specific system structures, technologies, etc. are provided for the purpose of illustration rather than limitation, so as to provide a thorough understanding of the embodiments of the present invention. However, it should be clear to those skilled in the art that the present invention may be implemented in other embodiments without these specific details. In other cases, detailed descriptions of well-known systems, systems, circuits, and methods are omitted to prevent unnecessary details from obstructing the description of the present invention.

[0013] It should be understood that when used in the present specification and the appended claims, the term "comprising" indicates the presence of described features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.

[0014] It should also be understood that the term "and / or" used in the present description and the appended claims refers to and includes any and all possible combinations of one or more of the associated listed items.

[0015] As used in the present specification and the appended claims, the term "if" may be interpreted as "when" or "upon" or "in response to determining" or "in response to detecting", depending on the context. Similarly, the phrase "if it is determined" or "if [described condition or event] is detected" may be interpreted as meaning "upon determination" or "in response to determining" or "upon detection of [described condition or event]" or "in response to detecting [described condition or event]", depending on the context.

[0016] In addition, in the description of the present specification and the appended claims, the terms "first", "second", "third", etc. are only used to distinguish the descriptions and cannot be understood as indicating or implying relative importance.

[0017] References to "one embodiment" or "some embodiments" etc. described in the present specification mean that one or more embodiments of the present invention include specific features, structures or characteristics described in conjunction with the embodiment. Therefore, the statements "in one embodiment", "in some embodiments", "in some other embodiments", "in some other embodiments", etc. that appear in different places in this specification do not necessarily refer to the same embodiment, but mean "one or more but not all embodiments", unless otherwise specifically emphasized in other ways. The terms "including", "comprising", "having" and their variations all mean "including but not limited to", unless otherwise specifically emphasized in other ways.

[0018] It should be understood that the order of execution of the steps in the following embodiments does not imply a precedence of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0019] In order to illustrate the technical solution of the present invention, specific embodiments are provided below for illustration.

[0020] See also Figure 1 , is a flow chart of a method for optimizing database query provided by Embodiment 1 of the present invention, such as Figure 1 As shown, the database query optimization method may include the following steps.

[0021] S101: Obtain a query plan corresponding to a query statement.

[0022] In step S101, the query statement is an execution statement for querying a database, the query plan is a plan tree generated by parsing the query statement, and the query sequence corresponding to the plan tree is the corresponding query plan.

[0023] In this embodiment, a query statement is obtained, wherein the query statement is a statement that can access database data, the query statement is parsed, and a corresponding query plan is generated, wherein the query plan is an execution order for executing the query statement.

[0024] For example, the query statement may be a SQL (Structured Query Language) statement. The SQL statement execution will be parsed, rewritten, and optimized to generate an execution plan tree, i.e., a query plan, and then the execution plan tree. The plan tree is composed of multiple operators (such as a connection operator, a grouping operator, and a filtering operator, etc.). It may also be other query statements, which are not limited in this embodiment.

[0025] It should be noted that the query statement may be a single query statement or multiple query statements. The query statement may also be other query statements, which is not limited in this embodiment.

[0026] S102: During the execution of the query plan, the query statement applies for available resources for executing the query plan. If the application fails, N execution paths corresponding to continuing to execute the query plan are obtained, where N is an integer greater than zero.

[0027] In step S102, during the execution of the query plan, the query statement applies to the database system for available resources to store the queried data results, where the available resources may include CPU, memory, IO and other resources. If the application fails, it is considered that the database system has insufficient resources and cannot continue to execute the query plan, then it is necessary to continue to execute the query plan through other execution paths, where different execution paths have different execution costs.

[0028] In this embodiment, during the execution of the query plan, the query statement continuously applies to the database system for available resources in order to store the query results. When the available resources applied for are greater than the remaining resources of the database system, the application fails, and it is necessary to continue to execute the query plan through other execution paths.

[0029] Among them, the first execution path is: the query plan continues to execute. When the database system resources are insufficient, the query plan continues to execute and the memory overflow occurs. The intermediate results of the query need to be written to the disk, which increases the memory overflow cost. The second execution path is: when there are multiple algorithms for the query operators in the query plan, and the efficiency of the algorithm is related to resources, the query operators in the query plan are switched to query operators in other algorithms, so that the resources occupied by the switched query operators are less than the resources occupied by the query operators in the query plan when they are executed, so as to reduce the pressure on the database system resources. The third execution path is: the query statement enters the waiting state and waits for the database system to release new resources before executing.

[0030] It should be noted that when there are multiple query statements, that is, when multiple query statements execute the query plan at the same time, when the query resources corresponding to other query statements are releasable resources, that is, when executing the query plan, when the resources occupied by other query statements can be released, other query statements release the corresponding resources, that is, the query statements enter the waiting state, waiting for other query statements to release the corresponding resources before execution. For example, when query statements A and B are executed at the same time, the system can use 1000MB of resources, query statement A needs to use 500MB during execution, query statement B needs to use 1000MB during execution, query statement A and query statement B are executed at the same time, query statement A consumes 300MB during execution, and query statement B consumes 700MB during execution. If query statement B enters the waiting state according to the path selection and is marked as a releasable resource, part of the resources consumed during the execution of query statement B will be released first, and execution will continue. When insufficient memory resources are encountered, if the system has available memory application, it will directly apply, otherwise it will continue to release the memory consumed during the execution of query statement B until query statement B is executed.

[0031] In this embodiment, during the process of executing the query plan, the query statement applies for available resources for executing the query plan. If the application fails, N execution paths corresponding to the continued execution of the query plan are obtained, so as to select a path with a lower execution cost from the N execution paths, and use the path with a lower execution cost to execute the query plan corresponding to the corresponding query statement, thereby improving the execution efficiency of the query statement.

[0032] S103: Evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path.

[0033] In step S103, the execution cost of the query plan under each execution path is evaluated, wherein the execution cost is the time consumed during the execution of the query statement.

[0034] In this embodiment, the execution cost of the query plan under each execution path is evaluated to obtain the execution cost under each execution path, wherein the calculation process of the execution cost under each execution path is different.

[0035] In this embodiment, the execution cost under each execution path is calculated to obtain the execution cost under each execution path, which is used as a basis for selecting the optimal execution path.

[0036] See also Figure 2 , is a flow chart of a method for optimizing database query provided by Embodiment 2 of the present invention. Figure 2 As shown, in step S103, the execution cost of the query plan under each execution path is evaluated to obtain the execution cost under each execution path, including: S201: when the execution path is the first execution path, obtaining remaining resources required for remaining data of the query plan; S202: Evaluate the execution cost of the query plan under the first execution path according to the required remaining resources to obtain the execution cost under the first execution path.

[0037] In this embodiment, the N execution paths include the first execution path, wherein the first execution path is to continue to execute according to the query plan, the remaining resources of the database system are insufficient, the memory overflows, and the intermediate results are written to the disk, that is, the resources lacking in the database system are written to the disk, and the execution cost increases the corresponding memory overflow cost, and the memory overflow cost is the sum of the time consumed to write the intermediate results to the disk and the time to read the intermediate results later. According to the required remaining resources, the first memory overflow cost is calculated, and the first memory overflow cost is the sum of the time consumed to write the intermediate results to the disk when querying the remaining data and the time to read the intermediate results later. Therefore, the execution cost under the first execution path includes the sum of the execution cost when querying the remaining data according to the query plan and the first memory overflow cost.

[0038] When the execution path is the first execution path, the remaining resources required for the remaining data of the query plan are obtained, that is, the resources that need to be applied for during the execution of the query plan. The remaining resources required for the remaining data of the query plan can be obtained by subtracting the resources already consumed by the execution of the query plan from the total required resources required to execute the query plan. According to the required remaining resources, the execution cost of the query plan under the first execution path is evaluated to obtain the execution cost under the first execution path.

[0039] For example, the available resources of the system are 1000MB, the total required resources for query statement A to execute the query plan are 2000MB, and the resources consumed by query statement A to execute the query plan are 1000MB. Then the remaining resources required to query the remaining data are 1000MB. After the available resources of the system are consumed, query statement A applies for 1000MB of resources from the system. The system has no available resources and the application fails. Then, the 1000MB intermediate result needs to be written to the disk. The execution cost increases the corresponding memory overflow cost, that is, the time to write the 1000MB intermediate result to the disk and the time to read the 1000MB intermediate result. Then the execution cost of the first execution path is the sum of the execution cost of querying the 1000MB remaining data according to the query plan of query statement A, the time to write the 1000MB intermediate result to the disk, and the time to read the 1000MB intermediate result.

[0040] See also Figure 3 , is a flow chart of a method for optimizing database query provided by Embodiment 3 of the present invention. Figure 3As shown, in step S103, the execution cost of the query plan under each execution path is evaluated to obtain the execution cost under each execution path, and further includes: S301: When the execution path is the second execution path, the query operator in the query plan is switched to a query operator that occupies less resources during execution; S302: Evaluate the execution cost of the query plan under the second execution path according to the query operator that occupies less resources during execution, and obtain the execution cost under the second execution path.

[0041] In this embodiment, the N execution paths also include a second execution path, and the second execution path is to switch the query operator for execution, that is, to switch the query operator in the query plan to a query operator that occupies less resources during execution, so as to reduce the available resources for the query statement to apply for the execution of the query plan, and evaluate the execution cost of the query plan under the second execution path according to the query operator that occupies less resources during execution, and obtain the execution cost under the second execution path. When evaluating the execution cost of the query plan under the second execution path, it is necessary to evaluate the cost of executing the query plan using the switched query operator.

[0042] For example, the available resources of the system are 500MB, the total required resources for query statement A to execute the query plan are 600MB, the resources consumed by query statement A to execute the query plan are 300MB, the remaining resources of the system are 200MB, and the remaining resources required for the remaining data of the query plan are 300MB. Query statement A applies for 300MB of resources from the system, but the remaining resources of the system are less than the remaining resources required for the remaining data of the query plan, so the application fails. The query operator in query statement A can be switched to other query operators that occupy less resources during execution, such as switching the hash grouping operator to the sort grouping operator, and switching the merge join operator to the hash join operator. When evaluating the execution cost, it is necessary to evaluate the cost of using the switched query operator to query the remaining data, that is, the cost of using the switched query operator to query the remaining data of 300MB of resources, and obtain the execution cost under the second execution path.

[0043] It should be noted that after the query operator in the query plan is switched to a query operator that occupies less resources during execution, the required resources of the query operator that occupies less resources are reacquired. If the available resources of the system are not less than the required resources of the query operator that occupies less resources, the switched query operator is used to execute the corresponding query plan. The execution cost under the second execution path is the execution cost of querying the remaining data according to the query plan after switching to the query operator that occupies less resources during execution.

[0044] If the available resources of the system are less than the required resources of the query operator with smaller resource usage, the execution continues. When the resources are insufficient, the memory overflows and the intermediate results are written to the disk, that is, the resources that the system lacks are written to the disk. The execution cost increases the corresponding memory overflow cost, that is, the second memory overflow cost. The second memory overflow cost is the sum of the time consumed by writing the intermediate results of the remaining data to the disk according to the query plan after switching to the query operator with smaller resource usage during execution and the time of subsequent reading of the intermediate results, that is, the execution cost under the second execution path includes the sum of the execution cost of querying the remaining data according to the query plan after switching to the query operator with smaller resource usage during execution and the second memory overflow cost. Among them, when calculating the second memory overflow cost, when the query plan is executed using the corresponding switched query operator, the second remaining resources required for the remaining data of the query plan are recalculated, that is, the difference between the required resources required to execute the query plan using the switched query operator and the available resources of the system is calculated, and the memory overflow cost is calculated based on the recalculated second remaining resources, that is, the second memory overflow cost. The second memory overflow cost is the sum of the time consumed by writing the intermediate results corresponding to the second remaining resources to the disk and the time of subsequent reading of the intermediate results.

[0045] See also Figure 4 , is a flow chart of a database query optimization method provided by Embodiment 4 of the present invention. Figure 4 As shown, in step S103, the execution cost of the query plan under each execution path is evaluated to obtain the execution cost under each execution path, and further includes: S401: When the execution path is the third execution path, obtaining preset resources required when the query plan is waiting to be executed; S402: Evaluate the execution cost of the query plan under the third execution path according to preset resources to obtain the execution cost under the third execution path.

[0046] In this embodiment, the N execution paths also include a third execution path, wherein the third execution path is a query statement that enters a waiting state, and waits for the system to release new resources before executing. That is, when the execution path is the third execution path, the preset resources required for the query plan to wait for execution are obtained; according to the preset resources, the execution cost of the query plan under the third execution path is evaluated to obtain the execution cost under the third execution path. Among them, the preset resources required for the query plan to wait for execution can be a pre-set fixed-size resource, such as 100MB of memory resources, that is, after the system releases 100MB of memory resources, the query plan continues to be executed. The size of the preset resources required when the query plan is waiting for execution can also be dynamically set, such as the preset resources required when the query plan is waiting for execution can be a part of the resources already used by the query plan, such as one-tenth or one-fifth of the resources already used by the query plan, etc. When the system releases the corresponding preset resources, the query plan continues to execute without waiting for the system to release all the resources required for the query plan, thereby reducing the waiting time of the query plan.

[0047] According to the preset resources, the execution cost of the query plan under the third execution path is evaluated to obtain the execution cost under the third execution path. Wherein, when calculating the execution cost under the third execution path, the waiting time required for the system to release the preset resources is calculated according to the preset resources and the speed at which the system releases the memory resources, and it is determined whether the system can release the third remaining resources required by the query plan within the time when the preset resources are consumed, wherein the third remaining resources required by the query plan are the remaining resources required to execute the remaining data of the query plan, minus the difference of the preset resources. If within the time when the preset resources are consumed, according to the speed at which the system releases the memory resources, the system can release the third remaining resources required by the query plan, then the execution cost under the third execution path is the sum of the execution cost of querying the remaining data according to the query plan and the waiting time required for the system to release the preset resources. That is, after the corresponding waiting time, the execution continues according to the corresponding query plan. It should be noted that the time when the query plan consumes the preset resources can be calculated according to the execution speed of the query plan, and the execution speed is the resources that can be consumed per unit time.

[0048] If the query plan cannot release the third remaining resource required by the query plan within the time when the preset resources are consumed, according to the speed at which the system releases memory resources, and if there are final fourth remaining resources that have not been released, it is necessary to calculate the memory overflow cost corresponding to the final fourth remaining resource to obtain the fourth memory overflow cost. Among them, the fourth remaining resource is the remaining resource required to execute the remaining data of the query plan, minus the preset resources, and then minus the memory resources released by the system within the time when the preset resources are consumed. The fourth memory overflow cost is the sum of the time consumed to write the intermediate result of querying the fourth remaining resource to the disk according to the query plan and the time to subsequently read the intermediate result. Therefore, the execution cost under the third execution path includes the sum of the execution cost when querying the remaining data according to the query plan, the waiting time required for the system to release the preset resources, and the fourth memory overflow cost.

[0049] For example, the system has 50MB of available resources, and query statement A requires 100MB during execution. Query statement A consumes 50MB of execution, so the remaining resources required to query the remaining data are 50MB. The system resources are insufficient, and the query statement applies for 50MB of available resources from the system. The application fails, and the query plan needs to wait for the system to release the corresponding resources before continuing to execute. Get the preset resources required when the query plan is waiting for execution, where the preset resources required when the query plan is waiting for execution can be a fixed-size resource, such as 50 MB. If the system releases memory resources at a speed of 10MB / s, the waiting time required for the system to release 50 MB is 5s. After the system releases 50MB, the execution cost under the third execution path is the sum of the execution cost of querying the remaining 50MB of data according to the query plan of query statement A and the waiting time.

[0050] If the available resources of the system are 300MB, 500MB is required during the execution of query statement A, and 300MB is consumed during the execution of query statement A, then the remaining resources required for querying the remaining data are 200MB. The system resources are insufficient, and the query statement applies for 200MB of available resources from the system. The application fails, and the query plan needs to be continued after the system releases the corresponding resources. Get the preset resources required when the query plan is waiting for execution, where the preset resources required when the query plan is waiting for execution are one tenth of the resources consumed by the query statement, that is, the preset resources are 30MB. If the system releases memory resources at a speed of 10MB / s, the waiting time required for the system to release 30 MB is 3s. According to the execution speed of the query plan, calculate the time required for the query plan to consume 30 MB, and determine whether the system can completely release the third remaining resource within the time required for the query plan to consume 30 MB. The third remaining resource is the difference between the remaining resources required to execute the remaining data of the query plan and the preset resources, that is, 170MB. If the system can completely release the third remaining resource, i.e., 170MB, within the time required for the query plan to consume 30MB, then the execution cost under the third execution path is the sum of the execution cost of querying 200MB of remaining data according to the query plan of query statement A and the waiting time required for the system to release the preset resources, where the waiting time is 3s. If the system cannot completely release the third remaining resource within the time required for the query plan to consume 30 MB, then there is a final fourth remaining resource that has not been released, and it is necessary to calculate the memory overflow cost corresponding to the final fourth remaining resource, that is, the fourth memory overflow cost. Among them, the fourth remaining resource is the difference between the remaining resources required to execute the remaining data of the query plan minus the preset resources and the resources released by the system within the time required to consume 30 MB. For example, within the time required for the query plan to consume 30 MB, the system releases 100MB of resources, then the fourth remaining resource is the difference between the remaining resources 200MB minus the preset resources 30 MB and the resources released by the system within the time required to consume 30 MB, that is, 70MB. The sum of the time to write the intermediate results of 70MB resources to the disk and the subsequent reading of the intermediate results is calculated, that is, the fourth memory overflow cost. The execution cost under the third execution path is the sum of the execution cost of querying 200MB of remaining data according to the query plan of query statement A, the waiting time 3s required for the system to release the preset resources, and the fourth memory overflow cost.

[0051] In this embodiment, when the execution path is the third execution path, the preset resources required when the query plan is waiting for execution are obtained; according to the preset resources, the execution cost of the query plan under the third execution path is evaluated to obtain the execution cost under the third execution path. That is, it is only necessary to increase the cost corresponding to the time of waiting for the system to release the preset resources, or increase the cost corresponding to the time of waiting for the system to release the preset resources and the memory overflow cost of some resources. During the execution of the query plan, the system releases resources at the same time, so that after the execution plan consumes the preset resources, it can continue to execute according to the resources released by the system, thereby reducing the execution cost and improving the execution efficiency.

[0052] It should be noted that when there are multiple query statements, that is, when multiple query statements execute the query plan at the same time, when the query resources corresponding to other query statements are releasable resources, that is, when executing the query plan, when the resources occupied by other query statements can be released, other query statements release the corresponding resources, that is, the query statements enter the waiting state, waiting for other query statements to release the corresponding resources before executing. Instead of releasing the corresponding resources through the system, the corresponding resources are released through the query statements that can be released, that is, the resources occupied during the execution of the query statements are released.

[0053] For example, query statement SQL1 is select a, sum(b) from test1 group by a, query statement SQL2 is select b, sum(c) from test2 group by b, and query statement SQL3 is select c, sum(d)from test3 group by c. Assume that the algorithms selected by query statements SQL1 and SQL2 are both hash group, and query statement SQL3 is being executed. It requires 500 MB of resources and the system can use 1000 MB of resources. The execution process of query statement SQL1 consumes a total of 500 MB of resources and query statement SQL2 consumes a total of 1000 MB of resources.

[0054] When query statements SQL1 and SQL2 are executed at the same time, suppose query statement SQL1 is executed until it consumes 200MB of resources, and the 200MB of resources consumed by query statement SQL1 can be released, and query statement SQL2 is executed until it consumes 300MB of resources. If it is executed again at this time, the system memory will overflow.

[0055] If the resources consumed by the query statement SQL2 are marked as releasable resources, the execution path for the query statement SQL1 may include a first execution path, a second execution path, a third execution path and a fourth execution path, wherein the execution costs of the first execution path, the second execution path and the third execution path are calculated by the above method and are not described in this embodiment.

[0056] The fourth execution path is a path where the query statement SQL1 waits for the releasable resources to release the corresponding resources. The calculation of the corresponding execution cost is equal to the calculation of the execution cost of the third execution path, that is, the query statement SQL1 enters the waiting state, waits for the releasable resources to release the corresponding waiting resources, and then executes.

[0057] The waiting resources required when the query statement SQL1 is waiting for execution are obtained, and the execution cost of the query statement SQL1 under the fourth execution path is evaluated according to the waiting resources to obtain the execution cost under the fourth execution path. Among them, the waiting resources required when the query statement SQL1 is waiting for execution can be a pre-set fixed-size resource, such as a 100MB memory resource, that is, after the releasable resources release the 100MB memory resource, the query statement SQL1 continues to be executed. The size of the waiting resources required when the query statement SQL1 is waiting for execution can also be dynamically set, such as the waiting resources required when the query statement SQL1 is waiting for execution can be one-tenth or one-fifth of the required resources of the query statement SQL1. For example, if the required resources of the query statement SQL1 are 300MB, the waiting resources are one-tenth of the required resources of the query statement SQL1, that is, 30MB. When the releasable resources release the corresponding waiting resources, that is, 30MB, the query statement SQL1 continues to be executed. It should be noted that if the resources consumed by the query statement SQL3 are marked as releasable resources, the resources consumed by the query statement SQL3 can also be released, and one of the corresponding resources marked as releasable resources can be selected for release.

[0058] According to the waiting resources, the execution cost of the query statement SQL1 under the fourth execution path is evaluated to obtain the execution cost under the fourth execution path. When calculating the execution cost under the fourth execution path, the waiting time required for the releasable resources to release the waiting resources is calculated according to the speed at which the waiting resources and the releasable resources release the memory resources, and it is determined whether the resources released by the releasable resources are greater than the third remaining resources required by the query statement SQL1 within the time when the waiting resources are consumed by the query statement SQL1, wherein the third remaining resources required by the query statement SQL1 are the difference between the total resources 500MB required by the query statement SQL1 minus the consumed 200MB and the waiting resources 30MB. If the resources released by the releasable resources are greater than the third remaining resources required by the query statement SQL1 within the time when the waiting resources are consumed by the query statement SQL1, then the execution cost under the fourth execution path is the sum of the execution cost of the query statement SQL1 querying the remaining 300MB of data according to the query plan and the waiting time required for the releasable resources to release the waiting resources.

[0059] If the resources released by the releasable resources in the time when the waiting resources are consumed by the query statement SQL1 are less than the third remaining resources required by the query statement SQL1, the fourth remaining resources required by the query statement SQL1 are calculated, wherein the fourth remaining resources required by the query statement SQL1 are the total resources 500MB required by the query statement SQL1 minus the consumed 200MB, minus the waiting resources, and then minus the memory resources released by the releasable resources in the time when the waiting resources are consumed by the query statement SQL1, to obtain the fourth remaining resources required by the query statement SQL1. It is necessary to calculate the memory overflow cost corresponding to the fourth remaining resource required by the query statement SQL1, that is, the fourth memory overflow cost, and the execution cost under the fourth execution path is the sum of the execution cost of the query statement SQL1 querying 300MB of remaining data according to the query plan, the waiting time required for the releasable resources to release the waiting resources, and the fourth memory overflow cost.

[0060] It should be noted that when query statement SQL1 continues to execute according to the query plan, if it needs to continue to execute after consuming the waiting resources, query statement SQL1 applies to the system for corresponding resources for execution. If the application is successful, the applied resources are used. If the application fails, the resources can be released and continue to be released until the query statement SQL2 is executed.

[0061] If the resources consumed by the query statement SQL1 are identified as releasable resources, the execution path for the query statement SQL2 may include a first execution path, a second execution path, a third execution path and a fourth execution path, wherein the calculation method of the execution cost of the first execution path, the second execution path, the third execution path and the fourth execution path is equal to the calculation method of the execution cost of the above-mentioned query statement SQL1, and will not be described again in this embodiment.

[0062] In this embodiment, adding the fourth execution path can release resources consumed by other query statements, thereby reducing the resource pressure of the system.

[0063] Optionally, after the query statement applies for available resources for executing the query plan, it also includes: If the application is successful, the query will continue based on the available resources.

[0064] In this embodiment, the query statement applies for available resources for executing the query plan. If the application is successful, execution continues based on the available resources, that is, according to the query plan, without using other execution paths for execution, that is, there is no need to calculate the execution costs of other execution paths.

[0065] S104: Selecting an execution path corresponding to a minimum execution cost as an optimal execution path, and executing the query plan according to the optimal execution path.

[0066] In step S104, the execution path corresponding to the minimum execution cost is selected as the optimal execution path, and the query plan is executed according to the optimal execution path, that is, the corresponding query plan is executed when the execution cost is minimum.

[0067] In this embodiment, the execution cost corresponding to each executable path is obtained respectively, and the execution path corresponding to the minimum execution cost value is selected as the optimal execution path. The query plan is executed according to the optimal execution path, thereby improving the execution efficiency.

[0068] Obtain the query plan corresponding to the query statement. During the execution of the query plan, the query statement applies for available resources for executing the query plan. If the application fails, obtain N execution paths corresponding to the continued execution of the query plan, where N is an integer greater than zero. Evaluate the execution cost of the query plan under each execution path, obtain the execution cost under each execution path, select the execution path corresponding to the minimum execution cost as the optimal execution path, and execute the query plan according to the optimal execution path. In this application, when resources are insufficient, evaluate the execution costs of the query statement under different execution paths, determine the execution path with the minimum execution cost as the optimal path, and use the optimal path to execute the query statement, thereby improving execution efficiency.

[0069] See also Figure 5 , Figure 5 1 is a structural block diagram of a database query optimization device provided by Embodiment 5 of the present invention. For the sake of convenience, only the part related to the embodiment of the present application is shown. Figure 5 The optimization device 50 includes a first acquisition module 51, a second acquisition module 52, an evaluation module 53, and a first execution module 54.

[0070] The first acquisition module 51 is used to acquire a query plan corresponding to a query statement.

[0071] The second acquisition module 52 is used for the query statement to apply for available resources for executing the query plan during the execution of the query plan. If the application fails, N execution paths corresponding to the continued execution of the query plan are obtained, where N is an integer greater than zero.

[0072] The evaluation module 53 is used to evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path.

[0073] The first execution module 54 is used to select an execution path corresponding to a minimum execution cost as an optimal execution path, and execute the query plan according to the optimal execution path.

[0074] Optionally, the optimization device 50 further includes: The second execution module is used to continue to execute the query based on available resources if the application is successful.

[0075] Optionally, the evaluation module 53 includes: The first acquisition unit is used to acquire remaining resources required for remaining data of the query plan when the execution path is the first execution path.

[0076] The first evaluation unit is used to evaluate the execution cost of the query plan under the first execution path according to the required remaining resources to obtain the execution cost under the first execution path.

[0077] Optionally, the evaluation module 53 further includes: The switching unit is used to switch the query operator in the query plan to a query operator that occupies less resources during execution when the execution path is the second execution path.

[0078] The second evaluation unit is used to evaluate the execution cost of the query plan under the second execution path according to the query operator that occupies less resources during execution, so as to obtain the execution cost under the second execution path.

[0079] Optionally, the evaluation module 53 further includes: The second acquisition unit is used to acquire preset resources required when the query plan is waiting to be executed when the execution path is the third execution path.

[0080] The third evaluation unit is used to evaluate the execution cost of the query plan under the third execution path according to preset resources to obtain the execution cost under the third execution path.

[0081] It should be noted that the information interaction, execution process and other contents between the above-mentioned modules are based on the same concept as the method embodiment of the present application. Their specific functions and technical effects can be found in the method embodiment part and will not be repeated here.

[0082] Figure 6 Schematic diagram of the structure of a computer device provided by Embodiment 6 of the present invention. Figure 6 As shown, the computer device of this embodiment includes: at least one processor ( Figure 6 Only one is shown in the figure), a memory, and a computer program stored in the memory and executable on at least one processor, wherein when the processor executes the computer program, the steps in any of the above-mentioned optimization method embodiments are implemented.

[0083] The computer device may include, but is not limited to, a processor and a memory. Those skilled in the art will appreciate that Figure 6 This is merely an example of a computer device and does not constitute a limitation on the computer device. The computer device may include more or fewer components than those shown in the figure, or a combination of certain components, or different components. For example, it may also include a network interface, a display screen, and an input device.

[0084] The processor may be a CPU, or other general-purpose processors, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (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.

[0085] The memory includes a readable storage medium, an internal memory, etc., wherein the internal memory may be the memory of a computer device, and the internal memory provides an environment for the operation of the operating system and computer-readable instructions in the readable storage medium. The readable storage medium may be a hard disk of a computer device, and in other embodiments, it may also be an external storage device of the computer device, for example, a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (Flash Card), etc. equipped on the computer device. Further, the memory may also include both an internal storage unit of the computer device and an external storage device. The memory is used to store an operating system, an application program, a boot loader (BootLoader), data, and other programs, such as the program code of a computer program, etc. The memory may also be used to temporarily store data that has been output or is to be output.

[0086] The technicians in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In practical applications, the above-mentioned function allocation can be completed by different functional units and modules as needed, that is, the internal structure of the device is divided into different functional units or modules to complete all or part of the functions described above. The functional units and modules in the embodiment can be integrated into a processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of software functional units. In addition, the specific names of the functional units and modules are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of this application. The specific working process of the units and modules in the above-mentioned device can refer to the corresponding process in the above-mentioned method embodiment, which will not be repeated here. If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present application implements all or part of the processes in the above-mentioned embodiment method, which can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a computer-readable storage medium. When the computer program is executed by the processor, the steps of the above-mentioned method embodiment can be implemented. Among them, the computer program includes computer program code, and the computer program code can be in source code form, object code form, executable file or some intermediate form. The computer-readable medium may at least include: any entity or device capable of carrying computer program code, recording medium, computer memory, read-only memory (ROM), random access memory (RAM), electric carrier signal, telecommunication signal and software distribution medium. For example, a USB flash drive, a mobile hard disk, a magnetic disk or an optical disk. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electric carrier signals and telecommunication signals.

[0087] The present application implements all or part of the processes in the above-mentioned embodiment method, and may also be completed through a computer program product. When the computer program product runs on a computer device, the computer device can implement the steps in the above-mentioned method embodiment when executing the computer program product.

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

[0089] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of this application.

[0090] In the embodiments provided in the present application, it should be understood that the disclosed devices / computer equipment and methods can be implemented in other ways. For example, the device / computer equipment embodiments described above are only schematic, for example, the division of modules or units is only a logical function division, and there may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

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

[0092] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. These modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present application, and should all be included in the protection scope of the present application.

Claims

1. A method for optimizing database query, characterized in that: The optimization method comprises: Get the query plan corresponding to the query statement; During the execution of the query plan, the query statement applies for available resources for executing the query plan. If the application fails, N execution paths corresponding to continuing to execute the query plan are obtained, where N is an integer greater than zero. Evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path; An execution path corresponding to a minimum execution cost value is selected as an optimal execution path, and the query plan is executed according to the optimal execution path.

2. The optimization method according to claim 1, characterized in that: The N execution paths include a first execution path; The step of evaluating the execution cost of the query plan under each execution path to obtain the execution cost under each execution path includes: When the execution path is the first execution path, obtaining remaining resources required for remaining data of the query plan; The execution cost of the query plan under the first execution path is evaluated according to the required remaining resources to obtain the execution cost under the first execution path.

3. The optimization method according to claim 1, characterized in that: The N execution paths also include a second execution path; The step of evaluating the execution cost of the query plan under each execution path to obtain the execution cost under each execution path further includes: When the execution path is the second execution path, switching the query operator in the query plan to a query operator that occupies less resources during execution; The execution cost of the query plan under the second execution path is evaluated according to the query operator that occupies less resources during execution, so as to obtain the execution cost under the second execution path.

4. The optimization method according to claim 1, characterized in that: The N execution paths also include a third execution path; The step of evaluating the execution cost of the query plan under each execution path to obtain the execution cost under each execution path further includes: When the execution path is the third execution path, obtaining preset resources required when the query plan is waiting to be executed; According to the preset resources, the execution cost of the query plan under the third execution path is evaluated to obtain the execution cost under the third execution path.

5. The optimization method according to claim 1, characterized in that: After the query statement applies for available resources for executing the query plan, the method further includes: If the application is successful, the query is continued based on the available resources.

6. A database query optimization device, characterized in that: The optimization device comprises: A first acquisition module is used to acquire a query plan corresponding to a query statement; A second acquisition module is used for, during the process of executing the query plan, the query statement applies for available resources for executing the query plan, and if the application fails, obtains N execution paths corresponding to continuing to execute the query plan, where N is an integer greater than zero; An evaluation module, used to evaluate the execution cost of the query plan under each execution path to obtain the execution cost under each execution path; The first execution module is used to select an execution path corresponding to a minimum execution cost as an optimal execution path, and execute the query plan according to the optimal execution path.

7. The optimization device according to claim 6, characterized in that: The optimization device also includes: The second execution module is configured to continue executing the query based on the available resources if the application is successful.

8. The optimization device according to claim 6, characterized in that: The evaluation module includes: A first acquisition unit, configured to acquire remaining resources required for remaining data of the query plan when the execution path is the first execution path; The first evaluation unit is used to evaluate the execution cost of the query plan under the first execution path according to the required remaining resources to obtain the execution cost under the first execution path.

9. A computer device, characterized in that: The computer device includes a processor, a memory, and a computer program stored in the memory and executable on the processor, and the processor implements the optimization method according to any one of claims 1 to 5 when executing the computer program.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the optimization method according to any one of claims 1 to 5 is implemented.

Images