mysql分区实操

分成2步:

1.要新建与原表一样的新表,在建立新表时(create)同时分区,不能直接对原表分区

2.将原表数据插入新表

INSERT INTO 目标表 SELECT * FROM 来源表;

CREATE TABLE met_shopv2_order_copy1  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orderid` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '订单ID',
  `type` int(1) NOT NULL COMMENT '订单类型',
  `uid` int(11) NOT NULL COMMENT '用户ID',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
  `state` int(2) NOT NULL COMMENT '订单状态',
  `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮件',
  `message` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单留言',
  `address` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '收货地址',
  `price` double NOT NULL COMMENT '商品金额',
  `cprice` double NOT NULL COMMENT '修改总金额',
  `tprice` double NOT NULL COMMENT '订单总金额',
  `discount` double NOT NULL COMMENT '折扣价格',
  `discount_info` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '折扣信息',
  `discount_use` int(1) NOT NULL COMMENT '修改总金额后是否可以继续使用折扣',
  `freight` double NOT NULL COMMENT '运费',
  `cinfo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '物流公司',
  `oinfo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '物流单号',
  `paytype` int(1) NOT NULL COMMENT '付款方式',
  `payinfo` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '付款信息',
  `invoice` int(1) NOT NULL COMMENT '是否需要发票',
  `invoice_info` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '发票信息',
  `remark` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '备注',
  `rtime` int(11) NOT NULL COMMENT '下订单时间',
  `ptime` int(11) NOT NULL COMMENT '付款时间',
  `stime` int(11) NOT NULL COMMENT '发货时间',
  `ctime` int(11) NOT NULL COMMENT '关闭订单时间',
  `search` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '信息搜索字段',
  `lang` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '所属语言',
  PRIMARY KEY (id,ptime) USING BTREE,//原表中只有id是主键,但分表的话,是要求分表条件也是主键
  INDEX `orderid`(`orderid`) USING BTREE,
  INDEX `idx_shop_order_uid`(`uid`) USING BTREE,
  INDEX `id`(`id`) USING BTREE,
  INDEX `uid`(`uid`) USING BTREE,
  INDEX `state`(`state`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic

PARTITION BY RANGE (ptime)//range后是表分区的条件字段.
(   
PARTITION p_201803 VALUES LESS THAN (1522512000),//格式化过的时间
PARTITION p_201804 VALUES LESS THAN (1525104000),
PARTITION p_201805 VALUES LESS THAN (1527782400),
PARTITION p_Dec VALUES LESS THAN MAXVALUE );

INSERT INTO met_shopv2_order_copy1 SELECT * FROM met_shopv2_order;

总结:按月份进行的分表,按照月份内查询数据的话,速度是会高于原表.

但同时查几个月的数据时,原表速度反而略高于分表后的数据.

参考资料:

MYSQL的分区字段必须包含主键-介绍以及解决方法

MySQL 表分区详解MyiSam引擎和InnoDb 区别（实测）

mysql中把一个表的数据批量导入另一个表中