Database query processing method, machine readable storage medium and computer device

Abstract

The application provides a database query processing method, a machine readable storage medium and a computer device. The query processing method comprises the following steps: determining the batch size of each execution operator according to the data volume of each execution operator; and performing each execution operator in batches according to the batch size of each execution operator. In the technical scheme of the application, the batch size of each execution operator is dynamically obtained according to the data volume of each execution operator instead of being fixed, so that the rationality of the selection of the batch size of each execution operator can be ensured, and the speed of data query is prevented from being affected due to too small batch size, or space is prevented from being wasted due to too large batch size.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database query processing method, a machine-readable storage medium, and a computer device. Background Art

[0002] With the rapid advancement of information technology, database technology has also developed rapidly, and the performance requirements for database data query execution have become increasingly stringent. Batch execution is a technology that can accelerate database data query speed. Its characteristic is that after the database generates an execution plan based on the query statement, it can execute multiple execution lines of execution operators at once. Compared with the conventional execution of only one execution line of execution operator at a time, it can significantly improve the execution speed of the execution plan.

[0003] In the prior art, when databases use batch execution, the batch size of each execution operator is fixed. The batch size refers to the number of statements executed during each batch execution. However, when querying database data, the amount of data in each execution operator varies. If the batch size is smaller than the amount of data in the execution operator, the batch execution of the execution operator will need to be executed multiple times, slowing down the query. Conversely, if the batch size is larger than the amount of data in the execution operator, space is wasted. Summary of the Invention

[0004] In view of the above problems, the present invention is proposed to provide a database query processing method, machine-readable storage medium and computer device that overcome the above problems or at least partially solve the above problems, and can solve the technical problem of unreasonable batch size during batch execution, thereby improving data query speed and reducing space waste.

[0005] Specifically, the present invention provides a database query processing method, comprising:

[0006] Determining a batch size for each execution operator based on the amount of data for each execution operator; and

[0007] According to the batch sizes, the execution operators are executed in batches respectively.

[0008] According to one embodiment of the present invention, determining the batch size of each execution operator according to the amount of data of each execution operator includes:

[0009] The interval in which the data volume is located is obtained, and the batch size is determined according to the interval.

[0010] According to one embodiment of the present invention, executing the execution operators in batches according to the batch size includes:

[0011] Batch execution of join operators, batch execution of scan operators, and / or batch execution of aggregate operators.

[0012] According to one embodiment of the present invention, the batch execution of the join operator includes batch execution of a hash join algorithm, batch execution of a merge join algorithm, or batch execution of a nested loop join algorithm;

[0013] The batch execution scan operator includes a batch execution column storage scan algorithm; and / or

[0014] The batch execution of the aggregation operator includes batch execution of a hash aggregation algorithm or batch execution of a sorting aggregation algorithm.

[0015] According to one embodiment of the present invention, executing the execution operators in batches according to the batch size further includes:

[0016] Execute expression calculations in batches.

[0017] According to one embodiment of the present invention, executing the execution operators in batches according to the batch size further includes:

[0018] SIMD technology is used to accelerate the calculation of the expression.

[0019] According to one embodiment of the present invention, the use of SIMD technology to accelerate the calculation of the expression includes:

[0020] Comparing the column data with the set data using the SIMD technique; and / or

[0021] The SIMD technology is used to compress, decompress, encrypt and / or decrypt column data.

[0022] According to one embodiment of the present invention, executing the execution operators in batches according to the batch size further includes:

[0023] The calculation of each of the expressions is performed by compiling and executing.

[0024] On the other hand, the present invention further provides a machine-readable storage medium having a machine-executable program stored thereon. When the machine-executable program is executed by a processor, the query processing method as described in any one of the above embodiments is implemented.

[0025] On the other hand, the present invention also provides a computer device, including a memory, a processor, and a machine executable program stored in the memory and running on the processor, and when the processor executes the machine executable program, it implements the query processing method described in any one of the above embodiments.

[0026] The technical solution provided by the present invention, when executing operators in batches, first determines the batch size of each operator based on the amount of data in each operator, and then executes each operator in batches based on the batch size. Because the batch size of each operator in the technical solution of the present invention is dynamically determined based on the amount of data in each operator rather than being fixed, the rationality of the batch size selection for each operator can be ensured, preventing the batch size from being too small, which would affect the speed of data query, or the batch size from being too large, which would waste space.

[0027] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0029] Figure 1 is an exemplary flow chart of a database query processing method according to one embodiment of the present invention;

[0030] Figure 2 is an exemplary flow chart for determining the batch size of each execution operator based on the amount of data of each execution operator according to one embodiment of the present invention;

