分區(qū)表介紹

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

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

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

1. *.frm：這個是表定義，是描述表結構的文件。
2. *.myd：這個是數(shù)據(jù)信息文件，是表的數(shù)據(jù)文件。
3. *.myi：這個是索引信息文件。

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

1. *.frm：表結構文件。
2. *.ibd：表數(shù)據(jù)和索引的文件。

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

為了解決這個問題，我們可以利用 MySQL 的分區(qū)功能，在物理上將這一張表對應的文件，分割成許多小塊，如此，當我們查找一條數(shù)據(jù)時，就不用在某一個文件中進行整個遍歷了，我們只需要知道這條數(shù)據(jù)位于哪一個數(shù)據(jù)塊，然后在那一個數(shù)據(jù)塊上查找就行了；

另一方面，如果一張表的數(shù)據(jù)量太大，可能一個磁盤放不下，這個時候，通過表分區(qū)我們就可以把數(shù)據(jù)分配到不同的磁盤里面去。

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

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

MySQL 從 5.1 開始添加了對分區(qū)的支持，分區(qū)的過程是將一個表或索引分解為多個更小、更可管理的部分。

對于開發(fā)者而言，分區(qū)后的表使用方式和不分區(qū)基本上還是一模一樣，只不過在物理存儲上，原本該表只有一個數(shù)據(jù)文件，現(xiàn)在變成了多個，每個分區(qū)都是獨立的對象，可以獨自處理，也可以作為一個更大對象的一部分進行處理。

需要注意的是，分區(qū)功能并不是在存儲引擎層完成的，常見的存儲引擎如 InnoDB、MyISAM、NDB 等都支持分區(qū)。

但并不是所有的存儲引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分區(qū)，因此在使用此分區(qū)功能前，應該對選擇的存儲引擎對分區(qū)的支持有所了解。

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

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

優(yōu)點：

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

缺點：

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

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

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

限制：

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

分區(qū)適用場景

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

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

定義方式：

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

RANGE分區(qū)的特點：

• 范圍劃分：根據(jù)指定列的范圍進行分區(qū)，適用于需要按范圍進行查詢和管理的情況。
• 靈活的范圍定義：可以定義任意數(shù)量的分區(qū)，并且每個分區(qū)可以具有不同的范圍。
• 高效查詢：根據(jù)查詢條件的范圍，MySQL能夠快速定位到特定的分區(qū)，提高查詢效率。
• 動態(tài)管理：可以根據(jù)業(yè)務需求輕松添加或刪除分區(qū)，適應數(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列的年份進行分區(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ū)中，適用于按范圍進行查詢和管理的情況。它提供了更靈活的數(shù)據(jù)管理和查詢效率的提升。

LIST分區(qū)

• LIST分區(qū)是根據(jù)某一列的離散值將數(shù)據(jù)分布到不同的分區(qū)。每個分區(qū)包含特定的列值列表。

下面是LIST分區(qū)的定義方式、特點以及代碼示例。

定義方式：

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

LIST分區(qū)的特點：

1. 列值離散：根據(jù)指定列的具體取值進行分區(qū)，適用于具有離散值的列。
2. 靈活的分區(qū)定義：可以定義任意數(shù)量的分區(qū)，并且每個分區(qū)可以具有不同的列值列表。
3. 高效查詢：根據(jù)查詢條件的列值直接定位到特定分區(qū)，提高查詢效率。
4. 動態(tài)管理：可以根據(jù)業(yè)務需求輕松添加或刪除分區(qū)，適應數(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列的值進行分區(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ū)的定義方式、特點以及代碼示例。

定義方式：

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

HASH分區(qū)的特點：

1. 數(shù)據(jù)均勻分布：HASH分區(qū)使用哈希算法將數(shù)據(jù)均勻地分布到不同的分區(qū)中，確保數(shù)據(jù)在各個分區(qū)之間平衡。
2. 并行查詢性能：通過將數(shù)據(jù)分散到多個分區(qū)，HASH分區(qū)可以提高并行查詢的性能，多個查詢可以同時在不同分區(qū)上執(zhí)行。
3. 簡化管理：HASH分區(qū)使得數(shù)據(jù)管理更加靈活，可以輕松地添加或刪除分區(qū)，以適應數(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列的哈希值進行分區(qū)。
• PARTITIONS 4：指定創(chuàng)建4個分區(qū)。

KEY分區(qū)

• KEY分區(qū)是根據(jù)某一列的哈希值將數(shù)據(jù)分布到不同的分區(qū)。不同于HASH分區(qū)，KEY分區(qū)使用的是列值的哈希值而不是哈希函數(shù)。

下面是KEY分區(qū)的定義方式、特點以及代碼示例。

定義方式：

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

KEY分區(qū)的特點：

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

COLUMNS 分區(qū)

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

1.RANGE COLUMNS分區(qū)： RANGE COLUMNS分區(qū)是根據(jù)列的范圍值將數(shù)據(jù)分布到不同的分區(qū)的分區(qū)策略。

它類似于RANGE分區(qū)，但是根據(jù)多個列的范圍值進行分區(qū)，而不是只根據(jù)一個列。這使得范圍的定義更加靈活，可以基于多個列的組合來進行分區(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ù)這兩列的范圍值進行劃分。

2.LIST COLUMNS分區(qū)： LIST COLUMNS分區(qū)是根據(jù)列的離散值將數(shù)據(jù)分布到不同的分區(qū)的分區(qū)策略。

它類似于LIST分區(qū)，但是根據(jù)多個列的離散值進行分區(qū)，而不是只根據(jù)一個列。這使得離散值的定義更加靈活，可以基于多個列的組合來進行分區(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ù)這兩列的離散值進行劃分。

常見分區(qū)命令

是否支持分區(qū)

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

從 MySQL5.6.1 開始，have_partitioning 參數(shù)已經被去掉了，而是用 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)計信息：

ALTER TABLE sales ANALYZE PARTITION p1;

到此這篇關于MySQL分區(qū)表分區(qū)策略詳解的文章就介紹到這了,更多相關MySQL分區(qū)表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論