Issue of Specified Index Becoming Invalid After Enabling TiFlash

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

Original topic: 开启TiFlash后 指定索引失效问题

| username: wzf0072

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.2
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]


After enabling TiFlash for the table oms_way_address, even when specifying the index using USE INDEX or force index, it still uses TiFlash. This remains the case even after running analyze table.
This table has 10 million records, and querying 20 records by in way_billcode takes 2 seconds. After disabling TiFlash for the table oms_way_address, the execution only takes 0.1 seconds.
[Resource Configuration]

[Attachments: Screenshots/Logs/Monitoring]

| username: Kongdom | Original post link

What does this configuration item in the TiDB configuration file configure?

| username: wzf0072 | Original post link

tidb_isolation_read_engines = tikv,tiflash,tidb
image

| username: cy6301567 | Original post link

Inaccurate statistics: TiDB uses statistics to optimize query plans. If the statistics are inaccurate, TiFlash may choose inappropriate indexes, causing the specified index to become invalid. The solution is to regularly collect and update statistics, which can be done using the “ANALYZE TABLE” statement to manually update the statistics.
TiFlash metadata synchronization issue: If the metadata of TiFlash is inconsistent with that of TiDB, it may also cause the specified index to become invalid. Ensure that TiFlash’s metadata is synchronized with TiDB. You can try restarting the TiFlash instance or use commands like “flush privileges” to refresh permissions and metadata information.

| username: Kongdom | Original post link

What about specifying the query engine?
/*+ read_from_storage(tikv[o]) */

| username: 大飞哥online | Original post link

This SQL should use KV, as there are no statistical analyses or functions involved, but it went to Flash. Run analyze and then check the execution plan again.

| username: h5n1 | Original post link

There is an issue with the optimizer evaluation. Try using “drop stats XXX” and then update the statistics with the analyze method. This might affect other SQL queries during this period.

| username: wzf0072 | Original post link

The specified query engine did not take effect either.
(The previous table is normal now, but I switched to another table and the same situation occurred.)

| username: wzf0072 | Original post link

After running ANALYZE TABLE and flush privileges, the issue still persists.

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

Does specifying the index in the hint /*+ USE_INDEX(t1, idx1) */ not work either?

| username: wzf0072 | Original post link

The problem persists.

| username: wzf0072 | Original post link

Yes

| username: Kongdom | Original post link

:thinking: How about restarting the cluster? According to the description, some can and some cannot.

| username: wzf0072 | Original post link

Restarted the TiFLASH node, the issue persists.

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

Try deleting and re-adding the tiflash replica of this table.

| username: 昵称想不起来了 | Original post link

That’s strange. Is it impossible to confirm which step caused the table to return to normal? Try the steps you executed before to see which one is effective?

| username: wzf0072 | Original post link

After deleting the TiFlash replica, it can use the index without any prompt. After enabling TiFlash again, it uses TiFlash again.

| username: wzf0072 | Original post link

It’s quite mysterious. This morning, both tables were using TiFlash. Yesterday, for the table that returned to normal, I performed operations to delete and re-add the TiFlash replica and manually collected statistics (after executing these operations on both tables, one used TiKV and the other used TiFlash).

| username: Kongdom | Original post link

:thinking: For the same operation, some work and some don’t. Are the query statements and table structures the same? It’s really mysterious.

| username: wzf0072 | Original post link

The table structures are generally consistent, and the query SQLs are similar single-table queries with the same WHERE conditions and index fields.