The SQL execution plan used the wrong index

Translated
1

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

Original topic: sql执行计划走了错误的索引

| username: magongyong

To improve efficiency, please provide the following information. Clear problem descriptions can lead to faster resolutions:
[TiDB Usage Environment]
tidb 5.4.0, centos 7.5

[Overview] Scenario + Problem Overview
Recently upgraded TiDB to 5.4.0 and found that it always uses the wrong index. Each time after manual parsing, it gets better, but after a while, it uses the wrong index again.

Dashboard execution plan, using the wrong index

Image
Image1380×750 58.6 KB

Execution time 2.4 seconds

Image
Image951×401 52.1 KB

Table health

Image
Image998×175 6.52 KB

Table index structure

Image
Image977×412 41.3 KB

After manual parsing, it completes instantly

Image
Image735×671 32 KB

After manual parsing, the execution plan uses the correct index

Image
Image916×258 11.4 KB

The statistics version is 1. We just upgraded from 4.0.13 to 5.4.0

Image
Image822×331 9.09 KB

[Background] Operations performed

[Phenomenon] Business and database phenomena

[Problem] Current issue encountered
Is there any way to solve this problem? Thank you.

[Business Impact]

[TiDB Version]

[Application Software and Version]

[Attachments] Relevant logs and configuration information

  • TiUP Cluster Display information
  • TiUP Cluster Edit config information

Monitoring (https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana monitoring
  • TiDB Grafana monitoring
  • TiKV Grafana monitoring
  • PD Grafana monitoring
  • Corresponding module logs (including logs 1 hour before and after the issue)

If the question is related to performance optimization or troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output results for upload.

2
| username: caiyfc | Original post link

I think there are two methods:

  1. Bind the execution plan:
    执行计划管理 (SPM) | PingCAP 文档中心
  2. Use hints in the SQL to force the use of a specified index:
    Optimizer Hints | PingCAP Docs
3
| username: ddhe9527 | Original post link

The reason the optimizer tends to choose the time field index is probably because the time range in your WHERE condition is outside the range recorded by the statistics. The statistics are always outdated. For example, if the statistics record data up to yesterday, and your SQL specifies a time range for today, the optimizer will assume that there is no data or very little data for the specified time range based on the statistics. Therefore, the time range index will be considered very efficient for filtering.

  1. It is recommended to delete the index on the phone number field and create a composite index on (phone number, time) to reduce the optimizer’s concerns about index selection.
  2. Try using the v2 version of the statistics to see if it improves the situation.
  3. Bind the execution plan or use force index.
4
| username: xiaohetao | Original post link
  1. You can manually bind the execution plan first, but this can only be done for known SQL;
  2. Forcing an index with a hint: It’s inconvenient to modify SQL generated by the program, and even if it can be modified, it requires reconfiguring the business, which carries certain risks.

Personally, I think you can analyze the SQL to see if the index is optimal, especially for composite indexes.

5
| username: magongyong | Original post link

It has been changed to version v2. Let’s observe for a while. If it still doesn’t work, we will modify the index. Thanks!

6
| username: caiyfc | Original post link

It’s better not to set analyze version to 2, as it may trigger an OOM bug.

7
| username: ddhe9527 | Original post link

Is the OOM issue still not fixed in version 5.4?

8
| username: caiyfc | Original post link

:joy: I remembered incorrectly. I just asked, and this bug was fixed after version 5.3.

9
| username: magongyong | Original post link

It happened again this morning. Changing to version v2 didn’t work either. I’m planning to use a composite index.

10
| username: ddhe9527 | Original post link

A composite index should be able to solve the issue. It is recommended to create the composite index first and then delete the single-column index to avoid having no index available in between.

11
| username: magongyong | Original post link

After adding the composite index, this issue hasn’t reappeared in the past few days. Thanks a lot, expert!

12
| username: xuexiaogang | Original post link

Why is your phone a string of letters? Shouldn’t it be Arabic numerals?

13
| username: ddhe9527 | Original post link

This depends on how the application is designed. Additionally, if considering international numbers, a plus sign should be included at the beginning.

14
| username: xiaohetao | Original post link

Some might have been desensitized for security reasons.

15
| username: magongyong | Original post link

Desensitized and encrypted.

16
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.