MySQL 分區表,為什麼分區鍵必須是主鍵的一部分?

2021-12-28 MySQL實戰

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

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

雖然在很多 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> 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 (
  2      opr_no NUMBER,
  3      opr_date DATE,
  4      description VARCHAR2(30),
  5      PRIMARY KEY (opr_no)
  6  )
  7  ORGANIZATION INDEX
  8  PARTITION BY RANGE (opr_date) (
  9      PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
 10      PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
 11      PARTITION p2 VALUES LESS THAN (MAXVALUE)
 12  );
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 (
  2      opr_no NUMBER,
  3      opr_date DATE,
  4      description VARCHAR2(30),
  5      PRIMARY KEY (opr_no, opr_date)
  6  )
  7  ORGANIZATION INDEX
  8  PARTITION BY RANGE (opr_date) (
  9      PARTITION p0 VALUES LESS THAN (TO_DATE('20170713', 'yyyymmdd')),
 10      PARTITION p1 VALUES LESS THAN (TO_DATE('20170714', 'yyyymmdd')),
 11      PARTITION p2 VALUES LESS THAN (MAXVALUE)
 12  );

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
總結
1. MySQL 分區表關於「分區鍵必須是唯一鍵（主鍵和唯一索引）的一部分」的限制，本質上是索引組織表的限制。
2. MySQL 分區表只實現了本地分區索引，沒有實現全局索引，所以無法保證非分區列的全局唯一。
如果要保證非分區列的全局唯一，只能依賴業務實現了。
3. 不推薦使用 MyISAM 分區表。當然，任何場景都不推薦使用 MyISAM 表。

相關焦點

  • MySQL中的分區表
    在某些情況下可以並行地讀取各個分區中的數據，這樣有利於提高數據存儲和查詢的效率。為什麼要用分區表分區表，在一定的程度上可以提高MySQL資料庫的性能。因為每一個分區對應著磁碟上面的一個單獨的數據文件，這樣可以提高磁碟的I/O，有助於提高查詢和存儲的時候的效率。但是這個是有一個前提：每一個分區都位於不同的存儲設備上。
  • 表分區:mysql partition
    收錄於話題 #mysql可以看到每個分區都是一個獨立的ibd文件。下
  • MySQL 分區和分表知識總結、分區實戰以及分區的管理 | 資料
    分區表的原理分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分
  • MySQL 分區和分表知識總結、分區實戰以及分區的管理 | 周末送資料
    分區表的原理分區表是由多個相關的底層表實現，這些底層表也是由句柄對象表示，所以我們也可以直接訪問各個分區，存儲引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的存儲引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從存儲引擎的角度來看，底層表和一個普通表沒有任何不同，存儲引擎也無須知道這是一個普通表還是一個分區表的一部分
  • MySQL 主鍵的重要度
    數據行的所有操作都是基本主鍵進行的。資料庫中定義的主鍵具備如下特性：1、任何兩行都不具有相同的主鍵值，保證唯一性。2、每個行都必須具有一個主鍵值（主鍵列不允許NULL值）目前來說MySQL的處理邏輯都是跟主鍵綁在一起。主鍵的重要度不言而喻。
  • MySQL的主鍵命名挺任性,就這麼定了
    mysql> use testmysql> create table test_data2 (id int ,name varchar(30));Query OK, 0 rows affected (0.05 sec)接著創建一個主鍵
  • 為什麼不推薦使用 uuid 作為 mysql 主鍵?
    形且唯一，單機遞增)，而是推薦連續自增的主鍵 id，官方的推薦是auto_increment，那麼為什麼不建議採用 uuid，使用 uuid 究竟有什麼壞處？mysql程序實例使用uuid和自增id的索引結構對比一、mysql和程序實例1.1 要說明這個問題，我們首先來建立三張表分別是 user_auto_key, user_uuid, user_random_key, 分別表示自動增長的主鍵，uuid作為主鍵，隨機 key 作為主鍵，其它我們完全保持不變
  • 寫給 Java 程式設計師的 24 個MySQL面試題!
    一、為什麼用自增列作為主鍵？
  • MySQL時間分區的實現
    以月為維度做拆分按照月來拆分，有以下兩種寫法：第一種：直接按照月來拆12個分區，下面表ytt_pt1_month1分區類型為LIST，基於函數month直接計算，mysql> show create table ytt_pt1_month1\G*************************
  • 深入學習Oracle分區表及分區索引
    對於索引組織表，只能夠支持普通分區方式，不支持組合分區，常規表的限制對於索引組織表同樣有效，除此之外呢，還有一些其實的限制，比如要求索引組織表的分區依賴列必須是主鍵才可以等。註：本篇所有示例僅針對常規表，即堆組織表!對於索引，需要區分創建的是全局索引，或本地索引：全局索引(global index)：即可以分區，也可以不分區。
  • MySQL為什麼需要一個主鍵
    「主鍵（primary key） 一列（或一組列）」，其值能夠唯一區分表中的每個行。唯一標識表中每行的這個列（或這組列）稱為主鍵。「沒有主鍵，更新或刪除表中特定行很困難，因為沒有安全的方法保證只設計相關的行。」
  • 為什麼mysql不推薦使用uuid或者雪花id作為主鍵
    轉載至：https://www.cnblogs.com/wyq178/p/12548864.html為什麼mysql不推薦使用uuid或者雪花id作為主鍵前言：在mysql中設計表的時候,mysql官方推薦不要使用uuid或者不連續不重複的雪花id(long形且唯一),而是推薦連續自增的主鍵id,官方的推薦是auto_increment
  • Mysql分區(PARTITION)實戰--解決Mysql大量刪除數據效率低的問題
    法一： 減少每次delete的數據量，寫一個循環語句分多次刪除。這種方法沒有測過，但是目測效率也不會高到哪裡，而且對一張表進行頻繁讀寫和刪數據操作，會造成很高的I/O.法二：對netmon_history這張表進行分區。這種方法的優點是減少I/O操作，避免表的鎖定，而且還能提高mysql表的查詢效率。
  • 初中級必須知道的MYSQL經典面試題
    ，非主屬性必須完全依賴於主鍵。第三範式：在滿足第二範式的基礎上，在實體中不存在其他實體中的非主鍵屬性，傳遞函數依賴於主鍵屬性，確保數據表中的每一列數據都和主鍵直接相關，而不能間接相關(表中欄位[非主鍵]不存在對主鍵的傳遞依賴)6.你對sql的優化了解有多少，舉例說明一下，至少5個查詢不要使用select *；使用連接（join
  • MySQL數據量太大,日常卡死!想跑路了!
    MySQL實現分區的方式也意味著索引也是按照分區的子表定義，沒有全局索引用戶的SQL語句是需要針對分區表做優化，SQL條件中要帶上分區條件的列，從而使查詢定位到少量的分區上，否則就會掃描全部分區，可以通過EXPLAIN PARTITIONS來查看某條SQL語句會落在那些分區上，
  • 為什麼 MySQL 不推薦使用 uuid 或者雪花 id 作為主鍵?
    1.2.光有理論不行,直接上程序,使用spring的jdbcTemplate來實現增查測試：前言在mysql中設計表的時候,mysql官方推薦不要使用uuid或者不連續不重複的雪花id(long形且唯一，單機遞增),而是推薦連續自增的主鍵id,官方的推薦是auto_increment,那麼為什麼不建議採用uuid,使用uuid究竟有什麼壞處
  • 為什麼MySQL不推薦使用uuid或者雪花id作為主鍵?
    前言 在mysql中設計表的時候,mysql官方推薦不要使用uuid或者不連續不重複的雪花id(long形且唯一，單機遞增),而是推薦連續自增的主鍵id,官方的推薦是auto_increment,那麼為什麼不建議採用uuid,使用uuid究竟有什麼壞處？
  • Oracle 分區表自動分區
    單位的部分業務使用了分區表，每年都要提前創建新一年的表分區，不然到了第二年就會因為分區不存在無法寫入數據。
  • STAUBLI提花機的分區表與View60分區設置
    這個是STAUBLI JC7提花機吧，讀取上機文件時有一提示，為什麼有這錯誤呢？這個主要是因為提花要終端設置了分區表，而JC5上機文件設置的分區表沒有對應，所以………………那麼今天來講講STAUBLI提花機終端與View60軟體，對於分區表設置。前陣子我們講了BONAS提花機，提花機吊綜表設置，講了BONAS。
  • 憑啥不能用uuid做MySQL的主鍵!?
    前言在mysql中設計表的時候,mysql官方推薦不要使用uuid或者不連續不重複的雪花id(long形且唯一，單機遞增),而是推薦連續自增的主鍵id,官方的推薦是auto_increment,那麼為什麼不建議採用uuid,使用uuid究竟有什麼壞處？本篇博客我們就來分析這個問題,探討一下內部的原因。