mysql分區表的原理和優缺點

標籤：

1.分區表的原理

分區表是由多個相關的底層表實現，這些底層表也是由控制代碼對象表示，所以我們也可以直接存取各個分區，儲存引擎管理分區的各個底層表和管理普通表一樣（所有的底層表都必須使用相同的儲存引擎），分區表的索引只是在各個底層表上各自加上一個相同的索引，從儲存引擎的角度來看，底層表和一個普通表沒有任何不同，儲存引擎也無須知道這是一個普通表還是一個分區表的一部分。

 

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

select查詢：

當查詢一個分區表的時候，分區層先開啟並鎖住所有的底層表，最佳化器判斷是否可以過濾部分分區，然後再調用對應的儲存引擎介面訪問各個分區的資料

insert操作：

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

delete操作：

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

update操作：

當更新一條資料時，分區層先開啟並鎖住所有的底層表，mysql先確定需要更新的記錄在哪個分區，然後取出資料並更新，再判斷更新後的資料應該放在哪個分區，然後對底層表進行寫入操作，並對原資料所在的底層表進行刪除操作

 

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

 

 

2.在下面的情境中，分區可以起到非常大的作用：

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

B：分區表的資料更容易維護，如：想大量刪除大量資料可以使用清除整個分區的方式。另外，還可以對一個獨立分區進行最佳化、檢查、修複等操作

C：分區表的資料可以分布在不同的物理裝置上，從而高效地利用多個硬體裝置

D：可以使用分區表來避免某些特殊的瓶頸，如：innodb的單個索引的互斥訪問，ext3檔案系統的inode鎖競爭等

E：如果需要，還可以備份和恢複獨立的分區，這在非常大的資料集的情境下效果非常好

  

 

3.分區本身也有一些限制：

A：一個表最多隻能有1024個分區（mysql5.6之後支援8192個分區）

B：在mysql5.1中分區運算式必須是整數，或者是返回整數的運算式，在5.5之後，某些情境可以直接使用字串列和日期類型列來進行分區（使用varchar字串類型列時，一般還是字串的日期作為分區）。

C：如果分區欄位中有主鍵或者唯一索引列，那麼所有主鍵列和唯一索引列都必須包含進來，如果表中有主鍵或唯一索引，那麼分區鍵必須是主鍵或唯一索引

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

 

 

mysql分區表的原理和優缺點