Partitioning Consultation

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 分区划分请教咨询

| username: TiDBer_sX3j5LdU

[TiDB Usage Environment] Poc
[TiDB Version] 6.1.3
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact]

Issue Description: Different stocks have different numbers of records. The goal is to partition the data so that it can be evenly distributed in the database. Some codes have more than 20 records, while some stocks have only 1 record.

Tried using Range partitioning, but encountered an error. Seeking advice on which partition type would be more suitable for this situation. The SQL syntax is:

code.xlsx (42.0 KB)

2
| username: 我是吉米哥 | Original post link

MySQL requires the primary key to include the partition key.

3
| username: 有猫万事足 | Original post link

Different stocks have different record counts, and you want to partition the data so that it can be evenly distributed into the database.

The advantage of partitioning is that it makes deletion easier by partition, and it also helps in separating hot and cold data.

If you don’t have these two requirements, it’s better not to partition. TiDB inherently shards data by region and will automatically schedule it.

If you are looking for optimizations for high concurrency writes, refer to the best practices in the link above.

4
| username: zhanggame1 | Original post link

Evaluate the data volume; if it’s in the hundreds of millions, don’t use partitioning.

5
| username: TiDBer_QKDdYGfz | Original post link

Master, may I ask if querying data in a single region is faster than querying data distributed across multiple regions?

6
| username: 有猫万事足 | Original post link

Distributed across multiple regions is fast, but if reads are concentrated in one region, it becomes a read hotspot. This might need optimization.

7
| username: zhaokede | Original post link

Learned.

8
| username: WinterLiu | Original post link

This is a table design logic issue, right? You are converting a varchar type field to a date, and then comparing the date with another varchar type field. This will definitely cause an error.