Overview of Optimizing SQL Performance
This document introduces how to optimize the performance of SQL statements in TiDB. To get good performance, you can start with the following aspects:
- SQL performance tuning
- Schema design: Based on your application workload patterns, you might need to change the table schema to avoid transaction contention or hot spots.
SQL performance tuning
To get good SQL statement performance, you can follow these guidelines:
- Scan as few rows as possible. It is recommended to scan only the data you need and avoid scanning excess data.
- Use the right index. Ensure that there is a corresponding index for the column in the
WHEREclause in SQL. If not, the statement entails a full table scan and thus causes poor performance. - Use the right join type. It is important to choose the right join type based on the relative size of the tables involved in the query. In general, TiDB's cost-based optimizer picks the best-performing join type. However, in a few cases, you might need to manually specify a better join type.
- Use the right storage engine. For hybrid OLTP and OLAP workloads, the TiFlash engine is recommended. For details, see HTAP Query.
Schema design
After tuning SQL performance, if your application still cannot get good performance, you might need to check your schema design and data access patterns to avoid the following issues:
- Transaction contention. For how to diagnose and resolve transaction contention, see Troubleshoot Lock Conflicts.
- Hot spots. For how to diagnose and resolve hot spots, see Troubleshoot Hotspot Issues.