mysql表分区案例

0、整理表空间碎片
optimize table tablename

1、表分区按年分区，季度子分区

alter table key_part 
partition by range(year(create_time))
subpartition by hash(quarter(create_time))
subpartitions 4 (
    partition p0 values less than (2015),
    partition p2015 values less than (2016),
    partition p2016 values less than (2017),
    partition p2017 values less than (2018),
    partition p2018 values less than (2019),
    partition p1 values less than maxvalue
);

 

总共产生24个分区，1年4个季度，6年24个季度。

2、按照天分区月表
create_time支持如下日期格式：
%Y-%c-%d
%Y-%c-%d %h:%i:%s

alter table aa 
partition by range (to_days(create_time)) (
    partition p01 values less than (to_days('2018-04-01')) engine = innodb,
    partition p02 values less than (to_days('2018-04-02')) engine = innodb,
    partition p03 values less than (to_days('2018-04-03')) engine = innodb,
    partition p04 values less than (to_days('2018-04-04')) engine = innodb,
    partition p05 values less than (to_days('2018-04-05')) engine = innodb,
    partition p06 values less than (to_days('2018-04-06')) engine = innodb,
    partition p07 values less than (to_days('2018-04-07')) engine = innodb,
    partition p08 values less than (to_days('2018-04-08')) engine = innodb,
    partition p09 values less than (to_days('2018-04-09')) engine = innodb,
    partition p10 values less than MAXVALUE engine = innodb     
);

 

explain partitions select * from key_part where create_time>='2018-04-12' and create_time<='2018-04-15'

可以看到只遍历了4个分区表，只扫描了4行，而不是扫描所有的行。

explain partitions select * from key_part where create_time>='2018-04-27'

可以看到只遍历了5个分区表，只扫描了5行，而不是扫描所有的行。

 3、按照年分区表

alter table user partition by linear hash(year(create_time)) partitions 12;

在5.7版本之前，对于data和datetime类型的列，如果要实现分区裁剪，只能使用year() 和to_days()函数，在5.7版本中，又新增了to_seconds()函数。

4、MYSQL的分区字段，必须包含在主键字段内或唯一索引内

这是因为表分区时，分区字段必须包含在主键字段内或唯一索引内，一张表只能有一个主键或一个唯一索引，主键和唯一索引不能同时存在

CREATE TABLE `key_part` (
  `news_id` int(11) NOT NULL COMMENT '新闻id',
  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',
  `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源ip',
  `create_time` datetime NOT NULL COMMENT '时间',
  PRIMARY KEY (`u_id`,`create_time`),
  KEY `create_time` (`create_time`) USING BTREE,
  KEY `aa` (`news_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p01 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN ('2018-03-01') ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN ('2018-05-01') ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN ('2018-09-01') ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN ('2018-11-01') ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;

 

5、Columns分区

alter table key_part 
partition by range columns(create_time) (
    partition p01 values less than ('2018-01-01') engine = innodb,
    partition p02 values less than ('2018-03-01') engine = innodb,
    partition p03 values less than ('2018-05-01') engine = innodb,
    partition p04 values less than ('2018-07-01') engine = innodb,
    partition p05 values less than ('2018-09-01') engine = innodb,
    partition p06 values less than ('2018-11-01') engine = innodb,
    partition p07 values less than ('2019-01-01') engine = innodb,
    partition p08 values less than ('2020-01-01') engine = innodb,
    partition p09 values less than maxvalue engine = innodb
);

 

6、添加表分区

-- 对表重新表分区
alter table lot_order_aa partition by range columns(create_time)(partition p03 values less than ('2018-03-01'));

-- 在已有分区的表上，添加表分区
alter table lot_order_aa add partition (partition p05 values less than ('2018-05-01'));