MySQL?分區(qū)表中分區(qū)鍵為什么必須是主鍵的一部分
前言:
分區(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í)間?;诓僮鲿r(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)稱 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
總結(jié)
1. MySQL 分區(qū)表關(guān)于“分區(qū)鍵必須是唯一鍵(主鍵和唯一索引)的一部分”的限制,本質(zhì)上是索引組織表的限制。
2. MySQL 分區(qū)表只實(shí)現(xiàn)了本地分區(qū)索引,沒(méi)有實(shí)現(xiàn)全局索引,所以無(wú)法保證非分區(qū)列的全局唯一。
如果要保證非分區(qū)列的全局唯一,只能依賴業(yè)務(wù)實(shí)現(xiàn)了。
3. 不推薦使用 MyISAM 分區(qū)表。當(dāng)然,任何場(chǎng)景都不推薦使用 MyISAM 表。
到此這篇關(guān)于MySQL 分區(qū)表中分區(qū)鍵為什么必須是主鍵的一部分的文章就介紹到這了,更多相關(guān)MySQL 分區(qū)表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL查詢?nèi)哂嗨饕臀词褂眠^(guò)的索引操作
這篇文章主要介紹了MySQL查詢?nèi)哂嗨饕臀词褂眠^(guò)的索引操作,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧2021-03-03MySQL之Innodb_buffer_pool_size設(shè)置方式
這篇文章主要介紹了MySQL之Innodb_buffer_pool_size設(shè)置方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-08-08

將圖片儲(chǔ)存在MySQL數(shù)據(jù)庫(kù)中的幾種方法

MySQL 多個(gè)%等模糊查詢功能實(shí)現(xiàn)

Mysql關(guān)于數(shù)據(jù)庫(kù)是否應(yīng)該使用外鍵約束詳解說(shuō)明

MySQL安裝過(guò)程報(bào)starting?the?server報(bào)錯(cuò)詳細(xì)解決方案(附MySQL安裝程序)

簡(jiǎn)單實(shí)現(xiàn)SQLServer轉(zhuǎn)MYSQL的方法