[0031] Figure 3 is a schematic diagram of a machine-readable storage medium according to one embodiment of the present invention;

[0032] Figure 4 is a schematic diagram of a computer device according to one embodiment of the present invention. DETAILED DESCRIPTION

[0033] Refer to the following Figures 1 to 4 To describe a database query processing method, machine-readable storage medium, and computer device according to an embodiment of the present invention. In the description of this embodiment, the reference terms "one embodiment," "some embodiments," "illustrative embodiments," "example," "specific example," or "some examples" mean 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 invention. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials, or characteristics described may be combined in any appropriate manner in any one or more embodiments or examples.

[0034] Figure 1What is shown is an exemplary flow chart of a query processing method for a database according to an embodiment of the present invention. The query processing method is applicable to OLAP (Online Analytical Processing) business, and the database adopts a storage based on a column storage architecture, that is, the data in the database adopts a column storage method. The query processing method of this embodiment realizes the batch execution of each execution operator of the data query on this basis, and when executing each execution operator in batches, the batch size of each execution operator is adaptively and dynamically determined to improve the rationality of the batch size selection. Figure 1 The process shown is a detailed introduction to the database query processing method of this embodiment.

[0035] like Figure 1 As shown, the database query processing method of this embodiment includes the following steps:

[0036] Step S1: Obtain the data volume of each execution operator;

[0037] Step S2: Determine the batch size of each execution operator based on the amount of data of each execution operator;

[0038] Step S3: Execute each execution operator in batches according to the batch size of each execution operator.

[0039] Acquiring the data volume of each execution operator in the above step S1 means that when the database generates an execution plan, it evaluates the total number of rows of execution statements in each execution operator and uses the total number of rows of execution statements in each execution operator as the data volume corresponding to each execution operator.

[0040] In step S2 above, a preset rule can be used to determine the batch size of each execution operator based on the data volume of each execution operator. Taking a single execution operator as an example, when determining the batch size of the execution operator based on the data volume of the execution operator, a preset value is first obtained that is no less than the data volume of the execution operator and whose difference from the data volume of the execution operator is no greater than a preset difference value, and the preset value is used as the batch size of the execution operator.

[0041] In the above step S3, taking an execution operator as an example, executing the execution operator in batches according to the batch size of the execution operator means batch processing the algorithms of each execution operator according to the batch size of each execution operator, that is, the number of lines of execution statements executed each time is the above batch size, so that each execution operator is completed with the least number of executions.

[0042] To sum up, the technical scheme of the embodiment first determines the batch size of each execution operator according to the data volume of each execution operator, and then batch executes each execution operator according to the batch size of each execution operator. Since the batch size of each execution operator in the technical scheme of the application is dynamically obtained according to the data volume of each execution operator rather than fixed, the rationality of the selection of the batch size of each execution operator can be ensured, and the speed of data query is prevented from being affected by too small batch size, or space is prevented from being wasted by too large batch size.

[0043] The setting mode of the embodiment can adaptively determine the batch size of each execution operator according to the data volume of each execution operator in the query processing process of the database, and has outstanding substantial features and significant progress compared with the fixed batch size.

[0044] The method of determining the batch size of each execution operator according to the data volume of each execution operator in step S2 will be described in detail below in combination with a specific application scenario. It can be understood that the method of determining the batch size of each execution operator described below is exemplary and not limiting, and in other embodiments, other ways of determining the batch size of each execution operator according to the data volume of each execution operator can be used.

[0045] In an embodiment of the application, the process of determining the batch size of each execution operator according to the data volume of each execution operator in step S2 is as shown in Figure 2 The process of determining the batch size of each execution operator according to the data volume of each execution operator in step S2 is as shown in

[0046] Step S21: Obtain the interval in which the data volume of each execution operator is located.

[0047] Step S22: Determine the batch size of each execution operator according to the interval in which the data volume of each execution operator is located.

[0048] In step S21, a plurality of value intervals can be pre-set, and a preset batch size is set for each value interval. For example, the plurality of value intervals pre-set above can be (0, 64], (64, 128], (128, 256], (256, 512], (512, 1024] and (1024, ∞), wherein the preset batch size corresponding to the interval (0, 64] is 64, the preset batch size corresponding to the interval (64, 128] is 128, the preset batch size corresponding to the interval (128, 256] is 256, the preset batch size corresponding to the interval (256, 512] is 512, the preset batch size corresponding to the interval (512, 1024] is 1024, and the preset batch size corresponding to the interval (1024, ∞) is also 1024.

