MySQL分區(qū)表的基本入門教程
前言
在最近的項目中,我們需要保存大量的數(shù)據(jù),而且這些數(shù)據(jù)是有有效期的,為了提供查詢效率以及快速刪除過期數(shù)據(jù),我們選擇了MySQL的分區(qū)機制。把數(shù)據(jù)按照時間進行分區(qū)。
分區(qū)類型
- Range分區(qū):最為常用,基于屬于一個給定連續(xù)區(qū)間的列值,把多行分配給分區(qū)。最常見的是基于時間字段. 基于分區(qū)的列最好是整型,如果日期型的可以使用函數(shù)轉(zhuǎn)換為整型。
- List分區(qū):LIST分區(qū)和RANGE分區(qū)類似,區(qū)別在于LIST是枚舉值列表的集合,RANGE是連續(xù)的區(qū)間值的集合。
- Hash分區(qū):基于給定的分區(qū)個數(shù),將數(shù)據(jù)分配到不同的分區(qū),HASH分區(qū)只能針對整數(shù)進行HASH,對于非整形的字段只能通過表達式將其轉(zhuǎn)換成整數(shù)。
- Key分區(qū):KEY分區(qū)其實跟HASH分區(qū)差不多,不同點如下:
- KEY分區(qū)允許多列,而HASH分區(qū)只允許一列。
- 如果在有主鍵或者唯一鍵的情況下,key中分區(qū)列可不指定,默認為主鍵或者唯一鍵,如果沒有,則必須顯性指定列。
- KEY分區(qū)對象必須為列,而不能是基于列的表達式。
- KEY分區(qū)和HASH分區(qū)的算法不一樣,PARTITION BY HASH (expr),MOD取值的對象是expr返回的值,而PARTITION BY KEY (column_list),基于的是列的MD5值。
分區(qū)命令
創(chuàng)建分區(qū)
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) */;
創(chuàng)建后可以看到,每個分區(qū)都會對應1個ibd文件
分區(qū)表
新增分區(qū)
alter table access_log add partition( partition p4 values less than (to_days('20190105')) );
刪除分區(qū)
alter table access_log drop partition p1;
拆分分區(qū)
alter table access_log reorganize partition p4 into( -> partition s0 values less than(to_days('20190104')), -> partition s1 values less than(to_days('20190105')) -> );
合并分區(qū)
alter table access_log reorganize partition s0,s1 into ( partition p4 values less than (to_days('20190105')) );
注意事項
- MySQL分區(qū)中如果存在主鍵或唯一鍵,則分區(qū)列必須包含在其中(否則判斷主鍵或唯一時,需要掃描所有分區(qū))
- 分區(qū)字段不能為NULL,要不然怎么確定分區(qū)范圍呢,所以盡量NOT NULL
- 最大分區(qū)數(shù)目不能超過1024
- 不支持外鍵
- 只能對數(shù)據(jù)表的整型列進行分區(qū),或者數(shù)據(jù)列可以通過分區(qū)函數(shù)轉(zhuǎn)化成整型列
- 分區(qū)表不影響自增列
常見問題
- A PRIMARY KEY must include all columns in the table's partitioning function:這樣的話判斷主鍵是否唯一就可以在單個分區(qū)內(nèi)部完成,否則就需要跨所有的分區(qū)
- MAXVALUE can only be used in last partition definition:RANGE表分區(qū)后不能帶MAXVALUE分區(qū),否則無法增加分區(qū)。或者就只能重新分區(qū)了
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:因為分區(qū)的范圍沒有包含所有可能的記錄的值
參考
- MySQL · 最佳實踐 · 分區(qū)表基本類型
- 互聯(lián)網(wǎng)公司為啥不使用mysql分區(qū)表?
- How to Resolve ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
總結
到此這篇關于MySQL分區(qū)表基本入門教程的文章就介紹到這了,更多相關MySQL分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySql Error 1698(28000)問題的解決方法
這篇文章主要介紹了MySql Error 1698(28000)問題的解決方法,需要的朋友可以參考下2017-06-06MySQL刪除表操作實現(xiàn)(delete、truncate、drop的區(qū)別)
這篇文章主要介紹了MySQL刪除表操作實現(xiàn)(delete、truncate、drop的區(qū)別),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2019-12-12navicat 8 創(chuàng)建數(shù)據(jù)庫與創(chuàng)建用戶分配權限圖文方法
navicat是一款不錯的圖形化管理mysql的工具,大家一般都是用phpmyadmin或直接命令行操作,對于不是很熟悉命令的朋友,就可以使用navicat這個工具了,方便操作。2011-04-04