mysql几种分区类型说明:

1、RANGE分区

 说明:基于属于一个给定连续区间的列值,把多行分配给分区

创建一个订单表: [JD我的订单页面]

按年来分:

CREATE TABLE `my_order` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',

 `order_id`  int NULL COMMENT '订单号' ,

`order_amount`  decimal NULL COMMENT '订单金额' ,

`user_id`  int NULL COMMENT '用户id' ,

`order_payway`  tinyint(1) NOT NULL COMMENT '支付方式' ,

`order_pay_time`  int NULL COMMENT '支付时间' ,

`create_time`  datetime NULL COMMENT '创建时间' ,

`update_time`  datetime NULL COMMENT '修改时间' ,

PRIMARY KEY (`id`,`create_time`)

)ENGINE=InnoDB  PARTITION BY RANGE ( YEAR(  create_time) ) (

    PARTITION order_2014 VALUES LESS THAN (2015),

    PARTITION order_2015 VALUES LESS THAN (2016),

    PARTITION order_2016 VALUES LESS THAN (2017),

    PARTITION other VALUES LESS THAN MAXVALUE

)

 

按天分区

PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);

 

按照id  [ 超多用户 ]

PARTITION BY RANGE (id) (     

PARTITION p0 VALUES LESS THAN (5000000),    

PARTITION p1 VALUES LESS THAN (10000000),     

PARTITION p2 VALUES LESS THAN (15000000),     

PARTITION p3 VALUES LESS THAN MAXVALUE

);

 

 

------------

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ('2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `my_order` ( `order_id`, `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '2', '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

 

2LIST分区

说明:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择

CREATE TABLE `list_order` (

`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',

`order_id` INT (11) DEFAULT NULL COMMENT '订单号',

`order_amount` DECIMAL (10, 0) DEFAULT NULL COMMENT '订单金额',

`user_id` INT (11) DEFAULT NULL COMMENT '用户id',

`order_payway` TINYINT (1) NOT NULL COMMENT '支付方式 1、货到付款 2、支付宝 3、微信 4、银行卡',

`order_pay_time` INT (11) DEFAULT NULL COMMENT '支付时间',

`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

PRIMARY KEY (`id`, `order_payway`)

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

PARTITION BY LIST (order_payway)(

PARTITION offline_buy VALUES IN (1),

PARTITION online_buy VALUES IN (2, 3, 4)

);

 

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ('211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '1', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

 

这使得在表中增加或删除记录变得容易起来。如果要删除所有线上交易的记录,可以使用查询“ALTER TABLE list_order DROP PARTITION online_buy”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees WHERE store_id IN (2,3,4);”比起来,要有效得多。

 

这样的SQL语句是不能执行成功的: [收到分区的影响,找不到要写的位置]

INSERT INTO `list_order` (  `order_amount`, `user_id`, `order_payway`, `create_time`, `update_time`) VALUES ( '211', '100', '5', '2016-06-28 01:58:47', '2016-06-28 01:58:52');

删除和查询没有影响。

 

 

3HASH分区      

 基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
      要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

 

CREATE TABLE `hash_order` (

`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',

`order_id` INT (11) DEFAULT NULL COMMENT '订单号',

`order_amount` DECIMAL (10, 0) DEFAULT NULL COMMENT '订单金额',

`user_id` INT (11) DEFAULT NULL COMMENT '用户id',

`order_payway` TINYINT (1) NOT NULL COMMENT '支付方式 1、货到付款 2、支付宝 3、微信 4、银行卡',

`order_pay_time` INT (11) DEFAULT NULL COMMENT '支付时间',

`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

PRIMARY KEY (`id`)

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

PARTITION BY HASH (id)

PARTITIONS 4;

4key分区

CREATE TABLE `hash_key_order` (

`id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',

`order_id` INT (11) DEFAULT NULL COMMENT '订单号',

`order_amount` DECIMAL (10, 0) DEFAULT NULL COMMENT '订单金额',

`user_id` INT (11) DEFAULT NULL COMMENT '用户id',

`order_payway` TINYINT (1) NOT NULL COMMENT '支付方式 1、货到付款 2、支付宝 3、微信 4、银行卡',

`order_pay_time` INT (11) DEFAULT NULL COMMENT '支付时间',

`create_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

PRIMARY KEY (`id`)

) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

PARTITION BY LINEAR KEY (id)

PARTITIONS 4;

 

七、手动分表和分区对比

 

手动分表 

分区

多张数据表

一张数据表

重复数据的风险

没有数据重复的风险

写入多张表

写入一张表

没有统一的约束限制

强制的约束限制

 

Mysql 约束 :

 主键约束 --- 约束为1

 非空约束  

 外键约束