Farmland fertility data acquisition and analysis system based on data mining

Abstract

The invention relates to the technical field of data mining, in particular to a farmland fertility data acquisition and analysis system based on data mining, and the system comprises a query log analysis module which continuously records user query logs and generates a query log feature set. According to the method, high-frequency access features and repeated query structures in a user query log are continuously recorded and analyzed, a hotspot data area is dynamically identified, an index adjustment instruction is generated, and the index density and cache space distribution of a database physical structure are optimized in combination with a cache priority strategy updated in real time; redundant data retrieval and disk I / O operation are reduced, and query delay is reduced. Quality scoring is carried out on farmland fertility data according to core indexes such as a pH value, organic matter content and sampling depth, a hierarchical storage strategy is established, high-credibility data is distributed to a high-performance storage medium and matched with a low-delay compression algorithm, low-frequency data is distributed to a low-cost storage medium and a high-compression-rate algorithm is adopted.

Description

Technical Field

[0001] The present invention relates to the technical field of data mining, and in particular to a farmland fertility data acquisition and analysis system based on data mining. Background Art

[0002] Data mining is a technical field that extracts implicit, unknown, and potentially valuable information or patterns from large-scale datasets. Its core tasks include data preprocessing, pattern discovery, association analysis, classification prediction, and clustering analysis, etc.

[0003] Existing data mining technologies lack a fine-grained quality stratification mechanism. They only process heterogeneous data through a single compression algorithm or storage medium, and high-value data and low-value data are stored mixedly. This not only fails to ensure the fast access of high-priority data but also makes it difficult to compress the storage cost of low-frequency data. The query optimization process does not integrate data quality constraint conditions, and the execution plan only relies on basic indexes or full-scan. When facing a multi-level storage structure, it may repeatedly access low-quality data or trigger the cold storage thawing process, increasing unnecessary I / O consumption and the risk of query timeout. Therefore, improvements are needed. Summary of the Invention

[0004] The purpose of the present invention is to solve the deficiencies existing in the prior art, and a farmland fertility data acquisition and analysis system based on data mining is proposed.

[0005] To achieve the above purpose, the present invention adopts the following technical solutions: The farmland fertility data acquisition and analysis system based on data mining includes: A query log analysis module, continuously recording user query logs, generating a query log feature set, analyzing the query log feature set, identifying repeatedly occurring query structures, and obtaining a recognition result of hot data regions; An adaptive structure optimization module, based on the recognition result of the hot data regions, determining the farmland fertility data indexes that need to be created, modified, or deleted, generating a database index adjustment instruction set, executing the database index adjustment instruction set, and adjusting the data cache settings according to the recognition result of the hot data regions to establish a dynamically optimized database physical structure; A data quality stratification module, receiving the input farmland fertility data, assigning a quality level to each piece of farmland fertility data according to a preset quality evaluation standard, establishing a data quality stratification mapping table, and reorganizing the storage of farmland fertility data by using the data quality stratification mapping table to construct a multi-resolution farmland fertility data storage structure; The quality perception query module parses the farmland fertility data query request submitted by the user, extracts the quality requirements in the query statement, filters the farmland fertility data levels that meet the quality requirements according to the multi-resolution farmland fertility data storage structure, generates a quality constraint query execution plan, and based on the quality constraint query execution plan, performs data retrieval on the data levels of the dynamically optimized database physical structure and the selected multi-resolution farmland fertility data storage structure to obtain a query result set.

[0006] Preferably, the steps for obtaining the hot data area identification result are as follows: Continuously record the user query logs, extract the query frequency, calculation overhead, accessed farmland fertility data spatial range and time range of each query, and generate a query log feature set; Based on the query log feature set, calculate the query density index of each query structure; According to the query density index, filter the query structures whose query density index is greater than or equal to the average value of all query density indexes, combine the overlapping areas of the spatial range and the time range, identify the query structures that appear repeatedly more than the preset threshold, and generate the hot data area identification result.

[0007] Preferably, the steps for obtaining the database index adjustment instruction set are as follows: Based on the hot data area identification result, extract the response time, query hit rate, and storage overhead of the current database index, and generate the current index status set; According to the current index status set, calculate the support efficiency index of each index; Filter the indexes whose support efficiency index is less than 50% of the average value of all index support efficiency indexes and determine them as indexes to be deleted. Filter the indexes whose support efficiency index is between 50% and 100% of the average value and whose query hit rate is lower than 60% and determine them as indexes to be modified. Generate a creation instruction for the index gap that does not cover the hot area and whose query hit rate is lower than 30%, and generate a database index adjustment instruction set.

[0008] Preferably, the steps for obtaining the dynamically optimized database physical structure are as follows: Execute the delete, modify, and create instructions in the database index adjustment instruction set, parse the operation type field, index name field, and associated data table field of each instruction in the instruction set, traverse the index name field list, call the database engine interface to execute the corresponding operation, record the operation execution status, execution timestamp, and exception error code, and generate a database index operation log set; Based on the hotspot data region recognition result, count the number of accesses of each data block in the past 24 hours, filter out the data blocks whose access times exceed the preset threshold, extract the storage path and the hash value of the associated query result cache copy, sort them in descending order of access times, assign cache priority levels, and generate a high-frequency data cache mapping table; According to the execution status field of the database index operation log set and the cache priority level field of the high-frequency data cache mapping table, adjust the index density parameter, cache partition size, and preloading strategy of the database storage engine, force the data blocks of level 1 in the high-frequency data cache mapping table to be resident in memory and lock the cache eviction mechanism, dynamically allocate the remaining cache space to the data blocks of level 2 and level 3 and enable the LRU replacement strategy, and generate a dynamically optimized database physical structure.

[0009] Preferably, the steps for obtaining the data quality hierarchical mapping table are as follows: Receive the input farmland fertility data, extract the pH value, organic matter content, conductivity, sampling depth, and data timeliness of each piece of data, and generate an original data quality deviation set by comparing with the ideal pH value range, lower limit of organic matter content, upper limit of conductivity, standard sampling depth, and maximum allowable time difference in the preset quality evaluation criteria; Based on the original data quality deviation set, calculate the quality deviation degree of each piece of data; According to the quality deviation degree, filter out the data with a quality deviation degree less than or equal to the preset first-level threshold and assign it to quality level 1, the data with a quality deviation degree between the first-level threshold and the second-level threshold and assign it to quality level 2, and the data with a quality deviation degree greater than the second-level threshold and assign it to quality level 3, and establish a data quality hierarchical mapping table.

[0010] Preferably, the steps for obtaining the multi-resolution farmland fertility data storage structure are as follows: Based on the quality level classification field in the data quality hierarchical mapping table, match the preset storage strategy configuration template, where quality level 1 corresponds to an index density of 95%, a storage medium type of SSD, and a compression algorithm of LZ4, quality level 2 corresponds to an index density of 80%, a storage medium type of HDD, and a compression algorithm of Zstd, and quality level 3 corresponds to an index density of 70%, a storage medium type of cold storage, and a compression algorithm of Gzip, and generate a data quality level storage strategy configuration table; According to the index density parameter in the data quality level storage strategy configuration table, gradually adjust the index fill factor and B-tree depth of the corresponding data tables in the order of quality levels 1 to 3, and at the same time migrate the quality level 1 data to the SSD partition, the quality level 2 data to the HDD partition, and the quality level 3 data to the cold storage partition, and generate a storage medium allocation record and an index parameter update log; Based on the storage medium, allocate records and index parameter update logs, deploy storage access routing rules, and generate a multi-resolution farm fertility data storage structure.

[0011] Preferably, the step of obtaining the quality constraint query execution plan is as follows: Parse the farm fertility data query request submitted by the user, match the quality constraint keywords in the query statement through regular expressions, extract the quality level requirement, time range limit field, and space range limit field, and generate a set of quality requirement constraint conditions; Based on the quality level thresholds in the set of quality requirement constraint conditions, call the multi-resolution farm fertility data storage structure, match the SSD storage path corresponding to quality level 1, the HDD storage path corresponding to quality level 2, and the cold storage path corresponding to quality level 3, filter the list of data block addresses that meet the quality level thresholds and whose storage medium types are consistent with the query performance requirements, and generate a storage medium and data block address mapping table; According to the storage path fields in the storage medium and data block address mapping table, combined with the index parameter snapshot and compression algorithm configuration items of the multi-resolution storage structure, define the data access order and decompression algorithm call chain, and generate a quality constraint query execution plan.

[0012] Preferably, the step of obtaining the query result set is as follows: Based on the storage path priority fields in the quality constraint query execution plan, call the storage medium access interface that dynamically optimizes the database physical structure, perform data block reading in the order of priority, trigger the thawing process for the cold storage path and monitor the thawing completion status, record the reading timestamp and storage medium type of each data block, and generate a data block retrieval record set; According to the storage medium type fields in the data block retrieval record set, perform real-time decompression of LZ4 on the SSD path data, perform streaming decompression of Zstd on the HDD path data, and perform batch decompression of Gzip on the cold storage path data, merge the decompressed data blocks and eliminate duplicate timestamp records, and generate an integrated farm fertility data set; Based on the integrated farm fertility data set, sort by the time range interval field and encapsulate it in a standardized JSON format to generate a query result set.

[0013] Compared with the prior art, the advantages and positive effects of the present invention are as follows: In the present invention, by continuously recording and analyzing the high-frequency access features and repeated query structures in the user query log, the hot data area is dynamically identified and an index adjustment instruction is generated. Combining with the real-time updated cache priority policy, the index density of the database physical structure and the cache space allocation are optimized, redundant data retrieval and disk I / O operations are reduced, and the query latency is decreased. The farmland fertility data is quality scored according to core indicators such as pH value, organic matter content, sampling depth, etc., and a hierarchical storage strategy is established. The highly reliable data is allocated to high-performance storage media and matched with a low-latency compression algorithm, and the low-frequency data is allocated to low-cost storage media and a high-compression ratio algorithm is adopted to achieve the balance of storage resource utilization rate and access efficiency. During the query execution process, by parsing the quality constraint conditions and associating with the hierarchical storage path, the highly reliable data block is preferentially accessed, and the performance bottleneck of the low-speed storage media is avoided by combining asynchronous preloading and fusing mechanism, the response speed and result accuracy of complex queries are improved, and at the same time, redundant data transmission and calculation overhead are reduced. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Figure 1 It is a system flow chart of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0015] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0016] Please refer to Figure 1 , the technical solution provided by the present invention is: a farmland fertility data acquisition and analysis system based on data mining includes: A query log analysis module, continuously recording the user query log, generating a query log feature set, analyzing the query log feature set, identifying the repeatedly occurring query structures, and obtaining the identification result of the hot data area; An adaptive structure optimization module, based on the identification result of the hot data area, determining the farmland fertility data indexes that need to be created, modified or deleted, generating a database index adjustment instruction set, executing the database index adjustment instruction set, and adjusting the data cache setting according to the identification result of the hot data area to establish a dynamically optimized database physical structure; A data quality stratification module, receiving the input farmland fertility data, allocating a quality level to each piece of farmland fertility data according to a preset quality evaluation standard, establishing a data quality stratification mapping table, reorganizing the storage of the farmland fertility data by using the data quality stratification mapping table, and constructing a multi-resolution farmland fertility data storage structure; The quality-aware query module parses the farmland fertility data query request submitted by the user, extracts the quality requirements in the query statement, filters the levels of farmland fertility data that meet the quality requirements according to the multi-resolution farmland fertility data storage structure, generates a quality-constrained query execution plan, and executes data retrieval on the data levels of the dynamically optimized database physical structure and the selected multi-resolution farmland fertility data storage structure according to the quality-constrained query execution plan to obtain the query result set.

[0017] The steps for obtaining the identification result of the hot data area are as follows: Continuously record the user query logs, extract the query frequency, calculation overhead, spatial range and time range of the farmland fertility data accessed for each query, and generate a query log feature set; Based on the query log feature set, calculate the query density index for each query structure. The calculation formula is: ; Among them, is the query density index, is the frequency of the th query, is the area of the spatial range (unit: square kilometers), is the span of the time range (unit: days), is the maximum calculation overhead (unit: seconds), is the minimum calculation overhead (unit: seconds), is a constant to prevent division by zero, is the total number of entries in the query log feature set within the current analysis window; According to the query density index, filter the query structures whose query density index is greater than or equal to the average value of all query density indexes, combine the overlapping areas of the spatial range and the time range, identify the query structures whose repeated occurrence times exceed the preset threshold, and generate the identification result of the hot data area.

[0018] Specifically, the system background service continuously monitors and records all farmland fertility data query requests submitted by users through the interface. Each original log entry includes the user identifier, query submission timestamp, full query statement text, query execution status, and the execution time consumed returned by the database. Using a log parser, for each successful query log, first, count the number of times it appears within a preset time window (e.g., the past 1 hour) to obtain the query frequency; second, directly extract the database execution time consumed recorded in the log (unit converted to seconds) as the calculation overhead; third, use regular expression or structured query language (SQL) parsing technology to identify and extract the spatial range limitation information from the conditional clause (such as the WHERE clause) of the query statement, such as geographical coordinate points, rectangular areas, or polygon vertex coordinates, and calculate the area covered by this spatial range through a geometric calculation library (such as GEOS), with the unified unit being square kilometers. If the query does not specify a spatial range, it is marked as a global range and given a maximum area value representing the entire region or a specific identifier; similarly, parse the time range limitation information in the query statement, extract the start date and end date, and calculate the number of days between them. If the time range is not specified, it is marked as the entire time period and given a maximum time span value or a specific identifier; finally, associate the information of the four dimensions of query frequency, calculation overhead, spatial range area, and time range span extracted with the structured representation of the query statement (e.g., the query template after removing specific numerical parameters), and organize it into a structured data record, such as forming an entry containing fields such as {query structure identifier, frequency, average overhead, maximum overhead, minimum overhead, spatial range descriptor, time range descriptor}. All these entries together form a query log feature set.

[0019] Formula: , the benefit of the formula is that by comprehensively considering the influence of the query frequency ( ), spatial range ( ), and time span ( ) (the numerator part, taking the square root is to smooth the influence of extreme values), and adjusting it with the stability of the query execution efficiency (the denominator part, that is, the variation range of the calculation overhead), it can more accurately identify those query patterns that not only have a large number of accesses and a wide influence range, but also have relatively stable execution overhead. These patterns often correspond to the data areas that users really care about and have continuous value, that is, the hot data areas, avoiding the deviation that may be brought by relying solely on a single index such as frequency or overhead. For example, even if a query with a huge fluctuation in execution overhead has a high frequency, its value will be effectively reduced, reducing the possibility of being misjudged as a stable hot spot, thus providing a more reliable basis for subsequent index optimization and caching strategies.

[0020] The parameter is the The frequency of the next query. The obtaining steps are as follows: The system monitors in real time the total number of times a specific query structure (for example, queries with the same SELECT fields, FROM clause, and WHERE condition structure, but specific parameter values may be different) is executed within a defined time window (for example, the last 15 minutes). By analyzing the query log database, the log entries within a specific time window are grouped and counted according to the query structure. For example, between 14:00 and 14:15 on April 27, 2025, the query with the structure "Query the nitrogen, phosphorus, and potassium content of a specified plot in the past year" was executed 120 times. Then times.

[0021] The parameter is the area of the spatial range involved in the next query, in square kilometers. The obtaining steps are as follows: Analyze the spatial constraint conditions in the query statement (such as the geographical coordinate range or the list of plot IDs in the WHERE clause). For the geographical coordinate range, use a Geographic Information System (GIS) to calculate the covered area. For the list of plot IDs, query the pre-stored plot information table to obtain the area of each plot and sum them up. For example, for a certain query with a limited range of a rectangular area from longitude 116.3 to 116.4 and latitude 39.9 to 40.0, the calculated area is approximately the adjustment factor of 111 square kilometers multiplied by cos(39.95 degrees), which is approximately equal to 85.2 square kilometers. Then square kilometers.

[0022] The parameter is the time range span involved in the next query, in days. The obtaining steps are as follows: Analyze the time constraint conditions in the query statement (such as the date range in the WHERE clause), extract the start date and the end date , calculate the number of days difference plus 1. For example, if the query time range is from '2024-01-01' to '2024-12-31', then the time span is (December 31, 2024 - January 1, 2024) number of days + 1 = 365 + 1 = 366 days (considering leap years). Then days.

[0023] The parameter is the maximum calculation overhead among all instances of the st query structure executed within the current analysis window, in seconds. The obtaining steps are as follows: The query log records the specific execution time of each query. Filter out all those belonging to the For the log entries of a query structure, find the maximum value of the recorded execution time. For example, within the analysis window, the query structure was executed 10 times, and the execution times were [0.5, 0.6, 0.55, 0.7, 0.62, 0.58, 0.65, 0.71, 0.59, 0.66] seconds. Then seconds.

[0024] The parameter is the minimum computational overhead among all instances of the th query structure executed within the current analysis window, in seconds. The steps to obtain it are similar to those for obtaining the maximum overhead. Filter out all log entries within the current analysis window that belong to the th query structure, find the minimum value of the recorded execution time. Using the above example, seconds.

[0025] The parameter is a non-zero constant prevention value, a preset extremely small positive number used to prevent the situation where the denominator is zero. For example, if the system timing accuracy is in milliseconds (0.001 seconds) and the expected overhead difference is usually greater than 0.01 seconds, it can be set to .

[0026] The parameter is the total number of entries in the query log feature set within the current analysis window, that is, the total number of queries that occurred within this window. The steps to obtain it are to directly count the total number of query log records within the analysis window. For example, within a 15-minute analysis window, a total of 5000 query logs were recorded. Then .

[0027] Substitute the parameters into the formula to calculate the query density index of the query structure value is .

[0028] Based on the calculated query density indices of each query structure, first calculate the arithmetic mean of all values, and then filter out all those that satisfy The query structure forms a list of candidate query structures. Then, for each query structure in the list, its associated spatial range (e.g., geographical polygon coordinates) and time range (start and end dates) are extracted and compared pairwise. Using geometric intersection algorithms (such as determining whether polygons have overlapping areas) and time interval intersection judgments (judging whether two time periods have a common part), combinations of query structures with overlapping access in space and time and their corresponding overlapping areas (spatiotemporal ranges) are identified. A counter is maintained for each identified independent overlapping spatiotemporal area to record the number of different query structures accessing this area or the total access frequency (defining the number of repetitions according to specific strategies, here the number of structures is used). Then, the number of repeated accesses to each overlapping area is compared with a preset threshold for comparison. This threshold is set with reference to historical data analysis. For example, the frequency distribution of each data area accessed by different query structures in the past month is statistically analyzed, and the 85th percentile of its distribution is taken as the threshold. If the calculated 85th percentile is 5, then set , which means that a spatiotemporal area needs to be accessed by at least 5 different high-density query structures ( ) to be considered "hot" enough. Finally, all query structures with the number of repeated occurrences exceeding (e.g., greater than or equal to 5 times) and their associated overlapping spatiotemporal areas are summarized to form a structured list of hot data area identification results. Each entry contains a description of the spatial range, a description of the time range, and a list of query structure identifiers accessing this area.

[0029] The steps to obtain the database index adjustment instruction set are as follows: Based on the hot data area identification results, the response time, query hit rate, and storage overhead of the current database index are extracted to generate the current index status set; According to the current index status set, the support efficiency index of each index is calculated. The calculation formula is: ; where is the support efficiency index, is the response time (unit: seconds) of the th index, is the query hit rate (percentage) of the th index, is the storage overhead (unit: GB) of the th index, is the area of the spatial range (unit: square kilometers) of the th area in the hot data area identification results, is the total number of hot areas; Indexes with a support efficiency index less than 50% of the average of all index support efficiency indexes are determined to be indexes to be deleted. Indexes with a support efficiency index between 50% and 100% of the average and a query hit rate lower than 60% are determined to be indexes to be modified. Create instructions are generated for index gaps that do not cover hot regions and have a query hit rate lower than 30%, and a database index adjustment instruction set is generated.

[0030] Specifically, based on the query structure and spatio-temporal range information contained in the hot data area recognition results generated in the previous step, the system traverses all existing indexes in the current database that serve data tables related to farmland fertility. For each index, it calls the built-in performance views and statistical information interfaces of the database (for example, querying pg_stat_user_indexes, pg_relation_size(), and analyzing the output of EXPLAIN ANALYZE in PostgreSQL) to extract its key performance indicators: First, record the current disk storage space occupied by the index, convert it to gigabytes (GB) to obtain the storage overhead; Second, statistically analyze the query plans related to the index table over a period of time (such as the past 24 hours), calculate the ratio of the number of index scans (IndexScan) to the total number of scans (including sequential scans SeqScan and index scans), or directly use the index usage statistics provided by the database (such as idx_scan count) to obtain the query hit rate (percentage); Third, for those queries that actually use the index (confirmed by the query plan), record their average execution time, especially paying attention to those queries that match the query structure of the identified hot data area, and calculate their weighted average response time (the weight can be based on the query density index of the hot area or other business importance metrics), in seconds, to obtain the response time; Integrate the information such as the name, belonging table, response time, query hit rate, and storage overhead of each index into a record, and finally gather all the index records to generate a structured set of the current index status for subsequent efficiency evaluation.

[0031] Formula: , The benefit of the formula is that it establishes a quantitative model for comprehensively evaluating the value of database indexes, considering not only the usage efficiency (query hit rate ) and performance (response time ) of the index itself, but also combining the resource cost of the index (storage overhead ) and its support for core business requirements (reflected by the total area of the hot data area ). The numerator part represents the benefit of the index. Indexes with high hit rates and covering a large area of hot regions have higher scores. Using the natural logarithm It can smooth the impact of the total area of hot spots and avoid the dominance of extreme values in the results; the denominator part represents the cost or price of the index. The longer the response time and the greater the storage overhead of the index, the lower the score. For the storage overhead take the cube root is to reduce the penalty for large indexes, recognize the possible necessity they bring, but still constrain their resource consumption. Through this efficiency metric it is possible to more comprehensively and balancedly judge the actual contribution and cost of an index, providing an objective basis for the optimization decision (deletion, modification, creation) of the index; The parameter is the response time of the th index, in seconds. The acquisition step is to extract the response time value of the th index recorded from the generated current index status set. This value is the average or weighted average execution time measured by monitoring and analyzing the queries actually using the th index executed by the database within a specific time window (e.g., 24 hours). For example, if the index idx_soil_ph has been queried 1000 times in the past 24 hours and the average response time of these queries is 0.05 seconds, then seconds, refers to the slowest query response time served by the index during the analysis period. In this example is ; The parameter is the query hit rate of the th index, expressed as a percentage. The acquisition step is to extract the query hit rate of the th index recorded from the generated current index status set. This value reflects the frequency at which the database query optimizer selects to use this index, and is statistically obtained through database performance monitoring tools. For example, if the index idx_soil_ph accounts for 92% of the total query scans of its associated table, then (i.e., 92%); The parameter is the storage overhead of the th index. The acquisition step is to extract the storage overhead of the th index recorded from the generated current index status set. This value is obtained by querying the database system catalog or using management tools to obtain the physical disk space occupied by the index. For example, if the storage space occupied by the index idx_soil_ph is 5.5GB, then GB; The parameter is the The spatial range area of a hotspot region, in square kilometers. The acquisition steps are as follows: directly read the spatial range area value recorded in the th hotspot region from the "hotspot data region recognition result" list generated in the previous step. This value was calculated when identifying the hotspot region. For example, if 3 hotspot regions are identified, their areas are km 2 , km 2 , km 2 ; Parameter: Total number of hotspot regions. The acquisition steps are as follows: directly count the total number of entries in the "hotspot data region recognition result" list. For example, if 3 hotspot regions are identified, then ; Calculation process: Taking the calculation of the support efficiency index of index idx_soil_ph (assuming it is the jth index) as an example, use the parameter values obtained above: seconds, GB, Hotspot region area: km 2 , km 2 , km 2 , Total number of hotspot regions: ; First, calculate the sum of the total areas of the hotspot regions: ; Then calculate : ; Next, calculate the cube root of the storage overhead: ; Now substitute into the formula to calculate . Given that the observed maximum response time is 0.25 seconds, the calculation becomes: ; This result indicates that the support efficiency index of index idx_soil_ph is . This value reflects the comprehensive performance of the index under the current load and hotspot regions. A higher value generally indicates better index efficiency, effectively serving queries (high hit rate) and covering important hotspot data regions with relatively low response time and storage cost. In subsequent steps, this value will be compared with the values of all other indexes to decide whether to retain, modify, or delete this index. For example, if the average of all indexes If it is 25, then it indicates that the index performance is better than the average level.

