稱為分區修剪的優化基于一個相對簡單的概念，該概念可以描述為“ 不掃描沒有匹配值的分區 ”。假設t1通過以下語句創建分區表 ：

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

);

假設您希望從這樣的SELECT語句中獲得結果 ：

SELECT fname, lname, region_code, dob

FROM t1

WHERE region_code > 125 AND region_code < 130;

很容易看出，應該返回的行都不在分區p0或 p3;中。也就是說，我們只需要在分區中搜索 p1并p2找到匹配的行。通過限制搜索，與掃描表中的所有分區相比，查找匹配的行可以花費更少的時間和精力。這種“ 切掉 ”不需要的分區被稱為 修剪。當優化程序可以在執行此查詢時使用分區修剪時，對于包含相同列定義和數據的未分區表，查詢的執行速度可以比同一查詢快一個數量級。

只要WHERE條件可以減少到以下兩種情況之一，優化器就可以執行修剪 ：

partition_column = constant

partition_column IN (constant1, constant2, ..., constantN)

在第一種情況下，優化器僅對給定值的分區表達式求值，確定哪個分區包含該值，然后僅掃描該分區。在許多情況下，等號可以與另一個算術比較來代替，包括， <=，>=，和 <>。使用某些查詢 BETWEEN中WHERE子句也可以利用分區修剪。請參閱本節后面的示例。

在第二種情況下，優化器為列表中的每個值評估分區表達式，創建匹配分區的列表，然后僅掃描此分區列表中的分區。

SELECT， DELETE和 UPDATE語句支持分區修剪。一條INSERT語句對插入的每一行也只能訪問一個分區。即使對于由HASH或進行 分區的表也是如此，KEY盡管當前未在中顯示EXPLAIN。

修剪還可以應用于短范圍，優化程序可以將其轉換為等效的值列表。例如，在前面的示例中，WHERE子句可以轉換為WHERE region_code IN (126, 127, 128, 129)。然后，優化器可以確定列表中的前兩個值在partition中找到 p1，其余兩個值在partition中 p2，并且其他分區不包含任何相關值，因此無需在匹配的行中進行搜索。

優化程序還可以對WHERE涉及使用RANGE COLUMNS或LIST COLUMNS分區的表的多個列上的前述類型進行比較的條件 進行修剪 。

只要分區表達式包含一個等于或可以減少為一組相等項的范圍，或者當分區表達式表示增加或減少的關系時，都可以應用這種類型的優化。當分區表達式使用or 函數時，修剪還可以應用于在 DATE或 DATETIME列上分區的表 。當分區表達式使用該函數時，修剪也可以應用于此類表。 YEAR()TO_DAYS()TO_SECONDS()

假設使用以下所示的語句創建t2分區在表上的 表DATE：

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

);

以下語句using t2可以利用分區修剪：

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'

對于最后一條語句，優化器還可以執行以下操作：

查找包含范圍下限的分區。

YEAR('1984-06-21')產生1984在分區中找到 的值d3。

查找包含范圍高端的分區。

YEAR('1999-06-21')計算結果為 1999，可在partition中找到 d5。

僅掃描這兩個分區以及它們之間可能存在的任何分區。

在這種情況下，這意味著，只有分區 d3，d4以及 d5被掃描。其余分區可以安全地忽略(也可以忽略)。

重要

在針對分區表的語句條件中引用的 無效值DATE和DATETIME值WHERE均視為 NULL。這意味著諸如之類的查詢 不會返回任何值(請參見Bug＃40972)。 SELECT * FROM partitioned_table WHERE date_column < '2008-12-00'

到目前為止，我們僅查看了使用RANGE分區的示例 ，但是修剪也可以應用于其他分區類型。

考慮一個由分區的表LIST，其中分區表達式在增加或減少，例如t3此處所示的表。(在本示例中，為簡潔起見，我們假設該 region_code列的值限制為1到10之間(包括1和10)。

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)

);

對于諸如之類的語句SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3，優化器確定在值1、2和3中找到的分區(r0和r1)，并跳過其余的分區(r2和r3)。

對于用HASH或 進行分區的表，[LINEAR] KEY如果WHERE子句=針對分區表達式中使用的列使用簡單關系，則也可以進行分區修剪?？紤]這樣創建的表：

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;

可以刪除將列值與常量進行比較的語句：

UPDATE t4 WHERE region_code = 7;

修剪還可以用于短距離，因為優化程序可以將這種條件轉化為IN 關系。例如，使用與t4 先前定義的相同的表，可以刪除諸如此類的查詢：

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

SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

在這兩種情況下，WHERE子句均由優化器轉換為WHERE region_code IN (3, 4, 5)。

重要

僅當范圍大小小于分區數時才使用此優化。考慮以下語句：

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

中相應的范圍WHERE子句涵蓋9個值(4，5，6，7，8，9，10，11，12)，但t4僅具有8個分區。這意味著DELETE 不能修剪。

當用HASH或 分區表時[LINEAR] KEY，修剪只能在整數列上使用。例如，此語句不能使用修剪，因為它dob是一 DATE列：

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

但是，如果表將年份值存儲在 INT列中，則WHERE year_col >= 2001 AND year_col <= 2005可以刪除具有的查詢 。

如果表NDB被顯式分區，則可以修剪使用提供自動分區的存儲引擎的表，例如MySQL Cluster使用的存儲引擎。

總結

以上是生活随笔為你收集整理的mysql2008r1_mysql8 参考手册-分区修剪的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。