Data page index-based hash join query method, apparatus and system, and medium

Abstract

A data page index-based Hash Join query method, apparatus and system, and a medium. The method comprises: determining a build table and a probe table for a join query, as well as a join key field (S101); building a corresponding Hash table on the basis of data of the join key field in the build table, and determining whether a pre-created data page index of the join key field is present in the probe table, the data page index being an index structure using at least one data page as a unit (S102); if a pre-created data page index of the join key field is present in the probe table, associating the data page index of the join key field with the Hash table, and performing data page filtering for the probe table on the basis of an association result to generate a list of data pages to be accessed (S103); and reading corresponding data in the probe table on the basis of the list of data pages to be accessed, and performing a Hash Join with the Hash table to generate a corresponding data query result (S104).

Description

Hash Join query method, device, system and medium based on data page index

[0001] The present application claims priority to the Chinese patent application No. 202411116721.9, filed on August 15, 2024, to the Chinese Patent Office, the content of which is incorporated herein by reference in its entirety. TECHNICAL FIELD

[0002] The present application relates to the field of database technology, for example, to a Hash Join query method, device, system and medium based on data page index. BACKGROUND

[0003] Multi-table association is a common structured query language (SQL) syntax in databases. After years of development, there are mature join algorithms, such as nest loop, merge join, Hash Join, etc.

[0004] Mainstream commercial databases and open source databases basically support Hash Join algorithm. The conventional method of Hash Join is to load the smaller table into memory to build a Hash table, and then access the large table to compare each row with the rows in the Hash table to filter out the rows that meet the association conditions.

[0005] When the table data volume is large, the data volume accessed by the Hash table and the number of probe traversals are also very large when Hash Join is used to execute the join query, which reduces the efficiency of Hash Join and further affects the performance of multi-table join query. SUMMARY

[0006] The present application provides a Hash Join query method, device, system and medium based on data page index, which aims to reduce the data probe volume and I / O access volume of Hash Join, and improve the efficiency and performance of join query.

[0007] The present application provides a Hash Join query method based on data page index, comprising:

[0008] determining the build table and the probe table to be connected and the connection key field;

[0009] constructing a Hash table according to data in the connection key field in the build table, and determining whether a pre-created data page index of the connection key field exists in the probe table, the data page index being an index structure in units of at least one data page;

[0010] in response to the pre-created data page index of the connection key field existing in the probe table, associating the data page index of the connection key field with the Hash table, performing data page filtering on the probe table according to an association result, and generating a data page list to be accessed;

[0011] reading corresponding data in the probe table according to the data page list to be accessed, performing Hash Join with the Hash table, and generating a corresponding data query result.

[0012] In one embodiment, before the build table and the probe table of the to-be-connected query and the connection key field are determined, the method further comprises:

[0013] filtering tables and fields frequently participating in Join according to database record information;

[0014] creating a data page index of the field on the filtered table according to a preset logical structure.

[0015] In one embodiment, the data page index comprises a start data page id, an end data page id, a field name, a maximum value and a minimum value of the field in each data page.

[0016] In one embodiment, the associating the data page index of the connection key field with the Hash table, the data page filtering on the probe table according to an association result, and the generating a data page list to be accessed comprise:

[0017] associating the data page index of the connection key field with the Hash table, and comparing the connection key field in each data page with a connection key field in the Hash table according to a maximum value and a minimum value of the connection key field in each data page;

[0018] filtering out data pages in the probe table that do not need to be accessed according to a comparison result, and generating a data page list to be accessed.

[0019] In one embodiment, after the tables and fields frequently participating in Join are filtered according to the database record information, the method further comprises:

[0020] performing secondary filtering on the tables frequently participating in Join according to a preset lower limit of a row number.

[0021] In one embodiment, after the data page index of the field is created on the filtered table according to the preset logical structure, the method further comprises:

[0022] In response to an addition, deletion or modification operation on the table where the field is located, updating the data page index of the field in real time according to the operation result.

[0023] In one embodiment, the reading of the corresponding data in the probe table according to the list of data pages to be accessed, the Hash Join with the Hash table, and the generation of the corresponding data query result, comprise:

[0024] Traversing the data in the specified range in the probe table according to the list of data pages to be accessed;

[0025] Comparing the traversed data one by one with the data in the Hash table, and confirming the data in the traversed data that matches the Hash table;

[0026] Summarizing all the data that matches the Hash table to generate the corresponding data query result.

[0027] Embodiments of the present application provide a Hash Join query device based on data page index, comprising:

[0028] A query condition acquisition module is configured to determine a build table and a probe table to be connected for query, and a connection key field;

[0029] A build and index determination module is configured to construct a corresponding Hash table according to the data of the connection key field in the build table, and determine whether a data page index of the connection key field is pre-created in the probe table, the data page index being an index structure in units of at least one data page;

[0030] An association filtering module is configured to, in response to the pre-created data page index of the connection key field existing in the probe table, associate the data page index of the connection key field with the Hash table, perform data page filtering on the probe table according to the association result, and generate a list of data pages to be accessed;

[0031] A connection query module is configured to read the corresponding data in the probe table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate a corresponding data query result.

[0032] Embodiments of the present application provide a Hash Join query system based on data page index, the system comprising at least one processor; and

[0033] A memory in communication connection with the at least one processor; wherein,

[0034] The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the Hash Join query method based on the data page index.

[0035] The non-volatile computer readable storage medium stores computer executable instructions, and the computer executable instructions, when executed by one or more processors, can enable the one or more processors to perform the Hash Join query method based on the data page index. BRIEF DESCRIPTION OF DRAWINGS

[0036] FIG. 1 is a flowchart of the Hash Join query method based on the data page index provided by the embodiments of the present application;

[0037] FIG. 2 is a flowchart of constructing a data page index in the Hash Join query method based on the data page index provided by the embodiments of the present application;

[0038] FIG. 3 is a flowchart of step S103 in the Hash Join query method based on the data page index provided by the embodiments of the present application;

[0039] FIG. 4 is a flowchart of step S104 in the Hash Join query method based on the data page index provided by the embodiments of the present application;

[0040] FIG. 5 is a functional module schematic diagram of the Hash Join query device based on the data page index provided by the embodiments of the present application;

[0041] FIG. 6 is a hardware structure schematic diagram of the Hash Join query system based on the data page index provided by the embodiments of the present application. DETAILED DESCRIPTION

[0042] The embodiments of the present application are described below with reference to the accompanying drawings. It should be understood that the embodiments described herein are only used to explain the present application and should not be used to limit the present application.

[0043] The present application proposes a Hash Join query method based on a data page index. In the embodiments, the method is applied to an Online Analytical Processing (OLAP) business scenario in the database field. As shown in FIG. 1, the Hash Join query method based on the data page index provided by the embodiments includes the following steps:

[0044] S101, determining a construction table and an exploration table to be connected and a connection key field.

[0045] In the embodiment, the OLAP scenario is mainly in the star model and the snowflake model, in which the fact table has a large amount of data, the dimension table has less data, the business SQL is mainly for multi-table association statistical analysis, and usually one fact table is associated with multiple dimension tables. When performing multi-table connection query in the OLAP scenario, the corresponding query instruction can be input through the business SQL, and then the build table and the probe table to be connected and the connection key field are determined. The build table is usually a small data table such as a dimension table, and the probe table is a large data table such as a fact table. The build table and the probe table are connected through the connection key field as the key for connection between the two tables, so that the multi-table data is matched and queried through the connection key field in subsequent connection query. The connection key field is usually a common field in the two tables, such as a date field or other common fields. The embodiment is not limited in this regard.

[0046] S102, constructing a corresponding Hash table according to the data of the connection key field in the build table, and determining whether a pre-created data page index of the connection key field exists in the probe table. The data page index is an index structure in units of at least one data page.

[0047] In the embodiment, for a smaller build table, the build table is loaded into the memory, and a Hash value is generated for each record in the build table according to the data of the connection key field, a Hash table is constructed, and the records in the build table are stored through the Hash table, so that the corresponding record can be quickly found according to the connection key field in subsequent Hash Join. The range of constructing the Hash table can be all records or part of the records in the build table. For example, when the date field is used as the association key field, all records of the date can be found in the constructed Hash table, or the records of the specified year can be first selected from the build table through the SQL statement, the records of the specified year are stored in the memory and the corresponding Hash table is constructed. The embodiment is not limited in this regard.

[0048] After the Hash table is constructed, to improve the database query performance in the case of large data, the embodiment does not directly perform Hash Join with all the data of the probe table, but first determines whether there is a pre-created page index of the connection key field in the probe table, that is, a data page index, which is an index structure created in units of at least one data page. That is, the database automatically creates a corresponding data page index for the field in the probe table with large data volume during business idle time. Since one or more data pages correspond to a data page index, the structure of the data page index is small in size, which is convenient for being resident in memory to improve performance. By first confirming whether the data page index exists in the probe table, the data page where the record matched with the Hash table in the probe table can be located more quickly when the data page index exists, without the need to scan the entire probe table, thereby improving the query efficiency.

[0049] S103, if the data page index of the connection key field exists in the probe table, the data page index of the connection key field is associated with the Hash table, the probe table is filtered by data pages according to the association result, and a data page list to be accessed is generated.

[0050] In the embodiment, in the case of small table Join large table, if the data page index exists in the large table, the data page index is first associated with the Hash table, the related data page information in the data page index is matched with the Hash table to find the matched data page record, and the probe table is filtered by data pages according to the association result to filter out the data pages in the probe table that do not need to be accessed, thereby obtaining the data page list to be accessed. Since the data page index and the data page are in a one-to-many relationship and are resident in memory, the association operation of the data page index and the Hash table is very fast, thereby the pages in the probe table that do not need to be accessed can be efficiently filtered out, the data that needs to be accessed by the Hash table in the subsequent Hash Join is greatly reduced, and the Input / Output (I / O) operation is reduced.

[0051] S104, according to the data page list to be accessed, the corresponding data in the probe table is read, Hash Join is performed with the Hash table, and a corresponding data query result is generated

[0052] In this embodiment, based on the list of data pages to be accessed obtained through page filtering, the data of the corresponding pages in the probe table is read, and Hash Join is performed with the Hash table constructed previously, so that the data query result of multi-table connection is accurately and efficiently obtained through Hash Join within the range of part of the pages of the probe table. Through data page filtering of the probe table by the constructed data page index, the data that needs to be accessed by the Hash table is greatly reduced, and the efficiency of Hash Join is improved, thereby improving the efficiency and performance of multi-table connection query.

[0053] In one embodiment, as shown in FIG. 2, before the determination of the constructed table and the probe table of the to-be-connected query and the connection key field, the method further includes:

[0054] S201, filtering out the tables and fields frequently participating in Join according to database record information;

[0055] S202, creating a data page index of the field on the filtered table according to a preset logical structure.

[0056] In this embodiment, according to the record information of the database, for example, all the successfully executed SQLs of the database record or the statistical information of the historical Join of the database, the tables and fields frequently participating in Join are automatically analyzed and filtered out by taking frequency as the filtering index, for example, a frequency threshold is preset, and the tables and fields participating in Join with a frequency greater than the frequency threshold are filtered out. For the filtered tables and fields, the database automatically creates a page-level index according to a preset logical structure, that is, a data page index of the field, and these data page indexes are stored in the memory, so as to provide more accurate page positioning information when performing Hash Join query on multiple tables, save data access amount, and reduce data I / O operation.

[0057] The logical structure of the data page index includes two parts, the first part of the logical structure is used to store all basic information, including index name, index type, table name, column name, creation time, etc., and the second part of the logical structure is used to store metadata information of the data page index, including starting data page identity (Identity, id), ending data page id, field name, maximum and minimum values of the field in each data page, and global transaction number, etc., thereby providing accurate and reliable index basis for data page filtering.

