MYSQL分区

一、分区概念 

分区是指允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。

通俗地讲表分区是将一张大表，根据条件分割成若干个小表。数据库存存储的时候会存在多个文件中。而对于程序或者数据库来说，依旧是一张表，就是将mysql相关的数据文件，分成了多个存储。 【MYSQL 5.1之后支持分区】

比如：某用户表的记录超过了1500万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。 【mysql含char类型单表不建议超过500万数据，标量类型不建议超过1000万】

 

二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。
分区的一些优点包括：
      1）、与单个磁盘或文件系统分区相比，可以存储更多的数据。
      2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
      3）、一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
      4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT  COUNT (*) from user；”。 并行这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
      5）、通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、mysql表文件说明：

 

mysql两种常用存储引擎myisam和innodb

myisam不支持事务；innodb支持事务

myisam的数据库的物理文件结构为：

.frm文件：与表相关的原始数据信息都存放在frm文件，包括表结构的定义信息等。各种存储引擎都需要frm文件，并且存放于数据库名目录下。

.myd文件：myisam存储引擎专用，用于存储myisam表的数据

.myi文件：myisam存储引擎专用，用于存储myisam表的索引相关信息

innodb的数据库的物理文件结构为：

.frm文件

.ibd文件和.ibdata文件：

这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件

共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件

关于共享表空间和独享表空间：http://www.linuxidc.com/Linux/2015-01/111241.htm

四、查看mysql是否支持分区：

mysql可以通过下面语句判断是否支持分区：

SHOW VARIABLES LIKE '%partition%';

如果输出：

have_partitioning   YES 表示支持分区。

或者通过：

SHOW PLUGINS;

显示所有插件，如果有partition ACTIVE STORAGE ENGINE GPL 插件则表明支持分区

 

 

五、mysql几种分区类型说明：

分区类型

特点

RANGE分区

允许将数据划分不同范围。

例如：可以将一个表通过年份划分成若干个分区，或者是按照id的值范围进行划分。

LIST分区

这种模式允许系统通过预定义的列表的值来对数据进行分Values In (1,2,3)

例如：将用户按照地图进行划分【省id 1,2,3在一个分区 4,5,6在一个分区等】

Hash分区

这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。

例如：可以建立一个对表主键进行分区的表【根据hash算法将数据平均分配在分区内】

Key分区

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的

 

 

六、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_2015 VALUES LESS THAN (2015),

    PARTITION order_2016 VALUES LESS THAN (2016),

    PARTITION order_2017 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');

 

2、LIST分区

说明：类似于按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_orderDROP 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');

删除和查询没有影响。

 

 

3、HASH分区      

 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含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;

4、key分区

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

 非空约束  

 外键约束