Batch database operation execution sequence optimization method based on time delay detection

Abstract

According to the batch database operation execution sequence optimization method based on time delay detection, random I / O is converted into quasi-sequence I / O, and the execution efficiency of operations such as large-scale deletion and updating is improved. GMM statistical modeling and LLM cognitive analysis are combined to form a dual verification framework, the storage performance level can be adaptively recognized, an abstract statistical peak value can be converted into the insight with clear business semantics, and the analysis depth and accuracy of the method are far better than those of a traditional method. The physical aggregation relationship of the data to be operated can be deduced only by detecting the data with a minimum proportion by adopting a strategy of'intelligent sampling + sequence correlation full-quantity mapping '. The mapping process can be efficiently completed in the database, and minimum disturbance and global optimization coverage of the production system are achieved. And in combination with a cognitive throttling mechanism based on a real-time load, high throughput, low delay and predictability of an optimization process are ensured, and the stability of a core service is ensured.

Description

Technical Field

[0001] This invention relates to the field of database performance optimization technology, and in particular to a batch database operation execution order optimization method based on latency detection, a database operation optimization system, an electronic device, and a non-transitory computer-readable storage medium. Background Technology

[0002] Processing massive amounts of historical data is a common and challenging operational task in the long-term operation of databases. Typical application scenarios include:

[0003] Historical order cleanup on e-commerce platforms: Delete completed orders older than 3 months;

[0004] Periodic archiving of the log system: Update the status of logs older than 7 days to archived;

[0005] Compliant deletion of user data: Deleting all data of a specific user in accordance with compliance requirements;

[0006] ETL process in data warehouse: Batch update of status fields in dimension tables.

[0007] These scenarios share common characteristics: large volumes of data being operated on (typically tens of millions or more), the objects being operated on being determined by business logic (non-contiguous primary keys), and high requirements for execution efficiency and business stability. When such operations involve tens of millions or even billions of rows of data, developers often find that even if the operation command itself is simple (such as DELETE FROM ..WHERE idIN (...)), its execution process is exceptionally slow, and may even lead to database response timeouts, severely impacting online business operations.

[0008] The root cause lies in the fragmentation of physical storage. The logical order of data (such as auto-incrementing primary keys) becomes irrelevant to its physical storage location on disk after frequent additions, deletions, and modifications. Therefore, a batch deletion task executed in primary key ID order degenerates into a large amount of random I / O at the physical level, causing frequent disk head seeks and a sharp decline in performance. The same applies to distributed systems, where I / O is distributed across numerous backend storage servers.

[0009] Traditional performance optimization tools typically rely on accessing the database's internal physical metadata (such as ROWID) to address this issue. However, in modern database operations and maintenance practices, especially in cloud database as a Service (DBaaS) environments, this invention often needs to solve the problem solely through standard SQL interfaces without modifying the database kernel or relying on physical layout information provided by specific database vendors. Therefore, how to resolve the performance bottleneck of batch operations caused by physical fragmentation in this common scenario has become a pressing technical challenge. Summary of the Invention

[0010] To address the technical problems existing in current technologies, this invention provides a performance optimization method for large-scale batch data operations (such as deletion and update). This method, without relying on internal database metadata (a black-box environment), infers the physical data layout by analyzing access latency and reorders operations to improve I / O efficiency. Details are as follows:

[0011] On the one hand, a batch database operation execution order optimization method based on latency detection is provided, which is applied to a black-box database environment. The method includes the following steps:

[0012] a) Obtain a list of data record identifiers to be operated based on business filtering conditions, select a sorting field that can reflect the time order or logical order of data writing, and assign a unique sequence index (seq_index) to each data record identifier in the list according to the order of the sorting field.

[0013] b) Sample a portion of the record identifiers from the data record identifier list as probe samples;

[0014] c) Perform a lightweight query operation on each record identifier in the probe sample and accurately measure its access latency to form a latency dataset containing the record identifier, sequence index and latency;

[0015] d) Analyze the latency dataset to identify at least one physical hot cluster corresponding to a cache hit, wherein the physical hot cluster is identified by a set of probe records that are logically contiguous on the sequence index and whose access latency is at the lowest performance level;

[0016] e) Perform one-dimensional KNN classification on all other unsampled record identifiers in the data record identifier list: calculate the weighted distance between the sequence index of each record identifier and the sequence index of the probe record identifier in the physical hot cluster, and use a multi-round iterative proximity propagation strategy to assign a corresponding cluster category to each record identifier, forming multiple optimized batches with physical proximity.

[0017] f) Submit and execute the corresponding batch database operations to the database according to the priority order of the optimized batches.

[0018] As a preferred embodiment of this application, the sorting field in step a) includes at least one of the following: an auto-incrementing primary key, a creation timestamp, an update timestamp, or a business sequence number field.

[0019] As a preferred embodiment of this application, the lightweight query operation in step c) is executed in a strictly serial manner on a single database connection.

[0020] As a preferred embodiment of this application, the analysis of the time-delay dataset in step d) includes a dual-validation cognitive modeling process, which comprises:

[0021] i. Statistical clustering of the latency dataset is performed using a Gaussian mixture model (GMM) to identify peak values ​​and their threshold values ​​for multiple performance levels.

[0022] ii. Using a Large Language Model (LLM) as a cognitive interpretation engine, the statistical characteristics of the latency dataset are analyzed to independently identify performance levels and provide interpretations of physical meaning;

[0023] iii. Initiate the intelligent arbitration process. When the analysis results of GMM and LLM are inconsistent, decide whether to accept the statistical results of GMM or adopt the correction opinions of LLM based on the peak separation index of the GMM analysis results, so as to finally determine the division of performance levels.

[0024] As a preferred embodiment of this application, the one-dimensional KNN in step e) includes the following corresponding execution steps based on the one-dimensional K-nearest neighbor algorithm:

[0025] When calculating the weighted distance, higher weights are assigned to preceding probes to utilize the database page prefetching mechanism. The preceding probes are probes whose probe sequence index is less than or equal to the target record sequence index.

[0026] ii. A multi-round iterative strategy is adopted, using gradually relaxed distance thresholds for neighbor classification, including direct neighbor classification, neighbor propagation diffusion, and a final classification attempt with a relaxed threshold;

[0027] iii. The distance threshold is adaptively determined based on the probe distribution density to adapt to the actual distribution characteristics of different datasets.

[0028] As a preferred embodiment of this application, the formula for calculating the weighted distance is:

[0029] Weighted distance = |target_record.seq_index - probe.seq_index| / weight factor, where, weight factor = {

[0030] 2.0 If probe.seq_index ≤ target record.seq_index (preceding probe)

[0031] 1.0 If probe.seq_index > target record.seq_index (subsequent probe)

[0032] }

[0033] As a preferred embodiment of this application, before performing one-dimensional KNN classification in step e), the method further includes a sequence correlation self-verification step, which checks whether the probe record identifiers within the identified physical hot clusters exhibit high continuity on the sequence index; if the continuity does not meet the preset conditions, the delay-based optimization process is terminated, and the process reverts to the traditional batch processing mode executed according to the logical order of the sorting field.

[0034] In a preferred embodiment of this application, the one-dimensional KNN classification in step e) is accomplished by executing an SQL query within the database, the process of which includes:

[0035] i. Store the representative sequence index of the identified physical thermal clusters as representative probes in a temporary table;

[0036] ii. By performing a distance-based and sorted SQL update operation within the database, a nearest neighbor representative probe is found for each record identifier in the sequence space.

[0037] As a preferred embodiment of this application, before steps b) and c), the method further includes a security pre-check step, which uses the EXPLAIN command to analyze the execution plan of the operation to obtain the list of data record identifiers. If the plan will trigger a full table scan, the latency-based optimization process is aborted.

[0038] As a preferred embodiment of this application, in step f), the execution step of submitting and executing the corresponding batch database operations to the database according to the priority order of the optimized batches is as follows: the operations are executed according to the priority order from the batches of the corresponding physical hot clusters to the batches of the corresponding physical cold points, and a differentiated throttling control strategy is adopted for batches of different priorities.

[0039] As a preferred embodiment of this application, the method further includes a dynamic re-detection step:

[0040] i. During the execution of the optimized batch, monitor changes in batch execution performance;

[0041] ii. When a performance degradation is detected to reach a preset condition or a preset reprobe trigger condition is met, steps b) to e) are re-executed for the remaining unprocessed data;

[0042] iii. Based on the new detection and analysis results, regenerate the optimized batch and continue execution.

[0043] As a preferred embodiment of this application, the re-probe triggering condition includes at least one of the following: batch execution performance degrades by more than a preset threshold, a preset number of batches are completed, a preset time interval has elapsed, or a significant change in the database cache hit rate is detected.

[0044] As a preferred embodiment of this application, the dynamic reprobing adopts an incremental strategy, sampling and probing only on unprocessed data, so as to reduce the system overhead of reprobing.

[0045] On the other hand, a database operation optimization system is provided, including:

[0046] One or more processors;

[0047] Memory, on which computer-executable instructions are stored;

[0048] The processor is configured to execute the instructions to implement the method described above.

[0049] On the other hand, a non-transitory computer-readable storage medium is provided, on which computer program instructions are stored, which, when executed by one or more processors, cause the processors to perform the method described above.

[0050] The beneficial effects of the technical solutions provided in the embodiments of the present invention include at least the following:

[0051] 1. Significantly Improves Batch Operation Performance and Stability: By transforming random I / O into quasi-sequential I / O, this invention can improve the execution efficiency of large-scale deletion, update, and other operations by several times to an order of magnitude. Simultaneously, by "peak shaving and valley filling" of slow I / O, it significantly reduces the impact of operations on the database, ensuring the stability of core business operations.

[0052] 2. Pure black-box, non-intrusive optimization: This invention does not require access to internal database metadata or modification of table structures. The physical layout of data can be inferred solely through standard SQL queries and client latency observation, making it non-intrusive to existing systems and particularly suitable for access-restricted cloud databases and DBaaS environments.

[0053] 3. In-depth, adaptive physical insights: An innovative dual-validation framework combines GMM statistical modeling with LLM cognitive analysis. This framework not only adaptively identifies 2 to 5 complex storage performance levels, but also transforms abstract statistical peaks into insights with clear business semantics (such as memory, SSD, HDD, and network storage). Its analytical depth and accuracy far surpass traditional methods.

[0054] 4. Highly efficient and low-disturbance global optimization: Employing a "smart sampling + sequence association full mapping" strategy, only a very small percentage (e.g., 0.5%) of the data needs to be probed to infer the physical aggregation relationship of 100% of the data to be operated on. The mapping process can be completed efficiently within the database, achieving minimal disturbance to the production system and global optimization coverage.

[0055] 5. Secure and intelligent execution process: By reorganizing batches according to "physical hot clusters," inefficient random I / O is transformed into efficient quasi-sequential I / O. Combined with a cognitive throttling mechanism based on real-time load, the optimization process ensures high throughput, low latency, and predictability, guaranteeing the stability of core business operations.

[0056] 6. Broad versatility and applicability: This method is not dependent on the implementation details of any specific database. Its core principles are particularly suitable for database systems employing B-tree or similar page-based storage structures, such as MySQL (InnoDB clustered tables) and PostgreSQL (heap tables and B-tree indexes). In these systems, the caching and read-ahead mechanisms of data pages provide a stable and predictable signal foundation for latency-side-channel analysis. Conversely, the direct applicability of this method to databases employing LSM-tree (log structure merge tree) architectures (such as RocksDB and Cassandra) requires careful evaluation, as their read / write paths and data compaction mechanisms differ fundamentally from those of B-trees.

[0057] 7. Dynamic Adaptive Continuous Optimization Capability: Through an optional dynamic reprobing mechanism, this invention can sense and adapt to dynamic changes in the database cache state and physical layout during execution. This adaptive capability enables the system to maintain consistently high performance when processing ultra-large datasets (such as hundreds of millions of records) or long-running tasks, avoiding performance degradation caused by environmental changes.

[0058] 8. Balanced optimization of intelligent cost control: The dynamic reprobing mechanism has a built-in cost-benefit analysis. Reprobing is only initiated when the expected benefit exceeds the probing cost, achieving an intelligent balance between optimization effect and system overhead, ensuring that performance is improved without introducing excessive system burden. Attached Figure Description

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

[0060] Figure 1 This is an example of a sample delay distribution histogram (ASCII) detected in a MySQL experiment, provided by an embodiment of the present invention.

[0061] Figure 2This is a histogram of sample delay distribution detected in a PostgreSQL experiment, provided by an embodiment of the present invention.

[0062] Figure 3 This is a flowchart of a batch database operation execution order optimization method based on latency detection provided by an embodiment of the present invention;

[0063] Figure 4 This is a schematic diagram of a dynamic re-probing process provided in an embodiment of the present invention. Detailed Implementation

[0064] The technical solution of the present invention will now be described with reference to the accompanying drawings.

[0065] In embodiments of the present invention, words such as "exemplarily," "for example," etc., are used to indicate that something is an example, illustration, or description. Any embodiment or design described as "exemplary" in the present invention should not be construed as being more preferred or advantageous than other embodiments or designs. Specifically, the use of the word "exemplary" is intended to present the concept in a concrete manner. Furthermore, in embodiments of the present invention, the meaning expressed by "and / or" can be both, or either one.

[0066] In the embodiments of this invention, the terms "image" and "picture" may sometimes be used interchangeably. It should be noted that, without emphasizing the distinction between them, they convey the same meaning. Similarly, the terms "of," "corresponding (relevant)," and "corresponding" may sometimes be used interchangeably. It should be noted that, without emphasizing the distinction between them, they convey the same meaning.

[0067] In this embodiment of the invention, sometimes a subscript such as W1 may be mistakenly written as a non-subscript form such as W1. When the difference is not emphasized, the meaning they express is the same.

[0068] To make the technical problems, technical solutions and advantages of the present invention clearer, a detailed description will be given below in conjunction with the accompanying drawings and specific embodiments.

[0069] Terminology Explanation:

[0070] 1. Sequential Correlation

[0071] The core theoretical assumption proposed in this invention is that there is a statistically positive correlation between the logical proximity of data records on a selected sorting field and their layout proximity on the physical storage medium.

[0072] Logical Sequence: The order defined by a selected sorting field (such as an auto-incrementing ID, created_at, or other time fields), which is quantized as a unique seq_index in this invention.

[0073] Physical Proximity: refers to the fact that data records are stored in the same or physically adjacent database pages.

[0074] This invention utilizes this correlation to infer the physical distribution of all data based on the physical characteristics of the samples.

[0075] Important Clarification: This invention does not consider "sequence correlation" to be an irrefutable physical law applicable to all databases and scenarios. Instead, this invention defines it as a powerful "working hypothesis" or "spatiotemporal locality heuristic." Its probability of validity varies across different architectures.

[0076] High-Correlation Scenarios: In storage engines that use clustered indexes (such as MySQL InnoDB), the logical order of auto-incrementing primary keys is highly consistent with the physical order, which is assumed to be a strong correlation.

[0077] Statistical-Correlation Scenarios: In heap table structures (such as PostgreSQL) or tables using random primary keys (such as UUID), although the physical location of a single record may appear random, due to the operating system's file system's tendency to allocate contiguous physical blocks for consecutive writes from the same process, and the database's read-ahead and background flushing mechanisms, on a macro scale, a batch of data written around the same time still shows a statistically clustered trend in their physical storage locations.

[0078] Low-Correlation Scenarios: After large-scale deduplication or table reorganization (such as VACUUM FULL), this correlation may be temporarily broken.

[0079] 2. The sequence index (seq_index) (a term used in this invention) is a strictly incrementing integer, starting from 0, assigned to each primary key to be operated on after sorting according to a selected sorting field (such as auto-incrementing ID, created_at, business sequence number, etc.) that can represent the data writing or logical order. It is the quantitative representation of "sequence association" in engineering and serves as a bridge connecting logical order and physical layout.

[0080] 3. Gaussian Mixture Model (GMM) (Statistics)

[0081] A powerful statistical clustering algorithm. In this invention, it is used to mathematically rigorously and adaptively discover multiple performance levels (represented by multiple Gaussian distribution "peaks") in the distribution of time-delay data, and to calculate the optimal segmentation threshold between each level.

[0082] 4. Peak Separation Degree (Statistics)

