MySQL分區(qū)表的實(shí)現(xiàn)示例

更新時(shí)間：2024年08月31日 11:50:53 作者：Archie_java

MySQL分區(qū)是將一張表分割成獨(dú)立的子表的技術(shù),本文主要介紹了MySQL分區(qū)表的實(shí)現(xiàn)示例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

MySQL建立分區(qū)的條件是

什么是MySQL分區(qū)？

MySQL分區(qū)是將一張表分割成獨(dú)立的子表的技術(shù)。每個(gè)子表被稱為分區(qū)，它們有著相同的結(jié)構(gòu)和字段，但存儲著不同的數(shù)據(jù)。這項(xiàng)技術(shù)可以提高查詢速度，減少日志文件和磁盤空間的使用。

建立分區(qū)的條件

要建立MySQL分區(qū)，需要滿足以下幾個(gè)條件：

1.所需的MySQL版本：

MySQL 5.1.5及以上版本支持分區(qū)，但僅限于使用InnoDB和MyISAM存儲引擎的表。

2.分區(qū)字段：

必須定義一個(gè)或多個(gè)分區(qū)字段來確定如何將數(shù)據(jù)行分配到各個(gè)分區(qū)中。分區(qū)字段必須是表的主鍵或唯一索引之一。

3.分區(qū)類型：

MySQL提供了多種分區(qū)類型，包括范圍分區(qū)、哈希分區(qū)和列表分區(qū)。你需要根據(jù)數(shù)據(jù)特點(diǎn)和查詢需求選擇合適的分區(qū)類型。

4.分區(qū)數(shù)量：

決定分區(qū)數(shù)量需要考慮表的大小、查詢的復(fù)雜度、硬件資源等因素。建議根據(jù)具體情況選取合適的分區(qū)數(shù)量，一般不宜超過1000個(gè)。

MySQL分區(qū)技術(shù)可以大大提高查詢效率和管理的便利性，但在實(shí)際使用中需要根據(jù)具體情況選擇合適的分區(qū)條件和數(shù)量，避免性能瓶頸和資源浪費(fèi)。

分區(qū)表介紹

MySQL 數(shù)據(jù)庫中的數(shù)據(jù)是以文件的形勢存在磁盤上的，默認(rèn)放在 /var/lib/mysql/ 目錄下面，我們可以通過 show variables like '%datadir%'; 命令來查看：

我們進(jìn)入到這個(gè)目錄下，就可以看到我們定義的所有數(shù)據(jù)庫了，一個(gè)數(shù)據(jù)庫就是一個(gè)文件夾，一個(gè)庫中，有其對應(yīng)的表的信息，如下：

在 MySQL 中，如果存儲引擎是 MyISAM，那么在 data 目錄下會看到 3 類文件：.frm、.myi、.myd，如下：

*.frm：這個(gè)是表定義，是描述表結(jié)構(gòu)的文件。
*.myd：這個(gè)是數(shù)據(jù)信息文件，是表的數(shù)據(jù)文件。
*.myi：這個(gè)是索引信息文件。

如果存儲引擎是 InnoDB, 那么在 data 目錄下會看到兩類文件：.frm、.ibd，如下：

*.frm：表結(jié)構(gòu)文件。
*.ibd：表數(shù)據(jù)和索引的文件。

無論是哪種存儲引擎，只要一張表的數(shù)據(jù)量過大，就會導(dǎo)致 *.myd、*.myi 以及 *.ibd 文件過大，數(shù)據(jù)的查找就會變的很慢。

為了解決這個(gè)問題，我們可以利用 MySQL 的分區(qū)功能，在物理上將這一張表對應(yīng)的文件，分割成許多小塊，如此，當(dāng)我們查找一條數(shù)據(jù)時(shí)，就不用在某一個(gè)文件中進(jìn)行整個(gè)遍歷了，我們只需要知道這條數(shù)據(jù)位于哪一個(gè)數(shù)據(jù)塊，然后在那一個(gè)數(shù)據(jù)塊上查找就行了；另一方面，如果一張表的數(shù)據(jù)量太大，可能一個(gè)磁盤放不下，這個(gè)時(shí)候，通過表分區(qū)我們就可以把數(shù)據(jù)分配到不同的磁盤里面去。

通俗地講表分區(qū)是將一大表，根據(jù)條件分割成若干個(gè)小表。

如：某用戶表的記錄超過了600萬條，那么就可以根據(jù)入庫日期將表分區(qū)，也可以根據(jù)所在地將表分區(qū)。當(dāng)然也可根據(jù)其他的條件分區(qū)。

MySQL 從 5.1 開始添加了對分區(qū)的支持，分區(qū)的過程是將一個(gè)表或索引分解為多個(gè)更小、更可管理的部分。對于開發(fā)者而言，分區(qū)后的表使用方式和不分區(qū)基本上還是一模一樣，只不過在物理存儲上，原本該表只有一個(gè)數(shù)據(jù)文件，現(xiàn)在變成了多個(gè)，每個(gè)分區(qū)都是獨(dú)立的對象，可以獨(dú)自處理，也可以作為一個(gè)更大對象的一部分進(jìn)行處理。

需要注意的是，分區(qū)功能并不是在存儲引擎層完成的，常見的存儲引擎如 InnoDB、MyISAM、NDB 等都支持分區(qū)。但并不是所有的存儲引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分區(qū)，因此在使用此分區(qū)功能前，應(yīng)該對選擇的存儲引擎對分區(qū)的支持有所了解。

表分區(qū)的優(yōu)缺點(diǎn)和限制

MySQL分區(qū)有優(yōu)點(diǎn)也有一些缺點(diǎn)，如下：

優(yōu)點(diǎn)：

查詢性能提升：分區(qū)可以將大表劃分為更小的部分，查詢時(shí)只需掃描特定的分區(qū)，而不是整個(gè)表，從而提高查詢性能。特別是在處理大量數(shù)據(jù)或高并發(fā)負(fù)載時(shí)，分區(qū)可以顯著減少查詢的響應(yīng)時(shí)間。
管理和維護(hù)的簡化：使用分區(qū)可以更輕松地管理和維護(hù)數(shù)據(jù)?？梢葬槍μ囟ǖ姆謪^(qū)執(zhí)行維護(hù)操作，如備份、恢復(fù)、優(yōu)化和數(shù)據(jù)清理，而不必處理整個(gè)表。這簡化了維護(hù)任務(wù)并減少了操作的復(fù)雜性。
數(shù)據(jù)管理靈活性：通過分區(qū)，可以根據(jù)業(yè)務(wù)需求輕松地添加或刪除分區(qū)，而無需影響整個(gè)表。這使得數(shù)據(jù)的增長和變化更具彈性，可以根據(jù)需求進(jìn)行動態(tài)調(diào)整。
改善數(shù)據(jù)安全性和可用性：可以將不同分區(qū)的數(shù)據(jù)分布在不同的存儲設(shè)備上，從而提高數(shù)據(jù)的安全性和可用性。例如，可以將熱數(shù)據(jù)放在高速存儲設(shè)備上，而將冷數(shù)據(jù)放在廉價(jià)存儲設(shè)備上，以實(shí)現(xiàn)更高的性能和成本效益。

缺點(diǎn)：

復(fù)雜性增加：分區(qū)引入了額外的復(fù)雜性，包括分區(qū)策略的選擇、表結(jié)構(gòu)的設(shè)計(jì)和維護(hù)、查詢邏輯的調(diào)整等。正確地設(shè)置和管理分區(qū)需要一定的經(jīng)驗(yàn)和專業(yè)知識。
索引效率下降：對于某些查詢，特別是涉及跨分區(qū)的查詢，可能會導(dǎo)致索引效率下降。由于查詢需要在多個(gè)分區(qū)之間進(jìn)行掃描，可能無法充分利用索引優(yōu)勢，從而影響查詢性能。
存儲空間需求增加：使用分區(qū)會導(dǎo)致一定程度的存儲空間浪費(fèi)。每個(gè)分區(qū)都需要占用一定的存儲空間，包括分區(qū)元數(shù)據(jù)和一些額外的開銷。因此，對于分區(qū)鍵的選擇和分區(qū)粒度的設(shè)置需要權(quán)衡存儲空間和性能之間的關(guān)系。
功能限制：在某些情況下，分區(qū)可能會限制某些MySQL的功能和特性的使用。例如，某些類型的索引可能無法在分區(qū)表上使用，或者某些DDL操作可能需要更復(fù)雜的處理。

在考慮使用分區(qū)時(shí)，需要綜合考慮業(yè)務(wù)需求、查詢模式、數(shù)據(jù)規(guī)模和硬件資源等因素，并權(quán)衡分區(qū)帶來的優(yōu)勢和缺點(diǎn)。對于特定的應(yīng)用和數(shù)據(jù)場景，分區(qū)可能是一個(gè)有效的解決方案，但并不適用于所有情況。

同時(shí)分區(qū)表也存在一些限制，如下：

限制：

在mysql5.6.7之前的版本，一個(gè)表最多有1024個(gè)分區(qū)；從5.6.7開始，一個(gè)表最多可以有8192個(gè)分區(qū)。
分區(qū)表無法使用外鍵約束。
NULL值會使分區(qū)過濾無效。
所有分區(qū)必須使用相同的存儲引擎。

分區(qū)適用場景

分區(qū)表在以下情況下可以發(fā)揮其優(yōu)勢，適用于以下幾種使用場景：

