MySQL中分區表的詳細介紹

這篇文章帶給大家的內容是關於MySQL中分區表的詳細介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

對使用者而言，分區表是一個獨立的邏輯表，但是在底層由多個物理子表組成。實作分區的程式碼其實是對一組底層表的句柄物件的封裝，對分區表的請求都會透過句柄物件轉換成對儲存引擎的介面呼叫

意義

MySQL在建立表格的時候可以透過使用 PARTITION BY 子句定義每個分割區存放的資料。在執行查詢的時候，優化器會根據分區定義過濾那些沒有我們需要的資料的分區，這樣查詢就可以無需掃描所有分區——只需要查找包含需要資料的分區即可。

分區的一個主要目的是 將資料依照一個較粗的粒度分別存放在不同的表中。這樣做可以將相關的資料存放在一起，另外，當我們想要一次批量刪除整個分區的資料也會變得很方便。

在以下的場景中，分區可以起到很大的作用：

• 表非常大以至於無法全部都放在記憶體中，或者只在表的最後部分有熱點資料其他皆是歷史資料

• 分區表的資料更容易維護

• 分割區表的資料可以分佈在不同的實體裝置上

• 可以使用分割表來避免某些特殊的瓶頸

• 如果需要，可以備份並回覆獨立的分割區

分區表本身也有一些限制#，以下幾點特別重要：

• 一張表最多只能有1024個分割區

• 在MySQL5.1 中，分割區運算式必須是整數，或是傳回整數的運算式。在MySQL5.5 中，某些場景可以直接使用列來進行分區

• 分區表中無法使用外鍵約束

• 如果分區欄位中有主鍵或是唯一索引的資料列，那麼所有主鍵列和唯一索引列都必須包含進來

區表的原理

儲存引擎管理分區的各個底層表和管理普通表並沒有什麼區別（所有的底層表都必須使用相同的存儲引擎）
，分區表的索引只是在各個底層表上各自加上一個完全相同的索引。從儲存引擎的角度來看，底層表和一個普通表並沒有什麼區別，儲存引擎也無需知道這是一個普通表還是一個分區表的一部分。

分區表上的操作按照下面的操作邏輯進行：

SELECT 查詢

當查詢一個分區表的時候，分區層先打開並鎖住所有的底層表，優化器先判斷是否可以過濾部分分區，然後再呼叫對應的儲存引擎介面存取各個分區的資料

INSERT 操作

#當寫入一筆記錄的時候，分區層先打開並鎖住所有的底層表，然後確定哪個分區接收這條記錄，再將記錄寫入對應底層表

DELETE 操作

當刪除一筆記錄的的時候，分區層先打開並鎖住所有的底層表，然後確定資料對應的分區，最後對對應底層表進行刪除操作

UPDATE 操作

當更新一筆記錄時，分區層先打開並且鎖住所有的底層表，MySQL先確定需要更新的記錄再哪個分區，然後取出數據並更新，再判斷更新後的數據應該放在哪個分區，最後對底層表進行寫入操作，並對原數據所在的底層表進行刪除操作。

這些操作都是支援過濾的。

雖然每個操作都會“先打開並鎖住所有的底層表”， 但這並不是說分區表在處理過程中是鎖住全表的。如果儲存引擎能夠自行實現行級鎖定，則會在分區層釋放對應表鎖定。這個加鎖和解鎖過程與普通InnoDB上的查詢類似。

分區表的類型

MySQL支援多種分區表，我們看到最多的就是根據範圍進行分區，每個分區儲存落在某個範圍內的記錄。分區表達式可以是列，也可以是包含列的表達式。

例如，如下表就將每一年的銷售額存放在不同的分區中：

CREATE TABLE sales(
    order_date DATETIME NOT NULL,
    ....
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
    PARTITION p_2010 VALUES LESS THAN (2010),
    PARTITION p_2011 VALUES LESS THAN (2011),
    PARTITION p_2012 VALUES LESS THAN (2012),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE;
)

PARTITION 分區子句中可以使用各種函數。但是有一個要求， 表達式傳回的值必須是一個確定的整數，且不能是一個常數。

MySQL也支援鍵值、雜湊和清單分割區等。

如何使用分區表

如果我們希望從一個非常大的表中查詢出一段時間的記錄，我們應該如何查詢這個表，如何才能更有效率？

因為資料量非常大，肯定不能在每次查詢的時候掃描全表，考慮到索引在空間和維護上的消耗，我們也不希望使用索引。即使真的使用索引，也會發現資料並不是按照想要的方式進行聚集，會產生大量的碎片，最終導致一個查詢產生成千上萬的隨機I/O。而事實上，當資料量超級大時，B-Tree索引就已經無法祈禱作用了。

因此我們可以選擇一些更粗粒度但消耗更少的方式來檢索數據，例如在大量的數據上只索引對應的一小塊元數據。

這正是分割區要做的事情，理解分割區可以將其當作索引的最初形態。 因為分割區不需要額外的資料結構來記錄每個分割區有哪些資料－分割區不需要精確定位每個資料的位置，也就無須額外的資料結構－所以其代價非常低。只需要一個簡單的表達式就可以表達每個分割區存放的是什麼資料。

為了確保大數據量的可擴展性，一般有以下兩個策略：

1. 全量掃描數據，不需要任何索引： 只要能夠使用WHERE 條件，將所需的資料限制在少數分區中，則效率是很高的。使用這種策略假設不用將資料完全放入記憶體中，同時也假設所需的資料全部都在磁碟上。因為內存相對較小，資料很快就會被擠出內存，所以快取起不了任何作用。這個策略適用於以正常的方式存取大量資料的時候。

2. 索引數據，並分離熱點： 如果數據有明顯的“熱點”，而且除了這部分數據，其他數據很少被訪問到，那麼可以將這部分熱點資料單獨放在一個分區中，讓這個分區的資料可以有機會都快取在記憶體中。這樣的查詢可以只存取一個很小的分區表，能夠使用索引，也能夠有效的使用快取。

什麼情況下會出問題

上面介紹的兩個分區策略都基於兩個非常重要的假設：查詢都能夠過濾掉很多額外的分區、分區本身並不會帶來很多額外的代價。

事實證明，這兩個假設在某些場景下會有問題：

• #分區列和索引列不符： 如果定義的索引列和分區列不匹配，會導致可查詢無法進行分區過濾。

• 選擇分割區的成本可能很高： 不同類型的分割區的實作方式也不同，所以它們的效能也各不相同。尤其是範圍分區，對於查詢符合條件的行在哪些分區的成本可能會非常高，因為伺服器需要掃描所有的分區定義的清單來找到正確的答案。

• 打開並鎖定所有底層表的成本可能很高： 當查詢存取分區表的時候，MySQL需要開啟並鎖定所有的底層表，這是分區表的另一個開銷。

• 維護分割區的成本可能很高： 某些分割區維護作業的速度會非常快，例如新增或刪除分割區。而有些操作，例如重組分區或類似ALTER語句的操作成本可能會很高，因為這類操作需要複製資料。

#

以上是MySQL中分區表的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！