The data and indexes of partitioned or non-partitioned tables are divided into Regions and distributed across different nodes. All Regions on the same node exist within a single RocksDB instance, and the metadata of the Regions is stored on PD. It feels like there is no difference between directly querying using an index and using partition pruning before querying with an index. Both methods first locate the Region on PD and then perform a KV query within the Region. The process of PD locating the Region seems to be the same, just that the key with a partition includes the partition ID. If this is the case, why does partition pruning speed up retrieval? It feels like both methods involve a KV lookup on PD to find the Region, and then another KV lookup within the Region to find the data.
If this understanding is correct, it implies that TiDB completely abstracts away the concept of tables, with all table data and indexes mixed together within RocksDB (although different tables are in different Regions, the Regions still exist within the same RocksDB instance). Does this mean that if one table is very large, it will cause retrievals of other smaller tables to be slow as well, since the data and indexes all exist within the same RocksDB instance?
Without partition pruning, when executing a query, TiDB may need to scan all partitions of the entire table, even if most of the partition data does not meet the query conditions. This can lead to a large amount of invalid data being loaded into memory, increasing I/O overhead and the use of computing resources.
The role of partition pruning is to determine which partitions’ data may meet the conditions based on the query conditions during query execution, and then only scan those partitions, ignoring the partitions that do not meet the conditions. This can greatly reduce the amount of data that needs to be loaded, decrease the number of I/O operations, and improve query execution efficiency.
In my case, I am not considering the situation without indexes. Without indexes, it is indeed possible to scan less data, but online queries may not rely on full table scans to locate data. With indexes, is there not much difference? In traditional single-machine databases, partition keys can directly locate a specific partition, and the index of a single partition is also very small, thereby improving retrieval speed. However, in TiDB, the indexes of ordinary tables or partitioned tables will be split into multiple Regions. Therefore, index-based searches actually involve first going to PD to locate the Region based on the key, and then locating the primary key in the corresponding Region based on the key. The whole process feels the same whether there is partitioning or not; both involve two KV lookups. The only difference with partitioning is that the key contains the partition ID, but it seems that this should not improve performance during KV lookups.
The greatest advantage of partitioned tables in TiDB is that data deletion can be achieved by deleting partitions, which is very fast. However, the effect of partitioned tables on queries and other operations is generally mediocre.
I currently feel that the main significance of partitioning is to manage partitioned data separately and to disperse initial write hotspots through partitioning. However, the documentation does mention that partition pruning can speed up search times, which leaves me confused. I think the key point is whether different partitions are physically isolated. If they are multiple files at the physical level, a single partition would definitely speed up search times. But from what I see, the entire architecture treats Region as a logical concept, and the data is stored in a single RocksDB instance, which seems like it wouldn’t improve search speed at all. It might even decrease search speed when the query conditions do not include the partition key.
However, I have indeed seen some articles that show examples where search performance is better when the partition key is included. I suspect I might be misunderstanding something. Is there a step where the presence of a partition key would actually speed things up?
The secondary index of a partitioned table in TiDB is built along with the partition. If partition pruning can be used, it can also prune to the corresponding partition index, so the speed should not be slow. The problem arises when the condition does not involve partitioning and partition pruning cannot be used. Since TiDB’s secondary index for partitioned tables does not support global indexes, it has to scan all secondary index partitions each time, making this less efficient than the global index in traditional single-node databases.
If you cannot determine which partitions to scan, for example, by using a non-primary key index, is the efficiency of a partitioned table inferior to that of a regular table? I remember that in earlier versions, it was not possible to use indexes within each partition.
Thank you, I very much agree with your viewpoint, but I still have a question that needs to be confirmed. If the condition includes partitioning and uses a secondary index, can the partition really play a role in pruning? This is because the data of different tables and different partitions are mixed within the same KV, including the metadata of various Regions. In other words, when using an index, it does not first locate a partition and then use a smaller index within that partition. Instead, it still searches the global KV, just that the key includes the partition key. Overall, partitioning is just a logical concept, and the data and indexes of different partitions are still mixed in a large KV. From this perspective, I think hitting a partition does not help with using a secondary index at all, or maybe I have misunderstood. Perhaps partitioning does affect the physical distribution of data, such as separate KV instances (equivalent to smaller indexes) or trying to distribute across different nodes. What is your view on this?
Partitioning is not a logical concept.
A regular table has a unique ID, and the key inside the KV store is the table ID plus the data primary key.
The key of a partitioned table includes the partition key value.
I understand that the keys of partitioned tables include partition keys, but these keys all exist in a single large KV database. There isn’t a separate instance for each partition. Even with partition keys, it doesn’t seem like it would improve the speed of KV lookups.
My understanding is that TiDB’s KV is stored in order by key. Without partitioning, the key order is arranged by the table’s primary key. With partitioning, it is arranged by partition plus primary key, which means the keys of a partition can be arranged together.
Okay, I now understand that when the secondary index does not include the partition key but the condition also includes the partition condition, it indeed has some effect. However, if the secondary index itself already includes the partition key, there should be basically no difference.
In the early versions, I’m not sure, but we are using version V6.5.4, and indexes can be used in partitions. However, one issue with this version is that there is a problem with collecting statistics for partitioned tables. If only TiDB’s automatic collection is used, the partition statistics collection may be incomplete or outdated. I’m not sure if this issue has been fixed in later versions.
|
username: hayson
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: