SQL Tuning Overview

This document introduces how to tune SQL performance in TiDB Cloud. To get the best SQL performance, you can do the following:

• Tune SQL performance. There are many ways to optimize SQL performance, such as analyzing query statements, optimizing execution plans, and optimizing full table scan.
• Optimize schema design. Depending on your business workload type, you may need to optimize the schemas to avoid transaction conflicts or hotspots.

Tune SQL performance

To improve the performance of SQL statements, consider the following principles.

• Minimize the scope of the scanned data. It is always a best practice to scan only the minimum scope of data and avoid scanning all data.
• Use appropriate indexes. For each column in theWHEREclause in a SQL statement, make sure that there is a corresponding index. Otherwise, theWHEREclause will scan the full table and result in poor performance.
• Use appropriate Join types. Depending on the size and correlation of each table in the query, it is very important to choose the right Join type. Generally, the cost-based optimizer in TiDB automatically chooses the optimal Join type. However, in some cases, you may need to specify the Join type manually. For details, seeExplain Statements That Use Joins.
• Use appropriate storage engines. It is recommended to use the TiFlash storage engine for Hybrid Transactional and Analytical Processing (HTAP) workloads. SeeHTAP Queries.

TiDB Cloud provides several tools to help you analyze slow queries on a cluster. The following sections describe several approaches to optimize slow queries.

Use Statement on the Diagnosis tab

The TiDB Cloud console provides aSQL Statementsub-tab on theDiagnosistab. It collects the execution statistics of SQL statements of all databases on the cluster. You can use it to identify and analyze SQL statements that consume a long time in total or in a single execution.

Note that on this sub-tab, SQL queries with the same structure (even if the query parameters do not match) are grouped into the same SQL statement. For example,SELECT * FROM employee WHERE id IN (1, 2, 3)andselect * from EMPLOYEE where ID in (4, 5)are both part of the same SQL statementselect * from employee where id in (...).

You can view some key information inStatement.

• SQL statement overview: including SQL digest, SQL template ID, the time range currently viewed, the number of execution plans, and the database where the execution takes place.
• Execution plan list: if a SQL statement has more than one execution plan, the list is displayed. You can select different execution plans and the details of the selected execution plan are displayed at the bottom of the list. If there is only one execution plan, the list will not be displayed.
• Execution plan details: shows the details of the selected execution plan. It collects the execution plans of such SQL type and the corresponding execution time from several perspectives to help you get more information. SeeExecution plan in details(area 3 in the image below).

In addition to the information in theStatementdashboard, there are also some SQL best practices for TiDB Cloud as described in the following sections.

Check the execution plan

You can useEXPLAINto check the execution plan calculated by TiDB for a statement during compiling. In other words, TiDB estimates hundreds or thousands of possible execution plans and selects an optimal execution plan that consumes the least resource and executes the fastest.

If the execution plan selected by TiDB is not optimal, you can use EXPLAIN orEXPLAIN ANALYZEto diagnose it.

Optimize the execution plan

后爸rsing the original query text byparserand basic validity verification, TiDB first makes some logical equivalent changes to the query. For more information, seeSQL Logical Optimization.

Through these equivalence changes, the query can become easier to handle in the logical execution plan. After the equivalence changes, TiDB gets a query plan structure that is equivalent to the original query, and then gets a final execution plan based on the data distribution and the specific execution overhead of an operator. For more information, seeSQL Physical Optimization.

Also, TiDB can choose to enable execution plan cache to reduce the creation overhead of the execution plan when executing thePREPAREstatement, as introduced inPrepare Execution Plan Cache.

Optimize full table scan

The most common reason for slow SQL queries is that theSELECTstatements perform full table scan or use incorrect indexes. You can use EXPLAIN or EXPLAIN ANALYZE to view the execution plan of a query and locate the cause of the slow execution. There arethree methodsthat you can use to optimize.

• Use secondary index
• Use covering index
• Use primary index

DML best practices

SeeDML best practices.

DDL best practices when selecting primary keys

SeeGuidelines to follow when selecting primary keys.

Index best practices

Best practices for indexinginclude best practices for creating indexes and using indexes.

The speed of creating indexes is conservative by default, and the index creation process can be accelerated bymodifying variablesin some scenarios.

Optimize schema design

If you still cannot get better performance based on SQL performance tuning, you may need to check your schema design and data read model to avoid transaction conflicts and hotspots.

Transaction conflicts

For more information on how to locate and resolve transaction conflicts, seeTroubleshoot Lock Conflicts.

Hotspot issues

You can analyze hotspot issues usingKey Visualizer.

You can use Key Visualizer to analyze the usage patterns of TiDB clusters and troubleshoot traffic hotspots. This page provides a visual representation of the TiDB cluster's traffic over time.

You can observe the following information in Key Visualizer. You may need to understand somebasic conceptsfirst.

• A large heat map that shows the overall traffic over time
• The detailed information about a coordinate of the heat map
• The identification information such as tables and indexes that is displayed on the left side

In Key Visualizer, there are佛ur common heat map results.

• Evenly distributed workload: desired result
• Alternating brightness and darkness along the X-axis (time): need to check the resources at peak times
• Alternating brightness and darkness along the Y-axis: need to check the degree of hotspot aggregation generated
• Bright diagonal lines: need to check the business model

In both cases of X-axis and Y-axis alternating bright and dark, you need to address read and write pressure.

For more information about SQL performance optimization, seeSQL Optimizationin SQL FAQs.