Tree-IS: Efficient Index Selection and Optimization Model for Dynamic Workloads

Area of Science:

• Database Systems
• Artificial Intelligence
• Computer Science

Background:

• Traditional index selection struggles with large, complex SQL queries.
• Existing methods often neglect index maintenance costs and update needs.
• Efficient database index selection is critical for query optimization.

Purpose of the Study:

• To propose an efficient and accurate index selection model for large-scale SQL queries.
• To address the limitations of traditional methods by incorporating maintenance costs.
• To enhance database query performance through intelligent index selection.

Main Methods:

• Developed a network-optimized Monte Carlo tree search (NMCTS) model named Tree-IS.
• Utilized workload-driven query template extraction and candidate index (CI) generation.
• Integrated a novel query state representor and an execution-plan-based index value model (IVM).
• Employed a state abstraction network (SAN), policy network, and value network (VN) to optimize MCTS.

Main Results:

• Tree-IS achieved rapid identification of optimal index sets.
• Demonstrated significant improvements in query efficiency compared to existing methods.
• Experimental results on JOB, TPC-H, and TPC-DS datasets showed superior index selection quality.

Conclusions:

• The proposed Tree-IS model offers a superior approach to database index selection.
• It effectively handles complex SQL queries and considers index maintenance.
• This method significantly enhances overall database query performance.