SQL tuning is 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.
It includes basic SQL tuning and systematic SQL tuning.
Basic SQL tuning
In this mode where the main task is tuning the execution of a SQL query, the target generally includes optimization of the SQL execution time and resource consumption during the execution. 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:
|
Systematic SQL tuning
Systematic SQL tuning aims to improve the overall throughput or availability of the system. During systematic SQL tuning, multiple SQL execution plans are leveraged to analyze load characteristics, and global tuning points of the current system, such as hotspot row competition and buffer cache hit rate.
Throughput performance tuning aims at maximizing the request processing capacity of the database system based on a specified amount of resources (such as CPU, I/O, and network). The following table describes some typical methods of systematic SQL tuning.
| 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:
|