Abstract
In the digital age, the explosive growth of data and the need for instant performance pose challenges to database management systems. In a big data environment, query performance has become a key issue, and index optimization is a key means to improve performance. This paper explores the implementation of machine learning-driven automatic indexing to solve performance bottlenecks. By utilizing an "AI cycle" that captures, identifies, and verifies index candidates, the system autonomously reduces query times and resource consumption without manual intervention from a Database Administrator (DBA).
Introduction
In the era of big data, query performance is the lifeblood of database systems. Traditionally, DBAs managed performance through manual indexing—deciding which columns (like IDs or dates) should have a "shortcut" (index) to speed up searches. However, big data environments move too fast for human intervention. Query patterns change rapidly, and the sheer volume of data makes manual tuning a reactive and inefficient process. This research investigates how reinforcement learning and automated "invisible" testing can make indexing proactive rather than reactive, moving toward a truly "self-driving" database architecture.
The Automatic Indexing AI Cycle
The researchers propose and test an Automatic Indexing Artificial Intelligence Cycle, which allows the database to learn and adapt to workloads autonomously.
The cycle consists of five distinct phases:
- Capture: The system monitors the application workload and captures all SQL statements into a "tuning set."
- Identify: It analyzes query performance and suggests candidate indexes.
- Verify: This is a "safety first" step. The system creates these indexes but keeps them invisible to the general optimizer. It then runs tests to measure performance with and without the candidate.
- Decide: If the index provides a clear benefit, it is made "visible" (active). If it causes a performance regression, the system creates a "SQL plan baseline" to ensure those specific queries don't use the problematic index.
- Monitor: The system continuously tracks performance. If an index becomes useless over time, it is automatically deleted.
For the predictive element, the authors utilized Decision Tree models to learn from historical execution plans and database statistics, predicting which index configurations would be most effective for specific query types.
Experimental Evaluation
The study used a representative large-scale synthetic dataset to simulate a real-world big data environment. The dataset included approximately 900,000 random records across tables for customers, products, orders, and order details. This structure was designed to support multi-table associations and complex filtering queries (using WHERE conditions) to mimic actual business workloads.
Performance Results
The experimental results, conducted on Oracle 21c, showed dramatic improvements across several key performance indicators. The core optimization observed was the transition from a "Full Table Scan" (reading every single row) to an "Index Range Scan" (jumping straight to the relevant data).
Key findings include:
- Latency Reduction: The "Elapsed Time" for queries dropped from roughly 393 million units to just 5 million—a massive reduction in latency.
- CPU Efficiency: CPU time was reduced by nearly 99% (from 161M to 1.7M units).
- I/O Optimization: "Disk Reads" dropped to zero, indicating the system was successfully hitting memory-resident indexes.
- Optimizer Cost: The internal "cost" calculated by the database optimizer for query execution dropped from 86,828 to 32,751, effectively cutting the computational burden by more than half.
Conclusion
The implementation of an automated indexing cycle represents a significant step toward autonomous database management. By using "invisible indexes" for A/B testing and "blacklisting" failed candidates, the system ensures performance stability without manual DBA intervention. While challenges remain regarding data heterogeneity and high-dimensional data distribution patterns, this approach demonstrates the potential for machine learning to significantly optimize big data query performance.
Summary updated