[0083] This invention proposes a quantitative metric for evaluating the distinguishability between different performance levels (peak values) identified by the GMM model. It is a key criterion for determining the confidence level of the GMM results in the "intelligent arbitration process." The calculation formula is: Separability = |μ_i - μ_j| / ((σ_i + σ_j) / 2).

[0084] 5. Black-box database environment, which is an environment in which the physical metadata inside the database cannot be accessed.

[0085] This invention aims to solve the performance bottleneck problem caused by physical storage fragmentation when performing large-scale, logically disordered batch deletion or update tasks in a black-box database environment.

[0086] This invention primarily addresses the following technical problem: how to discover and utilize the clustering characteristics of data in physical storage solely through externally observable signals (such as access latency) without relying on the internal physical metadata of the database, thereby intelligently reorganizing the operation sequence and transforming inefficient random I / O into efficient quasi-sequential I / O, thus enabling safe and rapid completion of large-scale batch operations?

[0087] Combined with appendix Figure 3-4 As shown, to solve the above technical problems, this invention proposes a physical awareness optimization method for large-scale database operations based on access latency analysis. The specific method consists of the following steps:

[0088] Step 1: Obtaining the dataset to be operated on and constructing the logical sequence

[0089] 1.1 Obtain the list of data identifiers to be operated on

[0090] Based on business requirements, obtain a list of data record identifiers that need to be processed in batches using an SQL query:

[0091] -- Example: Retrieve the unique identifier of the record to be processed based on business conditions

[0092] SELECT [unique identifier field] FROM [target table] WHERE [business filter criteria]

[0093] -- Note: Unique identifier fields are usually primary keys, but can also be unique index fields.

[0094] Filter the primary keys / unique identifiers of records that need to be processed in batches according to business conditions, and form an initial set to be operated.

[0095] 1.2 Select the sorting field and build a logical sequence index (seq_index)

[0096] Choose a field that reflects the time or logical order in which the data was written as the sorting field:

[0097] Preferred fields: Auto-incrementing ID, creation timestamp (created_at), business sequence number, etc.

[0098] Selection principle: This field should have a potential correlation with the physical order in which the data is written.

[0099] The obtained list of data identifiers is sorted according to the sorting field, and then consecutive sequence numbers are assigned:

[0100] -- Generate logical sequence index

[0101] SELECT

[0102] [Unique identifier field],

[0103] ROW_NUMBER() OVER(ORDER BY [sort field] ASC) - 1 AS seq_index FROM (list of data identifiers to be operated on).

[0104] A continuous logical sequence index is created based on the sorting field that can approximately represent the write / logical order, which is then used for subsequent sequence-based sampling, segmentation, and physical clustering inference.

[0105] 1.3 Preparation of Working Dataset

[0106] CREATE TEMPORARY TABLE temp_work_list (

[0107] record_id [identifier field type], -- a unique identifier for a data record.

[0108] seq_index BIGINT, -- Logical sequence index

[0109] PRIMARY KEY (record_id) );

[0111] Construct a temporary working set table to store the records to be processed and their logical sequence indexes, which facilitates subsequent sampling, detection and scheduling without polluting the formal business table.

[0112] Step 2: High-precision time delay detection and signal acquisition

[0113] 2.1 Intelligent Sampling

[0114] A small subset (e.g., 0.5%-1%) is extracted from temp_work_list using a stratified random sampling strategy as the "probe sample set":

[0115] SELECT record_id, seq_index FROM temp_work_list

[0116] TABLESAMPLE BERNOULLI(0.5) -- Randomly sample 0.5% ORDER BY RANDOM();

[0117] The working set is stratified / randomly sampled (approximately 0.5%) to obtain a probe sample set, reducing detection costs and covering different logical locations; the order is then randomly shuffled to avoid systematic bias.

[0118] 2.2 Serial Precision Detection

[0119] On a single database connection, perform a very lightweight read-only query on the record_id of each probe:

[0120] -- Perform a probe query on the record_id of each sample

[0121] SELECT 1 FROM [target table] WHERE [unique identifier field] = ? LIMIT 1

[0122] Perform minimal-cost point lookups on individual sample records to measure actual access round-trip latency, acquiring physical access performance signals without generating data modifications or unnecessary load.

[0123] 2.3 Time Delay Signal Recording

[0124] Accurately record the round-trip time for each query to form a dataset of (record_id, seq_index, latency).

[0125] Step 3: Cognitive Modeling and Hierarchical Threshold Determination Based on Dual Validation

[0126] This is the core innovation of the present invention, which aims to automatically and accurately identify the performance boundaries of different storage media from noisy delay signals. The present invention proposes a dual-validation cognitive modeling framework consisting of statistical models and artificial intelligence models.

[0127] 3.1 Core Model 1: Adaptive Statistical Modeling Based on Gaussian Mixture Model (GMM)

[0128] GMM provides mathematical rigor to the system, enabling it to adaptively discover multiple performance levels within the latency distribution. Its implementation involves the following sophisticated steps:

[0129] 1. Model Assumptions and Iterative Optimization:

[0130] Basic assumption: It is assumed that the observed latency is a mixture of multiple (K) Gaussian distributions, each representing an independent performance level (such as memory, SSD, HDD).

[0131] Iterative fitting: The system iterates over a series of candidate peak counts (e.g., K from 1 to 5) in an automated manner. The goal is to construct and fit a GMM model with K Gaussian components.

[0132] 2. Automatic determination of peak count and model selection:

[0133] Information Criterion Evaluation: For each fitted model (K=1, 2, 3, 4, 5), the system calculates its Bayesian Information Criterion (BIC) and Akaike Information Criterion (AIC) scores.

[0134] Optimal Model Selection: The BIC criterion, due to its stronger penalty for model complexity and its ability to effectively avoid overfitting, is used as the final decision-making basis. The system automatically selects the K value that minimizes the BIC score as the optimal number of peaks. This process is fully automated, requiring no manual intervention, and achieves adaptive discovery of the inherent structure of the data.

[0135] 3. Parameter estimation and engineering implementation details:

[0136] Core Algorithm: The Expectation-Maximization (EM) algorithm is used to iteratively estimate three core parameters for each Gaussian distribution: mean (μ, representing the average latency at that performance level), variance (σ), and variance. 2, representing time delay stability) and mixed weight (π, representing the proportion of this level).

[0137] Key parameter configuration:

[0138] covariance_type='full': Uses a full covariance matrix, allowing each Gaussian component to take on an arbitrary elliptical shape, providing the model with maximum flexibility to adapt to complex real-world data distributions.

[0139] max_iter=100: Set a reasonable upper limit for iterations to ensure that the algorithm converges in a finite amount of time.

[0140] Numerical stability guarantee: In the implementation of the EM algorithm, by adding a small regularization term (such as 1e-6) to the diagonal of the covariance matrix, the singular matrix problem that may be caused by data collinearity is effectively avoided, thus ensuring the numerical stability and robustness of the algorithm.

[0141] 4. Determination of stratified thresholds and quantitative assessment of peak significance:

[0142] Threshold calculation: After the optimal model is determined, the optimal boundary threshold between two adjacent performance levels (i.e. two Gaussian distributions) is analytically obtained from the intersection of their probability density function curves.

[0143] Peak Separation Degree: To quantify the distinguishability between different levels, the system calculates a specific peak separation degree metric, the formula of which is:

[0144] Separation degree = |μ_i - μ_j| / ((σ_i + σ_j) / 2), where i and j are the identifiers of the upper and lower layers, respectively;

[0145] Significance rating: Based on the separation degree calculation results, the system will automatically perform a significance rating to provide a basis for subsequent decision-making.

[0146] Excellent: Separation ≥ 3.0 (virtually no overlap between peaks, highly significant features);

[0147] Good: Resolution ≥ 2.0 (peaks are clearly distinguishable);

[0148] Moderate: Separation ≥ 1.5 (peaks exist but partially overlap);

[0149] Poor: Resolution < 1.5 (insufficient peak resolution).

[0150] 3.2 Core Model Two: A Cognitive Interpretation Engine Based on Large Language Model (LLM)

[0151] LLM provides the system with powerful semantic understanding and expert-level decision-making capabilities, transforming pure numbers into actionable insights. This invention designs a sophisticated cognitive process that includes feature enhancement and two-stage cue words to ensure the depth and accuracy of the analysis.

[0152] 1. Feature-Enhanced Input

[0153] Before invoking LLM, the system performs in-depth preprocessing on the raw latency data to generate a "rich context" report containing multi-dimensional information, rather than simply passing in the raw data. This includes:

[0154] High-resolution statistical features: Calculate high-resolution histograms for up to 23 detailed quantiles (P1, P2, P5...P98, P99) and 50 bins to capture subtle distribution variations.

[0155] Local peak pre-detection: Use signal processing algorithms (such as scipy.signal.find_peaks) to automatically identify candidate peaks and their intensities.

[0156] Distribution feature slices: Provide data samples of "fastest 10%", "slowest 10%" and "middle range" to allow LLM to intuitively perceive the characteristics of the two ends and the center of the distribution.

[0157] Data gap detection: By calculating the difference in latency after sorting, significant gaps in the distribution are automatically identified as candidate boundaries for different storage levels.

[0158] 2. Two-Stage Prompting Strategy

[0159] This invention employs a two-stage prompting strategy. The first stage is used for in-depth analysis and insight generation, while the second stage is used for structured information extraction, thus realizing the transformation from unstructured intelligence to structured data.

[0160] Phase 1: Analytical Prompt

[0161] The goal of this stage is to guide LLMs to think and analyze like domain experts. The prompts consist of the following key components:

[0162] ① Expert Role Definition:

[0163] "You are a world-class expert in storage system performance analysis."

[0164] Proficient in identifying complex data distribution patterns. Please carefully analyze the following database access latency data.

[0165] ② Inject all enhanced features: Provide the LLM with all statistical data, peaks, slices, and gap information generated in step 1. ③ Inject prior knowledge:

[0166] Important Note:

[0167] The access latency of a storage system may exhibit 1-5 different peaks, each peak representing a different storage tier (such as CPU cache, memory, SSD, HDD, network storage, etc.).

[0168] 2. A small percentage of data points with significantly higher latency are often important anomalous access patterns...

[0169] ④ Precise Analytical Instructions:

[0170] Please conduct an in-depth analysis:

[0171] 1. Precise Peak Identification: ...How many distinct peaks can you identify? Please list the approximate location (ms) and percentage of each peak.

[0172] 2. Storage tier inference: What is the possible storage mechanism corresponding to each peak?

[0173] 3. Precise outlier location: ...What should be the precise threshold in milliseconds for abnormal access?

[0174] 4. Multi-level classification scheme: How many levels are recommended for the data? What are the boundary values ​​for each level?

[0175] "

[0176] By posing a series of closed and specific questions, LLM is forced to engage in quantitative analysis rather than vague qualitative descriptions.

[0177] Phase Two: Structured Prompt Extraction

[0178] The first stage outputs a detailed analysis report in natural language format. To allow the program to directly utilize these insights, second-stage prompts are designed for information extraction and formatting.

[0179] ① Extraction Task Definition: "Please extract structured information from the following analyzed text and return it in JSON format:"

[0180] ② Inject the text to be extracted: Use the full text of the analysis report generated by the LLM in the first stage as input.

[0181] ③ Define the target JSON schema:

[0182] {

[0183] "peak_count": "<the exact number of identified peak values ​​(integer)>",

[0184] "peak_details": [

[0185] {

[0186] "peak_number": 1,

[0187] "position_ms": "<peak position>",

[0188] "estimated_percentage": "<Percentage of this peak value>",

[0189] "significance": "<major / minor / weak>"

[0190] }

[0191] ],

[0192] "layer_thresholds": " []",

[0193] "anomaly_threshold": "<outlier threshold>", ...

[0195] }

[0196] It provides a clear template, which greatly improves the accuracy and stability of the LLM output format.

[0197] ④ Strict formatting instructions:

[0198] "Important: peak_count must be a specific integer... Only return JSON, no other text."

[0199] It can also be achieved through standard interface parameters.

[0200] response_format={

[0201] 'type': 'json_object'

[0202] }

[0203] Through this sophisticated process, this invention transforms LLM from a general-purpose language tool into an integrable, predictable, and highly specialized database performance analysis engine.

[0204] 3.3 Dual Authentication and Intelligent Arbitration Decision-Making Process

[0205] The statistical rigor of GMM and the cognitive intelligence of LLM are integrated here to form a deterministic decision-making process, rather than a simple comparison of results. This process follows the following priority rules:

[0206] Step 1: Concordance Check

[0207] The system runs GMM and LLM analyses in parallel. If the number of major peaks identified by both analyses is consistent and the peak locations are highly similar (e.g., relative error < 5%), the result is marked as "high confidence" and directly adopted. This represents the best-case scenario for double validation.

[0208] Step Two: Discordance Arbitration

[0209] When the results of GMM and LLM are inconsistent, the system initiates intelligent arbitration. The core idea is to use objective statistical indicators to determine when to trust the statistical model and when to adopt the "correction opinion" of the cognitive model.

[0210] 1. Assessing the confidence level of the GMM: The system first checks the "Separation Degree" metric of the GMM analysis output. This metric quantifies the distinguishability between adjacent peaks identified by the GMM.

[0211] Strong signal: "Excellent" or "Good" separation (e.g., > 2.0) indicates that the data distribution is clear and the results of GMM have high statistical confidence.

[0212] Weak signal: "Medium" or "poor" separation (e.g., < 1.5) indicates that the peaks are statistically highly overlapping and the distinction of the GMM is ambiguous or uncertain.

[0213] 2. Decision-making rules:

[0214] Rule A: When the GMM signal is strong, the GMM signal is given priority.

[0215] Scenario: GMM identified 3 peaks with good separation, while LLM may have oversimplified and only reported 2.

[0216] Decision: Adopt the 3-peak result of GMM.

[0217] Reason: When data features are clearly identifiable, mathematical models are more accurate than LLMs, which may be subject to "cognitive bias." This prevents the "illusion" that LLMs can create with clear data.

[0218] Rule B: When the GMM signal is weak, LLM correction is allowed.

[0219] Scenario: GMM barely identified two peaks with extremely poor separation (almost overlapping), while LLM's cognitive analysis believed that they were actually the same storage level, just with a slightly wider distribution.

[0220] Decision: Adopt the peak value judgment of LLM.

[0221] Reason: The “expert experience” of LLM comes into play here, making a more robust judgment on statistical ambiguity that is more in line with engineering practice (e.g., merging peaks that are split due to noise).

[0222] Step 3: Final Fallback

[0223] In rare cases, such as when both GMM and LLM show high confidence in their respective conflict outcomes, the arbitration rule cannot resolve the conflict. In this situation, the system will mark the analysis as "high ambiguity" and automatically abort the latency-based optimization process, reverting to the traditional, primary key-sorted secure batch processing mode, while logging for manual review.

[0224] Through this structured process, the combination of GMM and LLM is no longer a simple "voting" process, but a more intelligent and reliable integration mechanism with clear priorities and decision-making criteria.

[0225] The "probe and modeling" of this invention is not only for measuring latency, but its deeper strategic significance lies in the fact that it is a real-time, data-driven "self-validation" process for the sequence association hypothesis against the current target table.

[0226] Verification implementation method: After modeling the latency data, the system will perform a correlation check: whether the probe members inside the identified "physical thermal clusters" or "physical temperature clusters" also show a high degree of continuity in the seq_index sequence?

[0227] Two possible outcomes:

[0228] i. Verification successful (correlation exists): If the system detects "hot spots" with extremely low latency, their seq_index will also be clustered in...

[0229] Within one or more consecutive intervals. This provides real-time and strong proof that, for the current table, at the current point in time, the "sequence association" hypothesis holds. Only then will the system proceed to the subsequent "full mapping" and "optimized batch generation" steps.

[0230] ii. Validation Failure (No Association): If the system finds that the time delay distribution is random, or that the seq_index of "hot spots" is uniformly distributed across the entire interval [0, N-1] without any clustering characteristics, this proves that for the current table, the sequence association is either nonexistent or extremely weak.

[0231] Enhanced safety rollback mechanism: In the event of such "verification failure", the system will automatically abort the sequence association-based recombination optimization and roll back to the traditional, primary key sorted (or original list order) safe batch processing mode.

[0232] 3.4 Transformation from Multimodal Model to Hierarchical Clustering Strategy

[0233] The dual verification model of this invention not only identifies peak values, but more importantly, it transforms these peak values ​​into an executable hierarchical clustering strategy, thereby fully transmitting the depth of analysis to the optimization execution stage.

[0234] 1. Performance Level Definition: Based on the N peaks identified by GMM / LLM, this invention automatically labels the probe samples into multiple performance levels. For example, in a typical 4-peak model:

[0235] Peak 1 (fastest, such as 0.5ms) -> marked as "Physical Hot Cluster", representing the fastest cache hit (such as InnoDB Builder Pool).

[0236] Peak 2 (second fastest, such as 1.5ms) -> marked as "Physical Warm Cluster", indicating a secondary cache hit (such as OS File Cache).

[0237] Peak 3 (slower, such as 15ms) -> marked as "Physical Cold Point", representing regular disk or network storage reads.

[0238] Peak 4 (slowest / outlier, such as 30ms+) -> also marked as "physical cold spot" and can be selectively isolated for analysis.

[0239] 2. Strategy Mapping: This hierarchical result directly guides subsequent batch generation. The goal of this invention is no longer simply to create an "optimized batch," but to create multiple types of optimized batches with priorities.

[0240] Step 4: Full mapping and priority batch generation based on hierarchical sequence association

[0241] This step is essentially a K-nearest neighbor classification algorithm in a one-dimensional sequence space (seq_index) that intelligently groups global data based on their proximity to known "physical hotspots".

[0242] 4.1 Establishing the basic classification points (training set)

[0243] All probe records in the "physical thermal clusters" and "physical temperature clusters" identified in step three are used as labeled training samples:

[0244] Each probe has a defined coordinate: seq_index (position).

[0245] Each probe has a specific label: cluster_type (HOT / WARM / COLD)

[0246] 4.2 Multi-round Iterative Optimization of One-dimensional KNN Classification

[0247] To fully utilize physical locality, a multi-round iterative neighbor propagation strategy is adopted:

[0248] Round 1: Direct Proximity Classification

[0249] For each unlabeled record, calculate its one-dimensional distance to all labeled probes: |record.seq_index - probe.seq_index |;

[0250] A weighted distance function is applied, with higher weights for preceding probes (which can trigger read-ahead).

[0251] weight = {

[0252] 2.0 if probe.seq_index ≤ record.seq_index (preceding probe, read-ahead friendly)

[0253] 1.0 if probe.seq_index > record.seq_index (subsequent probes, standard weights)

[0254] },

[0255] effective_distance = distance / weight.

[0256] Select the K nearest neighbor probes with the smallest effective distance (usually K=3-5);

[0257] If the effective distance to the nearest neighbor is less than the threshold T1 (e.g., the difference in seq_index is <1000), then inherit its cluster_type in the second round: neighbor propagation diffusion.

[0258] Records newly classified in the first round are also used as "secondary seed points";

[0259] For records that are still unclassified, repeat the KNN process, but use a stricter distance threshold T2 (e.g., T2 = T1 / 2).

[0260] This round mainly targets "warm areas surrounding hot spots".

[0261] Round Three: Conservative Classification to Conclude

[0262] For the remaining unclassified records, use the most lenient threshold T3 for a final round of classification.

[0263] If the effective distance between the record and the nearest probe is ≤ T3, then the probe category (HOT / WARM) is inherited.

[0264] If the distance is greater than T3, it will be classified as a COLD batch.

[0265] 4.3 Priority Batch Generation Based on Classification Results

[0266] After multiple rounds of one-dimensional KNN classification, the original unordered list was reconstructed into hierarchical batches with clear physical semantics:

[0267] P1 - Hot Batches: All records classified as HOT are sorted in ascending order by seq_index and divided into multiple consecutive sub-batches;

[0268] P2 - Warm Batches: All records classified as WARM are also grouped in order by seq_index; P3 - Cold Batches: Records that could not be classified or were classified as COLD are grouped in smaller batches.

[0269] 4.4 Determination of the adaptive distance threshold for the algorithm

[0270] The distance threshold is not a fixed value, but is adaptively calculated based on the probe distribution density:

[0271] Average probe spacing = median([abs(probe [i+1].seq_index - probe [i].seq_index) for all adjacent probes; T1 = average probe spacing * 2.0 (allowing moderate gap jumps);

[0272] T2 = Average probe spacing * 1.0 (strict proximity requirement);

[0273] T3 = Average probe spacing * 4.0 (least lenient catch-all classification).

[0274] Step 5: Priority- and Load-Aware Adaptive Execution

[0275] The execution engine strictly follows the order of P1 -> P2 -> P3 for scheduling.

[0276] 1. Execute by priority: First, execute all "hot batches," then all "warm batches," and finally process "cold batches." This order ensures that most of the task's progress is completed in the most efficient mode.

[0277] 2. Differentiated Throttling Control: The cognitive throttling controller employs different strategies for batches of different priorities.

[0278] For P1 hot batches and P2 warm batches, a larger batch size and a shorter execution interval can be used to pursue maximum throughput.

[0279] For P3 cold batches, the system automatically uses smaller batch sizes and longer execution intervals (sleep). This achieves "peak shaving and valley filling" of random I / O, minimizing the impact on the database, perfectly handling abnormal slow access points, and ensuring the stability of core business operations.

[0280] Step Six: Dynamic Reprobing and Adaptive Optimization (Optional Enhancement Mode)

[0281] When dealing with large datasets or long-running optimization tasks, the system can initiate a dynamic reprobing mechanism to address changes in cache state and physical layout during execution.

[0282] 6.1 Re-probe triggering conditions

[0283] The system may selectively trigger reprobing in the following situations:

[0284] Batch quantity trigger: After each hot batch of a preset quantity (e.g., 5-10) is completed;

[0285] Time interval trigger: Triggered after a preset time interval (e.g., 30 minutes);

[0286] Performance degradation trigger: When a significant degradation in batch execution performance is detected (e.g., an increase in average latency of more than 50%);

[0287] Cache state change trigger: When the system detects a significant change in the database cache hit rate.

[0288] 6.2 Incremental Reprobe Strategy

[0289] To avoid the overhead of full reprobing, the system adopts an incremental strategy:

[0290] Remaining data sampling: Only perform a new round of sampling and probing on data that has not yet been processed;

[0291] Probe Update: Based on new detection results, update or add physical thermal cluster probes;

[0292] Batch regrouping: Reclassify and prioritize the remaining data to be processed.

[0293] 6.3 State Maintenance and Recovery

[0294] During the re-detection process, the system will:

[0295] Save progress: Record completed batches and current optimization status;

[0296] Rollback guarantee: If reprobing fails, it can roll back to the previous stable state and continue execution.

[0297] To describe in detail the implementation process and principle of the above technology, the following detailed description will be provided in conjunction with the embodiments.

[0298] Example 1: Cross-database empirical study of the effectiveness of core technologies

[0299] To verify the effectiveness and universality of the core technology of this invention (latency multi-peak feature identification and access locality benefits), this invention has undergone rigorous empirical testing on two mainstream databases, MySQL and PostgreSQL.

[0300] Experimental environment:

[0301] Database: MySQL 8.0 / PostgreSQL 14.

[0302] Hardware and storage: 4-core CPU, 4GB memory, and multiple HDDs mounted via NFS to simulate remote I / O scenarios (cloud disks).

[0303] Test table: A log table containing 5 million records, with an auto-incrementing ID as the primary key, and then 5% random insertions and deletions are introduced.

[0304] Experiment 1: Verification of the multi-peak characteristics of time delay distribution

[0305] Process: 800 IDs are randomly sampled from the table, their precise latency is recorded through serial lightweight query, and the dual verification modeling framework of this invention is applied for analysis.

[0306] MySQL Experiment Results:

[0307] Baseline noise assessment (baseline calibration, sample optimization):

[0308] Before the formal probing, 50 queries were performed against invalid primary keys (e.g., pk = -1 ..-50) to accurately measure pure network round trip and server-side processing latency. This provided a noisy baseline for subsequent analysis.

[0309] Baseline noise (network + server basic processing):

[0310] Mean: 0.166 ms;

[0311] Standard deviation: 0.017 ms;

[0312] Figure 1 This is a histogram of the time delay distribution of 800 samples detected in the MySQL experiment, clearly showing the multi-peak distribution characteristics. The results indicate that:

[0313] Statistical analysis using GMM revealed that the system automatically selected K=4 as the optimal model and identified four Gaussian components, which manifested as three main access levels plus one outlier group.

[0314] Ultra-fast access layer (memory page cache): mean 0.583ms (weight 18.4%);

[0315] Mainstream access layer (regular cache): Average 0.774ms (weight 63.7%);

[0316] Medium-speed access layer (partial cache hits): Average 0.955ms (weight 17.2%);

[0317] Slow access (cache miss / disk I / O): Average 1.188ms (weight 0.7%);

[0318] Peak separation: 5.38 and 6.58, both reaching the "excellent" level, proving that the distinction between each level is obvious.

[0319] The system uses the GaussianMixture algorithm from the Python scikit-learn library to automatically fit a Gaussian mixture model using gmm.fit(latency_data) and selects the optimal number of components using the BIC information criterion.

[0320] LLM cognitive analysis: After independent analysis using the DeepSeek V3 model, LLM also identified three main peaks (0.592ms, 0.776ms, 0.96ms) and inferred them as "L2 cache level", "main memory level" and "disk level" respectively, which is highly consistent with the GMM results (position error < 0.01ms).

[0321] PostgreSQL Experiment Results:

[0322] Baseline noise assessment:

[0323] Similar to the MySQL experiment, multiple rounds of baseline measurements were first performed on PostgreSQL to assess the inherent latency of the network and server. The data showed that the measurement environment was stable.

[0324] Baseline mean values ​​for each round: ['0.800ms', '0.783ms', '0.776ms'];

[0325] Coefficients of variation for each round: ['0.093', '0.066', '0.061'];

[0326] Inter-wheel stability (CV): 0.015.

[0327] Compared to MySQL, PostgreSQL exhibits a significant long tail (~10ms). After removing extreme long-tail values, the remaining samples show a very clear multi-modal shape (e.g., Figure 2 (as shown)

[0328] Both GMM and LLM can detect long tails, but for the sake of illustration, the long tail portion is not shown here (otherwise the diagram would be very large).

[0329] GMM statistical analysis: The system automatically selects K=3 as the optimal model and identifies 3 Gaussian components, which are represented by 2 main access levels plus 1 extreme outlier group.

[0330] Fast access layer (shared_buffers hit): Mean 1.329ms (weight 62.8%);

[0331] Regular access layer (file system cache): mean 1.911ms (weight 37.1%);

[0332] Extremely slow access (real disk I / O): Average 10.292ms (weight 0.1%);

[0333] Peak separation: 3.67 and 84.66, reaching the "excellent" level, with extremely significant features.

[0334] LLM cognitive analysis: LLM also identified three peaks (1.258ms, 1.817ms, 10.292ms) and inferred them as "L1 / L2 cache", "main memory" and "ultra-slow layer", which is completely consistent with the GMM results.

[0335] Conclusion: Cross-database experiments strongly demonstrate that the time-delay side-channel signal is real and measurable, and that its complex multi-layered structure can be accurately captured and utilized by the dual-verification modeling framework of this invention.

[0336] Experiment 2: Validation of the benefits of locality in data access

[0337] Experimental Objective: The core objective of this experiment is to verify the upper limit of the theoretical performance gain that the "operational recombination" mechanism of this invention can bring under the assumption of "sequence correlation".

[0338] The experimental environment is the same as that of Experiment 1.

[0339] Process: To clearly verify the effectiveness of the core strategy of this invention (i.e. batch reassembly), this experiment uses lightweight read-only queries to simulate the I / O access patterns before and after optimization.

[0340] Using read-only simulation instead of direct deletion is to avoid changes to the physical layout of the data caused by the DML operation itself, thereby ensuring that the comparative experiments are based on the exact same table state and accurately isolate the performance impact caused by the difference in access mode.

[0341] The following two methods are compared in detail:

[0342] Sequential ID Access: This mode represents the optimized execution method of this invention. It accesses a batch (8) of IDs in physically consecutive order, simulating efficient operation on a "physical hot cluster".

[0343] Random ID Access: This mode represents a traditional, unoptimized execution approach. It accesses a batch (8) of IDs in logical order (e.g., random sampling), simulating random I / O on fragmented storage.

[0344] MySQL experimental results (see Table 1 below): The experiment verifies the performance improvement after batch reorganization by comparing "continuous ID access" (after simulation optimization) and "random ID access" (before simulation optimization). All latency data have been reduced by the mean of baseline noise.

[0345] Table 1 --- Comparison of Net MySQL Access Latency (Baseline Subtracted)

[0346] First-hit Subsequent improve Continuous ID Group 0.453ms 0.067ms 6.76x Random ID group 0.441ms 0.411ms 1.07x

[0347] Results analysis: Subsequent accesses to consecutive ID groups (0.067ms) were 6.76 times faster than the first access (0.453ms), demonstrating a strong cache prefetching effect. However, subsequent accesses to random ID groups showed almost no performance improvement (only 1.07 times). This indicates that the performance improvement potential brought by this invention through batch reorganization is 6.32 times (6.76x / 1.07x) of random access.

[0348] Experimental results in PostgreSQL (see Table 2 below): The same locality benefit verification was performed in the PostgreSQL environment, and the mean of the baseline noise was subtracted from all latency data.

[0349] Table 2 --- Comparison of Net Access Latency for PostgreSQL (Baseline Subtracted)

[0350] First-hit Subsequent improve Continuous ID Group 0.759ms 0.319ms 2.38x Random ID group 0.765ms 0.753ms 1.02x

[0351] Results analysis: Subsequent accesses to consecutive ID groups (0.319ms) were 2.38 times faster than the first access (0.759ms). Random ID groups, however, showed almost no caching benefit (1.02x). This demonstrates that the optimization strategy of this invention also delivers a significant performance improvement of 2.33x (2.38x / 1.02x) on PostgreSQL.

[0352] Conclusion: This experiment directly verifies the great value of the core strategy of this invention—reorganizing random access into quasi-sequential access that utilizes physical locality can trigger the database caching mechanism, resulting in a performance improvement of several times.

[0353] Example 2: Large-scale historical data deletion based on external lists

[0354] This embodiment aims to demonstrate how the present invention can be applied to a common and real-world scenario: performing large-scale historical data deletion on a table using an auto-incrementing ID as the primary key, based on an unordered list provided by an external system. This clearly demonstrates how the present invention can reconstruct a logical sequence from unordered input through simple preprocessing steps and apply its core optimization process.

[0355] Scene description:

[0356] Data source: The orders table of an e-commerce platform, with an auto-incrementing ID as the primary key.

[0357] Task: Delete historical orders filtered by an external data warehouse system using complex business logic, in accordance with data compliance requirements. Source of the list: The list of order IDs to be deleted is pre-generated and provided by the external system; it is an unordered list of IDs.

[0358] Core challenges:

[0359] Although the externally provided ID list is an auto-incrementing ID, the order in which they appear in the list is random, making it impossible to directly apply the core "sequence association" analysis of this invention.

[0360] An efficient way is needed to establish a logical order (seq_index) for this unordered list, while avoiding polluting the database cache before analysis.

[0361] Solutions and steps (e.g.) Figure 3 (as shown)

[0362] Step 1: Receive the external manifest and build the serialization context (completed within the database).

[0363] The goal of this step is to efficiently transform the unordered list of external IDs into an internally ordered working set with a seq_index that can be used for analysis.

[0364] Instructions sent by the client:

[0365] -- 1. Create a temporary table to store an externally provided list of unordered IDs.

[0366] CREATE TEMPORARY TABLE temp_delete_candidates (id BIGINT PRIMARYKEY);

[0367] -- 2 (Illustration) Batch import IDs to be deleted from external sources (such as files)

[0368] -- COPY temp_delete_candidates(id) FROM 'path / to / ids.csv ' WITH(FORMAT csv);

[0369] -- This step is a pure data loading process that does not access the main orders table, so it will not pollute the cache.

[0370] -- 3. Create a new temporary table containing a sequence index.

[0371] -- Generating seq_index directly during the import and sorting process is the most crucial simplification step.

[0372] CREATE TEMPORARY TABLE temp_delete_candidates_sequenced AS

[0373] SELECT

[0374] id,

[0375] ROW_NUMBER() OVER(ORDER BY id ASC) - 1 AS seq_index

[0376] FROM

[0377] temp_delete_candidates;

[0378] -- 4. Create an index for the sequence index to provide high-speed support for the subsequent "full mapping" step.

[0379] CREATE INDEX idx_temp_seq_index ON temp_delete_candidates_sequenced(seq_index);

[0380] Result: An ordered temporary table containing `id` and `seq_index` was obtained from the database. `seq_index` now correctly reflects the logical order of the primary keys, laying the foundation for subsequent analysis.

[0381] Step 2: Sampling, Probe, and Physical Thermal Cluster Representative Probe Identification (Client-Database Interaction)

[0382] Sampling: The client randomly samples 0.5% of the probes from the temp_delete_candidates_sequenced table and obtains their id and seq_index.

[0383] Probe: The client performs a serial SELECT 1 FROM orders WHERE id = ? probe on these probe IDs to measure latency.

[0384] Identifying representative probes and self-verification: The client performs latency modeling and clustering in memory to identify multiple "physical hot clusters." Then, it performs sequence association self-verification: checking whether the seq_index of members within these hot clusters also exhibits high continuity. Only when the self-verification is successful, proving that there is an exploitable association between the primary key ID order and the physical layout, does it proceed to the next step.

[0385] Step 3: Full mapping based on sequence association (completed within the database)

[0386] The "physical hot cluster representative probe" information identified by the client is transmitted back to the database, and the attribution mapping of all data is efficiently completed within the database.

[0387] 3.1 Represents probe information feedback

[0388] The client stores the representative probe information (including cluster identifiers and corresponding sequence index positions) obtained from the detection and analysis into a temporary table in the database to establish the mapping basis.

[0389] 3.2 SQL Implementation of the One-Dimensional KNN Mapping Algorithm

[0390] By performing distance calculations and sorting operations within the database, find the nearest neighbor representative probe in the sequence space for each record to be operated on:

[0391] -- Core mapping logic example

[0392] UPDATE working dataset

[0393] SET cluster_id = (

[0394] SELECT probe table.cluster_id

[0395] FROM probe table

[0396] ORDER BY calculates the weighted distance function (current record.seq_index, probe table.seq_index)

[0397] LIMIT 1).

[0398] The weighted distance function prioritizes preceding representative probes (probes that can trigger the pre-read mechanism) and sets a distance threshold to control the strictness of classification.

[0399] 3.3 Multi-round iterative mapping

[0400] By executing the above mapping query multiple times and using gradually relaxed distance thresholds, multi-round classification from strict proximity to loose proximity is achieved, maximizing the use of physical locality.

[0401] 3.4 Priority Batch Generation

[0402] After mapping is complete, the original list is reconstructed into hierarchical batches with physical semantics based on cluster_id: hot batch (highest priority), warm batch (medium priority), and cold batch (lowest priority). Within each batch, the batches are ordered by seq_index to maintain the continuity of access.

[0403] Step 4: Execute in optimized batches (client-database interaction)

[0404] The client can now simply cycle through cluster_ids to issue batch delete commands.

[0405] The client sends commands in a loop:

[0406] -- The client first retrieves all unique cluster_ids

[0407] -- SELECT DISTINCT cluster_id

[0408] -- FROM temp_delete_candidates_sequenced ORDER BY cluster_id;

[0409] -- Perform cyclic deletion on each cluster

[0410] FOR each_cluster_id IN [1, 2, ...]:

[0411] DELETE FROM orders

[0412] WHERE id IN (

[0413] SELECT id FROM temp_delete_candidates_sequenced

[0414] WHERE cluster_id = each_cluster_id );

[0416] Load monitoring and dynamic throttling logic are added here.

[0417] Conclusion: Through the above process, this invention successfully transforms a completely unordered ID list deletion task from an external source into a series of physically highly continuous, quasi-sequential deletion operations that leverage the primary key's "sequence association" and are extremely friendly to database caching. This embodiment demonstrates how this invention handles unordered input and restores the logical order through simple preprocessing steps (importing and sorting), thereby applying its core optimization logic and proving its flexibility and practicality in real-world external integration scenarios.

[0418] Example 3: Intelligent rollback in the "Optimization Not Applicable" scenario

[0419] Objective: To prove that the "safety pre-inspection and rollback mechanism" of the present invention is true and effective, and to demonstrate the completeness of the solution in decision-making.

[0420] Scene description:

[0421] Data status: A core business table, which has just undergone a full table scan for data analysis, has had its data pages fully loaded into the database's memory cache (such as InnoDB's Buffer Pool or PostgreSQL's SharedBuffers).

[0422] Task: Perform a batch delete on this table based on a time range.

[0423] System execution process and decision-making:

[0424] 1. Pre-detection and detection: The system obtains the list of primary keys to be deleted according to the standard procedure and performs latency detection.

[0425] 2. Analysis of Detection Results:

[0426] Latency distribution: Since all data pages are in memory, the detected latency exhibits a very narrow single-peak distribution, with all access latency concentrated at a very low “memory access” level (e.g., around 0.5ms in MySQL).

[0427] Modeling and Validation: Both the GMM model and the LLM cognitive engine consistently and with high confidence identified only one performance tier. The LLM analysis report explicitly states: "No significant storage tiers were found; all samples exhibited cache access characteristics."

[0428] 3. Intelligent decision-making:

[0429] Benefit Assessment: Based on the model results, the system determines that the current physical data layout is not a performance bottleneck, and all data is already in an optimal memory access state. Therefore, performing batch reorganization based on physical location offers almost no benefit.

[0430] Automatic rollback: The system decision engine automatically aborts the latency-based reordering optimization process and rolls back to the traditional, safe batch processing mode that follows the primary key logical order.

[0431] Conclusion: This embodiment demonstrates another aspect of the "intelligence" of this invention: not only in "when to optimize," but also in "when not to optimize." Through precise time delay signal analysis, the system can effectively identify scenarios unsuitable for optimization, avoiding unnecessary operational overhead, and demonstrating its maturity and reliability in a real production environment.

[0432] Example 4: General applicability to other DML operations (such as UPDATE)

[0433] Objective: To demonstrate that this invention is a general optimization framework, and its core ideas are also applicable to other large-scale DML operations besides DELETE, such as UPDATE.

[0434] Briefly: The optimization process of this invention is decoupled from the specific DML operation types (DELETE, UPDATE, etc.) performed. For a large-scale UPDATE task, such as archiving historical orders according to a time range (UPDATE ordersSET is_archived = 1 WHERE ..), the optimization process is exactly the same as the aforementioned deletion embodiment:

[0435] 1. Obtain the list: First, obtain the primary key and sorted sequence of all records to be updated, and create seq_index.

[0436] 2. Probe and Map: Similarly, perform latency detection, modeling, physical hot cluster identification and full mapping, and assign a cluster_id to each primary key to be updated.

[0437] 3. Perform optimization operations: Finally, perform UPDATE operations in a loop by cluster_id instead of DELETE.

[0438] Conclusion: By simply replacing the final executed DML statements, the capabilities of this invention can be seamlessly extended from batch deletion to batch updates and other scenarios. This demonstrates its broad applicability and high scalability as a general "physical optimization framework for large-scale DML operations".

[0439] Example 5: Dynamic Reprobing and Adaptive Optimization in Ultra-Large-Scale Data Processing

[0440] Scene description:

[0441] Data scale: A financial institution needs to archive a historical table containing 500 million transaction records;

[0442] Task complexity: The estimated processing time exceeds 8 hours, involving the execution of thousands of batches;

[0443] Dynamic environment: During processing, the database may take on other business loads, and the cache status may change.

[0444] Analysis of the necessity of dynamic re-detection:

[0445] In such a large-scale, long-term processing process, the initial latency analysis results may gradually become invalid for the following reasons:

[0446] 1. Cache warm-up effect: The execution of early batches warms up the relevant data pages, changing the latency characteristics of subsequent accesses;

[0447] 2. Cache capacity limit: Processing large amounts of data may cause early-loaded hot data to be evicted from the cache;

[0448] 3. Impact of concurrent services: Concurrent access from other services will change the overall cache state of the database;

[0449] 4. Changes in physical layout: A large number of deletion operations may trigger the database's background cleanup process.

[0450] like Figure 4 As shown, the dynamic reprobing execution flow is as follows:

[0451] First round of optimization (0-2 hours):

[0452] 1. The system completes the initial detection according to the standard procedure, identifies the physical heat clusters, and begins execution;

[0453] 2. Successfully processed the first 1000 hot batches, with performance meeting expectations.

[0454] First re-detection (2-hour time point):

[0455] 3. The system detected that the average execution latency of the most recent 100 batches increased from 0.8ms to 1.2ms, triggering a performance degradation alarm;

[0456] 4. Initiate re-probing: Resample 2 million records from the remaining 400 million records for re-probing;

[0457] 5. Significant changes were observed in the time delay distribution:

[0458] Original hot cluster area: latency increased from 0.5ms to 0.9ms (cache eviction effect); New hot cluster area: a new 0.4ms hot spot area was discovered (cache warm-up effect).

[0459] Adaptive recombination (2-4 hours):

[0460] 6. The system regenerates optimized batches based on the new detection results;

[0461] 7. Reclassify the remaining data into the newly identified hot clusters;

[0462] 8. Execution performance has recovered to expected levels: average latency has dropped back to 0.7ms.

[0463] Second re-detection (6-hour time point):

[0464] 9. If performance changes are detected again, initiate a second round of re-probing;

[0465] 10. It was discovered that the overall latency pattern changed again due to increased batch processing load at night;

[0466] 11. The system adaptively adjusts batch size and execution interval to reduce the impact on other services while maintaining efficiency.

[0467] While this invention provides a general and powerful optimization paradigm, its effectiveness may be limited by certain objective conditions under specific technical architectures and workloads. Clearly defining these boundaries is key to ensuring that this method delivers its maximum value in appropriate scenarios.

[0468] 1. Limited applicability to LSM-tree architecture: For databases employing Log-Structured Merge Tree (LSM-tree) architectures (such as Cassandra and RocksDB), the core assumption of this invention, "sequence association," may be weaker. LSM-trees use an in-memory write buffer (MemTable) and immutable SSTable files on disk for writing and merging, which means that data written at logically similar times may be scattered across different SSTable files. While its background compaction process reorganizes the data, the final physical layout is not as directly correlated with the write timestamp as it is with the B-tree page storage structure.

[0469] 2. Impact of Large-Scale Data Reorganization: When database tables undergo physical reorganization that is unrelated to the logical order (such as `created_at`) upon which this invention relies, the core assumptions may be broken. For example, performing a table rewrite operation sorted by the user name field (such as the PostgreSQL `CLUSTER` command) will completely decouple the `created_at` order from the physical order. It is worth noting that the "sequence association self-verification" step built into this invention can effectively identify this situation: if the detection finds that the `seq_index` of latency hotspots is randomly distributed, the system will automatically fall back to the traditional mode, thereby ensuring security.

[0470] 3. Challenges of High-Concurrency Dynamic Workloads: The latency detection process of this invention is a "snapshot" of the database's physical layout. In dynamic environments with extremely high concurrency and very frequent random insertion and deletion operations, the physical layout of the data can change rapidly in a short period of time. This means that the physical hot clusters identified in the "detection" phase may no longer be physically contiguous by the time of the "execution" phase. Therefore, this method works best in environments with a relatively stable physical layout (e.g., during maintenance windows, or for tables that are primarily append-only writes and reads).

[0471] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A batch database operation execution order optimization method based on latency detection, applied to a black-box database environment, characterized in that, The method includes the following steps: a) Obtain a list of data record identifiers to be operated based on business filtering conditions, select a sorting field that can reflect the time order or logical order of data writing, and assign a unique sequence index (seq_index) to each data record identifier in the list according to the order of the sorting field. b) Sample a portion of the record identifiers from the data record identifier list as probe samples; c) Perform baseline calibration on the probe sample, subtract network baseline latency, perform a lightweight query operation on each record identifier in the calibrated probe sample, and measure its access latency to form a latency dataset containing record identifier, sequence index, and latency. d) Analyze the latency dataset to identify at least one physical hot cluster corresponding to a cache hit or low access latency, wherein the physical hot cluster is composed of a set of probe records that are logically continuous on the sequence index and have access latency lower than a preset performance level. e) Classify all other unsampled record identifiers in the data record identifier list: calculate the weighted distance between the sequence index of each record identifier and the sequence index of the probe record identifier in the physical hot cluster, and use a multi-round iterative proximity propagation strategy to assign a corresponding cluster category to each record identifier, forming multiple optimized batches with physical proximity. f) Submit and execute the corresponding batch database operations to the database according to the priority order of the optimized batches.

2. The method according to claim 1, characterized in that, The analysis of the time-delay dataset in step d) includes a dual-validation cognitive modeling process, which comprises: i. Statistical clustering of the latency dataset is performed using a Gaussian mixture model (GMM) to identify peak values ​​and their threshold values ​​for multiple performance levels. ii. Using a Large Language Model (LLM) as a cognitive interpretation engine, the statistical characteristics of the latency dataset are analyzed to independently identify performance levels and provide interpretations of physical meaning; iii. Initiate the intelligent arbitration process. When the analysis results of GMM and LLM are inconsistent, decide whether to accept the statistical results of GMM or adopt the correction opinions of LLM based on the peak separation index of the GMM analysis results, so as to finally determine the division of performance levels.

3. The method according to claim 1, characterized in that, The classification algorithm used in step e) is either a nearest neighbor classification algorithm or a distance-based classification algorithm, and the classification step includes the following execution steps: i. Calculate the weighted distance between the sequence index of each record identifier and the sequence index of the probe record identifier; the weighted distance is calculated as follows: Weighted distance = |target record.seq_index - probe.seq_index| / weight factor; where, if the probe is a preceding probe whose sequence index is less than or equal to the sequence index of the target record, the weight factor is set to the first threshold; if the probe is a following probe, the weight factor is set to the second threshold, and the preceding probe is given a higher weight to utilize the database page prefetching mechanism; ii. A multi-round iterative strategy is adopted, using gradually relaxed distance thresholds for neighbor classification, including direct neighbor classification, neighbor propagation diffusion, and a final classification attempt with a relaxed threshold; iii. The distance threshold is adaptively determined based on the probe distribution density to adapt to the actual distribution characteristics of different datasets.

