前言

在最近的項目中，我們需要保存大量的數據，而且這些數據是有有效期的，為了提供查詢效率以及快速刪除過期數據，我們選擇了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

總結

到此這篇關于MySQL分區表基本入門教程的文章就介紹到這了,更多相關MySQL分區表內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！