Query acceleration system of column storage database and self-adaptive index construction method

Abstract

The invention discloses a query acceleration system of a column storage database and a self-adaptive index construction method, and the system comprises a data compression module which is used for compressing data of the column storage database; the input end of the parallel processing module is electrically connected with the output end of the data compression module; the input end of the query load analysis module is electrically connected with the output end of the parallel processing module; the input end of the self-adaptive index module is electrically connected with the output end of the query load analysis module and the output end of the parallel processing module; and the input end of the machine learning prediction module is electrically connected with the output end of the query load analysis module. The system comprises a data compression module and the like, through multi-module cooperation and a five-step index construction method, the static index problem is solved, and the park monitoring data query efficiency and the system adaptability are improved.

Description

Technical Field

[0001] This invention relates to the field of columnar storage databases, and in particular to a query acceleration system and adaptive index construction method for columnar storage databases. Background Technology

[0002] Columnar storage databases, with their efficient columnar storage and aggregation query capabilities, have been widely used in big data analysis and real-time computing scenarios. In the field of smart park monitoring, they can be used to efficiently process massive amounts of monitoring data, including real-time video frame feature data, equipment operation logs, personnel flow statistics, abnormal event records, etc. By quickly aggregating and querying this data, they support the core functions of the monitoring platform, such as real-time early warning and historical data backtracking.

[0003] However, existing columnar storage databases have significant limitations in indexing strategies. The commonly used static indexing strategy, while improving query efficiency to some extent, lacks flexibility and adaptability. Once a static indexing strategy is determined, it cannot be dynamically adjusted according to changes in query load during database operation. For example, when the query frequency of monitoring data in a specific area of ​​the park suddenly increases due to temporary activities, the static index cannot promptly create more efficient indexes for the device ID column, timestamp column, etc., of that area, resulting in low query efficiency. Furthermore, for some low-frequency query monitoring data columns (such as device status logs in secondary areas), the static index still occupies a large amount of storage space, resulting in a waste of resources.

[0004] Furthermore, when data is updated, the existing static index strategy requires synchronous index reconstruction. In the smart monitoring scenario of the park, data updates are extremely frequent (such as video frame feature data generated per second and real-time refreshed personnel flow counts). Frequent index reconstruction will consume a lot of system resources, seriously affect the system throughput, and cause the monitoring platform to respond slowly when handling emergency query requests (such as tracing historical monitoring data of sudden events), which cannot meet the real-time requirements of park security.

[0005] With the continuous development of smart monitoring technology in the park, the amount of monitoring data has exploded, and the query load has become increasingly complex and variable (such as the difference in query patterns between weekdays and holidays, and the different query needs between daily monitoring and emergency events). To address this, a query acceleration system for columnar storage databases and an adaptive index construction method are proposed. Summary of the Invention

[0006] To address the shortcomings of existing technologies, this invention provides a query acceleration system and adaptive index construction method for columnar storage databases, thereby resolving the problems mentioned in the background section.

[0007] To achieve the above objectives, the present invention provides the following technical solution: a query acceleration system for columnar storage databases, comprising: The data compression module is used to compress the database data stored in columnar arrays. The database data includes real-time video frame feature data generated by the park's smart monitoring system, equipment operation logs, and personnel flow statistics. The parallel processing module has its input end electrically connected to the output end of the data compression module. It is used to receive the compressed data, divide the data into columns, and process the divided data through multi-threaded parallel reading and distributed computing. It supports parallel analysis of monitoring data of multiple areas and multiple devices in the park. The query load analysis module is electrically connected to the output of the parallel processing module. It is used to monitor and analyze the query load of the database in real time, and to extract high-frequency query features in the smart monitoring scenario of the park. An adaptive indexing module, whose input is electrically connected to the output of the query load analysis module and the parallel processing module respectively, is used to dynamically select and optimize the indexing strategy based on the characteristics of the query load and a greedy algorithm. It prioritizes the construction of efficient indexes for the monitoring data columns that are frequently accessed in the smart monitoring of the park, and builds and adjusts the indexes based on the optimized indexing strategy. The machine learning prediction module has its input terminal electrically connected to the output terminal of the query load analysis module and its output terminal electrically connected to the input terminal of the adaptive index module. It is used to learn the periodic query patterns of the park's smart monitoring based on the characteristic information of historical query loads, use machine learning algorithms to predict future query patterns, and send the prediction results to the adaptive index module. The data compression module employs differentiated compression strategies for different types of data. For video frame feature data, a lightweight lossless compression algorithm (such as Snappy) is selected to reduce storage space while ensuring real-time performance. For text data such as equipment operation logs and personnel flow statistics, a lossy compression algorithm with a high compression ratio (such as ZSTD) is used to balance storage space and data accuracy by setting a reasonable compression level.

[0008] Preferably, the data compression module employs multiple compression algorithms and automatically selects the appropriate compression algorithm for compression processing based on the data type and characteristics. For video frame feature data in smart park monitoring, a lightweight compression algorithm is used, and for historical alarm logs, a high compression ratio algorithm is used. The compression algorithms include, but are not limited to, LZO compression algorithm, Snappy compression algorithm, and ZSTD compression algorithm. When implementing the data compression module, a mapping table between data types and compression algorithms can be preset. For example, video frame feature data can be defined to correspond to lightweight compression algorithms (such as Snappy), and historical alarm logs can correspond to high compression ratio algorithms (such as ZSTD). After receiving data, the module first identifies the data type through metadata or file header, and then automatically selects the compression algorithm according to the mapping table. If the data type is not in the mapping table, the most suitable algorithm is dynamically matched by analyzing data characteristics (such as data distribution and redundancy) to ensure a balance between compression efficiency and real-time performance.

[0009] Preferably, the parallel processing module includes a task allocation unit and multiple computing nodes. The task allocation unit is used to allocate the segmented column data to different computing nodes, and prioritizes allocating emergency query tasks in the park's smart monitoring to idle computing nodes. Each computing node processes the allocated data in a multi-threaded parallel manner, and after processing, feeds the results back to the task allocation unit for aggregation. For the task allocation unit, a priority queue algorithm can be used to dynamically adjust the task allocation order based on the urgency of the query task and the load of the computing nodes. When an urgent query task arrives, it is inserted at the head of the queue and given priority to be allocated to idle computing nodes. Each computing node uses multi-threading technology to further subdivide the allocated column data, with each thread processing a portion of the data. Finally, the processing results of each thread are merged and fed back to the task allocation unit for summary.

[0010] Preferably, the query load feature information extracted by the query load analysis module includes query frequency, columns involved in the query, query conditions, and query response time, wherein the columns involved in the query include data columns unique to smart monitoring in the park, such as monitoring device ID column, abnormal event type column, timestamp column, and area code column. For the query load analysis module, when extracting query load characteristic information, the query frequency can be counted by setting a counter at the database query interface; the columns involved in the query can be obtained by using the database metadata information; the query conditions can be obtained by parsing the query statement; and the time interval from initiating the query to obtaining the result can be recorded as the query response time. For data columns unique to smart monitoring in the park, these columns can be clearly identified during the database design, and the information of the relevant identified columns can be directly extracted during analysis.

[0011] Preferably, the adaptive indexing module further includes an index evaluation unit, which is used to evaluate the performance of the constructed index. The evaluation indicators include query response latency and index storage resource utilization in the smart monitoring scenario of the park. When the evaluation result does not meet the preset threshold, the index strategy selection and optimization process is retried. The index evaluation unit periodically collects query response latency data (such as recording the start and end times of queries by using a built-in timer to record abnormal events) and index storage usage data (such as obtaining the index file size through the file system API), and compares them with preset thresholds. If any indicator exceeds the limit, a re-optimization signal is sent to the query load analysis module through an internal interface, triggering the iterative process of the index strategy.

[0012] An adaptive index building method for columnar storage databases, based on the aforementioned query acceleration system for columnar storage databases, includes the following steps: Step 1: Data preprocessing. Clean and organize the data in the columnar storage database, remove invalid frame information and duplicate device logs from the smart monitoring data of the park, remove invalid and redundant data, and standardize the data format. Step 2: Query load monitoring and analysis. Monitor database query requests in real time, focusing on collecting query commands initiated by the park's smart monitoring platform, collecting query load information, and analyzing the collected query load information to extract the characteristic parameters of the query load. Step 3: Preliminary selection of indexing strategy. Based on the query load characteristic parameters extracted in Step 2 and combined with the preset index selection rules, the applicable indexing strategy is initially determined for the monitoring data columns that are frequently queried in the smart monitoring of the park. Step 4: Optimize the indexing strategy based on the greedy algorithm. With the goal of maximizing query efficiency, the initially determined indexing strategy is iteratively optimized using the greedy algorithm. During the optimization process, priority is given to ensuring query efficiency in emergency scenarios within the park, and the optimal indexing strategy is obtained. Step 5: Build and dynamically adjust the index. Build the index according to the optimal indexing strategy, and dynamically adjust the index structure based on real-time changes in query load and the future query pattern prediction results provided by the machine learning prediction module. For smart monitoring data in the park, a threshold for the integrity of video frame information is set, and frames below this value are considered invalid. For device logs, the device running time is compared with the log recording time, and logs with a time difference exceeding a preset range are considered duplicate logs. At the same time, a unified data format template is developed to convert various types of data into a standard format to ensure data consistency.

[0013] Preferably, when analyzing the query load information in step two, the priority of different queries is also calculated. The priority calculation is based on the query frequency, the amount of data involved in the query, and the priority weight set by the user. The priority weight set by the user is configured according to the business needs of smart monitoring in the park. When analyzing the query load information and calculating the priority of different queries in step two, the basic score calculation method of query frequency and the amount of data involved in the query can be set first, such as assigning scores linearly by the number of queries and assigning scores in segments according to the size of the data. For the priority weight set by the user, preset weight templates for common smart monitoring business scenarios in the park (such as emergency command, daily statistics, etc.) are provided. Users can also customize the weight value according to actual needs. The system calculates the final priority by multiplying the weight by the basic score.

[0014] Preferably, the specific process of optimizing the indexing strategy using a greedy algorithm in step four is as follows: Step 1: Initialize the index strategy set. Add the initially determined index strategies as initial elements to the set. The set contains candidate index schemes for different data types in smart monitoring of the park. Step 2: Select the current optimal indexing strategy from the set of indexing strategies as a candidate strategy; Step 3: Make local adjustments to the candidate strategies to generate new indexing strategies; Step 4: Calculate the performance metrics of the new indexing strategy. If the performance metrics are better than the current best indexing strategy, then update the best indexing strategy. Step 5: Repeat steps 2, 3 and 4 until the preset number of iterations is reached or the performance index no longer improves. The final optimal indexing strategy is taken as the optimization result. For the calculation of performance indicators in step four, which uses a greedy algorithm to optimize the index strategy, query response latency and the index's storage resource utilization rate can be used as evaluation indicators. Query response latency is obtained by recording the time difference from initiating a query to obtaining the result, and storage resource utilization rate is obtained by calculating the ratio of the storage space occupied by the index to the total storage space. This is used to determine whether the new index strategy is more efficient.

[0015] Preferably, when dynamically adjusting the index structure in step five, an incremental update method is used, updating only the index portion corresponding to the changed data. If the real-time data column of the newly added monitoring device and the code column after the adjustment of the park area change, there is no need to rebuild the entire index synchronously. When dynamically adjusting the index structure in step five using incremental updates, this can be achieved by establishing a data change monitoring mechanism. By utilizing the database's built-in triggers or log recording functions, changes such as the insertion of new monitoring equipment data columns and the modification of park area code columns can be captured in real time. When a change is detected, the system automatically locates the index portion corresponding to the affected data and only updates that portion of the index, such as recalculating index key values ​​or adjusting index node pointers, to ensure the consistency between the index and the data without rebuilding the entire index.

[0016] Preferably, step five also includes verifying the dynamically adjusted index structure by testing the performance of the index structure through simulated queries. The simulated queries include typical scenarios of smart monitoring in the park. If the performance does not meet expectations, the index strategy will be optimized and adjusted again. In step five, when verifying the dynamically adjusted index structure, if a simulated query is used to test performance, a simulated query environment can be constructed to simulate the real smart monitoring business scenario and data scale of the park. Commands such as cross-regional personnel trajectory tracking query and multi-device collaborative linkage query similar to actual query commands can be generated. Query response time, accuracy and other indicators can be recorded. If the performance does not meet expectations, the bottlenecks of the index structure in processing these queries can be analyzed, such as index granularity and index type, and the index strategy can be re-optimized and adjusted accordingly.

[0017] In summary, compared with the prior art, the present invention provides a query acceleration system and adaptive index construction method for columnar storage databases, which have the following beneficial effects: This invention uses a data compression module to compress and process monitoring data such as real-time video frame feature data and device logs, reducing storage and transmission costs; the parallel processing module divides the compressed data by column, and uses multi-threading and distributed computing to achieve parallel analysis of monitoring data from multiple regions and devices, thereby improving processing speed. The query load analysis module monitors and extracts high-frequency query characteristics from the park's surveillance system in real time; the adaptive indexing module combines a greedy algorithm to dynamically optimize the indexing strategy based on the query load, prioritizing the construction of efficient indexes for frequently accessed data columns; and the machine learning prediction module learns periodic query patterns to predict future query patterns in order to assist in index adjustments. The corresponding adaptive index construction method consists of five steps: preprocessing and cleaning monitoring data to remove invalid information and standardize the format; monitoring and analyzing query load to extract feature parameters; initially selecting an index strategy based on rules; iteratively optimizing based on a greedy algorithm, prioritizing efficiency for emergency scenarios; and finally constructing and dynamically adjusting the index based on real-time load and prediction results. This system and method solve the problems of insufficient flexibility and resource waste of static indexes, improving the query efficiency and system adaptability of park monitoring data. Attached Figure Description

[0018] Figure 1 This is a schematic diagram of the query acceleration system for the columnar storage database of this invention.

[0019] Figure 2 This is a flowchart illustrating the steps of the adaptive index construction method for columnar storage databases in this invention. Detailed Implementation

[0020] This invention provides a technical solution: a query acceleration system for columnar storage databases. Please refer to [link / reference]. Figure 1 and Figure 2 ,include: The data compression module is used to compress the database data stored in columnar arrays. The database data includes real-time video frame feature data generated by the park's smart monitoring, equipment operation logs, and personnel flow statistics, in order to reduce data storage space and improve data extraction speed. The parallel processing module has its input end electrically connected to the output end of the data compression module. It is used to receive the compressed data, divide the data into columns, and process the divided data through multi-threaded parallel reading and distributed computing. It supports parallel analysis of monitoring data of multiple areas and multiple devices in the park. The query load analysis module is electrically connected to the output of the parallel processing module. It is used to monitor and analyze the query load of the database in real time, and to extract high-frequency query features in the smart monitoring scenario of the park, such as historical data backtracking requests for specific monitoring areas and abnormal event related data queries, and extract the feature information of the query load. An adaptive indexing module, whose input is electrically connected to the output of the query load analysis module and the parallel processing module respectively, is used to dynamically select and optimize indexing strategies based on the characteristics of the query load and a greedy algorithm. It prioritizes building efficient indexes for high-frequency access monitoring data columns (such as abnormal event tag columns and equipment status columns) in smart park monitoring, and builds and adjusts the indexes based on the optimized indexing strategies. The machine learning prediction module has its input terminal electrically connected to the output terminal of the query load analysis module and its output terminal electrically connected to the input terminal of the adaptive index module. It is used to learn the periodic query patterns of the park's smart monitoring based on the characteristic information of historical query loads (such as querying personnel flow data during peak hours and calling nighttime security patrol data). It uses machine learning algorithms to predict future query patterns and sends the prediction results to the adaptive index module. The data compression module employs differentiated compression strategies for different types of data. For video frame feature data, a lightweight lossless compression algorithm (such as Snappy) is selected to reduce storage space while ensuring real-time performance. For text data such as equipment operation logs and personnel flow statistics, a lossy compression algorithm with a high compression ratio (such as ZSTD) is used to balance storage space and data accuracy by setting a reasonable compression level. The data compression module reduces storage space and improves extraction speed, laying the foundation for subsequent processing; the parallel processing module enables parallel analysis of monitoring data from multiple regions and devices through column partitioning and multi-threaded parallel computing, shortening processing time; the query load analysis module monitors high-frequency query characteristics in real time, providing a basis for index optimization; the adaptive indexing module dynamically builds efficient indexes using a greedy algorithm, prioritizing the query performance of frequently accessed columns; the machine learning prediction module learns periodic query patterns and adjusts indexing strategies in advance to adapt to future query patterns. These modules work closely together to ensure the system maintains high-efficiency and stable query performance even in complex monitoring scenarios.

[0021] Please see Figure 1 The data compression module employs multiple compression algorithms, automatically selecting the appropriate compression algorithm based on the data type and characteristics. For video frame feature data in the smart park monitoring, a lightweight compression algorithm is used to ensure real-time performance, while for historical alarm logs, a high compression ratio algorithm is used to save storage space. The compression algorithms include, but are not limited to, LZO compression algorithm, Snappy compression algorithm, and ZSTD compression algorithm. When implementing the data compression module, a mapping table between data types and compression algorithms can be preset. For example, video frame feature data can be defined to correspond to lightweight compression algorithms (such as Snappy), and historical alarm logs can correspond to high compression ratio algorithms (such as ZSTD). After receiving data, the module first identifies the data type through metadata or file header, and then automatically selects the compression algorithm according to the mapping table. If the data type is not in the mapping table, the most suitable algorithm is dynamically matched by analyzing data characteristics (such as data distribution and redundancy) to ensure a balance between compression efficiency and real-time performance. Lightweight algorithms are used for video frame feature data to ensure smooth real-time monitoring and avoid impacting abnormal event response due to compression and decompression delays. High compression ratio algorithms are used for historical alarm logs, which effectively saves storage space and extends the data retention period. The module supports algorithms such as LZO, Snappy, and ZSTD to cover different compression requirements, ensuring both compression speed and compression ratio. This flexible strategy reduces storage costs and indirectly improves query efficiency by optimizing data extraction speed, enabling the system to more efficiently support the monitoring data analysis needs of multiple areas and devices in the park.

[0022] Please see Figure 1The parallel processing module includes a task allocation unit and multiple computing nodes. The task allocation unit is used to allocate the segmented column data to different computing nodes. Priority is given to allocating emergency query tasks (such as real-time abnormal event location query) in the park's smart monitoring to idle computing nodes. Each computing node processes the allocated data in a multi-threaded parallel manner. After processing, the results are fed back to the task allocation unit for aggregation. For the task allocation unit, a priority queue algorithm can be used to dynamically adjust the task allocation order according to the urgency of the query task and the load of the computing nodes. When an urgent query task arrives, it is inserted at the head of the queue and given priority to be allocated to idle computing nodes. Each computing node uses multi-threading technology to further subdivide the allocated column data, with each thread processing a part of the data. Finally, the processing results of each thread are merged and fed back to the task allocation unit for summary. The parallel processing module offers numerous benefits. The task allocation unit can accurately distribute the segmented column data to appropriate computing nodes based on the characteristics of the query task and the status of the computing nodes. It prioritizes urgent query tasks in smart park monitoring, ensuring timely acquisition of critical information. Multiple computing nodes process data in parallel through multi-threading, significantly improving data processing efficiency and shortening query response time. After processing, the results are fed back to the task allocation unit for aggregation, ensuring data integrity and accuracy. This provides efficient and reliable data processing support for smart park monitoring, enhancing the overall performance and usability of the monitoring system.

[0023] Please see Figure 1 The query load analysis module extracts query load feature information including query frequency, columns involved in the query, query conditions, and query response time. The columns involved in the query include data columns unique to smart park monitoring, such as monitoring device ID, abnormal event type, timestamp, and area code. For the query load analysis module, when extracting query load characteristic information, the query frequency can be counted by setting a counter at the database query interface; the columns involved in the query can be obtained by using the database metadata information; the query conditions can be obtained by parsing the query statement; the time interval from initiating the query to obtaining the result can be recorded as the query response time. For data columns unique to smart monitoring in the park, these columns can be clearly identified in the database design, and the information of the relevant identified columns can be directly extracted during analysis. The query load analysis module extracts characteristic information such as query frequency, involved columns, conditions, and response time, and specifically identifies data columns unique to smart park monitoring. This offers significant advantages: it accurately tracks the frequency of different queries in park monitoring scenarios, providing a basis for resource allocation; identifying query columns allows for rapid data location, improving processing efficiency; understanding query conditions helps optimize query logic; and knowing response time enables timely detection of performance issues. Analysis of park-specific columns better meets the business needs of smart park monitoring, enhancing the stability and reliability of the overall monitoring system and ensuring safe and efficient park management.

[0024] Please see Figure 1 The adaptive indexing module also includes an index evaluation unit, which is used to evaluate the performance of the constructed index. The evaluation indicators include the query response latency in the smart monitoring scenario of the park (such as the response time of the abnormal event tracing query) and the index's utilization rate of storage resources. When the evaluation result does not meet the preset threshold, the selection and optimization process of the index strategy is retried. The index evaluation unit periodically collects query response latency data (such as recording abnormal events through a built-in timer to trace the start and end times of queries) and index storage usage data (such as obtaining the index file size through the file system API), and compares them with preset thresholds. If any indicator exceeds the limit, a re-optimization signal is sent to the query load analysis module through an internal interface, triggering the iterative process of the index strategy. In smart park monitoring scenarios, the real-time performance of abnormal event tracing and querying directly affects emergency response efficiency, while storage occupancy rate impacts the long-term operating costs of the system. The index evaluation unit can monitor these two core indicators in real time and automatically trigger index strategy reconstruction when performance degrades, avoiding the lag of manual intervention. For example, during peak periods of holiday traffic, the system can prioritize the response speed of high-frequency queries while reducing storage overhead by dynamically adjusting the index granularity. This adaptive mechanism ensures that the index structure always matches the actual query load, improving the retrieval efficiency of monitoring data and optimizing system resource utilization, providing stable technical support for smart park management.

[0025] For an adaptive indexing method for columnar storage databases, and a query acceleration system based on the aforementioned columnar storage database, please refer to [link / reference]. Figure 1 and Figure 2 It includes the following steps: Step 1: Data preprocessing. Clean and organize the data in the columnar storage database, remove invalid frame information and duplicate device logs from the smart monitoring data of the park, remove invalid and redundant data, and standardize the data format. Step 2: Query load monitoring and analysis. Monitor database query requests in real time, focusing on collecting query commands initiated by the park's smart monitoring platform (such as historical video access requests from security personnel, abnormal behavior correlation analysis queries automatically triggered by the system, etc.), collect query load information, analyze the collected query load information, and extract the characteristic parameters of the query load. Step 3: Preliminary selection of indexing strategy. Based on the query load characteristic parameters extracted in Step 2 and combined with the preset index selection rules, the applicable indexing strategy is initially determined for the monitoring data columns that are frequently queried in the park's smart monitoring (such as personnel face recognition feature columns and equipment fault code columns). Step 4: Optimize the indexing strategy based on the greedy algorithm. With the goal of maximizing query efficiency, the initially determined indexing strategy is iteratively optimized using the greedy algorithm. During the optimization process, priority is given to ensuring query efficiency in emergency scenarios in the park (such as rapid retrieval of monitoring data for sudden events) to obtain the optimal indexing strategy. Step 5: Build and dynamically adjust the index. Build the index according to the optimal indexing strategy, and dynamically adjust the index structure based on real-time changes in query load and the future query pattern prediction results provided by the machine learning prediction module, such as the query trend of monitoring data during peak hours of park traffic on holidays. For smart monitoring data in the park, a threshold for the integrity of video frame information is set, and frames below this value are considered invalid. For device logs, the device running time is compared with the log recording time, and logs with a time difference exceeding a preset range are considered duplicate logs. At the same time, a unified data format template is developed to convert various types of data into a standard format to ensure data consistency. In the data preprocessing stage, data cleaning and organization, eliminating invalid and redundant information and standardizing formats can improve data quality and lay the foundation for subsequent processing. Query load monitoring and analysis can accurately grasp the database query status, extract feature parameters, and provide a basis for index strategy selection. Preliminary selection of index strategy can quickly locate the applicable index method and improve efficiency. Based on the greedy algorithm, the index strategy is optimized with the goal of maximizing query efficiency, ensuring query efficiency in emergency scenarios, and making the index more in line with actual needs. The index is built and dynamically adjusted, and the structure is adjusted according to real-time query load and future prediction results to ensure that the index is always efficient, improve database query performance, and meet the complex and ever-changing query needs of smart monitoring in the park.

[0026] Please see Figure 2 When analyzing the query load information in step two, it also includes calculating the priority of different queries. The priority calculation is based on the query frequency, the amount of data involved in the query, and the priority weight set by the user. The priority weight set by the user is configured according to the business needs of the park's smart monitoring. For example, the weight of emergency command queries is higher than that of daily statistical queries. When analyzing and calculating different query priorities for query load information in step two, you can first set the query frequency and the basic score calculation method for the amount of data involved in the query, such as assigning scores linearly increasing by the number of queries, or assigning scores in segments according to the size of the data. For the priority weights set by the user, preset weight templates for common smart monitoring business scenarios in parks (such as emergency command, daily statistics, etc.) are provided. Users can also customize weight values ​​according to actual needs. The system calculates the final priority by multiplying the weight by the basic score. Priority is calculated based on query frequency, data volume, and user-defined weights, enabling more precise allocation of system resources. This ensures that important queries (such as emergency command queries) are processed first, improving the system's response speed to critical business needs. Users can flexibly configure weights according to the needs of smart park monitoring, enhancing the system's adaptability and relevance. This priority calculation method helps optimize query processing, improves overall system efficiency, ensures the timeliness and accuracy of queries in various smart park monitoring scenarios, and provides stronger data support for park management.

[0027] Please see Figure 2 The specific process of optimizing the indexing strategy using a greedy algorithm in step four is as follows: Step 1: Initialize the index strategy set. Add the initially determined index strategies as initial elements to the set. The set contains candidate index schemes for different data types in smart monitoring of the park. Step 2: Select the current optimal indexing strategy from the set of indexing strategies as a candidate strategy; Step 3: Make partial adjustments to the candidate strategies and generate new indexing strategies, such as increasing the indexing granularity of monitoring data columns for key areas of the park (such as entrances and exits, equipment rooms); Step 4: Calculate the performance metrics of the new indexing strategy. If the performance metrics are better than the current best indexing strategy, then update the best indexing strategy. Step 5: Repeat steps 2, 3 and 4 until the preset number of iterations is reached or the performance index no longer improves. The final optimal indexing strategy is taken as the optimization result. For the calculation of performance indicators in step four, which uses a greedy algorithm to optimize the index strategy, query response latency and the index's storage resource utilization rate can be used as evaluation indicators. Query response latency is obtained by recording the time difference from initiating a query to obtaining the result, and storage resource utilization rate is obtained by calculating the ratio of the storage space occupied by the index to the total storage space. This is used to determine whether the new index strategy is more efficient. By initializing a set of candidate index schemes containing different data types, the diverse data of smart monitoring in the park can be fully considered, providing a rich foundation for optimization. Secondly, the current best option is selected from the set as a candidate and locally adjusted to generate a new strategy. This allows for targeted optimization, such as the index granularity of monitoring data columns in key areas. Furthermore, the performance indicators of the new strategy are calculated and compared with the current best option to ensure the selection of a more performant strategy. Finally, the operation is repeated until the preset number of iterations is reached or the performance no longer improves, thus obtaining the optimal index strategy. This effectively improves the query efficiency of the smart monitoring database in the park and meets the requirements for real-time performance and accuracy.

[0028] Please see Figure 2 When dynamically adjusting the index structure in step five, an incremental update method is used, which only updates the index part corresponding to the data that has changed. If the real-time data column of the newly added monitoring device and the code column after the adjustment of the park area change, there is no need to rebuild the entire index synchronously. When dynamically adjusting the index structure in step five using incremental updates, this can be achieved by establishing a data change monitoring mechanism. By utilizing the database's built-in triggers or log recording functions, changes such as the insertion of new monitoring equipment data columns and the modification of park area code columns can be captured in real time. When a change is detected, the system automatically locates the index portion corresponding to the affected data and only updates that portion of the index, such as recalculating index key values ​​or adjusting index node pointers, to ensure the consistency between the index and the data without rebuilding the entire index. By updating only the index portion corresponding to the changed data, the huge computational overhead and time consumption caused by synchronously rebuilding the entire index are avoided, greatly improving the efficiency of index adjustment and enabling the system to respond to data changes more quickly. On the other hand, for situations such as adding real-time data columns for new monitoring devices and changes in park area code columns, there is no need to rebuild the entire index, reducing the occupation of storage resources and lowering system maintenance costs. At the same time, this incremental update method ensures the real-time performance and accuracy of the index, which helps to improve database query performance and provides strong support for efficient data retrieval in smart park monitoring scenarios, ensuring that the required data can be quickly obtained under various complex query requirements.

[0029] Please see Figure 2 Step five also includes verifying the dynamically adjusted index structure by testing the performance of the index structure through simulated queries. The simulated queries include typical scenarios of smart monitoring in the park (such as cross-regional personnel trajectory tracking queries and multi-device collaborative linkage queries). If the performance does not meet expectations, the index strategy will be optimized and adjusted again. In step five, when verifying the dynamically adjusted index structure, if a simulated query is used to test the performance, a simulated query environment can be constructed to simulate the real smart monitoring business scenario and data scale of the park. Commands such as cross-regional personnel trajectory tracking query and multi-device collaborative linkage query similar to actual query commands can be generated. Query response time, accuracy and other indicators can be recorded. If the performance does not meet expectations, the bottlenecks of the index structure in processing these queries can be analyzed, such as index granularity and index type. Based on this, the index strategy can be re-optimized and adjusted. Validating the dynamically adjusted index structure has several benefits. By simulating queries in typical smart park monitoring scenarios, such as cross-regional personnel tracking and multi-device collaborative queries, the performance of the index structure in actual business operations can be accurately tested. If the performance does not meet expectations, the index strategy can be optimized and adjusted in a timely manner to ensure that the index always adapts to actual query needs, guaranteeing query efficiency and accuracy. This helps improve the data processing capabilities of the smart park monitoring system, enabling the system to respond to various monitoring query commands more quickly and accurately, providing strong support for park security management and equipment maintenance, and enhancing the system's stability and reliability.

[0030] It should be noted that, in this document, relational terms such as "first" and "second" are used only to distinguish one entity or operation from another, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Furthermore, the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, article, or apparatus.

[0031] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A query acceleration system for columnar storage databases, characterized in that, include: The data compression module is used to compress the database data stored in columnar arrays. The database data includes real-time video frame feature data generated by the park's smart monitoring system, equipment operation logs, and personnel flow statistics. The parallel processing module has its input end electrically connected to the output end of the data compression module. It is used to receive the compressed data, divide the data into columns, and process the divided data through multi-threaded parallel reading and distributed computing. It supports parallel analysis of monitoring data of multiple areas and multiple devices in the park. The query load analysis module is electrically connected to the output of the parallel processing module. It is used to monitor and analyze the query load of the database in real time, and to extract high-frequency query features in the smart monitoring scenario of the park. An adaptive indexing module, whose input is electrically connected to the output of the query load analysis module and the parallel processing module respectively, is used to dynamically select and optimize the indexing strategy based on the characteristics of the query load and a greedy algorithm. It prioritizes the construction of efficient indexes for the monitoring data columns that are frequently accessed in the smart monitoring of the park, and builds and adjusts the indexes based on the optimized indexing strategy. The machine learning prediction module has its input terminal electrically connected to the output terminal of the query load analysis module and its output terminal electrically connected to the input terminal of the adaptive index module. It is used to learn the periodic query patterns of the park's smart monitoring based on the characteristic information of historical query loads, use machine learning algorithms to predict future query patterns, and send the prediction results to the adaptive index module.

2. The query acceleration system for columnar storage database according to claim 1, characterized in that: The data compression module employs multiple compression algorithms, automatically selecting the appropriate compression algorithm based on the data type and characteristics. For video frame feature data in the smart park monitoring, a lightweight compression algorithm is used, while for historical alarm logs, a high compression ratio algorithm is used. The compression algorithms include, but are not limited to, LZO compression algorithm, Snappy compression algorithm, and ZSTD compression algorithm.

3. The query acceleration system for columnar storage database according to claim 1, characterized in that: The parallel processing module includes a task allocation unit and multiple computing nodes. The task allocation unit is used to allocate the segmented column data to different computing nodes, prioritizing the allocation of emergency query tasks in the park's smart monitoring to idle computing nodes. Each computing node processes the allocated data in a multi-threaded parallel manner, and after processing, the results are fed back to the task allocation unit for aggregation.

4. The query acceleration system for columnar storage database according to claim 1, characterized in that: The query load analysis module extracts query load characteristic information including query frequency, columns involved in the query, query conditions, and query response time. The columns involved in the query include data columns unique to smart park monitoring, such as monitoring device ID, abnormal event type, timestamp, and area code.

5. The query acceleration system for columnar storage database according to claim 1, characterized in that: The adaptive indexing module also includes an index evaluation unit, which is used to evaluate the performance of the constructed index. The evaluation indicators include query response latency in the smart monitoring scenario of the park and the index's utilization rate of storage resources. When the evaluation result does not meet the preset threshold, the index strategy selection and optimization process is re-triggered.

6. An adaptive index construction method for columnar storage databases, based on the query acceleration system for columnar storage databases as described in any one of claims 1-5, characterized in that, Includes the following steps: Step 1: Data preprocessing. Clean and organize the data in the columnar storage database, remove invalid frame information and duplicate device logs from the smart monitoring data of the park, remove invalid and redundant data, and standardize the data format. Step 2: Query load monitoring and analysis. Monitor database query requests in real time, focusing on collecting query commands initiated by the park's smart monitoring platform, collecting query load information, and analyzing the collected query load information to extract the characteristic parameters of the query load. Step 3: Preliminary selection of indexing strategy. Based on the query load characteristic parameters extracted in Step 2 and combined with the preset index selection rules, the applicable indexing strategy is initially determined for the monitoring data columns that are frequently queried in the smart monitoring of the park. Step 4: Optimize the indexing strategy based on the greedy algorithm. With the goal of maximizing query efficiency, the initially determined indexing strategy is iteratively optimized using the greedy algorithm. During the optimization process, priority is given to ensuring query efficiency in emergency scenarios within the park, and the optimal indexing strategy is obtained. Step 5: Build and dynamically adjust the index. Build the index according to the optimal indexing strategy, and dynamically adjust the index structure based on real-time changes in query load and the future query pattern prediction results provided by the machine learning prediction module.

7. The adaptive index construction method for columnar storage database according to claim 6, characterized in that: When analyzing the query load information in step two, the priority of different queries is also calculated. The priority is calculated based on the query frequency, the amount of data involved in the query, and the priority weight set by the user. The priority weight set by the user is configured according to the business needs of the park's smart monitoring.

8. The adaptive index construction method for columnar storage database according to claim 6, characterized in that: The specific process of optimizing the indexing strategy using a greedy algorithm in step four is as follows: Step 1: Initialize the index strategy set. Add the initially determined index strategies as initial elements to the set. The set contains candidate index schemes for different data types in smart monitoring of the park. Step 2: Select the current optimal indexing strategy from the set of indexing strategies as a candidate strategy; Step 3: Make local adjustments to the candidate strategies to generate new indexing strategies; Step 4: Calculate the performance metrics of the new indexing strategy. If the performance metrics are better than the current best indexing strategy, then update the best indexing strategy. Step 5: Repeat steps 2, 3, and 4 until the preset number of iterations is reached or the performance index no longer improves. The final optimal indexing strategy is then taken as the optimization result.

9. The adaptive index construction method for columnar storage database according to claim 6, characterized in that: When dynamically adjusting the index structure in step five, an incremental update method is used, updating only the index portion corresponding to the changed data. If the real-time data column of the newly added monitoring device or the code column after the adjustment of the park area changes, there is no need to rebuild the entire index synchronously.

10. The adaptive index construction method for columnar storage database according to claim 6, characterized in that: Step five also includes verifying the dynamically adjusted index structure by testing the performance of the index structure through simulated queries. The simulated queries include typical scenarios of smart monitoring in the park. If the performance does not meet expectations, the index strategy will be optimized and adjusted again.

Images