Databases Reference
In-Depth Information
SELECT
queries
When you query a partitioned table, the partitioning layer opens and locks all of
the underlying partitions, the query optimizer determines whether any of the par-
titions can be ignored (pruned), and then the partitioning layer forwards the han-
dler API calls to the storage engine that manages the partitions.
INSERT
queries
When you insert a row, the partitioning layer opens and locks all partitions, de-
termines which partition should receive the row, and forwards the row to that
partition.
DELETE
queries
When you delete a row, the partitioning layer opens and locks all partitions, de-
termines which partition contains the row, and forwards the deletion request to
that partition.
UPDATE
queries
When you modify a row, the partitioning layer (you guessed it) opens and locks
all partitions, determines which partition contains the row, fetches the row, modi-
fies the row and determines which partition should contain the new row, forwards
the row with an insertion request to the destination partition, and forwards the
deletion request to the source partition.
Some of these operations support pruning. For example, when you delete a row, the
server first has to locate it. The server can prune partitions that can't contain the row
if you specify a
WHERE
clause that matches the partitioning expression. The same applies
to
UPDATE
queries.
INSERT
queries are naturally self-pruned; the server looks at the values
to be inserted and finds one and only one destination partition.
Although the partitioning layer opens and locks all partitions, this doesn't mean that
the partitions remain locked. A storage engine such as InnoDB, which handles its own
locking at the row level, will instruct the partitioning layer to unlock the partitions.
This lock-and-unlock cycle is similar to how queries against ordinary InnoDB tables
are executed.
We'll show some examples a bit later that illustrate the cost and consequences of
opening and locking every partition when there's any access to the table.
Types of Partitioning
MySQL supports several types of partitioning. The most common type we've seen used
is range partitioning, in which each partition is defined to accept a specific range of
values for some column or columns, or a function over those columns. For example,
here's a simple way to place each year's worth of sales into a separate partition:
CREATE TABLE sales (
order_date DATETIME NOT NULL,
-- Other columns omitted
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
