Query optimization method, device and data query system

Abstract

An embodiment of the present invention discloses a query optimization method, comprising: receiving an execution plan, the execution plan being generated based on a query statement and including multiple operation nodes; determining a target operation node in the execution plan that meets optimization conditions; determining a target path included in the execution plan and target data associated with the target path based on the target operation node; and optimizing the target path to convert the storage format of the target data. The embodiment of the present invention also discloses a corresponding query optimization apparatus, a data query system, a computing device, and a readable storage medium.

Description

Technical Field

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

[0002] Databases organize, store, and manage data according to data structures, and mostly use Structured Query Language (SQL) to write and query data.

[0003] In common database types, data can be stored in row storage format and / or column storage format. Specifically, the row storage format is usually very suitable for online transaction processing (OLTP), but it is not conducive to parallel computing and the query speed is slow when a large amount of data needs to be processed. In online analytical processing (OLAP), the column storage format shows good performance because it only needs to read the data of those columns required to process the query, which can greatly reduce disk input / output (I / O) operations. Compared with the row storage format, the query efficiency is higher and it also has the advantage of good compression effect. However, the characteristics of the column storage format lead to discontinuous data distribution. When the query needs to access multiple columns of data at the same time, there is a problem of low memory access efficiency.

[0004] Therefore, a more advanced query optimization solution is needed to take into account the advantages of both row storage format and column storage format and improve query performance. Summary of the Invention

[0005] To this end, embodiments of the present invention provide a query optimization method, apparatus, and data query system to attempt to solve or at least alleviate at least one of the above problems.

[0006] According to one aspect of an embodiment of the present invention, a query optimization method is provided, comprising: receiving an execution plan, the execution plan being generated based on a query statement and including a plurality of operation nodes; determining a target operation node in the execution plan that meets optimization conditions; based on the target operation node, determining a target path contained in the execution plan and target data associated with the target path; and optimizing the target path so as to convert the storage format of the target data.

[0007] Optionally, in the method according to the embodiment of the present invention, the optimization condition includes: the operation node is suitable for performing calculations on data of multiple columns.

[0008] Optionally, in the method according to the embodiment of the present invention, the operation node is suitable for performing calculations on data of multiple columns, including: the operation node is suitable for performing association calculations or aggregation calculations.

[0009] Optionally, in a method according to an embodiment of the present invention, based on the target operation node, the target path and target data associated with the target path included in the execution plan are determined, including: based on each target operation node in the execution plan and the data of multiple columns calculated by each target operation node, the target path and the target data associated with the target path are determined, the target path includes at least one target operation node, and the target data is the data of multiple columns calculated jointly by at least some of the target operation nodes in the target path.

[0010] Optionally, in the method according to the embodiment of the present invention, the target path includes a plurality of consecutive target operation nodes in the execution plan, and the target data is data of a plurality of columns processed jointly by the plurality of consecutive target operation nodes in the target path.

[0011] Optionally, in a method according to an embodiment of the present invention, the target path includes multiple discontinuous target operation nodes in the execution plan, and the target data is data of multiple columns jointly calculated by the multiple discontinuous target operation nodes in the target path, and is not data calculated by the operation nodes located between the multiple discontinuous target operation nodes in the target path.

[0012] Optionally, in a method according to an embodiment of the present invention, the storage format includes a row storage format and a column storage format, and the target path is optimized, including: adding corresponding conversion nodes at both ends of the target path, so as to convert the target data from the column storage format to the row storage format, and / or from the row storage format to the column storage format.

[0013] Optionally, in a method according to an embodiment of the present invention, the target data is stored in a column storage format, and corresponding conversion nodes are added at both ends of the target path, including: adding a column-row conversion node at the input end of the target path to convert the target data into a row storage format; adding a row-column conversion node at the output end of the target path to convert the target data into a column storage format.

[0014] Optionally, in a method according to an embodiment of the present invention, the target data is stored in a row storage format, and corresponding conversion nodes are added at both ends of the target path, including: adding a row-column conversion node at the input end of the target path to convert the target data into a column storage format; adding a column-row conversion node at the output end of the target path to convert the target data into a row storage format.

[0015] Optionally, the method according to the embodiment of the present invention further includes: before optimizing the target path, determining whether to optimize the target path based on the data volume of the target data.

[0016] Optionally, the method according to the embodiment of the present invention further includes: executing the optimized execution plan.

[0017] According to another aspect of an embodiment of the present invention, a query optimization method is provided, including: receiving a query statement and generating an execution plan corresponding to the query statement, the execution plan including multiple operation nodes; determining a target operation node in the execution plan that meets the optimization conditions; based on the target operation node, determining a target path contained in the execution plan and target data associated with the target path; optimizing the target path to convert the storage format of the target data; and sending the optimized execution plan to a computing server.

[0018] According to another aspect of an embodiment of the present invention, a query optimization device is provided, including: a plan receiving module, suitable for receiving an execution plan, the execution plan is generated based on a query statement and includes multiple operation nodes; a target determination module, suitable for determining a target operation node in the execution plan that meets the optimization conditions; based on the target operation node, determining a target path contained in the execution plan and target data associated with the target path; and a plan optimization module, suitable for optimizing the target path so as to convert the storage format of the target data.

[0019] According to another aspect of an embodiment of the present invention, a query optimization device is provided, including: a query receiving module, suitable for receiving a query statement; a plan generating module, suitable for generating an execution plan corresponding to the query statement, the execution plan including multiple operation nodes; a target determining module, suitable for determining a target operation node in the execution plan that meets the optimization conditions; based on the target operation node, determining a target path contained in the execution plan and target data associated with the target path; a plan optimizing module, suitable for optimizing the target path so as to convert the storage format of the target data; and a plan sending module, suitable for sending the optimized execution plan to a computing server.

[0020] According to another aspect of an embodiment of the present invention, a data query system is provided, including: a front-end server, suitable for receiving query statements; generating an execution plan corresponding to the query statements, and sending the execution plan to a computing server; the computing server, including a query optimization device according to an embodiment of the present invention, the computing server is suitable for executing the optimized execution plan so as to operate on the data stored in the storage server; returning the query results obtained by executing the execution plan to the front-end server; and a storage server, suitable for storing data.

[0021] According to another aspect of an embodiment of the present invention, a data query system is provided, comprising: a front-end server, comprising a query optimization device according to an embodiment of the present invention; a computing server, suitable for executing an optimized execution plan so as to operate on data stored in a storage server; returning query results obtained by executing the execution plan to the front-end server; and a storage server, suitable for storing data.

[0022] According to another aspect of an embodiment of the present invention, a computing device is provided, comprising: one or more processors; a memory; and one or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, and the one or more programs include instructions for executing any one of the query optimization methods according to an embodiment of the present invention.

[0023] According to another aspect of an embodiment of the present invention, a readable storage medium storing a program is provided. The program includes instructions that, when executed by a computing device, cause the computing device to perform any one of the query optimization methods according to an embodiment of the present invention.

[0024] The query optimization solution according to an embodiment of the present invention determines the target path and target data based on the operation nodes in the execution plan, and changes and restores the storage format of the target data by optimizing the target path. This improves memory access efficiency and optimizes query performance while not affecting the computational logic of the original execution plan. Since only the storage format of the target data is changed and the storage format of the remaining data is retained, the advantages of both the row storage format and the column storage format can be taken into account, thereby further optimizing query performance. In addition, the query optimization solution according to an embodiment of the present invention converts the storage format by adding corresponding conversion nodes to the target path, which has low modification costs and is easy to implement. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] To achieve the above and related purposes, certain illustrative aspects are described herein in conjunction with the following description and accompanying drawings, which indicate various ways in which the principles disclosed herein may be practiced, and all aspects and their equivalents are intended to fall within the scope of the claimed subject matter. The above and other objects, features, and advantages of the present disclosure will become more apparent by reading the following detailed description in conjunction with the accompanying drawings. Throughout this disclosure, the same reference numerals generally refer to the same parts or elements.

[0026] Figure 1 FIG2 shows a schematic diagram of a data query system 100 according to an embodiment of the present invention;

[0027] Figure 2 A schematic diagram showing a storage format according to an embodiment of the present invention;

[0028] Figure 3shows a schematic diagram of a computing device 300 according to one embodiment of the present invention;

[0029] Figure 4 A flowchart of a query optimization method 400 according to an embodiment of the present invention is shown;

[0030] Figure 5A and Figure 5B Schematic diagrams of an execution plan before and after optimization according to an embodiment of the present invention are respectively shown;

[0031] Figure 6 A flowchart of a query optimization method 600 according to an embodiment of the present invention is shown;

[0032] Figure 7 A schematic diagram showing a query optimization apparatus 700 according to an embodiment of the present invention; and

[0033] Figure 8 FIG. 8 is a schematic diagram of a query optimization apparatus 800 according to an embodiment of the present invention. DETAILED DESCRIPTION

[0034] Exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0035] Figure 1 FIG. 1 shows a schematic diagram of a data query system 100 according to an embodiment of the present invention. Figure 1 As shown, the data query system 100 may include a front-end server 120, a computing server 140 and a storage server 160. It should be noted that Figure 1 The number of servers of each type shown is only an example, and the embodiment of the present invention does not limit the specific number of front-end servers 120, computing servers 140, and storage servers 160. In other embodiments, the data query system 100 may further include different and / or additional modules.

[0036] The data query system 100 may support querying using any suitable query language, such as Structured Query Language (SQL). The front-end server 120 is adapted to receive query statements defined in a query language supported by the system 100. For example, the front-end server 120 may receive a query request from the client 110, the query request including an SQL query statement.

[0037] The front-end server 120 may also parse the received query statement to obtain a query tree structure, such as an abstract syntax tree. Based on the query tree structure, the front-end server 120 generates an execution plan (also called a query plan or query execution plan) corresponding to the query statement and sends the execution plan to the computing server 140 for execution.