大型表處理：當(dāng)面對非常大的表時(shí)，分區(qū)表可以提高查詢性能。通過將表分割為更小的分區(qū)，查詢操作只需要處理特定的分區(qū)，從而減少掃描的數(shù)據(jù)量，提高查詢效率。這在處理日志數(shù)據(jù)、歷史數(shù)據(jù)或其他需要大量存儲和高性能查詢的場景中非常有用。
時(shí)間范圍查詢：對于按時(shí)間排序的數(shù)據(jù)，分區(qū)表可以按照時(shí)間范圍進(jìn)行分區(qū)，每個(gè)分區(qū)包含特定時(shí)間段內(nèi)的數(shù)據(jù)。這使得按時(shí)間范圍進(jìn)行查詢變得更高效，例如在某個(gè)時(shí)間段內(nèi)檢索數(shù)據(jù)、生成報(bào)表或執(zhí)行時(shí)間段的聚合操作。
數(shù)據(jù)歸檔和數(shù)據(jù)保留：分區(qū)表可用于數(shù)據(jù)歸檔和數(shù)據(jù)保留的需求。舊數(shù)據(jù)可以歸檔到單獨(dú)的分區(qū)中，并將其存儲在低成本的存儲介質(zhì)上。同時(shí)，可以保留較新數(shù)據(jù)在高性能的存儲介質(zhì)上，以便快速查詢和操作。
并行查詢和負(fù)載均衡：通過哈希分區(qū)或鍵分區(qū)，可以將數(shù)據(jù)均勻地分布在多個(gè)分區(qū)中，從而實(shí)現(xiàn)并行查詢和負(fù)載均衡。查詢可以同時(shí)在多個(gè)分區(qū)上進(jìn)行，并在最終合并結(jié)果，提高查詢性能和系統(tǒng)吞吐量。
數(shù)據(jù)刪除和維護(hù)：使用分區(qū)表，可以更輕松地刪除或清理不再需要的數(shù)據(jù)。通過刪除整個(gè)分區(qū)，可以更快速地刪除大量數(shù)據(jù)，而不會影響整個(gè)表的操作。此外，可以針對特定分區(qū)執(zhí)行維護(hù)任務(wù)，如重新構(gòu)建索引、備份和優(yōu)化，以減少對整個(gè)表的影響。

分區(qū)表并非適用于所有情況。在選擇使用分區(qū)表時(shí)，需要綜合考慮數(shù)據(jù)量、查詢模式、存儲資源和硬件能力等因素，并評估分區(qū)對性能和管理的影響。

分區(qū)方式

分區(qū)有2種方式，水平切分和垂直切分。MySQL 數(shù)據(jù)庫支持的分區(qū)類型為水平分區(qū)，它不支持垂直分區(qū)。

此外，MySQL數(shù)據(jù)庫的分區(qū)是局部分區(qū)索引，一個(gè)分區(qū)中既存放了數(shù)據(jù)又存放了索引。而全局分區(qū)是指，數(shù)據(jù)存放在各個(gè)分區(qū)中，但是所有數(shù)據(jù)的索引放在一個(gè)對象中。目前，MySQL數(shù)據(jù)庫還不支持全局分區(qū)。

分區(qū)策略

RANGE分區(qū)

RANGE分區(qū)是MySQL中的一種分區(qū)策略，根據(jù)某一列的范圍值將數(shù)據(jù)分布到不同的分區(qū)。每個(gè)分區(qū)包含特定的范圍。下面是RANGE分區(qū)的定義方式、特點(diǎn)以及代碼示例。

定義方式：

指定分區(qū)鍵：選擇作為分區(qū)依據(jù)的列作為分區(qū)鍵，通常是日期、數(shù)值等具有范圍特性的列。
分區(qū)函數(shù)：通過PARTITION BY RANGE指定使用RANGE分區(qū)策略。
定義分區(qū)范圍：使用VALUES LESS THAN子句定義每個(gè)分區(qū)的范圍。

RANGE分區(qū)的特點(diǎn)：

范圍劃分：根據(jù)指定列的范圍進(jìn)行分區(qū)，適用于需要按范圍進(jìn)行查詢和管理的情況。
靈活的范圍定義：可以定義任意數(shù)量的分區(qū)，并且每個(gè)分區(qū)可以具有不同的范圍。
高效查詢：根據(jù)查詢條件的范圍，MySQL能夠快速定位到特定的分區(qū)，提高查詢效率。
動態(tài)管理：可以根據(jù)業(yè)務(wù)需求輕松添加或刪除分區(qū)，適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個(gè)使用RANGE分區(qū)的代碼示例：

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sales_date)) (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

在上述示例中，我們創(chuàng)建了名為sales的表，使用RANGE分區(qū)策略。根據(jù)sales_date列的年份范圍將數(shù)據(jù)分布到不同的分區(qū)。

PARTITION BY RANGE (YEAR(sales_date))：指定使用RANGE分區(qū)，基于sales_date列的年份進(jìn)行分區(qū)。
PARTITION p1 VALUES LESS THAN (2020)：定義名為p1的分區(qū)，包含年份小于2020的數(shù)據(jù)。
PARTITION p2 VALUES LESS THAN (2021)：定義名為p2的分區(qū)，包含年份小于2021的數(shù)據(jù)。
PARTITION p3 VALUES LESS THAN (2022)：定義名為p3的分區(qū)，包含年份小于2022的數(shù)據(jù)。
PARTITION p4 VALUES LESS THAN MAXVALUE：定義名為p4的分區(qū)，包含超出定義范圍的數(shù)據(jù)。

RANGE分區(qū)允許根據(jù)列值的范圍將數(shù)據(jù)分散到不同的分區(qū)中，適用于按范圍進(jìn)行查詢和管理的情況。它提供了更靈活的數(shù)據(jù)管理和查詢效率的提升。

LIST分區(qū)

LIST分區(qū)是根據(jù)某一列的離散值將數(shù)據(jù)分布到不同的分區(qū)。每個(gè)分區(qū)包含特定的列值列表。下面是LIST分區(qū)的定義方式、特點(diǎn)以及代碼示例。
定義方式：

指定分區(qū)鍵：選擇作為分區(qū)依據(jù)的列作為分區(qū)鍵，通常是具有離散值的列，如地區(qū)、類別等。
分區(qū)函數(shù)：通過PARTITION BY LIST指定使用LIST分區(qū)策略。
定義分區(qū)列表：使用VALUES IN子句定義每個(gè)分區(qū)包含的列值列表。

LIST分區(qū)的特點(diǎn)：

列值離散：根據(jù)指定列的具體取值進(jìn)行分區(qū)，適用于具有離散值的列。

靈活的分區(qū)定義：可以定義任意數(shù)量的分區(qū)，并且每個(gè)分區(qū)可以具有不同的列值列表。

高效查詢：根據(jù)查詢條件的列值直接定位到特定分區(qū)，提高查詢效率。

動態(tài)管理：可以根據(jù)業(yè)務(wù)需求輕松添加或刪除分區(qū)，適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個(gè)使用LIST分區(qū)的代碼示例：

CREATE TABLE users (
id INT,
username VARCHAR(50),
region VARCHAR(50)
)
PARTITION BY LIST (region) (
PARTITION p_east VALUES IN ('New York', 'Boston'),
PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'),
PARTITION p_other VALUES IN (DEFAULT)
);

在上述示例中，我們創(chuàng)建了名為users的表，使用LIST分區(qū)策略。根據(jù)region列的具體取值將數(shù)據(jù)分布到不同的分區(qū)。

PARTITION BY LIST (region)：指定使用LIST分區(qū)，基于region列的值進(jìn)行分區(qū)。
PARTITION p_east VALUES IN ('New York', 'Boston')：定義名為p_east的分區(qū)，包含值為’New York’和’Boston’的region列的數(shù)據(jù)。
PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco')：定義名為p_west的分區(qū)，包含值為’Los Angeles’和’San Francisco’的region列的數(shù)據(jù)。
PARTITION p_other VALUES IN (DEFAULT)：定義名為p_other的分區(qū)，包含其他region列值的數(shù)據(jù)。

HASH分區(qū)

HASH分區(qū)是使用哈希算法將數(shù)據(jù)均勻地分布到多個(gè)分區(qū)中。下面是HASH分區(qū)的定義方式、特點(diǎn)以及代碼示例。
定義方式：

指定分區(qū)鍵：選擇作為分區(qū)依據(jù)的列作為分區(qū)鍵。
分區(qū)函數(shù)：通過PARTITION BY HASH指定使用HASH分區(qū)策略。
定義分區(qū)數(shù)量：使用PARTITIONS關(guān)鍵字指定分區(qū)的數(shù)量。

HASH分區(qū)的特點(diǎn)：

數(shù)據(jù)均勻分布：HASH分區(qū)使用哈希算法將數(shù)據(jù)均勻地分布到不同的分區(qū)中，確保數(shù)據(jù)在各個(gè)分區(qū)之間平衡。
并行查詢性能：通過將數(shù)據(jù)分散到多個(gè)分區(qū)，HASH分區(qū)可以提高并行查詢的性能，多個(gè)查詢可以同時(shí)在不同分區(qū)上執(zhí)行。
簡化管理：HASH分區(qū)使得數(shù)據(jù)管理更加靈活，可以輕松地添加或刪除分區(qū)，以適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個(gè)使用HASH分區(qū)的代碼示例：

CREATE TABLE sensor_data (
id INT,
sensor_name VARCHAR(50),
value INT
)
PARTITION BY HASH (id)
PARTITIONS 4;

在上述示例中，我們創(chuàng)建了名為sensor_data的表，使用HASH分區(qū)策略。根據(jù)id列的哈希值將數(shù)據(jù)分布到4個(gè)分區(qū)中。

PARTITION BY HASH (id)：指定使用HASH分區(qū)，基于id列的哈希值進(jìn)行分區(qū)。
PARTITIONS 4：指定創(chuàng)建4個(gè)分區(qū)。

KEY分區(qū)

KEY分區(qū)是根據(jù)某一列的哈希值將數(shù)據(jù)分布到不同的分區(qū)。不同于HASH分區(qū)，KEY分區(qū)使用的是列值的哈希值而不是哈希函數(shù)。下面是KEY分區(qū)的定義方式、特點(diǎn)以及代碼示例。

定義方式：

指定分區(qū)鍵：選擇作為分區(qū)依據(jù)的列作為分區(qū)鍵。
分區(qū)函數(shù)：通過PARTITION BY KEY指定使用KEY分區(qū)策略。
定義分區(qū)數(shù)量：使用PARTITIONS關(guān)鍵字指定分區(qū)的數(shù)量。

KEY分區(qū)的特點(diǎn)：

哈希分布：KEY分區(qū)使用列值的哈希值將數(shù)據(jù)分布到不同的分區(qū)中，與哈希函數(shù)不同，它使用的是列值的哈希值。
高度自定義：KEY分區(qū)允許根據(jù)業(yè)務(wù)需求自定義分區(qū)邏輯，可以靈活地選擇分區(qū)鍵和分區(qū)數(shù)量。
并行查詢性能：通過將數(shù)據(jù)分散到多個(gè)分區(qū)，KEY分區(qū)可以提高并行查詢的性能，多個(gè)查詢可以同時(shí)在不同分區(qū)上執(zhí)行。
簡化管理：KEY分區(qū)使得數(shù)據(jù)管理更加靈活，可以輕松地添加或刪除分區(qū)，以適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個(gè)使用KEY分區(qū)的代碼示例：

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
)
PARTITION BY KEY (customer_id)
PARTITIONS 5;

在上述示例中，我們創(chuàng)建了名為orders的表，使用KEY分區(qū)策略。根據(jù)customer_id列的哈希值將數(shù)據(jù)分布到5個(gè)分區(qū)中。

PARTITION BY KEY (customer_id)：指定使用KEY分區(qū)，基于customer_id列的哈希值進(jìn)行分區(qū)。
PARTITIONS 5：指定創(chuàng)建5個(gè)分區(qū)。

COLUMNS 分區(qū)

MySQL在5.5版本引入了COLUMNS分區(qū)類型，其中包括RANGE COLUMNS分區(qū)和LIST COLUMNS分區(qū)。以下是對這兩種COLUMNS分區(qū)的詳細(xì)說明：

RANGE COLUMNS分區(qū)： RANGE COLUMNS分區(qū)是根據(jù)列的范圍值將數(shù)據(jù)分布到不同的分區(qū)的分區(qū)策略。它類似于RANGE分區(qū)，但是根據(jù)多個(gè)列的范圍值進(jìn)行分區(qū)，而不是只根據(jù)一個(gè)列。這使得范圍的定義更加靈活，可以基于多個(gè)列的組合來進(jìn)行分區(qū)。
下面是一個(gè)RANGE COLUMNS分區(qū)的代碼示例：

CREATE TABLE sales (
   id INT,
   sales_date DATE,
   region VARCHAR(50),
   amount DECIMAL(10,2)
   )
   PARTITION BY RANGE COLUMNS(region, sales_date) (
   PARTITION p1 VALUES LESS THAN ('East', '2022-01-01'),
   PARTITION p2 VALUES LESS THAN ('West', '2022-01-01'),
   PARTITION p3 VALUES LESS THAN ('East', MAXVALUE),
   PARTITION p4 VALUES LESS THAN ('West', MAXVALUE)
   );

在上述示例中，我們創(chuàng)建了一個(gè)名為sales的表，并使用RANGE COLUMNS分區(qū)策略。根據(jù)region和sales_date兩列的范圍將數(shù)據(jù)分布到不同的分區(qū)。每個(gè)分區(qū)根據(jù)這兩列的范圍值進(jìn)行劃分。

LIST COLUMNS分區(qū)： LIST COLUMNS分區(qū)是根據(jù)列的離散值將數(shù)據(jù)分布到不同的分區(qū)的分區(qū)策略。它類似于LIST分區(qū)，但是根據(jù)多個(gè)列的離散值進(jìn)行分區(qū)，而不是只根據(jù)一個(gè)列。這使得離散值的定義更加靈活，可以基于多個(gè)列的組合來進(jìn)行分區(qū)。
下面是一個(gè)LIST COLUMNS分區(qū)的代碼示例：

CREATE TABLE users (
   id INT,
   username VARCHAR(50),
   region VARCHAR(50),
   category VARCHAR(50)
   )
   PARTITION BY LIST COLUMNS(region, category) (
   PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')),
   PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')),
   PARTITION p_other VALUES IN (DEFAULT)
   );

在上述示例中，我們創(chuàng)建了一個(gè)名為users的表，并使用LIST COLUMNS分區(qū)策略。根據(jù)region和category兩列的離散值將數(shù)據(jù)分布到不同的分區(qū)。每個(gè)分區(qū)根據(jù)這兩列的離散值進(jìn)行劃分。

常見分區(qū)命令

是否支持分區(qū)

在 MySQL5.6.1 之前可以通過命令 show variables like '%have_partitioning%' 來查看 MySQL 是否支持分區(qū)。如果 have_partitioning 的值為 YES，則表示支持分區(qū)。

從 MySQL5.6.1 開始，have_partitioning 參數(shù)已經(jīng)被去掉了，而是用 SHOW PLUGINS 來代替。若有 partition 行且 STATUS 列的值為 ACTIVE，則表示支持分區(qū)，如下所示：

創(chuàng)建分區(qū)表

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sales_date)) (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

向分區(qū)表添加新的分區(qū)

ALTER TABLE sales ADD PARTITION (
    PARTITION p5 VALUES LESS THAN (2023)
);

刪除指定的分區(qū)

ALTER TABLE sales DROP PARTITION p3;

重新組織分區(qū)

ALTER TABLE sales REORGANIZE PARTITION p1, p2, p5 INTO (
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

合并相鄰的分區(qū)：

ALTER TABLE sales COALESCE PARTITION p1, p2;

分析指定分區(qū)的統(tǒng)計(jì)信息：

ALTER TABLE sales ANALYZE PARTITION p1;

MySQL分區(qū)表的正確使用方法

MySQL分區(qū)表概述

我們經(jīng)常遇到一張表里面保存了上億甚至過十億的記錄，這些表里面保存了大量的歷史記錄。對于這些歷史數(shù)據(jù)的清理是一個(gè)非常頭疼事情，由于所有的數(shù)據(jù)都一個(gè)普通的表里。所以只能是啟用一個(gè)或多個(gè)帶where條件的delete語句去刪除（一般where條件是時(shí)間）。這對數(shù)據(jù)庫的造成了很大壓力。即使我們把這些刪除了，但底層的數(shù)據(jù)文件并沒有變小。面對這類問題，最有效的方法就是在使用分區(qū)表。最常見的分區(qū)方法就是按照時(shí)間進(jìn)行分區(qū)。

分區(qū)一個(gè)最大的優(yōu)點(diǎn)就是可以非常高效的進(jìn)行歷史數(shù)據(jù)的清理。

1. 確認(rèn)MySQL服務(wù)器是否支持分區(qū)表

命令：
show plugins; |

2. MySQL分區(qū)表的特點(diǎn)

在邏輯上為一個(gè)表，在物理上存儲在多個(gè)文件中

HASH分區(qū)（HASH）

HASH分區(qū)的特點(diǎn)

根據(jù)MOD(分區(qū)鍵，分區(qū)數(shù))的值把數(shù)據(jù)行存儲到表的不同分區(qū)中
數(shù)據(jù)可以平均的分布在各個(gè)分區(qū)中
HASH分區(qū)的鍵值必須是一個(gè)INT類型的值，或是通過函數(shù)可以轉(zhuǎn)為INT類型

如何建立HASH分區(qū)表

以INT類型字段 customer_id為分區(qū)鍵

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
 `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用戶登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶登錄日志表'
 
PARTITION BY HASH(customer_id) PARTITIONS 4;

以非INT類型字段 login_time 為分區(qū)鍵(需要先轉(zhuǎn)換成INT類型)

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
 `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用戶登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類型:0未成功 1成功'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用戶登錄日志表'
 
PARTITION BY HASH(UNIX_TIMESTAMP(login_time)) PARTITIONS 4;

customer_login_log 表如果不分區(qū)，在物理磁盤上文件為

customer_login_log.frm # 存儲表原數(shù)據(jù)信息
customer_login_log.ibd # Innodb數(shù)據(jù)文件

如果按上面的建HASH分區(qū)表，則有五個(gè)文件

customer_login_log.frm 
customer_login_log#P#p0.ibd
customer_login_log#P#p1.ibd
customer_login_log#P#p2.ibd
customer_login_log#P#p3.ibd

演示

使用起來和不分區(qū)是一樣的，看起來只有一個(gè)數(shù)據(jù)庫，其實(shí)有多個(gè)分區(qū)文件，比如我們要插入一條數(shù)據(jù)，不需要指定分區(qū)，MySQL會自動幫我們處理

查詢

范圍分區(qū)（RANGE）

RANGE分區(qū)特點(diǎn)

根據(jù)分區(qū)鍵值的范圍把數(shù)據(jù)行存儲到表的不同分區(qū)中
多個(gè)分區(qū)的范圍要連續(xù)，但是不能重疊
默認(rèn)情況下使用VALUES LESS THAN屬性，即每個(gè)分區(qū)不包括指定的那個(gè)值

如何建立RANGE分區(qū)

如果沒有定義p3分區(qū)，當(dāng)插入的customer_id大于29999時(shí)會報(bào)錯(cuò)，定義了則超過的數(shù)據(jù)都存入p3中

RANGE分區(qū)的適用場景

分區(qū)鍵為日期或是時(shí)間類型（可以使得各個(gè)分區(qū)表的數(shù)據(jù)比較均衡，如果按上面的例子中以整型id為分區(qū)鍵，假如活躍用戶集中在10000-19999之間，則p1中的數(shù)據(jù)量就會比其他分區(qū)的數(shù)據(jù)量大很多，這就失去了分區(qū)的意義；而且按時(shí)間類型分區(qū)，如果要按時(shí)間順序進(jìn)行數(shù)據(jù)的歸檔，則只需要對某一個(gè)分區(qū)進(jìn)行歸檔就可以了）
所有查詢中都包括分區(qū)鍵（避免跨分區(qū)查詢）
定期按分區(qū)范圍清理歷史數(shù)據(jù)

LIST分區(qū)

LIST分區(qū)的特點(diǎn)

按分區(qū)鍵取值的列表進(jìn)行分區(qū)
同范圍分區(qū)一樣，各分區(qū)的列表值不能重復(fù)
每一行數(shù)據(jù)必須能找到對應(yīng)的分區(qū)列表，否則數(shù)據(jù)插入失敗

如何建立LIST分區(qū)

如果插入一條login_type為10的數(shù)據(jù)行，則會報(bào)錯(cuò)

3. 如何為登錄日志表（customer_login_log）分區(qū)

業(yè)務(wù)場景

用戶每次登錄都會記錄customer_login_log日志
用戶登錄日志保存一年，1年后可以刪除或者歸檔

登錄日志表的分區(qū)類型及分區(qū)鍵

使用RANGE分區(qū)
以login_time為分區(qū)鍵

分區(qū)后的用戶登錄日志表

按年份分區(qū)存儲，所以用YEAR函數(shù)進(jìn)行了轉(zhuǎn)化

CREATE TABLE `customer_login_log` (
 `customer_id` int(10) unsigned NOT NULL COMMENT '登錄用戶ID',
 `login_time` DATETIME NOT NULL COMMENT '用戶登錄時(shí)間',
 `login_ip` int(10) unsigned NOT NULL COMMENT '登錄IP',
 `login_type` tinyint(4) NOT NULL COMMENT '登錄類型:0未成功 1成功'
) ENGINE=InnoDB 
PARTITION BY RANGE (YEAR(login_time))(
PARTITION p0 VALUES LESS THAN (2017),
PARTITION p1 VALUES LESS THAN (2018),
PARTITION p2 VALUES LESS THAN (2019)
)

插入并查詢數(shù)據(jù)

查詢指定表中的分區(qū)數(shù)據(jù)情況

SELECT table_name,partition_name,partition_description,table_rows FROM
information_schema.`PARTITIONS` WHERE table_name = 'customer_login_log';

再插入2條18年的日志，會存入p2表中

之前說過建立分區(qū)表時(shí)，最好建立一個(gè)MAXVALUE的分區(qū)，這里之所以沒有建立，是為了數(shù)據(jù)維護(hù)的方便，如果我們建立了MAXVALUE分區(qū)，很容易忽視一個(gè)問題，當(dāng)我們2019年有的數(shù)據(jù)插入時(shí)，會自動存入那個(gè)MAXVALUE分區(qū)中，之后在做數(shù)據(jù)維護(hù)時(shí)會不方便，所以沒有建立MAXVALUE分區(qū)

而是通過計(jì)劃任務(wù)的方式，在每年年底的時(shí)候增加這個(gè)分區(qū)，比如我們現(xiàn)在在2018年年底，我們需要在日志表中為2019年建立日志分區(qū)，否則2019年的日志都會插入失敗

我們可以通過下面語句

增加分區(qū)

ALTER TABLE customer_login_log ADD PARTITION (PARTITION p3 VALUES LESS THAN(2020))

增加分區(qū)，并插入數(shù)據(jù)

刪除分區(qū)

假如我們現(xiàn)在要刪除2016年到2017年間一年的數(shù)據(jù)，因?yàn)槲覀円呀?jīng)做了分區(qū)，所以只需要通過一條語句，刪除p0分區(qū)即可

ALTER TABLE customer_login_log DROP PARTITION p0;

可以發(fā)現(xiàn)p0分區(qū)已被刪除，且2016年的日志全部被清除了

歸檔分區(qū)歷史數(shù)據(jù)

我們可能有另一種需求對數(shù)據(jù)進(jìn)行歸檔

Mysql版本>=5.7，歸檔分區(qū)歷史數(shù)據(jù)非常方便，提供了一個(gè)交換分區(qū)的方法

分區(qū)數(shù)據(jù)歸檔遷移條件：

MySQL>=5.7
結(jié)構(gòu)相同
歸檔到的數(shù)據(jù)表一定要是非分區(qū)表
非臨時(shí)表；不能有外鍵約束
歸檔引擎要是：archive

建表并交換分區(qū)

CREATE TABLE `arch_customer_login_log` (
 `customer_id` INT unsigned NOT NULL COMMENT '登錄用戶ID',
 `login_time` DATETIME NOT NULL COMMENT '用戶登錄時(shí)間',
 `login_ip` INT unsigned NOT NULL COMMENT '登錄IP',
 `login_type` TINYINT NOT NULL COMMENT '登錄類型:0未成功 1成功'
) ENGINE=InnoDB ;

ALTER TABLE customer_login_log 
 exchange PARTITION p1 WITH TABLE arch_customer_login_log;

