MySQL分区表的应用介绍
日期：2014-05-16 浏览次数：20741 次
MySQL使用分区表的好处：
1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
2，方便维护，通过删除分区来删除老的数据。
3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区：
????????? RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
· ? ? ? ??LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
·???????? HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· ? ? ? ??KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
一般用得多的是range分区和list分区。
RANGE分区
这里以一个销售的业务来做测试
销售表有日期/商品/销售额三个字段
测试数据从2010年1月1日至2010年9月31日
以“月”为单位进行分区
初期分区定义
首先需要查看，当前数据库是否支持分区
mysql>SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name?????| Value |
?
?
?
?
?
+-------------------+-------+
|?have_partitioning | YES???|
?
+-------------------+-------+
1 row in set (0.03 sec)
创建分区表，按照年月的方式分区。
mysql> CREATE TABLE sale_data (
????->???sale_date??DATETIME NOT NULL,
?
????->???sale_item??VARCHAR(2) NOT NULL ,
?
????->???sale_money DECIMAL(10,2) NOT NULL
?
????-> )
?
????-> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (
?
????->???PARTITION p201001 VALUES LESS THAN (201002),
?
????->???PARTITION p201002 VALUES LESS THAN (201003),
?
????->???PARTITION p201003 VALUES LESS THAN (201004),
?
????->???PARTITION p201004 VALUES LESS THAN (201005),
?
????->???PARTITION p201005 VALUES LESS THAN (201006),
?
????->???PARTITION p201006 VALUES LESS THAN (201007),
?
????->???PARTITION p201007 VALUES LESS THAN (201008),
?
????->???PARTITION p201008 VALUES LESS THAN (201009),
?
????->???PARTITION p201009 VALUES LESS THAN (201010),
?
? ? -> ? PARTITION pcatchall VLAUES LESS THAN MAXVALUE
????-> );
?
Query OK, 0 rows affected (0.20 sec)
新增分区
mysql>?ALTER TABLE?sale_data
????->???ADD PARTITION?(PARTITION p201010 VALUES LESS THAN (201011));
?
Query OK, 0 rows affected (0.36 sec)
Records: 0??Duplicates: 0??Warnings: 0
?
删除分区
--当删除了一个分区，也同时删除了该分区中所有的数据。
mysql>?ALTER TABLE?sale_data?DROP PARTITION?p201010;
Query OK, 0 rows affected (0.22 sec)
Records: 0??Duplicates: 0??Warnings: 0
?
分区的合并
下面的SQL，将p201001 - p201009?合并为3个分区p2010Q1 - p2010Q3
?
mysql>?ALTER TABLE?sale_data
????->???REORGANIZE PARTITION?p201001,p201002,p201003,
?
????->????????????????????????p201004,p201005,p201006,
?
????->????????????????????????p201007,p201008,p201009?INTO
?
????-> (
?
????->???PARTITION p2010Q1 VALUES LESS THAN (201004),
?
????->???PARTITION p2010Q2 VALUES LESS THAN (201007),
?
????->???PARTITION p2010Q3 VALUES LESS THAN (201010)
?
????-> );
?
Query OK, 0 rows affected (1.14 sec)
Records: 0??Duplicates: 0??Warnings: 0
?
分区的拆分
下面的SQL，将p2010Q1?分区，拆分为s2009?与s2010?两个分区
?
mysql>?ALTER TABLE?sale_data?REORGANIZE PARTITION?p2010Q1?INTO?(
?
????->?????PARTITION s2009 VALUES LESS THAN (201001),
?
????->?????PARTITION s2010 VALUES LESS THAN (201004)
?
????-> );
?
Query OK, 0 rows affected (0.36 sec)
Records: 0??Duplicates: 0??Warnings: 0
?
一个利用不同物理位置数据源做分区的例子：
CREATE TABLE ts (id INT, purchased DATE)?
??? ENGINE=innodb?
??? PARTITION BY RANGE(YEAR(purchased))?
??? SUBPARTITION BY HASH(id)?
