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

什么是MySQL分區(qū)？

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

建立分區(qū)的條件

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

1.所需的MySQL版本：

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

2.分區(qū)字段：

必須定義一個或多個分區(qū)字段來確定如何將數(shù)據(jù)行分配到各個分區(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個。

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)入到這個目錄下，就可以看到我們定義的所有數(shù)據(jù)庫了，一個數(shù)據(jù)庫就是一個文件夾，一個庫中，有其對應(yīng)的表的信息，如下：

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

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

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

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

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

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

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

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

MySQL 從 5.1 開始添加了對分區(qū)的支持，分區(qū)的過程是將一個表或索引分解為多個更小、更可管理的部分。對于開發(fā)者而言，分區(qū)后的表使用方式和不分區(qū)基本上還是一模一樣，只不過在物理存儲上，原本該表只有一個數(shù)據(jù)文件，現(xiàn)在變成了多個，每個分區(qū)都是獨(dú)立的對象，可以獨(dú)自處理，也可以作為一個更大對象的一部分進(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ū)可以將大表劃分為更小的部分，查詢時只需掃描特定的分區(qū)，而不是整個表，從而提高查詢性能。特別是在處理大量數(shù)據(jù)或高并發(fā)負(fù)載時，分區(qū)可以顯著減少查詢的響應(yīng)時間。
• 管理和維護(hù)的簡化：使用分區(qū)可以更輕松地管理和維護(hù)數(shù)據(jù)?？梢葬槍μ囟ǖ姆謪^(qū)執(zhí)行維護(hù)操作，如備份、恢復(fù)、優(yōu)化和數(shù)據(jù)清理，而不必處理整個表。這簡化了維護(hù)任務(wù)并減少了操作的復(fù)雜性。
• 數(shù)據(jù)管理靈活性：通過分區(qū)，可以根據(jù)業(yè)務(wù)需求輕松地添加或刪除分區(qū)，而無需影響整個表。這使得數(shù)據(jù)的增長和變化更具彈性，可以根據(jù)需求進(jìn)行動態(tài)調(diào)整。
• 改善數(shù)據(jù)安全性和可用性：可以將不同分區(qū)的數(shù)據(jù)分布在不同的存儲設(shè)備上，從而提高數(shù)據(jù)的安全性和可用性。例如，可以將熱數(shù)據(jù)放在高速存儲設(shè)備上，而將冷數(shù)據(jù)放在廉價存儲設(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)致索引效率下降。由于查詢需要在多個分區(qū)之間進(jìn)行掃描，可能無法充分利用索引優(yōu)勢，從而影響查詢性能。
• 存儲空間需求增加：使用分區(qū)會導(dǎo)致一定程度的存儲空間浪費(fèi)。每個分區(qū)都需要占用一定的存儲空間，包括分區(qū)元數(shù)據(jù)和一些額外的開銷。因此，對于分區(qū)鍵的選擇和分區(qū)粒度的設(shè)置需要權(quán)衡存儲空間和性能之間的關(guān)系。
• 功能限制：在某些情況下，分區(qū)可能會限制某些MySQL的功能和特性的使用。例如，某些類型的索引可能無法在分區(qū)表上使用，或者某些DDL操作可能需要更復(fù)雜的處理。

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

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

限制：

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

分區(qū)適用場景

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

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

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

分區(qū)方式

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

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

分區(qū)策略

RANGE分區(qū)

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

定義方式：

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

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

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

以下是一個使用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ū)。每個分區(qū)包含特定的列值列表。下面是LIST分區(qū)的定義方式、特點(diǎn)以及代碼示例。
  定義方式：

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

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

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

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

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

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

以下是一個使用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ù)均勻地分布到多個分區(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ù)在各個分區(qū)之間平衡。
• 并行查詢性能：通過將數(shù)據(jù)分散到多個分區(qū)，HASH分區(qū)可以提高并行查詢的性能，多個查詢可以同時在不同分區(qū)上執(zhí)行。
• 簡化管理：HASH分區(qū)使得數(shù)據(jù)管理更加靈活，可以輕松地添加或刪除分區(qū)，以適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個使用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個分區(qū)中。

• PARTITION BY HASH (id)：指定使用HASH分區(qū)，基于id列的哈希值進(jìn)行分區(qū)。
• PARTITIONS 4：指定創(chuàng)建4個分區(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ù)分散到多個分區(qū)，KEY分區(qū)可以提高并行查詢的性能，多個查詢可以同時在不同分區(qū)上執(zhí)行。
• 簡化管理：KEY分區(qū)使得數(shù)據(jù)管理更加靈活，可以輕松地添加或刪除分區(qū)，以適應(yīng)數(shù)據(jù)增長或變更的需求。

以下是一個使用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個分區(qū)中。

• PARTITION BY KEY (customer_id)：指定使用KEY分區(qū)，基于customer_id列的哈希值進(jìn)行分區(qū)。
• PARTITIONS 5：指定創(chuàng)建5個分區(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ù)多個列的范圍值進(jìn)行分區(qū)，而不是只根據(jù)一個列。這使得范圍的定義更加靈活，可以基于多個列的組合來進(jìn)行分區(qū)。
  下面是一個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)建了一個名為sales的表，并使用RANGE COLUMNS分區(qū)策略。根據(jù)region和sales_date兩列的范圍將數(shù)據(jù)分布到不同的分區(qū)。每個分區(qū)根據(jù)這兩列的范圍值進(jìn)行劃分。

• LIST COLUMNS分區(qū)： LIST COLUMNS分區(qū)是根據(jù)列的離散值將數(shù)據(jù)分布到不同的分區(qū)的分區(qū)策略。它類似于LIST分區(qū)，但是根據(jù)多個列的離散值進(jìn)行分區(qū)，而不是只根據(jù)一個列。這使得離散值的定義更加靈活，可以基于多個列的組合來進(jìn)行分區(qū)。
  下面是一個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)建了一個名為users的表，并使用LIST COLUMNS分區(qū)策略。根據(jù)region和category兩列的離散值將數(shù)據(jù)分布到不同的分區(qū)。每個分區(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ù)的清理是一個非常頭疼事情，由于所有的數(shù)據(jù)都一個普通的表里。所以只能是啟用一個或多個帶where條件的delete語句去刪除（一般where條件是時間）。 這對數(shù)據(jù)庫的造成了很大壓力。即使我們把這些刪除了，但底層的數(shù)據(jù)文件并沒有變小。面對這類問題，最有效的方法就是在使用分區(qū)表。最常見的分區(qū)方法就是按照時間進(jìn)行分區(qū)。

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

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

命令：
show plugins; |

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

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

HASH分區(qū)（HASH）

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

• 根據(jù)MOD(分區(qū)鍵，分區(qū)數(shù))的值把數(shù)據(jù)行存儲到表的不同分區(qū)中
• 數(shù)據(jù)可以平均的分布在各個分區(qū)中
• HASH分區(qū)的鍵值必須是一個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 '用戶登錄時間',
 `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 '用戶登錄時間',
 `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ū)表，則有五個文件

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ū)是一樣的，看起來只有一個數(shù)據(jù)庫，其實(shí)有多個分區(qū)文件，比如我們要插入一條數(shù)據(jù)，不需要指定分區(qū)，MySQL會自動幫我們處理

查詢

范圍分區(qū)（RANGE）

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

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

如何建立RANGE分區(qū)

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

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

• 分區(qū)鍵為日期或是時間類型 （可以使得各個分區(qū)表的數(shù)據(jù)比較均衡，如果按上面的例子中以整型id為分區(qū)鍵，假如活躍用戶集中在10000-19999之間，則p1中的數(shù)據(jù)量就會比其他分區(qū)的數(shù)據(jù)量大很多，這就失去了分區(qū)的意義；而且按時間類型分區(qū)，如果要按時間順序進(jìn)行數(shù)據(jù)的歸檔，則只需要對某一個分區(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)錯

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 '用戶登錄時間',
 `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ū)表時，最好建立一個MAXVALUE的分區(qū)，這里之所以沒有建立，是為了數(shù)據(jù)維護(hù)的方便，如果我們建立了MAXVALUE分區(qū)，很容易忽視一個問題，當(dāng)我們2019年有的數(shù)據(jù)插入時，會自動存入那個MAXVALUE分區(qū)中，之后在做數(shù)據(jù)維護(hù)時會不方便，所以沒有建立MAXVALUE分區(qū)

而是通過計(jì)劃任務(wù)的方式，在每年年底的時候增加這個分區(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ù)非常方便，提供了一個交換分區(qū)的方法

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

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

建表并交換分區(qū)

CREATE TABLE `arch_customer_login_log` (
 `customer_id` INT unsigned NOT NULL COMMENT '登錄用戶ID',
 `login_time` DATETIME NOT NULL COMMENT '用戶登錄時間',
 `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ū)時去掉了主鍵登錄日志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è)定主鍵或者非空唯一索引，就會自動生成一個6字節(jié)的主鍵(用戶不可見)

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

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

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

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

4.全文索引

myisam支持 FULLTEXT類型的全文索引

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

5.delete from table

使用這條命令時，innodb不會從新建立表，而是一條一條的刪除數(shù)據(jù)，在innodb上如果要清空保存有大量數(shù)據(jù)的表，最 好不要使用這個命令。(推薦使用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)文章希望大家以后多多支持腳本之家！