[0049] In the above step S2, when determining the batch size of each execution operator based on the interval in which the data volume of each execution operator is located, the preset batch size corresponding to the interval in which the data volume of each execution operator is located is used as the batch size of the corresponding execution operator, and when the data volume of the execution operator is greater than the preset threshold, the preset threshold can be used as the batch size of the execution operator.

[0050] Taking one of the execution operators as an example, the method for determining the batch size of the execution operator based on the range of the data volume of the execution operator includes:

[0051] If the data volume of the operator is in the interval (0, 64], the batch size of the operator is 64; if the data volume of the operator is in the interval (64, 128], the batch size of the operator is 128; if the data volume of the operator is in the interval (128, 256], the batch size of the operator is 256; if the data volume of the operator is in the interval (256, 512], the batch size of the operator is 512; if the data volume of the operator is in the interval (512, 1024], the batch size of the execution operator is 1024; if the data volume of the execution operator is less than 1024, the batch size of the execution operator is still 1024. For example, when the data volume of one of the execution operators is 18 rows, the batch size of the execution operator is set to 64; when the data volume of one of the execution operators is 1000 rows, the batch size of the execution operator is set to 1024; when the data volume of one of the execution operators is greater than 1024 rows, the batch size of the execution operator is still set to 1024.

[0052] In this embodiment, the threshold value of each interval is an empirical value obtained by the staff based on experience. In other embodiments, other threshold values ​​may be used for each interval.

[0053] Through the setting method of this embodiment, the corresponding batch size can be quickly determined according to the interval of the data volume of each execution operator, so that the batch size of each execution operator can match the corresponding data volume, thereby further improving the rationality of the batch size of each execution operator.

[0054] The following describes in detail the method for batch-executing the corresponding execution operators according to the batch sizes in step S3 described above, in conjunction with specific application scenarios. It should be understood that the methods for batch-executing the execution operators described in the following embodiments are exemplary and non-limiting. In other embodiments, other methods may be used to batch-executing the execution operators.

[0055] During the database query execution process, after an execution plan is generated based on the query statement, data scanning, data joining, and data aggregation are performed according to the execution plan. Therefore, in one embodiment of the present invention, the execution operators of the execution plan include a scan operator, a join operator, and an aggregation operator; accordingly, the batch execution of each execution operator in step S3 includes: batch execution of the scan operator, batch execution of the join operator, and / or batch execution of the aggregation operator.

[0056] Through the configuration of this embodiment, batch processing can be fully utilized in the process of querying data in the database to speed up the data query and improve the working efficiency of the data query.

[0057] A table join is a common operation performed between tables in a database during data queries. It extracts tuples with identical columns from multiple tables to form a new table. In one embodiment of the present invention, the join operator can employ a hash join algorithm, a merge join algorithm, or a nested loop join algorithm. Accordingly, batch execution of the join operator includes batch execution of the hash join algorithm, the merge join algorithm, or the nested loop join algorithm.

[0058] The hash join described above is suitable for joining unordered data tables. Its input consists of two data tables. The query optimizer uses the table with the smaller amount of data as the build input and the table with the larger amount of data as the probe input. During the build phase, the build input is scanned to generate a hash table. During the probe phase, the hash value of each row in the probe input is calculated and matched against the data in the hash table to obtain matching rows in the probe input. During the probe phase, each hash join operation scans each row of the probe input only once, and then scans the entire hash table based on the hash key value.

[0059] The above-mentioned merge join algorithm is applicable to joining ordered data tables, which includes: first, accessing the first data table with the predicate condition specified in the target query statement, then sorting the access results according to the connection column in the first data table, and recording the sorted result set as the first result set; then accessing the second data table with the predicate condition specified in the target query statement, then sorting the access results according to the connection column in the second data table, and recording the sorted result set as the second result set; finally, performing a merge operation on the first result set and the second result set to realize the merge join.

[0060] The above nested loop join algorithm is a table join method that relies on two layers of nested loops (i.e., an outer loop and an inner loop) to obtain the join result set when joining two data tables. That is, the inner loop traversing the driven table must be performed as many times as there are records in the driving result set corresponding to the outer loop.

[0061] In this embodiment, since the database adopts storage based on column storage architecture, when performing batch scanning, a column storage scanning batch execution algorithm is adopted. That is, when scanning the data in the data table, the number of data rows scanned each time is the batch size corresponding to the scanning operator.

[0062] In this embodiment, the aggregation operator is used to aggregate the data queried from the data table according to the execution plan during the execution of the execution plan, and the aggregation operator includes a hash aggregation algorithm or a sorting aggregation algorithm. Accordingly, the batch execution aggregation operator includes the batch execution of the hash aggregation algorithm or the batch execution of the sorting aggregation algorithm.

[0063] The above-mentioned hash aggregation algorithm and sorting aggregation algorithm are both used to aggregate data queried from a data table. The hash aggregation algorithm can be used to perform discrete aggregation on unordered hash data queried from a data table, and the sorting aggregation algorithm can be used to perform ordered aggregation on ordered data queried.

[0064] Through the configuration of this embodiment, the batch execution method can be adopted when the connection operator, scan operator and aggregation operator adopt different algorithms, thereby improving the applicability of batch execution and achieving the problem of improving the execution efficiency of each execution operator.

[0065] In one embodiment of the present invention, the batch execution of each execution operator in step S3 further includes: batch execution of expression calculations.

[0066] In this embodiment, when batch executing expression calculations, different batch execution modes can be used for different situations. For example, batch execution can be used when comparing columns of an operator with data stored in the columns, when comparing data stored in the columns of an operator with a constant, or when evaluating expressions in an operator, such as summing expressions in an operator.

[0067] Through the configuration of this embodiment, batch execution can be used to perform expression calculations, thereby increasing the execution speed of expressions and improving the execution efficiency of data queries.

[0068] In one embodiment of the present invention, when executing operators in batches, it further includes: using SIMD technology to accelerate the calculation of expressions.

[0069] SIMD (Single Instruction Multiple Data) technology achieves spatial parallelism by simultaneously executing the same operation on each piece of data within a set of data (also known as a "data vector"). In this embodiment, SIMD technology is used to accelerate expression calculations. This refers to using SIMD technology to simultaneously execute operations on algorithms of the same type within an expression, thereby increasing the speed of expression calculations and improving the efficiency of database data query execution.

[0070] The following is a detailed introduction to the method of using SIMD technology to accelerate the calculation of expressions in combination with specific application scenarios. It can be understood that the method of using SIMD technology to accelerate the calculation of expressions introduced in the following embodiments is exemplary and not restrictive. In other embodiments, other methods can be used to accelerate the calculation of expressions through SIMD technology.

[0071] In one embodiment of the present invention, the above-mentioned use of SIMD technology to accelerate the calculation of the expression includes:

[0072] Using SIMD technology to compare the column data with a specific set of data, that is, using SIMD technology to compare multiple values ​​of multiple data stored in the column with the specific set of data; and / or

[0073] Use SIMD technology to compress, decompress, encrypt, and / or decrypt column data.

[0074] The above-mentioned column data refers to data stored in a data table of a database in a column storage manner, for example, using data storage processing.

[0075] In this embodiment, the speed of data comparison, data compression and decompression, and data encryption and decryption during the expression calculation process can be accelerated to improve the execution efficiency of the expression calculation. Furthermore, through the configuration of this embodiment, SIMD technology can be fully utilized in the expression calculation to maximize the execution efficiency of the expression calculation.

[0076] In one embodiment of the present invention, batch execution of the execution operators further includes: executing calculations of the expressions in a compilation execution manner.

[0077] When executing each expression, it is necessary to write the corresponding target code based on the expression and compile the target code into a corresponding executable target program. Prior art methods for compiling target code include interpreted execution and compiled execution. Interpreted execution involves interpreting the target code line by line into the corresponding machine code and executing it during program execution. This means that each line is interpreted and executed during the execution process. Compiled execution involves a compiler compiling the target code into the corresponding target program all at once. Compared to interpreted execution, this method offers the advantage of faster compilation speed.

[0078] In this embodiment, a compilation-based execution approach is used to calculate each expression. For example, when executing a hash join algorithm, compilation-based execution is used to evaluate the inner and outer table data in the join condition and to compare the data. Because compilation-based execution allows the compiler to compile the target code into a target program in one go, it can speed up expression calculation and improve query processing efficiency.

[0079] This embodiment also provides a machine-readable storage medium and a computer device. Figure 3 is a schematic diagram of a machine-readable storage medium 330 according to one embodiment of the present invention; Figure 4 3 is a schematic diagram of a computer device 400 according to an embodiment of the present invention. The machine-readable storage medium 330 stores a machine-executable program 340 thereon. When the machine-executable program 340 is executed by a processor, it implements the database query processing method of any of the above embodiments.

[0080] The computer device 400 may include a memory 420, a processor 410, and a machine executable program 340 stored in the memory 420 and running on the processor 410. When the processor 410 executes the machine executable program 440, the database query processing method of any of the above embodiments is implemented.

