分类 技术安全 下的文章

MySQL的表分区

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

二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。
分区的一些优点包括:
1)、与单个磁盘或文件系统分区相比,可以存储更多的数据
2)、
对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那
些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的
优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分
区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT
salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY
salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
三、分区类型

· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

  • RANGE分区

基于属于一个给定连续区间的列值,把多行分配给分区。
这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。以下是实例。

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INTNOTNULL,
  8. store_id INTNOTNULL
  9. )
  10. partition BYRANGE (store_id) (
  11. partition p0 VALUESLESS THAN (6),
  12. partition p1 VALUESLESS THAN (11),
  13. partition p2 VALUESLESS THAN (16),
  14. partition p3 VALUESLESS THAN (21)
  15. );

按照这种分区方案,在商店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代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INTNOTNULL,
  8. store_id INTNOTNULL
  9. )
  10. PARTITION BYRANGE (store_id) (
  11. PARTITION p0 VALUESLESS THAN (6),
  12. PARTITION p1 VALUESLESS THAN (11),
  13. PARTITION p2 VALUESLESS THAN (16),
  14. PARTITION p3 VALUESLESS THAN MAXVALUE
  15. );

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

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INTNOTNULL,
  8. store_id INTNOTNULL
  9. )
  10. PARTITION BYRANGE (job_code) (
  11. PARTITION p0 VALUESLESS THAN (100),
  12. PARTITION p1 VALUESLESS THAN (1000),
  13. PARTITION p2 VALUESLESS THAN (10000)
  14. );


在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。
除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE
(日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分
区模式的CREATE TABLE 语句的一个例子如下所示:

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INT,
  8. store_id INT
  9. )
  10. PARTITION BYRANGE (YEAR(separated)) (
  11. PARTITION p0 VALUESLESS THAN (1991),
  12. PARTITION p1 VALUESLESS THAN (1996),
  13. PARTITION p2 VALUESLESS THAN (2001),
  14. PARTITION p3 VALUESLESS THAN MAXVALUE
  15. );

在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。
RANGE分区在如下场合特别有用:
1)、 当需要删除一个分区上的“旧的”数据时,只删除分区即可
如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION
p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees
WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE
YEAR(separated) = 2000 GROUP BY
store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
注释:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

  • LIST分区

类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表
注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INT,
  8. store_id INT
  9. );


假定有20个音像店,分布在4个有经销权的地区,如下表所示:
====================
地区 商店ID 号
------------------------------------
北区 3, 5, 6, 9, 17
东区 1, 2, 10, 11, 19, 20
西区 4, 12, 13, 14, 18
中心区 7, 8, 15, 16
====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INT,
  8. store_id INT
  9. )
  10. PARTITION BYLIST(store_id)
  11. PARTITION pNorth VALUESIN(3,5,6,9,17),
  12. PARTITION pEast VALUESIN(1,2,10,11,19,20),
  13. PARTITION pWest VALUESIN(4,12,13,14,18),
  14. PARTITION pCentral VALUESIN(7,8,15,16)
  15. );

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录
(行)可以使用查询“ALTER TABLE employees DROP PARTITION
pWest;”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees
WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。
【要点】:如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。例如,假定LIST分区的采用上面的方案,下面的查询将失败:

Sql代码
  1. INSERTINTOemployeesVALUES(224,'Linus','Torvalds','2002-05-01','2004-10-12', 42, 21);


这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区,与HASH和KEY分区结合起来生成复合的子分区也是可能的。

  • HASH分区

基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH
(expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL
整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num
是一个非负的整数,它表示表将要被分割成分区的数量。

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INT,
  8. store_id INT
  9. )
  10. PARTITION BYHASH(store_id)
  11. PARTITIONS 4;

如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,
这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。

  • LINER HASH

MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字。

Sql代码
  1. CREATETABLEemployees (
  2. id INTNOTNULL,
  3. fname VARCHAR(30),
  4. lname VARCHAR(30),
  5. hired DATENOTNULLDEFAULT'1970-01-01',
  6. separated DATENOTNULLDEFAULT'9999-12-31',
  7. job_code INT,
  8. store_id INT
  9. )
  10. PARTITION BYLINEAR HASH(YEAR(hired))
  11. PARTITIONS 4;

假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:
1. 找到下一个大于num.的、2的幂,我们把这个值称为V ,它可以通过下面的公式得到:
2. V = POWER(2, CEILING(LOG(2, num)))
(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。
3. 设置 N = F(column_list) & (V - 1).
4. 当 N >= num:
· 设置 V = CEIL(V / 2)
· 设置 N = N & (V - 1)
例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插入两行记录到表t1中,其中一条记录col3列值为'2003-04-14',另一条记录col3列值为'1998-10-19'。第一条记录将要保存到的分区确定如下:
V = POWER(2, CEILING(LOG(2,7))) = 8
N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下:
V = 8
N = YEAR('1998-10-19') & (8-1)
= 1998 & 7
= 6
(6 >= 4 为真(TRUE): 还需要附加的步骤)
N = 6 & CEILING(5 / 2)
= 6 & 3
= 2

(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用
常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。

  • KSY分区

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

Sql代码
  1. CREATETABLEtk (
  2. col1 INTNOTNULL,
  3. col2 CHAR(5),
  4. col3 DATE
  5. )
  6. PARTITION BYLINEARKEY(col1)
  7. PARTITIONS 3;


在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。

mysql分区功能详细介绍,以及实例

3,list分区
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分 区是从属于一个连续区间值的集合。

  1. //这种方式失败  
  2. mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
  3.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',  
  4.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',  
  5.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',  
  6.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女',  
  7.  ->   PRIMARY KEY (`id`)  
  8.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
  9.  -> PARTITION BY LIST (province_id) (  
  10.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
  11.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
  12.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
  13.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
  14.  -> );  
  15. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
  16.  
  17. //这种方式成功 
  18. mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
  19.  ->   `id` int(11) NOT NULL  COMMENT '用户ID',  
  20.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '', 
  21.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
  22.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女'  
  23.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  24.  -> PARTITION BY LIST (province_id) (  
  25.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
  26.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
  27.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
  28.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
  29.  -> );  
  30. Query OK, 0 rows affected (0.33 sec)  

上面的这个创建list分区时,如果有主銉的话,分区时主键必须在其中,不然就会报错。如果我不用主键,分区就创建成功了,一般情况下,一个张表肯定会有一个主键,这算是一个分区的局限性吧。
如果对数据进行测试,请参考range分区的测试来操作
4,hash分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以 及指定被分区的表将要被分割成的分区数量。

  1. mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
  2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
  3.  ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
  4.  ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
  5.  ->   PRIMARY KEY (`id`)  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
  7.  -> PARTITION BY HASH(id)  
  8.  -> PARTITIONS 3;  
  9. Query OK, 0 rows affected (0.06 sec)  

测试请参考range分区的操作
5,key分区
按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用 户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。

  1. mysql> CREATE TABLE IF NOT EXISTS `key_part` (  
  2.  ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
  4.  ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  7.  -> PARTITION BY LINEAR HASH(YEAR(create_time))  
  8.  -> PARTITIONS 3;  
  9. Query OK, 0 rows affected (0.07 sec)  

测试请参考range分区的操作
6,子分区
子分区是分区表中每个分区的再次分割,子分区既可以使用HASH希分区,也可以使用KEY分区。这 也被称为复合分区(composite partitioning)。

1,如果一个分区中创建了子分区,其他分区也要有子分区

2,如果创建了了分区,每个分区中的子分区数必有相同

3,同一分区内的子分区,名字不相同,不同分区内的子分区名子可以相同(5.1.50不适用)

  1. mysql> CREATE TABLE IF NOT EXISTS `sub_part` (  
  2.  ->   `news_id` int(11) NOT NULL  COMMENT '新闻ID',  
  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
  4.  ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT '来源IP',  
  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间'  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  7.  -> PARTITION BY RANGE(YEAR(create_time))  
  8.  -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  
  9.  -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  
  10.  -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  
  11.  -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  
  12.  -> );  
  13. Query OK, 0 rows affected (0.07 sec)  

官方网站说不同分区内的子分区可以有相同的名字,但是mysql5.1.50却不行会提示以下错误
ERROR 1517 (HY000): Duplicate partition name s1
三,分区管理
1,删除分区

  1. mysql> alter table user drop partition p4;  

2,新增分区

  1. //range添加新分区  
  2. mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
  3. Query OK, 0 rows affected (0.06 sec)  
  4. Records: 0  Duplicates: 0  Warnings: 0  
  5.   
  6. //list添加新分区  
  7. mysql> alter table list_part add partition(partition p4 values in (25,26,28));  
  8. Query OK, 0 rows affected (0.01 sec)  
  9. Records: 0  Duplicates: 0  Warnings: 0  
  10.   
  11. //hash重新分区  
  12. mysql> alter table hash_part add partition partitions 4;  
  13. Query OK, 0 rows affected (0.12 sec)  
  14. Records: 0  Duplicates: 0  Warnings: 0  
  15.   
  16. //key重新分区  
  17. mysql> alter table key_part add partition partitions 4;  
  18. Query OK, 1 row affected (0.06 sec)    //有数据也会被重新分配  
  19. Records: 1  Duplicates: 0  Warnings: 0  
  20.   
  21. //子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的  
  22. mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);  
  23. Query OK, 0 rows affected (0.02 sec)  
  24. Records: 0  Duplicates: 0  Warnings: 0  
  25.   
  26. mysql> show create table sub1_part\G;  
  27. *************************** 1. row ***************************  
  28.  Table: sub1_part  
  29. Create Table: CREATE TABLE `sub1_part` (  
  30.  `news_id` int(11) NOT NULL COMMENT '新闻ID',  
  31.  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容',  
  32.  `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
  33.  `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间'  
  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  35. !50100 PARTITION BY RANGE (YEAR(create_time))  
  36. SUBPARTITION BY HASH (TO_DAYS(create_time))  
  37. (PARTITION p0 VALUES LESS THAN (1990)  
  38.  (SUBPARTITION s0 ENGINE = InnoDB,  
  39.  SUBPARTITION s1 ENGINE = InnoDB,  
  40.  SUBPARTITION s2 ENGINE = InnoDB),  
  41.  PARTITION p1 VALUES LESS THAN (2000)  
  42.  (SUBPARTITION s3 ENGINE = InnoDB,  
  43.  SUBPARTITION s4 ENGINE = InnoDB,  
  44.  SUBPARTITION good ENGINE = InnoDB),  
  45.  PARTITION p2 VALUES LESS THAN (3000)  
  46.  (SUBPARTITION tank0 ENGINE = InnoDB,  
  47.  SUBPARTITION tank1 ENGINE = InnoDB,  
  48.  SUBPARTITION tank3 ENGINE = InnoDB),  
  49.  PARTITION p3 VALUES LESS THAN MAXVALUE  
  50.  (SUBPARTITION p3sp0 ENGINE = InnoDB,    //子分区的名子是自动生成的  
  51.  SUBPARTITION p3sp1 ENGINE = InnoDB,  
  52.  SUBPARTITION p3sp2 ENGINE = InnoDB))  
  53. 1 row in set (0.00 sec)  

3,重新分区

  1. //range重新分区  
  2. mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
  3. Query OK, 11 rows affected (0.08 sec)  
  4. Records: 11  Duplicates: 0  Warnings: 0  
  5.   
  6. //list重新分区  
  7. mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  
  8. Query OK, 0 rows affected (0.28 sec)  
  9. Records: 0  Duplicates: 0  Warnings: 0  
  10.   
  11. //hash和key分区不能用REORGANIZE,官方网站说的很清楚  
  12. mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;  
  13. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1  

四,分区优点

1,分区可以分在多个磁盘,存储更大一点

2,根据查找条件,也就是where后面的条件,查找只查找相应的分区不用全部查找了

3,进行大数据搜索时可以进行并行处理。

4,跨多个磁盘来分散数据查询,来获得更大的查询吞吐量

perl 计算硬盘利用率

perl 计算硬盘利用率, 以%来查看硬盘资源是否存在IO消耗cpu资源情况;

部份代码参考了iostat源码;

#!/usr/bin/perl
use Time::HiRes qw(gettimeofday);
use POSIX;

$SLEEPTIME=3;
sub getDiskUtl()
{
$clock_ticks = POSIX::sysconf( &POSIX::_SC_CLK_TCK );

# ========= get start time ===============
($start_sec, $start_usec) = gettimeofday;

# ========== 在计时开始时,取出操作时间
my $tot_ticks_0 = `grep "0 sda" /proc/diskstats | awk '{print \$13}'`;

sleep($SLEEPTIME);
#for(1..$SLEEPTIME*1000000){}

# ========= get end time ===============
($end_sec, $end_usec) = gettimeofday;

# ========== 在计时结束时,再取出一次操作时间
my $tot_ticks_1 = `grep "0 sda" /proc/diskstats | awk '{print \$13}'`;

if ($end_usec < $start_usec)
{
$time_used = (($end_sec - $start_sec - 1)*1e6 + ($end_usec+1e6 - $start_usec)) / 1e3; # Microseconds = Millisecond * 10e3;
}
else
{
$time_used = (($end_sec - $start_sec)*1e6 + ($end_usec - $start_usec)) / 1e3; # Microseconds = Millisecond * 1e3
}

my $disk_util = ($tot_ticks_1 - $tot_ticks_0)/$time_used*$clock_ticks/10.0;
}
#printf("\$start_sec is:$start_sec\t \$end_sec is:$end_sec\t\$end_usec is:$end_usec\t\$start_usec is:$start_usec\n");

#printf("\$tot_ticks_1 is %d\t\$tot_ticks_0 is %d \t\$time_used is %d\t\$clock_ticks is %d\n",
# $tot_ticks_1, $tot_ticks_0, $time_used, $clock_ticks);
printf("%8.8f\%\n", getDiskUtl());