Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 为什么只单独分区值也会非常慢呢
|
username: TiDBer_pIbgN3Km
I have a question. My TiDB uses school_id for partitioning. When I query with school_id, it is very fast. However, when I want to query all school_ids (select school_id from xxx), I find that it doesn’t use the index and needs to query each partition one by one, which is very slow. Is there any way to solve this problem?
Of course, a full table scan is slow. Adding DISTINCT should be much faster.
However, adding it is also slow. Shouldn’t there be an index tree to store the partition key? Why is a full table scan necessary?
TiDB defaults to clustered tables, meaning the data is ordered by key, and the primary key does not actually have an index.
There is no such default index tree. You can create an index for the partition key, which will make queries a bit faster. However, since TiDB only has partitioned indexes, it will still scan all partitioned indexes, making the speed somewhat slow compared to global indexes.
Without a partition key, it’s similar to broadcasting, so the speed will definitely be slower.
Currently, none of the versions of TiDB have global index functionality for partitioned tables. You can test it and see that even querying through the primary key will scan all partitions. This feature will be improved in TiDB 8.x.
Is school_id the primary key? Your SQL has no conditions, so it will definitely do a full table scan. A full table scan of the primary key is the same as a full index scan. If it’s a secondary index, you can try doing an ANALYZE TABLE to see if it can perform a full index scan. Also, upload the execution plan from EXPLAIN ANALYZE. TiDB uses local partitioning, so a secondary index will need to scan all partitions.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: