CN103064955A - Inquiry planning method and device - Google Patents

Abstract

Embodiments of the present invention provide a query planning method and device. The query planning method includes: receiving a query request message; determining at least one query path according to the operation object of the query request message, and each query path includes at least one query operation step; for each query Path, determine the execution cost of each query path; determine the query path with the smallest execution cost as the optimal query path; select the memory that executes the query operation steps according to the set rules according to the current computing power of each memory; allocate the optimal query path The executor is dispatched to each memory for execution. The query planning method and device of the embodiments of the present invention can realize the query results obtained by executing each step of the optimal query path according to the memory determined during the optimal query path planning, and can ensure that the query results obtained through the optimal query path are actually The execution cost paid is minimal.

Description

Query planning method and device

technical field

Embodiments of the present invention relate to computer database technology, and in particular to a query planning method and device.

Background technique

When the client sends a query request to the master server of the database to query the data in the database, the master server determines the "best execution plan" for the query request through the "query planner". When planning”, usually according to the calculation cost, disk reading speed and other factors, the execution steps that can realize the query request and the ordering relationship between each operation step are determined first, and at least one “execution plan” is generated, and each “execution plan” constitutes the search space , the master server then estimates the cost of the "execution plan" in the search space, and determines the "execution plan" with the least cost as the "best execution plan" for the query request according to the cost estimation results. The query executor of the master server Execute the "best execution plan" sequentially to obtain query results, and finally feed back the query results to the client that sent the query request through the master server.

When the data in the database is distributed on different storage devices, that is, when the data is stored in a distributed storage system, multiple different storage devices are required to participate in order to generate query results for query requests. However, traditional query planning methods determine The "best execution plan" is executed sequentially in the case of the same memory, which cannot be applied to the distributed storage architecture, and the plan with the least execution cost can be obtained.

Contents of the invention

Embodiments of the present invention provide a query planning method and device, aiming at solving the problem that the actual cost of execution is not necessarily the smallest when using the "best execution plan" determined by the existing query planning method to query and obtain query results.

In a first aspect, an embodiment of the present invention provides a query planning method, including:

Receive query request message;

Determine at least one query path according to the operation object of the query request message, each query path includes at least one query operation step;

For each of the query paths, determine the execution cost of each query path, wherein the execution cost includes processor execution time, disk input and output time, and network transmission time, and the network transmission time is the transmission of data to be processed between memories time spent;

Determine the query path with the least execution cost as the optimal query path;

Allocating the optimal query path to an executor and scheduling each memory for execution.

With reference to the first aspect, in a first possible implementation manner of the first aspect, before determining at least one query path according to the operation object of the query request message, the method further includes:

According to the current computing capability of each memory, the memory for executing the query operation step is selected according to the set rule.

In a second aspect, an embodiment of the present invention provides a query planning device, including:

A receiving module, configured to receive a query request message;

A query planning module, configured to determine at least one query path according to the operation object of the query request message, each query path includes at least one query operation step; for each of the query paths, determine the execution cost of each query path, wherein the The execution cost includes processor execution time, disk input and output time, and network transmission time, and the network transmission time is the time consumed by the transmission of data to be processed between memories; the query path with the smallest execution cost is determined to be the optimal query path;

The execution module is configured to assign the optimal query path to executors and schedule each memory for execution.

In combination with the second aspect, the first possible implementation manner of the second aspect further includes:

The parallelization planning module is used to select the memory for performing the query operation step according to the set rules according to the current computing capability of each memory before the query planning module determines at least one query path according to the operation object of the query request message.

In the query planning method and device of this embodiment, by receiving a query request message, at least one query path is determined according to the operation object of the query request message, each query path includes at least one query operation step, and each query path is determined for each query path The execution cost of the query path, where the execution cost includes processor execution time, disk input and output time, and network transmission time. The query path with the smallest execution cost is determined to be the optimal query path. According to the current computing power of each memory, select according to the set rules The memory that executes the query operation steps, wherein the number of memory selected is less than the saturation point and greater than the critical point, and the optimal query path is allocated to the executor to schedule each memory for execution, which can realize the memory execution determined according to the optimal query path planning The query results obtained by each step of the optimal query path can ensure that the execution cost actually paid for obtaining the query results through the optimal query path is the smallest.

