MySQL 分割區表中分割區鍵為什麼必須是主鍵的一部分

2022-03-17 10:00:41

分割區是一種表的設計模式，通俗地講表分割區是將一大表，根據條件分割成若干個小表。但是對於應用程式來講，分割區的表和沒有分割區的表是一樣的。換句話來講，分割區對於應用是透明的，只是資料庫對於資料的重新整理

隨著業務的不斷髮展，資料庫中的資料會越來越多，相應地，單表的資料量也會越到越大，大到一個臨界值，單表的查詢效能就會下降。

這個臨界值，並不能一概而論，它與硬體能力、具體業務有關。

雖然在很多 MySQL 運維規範裡，都建議單表不超過 500w、1000w。

但實際上，我在生產環境，也見過大小超過 2T，記錄數過億的表，同時，業務不受影響。

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

水平拆分 VS 垂直拆分

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

拆分後的表既可在一個範例，也可在多個不同範例中。如果是後者，又會涉及到分散式事務。

垂直切分，拆分的維度是列，一般是將列拆分到多個業務模組中。這種拆分更多的是上層業務的拆分。

從改造的複雜程度來說，前者小於後者。

所以，在單表資料量過大時，業界用得較多的還是水平拆分。

常見的水平拆分方案有：分庫分表、分割區表。

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

分割區表

很多人可能會認為 MySQL 的優勢在於 OLTP 應用，對於 OLAP 應用就不太適合，所以，也不太推薦分割區表這種偏 OLAP 的特性。

但實際上，對於某些業務型別，還是比較適合使用分割區表的，尤其是那些有明顯冷熱資料之分，且資料的冷熱與時間相關的業務。

下面我們看看分割區表的優點：

提升查詢效能：

對於分割區表的查詢操作，如果查詢條件中包含分割區鍵，則這個查詢操作就只會被下推到符合條件的分割區內進行，無關分割區將自動過濾掉。

在資料量比較大的情況下，能提升查詢速度。

對業務透明：

將表從一個非分割區錶轉換為分割區表，業務端無需做任何改造。

管理方便：

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

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

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

但 MySQL 並不支援，包括早期的官方檔案，也提到了這個問題，也將這個功能的實現放到了優先順序列表中。

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 中分割區表的變化

在 MySQL 5.7 中，對於分割區表，有個很重大的更新，即 InnoDB 儲存引擎原生支援了分割區，無需再通過 ha_partition 介面來實現。

所以，在 MySQL 5.7 中，如果要建立基於 MyISAM 儲存引擎的分割區表，會提示 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 介面程式碼。

如果要使用分割區表，只能使用支援原生分割區的儲存引擎。在 MySQL 8.0 中，就只有 InnoDB。

這就意味著，在 MySQL 8.0 中，如果要建立 MyISAM 分割區表，基本上就不可能了。

這也從另外一個角度說明了為什麼生產上不建議使用 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

為什麼分割區鍵必須是主鍵的一部分？

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

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).

即分割區鍵必須是主鍵的一部分。

上面的 opr 是一張操作流水錶。其中，opr_no 是操作流水號，一般都會被設定為主鍵，opr_date 是操作時間。基於操作時間來進行分割區，是一個常見的分割區場景。

為了突破這個限制，可將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)

但是這麼建立，又會帶來一個新的問題，即對於同一個 opr_no ，可插入到不同分割區中。

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)

這實際上違背了業務對於 opr_no 的唯一性要求。

既然這樣，有的童鞋會建議給opr_no 新增個唯一索引，But，現實是殘酷的。

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)

即便是新增唯一索引，分割區鍵也必須包含在唯一索引中。

總而言之，對於 MySQL 分割區表，無法從資料庫層面保證非分割區列在表級別的唯一性，只能確保其在分割區內的唯一性。

這也是 MySQL 分割區表所為人詬病的地方之一。

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

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

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 中的執行效果。

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 ，建立的是索引組織表。

看來，分割區鍵必須是主鍵的一部分並不是 MySQL 的限制，而是索引組織表的限制。

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

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

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

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

但與 MySQL 不一樣的是，Oracle 實現了全域性索引，所以針對上面的，同一個 opr_no，允許插入到不同分割區中的問題，可通過全域性唯一索引來規避。

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 分割區表只實現了本地分割區索引（Local Partitioned Index），而沒有實現 Oracle 中的全域性索引（Global Index）。

本地分割區索引 VS 全域性索引

本地分割區索引和全域性索引的原理圖如下所示：

結合原理圖，我們來看看兩種索引之間的區別：

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

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