database partitioning explanation

I just read about database partitioning and still have some confusion about it. So anybody please give me some explanations about what were changed in term of disk storage(like data file, index file, ...) behind a database partitioning, the pros and cons.开发者_StackOverflow中文版 I prefer MySQL with innodb but another db is welcome for comparison.

---

A horizontal partitioning allows you to divide a table up into different "partitions", though the table appears to be a single unified entity.

This is usually done when there is a well-known access pattern to the particular partitions. The most active data is kept on the fastest spindles, and the less accessed data ends up on other drives. Or, even if the entire table is active, it can be divided across different spindle sets to reduce disk latency.

This is closely related to the use of tablespaces. Assume you have two very active tables and you want to improve your disk performance. You can have two complete sets of spindles, and define a table space on each one. Then you assign each of the two highly active tables to a different table space, which means they are on different spindle sets, and they both go faster because they are not fighting over disk access.