mysql分区

mysql分区

1、分区的字段，必须是表上所有的唯一索引（或者主键索引）包含的字段的子集
2、range分区：分区字段必须是整型或者转换为整型，
按照字段的区间划分数据的归属，典型的就是按照时间维度的月份分区
操作步骤：
一亿条数据

1、更改主键（757s）

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

2、分27个区（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、新增一个分区（17s）

采用存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `pm_hba_port`()
BEGIN
/* 事务回滚，其实放这里没什么作用，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 = '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 ), ''')))' );
/* 输出查看增加分区语句*/
    SELECT
        @s1;
    PREPARE stmt2 
    FROM
        @s1;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
    COMMIT;

END