mysql的表分区

tedwhy

　　一、什么是表分区
　　通俗地讲表分区是将一大表，根据条件分割成若干个小表。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代码 http://lehsyh.iteye.com/images/spinner.gif　　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 (21)
　　)；
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 (21)
　　
)；
　　按照这种分区方案，在商店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代码 http://lehsyh.iteye.com/images/icon_copy.gifhttp://lehsyh.iteye.com/images/icon_star.pnghttp://lehsyh.iteye.com/images/spinner.gif

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

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

• 　　CREATETABLE employees (
  
• 　　id INTNOTNULL,
  
• 　　fname VARCHAR(30),
  
• 　　lname VARCHAR(30),
  
• 　　hired DATENOTNULLDEFAULT'1970-01-01',
  
• 　　separated DATENOTNULLDEFAULT'9999-12-31',
  
• 　　job_code INTNOTNULL,
  
• 　　store_id INTNOTNULL
  
• 　　)
  
• 　　PARTITION BY RANGE (job_code) (
  
• 　　PARTITION p0 VALUES LESS THAN (100),
  
• 　　PARTITION p1 VALUES LESS THAN (1000),
  
• 　　PARTITION p2 VALUES LESS THAN (10000)
  
• 　　)；
  

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 (job_code) (
　　
PARTITION p0 VALUES LESS THAN (100),
　　
PARTITION p1 VALUES LESS THAN (1000),
　　
PARTITION p2 VALUES LESS THAN (10000)
　　
)；
　　在这个例子中, 店内工人相关的所有行将保存在分区p0中，办公室和支持人员相关的所有行保存在分区p1中，管理层相关的所有行保存在分区p2中。
　　在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (= 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代码 http://lehsyh.iteye.com/images/icon_copy.gifhttp://lehsyh.iteye.com/images/icon_star.pnghttp://lehsyh.iteye.com/images/spinner.gif

• 　　CREATETABLE tk (
  
• 　　col1 INTNOTNULL,
  
• 　　col2 CHAR(5),
  
• 　　col3 DATE
  
• 　　)
  
• 　　PARTITION BY LINEAR KEY (col1)
  
• 　　PARTITIONS 3;
  

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）算法得到，而不是通过模数算法。
　　转自：http://lehsyh.iteye.com/blog/732719