[0081] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by an instruction execution system, device or equipment (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 equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0082] For the description of the present embodiment, the machine-readable storage medium 430 can be any means for storing, communicating, propagating or transporting a program for use by or in connection with an instruction execution system, apparatus or device. More specific examples (a non-exhaustive list) of the computer-readable medium include the following: an electrical connection having one or more wires (electrical apparatus), a portable computer diskette (magnetic apparatus), a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, and a portable compact disc read-only memory (CDROM). In addition, the computer-readable medium 330 can even be paper or another suitable medium upon which the program is printed, because the program can be electronically captured, for example, by optically scanning the paper or other suitable medium, then electronically converted into a form that is suitable for use by the computer, and then stored in computer memory.

[0083] It should be understood that parts of the application can be implemented in hardware, software, firmware or a combination thereof. In the above embodiments, a number of steps or methods can be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.

[0084] The computer device 400 can be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, the computer device 400 can be a cloud computing node. The computer device 400 can be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules can include routines, programs, objects, components, logic, data structures, and the like, that perform particular tasks or implement particular abstract data types. The computer device 900 can be implemented in a distributed cloud computing environment in which remote processing devices are linked through a communications network. In a distributed cloud computing environment, program modules can be located in local or remote computer system storage media including memory storage devices.

[0085] The computer device 400 can include a processor 410 suitable for executing stored instructions, a memory 420 that provides temporary storage for the operations of the instructions during operation. The processor 410 can be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 420 can include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0086] The processor 410 can be connected to an I / O interface (input / output interface) suitable for connecting the computer device 400 to one or more I / O devices (input / output devices) via a system interconnect (e.g., PCI, PCI-Express, etc.). The I / O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touchpad or a touch screen, etc. The I / O devices may be built-in components of the computer device 400, or may be devices externally connected to the computing device.

[0087] Processor 410 can also be linked to the display interface that is suitable for connecting computer device 400 to display device through system interconnection.Display device can include the display screen that is built-in component of computer device 400.Display device can also include the computer monitor, television or projector etc. that are externally connected to computer device 400.In addition, network interface controller (network interface controller, NIC) can be suitable for connecting computer device 400 to network through system interconnection.In certain embodiments, NIC can use any suitable interface or protocol (such as Internet Small Computer System Interface etc.) to transmit data.Network can be cellular network, radio network, wide area network (WAN)), local area network (LAN) or Internet etc.Remote device can be connected to computing device through network.

[0088] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all cases. In addition, the above method may include additional operations. Within the scope of the technical ideas provided by the method of this embodiment, additional changes can be made to the above method.

[0089] At this point, those skilled in the art will recognize that, although a number of exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications consistent with the principles of the present invention may be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.

Claims

1. A database query processing method, comprising: Get the data volume of each execution operator; Obtaining the data volume of each execution operator means that when the database generates an execution plan, it evaluates the total number of rows of execution statements in each execution operator and uses the total number of rows of execution statements in each execution operator as the data volume corresponding to each execution operator; Determining the batch size of each execution operator based on the data volume of each execution operator, wherein the batch size refers to the number of execution statements executed in each batch execution; determining the batch size of each execution operator based on the data volume of each execution operator includes: Multiple value intervals are pre-set, and each value interval corresponds to a preset batch size; The preset batch size corresponding to the data volume interval of each execution operator is used as the batch size of the corresponding execution operator; According to the batch sizes, the execution operators are executed in batches respectively.

2. The query processing method according to claim 1, wherein: The step of executing the execution operators in batches according to the batch sizes includes: Batch execution of join operators, batch execution of scan operators, and / or batch execution of aggregate operators.

3. The query processing method according to claim 2, wherein: The batch execution connection operator includes a batch execution hash connection algorithm, a batch execution merge connection algorithm or a batch execution nested loop connection algorithm; The batch execution scan operator includes a batch execution column storage scan algorithm; and / or The batch execution of the aggregation operator includes batch execution of a hash aggregation algorithm or batch execution of a sorting aggregation algorithm.

4. The query processing method according to claim 2, wherein: The executing operators in batches according to the batch sizes further includes: Execute expression calculations in batches.

5. The query processing method according to claim 4, wherein: The executing operators in batches according to the batch sizes further includes: SIMD technology is used to accelerate the calculation of the expression.

6. The query processing method according to claim 5, wherein: The use of SIMD technology to accelerate the calculation of expressions includes: Comparing the column data with the set data using the SIMD technique; and / or The SIMD technology is used to compress, decompress, encrypt and / or decrypt column data.

7. The query processing method according to claim 4, wherein: The executing operators in batches according to the batch sizes further includes: The calculation of each of the expressions is performed by compiling and executing.

8. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program, when executed by a processor, implements the query processing method according to any one of claims 1 to 7.

9. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the query processing method according to any one of claims 1 to 7 when executing the machine executable program.

Images