Database sampling method and device, electronic equipment and program product

Abstract

The invention discloses a database sampling method and device, electronic equipment and a program product, and relates to the field of financial science and technology or other related technical fields, and the method comprises the steps: collecting system resource states and hotspot distribution change data based on a plurality of system monitoring modules, and obtaining a database sampling result based on a gradient promotion decision tree model; reasoning the system resource state and the hot spot distribution change data, outputting a sampling preference score which is used for determining a sampling opportunity and querying a logic data hot spot distribution model to obtain a current hot spot region, and generating a directional sampling plan based on the sampling opportunity and the current hot spot region by using an adaptive sampler; the directional sampling plan is executed, sample data obtained through sampling are output to a query optimizer, and the query optimizer is used for selecting an optimal query execution plan based on the sample data. According to the invention, the technical problem of low sampling accuracy caused by unreasonable sampling resource allocation of cold and hot areas in a sampling-based cardinal number estimation strategy in related technologies is solved.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of financial technology or other related fields, in particular, to a database sampling method and device, an electronic device and a program product. BACKGROUND

[0002] Under the background of the current information industry in-depth development, as the core infrastructure of financial, e-commerce and other key businesses, the query performance of the database system directly affects the response speed of the business and the user experience. The query execution process of the relational database involves query syntax analysis, logical query optimization, execution plan generation and other links, among which the query optimizer plays a core role. It evaluates and selects the optimal execution plan through cost-based optimization technology (CBO).

[0003] Among them, the cost-based optimization technology contains three core components: cardinality estimation module, cost evaluation model and plan enumeration module. The cardinality estimation module is responsible for predicting the number of rows of the intermediate result set generated by the query operation (such as table scanning, connection, filtering, etc.), which is the cornerstone of the entire optimization technology; the cost evaluation model calculates the resource consumption cost of different execution strategies based on the cardinality estimation result; the plan enumeration module selects the most economical execution plan according to the cost evaluation result.

[0004] In order to balance the accuracy of cardinality estimation and the resource overhead of the sampling process, the statistical information collection method based on sampling is widely used. Through the periodic or high data update amount triggered method, the database table or index is sampled to collect statistical information. The sampling algorithm includes simple random sampling, system sampling (sampling by page) or water pond sampling, which aims to infer the cardinality and other statistical characteristics of the entire data set by analyzing a smaller data subset.

[0005] However, the sampling-based cardinality estimation strategy in the related art has inherent defects. First, the contradiction between static sampling and dynamic evolution of data: the static sampling strategy cannot monitor the change of data distribution in real time, especially in high-frequency data update scenarios (such as real-time transaction data, log writing), the outdated sampling data will lead to distortion of cardinality estimation, and then affect the generation of execution plan. Second, the sampling strategy lacks query load awareness: the sampling mechanism tends to be data-driven rather than query load-driven, which means that resource allocation does not consider the frequency and type of queries, resulting in insufficient accuracy of statistical information in hot data areas, and redundant sampling resources in cold data areas, which cannot maximize the effectiveness of sampling.

[0006] In view of the above problems, no effective solution has been proposed so far. SUMMARY

[0007] Embodiments of the present application provide a database sampling method and device, electronic equipment and program product, to at least solve the technical problem of unreasonable sampling resource allocation in hot and cold regions based on the sampling-based cardinality estimation strategy in the related art, resulting in reduced sampling accuracy.

[0008] To achieve the above object, according to an aspect of the present application, a database sampling method is provided, comprising: collecting system resource state and hotspot distribution change data based on a plurality of system monitoring modules; inferring the system resource state and the hotspot distribution change data based on a gradient boosting decision tree model, and outputting a sampling preference score, wherein the sampling preference score is used to determine a sampling opportunity; querying a logical data hotspot distribution model to obtain a current hotspot region, wherein the current hotspot region refers to a data region with a higher access frequency than a preset access frequency threshold in a current time period, and the access frequency is determined by element analysis of query requests initiated by all query loads; generating a directional sampling plan based on the sampling opportunity and the current hotspot region using an adaptive sampler; executing the directional sampling plan, and outputting sample data obtained by sampling to a query optimizer, wherein the query optimizer is used to select an optimal query execution plan based on the sample data.

[0009] Optionally, the logical data hotspot distribution model comprises: a data storage layer that stores access statistical information using a hash table, wherein the hash table includes a data entity identifier corresponding to a data region, metadata statistical values and a metadata list, the data entity identifier is used as a key, and the metadata statistical values and the metadata list are used as values; a statistical calculation module that calculates an access frequency and a historical estimation error rate of the data entity identifier; a hotspot recognition engine that recognizes a hotspot region based on the access frequency and the historical estimation error rate of the data entity identifier; and a hotspot region change rate statistical module that calculates a hotspot region change rate based on dimension data of a plurality of change dimensions associated with the hotspot region in a scheduling monitoring period, wherein the plurality of change dimensions at least include an added dimension, a disappeared dimension and an attribute change dimension.

[0010] Optionally, the step of calculating the access frequency and the historical estimation error rate of the data entity identifier comprises: obtaining the number of queries containing the data entity identifier in a first predetermined time length and a first time weight parameter corresponding to the first predetermined time length, wherein the first predetermined time length is a time length within a first specified time range from a current time point; obtaining the number of queries containing the data entity identifier in a second predetermined time length and a second time weight parameter corresponding to the second predetermined time length, wherein the second predetermined time length is a time length in a historical process within a second specified time range from the start time point of the first predetermined time length; obtaining the total number of accesses containing all data entity identifiers in the first predetermined time length and the total number of accesses containing all data entity identifiers in the second predetermined time length; calculating the access frequency of the data entity identifier based on the number of queries containing the data entity identifier in the first predetermined time length and the first time weight parameter corresponding to the first predetermined time length, the number of queries containing the data entity identifier in the second predetermined time length and the second time weight parameter corresponding to the second predetermined time length, the total number of accesses containing all data entity identifiers in the first predetermined time length and the total number of accesses containing all data entity identifiers in the second predetermined time length; obtaining the single-time relative error rate of the i-th query in the first predetermined time length and the single-time relative error rate of the j-th query in the second predetermined time length, wherein i and j are positive integers; and calculating the historical estimation error rate of the data entity identifier based on the number of queries containing the data entity identifier in the first predetermined time length and the first time weight parameter corresponding to the first predetermined time length, the number of queries containing the data entity identifier in the second predetermined time length and the second time weight parameter corresponding to the second predetermined time length, the single-time relative error rate of the i-th query in the first predetermined time length and the single-time relative error rate of the j-th query in the second predetermined time length.

[0011] Optionally, the step of calculating the hotspot area change rate based on the dimension data of the multiple change dimensions associated with the hotspot area in the scheduling monitoring period comprises: obtaining the number of hotspot areas newly added to the hotspot area list in a statistical period in the newly added dimension, to obtain the number of newly added areas, wherein the hotspot area list contains all hotspot areas in the current time period; obtaining the number of hotspot areas that exit from the hotspot area list in the statistical period in the disappeared dimension, to obtain the number of exited areas; obtaining the number of hotspot areas that have been in the hotspot area list but have changed the core attributes of the area in the statistical period in the attribute change dimension, to obtain the number of attribute change areas; and calculating the hotspot area change rate based on the number of newly added areas and the weight parameter corresponding to the newly added dimension, the number of exited areas and the weight parameter corresponding to the disappeared dimension, and the number of attribute change areas and the weight parameter corresponding to the attribute change dimension.

[0012] Optionally, the logic data hotspot distribution model further comprises, at runtime: receiving a query syntax tree core element output by a database query collector, and parsing to obtain a data entity identifier and an access type; in a case where the data entity identifier is detected to exist in the hash table, updating an access frequency of the data entity identifier using a time decay algorithm; in a case where the data entity identifier is detected to not exist in the hash table, adding a key-value pair associated with the data entity identifier in the hash table, and setting an initial access frequency of the data entity identifier.

[0013] Optionally, the step of inferring the system resource state and the hotspot distribution change data based on a gradient boosting decision tree model to output a sampling preference score comprises: converting data in the system resource state and the hotspot distribution change data into a feature vector, wherein the data types include numerical features and categorical features, the numerical features include at least one of the following: CPU usage, memory usage, disk input / output (IO) utilization, network IO throughput, and hotspot area change rate, and the categorical features include at least one of the following: file descriptor usage state and hotspot distribution change level; performing normalization processing on the feature vector of the numerical features, and performing one-hot encoding processing on the feature vector of the categorical features; inputting the processed feature vector into the gradient boosting decision tree model to output the sampling preference score.

[0014] Optionally, the step of generating a directional sampling plan based on the sampling time and the current hotspot area comprises: obtaining a historical estimated error rate and an access frequency of a data entity identifier corresponding to the current hotspot area, which are pre-recorded in a hash table in the logic data hotspot distribution model; assigning a first sampling rate to the current hotspot area based on a region weight of the current hotspot area, the historical estimated error rate, and the access frequency of the data entity identifier corresponding to the current hotspot area; assigning a second sampling rate to a non-hotspot area other than the current hotspot area in the hash table according to a historical estimated error rate of a data entity identifier corresponding to the non-hotspot area; and generating the directional sampling plan based on the first sampling rate of the current hotspot area, the second sampling rate of the non-hotspot area, and the sampling time.

[0015] Optionally, the step of executing the directional sampling plan comprises: executing the directional sampling plan, linking a database storage layer; in the database storage layer, randomly extracting a data page, and filtering out row data belonging to the current hotspot area from the data page to obtain sample data, wherein the sample data is stored using a least recently used cache strategy.

