一、MySQL分區(qū)表
分區(qū)的作用是將一個(gè)表的數(shù)據(jù)按照某種方式，比如按照時(shí)間上的月份，分成多個(gè)較小的，更容易管理的部分，但是邏輯上仍是一個(gè)表。
二、MySQL分區(qū)表對(duì)分區(qū)字段的限制
分區(qū)的字段，必須是表上所有的唯一索引（或者主鍵索引）包含的字段的子集
MySQL是局部分區(qū)，意思是一個(gè)分區(qū)中，包含分區(qū)的數(shù)據(jù)和其對(duì)應(yīng)的索引，而不是索引是一個(gè)索引統(tǒng)一存放在一個(gè)地方，僅分區(qū)數(shù)據(jù)這種方式。
想一下，為什么MySQL的分區(qū)表會(huì)有這個(gè)么一個(gè)奇怪的要求：一個(gè)表上有一個(gè)或者多個(gè)唯一索引的情況下，分區(qū)的字段必須被包含在所有的主鍵或者唯一索引字段中？
三、分區(qū)類型
range分區(qū),分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
按照字段的區(qū)間劃分?jǐn)?shù)據(jù)的歸屬，典型的就是按照時(shí)間維度的月份分區(qū)
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);
insert into test_range_partition (createdate) values ('20180105');
insert into test_range_partition (createdate) values ('20180205');
insert into test_range_partition (createdate) values ('20180206');
insert into test_range_partition (createdate) values ('20180305');
insert into test_range_partition (createdate) values ('20180405');
insert into test_range_partition (createdate) values ('20180505');
insert into test_range_partition (createdate) values ('20180605');
insert into test_range_partition (createdate) values ('20180705');
insert into test_range_partition (createdate) values ('20180805');
insert into test_range_partition (createdate) values ('20180905');
insert into test_range_partition (createdate) values ('20181005');
insert into test_range_partition (createdate) values ('20181105');
select
table_schema,
table_name,
partition_name,
partition_ordinal_position,
partition_method,
partition_expression,
table_rows
from information_schema.`PARTITIONS` where table_schema = 'db01' and table_name = 'test_range_partition';
對(duì)應(yīng)的物理文件
查看每個(gè)分區(qū)的信息
分區(qū)在查詢中的優(yōu)化體現(xiàn)
并不是說(shuō)一個(gè)表只要分區(qū)了，對(duì)于任何查詢都會(huì)實(shí)現(xiàn)查詢優(yōu)化，只有查詢條件的數(shù)據(jù)分布在某一個(gè)分區(qū)的時(shí)候，查詢引擎只會(huì)去某一個(gè)分區(qū)查詢，而不是遍歷整個(gè)表
在管理層面，如果需要?jiǎng)h除某一個(gè)分區(qū)的數(shù)據(jù)，只需要?jiǎng)h除對(duì)應(yīng)的分區(qū)即可
增加與刪除分區(qū)
ALTER TABLE test ADD PARTITION (PARTITION p201902 VALUES LESS THAN ( TO_DAYS('20190301') ));
ALTER TABLE test DROP PARTITION p20180201;
對(duì)于range分區(qū)，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型，如果分區(qū)字段是日期類型的字段，那么就必須將日期類型的字段轉(zhuǎn)換成整型類型
對(duì)于日期類型的轉(zhuǎn)換，優(yōu)化器只支持year()，to_days，to_seconds，unix_timestamp()函數(shù)的轉(zhuǎn)換，其他的并不支持，
也就是說(shuō)，在按日期字段分區(qū)的時(shí)候，如果不是使用上述幾個(gè)函數(shù)轉(zhuǎn)換的，查詢優(yōu)化器將無(wú)法對(duì)相關(guān)查詢進(jìn)行優(yōu)化。
List分區(qū)，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
按照某個(gè)字段上的規(guī)則，不同的數(shù)據(jù)離散地分布在不同的區(qū)中。
create table test_list_partiotion
(
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
(
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
);
對(duì)于List分區(qū)，分區(qū)字段必須是已知的，如果插入的字段不在分區(qū)時(shí)枚舉值中，將無(wú)法插入
Hash分區(qū)，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
Hash分區(qū)可以將數(shù)據(jù)均勻地分不到預(yù)先定義的分區(qū)中，使得各個(gè)分區(qū)的數(shù)據(jù)量分布基本上一致。同樣，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
drop table test_hash_partiotion;
create table test_hash_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
一個(gè)很明顯的問(wèn)題就是，如果分區(qū)字段本身的分布不勻均，那么hash分區(qū)之后存儲(chǔ)的分區(qū)也是不均勻的，hash分區(qū)時(shí)對(duì)于hash的字段，需要慎重。
對(duì)于單個(gè)值的查詢hash分區(qū)可以定位到某一個(gè)分區(qū)
hash分區(qū)在查詢優(yōu)化方面，無(wú)法優(yōu)化范圍查詢，因?yàn)闊o(wú)法確定一個(gè)某個(gè)字段經(jīng)過(guò)hash計(jì)算之后究竟分布了在哪個(gè)分區(qū)之中。
Key分區(qū)，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
與hash分區(qū)不用的是，key分區(qū)使用MySQL自定義的庫(kù)函數(shù)進(jìn)行分區(qū)，不需要hash分區(qū)那樣對(duì)字段整型進(jìn)行轉(zhuǎn)換，同樣，分區(qū)字段必須是整型或者轉(zhuǎn)換為整型
create table test_key_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by key(create_date) partitions 10;
對(duì)于查詢優(yōu)化，Key分區(qū)的特點(diǎn)與Hash分區(qū)一致，對(duì)于單個(gè)字段可以
column 分區(qū)
解決了分區(qū)字段必須是整型或者必須轉(zhuǎn)換為整型的限制，可以對(duì)整型，date或者datetime進(jìn)行支持。
create table test_column_partiotion
(
id int auto_increment,
data_type datetime,
primary key(id,data_type)
)partition by range columns(data_type) (
partition p0 values less than ('20180101'),
partition p1 values less than ('20180201'),
partition p2 values less than ('20180301'),
partition p3 values less than ('20180401'),
partition p4 values less than ('20180501'),
partition p5 values less than ('20180601'),
partition p6 values less than ('20180701'),
partition p7 values less than ('20180801')
);
四、其他例子
1.基于字段進(jìn)行分區(qū)
CREATE TABLE e (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30)
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (150),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES
(1669, "Jim", "Smith"),
(337, "Mary", "Jones"),
(16, "Frank", "White"),
(2005, "Linda", "Black");
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
ALTER TABLE tr DROP PARTITION p2;
2.基于hash進(jìn)行分區(qū)
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
3.每天增加分區(qū)
首先，先多增加幾個(gè)分區(qū)
ALTER TABLE tb_3a_huandan_detail PARTITION BY RANGE (TO_DAYS(ServiceStartTime))
(
PARTITION p20160523 VALUES LESS THAN (TO_DAYS('2016-05-23')),
PARTITION p20160524 VALUES LESS THAN (TO_DAYS('2016-05-24')),
PARTITION p20160525 VALUES LESS THAN (TO_DAYS('2016-05-25')),
PARTITION p20160526 VALUES LESS THAN (TO_DAYS('2016-05-26')),
PARTITION p20160527 VALUES LESS THAN (TO_DAYS('2016-05-27'))
)
其次,分區(qū)存儲(chǔ)過(guò)程
DELIMITER $$
USE `sdk`$$
ROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_Partition_3Ahuadan`()
EGIN
/* 事務(wù)回滾，其實(shí)放這里沒(méi)什么作用，ALTER TABLE是隱式提交，回滾不了的。*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
/* 到系統(tǒng)表查出這個(gè)表的最大分區(qū)，得到最大分區(qū)的日期。在創(chuàng)建分區(qū)的時(shí)候，名稱就以日期格式存放，方便后面維護(hù) */
SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name='tb_3a_huandan_detail' ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表，在最大分區(qū)的后面增加一個(gè)分區(qū)，時(shí)間范圍加1天 */
SET @s1=CONCAT('ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 輸出查看增加分區(qū)語(yǔ)句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 取出最小的分區(qū)的名稱，并刪除掉 。
注意：刪除分區(qū)會(huì)同時(shí)刪除分區(qū)內(nèi)的數(shù)據(jù)，慎重 */
/*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
where table_name='tb_3a_huandan_detail' order by partition_ordinal_position limit 1;
SET @s=concat('ALTER TABLE tb_3a_huandan_detail DROP PARTITION ',@P0_Name);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; */
/* 提交 */
COMMIT ;
END$$
DELIMITER ;
最后，增加定時(shí)事件
DELIMITER ||
CREATE EVENT Partition_3Ahuadan_event
ON SCHEDULE
EVERY 1 day STARTS '2016-05-27 23:59:59'
DO
BEGIN
CALL nres.`create_Partition_3Ahuadan`;
END ||
DELIMITER ;
