Columnar scan optimization method based on minmax value and predicate selectivity
By obtaining the MinMax statistics and predicate selectivity of columnar data, a pushdown predicate sequence is generated, and multi-level collaborative filtering is performed, which solves the problem of low efficiency in traditional columnar scanning methods and achieves more efficient query performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- DIGITAL YI TECH (BEIJING) CO LTD
- Filing Date
- 2026-04-16
- Publication Date
- 2026-06-19
AI Technical Summary
Traditional column-oriented scanning methods require a full table scan for any query, resulting in low efficiency.
By acquiring the MinMax statistics and predicate selectivity of columnar data, a pushdown predicate sequence is generated to perform multi-level collaborative filtering, including block-level and row-level filtering, thereby reducing disk I/O and CPU overhead.
It improves columnar storage scanning efficiency, reduces disk I/O and CPU overhead, and enhances query performance.
Smart Images
Figure CN122240623A_ABST
Abstract
Description
Technical Field
[0001] This application belongs to the field of database technology, and more specifically, relates to a columnar storage scan optimization method based on the minimum maximum (MinMax) value and predicate selectivity. Background Technology
[0002] While columnar storage implementations vary slightly across different database systems, the core idea remains the same: storing a column of data together (potentially in one or multiple files). This data shares a common characteristic: identical data type and identical storage space for each record. The scanning performance and implementation scheme are highly dependent on the characteristics of this data.
[0003] The traditional columnar storage scanning method is as follows: Step 1a: The query compilation layer parses the Structured Query Language (SQL) statement through the parser and optimizes it through the optimizer to generate the corresponding execution plan, and then sends the execution plan to the executor; Step 2a: The query execution layer drives the lower-level operators layer by layer from the top-level operator according to the execution steps of the plan, until the scan operator of the leaf node interacts with the storage to read the columnar storage file data from the disk. This mainly focuses on the conventional disk input / output (I / O) scenario; Step 3a: The storage layer scans one or more blocks from the data file of each column into memory; Step 4a: Combining the data type of the column, the page header and data packet content are parsed out, and then the values of each column are parsed out one by one and assembled into a complete tuple (a row of data) and returned to the executor; Step 5a: The execution layer receives the tuple returned by the storage layer, and the various operators in the plan begin to work, performing conditions comparison, projection, join, sorting, aggregation, etc., as a central processing unit (CPU). Unit (CPU) intensive computational operations; Step 6a: The data processed by all operators is returned to the client. However, this scanning method requires a full table scan for any query, which is relatively inefficient. Summary of the Invention
[0004] To address the shortcomings of existing technologies, the purpose of this application is to provide a columnar scan optimization method based on MinMax value and predicate selectivity, aiming to solve the problem of low efficiency caused by the traditional columnar scan method requiring a full table scan for all query statements.
[0005] To achieve the above objectives, firstly, this application provides a column-store scan optimization method based on MinMax value and predicate selectivity, including: Obtain statistical information of multiple data blocks in columnar data, wherein the statistical information includes at least MinMax statistical information reflecting the value range of each data block; Obtain the predicate conditions from the query request and determine the selectivity of each predicate condition based on a pre-built selectivity prediction model; Based on the selectivity of each predicate condition, a pushdown predicate sequence is generated; Based on the MinMax statistics and the pushdown predicate sequence, multi-level collaborative filtering is performed on the multiple data blocks to filter out data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. The multi-level collaborative filtering includes at least block-level filtering and row-level filtering. Perform query calculations on the filtered data and return the results.
[0006] This application uses a pre-built selectivity prediction model to predict the selectivity of predicate conditions in a query request, improving the accuracy of selectivity judgment. It evaluates the filtering capability of predicate conditions based on selectivity and generates a pushdown predicate sequence with the highest overall filtering utility. Block-level and row-level filtering are performed by comparing the pushdown predicate sequence with the MinMax statistics of data blocks. This filters data blocks and rows that do not meet the predicate conditions in the pushdown predicate sequence. On the one hand, this filtering is performed before data is loaded into memory, reducing disk I / O. On the other hand, filtering data rows already loaded into memory ensures efficient utilization of computing resources and reduces CPU overhead. By reducing disk I / O and CPU overhead, columnar storage scan efficiency can be improved.
[0007] According to the columnar storage scan optimization method based on MinMax value and predicate selectivity provided in this application, the step of obtaining statistical information of multiple data blocks in the columnar storage data includes: Obtain MinMax statistics at different granularities pre-maintained in the columnar data, including data block-level granularity and intra-block segmentation granularity that is finer than data block level.
[0008] This application avoids the problem of "one-size-fits-all" when the data distribution is uneven by dynamically selecting MinMax statistical information of different granularities. It can dynamically balance storage overhead and filtering accuracy, and improve flexibility.
[0009] According to the column-store scan optimization method based on MinMax value and predicate selectivity provided in this application, the method for determining the selectivity of each predicate condition based on a pre-built selectivity prediction model includes: The pre-built selection rate prediction model is constructed by taking the predicate conditions, the historical data distribution characteristics of the columnar data (skewness, kurtosis, high frequency value), and the query request context as input, and then obtaining the selection rate of each predicate condition output by the selection rate prediction model.
[0010] This application utilizes a machine learning model to predict selectivity by combining the predicate itself, data distribution characteristics, and query context. This significantly improves the accuracy of selectivity estimation and provides the optimizer with a more reliable decision-making basis. The optimizer can evaluate the benefits of pushing down multiple predicate combinations and select the optimal set of predicates to push down. This breaks through the limitation of traditional solutions that only push down a single predicate with a high selectivity, and is especially beneficial for complex and compound condition queries.
[0011] According to the column-store scan optimization method based on MinMax value and predicate selectivity provided in this application, the multi-level collaborative filtering of the multiple data blocks based on the MinMax statistics and the pushdown predicate sequence includes: The MinMax statistics at the data block level of each data block are compared with the MinMax values of each predicate condition in the pushdown predicate sequence, and the data blocks that do not meet the predicate conditions are filtered for the first time. The MinMax statistics of the segmented granularity within the data blocks that passed the first filtering are compared with the MinMax values of each predicate condition in the pushdown predicate sequence. Data blocks that do not meet any of the predicate conditions are filtered a second time. For data units that pass the second filter, they are filtered row by row according to the order of the predicate conditions in the pushdown predicate sequence. Data rows that do not meet any of the predicate conditions are filtered a third time.
[0012] This application performs a three-level filtering process on columnar data, from coarse to fine. The first level quickly eliminates a large number of irrelevant data blocks. The second level performs fine-grained positioning within blocks, avoiding the loading of the entire data block. The third level performs final and precise filtering at the row level, avoiding materializing all columns before confirming that a row does not meet the conditions. This greatly reduces CPU and memory overhead. This structure minimizes the amount of data that needs to be processed at each level and improves scanning efficiency.
[0013] According to the column-store scan optimization method based on MinMax value and predicate selectivity provided in this application, the method further includes: When performing multi-level collaborative filtering on the multiple data blocks, the pre-analyzed and stored inter-column statistical correlation information is used to collaboratively judge the predicate conditions involving multiple column composite conditions. When it is determined from the inter-column statistical correlation information that a data block satisfies the predicate condition of one column but not the predicate condition of the associated column, the data block is directly filtered.
[0014] This application utilizes statistical correlation information between columns. Even if the MinMax of a single column cannot filter out a data block on its own, it can be skipped by combining the information of the related columns. This greatly improves the filtering capability for complex query conditions with multiple columns. Furthermore, correlation analysis provides an additional filtering dimension, which can filter out data blocks that cannot be excluded by the MinMax of a single column alone, thereby further reducing disk I / O.
[0015] According to the column-store scan optimization method based on MinMax value and predicate selectivity provided in this application, the method further includes: The granularity of the MinMax statistics is dynamically adjusted based on the resource usage of the database system.
[0016] This application dynamically adjusts the filtering strategy based on real-time load, ensuring that the optimization action itself does not exacerbate the system burden when resources are scarce, thereby achieving optimal overall system performance, keeping query performance stable under different workloads, adapting to changing production environments, and improving robustness.
[0017] Secondly, this application provides a column-store scan optimization device based on MinMax value and predicate selectivity, comprising: The first acquisition module is used to acquire statistical information of multiple data blocks in the columnar data, wherein the statistical information includes at least MinMax statistical information reflecting the value range of each data block; The second acquisition module is used to acquire the predicate conditions in the query request and determine the selection rate of each predicate condition based on the pre-built selection rate prediction model; The generation module is used to generate a pushdown predicate sequence based on the selectivity of each predicate condition; The filtering module is used to perform multi-level collaborative filtering on the multiple data blocks based on the MinMax statistical information and the pushdown predicate sequence, so as to filter data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. The multi-level collaborative filtering includes at least block-level filtering and row-level filtering. The query module is used to perform query calculations on the filtered data and return the results.
[0018] Thirdly, this application provides an electronic device, comprising: at least one memory for storing a program; and at least one processor for executing the program stored in the memory. When the program stored in the memory is executed, the processor is configured to execute the columnar scan optimization method based on MinMax value and predicate selectivity described in the first aspect or any possible implementation of the first aspect.
[0019] Fourthly, this application provides a computer-readable storage medium storing a computer program that, when run on a processor, causes the processor to perform the columnar scan optimization method based on MinMax value and predicate selectivity as described in the first aspect or any possible implementation of the first aspect.
[0020] Fifthly, this application provides a computer program product that, when run on a processor, causes the processor to execute the columnar scan optimization method based on MinMax value and predicate selectivity described in the first aspect or any possible implementation of the first aspect.
[0021] It is understood that the beneficial effects of the second to fifth aspects mentioned above can be found in the relevant descriptions in the first aspect mentioned above, and will not be repeated here.
[0022] Overall, the technical solutions conceived in this application have the following beneficial effects compared with the prior art: This application uses a pre-built selectivity prediction model to predict the selectivity of predicate conditions in a query request, improving the accuracy of selectivity judgment. It evaluates the filtering capability of predicate conditions based on selectivity and generates a pushdown predicate sequence with the highest overall filtering utility. Block-level and row-level filtering are performed by comparing the pushdown predicate sequence with the MinMax statistics of data blocks. This filters data blocks and rows that do not meet the predicate conditions in the pushdown predicate sequence. On the one hand, this filtering is performed before data is loaded into memory, reducing disk I / O. On the other hand, filtering data rows already loaded into memory ensures efficient utilization of computing resources and reduces CPU overhead. By reducing disk I / O and CPU overhead, columnar storage scan efficiency can be improved. Attached Figure Description
[0023] To more clearly illustrate the technical solutions in this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.
[0024] Figure 1 This is a flowchart illustrating the columnar storage scan optimization method based on MinMax value and predicate selectivity provided in the embodiments of this application; Figure 2 This is a schematic diagram of the columnar storage scan optimization device based on MinMax value and predicate selectivity provided in the embodiments of this application; Figure 3 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application. Detailed Implementation
[0025] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.
[0026] In this article, the term "and / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, or B existing alone. The symbol " / " in this article indicates that the related objects are in an "or" relationship; for example, A / B means A or B.
[0027] In the embodiments of this application, the terms "exemplary" or "for example" are used to indicate that something is an example, illustration, or description. Any embodiment or design that is described as "exemplary" or "for example" in the embodiments of this application should not be construed as being more preferred or advantageous than other embodiments or design. Specifically, the use of the terms "exemplary" or "for example" is intended to present the relevant concepts in a specific manner.
[0028] In the description of the embodiments of this application, unless otherwise stated, "multiple" means two or more, for example, multiple processing units means two or more processing units, multiple elements means two or more elements, etc.
[0029] Next, combined Figure 1 The column-store scan optimization method based on MinMax value and predicate selectivity provided in the embodiments of this application is introduced.
[0030] Figure 1 This is a flowchart illustrating the columnar storage scan optimization method based on MinMax value and predicate selectivity provided in this application embodiment, as follows: Figure 1 As shown, the method includes the following steps: Step S1: Obtain statistical information of multiple data blocks in the columnar data. The statistical information shall include at least the MinMax statistical information reflecting the value range of each data block. Database storage systems typically use a metadata table to record the physical offset of data blocks within a file for columnar storage, enabling random access to the file. However, this information is only used for calculating the physical offset of the file and does not have the ability to filter data blocks. Therefore, this application modifies the content stored in the metadata table of columnar storage. During the data import process, in addition to retaining the block offset management information, it is also necessary to retain the MinMax values of the data blocks. These values are calculated and stored on a block-by-block basis, just like the physical offset.
[0031] The reason why traditional scanning methods have poor performance is mainly because all data blocks involve disk I / O and parsing processes. The core idea of this application is to extract a series of predicates from the query conditions of the SQL statement that can filter out the most data blocks, push them down to the storage layer and compare them with the MinMax statistics of data blocks in MetaData, and filter out data blocks and data rows that do not meet the conditions, thereby reducing a large amount of disk I / O and central processing unit (CPU) computation.
[0032] First, obtain the MinMax statistics of multiple data blocks in the columnar data, which reflect the value range of each data block.
[0033] Step S2: Obtain the predicate conditions in the query request, and determine the selection rate of each predicate condition based on the pre-built selection rate prediction model; In the statistical information model of modern relational databases, it is appropriate to select the predicate conditions to push down based on the selectivity rate. The smaller the selectivity rate, the more data blocks are filtered out by the predicate conditions.
[0034] Optionally, a selection rate prediction model, such as a gradient boosting decision tree, can be constructed to predict the selection rate based on features with predicate conditions.
[0035] Step S3: Generate a pushdown predicate sequence based on the selection rate of each predicate condition; Optionally, a confidence score can be attached to the selection rate of each prediction. This score can be calculated based on factors such as the model's historical prediction accuracy, the coverage of current input features and training samples, and the confidence score is used to assess the risk of the push-down decision.
[0036] Optionally, based on the selectivity and confidence of each predicate condition predicted by the model, the potential benefits and costs of pushing down multiple predicate combinations can be evaluated, and a set of predicate conditions that maximizes the overall filtering utility can be selected, i.e., the predicate sequence is pushed down to the storage layer.
[0037] Step S4: Based on MinMax statistics and the pushdown predicate sequence, perform multi-level collaborative filtering on multiple data blocks to filter out data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. Multi-level collaborative filtering includes at least block-level filtering and row-level filtering. The pushdown predicate sequence is pushed down to the storage layer and compared with the MinMax statistics of the data blocks to filter out data blocks and data rows that definitely do not meet the conditions.
[0038] For example, if the predicate condition is "where c1 between 10 and 100", then if the maximum value of the data block is less than 100 and the minimum value is greater than 10, then the data block satisfies the predicate condition.
[0039] Step S5: Perform query calculations on the filtered data and return the results.
[0040] The column-store scan optimization method based on MinMax value and predicate selectivity provided in this application predicts the selectivity of predicate conditions in the query request through a pre-built selectivity prediction model, thereby improving the accuracy of selectivity judgment. The filtering capability of predicate conditions is evaluated by the selectivity, and a pushdown predicate sequence with the highest overall filtering utility is generated. Block-level and row-level filtering are performed by comparing the pushdown predicate sequence with the MinMax statistics of the data blocks. Data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence are filtered. On the one hand, the data is filtered before it is loaded into memory, reducing disk I / O. On the other hand, the data rows already loaded into memory are filtered to ensure that computing resources are used efficiently and CPU overhead is reduced. By reducing disk I / O and CPU overhead, the column-store scan efficiency can be improved.
[0041] In some embodiments, step S1 specifically includes: Step S11: Obtain the MinMax statistics of different granularities that are pre-maintained in the columnar data. The different granularities include data block level granularity and intra-block segmentation granularity that is finer than data block level.
[0042] Optionally, the segmentation granularity within a block can be divided using one of the following two methods: Method 1, dividing the data block into multiple row groups according to a fixed number of rows, with each row group serving as a segment; Method 2, dividing the data block into multiple data segments according to a fixed byte size, with each data segment serving as a segment. During data import or data update, the partitioning method is automatically selected based on the value distribution characteristics of the column: if the value distribution of the column is relatively uniform, Method 1 or Method 2 is used; if the value distribution of the column has a significant data skew, a dynamic partitioning method based on equal-depth bucketing is used to ensure that the number of data rows in each segment is approximately equal.
[0043] Optionally, traditional schemes store a fixed MinMax value for each data block. This application innovatively proposes a dynamic multi-granularity MinMax statistical mechanism, which includes the following aspects: Multi-granularity statistical structure: In addition to the data block-level MinMax, the metadata table also supports storing coarser-grained and finer-grained MinMax values, such as segments within the block. Statistical information of different granularities forms a hierarchical index in the form of nested arrays.
[0044] Specifically, an index entry is maintained for each data block in the metadata table. This entry includes: the file offset address of the data block, the block-level MinMax value, and a pointer to the MinMax array of segments within the block. The MinMax array of segments within the block stores the first line offset address and the corresponding MinMax value of each segment in segment order. When filtering data blocks, the block-level MinMax value is first compared with the predicate condition. If the condition is met, the pointer is used to locate the MinMax array of segments within the block. The array is traversed and the MinMax value of each segment is compared with the predicate condition, thereby locating the specific segment that may meet the condition and its starting position in the file.
[0045] Adaptive granularity selection algorithm: Based on data feature update frequency, value range span, order, and query history pattern, it dynamically selects and maintains the optimal combination of statistical granularity for each column. For columns with high-frequency updates or concentrated value ranges, finer granularity is used to improve filtering accuracy; for stable columns or columns with dispersed value ranges, coarser granularity is used to reduce metadata storage and comparison overhead.
[0046] Specifically, the system maintains a utility scoring function for each column to evaluate the filtering efficiency of the current statistical granularity combination; the utility scoring function is calculated based on the following parameters: Data characteristics include the column's update frequency (number of updates per unit time), value range span (difference between maximum and minimum values), and value range concentration (measured by the ratio of standard deviation to mean; when the ratio is less than a preset concentration threshold, it is considered 'value range concentrated'). Query Benefits: Statistics show the ratio of the number of data blocks successfully skipped using the current granularity of MinMax statistics to the total number of scanned blocks over a period of time; Storage overhead: The metadata storage space occupied by statistical information at different granularities; The system periodically calculates the utility score of the current granularity combination. When the score is lower than a preset threshold, it triggers granularity adjustment: if the column is updated frequently or the query benefit is low, it is merged into a coarser granularity to reduce maintenance overhead; if the column value range is concentrated and the query skipping rate is high, it is split into a finer granularity to improve filtering accuracy.
[0047] Dynamic adjustment mechanism: The system monitors the actual effect of statistical information at each granularity in query filtering, and automatically triggers the merging or splitting of statistical granularity based on feedback to achieve a balance between statistical accuracy and efficiency.
[0048] Specifically, the system monitors real-time feedback during query execution, including the actual skip rate of each segment before row-level filtering. When the actual skip rate of a segment is lower than a preset skip rate threshold for N consecutive query cycles, the statistical information of that segment is deemed invalid, and a merging operation is automatically triggered to merge it with the MinMax statistical information of the adjacent segments. The new MinMax value after merging is the smaller of the Min values and the larger of the Max values of the two segments. The original segment entry is deleted and the new merged entry is inserted into the metadata table. When the overall skip rate of a data block is higher than a preset benefit threshold, and the value range distribution of each segment within the data block is significantly different (i.e., the MinMax overlap between segments is lower than a preset overlap threshold), a splitting operation is automatically triggered to re-divide the data block into finer-grained segments and recalculate the MinMax value of each new segment. The splitting operation is executed asynchronously in the background and does not affect ongoing queries. When recalculating the MinMax value, the system scans the data rows of the corresponding segment to obtain the actual minimum and maximum values of all rows within that segment.
[0049] Among them, the finer-grained MinMax refers to a range smaller than the range of the data block-level MinMax.
[0050] In some embodiments, step S2 specifically includes: The pre-built selection rate prediction model is constructed by taking the predicate conditions, the historical data distribution characteristics of the columnar data (skewness, kurtosis, high frequency value), and the query request context as input, and then obtaining the selection rate of each predicate condition output by the selection rate prediction model.
[0051] Optionally, a lightweight machine learning model, such as a gradient boosting decision tree, is first built. The input features of the model include: the predicate itself, the historical data distribution features of the column (skewness, kurtosis, high frequency value), and the context of the query conditions. The output is the selectivity of each predicate condition.
[0052] Optionally, the model is trained using historical query execution feedback to predict the actual filtering selectivity of the predicate.
[0053] Optionally, a confidence score is attached to the selection rate of each prediction. This score is calculated based on factors such as the model's historical prediction accuracy, the coverage of current input features and training samples, and the confidence score is used to assess the risk of the push-down decision.
[0054] In some embodiments, step S4 specifically includes: The MinMax statistics at the data block level and the MinMax values of each predicate condition in the pushdown predicate sequence are compared separately, and the data blocks that do not meet the predicate conditions are filtered out for the first time. The MinMax statistics of the segmented granularity within the data blocks that passed the first filter and the MinMax values of each predicate condition in the pushdown predicate sequence are compared respectively. Data blocks that do not meet any of the predicate conditions are filtered a second time. For data units that pass the second filter, they are filtered row by row according to the order of the predicate conditions in the pushdown predicate sequence. Data rows that do not meet any of the predicate conditions are filtered a third time.
[0055] Optionally, this application provides a three-layer filtration system: The first layer, coarse-grained block filtering: utilizes multi-granularity MinMax information to quickly filter data blocks that could never satisfy any pushdown predicate conditions.
[0056] The second layer is fine-grained intra-block filtering: For data blocks that have passed the first filtering, the more granular MinMax statistics within the block are read for precise judgment, filtering out only partially satisfied intra-block regions, which may only require reading part of the block content.
[0057] The third layer, row-level early materialization filtering: For data units that have passed the first two levels of filtering, the relevant column values are materialized early according to the order of the predicates pushed down by the optimizer. Filtering is performed row by row. Only when a row meets all the conditions of the pushed-down predicates will the reading and materialization of the values of other columns in that row be triggered.
[0058] Optionally, the results of each filtering step, such as the percentage of skipped blocks / rows and the resources consumed, can be collected to continuously optimize the granularity of MinMax statistics and the selection rate prediction model.
[0059] In some embodiments, the method further includes: When performing multi-level collaborative filtering on multiple data blocks, the pre-analyzed and stored inter-column statistical correlation information is used to collaboratively judge the predicate conditions involving multiple composite conditions. When it is determined from the inter-column statistical correlation information that a data block satisfies the predicate condition of one column but not the predicate condition of the associated column, the data block is directly filtered.
[0060] Optionally, to handle multi-column composite condition queries, a collaborative multi-level filtering architecture can be built on top of the MinMax-based filtering: the system analyzes and stores statistical correlation information between columns, such as block-level correlation coefficients or joint MinMax summaries. When the push-predicate involves multiple columns, the storage layer can use the correlation information for secondary filtering.
[0061] For example, if it is known that the MinMax of a certain block in column A meets the condition, but the association information indicates that the value range of column B in that block can never match another predicate, the block can be skipped directly, even if the MinMax of column B alone fails to exclude it.
[0062] In some embodiments, the method further includes: The granularity of MinMax statistics is dynamically adjusted based on the resource usage of the database system.
[0063] Optionally, to enable the filtering system to adapt to dynamically changing system loads, the system monitors resource indicators such as I / O bandwidth utilization, CPU load, and memory pressure in real time.
[0064] Optionally, a resource consumption model can be established to quantify the savings and consumption of I / O and CPU by different filtering levels (such as using only the first level, enabling the second level, and using all three levels).
[0065] Optionally, when the system is under I / O bottleneck, it tends to enable more aggressive multi-granularity MinMax filtering and correlation filtering, that is, to increase some CPU computation to reduce I / O; when under CPU bottleneck, it may relax the granularity of block filtering, reduce complex correlation analysis, and avoid filtering itself becoming a CPU burden.
[0066] Optionally, hot columns or data regions that are frequently used for filtering can be identified, and their multi-granularity MinMax statistics and correlation information can be kept resident in memory.
[0067] Optionally, for repeated identical or similar query predicate conditions, cache the list of data block IDs obtained after first and second level filtering to avoid repeated MinMax comparison calculations.
[0068] Optionally, when the underlying data is updated, the hot statistics information cache in memory is updated incrementally to ensure the timeliness of filtering decisions, while persistent metadata is updated asynchronously through background tasks.
[0069] This application makes block-level and row-level filtering decisions more accurate through dynamic multi-granularity MinMax statistics and intelligent selectivity prediction, fundamentally improving the effectiveness of filtering and making it applicable to a wider range of data distributions and query patterns.
[0070] This application introduces cross-block correlation analysis and a three-layer filtering pipeline to achieve coarse-to-fine, multi-dimensional collaborative filtering, which significantly improves the ability to handle complex and compound condition queries and further squeezes out the optimization potential of I / O and CPU.
[0071] This application transforms the filtering mechanism from a static query optimization component into an intelligent subsystem capable of self-adjusting based on the real-time status of the system through resource-aware dynamic strategies and deep caching integration, thereby achieving sustained high performance under varying loads.
[0072] This application establishes a complete self-optimization closed loop, from statistical information management and decision-making to execution filtering and effect feedback, enabling the system to continuously learn and adjust as it runs, and possess continuous evolution capabilities.
[0073] The column-store scan optimization apparatus based on MinMax value and predicate selectivity provided in this application is described below. The column-store scan optimization apparatus based on MinMax value and predicate selectivity described below can be referred to in correspondence with the column-store scan optimization method based on MinMax value and predicate selectivity described above.
[0074] Figure 2 This is a schematic diagram of a columnar storage scan optimization device based on MinMax value and predicate selectivity provided in an embodiment of this application, as shown below. Figure 2 As shown, the device 200 includes: The first acquisition module 210 is used to acquire statistical information of multiple data blocks in the columnar data. The statistical information includes at least the MinMax statistical information reflecting the value range of each data block. The second acquisition module 220 is used to acquire the predicate conditions in the query request and determine the selection rate of each predicate condition based on the pre-built selection rate prediction model; The generation module 230 is used to generate a pushdown predicate sequence based on the selectivity of each predicate condition; The filtering module 240 is used to perform multi-level collaborative filtering on multiple data blocks based on MinMax statistics and pushdown predicate sequence to filter data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. The multi-level collaborative filtering includes at least block-level filtering and row-level filtering. The query module 250 is used to perform query calculations on the filtered data and return the results.
[0075] It should be understood that the above-described device is used to execute the methods in the above embodiments. The implementation principle and technical effect of the corresponding program modules in the device are similar to those described in the above methods. The working process of the device can be referred to the corresponding process in the above methods, and will not be repeated here.
[0076] Based on the methods in the above embodiments, Figure 3 An example is a schematic diagram of the physical structure of an electronic device, such as... Figure 3As shown in the illustration, this application provides an electronic device that may include a processor 310, a communication interface 320, a memory 330, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 can call logical instructions stored in the memory 330 to execute the columnar storage scan optimization method based on MinMax values and predicate selectivity described in the above embodiment.
[0077] Furthermore, the logical instructions in the aforementioned memory 330 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the columnar storage scan optimization method based on MinMax value and predicate selectivity described in the various embodiments of this application.
[0078] Based on the methods in the above embodiments, this application provides a computer-readable storage medium storing a computer program. When the computer program runs on a processor, it causes the processor to execute the columnar storage scan optimization method based on MinMax value and predicate selectivity in the above embodiments.
[0079] Based on the methods in the above embodiments, this application provides a computer program product that, when running on a processor, causes the processor to execute the columnar storage scan optimization method based on MinMax value and predicate selectivity in the above embodiments.
[0080] It is understood that the processor in the embodiments of this application can be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. A general-purpose processor can be a microprocessor or any conventional processor.
[0081] The method steps in this application embodiment can be implemented in hardware or by a processor executing software instructions. The software instructions can consist of corresponding software modules, which can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, portable hard disks, CD-ROMs, or any other form of storage medium known in the art. An exemplary storage medium is coupled to the processor, enabling the processor to read information from and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can reside in an ASIC.
[0082] In the above embodiments, implementation can be achieved entirely or partially through software, hardware, firmware, or any combination thereof. When implemented using software, it can be implemented entirely or partially as a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of this application are generated. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted through the computer-readable storage medium. The computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via wired (e.g., coaxial cable, fiber optic, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) means. The computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server or data center that integrates one or more available media. The available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid-state disk (SSD)).
[0083] It is understood that the various numerical designations used in the embodiments of this application are merely for the convenience of description and are not intended to limit the scope of the embodiments of this application.
[0084] Those skilled in the art will readily understand that the above description is merely a preferred embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this application should be included within the scope of protection of this application.
Claims
1. A column-store scan optimization method based on MinMax value and predicate selectivity, characterized in that, include: Obtain statistical information of multiple data blocks in columnar data, wherein the statistical information includes at least MinMax statistical information reflecting the value range of each data block; Obtain the predicate conditions from the query request and determine the selectivity of each predicate condition based on a pre-built selectivity prediction model; Based on the selectivity of each predicate condition, a pushdown predicate sequence is generated; Based on the MinMax statistics and the pushdown predicate sequence, multi-level collaborative filtering is performed on the multiple data blocks to filter out data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. The multi-level collaborative filtering includes at least block-level filtering and row-level filtering. Perform query calculations on the filtered data and return the results.
2. The column-store scan optimization method based on MinMax value and predicate selectivity according to claim 1, characterized in that, The step of obtaining statistical information from multiple data blocks in the columnar storage data includes: Obtain MinMax statistics at different granularities pre-maintained in the columnar data, including data block-level granularity and intra-block segmentation granularity that is finer than data block level.
3. The column-store scan optimization method based on MinMax value and predicate selectivity according to claim 1, characterized in that, The selection rate prediction model based on the pre-built selection rate determines the selection rate of each predicate condition, including: The pre-built selection rate prediction model is constructed by taking the predicate conditions, the historical data distribution characteristics of the columnar data (skewness, kurtosis, high frequency value), and the query request context as input, and then obtaining the selection rate of each predicate condition output by the selection rate prediction model.
4. The column-store scan optimization method based on MinMax value and predicate selectivity according to claim 2, characterized in that, The multi-level collaborative filtering of the multiple data blocks based on the MinMax statistics and the pushdown predicate sequence includes: The MinMax statistics at the data block level of each data block are compared with the MinMax values of each predicate condition in the pushdown predicate sequence, and the data blocks that do not meet the predicate conditions are filtered for the first time. The MinMax statistics of the segmented granularity within the data blocks that passed the first filtering are compared with the MinMax values of each predicate condition in the pushdown predicate sequence. Data blocks that do not meet any of the predicate conditions are filtered a second time. For data units that pass the second filter, they are filtered row by row according to the order of the predicate conditions in the pushdown predicate sequence. Data rows that do not meet any of the predicate conditions are filtered a third time.
5. The column-store scan optimization method based on MinMax value and predicate selectivity according to claim 4, characterized in that, The method further includes: When performing multi-level collaborative filtering on the multiple data blocks, the pre-analyzed and stored inter-column statistical correlation information is used to collaboratively judge the predicate conditions involving multiple column composite conditions. When it is determined from the inter-column statistical correlation information that a data block satisfies the predicate condition of one column but not the predicate condition of the associated column, the data block is directly filtered.
6. The column-store scan optimization method based on MinMax value and predicate selectivity according to claim 2, characterized in that, The method further includes: The granularity of the MinMax statistics is dynamically adjusted based on the resource usage of the database system.
7. A columnar storage scan optimization device based on MinMax value and predicate selectivity, characterized in that, include: The first acquisition module is used to acquire statistical information of multiple data blocks in the columnar data, wherein the statistical information includes at least MinMax statistical information reflecting the value range of each data block; The second acquisition module is used to acquire the predicate conditions in the query request and determine the selection rate of each predicate condition based on the pre-built selection rate prediction model; The generation module is used to generate a pushdown predicate sequence based on the selectivity of each predicate condition; The filtering module is used to perform multi-level collaborative filtering on the multiple data blocks based on the MinMax statistical information and the pushdown predicate sequence, so as to filter data blocks and data rows that do not meet the predicate conditions in the pushdown predicate sequence. The multi-level collaborative filtering includes at least block-level filtering and row-level filtering. The query module is used to perform query calculations on the filtered data and return the results.
8. An electronic device, characterized in that, include: At least one memory for storing computer programs; At least one processor is configured to execute a program stored in the memory, wherein when the program stored in the memory is executed, the processor is configured to execute the columnar scan optimization method based on the MinMax value and predicate selectivity as described in any one of claims 1-6.
9. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is run on the processor, the processor performs the columnar scan optimization method based on the MinMax value and predicate selectivity as described in any one of claims 1-6.
10. A computer program product, characterized in that, When the computer program product is run on a processor, the processor performs the columnar scan optimization method based on the MinMax value and predicate selectivity as described in any one of claims 1-6.