MySQL?分區(qū)表中分區(qū)鍵為什么必須是主鍵的一部分

更新時間：2022年03月17日 09:12:29 作者：iVictor

這篇文章主要介紹了MySQL?分區(qū)表中分區(qū)鍵為什么必須是主鍵的一部分，分區(qū)是一種表的設(shè)計模式，通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表，更多相關(guān)資料需要的小伙伴可以參考下面文章內(nèi)容

分區(qū)是一種表的設(shè)計模式，通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個小表。但是對于應用程序來講，分區(qū)的表和沒有分區(qū)的表是一樣的。換句話來講，分區(qū)對于應用是透明的，只是數(shù)據(jù)庫對于數(shù)據(jù)的重新整理

隨著業(yè)務(wù)的不斷發(fā)展，數(shù)據(jù)庫中的數(shù)據(jù)會越來越多，相應地，單表的數(shù)據(jù)量也會越到越大，大到一個臨界值，單表的查詢性能就會下降。

這個臨界值，并不能一概而論，它與硬件能力、具體業(yè)務(wù)有關(guān)。

雖然在很多 MySQL 運維規(guī)范里，都建議單表不超過 500w、1000w。

但實際上，我在生產(chǎn)環(huán)境，也見過大小超過 2T，記錄數(shù)過億的表，同時，業(yè)務(wù)不受影響。

單表過大時，業(yè)務(wù)通常會考慮兩種拆分方案：水平切分和垂直切分。

水平拆分 VS 垂直拆分

水平切分，拆分的維度是行，一般會根據(jù)某種規(guī)則或算法將表中的記錄拆分到多張表中。

拆分后的表既可在一個實例，也可在多個不同實例中。如果是后者，又會涉及到分布式事務(wù)。

垂直切分，拆分的維度是列，一般是將列拆分到多個業(yè)務(wù)模塊中。這種拆分更多的是上層業(yè)務(wù)的拆分。

從改造的復雜程度來說，前者小于后者。

所以，在單表數(shù)據(jù)量過大時，業(yè)界用得較多的還是水平拆分。

常見的水平拆分方案有：分庫分表、分區(qū)表。

雖然分庫分表是一個比較徹底的水平拆分方案，但一方面，它的改造需要一定的時間；另一方面，它對開發(fā)的能力也有一定的要求。相對來說，分區(qū)表就比較簡單，也無需業(yè)務(wù)改造。

分區(qū)表

很多人可能會認為 MySQL 的優(yōu)勢在于 OLTP 應用，對于 OLAP 應用就不太適合，所以，也不太推薦分區(qū)表這種偏 OLAP 的特性。

但實際上，對于某些業(yè)務(wù)類型，還是比較適合使用分區(qū)表的，尤其是那些有明顯冷熱數(shù)據(jù)之分，且數(shù)據(jù)的冷熱與時間相關(guān)的業(yè)務(wù)。

下面我們看看分區(qū)表的優(yōu)點：

提升查詢性能：

對于分區(qū)表的查詢操作，如果查詢條件中包含分區(qū)鍵，則這個查詢操作就只會被下推到符合條件的分區(qū)內(nèi)進行，無關(guān)分區(qū)將自動過濾掉。

在數(shù)據(jù)量比較大的情況下，能提升查詢速度。

對業(yè)務(wù)透明：

將表從一個非分區(qū)表轉(zhuǎn)換為分區(qū)表，業(yè)務(wù)端無需做任何改造。

管理方便：

在對單個分區(qū)進行刪除、遷移和維護時，不會影響到其它分區(qū)。

尤其是針對單個分區(qū)的刪除（DROP）操作，避免了針對這個分區(qū)所有記錄的 DELETE 操作。

遺憾的是，MySQL 分區(qū)表不支持并行查詢。理論上，當一個查詢涉及到多個分區(qū)時，分區(qū)與分區(qū)之間應進行并行查詢，這樣才能充分利用多核 CPU 資源。

但 MySQL 并不支持，包括早期的官方文檔，也提到了這個問題，也將這個功能的實現(xiàn)放到了優(yōu)先級列表中。

These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.

- Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By “parallelized,” we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.

- Achieving greater query throughput in virtue of spreading data seeks over multiple disks.

MySQL 8.0 中分區(qū)表的變化

在 MySQL 5.7 中，對于分區(qū)表，有個很重大的更新，即 InnoDB 存儲引擎原生支持了分區(qū)，無需再通過 ha_partition 接口來實現(xiàn)。

所以，在 MySQL 5.7 中，如果要創(chuàng)建基于 MyISAM 存儲引擎的分區(qū)表，會提示 warning 。

The partition engine, used by table 'sbtest.t_range', is deprecated and will be removed in a future release. Please use native partitioning instead.

而在 MySQL 8.0 中，則更為徹底，server 層移除了 ha_partition 接口代碼。

如果要使用分區(qū)表，只能使用支持原生分區(qū)的存儲引擎。在 MySQL 8.0 中，就只有 InnoDB。

這就意味著，在 MySQL 8.0 中，如果要創(chuàng)建 MyISAM 分區(qū)表，基本上就不可能了。

這也從另外一個角度說明了為什么生產(chǎn)上不建議使用 MyISAM 表。

mysql> CREATE TABLE t_range (
? ? -> ? ? id INT,
? ? -> ? ? name VARCHAR(10)
? ? -> ) ENGINE = MyISAM
? ? -> PARTITION BY RANGE (id) (
? ? -> ? ? PARTITION p0 VALUES LESS THAN (5),
? ? -> ? ? PARTITION p1 VALUES LESS THAN (10)
? ? -> );
ERROR 1178 (42000): The storage engine for the table doesn't support native partitioning

為什么分區(qū)鍵必須是主鍵的一部分？

在使用分區(qū)表時，大家常常會碰到下面這個報錯。

mysql> CREATE TABLE opr (
? ? -> ? ? opr_no INT,
? ? -> ? ? opr_date DATETIME,
? ? -> ? ? description VARCHAR(30),
? ? -> ? ? PRIMARY KEY (opr_no)
? ? -> )
? ? -> PARTITION BY RANGE COLUMNS (opr_date) (
? ? -> ? ? PARTITION p0 VALUES LESS THAN ('20210101'),
? ? -> ? ? PARTITION p1 VALUES LESS THAN ('20210102'),
? ? -> ? ? PARTITION p2 VALUES LESS THAN MAXVALUE
? ? -> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

即分區(qū)鍵必須是主鍵的一部分。

上面的 opr 是一張操作流水表。其中，opr_no 是操作流水號，一般都會被設(shè)置為主鍵，opr_date 是操作時間?；诓僮鲿r間來進行分區(qū)，是一個常見的分區(qū)場景。

為了突破這個限制，可將opr_date 作為主鍵的一部分。

mysql> CREATE TABLE opr (
? ? -> ? ? opr_no INT,
? ? -> ? ? opr_date DATETIME,
? ? -> ? ? description VARCHAR(30),
? ? -> ? ? PRIMARY KEY (opr_no, opr_date)
? ? -> )
? ? -> PARTITION BY RANGE COLUMNS (opr_date) (
? ? -> ? ? PARTITION p0 VALUES LESS THAN ('20210101'),
? ? -> ? ? PARTITION p1 VALUES LESS THAN ('20210102'),
? ? -> ? ? PARTITION p2 VALUES LESS THAN MAXVALUE
? ? -> );
Query OK, 0 rows affected (0.04 sec)

但是這么創(chuàng)建，又會帶來一個新的問題，即對于同一個 opr_no ，可插入到不同分區(qū)中。

mysql> CREATE TABLE opr (
? ? -> ? ? opr_no INT,
? ? -> ? ? opr_date DATETIME,
? ? -> ? ? description VARCHAR(30),
? ? -> ? ? PRIMARY KEY (opr_no, opr_date)
? ? -> )
? ? -> PARTITION BY RANGE COLUMNS (opr_date) (
? ? -> ? ? PARTITION p0 VALUES LESS THAN ('20210101'),
? ? -> ? ? PARTITION p1 VALUES LESS THAN ('20210102'),
? ? -> ? ? PARTITION p2 VALUES LESS THAN MAXVALUE
? ? -> );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into opr values(1,'2020-12-31 00:00:01','abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into opr values(1,'2021-01-01 00:00:01','abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from opr partition (p0);
+--------+---------------------+-------------+
| opr_no | opr_date ? ? ? ? ? ?| description |
+--------+---------------------+-------------+
| ? ? ?1 | 2020-12-31 00:00:01 | abc ? ? ? ? |
+--------+---------------------+-------------+
1 row in set (0.00 sec)

mysql> select * from opr partition (p1);
+--------+---------------------+-------------+
| opr_no | opr_date ? ? ? ? ? ?| description |
+--------+---------------------+-------------+
| ? ? ?1 | 2021-01-01 00:00:01 | abc ? ? ? ? |
+--------+---------------------+-------------+
1 row in set (0.00 sec)

這實際上違背了業(yè)務(wù)對于 opr_no 的唯一性要求。

既然這樣，有的童鞋會建議給opr_no 添加個唯一索引，But，現(xiàn)實是殘酷的。

mysql> create unique index uk_opr_no on opr (opr_no);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered)

即便是添加唯一索引，分區(qū)鍵也必須包含在唯一索引中。

總而言之，對于 MySQL 分區(qū)表，無法從數(shù)據(jù)庫層面保證非分區(qū)列在表級別的唯一性，只能確保其在分區(qū)內(nèi)的唯一性。

這也是 MySQL 分區(qū)表所為人詬病的地方之一。

但實際上，這個鍋讓 MySQL 背并不合適，對于 Oracle 索引組織表（ InnoDB 即是索引組織表），同樣也有這個限制。

Oracle 官方文檔（ http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514），在談到索引組織表（Index-Organized Table，簡稱 IOT）的特性時，就明確提到了 “分區(qū)鍵必須是主鍵的一部分”。

Note the following characteristics of partitioned IOTs:

? ?- Partition columns must be a subset of primary key columns.
? ?- Secondary indexes can be partitioned locally and globally.
? ?- OVERFLOW data segments are always equipartitioned with the table partitions.

下面，我們看看剛開始的建表 SQL ，在 Oracle 中的執(zhí)行效果。

SQL> CREATE TABLE opr_oracle (
? ? ? ? opr_no NUMBER,
? ? ? ? opr_date DATE,
? ? ? ? description VARCHAR2(30),
? ? ? ?PRIMARY KEY (opr_no)
? ? )
? ? ORGANIZATION INDEX
? ? PARTITION BY RANGE (opr_date) (
? ? ? ? PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
? ? ? ?PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
? ? ? ?PARTITION p2 VALUES LESS THAN (MAXVALUE)
? ?);
PARTITION BY RANGE (opr_date) (
? ? ? ? ? ? ? ? ? ? *
ERROR at line 8:
ORA-25199: partitioning key of a index-organized table must be a subset of the
primary key

同樣報錯。

注意：這里指定了 ORGANIZATION INDEX ，創(chuàng)建的是索引組織表。

看來，分區(qū)鍵必須是主鍵的一部分并不是 MySQL 的限制，而是索引組織表的限制。

之所以對索引組織表有這樣的限制，個人認為，還是基于性能考慮。

假設(shè)分區(qū)鍵和主鍵是兩個不同的列，在進行插入操作時，雖然也指定了分區(qū)鍵，但還是需要掃描所有分區(qū)才能判斷插入的主鍵值是否違反了唯一性約束。這樣的話，效率會比較低下，違背了分區(qū)表的初衷。

而對于堆表則沒有這樣的限制。

在堆表中，主鍵和表中的數(shù)據(jù)是分開存儲的，在判斷插入的主鍵值是否違反唯一性約束時，只需利用到主鍵索引。

但與 MySQL 不一樣的是，Oracle 實現(xiàn)了全局索引，所以針對上面的，同一個 opr_no，允許插入到不同分區(qū)中的問題，可通過全局唯一索引來規(guī)避。

SQL> CREATE TABLE opr_oracle (
? ? ? ? opr_no NUMBER,
? ? ? ? opr_date DATE,
? ? ? ? description VARCHAR2(30),
? ? ? ? PRIMARY KEY (opr_no, opr_date)
? ? )
? ? ORGANIZATION INDEX
? ? PARTITION BY RANGE (opr_date) (
? ? ? ? PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
? ? ? ?PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
? ? ? ?PARTITION p2 VALUES LESS THAN (MAXVALUE)
? ?);

Table created.

SQL> create unique index uk_opr_no on opr_oracle (opr_no);

Index created.

SQL> insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc');

1 row created.

SQL> insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc');
insert into opr_oracle values(1,to_date('2020-12-31 00:00:01','yyyy-mm-dd hh24:mi:ss'),'abc')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_IOT_TOP_87350) violated

但 MySQL 卻無能為力，之所以會這樣，是因為 MySQL 分區(qū)表只實現(xiàn)了本地分區(qū)索引（Local Partitioned Index），而沒有實現(xiàn) Oracle 中的全局索引（Global Index）。

本地分區(qū)索引 VS 全局索引

本地分區(qū)索引和全局索引的原理圖如下所示：

結(jié)合原理圖，我們來看看兩種索引之間的區(qū)別：

本地分區(qū)索引同時也是分區(qū)索引，分區(qū)索引和表分區(qū)之間是一一對應的。
- 而全局索引，既可以是分區(qū)的，也可以是不分區(qū)的。
- 如果是全局分區(qū)索引，一個分區(qū)索引可對應多個表分區(qū)，同樣，一個表分區(qū)也可對應多個分區(qū)索引。
對本地分區(qū)索引的管理操作只會影響到單個分區(qū)，不會影響到其它分區(qū)。
- 而對全局分區(qū)索引的管理操作會造成整個索引的失效，當然，這一點可通過 UPDATE INDEXES 子句加以規(guī)避。
本地分區(qū)索引只能保證分區(qū)內(nèi)的唯一性，無法保證表級別的唯一性，但全局分區(qū)可以。
在 Oracle 中，無論是索引組織表還是堆表，如果要創(chuàng)建本地唯一索引，同樣也要求分區(qū)鍵必須是唯一鍵的一部分。

SQL> create unique index uk_opr_no_local on opr_oracle(opr_no) local;
create unique index uk_opr_no_local on opr_oracle(opr_no) local
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index