Databases Reference
In-Depth Information
CHAPTER 7
Advanced MySQL Features
MySQL 5.0 and 5.1 introduced many features, such as partitioning and triggers, which
are familiar to users with a background in other database servers. The addition of these
features attracted many new users to MySQL. However, their performance implications
did not really become clear until people began to use them widely. In this chapter we
explain what we've learned from seeing these features in the real world, beyond what
the manuals and reference material have taught us.
Partitioned Tables
A partitioned table is a single logical table that's composed of multiple physical sub-
tables. The partitioning code is really just a wrapper around a set of Handler objects
that represent the underlying partitions, and it forwards requests to the storage engine
through the Handler objects. Partitioning is a kind of black box that hides the under-
lying partitions from you at the SQL layer, although you can see them quite easily by
looking at the filesystem, where you'll see the component tables with a hash-delimited
naming convention.
The way MySQL implements partitioning—as a wrapper over hidden tables—means
that indexes are defined per-partition, rather than being created over the entire table.
This is different from Oracle, for example, where indexes and tables can be partitioned
in more flexible and complex ways.
MySQL decides which partition holds each row of data based on the
PARTITION BY
clause that you define for the table. The query optimizer can prune partitions when
you execute queries, so the queries don't examine all partitions—just the ones that
hold the data you are looking for.
The primary purpose of partitioning is to act as a coarse form of indexing and data
clustering over the table. This can help to eliminate large parts of the table from being
accessed, and to store related rows close together.
