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 WHERE clause 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:

See also