Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tikv cpu 使用率高
Experts, I have a question. For a table with over a hundred million rows, which is a non-clustered table, there is a frequently executed statement. The execution plan for this statement is a full table scan, and the where clause field cannot be indexed because its selectivity is too low. This is causing high TiKV CPU usage and read hotspots. Besides regularly cleaning up the data in this table and reducing the execution frequency of this statement, are there any other good solutions? Assume the statement cannot be rewritten.
For tables with hundreds of millions of rows and poor selectivity, how many rows are returned per query? If the number of returned rows is small, creating an index might be worth a try. You can give it a shot.
First, post the SQL table structure, the distinct values of the where column, and the execution plan of explain analyze. Let’s take a look.
What is this query for? If it’s an aggregate function, you might consider TiFlash.
Low selectivity does not mean that you cannot add an index. This is a misconception. It still depends on how many rows your selection criteria can filter out. For example, take a gender field: if you have 100 billion records and only one is female while the rest are male, you can still add an index. If you want to query for the female gender, you can add an index for that as well. As mentioned earlier, please paste the SQL table structure.
I think you can try adding an index, after all, TiDB supports histograms.
Thank you, expert. I think we can add an index. TiDB supports histograms.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: Raymond
|
username: 
|
username: 
|
username: 
|
username: 
|
username: