Data query method and device, equipment, storage medium, program product and database management system

Abstract

The invention provides a data query method and device, equipment, a storage medium, a program product and a database management system. The method comprises the steps of obtaining a target query statement; the target query statement is a query statement with two connected tables. And if the target query statement adopts the Hash join algorithm, determining a target query execution path of the target query statement based on the difference between the estimated row numbers of the first table and the second table in the target query statement. And executing the target query statement based on the target query execution path. According to the method provided by the invention, the occupation of a memory during the connection operation of the two tables is reduced, and the query efficiency is improved.

Description

Technical Field

[0001] The present application relates to data query technology, and in particular to a data query method, device, equipment, storage medium, program product and database management system. Background Art

[0002] A database management system is a software system used to create, manage, and operate a database. The system can implement two-table join operations through a hash join algorithm. The hash join algorithm optimizes memory usage and improves join performance by reading the smaller of the two tables into memory as a build table to build a hash table, and the larger table as a probe table to find matching rows in the hash table.

[0003] Database management systems use cost-based query optimizers to determine the build table and probe table in two-table join operations. However, the query optimizer mainly relies on the cost factor when estimating the cost of joining two tables. When there is an error in the calculation of the cost factor, the query optimizer may mistakenly select the large table as the build table and the small table as the probe table, thereby increasing memory usage and reducing query efficiency. Summary of the invention

[0004] The present application provides a data query method, apparatus, device, storage medium, program product and database management system to solve the problem that when the cost factor is calculated incorrectly, the cost-based query optimizer incorrectly selects a large table as a construction table and a small table as a detection table, thereby increasing memory usage and reducing query efficiency.

[0005] In one aspect, the present application provides a data query method, the method comprising:

[0006] Obtain a target query statement; the target query statement is a query statement that connects two tables;

[0007] If the target query statement adopts a hash join algorithm, determining a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement;

[0008] Based on the target query execution path, the target query statement is executed.

[0009] Optionally, before determining the target query execution path based on the difference between the estimated number of rows of the first table and the second table in the target query statement, the method further includes:

[0010] Generate multiple execution paths of the target query statement;

[0011] estimating the cost of each execution path, and determining an optimal query execution path based on the estimated cost of the execution path; the connection order of two tables in the optimal query execution path is used to indicate the construction table and the detection table;

[0012] The construction table indicated by the connection order of the two tables is used as the first table, and the detection table is used as the second table.

[0013] Optionally, determining the target query execution path based on the difference between the estimated number of rows of the first table and the second table in the target query statement includes:

[0014] Determine whether the estimated number of rows in the first table is less than a first product; the first product is the product of the number of rows in the second table and a row number difference factor;

[0015] If so, the target query execution path is determined to be the optimal query execution path.

[0016] Optionally, determining the target query execution path between the first table and the second table based on the difference between the estimated number of rows of the first table and the second table in the target query statement includes:

[0017] If the estimated number of rows of the first table is greater than the first product, exchanging the connection order of the two tables in the optimal query execution path so as to use the second table as a construction table and the first table as a detection table;

[0018] Determine whether the estimated number of rows in the second table is less than a second product; the second product is the product of the number of rows in the first table and a row number difference factor;

[0019] If so, it is determined that the target query execution path is a query execution path in which the connection order of the two tables of the optimal query execution path is exchanged.

[0020] Optionally, the method further comprises:

[0021] If the estimated number of rows in the second table is greater than the second product, the optimal query execution path is used as the target query execution path.

[0022] Optionally, the method further comprises:

[0023] The row number difference factor is determined based on sizes of the first table and the second table.

[0024] Optionally, after obtaining the query statement, the method further includes:

[0025] Determine the optimal connection algorithm for the target query statement according to the cost;

[0026] If the optimal connection algorithm is a hash connection algorithm, it is determined that the target query statement adopts the hash connection algorithm.

[0027] In a second aspect, the present application provides a data query device, the device comprising:

[0028] An acquisition module, used to acquire a target query statement; the target query statement is a query statement connecting two tables;

[0029] A determination module, if the target query statement adopts a hash join algorithm, determines a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement;

[0030] A processing module is used to execute the target query statement based on the target query execution path.

[0031] In a third aspect, the present application provides an electronic device, the electronic device comprising: a processor, and a memory communicatively connected to the processor;

[0032] The memory stores computer-executable instructions;

[0033] The processor executes the computer-executable instructions stored in the memory to implement the method as described in any one of the first aspects.

[0034] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method as described in any one of the first aspects.

[0035] In a fifth aspect, the present application provides a computer program product, including a computer program, which implements the method described in any one of the first aspects when executed by a processor.

[0036] In a sixth aspect, the present application provides a database management system, the database management system comprising: a query optimizer and a query processor;

[0037] The query processor is used to obtain a target query statement; the target query statement is a query statement connecting two tables;

[0038] The query optimizer is used to determine the target query execution path of the target query statement by adopting the method as described in any one of the first aspects;

[0039] The query processor is further configured to execute the target query statement based on the target query execution path.

[0040] The data query method, device, equipment, storage medium, program product and database management system provided by the present application optimize the way in which the cost-based query optimizer determines the construction table and the detection table, and introduces a feature of comparing the difference in the estimated number of rows of the two tables. By comparing the difference between the estimated number of rows of the two tables, the target query execution path is determined and the target query statement is executed. When executing the connection operation between the two tables, the cost-based query optimizer can select the small table as the construction table and the large table as the detection table. This reduces memory usage and improves query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0042] Figure 1 A flowchart of a data query method provided in an embodiment of the present application;

[0043] Figure 2 A flowchart of a method for determining a target query execution path provided in an embodiment of the present application;

[0044] Figure 3 A flowchart of a data query method provided in an embodiment of the present application;

[0045] Figure 4 A schematic diagram of the structure of a data query device provided in an embodiment of the present application;

[0046] Figure 5 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0047] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0048] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0049] Taking the database management system as an example, the terms involved in this application are explained below:

[0050] Two-table join: The database management system combines two different data tables into one result set through common fields, thereby achieving simultaneous retrieval and analysis of related data.

[0051] Hash join algorithm: also known as hash join algorithm, is an algorithm for performing database query operations. It builds a hash table for the construction table and uses the hash table to quickly match the connection keys in the detection table to efficiently perform the connection operation of the two tables. For the sake of ease of expression, the embodiments of this application are all described using the hash join algorithm.

[0052] Build table: One of the tables in the two-table connection, which is used to create a hash table.

[0053] Probe table: One of the tables in a two-table join that is used to find matching rows in the hash table.

[0054] Hash table: A data structure used to store rows in a built table and allow fast lookups. A hash table maps join keys to buckets in the table using a hash function.

[0055] Join Keys: Fields used to join two tables. These fields exist in both tables and contain matching data values, which are used to determine which rows should be joined together.

[0056] Hash value: A fixed-size output value generated by a hash function after processing the input data, which is used for fast data retrieval, verifying data integrity, encryption, deduplication and load balancing, etc.

[0057] Hash function: The function used to convert the join key into a hash value.

[0058] Bucket: A storage unit in a hash table. The hash join algorithm needs to estimate the number of buckets required to ensure that the hash table is sized appropriately.

[0059] Cost-based query optimizer: A database optimizer that estimates the costs of different query execution paths by calculating cost factors to select the optimal query execution path.

[0060] Cost factor: Cost factor is a parameter used by cost-based query optimizers to quantify the cost of different query execution paths, including processor processing time, memory usage, number of input / output operations, etc. It is calculated based on multiple factors such as statistical information of the two tables, hardware performance parameters, data access patterns, query structure, etc.

[0061] Cost: Also known as cost, cost is an estimated value of total resource consumption required for a query operation calculated based on cost factors, including processor cost, memory cost, input / output cost, etc. For ease of description, the embodiments of the present application are all described in terms of cost.

[0062] Statistical information: Statistical data about data and objects in the database stored in the database management system, such as the number of rows in the table, the cardinality of the column, the selectivity of the index, the maximum and minimum values ​​of the column, histograms, storage occupancy of tables and indexes, etc.

[0063] Hardware performance parameters: refers to a series of indicators that describe the performance characteristics of computer hardware components, including processor processing speed, number of cores, memory capacity and speed, storage device read and write speed, etc. These parameters directly affect the computer's ability and efficiency to process data and determine the performance of the hardware when performing various tasks.

[0064] Data access mode: refers to the data retrieval and operation mode used in the database management system, including full table scan, index search, range query, join operation, update and delete, etc. These modes directly affect the performance and response speed of the database management system. Different access modes will result in different input / output operations, processor processing and resource consumption.

[0065] Query structure: refers to the organization and logical relationship of each component in the database management system query, including the arrangement order and combination of table connection methods, condition filtering, subqueries, grouping aggregation, and sorting operations. These structures determine the execution path and efficiency of the query.

[0066] The database management system includes a query optimizer and a query processor. The query processor is used to receive and parse the user's query statements, convert the query statements into operation instructions that the database can understand, and retrieve or modify data according to the generated query execution path.

[0067] The query optimizer is responsible for generating and selecting the lowest cost execution path for executing Structured Query Language (SQL) queries. The query optimizer may be, for example, a cost-based query optimizer or any other optimizer that can generate and select the lowest cost execution path for executing SQL queries.

[0068] The two-table join operation is a basic operation in Structured Query Language (SQL) used to merge related data in two tables. This operation connects two tables through one or more common fields, namely the join key. The result of joining two tables is a new result set containing matching data rows from the two original tables. This result set is usually used to perform further data analysis or reporting.

[0069] There are many join algorithms in the database management system that can perform two-table join operations, including hash join algorithm, nested loop join algorithm, index merge join algorithm, sort-merge join algorithm, etc. Each algorithm has specific performance characteristics.

[0070] The cost-based query optimizer can calculate the cost factors of the query execution paths of different connection algorithms by analyzing multiple factors such as statistical information in the two tables, hardware performance parameters, data access patterns, query structures, etc., and select the algorithm with the lowest estimated cost as the execution algorithm for the two-table connection operation to efficiently execute the two-table connection operation.

[0071] When performing a two-table join operation using the hash join algorithm, the cost-based query optimizer analyzes multiple factors such as the statistical information of the two tables, hardware performance parameters, data access patterns, query structures, etc. to estimate the cost factors of different query execution paths in the two-table join operation, and then estimates the resource consumption of processor processing time, memory usage, input / output (I / O) operation times, etc. of different query execution paths. Based on these estimated cost factors, the cost-based query optimizer selects the query execution path with the lowest cost as the final query execution path for the two-table join operation, that is, selects one of the two tables as the build table and the other table as the probe table.

[0072] The query processor then executes the query statement based on the query execution path. That is, the hash function is applied to the join columns in the build table, the hash values ​​are calculated, and the records are stored in the hash table. Then, the full table scan is performed on the probe table, the data is read row by row, the same hash function is applied to the join keys in the probe table, the hash values ​​are calculated, and the hash table is checked for matching hash values. When a match is found, the corresponding record combination is output as the join result.

[0073] When the hash join algorithm is used to join two tables, the query processor will read the build table completely into memory to build the hash table. Therefore, in order to optimize memory usage, reduce input / output operations, improve hash table lookup efficiency and overall join performance. Therefore, when determining the query execution path, the cost-based query optimizer usually selects the small table as the build table and the large table as the probe table.

[0074] Among them, "small table" and "large table" are relative concepts. The table with smaller data volume, fewer rows, and smaller storage space is usually called a small table; and the table with larger data volume, more rows, and larger storage space is usually called a large table.

[0075] The above data query method can save memory space to a certain extent and improve the efficiency of the connection. However, as mentioned above, the cost-based query optimizer will comprehensively analyze the statistical information of the two tables, hardware performance parameters, data access mode, query structure and other factors to estimate the cost factor. Therefore, the accuracy of the cost factor is affected by multiple factors such as statistical information, hardware performance parameters, data access mode, query structure, etc. If there are errors in some of these factors, the cost factor calculation may be incorrect, thereby affecting the cost calculation. The cost-based query optimizer may mistakenly select the large table as the construction table and the small table as the detection table, thereby increasing memory usage and reducing query efficiency.

[0076] In view of this, the present application proposes a data query method, which optimizes the way in which a cost-based query optimizer determines the construction table and the detection table, and introduces a feature of comparing the difference in the estimated number of rows of the two tables. When there is an error in the calculation of the cost factor, the target query execution path can be determined by comparing the difference between the estimated number of rows of the two tables, and the target query statement can be executed. By considering the difference between the estimated number of rows of the two tables, this method enables the cost-based query optimizer to still choose a small table as the construction table and a large table as the detection table as much as possible when there is an error in the calculation of the cost factor. Thereby reducing memory usage and improving query efficiency.

[0077] For the convenience of description in the following embodiments, the cost-based query optimizer is referred to as a query optimizer.

[0078] It should be noted that the above Figure 1 The data query method of the database management system shown is only an illustration, and the present application does not limit the components of the database management system, the naming of the components, and the coordinated operation mode between the components.

[0079] In one embodiment, the database management system can be deployed entirely in a cloud environment. For example, if the computing resources included in the cloud environment are servers running virtual machines, the database management system can be independently deployed on a server or virtual machine in the cloud environment, or the database management system can be distributedly deployed on multiple servers in the cloud environment, or distributedly deployed on multiple virtual machines in the cloud environment, or distributedly deployed on servers and virtual machines in the cloud environment.

[0080] The database management system provided by the embodiment of the present application can also be deployed in different environments in a distributed manner. The database management system provided by the present application can be logically divided into multiple parts, each of which has different functions. The various parts in the database management system can be deployed in any two or three of the terminal computing device (located on the user side), the edge environment, and the cloud environment. The various parts of the database management system deployed in different environments or devices collaborate to realize the function of data processing. It should be understood that the embodiment of the present application does not restrictively divide which parts of the database management system are deployed in what environment. In actual application, it can be adaptively deployed according to the computing power of the terminal computing device, the resource occupancy of the edge environment and the cloud environment, or the specific application requirements.

[0081] The database management system can also be deployed separately on a computing device in any environment (for example, deployed separately on an edge server in an edge environment).

[0082] The method provided by the present application and how the method provided by the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0083] Figure 1 A flow chart of a data query method provided in an embodiment of the present application is shown as follows: Figure 2 As shown, this method is applied to Figure 1 In the database management system shown, the method comprises the following steps:

[0084] S101, obtaining a target query statement; the target query statement is a query statement for connecting two tables.

[0085] The target query statement is a query operation instruction in the structured query language, which is used to instruct the query processor to combine the relevant data of two tables to be connected through one or more common connection keys to generate a new result set.

[0086] The target query statement may be, for example, a SQL query statement or any other query statement that can realize the connection between two tables. The embodiment of the present application does not limit the implementation form of the target query statement.

[0087] S102: If the target query statement adopts a hash join algorithm, determine a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement.

[0088] The first table and the second table are two tables involved in the two-table join operation in the target query statement. For example, one of the two tables can be randomly designated as the first table and the other table as the second table. The first table and the second table in the two tables can also be determined in other ways. The embodiment of the present application does not limit the setting method of the first table and the second table.

[0089] The estimated number of rows is the query optimizer's estimate of the number of rows in each table based on statistics.

[0090] The target query execution path is the specific steps and sequence followed by the query processor when executing the join operation between two tables. The target query path determines the join order of the two tables.

[0091] In this embodiment, when the target query statement adopts the hash join algorithm, the target query execution path may be determined based on the difference between the estimated number of rows of the first table and the second table in the target query statement.

[0092] For example, the query optimizer may use the table with fewer estimated rows of the first and second tables as a construction table and use the other table as a probe table to obtain the target query execution path.

[0093] For another example, the query optimizer may determine the target query execution path based on the determination result of whether the ratio of the estimated number of rows of the first table to the estimated number of rows of the second table is less than a preset threshold. Exemplarily, if so, the target query execution path is determined to be a query execution path with the first table as the build table and the second table as the detection table. If not, the target query execution path is determined to be a query execution path with the second table as the build table and the first table as the detection table, etc.

[0094] For another example, the query optimizer may use the judgment results of the following formulas (1) and / or (2) to determine the target query execution path.

[0095] row(t1) <rows_factor*row(t2) (1)

[0096] Among them, the above row(t1) is the estimated number of rows in the first table, rows_factor is the row number difference factor, row(t2) is the estimated number of rows in the second table, and rows_factor*row(t2) is the first product.

[0097] row(t2) <rows_factor*row(t1) (2)

[0098] Among them, the above row(t2) is the estimated number of rows in the second table, rows_factor is the row number difference factor, row(t1) is the estimated number of rows in the first table, and rows_factor*row(t1) is the second product.

[0099] Exemplarily, the query optimizer may use formula (1) to determine whether the first table satisfies the condition shown in formula (1), that is, whether the estimated number of rows in the first table is less than the first product. If so, it indicates that the estimated number of rows in the first table is much smaller than the estimated number of rows in the second table. Then, the first table is used as a construction table, and the query execution path of the second table as a detection table is used as the target query execution path. If not, the second table is used as a construction table, and the query execution path of the first table as a detection table is used as the target query execution path.

[0100] Alternatively, the query optimizer can use formula (2) to determine whether the second table satisfies the condition shown in formula (2), that is, whether the estimated number of rows in the second table is less than the second product. If so, it indicates that the estimated number of rows in the second table is much smaller than the estimated number of rows in the first table, and the second table is used as a construction table and the first table is used as a detection table. If not, the first table is used as a construction table and the second table is used as a detection table.

[0101] Alternatively, the query optimizer may first use formula (1) to determine whether the first table satisfies the condition shown in formula (1). If so, the first table is used as the construction table and the second table is used as the detection table. If not, formula (2) is further used to determine whether the second table satisfies the condition shown in formula (2). If so, the second table is used as the construction table and the first table is used as the detection table. If not, any one of the tables may be used as the construction table and the other table may be used as the detection table. Alternatively, further determination may be made in combination with other determination conditions or the conclusion may be obtained by using other determination methods, etc., which are not limited to this.

[0102] It should be understood that the row number difference factor used in the above formulas (1) and (2) is a parameter used to measure the difference between the estimated row numbers of the two tables, and the value range may be, for example, [0, 1].

[0103] This parameter can be a preset value, or it can be dynamically adjusted based on the size of the first table and the second table. Exemplarily, a mapping relationship between the size range of the two tables and the row number difference factor can be preset. Therefore, the row number difference factor that meets the size can be found from the mapping relationship based on the size of the two tables, or it can be calculated through the functional relationship between the size of the two tables and the row number difference factor. The construction of the official function relationship can be set according to actual needs, or it can be fitted according to the size of the construction table and the detection table used for the historical connection of the two tables to obtain the function relationship.

[0104] It should be understood that the above content describes how to determine the target query execution path of the target query statement based on the difference between the estimated number of rows of the first table and the second table in the target query statement when the target query statement adopts the hash join algorithm. There is no limitation on how to define the target query statement adopting the hash join algorithm.

[0105] For example, the query optimizer may determine the optimal join algorithm for the target query statement based on the cost factor. If the optimal join algorithm is a hash join, then the target query statement is determined to use the hash join algorithm.

[0106] Exemplarily, the query optimizer can estimate the cost factors of query execution paths of different join algorithms by analyzing the statistical information of the first table and the second table. If the algorithm with the lowest cost factor is the hash join, the query optimizer selects the target query statement to use the hash join algorithm and executes the target query statement.

[0107] For another example, the query processor uses a hash join algorithm through a specific hint used in the target query statement to execute the target query statement.

[0108] It should be noted that the embodiment of the present application does not limit the specific setting method of the hash join algorithm used in the target query statement.

[0109] S103: Execute the target query statement based on the target query execution path.

[0110] Exemplarily, the query processor executes the target query statement based on the construction table and the detection table confirmed in the target query execution path. The method of executing the target query statement is shown in the method of performing two-table join operation by the above-mentioned hash join algorithm, which will not be described in detail here.

[0111] The method provided in the present application determines the target query execution path between the first table and the second table by comparing the difference between the estimated number of rows of the two tables to execute the query statement. By considering the difference between the estimated number of rows of the two tables, the method enables the query optimizer to select the small table as the construction table and the large table as the detection table as much as possible when selecting the target query execution path, thereby reducing memory usage and improving query efficiency.

[0112] As described above, the first table can be any one of the two tables, and the second table can be another table, or the first table can also be a construction table determined by an existing query optimizer based on a cost factor, and the second table can be a detection table determined by the query optimizer based on a cost factor. The following uses the method of using an existing query optimizer to determine the first table and the second table based on a cost factor as an example to illustrate how to determine the target query execution path between the first table and the second table. Figure 2 A flowchart of a method for determining a target query execution path provided by an embodiment of the present application is shown in FIG. Figure 2 As shown, step S102 may include, for example:

[0113] S201: Generate multiple execution paths of the target query statement.

[0114] Exemplarily, the query optimizer generates multiple execution paths of the target query statement.

[0115] For example, in a two-table join operation, the first execution path may be to use one of the two tables as a build table and the other table as a probe table. The second execution path is the opposite, that is, to change the table originally used as a probe table to a build table, and vice versa.

[0116] It should be noted that the embodiment of the present application does not limit the generation method and number of execution paths, and can be generated according to the actual needs of the solution.

[0117] S202: Estimate the cost of each execution path, and determine the optimal query execution path based on the estimated cost of the execution path.

[0118] The cost refers to the resource consumption that may be generated by each execution path. The optimal query execution path is the execution path with the lowest estimated cost and the highest efficiency among all possible execution paths. The optimal query execution path includes the confirmation build table and the detection table.

[0119] Exemplarily, the query optimizer analyzes statistical information and estimates the cost factors of each execution path to select the execution path with the lowest cost as the optimal query execution path for the two-table join operation.

[0120] The embodiment of the present application does not limit the cost estimation method of each execution plan.

[0121] S203: The construction table indicated by the connection sequence of the two tables is used as the first table, and the detection table is used as the second table.

[0122] The connection order of the two tables is the connection order of the build table and the detection table determined in the above optimal query execution path.

[0123] Exemplarily, the construction table determined in the optimal query execution path is used as the first table, and the detection table is used as the second table.

[0124] S204: Determine whether the estimated number of rows in the first table is less than the first product.

[0125] If yes, it means that the estimated number of rows in the first table in the current optimal query execution path is much smaller than the estimated number of rows in the second table, and then S205 is executed. If no, the estimated number of rows in the first table is not necessarily much smaller than the estimated number of rows in the second table, and the difference between the two numbers of rows cannot be accurately determined, and then S206 is executed.

[0126] S205: Determine the target query execution path as an optimal query execution path determined based on the estimated execution path cost.

[0127] S206: Exchange the connection order of the two tables in the optimal query execution path, so that the second table is used as a construction table and the first table is used as a detection table.

[0128] S207: Determine whether the estimated number of rows in the second table is less than the second product.

[0129] If yes, it means that the estimated number of rows in the second table is much smaller than that in the first table, then S208 is executed; if no, the estimated number of rows in the second table is not necessarily much smaller than that in the first table, and the difference between the numbers of rows in the two tables cannot be accurately determined, then S209 is executed.

[0130] S208: Determine that the target query execution path is a query execution path that exchanges the connection order of two tables of the optimal query execution path.

[0131] When the estimated number of rows in the second table is less than the second product, the estimated number of rows in the second table is much smaller than the estimated number of rows in the first table. Then the query execution path that exchanges the connection order of the two tables of the optimal query execution path is a query execution path with higher connection efficiency, and this path is determined as the target query execution path.

[0132] S209: Taking the optimal query execution path determined based on the estimated cost of the execution path as the target query execution path.

[0133] When the estimated number of rows of the second table is also not less than the second product, it proves that the difference between the estimated number of rows of the first table and the estimated number of rows of the second table may not be large, and the size relationship between the two cannot be determined based on the number of rows. Therefore, the existing execution path with the lowest cost selected by analyzing multiple factors such as statistical information of the two tables, hardware performance parameters, data access patterns, query structures, etc. can be used as the target query execution path for the connection operation of the two tables.

[0134] The method provided in the present application determines the optimal query execution path and the first table and the second table by estimating the cost of each execution plan. The difference between the estimated number of rows between the first table and the second table is compared. If the difference between the estimated number of rows between the two does not meet the conditions, the connection order of the first table and the second table is exchanged, and the difference in the estimated number of rows between the two tables is recalculated to determine the target query execution path. By considering the difference between the estimated number of rows of the two tables, this method enables the query optimizer to choose a small table as a construction table and a large table as a detection table as much as possible when determining the target query execution path, thereby reducing memory usage and improving query efficiency.

[0135] The following is a specific example to illustrate how to implement data query. Figure 3 A flow chart of a data query method provided in an embodiment of the present application is shown as follows: Figure 3 As shown, the method may include the following steps:

[0136] S301: The query processor obtains a target query statement, which is a query statement for connecting two tables.

[0137] S302: The query optimizer confirms whether the target query statement is a hash join. If so, S303 is executed.

[0138] S303: The query optimizer generates multiple execution paths of the target query statement.

[0139] S304: The query optimizer estimates the cost of each execution path, and determines the optimal query execution path based on the estimated cost of the execution plan.

[0140] The optimal query execution path includes confirming the detection table and the construction table in the two tables, taking the construction table as the first table and the detection table as the second table.

[0141] S305: The query optimizer determines whether the estimated number of rows in the first table is less than the first product. If yes, S309 is executed; if no, S306 is executed.

[0142] The first product is the product of the number of rows in the first table and the row number difference factor.

[0143] S306: The query optimizer exchanges the connection order of the first table and the second table, so as to use the second table as a construction table and the first table as a detection table.

[0144] S307: The query optimizer determines whether the estimated number of rows in the second table is less than the second product. If yes, execute S308; if no, execute S310.

[0145] The second product is the product of the number of rows in the first table and the row number difference factor.

[0146] S308: The query optimizer determines that the target query execution path is a query execution path in which the second table is used as a construction table and the first table is used as a detection table.

[0147] S309: The query optimizer determines that the target query execution path is a query execution path in which the first table is a construction table and the second table is a detection table.

[0148] S310: The query optimizer uses the optimal query execution path as the target query execution path.

[0149] S311. The query processor executes the target query statement based on the target query execution path.

[0150] The method provided by the present application determines the optimal query execution path by estimating the cost of each execution path when the hash join algorithm is used to connect two tables. The difference between the estimated number of rows between the first table and the second table is compared. If the difference between the estimated number of rows between the two does not meet the conditions, the connection order of the first table and the second table is exchanged, and the difference in the estimated number of rows between the two tables is recalculated to determine the target query execution path and execute the query statement. By considering the difference between the estimated number of rows of the two tables, this method enables the optimizer to choose a small table as a construction table and a large table as a detection table as much as possible when determining the target query execution path, thereby reducing memory usage and improving query efficiency.

[0151] The above is the method of the embodiment of the present application. The device provided by the embodiment of the present application is described below.

[0152] Figure 4 A schematic diagram of a data query device provided in an embodiment of the present application is shown in FIG. Figure 4 As shown, the data query device includes: an acquisition module 401, a determination module 402 and a processing module 403.

[0153] The acquisition module 401 is used to acquire a target query statement. The target query statement is a query statement for connecting two tables.

[0154] The determination module 402 is configured to determine a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement if the target query statement adopts a hash join algorithm.

[0155] The processing module 403 is used to execute the target query statement based on the target query execution path.

[0156] Optionally, the determination module 402 is specifically used to generate multiple execution paths of the target query statement before determining the target query execution path based on the difference between the estimated number of rows of the first table and the second table in the target query statement. Estimate the cost of each execution path, and determine the optimal query execution path based on the estimated execution path. Connect the build table in the optimal query execution path with the detection table, and use the build table as the first table and the detection table as the second table.

[0157] Optionally, the determination module 402 is specifically configured to determine whether the estimated number of rows in the first table is less than a first product. The first product is the product of the number of rows in the second table and the row number difference factor. If so, the target query execution path is determined to be the optimal query execution path.

[0158] Exemplarily, the determination module 402 is specifically used to exchange the connection order of the two tables in the optimal query execution path if the estimated number of rows of the first table is greater than the first product, so as to use the second table as the construction table and the first table as the detection table. It is determined whether the estimated number of rows of the second table is less than the second product. The second product is the product of the number of rows of the first table and the row number difference factor. If so, the target query execution path is determined to be a query execution path in which the connection order of the two tables of the optimal query execution path is exchanged.

[0159] Exemplarily, the determination module 402 is further configured to use the optimal query execution path as the target query execution path if the estimated number of rows in the second table is greater than the second product.

[0160] Optionally, the determination module 402 is further configured to determine a row number difference factor based on the sizes of the first table and the second table.

[0161] Optionally, the determination module 402 is specifically configured to determine the optimal join algorithm for the target query statement according to the cost after the processing module 403 obtains the query statement. If the optimal join algorithm is a hash join algorithm, then determine that the target query statement uses the hash join algorithm.

[0162] The data query device provided in the embodiment of the present application can be used to execute the aforementioned data query method. Its implementation principle, process and beneficial effects can be found in the aforementioned embodiment and will not be repeated here.

[0163] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application. Figure 5 As shown, the electronic device 500 may include: a memory 501 and a processor 502. The electronic device may be, for example, the aforementioned database management system. Optionally, the electronic device may further include a communication interface 503, wherein the memory 501 and the processor 502 communicate; illustratively, the memory 501, the processor 502 and the communication interface 503 may communicate via a communication bus 504, the memory 501 is used to store a computer program, and the processor 502 executes the computer program to implement the method of the above embodiment.

[0164] Optionally, the processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), etc. A general-purpose processor may be a microprocessor or the processor may be any conventional processor, etc. The steps in the method embodiments disclosed in the present application may be directly implemented as being executed by a hardware processor, or may be implemented by a combination of hardware and software modules in the processor.

