分区表的使用--mysql

MySQL使用分区表的好处：

1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。

2，方便维护，通过删除分区来删除老的数据。

3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。

MySQL可以建立四种分区类型的分区：(也可说有五种)

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

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

·         HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

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

一般用得多的是range分区和list分区。

---------------------------------------------------------------------------------------------------------------------------------------

1、分区表类型
RANGE分区、LIST分区、HASH分区、KEY分区、子分区

二、分区表的创建
1、RANGE分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

2、 LIST分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

Note：LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

3、 HASH分区

1 2 3 4 5 6 7 8 9 10 11

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;

3.1、LINEAR HASH分区

1 2 3 4 5 6 7 8 9 10 11

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;

Note：线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则，而常规哈希使用的是求哈希函数值的模数

4、KEY分区

1 2 3 4 5 6 7

CREATE  TABLE  tk (      col1 INT  NOT  NULL ,      col2 CHAR (5),      col3 DATE ) PARTITION  BY LINEAR  KEY  (col1) PARTITIONS 3;

Note：
1）按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

2）“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH，并且KEY分区只采用一个或多个列名的一个列表。

5、子分区

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

CREATE  TABLE  ts (id  INT , purchased DATE )      PARTITION BY  RANGE( YEAR (purchased))      SUBPARTITION BY  HASH(TO_DAYS(purchased))      (          PARTITION p0 VALUES  LESS THAN (1990)          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk0/data'                  INDEX DIRECTORY =  '/disk0/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk1/data'                  INDEX DIRECTORY =  '/disk1/idx'          ),          PARTITION p1 VALUES  LESS THAN (2000)          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk2/data'                  INDEX DIRECTORY =  '/disk2/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk3/data'                  INDEX DIRECTORY =  '/disk3/idx'          ),          PARTITION p2 VALUES  LESS THAN MAXVALUE          (              SUBPARTITION s0                  DATA DIRECTORY = '/disk4/data'                  INDEX DIRECTORY =  '/disk4/idx' ,              SUBPARTITION s1                  DATA DIRECTORY = '/disk5/data'                  INDEX DIRECTORY =  '/disk5/idx'          )      );

Note:子分区是分区表中每个分区的再次分割，DATA DIRECTORY/INDEX DIRECTORY确定数据和索引的存储位置

-----------------------------------------------------------------------------------------------------------------------------

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) 
    ( 
        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，分区不支持外键。

6，不能使用LOAD INDEX INTO CACHE

7，分区并不能总是改善性能，要进行性能评测。

例如可以使用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)