SQL tuning is to optimize the execution plan for an SQL statement.
Plan optimization typically encounters two types of issues:
In cases where the plan space is too small, the optimizer may not be able to select a good execution plan, leading to consistently slow SQL performance in practical scenarios.
On the other hand, when the plan space is too large, the optimizer may mistakenly select a suboptimal execution plan, resulting in fluctuating SQL performance. In extreme situations, this can impact the entire cluster and overload the CPU.
When the plan space is too small, a common optimization method is to add appropriate indexes, which can improve the selection of base table paths and expand the plan space. This is quite achievable as long as there is a comprehensive understanding of index selection and optimization. On the other hand, when the plan space is too large, it becomes more difficult for the optimizer to choose the right plan. In such cases, a common optimization method is to add hints to control the optimizer's plan selection.
In OceanBase Database, we can also plan and design better indexes to help the optimizer filter out poor execution plans based on rules.