欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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í)間?;诓僮鲿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)文章

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

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

    今天小編就為大家分享一篇關(guān)于將圖片儲(chǔ)存在MySQL數(shù)據(jù)庫(kù)中的幾種方法,小編覺(jué)得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來(lái)看看吧
    2019-03-03
  • mysql解壓縮方式安裝和徹底刪除的方法圖文詳解

    mysql解壓縮方式安裝和徹底刪除的方法圖文詳解

    這篇文章主要介紹了mysql解壓縮方式安裝和徹底刪除的方法,只有mysql徹底刪除干凈了,才可以裝另外新的版本,需要的朋友可以參考下
    2018-01-01
  • mysql?8.0.26?安裝配置圖文教程

    mysql?8.0.26?安裝配置圖文教程

    這篇文章主要為大家詳細(xì)介紹了mysql?8.0.26?安裝配置圖文教程,文中安裝步驟介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2022-04-04
  • MySQL 多個(gè)%等模糊查詢功能實(shí)現(xiàn)

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

    這篇文章主要介紹了MySQL 多個(gè)%等模糊查詢功能實(shí)現(xiàn),本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2023-07-07
  • Mysql關(guān)于數(shù)據(jù)庫(kù)是否應(yīng)該使用外鍵約束詳解說(shuō)明

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

    MySQL 外鍵約束(FOREIGN KEY)是表的一個(gè)特殊字段,經(jīng)常與主鍵約束一起使用。對(duì)于兩個(gè)具有關(guān)聯(lián)關(guān)系的表而言,相關(guān)聯(lián)字段中主鍵所在的表就是主表,外鍵所在的表就是從表。外鍵用來(lái)建立主表與從表的關(guān)聯(lián)關(guān)系,為兩個(gè)表的數(shù)據(jù)建立連接,約束兩個(gè)表中數(shù)據(jù)的一致性和完整性
    2021-10-10
  • MySQL安裝過(guò)程報(bào)starting?the?server報(bào)錯(cuò)詳細(xì)解決方案(附MySQL安裝程序)

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

    如果電腦是第一次安裝MySQL,一般不會(huì)出現(xiàn)這樣的報(bào)錯(cuò),starting the server失敗通常是因?yàn)樯洗伟惭b的該軟件未清除干凈,這篇文章主要給大家介紹了關(guān)于MySQL安裝過(guò)程報(bào)starting?the?server報(bào)錯(cuò)的詳細(xì)解決方案,文中還附MySQL安裝程序,需要的朋友可以參考下
    2024-03-03
  • 簡(jiǎn)單實(shí)現(xiàn)SQLServer轉(zhuǎn)MYSQL的方法

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

    SqlServer數(shù)據(jù)轉(zhuǎn)換成mysql數(shù)據(jù),可以說(shuō)是一個(gè)老生常談了,網(wǎng)上也有很多的方法,今天我們來(lái)看一種不一樣的方法,而且也非常的簡(jiǎn)單,雖然有點(diǎn)小缺陷,但還是不失為一種很好的方法,當(dāng)然如果結(jié)合mss2sql那就非常完美了
    2014-08-08
  • 最新評(píng)論