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

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

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

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

什么是MySQL分區(qū)？

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

建立分區(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ù)特點和查詢需求選擇合適的分區(qū)類型。

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

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

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

分區(qū)表介紹

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

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

在 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ù)量過大，就會導致 *.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)點：

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

缺點：

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

在考慮使用分區(qū)時，需要綜合考慮業(yè)務需求、查詢模式、數(shù)據(jù)規(guī)模和硬件資源等因素，并權(quán)衡分區(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)勢，適用于以下幾種使用場景：

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

定義方式：

指定分區(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ū)的定義方式、特點以及代碼示例。
定義方式：

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

LIST分區(qū)的特點：

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

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

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

動態(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ū)的定義方式、特點以及代碼示例。
定義方式：

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

HASH分區(qū)的特點：

數(shù)據(jù)均勻分布：HASH分區(qū)使用哈希算法將數(shù)據(jù)均勻地分布到不同的分區(qū)中，確保數(shù)據(jù)在各個分區(qū)之間平衡。
并行查詢性能：通過將數(shù)據(jù)分散到多個分區(qū)，HASH分區(qū)可以提高并行查詢的性能，多個查詢可以同時在不同分區(qū)上執(zhí)行。
簡化管理：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ū)的詳細說明：

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ù)這兩列的范圍值進行劃分。

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ù)已經(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)計信息：

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ū)方法就是按照時間進行分區(qū)。

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

1. 確認MySQL服務器是否支持分區(qū)表

命令：
show plugins; |

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

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

HASH分區(qū)（HASH）

HASH分區(qū)的特點

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

查詢

范圍分區(qū)（RANGE）

RANGE分區(qū)特點

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

如何建立RANGE分區(qū)

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

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

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

LIST分區(qū)

LIST分區(qū)的特點

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

如何建立LIST分區(qū)

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

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

業(yè)務場景

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

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

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

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

按年份分區(qū)存儲，所以用YEAR函數(shù)進行了轉(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ù)維護的方便，如果我們建立了MAXVALUE分區(qū)，很容易忽視一個問題，當我們2019年有的數(shù)據(jù)插入時，會自動存入那個MAXVALUE分區(qū)中，之后在做數(shù)據(jù)維護時會不方便，所以沒有建立MAXVALUE分區(qū)

而是通過計劃任務的方式，在每年年底的時候增加這個分區(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ù)，因為我們已經(jīng)做了分區(qū)，所以只需要通過一條語句，刪除p0分區(qū)即可

ALTER TABLE customer_login_log DROP PARTITION p0;

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

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

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

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所占用的空間更少，但是歸檔引擎只能進行查詢操作，不能進行寫操作

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

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

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

1.關于自動增長

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

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

2.關于主鍵

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

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

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

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

3.關于count()函數(shù)

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

innodb沒有保存表的總行數(shù)，如果使用select count(*) from table；就會遍歷整個表，消耗相當大，但是在加了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ù)一起保存在表空間里。

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

您可能感興趣的文章:

MySQL使用select語句查詢指定表中指定列(字段)的數(shù)據(jù)
本文介紹MySQL數(shù)據(jù)庫中執(zhí)行select查詢語句，查詢指定列的數(shù)據(jù)，即指定字段的數(shù)據(jù),需要的朋友可以參考下
2016-11-11
從零開始MySQL觸發(fā)器實戰(zhàn)攻略
想要掌握MySQL觸發(fā)器的實戰(zhàn)技巧嗎？從零開始,跟我一起揭開觸發(fā)器神秘的面紗！輕松入門,迅速提升,這個指南將讓你的MySQL技能煥然一新！一探究竟,驚喜等你發(fā)現(xiàn)！
2024-02-02
調(diào)用MySQL中數(shù)據(jù)庫元數(shù)據(jù)的方法
這篇文章主要介紹了調(diào)用MySQL中數(shù)據(jù)庫元數(shù)據(jù)的方法,文中給出了了PHP和Perl腳本下的調(diào)用示例,需要的朋友可以參考下
2015-05-05
mysql 記錄不存在時插入記錄存在則更新的實現(xiàn)方法
相信很多人都需要用到這個語句，請看下文:(在4.1以后的版本才有效)
2008-08-08
安裝mysql 8.0.17并配置遠程訪問的方法
這篇文章主要介紹了安裝mysql 8.0.17并配置遠程訪問的方法，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下
2019-10-10
CentOs7安裝部署Sonar環(huán)境的詳細過程(JDK1.8+MySql5.7+sonarqube7.8)
這篇文章主要介紹了CentOs7安裝部署Sonar環(huán)境(JDK1.8+MySql5.7+sonarqube7.8),本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2023-06-06
通過命令行導入到mysql數(shù)據(jù)庫時出現(xiàn)亂碼的解決方法
原因：含有中文字符的mysql數(shù)據(jù)庫文件，通過命令行導入到mysql數(shù)據(jù)庫中容易出現(xiàn)亂碼。
2014-07-07
MySQL數(shù)據(jù)庫手冊DATABASE操作與編碼(小白入門篇)
這篇文章主要介紹了MySQL數(shù)據(jù)庫手冊DATABASE操作與編碼的小白入門篇,有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2023-05-05
MySQL快速禁用賬戶登入及如何復制/復用賬戶密碼(最新推薦)
這篇文章主要介紹了MySQL如何快速禁用賬戶登入及如何復制/復用賬戶密碼,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下
2024-01-01
mac下重置mysl8.0.11密碼的方法
mac下安裝mysql8.0.11時要求輸入密碼之后想修改密碼。接下來通過本文給大家介紹mac下重置mysl8.0.11密碼的方法,需要的朋友可以參考下
2018-06-06

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

目錄

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

什么是MySQL分區(qū)？

建立分區(qū)的條件

分區(qū)表介紹

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

分區(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)計信息：

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

相關文章

最新評論

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

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

常用在線小工具

亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

目錄

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

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

建立分區(qū)的條件

分區(qū)表介紹

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

分區(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)計信息：

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

相關文章

最新評論

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

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

常用在線小工具

什么是MySQL分區(qū)？