Database query method based on dynamic load perception and adaptive index optimization

Abstract

The present invention provides a database query method based on dynamic load sensing and adaptive index optimization, comprising: generating a task execution order for all pending data processing tasks according to task priority rules; dynamically sensing the database load based on a set of database performance indicators to predict the database's peak load data; adaptively adjusting the resource pool and task priority rules based on the peak load data to execute the data processing tasks according to the task execution order based on the adaptively adjusted resource pool; and, during the execution of the data processing tasks, determining the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein based on an adaptive index optimization operation and a dynamic plan adjustment mechanism, to determine the results corresponding to the data processing tasks. The present invention can significantly improve the limitations of traditional technologies and better meet complex and changing practical needs.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a database query method based on dynamic load perception and adaptive index optimization. Background Art

[0002] As a core component of modern information technology, database technology is widely used in various data management scenarios, from enterprise applications to internet services. Traditional database systems typically rely on predefined static indexing strategies (such as B+ trees and hash indexes) to accelerate query operations. However, changes in query patterns can lead to redundant or missing indexes, thus affecting query efficiency.

[0003] Furthermore, database query optimizers typically generate fixed execution plans based on historical statistical information to guide query processing. However, in real-world applications, data distribution and load often change dynamically. This fixed execution plan is difficult to adapt to real-time requirements, and can easily lead to plan failure or resource waste.

[0004] In modern database systems, OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) workloads often need to execute concurrently. However, these two workloads have significantly different resource requirements: OLTP prioritizes low latency and high concurrency, while OLAP emphasizes batch processing and throughput. When these workloads run concurrently, resource contention issues such as lock contention and I / O (Input / Output) bottlenecks can easily arise. Traditional scheduling strategies struggle to balance the performance metrics of these two workloads, such as throughput and response time.

[0005] In recent years, the industry has proposed various solutions to address these challenges, such as rule-based optimizers and machine learning-driven index recommendation technologies. While these approaches have mitigated the limitations of traditional technologies to some extent, they still suffer from high computational overhead, insufficient real-time performance, and poor adaptability to different scenarios, making them difficult to meet the complex and ever-changing needs of real-world applications. Summary of the Invention

[0006] In view of this, the purpose of the present invention is to provide a database query method based on dynamic load perception and adaptive index optimization, which can significantly improve the limitations of traditional technologies and better meet complex and changeable actual needs.

[0007] In a first aspect, the present invention provides a database query method based on dynamic load perception and adaptive index optimization. The method is applied to a database, wherein the database provides independent resource pools for OLTP tasks and OLAP tasks, respectively, and the database server is installed with a lightweight monitoring agent tool, which is used to collect a set of database performance indicators in real time. The method includes:

[0008] Step 1: Generate a task execution order for all pending data processing tasks according to the task priority rule. Data processing tasks are classified as OLTP tasks or OLAP tasks.

[0009] Step 2: Based on the database performance indicator set, the database is dynamically load-aware and its corresponding load peak data in the future period is predicted. The load peak data is used to describe the conflict period between OLTP tasks and OLAP tasks.

[0010] Step 3: Based on the load peak data, adaptively adjust the task priority rules and the resource pools of the OLTP tasks and OLAP tasks, so as to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools;

[0011] Step 4: During the execution of the data processing task, based on the adaptive index optimization operation and the dynamic plan adjustment mechanism, the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein are determined to determine the result corresponding to the data processing task;

[0012] Step 5: If a new data processing task is received, return to step 1 and continue until the results corresponding to all the data processing tasks to be executed are obtained.

[0013] In one embodiment, based on a set of database performance indicators, dynamic database load perception is performed to predict the corresponding peak load data of the database in a future period, including:

[0014] For any performance indicator in the performance indicator set of the database, the performance indicator is aggregated using a sliding window to determine the statistics of the performance indicator within the sliding window, and the key features corresponding to the performance indicator are extracted from the statistics;

[0015] Through the pre-trained load prediction model, based on the key features corresponding to each performance indicator in the performance indicator set, the corresponding load peak data of the database in the future period is predicted.

[0016] In one embodiment, based on the adaptive index optimization operation and the dynamic plan adjustment mechanism, determining the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein, so as to determine the result corresponding to the data processing task, includes:

[0017] Determine the initial execution plan corresponding to the data processing task;

[0018] Using adaptive index optimization operations, determine candidate indexes and index types that match the SQL statements included in the initial execution plan;

[0019] Through the RFO optimizer, based on the plan dynamic adjustment mechanism, the initial execution plan is dynamically adjusted to obtain the target execution plan;

[0020] Based on the target execution plan and the candidate indexes and index types matched by the SQL statements contained therein, data queries are performed to obtain the results corresponding to the data processing tasks.

[0021] In one embodiment, after obtaining the target execution plan and the candidate indexes and index types that match the SQL statements contained therein, the method further includes:

[0022] The target execution plan is cached so that when a new data processing task is received, if the SQL statement contained in the new data processing task matches the SQL statement contained in the cached target execution plan, the cached target execution plan is directly called.

[0023] In one embodiment, the adaptive index optimization operation is used to determine candidate indexes and index types that match the SQL statements included in the initial execution plan, including:

[0024] A lightweight monitoring agent tool captures running queries in real time and analyzes historical query logs to identify query patterns that can benefit from indexing. It then generates index sets corresponding to the query patterns, including simple and composite indexes.

[0025] The index cost model is used to evaluate the benefit and cost of each index in the index set to determine the candidate index corresponding to the query pattern from the index set. The index type corresponding to the query pattern is then determined based on the load characteristics of the query pattern.

[0026] Based on the query pattern of the SQL statement contained in the initial execution plan, determine the candidate indexes and index types that match the SQL statement.

[0027] In one embodiment, the index types include B+ tree indexes and Bloom filter indexes. Determining the index type corresponding to the query pattern based on the load characteristics of the query pattern includes:

[0028] When the load characteristic of the query mode is a range query, the query type of the query mode is determined to be a B+ tree index;

[0029] When the load characteristic of the query mode is an equal value query, it is determined that the query type of the query mode is a Bloom filter index.

[0030] In one embodiment, the RFO optimizer dynamically adjusts the initial execution plan based on the plan dynamic adjustment mechanism to obtain the target execution plan, including:

[0031] Determine the actual execution state of the database based on the database performance indicator set; and determine the predicted execution state of the database through the optimizer cost model of the RFO optimizer;

[0032] When there is a deviation between the actual execution state and the predicted execution state, the RFO optimizer is used to dynamically adjust the initial execution plan based on the plan dynamic adjustment mechanism to obtain the target execution plan.

[0033] In one embodiment, the method further comprises:

[0034] Based on the initial execution plan and the target execution plan, the model parameters of the optimizer cost model are dynamically adjusted.

[0035] In a second aspect, the present invention further provides a database query device based on dynamic load perception and adaptive index optimization. The device is applied to a database, wherein the database provides independent resource pools for OLTP tasks and OLAP tasks, respectively. The database server is installed with a lightweight monitoring agent tool, which is used to collect a set of database performance indicators in real time. The device includes:

[0036] The task planning module is used to generate the task execution order for all pending data processing tasks according to the task priority rules. Data processing tasks are divided into OLTP tasks or OLAP tasks;

[0037] The dynamic load sensing module is used to perform dynamic load sensing on the database based on the database's performance indicator set and predict the database's corresponding load peak data in the future period. The load peak data is used to describe the conflict period between OLTP tasks and OLAP tasks.

[0038] An adaptive adjustment module is used to adaptively adjust the task priority rules and the resource pools of OLTP tasks and OLAP tasks based on load peak data, so as to execute data processing tasks in the task execution order based on the adaptively adjusted resource pools;

[0039] The index optimization and dynamic adjustment module is used to determine the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein based on the adaptive index optimization operation and plan dynamic adjustment mechanism during the execution of the data processing task, so as to determine the corresponding results of the data processing task;

[0040] The repeated execution module is used to return to call the task planning module if a new data processing task is received until the results corresponding to all the data processing tasks to be executed are obtained.

[0041] In a third aspect, the present invention further provides a database comprising a processor and a memory, wherein the memory stores computer-executable instructions that can be executed by the processor, and the processor executes the computer-executable instructions to implement any one of the methods provided in the first aspect.

[0042] The present invention provides a database query method based on dynamic load perception and adaptive index optimization. The database provides independent resource pools for OLTP tasks and OLAP tasks respectively, and the database server is installed with a lightweight monitoring agent tool. The lightweight monitoring agent tool is used to collect the performance indicator set of the database in real time. First, according to the task priority rule, a task execution order is generated for all data processing tasks to be executed. The data processing tasks are divided into OLTP tasks or OLAP tasks; then, based on the performance indicator set of the database, the database is dynamically load perceived and the corresponding load peak data of the database in the future period is predicted. The load peak data is used to describe the conflict period between OLTP tasks and ... Based on the load peak data, the task priority rules and the resource pools of OLTP tasks and OLAP tasks are adaptively adjusted to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools; in the process of executing the data processing tasks, based on the adaptive index optimization operation and the dynamic adjustment mechanism of the plan, the target execution plan corresponding to the data processing task and the candidate indexes and index types matched by the SQL statements contained therein are determined to determine the results corresponding to the data processing tasks; if a new data processing task is received, the above steps are repeated to continue generating the task execution order for all data processing tasks to be executed according to the adjusted task priority rules until the results corresponding to all data processing tasks to be executed are obtained. The above method predicts the database's corresponding load peak data in future time periods through dynamic load perception, and then adaptively adjusts the resource pools of OLTP tasks and OLAP tasks, thereby effectively balancing the conflicting demands of OLTP tasks and OLAP tasks and improving system stability; in addition, through adaptive index optimization operations and plan dynamic adjustment mechanism to generate target execution plans for data processing tasks, it can better break through the limitations of static indexes, realize real-time adaptation of index structure and data processing tasks, and generate the optimal target execution plan, which can significantly improve the limitations of traditional technologies and better meet complex and changing actual needs.

[0043] Other features and advantages of the present invention will be described in the following description, and in part will become apparent from the description, or understood by practicing the present invention. The purposes and other advantages of the present invention are realized and obtained by the structures particularly pointed out in the description, claims and drawings.

[0044] In order to make the above-mentioned objects, features and advantages of the present invention more obvious and easy to understand, preferred embodiments are given below and described in detail with reference to the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the specific embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0046] Figure 1 A flowchart of a database query method based on dynamic load perception and adaptive index optimization provided by an embodiment of the present invention;

[0047] Figure 2 An adaptive index optimization flow chart provided by an embodiment of the present invention;

[0048] Figure 3 A schematic diagram of the structure of a database query device based on dynamic load perception and adaptive index optimization provided by an embodiment of the present invention;

[0049] Figure 4 A schematic structural diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0050] To make the objectives, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions of the present invention will be clearly and completely described below in conjunction with the embodiments. Obviously, the embodiments described are only part of the embodiments of the present invention, not all of them. All other embodiments obtained by ordinary technicians in this field based on the embodiments of the present invention without making any creative efforts shall fall within the scope of protection of the present invention.

[0051] At present, traditional technologies have problems such as high computational overhead, insufficient real-time performance, and poor scenario adaptability, making it difficult to meet complex and changing actual needs. Based on this, the present invention provides a database query method based on dynamic load perception and adaptive index optimization, which can significantly improve the limitations of traditional technologies and better meet complex and changing actual needs.

[0052] To facilitate understanding of this embodiment, a database query method based on dynamic load perception and adaptive index optimization disclosed in an embodiment of the present invention is first described in detail. The method is applied to a database, where the database provides independent resource pools for OLTP tasks and OLAP tasks respectively, and a lightweight monitoring agent tool is installed on the database server. The lightweight monitoring agent tool is used to collect a set of performance indicators of the database in real time; see Figure 1 The flowchart of a database query method based on dynamic load perception and adaptive index optimization is shown. The method mainly includes the following steps 1 to 5:

[0053] Step 1: Generate a task execution order for all pending data processing tasks according to the task priority rule.

