In AP complex scenarios, it is important to choose cost-based SQL optimization (CBO) plans.
The cost-based SQL optimizer of AnalyticDB for MySQL has the following characteristics:
- Efficient search framework
- CBO uses the Cascades framework that is more scalable than System R solutions and supports multiple dimensions to seek optimal solutions. The Adaptive Join Reorder mechanism is used for joins of multiple tables to balance the search time and optimization effects.
- Oriented to distributed scenarios
- A complete set of Property Enforcement framework is designed and implemented to describe the data distribution requirements of distributed plans in distributed scenarios. And the Property Enforcement process and search framework are seamlessly combined to implement CBO of distributed massively parallel processing (MPP) databases.
- Diversified cost estimation systems
- The reliability of cost estimation is improved based on three phases: pre-event, mid-event, and post-event. In the pre-event phase, tasks are automatically collected based on statistical information. This provides necessary support for the optimizer to make decisions. In the mid-event phase, dynamic adjustments are made based on feedbacks returned during execution by using the Adaptive Query Optimization method. In the post-event phase, cost estimation is optimized based on the feedbacks on historical queries.
- Automated statistics collection
- Statistical information is divided into two categories: basic statistical information and advanced statistical information. Basic statistical information is automatically collected. Advanced statistical information can be manually collected, which is similar to column groups.