詳解MySQL分區表

小編這次要給大家分享的是詳解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分區表的文章，如果覺得文章內容寫得不錯的話，可以把它分享出去給更多人看到。