[0032] According to the support efficiency index of each calculated index , first calculate all indexes the arithmetic mean of the values , and then make classification decisions for each index based on preset rules and thresholds: For the first category, filter out indexes whose comprehensive efficiency is significantly lower than the average level (lower than 50% of the average value), and determine them as inefficient indexes, and directly generate deletion instructions; this 50% threshold is based on the consideration of balancing resource recovery and potential risks. It is considered that for indexes with efficiency less than half of the average level, the maintenance cost is likely to exceed the performance benefits brought. For example, if , then all indexes will be marked for deletion; for the second category, filter out indexes that meet and whose query hit rate is less than 60%. These indexes are at a medium to low efficiency level and are not fully utilized by the query optimizer (hit rate less than 60%), and are determined as indexes with room for optimization, and generate modification instructions. The modification content may include adjusting index columns, included columns, or index types, etc., aiming to improve their hit rate and efficiency; the 60% hit rate threshold is set based on experience. Below this value, it is usually considered that the index does not match the query pattern well and needs to be adjusted; for the third category, check whether there are query patterns indicated by the "hot data area recognition result" but not effectively covered by any current index (that is, the index columns do not match the key filtering or joining columns involved in the hot query), and simulate (or through historical query analysis) and find that these query patterns perform poorly without a suitable index (for example, the simulated query shows that the hit rate is less than 30%), then for these "index gaps", generate instructions to create new indexes, specifying the columns, types (such as B-tree, hash, spatial index) that the new index should contain, etc.; the 30% hit rate threshold is used to identify those queries with extremely poor performance and in urgent need of index support; summarize all the generated deletion, modification, and creation instructions, and each instruction includes operation type (DELETE / MODIFY / CREATE), target index name (for MODIFY / DELETE), index definition (for CREATE / MODIFY, such as column names, table names, index types), etc. information to form a database index adjustment instruction set.

[0033] The steps to obtain the dynamically optimized database physical structure are as follows: Execute the delete, modify, and create instructions in the database index adjustment instruction set, parse the operation type field, index name field, and associated data table field of each instruction in the instruction set, traverse the list of index name fields, call the database engine interface to execute the corresponding operations, record the operation execution status, execution timestamp, and exception error code, and generate a database index operation log set; Based on the hot data area recognition result, count the access times of each data block in the past 24 hours, filter out the data blocks whose access times exceed the preset threshold, extract the storage path and the hash value of the associated query result cache copy, sort them in descending order of access times, assign cache priority levels, and generate a high-frequency data cache mapping table; According to the execution status field of the database index operation log set and the cache priority level field of the high-frequency data cache mapping table, adjust the index density parameter, cache partition size, and preloading strategy of the database storage engine, force the data blocks of level 1 in the high-frequency data cache mapping table to be resident in memory and lock the cache eviction mechanism, dynamically allocate the remaining cache space to the data blocks of level 2 and level 3 and enable the LRU replacement strategy, and generate a dynamically optimized database physical structure.

[0034] Specifically, for the database index adjustment instruction set generated according to the previous steps, the system processes each instruction in sequence. First, it parses the instruction record to extract the operation type field (specifying whether it is "DELETE", "MODIFY", or "CREATE"), the index name field (for "DELETE" and "MODIFY" operations), and the associated data table field and index definition details (such as column names, order, included columns, for "CREATE" and "MODIFY" operations). According to the parsed operation type, it dynamically constructs the corresponding database data definition language (DDL) statements. For example, for a "DELETE" instruction, it generates a DROP INDEX index_name; statement, for a "CREATE" instruction, it generates a CREATE INDEX index_name ON table_name (column1, column2...); statement, and for a "MODIFY" instruction, it may generate a DROP INDEX old_index_name; CREATE INDEX new_index_name...; or an ALTER INDEX index_name...; statement supported by a specific database. Then, through the database connection driver, it calls the standard interfaces of the database engine (such as ODBC, JDBC, or the API of a specific database) to execute these generated DDL statements. During the execution process, it synchronously captures the return status of the operation, records it as successful or failed, and obtains the timestamp when the operation is completed. If the operation fails, it captures the detailed exception error code (such as the SQLSTATE code or the error number of a specific database) and error message returned by the database. It details the execution situation of each instruction, including the instruction identifier, operation type, target index and table, execution timestamp, execution status (success / failure), elapsed time, and any relevant error codes and information, and records them in a structured database index operation log set for tracking the index change history and status.

[0035] Based on the previously identified hot data area access patterns, the system initiates the monitoring and statistics of the access situation of the underlying data blocks in the database. By using the internal performance counters of the database or enabling specific tracing flags (e.g., monitoring the access events of data pages in the buffer pool), within a set time window (fixed at the past 24 hours), continuously accumulate and record the number of times each data block in the database (usually the basic I / O unit managed by the database, such as an 8KB-sized page) is accessed. After the statistical period ends, summarize to obtain the total access count value for each data block. Then, set an access count threshold to filter out the frequently accessed data blocks. The determination method of this threshold is as follows: calculate the distribution of the access counts of all the accessed data blocks in the past 24 hours, and select the access count corresponding to, for example, the 95th percentile as the threshold. The calculation process is to sort all the access counts in descending order and find the access count at the 5% position from the top of the list. If this value is 1200 times, then the access count threshold is set to 1200. Filter out all the data blocks with access counts greater than or equal to 1200 times. For each filtered frequently accessed data block, extract its unique block identifier (BlockID), the current physical storage path (indicating which tablespace, file, and the offset within the file it is located in), and, if the system maintains a query result cache, extract the hash value of the query result cache entry related to the content of this data block (for potential cache consistency maintenance or correlation analysis). Then, sort these filtered frequently accessed data blocks in descending order according to their access counts, and assign cache priority levels based on the sorting results: the data blocks ranked in the top 10% (e.g., access counts greater than or equal to 5000 times) are assigned level 1 (highest priority), the data blocks ranked in the next 20% (e.g., access counts between 2000 and 4999 times) are assigned level 2 (medium priority), and the remaining data blocks whose access counts meet the threshold conditions (e.g., access counts between 1200 and 1999 times) are assigned level 3 (lower priority). Finally, organize the information including the data block ID, storage path, access count, associated cache hash value, and the assigned cache priority level into a table structure to generate a frequently accessed data cache mapping table.