[0016] Optionally, after the directional sampling plan is executed, the method further comprises: separately generating a subsampling plan for a column combination of the multiple-column joint query, wherein a sample quantity of each column combination in the subsampling plan is greater than a preset sample quantity threshold.

[0017] According to another aspect of the embodiments of the present application, a database sampling device is further provided, comprising: a data collection unit configured to collect system resource state and hotspot distribution change data based on a plurality of system monitoring modules; a data inference unit configured to infer the system resource state and the hotspot distribution change data based on a gradient boosting decision tree model, and output a sampling preference score, wherein the sampling preference score is used to determine a sampling time; a hotspot region query unit configured to query a logical data hotspot distribution model to obtain a current hotspot region, wherein the current hotspot region refers to a data region with a higher access frequency than a preset access frequency threshold in a current time period, and the access frequency is determined by element analysis on query requests initiated by all query loads; a sampling plan generation unit configured to generate a directional sampling plan based on the sampling time and the current hotspot region by using an adaptive sampler; and a sampling plan execution unit configured to execute the directional sampling plan and output sample data obtained by sampling to a query optimizer, wherein the query optimizer is configured to select an optimal query execution plan based on the sample data.

[0018] According to another aspect of the embodiments of the present application, a computer readable storage medium is further provided, comprising a stored computer program, wherein the computer readable storage medium controls a device where the computer readable storage medium is located to perform the database sampling method of any one of the above when the computer program is running.

[0019] According to another aspect of the embodiments of the present application, an electronic device is further provided, comprising one or more processors and a memory, the memory being configured to store one or more programs, wherein the one or more programs, when executed by the one or more processors, cause the one or more processors to implement the database sampling method of any one of the above.

[0020] According to another aspect of the embodiments of the present application, a computer program product is further provided, comprising a computer program, which, when executed by a processor, implements the steps of the database sampling method of any one of the above.

[0021] According to the above disclosure, the sampling opportunity can be intelligently determined according to the system resource state and the hotspot distribution change data, and then the adaptive sampler is used to generate a directional sampling plan based on the sampling opportunity and the current hotspot area. The hotspot area is allocated a higher sampling frequency, and the sampling frequency of the non-hotspot area is correspondingly reduced, so as to ensure that the sampling resources are concentrated in the most needed area, improve the representativeness of the sampling data, greatly reduce the overall sampling data volume, and improve the sampling accuracy, thereby solving the technical problems in the related art that the sampling resource allocation of the cold and hot areas is unreasonable, and the sampling accuracy is reduced. BRIEF DESCRIPTION OF DRAWINGS

[0022] The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application. In the drawings:

[0023] Figure 1 A hardware structure block diagram of a computer terminal (or mobile device) for implementing a database sampling method is shown;

[0024] Figure 2 A flowchart of an optional database sampling method according to an embodiment of the application is shown;

[0025] Figure 3 A schematic diagram of an optional sampling system suitable for database cardinality estimation according to an embodiment of the application is shown;

[0026] Figure 4 A schematic diagram of the component composition of an optional logical data hotspot distribution model according to an embodiment of the application is shown;

[0027] Figure 5 A schematic diagram of an optional database sampling device according to an embodiment of the application is shown;

[0028] Figure 6 A structural block diagram of an electronic device for executing a database sampling method according to an embodiment of the application is shown. DETAILED DESCRIPTION

[0029] In order to enable persons skilled in the art to better understand the application scheme, the technical solutions in the embodiments of the application will be described clearly and completely below with reference to the accompanying drawings in the embodiments of the application. Obviously, the described embodiments are only a part of the embodiments of the application, rather than all the embodiments of the application. Based on the embodiments in the application, all other embodiments obtained by persons skilled in the art without creative labor should fall within the protection scope of the application.

[0030] It is to be understood that the terms "first", "second", and the like, used in the description and the claims of the application, as well as the above-described drawings, are used to distinguish similar objects and are not necessarily used to describe a particular sequential or chronological order. It should be understood that the data thus used can be interchanged, where appropriate, so that the embodiments of the application described herein can be carried out in other than the order shown or described herein. Furthermore, the terms "comprising" and "having", as well as any variations thereof, are intended to cover non-exclusive inclusion, for example, processes, methods, systems, products, or devices that include a series of steps or units not necessarily limited to those clearly listed, but can include other steps or units not clearly listed or inherent to such processes, methods, products, or devices.

[0031] To facilitate the understanding of the present application for those skilled in the art, the following explanations are made for some terms or nouns involved in the embodiments of the present application:

[0032] The database query optimizer is the core component of the database management system, which is used to convert high-level declarative queries into low-level efficient execution plans, select the best execution strategy, compare different execution plans (such as index scan, table scan, join operation, etc.), and select the most economical and efficient execution path based on cost evaluation and resource availability, so as to optimize the execution time and resource consumption of the query.

[0033] Cost-based optimization is a common method of database query optimization, which estimates the cost of various query operations and predicts the total cost of different execution plans, and selects the execution plan with the lowest cost based on this. It includes three parts: cardinality estimation module, cost evaluation model, and plan enumeration module. The cardinality estimation module provides an estimate of the number of intermediate tuples, the cost evaluation model calculates the execution cost based on the estimate, and the plan enumeration module selects the execution plan with the lowest cost.

[0034] Cardinality estimation is the process of predicting the number of rows in the intermediate result set generated by a query operation when the database query optimizer generates an execution plan. It is a key step in determining the quality of the execution plan. Accurate cardinality estimation helps the optimizer to correctly calculate the cost of the execution plan and avoid selecting suboptimal or inefficient execution paths.

[0035] Sampling is a statistical method that randomly selects a smaller subset of data from a database table or index. By analyzing the data distribution of the subset (such as value frequency, NULL value proportion, data distribution trend, etc.), the method infers the cardinality and other statistical information of the entire data set, aiming to balance the statistical information collection overhead and estimation accuracy.

[0036] Structured Query Language, commonly known as SQL, is a standard language for querying and manipulating databases.

[0037] XGBOOST, or Extreme Gradient Boosting, is an optimized gradient boosting decision tree algorithm used for training predictive models. It enhances the predictive power of the model by iteratively adding weak predictors (decision trees), making it suitable for various machine learning tasks such as regression and classification. It is known for its high speed, high performance, and high scalability.

[0038] Logical data hotspot distribution model is a dynamic model for analyzing data access patterns. It identifies hotspots in the database (frequently accessed data parts) by real-time statistics of access features in SQL queries, guiding the optimization of sampling strategies and improving the accuracy of cardinality estimation.

[0039] Learning-based sampling trigger is an intelligent component that uses machine learning models to determine when to start the sampling process. It analyzes system resource status and hotspot data changes, and outputs a sampling preference score. Only when the score reaches a certain threshold will the sampling be triggered, thereby minimizing the impact on system resources while ensuring the accuracy of cardinality estimation.

[0040] Adaptive sampler is a component that dynamically adjusts sampling strategies based on data hotspot distribution. Based on the output of the logical data hotspot distribution model, it prioritizes sampling in hot areas and reduces sampling in non-hot areas, achieving the effect of reducing sampling overhead while improving the accuracy of key data statistics.

[0041] Result modifier is responsible for building a closed feedback loop, returning the actual error of cardinality estimation to the logical data hotspot distribution model for adjusting and optimizing model parameters. In this way, the system can continuously improve itself and improve the accuracy of subsequent cardinality estimation.

[0042] It should be noted that the database sampling method and device in the present disclosure can be used in the financial technology field to achieve adaptive sampling suitable for database cardinality estimation. In addition to the financial technology field, the database sampling method and device in the present disclosure can also be used in any field that is suitable for adaptive sampling of database cardinality estimation. The application field of the database sampling method and device in the present disclosure is not limited.

[0043] It should be noted that the information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) collected by the present disclosure are information and data authorized by the user or authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of related data comply with relevant laws, regulations and standards in the relevant region, necessary security measures are taken, it does not violate public order and good customs, and provides corresponding operation portal for user to choose authorization or refusal. For example, an interface is provided between the system and the related user or institution, and before obtaining the relevant information, the interface needs to send a request to the aforementioned user or institution, and after receiving the consent information feedback from the aforementioned user or institution, the relevant information is obtained.

[0044] It should be noted that in the present disclosure, the customer information is collected, the customer information is analyzed, and the corresponding operation portal is provided for the user to select to agree or refuse the automatic decision result; if the user chooses to refuse, the expert decision process is entered.

[0045] The following embodiments of the present application can be applied to various database sampling systems / applications / devices. The present application can be applied to query optimization scenarios of database management systems, especially in scenarios of complex queries and high concurrency access in large relational databases. Specific application scenarios include but are not limited to: the financial industry needs to frequently perform complex queries such as multi-table join and multi-condition filtering when processing massive transaction data, user account information and market data analysis. The present application can significantly improve the precision of cardinality estimation of the query optimizer, thereby improving the response speed of the query and reducing the query cost. E-commerce platforms need to analyze user behavior, sales data and inventory in real time, involving real-time query and statistics of a large amount of data. The present application can effectively cope with the dynamic changes of data and improve the adaptability and accuracy of query plans through adaptive sampling and hotspot area directional strategy.

[0046] The present application can more accurately predict the number of rows of query operations through the logical data hotspot distribution model and adaptive sampling strategy, especially in the scenario of multi-column joint query, which greatly improves the query efficiency. At the same time, the learning sampling trigger is used to intelligently determine the sampling time according to the system resource state and query load characteristics, and the adaptive sampler is used to sample the hotspot data area, so that the overall sampling data volume is greatly reduced.

[0047] The present application will be described in detail below in conjunction with various embodiments.

[0048] Embodiment one

[0049] According to an embodiment of the present invention, an embodiment of a database sampling method is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0050] The database sampling method embodiment provided in Embodiment 1 of this application can be executed on a mobile terminal, computer terminal, or similar computing device. Figure 1 A hardware block diagram of a computer terminal (or mobile device) for implementing a database sampling method is shown. Figure 1 As shown, computer terminal 10 (or mobile device) may include one or more ( Figure 1 (Illustrated as 102a, 102b, ..., 102n) Processor 102 (processor 102 may include, but is not limited to, a microprocessor MCU (Microcontroller Unit) or a programmable gate array (FPGA), etc.), memory 104 for storing data, and transmission device 106 for communication functions. In addition, it may include: a display, input / output interface (I / O interface), Universal Serial Bus (USB) port (which may be included as one of the ports of a BUS bus), network interface, power supply, and / or camera. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, computer terminal 10 may also include... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0051] It should be noted that the aforementioned one or more processors 102 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be integrated, in whole or in part, into any other element within the computer terminal 10 (or mobile device). As involved in the embodiments of this application, the data processing circuits serve as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0052] The memory 104 can be used to store software programs of application software and modules, such as program instructions / data storage devices corresponding to the database sampling method in the embodiments of the present application. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, implements the above-mentioned database sampling method. The memory 104 can include a high-speed random access memory, and can also include a non-volatile memory, such as one or more magnetic storage devices, flash memories, or other non-volatile solid-state memories. In some examples, the memory 104 can further include a memory remotely arranged with respect to the processor 102, which can be connected to the computer terminal 10 through a network. Examples of the above-mentioned network include but are not limited to the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0053] The transmission device 106 is used to receive or send data via a network. Specific examples of the above-mentioned network can include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to be able to communicate with the Internet. In one example, the transmission device 106 can be a radio frequency (Radio Frequency, RF) module, which is used to communicate with the Internet in a wireless manner.

[0054] The display can be, for example, a touch screen type liquid crystal display (Liquid Crystal Display, LCD), which can enable a user to interact with the user interface of the computer terminal 10 (or mobile device).

[0055] Under the above-mentioned operating environment, the present application provides a database sampling method as shown in Figure 2 Figure 2 is a flowchart of an optional database sampling method according to an embodiment of the present application, as shown in Figure 2 The method includes the following steps S201 to S205, which will be described in detail below in combination with each implementation step.

[0056] Firstly, the present application can capture all structured query syntax trees entering the database in real time through the collector, extract core elements (for example, table name, filtering conditions (such as age greater than 30 years old, city equal to **, etc.), connection relationship (connecting two or more tables), etc.), and input them into a logical data hotspot distribution model to construct a query load driven logical hotspot data map.

[0057] ​After the data collector completes the parsing of the syntax tree and the extraction of elements, the logical data hotspot distribution model then enters its working state, beginning to construct a logical hotspot data map. This transforms the extracted core elements into an understanding of data access patterns. Specifically, by analyzing the frequency and combination of table names, filtering conditions, and join relationships, the model can identify which data areas are the focus of queries and which column values ​​are most frequently used to filter results. By analyzing query load drivers, it ensures efficient resource utilization, directing sampling activities towards hotspot areas that truly bear high query frequency and important query logic.

[0058] Step S201: Collect system resource status and hotspot distribution change data based on multiple system monitoring modules.

[0059] Multiple system monitoring modules work collaboratively to cover various resource levels that may be involved in the database operation, including but not limited to CPU (Central Processing Unit) utilization, memory usage, disk I / O rate, network transmission rate, and file descriptor usage. These monitoring modules start at a preset period (in this embodiment, it can be set to once every 300 seconds by default, but can be dynamically adjusted according to actual conditions) to comprehensively collect system resource consumption data and changes in the distribution of logical data hotspots during database operation.

[0060] In this embodiment, by collecting system resource status data, the current system health and available resources can be analyzed. For example, the CPU monitoring module and memory monitoring module can reveal whether the database server's computing power and memory resources are sufficient, while the disk I / O monitoring module and network I / O monitoring module provide bottleneck information for data read / write and transmission. Simultaneously, the logical data hotspot distribution change monitoring module focuses on monitoring the dynamic changes of hotspot data areas. By tracking the access frequency of "data entity identifiers" (i.e., combinations of table names, column names, and value ranges), historical estimation error rates, and whether these identifiers appear or disappear for the first time, the rate of change in hotspot areas can be accurately measured.

[0061] Step S202: Based on the gradient boosting decision tree model, reason about the system resource status and hotspot distribution change data, and output sampling preference score, where the sampling preference score is used to determine the sampling timing.

[0062] Optionally, the step of reasoning about system resource status and hotspot distribution change data and outputting a sampling preference score based on the gradient boosting decision tree model includes: converting the data in the system resource status and hotspot distribution change data into feature vectors, wherein the data types include numerical features and categorical features, the numerical features include at least one of the following: CPU utilization, memory utilization, disk I / O utilization, network I / O throughput, hotspot area change rate, and the categorical features include at least one of the following: file descriptor usage status, hotspot distribution change level; normalizing the feature vectors of the numerical features and performing one-hot encoding on the feature vectors of the categorical features; and inputting the processed feature vectors into the gradient boosting decision tree model to output a sampling preference score.

[0063] In this embodiment, a gradient boosting decision tree model (such as the XGBOOST decision tree algorithm) can be used to analyze the system resource status and hotspot distribution change data, ensuring that the sampling activity will neither increase the server burden during periods of high system load nor miss important periods when the data distribution changes significantly.

[0064] In this embodiment, the numerical features include CPU utilization, memory utilization, disk I / O utilization (disk input / output rate), network I / O throughput (network data transfer speed), and hotspot area change rate (describing the degree of change of hotspot data areas over time). Each numerical feature directly reflects the real-time health status of the database server and the dynamic trend of data access. Categorical features may include file descriptor usage status (assessing the pressure level of database file processing, divided into normal and stressful states), and hotspot distribution change level (e.g., based on the hotspot area change rate, divided into low, medium, and high levels, corresponding to three scenarios: change rate less than 10%, 10% to 30%, and greater than 30%, respectively).

[0065] For numerical features, normalization was used to ensure that each feature maps to the same numerical range (generally between 0 and 1). This is to eliminate the adverse effects of differences in feature scale and ensure that the model treats each feature fairly during training and prediction. For categorical features, one-hot encoding was used. By converting each category into an independent binary vector, one-hot encoding can express all the information of the categorical feature, while avoiding the potential misleading effect of the model assigning sequential values ​​to categorical features.

[0066] After the aforementioned transformation and preprocessing, the input data feature vectors are fed into the model for inference. The model is an ensemble learning method that optimizes the model's predictive performance by iteratively constructing a series of weak classifiers (decision trees) and combining them with the gradient descent algorithm. It is particularly suitable for handling large-scale, complex, and diverse data inputs.

[0067] Step S203: Query the logical data hotspot distribution model to obtain the current hotspot area. The current hotspot area refers to the data area that is accessed more frequently than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data area to be queried.

[0068] It should be noted that the logical data hotspot distribution model is based on the "locality of data access" principle. By continuously monitoring the dynamics of all query loads, it identifies and marks data areas that are accessed frequently and have high estimation error rates. Optionally, the logical data hotspot distribution model includes: a data storage layer that uses a hash table to store access statistics, wherein the hash table includes: data entity identifiers corresponding to the data areas, metadata statistics, and metadata lists, using the data entity identifiers as keys and the metadata statistics and metadata lists as values; a statistical calculation module that calculates the access frequency and historical estimation error rate of the data entity identifiers; a hotspot identification engine that identifies hotspot areas based on the access frequency and historical estimation error rate of the data entity identifiers; and a hotspot area change rate statistics module that calculates the hotspot area change rate based on dimensional data of multiple change dimensions associated with the hotspot areas within the scheduling and monitoring period, wherein the multiple change dimensions include at least: new dimensions, disappearance dimensions, and attribute change dimensions.

[0069] In the data storage layer, a hash table is used as the underlying data structure to store statistical information related to data access. In the hash table, each "data entity identifier" (consisting of table name, column name, and value range) is used as a key, corresponding to a value containing metadata statistics and a metadata list. The metadata statistics cover historical estimation error rates, access frequency, and the most recent access timestamp, while the metadata list records in detail the specific context of each access, such as the type of filter condition, the time of data access, and the estimation error rate at that time.

[0070] Furthermore, the statistical calculation module can dynamically calculate the access frequency and historical estimation error rate of data entity identifiers by applying a "weighted average + time decay" algorithm, ensuring that the identification of hotspot areas can quickly respond to changes in data access patterns while maintaining control over time sensitivity. Optionally, the steps of calculating the access frequency and historical estimation error rate of data entity identifiers include: obtaining the number of queries containing data entity identifiers within a first predetermined time period and a first time weight parameter corresponding to the first predetermined time period, wherein the first predetermined time period is the duration within a first specified time range from the current time point; obtaining the number of queries containing data entity identifiers within a second predetermined time period and a second time weight parameter corresponding to the second predetermined time period, wherein the second predetermined time period is the duration within a second specified time range from the start time point of the first predetermined time period in the historical process; obtaining the total number of accesses containing all data entity identifiers within the first predetermined time period and the total number of accesses containing all data entity identifiers within the second predetermined time period; based on the number of queries containing data entity identifiers within the first predetermined time period and the first time weight parameter corresponding to the first predetermined time period, the access frequency and historical estimation error rate of data entity identifiers within the second predetermined time period are calculated; The access frequency of a data entity identifier is calculated by considering the number of queries containing the data entity identifier, the second time weight parameter corresponding to the second predetermined duration, the total number of accesses containing all data entity identifiers within the first predetermined duration, and the total number of accesses containing all data entity identifiers within the second predetermined duration. The single relative error rate of the i-th query within the first predetermined duration and the single relative error rate of the j-th query within the second predetermined duration are obtained, where i and j are positive integers. Based on the number of queries containing the data entity identifier within the first predetermined duration and the first time weight parameter corresponding to the first predetermined duration, the number of queries containing the data entity identifier within the second predetermined duration and the second time weight parameter corresponding to the second predetermined duration, the single relative error rate of the i-th query within the first predetermined duration, and the single relative error rate of the j-th query within the second predetermined duration, the historical estimation error rate of the data entity identifier is calculated.

[0071] The first and second predetermined time periods can be defined by the user. For example, the first predetermined time period can be defined as one hour from the current time, with a fixed weight parameter of 0.8, indicating that access within the most recent time period is given high priority to reflect the latest data access patterns. The second predetermined time period is set to 1 to 24 hours in the historical process, with a fixed weight parameter of 0.2, meaning that access to relatively distant data still has reference value, but with a lower weight, to balance the impact of recent and distant data on the overall access frequency. The number of queries within the first predetermined time period is multiplied by the first time weight parameter, and the number of queries within the second predetermined time period is multiplied by the second time weight parameter. Finally, the two products are added together to obtain the weighted query count. The weighted query count is divided by the weighted total access count of all data entity identifiers within the first and second predetermined time periods to obtain the access frequency of the data entity identifier. This ensures that the identification of hotspot areas can quickly respond to the latest changes in data access patterns, while maintaining a certain level of attention to historical data access patterns based on the weighted average strategy, avoiding the bias that may be caused by completely ignoring historical data.

[0072] When calculating the historical estimation error rate, the number of queries containing data entity identifiers within a first predetermined time period is first multiplied by a first time weight parameter. Then, the number of queries containing data entity identifiers within a second predetermined time period is multiplied by a second time weight parameter. Subsequently, the relative error rate of the i-th query within the first predetermined time period is multiplied by the weighted number of queries corresponding to that query, and the relative error rate of the j-th query within the second predetermined time period is multiplied by the weighted number of queries corresponding to that query. All weighted errors are then summed, and finally, the result is divided by the weighted total number of queries for all data entity identifiers within the first and second predetermined time periods to obtain the historical estimation error rate. This dynamic evaluation of the historical estimation results effectively reflects the trend of estimation error changes for data entity identifiers over different time periods, providing data support for the identification of hotspot areas and the adjustment of sampling strategies.

[0073] This embodiment can not only capture data access hotspots in real time, but also accurately reflect the statistical information estimation error of data entity identifiers, providing a more comprehensive and accurate data basis for adaptive sampling decisions, thereby improving the overall query optimization effect and the operating efficiency of the database system.

[0074] Furthermore, the hotspot identification engine combines access frequency and historical estimation error rate to identify data entities with high access frequency and low historical estimation accuracy, i.e., hotspot areas. For example, the engine uses a dual screening criterion of Top-K access frequency and historical estimation error rate >15% to ensure that data areas that truly need extra attention and sampling optimization are marked as hotspot areas, thus avoiding resource waste.

[0075] Furthermore, the hotspot area change rate statistics module can monitor the dynamic changes of hotspot areas within the scheduling and monitoring period, including additions, disappearances, or attribute changes, to quantify the stability of hotspot areas. By calculating the change rate of hotspot areas, the volatility of data access patterns can be assessed, thereby guiding the adjustment of sampling strategies. Optionally, the steps for calculating the hotspot area change rate based on dimensional data of multiple change dimensions of associated hotspot areas within the scheduling and monitoring period include: in the addition dimension, obtaining the number of hotspot areas added to the hotspot area list within the statistical period, where the hotspot area list contains all hotspot areas in the current time period; in the disappearance dimension, obtaining the number of hotspot areas removed from the hotspot area list within the statistical period; in the attribute change dimension, obtaining the number of hotspot areas that were already in the hotspot area list within the statistical period but whose core attributes changed; and calculating the hotspot area change rate based on the number of added areas and the weight parameters corresponding to the addition dimension, the number of removed areas and the weight parameters corresponding to the disappearance dimension, and the number of attribute change areas and the weight parameters corresponding to the attribute change dimension.

[0076] This embodiment, for the newly added dimension, obtains the number of newly added regions by counting the number of hotspot regions newly added to the hotspot region list during the monitoring period, reflecting the number of data regions first identified as hotspots during the monitoring period. Then, this embodiment focuses on the disappearance dimension, counting the number of hotspot regions removed from the hotspot region list during the monitoring period, determining the number of exiting regions, reflecting the instability of data hotspot regions, i.e., those data regions that were frequently accessed in the past but whose current access frequency has decreased. In the attribute change dimension, this embodiment calculates the changes in the core attributes of hotspot regions during the monitoring period and records the number of regions with attribute changes. Changes in core attributes may include significant fluctuations in historical estimation error rates, adjustments to the recommended sampling rate, etc., revealing the instability of the statistical characteristics within hotspot regions and affecting the formulation of subsequent sampling strategies.

[0077] Based on the number of regions across the three dimensions mentioned above, this embodiment further calculates the rate of change of hotspot regions. For example, the formula for calculating the rate of change is as follows:

[0078] The rate of change is calculated as follows: (Number of newly added regions * Weighting parameter for new additions + Number of regions exiting the data * Weighting parameter for disappearances + Number of regions with attribute changes * Weighting parameter for attribute changes) / Total number of hotspot regions. Here, the dimensions of addition, disappearance, and attribute changes each correspond to different weighting parameters, used to adjust the contribution of different change dimensions to the overall rate of change. The weighting parameters for addition and disappearance together reflect the increase or decrease in data hotspot regions, while the weighting parameter for attribute changes measures the degree of fluctuation in statistical information within hotspot regions. By appropriately setting these weighting parameters, the calculation of the rate of change can be ensured to be both comprehensive and accurate, providing strong data support for the dynamic adjustment of sampling strategies.

[0079] Optionally, the logical data hotspot distribution model, during runtime, also includes: receiving the core elements of the query syntax tree output by the database query collector and parsing them to obtain the data entity identifier and access type; if a data entity identifier already exists in the hash table, updating the access frequency of the data entity identifier using a time decay algorithm; if no data entity identifier is detected in the hash table, adding a key-value pair associated with the data entity identifier to the hash table and setting the initial access frequency of the data entity identifier.

[0080] In this embodiment, the operation of the logical data hotspot distribution model also includes real-time processing of the core elements of the query syntax tree output by the database query collector. Specifically, if a data entity identifier already exists in the hash table, the access frequency of the identifier is updated based on the time decay algorithm to ensure that the access frequency can dynamically reflect the access popularity of the data entity in different time windows, and give higher weight to recent accesses to capture the latest access trends. If a data entity identifier does not exist in the hash table, a key-value pair is added to the hash table, with the data entity identifier as the key, and its access frequency is initialized. For example, the initial access frequency is set to a low value (such as 1%) to ensure that every data entity can be taken into consideration by the model, even when the initial access frequency is low.

[0081] Step S204: Use an adaptive sampler to generate a targeted sampling plan based on the sampling timing and the current hotspot area.

[0082] In this embodiment, an adaptive sampler is used to generate a targeted sampling plan based on the current sampling timing and the current hotspot region information extracted from the logical data hotspot distribution model. This optimizes the statistical information collection process, ensuring that hotspot regions receive higher sampling density while non-hotspot regions receive relatively less sampling, thereby reducing system resource consumption while ensuring the accuracy of cardinality estimation. Optionally, the step of generating a targeted sampling plan based on the sampling timing and the current hotspot region includes: obtaining the historical estimation error rate and access frequency of the data entity identifiers corresponding to the current hotspot region, which are pre-recorded in the hash table of the logical data hotspot distribution model; allocating a first sampling rate to the current hotspot region based on the region weight, the historical estimation error rate, and the access frequency of the data entity identifiers corresponding to the current hotspot region; allocating a second sampling rate to other non-hotspot regions in the hash table, based on the historical estimation error rate of the data entity identifiers corresponding to the non-hotspot regions; and generating a targeted sampling plan based on the first sampling rate of the current hotspot region, the second sampling rate of the non-hotspot region, and the sampling timing.

[0083] First, the historical estimation error rate and access frequency of the data entity identifiers corresponding to the current hotspot region are retrieved from the hash table in the logical data hotspot distribution model. Then, this embodiment employs a multi-factor comprehensive evaluation based on the region weight, historical estimation error rate, and access frequency of the current hotspot region, allocating a higher primary sampling rate (denoted as the first sampling rate) to the current hotspot region. The region weight reflects the relative importance of the hotspot region in the data access pattern, the historical estimation error rate reflects the inaccuracy of past estimates, and the access frequency directly shows the activity level of the data region. For non-hotspot regions in the hash table other than the current hotspot region, this embodiment samples based on a relatively lower secondary sampling rate (denoted as the second sampling rate). Considering the historical estimation error rate of non-hotspot regions, if the error rate of a certain non-hotspot region is low, the allocated sampling rate will be lowered accordingly, and vice versa. This ensures that with limited sampling resources, resources can be more effectively concentrated on the most critical data regions, while avoiding oversampling of non-hotspot regions with known accurate estimates, thereby reducing unnecessary system burden.

[0084] Step S205: Execute the targeted sampling plan and output the sampled data to the query optimizer, whereby the query optimizer is used to select the optimal query execution plan based on the sample data.

[0085] Optionally, the steps of executing the targeted sampling plan include: executing the targeted sampling plan and linking the database storage layer; randomly selecting data pages in the database storage layer, and then filtering out row data belonging to the current hotspot area from the data pages to obtain sample data, wherein the sample data is stored using a least recently used caching strategy.

[0086] Executing a targeted sampling plan first requires establishing a link with the database storage layer to ensure that sampling activities can directly affect the stored data pages. Within the database storage layer, sampling activities follow the guidelines of the targeted sampling plan, employing a page-by-page sampling + row filtering approach. Specifically, data pages are first randomly selected; this measure aims to reduce sampling bias and ensure the representativeness of the sample data. Subsequently, rows belonging to the current hotspot regions are further filtered from the selected data pages. Through this step, the sampling activity can focus on data regions with high access frequency and large estimation error rates, thereby reducing the total amount of sampled data while improving the accuracy of statistical information for hotspot regions.

[0087] The sample data obtained will be stored using a Least Recently Used (LRU) caching strategy. This strategy determines the cache eviction mechanism based on the access frequency and time order of the data, ensuring that the most frequently accessed data is retained first. This ensures that the sample data can be effectively reused in subsequent query optimization processes, avoiding the waste of resources from repeated sampling.

[0088] To address the specific requirements of multi-column joint queries, this embodiment adds a sub-sampling plan generation step after executing the targeted sampling plan. Optionally, after executing the targeted sampling plan, it further includes: generating a separate sub-sampling plan for each column combination in the multi-column joint query, wherein the number of samples for each column combination in the sub-sampling plan is greater than a preset sample number threshold. Specifically, for each column combination in the multi-column joint query, the sub-sampling plan requires that the number of samples sampled must exceed the preset sample number threshold (in this embodiment, it can be set to no less than 100 samples).

[0089] Optionally, after outputting the sampled data to the query optimizer, the method further includes: outputting the sampled data to the cardinality estimator in the query optimizer, whereby the cardinality estimator generates a prediction result of the number of associated intermediate operation tuples; comparing the prediction result of the cardinality estimator with the sample data to obtain a comparison error value; and feeding the comparison error value back to the logical data hotspot distribution model, whereby the logical data hotspot distribution model adjusts the weight parameters of the current hotspot region.

[0090] After the collection of sampling data is completed, the sample data is output to the cardinality estimator module within the query optimizer. The cardinality estimator receives this data and, based on statistical analysis and a pre-defined model algorithm, generates a prediction result related to the number of intermediate operation tuples. Next, this embodiment involves comparing the prediction result generated by the cardinality estimator with the collected sample data to evaluate the accuracy of the prediction. The comparison process generates a comparison error value, quantifying the deviation between the prediction result and the actual situation. The comparison error value is obtained by directly comparing the actual statistical information of the sample data (such as the actual number of rows, value distribution, etc.) with the prediction result of the cardinality estimator. The magnitude of the deviation directly reflects the matching degree between the current hotspot area sampling strategy and the cardinality estimation model, and whether adjustments to the sampling strategy or model parameters are needed to reduce the prediction error and improve the decision quality of the query optimizer.

[0091] Finally, the comparison error value is fed back to the logical data hotspot distribution model. Based on this feedback, the model adjusts the weight parameters of hotspot regions, dynamically optimizing the identification and sampling strategy by learning the correlation between prediction error and hotspot region access patterns. When the comparison error value corresponding to a hotspot region exceeds a preset error threshold (e.g., greater than 30%), the weight parameter of that hotspot region is increased (e.g., by 20%) to prompt more intensive sampling of that region in the future. Conversely, if the comparison error value is low (e.g., below 5%), the weight parameter is decreased (e.g., by 10%) to reduce subsequent sampling resource consumption. In this way, the logical data hotspot distribution model can dynamically adjust the priority of hotspot regions based on actual results, ensuring that the sampling strategy is both accurate and efficient.

[0092] Through the above steps, system resource status and hotspot distribution change data can be collected based on multiple system monitoring modules; based on the gradient boosting decision tree model, reasoning is performed on the system resource status and hotspot distribution change data to output a sampling preference score, which is used to determine the sampling timing; the hotspot distribution model of the query logic data is queried to obtain the current hotspot region, where the current hotspot region refers to the data region whose access frequency is higher than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data region to be queried; an adaptive sampler is used to generate a targeted sampling plan based on the sampling timing and the current hotspot region; the targeted sampling plan is executed, and the sampled sample data is output to the query optimizer, where the query optimizer is used to select the optimal query execution plan based on the sample data. In this embodiment, the sampling timing can be intelligently determined based on the system resource status and hotspot distribution change data. Then, an adaptive sampler is used to generate a targeted sampling plan based on the sampling timing and the current hotspot area. Hotspot areas are allocated a higher sampling frequency, while the sampling frequency of non-hotspot areas is reduced accordingly. This ensures that sampling resources are concentrated in the most needed areas, improves the representativeness of the sampling data, greatly reduces the overall amount of sampling data, and improves the sampling accuracy. This solves the technical problem in related technologies where the sampling-based cardinality estimation strategy has an unreasonable allocation of sampling resources in hot and cold areas, leading to a decrease in sampling accuracy.

[0093] The following describes in detail another optional implementation method.

[0094] This invention addresses the shortcomings of existing sampling methods by providing an adaptive sampling method suitable for database cardinality estimation. It can resolve the contradiction between static sampling and dynamic data evolution, and achieve real-time adaptation of sampling triggering timing to changes in data distribution. At the same time, it can solve the problem of sampling strategies lacking query load awareness, and achieve targeted allocation of sampling resources to hot data areas, thereby improving sampling efficiency.

[0095] This invention proposes an adaptive sampling method suitable for database cardinality estimation, comprising the following steps:

[0096] 1. SQL Acquisition and Hotspot Modeling: The database query SQL collector captures all SQL syntax trees entering the database in real time, extracts core elements (table names, filter conditions, join relationships, etc.), and inputs them into the logical data hotspot distribution model to build a logical hotspot data map driven by query load.

[0097] 2. System and Hotspot Monitoring: The unified scheduling module triggers the CPU monitoring module, memory monitoring module, disk I / O monitoring module, file descriptor monitoring module, network I / O monitoring module, and logical data hotspot distribution change monitoring module at preset cycles (default 300s, can be dynamically adjusted) to collect system resource status and hotspot distribution change data, and outputs them to the learning sampling trigger.

[0098] 3. Adaptive sampling trigger: The learning-based sampling trigger is based on the XGBOOST pre-trained model. It infers the input system resource status and hotspot distribution change data and outputs a "sampling preference score" in the range of 0-1. When the score exceeds the dynamic threshold (which can be adjusted according to the system load strategy), the adaptive sampler is triggered.

[0099] 4. Targeted Sampling Execution: The adaptive sampler queries the logical data hotspot distribution model to obtain the current hotspot data region (such as high-frequency access tables, column combinations of multi-column joint queries, and numerical ranges corresponding to high-frequency filtering conditions), generates a targeted sampling plan (the sampling rate of hotspot regions is higher than that of non-hotspot regions), executes sampling, and provides the sample data to the cardinality estimator.

[0100] 5. Feedback Correction Closed Loop: The result trimmer compares the prediction results of the cardinality estimator with the actual execution results of the execution engine in real time, calculates the error value and feeds it back to the logical data hotspot distribution model, dynamically adjusts the weight and sampling priority of hotspot regions, and optimizes subsequent sampling strategies.

[0101] Figure 3 This is a schematic diagram of an optional sampling system for database cardinality estimation according to an embodiment of the present invention, such as... Figure 3 As shown, the sampling system includes: an SQL collector, a logical data hotspot distribution model, a result corrector, an adaptive sampler, a learning sampling trigger, and a unified scheduling module. The following is a schematic description of each of these modules.

[0102] First, the logical data hotspot distribution model is a dynamic model that simulates physical data access patterns at the logical level. Its core principle is based on the assumption of "locality of access" and simulates the query access patterns of physical data at the logical level. By statistically analyzing the access characteristics of historical and current queries in real time, it identifies data areas with high frequency of access and provides a directional basis for adaptive sampling. Unlike the traditional "data-driven" model, this model is a "query load-driven" model, which can dynamically adapt to changes in query requirements.

[0103] Figure 4 This is a schematic diagram illustrating the component composition of an optional logical data hotspot distribution model according to an embodiment of the present invention, such as... Figure 4As shown, it includes: a data storage layer, a statistical calculation module, a hotspot area change statistics module, a hotspot identification engine, and an error feedback processor. Each part is illustrated below.

[0104] (1) Data storage layer: using hash tables (e.g.) Figure 4 The access statistics table shown in the image stores access statistics information. The key is "data entity identifier" (format: table name_column name_value range, such as users_age_30-40), and the value is metadata statistics value + metadata list (statistics value includes historical estimation error rate, access frequency, and recent access time; metadata list includes timestamp of each access, filter condition type (equal / range / join), and current estimation error rate).

[0105] (2) Statistical calculation module: The access frequency and historical estimation error rate are calculated using the "weighted average + time decay" algorithm. Access records within the last hour are assigned a weight of 0.8, access records within 1-24 hours are assigned a weight of 0.2, and records older than 24 hours are automatically invalidated to ensure the timeliness of hotspot areas.

[0106] The access frequency can include the access popularity of a specific "data entity identifier" within 24 hours, used to quantify how frequently the data area is queried and accessed. The calculation formula is as follows:

