Tidb: Support for table partition

[tiancaiamao]

There is a project for the table partition feature.

In fact, "partition by range" is nearly done, but it's regretful that the progress was traced in our inner project management tools.
Now, it's time for "partition by hash", and this issue is fired to discuss & trace the progress, in a collaborative way.

• [x] [Enhance partition prunning for range table partition](https://github.com/pingcap/tidb/issues/7516)
• [ ] Evaluate the performance of range partitioned table, compare it with normal table
• [x] [Enable range partition by default](https://github.com/pingcap/tidb/pull/8011)
• [x] [Fix "partition by hash" syntax](https://github.com/pingcap/parser/pull/33)
• [x] [Create hash partitioned table](https://github.com/pingcap/tidb/pull/8026)
• [x] [Drop and truncate hash partitioned table](https://github.com/pingcap/tidb/issues/8383)
• [x] [Add index on a hash partitioned table](https://github.com/pingcap/tidb/issues/8379)
• [x] [Drop index operation on hash partitioned table](https://github.com/pingcap/tidb/issues/8378)
• [x] [Implement insert and replace for hash partitioned table](https://github.com/pingcap/tidb/issues/8377)
• [x] [Implement select for hash partitioned table](https://github.com/pingcap/tidb/issues/8382)
• [x] [Implemnet delete for hash partitioned table](https://github.com/pingcap/tidb/issues/8380)
• [x] Implement update for hash partitioned table

Most of those are trivial. For example, implement insert needs to change partitionedTable.AddRecord, the locatePartiiotn() function should adapt to hash partition. Partition prunning probably can't work on "partition by hash", so implement select just change the DataSource to UnionAll.

[shenli]

@tiancaiamao Good job!
One suggestion: It is better to create a separate issue for each subtask and link them here.

[darren]

Is there any public design doc of partition feature of TiDB?

1. Is it compatible with MySQL's partition syntax? especially syntax for partition management, eg Add, Drop, Reorganize...

2. Any new feature that TiDB's partition supports while MySQL does not?

3. Does partition support namespace(Although I heard that namespace is deprecated in TiDB?)

[tiancaiamao]

Is there any public design doc of partition feature of TiDB?

There should be a design proposal, I'll push one later.

Is it compatible with MySQL's partition syntax? especially syntax for partition management, eg Add, Drop, Reorganize...

The partition syntax should be totally compatible with MySQL, while there are some limitations.
Reorganize partition involving data move is not supported under current non-block DDL implementation.

Any new feature that TiDB's partition supports while MySQL does not?

It depends. We plan to do it step by step, "partition by range" first, then "partition by hash"...
Currently no new feature that TiDB's partition supports while MySQL does not was introduced.

Does partition support namespace(Although I heard that namespace is deprecated in TiDB?)

No. There is no plan for namespace now.

@Darren

[laidahe]

i want to do this "Drop index operation on hash partitioned table"

[dreamquster]

I want to take the "Implement insert and replace for hash partitioned table"

[hhxcc]

I want to do this "Implement select for hash partitioned table"

[ddorian]

Is there a task to colocate partitions from different tables together so you can have single-partition transactions over multiple tables if they share partition-keys ?

[tiancaiamao]

Hi, here
I've create a issue https://github.com/pingcap/tidb/issues/8378, just have a try! @laidahe
Insert and replace https://github.com/pingcap/tidb/issues/8377 is similar to update, you can modify the UpdateRecord function for PartitionedTable @dreamquster
https://github.com/pingcap/tidb/issues/8382 maybe a bit more hard core, take a look first first, if you find it difficult for you, feel free to pick up some other tasks. @hhxcc
If you meet any problems, let's discuss in the related issue comments.

We focus primary on MySQL compatibility, not clever tricks in NoSQL discussed there https://github.com/pingcap/tidb/issues/7392 @ddorian

[crazycs520]

I want to Implement update for hash partitioned table.
BTW, Maybe there is nothing need todo for add index, drop index, drop table for hash table, because range partition's code is compatible with hash table.
@laidahe

[tiancaiamao]

We can still add more tests to verify it works @crazycs520

[hhxcc]

@tiancaiamao thank you, I would like to have a try. please guide me when I meet some problems~

[tiancaiamao]

Alter table truncate partition is done https://github.com/pingcap/tidb/pull/8624