MySQL 分区表

栏目: Mysql · 发布时间: 6年前

内容简介：表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

今天统计数据的时候发现一张表使用了表分区，借此机会记录一下。

1. 什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

2. 表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

3. 表分区有什么好处？

1）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。 2）和单个磁盘或者文件系统相比，可以存储更多数据 3）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。 4）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。 5）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

4. 分区表的限制因素

1）一个表最多只能有1024个分区 2） MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在 MySQL 5.5中提供了非整数表达式分区的支持。 3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。 4）分区表中无法使用外键约束 5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

5. 如何判断当前MySQL是否支持分区？

命令：show variables like '%partition%' 运行结果:

mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%partition%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| have_partitioning | YES |

+-------------------+-------+

1 row in set (0.00 sec)

have_partintioning 的值为YES，表示支持分区。

6. MySQL支持的分区类型有哪些？

1）RANGE分区：按照数据的区间范围分区 2）LIST分区：按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。 3）HASH分区 4）KEY分区说明在MySQL5.1版本中，RANGE,LIST,HASH分区要求分区键必须是INT类型，或者通过表达式返回INT类型。但KEY分区的时候，可以使用其他类型的列（BLOB，TEXT类型除外）作为分区键。

7. Range分区

利用取值范围进行分区，区间要连续并且不能互相重叠。语法：

partition by range(exp)( //exp可以为列名或者表达式，比如to_date(created_date)
    partition p0 values less than(num)
)

partition by range(exp)( //exp可以为列名或者表达式，比如to_date(created_date)

partition p0 values less than(num)

)

例如：

mysql> create table emp(
    -> id INT NOT null,
    -> store_id int not null
    -> )
    -> partition by range(store_id)(  
    -> partition p0 values less than(10),  
    -> partition p1 values less than(20)  
    -> );

mysql> create table emp(

-> id INT NOT null,

-> store_id int not null

-> )

-> partition by range(store_id)(

-> partition p0 values less than(10),

-> partition p1 values less than(20)

-> );

上面的语句创建了emp表，并根据store_id字段进行分区，小于10的值存在分区p0中，大于等于10，小于20的值存在分区p1中。注意每个分区都是按顺序定义的，从最低到最高。上面的语句，如果将less than(10) 和less than (20)的顺序颠倒过来，那么将报错，如下：

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

1	ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

RANGE分区存在的问题

range范围覆盖问题：当插入的记录中对应的分区键的值不在分区定义的范围中的时候，插入语句会失败。上面的例子，如果我插入一条store_id = 30的记录会怎么样呢？我们上面分区的时候，最大值是20，如果插入一条超过20的记录，会报错:

mysql> insert into emp(id,store_id) values(2,30); ERROR 1526 (HY000): Table has no partition for value 30

1
2

mysql> insert into emp(id,store_id) values(2,30);
ERROR 1526 (HY000): Table has no partition for value 30

提示30这个值没有对应的分区。 解决办法 A. 预估分区键的值，及时新增分区。 B. 设置分区的时候，使用values less than maxvalue 子句,MAXVALUE表示最大的可能的整数值。 C. 尽量选择能够全部覆盖的字段作为分区键，比如一年的十二个月等。
Range分区中，分区键的值如果是NULL，将被作为一个最小值来处理。

8. LIST分区

List分区是建立离散的值列表告诉数据库特定的值属于哪个分区。语法：

partition by list(exp)( //exp为列名或者表达式
        partition p0 values in (3,5)  //值为3和5的在p0分区
    )

partition by list(exp)( //exp为列名或者表达式

partition p0 values in (3,5) //值为3和5的在p0分区

)

与Range不同的是，list分区不必生命任何特定的顺序。例如：

mysql> create table emp1(
    -> id int not null,
    -> store_id int not null
    -> )
    -> partition by list(store_id)(
    ->   partition p0 values in (3,5),
    ->   partition p1 values in (2,6,7,9)
    -> );

mysql> create table emp1(

-> id int not null,

-> store_id int not null

-> )

