Data query method and device, computer equipment and computer readable storage medium

Abstract

The invention is suitable for the technical field of databases, and relates to a data query method and device, equipment and a medium. The method comprises the following steps: converting a received structured query statement into a physical execution plan, and if the physical execution plan comprises a Hash join operator, allocating a corresponding memory quota to the Hash join operator; if the first memory demand of the Hash table constructed by the Hash join operator is greater than the memory quota, executing the Hash join operator by adopting a memory elimination strategy; the memory elimination strategy comprises the steps that an execution quota is divided from memory quotas, the execution quota comprises a first sub-quota used for storing a hash table structure corresponding to a hash table and connection key data and a second sub-quota used for storing a data record of a small table related to the hash table, and the first sub-quota is larger than the second sub-quota; and respectively performing longest unused elimination management on the first sub-quota and the second sub-quota in the execution process of the Hash join operator. According to the invention, the execution efficiency of the Hash connection can be improved under the condition that the memory is limited.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a data query method, a data query device, a computer device, and a computer-readable storage medium. Background Technology

[0002] Conventional databases are mostly relational databases, storing data in tables. For relational databases, structured query statements (SMS) can be used to query and manipulate the data. Due to their intuitiveness and rich functionality, SMS is a widely used query language in the field of database querying. Hash joins, as a commonly used join algorithm in database queries, achieve fast matching by building hash tables and have high execution efficiency in large-scale data processing. However, when available memory is limited, hash joins can cause a sharp performance drop due to memory overflow. Summary of the Invention

[0003] This invention provides a data query method, a data query device, a computer device, and a computer-readable storage medium, which can improve the execution efficiency of hash joins under memory-constrained conditions.

[0004] In a first aspect, embodiments of the present invention provide a data query method, including: It receives input structured query statements and converts them into physical execution plans; If the physical execution plan includes a hash join operator, then allocate the corresponding memory quota to the hash join operator; Determine the initial memory requirement for the hash join operator to build the hash table. If the initial memory requirement exceeds the memory quota, then execute the hash join operator using a memory eviction policy. Memory eviction policies include: An execution quota is allocated from the memory quota. The execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks. The first sub-quota is greater than the second sub-quota. During the execution of the hash join operator, the first and second sub-quotas are subject to the longest-unused eviction management.

[0005] Secondly, embodiments of the present invention provide a data query device, comprising: The plan generation module receives the input structured query statement and converts it into a physical execution plan. The quota management module is used to allocate corresponding memory quotas to hash join operators if the physical execution plan includes hash join operators; The operator execution module is used to determine the first memory requirement for the hash join operator to build the hash table. If the first memory requirement is greater than the memory quota, the hash join operator is executed using a memory eviction policy. Memory eviction policies include: An execution quota is allocated from the memory quota. The execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks. The first sub-quota is greater than the second sub-quota. During the execution of the hash join operator, the first and second sub-quotas are subject to the longest-unused eviction management.

[0006] Optionally, in one embodiment, the operator execution module is further configured to evaluate the first IO overhead of executing the hash join operator using a partition hash join strategy, and to evaluate the second IO overhead of executing the hash join operator using a memory eviction strategy; if the second IO overhead is less than the first IO overhead, then the hash join operator is executed using a memory eviction strategy.

[0007] Optionally, in one embodiment, the operator execution module is used to obtain the first occupied space of the small table and the second occupied space of the large table involved in the hash table; determine the number of partitions based on the first occupied space and memory quota, and determine the number of disk read / write operations based on the number of partitions; obtain the unit read IO overhead and unit write IO overhead of the disk, and calculate the first IO overhead based on the first occupied space, the second occupied space, the number of disk read / write operations, the unit read IO overhead, and the unit write IO overhead.

[0008] Optionally, in one embodiment, the operator execution module is used to obtain the second memory requirement of the hash table structure, the total number of records in the large table, and the selectivity of the hash join operator; determine the disk access probability based on the first occupied space, the first memory requirement, and the second memory requirement; calculate the read IO cost based on the total number of records, the selectivity, the disk access probability, the unit read IO cost, and the record length of the small table; calculate the write IO cost based on the first occupied space and the unit write IO cost; and calculate the second IO cost based on the read IO cost and the write IO cost.

[0009] Optionally, in one embodiment, the operator execution module is further configured to: if the second IO overhead is less than the first IO overhead, then execute the hash join operator using a partition hash join strategy; during the execution of the hash join operator using the partition hash join strategy, if the current partition meets the strategy switching condition, then execute the hash join operation of the current partition using a memory eviction strategy.

[0010] Optionally, in one embodiment, the policy switching condition includes: the sum of the third memory requirement of the current partition and the fourth memory requirement of the maintenance partition is greater than the memory quota.

[0011] Optionally, in one embodiment, the operator execution module is further configured to execute the hash join operator using a memory eviction policy if partitioning fails using the partition hash join strategy.

[0012] Thirdly, the computer device provided in the embodiments of the present invention includes a processor and a memory, wherein the memory stores a computer program that can run on the processor, and the processor implements the data query method provided in the embodiments of the present invention when running the computer program.

[0013] Fourthly, the computer-readable storage medium provided in the embodiments of the present invention stores a computer program, which, when executed by a processor, implements the data query method provided in the embodiments of the present invention.