[0107]

[0108] Variable definition:

[0109] n1: The number of queries containing this data entity in the past hour;

[0110] w1: The time weight of the error within the last hour, fixed at 0.8;

[0111] T1: Total number of accesses to all data entities in the past hour (used for normalization to ensure that the access rate is a relative percentage);

[0112] n2: The number of queries containing this data entity within 1-24 hours;

[0113] w2: Time weight of error from 1 to 24 hours, fixed at 0.2;

[0114] T2: Total number of accesses to all data entities within 1-24 hours.

[0115] The historical estimation error rate can be a weighted average of all single relative error rates within the past 24 hours, weighted by time decay. The formula for calculating the historical estimation error rate is:

[0116]

[0117] Variable definition:

[0118] n1: The number of queries containing this data entity in the past hour;

[0119] w1: The time weight of the error within the last hour, fixed at 0.8;

[0120] n2: The number of queries containing this data entity within 1-24 hours;

[0121] w2: Time weight of error from 1 to 24 hours, fixed at 0.2;

[0122] E i The relative error rate (%) of the i-th query within the past hour;

[0123] E j The relative error rate (%) of the j-th query within 1-24 hours.

[0124] (3) Hotspot identification engine: Based on the dual conditions of “Top-K access frequency” (default K=10) and “historical estimation error rate>15%”, hotspot areas are identified, that is, only data areas with high access frequency and low historical estimation accuracy are marked as “key sampling areas”.

[0125] (4) Hotspot Area Change Rate Statistics Module: This module calculates the hotspot area change rate by analyzing three core change dimensions: "addition, disappearance, and attribute change" of hotspot areas within the scheduling and monitoring period. The hotspot area change rate is triggered by a request from the logical data hotspot distribution change monitoring module and can accurately quantify the dynamic change degree of hotspot areas within the monitoring period. The formula for calculating the hotspot area change rate is:

[0126]

[0127] Variable definition:

[0128] H new The number of hotspot areas that first entered the "key sampling area" list within the statistical period;

[0129] H lost The number of hotspot areas removed from the "key sampling area" list during the statistical period;

[0130] H attr The number of hotspots that were already in the "key sampling areas" list during the statistical period but whose core attributes have changed (historical estimation error rate fluctuations exceed 20%, recommended sampling rate adjustments exceed 5%).

[0131] w1, w2, w3: These are the weights for new additions, disappearances, and attribute changes, respectively, used to adjust the contribution of different change dimensions to the overall change rate, and are 1, 1, and 0.5 respectively.

[0132] The logical data hotspot distribution model operation flow is as follows:

[0133] (1) The logical data hotspot distribution model receives the core elements of the SQL syntax tree output by the SQL collector and parses them to obtain the "data entity identifier" and access type;

[0134] (2) If the “data entity identifier” already exists in the hash table, update its access frequency (apply time decay algorithm) and timestamp; if it does not exist, add a key-value pair and set the initial access frequency to 1%.

[0135] (3) Perform model cleanup every 300 seconds: delete “data entity identifiers” that have not been accessed for more than 24 hours and those that have been accessed within 24 hours but whose weighted access frequency has been recalculated.

[0136] (4) Respond to the query request of the adaptive sampler and output a list of key sampling areas (including table name, column name, value range, and recommended sampling rate);

[0137] (5) Respond to the request from the logical data hotspot distribution change monitoring module and output the hotspot area change rate;

[0138] (6) Receive error feedback from the result trimmer and update the current estimation error rate and historical estimation error rate; if the current relative estimation error rate of a certain “data entity identifier” is >30%, increase its access frequency by 20% (not higher than 100%); if the relative error rate is ≤5%, decrease its access frequency by 10% (not lower than the initial value).

[0139] Part Two: Learning Sample Trigger.

[0140] The learning-based sampling trigger dynamically determines the sampling timing based on a machine learning model, avoiding the resource waste or estimation lag problems of traditional "fixed period / fixed threshold" triggering. By learning the correlation between system resource load and hotspot distribution changes, it triggers sampling when "system resources are idle" and "hotspot distribution changes significantly", balancing sampling overhead and estimation accuracy.

[0141] The learning-based sampling trigger includes the following components:

[0142] Feature extraction module: Converts the data collected by the monitoring module into feature vectors, including numerical features and categorical features. Numerical features include CPU utilization (%), memory utilization (%), disk I / O utilization (%), network I / O throughput (MB / s), and hotspot area change rate (%). Categorical features include file descriptor usage status (normal / stressful) and hotspot distribution change level (low / medium / high, corresponding to hotspot area change rate <10% / 10%-30% / >30%). The module normalizes the numerical features (mapped to the 0-1 interval) and performs one-hot encoding on the categorical features.

