This means primary keys and unique keys work as expected over the whole table,
and the MySQL query optimizer can optimize queries against partitioned tables more
intelligently than with merge tables.
Here are some important benefits of partitioned tables:
• You can specify that certain rows are stored together in one partition, which can
reduce the amount of data the server has to examine and make queries faster.
For example, if you partition by date range and then query on a date range that
accesses only one partition, the server will read only that partition.
• Partitioned data is easier to maintain than non-partitioned data, and it’s easier to
discard old data by dropping an entire partition.
• Partitioned data can be distributed physically, enabling the server to use multi-
ple hard drives more efficiently.
The following
will make MySQL distribute the rows by the modulus of the primary key. This is a
fine way to spread data uniformly among the partitions:
mysql> ALTER TABLE mydb.very_big_table
-> PARTITION BY KEY(<primary key columns>) (
-> PARTITION p0 DATA DIRECTORY='/data/mydb/big_table_p0/',
-> PARTITION p1 DATA DIRECTORY='/data/mydb/big_table_p1/');
You can achieve the same goal in a different way with a RAID controller. This can
sometimes be better: because it is implemented in hardware, it hides the details of
how it works, so it doesn’t introduce more complexity into your schema and que-
ries. It also may provide better, more uniform performance if your only goal is to dis-
tribute your data physically.
artitioned tables are not a “silver bullet” solution. Here are some of the limitations
in the current implementation:
• At present, all partitions have to use the same storage engine. For example, you
cannot compress only some partitions the way you can compress some underly-
ing tables in a merge table.
• Every unique index on a partitioned table must contain the columns referred to
by the partition function. As a result, many instructional examples avoid using a
primary key. Although it’s common for data warehouses to contain tables with-
out primary keys or unique indexes, this is less common in OLTP systems. Con-
sequently, your choices of how to partition your data might be more limited
than you’d think at first.
• Although MySQL may be able to avoid accessing all of the partitions in a parti-
tioned table during a query, it still locks all the partitions.
• There are quite a few limitations on the functions and expressions you can use in
a partitioning function.
• Some storage engines don’t work with partitioning.
• Foreign keys don’t work.
• You can’t use LOAD INDEX INTO CACHE.
There are many other limitations as well (at least at the time of this writing, when
MySQL 5.1 is not yet generally available). Partitioned tables actually provide less
flexibility than merge tables in some ways. For example, if you want to add an index
to a partitioned table, you can’t do it a bit at a time; the ALTER will lock and rebuild
the entire table. Merge tables give you more possibilities, such as adding the index
one underlying table at a time. Similarly, you can’t back up or restore just one parti-
tion at a time, which you can do with the underlying tables in a merge table.
Whether a table will benefit from partitioning depends on many factors, and you’ll
need to benchmark your own application to determine whether it is a good solution
for you.