SQL tuning refers to the process of 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-statement tuning and system-level SQL tuning.
Single-statement tuning
Single-statement tuning focuses on the execution of the SQL statement being tuned. Common tuning objectives include reducing the execution time and runtime resource consumption. Common tuning techniques include modifying the access path, adjusting the execution order, and rewriting the query logic.
Performance tuning for a single SQL statement's execution plan can be further divided into two scenarios: single-table access and multi-table access.
Scenario |
Focus areas of SQL tuning |
|---|---|
| Single-table access |
|
| Multi-table access | Apart from the SQL tuning items of single-table access, you need to pay attention to multi-table joins:
|
System-level SQL tuning
The purpose of system-level SQL tuning is to improve the overall system throughput or utilization. This process often requires analyzing the execution plans of multiple SQL statements and comprehensively assessing the current system's workload characteristics. It primarily focuses on global tuning points such as hotspot row contention and buffer cache hit rates.
Throughput performance tuning aims to maximize the database system's request processing capacity under given resources (CPU, I/O, network, etc.). Key considerations include the following aspects:
Method |
Description |
|---|---|
| Optimize slow SQL queries | Find a specific slow SQL query and optimize its performance. For more information, see Find the TOP N queries with the longest execution time within a certain period. |
| Balance the traffic of SQL queries | For more information, see Check whether SQL queries are balanced across servers in a cluster. The main factors that may affect the balance are as follows:
|
| Balance the resources for RPCs of subplans | For more information, see Check whether the RPC execution count of a distributed subplan is balanced across servers in a cluster. The main factors that may affect the balance are as follows:
|