[0014] The data query scheme provided by this invention allocates a corresponding memory quota to the hash join operator when the physical execution plan of the structured query statement includes the hash join operator. If the initial memory requirement for building the hash table by the hash join operator exceeds the memory quota, a memory eviction policy is adopted to execute the hash join operator. This memory eviction policy includes: allocating an execution quota from the memory quota, and further dividing the execution quota into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks, and the second sub-quota is used to store the data records of the smaller tables involved in the hash table in units of data blocks, with the first sub-quota being larger than the second sub-quota. During the execution of the hash join operator, the first and second sub-quotas are subject to least-used eviction management. In this way, by allocating memory resources according to priority and implementing differentiated eviction of different sub-quotas using the least-used policy, the memory occupation of the hash table structure and the smaller table data is effectively balanced. This maximizes the execution efficiency of hash joins in a limited memory environment and avoids query failures or performance drops due to memory overflow. Attached Figure Description

[0015] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0016] Figure 1 This is a schematic diagram of an application environment for the data query method provided in an embodiment of the present invention; Figure 2This is a flowchart illustrating the data query method provided in an embodiment of the present invention; Figure 3 This is a schematic diagram of the structure of the data query device provided in an embodiment of the present invention; Figure 4 This is a schematic diagram of the structure of a computer device provided in an embodiment of the present invention. Detailed Implementation

[0017] In the following description, specific details such as particular system architectures and techniques are set forth for illustrative purposes and not for limitation, in order to provide a thorough understanding of the embodiments of the invention. However, those skilled in the art will understand that the invention can be implemented in other embodiments without these specific details. In other instances, detailed descriptions of well-known systems, apparatuses, circuits, and methods are omitted so as not to obscure the description of the invention with unnecessary detail.

[0018] It should be understood that, when used in this specification and the appended claims, the term "comprising" indicates the presence of the described features, integrals, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integrals, steps, operations, elements, components and / or collections thereof.

[0019] It should also be understood that the term "and / or" as used in this specification and the appended claims refers to any combination of one or more of the associated listed items and all possible combinations, and includes such combinations.

[0020] As used in this specification and the appended claims, the term "if" may be interpreted, depending on the context, as "when," "once," "in response to determination," or "in response to detection." Similarly, the phrase "if determined" or "if [described condition or event] is detected" may be interpreted, depending on the context, as meaning "once determined," "in response to determination," "once [described condition or event] is detected," or "in response to detection of [described condition or event]."

[0021] Furthermore, in the description of this invention and the appended claims, the terms "first," "second," "third," etc., are used only to distinguish descriptions and should not be construed as indicating or implying relative importance.

[0022] References to "one embodiment" or "some embodiments" as described in this specification mean that one or more embodiments of the invention include a specific feature, structure, or characteristic described in connection with that embodiment. Therefore, the phrases "in one embodiment," "in some embodiments," "in other embodiments," "in still other embodiments," etc., appearing in different parts of this specification do not necessarily refer to the same embodiment, but rather mean "one or more, but not all, embodiments," unless otherwise specifically emphasized. The terms "comprising," "including," "having," and variations thereof mean "including but not limited to," unless otherwise specifically emphasized.

[0023] It should be understood that the sequence number of each step in the following embodiments does not imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0024] To illustrate the technical solution of the present invention, specific embodiments are described below.

[0025] Please refer to Figure 1 , Figure 1 This is a schematic diagram illustrating an application environment of the data query method provided by the present invention. As one implementation, the data query method provided by the present invention can be applied to a server 100, which is connected to a terminal device 200 via a network. The server 100 provides database services to the terminal device 200. The network, used as a medium to provide a communication link between the server 100 and the terminal device 200, can include various connection types, such as wired communication links, wireless communication links, etc., and the embodiments of the present invention do not limit this.

[0026] It should be noted that, Figure 1 The server 100, network, and terminal device 200 shown are merely illustrative. Depending on actual needs, there can be any number of servers 100. For example, the server 100 can be implemented by a standalone physical server, a server cluster consisting of multiple servers, or a distributed system, etc., and the terminal device 200 can be any device such as a mobile phone, tablet, desktop computer, or laptop.

[0027] In some embodiments, server 100 can receive structured query statements generated and input by user query operations of terminal device 200, receive the input structured query statements, and convert the structured query statements into physical execution plans; if the physical execution plan includes a hash join operator, then allocate a corresponding memory quota for the hash join operator; determine the first memory requirement for the hash join operator to build a hash table, and if the first memory requirement is greater than the memory quota, then execute the hash join operator using a memory eviction policy; the memory eviction policy includes: allocating an execution quota from the memory quota, dividing the execution quota into a first sub-quota and a second sub-quota, the first sub-quota being used to store the hash table structure and join key data corresponding to the hash table in units of data blocks, the second sub-quota being used to store the data records of the small tables involved in the hash table in units of data blocks, and the first sub-quota being greater than the second sub-quota; during the execution of the hash join operator, the first sub-quota and the second sub-quota are respectively subject to least-used eviction management; finally, server 100 returns a data query response including the execution result of the hash join operator to terminal device 200 for user viewing and use. In practical applications, this data query method can be widely used in various big data scenarios, such as data warehouse queries, real-time analysis systems, and online transaction processing, effectively improving the execution efficiency and resource utilization of complex queries. By dynamically allocating memory quotas and combining them with fine-grained eviction policies, it can maximize the performance of hash joins in a limited memory environment, avoiding query failures or performance drops caused by memory overflow.