[0036] Based on the operation execution status in the generated database index operation log set (confirming that the index adjustment has been successfully completed) and the cache priority level information in the generated high-frequency data cache mapping table, the system starts to adjust the relevant parameters of the database storage engine to optimize the physical structure. The specific adjustments include: referring to the read and write characteristics of the main associated tables of high-frequency data blocks (which can be indirectly obtained from query log analysis), appropriately adjusting the density parameters of relevant indexes. For example, for the indexes of read-intensive operations involved in level 1 and level 2 data blocks, a lower fill factor can be considered (such as setting FILLFACTOR to 80 instead of the default 90 or 100) to reduce index page splitting and optimize read performance. At the same time, according to the priority distribution and the number of data blocks provided by the high-frequency data cache mapping table, re-plan the size of the memory cache partition of the database. For example, 40% of the total cache is fixedly allocated to level 1 data blocks, 30% to level 2, 20% to level 3, and the remaining 10% is used as a general cache. In addition, configure the preloading strategy so that the database actively loads level 1 and some level 2 data blocks into memory when starting up or during off-peak periods. For cache management, set the memory pages corresponding to all data blocks marked as level 1 in the high-frequency data cache mapping table to the forced resident state and lock its cache eviction mechanism so that they will not be swapped out of memory by the conventional page replacement algorithm (such as LRU). For the cache space allocated to level 2 and level 3 data blocks, enable the standard least recently used (LRU) cache replacement strategy to allow these pages to be swapped in and out dynamically according to the access pattern. By performing the above index parameter adjustment, cache partition setting, preloading configuration, and hierarchical cache management strategy, a database physical structure that is dynamically adjusted and optimized according to the actual data access heat and index operation status is finally formed.

[0037] The steps for obtaining the data quality stratification mapping table are as follows: Receive the input farmland fertility data, extract the pH value, organic matter content, conductivity, sampling depth, and data timeliness of each piece of data, and generate a set of original data quality deviations by comparing with the ideal pH value range, lower limit of organic matter content, upper limit of conductivity, standard sampling depth, and maximum allowable time difference in the preset quality evaluation criteria. Based on the set of original data quality deviations, calculate the quality deviation degree of each piece of data. The calculation formula is: ; where, is the quality deviation degree, is the measured pH value, is the median of the ideal pH value range, is the measured organic matter content, is the lower limit of organic matter content, is the measured sampling depth, is the standard sampling depth, is the data acquisition timestamp, is the current timestamp, is the maximum allowable time difference; According to the quality deviation degree, data with a quality deviation degree less than or equal to the preset first-level threshold is screened and assigned to quality level 1, data with a quality deviation degree between the first-level threshold and the second-level threshold is assigned to quality level 2, and data with a quality deviation degree greater than the second-level threshold is assigned to quality level 3, and a data quality stratification mapping table is established.

[0038] Specifically, the system receives farmland fertility data records input in a standardized format (for example, a CSV file or JSON stream containing fields such as sample ID, acquisition timestamp, geographical coordinates, pH value, percentage of organic matter content, conductivity dS / m, sampling depth cm, etc.). For each data record, first, five key quality-related parameters recorded therein, namely pH value, organic matter content, conductivity, sampling depth, and data acquisition timestamp, are accurately extracted. At the same time, the system loads a set of pre-configured quality evaluation standard reference values for a specific region and crop type. These reference values are comprehensively set according to the local agricultural department's recommended specifications, soil science research, and the best growth requirements of crops. For example, for a corn planting area in a certain region, the ideal range of pH value is set to 6.5 to 7.5, the lower limit of organic matter content is 1.8%, the upper limit of conductivity is 2.0 dS / m, the standard sampling depth is 15 cm below the surface (representing the plough layer), and the maximum allowable time difference for data is 120 days ( days). The system compares the parameter values of each extracted data record with these corresponding standard values one by one, calculates the difference between each parameter and the ideal state or whether it exceeds the limit. For example, calculate the absolute difference between the measured pH and the median of the ideal range ((6.5 + 7.5) / 2 = 7.0), record whether the organic matter content is lower than the lower limit of 1.8%, record whether the conductivity exceeds the upper limit of 2.0 dS / m, calculate the absolute difference between the measured sampling depth and the standard depth of 15 cm, and calculate the time difference (in days) between the data acquisition timestamp and the current time, and collect these comparison results (including specific differences or over-limit situations) to form a set of raw data quality deviation, where each record corresponds to an input data point and the deviation of each quality dimension.

[0039] Formula: , The benefit of the formula is that it provides a method for comprehensively quantifying the quality deviation degree of farmland fertility data, comprehensively considering multiple key quality dimensions: the deviation of core chemical indicators (pH value and organic matter content ) from the ideal value, the gap between the physical sampling normativity (sampling depth ) and the standard, and the timeliness of the data itself ( Relative to ), in the molecular part, the absolute deviations of the two most important fertility indicators, i.e., pH value and organic matter content, are directly accumulated, which directly reflects the deviation of the accuracy of the core content; The parameter is the measured pH value recorded in the input farmland fertility data. The acquisition step is to directly read the value of the pH field from the processed input data record. For example, the pH value of a certain record is 5.8; The parameter is the median of the ideal pH value range defined in the preset quality evaluation standard. The acquisition step is to find the set ideal pH range and calculate its midpoint value. For example, if the ideal range is 6.5 to 7.5, then ; The parameter is the measured organic matter content value recorded in the input farmland fertility data. The acquisition step is to directly read the value of the organic matter content field from the input data record. For example, the organic matter content of a certain record is 2.1%; The parameter is the acceptable lower limit value of the organic matter content defined in the preset quality evaluation standard. The acquisition step is to find the set lower limit standard of the organic matter content. For example, if the lower limit standard is 1.8%, then ; The parameter is the measured sampling depth value recorded in the input farmland fertility data, and the unit is usually centimeter (cm). The acquisition step is to directly read the value of the sampling depth field from the input data record. For example, the sampling depth of a certain record is 30 cm; The parameter is the standard sampling depth value defined in the preset quality evaluation standard, and the unit is the same as that. The acquisition step is to find the set standard sampling depth. For example, if the standard depth is 15 cm, then ; The parameter is the data collection timestamp recorded in the input farmland fertility data. The acquisition step is to directly read the value of the timestamp field from the input data record and convert it into a format that can calculate the difference (such as Unix timestamp or date object). For example, the collection time is "2024-10-30 10:00:00"; The parameter is the current system timestamp when the calculation is executed. The acquisition step is to call the system function to obtain the current date and time and convert it into the same format. For example, the current time is "2025-04-27 14:09:43" (according to the context); The parameter is the maximum allowable time difference defined in the preset quality evaluation criteria, with the unit of days. The steps to obtain it are to search for the set maximum allowable time difference standard. For example, days; The parameter is to prevent from causing a division-by-zero error in the logarithmic function and is added as a very small normal constant according to the requirements of numerical calculation stability. It is usually set to to For example, take ; Calculation process: Calculate using the parameter values in the above example : , , , = 2024 - 10 - 30 (about 180 days ago, relative to = 2025 - 04 - 27), = 2025 - 04 - 27 days, ; Calculate the absolute differences of each item: = 180 days; Calculate the logarithmic term in the denominator: ; Calculate the time penalty factor: ; Substitute into the formula to calculate : ; ; ; ; The result shows that: the quality deviation degree of this farmland fertility data is approximately . This value comprehensively reflects the overall gap between this data and the preset ideal standard in multiple dimensions such as pH value, organic matter content, sampling depth standardization, and timeliness. The higher the The value is mainly caused by the low pH value, large deviation in sampling depth, and the data timeliness exceeding the maximum allowable range. This value will be used for data quality stratification in the next step.

[0040] The system, based on the quality deviation degree value calculated for each piece of farmland fertility data in the previous step, divides the data into different quality levels. The basis for the division is two preset quality deviation degree thresholds: the first-level threshold ( ) and the second-level threshold ( ). The setting process of these two thresholds is as follows: First, collect a batch of representative historical farmland fertility data samples (for example, containing more than 10,000 records covering different regions and different times), calculate the value for each piece of data in this batch of samples, obtain the overall distribution of the value, and then, according to the business requirements and the requirements for data quality in the data application scenario, determine the stratification ratio or absolute threshold. For example, a percentile-based setting method is to sort the values from small to large, and take the value at the lower one-third position (for example, the 33rd percentile) in the distribution as , and take the value at the higher two-thirds position (for example, the 66th percentile) in the distribution as . If the 33rd percentile of the sample data is calculated to be 0.30 and the 66th percentile is 0.75, then set and . After setting the thresholds, the system traverses all the data records to be processed: If the of a certain record, then it is assigned to quality level 1 (highest quality); if , then it is assigned to quality level 2 (medium quality); if , then it is assigned to quality level 3 (lowest quality). Associate the unique identifier of each data record with its calculated value and the finally assigned quality level (1, 2, or 3), store them in a mapping table, and finally establish a data quality stratification mapping table, which clearly indicates the quality level to which each piece of farmland fertility data belongs.

[0041] The steps to obtain the multi-resolution farmland fertility data storage structure are as follows: Based on the quality level classification field in the data quality hierarchical mapping table, match the preset storage policy configuration template. The quality level 1 corresponds to an index density of 95%, a storage medium type of SSD, and a compression algorithm of LZ4; the quality level 2 corresponds to an index density of 80%, a storage medium type of HDD, and a compression algorithm of Zstd; the quality level 3 corresponds to an index density of 70%, a storage medium type of cold storage, and a compression algorithm of Gzip, and generate a data quality level storage policy configuration table; According to the index density parameters in the data quality level storage policy configuration table, gradually adjust the index fill factor and B-tree depth of the corresponding data tables in the order of quality levels 1 to 3. At the same time, migrate the data of quality level 1 to the SSD partition, the data of quality level 2 to the HDD partition, and the data of quality level 3 to the cold storage partition, and generate a storage medium allocation record and an index parameter update log; Based on the storage medium allocation record and the index parameter update log, deploy the storage access routing rules to generate a multi-resolution farmland fertility data storage structure.

[0042] Specifically, the system looks up and applies a predefined storage policy configuration template based on the data quality hierarchical mapping table generated in the previous steps, which records the quality level (1, 2, or 3) of each farmland fertility data. This template is a structured configuration set (e.g., stored in a configuration file or a database management table), which specifies a set of specific storage parameter combinations for each quality level. The setting is based on balancing data access frequency, query performance requirements, and storage cost: The data of quality level 1 has the highest quality and is expected to be accessed most frequently. Therefore, the configuration template assigns the highest index density to it, such as 95% (by setting the index fill factor to 95, making the index pages more full and reducing the number of pages read, suitable for read-intensive scenarios), and selects the highest-performance storage medium type, i.e., solid-state drive (SSD), and a compression algorithm with relatively fast read and write speeds, such as LZ4; the data of quality level 2 has medium quality and medium access frequency. The template configures a medium index density for it, such as 80%, selects a mechanical hard drive (HDD) with a more balanced performance-price ratio, and adopts a compression algorithm that takes both compression ratio and speed into account, such as Zstd; the data of quality level 3 has the lowest quality and the lowest expected access frequency. The template configures the lowest index density for it, such as 70% (allowing more space within the page for future updates, reducing page splitting, suitable for scenarios with fewer updates or tolerating slightly lower read performance), selects the lowest-cost storage medium type, i.e., cold storage (such as the archival layer of an object storage service or a nearline tape library), and adopts a compression algorithm with a high compression ratio, such as Gzip. The system matches the corresponding entries in the template according to the quality level values (1, 2, 3) in the mapping table, extracts these specific parameter combinations (index density, storage medium type, compression algorithm), and finally generates a clear storage policy configuration for each quality level, which is summarized into a data quality level storage policy configuration table.

[0043] Referring to the index density parameters defined for each quality level (1, 2, 3) in the data quality level storage policy configuration table, the system performs database physical structure adjustment and data migration operations in hierarchical order. First, for the database tables (or table partitions) carrying data of different quality levels, adjust the physical properties of their associated indexes, mainly by setting the index fill factor (FILLFACTOR) to indirectly affect the index density and the future B-tree depth. For example, execute ALTER INDEX... SET (fillfactor = 95); for the indexes related to data of quality level 1, ALTER INDEX... SET (fillfactor = 80); for level 2, and ALTER INDEX... SET (fillfactor = 70); for level 3, and trigger the index rebuild (REINDEX) operation when necessary (such as when the fill factor changes significantly or there are other changes in the table structure) to ensure the parameters take effect. At the same time, the system starts the data migration process. Based on the data quality stratification mapping table, identify all data records marked as quality level 1 and move them from their original storage locations to a pre-configured SSD-based tablespace or partition. Similarly, migrate all data records of quality level 2 to an HDD-based tablespace or partition, and migrate all data records of quality level 3 to a storage location configured to access cold storage (possibly through specific database extensions or external table mechanisms). The migration process is usually executed in batches, and the operation status is recorded after each migration. After the entire adjustment and migration are completed, the system generates a detailed execution log, including the source location of each batch of data migration, the type of target storage medium (SSD / HDD / Cold), the range or quantity of migrated data record identifiers, the operation timestamp, the success or failure status, and the name of each adjusted index, the modified parameters (such as the fill factor), the old value, the new value, and the update timestamp, forming a storage medium allocation record and an index parameter update log.

[0044] Based on the generated storage medium allocation record (confirming that data has been physically separated into SSD, HDD, and cold storage according to quality levels) and the index parameter update log (confirming that the index structure has been optimized according to levels), the system begins to deploy or update storage access routing rules. These rules determine how data query requests issued by users or applications are directed to the correct physical storage level. The deployment method depends on the database architecture. A common way is to utilize the table partitioning function of the database itself. If the data has been partitioned according to quality levels, the database query optimizer can automatically direct the query to the SSD, HDD, or cold storage interface storing that partition based on the quality level limit in the query condition (or through an internal mechanism associated with the partition where the data is located). Another way is to implement the routing logic at the data access middleware or application level. This logic parses the incoming query request, especially identifying the quality requirement parameters that may be included in the request, and then, based on these requirements and the maintained internal mapping (this mapping relationship is updated in real time according to the storage medium allocation record, knowing which quality level of data is on which type of storage), dynamically routes the query statement to the corresponding database connection pool or table object (pointing to a specific storage level). For requests to access data at the cold storage level, the routing rules also need to include the logic to trigger data thawing (recovery from the archived state) and wait for it to become accessible. By deploying these clear routing rules, a multi-resolution farmland fertility data storage structure is finally constructed. Under this structure, data is distributed according to quality on storage media with different performances and costs, and a unified data access interface that can perceive and utilize the underlying multi-resolution characteristics is provided through the routing rules.

[0045] The steps to obtain the quality-constrained query execution plan are as follows: Parse the farmland fertility data query request submitted by the user. Match the quality constraint keywords in the query statement through regular expressions, extract the quality level requirement, time range limit field, and spatial range limit field, and generate a set of quality requirement constraint conditions. Based on the quality level thresholds in the set of quality requirement constraint conditions, call the multi-resolution farmland fertility data storage structure to match the SSD storage path corresponding to quality level 1, the HDD storage path corresponding to quality level 2, and the cold storage path corresponding to quality level 3. Filter the list of data block addresses that meet the quality level thresholds and whose storage medium types are consistent with the query performance requirements, and generate a storage medium and data block address mapping table. According to the storage path field in the storage medium and data block address mapping table, combined with the index parameter snapshot and compression algorithm configuration items of the multi-resolution storage structure, define the data access order and decompression algorithm call chain, and generate the quality-constrained query execution plan.