4. The method according to claim 1, characterized in that, Before the classification in step e), the method also includes a sequence association self-verification step, which checks whether the probe record identifiers within the identified physical hot clusters exhibit a high degree of continuity on the sequence index; If the continuity does not meet the preset conditions, the delay-based optimization process is aborted, and the process reverts to the traditional batch processing mode that executes according to the logical order of the sorting field.

5. The method according to claim 1, characterized in that, The classification in step e) is accomplished by executing an SQL query within the database, a process that includes: i. Store the representative sequence index of the identified physical thermal clusters as representative probes in a temporary table; ii. By performing a distance-based and sorted SQL update operation within the database, a nearest neighbor representative probe is found for each record identifier in the sequence space.

6. The method according to claim 1, characterized in that, Before steps b) and c), the method also includes a security pre-check step, which uses the EXPLAIN command to analyze the execution plan of the operation to obtain the list of data record identifiers. If the plan will trigger a full table scan, the latency-based optimization process is aborted.

7. The method according to claim 1, characterized in that, In step f), the execution steps of submitting and executing the corresponding batch database operations to the database according to the priority order of the optimized batches are as follows: the batches are executed according to the priority order from the batches of the corresponding physical hot clusters to the batches of the corresponding physical cold points, and differentiated throttling control strategies are adopted for batches of different priorities.

8. The method according to claim 1, characterized in that, The method also includes a dynamic re-probe step: i. During the execution of the optimized batch, monitor changes in batch execution performance; ii When a performance degradation is detected to reach a preset condition or a preset reprobing trigger condition is met, steps b) to e) are re-executed for the remaining unprocessed data; the reprobing trigger condition includes at least one of the following: the batch execution performance degradation exceeds a preset threshold, a preset number of batches are completed, a preset time interval has elapsed, or a preset change in the database cache hit rate is detected; iii. Based on the new detection and analysis results, an optimized batch is regenerated and execution continues; wherein, the dynamic re-detection adopts an incremental strategy, sampling and detecting only the data that has not yet been processed.

9. A database operation optimization system, characterized in that, The electronic device includes: One or more processors; Memory, on which computer-executable instructions are stored; The processor is configured to execute the instructions to implement the method as described in any one of claims 1 to 11.

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium contains program code that can be invoked by a processor to execute the method as described in any one of claims 1 to 11.

Images