[0038] In some embodiments, the front-end server 120 may further split the execution plan into multiple fragmented execution plans and send each of these fragmented execution plans to the corresponding compute servers 140 so that these fragmented execution plans can be executed on the distributed servers. Unless otherwise specified, the execution plans mentioned below may refer to either complete execution plans or fragmented execution plans.

[0039] Specifically, an execution plan (full or fragmented) includes the steps required to execute a query statement. These steps are represented by multiple operation nodes connected in the execution order. Each operation node reads data from its input, processes the data (e.g., performs various calculations), and then outputs it to its parent node.

[0040] The execution plan can generally be represented in a tree format, or in other appropriate formats, such as a table format. The embodiment of the present invention does not limit the specific format of the execution plan.

[0041] Computing server 140 may receive and execute the execution plan to perform operations on the data stored by storage server 160, such as querying, inserting, deleting, and / or updating the data stored by storage server 160. After computing server 140 obtains a query result by executing the execution plan, it may return the query result to front-end server 120, which then returns the query result to client 110.

[0042] Those skilled in the art will appreciate that the data in the storage server 160 is stored in the form of tables. Each table includes a set of records or tuples of data stored in one or more data fields. The records of a table are also called rows, and the data fields recorded in the table are also called columns. During the execution of the execution plan, the computing server 140 generally stores the data read from the storage server 160 in a row-based storage format and / or a column-based storage format. The row-based storage format organizes data in the form of rows, and the data in a row exists in a continuous storage form in the storage medium. This storage format can efficiently handle operations such as data insertion (INSERT), deletion (DELETE) and update (UPDATA), and is particularly suitable for OLTP scenarios. The column-based storage format organizes data in the form of columns, and the data in a column exists in a continuous storage form in the storage medium, and is suitable for OLAP scenarios that require complex data analysis.

[0043] For ease of understanding, the following Figure 2 Describes the row storage format and column storage format.

[0044] Figure 2 If the table shown is stored in row-store format, the data in the table is stored sequentially in rows, as indicated by the "Row Store" label below the table. If it is stored in column-store format, the data in the table is stored sequentially in columns, as indicated by the "Column Store" label below the table. It should be noted that whether a table is stored in row-store or column-store format refers to the different way data is stored in memory, not the table itself.

[0045] When storing data in a column-based format, due to the discontinuous data distribution, queries that require simultaneous access to multiple columns of data (common in OLAP scenarios) suffer from low memory access efficiency. Similarly, when storing data in a row-based format, queries that require simultaneous access to multiple columns of data also suffer from low memory access efficiency.

[0046] In order to solve the above problems, according to an embodiment of the present invention, the computing server 140 may include a query optimization device 700. The query optimization device 700 may optimize the execution plan using the query optimization method 400 according to an embodiment of the present invention before the computing server 140 executes the received execution plan (which will be described in detail later).

[0047] Optionally, according to another embodiment of the present invention, the front-end server 120 may also optimize the execution plan. Specifically, the front-end server 120 may include a query optimization device 800. After the front-end server 140 generates an execution plan, the query optimization device 800 may optimize the execution plan using the query optimization method 600 according to an embodiment of the present invention (described in detail below). After the query optimization device 800 optimizes the execution plan, the front-end server 120 may send the optimized execution plan to the computing server 140 for execution.

[0048] In these embodiments, various components in the above-mentioned data query system 100, such as various servers, etc., can be implemented by the computing device 300 described below.

[0049] Figure 3 FIG. 3 is a schematic diagram of a computing device 300 according to an embodiment of the present invention. Figure 3 As shown, in a basic configuration 302, computing device 300 typically includes system memory 306 and one or more processors 304. A memory bus 308 may be used for communication between processor 304 and system memory 306.

[0050] Depending on the desired configuration, processor 304 can be any type of processor, including, but not limited to, a microprocessor (μP), a microcontroller (μC), a digital signal processing unit (DSP), or any combination thereof. Processor 304 can include one or more levels of cache, such as a level 1 cache 310 and a level 2 cache 312, a processor core 314, and registers 316. An example processor core 314 can include an arithmetic logic unit (ALU), a floating point unit (FPU), a digital signal processing core (DSP core), or any combination thereof. An example memory controller 318 can be used with processor 304, or in some implementations, memory controller 318 can be an internal part of processor 304.

[0051] Depending on the desired configuration, system memory 306 can be any type of memory, including but not limited to volatile memory (such as RAM), non-volatile memory (such as ROM, flash memory, etc.), or any combination thereof. System memory 306 can include an operating system 320, one or more programs 322, and program data 324. In some embodiments, program 322 can be arranged to execute instructions on the operating system by one or more processors 304 using program data 324.

[0052] Computing device 300 may also include a storage interface bus 334 that facilitates communication from various storage devices 332 to basic configuration 302 via bus / interface controller 330. Storage devices 332 include, but are not limited to, removable storage 336 (such as CD / DVD), non-removable storage 338 (HDD), or any combination thereof.

[0053] The computing device 300 may also include an interface bus 340 that facilitates communication from various interface devices (e.g., output devices 342, peripheral interfaces 344, and communication devices 346) to the basic configuration 302 via the bus / interface controller 330. Example output devices 342 include a graphics processing unit 348 and an audio processing unit 350. These can be configured to facilitate communication with various external devices such as a display or speakers via one or more A / V ports 352. Example peripheral interfaces 344 may include a serial interface controller 354 and a parallel interface controller 356, which can be configured to facilitate communication with external devices such as input devices (e.g., a keyboard, mouse, pen, voice input device, touch input device) or other peripherals (e.g., a printer, scanner, etc.) via one or more I / O ports 358. Example communication devices 346 may include a network controller 360, which can be arranged to facilitate communication with one or more other computing devices 362 via a network communication link via one or more communication ports 364.

[0054] A network communication link can be an example of a communication medium. Communication media can generally be embodied as computer-readable instructions, data structures, program modules in a modulated data signal such as a carrier wave or other transmission mechanism, and can include any information delivery medium. A "modulated data signal" can be a signal in which one or more of a data set or a change thereof can be performed in a manner that encodes information in the signal. As non-limiting examples, communication media can include wired media such as a wired network or a dedicated line network, and various wireless media such as sound, radio frequency (RF), microwave, infrared (IR) or other wireless media. The term computer-readable medium as used herein can include both storage media and communication media.

[0055] The computing device 300 can be implemented as a server, such as a database server, an application server, a web server, etc., or as a personal computer including a desktop computer and a notebook computer. Of course, the computing device 300 can also be implemented as a part of a small-sized portable (or mobile) electronic device.

[0056] In an embodiment according to the present invention, computing device 300 is implemented as a query optimization apparatus 700 / 800 located in front-end server 120 and / or computing server 140, and is configured to execute query optimization method 400 / 600 according to an embodiment of the present invention. The program 322 of computing device 300 includes multiple instructions for executing query optimization method 400 / 600 according to an embodiment of the present invention, and program data 224 may also store configuration information of data query system 100, etc.

[0057] Figure 4 FIG2 is a flowchart of a query optimization method 400 according to an embodiment of the present invention. The query optimization method 400 is suitable for being executed in a query optimization apparatus 700.

[0058] like Figure 4 As shown, the query optimization method 400 begins at step S410. In step S410, an execution plan corresponding to a query statement is received. As described above, the query statement may be an SQL query statement. The execution plan is generated based on the query statement and may include multiple operation nodes.

[0059] Then, in step S420, a target computing node in the received execution plan that meets the optimization criteria is determined. In some embodiments, the optimization criteria may include: the computing node is suitable for computing data in multiple columns. In other words, if the computing node is suitable for computing data in multiple columns, then the computing node is the target computing node that meets the optimization criteria.

[0060] Specifically, the target operation node can be determined by traversing the operation nodes in the execution plan. For each traversed operation node, a determination is made as to whether the operation node is a target operation node that meets the optimization criteria. If the operation node is a target operation node, the data of multiple columns calculated by the target operation node can be recorded.

[0061] In practice, computational logic such as joins and aggregations involves computing data across multiple columns, so the target computation node can be the computation node corresponding to these computational logics. Therefore, in one embodiment, it is possible to directly determine whether a computation node is a computation node that performs joins or aggregations. If so, the computation node is the target computation node.

[0062] Of course, in another implementation, the target operation node may be determined by judging whether the operation node is another appropriate operation node that performs calculations on data of multiple columns, and the embodiment of the present invention does not impose any limitation on this.

[0063] After determining the target operation node in the execution plan, in step S430 , based on the target operation node, the target path included in the execution plan and the target data associated with the target path may be determined.

[0064] Specifically, a target path and target data associated with the target path can be determined based on each target operation node in the execution plan and the data in multiple columns calculated by each target operation node. The determined target path can include at least one target operation node in the execution plan, and the data in multiple columns calculated by at least some of the target operation nodes in the target path are the target data associated with the target path.

[0065] In some embodiments, the target path may include multiple consecutive target operation nodes in the execution plan. For example, if the execution plan includes multiple consecutive target operation nodes, the longest path in the execution plan including the multiple consecutive target operation nodes may be determined to be the target path. The multiple columns of data commonly calculated by the multiple target operation nodes in the target path may be determined to be the target data associated with the target path, that is, the intersection of the multiple columns of data respectively calculated by the multiple target operation nodes in the target path.

[0066] In other embodiments, the target path may include multiple discontinuous target computing nodes in the execution plan. The target data associated with the target path is the data of multiple columns calculated by the discontinuous target computing nodes in the target path, and does not include the data calculated by computing nodes located between the discontinuous target computing nodes in the target path. In other words, after removing the data calculated by computing nodes located between the discontinuous target computing nodes in the target path, the intersection of the data of multiple columns calculated by the multiple target computing nodes in the target path is the target data.

[0067] After determining the target path and target data, the target path may be optimized in step S440 to convert the storage format of the target data associated with the target path. As described above, the storage format may include a row storage format and a column storage format.

[0068] Typically, corresponding conversion nodes may be added at both ends of the target path to convert the storage format of the target data from a column storage format to a row storage format, and / or from a row storage format to a column storage format.

