SQL is a declarative language that expresses only what the user wants to achieve, while the specific implementation of the user's intent is determined by the database's optimizer module, which selects the appropriate execution plan for the query. For the same user SQL statement, there may be multiple different implementation approaches in the database. Common choices faced by the optimizer include:
Whether to perform a full table scan or an index scan for table access in a query, and which index should be selected.
Choosing the JOIN order for JOIN operations in the query and the JOIN algorithm for each JOIN.
Selecting a policy for implementing GROUP BY and ORDER BY in queries.
Selecting a policy to handle subqueries in queries.
These choices generate a large number of combination results, that is, candidate execution plans, and the optimizer will select the one it deems optimal from these candidate execution plans for execution.
In most cases, the optimizer can select execution plans with higher execution efficiency. However, there are inevitably scenarios where it fails to choose the optimal execution plan due to two possible reasons:
The optimizer failed to enumerate the optimal candidate execution plan.
The optimizer encountered deviations when evaluating all candidate execution plans, and the optimal execution plan was not selected.
In such scenarios, if the execution plan selected by the optimizer by default exhibits very low efficiency, DBAs or users need to perform SQL tuning to enhance query performance.
To perform SQL tuning, it is first necessary to understand the execution logic represented by the execution plan selected by the optimizer, identify the parts with high execution costs, analyze why the optimizer made such choices, and then intervene with the optimizer's selection of execution plans to ensure that the desired better execution plan is chosen.