Supervisory control and data acquisition (SCADA) system historical data query optimization system and method

Abstract

The invention relates to an SCADA (supervisory control and data acquisition) system historical data query optimization system and method, in particular to the field of industrial automation, and is characterized in that a storage architecture is dynamically optimized through a data thermal layering module, so that high-frequency access data automatically resides in a high-speed storage layer, and the hotspot data query efficiency is remarkably improved; the intelligent preloading module prejudges data requirements based on a working condition context, converts passive query into active loading, and effectively reduces response delay of a key operation scene; the multi-dimensional index construction module accurately locates data through a dynamic index strategy, so that full-table scanning resource consumption is avoided; and the query execution cache module is combined with an intelligent compression and self-adaptive adjustment mechanism to construct an efficient closed loop from query request to result return. The whole system greatly reduces time consumption of complex condition retrieval while guaranteeing query stability, and is particularly suitable for high-concurrency industrial monitoring scenes.

Description

Technical Field

[0001] This invention relates to the field of industrial automation technology, and more specifically, to a SCADA system historical data query optimization system. Background Technology

[0002] In the field of industrial automation, Supervisory Control and Data Acquisition (SCADA) systems, as critical infrastructure, are widely used for real-time monitoring and process control in industries such as power, petrochemicals, and rail transportation. With the rapid development of Industrial Internet of Things (IIoT) technology, modern SCADA systems need to process massive amounts of time-series data, such as equipment operating parameters like fan speed, temperature, and vibration signals. This data is continuously generated at millisecond-level frequencies and stored in historical databases. In actual operation and maintenance scenarios, operators often need to backtrack on historical data for analysis, such as diagnosing equipment faults, optimizing operating parameters, or generating compliance reports. Typical query operations include retrieving historical data curves of specific equipment under a certain operating condition (such as full-load operation) or comparing trends of operating parameters over different time periods. These queries often involve multi-dimensional condition combinations, such as simultaneously specifying equipment number, data type, time range, and operating condition label, and require response times to be controlled within seconds to meet real-time decision-making needs. The data scale in industrial sites can reach terabyte levels, and query concurrency increases significantly during peak periods, placing extremely high demands on the efficiency of historical data queries.

[0003] The traditional data query schemes commonly used in current SCADA systems have significant limitations. Most systems rely on standard indexing mechanisms of relational databases, such as timestamp indexes based on B-tree structures or composite indexes combined with device identifiers. However, this static indexing architecture is ill-suited to the highly dynamic nature of industrial data. Due to significant differences in the access frequency of different data, frequently accessed hot data (such as parameters of recently failed equipment) and infrequently accessed archived data (such as annual maintenance records) are stored together on the same medium, leading to drastic fluctuations in query performance. Existing technologies attempt to alleviate this problem through data tiered storage schemes, such as storing hot data in memory caches and cold data in hard disk arrays. However, such schemes lack intelligent data heat perception mechanisms and can only perform static tiering based on simple rules (such as recent access time), failing to dynamically adjust data distribution according to actual query patterns. A more prominent problem is that traditional systems do not consider the correlation between operating context and query behavior, and cannot predict the data needs of operators under specific alarm events. When equipment malfunctions, the system still needs to passively respond to query requests, retrieving data layer by layer from the underlying storage, resulting in response delays of several seconds or even minutes, severely impacting fault handling efficiency. This query latency not only hinders real-time decision-making but can also lead to system overload in multi-user concurrent scenarios, creating a cycle of performance degradation. Therefore, how to achieve intelligent tiered storage based on adaptive data access patterns and preload key data in conjunction with operating context has become a core technical bottleneck for improving the historical data query performance of SCADA systems. Summary of the Invention

[0004] This invention addresses the technical problems existing in the prior art by providing a SCADA system historical data query optimization system and method to solve the problems mentioned in the background.