-> partition by list(store_id)(

-> partition p0 values in (3,5),

-> partition p1 values in (2,6,7,9)

-> );

注意如果插入的记录对应的分区键的值不在list分区指定的值中，将会插入失败。并且，list不能像range分区那样提供maxvalue。

9. Columns分区

MySQL5.5中引入的分区类型，解决了5.5版本之前range分区和list分区只支持整数分区的问题。 Columns分区可以细分为 range columns分区和 list columns分区，他们都支持整数，日期时间，字符串三大数据类型。（不支持text和blob类型作为分区键） columns分区还支持多列分区（这里不详细展开）。

10. Hash分区

Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。 MySQL支持两种Hash分区:常规Hash分区和线性Hash分区。 A. 常规Hash分区:使用取模算法语法：

partition by hash(store_id) partitions 4;

1	partition by hash(store_id) partitions 4;

上面的语句，根据store_id对4取模，决定记录存储位置。比如store_id = 234的记录，MOD(234,4)=2,所以会被存储在第二个分区。

常规Hash分区的优点和不足 优点：能够使数据尽可能的均匀分布。缺点：不适合分区经常变动的需求。假如我要新增加两个分区，现在有6个分区，那么MOD(234,6)的结果与之前MOD(234,4)的结果就会出现不一致，这样大部分数据就需要重新计算分区。为解决此问题，MySQL提供了线性Hash分区。

B. 线性Hash分区：分区函数是一个线性的2的幂的运算法则。语法：

partition by LINER hash(store_id) partitions 4;

1	partition by LINER hash(store_id) partitions 4;

与常规Hash的不同在于，“Liner”关键字。算法介绍: 假设要保存记录的分区编号为N,num为一个非负整数,表示分割成的分区的数量，那么N可以通过以下步骤得到：
Step 1. 找到一个大于等于num的2的幂，这个值为V，V可以通过下面公式得到：
V = Power(2,Ceiling(Log(2,num)))

例如：刚才设置了4个分区，num=4，Log(2,4)=2,Ceiling(2)=2,power(2,2)=4,即V=4
Step 2. 设置N=F(column_list)&(V-1)

例如：刚才V=4，store_id=234对应的N值，N = 234&（4-1） =2
Step 3. 当N>=num,设置V=Ceiling(V/2),N=N&(V-1)

例如：store_id=234,N=2<4,所以N就取值2，即可。
假设上面算出来的N=5，那么V=Ceiling(4/2)=2,N=5&(2-1)=1,即在第一个分区。

线性Hash的优点和不足 优点：在分区维护（增加，删除，合并，拆分分区）时，MySQL能够处理得更加迅速。缺点：与常规Hash分区相比，线性Hash各个分区之间的数据分布不太均衡。

11. Key分区

类似Hash分区，Hash分区允许使用用户自定义的表达式，但Key分区不允许使用用户自定义的表达式。Hash仅支持整数分区，而Key分区支持除了Blob和text的其他类型的列作为分区键。语法:

partition by key(exp) partitions 4;//exp是零个或多个字段名的列表

1	partition by key(exp) partitions 4;//exp是零个或多个字段名的列表

key分区的时候，exp可以为空，如果为空，则默认使用主键作为分区键，没有主键的时候，会选择非空惟一键作为分区键。

12. 子分区

分区表中对每个分区再次分割，又成为复合分区。

13. 分区对于NULL值的处理

MySQ允许分区键值为NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。

注意
Range分区中：NULL值被当作最小值来处理
List分区中：NULL值必须出现在列表中，否则不被接受
Hash/Key分区中：NULL值会被当作零值来处理

14. 分区管理

分区管理包括对于分区的增加，删除，以及查询。

增加分区：
对于Range分区和LIst分区来说：

alter table table_name add partition (partition p0 values ...(exp))

1

alter table table_name add partition (partition p0 values ...(exp))

values后面的内容根据分区的类型不同而不同。
对于Hash分区和Key分区来说：

alter table table_name add partition partitions 8;

1

alter table table_name add partition partitions 8;

上面的语句，指的是新增8个分区。
删除分区
对于Range分区和List分区：

alter table table_name drop partition p0; //p0为要删除的分区名称

1

alter table table_name drop partition p0; //p0为要删除的分区名称

删除了分区，同时也将删除该分区中的所有数据。同时，如果删除了分区导致分区不能覆盖所有值，那么插入数据的时候会报错。
对于Hash和Key分区：

alter table table_name coalesce partition 2; //将分区缩减到2个

1

alter table table_name coalesce partition 2; //将分区缩减到2个

coalesce [ˌkəʊəˈles] vi. 联合，合并

分区查询 1）查询某张表一共有多少个分区

mysql> select 
 ->   partition_name,
 ->   partition_expression,
 ->   partition_description,
 ->   table_rows
 -> from 
 ->   INFORMATION_SCHEMA.partitions
 -> where
 ->   table_schema='test'
 ->   and table_name = 'emp';
+----------------+----------------------+-----------------------+------------+
| partition_name | partition_expression | partition_description | table_rows |
+----------------+----------------------+-----------------------+------------+
| p0             | store_id             | 10                    |          0 |
| p1             | store_id             | 20                    |          1 |
+----------------+----------------------+-----------------------+------------+

mysql> select

-> partition_name,

-> partition_expression,

-> partition_description,

-> table_rows

-> from

-> INFORMATION_SCHEMA.partitions

-> where

-> table_schema='test'

-> and table_name = 'emp';

+----------------+----------------------+-----------------------+------------+

+----------------+----------------------+-----------------------+------------+

| p0 | store_id | 10 | 0 |

| p1 | store_id | 20 | 1 |

+----------------+----------------------+-----------------------+------------+

即，可以从information_schema.partitions表中查询。
2）查看执行计划，判断查询数据是否进行了分区过滤

mysql> explain partitions select * from emp where store_id=10 G;
*************************** 1. row ***************************
        id: 1
select_type: SIMPLE
     table: emp
partitions: p1
      type: system
possible_keys: NULL
       key: NULL
   key_len: NULL
       ref: NULL
      rows: 1
     Extra: 
1 row in set (0.00 sec)

mysql> explain partitions select * from emp where store_id=10 G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: emp

partitions: p1

type: system

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1

Extra:

1 row in set (0.00 sec)

上面的结果：partitions:p1 表示数据在p1分区进行检索。

[参考资料] 《深入MySQL数据库开发、优化与管理维护（第2版）》
《高性能MySQL》

原文出处：一条大河波浪宽

以上所述就是小编给大家介绍的《MySQL 分区表》，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对码农网的支持！

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

论因特网

[美] 休伯特·L.德雷福斯 / 喻向午、陈硕 / 河南大学出版社 / 2015-5 / 32.00

本书是与日俱增的关于因特网利弊之文献的重要补充。 ——《哲学评论》关于因特网种种承诺的一次清晰辨析……以哲学家的眼光审视一个影响我们所有人的问题。 ——《普遍存在》杂志 ……一场精心设计的论战……我们需要更多德雷福斯这样的老师，将网络融入依然具有深邃人性的课程。 ——亚当•莫顿（出自《泰晤士报文学增刊》）在互联网世界，不管你是菜鸟，还是浸淫其中已久—......一起来看看《论因特网》这本书的介绍吧!

码农工具

MySQL 分区表

1. 什么是表分区？

2. 表分区与分表的区别

3. 表分区有什么好处？

4. 分区表的限制因素

5. 如何判断当前MySQL是否支持分区？

6. MySQL支持的分区类型有哪些？

7. Range分区

8. LIST分区

9. Columns分区

10. Hash分区

11. Key分区

12. 子分区

13. 分区对于NULL值的处理

14. 分区管理

论因特网

RGB CMYK 转换工具

HEX HSV 转换工具

HSV CMYK 转换工具