mysql分区-range

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)

    -> );

Query OK, 0 rows affected (0.20 sec)

查询分区信息

通过查看建表语句的方式来看

mysql> SHOW CREATE TABLE sale_data\G

*************************** 1. row ***************************

       Table: sale_data

Create Table: CREATE TABLE `sale_data` (

  `sale_date` datetime NOT NULL,

  `sale_item` varchar(2) NOT NULL,

  `sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date))

(PARTITION p201001 VALUES LESS THAN (201002) ENGINE = InnoDB,

PARTITION p201002 VALUES LESS THAN (201003) ENGINE = InnoDB,

PARTITION p201003 VALUES LESS THAN (201004) ENGINE = InnoDB,

PARTITION p201004 VALUES LESS THAN (201005) ENGINE = InnoDB,

PARTITION p201005 VALUES LESS THAN (201006) ENGINE = InnoDB,

PARTITION p201006 VALUES LESS THAN (201007) ENGINE = InnoDB,

PARTITION p201007 VALUES LESS THAN (201008) ENGINE = InnoDB,

PARTITION p201008 VALUES LESS THAN (201009) ENGINE = InnoDB,

PARTITION p201009 VALUES LESS THAN (201010) ENGINE = InnoDB) */

1 row in set (0.00 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

核对结果

mysql> SHOW CREATE TABLE sale_data\G

*************************** 1. row ***************************

       Table: sale_data

Create Table: CREATE TABLE `sale_data` (

  `sale_date` datetime NOT NULL,

  `sale_item` varchar(2) NOT NULL,

  `sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date))

(PARTITION p2010Q1 VALUES LESS THAN (201004) ENGINE = InnoDB,

PARTITION p2010Q2 VALUES LESS THAN (201007) ENGINE = InnoDB,

PARTITION p2010Q3 VALUES LESS THAN (201010) ENGINE = InnoDB) */

1 row in set (0.00 sec)

分区的拆分

下面的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

核对结果

mysql> SHOW CREATE TABLE sale_data\G

*************************** 1. row ***************************

       Table: sale_data

Create Table: CREATE TABLE `sale_data` (

  `sale_date` datetime NOT NULL,

  `sale_item` varchar(2) NOT NULL,

  `sale_money` decimal(10,2) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

/*!50100 PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date))

(PARTITION s2009 VALUES LESS THAN (201001) ENGINE = InnoDB,

PARTITION s2010 VALUES LESS THAN (201004) ENGINE = InnoDB,

PARTITION p2010Q2 VALUES LESS THAN (201007) ENGINE = InnoDB,

PARTITION p2010Q3 VALUES LESS THAN (201010) ENGINE = InnoDB) */

1 row in set (0.02 sec)