[0143] XGBOOST model: The model is trained using a mixture of historical system operation data (including manually labeled "optimal sampling time") and simulation data (simulating high load / high hotspot change scenarios); the model input is a 10-dimensional feature vector (5 numerical features + 5 one-hot encoded categorical features); the model output is a sampling preference score (0-1, the higher the score, the more suitable it is for sampling); incremental training is performed every day at 3:00 AM (during the system's off-peak period) based on the new data from the previous 24 hours to update the model parameters.

[0144] Decision engine: Employs a dynamic threshold mechanism - when system load is <50% (average CPU + memory usage), the threshold is set to 0.6; when system load is ≥50%, the threshold is set to 0.8; if the sampling preference score is ≥ the threshold, the adaptive sampler is triggered.

[0145] Part Three: Adaptive Sampler.

[0146] The adaptive sampler is a targeted sampling mechanism based on the "key sampling area list" of the logical data hotspot distribution model. It adopts the "hierarchical importance sampling" algorithm, which allocates a higher sampling rate to hotspot areas and a lower sampling rate to non-hotspot areas. While reducing the total sample size, it improves the accuracy of statistical information in hotspot areas, especially optimizing the cardinality estimation effect of multi-column joint queries.

[0147] The adaptive sampler includes the following components:

[0148] (1) Sampling Plan Generator: Generates a sampling plan based on the "List of Key Sampling Areas", with the following rules:

[0149] 1. Hotspot tables (Top 10 most frequently accessed tables with historical estimated error rate > 15%): The basic sampling rate is 10%. If the table includes a combination of columns in a multi-column query, the sampling rate is increased to 15%.

[0150] 2. Non-hotspot table: The basic sampling rate is 1%. If the historical estimation error rate is <5%, the sampling rate is reduced to 0.5%.

[0151] 3. Column combination for multi-column joint query: Generate a separate sub-sampling plan to ensure that the sample size of each column combination is ≥100 rows (if the data size of the combination is less than 100 rows, then full sampling will be performed).

[0152] (2) Sampling execution engine: It interacts directly with the database storage layer and uses the "page sampling + row filtering" method to perform sampling - first randomly extract data pages, and then filter out the row data belonging to the "key sampling area" from the pages to reduce the reading of invalid data.

[0153] (3) Sample management module: The LRU (Least Recently Used) caching strategy is used to store sample data, and the cache validity period is 30 minutes. If the "data entity identifier" corresponding to a sample is marked as a "key sampling area", the cache validity period is extended to 1 hour.

[0154] Part Four, Result Trimmer.

[0155] The core function of the trimmer is to build a closed loop of "estimation-feedback-correction", feed the estimation error back to the logical data hotspot distribution model, quantify the cardinality estimation error and guide the model optimization.

[0156] The estimation error is calculated using the "relative error rate", and the formula is as follows:

[0157]

[0158] Variable definition:

[0159] ∈: Minimum value (10 in this case) -6 This is used to avoid calculation anomalies where the denominator is 0 when the actual number of rows in the result is 0, thus ensuring the validity of the formula.

[0160] Through the above embodiments, high-frequency data areas of multi-column joint queries can be identified by logical data hotspot distribution model. Combined with targeted sampling strategy, the cardinality estimation relative error rate of multi-column joint predicates can be reduced by 30%-50%, which is significantly better than traditional general sampling methods. Based on the dynamic triggering mechanism of learning-type sampling trigger, sampling is only performed when system resources are idle and hotspot changes are significant, reducing the total amount of sampled data by 40%-60% and reducing CPU, disk I / O and memory resource consumption.

[0161] Furthermore, this embodiment can achieve a real-time closed loop of "SQL collection - hot spot update - feedback correction" to dynamically adjust the sampling strategy according to data distribution and query load. In high-frequency data update scenarios, the timeliness of cardinality estimation results is improved by more than 80%, avoiding execution plan failure caused by outdated samples.

[0162] The following is a detailed description with reference to another embodiment.

[0163] Example 2

[0164] The database sampling device provided in this embodiment includes multiple implementation units, each of which corresponds to a specific implementation step in the above embodiment one. The specific implementation method and beneficial effects can be referred to the foregoing method embodiment, and will not be repeated here.

[0165] Figure 5 This is a schematic diagram of an optional database sampling device according to an embodiment of the present invention, such as... Figure 5 As shown, the database sampling device may include: a data acquisition unit 51, a data inference unit 52, a hotspot area query unit 53, a sampling plan generation unit 54, and a sampling plan execution unit 55.

[0166] Among them, the data acquisition unit 51 is used to collect data on the status of system resources and changes in hotspot distribution based on multiple system monitoring modules.

[0167] The data inference unit 52 is used to infer the system resource status and hotspot distribution change data based on the gradient boosting decision tree model, and output the sampling preference score, which is used to determine the sampling timing.

[0168] Hotspot area query unit 53 is used to query the logical data hotspot distribution model to obtain the current hotspot area. The current hotspot area refers to the data area that is accessed more frequently than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data area to be queried.

[0169] The sampling plan generation unit 54 is used to generate a targeted sampling plan based on the sampling timing and the current hot spot area using an adaptive sampler.

[0170] The sampling plan execution unit 55 is used to execute the targeted sampling plan and output the sampled data to the query optimizer, whereby the query optimizer is used to select the optimal query execution plan based on the sample data.

[0171] The aforementioned database sampling device can collect system resource status and hotspot distribution change data based on multiple system monitoring modules through the data acquisition unit 51, and infer the system resource status and hotspot distribution change data based on the gradient boosting decision tree model through the data inference unit 52, outputting a sampling preference score, which is used to determine the sampling timing. The hotspot area query unit 53 queries the logical data hotspot distribution model to obtain the current hotspot area, which refers to the data area whose access frequency is higher than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data area to be queried. The sampling plan generation unit 54 uses an adaptive sampler to generate a targeted sampling plan based on the sampling timing and the current hotspot area. The sampling plan execution unit 55 executes the targeted sampling plan and outputs the sampled sample data to the query optimizer, which is used to select the optimal query execution plan based on the sample data. In this embodiment, the sampling timing can be intelligently determined based on the system resource status and hotspot distribution change data. Then, an adaptive sampler is used to generate a targeted sampling plan based on the sampling timing and the current hotspot area. Hotspot areas are allocated a higher sampling frequency, while the sampling frequency of non-hotspot areas is reduced accordingly. This ensures that sampling resources are concentrated in the most needed areas, improves the representativeness of the sampling data, greatly reduces the overall amount of sampling data, and improves the sampling accuracy. This solves the technical problem in related technologies where the sampling-based cardinality estimation strategy has an unreasonable allocation of sampling resources in hot and cold areas, leading to a decrease in sampling accuracy.

[0172] Optionally, the logical data hotspot distribution model includes: a data storage layer, which uses a hash table to store access statistics, wherein the hash table includes: data entity identifiers corresponding to data regions, metadata statistics, and a metadata list, with the data entity identifiers as keys and the metadata statistics and metadata list as values; a statistical calculation module, which calculates the access frequency and historical estimation error rate of the data entity identifiers; a hotspot identification engine, which identifies hotspot regions based on the access frequency and historical estimation error rate of the data entity identifiers; and a hotspot region change rate statistics module, which calculates the hotspot region change rate based on dimensional data of multiple change dimensions associated with the hotspot regions within the scheduling and monitoring period, wherein the multiple change dimensions include at least: new dimensions, disappearance dimensions, and attribute change dimensions.

[0173] Optionally, the statistical calculation module includes: a first acquisition submodule, used to acquire the number of queries containing data entity identifiers within a first predetermined time period and a first time weight parameter corresponding to the first predetermined time period, wherein the first predetermined time period is the time within a first specified time range from the current time point; a second acquisition submodule, used to acquire the number of queries containing data entity identifiers within a second predetermined time period and a second time weight parameter corresponding to the second predetermined time period, wherein the second predetermined time period is the time within a second specified time range from the start time point of the first predetermined time period in the historical process; a third acquisition submodule, used to acquire the total number of accesses containing all data entity identifiers within the first predetermined time period and the total number of accesses containing all data entity identifiers within the second predetermined time period; and a first calculation submodule, used to calculate the number of queries containing data entity identifiers within the first predetermined time period and the first time weight parameter corresponding to the first predetermined time period, and the total number of accesses containing all data entity identifiers within the second predetermined time period. The system includes a first predetermined time period (10 ...

[0174] Optionally, the hotspot area change rate statistics module includes: a fifth acquisition submodule, used to acquire the number of hotspot areas added to the hotspot area list within the statistical period in the addition dimension, thereby obtaining the number of newly added areas, wherein the hotspot area list contains all hotspot areas in the current time period; a sixth acquisition submodule, used to acquire the number of hotspot areas removed from the hotspot area list within the statistical period in the disappearance dimension, thereby obtaining the number of exited areas; a seventh acquisition submodule, used to acquire the number of hotspot areas that are already in the hotspot area list within the statistical period but whose core attributes have changed in the attribute change dimension, thereby obtaining the number of attribute-changed areas; and a fourth calculation submodule, used to calculate the hotspot area change rate based on the number of newly added areas and the weight parameters corresponding to the addition dimension, the number of exited areas and the weight parameters corresponding to the disappearance dimension, and the number of attribute-changed areas and the weight parameters corresponding to the attribute-changed dimension.

[0175] Optionally, the logical data hotspot distribution model, during runtime, also includes: a parsing module, used to receive the core elements of the query syntax tree output by the database query collector and parse them to obtain the data entity identifier and access type; an access frequency update module, used to update the access frequency of the data entity identifier using a time decay algorithm when the existence of the data entity identifier in the hash table is detected; and a key-value pair addition module, used to add a key-value pair associated with the data entity identifier in the hash table when the absence of the data entity identifier in the hash table is detected, and set the initial access frequency of the data entity identifier.

[0176] Optionally, the data inference unit includes: a data transformation module, used to convert data in system resource status and hotspot distribution change data into feature vectors, wherein the data types include numerical features and categorical features, the numerical features including at least one of the following: CPU utilization, memory utilization, disk input / output IO utilization, network IO throughput, hotspot area change rate, and the categorical features including at least one of the following: file descriptor usage status, hotspot distribution change level; a feature processing module, used to normalize the feature vectors of numerical features and perform one-hot encoding on the feature vectors of categorical features; and a feature vector input module, used to input the processed feature vectors into the gradient boosting decision tree model and output sampling preference scores.

[0177] Optionally, the sampling plan generation unit includes: an entity access frequency acquisition module, used to acquire the historical estimation error rate and access frequency of the data entity identifier corresponding to the current hot spot area, which are pre-recorded in the hash table of the logical data hot spot distribution model; a first sampling rate allocation module, used to allocate a first sampling rate to the current hot spot area based on the area weight of the current hot spot area, the historical estimation error rate of the data entity identifier corresponding to the current hot spot area, and the access frequency; a second sampling rate allocation module, used to allocate a second sampling rate to other non-hot spot areas in the hash table, based on the historical estimation error rate of the data entity identifier corresponding to the non-hot spot area; and a sampling plan generation module, used to generate a targeted sampling plan based on the first sampling rate of the current hot spot area, the second sampling rate of the non-hot spot area, and the sampling timing.

[0178] Optionally, the sampling plan execution unit includes: a sampling plan execution module for executing a targeted sampling plan and linking to the database storage layer; and a sample data acquisition module for randomly extracting data pages from the database storage layer, filtering out row data belonging to the current hotspot area from the data pages to obtain sample data, wherein the sample data is stored using a least recently used caching strategy.

[0179] Optionally, the database sampling device further includes a sub-sampling plan generation unit, used to generate a separate sub-sampling plan for column combinations of multi-column joint queries after executing the directional sampling plan, wherein the number of samples for each column combination in the sub-sampling plan is greater than a preset sample number threshold.

[0180] Optionally, the database sampling device further includes: a prediction result generation unit, used to output the sampled data obtained from sampling to the cardinality estimator in the query optimizer after outputting the sampled data to the query optimizer, so that the cardinality estimator generates a prediction result of the number of associated intermediate operation tuples; a sample data comparison unit, used to compare the prediction result of the cardinality estimator with the sample data to obtain a comparison error value; and a region weight adjustment unit, used to feed the comparison error value back to the logical data hotspot distribution model, so that the logical data hotspot distribution model adjusts the weight parameters of the current hotspot region.

[0181] The aforementioned database sampling device may also include a processor and a memory. The aforementioned data acquisition unit 51, data inference unit 52, hotspot area query unit 53, sampling plan generation unit 54, sampling plan execution unit 55, etc., are all stored in the memory as program units, and the processor executes the aforementioned program units stored in the memory to realize the corresponding functions.

[0182] The aforementioned processor contains a kernel, which retrieves the corresponding program units from memory. One or more kernels can be configured, and adaptive sampling suitable for database cardinality estimation can be achieved by adjusting kernel parameters.

[0183] The aforementioned memory may include non-permanent memory in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0184] Example 3

[0185] Embodiments of this application may provide an electronic device. Figure 6 This is a structural block diagram of an electronic device for performing a database sampling method according to an embodiment of this application. Figure 6 As shown, the electronic device may include: one or more ( Figure 6 (Only one is shown) processor 602, memory 604, memory controller, and peripheral interface, wherein the peripheral interface is connected to the radio frequency module, audio module and display.

[0186] The memory can be used to store software programs and modules, such as the program instructions / modules corresponding to the database sampling method and apparatus in this application embodiment. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory, thereby implementing the aforementioned database sampling method. The memory may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include memory remotely located relative to the processor, and these remote memories can be connected to the terminal via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0187] The processor can access information and applications stored in memory via a transmission device to execute the following steps: collecting system resource status and hotspot distribution change data based on multiple system monitoring modules; inferring system resource status and hotspot distribution change data based on a gradient boosting decision tree model, outputting a sampling preference score, where the sampling preference score is used to determine the sampling timing; querying the logical data hotspot distribution model to obtain the current hotspot region, where the current hotspot region refers to the data region whose access frequency is higher than a preset access frequency threshold in the current time period, the access frequency being determined by parsing the elements of all query requests initiated by the query load; generating a targeted sampling plan based on the sampling timing and the current hotspot region using an adaptive sampler; executing the targeted sampling plan and outputting the sampled data to the query optimizer, where the query optimizer is used to select the optimal query execution plan based on the sample data.

[0188] Those skilled in the art will understand that Figure 6 The structure shown is for illustrative purposes only. Electronic devices can also be smartphones, tablets, handheld computers, mobile internet devices (MIDs), PADs, and other terminal devices. Figure 6 This does not limit the structure of the aforementioned electronic device. For example, electronic devices may also include components that are more... Figure 6 The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 6 The different configurations shown.

[0189] Those skilled in the art will understand that all or part of the steps in the various database sampling methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0190] Example 4

[0191] Embodiments of this application also provide a storage medium. Optionally, in this embodiment, the storage medium can be used to store the program code executed by the database sampling method provided in Embodiment 1.

[0192] According to another aspect of the present invention, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored computer program, wherein, when the computer program is executed, it controls the device where the computer-readable storage medium is located to execute any of the database sampling methods in the first embodiment described above.

[0193] Optionally, in this embodiment, the storage medium may be located in any computer terminal in a group of computer terminals in a computer network, or in any mobile terminal in a group of mobile terminals.

[0194] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the database sampling method described in various embodiments of this application.

[0195] This application also provides a computer program product, including a non-volatile computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the database sampling method described in various embodiments of this application.

[0196] The sequence numbers of the above embodiments of the present invention are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0197] In the above embodiments of the present invention, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0198] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0199] 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 units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0200] Furthermore, the functional units in the various embodiments of the present invention 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.

[0201] 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 technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part 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 methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, read-only memory (ROM), random access memory (RAM), portable hard drives, magnetic disks, or optical disks.

[0202] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A database sampling method, characterized in that, include: Data on system resource status and hotspot distribution changes are collected based on multiple system monitoring modules; Based on the gradient boosting decision tree model, reasoning is performed on the system resource status and the hotspot distribution change data, and a sampling preference score is output, wherein the sampling preference score is used to determine the sampling timing; The query logic data hotspot distribution model is used to obtain the current hotspot area. The current hotspot area refers to the data area that is accessed more frequently than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data area to be queried. An adaptive sampler is used to generate a targeted sampling plan based on the sampling timing and the current hotspot area. The targeted sampling plan is executed, and the sampled data is output to the query optimizer, wherein the query optimizer is used to select the optimal query execution plan based on the sample data.

2. The database sampling method according to claim 1, characterized in that, The logical data hotspot distribution model includes: The data storage layer uses a hash table to store access statistics. The hash table includes: data entity identifiers corresponding to the data area, metadata statistics, and a metadata list. The data entity identifiers are used as keys, and the metadata statistics and the metadata list are used as values. The statistical calculation module calculates the access frequency and historical estimation error rate of the data entity identifier; The hotspot identification engine identifies hotspot areas based on the access frequency and historical estimation error rate of the data entity identifiers. The hotspot area change rate statistics module calculates the hotspot area change rate based on the dimensional data of multiple change dimensions of the associated hotspot areas within the scheduling and monitoring cycle. The multiple change dimensions include at least: new dimension, disappearance dimension, and attribute change dimension.

3. The database sampling method according to claim 2, characterized in that, The steps for calculating the access frequency and historical estimation error rate of the data entity identifier include: Obtain the number of queries containing the data entity identifier within a first predetermined time period and the first time weight parameter corresponding to the first predetermined time period, wherein the first predetermined time period is the time within a first specified time range from the current time point; Obtain the number of queries containing the data entity identifier within a second predetermined time period and the second time weight parameter corresponding to the second predetermined time period, wherein the second predetermined time period is the duration within a second specified time range from the start time point of the first predetermined time period in the historical process; Obtain the total number of accesses containing all data entity identifiers within the first predetermined time period and the total number of accesses containing all data entity identifiers within the second predetermined time period; The access frequency of the data entity identifier is calculated based on the number of queries containing the data entity identifier within the first predetermined time period and the first time weight parameter corresponding to the first predetermined time period, the number of queries containing the data entity identifier within the second predetermined time period and the second time weight parameter corresponding to the second predetermined time period, the total number of accesses containing all data entity identifiers within the first predetermined time period and the total number of accesses containing all data entity identifiers within the second predetermined time period. Obtain the single relative error rate of the i-th query within the first predetermined time period and the single relative error rate of the j-th query within the second predetermined time period, where i and j are positive integers; Based on the number of queries containing the data entity identifier within the first predetermined time period and the first time weight parameter corresponding to the first predetermined time period, the number of queries containing the data entity identifier within the second predetermined time period and the second time weight parameter corresponding to the second predetermined time period, the single relative error rate of the i-th query within the first predetermined time period and the single relative error rate of the j-th query within the second predetermined time period, the historical estimation error rate of the data entity identifier is calculated.

4. The database sampling method according to claim 2, characterized in that, The steps for calculating the rate of change of hotspot areas based on dimensional data of multiple change dimensions of associated hotspot areas within the scheduling and monitoring period include: In the newly added dimension, the number of newly added hotspot areas in the hotspot area list within the statistical period is obtained to obtain the number of newly added areas, wherein the hotspot area list contains all hotspot areas in the current time period; In the disappearance dimension, the hotspot areas that exited the hotspot area list within the statistical period are obtained, and the number of exiting areas is obtained. In the attribute change dimension, obtain the hotspot areas that are in the hotspot area list within the statistical period but whose core attributes have changed, and obtain the number of attribute change areas. The change rate of the hotspot region is calculated based on the number of newly added regions and the weight parameters corresponding to the newly added dimensions, the number of exited regions and the weight parameters corresponding to the disappeared dimensions, and the number of attribute-changed regions and the weight parameters corresponding to the attribute-changed dimensions.

5. The database sampling method according to claim 2, characterized in that, The logical data hotspot distribution model, when running, also includes: Receive the core elements of the query syntax tree output by the database query collector, and parse them to obtain the data entity identifier and access type; If the data entity identifier already exists in the hash table, the access frequency of the data entity identifier is updated using a time decay algorithm. If the hash table does not contain the data entity identifier, a key-value pair associated with the data entity identifier is added to the hash table, and the initial access frequency of the data entity identifier is set.

6. The database sampling method according to claim 1, characterized in that, The steps for reasoning about the system resource status and hotspot distribution changes based on the gradient boosting decision tree model, and outputting sampling preference scores, include: The data in the system resource status and the hotspot distribution change data are converted into feature vectors. The data types include numerical features and categorical features. The numerical features include at least one of the following: CPU utilization, memory utilization, disk input / output IO utilization, network IO throughput, and hotspot area change rate. The categorical features include at least one of the following: file descriptor usage status and hotspot distribution change level. The feature vectors of the numerical features are normalized, and the feature vectors of the categorical features are one-hot encoded. The processed feature vector is input into the gradient boosting decision tree model, and the sampling preference score is output.

7. The database sampling method according to claim 1, characterized in that, The steps for generating a targeted sampling plan based on the sampling timing and the current hotspot area include: Obtain the historical estimation error rate and access frequency of the data entity identifiers corresponding to the current hotspot area, which are pre-recorded in the hash table of the logical data hotspot distribution model. Based on the regional weight of the current hotspot region, the historical estimation error rate of the data entity identifier corresponding to the current hotspot region, and the access frequency, a first sampling rate is assigned to the current hotspot region. For non-hotspot regions in the hash table other than the current hotspot region, a second sampling rate is assigned to the non-hotspot region based on the historical estimation error rate of the data entity identifier corresponding to the non-hotspot region; The targeted sampling plan is generated based on the first sampling rate of the current hot spot area, the second sampling rate of the non-hot spot area, and the sampling timing.

8. The database sampling method according to claim 7, characterized in that, The steps for executing the targeted sampling plan include: Execute the targeted sampling plan and link the database storage layer; In the database storage layer, data pages are randomly selected, and then row data belonging to the current hot spot area are filtered out from the data pages to obtain sample data. The sample data is stored using a least recently used caching strategy.

9. The database sampling method according to claim 8, characterized in that, After executing the targeted sampling plan, the following is also included: For column combinations in a multi-column joint query, a separate sub-sampling plan is generated, wherein the number of samples for each column combination in the sub-sampling plan is greater than a preset sample number threshold.

10. The database sampling method according to any one of claims 1 to 9, characterized in that, After outputting the sampled data to the query optimizer, the process also includes: The sampled data is output to the cardinality estimator in the query optimizer, which generates a prediction of the number of associated intermediate operation tuples. The comparison error value is obtained by comparing the prediction result of the cardinality estimator with the sample data; The comparison error value is fed back to the logical data hotspot distribution model, which then adjusts the weight parameters of the current hotspot region.

11. A database sampling device, characterized in that, include: The data acquisition unit is used to collect data on system resource status and hotspot distribution changes based on multiple system monitoring modules; The data inference unit is used to infer the system resource status and hotspot distribution change data based on the gradient boosting decision tree model, and output a sampling preference score, wherein the sampling preference score is used to determine the sampling timing; The hotspot area query unit is used to query the logical data hotspot distribution model to obtain the current hotspot area. The current hotspot area refers to the data area that is accessed more frequently than a preset access frequency threshold in the current time period. The access frequency is determined by parsing the elements of all query requests initiated by the query load to obtain the data area to be queried. A sampling plan generation unit is used to generate a targeted sampling plan based on the sampling timing and the current hotspot area using an adaptive sampler. The sampling plan execution unit is used to execute the targeted sampling plan and output the sampled data to the query optimizer, wherein the query optimizer is used to select the optimal query execution plan based on the sample data.

12. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the database sampling method according to any one of claims 1 to 10.

13. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the database sampling method according to any one of claims 1 to 10.

Images