1.了解

分區是把一個表分成若干個部分，就是分區，分而化之，表明上還是一張表，內容存放在不同的文件了；

數據存放在不同的分區的依據就是分區鍵；

3.優勢：存儲量大 查詢快 查詢大 方便刪除?

存儲量：和單個磁盤或者文件系統分區相比，可以存儲更多數據;

優化查詢：在where子句中包含分區條件時，可以只掃描必要的一個或者多個分區來提高查詢效率；同時在涉及sum()和count()這類聚合函數的查詢時，可以容易的在每個分區上并行處理，最終只需要匯總所有分區得到的結果

方便刪除：對于已經過期或者不需要保存的數據，可以通過刪除與這些數據有關的分區來快速刪除數據

查詢吞吐量：跨多個磁盤來分散數據查詢，以獲得更大的查詢吞吐量

4.準備

建表填500w數據

CREATE TABLE `tbl_user_no_part` (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL,`email` varchar(20) DEFAULT NULL,`age` tinyint(4) DEFAULT NULL,`type` int(11) DEFAULT NULL,`create_time` datetime DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;-- 修改mysql默認的結束符號，默認是分號；但是在函數和存儲過程中會使用到分號導致解析不正確 delimiter $$ -- 隨機生成一個指定長度的字符串 create function rand_string(n int) returns varchar(255) begin # 定義三個變量declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n do set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));set i = i + 1;end while;return return_str; end $$-- 創建插入的存儲過程 create procedure insert_user(in start int(10), in max_num int(10)) begindeclare i int default 0; set autocommit = 0; repeatset i = i + 1;insert into tbl_user_no_part values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());until i = max_numend repeat;commit; end $$-- 將命令結束符修改回來 delimiter ;-- 調用存儲過程，插入500萬數據，需要等待一會時間，等待執行完成 call insert_user(100001,5000000);select count(*) from tbl_user_no_part;

5.查看版本可行性

-- 查看mysql版本 select version();-- 查看分區插件是否激活 partition active show plugins;對于低版本的MySQL，如果InnoDB引擎要想分區成功，需要在my.conf中設置innodb_file_per_table=1 設置成獨立表空間 獨立表空間：每張表都有對應的.ibd文件 innodb_file_per_table=1

6.range分區：分區鍵是范圍 范圍分配

6.1定義

-- 語法 create table <table> ( ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 partition by range (分區字段) (partition <分區名稱> values less than (Value),partition <分區名稱> values less than (Value),...partition <分區名稱> values less than maxvalue );range：表示按范圍分區 分區字段：表示要按照哪個字段進行分區，可以是一個字段名，也可以是對某個字段進行 表達式運算如year(create_time)，使用range最終的值必須是數字 分區名稱: 要保證不同，也可以采用 p0、p1、p2 這樣的分區名稱， less than : 表示小于 Value : 表示要小于某個具體的值，如 less than (10) 那么分區字段的值小于10的 都會被分到這個分區 maxvalue: 表示一個最大的值注意：range 對應的分區鍵值必須是數字值，可以使用range columns(分區字段) 對非int型做分區， 如字符串，對于日期類型的可以使用year()、to_days()、to_seconds()等函數 create table emp_date(id int not null,separated date not null default '9999-12-31' ) partition by range columns(separated) (partiontion p0 values less than ('1990-01-01'),partiontion p0 values less than ('2001-01-01'),partiontion p0 values less than ('2018-01-01') );分區可以在創建表的時候進行分區，也可以在創建表之后進行分區 alter table <table> partition by RANGE(id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN (2000000),PARTITION p2 VALUES LESS THAN (3000000),PARTITION p3 VALUES LESS THAN (4000000),PARTITION p4 VALUES LESS THAN MAXVALUE );

6.2案例

-- 創建分區表 CREATE TABLE `tbl_user_part` (`id` int(11) NOT NULL ,`username` varchar(255) DEFAULT NULL,`email` varchar(20) DEFAULT NULL,`age` tinyint(4) DEFAULT NULL,`type` int(11) DEFAULT NULL,`create_time` datetime DEFAULT CURRENT_TIMESTAMPPRIMARY KEY (`id`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY RANGE (age) (PARTITION p0 VALUES LESS THAN (20),PARTITION p1 VALUES LESS THAN (40),PARTITION p2 VALUES LESS THAN (60),PARTITION p3 VALUES LESS THAN (80),PARTITION p4 VALUES LESS THAN MAXVALUE );

?6.3.錯誤

A PRIMARY KEY must include all columns in the table’s partitioning function。

意思是說分區的字段必須是要包含在主鍵當中。 可以使用PRIMARY KEY (id,xxx)來將多個字段作為主鍵。

在做分區表時，選擇分區的依據字段時要謹慎，需要仔細斟酌這個字段拿來做為分區依據是否合適，

這個字段加入到主鍵中做為復合主鍵是否適合。

6.4.對比

7.list :使用in 固定分配

-- 語法 create table <table> (// 字段 ) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 partition by LIST (分區字段或者基于該字段的返回的整數值的表達式) (partition <分區名稱> values IN (Value1，Value2, Value3),...partition <分區名稱> values IN (Value4, Value5), );

8.columns

columns分區分為 range columns 和 list columns 兩種

支持整數(tinyint到bigint,不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三類

columns分區支持一個或者多個字段作為分區鍵，不支持表達式作為分區鍵，這點區別于range 和 list 分區。

多列排序，先根據a字段排序再根據b字段排序，根據排序結果來分區存放數據

create talbe rc3 (a int,b int ) partition by range columns(a, b) (partition p01 values less than (0, 10),partition p02 values less than (10, 10),partition p03 values less than (10, 20),partition p04 values less than (10, 35),partition p05 values less than (10, maxvalue),partition p06 values less than (maxvalue, maxvalue), ); insert into rc3(a, b) values(1, 10); select (1, 10) < (10, 10) from dual; -- 根據結果存放到p02分區上了 1小于10 10等于10 在02規定的范圍內 selectpartition_name,partition_expression,partition_description,table_rows from information_schema.partitions where table_schema = schema() and table_name = 'rc3';

9.hash：散列函數

9.1定義

分區鍵應用一個散列函數，以此確定數據應當放在N個分區中的哪個分區

mysql支持兩種hash分區：常規hash分區和線性hash分區

• 常規hash分區使用的是取模算法，對應一個表達式expr是可以計算出它被保存到哪個分區中，N = MOD(expr, num)
• 線性hash分區使用的是一個線性的2的冪運算法則。

對指定的字段(整型字段)進行哈希，將記錄平均的分配到分區中，使得所有分區的數據比較平均。 hash分區只需要指定要分區的字段和要分成幾個分區,
expr是一個字段值或者基于某列值云散返回的一個整數，

expr可以是mysql中有效的任何函數或者其它表達式，只要它們返回一個即非常熟也非隨機數的整數。num 表示分區數量

-- HASH create table <table> (// 字段 ) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY HASH(expr) PARTITIONS <num>;

9.2常規hash問題：新增的話 會導致重新計算

常規hash分區方式看上去挺不錯的，通過取模的方式來數據盡可能平均分布在每個分區，讓每個分區管理的數據都減少，提高查詢效率，可是當我們要增加分區時或者合并分區，問題就來了，假設原來是5個常規hash分區，現在需要增加一個常規分區，原來的取模算法是MOD(expr, 5), 根據余數0~4分布在5個分區中，現在新增一個分區后，取模算法變成MOD(expr, 6),根據余數0~6分區在6個分區中，原來5個分區的數據大部分都需要通過重新計算進行重新分區。

9.3?線性hash分區

常規hash分區在管理上帶來了的代價太大，不適合需要靈活變動分區的需求。為了降低分區管理上的代價，mysql提供了線性hash分區，分區函數是一個線性的2的冪的運算法則。同樣線性hash分區的記錄被存在那個分區也是能被計算出來的。線性hash分區的優點是在分區維護(增加、刪除、合并、拆分分區)時，mysql能夠處理的更加迅速，缺點是：對比常規hash分區，線性hash各個分區之間數據的分布不太均衡。

-- LINEAR HASH create table <table> (// 字段 ) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY LINEAR HASH(expr) PARTITIONS <num>;

10.key

照key進行分區非常類似于按照hash進行分區?分區的字段可以是非int類型,如字符串、日期等類型

partition by key(expr) partitions num;-- 不指定默認首選主鍵作為分區鍵，在沒有主鍵的情況下會選擇非空唯一鍵作為分區鍵 partition by key() partitions num;-- linear key partition by linear key(expr)create table <table> (// 字段 ) ENGINE=數據庫引擎 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 PARTITION BY HASH(分區字段名) PARTITIONS <count>;

11.復合分區

是分區表中對每個分區的再次分割，支持對range和list進行父分區，復合分區即可以使用hash分區也可以使用key分區進行子分區。

復合分區適用于保存非常大量的數據記錄。

-- 根據年進行分區 -- 再根據天數分區 -- 3個range分區(p0,p1,p2)又被進一步分成2個子分區，實際上整個分區被分成了 3 x 2 = 6個分區 create table ts (id int, purchased date ) partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2 (partition p0 values less than (1990),partition p0 values less than (2000),partition p0 values less than maxvalue );CREATE TABLE IF NOT EXISTS `sub_part` (`news_id` int(11) NOT NULL COMMENT '新聞ID',`content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新聞內容',`u_id` int(11) NOT NULL DEFAULT 0s COMMENT '來源IP',`create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '時間' ) ENGINE=INNODB DEFAULT CHARSET=utf8 PARTITION BY RANGE(YEAR(create_time)) SUBPARTITION BY HASH(TO_DAYS(create_time)) ( PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2), PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION good), PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, SUBPARTITION tank1, SUBPARTITION tank3) );

12.管理分區

mysql提供了添加、刪除、重定義、合并、拆分分區的命令，這些操作都可以通過alter table 命令來實現

-- 刪除list或者range分區(同時刪除分區對應的數據) alter table <table> drop partition <分區名稱>;-- 新增分區 -- range添加新分區 alter table <table> add partition(partition p4 values less than MAXVALUE);-- list添加新分區 alter table <table> add partition(partition p4 values in (25,26,28));-- hash重新分區 alter table <table> add partition partitions 4;-- key重新分區 alter table <table> add partition partitions 4;-- 子分區添加新分區，雖然我沒有指定子分區，但是系統會給子分區命名的 alter table <table> add partition(partition p3 values less than MAXVALUE);-- range重新分區 ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);-- list重新分區 ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

?

總結

以上是生活随笔為你收集整理的MySQL分区：range(范围)list(in)columns(多字段)hash(散列)key(非数值型的hash)复合(hash key)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。