Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb查询索引问题
[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Executing SQL statement:
This is performing a full table scan,
even though the query conditions have indexes.
The task description mentions that this full table scan uses TiFlash, a columnar scan. It is possible that the CBO (Cost-Based Optimizer) determined that a columnar scan would be faster than a row scan on TiKV. Does this meet your expectations?
You can use hints to force the CBO to use a row scan on TiKV. You can give it a try.
It seems like you are using TiFlash. If you want to use TiKV’s index, you can specify a hint or collect the statistics for this table. It doesn’t seem very accurate…
actrow only has 3 rows, it seems better to use the index.
You can force it to use TiKV by adding a hint /*+ READ_FROM_STORAGE(TIKV[rr]) */
Additionally, inaccurate statistics might cause the CBO to choose the wrong execution plan, you can try running analyze table and then test again.
In an online environment, is it better to configure TiFlash or TiKV?
There is no standard; it depends on which consumes fewer resources and returns more accurate information. This way, the cluster can be healthier.
Fewer slow queries.
Currently, the online TiDB experiences very slow queries during stress testing, and even after restarting TiDB, the queries do not recover. Is this related to using TiFlash?
How is this generally configured, which ones go to TiKV and which ones go to TiFlash?
asktug has a complete guide for query optimization that you can refer to for troubleshooting:
After running ANALYZE table, when running it again, a single table scan, why did it run on TiFlash?
Set TiFlash as the default now.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: Z六月星星
|
username: 
|
username: 
|
username: 
|
username: 
|
username: