mysql分區(qū)

1挤牛、分區(qū)的字段臭家，必須是表上所有的唯一索引（或者主鍵索引）包含的字段的子集
2忽匈、range分區(qū)：分區(qū)字段必須是整型或者轉(zhuǎn)換為整型房午，
按照字段的區(qū)間劃分?jǐn)?shù)據(jù)的歸屬，典型的就是按照時(shí)間維度的月份分區(qū)
操作步驟：
一億條數(shù)據(jù)

1丹允、更改主鍵（757s）

ALTER TABLE `pm_hba_port` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`, `insert_time`);

2郭厌、分27個(gè)區(qū)（2571s==42min）

ALTER TABLE pm_hba_port
PARTITION BY RANGE(TO_DAYS(insert_time))
(
PARTITION p19800101 VALUES LESS THAN (TO_DAYS('19800101')),

PARTITION p19850101 VALUES LESS THAN (TO_DAYS('19850101')),

PARTITION p19900101 VALUES LESS THAN (TO_DAYS('19900101')) ,

PARTITION p19950101 VALUES LESS THAN (TO_DAYS('19950101')) ,

 PARTITION p20000101 VALUES LESS THAN (TO_DAYS('20000101') ),

PARTITION p20050101 VALUES LESS THAN (TO_DAYS('20050101')) ,

PARTITION p20100101 VALUES LESS THAN (TO_DAYS('20100101')) ,

PARTITION p20150101 VALUES LESS THAN (TO_DAYS('20150101')) ,

PARTITION p20200101 VALUES LESS THAN (TO_DAYS('20200101')) ,

PARTITION p20250101 VALUES LESS THAN (TO_DAYS('20250101')) ,
PARTITION p20300101 VALUES LESS THAN (TO_DAYS('20300101')) ,
PARTITION p20350101 VALUES LESS THAN (TO_DAYS('20350101')) ,

PARTITION p20400101 VALUES LESS THAN (TO_DAYS('20400101')) ,

PARTITION p20450101 VALUES LESS THAN (TO_DAYS('20450101')) ,

PARTITION p20500101 VALUES LESS THAN (TO_DAYS('20500101')) ,
PARTITION p20550101 VALUES LESS THAN (TO_DAYS('20550101')) ,
PARTITION p20630101 VALUES LESS THAN (TO_DAYS('20630101')), 

PARTITION p20800101 VALUES LESS THAN (TO_DAYS('20800101')) ,

PARTITION p21000101 VALUES LESS THAN (TO_DAYS('21000101')) ,

PARTITION p21100101 VALUES LESS THAN (TO_DAYS('21100101')) ,
PARTITION p21200101 VALUES LESS THAN (TO_DAYS('21200101')) ,
PARTITION p21300101 VALUES LESS THAN (TO_DAYS('21300101')), 

PARTITION p21400101 VALUES LESS THAN (TO_DAYS('21400101')) ,

PARTITION p21500101 VALUES LESS THAN (TO_DAYS('21500101')) ,

PARTITION p21600101 VALUES LESS THAN (TO_DAYS('21600101')) ,
PARTITION p21700101 VALUES LESS THAN (TO_DAYS('21700101')) ,
PARTITION p21770101 VALUES LESS THAN (TO_DAYS('21770101'))
);

3、新增一個(gè)分區(qū)（17s）

采用存儲(chǔ)過(guò)程

CREATE DEFINER=`root`@`%` PROCEDURE `pm_hba_port`()
BEGIN
/* 事務(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 = 'pm_hba_port' 
    ORDER BY
        partition_ordinal_position DESC 
        LIMIT 1;
    SELECT
        date_format( NOW(), '%Y%m%d' ) INTO @now_time;
    IF
        unix_timestamp( @now_time )>= unix_timestamp( @P12_Name ) THEN
            
            SET @Max_date = DATE(
            DATE_ADD( @now_time + 0, INTERVAL 1 DAY ))+ 0;
        ELSE 
            SET @Max_date = DATE(
            DATE_ADD( @P12_Name + 0, INTERVAL 1 DAY ))+ 0;
        
    END IF;
    
    SET @s1 = CONCAT( 'ALTER TABLE pm_hba_port 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;
    COMMIT;

END