MySQL使用教程之分区表的使用方法(删除分区表)

这篇文章主要为大家详细介绍了MySQL使用教程之分区表的使用方法(删除分区表)，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

MySQL使用分区表的好处：

1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。2，方便维护，通过删除分区来删除老的数据。3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

MySQL可以建立四种分区类型的分区：

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 www.512pic.com

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

 -> ) www.512pic.com 

 -> 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) www.512pic.com 
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),
 www.512pic.com 
 -> 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) 
( 
PARTITION p0 VALUES LESS THAN (1990) 
( 
SUBPARTITION s0 //在大的分区下又有小的分区
DATA DIRECTORY='/usr/local/mysql/data0' //数据源
INDEX DIRECTORY='/usr/local/mysql/index0', //索引数据源
SUBPARTITION s1 
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1'
), 
PARTITION p1 VALUES LESS THAN (MAXVALUE) 
( 
SUBPARTITION s2 
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1', 
SUBPARTITION s3 
DATA DIRECTORY='/usr/local/mysql/data2'
INDEX DIRECTORY='/usr/local/mysql/index2'
) 
); 

分区索引的局限：1，所有分区都要使用同样的引擎。2，分区表的每一个唯一索引必须包含由分区函数引用的列。3，mysql能避免查询所有的分区，但仍然锁定了所有分区。4，分区函数能使用的函数和表达式有限，例如函数有上面的4种。5，分区不支持外键。 www.512pic.com6，不能使用LOAD INDEX INTO CACHE7，分区并不能总是改善性能，要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据：

代码如下:

mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fenqubiao | p_2010,p_2011 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

注：关于MySQL使用教程之分区表的使用方法(删除分区表)的内容就先介绍到这里，更多相关文章的可以留意