Like most major commercial databases, AnalyticDB for MySQL generates an optimized query execution plan for each query statement. The optimized plan includes the calculation operator and the table join order. This topic describes the execution plan categories in AnalyticDB for MySQL.

The execution plans in AnalyticDB for MySQL include the following categories: Join Order, Join Type, Join Method, Access Method, Data Shuffle, and Aggregation.

Join Order

Based on the workload characteristics of queries, AnalyticDB for MySQL provides various join reordering strategies, including left-deep tree (LDT) and bushy tree (BT). LDT is ideal for scenarios where the tables to be joined have the same partitions, the optimization cost is low, and queries are simple. BT is ideal for complex queries and requires a longer period of time to be optimized.

Note The outer table of a join is called the left table and the inner table of a join is called the right table in AnalyticDB for MySQL.

Join Type

The join types in AnalyticDB for MySQL include inner join, left outer join, right outer join, full outer join, and semi or anti join. The corresponding outputs from EXPLAIN are inner join, left join, right join, full join, and semi join.

Note An anti join is displayed as a semi join and a FILTER clause in AnalyticDB for MySQL.

Access Method

By default, single-table access in AnalyticDB for MySQL is index access through filter pushdown. Index access fully utilizes the benefits of the full-text index design in AnalyticDB for MySQL. Therefore, query execution plans are not required to mention whether index access is used. Typically, the related operator is displayed as TableScan. In specific scenarios, you can specify a no_index hint to avoid using indexes.

Data Shuffle

In the MPP shared-nothing architecture of AnalyticDB for MySQL, data shuffling (redistribution) may be required based on the characteristics of queries and data distribution. Data shuffling involves the redistribution of base table data and results generated during query execution. In AnalyticDB for MySQL, data shuffling uses two data redistribution strategies: broadcast and repartition. The corresponding outputs from EXPLAIN are RemoteExchange[REPLICATED] and RemoteExchange[REPARTITION], respectively. In addition, the RemoteExchange[GATHER] operator corresponds to data gathering.

Aggregation

Both batch queries and interactive queries of a data warehouse contain different types of aggregations. In AnalyticDB for MySQL, all these aggregation operators are displayed as Aggregation in the outputs of EXPLAIN, and specific aggregation operator types are marked. AnalyticDB for MySQL determines whether to split distributed aggregation computing into multi-step distributed aggregation computing based on data characteristics.