一、什么是表分区:
通俗地讲表分区是将一大表,根据条件分割成若干个小表。
mysql5.1开始支持数据表分区了。
二、为什么要对表进行分区:
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。
三、分区的优点:
1、单个磁盘或文件系统分区相比,可以存储更多的数据。
2、提高数据管理性,对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。
相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
3、提高查询效率,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,
这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,
所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
4、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
四、分区类型:
1、RANGE 分区:
基于属于一个给定连续区间的列值,把多行分配给分区。
2、LIST 分区:
类似于按 RANGE 分区,区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。
3、HASH 分区:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。
这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
4、KEY 分区:
类似于按 HASH 分区,区别在于 KEY 分区只支持计算一列或多列,且 MySQL 服务器提供其自身的哈希函数。
必须有一列或多列包含整数值。
五、详情:
1、RANGE 分区:
基于属于一个给定连续区间的列值,把多行分配给分区。
这些区间要连续且不能相互重叠,使用 VALUES LESS THAN 操作符来进行定义。
1、整数值分区:
PARTITION BY RANGE (字段名) (
PARTITION 分区名 VALUES LESS THAN (整数值),
PARTITION 分区名 VALUES LESS THAN (11),
PARTITION 分区名 VALUES LESS THAN MAXVALUE # 表示最大的可能的整数值
);
2、按日期分区:
PARTITION BY RANGE (YEAR (字段名)) ( # YEAR() 日期-年
PARTITION 分区名 VALUES LESS THAN (年),
PARTITION 分区名 VALUES LESS THAN (1996),
PARTITION 分区名 VALUES LESS THAN MAXVALUE
);
2、LIST 分区:
类似于按 RANGE 分区,区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST 分区通过使用 “PARTITION BY LIST(expr)” 来实现,其中 “expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过 “VALUES IN (value_list)” 的方式来定义每个分区,其中 “value_list” 是一个通过逗号分隔的整数列表。 注:在MySQL 5.1中,当使用 LIST 分区时,有可能只能匹配整数列表。
PARTITION BY LIST (字段名)
PARTITION 分区名 VALUES IN (值, 值, ...),
PARTITION 分区名 VALUES IN (1, 3, 5, 100),
PARTITION 分区名 VALUES IN (7, 8, 10)
);
3、HASH 分区:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
要使用 HASH 分区来分割一个表,要在 CREATE TABLE 语句上添加一个 “PARTITION BY HASH (expr)” 子句,其中 “expr” 是一个返回一个整数的表达式。它可以仅仅是字段类型为 MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个 “PARTITIONS num” 子句,其中 num 是一个非负的整数,它表示表将要被分割成分区的数量。
1、HASH:
PARTITION BY HASH (字段名)
PARTITIONS 4; # PARTITIONS 值;
2、LINER HASH:
PARTITION BY LINEAR HASH(YEAR(字段)) # YEAR() 日期-年
PARTITIONS 4; # PARTITIONS 值;
4、KSY 分区:
类似于按 HASH 分区,区别在于 KEY 分区只支持计算一列或多列,且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
PARTITION BY LINEAR KEY (字段名)
PARTITIONS 3; # PARTITIONS 值;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。
六、删除分区:
ALTER TABLE 表名 DROP PARTITION 分区名;