一、背景 話說風(fēng)和日麗的一天，為提高隨著業(yè)務(wù)增長的大表（3510449行吧）的訪問效率，于是決定對表分區(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)

不過我們表已經(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ù)也沒了） 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有以下幾種模式

Range（范圍） – 這種模式允許DBA將數(shù)據(jù)劃分不同范圍。例如DBA可以將一個(gè)表通過年份劃分成三個(gè)分區(qū)，80年代（1980’s）的數(shù)據(jù)，90年代（1990’s）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。

Hash（哈希） – 這中模式允許DBA通過對表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)，。例如DBA可以建立一個(gè)對表主鍵進(jìn)行分區(qū)的表。

Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。

List（預(yù)定義列表） – 這種模式允許系統(tǒng)通過DBA定義的列表的值所對應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如：DBA建立了一個(gè)橫跨三個(gè)分區(qū)的表，分別根據(jù)2004年2005年和2006年值所對應(yīng)的數(shù)據(jù)。

Composite（復(fù)合模式） - 很神秘吧，哈哈，其實(shí)是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經(jīng)進(jìn)行了Range范圍分區(qū)的表上，我們可以對其中一個(gè)分區(qū)再進(jìn)行hash哈希分區(qū)。