Hash Join query method, device, system and medium based on data page index
The Hash Join query method employs data page indexes to filter and reduce data access, addressing inefficiencies in large dataset queries and improving performance by associating data pages with Hash tables.
Patent Information
- Application Number
- CN202411116721.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-15
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2044-08-15
AI Technical Summary
The inefficiency of Hash Join queries in large datasets due to excessive data access and traversal, which affects the performance of multi-table join operations in databases.
A Hash Join query method utilizing data page indexes to filter and reduce data access by associating data page indexes with Hash tables, thereby improving query efficiency and performance.
Significantly reduces data access and IO operations, enhancing the efficiency and performance of Hash Join queries by filtering out unnecessary data pages using data page indexes.
Smart Images

Figure CN118626526B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to a Hash Join query method, device, system and medium based on data page index. Background Art
[0002] Multi-table association is a common SQL syntax in databases. After years of development, there are relatively mature Join algorithms, such as nest loop, merge Join, Hash Join, etc.
[0003] Currently, mainstream commercial databases and open source databases basically support the Hash Join algorithm. The general practice of Hash Join is to select a smaller table to load into memory, build a hash table, and then access the large table, compare it row by row with the rows in the hash table, and filter out the rows that meet the association conditions.
[0004] When querying multiple tables, if the amount of table data is large, when Hash Join is used to execute the query, the amount of data that the Hash table needs to access and the number of exploration and traversal are also very large, which reduces the efficiency of Hash Join and affects the performance of multi-table join query. Summary of the invention
[0005] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a Hash Join query method, device, system and medium based on data page index that can be applied to financial technology or other related fields, aiming to reduce the data exploration amount and IO access amount of Hash Join and improve the connection query efficiency and performance.
[0006] The technical solution of the present invention is as follows:
[0007] A Hash Join query method based on data page index, comprising:
[0008] Determine the build table and exploration table and the join key fields for the query to be joined;
[0009] Constructing a corresponding Hash table according to the data of the connection key field in the construction table, and determining whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index has an index structure with at least one data page as a unit;
[0010] If it exists, the data page index of the connection key field is associated with the Hash table, and the data page of the exploration table is filtered according to the association result to generate a list of data pages to be accessed;
[0011] The corresponding data in the exploration table is read according to the list of data pages to be accessed, and a Hash Join is performed with the Hash table to generate a corresponding data query result.
[0012] In one embodiment, before determining the construction table and the exploration table and the connection key field of the query to be connected, the method further includes:
[0013] Filter out tables and fields that frequently participate in Join based on database record information;
[0014] A data page index of the field is created on the filtered table according to a preset logical structure.
[0015] In one embodiment, the data page index includes a start data page id, an end data page id, a field name, and a maximum value and a minimum value of the field in each data page.
[0016] In one embodiment, associating the data page index of the connection key field with the Hash table, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed includes:
[0017] Associating the data page index of the connection key field with the Hash table, and comparing the maximum value and the minimum value of the connection key field in each data page with the connection key field in the Hash table;
[0018] Data pages that do not need to be accessed in the exploration table are filtered out according to the comparison result, and a list of data pages to be accessed is generated.
[0019] In one embodiment, after filtering out the tables and fields that frequently participate in Join according to the database record information, the method further includes:
[0020] According to the preset lower limit of the number of rows, the tables that frequently participate in Join are screened out for secondary screening.
[0021] In one embodiment, after creating the data page index of the field on the screened table according to the preset logical structure, the method further includes:
[0022] When the table where the field is located undergoes addition, deletion, or modification operations, the data page index of the field is updated in real time according to the operation results.
[0023] In one embodiment, reading the corresponding data in the exploration table according to the list of data pages to be accessed, performing a Hash Join with the Hash table, and generating corresponding data query results includes:
[0024] Traversing the data in a specified range in the exploration table according to the list of data pages to be accessed;
[0025] Compare the traversed data with the data in the Hash table one by one to confirm the data that matches the Hash table;
[0026] Summarize all data matching the Hash table and generate corresponding data query results.
[0027] A Hash Join query device based on data page index, comprising:
[0028] A query condition acquisition module is used to determine the construction table and exploration table and the connection key field of the query to be connected;
[0029] A construction and index determination module, configured to construct a corresponding hash table according to the data of the connection key field in the construction table, and determine whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index is an index structure with at least one data page as a unit;
[0030] An association filtering module, used for associating the data page index of the connection key field with the Hash table if it exists, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed;
[0031] The connection query module is used to read the corresponding data in the exploration table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate corresponding data query results.
[0032] A Hash Join query system based on data page index, the system comprising at least one processor; and
[0033] a memory communicatively connected to the at least one processor; wherein,
[0034] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the above-mentioned Hash Join query method based on data page index.
[0035] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned Hash Join query method based on data page index.
[0036] Beneficial effect: The present invention discloses a Hash Join query method, device, system and medium based on data page index. Compared with the prior art, the embodiments of the present invention associate a data page index corresponding to at least one data page with a Hash table first, and filter the data pages of the exploration table, thereby greatly reducing the data that needs to be accessed by the Hash table in subsequent Hash Joins, and improving the connection query efficiency and performance. BRIEF DESCRIPTION OF THE DRAWINGS
[0037] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:
[0038] Figure 1 A flow chart of a Hash Join query method based on data page index provided by an embodiment of the present invention;
[0039] Figure 2 A flow chart of constructing a data page index in a Hash Join query method based on a data page index provided in an embodiment of the present invention;
[0040] Figure 3 A flow chart of step S103 in the Hash Join query method based on data page index provided in an embodiment of the present invention;
[0041] Figure 4 A flow chart of step S104 in the Hash Join query method based on data page index provided in an embodiment of the present invention;
[0042] Figure 5 A schematic diagram of functional modules of a Hash Join query device based on data page index provided by an embodiment of the present invention;
[0043] Figure 6 A schematic diagram of the hardware structure of a Hash Join query system based on data page indexes provided in an embodiment of the present invention. DETAILED DESCRIPTION
[0044] In order to make the purpose, technical solution and effect of the present invention clearer and more specific, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below in conjunction with the accompanying drawings.
[0045] Multi-table association is a common SQL syntax in databases. After years of development, there are relatively mature Join algorithms, such as nest loop, merge Join, Hash Join, etc.
[0046] Currently, mainstream commercial databases and open source databases basically support the Hash Join algorithm. The general practice of Hash Join is to select a smaller table to load into memory, build a hash table, and then access the large table, compare it row by row with the rows in the hash table, and filter out the rows that meet the association conditions.
[0047] When querying multiple tables, if the amount of table data is large, when Hash Join is used to execute the query, the amount of data that the Hash table needs to access and the number of exploration and traversal are also very large, which reduces the efficiency of Hash Join and affects the performance of multi-table join query.
[0048] In order to solve the above problems, the present invention proposes a Hash Join query method based on data page index. In this embodiment, the method is applied to the OLAP (Online Analytical Processing) business scenario in the database field for illustration. Figure 1 As shown, the Hash Join query method based on data page index provided in this embodiment includes the following steps:
[0049] S101: Determine the construction table and exploration table and the connection key field of the query to be connected.
[0050] In this embodiment, the OLAP scenario is mainly based on the star model and the snowflake model, in which the fact table has a large amount of data and the dimension table has less data. The business SQL is mainly based on multi-table association statistical analysis, usually one fact table is associated with multiple dimension tables. When performing a multi-table connection query in the OLAP scenario, the corresponding query instruction can be input through the business SQL to determine the construction table and exploration table to be connected and the connection key field, in which the construction table is usually a table with a small amount of data, such as a dimension table, and the exploration table is a table with a large amount of data, such as a fact table. The construction table and the exploration table are connected through the connection key field as the key for connection between the two tables, so that the multi-table data can be matched and queried through the connection key field in the 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, etc., which is not limited in this embodiment.
[0051] S102, constructing a corresponding Hash table according to the data of the connection key field in the construction table, and determining whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index is an index structure with at least one data page as a unit.
[0052] In this embodiment, for a smaller build table, it 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, and a Hash table is constructed. The records in the build table are stored through the Hash table, so that the corresponding records can be quickly found according to the connection key field in the subsequent Hash Join. Specifically, the scope of constructing the Hash table can be all or part of the records in the build table. For example, when the date field is used as the association key field, the constructed Hash table can include records of all dates, or the records of the specified year can be selected from the build table through SQL statements, and the records of the specified year are stored in the memory and the corresponding Hash table is constructed. It can be flexibly set according to the actual query requirements, and this embodiment does not limit this.
[0053] After the Hash table is constructed, in order to improve the database query performance in the case of large data volume, this embodiment does not directly perform Hash Join with all the data in the exploration table, but first determines whether there is a page index of the pre-created connection key field in the exploration table, that is, a data page index, and the data page index is an index structure created in units of at least one data page. That is, when the business is idle, the database will automatically create a corresponding data page index for the fields in the exploration table with a large amount of data. Since one or more data pages correspond to a data page index, the structure volume of the data page index is small, which is convenient for resident memory to improve performance. By first confirming whether the data page index exists in the exploration table, when the data page index exists, it is possible to more quickly locate the data page in the exploration table where the record matching the Hash table is located, without scanning the entire exploration table, thereby improving query efficiency.
[0054] S103: If it exists, associate the data page index of the connection key field with the Hash table, filter the data pages of the search table according to the association result, and generate a list of data pages to be accessed.
[0055] In this embodiment, in the scenario of a small table joining a large table, if there is a data page index in the large table, the data page index is first associated with the Hash table, and the relevant data page information in the data page index is matched with the Hash table to find the matching data page record, so as to filter the data pages in the exploration table according to the association result, filter out the data pages that do not need to be accessed in the exploration table, and obtain a list of data pages 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 speed of the data page index and the Hash table is very fast, so that the pages that do not need to be accessed in the exploration table can be efficiently filtered out, which greatly reduces the data that needs to be accessed by the Hash table in the subsequent Hash Join and reduces data IO operations.
[0056] S104: Read the corresponding data in the exploration table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate corresponding data query results
[0057] In this embodiment, based on the list of data pages to be accessed obtained by page filtering, the data of the corresponding pages in the exploration table are read, and Hash Join is performed with the previously constructed Hash table, so as to accurately and efficiently obtain the data query results of multi-table connection through Hash Join within the partial page range of the exploration table. The exploration table is filtered by the constructed data page index, so as to greatly reduce the data that the Hash table needs to access, thereby improving the efficiency of Hash Join and improving the efficiency and performance of multi-table connection query.
[0058] In one embodiment, Figure 2 As shown, before determining the construction table and the exploration table and the connection key field of the query to be connected, the method also includes:
[0059] S201, filter out tables and fields that frequently participate in Join according to database record information;
[0060] S202: Create a data page index for the field on the screened table according to a preset logical structure.
[0061] In this embodiment, based on the recorded information of the database, such as all successfully executed SQLs recorded in the database or the statistical information of the database on historical Joins, etc., the frequency is used as a screening index to automatically analyze and screen out the tables and fields that frequently participate in Joins, for example, a frequency threshold is preset to screen out the tables and fields that participate in Joins with a frequency greater than the frequency threshold. For the screened tables and fields, the database automatically creates a page-level index according to the preset logical structure when the business is idle, that is, creates a data page index for the field. These data page indexes are stored in the memory to provide more accurate page location information when performing Hash Join queries on multiple tables, saving data access and reducing data IO operations.
[0062] Specifically, the logical structure of the data page index consists of 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. The second part of the logical structure is used to store metadata information of the data page index, including the starting data page id, the ending data page id, the field name, the maximum and minimum values of the field in each data page, and the global transaction number, etc., thereby providing an accurate and reliable index basis for data page filtering.
[0063] In one embodiment, after filtering out the tables and fields that frequently participate in Join according to the database record information, the method further includes:
[0064] According to the preset lower limit of the number of rows, the tables that frequently participate in Join are screened out for secondary screening.
[0065] In this embodiment, since the method of improving the efficiency of Hash Join by using data page index has limited effect on tables with small data volume, in order to improve resource utilization, after filtering out tables and fields that frequently participate in Join based on database record information, a secondary screening is performed based on the data volume, that is, a lower limit value of the number of rows is preset, such as 1 million, etc., and tables with a number of rows greater than the lower limit value are further screened out from tables that frequently participate in Join through the lower limit value of the number of rows, so that the operation of creating data page indexes is performed on tables that frequently participate in Join and have large data volume, ensuring the improvement of overall data query efficiency through data page indexes under limited resources.
[0066] In one embodiment, after creating the data page index of the field on the screened table according to the preset logical structure, the method further includes:
[0067] When the table where the field is located undergoes addition, deletion, or modification operations, the data page index of the field is updated in real time according to the operation results.
[0068] In this embodiment, for the data page index created by the filtered fields, the index information will be updated in real time as the data of the table where the field is located changes. Specifically, when the table is added, deleted, or modified, 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 and minimum values of the field in each data page, and the global transaction number, etc. By updating the data page index in real time when the table is added, deleted, or modified, it is ensured that the latest and most accurate data page index information is used to associate with the Hash table during Hash Join queries, thereby ensuring the accuracy and reliability of data page filtering.
[0069] Furthermore, to improve the utilization of memory resources, when a table is deleted, the page index corresponding to the first and second logical structures is automatically deleted; when the page index field is deleted, the page index corresponding to the first and second logical structures is automatically deleted; when the table is truncate, the page index corresponding to the second logical structure is automatically deleted to ensure the validity of the data page index in the memory.
[0070] In one embodiment, Figure 3 As shown, the data page index of the connection key field is associated with the Hash table, and the data page of the exploration table is filtered according to the association result to generate a list of data pages to be accessed, including:
[0071] S301, associating the data page index of the connection key field with the Hash table, and comparing the maximum value and the minimum value of the connection key field in each data page with the connection key field in the Hash table;
[0072] S302: Filter out data pages in the search table that do not need to be accessed according to the comparison result, and generate a list of data pages to be accessed.
[0073] In this embodiment, when the data page index of the connection key field is associated with the Hash table, the association can be performed through the corresponding SQL statement, and then a list of data page ids can be generated. Specifically, the data page id can be used as the 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 certain 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, it is a data page that does not need to be accessed and needs to be filtered out. A list of data pages to be accessed is generated based on 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 exploration table can be determined first, so that only these pages are scanned when the Hash Join is subsequently executed, instead of the entire exploration table, which greatly reduces the amount of data exploration, thereby effectively improving the query efficiency.
[0074] In one embodiment, Figure 4 As shown, the corresponding data in the exploration table is read according to the list of data pages to be accessed, and a Hash Join is performed with the Hash table to generate corresponding data query results, including:
[0075] S401, traversing the data in a specified range in the exploration table according to the list of data pages to be accessed;
[0076] S402, comparing the traversed data with the data in the Hash table one by one, and confirming the data that matches the Hash table;
[0077] S403: Summarize all data matching the Hash table and generate corresponding data query results.
[0078] In this embodiment, after obtaining the list of data pages to be accessed through the data page index and the Hash table association filtering, the data pages that do not need to be accessed in the exploration table can be largely filtered out, and the data page list gives the specified range that needs to be completed in the Hash table normally. The data in the specified range of the exploration table is traversed based on the data page list to be accessed, and the traversed data is compared with the data in the Hash table one by one to confirm the data matching the Hash table. Specifically, the same Hash function is used to generate a Hash value for the traversed data in the exploration table, and then the corresponding record is searched in the Hash table. If the record in the exploration table finds a match in the Hash table, then the two records are a pair of matching connection results. All matching records are combined, that is, all data matching the Hash table are summarized, and the corresponding multi-table connection data query results can be generated. On the basis of quickly locating records by using the Hash table, the data page index is combined to reduce the data that needs to be accessed, and the Hash Join efficiency is further improved. Even in the query scenario with a large amount of table data, high query efficiency can still be maintained.
[0079] It should be noted that there is not necessarily a certain order between the above-mentioned steps. A person skilled in the art can understand, based on the description of the embodiments of the present invention, that in different embodiments, the above-mentioned steps may have different execution orders, that is, they may be executed in parallel, may be executed interchangeably, and so on.
[0080] In order to better understand the implementation process of the Hash Join query method based on data page index provided in the embodiment of the present invention, the implementation process of the Hash Join query method based on data page index is introduced below by taking a specific application embodiment as an example:
[0081] Currently, there are two tables, lineorder and dwdate, both from the SSBM model. Lineorder is a fact table with a large amount of data, and dwdate is a dimension table with a small amount of data that can be put into memory. The table structure is as follows:
[0082] --Date dimension table
[0083] create table dates (
[0084] d_datekey integer,
[0085] d_date varchar(18) not null,
[0086] d_dayofweek varchar(18) not null,
[0087] d_month varchar(9) not null,
[0088] d_year integer not null,
[0089] d_yearmonthnum integer,
[0090] d_yearmonth varchar(7) not null,
[0091] d_daynuminweek integer,
[0092] d_daynuminmonth integer,
[0093] d_daynuminyear integer,
[0094] d_monthnuminyear integer,
[0095] d_weeknuminyear integer,
[0096] d_sellingseason varchar(12) not null,
[0097] d_lastdayinweekfl integer,
[0098] d_lastdayinmonthfl integer,
[0099] d_holidayfl integer,
[0100] d_weekdayfl integer );
[0102] -- Order table
[0103] create table lineorder (
[0104] lo_orderkey bigint,
[0105] lo_linenumber bigint,
[0106] lo_custkey integer not null,
[0107] lo_partkey integer not null,
[0108] lo_suppkey integer not null,
[0109] lo_orderdate integer not null,
[0110] lo_orderpriotity varchar ( 15 ) not null ,
[0111] lo_shippriotity integer,
[0112] lo_quantity begins,
[0113] lo_extendedprice begins,
[0114] lo_wordtotalprice begins,
[0115] lo_discount begins,
[0116] lo_revenue begins,
[0117] lo_supplycost begins,
[0118] lo_tax begins,
[0119] lo_commitdate integer not null,
[0120] lo_shipmode varchar ( 10 ) not null );
[0122] --Any SQL database 2023,2024 versions
[0123] select d_year,sum(lo_revenue)
[0124] from lineorder, dwdate
[0125] where lo_orderdate = d_datekey
[0126] and d_year in ('2023','2024')
[0127] group by d_year;
[0128] Note: The table lineorder field lo_orderdate has a page index created in advance
[0129] That is, in this embodiment, the lineorder table is used as the exploration table, the dwdate table is used as the construction table, and the connection conditions and filter conditions are specified, that is, the lo_orderdate field in the lineorder table must match the d_datekey field in the dwdate table, the date fields in the two tables are used as the connection key fields, and only the records whose d_year field is 2023 or 2024 are selected.
[0130] Based on the query requirements of the above business SQL, the Hash Join query process is:
[0131] Step 1: When the business is not busy, create a page index on the lo_orderdate field. For the convenience of subsequent use, name it lineorder_pi_1, which contains the minimum and maximum lo_orderdate values (c_min and c_max) of each page. Keep the index in memory.
[0132] Step 2: Generate SQL to access the dimension table dwdate based on the above business SQL:
[0133] select * from dwdate where d_year in ('2023','2024')
[0134] That is, select all records with the year 2023 or 2024 from the dwdate table, read the results into memory, and construct a Hash table;
[0135] Step 3: Use the page index on the lo_orderdate field to associate with the Hash table built in step 2. The associated SQL is as follows:
[0136] select pid
[0137] from linerorder_pi_1 a,dwdate b
[0138] where b.d_datekey >= a.c_min
[0139] and b.d_datekey <= a.c_max;
[0140] The above SQL generates a page list of the lineorder table by comparing the d_datekey field of the dwdate table with the lo_orderdate field of the lineorder table. The page list is a list of all lineorder page IDs that match the specified year in the dwdate table.
[0141] Step 4: Read the lineorder data according to the page list generated in step 3, and associate them with the hash table built in step 2 one by one until all page lists in step 3 are read;
[0142] Step 5: Aggregate the data based on d_year and return to the client.
[0143] In this embodiment, since the page index and the data page are in a one-to-many relationship, the page index can be kept small, easy to reside in memory, and associated with the Hash table very quickly without any negative impact. By first associating the page index with the Hash table in step 2 and returning the page list, the lineorder data is effectively filtered, thereby reducing the data that the Hash table needs to access. For example, assuming that the lineorder stores 10 years of data, nearly 80% of the data will be filtered out in this embodiment under ideal conditions, so that the large table IO will be greatly reduced in the subsequent Hash Join, thereby effectively improving the Hash Join performance.
[0144] Another embodiment of the present invention provides a Hash Join query device based on data page index, such as Figure 5 As shown, the device 1 comprises:
[0145] A query condition acquisition module 11 is used to determine the construction table and the exploration table and the connection key field to be queried;
[0146] A construction and index determination module 12 is used to construct a corresponding hash table according to the data of the connection key field in the construction table, and determine whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index is an index structure with at least one data page as a unit;
[0147] An association filtering module 13 is used to associate the data page index of the connection key field with the Hash table if it exists, and to filter the data pages of the exploration table according to the association result to generate a list of data pages to be accessed;
[0148] The connection query module 14 is used to read the corresponding data in the exploration table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate corresponding data query results.
[0149] The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions, which is more suitable for describing the execution process of Hash Join query based on data page index than a program. For the specific implementation of each module, please refer to the corresponding method embodiment above, which will not be repeated here.
[0150] Another embodiment of the present invention provides a Hash Join query system based on data page index, such as Figure 6 As shown, the system 10 includes:
[0151] One or more processors 110 and memory 120, Figure 6 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 6 The example of connecting through bus is taken in the following.
[0152] The processor 110 is used to complete various control logics of the system 10, and it can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional 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, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.
[0153] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the HashJoin query method based on data page index in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, implements the HashJoin query method based on data page index in the above method embodiment.
[0154] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may 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 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the system 10 via 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.
[0155] One or more units are stored in the memory 120, and when executed by one or more processors 110, the following steps are implemented:
[0156] Determine the build table and exploration table and the join key fields for the query to be joined;
[0157] Constructing a corresponding Hash table according to the data of the connection key field in the construction table, and determining whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index has an index structure with at least one data page as a unit;
[0158] If it exists, the data page index of the connection key field is associated with the Hash table, and the data page of the exploration table is filtered according to the association result to generate a list of data pages to be accessed;
[0159] The corresponding data in the exploration table is read according to the list of data pages to be accessed, and a Hash Join is performed with the Hash table to generate a corresponding data query result.
[0160] In one embodiment, before determining the construction table and the exploration table and the connection key field of the query to be connected, the method further includes:
[0161] Filter out tables and fields that frequently participate in Join based on database record information;
[0162] A data page index of the field is created on the filtered table according to a preset logical structure.
[0163] In one embodiment, the data page index includes a start data page id, an end data page id, a field name, and a maximum value and a minimum value of the field in each data page.
[0164] In one embodiment, associating the data page index of the connection key field with the Hash table, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed includes:
[0165] Associating the data page index of the connection key field with the Hash table, and comparing the maximum value and the minimum value of the connection key field in each data page with the connection key field in the Hash table;
[0166] Data pages that do not need to be accessed in the exploration table are filtered out according to the comparison result, and a list of data pages to be accessed is generated.
[0167] In one embodiment, after filtering out the tables and fields that frequently participate in Join according to the database record information, the method further includes:
[0168] According to the preset lower limit of the number of rows, the tables that frequently participate in Join are screened out for secondary screening.
[0169] In one embodiment, after creating the data page index of the field on the screened table according to the preset logical structure, the method further includes:
[0170] When the table where the field is located undergoes addition, deletion, or modification operations, the data page index of the field is updated in real time according to the operation results.
[0171] In one embodiment, reading the corresponding data in the exploration table according to the list of data pages to be accessed, performing a Hash Join with the Hash table, and generating corresponding data query results includes:
[0172] Traversing the data in a specified range in the exploration table according to the list of data pages to be accessed;
[0173] Compare the traversed data with the data in the Hash table one by one to confirm the data that matches the Hash table;
[0174] Summarize all data matching the Hash table and generate corresponding data query results.
[0175] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S101 to S104.
[0176] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.
[0177] In summary, in the Hash Join query method, device, system and medium based on data page index disclosed in the present invention, the method determines the construction table and exploration table and the connection key field to be queried; constructs the corresponding Hash table according to the data of the connection key field in the construction table, and determines whether there is a pre-created data page index of the connection key field in the exploration table, and the data page index is an index structure with at least one data page as a unit; if it exists, the data page index of the connection key field is associated with the Hash table, and the exploration table is filtered according to the association result to generate a list of data pages to be accessed; read the corresponding data in the exploration table according to the list of data pages to be accessed, and perform Hash Join with the Hash table to generate the corresponding data query result. By first associating the data page index corresponding to at least one data page with the Hash table, filtering the data page of the exploration table, the data that needs to be accessed by the Hash table in the subsequent Hash Join is greatly reduced, and the efficiency and performance of the connection query are improved.
[0178] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.
[0179] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.
Claims
1. A Hash Join query method based on data page index, characterized in that: include: Determine the build table and exploration table and the join key fields for the query to be joined; Construct a corresponding Hash table according to the data of the connection key field in the construction table, and determine whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index is an index structure with at least one data page as a unit, and the data page index includes a starting data page id, an ending data page id, a field name, and a maximum value and a minimum value of the field in each data page; If it exists, the data page index of the connection key field is associated with the Hash table, and the data page of the exploration table is filtered according to the association result to generate a list of data pages to be accessed; Read the corresponding data in the exploration table according to the list of data pages to be accessed, perform HashJoin with the Hash table, and generate corresponding data query results; The step of associating the data page index of the connection key field with the Hash table, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed includes: Associating the data page index of the connection key field with the Hash table, and comparing the maximum value and the minimum value of the connection key field in each data page with the connection key field in the Hash table; Data pages that do not need to be accessed in the exploration table are filtered out according to the comparison result, and a list of data pages to be accessed is generated.
2. The Hash Join query method based on data page index according to claim 1, characterized in that: Before determining the construction table and the exploration table and the connection key field of the query to be connected, the method further includes: Filter out tables and fields whose frequency of participating in Join is greater than the preset frequency threshold based on database record information; A data page index of the field is created on the filtered table according to a preset logical structure.
3. The Hash Join query method based on data page index according to claim 2 is characterized in that: After filtering out the tables and fields whose frequency of participating in Join is greater than a preset frequency threshold according to the database record information, the method further includes: According to the preset lower limit of the number of rows, a secondary screening is performed on the tables whose frequency of participating in Join is greater than the preset frequency threshold.
4. The Hash Join query method based on data page index according to claim 2, characterized in that: After creating the data page index of the field on the screened table according to the preset logical structure, the method further includes: When the table where the field is located undergoes addition, deletion, or modification operations, the data page index of the field is updated in real time according to the operation results.
5. The Hash Join query method based on data page index according to claim 1, characterized in that: The step of reading the corresponding data in the exploration table according to the list of data pages to be accessed, performing a Hash Join with the Hash table, and generating a corresponding data query result includes: Traversing the data in a specified range in the exploration table according to the list of data pages to be accessed; Compare the traversed data with the data in the Hash table one by one to confirm the data that matches the Hash table; Summarize all data matching the Hash table and generate corresponding data query results.
6. A Hash Join query device based on data page index, characterized in that: include: A query condition acquisition module is used to determine the construction table and exploration table and the connection key field of the query to be connected; A construction and index determination module, used to construct a corresponding Hash table according to the data of the connection key field in the construction table, and determine whether there is a pre-created data page index of the connection key field in the exploration table, wherein the data page index is an index structure with at least one data page as a unit, and the data page index includes a starting data page id, an ending data page id, a field name, and a maximum value and a minimum value of the field in each data page; An association filtering module, used for associating the data page index of the connection key field with the Hash table if it exists, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed; A connection query module is used to read the corresponding data in the exploration table according to the list of data pages to be accessed, perform Hash Join with the Hash table, and generate corresponding data query results; The step of associating the data page index of the connection key field with the Hash table, filtering the data pages of the exploration table according to the association result, and generating a list of data pages to be accessed includes: Associating the data page index of the connection key field with the Hash table, and comparing the maximum value and the minimum value of the connection key field in each data page with the connection key field in the Hash table; Data pages that do not need to be accessed in the exploration table are filtered out according to the comparison result, and a list of data pages to be accessed is generated.
7. A Hash Join query system based on data page index, characterized in that: The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the Hash Join query method based on data page index as described in any one of claims 1-5.
8. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the Hash Join query method based on data page index as described in any one of claims 1-5.
Citation Information
Patent Citations
Processing connection query method and device
CN107784030A
Multi-source heterogeneous data association query method and system
CN110837585A
