Histogram-based query optimization method and device, equipment and medium

Abstract

The invention relates to the technical field of big data and the field of financial science and technology, and discloses a histogram-based query optimization method, which comprises the following steps: determining database tables and associated fields thereof, generating histograms of the associated fields, comparing data distribution densities of the associated fields, and selecting the database table with the most sparse data distribution density as a propulsion reference table. Traversing associated field data of the reference table item by item, generating a matching range in combination with the distribution information of the non-reference table, executing a matching operation in the matching range, and if the matching fails, generating a new matching range according to the next data item of the reference table and executing the matching operation, and finally, ending the operation and generating a query result set when the traversal of the data items is completed or a matching termination condition is met. According to the method, the data distribution characteristics of the associated fields are accurately described through the histogram, dynamic optimization of the matching range is achieved, full-table scanning and unnecessary matching operation are effectively avoided, the execution efficiency of connection query is remarkably improved, and meanwhile the resource occupancy rate is reduced.

Description

Technical Field

[0001] The present invention relates to the field of big data technology and financial technology, and in particular to a histogram-based query optimization method, device, equipment and storage medium. Background Art

[0002] In database technology, join query is one of the basic functions for realizing association operations between data sets. Through join query, users can obtain related data from multiple tables to meet complex business needs. Especially in the financial field, join query is widely used in scenarios such as matching customer information with transaction records, reconciling account flow with bills, and data integration of financial risk analysis models. These operations usually place high demands on query performance and execution efficiency.

[0003] At present, the join query implementation methods in databases include Nested Loop Join, Hash Join and Merge Join. Among them, Merge Join is a data processing method based on sorting and step-by-step merging, which is particularly suitable for efficient query of large-scale data sets. Its operation process usually includes: first sorting the tables involved in the connection according to the connection key, and then matching the sorted data one by one to improve the query performance. Due to the advantages of Merge Join in reducing memory usage and avoiding repeated scanning, it is widely used in large-scale data queries in the financial field.

[0004] Although Merge Join has high query efficiency, its performance is still limited by the following factors in practical applications:

[0005] Data distribution affects matching efficiency: When the data distribution of the join key is highly uneven, some data blocks may participate in more matching operations, resulting in decreased query performance;

[0006] The determination of the matching range lacks dynamism: Traditional Merge Join usually determines the matching range based on fixed rules and algorithms, but fails to optimize the actual distribution of data;

[0007] Redundant matching operations: In some scenarios, the distribution information of the data is not fully utilized, resulting in low efficiency of matching operations and increased query time.

[0008] Therefore, in the existing Merge Join method, the mechanism for optimizing data distribution characteristics is still imperfect, and the statistical information of the join key fields (such as the distribution range of the fields, bucket density, etc.) is not fully utilized, and the matching range cannot be effectively optimized. In the actual query process, there is a lack of the ability to dynamically adjust the advancement and matching range according to the data distribution density, which easily leads to resource waste or performance bottlenecks. When the matching operation fails, it is difficult for the existing method to efficiently determine the new matching range, thereby increasing the traversal time. Summary of the invention

[0009] The main purpose of the present invention is to provide a histogram-based query optimization method, device, equipment and storage medium, aiming to solve the technical problem in the prior art that when executing a connection query, the distribution statistical information of the associated fields is not effectively utilized to optimize the matching range and query strategy, resulting in low query efficiency and poor resource utilization.

[0010] To achieve the above object, the present invention provides a histogram-based query optimization method, comprising:

[0011] Based on the merge connection query task, determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection;

[0012] Generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively;

[0013] Comparing data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0014] Selecting a database table with the sparsest data distribution density from among the first database table and the second database table as a promotion reference table, and using database tables other than the promotion reference table as non-reference tables;

[0015] Traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram record corresponding to the non-reference table, and performing matching operations in sequence within the matching range;

[0016] If the current matching operation fails, a new matching range is generated according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and a matching operation is performed within the new matching range;

[0017] When the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set.

[0018] Furthermore, to achieve the above object, the present invention provides a query optimization device based on a histogram, comprising:

[0019] A task parsing module, used to determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection based on the merge connection query task;

[0020] A histogram generating module, used to generate a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively;

[0021] A distribution density comparison module, configured to compare data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0022] A promotion benchmark selection module, configured to select a database table with the sparsest data distribution density from the first database table and the second database table as a promotion benchmark table, and to use database tables other than the promotion benchmark table as non-benchmark tables;

[0023] A matching range generation and matching module, used to traverse and extract data items of associated fields in the advancement benchmark table one by one, generate a matching range in combination with distribution information of the histogram records corresponding to the non-benchmark table, and perform matching operations in sequence within the matching range;

[0024] A matching range adjustment module, for generating a new matching range according to the next data item of the associated field in the advancement reference table in combination with the histogram corresponding to the non-reference table if the current matching operation fails, and performing the matching operation within the new matching range;

[0025] The result generation module is used to end the matching operation when the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, and merge the successfully matched records to generate a query result set.

[0026] Furthermore, to achieve the above-mentioned purpose, the present invention also provides a computer device, which includes a memory, a processor, and a histogram-based query optimization program stored in the memory and executable on the processor, wherein the histogram-based query optimization program, when executed by the processor, implements the steps of the histogram-based query optimization method as described above.

[0027] Furthermore, to achieve the above-mentioned purpose, the present invention also provides a computer-readable storage medium, on which a histogram-based query optimization program is stored, and when the histogram-based query optimization program is executed by a processor, the steps of the histogram-based query optimization method as described above are implemented.

[0028] Beneficial effects: The present invention relates to the fields of big data technology and financial technology, and discloses a query optimization method based on a histogram, which determines a database table and its associated fields, generates a histogram of the associated fields, compares the data distribution density of the associated fields, selects the database table with the sparsest data distribution density as the advancing reference table, traverses the associated field data of the reference table item by item and generates a matching range in combination with the distribution information of the non-reference table, performs a matching operation within the matching range, and if the match fails, generates a new matching range according to the next data item of the reference table and performs a matching operation, and finally terminates the operation and generates a query result set when the data item traversal is completed or the matching termination condition is met. The present invention accurately describes the data distribution characteristics of the associated fields through a histogram, realizes dynamic optimization of the matching range, effectively avoids full table scanning and unnecessary matching operations, significantly improves the execution efficiency of connection queries, and reduces resource occupancy. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0030] Figure 1 A schematic diagram of an application environment of a histogram-based query optimization method in an embodiment of the present invention;

[0031] Figure 2 It is a flow chart of an embodiment of a query optimization method based on a histogram of the present invention;

[0032] Figure 3 It is a functional module schematic diagram of a preferred embodiment of a query optimization device based on a histogram of the present invention;

[0033] Figure 4 A schematic diagram of the structure of a computer device in one embodiment of the present invention;

[0034] Figure 5 FIG. 4 is another schematic diagram of the structure of a computer device in one embodiment of the present invention. DETAILED DESCRIPTION

[0035] It should be understood that the specific embodiments described herein are only used to explain the present invention, and are not used to limit the present invention.

[0036] The query optimization method based on histogram provided by the embodiment of the present invention can be applied in Figure 1In an application environment, the user terminal communicates with the server terminal through a network. The server terminal can determine the database table and its associated fields through the user terminal, generate a histogram of the associated fields, compare the data distribution density of the associated fields, select the database table with the sparsest data distribution density as the advancing reference table, traverse the associated field data of the reference table item by item and generate a matching range in combination with the distribution information of the non-reference table, perform a matching operation within the matching range, and if the match fails, generate a new matching range according to the next data item of the reference table and perform a matching operation, and finally terminate the operation and generate a query result set when the data item traversal is completed or the matching termination condition is satisfied. The present invention accurately describes the data distribution characteristics of the associated fields through a histogram, realizes dynamic optimization of the matching range, effectively avoids full table scanning and unnecessary matching operations, significantly improves the execution efficiency of the connection query, and reduces resource occupancy. Among them, the user terminal can be but is not limited to various personal computers, laptops, smart phones, tablet computers and portable wearable devices. The server terminal can be implemented with an independent server or a server cluster composed of multiple servers. The present invention is described in detail below through specific embodiments.

[0037] See also Figure 2 , Figure 2 This is a flow chart of an embodiment of a query optimization method based on a histogram provided by the present invention. It should be noted that although a logical sequence is shown in the flow chart, in some cases, the steps shown or described may be performed in a different order than that shown here.

[0038] like Figure 2 As shown, the histogram-based query optimization method proposed by the present invention includes the following steps:

[0039] S10, determining the first database table, the second database table, and the associated fields in the first database table and the second database table for connection based on the merge connection query task;

[0040] In this embodiment, the query task refers to a logical description, which is used to define a connection operation between two tables in a database through field association. This operation involves two database tables, and the names of the two tables need to be clarified from the task.

[0041] The first database table refers to the table that is first mentioned in the task or plays a leading role in the connection, usually the primary table, such as the "order table" in the business query. The second database table refers to the table that participates in the connection as a subsidiary in the task, usually the foreign table, such as the "customer table" in the business query.

[0042] The names of database tables are usually explicitly reflected in query tasks through syntax or configuration, such as the FROM and JOIN clauses in SQL statements.

[0043] Use SQL parsing tools (such as Apache Calcite) to parse the statements in the query task and extract the table names in FROM and JOIN:

[0044] SELECT*FROM orders o JOIN customers c ON o.customer_id=c.customer_id;

[0045] In this task, orders is parsed as the first database table and customers is the second database table.

[0046] If the query task is a non-SQL expression, such as a script or configuration file, you can manually specify the names of the two tables in the task.

[0047] The associated field is the field that defines the connection between two tables, usually a match between a foreign key and a primary key. The task requires that the field names used for the connection in the two tables be clearly stated.

[0048] The associated fields can appear directly in the join conditions of the query task, or be obtained indirectly through metadata information. The associated fields usually need to be guaranteed to have the same data type (such as integer, string) in both tables. The associated fields need to have the same set of values ​​in both tables to support matching operations, for example, customer_id in the orders table references the primary key in the customers table.

[0049] Use SQL parsing tools to extract the field mapping relationship in the ON clause:

[0050] SELECT*FROM orders o JOIN customers c ON o.customer_id=c.customer_id;

[0051] The associated fields extracted in this task are o.customer_id and c.customer_id.

[0052] If the task does not explicitly specify the associated fields, you can query the metadata of the database table to find the definition of the primary and foreign key constraints: SHOW CREATE TABLE orders;

[0053] The query results show that customer_id is the foreign key field.

[0054] After identifying the two tables and their associated fields, you need to define the roles of the two tables for subsequent processing. The first database table is usually a candidate for the promotion benchmark table; the second database table is a candidate for the non-baseline table. Roles can be dynamically assigned based on the number of rows in the table, task requirements, data distribution, etc. For example, a table with fewer records can be selected as the promotion benchmark table to reduce processing overhead.

[0055] Assign roles based on query task requirements, for example, set the orders table as the first database table and the customers table as the second database table. Select a table with fewer records as the first database table based on database statistics.

[0056] Example description: In financial applications, database join query is an important basis for implementing complex business logic. For example, in a risk assessment scenario, it is necessary to merge the loan record table and the customer information table to assess the customer's credit risk. Financial institutions need to analyze the customer's historical loan behavior and combine the customer's basic information (such as age, occupation, assets, etc.) to generate a comprehensive credit score report. This requires a merge join query on the following two tables:

[0057] Loan records table (loan_records):

[0058] Field examples: loan_id (loan number), customer_id (customer number), loan_amount (loan amount), loan_date (loan date).

[0059] Customer information table (customer_info):

[0060] Field examples: customer_id (customer number), name (customer name), age (age), income (income level).

[0061] Query the basic information of each customer's loan and associate it with the customer's income level to calculate the loan-to-income ratio. The example query task is as follows:

[0062] SELECT

[0063] l.loan_id,

[0064] l.loan_amount,

[0065] l.loan_date,

[0066] c.name,

[0067] c.age,

[0068] c.income

[0069] FROM

[0070] loan_records

[0071] JOIN

[0072] customer_info c

[0073] ON

[0074] l.customer_id=c.customer_id;

[0075] The table names loan_records and customer_info are parsed from the SQL query and used as the first and second database tables respectively. loan_records is a transaction data table related to loans, and customer_info is a static basic information table for customers.

[0076] Extract customer_id in the ON clause and determine that this field is the association field connecting the two tables. customer_id is the customer ID and has the same data type in the two tables, ensuring the matching of the fields.

[0077] If the number of records in the loan_records table is much larger than that in the customer_info table, then the customer_info table with fewer records is selected as the advancement base table to reduce the matching overhead.

[0078] Assume that loan_records contains millions of loan records, while customer_info contains only hundreds of thousands of customer data, then set the customer_info table as the promotion base table.

[0079] By parsing and merging connection query tasks, the two database tables and their connection fields that need to participate in the query operation can be automatically identified, avoiding the error-proneness of manual configuration and improving processing efficiency and accuracy.

[0080] S20, generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively;

[0081] In this embodiment, for the associated fields in the first database table, the data distribution characteristics are used to perform statistical analysis to generate a histogram to represent the data distribution. The histogram is usually composed of a bucket range, a bucket number, a bucket width, and the number of data items in each bucket.

[0082] Extract the associated field data from the first database table and count the minimum and maximum values ​​of the field. Divide the bucket range and calculate the number of buckets based on preset rules (such as fixed width or dynamic width). Traverse the field data, classify each data item into the corresponding bucket, and record the number of data items in each bucket. Summarize the above information to build a histogram of the first associated field, including bucket range, bucket width, bucket number and distribution density.

[0083] Similar to the first database table, statistical analysis is performed on the associated fields in the second database table to generate a histogram of data distribution. The minimum and maximum values ​​of the associated fields are obtained, and buckets are divided according to the field value range and preset rules. The field data is traversed to count the number of data items in each bucket. The information such as bucket range, bucket width and distribution density is constructed as a histogram of the second associated field.

[0084] Ensure that the histograms of the first database table and the second database table are comparable (for example, the number of buckets is the same and the bucket ranges are aligned).

[0085] Select the global range (the minimum and maximum values ​​of the fields in the two tables) to divide the buckets.

[0086] In view of the differences in distribution density between different tables, the data density is normalized proportionally to ensure consistent dimensions when comparing.

[0087] Example: In risk analysis of financial institutions, it is usually necessary to associate transaction records with customer credit tables to assess transaction anomalies. For example:

[0088] Transaction record table (transactions):

[0089] Field examples: transaction_id (transaction number), customer_id (customer number), amount (transaction amount).

[0090] Customer credit table (credit_scores):

[0091] Field examples: customer_id (customer number), credit_score (credit score).

[0092] A financial institution wants to count high-risk transactions of its customers (for example, records with low credit scores and large transaction amounts). To do this, it is necessary to generate histograms of the two tables.

[0093] Transaction record table (transactions):

[0094] Field: customer_id

[0095] Histogram contents:

[0096] Bucket range: such as 1-100, 101-200.

[0097] Number of buckets: 10 buckets.

[0098] Distribution density: the number of transactions in each bucket.

[0099] Customer credit table (credit_scores):

[0100] Field: customer_id

[0101] Histogram contents:

[0102] Bucket range: aligned with the transactions table.

[0103] Number of buckets: 10 buckets.

[0104] Distribution density: the number of customers in each bucket.

[0105] By generating histograms for the associated fields of the first database table and the second database table, the data distribution can be accurately modeled, providing effective support for the comparison of the distribution density of cross-table data and the selection of the benchmark table, avoiding the errors caused by the simple assumptions of the traditional method on the data distribution. At the same time, the histogram structure compactly represents large-scale data sets with a small number of buckets and statistical information, effectively reducing storage and computing costs.

[0106] S30, comparing data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0107] In this embodiment, the bucket range, bucket quantity, bucket width and the number of data items in each bucket of the associated fields in the first database table and the second database table are recorded respectively through the histogram constructed in the early stage.

[0108] Data distribution density refers to the ratio of the number of data items in each bucket to the width of the bucket, which is used to measure the density of data in a bucket. Based on the bucket information of each histogram, the density value, average density, maximum density, minimum density and distribution deviation of each bucket are calculated to fully describe the distribution characteristics of the two related fields. The density information of the two histograms is compared item by item, including the average density, maximum density, minimum density and distribution deviation, to generate detailed distribution density comparison results as an important basis for promoting the selection of the benchmark table.

[0109] The following information is extracted from the first associated field histogram and the second associated field histogram: bucket range, bucket number, bucket width, and the number of data items in each bucket. This step can be achieved by parsing metadata information of the histogram.

[0110] For each bucket, use the formula:

[0111]

[0112] Calculate the density values ​​of all buckets and count the average density, maximum density, minimum density and distribution deviation of the histogram.

[0113] Compare the density information of the two histograms item by item to analyze the density changes and data distribution characteristics, such as the distribution characteristics of dense and sparse areas. The comparison results are stored as a density comparison table, including the average density difference, maximum density difference, and distribution deviation difference. The comprehensive comparison results generate a density analysis report, which is used to select the benchmark table for advancement in the subsequent steps.

[0114] Example description: In financial data analysis, suppose you need to merge and query the "User Transaction Record Table" and the "Risk Assessment Table" based on the user ID field. This can be achieved by following the steps below:

[0115] Generate a histogram of the user ID field in the two tables. The ID distribution in the user transaction record table is dense, while the ID distribution in the risk assessment table is relatively sparse.

[0116] By comparing the histogram density information, it is found that the risk assessment table has a low average density and a small distribution deviation, which is suitable as a benchmark table for advancement and reduces the number of traversals.

[0117] Use density difference results to guide the selection of promotion tables, reduce repeated calculations in high-density areas in actual queries, significantly improve query efficiency, and ensure the integrity and accuracy of data integration.

[0118] The histogram-based method for comparing the distribution density of associated field data can quickly and accurately evaluate the distribution characteristics of associated fields in different tables, provide a scientific basis for promoting the selection of benchmark tables, and significantly reduce unnecessary traversal and matching operations during the query process, thereby improving query efficiency and reducing computing resource consumption.

[0119] S40, selecting a database table with the sparsest data distribution density from the first database table and the second database table as a promotion reference table, and using database tables other than the promotion reference table as non-reference tables;

[0120] In this embodiment, based on the histogram of the associated fields in the first database table and the second database table, the table with the sparsest data distribution density is determined by density comparison. Sparsity is usually evaluated based on the following indicators:

[0121] Average density: The average value of all bucket density values ​​in the histogram.

[0122] Maximum density: The bucket with the largest density value in the histogram.

[0123] Distribution bias: The degree of dispersion of density distribution in the histogram.

[0124] The advancing reference table refers to the table that is preferentially traversed item by item in the matching operation. The selection logic is as follows:

[0125] If the average density of the first database table is less than that of the second database table, the first database table is the advancement reference table;

[0126] If the average densities are the same, compare the maximum densities, and the smaller one is used as the benchmark for advancement;

[0127] If the density index is exactly the same, the base table can be selected for advancement based on the number of rows in the table or the user-specified priority.

[0128] The non-reference table is a database table other than the advancement reference table, and is usually used to assist in positioning and matching within the matching range.

[0129] In the specific implementation process, the average density, maximum density, minimum density, distribution deviation and other information can be extracted from the first associated field histogram and the second associated field histogram, recorded as a density table, and the density information of the two tables is compared. According to the above logic, the following are judged in turn:

[0130] If the average density of the second database table is less than the average density of the first database table, the second database table is selected as the advancement reference table;

[0131] If the average densities are the same and the maximum density of the second database table is less than the maximum density of the first database table, the second database table is selected as the advancement reference table;

[0132] Otherwise the first database table is selected.

[0133] The selected advancing benchmark tables and non-benchmark tables are marked and passed to the modules for traversal and matching in subsequent steps.

[0134] Example description: In the bank customer transaction analysis, it is necessary to merge the "Transaction Flow Table" and the "Credit Risk Assessment Table" for query, and associate them based on the customer ID field.

[0135] Step 1: Generate histograms of the two tables and obtain density information for the customer ID field:

[0136] Transaction flow table: average density is 4.5, maximum density is 10.0, and distribution deviation is 2.3.

[0137] Credit risk assessment table: average density is 1.8, maximum density is 4.5, and distribution deviation is 1.1.

[0138] Step 2: The analysis results show that the credit risk assessment table is more sparse, so it is selected as the advancement benchmark table.

[0139] Step 3: When querying, give priority to traversing the credit risk assessment table item by item, use the transaction flow table as an auxiliary positioning table, and ultimately achieve efficient matching operations.

[0140] By selecting the advancing reference table based on data distribution density, the sparsity can be fully utilized to reduce the computational complexity of the query operation. Selecting a sparse advancing reference table helps to reduce unnecessary data scanning and matching processes in the matching operation, thereby improving query efficiency and reducing resource consumption.

[0141] S50, traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram records corresponding to the non-reference table, and performing matching operations in sequence within the matching range;

[0142] In this embodiment, starting from the advancing reference table, each data item of the associated field is read in sequence to ensure that no item is missed in the sequential traversal. After each reading, the current data item is used as the input of the matching operation.

[0143] Based on the distribution information of the non-reference table histogram, including the bucket range, bucket width, bucket number, and density value of each bucket, the possible matching range of the current data item of the advancement reference table in the non-reference table is calculated. The matching range is determined by locating the bucket to which the current data item of the advancement reference table belongs in the histogram and expanding it to a certain range (such as adjacent buckets).

[0144] For the calculated matching range, read the data items in the non-base table in turn and compare them with the current data items in the advancing base table to determine whether the matching conditions are met (such as equal field values ​​or other conditions). If the match is successful, record the matching result and proceed to the next step.

[0145] Read the values ​​of the associated fields of the advancement benchmark table one by one. For example, in a user transaction flow table, the associated field is the user ID. The system reads each user ID in turn, such as the current extracted value is 123456. Each time a data item is read, it is used as the input value for the current match.

[0146] For the currently extracted data item, for example, the user ID is 123456, the system uses the histogram information of the non-base table to perform range calculation. Assuming that the histogram of the non-base table represents the distribution range and density value of the user ID in the basic account information, the system will find the bucket range where the current data item 123456 is located. For example, the bucket range may be [123450,123460], and the matching range is defined as the data items contained in this segment.

[0147] Within the matching range, the system reads data items in the non-reference table one by one, such as 123452, 123455, and 123456. They are compared one by one with data item 123456 of the current advancement reference table. If the matching condition is met (such as the field values ​​are completely equal), the matching item is recorded as the matching result.

[0148] Example description: In financial business, it is often necessary to merge the user transaction flow table and the user basic information table for query to achieve user behavior analysis.

[0149] The promotion benchmark table is the transaction flow table, the non-benchmark table is the basic information table, and the associated field is the user ID.

[0150] The system reads the user ID in the transaction flow table one by one, for example, the current value is 123456.

[0151] The basic information table histogram is used to calculate that the matching range of user ID 123456 is [123450, 123460]. The system reads user data items in the basic information table one by one within this range, such as 123452, 123455, and 123456.

[0152] These data items are compared with the current user ID 123456 and a match is determined to be 123456.

[0153] Finally, the matching results are recorded and used to generate query results.

[0154] By traversing the data items in the benchmark table one by one and combining the histogram distribution information of the non-benchmark table to determine the matching range, full table scans in the non-benchmark table are avoided. The precise definition of the matching range reduces the reading and comparison operations of invalid data, greatly improves the efficiency of merge join queries, and reduces the consumption of system resources.

[0155] S60, if the current matching operation fails, generating a new matching range according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and performing the matching operation within the new matching range;

[0156] In this embodiment, for the data item of the associated field in the current advancing reference table, it is checked whether a data item that meets the condition can be found in the matching range of the non-reference table. If no matching item is found, it is determined that the current matching operation fails.

[0157] In the advancement base table, locate the next item of the current associated field data item to ensure that the matching process can be advanced sequentially without interruption. For example, if the current data item is 123456, extract the next item 123457.

[0158] Using the distribution information (bucket range, bucket width, bucket number, density value) of the non-reference table histogram, the new matching range is calculated with the next data item of the reference table as a reference. The new matching range is determined by locating the bucket position of the next data item in the histogram.

[0159] For the new matching range, read the data of the associated fields in the non-reference table one by one, and perform matching operations with the next data item in the reference table. Determine whether the matching condition is met, record the matching result or continue to the next step.

[0160] When the current data item (such as 123456) of the advancing reference table cannot find a corresponding item in the matching range of the non-reference table, the matching failure status is recorded. For example, if there is no data in the non-reference table that matches 123456 in the matching range [123450,123460], the failure is marked.

[0161] Sequentially read the associated fields of the advancement reference table to obtain the next item of the current data item. Assuming that the current data item is 123456, read the next item 123457.

[0162] Using the histogram information of the non-reference table, the new matching range is determined by locating the bucket to which 123457 belongs. For example, if 123457 in the histogram belongs to the bucket range [123455,123465], the new matching range is defined as the data items within this range.

[0163] Read the data of the associated fields in the non-reference table item by item in the new matching range, and compare it with the next data item of the advancing reference table (such as 123457). If a data item that meets the conditions is found, the matching result is recorded; otherwise, repeat the above steps.

[0164] Example description: In the financial data processing scenario, such as the combined query of the user transaction flow table (promotion benchmark table) and the user account basic information table (non-benchmark table):

[0165] The current matching operation uses user ID 123456 in the transaction flow table, and no matching item is found in the matching range [123450,123460] of the basic information table histogram.

[0166] After the match fails, extract the next user ID 123457 in the transaction flow table.

[0167] Based on the histogram of the basic information table, locate the bucket range to which 123457 belongs, and determine the new matching range as [123455,123465].

[0168] Read the basic information table data item by item in the new matching range and match it with 123457. If a matching item 123457 is found, record the matching result.

[0169] By dynamically adjusting the matching range when a match fails, and combining the distribution information of the next data item in the benchmark table and the non-benchmark table histogram to generate a new matching range, the cost of full table scan is effectively reduced. While ensuring the accuracy of query results, the flexibility and efficiency of matching operations are significantly improved, making the performance of merge join queries on large-scale data sets more optimized.

[0170] S70, when the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set.

[0171] In this embodiment, it is determined whether all data items of the associated fields in the reference table and the non-reference table have been processed. The data item traversal status of the two tables is checked, and whether the traversal is completed is recorded.

[0172] Determine whether the termination conditions of the matching operation have been met, including but not limited to the following scenarios:

[0173] The query execution time has reached the preset maximum timeout period.

[0174] The number of matching result records has reached the specified limit.

[0175] Other user-defined termination rules are triggered.

[0176] When any of the above conditions are met, the matching operation of the associated field data items in the reference table and the non-reference table is stopped immediately to prevent unnecessary resource consumption. The matching termination status is recorded for subsequent processing.

[0177] Extract all the records marked as successfully matched from the matching operation records. According to the requirements of the merge connection query task, the field data in the advancement base table and the non-base table are spliced ​​into a complete result set that meets the query requirements.

[0178] In the specific implementation process, you can traverse the associated field data items in the reference table and the non-reference table to determine whether the last record of the table has been reached. For example:

[0179] The last item in the advancement reference table is 456789, and the last item in the non-reference table is 789123. If the current traversal data item is the last one, the traversal is marked complete.

[0180] You can also check whether one of the following conditions is met:

[0181] The duration of the current query operation exceeds the preset time (such as 30 seconds); the number of successfully matched records reaches the query task limit (such as 500); user-set rules, such as termination after a specific data range or field value matches.

[0182] If the traversal is completed or the matching termination condition is met, the matching logic is immediately interrupted and reading or processing new data items stops.

[0183] Extract all records marked as "matched successfully". For example, the advancing base table fields are [ID, Name], the non-base table fields are [ID, Account], and the merged result is [ID, Name, Account]. Sort the concatenated records (such as ascending timestamp) and format them into the specified output format (such as CSV or JSON).

[0184] Example description: In the financial data analysis scenario, a merge query is performed on the large transaction record table (promotion benchmark table) and the customer account table (non-benchmark table):

[0185] If all data items in the large transaction record table (such as ID: 1001-1050) have been traversed, or the matching results have reached the task limit of 1000, the matching operation will be stopped. The successfully matched records are [transaction ID, transaction amount, customer ID, account balance], which are merged to form a complete query result set.

[0186] The result set is sorted by transaction amount and output in JSON format for subsequent report generation or decision support system use.

[0187] By stopping the matching operation when the data item traversal is completed or the matching termination condition is met, meaningless resource consumption is avoided and query efficiency is significantly improved. At the same time, the merging logic of successfully matched records ensures the accuracy and completeness of the query results and can meet the performance requirements of large-scale data queries.

[0188] The present invention relates to the fields of big data technology and financial technology, and discloses a query optimization method based on a histogram, which determines a database table and its associated fields, generates a histogram of the associated fields, compares the data distribution density of the associated fields, selects the database table with the sparsest data distribution density as a reference table for advancement, traverses the associated field data of the reference table item by item, and generates a matching range in combination with the distribution information of the non-reference table, performs a matching operation within the matching range, and generates a new matching range and performs a matching operation according to the next data item of the reference table if the match fails, and finally terminates the operation and generates a query result set when the data item traversal is completed or the matching termination condition is satisfied. The present invention accurately describes the data distribution characteristics of the associated fields through a histogram, realizes dynamic optimization of the matching range, effectively avoids full table scanning and unnecessary matching operations, significantly improves the execution efficiency of connection queries, and reduces resource occupancy.

[0189] In one embodiment, the above S10 includes:

[0190] S101, parsing the names of database tables involved in the connection operation from the merge connection query task, the database tables including a first database table and a second database table;

[0191] S102, retrieving metadata information of the first database table and the second database table, and extracting specific names of associated fields and data types of associated fields in the first database table and the second database table respectively;

[0192] S103, verifying whether the data types of the associated fields in the first database table and the associated fields in the second database table are compatible, and terminating the connection operation if they are not compatible;

[0193] S104, checking whether an index structure has been established for the associated fields in the first database table and the associated fields in the second database table; if an index structure has not been established, creating an index structure for the associated fields in the first database table and the second database table respectively.

[0194] In this embodiment, the names of two database tables that need to participate in the merge join operation are parsed from the query statement or configuration of the merge join query task. These tables are the operation objects of subsequent query optimization and are defined as the first database table and the second database table respectively.

[0195] Extract the FROM clause in the query task to determine the table names involved in the join operation. Use regular expressions or database parsing tools to parse the table names, for example, SELECT*FROM Table1 JOIN Table2 to parse Table1 and Table2. Verify the uniqueness of the parsed table names to ensure that they are valid and unique.

[0196] According to the parsed database table name, obtain the metadata information of the table (such as field name, field type, field constraint, etc.). Metadata information is the descriptive data of the table in the database, and the name and data type of the associated field used for connection are extracted. Query the table structure information through the metadata interface of the database management system (such as INFORMATION_SCHEMA or DESCRIBE command). Extract the field name and type used for the connection operation from the table, for example, ON Table1.id=Table2.foreign_id determines that id and foreign_id are associated fields. Check whether the associated field exists, and throw an exception if it does not exist.

[0197] Ensure that the data types of the associated fields in the first database table and the second database table are consistent or compatible. For example, INT is not compatible with VARCHAR, and INT is compatible with BIGINT. If the data types of the associated fields are incompatible, terminate the connection operation to avoid runtime errors or performance issues. Check whether the data types of the associated fields are of the same type family (such as numeric type, character type). If there is a data type mismatch, you can prompt or terminate the operation as needed. Example: Table1.id (INT) and Table2.foreign_id (VARCHAR), after detection, terminate the connection and prompt an error.

[0198] Indexes are important structures for optimizing query performance. Check whether the associated fields have been indexed. If not, create indexes for the associated fields in the first and second database tables respectively. Use the SHOW INDEX command of the database system or query metadata to check whether the fields have been indexed. If not, create a single-column index to optimize subsequent join query operations, such as CREATE INDEX idx_field ON Table1(field). Create indexes for the associated fields of each table to ensure the parity of the query performance of the two-table join.

[0199] This embodiment parses the database table name in the connection query task, extracts and verifies the metadata information of the table, ensures the data type compatibility of the connection field, and automatically creates an index structure for the associated fields that are not indexed. This achieves the pre-optimization of the merged connection query and significantly improves the accuracy and execution efficiency of the connection query task. Especially in the big data processing scenario, it effectively reduces unnecessary query operations and runtime errors.

[0200] In one embodiment, the above S20 includes:

[0201] S201, retrieving the associated field data in the first database table and the second database table, and respectively calculating the minimum value and the maximum value of each associated field;

[0202] S202, determining a bucket range of each associated field based on the minimum value and the maximum value of each associated field;

[0203] S203, generating the number of buckets and the width of buckets for each associated field based on a preset bucketing rule and the bucketing range of each associated field;

[0204] S204, dividing the data of the associated fields in the first database table and the second database table into corresponding buckets according to the bucket range, bucket number and bucket width of each associated field, and counting the number of data items in each bucket;

[0205] S205, constructing a first associated field histogram of the associated fields in the first database table and a second associated field histogram of the associated fields in the second database table according to the bucket range, the number of buckets, the bucket width and the number of data items in each bucket of each associated field.

[0206] In this embodiment, by scanning the associated fields in the first database table and the second database table, all data values ​​in the fields are obtained, and the minimum and maximum values ​​of each field are calculated. These values ​​will provide a range basis for subsequent bucketing operations. Use SQL queries to scan field data, such as SELECT MIN(field), MAX(field) FROM Table1. Ensure that the data types in the fields are compatible to avoid calculation errors caused by data anomalies. Return the minimum and maximum values ​​of each field, and cache the results for use in subsequent steps.

[0207] The bucket range is the interval division of the field data distribution, and its upper and lower bounds are determined by the minimum and maximum values. The purpose of the division is to facilitate the construction of the histogram. The bucket range is set to [min_value, max_value]. For example, if the field data is between 10 and 100, the bucket range is [10, 100]. If there are extreme values ​​in the field data, you can set reasonable clipping rules to adjust the bucket range.

[0208] The bucketing rule determines the number and width of buckets, such as fixed-width bucketing or fixed-number bucketing. The number of buckets determines the granularity of data division, and the bucket width is the size of the interval covered by each bucket. The number of buckets is calculated based on the bucket range and width, and the formula is bucket_count = (max_value-min_value) / bucket_width. Divide the data into equal numbers of buckets by sorting segments. Example: If the bucketing rule is a fixed width of 10, the bucket range is [10,100], and the number of buckets is (100-10) / 10 = 9.

[0209] Data partitioning is to map all data items of the associated field to the corresponding buckets and count the number of items in each bucket. These statistical results will be used to build the histogram. Traverse the field data and use the bucket range and width to determine the bucket where the data is located. The formula is bucket_index = (value-min_value) / bucket_width. Create a counter array, one counter for each bucket, and record the number of data items in the bucket. Example: If the data point is 15, the bucket range is [10,100], and the width is 10, it falls in the bucket [10,20), and the corresponding counter is increased by 1.

[0210] A histogram is a statistical data chart with buckets as the unit. Each bucket corresponds to a bar, indicating the data distribution density. The data structure of the histogram is constructed using the bucket range, width, number, and number of data items. Each bucket records its range and data volume. The histogram results are stored in JSON format or other formats available to visualization tools for subsequent query optimization. Example: The histogram data is [{bucket_range:[10,20),count:5},{bucket_range:[20,30),count:7}].

[0211] This embodiment can clearly describe the distribution characteristics of field data by generating histograms for the associated fields in the first database table and the second database table. The information such as the bucket range and the number of data items in the histogram is used to optimize subsequent matching operations, effectively improving the efficiency of merge join queries. Especially when the amount of data is large and unevenly distributed, unnecessary calculations and resource consumption can be significantly reduced, improving query performance.

[0212] In one embodiment, the above S30 includes:

[0213] S301, extracting a bucket range, a bucket width, a bucket number, and a number of data items in each bucket from the first associated field histogram and the second associated field histogram respectively;

[0214] S302, determining the density value of each bucket of the first associated field histogram and the second associated field histogram respectively based on the number of data items in each bucket and the bucket width;

[0215] S303: Determine the average density, maximum density, minimum density, and distribution deviation of the first associated field histogram and the second associated field histogram respectively based on the density value of each bucket;

[0216] S304: Compare the average density, maximum density, minimum density, and distribution deviation of the first associated field histogram and the second associated field histogram to generate a data distribution density comparison result of the associated fields in the first database table and the second database table.

[0217] In this embodiment, the histogram records the data distribution information of the associated fields, including the range of bucket division, the width of each bucket, the total number of buckets, and the number of data items in each bucket. Extracting this information is the basis for subsequent calculation of data distribution density. Read the storage structure of the histogram, such as JSON, database table, etc., and parse the field information therein. Ensure that the extracted bucket range, width, number, and number of data items are complete and accurate. Example: Parse from the histogram that bucket_range = [10, 20), bucket_width = 10, bucket_count = 50, item_count_per_bucket = [5, 7, 10...].

[0218] Bucket density reflects the concentration of data in each bucket. The calculation formula is density = number of data items / bucket width. Traverse each bucket and take out the number of data items and bucket width. Use the formula density = item_count / bucket_width to calculate the density value and store it in the corresponding data structure. Example: If item_count = 50 and bucket_width = 10 for a bucket, the density value is 5.

[0219] The overall data distribution characteristics of the statistical histogram include average density, maximum density, minimum density, and distribution deviation (such as standard deviation) used to describe the degree of distribution dispersion.

[0220] Average density: Calculates the arithmetic mean of all bucket density values.

[0221] Maximum density: Get the maximum value among the bucket density values.

[0222] Minimum density: Get the minimum value among the bucket density values.

[0223] Distribution Deviation: Calculates the standard deviation or coefficient of variation of all density values.

[0224] Example: If the density value of a histogram is [5,10,15], the average density is 10, the maximum density is 15, and the minimum density is 5.

[0225] Perform a comprehensive comparison of the data distribution characteristics of the associated fields of the two tables to generate specific comparison results to provide a basis for subsequent optimization. For example, if the density deviation of a table is small, it means that the data distribution is relatively uniform. Calculate the difference or ratio of the average density, maximum density, minimum density and distribution deviation of the two tables. Generate a comparison report based on the results to record the differences in data distribution between the two tables. Example: If the average density of Table A is 10 and the average density of Table B is 5, it can be recorded that Table A has a higher density and may be the focus of optimization.

[0226] You can set weights for the above features and decide which indicators to prioritize based on specific needs. For example, in a scenario where sparsity is the main concern, prioritize average density; in a scenario where uniformity is a high requirement, prioritize distribution deviation.

[0227] For example, if the average density difference is greater than a certain threshold, the table with lower density is selected as the reference table for advancement. If the average density is close, the table with smaller deviation is selected in combination with the distribution deviation judgment.

[0228] The weighted average method is used to comprehensively evaluate the indicators. The comprehensive scores of the two tables are compared. The one with the lower score is given priority as the benchmark table for advancement, and the other table is the non-benchmark table.

[0229] This embodiment can quantify the data distribution characteristics of the associated fields of two tables by calculating and comparing the distribution density based on the associated field histogram. Using statistical indicators such as average density, maximum density, minimum density, and distribution deviation, the sparsity and concentration trend of data distribution can be effectively identified, providing a reliable basis for optimizing the selection of the reference table, thereby reducing unnecessary traversal and matching operations and significantly improving the execution efficiency of the merge join query.

[0230] In one embodiment, the above S50 includes:

[0231] S501, extracting the current data items of the associated fields one by one from the advancement reference table;

[0232] S502, determining a matching range corresponding to the current data item based on a bucket range, a bucket width, a bucket number, and a density value of each bucket recorded in a histogram corresponding to the non-reference table;

[0233] S503, reading all data items of the associated fields in the non-reference table within the matching range, and matching them with the current data items in the advancement reference table to determine matching data items that meet the preset matching conditions;

[0234] S504: Generate a matching result based on the current data item of the advancement reference table and the matching data item of the non-reference table.

[0235] In this embodiment, when traversing the advancement benchmark table item by item, a specific data value is extracted from its associated field each time as the current data item. The data items in the advancement benchmark table are arranged in order and processed step by step to ensure the integrity of the query process. Use sequential traversal or iterators to read the values ​​of the associated fields from the advancement benchmark table one by one. After each reading, the current value is cached for subsequent matching operations. Example: If the associated field value of the advancement benchmark table is {1,3,5,7}, the data item currently extracted is 1, and the next extraction is 3.

[0236] The possible matching range of the data item in the non-reference table is estimated by using the histogram information of the non-reference table and combining it with the current data item value.

[0237] Bucket range: used to limit the upper and lower bounds that the current data item may match.

[0238] Bucket width: used to calculate the span of each bucket and refine the range estimate.

[0239] Number of buckets: used to determine the overall distribution characteristics of the histogram.

[0240] Density value: indicates the sparsity of data in each bucket and is used to optimize the selection of matching range.

[0241] Determine the corresponding bucket based on the current data item value of the advancement benchmark table. Combine the upper and lower bounds and density value of the bucket in the histogram to calculate a reasonable matching range. Example: If the current data item value is 3, the histogram information of the non-benchmark table shows that the bucket range is [2,4], the density value is 5, and the matching range is set to [2,4].

[0242] For data items in the non-reference table's associated fields that fall within the matching range, compare them one by one with the current data items in the advancing reference table to determine whether they meet the matching conditions. Traverse all data items in the non-reference table's associated fields that fall within the matching range. Apply the preset matching conditions (such as equal value matching, range matching, etc.) to each data item. Determine the data items that have successfully matched and record them. Example: If the current data item in the advancing reference table is 3, the matching range is [2,4], and the data items in the non-reference table's associated fields are {2,3,4,5}, the data item that meets the matching conditions is {3}.

[0243] Combine the current data item of the advancing reference table with the successfully matched data item of the non-reference table to generate a matching result for subsequent merge operations. Combine the current data item with the successfully matched data item into one record.

[0244] Save the matching results to the intermediate result set. Example: If the current advancing reference table data item is 3, and the non-reference table successfully matched data item is {3}, the generated matching result is {3,3}.

[0245] This embodiment extracts data items of associated fields in the advancement benchmark table one by one, dynamically generates matching ranges and performs matching operations in combination with the distribution information of the non-benchmark table histogram records, thereby greatly reducing unnecessary data traversal. In particular, when processing large-scale data sets, it effectively avoids the inefficient query method of scanning the entire table row by row, and achieves efficient and accurate merge join query optimization.

[0246] In one embodiment, the above S60 includes:

[0247] S601, detecting whether the matching operation of the current data item of the associated field in the advancement reference table is successful, and if the matching fails, extracting the next data item of the associated field in the advancement reference table;

[0248] S602, determining a new matching range based on the bucket range, bucket width, bucket number, and density value of each bucket recorded in the histogram corresponding to the non-reference table and in combination with the next data item of the advancement reference table;

[0249] S603: Read the data item of the associated field in the non-reference table within the new matching range, and match it with the next data item in the advancing reference table.

[0250] In this embodiment, after each matching operation is completed, it is checked whether the current data item successfully matches the data item in the non-reference table. If the matching operation fails (i.e., no non-reference table data item that meets the conditions is found), it is necessary to extract the data item of the next associated field from the advancing reference table and continue the subsequent matching operation. The matching result of the current advancing reference table data item and the non-reference table data item is compared. If there is no matching result, the pointer of the advancing reference table is moved to the next data item. The next data item is extracted as the current data item, and a new matching operation is prepared.

[0251] Use the histogram information of the non-reference table (including bucket range, bucket width, number of buckets, and density value of each bucket) to dynamically generate a new matching range. Combined with the value of the next data item of the advancing reference table, calculate the possible matching non-reference table data range. According to the value of the next data item of the advancing reference table, locate its corresponding bucket in the histogram of the non-reference table. Use the range information of the bucket (such as upper and lower bounds) and the bucket density value to determine a reasonable new matching range. If the next data item falls between multiple buckets, merge the bucket ranges.

[0252] Within the determined new matching range, read the associated field values ​​of the non-reference table one by one. Each data item is matched with the next data item of the advancing reference table to determine whether it meets the preset matching conditions (such as equal value matching or range matching). Traverse all data items in the non-reference table that fall within the new matching range. Compare each data item and record the data items that meet the matching conditions. If a match is found, save the matching result; if no match is found, regenerate the next matching range.

[0253] This embodiment can flexibly adapt to the uneven distribution of data by dynamically generating a new matching range when a match fails, avoiding blind scanning of all data items in non-reference tables, thereby reducing query time and improving the performance of merge join queries on large-scale data sets.

[0254] In one embodiment, the above S70 includes:

[0255] S701, detecting the traversal status of the associated field data items in the advancing reference table and the associated field data items in the non-reference table, and if the traversal of the associated field data items in the advancing reference table or the associated field data items in the non-reference table is all completed, marking the traversal as finished;

[0256] S702, or when the matching termination condition is met, stop advancing the data item matching operation of the associated fields in the reference table and the non-reference table, and record the matching status, wherein the matching termination condition includes that the query time reaches a preset maximum timeout time or the number of matching results reaches a specified number;

[0257] S703, extracting all records marked as successfully matched from the matching operation records, and merging the records to generate a query result set that meets the requirements of the merge connection query task.

[0258] In this embodiment, by monitoring the traversal status, it is determined whether all data items in the advancing reference table or the non-reference table have been accessed. The traversal end condition is that all data items in any of the two tables have been scanned and marked as the end state. Pointers are set for the advancing reference table and the non-reference table respectively to record the current progress of the traversal. After each matching operation, it is checked whether the pointer points to the end data item of the table. If the data items in any table have been completely scanned, the update mark is "traversal completed".

[0259] Set termination conditions to prevent long or invalid matching operations. Termination conditions include: the query time reaches the preset maximum timeout; the number of matching result records reaches the specified number (to meet business needs).

[0260] The query operation execution time can be monitored by a timer. If it exceeds the preset time, the matching is stopped. The number of matching results is counted each time, and the operation is terminated if the preset number is reached. After termination, the status of the matching operation is recorded for subsequent query result generation.

[0261] Extract matching items that meet the conditions from the matching results of the records. Merge the data items of the successfully matched advancement benchmark table and non-benchmark table to generate a result set that meets the query requirements. Traverse the matching records and filter all items marked as "matched successfully". Extract the advancement benchmark table fields and non-benchmark table fields of the successfully matched items. Merge the matching fields according to the requirements of the query task, including field splicing, filtering, and sorting. Format the results to the standard output format.

[0262] This embodiment can effectively avoid invalid matching operations and resource waste by setting matching termination conditions and monitoring traversal status, ensuring that efficient and complete query results are generated within the specified time. At the same time, the extraction of successfully matched records and the result merging process improve the accuracy and performance of data integration.

[0263] In one embodiment, a histogram-based query optimization device is provided, and the histogram-based query optimization device corresponds one-to-one to the histogram-based query optimization method in the above embodiment. Figure 3 , Figure 3 This is a functional module diagram of a preferred embodiment of the query optimization device based on histogram of the present invention. Task parsing module 10, histogram generation module 20, distribution density comparison module 30, advancement benchmark selection module 40, matching range generation and matching module 50, matching range adjustment module 60 and result generation module 70. The functional modules are described in detail as follows:

[0264] The task parsing module 10 is used to determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection based on the merge connection query task;

[0265] A histogram generating module 20, configured to generate a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table, respectively;

[0266] A distribution density comparison module 30, configured to compare data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0267] A promotion benchmark selection module 40, configured to select a database table with the sparsest data distribution density from the first database table and the second database table as a promotion benchmark table, and to use database tables other than the promotion benchmark table as non-benchmark tables;

[0268] A matching range generation and matching module 50 is used to traverse and extract data items of associated fields in the advancement reference table one by one, generate a matching range in combination with distribution information of the histogram records corresponding to the non-reference table, and perform matching operations in sequence within the matching range;

[0269] A matching range adjustment module 60, for generating a new matching range according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table if the current matching operation fails, and performing the matching operation within the new matching range;

[0270] The result generation module 70 is used to terminate the matching operation when the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, and merge the successfully matched records to generate a query result set.

[0271] In one embodiment, the task parsing module 10 is specifically used to:

[0272] Parsing the names of the database tables involved in the join operation from the merge join query task, the database tables including a first database table and a second database table;

[0273] Retrieving metadata information of the first database table and the second database table, and extracting specific names of associated fields and data types of associated fields in the first database table and the second database table respectively;

[0274] Verify whether the data types of the associated fields in the first database table and the associated fields in the second database table are compatible, and terminate the connection operation if they are not compatible;

[0275] Check whether the associated fields in the first database table and the associated fields in the second database table have established index structures. If no index structures have been established, create index structures for the associated fields in the first database table and the second database table respectively.

[0276] In one embodiment, the histogram generating module 20 is specifically used for:

[0277] Retrieving the associated field data in the first database table and the second database table, and calculating the minimum value and the maximum value of each associated field respectively;

[0278] Determine the bucket range of each associated field based on the minimum value and the maximum value of each associated field;

[0279] Based on the preset bucketing rules and the bucketing range of each associated field, generate the number of buckets and the bucket width of each associated field;

[0280] According to the bucket range, bucket quantity and bucket width of each associated field, divide the data of the associated fields in the first database table and the second database table into corresponding buckets, and count the number of data items in each bucket;

[0281] According to the bucket range, bucket number, bucket width and the number of data items in each bucket of each associated field, a first associated field histogram of the associated field in the first database table and a second associated field histogram of the associated field in the second database table are constructed respectively.

[0282] In one embodiment, the distribution density comparison module 30 is specifically used to:

[0283] Extracting a bucket range, a bucket width, a bucket number, and a number of data items in each bucket from the first associated field histogram and the second associated field histogram respectively;

[0284] Determine, based on the number of data items in each bucket and the bucket width, a density value of each bucket of the first associated field histogram and the second associated field histogram;

[0285] Based on the density value of each bucket, respectively determine the average density, maximum density, minimum density and distribution deviation of the first associated field histogram and the second associated field histogram;

[0286] The average density, maximum density, minimum density and distribution deviation of the first associated field histogram and the second associated field histogram are compared to generate a data distribution density comparison result of the associated fields in the first database table and the second database table.

[0287] In one embodiment, the matching range generation and matching module 50 is specifically used to:

[0288] Extracting the current data items of the associated fields one by one from the advancement reference table;

[0289] Determine a matching range corresponding to the current data item based on the bucket range, bucket width, number of buckets, and density value of each bucket of the histogram record corresponding to the non-reference table;

[0290] Within the matching range, all data items of the associated fields in the non-reference table are read and matched with the current data items in the advancement reference table to determine matching data items that meet the preset matching conditions;

[0291] A matching result is generated based on the current data item of the advancing reference table and the matching data item of the non-reference table.

[0292] In one embodiment, the matching range adjustment module 60 is specifically configured to:

[0293] Detecting whether a matching operation of a current data item of an associated field in the advancement reference table is successful, and if the matching fails, extracting a next data item of the associated field in the advancement reference table;

[0294] Determine a new matching range based on the bucket range, bucket width, bucket number, and density value of each bucket recorded in the histogram corresponding to the non-reference table and in combination with the next data item of the advancement reference table;

[0295] The data item of the associated field in the non-reference table is read within the new matching range and matched with the next data item of the advancing reference table.

[0296] In one embodiment, the result generation module 70 is specifically configured to:

[0297] Detect the traversal status of the associated field data items in the advancing reference table and the associated field data items in the non-reference table. If the traversal of the associated field data items in the advancing reference table or the associated field data items in the non-reference table is completed, mark the traversal as finished.

[0298] Or when the matching termination condition is met, the data item matching operation of the associated fields in the reference table and the non-reference table is stopped, and the matching status is recorded. The matching termination condition includes that the query time reaches a preset maximum timeout time or the number of matching results reaches a specified number;

[0299] All records marked as successfully matched are extracted from the matching operation records, and a query result set meeting the requirements of the merge connection query task is generated based on the records.

[0300] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 4 As shown. The computer device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile and / or volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external user terminal through a network connection. When the computer program is executed by the processor, it realizes the functions or steps of a query optimization method based on a histogram on the service side.

[0301] In one embodiment, a computer device is provided. The computer device may be a user terminal, and its internal structure diagram may be as follows: Figure 5 As shown. The computer device includes a processor, a memory, a network interface, a display screen and an input device connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external server through a network connection. When the computer program is executed by the processor, the functions or steps on the user side of a histogram-based query optimization method are implemented.

[0302] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the following steps are implemented:

[0303] Based on the merge connection query task, determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection;

[0304] Generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively;

[0305] Comparing data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0306] Selecting a database table with the sparsest data distribution density from among the first database table and the second database table as a promotion reference table, and using database tables other than the promotion reference table as non-reference tables;

[0307] Traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram record corresponding to the non-reference table, and performing matching operations in sequence within the matching range;

[0308] If the current matching operation fails, a new matching range is generated according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and a matching operation is performed within the new matching range;

[0309] When the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set.

[0310] In one embodiment, a computer readable storage medium is provided, on which a computer program is stored, and when the computer program is executed by a processor, the following steps are implemented:

[0311] Based on the merge connection query task, determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection;

[0312] Generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively;

[0313] Comparing data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram;

[0314] Selecting a database table with the sparsest data distribution density from among the first database table and the second database table as a promotion reference table, and using database tables other than the promotion reference table as non-reference tables;

[0315] Traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram record corresponding to the non-reference table, and performing matching operations in sequence within the matching range;

[0316] If the current matching operation fails, a new matching range is generated according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and a matching operation is performed within the new matching range;

[0317] When the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set.

[0318] It should be noted that the above functions or steps that can be implemented by the computer-readable storage medium or computer device can refer to the relevant descriptions on the server side and the user side in the aforementioned method embodiment. To avoid repetition, they will not be described one by one here.

[0319] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, 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 embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory can include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0320] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0321] It should be noted that if software tools or components other than those of the Company appear in the embodiments of the present application, they are only used for illustration and do not represent actual use. The above-described embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the above-mentioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the above-mentioned embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents; and these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A histogram-based query optimization method, characterized in that: The following steps are involved: Based on the merge connection query task, determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection; Generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively; Comparing data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram; Selecting a database table with the sparsest data distribution density from among the first database table and the second database table as a promotion reference table, and using database tables other than the promotion reference table as non-reference tables; Traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram record corresponding to the non-reference table, and performing matching operations in sequence within the matching range; If the current matching operation fails, a new matching range is generated according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and a matching operation is performed within the new matching range; When the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set.

2. The query optimization method based on histogram according to claim 1, characterized in that: Determining the first database table, the second database table, and associated fields for connection between the first database table and the second database table based on the merge connection query task includes: Parsing the names of the database tables involved in the join operation from the merge join query task, the database tables including a first database table and a second database table; Retrieving metadata information of the first database table and the second database table, and extracting specific names of associated fields and data types of associated fields in the first database table and the second database table respectively; Verify whether the data types of the associated fields in the first database table and the associated fields in the second database table are compatible, and terminate the connection operation if they are not compatible; Check whether the associated fields in the first database table and the associated fields in the second database table have established index structures. If no index structures have been established, create index structures for the associated fields in the first database table and the second database table respectively.

3. The query optimization method based on histogram according to claim 1, characterized in that: Generating a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively, including: Retrieving the associated field data in the first database table and the second database table, and calculating the minimum value and the maximum value of each associated field respectively; Determine the bucket range of each associated field based on the minimum value and the maximum value of each associated field; Based on the preset bucketing rules and the bucketing range of each associated field, generate the number of buckets and the bucket width of each associated field; According to the bucket range, bucket quantity and bucket width of each associated field, divide the data of the associated fields in the first database table and the second database table into corresponding buckets, and count the number of data items in each bucket; According to the bucket range, bucket number, bucket width and the number of data items in each bucket of each associated field, a first associated field histogram of the associated fields in the first database table and a second associated field histogram of the associated fields in the second database table are constructed respectively.

4. The query optimization method based on histogram as claimed in claim 1, characterized in that: Comparing the data distribution density of the associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram includes: Extracting a bucket range, a bucket width, a bucket number, and a number of data items in each bucket from the first associated field histogram and the second associated field histogram respectively; Determine, based on the number of data items in each bucket and the bucket width, a density value of each bucket of the first associated field histogram and the second associated field histogram; Based on the density value of each bucket, respectively determine the average density, maximum density, minimum density and distribution deviation of the first associated field histogram and the second associated field histogram; The average density, maximum density, minimum density and distribution deviation of the first associated field histogram and the second associated field histogram are compared to generate a data distribution density comparison result of the associated fields in the first database table and the second database table.

5. The query optimization method based on histogram according to claim 1, characterized in that: Traversing and extracting data items of associated fields in the advancement reference table one by one, generating a matching range in combination with distribution information of the histogram record corresponding to the non-reference table, and performing matching operations in sequence within the matching range, including: Extracting the current data items of the associated fields one by one from the advancement reference table; Determine a matching range corresponding to the current data item based on the bucket range, bucket width, number of buckets, and density value of each bucket of the histogram record corresponding to the non-reference table; Within the matching range, all data items of the associated fields in the non-reference table are read and matched with the current data items in the advancement reference table to determine matching data items that meet the preset matching conditions; A matching result is generated based on the current data item of the advancing reference table and the matching data item of the non-reference table.

6. The query optimization method based on histogram according to claim 1, characterized in that: If the current matching operation fails, a new matching range is generated according to the next data item of the associated field in the advancing reference table in combination with the histogram corresponding to the non-reference table, and a matching operation is performed within the new matching range, including: Detecting whether a matching operation of a current data item of an associated field in the advancement reference table is successful, and if the matching fails, extracting a next data item of the associated field in the advancement reference table; Determine a new matching range based on the bucket range, bucket width, bucket number, and density value of each bucket recorded in the histogram corresponding to the non-reference table and in combination with the next data item of the advancement reference table; The data item of the associated field in the non-reference table is read within the new matching range and matched with the next data item of the advancing reference table.

7. The query optimization method based on histogram according to claim 1, characterized in that: When the data item traversal in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, the matching operation is terminated, and the successfully matched records are merged to generate a query result set, including: Detect the traversal status of the associated field data items in the advancing reference table and the associated field data items in the non-reference table. If the traversal of the associated field data items in the advancing reference table or the associated field data items in the non-reference table is completed, mark the traversal as finished. Or when the matching termination condition is met, the data item matching operation of the associated fields in the reference table and the non-reference table is stopped, and the matching status is recorded. The matching termination condition includes that the query time reaches a preset maximum timeout time or the number of matching results reaches a specified number; All records marked as successfully matched are extracted from the matching operation records, and a query result set meeting the requirements of the merge connection query task is generated based on the records.

8. A query optimization device based on histogram, characterized in that: The query optimization device based on histogram comprises: A task parsing module, used to determine the first database table, the second database table, and the associated fields in the first database table and the second database table for connection based on the merge connection query task; A histogram generating module, used to generate a first associated field histogram and a second associated field histogram for the associated fields in the first database table and the associated fields in the second database table respectively; A distribution density comparison module, configured to compare data distribution densities of associated fields in the first database table and the second database table based on the first associated field histogram and the second associated field histogram; A promotion benchmark selection module, configured to select a database table with the sparsest data distribution density from the first database table and the second database table as a promotion benchmark table, and to use database tables other than the promotion benchmark table as non-benchmark tables; A matching range generation and matching module, used to traverse and extract data items of associated fields in the advancement benchmark table one by one, generate a matching range in combination with distribution information of the histogram records corresponding to the non-benchmark table, and perform matching operations in sequence within the matching range; A matching range adjustment module, for generating a new matching range according to the next data item of the associated field in the advancement reference table in combination with the histogram corresponding to the non-reference table if the current matching operation fails, and performing the matching operation within the new matching range; The result generation module is used to end the matching operation when the traversal of the data items in the advancement reference table or the non-reference table is completed, or when it is detected that the matching termination condition is met, and merge the successfully matched records to generate a query result set.

9. A computer device, characterized in that: The computer device includes a memory, a processor, and a histogram-based query optimization program stored in the memory and executable on the processor. When the histogram-based query optimization program is executed by the processor, the steps of the histogram-based query optimization method described in any one of claims 1 to 7 are implemented.

10. A computer-readable storage medium, characterized in that: The storage medium stores a histogram-based query optimization program, and when the histogram-based query optimization program is executed by a processor, the steps of the histogram-based query optimization method according to any one of claims 1 to 7 are implemented.

Images