[0054] Data processing tasks are categorized as either OLTP or OLAP tasks. OLTP tasks are OLTP-type data processing tasks, while OLAP tasks are OLAP-type data processing tasks. In one example, data processing tasks are first categorized. Then, task priority rules based on the SLA (Service Level Agreement) are used to prioritize all pending data processing tasks. Based on resource availability monitored by a lightweight monitoring agent, as well as the execution status and resource consumption of each data processing task, a priority scheduling algorithm is used to dynamically adjust the execution order of data processing tasks.

[0055] Step 2: Based on the database performance indicator set, dynamically sense the database load and predict the corresponding peak load data of the database in the future period.

[0056] Performance metrics can include SQL (Structured Query Language) execution logs, lock wait events, query type, query frequency, data access patterns, CPU, memory, and I / O throughput. Peak load data is used to describe periods of conflict between OLTP and OLAP tasks. In one example, a sliding window can be used to aggregate key performance metrics. A load prediction model, such as an LSTM (Long Short-Term Memory) model, can then be used to predict the database's peak load for future periods.

[0057] Step 3: Based on the load peak data, adaptively adjust the task priority rules and the resource pools of the OLTP tasks and OLAP tasks, so as to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools.

[0058] Step 4: During the execution of the data processing task, based on the adaptive index optimization operation and the dynamic plan adjustment mechanism, determine the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein to determine the result corresponding to the data processing task.

[0059] Among them, the adaptive index optimization operation is used to adaptively determine candidate indexes and index types corresponding to different query patterns, thereby overcoming the limitations of static indexes. The dynamic plan adjustment mechanism is used to introduce a set of performance indicators collected in real time by a lightweight monitoring agent tool, dynamically rewriting the execution plan of the data processing task based on the latest database data distribution and system resource status. In one example, the initial execution plan of the currently executing data processing task is first determined. The adaptive index optimization operation is used to determine the candidate indexes and index types that match the SQL statements contained in the initial execution plan. The initial execution plan is then dynamically rewritten through the dynamic plan adjustment mechanism. The rewritten target execution plan is cached, and the data processing task is executed according to the rewritten target execution plan to obtain the corresponding results.

[0060] Step 5: If a new data processing task is received, return to step 1 and continue until the results corresponding to all the data processing tasks to be executed are obtained.

[0061] The database query method based on dynamic load perception and adaptive index optimization provided by the embodiment of the present invention predicts the corresponding load peak data of the database in the future time period through dynamic load perception, and then adaptively adjusts the resource pools of the OLTP tasks and OLAP tasks, thereby effectively balancing the conflicting demands of the OLTP tasks and OLAP tasks and improving system stability; in addition, the target execution plan of the data processing task is generated through the adaptive index optimization operation and the dynamic adjustment mechanism of the plan, which can better break through the limitations of static indexes, realize real-time adaptation of the index structure and the data processing tasks, and generate the optimal target execution plan, thereby significantly improving the limitations of traditional technologies and better meeting complex and changeable actual needs.

[0062] For ease of understanding, the present invention provides a specific implementation of a database query method based on dynamic load awareness and adaptive index optimization. The implementation includes:

[0063] (1) The database provides independent resource pools for OLTP and OLAP tasks, including CPU (Central Processing Unit) resource pools, memory resource pools, and I / O bandwidth resource pools. The specific implementation process is as follows:

[0064] (1.1) CPU resource pool allocation:

[0065] CPU resource pool for OLTP tasks: High-frequency, low-latency CPU cores are allocated to ensure efficient transaction processing. CPU affinity is also used to bind OLTP tasks to specific cores to reduce context switching overhead. In one example, eight cores of a physical machine can be allocated to OLTP tasks and bound to specific NUMA (Non-Uniform Memory Access) nodes.

[0066] CPU resource pool for OLAP tasks: Allocate multi-core, highly parallel CPU resources to support complex analytical queries. In one example, you can allocate remaining CPU cores or use dedicated compute nodes for OLAP tasks.

[0067] (1.2) Memory resource pool allocation:

[0068] Memory resource pool for OLTP tasks: Allocate sufficient memory to cache hot data and reduce disk I / O. Use the in-memory database Redis as a caching layer to speed up transaction processing. In one example, 60% of the server's total memory can be allocated to OLTP tasks.

[0069] Memory resource pool for OLAP tasks: Allocates large amounts of memory to store intermediate results and temporary data, supporting in-memory computing for complex queries. In one example, the remaining memory resources can be allocated to OLAP tasks.

[0070] (1.3) I / O bandwidth resource pool allocation:

[0071] I / O bandwidth resource pool for OLTP tasks: Use high-speed SSD (Solid State Disk or Solid State Drive) or NVME (Non-Volatile Memory Express) storage devices to provide low-latency, high-throughput I / O performance. Configure RAID 10 or similar high-availability, high-performance storage arrays. In one example, a separate SSD storage pool can be allocated for OLTP tasks.

[0072] I / O bandwidth resource pool for OLAP tasks: Use large-capacity, high-bandwidth storage devices to support large-scale data scanning and analysis. Consider using a distributed file system (such as HDFS, Hadoop Distributed File System) or object storage (such as Ceph). In one example, a large-capacity HDD (hard disk drive) storage pool or cloud storage service can be allocated for OLAP tasks.

[0073] (2) A lightweight monitoring agent tool collects a set of database performance indicators in real time.

[0074] Due to differences in resource requirements and usage patterns between OLTP and OLAP tasks, conflicts may arise when they run on the same database system. These conflicts include: (I) Resource contention: OLAP operations can consume significant CPU and memory resources, resulting in longer response times for OLTP operations. OLAP operations typically involve large amounts of data reads, which can consume disk I / O bandwidth and impact the I / O performance of OLTP operations. (II) Lock contention: OLTP operations typically involve frequent data updates, requiring row-level or table-level locks. If OLAP operations simultaneously scan or modify the same data, lock waits or deadlocks may occur, further degrading system performance. (III) Cache pollution: OLAP operations may load large amounts of data into the database cache, pushing hot data required by OLTP operations out of the cache and increasing physical I / O overhead.

[0075] To address these issues, the present invention deploys a lightweight monitoring agent tool on the database. This tool is a custom monitoring script written in Python that collects specific performance metrics. This embodiment of the present invention uses SQL execution logs, lock wait events, and buffer hit rate as examples to illustrate:

