MySQL表分区

columns 分区： columns分区是range分区或list分区的一种变体，支持非整形字段作为 分区的键，也可以用多个字段组合起来作为分区的键。 columns分区可允许使用的分区键类型有： 1. 所有的整形：tinyint, smallint, mediumint, int , bigint (和range分 区和list分区相同) 不包括decimal和float这种数字类型的。 2. date 和 datetime 3. 字符型：chra, varchar, binary, varbinary 不包括text和blob型
子分区:
对于已经通过RANGE或LIST分区了的表可以再进行子分区。子分区既可以使用 HASH希分区，也可以使ቤተ መጻሕፍቲ ባይዱKEY分区。这也被称为复合分区（composite partitioning）。 例如： create table ts (id int, purchased date) partition by range(YEAR(purchased)) subpartition by hash(TO_DAYS(purchased)) subpartition 2 ( partition p0 values less than (1990), partition p1 values less than (2000), partition p2 values less than MAXVALUE )；
也可以通过线性key分割一个表。下面是一个简单的例子： create table tk ( col1 int not null, col2 char(5), col3 date ) partition by linear key (col1) partitions 3;
在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作 用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模 数算法。关于该算法及其蕴涵式的描述请参考线性哈希功能。
range columns分区： create table test2 ( a int, b int, c char(3), d int ) partition by range columns(a,d,c) ( partition p0 values less than (5,10,'ggg'), partition p1 values less than (10,20,'mmm'), partition p2 values less than (15,30,'sss'), partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE) );
4. 涉及到例如sum() 和 count()这样聚合函数的查询，可以很容易地进行并 行处理。这种查询的一个简单例子如 “select salesperson_id, count(orders) as order_total from sales group by salesperson_id；”。 通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只 需通过总计所有分区得到的结果。 * 5. 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
例如，假设表t1，使用线性哈希分区且有4个分区，是通过下面的语句创建 的： create table t1 ( col1 int not null, col2 char (5), col3 date ) partition by linear key(col1) partitions 4;
现在假设要插入两行记录到表t1中，其中一条记录col3列值为'200304-14'，另一条记录col3列值为'1998-10-19'。 第一条记录将要保存到的分区确定如下： V = POWER(2, CEILING(LOG(2,5))) = 8 N = YEAR('2003-04-14') & (8 - 1) = 2003 & 7 =3 (3 >= 4 为假（FALSE）: 记录将被保存到#3号分区中)
要使用hash分区来分割一个表，要在create table 语句上添加一个 “partition by hash (expr)”子句，其中“expr”是一个返回一个整数的 表达式。它可以仅仅是字段类型为mysql 整型的一列的名字。此外，需要 在后面再添加一个“partitions num”子句，其中num 是一个非负的整数， 它表示表将要被分割成分区的数量。 如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。 如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语 法错误。
分区的优点： 1. 与单个磁盘或文件系统分区相比，可以存储更多的数据。 2. 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关 的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过 程又可以通过为那些新数据专门增加一个新的分区来很方便地实现。 3. 一些查询可以得到极大的优化，这主要是借助于满足一个给定where 语 句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩 余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分 区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
假设一个表达式expr, 当使用线性哈希功能时，记录将要保存到的分区 是num 个分区中的分区N，其中N是根据下面的算法得到： 1. 找到下一个大于num.的、2的幂，我们把这个值称为V ，它可以通过下 面的公式得到： 2. V = POWER(2, CEILING(LOG(2, num))) 3. 设置 N = F (column_list) & (V - 1). 4. 当 N >= num: 设置 V = CEIL(V / 2) 设置 N = N & (V - 1)
分区的类型: 1. RANGE分区 2. LIST分区 3. COLUMNS分区 4. HASH分区 5. KEY分区
range 分区：基于属于一个给定连续区间的列值进行分配 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 maxvalue )；
第二条记录将要保存到的分区序号计算如下： V=8 N = YEAR('1998-10-19') & (8-1) = 1998 & 7 =6 (6 >= 4 为真（TRUE）: 还需要附加的步骤) N = 6 & (CEILING(8 / 2) – 1) =6&3 =2 (2 >= 4 为假（FALSE）: 记录将被保存到#2分区中)
list 分区：类似range分区,它们的主要区别在于，list分区中每个分区的定义和选择是基于某列的值从属于一个集合，而range分区是从属 于一个连续区间值的集合 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, store_id int ) partition by list(store_id) partition pnorth values in (3,5,6,9,17), partition peast values in (1,2,10,11,19,20), partition pwest values in (4,12,13,14,18), partition pcentral values in (7,8,15,16) )； 如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“insert”查询将失败并报错
hash分区： 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要 插入到表中的这些行的列值进行计算。这个函数可以包含mysql 中有效的、 产生非负整数值的任何表达式。 hash分区主要用来确保数据在预先确定数目的分区中平均分布。在 range和list分区中，必须明确指定一个给定的列值或列值集合应该保存在 哪个分区中；而在hash分区中，mysql 自动完成这些工作，我们所要做的 只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将 要被分割成的分区数量。
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, store_id int ) partition by hash(year(hired)) partitions 4；
list columns分区： set names utf8; create table customers ( name varchar(25), city varchar(15) ) partition by list columns(city) ( partition p0 values in('Beijing', 'Shanghai', 'Tianjin'), partition p1 values in('Guanzhou', 'Shenzhen', 'Xiamen') );
MySQL表分区
MySQL表分区 houdelei@comsenz.com
一、什么是表分区 二、为什么要对表进行分区 三、分区的类型 四、分区的管理 五、表分区的局限性
表分区通俗来讲就是允许把一个数据表根据一定的规则，跨文件系统划 分成多个可以设置为任意大小的部分。 MySQL从5.1起开始支持表分区，安装了5.1的MySQL默认是开启表分 区支持的。 可以通过： show variables like ‘%partition%’; 来查看你的MySQL是否支持分区。
mysql还支持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使用的一个线 性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数。线 性哈希分区和常规哈希分区在语法上的唯一区别在于，在“partition by”子句中添加 “linear”关键字，如下面所示： 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, store_id int ) partition by linear hash(year(hired)) partitions 4；
key 分区： 按照key进行分区类似于按照hash分区，除了hash分区使用的用户定义的表 达式，而key分区的哈希函数是由mysql 服务器提供。 create table tk ( col1 int not null, col2 char(5), col3 date ) partition by linear key (col1) partitions 3;