[0069] In some embodiments, if the target data is stored in a column storage format (e.g., in an OLAP scenario), a column-row conversion node may be added at the input end of the target path, and a row-column conversion node may be added at the output end of the target path. The column-row conversion node may convert the storage format of the target data associated with the target path from a column storage format to a row storage format, and the row-column conversion node may convert the storage format of the target data from a row storage format back to a column storage format.

[0070] In other embodiments, if the target data is stored in a row-store format (e.g., in an OLTP scenario), a row-column conversion node may be added at the input end of the target path, and a column-row conversion node may be added at the output end of the target path. The column-row conversion node may convert the storage format of the target data associated with the target path from a column-store format to a row-store format, and the row-column conversion node may convert the storage format of the target data from a row-store format back to a column-store format.

[0071] If the target path includes multiple input terminals, a column-row conversion node or a row-column conversion node may be added to the input terminal associated with the target data. Similarly, if the target path includes multiple output terminals, a row-column conversion node or a column-row conversion node may be added to the output terminal associated with the target data.

[0072] Furthermore, according to one embodiment of the present invention, before optimizing the target path, a determination can be made based on the amount of target data. For example, if the amount of target data is less than a predetermined threshold, the performance improvement resulting from optimization can be considered minimal, and thus the target path is not optimized. However, if the amount of target data is not less than the predetermined threshold, the target path can be optimized. The value of the predetermined threshold can be set based on actual circumstances and is not limited in this embodiment of the present invention.

[0073] After the execution plan is optimized, according to one embodiment of the present invention, the optimized execution plan may be executed, that is, the execution plan including the optimized target path may be executed.

[0074] For ease of understanding, the following Figure 5A and Figure 5B A query optimization method according to an embodiment of the present invention is described.

[0075] Figure 5A FIG. 5 is a schematic diagram showing an execution plan 500a according to an embodiment of the present invention. Figure 5B A schematic diagram of an execution plan 500b according to an embodiment of the present invention is shown. The execution plan 500b is obtained by optimizing the execution plan 500a using the query optimization method according to an embodiment of the present invention.

[0076] in, Figure 5A The execution plan 500a shown is generated based on the following SQL query statement:

[0077]

[0078]

[0079] like Figure 5A As shown, the execution plan 500 a includes the following operation nodes: a join node 520 , a project node 540 , and a hash aggregation node 560 .

[0080] Join node 520 is used to join data based on a join key. Its input includes a build table and a probe table. The build table is typically a smaller table used to construct a HashMap using the join key. The probe table is typically a larger table used to perform matching within the constructed hash table.

[0081] like Figure 5A As shown, the build table includes data from the following columns in the store_sales table: ss_customer_sk, ss_ext_list_price, and ss_ext_discount_amt. The probe table includes data from the following columns in the customer table: c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address, and c_customer_sk. Join node 520 calculates the data in the build and customer tables and outputs the data from the following seven columns that satisfy c_customer_sk = ss_customer_sk to project node 540: c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, and c_email_address.

[0082] The project node 540 is suitable for projecting data. Figure 5AAs shown, the project node 540 calculates the two columns of data ss_ext_list_price and ss_ext_discount_amt (i.e., calculates ss_ext_list_price-ss_ext_discount_amt), and outputs the calculation result sum_0 and the following 7 columns of data (these 7 columns of data are not calculated) to the hash aggregation node 560: c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, c_email_address.

[0083] The hash aggregation node 560 is suitable for aggregating data. Figure 5A As shown, hash aggregation node 560 calculates sum_0 for the received seven columns of data, thereby grouping the seven columns of data and sum_0 according to the seven columns (i.e., using the seven columns as group keys). Next, hash aggregation node 560 performs a sum calculation within the grouped data (i.e., summing sum_0) and outputs the summed seven columns of data and sum_0 (i.e., sum(sum_0)).

[0084] Assuming that the data of the above multiple columns are stored in a column storage format, the discontinuous storage will result in low query memory access efficiency and reduced query performance. Therefore, the query optimization method according to an embodiment of the present invention can be used to optimize the execution plan 500a.

[0085] When the target operation node is determined based on whether the operation node is suitable for association or aggregation calculation, the discontinuous join node 520 and hash aggregation node 560 in execution plan 500a can be determined as the target operation node. Furthermore, based on the above description, the data involved in both the calculation of join node 520 and the calculation of hash aggregation node 560 can be determined to be the following seven columns: c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, and c_email_address.

[0086] Since the data in these seven columns does not participate in the calculation of the project node 540 but is directly output, it can be determined that the path including the join node 520, the project node 540 and the hash aggregation node 560 is the target path, and c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, and c_email_address are the target data associated with the target path.

[0087] A column-row conversion node 510 can be added to the input end of the target path, and a row-column conversion node 570 can be added to the output end of the target path, so as to obtain the following: Figure 5B The optimized execution plan 500b is shown. Since the target path has multiple input terminals (ie, build tables and probe tables), a column-row conversion node 510 is added to the input terminal associated with the target data (ie, the build table for storing the target data).

[0088] The column-row conversion node 510 can convert the target data's storage format from a column-store format to a row-store format, i.e., converting c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, and c_email_address, which are stored in a column-store format, to a row-store format, for example, by concatenating these seven columns into a single column, called combined_cols. The row-column conversion node 570 can convert the target data's storage format from a row-store format to a column-store format, i.e., restoring c_customer_id, c_first_name, c_last_name, c_preferred_cust_flag, c_birth_country, c_login, and c_email_address, which are stored in a row-store format, to a column-store format, for example, by splitting the single column, called combined_cols, obtained by the concatenation, into seven columns.

[0089] This optimization method only changes the storage format of some columns, while other columns retain the original storage format. It takes into account the advantages of both row storage format and column storage format. While improving memory access efficiency and optimizing query performance, it does not affect the calculation logic of the original execution plan. The transformation cost is low, and only the corresponding conversion nodes need to be added.

[0090] Figure 6A flowchart of a query optimization method 600 according to an embodiment of the present invention is shown. The query optimization method is suitable for execution in a query optimization device 800 .

[0091] like Figure 6 As shown, the query optimization method 600 begins at step S610. In step S610, a query statement is received and an execution plan corresponding to the query statement is generated. The execution plan may include multiple operation nodes.

[0092] Then, in step S620 , a target operation node that meets the optimization condition in the execution plan may be determined. In step S630 , based on the target operation node, a target path included in the execution plan and target data associated with the target path may be determined.

[0093] Then, in step S640, the target path is optimized to convert the storage format of the target data associated with the target path. Finally, in step S650, the optimized execution plan (including the optimized target path) can be sent to the computing server 140 so that the computing server 140 executes the execution plan.

[0094] The above combined Figure 1 The corresponding processing in each step of the query optimization method 600 has been explained in detail in the specific description of the data query system 100 and the query optimization method 400 shown in FIG5 , and the repeated content will not be repeated here.

[0095] Figure 7 FIG. 7 is a schematic diagram showing a query optimization device 700 according to an embodiment of the present invention. Figure 7 As shown, the query optimization apparatus 700 may include a plan receiving module 720 , a target determination module 740 , and a plan optimization module 760 .

[0096] Plan receiving module 720 is adapted to receive an execution plan, which is generated based on a query statement and includes multiple operation nodes. Target determination module 740 is connected to plan receiving module 720 and is adapted to determine target operation nodes in the execution plan that meet optimization criteria. It is also adapted to determine, based on the target operation nodes, the target paths included in the execution plan and the target data associated with the target paths. Plan optimization module 760 is connected to target determination module 740 and is adapted to optimize the target paths in order to convert the storage format of the target data.

[0097] In some embodiments, the query optimization apparatus 700 may further include a plan execution module 770. The plan execution module 770 is connected to the plan optimization module 760 and is adapted to execute the optimized execution plan, ie, the execution plan including the optimized target path.

[0098] The above combined Figures 1 to 6 The corresponding processing in each module of the query optimization device 700 has been explained in detail in the specific description of the data query system 100 and the query optimization method 400, and the repeated content will not be repeated here.

[0099] Figure 8 FIG. 8 is a schematic diagram showing a query optimization apparatus 800 according to an embodiment of the present invention. Figure 8 As shown, the query optimization apparatus 800 may include a query receiving module 810 , a plan generating module 830 , a target determining module 850 , a plan optimizing module 870 and a plan sending module 890 .

[0100] The query receiving module 810 is suitable for receiving query statements, typically SQL query statements. The plan generating module 830 is connected to the query receiving module 810 and is suitable for generating an execution plan corresponding to the query statement, and the execution plan includes multiple operation nodes. The target determination module 850 is connected to the plan generating module 830 and is suitable for determining the target operation node that meets the optimization conditions in the execution plan, and is also suitable for determining the target path and target data associated with the target path contained in the execution plan based on the target operation node. The plan optimization module 870 is connected to the target determination module 850 and is suitable for optimizing the target path so as to convert the storage format of the target data. The plan sending module 890 is connected to the plan optimization module 870 and is suitable for sending the optimized execution plan, that is, the execution plan including the optimized target path, to the computing server 140.

[0101] In some embodiments, the query optimization apparatus 800 may further include a plan splitting module 840. The plan splitting module 840 is connected to the plan generation module 830 and the target determination module 850, respectively, and is adapted to split the execution plan generated by the plan generation module 830 into a plurality of segmented execution plans. The target determination module 850 may determine the target path contained in the segmented execution plan and the target data associated with the target path. The plan optimization module 870 may optimize the target path contained in the segmented execution plan. The plan sending module 890 may send the optimized segmented execution plan to the computing server 140.

[0102] The above combined Figures 1 to 7 The corresponding processing in each module of the query optimization device 800 has been explained in detail in the specific description of the data query system 100 and the query optimization method 600, and the repeated content will not be repeated here.

[0103] In summary, the query optimization solution according to an embodiment of the present invention determines the target path and target data based on the operation nodes in the execution plan, and changes and restores the storage format of the target data by optimizing the target path. This improves memory access efficiency and optimizes query performance while not affecting the computational logic of the original execution plan. Since only the storage format of the target data is changed and the storage format of the remaining data is retained, the advantages of both the row storage format and the column storage format can be taken into account, thereby further optimizing query performance. In addition, the query optimization solution according to an embodiment of the present invention converts the storage format by adding corresponding conversion nodes to the target path, which has low modification costs and is easy to implement.

[0104] It should be understood that the various techniques described herein can be implemented in conjunction with hardware or software, or a combination thereof. Thus, the methods and apparatus of the present invention, or certain aspects or portions of the methods and apparatus of the present invention, can take the form of program code (i.e., instructions) embedded in a tangible medium, such as a floppy disk, CD-ROM, hard drive, or any other machine-readable storage medium, wherein when the program is loaded into a machine such as a computer and executed by the machine, the machine becomes an apparatus for practicing the present invention.

[0105] When program code is executed on a programmable computer, the computing device generally includes a processor, a storage medium readable by the processor (including volatile and non-volatile memory and / or storage elements), at least one input device, and at least one output device. The memory is configured to store the program code; and the processor is configured to execute the various methods of the present invention according to the instructions in the program code stored in the memory.

[0106] By way of example and not limitation, computer-readable media include computer storage media and communication media. Computer-readable media include computer storage media and communication media. Computer storage media stores information such as computer-readable instructions, data structures, program modules, or other data. Communication media generally embodies computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transport mechanism, and includes any information delivery media. Combinations of any of the above are also included within the scope of computer-readable media.

[0107] It should be understood that in order to streamline the present disclosure and aid in understanding one or more of the various inventive aspects, in the above description of exemplary embodiments of the invention, various features of the invention are sometimes grouped together into a single embodiment, figure, or description thereof. However, this disclosed method should not be interpreted as reflecting an intention that the claimed invention requires more features than are expressly recited in each claim. Rather, as reflected in the claims below, inventive aspects lie in less than all the features of the individual embodiments disclosed above. Accordingly, the claims following the detailed description are hereby expressly incorporated into this detailed description, with each claim standing on its own as a separate embodiment of the invention.

[0108] Those skilled in the art will appreciate that the modules, units, or components of the devices in the examples disclosed herein may be arranged in the device described in the embodiment, or alternatively may be located in one or more devices different from the devices in the examples. The modules in the foregoing examples may be combined into one module or further divided into multiple submodules.

[0109] Those skilled in the art will appreciate that the modules in the devices in the embodiments may be adaptively changed and arranged in one or more devices different from the embodiments. The modules or units or components in the embodiments may be combined into one module or unit or component, and in addition may be divided into multiple submodules or subunits or subcomponents. All features disclosed in this specification (including the accompanying claims, abstracts and drawings) and all processes or units of any method or device disclosed herein may be combined in any combination, except that at least some of such features and / or processes or units are mutually exclusive. Unless expressly stated otherwise, each feature disclosed in this specification (including the accompanying claims, abstracts and drawings) may be replaced by an alternative feature providing the same, equivalent or similar purpose.

[0110] Furthermore, those skilled in the art will appreciate that although some embodiments described herein include certain features included in other embodiments but not other features, combinations of features from different embodiments are intended to be within the scope of the present invention and to form different embodiments. For example, in the claims below, any of the claimed embodiments may be used in any combination.

[0111] In addition, some of the embodiments are described herein as methods or combinations of method elements that can be implemented by a processor of a computer system or by other devices that perform the functions described. Thus, a processor having the necessary instructions for implementing the method or method element forms a device for implementing the method or method element. Furthermore, the elements described herein of the device embodiments are examples of devices for implementing the functions performed by the elements for the purpose of implementing the invention.

[0112] As used herein, unless otherwise specified, the use of ordinal numbers "first," "second," "third," etc. to describe common objects merely indicates that different instances of similar objects are involved and are not intended to imply that the objects so described must have a given order in time, space, ranking, or in any other manner.

[0113] Although the present invention has been described with respect to a limited number of embodiments, it will be apparent to those skilled in the art, having benefit of the foregoing description, that other embodiments are contemplated within the scope of the invention thus described. Furthermore, it should be noted that the language used in this specification has been selected primarily for readability and didactic purposes, rather than for the purpose of explaining or limiting the subject matter of the present invention. Consequently, many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the appended claims. The disclosure of the present invention is intended to be illustrative rather than restrictive of the scope of the invention, which is defined by the appended claims.

Claims

1. A query optimization method, comprising: receiving an execution plan, where the execution plan is generated based on a query statement and includes a plurality of computing nodes; Determining a target computing node in the execution plan that meets optimization conditions; Determining, based on the target computing node, a target path included in the execution plan and target data associated with the target path; as well as Optimizing the target path to convert the storage format of the target data; The storage format includes a row storage format and a column storage format, and the optimizing the target path includes: Corresponding conversion nodes are added at both ends of the target path respectively, so as to convert the target data from a column storage format to a row storage format, and / or from a row storage format to a column storage format.

2. The method according to claim 1, wherein The optimization conditions include: The operation node is suitable for performing calculations on data in multiple columns.

3. The method according to claim 2, wherein: The operation node is suitable for performing calculations on data of multiple columns, including: the operation node is suitable for performing association calculations or aggregation calculations.

4. The method according to claim 1, wherein The determining, based on the target computing node, a target path included in the execution plan and target data associated with the target path includes: Based on each target operation node in the execution plan and the data of multiple columns calculated by each target operation node, the target path and the target data associated with the target path are determined, the target path includes at least one target operation node, and the target data is the data of multiple columns jointly calculated by at least some of the target operation nodes in the target path.

5. The method according to claim 4, wherein: The target path includes a plurality of consecutive target operation nodes in the execution plan, and the target data is data of a plurality of columns processed together by the plurality of consecutive target operation nodes in the target path.

6. The method of claim 4, wherein: The target path includes multiple discontinuous target operation nodes in the execution plan, and the target data is data of multiple columns jointly calculated by the multiple discontinuous target operation nodes in the target path, and is not data calculated by the operation nodes located between the multiple discontinuous target operation nodes in the target path.

7. The method of claim 1, wherein: The target data is stored in a column storage format, and the adding of corresponding conversion nodes at both ends of the target path includes: Adding a column-row conversion node at the input end of the target path to convert the target data into a row storage format; A row-column conversion node is added at the output end of the target path to convert the target data into a column storage format.

8. The method of claim 1, wherein: The target data is stored in a row storage format, and the corresponding conversion nodes are added at both ends of the target path, including: Adding a row-column conversion node at the input end of the target path to convert the target data into a column storage format; A column-row conversion node is added at the output end of the target path to convert the target data into a row storage format.

9. The method of claim 1 , further comprising: Before optimizing the target path, whether to optimize the target path is determined based on the data volume of the target data.

10. The method according to any one of claims 1 to 9, further comprising: Execute the optimized execution plan.

11. A query optimization method, comprising: Receive a query statement and generate an execution plan corresponding to the query statement, wherein the execution plan includes multiple operation nodes; Determining a target computing node in the execution plan that meets optimization conditions; Determining, based on the target computing node, a target path included in the execution plan and target data associated with the target path; Optimizing the target path to convert the storage format of the target data; as well as Send the optimized execution plan to the computing server; The storage format includes a row storage format and a column storage format, and the optimizing the target path includes: Corresponding conversion nodes are added at both ends of the target path respectively, so as to convert the target data from a column storage format to a row storage format, and / or from a row storage format to a column storage format.

12. A query optimization device comprising: a plan receiving module adapted to receive an execution plan, wherein the execution plan is generated based on a query statement and includes a plurality of operation nodes; A target determination module, adapted to determine a target operation node in the execution plan that meets optimization conditions; Determining, based on the target computing node, a target path included in the execution plan and target data associated with the target path; as well as a plan optimization module, adapted to optimize the target path so as to convert the storage format of the target data; The storage format includes a row storage format and a column storage format. The plan optimization module is further adapted to add corresponding conversion nodes at both ends of the target path, respectively, so as to convert the target data from the column storage format to the row storage format, and / or from the row storage format to the column storage format.

13. A query optimization device comprising: A query receiving module, adapted to receive query statements; A plan generation module, adapted to generate an execution plan corresponding to the query statement, the execution plan including a plurality of operation nodes; A target determination module, adapted to determine a target operation node in the execution plan that meets optimization conditions; Determining, based on the target computing node, a target path included in the execution plan and target data associated with the target path; a plan optimization module, adapted to optimize the target path so as to convert the storage format of the target data; as well as A plan sending module, adapted to send the optimized execution plan to the computing server; The storage format includes a row storage format and a column storage format. The plan optimization module is further adapted to add corresponding conversion nodes at both ends of the target path, respectively, so as to convert the target data from the column storage format to the row storage format, and / or from the row storage format to the column storage format.

14. A data query system comprising: A front-end server, adapted to receive query statements; Generate an execution plan corresponding to the query statement and send the execution plan to the computing server; a computing server comprising the query optimization apparatus according to claim 12, wherein the computing server is adapted to execute the optimized execution plan so as to operate on the data stored in the storage server; Returning the query result obtained by executing the execution plan to the front-end server; as well as Storage servers are suitable for storing data.

15. A data query system comprising: A front-end server comprising the query optimization device according to claim 13; A computing server adapted to execute the optimized execution plan so as to operate on the data stored in the storage server; Returning the query result obtained by executing the execution plan to the front-end server; as well as Storage servers are suitable for storing data.

16. A computing device comprising: one or more processors; Memory; as well as One or more programs, wherein the one or more programs are stored in the memory and configured to be executed by the one or more processors, the one or more programs comprising instructions for executing the query optimization method according to any one of claims 1 to 11. 17 . A readable storage medium storing a program, the program comprising instructions, which, when executed by a computing device, cause the computing device to execute the query optimization method according to claim 1 .

Images