[0058] In one embodiment, after the filtering out of the tables and fields frequently participating in Join according to the database record information, the method further includes:

[0059] According to a preset lower limit of the number of rows, the tables frequently participating in Join are secondarily filtered.

[0060] In the embodiment, since the effect of the method of improving the Hash Join efficiency by the data page index is limited for the table with small data volume, in order to improve the resource utilization, after the table and the field participating in the Join frequently are filtered based on the database record information, the table with the row number greater than the row number lower limit value is further filtered from the table participating in the Join frequently, so that the operation of creating the data page index is performed in the table participating in the Join frequently and having large data volume, and the improvement effect of the overall data query efficiency by the data page index under the limited resource is ensured.

[0061] In one embodiment, after the data page index of the field is created on the table filtered according to the preset logical structure, the method further includes:

[0062] When the table where the field is located is subjected to the operation of adding, deleting or modifying, the data page index of the field is updated in real time according to the operation result.

[0063] In the embodiment, for the data page index created for the filtered field, the index information is updated in real time along with the data transformation of the table where the field is located, and when the table is subjected to the operation of adding, deleting or modifying, the index information corresponding to the second part of the logical structure in the data page index is updated in real time, including the starting data page id, the ending data page id, the field name, the maximum value and the minimum value of the field in each data page, and the global transaction number and the like. By updating the data page index in real time when the table is subjected to the operation of adding, deleting or modifying, it is ensured that the latest and most accurate data page index information is used for the association with the Hash table in the Hash Join query, and the accuracy and reliability of the data page filtering are ensured.

[0064] In order to improve the memory resource utilization, when the table is deleted, the page index corresponding to the first part of the logical structure and the second part of the logical structure is automatically deleted; when the field of the page index is deleted, the page index corresponding to the first part of the logical structure and the second part of the logical structure is automatically deleted; and when the table is truncated, the page index corresponding to the second part of the logical structure is automatically deleted, so that the effectiveness of the data page index in the memory is ensured.

[0065] In one embodiment, as shown in FIG. 3, the data page index of the connection key field is associated with the Hash table, the data page filtering is performed on the probe table according to the association result, and the data page list to be accessed is generated, including:

[0066] S301, the data page index of the connection key field is associated with the Hash table, and the maximum value and the minimum value of the connection key field in each data page are compared with the connection key field in the Hash table;

[0067] S302, filtering out the data pages in the probe table that do not need to be accessed according to the comparison result, and generating a list of data pages to be accessed.

[0068] In this embodiment, when the data pages are associated by the data page index of the connection key field in the Hash table, the association can be performed by the corresponding SQL statement, and then a list of data page ids is generated. The data page id can be used as a query field, and the maximum value c_max and the minimum value c_min of the connection key field in each data page are compared with the connection key field in the Hash table. When the connection key field in the Hash table is greater than or equal to the c_min of a data page and less than or equal to the c_max of the data page, the data page is a page that meets the association condition, and the id of the data page is recorded. Otherwise, the data page is a data page that does not need to be accessed and needs to be filtered out. The list of data pages to be accessed is generated according to all the data page ids that meet the association condition. By associating the data page index with the Hash table, the list of data pages containing matching records in the probe table can be determined first, so that only these pages are scanned during the subsequent Hash Join, instead of the entire probe table, which greatly reduces the data probe amount and effectively improves the query efficiency.

[0069] In one embodiment, as shown in FIG. 4, the reading of the corresponding data in the probe table according to the list of data pages to be accessed, the Hash Join with the Hash table, and the generation of the corresponding data query result, include:

[0070] S401, traversing the data in the specified range of the probe table according to the list of data pages to be accessed;

[0071] S402, comparing the traversed data with the data in the Hash table one by one, and confirming the data in the traversed data that matches the Hash table;

[0072] S403, summarizing all the data that matches the Hash table, and generating the corresponding data query result.

[0073] In this embodiment, after the list of data pages to be accessed is obtained by associating and filtering through the data page index and the Hash table, the data pages in the probe table that do not need to be accessed can be greatly filtered out, and the data page list gives the specified range that needs to be normally completed in the Hash Join with the Hash table. Based on the list of data pages to be accessed, the data in the specified range in the probe table is traversed, the traversed data is compared with the data in the Hash table one by one, the data matched with the Hash table is confirmed, the Hash value of the data in the traversed probe table is generated by using the same Hash function, and then the corresponding record in the Hash table is searched. If the record in the probe table is found in the Hash table, then the two records are a pair of matched connection results. All matched records are combined, that is, all data matched with the Hash table are summarized, and the data query result of the corresponding multi-table connection can be generated. On the basis of quickly locating the record by using the Hash table, in combination with the data page index to reduce the data that needs to be accessed, the Hash Join efficiency is further improved, and even in the query scene with large table data, high query efficiency can still be maintained.

[0074] The above steps do not necessarily have a certain sequence, and a person of ordinary skill in the art can understand, according to the description of the embodiments of the present application, that the above steps can have different execution sequences in different embodiments, that is, can be executed in parallel, can be exchanged, and the like.

[0075] In order to understand the implementation process of the Hash Join query method based on the data page index provided by the embodiments of the present application, the implementation process of the Hash Join query method based on the data page index is introduced by using an application embodiment as follows:

[0076] Currently, there are two tables of lineorder and dwdate, both from the SSBM model, wherein the lineorder is a fact table, the data volume is large, the dwdate is a dimension table, the data volume is small, and can be put into memory, and the table structure is as follows:

[0077] --Date dimension table

[0078] create table dates(

[0079] d_datekey integer,

[0080] d_date varchar(18)not null,

[0081] d_dayofweek varchar(18)not null,

[0082] d_month varchar(9) not null,

[0083] d_year integer not null,

[0084] d_yearmonthnum integer,

[0085] d_yearmonth varchar(7) not null,

[0086] d_daynuminweek integer,

[0087] d_daynuminmonth integer,

[0088] d_daynuminyear integer,

[0089] d_monthnuminyear integer,

[0090] d_weeknuminyear integer,

[0091] d_sellingseason varchar(12) not null,

[0092] d_lastdayinweekfl integer,

[0093] d_lastdayinmonthfl integer,

[0094] d_holidayfl integer,

[0095] d_weekdayfl integer

[0096] ) ;

[0097] -- orders table

[0098] create table lineorder (

[0099] lo_orderkey bigint,

[0100] lo_linenumber bigint,

[0101] lo_custkey integer not null,

[0102] lo_partkey integer not null,

[0103] lo_suppkey integer not null,

[0104] lo_orderdate integer not null,

[0105] lo_orderpriotity varchar(15)not null,

[0106] lo_shippriotity integer,

[0107] lo_quantity bigint,

[0108] lo_extendedprice bigint,

[0109] lo_ordtotalprice bigint,

[0110] lo_discount bigint,

[0111] lo_revenue bigint,

[0112] lo_supplycost bigint,

[0113] lo_tax bigint,

[0114] lo_commitdate integer not null,

[0115] lo_shipmode varchar(10)not null

[0116] ) ;

[0117] -- The following SQL is used to calculate the revenue for 2023 and 2024

[0118] select d_year, sum(lo_revenue)

[0119] from lineorder, dwdate

[0120] where lo_orderdate=d_datekey

[0121] and d_year in('2023','2024')

[0122] group by d_year;

[0123] Note: The field lo_orderdate in table lineorder has a page index created in advance

[0124] That is, in this embodiment, the lineorder table is taken as the probe table, the dwdate table is taken as the build table, the connection condition and the filter condition are specified, that is, the lo_orderdate field in the lineorder table is matched with the d_datekey field in the dwdate table, the date fields in the two tables are taken as the connection key fields, and only the d_year field is selected as the record of 2023 or 2024.

[0125] Based on the query requirement of the above business SQL, the Hash Join query flow is as follows:

[0126] Step 1: In the business idle time, create a page index on the lo_orderdate field, name it lineorder_pi_1 for subsequent use, which contains the minimum and maximum lo_orderdate values (c_min and c_max) of each page, and the index is resident in memory;

[0127] Step 2: Generate the access dimension table dwdate SQL according to the above business SQL:

[0128] select*from dwdate where d_year in('2023','2024')

[0129] That is, select all records with the year of 2023 or 2024 from the dwdate table, read the result into memory, and construct a Hash table;

[0130] Step 3: Use the page index on the lo_orderdate field to associate with the Hash table constructed in step 2, and the association SQL is as follows:

[0131] select pid

[0132] from lineorder_pi_1a,dwdate b

[0133] where b.d_datekey>=a.c_min

[0134] and b.d_datekey<=a.c_max;

[0135] The above SQL will generate a page list of lineorder table by comparing the d_datekey field of dwdate table with the lo_orderdate field of lineorder table, and the page list is a list containing all page IDs of lineorder table matching the specified year in dwdate table;

[0136] Step 4: According to the page list generated in step 3, read the lineorder data one by one and associate it with the Hash table constructed in step 2 until all page lists in step 3 are read;

[0137] Step 5: According to the d_year summary data and return to the client.

[0138] In this embodiment, since the page index and the data page are in a one-to-many relationship, it can ensure that the page index is small in size, easy to reside in memory, and associated with the Hash table very quickly, without negative impact. By associating the page index with the Hash table in step 2 and returning the page list, the lineorder data is effectively filtered, and the data accessed by the Hash table is reduced. For example, assuming that lineorder stores 10 years of data, nearly 80% of the data will be filtered out in the ideal state of this embodiment, so that the large table I / O in the subsequent Hash Join will be greatly reduced, thereby effectively improving the Hash Join performance.

[0139] Another embodiment of the present application provides a Hash Join query device based on data page index, as shown in FIG. 5, the device 1 comprises:

[0140] The query condition acquisition module 11 is configured to determine the construction table and the probe table to be connected and the connection key field;

[0141] The construction and index determination module 12 is configured to construct a corresponding Hash table according to the data of the connection key field in the construction table, and determine whether a pre-created data page index of the connection key field exists in the probe table, and the data page index is an index structure in units of at least one data page;

[0142] The association filtering module 13 is configured to associate the data page index of the connection key field with the Hash table if the data page index of the connection key field exists in the probe table, and filter the data page of the probe table according to the association result to generate a list of data pages to be accessed.

[0143] The connection query module 14 is configured to read corresponding data in the probe table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate a corresponding data query result.

[0144] The module in the present application refers to a series of computer program instructions capable of completing a specific function, and is more suitable for describing the execution process of Hash Join query based on data page index than a program. The specific implementation of multiple modules can refer to the corresponding method embodiments described above, and will not be described here.

[0145] Another embodiment of the present application provides a Hash Join query system based on data page index, as shown in FIG. 6, the system 10 includes:

[0146] One or more processors 110 and memories 120, and one processor 110 is taken as an example in FIG. 6, the processor 110 and the memory 120 can be connected through a bus or other means, and the connection through the bus is taken as an example in FIG. 6.

[0147] The processor 110 is configured to complete various control logics of the system 10, and can be a general processor, a digital signal processor (Digital Signal Processor, DSP), an application specific integrated circuit (Application Specific Integrated Circuit, ASIC), a field programmable gate array (Field-Programmable Gate Array, FPGA), a single-chip microcomputer, an acorn RISC machine (Acorn RISC Machine, ARM) or other programmable logic devices, discrete gates or transistor logic, discrete hardware components or any combination of these components. In addition, the processor 110 can also be any conventional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, multiple microprocessors, one or more microprocessors in combination with a DSP and / or any other such configuration.

[0148] The memory 120, as a non-volatile computer readable storage medium, can be configured to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the Hash Join query method based on data page index in the embodiments of the present application. The processor 110 executes the non-volatile software programs, instructions and units stored in the memory 120, thereby performing various functional applications and data processing of the system 10, that is, implementing the Hash Join query method based on data page index in the above-mentioned method embodiments.

[0149] The memory 120 can include a program storage area and a data storage area, wherein the program storage area can store an operating system and application programs required by at least one function; and the data storage area can store data created according to the use of the system 10, etc. In addition, the memory 120 can include a high-speed random access memory, and can also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 can optionally include a memory remotely arranged with respect to the processor 110, and these remote memories can be connected to the system 10 through a network. Examples of the above-mentioned network include but are not limited to the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0150] One or more instructions are stored in the memory 120, and when executed by one or more processors 110, the following steps are implemented:

[0151] Determine the build table and the probe table of the query to be connected and the connection key field;

[0152] According to the data of the connection key field in the build table, a corresponding Hash table is constructed, and it is determined whether there is a pre-created data page index of the connection key field in the probe table, and the data page index is an index structure in units of at least one data page;

[0153] If the pre-created data page index of the connection key field exists in the probe table, the data page index of the connection key field is associated with the Hash table, the probe table is filtered according to the association result, and a data page list to be accessed is generated;

[0154] According to the data page list to be accessed, corresponding data in the probe table is read, Hash Join is performed with the Hash table, and a corresponding data query result is generated.

[0155] In one embodiment, before the determination of the build table and the probe table of the query to be connected and the connection key field, the method further comprises:

[0156] According to the database record information, the tables and fields frequently participating in Join are screened out.

[0157] creating a data page index of the field on the screened table according to a preset logical structure.

[0158] In one embodiment, the data page index comprises a start data page id, an end data page id, a field name, a maximum value and a minimum value of the field in each data page.

[0159] In one embodiment, the associating the data page index of the join key field with the Hash table, and filtering the data pages of the probe table according to the association result to generate a list of data pages to be accessed, comprises:

[0160] associating the data page index of the join key field with the Hash table, and comparing the maximum value and the minimum value of the join key field in each data page with the join key field in the Hash table;

[0161] filtering out the data pages in the probe table that do not need to be accessed according to the comparison result to generate the list of data pages to be accessed.

[0162] In one embodiment, after the tables and fields frequently participating in join are screened according to the database record information, the method further comprises:

[0163] performing secondary screening on the tables screened for frequently participating in join according to a preset lower limit of the number of rows.

[0164] In one embodiment, after the data page index of the field is created on the screened table according to the preset logical structure, the method further comprises:

[0165] when the table where the field is located is subjected to an operation of addition, deletion or modification, updating the data page index of the field in real time according to the operation result.

[0166] In one embodiment, the reading the corresponding data in the probe table according to the list of data pages to be accessed, performing Hash Join with the Hash table, and generating a corresponding data query result, comprises:

[0167] traversing the data in a specified range in the probe table according to the list of data pages to be accessed;

[0168] comparing the traversed data one by one with the data in the Hash table, and confirming the data in the traversed data that matches the Hash table;

[0169] summarizing all the data that matches the Hash table to generate a corresponding data query result.

[0170] The embodiment of the present application provides a kind of nonvolatile computer readable storage medium, computer readable storage medium has computer executable instruction, the computer executable instruction is executed by one or more processors, for example, the method steps S101 to step S104 in the execution of above description in figure 1.

[0171] By way of example, nonvolatile storage can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile storage can include random access memory (RAM), as external cache memory. By way of illustration, and not limitation, RAM can be available at various speeds such as synchronous RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). Disclosed memory components or memory within the operating environments described herein are intended to include one or more of these and / or any other suitable type of memory.

[0172] To sum up, in the Hash Join query method, device, system and medium based on data page index disclosed in the application, the method determines the build table and the probe table to be connected and the connection key field; constructs a corresponding Hash table according to the data of the connection key field in the build table, and determines whether there is a data page index of the connection key field that is created in advance in the probe table, the data page index being an index structure in units of at least one data page; if there is a data page index of the connection key field that is created in advance in the probe table, the data page index of the connection key field is associated with the Hash table, the probe table is filtered according to the association result, and a data page list to be accessed is generated; the corresponding data in the probe table is read according to the data page list to be accessed, and Hash Join is performed with the Hash table, so that a corresponding data query result is generated. The data page index corresponding to at least one data page is associated with the Hash table first, the probe table is filtered, so that the data that needs to be accessed by the Hash table in subsequent Hash Join is greatly reduced, and the connection query efficiency and performance are improved.