Description of drawings

In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings that need to be used in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description These are some embodiments of the present invention. For those skilled in the art, other drawings can also be obtained according to these drawings without any creative effort.

FIG. 1 is a flowchart of Embodiment 1 of the query planning method of the present invention;

FIG. 2 is a schematic diagram of a shared-nothing distributed database networking structure applicable to an embodiment of the present invention;

FIG. 3 is a flow chart of Embodiment 1 of the query planning device of the present invention.

Detailed ways

In order to make the purpose, technical solutions and advantages of the present invention clearer, the technical solutions in the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the present invention. Obviously, the described embodiments are part of the embodiments of the present invention , but not all examples. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without making creative efforts belong to the protection scope of the present invention.

The query planning method in this embodiment can be implemented by using a query planning device, which can be implemented by means of hardware or software, and the query planning device can be integrated in a computer to implement the query planning method.

Figure 1 is a flowchart of Embodiment 1 of the query planning method of the present invention. The query planning method of this embodiment can be used to perform data query operations in a Shared-nothing distributed database network. Figure 2 is an implementation of the present invention. A schematic diagram of the shared-nothing distributed database network structure applicable to the example, as shown in Figure 2, the shared-nothing distributed database network includes: a primary server, a standby server, and several storage devices, where the primary server is the entire shared-nothing distributed database The control and decision-making center of the system provides services to the client host. The main server stores data characteristic information such as data distribution information in each memory, and the main server responds to the query request of the client host. The backup server exists as the backup of the main server. . As shown in Figure 1, the query planning method of this embodiment includes:

101. Receive a query request message.

Specifically, the main server in the shared-nothing distributed database receives a query request message sent from any client host. The query request message can be a structured query language, and the query operation performed by the structured query language request server is in this implementation For example, it can be: request the server to rank all the students in classes C1, C2, and C3 in the first grade according to their math scores from low to high, and the math scores of all the students in classes C1, C2, and C3 in the first grade can be distributed in an infinite number of places. Share among multiple stores in a distributed database.

102. Determine at least one query path according to the operation object of the query request message, where each query path includes at least one query operation step.

Specifically, the query planning device of the main server determines at least one query path according to the operation object of the query request message, that is, all students in classes C1, C2, and C3 of the first grade are ranked from low to high according to their mathematics scores, and the at least one query path is able to To obtain the query path of the result of the query request, the query planning device of the main server can generate at least one query path according to a dynamic programming algorithm or a genetic algorithm, each query path includes at least one query operation step, for example, the query planning device of the main server according to the dynamic A query path A generated by the planning algorithm. The query path A includes: Operation 1-Scan the score list, that is, according to the records of the data storage location of the main server, the mathematics score data of all students in classes C1, C2 and C3 in the first grade are obtained in each The storage location of the memory; operation 2-redistribute the data according to the class number, that is, the mathematics achievement data of all the students in the C1 class are transferred from the scanned storage locations to the target memory 1 through the network, and the mathematics achievement data of all the students in the C2 class Data is transmitted to the target memory 2 through the network from each storage location scanned, and the mathematics performance data of all students in the C3 class is transmitted to the target memory 3 from each storage location scanned to, the target memory 1, the target memory 2 and The target storage 3 can be the same storage or different storages, or two of the storages are the same and different from the other storage. The query planning device of the master server can, for example, be based on all storages in the shared-nothing distributed database network. Determine the target memory 1, target memory 2, and target memory 3 for performing the operation 2; operation 3-arrange the students in each class according to the mathematics scores from low to high, which is determined in the above operation 2 The mathematics grades of all students in classes C1, C2 and C3 are sorted in target memory 1, target memory 2 and target memory 3 respectively. The query planning device of the main server can also generate another query path B according to the dynamic programming algorithm, and the query path B includes: operation 1-scanning score list; Math scores are ranked from low to high, that is, according to the scanned storage location, the scores are sorted in the storage where the scores are located; operation 3 - redistribute the data according to the class number, and the first grade C1 and C2 in the local storage The mathematics results of the students in class C3 and class C3 are respectively transmitted to target storage 1, target storage 2 and target storage 3 through the network. The target storage 1, target storage 2 and target storage 3 can be the same storage or different storages , It can also be that the two memories are the same and the other memory is different; Operation 4—on the target memory 1, the target memory 2 and the target memory 3, the students in each class are ranked from low to high according to their mathematics scores. According to the query path A and the query path B, the query request result can be obtained, and the query planning device of the main server can also generate other query paths according to the dynamic programming algorithm, and the operations included in each query path are not listed here.

103. For each query path, determine the execution cost of each query path, where the execution cost includes processor execution time, disk input and output time, and network transmission time, and the network transmission time is the time consumed by the transmission of data to be processed between memories .

Specifically, calculate the execution cost required by the main server and storage to execute each query path, that is, calculate the processor execution time, disk input and output time, and network transmission time required by the main server and storage to execute each operation included in each query path.

For example: separately calculate the execution costs of query path A, query path B, and other query paths generated by the query planning device of the master server according to the dynamic programming algorithm, the execution cost of query path A is: the processor execution scan score required to perform operation 1 The time of the table and the network transmission time required to perform operation 2 to transfer the student's math grades from the local storage where the grades are located to the target storage through the network and the time required to perform operation 3 to sort the students' grades on the target storage The sum of the calculation time of the processor; the execution cost of the query path B is: the time required for the processor to scan the grade table for operation 1 and the processing required for the processor in the local memory required for operation 2 to sort the grades of the students Computing time of the processor and the network transmission time required to transfer the mathematics grades of the students in the same class from the local storage where the grades are located to the target storage through the network and the computing time of the processor required for the target storage to perform operation 4 generated by performing operation 3 Sum.

104. Determine the query path with the smallest execution cost as the optimal query path.

Specifically, compare the execution cost of each query path determined in 103 above, and use the query path with the smallest execution cost as the optimal query path, for example, query path A has the smallest execution cost, then query path A is the optimal query path.

105. Allocate the optimal query path to an executor and schedule each memory for execution.

Specifically, the query planning device of the main server assigns the optimal query path to the executor of the main server, and the executor schedules the memory that participates in each operation of the optimal query path according to the query path, and obtains the operation result, for example: the determined participating execution The target memory of operation 2 of the optimal query path A is memory 1 and memory 2, transfer the mathematics scores of all students in classes C1 and C2 to memory 1, and transfer the mathematics scores of all students in class C3 to memory 2, then the main The server's executor schedules storage1 and storage2 to execute operation2. Then perform operation 3 to sort the grades.

Storage 1 and storage 2 feed back the execution results to the main server.

The query planning method of this embodiment can be applied to the distributed storage architecture by taking into account the network transmission cost, that is, the transmission cost between different memories in each operation of the data, and ensure that the optimal query path is obtained through the optimal query path. The query result actually pays the least execution cost.

On the basis of the above embodiments, further, before determining at least one query path according to the operation object of the query request message, the query planning device of the main server can also select and execute the query operation steps according to the set rules according to the current computing capabilities of each memory. of memory.

Specifically, when selecting a memory for performing query operation steps, for example: when selecting a memory for performing operation 2 of query path A before determining at least one query path according to the operation object of the query request message, the query planning device of the master server may according to the current Select an appropriate amount of memory to perform operation 2 of query path A based on factors such as the busyness of the memory and the size of the remaining memory space. The appropriate amount of the memory can be less than the saturation point and greater than the critical point. The saturation point and critical point of the amount of memory can be determined according to The current busy state of the memory is determined dynamically. For example, if the cost of 3 memories involved in performing operation 2 is less than the cost of 4 memories involved in performing operation 2, then 4 can be considered as the saturation point for the number of memories performing operation 2, and if 2 memories participate in performing operations The cost required to pay for 2 is greater than the cost required for 3 memories to participate in the execution of operation 2, then 2 can be considered to be the critical point of the number of memories that perform operation 2.