可以發(fā)現(xiàn)，原customer_login_log表中的2017年的數(shù)據(jù)（p1分區(qū)中的數(shù)據(jù)）已轉(zhuǎn)移到了arch_customer_login_log表中，但是p1分區(qū)未刪除，只是數(shù)據(jù)轉(zhuǎn)移了,所以我們還需要執(zhí)行DROP命令刪除分區(qū)，以免有數(shù)據(jù)插入其中

將歸檔數(shù)據(jù)的存儲引擎改為歸檔引擎

最后我們將歸檔數(shù)據(jù)的存儲引擎改為歸檔引擎，命令為

ALTER TABLE customer_login_log ENGINE=ARCHIVE;

使用歸檔引擎的好處是：它比Innodb所占用的空間更少，但是歸檔引擎只能進(jìn)行查詢操作，不能進(jìn)行寫操作

4. 使用分區(qū)表的主要事項(xiàng)

結(jié)合業(yè)務(wù)場景選擇分區(qū)鍵，避免跨分區(qū)查詢
對分區(qū)表進(jìn)行查詢最好在WHERE從句中包含分區(qū)鍵
具有主鍵或唯一索引的表，主鍵或唯一索引必須是分區(qū)鍵的一部分（這也是為什么我們上面分區(qū)時(shí)去掉了主鍵登錄日志id(login_id)的原因，不然就無法按照上面的按年份進(jìn)行分區(qū)，所以分區(qū)表其實(shí)更適合在MyISAM引擎中）

關(guān)于MyISAM和Innodb的索引區(qū)別

1.關(guān)于自動增長

myisam引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據(jù)前面幾列進(jìn)行排序后遞增。

innodb引擎的自動增長咧必須是索引，如果是組合索引也必須是組合索引的第一列。

2.關(guān)于主鍵

myisam允許沒有任何索引和主鍵的表存在，

myisam的索引都是保存行的地址。

innodb引擎如果沒有設(shè)定主鍵或者非空唯一索引，就會自動生成一個(gè)6字節(jié)的主鍵(用戶不可見)

innodb的數(shù)據(jù)是主索引的一部分，附加索引保存的是主索引的值。

3.關(guān)于count()函數(shù)

myisam保存有表的總行數(shù)，如果select count(*) from table;會直接取出出該值

innodb沒有保存表的總行數(shù)，如果使用select count(*) from table；就會遍歷整個(gè)表，消耗相當(dāng)大，但是在加了wehre 條件后，myisam和innodb處理的方式都一樣。

4.全文索引

myisam支持 FULLTEXT類型的全文索引

innodb不支持FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx 是一個(gè)開源軟件，提供多種語言的API接口，可以優(yōu)化mysql的各種查詢）

5.delete from table

使用這條命令時(shí)，innodb不會從新建立表，而是一條一條的刪除數(shù)據(jù)，在innodb上如果要清空保存有大量數(shù)據(jù)的表，最好不要使用這個(gè)命令。(推薦使用truncate table，不過需要用戶有drop此表的權(quán)限)

6.索引保存位置

myisam的索引以表名+.MYI文件分別保存。

innodb的索引和數(shù)據(jù)一起保存在表空間里。

到此這篇關(guān)于MySQL分區(qū)表的實(shí)現(xiàn)示例的文章就介紹到這了,更多相關(guān)MySQL分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

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

MySQL分區(qū)表的實(shí)現(xiàn)示例

目錄

MySQL建立分區(qū)的條件是

什么是MySQL分區(qū)？

建立分區(qū)的條件

分區(qū)表介紹

表分區(qū)的優(yōu)缺點(diǎn)和限制

分區(qū)適用場景

分區(qū)方式

分區(qū)策略

RANGE分區(qū)

LIST分區(qū)

HASH分區(qū)

KEY分區(qū)

COLUMNS 分區(qū)

常見分區(qū)命令

是否支持分區(qū)

創(chuàng)建分區(qū)表

向分區(qū)表添加新的分區(qū)

刪除指定的分區(qū)

重新組織分區(qū)

合并相鄰的分區(qū)：

分析指定分區(qū)的統(tǒng)計(jì)信息：

MySQL分區(qū)表的正確使用方法

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

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

MySQL分區(qū)表的實(shí)現(xiàn)示例

目錄

MySQL建立分區(qū)的條件是

什么是MySQL分區(qū)？

建立分區(qū)的條件

分區(qū)表介紹

表分區(qū)的優(yōu)缺點(diǎn)和限制

分區(qū)適用場景

分區(qū)方式

分區(qū)策略

RANGE分區(qū)

LIST分區(qū)

HASH分區(qū)

KEY分區(qū)

COLUMNS 分區(qū)

常見分區(qū)命令

是否支持分區(qū)

創(chuàng)建分區(qū)表

向分區(qū)表添加新的分區(qū)

刪除指定的分區(qū)

重新組織分區(qū)

合并相鄰的分區(qū)：

分析指定分區(qū)的統(tǒng)計(jì)信息：

MySQL分區(qū)表的正確使用方法

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

什么是MySQL分區(qū)？