文档章节

MySQL 分区表

一条大河波浪宽
 一条大河波浪宽
发布于 2015/12/21 23:14
字数 2658
阅读 16652
收藏 499

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

1. 什么是表分区?

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

2. 表分区与分表的区别

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

3. 表分区有什么好处?

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

4. 分区表的限制因素

1)一个表最多只能有1024个分区 2) MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.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)

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)
)

例如:

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

RANGE分区存在的问题

  1. 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

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

8. LIST分区

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

    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)
    -> );

注意 如果插入的记录对应的分区键的值不在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;

上面的语句,根据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;

与常规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是零个或多个字段名的列表

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

12. 子分区

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

13. 分区对于NULL值的处理

MySQ允许分区键值为NULL,分区键可能是一个字段或者一个用户定义的表达式。一般情况下,MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。
注意
Range分区中:NULL值被当作最小值来处理
List分区中:NULL值必须出现在列表中,否则不被接受
Hash/Key分区中:NULL值会被当作零值来处理

14. 分区管理

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

  1. 增加分区:
    对于Range分区和LIst分区来说:
alter table table_name add partition (partition p0 values ...(exp))

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

alter table table_name add partition partitions 8;

上面的语句,指的是新增8个分区 。
2. 删除分区
对于Range分区和List分区:

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

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

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

coalesce [ˌkəʊəˈles] vi. 联合,合并
3. 分区查询 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 |
+----------------+----------------------+-----------------------+------------+

即,可以从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)

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

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

© 著作权归作者所有

共有 人打赏支持
一条大河波浪宽
粉丝 115
博文 27
码字总数 42547
作品 0
海淀
CEO
私信 提问
加载中

评论(21)

d
ddxg
上回不小心复制了 最近学习分区,有些问题问下,如果我对一个表中的sex字段进行分区,但是我表里面的id字段是属于主键,那么解决的方法是删除id的主键吗
d
ddxg
表分区
是指根据一定的规则,将数据库的一张表进行分解成更多更小的,容易管理的部分,从逻辑上看只有一张表,但是底层却是有多个物理分区组成
表分区带来的好处
1.分区表的数据可以分布在不同的物理设备上,从而高效的利用多个硬件设备
2.和单个磁盘或者文件系统相比,可以储存更多的数据
3.优化查询 在where语句中包含分区条件时,可以只扫描其中的一个分区或者多个分区来提高查询效率
,涉及sum和count的时候可以在多个分区上进行并发处理,汇总结果;
4.分区表更加容易维护 例:想批量删除数据的时候可以删除整分区就行
5.可以使用分区表来避免某个特殊的瓶颈 ,例 InnoDB 的单个索引的互斥访问,ext3问价系统的inode的锁竞争等(现在不是很理解)
分区表带来的限制因素
1.一个表最多有1024个分区
2.MySQL5.1 中,分区表达式必须是整数,或者返回整数的表达式,在MySQL5.5中提供非整数表达式分区的支持.
3.如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都需要包含在其中
例 分区字段要么不包含主键和索引列,要么包含全部的主键和索引列
4.无法使用外键约束
5.mysql的分区适用于一个表的所有数据和索引,不能只对表数据分区不对索引分区,也不能只对索引分区不对表数据分区,也不能只对表的一部分数据分区
判断当前的mysql是否支持分区
show variables like '%partition%'


Range分区
利用取值范围进行分区,区间要连续且不能相互重叠

上面的语句是创建了一个test1表,并根据id字段进行分区,小于10的值存在分区p0,大于等于10,小于20的存在分区p1中
PS: 每个分区是按顺序定义的,从最低到最高,上述语句是从less than(10) 到less than(20) 依次下来的,顺序反了便会报错
例 : ERROR 1493(HY000):VALUES LESS THAN value must be strictly increasing for each partotion
range 分区存在的问题
1.range范围覆盖问题 当插入的是值不在分区定义的范围中的时候,插入语句会失败
例 根据上面的语句写法 我插入一条id为30的值会报错, 我们上面的分区最大值是20,插入30就
一条大河波浪宽
一条大河波浪宽

引用来自“zcoder”的评论

感谢分享 学习了

有分享才能共提高
zcoder
zcoder
感谢分享 学习了
一条大河波浪宽
一条大河波浪宽

