一、背景
成都創(chuàng)新互聯(lián)從2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目網(wǎng)站制作、做網(wǎng)站網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元和平做網(wǎng)站,已為上家服務(wù),為和平各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792
話說(shuō)風(fēng)和日麗的一天，為提高隨著業(yè)務(wù)增長(zhǎng)的大表（3510449行吧）的訪問(wèn)效率，于是決定對(duì)表分區(qū)，記錄如下。
二、實(shí)操
結(jié)合業(yè)務(wù)，若干條記錄會(huì)集中在一個(gè)日期，查詢時(shí)也往往只查詢一個(gè)日期內(nèi)的數(shù)據(jù)，于是選取分區(qū)字段為時(shí)間。
創(chuàng)建分區(qū) 比如
CREATE TABLE message_all (
id int(10) NOT NULL AUTO_INCREMENT,
......
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間'
PRIMARY KEY ( id , createtime )
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(createtime))
(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
不過(guò)我們表已經(jīng)有了當(dāng)然不能這么建，除非你想導(dǎo)一次數(shù)據(jù)。
如下操作 ：
1、
ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))
(
PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),
PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),
PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),
PARTITION p2018 VALUES LESS THAN MAXVALUE
);
或者
2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))
(
PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))
);
然后追加。
ALTER TABLE message_all ADD PARTITION
(
PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),
PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),
PARTITION p2018 VALUES LESS THAN MAXVALUE
);
這里會(huì)有幾種錯(cuò)誤情況：
1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ;
[Err] 1492 - For RANGE partitions each partition must be defined
解釋：必須指定至少一個(gè)分區(qū)。
2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function
解釋：分區(qū)字段必須是主鍵之一。
3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
解釋：分區(qū)字段為timestamp，換成datetime。
4、[Err] 1526 - Table has no partition for value xxxx
解釋：用追加方式第一次必須覆蓋目前所有數(shù)據(jù)。
總結(jié)：
1、創(chuàng)建時(shí)必須指定至少一個(gè)分區(qū)。
2、key必須為主鍵之一。
3、RANGE處必須為INT型，時(shí)間字段用函數(shù)轉(zhuǎn)——YEAR()、YEARWEEK()、TO_DAYS()。
4、THAN處必須為INT型，時(shí)間字段用函數(shù)轉(zhuǎn)——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。
5、它就是以兩個(gè)INT比大小劃分的文件。
6、所有ENGINE必須一樣。
7、范圍分區(qū)添加只能在最大值后面追加。
8、分區(qū)是有上限的貌似1024個(gè)。
用到的其他操作
1、刪除分區(qū)（直接扔掉分區(qū)文件，數(shù)據(jù)也沒(méi)了）
ALTER TABLE message_all DROP PARTITION p2016;
2、清空分區(qū)數(shù)據(jù)
ALTER TABLE message_all TRUNCATE PARTITION p2017;
3、重定義（可實(shí)現(xiàn)：分區(qū)拆分、合并、重命名）
ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO
(
PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),
PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))
);
檢查/查看你的分區(qū)
1、SHOW TABLE STATUS LIKE 'message_all';
2、SELECT * FROM information_schema.partitions WHERE table_name='message_all';
3、SHOW CREATE TABLE message_all;
4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime= '2016-01-01' AND createtime '2016-12-30';如果用到了分區(qū)partitions里會(huì)有顯示。
5、指定分區(qū)查
SELECT COUNT(1) FROM message_all PARTITION (p2016) 表別名 WHERE ......;
到這里就結(jié)束啦，土豆白。
一些概念
水平分區(qū)Partition有以下幾種模式
分區(qū)管理
常規(guī)HASH和線性HASH的增加收縮分區(qū)的原理是一樣的。增加和收縮分區(qū)后原來(lái)的數(shù)據(jù)會(huì)根據(jù)現(xiàn)有的分區(qū)數(shù)量重新分布。HASH分區(qū)不能刪除分區(qū)，所以不能使用DROP PARTITION操作進(jìn)行分區(qū)刪除操作；
只能通過(guò)ALTER TABLE ... COALESCE PARTITION num來(lái)合并分區(qū)，這里的num是減去的分區(qū)數(shù)量；
可以通過(guò)ALTER TABLE ... ADD PARTITION PARTITIONS num來(lái)增加分區(qū)，這里是null是在原先基礎(chǔ)上再增加的分區(qū)數(shù)量。
1.合并分區(qū)
減去3個(gè)分區(qū)
ALTER TABLE tblinhash COALESCE PARTITION 3;
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
注意：減去兩個(gè)分區(qū)后，數(shù)據(jù)根據(jù)現(xiàn)有的分區(qū)進(jìn)行了重新的分布，以'2003-04-14'為例：POWER(2, CEILING( LOG(2,3) ))=4，2003（4-1）=3,3=3,3(CEILING(3/2)-1)=1,所以現(xiàn)在的'2003-04-14'這條記錄由原來(lái)的p3變成了p1
2.增加分區(qū)
增加4個(gè)分區(qū)
ALTER TABLE tblinhash add PARTITION partitions 4;
SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tblinhash';
當(dāng)在3個(gè)分區(qū)的基礎(chǔ)上增加4個(gè)分區(qū)后，‘2003-04-14’由原來(lái)的p1變成了p3,而另一條記錄由原來(lái)的p2變成了p6
當(dāng)數(shù)據(jù)表中的數(shù)據(jù)量很大時(shí)，分區(qū)帶來(lái)的效率提升才會(huì)顯現(xiàn)出來(lái)。
只有檢索字段為分區(qū)字段時(shí)，分區(qū)帶來(lái)的效率提升才會(huì)比較明顯。因此， 分區(qū)字段的選擇很重要 ，并且 業(yè)務(wù)邏輯要盡可能地根據(jù)分區(qū)字段做相應(yīng)調(diào)整 （盡量使用分區(qū)字段作為查詢條件）。
1、分區(qū)表對(duì)業(yè)務(wù)透明，只需要維護(hù)一個(gè)表的數(shù)據(jù)結(jié)構(gòu)。
2、DML操作加鎖僅影響操作的分區(qū)，不會(huì)影響未訪問(wèn)分區(qū)。
3、通過(guò)分區(qū)交換快速將數(shù)據(jù)換入和換出分區(qū)表。
4、通過(guò)TRUNCATE操作快速清理特定分區(qū)數(shù)據(jù)。
5、通過(guò)強(qiáng)制分區(qū)僅訪問(wèn)特定分區(qū)數(shù)據(jù)，減少操作影響。
6、通過(guò)大數(shù)據(jù)量分區(qū)能有效降低索引層數(shù)，提高查詢性能。
1、創(chuàng)建表時(shí)指定分區(qū)
物理文件：
2、插入數(shù)據(jù)
3、查詢 information_schema.partitions 表得到該表的分區(qū)信息
4、查詢計(jì)劃分析
5、添加分區(qū)
若報(bào)錯(cuò)：
6、修改/覆蓋/合并分區(qū)
報(bào)錯(cuò)
所以需要添加pmax 分區(qū)，一同修改
7、刪除分區(qū)
8、查詢具體分區(qū)的下數(shù)據(jù)
MySQL分區(qū)的限制
重慶分公司
重慶分公司
