SQL tuning is the process of adjusting SQL execution policies 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 | * Whether index scanning is enabled for access path: Index scanning reduces the amount of data to read. * Whether a proper index is created: Index sorting reduces time-consuming operations such as sorting and aggregation. * Whether partition pruning conditions are correctly set: Appropriate partitioning conditions reduce unnecessary partition access. * Whether the degree of parallelism is improved: When a great number of partitions need to be accessed, a higher degree of parallelism improves the performance of an SQL query at the cost of higher resource consumption. |
| Multi-table access | Apart from the SQL tuning of a single table, you need to pay attention to multi-table joining: * Join order * Join algorithms * Mode of data redistribution in cross-zone or parallel joins * Query rewrite |
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 measures of systematic SQL tuning.
| Typical measure | 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 specified period. |
| Balance the traffic of SQL queries | For more information, see Check whether SQL queries are balanced across servers in a cluster. Main factors affecting the balance: * Settings of the ob_read_consistency parameter * Settings of the primary zone * Settings of the proxy or Java client routing policy * Partitioning for frequently executed queries |
| 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. Main factors affecting the balance: * Settings of the internal routing policy of OBServer * Partitioning for frequently executed queries |