MySQL数据库表分区注意事项大全【推荐】

发布时间：2020-05-24 05:33:25 所属栏目：MsSql 来源：互联网

导读：表分区与数据库分区是不一样的那么碰到表分区使用时我们要注意一些什么事情呢，今天我们来看一篇关于MySQL数据库表分区注意事项的细节。

表分区与数据库分区是不一样的那么碰到表分区使用时我们要注意一些什么事情呢，今天我们来看一篇关于MySQL数据库表分区注意事项的细节。

1、分区列索引约束

若表有primary key或unique key，则分区表的分区列必须包含在primary key或unique key列表里，这是为了确保主键的效率，否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。

2、各分区类型条件

range 每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠

list只支持整形字段或返回整形数的表达式，每个分区列表里的值列表必须整数

hash类型只支持整形字段或返回整形数的表达式

key类型只支持列名形式（可一个或多个列名），不支持表达式

3、分区可用函数

ABS()

CEILING() (see CEILING() and FLOOR(),immediately following this list)

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT()

FLOOR() (see CEILING() and FLOOR(),immediately following this list)

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

WEEKDAY()

YEAR()

YEARWEEK()

注意：

因为分区函数不包括FROM_UNIXTIME函数，所以用时间戳转时间来分区就无法实现了，只能用date或者datetime来分区

例如按年我们可以用：

PARTITION BY RANGE (YEAR(date))

按月：

PARTITION BY RANGE(date div 100)

#div 会把日期变成整数，例如：2014-12-01 -> 20141201、100就是从后面去掉两位，最后结果是201412

一个订单做分区的例子：

CREATE TABLE `order` (
 `order_id` bigint(19) NOT NULL DEFAULT '0' COMMENT '订单ID：年月日时分秒12位 7位随机数',`date` date NOT NULL DEFAULT '0000-00-00' COMMENT '订单日期',`amount` int(11) DEFAULT NULL COMMENT '支付金额，单位分',`status` tinyint(1) DEFAULT '0' COMMENT '0:等待支付 1:支付成功 2:支付失败 3:验证失败',`addtime` int(10) DEFAULT NULL COMMENT '订单添加时间',PRIMARY KEY (`order_id`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

因为我们没法用时间戳来做按时间分区，所以添加了一个date字段，这个字段和order_id一起作为主键，我们知道分区的列一定要放到主键里面去的。下面我们用date计算成年月组合来分区

ALTER TABLE order PARTITION BY RANGE( date DIV 100)
(
  PARTITION p_2014_06 VALUES LESS THAN (201407),PARTITION p_2014_07 VALUES LESS THAN (201408),PARTITION p_2014_08 VALUES LESS THAN (201409),PARTITION p_2014_09 VALUES LESS THAN (201410),PARTITION p_2014_10 VALUES LESS THAN (201411),PARTITION p_catch_all VALUES LESS THAN MAXVALUE
);

以上 LESS THAN MAXVALUE 设置了最后一个分区p_catch_all，所以不能用add的方式来添加分区了，以下语句不可用：
ALTER TABLE order ADD PARTITION (PARTITION p_2014_11 VALUES LESS THAN (201412));

只能把最后的p_catch_all分区拆分成两个，这样还有一个好处就是在p_catch_all分区的数据不会丢失。数据的合并与拆分用REORGANIZE PARTITION进行。

alter table order reorganize partition p_catch_all into 
(
  partition p_2014_11 values less than (201412),partition p_catch_all values less than maxvalue
);

合并分区：

alter table order reorganize partition p_2014_10,p_2014_11,p_catch_all into 
(
  partition p_catch_test values less than MAXVALUE
);

为什么不分到p_catch_all去？因为会报分区以存在。

为什么合并的时候要带上最后一个分区p_catch_all？因为除了最后一个分区，其他重组的分区范围不能改变总范围。
删除分区但是不删除数据：

alter table 表名 remove partitioning

注意：上面语句在5.5可以执行，5.6好像有问题，要先测试一下

分区之后，where条件是一个范围的话分区是不起作用的，如 where date >= '2014-01-01' And date <= '2014-01-31'
一定要用 = 或者 in 条件才行 where date = '2014-01-01' 或者 where date in ('2014-01-01','2014-01-02','2014-01-03'...)

补充:MySQL表的四种分区类型

一、什么是表分区

通俗地讲表分区是将一大表，根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。

二、为什么要对表进行分区

为了改善大型表以及具有各种访问模式的表的可伸缩性，可管理性和提高数据库效率。

分区的一些优点包括：

与单个磁盘或文件系统分区相比，可以存储更多的数据。

对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。

一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。

涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id,COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。

通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、分区类型

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

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

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

RANGE分区

基于属于一个给定连续区间的列值，把多行分配给分区。

这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。以下是实例。

Sql代码：

CREATE TABLE employees (
  id INT NOT NULL,fname VARCHAR(30),lname VARCHAR(30),hired DATE NOT NULL DEFAULT '1970-01-01',separated DATE NOT NULL DEFAULT '9999-12-31',job_code INT NOT NULL,store_id INT NOT NULL
)
partition BY RANGE (store_id) (
  partition p0 VALUES LESS THAN (6),partition p1 VALUES LESS THAN (11),partition p2 VALUES LESS THAN (16),partition p3 VALUES LESS THAN (21)
);

按照这种分区方案，在商店1到5工作的雇员相对应的所有行被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进行定义，从最低到最高。这是PARTITION BY RANGE 语法的要求；在这点上，它类似于C或Java中的“switch … case”语句。对于包含数据(72,‘Michael',‘Widenius','1998-06-25′,NULL,13)的一个新行，可以很容易地确定它将插入到p2分区中，但是如果增加了一个编号为第21的商店，将会发生什么呢？在这种方案下，由于没有规则把store_id大于20的商店包含在内，服务器将不知道把该行保存在何处，将会导致错误。要避免这种错误，可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句，该子句提供给所有大于明确指定的最高值的值：

Sql代码：

CREATE TABLE employees (
  id INT NOT NULL,store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
  PARTITION p0 VALUES LESS THAN (6),PARTITION p1 VALUES LESS THAN (11),PARTITION p2 VALUES LESS THAN (16),PARTITION p3 VALUES LESS THAN MAXVALUE
);

MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25,30,或更多，可以使用ALTER TABLE语句为商店21-25,26-30,等等增加新的分区。在几乎一样的结构中，你还可以基于雇员的工作代码来分割表，也就是说，基于job_code 列值的连续区间。例如――假定2位数字的工作代码用来表示普通（店内的）工人，三个数字代码表示办公室和支持人员，四个数字代码表示管理层，你可以使用下面的语句创建该分区表：

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!