mysql 表分區技術

標籤：

表分區，是指根據一定規則，將資料庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分區組成。

表分區有什麼好處：a.分區表的資料可以分布在不同的物理裝置上，從而高效地利用多個硬體裝置。 
b.和單個磁碟或者檔案系統相比，可以儲存更多資料 
c.最佳化查詢。在where語句中包含分區條件時，可以只掃描一個或多個分區表來提高查詢效率；涉及sum和count語句時，也可以在多個分區上平行處理，最後匯總結果。
d.分區表更容易維護。例如：想大量刪除大量資料可以清除整個分區。 
e.可以使用分區表來避免某些特殊的瓶頸，例如InnoDB的單個索引的互斥訪問，ext3問價你系統的inode鎖競爭等。

   今天，我通過查閱相關資料與動手操作，學習了一下資料庫表分區的技術。個人理解，其實就是當有大資料的資料表時，將資料表中的資料按照一定的規則，分門別類儲存到規定的地區空間，
   如果要對錶進行“增刪改查”的操作時，執行操作的地區不會是整張表，而是該表中的某個地區，實際就是“以空間換時間”，無疑會提高執行效率。

  分區表的限制因素

  a.一個表最多隻能有1024個分區

  b.MySQL5.1中，分區運算式必須是整數，或者返回整數的運算式。在MySQL5.5中提供了非整數運算式分區的支援。

  c.如果分區欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分區欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列。

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

  e.MySQL的分區適用於一個表的所有資料和索引，不能只對錶資料分區而不對索引分割區，也不能只對索引分割區而不對錶分區，也不能只對錶的一部分資料分區。

  如何判斷當前MySQL是否支援分區

  命令：show variables like ‘%partition%‘

    

   其中 Variable_name 的Value = 1,我測試過了，表示可以正常分區的。我資料庫的版本是：

  

    MySQL支援的分區類型有：RANGE分區，LIST分區，HASH分區，KEY分區。其中RANGE,LIST,HASH分區等一般使用Int類型，KEY分區使用BLOB，TEXT類型等。

  接下來建立資料表
 
  緊接著：
  
   以上的報錯，說明partition by 不能夠單獨的使用（stand-alone）.此處要注意！
   然後採取第二種用法，：  
  

     
    分區成功了，呵呵呵！  

 有幾點注意：
 a. 對於分區s1,表示 1 <= id < 10;對於分區s2,表示 10<= id < 20;對於分區s3，表示 20<= id < 30；對於分區s4,表示 id >= 30,無上限                                   b. 如果將less than(10) 和less than (20)的順序顛倒過來，那麼將報錯,如： VALUES LESS THAN value must be strictly increasing for each partition，
    所以也用注意順序問題                                                                                                       
 c. 一個表最大分區為：1024.(上面已經提到過)，在有限的表分區內，最後加上 ：partition xxx values less than maxvalue,是很有必要的。 
 d. 不管哪種分區類型，分區鍵必須是主鍵或唯一鍵，除非兩者都沒有，否者將會報如下錯誤。

如果是將註冊日期作為分區鍵，則須要使用日期處理函數轉換為整型，例如year(regDate),to_days(regDate),to_seconds(regDate),且只支援這三個函數。

或者使用RANGE COLUMNS分區，則不需要轉換日期，如下所示

create table users_par(    id int not null,    usrName varchar(50) not null,    usrEmail varchar(50) not null,    age int not null,    regDate date not null    partition by range columns(regDate)(    partition p0 values less than(‘2005-05-05‘),    partition p1 values less than(‘2009-09-09‘),    partition p2 values less than(‘2015-05-05‘),);

RANGE分區特別適用於刪除到期資料或者某範圍資料，只需要alter table tbl_name truncate partition partition_name即可，
比delete語句效率要高很多，還有就是經常使用分區鍵的查詢，可以提高查詢效能，因為只需掃描某些分區就OK

Error Code: 1503. A PRIMARY KEY must include all columns in the table‘s partitioning function

 接下來，我們查看，我們建立的分區，相關的文法：EXPLAIN PARTITIONS SELECT * FROM `demo`    當然,我們需要做測試，“實踐是檢驗真理的唯一標準”嘛。       文法： explain partitions sql語句，如  等等，也可以做其他的測試。 接下來，嘗試其他方式的表分區形式。  List 表分區建立：文法：desc partitions select * from table_name;同時，文法：show create table table_name;

Hash表分區建立：

Key分區建立與Hash分區類似。可以參考上面的。

子分區的建立：分區表中對每個分區再次分割，又成為複合分區。可參考：資料切分——Mysql分區表的建立及效能分析



地址：http://www.cnblogs.com/zmxmumu/p/4450857.html
分區管理補充
RANGE和LIST分區在刪除，添加，重新定義等分區管理上非常類似，如下所示。
刪除分區(alter table tbl_name drop partition partition_name)，分區被刪除後，該分區的資料一起被刪除。

mysql> alter table users_par drop partition p0;Query OK, 0 rows affected (0.24 sec)Records: 0  Duplicates: 0  Warnings: 0

添加分區(alter table tbl_name add partition)

mysql>  alter table users_par add partition (partition p0 values less than (20));ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition--這裡報錯是因為添加分區必須在原分區的最大端添加，在為LIST分區添加分區時，新分區的值列表的值不能包含任意一個現有分區中值列表中的值，否則報錯mysql> alter table student add partition (partition p2 values in (‘男‘));ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

重新定義分區(alter table tbl_name reorganize partition partition_name into)，可以將一個分區拆開成多個，反之可以合并多個成一個或多個。

mysql> alter table users_par reorganize partition p1 into (partition p0 values less than (20),partition p1 values less than(30));Query OK, 1 row affected (0.05 sec)Records: 1  Duplicates: 0  Warnings: 0

需要注意的是：RANGE和LIST分區在重新定義時，只能重新定義相鄰的分區，不可以跳過分區，並且重新定義的分區區間必須和原分區區間一致，也不可以改變分區的類型。

HASH和KEY分區的管理

減少分區數量，使用coaleace關鍵字

mysql> alter table hash_par coalesce partition 2;Query OK, 1 row affected (0.04 sec)Records: 1  Duplicates: 0  Warnings: 0

增加分區數量

mysql> alter table hash_par add partition partitions 2;Query OK, 1 row affected (0.04 sec)Records: 1  Duplicates: 0  Warnings: 0

MySQL分區有利於查詢最佳化，快速刪除到期資料，提高查詢輸送量等。

 

 

mysql 表分區技術