前面兩篇講述了數(shù)據(jù)表分區(qū)的概念，特性及可能的缺陷，本篇介紹數(shù)據(jù)表分區(qū)的應(yīng)用。

優(yōu)化查詢

分區(qū)引入了的新的方式來優(yōu)化查詢（當(dāng)然也會有一些坑）。最大的特性是優(yōu)化器可以使用分區(qū)函數(shù)過濾掉無關(guān)的分區(qū)。這個特性就像是一個粗粒度的索引，使得查詢時可以訪問更少的數(shù)據(jù)。

因此在 WHERE 子句中指定分區(qū)條件十分重要，即便有可能是多余的。有了這個條件，優(yōu)化器就可以過濾掉不需要的分區(qū)。如果不這么做的話，查詢執(zhí)行器引擎不得不訪問數(shù)據(jù)表的全部分區(qū)，這會導(dǎo)致極其慢的查詢速度。

可以通過 EXPLAIN PARTITION 指令來查看優(yōu)化器是否過濾掉了分區(qū)，以下面的例子為例：

EXPLAIN PARTITIONS SELECT * FROM sales;

id: 1
select_type: SIMPLE
partitions: p_2019,p_2020,p_2021
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: 

如上面所見，這種情況會訪問全部的分區(qū)，而如果在 WHERE 條件增加約束條件：

EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE day > '2020-01-01';

id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2020,p_2021

優(yōu)化器對分區(qū)的優(yōu)化很到位，將范圍值轉(zhuǎn)換為離散的值并會針對它們決定要查詢哪些分區(qū)。然而，它并不是萬能的，下面的查詢看起來是可以優(yōu)化的，實際卻不會：

EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE YEAR(day) > 2020;

id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2019, p_2020,p_2021

MySQL 只能在分區(qū)函數(shù)對應(yīng)的列上進(jìn)行分區(qū)過濾優(yōu)化，而不能基于一個表達(dá)式，即便是表達(dá)式的結(jié)果看起來是一樣的。這和索引不能用在表達(dá)式的參數(shù)里是一樣的道理。上面的查詢可以用下面等效的方式進(jìn)行優(yōu)化：

EXPLAIN PARTITIONS SELECT * FROM sales_by_day 
WHERE day BETWEEN '2020-01-01' AND '2020-12-31';

id: 1
select_type: SIMPLE
table: sales_by_day
partitions: p_2020

由于 WHERE 條件直接引用了分區(qū)列的值，而不是表達(dá)式，優(yōu)化器可以進(jìn)行分區(qū)優(yōu)化。這個特性的首要原則是我們可以使用表達(dá)式進(jìn)行分區(qū)，但必須依據(jù)分區(qū)列進(jìn)行搜索查詢。

在查詢過程中，優(yōu)化器也足夠聰明地進(jìn)行分區(qū)優(yōu)化。例如，如果分區(qū)表在聯(lián)合查詢的第二張表中，而聯(lián)合查詢的條件是分區(qū)鍵，那么 MySQL 會只在關(guān)聯(lián)的分區(qū)中查找數(shù)據(jù)行（但 EXPLAIN 不會顯示優(yōu)化結(jié)果，這是因為這是在運(yùn)行中優(yōu)化的，而不是查詢優(yōu)化期）。

合并表

合并表的更早的、更簡單的分區(qū)，存在不同的限制并且優(yōu)化手段更少。由于數(shù)據(jù)表分區(qū)是處于嚴(yán)格的抽象層，并且不允許直接訪問分區(qū)的物理隱藏表。而合并表則允許我們從合并的表中單獨訪問隱藏的物理表。隨著未來分區(qū)的進(jìn)一步集成化和優(yōu)化器的改進(jìn)，合并表很可能被廢棄甚至被移除。

合并表實際上就是真實物理表的一個容器?？梢酝ㄟ^一個特殊的 UNION 語法去創(chuàng)建多張表進(jìn)行合并，下面就是一個例子：

