Optimization Directions for Slow SQL

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 针对慢sql的优化方向

| username: zqk_zqk

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.0
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issues: Problem Phenomenon and Impact] The SQL query is slow, and the speed of inserting collected data into a certain table is very slow. What are the optimization directions for SQL? Also, regarding the priority of operator efficiency, I hope the experts can rank them. I now understand why various operators appear there, but I don’t know which operator is optimal for different data volumes, such as table joins. As a newbie to databases, I hope the experts can provide some guidance.
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]

2
| username: Kongdom | Original post link

You can refer to the SOP written by community experts:

Additionally, the official documentation also provides a detailed explanation:

However, it is best to provide an example and analyze it case by case.

3
| username: Kongdom | Original post link

The image is not available for translation. Please provide the text content directly.

4
| username: zqk_zqk | Original post link

This is an example, I want to know the optimization direction.

5
| username: Kongdom | Original post link
  1. I noticed you used a lot of “hit” methods. You can start by using explain analyze to see if there are any hits.
  2. For such large queries, I usually break them down and analyze each part for optimization. This involves using explain analyze to identify areas for improvement.
  3. Generally, such large queries can be optimized from the business logic level.
  4. I see your version is up to 6. You can use temporary tables to solve some performance issues.
  5. If you still have no clue, I recommend checking out the SOP written by experts and analyzing step by step.
6
| username: zqk_zqk | Original post link

Thank you, boss.

7
| username: zqk_zqk | Original post link

Are there any articles about SOP? I’d like to study them.

8
| username: 胡杨树旁 | Original post link

Do you have any good optimization suggestions for this kind of group by and order by?

9
| username: Kongdom | Original post link

It’s the ones shared above

10
| username: Kongdom | Original post link

:sunglasses: Go straight to TiFlash, simple and straightforward.

11
| username: 胡杨树旁 | Original post link

Such a comprehensive article, thank you.

12
| username: zqk_zqk | Original post link

Currently, there is no TiFlash instance deployed.

13
| username: zqk_zqk | Original post link

:handshake: :handshake: :handshake:

14
| username: Hacker_xUwtuKxa | Original post link

It is recommended to first explain and look at the execution plan. If the statistics are inaccurate, gather the statistics first. By looking at the execution plan, you can see where the bottleneck is, and then analyze it accordingly.

Looking at your SQL, it is actually not complicated. It just has many case when statements and calculated fields in the select clause. Additionally, there are group and order clauses. Check the execution plan to evaluate the indexes and data volume involved. Establishing appropriate indexes should be effective.

15
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.