A bitmap join query optimization method based on a relational database, an electronic device, a storage medium and a product

Abstract

This invention discloses a Bitmap Join query optimization method, electronic device, storage medium, and product based on a relational database. The method includes: a database optimizer analyzing a query statement, identifying join operations, generating a main execution plan containing Bitmap Join operators based on the identified join operations, and simultaneously generating at least one backup execution plan; scanning the inner table according to the main execution plan, obtaining a row, determining whether data exists in the current row, and if data exists, extracting the join key and determining whether the current statistical state of the bitmap meets the rollback condition; if the rollback condition is met, the execution plan rolls back, and the query continues using the backup execution plan, ending the optimization; if the rollback condition is not met, constructing / updating the bitmap based on the join key, maintaining the corresponding join key's count information in the bitmap, and repeating this step; if no data exists, completing the inner and outer join calculations according to the main execution plan and outputting the query results.

Description

Technical Field

[0001] This invention belongs to the field of relational database query optimization and indexing technology, specifically relating to a Bitmap Join query optimization method, electronic device, storage medium and product based on relational database. Background Technology

[0002] Bitmap Join is an efficient join algorithm based on a bitmap structure. The algorithm first constructs a bitmap filter using the join keys of the inner table. Then, while scanning the outer table, it quickly filters candidate tuples using the bitmap and outputs the matching results. When duplicate join keys exist in the inner table, the corresponding position in the bitmap needs to maintain a count to avoid missing results due to duplicate keys. Current bitmap filter implementations typically allocate a uint32 type count slot for each join key, and the entire implementation is organized as a uint32 array, forming a Bitmap table.

[0003] In a typical execution flow, Bitmap Join can build a bitmap structure based on the small table before scanning the large table, thereby identifying "rows that may participate in the join" in advance. This effectively reduces irrelevant tuples entering subsequent operators, reduces computation and memory access overhead, and is especially suitable for small table-driven large table join scenarios.

[0004] However, this implementation still has certain limitations.

[0005] First, using the uint32 type to store counting information carries the risk of count overflow. Common improvements include extending the counting type from uint32 to uint64, or introducing other data structures that can support a larger counting range, but these solutions typically increase memory usage significantly. For example, extending the element type from uint32 to uint64 directly doubles the memory usage of the bitmap, and with a fixed memory budget, the number of elements that can be accommodated decreases accordingly.

[0006] Secondly, in the current database execution model, even if the join column for accessing the Bitmap table has been obtained through an index, the execution engine still needs to perform a heap access to check visibility and obtain the complete tuple before it can query and filter the bitmap. This process introduces additional memory access and CPU overhead, weakening the performance advantage of Bitmap Join in the filtering stage. Currently, there is no mature and universally applicable solution to this problem in the industry. Summary of the Invention

[0007] Purpose of the invention: To address the limitations of existing Bitmap Join algorithms, this invention proposes a Bitmap Join query optimization method, electronic device, storage medium, and product based on relational databases. Based on a planned rollback mechanism and a delayed table access strategy, it reduces redundant memory consumption and invalid data access by dynamically adjusting the access path and postponing heap table access during the execution phase.

[0008] Technical solution: In the first aspect, this invention proposes a Bitmap Join query optimization method based on relational databases, including:

[0009] The database optimizer analyzes the query statement, identifies the join operations, and generates a main execution plan containing the Bitmap Join operator based on the identified join operations, while also generating at least one alternative execution plan.

[0010] The internal table is scanned according to the main execution plan. A row is retrieved, and it is determined whether data exists in the current row. If data exists, the join key is extracted, and it is determined whether the current statistical status of the bitmap meets the rollback condition. If the rollback condition is met, the execution plan rolls back, and the query continues to be executed using the backup execution plan, and the optimization ends. If the rollback condition is not met, the bitmap is built / updated based on the join key, and the count information of the corresponding join key is maintained in the bitmap. The next row is retrieved and this step is executed repeatedly. If no data exists, the internal and external join calculations are completed according to the main execution plan, and the query results are output.

[0011] Furthermore, the statistical status includes: count value distribution, bitmap density, and memory usage.

[0012] Furthermore, the fallback conditions include: reaching a count overflow risk threshold or reaching a memory usage threshold.

[0013] Secondly, this invention proposes a Bitmap Join query optimization method based on relational databases, including:

[0014] Perform an external scan to determine if there are any candidate rows that meet the external conditions;

[0015] If it exists, the bitmap is queried based on the candidate row identifier information to determine whether the current candidate row identifier information matches the filtering condition. If it matches, the visibility check is performed on the data row in the corresponding heap table. If it is not visible, the outer surface scan is performed again. If it is visible, the complete row data is obtained, and the obtained complete row data is joined with the inner table. The join result is output, and the outer surface scan is performed again to obtain the next row.

