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

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

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

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

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

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

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

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

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

水平拆分 VS 垂直拆分

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

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

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

從改造的復(fù)雜程度來(lái)說(shuō)，前者小于后者。

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

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

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

分區(qū)表

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

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

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

提升查詢性能：

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

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

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

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

管理方便：

在對(duì)單個(gè)分區(qū)進(jìn)行刪除、遷移和維護(hù)時(shí)，不會(huì)影響到其它分區(qū)。

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

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

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

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 中，對(duì)于分區(qū)表，有個(gè)很重大的更新，即 InnoDB 存儲(chǔ)引擎原生支持了分區(qū)，無(wú)需再通過(guò) ha_partition 接口來(lái)實(shí)現(xiàn)。

所以，在 MySQL 5.7 中，如果要?jiǎng)?chuàng)建基于 MyISAM 存儲(chǔ)引擎的分區(qū)表，會(huì)提示 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ū)的存儲(chǔ)引擎。在 MySQL 8.0 中，就只有 InnoDB。

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

這也從另外一個(gè)角度說(shuō)明了為什么生產(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ū)表時(shí)，大家常常會(huì)碰到下面這個(gè)報(bào)錯(cuò)。

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 是操作流水號(hào)，一般都會(huì)被設(shè)置為主鍵，opr_date 是操作時(shí)間。基于操作時(shí)間來(lái)進(jìn)行分區(qū)，是一個(gè)常見(jiàn)的分區(qū)場(chǎng)景。

為了突破這個(gè)限制，可將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)建，又會(huì)帶來(lái)一個(gè)新的問(wèn)題，即對(duì)于同一個(gè) 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)

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

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

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ū)鍵也必須包含在唯一索引中。

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

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

但實(shí)際上，這個(gè)鍋?zhàn)?MySQL 背并不合適，對(duì)于 Oracle 索引組織表（ InnoDB 即是索引組織表），同樣也有這個(gè)限制。

Oracle 官方文檔（ http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514），在談到索引組織表（Index-Organized Table，簡(jiǎn)稱(chēng) IOT）的特性時(shí)，就明確提到了 “分區(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.

下面，我們看看剛開(kāi)始的建表 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

同樣報(bào)錯(cuò)。

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

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

之所以對(duì)索引組織表有這樣的限制，個(gè)人認(rèn)為，還是基于性能考慮。

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

而對(duì)于堆表則沒(méi)有這樣的限制。

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

但與 MySQL 不一樣的是，Oracle 實(shí)現(xiàn)了全局索引，所以針對(duì)上面的，同一個(gè) opr_no，允許插入到不同分區(qū)中的問(wèn)題，可通過(guò)全局唯一索引來(lái)規(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 卻無(wú)能為力，之所以會(huì)這樣，是因?yàn)?MySQL 分區(qū)表只實(shí)現(xiàn)了本地分區(qū)索引（Local Partitioned Index），而沒(méi)有實(shí)現(xiàn) Oracle 中的全局索引（Global Index）。

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

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

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

本地分區(qū)索引同時(shí)也是分區(qū)索引，分區(qū)索引和表分區(qū)之間是一一對(duì)應(yīng)的。
- 而全局索引，既可以是分區(qū)的，也可以是不分區(qū)的。
- 如果是全局分區(qū)索引，一個(gè)分區(qū)索引可對(duì)應(yīng)多個(gè)表分區(qū)，同樣，一個(gè)表分區(qū)也可對(duì)應(yīng)多個(gè)分區(qū)索引。
對(duì)本地分區(qū)索引的管理操作只會(huì)影響到單個(gè)分區(qū)，不會(huì)影響到其它分區(qū)。
- 而對(duì)全局分區(qū)索引的管理操作會(huì)造成整個(gè)索引的失效，當(dāng)然，這一點(diǎn)可通過(guò) UPDATE INDEXES 子句加以規(guī)避。
本地分區(qū)索引只能保證分區(qū)內(nèi)的唯一性，無(wú)法保證表級(jí)別的唯一性，但全局分區(qū)可以。
在 Oracle 中，無(wú)論是索引組織表還是堆表，如果要?jiǎng)?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