SQL tuning refers to adjusting SQL execution strategies to maximize resource utilization by comprehensively analyzing factors such as SQL execution plans, execution monitoring information, system configuration, and system load.
SQL tuning can generally be divided into single SQL tuning and system SQL tuning.
Single SQL tuning
The optimization target of single SQL tuning is the SQL execution itself. The tuning targets generally include the SQL execution time and runtime resource consumption. General tuning measures used for basic SQL tuning include the change of the access path, the adjustment of the execution order, and the rewriting of the execution logic.
Tuning of a single SQL query involves two scenarios: single-table access and multi-table access.
| Scenario | Checklist |
|---|---|
| Single-table access |
|
| Multi-table access | Apart from the SQL tuning items of single-table access, you need to pay attention to multi-table joining:
|
System SQL tuning
The goal of system SQL tuning is to improve overall system throughput or system utilization. The system SQL tuning process often requires combining multiple SQL execution plans to comprehensively analyze the load characteristics of the current system, focusing on global tuning points such as hotspot row competition and buffer cache hit rate.
Throughput performance tuning mainly considers maximizing the request processing capacity of the database system under certain resources (CPU, I/O, network, etc.). The following aspects are the main focus:
| Main method | Description |
|---|---|
| Optimize slow SQL | Find the specific slow SQL and perform performance tuning for it. For more information, see Query the TOP N requests with the most execution time in a period. |
| Balance SQL request traffic resources | For more information, see Check whether SQL request traffic is balanced in the cluster. The main factors affecting balance are:
|
| Balance subplan RPC request traffic resources | For more information, see Check whether distributed subplan RPC execution count is balanced. The main factors affecting subplan request balance are:
|