mysql 5.6分区表应用

mysql 5.6分区表测试:
删除表如果存在“my_orders”;
创建表“my_orders”(
,“id”int(10)无符号NOT NULL AUTO_INCREMENT评论& # 39;表主键& # 39;,
,“pid”int(10)无符号NOT NULL评论& # 39;产品id # 39;,
,“价格”十进制(15日2)NOT NULL评论& # 39;单价& # 39;,
,“num”int (11) NOT NULL评论& # 39;购买数量& # 39;,
,“uid”int(10)无符号NOT NULL评论& # 39;客户id # 39;,
,“一次”datetime NOT NULL评论& # 39;下单时间& # 39;,
,utime int(10)无符号NOT NULL默认0评论& # 39;修改时间& # 39;,
,“isdel”非常小的整数(4)NOT NULL默认& # 39;0 & # 39;评论& # 39;软删除标识& # 39;,
,主键(“id”,“一次”)
)引擎=InnoDB的默认字符集=utf8


/* * * * * * * * *分区信息* * * * * * * * * * * * * */

分区范围(一年(一次))
(
,,分区p0值小于(2016),
,,分区p1值小于(2017),
,,分区p2值小于MAXVALUE
);

删除表如果存在“my_order”;
创建表“my_order”(
,“id”int(10)无符号NOT NULL AUTO_INCREMENT评论& # 39;表主键& # 39;,
,“pid”int(10)无符号NOT NULL评论& # 39;产品id # 39;,
,“价格”十进制(15日2)NOT NULL评论& # 39;单价& # 39;,
,“num”int (11) NOT NULL评论& # 39;购买数量& # 39;,
,“uid”int(10)无符号NOT NULL评论& # 39;客户id # 39;,
,“一次”datetime NOT NULL评论& # 39;下单时间& # 39;,
,utime int(10)无符号NOT NULL默认0评论& # 39;修改时间& # 39;,
,“isdel”非常小的整数(4)NOT NULL默认& # 39;0 & # 39;评论& # 39;软删除标识& # 39;,
,PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/**************************向分区表插入数据****************************/

INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_orders(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00');

/**************************向未分区表插入数据****************************/

INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00');
INSERT INTO my_order(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00'；

/**********************************主从复制大量数据******************************/

INSERT INTO `my_orders`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_orders`;
INSERT INTO `my_order`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `my_order`;

/***************************查询性能分析**************************************/

SELECT * FROM `my_orders` WHERE `uid`=89757 AND `atime`/* * * *用时0.084 s * * * */
SELECT *从“my_order”“uid”=89757和'使' & lt;CURRENT_TIMESTAMP ();
/* * * *用时0.284 s * * * */
解释分区SELECT *从“my_orders”“uid”=89757和'使' & lt;CURRENT_TIMESTAMP ();
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - - - - - - - - - +
| | id select_type |表,,| | |分区类型possible_keys |关键,| key_len | ref, |行,|额外的,,,|
+ - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - +

mysql 5.6分区表应用