CREATE TABLE t1(a INT NOT NULL PRIMARY KEY) ENGINE=MYISAM;
CREATE TBLE t2(a INT NOT NULL PRIMARY) ENGINE=MYISAM;
INSERT INTO t1(a) VALUES (1), (2);
INSERT INTO t2(a) VALUES(1), (2);
CREATE TABLE mrg(a INT NOT NULL PRIMARY KEY)
ENGINE=MERGE UNION=(t1, t2) INSERT_METHOD=LAST;
SELECT a FROM mrg;

執(zhí)行下面的操作后，其實相當(dāng)于從兩張表同時取出了數(shù)據(jù)，結(jié)果是：

需要注意的是實際的表的字段名，數(shù)量和類型都相同，而且索引既在合并表上有，在各自的分表也有。這是創(chuàng)建合并表的要求。注意在每個分表在相同的列上都是相同的主鍵，但是合并表可以有重復(fù)的行。這是合并表的缺陷，合并表的每條數(shù)據(jù)看起來都是正常的，但合并表不會自己增加相應(yīng)的約束，因此需要使用程序來約束。

INSERT_METHOD=LAST 指令告知 MySQL 會將對上一個表的插入數(shù)據(jù)同時發(fā)送到合并表中。我們只能指定 FIRST 或 LAST 操作，而且也可以直接對分表單獨操作。這與分區(qū)表是不同的，分區(qū)表對數(shù)據(jù)的存儲有更多的控制條件。

往合并表插入數(shù)據(jù)會同時在合并表和分區(qū)表中插入。

INSERT INTO mrg(a) VALUES(3);
SELECT a FROM t2;

合并表還有一些有趣的特性和限制，例如刪除合并表或其中的一個分表。刪除合并表不影響其分表，但是刪除其中一個分表則根據(jù)操作系統(tǒng)不同而不同。例如，對于 Linux 系統(tǒng)，分表的文件描述符依舊保留并且數(shù)據(jù)表也存在，但只能通過合并表查詢。

DROP TABLE t1, t2;
SELECT a FROM mrg;

下面是合并表的一些限制，這些需要記住，以避免踩坑：

• 創(chuàng)建合并表的操作不會檢查分表是否兼容，如果分表的定義存在不同，MySQL 可能創(chuàng)建一個不能用的合并表。而且，在創(chuàng)建一個有效的合并表后再單獨修改一個分表會導(dǎo)致合并表無法繼續(xù)使用。
• REPLACE 操作對 合并表來說不可用，并且自增鍵也一樣不會像預(yù)期那樣工作。
• 查詢合并表會訪問每個分表。這也會導(dǎo)致單行的查詢相比單表查詢更慢。因此，最好是限制分表的數(shù)量，尤其是這個表可能作為聯(lián)合查詢的第二張表時。每次訪問的數(shù)據(jù)越少，合并表的性能影響會越大。
• 通過提前訪問分區(qū)表可以使得范圍查詢相比單獨直接對合并表查詢受到的影響更小。
• 數(shù)據(jù)表掃描的速度對于合并表和正常表而言是相同的。
• 唯一性字段和主鍵在一旦查詢成功后就會停止查詢，這種情況下，服務(wù)端會依次訪問一張分表，直到獲取到想要的數(shù)據(jù)。
• 分表查詢的次序是根據(jù)創(chuàng)建表時候的次序決定的，如果需要經(jīng)常對數(shù)據(jù)指定訪問次序，可以通過這個特性改善合并表的排序操作速度。
• 由于合并表沒有隱藏 MyISAM 的數(shù)據(jù)表，會提供一些數(shù)據(jù)表分區(qū)所沒有的特性：一個 MyISAM 表可以是多個合并表的分表；可以直接在不同的服務(wù)器復(fù)制分表的.frm，.MYI 和.MYD 文件。在合并表定義好之后，還可以加入更多的分表。可以創(chuàng)建一個臨時合并表，例如指定時間范圍的數(shù)據(jù)，這是數(shù)據(jù)表分區(qū)沒法做到的?？梢詮暮喜⒈碇幸瞥粋€分表，以便進(jìn)行備份、恢復(fù)、更改、修復(fù)或其他操作。而完成這些操作后又可以加入到同一張合并表。
• 可以使用 myisampack 壓縮某些分表。

而數(shù)據(jù)表分區(qū)則不同，所有的物理表被 MySQL 服務(wù)端隱藏了，只能通過分區(qū)表訪問和控制。