[0076] SQL execution logs: In one example, a lightweight monitoring agent collects the database's slow query logs or audit logs to obtain SQL execution logs. In another example, a lightweight monitoring agent collects the database's native performance views (such as MySQL's information_schema, PostgreSQL's pg_stat_activity, and GBase 8s's sysmaster) to obtain real-time SQL execution logs.

[0077] Lock wait events: In one example, a lightweight monitoring agent tool monitors the lock wait events of a database (such as SHOW ENGINE INNODB STATUS for MySQL and pg_locks for PostgreSQL).

[0078] Buffer hit ratio: In one example, a lightweight monitoring agent tool calculates the database's buffer hit ratio (such as Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads for MySQL).

[0079] The above performance indicators can be stored in a time series database for subsequent analysis and query.

[0080] (3) Generate a task execution order for all pending data processing tasks in accordance with the task priority rules. This specifically includes the following steps:

[0081] (3.1) Classify tasks based on factors such as task type (e.g., query, update, delete), source (e.g., user, application), and user identity (e.g., VIP user, ordinary user);

[0082] (3.2) Based on the above classification results and task priority rules, assign a priority label (such as high, medium, or low) to each data processing task. High-priority tasks usually correspond to transactions with stricter SLA requirements.

[0083] (3.3) Monitor the usage of resource pools such as CPU, memory, and I / O in real time, as well as the execution status and resource consumption of each data processing task. Optionally, a lightweight monitoring agent can be used to collect and analyze the above data.

[0084] (3.4) Use a priority scheduling algorithm to dynamically adjust the task execution order based on the priority label, execution status, and resource consumption of each data processing task, as well as the usage of the resource pool.

[0085] Optionally, when a high-priority data processing task arrives, if system resources are insufficient, the resources of the low-priority data processing task can be preempted or the execution of the low-priority data processing task can be suspended.

[0086] Optionally, during the process of dynamically adjusting the task execution order, Linux cgroup or systemd can be used to perform resource restrictions and priority scheduling.

[0087] (4) Based on the database performance indicator set, dynamically sense the database load and predict the database load peak data corresponding to the future period. Specifically, the following steps are included:

[0088] (4.1) For any performance indicator in the performance indicator set of the database, the performance indicator is aggregated using a sliding window to determine the statistics of the performance indicator within the sliding window, and the key features corresponding to the performance indicator are extracted from the statistics.

[0089] The specific implementation process is as follows: First, use the sliding window mechanism to aggregate the collected performance indicators and calculate statistics such as the average, maximum, minimum, and standard deviation within the sliding window. The time window size can be set to 5 minutes. Then, based on actual needs, the key characteristics of the performance indicators are extracted based on these statistics, such as SQL execution frequency, average execution time, lock wait time, buffer hit rate, CPU utilization, memory utilization, I / O latency, etc.

[0090] (4.2) Using the pre-trained load prediction model, based on the key features corresponding to each performance indicator in the performance indicator set, the corresponding load peak data of the database in the future period is predicted.

[0091] In one example, the load forecasting model can adopt the LSTM model, which is a deep learning model suitable for processing time series data and can effectively capture long-term dependencies. The LSTM model can be implemented using frameworks such as TensorFlow or PyTorch.

[0092] The LSTM model training process is as follows: First, historical load data is divided into training and test sets, and the data is standardized or normalized to improve model performance. Then, using features extracted using a sliding window as input, the load peak for the next five minutes is predicted. Finally, model hyperparameters (such as the number of LSTM layers, number of hidden units, and learning rate) are adjusted to optimize prediction results.

[0093] After the LSTM model training is completed, it can be deployed to the production environment, using the real-time collected performance indicators for prediction, and regularly updating the LSTM model to adapt to changes in the load pattern.

[0094] (5) Based on the load peak data, adaptively adjust the task priority rules and the resource pools of the OLTP tasks and OLAP tasks respectively, so as to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools.

[0095] In specific implementations, system resource allocation strategies are adjusted based on predicted peak load data. Based on a comparison of actual task execution data with the SLA, resource pool sizes and task priority rules for OLTP and OLAP tasks are dynamically adjusted according to the allocation strategy. For example, if OLTP task loads consistently exceed expectations, the CPU and memory allocation to the OLTP resource pool can be temporarily increased.

[0096] (6) In the process of executing a data processing task, based on the adaptive index optimization operation and the dynamic adjustment mechanism of the plan, determine the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein, so as to determine the result corresponding to the data processing task.

[0097] In database systems, the Realtime Feedback Optimizer (RFO) is responsible for generating efficient execution plans. However, these plans are typically based on statistical information about data distribution and static assumptions about system resources. However, as data is constantly updated and system loads fluctuate, these assumptions may become invalid, resulting in decreased execution plan efficiency.

[0098] Specific issues include: (I) Data distribution changes: Data insertions, updates, and deletions can cause data distribution (such as histograms and cardinality) to deviate from the statistics initially used by the optimizer. (II) System resource fluctuations: The availability of resources such as CPU, memory, and I / O can change dynamically, affecting the selection of the optimal execution plan. (III) Plan cache invalidation: Fixed cached execution plans cannot adapt to new environments, and frequent recompilation can lead to performance overhead.

[0099] To address these issues, embodiments of the present invention utilize a dynamic plan adjustment mechanism to dynamically adjust execution plans based on the latest data distribution and system resource status. This mechanism also supports hot updates of the plan cache, reducing recompilation overhead. Furthermore, adaptive index optimization overcomes the limitations of static indexes and enables real-time adaptation of index types and structures.

[0100] In specific implementations, according to the aforementioned task execution order, the resource pools for OLTP tasks and OLAP tasks execute the OLTP tasks and OLAP tasks, respectively. That is, the resource pool for OLTP tasks executes the OLTP tasks, and the resource pool for OLAP tasks executes the OLAP tasks. This embodiment of the present invention uses one type of OLTP or OLAP task as an example to explain the task execution process, which specifically includes the following steps:

[0101] (6.1) Determine the initial execution plan corresponding to the data processing task.

[0102] (6.2) Use adaptive index optimization to determine candidate indexes and index types that match the SQL statements included in the initial execution plan. Figure 2 The adaptive index optimization flow chart shown mainly includes query pattern analysis, index generation, cost evaluation, index implementation decision, and index implementation. Specifically, it includes:

[0103] (6.21) Use a lightweight monitoring agent to capture queries being executed in real time and analyze historical query logs, identify query patterns that can benefit from indexing, and generate index sets corresponding to the query patterns. The index sets include simple indexes and compound indexes.

[0104] The embodiment of the present invention captures the executed queries in real time based on a database monitoring tool and performs query log analysis to generate all possible simple indexes corresponding to the query pattern and compound indexes supporting compound queries.

[0105] First, we use a lightweight monitoring agent to capture running queries in real time and analyze historical query logs to identify query patterns that could benefit from indexing. This includes: (I) Query log analysis: Recording and analyzing historical query logs to identify frequently executed query patterns. (II) Real-time monitoring: Using database monitoring tools to capture running queries in real time. (III) Pattern recognition: Identifying commonly used columns, join conditions, and filter conditions in queries.

[0106] Then, based on the query pattern analysis results, we dynamically generate a corresponding index set, including: (I) query-based index recommendations: generating possible index column combinations based on the WHERE, JOIN, and ORDER BY clauses in the query; (II) considering the generation of composite indexes to support complex query conditions; and (III) using algorithms (such as greedy algorithms and genetic algorithms) to perform a preliminary screening of all possible simple and composite indexes.

[0107] (6.22) The benefit and cost values ​​corresponding to each index in the index set are evaluated through the index cost model to determine the candidate index corresponding to the query pattern from the index set, and the index type corresponding to the query pattern is determined based on the load characteristics of the query pattern.

[0108] The process of determining the candidate index corresponding to the query pattern through the index cost model is as follows: Build an index cost model to evaluate the benefits and maintenance costs of the index. The index cost model includes the following factors: (I) Query performance improvement: Estimate the improvement of the index on query performance, usually measured by reducing I / O operations and CPU usage. (II) Index maintenance cost: Consider the overhead of the index in insert, update, and delete operations. The maintenance cost is usually related to the size and complexity of the index. (III) Storage overhead: The index consumes additional storage space, which is also a cost that needs to be considered.

[0109] Based on the above factors, the index cost model evaluates the benefit and cost of all simple and composite indexes obtained through preliminary screening for further selection. This selection can include: (I) Benefit-Cost Ratio: Calculate the benefit-to-cost ratio of each index and select those with the highest ratio. (II) Threshold Setting: Set a threshold, and only indexes with a benefit-to-cost ratio above the threshold will be considered for creation.

[0110] Among them, index types include B+ tree indexes and Bloom filter indexes. B+ trees perform well in range queries and sequential access and are suitable for processing range queries (such as WHERE column BETWEEN value1 AND value2). Therefore, when the query load is a range query, the index is switched to a B+ tree index. Bloom filters are probabilistic data structures that can efficiently determine whether an element exists in a set and are suitable for processing equality queries (such as WHERE column = value). Therefore, when the query load is an equality query, the index is switched to a Bloom filter. Based on this, when the query load characteristic is a range query, the query type of the query mode is determined to be a B+ tree index; when the query load characteristic is an equality query, the query type of the query mode is determined to be a Bloom filter index. In a specific application example, if it is detected that a table frequently executes WHERE status='active' queries, the index is automatically switched to a Bloom filter index. When the proportion of range queries exceeds a threshold, the index is automatically switched to a B+ tree index and redundant indexes are merged.

[0111] (6.23) Based on the query pattern of the SQL statements contained in the initial execution plan, determine the candidate indexes and index types that match the SQL statements.

[0112] (6.3) The RFO optimizer dynamically adjusts the initial execution plan to the target execution plan based on a dynamic plan adjustment mechanism. RFO is a query optimizer based on real-time feedback. Through dynamic monitoring, feedback analysis, and adaptive adjustment, it continuously optimizes the execution plan to cope with changes in data distribution and system load fluctuations. Specifically, it includes:

[0113] (6.31) Determine the actual execution state of the database based on a set of performance metrics for the database; and determine the predicted execution state of the database using the optimizer cost model of the RFO optimizer.

[0114] In one example, a lightweight monitoring agent tool is used to track database data distribution and monitor resources.

[0115] Data distribution tracking involves maintaining lightweight, real-time statistics (such as column cardinality and data skew), updated through sampling or incremental computation. Specifically, it tracks the number of unique values ​​(cardinality) in a column and the degree of data distribution skew (e.g., some values ​​appear much more frequently than others). This information is quickly updated through sampling or incremental computation (e.g., counters and histograms) to avoid full table scans. For example, if the number of unique values ​​in a column decreases from 1,000 to 100, RFO will detect an increase in data skew and may adjust the index selection strategy.

[0116] Resource monitoring involves monitoring the real-time usage of resources such as CPU, memory, and I / O to assess system load. Specifically, if CPU utilization exceeds 80%, RFO tends to select plans with low CPU overhead (such as avoiding complex sorting); when memory is insufficient, it avoids memory-intensive operations (such as hash joins); and when I / O load is high, it prioritizes plans with high cache hit rates. In high-concurrency scenarios, RFO may choose a full table scan (if the table is small and memory is sufficient) instead of an index scan to reduce CPU overhead. For example, if CPU utilization exceeds 80%, RFO may tend to select an execution plan with low CPU overhead.

[0117] Based on the aforementioned data distribution tracking and resource monitoring, the RFO optimizer analyzes the current query's execution status (multi-dimensional metrics such as actual I / O counts, CPU time, and network latency) and compares it with the execution status predicted by its optimizer's cost model (referred to as the predicted execution status). If any deviation is found, the initial execution plan will be dynamically rewritten. For example, if the actual I / O of an index scan is 50% higher than predicted, the RFO optimizer may recommend a full table scan instead (if the table is small) to obtain the rewritten target execution plan. If the actual CPU time of a join operation is significantly higher than predicted, RFO may adjust the join algorithm (for example, switching from a hash join to a nested loop).

[0118] (6.32) When the actual execution state deviates from the predicted execution state, the RFO optimizer uses its dynamic plan adjustment mechanism to dynamically adjust the initial execution plan to the target execution plan. For example, the initial execution plan selects a full table scan. After detecting data skew, the RFO optimizer automatically adjusts to an index scan plus parallel partition query. It should be noted that this adjustment is triggered only when the deviation exceeds a certain threshold to avoid frequent fluctuations.

[0119] (6.33) Dynamically adjust the model parameters of the optimizer cost model based on the initial execution plan and the target execution plan.

[0120] In one example, reinforcement learning can be used to dynamically adjust the optimizer's cost model parameters (such as I / O cost and CPU cost). For example, if the I / O cost of a certain type of query is underestimated, RFO will increase the I / O cost weight through reinforcement learning. This includes: state definition: current query characteristics (such as table size, index distribution, and historical execution time); action selection: adjusting cost model parameters (such as increasing the I / O cost weight); reward feedback: providing rewards or penalties based on the actual performance of the execution plan (such as response time); and strategy update: optimizing parameters and adjusting strategies through reward feedback.

[0121] Furthermore, the optimizer's decision thresholds (such as whether to use indexes) can be dynamically adjusted based on system load. For example, when CPU utilization is high, the threshold for using indexes is raised (increasing the likelihood of a full table scan); when memory is sufficient, the threshold for using hash joins is lowered (increasing the likelihood of a hash join). Thresholds can also be dynamically adjusted based on resource monitoring data (such as CPU, memory, and I / O).

[0122] (6.4) Based on the target execution plan and the candidate indexes and index types matched by the SQL statements contained therein, perform data queries to obtain the results corresponding to the data processing task.

[0123] (6.5) Cache the target execution plan so that, when a new data processing task is received and the SQL statements contained in the new data processing task match the SQL statements contained in the cached target execution plan, the cached target execution plan is used to execute the result of the current new data processing task. For example, if the SQL statements contained in the new data processing task are completely consistent with the SQL statements contained in the target execution plan, the cached target execution plan is directly called; if the SQL statements contained in the new data processing task are partially consistent or similar to the SQL statements contained in the target execution plan, an execution plan is regenerated for the inconsistent or dissimilar SQL statements, and the cached target execution plan is hot-updated using the newly generated execution plan to obtain the target execution plan corresponding to the new data processing task.

[0124] The RFO optimizer updates the adjusted target execution plan to the database cache to avoid full recompilation. The caching mechanism is as follows: the generated target execution plan is stored in a specific area of ​​memory. When the same or similar SQL statement is executed again, the database checks the cache for a matching target execution plan. If a matching target execution plan is found, it is used directly, avoiding repeated optimization.

[0125] Taking into account that the SQL statements contained in different data processing tasks may be only partially identical or similar, the embodiments of the present invention further provide incremental update functions and version control functions. The incremental update function refers to modifying only the affected operators in the plan (such as index selection, connection order), and the version control function refers to retaining the old plan and supporting rollback (such as when the performance of the new plan is worse). For example, if the performance of a query degrades after the index selection strategy is adjusted, RFO can quickly roll back to the old plan and mark the new plan as "pending verification". In the embodiments of the present invention, the execution plan cache is an important mechanism for optimizing query performance. It avoids repeated parsing and optimization of the same query by storing compiled query execution plans.

[0126] (7) If a new data processing task is received, repeat (3) to (6) and continue to generate a task execution order for all pending data processing tasks according to the adjusted task priority rules until the results corresponding to all pending data processing tasks are obtained.

[0127] In summary, the database query method based on dynamic load awareness and adaptive index optimization provided by the embodiments of the present invention has at least the following features: (I) Dynamic load-driven hybrid indexing technology: This breaks through the limitations of static indexing and enables real-time adaptation of index types and structures. (II) A lightweight real-time feedback optimizer: This dynamically generates optimal execution plans by combining online statistical information and resource status. (III) Resource isolation and SLA guarantee mechanisms: This effectively balances the conflicting demands of OLTP and OLAP workloads, improving system stability.

[0128] Based on the above embodiments, the present invention provides a database query device based on dynamic load perception and adaptive index optimization. The device is applied to a database. The database provides independent resource pools for OLTP tasks and OLAP tasks respectively. The database server is installed with a lightweight monitoring agent tool, which is used to collect a set of database performance indicators in real time. Figure 3 The structure diagram of a database query device based on dynamic load perception and adaptive index optimization is shown, and the device mainly includes the following parts:

[0129] A task planning module 302 is used to generate a task execution order for all pending data processing tasks according to a task priority rule. Data processing tasks are classified as OLTP tasks or OLAP tasks.

[0130] Dynamic load sensing module 304 is used to perform dynamic load sensing on the database based on the database performance indicator set and predict the corresponding load peak data of the database in the future period. The load peak data is used to describe the conflict period between OLTP tasks and OLAP tasks;

[0131] An adaptive adjustment module 306 is configured to adaptively adjust the task priority rules and the resource pools of the OLTP tasks and the OLAP tasks based on the load peak data, so as to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools;

[0132] The index optimization and dynamic adjustment module 308 is used to determine the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein based on the adaptive index optimization operation and plan dynamic adjustment mechanism during the execution of the data processing task, so as to determine the result corresponding to the data processing task;

[0133] The repetitive execution module 310 is used to return to calling the task planning module 302 to index optimization if a new data processing task is received, until the results corresponding to all the data processing tasks to be executed are obtained.

[0134] The database query device based on dynamic load perception and adaptive index optimization provided by the embodiment of the present invention predicts the corresponding load peak data of the database in the future time period through dynamic load perception, and then adaptively adjusts the resource pools of the OLTP tasks and OLAP tasks, thereby effectively balancing the conflicting demands of the OLTP tasks and the OLAP tasks and improving the stability of the system; in addition, the target execution plan of the data processing task is generated through the adaptive index optimization operation and the dynamic adjustment mechanism of the plan, which can better break through the limitations of static indexes, realize real-time adaptation of the index structure and the data processing tasks, and generate the optimal target execution plan, thereby significantly improving the limitations of traditional technologies and better meeting complex and changeable actual needs.

[0135] In one embodiment, the dynamic load sensing module 304 is specifically configured to:

[0136] For any performance indicator in the performance indicator set of the database, the performance indicator is aggregated using a sliding window to determine the statistics of the performance indicator within the sliding window, and the key features corresponding to the performance indicator are extracted from the statistics;

[0137] Through the pre-trained load prediction model, based on the key features corresponding to each performance indicator in the performance indicator set, the corresponding load peak data of the database in the future period is predicted.

[0138] In one embodiment, the adaptive adjustment module 306 is specifically configured to:

[0139] Determine the initial execution plan corresponding to the data processing task;

[0140] Using adaptive index optimization operations, determine candidate indexes and index types that match the SQL statements included in the initial execution plan;

[0141] Through the RFO optimizer, based on the plan dynamic adjustment mechanism, the initial execution plan is dynamically adjusted to obtain the target execution plan;

[0142] Based on the target execution plan and the candidate indexes and index types matched by the SQL statements contained therein, data queries are performed to obtain the results corresponding to the data processing tasks.

[0143] In one embodiment, the adaptive adjustment module 306 is specifically configured to:

[0144] The target execution plan is cached so that when a new data processing task is received, if the SQL statement contained in the new data processing task matches the SQL statement contained in the cached target execution plan, the cached target execution plan is directly called.

[0145] In one embodiment, the adaptive adjustment module 306 is specifically configured to:

[0146] A lightweight monitoring agent tool captures running queries in real time and analyzes historical query logs to identify query patterns that can benefit from indexing. It then generates index sets corresponding to the query patterns, including simple and composite indexes.

[0147] The index cost model is used to evaluate the benefit and cost of each index in the index set to determine the candidate index corresponding to the query pattern from the index set. The index type corresponding to the query pattern is then determined based on the load characteristics of the query pattern.

[0148] Based on the query pattern of the SQL statement contained in the initial execution plan, determine the candidate indexes and index types that match the SQL statement.

[0149] In one embodiment, the index types include B+ tree index and Bloom filter index; the adaptive adjustment module 306 is specifically configured to:

[0150] When the load characteristic of the query mode is a range query, the query type of the query mode is determined to be a B+ tree index;

[0151] When the load characteristic of the query mode is an equal value query, it is determined that the query type of the query mode is a Bloom filter index.

[0152] In one embodiment, the adaptive adjustment module 306 is specifically configured to:

[0153] Determine the actual execution state of the database based on the database performance indicator set; and determine the predicted execution state of the database through the optimizer cost model of the RFO optimizer;

[0154] When there is a deviation between the actual execution state and the predicted execution state, the RFO optimizer is used to dynamically adjust the initial execution plan based on the plan dynamic adjustment mechanism to obtain the target execution plan.

[0155] In one embodiment, the adaptive adjustment module 306 is specifically configured to:

[0156] Based on the initial execution plan and the target execution plan, the model parameters of the optimizer cost model are dynamically adjusted.

[0157] The device provided in the embodiment of the present invention has the same implementation principle and technical effects as those in the aforementioned method embodiment. For the sake of brief description, for matters not mentioned in the device embodiment, reference can be made to the corresponding content in the aforementioned method embodiment.

[0158] An embodiment of the present invention provides an electronic device. Specifically, the electronic device includes a processor and a storage device. The storage device stores a computer program, and when the computer program is executed by the processor, it executes the method described in any one of the above-mentioned embodiments.

[0159] Figure 4 This is a structural diagram of an electronic device provided in an embodiment of the present invention. The electronic device 100 includes: a processor 40, a memory 41, a bus 42 and a communication interface 43. The processor 40, the communication interface 43 and the memory 41 are connected via the bus 42; the processor 40 is used to execute an executable module stored in the memory 41, such as a computer program.

[0160] Memory 41 may include high-speed random access memory (RAM) and may also include non-volatile memory, such as at least one disk storage device. Communication between the system network element and at least one other network element is achieved through at least one communication interface 43 (which may be wired or wireless), and may utilize the Internet, a wide area network, a local area network, a metropolitan area network, or the like.

[0161] The bus 42 may be an ISA bus, a PCI bus, or an EISA bus. The bus may be divided into an address bus, a data bus, a control bus, and the like. For ease of representation, Figure 4 Only one bidirectional arrow is used in the diagram, but this does not mean that there is only one bus or one type of bus.

[0162] Among them, the memory 41 is used to store programs, and the processor 40 executes the program after receiving the execution instruction. The method executed by the device for flow process definition disclosed in any embodiment of the above-mentioned embodiment of the present invention can be applied to the processor 40 or implemented by the processor 40.

[0163] Processor 40 may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method may be completed by hardware integrated logic circuits or software instructions in processor 40. The above processor 40 may be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it may also be a digital signal processing unit (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It may implement or execute the various methods, steps, and logic block diagrams disclosed in the embodiments of the present invention. The general-purpose processor may be a microprocessor or any conventional processor. The steps of the method disclosed in conjunction with the embodiments of the present invention may be directly implemented and executed by a hardware decoding processor, or by a combination of hardware and software modules in the decoding processor. The software module may be located in a storage medium well-known in the art, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, etc. The storage medium is located in the memory 41 , and the processor 40 reads the information in the memory 41 and completes the steps of the above method in combination with its hardware.

[0164] The computer program product of the readable storage medium provided in the embodiment of the present invention includes a computer-readable storage medium storing program code. The instructions included in the program code can be used to execute the method described in the previous method embodiment. The specific implementation can be referred to the previous method embodiment and will not be repeated here.

[0165] If the functions are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the portion that contributes to the prior art, or a portion of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. The aforementioned storage media include various media capable of storing program code, such as USB flash drives, mobile hard drives, read-only memories (ROMs), random access memories (RAMs), magnetic disks, or optical disks.

[0166] Finally, it should be noted that the above-described embodiments are only specific implementation methods of the present invention, which are used to illustrate the technical solutions of the present invention, rather than to limit them. The scope of protection of the present invention is not limited thereto. Although the present invention has been described in detail with reference to the above-described embodiments, those skilled in the art should understand that any person skilled in the art can modify or easily conceive of changes to the technical solutions described in the above-described embodiments within the technical scope disclosed by the present invention, or replace some of the technical features therein with equivalents. Such modifications, changes, or replacements do not deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should be included in the scope of protection of the present invention. Therefore, the scope of protection of the present invention shall be subject to the scope of protection of the claims.

Claims

1. A database query method based on dynamic load perception and adaptive index optimization, characterized in that: The method is applied to a database, wherein the database provides independent resource pools for OLTP tasks and OLAP tasks respectively, and a lightweight monitoring agent tool is installed on the database server, and the lightweight monitoring agent tool is used to collect a set of performance indicators of the database in real time; the method includes: Step 1: generating a task execution order for all data processing tasks to be executed according to a task priority rule, wherein the data processing tasks are classified as the OLTP tasks or the OLAP tasks; Step 2: Based on the performance indicator set of the database, dynamically sense the database load and predict the corresponding load peak data of the database in a future period. The load peak data is used to describe the conflict period between the OLTP task and the OLAP task. Step 3: Based on the load peak data, adaptively adjust the task priority rule and the resource pools of the OLTP task and the OLAP task, so as to execute the data processing tasks in the task execution order based on the adaptively adjusted resource pools; Step 4: During the execution of the data processing task, based on the adaptive index optimization operation and the dynamic plan adjustment mechanism, determine the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein, so as to determine the result corresponding to the data processing task; Step 5: If a new data processing task is received, return to execute step 1 until the results corresponding to all the data processing tasks to be executed are obtained.

2. The database query method based on dynamic load perception and adaptive index optimization according to claim 1 is characterized in that: Based on the performance indicator set of the database, dynamic load perception is performed on the database, and corresponding load peak data of the database in a future time period is predicted, including: For any of the performance indicators in the performance indicator set of the database, aggregating the performance indicator using a sliding window to determine a statistic of the performance indicator within the sliding window, and extracting a key feature corresponding to the performance indicator from the statistic; The load peak data corresponding to the database in a future time period is predicted by a pre-trained load prediction model based on the key feature corresponding to each performance indicator in the performance indicator set.

3. The database query method based on dynamic load perception and adaptive index optimization according to claim 1 is characterized in that: Based on the adaptive index optimization operation and the dynamic plan adjustment mechanism, determining the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein, so as to determine the result corresponding to the data processing task, including: Determining an initial execution plan corresponding to the data processing task; Determining, by using an adaptive index optimization operation, candidate indexes and index types that match the SQL statements included in the initial execution plan; The RFO optimizer dynamically adjusts the initial execution plan based on the plan dynamic adjustment mechanism to obtain the target execution plan; Based on the target execution plan and the candidate indexes and index types matched by the SQL statements contained therein, a data query is performed to obtain a result corresponding to the data processing task.

4. The database query method based on dynamic load perception and adaptive index optimization according to claim 3 is characterized in that: After obtaining the target execution plan and the candidate index and index type matched by the SQL statement contained therein, the method further includes: The target execution plan is cached so that when a new data processing task is received, if the SQL statement contained in the new data processing task matches the SQL statement contained in the cached target execution plan, the cached target execution plan is directly called.

5. The database query method based on dynamic load perception and adaptive index optimization according to claim 3 is characterized in that: By using an adaptive index optimization operation, candidate indexes and index types that match the SQL statements included in the initial execution plan are determined, including: Based on the lightweight monitoring agent tool, the tool captures the queries being executed in real time and analyzes historical query logs, identifies query patterns that can benefit from indexing, and generates an index set corresponding to the query pattern, wherein the index set includes simple indexes and compound indexes; Evaluate the benefit and cost of each index in the index set using an index cost model to determine a candidate index corresponding to the query pattern from the index set, and determine the index type corresponding to the query pattern based on a load characteristic of the query pattern; Based on the query pattern to which the SQL statement included in the initial execution plan belongs, the candidate index and the index type that the SQL statement matches are determined.

6. The database query method based on dynamic load perception and adaptive index optimization according to claim 5 is characterized in that: The index types include B+ tree index and Bloom filter index; Determining an index type corresponding to the query pattern based on a load characteristic of the query pattern includes: In a case where the load characteristic of the query mode is a range query, determining that the query type of the query mode is the B+ tree index; In a case where the load characteristic of the query pattern is an equal value query, the query type of the query pattern is determined to be the Bloom filter index.

7. The database query method based on dynamic load perception and adaptive index optimization according to claim 3 is characterized in that: The RFO optimizer dynamically adjusts the initial execution plan based on the plan dynamic adjustment mechanism to obtain the target execution plan, including: determining an actual execution state of the database based on the set of performance indicators of the database; and determining a predicted execution state of the database using an optimizer cost model of an RFO optimizer; In the case where there is a deviation between the actual execution state and the predicted execution state, the RFO optimizer is used to dynamically adjust the initial execution plan based on a plan dynamic adjustment mechanism to obtain a target execution plan.

8. The database query method based on dynamic load perception and adaptive index optimization according to claim 7 is characterized in that: The method further comprises: Based on the initial execution plan and the target execution plan, model parameters of the optimizer cost model are dynamically adjusted.

9. A database query device based on dynamic load perception and adaptive index optimization, characterized in that: The device is applied to a database, wherein the database provides independent resource pools for OLTP tasks and OLAP tasks respectively, and a lightweight monitoring agent tool is installed on the database server, wherein the lightweight monitoring agent tool is used to collect a set of performance indicators of the database in real time; the device comprises: A task planning module is used to generate a task execution order for all pending data processing tasks according to task priority rules. The data processing tasks are classified as OLTP tasks or OLAP tasks. a dynamic load sensing module, configured to perform dynamic load sensing on the database based on the performance indicator set of the database, and predict corresponding load peak data of the database in a future period, wherein the load peak data is used to describe a conflict period between the OLTP task and the OLAP task; an adaptive adjustment module, configured to adaptively adjust the task priority rule and the respective resource pools of the OLTP task and the OLAP task based on the load peak data, so as to execute the data processing task in the task execution order based on the adaptively adjusted resource pool; An index optimization and dynamic adjustment module is used to determine, during the execution of the data processing task, the target execution plan corresponding to the data processing task and the candidate indexes and index types that match the SQL statements contained therein based on the adaptive index optimization operation and plan dynamic adjustment mechanism, so as to determine the result corresponding to the data processing task; The repeated execution module is used to return to call the task planning module if a new data processing task is received until the results corresponding to all the data processing tasks to be executed are obtained.

10. A database, characterized in that The method comprises a processor and a memory, wherein the memory stores computer-executable instructions that can be executed by the processor, and the processor executes the computer-executable instructions to implement the method according to any one of claims 1 to 8.

Images