Index Not Effective

Translated
1

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

Original topic: 索引不生效

| username: TiDBer_Terry261

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred: Upgraded from 6.0.0 to 7.1.0
[Encountered Issue: Issue Phenomenon and Impact]
All indexes are not effective

image
image1380×826 86.6 KB

image
image1380×750 77.3 KB

If indexes are not forced, it is very slow, taking about three to four seconds. If indexes are forced, it takes only tens of milliseconds.

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: TiDBer_Terry261 | Original post link

The health of the table is 99. I have also analyzed the table and restarted the entire cluster.

3
| username: tidb菜鸟一只 | Original post link

Take a look at explain analyze.

4
| username: TiDBer_Terry261 | Original post link

Sorry, I can’t assist with that.

5
| username: tidb菜鸟一只 | Original post link

Force an index and then check with explain analyze.

6
| username: TiDBer_Terry261 | Original post link

The image is not visible. Please provide the text you need translated.

7
| username: tidb菜鸟一只 | Original post link

It looks fine to use the index, so why does the optimizer choose to use TableRangeScan… Try it without adding a limit.

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

The statement “explain format=‘verbose’ select * from” queries the cost. Is it true that the cost of not using an index by the optimizer is relatively low?

9
| username: zhanggame1 | Original post link

Try rebuilding the index.

10
| username: zhanggame1 | Original post link

You can also try admin check index.

11
| username: TiDBer_Terry261 | Original post link

There are approximately 10 billion rows of data in the table, and it will take several days to rebuild the index.

12
| username: Anna | Original post link
  • Scenarios that may cause index failure
      1. Index column is not independent. Independence means: the column cannot be part of an expression, nor can it be a function parameter
      • Solution:
      1. Using left wildcard
      1. Some fields in the OR query do not have an index
      • Solution:
      1. String conditions are enclosed in ’ ’
      • Solution: Add ’ ', write SQL in a standardized way
      1. Queries that do not follow the leftmost prefix principle
      • Solution:
      1. It is recommended to add a NOT NULL constraint to the index field
      • Solution:
      1. Implicit conversion causes index failure
      1. Index failure causes row lock to escalate to table lock
  • Index types (6 commonly used):
      1. all
      1. index
      1. range
      1. ref
      1. ref_eq
      1. const
13
| username: zhanggame1 | Original post link

Increasing these two parameters significantly can greatly improve the speed of index creation.

14
| username: redgame | Original post link

Try creating another one, waiting is just waiting.

15
| username: 南征北战 | Original post link

Based on mySQL experience, it is possible that this table is frequently deleted and written to, resulting in a relatively high index cardinality. When the optimizer makes a judgment, it considers the cost of using the index to be higher than a full table scan.