[0028] It should be noted that the data query system described above is merely an example, intended to more clearly illustrate the technical solutions of the embodiments of the present invention, and does not constitute a limitation on the technical solutions provided by the embodiments of the present invention. As those skilled in the art will know, with the evolution of data query systems and the emergence of new business scenarios, the technical solutions provided by the embodiments of the present invention are also applicable to similar technical problems.

[0029] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the embodiments described below are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0030] Please refer to Figure 2 , Figure 2 This is a flowchart illustrating a data query method provided in an embodiment of the present invention, as shown below. Figure 2 As shown, the flow of this data query method can be as follows: In S110, the input structured query statement is received and converted into a physical execution plan.

[0031] Structured Query Language (SQL) is a standard language for managing relational databases, whose syntax supports data querying, updating, defining, and controlling operations.

[0032] Structured query statements are statements built on the syntax of the Structured Query Language (SCL) to express the user's query intent.

[0033] An abstract syntax tree (AST) is an abstract representation of the syntax structure of source code. It expresses program syntax in a tree structure, with each node representing a syntax structure. For example, clauses such as SELECT, FROM, and WHERE correspond to different node types.

[0034] An execution plan is a description of the specific operation flow of a query statement in a database system. It can be divided into physical execution plans and logical execution plans. The logical execution plan describes the algebraic expression of the operation, while the physical execution plan specifies the operator sequence and access path of the operation.

[0035] The hash join operator is one of the core operators in the physical execution plan used to implement table join operations. It accelerates the join matching process by building a hash table and bucketing related data.

[0036] The following description uses the server as the execution subject of the data query method provided by this invention.

[0037] The server can receive structured query statements input from terminal devices. For example, the structured query statement received by the server might be as follows: SELECT * FROM table_a JOIN table_b ON table_a.id = table_b.a_id WHEREtable_a.value>100; The above structured query statement means: retrieve the combined records from tables table_a and table_b that satisfy table_a.value greater than 100, where the two tables are joined by table_a.id and table_b.a_id.

[0038] Upon receiving a structured query, the server first performs lexical and syntactic analysis to generate an initial abstract syntax tree. Then, semantic validation verifies the validity of table names, fields, and join conditions. Next, the optimizer, based on statistical information and a cost model, transforms the query into an optimal physical execution plan. This plan includes key decisions such as selecting a suitable join algorithm, determining the join order, and the data scanning method. The physical execution plan may contain a sequence of operators such as scanning, filtering, hash joins, and projection, each corresponding to a specific data processing action.

[0039] As shown above, after the server converts the received structured query statement into a physical execution plan, it further identifies whether the physical execution plan contains a hash join operator.

[0040] In S120, if the physical execution plan includes a hash join operator, then a corresponding memory quota is allocated to the hash join operator.

[0041] In this embodiment of the invention, if a hash join operator is detected in the physical execution plan, the server allocates a corresponding memory quota to the hash join operator. This memory quota is the upper limit of memory that can be used to build a hash table and store intermediate data during the hash join execution process.

[0042] It should be noted that the specific value of the memory quota is not limited in the embodiments of the present invention. For example, it can be the default value configured by the system, or the server can dynamically calculate and allocate a reasonable memory quota based on the total available memory of the database, the current number of concurrent queries, and the memory requirements of other operators.

[0043] For example, the server statically allocates 5000KB of memory to the hash join operator. Or, the server dynamically calculates that the memory quota currently available to the hash join operator is 1000KB based on the total available memory of the database, the current number of concurrent queries, and the memory requirements of other operators.

[0044] In S130, the first memory requirement for the hash join operator to build the hash table is determined. If the first memory requirement is greater than the memory quota, the hash join operator is executed using a memory eviction policy. Memory eviction policies include: An execution quota is allocated from the memory quota. The execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks (which can be the original data record itself or the index of the data record, such as RowID, etc.). The first sub-quota is larger than the second sub-quota. During the execution of the hash join operator, the first and second sub-quotas are subject to the longest-unused eviction management.

[0045] A hash table is a data structure that maps join keys to storage locations using a hash function. It consists of buckets and collision chains. Each join key has its hash value calculated using a hash function, and the bucket index is obtained by taking the modulo of the number of buckets. Keys with the same hash value are linked together through a collision chain.

[0046] Small tables and large tables are data tables loaded into memory in a hash join to build the hash table. Small tables refer to data tables with a smaller amount of data, while large tables refer to data tables with a larger amount of data.

[0047] The join key is a key field used to link two tables, a smaller table and a larger table, and the join key data is the specific value of the join key.

[0048] It should be noted that this embodiment of the invention provides a memory eviction policy in which the server divides the memory quota allocated to the hash join operator into two parts: a maintenance quota for maintaining the metadata and status data required for the execution process, and an execution quota. The metadata includes information such as the input data source information of the hash join, the current processing progress, and error status identifiers; the status data includes statistical information or control signals generated during runtime.

[0049] The server further divides the allocated execution quota into a first sub-quota and a second sub-quota. The former is used to store the hash table structure and join key data, while the latter is used to store the data records of the smaller table. The first sub-quota is larger than the second sub-quota to prioritize the caching efficiency of the index structure. During execution, a least-used data block-based eviction mechanism is implemented for each of the two sub-quotas. When a data block in a sub-quota has remained unaccessed for the longest time, it is marked as an object that can be evicted and released first to free up space. If the corresponding sub-quota is full when new data is written, the eviction mechanism is triggered, the least-used data block is removed, and new data is loaded to ensure that hot data continues to reside in memory.

[0050] In this embodiment of the invention, there is no limitation on the specific ratio of the first sub-quota to the second sub-quota. For example, the ratio of the first sub-quota to the second sub-quota can be configured as 4:1, that is, 80% of the execution quota is allocated to the first sub-quota and 20% of the execution quota is allocated to the second sub-quota.

[0051] Furthermore, after the initial allocation of the first and second sub-quotas is completed, this ratio can be dynamically adjusted based on the actual runtime load. For example, if one of the two sub-quotas has not reached its quota limit and the other is experiencing frequent evictions, the idle quota can be allocated to the more stressed sub-quota as needed to improve the overall cache hit rate and ensure the execution efficiency and memory usage balance of hash connections under different data distribution scenarios. For instance, assuming the first sub-quota is frequently evictioned while the second sub-quota still has idle space, the server can dynamically reclaim some of the second sub-quota space and add it to the first sub-quota.

[0052] The above memory eviction strategies improve the access efficiency of hash table structures and reduce the risk of frequent rehashing or disk overflow caused by insufficient memory through differentiated quota allocation and independent eviction control, thereby optimizing overall query performance.

[0053] In this embodiment of the invention, after allocating memory quotas to the hash join operator, the server further determines the memory requirement for the hash join operator to build the hash table, denoted as the first memory requirement. For example, the server can determine the memory requirement for the hash join operator to build the hash table in the following manner: The first memory requirement = the space occupied by the hash table structure + the size of the join key data + the size of the small table data records; The space occupied by the hash table structure is determined by the length of the bucket array and the number of bytes occupied by each bucket. The size of the join key data depends on the join key field type and the number of rows. The size of the small table data record is equal to the number of rows in the small table multiplied by the average number of bytes per row.

[0054] For example, if the join key is an integer and the smaller table contains 100,000 rows of data records, with an average row length of 20 bytes, a bucket length of 131,072, and each bucket occupying 8 bytes, then the hash table structure occupies 131,072 × 8 = 1,048,576 bytes. The join key data size is 100,000 × 4 = 400,000 bytes, and the smaller table data record size is 100,000 × 20 = 2,000,000 bytes. Therefore, the initial memory requirement is 1,048,576 + 400,000 + 2,000,000 = 3,448,576 bytes.

[0055] As described above, after determining the first memory requirement, the server compares the first memory requirement with the memory quota allocated to the hash join operator. If the first memory requirement is less than or equal to the memory quota, the server directly allocates memory on demand and starts hash table construction. If the first memory requirement is greater than the memory quota, the server uses the memory eviction policy provided by this invention to execute the hash join operator.

[0056] The execution process of the hash join operator can be divided into two stages: Construction Phase: Only the hash table structure and join key data need to be accessed; the small table data records do not require access. After retrieving all the small table data records, the data records belonging to the logical partition currently undergoing hash joining (here, a logical partition refers to a logical unit processed in batches, not a fixed memory allocation in the traditional partitioned hash join strategy) are used to build the hash table. The hash table structure and join key data share the first sub-quota, while the small table data records exclusively occupy the second sub-quota. During construction, independent memory eviction control is implemented for the first and second sub-quotas. For example, when the first sub-quota is insufficient, the bucket or join key cache that has not been accessed for the longest time is evicted first. After the hash table construction is complete, the probing phase begins.

[0057] Probing Phase: Data records from the large table are retrieved. For data records not belonging to the logical partition currently undergoing a hash join, they are written to external storage (e.g., disk). For data records belonging to the logical partition currently undergoing a hash join, the hash table structure is searched based on the join key data of each row. If a matching small table data record is found, the join result is constructed using the found small table data record and the current large table data record, and then output. During the search process, if the hash table structure or join key data being searched for is not resident in memory, the least accessed data block is evicted from memory and moved to external storage. After freeing up memory space, the corresponding data block is loaded from external storage into memory to continue the search, ensuring that the probing operation continues to execute efficiently. Throughout the process, the server dynamically monitors the usage of each sub-quota, prioritizing the retention of frequently accessed hash structures and join key data in memory, thereby reducing I / O overhead and improving overall connection performance.

[0058] In this embodiment of the invention, during the execution of the hash join operator, the hash table structure, join key data, and small table data records are all organized in array form. For example, the hash table structure is stored using a fixed-length array; for join key data, if the data record is fixed-length and the size of the join key data is 1 / 100 of the data block size, it is stored using a fixed-length array; otherwise, it is stored using a variable-length array; for small table data records, it is stored using a variable-length array.

[0059] Furthermore, it should be noted that in actual implementation, the hash table structure only stores the join key data and the array index of the sub-table data records, rather than the complete data content, and the corresponding data records are associated through the index.

[0060] In this embodiment of the invention, after the server completes the physical execution plan, including the hash join operator, it returns the final execution result as a data query response to the terminal device.

[0061] Optionally, in one embodiment, before executing the hash join operator using a memory eviction policy, the method further includes: The first I / O overhead of executing the hash join operator using a partitioned hash join strategy and the second I / O overhead of executing the hash join operator using a memory eviction strategy are evaluated. If the overhead of the second I / O is less than that of the first I / O, then the hash join operator is executed using a memory eviction policy.

[0062] The partitioned hash join strategy refers to dividing both large and small tables into multiple physical partitions according to a hash function, so that the data volume of each physical partition is adapted to the memory capacity. Each physical partition is loaded into memory sequentially to perform a hash join, while the remaining partitions are temporarily stored on disk.

[0063] Considering that the partition hash join strategy requires multiple data exchanges, which increases IO overhead, while the memory eviction strategy provided by this invention can achieve lower IO overhead when the cache hit rate is high, especially in scenarios where the join key distribution has strong locality, this invention dynamically evaluates the IO overhead of the two strategies and prioritizes the strategy with lower IO overhead to execute the hash join operator, thereby adaptively optimizing query performance under different data distribution characteristics and effectively improving execution efficiency.

[0064] The server first evaluates the IO overhead of executing the hash operator using the partition hash join strategy according to the configured first overhead evaluation strategy, denoted as the first IO overhead. Then, it calculates the IO overhead of executing the hash join operator using the memory eviction strategy according to the configured second overhead evaluation strategy, denoted as the second IO overhead. The specific configuration of the first and second overhead evaluation strategies is not limited here. For example, the first overhead evaluation strategy can be set to estimate disk IO volume based on the number of data partitions, and the second overhead evaluation strategy can be set to predict memory access costs based on cache hit rate.

[0065] As shown above, after the server evaluates the first IO overhead of executing the hash join operator using the partition hash join strategy and the second IO overhead of executing the hash join operator using the memory eviction strategy, it further compares the size of the first IO overhead and the second IO overhead. If the second IO overhead is less than the first IO overhead, the server executes the hash join operator using the memory eviction strategy.

[0066] Optionally, in one embodiment, evaluating the first IO overhead of performing the hash join operator using a partitioned hash join strategy includes: Get the first space occupied by the small table, and get the second space occupied by the large table involved in the hash table; The number of partitions is determined based on the initial occupied space and memory quota, and the number of disk read / write operations is determined based on the number of partitions; Obtain the unit read IO cost and unit write IO cost of the disk, and calculate the first IO cost based on the first occupied space, the second occupied space, the number of disk read and write operations, the unit read IO cost, and the unit write IO cost.

[0067] This invention provides an optional first overhead assessment strategy, wherein the server first obtains the first occupied space of the small table (i.e., the total amount of data of the small table on the disk) and the second occupied space of the large table (i.e., the total amount of data of the large table on the disk) as the basis for calculation; then, based on the first occupied space and memory quota, the required number of partitions is calculated, and then the number of disk read / write operations is determined based on the number of partitions; next, combining the unit read IO overhead (i.e., the time overhead required to read 1KB of data) and the unit write IO overhead (i.e., the time overhead required to write 1KB of data), and integrating the first occupied space, the second occupied space, and the number of disk read / write operations, the total IO overhead when using the partition hash join strategy is finally calculated, which is the first IO overhead, and can be expressed as: IO Cost1 = (First occupied space + Second occupied space) * (Number of partitions - 1) * (Unit read IO cost + Unit write IO cost); Among them, IO Cost1 is the first IO overhead. Since each other physical partition, except the initial physical partition, requires one disk read operation and one disk write operation, the number of read and write operations is (number of partitions - 1).

[0068] For example, assuming the small table occupies 100MB of space, the large table 1GB, and the memory quota is 200MB, then the number of partitions is (100 + 1024) / 200 ≈ 6, so the number of disk read / write operations is 5. If the unit read IO cost and the unit write IO cost are both 0.1ms / KB, then the first IO cost IOCost1 = (100 + 1024) × 1024 × 5 × (0.1 + 0.1) / 1000 ≈ 115.7 seconds.

[0069] Optionally, in one embodiment, evaluating the second I / O overhead of performing the hash join operator using a memory eviction policy includes: Obtain the second memory requirement of the hash table structure, the total number of records in the large table, and the selectivity of the hash join operator; Determine the disk access probability based on the first occupied space, the first memory requirement, and the second memory requirement; Read I / O overhead is calculated based on the total number of records, selectivity, disk access probability, unit read I / O overhead, and record length of the small table; write I / O overhead is calculated based on the first occupied space and unit write I / O overhead. The second I / O overhead is calculated based on the read I / O overhead and the write I / O overhead.

[0070] This invention provides an optional first overhead assessment strategy, wherein the server first obtains the memory space required by the hash table structure during the construction phase as the second memory requirement, obtains the total number of data records in the large table, obtains the record length of the small table, and obtains the selectivity of the hash join operator from the physical execution plan; then, based on the first occupied space of the small table, the first memory requirement of the hash table structure, and the second memory requirement, the probability of disk access due to insufficient memory is calculated; combining the total number of records in the large table, the selectivity, the disk access probability, the unit read IO overhead, and the record length of the small table, the read IO overhead is calculated; and the write IO overhead is calculated based on the first occupied space and the unit write IO overhead. Finally, the read IO overhead and the write IO overhead are added together to obtain the second IO overhead, which can be expressed as: IO Cost2 = Initial space occupied * Unit write IO cost + Total number of records in the large table * Selectivity * Record length in the small table * Disk access probability * Unit read IO cost; Wherein, disk access probability = (first occupied space - memory quota + second memory requirement) / first occupied space; The second memory requirement = number of duplicate records in the smaller table * space occupied by each distinct value + total number of records in the smaller table * space occupied by each duplicate value.

[0071] For example, assuming a memory quota of 200MB, a smaller table with 10 duplicate records (each distinct value occupies 20 bytes, duplicate values ​​occupy 8 bytes), a total of 1.5 million records in the smaller table, and a record length of 716 bytes, and a total of 1 million records in the larger table, with a selectivity of 0.8, a read IO overhead of 0.1ms / KB, and a write IO overhead of 0.1ms / KB, then the second memory requirement = 10 × 20 + 1,500,000 × 8 = 200 + 12,000,000 = 12,000,200 bytes ≈ 11.45 MB, First occupied space = 1,500,000 × 716 = 1074000000 bytes ≈ 1024MB, so the disk access probability = (1024 - 200 + 11.45) / 1024 ≈ 0.816. The read IO overhead = 1000000 × 0.8 × 716 × 0.816 × 0.1 / 1024 = 45645ms, and the write IO overhead = 1024 × 0.1 × 1024 = 104857.6ms. Therefore, the second IO overhead is 45645 + 104857.6 = 150502.6ms.

[0072] Optionally, in one embodiment, after evaluating the first IO overhead of performing the hash join operator using a partitioned hash join strategy and the second IO overhead of performing the hash join operator using a memory eviction strategy, the method further includes: If the overhead of the second I / O is greater than that of the first I / O, then the hash join operator is executed using the partition hash join strategy; During the execution of the hash join operator using the partition hash join strategy, if the currently executed physical partition meets the strategy switching conditions, the memory eviction strategy is used to execute the hash join operation of the physical partition.

[0073] In this embodiment of the invention, after the server evaluates the first IO overhead of executing the hash join operator using the partition hash join strategy and the second IO overhead of executing the hash join operator using the memory eviction strategy, it further compares the size of the first IO overhead and the second IO overhead. If the second IO overhead is greater than the first IO overhead, the server executes the hash join operator using the partition hash join strategy to reduce the overall IO overhead.

[0074] Furthermore, during the execution of the hash join operator using the partition hash join strategy, if the currently executing physical partition meets the strategy switching conditions, then the memory eviction strategy is used to execute the hash join operation for that physical partition. The configuration of the strategy switching conditions is not limited here and can be flexibly set according to the actual scenario.

[0075] Optionally, in one embodiment, the policy switching condition includes: the sum of the third memory requirement of the physical partition and the fourth memory requirement of the maintenance partition is greater than the memory quota.

[0076] It should be noted that if the memory quota is insufficient to accommodate a physical partition, the physical partition needs to be split into multiple sub-partitions for processing.

[0077] The third memory requirement of a physical partition refers to the memory space required to perform a hash join operation on the physical partition, and the fourth memory requirement for maintaining the partition refers to the memory space required to maintain the multiple sub-partitions that the physical partition is divided into.

[0078] In actual execution, the large number of sub-partitions may lead to a significant fourth memory requirement for maintaining these sub-partitions. This could result in the allocated memory quota being insufficient to simultaneously meet both the third and fourth memory requirements, causing hash joins to fail. In this situation, the server can employ the memory eviction policy provided by this invention to perform the hash join operation on the physical partition, ensuring the smooth execution of the hash join operation.

[0079] Optionally, in one embodiment, after executing the hash join operator using the partition hash join strategy, the method further includes: If the partition hash join strategy fails to partition the physical partitions, the process will proceed to the step of executing the hash join operator using the memory eviction strategy.

[0080] It is understandable that in actual execution, there may be situations where physical partitioning fails, meaning that even physical partitioning cannot reduce memory usage.

[0081] For example, when there are a large number of duplicate key values ​​in the smaller tables involved in the join, the partition hash join strategy algorithm will fail: Regardless of the hash modulo operation, these duplicate key values ​​will be assigned to the same physical partition, resulting in data skew and making it impossible to reduce memory usage by dividing the physical partition.

[0082] For example, assuming a memory quota of 5000KB, a small table with 40,000 data records, and the join key requiring only two distinct values ​​(each value corresponding to 20,000 records, with each record being 4100 bytes), if the table is divided into two physical partitions using a hash modulo method, each partition would need to hold approximately 20,000 records. The memory required for a single physical partition would be 20,000 × 4100 ≈ 80,000KB, far exceeding the 5000KB memory quota. Therefore, partitioning cannot alleviate the memory pressure. Clearly, no matter how the physical partitions are divided, the memory requirements cannot be met; that is, physical partitioning fails, and the partition hash join strategy becomes ineffective. In this case, the server uses a memory eviction policy to execute the hash join operator, ensuring the normal execution of the hash join operation.

[0083] As can be seen from the above, the data query scheme provided by this invention allocates a corresponding memory quota to the hash join operator when the physical execution plan of the structured query statement includes the hash join operator. If the first memory requirement for building the hash table by the hash join operator exceeds the memory quota, a memory eviction policy is adopted to execute the hash join operator. This memory eviction policy includes: allocating an execution quota from the memory quota, and further dividing the execution quota into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks, and the second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks, and the first sub-quota is greater than the second sub-quota. During the execution of the hash join operator, the first sub-quota and the second sub-quota are subject to least-used eviction management. In this way, by allocating memory resources according to priority and using the least-used policy to implement differentiated eviction of different sub-quotas, the memory occupation of the hash table structure and the small table data is effectively balanced, maximizing the execution efficiency of hash joins in a limited memory environment and avoiding query failures or performance drops due to memory overflow.

[0084] To facilitate better implementation of the data query method described above, this embodiment of the invention also provides a corresponding data query device. The meanings of the terms used are the same as in the data query method described above; for specific implementation details, please refer to the descriptions in the above method embodiments.

[0085] Please refer to Figure 3 , Figure 3 This is a schematic diagram of the structure of a data query device provided in an embodiment of the present invention. The data query device may include a plan generation module 210, a quota management module 220, and an operator execution module 230, wherein... The plan generation module 210 is used to receive the input structured query statement and convert the structured query statement into a physical execution plan; The quota management module 220 is used to allocate a corresponding memory quota to the hash join operator if the physical execution plan includes a hash join operator; The operator execution module 230 is used to determine the first memory requirement for the hash join operator to build the hash table. If the first memory requirement is greater than the memory quota, the hash join operator is executed using a memory eviction policy. Memory eviction policies include: An execution quota is allocated from the memory quota. The execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks. The first sub-quota is greater than the second sub-quota. During the execution of the hash join operator, the first and second sub-quotas are subject to the longest-unused eviction management.

[0086] Optionally, in one embodiment, the operator execution module 230 is further configured to evaluate the first IO overhead of executing the hash join operator using a partition hash join strategy, and to evaluate the second IO overhead of executing the hash join operator using a memory eviction strategy; if the second IO overhead is less than the first IO overhead, then the hash join operator is executed using a memory eviction strategy.

[0087] Optionally, in one embodiment, the operator execution module 230 is used to obtain the first occupied space of the small table and the second occupied space of the large table involved in the hash table; determine the number of partitions based on the first occupied space and memory quota, and determine the number of disk read / write operations based on the number of partitions; obtain the unit read IO overhead and unit write IO overhead of the disk, and calculate the first IO overhead based on the first occupied space, the second occupied space, the number of disk read / write operations, the unit read IO overhead, and the unit write IO overhead.

[0088] Optionally, in one embodiment, the operator execution module 230 is used to obtain the second memory requirement of the hash table structure, the total number of records in the large table, and the selectivity of the hash join operator; determine the disk access probability based on the first occupied space, the first memory requirement, and the second memory requirement; calculate the read IO cost based on the total number of records, the selectivity, the disk access probability, the unit read IO cost, and the record length of the small table; calculate the write IO cost based on the first occupied space and the unit write IO cost; and calculate the second IO cost based on the read IO cost and the write IO cost.

[0089] Optionally, in one embodiment, the operator execution module 230 is further configured to: if the second IO overhead is less than the first IO overhead, then execute the hash join operator using a partition hash join strategy; during the execution of the hash join operator using the partition hash join strategy, if the current partition meets the strategy switching condition, then execute the hash join operation of the current partition using a memory eviction strategy.

[0090] Optionally, in one embodiment, the policy switching condition includes: the sum of the third memory requirement of the current partition and the fourth memory requirement of the maintenance partition is greater than the memory quota.

[0091] Optionally, in one embodiment, the operator execution module 230 is further configured to execute the hash join operator using a memory eviction policy if partitioning fails using the partition hash join strategy.

[0092] It should be noted that the information interaction and execution process between the above modules are based on the same concept as the method embodiments of the present invention. For details on their specific functions and technical effects, please refer to the method embodiments section, which will not be repeated here.

[0093] Figure 4 This is a schematic diagram of the structure of a computer device provided in an embodiment of the present invention. Figure 4 As shown, the computer device includes: at least one processor ( Figure 4 The diagram shows only one of the following: a memory and a computer program stored in the memory and capable of running on at least one processor. When the processor executes the computer program, it implements the steps in the above-described data query method embodiments.

[0094] This computer device may include, but is not limited to, a processor and memory. Those skilled in the art will understand that... Figure 4 The examples of computer devices are merely examples and do not constitute a limitation on computer devices. Computer devices may include more or fewer components than shown in the illustration, or combinations of certain components, or different components, such as network interfaces, displays, and input devices.

[0095] The processor referred to can be a CPU, but it can also be other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. A general-purpose processor can be a microprocessor or any conventional processor.

[0096] Memory includes readable storage media, internal memory, etc., wherein internal memory can be the RAM of a computer device, providing an environment for the operation of the operating system and computer-readable instructions stored in the readable storage media. The readable storage media can be the hard drive of a computer device, or in other embodiments, it can be an external storage device of the computer device, such as a plug-in hard drive, Smart Media Card (SMC), Secure Digital (SD) card, or Flash Card. Furthermore, memory can include both internal storage units and external storage devices of the computer device. Memory is used to store the operating system, applications, bootloader, data, and other programs, such as program code for computer programs. Memory can also be used to temporarily store data that has been output or will be output.

[0097] Those skilled in the art will understand that, for the sake of convenience and brevity, the above-described division of functional units and modules is used as an example. In practical applications, the functions described above can be assigned to 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. The functional units and modules in the embodiments can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit. Furthermore, the specific names of the functional units and modules are only for easy differentiation and are not intended to limit the scope of protection of this invention. The specific working process of the units and modules in the above device can be referred to the corresponding process in the foregoing method embodiments, and will not be repeated here. If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the present invention can implement all or part of the processes in the methods of the above embodiments by instructing related hardware through a computer program. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the above method embodiments. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. A computer-readable medium can include at least: any entity or device capable of carrying computer program code, a recording medium, a computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media. Examples include USB flash drives, portable hard drives, magnetic disks, or optical disks. In some jurisdictions, according to legislation and patent practice, computer-readable media cannot be electrical carrier signals or telecommunication signals.

[0098] The present invention can implement all or part of the processes in the methods of the above embodiments, or it can be accomplished by a computer program product. When the computer program product is run on a computer device, the computer device executes the steps in the above method embodiments.

[0099] In the above embodiments, the descriptions of each embodiment have different focuses. For parts that are not described in detail or recorded in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0100] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.

[0101] In the embodiments provided by this invention, it should be understood that the disclosed apparatus / computer devices and methods can be implemented in other ways. For example, the apparatus / computer device embodiments described above are merely illustrative. For instance, the division of modules or units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the mutual coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection between apparatuses or units may be electrical, mechanical, or other forms.

[0102] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0103] The above embodiments are only used to illustrate the technical solutions of the present invention, and are not intended to limit it. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included within the protection scope of the present invention.

Claims

1. A data query method, characterized in that, include: Receive the input structured query statement and convert the structured query statement into a physical execution plan; If the physical execution plan includes a hash join operator, then allocate a corresponding memory quota to the hash join operator; Determine the first memory requirement for the hash table to be constructed by the hash join operator. If the first memory requirement is greater than the memory quota, then execute the hash join operator using a memory eviction policy. The memory eviction policy includes: An execution quota is allocated from the memory quota, and the execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks. The first sub-quota is greater than the second sub-quota. During the execution of the hash connection operator, the first sub-quota and the second sub-quota are subject to the longest unused elimination management.

2. The data query method according to claim 1, characterized in that, Before executing the hash join operator using a memory eviction policy, the method further includes: The first I / O overhead of executing the hash join operator using the partition hash join strategy is evaluated, and the second I / O overhead of executing the hash join operator using the memory eviction strategy is evaluated. If the second IO overhead is less than the first IO overhead, then the hash join operator is executed using the memory eviction policy.

3. The data query method according to claim 2, characterized in that, The evaluation employs a partitioned hash join strategy to perform the first IO overhead of the hash join operator, including: Obtain the first space occupied by the small table, and obtain the second space occupied by the large table involved in the hash table; The number of partitions is determined based on the first occupied space and the memory quota, and the number of disk read / write operations is determined based on the number of partitions; Obtain the unit read IO cost and unit write IO cost of the disk, and calculate the first IO cost based on the first occupied space, the second occupied space, the number of disk read / write operations, the unit read IO cost, and the unit write IO cost.

4. The data query method according to claim 3, characterized in that, The evaluation employs the memory eviction policy to perform the second I / O overhead of the hash join operator, including: Obtain the second memory requirement of the hash table structure, the total number of records in the large table, the record length of the small table, and the selectivity of the hash join operator; The disk access probability is determined based on the first occupied space, the first memory requirement, and the second memory requirement; The read I / O cost is calculated based on the total number of records, the selectivity, the disk access probability, the unit read I / O cost, and the record length; and the write I / O cost is calculated based on the first occupied space and the unit write I / O cost. The second I / O overhead is calculated based on the read I / O overhead and the write I / O overhead.

5. The data query method according to claim 2, characterized in that, After evaluating the first I / O overhead of executing the hash join operator using the partition hash join strategy and the second I / O overhead of executing the hash join operator using the memory eviction strategy, the method further includes: If the second IO overhead is greater than the first IO overhead, then the hash join operator is executed using a partitioned hash join strategy; During the execution of the hash join operator using the partition hash join strategy, if the current physical partition meets the strategy switching conditions, then the memory eviction strategy is used to execute the hash join operation of the physical partition.

6. The data query method according to claim 5, characterized in that, The policy switching condition includes: the sum of the third memory requirement of the current partition and the fourth memory requirement of the maintenance partition is greater than the memory quota.

7. The data query method according to claim 5, characterized in that, After executing the hash join operator using the partition hash join strategy, the method further includes: If the partition hash join strategy fails to partition the physical partitions, then proceed to the step of executing the hash join operator using the memory eviction strategy.

8. A data query device, characterized in that, include: The plan generation module is used to receive the input structured query statement and convert the structured query statement into a physical execution plan; The quota management module is used to allocate a corresponding memory quota to the hash join operator if the physical execution plan includes a hash join operator; The operator execution module is used to determine the first memory requirement for the hash join operator to build the hash table. If the first memory requirement is greater than the memory quota, the hash join operator is executed using a memory eviction policy. The memory eviction policy includes: An execution quota is allocated from the memory quota, and the execution quota is divided into a first sub-quota and a second sub-quota. The first sub-quota is used to store the hash table structure and join key data corresponding to the hash table in units of data blocks. The second sub-quota is used to store the data records of the small tables involved in the hash table in units of data blocks. The first sub-quota is greater than the second sub-quota. During the execution of the hash connection operator, the first sub-quota and the second sub-quota are subject to the longest unused elimination management.

9. A computer device, characterized in that, The computer device includes a processor and a memory, the memory storing a computer program that can run on the processor, and the processor, when running the computer program, implements the data query method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the data query method as described in any one of claims 1 to 7.

Images