Mysql partitioning: Difference between revisions
From wikinotes
No edit summary |
No edit summary |
||
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
Partitioning is a '''write-optimization'''. | |||
A partitioned table's contents are stored in different locations (ex. different servers).<br> | A partitioned table's contents are stored in different locations (ex. different servers).<br> | ||
A '''partitioning key''' is passed to a function to determine which partition a row or column should belong to.<br> | A '''partitioning key''' is passed to a function to determine which partition a row or column should belong to.<br> | ||
Partitions must share the same storage-engine | Partitions must share the same storage-engine | ||
Frequently a globally relevant identifier is used as the partitioning key (ex. a user).<br> | |||
then all of the rest of their data (ex. comments, projects, posts, ..) are stored on the same database instance. | |||
= Partition Methods = | = Partition Methods = | ||
Line 16: | Line 21: | ||
</blockquote><!-- Partitioning Strategies --> | </blockquote><!-- Partitioning Strategies --> | ||
= Partition Introspection = | |||
<blockquote> | |||
<syntaxhighlight lang="mysql"> | |||
EXPLAIN PARTITIONS SELECT * FROM users; # list partitions, partition-key for query | |||
</syntaxhighlight> | |||
</blockquote><!-- Partition Introspection --> |
Latest revision as of 18:07, 24 September 2022
Partitioning is a write-optimization.
A partitioned table's contents are stored in different locations (ex. different servers).
A partitioning key is passed to a function to determine which partition a row or column should belong to.
Partitions must share the same storage-engine
Frequently a globally relevant identifier is used as the partitioning key (ex. a user).
then all of the rest of their data (ex. comments, projects, posts, ..) are stored on the same database instance.
Partition Methods
horizontal: rows are partitioned vertical: cols are partitioned
Partitioning Strategies
Partition Introspection
EXPLAIN PARTITIONS SELECT * FROM users; # list partitions, partition-key for query