一、MySQL分區表
分區的作用是將一個表的數據按照某種方式，比如按照時間上的月份，分成多個較小的，更容易管理的部分，但是邏輯上仍是一個表。
二、MySQL分區表對分區字段的限制
分區的字段，必須是表上所有的唯一索引（或者主鍵索引）包含的字段的子集
MySQL是局部分區，意思是一個分區中，包含分區的數據和其對應的索引，而不是索引是一個索引統一存放在一個地方，僅分區數據這種方式。
想一下，為什么MySQL的分區表會有這個么一個奇怪的要求：一個表上有一個或者多個唯一索引的情況下，分區的字段必須被包含在所有的主鍵或者唯一索引字段中？
三、分區類型
range分區,分區字段必須是整型或者轉換為整型
按照字段的區間劃分數據的歸屬，典型的就是按照時間維度的月份分區
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';
對應的物理文件
查看每個分區的信息
分區在查詢中的優化體現
并不是說一個表只要分區了，對于任何查詢都會實現查詢優化，只有查詢條件的數據分布在某一個分區的時候，查詢引擎只會去某一個分區查詢，而不是遍歷整個表
在管理層面，如果需要刪除某一個分區的數據，只需要刪除對應的分區即可
增加與刪除分區
ALTER TABLE test ADD PARTITION (PARTITION p201902 VALUES LESS THAN ( TO_DAYS('20190301') ));
ALTER TABLE test DROP PARTITION p20180201;
對于range分區，分區字段必須是整型或者轉換為整型，如果分區字段是日期類型的字段，那么就必須將日期類型的字段轉換成整型類型
對于日期類型的轉換，優化器只支持year()，to_days，to_seconds，unix_timestamp()函數的轉換，其他的并不支持，
也就是說，在按日期字段分區的時候，如果不是使用上述幾個函數轉換的，查詢優化器將無法對相關查詢進行優化。
List分區，分區字段必須是整型或者轉換為整型
按照某個字段上的規則，不同的數據離散地分布在不同的區中。
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)
);
對于List分區，分區字段必須是已知的，如果插入的字段不在分區時枚舉值中，將無法插入
Hash分區，分區字段必須是整型或者轉換為整型
Hash分區可以將數據均勻地分不到預先定義的分區中，使得各個分區的數據量分布基本上一致。同樣，分區字段必須是整型或者轉換為整型
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;
一個很明顯的問題就是，如果分區字段本身的分布不勻均，那么hash分區之后存儲的分區也是不均勻的，hash分區時對于hash的字段，需要慎重。
對于單個值的查詢hash分區可以定位到某一個分區
hash分區在查詢優化方面，無法優化范圍查詢，因為無法確定一個某個字段經過hash計算之后究竟分布了在哪個分區之中。
Key分區，分區字段必須是整型或者轉換為整型
與hash分區不用的是，key分區使用MySQL自定義的庫函數進行分區，不需要hash分區那樣對字段整型進行轉換，同樣，分區字段必須是整型或者轉換為整型
create table test_key_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by key(create_date) partitions 10;
對于查詢優化，Key分區的特點與Hash分區一致，對于單個字段可以
column 分區
解決了分區字段必須是整型或者必須轉換為整型的限制，可以對整型，date或者datetime進行支持。
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.基于字段進行分區
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進行分區
CREATE TABLE clients (
id INT,
fname VARCHAR(30),
lname VARCHAR(30),
signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
3.每天增加分區
首先，先多增加幾個分區
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'))
)
其次,分區存儲過程
DELIMITER $$
USE `sdk`$$
ROP PROCEDURE IF EXISTS `create_Partition_3Ahuadan`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_Partition_3Ahuadan`()
EGIN
/* 事務回滾，其實放這里沒什么作用，ALTER TABLE是隱式提交，回滾不了的。*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
/* 到系統表查出這個表的最大分區，得到最大分區的日期。在創建分區的時候，名稱就以日期格式存放，方便后面維護 */
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;
/* 修改表，在最大分區的后面增加一個分區，時間范圍加1天 */
SET @s1=CONCAT('ALTER TABLE tb_3a_huandan_detail ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 輸出查看增加分區語句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 取出最小的分區的名稱，并刪除掉 。
注意：刪除分區會同時刪除分區內的數據，慎重 */
/*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 ;
最后，增加定時事件
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 ;