[0005] The technical solution of this invention to solve the above-mentioned technical problems is as follows: a SCADA system historical data query optimization system, specifically comprising: a data heatmap layering module, an intelligent preloading module, a multidimensional index construction module, and a query execution cache module; wherein; The data heat map layering module runs on a daily preset cycle after the system starts. It uses time-series datasets in the SCADA historical database as its operation objects, uses configuration scripts to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity of data access conditions, and finally outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. The intelligent preloading module is triggered when a data heat map is received and a real-time operating condition event is generated by the SCADA system. It takes the operator's historical query sequence and real-time operating condition label as the operation objects. Based on the similarity of the query sequence and the matching degree of the operating condition label, it applies a weighted calculation model to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preload decision record is generated to the multi-dimensional index building module. The multidimensional index building module is triggered when it receives preloaded decision records or when new data is added to the database. It uses device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on B+ tree structure for highly correlated data in the preloaded decision records, and maintains basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. The query execution cache module is triggered when it receives a query request and an index mapping table. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

[0006] In a preferred embodiment, the data heat map module operates on the time-series dataset in the SCADA historical database according to the first preset cycle every day after the system starts. It uses configuration scripts to count the access frequency of each dataset in the past seven days and dynamically allocates the data to the memory cache layer, solid-state drive storage layer and hard disk archive layer according to high frequency threshold, medium frequency threshold and low frequency threshold.

[0007] In a preferred embodiment, in the data thermal stratification module, the high-frequency threshold, mid-frequency threshold, and low-frequency threshold are dynamically adjusted based on the thermal entropy value; Meanwhile, the data heat map layering module triggers the promotion and demotion scheduling of data between layers based on the continuity condition of data access. The continuity condition refers to the fact that the dataset has multiple consecutive access events in the most recent statistical period and the last access time is within a preset grace period. Finally, the data heatmap layering module outputs a data heatmap containing data identifiers, current storage level, and heatmap values ​​to the intelligent preloading module. In a preferred embodiment, the intelligent preloading module is triggered when it receives a data heat map and the SCADA system generates a real-time operating condition event. It takes the operator's historical query sequence and real-time operating condition label as the operation objects, and applies a weighted calculation model to obtain the correlation degree based on the similarity of the query sequence and the matching degree of the operating condition label. When the correlation degree is greater than or equal to the preset correlation degree threshold, it locates the target data from the data heat map and preloads it into the memory cache layer. At the same time, it generates a preload decision record to the multi-dimensional index building module.

[0008] In a preferred embodiment, in the intelligent preloading module, the query sequence similarity is calculated by an improved dynamic time warping algorithm, which introduces an adaptive weight allocation mechanism and considers both temporal alignment deviation and thermal weight. The working condition label matching degree is calculated through a multimodal embedding space, combining semantic similarity and contextual similarity, and then fused through a composite function; the weighted calculation model uses an attention mechanism to dynamically allocate the weights of query sequence similarity and working condition label matching degree to obtain the comprehensive relevance.

[0009] In a preferred embodiment, the multidimensional index building module is triggered when a preloaded decision record is received or new data is detected entering the database. Using device identifier, data type, timestamp, and operating condition label as index fields, a composite index based on a B+ tree structure is built first for highly correlated data in the preloaded decision record. Basic index, parameter index, and operating condition index are maintained for all data. At the same time, the index nodes are updated synchronously at a preset incremental period, and the index is dynamically started and stopped according to the index usage frequency. Finally, the latest index mapping table is output to the query execution cache module.

[0010] In a preferred embodiment, in the multidimensional index building module, when synchronously updating index nodes, the optimal batch size for incremental updates is calculated by an optimization model to balance input / output costs and processor costs. The dynamic start-stop index calculates the index utility value through a utility function. This utility value is based on the index usage frequency, storage size, and query latency, combined with a weighting coefficient. When the utility value is lower than a preset threshold, the index is stopped; otherwise, the index is enabled.

[0011] In a preferred embodiment, the query execution cache module is triggered upon receiving a query request and an index mapping table. It first searches the memory cache layer to see if there is a valid compressed cache result. If a match is found, the result is returned directly. If no match is found, the index mapping table is called to locate the data in the hierarchical storage, the data is extracted, compressed using the LZ4 compression algorithm, and the result is returned and the cache is updated. At the same time, the query time and cache hit rate are monitored in real time. When the query time exceeds the maximum tolerable time or the cache hit rate is lower than the minimum hit rate, an adaptive adjustment signal is sent to the data thermal stratification module and the multidimensional index construction module.

[0012] In a preferred embodiment, in the query execution caching module, when updating the cache, the cache replacement strategy is determined by a cache priority score calculation model. This model calculates the priority score based on the historical access frequency, heat value, compressed data size, and last access time interval of the data item. The query time is calculated using an exponentially weighted moving average to smooth short-term fluctuations. The cache hit rate is calculated using an exponentially decaying weighted sum to emphasize recent queries.

[0013] A method for optimizing historical data query in a SCADA system includes the following steps: S1. After the system starts, it runs according to the daily preset cycle. It uses the time series dataset in the SCADA historical database as the operation object, uses the configuration script to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to the high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity condition of data access. Finally, it outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. S2 is triggered when the data heat map is received and the SCADA system generates a real-time operating condition event. It takes the operator's historical query sequence and real-time operating condition label as the operation object. Based on the similarity of the query sequence and the matching degree of the operating condition label, a weighted calculation model is applied to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preloaded decision record is generated to the multi-dimensional index construction module. S3 is triggered when a preloaded decision record is received or new data is added to the database. It uses the device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on a B+ tree structure for highly correlated data in the preloaded decision record, and maintains a basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. S4 is triggered when a query request and index mapping table are received. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

[0014] The beneficial effects of this invention are as follows: The data heat-based tiering module dynamically optimizes the storage architecture, enabling frequently accessed data to automatically reside in the high-speed storage layer, significantly improving the efficiency of hot data queries; the intelligent pre-loading module predicts data needs based on the operating context, transforming passive queries into active loading, effectively reducing response latency in critical operation scenarios; the multi-dimensional index building module accurately locates data through dynamic indexing strategies, avoiding the resource consumption of full table scans; and the query execution caching module combines intelligent compression and adaptive adjustment mechanisms to construct an efficient closed loop from query request to result return. The entire system significantly reduces the time consumption of complex condition retrieval while ensuring query stability, making it particularly suitable for high-concurrency industrial monitoring scenarios. Attached Figure Description

[0015] Figure 1 This is a flowchart of the SCADA system historical data query optimization method in an embodiment of the present invention.

[0016] Figure 2 This is a schematic diagram of the SCADA system historical data query optimization system framework in an embodiment of the present invention. Detailed Implementation

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

[0018] In the description of this application, the terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include one or more features. In the description of this application, "multiple" means two or more, unless otherwise explicitly specified.

[0019] In the description of this application, the term "for example" is used to mean "used as an example, illustration, or description." Any embodiment described as "for example" in this application is not necessarily to be construed as being more preferred or advantageous than other embodiments. The following description is provided to enable any person skilled in the art to make and use the invention. Details are set forth in the following description for purposes of explanation. It should be understood that those skilled in the art will recognize that the invention can be made without using these specific details. In other instances, well-known structures and processes will not be described in detail to avoid obscuring the description of the invention with unnecessary detail. Therefore, the invention is not intended to be limited to the embodiments shown, but is consistent with the broadest scope of the principles and features disclosed in this application.

[0020] This embodiment provides, for example Figure 2 The SCADA system historical data query optimization system shown includes: a data heatmap layering module, an intelligent preloading module, a multidimensional index construction module, and a query execution cache module; wherein; The data heat map layering module runs on a daily preset cycle after the system starts. It uses time-series datasets in the SCADA historical database as its operation objects, uses configuration scripts to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity of data access conditions, and finally outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. The intelligent preloading module is triggered when a data heat map is received and a real-time operating condition event is generated by the SCADA system. It takes the operator's historical query sequence and real-time operating condition label as the operation objects. Based on the similarity of the query sequence and the matching degree of the operating condition label, it applies a weighted calculation model to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preload decision record is generated to the multi-dimensional index building module. The multidimensional index building module is triggered when it receives preloaded decision records or when new data is added to the database. It uses device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on B+ tree structure for highly correlated data in the preloaded decision records, and maintains basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. The query execution cache module is triggered when it receives a query request and an index mapping table. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