Those of ordinary skill in the art can understand that all or part of the steps for realizing the above-mentioned method embodiments can be completed by hardware related to program instructions, and the aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the It includes the steps of the above method embodiments; and the aforementioned storage medium includes: ROM, RAM, magnetic disk or optical disk and other various media that can store program codes.

FIG. 3 is a flowchart of Embodiment 1 of the query planning device of the present invention. As shown in FIG. For receiving a query request message; the query planning module 302 can be used to determine at least one query path according to the operation object of the query request message, each query path includes at least one query operation step; for each of the query paths, determine each The execution cost of the query path, wherein the execution cost includes processor execution time, disk input and output time, and network transmission time, and the network transmission time is the time consumed by the transmission of data to be processed between memories; determine the minimum execution cost The query path is an optimal query path; the execution module 303 can be configured to assign the optimal query path to executors and schedule each memory for execution.

The query planning apparatus 300 in this embodiment can be used to execute the query planning method in the first embodiment of the query planning method. For a specific execution method, refer to the first embodiment of the query planning method, which will not be repeated here.

The query planning device of this embodiment receives the query request message through the receiving module, the query planning module determines at least one query path according to the operation object of the query request message, each query path includes at least one query operation step, and for each query path, Determine the execution cost of each query path, where the execution cost includes processor execution time, disk input and output time, and network transmission time, determine the query path with the smallest execution cost as the optimal query path, and the execution module assigns the optimal query path to the execution The processor is scheduled to be executed by each memory, which can achieve the minimum execution cost to obtain the query results according to the optimal query path determined by the query planning module.

Further, on the basis of the above-mentioned embodiments, the query planning device 300 may further include: a parallel planning module, wherein the parallel planning module may be used to determine at least one query in the query planning module according to the operation object of the query request message Before the path, according to the current computing power of each memory, the memory for performing the query operation step is selected according to the set rules.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solutions of the present invention, rather than limiting them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: It is still possible to modify the technical solutions described in the foregoing embodiments, or perform equivalent replacements for some or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the technical solutions of the various embodiments of the present invention. scope.

Claims (4)

1. a query planning method is characterized in that, comprising:

Receive inquiry request message;

Determine at least one query path according to the operand of described inquiry request message, each query path comprises at least one query manipulation step;

For each described query path, determine the Executing Cost of each query path, wherein said Executing Cost comprises processor execution time, disk input and output time and network latency, and described network latency is that pending data are transmitted the time that consumes between storer;

The query path of determining the Executing Cost minimum is optimum query path;

Actuator is distributed in described optimum query path to be dispatched to each storer execution.

2. query planning method according to claim 1 is characterized in that, before determining at least one query path according to the operand of described inquiry request message, also comprises:

According to the arithmetic capability of current each storer, according to the storer of setting rules selection execution query manipulation step.

3. a query planning device is characterized in that, comprising:

Receiver module is used for receiving inquiry request message;

The query planning module is used for determining at least one query path according to the operand of described inquiry request message that each query path comprises at least one query manipulation step; For each described query path, determine the Executing Cost of each query path, wherein said Executing Cost comprises processor execution time, disk input and output time and network latency, and described network latency is that pending data are transmitted the time that consumes between storer; The query path of determining the Executing Cost minimum is optimum query path;

Execution module is used for that actuator is distributed in described optimum query path and dispatches to each storer execution.

4. device according to claim 3 is characterized in that, also comprises:

The parallelization planning module was used for before the query planning module is determined at least one query path according to the operand of described inquiry request message, according to the arithmetic capability of current each storer, according to the storer of setting rules selection execution query manipulation step.

Images