[0046] Specifically, the system receives a farmland fertility data query request string submitted by the user through the application programming interface (API) and expressed in the structured query language (SQL) or a specific query format. The query parser first applies a series of predefined regular expressions to scan the entire query statement to find specific quality constraint keywords or patterns, such as matching syntax structures like QUALITY_LEVEL=1, MIN_QUALITY>1, or QUALITY BETWEEN 1 AND 2. Once a match is successful, the numerical value of the quality level requirement is extracted (for example, equal to 1, greater than 1, between 1 and 2). If the quality constraint is not explicitly specified, the system default value may be used (for example, default querying all levels or only the highest quality level). At the same time, the parser uses standard SQL syntax analysis techniques to identify and extract the time conditions (such as the WHERE date>='YYYY-MM-DD' AND date<'YYYY-MM-DD' clause) and spatial conditions (such as the WHERE ST_Contains(ST_GeomFromText('POLYGON(...'), location) clause) used to limit the data range in the query statement, and respectively extracts the start and end limit values of the time range and the geometric descriptor of the spatial range (such as a polygon coordinate string). The extracted quality level requirement (which may be a specific level or a range), time range limit fields (start time, end time), and spatial range limit fields (geometric object description) are integrated into a structured data object to form a set of quality requirement constraint conditions, which precisely defines the specific requirements of the user query in the dimensions of data quality, time, and space.

[0047] Based on the quality level requirements specified in the set of quality requirement constraint conditions generated in the previous step (for example, requiring to query data with quality levels 1 and 2), the system queries and utilizes the metadata information of the established multi-resolution farmland fertility data storage structure. This metadata maintains the physical storage medium types where different quality level data (Level1, Level2, Level3) are currently located and their corresponding access paths or identifiers. Based on this, the system matches the storage paths that meet the user's quality requirements. For example, if the user requests levels 1 and 2, it matches the SSD storage path corresponding to level 1 data (such as pointing to / ssd_partition / level1_data) and the HDD storage path corresponding to level 2 data (such as pointing to / hdd_partition / level2_data). Next, the system combines the time range and spatial range limitation fields in the set of quality requirement constraint conditions and performs a preliminary screening on these selected storage paths (SSD and HDD) to identify those data blocks (or files, objects) that may overlap with the user's query conditions in terms of time and space. Further, the system also considers the performance requirements implicitly or explicitly declared in the query itself. For example, if a query is marked as requiring a low-latency response (such as an interactive query), and some of the filtered data blocks are located in the cold storage level (Level3), even if their quality meets the requirements, the system may temporarily exclude these cold storage data blocks from the list of data to be accessed or mark them as low-priority asynchronous acquisition according to a preset policy (for example, cold storage access is expected to take more than 2 minutes, so it is not applicable to low-latency queries). After comprehensive screening based on quality levels, time and space ranges, and performance requirements, a list containing all the data blocks that are determined to need to be accessed and their detailed information is finally generated. Each record in it contains the unique identifier of the data block, the quality level it belongs to, the storage medium type (SSD / HDD / Cold), and the exact physical storage path, forming a mapping table between the storage medium and the data block address.

[0048] Based on the generated storage medium and data block address mapping table, which lists all the data blocks to be queried and their storage paths and medium types, the query plan generator starts to construct detailed execution steps. First, using the storage path field information in the mapping table and referring to the access speed characteristics of known different storage media (SSD, HDD, cold storage), it defines the access order of data blocks. Usually, it preferentially arranges to access the data blocks on the fastest SSD, followed by the data blocks on the HDD. For the data blocks on the cold storage, it arranges to start the data thawing or retrieval process and may process the data access of other media in parallel. Then, the query plan generator combines the snapshot of index parameters associated with each storage level recorded when defining the multi-resolution storage structure (for example, recording that the index fill factor of level 1 is 95%, level 2 is 80%, etc., to assist in estimating I / O efficiency) and the compression algorithm configuration items (specifying that LZ4 is used at level 1, Zstd at level 2, and Gzip at level 3), and precisely specifies the subsequent processing steps for the data streams read from different storage paths. In particular, it defines the decompression algorithm call chain, clearly indicating that the data read from the SSD needs to call the LZ4 decompression library, the data read from the HDD calls the Zstd decompression library, and the data recovered from the cold storage calls the Gzip decompression library. It concatenates the data access order (including possible parallel and serial steps, cold storage waiting logic) with the corresponding decompression operations and integrates the filtering, joining, aggregation, etc. logic in the original query to finally generate a complete, ordered, quality-constrained query execution plan that includes specific operations (access path, decompression algorithm, data processing logic).

[0049] The steps to obtain the query result set are as follows: Based on the storage path priority field in the quality-constrained query execution plan, call the storage medium access interface that dynamically optimizes the database physical structure, execute the data block reading in the priority order, trigger the thawing process for the cold storage path and monitor the thawing completion status, record the read timestamp and storage medium type of each data block, and generate a set of data block retrieval records; According to the storage medium type field in the set of data block retrieval records, perform real-time decompression of LZ4 on the SSD path data, perform streaming decompression of Zstd on the HDD path data, and perform batch decompression of Gzip on the cold storage path data. Merge the decompressed data blocks and eliminate duplicate timestamp records to generate an integrated farmland fertility dataset; Based on the integrated farmland fertility dataset, sort by the time range interval field and encapsulate it in a standardized JSON format to generate the query result set.

[0050] Specifically, the system strictly follows the storage path priority and access order defined in the quality constraint query execution plan generated in the previous step. First, for the data block list marked as the highest priority (usually corresponding to the SSD storage path) in the execution plan, the system calls the storage media access interface function optimized for SSD provided by the dynamic optimization database physical structure (for example, read_blocks_ssd(block_ids_list)) to initiate an asynchronous or synchronous read operation. Then, according to the plan order, for the data block list with the second highest priority (usually corresponding to the HDD storage path), the system calls the corresponding HDD storage media access interface function (for example, read_blocks_hdd(block_ids_list)) to perform reading. If the execution plan contains a path pointing to cold storage, the system calls the cold storage access interface (for example, read_blocks_hdd(block_ids_list)). For example, initiate_thaw(cold_storage_path, block_id) triggers the thawing or recovery process of the specified data block. Thawing usually takes a certain amount of time. The system then starts a monitoring mechanism to track the progress of the thawing task by periodically querying the status interface of the cold storage service (for example, get_thaw_status(job_id)) until the status is displayed as completed or accessible. During the execution of all these read and thaw operations, the system records the retrieval activities of each data block in detail, including the timestamp of the attempt to read or trigger the thawing, the timestamp of the operation completion, the type of storage media actually accessed (SSD, HDD or ColdStorage), and the final status of the operation (success, failure, in progress). All these records are collected together to form a data block retrieval record set.

[0051] According to each successfully retrieved data block and its source storage media type information recorded in the data block retrieval record set, the system performs targeted decompression operations, using the decompression library that matches the compression algorithm pre-configured for each storage layer. For data blocks retrieved from the SSD path (the media type in the record set is SSD), the LZ4 decompression library is called to perform real-time decompression, which is usually performed synchronously when the data is read and flows into the memory. For data blocks retrieved from the HDD path (the media type is HDD), the Zstd decompression library is called for streaming decompression, which adapts to the slower I / O speed of the HDD and decompresses while reading. For data blocks that are thawed and retrieved from the cold storage path (the media type is ColdStorage), the Gzip decompression library is called for batch decompression. , usually processed at one time after the entire data object or file is completely retrieved, the system merges the data block contents (now the original farmland fertility data records) from different storage levels that have been decompressed by their corresponding algorithms to form a temporary set containing all potentially related records. Subsequently, the data cleaning step is performed on this temporary set, mainly to remove duplicate timestamp records that may be introduced due to data redundancy or overlapping query range boundaries. By checking the timestamp field in the record (and possible geographic location field or sample unique ID field), it is ensured that only one copy of the data for each independent sampling event is retained. Finally, an integrated and deduplicated farmland fertility dataset is obtained, which contains all valid data records that meet the original query time and space scope and quality requirements.

[0052] Based on the generated, decompressed, deduplicated, and integrated farmland fertility dataset, the system performs the final sorting and formatting work. First, the entire dataset is sorted according to the time range field of each record in the dataset (i.e., the timestamp field of data collection), usually arranging the records in chronological order (for example, from earliest to latest). After the sorting is completed, the system encapsulates the sorted dataset into a standardized JSON (JavaScript Object Notation) format, which usually involves converting each farmland fertility data record (including all relevant fields such as pH value, organic matter content, conductivity, sampling depth, geographic coordinates, timestamp, etc.) into a JSON format. SON object (in the form of key-value pairs), and then organize all these JSON objects representing single records into a JSON array. For example, the output format is [{"timestamp":"YYYY-MM-DDTHH:mm:ssZ","ph":7.1,"organic_matter":2.3,...},{"timestamp":"...",...},...]. This standardized JSON format has good readability and cross-platform compatibility, and is easy for subsequent application processing or display. The final encapsulated JSON data structure is the final query result set of this query request.

Claims

1. A farmland fertility data collection and analysis system based on data mining, characterized in that, The system includes: A query log analysis module that continuously records user query logs, generates a query log feature set, analyzes the query log feature set, identifies recurring query structures, and obtains a hot data area identification result; An adaptive structure optimization module that, based on the hot data area identification result, determines the farmland fertility data indexes that need to be created, modified, or deleted, generates a database index adjustment instruction set, executes the database index adjustment instruction set, and adjusts the data cache settings according to the hot data area identification result to establish a dynamically optimized database physical structure; A data quality stratification module that receives the input farmland fertility data, assigns a quality level to each piece of farmland fertility data according to a preset quality evaluation standard, establishes a data quality stratification mapping table, and uses the data quality stratification mapping table to reorganize the storage of farmland fertility data to construct a multi-resolution farmland fertility data storage structure; A quality-aware query module that parses the user's submitted farmland fertility data query request, extracts the quality requirements in the query statement, filters the farmland fertility data levels that meet the quality requirements according to the multi-resolution farmland fertility data storage structure, generates a quality-constrained query execution plan, and executes data retrieval on the dynamically optimized database physical structure and the selected data levels of the multi-resolution farmland fertility data storage structure according to the quality-constrained query execution plan to obtain a query result set.

2. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that The steps for obtaining the hot data area identification result are as follows: Continuously record user query logs, extract the query frequency, calculation overhead, accessed farmland fertility data spatial range, and time range of each query, and generate a query log feature set; Based on the query log feature set, calculate the query density index for each query structure; According to the query density index, filter out the query structures whose query density index is greater than or equal to the average of all query density indexes, combine the overlapping areas of the spatial range and time range, identify the query structures that appear more than the preset threshold, and generate a hot data area identification result.

3. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that The steps for obtaining the database index adjustment instruction set are as follows: Based on the hot data area identification result, extract the response time, query hit rate, and storage overhead of the current database index, and generate a current index status set; According to the current index status set, calculate the support efficiency index for each index; Filter out the indexes whose support efficiency index is less than 50% of the average of all index support efficiency indexes and determine them as indexes to be deleted, filter out the indexes whose support efficiency index is between 50% and 100% of the average and whose query hit rate is lower than 60% and determine them as indexes to be modified, and generate creation instructions for the index gaps that do not cover the hot areas and whose query hit rate is lower than 30% to generate a database index adjustment instruction set.

4. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that The steps for obtaining the dynamically optimized database physical structure are as follows: Execute the delete, modify, and create instructions in the database index adjustment instruction set, parse the operation type field, index name field, and associated data table field of each instruction in the instruction set, traverse the index name field list, call the database engine interface to execute the corresponding operation, record the operation execution status, execution timestamp, and exception error code, and generate a database index operation log set; Based on the identified hot data area results, count the access times of each data block in the past 24 hours, filter out the data blocks with access times exceeding the preset threshold, extract the storage path and the hash value of the associated query result cache copy, sort them in descending order of access times, assign cache priority levels, and generate a high-frequency data cache mapping table; According to the execution status field of the database index operation log set and the cache priority level field of the high-frequency data cache mapping table, adjust the index density parameter, cache partition size, and preloading strategy of the database storage engine, force the data blocks at level 1 in the high-frequency data cache mapping table to reside in memory and lock the cache eviction mechanism, dynamically allocate the remaining cache space to level 2 and level 3 data blocks and enable the LRU replacement strategy, and generate a dynamically optimized database physical structure.

5. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, wherein The steps for obtaining the data quality hierarchical mapping table are as follows: Receive the input farmland fertility data, extract the pH value, organic matter content, conductivity, sampling depth, and data timeliness of each data item, and generate an original data quality deviation set by comparing with the ideal pH value range, lower limit of organic matter content, upper limit of conductivity, standard sampling depth, and maximum allowable time difference in the preset quality evaluation criteria; Based on the original data quality deviation set, calculate the quality deviation degree of each data item; According to the quality deviation degree, filter out the data with a quality deviation degree less than or equal to the preset first-level threshold and assign it to quality level 1, the data with a quality deviation degree between the first-level threshold and the second-level threshold and assign it to quality level 2, and the data with a quality deviation degree greater than the second-level threshold and assign it to quality level 3, and establish a data quality hierarchical mapping table.

6. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that, The steps for obtaining the multi-resolution farmland fertility data storage structure are as follows: Based on the quality level classification field in the data quality hierarchical mapping table, match the preset storage strategy configuration template, where quality level 1 corresponds to an index density of 95%, a storage medium type of SSD, and a compression algorithm of LZ4, quality level 2 corresponds to an index density of 80%, a storage medium type of HDD, and a compression algorithm of Zstd, and quality level 3 corresponds to an index density of 70%, a storage medium type of cold storage, and a compression algorithm of Gzip, and generate a data quality level storage strategy configuration table; According to the index density parameter in the data quality level storage strategy configuration table, gradually adjust the index fill factor and B-tree depth of the corresponding data table in the order of quality levels 1 to 3. At the same time, migrate the quality level 1 data to the SSD partition, the quality level 2 data to the HDD partition, and the quality level 3 data to the cold storage partition, and generate a storage medium allocation record and an index parameter update log; Based on the storage medium, allocate records and index parameter update logs, deploy storage access routing rules, and generate a multi-resolution farmland fertility data storage structure.

7. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that, The steps for obtaining the quality constraint query execution plan are as follows: Parse the farmland fertility data query request submitted by the user, match the quality constraint keywords in the query statement through regular expressions, extract the quality level requirements, time range limit fields, and space range limit fields, and generate a set of quality requirement constraint conditions; Based on the quality level thresholds in the set of quality requirement constraint conditions, call the multi-resolution farmland fertility data storage structure, match the SSD storage path corresponding to quality level 1, the HDD storage path corresponding to quality level 2, and the cold storage path corresponding to quality level 3, filter the list of data block addresses that meet the quality level thresholds and whose storage medium types are consistent with the query performance requirements, and generate a storage medium and data block address mapping table; According to the storage path fields in the storage medium and data block address mapping table, combined with the index parameter snapshot and compression algorithm configuration items of the multi-resolution storage structure, define the data access order and decompression algorithm call chain, and generate a quality constraint query execution plan.

8. The farmland fertility data acquisition and analysis system based on data mining according to claim 1, characterized in that, The steps for obtaining the query result set are as follows: Based on the storage path priority fields in the quality constraint query execution plan, call the storage medium access interface that dynamically optimizes the database physical structure, perform data block reading in the order of priority, trigger the thawing process for the cold storage path and monitor the thawing completion status, record the read timestamp and storage medium type of each data block, and generate a data block retrieval record set; According to the storage medium type fields in the data block retrieval record set, perform real-time decompression of LZ4 on the SSD path data, perform streaming decompression of Zstd on the HDD path data, and perform batch decompression of Gzip on the cold storage path data, merge the decompressed data blocks and eliminate duplicate timestamp records, and generate an integrated farmland fertility data set; Based on the integrated farmland fertility data set, sort by the time range interval fields and encapsulate them in a standardized JSON format to generate a query result set.

Images