[0165] An embodiment of the present application further provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, a method in any of the above method embodiments is implemented.

[0166] An embodiment of the present application also provides a computer program product, including a computer program, which implements the method in any of the above method embodiments when the computer program is executed by a processor.

[0167] All or part of the steps of the above-mentioned method embodiments can be completed by hardware related to program instructions. The above-mentioned program can be stored in a readable memory. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the above-mentioned memory (storage medium) includes: read-only memory (ROM), RAM, flash memory, hard disk, solid state drive, magnetic tape, floppy disk, optical disc and any combination thereof.

[0168] The embodiments of the present application are described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processing unit of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a machine, so that the instructions executed by the processing unit of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0169] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0170] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0171] Continue to refer to Figure 1 , an embodiment of the present application also provides a database management system, the database management system includes: a query optimizer and a query processor.

[0172] The query processor is used to obtain a target query statement. The target query statement is a query statement for connecting two tables.

[0173] A query optimizer is used to determine a target query execution path of a target query statement by using the above method embodiment.

[0174] The query processor is also used to execute the target query statement based on the target query execution path.

[0175] The database management system provided in the embodiment of the present application can be used to execute the aforementioned data query method. Its implementation principle, process and beneficial effects can be found in the aforementioned embodiment and will not be repeated here.

[0176] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the following claims.

[0177] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.

Claims

1. A data query method, characterized in that: The method comprises: Obtain a target query statement; the target query statement is a query statement that connects two tables; If the target query statement adopts a hash join algorithm, determining a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement; Based on the target query execution path, the target query statement is executed.

2. The method according to claim 1, characterized in that Before determining the target query execution path based on the difference between the estimated number of rows of the first table and the second table in the target query statement, the method further includes: Generate multiple execution paths of the target query statement; estimating the cost of each execution path, and determining an optimal query execution path based on the estimated cost of the execution path; the connection order of two tables in the optimal query execution path is used to indicate the construction table and the detection table; The construction table indicated by the connection order of the two tables is used as the first table, and the detection table is used as the second table.

3. The method according to claim 2, characterized in that The determining the target query execution path based on the difference between the estimated number of rows of the first table and the second table in the target query statement includes: Determine whether the estimated number of rows in the first table is less than a first product; the first product is the product of the number of rows in the second table and a row number difference factor; If so, the target query execution path is determined to be the optimal query execution path.

4. The method according to claim 3, characterized in that The determining the target query execution path between the first table and the second table based on the difference between the estimated number of rows of the first table and the second table in the target query statement includes: If the estimated number of rows of the first table is greater than the first product, exchanging the connection order of the two tables in the optimal query execution path so as to use the second table as a construction table and the first table as a detection table; Determine whether the estimated number of rows in the second table is less than a second product; the second product is the product of the number of rows in the first table and a row number difference factor; If so, it is determined that the target query execution path is a query execution path in which the connection order of the two tables of the optimal query execution path is exchanged.

5. The method according to claim 4, characterized in that The method further comprises: If the estimated number of rows in the second table is greater than the second product, the optimal query execution path is used as the target query execution path.

6. The method according to any one of claims 2 to 5, characterized in that: The method further comprises: The row number difference factor is determined based on sizes of the first table and the second table.

7. The method according to any one of claims 1 to 5, characterized in that: After obtaining the query statement, the method further includes: Determine the optimal connection algorithm for the target query statement according to the cost; If the optimal connection algorithm is a hash connection algorithm, it is determined that the target query statement adopts the hash connection algorithm.

8. A data query device, characterized in that: The device comprises: An acquisition module, used to acquire a target query statement; the target query statement is a query statement connecting two tables; A determination module, if the target query statement adopts a hash join algorithm, determines a target query execution path of the target query statement based on a difference between the estimated number of rows of the first table and the second table in the target query statement; A processing module is used to execute the target query statement based on the target query execution path.

9. An electronic device, characterized in that: The electronic device comprises: a processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

11. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 7 when being executed by a processor.

12. A database management system, characterized in that: The database management system includes: a query optimizer and a query processor; The query processor is used to obtain a target query statement; the target query statement is a query statement connecting two tables; The query optimizer is used to determine the target query execution path of the target query statement by using the method according to any one of claims 1 to 7; The query processor is further configured to execute the target query statement based on the target query execution path.

Images