TiDB Index Optimizer Anomaly

translator_bot · June 23, 2024, 3:43am

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

Original topic: TiDB 索引优化器异常

| username: Leonadle

[TiDB Cluster Information]

Version: v5.1.1
TiDB Instances: 2

[Encountered Issues]

Currently, there are two TiDB instances. Yesterday, a slow query alert appeared, and it was found that one of the TiDB instances executed a certain SQL query very slowly (over 1000s), while the other TiDB instance executed the same SQL query normally.
Analyzing the slow query logs, this SQL query used different indexes on the two TiDB instances, resulting in a significant difference in query speed:

TiDB (1.20) used the wrong index

image986×565 49.6 KB
TiDB (1.21) used the correct index

image1038×574 49.3 KB

[Questions]

The configurations and parameters of the two TiDB instances are identical. Why is there a difference in index selection?

translator_bot · June 23, 2024, 3:43am

| username: h5n1 | Original post link

Each TiDB instance independently parses SQL and generates execution plans. Check the health of the table to see if statistics need to be recollected. For unstable execution plans, you can bind the execution plan.