Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 表的统计失效会对sql执行变得很慢各位大佬有好办法吗
To improve efficiency, please provide the following information. Clear problem descriptions can be resolved faster:
【TiDB Usage Environment】Production
【TiDB Version】6.1
【Encountered Problem】
SQL becomes slow once 80% of the table’s statistics become invalid.
I am currently using a timer to solve this, but there is still a 1-hour invalidation period.
Do any experts have a good solution?
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
【Attachments】
Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.
The increased average time in the middle is the time when a large table’s statistics become invalid.
Is the data changing too frequently? Try binding the execution plan?
Refer to: 专栏 - 使用SPM固定执行计划 | TiDB 社区
Binding execution plans.
The default value for table-level changes % triggers TiDB to automatically analyze the table, which will determine the collection of statistics. If the execution plan selection is affected, it is best to fix the execution plan using global binding.
In this situation, it is highly likely that the table is very large, and the SQL query includes a time filter condition with the time range being the most recent period. Therefore, the statistics are always outdated, and the issue can only be resolved through hints or binding methods.
How do I know what hint is being used at this stage?
Please post the SQL.
You can first check the execution plan when it is slow, manually collect statistics, then check the execution plan when it is fast, and compare the differences between the two. Use hints for targeted binding correction.
Bind the execution plan or set automatic analyze.
Check slow SQL and perform hint optimization.
Yes, bind the execution plan.
This topic will be automatically closed 60 days after the last reply. No new replies are allowed.
|
username: tidb狂热爱好者
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: