MySQL使用Partition功能實(shí)現(xiàn)水平分區(qū)的策略
1 回顧
上一節(jié)我們?cè)敿?xì)講解了如何對(duì)數(shù)據(jù)庫(kù)進(jìn)行分區(qū)操作，包括了 垂直拆分（Scale Up 縱向擴(kuò)展）和水平拆分（Scale Out 橫向擴(kuò)展） ，同時(shí)簡(jiǎn)要整理了水平分區(qū)的幾種策略，現(xiàn)在來(lái)回顧一下。
2 水平分區(qū)的5種策略
2.1 、Hash（哈希）
這種策略是通過(guò)對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過(guò)這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。例如我們可以建立一個(gè)對(duì)表的日期的年份進(jìn)行分區(qū)的策略，這樣每個(gè)年份都會(huì)被聚集在一個(gè)區(qū)間。
PARTITION BY HASH(YEAR(createtime)) PARTITIONS 10
2.2、 Range（范圍）?
這種策略是將數(shù)據(jù)劃分不同范圍。例如我們可以將一個(gè)千萬(wàn)級(jí)別的表通過(guò)id劃分成4個(gè)分區(qū)，每個(gè)分區(qū)大約500W的數(shù)據(jù)，超過(guò)750W后的數(shù)據(jù)統(tǒng)一放在第4個(gè)分區(qū)。
PARTITION BY RANGE(id) ( PARTITIONP0 VALUES LESS THAN(2500001), PARTITIONP1 VALUES LESS THAN(5000001), PARTITIONp2 VALUES LESS THAN(7500001), PARTITIONp3 VALUES LESS THAN MAXVALUE )
2.3、Key（鍵值）
Hash策略的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。
2.4、List（預(yù)定義列表）
這種策略允許系統(tǒng)通過(guò)定義列表的值所對(duì)應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如，我們根據(jù)崗位編碼進(jìn)行分區(qū)，不同崗位類型的編碼對(duì)應(yīng)到不同的分區(qū)去，達(dá)到分治的目的。
PARTITION BY LIST(gwcode) ( PARTITIONP0 VALUES IN (46,77,89), PARTITIONP1 VALUES IN (106,125,177), PARTITIONP2 VALUES IN (205,219,289), PARTITIONP3 VALUES IN (302,317,458,509,610) )
上述的SQL腳本，使用了列表匹配LIST函數(shù)對(duì)員工崗位編號(hào)進(jìn)行分區(qū)，共分為4個(gè)分區(qū)，行政崗位 編號(hào)為46,77,89的對(duì)應(yīng)在分區(qū)P0中，技術(shù)崗位 106,125,177類別在分區(qū)P1中，依次類推即可。
2.5、Composite（復(fù)合模式）
復(fù)合模式其實(shí)就是對(duì)上面幾種模式的組合使用，比如你在Range的基礎(chǔ)上，再進(jìn)行Hash 哈希分區(qū)。
3 測(cè)試Range策略
3.1 建立總表與分表
我們建立一個(gè)普通的用戶表 users，再建立一個(gè)分區(qū)表users_part，將80年代出生的用戶按照年份進(jìn)行了分區(qū)，如下：
3.1.1 總表語(yǔ)句
mysql> CREATE TABLE users ( "id" int(10) unsigned NOT NULL, "name" varchar(100) DEFAULT NULL, "birth" datetime ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected
3.1.2 分表語(yǔ)句
最后一行注意一下，是將89年之后出生的都?xì)w屬到第10個(gè)分區(qū)上，我們這邊模擬的都是80年代出生的用戶，實(shí)際業(yè)務(wù)中跟據(jù)具體情況進(jìn)行拆分。
mysql> create table users_part
(
"id" int(10) unsigned NOT NULL,
"name" varchar(100) DEFAULT NULL,
"birth" datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (year(birth)) (
PARTITION p0 VALUES LESS THAN (1981),
PARTITION p1 VALUES LESS THAN (1982),
PARTITION p2 VALUES LESS THAN (1983),
PARTITION p3 VALUES LESS THAN (1984),
PARTITION p4 VALUES LESS THAN (1985),
PARTITION p5 VALUES LESS THAN (1986),
PARTITION p6 VALUES LESS THAN (1987),
PARTITION p7 VALUES LESS THAN (1988),
PARTITION p8 VALUES LESS THAN (1989),17 PARTITION p9 VALUES LESS THAN MAXVALUE
);
Query OK, 0 rows affected
3.2 初始化表數(shù)據(jù)
我們可以使用函數(shù)或者存儲(chǔ)過(guò)程批量進(jìn)行數(shù)據(jù)初始化,這邊插入1000W條數(shù)據(jù)。
DROP PROCEDURE IF EXISTS init_users_part;
delimiter $ /* 設(shè)定語(yǔ)句終結(jié)符為 $*/
CREATE PROCEDURE init_users_part()
? begin
? ?DECLARE srt int default 0;
? ?while
srt < 10000000 /* 設(shè)定寫(xiě)入1000W的數(shù)據(jù) */
do
? ? insert into `users_part` values (srt, concat('username_',idx1),adddate('1980-01-01',rand() * 3650)); /*在10年的時(shí)間內(nèi)隨機(jī)取值*/
? set srt = srt + 1;
? end while;
? end $
delimiter ;
call init_users_part();
3.3 同步數(shù)據(jù)至完整表中
mysql> insert into users select * from users_part; //將1000w數(shù)據(jù)復(fù)制到未分區(qū)的完整表users 中 Query OK, 10000000 rows affected (51.59 sec) Records: 10000000 Duplicates: 0 Warnings: 0
3.4 測(cè)試執(zhí)行SQL的效率
mysql> select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (0.335 sec) mysql> select count(*) from users where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (5.187 sec)
結(jié)果比較清晰，分區(qū)表的執(zhí)行效率確實(shí)比較高，執(zhí)行時(shí)間是未分區(qū)表 1/10 都不到。
3.5 使用Explain執(zhí)行計(jì)劃分析
mysql> explain select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | users_part | p7 | ALL | NULL | NULL | NULL | NULL | 987769| 100.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select count(*) from users where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL |10000000 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
這邊關(guān)注兩個(gè)關(guān)鍵參數(shù)：一個(gè) 是partitions，users_part中是p7，說(shuō)明數(shù)據(jù)檢索在第七分區(qū)中，users表是null的，說(shuō)明是全區(qū)域掃描，無(wú)分區(qū)。
另外一個(gè)參數(shù)是rows，是預(yù)測(cè)掃描的行數(shù)，users表明顯是全表掃描。
3.6 建索引提效
因?yàn)槲覀兪褂胋irth字段進(jìn)行分區(qū)和條件查詢，所以這邊嘗試在birth字段上簡(jiǎn)歷索引進(jìn)行效率優(yōu)化。
mysql> create index idx_user on users(birth); Query OK, 0 rows affected (1 min 7.04 sec) Records: 10000000 Duplicates: 0 Warnings: 0 mysql> create index idx_user_part on users_part(birth); Query OK, 0 rows affected (1 min 1.05 sec) Records: 10000000 Duplicates: 0 Warnings: 0
創(chuàng)建索引后的數(shù)據(jù)庫(kù)文件大小列表：
2008-05-24 09:23???????????? 8,608 no_part_tab.frm 2008-05-24 09:24?????? 255,999,996 no_part_tab.MYD 2008-05-24 09:24??????? 81,611,776 no_part_tab.MYI 2008-05-24 09:25???????????????? 0 part_tab#P#p0.MYD 2008-05-24 09:26???????????? 1,024 part_tab#P#p0.MYI 2008-05-24 09:26??????? 25,550,656 part_tab#P#p1.MYD 2008-05-24 09:26???????? 8,148,992 part_tab#P#p1.MYI 2008-05-24 09:26??????? 25,620,192 part_tab#P#p10.MYD 2008-05-24 09:26???????? 8,170,496 part_tab#P#p10.MYI 2008-05-24 09:25???????????????? 0 part_tab#P#p11.MYD 2008-05-24 09:26???????????? 1,024 part_tab#P#p11.MYI 2008-05-24 09:26??????? 25,656,512 part_tab#P#p2.MYD 2008-05-24 09:26???????? 8,181,760 part_tab#P#p2.MYI 2008-05-24 09:26??????? 25,586,880 part_tab#P#p3.MYD 2008-05-24 09:26???????? 8,160,256 part_tab#P#p3.MYI 2008-05-24 09:26??????? 25,585,696 part_tab#P#p4.MYD 2008-05-24 09:26???????? 8,159,232 part_tab#P#p4.MYI 2008-05-24 09:26??????? 25,585,216 part_tab#P#p5.MYD 2008-05-24 09:26???????? 8,159,232 part_tab#P#p5.MYI 2008-05-24 09:26??????? 25,655,740 part_tab#P#p6.MYD 2008-05-24 09:26???????? 8,181,760 part_tab#P#p6.MYI 2008-05-24 09:26??????? 25,586,528 part_tab#P#p7.MYD 2008-05-24 09:26???????? 8,160,256 part_tab#P#p7.MYI 2008-05-24 09:26??????? 25,586,752 part_tab#P#p8.MYD 2008-05-24 09:26???????? 8,160,256 part_tab#P#p8.MYI 2008-05-24 09:26??????? 25,585,824 part_tab#P#p9.MYD 2008-05-24 09:26???????? 8,159,232 part_tab#P#p9.MYI 2008-05-24 09:25???????????? 8,608 part_tab.frm 2008-05-24 09:25??????????????? 68 part_tab.par
再次測(cè)試SQL性能
mysql> select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (0.171 sec) mysql> select count(*) from users where `birth` > '1986-01-01' and `birth` < '1986-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (0.583 sec)
這邊可以看到，在關(guān)鍵的字段添加索引并重啟（net stop mysql，net start mysql）之后，分區(qū)的表性能有略微提升。而未分區(qū)的全表性能提升最明顯，幾乎接近分區(qū)的效率。
3.7 跨區(qū)執(zhí)行效率分析
通過(guò)上面的分析可以看出，在單個(gè)區(qū)內(nèi)執(zhí)行，比不分區(qū)效率又很明顯的差距，這是因?yàn)榉謪^(qū)之后掃描非范圍縮小了。
那如果我們上面條件增加出生年份的范圍，讓他產(chǎn)生跨區(qū)域的情況，效果會(huì)怎么樣呢，我們測(cè)試一下。
mysql> select count(*) from users_part where `birth` > '1986-01-01' and `birth` < '1987-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (1.914 sec) mysql> select count(*) from users where `birth` > '1986-01-01' and `birth` < '1987-12-31'; +----------+ | count(*) | +----------+ | 976324 | +----------+ 1 row in set (3.871 sec)
可見(jiàn)，跨區(qū)之后性能會(huì)差一些。這邊應(yīng)該這樣理解，跨區(qū)的越多，性能越差，所以做分區(qū)設(shè)計(jì)的時(shí)候應(yīng)該意識(shí)到，避免那種頻繁的跨區(qū)情況發(fā)生，謹(jǐn)慎判斷分區(qū)邊界條件。
3.8 總結(jié)
1、分區(qū)和未分區(qū)占用文件空間大致相同 （數(shù)據(jù)和索引文件）
2、查詢語(yǔ)句中關(guān)鍵字段未建立索引字段時(shí)，分區(qū)時(shí)間遠(yuǎn)遠(yuǎn)優(yōu)于未分區(qū)時(shí)間
3、如果查詢語(yǔ)句中字段建立了索引，分區(qū)和未分區(qū)的差別縮小，但是仍然優(yōu)于未分區(qū)情況，而且隨著數(shù)據(jù)量增加，這個(gè)優(yōu)勢(shì)會(huì)更明顯。
4、對(duì)于大數(shù)據(jù)量，還是建議使用分區(qū)功能，無(wú)論他有沒(méi)有建立索引。
5、根據(jù)MySQL手冊(cè)， 增加myisam_max_sort_file_size 會(huì)增加分區(qū)性能(mysql重建索引時(shí)允許使用的臨時(shí)文件最大大小)
6、對(duì)分區(qū)進(jìn)行設(shè)計(jì)時(shí)，謹(jǐn)慎判斷分區(qū)邊界條件，避免有過(guò)度頻繁的跨區(qū)操作，否則性能不會(huì)理想。
4 分區(qū)策略詳解
4.1 、HASH（哈希）
HASH分區(qū)主要用來(lái)確保數(shù)據(jù)在預(yù)先確定數(shù)目的分區(qū)中平均分布，而在RANGE和LIST分區(qū)中，必須明確指定一個(gè)給定的列值或列值集合應(yīng)該保存在哪個(gè)分區(qū)中，
而在HASH分區(qū)中，MySQL自動(dòng)完成這些工作，
你所要做的只是基于將要被哈希的列值指定一個(gè)列值或表達(dá)式，以及指定被分區(qū)的表將要被分割成的分區(qū)數(shù)量。 示例如下：
/*Hash*/
drop table if EXISTS `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(YEAR(createtime))
PARTITIONS 4(
PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);
上面的例子，使用HASH函數(shù)對(duì)createtime日期進(jìn)行HASH運(yùn)算，并根據(jù)這個(gè)日期來(lái)分區(qū)數(shù)據(jù)，這里共分為10個(gè)分區(qū)。
建表語(yǔ)句上添加一個(gè)“PARTITION BY HASH (expr)”子句，其中“expr”是一個(gè)返回整數(shù)的表達(dá)式，它可以是字段類型為MySQL 整型的一列的名字，也可以是返回非負(fù)數(shù)的表達(dá)式。
另外，可能需要在后面再添加一個(gè)“PARTITIONS num”子句，其中num 是一個(gè)非負(fù)的整數(shù)，它表示表將要被分割成分區(qū)的數(shù)量。
每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄，并且這些目錄所在的物理磁盤分區(qū)可能也都是完全獨(dú)立的，可以提高磁盤IO吞吐量。
4.2、 RANGE（范圍）
基于屬于一個(gè)給定連續(xù)區(qū)間的列值，把多行分配給同一個(gè)分區(qū)，這些區(qū)間要連續(xù)且不能相互重疊，使用VALUES LESS THAN操作符來(lái)進(jìn)行定義。示例如下：
/*Range*/ drop table if EXISTS `t_userinfo`; CREATE TABLE `t_userinfo` ( `id` int(10) unsigned NOT NULL, `personcode` varchar(20) DEFAULT NULL, `personname` varchar(100) DEFAULT NULL, `depcode` varchar(100) DEFAULT NULL, `depname` varchar(500) DEFAULT NULL, `gwcode` int(11) DEFAULT NULL, `gwname` varchar(200) DEFAULT NULL, `gravalue` varchar(20) DEFAULT NULL, `createtime` DateTime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE(gwcode) ( PARTITION P0 VALUES LESS THAN(101) DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx', PARTITION P1 VALUES LESS THAN(201) DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx', PARTITION P2 VALUES LESS THAN(301) DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx', PARTITION P3 VALUES LESS THAN MAXVALUE DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx' );
上面的示例，使用了范圍RANGE函數(shù)對(duì)崗位編號(hào)進(jìn)行分區(qū)，共分為4個(gè)分區(qū)，
崗位編號(hào)為1~100 的對(duì)應(yīng)在分區(qū)P0中，101~200的編號(hào)在分區(qū)P1中，依次類推即可。那么類別編號(hào)大于300，可以使用MAXVALUE來(lái)將大于300的數(shù)據(jù)統(tǒng)一存放在分區(qū)P3中即可。
每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄，并且這些目錄所在的物理磁盤分區(qū)可能也都是完全獨(dú)立的，可以提高磁盤IO吞吐量。
4.3 、LIST（預(yù)定義列表）
類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個(gè)離散值集合中的某個(gè)值來(lái)進(jìn)行選擇分區(qū)的。LIST分區(qū)通過(guò)使用“PARTITION BY LIST(expr)”來(lái)實(shí)現(xiàn)，其中“expr” 是某列值或一個(gè)基于某個(gè)列值、并返回一個(gè)整數(shù)值的表達(dá)式，
然后通過(guò)“VALUES IN (value_list)”的方式來(lái)定義每個(gè)分區(qū)，其中“value_list”是一個(gè)通過(guò)逗號(hào)分隔的整數(shù)列表。 示例如下：
/*List*/ drop table if EXISTS `t_userinfo`; CREATE TABLE `t_userinfo` ( `id` int(10) unsigned NOT NULL, `personcode` varchar(20) DEFAULT NULL, `personname` varchar(100) DEFAULT NULL, `depcode` varchar(100) DEFAULT NULL, `depname` varchar(500) DEFAULT NULL, `gwcode` int(11) DEFAULT NULL, `gwname` varchar(200) DEFAULT NULL, `gravalue` varchar(20) DEFAULT NULL, `createtime` DateTime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY LIST(`gwcode`) ( PARTITION P0 VALUES IN (46,77,89) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx', PARTITION P1 VALUES IN (106,125,177) DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx', PARTITION P2 VALUES IN (205,219,289) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx', PARTITION P3 VALUES IN (302,317,458,509,610) DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx' );
上面的例子，使用了列表匹配LIST函數(shù)對(duì)員工崗位編號(hào)進(jìn)行分區(qū)，共分為4個(gè)分區(qū)，編號(hào)為46,77,89的對(duì)應(yīng)在分區(qū)P0中，106,125,177類別在分區(qū)P1中，依次類推即可。
不同于RANGE的是，LIST分區(qū)的數(shù)據(jù)必須匹配列表中的崗位編號(hào)才能進(jìn)行分區(qū)，所以這種方式只是適合比較區(qū)間值確定并少量的情況。
每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄，并且這些目錄所在的物理磁盤分區(qū)可能也都是完全獨(dú)立的，可以提高磁盤IO吞吐量。
4.4 KEY（鍵值）
類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計(jì)算一列或多列，且MySQL 服務(wù)器提供其自身的哈希函數(shù)。必須有一列或多列包含整數(shù)值。 示例如下：
/*key*/
drop table if EXISTS `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY KEY(gwcode)
PARTITIONS 4(
PARTITION P0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION P1 DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx',
PARTITION P2 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data2/idx',
PARTITION P3 DATA DIRECTORY = '/data3/data' INDEX DIRECTORY = '/data3/idx'
);
注意：此種分區(qū)算法目前使用的比較少，使用服務(wù)器提供的哈希函數(shù)有不確定性，對(duì)于后期數(shù)據(jù)統(tǒng)計(jì)、整理存在會(huì)更復(fù)雜，所以我們更傾向于使用由我們定義表達(dá)式的Hash，大家知道其存在和怎么使用即可。
4.5 嵌套分區(qū)（子分區(qū)）
嵌套分區(qū)（子分區(qū)）是針對(duì) RANGE/LIST 類型的分區(qū)表中每個(gè)分區(qū)的再次分割。再次分割可以是 HASH/KEY 等類型。
drop table if EXISTS `t_userinfo`;
CREATE TABLE `t_userinfo` (
`id` int(10) unsigned NOT NULL,
`personcode` varchar(20) DEFAULT NULL,
`personname` varchar(100) DEFAULT NULL,
`depcode` varchar(100) DEFAULT NULL,
`depname` varchar(500) DEFAULT NULL,
`gwcode` int(11) DEFAULT NULL,
`gwname` varchar(200) DEFAULT NULL,
`gravalue` varchar(20) DEFAULT NULL,
`createtime` DateTime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) SUBPARTITION BY HASH (id% 4) SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (5000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data0/idx',
PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data1/data' INDEX DIRECTORY = '/data1/idx'
);
如上，對(duì)RANGE 分區(qū)再次進(jìn)行子分區(qū)劃分，子分區(qū)采用 HASH 類型。
5 分區(qū)管理
5.1 刪除分區(qū)
/*刪除分區(qū) P1*/ 2 ALERT TABLE users_part DROP PARTITION P1;
5.2 重建分區(qū)
5.2.1 RANGE 分區(qū)重建
/*這邊將原來(lái)的 P0,P1 分區(qū)合并起來(lái)，放到新的 P0 分區(qū)中，并重新設(shè)定條件為少于5000000。*/ ALTER TABLE users_part REORGANIZE PARTITION P0,P1 INTO (PARTITION P0 VALUES LESS THAN (5000000));
用于因空間過(guò)于浪費(fèi)而產(chǎn)生的合并情況。
5.2.2 LIST 分區(qū)重建
/*將原來(lái)的 P0,P1 分區(qū)合并起來(lái)，放到新的 P0 分區(qū)中,跟上一個(gè)的意思有點(diǎn)像。*/ ALTER TABLE users_part REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(1,4,5,8,9,12,13,101,555));
5.2.3 HASH/KEY 分區(qū)重建
/*用 REORGANIZE 方式重建分區(qū)的數(shù)量變成2，在這里數(shù)量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。*/ ALTER TABLE users_part REORGANIZE PARTITION COALESCE PARTITION 2;
5.3 新增分區(qū)
5.3.1 新增 RANGE 分區(qū)
/*新增一個(gè)RANGE分區(qū)*/ ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data8/idx');
5.3.2 新增 HASH/KEY 分區(qū)
/* 將分區(qū)總數(shù)擴(kuò)展到n個(gè)。n請(qǐng)用數(shù)值代替 */ ALTER TABLE users_part ADD PARTITION PARTITIONS n;
5.3.3 給已有的表加上分區(qū)
alter tableuser_part partition by RANGE (month(birth)) ( PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) , PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) , PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) , PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) , PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11), PARTITION p11 VALUES LESS THAN (12), PARTITION P12 VALUES LESS THAN (13) );
6 去除分區(qū)主鍵限制
默認(rèn)分區(qū)限制分區(qū)字段必須是主鍵（PRIMARY KEY)的一部分，需要去除此限制。
如果表中設(shè)立主鍵，會(huì)報(bào)出如下提示：A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
一種解決方式就是使用主鍵來(lái)做為分區(qū)條件：
ALTER TABLE users_part PARTITION BY HASH(id) PARTITIONS 4;
另外一種方式就是把分區(qū)條件字段加入主鍵中，變成聯(lián)合主鍵。如下，id和gwcode 組成了聯(lián)合主鍵：
alter table users_part drop PRIMARY KEY; alter table users_part add PRIMARY KEY(id, gwcode);
到此這篇關(guān)于MySQL使用Partition功能實(shí)現(xiàn)水平分區(qū)的文章就介紹到這了,更多相關(guān)mysql水平分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了幾個(gè)比較重要的MySQL變量，具有一定參考價(jià)值，需要的朋友可以了解下。2017-10-10
MySQL超詳細(xì)實(shí)現(xiàn)用戶管理實(shí)例
MySQL 是一個(gè)多用戶數(shù)據(jù)庫(kù)，具有功能強(qiáng)大的訪問(wèn)控制系統(tǒng)，可以為不同用戶指定不同權(quán)限。在前面的章節(jié)中我們使用的是 root 用戶，該用戶是超級(jí)管理員，擁有所有權(quán)限，包括創(chuàng)建用戶、刪除用戶和修改用戶密碼等管理權(quán)限2022-06-06
socket文件是一種特殊的文件,可以促進(jìn)不同進(jìn)程之間的通信,有時(shí)候系統(tǒng)或MySQL配置的更改可能導(dǎo)致MySQL無(wú)法讀取套接字文件,本文主要介紹了MySQL中的套接字錯(cuò)誤解決,感興趣的可以了解一下2024-02-02
詳解Windows?Server?2012下安裝MYSQL5.7.24的問(wèn)題
這篇文章主要介紹了Windows?Server?2012下安裝MYSQL5.7.24的詳細(xì)過(guò)程，本文通過(guò)圖文并茂實(shí)例代碼相結(jié)合給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02
這篇文章主要介紹了MySQL中的最左匹配原則,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-09-09
關(guān)于mysql中string和number的轉(zhuǎn)換問(wèn)題
這篇文章主要介紹了關(guān)于mysql中string和number的轉(zhuǎn)換問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2022-06-06
MySQL下使用Inplace和Online方式創(chuàng)建索引的教程
這篇文章主要介紹了MySQL下使用Inplace和Online方式創(chuàng)建索引的教程,針對(duì)InnoDB為存儲(chǔ)引擎的情況,需要的朋友可以參考下2015-11-11
解決mysql創(chuàng)建數(shù)據(jù)庫(kù)后出現(xiàn)：Access denied for user ''root''@''%'' to dat
這篇文章主要給大家介紹了如何解決mysql在創(chuàng)建數(shù)據(jù)庫(kù)后出現(xiàn)：Access denied for user 'root'@'%' to database 'xxx'的錯(cuò)誤提示，文中介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面來(lái)一起看看吧。2017-05-05
這篇文章主要為大家詳細(xì)介紹了MySQL5.7.21安裝與密碼圖文配置教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-02-02
Sphinx/MySQL 協(xié)議支持與SphinxQL應(yīng)用實(shí)例
Sphinx/MySQL 協(xié)議支持與SphinxQL應(yīng)用例子，供大家學(xué)習(xí)參考2013-02-02