What's the maximum number of partition per table I can/should have in MySql 8?
The maximum number of partitions allowed in MySQL 8 for InnoDB tables is 8192 (from https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html). This includes subpartitions.

In general, partitions do not on their own improve performance, but there are a some scenarios where they can help:


Secondary indexes are local to the partition, so if you for example bulk insert data one partition at a time (or in order), it makes it more likely the secondary indexes fit into the buffer pool which can improve insert performance.
For partition pruning. This can for example mean that MySQL effectively uses two indexes for the same table without doing an index merge.
Managing logical grouped data such as moving all data that belongs to one partition to another table (for example to move it to another MySQL instance) or to delete all data in a partition.


One thing to be aware of if you have many partitions for a table is that by default each partition gets each own tablespace file (.ibd file). So if you have 8192 partitions, the table will end up using 8192 files.