support sub parititioning

[Dieken]

Feature Request

Describe the feature you'd like:

https://dev.mysql.com/doc/refman/5.7/en/partitioning-subpartitions.html

I have a table like (account_id, transaction_date, order_id, amount, price, confirm_info), I would like to partition it by transaction_date with monthly range partitioning, then subpartition it by account_id with hash partitioning.

The online service queries the table by account_id = xxxx, this is why I put account_id at the beginning.

But the offline batch job processes the table by transaction-date >= 'yyyy-mm-dd' and fill confirm_info, I don't want to add an extra index for transaction_date because that would slow down TiSpark batch write. If TiDB supports subpartitioning, then the batch job can do partition pruning, not have to do a full table scan.

Describe alternatives you've considered:

Without subpartitioning, I have to add a fine-grained index, this slow down batch write, it's a pity TiDB doesn't support coarse-grained index like BRIN in PostgreSQL: https://www.postgresql.org/docs/current/brin-intro.html

Teachability, Documentation, Adoption, Migration Strategy:

The MySQL manual is enough.

[mjonss]

/component tablepartition