[0021] In this embodiment, it should be specifically noted that in the data heatmap layering module, after the system starts, it follows the first preset cycle every day. run; Using time series datasets from the SCADA historical database For the operation object, where, Representing an independent dataset (such as a sequence of data points at a specific time granularity for a given measurement point), each run is treated as an independent analysis window. ; The configuration script is used to count the access frequency of each dataset in the past seven days. A predefined configuration script is called to scan and count the access frequency over the past 7 calendar days (i.e., the statistical window). Within each dataset The total number of times a query request is accessed is denoted as the access frequency. Finally, an initial access frequency vector is generated. .

[0022] Next, the thermodynamic entropy value is calculated. It is used to quantify the concentration of data access patterns and calculate thermal entropy values. The expression is:

[0023] in, For dataset The probability of access (dimensionless) has a range of [0,1]. The value of thermodynamic entropy (dimensionless) has a range of . The smaller the value, the more concentrated the access.

[0024] Then, based on the preset baseline threshold and adjustment coefficient, the dynamic threshold is calculated, and its expression is:

[0025]

[0026]

[0027] in, , , Preset reference high-frequency, mid-frequency, and low-frequency thresholds (positive real numbers) to satisfy For example, values ​​can be 100, 20, or 5. To adjust the coefficients (positive real numbers), satisfying (For example, values ​​of 0.1, 0.2, and 0.3) are used to dynamically scale the threshold based on the entropy value; The actual high-frequency, mid-frequency, and low-frequency thresholds (positive real numbers) are dynamically adjusted; finally, according to By comparing with dynamic thresholds, the storage level can be preliminarily determined. :

[0028] Data is dynamically allocated to the memory cache layer, solid-state drive storage layer, and hard disk archive layer based on high-frequency, medium-frequency, and low-frequency thresholds.

[0029] Among them, the high-frequency, mid-frequency, and low-frequency thresholds are dynamically adjusted based on the thermal entropy value. Simultaneously, the data thermal stratification module triggers data migration and degradation scheduling between strata based on data access continuity conditions. The continuity condition refers to the dataset... In the most recent statistical period Memory in continuous Second access event ( (The last access time is a positive integer, such as 3). Within the preset grace period satisfy ( (where 3600 seconds is a positive real number), and the continuity condition scheduling rule is: if the continuity condition is met, then regardless of... Size, target level Set as ratio A higher level; if the conditions are not met and Below the degradation threshold (e.g., memory layer data) If the rating is positive, the level will be downgraded by one level; otherwise, the rating will remain the same. .

[0030] Finally, the data heatmap layering module outputs a data heatmap containing data identifiers, current storage levels, and heatmap values. Data heatmap to the intelligent preloading module Includes the following field: Data Identifier (corresponding to...) ), current storage level , , It is sent to the smart preload module.

[0031] In this embodiment, it should be specifically noted that the intelligent preloading module receives the data heat map. Furthermore, the SCADA system generates real-time operating condition events. Time-triggered, real-time operating condition event Carrying standardized operating condition labels Based on operator historical query sequence ( Each query Includes query data identifier set and query timestamp ) and real-time operating condition tags For the operation object, and from Extracting operating condition labels from the data And vectorize it into feature representation By calculating the similarity of query sequences The degree of matching with the working condition label was determined, and a weighted fusion model was applied to obtain the comprehensive correlation. When the overall correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preloaded decision record is generated in the multidimensional index building module, wherein the generated preloaded decision record contains the trigger event identifier. Preloaded data identifier set Calculate the correlation degree and various intermediate values ​​and decision timestamps .

[0032] In this embodiment, it should be specifically noted that the query sequence similarity is calculated using an improved dynamic time warping algorithm, the expression of which is:

[0033] The algorithm includes an adaptive weighting function to handle timing alignment bias and thermal weights. The adaptive weighting function is as follows:

[0034] in, To query sequence similarity, the value range is [0,1], and the larger the value, the higher the similarity. This is a potential query sequence predicted based on the current operating conditions; For adaptive dynamic time warping distance; This is the maximum tolerable distance threshold used for normalization; The adaptive weighting function takes into account both time alignment bias and thermal weighting. This is the time decay coefficient, which controls the sensitivity of timing alignment; This is the thermal weighting coefficient, which adjusts the influence of data thermal values ​​on similarity calculation. This is a thermal weighting function based on a heat map.

[0035] The working condition label matching score is calculated using a multimodal embedding space, which includes the fusion of semantic similarity and contextual similarity. The expression for calculating the working condition label matching score is as follows:

[0036] The composite similarity function is:

[0037] in, The value range is [0,1] for the working condition label matching degree. Use the Sigmoid activation function to ensure that the output is in the range [0,1]. For semantic similarity, it is based on the cosine similarity of the label embedding vectors; For contextual similarity, it is based on tag co-occurrence frequency and timeliness weight; This is a statistical function for tag co-occurrence frequency; As a time-sensitivity weighting function, recent co-occurrences are given higher weight; For the combined weight coefficients, satisfying .

[0038] The weighted fusion model uses an attention mechanism to dynamically allocate weights for query sequence similarity and work condition label matching to obtain a comprehensive relevance score, expressed as:

[0039] in, To determine the overall correlation, the value range is [0,1], used for preloading decisions; , Attention weights for sequence similarity and label matching; , The attention scoring function is dynamically calculated based on multiple features. , The feature extraction function takes into account factors such as sequence variance, historical length, and timeliness.

[0040] In this embodiment, it should be specifically noted that the multidimensional index building module is activated when a preloaded decision record is received or new data is detected entering the database. The multidimensional index building module is activated when one of the following two events occurs: 1. Receive a preloading decision record from the intelligent preloading module, which contains a list of highly correlated data identifiers; 2. A new data entry event was detected in the SCADA system, which contains a new set of data records; The integrity of the triggered event is then verified, and the index fields are parsed out. The device identifier, data type, timestamp, and operating condition label are used as index fields. For highly correlated data in the preloaded decision records, a composite index based on a B+ tree structure is built first. Basic index, parameter index, and operating condition index are maintained for all data. At the same time, the index nodes are updated synchronously at a preset incremental period. The index is dynamically started and stopped according to the frequency of index usage. Finally, the latest index mapping table is output to the query execution cache module. In the multidimensional index building module, when synchronously updating index nodes, the optimal batch size for incremental updates is calculated through an optimization model to balance input / output costs and processor costs. The incremental update uses the optimal batch size. Through the formula:

[0041] Calculate, where, This is the disk input / output cost coefficient, representing the cost of a single disk input / output operation, which is related to the storage medium. This is the CPU cost factor, representing the cost of a single CPU processing operation, which is related to system load. To change the size of the dataset, this indicates the number of data records that have changed since the last update; The batch size variable represents the number of data records processed in a single update; this formula is used to calculate the optimal batch size by balancing disk I / O costs and CPU costs to minimize the total overhead of incremental updates. Dynamically starting and stopping indexes calculates the index's utility value using a utility function. This utility value is based on index usage frequency, storage size, and query latency, combined with a weighting coefficient. The index is stopped when the utility value falls below a preset threshold; otherwise, it is started. Dynamically starting and stopping indexes utilizes the index utility function. The comprehensive evaluation of the query benefits, resource consumption, and performance impact of the index is expressed as follows:

[0042] in, The frequency of index i within the time window t represents the number of times the index is queried per unit of time. The storage size of index i represents the amount of storage space occupied by the index; The average query latency using index i represents the average time taken for a query operation. The frequency of use weighting coefficient is used to adjust the contribution of frequency of use in utility calculation; This is a storage size weighting coefficient used to adjust the contribution of storage size in utility calculation; This is the query latency weighting coefficient, used to adjust the contribution of query latency to utility calculation. This formula is used to dynamically calculate the utility value by quantifying the combined impact of index usage frequency, storage size, and query latency, in order to determine the start / stop status of the index, thereby optimizing resource utilization and query performance.

[0043] In this embodiment, it should be specifically noted that in the query execution cache module, upon receiving a query request and an index mapping table, the following is triggered (including verifying the format integrity of the query request and the data structure consistency of the index mapping table; if verification fails, an error message is returned). First, it searches the memory cache layer to see if a valid compressed cache result exists (cache retrieval and hit processing, specifically including generating a cache key, checking the validity of the cache item (e.g., not expired and data version matching); if a hit occurs, the result is returned directly and the access time and frequency are updated). If a hit occurs, the result is returned directly; if a miss occurs, the index mapping table is called to locate the data's position in the hierarchical storage (cache miss processing and data retrieval, specifically including parsing the query request to determine the data range and using the index mapping table to locate the data in the memory cache). The system retrieves data from the physical addresses of the high-speed layer, SSD storage layer, or HDD archive layer, prioritizing data extraction from the high-speed layer. The extracted data is then compressed using the LZ4 compression algorithm, and the results are returned and the cache updated. (Specifically, this includes applying the LZ4 compression algorithm to the extracted raw data to generate compressed data, and using a cache priority score calculation model to determine whether to cache or replace existing data.) Simultaneously, it monitors query time and cache hit rate in real time (including calculating the exponentially weighted moving average of query time and the exponentially decaying weighted sum of cache hit rate). When query time exceeds the maximum tolerable time or cache hit rate falls below the minimum hit rate, an adaptive adjustment signal is sent to the data thermal stratification module and the multidimensional index building module (adaptive adjustment signal sending includes generating a signal containing performance data and sending it to the upstream module).

[0044] In the query execution caching module, when updating the cache, the cache replacement strategy is determined by a cache priority score calculation model. This model calculates a priority score based on the historical access frequency, heat value, compressed data size, and last access time interval of the data item. Query time is calculated using an exponentially weighted moving average to smooth short-term fluctuations. Cache hit rate is calculated using an exponentially decaying weighted sum to emphasize recent queries. The specific calculation formula for the cache priority score calculation model is: the priority score equals the product of access frequency and heat value, divided by the product of compressed data size and the logarithm of the time interval plus one. The expression is:

[0045] in, , is the cache priority score for data item i, dimensionless, representing the retention value of the data item in the cache; the higher the value, the more it should be retained. is the historical access frequency of data item i, a non-negative integer, representing the number of times the data item has been queried, reflecting the popularity of the data; The heat value of data item i is dimensionless, derived from the data heat map, and reflects the access value of the data. The compressed data size of data item i, in bytes, represents the storage space occupied by the data; This is the current timestamp, in milliseconds. Let be the last access timestamp of data item i, in milliseconds; this formula is used to quantify the cache value of data by comprehensively considering access frequency, heat value, data size and access time interval, so as to optimize cache replacement decisions and ensure that high-value data resides in the cache. The smoothing factor in the exponentially weighted moving average calculation ranges from 0 to 1, and its expression is as follows:

[0046] in, The exponentially weighted moving average of query time, in milliseconds, represents the recent query performance trend. This is the exponentially weighted moving average of the previous period, in milliseconds. The time taken for the current query, in milliseconds; This is a smoothing factor, ranging from 0 to 1, typically set to 0.9, which controls the weight of historical data. This formula is used to eliminate random fluctuations in query time, more accurately evaluate query performance, and provide a reliable basis for adaptive adjustment. Furthermore, the decay factor in the exponential decay weighted sum calculation is a positive real number, and its expression is:

[0047] in, ; Cache hit rate, dimensionless, value range [0,1], representing the proportion of queries that hit the cache; The total number of queries within the time window, a positive integer; This is the time decay weight for the j-th query. It is dimensionless, and the larger the value, the higher the contribution of recent queries. This is the decay factor, a positive real number, usually taken as 0.01, which controls the rate of weight decay. The timestamp for the j-th query, in milliseconds; The function is an indicator function, which is 1 if the cache is hit in the j-th query and 0 otherwise. This formula uses exponential decay weighting to make the recent hit rate contribute more to the overall value, reflect system changes in a timely manner, and improve the sensitivity of monitoring.

[0048] Example 2 This embodiment provides, for example Figure 1 The method for optimizing historical data query in a SCADA system, as shown, includes the following steps: S1. After the system starts, it runs according to the daily preset cycle. It uses the time series dataset in the SCADA historical database as the operation object, uses the configuration script to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to the high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity condition of data access. Finally, it outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. S2 is triggered when the data heat map is received and the SCADA system generates a real-time operating condition event. It takes the operator's historical query sequence and real-time operating condition label as the operation object. Based on the similarity of the query sequence and the matching degree of the operating condition label, a weighted calculation model is applied to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preloaded decision record is generated to the multi-dimensional index construction module. S3 is triggered when a preloaded decision record is received or new data is added to the database. It uses the device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on a B+ tree structure for highly correlated data in the preloaded decision record, and maintains a basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. S4 is triggered when a query request and index mapping table are received. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

[0049] It should be noted that the descriptions of each embodiment in the above embodiments have different focuses. For parts that are not described in detail in a certain embodiment, please refer to the relevant descriptions in other embodiments.

[0050] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0051] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded computer, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0052] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0053] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0054] Although preferred embodiments of the invention have been described, those skilled in the art, upon learning the basic inventive concept, can make other changes and modifications to these embodiments. Therefore, the appended claims are intended to be interpreted as including both the preferred embodiments and all changes and modifications falling within the scope of the invention.

[0055] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

Claims

1. A SCADA system historical data query optimization system, characterized in that, Specifically, it includes: Data heatmap layering module, intelligent preloading module, multidimensional index building module, and query execution caching module; in; The data heat map layering module runs on a daily preset cycle after the system starts. It uses time-series datasets in the SCADA historical database as its operation objects, uses configuration scripts to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity of data access conditions, and finally outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. The intelligent preloading module is triggered when a data heat map is received and a real-time operating condition event is generated by the SCADA system. It takes the operator's historical query sequence and real-time operating condition label as the operation objects. Based on the similarity of the query sequence and the matching degree of the operating condition label, it applies a weighted calculation model to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preload decision record is generated to the multi-dimensional index building module. The multidimensional index building module is triggered when it receives preloaded decision records or when new data is added to the database. It uses device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on B+ tree structure for highly correlated data in the preloaded decision records, and maintains basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. The query execution cache module is triggered when it receives a query request and an index mapping table. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

2. The SCADA system historical data query optimization system according to claim 1, characterized in that: In the data heat map layering module, after the system starts, it runs according to the first preset cycle of each day, using the time series dataset in the SCADA historical database as the operation object. It uses configuration scripts to count the access frequency of each dataset in the past seven days, and dynamically allocates the data to the memory cache layer, solid-state drive storage layer and hard disk archive layer according to high frequency threshold, medium frequency threshold and low frequency threshold.

3. The SCADA system historical data query optimization system according to claim 2, characterized in that: In the data thermal stratification module, the high-frequency threshold, mid-frequency threshold, and low-frequency threshold are dynamically adjusted based on the thermal entropy value; Meanwhile, the data heat map layering module triggers the promotion and demotion scheduling of data between layers based on the continuity condition of data access. The continuity condition refers to the fact that the dataset has multiple consecutive access events in the most recent statistical period and the last access time is within a preset grace period. Finally, the data heatmap layering module outputs a data heatmap containing data identifiers, current storage level, and heatmap values ​​to the intelligent preloading module.

4. The SCADA system historical data query optimization system according to claim 3, characterized in that: The intelligent preloading module is triggered when it receives a data heat map and the SCADA system generates a real-time operating condition event. It takes the operator's historical query sequence and real-time operating condition label as the operation objects, and applies a weighted calculation model to obtain the correlation degree based on the similarity of the query sequence and the matching degree of the operating condition label. When the correlation degree is greater than or equal to the preset correlation degree threshold, it locates the target data from the data heat map and preloads it into the memory cache layer. At the same time, it generates a preloaded decision record to the multi-dimensional index building module.

5. The SCADA system historical data query optimization system according to claim 4, characterized in that: In the intelligent preloading module, the similarity of the query sequence is calculated through an improved dynamic time warping algorithm, which introduces an adaptive weight allocation mechanism and considers both temporal alignment deviation and thermal weight. The working condition label matching degree is calculated through multimodal embedding space, combining semantic similarity and contextual similarity, and fused through composite function; the weighted calculation model uses an attention mechanism to dynamically allocate the weights of query sequence similarity and working condition label matching degree to obtain the comprehensive relevance.

6. The SCADA system historical data query optimization system according to claim 5, characterized in that: In the multidimensional index building module, the process is triggered when a preloaded decision record is received or new data is detected entering the database. Using device identifier, data type, timestamp, and operating condition label as index fields, a composite index based on a B+ tree structure is built first for highly correlated data in the preloaded decision record. Basic index, parameter index, and operating condition index are maintained for all data. At the same time, the index nodes are updated synchronously at a preset incremental period, and the index is dynamically started and stopped according to the index usage frequency. Finally, the latest index mapping table is output to the query execution cache module.

7. The SCADA system historical data query optimization system according to claim 6, characterized in that: In the multidimensional index building module, when synchronously updating index nodes, the optimal batch size for incremental updates is calculated through an optimization model to balance input / output costs and processor costs. The dynamic start and stop of the index calculates the utility value of the index through a utility function. This utility value is based on the index usage frequency, storage size, and query latency, combined with a weight coefficient. When the utility value is lower than a preset threshold, the index is stopped; otherwise, the index is enabled.

8. The SCADA system historical data query optimization system according to claim 7, characterized in that: In the query execution cache module, the process is triggered upon receiving a query request and an index mapping table. It first checks whether there is a valid compressed cache result in the memory cache layer. If a match is found, the result is returned directly. If no match is found, the index mapping table is called to locate the data in the hierarchical storage, the data is extracted, compressed using the LZ4 compression algorithm, and the result is returned and the cache is updated. At the same time, the query time and cache hit rate are monitored in real time. When the query time exceeds the maximum tolerable time or the cache hit rate is lower than the minimum hit rate, an adaptive adjustment signal is sent to the data thermal tiering module and the multidimensional index building module.

9. The SCADA system historical data query optimization system according to claim 8, characterized in that: In the query execution caching module, when updating the cache, the cache replacement strategy is determined by a cache priority score calculation model. This model calculates the priority score based on the historical access frequency, heat value, compressed data size, and last access time interval of the data item. The query time is calculated using an exponentially weighted moving average to smooth short-term fluctuations. The cache hit rate is calculated using an exponentially decaying weighted sum to emphasize recent queries.

10. A method for optimizing historical data query in a SCADA system according to any one of claims 1-9, characterized in that: Specifically, the following steps are included: S1. After the system starts, it runs according to the daily preset cycle. It uses the time series dataset in the SCADA historical database as the operation object, uses the configuration script to count the access frequency of each dataset in the past 7 days, and dynamically allocates the data to the memory cache layer, SSD storage layer and HDD archive layer according to the high frequency threshold, medium frequency threshold and low frequency threshold. At the same time, it triggers the promotion and demotion scheduling of data between layers according to the continuity condition of data access. Finally, it outputs a data heat map containing data identifier, current storage layer and heat value to the intelligent preloading module. S2 is triggered when the data heat map is received and the SCADA system generates a real-time operating condition event. It takes the operator's historical query sequence and real-time operating condition label as the operation object. Based on the similarity of the query sequence and the matching degree of the operating condition label, a weighted calculation model is applied to obtain the correlation degree. When the correlation degree is greater than or equal to the preset correlation degree threshold, the target data is located from the data heat map and preloaded into the memory cache layer. At the same time, a preloaded decision record is generated to the multi-dimensional index construction module. S3 is triggered when a preloaded decision record is received or new data is added to the database. It uses the device identifier, data type, timestamp, and operating condition label as index fields. It prioritizes building a composite index based on a B+ tree structure for highly correlated data in the preloaded decision record, and maintains a basic index, parameter index, and operating condition index for all data. At the same time, it updates the index nodes synchronously in an incremental period of T2, and dynamically starts and stops the index according to the index usage frequency. Finally, it outputs the latest index mapping table to the query execution cache module. S4 is triggered when a query request and index mapping table are received. It first checks whether there is a valid compressed cache result in the memory cache layer. If no match is found, it calls the index mapping table to locate the data in the hierarchical storage, extracts the data, compresses it using the LZ4 algorithm, returns the result, and updates the cache. At the same time, it monitors the query time and cache hit rate in real time. When the query time is greater than the maximum tolerable time or the cache hit rate is less than the minimum hit rate, it sends an adaptive adjustment signal to the data thermal stratification module and the multidimensional index construction module.

Images