mysql分区举例---RANGE(范围)

MYSQL版本：windows下mysql5.5.23，存储引擎：INNODB

 

 

   参考《MYSQL技术内幕SQL编程》一书

 

   在这里总结一下mysql分区的使用，理解也不是很深，暂时停留在基本使用上

   MYSQL分区方式：

1.RANGE：行数据基于属于一个给定连续区间的列值放入分区。MySql 5.5开始

支持RANGE COLUMNS分区。

2.LIST：和RANGE分区类型一样，只是LIST分区面向的是离散的值。MySql 5.5开始

支持LIST COLUMNS分区。

3.HASH：根据用户自定义表达式的返回值来进行分区，返回值不能为负数。

4.KEY:根据MYSQL数据库提供的散列函数来进行分区。

5.COLUMNS：MYSQL5.5开始支持COLUMNS分区，可视为RANGE和LIST分区的一个进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得到，不需要转化为整型。

COLUMNS分区支持以下数据类型：

• 所有的整型类型，如INT,TINYINT,SMALLINT,BIGINT。对FLOAT和DECIMAL不支持
• 日期类型，DATE,DATETIME。其余日期类型不支持。
• 字符串类型，如CHAR,VARCHAR,BINARY,VARBINARY。对BLOB和TEXT不支持。

 

(一)RANGE分区：

(1)数字分区

创建表和分区：

两个分区，P0为小于10的分区，P1为10到19的分区

CREATE TABLE t (

  id int

) 

PARTITION BY RANGE (id)(

    PARTITION p0 VALUES LESS THAN (10) ,

    PARTITION p1 VALUES LESS THAN (20)

) ;

 

插入数据：

mysql> select * from t;

Empty set (0.00 sec)

 

mysql> insert into t select 1;

Query OK, 1 row affected (0.12 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 2;

Query OK, 1 row affected (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 3;

Query OK, 1 row affected (0.16 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 10;

Query OK, 1 row affected (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 11;

Query OK, 1 row affected (0.08 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 12;

Query OK, 1 row affected (0.08 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from t;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|   10 |

|   11 |

|   12 |

+------+

6 rows in set (0.00 sec)

 

查看分区具体信息：

mysql> select * from information_schema.partitions

    -> where table_schema=database() and table_name='t'\G;

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

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test

                   TABLE_NAME: t

               PARTITION_NAME: p0

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: RANGE

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: id

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: 10

                   TABLE_ROWS: 3

               AVG_ROW_LENGTH: 5461

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 26214400

                  CREATE_TIME: NULL

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

*************************** 2. row ***************************

                TABLE_CATALOG: def

                 TABLE_SCHEMA: test

                   TABLE_NAME: t

               PARTITION_NAME: p1

            SUBPARTITION_NAME: NULL

   PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: NULL

             PARTITION_METHOD: RANGE

          SUBPARTITION_METHOD: NULL

         PARTITION_EXPRESSION: id

      SUBPARTITION_EXPRESSION: NULL

        PARTITION_DESCRIPTION: 20

                   TABLE_ROWS: 3

               AVG_ROW_LENGTH: 5461

                  DATA_LENGTH: 16384

              MAX_DATA_LENGTH: NULL

                 INDEX_LENGTH: 0

                    DATA_FREE: 0

                  CREATE_TIME: NULL

                  UPDATE_TIME: NULL

                   CHECK_TIME: NULL

                     CHECKSUM: NULL

            PARTITION_COMMENT:

                    NODEGROUP: default

              TABLESPACE_NAME: NULL

2 rows in set (0.04 sec)

 

可以看到PARTITION_NAME:P0分区插入TABLE_ROWS:3条数据，P1分区插入3条数据

 

如果此时插入一个30，那么报错

mysql> insert into t select 30;

ERROR 1526 (HY000): Table has no partition for value 30

 

增加分区：

mysql> alter table t add partition(partition p2 values less than (40));

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 30;

Query OK, 1 row affected (0.14 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from t;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|   10 |

|   11 |

|   12 |

|   30 |

+------+

7rows in set (0.00 sec)

 

 

还可以对分区添加一个MAXVALUE值的分区，MAXVALUE可以理解为正无穷大，大于30的并且小于MAXVALUE的值都可以放入P3区，如下：

mysql> alter table t add partition(partition p3 values less than maxvalue);

Query OK, 0 rows affected (0.36 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into t select 50;

Query OK, 1 row affected (0.10 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from t;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|   10 |

|   11 |

|   12 |

|   30 |

|   50 |

+------+

8rows in set (0.01 sec)

 

 

分析：

mysql> explain partitions select * from t where id>=1 and id<10\G;

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

           id: 1

  select_type: SIMPLE

        table: t

   partitions: p0

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: Using where

1 row in set (0.00 sec)

可以看到只在P0分区使用

 

因为id范围在p0分区内，但是:

mysql> explain partitions select * from t where id>=1 and id<12\G;

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

           id: 1

  select_type: SIMPLE

        table: t

   partitions: p0,p1

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 6

        Extra: Using where

1 row in set (0.00 sec)

使用了两个分区，所以根据实际情况进行分区。

 

(2)日期分区

在对RANGE按日期分区的查询，优化器只能对YEAR(),TO_DAYS(),TO_SECONDS()和

UNIX_TIMESTAMP()进行优化选择。

创建表和分区

P201001 分区小于2010年2月份

P201002 分区小于2010年3月份

P201003 分区小于2010年4月份

 

CREATE TABLE sales2 (

  money int(11) NOT NULL,

  date datetime DEFAULT NULL

) 

PARTITION BY RANGE (YEAR(date)*100+MONTH(date))

(PARTITION P201001 VALUES LESS THAN (201002),

 PARTITION P201002 VALUES LESS THAN (201003) ,

 PARTITION P201003 VALUES LESS THAN (201004) ) ;

 

插入数据：

mysql> insert into sales2 select 1,'20100102';

Query OK, 1 row affected (0.09 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into sales2 select 1,'20100103';

Query OK, 1 row affected (0.09 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into sales2 select 1,'20100203';

Query OK, 1 row affected (0.12 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> insert into sales2 select 1,'20100303';

Query OK, 1 row affected (0.08 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

分析：

mysql> explain partitions select * from sales2

    -> where date>='2010-01-01' and date<='2010-1-31'\G;

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

           id: 1

  select_type: SIMPLE

        table: sales2

   partitions: P201001,P201002,P201003

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 4

        Extra: Using where

1 row in set (0.00 sec)

 

看到这里，可以发现对三个分区都进行了搜索，并不是我们想要的，这样处理：

CREATE TABLE sales (

  money int(11) NOT NULL,

  date datetime DEFAULT NULL

) 

PARTITION BY RANGE (TO_DAYS(date))

(PARTITION P201001 VALUES LESS THAN (TO_DAYS('2010-02-01')),

 PARTITION P201002 VALUES LESS THAN ( TO_DAYS('2010-03-01') ),

 PARTITION P201003 VALUES LESS THAN ( TO_DAYS('2010-04-01') )) ;

 

 

mysql> explain partitions select * from sales

    -> where date>='2010-01-01' and date<='2010-1-31'\G;

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

           id: 1

  select_type: SIMPLE

        table: sales

   partitions: P201001

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 3

        Extra: Using where

1 row in set (0.00 sec)

 

可以看到只在P0分区进行搜索