Extend 'A Practical Guide For SQL Tuning' with table partitioning

[mjonss]

Change Request

Please answer the following questions before submitting your issue. Thanks!

1. Describe what you find is inappropriate or missing in the existing docs.
   As a follow up to A Practical Guide For SQL Tuning #19108, also write a part where table partitioning is explained and what its best practice can be used for SQL tuning

2. Describe your suggestion or addition.
   It should mention:

• Benefits of table partitioning
  • partition pruning can skip all partitions not matching the predicates of the query, also for TiFlash
  • DROP PARTITION very efficient way of deleting a whole partition, similar to DROP TABLE, without having to go through the transaction layer.
  • EXCHANGE PARTITION
• Drawbacks of table partitioning
  • non-global secondary indexes will do index lookups for each matching partition
• Global index
  • Remove the limitation that 'all unique indexes needs to include all partitioning columns'
  • A single table level index so not one lookup per partition
  • Needs index reorganization when dropping / reorganizing partitions, so not meta-data only DROP PARTITION.

3. Provide some reference materials (such as documents and websites) if you could.

[dbsid]

We're prepare a dedicated best practice document for partition table