[0016] If it does not exist, the query ends.

[0017] Thirdly, this invention proposes a Bitmap Join query optimization method based on relational databases, applied to database execution engines, including:

[0018] S301: Analyze the query statement, identify the join operations, generate a main execution plan containing the Bitmap Join operator based on the identified join operations, and generate at least one alternative execution plan.

[0019] S302: The database execution engine scans the internal table according to the main execution plan, obtains a row, and determines whether the internal table contains data. If it does, proceed to S303; if it does not, it indicates that the bitmap has been constructed and proceeds to S304.

[0020] S303: Extract the connection key and determine whether the current statistical status of the bitmap meets the rollback condition. If the rollback condition is met, stop the current main execution process and switch to the standby execution plan to continue the query. The query ends here. If the rollback condition is not met, construct a bitmap based on the connection key and maintain the count information of the corresponding connection key in the bitmap. Proceed to S302.

[0021] S304: Perform an external scan to determine if there are candidate rows that meet the external conditions. If so, query the bitmap based on the candidate row identifier information to determine if the current candidate row identifier information matches the filter conditions. If it matches, perform a visibility check on the data rows in the corresponding heap table. If it does not match, re-perform the external scan. If it does match, obtain the complete row data, perform a join calculation on the obtained complete row data and the internal table, output the join result, and then return to S304. If the filter conditions are not matched, return to S304.

[0022] If it does not exist, the query ends.

[0023] Fourthly, the present invention provides an electronic device, the electronic device comprising:

[0024] At least one processor;

[0025] and a memory communicatively connected to the at least one processor;

[0026] The memory stores a computer program that can be executed by the at least one processor, which enables the at least one processor to perform a Bitmap Join query optimization method based on a relational database.

[0027] Fifthly, the present invention proposes a computer-readable storage medium, characterized in that the computer-readable storage medium stores computer instructions, which are used to cause a processor to execute a Bitmap Join query optimization method based on a relational database.

[0028] Sixthly, the present invention proposes a computer program product, characterized in that the computer program product includes a computer program, which, when executed by a processor, implements a BitmapJoin query optimization method based on a relational database.

[0029] Beneficial effects: Compared with the prior art, the present invention has the following advantages:

[0030] (1) The method of the present invention effectively avoids bitmap count overflow and the resulting memory abnormality by introducing an execution plan rollback mechanism, thereby improving the stability of system operation;

[0031] (2) The method of the present invention significantly reduces invalid heap access during the external table scanning process by using a delayed table lookup strategy, thereby reducing CPU and memory access overhead;

[0032] (3) The method of the present invention has adaptive capability under different data distribution and connection base scenarios, and takes into account both execution performance and memory usage efficiency;

[0033] (4) The method of the present invention does not require intrusive modification to the existing database storage structure and has good engineering feasibility. Attached Figure Description

[0034] Figure 1 Flowchart of a Bitmap Join query optimization method based on planned rollback;

[0035] Figure 2 A flowchart of a Bitmap Join query optimization method based on delayed table lookup;

[0036] Figure 3 This is a flowchart of a Bitmap Join query optimization method that combines planned rollback and delayed table lookup. Detailed Implementation

[0037] This invention proposes an optimization technique for Bitmap Join in database execution engines. This technique combines an execution plan rollback mechanism with a delayed table lookup strategy. While ensuring the correctness of the join result, it reduces the memory overhead of the bitmap structure and reduces unnecessary heap table accesses, thereby improving the overall execution efficiency of the join operator.

[0038] Specifically, during the execution phase, the internal table is first scanned to extract the join keys based on the join conditions. A bitmap filtering structure is then constructed to characterize the set of key-value pairs that may participate in the join operation. During the construction and use of the bitmap filtering structure, the count information of each statistical unit in the bitmap is maintained in a controlled manner, and the size and count distribution characteristics of the bitmap structure are continuously monitored. When the bitmap size or count characteristics exceed a preset threshold, an execution plan rollback mechanism is triggered to dynamically adjust the execution path of the current Bitmap Join and switch the join strategy. This avoids memory amplification issues caused by bitmap structure expansion or inaccurate query issues caused by abnormal counts.

[0039] Meanwhile, during the external table scanning phase, a delayed table lookup strategy is introduced to optimize the access order of external table tuples. Specifically, when scanning the external table, join column data for bitmap matching is obtained first based on index or row identifier information, and the join column data is matched against the bitmap filtering structure. Only when the external table tuple passes the bitmap filtering and is confirmed to be necessary for the join operation is the heap table access operation triggered, the corresponding visibility check is performed, and the complete tuple data is obtained. This effectively reduces table lookups for invalid tuples during join execution and reduces unnecessary storage access overhead.

[0040] Through the above technical solution, the present invention can adaptively select a better execution path under different data distribution and join cardinality scenarios, taking into account both memory usage efficiency and execution performance, and is especially suitable for query scenarios where small tables drive large tables and join keys have high repetition.

[0041] Example 1:

[0042] like Figure 1 As shown in the figure, this embodiment of the invention provides a Bitmap Join query optimization method based on plan rollback, the process of which includes the following steps:

[0043] S101: Upon receiving a query request, the database optimizer analyzes the query statement, identifies the join operations within it, and generates an initial execution plan containing the Bitmap Join operator, while also generating at least one alternative execution plan.

[0044] S102: The execution engine scans the internal table according to the initial execution plan and extracts the join key. It returns one row; if the join key does not exist, the construction is complete, proceed to S107; otherwise, proceed to S103.

[0045] S103: During the construction and use of bitmaps, monitor the statistical status of the bitmap filtering structure in real time, including the distribution of count values, bitmap density, and memory usage.

[0046] S104: Determine whether the statistical status of the bitmap filtering structure has reached the preset backoff threshold. The backoff threshold includes the count overflow risk threshold or the memory usage threshold. If the backoff condition is met, proceed to S106; otherwise, proceed to S105.

[0047] S105: Construct a bitmap filtering structure based on the join keys, and maintain the count information of the corresponding join keys in the bitmap structure. Go to S102.

[0048] S106: Trigger the execution plan rollback mechanism, suspend the current Bitmap Join execution process, and switch to the standby execution plan to continue executing the query. End of process.

[0049] S107: Perform inner and outer join calculations according to the execution plan and output the query results.

[0050] Through the above steps, this embodiment can dynamically adjust the execution path when the bitmap filtering structure cost is abnormal, thereby avoiding abnormal consumption of system resources.

[0051] Example 2:

[0052] like Figure 2 As shown in the figure, this embodiment of the invention provides a Bitmap Join query optimization method based on delayed table lookup, the process of which includes the following steps:

[0053] S201: When performing an external table scan, retrieve the identifiers of candidate rows that meet the join conditions through the index structure, without immediately accessing the heap table. If the candidate rows exist, proceed to S202; otherwise, end the query.

[0054] S202: Based on the candidate row identifier information, query the bitmap filtering structure, then proceed to S203.

[0055] S203: Determine if the candidate row matches the bitmap filter condition. If it matches, proceed to S204; otherwise, proceed to S205.

[0056] S204: Triggers a table lookup operation, performs a visibility check on the data rows in the corresponding heap table, and retrieves the complete row data. If the data is not visible, proceed to S201; otherwise, proceed to S206.

[0057] S205: Skip the table lookup operation and proceed directly to process the next candidate row. Go to S201.

[0058] S206: Perform a join calculation on the data rows processed by delayed table lookup and the internal table, and output the join result. Proceed to S201.

[0059] Through the above steps, this embodiment can significantly reduce the number of unnecessary heap table accesses during external table scanning and improve query execution efficiency.

[0060] Example 3:

[0061] like Figure 3 As shown, this embodiment provides a Bitmap Join query optimization method that combines planned rollback and delayed table lookup. The process includes the following steps:

[0062] S301: When generating the query execution plan, determine the main execution plan that includes the Bitmap Join operator and generate a backup execution plan.

[0063] S302: The execution engine scans the internal table according to the initial execution plan and extracts the join key. It returns one row; if the join key does not exist, the construction is complete, proceed to S307; otherwise, proceed to S303.

[0064] S303: During the construction and use of bitmaps, monitor the statistical status of the bitmap filtering structure in real time, including the distribution of count values, bitmap density, and memory usage.

[0065] S304: Determine whether the statistical status of the bitmap filtering structure has reached a preset backoff threshold, whereby the backoff threshold includes a count overflow risk threshold or a memory usage threshold. If the backoff condition is met, proceed to S306; otherwise, proceed to S305.

[0066] S305: Construct a bitmap filtering structure based on the connection keys, and maintain the counting information of the corresponding connection keys in the bitmap structure. Proceed to S302.

[0067] S306: Trigger the execution plan rollback mechanism, suspend the current Bitmap Join execution flow, and switch to the backup execution plan to continue executing the query. This concludes the process. The execution plan rollback mechanism is used to dynamically adjust the join execution path, stop or downgrade the current bitmap filtering strategy, and switch to the backup join scheme to avoid memory amplification or counting anomalies caused by the bitmap structure.

[0068] S307: Perform an index scan on the external table to obtain candidate row identifiers. If the data exists, proceed to S308; otherwise, end the query.

[0069] S308: Based on the candidate row identifier information, query the bitmap filtering structure to determine whether the filtering condition is met. If the condition is met, proceed to S310; otherwise, proceed to S309.

[0070] S309: Skip the table lookup operation and proceed directly to process the next candidate row. Go to S307.

[0071] S310: Perform a table lookup operation and a visibility check. If the table is not visible, proceed to S307; otherwise, proceed to S311.

[0072] S311: Perform a join calculation on data rows processed via delayed table lookup or via rollback plan, and output the query results. Proceed to S307.

[0073] Through the above steps, this embodiment reduces the overhead of invalid table lookups while providing execution guarantees in bitmap failure scenarios, making it suitable for query environments with complex data distribution.

[0074] When scanning external tables, tuples are preferentially filtered based on a bitmap filter structure. Only tuples that match the bitmap filter are processed further. For external table tuples that pass the bitmap filter, heap table access and visibility checks are delayed. Complete tuple data is only retrieved when it is confirmed that the tuple needs to participate in the join calculation. Join calculations are then performed on the tuples processed by the delayed table lookup, and the final join result is output.

Claims

1. A Bitmap Join query optimization method based on relational databases, characterized in that: include: The database optimizer analyzes the query statement, identifies the join operations, and generates a main execution plan containing the Bitmap Join operator based on the identified join operations, while also generating at least one alternative execution plan. The internal table is scanned according to the main execution plan. A row is obtained, and it is determined whether the current row contains data. If data exists, the join key is extracted, and it is determined whether the current statistical status of the bitmap meets the rollback condition. If the rollback condition is met, the execution plan is rolled back, and the query is continued to be executed using the backup execution plan. The optimization ends. If the rollback condition is not met, a bitmap is built / updated based on the join key, and the corresponding join key count information is maintained in the bitmap. The next row is taken and this step is executed repeatedly. If no data exists, the inner and outer join calculations are completed according to the main execution plan, and the query results are output.

2. The Bitmap Join optimization method based on a relational database according to claim 1, characterized in that: The statistical status includes: count value distribution, bitmap density, and memory usage.

3. The Bitmap Join optimization method based on a relational database according to claim 2, characterized in that: The conditions for reverting to the previous state include: reaching the count overflow risk threshold or reaching the memory usage threshold.

4. A Bitmap Join query optimization method based on relational databases, characterized in that: include: Perform an external scan to determine if there are any candidate rows that meet the external conditions; If it exists, then based on the candidate row identifier information, query the bitmap to determine whether the current candidate row identifier information matches the filtering condition. If it matches, then perform a visibility check on the data row in the corresponding heap table. If not visible, perform an outer table scan again; if visible, obtain the complete row data, perform a join calculation between the obtained complete row data and the inner table, output the join result, and then perform an outer table scan to obtain the next row. If it does not exist, the query ends.

5. A Bitmap Join query optimization method based on relational databases, applied to a database execution engine, characterized in that: include: S301: Analyze the query statement, identify the join operations, generate a main execution plan containing the Bitmap Join operator based on the identified join operations, and generate at least one alternative execution plan. S302: The database execution engine scans the internal table according to the main execution plan, obtains a row, and determines whether the internal table contains data. If it does, proceed to S303; if it does not, it indicates that the bitmap has been constructed and proceeds to S304. S303: Extract the connection key and determine whether the current statistical status of the bitmap meets the rollback condition. If the rollback condition is met, terminate the current main execution flow and switch to the standby execution plan to continue the query. The query ends here. If the rollback condition is not met, a bitmap is constructed based on the connection key, and the counting information of the corresponding connection key is maintained in the bitmap, then proceed to S302; S304: Perform an external scan to determine if there are candidate rows that meet the external conditions. If so, query the bitmap based on the candidate row identifier information to determine if the current candidate row identifier information matches the filter conditions. If it does, perform a visibility check on the data rows in the corresponding heap table. If not visible, re-execute the external table scan; if visible, obtain the complete row data, perform a join calculation on the obtained complete row data and the internal table, output the join result, and then go to S304; if the filter condition is not met, go to S304. If it does not exist, the query ends.

6. An electronic device, characterized in that, The electronic device includes: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores a computer program that can be executed by the at least one processor, which is then executed by the at least one processor to enable the at least one processor to perform the Bitmap Join query optimization method based on a relational database as described in any one of claims 1-5.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that cause a processor to execute the Bitmap Join query optimization method based on a relational database as described in any one of claims 1-5.

8. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the BitmapJoin query optimization method based on a relational database as described in any one of claims 1-5.

Images