引用来自“xwlyun”的评论

MySQL的分区好像是和一些NoSQL的分区道理差不多,楼主有兴趣可以了解一下。

NoSQL有好多,你说的是哪一款?
xwlyun
xwlyun
MySQL的分区好像是和一些NoSQL的分区道理差不多,楼主有兴趣可以了解一下。
一条大河波浪宽
一条大河波浪宽

引用来自“青岑”的评论

线性哈希中,“假设上面算出来的N=5,那么V=Ceiling(2.5)=3,N=234&(3-1)=1,即在第一个分区。”这一句不理解。
假如是5,由于num=4,满足N>=num,设置V=Ceiling(V/2),N=N&(V-1),由于V=4,所以V=Ceiling(4/2)=2,N=5&(2-1)=1,我文章中写错了,不好意思,掀桌子修正了
海綿寶寶
海綿寶寶
学习了
淮河一梦
淮河一梦
什么时候能提供自动分区的功能就好了
青岑
青岑
线性哈希中,“假设上面算出来的N=5,那么V=Ceiling(2.5)=3,N=234&(3-1)=1,即在第一个分区。”这一句不理解。
MySQL下的表分区简述

MySQL表分区就是把一张表根据设定好的条件下把表切分成若干个小表相互之间,在MySQL的5.1版本以后就开始支持表分区的功能,在使用表的分区后会使MySQL中大表在平时查询统计时性能提升。使用M...

往事_Jim_遗
2017/11/10
0
0
MySQL学习——分区表

MySQL存储引擎现状及发展趋势 MySQL官网有这样一句话: As of MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is removed in MySQL 8.0, when th......

沈渊
2017/09/03
0
0
MySQL表分区优势及分类简析 MySQL DBA学习

表分区是将一个表的数据按照一定规则水平划分成不同的逻辑块,并分别进行物理存储,这个规则就叫做分区函数,可以有不同的分区规则。通过show plugins语句查看当前MySQL是否支持表分区功能。...

mo默瑶
05/24
0
0
mysql分表,分区的区别和联系

一,什么是mysql分表,分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表,具体请看mysql分表的3种方法 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同...

haorizi
2013/03/11
0
1
MySQL表分区类型简析 数据库周末提升

  表分区是将一个表的数据按照一定规则水平划分成不同的逻辑块,并分别进行物理存储,这个规则就叫做分区函数,可以有不同的分区规则。通过show plugins语句查看当前MySQL是否支持表分区功...

zhouzhou2018
05/24
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Kafka+Flink 实现准实时异常检测系统

1.背景介绍 异常检测可以定义为“基于行动者(人或机器)的行为是否正常作出决策”,这项技术可以应用于非常多的行业中,比如金融场景中做交易检测、贷款检测;工业场景中做生产线预警;安防...

架构师springboot
9分钟前
0
0
DecimalFormat 类基本使用

/* * DecimalFormat 类主要靠 # 和 0 两种占位符号来指定数字长度 * 0 表示如果位数不足则以 0 填充 * # 表示只要有可能就把数字拉上这个位置 * */ public static void main(String[] args){...

嘴角轻扬30
26分钟前
1
0
This APT has Super Cow Powers.

在Debian/Ubuntu上,apt包管理器内嵌着一个彩蛋. 如果你在命令行界面输入 apt help 在最后一行能找到This APT has Super Cow Powers. 说明该apt具有超级牛力 牛力是个什么梗? 则说明你的系统...

taadis
43分钟前
1
0
起薪2万的爬虫工程师,Python需要学到什么程度才可以就业?

爬虫工程师的的薪资为20K起,当然,因为大数据,薪资也将一路上扬。那么,Python需要学到什么程度呢?今天我们来看看3位前辈的回答。 1、前段时间快要毕业,而我又不想找自己的老本行Java开发...

糖宝lsh
52分钟前
6
0
携手开发者共建云生态 首届腾讯云+社区开发者大会在京举办

本文由云+社区发表 北京时间12月15日,由腾讯云主办,极客邦科技、微信、腾讯TEG协办的首届腾讯云+社区开发者大会在北京朝阳悠唐皇冠假日酒店举办。在会上,腾讯云发布了重磅产品开发者平台以...

腾讯云加社区
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部