Movatterモバイル変換

This section discusses an optimization known aspartition pruning. The core concept behind partition pruning is relatively simple, and can be described as“Do not scan partitions where there can be no matching values”. Suppose that you have a partitioned tablet1 defined by this statement:

CREATE TABLE t1 (    fname VARCHAR(50) NOT NULL,    lname VARCHAR(50) NOT NULL,    region_code TINYINT UNSIGNED NOT NULL,    dob DATE NOT NULL)PARTITION BY RANGE( region_code ) (    PARTITION p0 VALUES LESS THAN (64),    PARTITION p1 VALUES LESS THAN (128),    PARTITION p2 VALUES LESS THAN (192),    PARTITION p3 VALUES LESS THAN MAXVALUE);

Consider the case where you wish to obtain results from aSELECT statement such as this one:

SELECT fname, lname, region_code, dob    FROM t1    WHERE region_code > 125 AND region_code < 130;

It is easy to see that none of the rows which ought to be returned are in either of the partitionsp0 orp3; that is, we need to search only in partitionsp1 andp2 to find matching rows. By doing so, it is possible to expend much less time and effort in finding matching rows than would be required to scan all partitions in the table. This“cutting away” of unneeded partitions is known aspruning. When the optimizer can make use of partition pruning in performing this query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data.

The optimizer can perform pruning whenever aWHERE condition can be reduced to either one of the following two cases:

In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including<,>,<=,>=, and<>. Some queries usingBETWEEN in theWHERE clause can also take advantage of partition pruning. See the examples later in this section.

In the second case, the optimizer evaluates the partitioning expression for each value in the list, creates a list of matching partitions, and then scans only the partitions in this partition list.

MySQL can apply partition pruning toSELECT,DELETE, andUPDATE statements. AnINSERT statement also accesses only one partition per inserted row; this is true even for a table that is partitioned byHASH orKEY although this is not currently shown in the output ofEXPLAIN.

Pruning can also be applied to short ranges, which the optimizer can convert into equivalent lists of values. For instance, in the previous example, theWHERE clause can be converted toWHERE region_code IN (126, 127, 128, 129). Then the optimizer can determine that the first two values in the list are found in partitionp1, the remaining two values in partitionp2, and that the other partitions contain no relevant values and so do not need to be searched for matching rows.

The optimizer can also perform pruning forWHERE conditions that involve comparisons of the preceding types on multiple columns for tables that useRANGE COLUMNS orLIST COLUMNS partitioning.

This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on aDATE orDATETIME column when the partitioning expression uses theYEAR() orTO_DAYS() function. In addition, in MySQL 5.7, pruning can be applied for such tables when the partitioning expression uses theTO_SECONDS() function.

Suppose that tablet2, defined as shown here, is partitioned on aDATE column:

CREATE TABLE t2 (    fname VARCHAR(50) NOT NULL,    lname VARCHAR(50) NOT NULL,    region_code TINYINT UNSIGNED NOT NULL,    dob DATE NOT NULL)PARTITION BY RANGE( YEAR(dob) ) (    PARTITION d0 VALUES LESS THAN (1970),    PARTITION d1 VALUES LESS THAN (1975),    PARTITION d2 VALUES LESS THAN (1980),    PARTITION d3 VALUES LESS THAN (1985),    PARTITION d4 VALUES LESS THAN (1990),    PARTITION d5 VALUES LESS THAN (2000),    PARTITION d6 VALUES LESS THAN (2005),    PARTITION d7 VALUES LESS THAN MAXVALUE);

The following statements usingt2 can make of use partition pruning:

SELECT * FROM t2 WHERE dob = '1982-06-23';UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

In the case of the last statement, the optimizer can also act as follows:

So far, we have looked only at examples usingRANGE partitioning, but pruning can be applied with other partitioning types as well.

Consider a table that is partitioned byLIST, where the partitioning expression is increasing or decreasing, such as the tablet3 shown here. (In this example, we assume for the sake of brevity that theregion_code column is limited to values between 1 and 10 inclusive.)

CREATE TABLE t3 (    fname VARCHAR(50) NOT NULL,    lname VARCHAR(50) NOT NULL,    region_code TINYINT UNSIGNED NOT NULL,    dob DATE NOT NULL)PARTITION BY LIST(region_code) (    PARTITION r0 VALUES IN (1, 3),    PARTITION r1 VALUES IN (2, 5, 8),    PARTITION r2 VALUES IN (4, 9),    PARTITION r3 VALUES IN (6, 7, 10));

For a statement such asSELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3, the optimizer determines in which partitions the values 1, 2, and 3 are found (r0 andr1) and skips the remaining ones (r2 andr3).

For tables that are partitioned byHASH or[LINEAR] KEY, partition pruning is also possible in cases in which theWHERE clause uses a simple= relation against a column used in the partitioning expression. Consider a table created like this:

CREATE TABLE t4 (    fname VARCHAR(50) NOT NULL,    lname VARCHAR(50) NOT NULL,    region_code TINYINT UNSIGNED NOT NULL,    dob DATE NOT NULL)PARTITION BY KEY(region_code)PARTITIONS 8;

A statement that compares a column value with a constant can be pruned:

UPDATE t4 WHERE region_code = 7;

Pruning can also be employed for short ranges, because the optimizer can turn such conditions intoIN relations. For example, using the same tablet4 as defined previously, queries such as these can be pruned:

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

In both these cases, theWHERE clause is transformed by the optimizer intoWHERE region_code IN (3, 4, 5).

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

When a table is partitioned byHASH or[LINEAR] KEY, pruning can be used only on integer columns. For example, this statement cannot use pruning becausedob is aDATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

However, if the table stores year values in anINT column, then a query havingWHERE year_col >= 2001 AND year_col <= 2005 can be pruned.

Prior to MySQL 5.7.1, partition pruning was disabled for all tables using a storage engine that provides automatic partitioning, such as theNDB storage engine used by NDB Cluster. (Bug #14672885) Beginning with MySQL 5.7.1, such tables can be pruned if they are explicitly partitioned. (Bug #14827952)