MySQL分區表的基本入門教程

前言

在最近的項目中，我們需要保存大量的數據，而且這些數據是有有效期的，為了提供查詢效率以及快速刪除過期數據，我們選擇了MySQL的分區機制。把數據按照時間進行分區。

分區類型
 

• Range分區：最為常用，基於屬於一個給定連續區間的列值，把多行分配給分區。最常見的是基於時間字段. 基於分區的列最好是整型，如果日期型的可以使用函數轉換為整型。

• List分區：LIST分區和RANGE分區類似，區別在於LIST是枚舉值列表的集合，RANGE是連續的區間值的集合。

• Hash分區：基於給定的分區個數，將數據分配到不同的分區，HASH分區只能針對整數進行HASH，對於非整形的字段只能通過表達式將其轉換成整數。

• Key分區：KEY分區其實跟HASH分區差不多，不同點如下：

• KEY分區允許多列，而HASH分區只允許一列。

• 如果在有主鍵或者唯一鍵的情況下，key中分區列可不指定，默認為主鍵或者唯一鍵，如果沒有，則必須顯性指定列。

• KEY分區對象必須為列，而不能是基於列的表達式。

• KEY分區和HASH分區的算法不一樣，PARTITION BY HASH (expr)，MOD取值的對象是expr返回的值，而PARTITION BY KEY (column_list)，基於的是列的MD5值。
  

分區命令
 

創建分區
 

  CREATE TABLE `access_log` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `access_time` datetime NOT NULL,   PRIMARY KEY (`id`,`access_time`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8  /*!50100 PARTITION BY RANGE (to_days(access_time))  (PARTITION p1 VALUES LESS THAN (to_days(20190101)) ENGINE = InnoDB,   PARTITION p2 VALUES LESS THAN (to_days(20190102)) ENGINE = InnoDB,   PARTITION p3 VALUES LESS THAN (to_days(20190103)) ENGINE = InnoDB) */;

創建後可以看到，每個分區都會對應1個ibd文件

 分區表
 

新增分區
 

  alter table access_log add partition(    partition p4 values less than (to_days('20190105'))  );

刪除分區
 

  alter table access_log drop partition p1;

拆分分區
 

  alter table access_log reorganize partition p4 into(    -> partition s0 values less than(to_days('20190104')),    -> partition s1 values less than(to_days('20190105'))    -> );

合併分區
 

  alter table access_log reorganize partition s0,s1 into (     partition p4 values less than (to_days('20190105'))   );

注意事項
 

• MySQL分區中如果存在主鍵或唯一鍵，則分區列必須包含在其中（否則判斷主鍵或唯一時，需要掃描所有分區）

• 分區字段不能為NULL，要不然怎麼確定分區範圍呢，所以儘量NOT NULL

• 最大分區數目不能超過1024

• 不支持外鍵

• 只能對數據表的整型列進行分區，或者數據列可以通過分區函數轉化成整型列

• 分區表不影響自增列
  

常見問題
 

• A PRIMARY KEY must include all columns in the table's partitioning function：這樣的話判斷主鍵是否唯一就可以在單個分區內部完成，否則就需要跨所有的分區

• MAXVALUE can only be used in last partition definition：RANGE表分區後不能帶MAXVALUE分區，否則無法增加分區。或者就只能重新分區了
  

  alter table access_log partition by range(to_days(access_time))(  partition p1 values less than (to_days('20191202')),   partition p2 values less than (to_days('20191203')),     partition po values less than (maxvalue)  )

• Table has no partition for value 737425：因為分區的範圍沒有包含所有可能的記錄的值

參考

• MySQL ・ 最佳實踐 ・ 分區表基本類型

• 互聯網公司為啥不使用mysql分區表?

• How to Resolve ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition