A table that is partitioned by range is partitioned in such a
way that each partition contains rows for which the partitioning
expression value lies within a given range. Ranges should be
contiguous but not overlapping, and are defined using the
VALUES LESS THAN
operator. For the next few
examples, suppose that you are creating a table such as the
following to hold personnel records for a chain of 20 video
stores, numbered 1 through 20:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL );
This table can be partitioned by range in a number of ways,
depending on your needs. One way would be to use the
store_id
column. For instance, you might
decide to partition the table 4 ways by adding a
PARTITION BY RANGE
clause as shown here:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) );
In this partitioning scheme, all rows corresponding to employees
working at stores 1 through 5 are stored in partition
p0
, to those employed at stores 6 through 10
are stored in partition p1
, and so on. Note
that each partition is defined in order, from lowest to highest.
This is a requirement of the PARTITION BY
RANGE
syntax; you can think of it as being analogous
to a series of if ... elseif ...
statements
in C or Java in this regard.
It is easy to determine that a new row containing the data
(72, 'Michael', 'Widenius', '1998-06-25', NULL,
13)
is inserted into partition p2
,
but what happens when your chain adds a
21st store? Under this scheme, there
is no rule that covers a row whose store_id
is greater than 20, so an error results because the server does
not know where to place it. You can keep this from occurring by
using a “catchall” VALUES LESS
THAN
clause in the CREATE
TABLE
statement that provides for all values greater
than highest value explicitly named:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Another way to avoid an error when no matching value is found
is to use the IGNORE
keyword as part of the
INSERT
statement. For an
example, see Section 18.2.2, “LIST
Partitioning”. Also see
Section 12.2.5, “INSERT
Syntax”, for general information about
IGNORE
.
MAXVALUE
represents an integer value that is
always greater than the largest possible integer value (in
mathematical language, it serves as a least upper
bound). Now, any rows whose
store_id
column value is greater than or
equal to 16 (the highest value defined) are stored in partition
p3
. At some point in the future — when
the number of stores has increased to 25, 30, or more —
you can use an ALTER TABLE
statement to add new partitions for stores 21-25, 26-30, and so
on (see Section 18.3, “Partition Management”, for details
of how to do this).
In much the same fashion, you could partition the table based on
employee job codes — that is, based on ranges of
job_code
column values. For example —
assuming that two-digit job codes are used for regular
(in-store) workers, three-digit codes are used for office and
support personnel, and four-digit codes are used for management
positions — you could create the partitioned table using
the following:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (job_code) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (10000) );
In this instance, all rows relating to in-store workers would be
stored in partition p0
, those relating to
office and support staff in p1
, and those
relating to managers in partition p2
.
It is also possible to use an expression in VALUES LESS
THAN
clauses. However, MySQL must be able to evaluate
the expression's return value as part of a LESS
THAN
(<
) comparison.
Rather than splitting up the table data according to store
number, you can use an expression based on one of the two
DATE
columns instead. For
example, let us suppose that you wish to partition based on the
year that each employee left the company; that is, the value of
YEAR(separated)
. An example of a
CREATE TABLE
statement that
implements such a partitioning scheme is shown here:
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE );
In this scheme, for all employees who left before 1991, the rows
are stored in partition p0
; for those who
left in the years 1991 through 1995, in p1
;
for those who left in the years 1996 through 2000, in
p2
; and for any workers who left after the
year 2000, in p3
.
Range partitioning is particularly useful when:
You want or need to delete “old” data. If you
are using the partitioning scheme shown immediately above,
you can simply use ALTER TABLE employees DROP
PARTITION p0;
to delete all rows relating to
employees who stopped working for the firm prior to 1991.
(See Section 12.1.7, “ALTER TABLE
Syntax”, and
Section 18.3, “Partition Management”, for more
information.) For a table with a great many rows, this can
be much more efficient than running a
DELETE
query such as
DELETE FROM employees WHERE YEAR(separated) <=
1990;
.
You want to use a column containing date or time values, or containing values arising from some other series.
You frequently run queries that depend directly on the
column used for partitioning the table. For example, when
executing a query such as EXPLAIN PARTITIONS SELECT
COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01'
AND '2000-12-31' GROUP BY store_id;
, MySQL can
quickly determine that only partition p2
needs to be scanned because the remaining partitions cannot
contain any records satisfying the WHERE
clause. See Section 18.4, “Partition Pruning”, for more
information about how this is accomplished.
Beginning with MySQL 5.1.43, it is also possible to partition a
table by RANGE
based on the value of a
TIMESTAMP
column, using the
UNIX_TIMESTAMP()
function, as
shown in this example:
CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE) );
Also beginning with MySQL 5.1.43, any other expressions
involving TIMESTAMP
values are
disallowed. (See Bug#42849.)
It is also possible in MySQL 5.1.43 and later to use
UNIX_TIMESTAMP(timestamp_column)
as a partitioning expression for tables that are partitioned
by LIST
. However, it is usually not
practical to do so.
User Comments
Add your own comment.