[0173] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by a computer program instructing relevant hardware (such as a processor, a controller, etc.) to complete, and the computer program can be stored in a non-volatile computer readable storage medium. When the computer program is executed, it can include the processes of the above-mentioned method embodiments. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage, etc.

Claims

1. A Hash Join query method based on data page index, comprising: determining a build table and a probe table of a query to be connected and a connection key field; constructing a Hash table corresponding to the connection key field in the build table according to data of the connection key field, and determining whether a data page index of the connection key field pre-created exists in the probe table, the data page index being an index structure in units of at least one data page; in response to the data page index of the connection key field pre-created existing in the probe table, associating the data page index of the connection key field with the Hash table, performing data page filtering on the probe table according to an association result, and generating a data page list to be accessed; reading corresponding data in the probe table according to the data page list to be accessed, performing Hash Join with the Hash table, and generating a corresponding data query result. 2.The Hash Join query method based on data page index according to claim 1, before the determining a build table and a probe table of a query to be connected and a connection key field, the method further comprises: screening tables and fields frequently participating in Join according to database record information; creating a data page index of the field on the screened table according to a preset logical structure.

3. The Hash Join query method based on data page index according to claim 2, wherein, The data page index comprises a start data page identifier id, an end data page id, a field name, a maximum value and a minimum value of the field in each data page.

4. The Hash Join query method based on data page index according to claim 3, wherein, The associating the data page index of the connection key field with the Hash table, the data page filtering on the probe table according to an association result, and the generating a data page list to be accessed, comprises: associating the data page index of the connection key field with the Hash table, comparing the maximum value and the minimum value of the connection key field in each data page with a connection key field in the Hash table, filtering out data pages in the probe table not needed to be accessed according to a comparison result, and generating a data page list to be accessed. 5.The Hash Join query method based on data page index according to any one of claims 2-4, after the screening tables and fields frequently participating in Join according to database record information, the method further comprises: performing secondary screening on the screened tables frequently participating in Join according to a preset lower limit of row number. 6.The Hash Join query method based on data page index according to any one of claims 2-4, after the creating a data page index of the field on the screened table according to a preset logical structure, the method further comprises: in response to an adding, deleting or modifying operation occurring in a table where the field is located, updating the data page index of the field in real time according to an operation result. The reading corresponding data in the probe table according to the data page list to be accessed, the Hash Join with the Hash table, and the generating a corresponding data query result, comprises:

7. The Hash Join query method based on data page index as claimed in claim 1, wherein, traversing data in a specified range in the probe table according to the data page list to be accessed. ​ comparing the traversed data with the data in the Hash table one by one, and confirming the data in the traversed data that matches the Hash table; collecting all the data that matches the Hash table, and generating a corresponding data query result. 8.A Hash Join query device based on data page index, comprising: a query condition acquisition module configured to determine a build table and a probe table to be connected and a connection key field; a build and index determination module configured to build a corresponding Hash table according to the data of the connection key field in the build table, and determine whether a data page index of the connection key field is pre-created in the probe table, the data page index being an index structure in units of at least one data page; an association filtering module configured to, in response to the data page index of the connection key field being pre-created in the probe table, associate the data page index of the connection key field with the Hash table, perform data page filtering on the probe table according to an association result, and generate a data page list to be accessed; a connection query module configured to read corresponding data in the probe table according to the data page list to be accessed, perform Hash Join with the Hash table, and generate a corresponding data query result. 9.A Hash Join query system based on data page index, comprising at least one processor; and a memory connected in communication with the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the Hash Join query method based on data page index according to any one of claims 1-7. 10.A non-volatile computer readable storage medium storing computer executable instructions, which, when executed by one or more processors, cause the one or more processors to perform the Hash Join query method based on data page index according to any one of claims 1-7.

Images