1.检查你的Mysql是否支持分区
mysql> SHOW VARIABLES LIKE '%partition%';
若结果如下，表示你的Mysql支持表分区:
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| have_partition_engine | YES |
+-----------------------+-------+
1 row in set (0.00 sec)
RANGE分区表创建方式：
DROP TABLE IF EXISTS `my_orders`;
CREATE TABLE `my_orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
`pid` int(10) unsigned NOT NULL COMMENT '产品ID',
`price` decimal(15,2) NOT NULL COMMENT '单价',
`num` int(11) NOT NULL COMMENT '购买数量',
`uid` int(10) unsigned NOT NULL COMMENT '客户ID',
`atime` datetime NOT NULL COMMENT '下单时间',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*********分区信息**************/
PARTITION BY RANGE (YEAR(atime))
(
PARTITION p0 VALUES LESS THAN (2016),
PARTITION p1 VALUES LESS THAN (2017),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
以上是一个简单的订单表，分区字段是atime,根据RANGE分区，这样当你向该表中插入数据的时候，Mysql会根据YEAR(atime)的值进行分区存储。
?
检查分区是否创建成功，执行查询语句：
EXPLAIN PARTITIONS SELECT * FROM `my_orders`
若成功，结果如下：
?
性能分析：
1）.创建同样表结构，但没有进行分区的表
DROP TABLE IF EXISTS `my_order`;
CREATE TABLE `my_order` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键',
`pid` int(10) unsigned NOT NULL COMMENT '产品ID',
`price` decimal(15,2) NOT NULL COMMENT '单价',
`num` int(11) NOT NULL COMMENT '购买数量',
`uid` int(10) unsigned NOT NULL COMMENT '客户ID',
`atime` datetime NOT NULL COMMENT '下单时间',
`utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间',
`isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识',
PRIMARY KEY (`id`,`atime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
?
2）.向两张表中插入相同的数据
?
/**************************向分区表插入数据****************************/
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`) V